ECC Projects, Projects Costing: Transactions, SQL3
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
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
Run
ECC Projects, Projects Costing: Transactions, SQL3 and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( WITH sq_bill_proj AS ( SELECT ppa.project_id, ppa.name project_name, ppa.segment1 project_number, ppa.org_id, ppa.project_type, nvl(ppa.project_level_funding_flag, 'N') project_level_funding_flag, ppa.multi_currency_billing_flag, ppta.project_type_class_code, ppa.start_date, ppa.completion_date, pia.set_of_books_id FROM pa_projects_all ppa, pa_project_statuses ps, pa_project_types_all ppta, pa_budget_versions pbv, pa_budget_types pbt, pa_budget_entry_methods pabem, pa_implementations_all pia WHERE pia.org_id = ppa.org_id AND ppta.project_type = ppa.project_type AND ppta.org_id = ppa.org_id AND ppta.direct_flag = 'Y' /* CONTRACT PROJECT */ AND ppta.project_type_class_code = 'CONTRACT' AND nvl(ppta.cc_prvdr_flag, 'N') <> 'Y' /* NOT TO INCLUDE INTERCOMPANY PROJECTS */ AND ppa.project_status_code = ps.project_status_code AND ps.project_system_status_code NOT IN ('CLOSED','PENDING_CLOSE','PENDING_PURGE','PARTIALLY_PURGED','PURGED') AND ppa.template_flag = 'N' AND ppa.revenue_accrual_method IN ('WORK','EVENT') AND ppa.invoice_method IN ('WORK','EVENT') AND pbv.project_id = ppa.project_id AND pbv.budget_status_code = 'B' AND pbv.current_flag = 'Y' AND pbv.budget_type_code = 'AR' /* APPROVED REVENUE BUDGET HARD CHECK? */ AND pbv.budget_type_code = pbt.budget_type_code AND pabem.budget_entry_method_code = pbv.budget_entry_method_code AND PABEM.TIME_PHASED_TYPE_CODE = 'G' /* BY GL PERIOD */ AND pabem.entry_level_code IN ('P','T') ) SELECT 'PROJ_DS_EICDL_EXCLD' record_type, 'PROJ-EXCLD-' || TO_CHAR(proj.project_id) || '-' || to_char(pt.top_task_id)||'-'||gps.period_name ecc_spec_id, proj.project_id, proj.project_name, proj.project_number, proj.org_id, case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end funding_task_id, pt.task_id, pt.task_number, pt.task_name, pt.top_task_id top_task_id, pt.task_name top_task_name, pt.task_number top_task_number, proj.project_type_class_code, 'N' billproj_display_flag, -9999 ei_ev_reference, gps.period_name bill_gl_period_name, proj.project_id||':'||case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end||':'||'-9999' DS_ASSOC_PRJ_TSK_EE_ATTR, proj.project_id||':'||case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end||':'||'-9999'||':'||gps.period_name DS_ASSOC_PRJ_TSK_EE_PER_ATTR, proj.project_id||':'||case proj.project_level_funding_flag when 'Y' then 0 else pt.top_task_id end||':'||gps.period_name DS_ASSOC_PRJ_TSK_PER_ATTR FROM sq_bill_proj proj, pa_tasks pt, gl_period_statuses gps WHERE proj.project_id = pt.project_id AND pt.task_id = pt.top_task_id AND pt.wbs_level = 1 AND gps.set_of_books_id = proj.set_of_books_id AND gps.application_id = 101 AND gps.start_date between proj.start_date and nvl(proj.completion_date, add_months(sysdate, 6)) AND gps.adjustment_period_flag = 'N' ) x where 2=2 |