PO Backordered Internal Requisitions

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Backordered Internal Requisitions Report (XML)
Short Name: POXRQOBO_XML
DB package: PO_POXRQOBO_XMLP_PKG
SELECT  prh.segment1                      Req_Number
,       prl.creation_date                 CDate
,       prl.line_num                      Line
,       prl.item_id                       Item_ID
,       prl.source_organization_id        Org_ID
,       null                      C_FLEX_ITEM
,       null                       C_FLEX_CAT
,       prl.item_description              Item_Description
,       papf.full_name                    Requestor
,       ood.organization_code             Organization
,       prl.source_subinventory           Subinventory
,       (prl.quantity - nvl(prl.quantity_cancelled,0))              Ordered_Quantity
,       round(SUM(&C_backordered),:P_qty_precision)                 Backordered
,       prl.unit_meas_lookup_code               		    Unit
,       round(SUM(&C_ship_quantity), :P_qty_precision) 		    Shipped
,       decode(psp1.manual_req_num_type,'NUMERIC',  
               null,prh.segment1)
,       decode(psp1.manual_req_num_type,'NUMERIC',
               to_number(prh.segment1),null), 
	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, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP
FROM    po_requisition_headers    prh
,       po_requisition_lines      prl
,       mtl_system_items          msi
,       per_all_people_f          papf
&C_from
,       mtl_categories                mca
,       org_organization_definitions  ood
,       po_system_parameters          psp1
WHERE   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') = 'Y'
AND     prl.source_organization_id      = ood.organization_id
AND     papf.person_id              = prl.to_person_id
AND     msi.inventory_item_id       = prl.item_id
AND     msi.organization_id         = prl.source_organization_id 
AND     mca.category_id             = prl.category_id   
AND     trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND     decode(hr_general.get_xbg_profile,
                            'Y',papf.business_group_id,
                            hr_general.get_business_group_id)=papf.business_group_id
&C_where
GROUP BY prh.segment1
,        prl.creation_date                  
,        prl.line_num
,        prl.item_id
,        prl.source_organization_id         
,        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')                      
,        fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')                        
,        prl.item_description                   
,        papf.full_name                      
,        ood.organization_code               
,        prl.source_subinventory    
,        &C_backordered             
,        prl.unit_meas_lookup_code 
,        &C_ship_quantity           
,        psp1.manual_req_num_type 
,       (prl.quantity - nvl(prl.quantity_cancelled,0)) 
ORDER BY decode(psp1.manual_req_num_type,'NUMERIC',
                null,prh.segment1)
,        decode(psp1.manual_req_num_type,'NUMERIC',
                to_number(prh.segment1), null)
,        prl.line_num
Parameter Name SQL text Validation
Dynamic Precision Option
 
LOV Oracle
To
 
LOV Oracle
Subinventories From
 
LOV Oracle
Requester
 
LOV Oracle
To
 
Date
Creation Dates From
 
Date
Title