F0911 to F03B14

I'm not a JDE Financials guy but am trying to find the correlation between the F0911 and F03B14 via SQL. I want to get the Receipt information as well as the Voucher information for specific customers. I am doing a LEFT OUTER JOIN like this:

FROM
F0911 T1
LEFT OUTER JOIN F03B14 T2 ON
T1.GLICU = T2.RZICU AND
T1.GLAN8 = T2.RZAN8

I need to do the LEFT OUTER in order to get the Voucher information from the F0911 since that information is not stored in the F03B14. For some reason, when I do a DISTINCT, it is omitting one of my expected lines, but if I don't do the DISTINCT in there it duplicates or triplicates the records. Hmmm, so I am wondering if there is a true join between these tables or if I need to use an additional table or just fix my JOIN. I hope this is enough information. Thanks in advance for your help!!

mtbinpa,

What do you mean by "Voucher information"? Voucher is the term used by JDE for the AP documents, and your question is about AR.

The correlation between F03B14 and F0911 is something like the following, and links the receipt detail to the debtors control account automatic entries in the GL (one to one):

F0911.GLDOC = F03B14.RZDOCG
F0911.GLDCT = 'AE'
F0911.GLICU = F03B14.RZICU
F0911.GLICUT = F03B14.RZICUT
F0911.GLDGJ = F03B14.RZDGJ
F0911.GLCRCD = F03B14.RZCRCD
F0911.GLJELN = F03B14.RZRC5
F0911.GLEXTL = 'AE'

The Financial Systems Group wants to look at transactions for customers for a specific period of time that includes all of the AR and AP transactions. For example, FY 2008, Period 1, there is a customer that has 3 receipt transactions and 1 voucher. They want to be able to grab all of that information in one nice little report. Like I said, I am not the Financial dude but I try to do what I can to udnerstand it all. I like to stick with the Distribution and Manufacturing side of JDE!! But since everything eventually ends up running through financials, I guess I better know it better.

Tim,

The extra information you supplied is vital to the resolution of you issue. You will have to treat the AP and AR separately, there is actually no need to link to the F0911. By the way, do want AR invoice and/or AP payment information as well? You should also treat the AP Voucher separately to the AP Payment and the AR Invoice separately to the AR Receipt, that is separate SQLs (or use UNION, but that adds another level of complexity).

AR

Invoices are in F03B11
Receipts are in F03B13 (header) and F03B14 (detail) (F03B13.RYPYID = F03B14.RZPYID - one to many)

AP

Vouchers are in F0411
Payments are in F0413 (header) and F0414 (detail) (F0413.RMPYID = F0414.RNPYID - one to many)

Your SQL Where clauses will need to include the following for each table/SQL:

Address Book number
F03B11.RPAN8
F03B13.RYAN8
F0411.RPAN8
F0413.RMAN8

Dates for F03B11, F03B13 and F0411

Century
F03B11.RPCTRY
F03B13.RYCTRY
F0411.RPCTRY

Fiscal year
F03B11.RPFY
F03B13.RYFY
F0411.RPFY

Period
F03B11.RPPN
F03B13.RYPN
F0411.RPPN

Date ranges (including an OR for Void dates) F0413

GL Date
F0413.RMDMTJ

Void Date
F0413.RMVDGJ

This should give you a start. All the best with this.