WSH Open Deliveries
Description
Categories: BI Publisher
Application: Shipping Execution
Source: Open Deliveries Report (XML)
Short Name: WSHRDOPN_XML
DB package: WSH_WSHRDOPN_XMLP_PKG
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 | |
---|---|---|---|
Batch Name |
|
LOV Oracle | |
Customer Name |
|
LOV Oracle | |
Creation Date(Low) |
|
Date | |
Creation Date(High) |
|
Date | |
Item Display |
|
LOV Oracle | |
Warehouse |
|
LOV Oracle | |
Ship Method |
|
LOV Oracle | |
Ship To |
|
LOV Oracle | |
Sort Order |
|
LOV Oracle |