Query to Find Requisition Header Info

Query to Find Requisition Header Info

SELECT prh.segment1              Requisition
,      psp.manual_req_num_type        req_num_type
,      ppf.full_name             Preparer
,      prh.creation_date         Creation_Date
,      prh.type_lookup_code
,      ppf1.full_name             Approver
,      t.type_name             Req_type
,      prh.description           Description
,      pah.note                  Note_To_Approver
,      prh.requisition_header_id Req_header
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 prh.REQUISITION_HEADER_ID=11675
and  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 nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE',
                                              'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN')   
--AND prh.segment1 = P_req_num_from
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.document_type_code  = 'REQUISITION'
AND    b.document_subtype    = prh.type_lookup_code
AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99)
AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ',
NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND T.LANGUAGE = USERENV('LANG')
/