ECC Procurement, Requisitions
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: po-pcc-requisitions
Query Procedure: PO_PCC_REQUISITIONS_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesReq
Dataset Key: po-pcc-requisitions
Query Procedure: PO_PCC_REQUISITIONS_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PO_PCC_DATASECURITY_PKG_PUB.GetFilterAttributeValuesReq
Run
ECC Procurement, Requisitions and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( select * from ( SELECT req_header.requisition_header_id || '-' || req_line.requisition_line_id || '-' || req_dist.distribution_Id AS ecc_spec_id, DFV_H.*, DFV_L.*, req_header.requisition_header_id requisition_header_id, req_header.segment1 requisition, req_header.description description, CASE WHEN req_header.closed_code = 'CLOSED' THEN fnd_message.Get_string('PO', 'PO_STATUS_CLOSED') WHEN req_header.closed_code = 'FINALLY CLOSED' THEN fnd_message.Get_string('PO', 'PO_STATUS_FINALLY_CLOSED') WHEN NVL(req_header.cancel_flag, 'N') = 'Y' THEN fnd_message.Get_string('PO', 'PO_STATUS_CANCELED') ELSE status_lkup.meaning END status, status_lkup.lookup_code requisition_status_code, status_lkup.LANGUAGE status_language, nvl((SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id = req_line.suggested_buyer_id AND emp.effective_start_date = (SELECT Max(c.effective_start_date) FROM per_all_people_f c WHERE emp.person_id = c.person_id) and rownum < 2),po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_BUYER_UNASSIGNED', '201',status_lkup.language)) buyer_name, req_header.preparer_id, ( SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id = req_header.preparer_id AND emp.effective_start_date = (SELECT Max(c.effective_start_date) FROM per_all_people_f c WHERE emp.person_id = c.person_id) and rownum <2 ) preparer , /*por_view_reqs_pkg.get_req_total(req_header.requisition_header_id) amount ,*/ ( select nvl(SUM(decode(prl.matching_basis, 'AMOUNT', prl.amount, prl.unit_price * (prl.quantity - nvl(prl.quantity_cancelled,0)))),0) from po_requisition_lines_all prl where prl.requisition_header_id = req_header.requisition_header_id and nvl(prl.modified_by_agent_flag, 'N') = 'N' and nvl(prl.cancel_flag, 'N') = 'N') amount, (SELECT sob.currency_code FROM gl_sets_of_books sob,financials_system_params_all fsp WHERE sob.set_of_books_id = fsp.set_of_books_id AND fsp.org_id = req_header.org_id) AS currency , -- functional currency (SELECT description FROM fnd_lookup_values WHERE lookup_type = 'REQUISITION TYPE' AND lookup_code = req_header.type_lookup_code AND LANGUAGE = status_lkup.LANGUAGE ) requisition_type , req_header.creation_date creation_date , req_header.approved_date approved_date , EXTRACT( YEAR FROM approved_date ) approved_date_year, To_Char(approved_date,'Month') approved_date_month, To_Char(NULL) approved_date_range, (SELECT houtl.name FROM hr_all_organization_units_tl houtl WHERE houtl.organization_id (+) = req_header.org_id AND houtl.LANGUAGE = status_lkup.LANGUAGE ) operating_unit , req_header.org_id org_id, req_line.requisition_line_id requisition_line_id, req_line.item_id, req_line.line_num line_number, mtl_sys_item.concatenated_segments item, req_line.item_description item_description, req_line.category_id, req_mtl_cat.concatenated_segments category, req_line.quantity quantity, req_line.quantity - req_line.quantity_cancelled requested_quantity, req_line.need_by_date need_by_date, (req_line.need_by_date - nvl(((SELECT nvl(lead_time,0) FROM icx_cat_attribute_values WHERE po_line_id = (SELECT po_line_id FROM po_lines_all WHERE po_header_id = req_line.BLANKET_PO_HEADER_ID AND line_num = req_line.BLANKET_PO_LINE_NUM AND ROWNUM =1) AND rownum<2)), 0)) order_by_date, EXTRACT( YEAR FROM need_by_date ) need_by_date_year, To_Char(need_by_date,'Month') need_by_date_month, NVL(req_line.REQS_IN_POOL_FLAG,'N') reqs_in_pool_flag, req_line.unit_meas_lookup_code uom_code, req_line.unit_meas_lookup_code uom, req_line.unit_price unit_price, req_line.line_type_id, (SELECT line_type FROM po_line_types_tl WHERE line_type_id = req_line.line_type_id AND LANGUAGE = status_lkup.language) line_type , req_line.matching_basis, req_line.item_revision item_revision , DECODE(req_line.matching_basis, 'AMOUNT', req_line.amount, (req_line.quantity-NVL(req_line.quantity_cancelled,0)) * req_line.unit_price ) line_amount , Nvl2(req_line.currency_code,req_line.currency_code, (SELECT sob.currency_code FROM gl_sets_of_books sob,financials_system_params_all fsp WHERE sob.set_of_books_id = fsp.set_of_books_id AND fsp.org_id = req_header.org_id)) line_currency, (SELECT gsb.currency_code FROM financials_system_params_all fsp, gl_sets_of_books gsb WHERE fsp.set_of_books_id = gsb.set_of_books_id AND fsp.org_id = req_line.org_id ) line_functional_currency , (CASE WHEN req_line.MODIFIED_BY_AGENT_FLAG = 'Y' THEN 0 WHEN req_line.currency_code IS NOT NULL AND req_line.matching_basis = 'AMOUNT' THEN Nvl(req_line.currency_amount, req_line.amount) WHEN req_line.currency_code IS NOT NULL AND req_line.matching_basis <> 'AMOUNT' THEN (Nvl(req_line.currency_unit_price,req_line.unit_price) * (req_line.quantity - nvl(req_line.quantity_cancelled,0))) WHEN req_line.matching_basis = 'AMOUNT' THEN req_line.amount ELSE req_line.unit_price *(req_line.quantity - nvl(req_line.quantity_cancelled,0)) END) line_currency_amount , req_header.cancel_flag, req_line.cancel_flag line_cancel_flag, req_header.closed_code, req_line.closed_code line_closed_code, req_line.modified_by_agent_flag, ( CASE WHEN req_line.cancel_flag='Y' THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQ_CANCELLED', '201',status_lkup.language) WHEN nvl(req_line.urgent_flag, 'N') = 'Y' AND req_line.REQS_IN_POOL_FLAG='Y' THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_URGENT', '201',status_lkup.language) WHEN req_line.REQS_IN_POOL_FLAG='Y' AND Trunc(req_line.need_by_date) < Trunc(SYSDATE + (SELECT nvl(lead_time,0) FROM icx_cat_attribute_values WHERE po_line_id = (SELECT po_line_id FROM po_lines_all WHERE po_header_id = req_line.BLANKET_PO_HEADER_ID AND line_num = req_line.BLANKET_PO_LINE_NUM AND ROWNUM =1) AND rownum<2)) THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_LATE2', '201',status_lkup.language) WHEN req_line.REQS_IN_POOL_FLAG='Y' AND req_line.need_by_date <= SYSDATE THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_LATE', '201',status_lkup.language) WHEN req_line.reqs_in_pool_flag = 'Y' THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_POOL', '201',status_lkup.language) WHEN req_line.at_sourcing_flag = 'Y' THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_NEG', '201',status_lkup.language) WHEN (SELECT Count(1) FROM po_distributions_all pod WHERE pod.REQ_DISTRIBUTION_ID IN (SELECT distribution_Id FROM po_req_distributions_all WHERE requisition_line_id = req_line.requisition_line_id) AND ROWNUM < 2) > 0 THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_ORDER', '201',status_lkup.language) ELSE (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'AUTHORIZATION STATUS' AND lookup_code = req_header.authorization_status AND LANGUAGE = status_lkup.language) END ) line_status , req_line.to_person_id requester_id, ( SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id = req_line.to_person_id AND emp.effective_start_date = (SELECT Max(c.effective_start_date) FROM per_all_people_f c WHERE emp.person_id = c.person_id) and rownum<2 ) requester, (SELECT SUM(Nvl(prd1.recoverable_tax,0)) FROM po_requisition_lines_all prl1, po_req_distributions_all prd1 WHERE prd1.requisition_line_id = req_line.requisition_line_id AND prl1.requisition_line_id = prd1.requisition_line_id AND Nvl(prl1.cancel_flag, 'N') = 'N' AND Nvl(prl1.modified_by_agent_flag, 'N') = 'N' ) recoverable_tax , (SELECT SUM(Nvl(prd1.nonrecoverable_tax,0)) FROM po_requisition_lines_all prl1, po_req_distributions_all prd1 WHERE prd1.requisition_line_id = req_line.requisition_line_id AND prl1.requisition_line_id = prd1.requisition_line_id AND Nvl(prl1.cancel_flag, 'N') = 'N' AND Nvl(prl1.modified_by_agent_flag, 'N') = 'N' ) non_recoverable_tax , req_line.suggested_buyer_id, ( SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id =req_line.suggested_buyer_id AND emp.effective_start_date = (SELECT Max(c.effective_start_date) FROM per_all_people_f c WHERE emp.person_id = c.person_id ) and rownum<2 ) suggested_buyer , req_line.note_to_agent note_to_buyer, req_line.suggested_vendor_name suggested_supplier , (SELECT description FROM hr_locations_all_tl WHERE location_id = req_line.deliver_to_location_id AND LANGUAGE = status_lkup.language ) deliver_to_location , req_line.deliver_to_location_id, (SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL_TL WHERE location_id = req_line.deliver_to_location_id and language = status_lkup.language) ship_to_location, req_line.destination_type_code, (SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'DESTINATION TYPE' AND lookup_code = req_line.destination_type_code AND LANGUAGE = status_lkup.language ) destination_type , nvl(req_line.urgent_flag, 'N') urgent_flag, req_line.last_update_date req_last_upd_date, Decode(req_header.type_lookup_code, 'INTERNAL', (SELECT to_char(oe1.order_number) FROM oe_order_headers_all oe1 WHERE oe1.order_source_id = 10 AND oe1.orig_sys_document_ref = req_header.segment1 AND oe1.source_document_id = req_header.requisition_header_id AND rownum=1), po_v.segment1) AS purchase_order, po_v.po_header_id, req_line.blanket_po_header_id, (SELECT segment1 FROM po_headers_all WHERE po_header_id = req_line.blanket_po_header_id) AS agreement, (SELECT document_number FROM pon_auction_Headers_all WHERE auction_header_id = req_line.auction_header_id) AS negotiation, To_Char(NULL) next_action_owner, To_Char(NULL) next_action_type, case when prl_reserved_status.unreserved_count=0 and prl_reserved_status.reserved_count>0 then (SELECT meaning FROM fnd_lookup_values POLC WHERE POLC.lookup_type = 'DOCUMENT STATE' AND POLC.lookup_code = 'RESERVED' AND LANGUAGE = status_lkup.language) else (SELECT meaning FROM fnd_lookup_values POLC WHERE POLC.lookup_type = 'DOCUMENT STATE' AND POLC.lookup_code = 'NOT RESERVED' AND LANGUAGE = status_lkup.language) end AS reservation_status, req_dist.distribution_id distribution_id, req_header.segment1 || ', ' || req_line.line_Num requisition_line, req_dist.distribution_num distribution_number, req_dist.req_line_quantity distribution_quantity, req_dist.code_combination_id, (SELECT concatenated_segments FROM gl_code_combinations_kfv gl_code_kfv WHERE code_combination_id = req_dist.code_combination_id ) charge_account , req_dist.expenditure_type expenditure_type, (SELECT name FROM hr_all_organization_units_tl WHERE organization_id=req_dist.expenditure_organization_id AND LANGUAGE = status_lkup.language) expenditure_org, req_dist.expenditure_item_date expenditure_item_date, pa_project.name project , pa_project.project_id, pa_project.project_currency_code project_currency_code, pa_project.segment1 project_number, pa_task.task_number task_number, pa_task.task_id, To_Char(NULL) project_status, DECODE(req_line.requisition_line_id,NULL,NULL,pa_task.task_name ) task_name, DECODE(nvl(req_header.contractor_requisition_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',status_lkup.language), PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',status_lkup.language)) contractor_requisition_flag, (SELECT name FROM hr_all_organization_units_tl WHERE organization_id=req_line.destination_organization_id AND LANGUAGE = status_lkup.language) destination_organization FROM po_requisition_headers_all req_header, po_requisition_lines_all req_line, po_req_distributions_all req_dist, pa_projects_all pa_project, pa_tasks pa_task, mtl_system_items_b_kfv mtl_sys_item, ap_suppliers ap_supplier, mtl_categories_kfv req_mtl_cat, fnd_lookup_values status_lkup, (SELECT poh.segment1,poh.po_header_id, pod.REQ_DISTRIBUTION_ID FROM po_distributions_all pod, po_headers_all poh WHERE pod.po_header_id = poh.po_header_id AND poh.type_lookup_code = 'STANDARD' ) po_v, ORG_ORGANIZATION_DEFINITIONS OOD, PO_SYSTEM_PARAMETERS_ALL PSP1, (SELECT PRL1.requisition_line_id, COUNT( DECODE( PRD1.prevent_encumbrance_flag , 'Y', NULL , DECODE( PRD1.encumbered_flag , 'Y', 'Y', NULL ) ) ) reserved_count, COUNT( DECODE( PRD1.prevent_encumbrance_flag , 'Y', NULL , DECODE( PRD1.encumbered_flag , 'Y', NULL, 'N'))) unreserved_count, COUNT( DECODE( PRD1.prevent_encumbrance_flag , 'Y', 'Y', NULL)) prevented_count FROM PO_REQ_DISTRIBUTIONS_ALL PRD1 , PO_REQUISITION_LINES_ALL PRL1 WHERE PRL1.requisition_line_id = PRD1.requisition_line_id AND NVL(PRL1.cancel_flag,'N') <> 'Y' AND NVL(PRL1.closed_code,'OPEN') <> 'FINALLY CLOSED' GROUP BY PRL1.requisition_line_id)prl_reserved_status ,(select "ROW_ID" "'PO_REQ_H_ROW_ID'","CONTEXT" "'PO_REQ_H_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_REQ_H_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_REQUISITION_HEADERS_ALL )) DFV_H ,(select "ROW_ID" "'PO_REQ_L_ROW_ID'","CONTEXT" "'PO_REQ_L_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_REQ_L_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_REQUISITION_LINES_ALL )) DFV_L WHERE req_header.creation_date >= nvl(to_date(to_char(to_timestamp(''),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS'),req_header.creation_date) and req_header.authorization_status = 'APPROVED' and nvl(req_header.closed_code,'NO') <> 'FINALLY CLOSED' and NVL(req_header.cancel_flag, 'N') <> 'Y' and nvl(req_header.federal_flag,'N') = 'N' AND req_header.requisition_header_id = req_line.requisition_header_id and nvl(req_line.cancel_flag,'N') <> 'Y' AND Nvl(req_line.modified_by_agent_flag,'N') <> 'Y' AND Nvl(req_line.closed_code,'NO') NOT IN ('CLOSED','FINALLY CLOSED') AND req_line.requisition_line_id = req_dist.requisition_line_id(+) and req_dist.task_id = pa_task.task_id (+) and req_dist.project_id = pa_task.project_id (+) and pa_task.project_id = pa_project.project_id(+) AND NVL(req_header.authorization_status,'INCOMPLETE') = status_lkup.lookup_code AND status_lkup.lookup_type = 'AUTHORIZATION STATUS' AND status_lkup.LANGUAGE IN ('US') and req_line.vendor_id = ap_supplier.vendor_id(+) and req_line.category_id = req_mtl_cat.category_id(+) --req line category to req mtl category and req_line.item_id = mtl_sys_item.inventory_item_id(+) --req line item to mtl system inv item and req_line.destination_organization_id = mtl_sys_item.organization_id(+) AND po_v.REQ_DISTRIBUTION_ID(+) = req_dist.distribution_id AND OOD.ORGANIZATION_ID(+) = req_line.SOURCE_ORGANIZATION_ID AND PSP1.ORG_ID(+) = OOD.OPERATING_UNIT /*AND (nvl(req_header.CONTRACTOR_REQUISITION_FLAG,'N')='N' or (req_header.AUTHORIZATION_STATUS='APPROVED' and req_header.CONTRACTOR_STATUS='ASSIGNED'))*/ AND prl_reserved_status.requisition_line_id(+) = req_line.requisition_line_id AND req_header.rowid = dfv_h."'PO_REQ_H_ROW_ID'" (+) AND req_line.rowid = dfv_l."'PO_REQ_L_ROW_ID'" (+) ) pivot(Max(status) AS status, Max(operating_unit) AS operating_unit, Max(buyer_name) AS buyer_name, Max(line_type) AS line_type, Max(line_status) AS line_status, Max(requisition_type) AS requisition_type, Max(deliver_to_location) AS deliver_to_location, Max(ship_to_location) AS ship_to_location, Max(destination_type) AS destination_type, Max(expenditure_org) AS expenditure_org, Max(reservation_status) AS reservation_status, Max(contractor_requisition_flag) AS contractor_requisition_flag, Max(destination_organization) AS destination_organization FOR status_language IN ('US' "US")) ) x where 2=2 |
Parameter Name |
---|