ECC Project Manufacturing, Procurement Activities, SQL4

Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Project Manufacturing Procurement Details
Dataset Key: pjm-procurement
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where