SQL Check Register, Invoice & G/L Expense Account Extract (F0413,F0414,F0411,F0911)

[jdenub]

SQL Check Register, Invoice & G/L Expense Account Extract (F0413,F0414,F0411,F0911)

I have a request from Accounting to pull Accounts Payable data for a certain time period. The extract needs to contain the check date, check#, voided date, check amount, invoice date, invoice#, invoice amt, discount amt, gl/acct and g/l amount. I have made several attempts to pull the data together and latest query is shown below. As you can see, I am certainly not a SQL Query writer of the caliber I have seen on these forums but I am trying. Where I seem to run into problems is that there is not always corresponding sfx field in all of the files. Sfx 5 in F0414 & F0411 appears to correspond with sfx 2 in F0911. I've attached an excel file of data to show one example of problem. Am I on the right track and is there a way to get around the problem? I have searched several posts and websites and have garnered much valuable information. However, I have not found one example where anyone was able to pull this off in SQL. Perhaps JDE was not designed to allow this. I notice on the supplier ledger and supplier payment screens that you can view invoice or g/l information but not a correlation of the two. For the most part, the extract is correct - only about 60+ out of 2000 check are incorrect. But you know accountants - they like things to be perfect, lol. Any help would be much appreciated. I hope I have given all information that is needed.

with apgltemp as
(select glkco, gldoc, gldct, glsfx, glani, sum(glaa) as "gltot"
from [JDE_PRODUCTION].[PRODDTA].[F0911]
where gldct in ('PD','PV')
group by glkco, gldoc, gldct, glsfx, glani)

select RMICU, rmdocm as "check_number",
substring(convert(varchar(19),(dateadd(dayofyear,rmdmtj % 1000 - 1,
dateadd(year, rmdmtj / 1000, '1900-01-01'))),106),1,2)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmdmtj % 1000 - 1,
dateadd(year, rmdmtj / 1000, '1900-01-01'))),106),4,3)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmdmtj % 1000 - 1,
dateadd(year, rmdmtj / 1000, '1900-01-01'))),106),8,4)
as "Check_Date",
substring(convert(varchar(19),(dateadd(dayofyear,rmvdgj % 1000 - 1,
dateadd(year, rmvdgj / 1000, '1900-01-01'))),106),1,2)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmvdgj % 1000 - 1,
dateadd(year, rmvdgj / 1000, '1900-01-01'))),106),4,3)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmvdgj % 1000 - 1,
dateadd(year, rmvdgj / 1000, '1900-01-01'))),106),8,4)
as "Void_Date",
rmpye as "supplier#",
(select abalph FROM [JDE_PRODUCTION].[PRODDTA].[F0101]
where rmpye=aban8) as "suppler_name",
rmdctm as "check_type", (rmpaap/100)*-1 as "check_amount", rmpyid, rndoc, rpam, rndct,
rpvinv as "invoice#", rpurrf as "P.O.#",
substring(convert(varchar(19),(dateadd(dayofyear,rpdgj % 1000 - 1,
dateadd(year, rpdgj / 1000, '1900-01-01'))),106),1,2)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rpdgj % 1000 - 1,
dateadd(year, rpdgj / 1000, '1900-01-01'))),106),4,3)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rpdgj % 1000 - 1,
dateadd(year, rpdgj / 1000, '1900-01-01'))),106),8,4)
as "invoice_date",
(select sum(rpag/100) from [JDE_PRODUCTION].[PRODDTA].[F0411]
where rndoc=rpdoc and rndoc=rpdoc and rndct=rndct) as "inv_amount",
(select sum(rpadsa/100) from [JDE_PRODUCTION].[PRODDTA].[F0411]
where rndoc=rpdoc and rndoc=rpdoc and rndct=rndct) as "disc_amount",

glani as "account", gltot/100 as "g/l_amount"
from [JDE_PRODUCTION].[PRODDTA].[F0413],
[JDE_PRODUCTION].[PRODDTA].[F0414],
[JDE_PRODUCTION].[PRODDTA].[F0411],
[apgltemp]
where (rmpyid=rnpyid and rmdctm=rndctm and
(rnkco=rpkco and rndoc=rpdoc and rpdct=rndct and rnsfx=rpsfx) and
(rnkco=glkco and rndoc=gldoc and rndct=gldct) and
rpam<>' ' and rmdctm in ('PN','PO','PK')) and
((rmdmtj>='114001' and rmvdgj=0) or rmvdgj>='114001')

union all

select RMICU, rmdocm as "check_number",
substring(convert(varchar(19),(dateadd(dayofyear,rmdmtj % 1000 - 1,
dateadd(year, rmdmtj / 1000, '1900-01-01'))),106),1,2)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmdmtj % 1000 - 1,
dateadd(year, rmdmtj / 1000, '1900-01-01'))),106),4,3)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmdmtj % 1000 - 1,
dateadd(year, rmdmtj / 1000, '1900-01-01'))),106),8,4)
as "Check_Date",
substring(convert(varchar(19),(dateadd(dayofyear,rmvdgj % 1000 - 1,
dateadd(year, rmvdgj / 1000, '1900-01-01'))),106),1,2)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmvdgj % 1000 - 1,
dateadd(year, rmvdgj / 1000, '1900-01-01'))),106),4,3)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rmvdgj % 1000 - 1,
dateadd(year, rmvdgj / 1000, '1900-01-01'))),106),8,4)
as "Void_Date",
rmpye as "supplier#",
(select abalph FROM [JDE_PRODUCTION].[PRODDTA].[F0101]
where rmpye=aban8) as "suppler_name",
rmdctm as "check_type", (rmpaap/100)*-1 as "check_amount", rmpyid, rndoc, rpam, rndct,
rpvinv as "invoice#", rpurrf as "P.O.#",
substring(convert(varchar(19),(dateadd(dayofyear,rpdgj % 1000 - 1,
dateadd(year, rpdgj / 1000, '1900-01-01'))),106),1,2)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rpdgj % 1000 - 1,
dateadd(year, rpdgj / 1000, '1900-01-01'))),106),4,3)+'-'+
substring(convert(varchar(19),(dateadd(dayofyear,rpdgj % 1000 - 1,
dateadd(year, rpdgj / 1000, '1900-01-01'))),106),8,4)
as "invoice_date",
(select sum(rpag/100) from [JDE_PRODUCTION].[PRODDTA].[F0411]
where rnkco=rpkco and rndoc=rpdoc and rndct=rndct) as "inv_amount",
(select sum(rpadsa/100) from [JDE_PRODUCTION].[PRODDTA].[F0411]
where rndoc=rpdoc and rndoc=rpdoc and rndct=rndct) as "disc_amount",
glani as "account", gltot/100 as "g/l_amount"
from [JDE_PRODUCTION].[PRODDTA].[F0413],
[JDE_PRODUCTION].[PRODDTA].[F0414],
[JDE_PRODUCTION].[PRODDTA].[F0411],
[apgltemp]
where (rmpyid=rnpyid and rmdctm=rndctm and
(rnkco=rpkco and rndoc=rpdoc and rpdct=rndct and rnsfx=rpsfx) and
(rnkco=glkco and rndoc=gldoc and rndct=gldct and rnsfx=glsfx) and
rpam=' ' and rmdctm in ('PN','PO','PK')) and
((rmdmtj>='114001' and rmvdgj=0) or rmvdgj>='114001')
order by rmicu, rmdocm, rpvinv;

 

[peterbruce]

jdenub,

I haven't had time to look at your post in detail, but below is some information that may assist you.

When dealing with payments, start with the Payment header table F0413. It contains all the cheque (I'm Australian) level detail like cheque numbers and dates. Next is the F0414 which contains the Voucher lines that the cheque is paying. The F0414 contains the key to the F0411 so you can connect to it for any detail that is not in the F0414. Last is the F0911 which contains the journal lines for both the cheques (use the F0413 detail to look at the F0911) and the vouchers (use the F0411 detail to look at the F0911).

 

[peterbruce]

jdenub,

A couple of questions:

1) For which database is this? (Oracle, DB2, etc.)
2) How many reports are expected? (2?)

A couple of threads that may help:

1) http://www.jdelist.com/vb4/showthread.php/38710-Accounts-Payable-Questions
2) http://www.jdelist.com/vb4/showthre...(Normal)-to-Julian-Conversion-Oracle-Function