Project Invoice Query

This is an AP invoice query that we use to do a reconciliation between Payable, GL and Projects. The base of the query is a map from Payables to General Ledger through the XLA. It also pulls in project and vendor information.

Select aia.invoice_num AS "Invoice Number", REPLACE(REPLACE(aida.description,chr(10),''),chr(13),'') AS "Distribution Description",
aida.invoice_distribution_id,
NVL(psv.vendor_name, (SELECT PERSON_FIRST_NAME||' '||person_last_name
    FROM hz_parties hp
    WHERE aia.party_id = hp.party_id)) AS Supplier,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 "GL Combination",
aida.PJC_EXPENDITURE_ITEM_DATE,
aia.SOURCE AS "Invoice Source",
pett.EXPENDITURE_TYPE_NAME,
prj.segment1 AS "Project Number",
prj.name AS "Project Name",
tsk.task_number AS "Task",
poev.name AS "Project Organization",
aida.amount AS "Distribution Amount",
xah.period_name
--, aia.*FROM AP_INVOICES_ALL aia,
ap_invoice_lines_all al,
AP_INVOICE_DISTRIBUTIONS_ALL aida,
XLA_DISTRIBUTION_LINKS XDL,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
GL_JE_LINES gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc,
poz_suppliers_v psv,
PJF_EXP_TYPES_TL pett,
PJF_PROJECTS_ALL_VL prj,
PJC_XLA_TASK_REF_V tsk,
PJF_ORGANIZATIONS_EXPEND_V poev
WHERE 1=1
AND al.invoice_id = aia.invoice_id
AND al.line_number = aida.invoice_line_number
AND al.invoice_id = aida.invoice_id
AND aida.invoice_distribution_id = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND aida.accounting_event_id = xdl.event_id
AND xal.ae_header_id = xdl.ae_header_id       AND xal.ae_line_num = xdl.ae_line_num
AND XDL.ae_header_id = xah.ae_header_id
AND xal.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjl.je_header_id = gjh.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.code_combination_id = gcc.code_combination_id
AND aia.vendor_id = psv.vendor_id (+)
AND aida.pjc_EXPENDITURE_TYPE_ID = pett.EXPENDITURE_TYPE_ID
AND aida.pjc_project_id = prj.project_id
AND aida.pjc_task_id = tsk.task_id
AND aida.pjc_organization_id = poev.organization_id
AND poev.effective_end_date > aida.PJC_EXPENDITURE_ITEM_DATE
AND poev.effective_start_date < aida.PJC_EXPENDITURE_ITEM_DATE
AND xdl.APPLIED_TO_APPLICATION_ID = 200
AND XDL.accounting_line_code not IN ('AP_LIAB_INV','AP_LIAB_PMT','AP_CASH_PMT','AP_LIAB_CM' )
AND xal.accounting_class_code != 'INTRA'
--AND al.LINE_SOURCE != 'ETAX'AND aia.invoice_num = NVL(:Invoice, aia.invoice_num)
AND prj.name LIKE '%'||:PrjName||'%'
AND (psv.vendor_name LIKE '%'||:SupplierName||'%' OR 'All' IN (:SupplierName||'All'))
AND xah.period_name = NVL(:Period,xah.period_name)
AND (prj.segment1 IN (:Project_num) OR 'All' IN (:Project_num||'All'))
UNION ALL
Select aia.invoice_num AS "Invoice Number", REPLACE(REPLACE(st.description,chr(10),''),chr(13),'') AS "Distribution Description",
st.invoice_distribution_id,
psv.vendor_name AS Supplier,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 "GL Combination",
st.PJC_EXPENDITURE_ITEM_DATE,
aia.SOURCE AS "Invoice Source",
pett.EXPENDITURE_TYPE_NAME,
prj.segment1 AS "Project Number",
prj.name AS "Project Name",
tsk.task_number AS "Task",
poev.name AS "Project Organization",
st.amount AS "Distribution Amount",
xah.period_name
--distinct XDL.*FROM AP_INVOICES_ALL aia,
ap_invoice_lines_all al,
AP_SELF_ASSESSED_TAX_DIST_ALL st,
XLA_DISTRIBUTION_LINKS XDL,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
GL_JE_LINES gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc,
poz_suppliers_v psv,
PJF_EXP_TYPES_TL pett,
PJF_PROJECTS_ALL_VL prj,
PJC_XLA_TASK_REF_V tsk,
PJF_ORGANIZATIONS_EXPEND_V poev
WHERE 1=1
AND al.invoice_id = aia.invoice_id
AND al.line_number = st.invoice_line_number
AND al.invoice_id = st.invoice_id
AND st.invoice_distribution_id = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND st.accounting_event_id = xdl.event_id
AND xal.ae_header_id = xdl.ae_header_id       AND xal.ae_line_num = xdl.ae_line_num
AND XDL.ae_header_id = xah.ae_header_id
AND xal.GL_SL_LINK_ID = gir.GL_SL_LINK_ID
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjl.je_header_id = gjh.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.code_combination_id = gcc.code_combination_id
AND aia.vendor_id = psv.vendor_id
AND st.pjc_EXPENDITURE_TYPE_ID = pett.EXPENDITURE_TYPE_ID
AND st.pjc_project_id = prj.project_id
AND st.pjc_task_id = tsk.task_id
AND st.pjc_organization_id = poev.organization_id
AND poev.effective_end_date > st.PJC_EXPENDITURE_ITEM_DATE
AND poev.effective_start_date < st.PJC_EXPENDITURE_ITEM_DATE
AND xdl.APPLIED_TO_APPLICATION_ID = 200
AND XDL.accounting_line_code != 'AP_SELF_ASSESSED_TAX_CRL_INV'
AND xal.accounting_class_code != 'INTRA'
AND aia.invoice_num = NVL(:Invoice, aia.invoice_num)
AND prj.name LIKE '%'||:PrjName||'%'
AND psv.vendor_name LIKE '%'||:SupplierName||'%'
AND xah.period_name = NVL(:Period,xah.period_name)
AND (prj.segment1 IN (:Project_num) OR 'All' IN (:Project_num||'All'))