[Home] [Help]
PACKAGE BODY: APPS.INV_REDUCE_MOQD_PVT
Source
1 PACKAGE BODY INV_REDUCE_MOQD_PVT AS
2 /* $Header: INVRMOQB.pls 120.2 2006/04/21 02:42:15 salagars noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_REDUCE_MOQD_PVT';
5
6 PROCEDURE debug(
7 p_message IN VARCHAR2
8 ) IS
9 BEGIN
10 inv_log_util.trace(p_message, G_PKG_NAME , 10 );
11 EXCEPTION
12 WHEN OTHERS THEN
13 NULL;
14 END debug;
15
16
17 PROCEDURE consolidate_moqd(
18 ERRBUF OUT NOCOPY VARCHAR2 ,
19 RETCODE OUT NOCOPY NUMBER ,
20 P_ORG_ID IN NUMBER
21 ) IS
22
23 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
24 l_profile VARCHAR2(1) := NVL(FND_PROFILE.VALUE('INV_FIFO_ORIG_REC_DATE'),'N');
25 l_user_id NUMBER := NVL(FND_PROFILE.VALUE('USER_ID'),-999);
26 l_request_id NUMBER := NVL(FND_PROFILE.VALUE('CONC_REQUEST_ID'),-999);
27
28 l_moq_count NUMBER;
29 l_moq_sum NUMBER;
30 l_moq_backup_count NUMBER;
31 l_moq_backup_sum NUMBER;
32
33 l_stmt_count NUMBER ;
34 l_return_status VARCHAR2(30);
35 l_conc_status BOOLEAN;
36 l_ret BOOLEAN;
37
38 l_org_id NUMBER;
39 l_org_code VARCHAR2(3);
40
41 l_prof_cnt NUMBER;
42 l_resource NUMBER;
43 RESOURCE_BUSY EXCEPTION;
44 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY ,-54);
45
46 CURSOR elig_orgs IS
47 SELECT mp.organization_id, mp.organization_code
48 FROM mtl_parameters mp
49 WHERE mp.organization_id = P_ORG_ID
50 AND NOT (mp.wms_enabled_flag = 'Y' OR mp.process_enabled_flag = 'Y');
51
52 CURSOR lock_moqd IS
53 SELECT ORGANIZATION_ID
54 FROM MTL_ONHAND_QUANTITIES_DETAIL
55 WHERE ORGANIZATION_ID = P_ORG_ID
56 FOR UPDATE NOWAIT;
57
58 BEGIN
59 RETCODE := 0;
60
61 l_stmt_count := 50;
62
63 IF P_ORG_ID IS NULL THEN
64 IF(l_debug = 1) THEN
65 debug('The parameter P_ORG_ID cannot be NULL');
66 END IF;
67 RETCODE := 2;
68 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
69 -- Consolidation of Onhand Quantities failed.
70 ERRBUF := FND_MESSAGE.get;
71 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
72 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
73 ROLLBACK;
74 RETURN;
75 END IF;
76
77 l_stmt_count := 100;
78
79 -- If fnd_profile returns 'N' for the profile, then make sure that this profile is not
80 -- set to 'Y' at any other levels before proceeding with onhand consolidation.
81
82 IF (l_profile = 'N') THEN
83
84 l_stmt_count := 110;
85
86 SELECT count(1) INTO l_prof_cnt
87 FROM fnd_profile_options o, fnd_profile_option_values v
88 WHERE o.profile_option_name = 'INV_FIFO_ORIG_REC_DATE'
89 AND o.start_date_active <= sysdate
90 AND (nvl(o.end_date_active,sysdate) >= sysdate)
91 AND o.profile_option_id = v.profile_option_id
92 AND o.application_id = v.application_id
93 AND nvl(v.profile_option_value,'N') = 'Y' ;
94
95 l_stmt_count := 120;
96
97 IF (l_prof_cnt > 0) THEN
98 IF(l_debug = 1) THEN
99 debug('The Profile INV: FIFO for Original Receipt Date is set to Yes at Appl/Resp/User Level');
100 END IF;
101 RETCODE := 2;
102 FND_MESSAGE.set_name('INV','INV_MOQD_FIFO_SET');
103 -- 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.
104 ERRBUF := FND_MESSAGE.get;
105 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
106 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
107 ROLLBACK;
108 RETURN;
109 END IF;
110 ELSE -- l_profile = 'Y'
111
112 l_stmt_count := 130;
113
114 -- The error message says it all.
115 IF(l_debug = 1) THEN
116 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');
117 END IF;
118 RETCODE := 2;
119 FND_MESSAGE.set_name('INV','INV_MOQD_FIFO_SET');
120 -- 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.
121 ERRBUF := FND_MESSAGE.get;
122 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
123 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
124 ROLLBACK;
125 RETURN;
126 END IF;
127
128 l_stmt_count := 150;
129 BEGIN
130
131 -- Lock all the rows in MOQD for that Org_id using lock_moqd cursor
132
133 BEGIN
134 OPEN elig_orgs;
135 FETCH elig_orgs INTO l_org_id, l_org_code;
136 IF elig_orgs%NOTFOUND THEN
137 IF(l_debug = 1) THEN
138 debug('This Organization is either WMS enabled or Process enabled');
139 END IF;
140 RETCODE := 2;
141 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
142 -- Consolidation of Onhand Quantities failed.
143 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
144 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
145 RETURN;
146 END IF;
147 CLOSE elig_orgs;
148 END;
149
150 OPEN lock_moqd;
151 CLOSE lock_moqd;
152
153 EXCEPTION
154 WHEN RESOURCE_BUSY THEN
155 IF(l_debug = 1) THEN
156 debug('Cannot obtain locks on MOQD');
157 END IF;
158
159 -- Just for pre-caution
160 IF (lock_moqd%ISOPEN) THEN
161 CLOSE lock_moqd;
162 END IF;
163
164 RETCODE := 2;
165 FND_MESSAGE.set_name('INV','INV_MOQD_CANNOT_LOCK');
166 -- The Onhand Quantities table cannot be locked for consolidation.
167 ERRBUF := FND_MESSAGE.get;
168 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
169 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
170 ROLLBACK;
171 RETURN;
172 END;
173
174 l_stmt_count := 200;
175 --delete non-summarized rows from mtl_moq_backup table
176 DELETE FROM mtl_moqd_backup
177 WHERE summarized_flag = 'N';
178
179 l_stmt_count := 300;
180
181 -- Go thru the loop for the eligible org
182 FOR elig_orgs_rec in elig_orgs LOOP
183
184 IF(l_debug = 1) THEN
185 debug('-----------------------------------------------');
186 debug('Running for Org - '||elig_orgs_rec.organization_code||', Id = '||elig_orgs_rec.organization_id);
187 END IF;
188
189 --copy records from mtl_onhand_quantities_detail into mtl_moq_backup table
190 INSERT INTO MTL_MOQD_BACKUP(
191 INVENTORY_ITEM_ID
192 ,ORGANIZATION_ID
193 ,DATE_RECEIVED
194 ,LAST_UPDATE_DATE
195 ,LAST_UPDATED_BY
196 ,CREATION_DATE
197 ,CREATED_BY
198 ,LAST_UPDATE_LOGIN
199 ,PRIMARY_TRANSACTION_QUANTITY
200 ,SUBINVENTORY_CODE
201 ,REVISION
202 ,LOCATOR_ID
203 ,CREATE_TRANSACTION_ID
204 ,UPDATE_TRANSACTION_ID
205 ,LOT_NUMBER
206 ,ORIG_DATE_RECEIVED
207 ,COST_GROUP_ID
208 ,CONTAINERIZED_FLAG
209 ,PROJECT_ID
210 ,TASK_ID
211 ,ONHAND_QUANTITIES_ID
212 ,ORGANIZATION_TYPE
213 ,OWNING_ORGANIZATION_ID
214 ,OWNING_TP_TYPE
215 ,PLANNING_ORGANIZATION_ID
216 ,PLANNING_TP_TYPE
217 ,TRANSACTION_UOM_CODE
218 ,TRANSACTION_QUANTITY
219 ,SECONDARY_UOM_CODE
220 ,SECONDARY_TRANSACTION_QUANTITY
221 ,IS_CONSIGNED
222 ,ROW_ID
223 ,SUMMARIZED_FLAG
224 )
225 SELECT
226 INVENTORY_ITEM_ID
227 ,ORGANIZATION_ID
228 ,DATE_RECEIVED
229 ,LAST_UPDATE_DATE
230 ,LAST_UPDATED_BY
231 ,CREATION_DATE
232 ,CREATED_BY
233 ,LAST_UPDATE_LOGIN
234 ,PRIMARY_TRANSACTION_QUANTITY
235 ,SUBINVENTORY_CODE
236 ,REVISION
237 ,LOCATOR_ID
238 ,CREATE_TRANSACTION_ID
239 ,UPDATE_TRANSACTION_ID
240 ,LOT_NUMBER
241 ,ORIG_DATE_RECEIVED
242 ,COST_GROUP_ID
243 ,CONTAINERIZED_FLAG
244 ,PROJECT_ID
245 ,TASK_ID
246 ,ONHAND_QUANTITIES_ID
247 ,ORGANIZATION_TYPE
248 ,OWNING_ORGANIZATION_ID
249 ,OWNING_TP_TYPE
250 ,PLANNING_ORGANIZATION_ID
251 ,PLANNING_TP_TYPE
252 ,TRANSACTION_UOM_CODE
253 ,TRANSACTION_QUANTITY
254 ,SECONDARY_UOM_CODE
255 ,SECONDARY_TRANSACTION_QUANTITY
256 ,IS_CONSIGNED
257 ,ROWID
258 ,'N'
259 FROM MTL_ONHAND_QUANTITIES_DETAIL
260 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
261 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
262 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
263 AND PLANNING_TP_TYPE = 2
264 AND OWNING_TP_TYPE = 2
265 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0;
266
267 IF(l_debug = 1) THEN
268 debug('Finished insert into MTL_MOQD_BACKUP');
269 END IF;
270
271 l_stmt_count := 310;
272
273 --check count(*) to make sure copy went okay
274 SELECT count(*)
275 INTO l_moq_count
276 FROM mtl_onhand_quantities_detail moqd
277 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
278 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
279 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
280 AND PLANNING_TP_TYPE = 2
281 AND OWNING_TP_TYPE = 2
282 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
283 AND ROWID IN (SELECT ROW_ID FROM MTL_MOQD_BACKUP mmb
284 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
285 AND SUMMARIZED_FLAG = 'N');
286
287 l_stmt_count := 320;
288
289 SELECT count(*)
290 INTO l_moq_backup_count
291 FROM mtl_moqd_backup
292 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
293 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
294 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
295 AND PLANNING_TP_TYPE = 2
296 AND OWNING_TP_TYPE = 2
297 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
298 AND SUMMARIZED_FLAG = 'N';
299
300 l_stmt_count := 330;
301
302 IF(l_debug = 1) THEN
303 debug('MOQD count: ' || l_moq_count);
304 debug('MOQD_BACKUP count: ' || l_moq_backup_count);
305 END IF;
306
307 If l_moq_count <> l_moq_backup_count Then
308 IF(l_debug = 1) THEN
309 debug('MOQD and Backup Count are not same');
310 END IF;
311 RETCODE := 2;
312 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
313 -- Consolidation of Onhand Quantities failed.
314 ERRBUF := FND_MESSAGE.get;
315 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
316 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
317 ROLLBACK;
318 RETURN;
319 END IF;
320
321 IF l_moq_count <> 0 THEN
322
323 l_stmt_count := 340;
324
325 --delete records from mtl_onhand_quantities_detail in that Org
326 delete from mtl_onhand_quantities_detail moqd
327 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
328 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
329 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
330 AND PLANNING_TP_TYPE = 2
331 AND OWNING_TP_TYPE = 2
332 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
333 AND ROWID IN (SELECT ROW_ID FROM MTL_MOQD_BACKUP mmb
334 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
335 AND SUMMARIZED_FLAG = 'N');
336
337 IF(l_debug = 1) THEN
338 debug('Deleted '||SQL%ROWCOUNT||' rows from MOQD');
339 END IF;
340
341 l_stmt_count := 350;
342
343 --copy grouped records from mtl_moq_backup into mtl_onhand_quantities_detail
344 insert into mtl_onhand_quantities_detail(
345 INVENTORY_ITEM_ID
346 ,ORGANIZATION_ID
347 ,DATE_RECEIVED
348 ,LAST_UPDATE_DATE
349 ,LAST_UPDATED_BY
350 ,CREATION_DATE
351 ,CREATED_BY
352 ,LAST_UPDATE_LOGIN
353 ,PRIMARY_TRANSACTION_QUANTITY
354 ,SUBINVENTORY_CODE
355 ,REVISION
356 ,LOCATOR_ID
357 ,CREATE_TRANSACTION_ID
358 ,UPDATE_TRANSACTION_ID
359 ,LOT_NUMBER
360 ,ORIG_DATE_RECEIVED
361 ,COST_GROUP_ID
362 ,CONTAINERIZED_FLAG
363 ,PROJECT_ID
364 ,TASK_ID
365 ,ONHAND_QUANTITIES_ID
366 ,ORGANIZATION_TYPE
367 ,OWNING_ORGANIZATION_ID
368 ,OWNING_TP_TYPE
369 ,PLANNING_ORGANIZATION_ID
370 ,PLANNING_TP_TYPE
371 ,TRANSACTION_UOM_CODE
372 ,TRANSACTION_QUANTITY
373 ,SECONDARY_UOM_CODE
374 ,SECONDARY_TRANSACTION_QUANTITY
375 ,IS_CONSIGNED
376 )
377 SELECT
378 INVENTORY_ITEM_ID
379 ,ORGANIZATION_ID
380 ,MIN(DATE_RECEIVED)
381 ,MIN(LAST_UPDATE_DATE)
382 ,MIN(LAST_UPDATED_BY)
383 ,MIN(CREATION_DATE)
384 ,MIN(CREATED_BY)
385 ,MIN(LAST_UPDATE_LOGIN)
386 ,ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5)
387 ,SUBINVENTORY_CODE
388 ,REVISION
389 ,LOCATOR_ID
390 ,MIN(CREATE_TRANSACTION_ID)
391 ,MAX(UPDATE_TRANSACTION_ID) --Capturing Max(update_transaction_id)
392 ,LOT_NUMBER
393 ,MIN(ORIG_DATE_RECEIVED)
394 ,COST_GROUP_ID
395 ,NVL(CONTAINERIZED_FLAG,2) CONTAINERIZED_FLAG
396 ,PROJECT_ID
397 ,TASK_ID
398 ,MIN(ONHAND_QUANTITIES_ID)
399 ,ORGANIZATION_TYPE
400 ,OWNING_ORGANIZATION_ID
401 ,OWNING_TP_TYPE
402 ,PLANNING_ORGANIZATION_ID
403 ,PLANNING_TP_TYPE
404 ,TRANSACTION_UOM_CODE
405 ,ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5)
406 ,SECONDARY_UOM_CODE
407 ,ROUND(SUM(SECONDARY_TRANSACTION_QUANTITY),5)
408 ,IS_CONSIGNED
409 FROM MTL_MOQD_BACKUP
410 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
411 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
412 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
413 AND PLANNING_TP_TYPE = 2
414 AND OWNING_TP_TYPE = 2
415 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
416 AND SUMMARIZED_FLAG = 'N'
417 GROUP BY
418 INVENTORY_ITEM_ID,
419 ORGANIZATION_ID,
420 SUBINVENTORY_CODE,
421 REVISION,
422 LOCATOR_ID,
423 LOT_NUMBER,
424 COST_GROUP_ID,
425 PROJECT_ID,
426 TASK_ID,
427 NVL(CONTAINERIZED_FLAG,2),
428 ORGANIZATION_TYPE,
429 OWNING_ORGANIZATION_ID,
430 OWNING_TP_TYPE,
431 PLANNING_ORGANIZATION_ID,
432 PLANNING_TP_TYPE,
433 TRANSACTION_UOM_CODE,
434 SECONDARY_UOM_CODE,
435 IS_CONSIGNED
436 HAVING ROUND(SUM(PRIMARY_TRANSACTION_QUANTITY),5) <> 0 ;
437
438 IF(l_debug = 1) THEN
439 debug('Inserted '||SQL%ROWCOUNT||' rows into MOQD');
440 END IF;
441
442 l_stmt_count := 360;
443
444 --check sum to see if any quantities have been lost
445
446 SELECT nvl(sum(primary_transaction_quantity) ,0)
447 INTO l_moq_sum
448 FROM mtl_onhand_quantities_detail
449 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
450 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
451 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
452 AND PLANNING_TP_TYPE = 2
453 AND OWNING_TP_TYPE = 2
454 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0;
455
456 l_stmt_count := 370;
457
458 SELECT nvl(sum(primary_transaction_quantity) ,0)
459 INTO l_moq_backup_sum
460 FROM mtl_moqd_backup
461 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
462 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
463 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
464 AND PLANNING_TP_TYPE = 2
465 AND OWNING_TP_TYPE = 2
466 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0
467 AND SUMMARIZED_FLAG = 'N';
468
469 l_stmt_count := 380;
470
471 IF(l_debug = 1) THEN
472 debug('Sum of Pri Qty in MOQD : ' || l_moq_sum);
473 debug('Sum of Pri Qty in MOQD_BACKUP : ' || l_moq_backup_sum);
474 END IF;
475
476 IF l_moq_sum <> l_moq_backup_sum Then
477 IF(l_debug = 1) THEN
478 debug('Sum of MOQ Qty and Backup Qty Count are not same');
479 END IF;
480 RETCODE := 2;
481 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
482 -- Consolidation of Onhand Quantities failed.
483 ERRBUF := FND_MESSAGE.get;
484 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
485 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
486 ROLLBACK;
487 RETURN;
488 END IF;
489
490 l_stmt_count := 385;
491
492 -- Insert Summary records into mtl_moq_backup table capturing the user_id, request_id and request run date.
493 INSERT INTO MTL_MOQD_BACKUP(
494 INVENTORY_ITEM_ID
495 ,ORGANIZATION_ID
496 ,DATE_RECEIVED
497 ,LAST_UPDATE_DATE
498 ,LAST_UPDATED_BY
499 ,CREATION_DATE
500 ,CREATED_BY
501 ,LAST_UPDATE_LOGIN
502 ,PRIMARY_TRANSACTION_QUANTITY
503 ,SUBINVENTORY_CODE
504 ,REVISION
505 ,LOCATOR_ID
506 ,CREATE_TRANSACTION_ID
507 ,UPDATE_TRANSACTION_ID
508 ,LOT_NUMBER
509 ,ORIG_DATE_RECEIVED
510 ,COST_GROUP_ID
511 ,CONTAINERIZED_FLAG
512 ,PROJECT_ID
513 ,TASK_ID
514 ,ONHAND_QUANTITIES_ID
515 ,ORGANIZATION_TYPE
516 ,OWNING_ORGANIZATION_ID
517 ,OWNING_TP_TYPE
518 ,PLANNING_ORGANIZATION_ID
519 ,PLANNING_TP_TYPE
520 ,TRANSACTION_UOM_CODE
521 ,TRANSACTION_QUANTITY
522 ,SECONDARY_UOM_CODE
523 ,SECONDARY_TRANSACTION_QUANTITY
524 ,IS_CONSIGNED
525 ,SUMMARIZED_FLAG
526 ,REQUEST_ID
527 ,USER_ID
528 ,CONSOLIDATION_DATE
529 ,ROW_ID
530 )
531 SELECT
532 INVENTORY_ITEM_ID
533 ,ORGANIZATION_ID
534 ,DATE_RECEIVED
535 ,LAST_UPDATE_DATE
536 ,LAST_UPDATED_BY
537 ,CREATION_DATE
538 ,CREATED_BY
539 ,LAST_UPDATE_LOGIN
540 ,PRIMARY_TRANSACTION_QUANTITY
541 ,SUBINVENTORY_CODE
542 ,REVISION
543 ,LOCATOR_ID
544 ,CREATE_TRANSACTION_ID
545 ,UPDATE_TRANSACTION_ID
546 ,LOT_NUMBER
547 ,ORIG_DATE_RECEIVED
548 ,COST_GROUP_ID
549 ,CONTAINERIZED_FLAG
550 ,PROJECT_ID
551 ,TASK_ID
552 ,ONHAND_QUANTITIES_ID
553 ,ORGANIZATION_TYPE
554 ,OWNING_ORGANIZATION_ID
555 ,OWNING_TP_TYPE
556 ,PLANNING_ORGANIZATION_ID
557 ,PLANNING_TP_TYPE
558 ,TRANSACTION_UOM_CODE
559 ,TRANSACTION_QUANTITY
560 ,SECONDARY_UOM_CODE
561 ,SECONDARY_TRANSACTION_QUANTITY
562 ,IS_CONSIGNED
563 ,'Y'
564 ,l_request_id
565 ,l_user_id
566 ,SYSDATE
567 ,ROWID
568 FROM MTL_ONHAND_QUANTITIES_DETAIL
569 WHERE ORGANIZATION_ID = elig_orgs_rec.ORGANIZATION_ID
570 AND PLANNING_ORGANIZATION_ID = ORGANIZATION_ID
571 AND OWNING_ORGANIZATION_ID = ORGANIZATION_ID
572 AND PLANNING_TP_TYPE = 2
573 AND OWNING_TP_TYPE = 2
574 AND NVL(SECONDARY_TRANSACTION_QUANTITY,0) = 0;
575
576 IF(l_debug = 1) THEN
577 debug('Inserted '||SQL%ROWCOUNT||' rows into MOQD_BACKUP as Summary Rows');
578 END IF;
579
580 l_stmt_count := 390;
581
582 -- Delete all non-summary records from mtl_moq_backup table
583 DELETE FROM MTL_MOQD_BACKUP
584 WHERE SUMMARIZED_FLAG = 'N';
585
586 IF(l_debug = 1) THEN
587 debug('Deleted '||SQL%ROWCOUNT||' Non-Summary rows from MOQD_BACKUP');
588 END IF;
589
590 END IF; --IF l_moq_count <> 0 THEN
591
592 END LOOP;
593
594 l_stmt_count := 400;
595
596 IF(l_debug = 1) THEN
597 debug('Completed Successfully');
598 END IF;
599
600 RETCODE := 1;
601 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_SUCC');
602 -- Consolidation of Onhand Quantities completed successfully.
603 ERRBUF := FND_MESSAGE.get;
604 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',ERRBUF);
605
606 COMMIT;
607 RETURN;
608
609 EXCEPTION
610 WHEN OTHERS then
611 IF(l_debug = 1) THEN
612 debug('Error during script, Statement = '||l_stmt_count);
613 debug('Rolling back... Error Message = ' ||SQLERRM);
614 END IF;
615 RETCODE := 2;
616 FND_MESSAGE.set_name('INV','INV_MOQD_REQ_ERR');
617 -- Consolidation of Onhand Quantities failed.
618 ERRBUF := FND_MESSAGE.get;
619 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',ERRBUF);
620 ROLLBACK;
621 RETURN;
622 END;
623
624 END INV_REDUCE_MOQD_PVT;