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
,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
Good Blog, Thanks for sharing this informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp a great deal more around this condition. and I could assume you are an expert on this subject. Same as your blog i found another one Oracle Fusion Cloud Technical .Actually I was looking for the same information on internet for Oracle Fusion Cloud Technical and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.
ReplyDelete