PO Requisition Distribution Detail

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Requisition Distribution Detail Report (XML)
Short Name: POXRQDDR_XML
DB package: PO_POXRQDDR_XMLP_PKG
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)
Parameter Name SQL text Validation
P_CHART_OF_ACCOUNTS_ID
 
Number
Dynamic Precision Option
 
LOV Oracle
Failed Funds Only
 
LOV Oracle
Preparer
 
LOV Oracle
To
 
Date
Requisition Creation Dates From
 
Date
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
Title