[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.2.12000000.2 2007/09/03 06:48:57 ajunnikr 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.2.12000000.2 2007/09/03 06:48:57 ajunnikr 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 /**
1153 * <p>Procedure:<b>Delete_Dispatched_task</b>
1154 * This procedure deletes the task records into WMS_DISPATCHED_TASKS</p>
1155 * AND THERE IS AN AUTOMONOUS COMMIT IN THIS DELETE!
1156 * SO IT SHOULD ONLY BE USED TO DELETE WDT CREATED BY insert_dispatched_tasks.
1157 * @param p_source_task_id - Transaction Temp Id of the WDT record.
1158 * @param x_return_status - Return Status
1159 * @param x_msg_count - Returns Message Count
1160 * @param x_msg_data - Returns Error Message
1161 *
1162 * This API is necessary for instance when load errors out at online mode.
1163 * The calling procedure will simply rollback, but need to delete WDT
1164 * Autonomously inserted earlier.
1165 */
1166 PROCEDURE delete_dispatched_task
1167 (
1168 p_source_task_id IN NUMBER
1169 , p_wms_task_type IN NUMBER
1170 , x_return_status OUT NOCOPY VARCHAR2
1171 , x_msg_count OUT NOCOPY NUMBER
1172 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1173 )IS
1174 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1175 l_module_name CONSTANT VARCHAR2(30):='delete_dispatched_task';
1176 PRAGMA AUTONOMOUS_TRANSACTION;
1177 BEGIN
1178
1179 IF (l_debug=1) THEN
1180 print_debug('Entered. ',l_module_name,3);
1181 print_debug('p_source_task_id = '||p_source_task_id,l_module_name,3);
1182 print_debug('p_wms_task_type = '||p_wms_task_type,l_module_name,3);
1183 END IF;
1184
1185 x_return_status := fnd_api.g_ret_sts_success;
1186
1187 DELETE wms_dispatched_tasks
1188 WHERE transaction_temp_id = p_source_task_id
1189 AND task_type= p_wms_task_type;
1190
1191 COMMIT;
1192
1193 IF (l_debug=1) THEN
1194 print_debug('Completed. ',l_module_name,3);
1195
1196 END IF;
1197
1198
1199 END delete_dispatched_task;
1200
1201
1202
1203 /**
1204 * <p>Procedure:<b>Update_Dispatched_tasks</b>
1205 * This procedure updates the task records in WMS_DISPATCHED_TASKS</p>
1206 * @param p_wdt_rec - WDT record that has to be updated.
1207 * @param x_return_status - Return Status
1208 * @param x_msg_count - Returns Message Count
1209 * @param x_msg_data - Returns Error Message
1210 */
1211 PROCEDURE update_dipatched_tasks(
1212 p_wdt_rec IN wms_dispatched_tasks%ROWTYPE
1213 , x_return_status OUT NOCOPY VARCHAR2
1214 , x_msg_count OUT NOCOPY NUMBER
1215 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1216 )IS
1217
1218 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1219 l_module_name CONSTANT VARCHAR2(30):='Update_Dispatched_Tasks';
1220
1221 BEGIN
1222 IF (l_debug=1) THEN
1223 print_debug('p_wdt_rec.task_id'||p_wdt_rec.task_id,l_module_name,3);
1224 END IF;
1225
1226 x_return_status:=fnd_api.g_ret_sts_success;
1227
1228 IF p_wdt_rec.task_id IS NULL THEN
1229 IF (l_debug=1) THEN
1230 print_debug('Task Id is null',l_module_name,1);
1231 END IF;
1232 RAISE FND_API.G_EXC_ERROR;
1233 END IF;
1234
1235 --Updating the WDT Record
1236 IF (l_debug=1) THEN
1237 print_debug('Updating WMS_Dispatched_tasks',l_module_name,9);
1238 END IF;
1239
1240 UPDATE WMS_DISPATCHED_TASKS
1241 SET
1242 TASK_ID =decode(p_wdt_rec.TASK_ID,G_MISS_NUM,NULL,NULL,TASK_ID,p_wdt_rec.TASK_ID),
1243 TRANSACTION_TEMP_ID =decode(p_wdt_rec.TRANSACTION_TEMP_ID,G_MISS_NUM,NULL,NULL,TRANSACTION_TEMP_ID,p_wdt_rec.TRANSACTION_TEMP_ID),
1244 ORGANIZATION_ID =decode(p_wdt_rec.ORGANIZATION_ID,G_MISS_NUM,NULL,NULL,ORGANIZATION_ID,p_wdt_rec.ORGANIZATION_ID),
1245 USER_TASK_TYPE =decode(p_wdt_rec.USER_TASK_TYPE,G_MISS_NUM,NULL,NULL,USER_TASK_TYPE,p_wdt_rec.USER_TASK_TYPE),
1246 PERSON_ID =decode(p_wdt_rec.PERSON_ID,G_MISS_NUM,NULL,NULL,PERSON_ID,p_wdt_rec.PERSON_ID),
1247 EFFECTIVE_START_DATE =decode(p_wdt_rec.EFFECTIVE_START_DATE,NULL,EFFECTIVE_START_DATE,G_MISS_DATE,NULL,p_wdt_rec.EFFECTIVE_START_DATE),
1248 EFFECTIVE_END_DATE =decode(p_wdt_rec.EFFECTIVE_END_DATE,NULL,EFFECTIVE_END_DATE,G_MISS_DATE,NULL,p_wdt_rec.EFFECTIVE_END_DATE),
1249 EQUIPMENT_ID =decode(p_wdt_rec.EQUIPMENT_ID,G_MISS_NUM,NULL,NULL,EQUIPMENT_ID,p_wdt_rec.EQUIPMENT_ID),
1250 EQUIPMENT_INSTANCE =decode(p_wdt_rec.EQUIPMENT_INSTANCE,G_MISS_CHAR,NULL,NULL,EQUIPMENT_INSTANCE,p_wdt_rec.EQUIPMENT_INSTANCE),
1251 PERSON_RESOURCE_ID =decode(p_wdt_rec.PERSON_RESOURCE_ID,G_MISS_NUM,NULL,NULL,PERSON_RESOURCE_ID,p_wdt_rec.PERSON_RESOURCE_ID),
1252 MACHINE_RESOURCE_ID =decode(p_wdt_rec.MACHINE_RESOURCE_ID,G_MISS_NUM,NULL,NULL,MACHINE_RESOURCE_ID,p_wdt_rec.MACHINE_RESOURCE_ID),
1253 STATUS =decode(p_wdt_rec.STATUS,G_MISS_NUM,NULL,NULL,STATUS,p_wdt_rec.STATUS),
1254 DISPATCHED_TIME =decode(p_wdt_rec.DISPATCHED_TIME,NULL,DISPATCHED_TIME,G_MISS_DATE,NULL,p_wdt_rec.DISPATCHED_TIME),
1255 LOADED_TIME =decode(p_wdt_rec.LOADED_TIME,NULL,LOADED_TIME,G_MISS_DATE,NULL,p_wdt_rec.LOADED_TIME),
1256 DROP_OFF_TIME =decode(p_wdt_rec.DROP_OFF_TIME,NULL,DROP_OFF_TIME,G_MISS_DATE,NULL,p_wdt_rec.DROP_OFF_TIME),
1257 LAST_UPDATE_DATE =SYSDATE,
1258 LAST_UPDATED_BY =FND_GLOBAL.USER_ID,
1259 LAST_UPDATE_LOGIN =decode(p_wdt_rec.LAST_UPDATE_LOGIN,G_MISS_NUM,NULL,NULL,LAST_UPDATE_LOGIN,p_wdt_rec.LAST_UPDATE_LOGIN),
1260 ATTRIBUTE_CATEGORY =decode(p_wdt_rec.ATTRIBUTE_CATEGORY,G_MISS_CHAR,NULL,NULL,ATTRIBUTE_CATEGORY,p_wdt_rec.ATTRIBUTE_CATEGORY),
1261 ATTRIBUTE1 =decode(p_wdt_rec.ATTRIBUTE1,G_MISS_CHAR,NULL,NULL,ATTRIBUTE1,p_wdt_rec.ATTRIBUTE1),
1262 ATTRIBUTE2 =decode(p_wdt_rec.ATTRIBUTE2,G_MISS_CHAR,NULL,NULL,ATTRIBUTE2,p_wdt_rec.ATTRIBUTE2),
1263 ATTRIBUTE3 =decode(p_wdt_rec.ATTRIBUTE3,G_MISS_CHAR,NULL,NULL,ATTRIBUTE3,p_wdt_rec.ATTRIBUTE3),
1264 ATTRIBUTE4 =decode(p_wdt_rec.ATTRIBUTE4,G_MISS_CHAR,NULL,NULL,ATTRIBUTE4,p_wdt_rec.ATTRIBUTE4),
1265 ATTRIBUTE5 =decode(p_wdt_rec.ATTRIBUTE5,G_MISS_CHAR,NULL,NULL,ATTRIBUTE5,p_wdt_rec.ATTRIBUTE5),
1266 ATTRIBUTE6 =decode(p_wdt_rec.ATTRIBUTE6,G_MISS_CHAR,NULL,NULL,ATTRIBUTE6,p_wdt_rec.ATTRIBUTE6),
1267 ATTRIBUTE7 =decode(p_wdt_rec.ATTRIBUTE7,G_MISS_CHAR,NULL,NULL,ATTRIBUTE7,p_wdt_rec.ATTRIBUTE7),
1268 ATTRIBUTE8 =decode(p_wdt_rec.ATTRIBUTE8,G_MISS_CHAR,NULL,NULL,ATTRIBUTE8,p_wdt_rec.ATTRIBUTE8),
1269 ATTRIBUTE9 =decode(p_wdt_rec.ATTRIBUTE9,G_MISS_CHAR,NULL,NULL,ATTRIBUTE9,p_wdt_rec.ATTRIBUTE9),
1270 ATTRIBUTE10 =decode(p_wdt_rec.ATTRIBUTE10,G_MISS_CHAR,NULL,NULL,ATTRIBUTE10,p_wdt_rec.ATTRIBUTE10),
1271 ATTRIBUTE11 =decode(p_wdt_rec.ATTRIBUTE11,G_MISS_CHAR,NULL,NULL,ATTRIBUTE11,p_wdt_rec.ATTRIBUTE11),
1272 ATTRIBUTE12 =decode(p_wdt_rec.ATTRIBUTE12,G_MISS_CHAR,NULL,NULL,ATTRIBUTE12,p_wdt_rec.ATTRIBUTE12),
1273 ATTRIBUTE13 =decode(p_wdt_rec.ATTRIBUTE13,G_MISS_CHAR,NULL,NULL,ATTRIBUTE13,p_wdt_rec.ATTRIBUTE13),
1274 ATTRIBUTE14 =decode(p_wdt_rec.ATTRIBUTE14,G_MISS_CHAR,NULL,NULL,ATTRIBUTE14,p_wdt_rec.ATTRIBUTE14),
1275 ATTRIBUTE15 =decode(p_wdt_rec.ATTRIBUTE15,G_MISS_CHAR,NULL,NULL,ATTRIBUTE15,p_wdt_rec.ATTRIBUTE15),
1276 TASK_TYPE =decode(p_wdt_rec.TASK_TYPE,G_MISS_NUM,NULL,NULL,TASK_TYPE,p_wdt_rec.TASK_TYPE),
1277 PRIORITY =decode(p_wdt_rec.PRIORITY,G_MISS_NUM,NULL,NULL,PRIORITY,p_wdt_rec.PRIORITY),
1278 TASK_GROUP_ID =decode(p_wdt_rec.TASK_GROUP_ID,G_MISS_NUM,NULL,NULL,TASK_GROUP_ID,p_wdt_rec.TASK_GROUP_ID),
1279 DEVICE_ID =decode(p_wdt_rec.DEVICE_ID,G_MISS_NUM,NULL,NULL,DEVICE_ID,p_wdt_rec.DEVICE_ID),
1280 DEVICE_INVOKED =decode(p_wdt_rec.DEVICE_INVOKED,G_MISS_CHAR,NULL,NULL,DEVICE_INVOKED,p_wdt_rec.DEVICE_INVOKED),
1281 DEVICE_REQUEST_ID =decode(p_wdt_rec.DEVICE_REQUEST_ID,G_MISS_NUM,NULL,NULL,DEVICE_REQUEST_ID,p_wdt_rec.DEVICE_REQUEST_ID),
1282 SUGGESTED_DEST_SUBINVENTORY=decode(p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY,G_MISS_CHAR,NULL,NULL,SUGGESTED_DEST_SUBINVENTORY,p_wdt_rec.SUGGESTED_DEST_SUBINVENTORY),
1283 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),
1284 OPERATION_PLAN_ID =decode(p_wdt_rec.OPERATION_PLAN_ID,G_MISS_NUM,NULL,NULL,OPERATION_PLAN_ID,p_wdt_rec.OPERATION_PLAN_ID),
1285 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),
1286 TRANSFER_LPN_ID =decode(p_wdt_rec.TRANSFER_LPN_ID,G_MISS_NUM,NULL,NULL,TRANSFER_LPN_ID,p_wdt_rec.transfer_lpn_id),
1287 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)
1288 WHERE task_id=p_wdt_rec.task_id;
1289
1290 EXCEPTION
1291 WHEN FND_API.g_EXC_ERROR THEN
1292 x_return_status:=FND_API.G_RET_STS_ERROR;
1293 IF (l_debug=1) THEN
1294 print_debug('Error While Updating task',l_module_name,1);
1295 END IF;
1296
1297 WHEN OTHERS THEN
1298 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1299 IF (l_debug=1) THEN
1300 print_debug('Unexpected Error:'||SQLERRM,l_module_name,1);
1301 END IF;
1302 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1303 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
1304 END IF;
1305 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1306 END;
1307
1308
1309
1310 /**
1311 * <p>Procedure:<b>Archive_Dispatched_tasks</b>
1312 * This procedure archives the task records into WMS_DISPATCHED_TASKS_HISTORY</p>
1313 * @param p_task_id - Task Id of WMS_DISPATCHED_TASKS
1314 * @param p_source_task_id - Document Id for the Parent document record
1315 * @param p_activity_type_id - Activity Type Id
1316 * @param p_op_plan_instance_id - Operation Plan Id for the Parent Record
1317 * @param p_op_plan_status - Operation plan status for the Parent record
1318 */
1319
1320 PROCEDURE archive_dispatched_tasks(
1321 x_return_status OUT NOCOPY VARCHAR2
1322 , x_msg_count OUT NOCOPY NUMBER
1323 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
1324 , p_task_id IN NUMBER
1325 , p_source_task_id IN NUMBER
1326 , p_activity_type_id IN NUMBER
1327 , p_op_plan_instance_id IN NUMBER
1328 , p_op_plan_status IN NUMBER
1329 ) IS
1330
1331 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1332 l_module_name CONSTANT VARCHAR2(30):= 'Archive_Dispatched_tasks';
1333 l_progress NUMBER;
1334 l_last_operation_dest_sub VARCHAR2(30);
1335 l_last_operation_dest_loc_id NUMBER;
1336 l_last_drop_off_time DATE;
1337 l_current_txn_id NUMBER; --5523365
1338 l_parent_txn_id NUMBER; --5523365
1339
1340 CURSOR c_last_task IS
1341 SELECT dest_subinventory_code, dest_locator_id, drop_off_time, source_document_id
1342 FROM wms_dispatched_tasks_history
1343 WHERE parent_transaction_id = p_source_task_id
1344 ORDER BY task_id DESC;
1345
1346 BEGIN
1347
1348 IF (l_debug=1) THEN
1349 print_debug('Archiving Dispatched tasks ',l_module_name,3);
1350 print_debug('p_task_id ==> '||p_task_id,l_module_name,3);
1351 print_debug('p_source_task_id ==> '||p_source_task_id,l_module_name,3);
1352 print_debug('p_activity_id ==> '||p_activity_type_id,l_module_name,3);
1353 END IF;
1354
1355 x_return_status :=FND_API.g_ret_sts_success;
1356
1357 l_progress:=10;
1358
1359 IF (p_task_id IS NULL AND p_source_task_id IS NULL AND p_activity_type_id IS NULL) THEN
1360 IF (l_debug=1) THEN
1361 print_debug('Invalid Input',l_module_name,1);
1362 END IF;
1363 RAISE FND_API.G_EXC_ERROR;
1364 END IF;
1365
1366 l_progress:=20;
1367
1368 IF (p_task_id IS NOT NULL) THEN /*Archiving the Child record*/
1369 IF (l_debug=1) THEN
1370 print_debug('Archiving the Child task with WDT task Id'||p_task_id,l_module_name,9);
1371 END IF;
1372
1373 l_progress:=30;
1374
1375
1376
1377 INSERT INTO WMS_DISPATCHED_TASKS_HISTORY
1378 ( TASK_ID
1379 ,TRANSACTION_ID
1380 ,ORGANIZATION_ID
1381 ,USER_TASK_TYPE
1382 ,PERSON_ID
1383 ,EFFECTIVE_START_DATE
1384 ,EFFECTIVE_END_DATE
1385 ,EQUIPMENT_ID
1386 ,EQUIPMENT_INSTANCE
1387 ,PERSON_RESOURCE_ID
1388 ,MACHINE_RESOURCE_ID
1389 ,STATUS
1390 ,DISPATCHED_TIME
1391 ,LOADED_TIME
1392 ,DROP_OFF_TIME
1393 ,LAST_UPDATE_DATE
1394 ,LAST_UPDATED_BY
1395 ,CREATION_DATE
1396 ,CREATED_BY
1397 ,LAST_UPDATE_LOGIN
1398 ,ATTRIBUTE_CATEGORY
1399 ,ATTRIBUTE1
1400 ,ATTRIBUTE2
1401 ,ATTRIBUTE3
1402 ,ATTRIBUTE4
1403 ,ATTRIBUTE5
1404 ,ATTRIBUTE6
1405 ,ATTRIBUTE7
1406 ,ATTRIBUTE8
1407 ,ATTRIBUTE9
1408 ,ATTRIBUTE10
1409 ,ATTRIBUTE11
1410 ,ATTRIBUTE12
1411 ,ATTRIBUTE13
1412 ,ATTRIBUTE14
1413 ,ATTRIBUTE15
1414 ,TASK_TYPE
1415 ,PRIORITY
1416 ,TASK_GROUP_ID
1417 ,SUGGESTED_DEST_SUBINVENTORY
1418 ,SUGGESTED_DEST_LOCATOR_ID
1419 ,OPERATION_PLAN_ID
1420 ,MOVE_ORDER_LINE_ID
1421 ,TRANSFER_LPN_ID
1422 ,TRANSACTION_BATCH_ID
1423 ,TRANSACTION_BATCH_SEQ
1424 ,INVENTORY_ITEM_ID
1425 ,REVISION
1426 ,TRANSACTION_QUANTITY
1427 ,TRANSACTION_UOM_CODE
1428 ,SOURCE_SUBINVENTORY_CODE
1429 ,SOURCE_LOCATOR_ID
1430 ,DEST_SUBINVENTORY_CODE
1431 ,DEST_LOCATOR_ID
1432 ,LPN_ID
1433 ,CONTENT_LPN_ID
1434 ,IS_PARENT
1435 ,PARENT_TRANSACTION_ID
1436 ,TRANSFER_ORGANIZATION_ID
1437 ,SOURCE_DOCUMENT_ID
1438 ,OP_PLAN_INSTANCE_ID
1439 ,TRANSACTION_SOURCE_TYPE_ID
1440 ,TRANSACTION_TYPE_ID
1441 ,transaction_action_id
1442 ,transaction_temp_id
1443 )
1444 (SELECT
1445 WDT.TASK_ID
1446 ,WDT.TRANSACTION_TEMP_ID
1447 ,WDT.ORGANIZATION_ID
1448 ,WDT.USER_TASK_TYPE
1449 ,WDT.PERSON_ID
1450 ,WDT.EFFECTIVE_START_DATE
1451 ,WDT.EFFECTIVE_END_DATE
1452 ,WDT.EQUIPMENT_ID
1453 ,WDT.EQUIPMENT_INSTANCE
1454 ,WDT.PERSON_RESOURCE_ID
1455 ,WDT.MACHINE_RESOURCE_ID
1456 ,6
1457 ,WDT.DISPATCHED_TIME
1458 ,WDT.LOADED_TIME
1459 ,WDT.DROP_OFF_TIME
1460 ,SYSDATE
1461 ,FND_GLOBAL.USER_ID
1462 ,WDT.CREATION_DATE
1463 ,WDT.CREATED_BY
1464 ,WDT.LAST_UPDATE_LOGIN
1465 ,WDT.ATTRIBUTE_CATEGORY
1466 ,WDT.ATTRIBUTE1
1467 ,WDT.ATTRIBUTE2
1468 ,WDT.ATTRIBUTE3
1469 ,WDT.ATTRIBUTE4
1470 ,WDT.ATTRIBUTE5
1471 ,WDT.ATTRIBUTE6
1472 ,WDT.ATTRIBUTE7
1473 ,WDT.ATTRIBUTE8
1474 ,WDT.ATTRIBUTE9
1475 ,WDT.ATTRIBUTE10
1476 ,WDT.ATTRIBUTE11
1477 ,WDT.ATTRIBUTE12
1478 ,WDT.ATTRIBUTE13
1479 ,WDT.ATTRIBUTE14
1480 ,WDT.ATTRIBUTE15
1481 ,WDT.TASK_TYPE
1482 ,WDT.PRIORITY
1483 ,WDT.TASK_GROUP_ID
1484 ,Nvl(WDT.suggested_dest_subinventory,pmmtt.subinventory_code)
1485 ,Nvl(WDT.suggested_dest_locator_id,pmmtt.locator_id)
1486 ,MMTT.OPERATION_PLAN_ID
1487 ,MMTT.MOVE_ORDER_LINE_ID
1488 ,MMTT.TRANSFER_LPN_ID
1489 ,MMTT.TRANSACTION_BATCH_ID
1490 ,MMTT.TRANSACTION_BATCH_SEQ
1491 ,MMTT.INVENTORY_ITEM_ID
1492 ,MMTT.REVISION
1493 ,MMTT.TRANSACTION_QUANTITY
1494 ,MMTT.TRANSACTION_UOM
1495 ,decode(MMTT.TRANSFER_SUBINVENTORY,NULL,NULL,MMTT.SUBINVENTORY_CODE)
1496 ,decode(MMTT.TRANSFER_TO_LOCATION,NULL,NULL,MMTT.LOCATOR_ID)
1497 ,nvl(MMTT.TRANSFER_SUBINVENTORY,MMTT.SUBINVENTORY_CODE)
1498 ,nvl(MMTT.TRANSFER_TO_LOCATION,MMTT.LOCATOR_ID)
1499 ,MMTT.LPN_ID
1500 ,MMTT.CONTENT_LPN_ID
1501 ,'N'
1502 ,MMTT.PARENT_LINE_ID
1503 ,MMTT.TRANSFER_ORGANIZATION
1504 ,NVL(MMTT.rcv_transaction_id,MMTT.transaction_header_id)
1505 ,WDT.OP_PLAN_INSTANCE_ID
1506 ,mmtt.transaction_source_type_id
1507 ,mmtt.transaction_type_id
1508 ,mmtt.transaction_action_id
1509 ,mmtt.transaction_temp_id
1510 FROM WMS_DISPATCHED_TASKS wdt
1511 , MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1512 , mtl_material_transactions_temp pmmtt
1513 WHERE WDT.TASK_ID=p_task_id
1514 AND WDT.transaction_temp_id=MMTT.transaction_temp_id
1515 AND mmtt.parent_line_id = pmmtt.transaction_temp_id (+)
1516 );
1517
1518 l_progress:=40;
1519
1520 /*Archive Tasks,now delete records from WDT*/
1521 IF (l_debug=1) THEN
1522 print_debug('Archived WDT,now deleting the WDT record',l_module_name,9);
1523 END IF;
1524
1525 DELETE FROM wms_dispatched_tasks
1526 WHERE task_id = p_task_id;
1527
1528 l_progress:=50;
1529
1530
1531 ELSE
1532
1533 /*Archiving the parent Record*/
1534
1535 -- get the destination sub/loc for the last task
1536 -- which might be different to those of the parent mmtt
1537 OPEN c_last_task;
1538 FETCH c_last_task INTO
1539 l_last_operation_dest_sub,
1540 l_last_operation_dest_loc_id,
1541 l_last_drop_off_time,
1542 l_current_txn_id;
1543 CLOSE c_last_task;
1544
1545 --In R12, to fix bug 5523365, we need to get the
1546 --parent_transaction_id from RT as we no longer stamp it on
1547 --MOL/MMTT. This was a change done for MOL consolidation project.
1548 --This will be stamped on WDTH for parent if it is a putaway task.
1549 IF l_current_txn_id IS NOT NULL THEN
1550 BEGIN
1551 SELECT parent_transaction_id
1552 INTO l_parent_txn_id
1553 FROM rcv_transactions
1554 WHERE transaction_id = l_current_txn_id;
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 l_parent_txn_id := NULL;
1558 END;
1559 END IF;
1560
1561
1562 IF (l_debug=1) THEN
1563 print_debug('l_last_operation_dest_sub = '||l_last_operation_dest_sub,l_module_name,9);
1564 print_debug('l_last_operation_dest_loc_id = '||l_last_operation_dest_loc_id,l_module_name,9);
1565 print_debug('l_current_txn_id = '||l_current_txn_id,l_module_name,9);
1566 print_debug('l_parent_txn_id = '||l_parent_txn_id,l_module_name,9);
1567 print_debug('Inserting Records for Parent with source_task_id'||p_source_task_id,l_module_name,9);
1568 END IF;
1569
1570 INSERT INTO WMS_DISPATCHED_TASKS_HISTORY
1571 ( TASK_ID
1572 ,TRANSACTION_ID
1573 ,ORGANIZATION_ID
1574 ,USER_TASK_TYPE
1575 ,PERSON_ID
1576 ,EFFECTIVE_START_DATE
1577 ,EFFECTIVE_END_DATE
1578 ,EQUIPMENT_ID
1579 ,EQUIPMENT_INSTANCE
1580 ,PERSON_RESOURCE_ID
1581 ,MACHINE_RESOURCE_ID
1582 ,STATUS
1583 ,DISPATCHED_TIME
1584 ,LOADED_TIME
1585 ,DROP_OFF_TIME
1586 ,LAST_UPDATE_DATE
1587 ,LAST_UPDATED_BY
1588 ,CREATION_DATE
1589 ,CREATED_BY
1590 ,LAST_UPDATE_LOGIN
1591 ,ATTRIBUTE_CATEGORY
1592 ,ATTRIBUTE1
1593 ,ATTRIBUTE2
1594 ,ATTRIBUTE3
1595 ,ATTRIBUTE4
1596 ,ATTRIBUTE5
1597 ,ATTRIBUTE6
1598 ,ATTRIBUTE7
1599 ,ATTRIBUTE8
1600 ,ATTRIBUTE9
1601 ,ATTRIBUTE10
1602 ,ATTRIBUTE11
1603 ,ATTRIBUTE12
1604 ,ATTRIBUTE13
1605 ,ATTRIBUTE14
1606 ,ATTRIBUTE15
1607 ,TASK_TYPE
1608 ,PRIORITY
1609 ,TASK_GROUP_ID
1610 ,SUGGESTED_DEST_SUBINVENTORY
1611 ,SUGGESTED_DEST_LOCATOR_ID
1612 ,OPERATION_PLAN_ID
1613 ,MOVE_ORDER_LINE_ID
1614 ,TRANSFER_LPN_ID
1615 ,TRANSACTION_BATCH_ID
1616 ,TRANSACTION_BATCH_SEQ
1617 ,INVENTORY_ITEM_ID
1618 ,REVISION
1619 ,TRANSACTION_QUANTITY
1620 ,TRANSACTION_UOM_CODE
1621 ,SOURCE_SUBINVENTORY_CODE
1622 ,SOURCE_LOCATOR_ID
1623 ,DEST_SUBINVENTORY_CODE
1624 ,DEST_LOCATOR_ID
1625 ,LPN_ID
1626 ,CONTENT_LPN_ID
1627 ,IS_PARENT
1628 ,PARENT_TRANSACTION_ID
1629 ,TRANSFER_ORGANIZATION_ID
1630 ,SOURCE_DOCUMENT_ID
1631 ,op_plan_instance_id
1632 ,TRANSACTION_SOURCE_TYPE_ID
1633 ,TRANSACTION_TYPE_ID
1634 ,transaction_action_id
1635 ,transaction_temp_id)
1636 ( SELECT
1637 wms_dispatched_tasks_s.NEXTVAL
1638 ,MMTT.TRANSACTION_TEMP_ID
1639 ,MMTT.ORGANIZATION_ID
1640 ,-1
1641 ,-1
1642 ,SYSDATE
1643 ,SYSDATE
1644 ,NULL
1645 ,NULL
1646 ,NULL
1647 ,NULL
1648 ,decode(p_op_plan_status,3,6,4,12,5,11,6)
1649 ,NULL
1650 ,NULL
1651 ,l_last_drop_off_time
1652 ,SYSDATE
1653 ,FND_GLOBAL.USER_ID
1654 ,SYSDATE
1655 ,FND_GLOBAL.USER_ID
1656 ,NULL
1657 ,NULL
1658 ,NULL
1659 ,NULL
1660 ,NULL
1661 ,NULL
1662 ,NULL
1663 ,NULL
1664 ,NULL
1665 ,NULL
1666 ,NULL
1667 ,NULL
1668 ,NULL
1669 ,NULL
1670 ,NULL
1671 ,NULL
1672 ,NULL
1673 ,MMTT.WMS_TASK_TYPE
1674 ,NULL
1675 ,NULL
1676 ,nvl(MMTT.TRANSFER_SUBINVENTORY,MMTT.SUBINVENTORY_CODE)
1677 ,nvl(MMTT.TRANSFER_TO_LOCATION,MMTT.LOCATOR_ID)
1678 ,MMTT.OPERATION_PLAN_ID
1679 ,MMTT.MOVE_ORDER_LINE_ID
1680 ,MMTT.TRANSFER_LPN_ID
1681 ,MMTT.TRANSACTION_BATCH_ID
1682 ,MMTT.TRANSACTION_BATCH_SEQ
1683 ,MMTT.INVENTORY_ITEM_ID
1684 ,MMTT.REVISION
1685 ,MMTT.TRANSACTION_QUANTITY
1686 ,MMTT.TRANSACTION_UOM
1687 ,decode(MMTT.TRANSFER_SUBINVENTORY,NULL,NULL,MMTT.SUBINVENTORY_CODE)
1688 ,decode(MMTT.TRANSFER_TO_LOCATION,NULL,NULL,MMTT.LOCATOR_ID)
1689 ,l_last_operation_dest_sub
1690 ,l_last_operation_dest_loc_id
1691 ,MMTT.LPN_ID
1692 ,MMTT.CONTENT_LPN_ID
1693 ,'Y'
1694 ,NULL
1695 ,MMTT.TRANSFER_ORGANIZATION
1696 ,Decode(mmtt.wms_task_type,2,Nvl(mmtt.transaction_source_id,l_parent_txn_id),mmtt.transaction_source_id)
1697 ,p_op_plan_instance_id
1698 ,mmtt.transaction_source_type_id
1699 ,mmtt.transaction_type_id
1700 ,mmtt.transaction_action_id
1701 ,mmtt.transaction_temp_id
1702 FROM mtl_material_transactions_temp MMTT
1703 WHERE transaction_temp_id=p_source_task_id);
1704
1705
1706 END IF;
1707 EXCEPTION
1708 WHEN FND_API.g_EXC_ERROR THEN
1709 x_return_status:=FND_API.G_RET_STS_ERROR;
1710 IF (l_debug=1) THEN
1711 print_debug('Error While Updating task',l_module_name,1);
1712 END IF;
1713
1714 WHEN OTHERS THEN
1715 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1716
1717 IF (l_debug=1) THEN
1718 print_debug('Unexpected Error:'||SQLERRM,l_module_name,1);
1719 END IF;
1720
1721 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1722 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
1723 END IF;
1724
1725 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1726 END;
1727
1728
1729
1730 END WMS_OP_RUNTIME_PVT_APIS;