creating cross module joins

[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.

[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

[MuhammadZeeshan]

Hello dear
i tried that but no data has been stored in that column.

[oteixeira]

Hello.

Are you posting in summary from subledgers to the General Ledger?

Octavio

[MuhammadZeeshan]

Hello

Previously we were using summary but now we are using detail option
when running create accounting

[oteixeira]

Hello.
Ok. Now tell me which is the information you want to see from:
GL/AP
GL/AR

Octavio

[MuhammadZeeshan]

Hello

I want to extract supplier wise balances.
In future i have to create more reports relating on these links.

[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

[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.

[oteixeira]

Hello.

There's a standard oracle report - Account Analysis - Subledger Detail (180 char).
See if solves your issue.

Octavio

[MuhammadZeeshan]

Hello

It gives all the balances but not supplier wise, as i have to get supplier wise data this problem still exists.

[oteixeira]

Hello.

Sorry, i meant Account Analysis - Payables Detail(180 Char). This one shows suppliers names.

Octavio

[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.