WSH Backorder Summary

Description
Categories: BI Publisher
Application: Shipping Execution
Source: Backorder Summary Report (XML)
Short Name: WSHRDBSR_XML
DB package: WSH_WSHRDBSR_XMLP_PKG
select
                   wdd.inventory_item_id,
                   &LP_ITEM_DISPLAY_VALUE item_info,
                   wdd.item_description,
                   wdd.organization_id,
                   wdd.source_code,
                   wdd.source_header_number,
                   sum(nvl(wdd.requested_quantity,0)) backordered_quantity,
                      max(wts.actual_departure_date) last_shipped_date,
                   nvl(wdd.unit_price, 0) unit_price,
                   wdd.currency_code currency_code,
                   wdd.requested_quantity_uom,
                   wdd.source_header_type_name order_type,
                WSH_WSHRDBSR_XMLP_PKG.cf_warehouseformula(wdd.organization_id) CF_WAREHOUSE,
                WSH_WSHRDBSR_XMLP_PKG.cf_item_descriptionformula(wdd.inventory_item_id, wdd.organization_id, wdd.item_description) CF_ITEM_DESCRIPTION,
                WSH_WSHRDBSR_XMLP_PKG.cf_last_shipped_dateformula(wdd.organization_id, wdd.source_header_number) CF_LAST_SHIPPED_DATE,
                WSH_WSHRDBSR_XMLP_PKG.cf_shipped_qtyformula(wdd.currency_code, wdd.organization_id, wdd.inventory_item_id, wdd.source_header_number, wdd.source_header_type_name, wdd.requested_quantity_uom, nvl ( wdd.unit_price , 0 )) CF_Shipped_qty,
                WSH_WSHRDBSR_XMLP_PKG.cf_shipped_amtformula(WSH_WSHRDBSR_XMLP_PKG.cf_shipped_qtyformula(wdd.currency_code, wdd.organization_id, wdd.inventory_item_id, wdd.source_header_number, wdd.source_header_type_name, wdd.requested_quantity_uom, nvl ( wdd.unit_price , 0 )), nvl ( wdd.unit_price , 0 )) CF_shipped_amt,
                WSH_WSHRDBSR_XMLP_PKG.cf_bo_amtformula(sum ( nvl ( wdd.requested_quantity , 0 ) ), nvl ( wdd.unit_price , 0 )) CF_bo_amt
            from  wsh_delivery_details wdd,
                     wsh_delivery_assignments_v wda,
                     wsh_delivery_legs wdl,
                     wsh_trip_stops wts
            where wdd.released_status = 'B'
            and   wdd.source_code = 'OE'
            and   wdd.delivery_detail_id = wda.delivery_detail_id
            and  nvl(wdd.line_direction, 'O') IN ('O', 'IO')
            and   wda.delivery_id = wdl.delivery_id(+)
            and   wdl.pick_up_stop_id = wts.stop_id(+)
            &lp_organization_id
            &lp_header_number
            &lp_order_type
            GROUP BY wdd.inventory_item_id,
                     &LP_ITEM_DISPLAY_VALUE,
                     wdd.item_description,
                     wdd.organization_id,
                     wdd.source_code,
                     wdd.source_header_number,
                     wdd.source_header_type_name,
                     wdd.requested_quantity_uom,
                     wdd.unit_price,
                     wdd.currency_code
            ORDER BY 4 ASC,1 ASC,2 ASC ,wdd.organization_id,
                               item_info,
                      wdd.source_code,
                              wdd.source_header_number
Parameter NameSQL textValidation
Transaction Type
 
LOV Oracle
Sales Order Number (Low)
 
LOV Oracle
Sales Order Number (High)
 
LOV Oracle
Warehouse
 
LOV Oracle
Item Display
 
LOV Oracle
Item Flex Code