PO Expected Receipts

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Expected Receipts Report (XML)
Short Name: POXRVXRV_XML
DB package: PO_POXRVXRV_XMLP_PKG
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
Category Structure
 
Number
Customer
 
LOV Oracle
To
 
LOV Oracle
RMA Numbers From
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle
Organization Name
 
LOV Oracle
Ship-To Location
 
LOV Oracle
To
 
Date
Due Dates From
 
Date
To
 
Categories From
 
To
 
Items From
 
Supplier
 
LOV Oracle
To
 
LOV Oracle
Requisition Numbers From
 
LOV Oracle
To
 
LOV Oracle
Purchase Order Numbers From
 
LOV Oracle
Title