Bank Positive Pay File

[keith_taylor2]

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

[Former Member · Accepted solution]

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.

[former_member583013]

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]

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]'