cancel
Showing results for 
Search instead for 
Did you mean: 

Bank Positive Pay File

keith_taylor2
Active Contributor
0 Kudos

Has anyone developed a postive pay file export using query. I know how to get a listing of checks for a day but I am trying to figure out how to export the values all as one string with padded zeros. Example of the layout:

Sample line of layout:

10028xxxxxxxxxx00000260912007122000001196000

explanation of layout

10028=control nbr

xxxxxxxxxx=bank account number

0000026091 = check number 26091

20071220 = date of check

00001196000 = value of check in this case 11,960.00

Any help is greatly appreciated

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

We're using 2005A SP01 PL:11. But unless it has been fixed in a newer version, the OCHO table does not always contain the VOID information. If the disbursement originated from an outgoing payment, the outgoing payment table holds the void information, and OCHO is not updated.

Answers (1)

Answers (1)

former_member583013
Active Contributor
0 Kudos

Keith,

If the data is available in the table, then you would need to create a SQL with text padding.

SELECT '10028''Bank Account Number'

CASE WHEN LEN(check number) = 5 THEN '00000'+(CheckNumber)

WHEN LEN(..) = 4 THEN '000000'+(checknumber)

..

..

END

+

checkdateCAST(check value AS VARCHAR(10)'00'

Use the above syntax and I believe you should be able to get it.

NOTE: Where ever the field data type is not char use CAST/CONVERT to make them Char type so that the Concatenation works

Suda

keith_taylor2
Active Contributor
0 Kudos

Kind sir,

when I try your command even without amount I get integer overflow error. Am I doing something wrong here?

SELECT '10028'+'0123456789' +

CASE WHEN LEN(T0.CheckNum) = 1 THEN '000000000'(T0.CheckNum) WHEN LEN(T0.CheckNum) = 2 THEN '00000000'(T0.CheckNum) end

FROM [dbo].[OCHO] T0 with (NOLOCK) where T0.createdate >= '[%1]' and T0.createdate <= '[%2]'