[Home] [Help]
18: IS
19: l_lpn_count NUMBER := 0;
20: BEGIN
21: SELECT count(*) INTO l_lpn_count
22: FROM wsh_delivery_assignments_v
23: WHERE delivery_id = p_delivery_id
24: AND parent_delivery_detail_id is null;
25: RETURN l_lpn_count;
26: EXCEPTION
38: CURSOR Dup_Staging_txn_cur IS
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
110: ------------------------End of this change for Custom Labels project code--------------------
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;
118: AND wdd.delivery_detail_id(+) = wda.delivery_detail_id;
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
129: -- Fix for Bug# 3786272. Removed the 'wms_packaging_hist' from the 'FROM' clause.
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
158:
159:
160: CURSOR c_wda_delivery_id IS
161: SELECT delivery_id
162: FROM wsh_delivery_assignments_v wda
163: WHERE wda.delivery_detail_id = l_delivery_detail_id;*/
164:
165: /* Combined the above three cursors into one */
166: CURSOR c_mmtt_pick_drop 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;
177: * Delivery ID can be derived with the LPN_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
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
388: , hz_locations loc
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' ;
542: