PO Printed Requisitions

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Printed Requisitions Report
Application: Purchasing
Source: Printed Requisitions Report (XML)
Short Name: POXPRREQ_XML
DB package: PO_POXPRREQ_XMLP_PKG
Run PO Printed Requisitions and other Oracle EBS reports with Blitz Report™ on our demo environment
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