Page 1 of 1
creating cross module joins
Posted: Wed Jul 08, 2009 1:53 am
by MuhammadZeeshan
Hello friends
I just want to know that what is the link between AR and GL and AP and GL
to get the party wise entries for a particular period. i know how to join these modules to get GL code wise entries but i want to know how to join to get party wise entries.
Posted: Wed Jul 08, 2009 6:23 am
by oteixeira
Hello.
It depends on what you want to see. For example, in GL_JE_LINES table, the system stores the supplier name into REFERENCE_1 column.
Octavio
Posted: Thu Jul 09, 2009 4:45 am
by MuhammadZeeshan
Hello dear
i tried that but no data has been stored in that column.
Posted: Thu Jul 09, 2009 6:56 am
by oteixeira
Hello.
Are you posting in summary from subledgers to the General Ledger?
Octavio
Posted: Fri Jul 10, 2009 3:59 am
by MuhammadZeeshan
Hello
Previously we were using summary but now we are using detail option
when running create accounting
Posted: Fri Jul 10, 2009 6:15 am
by oteixeira
Hello.
Ok. Now tell me which is the information you want to see from:
GL/AP
GL/AR
Octavio
Posted: Sat Jul 11, 2009 1:44 am
by MuhammadZeeshan
Hello
I want to extract supplier wise balances.
In future i have to create more reports relating on these links.
Posted: Mon Jul 13, 2009 5:54 am
by oteixeira
Hello.
Supplier balances are kept on AP. You you want to knoe the balance of a supplier in a certain date enter the following query:
Code: Select all
SELECT
v.vendor_name "Vendor Name", cc.segment2 "Gl Account", v.segment1 "Vendor Num",
SUM (alb.accounted_cr) - SUM (alb.accounted_dr) remaining_amount
FROM ap_liability_balance alb, po_vendors v, gl_code_combinations cc
WHERE trunc(accounting_date) <= '31-DEC-08'
AND alb.ORG_ID = 152 and 1=1
and cc.code_combination_id = alb.code_combination_id
and v.vendor_id = alb.vendor_id
GROUP BY v.vendor_name, v.segment1, cc.segment2
HAVING SUM (accounted_cr) <> SUM (accounted_dr)
Octavio
Posted: Tue Jul 14, 2009 7:17 am
by MuhammadZeeshan
Hello
Thank you for all your help, but i want to see it from GL because in the end its GL from where we get the debit and credit.
Posted: Tue Jul 14, 2009 7:40 am
by oteixeira
Hello.
There's a standard oracle report - Account Analysis - Subledger Detail (180 char).
See if solves your issue.
Octavio
Posted: Thu Jul 16, 2009 1:36 am
by MuhammadZeeshan
Hello
It gives all the balances but not supplier wise, as i have to get supplier wise data this problem still exists.
Posted: Thu Jul 16, 2009 5:34 am
by oteixeira
Hello.
Sorry, i meant Account Analysis - Payables Detail(180 Char). This one shows suppliers names.
Octavio
Posted: Mon Jul 20, 2009 1:46 am
by MuhammadZeeshan
Hello
I have to get results from GL.
The reason is sometimes when create accounting stucks then it displays ACCOUNTING flag yes in AP while the invoices does not move to GL, the amount was same before and after accounting stucks.