WSH Backorder Summary

Description
Categories: BI Publisher, Logistics
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 Name SQL text Validation
Item Flex Code
 
Item Display
 
LOV Oracle
Warehouse
 
LOV Oracle
Sales Order Number (High)
 
LOV Oracle
Sales Order Number (Low)
 
LOV Oracle
Transaction Type
 
LOV Oracle