按着 项目号(gcc.segment5),业务实体(org_name),供应商维度取值
主循环Cursor
SELECT hou.name org_name
,ai.vendor_id --业务实体
,gcc.segment5
,nvl(pv.vendor_name
,hp.party_name) vendor_name -- 供应商
FROM ap_invoices_all ai
,po_vendors pv
,hz_parties hp
,po_vendor_sites_all pvs
,hr_operating_units hou
,xla_transaction_entities xte
,xla_events xe
,xla_ae_headers xah
,xla_ae_lines xal
,gl_code_combinations_kfv gcc
,gl_code_combinations_kfv gcc1
WHERE 1 = 1
AND ai.set_of_books_id = xte.ledger_id
AND ai.invoice_id = xte.source_id_int_1
AND xte.entity_id = xe.entity_id
AND xe.event_id = xah.event_id
AND xal.ae_header_id = xah.ae_header_id
AND ai.accts_pay_code_combination_id = gcc1.code_combination_id
AND hou.organization_id = ai.org_id
AND ai.org_id = nvl(122 --p_organization_id
,ai.org_id)
AND ai.vendor_site_id = pvs.vendor_site_id(+)
AND pv.vendor_id(+) = ai.vendor_id
AND ai.party_id = hp.party_id
AND xal.code_combination_id = gcc.code_combination_id
AND gcc.segment5 <> 0
GROUP BY hou.name
,ai.vendor_id --业务实体
,gcc.segment5
,nvl(pv.vendor_name
,hp.party_name)
--------------- 取分类账金额
SELECT SUM(nvl(xal.accounted_cr
,0) - nvl(xal.accounted_dr
,0))
FROM xla_ae_lines xal
,xla_transaction_entities xte
,xla_events xe
,xla_ae_headers xah
,gl_code_combinations_kfv gcc
,ap_invoices_all ai
WHERE 1 = 1
AND nvl(p_org_id
,ai.org_id) = ai.org_id
--ap xte
AND ai.set_of_books_id = xte.ledger_id --
AND ai.invoice_id = xte.source_id_int_1 --
-- xte xal
AND xte.entity_id = xe.entity_id
AND xe.event_id = xah.event_id
AND xal.ae_header_id = xah.ae_header_id
AND nvl(nvl(xal.accounted_cr
,xal.accounted_dr)
,0) <> 0
--xal gcc
AND xal.code_combination_id = gcc.code_combination_id
AND gcc.segment5 = nvl(p_item
,gcc.segment5)
AND gcc.segment3 IN ('2202020101'
,'2202030101')
AND (to_char(xal.accounting_date
,'yyyy') = to_char(l_period_num
,'yyyy') OR l_period_num IS NULL)
AND (xal.accounting_date < l_period_num OR l_period_num IS NULL)
AND xal.party_id = nvl(p_vendor_id
,xal.party_id);