PO Requisition Distribution Detail

SELECT prh.segment1               Req_Number
,      trunc(prh.creation_date)          Creation_Date 
,      papf.full_name              Preparer
,      prh.description            Description
,      prh.requisition_header_id requisition_header_id_header
FROM   po_requisition_headers prh
,     per_all_people_f   papf 
,      po_system_parameters_all   psp
WHERE  prh.preparer_id = papf.person_id
AND papf.business_group_id = (select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp)
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id,papf.person_type_id,papf.employee_number,papf.applicant_number
,papf.npw_number ))='TRUE' and              
decode(hr_general.get_xbg_profile,'Y',papf.business_group_id,hr_general.get_business_group_id)=papf.business_group_id
AND    trunc(prh.creation_date) 
       BETWEEN
       nvl(:P_date_from,trunc(prh.creation_date) - 1) 
       AND 
       nvl(:P_date_to,trunc(prh.creation_date) + 1)
&C_WHERE_REQ_NUM
AND     papf.full_name = nvl(:P_preparer, papf.full_name)
AND     prh.type_lookup_code in ('INTERNAL','PURCHASE')
AND   psp.ORG_ID = prh.ORG_ID 
AND EXISTS (SELECT 'requisition line exists'
            FROM   po_req_distributions        prd
            ,      po_requisition_lines        prl
            ,      po_lookup_codes             plc2
            &from_failed_f 
            WHERE  prh.requisition_header_id = prl.requisition_header_id
            AND    prl.requisition_line_id   = prd.requisition_line_id
            AND    prl.source_type_code      = plc2.lookup_code
            &where_failed_f
            AND    plc2.lookup_type              = 'REQUISITION SOURCE TYPE')
AND NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'                 
ORDER BY
decode(psp.manual_req_num_type,'NUMERIC',decode(rtrim(prh.segment1,'0123456789'),NULL,
to_number(prh.segment1),null)),
 decode(psp.manual_req_num_type,'ALPHANUMERIC',prh.segment1,null)

SELECT prh.segment1 Req_Number , trunc(prh.creation_date) Creation_Date , papf.full_name Preparer , prh.description Description , prh.requisition_header_id requisition_header_id_header FROM po_requisition_headers prh , per_all_people_f papf , po_system_parameters_all psp WHERE prh.preparer_id = papf.person_id AND papf.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id,papf.person_type_id,papf.employee_number,papf.applicant_number ,papf.npw_number ))='TRUE' and decode(hr_general.get_xbg_profile,'Y',papf.business_group_id,hr_general.get_business_group_id)=papf.business_group_id AND trunc(prh.creation_date) BETWEEN nvl(:P_date_from,trunc(prh.creation_date) - 1) AND nvl(:P_date_to,trunc(prh.creation_date) + 1) &C_WHERE_REQ_NUM AND papf.full_name = nvl(:P_preparer, papf.full_name) AND prh.type_lookup_code in ('INTERNAL','PURCHASE') AND psp.ORG_ID = prh.ORG_ID AND EXISTS (SELECT 'requisition line exists' FROM po_req_distributions prd , po_requisition_lines prl , po_lookup_codes plc2 &from_failed_f WHERE prh.requisition_header_id = prl.requisition_header_id AND prl.requisition_line_id = prd.requisition_line_id AND prl.source_type_code = plc2.lookup_code &where_failed_f AND plc2.lookup_type = 'REQUISITION SOURCE TYPE') AND NVL(PRH.contractor_requisition_flag, 'N') <> 'Y' ORDER BY decode(psp.manual_req_num_type,'NUMERIC',decode(rtrim(prh.segment1,'0123456789'),NULL, to_number(prh.segment1),null)), decode(psp.manual_req_num_type,'ALPHANUMERIC',prh.segment1,null)