jdenub
Member
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;
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;