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

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
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV