AP Inquiry Report
SELECT period,
ledger_name,
je_batch_name,
je_journal_name,
co,
gac,
lac,
dept,
ico,
proj,
bu,
fut,
/*Manager1,
Manager2,
Manager3,
Manager4,
Manager5,
Manager6,
Manager7,
Fin_Analyst,
Dept_Func,
Sub_To_Bdgt,*/
ent_cy_cd,
book_cy_cd,
amt_ent_cy,
amt_book_cy,
invoice_id,
inv_num,
vendor,
vendor_site_cd,
inv_dt,
inv_creation_dt,
acct_dt,
inv_source,
inv_type,
inv_desc,
terms,
terms_dt,
po_num,
tax_country,
requester,
SYSDATE timestamp,
ap_attachements,
CASE
WHEN EXISTS
(
SELECT 1
FROM fnd_attached_documents fad,
po_headers_all pha
WHERE fad.entity_name IN ('PO_HEAD',
'PO_HEADERS')
AND fad.pk1_value = To_char(pha.po_header_id)
AND pha.segment1 = po_num) THEN 'Y'
ELSE 'N'
END po_attachements
FROM (
SELECT gp.period_name period,
gl.name ledger_name,
gjb.name je_batch_name,
je_journal_name,
gcc.segment1 co,
gcc.segment3 gac,
gcc.segment4 lac,
gcc.segment5 dept,
gcc.segment7 ico,
gcc.segment8 proj,
gcc.segment6 bu,
gcc.segment9 fut,
/* NULL manager1,
NULL manager2,
NULL manager3,
NULL manager4,
NULL manager5,
NULL manager6,
NULL manager7,
NULL fin_analyst,
NULL dept_func,
NULL sub_to_bdgt,*/
--20250512 Removed this columns has specified by kruthika
xal.currency_code ent_cy_cd,
gl.currency_code book_cy_cd,
((Nvl(xal.entered_dr, 0)) - (Nvl(xal.entered_cr, 0))) amt_ent_cy,
((Nvl(xal.accounted_dr, 0)) - (Nvl(xal.accounted_cr, 0))) amt_book_cy,
aia.invoice_id,
aia.invoice_num inv_num,
-- aps.vendor_name vendor,
CASE
WHEN aia.pay_group_lookup_code='SUPPLIERS'
AND aia.invoice_type_lookup_code='STANDARD'THEN aps.vendor_name
ELSE
(
SELECT party_name
FROM hz_parties
WHERE party_id =aia.party_id)
END vendor,
-- else aps.vendor_name end vendor,
-- apss.vendor_site_code vendor_site_cd,
CASE
WHEN aia.pay_group_lookup_code='SUPPLIERS'
AND aia.invoice_type_lookup_code='STANDARD' THEN apss.vendor_site_code
ELSE
(
SELECT party_site_name
FROM hz_party_sites
WHERE party_site_id =aia.party_site_id)
END vendor_site_cd,
To_char(aia.invoice_date ,'MM/DD/YYYY') inv_dt,
To_char(aia.creation_date,'MM/DD/YYYY') inv_creation_dt,
To_char(xal.accounting_date,'MM/DD/YYYY') acct_dt,
aia.source inv_source,
aia.invoice_type_lookup_code inv_type,
aia.description inv_desc,
apt.name terms,
To_char(aia.terms_date,'MM/DD/YYYY') terms_dt,
CASE
WHEN invoice_num ='' THEN max_po.max_po_number
ELSE
(
SELECT phd.segment1 max_po_number
FROM po_distributions_all pod,
po_headers_all phd
WHERE pod.po_distribution_id =
(
SELECT Max(aid.po_distribution_id)
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = aia.invoice_id)
AND phd.po_header_id = pod.po_header_id)
END po_num,
aia.taxation_country tax_country,
fu1.full_name requester,
CASE
WHEN EXISTS
(
SELECT 1
FROM fnd_attached_documents fad
WHERE fad.entity_name = 'AP_INVOICES'
AND fad.pk1_value = To_char(aia.invoice_id)) THEN 'Y'
ELSE 'N'
END ap_attachements
FROM --Xxon_Gl_Fs_Glcc_Attr_Bsheet Xgcc,
gl_code_combinations gcc,
poz_suppliers_v aps,
poz_supplier_sites_all_m apss,
ap_invoices_all aia,
ap_terms apt,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_transaction_entities xte,
gl_ledgers gl,
gl_je_batches gjb,
gl_periods gp,
fnd_application fa,
(
SELECT person_id,
full_name
FROM per_person_names_f
WHERE Trunc(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND name_type='GLOBAL') fu1 ,
(
SELECT DISTINCT aid.org_id org_id,
aid.invoice_id invoice_id,
phd.segment1 max_po_number
FROM ap_invoice_distributions_all aid,
po_distributions_all pod,
po_headers_all phd
WHERE pod.po_distribution_id = aid.po_distribution_id
AND phd.po_header_id = pod.po_header_id
AND aid.po_distribution_id =
(
SELECT DISTINCT Max(mxd.po_distribution_id) over()
FROM ap_invoice_distributions_all mxd
WHERE mxd.invoice_id = aid.invoice_id)) max_po,
lateral
(
SELECT gjh.name je_journal_name
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir
WHERE gjh.actual_flag = 'A'
AND gjh.je_source = 'Payables'
AND gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gcc.code_combination_id = gjl.code_combination_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gl.ledger_id = gjh.ledger_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.period_name = gp.period_name ) je
WHERE aia.vendor_id = aps.vendor_id(+)
AND aia.vendor_site_id = apss.vendor_site_id(+)
AND aps.vendor_id = apss.vendor_id(+)
AND apt.term_id(+) = aia.terms_id
AND aia.invoice_id = max_po.invoice_id(+)
AND aia.org_id = max_po.org_id(+)
AND xte.ledger_id = aia.set_of_books_id(+)
AND xte.entity_code(+) IN ('AP_INVOICES',
'AP_PAYMENTS')
AND aia.invoice_id(+) = nvl(xte.source_id_int_1, (-99))
AND xte.application_id(+) = 200
AND fu1.person_id(+) = aia.requester_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = xe.event_id
AND xe.entity_id = xte.entity_id
AND xah.application_id = xe.application_id
AND fa.application_id = xah.application_id
AND fa.application_id = xte.application_id
AND gl.ledger_id = xah.ledger_id
--AND fa.application_short_name ='SQLAP'
AND gcc.code_combination_id = xal.code_combination_id
AND (
gl.name IN (:P_LEDGER_NAME)
OR 'All' IN (:P_LEDGER_NAME
||'All'))
AND (
gp.period_name IN (:p_period_name)
OR 'All' IN (:p_period_name
||'All'))
AND (
gjb.name IN (:p_je_batch_name)
OR 'All' IN (:p_je_batch_name
||'All'))
AND (
je.je_journal_name IN (:P_journal_name)
OR 'All' IN(:P_journal_name
||'All'))
AND (
gcc.segment1 IN (:P_CO)
OR 'All' IN (:P_CO
||'All'))
AND (
gcc.segment3 IN (:P_GAC)
OR 'All' IN (:P_GAC
||'All'))
AND (
gcc.segment5 IN (:P_DEPT)
OR 'All' IN (:P_DEPT
||'All'))
AND (
gcc.segment8 IN (:P_PROJ)
OR 'All' IN (:P_PROJ
||'All'))
AND (
gcc.segment6 IN (:P_BU)
OR 'All' IN (:P_BU
||'All'))
AND (
aia.invoice_num IN (:P_INVOICE_NUM)
OR 'All' IN (:P_INVOICE_NUM
||'All'))
AND (
max_po.max_po_number IN (:P_max_po_number)
OR 'All' IN (:P_max_po_number
||'All'))
AND aia.invoice_num IS NOT NULL
AND (
aps.vendor_name IN (:P_VENDOR_NAME)
OR 'All' IN (:P_VENDOR_NAME
||'All'))
AND (
apss.vendor_site_code IN (:P_VENDOR_SITE_CD)
OR 'All' IN (:P_VENDOR_SITE_CD
||'All')) )
WHERE 1=1
GROUP BY period,
ledger_name,
je_batch_name,
je_journal_name,
co,
gac,
lac,
dept,
ico,
proj,
bu,
fut,
/*Manager1,
Manager2,
Manager3,
Manager4,
Manager5,
Manager6,
Manager7,
Fin_Analyst,
Dept_Func,
Sub_To_Bdgt,*/
ent_cy_cd,
book_cy_cd,
amt_ent_cy,
amt_book_cy,
invoice_id,
inv_num,
vendor,
vendor_site_cd,
inv_dt,
inv_creation_dt,
acct_dt,
inv_source,
inv_type,
inv_desc,
terms,
terms_dt,
po_num,
tax_country,
requester,
-- SYSDATE TIMESTAMP,
ap_attachements
No comments:
Post a Comment