PO Requisition Activity Register

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Requisition Activity Register
Application: Purchasing
Source: Requisition Activity Register (XML)
Short Name: POXREQAC_XML
DB package: PO_POXREQAC_XMLP_PKG
Run PO Requisition Activity Register and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT trunc(prh.creation_date)                         Creation_Date
,      prh.segment1                                     Requisition_Number
,      papf.full_name                                   Preparer
,      t.type_name                                    Type
,      prh.description                                  Description
,      SUM (DECODE (PRL.order_type_lookup_code,                  
                                 'FIXED PRICE', NVL(PRL.amount, 0),
                                 'RATE', NVL(PRL.amount, 0),
                                 NVL(PRL.quantity, 0) * NVL(PRL.unit_price, 0))) C_AMOUNT_REQ, 
	PO_POXREQAC_XMLP_PKG.round_amount_sum_req(:C_AMOUNT_SUM_REQ, :C_CURRENCY_PRECISION) C_AMOUNT_SUM_REQ_ROUND, 
	PO_POXREQAC_XMLP_PKG.round_amount_req(SUM ( DECODE ( PRL.order_type_lookup_code , 'FIXED PRICE' , NVL ( PRL.amount , 0 ) , 'RATE' , NVL ( PRL.amount , 0 ) , NVL ( PRL.quantity , 0 ) * NVL ( PRL.unit_price , 0 ) ) ), :C_CURRENCY_PRECISION) C_AMOUNT_REQ_ROUND
FROM   po_requisition_lines       prl
,      po_requisition_headers     prh
,      per_all_people_f           papf 
,      po_document_types_all_tl   t
,      po_document_types_all_b    b 
WHERE  trunc(prh.creation_date)          >= decode(:P_Creation_Date_From,'',
                                            trunc(prh.creation_date),
                                            :P_Creation_Date_From)
AND    trunc(prh.creation_date)          <= decode(:P_Creation_Date_To,'',
                                            trunc(prh.creation_date),
                                            :P_Creation_Date_To)
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    papf.person_id            = prh.preparer_id
AND    papf.full_name              = decode(:P_Preparer,'',papf.full_name,
                                                           :P_Preparer)
AND    prh.type_lookup_code       = decode(:P_Type,'',prh.type_lookup_code,
                                                      :P_Type)
AND    prh.authorization_status <> 'SYSTEM_SAVED'
AND    NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'    
AND    prl.requisition_header_id  = prh.requisition_header_id
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')
AND    b.document_type_code     = 'REQUISITION'
AND    b.document_subtype       = prh.type_lookup_code
AND    NVL(prl.cancel_flag, 'N') = 'N'
GROUP BY prh.creation_date    
,        papf.full_name
,        t.type_name 
,        prh.description      
,        prh.segment1
,        prl.currency_code
ORDER BY prh.creation_date
,        papf.full_name
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Title 
 
 Char
Creation Dates From 
 
 Date
To 
 
 Date
Preparer 
 
 LOV Oracle
Requisition Type 
 
 LOV Oracle
Download
 
Related Blitz Reports
ECC Procurement, Requisitions PO Cancelled Requisitions PO Overshipments PO Printed Requisitions GL Account Analysis (Distributions) ECC Project Manufacturing, Procurement Activities, SQL2 ECC Procurement, Purchase Orders PO Receiving Transactions Pipeline PO Receiving Transactions Register
Blitz Report™

Blitz Report™ provides multiple benefits: