ECC Projects, Projects Costing: Transactions, SQL1

Description
Columns: Record Type, Ecc Spec Id, Expenditure Item Id, Expenditure Id, Project Id, Project Name, Project Number, Project Type Class Code, Project Manager, Project Start Date ...
Imported from ECC
select * from (select
record_type,
ecc_spec_id,
tmp.expenditure_item_id,
tmp.expenditure_id,
tmp.project_id,
project_name,
project_number,
user_project_type,
project_type_class_code,
tmp.project_type,
project_manager,
project_start_date,
project_completion_date,
project_organization,
project_status_name,
project_status,
project_budget,
task_plan_capitalizable,
task_non_plan_capitalize,
proj_plan_capitalize,
proj_non_plan_capitalize,
tmp.proj_capitalizable,
actual_capitalizable,
actual_non_capitalizable,
actual_yet_to_capitalizable,
project_revenue_budget,
proj_grouping_method,
proj_grp_supplier_invoice,
proj_asset_cost_alloc_mhd,
proj_enbl_burden_cost_acc,
current_pa_period,
current_gl_period,
current_reporting_pa_period,
tmp.projfunc_currency_code,
tmp.number_of_assets,
tmp.proj_capitalized_cost,
(proj_capitalizable-proj_capitalized_cost)proj_cip_cost,
tmp.task_id,
tmp.task_number,
tmp.task_name,
tmp.top_task_id,
tmp.top_task_name,
tmp.top_task_number,
tmp.task_start_date,
tmp.task_finish_date,
tmp.task_manager,
tmp.task_organization,
tmp.task_burden_schedule,
tmp.task_billable_flag,
tmp.task_work_type,
tmp.expenditure_item_date,
tmp.expenditure_week_ending_date,
tmp.expenditure_type,
tmp.expenditure_category,
tmp.expenditure_type_class,
tmp.incurred_by_person_id,
tmp.employee_name,
tmp.employee_number,
tmp.location_code,
tmp.job_id,
tmp.job_name,
tmp.incurred_by_organization_id,
tmp.override_to_organization_id,
tmp.expenditure_organization_id,
tmp.expenditure_org_name,
tmp.non_labor_resource,
tmp.system_linkage_function,
tmp.transaction_source_code,
nvl (tmp.transaction_source,'Projects') transaction_source,
tmp.orig_transaction_ref,
tmp.expenditure_group,
tmp.ei_pend_burden_dist,
decode(tmp.ei_pend_burden_dist, 'Y', 1, 0) ei_pend_burden_dist_count,
tmp.ei_group_unreleased,
decode(tmp.ei_group_unreleased, 'Y', 1, 0) ei_group_unreleased_count,
tmp.ei_cost_exception,
decode(tmp.ei_cost_exception, 'Y', 1, 0) ei_cost_exception_count,
tmp.ei_uncosted,
decode(tmp.ei_uncosted, 'Y', 1, 0) ei_uncosted_count,
tmp.ei_costed,
decode(tmp.ei_costed, 'Y', 1, 0) ei_costed_count,
tmp.ei_costed_value,
tmp.quantity,
tmp.burden_cost,
tmp.ei_labor_cost,
tmp.ei_non_labor_cost,
tmp.unit_of_measure,
tmp.unit_of_measure_m,
tmp.raw_cost,
tmp.raw_cost_rate,
tmp.cost_distributed_flag,
tmp.cost_distributed,
tmp.cost_dist_rejection_code,
tmp.revenue_distributed,
tmp.revenue_distributed_flag,
tmp.billed,
tmp.billable_flag,
tmp.billable,
tmp.bill_hold_flag,
tmp.bill_hold,
tmp.billed_cost,
tmp.billable_unbilled_cost,
tmp.billable_onhold_cost,
tmp.bill_billable_cost,
tmp.bill_nonbilable_flag,
tmp.bill_nonbilable_cost,
tmp.bill_billhold_cost,
tmp.bill_billed_flag,
tmp.bill_billed_cost,
tmp.bill_not_billed_flag,
tmp.bill_not_billed_cost,
tmp.ei_work_burden_cost,
tmp.burdened_cost,
tmp.burdened_cost_rate,
tmp.denom_currency_code,
tmp.denom_raw_cost,
tmp.denom_burdened_cost,
tmp.acct_currency_code,
tmp.acct_rate_type,
tmp.acct_rate_date,
tmp.acct_exchange_rate,
tmp.acct_raw_cost,
tmp.acct_burdened_cost,
cip_cost,
tmp.project_currency_code,
tmp.project_raw_cost,
tmp.project_burdened_cost,
tmp.cost_bur_distributed_flag,
tmp.capitalizable_flag,
tmp.capitalizable,
tmp.asset_line_generated,
(decode(project_type_class_code,'CAPITAL',decode(capitalizable_flag,'N',tmp.cdl_amount,0),0))non_capitalizable_cost,
(decode(project_type_class_code,'CAPITAL',decode(capitalizable_flag,'Y',tmp.cdl_amount,0),0))capitalizable_cost,
(decode(project_type_class_code,'CAPITAL',decode(capitalized_flag,'Y',tmp.cdl_amount,0),0))capitalized_cost,
tmp.capitalized_flag,
tmp.capitalized,
tmp.capitalizable_cap_hold_flag,
tmp.capitalizable_cap_hold,
tmp.adjusted_expenditure_item_id,
tmp.net_zero_adjustment_flag,
tmp.net_zero_adjustment,
tmp.transferred_from_exp_item_id,
tmp.transferred_item_flag,
tmp.cc_cross_charge_code,
tmp.cc_cross_charge_type,
tmp.cc_bl_distributed_code,
tmp.org_id,
tmp.vendor_id,
tmp.vendor_name,
tmp.document_header_id,
tmp.document_distribution_id,
tmp.document_line_number,
tmp.document_payment_id,
tmp.document_type,
tmp.document_distribution_type,
tmp.Operating_Unit,
tmp.capital_cost_code_amt,
tmp.line_num,
tmp.line_type_code,
tmp.line_type,
tmp.reversed_flag,
tmp.transfer_status_code,
tmp.pa_date,
tmp.gl_date,
tmp.recvr_pa_date,
tmp.recvr_gl_date,
tmp.pa_period_name,
tmp.gl_period_name,
tmp.recvr_pa_period_name,
tmp.recvr_gl_period_name,
tmp.acct_event_id,
tmp.amount,
tmp.cdl_burdened_cost,
tmp.cdl_amount,
tmp.cdl_acct_status,
tmp.bl_acct_status,
tmp.exp_group_status_code,
tmp.exp_group_status,
tmp.expenditure_status,
tmp.precosting_status_code,
tmp.precosting_status,
asset_name,
tmp.work_type,
po_number,
po_line_number,
receipt_number,
receipt_line_number,
invoice_no,
invoice_line_number,
po_distribution_line_number,
invoice_distribution_line_no,
debit_account,
credit_account,
accounting_source,
transfer_status,
tmp.provider_le_name,
tmp.receiver_le_name,
tmp.receiver_operating_unit,
tmp.transfer_price,
tmp.labot_tp_sch_name,
tmp.non_labor_tp_sch_name,
accounting_status_code,
project_sla_acc_status,
sla_gl_acc_status,
pre_accounting_status,
tmp.project_exchange_rate,
tmp.project_rate_date,
tmp.project_rate_type,
tmp.revenue_accrued,
tmp.burden_schedule,
tmp.transfer_rejection_reason,
tmp.cc_rejection_reason,
alert_type,
alert_text,
cost_identifier,
unaccounted,
(decode(tmp.unaccounted,'Unaccounted','Y',null))ei_unaccounted,
(decode(tmp.unaccounted,'Unaccounted',1,0))ei_unaccounted_count,
(decode(tmp.unaccounted,'Unaccounted',tmp.raw_cost,null))ei_unaccounted_cost,
accounting_exception,
project_accounted,
sla_accounted,
gl_accounted,
(decode(nvl(tmp.project_accounted,'N'),'Y',tmp.amount,0))project_accounted_cost,
(decode(nvl(tmp.sla_accounted,'N'),'Y',tmp.amount,0))sla_accounted_cost,
(decode(nvl(tmp.gl_accounted,'N'),'Y',tmp.amount,0))gl_accounted_cost,
ei_account_exception,
(decode(nvl(tmp.ei_account_exception,'N'),'Y',tmp.amount,0))ei_account_exception_cost,
ei_accounted,
(decode(nvl(tmp.ei_accounted,'N'),'Y',tmp.amount,0))ei_accounted_cost,
sla_acct_code,
language,
NVL(tmp.sla_gl_acc_status,tmp.project_sla_acc_status) exp_accounting_stage,
( SELECT
nvl( pa_utils.get_lookup_values ( 'PA_EI_ACCOUNTING_STATUS',(CASE
WHEN(xea.gl_transfer_status_code = 'Y'
OR nvl(tmp.historical_flag, 'Y') = 'Y') THEN 'GL'
WHEN xea.gl_transfer_status_code <> 'Y'
AND pa_utils.iseifinalaccounted(cdl.expenditure_item_id, cdl.line_num) = 'Y' THEN 'PGL'
END), tmp.language),
pa_utils.get_lookup_values('PA_EI_ACCOUNTING_STATUS',(CASE
WHEN  DECODE(cdl.transfer_status_code, 'X', 'PSLA', 'R', 'PSLA', 'P', 'PSLA', 'A', DECODE(pa_utils.iseifinalaccounted(cdl.expenditure_item_id, cdl.line_num), 'N', 'PSLA', 'SLA')) = 'PSLA'
THEN 'PSLA'
WHEN cdl.transfer_status_code NOT IN( 'V', 'G') THEN 'SLA'
END), tmp.language)
)
FROM
(
SELECT
        cd.*
    FROM
        (
            SELECT
                c.expenditure_item_id,
                c.transfer_status_code,
                c.acct_event_id,
                c.line_num
            FROM
                pa_cost_dist_lines_v c
            WHERE
                c.line_type IN (
                    'D'
                )
                AND c.creation_date >= '22-APR-19'
				and (c.expenditure_item_id, c.line_num) in
				(select expenditure_item_id, max(line_num)
				from  pa_cost_dist_lines_v b
				where creation_date >='22-APR-19'
				and line_type = 'D'
				group by b.expenditure_item_id
				)
        ) cd
) cdl,
xla_ae_headers xea
WHERE
tmp.cost_distributed_flag = 'Y'
AND cdl.expenditure_item_id = tmp.expenditure_item_id
and cdl.acct_event_id is not null
AND xea.balance_type_code (+) = 'A'
AND cdl.acct_event_id = xea.event_id(+)
AND tmp.set_of_books_id = xea.ledger_id(+)
) acct_status_tot_bur,
decode(project_type_class_code, 'CAPITAL',
   (select case when cdl.transfer_status_code IN('P', 'A', 'V', 'T', 'R', 'G', 'B')
    AND(cdl.line_type = tmp.derive_tot_burden_flag
        OR cdl.line_type = DECODE(tmp.derive_tot_burden_flag, 'R', 'I', tmp.derive_tot_burden_flag)
       )
    AND( (cdl.line_type = 'I'
        AND cdl.transfer_status_code = 'G')
       OR cdl.line_type IN('R', 'D')
        AND cdl.transfer_status_code IN('P', 'V', 'A', 'B')
       )
    AND cdl.billable_flag = 'Y'
    and pa_utils.iseifinalaccounted(tmp.expenditure_item_id, cdl.line_num) = 'N'
   then decode(tmp.capital_cost_type_code,'R',nvl(cdl.amount, 0),'B',nvl(cdl.burdened_cost, cdl.amount),nvl(cdl.amount, 0))
   else 0
   end
   from
           (
               SELECT
                   cd.*
               FROM
                   (
                       SELECT
                           c.expenditure_item_id,
                           c.transfer_status_code,
                           c.acct_event_id,
                           c.line_num,
                           c.amount,
                           c.burdened_cost,
                           c.billable_flag,
                           c.line_type
                       FROM
                           pa_cost_dist_lines_v c
                       WHERE c.creation_date >= '22-APR-19'
                           AND ( c.expenditure_item_id,
                                 c.line_num ) IN (
                               SELECT
                                   expenditure_item_id,
                                   MAX(line_num)
                               FROM
                                   pa_cost_dist_lines_v b
                               WHERE
                                   creation_date >= '22-APR-19'
                                   AND line_type in ('R','D')
                               GROUP BY
                                   b.expenditure_item_id, b.line_type
                           )
                   ) cd
           ) cdl where cdl.line_type = tmp.DERIVE_TOT_BURDEN_FLAG and cdl.expenditure_item_id = tmp.expenditure_item_id)
           , 0) pending_final_accounting,
decode(project_type_class_code, 'CAPITAL',
   (select case when cdl.transfer_status_code IN('P', 'A', 'V', 'T', 'R', 'G', 'B')
    AND(cdl.line_type = tmp.derive_tot_burden_flag
        OR cdl.line_type = DECODE(tmp.derive_tot_burden_flag, 'R', 'I', tmp.derive_tot_burden_flag)
       )
    AND( (cdl.line_type = 'I'
        AND cdl.transfer_status_code = 'G')
       OR cdl.line_type IN('R', 'D')
        AND cdl.transfer_status_code IN('P', 'V', 'A', 'B')
       )
    AND cdl.billable_flag = 'Y'
	AND tmp.capitalized_flag = 'N'
    and pa_utils.iseifinalaccounted(tmp.expenditure_item_id, cdl.line_num) = 'Y'
   then decode(tmp.capital_cost_type_code,'R',nvl(cdl.amount, 0),'B',nvl(cdl.burdened_cost, cdl.amount),nvl(cdl.amount, 0))
   else 0
   end
   from
           (
               SELECT
                   cd.*
               FROM
                   (
                       SELECT
                           c.expenditure_item_id,
                           c.transfer_status_code,
                           c.acct_event_id,
                           c.line_num,
                           c.amount,
                           c.burdened_cost,
                           c.billable_flag,
                           c.line_type
                       FROM
                           pa_cost_dist_lines_v c
                       WHERE c.creation_date >= '22-APR-19'
                           AND ( c.expenditure_item_id,
                                 c.line_num ) IN (
                               SELECT
                                   expenditure_item_id,
                                   MAX(line_num)
                               FROM
                                   pa_cost_dist_lines_v b
                               WHERE
                                   creation_date >= '22-APR-19'
                                   AND line_type in ('R','D')
                               GROUP BY
                                   b.expenditure_item_id, b.line_type
                           )
                   ) cd
           ) cdl where cdl.line_type = tmp.DERIVE_TOT_BURDEN_FLAG and cdl.expenditure_item_id = tmp.expenditure_item_id)
           , 0) proj_cost_pend_al_gen,
tmp.asset_asgn_exists_code,
DECODE(project_type_class_code, 'CAPITAL', pa_utils.get_lookup_values('PA_ASSET_ASG_LEVEL', tmp.asset_asgn_exists_code, tmp.language)) asset_assignment_exists,
tmp.grouped_task_id,
DECODE(project_type_class_code, 'CAPITAL', decode(tmp.grouped_task_id,-9999,NULL,(select task_number from pa_tasks where task_id = tmp.grouped_task_id))) grouped_task_number,
DECODE(project_type_class_code, 'CAPITAL', decode(tmp.grouped_task_id,-9999,NULL,(select task_name from pa_tasks where task_id = tmp.grouped_task_id))) grouped_task_name , dfv2.* 
from
(
SELECT DISTINCT
'PROJ_DS_EICDL' record_type,
'PROJ-'
|| TO_CHAR(ei.expenditure_item_id) ecc_spec_id,
ei.expenditure_item_id,
ei.expenditure_id,
ei.project_id,
ei.project_name,
ei.project_number,
ei.project_type user_project_type,
ei.project_type_class_code ,
ei.CAPITAL_COST_TYPE_CODE,
pa_utils.get_lookup_values('PROJECT TYPE CLASS', ei.project_type_class_code, ei.language) project_type,
ei.DERIVE_TOT_BURDEN_FLAG,
ei.project_manager,
ei.project_start_date,
ei.project_completion_date,
ei.project_organization,
ei.task_burden_schedule,
pa_utils.get_lookup_values('YES_NO',ei.task_billable_flag,ei.language) task_billable_flag,
ei.task_work_type,
ei.project_status_name,
ei.project_status,
(SELECT
        nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0)
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt
    WHERE
        pbv.project_id = ei.project_id
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
) project_budget,
(SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt
    WHERE
        pbv.project_id = ei.project_id
		AND pra.task_id = ei.task_id
		AND ei.task_billable_flag = 'Y'
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
) task_plan_capitalizable,
(SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt
    WHERE
        pbv.project_id = ei.project_id
		AND pra.task_id = ei.task_id
		AND ei.task_billable_flag = 'N'
        AND pbv.budget_type_code = pbt.budget_type_code
        AND pbt.budget_amount_code = 'C'
        AND pbv.budget_status_code = 'B'
        AND pbv.current_flag = 'Y'
        AND pra.budget_version_id = pbv.budget_version_id
        AND pra.project_id = pbv.project_id
        AND pbl.budget_version_id = pra.budget_version_id
        AND pbl.resource_assignment_id = pra.resource_assignment_id
) task_non_plan_capitalize,
(
(SELECT
        (decode(project_type_class_code,'CAPITAL',nvl(SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(pbl.raw_cost, 0), nvl(pbl.burdened_cost, 0
        ))), 0),0))
    FROM
        pa_budget_versions pbv,
        pa_budget_lines p