ECC Project Manufacturing, Procurement Activities, SQL4

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Record Type, Ecc Last Update Date, Project Id, Project Number, Org Id, Task Id, Task Number, Po Header Id, Quot Number ...
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
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, 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
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