WSH Open Deliveries

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
Source: Open Deliveries Report (XML)
Short Name: WSHRDOPN_XML
DB package: WSH_WSHRDOPN_XMLP_PKG
SELECT  wnd.organization_id,
                    wnd.delivery_id deliv,
                     wnd.name del_name,
                    wnd.creation_date credate,
                    wsh_util_core.get_location_description(wnd.ultimate_dropoff_location_id,
                      'NEW UI CODE') ship_to,
                    wsh_util_core.get_location_description(wnd.intmed_ship_to_location_id,
                      'NEW UI CODE') int_med_ship_to,
                    wsh_util_core.derive_shipment_priority(wnd.delivery_id) as shippri,
                    wdd.delivery_detail_id,
                    wdd.requested_quantity,
                    decode(wdd.released_status,'Y',nvl(wdd.picked_quantity,wdd.requested_quantity),NULL) picked_qty,
                    wdd.shipped_quantity,
                    wdd.source_header_number,
                    wdd.batch_id,
                    wdd.container_flag,
                    decode(wdd.inventory_item_id, NULL,
                    decode(:P_ITEM_DISPLAY,'D',wdd.item_description,'F',msik.concatenated_segments,
                            msik.concatenated_segments||' '||wdd.item_description),
                decode(:P_ITEM_DISPLAY,'D',msi.description,'F',msik.concatenated_segments,
                            msik.concatenated_segments||' '||msi.description) )  item_description,
                    wdd.inventory_item_id,
                    wpb.name batchname,
                    wpb.creation_date reldate,
                    msi.organization_id,
                    msik.organization_id,
                    hca.account_number customer,
                    mp.organization_code,
                    flv.meaning as shipmeth,
                   wl.meaning as srcys,
                  &F_SORT_SELECT,
                WSH_WSHRDOPN_XMLP_PKG.cf_shipment_priorityformula(wsh_util_core.derive_shipment_priority ( wnd.delivery_id )) CF_shipment_priority,
                WSH_WSHRDOPN_XMLP_PKG.cf_lpnformula(wdd.container_flag) CF_CONTAINER_FLAG
            FROM    wsh_new_deliveries wnd,
                    wsh_delivery_assignments_v wda,
                    wsh_delivery_details wdd,
                    wsh_picking_batches wpb ,
                    mtl_system_items_vl msi,
                    mtl_system_items_kfv msik,
                    wsh_lookups wl,
                   ( SELECT cust_acct.cust_account_id,
                           'C' consignee_flag,
                           cust_acct.account_number account_number
                    FROM   hz_cust_accounts cust_acct
                    UNION
                    SELECT pv.vendor_id cust_account_id,
                           'V' consignee_flag,
                           pv.segment1 account_number
                    FROM   po_vendors pv ) hca,
                    mtl_parameters mp,
                    fnd_lookup_values_vl flv
            WHERE   wnd.status_code IN ('OP', 'SR', 'SC', 'SA')
              AND   wda.delivery_id = wnd.delivery_id
              AND   wda.delivery_id IS NOT NULL
              AND   wda.delivery_detail_id = wdd.delivery_detail_id
              AND   wdd.batch_id = wpb.batch_id(+)
              AND   wdd.organization_id = msi.organization_id(+)
              AND   wdd.inventory_item_id = msi.inventory_item_id(+)
              AND   wdd.organization_id = msik.organization_id(+)
              AND   wdd.inventory_item_id = msik.inventory_item_id(+)
              AND   wdd.source_code = wl.lookup_code
              AND   wl.lookup_type = 'SOURCE_SYSTEM'
              AND   wnd.customer_id = hca.cust_account_id(+)
              AND   nvl(wnd.consignee_flag,'C') = hca.consignee_flag(+) 
              AND   wnd.organization_id = mp.organization_id(+)
              AND   nvl(wnd.shipment_direction, 'O') IN ('O', 'IO')
              AND   wnd.delivery_type = 'STANDARD'
              AND  wdd.container_flag IN ('N', 'Y')
              AND   wdd.organization_id = :p_organization_id
              AND   flv.lookup_type(+) = 'SHIP_METHOD'
              AND   flv.lookup_code(+) = wnd.ship_method_code
              AND   flv.view_application_id(+) = 3
              &F_WHERE
            order by 19 ASC,22 ASC,7 ASC,2 ASC,18 ASC,4 
  ASC,21 ASC,5 ASC,6 ASC,3 ASC,23 ASC,24 ASC,12 ASC ,&F_SORT_ORDER
Parameter Name SQL text Validation
Sort Order
 
LOV Oracle
Ship To
 
LOV Oracle
Ship Method
 
LOV Oracle
Warehouse
 
LOV Oracle
Item Display
 
LOV Oracle
Creation Date(High)
 
Date
Creation Date(Low)
 
Date
Customer Name
 
LOV Oracle
Batch Name
 
LOV Oracle