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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

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

LOV Oracle

Dynamic Precision Option

LOV Oracle

RMA Numbers From

LOV Oracle

To 6

LOV Oracle

Customer

LOV Oracle

Download

Related Blitz Reports

CAC Purchase Price Variance PO Purchase Order and Releases Detail PO Headers and Lines 11i PO Receiving Transactions Register PO Receipt Adjustments PO Receiving Exceptions CAC Manufacturing Variance Summary CAC WIP Resource Efficiency CAC WIP Resource Efficiency with Method and Lot Variances

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Find out more

Features

Download

FAQ