PO Printed Requisitions
Description
- Categories
- BI Publisher
- Application
- Purchasing
- Source
- Printed Requisitions Report (XML)
- Short Name
- POXPRREQ_XML
- DB package
- PO_POXPRREQ_XMLP_PKG
Imported from BI Publisher
Printed Requisitions Report
Printed Requisitions Report
SQL 345 lines
SELECT decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1) DECODE_PSP_MANUAL_REQ_NUM_TYPE , decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1), null) DECODE_PSP_MANUAL_REQ_NUM_TYP1 , prh.segment1 Requisition , psp.manual_req_num_type req_num_type , ppf.full_name Preparer , prh.creation_date Creation_Date , ppf1.full_name Approver , t.type_name Req_type , prh.description Description , pah.note Note_To_Approver , prh.requisition_header_id Req_header, PO_POXPRREQ_XMLP_PKG.c_total_amount_precision(:GL_CURRENCY, :Total_amount) C_TOTAL_AMOUNT_PRECISION FROM po_requisition_headers prh , per_people_f ppf1 , per_people_f ppf , po_action_history pah , po_system_parameters psp , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B WHERE NVL(PRH.contractor_requisition_flag, 'N') <> 'Y' AND prh.preparer_id = ppf.person_id AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0) from financials_system_parameters fsp) AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate)) AND trunc(sysdate) BETWEEN nvl(ppf1.effective_start_date, trunc(sysdate)) AND nvl(ppf1.effective_end_date, trunc(sysdate)) -- AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE', -- 'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN') AND :P_single_req_print = 1 AND prh.segment1 = :P_req_num_from AND b.document_type_code = 'REQUISITION' AND b.document_subtype = prh.type_lookup_code AND EXISTS (SELECT null FROM po_requisition_lines prl WHERE prl.requisition_header_id = prh.requisition_header_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED') AND pah.object_id = prh.requisition_header_id AND pah.employee_id = ppf1.person_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code AND pah.sequence_num = (SELECT max(sequence_num) FROM po_action_history pah WHERE pah.object_id = prh.requisition_header_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code) and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = prh.ORG_ID AND T.LANGUAGE = USERENV('LANG') UNION ALL SELECT decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1) , decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1), null) , prh.segment1 Requisition , psp.manual_req_num_type req_num_type , ppf.full_name Preparer , prh.creation_date Creation_Date , ppf1.full_name Approver , t.type_name Req_type , prh.description Description , pah.note Note_To_Approver , prh.requisition_header_id Req_header, PO_POXPRREQ_XMLP_PKG.c_total_amount_precision(:GL_CURRENCY, :Total_amount) C_TOTAL_AMOUNT_PRECISION FROM po_requisition_headers prh , per_people_f ppf1 , per_people_f ppf , po_action_history pah , po_system_parameters psp , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B WHERE NVL(PRH.contractor_requisition_flag, 'N') <> 'Y' AND prh.preparer_id = ppf.person_id AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0) from financials_system_parameters fsp) AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate)) AND trunc(sysdate) BETWEEN nvl(ppf1.effective_start_date, trunc(sysdate)) AND nvl(ppf1.effective_end_date, trunc(sysdate)) -- AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE', -- 'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN') AND :P_single_req_print != 1 AND psp.manual_req_num_type = 'NUMERIC' AND decode(rtrim(prh.segment1,'0123456789'),NULL,to_number(prh.segment1),-1) BETWEEN decode(rtrim(nvl(:P_req_num_from,prh.segment1),'0123456789'),NULL, to_number(nvl(:P_req_num_from,prh.segment1)),-1) AND decode(rtrim(nvl(:P_req_num_to,prh.segment1),'0123456789'),NULL, to_number(nvl(:P_req_num_to,prh.segment1)),-1) AND b.document_type_code = 'REQUISITION' AND b.document_subtype = prh.type_lookup_code AND EXISTS (SELECT null FROM po_requisition_lines prl WHERE prl.requisition_header_id = prh.requisition_header_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED') AND pah.object_id = prh.requisition_header_id AND pah.employee_id = ppf1.person_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code AND pah.sequence_num = (SELECT max(sequence_num) FROM po_action_history pah WHERE pah.object_id = prh.requisition_header_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code) and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = prh.ORG_ID AND T.LANGUAGE = USERENV('LANG') UNION ALL SELECT decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1) , decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1), null) , prh.segment1 Requisition , psp.manual_req_num_type req_num_type , ppf.full_name Preparer , prh.creation_date Creation_Date , ppf1.full_name Approver , t.type_name Req_type , prh.description Description , pah.note Note_To_Approver , prh.requisition_header_id Req_header, PO_POXPRREQ_XMLP_PKG.c_total_amount_precision(:GL_CURRENCY, :Total_amount) C_TOTAL_AMOUNT_PRECISION FROM po_requisition_headers prh , per_people_f ppf1 , per_people_f ppf , po_action_history pah , po_system_parameters psp , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B WHERE NVL(PRH.contractor_requisition_flag, 'N') <> 'Y' AND prh.preparer_id = ppf.person_id AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0) from financials_system_parameters fsp) AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate)) AND trunc(sysdate) BETWEEN nvl(ppf1.effective_start_date, trunc(sysdate)) AND nvl(ppf1.effective_end_date, trunc(sysdate)) -- AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE', -- 'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN') AND :P_single_req_print != 1 AND psp.manual_req_num_type = 'ALPHANUMERIC' AND :P_req_num_from IS NOT NULL AND :P_req_num_to IS NOT NULL AND prh.segment1 >= :P_req_num_from AND prh.segment1 <= :P_req_num_to AND b.document_type_code = 'REQUISITION' AND b.document_subtype = prh.type_lookup_code AND EXISTS (SELECT null FROM po_requisition_lines prl WHERE prl.requisition_header_id = prh.requisition_header_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED') AND pah.object_id = prh.requisition_header_id AND pah.employee_id = ppf1.person_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code AND pah.sequence_num = (SELECT max(sequence_num) FROM po_action_history pah WHERE pah.object_id = prh.requisition_header_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code) and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = prh.ORG_ID AND T.LANGUAGE = USERENV('LANG') UNION ALL SELECT decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1) , decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1), null) , prh.segment1 Requisition , psp.manual_req_num_type req_num_type , ppf.full_name Preparer , prh.creation_date Creation_Date , ppf1.full_name Approver , t.type_name Req_type , prh.description Description , pah.note Note_To_Approver , prh.requisition_header_id Req_header, PO_POXPRREQ_XMLP_PKG.c_total_amount_precision(:GL_CURRENCY, :Total_amount) C_TOTAL_AMOUNT_PRECISION FROM po_requisition_headers prh , per_people_f ppf1 , per_people_f ppf , po_action_history pah , po_system_parameters psp , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B WHERE NVL(PRH.contractor_requisition_flag, 'N') <> 'Y' AND prh.preparer_id = ppf.person_id AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0) from financials_system_parameters fsp) AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate)) AND trunc(sysdate) BETWEEN nvl(ppf1.effective_start_date, trunc(sysdate)) AND nvl(ppf1.effective_end_date, trunc(sysdate)) -- AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE', -- 'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN') AND :P_single_req_print != 1 AND psp.manual_req_num_type = 'ALPHANUMERIC' AND :P_req_num_from IS NOT NULL AND :P_req_num_to IS NULL AND prh.segment1 >= :P_req_num_from AND b.document_type_code = 'REQUISITION' AND b.document_subtype = prh.type_lookup_code AND EXISTS (SELECT null FROM po_requisition_lines prl WHERE prl.requisition_header_id = prh.requisition_header_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED') AND pah.object_id = prh.requisition_header_id AND pah.employee_id = ppf1.person_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code AND pah.sequence_num = (SELECT max(sequence_num) FROM po_action_history pah WHERE pah.object_id = prh.requisition_header_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code) and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = prh.ORG_ID AND T.LANGUAGE = USERENV('LANG') UNION ALL SELECT decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1) , decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1), null) , prh.segment1 Requisition , psp.manual_req_num_type req_num_type , ppf.full_name Preparer , prh.creation_date Creation_Date , ppf1.full_name Approver , t.type_name Req_type , prh.description Description , pah.note Note_To_Approver , prh.requisition_header_id Req_header, PO_POXPRREQ_XMLP_PKG.c_total_amount_precision(:GL_CURRENCY, :Total_amount) C_TOTAL_AMOUNT_PRECISION FROM po_requisition_headers prh , per_people_f ppf1 , per_people_f ppf , po_action_history pah , po_system_parameters psp , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B WHERE NVL(PRH.contractor_requisition_flag, 'N') <> 'Y' AND prh.preparer_id = ppf.person_id AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0) from financials_system_parameters fsp) AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate)) AND trunc(sysdate) BETWEEN nvl(ppf1.effective_start_date, trunc(sysdate)) AND nvl(ppf1.effective_end_date, trunc(sysdate)) -- AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE', -- 'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN') AND :P_single_req_print != 1 AND psp.manual_req_num_type = 'ALPHANUMERIC' AND :P_req_num_from IS NULL AND :P_req_num_to IS NOT NULL AND prh.segment1 <= :P_req_num_to AND b.document_type_code = 'REQUISITION' AND b.document_subtype = prh.type_lookup_code AND EXISTS (SELECT null FROM po_requisition_lines prl WHERE prl.requisition_header_id = prh.requisition_header_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED') AND pah.object_id = prh.requisition_header_id AND pah.employee_id = ppf1.person_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code AND pah.sequence_num = (SELECT max(sequence_num) FROM po_action_history pah WHERE pah.object_id = prh.requisition_header_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code) and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = prh.ORG_ID AND T.LANGUAGE = USERENV('LANG') UNION ALL SELECT decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1) , decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1), null) , prh.segment1 Requisition , psp.manual_req_num_type req_num_type , ppf.full_name Preparer , prh.creation_date Creation_Date , ppf1.full_name Approver , t.type_name Req_type , prh.description Description , pah.note Note_To_Approver , prh.requisition_header_id Req_header, PO_POXPRREQ_XMLP_PKG.c_total_amount_precision(:GL_CURRENCY, :Total_amount) C_TOTAL_AMOUNT_PRECISION FROM po_requisition_headers prh , per_people_f ppf1 , per_people_f ppf , po_action_history pah , po_system_parameters psp , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B WHERE NVL(PRH.contractor_requisition_flag, 'N') <> 'Y' AND prh.preparer_id = ppf.person_id AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0) from financials_system_parameters fsp) AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate)) AND trunc(sysdate) BETWEEN nvl(ppf1.effective_start_date, trunc(sysdate)) AND nvl(ppf1.effective_end_date, trunc(sysdate)) -- AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE', -- 'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN') AND :P_single_req_print != 1 AND psp.manual_req_num_type = 'ALPHANUMERIC' AND :P_req_num_from IS NULL AND :P_req_num_to IS NULL AND b.document_type_code = 'REQUISITION' AND b.document_subtype = prh.type_lookup_code AND EXISTS (SELECT null FROM po_requisition_lines prl WHERE prl.requisition_header_id = prh.requisition_header_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED') AND pah.object_id = prh.requisition_header_id AND pah.employee_id = ppf1.person_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code AND pah.sequence_num = (SELECT max(sequence_num) FROM po_action_history pah WHERE pah.object_id = prh.requisition_header_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code) and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = prh.ORG_ID AND T.LANGUAGE = USERENV('LANG') ORDER BY 1,2 |
Parameters 4
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| Requisition Numbers From | LOV Oracle | |
| To | LOV Oracle | |
| Dynamic Precision Option | LOV Oracle |