SQL & PL/SQL

Hi,

I tried to built a query to get the AP payment drill down to GL.

But when I compare the dump shared from third party system, we have n number of lines showing in my query, eveni used group by but even though the query returns multiple lines.

For example :

From the third party dump the balance has been grouped up based on the accounting code combination or segment3 from gl_code_combinations .

But in my query the balances are splitting up into line level and the total no of lined does not matching with the dump.

In dump it is showing 8 lines for AP payment for 01JUL to 10JUL

But in query output hetting more than 700 lines. Bcoz grouping not working based on segment3

Please chekc the query and the attachment

Third party dump:

My query output:

SELECT DISTINCT

gjl.effective_date "Effective Date",

glcc.concatenated\_segments "Account",

gjh.je_source "Journal Source",

initcap(gjl.description) "JE line Description",

gjh.name "Journal Entry",

NULL "Unit Number",

NULL "Work Order Number",

aia.invoice_num "Invoice Number",

poh.segment1 "Purchase Order Number",

NULL "Item Number",

SUM(nvl(XAL.ACCOUNTED_DR,0))-SUM(nvl(XAL.ACCOUNTED_CR,0)) "Balance",

( nvl(aid.quantity\_invoiced, 0) ) "Qty",

concat('INV', aia.invoice_num) "Description",

aps.vendor_name "Supplier",

gjl.period\_name "Period Name",

glcc.segment2 "Location Code"

FROM

gl_je_batches gjb,

gl_je_headers gjh,

gl_je_lines gjl,

gl_import_references gir,

xla_ae_lines xal,

gl_code_combinations_kfv glcc,

xla\_distribution\_links xdl,

ap\_invoice\_distributions\_all aid,

ap_invoices_all aia,

pa_projects_all prj,

pa_tasks pt,

po_distributions_all pd,

po_lines_all pol,

po_headers_all poh,

ap\_suppliers aps,

xla_lookups lkp

WHERE

1 = 1

AND gjh.je\_source = 'Payables'

AND je\_category = 'Payments'

AND gjb.je\_batch\_id = gjh.je\_batch\_id

AND gjh.je\_header\_id = gjl.je\_header\_id

AND gjh.ledger\_id = gjl.ledger\_id

AND gjl.je\_header\_id = gir.je\_header\_id

AND gjl.je\_line\_num = gir.je\_line\_num

AND gir.gl\_sl\_link\_id = xal.gl\_sl\_link\_id

AND gir.gl\_sl\_link\_table = xal.gl\_sl\_link\_table

AND gjl.code\_combination\_id = glcc.code\_combination\_id

AND xal.ae\_header\_id = xdl.ae\_header\_id

AND xal.ae\_line\_num = xdl.ae\_line\_num

AND xal.application\_id = xdl.application\_id

AND xdl.source\_distribution\_id\_num\_1 = aid.invoice\_distribution\_id

AND aid.invoice\_id = aia.invoice\_id

AND aia.vendor\_id = aps.vendor\_id

AND aid.project\_id = prj.project\_id

AND aid.task\_id = pt.task\_id

AND aid.po\_distribution\_id = pd.po\_distribution\_id

AND pd.po\_header\_id = poh.po\_header\_id

AND pd.po\_line\_id = pol.po\_line\_id

AND xal.accounting\_class\_code = lkp.lookup\_code

--AND lkp.lookup\_type = 'XLA\_ACCOUNTING\_CLASS'

--AND aia.invoice\_num = '63342'

-- and glcc.segment3='1008'

AND gjh.actual\_flag = 'A'

AND gjh.status = 'P'

AND ( TO\_DATE(gjl.effective\_date, 'DD-MON-YY') BETWEEN TO\_DATE(nvl(:p\_from\_period, gjl.effective\_date), 'DD-MON-YY') AND TO\_DATE

(nvl(:p\_to\_period, gjl.effective\_date), 'DD-MON-YY')

      OR gjl.effective\_date IS NULL )

GROUP BY

gjl.effective\_date,

glcc.concatenated\_segments,

gjh.je\_source,

gjl.description,

gjh.name,

NULL,

NULL,

aia.invoice\_num,

poh.segment1,

NULL,

--  glcc.code\_combination\_id,

--(NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0)) ,

( nvl(aid.quantity\_invoiced, 0) ),

aia.invoice\_num,

aps.vendor\_name,

gjl.period\_name,

glcc.segment2

.