ECC Projects, Projects Costing: Transactions, SQL2

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 -3 and 3 then 'Y' else 'N' end display_totexp_chart,
	        case when e.bill_trans_rev_amount is not null and e.revenue_distributed_flag = 'N'
	        then PA_ECC_BILL_LOAD_PKG.get_rev_amount('EV', e.event_id) end evt_pfc_rev_derived_amt,
            hou.language,
			e.set_of_books_id,
			spi.start_date gl_period_start_date
        FROM
            sq_bill_proj                   p,
            pa_tasks                       t,
            hr_all_organization_units_tl   hou,
            sq_events                      e,
            pa_event_types                 et,
            sq_event_rdl                   ser,
            sq_inv_amount                  sia,
			sq_period_info                 spi
        WHERE
            hou.organization_id = e.organization_id
            AND p.project_id = e.project_id
            AND e.task_id = t.task_id
            AND e.event_type = et.event_type
            AND e.project_id = ser.project_id (+)
            AND e.task_id = ser.task_id (+)
            AND e.event_num = ser.event_num (+)
            /* --AND ser.task_id (+) IS NOT NULL */
            AND e.task_id IS NOT NULL
            AND e.project_id = sia.project_id (+)
            AND e.event_num = sia.event_num (+)
            AND e.task_id = sia.event_task_id (+)
			AND e.set_of_books_id = spi.set_of_books_id (+)
			AND e.completion_date  between spi.start_date (+) and spi.end_date (+)
            /* --AND sia.event_task_id IS NOT NULL */
        UNION ALL
        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,
            NULL,
            NULL,
			NULL top_task_id,
			NULL top_task_name, /* -- Top task Bill events can be created at Proj/TopTask only */
			NULL 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,
			0 funding_task_id,
			NULL funding_task_name,
			NULL funding_task_number,
	        case when e.billed_flag = 'Y' THEN sia.projfunc_bill_amount
	             when e.billed_flag = 'N' AND e.bill_trans_bill_amount is not null THEN PA_ECC_BILL_LOAD_PKG.get_bill_amount('EV',e.event_id)
            end billdb_pfc_bill_amount,
			case when 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 -3 and 3 then 'Y' else 'N' end display_totexp_chart,
	        case when e.bill_trans_rev_amount is not null and e.revenue_distributed_flag = 'N'
	        then PA_ECC_BILL_LOAD_PKG.get_rev_amount('EV', e.event_id) end evt_pfc_rev_derived_amt,
            hou.language,
			e.set_of_books_id,
			spi.start_date gl_period_start_date
        FROM
            sq_bill_proj                   p,
            hr_all_organization_units_tl   hou,
            sq_events                      e,
            pa_event_types                 et,
            sq_event_rdl                   ser,
            sq_inv_amount                  sia,
			sq_period_info                 spi
        WHERE
            hou.organization_id = e.organization_id
            AND p.project_id = e.project_id
            AND e.event_type = et.event_type
            AND e.project_id = ser.project_id (+)
            AND e.event_num = ser.event_num (+)
            AND ser.task_id IS NULL
            AND e.task_id IS NULL
            AND e.event_num = sia.event_num (+)
            AND e.project_id = sia.project_id (+)
            AND sia.event_task_id IS NULL
			AND e.set_of_books_id = spi.set_of_books_id (+)
			AND e.completion_date  between spi.start_date (+) and spi.end_date (+)
    ) pevent, pa_events pevt_outer
 
WHERE 1 = 1
AND   pevent.event_id = pevt_outer.event_id
AND   pevt_outer.last_update_date >=  to_date('12-DEC-21')
 
)
PIVOT
(
MAX(event_organization) AS event_organization,
MAX(bill_hold) AS bill_hold,
MAX(Revenue_Distributed_flag) AS Revenue_Distributed_flag,
MAX(invoice_dist_rejection) AS invoice_dist_rejection,
MAX(revenue_dist_rejection) AS revenue_dist_rejection,
MAX(adjust_revenue_event) AS adjust_revenue_event,
MAX(revenue_hold) AS revenue_hold,
MAX(billed) AS billed,
MAX(wip_status) AS wip_status
FOR language IN ( 'US' "US" ))
) x
where
2=2