My customer need a report which show the money pay use which account object

[Bob Tsai]

My customer need a report which show the money pay use which account object
sample:
Amount Payment date Obiect
------------ ---------------------- -----------------
1000 12/05/03 2135
2000 12/21/03 2137
I can get pay detial amount use F0414, use Payment ID (PYID) to chain F0413, we can get Payment date (MDTJ) from it.
The problem is where can I get account object?
first I use Doucment Type (DCT), Doucument No. (DOC) and Pay item (SFX) to chain F0911 and F0414, I find in F0911, a lot of "PV" record doesn't have pay item No.
then I try to use Doucment Type (DCT), Doucument No. (DOC) and Pay item (SFX) to chain F0411 and F0414, this time I can get all records but not all records in F0411 have account object,a lot of them is blank
so, who can give me some advise that How JDE standard program do it? because I can find account objet through P0411 even the inquiry programs are different.such as P4314, P041016 ......
tahnks in advance.
Derek

 

[HelenL]

Derek,
I think, that to create a report your customer needs you need to use a business view based on a F0414/F0413 files.
In the Event Rules, you need to do a fetch from F0911 where:
DCT ='PK'
DOC = F0413/DOCM
DGJ = F0413/DMTJ
Good luck

 

[Bob Tsai]

Thanks for your suggest, use PK I can get the payment bank account object, use AE I can get A/P account object.
But the customer needs the expense account object, which they crate a payment voucher they used.
The propose of the report is they want to know the money the pay in a month on which expense object
Such as:
Capital expenditures Realty Tax Operating Cost Other Total Payment
1000 200 500 300 2000
I have no idea now¡­

 

[peterbruce]

Derek, HelenL,

You are both so very close.

First, to see the expense accounts for a voucher (PV Doc) you need to take the GL Distribution row exit in the Supplier Ledger Inquiry or the GL Distribution form exit when inquiring on a PV Doc.

Next to get expense accounts for your report, you need to understand that the expense accounts are not linked to the line items of the PV Doc. They are linked to the PV Doc as a whole.

Use HelenL's suggestion of a business view based on the F0414/F0413 tables and do a fetch from F0911 where:
KCO = F0414.RNKCO (Document Company)
DCT = F0414.RNDCT (Document Type)
DOC = F0414.RNDOC (Document Number)
PN = F0414.RNPN (Period Number)
FY = F0414.RNFY (Fiscal Year)
CTRY = F0414.RNCTRY (Century)
LT = 'AA' (Ledger Type)

That should do the trick. Just be aware that if there are partly paid PV Docs involved then the total of the payment will not equal the total of the expense accounts.

 

[Bob Tsai]

Hi, Peter
I have try this way before, but still have some problem.
You know in F0414 we get detail A/P matching data, it means we have more than 1 record in F0414 for a payment ID in F0413 ( summary A/P mantching), different record has different expence object in F0911
If we use (Document Company, Document Type, Document Number, Period Number, Fiscal Year, Century, Ledger Type) to chain F0911, we can get more than 1 record too.
I try to use pay item No. (SFX) to chain F0911 I find in F0911 PV,PM,PR,PO, but a lot of records in F0911do not have pay item No., so we have mulitple records in both F0911 and F0414, I can not let UBE know which payment item use which object account in F0911 inside one batch.
I have try to use F0411, but not all records have object account in F0411, some is blank.
I can not understand how JDE program can do it even I find the inquiry program in supplier ledger inquiry are different.
thank you all for your help, have a good night
Derek

 

[peterbruce]

Derek,

You will have to put the F0911 fetch in a loop and report the Expense accounts vertically instead of horizontally for each payment. Note that this may have accounts being listed more than once for the same payment. If you need to report the total for each account and then sum them for each payment, I suggest you write the information to a work file, then run another report on the work file.

I'm in Australia, it is about mid day here now. So I wish you a good night.