ECC Project Manufacturing, Procurement Activities, SQL4
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Project Manufacturing Procurement Details
Dataset Key: pjm-procurement
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Description: Project Manufacturing Procurement Details
Dataset Key: pjm-procurement
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run
ECC Project Manufacturing, Procurement Activities, SQL4 and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( select * from ( SELECT /* Quot */ 'PROJ_QUOT' || '-' || line.project_id || '-' || NVL( TO_CHAR(line.task_id), 'NO_TASK' ) || '-' || poh.po_header_id || '-' || poh1.po_header_id AS ecc_spec_id, 'PROJ_QUOT' AS record_type, GREATEST( NVL( pp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( pt.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( poh.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( poh1.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( line.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) 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, poh.po_header_id AS po_header_id, poh.segment1 AS quot_number, quotetype_lkup.meaning AS quot_type, quoteclass_lkup.meaning AS quot_class, quotestatus_lkup.meaning AS quot_status, poh1.po_header_id AS rfq_id, TO_CHAR(poh.po_header_id) AS ACTIVITY_ID, poh1.segment1 AS rfq_number, poh.start_date AS quot_effective_date, poh.end_date AS quot_expiration_date, poh.vendor_id AS supplier_id, pov.vendor_name AS supplier_name, poh.agent_id AS po_agent_id, prf.full_name AS po_agent_name, quotestatus_lkup.language AS language FROM pa_projects_all pp, pa_tasks pt, fnd_lookup_values quotestatus_lkup, fnd_lookup_values quotetype_lkup, fnd_lookup_values quoteclass_lkup, po_vendors pov, po_headers_all poh, po_headers_all poh1, po_lines_all line, per_all_people_f prf ,(select distinct project_id from pjm_project_parameters ) p3 WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date) and pp.project_id = p3.project_id AND pp.project_type <> 'AWARD_PROJECT' AND pp.template_flag = 'N' AND pp.project_id = line.project_id AND pt.task_id(+) = line.task_id AND quotestatus_lkup.lookup_type = 'RFQ/QUOTE STATUS' AND quotestatus_lkup.lookup_code = NVL(poh.status_lookup_code, 'I') AND quotestatus_lkup.view_application_id = 201 AND quotestatus_lkup.security_group_id = 0 AND quotetype_lkup.lookup_type = 'QUOTATION SUBTYPE' AND quotetype_lkup.lookup_code = NVL(poh.quote_type_lookup_code, 'STANDARD') AND quotetype_lkup.view_application_id = 201 AND quotetype_lkup.security_group_id = 0 AND quoteclass_lkup.lookup_type = 'QUOTATION CLASS' AND quoteclass_lkup.lookup_code = NVL(poh.quotation_class_code, 'CATALOG') AND quoteclass_lkup.view_application_id = 201 AND quoteclass_lkup.security_group_id = 0 AND poh1.po_header_id(+) = poh.from_header_id AND poh.type_lookup_code = 'QUOTATION' AND line.po_header_id = poh.po_header_id AND line.org_id = poh.org_id AND pov.vendor_id(+) = poh.vendor_id AND prf.person_id(+) = poh.agent_id AND trunc(SYSDATE) BETWEEN prf.effective_start_date AND prf.effective_end_date AND quotestatus_lkup.language = quotetype_lkup.language AND quotestatus_lkup.language = quoteclass_lkup.language AND quotestatus_lkup.language IN ('US') AND quotetype_lkup.language IN ('US') AND quoteclass_lkup.language IN ('US') ) PIVOT(MAX (quot_type) AS quot_type, MAX (quot_class) AS quot_class, MAX (quot_status) AS quot_status FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |