ECC Projects, Projects Costing: Transactions

➜ Back to Library
Description
Categories: Enterprise Command Center
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 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 
select
x.*
from
(
select * from (select /*+ push_pred(ei) */
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 cdl.is_ei_final_accounted = 'Y' THEN 'PGL' /* Bug 30749534 */
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(cdl.is_ei_final_accounted, 'N', 'PSLA', 'SLA')) = 'PSLA'
THEN 'PSLA'
WHEN cdl.transfer_status_code NOT IN( 'V', 'G') THEN 'SLA'
END), tmp.language)
)
FROM
/* Commented for Bug#30749534
(
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 >= '26-OCT-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 >='26-OCT-19'
				and line_type = 'D'
				group by b.expenditure_item_id
				)
        ) cd
) cdl,
*/
/* Added Bug#30749534 */
(
select /*+ index (pecca pa_ecc_cost_cdl_account_n2) */
pecca.expenditure_item_id,
pecca.transfer_status_code,
pecca.acct_event_id,
pecca.line_num,
pecca.is_ei_final_accounted,
pecca.project_id,
pecca.task_id
from pa_ecc_cost_cdl_account pecca
where pecca.line_type = 'D'
) 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 xea.application_id (+) = 275
AND cdl.acct_event_id = xea.event_id(+)
AND tmp.set_of_books_id = xea.ledger_id(+)
/* Added for Bug#30749534 */
AND cdl.project_id = tmp.project_id
AND cdl.task_id = tmp.task_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 >= '26-OCT-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 >= '26-OCT-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 >= '26-OCT-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 >= '26-OCT-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 /*+ push_pred(ei) */
'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 pbl,
        pa_resource_assignments pra,
        pa_budget_types pbt,
		pa_budget_entry_methods bem
    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 pbv.budget_entry_method_code = bem.budget_entry_method_code
        and bem.entry_level_code = 'P'
        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)
	+
(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,
		pa_budget_entry_methods bem,
        pa_tasks pt
    WHERE
        pbv.project_id = ei.project_id
		AND pra