Hi, I'm writing a query that pulls the same data that is in the projects expenditure items screen (in Projects controller resp) and I can pull everything through apart from the PO Number. The base view for the screen is PA_EXPEND_ITEMS_ADJUST2 but nothing was returning even when I set the appropriate contexts. However I've just gone down the route of building the report from the base tables (its good for my knowledge as much as anything) but I can't seem to join to the PO Number from anywhere. I managed to get some but not all POs going via AP_INVOICES_ALL from PA_COST_DISTRIBUTION_LINES_ALL (system_reference2 is the invoice_id and System_reference1 is the vendor_id). Code is below (apologies, very messy):
SELECT
EI.EXPENDITURE_ITEM_ID Transaction_ID,
aia.invoice_id,
--pei.po_number,
-- pei.ORIG_USER_EXP_TXN_REFERENCE,
nvl(pe.po_number,(select segment1
from PO_headers_all poh
where poh.po_header_id = aia.quick_po_header_id)) PO_Number,
pe.invoice_number PO_receipt_INV_number,
(SELECT vendor_name
FROM ap_suppliers aps
WHERE aps.vendor_id = pcdl.system_reference1)
Supplier,
(SELECT segment1
FROM ap_suppliers aps
WHERE aps.vendor_id = pcdl.system_reference1)
Supplier_Number,
aia.invoice_num,
DECODE (pe.transfer_status_code, 'T', 'Transferred', 'P', 'Pending')
Transfer_status,
TR.USER_TRANSACTION_SOURCE,
O1.NAME EXPENDITURE_ORGANIZATION_NAME,
PT.PROJECT_TYPE,
DECODE (PT.PROJECT_TYPE_CLASS_CODE, 'CAPITAL', EI.BILLABLE_FLAG, NULL)
CAPITALIZABLE_FLAG,
P.SEGMENT1 PROJECT_NUMBER,
P.NAME PROJECT_NAME,
t.task_number,
EI.EXPENDITURE_TYPE,
(SELECT p.full_name
FROM PER_ALL_PEOPLE_F p
WHERE p.person_id = x.incurred_by_person_id
AND EI.expenditure_item_date BETWEEN p.effective_start_date
AND p.effective_end_date)
EMPLOYEE_NAME,
(SELECT DECODE (p.current_employee_flag,
'Y', p.employee_number,
NVL (p.npw_number, p.employee_number))
FROM PER_ALL_PEOPLE_F p
WHERE p.person_id = x.incurred_by_person_id
AND ei.expenditure_item_date BETWEEN p.effective_start_date
AND p.effective_end_date)
EMPLOYEE_NUMBER,
NVL (EMP.FULL_NAME, (SELECT vendor_name
FROM ap_suppliers aps
WHERE aps.vendor_id = pcdl.system_reference1)) EMPLOYEE_VENDOR_NAME,
EI.BURDEN_COST,
(SELECT EXPENDITURE_COMMENT
FROM pa_expenditure_comments
WHERE EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID)
EXPENDITURE_COMMENT,
x.expenditure_group expenditure_batch,
ei.orig_transaction_reference,
ei.expenditure_item_date,
TO_DATE (
PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME (
EI.EXPENDITURE_ITEM_ID,
'GL_DATE'),
'DD-MON-RRRR')
LATEST_GL_DATE,
SUBSTR (
PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME (
EI.EXPENDITURE_ITEM_ID,
'GL_PERIOD_NAME'),
1,
15)
LATEST_GL_PERIOD_NAME,
(SELECT segment1
FROM gl_code_combinations gcc
WHERE pe.cip_ccid = gcc.code_combination_id)
Enity,
(SELECT segment2
FROM gl_code_combinations gcc
WHERE pe.cip_ccid = gcc.code_combination_id)
BU,
(SELECT segment3
FROM gl_code_combinations gcc
WHERE pe.cip_ccid = gcc.code_combination_id)
CC,
(SELECT segment4
FROM gl_code_combinations gcc
WHERE pe.cip_ccid = gcc.code_combination_id)
Account,
(SELECT segment5
FROM gl_code_combinations gcc
WHERE pe.cip_ccid = gcc.code_combination_id)
Counterparty ,
p.project_id
FROM PA_PROJECTS_ALL P,
PA_TASKS T,
PA_EXPENDITURE_ITEMS_ALL EI,
PA_EXPENDITURES_ALL X,
PA_PROJECT_TYPES_ALL PT,
PA_TRANSACTION_SOURCES TR,
HR_ALL_ORGANIZATION_UNITS_TL O1,
pa_project_asset_lines_all pe,
PA_ASSET_LINE_DETAILS_v pal,
PER_JOBS J,
pa_cost_distribution_lines_all pcdl,
ap_invoices_all aia,
per_all_people_f emp
WHERE T.PROJECT_ID = P.PROJECT_ID
AND EI.PROJECT_ID = P.PROJECT_ID
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND P.ORG_ID = PT.ORG_ID
AND EI.TASK_ID = T.TASK_ID
AND EI.EXPENDITURE_ID = X.EXPENDITURE_ID
AND NVL (EI.OVERRIDE_TO_ORGANIZATION_ID,
X.INCURRED_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID
AND EI.JOB_ID = J.JOB_ID(+)
AND EI.TRANSACTION_SOURCE = TR.TRANSACTION_SOURCE(+)
AND O1.LANGUAGE = USERENV ('LANG')
AND EXISTS
(SELECT 1
FROM PA_IMPLEMENTATIONS_all IMP
WHERE ( EI.ORG_ID = IMP.ORG_ID
OR NVL (PA_EXPENDITURE_INQUIRY.Get_Mode, 'X') <>
'CROSS-PROJECT'))
AND ei.expenditure_item_id = pal.expenditure_item_id(+)
-- AND ei.expenditure_item_id = pei.expenditure_item_id
AND ei.document_distribution_id = pe.invoice_distribution_id(+)
AND ei.project_id = 78008
and ei.task_id = t.task_id
and ei.expenditure_item_id = pcdl.expenditure_item_id
and pcdl.system_reference2 = aia.invoice_id(+)
AND EI.EXPENDITURE_ITEM_ID = '444092'--'625919'
--AND EI.EXPENDITURE_ITEM_ID = '625919'
AND X.INCURRED_BY_PERSON_ID = EMP.PERSON_ID(+)
AND ( TRUNC (EI.EXPENDITURE_ITEM_DATE) BETWEEN EMP.EFFECTIVE_START_DATE
AND EMP.EFFECTIVE_END_DATE
OR NVL (X.INCURRED_BY_PERSON_ID, 0) = 0)