ECC Project Manufacturing, Expenditures

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Ecc Last Update Date, Project Id, Project Number, Org Id, Task Id, Task Number, Item Id, Exp Type, Exp Category ...
Imported from Enterprise Command Center
Description: Project Manufacturing Expenditures
Dataset Key: pjm-expenditures
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
select
x.*
from
(
select * from ( SELECT  /*+ leading(ppp) index(pp PA_PROJECTS_U1) */    'PROJ_EXP-' || pp.project_id || '-' || NVL( TO_CHAR(pt.task_id), 'NO_TASK' ) || '-' || TO_CHAR(pei.expenditure_item_id) AS ecc_spec_id,  GREATEST( pp.last_update_date, pt.last_update_date, pei.last_update_date, pet.last_update_date, pe.last_update_date,  NVL( pap.last_update_date, pp.last_update_date ) , NVL( pj.last_update_date, pp.last_update_date ) , NVL( ap1.last_update_date, pp.last_update_date ) ) AS ecc_last_update_date,  pp.project_id AS project_id, pp.segment1 AS project_number, pp.org_id AS org_id, pt.task_id AS task_id,  pt.task_number AS task_number, pei.expenditure_item_id AS item_id, pei.expenditure_type AS exp_type,  pet.expenditure_category AS exp_category, pet.revenue_category_code AS exp_revenue_category_code, hou1.name AS exp_org,  pei.expenditure_item_date AS exp_item_date, pei.quantity AS exp_quantity, flv.meaning AS uom,  pei.projfunc_currency_code AS exp_projfunc_currency_code, NVL( pei.burden_cost, 0 ) AS exp_burdened_cost,  pei.accrued_revenue AS exp_accrued_revenue, pei.bill_amount AS exp_bill_amount, pei.project_currency_code AS exp_project_currency_code, NVL( pei.project_burdened_cost, 0 ) AS exp_project_burdened_cost, pei.project_raw_revenue AS exp_project_raw_revenue, pei.project_bill_amount AS exp_project_bill_amount, pei.bill_trans_currency_code AS exp_bill_trans_currency_code,  pei.bill_trans_bill_amount AS exp_bill_trans_bill_amount, pei.non_labor_resource AS exp_non_labor_resc,  hou2.name AS exp_non_labor_org, pe.incurred_by_person_id AS exp_incurred_by_person_id, pap.full_name AS exp_employee_name,  pap.employee_number AS exp_employee_number, pei.job_id AS exp_person_job_id, pj.name AS exp_person_job_name,  pe.vendor_id AS supplier_id, ap1.vendor_name AS supplier_name, hou1.language AS language  FROM pa_projects_all pp, pa_tasks pt, pa_expenditure_items_all pei, pa_expenditure_types pet, pa_expenditures_all pe,  per_all_people_f pap, per_jobs pj, ap_suppliers ap1, hr_all_organization_units_tl hou1,  hr_all_organization_units_tl hou2, fnd_lookup_values flv   , (SELECT DISTINCT project_id FROM pjm_project_parameters) ppp     WHERE pp.start_date >= NVL(to_date('','RRRR/MM/DD HH24:MI:SS'), pp.start_date) AND pp.project_type <> 'AWARD_PROJECT'  AND pp.template_flag = 'N'  AND pp.project_id = ppp.project_id  AND pt.project_id = pp.project_id  and PEI.project_id = pt.project_id   AND pei.task_id = pt.task_id  AND pet.expenditure_type = pei.expenditure_type  AND pe.expenditure_id = pei.expenditure_id  AND pe.org_id = pei.org_id  AND pap.person_id(+) = pe.incurred_by_person_id  AND TRUNC(SYSDATE) BETWEEN NVL( pap.effective_start_date, SYSDATE -1 ) AND NVL( pap.effective_end_date, SYSDATE +1 )  AND pj.job_id(+) = pei.job_id  AND ap1.vendor_id(+) = pe.vendor_id  AND hou1.organization_id = NVL( pei.override_to_organization_id, pe.incurred_by_organization_id )  AND hou2.organization_id(+) = pei.organization_id  AND (hou2.language IS NULL OR hou2.language = hou1.language)  AND flv.view_application_id =275  AND flv.security_group_id =0  AND flv.lookup_type = 'UNIT'  AND flv.lookup_code = pet.unit_of_measure  AND flv.language = hou1.language  AND hou1.language IN ('US') ) PIVOT( MAX(exp_org) AS exp_org, MAX(uom) AS uom,  MAX(exp_non_labor_org) AS exp_non_labor_org FOR LANGUAGE IN ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV