ECC Projects, Projects Costing: Transactions, SQL1

➜ Back to Library
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
select
x.*
from
(
select * from (select /*+ push_pred(ei) */
record_type,
ecc_spec_id,
tmp.expenditure_item_id,'N' IS_WORKTYPE_BILLABLE_ENABLED
,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,
tmp.proj_non_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.project_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,
pa_utils.get_lookup_values('COST_TYPE_DESC', cost_identifier, tmp.language) cost_identifier_desc,
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 >= '12-DEC-21'
				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 >='12-DEC-21'
				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 >= '12-DEC-21'
                           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 >= '12-DEC-21'
                                   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 >= '12-DEC-21'
                           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 >= '12-DEC-21'
                                   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,
tmp.rate_source_id,  /* V6 */
tmp.discount_percentage,
tmp.rate_disc_reason_code,
pa_utils.get_lookup_values('RATE AND DISCOUNT REASON', tmp.rate_disc_reason_code, tmp.language) rate_discount_reason,
tmp.bill_markup_percentage,
tmp.bill_group,
tmp.projfunc_bill_amount,
tmp.bill_amount,
tmp.bill_rate,
tmp.revenue_hold_flag,
pa_utils.get_lookup_values('YES_NO', tmp.revenue_hold_flag, tmp.language) revenue_hold,
tmp.accrual_rate,
tmp.inventory_item_id,
tmp.inventory_item,
-9999 event_id,
case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y')
     then 'EI-'||tmp.expenditure_item_id else '-9999' end ei_ev_reference,
tmp.funding_task_id,
tmp.funding_task_name,
tmp.funding_task_number,
decode(tmp.billing_project_id, null, 'N', 'Y') billproj_display_flag,
bill_chart_period_name,
display_planrev_chart,
display_totexp_chart,
billed_flag,
case when tmp.project_type_class_code = 'CONTRACT' THEN
  case when tmp.billable_flag = 'N' then 'NOBILL'
      when tmp.cost_distributed_flag = 'N' THEN 'UCST'
     when tmp.cost_distributed_flag = 'Y' THEN
     case
       when tmp.rev_dist_rejection_code is NULL and tmp.revenue_distributed in ('N', 'P')
	        THEN 'GENREV'
	   when tmp.rev_dist_rejection_code is NOT NULL and tmp.revenue_distributed in ('N', 'P')
	        THEN 'REVREJ'
       when ((tmp.inv_gen_rejection_code is NULL or (tmp.inv_gen_rejection_code = 'N' and tmp.billed_flag = 'N'))
            and (tmp.revenue_distributed = 'Y' AND tmp.billable_flag = 'Y'))
			THEN 'GENINV'
	   when (tmp.inv_gen_rejection_code is NOT NULL and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y')
	        THEN 'INVREJ'
       when tmp.billable_flag = 'Y' AND tmp.INVOICE_METHOD = 'WORK' AND tmp.billed_flag = 'Y'
	        THEN 'BILLED'
	   else NULL
     end
  else NULL
  end
else NULL
end wip_status_code,
pa_utils.get_lookup_values('PA_BILL_ECC_WIP_STATUS',
		case when tmp.project_type_class_code = 'CONTRACT' THEN
		  case when tmp.billable_flag = 'N' then 'NOBILL'
		     when tmp.cost_distributed_flag = 'N' THEN 'UCST'
			 when tmp.cost_distributed_flag = 'Y' THEN
			 case
			   when tmp.rev_dist_rejection_code is NULL and tmp.revenue_distributed in ('N', 'P')
			        THEN 'GENREV'
			   when tmp.rev_dist_rejection_code is NOT NULL and tmp.revenue_distributed in ('N', 'P')
			        THEN 'REVREJ'
			   when ((tmp.inv_gen_rejection_code is NULL or (tmp.inv_gen_rejection_code = 'N' and tmp.billed_flag = 'N'))
			        and (tmp.revenue_distributed = 'Y' AND tmp.billable_flag = 'Y'))
					THEN 'GENINV'
			   when (tmp.inv_gen_rejection_code is NOT NULL and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y')
			        THEN 'INVREJ'
			   when tmp.billable_flag = 'Y' AND tmp.INVOICE_METHOD = 'WORK' AND tmp.billed_flag = 'Y'
			        THEN 'BILLED'
			   else NULL
			 end
		  else NULL
		  end
		else NULL
		end
 , tmp.language) wip_status,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.billed_flag = 'Y' and tmp.billable_flag = 'Y' THEN
     tmp.billdb_pfc_bill_amount else 0 end billable_billed_pfc_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.billed_flag = 'N' and tmp.billable_flag = 'Y' THEN
     tmp.billdb_pfc_bill_amount else 0 end billable_nonbilled_pfc_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y' THEN
      tmp.billdb_pfc_bill_amount else 0 end billable_pfc_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.projfunc_bill_amount is not null then 'N' else 'Y' end derived_bill_amt_flag,
tmp.billdb_pfc_bill_amount,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.revenue_hold_flag <> 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_revenue_hold_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.rev_dist_rejection_code is not null
     and tmp.revenue_distributed in ('N', 'P') and tmp.revenue_hold_flag = 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_rev_dist_rej_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.inv_gen_rejection_code is not null
     and tmp.inv_gen_rejection_code <> 'N' and tmp.revenue_distributed = 'Y' and tmp.bill_hold_flag = 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_inv_gen_rej_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.rev_dist_rejection_code is NULL
     and tmp.revenue_distributed in ('N', 'P') and tmp.revenue_hold_flag = 'N'
     then tmp.billdb_pfc_bill_amount else 0 end ei_pend_gen_rev_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.inv_gen_rejection_code is NULL and tmp.revenue_distributed = 'Y'
     and tmp.billable_flag = 'Y' and tmp.bill_hold_flag = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_pend_gen_inv_amt,
case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and nvl(tmp.bill_hold_flag, 'N') = 'Y' then tmp.billdb_pfc_bill_amount else 0 end ei_bill_hold_amt,
    0 evt_revenue_hold_amt,
	0 evt_rev_dist_rej_amt,
	0 evt_inv_gen_rej_amt,
	0 evt_pend_gen_rev_amt,
	0 evt_pend_gen_inv_amt,
	0 evt_bill_hold_amt,
	0 evt_pfc_rev_derived_amt,
     tmp.project_id||':'||tmp.funding_task_id||':'||
               case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y')
                then 'EI-'||tmp.expenditure_item_id else '-9999' end DS_ASSOC_PRJ_TSK_EE_ATTR,
     tmp.project_id||':'||tmp.funding_task_id||':'||
               case when tmp.project_type_class_code = 'CONTRACT' and (tmp.billed_flag = 'Y' or tmp.revenue_distributed = 'Y')
                then 'EI-'||tmp.expenditure_item_id else '-9999' end ||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_EE_PER_ATTR,
     tmp.project_id||':'||tmp.funding_task_id||':'||bill_gl_period_name DS_ASSOC_PRJ_TSK_PER_ATTR,
     case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and tmp.revenue_distributed <> 'N'
	 and nvl(tmp.bill_hold_flag, 'N') = 'N' and tmp.billed_flag <> 'Y' then tmp.billdb_pfc_bill_amount else 0 end ei_ready_to_bill_amt,
	 case when tmp.project_type_class_code = 'CONTRACT' and tmp.billable_flag = 'Y' and tmp.revenue_distributed = 'N'
	 and nvl(tmp.bill_hold_flag, 'N') = 'N' then tmp.billdb_pfc_bill_amount else 0 end ei_unaccrued_amt,
	 pa_ecc_bill_load_pkg.get_three_quarter_flag(tmp.set_of_books_id, tmp.bill_gl_period_name) period_disp_flag,
	 gl_period_start_date,
	 case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y'
	 and tmp.revenue_distributed <> 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' and tmp.billed_flag <> 'Y'
	 then 'Y' else 'N' end ready_to_bill_flag,
	 case when tmp.project_type_class_code = 'CONTRACT' and tmp.cost_distributed_flag = 'Y' and tmp.billable_flag = 'Y'
	 and tmp.revenue_distributed = 'N' and nvl(tmp.bill_hold_flag, 'N') = 'N' then 'Y' else 'N' end ei_unaccrued_flag
 
 , 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,
ei.project_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.task_id = pt.task_id
		AND pt.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 pbv.budget_entry_method_code = bem.budget_entry_method_code
        and bem.entry_level_code in ( 'M', 'L', 'T')
        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)
) proj_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,
        pa_tasks pt
    WHERE
        pbv.project_id = ei.project_id
		AND pra.task_id = pt.task_id
		AND pt.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 pbv.budget_entry_method_code = bem.budget_entry_method_code
        and bem.entry_level_code in ( 'M', 'L', 'T')
        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
) proj_non_plan_capitalize,
     DECODE(project_type_class_code, 'CAPITAL', nvl(
        (SELECT
            SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(paie.raw_cost, 0), nvl(paie.burden_cost, 0) ))
        FROM
            pa_expenditure_items_all paie
        WHERE
            paie.project_id = ei.project_id
			AND paie.billable_flag = 'Y'
            AND paie.creation_date >= '12-DEC-21'
    ),0), 0)  proj_capitalizable,
     DECODE(project_type_class_code, 'CAPITAL', nvl(
        (SELECT
            SUM(DECODE(ei.capital_cost_type_code, 'R', nvl(paie.raw_cost, 0), nvl(paie.burden_cost, 0) ))
        FROM
            pa_expenditure_items_all paie
        WHERE
            paie.project_id = ei.project_id
			AND paie.billable_flag = 'N'
            AND paie.creation_date >= '12-DEC-21'
    ),0), 0)  proj_non_capitalizable,
(decode(project_type_class_code,'CAPITAL',decode(ei.capitalizable_flag,'Y',(nvl(DECODE(ei.capital_cost_type_code, 'R', nvl(ei.raw_cost, 0), nvl(ei.burdened_cost, 0
        )), 0)),0),0))actual_capitalizable,
(decode(project_type_class_code,'CAPITAL',decode(ei.capitalizable_flag,'N',(nvl(DECODE(ei.capital_cost_type_code, 'R', nvl(ei.raw_cost, 0), nvl(ei.burdened_cost, 0
        )), 0)),0),0))actual_non_capitalizable,
( DECODE(project_type_class_code, 'CAPITAL', DECODE(ei.capitalizable_flag, 'Y',decode(ei.capitalized_flag,'N',(nvl(DECODE(ei.capital_cost_type_code
, 'R', nvl(ei.raw_cost, 0), nvl(ei.burdened_cost, 0)), 0)),0), 0), 0) ) actual_yet_to_capitalizable,
(SELECT
        SUM(nvl(pbl.revenue,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 = 'R'
        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_revenue_budget,
ei.proj_grouping_method,
ei.proj_grp_supplier_invoice,
ei.proj_asset_cost_alloc_mhd,
pa_utils.get_lookup_values('YES_NO', ei.total_burden_flag, ei.language) proj_enbl_burden_cost_acc,
(SELECT per.period_name FROM pa_periods_all per
 where start_date in
         (select max(start_date) from pa_periods_all per1
          where per1.status in ('O','F')
          and per1.org_id = ei.org_id)
      and per.org_id = ei.org_id) current_pa_period,
(SELECT per.gl_period_name FROM pa_periods_all per
 where start_date in
         (select max(start_date) from pa_periods_all per1
          where per1.status in ('O','F')
          and per1.org_id = ei.org_id)
      and per.org_id = ei.org_id) current_gl_period,
(select per.period_name from pa_periods_all per where ei.org_id = per.org_id
 		AND per.current_pa_period_flag = 'Y') current_reporting_pa_period, /* Bug#31692951 */
ei.projfunc_currency_code,
(decode(project_type_class_code,'CAPITAL',(select count( project_asset_id ) from pa_project_assets_all ppaa where ppaa.project_id = ei.project_id),0)) number_of_assets,
(decode(project_type_class_code,'CAPITAL',nvl((select sum( current_asset_cost ) from pa_project_asset_lines_all ppal where ppal.project_id = ei.project_id and ppal.transfer_status_code = 'T'
                            		AND EXISTS(  SELECT   1
                     FROM pa_expenditure_items_all peia,pa_project_asset_line_Details ppald
                     WHERE peia.expenditure_item_id = ppald.expenditure_item_id
                     and  ppal.project_asset_line_detail_id = ppald.project_asset_line_detail_id
				     and peia.project_id = ei.project_id
                     AND peia.creation_date >= '12-DEC-21'
                  )),0),0)) proj_capitalized_cost,
ei.task_id,
ei.task_number,
ei.task_name,
ei.top_task_id,
ei.top_task_name,
ei.top_task_number,
ei.task_start_date,
ei.task_finish_date,
ei.task_manager,
ei.task_organization,
ei.expenditure_item_date,
ei.expenditure_ending_date expenditure_week_ending_date,
ei.expenditure_type,
ei.expenditure_category,
ei.expenditure_type_class,
ei.incurred_by_person_id,
ei.employee_name,
ei.employee_number,
ei.location_code,
ei.job_id,
ei.job_name,
ei.incurred_by_organization_id,
ei.override_to_organization_id,
ei.expenditure_organization_id,
ei.expenditure_org_name,
ei.non_labor_resource,
ei.system_linkage_function,
ei.transaction_source transaction_source_code,
ei.user_transaction_source transaction_source,
ei.orig_transaction_reference orig_transaction_ref,
ei.expenditure_group,
(CASE
WHEN ei.cost_distributed_flag = 'Y'
     AND ei.cost_burden_distributed_flag = 'N' THEN 'Y'
ELSE null
END)ei_pend_burden_dist,
( CASE
    WHEN ei.expenditure_group_status_code IN (
        'UPDATE_RELEASED',
        'RELEASED'
    ) THEN null
    ELSE 'Y'
END ) ei_group_unreleased,
(case when ei.cost_dist_rejection_code is not null then 'Y' else null end) ei_cost_exception,
(case when ei.expenditure_group_status_code IN (
        'UPDATE_RELEASED',
        'RELEASED'
    ) AND ei.cost_distributed_flag = 'N' then 'Y' else null end) ei_uncosted,
DECODE(ei.cost_distributed_flag, 'Y', 'Y', null) ei_costed,
DECODE(ei.cost_distributed_flag, 'Y', nvl(ei.burden_cost, 0), 0) ei_costed_value,
ei.quantity,
decode(ei.cost_distributed_flag,'Y',ei.burden_cost,0) burden_cost,
( CASE
    WHEN ei.system_linkage_function IN (
        'OT',
        'ST'
    ) THEN nvl(ei.burden_cost, 0)
    ELSE 0
END ) ei_labor_cost,
( CASE
    WHEN ei.system_linkage_function IN (
        'OT',
        'ST'
    ) THEN 0
    ELSE nvl(ei.burden_cost, 0)
END ) ei_non_labor_cost,
ei.unit_of_measure,
ei.unit_of_measure_m,
decode(ei.cost_distributed_flag,'Y',ei.raw_cost,0) raw_cost,
decode(ei.cost_distributed_flag,'Y',ei.raw_cost_rate,0) raw_cost_rate,
ei.cost_distributed_flag cost_distributed_flag,
pa_utils.get_lookup_values('YES_NO', ei.cost_distributed_flag, ei.language) cost_distributed,
pa_funds_control_utils.get_cost_rejection_reason(NVL(ei.cost_dist_rejection_code,ei.IND_COST_DIST_REJECTION_CODE),NULL) cost_dist_rejection_code,
decode(ei.project_type_class_code ,'INDIRECT',NULL,'CAPITAL',NULL,NVL(ei.revenue_distributed_flag,ei.capitalized_flag)) revenue_distributed,
pa_utils.get_lookup_values('YES_NO',decode(ei.project_type_class_code ,'INDIRECT',NULL,'CAPITAL',NULL,NVL(ei.revenue_distributed_flag,ei.capitalized_flag)), ei.language) revenue_distributed_flag,
pa_utils.get_lookup_values('YES_NO',(CASE WHEN ei.project_type_class_code = 'CONTRACT' AND bill_amount IS NOT NULL THEN 'Y'
WHEN ei.project_type_class_code = 'CONTRACT' AND bill_amount IS NULL THEN 'N'
ELSE NULL END), ei.language) billed,
ei.billable_flag  billable_flag,
pa_utils.get_lookup_values('YES_NO',ei.billable_flag,ei.language)  billable,
ei.bill_hold_flag ,
pa_utils.get_lookup_values('BILLING HOLD',ei.bill_hold_flag,ei.language) bill_hold,
( CASE
WHEN ei.billable_flag = 'Y'
AND ei.bill_amount IS NOT NULL
AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
AND ei.INVOICE_METHOD = 'WORK'
AND ei.cost_distributed_flag = 'Y' THEN ei.bill_amount
ELSE 0
END ) billed_cost,
( CASE
WHEN ei.billable_flag = 'Y'
AND ei.bill_amount IS NULL
AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
AND ei.INVOICE_METHOD = 'WORK'
AND ei.cost_distributed_flag = 'Y' THEN ei.BURDEN_COST
ELSE 0
END ) billable_unbilled_cost,
( CASE
WHEN ei.billable_flag = 'Y'
AND ei.bill_amount IS NULL
AND ei.bill_hold_flag = 'Y'
AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
AND ei.INVOICE_METHOD = 'WORK'
AND ei.cost_distributed_flag = 'Y' THEN ei.BURDEN_COST
ELSE 0
END ) billable_onhold_cost,
case when ei.cost_distributed_flag = 'Y' and ei.project_type_class_code = 'CONTRACT' then ei.bill_billable_cost else 0 end bill_billable_cost,
pa_utils.get_lookup_values('YES_NO',ei.bill_nonbilable_flag,ei.language) bill_nonbilable_flag,
case when ei.cost_distributed_flag = 'Y' and ei.project_type_class_code = 'CONTRACT' then ei.bill_nonbilable_cost else 0 end bill_nonbilable_cost,
ei.bill_billhold_cost,
pa_utils.get_lookup_values('YES_NO',ei.bill_billed_flag,ei.language) bill_billed_flag,
ei.bill_billed_cost,
pa_utils.get_lookup_values('YES_NO',ei.bill_not_billed_flag,ei.language) bill_not_billed_flag,
ei.bill_not_billed_cost,
(case when ei.project_type_class_code = 'CONTRACT' AND ei.billable_flag = 'Y' AND ei.bill_amount IS NULL AND ei.cost_distributed_flag = 'Y' AND ei.invoice_method = 'WORK' then ei.burden_cost else 0 end)ei_work_burden_cost,
decode(ei.cost_distributed_flag,'Y',ei.burdened_cost,0) burdened_cost,
decode(ei.cost_distributed_flag,'Y',ei.burdened_cost_rate,0) burdened_cost_rate,
ei.denom_currency_code,
decode(ei.cost_distributed_flag,'Y',ei.denom_raw_cost,0) denom_raw_cost,
decode(ei.cost_distributed_flag,'Y',ei.denom_burdened_cost,0) denom_burdened_cost,
ei.acct_currency_code,
ei.acct_rate_type,
ei.acct_rate_date,
ei.acct_exchange_rate,
decode(ei.cost_distributed_flag,'Y',ei.acct_raw_cost,0) acct_raw_cost,
decode(ei.cost_distributed_flag,'Y',ei.acct_burdened_cost,0) acct_burdened_cost,
( DECODE(ei.capitalizable_flag, 'Y', DECODE(ei.capitalized_flag, 'N', ei.BURDEN_COST, 0), 0) - DECODE(
ei.capitalized_flag, 'Y', ei.BURDEN_COST, 0) - DECODE(ei.capitalizable_flag, 'N', ei.BURDEN_COST
, 0) ) cip_cost,
ei.project_currency_code,
decode(ei.cost_distributed_flag,'Y',ei.project_raw_cost,0) project_raw_cost,
decode(ei.cost_distributed_flag,'Y',ei.project_burdened_cost,0) project_burdened_cost,
pa_utils.get_lookup_values('YES_NO',ei.cost_burden_distributed_flag,ei.language) cost_bur_distributed_flag,
ei.capitalizable_flag,
pa_utils.get_lookup_values('YES_NO',ei.capitalizable_flag,ei.language) capitalizable,
pa_utils.get_lookup_values('YES_NO',ei.asset_line_generated,ei.language) asset_line_generated,
ei.capitalized_flag,
pa_utils.get_lookup_values('YES_NO',ei.capitalized_flag,ei.language) capitalized,
ei.bill_hold_flag capitalizable_cap_hold_flag,
decode(ei.project_type_class_code,'CAPITAL',pa_utils.get_lookup_values('YES_NO',ei.bill_hold_flag,ei.language)) capitalizable_cap_hold,
ei.adjusted_expenditure_item_id,
ei.net_zero_adjustment_flag ,
pa_utils.get_lookup_values('YES_NO',ei.net_zero_adjustment_flag,ei.language) net_zero_adjustment,
ei.transferred_from_exp_item_id,
ei.transferred_item_flag,
pa_utils.get_lookup_values('CC_CROSS_CHARGE_CODE', ei.cc_cross_charge_code, ei.language) cc_cross_charge_code,
pa_utils.get_lookup_values('CC_CROSS_CHARGE_TYPE', ei.cc_cross_charge_type, ei.language) cc_cross_charge_type,
pa_utils.get_lookup_values('CC_PROCESSED_CODE', ei.cc_bl_distributed_code, ei.language) cc_bl_distributed_code,
ei.org_id,
ei.vendor_id,
decode (ei.vendor_id, null, null, (
SELECT
vendor_name
FROM
ap_suppliers
WHERE
vendor_id = ei.vendor_id
)) vendor_name, /* Bug#30749534 */
ei.document_header_id,
ei.document_distribution_id,
ei.document_line_number,
ei.document_payment_id,
ei.document_type,
ei.document_distribution_type,
ei.prvdr_org_name Operating_Unit,
decode(ei.cost_distributed_flag,'Y',DECODE(ei.capital_cost_type_code, 'R', ei.raw_cost, DECODE(ei.cost_burden_distributed_flag,'Y', ei.BURDEN_COST,0)),0) capital_cost_code_amt,
cdl.line_num,
cdl.line_type line_type_code,
pa_utils.get_lookup_values('COST DISTRIBUTION LINE TYPE', cdl.line_type, ei.language) line_type,
cdl.reversed_flag,
cdl.transfer_status_code transfer_status_code,
cdl.pa_date,
cdl.gl_date,
cdl.recvr_pa_date,
cdl.recvr_gl_date,
cdl.pa_period_name,
cdl.gl_period_name,
cdl.recvr_pa_period_name,
cdl.recvr_gl_period_name,
cdl.acct_event_id,
decode(ei.cost_distributed_flag,'Y',cdl.amount,0) amount,
decode(ei.cost_distributed_flag,'Y',cdl.burdened_cost,0) cdl_burdened_cost,
decode(ei.cost_distributed_flag,'Y',DECODE(ei.capital_cost_type_code, 'R', cdl.AMOUNT, NVL(cdl.BURDENED_COST,cdl.AMOUNT)),0) cdl_amount,
pa_utils.get_lookup_values('YES_NO',cdl.is_ei_final_accounted,ei.language) cdl_acct_status, /* Bug#30749534 */
/* pa_utils.get_lookup_values('PA_XLA_TRANSFER_STATUS',(SELECT
DECODE(xe.process_status_code, 'P', 'A', 'U', 'P', 'I', 'R') FROM xla_events xe, pa_cc_dist_lines_all cc
WHERE
xe.application_id = 275
AND xe.event_id = cc.acct_event_id
AND cc.expenditure_item_id = ei.expenditure_item_id
AND cc.line_num = cdl.line_num
AND ei.cc_bl_distributed_code = 'Y'),ei.language) bl_acct_status,
*/ /* Bug#30749534 */
NULL bl_acct_status,
ei.expenditure_group_status_code exp_group_status_code,
pa_utils.get_lookup_values('EXPENDITURE GROUP STATUS', ei.expenditure_group_status_code, ei.language) exp_group_status,
ei.expenditure_status_code EXPENDITURE_STATUS,
( CASE
WHEN ei.cost_dist_rejection_code IS NOT NULL THEN 'CE'
WHEN ei.cost_distributed_flag = 'N' AND ei.expenditure_group_status_code not in ('RELEASED','UPDATE_RELEASED') THEN 'UR'
WHEN ei.cost_distributed_flag = 'N' THEN 'UCST'
WHEN ei.cost_distributed_flag = 'Y' AND (cdl.transfer_status_code IN ('X','R') OR xla.process_status_code = 'I') THEN 'CAE'
WHEN ei.cost_distributed_flag = 'Y' AND cdl.is_ei_final_accounted = 'Y' THEN 'ACCT'
WHEN ei.cost_distributed_flag = 'Y' THEN 'CST'
ELSE NULL
END ) precosting_status_code,
( CASE
WHEN ei.cost_dist_rejection_code IS NOT NULL THEN pa_utils.get_lookup_values('PA_EI_COSTING_STATUS','CE', ei.LANGUAGE)
WHEN ei.cost_distributed_flag = 'N' AND ei.expenditure_group_status_code not in  ('RELEASED', 'UPDATE_RELEASED') THEN pa_utils.get_lookup_values('PA_EI_COSTING_STATUS','UR', ei.LANGUAGE)
WHEN ei.cost_distributed_flag = 'N' THEN pa_utils.get_lookup_values('PA_EI_COSTING_STATUS','UCST', ei.LANGUAGE)
WHEN ei.cost_distributed_flag = 'Y' AND (cdl.transfer_status_code IN ('X','R') OR xla.process_status_code = 'I') THEN pa_utils.get_lookup_values('PA_EI_COSTING_STATUS','CAE', ei.LANGUAGE)
WHEN ei.cost_distributed_flag = 'Y' AND cdl.is_ei_final_accounted = 'Y' THEN pa_utils.get_lookup_values('PA_EI_COSTING_STATUS','ACCT', ei.LANGUAGE)
WHEN ei.cost_distributed_flag = 'Y' THEN pa_utils.get_lookup_values('PA_EI_COSTING_STATUS','CST', ei.LANGUAGE)
ELSE NULL
END ) precosting_status,
NULL asset_name,
ei.work_type_name WORK_TYPE,
(CASE WHEN ei.transaction_source LIKE 'PO%' THEN NVL(EI.receipt_po_number,ei.po_number)
WHEN ei.transaction_source LIKE 'AP%' THEN ei.matched_po_number
ELSE NULL
END
) po_number,
(CASE WHEN ei.transaction_source LIKE 'PO%' THEN nvl(ei.receipt_po_line_num,ei.po_line_number)
WHEN ei.transaction_source LIKE 'AP%' THEN ei.matched_po_line_number
ELSE NULL
END
) po_line_number,
(CASE WHEN ei.transaction_source LIKE 'PO%' THEN ei.receipt_number
WHEN ei.transaction_source LIKE 'AP%' THEN ei.matched_receipt_num
ELSE NULL
END) receipt_number,
(CASE WHEN ei.transaction_source LIKE 'PO%' THEN ei.receipt_line_number
WHEN ei.transaction_source LIKE 'AP%' THEN ei.matched_receipt_line_num
ELSE NULL
END) receipt_line_number,
ei.invoice_no,
ei.invoice_line_number,
(
CASE WHEN ei.transaction_source LIKE 'PO%' THEN EI.po_distribution_line_num
WHEN ei.transaction_source LIKE 'AP%' THEN ei.matched_po_dist_line_num
ELSE NULL
END
) po_distribution_line_number,
ei.invoice_distribution_line_num invoice_distribution_line_no,
(
SELECT
concatenated_segments
FROM
gl_code_combinations_kfv
WHERE
code_combination_id = cdl.dr_code_combination_id
) debit_account,
(
SELECT
concatenated_segments
FROM
gl_code_combinations_kfv
WHERE
code_combination_id = cdl.cr_code_combination_id
) credit_account,
(CASE
WHEN pa_xla_interface_pkg.get_source(ei.transaction_source, ei.document_payment_id) = 'PA' and cdl.transfer_status_code in ('V','A') THEN 'Projects Accounting'
WHEN pa_xla_interface_pkg.get_source(ei.transaction_source, ei.document_payment_id) = 'RCV' and cdl.transfer_status_code in ('V','A') THEN 'Purchasing'
WHEN pa_xla_interface_pkg.get_source(ei.transaction_source, ei.document_payment_id) = 'AP_PAY' and cdl.transfer_status_code in ('V','A') THEN 'Payables'
WHEN pa_xla_interface_pkg.get_source(ei.transaction_source, ei.document_payment_id) = 'AP_INV' and cdl.transfer_status_code in ('V','A') THEN 'Payables'
WHEN pa_xla_interface_pkg.get_source(ei.transaction_source, ei.document_payment_id) = 'INV' and cdl.transfer_status_code in ('V','A') THEN 'Inventory'
WHEN pa_xla_interface_pkg.get_source(ei.transaction_source, ei.document_payment_id) = 'WIP' and cdl.transfer_status_code in ('V','A') THEN 'Work In Progress'
ELSE NULL
END) accounting_source,
(pa_utils.get_lookup_values('TRANSFER STATUS', cdl.transfer_status_code, ei.language)) transfer_status,
ei.provider_le_name,
ei.receiver_le_name,
(
SELECT
name
FROM
hr_all_organization_units
WHERE
organization_id IN (
SELECT
to_number(org_information2)
FROM
hr_organization_information
WHERE
organization_id = ei.cc_recvr_organization_id
AND org_information_context = 'Operating Unit Information'
)
) receiver_operating_unit,
ei.acct_transfer_price transfer_price,
/* Bug#30749534
(
SELECT
name
FROM
pa_cc_tp_schedules tps
WHERE
tps.tp_schedule_id = ei.labot_tp_sch_id
) labot_tp_sch_name,
(
SELECT
name
FROM
pa_cc_tp_schedules tps
WHERE
tps.tp_schedule_id = ei.non_labor_tp_sch_id
) non_labor_tp_sch_name,
*/
NULL labot_tp_sch_name,
NULL non_labor_tp_sch_name,
(CASE WHEN (cdl.transfer_status_code = 'A' AND xla.gl_transfer_status_code = 'Y' OR nvl(ei.historical_flag, 'Y') = 'Y')  THEN 'GL'
WHEN  cdl.transfer_status_code = 'A' AND xla.gl_transfer_status_code <> 'Y' THEN 'PGL'
WHEN  cdl.transfer_status_code = 'A' AND /* pa_xla_interface_pkg.get_post_acc_sla_ccid(cdl.acct_event_id, cdl.transfer_status_code, ei.transaction_source
, nvl(ei.historical_flag, 'Y'), cdl.expenditure_item_id, cdl.line_num, cdl.line_type, cdl.dr_code_combination_id
, 'DEBIT', ei.set_of_books_id) IS NOT NULL */ cdl.is_ei_final_accounted = 'Y' THEN 'SLA'
WHEN ( cdl.transfer_status_code IN ('R','X') OR xla.process_status_code IN ('I','U')) THEN 'PSLA'
END) accounting_status_code,
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_xla_interface_pkg.get_post_acc_sla_ccid(cdl.acct_event_id, cdl.transfer_status_code, ei.transaction_source
, nvl(ei.historical_flag, 'Y'), cdl.expenditure_item_id, cdl.line_num, cdl.line_type, cdl.dr_code_combination_id
, 'DEBIT', ei.set_of_books_id),NULL, */ cdl.is_ei_final_accounted, 'N','PSLA','SLA')) = 'PSLA'
THEN 'PSLA'
WHEN cdl.transfer_status_code NOT IN ('V','G') THEN 'SLA'
END),ei.language ) project_sla_acc_status,
pa_utils.get_lookup_values('PA_EI_ACCOUNTING_STATUS',(CASE WHEN ( xla.gl_transfer_status_code = 'Y' OR nvl(ei.historical_flag, 'Y')= 'Y') THEN 'GL'
WHEN xla.gl_transfer_status_code <> 'Y'
AND /* pa_xla_interface_pkg.get_post_acc_sla_ccid(cdl.acct_event_id, cdl.transfer_status_code, ei.transaction_source
, nvl(ei.historical_flag, 'Y'), cdl.expenditure_item_id, cdl.line_num, cdl.line_type, cdl.dr_code_combination_id
, 'DEBIT', ei.set_of_books_id) IS NOT NULL */ cdl.is_ei_final_accounted = 'Y' THEN 'PGL'
END),ei.language ) sla_gl_acc_status,
( CASE
  WHEN  (cdl.transfer_status_code IN ('X','R') OR xla.process_status_code = 'I')
  THEN pa_utils.get_lookup_values('PA_EI_COSTING_STATUS','CAE',ei.language )
  WHEN  cdl.transfer_status_code = 'A'
  THEN pa_utils.get_lookup_values('PA_EI_ACCOUNTING_STATUS','PA' ,ei.language )
  ELSE NULL
  END) pre_accounting_status,
ei.project_exchange_rate,
ei.project_rate_date,
ei.project_rate_type,
ei.accrued_revenue REVENUE_ACCRUED,
ei.burden_schedule,
pa_utils.get_lookup_values('TRANSFER REJECTION CODE', cdl.transfer_rejection_reason, ei.language) transfer_rejection_reason,
/* (select pa_utils.get_lookup_values('CC_REJECTION_CODE', cc.transfer_rejection_code, ei.language)  from pa_cc_dist_lines_all cc
where cc.expenditure_item_id = cdl.expenditure_item_id
AND cc.line_num = cdl.line_num
AND transfer_rejection_code is not null) cc_rejection_reason, */ /* Bug#30749534 */
NULL cc_rejection_reason,
(CASE
WHEN ei.cost_dist_rejection_code IS NOT NULL
THEN ei.cost_dist_rejection_code
WHEN ei.ind_cost_dist_rejection_code IS NOT NULL
THEN ei.ind_cost_dist_rejection_code
WHEN ei.cc_rejection_code IS NOT NULL
THEN ei.cc_rejection_code
WHEN cdl.transfer_rejection_reason IS NOT NULL
THEN cdl.transfer_status_code
WHEN xla.process_status_code = 'I'
THEN to_char(xla.message_number)
WHEN ei.project_type_class_code = 'CONTRACT' AND ei.inv_gen_rejection_code IS NOT NULL AND ei.inv_gen_rejection_code <> 'N'
THEN ei.inv_gen_rejection_code
WHEN ei.project_type_class_code = 'CONTRACT' AND ei.rev_dist_rejection_code IS NOT NULL
THEN ei.rev_dist_rejection_code
END) alert_type,
(CASE
WHEN ei.cost_dist_rejection_code IS NOT NULL
THEN nvl( pa_utils.get_lookup_values('COST DIST REJECTION CODE', ei.cost_dist_rejection_code, ei.language) ,
pa_utils.get_lookup_values('FC_RESULT_CODE', ei.cost_dist_rejection_code, ei.language) )
WHEN ei.ind_cost_dist_rejection_code IS NOT NULL
THEN pa_utils.get_lookup_values('IND COST DIST REJECTION CODE', ei.ind_cost_dist_rejection_code, ei.language)
WHEN ei.cc_rejection_code IS NOT NULL
THEN pa_utils.get_lookup_values('CC_REJECTION_CODE', ei.cc_rejection_code, ei.language)
WHEN cdl.transfer_rejection_reason IS NOT NULL
THEN cdl.transfer_rejection_reason
WHEN xla.process_status_code = 'I'
THEN xla.encoded_msg
WHEN ei.project_type_class_code = 'CONTRACT' AND ei.inv_gen_rejection_code IS NOT NULL AND ei.inv_gen_rejection_code <> 'N'
THEN pa_utils.get_lookup_values('REVENUE DIST REJECTION CODE', ei.inv_gen_rejection_code, ei.language)
WHEN ei.project_type_class_code = 'CONTRACT' AND ei.rev_dist_rejection_code IS NOT NULL
THEN pa_utils.get_lookup_values('REVENUE DIST REJECTION CODE', ei.rev_dist_rejection_code, ei.language)
END) alert_text,
decode(ei.system_linkage_function,'ST','L','OT','L','NL') cost_identifier,
decode(cdl.transfer_status_code,'R','Unaccounted','X','Unaccounted','P','Unaccounted',null) unaccounted,
decode(cdl.transfer_status_code,'R','AccountingException','X','AccountingException',decode(xla.process_status_code,'I','AccountingException',null)) accounting_exception,
( CASE
WHEN cdl.transfer_status_code = 'A'
THEN 'Y'
ELSE null
END ) project_accounted,
( CASE
WHEN cdl.transfer_status_code = 'A'
AND /* pa_xla_interface_pkg.get_post_acc_sla_ccid(cdl.acct_event_id, cdl.transfer_status_code, ei.transaction_source
, nvl(ei.historical_flag, 'Y'), cdl.expenditure_item_id, cdl.line_num, cdl.line_type, cdl.dr_code_combination_id
, 'DEBIT', ei.set_of_books_id) IS NOT NULL */
cdl.is_ei_final_accounted = 'Y'
THEN 'Y'
ELSE null
END ) sla_accounted,
( CASE
WHEN xla.gl_transfer_status_code = 'Y'
THEN 'Y'
ELSE null
END ) gl_accounted,
(case WHEN (cdl.transfer_status_code IN ('X','R') OR xla.process_status_code = 'I') then 'Y' else null end) ei_account_exception,
(case WHEN cdl.is_ei_final_accounted = 'Y' THEN 'Y' else null end) ei_accounted,
pa_xla_interface_pkg.get_post_acc_sla_ccid
(
cdl.acct_event_id,
cdl.transfer_status_code,
ei.transaction_source,
nvl(ei.historical_flag, 'Y'),
decode(pa_xla_interface_pkg.get_source(ei.transaction_source,ei.document_payment_id),
'PA', cdl.expenditure_item_id,
'AP_PAY', cdl.system_reference5,
'AP_INV', ei.document_distribution_id,
'RCV', cdl.system_reference5,
'INV', cdl.system_reference5,
'WIP', cdl.system_reference5),
decode(pa_xla_interface_pkg.get_source(ei.transaction_source,ei.document_payment_id),
'PA', cdl.line_num,
'AP_PAY', ei.document_distribution_id,
'AP_INV', NULL,
'RCV', NULL,
'INV', NULL,
'WIP', NULL),
cdl.line_type,
cdl.dr_code_combination_id,
'DEBIT',
ei.set_of_books_id
) sla_acct_code,
ei.historical_flag,
ei.set_of_books_id,
DECODE(project_type_class_code, 'CAPITAL', pa_ecc_cost_proj_pkg.get_task_asset_asmt_lvl(ei.project_id, ei.task_id)) asset_asgn_exists_code,
DECODE(project_type_class_code, 'CAPITAL', nvl(pa_utils.get_group_level_task_id(ei.task_id, ei.top_task_id, ei.project_id), -9999)) grouped_task_id,
language,
ei.rate_source_id, /* V6 */
ei.discount_percentage,
ei.rate_disc_reason_code,
ei.bill_markup_percentage,
ei.bill_group,
ei.bill_amount,
ei.bill_rate,
ei.revenue_hold_flag,
ei.accrual_rate,
ei.inventory_item_id,
ei.inventory_item,
ei.invoice_method,
case ei.project_level_funding_flag when 'Y' then 0 else ei.top_task_id end funding_task_id,
case ei.project_level_funding_flag when 'Y' then NULL else ei.top_task_name end funding_task_name,
case ei.project_level_funding_flag when 'Y' then NULL else ei.top_task_number end funding_task_number,
ei.inv_gen_rejection_code,
ei.rev_dist_rejection_code,
eligible_bill_proj.project_id billing_project_id,
bill_gl_period.bill_period_name bill_chart_period_name,
bill_gl_period.period_name bill_gl_period_name,
bill_gl_period.start_date  gl_period_start_date,
case when bill_gl_period.display_ctr between -3 and 0 then 'Y' else 'N' end display_planrev_chart,
case when bill_gl_period.display_ctr between -3 and 3 then 'Y' else 'N' end display_totexp_chart,
ei.projfunc_bill_amount,
nvl(case when ei.project_type_class_code = 'CONTRACT' then
      (select 'Y' from dual where exists (select pcrdl.expenditure_item_id
	     from pa_cust_rev_dist_lines_all pcrdl
		 where ei.expenditure_item_id = pcrdl.expenditure_item_id
		 and pcrdl.draft_invoice_num is not null
		 group by pcrdl.expenditure_item_id
		 having sum(nvl(pcrdl.bill_trans_bill_amount, 0)) <> 0))
else null end, 'N') billed_flag,
case when ei.projfunc_bill_amount is not NULL and ei.projfunc_bill_amount <> 0 then ei.projfunc_bill_amount
     when (ei.project_type_class_code = 'CONTRACT' AND ei.cost_distributed_flag = 'Y' AND ei.billable_flag = 'Y'
	       AND (ei.projfunc_bill_amount is NULL or ei.projfunc_bill_amount = 0))
	      THEN PA_ECC_BILL_LOAD_PKG.get_bill_amount('EI',ei.expenditure_item_id)
else null end billdb_pfc_bill_amount
FROM
pa_exp_item_mls_v ei,
(
    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
    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 (ppa.revenue_accrual_method IN ('WORK') OR ppa.invoice_method IN ('WORK'))
        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')
) eligible_bill_proj,
(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
)
)
) bill_gl_period,
/* where rnum-perval between -3 and 3) bill_gl_period, */
/*
(
select expenditure_item_id from pa_cust_rev_dist_lines_all pcrdl
where  creation_date >=  to_date(|| g_ecc_last_load_date ||)
and    pcrdl.draft_invoice_num is not null
group by pcrdl.expenditure_item_id
having sum(nvl(pcrdl.bill_trans_bill_amount, 0)) <> 0
) check_ei_billed,
*/
/* Bug#30749534
(
SELECT
cd.*
FROM
(
    SELECT
        c.*
    FROM
        pa_cost_distribution_lines_all c
    WHERE
        c.line_type = 'R'
        AND c.creation_date >= '12-DEC-21'
        AND ( c.expenditure_item_id,
              c.line_num ) IN (
            SELECT
                expenditure_item_id,
                max(line_num)
            FROM
                pa_cost_distribution_lines_all b
            WHERE
                creation_date >= '12-DEC-21'
                AND line_type = 'R'
            GROUP BY
                b.expenditure_item_id
        )
) cd
) cdl,
*/
(
select * from pa_ecc_cost_cdl_account peccact where line_type = 'R'
) cdl,
/* Bug#30749534
(
SELECT
xe.event_id,
xe.application_id,
xeh.gl_transfer_status_code,
xeh.accounting_entry_status_code,
xeh.ledger_id,
xae_ledger_id,
nvl(xe.process_status_code, 'UP') process_status_code,
message_number,
encoded_msg
FROM
xla_events xe,
xla_ae_headers xeh,
(
SELECT
event_id,
xae_ledger_id,
message_number message_number,
encoded_msg encoded_msg
FROM
(
SELECT
event_id,
xae_ledger_id,
ltrim(sys_connect_by_path(message_number, '|'), '|') AS message_number,
ltrim(sys_connect_by_path(encoded_msg, '|'), '|') AS encoded_msg
FROM
(
SELECT
event_id,
xae.ledger_id xae_ledger_id,
message_number,
encoded_msg,
ROW_NUMBER() OVER(
PARTITION BY event_id
ORDER BY
message_number
) AS rn
FROM
xla_accounting_errors xae,
gl_ledgers gl
WHERE
xae.application_id = 275
AND xae.event_id IS NOT NULL
and xae.ledger_id = gl.ledger_id
and gl.LEDGER_CATEGORY_CODE = 'PRIMARY'
)
WHERE
CONNECT_BY_ISLEAF = 1
CONNECT BY event_id = PRIOR event_id
AND xae_ledger_id = PRIOR xae_ledger_id
AND rn = PRIOR rn + 1 START WITH rn = 1
)
) xae
WHERE
xe.application_id = 275
AND xe.event_id = xeh.event_id
AND xe.application_id = xeh.application_id
AND xeh.event_id = xae.event_id(+)
AND xeh.ledger_id = xae.xae_ledger_id(+)
) xla
*/
/* Added for Bug#30749534 */
(
SELECT
xe.event_id,
xe.application_id,
xeh.gl_transfer_status_code,
xeh.accounting_entry_status_code,
xeh.ledger_id,
nvl(xe.process_status_code, 'UP') process_status_code,
message_number,
encoded_msg
FROM
xla_events xe,
xla_ae_headers xeh,
(
SELECT
event_id, message_number, encoded_msg
FROM
(
SELECT
event_id,
message_number,
encoded_msg,
ROW_NUMBER() OVER(
PARTITION BY event_id
ORDER BY
message_number
) AS rn
FROM
xla_accounting_errors xae,
gl_ledgers gl
WHERE
xae.application_id = 275
AND xae.event_id IS NOT NULL
and xae.ledger_id = gl.ledger_id
and gl.LEDGER_CATEGORY_CODE = 'PRIMARY'
)
WHERE rn = 1
) xae
WHERE
xe.application_id = 275
AND xe.event_id = xeh.event_id
AND xe.application_id = xeh.application_id
AND xe.event_id = xae.event_id(+)
) xla
WHERE
ei.expenditure_item_id = cdl.expenditure_item_id (+)
AND ei.project_type <> 'AWARD_PROJECT'
AND ei.set_of_books_id = nvl(xla.ledger_id, ei.set_of_books_id)
AND cdl.acct_event_id = xla.event_id (+)
AND ei.project_id = cdl.project_id (+)
AND ei.task_id = cdl.task_id (+)
AND ei.creation_date >= '12-DEC-21'
AND eligible_bill_proj.project_id (+) = ei.project_id
AND ei.org_id = bill_gl_period.org_id (+)
AND ei.expenditure_item_date between bill_gl_period.start_date (+) and bill_gl_period.end_date (+)
/* AND ei.expenditure_item_id = check_ei_billed.expenditure_item_id (+) */
AND ei.language in ('US')
) tmp,
pa_expenditure_items_all peia,
pa_tasks pt , (select "ROW_ID" "'PA_COST_EIDFF_ROW_ID'","CONTEXT_VALUE" "'PA_COST_EIDFF_CONTEXT_VALUE'","ITEM_NUMBER" "'PA_COST_EIDFF_ITEM_NUMBER'","SERIAL_NUMBER" "'PA_COST_EIDFF_SERIAL_NUMBER'","CATEGORY" "'PA_COST_EIDFF_CATEGORY'","LOCATION" "'PA_COST_EIDFF_LOCATION'","PRODUCT" "'PA_COST_EIDFF_PRODUCT'","CONCATENATED_SEGMENTS" "'PA_COST_EIDFF_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT_VALUE",ATTRIBUTE6 "ITEM_NUMBER",ATTRIBUTE7 "SERIAL_NUMBER",ATTRIBUTE8 "CATEGORY",ATTRIBUTE9 "LOCATION",ATTRIBUTE10 "PRODUCT",ATTRIBUTE6||'.'||ATTRIBUTE7||'.'||ATTRIBUTE8||'.'||ATTRIBUTE9||'.'||ATTRIBUTE10 "CONCATENATED_SEGMENTS" from PA_EXPENDITURE_ITEMS_ALL )) dfv2
where tmp.expenditure_item_id = peia.expenditure_item_id (+)
and tmp.task_id = pt.task_id(+)
 AND peia.rowid = dfv2."'PA_COST_EIDFF_ROW_ID'"(+)
)
PIVOT (
MAX ( project_type ) AS project_type,
MAX (project_organization) As project_organization,
MAX (task_organization) AS task_organization,
MAX ( expenditure_org_name ) AS expenditure_org_name,
MAX (proj_grouping_method) as proj_grouping_method,
MAX (proj_grp_supplier_invoice) as proj_grp_supplier_invoice,
MAX (proj_asset_cost_alloc_mhd) as proj_asset_cost_alloc_mhd,
MAX ( proj_enbl_burden_cost_acc ) AS proj_enbl_burden_cost_acc,
MAX ( cost_distributed ) AS cost_distributed,
MAX ( revenue_distributed_flag ) AS revenue_distributed_flag,
MAX ( exp_group_status ) AS exp_group_status,
MAX ( cc_cross_charge_code ) AS cc_cross_charge_code,
MAX ( cc_cross_charge_type ) AS cc_cross_charge_type,
MAX ( cc_bl_distributed_code ) AS cc_bl_distributed_code,
MAX ( line_type ) AS line_type,
MAX ( transfer_status ) AS transfer_status,
MAX ( project_status ) AS project_status,
MAX( cc_rejection_reason) AS cc_rejection_reason,
MAX( billable) AS billable,
MAX( bill_hold) AS bill_hold,
MAX( task_billable_flag ) AS task_billable_flag,
MAX( task_work_type ) AS task_work_type,
MAX( bill_nonbilable_flag) AS bill_nonbilable_flag,
MAX( bill_billed_flag) AS bill_billed_flag,
MAX( bill_not_billed_flag) AS bill_not_billed_flag,
MAX( capitalizable) AS capitalizable,
MAX( asset_line_generated ) AS asset_line_generated,
MAX( capitalized) AS capitalized,
MAX( capitalizable_cap_hold) AS capitalizable_cap_hold,
MAX( net_Zero_adjustment) AS net_Zero_adjustment,
MAX( billed) AS billed,
MAX( cost_bur_distributed_flag) AS cost_bur_distributed_flag,
MAX( bl_acct_status) AS bl_acct_status,
MAX( cdl_acct_status) AS cdl_acct_status,
MAX( project_sla_acc_status) AS project_sla_acc_status,
MAX( sla_gl_acc_status) AS sla_gl_acc_status,
MAX( pre_accounting_status) AS pre_accounting_status,
MAX( transfer_rejection_reason) AS transfer_rejection_reason,
MAX( alert_text) AS alert_text,
MAX( precosting_status) AS precosting_status,
MAX( user_project_type) AS user_project_type,
MAX( exp_accounting_stage) AS exp_accounting_stage,
MAX( acct_status_tot_bur) AS acct_status_tot_bur,
MAX( Operating_Unit) AS Operating_Unit,
MAX( asset_assignment_exists) AS asset_assignment_exists,
MAX(rate_discount_reason) AS rate_discount_reason,
MAX(revenue_hold) AS revenue_hold,
MAX(WIP_STATUS) AS wip_status,
MAX(cost_identifier_desc) AS cost_identifier_desc
FOR language IN ( 'US' "US" ))
) x
where
2=2
Download
Blitz Report™