[Home] [Help]
39: SELECT 'Y'
40: FROM mtl_material_transactions mmt ,
41: mtl_material_transactions_temp mmtt,
42: wsh_delivery_assignments wda,
43: wsh_delivery_details wdd
44: WHERE mmtt.transaction_temp_id = p_transaction_id
45: AND wda.delivery_id = p_delivery_id
46: AND mmt.organization_id = mmtt.organization_id
47: AND mmt.transaction_source_type_id IN (INV_GLOBALS.G_SOURCETYPE_SALESORDER,INV_GLOBALS.G_SOURCETYPE_INTORDER)
111:
112: CURSOR c_wdd_shipping IS
113: SELECT wda.delivery_id, wdd.organization_id, wdd.subinventory
114: FROM wsh_delivery_assignments_v wda, wsh_new_deliveries wnd
115: , wsh_delivery_details wdd
116: WHERE wda.delivery_detail_id = p_transaction_id
117: AND wnd.delivery_id = wda.delivery_id
118: AND wdd.delivery_detail_id(+) = wda.delivery_detail_id;
119:
119:
120: CURSOR c_cart_shipping IS
121: SELECT wda.delivery_id, mmtt.organization_id, mmtt.subinventory_code
122: FROM wsh_delivery_assignments_v wda, wsh_new_deliveries wnd,
123: wsh_delivery_details wdd, mtl_material_transactions_temp mmtt
124: WHERE mmtt.move_order_line_id = wdd.move_order_line_id
125: AND wda.delivery_detail_id = wdd.delivery_detail_id
126: AND wnd.delivery_id = wda.delivery_id
127: AND mmtt.transaction_temp_id = p_transaction_id;
130:
131: CURSOR c_cart_shipping_pkg IS
132: SELECT DISTINCT(wda.delivery_id)
133: FROM wsh_delivery_assignments_v wda, wsh_new_deliveries wnd,
134: wsh_delivery_details wdd, mtl_material_transactions_temp mmtt
135: --wms_packaging_hist wph
136: WHERE mmtt.move_order_line_id = wdd.move_order_line_id (+)
137: AND wda.delivery_detail_id (+) = wdd.delivery_detail_id
138: AND wnd.delivery_id (+) = wda.delivery_id
152:
153:
154: CURSOR c_wdd_delivery_dtl_id IS
155: SELECT delivery_detail_id
156: FROM wsh_delivery_details wdd
157: WHERE wdd.move_order_line_id = l_move_order_line_id;
158:
159:
160: CURSOR c_wda_delivery_id IS
164:
165: /* Combined the above three cursors into one */
166: CURSOR c_mmtt_pick_drop IS
167: SELECT wda.delivery_id, mmtt.organization_id, mmtt.transfer_subinventory
168: FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
169: , mtl_material_transactions_temp mmtt
170: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
171: AND wdd.move_order_line_id = mmtt.move_order_line_id
172: AND mmtt.transaction_temp_id = p_transaction_id;
178: * New cursor to derive delivery_id with LPN */
179: CURSOR c_lpn_delivery(p_lpn_id NUMBER) IS
180: SELECT wda.delivery_id
181: FROM wsh_delivery_assignments_v wda
182: ,wsh_delivery_details wdd
183: WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
184: AND wda.delivery_id IS NOT NULL
185: AND wdd.lpn_id IS NOT NULL
186: AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
379: , wsh_trips wt
380: --Added to fix the issue reported in the Bug#4445708
381: , ( select wda.delivery_id delivery_id, party_site.addressee addressee -- Added p_delivery_id 13108954
382: , wdd.customer_id customer_id
383: from wsh_delivery_details wdd
384: , wsh_delivery_assignments wda
385: , hz_cust_site_uses_all hcsua
386: , hz_party_sites party_site
387: , hz_loc_assignments loc_assign
497: -- Old Design.
498: -- when the transactions manager calls printing for business flow of 'Pick Drop' (19) and passes the
499: -- transactions_temp_id, we derive the the transfer_lpn_id and the content_lpn_id from the MMTT and using either
500: -- the transfer_lpn_id or content_lpn_id (imp: only if transfer_lpn_id is null) derive the delivery_detail_id
501: -- from the wsh_delivery_details.
502: -- There may be no delivery at Pick Load, Pick Drop, and Cartonization is the users fails to create one.
503: -- But a delivery ID will be automatically created after ship confirm.
504: -- This means that of the business flows of Pick Load, Pick Drop, and Cartonization there may be no delivery
505: -- label printed on some ocassions mentioned above.
533: -- Bug 5740331 - Added the following block to fetch the sales order header.
534: BEGIN
535: SELECT DISTINCT(source_header_id)
536: INTO l_sales_order_header_id
537: FROM wsh_delivery_details wdd,
538: wsh_delivery_assignments wda
539: WHERE wdd.delivery_detail_id = wda.delivery_detail_id
540: AND wda.delivery_id = l_delivery_id
541: AND wdd.container_flag = 'N' ;