[Home] [Help]
PACKAGE BODY: APPS.INV_REDUCE_MOQD_PVT
Source
1 PACKAGE BODY INV_REDUCE_MOQD_PVT AS
2 /* $Header: INVRMOQB.pls 120.8.12020000.2 2012/07/09 08:16:19 asugandh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_REDUCE_MOQD_PVT';
5 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0); /* Added for bug 7588761 */
6 g_lock_handle VARCHAR2(128) :=''; /* Added for bug 7588761 */
7
8 PROCEDURE debug(
9 p_message IN VARCHAR2
10 ) IS
11 BEGIN
12 inv_log_util.trace(p_message, G_PKG_NAME , 10 );
13 EXCEPTION
14 WHEN OTHERS THEN
15 NULL;
16 END debug;
17
18
19 /* Added following new function for proper lock handling for bug 7588761 */
20 FUNCTION get_lock_handle (
21 p_request_id NUMBER,
22 p_org_id NUMBER,
23 p_item_id NUMBER
24 ) RETURN VARCHAR2 IS
25 /* Changed order of parameters for bug 8662708 */
26 PRAGMA AUTONOMOUS_TRANSACTION;
27 l_lock_handle VARCHAR2(128);
28 l_lock_name VARCHAR2(128); /* Increased width for bug 8662708 */
29 BEGIN
30
31 l_lock_name := 'INV_CMOQD_'||p_request_id||'_'|| p_org_id || '_' || p_item_id;
32 if (g_debug = 1) then
33 debug('get lock handler '||l_lock_name);
34 end if;
35
36 dbms_lock.allocate_unique(
37 lockname => l_lock_name
38 ,lockhandle => l_lock_handle);
39
40 return l_lock_handle;
41
42 END get_lock_handle;
43
44 /* Added following new procedure for proper lock handling for bug 7588761 */
45 PROCEDURE release_locks (
46 p_request_id NUMBER,
47 p_item_id NUMBER,
48 p_org_id NUMBER,
49 x_err_code OUT NOCOPY NUMBER,
50 x_err_msg OUT NOCOPY VARCHAR2
51 ) IS
52
53 l_lock_handle VARCHAR2(128);
54
55 BEGIN
56
57 if (p_request_id is null or p_org_id is null or p_item_id is null) then
58
59 fnd_message.set_name('INV','INV_LOCK_RELEASE_MISSING_ARGS');
60 x_err_msg := fnd_message.get;
61 fnd_file.put_line(fnd_file.log, x_err_msg);
62 rollback;
63 end if;
64
65 l_lock_handle := get_lock_handle (p_request_id, p_org_id, p_item_id);
66
67 if(g_debug = 1) then
68 debug('Release the lock : '||l_lock_handle);
69 end if;
70
71 x_err_code := dbms_lock.release(l_lock_handle);
72 -- parameter error,illegal lockhandle
73 if x_err_code IN (3,5) THEN
74 fnd_message.set_name('INV','INV_LOCK_RELEASE_ERROR');
75 x_err_msg := fnd_message.get;
76 fnd_file.put_line(fnd_file.log, x_err_msg);
77 rollback;
78 end if;
79
80 g_lock_handle :='';
81
82 END release_locks;
83
84 /* Added following new procedure for proper lock handling for bug 7588761 */
85 PROCEDURE lock_org_item (
86 p_request_id NUMBER,
87 p_item_id NUMBER,
88 p_org_id NUMBER,
89 x_err_code OUT NOCOPY NUMBER,
90 x_err_msg OUT NOCOPY VARCHAR2
91 ) IS
92
93 l_lock_handle VARCHAR2(128);
94
95 CURSOR C IS
96 SELECT *
97 FROM MTL_ONHAND_QUANTITIES_DETAIL
98 WHERE ORGANIZATION_ID = p_org_id
99 AND INVENTORY_ITEM_ID = p_item_id
100 FOR UPDATE NOWAIT;
101
102
103 BEGIN
104 l_lock_handle :=get_lock_handle (p_request_id, p_org_id, p_item_id);
105
106 if(g_debug = 1) then
107 debug('Got the lockhandle : '||l_lock_handle);
108 end if;
109
110 x_err_code := dbms_lock.request(
111 lockhandle => l_lock_handle
112 ,lockmode => dbms_lock.x_mode
113 ,timeout => dbms_lock.maxwait
114 ,release_on_commit => TRUE);
115
116
117 if (x_err_code not in (0,4)) then
118 if (x_err_code = 1 or x_err_code = 2) then -- timeout
119 rollback;
120 else -- internal error - not fault of user
121 x_err_code := SQLCODE;
122 x_err_msg := substr('Error request lock: ' || SQLERRM, 1, 2000);
123 rollback;
124 end if;
125 elsif (x_err_code = 0 ) then
126 g_lock_handle := l_lock_handle;
127 open c;
128 close c;
129 end if;
130
131 EXCEPTION
132 WHEN app_exceptions.record_lock_exception THEN
133 x_err_code :=1;
134 x_err_msg := substr('Exception in LOCK_ORG_ITEM: ' || SQLERRM, 1, 2000);
135 WHEN OTHERS THEN
136 x_err_code := SQLCODE;
137 x_err_msg := substr('Error in LOCK_ORG_ITEM: ' || SQLERRM, 1, 2000);
138
139 END lock_org_item;
140
141 PROCEDURE consolidate_moqd(
142 ERRBUF OUT NOCOPY VARCHAR2 ,
143 RETCODE OUT NOCOPY NUMBER ,
144 P_ORG_ID IN NUMBER
145 ) IS
146
147 /* Commented for bug 7588761 */
148 -- l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
149 l_profile VARCHAR2(1) := NVL(FND_PROFILE.VALUE('INV_FIFO_ORIG_REC_DATE'),'N');
150 l_user_id NUMBER := NVL(FND_PROFILE.VALUE('USER_ID'),-999);
151 l_request_id NUMBER := NVL(FND_PROFILE.VALUE('CONC_REQUEST_ID'),-999);
152
153 l_moq_count NUMBER;
154 l_moq_sum NUMBER;
155 l_moq_backup_count NUMBER;
156 l_moq_backup_sum NUMBER;
157 l_moq_sec_sum NUMBER;
158 l_moq_sec_backup_sum NUMBER;
159
160 l_stmt_count NUMBER ;
161 l_return_status VARCHAR2(30);
162 l_conc_status BOOLEAN;
163
164 l_err_msg varchar2(2000); /* Added for bug 7588761 */
165 l_err_code number; /* Added for bug 7588761 */
166 l_proc_cnt NUMBER; /* Added for bug 7588761 */
167
168 l_ret BOOLEAN;
169
170 l_org_id NUMBER;
171 l_org_code VARCHAR2(3);
172
173 l_prof_cnt NUMBER;
174 l_resource NUMBER;
175 /* Commented for bug 7588761
176 RESOURCE_BUSY EXCEPTION;
177 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY ,-54);
178 */
179
180 -- Bug 7681955
181 -- Allow for process enabled orgs.
182 CURSOR elig_orgs IS
183 SELECT mp.organization_id, mp.organization_code
184 FROM mtl_parameters mp
185 WHERE mp.organization_id = P_ORG_ID
186 AND mp.wms_enabled_flag = 'N';
187 -- OR mp.process_enabled_flag = 'Y');
188
189 /* Commented for bug 7588761
190 CURSOR lock_moqd IS */
191
192 CURSOR moqd_org_item (p_org_id NUMBER) IS
193 SELECT ORGANIZATION_ID, INVENTORY_ITEM_ID
194 FROM MTL_ONHAND_QUANTITIES_DETAIL
195 WHERE ORGANIZATION_ID = P_ORG_ID
196 GROUP BY ORGANIZATION_ID, INVENTORY_ITEM_ID;
197 -- FOR UPDATE NOWAIT; /* commented for bug 7588761 */
198 BEGIN
199 RETCODE := 0;
200
201 l_stmt_count := 50;
202
203 IF P_ORG_ID IS NULL THEN
204 IF(g_debug = 1) THEN
205 debug('The parameter P_ORG_ID cannot be NULL');
206 END IF;
207 RETCODE := 2;
208 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
209 -- Consolidation of Onhand Quantities failed.
210 ERRBUF := FND_MESSAGE.get;
211 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
212 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
213 ROLLBACK;
214 RETURN;
215 END IF;
216
217 l_stmt_count := 100;
218
219 -- If fnd_profile returns 'N' for the profile, then make sure that this profile is not
220 -- set to 'Y' at any other levels before proceeding with onhand consolidation.
221
222 IF (l_profile = 'N') THEN
223
224 l_stmt_count := 110;
225
226 SELECT count(1) INTO l_prof_cnt
227 FROM fnd_profile_options o, fnd_profile_option_values v
228 WHERE o.profile_option_name = 'INV_FIFO_ORIG_REC_DATE'
229 AND o.start_date_active <= sysdate
230 AND (nvl(o.end_date_active,sysdate) >= sysdate)
231 AND o.profile_option_id = v.profile_option_id
232 AND o.application_id = v.application_id
233 AND nvl(v.profile_option_value,'N') = 'Y' ;
234
235 l_stmt_count := 120;
236
237 IF (l_prof_cnt > 0) THEN
238 IF(g_debug = 1) THEN
239 debug('The Profile INV: FIFO for Original Receipt Date is set to Yes at Appl/Resp/User Level');
240 END IF;
241 RETCODE := 2;
242 FND_MESSAGE.set_name('INV','INV_MOQD_FIFO_SET');
243 -- The Profile INV: FIFO for Original Receipt Date is set to Yes and this program should not be run when the option is set to Yes.
244 ERRBUF := FND_MESSAGE.get;
245 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
246 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
247 ROLLBACK;
248 RETURN;
249 END IF;
250 ELSE -- l_profile = 'Y'
251
252 l_stmt_count := 130;
253
254 -- The error message says it all.
255 IF(g_debug = 1) THEN
256 debug('The Profile INV: FIFO for Original Receipt Date is set to Yes and this program should not be run when this option is set to Yes');
257 END IF;
258 RETCODE := 2;
259 FND_MESSAGE.set_name('INV','INV_MOQD_FIFO_SET');
260 -- The Profile INV: FIFO for Original Receipt Date is set to Yes and this program should not be run when the option is set to Yes.
261 ERRBUF := FND_MESSAGE.get;
262 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
263 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
264 ROLLBACK;
265 RETURN;
266 END IF;
267
268 l_stmt_count := 150;
269 -- BEGIN
270
271 -- Lock all the rows in MOQD for that Org_id using lock_moqd cursor
272
273 BEGIN
274 OPEN elig_orgs;
275 FETCH elig_orgs INTO l_org_id, l_org_code;
276 IF elig_orgs%NOTFOUND THEN
277 IF(g_debug = 1) THEN
278 -- Bug 7681955
279 -- Allow for process enabled orgs.
280 debug('This Organization is WMS enabled'); /* or Process enabled'); */
281 END IF;
282 RETCODE := 2;
283 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
284 -- Consolidation of Onhand Quantities failed.
285 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
286 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
287 RETURN;
288 END IF;
289 CLOSE elig_orgs;
290 END;
291
292 /* Commented for Bug 7588761
293
294 OPEN lock_moqd;
295 CLOSE lock_moqd;
296
297 EXCEPTION
298 WHEN RESOURCE_BUSY THEN
299 IF(g_debug = 1) THEN
300 debug('Cannot obtain locks on MOQD');
301 END IF;
302
303 -- Just for pre-caution
304 IF (lock_moqd%ISOPEN) THEN
305 CLOSE lock_moqd;
306 END IF;
307
308 RETCODE := 2;
309 FND_MESSAGE.set_name('INV','INV_MOQD_CANNOT_LOCK');
310 -- The Onhand Quantities table cannot be locked for consolidation.
311 ERRBUF := FND_MESSAGE.get;
312 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
313 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
314 ROLLBACK;
315 RETURN;
316 END;
317 */
318 l_proc_cnt := 0;
319 /* Modified following loop for bug 7588761 */
320
321 FOR l_moqd in moqd_org_item(l_org_id) LOOP
322 if(g_debug = 1) then
323 debug('-----------------------------------------------');
324 debug('Running for Org - '||l_org_code||', Id = '||l_org_id);
325 end if;
326 if (g_debug = 1) then
327 debug('lock on moqd for org_id' ||l_moqd.ORGANIZATION_ID||' and item '||l_moqd.INVENTORY_ITEM_ID );
328 end if;
329 /* Start of changes for bug 7588761 */
330 lock_org_item (
331 p_request_id =>l_request_id,
332 p_item_id =>l_moqd.INVENTORY_ITEM_ID,
333 p_org_id =>l_moqd.ORGANIZATION_ID,
334 x_err_code =>l_err_code,
335 x_err_msg =>l_err_msg
336 );
337
338 if l_err_code = 0 then -- lock granted
339 if (g_debug = 1) then
340 debug('Lock granted on item_id '||l_moqd.INVENTORY_ITEM_ID );
341 end if;
342 l_stmt_count := 200;
343 --delete non-summarized rows from mtl_moq_backup table
344 DELETE FROM mtl_moqd_backup
345 WHERE summarized_flag = 'N';
346
347 l_stmt_count := 300;
348
349 /* Commented following for bug 7588761 */
350 /*
351 -- Go thru the loop for the eligible org
352 FOR elig_orgs_rec in elig_orgs LOOP
353
354 IF(g_debug = 1) THEN
355 debug('-----------------------------------------------');
356 debug('Running for Org - '||elig_orgs_rec.organization_code||', Id = '||elig_orgs_rec.organization_id);
357 END IF;
358 */
359 --copy records from mtl_onhand_quantities_detail into mtl_moq_backup table
360 /* Added inventory_item_id condition for bug 7588761 */
361 INSERT INTO MTL_MOQD_BACKUP(
362 INVENTORY_ITEM_ID
363 ,ORGANIZATION_ID
364 ,DATE_RECEIVED
365 ,LAST_UPDATE_DATE
366 ,LAST_UPDATED_BY
367 ,CREATION_DATE
368 ,CREATED_BY
369 ,LAST_UPDATE_LOGIN
370 ,PRIMARY_TRANSACTION_QUANTITY
371 ,SUBINVENTORY_CODE
372 ,REVISION
373 ,LOCATOR_ID
374 ,CREATE_TRANSACTION_ID
375 ,UPDATE_TRANSACTION_ID
376 ,LOT_NUMBER
377 ,ORIG_DATE_RECEIVED
378 ,COST_GROUP_ID
379 ,CONTAINERIZED_FLAG
380 ,PROJECT_ID
381 ,TASK_ID
382 ,ONHAND_QUANTITIES_ID
383 ,ORGANIZATION_TYPE
384 ,OWNING_ORGANIZATION_ID
385 ,OWNING_TP_TYPE
386 ,PLANNING_ORGANIZATION_ID
387 ,PLANNING_TP_TYPE
388 ,TRANSACTION_UOM_CODE
389 ,TRANSACTION_QUANTITY
390 ,SECONDARY_UOM_CODE
391 ,SECONDARY_TRANSACTION_QUANTITY
392 ,IS_CONSIGNED
393 ,ROW_ID
394 ,SUMMARIZED_FLAG
395 ,STATUS_ID -- Bug 12776732
396 )
397 SELECT
398 INVENTORY_ITEM_ID
399 ,ORGANIZATION_ID
400 ,DATE_RECEIVED
401 ,LAST_UPDATE_DATE
402 ,LAST_UPDATED_BY
403 ,CREATION_DATE
404 ,CREATED_BY
405 ,LAST_UPDATE_LOGIN
406 ,PRIMARY_TRANSACTION_QUANTITY
407 ,SUBINVENTORY_CODE
408 ,REVISION
409 ,LOCATOR_ID
410 ,CREATE_TRANSACTION_ID
411 ,UPDATE_TRANSACTION_ID
412 ,LOT_NUMBER
413 ,ORIG_DATE_RECEIVED
414 ,COST_GROUP_ID
415 ,CONTAINERIZED_FLAG
416 ,PROJECT_ID
417 ,TASK_ID
418 ,ONHAND_QUANTITIES_ID
419 ,ORGANIZATION_TYPE
420 ,OWNING_ORGANIZATION_ID
421 ,OWNING_TP_TYPE
422 ,PLANNING_ORGANIZATION_ID
423 ,PLANNING_TP_TYPE
424 ,TRANSACTION_UOM_CODE
425 ,TRANSACTION_QUANTITY
426 ,SECONDARY_UOM_CODE
427 ,SECONDARY_TRANSACTION_QUANTITY
428 ,IS_CONSIGNED
429 ,ROWID
430 ,'N'
431 ,STATUS_ID -- Bug 12776732
432 FROM MTL_ONHAND_QUANTITIES_DETAIL
433 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
434 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
435 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
436 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
437 AND PLANNING_TP_TYPE = 2
438 AND OWNING_TP_TYPE = 2;
439 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0; -- Bug 7681955
440
441 IF(g_debug = 1) THEN
442 debug('Finished insert into MTL_MOQD_BACKUP');
443 END IF;
444
445 l_stmt_count := 310;
446
447 --check count(*) to make sure copy went okay
448 /* Added inventory_item_id condition for bug 7588761 */
449 SELECT count(*)
450 INTO l_moq_count
451 FROM mtl_onhand_quantities_detail moqd
452 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
453 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
454 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
455 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
456 AND PLANNING_TP_TYPE = 2
457 AND OWNING_TP_TYPE = 2
458 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0 -- Bug 7681955
459 AND ROWID IN (SELECT ROW_ID FROM MTL_MOQD_BACKUP mmb
460 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
461 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
462 AND SUMMARIZED_FLAG = 'N');
463
464 l_stmt_count := 320;
465
466 SELECT count(*)
467 INTO l_moq_backup_count
468 FROM mtl_moqd_backup
469 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
470 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID /* Added for bug 7588761 */
471 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
472 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
473 AND PLANNING_TP_TYPE = 2
474 AND OWNING_TP_TYPE = 2
475 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0 -- Bug 7681955
476 AND SUMMARIZED_FLAG = 'N';
477
478 l_stmt_count := 330;
479
480 IF(g_debug = 1) THEN
481 debug('MOQD count: ' || l_moq_count);
482 debug('MOQD_BACKUP count: ' || l_moq_backup_count);
483 END IF;
484
485 If l_moq_count <> l_moq_backup_count Then
486 IF(g_debug = 1) THEN
487 debug('MOQD and Backup Count are not same');
488 END IF;
489 RETCODE := 2;
490 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
491 -- Consolidation of Onhand Quantities failed.
492 ERRBUF := FND_MESSAGE.get;
493 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
494 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
495 ROLLBACK;
496 -- RETURN; /* Commented for bug 7588761 */
497 goto loop_end; /* Added for bug 7588761 */
498
499 END IF;
500 IF l_moq_count <> 0 THEN
501 l_stmt_count := 340;
502
503 --delete records from mtl_onhand_quantities_detail in that Org
504 /* Added inventory_item_id condition for bug 7588761 */
505 delete from mtl_onhand_quantities_detail moqd
506 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
507 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
508 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
509 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
510 AND PLANNING_TP_TYPE = 2
511 AND OWNING_TP_TYPE = 2
512 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0 -- Bug 7681955
513 AND ROWID IN (SELECT /*+ unnest */ ROW_ID FROM MTL_MOQD_BACKUP mmb
514 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
515 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
516 AND SUMMARIZED_FLAG = 'N');
517 /* Added hint for bug 12996651 */
518
519 IF(g_debug = 1) THEN
520 debug('Deleted '||SQL%ROWCOUNT||' rows from MOQD');
521 END IF;
522
523 l_stmt_count := 350;
524
525 --copy grouped records from mtl_moq_backup into mtl_onhand_quantities_detail
526 insert into mtl_onhand_quantities_detail(
527 INVENTORY_ITEM_ID
528 ,ORGANIZATION_ID
529 ,DATE_RECEIVED
530 ,LAST_UPDATE_DATE
531 ,LAST_UPDATED_BY
532 ,CREATION_DATE
533 ,CREATED_BY
534 ,LAST_UPDATE_LOGIN
535 ,PRIMARY_TRANSACTION_QUANTITY
536 ,SUBINVENTORY_CODE
537 ,REVISION
538 ,LOCATOR_ID
539 ,CREATE_TRANSACTION_ID
540 ,UPDATE_TRANSACTION_ID
541 ,LOT_NUMBER
542 ,ORIG_DATE_RECEIVED
543 ,COST_GROUP_ID
544 ,CONTAINERIZED_FLAG
545 ,PROJECT_ID
546 ,TASK_ID
547 ,ONHAND_QUANTITIES_ID
548 ,ORGANIZATION_TYPE
549 ,OWNING_ORGANIZATION_ID
550 ,OWNING_TP_TYPE
551 ,PLANNING_ORGANIZATION_ID
552 ,PLANNING_TP_TYPE
553 ,TRANSACTION_UOM_CODE
554 ,TRANSACTION_QUANTITY
555 ,SECONDARY_UOM_CODE
556 ,SECONDARY_TRANSACTION_QUANTITY
557 ,IS_CONSIGNED
558 ,STATUS_ID -- Bug 12776732
559 )
560 SELECT
561 INVENTORY_ITEM_ID
562 ,ORGANIZATION_ID
563 ,MIN(DATE_RECEIVED)
564 ,MIN(LAST_UPDATE_DATE)
565 ,MIN(LAST_UPDATED_BY)
566 ,MIN(CREATION_DATE)
567 ,MIN(CREATED_BY)
568 ,MIN(LAST_UPDATE_LOGIN)
569 ,ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5)
570 ,SUBINVENTORY_CODE
571 ,REVISION
572 ,LOCATOR_ID
573 ,MIN(CREATE_TRANSACTION_ID)
574 ,MAX(UPDATE_TRANSACTION_ID) --Capturing Max(update_transaction_id)
575 ,LOT_NUMBER
576 ,MIN(ORIG_DATE_RECEIVED)
577 ,COST_GROUP_ID
578 ,NVL(CONTAINERIZED_FLAG,2) CONTAINERIZED_FLAG
579 ,PROJECT_ID
580 ,TASK_ID
581 ,MIN(ONHAND_QUANTITIES_ID)
582 ,ORGANIZATION_TYPE
583 ,OWNING_ORGANIZATION_ID
584 ,OWNING_TP_TYPE
585 ,PLANNING_ORGANIZATION_ID
586 ,PLANNING_TP_TYPE
587 ,TRANSACTION_UOM_CODE
588 ,ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5)
589 ,SECONDARY_UOM_CODE
590 ,ROUND(SUM(SECONDARY_TRANSACTION_QUANTITY),5)
591 ,IS_CONSIGNED
592 ,STATUS_ID -- Bug 12776732
593 FROM MTL_MOQD_BACKUP
594 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
595 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
596 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
597 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
598 AND PLANNING_TP_TYPE = 2
599 AND OWNING_TP_TYPE = 2
600 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
601 AND SUMMARIZED_FLAG = 'N'
602 GROUP BY
603 INVENTORY_ITEM_ID,
604 ORGANIZATION_ID,
605 SUBINVENTORY_CODE,
606 REVISION,
607 LOCATOR_ID,
608 LOT_NUMBER,
609 COST_GROUP_ID,
610 PROJECT_ID,
611 TASK_ID,
612 STATUS_ID, -- Bug 12776732
613 NVL(CONTAINERIZED_FLAG,2),
614 ORGANIZATION_TYPE,
615 OWNING_ORGANIZATION_ID,
616 OWNING_TP_TYPE,
617 PLANNING_ORGANIZATION_ID,
618 PLANNING_TP_TYPE,
619 TRANSACTION_UOM_CODE,
620 SECONDARY_UOM_CODE,
621 IS_CONSIGNED
622 HAVING ( ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5) <> 0
623 OR ROUND(SUM(SECONDARY_TRANSACTION_QUANTITY),5) <> 0) ; -- Bug 7681955 Added OR secondary qty <> 0
624
625 IF(g_debug = 1) THEN
626 debug('Inserted '||SQL%ROWCOUNT||' rows into MOQD');
627 END IF;
628
629 l_stmt_count := 360;
630
631 --check sum to see if any quantities have been lost
632 /* Added inventory_item_id condition for bug 7588761 */
633 /* Bug 7681955 Added secondary qty check */
634 SELECT nvl(sum(primary_transaction_quantity) ,0), nvl(sum(secondary_transaction_quantity) ,0)
635 INTO l_moq_sum, l_moq_sec_sum
636 FROM mtl_onhand_quantities_detail
637 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
638 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
639 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
640 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
641 AND PLANNING_TP_TYPE = 2
642 AND OWNING_TP_TYPE = 2;
643 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0; -- Bug 7681955
644
645 l_stmt_count := 370;
646
647 /* Bug 7681955 Added secondary qty check */
648 SELECT nvl(sum(primary_transaction_quantity) ,0), nvl(sum(secondary_transaction_quantity) ,0)
649 INTO l_moq_backup_sum, l_moq_sec_backup_sum
650 FROM mtl_moqd_backup
651 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
652 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
653 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
654 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
655 AND PLANNING_TP_TYPE = 2
656 AND OWNING_TP_TYPE = 2
657 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0 -- Bug 7681955
658 AND SUMMARIZED_FLAG = 'N';
659
660 l_stmt_count := 380;
661
662 IF(g_debug = 1) THEN
663 debug('Sum of Pri Qty in MOQD : ' || l_moq_sum);
664 debug('Sum of Pri Qty in MOQD_BACKUP : ' || l_moq_backup_sum);
665 debug('Sum of Sec Qty in MOQD : ' || l_moq_sec_sum);
666 debug('Sum of Sec Qty in MOQD_BACKUP : ' || l_moq_sec_backup_sum);
667 END IF;
668
669 /* Bug 7681955 Added secondary qty check */
670 IF ( l_moq_sum <> l_moq_backup_sum OR l_moq_sec_sum <> l_moq_sec_backup_sum) Then
671 IF(g_debug = 1) THEN
672 debug('Sum of MOQ Qty and Backup Qty Count are not same');
673 END IF;
674 RETCODE := 2;
675 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
676 -- Consolidation of Onhand Quantities failed.
677 ERRBUF := FND_MESSAGE.get;
678 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
679 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
680 ROLLBACK;
681 -- RETURN; /* Commented for bug 7588761 */
682 goto loop_end; /* Added for bug 7588761 */
683 END IF;
684
685 l_stmt_count := 385;
686
687 -- Insert Summary records into mtl_moq_backup table capturing the user_id,
688 -- request_id and request run date.
689
690 /* Added inventory_item_id condition for bug 7588761 */
691 INSERT INTO MTL_MOQD_BACKUP(
692 INVENTORY_ITEM_ID
693 ,ORGANIZATION_ID
694 ,DATE_RECEIVED
695 ,LAST_UPDATE_DATE
696 ,LAST_UPDATED_BY
697 ,CREATION_DATE
698 ,CREATED_BY
699 ,LAST_UPDATE_LOGIN
700 ,PRIMARY_TRANSACTION_QUANTITY
701 ,SUBINVENTORY_CODE
702 ,REVISION
703 ,LOCATOR_ID
704 ,CREATE_TRANSACTION_ID
705 ,UPDATE_TRANSACTION_ID
706 ,LOT_NUMBER
707 ,ORIG_DATE_RECEIVED
708 ,COST_GROUP_ID
709 ,CONTAINERIZED_FLAG
710 ,PROJECT_ID
711 ,TASK_ID
712 ,ONHAND_QUANTITIES_ID
713 ,ORGANIZATION_TYPE
714 ,OWNING_ORGANIZATION_ID
715 ,OWNING_TP_TYPE
716 ,PLANNING_ORGANIZATION_ID
717 ,PLANNING_TP_TYPE
718 ,TRANSACTION_UOM_CODE
719 ,TRANSACTION_QUANTITY
720 ,SECONDARY_UOM_CODE
721 ,SECONDARY_TRANSACTION_QUANTITY
722 ,IS_CONSIGNED
723 ,SUMMARIZED_FLAG
724 ,REQUEST_ID
725 ,USER_ID
726 ,CONSOLIDATION_DATE
727 ,ROW_ID
728 ,STATUS_ID -- Bug 12776732
729 )
730 SELECT
731 INVENTORY_ITEM_ID
732 ,ORGANIZATION_ID
733 ,DATE_RECEIVED
734 ,LAST_UPDATE_DATE
735 ,LAST_UPDATED_BY
736 ,CREATION_DATE
737 ,CREATED_BY
738 ,LAST_UPDATE_LOGIN
739 ,PRIMARY_TRANSACTION_QUANTITY
740 ,SUBINVENTORY_CODE
741 ,REVISION
742 ,LOCATOR_ID
743 ,CREATE_TRANSACTION_ID
744 ,UPDATE_TRANSACTION_ID
745 ,LOT_NUMBER
746 ,ORIG_DATE_RECEIVED
747 ,COST_GROUP_ID
748 ,CONTAINERIZED_FLAG
749 ,PROJECT_ID
750 ,TASK_ID
751 ,ONHAND_QUANTITIES_ID
752 ,ORGANIZATION_TYPE
753 ,OWNING_ORGANIZATION_ID
754 ,OWNING_TP_TYPE
755 ,PLANNING_ORGANIZATION_ID
756 ,PLANNING_TP_TYPE
757 ,TRANSACTION_UOM_CODE
758 ,TRANSACTION_QUANTITY
759 ,SECONDARY_UOM_CODE
760 ,SECONDARY_TRANSACTION_QUANTITY
761 ,IS_CONSIGNED
762 ,'Y'
763 ,l_request_id
764 ,l_user_id
765 ,SYSDATE
766 ,ROWID
767 ,STATUS_ID -- Bug 12776732
768 FROM MTL_ONHAND_QUANTITIES_DETAIL
769 WHERE ORGANIZATION_ID = l_moqd.ORGANIZATION_ID
770 AND INVENTORY_ITEM_ID = l_moqd.INVENTORY_ITEM_ID
771 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
772 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
773 AND PLANNING_TP_TYPE = 2
774 AND OWNING_TP_TYPE = 2;
775 -- AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0; -- Bug 7681955
776
777 IF(g_debug = 1) THEN
778 debug('Inserted '||SQL%ROWCOUNT||' rows into MOQD_BACKUP as Summary Rows');
779 END IF;
780
781 l_stmt_count := 390;
782
783 -- Delete all non-summary records from mtl_moq_backup table
784 DELETE FROM MTL_MOQD_BACKUP
785 WHERE SUMMARIZED_FLAG = 'N';
786
787 IF(g_debug = 1) THEN
788 debug('Deleted '||SQL%ROWCOUNT||' Non-Summary rows from MOQD_BACKUP');
789 END IF;
790 END IF; --IF l_moq_count <> 0 THEN
791
792 /* Start of changes for bug 7588761 */
793 commit; --for each item
794
795 <<loop_end>>
796 -- we should release the lock handle at the end of the process
797 release_locks (
798 p_request_id =>l_request_id,
799 p_item_id =>l_moqd.INVENTORY_ITEM_ID,
800 p_org_id =>l_moqd.ORGANIZATION_ID,
801 x_err_code =>l_err_code,
802 x_err_msg =>l_err_msg
803 );
804 l_proc_cnt := l_proc_cnt + 1;
805
806 ELSIF l_err_code <> 0 THEN -- failed to lock this item
807
808 IF (g_debug = 1) THEN
809 debug('Not able to get lock on item_id '||l_moqd.INVENTORY_ITEM_ID );
810 END IF;
811 debug(l_err_msg);
812 -- not able to lock on item, release the lock requested.
813 release_locks (
814 p_request_id =>l_request_id,
815 p_item_id =>l_moqd.INVENTORY_ITEM_ID,
816 p_org_id =>l_moqd.ORGANIZATION_ID,
817 x_err_code =>l_err_code,
818 x_err_msg =>l_err_msg
819 );
820 END IF;
821 /* End of changes for bug 7588761 */
822
823 END LOOP; -- moqd_org_item loop
824
825 l_stmt_count := 400;
826
827 IF(g_debug = 1) THEN
828 debug('Completed Successfully, total number of item processed '||l_proc_cnt);
829 END IF;
830
831 RETCODE := 1;
832 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_SUCC');
833 -- Consolidation of Onhand Quantities completed successfully.
834 ERRBUF := FND_MESSAGE.get;
835 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',ERRBUF);
836
837 --COMMIT; /* Commented for bug 7588761 */
838 RETURN;
839
840 EXCEPTION
841 WHEN OTHERS then
842 IF(g_debug = 1) THEN
843 debug('Error during script, Statement = '||l_stmt_count);
844 debug('Rolling back... Error Message = ' ||SQLERRM);
845 END IF;
846 RETCODE := 2;
847 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
848 -- Consolidation of Onhand Quantities failed.
849 ERRBUF := FND_MESSAGE.get;
850 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
851 /* Added following if condition for bug 7588761 */
852 /* we should release any outstanding locks */
853 if g_lock_handle is not null then
854 l_err_code := dbms_lock.release(g_lock_handle);
855 end if;
856
857 ROLLBACK;
858 RETURN;
859 END;
860
861 END INV_REDUCE_MOQD_PVT;