[Home] [Help]
PACKAGE BODY: APPS.WMS_OP_RUNTIME_PVT_APIS
Source
1 PACKAGE BODY WMS_OP_RUNTIME_PVT_APIS AS
2 /*$Header: WMSOPPVB.pls 120.4.12020000.2 2012/09/22 11:55:45 ssrikaku ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_OP_RUNTIME_PVT_APIS';
5 G_VERSION_PRINTED BOOLEAN := FALSE;
6
7 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
8 G_MISS_CHAR CONSTANT VARCHAR2(1):= FND_API.G_MISS_CHAR;
9 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
10
11 G_ACTION_RECEIPT CONSTANT NUMBER := inv_globals.g_action_receipt ;
12 G_ACTION_INTRANSITRECEIPT CONSTANT NUMBER := inv_globals.G_ACTION_INTRANSITRECEIPT;
13 G_ACTION_SUBXFR CONSTANT NUMBER := inv_globals.g_action_subxfr;
14 G_SOURCETYPE_MOVEORDER CONSTANT NUMBER := inv_globals.g_sourcetype_moveorder;
15 G_SOURCETYPE_PURCHASEORDER CONSTANT NUMBER := inv_globals.G_SOURCETYPE_PURCHASEORDER;
16 G_SOURCETYPE_INTREQ CONSTANT NUMBER := inv_globals.G_SOURCETYPE_INTREQ;
17 G_SOURCETYPE_RMA CONSTANT NUMBER := inv_globals.G_SOURCETYPE_RMA;
18 G_TYPE_TRANSFER_ORDER_SUBXFR CONSTANT NUMBER := inv_globals.g_type_transfer_order_subxfr;
19
20 /**
21 * Procedure to print the Debug Messages
22 */
23 PROCEDURE print_debug(p_message IN VARCHAR2, p_module IN VARCHAR2,p_level NUMBER DEFAULT 9) IS
24 BEGIN
25 IF NOT g_version_printed THEN
26 inv_log_util.trace('$Header: WMSOPPVB.pls 120.4.12020000.2 2012/09/22 11:55:45 ssrikaku ship $',g_pkg_name, 9);
27 g_version_printed := TRUE;
28 END IF;
29 inv_log_util.trace(p_message, g_pkg_name || '.' || p_module,p_level);
30 END;
31
32
33 /**
34 * <p>Procedure:<b>Insert_Plan_instance</b>
35 * This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.</p>
36 * @param p_insert_rec - Record Variable of type WMS_OP_PLAN_INSTANCES%rowtype
37 * @param x_return_status - Return Status
38 * @param x_msg_count - Returns the Message Count
39 * @param x_msg_data - Returns Error Message
40 */
41 PROCEDURE insert_plan_instance
42 (p_insert_rec IN WMS_OP_PLAN_INSTANCES%ROWTYPE,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE) IS
46
47 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
48 l_module_name CONSTANT VARCHAR2(30) := 'Insert_Plan_Instance';
49
50 BEGIN
51
52 x_return_status:=FND_API.G_RET_STS_SUCCESS;
53
54 IF (l_debug=1) THEN
55 print_debug('Plan Instance Id:'||p_insert_rec.op_plan_instance_id,l_module_name,3);
56 END IF;
57
58
59 IF (l_debug=1) THEN
60 print_debug('Inserting Records into WMS_OP_PLAN_INSTANCES',l_module_name,9);
61 END IF;
62
63 INSERT INTO WMS_OP_PLAN_INSTANCES
64 (OP_PLAN_INSTANCE_ID,
65 OPERATION_PLAN_ID,
66 ACTIVITY_TYPE_ID,
67 PLAN_TYPE_ID,
68 SOURCE_TASK_ID,
69 STATUS,
70 PLAN_EXECUTION_START_DATE,
71 PLAN_EXECUTION_END_DATE,
72 ORGANIZATION_ID,
73 ORIG_SOURCE_SUB_CODE,
74 ORIG_SOURCE_LOC_ID,
75 ORIG_DEST_SUB_CODE,
76 ORIG_DEST_LOC_ID,
77 LAST_UPDATE_DATE,
78 LAST_UPDATED_BY,
79 CREATION_DATE,
80 CREATED_BY,
81 LAST_UPDATE_LOGIN,
82 ATTRIBUTE_CATEGORY,
83 ATTRIBUTE1,
84 ATTRIBUTE2,
85 ATTRIBUTE3,
86 ATTRIBUTE4,
87 ATTRIBUTE5,
88 ATTRIBUTE6,
89 ATTRIBUTE7,
90 ATTRIBUTE8,
91 ATTRIBUTE9,
92 ATTRIBUTE10,
93 ATTRIBUTE11,
94 ATTRIBUTE12,
95 ATTRIBUTE13,
96 ATTRIBUTE14,
97 ATTRIBUTE15)
98 VALUES
99 (decode(p_insert_rec.op_plan_instance_id,NULL,wms_op_instance_s.NEXTVAL,p_insert_rec.op_plan_instance_id),
100 p_insert_rec.OPERATION_PLAN_ID,
101 p_insert_rec.ACTIVITY_TYPE_ID,
102 p_insert_rec.PLAN_TYPE_ID,
103 p_insert_rec.SOURCE_TASK_ID,
104 p_insert_rec.STATUS,
105 p_insert_rec.PLAN_EXECUTION_START_DATE,
106 p_insert_rec.PLAN_EXECUTION_END_DATE,
107 p_insert_rec.ORGANIZATION_ID,
108 p_insert_rec.ORIG_SOURCE_SUB_CODE,
109 p_insert_rec.ORIG_SOURCE_LOC_ID,
110 p_insert_rec.ORIG_DEST_SUB_CODE,
111 p_insert_rec.ORIG_DEST_LOC_ID,
112 SYSDATE,
113 FND_GLOBAL.USER_ID,
114 SYSDATE,
115 FND_GLOBAL.USER_ID,
116 p_insert_rec.LAST_UPDATE_LOGIN,
117 p_insert_rec.ATTRIBUTE_CATEGORY,
118 p_insert_rec.ATTRIBUTE1,
119 p_insert_rec.ATTRIBUTE2,
120 p_insert_rec.ATTRIBUTE3,
121 p_insert_rec.ATTRIBUTE4,
122 p_insert_rec.ATTRIBUTE5,
123 p_insert_rec.ATTRIBUTE6,
124 p_insert_rec.ATTRIBUTE7,
125 p_insert_rec.ATTRIBUTE8,
126 p_insert_rec.ATTRIBUTE9,
127 p_insert_rec.ATTRIBUTE10,
128 p_insert_rec.ATTRIBUTE11,
129 p_insert_rec.ATTRIBUTE12,
130 p_insert_rec.ATTRIBUTE13,
131 p_insert_rec.ATTRIBUTE14,
132 p_insert_rec.ATTRIBUTE15);
133
134 IF SQL%NOTFOUND THEN
135 RAISE fnd_api.g_exc_unexpected_error;
136 END IF;
137
138 EXCEPTION
139
140 WHEN OTHERS THEN
141
142 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
143 IF (l_debug=1) THEN
144 print_debug('Unexpected Error,Insertion failed'||SQLERRM,l_module_name,3);
145 END IF;
146 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
147 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
148 END IF;
149 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
150
151 END;
152
153
154 /**
155 * <p>Procedure:<b>Update_Plan_instance</b>
156 * This procedure updates data into the table WMS_OP_PLAN_INSTANCES.</p>
157 * @param p_insert_rec - Record Variable of type WMS_OP_PLAN_INSTANCES%rowtype
158 * @param x_return_status - Return Status
159 * @param x_msg_count - Returns Message Count
160 * @param x_msg_data - Returns Error Message
161 */
162 PROCEDURE update_plan_instance
163 (p_update_rec IN WMS_OP_PLAN_INSTANCES%ROWTYPE,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE) IS
167
168 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
169 l_module_name CONSTANT VARCHAR2(30) := 'Update_Plan_Instance';
170
171 BEGIN
172
173 x_return_status:=FND_API.G_RET_STS_SUCCESS;
174
175 IF (l_debug=1) THEN
176 print_debug('Plan Instance Id:'||p_update_rec.op_plan_instance_id,l_module_name,3);
177 END IF;
178
179 IF p_update_rec.op_plan_instance_id IS NULL THEN /*Plan instance id is a must for Updation*/
180 IF (l_debug=1) THEN
181 print_debug('Plan Instance Id is null',l_module_name,1);
182 END IF;
183 RAISE fnd_api.g_exc_error;
184 END IF;
185
186 IF (l_debug=1) THEN
187 print_debug('Updating WMS_OP_PLAN_INSTANCES',l_module_name,3);
188 END IF;
189
190 /*Updating WOPI*/
191 UPDATE WMS_OP_PLAN_INSTANCES
192 SET
193 OP_PLAN_INSTANCE_ID = decode(p_update_rec.OP_PLAN_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OP_PLAN_INSTANCE_ID,p_update_rec.OP_PLAN_INSTANCE_ID),
194 OPERATION_PLAN_ID = decode(p_update_rec.OPERATION_PLAN_ID,G_MISS_NUM,NULL,NULL,OPERATION_PLAN_ID,p_update_rec.OPERATION_PLAN_ID),
195 ACTIVITY_TYPE_ID = decode(p_update_rec.ACTIVITY_TYPE_ID,G_MISS_NUM,NULL,NULL,ACTIVITY_TYPE_ID,p_update_rec.ACTIVITY_TYPE_ID),
196 PLAN_TYPE_ID = decode(p_update_rec.PLAN_TYPE_ID,G_MISS_NUM,NULL,NULL,PLAN_TYPE_ID,p_update_rec.PLAN_TYPE_ID),
197 SOURCE_TASK_ID = decode(p_update_rec.SOURCE_TASK_ID,G_MISS_NUM,NULL,NULL,SOURCE_TASK_ID,p_update_rec.SOURCE_TASK_ID),
198 STATUS = decode(p_update_rec.STATUS,G_MISS_CHAR,NULL,NULL,STATUS,p_update_rec.STATUS),
199 PLAN_EXECUTION_START_DATE = decode(p_update_rec.PLAN_EXECUTION_START_DATE,NULL,PLAN_EXECUTION_START_DATE,G_MISS_DATE,NULL,p_update_rec.PLAN_EXECUTION_START_DATE),
200 PLAN_EXECUTION_END_DATE = decode(p_update_rec.PLAN_EXECUTION_END_DATE,NULL,PLAN_EXECUTION_END_DATE,G_MISS_DATE,NULL,p_update_rec.PLAN_EXECUTION_END_DATE),
201 ORGANIZATION_ID = decode(p_update_rec.ORGANIZATION_ID,G_MISS_NUM,NULL,NULL,ORGANIZATION_ID,p_update_rec.ORGANIZATION_ID),
202 ORIG_SOURCE_SUB_CODE = decode(p_update_rec.ORIG_SOURCE_SUB_CODE,G_MISS_CHAR,NULL,NULL,ORIG_SOURCE_SUB_CODE,p_update_rec.ORIG_SOURCE_SUB_CODE),
203 ORIG_SOURCE_LOC_ID = decode(p_update_rec.ORIG_SOURCE_LOC_ID,G_MISS_NUM,NULL,NULL,ORIG_SOURCE_LOC_ID,p_update_rec.ORIG_SOURCE_LOC_ID),
204 ORIG_DEST_SUB_CODE = decode(p_update_rec.ORIG_DEST_SUB_CODE,G_MISS_CHAR,NULL,NULL,ORIG_DEST_SUB_CODE,p_update_rec.ORIG_DEST_SUB_CODE),
205 ORIG_DEST_LOC_ID = decode(p_update_rec.ORIG_DEST_LOC_ID,G_MISS_NUM,NULL,NULL,ORIG_DEST_LOC_ID,p_update_rec.ORIG_DEST_LOC_ID),
206 LAST_UPDATE_DATE = SYSDATE,
207 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
208 LAST_UPDATE_LOGIN = decode(p_update_rec.LAST_UPDATE_LOGIN,G_MISS_NUM,NULL,NULL,LAST_UPDATE_LOGIN,p_update_rec.LAST_UPDATE_LOGIN),
209 ATTRIBUTE_CATEGORY = decode(p_update_rec.ATTRIBUTE_CATEGORY,G_MISS_CHAR,NULL,NULL,ATTRIBUTE_CATEGORY,p_update_rec.ATTRIBUTE_CATEGORY),
210 ATTRIBUTE1 = decode(p_update_rec.ATTRIBUTE1,G_MISS_CHAR,NULL,NULL,ATTRIBUTE1,p_update_rec.ATTRIBUTE1),
211 ATTRIBUTE2 = decode(p_update_rec.ATTRIBUTE2,G_MISS_CHAR,NULL,NULL,ATTRIBUTE2,p_update_rec.ATTRIBUTE2),
212 ATTRIBUTE3 = decode(p_update_rec.ATTRIBUTE3,G_MISS_CHAR,NULL,NULL,ATTRIBUTE3,p_update_rec.ATTRIBUTE3),
213 ATTRIBUTE4 = decode(p_update_rec.ATTRIBUTE4,G_MISS_CHAR,NULL,NULL,ATTRIBUTE4,p_update_rec.ATTRIBUTE4),
214 ATTRIBUTE5 = decode(p_update_rec.ATTRIBUTE5,G_MISS_CHAR,NULL,NULL,ATTRIBUTE5,p_update_rec.ATTRIBUTE5),
215 ATTRIBUTE6 = decode(p_update_rec.ATTRIBUTE6,G_MISS_CHAR,NULL,NULL,ATTRIBUTE6,p_update_rec.ATTRIBUTE6),
216 ATTRIBUTE7 = decode(p_update_rec.ATTRIBUTE7,G_MISS_CHAR,NULL,NULL,ATTRIBUTE7,p_update_rec.ATTRIBUTE7),
217 ATTRIBUTE8 = decode(p_update_rec.ATTRIBUTE8,G_MISS_CHAR,NULL,NULL,ATTRIBUTE8,p_update_rec.ATTRIBUTE8),
218 ATTRIBUTE9 = decode(p_update_rec.ATTRIBUTE9,G_MISS_CHAR,NULL,NULL,ATTRIBUTE9,p_update_rec.ATTRIBUTE9),
219 ATTRIBUTE10 = decode(p_update_rec.ATTRIBUTE10,G_MISS_CHAR,NULL,NULL,ATTRIBUTE10,p_update_rec.ATTRIBUTE10),
220 ATTRIBUTE11 = decode(p_update_rec.ATTRIBUTE11,G_MISS_CHAR,NULL,NULL,ATTRIBUTE11,p_update_rec.ATTRIBUTE11),
221 ATTRIBUTE12 = decode(p_update_rec.ATTRIBUTE12,G_MISS_CHAR,NULL,NULL,ATTRIBUTE12,p_update_rec.ATTRIBUTE12),
222 ATTRIBUTE13 = decode(p_update_rec.ATTRIBUTE13,G_MISS_CHAR,NULL,NULL,ATTRIBUTE13,p_update_rec.ATTRIBUTE13),
223 ATTRIBUTE14 = decode(p_update_rec.ATTRIBUTE14,G_MISS_CHAR,NULL,NULL,ATTRIBUTE14,p_update_rec.ATTRIBUTE14),
224 ATTRIBUTE15 = decode(p_update_rec.ATTRIBUTE15,G_MISS_CHAR,NULL,NULL,ATTRIBUTE15,p_update_rec.ATTRIBUTE15)
225 WHERE op_plan_instance_id=p_update_rec.op_plan_instance_id ;
226 --
227 IF SQL%notfound THEN
228 IF (l_debug=1) THEN
229 print_debug('Updating Plan Instance failed',l_module_name,1);
230 END IF;
231 RAISE fnd_api.g_exc_unexpected_error;
232 END IF;
233 --
234 EXCEPTION
235 WHEN fnd_api.g_exc_error THEN
236 x_return_status:=FND_API.G_RET_STS_ERROR;
237 --
238 IF (l_debug=1) THEN
239 print_debug('Expected Error:Updation failed ',l_module_name,1);
240 END IF;
241 /*Message or error code to be populated for Operation PLan Instance Id Null*/
242
243 WHEN OTHERS THEN
244
245 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
246
247 IF (l_debug=1) THEN
248 print_debug('Unexpected Error:'||SQLERRM,l_module_name,1);
249 END IF;
250
251 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
252 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
253 END IF;
254
255 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
256
257 END;
258
259 /**
260 * <p>Procedure:<b>Delete_Plan_instance</b>
261 * This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.</p>
262 * @param p_op_plan_instance_id - Operation Plan Instance Id of the Plan that has to be deleted
263 * @param x_return_status - Return Status
264 * @param x_msg_count - Returns Message Count
265 * @param x_msg_data - Returns Error Message
266 */
267
268 PROCEDURE delete_plan_instance
269 (p_op_plan_instance_id IN NUMBER,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE)IS
273
274 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
275 l_module_name CONSTANT VARCHAR2(30):= 'Delete_Plan_Instance';
276
277 BEGIN
278 x_return_status:=fnd_api.g_RET_STS_SUCCESS;
279 --
280 IF (l_debug=1) THEN
281 print_debug('Plan Instance Id:'||p_op_plan_instance_id,l_module_name,3);
282 END IF;
283
284 IF p_op_plan_instance_id IS NULL THEN
285 IF (l_debug=1) THEN
286 print_debug('Plan Instance Id is null','Delete_Plan_instance',3);
287 END IF;
288 RAISE fnd_api.g_exc_error;
289 END IF;
290
291 IF (l_debug=1) THEN
292 print_debug('Deleting Plan Instances','Delete_Plan_instance',9);
293 END IF;
294
295 DELETE FROM WMS_OP_PLAN_INSTANCES
296 WHERE OP_PLAN_INSTANCE_ID = P_op_plan_instance_id;
297
298 IF SQL%notfound THEN
299 IF (l_debug=1) THEN
300 print_debug('Record not found while deleting','Delete_Plan_Instance',1);
301 END IF;
302 RAISE fnd_api.g_exc_unexpected_error;
303 END IF;
304
305 EXCEPTION
306
307 WHEN fnd_api.g_exc_error THEN
308 x_return_status:=FND_API.G_RET_STS_ERROR;
309 IF (l_debug=1) THEN
310 print_debug('Error while deleting Plan Instance','Delete_Plan_Instance',1);
311 END IF;
312 /*Message or error code to be populated for Operation PLan Instance Id Null*/
313
314
315 WHEN OTHERS THEN
316 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
317 IF (l_debug=1) THEN
318 print_debug('Unexpected Error While Deleting'||SQLERRM,'Delete_Plan_Instance',1);
319 END IF;
320 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
321 fnd_msg_pub.add_exc_msg(g_pkg_name, 'DELETE_PLAN_INSTANCE');
322 END IF;
323 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
324 END;
325
326 /**
327 * <p>Procedure:<b>Insert_operation_instance</b>
328 * This procedure inserts data into the table WMS_OP_OPERATION_INSTANCES.</p>
329 * @param p_insert_rec - Record Variable of type WMS_OP_OPERATION_INSTANCES%rowtype
330 * @param x_return_status - Return Status
331 * @param x_msg_count - Returns Message Count
332 * @param x_msg_data - Returns Error Message
333 */
334
335 PROCEDURE insert_operation_instance
336 (p_insert_rec IN WMS_OP_OPERATION_INSTANCES%ROWTYPE,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE) IS
340
341 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
342 l_module_name CONSTANT VARCHAR2(30):='Insert_Operation_Instance';
343
344 BEGIN
345 x_return_status:=fnd_api.g_ret_sts_success;
346 IF (l_debug=1) THEN
347 print_debug('Operation Instance Id:'||p_insert_rec.operation_instance_id,l_module_name,3);
348 END IF;
349
350 IF (l_debug=1) THEN
351 print_debug('Inserting records into WMS_OP_OPERATION_INSTANCES',l_module_name,9);
352 END IF;
353
354 INSERT INTO WMS_OP_OPERATION_INSTANCES (
355 OPERATION_INSTANCE_ID,
356 OPERATION_PLAN_DETAIL_ID,
357 OPERATION_SEQUENCE,
358 OPERATION_TYPE_ID,
359 OP_PLAN_INSTANCE_ID,
360 OPERATION_STATUS,
361 ACTIVITY_TYPE_ID,
362 SOURCE_TASK_ID,
363 ACTIVATE_TIME,
364 COMPLETE_TIME,
365 SUG_TO_SUB_CODE,
366 SUG_TO_LOCATOR_ID,
367 FROM_SUBINVENTORY_CODE,
368 FROM_LOCATOR_ID,
369 TO_SUBINVENTORY_CODE,
370 TO_LOCATOR_ID,
371 IS_IN_INVENTORY,
372 ORGANIZATION_ID,
373 EMPLOYEE_ID,
374 EQUIPMENT_ID,
375 CREATED_BY,
376 LAST_UPDATE_DATE,
377 CREATION_DATE,
378 LAST_UPDATED_BY,
379 LAST_UPDATE_LOGIN,
380 ATTRIBUTE_CATEGORY,
381 ATTRIBUTE1,
382 ATTRIBUTE2,
383 ATTRIBUTE3,
384 ATTRIBUTE4,
385 ATTRIBUTE5,
386 ATTRIBUTE6,
387 ATTRIBUTE7,
388 ATTRIBUTE8,
389 ATTRIBUTE9,
390 ATTRIBUTE10,
391 ATTRIBUTE11,
392 ATTRIBUTE12,
393 ATTRIBUTE13,
394 ATTRIBUTE14,
395 ATTRIBUTE15)
396 VALUES
397 (decode(p_insert_rec.operation_instance_id,NULL,wms_op_instance_s.NEXTVAL,p_insert_rec.operation_instance_id),
398 p_insert_rec.OPERATION_PLAN_DETAIL_ID,
399 p_insert_rec.OPERATION_SEQUENCE,
400 p_insert_rec.OPERATION_TYPE_ID,
401 p_insert_rec.OP_PLAN_INSTANCE_ID,
402 p_insert_rec.OPERATION_STATUS,
403 p_insert_rec.ACTIVITY_TYPE_ID,
404 p_insert_rec.SOURCE_TASK_ID,
405 p_insert_rec.ACTIVATE_TIME,
406 p_insert_rec.COMPLETE_TIME,
407 p_insert_rec.SUG_TO_SUB_CODE,
408 p_insert_rec.SUG_TO_LOCATOR_ID,
409 p_insert_rec.FROM_SUBINVENTORY_CODE,
410 p_insert_rec.FROM_LOCATOR_ID,
411 p_insert_rec.TO_SUBINVENTORY_CODE,
412 p_insert_rec.TO_LOCATOR_ID,
413 p_insert_rec.IS_IN_INVENTORY,
414 p_insert_rec.ORGANIZATION_ID,
415 p_insert_rec.EMPLOYEE_ID,
416 p_insert_rec.EQUIPMENT_ID,
417 FND_GLOBAL.USER_ID,
418 SYSDATE,
419 SYSDATE,
420 FND_GLOBAL.USER_ID,
421 p_insert_rec.LAST_UPDATE_LOGIN,
422 p_insert_rec.ATTRIBUTE_CATEGORY,
423 p_insert_rec.ATTRIBUTE1,
424 p_insert_rec.ATTRIBUTE2,
425 p_insert_rec.ATTRIBUTE3,
426 p_insert_rec.ATTRIBUTE4,
427 p_insert_rec.ATTRIBUTE5,
428 p_insert_rec.ATTRIBUTE6,
429 p_insert_rec.ATTRIBUTE7,
430 p_insert_rec.ATTRIBUTE8,
431 p_insert_rec.ATTRIBUTE9,
432 p_insert_rec.ATTRIBUTE10,
433 p_insert_rec.ATTRIBUTE11,
434 p_insert_rec.ATTRIBUTE12,
435 p_insert_rec.ATTRIBUTE13,
436 p_insert_rec.ATTRIBUTE14,
437 p_insert_rec.ATTRIBUTE15);
438
439
440 EXCEPTION
441
442 WHEN OTHERS THEN
443
444 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
445 IF (l_debug=1) THEN
446 print_debug('Unexptec Error while inserting'||SQLERRM,l_module_name,1);
447 END IF;
448
449 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
450 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
451 END IF;
452
453 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
454
455 END;
456
457
458 /**
459 * <p>Procedure:<b>Delete_Operation_instance</b>
460 * This procedure deletes the data in the table WMS_OP_PLAN_INSTANCES.</p>
461 * @param p_operation_instance_id - Plan Instance Id of all the Operations that has to be deleted
462 * @param x_return_status - Return Status
463 * @param x_msg_count - Returns Message Count
464 * @param x_msg_data - Returns Error Message
465 */
466 PROCEDURE delete_operation_instance
467 (p_operation_instance_id IN NUMBER,
468 x_return_status OUT NOCOPY VARCHAR2,
469 x_msg_count OUT NOCOPY NUMBER,
470 x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE)IS
471
472 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
473 l_module_name CONSTANT VARCHAR2(30) :='Delete_operation_instance';
474
475 BEGIN
476 x_return_status:=fnd_api.G_RET_STS_SUCCESS;
477 IF (l_debug=1) THEN
478 print_debug('Operation Instance Id:'||p_operation_instance_id,l_module_name,3);
479 END IF;
480
481 IF p_operation_instance_id IS NULL THEN
482
483 IF (l_debug=1) THEN
484 print_debug('Operation Instance Id is null',l_module_name,1);
485 END IF;
486
487 RAISE fnd_api.G_exc_error;
488 END IF;
489
490 IF (l_debug=1) THEN
491 print_debug('Deleting Records from WMS_OP_OPERATION_INSTANCES',l_module_name,9);
492 END IF;
493
494 DELETE FROM wms_op_operation_instances
495 WHERE operation_instance_id = p_operation_instance_id;
496
497 IF (SQL%notfound) THEN
498 IF (l_debug=1) THEN
499 print_debug('Deleting of Record failed as no record found',l_module_name,1);
500 END IF;
501 RAISE fnd_api.g_exc_unexpected_error;
502 END IF;
503
504 EXCEPTION
505
506 WHEN fnd_api.g_exc_error THEN
507 x_return_status:=FND_API.G_RET_STS_ERROR;
508
509 IF (l_debug=1) THEN
510 print_debug('Error Deleting Record',l_module_name,1);
511 END IF;
512 /*Message or error code to be populated for Operation PLan Instance Id Null*/
513
514
515 WHEN OTHERS THEN
516 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
517
518 IF (l_debug=1) THEN
519 print_debug('Unexpected Error'||SQLERRM,l_module_name,1);
520 END IF;
521
522 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
523 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
524 END IF;
525
526 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
527 END;
528
529
530 /**
531 * <p>Procedure:<b>Update_Operation_instance</b>
532 * This procedure updates data into the table WMS_OP_PLAN_INSTANCES.</p>
533 * @param p_update_rec - Record Variable of type WMS_OP_OPERATION_INSTANCES%rowtype
534 * @param x_return_status - Return Status
535 * @param x_msg_count - Returns Message Count
536 * @param x_msg_data - Returns Error Message
537 */
538 PROCEDURE update_operation_instance
539 (p_update_rec IN WMS_OP_OPERATION_INSTANCES%ROWTYPE,
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE)IS
543
544 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
545 l_module_name CONSTANT VARCHAR2(30):='Update_operation_instance';
546
547 BEGIN
548
549 x_return_status:=fnd_api.g_ret_sts_success;
550
551 IF (l_debug=1) THEN
552 print_debug('Operation Instance Id'||p_update_rec.operation_instance_id,l_module_name,3);
553 END IF;
554
555 IF p_update_rec.operation_instance_id IS NULL THEN
556 IF (l_debug=1) THEN
557 print_debug('Operation Instance Id is null',l_module_name,1);
558 END IF;
559
560 RAISE FND_API.G_EXC_ERROR;
561 END IF;
562
563 IF (l_debug=1) THEN
564 print_debug('Updating Operation instance',l_module_name,9);
565 END IF;
566
567 /**Updating Operation Instances*/
568 UPDATE wms_op_operation_instances
569 SET
570 OPERATION_INSTANCE_ID =decode(p_update_rec.OPERATION_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OPERATION_INSTANCE_ID,p_update_rec.OPERATION_INSTANCE_ID),
571 OPERATION_PLAN_DETAIL_ID=decode(p_update_rec.OPERATION_PLAN_DETAIL_ID,G_MISS_NUM,NULL,NULL,OPERATION_PLAN_DETAIL_ID,p_update_rec.OPERATION_PLAN_DETAIL_ID),
572 OPERATION_SEQUENCE =decode(p_update_rec.OPERATION_SEQUENCE,G_MISS_NUM,NULL,NULL,OPERATION_SEQUENCE,p_update_rec.OPERATION_SEQUENCE),
573 OPERATION_TYPE_ID =decode(p_update_rec.OPERATION_TYPE_ID,G_MISS_NUM,NULL,NULL,OPERATION_TYPE_ID,p_update_rec.OPERATION_TYPE_ID),
574 OP_PLAN_INSTANCE_ID =decode(p_update_rec.OP_PLAN_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OP_PLAN_INSTANCE_ID,p_update_rec.OP_PLAN_INSTANCE_ID),
575 OPERATION_STATUS =decode(p_update_rec.OPERATION_STATUS,G_MISS_NUM,NULL,NULL,OPERATION_STATUS,p_update_rec.OPERATION_STATUS),
576 ACTIVITY_TYPE_ID =decode(p_update_rec.ACTIVITY_TYPE_ID,G_MISS_NUM,NULL,NULL,ACTIVITY_TYPE_ID,p_update_rec.ACTIVITY_TYPE_ID),
577 SOURCE_TASK_ID =decode(p_update_rec.SOURCE_TASK_ID,G_MISS_NUM,NULL,NULL,SOURCE_TASK_ID,p_update_rec.SOURCE_TASK_ID),
578 ACTIVATE_TIME =decode(p_update_rec.ACTIVATE_TIME,NULL,ACTIVATE_TIME,G_MISS_DATE,NULL,p_update_rec.ACTIVATE_TIME),
579 COMPLETE_TIME =decode(p_update_rec.COMPLETE_TIME,NULL,COMPLETE_TIME,G_MISS_DATE,NULL,p_update_rec.COMPLETE_TIME),
580 SUG_TO_SUB_CODE =decode(p_update_rec.SUG_TO_SUB_CODE,G_MISS_CHAR,NULL,NULL,SUG_TO_SUB_CODE,p_update_rec.SUG_TO_SUB_CODE),
581 SUG_TO_LOCATOR_ID =decode(p_update_rec.SUG_TO_LOCATOR_ID,G_MISS_NUM,NULL,NULL,SUG_TO_LOCATOR_ID,p_update_rec.SUG_TO_LOCATOR_ID),
582 FROM_SUBINVENTORY_CODE =decode(p_update_rec.FROM_SUBINVENTORY_CODE,G_MISS_CHAR,NULL,NULL,FROM_SUBINVENTORY_CODE,p_update_rec.FROM_SUBINVENTORY_CODE),
583 FROM_LOCATOR_ID =decode(p_update_rec.FROM_LOCATOR_ID,G_MISS_NUM,NULL,NULL,FROM_LOCATOR_ID,p_update_rec.FROM_LOCATOR_ID),
584 TO_SUBINVENTORY_CODE =decode(p_update_rec.TO_SUBINVENTORY_CODE,G_MISS_CHAR,NULL,NULL,TO_SUBINVENTORY_CODE,p_update_rec.TO_SUBINVENTORY_CODE),
585 TO_LOCATOR_ID =decode(p_update_rec.TO_LOCATOR_ID,G_MISS_NUM,NULL,NULL,TO_LOCATOR_ID,p_update_rec.TO_LOCATOR_ID),
586 IS_IN_INVENTORY =decode(p_update_rec.IS_IN_INVENTORY,G_MISS_CHAR,NULL,NULL,IS_IN_INVENTORY,p_update_rec.IS_IN_INVENTORY),
587 ORGANIZATION_ID =decode(p_update_rec.ORGANIZATION_ID,G_MISS_NUM,NULL,NULL,ORGANIZATION_ID,p_update_rec.ORGANIZATION_ID),
588 EMPLOYEE_ID =decode(p_update_rec.EMPLOYEE_ID,G_MISS_NUM,NULL,NULL,EMPLOYEE_ID,p_update_rec.EMPLOYEE_ID),
589 EQUIPMENT_ID =decode(p_update_rec.EQUIPMENT_ID,G_MISS_NUM,NULL,NULL,EQUIPMENT_ID,p_update_rec.EQUIPMENT_ID),
590 LAST_UPDATE_DATE =SYSDATE,
591 LAST_UPDATED_BY =FND_GLOBAL.USER_ID,
592 LAST_UPDATE_LOGIN =decode(p_update_rec.LAST_UPDATE_LOGIN,G_MISS_NUM,NULL,NULL,LAST_UPDATE_LOGIN,p_update_rec.LAST_UPDATE_LOGIN),
593 ATTRIBUTE_CATEGORY =decode(p_update_rec.ATTRIBUTE_CATEGORY,G_MISS_CHAR,NULL,NULL,ATTRIBUTE_CATEGORY,p_update_rec.ATTRIBUTE_CATEGORY),
594 ATTRIBUTE1 =decode(p_update_rec.ATTRIBUTE1,G_MISS_CHAR,NULL,NULL,ATTRIBUTE1,p_update_rec.ATTRIBUTE1),
595 ATTRIBUTE2 =decode(p_update_rec.ATTRIBUTE2,G_MISS_CHAR,NULL,NULL,ATTRIBUTE2,p_update_rec.ATTRIBUTE2),
596 ATTRIBUTE3 =decode(p_update_rec.ATTRIBUTE3,G_MISS_CHAR,NULL,NULL,ATTRIBUTE3,p_update_rec.ATTRIBUTE3),
597 ATTRIBUTE4 =decode(p_update_rec.ATTRIBUTE4,G_MISS_CHAR,NULL,NULL,ATTRIBUTE4,p_update_rec.ATTRIBUTE4),
598 ATTRIBUTE5 =decode(p_update_rec.ATTRIBUTE5,G_MISS_CHAR,NULL,NULL,ATTRIBUTE5,p_update_rec.ATTRIBUTE5),
599 ATTRIBUTE6 =decode(p_update_rec.ATTRIBUTE6,G_MISS_CHAR,NULL,NULL,ATTRIBUTE6,p_update_rec.ATTRIBUTE6),
600 ATTRIBUTE7 =decode(p_update_rec.ATTRIBUTE7,G_MISS_CHAR,NULL,NULL,ATTRIBUTE7,p_update_rec.ATTRIBUTE7),
601 ATTRIBUTE8 =decode(p_update_rec.ATTRIBUTE8,G_MISS_CHAR,NULL,NULL,ATTRIBUTE8,p_update_rec.ATTRIBUTE8),
602 ATTRIBUTE9 =decode(p_update_rec.ATTRIBUTE9,G_MISS_CHAR,NULL,NULL,ATTRIBUTE9,p_update_rec.ATTRIBUTE9),
603 ATTRIBUTE10 =decode(p_update_rec.ATTRIBUTE10,G_MISS_CHAR,NULL,NULL,ATTRIBUTE10,p_update_rec.ATTRIBUTE10),
604 ATTRIBUTE11 =decode(p_update_rec.ATTRIBUTE11,G_MISS_CHAR,NULL,NULL,ATTRIBUTE11,p_update_rec.ATTRIBUTE11),
605 ATTRIBUTE12 =decode(p_update_rec.ATTRIBUTE12,G_MISS_CHAR,NULL,NULL,ATTRIBUTE12,p_update_rec.ATTRIBUTE12),
606 ATTRIBUTE13 =decode(p_update_rec.ATTRIBUTE13,G_MISS_CHAR,NULL,NULL,ATTRIBUTE13,p_update_rec.ATTRIBUTE13),
607 ATTRIBUTE14 =decode(p_update_rec.ATTRIBUTE14,G_MISS_CHAR,NULL,NULL,ATTRIBUTE14,p_update_rec.ATTRIBUTE14),
608 ATTRIBUTE15 =decode(p_update_rec.ATTRIBUTE15,G_MISS_CHAR,NULL,NULL,ATTRIBUTE15,p_update_rec.ATTRIBUTE15)
609 WHERE operation_instance_id=p_update_rec.operation_instance_id;
610
611 IF SQL%NOTFOUND THEN
612
613 IF (l_debug=1) THEN
614 print_debug('Record not Found',l_module_name,1);
615 END IF;
616
617 RAISE fnd_api.g_exc_unexpected_error;
618
619 END IF;
620
621 EXCEPTION
622 WHEN fnd_api.g_exc_error THEN
623
624 x_return_status:=FND_API.G_RET_STS_ERROR;
625 IF (l_debug=1) THEN
626 print_debug('Error Obtained While Updating Operation instance',l_module_name,1);
627 END IF;
628 /*Message or error code to be populated for Operation PLan Instance Id Null*/
629
630 WHEN OTHERS THEN
631 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
632
633 IF (l_debug=1) THEN
634 print_debug('Unexpected Error Obtained While Updating Operation instance'||SQLERRM,l_module_name,1);
635 END IF;
636
637 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
638 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
639 END IF;
640
641 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
642 END;
643
644 /**
645 * <p>Procedure:<b>Archive_Plan_instance</b>
646 * This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.</p>
647 * @param p_op_plan_instance_id - Operation Plan Instance Id of the Plan that has to be archived.
648 * @param p_inventory_item_id Inventory Item Id of the Plan
649 * @param p_transaction_quantity Transaction Quantitity of the Plan
650 * @param p_transaction_uom Transaction UOM of the Plan
651 * @param x_return_status - Return Status
652 * @param x_msg_count - Returns Message Count
653 * @param x_msg_data - Returns Error Message
654 */
655 PROCEDURE archive_plan_instance(
656 p_op_plan_instance_id IN NUMBER
657 , x_return_status OUT NOCOPY VARCHAR2
658 , x_msg_count OUT NOCOPY NUMBER
659 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
660 )IS
661
662
663 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
664 l_module_name CONSTANT VARCHAR2(30):='Archive_Plan_Instance';
665 l_progress NUMBER;
666
667
668 BEGIN
669
670 x_return_status:=fnd_api.g_ret_sts_success;
671 l_progress:=10;
672
673 IF (l_debug=1) THEN
674 print_debug('Input Parameters:','Archive_plan_instance',3);
675 print_debug('p_op_plan_instance_id'||p_op_plan_instance_id,l_module_name,3);
676 END IF;
677
678 IF p_op_plan_instance_id IS NULL THEN
679 IF (l_debug=1) THEN
680 print_debug('Operation Plan Instance Id is null',l_module_name,1);
681 END IF;
682 RAISE fnd_api.g_exc_error;
683 END IF;
684
685 IF (l_debug=1) THEN
686 print_debug('Archiving Plan Instances',l_module_name,9);
687 END IF;
688 l_progress:=20;
689
690 INSERT INTO WMS_OP_PLAN_INSTANCES_HIST
691 (OP_PLAN_INSTANCE_ID,
692 OPERATION_PLAN_ID,
693 ACTIVITY_TYPE_ID,
694 PLAN_TYPE_ID,
695 STATUS,
696 PLAN_EXECUTION_START_DATE,
697 PLAN_EXECUTION_END_DATE,
698 ORGANIZATION_ID,
699 ORIG_SOURCE_SUB_CODE,
700 ORIG_SOURCE_LOC_ID,
701 ORIG_DEST_SUB_CODE,
702 ORIG_DEST_LOC_ID,
703 LAST_UPDATE_DATE,
704 LAST_UPDATED_BY,
705 CREATION_DATE,
706 CREATED_BY,
707 LAST_UPDATE_LOGIN,
708 ATTRIBUTE_CATEGORY,
709 ATTRIBUTE1,
710 ATTRIBUTE2,
711 ATTRIBUTE3,
712 ATTRIBUTE4,
713 ATTRIBUTE5,
714 ATTRIBUTE6,
715 ATTRIBUTE7,
716 ATTRIBUTE8,
717 ATTRIBUTE9,
718 ATTRIBUTE10,
719 ATTRIBUTE11,
720 ATTRIBUTE12,
721 ATTRIBUTE13,
722 ATTRIBUTE14,
723 ATTRIBUTE15 )
724 (SELECT
725 OP_PLAN_INSTANCE_ID,
726 OPERATION_PLAN_ID,
727 ACTIVITY_TYPE_ID,
728 PLAN_TYPE_ID,
729 STATUS,
730 PLAN_EXECUTION_START_DATE,
731 SYSDATE,
732 ORGANIZATION_ID,
733 ORIG_SOURCE_SUB_CODE,
734 ORIG_SOURCE_LOC_ID,
735 ORIG_DEST_SUB_CODE,
736 ORIG_DEST_LOC_ID,
737 SYSDATE,
738 FND_GLOBAL.USER_ID,
739 SYSDATE,
740 FND_GLOBAL.USER_ID,
741 LAST_UPDATE_LOGIN,
742 ATTRIBUTE_CATEGORY,
743 ATTRIBUTE1,
744 ATTRIBUTE2,
745 ATTRIBUTE3,
746 ATTRIBUTE4,
747 ATTRIBUTE5,
748 ATTRIBUTE6,
749 ATTRIBUTE7,
750 ATTRIBUTE8,
751 ATTRIBUTE9,
752 ATTRIBUTE10,
753 ATTRIBUTE11,
754 ATTRIBUTE12,
755 ATTRIBUTE13,
756 ATTRIBUTE14,
757 ATTRIBUTE15
758 FROM WMS_OP_PLAN_INSTANCES
759 WHERE op_plan_instance_id=p_op_plan_instance_id);
760
761 IF (l_debug=1) THEN
762 print_debug('Records inserted into WMS_OP_PLAN_INSTANCES_HISTORY '||SQL%ROWCOUNT,l_module_name,9);
763 END IF;
764 l_progress:=30;
765
766 INSERT INTO WMS_OP_OPERTN_INSTANCES_HIST
767 ( OPERATION_INSTANCE_ID
768 ,OPERATION_TYPE_ID
769 ,OPERATION_PLAN_DETAIL_ID
770 ,OP_PLAN_INSTANCE_ID
771 ,OPERATION_STATUS
772 ,OPERATION_SEQUENCE
773 ,ORGANIZATION_ID
774 ,ACTIVITY_TYPE_ID
775 ,SUG_TO_SUB_CODE
776 ,SUG_TO_LOCATOR_ID
777 ,FROM_SUBINVENTORY_CODE
778 ,FROM_LOCATOR_ID
779 ,TO_SUBINVENTORY_CODE
780 ,TO_LOCATOR_ID
781 ,SOURCE_TASK_ID
782 ,EMPLOYEE_ID
783 ,EQUIPMENT_ID
784 ,ACTIVATE_TIME
785 ,COMPLETE_TIME
786 ,IS_IN_INVENTORY
787 ,CREATED_BY
788 ,CREATION_DATE
789 ,LAST_UPDATED_BY
790 ,LAST_UPDATE_DATE
791 ,LAST_UPDATE_LOGIN
792 ,ATTRIBUTE_CATEGORY
793 ,ATTRIBUTE1
794 ,ATTRIBUTE2
795 ,ATTRIBUTE3
796 ,ATTRIBUTE4
797 ,ATTRIBUTE5
798 ,ATTRIBUTE6
799 ,ATTRIBUTE7
800 ,ATTRIBUTE8
801 ,ATTRIBUTE9
802 ,ATTRIBUTE10
803 ,ATTRIBUTE11
804 ,ATTRIBUTE12
805 ,ATTRIBUTE13
806 ,ATTRIBUTE14
807 ,ATTRIBUTE15)
808 (SELECT OPERATION_INSTANCE_ID
809 ,OPERATION_TYPE_ID
810 ,OPERATION_PLAN_DETAIL_ID
811 ,OP_PLAN_INSTANCE_ID
812 ,OPERATION_STATUS
813 ,OPERATION_SEQUENCE
814 ,ORGANIZATION_ID
815 ,ACTIVITY_TYPE_ID
816 ,SUG_TO_SUB_CODE
817 ,SUG_TO_LOCATOR_ID
818 ,FROM_SUBINVENTORY_CODE
819 ,FROM_LOCATOR_ID
820 ,TO_SUBINVENTORY_CODE
821 ,TO_LOCATOR_ID
822 ,SOURCE_TASK_ID
823 ,EMPLOYEE_ID
824 ,EQUIPMENT_ID
825 ,ACTIVATE_TIME
826 ,COMPLETE_TIME
827 ,IS_IN_INVENTORY
828 ,FND_GLOBAL.USER_ID
829 ,SYSDATE
830 ,FND_GLOBAL.USER_ID
831 ,SYSDATE
832 ,LAST_UPDATE_LOGIN
833 ,ATTRIBUTE_CATEGORY
834 ,ATTRIBUTE1
835 ,ATTRIBUTE2
836 ,ATTRIBUTE3
837 ,ATTRIBUTE4
838 ,ATTRIBUTE5
839 ,ATTRIBUTE6
840 ,ATTRIBUTE7
841 ,ATTRIBUTE8
842 ,ATTRIBUTE9
843 ,ATTRIBUTE10
844 ,ATTRIBUTE11
845 ,ATTRIBUTE12
846 ,ATTRIBUTE13
847 ,ATTRIBUTE14
848 ,ATTRIBUTE15
849 FROM WMS_OP_OPERATION_INSTANCES
850 WHERE OP_PLAN_INSTANCE_ID=p_op_plan_instance_id);
851
852 l_progress:=40;
853
854
855 IF (l_debug=1) THEN
856 print_debug('Records inserted into WMS_OP_OPERTN_INSTANCES_HIST '||SQL%ROWCOUNT,l_module_name,9);
857 print_debug('Deleting Operation Instances from WMS_OP_OPERATION_INSTANCES',l_module_name,9);
858 END IF;
859
860
861 DELETE FROM WMS_OP_OPERATION_INSTANCES
862 WHERE op_plan_instance_id=p_op_plan_instance_id;
863
864 l_progress:=50;
865
866 IF (l_debug=1) THEN
867 print_debug('Deleted records form WMS_OP_OPERATION_INSTANCES'||SQL%ROWCOUNT,l_module_name,9);
868 print_debug('Calling Delete_plan_instance to delete the Plan instance record',l_module_name,9);
869 END IF;
870
871 DELETE_PLAN_INSTANCE(p_op_plan_instance_id,x_return_status,x_msg_data,x_msg_count);
872
873 l_progress:=60;
874
875 IF (l_debug=1) THEN
876 print_debug('Return status from Delete_plan_instance'||x_return_status,l_module_name,6);
877 END IF;
878
879 IF x_return_status=fnd_api.G_RET_STS_ERROR THEN
880 RAISE fnd_api.g_exc_error;
881 ELSIF x_return_status=fnd_api.G_RET_STS_UNEXP_ERROR THEN
882 RAISE fnd_api.g_exc_unexpected_error;
883 END IF;
884
885 EXCEPTION
886 WHEN fnd_api.g_exc_error THEN
887 x_return_status:=FND_API.G_RET_STS_ERROR;
888 /*Message to be populated for Operation PLan Instance Id Null*/
889
890 WHEN OTHERS THEN
891 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
892 IF (l_debug=1) THEN
893 print_debug('Unexpected Error:'||SQLERRM||l_progress,l_module_name,1);
894 END IF;
895 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
896 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
897 END IF;
898 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
899 END;
900
901
902 /**
903 * <p>Procedure:<b>Complete_Plan_instance</b>
904 * This procedure inserts data into the table WMS_OP_PLAN_INSTANCES.</p>
905 * @param p_op_plan_instance_id - Operation Plan Instance Id of the Plan that has to be completed.
906 * @param x_return_status - Return Status
907 * @param x_msg_count - Returns Message Count
908 * @param x_msg_data - Returns Error Message
909 */
910 PROCEDURE complete_plan_instance(
911 p_op_plan_instance_id IN NUMBER
912 , x_return_status OUT NOCOPY VARCHAR2
913 , x_msg_count OUT NOCOPY NUMBER
914 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
915 ) IS
916
917 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
918 l_module_name CONSTANT VARCHAR2(30):='Complete_Plan_Instance';
919 l_progress NUMBER;
920
921 BEGIN
922
923 x_return_status:=FND_API.G_RET_STS_SUCCESS;
924 IF (l_debug=1) THEN
925 print_debug('Plan Instance Id'||p_op_plan_instance_id,l_module_name,3);
926 END IF;
927
928 IF p_op_plan_instance_id IS NULL THEN
929
930 IF (l_debug=1) THEN
931 print_debug('Plan Instance Id is null',l_module_name,1);
932 END IF;
933
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936
937 l_progress:=10;
938
939 IF (l_debug=1) THEN
940 print_debug('Updating the Plan status to completed',l_module_name,9);
941 END IF;
942
943 /*Updating the Plan Status to Completed*/
944 UPDATE WMS_OP_PLAN_INSTANCES
945 SET status=WMS_GLOBALS.G_OP_INS_STAT_COMPLETED
946 WHERE op_plan_instance_id=p_op_plan_instance_id;
947
948
949 IF SQL%NOTFOUND THEN
950 IF (l_debug=1) THEN
951 print_debug('Record Not Found While Updating the status','Complete_plan_instance',1);
952 END IF;
953 RAISE FND_API.G_EXC_ERROR;
954 END IF;
955 l_progress:=20;
956
957 IF (l_debug=1) THEN
958 print_debug('Calling Archive_op_plan_instance with the following parameters:','Complete_plan_instance',3);
959 print_debug('p_op_plan_instance_id:'||p_op_plan_instance_id,'Complete_plan_instance',3);
960 END IF;
961
962 ARCHIVE_PLAN_INSTANCE(p_op_plan_instance_id,
963 x_return_status,
964 x_msg_count,
965 x_msg_data);
966
967 l_progress:=30;
968
969 IF (l_debug=1) THEN
970 print_debug('Return Status from Archive_op_plan_instance'||x_return_status,'Complete_plan_instance',6);
971 END IF;
972
973 IF x_return_status=fnd_api.G_RET_STS_ERROR THEN
974 RAISE fnd_api.g_exc_error;
975
976 ELSIF x_return_status=fnd_api.G_RET_STS_UNEXP_ERROR THEN
977 RAISE fnd_api.g_exc_unexpected_error;
978 END IF;
979
980 EXCEPTION
981 WHEN fnd_api.g_exc_error THEN
982 x_return_status:=FND_API.G_RET_STS_ERROR;
983
984 IF (l_debug=1) THEN
985 print_debug('Error at '||l_progress,l_module_name,1);
986 END IF;
987 /*Message to be populated for Operation PLan Instance Id Null*/
988
989 WHEN OTHERS THEN
990 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
991
992 IF (l_debug=1) THEN
993 print_debug('Unexpected error '||SQLERRM||' at '||l_progress,l_module_name,1);
994 END IF;
995
996 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
997 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
998 END IF;
999 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1000
1001 END;
1002
1003
1004
1005 /**
1006 * <p>Procedure:<b>Insert_Dispatched_tasks</b>
1007 * This procedure inserts the task records into WMS_DISPATCHED_TASKS</p>
1008 * @param p_wdt_rec - WDT record that has to be inserted.
1009 * @param p_source_task_id - Transaction Temp Id of the WDT record.
1010 * @param x_return_status - Return Status
1011 * @param x_msg_count - Returns Message Count
1012 * @param x_msg_data - Returns Error Message
1013 */
1014 PROCEDURE insert_dispatched_tasks(
1015 p_wdt_rec IN wms_dispatched_tasks%ROWTYPE
1016 , p_source_task_id IN NUMBER
1017 , x_return_status OUT NOCOPY VARCHAR2
1018 , x_msg_count OUT NOCOPY NUMBER
1019 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1020 )IS
1021
1022 PRAGMA AUTONOMOUS_TRANSACTION;
1023
1024 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1025 l_module_name CONSTANT VARCHAR2(30):='Insert_Dispatched_tasks';
1026
1027 BEGIN
1028 IF (l_debug=1) THEN
1029 print_debug('p_wdt_rec.task_id'||p_wdt_rec.task_id,l_module_name,3);
1030 print_debug('p_source_task_id'||p_source_task_id,l_module_name,3);
1031 END IF;
1032
1033 x_return_status:=fnd_api.g_ret_sts_success;
1034
1035 INSERT INTO WMS_DISPATCHED_TASKS
1036 ( TASK_ID,
1037 TRANSACTION_TEMP_ID,
1038 ORGANIZATION_ID,
1039 USER_TASK_TYPE,
1040 PERSON_ID,
1041 EFFECTIVE_START_DATE,
1042 EFFECTIVE_END_DATE,
1043 EQUIPMENT_ID,
1044 EQUIPMENT_INSTANCE,
1045 PERSON_RESOURCE_ID,
1046 MACHINE_RESOURCE_ID,
1047 STATUS,
1048 DISPATCHED_TIME,
1049 LOADED_TIME,
1050 DROP_OFF_TIME,
1051 LAST_UPDATE_DATE,
1052 LAST_UPDATED_BY,
1053 CREATION_DATE,
1054 CREATED_BY,
1055 LAST_UPDATE_LOGIN,
1056 ATTRIBUTE_CATEGORY,
1057 ATTRIBUTE1,
1058 ATTRIBUTE2,
1059 ATTRIBUTE3,
1060 ATTRIBUTE4,
1061 ATTRIBUTE5,
1062 ATTRIBUTE6,
1063 ATTRIBUTE7,
1064 ATTRIBUTE8,
1065 ATTRIBUTE9,
1066 ATTRIBUTE10,
1067 ATTRIBUTE11,
1068 ATTRIBUTE12,
1069 ATTRIBUTE13,
1070 ATTRIBUTE14,
1071 ATTRIBUTE15,
1072 TASK_TYPE,
1073 PRIORITY,
1074 TASK_GROUP_ID,
1075 DEVICE_ID,
1076 DEVICE_INVOKED,
1077 DEVICE_REQUEST_ID,
1078 SUGGESTED_DEST_SUBINVENTORY,
1079 SUGGESTED_DEST_LOCATOR_ID,
1080 OPERATION_PLAN_ID,
1081 MOVE_ORDER_LINE_ID,
1082 TRANSFER_LPN_ID,
1083 OP_PLAN_INSTANCE_ID)
1084 VALUES
1085 ( decode(p_wdt_rec.TASK_ID,NULL,wms_dispatched_tasks_s.NEXTVAL,p_wdt_rec.TASK_ID),
1086 p_wdt_rec.TRANSACTION_TEMP_ID,
1087 p_wdt_rec.ORGANIZATION_ID,
1088 p_wdt_rec.USER_TASK_TYPE,
1089 p_wdt_rec.PERSON_ID,
1090 --Bug No:6350525
1091 -- p_wdt_rec.EFFECTIVE_START_DATE,
1092 --p_wdt_rec.EFFECTIVE_END_DATE,
1093 SYSDATE,
1094 SYSDATE,
1095 p_wdt_rec.EQUIPMENT_ID,
1096 p_wdt_rec.EQUIPMENT_INSTANCE,
1097 p_wdt_rec.PERSON_RESOURCE_ID,
1098 p_wdt_rec.MACHINE_RESOURCE_ID,
1099 p_wdt_rec.STATUS,
1100 p_wdt_rec.DISPATCHED_TIME,
1101 p_wdt_rec.LOADED_TIME,
1102 p_wdt_rec.DROP_OFF_TIME,
1103 SYSDATE,
1104 FND_GLOBAL.USER_ID,
1105 SYSDATE,
1106 FND_GLOBAL.USER_ID,
1107 p_wdt_rec.LAST_UPDATE_LOGIN,
1108 p_wdt_rec.ATTRIBUTE_CATEGORY,
1109 p_wdt_rec.ATTRIBUTE1,
1110 p_wdt_rec.ATTRIBUTE2,
1111 p_wdt_rec.ATTRIBUTE3,
1112 p_wdt_rec.ATTRIBUTE4,
1113 p_wdt_rec.ATTRIBUTE5,
1114 p_wdt_rec.ATTRIBUTE6,
1115 p_wdt_rec.ATTRIBUTE7,
1116 p_wdt_rec.ATTRIBUTE8,
1117 p_wdt_rec.ATTRIBUTE9,
1118 p_wdt_rec.ATTRIBUTE10,
1119 p_wdt_rec.ATTRIBUTE11,
1120 p_wdt_rec.ATTRIBUTE12,
1121 p_wdt_rec.ATTRIBUTE13,
1122 p_wdt_rec.ATTRIBUTE14,
1123 p_wdt_rec.ATTRIBUTE15,
1124 p_wdt_rec.TASK_TYPE,
1125 p_wdt_rec.PRIORITY,
1126 p_wdt_rec.TASK_GROUP_ID,
1127 p_wdt_rec.DEVICE_ID,
1128 p_wdt_rec.DEVICE_INVOKED,
1129 p_wdt_rec.DEVICE_REQUEST_ID,
1130 p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY,
1131 p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID,
1132 p_wdt_rec.OPERATION_PLAN_ID,
1133 p_wdt_rec.MOVE_ORDER_LINE_ID,
1134 p_wdt_rec.TRANSFER_LPN_ID,
1135 p_wdt_rec.OP_PLAN_INSTANCE_ID);
1136
1137 COMMIT;
1138
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1142 IF (l_debug=1) THEN
1143 print_debug('Unexpected Error:'||SQLERRM,l_module_name,1);
1144 END IF;
1145 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1146 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
1147 END IF;
1148 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1149
1150 END;
1151
1152 -- non autonomous transaction -- Added for bug # 14284768
1153
1154 PROCEDURE insert_dispatched_tasks_nauto(
1155 p_wdt_rec IN wms_dispatched_tasks%ROWTYPE
1156 , p_source_task_id IN NUMBER
1157 , x_return_status OUT NOCOPY VARCHAR2
1158 , x_msg_count OUT NOCOPY NUMBER
1159 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1160 )IS
1161
1162 --PRAGMA AUTONOMOUS_TRANSACTION;
1163
1164 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1165 l_module_name CONSTANT VARCHAR2(30):='Insert_Dispatched_tasks';
1166
1167 BEGIN
1168 IF (l_debug=1) THEN
1169 print_debug('p_wdt_rec.task_id'||p_wdt_rec.task_id,l_module_name,3);
1170 print_debug('p_source_task_id'||p_source_task_id,l_module_name,3);
1171 END IF;
1172
1173 x_return_status:=fnd_api.g_ret_sts_success;
1174
1175 INSERT INTO WMS_DISPATCHED_TASKS
1176 ( TASK_ID,
1177 TRANSACTION_TEMP_ID,
1178 ORGANIZATION_ID,
1179 USER_TASK_TYPE,
1180 PERSON_ID,
1181 EFFECTIVE_START_DATE,
1182 EFFECTIVE_END_DATE,
1183 EQUIPMENT_ID,
1184 EQUIPMENT_INSTANCE,
1185 PERSON_RESOURCE_ID,
1186 MACHINE_RESOURCE_ID,
1187 STATUS,
1188 DISPATCHED_TIME,
1189 LOADED_TIME,
1190 DROP_OFF_TIME,
1191 LAST_UPDATE_DATE,
1192 LAST_UPDATED_BY,
1193 CREATION_DATE,
1194 CREATED_BY,
1195 LAST_UPDATE_LOGIN,
1196 ATTRIBUTE_CATEGORY,
1197 ATTRIBUTE1,
1198 ATTRIBUTE2,
1199 ATTRIBUTE3,
1200 ATTRIBUTE4,
1201 ATTRIBUTE5,
1202 ATTRIBUTE6,
1203 ATTRIBUTE7,
1204 ATTRIBUTE8,
1205 ATTRIBUTE9,
1206 ATTRIBUTE10,
1207 ATTRIBUTE11,
1208 ATTRIBUTE12,
1209 ATTRIBUTE13,
1210 ATTRIBUTE14,
1211 ATTRIBUTE15,
1212 TASK_TYPE,
1213 PRIORITY,
1214 TASK_GROUP_ID,
1215 DEVICE_ID,
1216 DEVICE_INVOKED,
1217 DEVICE_REQUEST_ID,
1218 SUGGESTED_DEST_SUBINVENTORY,
1219 SUGGESTED_DEST_LOCATOR_ID,
1220 OPERATION_PLAN_ID,
1221 MOVE_ORDER_LINE_ID,
1222 TRANSFER_LPN_ID,
1223 OP_PLAN_INSTANCE_ID)
1224 VALUES
1225 ( decode(p_wdt_rec.TASK_ID,NULL,wms_dispatched_tasks_s.NEXTVAL,p_wdt_rec.TASK_ID),
1226 p_wdt_rec.TRANSACTION_TEMP_ID,
1227 p_wdt_rec.ORGANIZATION_ID,
1228 p_wdt_rec.USER_TASK_TYPE,
1229 p_wdt_rec.PERSON_ID,
1230 --Bug No 6162455
1231 -- p_wdt_rec.EFFECTIVE_START_DATE,
1232 --p_wdt_rec.EFFECTIVE_END_DATE,
1233 sysdate,
1234 sysdate,
1235 p_wdt_rec.EQUIPMENT_ID,
1236 p_wdt_rec.EQUIPMENT_INSTANCE,
1237 p_wdt_rec.PERSON_RESOURCE_ID,
1238 p_wdt_rec.MACHINE_RESOURCE_ID,
1239 p_wdt_rec.STATUS,
1240 p_wdt_rec.DISPATCHED_TIME,
1241 p_wdt_rec.LOADED_TIME,
1242 p_wdt_rec.DROP_OFF_TIME,
1243 SYSDATE,
1244 FND_GLOBAL.USER_ID,
1245 SYSDATE,
1246 FND_GLOBAL.USER_ID,
1247 p_wdt_rec.LAST_UPDATE_LOGIN,
1248 p_wdt_rec.ATTRIBUTE_CATEGORY,
1249 p_wdt_rec.ATTRIBUTE1,
1250 p_wdt_rec.ATTRIBUTE2,
1251 p_wdt_rec.ATTRIBUTE3,
1252 p_wdt_rec.ATTRIBUTE4,
1253 p_wdt_rec.ATTRIBUTE5,
1254 p_wdt_rec.ATTRIBUTE6,
1255 p_wdt_rec.ATTRIBUTE7,
1256 p_wdt_rec.ATTRIBUTE8,
1257 p_wdt_rec.ATTRIBUTE9,
1258 p_wdt_rec.ATTRIBUTE10,
1259 p_wdt_rec.ATTRIBUTE11,
1260 p_wdt_rec.ATTRIBUTE12,
1261 p_wdt_rec.ATTRIBUTE13,
1262 p_wdt_rec.ATTRIBUTE14,
1263 p_wdt_rec.ATTRIBUTE15,
1264 p_wdt_rec.TASK_TYPE,
1265 p_wdt_rec.PRIORITY,
1266 p_wdt_rec.TASK_GROUP_ID,
1267 p_wdt_rec.DEVICE_ID,
1268 p_wdt_rec.DEVICE_INVOKED,
1269 p_wdt_rec.DEVICE_REQUEST_ID,
1270 p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY,
1271 p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID,
1272 p_wdt_rec.OPERATION_PLAN_ID,
1273 p_wdt_rec.MOVE_ORDER_LINE_ID,
1274 p_wdt_rec.TRANSFER_LPN_ID,
1275 p_wdt_rec.OP_PLAN_INSTANCE_ID);
1276
1277 -- COMMIT;
1278
1279 EXCEPTION
1280 WHEN OTHERS THEN
1281 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1282 IF (l_debug=1) THEN
1283 print_debug('Unexpected Error:'||SQLERRM,l_module_name,1);
1284 END IF;
1285 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1286 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
1287 END IF;
1288 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1289
1290 END;
1291
1292 /**
1293 * <p>Procedure:<b>Delete_Dispatched_task</b>
1294 * This procedure deletes the task records into WMS_DISPATCHED_TASKS</p>
1295 * AND THERE IS AN AUTOMONOUS COMMIT IN THIS DELETE!
1296 * SO IT SHOULD ONLY BE USED TO DELETE WDT CREATED BY insert_dispatched_tasks.
1297 * @param p_source_task_id - Transaction Temp Id of the WDT record.
1298 * @param x_return_status - Return Status
1299 * @param x_msg_count - Returns Message Count
1300 * @param x_msg_data - Returns Error Message
1301 *
1302 * This API is necessary for instance when load errors out at online mode.
1303 * The calling procedure will simply rollback, but need to delete WDT
1304 * Autonomously inserted earlier.
1305 */
1306 PROCEDURE delete_dispatched_task
1307 (
1308 p_source_task_id IN NUMBER
1309 , p_wms_task_type IN NUMBER
1310 , x_return_status OUT NOCOPY VARCHAR2
1311 , x_msg_count OUT NOCOPY NUMBER
1312 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1313 )IS
1314 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1315 l_module_name CONSTANT VARCHAR2(30):='delete_dispatched_task';
1316 PRAGMA AUTONOMOUS_TRANSACTION;
1317 BEGIN
1318
1319 IF (l_debug=1) THEN
1320 print_debug('Entered. ',l_module_name,3);
1321 print_debug('p_source_task_id = '||p_source_task_id,l_module_name,3);
1322 print_debug('p_wms_task_type = '||p_wms_task_type,l_module_name,3);
1323 END IF;
1324
1325 x_return_status := fnd_api.g_ret_sts_success;
1326
1327 DELETE wms_dispatched_tasks
1328 WHERE transaction_temp_id = p_source_task_id
1329 AND task_type= p_wms_task_type;
1330
1331 COMMIT;
1332
1333 IF (l_debug=1) THEN
1334 print_debug('Completed. ',l_module_name,3);
1335
1336 END IF;
1337
1338
1339 END delete_dispatched_task;
1340
1341
1342
1343 /**
1344 * <p>Procedure:<b>Update_Dispatched_tasks</b>
1345 * This procedure updates the task records in WMS_DISPATCHED_TASKS</p>
1346 * @param p_wdt_rec - WDT record that has to be updated.
1347 * @param x_return_status - Return Status
1348 * @param x_msg_count - Returns Message Count
1349 * @param x_msg_data - Returns Error Message
1350 */
1351 PROCEDURE update_dipatched_tasks(
1352 p_wdt_rec IN wms_dispatched_tasks%ROWTYPE
1353 , x_return_status OUT NOCOPY VARCHAR2
1354 , x_msg_count OUT NOCOPY NUMBER
1355 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1356 )IS
1357
1358 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1359 l_module_name CONSTANT VARCHAR2(30):='Update_Dispatched_Tasks';
1360
1361 BEGIN
1362 IF (l_debug=1) THEN
1363 print_debug('p_wdt_rec.task_id'||p_wdt_rec.task_id,l_module_name,3);
1364 END IF;
1365
1366 x_return_status:=fnd_api.g_ret_sts_success;
1367
1368 IF p_wdt_rec.task_id IS NULL THEN
1369 IF (l_debug=1) THEN
1370 print_debug('Task Id is null',l_module_name,1);
1371 END IF;
1372 RAISE FND_API.G_EXC_ERROR;
1373 END IF;
1374
1375 --Updating the WDT Record
1376 IF (l_debug=1) THEN
1377 print_debug('Updating WMS_Dispatched_tasks',l_module_name,9);
1378 END IF;
1379
1380 UPDATE WMS_DISPATCHED_TASKS
1381 SET
1382 TASK_ID =decode(p_wdt_rec.TASK_ID,G_MISS_NUM,NULL,NULL,TASK_ID,p_wdt_rec.TASK_ID),
1383 TRANSACTION_TEMP_ID =decode(p_wdt_rec.TRANSACTION_TEMP_ID,G_MISS_NUM,NULL,NULL,TRANSACTION_TEMP_ID,p_wdt_rec.TRANSACTION_TEMP_ID),
1384 ORGANIZATION_ID =decode(p_wdt_rec.ORGANIZATION_ID,G_MISS_NUM,NULL,NULL,ORGANIZATION_ID,p_wdt_rec.ORGANIZATION_ID),
1385 USER_TASK_TYPE =decode(p_wdt_rec.USER_TASK_TYPE,G_MISS_NUM,NULL,NULL,USER_TASK_TYPE,p_wdt_rec.USER_TASK_TYPE),
1386 PERSON_ID =decode(p_wdt_rec.PERSON_ID,G_MISS_NUM,NULL,NULL,PERSON_ID,p_wdt_rec.PERSON_ID),
1387 EFFECTIVE_START_DATE =decode(p_wdt_rec.EFFECTIVE_START_DATE,NULL,EFFECTIVE_START_DATE,G_MISS_DATE,NULL,p_wdt_rec.EFFECTIVE_START_DATE),
1388 EFFECTIVE_END_DATE =decode(p_wdt_rec.EFFECTIVE_END_DATE,NULL,EFFECTIVE_END_DATE,G_MISS_DATE,NULL,p_wdt_rec.EFFECTIVE_END_DATE),
1389 EQUIPMENT_ID =decode(p_wdt_rec.EQUIPMENT_ID,G_MISS_NUM,NULL,NULL,EQUIPMENT_ID,p_wdt_rec.EQUIPMENT_ID),
1390 EQUIPMENT_INSTANCE =decode(p_wdt_rec.EQUIPMENT_INSTANCE,G_MISS_CHAR,NULL,NULL,EQUIPMENT_INSTANCE,p_wdt_rec.EQUIPMENT_INSTANCE),
1391 PERSON_RESOURCE_ID =decode(p_wdt_rec.PERSON_RESOURCE_ID,G_MISS_NUM,NULL,NULL,PERSON_RESOURCE_ID,p_wdt_rec.PERSON_RESOURCE_ID),
1392 MACHINE_RESOURCE_ID =decode(p_wdt_rec.MACHINE_RESOURCE_ID,G_MISS_NUM,NULL,NULL,MACHINE_RESOURCE_ID,p_wdt_rec.MACHINE_RESOURCE_ID),
1393 STATUS =decode(p_wdt_rec.STATUS,G_MISS_NUM,NULL,NULL,STATUS,p_wdt_rec.STATUS),
1394 DISPATCHED_TIME =decode(p_wdt_rec.DISPATCHED_TIME,NULL,DISPATCHED_TIME,G_MISS_DATE,NULL,p_wdt_rec.DISPATCHED_TIME),
1395 LOADED_TIME =decode(p_wdt_rec.LOADED_TIME,NULL,LOADED_TIME,G_MISS_DATE,NULL,p_wdt_rec.LOADED_TIME),
1396 DROP_OFF_TIME =decode(p_wdt_rec.DROP_OFF_TIME,NULL,DROP_OFF_TIME,G_MISS_DATE,NULL,p_wdt_rec.DROP_OFF_TIME),
1397 LAST_UPDATE_DATE =SYSDATE,
1398 LAST_UPDATED_BY =FND_GLOBAL.USER_ID,
1399 LAST_UPDATE_LOGIN =decode(p_wdt_rec.LAST_UPDATE_LOGIN,G_MISS_NUM,NULL,NULL,LAST_UPDATE_LOGIN,p_wdt_rec.LAST_UPDATE_LOGIN),
1400 ATTRIBUTE_CATEGORY =decode(p_wdt_rec.ATTRIBUTE_CATEGORY,G_MISS_CHAR,NULL,NULL,ATTRIBUTE_CATEGORY,p_wdt_rec.ATTRIBUTE_CATEGORY),
1401 ATTRIBUTE1 =decode(p_wdt_rec.ATTRIBUTE1,G_MISS_CHAR,NULL,NULL,ATTRIBUTE1,p_wdt_rec.ATTRIBUTE1),
1402 ATTRIBUTE2 =decode(p_wdt_rec.ATTRIBUTE2,G_MISS_CHAR,NULL,NULL,ATTRIBUTE2,p_wdt_rec.ATTRIBUTE2),
1403 ATTRIBUTE3 =decode(p_wdt_rec.ATTRIBUTE3,G_MISS_CHAR,NULL,NULL,ATTRIBUTE3,p_wdt_rec.ATTRIBUTE3),
1404 ATTRIBUTE4 =decode(p_wdt_rec.ATTRIBUTE4,G_MISS_CHAR,NULL,NULL,ATTRIBUTE4,p_wdt_rec.ATTRIBUTE4),
1405 ATTRIBUTE5 =decode(p_wdt_rec.ATTRIBUTE5,G_MISS_CHAR,NULL,NULL,ATTRIBUTE5,p_wdt_rec.ATTRIBUTE5),
1406 ATTRIBUTE6 =decode(p_wdt_rec.ATTRIBUTE6,G_MISS_CHAR,NULL,NULL,ATTRIBUTE6,p_wdt_rec.ATTRIBUTE6),
1407 ATTRIBUTE7 =decode(p_wdt_rec.ATTRIBUTE7,G_MISS_CHAR,NULL,NULL,ATTRIBUTE7,p_wdt_rec.ATTRIBUTE7),
1408 ATTRIBUTE8 =decode(p_wdt_rec.ATTRIBUTE8,G_MISS_CHAR,NULL,NULL,ATTRIBUTE8,p_wdt_rec.ATTRIBUTE8),
1409 ATTRIBUTE9 =decode(p_wdt_rec.ATTRIBUTE9,G_MISS_CHAR,NULL,NULL,ATTRIBUTE9,p_wdt_rec.ATTRIBUTE9),
1410 ATTRIBUTE10 =decode(p_wdt_rec.ATTRIBUTE10,G_MISS_CHAR,NULL,NULL,ATTRIBUTE10,p_wdt_rec.ATTRIBUTE10),
1411 ATTRIBUTE11 =decode(p_wdt_rec.ATTRIBUTE11,G_MISS_CHAR,NULL,NULL,ATTRIBUTE11,p_wdt_rec.ATTRIBUTE11),
1412 ATTRIBUTE12 =decode(p_wdt_rec.ATTRIBUTE12,G_MISS_CHAR,NULL,NULL,ATTRIBUTE12,p_wdt_rec.ATTRIBUTE12),
1413 ATTRIBUTE13 =decode(p_wdt_rec.ATTRIBUTE13,G_MISS_CHAR,NULL,NULL,ATTRIBUTE13,p_wdt_rec.ATTRIBUTE13),
1414 ATTRIBUTE14 =decode(p_wdt_rec.ATTRIBUTE14,G_MISS_CHAR,NULL,NULL,ATTRIBUTE14,p_wdt_rec.ATTRIBUTE14),
1415 ATTRIBUTE15 =decode(p_wdt_rec.ATTRIBUTE15,G_MISS_CHAR,NULL,NULL,ATTRIBUTE15,p_wdt_rec.ATTRIBUTE15),
1416 TASK_TYPE =decode(p_wdt_rec.TASK_TYPE,G_MISS_NUM,NULL,NULL,TASK_TYPE,p_wdt_rec.TASK_TYPE),
1417 PRIORITY =decode(p_wdt_rec.PRIORITY,G_MISS_NUM,NULL,NULL,PRIORITY,p_wdt_rec.PRIORITY),
1418 TASK_GROUP_ID =decode(p_wdt_rec.TASK_GROUP_ID,G_MISS_NUM,NULL,NULL,TASK_GROUP_ID,p_wdt_rec.TASK_GROUP_ID),
1419 DEVICE_ID =decode(p_wdt_rec.DEVICE_ID,G_MISS_NUM,NULL,NULL,DEVICE_ID,p_wdt_rec.DEVICE_ID),
1420 DEVICE_INVOKED =decode(p_wdt_rec.DEVICE_INVOKED,G_MISS_CHAR,NULL,NULL,DEVICE_INVOKED,p_wdt_rec.DEVICE_INVOKED),
1421 DEVICE_REQUEST_ID =decode(p_wdt_rec.DEVICE_REQUEST_ID,G_MISS_NUM,NULL,NULL,DEVICE_REQUEST_ID,p_wdt_rec.DEVICE_REQUEST_ID),
1422 SUGGESTED_DEST_SUBINVENTORY=decode(p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY,G_MISS_CHAR,NULL,NULL,SUGGESTED_DEST_SUBINVENTORY,p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY),
1423 SUGGESTED_DEST_LOCATOR_ID =decode(p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID,G_MISS_NUM,NULL,NULL,SUGGESTED_DEST_LOCATOR_ID,p_wdt_rec.SUGGESTED_DEST_LOCATOR_ID),
1424 OPERATION_PLAN_ID =decode(p_wdt_rec.OPERATION_PLAN_ID,G_MISS_NUM,NULL,NULL,OPERATION_PLAN_ID,p_wdt_rec.OPERATION_PLAN_ID),
1425 MOVE_ORDER_LINE_ID =decode(p_wdt_rec.MOVE_ORDER_LINE_ID,G_MISS_NUM,NULL,NULL,MOVE_ORDER_LINE_ID,p_wdt_rec.MOVE_ORDER_LINE_ID),
1426 TRANSFER_LPN_ID =decode(p_wdt_rec.TRANSFER_LPN_ID,G_MISS_NUM,NULL,NULL,TRANSFER_LPN_ID,p_wdt_rec.transfer_lpn_id),
1427 OP_PLAN_INSTANCE_ID =decode(p_wdt_rec.OP_PLAN_INSTANCE_ID,G_MISS_NUM,NULL,NULL,OP_PLAN_INSTANCE_ID,p_wdt_rec.OP_PLAN_INSTANCE_ID)
1428 WHERE task_id=p_wdt_rec.task_id;
1429
1430 EXCEPTION
1431 WHEN FND_API.g_EXC_ERROR THEN
1432 x_return_status:=FND_API.G_RET_STS_ERROR;
1433 IF (l_debug=1) THEN
1434 print_debug('Error While Updating task',l_module_name,1);
1435 END IF;
1436
1437 WHEN OTHERS THEN
1438 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1439 IF (l_debug=1) THEN
1440 print_debug('Unexpected Error:'||SQLERRM,l_module_name,1);
1441 END IF;
1442 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1443 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
1444 END IF;
1445 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1446 END;
1447
1448
1449
1450 /**
1451 * <p>Procedure:<b>Archive_Dispatched_tasks</b>
1452 * This procedure archives the task records into WMS_DISPATCHED_TASKS_HISTORY</p>
1453 * @param p_task_id - Task Id of WMS_DISPATCHED_TASKS
1454 * @param p_source_task_id - Document Id for the Parent document record
1455 * @param p_activity_type_id - Activity Type Id
1456 * @param p_op_plan_instance_id - Operation Plan Id for the Parent Record
1457 * @param p_op_plan_status - Operation plan status for the Parent record
1458 */
1459
1460 PROCEDURE archive_dispatched_tasks(
1461 x_return_status OUT NOCOPY VARCHAR2
1462 , x_msg_count OUT NOCOPY NUMBER
1463 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1464 , p_task_id IN NUMBER
1465 , p_source_task_id IN NUMBER
1466 , p_activity_type_id IN NUMBER
1467 , p_op_plan_instance_id IN NUMBER
1468 , p_op_plan_status IN NUMBER
1469 ) IS
1470
1471 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1472 l_module_name CONSTANT VARCHAR2(30):= 'Archive_Dispatched_tasks';
1473 l_progress NUMBER;
1474 l_last_operation_dest_sub VARCHAR2(30);
1475 l_last_operation_dest_loc_id NUMBER;
1476 l_last_drop_off_time DATE;
1477 l_current_txn_id NUMBER; --5523365
1478 l_parent_txn_id NUMBER; --5523365
1479
1480 CURSOR c_last_task IS
1481 SELECT dest_subinventory_code, dest_locator_id, drop_off_time, source_document_id
1482 FROM wms_dispatched_tasks_history
1483 WHERE parent_transaction_id = p_source_task_id
1484 ORDER BY task_id DESC;
1485
1486 BEGIN
1487
1488 IF (l_debug=1) THEN
1489 print_debug('Archiving Dispatched tasks ',l_module_name,3);
1490 print_debug('p_task_id ==> '||p_task_id,l_module_name,3);
1491 print_debug('p_source_task_id ==> '||p_source_task_id,l_module_name,3);
1492 print_debug('p_activity_id ==> '||p_activity_type_id,l_module_name,3);
1493 END IF;
1494
1495 x_return_status :=FND_API.g_ret_sts_success;
1496
1497 l_progress:=10;
1498
1499 IF (p_task_id IS NULL AND p_source_task_id IS NULL AND p_activity_type_id IS NULL) THEN
1500 IF (l_debug=1) THEN
1501 print_debug('Invalid Input',l_module_name,1);
1502 END IF;
1503 RAISE FND_API.G_EXC_ERROR;
1504 END IF;
1505
1506 l_progress:=20;
1507
1508 IF (p_task_id IS NOT NULL) THEN /*Archiving the Child record*/
1509 IF (l_debug=1) THEN
1510 print_debug('Archiving the Child task with WDT task Id'||p_task_id,l_module_name,9);
1511 END IF;
1512
1513 l_progress:=30;
1514
1515
1516
1517 INSERT INTO WMS_DISPATCHED_TASKS_HISTORY
1518 ( TASK_ID
1519 ,TRANSACTION_ID
1520 ,ORGANIZATION_ID
1521 ,USER_TASK_TYPE
1522 ,PERSON_ID
1523 ,EFFECTIVE_START_DATE
1524 ,EFFECTIVE_END_DATE
1525 ,EQUIPMENT_ID
1526 ,EQUIPMENT_INSTANCE
1527 ,PERSON_RESOURCE_ID
1528 ,MACHINE_RESOURCE_ID
1529 ,STATUS
1530 ,DISPATCHED_TIME
1531 ,LOADED_TIME
1532 ,DROP_OFF_TIME
1533 ,LAST_UPDATE_DATE
1534 ,LAST_UPDATED_BY
1535 ,CREATION_DATE
1536 ,CREATED_BY
1537 ,LAST_UPDATE_LOGIN
1538 ,ATTRIBUTE_CATEGORY
1539 ,ATTRIBUTE1
1540 ,ATTRIBUTE2
1541 ,ATTRIBUTE3
1542 ,ATTRIBUTE4
1543 ,ATTRIBUTE5
1544 ,ATTRIBUTE6
1545 ,ATTRIBUTE7
1546 ,ATTRIBUTE8
1547 ,ATTRIBUTE9
1548 ,ATTRIBUTE10
1549 ,ATTRIBUTE11
1550 ,ATTRIBUTE12
1551 ,ATTRIBUTE13
1552 ,ATTRIBUTE14
1553 ,ATTRIBUTE15
1554 ,TASK_TYPE
1555 ,PRIORITY
1556 ,TASK_GROUP_ID
1557 ,SUGGESTED_DEST_SUBINVENTORY
1558 ,SUGGESTED_DEST_LOCATOR_ID
1559 ,OPERATION_PLAN_ID
1560 ,MOVE_ORDER_LINE_ID
1561 ,TRANSFER_LPN_ID
1562 ,TRANSACTION_BATCH_ID
1563 ,TRANSACTION_BATCH_SEQ
1564 ,INVENTORY_ITEM_ID
1565 ,REVISION
1566 ,TRANSACTION_QUANTITY
1567 ,TRANSACTION_UOM_CODE
1568 ,SOURCE_SUBINVENTORY_CODE
1569 ,SOURCE_LOCATOR_ID
1570 ,DEST_SUBINVENTORY_CODE
1571 ,DEST_LOCATOR_ID
1572 ,LPN_ID
1573 ,CONTENT_LPN_ID
1574 ,IS_PARENT
1575 ,PARENT_TRANSACTION_ID
1576 ,TRANSFER_ORGANIZATION_ID
1577 ,SOURCE_DOCUMENT_ID
1578 ,OP_PLAN_INSTANCE_ID
1579 ,TRANSACTION_SOURCE_TYPE_ID
1580 ,TRANSACTION_TYPE_ID
1581 ,transaction_action_id
1582 ,transaction_temp_id
1583 ,SECONDARY_TRANSACTION_QUANTITY --13431255
1584 ,SECONDARY_TRANSACTION_UOM_CODE --13431255
1585 )
1586 (SELECT
1587 WDT.TASK_ID
1588 ,WDT.TRANSACTION_TEMP_ID
1589 ,WDT.ORGANIZATION_ID
1590 ,WDT.USER_TASK_TYPE
1591 ,WDT.PERSON_ID
1592 ,WDT.EFFECTIVE_START_DATE
1593 ,WDT.EFFECTIVE_END_DATE
1594 ,WDT.EQUIPMENT_ID
1595 ,WDT.EQUIPMENT_INSTANCE
1596 ,WDT.PERSON_RESOURCE_ID
1597 ,WDT.MACHINE_RESOURCE_ID
1598 ,6
1599 ,WDT.DISPATCHED_TIME
1600 ,WDT.LOADED_TIME
1601 ,WDT.DROP_OFF_TIME
1602 ,SYSDATE
1603 ,FND_GLOBAL.USER_ID
1604 ,WDT.CREATION_DATE
1605 ,WDT.CREATED_BY
1606 ,WDT.LAST_UPDATE_LOGIN
1607 ,WDT.ATTRIBUTE_CATEGORY
1608 ,WDT.ATTRIBUTE1
1609 ,WDT.ATTRIBUTE2
1610 ,WDT.ATTRIBUTE3
1611 ,WDT.ATTRIBUTE4
1612 ,WDT.ATTRIBUTE5
1613 ,WDT.ATTRIBUTE6
1614 ,WDT.ATTRIBUTE7
1615 ,WDT.ATTRIBUTE8
1616 ,WDT.ATTRIBUTE9
1617 ,WDT.ATTRIBUTE10
1618 ,WDT.ATTRIBUTE11
1619 ,WDT.ATTRIBUTE12
1620 ,WDT.ATTRIBUTE13
1621 ,WDT.ATTRIBUTE14
1622 ,WDT.ATTRIBUTE15
1623 ,WDT.TASK_TYPE
1624 ,WDT.PRIORITY
1625 ,WDT.TASK_GROUP_ID
1626 ,Nvl(WDT.suggested_dest_subinventory,pmmtt.subinventory_code)
1627 ,Nvl(WDT.suggested_dest_locator_id,pmmtt.locator_id)
1628 ,MMTT.OPERATION_PLAN_ID
1629 ,MMTT.MOVE_ORDER_LINE_ID
1630 ,MMTT.TRANSFER_LPN_ID
1631 ,MMTT.TRANSACTION_BATCH_ID
1632 ,MMTT.TRANSACTION_BATCH_SEQ
1633 ,MMTT.INVENTORY_ITEM_ID
1634 ,MMTT.REVISION
1635 ,MMTT.TRANSACTION_QUANTITY
1636 ,MMTT.TRANSACTION_UOM
1637 ,decode(MMTT.TRANSFER_SUBINVENTORY,NULL,NULL,MMTT.SUBINVENTORY_CODE)
1638 ,decode(MMTT.TRANSFER_TO_LOCATION,NULL,NULL,MMTT.LOCATOR_ID)
1639 ,nvl(MMTT.TRANSFER_SUBINVENTORY,MMTT.SUBINVENTORY_CODE)
1640 ,nvl(MMTT.TRANSFER_TO_LOCATION,MMTT.LOCATOR_ID)
1641 ,MMTT.LPN_ID
1642 ,MMTT.CONTENT_LPN_ID
1643 ,'N'
1644 ,MMTT.PARENT_LINE_ID
1645 ,MMTT.TRANSFER_ORGANIZATION
1646 ,NVL(MMTT.rcv_transaction_id,MMTT.transaction_header_id)
1647 ,WDT.OP_PLAN_INSTANCE_ID
1648 ,mmtt.transaction_source_type_id
1649 ,mmtt.transaction_type_id
1650 ,mmtt.transaction_action_id
1651 ,mmtt.transaction_temp_id
1652 ,mmtt.SECONDARY_TRANSACTION_QUANTITY -- 13431255
1653 ,mmtt.SECONDARY_UOM_CODE -- 13431255
1654 FROM WMS_DISPATCHED_TASKS wdt
1655 , MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1656 , mtl_material_transactions_temp pmmtt
1657 WHERE WDT.TASK_ID=p_task_id
1658 AND WDT.transaction_temp_id=MMTT.transaction_temp_id
1659 AND mmtt.parent_line_id = pmmtt.transaction_temp_id (+)
1660 );
1661
1662 l_progress:=40;
1663
1664 /*Archive Tasks,now delete records from WDT*/
1665 IF (l_debug=1) THEN
1666 print_debug('Archived WDT,now deleting the WDT record',l_module_name,9);
1667 END IF;
1668
1669 DELETE FROM wms_dispatched_tasks
1670 WHERE task_id = p_task_id;
1671
1672 l_progress:=50;
1673
1674
1675 ELSE
1676
1677 /*Archiving the parent Record*/
1678
1679 -- get the destination sub/loc for the last task
1680 -- which might be different to those of the parent mmtt
1681 OPEN c_last_task;
1682 FETCH c_last_task INTO
1683 l_last_operation_dest_sub,
1684 l_last_operation_dest_loc_id,
1685 l_last_drop_off_time,
1686 l_current_txn_id;
1687 CLOSE c_last_task;
1688
1689 --In R12, to fix bug 5523365, we need to get the
1690 --parent_transaction_id from RT as we no longer stamp it on
1691 --MOL/MMTT. This was a change done for MOL consolidation project.
1692 --This will be stamped on WDTH for parent if it is a putaway task.
1693 IF l_current_txn_id IS NOT NULL THEN
1694 BEGIN
1695 SELECT parent_transaction_id
1696 INTO l_parent_txn_id
1697 FROM rcv_transactions
1698 WHERE transaction_id = l_current_txn_id;
1699 EXCEPTION
1700 WHEN OTHERS THEN
1701 l_parent_txn_id := NULL;
1702 END;
1703 END IF;
1704
1705
1706 IF (l_debug=1) THEN
1707 print_debug('l_last_operation_dest_sub = '||l_last_operation_dest_sub,l_module_name,9);
1708 print_debug('l_last_operation_dest_loc_id = '||l_last_operation_dest_loc_id,l_module_name,9);
1709 print_debug('l_current_txn_id = '||l_current_txn_id,l_module_name,9);
1710 print_debug('l_parent_txn_id = '||l_parent_txn_id,l_module_name,9);
1711 print_debug('Inserting Records for Parent with source_task_id'||p_source_task_id,l_module_name,9);
1712 END IF;
1713
1714 INSERT INTO WMS_DISPATCHED_TASKS_HISTORY
1715 ( TASK_ID
1716 ,TRANSACTION_ID
1717 ,ORGANIZATION_ID
1718 ,USER_TASK_TYPE
1719 ,PERSON_ID
1720 ,EFFECTIVE_START_DATE
1721 ,EFFECTIVE_END_DATE
1722 ,EQUIPMENT_ID
1723 ,EQUIPMENT_INSTANCE
1724 ,PERSON_RESOURCE_ID
1725 ,MACHINE_RESOURCE_ID
1726 ,STATUS
1727 ,DISPATCHED_TIME
1728 ,LOADED_TIME
1729 ,DROP_OFF_TIME
1730 ,LAST_UPDATE_DATE
1731 ,LAST_UPDATED_BY
1732 ,CREATION_DATE
1733 ,CREATED_BY
1734 ,LAST_UPDATE_LOGIN
1735 ,ATTRIBUTE_CATEGORY
1736 ,ATTRIBUTE1
1737 ,ATTRIBUTE2
1738 ,ATTRIBUTE3
1739 ,ATTRIBUTE4
1740 ,ATTRIBUTE5
1741 ,ATTRIBUTE6
1742 ,ATTRIBUTE7
1743 ,ATTRIBUTE8
1744 ,ATTRIBUTE9
1745 ,ATTRIBUTE10
1746 ,ATTRIBUTE11
1747 ,ATTRIBUTE12
1748 ,ATTRIBUTE13
1749 ,ATTRIBUTE14
1750 ,ATTRIBUTE15
1751 ,TASK_TYPE
1752 ,PRIORITY
1753 ,TASK_GROUP_ID
1754 ,SUGGESTED_DEST_SUBINVENTORY
1755 ,SUGGESTED_DEST_LOCATOR_ID
1756 ,OPERATION_PLAN_ID
1757 ,MOVE_ORDER_LINE_ID
1758 ,TRANSFER_LPN_ID
1759 ,TRANSACTION_BATCH_ID
1760 ,TRANSACTION_BATCH_SEQ
1761 ,INVENTORY_ITEM_ID
1762 ,REVISION
1763 ,TRANSACTION_QUANTITY
1764 ,TRANSACTION_UOM_CODE
1765 ,SOURCE_SUBINVENTORY_CODE
1766 ,SOURCE_LOCATOR_ID
1767 ,DEST_SUBINVENTORY_CODE
1768 ,DEST_LOCATOR_ID
1769 ,LPN_ID
1770 ,CONTENT_LPN_ID
1771 ,IS_PARENT
1772 ,PARENT_TRANSACTION_ID
1773 ,TRANSFER_ORGANIZATION_ID
1774 ,SOURCE_DOCUMENT_ID
1775 ,op_plan_instance_id
1776 ,TRANSACTION_SOURCE_TYPE_ID
1777 ,TRANSACTION_TYPE_ID
1778 ,transaction_action_id
1779 ,transaction_temp_id
1780 ,SECONDARY_TRANSACTION_QUANTITY --13431255
1781 ,SECONDARY_TRANSACTION_UOM_CODE --13431255
1782 )
1783 ( SELECT
1784 wms_dispatched_tasks_s.NEXTVAL
1785 ,MMTT.TRANSACTION_TEMP_ID
1786 ,MMTT.ORGANIZATION_ID
1787 ,-1
1788 ,-1
1789 ,SYSDATE
1790 ,SYSDATE
1791 ,NULL
1792 ,NULL
1793 ,NULL
1794 ,NULL
1795 ,decode(p_op_plan_status,3,6,4,12,5,11,6)
1796 ,NULL
1797 ,NULL
1798 ,l_last_drop_off_time
1799 ,SYSDATE
1800 ,FND_GLOBAL.USER_ID
1801 ,SYSDATE
1802 ,FND_GLOBAL.USER_ID
1803 ,NULL
1804 ,NULL
1805 ,NULL
1806 ,NULL
1807 ,NULL
1808 ,NULL
1809 ,NULL
1810 ,NULL
1811 ,NULL
1812 ,NULL
1813 ,NULL
1814 ,NULL
1815 ,NULL
1816 ,NULL
1817 ,NULL
1818 ,NULL
1819 ,NULL
1820 ,MMTT.WMS_TASK_TYPE
1821 ,NULL
1822 ,NULL
1823 ,nvl(MMTT.TRANSFER_SUBINVENTORY,MMTT.SUBINVENTORY_CODE)
1824 ,nvl(MMTT.TRANSFER_TO_LOCATION,MMTT.LOCATOR_ID)
1825 ,MMTT.OPERATION_PLAN_ID
1826 ,MMTT.MOVE_ORDER_LINE_ID
1827 ,MMTT.TRANSFER_LPN_ID
1828 ,MMTT.TRANSACTION_BATCH_ID
1829 ,MMTT.TRANSACTION_BATCH_SEQ
1830 ,MMTT.INVENTORY_ITEM_ID
1831 ,MMTT.REVISION
1832 ,MMTT.TRANSACTION_QUANTITY
1833 ,MMTT.TRANSACTION_UOM
1834 ,decode(MMTT.TRANSFER_SUBINVENTORY,NULL,NULL,MMTT.SUBINVENTORY_CODE)
1835 ,decode(MMTT.TRANSFER_TO_LOCATION,NULL,NULL,MMTT.LOCATOR_ID)
1836 ,l_last_operation_dest_sub
1837 ,l_last_operation_dest_loc_id
1838 ,MMTT.LPN_ID
1839 ,MMTT.CONTENT_LPN_ID
1840 ,'Y'
1841 ,NULL
1842 ,MMTT.TRANSFER_ORGANIZATION
1843 ,Decode(mmtt.wms_task_type,2,Nvl(mmtt.transaction_source_id,l_parent_txn_id),mmtt.transaction_source_id)
1844 ,p_op_plan_instance_id
1845 ,mmtt.transaction_source_type_id
1846 ,mmtt.transaction_type_id
1847 ,mmtt.transaction_action_id
1848 ,mmtt.transaction_temp_id
1849 ,mmtt.SECONDARY_TRANSACTION_QUANTITY -- 13431255
1850 ,mmtt.SECONDARY_UOM_CODE -- 13431255
1851 FROM mtl_material_transactions_temp MMTT
1852 WHERE transaction_temp_id=p_source_task_id);
1853
1854
1855 END IF;
1856 EXCEPTION
1857 WHEN FND_API.g_EXC_ERROR THEN
1858 x_return_status:=FND_API.G_RET_STS_ERROR;
1859 IF (l_debug=1) THEN
1860 print_debug('Error While Updating task',l_module_name,1);
1861 END IF;
1862
1863 WHEN OTHERS THEN
1864 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1865
1866 IF (l_debug=1) THEN
1867 print_debug('Unexpected Error:'||SQLERRM,l_module_name,1);
1868 END IF;
1869
1870 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1871 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
1872 END IF;
1873
1874 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1875 END;
1876
1877
1878
1879 END WMS_OP_RUNTIME_PVT_APIS;