Introduction
This Post illustrates the steps required to PA Invoice Detail in Fusion Application
Script to PA Invoice Detail Query in Fusion Application
SELECT PEI.expenditure_item_id
,PPA.segment1 project_number
,PPA.name project_name
,PPA.segment1||’-‘||pih.invoice_num invoice_number
,TO_CHAR(PIH.INVOICE_DATE, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) INVOICE_DATE
,(SELECT INV.invoice_num FROM ap_invoices_all INV
WHERE INV.invoice_id = PEI.original_header_id) expense_report_number
,PPN.full_name Consultant
,PT.task_number
,PT.task_name
,PET.expenditure_type_name
,TO_CHAR(PEI.expenditure_item_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) EXPENDITURE_ITEM_DATE
,DECODE(TO_CHAR(PEI.expenditure_item_date, ‘FmDay’), 5, TO_CHAR(PEI.expenditure_item_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’),
TO_CHAR(NEXT_DAY(PEI.expenditure_item_date, 6), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’)) Week_ending_date
,PBT.bill_rate
,SUM(PILD.invoice_curr_billed_amt) amount
,PEC.expenditure_comment
,PEI.quantity
,PIH.INVOICE_CURRENCY_CODE
FROM pjc_exp_items_all pei,
pjf_projects_all_vl ppa,
pjb_bill_trxs pbt,
pjb_inv_line_dists pild,
pjb_invoice_lines pil,
pjb_invoice_headers pih,
per_all_people_f ppf,
per_person_names_f ppn,
pjf_tasks_v pt,
pjf_exp_types_vl pet,
pjc_exp_comments pec
WHERE 1 = 1
AND pei.project_id = ppa.project_id
AND pbt.transaction_id = pei.expenditure_item_id
AND pild.bill_trx_id = pbt.bill_trx_id
AND pil.invoice_line_id = pild.invoice_line_id
AND pih.invoice_id = pil.invoice_id
AND pei.incurred_by_person_id = ppf.person_id(+)
AND ppf.person_id = ppn.person_id
AND ppn.name_type = ‘GLOBAL’
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date
AND ppn.effective_end_date
AND pei.task_id = pt.task_id
AND pei.expenditure_type_id = pet.expenditure_type_id
AND pei.expenditure_item_id = pec.expenditure_item_id(+)
AND (ppa.segment1 IN (:p_project_number) OR ‘ALL’ IN (:p_project_number))
GROUP BY pei.expenditure_item_id,
ppa.segment1,
ppa.NAME,
pih.invoice_num,
pih.invoice_date,
ppn.full_name,
pt.task_number,
pt.task_name,
pet.expenditure_type_name,
pei.expenditure_item_date,
pbt.bill_rate,
pec.expenditure_comment,
pei.original_header_id,
pei.quantity,
pih.invoice_currency_code
UNION
SELECT NULL expenditure_item_id
,PPA.segment1 project_number
,PPA.name project_name
,PPA.segment1||’-‘||pih.invoice_num invoice_number
–,TO_CHAR(PIH.bill_to_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) bill_through_date
,TO_CHAR(PIH.INVOICE_DATE, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) INVOICE_DATE
,NULL expense_report_number
,NULL Consultant
,PT.task_number
,PT.task_name
,PBE.EVENT_DESC expenditure_type_name
,TO_CHAR(PBE.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) EXPENDITURE_ITEM_DATE
,DECODE(TO_CHAR(PBE.completion_date, ‘FmDay’), 5, TO_CHAR(PBE.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’),
TO_CHAR(NEXT_DAY(PBE.completion_date, 6), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’)) Week_ending_date
,NULL bill_rate
–,SUM(PBE.bill_trns_amount) amount
,SUM(PBE.invoiced_amt) amount
,NULL expenditure_comment
,PBE.quantity_billed quantity
,PIH.INVOICE_CURRENCY_CODE
FROM pjb_inv_line_dists pild,
pjb_invoice_lines pil,
pjb_invoice_headers pih,
pjb_billing_events pbe,
pjf_projects_all_vl ppa,
pjf_tasks_v pt
WHERE 1 = 1
AND pil.invoice_line_id = pild.invoice_line_id
AND pih.invoice_id = pil.invoice_id
AND pild.bill_transaction_type_code = ‘EVT’
AND pild.transaction_id = pbe.event_id
AND pild.transaction_project_id = ppa.project_id
AND pbe.task_id = pt.task_id
AND (ppa.segment1 IN (:p_project_number) OR ‘ALL’ IN (:p_project_number))
GROUP BY ppa.segment1,
ppa.NAME,
pih.invoice_num,
pih.invoice_date,
pt.task_number,
pt.task_name,
pbe.event_desc,
pbe.completion_date,
pbe.quantity_billed,
pih.invoice_currency_code
ORDER BY 2, 1
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
