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
Run ECC Project Manufacturing, Procurement Activities, SQL4 and other Oracle EBS reports with Blitz Report™ on our demo environment
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, polc1.displayed_field AS quot_type,  polc2.displayed_field AS quot_class, polc.displayed_field 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  FROM pa_projects_all pp, pa_tasks pt, po_lookup_codes polc, po_lookup_codes polc1, po_lookup_codes polc2, 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('','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 polc.lookup_code(+) = NVL( poh.status_lookup_code, 'I' )  AND polc.lookup_type = 'RFQ/QUOTE STATUS'  AND polc1.lookup_code(+) = NVL( poh.quote_type_lookup_code, 'STANDARD' )  AND polc1.lookup_type = 'QUOTATION SUBTYPE'  AND polc2.lookup_code(+) = NVL( poh.quotation_class_code, 'CATALOG' )  AND polc2.lookup_type = 'QUOTATION CLASS'  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   )  
) x
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where