ECC Projects, Projects Costing: Transactions, SQL2
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Data set for information about expenditure items
Dataset Key: pa-ds-cost-eicdl
Query Procedure: PA_ECC_COST_PROJ_PKG.LOAD_FULL_EICDL_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
Description: Data set for information about expenditure items
Dataset Key: pa-ds-cost-eicdl
Query Procedure: PA_ECC_COST_PROJ_PKG.LOAD_FULL_EICDL_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
Run
ECC Projects, Projects Costing: Transactions, SQL2 and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( WITH sq_bill_proj AS ( SELECT ppa.project_id, ppa.name project_name, ppa.segment1 project_number, ppa.org_id, ppa.project_type, nvl(ppa.project_level_funding_flag, 'N') project_level_funding_flag, ppa.multi_currency_billing_flag, ppta.project_type_class_code, pia.set_of_books_id FROM pa_projects_all ppa, pa_project_statuses ps, pa_project_types_all ppta, pa_budget_versions pbv, pa_budget_types pbt, pa_budget_entry_methods pabem, pa_implementations_all pia WHERE pia.org_id = ppa.org_id AND ppta.project_type = ppa.project_type AND ppta.org_id = ppa.org_id AND ppta.direct_flag = 'Y' /* CONTRACT PROJECT */ AND ppta.project_type_class_code = 'CONTRACT' AND nvl(ppta.cc_prvdr_flag, 'N') <> 'Y' /* NOT TO INCLUDE INTERCOMPANY PROJECTS */ AND ppa.project_status_code = ps.project_status_code AND ps.project_system_status_code NOT IN ('CLOSED','PENDING_CLOSE','PENDING_PURGE','PARTIALLY_PURGED','PURGED') AND ppa.template_flag = 'N' AND ppa.revenue_accrual_method IN ('WORK','EVENT') AND ppa.invoice_method IN ('WORK','EVENT') AND pbv.project_id = ppa.project_id AND pbv.budget_status_code = 'B' AND pbv.current_flag = 'Y' AND pbv.budget_type_code = 'AR' /* APPROVED REVENUE BUDGET HARD CHECK? */ AND pbv.budget_type_code = pbt.budget_type_code AND pabem.budget_entry_method_code = pbv.budget_entry_method_code AND PABEM.TIME_PHASED_TYPE_CODE = 'G' /* BY GL PERIOD */ AND pabem.entry_level_code IN ('P','T') ), sq_events AS ( SELECT pvt.*, sbp.set_of_books_id FROM pa_events pvt, sq_bill_proj sbp WHERE pvt.project_id = sbp.project_id AND pvt.last_update_date >= to_date('12-DEC-21') ), sq_event_rdl AS ( SELECT proj.org_id, proj.project_id, pcera.task_id, pcera.event_num, pcera.projfunc_currency_code, pcera.funding_currency_code, pcera.revtrans_currency_code, SUM(pcera.project_revenue_amount) project_revenue_amount, SUM(pcera.projfunc_revenue_amount) projfunc_revenue_amount, SUM(pcera.funding_revenue_amount) funding_revenue_amount, SUM(pcera.revtrans_amount) revtrans_amount FROM pa_cust_event_rdl_all pcera, sq_events psq_evt, sq_bill_proj proj WHERE 1 = 1 AND proj.project_id = psq_evt.project_id AND psq_evt.project_id = pcera.project_id AND nvl(psq_evt.task_id, -99) = nvl(pcera.task_id, -99) AND psq_evt.event_num = pcera.event_num GROUP BY proj.org_id, proj.project_id, pcera.task_id, pcera.event_num, pcera.projfunc_currency_code, pcera.funding_currency_code, pcera.revtrans_currency_code ), sq_inv_amount AS ( SELECT pdi.project_id, /* --pdi.draft_invoice_num, */ pdiitm.event_task_id, pdiitm.event_num, pdiitm.funding_currency_code, pdiitm.project_currency_code, pdiitm.invproc_currency_code, SUM(pdiitm.amount) amount, SUM(pdiitm.inv_amount) inv_amount, SUM(pdiitm.acct_amount) acct_amount, SUM(pdiitm.funding_bill_amount) funding_bill_amount, SUM(pdiitm.project_bill_amount) project_bill_amount, SUM(pdiitm.projfunc_bill_amount) projfunc_bill_amount FROM pa_draft_invoices_all pdi, pa_draft_invoice_items pdiitm, sq_events psqevt WHERE 1 = 1 AND psqevt.project_id = pdi.project_id AND pdi.project_id = pdiitm.project_id AND pdi.draft_invoice_num = pdiitm.draft_invoice_num AND nvl(psqevt.task_id, - 99) = nvl(pdiitm.event_task_id, - 99) AND psqevt.event_num = pdiitm.event_num GROUP BY pdi.project_id, /* --pdi.draft_invoice_num, */ pdiitm.event_task_id, pdiitm.event_num, pdiitm.funding_currency_code, pdiitm.project_currency_code, pdiitm.invproc_currency_code ), sq_period_info AS ( select org_id, set_of_books_id, period_year||'-'||lpad(period_num, 2, 0) bill_period_name, period_name, closing_status, start_date, end_date, period_year, period_num, quarter_num, rnum, currper, (rnum-perval) display_ctr from ( select org_id, set_of_books_id, period_name, closing_status, start_date, end_date, period_year, period_num, quarter_num, currper, rnum, sum((case when currper = 'CR' then rnum else 0 end)) over (partition by org_id, set_of_books_id) perval from ( select pia.org_id, pia.set_of_books_id, gps.period_name, gps.closing_status, gps.start_date, gps.end_date, gps.period_year, gps.period_num, gps.quarter_num ,case when sysdate between start_date and end_date then 'CR' else 'NA' end currper ,row_number() over(partition by pia.org_id, pia.set_of_books_id order by gps.period_year desc, gps.period_num desc) rnum from gl_period_statuses gps, pa_implementations_all pia where gps.set_of_books_id = pia.set_of_books_id and gps.adjustment_period_flag = 'N' and gps.application_id = 101 ) ) /* where rnum-perval between -3 and 3 */ /* select projper.org_id, projper.set_of_books_id, eventsper.event_id, gps.period_name, gps.closing_status, gps.start_date, gps.end_date, gps.period_year, gps.period_num, gps.quarter_num from sq_bill_proj projper, sq_events eventsper, gl_period_statuses gps where gps.set_of_books_id = projper.set_of_books_id and gps.adjustment_period_flag = 'N' and gps.application_id = 101 and eventsper.project_id = projper.project_id and eventsper.completion_date between gps.start_date and gps.end_date */ ) select * from ( select pevent.*, -9999 expenditure_item_id, case when (pevent.revenue_distributed = 'Y' or pevent.billed_flag = 'Y') then 'EV-'||pevent.event_id else '-9999' end ei_ev_reference, 'Y' BILLPROJ_DISPLAY_FLAG, case /* when pevent.revenue_distributed = 'N' and pevent.rev_dist_rejection_code IS NULL then 'GENREV' when pevent.revenue_distributed = 'N' and pevent.rev_dist_rejection_code IS NOT NULL then 'REVREJ' */ when pevent.billed_flag = 'N' and pevent.inv_gen_rejection_code IS NULL THEN 'GENINV' when pevent.billed_flag = 'N' and pevent.inv_gen_rejection_code <> 'N' and pevent.inv_gen_rejection_code IS NOT NULL THEN 'INVREJ' when pevent.billed_flag = 'Y' then 'BILLED' else null end wip_status_code, pa_utils.get_lookup_values('PA_BILL_ECC_WIP_STATUS', case /* when pevent.revenue_distributed = 'N' and pevent.rev_dist_rejection_code IS NULL then 'GENREV' when pevent.revenue_distributed = 'N' and pevent.rev_dist_rejection_code IS NOT NULL then 'REVREJ' */ when pevent.billed_flag = 'N' and pevent.inv_gen_rejection_code IS NULL THEN 'GENINV' when pevent.billed_flag = 'N' and pevent.inv_gen_rejection_code <> 'N' and pevent.inv_gen_rejection_code IS NOT NULL THEN 'INVREJ' when pevent.billed_flag = 'Y' then 'BILLED' else null end, pevent.language) wip_status, case when pevent.billed_flag = 'Y' and pevent.bill_trans_bill_amount is not null then pevent.projfunc_bill_amount else 0 end billable_billed_pfc_amt, case when pevent.billed_flag = 'N' and pevent.billable_flag = 'Y' then pevent.billdb_pfc_bill_amount else 0 end billable_nonbilled_pfc_amt, /* case when pevent.bill_trans_bill_amount is not null THEN pevent.projfunc_bill_amount else 0 end billable_pfc_amt, */ case when pevent.billable_flag = 'Y' then case when pevent.billed_flag = 'Y' then pevent.projfunc_bill_amount when pevent.billed_flag = 'N' then pevent.billdb_pfc_bill_amount else null end else null end billable_pfc_amt, case when pevent.projfunc_bill_amount is not null then 'N' else 'Y' end derived_bill_amt_flag, 0 ei_revenue_hold_amt, 0 ei_rev_dist_rej_amt, 0 ei_inv_gen_rej_amt, 0 ei_pend_gen_rev_amt, 0 ei_pend_gen_inv_amt, 0 ei_bill_hold_amt, case when pevent.revenue_hold_flag <> 'N' then pevent.evt_pfc_rev_derived_amt else 0 end evt_revenue_hold_amt, case when pevent.revenue_distributed = 'N' and pevent.rev_dist_rejection_code is not null and pevent.revenue_hold_flag = 'N' then pevent.evt_pfc_rev_derived_amt else 0 end evt_rev_dist_rej_amt, case when pevent.billed_flag = 'N' and pevent.inv_gen_rejection_code is not null and pevent.inv_gen_rejection_code <> 'N' and pevent.bill_hold_flag = 'N' then pevent.billdb_pfc_bill_amount else 0 end evt_inv_gen_rej_amt, case when pevent.rev_dist_rejection_code is NULL and pevent.revenue_distributed = 'N' and pevent.revenue_hold_flag = 'N' then pevent.evt_pfc_rev_derived_amt else 0 end evt_pend_gen_rev_amt, case when pevent.inv_gen_rejection_code is NULL and pevent.billed_flag = 'N' and pevent.billable_flag = 'Y' and pevent.bill_trans_bill_amount is not null AND pevent.bill_hold_flag = 'N' then pevent.billdb_pfc_bill_amount else 0 end evt_pend_gen_inv_amt, case when pevent.bill_hold_flag <> 'N' then pevent.billdb_pfc_bill_amount else 0 end evt_bill_hold_amt, pevent.project_id||':'||pevent.funding_task_id||':'|| case when (pevent.revenue_distributed = 'Y' or pevent.billed_flag = 'Y') then 'EV-'||pevent.event_id else '-9999' end DS_ASSOC_PRJ_TSK_EE_ATTR, pevent.project_id||':'||pevent.funding_task_id||':'|| case when (pevent.revenue_distributed = 'Y' or pevent.billed_flag = 'Y') then 'EV-'||pevent.event_id else '-9999' end||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_EE_PER_ATTR, pevent.project_id||':'||pevent.funding_task_id||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_PER_ATTR, case when pevent.billable_flag = 'Y' and pevent.billed_flag = 'N' and nvl(pevent.bill_hold_flag, 'N') = 'N' and pevent.bill_trans_bill_amount is not null then pevent.billdb_pfc_bill_amount else 0 end evt_ready_to_bill_amt, pa_ecc_bill_load_pkg.get_three_quarter_flag(pevent.set_of_books_id, pevent.bill_gl_period_name) period_disp_flag, case when pevent.billable_flag = 'Y' and pevent.billed_flag = 'N' and nvl(pevent.bill_hold_flag, 'N') = 'N' and pevent.bill_trans_bill_amount is not null then 'Y' else 'N' end ready_to_bill_flag from ( SELECT 'PROJ_DS_EVT' record_type, 'PROJ-' || to_char(event_id) ecc_spec_id, e.event_id, e.project_id, p.project_number, p.project_name, p.project_type_class_code, e.task_id, t.task_number, t.task_name, t.top_task_id top_task_id, t.task_name top_task_name, /* -- Top task Bill events can be created at Proj/TopTask only */ t.task_number top_task_number, e.agreement_id, CASE WHEN e.agreement_id IS NULL THEN NULL ELSE ( SELECT agreement_num FROM pa_agreements_all pag WHERE pag.agreement_id = e.agreement_id ) END agreement_num, e.event_num, e.event_type, et.event_type_classification, e.completion_date, e.description, /* e.bill_amount, e.revenue_amount, */ e.organization_id, hou.name event_organization, e.bill_hold_flag, pa_utils.get_lookup_values('BILLING HOLD', e.bill_hold_flag, hou.language) bill_hold, e.revenue_distributed_flag revenue_distributed, pa_utils.get_lookup_values('YES_NO', e.revenue_distributed_flag, hou.language) revenue_distributed_flag, p.project_level_funding_flag, e.quantity_billed, e.uom_code, /* e.unit_price, e.last_update_date, e.last_updated_by, e.creation_date, e.created_by, e.last_update_login, */ e.bill_trans_currency_code event_currency, e.bill_trans_bill_amount, e.bill_trans_rev_amount, e.project_currency_code project_currency_code_evt, e.project_revenue_amount, e.projfunc_currency_code projfunc_currency_code, e.projfunc_revenue_amount, e.invproc_currency_code, e.revproc_currency_code revproc_currency_code, e.inv_gen_rejection_code, e.rev_dist_rejection_code, pa_utils.get_lookup_values('REVENUE DIST REJECTION CODE', e.inv_gen_rejection_code, hou.language) invoice_dist_rejection, pa_utils.get_lookup_values('REVENUE DIST REJECTION CODE', e.rev_dist_rejection_code, hou.language) revenue_dist_rejection, e.adjusting_revenue_flag, pa_utils.get_lookup_values('YES_NO', e.adjusting_revenue_flag, hou.language) adjust_revenue_event, /* e.zero_revenue_amount_flag, */ e.revenue_hold_flag, pa_utils.get_lookup_values('YES_NO', e.revenue_hold_flag, hou.language) revenue_hold, e.billed_flag, pa_utils.get_lookup_values('YES_NO', e.billed_flag, hou.language) billed, e.bill_group, ser.funding_currency_code funding_currency_code_rev, ser.funding_revenue_amount, ser.revtrans_amount rev_trans_amount, sia.funding_bill_amount, sia.funding_currency_code funding_currency_code_bill, sia.project_bill_amount, sia.projfunc_bill_amount, sia.amount inv_process_amount, case p.project_level_funding_flag when 'Y' then 0 else t.top_task_id end funding_task_id, case p.project_level_funding_flag when 'Y' then NULL else t.task_name end funding_task_name, case p.project_level_funding_flag when 'Y' then NULL else t.task_number end funding_task_number, case when e.billed_flag = 'Y' THEN sia.projfunc_bill_amount when e.billed_flag = 'N' AND nvl(t.billable_flag, 'N') = 'Y' and e.bill_trans_bill_amount is not null THEN PA_ECC_BILL_LOAD_PKG.get_bill_amount('EV',e.event_id) else null end billdb_pfc_bill_amount, case when nvl(t.billable_flag, 'N') = 'Y' and e.bill_trans_bill_amount is not null then 'Y' else 'N' end billable_flag, spi.bill_period_name bill_chart_period_name, spi.period_name bill_gl_period_name, case when spi.display_ctr between -3 and 0 then 'Y' else 'N' end display_planrev_chart, case when spi.display_ctr between |