PO Requisition Activity Register

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