ECC Project Manufacturing, Procurement Activities, SQL3
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
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
Run
ECC Project Manufacturing, Procurement Activities, SQL3 and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( select * from ( SELECT /* BR */ 'PROJ_BR' || '-' || dist.project_id || '-' || NVL( TO_CHAR(dist.task_id), 'NO_TASK' ) || '-' || por.po_header_id || '-' || por.po_release_id || '-' || pol.line_num || '-' || poll.shipment_num || '-' || dist.distribution_num AS ecc_spec_id, 'PROJ_BR' AS record_type, b.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')) , NVL( por.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, por.po_header_id AS po_header_id, poh.segment1 AS po_number, t.type_name AS br_document_type, por.po_release_id AS br_release_id, por.release_num AS br_release_number, TO_CHAR(por.po_header_id)|| ',' ||TO_CHAR(por.po_release_id) AS ACTIVITY_ID, pol.line_num AS po_line_number, poll.shipment_num AS po_shipment_number, dist.distribution_num AS po_distribution_number, por.revision_num AS po_revision_number, por.revised_date AS po_revised_date, por.release_date AS br_release_date, por.printed_date AS br_printed_date, po_status_lkup.meaning AS po_status, brclosestatus_lkup.meaning AS br_closure_status, poh.vendor_id AS supplier_id, pv.vendor_name AS supplier_name, poh.vendor_contact_id AS supplier_contact_id, DECODE(pvc.last_name||', '||pvc.first_name, ', ', NULL, pvc.last_name||', '||pvc.first_name) AS supplier_contact_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, hrl.location_code AS ship_to_location_name, shipvia_lkup.freight_code_tl AS ship_via, freight_lkup.meaning AS freight_terms, fob_lkup.meaning AS fob, por.agent_id AS po_agent_id, prf.full_name AS po_agent_name, poh.bill_to_location_id AS bill_to_location_id, hrl2.location_code AS bill_to_location_name, poh.terms_id AS terms_id, b.name AS payment_term, poh.currency_code AS currency, DECODE( pol.matching_basis, 'QUANTITY', dist.quantity_ordered * poll.price_override, 'AMOUNT', dist.amount_ordered ) AS po_dist_amount, SUBSTR( msi.segment1, 1, 40 ) AS item_number, NVL(msitl.description, pol.item_description) AS item_description, dist.end_item_unit_number AS end_item_unit_number FROM pa_projects_all pp, pa_tasks pt, po_releases_all por, po_headers_all poh, po_lines_all pol, po_line_locations_all poll, po_distributions_all dist, po_document_types_all_tl t, fnd_lookup_values po_status_lkup, fnd_lookup_values brclosestatus_lkup, po_vendors pv, po_vendor_contacts pvc, po_vendor_sites_all pvs, hr_locations_all_tl hrl, fnd_lookup_values freight_lkup, fnd_lookup_values fob_lkup, hr_locations_all_tl hrl2, per_all_people_f prf, ap_terms_tl b, mtl_system_items_b msi, mtl_system_items_tl msitl, org_freight_tl shipvia_lkup ,(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 = dist.project_id AND pt.task_id(+) = dist.task_id AND poh.po_header_id = por.po_header_id AND poh.type_lookup_code = 'BLANKET' AND por.approved_flag IN ('Y', 'R') AND dist.po_header_id = poh.po_header_id AND dist.po_release_id = por.po_release_id AND dist.po_line_id = pol.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 dist.org_id = pol.org_id AND pol.item_id IS NOT NULL AND t.document_type_code(+) = 'RELEASE' AND t.document_subtype(+) = por.release_type AND t.org_id(+) = por.org_id AND po_status_lkup.lookup_type = 'AUTHORIZATION STATUS' AND po_status_lkup.lookup_code = NVL( por.authorization_status, 'INCOMPLETE' ) AND po_status_lkup.view_application_id = 201 AND po_status_lkup.security_group_id = 0 AND brclosestatus_lkup.lookup_type = 'DOCUMENT STATE' AND brclosestatus_lkup.lookup_code = nvl(por.closed_code, 'OPEN') AND brclosestatus_lkup.view_application_id = 201 AND brclosestatus_lkup.security_group_id = 0 AND pv.vendor_id(+) = poh.vendor_id AND pvc.vendor_contact_id(+) = poh.vendor_contact_id AND pvc.vendor_site_id(+) = poh.vendor_site_id AND pvs.vendor_id(+) = poh.vendor_id AND pvs.vendor_site_id(+) = poh.vendor_site_id AND hrl.location_id(+) = poh.ship_to_location_id AND freight_lkup.lookup_type(+) = 'FREIGHT TERMS' AND freight_lkup.lookup_code(+) = poh.freight_terms_lookup_code AND freight_lkup.view_application_id(+) = 201 AND freight_lkup.security_group_id(+) = 0 AND fob_lkup.lookup_type(+) = 'FOB' AND fob_lkup.lookup_code(+) = poh.fob_lookup_code AND fob_lkup.view_application_id(+) = 201 AND fob_lkup.security_group_id(+) = 0 AND prf.person_id(+) = por.agent_id AND trunc(SYSDATE) BETWEEN prf.effective_start_date AND prf.effective_end_date AND hrl2.location_id(+) = poh.bill_to_location_id AND b.term_id = poh.terms_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 dist.project_id IS NOT NULL AND shipvia_lkup.organization_id(+) = poh.org_id AND shipvia_lkup.freight_code(+) = poh.ship_via_lookup_code AND b.language = NVL( hrl.language, b.language ) AND b.language = NVL( hrl2.language, b.language ) AND b.language = NVL( t.language, b.language ) AND b.language = nvl(msitl.language, b.language) AND b.language = po_status_lkup.language AND b.language = brclosestatus_lkup.language AND b.language = nvl(shipvia_lkup.language, b.language) AND b.language = nvl(freight_lkup.language, b.language) AND b.language = nvl(fob_lkup.language, b.language) AND b.language IN ('US') AND po_status_lkup.language IN ('US') AND brclosestatus_lkup.language IN ('US') AND shipvia_lkup.language(+) IN ('US') AND freight_lkup.language(+) IN ('US') AND fob_lkup.language(+) IN ('US') ) PIVOT(MAX(br_document_type) AS br_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, MAX (po_status) AS po_status, MAX (br_closure_status) AS br_closure_status, MAX (ship_via) AS ship_via ,MAX (freight_terms) AS freight_terms, MAX (fob) AS fob FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |