PO Expected Receipts

Description

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

PO Expected Receipts and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

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

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