ECC Project Manufacturing, Procurement Activities, SQL5

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
select
x.*
from
(
select * from ( SELECT /* RFQ */    'PROJ_RFQ' || '-' ||  line.project_id || '-' ||   NVL( TO_CHAR(line.task_id), 'NO_TASK' ) || '-' ||  poh.po_header_id || '-' ||  line.po_line_id || '-' ||   NVL(TO_CHAR(rfqv.vendor_id), 'NO_SUPP' ) || '-' ||  NVL(TO_CHAR(rfqv.vendor_site_id), 'NO_SITE' ) AS ecc_spec_id,  'PROJ_RFQ' 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( line.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( rfqv.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 rfq_id, poh.segment1 AS rfq_number,  TO_CHAR(poh.po_header_id) AS ACTIVITY_ID,  polc.displayed_field AS rfq_status, poh.agent_id AS po_agent_id,  prf.full_name AS po_agent_name, rfqv.vendor_id AS supplier_id, pov.vendor_name AS supplier_name,  rfqv.vendor_site_id AS supplier_site_id, pvs.vendor_site_code AS supplier_site_name, rfqv.vendor_contact_id AS supplier_contact_id,  DECODE( rfqv.vendor_contact_id, NULL , NULL , pvc.last_name || ' , ' || pvc.first_name ) AS supplier_contact_name,  poh.reply_date AS rfq_reply_date, rfq_close_date AS rfq_close_date  FROM pa_projects_all pp, pa_tasks pt, po_lookup_codes polc, po_headers_all poh, po_lines_all line,  po_vendors pov, po_rfq_vendors rfqv, po_vendor_sites_all pvs, ( SELECT c.vendor_contact_id vendor_contact_id, SUBSTR( hp.person_first_name, 1, 15 ) first_name,  SUBSTR( hp.person_middle_name, 1, 15 ) middle_name, SUBSTR( hp.person_last_name, 1, 15 ) last_name,  NVL( hp.person_pre_name_adjunct, hp.salutation ) prefix, SUBSTR( hp.person_title, 30 ) title  FROM ap_supplier_contacts c, hz_parties hp  WHERE c.per_party_id = hp.party_id  ) pvc, 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 poh.type_lookup_code = 'RFQ'  AND line.po_header_id = poh.po_header_id  AND rfqv.po_header_id(+) = poh.po_header_id  AND pov.vendor_id(+) = rfqv.vendor_id  AND pvs.vendor_site_id(+) = rfqv.vendor_site_id  AND pvc.vendor_contact_id(+) = rfqv.vendor_contact_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