ECC Project Manufacturing, Procurement Activities, SQL1

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 /* PO */    'PROJ_PO' || '-' || dist.project_id || '-' || NVL( TO_CHAR(dist.task_id), 'NO_TASK' ) || '-' ||  poh.po_header_id || '-' || pol.po_line_id || '-' || poll.line_location_id || '-' || dist.po_distribution_id AS ecc_spec_id, 'PROJ_PO' AS record_type,  t.language AS LANGUAGE,  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( pol.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( poll.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( dist.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 po_number,  TO_CHAR(poh.po_header_id) AS ACTIVITY_ID,  t.type_name AS po_document_type, poh.revision_num AS po_revision_number, poh.revised_date AS po_revised_date,  poh.creation_date AS po_creation_date, plc.displayed_field AS po_status, poh.vendor_id AS supplier_id, pv.vendor_name AS supplier_name, poh.vendor_site_id AS supplier_site_id, pvs.vendor_site_code AS supplier_site_name,  poh.ship_to_location_id AS ship_to_location_id, hrl2.location_code AS ship_to_location_name,  poh.ship_via_lookup_code AS ship_via, plc2.displayed_field AS freight_term, plc3.displayed_field AS fob,  poh.agent_id AS po_agent_id, peo.full_name AS po_agent_name, poh.bill_to_location_id AS bill_to_location_id,  hrl3.location_code AS bill_to_location_name, poh.terms_id AS terms_id, term.name AS payment_term,  poh.currency_code AS currency, pol.po_line_id AS po_line_id, pol.line_num AS po_line_number,  plt.line_type AS po_line_type, hou2.name AS po_purchasing_org, SUBSTR( msi.segment1, 1, 40 ) AS item_number,  pol.item_revision AS item_revision,  NVL( msitl.description, pol.item_description ) AS item_description,  pol.unit_meas_lookup_code AS po_line_uom,  pol.quantity AS po_line_quantity, pol.unit_price AS po_line_price, poll.line_location_id AS po_shipment_id,  poll.shipment_num AS po_shipment_number, ood.organization_code AS po_shipment_org,  hrl.location_code AS po_shipment_location, poll.unit_meas_lookup_code AS po_shipment_uom,  poll.quantity AS po_shipment_quantity, poll.quantity_received AS po_shipment_quantity_received,  poll.quantity_accepted AS po_shipment_quantity_accepted, poll.quantity_rejected AS po_shipment_quantity_rejected,  poll.quantity_billed AS po_shipment_quantity_billed, poll.quantity_cancelled AS po_shipment_quantity_cancelled,  poll.promised_date AS po_shipment_promised_date, poll.need_by_date AS po_shipment_need_by_date,  dist.po_distribution_id AS po_distribution_id, dist.distribution_num AS po_distribution_number,  dist.quantity_ordered AS po_distribution_quantity, dist.end_item_unit_number AS end_item_unit_number,  DECODE( pol.matching_basis, 'QUANTITY', dist.quantity_ordered * poll.price_override, 'AMOUNT', dist.amount_ordered ) AS po_dist_amount,  dist.destination_organization_id AS po_dist_dest_organization_id, hou3.name AS po_dist_dest_org_name  FROM pa_projects_all pp, pa_tasks pt, po_headers_all poh, po_lines_all pol, po_line_locations_all poll,  po_distributions_all dist, po_line_types plt, hr_all_organization_units_tl hou2,  hr_all_organization_units_tl hou3, org_organization_definitions ood, hr_locations_all_tl hrl,  mtl_system_items_b msi,  mtl_system_items_tl msitl,  po_document_types_all_tl t, po_lookup_codes plc, po_vendors pv, po_vendor_sites_all pvs,  hr_locations_all_tl hrl2, po_lookup_codes plc2, po_lookup_codes plc3, hr_locations_all_tl hrl3,  ap_terms_tl term, per_all_people_f peo  ,(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 = dist.project_id  AND pt.task_id(+) = dist.task_id  AND poh.type_lookup_code = 'STANDARD'  AND poh.approved_flag IN ('Y')  AND pol.po_line_id = dist.po_line_id  AND poh.po_header_id = pol.po_header_id  AND dist.line_location_id = poll.line_location_id  AND pol.po_line_id = poll.po_line_id  AND plt.line_type_id(+) = pol.line_type_id  AND hou2.organization_id(+) = poh.org_id  AND hou3.organization_id(+) = dist.destination_organization_id  AND ood.organization_id(+) = poll.ship_to_organization_id  AND hrl.location_id(+) = poll.ship_to_location_id  AND msi.inventory_item_id(+) = pol.item_id  AND NVL( msi.organization_id, dist.destination_organization_id ) = dist.destination_organization_id  AND msitl.inventory_item_id(+) = msi.inventory_item_id  AND msitl.organization_id(+) = msi.organization_id   AND t.document_type_code IN ('PO', 'PA')  AND t.document_subtype = 'STANDARD'  AND t.org_id = poh.org_id  AND plc.lookup_code = NVL( poh.authorization_status, 'INCOMPLETE' )  AND plc.lookup_type = 'AUTHORIZATION STATUS'  AND dist.project_id IS NOT NULL  AND pv.vendor_id = poh.vendor_id  AND pvs.vendor_id = poh.vendor_id  AND pvs.vendor_site_id = poh.vendor_site_id  AND hrl2.location_id = poh.ship_to_location_id  AND plc2.lookup_code(+) = poh.freight_terms_lookup_code  AND plc2.lookup_type(+) = 'FREIGHT TERMS'  AND plc3.lookup_code(+) = poh.fob_lookup_code  AND plc3.lookup_type(+) = 'FOB'  AND peo.person_id = poh.agent_id   AND (trunc(SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date)  AND hrl3.location_id = poh.bill_to_location_id  AND term.term_id = poh.terms_id  AND t.language = term.language  AND term.language = hrl2.language  AND hrl2.language = hrl3.language  AND hrl3.language = NVL( hrl.language, hrl3.language )  AND term.language = NVL( hou3.language, term.language )  AND hrl2.language = NVL( hou2.language, hrl2.language )  AND t.language = nvl(msitl.language, t.language)  AND t.language IN ('US')  AND hrl.language (+) IN ('US')  AND hou3.language (+) IN ('US')  AND hou2.language (+) IN ('US')  AND msitl.language (+) IN ('US') )  PIVOT(MAX(po_purchasing_org) AS po_purchasing_org, MAX(po_dist_dest_org_name) AS po_dist_dest_org_name, MAX(po_shipment_location) AS po_shipment_location, MAX(po_document_type) AS po_document_type, MAX(ship_to_location_name) AS ship_to_location_name, MAX(bill_to_location_name) AS bill_to_location_name, MAX(payment_term) AS payment_term, MAX(item_description) AS item_description FOR LANGUAGE IN ('US' "US"))
) 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