ECC Project Manufacturing, Procurement Activities, SQL2
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
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
select x.* from ( select * from ( SELECT /* Req */ /*+ push_pred(req_total) index(pah) */ 'PROJ_REQ' || '-' || dist.project_id || '-' || NVL( TO_CHAR(dist.task_id), 'NO_TASK' ) || '-' || prh.requisition_header_id || '-' || line.requisition_line_id || '-' || dist.distribution_id AS ecc_spec_id, 'PROJ_REQ' AS record_type, hou2.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( prh.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( 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, prh.requisition_header_id AS req_header_id, prh.segment1 AS req_number, TO_CHAR(prh.requisition_header_id) AS ACTIVITY_ID, prh.creation_date AS req_creation_date, prh.description AS req_description, podt.type_name AS req_type, plc_sta.meaning AS req_approval_status, pah.employee_id AS req_approver_id, hr2.full_name AS req_approver, l1.meaning AS req_closed_flag, l2.meaning AS req_cancelled_flag, prh.preparer_id AS req_preparer_id, hrv.full_name AS req_preparer, sob.currency_code AS currency, DECODE( req_total.total, NULL , 0, req_total.total ) AS req_total, line.requisition_line_id AS req_line_id, line.line_num AS req_line_number, hou2.name AS req_line_requesting_org, line.item_revision AS req_line_item_rev, SUBSTR( msi.segment1, 1, 40 ) AS item_number, NVL( msitl.description, line.item_description ) AS item_description, line.quantity AS req_line_quantity, lineuom.unit_of_measure_tl AS uom, line.unit_price AS req_line_unit_price, DECODE( line.matching_basis, 'AMOUNT', line.amount, line.unit_price * (line.quantity - nvl(line.quantity_cancelled,0))) AS req_line_amount, DECODE( line.matching_basis, 'AMOUNT', dist.REQ_LINE_AMOUNT, dist.req_line_quantity * line.unit_price ) AS req_dist_amount, line.need_by_date AS req_line_need_by_date, NVL( line.quantity_delivered, 0 ) AS req_line_quantity_delivered, line.deliver_to_location_id AS req_line_location_id, hrl1.location_code AS req_line_location, line.destination_organization_id AS req_line_dest_organization_id, hou3.name AS req_line_dest_organization, DECODE( req_line_total.total, NULL , 0, req_line_total.total ) AS req_line_total, dist.distribution_id AS req_dist_id, dist.distribution_num AS req_dist_number, dist.req_line_quantity AS req_dist_quantity, dist.end_item_unit_number AS end_item_unit_number FROM pa_projects_all pp, pa_tasks pt, gl_sets_of_books sob, financials_system_params_all fsp , fnd_lookup_values plc_sta, po_document_types_all_tl podt, po_action_history pah, per_all_people_f hr2, per_all_people_f hrv, po_requisition_headers_all prh, po_req_distributions_all dist, po_requisition_lines_all line, mtl_system_items_b msi, mtl_system_items_tl msitl, hr_all_organization_units_tl hou2, hr_all_organization_units_tl hou3, hr_locations_all_tl hrl1, fnd_lookup_values l1, fnd_lookup_values l2, mtl_units_of_measure_tl lineuom, ( SELECT NVL( SUM( DECODE( quantity, NULL , amount, (quantity * unit_price) ) ) , 0 ) AS total, requisition_header_id FROM po_requisition_lines_all WHERE NVL( cancel_flag, 'N' ) <> 'Y' AND NVL( modified_by_agent_flag, 'N' ) = 'N' AND NVL( closed_code, 'OPEN' ) <> 'FINALLY CLOSED' GROUP BY requisition_header_id)req_total, ( SELECT NVL( DECODE( quantity, NULL , amount, (quantity * unit_price) ) , 0 ) AS total, requisition_line_id FROM po_requisition_lines_all WHERE NVL( cancel_flag, 'N' ) <> 'Y' AND NVL( modified_by_agent_flag, 'N' ) = 'N' AND NVL( closed_code, 'OPEN' ) <> 'FINALLY CLOSED')req_line_total ,(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 plc_sta.lookup_code = nvl(prh.authorization_status, 'INCOMPLETE') AND plc_sta.lookup_type = 'AUTHORIZATION STATUS' AND plc_sta.view_application_id = 201 AND plc_sta.security_group_id = 0 AND podt.document_type_code = 'REQUISITION' AND podt.org_id = prh.org_id AND podt.document_subtype = prh.type_lookup_code AND prh.preparer_id = hrv.person_id(+) AND trunc(SYSDATE) BETWEEN NVL( hrv.effective_start_date, SYSDATE -1 ) AND NVL( hrv.effective_end_date, SYSDATE +1 ) AND sob.set_of_books_id = fsp.set_of_books_id AND fsp.set_of_books_id = dist.set_of_books_id AND fsp.org_id = dist.org_id AND pah.object_type_code = 'REQUISITION' AND pah.object_id(+) = prh.requisition_header_id AND line.requisition_header_id = prh.requisition_header_id AND line.org_id = prh.org_id AND dist.requisition_line_id = line.requisition_line_id AND dist.org_id = line.org_id AND pah.sequence_num = ( SELECT /*+ push_subq */ MAX( pah2.sequence_num ) FROM po_action_history pah2 WHERE pah2.object_type_code = 'REQUISITION' AND pah2.object_id = prh.requisition_header_id ) AND pah.employee_id = hr2.person_id(+) AND trunc(SYSDATE) BETWEEN NVL( hr2.effective_start_date, SYSDATE -1 ) AND NVL( hr2.effective_end_date, SYSDATE +1 ) AND msi.organization_id(+) = line.destination_organization_id AND msi.inventory_item_id(+) = line.item_id AND msitl.inventory_item_id(+) = msi.inventory_item_id AND msitl.organization_id(+) = msi.organization_id AND hou2.organization_id(+) = line.org_id AND hrl1.location_id(+) = line.deliver_to_location_id AND hou3.organization_id(+) = line.destination_organization_id AND l1.lookup_type = 'YES/NO' AND l1.lookup_code = DECODE(prh.closed_code, NULL, 'N', 'CLOSED', 'Y', 'CLOSED FOR INVOICE', 'Y', 'CLOSED FOR RECEIVING', 'Y', 'FINALLY CLOSED', 'Y', 'N') AND l1.view_application_id = 201 AND l1.security_group_id = 0 AND l2.lookup_type = 'YES/NO' AND l2.lookup_code = nvl(prh.cancel_flag, 'N') AND l2.view_application_id = 201 AND l2.security_group_id = 0 AND prh.requisition_header_id = req_total.requisition_header_id(+) AND line.requisition_line_id = req_line_total.requisition_line_id(+) AND lineuom.unit_of_measure( + ) = line.unit_meas_lookup_code AND hou2.language = NVL( hrl1.language, hou2.language ) AND hou2.language = NVL( hou3.language, hou2.language ) AND hou2.language = nvl( msitl.language, hou2.language) AND hou2.language = podt.language AND hou2.language = l1.language AND hou2.language = l2.language AND hou2.language = plc_sta.language AND hou2.language = nvl(lineuom.language, hou2.language) AND hou2.language IN ('US') AND hrl1.language (+) IN ('US') AND hou3.language (+) IN ('US') AND msitl.language (+) IN ('US') AND podt.language IN ('US') AND l1.language IN ('US') AND l2.language IN ('US') AND plc_sta.language IN ('US') AND lineuom.language( + ) IN ('US') ) PIVOT( MAX(req_line_requesting_org) AS req_line_requesting_org, MAX(req_line_location) AS req_line_location, MAX(req_line_dest_organization) AS req_line_dest_organization, MAX(item_description) AS item_description, MAX(req_type) AS req_type, MAX(req_closed_flag) AS req_closed_flag, MAX(req_cancelled_flag) AS req_cancelled_flag, MAX(req_approval_status) AS req_approval_status, MAX(uom) AS uom FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |