PO Expected Receipts

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Expected Receipts Report
Application: Purchasing
Source: Expected Receipts Report (XML)
Short Name: POXRVXRV_XML
DB package: PO_POXRVXRV_XMLP_PKG
Run PO Expected Receipts and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT TO_CHAR(nvl(pll.promised_date,need_by_date), 'DD/MON/YYYY HH24:MI:SS')            P_Date
,      pov.vendor_name                        Source
,      null                           C_FLEX_ITEM
,      null                            C_FLEX_CAT
,      poh.segment1                              PO_NUM
,     decode(por.release_num,null,null,por.release_num)  PO_REL_NUM
,      pol.line_num                                PO_LINE_NUM
,      poh.segment1||decode(por.release_num,null,null,'-'||por.release_num)||
       ','||pol.line_num                      Number_Release_Line
,      pdt.type_name                   Doc_type
,      pll.shipment_type               Shipment_type 
,      'ABC'                      Location           
,      pll.ship_to_location_id                       Location_id    
,      pol.item_revision                      Rev
,      pol.unit_meas_lookup_code              Unit
,      pol.vendor_product_num                 Product_Number
,      plt.line_type                          Line_Type
,      pol.item_description                   Item_Description
,      decode(nvl(rp.blind_receiving_flag,'N'), 'N',round(pll.quantity,:P_qty_precision), NULL)
Ordered
,      decode(nvl(rp.blind_receiving_flag,'N'), 'N',round(decode(sign(nvl(pll.quantity,0)-nvl(pll.quantity_received,0)),-1,0,0,0,
              +1,nvl(pll.quantity,0)-nvl(pll.quantity_received,0)),:P_qty_precision) ,NULL)  Due
,      poh.note_to_receiver                   Note_receiver
,      phc.hazard_class                       Hazard
,      pun.un_number                          Un_Number
,      pun.description                        Un_Description
,      plc2.displayed_field                   Source_Type
,        decode(psp.manual_po_num_type,'NUMERIC',null,poh.segment1)
                                                 Document_Numbering1
,        decode(psp.manual_po_num_type,'NUMERIC',to_number(poh.segment1),null)
                                                 Document_Numbering2
,      poh.po_header_id                       head_id
,      pll.line_location_id     loc_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') 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, 
,   decode(msi.inventory_item_id,null,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   -- Bug18496720	
,	decode(pol.category_id,null,null,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           -- Bug18496720	
,	PO_POXRVXRV_XMLP_PKG.location_code1formula('ABC', pll.shipment_type, pll.ship_to_location_id) LOCATION_CODE1
FROM   po_vendors                   pov
,      po_system_parameters_all         psp     
,      po_releases_all                  por              
,      po_line_types                plt
,      mtl_categories               mca
,      po_line_locations_all            pll             
,      po_lines_all                     pol                
,      po_headers                   poh
,      mtl_system_items             msi
,      po_hazard_classes            phc
,      po_un_numbers                pun
,      po_document_types         pdt
,      po_lookup_codes              plc2
,      rcv_parameters                  rp
WHERE  poh.org_id = psp.org_id      
AND poh.po_header_id           = pol.po_header_id
AND decode(poh.type_lookup_code, 'STANDARD', nvl(poh.closed_code,'OPEN'),
                                                       'PLANNED', nvl(poh.closed_code,'OPEN'),
                                                       'BLANKET', nvl(por.closed_code, 'OPEN')) = 'OPEN'
AND decode(poh.type_lookup_code, 'STANDARD', nvl(pol.closed_code,'OPEN'),
                                                       'PLANNED', nvl(pol.closed_code,'OPEN'),
                                                       'BLANKET','OPEN') = 'OPEN'
AND    nvl(poh.cancel_flag,'N')   = 'N'
AND    nvl(pol.cancel_flag,'N')   = 'N'
AND    nvl(por.cancel_flag,'N')   = 'N'
AND    poh.vendor_id              = pov.vendor_id
AND    pol.po_line_id             = pll.po_line_id
AND    pol.line_type_id           = plt.line_type_id
AND    pol.item_id                = msi.inventory_item_id(+)
AND    pol.category_id            = mca.category_id
AND    pll.po_release_id          = por.po_release_id(+)
AND    pll.shipment_type      IN ('STANDARD','SCHEDULED','BLANKET')
AND    nvl(pll.approved_flag,'N')          = 'Y'
AND    nvl(pll.cancel_flag,'N')   = 'N'
AND    poh.type_lookup_code in ('BLANKET','STANDARD','PLANNED')
AND    nvl(pll.closed_code,'OPEN') IN ('OPEN','CLOSED FOR INVOICE')
AND    pol.un_number_id           = pun.un_number_id(+)
AND    pol.hazard_class_id        = phc.hazard_class_id(+)
AND    pdt.document_type_code in ('PO','PA')
AND    pdt.document_subtype = poh.type_lookup_code
AND    plc2.lookup_type            = 'REQUISITION SOURCE TYPE'
AND    plc2.lookup_code            = 'VENDOR'
AND    nvl(pll.quantity,0)-
       nvl(pll.quantity_cancelled,0)        > nvl(pll.quantity_received,0)
AND    &P_WHERE_NO_PO_NUM
AND     &P_WHERE_PO_NUM_FROM
AND     &P_WHERE_PO_NUM_TO
AND     &P_WHERE_VENDOR
&P_PO_ORG
AND    nvl(nvl(pll.promised_date,pll.need_by_date),sysdate)
                BETWEEN  nvl(:P_PROMISE_DATE_FROM,                        nvl(nvl(pll.promised_date,pll.need_by_date),sysdate) - 1 )
                AND      nvl(:P_PROMISE_DATE_TO,
                        nvl(nvl(pll.promised_date,pll.need_by_date),sysdate) + 1)
AND    &P_WHERE_CAT
AND    &P_WHERE_ITEM  
AND    &LP_customer is null
AND    rp.organization_id = pll.ship_to_organization_id
AND    plt.matching_basis <> 'AMOUNT'
UNION
SELECT TO_CHAR(prl.need_by_date, 'DD/MON/YYYY HH24:MI:SS')                      P_Date
,      hou.name                                    Source
,      null                          C_FLEX_ITEM
,      null                           C_FLEX_CAT
,      prh.segment1                              PO_NUM
,      null                                               PO_REL_NUM
,      prl.line_num                                PO_LINE_NUM
,      prh.segment1||','||prl.line_num             Number_Release_Line
,      pdt.type_name                   Doc_type
,      null            Shipment_type 
,      hrtl.location_code                      Location 
,      hrl.location_id                        Location_id
,      prl.item_revision                      Rev
,      prl.unit_meas_lookup_code              Unit
,      prl.suggested_vendor_product_code      Product_Number
,      plt.line_type                          Line_Type
,      prl.item_description                   Item_Description
,      decode(nvl(rp.blind_receiving_flag,'N'), 'N',round(prl.quantity,:P_qty_precision), NULL)  Ordered
,      decode(nvl(rp.blind_receiving_flag,'N'), 'N',  round(decode(sign(prl.quantity- nvl(prl.quantity_delivered,0)),- 1,0,0,0,
              +1,prl.quantity - nvl(prl.quantity_delivered,0)),:P_qty_precision) , NULL)     Due
,      prl.note_to_receiver                   Note_receiver
,      phc.hazard_class                       Hazard
,      pun.un_number                          Un_Number
,      pun.description                        Un_Description
,      plc2.displayed_field                   Source_Type
,        decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment1)
                                                 Document_Numbering1
,        decode(psp.manual_req_num_type,'NUMERIC',to_number(prh.segment1),null)
                                                 Document_Numbering2
,      prh.requisition_header_id              head_id
,      -1       loc_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') 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, 
,   decode(msi.inventory_item_id,null,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   -- Bug18496720	
,	decode(prl.category_id,null,null,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           -- Bug18496720
,	PO_POXRVXRV_XMLP_PKG.location_code1formula(hrtl.location_code, null, hrl.location_id) LOCATION_CODE1
FROM   hr_locations_all             hrl   ,       hr_locations_all_tl              hrtl  ,      po_system_parameters_all         psp                
,      po_line_types                plt
,      mtl_categories               mca
,      po_requisition_headers       prh
,      po_requisition_lines_all         prl                        
,      mtl_system_items             msi
,      hr_organization_units        hou
,      po_hazard_classes            phc
,      po_un_numbers                pun
,      po_document_types          pdt
,      po_lookup_codes              plc2
,      rcv_parameters                  rp
WHERE  prh.org_id = psp.org_id         
AND    prh.requisition_header_id  = prl.requisition_header_id
AND    prl.line_type_id           = plt.line_type_id
AND    prl.item_id                = msi.inventory_item_id(+)
AND    hou.organization_id          = prl.source_organization_id
AND    prl.category_id            = mca.category_id(+)
AND    prl.deliver_to_location_id    = hrl.location_id
AND    hrl.location_id            = hrtl.location_id  AND    hrtl.language              = userenv('LANG')    AND    nvl(prh.authorization_status, 'INCOMPLETE') = 'APPROVED'
AND    nvl(prl.cancel_flag,'N')   = 'N'
AND    prl.source_type_code = 'INVENTORY'
AND    nvl(prl.closed_code,'OPEN')!= 'FINALLY CLOSED'
AND    prl.un_number_id           = pun.un_number_id(+)
AND    prl.hazard_class_id        = phc.hazard_class_id(+)
AND    pdt.document_type_code         = 'REQUISITION'
AND    pdt.document_subtype = prh.type_lookup_code
AND    plc2.lookup_type            = 'REQUISITION SOURCE TYPE'
AND    plc2.lookup_code            = 'INVENTORY'
AND    prl.quantity-
       nvl(prl.quantity_cancelled,0)        > nvl(prl.quantity_delivered,0)
AND    &P_WHERE_NO_REQ_NUM
AND     &P_WHERE_REQ_NUM_FROM
AND     &P_WHERE_REQ_NUM_TO
&P_REQ_ORG
AND    nvl(prl.need_by_date,sysdate)
                BETWEEN  nvl(:P_PROMISE_DATE_FROM,
                         nvl(prl.need_by_date,sysdate) - 1)
                AND      nvl(:P_PROMISE_DATE_TO,
                        nvl(prl.need_by_date,sysdate) + 1 )
AND    &P_WHERE_CAT
AND    &P_WHERE_ITEM
AND     &LP_vendor is null
AND     &LP_customer is null
AND  rp.organization_id = prl.destination_organization_id
AND  plt.matching_basis <> 'AMOUNT'
UNION
SELECT
TO_CHAR(rcv.expected_receipt_date, 'DD/MON/YYYY HH24:MI:SS')        P_Date
,      source           Source
,      null     C_FLEX_ITEM
,      null       C_FLEX_CAT
,     to_char(oe_order_num)       PO_NUM
,      decode(po_release_number,  null,  null, po_release_number)               PO_REL_NUM
,      oe_order_line_num    PO_LINE_NUM
,      rcv.oe_order_num || decode(po_release_number, null, null, '-'|| po_release_number) || ',' ||rcv.oe_order_line_num     Number_Release_Line
,      order_type           Doc_type
,      null             Shipment_type 
,      SHIP_TO_LOCATION         location
,      SHIP_TO_LOCATION_ID      Location_id
,      rcv.item_revision                Rev
,      primary_uom              Unit
,      vendor_item_number              Product_Number
,      ''               Line_Type
,      item_description         Item_Description
,      ORDERED_QTY              Ordered
,      nvl(oel.ordered_quantity,0)  - nvl(oel.shipped_quantity,0)  -  nvl(oel.fulfilled_quantity,0)     Due
,      note_to_receiver         Note_receiver
,      rcv.hazard_class         Hazard
,      rcv.un_number            Un_Number
,      null             Un_Description
,      plc.displayed_field                      Source_Type
,      NULL   Document_Numbering1
,    to_number( NULL ) Document_Numbering2
,     oe_order_header_id             head_id
,      oe_order_line_id                 loc_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') 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,
,   decode(msi.inventory_item_id,null,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   -- Bug18496720	
,	decode(rcv.Item_category_id,null,null,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      -- Bug18496720	
,	PO_POXRVXRV_XMLP_PKG.location_code1formula(SHIP_TO_LOCATION, null, SHIP_TO_LOCATION_ID) LOCATION_CODE1
from oe_po_enter_receipts_v       rcv   
,      mtl_categories           mca
,      mtl_system_items msi
,      po_lookup_codes          plc
,      oe_order_lines           oel
where
&P_WHERE_NO_RMA_NUM
AND &P_WHERE_RMA_NUM_FROM
AND &P_WHERE_RMA_NUM_TO
AND &P_WHERE_CUSTOMER
&P_RMA_ORG
AND rcv.item_id = msi.inventory_item_id(+)
AND rcv.Item_category_id = mca.category_id(+)
AND msi.organization_id = rcv.to_organization_id
AND nvl(rcv.expected_receipt_date, sysdate)
BETWEEN nvl(:P_PROMISE_DATE_FROM,  nvl(rcv.expected_receipt_date, sysdate) - 1)
AND nvl(:P_PROMISE_DATE_TO, nvl(rcv.expected_receipt_date, sysdate) + 1)
AND &P_WHERE_CAT
AND &P_WHERE_ITEM
AND rcv.source_type_code  = plc.lookup_code
AND  &LP_vendor is null
AND rcv.oe_order_line_id = oel.line_id
ORDER BY &orderby_clause, 23, 9, 24, 25
Parameter Name SQL text Validation
Operating Unit
 
LOV
Title
 
Char
Purchase Order Numbers From
 
LOV Oracle
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
To 2
 
LOV Oracle
Supplier
 
LOV Oracle
Items From
 
Char
To 3
 
Char
Categories From
 
Char
To 4
 
Char
Due Dates From
 
Date
To 5
 
Date
Ship-To Location
 
LOV Oracle
Organization Name
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle
RMA Numbers From
 
LOV Oracle
To 6
 
LOV Oracle
Customer
 
LOV Oracle