[Home] [Help]
PACKAGE BODY: APPS.WMS_INSERT_WDTH_PVT
Source
1 PACKAGE BODY wms_insert_wdth_pvt AS
2 /* $Header: WMSWDTHB.pls 120.3.12020000.2 2013/03/26 09:33:11 pyerrams ship $ */
3
4 g_pkg_body_ver CONSTANT VARCHAR2(100) := '$Header: WMSWDTHB.pls 120.3.12020000.2 2013/03/26 09:33:11 pyerrams ship $';
5 g_newline CONSTANT VARCHAR2(10) := fnd_global.newline;
6
7 PROCEDURE print_debug
8 ( p_msg IN VARCHAR2
9 , p_api_name IN VARCHAR2
10 ) IS
11 BEGIN
12 inv_log_util.trace
13 ( p_message => p_msg
14 , p_module => g_pkg_name || '.' || p_api_name
15 , p_level => 4
16 );
17 END print_debug;
18
19
20
21 PROCEDURE print_version_info
22 IS
23 BEGIN
24 print_debug ('Spec:: ' || g_pkg_spec_ver, 'print_version_info');
25 print_debug ('Body:: ' || g_pkg_body_ver, 'print_version_info');
26 END print_version_info;
27
28 PROCEDURE insert_into_wdth
29 ( x_return_status OUT NOCOPY VARCHAR2,
30 p_txn_header_id IN NUMBER,
31 p_transaction_temp_id IN NUMBER,
32 p_transaction_batch_id IN NUMBER,
33 p_transaction_batch_seq IN NUMBER,
34 p_transfer_lpn_id IN NUMBER) IS
35 BEGIN
36 insert_into_wdth
37 (x_return_status => x_return_status,
38 p_txn_header_id => p_txn_header_id,
39 p_transaction_temp_id => p_transaction_temp_id,
40 p_transaction_batch_id => p_transaction_batch_id,
41 p_transaction_batch_seq => p_transaction_batch_seq,
42 p_transfer_lpn_id => p_transfer_lpn_id,
43 p_status => 6);
44 END;
45
46
47 PROCEDURE insert_into_wdth
48 ( x_return_status OUT NOCOPY VARCHAR2,
49 p_txn_header_id IN NUMBER,
50 p_transaction_temp_id IN NUMBER,
51 p_transaction_batch_id IN NUMBER,
52 p_transaction_batch_seq IN NUMBER,
53 p_transfer_lpn_id IN NUMBER,
54 p_status IN NUMBER,
55 p_orig_transaction_temp_id IN NUMBER DEFAULT NULL) IS --Bug12812580
56
57 l_api_name VARCHAR2(30) := 'insert_into_wdth';
58 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
59
60 BEGIN
61 x_return_status := fnd_api.g_ret_sts_success;
62
63 IF l_debug = 1 THEN
64 print_version_info;
65 print_debug
66 ( 'Entered with parameters: ' || g_newline ||
67 'p_txn_header_id => ' || to_char(p_txn_header_id) || g_newline ||
68 'p_transaction_temp_id => ' || to_char(p_transaction_temp_id) || g_newline ||
69 'p_transaction_batch_id => ' || to_char(p_transaction_batch_id) || g_newline ||
70 'p_transaction_batch_seq => ' || to_char(p_transaction_batch_seq) || g_newline ||
71 'p_transfer_lpn_id => ' || to_char(p_transfer_lpn_id) || g_newline ||
72 'p_status => ' || to_char(p_status) || g_newline ||
73 'p_orig_transaction_temp_id => ' || to_char(p_orig_transaction_temp_id)
74 , l_api_name
75 );
76 END IF;
77
78 IF (p_orig_transaction_temp_id IS NULL OR p_orig_transaction_temp_id = p_transaction_temp_id) THEN --Bug12812580
79 INSERT INTO wms_dispatched_tasks_history
80 ( task_id
81 , transaction_id
82 , organization_id
83 , transaction_batch_id
84 , transaction_batch_seq
85 , user_task_type
86 , person_id
87 , effective_start_date
88 , effective_end_date
89 , equipment_id
90 , equipment_instance
91 , person_resource_id
92 , machine_resource_id
93 , status
94 , dispatched_time
95 , last_update_date
96 , last_updated_by
97 , creation_date
98 , created_by
99 , task_type
100 , loaded_time
101 , drop_off_time
102 , suggested_dest_subinventory
103 , suggested_dest_locator_id
104 , operation_plan_id
105 , move_order_line_id
106 , transfer_lpn_id
107 , inventory_item_id
108 , revision
109 , transaction_type_id
110 , transaction_source_type_id
111 , transaction_action_id
112 , source_subinventory_code
113 , source_locator_id
114 , dest_subinventory_code
115 , dest_locator_id
116 , lpn_id
117 , content_lpn_id
118 , transaction_temp_id
119 , priority -- For bug 5401222
120 ,is_parent --ER13869750
121 ,transaction_quantity --ER13869750
122 ,transaction_uom_code --ER13869750
123 ,secondary_transaction_quantity --ER13869750
124 ,secondary_transaction_uom_code --ER13869750
125 )
126 (SELECT wdt.task_id
127 , p_txn_header_id
128 , wdt.organization_id
129 , p_transaction_batch_id
130 , p_transaction_batch_seq
131 , wdt.user_task_type
132 , wdt.person_id
133 , SYSDATE -- wdt.effective_start_date (for bug#5412974)
134 , SYSDATE -- wdt.effective_end_date (for bug#5412974)
135 , wdt.equipment_id
136 , wdt.equipment_instance
137 , wdt.person_resource_id
138 , wdt.machine_resource_id
139 , p_status
140 , wdt.dispatched_time
141 , SYSDATE
142 , fnd_global.user_id
143 , SYSDATE
144 , fnd_global.user_id
145 , wdt.task_type
146 , wdt.loaded_time
147 , SYSDATE
148 , wdt.suggested_dest_subinventory
149 , wdt.suggested_dest_locator_id
150 , wdt.operation_plan_id
151 , wdt.move_order_line_id
152 , p_transfer_lpn_id
153 , mmtt.inventory_item_id
154 , mmtt.revision
155 , mmtt.transaction_type_id
156 , mmtt.transaction_source_type_id
157 , mmtt.transaction_action_id
158 , mmtt.subinventory_code
159 , mmtt.locator_id
160 , mmtt.transfer_subinventory
161 , mmtt.transfer_to_location
162 , mmtt.lpn_id
163 , mmtt.content_lpn_id
164 , mmtt.transaction_temp_id
165 , nvl(wdt.priority,mmtt.task_priority) -- For bug 5401222
166 ,Decode (mmtt.parent_line_id,NULL,'N','Y') --ER13869750
167 ,mmtt.transaction_quantity --ER13869750
168 ,mmtt.transaction_UOM --ER13869750
169 ,mmtt.secondary_transaction_quantity --ER13869750
170 ,substr(mmtt.secondary_uom_code,1,3) --ER13869750
171 FROM wms_dispatched_tasks wdt
172 , mtl_material_transactions_temp mmtt
173 WHERE wdt.transaction_temp_id = p_transaction_temp_id
174 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
175 );
176
177 ELSE --Bug12812580
178
179 INSERT INTO wms_dispatched_tasks_history
180 ( task_id
181 , transaction_id
182 , organization_id
183 , transaction_batch_id
184 , transaction_batch_seq
185 , user_task_type
186 , person_id
187 , effective_start_date
188 , effective_end_date
189 , equipment_id
190 , equipment_instance
191 , person_resource_id
192 , machine_resource_id
193 , status
194 , dispatched_time
195 , last_update_date
196 , last_updated_by
197 , creation_date
198 , created_by
199 , task_type
200 , loaded_time
201 , drop_off_time
202 , suggested_dest_subinventory
203 , suggested_dest_locator_id
204 , operation_plan_id
205 , move_order_line_id
206 , transfer_lpn_id
207 , inventory_item_id
208 , revision
209 , transaction_type_id
210 , transaction_source_type_id
211 , transaction_action_id
212 , source_subinventory_code
213 , source_locator_id
214 , dest_subinventory_code
215 , dest_locator_id
216 , lpn_id
217 , content_lpn_id
218 , transaction_temp_id
219 , priority -- For bug 5401222
220 ,is_parent --ER13869750
221 ,transaction_quantity --ER13869750
222 ,transaction_uom_code --ER13869750
223 ,secondary_transaction_quantity --ER13869750
224 ,secondary_transaction_uom_code --ER13869750
225 )
226 (SELECT Nvl(wdt.task_id, wms_dispatched_tasks_s.NEXTVAL)
227 , p_txn_header_id
228 , Nvl(wdt.organization_id, wdt_orig.organization_id)
229 , p_transaction_batch_id
230 , p_transaction_batch_seq
231 , Nvl(wdt.user_task_type, wdt_orig.user_task_type)
232 , Nvl(wdt.person_id, wdt_orig.person_id)
233 , SYSDATE -- wdt.effective_start_date (for bug#5412974)
234 , SYSDATE -- wdt.effective_end_date (for bug#5412974)
235 , Nvl(wdt.equipment_id, wdt_orig.equipment_id)
236 , Nvl(wdt.equipment_instance, wdt_orig.equipment_instance)
237 , Nvl(wdt.person_resource_id, wdt_orig.person_resource_id)
238 , Nvl(wdt.machine_resource_id, wdt_orig.machine_resource_id)
239 , p_status
240 , wdt.dispatched_time
241 , SYSDATE
242 , fnd_global.user_id
243 , SYSDATE
244 , fnd_global.user_id
245 , Nvl(wdt.task_type, wdt_orig.task_type)
246 , wdt.loaded_time
247 , SYSDATE
248 , wdt.suggested_dest_subinventory
249 , wdt.suggested_dest_locator_id
250 , wdt.operation_plan_id
251 , Nvl(wdt.move_order_line_id, mmtt.move_order_line_id)
252 , p_transfer_lpn_id
253 , mmtt.inventory_item_id
254 , mmtt.revision
255 , mmtt.transaction_type_id
256 , mmtt.transaction_source_type_id
257 , mmtt.transaction_action_id
258 , mmtt.subinventory_code
259 , mmtt.locator_id
260 , mmtt.transfer_subinventory
261 , mmtt.transfer_to_location
262 , mmtt.lpn_id
263 , mmtt.content_lpn_id
264 , mmtt.transaction_temp_id
265 , nvl(wdt.priority,mmtt.task_priority) -- For bug 5401222
266 ,Decode (mmtt.parent_line_id,NULL,'N','Y') --ER13869750
267 ,mmtt.transaction_quantity --ER13869750
268 ,mmtt.transaction_UOM --ER13869750
269 ,mmtt.secondary_transaction_quantity --ER13869750
270 ,substr(mmtt.secondary_uom_code,1,3) --ER13869750
271 FROM wms_dispatched_tasks wdt
272 , mtl_material_transactions_temp mmtt
273 , wms_dispatched_tasks wdt_orig
274 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
275 AND mmtt.transaction_temp_id = wdt.transaction_temp_id (+)
276 AND wdt_orig.transaction_temp_id = p_orig_transaction_temp_id
277 );
278
279 END IF;
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 x_return_status := fnd_api.g_ret_sts_unexp_error;
284
285 IF l_debug = 1 THEN
286 print_debug ('Other error: ' || sqlerrm, l_api_name);
287 END IF;
288
289 END insert_into_wdth;
290
291 END wms_insert_wdth_pvt;