PO Internal Requisition Status

SELECT 
 prh.segment1                            Req_Number
,       trunc(prl.creation_date)                CDate
,       papf.full_name                           Requestor
,       prl.source_subinventory                 Subinventory
,       null                            C_FLEX_ITEM 
,       prl.item_revision                       Rev
,       prl.item_description                    Description
,       prl.unit_meas_lookup_code               Unit
,       round(prl.quantity,:P_qty_precision)                           Required
,       round(nvl(prl.quantity_delivered,null),:P_qty_precision)                  Delivered
,       round(sum(&C_backordered),
                                       :P_qty_precision)                Backordered  
,       decode(nvl(prl.cancel_flag,'N'),
               'Y',
               round((prl.quantity - nvl(prl.quantity_delivered,0)),:P_qty_precision),
               to_number(NULL))                                         Cancelled
,     nvl(prl.unit_price, 0)	unit_price
,    round(&C_ship_qty, :P_qty_precision) Shipped, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP, 
	PO_POXRQSIN_XMLP_PKG.c_ship_amountformula(round ( prl.quantity , :P_qty_precision ), nvl ( prl.unit_price , 0 )) C_ship_amount
FROM    po_requisition_lines                    prl
,       po_requisition_headers                  prh
,       mtl_system_items                        msi
,       per_all_people_f                          papf
&C_from
,       po_system_parameters_all                    psp1
WHERE  
&P_WHERE_QUERY
AND     trunc(prl.creation_date) 
        BETWEEN nvl(:P_creation_date_from,trunc(prl.creation_date)-1)
        AND     nvl(:P_creation_date_to,trunc(prl.creation_date)+1)
AND   psp1.ORG_ID = prh.ORG_ID  
AND     papf.full_name = nvl(:P_requestor,papf.full_name)
AND     nvl(prl.source_subinventory,'-1') 
        BETWEEN nvl(:P_subinventory_from,nvl(prl.source_subinventory,'-1'))
        AND     nvl(:P_subinventory_to,  nvl(prl.source_subinventory,'-1'))
AND     prl.requisition_header_id                   = prh.requisition_header_id
AND     prl.source_type_code                        = 'INVENTORY'
AND     nvl(prh.transferred_to_oe_flag,'N')         = 'Y'
AND     msi.inventory_item_id                       = prl.item_id
AND     nvl(msi.organization_id,prl.source_organization_id)   = prl.source_organization_id
&C_where
AND     papf.person_id                             = prl.to_person_id
AND    prh.authorization_status = nvl(:P_status,prh.authorization_status)
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     &P_WHERE_ITEM
GROUP BY prh.segment1, prl.creation_date, papf.full_name,
         prl.source_subinventory, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'), prl.item_revision,
         prl.item_description, prl.unit_meas_lookup_code, prl.quantity,
         prl.quantity_delivered, &C_backordered, prl.cancel_flag, prl.unit_price, &C_ship_qty
UNION
SELECT 
prh.segment1                            Req_Number
,       trunc(prl.creation_date)                CDate
,       papf.full_name                           Requestor
,       prl.source_subinventory            Subinventory
,       null                            C_FLEX_ITEM
,       prl.item_revision                       Rev
,       prl.item_description                    Description
,       prl.unit_meas_lookup_code               Unit
,       round(prl.quantity,:P_qty_precision)                            Required
,       round(nvl(prl.quantity_delivered,0),:P_qty_precision)                  Delivered
,       to_number(null)                                                 Backordered  
,       decode(nvl(prl.cancel_flag,'N'),
               'Y',
               round((prl.quantity - nvl(prl.quantity_delivered,0)),:P_qty_precision),
               to_number(NULL))                                         Cancelled
,     nvl(prl.unit_price, 0)			unit_price
,    to_number(NULL)  Shipped, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP, 
	PO_POXRQSIN_XMLP_PKG.c_ship_amountformula(round ( prl.quantity , :P_qty_precision ), nvl ( prl.unit_price , 0 )) C_ship_amount
FROM    po_requisition_lines                    prl
,       po_requisition_headers                  prh
,       mtl_system_items                        msi
,       per_all_people_f                         papf
,       po_system_parameters_all                    psp1
WHERE  
&P_WHERE_QUERY
AND   psp1.ORG_ID = prh.ORG_ID  
AND     trunc(prl.creation_date) 
        BETWEEN nvl(:P_creation_date_from,trunc(prl.creation_date)-1)
        AND     nvl(:P_creation_date_to,trunc(prl.creation_date)+1)
AND     papf.full_name = nvl(:P_requestor,papf.full_name)
AND     nvl(prl.source_subinventory,'-1') 
        BETWEEN nvl(:P_subinventory_from,nvl(prl.source_subinventory,'-1'))
        AND     nvl(:P_subinventory_to,  nvl(prl.source_subinventory,'-1'))
AND     prl.requisition_header_id                   = prh.requisition_header_id
AND     prl.source_type_code                        = 'INVENTORY'
AND     (nvl(prh.transferred_to_oe_flag,'N')         = 'N'
        OR (nvl(prh.transferred_to_oe_flag,'N') = 'Y' &C_interface_where)) 
AND     msi.inventory_item_id(+)                    = prl.item_id
AND     nvl(msi.organization_id,prl.source_organization_id)   = prl.source_organization_id
AND     papf.person_id                             = prl.to_person_id
AND     prh.authorization_status = nvl(:P_status,prh.authorization_status)
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   &P_WHERE_ITEM
ORDER BY &ORDERBY_CLAUSE