[Home] [Help]
PACKAGE BODY: APPS.MRPP_CMERGE_FCST
Source
1 PACKAGE BODY MRPP_CMERGE_FCST as
2 /* $Header: MRPPMGFB.pls 120.0 2005/05/25 03:55:15 appldev noship $ */
3
4 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
5
6 /*-------------------------------------------------------------
7 |
8 | PROCEDURE
9 | MRP_FD
10 | DESCRIPTION :
11 | Account merge procedure for the table, MRP_FORECAST_DATES
12 |
13 | NOTES:
14 |
15 |--------------------------------------------------------------*/
16
17 PROCEDURE MRP_FD (
18 req_id NUMBER,
19 set_num NUMBER,
20 process_mode VARCHAR2) IS
21
22 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
23 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
24 INDEX BY BINARY_INTEGER;
25 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
26
27 TYPE TRANSACTION_ID_LIST_TYPE IS TABLE OF
28 MRP_FORECAST_DATES.TRANSACTION_ID%TYPE
29 INDEX BY BINARY_INTEGER;
30 PRIMARY_KEY_ID_LIST TRANSACTION_ID_LIST_TYPE;
31
32 TYPE customer_id_LIST_TYPE IS TABLE OF
33 MRP_FORECAST_DATES.customer_id%TYPE
34 INDEX BY BINARY_INTEGER;
35 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
36 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
37
38 TYPE ship_id_LIST_TYPE IS TABLE OF
39 MRP_FORECAST_DATES.ship_id%TYPE
40 INDEX BY BINARY_INTEGER;
41 NUM_COL2_ORIG_LIST ship_id_LIST_TYPE;
42 NUM_COL2_NEW_LIST ship_id_LIST_TYPE;
43
44 TYPE bill_id_LIST_TYPE IS TABLE OF
45 MRP_FORECAST_DATES.bill_id%TYPE
46 INDEX BY BINARY_INTEGER;
47 NUM_COL3_ORIG_LIST bill_id_LIST_TYPE;
48 NUM_COL3_NEW_LIST bill_id_LIST_TYPE;
49
50 l_profile_val VARCHAR2(30);
51 CURSOR merged_records IS
52 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
53 ,yt.TRANSACTION_ID
54 ,yt.customer_id
55 ,yt.ship_id
56 ,yt.bill_id
57 FROM MRP_FORECAST_DATES yt, ra_customer_merges m
58 WHERE (
59 yt.customer_id = m.DUPLICATE_ID
60 OR yt.ship_id = m.DUPLICATE_SITE_ID
61 OR yt.bill_id = m.DUPLICATE_SITE_ID
62 ) AND m.process_flag = 'N'
63 AND yt.origination_type = '10' /* Overconsumption */
64 AND m.request_id = req_id
65 AND m.set_number = set_num;
66 l_last_fetch BOOLEAN := FALSE;
67 l_count NUMBER;
68 BEGIN
69 IF process_mode='LOCK' THEN
70 NULL;
71 ELSE
72 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
73 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MRP_FORECAST_DATES',FALSE);
74 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
75 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
76
77 open merged_records;
78 LOOP
79 FETCH merged_records BULK COLLECT INTO
80 MERGE_HEADER_ID_LIST
81 , PRIMARY_KEY_ID_LIST
82 , NUM_COL1_ORIG_LIST
83 , NUM_COL2_ORIG_LIST
84 , NUM_COL3_ORIG_LIST
85 limit 1000
86 ;
87 IF merged_records%NOTFOUND THEN
88 l_last_fetch := TRUE;
89 END IF;
90 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
91 exit;
92 END IF;
93 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
94 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
95 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
96 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
97 END LOOP;
98 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
99 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
100 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
101 MERGE_LOG_ID,
102 TABLE_NAME,
103 MERGE_HEADER_ID,
104 PRIMARY_KEY_ID,
105 NUM_COL1_ORIG,
106 NUM_COL1_NEW,
107 NUM_COL2_ORIG,
108 NUM_COL2_NEW,
109 NUM_COL3_ORIG,
110 NUM_COL3_NEW,
111 ACTION_FLAG,
112 REQUEST_ID,
113 CREATED_BY,
114 CREATION_DATE,
115 LAST_UPDATE_LOGIN,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY
118 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
119 'MRP_FORECAST_DATES',
120 MERGE_HEADER_ID_LIST(I),
121 PRIMARY_KEY_ID_LIST(I),
122 NUM_COL1_ORIG_LIST(I),
123 NUM_COL1_NEW_LIST(I),
124 NUM_COL2_ORIG_LIST(I),
125 NUM_COL2_NEW_LIST(I),
126 NUM_COL3_ORIG_LIST(I),
127 NUM_COL3_NEW_LIST(I),
128 'U',
129 req_id,
130 hz_utility_pub.CREATED_BY,
131 hz_utility_pub.CREATION_DATE,
132 hz_utility_pub.LAST_UPDATE_LOGIN,
133 hz_utility_pub.LAST_UPDATE_DATE,
134 hz_utility_pub.LAST_UPDATED_BY
135 );
136
137 END IF;
138 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
139 UPDATE MRP_FORECAST_DATES yt SET
140 customer_id=NUM_COL1_NEW_LIST(I)
141 ,ship_id=NUM_COL2_NEW_LIST(I)
142 ,bill_id=NUM_COL3_NEW_LIST(I)
143 , LAST_UPDATE_DATE=SYSDATE
144 , last_updated_by=arp_standard.profile.user_id
145 , last_update_login=arp_standard.profile.last_update_login
146 , REQUEST_ID=req_id
147 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
148 , PROGRAM_ID=arp_standard.profile.program_id
149 , PROGRAM_UPDATE_DATE=SYSDATE
150 WHERE TRANSACTION_ID=PRIMARY_KEY_ID_LIST(I)
151 ;
152 l_count := l_count + SQL%ROWCOUNT;
153 IF l_last_fetch THEN
154 EXIT;
155 END IF;
156 END LOOP;
157
158 arp_message.set_name('AR','AR_ROWS_UPDATED');
159 arp_message.set_token('NUM_ROWS',to_char(l_count));
160 END IF;
161 EXCEPTION
162 WHEN OTHERS THEN
163 arp_message.set_line( 'MRP_FD');
164 RAISE;
165 END MRP_FD;
166
167
168 /*-------------------------------------------------------------
169 |
170 | PROCEDURE
171 | MRP_FDE
172 | DESCRIPTION :
173 | Account merge procedure for the table, MRP_FORECAST_DESIGNATORS
174 |
175 | NOTES:
176 |--------------------------------------------------------------*/
177
178 PROCEDURE MRP_FDE (
179 req_id NUMBER,
180 set_num NUMBER,
181 process_mode VARCHAR2) IS
182
183 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
184 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
185 INDEX BY BINARY_INTEGER;
186 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
187
188 TYPE ORGANIZATION_ID_LIST_TYPE IS TABLE OF
189 MRP_FORECAST_DESIGNATORS.ORGANIZATION_ID%TYPE
190 INDEX BY BINARY_INTEGER;
191 PRIMARY_KEY1_LIST ORGANIZATION_ID_LIST_TYPE;
192
193 TYPE FORECAST_DESIGNATOR_LIST_TYPE IS TABLE OF
194 MRP_FORECAST_DESIGNATORS.FORECAST_DESIGNATOR%TYPE
195 INDEX BY BINARY_INTEGER;
196 PRIMARY_KEY2_LIST FORECAST_DESIGNATOR_LIST_TYPE;
197
198 TYPE customer_id_LIST_TYPE IS TABLE OF
199 MRP_FORECAST_DESIGNATORS.customer_id%TYPE
200 INDEX BY BINARY_INTEGER;
201 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
202 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
203
204 TYPE ship_id_LIST_TYPE IS TABLE OF
205 MRP_FORECAST_DESIGNATORS.ship_id%TYPE
206 INDEX BY BINARY_INTEGER;
207 NUM_COL2_ORIG_LIST ship_id_LIST_TYPE;
208 NUM_COL2_NEW_LIST ship_id_LIST_TYPE;
209
210 TYPE bill_id_LIST_TYPE IS TABLE OF
211 MRP_FORECAST_DESIGNATORS.bill_id%TYPE
212 INDEX BY BINARY_INTEGER;
213 NUM_COL3_ORIG_LIST bill_id_LIST_TYPE;
214 NUM_COL3_NEW_LIST bill_id_LIST_TYPE;
215
216 l_profile_val VARCHAR2(30);
217 CURSOR merged_records IS
218 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
219 ,yt.ORGANIZATION_ID
220 ,yt.FORECAST_DESIGNATOR
221 ,yt.customer_id
222 ,yt.ship_id
223 ,yt.bill_id
224 FROM MRP_FORECAST_DESIGNATORS yt, ra_customer_merges m
225 WHERE (
226 yt.customer_id = m.DUPLICATE_ID
227 OR yt.ship_id = m.DUPLICATE_SITE_ID
228 OR yt.bill_id = m.DUPLICATE_SITE_ID
229 ) AND m.process_flag = 'N'
230 AND m.request_id = req_id
231 AND m.set_number = set_num;
232 l_last_fetch BOOLEAN := FALSE;
233 l_count NUMBER;
234 BEGIN
235 IF process_mode='LOCK' THEN
236 NULL;
237 ELSE
238 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
239 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MRP_FORECAST_DESIGNATORS',FALSE);
240 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
241 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
242
243 open merged_records;
244 LOOP
245 FETCH merged_records BULK COLLECT INTO
246 MERGE_HEADER_ID_LIST
247 , PRIMARY_KEY1_LIST
248 , PRIMARY_KEY2_LIST
249 , NUM_COL1_ORIG_LIST
250 , NUM_COL2_ORIG_LIST
251 , NUM_COL3_ORIG_LIST
252 limit 1000
253 ;
254 IF merged_records%NOTFOUND THEN
255 l_last_fetch := TRUE;
256 END IF;
257 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
258 exit;
259 END IF;
260 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
261 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
262 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
263 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
264 END LOOP;
265 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
266 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
267 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
268 MERGE_LOG_ID,
269 TABLE_NAME,
270 MERGE_HEADER_ID,
271 PRIMARY_KEY1,
272 PRIMARY_KEY2,
273 NUM_COL1_ORIG,
274 NUM_COL1_NEW,
275 NUM_COL2_ORIG,
276 NUM_COL2_NEW,
277 NUM_COL3_ORIG,
278 NUM_COL3_NEW,
279 ACTION_FLAG,
280 REQUEST_ID,
281 CREATED_BY,
282 CREATION_DATE,
283 LAST_UPDATE_LOGIN,
284 LAST_UPDATE_DATE,
285 LAST_UPDATED_BY
286 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
287 'MRP_FORECAST_DESIGNATORS',
288 MERGE_HEADER_ID_LIST(I),
289 PRIMARY_KEY1_LIST(I),
290 PRIMARY_KEY2_LIST(I),
291 NUM_COL1_ORIG_LIST(I),
292 NUM_COL1_NEW_LIST(I),
293 NUM_COL2_ORIG_LIST(I),
294 NUM_COL2_NEW_LIST(I),
295 NUM_COL3_ORIG_LIST(I),
296 NUM_COL3_NEW_LIST(I),
297 'U',
298 req_id,
299 hz_utility_pub.CREATED_BY,
300 hz_utility_pub.CREATION_DATE,
301 hz_utility_pub.LAST_UPDATE_LOGIN,
302 hz_utility_pub.LAST_UPDATE_DATE,
303 hz_utility_pub.LAST_UPDATED_BY
304 );
305
306 END IF;
307 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
308 UPDATE MRP_FORECAST_DESIGNATORS yt SET
309 customer_id=NUM_COL1_NEW_LIST(I)
310 ,ship_id=NUM_COL2_NEW_LIST(I)
311 ,bill_id=NUM_COL3_NEW_LIST(I)
312 , LAST_UPDATE_DATE=SYSDATE
313 , last_updated_by=arp_standard.profile.user_id
314 , last_update_login=arp_standard.profile.last_update_login
315 , REQUEST_ID=req_id
316 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
317 , PROGRAM_ID=arp_standard.profile.program_id
318 , PROGRAM_UPDATE_DATE=SYSDATE
319 WHERE ORGANIZATION_ID=PRIMARY_KEY1_LIST(I)
320 AND FORECAST_DESIGNATOR=PRIMARY_KEY2_LIST(I)
321 ;
322 l_count := l_count + SQL%ROWCOUNT;
323 IF l_last_fetch THEN
324 EXIT;
325 END IF;
326 END LOOP;
327
328 arp_message.set_name('AR','AR_ROWS_UPDATED');
329 arp_message.set_token('NUM_ROWS',to_char(l_count));
330 END IF;
331 EXCEPTION
332 WHEN OTHERS THEN
333 arp_message.set_line( 'MRP_FDE');
334 RAISE;
335 END MRP_FDE;
336
337
338 /*-------------------------------------------------------------
339 |
340 | PROCEDURE
341 | MRP_FU
342 | DESCRIPTION :
343 | Account merge procedure for the table, MRP_FORECAST_UPDATES
344 |
345 | NOTES:
346 |
347 |--------------------------------------------------------------*/
348
349 PROCEDURE MRP_FU (
350 req_id NUMBER,
351 set_num NUMBER,
352 process_mode VARCHAR2) IS
353
354 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
355 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
356 INDEX BY BINARY_INTEGER;
357 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
358
359 TYPE TRANSACTION_ID_LIST_TYPE IS TABLE OF
360 MRP_FORECAST_UPDATES.TRANSACTION_ID%TYPE
361 INDEX BY BINARY_INTEGER;
362 PRIMARY_KEY_ID_LIST TRANSACTION_ID_LIST_TYPE;
363
364 TYPE customer_id_LIST_TYPE IS TABLE OF
365 MRP_FORECAST_UPDATES.customer_id%TYPE
366 INDEX BY BINARY_INTEGER;
367 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
368 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
369
370 TYPE ship_id_LIST_TYPE IS TABLE OF
371 MRP_FORECAST_UPDATES.ship_id%TYPE
372 INDEX BY BINARY_INTEGER;
373 NUM_COL2_ORIG_LIST ship_id_LIST_TYPE;
374 NUM_COL2_NEW_LIST ship_id_LIST_TYPE;
375
376 TYPE bill_id_LIST_TYPE IS TABLE OF
377 MRP_FORECAST_UPDATES.bill_id%TYPE
378 INDEX BY BINARY_INTEGER;
379 NUM_COL3_ORIG_LIST bill_id_LIST_TYPE;
380 NUM_COL3_NEW_LIST bill_id_LIST_TYPE;
381
382 l_profile_val VARCHAR2(30);
383 CURSOR merged_records IS
384 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
385 ,yt.TRANSACTION_ID
386 ,yt.customer_id
387 ,yt.ship_id
388 ,yt.bill_id
389 FROM MRP_FORECAST_UPDATES yt, ra_customer_merges m
390 WHERE (
391 yt.customer_id = m.DUPLICATE_ID
392 OR yt.ship_id = m.DUPLICATE_SITE_ID
393 OR yt.bill_id = m.DUPLICATE_SITE_ID
394 ) AND m.process_flag = 'N'
395 AND m.request_id = req_id
396 AND m.set_number = set_num;
397 l_last_fetch BOOLEAN := FALSE;
398 l_count NUMBER;
399 BEGIN
400 IF process_mode='LOCK' THEN
401 NULL;
402 ELSE
403 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
404 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MRP_FORECAST_UPDATES',FALSE);
405 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
406 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
407
408 open merged_records;
409 LOOP
410 FETCH merged_records BULK COLLECT INTO
411 MERGE_HEADER_ID_LIST
412 , PRIMARY_KEY_ID_LIST
413 , NUM_COL1_ORIG_LIST
414 , NUM_COL2_ORIG_LIST
415 , NUM_COL3_ORIG_LIST
416 limit 1000
417 ;
418 IF merged_records%NOTFOUND THEN
419 l_last_fetch := TRUE;
420 END IF;
421 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
422 exit;
423 END IF;
424 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
425 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
426 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
427 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
428 END LOOP;
429 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
430 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
431 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
432 MERGE_LOG_ID,
433 TABLE_NAME,
434 MERGE_HEADER_ID,
435 PRIMARY_KEY_ID,
436 NUM_COL1_ORIG,
437 NUM_COL1_NEW,
438 NUM_COL2_ORIG,
439 NUM_COL2_NEW,
440 NUM_COL3_ORIG,
441 NUM_COL3_NEW,
442 ACTION_FLAG,
443 REQUEST_ID,
444 CREATED_BY,
445 CREATION_DATE,
446 LAST_UPDATE_LOGIN,
447 LAST_UPDATE_DATE,
448 LAST_UPDATED_BY
449 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
450 'MRP_FORECAST_UPDATES',
451 MERGE_HEADER_ID_LIST(I),
452 PRIMARY_KEY_ID_LIST(I),
453 NUM_COL1_ORIG_LIST(I),
454 NUM_COL1_NEW_LIST(I),
455 NUM_COL2_ORIG_LIST(I),
456 NUM_COL2_NEW_LIST(I),
457 NUM_COL3_ORIG_LIST(I),
458 NUM_COL3_NEW_LIST(I),
459 'U',
460 req_id,
461 hz_utility_pub.CREATED_BY,
462 hz_utility_pub.CREATION_DATE,
463 hz_utility_pub.LAST_UPDATE_LOGIN,
464 hz_utility_pub.LAST_UPDATE_DATE,
465 hz_utility_pub.LAST_UPDATED_BY
466 );
467
468 END IF;
469 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
470 UPDATE MRP_FORECAST_UPDATES yt SET
471 customer_id=NUM_COL1_NEW_LIST(I)
472 ,ship_id=NUM_COL2_NEW_LIST(I)
473 ,bill_id=NUM_COL3_NEW_LIST(I)
474 , LAST_UPDATE_DATE=SYSDATE
475 , last_updated_by=arp_standard.profile.user_id
476 , last_update_login=arp_standard.profile.last_update_login
477 , REQUEST_ID=req_id
478 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
479 , PROGRAM_ID=arp_standard.profile.program_id
480 , PROGRAM_UPDATE_DATE=SYSDATE
481 WHERE TRANSACTION_ID=PRIMARY_KEY_ID_LIST(I)
482 AND CUSTOMER_ID=NUM_COL1_ORIG_LIST(I)
483 AND NVL(SHIP_ID,-23453)=NVL(NUM_COL2_ORIG_LIST(I),-23453)
484 AND NVL(BILL_ID,-23453)=NVL(NUM_COL3_ORIG_LIST(I),-23453)
485 ;
486 l_count := l_count + SQL%ROWCOUNT;
487 IF l_last_fetch THEN
488 EXIT;
489 END IF;
490 END LOOP;
491
492 arp_message.set_name('AR','AR_ROWS_UPDATED');
493 arp_message.set_token('NUM_ROWS',to_char(l_count));
494 END IF;
495 EXCEPTION
496 WHEN OTHERS THEN
497 arp_message.set_line( 'MRP_FU');
498 RAISE;
499 END MRP_FU;
500
501
502 /*-------------------------------------------------------------
503 |
504 | PROCEDURE
505 | MRP_SOU
506 | DESCRIPTION :
507 | Account merge procedure for the table, MRP_SALES_ORDER_UPDATES
508 |
509 |
510 |--------------------------------------------------------------*/
511
512 PROCEDURE MRP_SOU (
513 req_id NUMBER,
514 set_num NUMBER,
515 process_mode VARCHAR2) IS
516
517 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
518 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
519 INDEX BY BINARY_INTEGER;
520 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
521
522 TYPE UPDATE_SEQ_NUM_LIST_TYPE IS TABLE OF
523 MRP_SALES_ORDER_UPDATES.UPDATE_SEQ_NUM%TYPE
524 INDEX BY BINARY_INTEGER;
525 PRIMARY_KEY_ID_LIST UPDATE_SEQ_NUM_LIST_TYPE;
526
527 TYPE current_customer_id_LIST_TYPE IS TABLE OF
528 MRP_SALES_ORDER_UPDATES.current_customer_id%TYPE
529 INDEX BY BINARY_INTEGER;
530 NUM_COL1_ORIG_LIST current_customer_id_LIST_TYPE;
531 NUM_COL1_NEW_LIST current_customer_id_LIST_TYPE;
532
533 TYPE current_ship_id_LIST_TYPE IS TABLE OF
534 MRP_SALES_ORDER_UPDATES.current_ship_id%TYPE
535 INDEX BY BINARY_INTEGER;
536 NUM_COL2_ORIG_LIST current_ship_id_LIST_TYPE;
537 NUM_COL2_NEW_LIST current_ship_id_LIST_TYPE;
538
539 TYPE current_bill_id_LIST_TYPE IS TABLE OF
540 MRP_SALES_ORDER_UPDATES.current_bill_id%TYPE
541 INDEX BY BINARY_INTEGER;
542 NUM_COL3_ORIG_LIST current_bill_id_LIST_TYPE;
543 NUM_COL3_NEW_LIST current_bill_id_LIST_TYPE;
544
545 TYPE previous_customer_id_LIST_TYPE IS TABLE OF
546 MRP_SALES_ORDER_UPDATES.previous_customer_id%TYPE
547 INDEX BY BINARY_INTEGER;
548 NUM_COL4_ORIG_LIST previous_customer_id_LIST_TYPE;
549 NUM_COL4_NEW_LIST previous_customer_id_LIST_TYPE;
550
551 TYPE previous_bill_id_LIST_TYPE IS TABLE OF
552 MRP_SALES_ORDER_UPDATES.previous_bill_id%TYPE
553 INDEX BY BINARY_INTEGER;
554 NUM_COL5_ORIG_LIST previous_bill_id_LIST_TYPE;
555 NUM_COL5_NEW_LIST previous_bill_id_LIST_TYPE;
556
557 TYPE previous_ship_id_LIST_TYPE IS TABLE OF
558 MRP_SALES_ORDER_UPDATES.previous_ship_id%TYPE
559 INDEX BY BINARY_INTEGER;
560 NUM_COL6_ORIG_LIST previous_ship_id_LIST_TYPE;
561 NUM_COL6_NEW_LIST previous_ship_id_LIST_TYPE;
562
563 l_profile_val VARCHAR2(30);
564 CURSOR merged_records IS
565 SELECT distinct CUSTOMER_MERGE_HEADER_ID
566 ,UPDATE_SEQ_NUM
567 ,current_customer_id
568 ,current_ship_id
569 ,current_bill_id
570 ,previous_customer_id
571 ,previous_bill_id
572 ,previous_ship_id
573 FROM MRP_SALES_ORDER_UPDATES yt, ra_customer_merges m
574 WHERE (
575 yt.current_customer_id = m.DUPLICATE_ID
576 OR yt.current_ship_id = m.DUPLICATE_SITE_ID
577 OR yt.current_bill_id = m.DUPLICATE_SITE_ID
578 OR yt.previous_customer_id = m.DUPLICATE_ID
579 OR yt.previous_bill_id = m.DUPLICATE_SITE_ID
580 OR yt.previous_ship_id = m.DUPLICATE_SITE_ID
581 ) AND m.process_flag = 'N'
582 AND m.request_id = req_id
583 AND m.set_number = set_num;
584 l_last_fetch BOOLEAN := FALSE;
585 l_count NUMBER;
586 BEGIN
587 IF process_mode='LOCK' THEN
588 NULL;
589 ELSE
590 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
591 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MRP_SALES_ORDER_UPDATES',FALSE);
592 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
593 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
594
595 open merged_records;
596 LOOP
597 FETCH merged_records BULK COLLECT INTO
598 MERGE_HEADER_ID_LIST
599 , PRIMARY_KEY_ID_LIST
600 , NUM_COL1_ORIG_LIST
601 , NUM_COL2_ORIG_LIST
602 , NUM_COL3_ORIG_LIST
603 , NUM_COL4_ORIG_LIST
604 , NUM_COL5_ORIG_LIST
605 , NUM_COL6_ORIG_LIST
606 limit 1000
607 ;
608 IF merged_records%NOTFOUND THEN
609 l_last_fetch := TRUE;
610 END IF;
611 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
612 exit;
613 END IF;
614 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
615 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
616 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
617 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
618 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL4_ORIG_LIST(I));
619 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL5_ORIG_LIST(I));
620 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL6_ORIG_LIST(I));
621 END LOOP;
622 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
623 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
624 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
625 MERGE_LOG_ID,
626 TABLE_NAME,
627 MERGE_HEADER_ID,
628 PRIMARY_KEY_ID,
629 NUM_COL1_ORIG,
630 NUM_COL1_NEW,
631 NUM_COL2_ORIG,
632 NUM_COL2_NEW,
633 NUM_COL3_ORIG,
634 NUM_COL3_NEW,
635 NUM_COL4_ORIG,
636 NUM_COL4_NEW,
637 NUM_COL5_ORIG,
638 NUM_COL5_NEW,
639 NUM_COL6_ORIG,
640 NUM_COL6_NEW,
641 ACTION_FLAG,
642 REQUEST_ID,
643 CREATED_BY,
644 CREATION_DATE,
645 LAST_UPDATE_LOGIN,
646 LAST_UPDATE_DATE,
647 LAST_UPDATED_BY
648 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
649 'MRP_SALES_ORDER_UPDATES',
650 MERGE_HEADER_ID_LIST(I),
651 PRIMARY_KEY_ID_LIST(I),
652 NUM_COL1_ORIG_LIST(I),
653 NUM_COL1_NEW_LIST(I),
654 NUM_COL2_ORIG_LIST(I),
655 NUM_COL2_NEW_LIST(I),
656 NUM_COL3_ORIG_LIST(I),
657 NUM_COL3_NEW_LIST(I),
658 NUM_COL4_ORIG_LIST(I),
659 NUM_COL4_NEW_LIST(I),
660 NUM_COL5_ORIG_LIST(I),
661 NUM_COL5_NEW_LIST(I),
662 NUM_COL6_ORIG_LIST(I),
663 NUM_COL6_NEW_LIST(I),
664 'U',
665 req_id,
666 hz_utility_pub.CREATED_BY,
667 hz_utility_pub.CREATION_DATE,
668 hz_utility_pub.LAST_UPDATE_LOGIN,
669 hz_utility_pub.LAST_UPDATE_DATE,
670 hz_utility_pub.LAST_UPDATED_BY
671 );
672
673 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
674 UPDATE MRP_SALES_ORDER_UPDATES yt SET
675 current_customer_id=NUM_COL1_NEW_LIST(I)
676 ,current_ship_id=NUM_COL2_NEW_LIST(I)
677 ,current_bill_id=NUM_COL3_NEW_LIST(I)
678 ,previous_customer_id=NUM_COL4_NEW_LIST(I)
679 ,previous_bill_id=NUM_COL5_NEW_LIST(I)
680 ,previous_ship_id=NUM_COL6_NEW_LIST(I)
681 , LAST_UPDATE_DATE=SYSDATE
682 , last_updated_by=arp_standard.profile.user_id
683 , last_update_login=arp_standard.profile.last_update_login
684 , REQUEST_ID=req_id
685 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
686 , PROGRAM_ID=arp_standard.profile.program_id
687 , PROGRAM_UPDATE_DATE=SYSDATE
688 WHERE UPDATE_SEQ_NUM=PRIMARY_KEY_ID_LIST(I)
689 ;
690 l_count := l_count + SQL%ROWCOUNT;
691 IF l_last_fetch THEN
692 EXIT;
693 END IF;
694 END LOOP;
695
696 arp_message.set_name('AR','AR_ROWS_UPDATED');
697 arp_message.set_token('NUM_ROWS',to_char(l_count));
698 END IF;
699 EXCEPTION
700 WHEN OTHERS THEN
701 arp_message.set_line( 'MRP_SOU');
702 RAISE;
703 END MRP_SOU;
704
705 /*-------------------------------------------------------------
706 |
707 | PROCEDURE
708 | MRP_SA
709 | DESCRIPTION :
710 | Account merge procedure for the table, MRP_SR_ASSIGNMENTS
711 |
712 | NOTES:
713 |--------------------------------------------------------------*/
714
715 PROCEDURE MRP_SA (
716 req_id NUMBER,
717 set_num NUMBER,
718 process_mode VARCHAR2) IS
719
720 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
721 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
722 INDEX BY BINARY_INTEGER;
723 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
724
725 TYPE ASSIGNMENT_ID_LIST_TYPE IS TABLE OF
726 MRP_SR_ASSIGNMENTS.ASSIGNMENT_ID%TYPE
727 INDEX BY BINARY_INTEGER;
728 PRIMARY_KEY_ID_LIST ASSIGNMENT_ID_LIST_TYPE;
729
730 TYPE customer_id_LIST_TYPE IS TABLE OF
731 MRP_SR_ASSIGNMENTS.customer_id%TYPE
732 INDEX BY BINARY_INTEGER;
733 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
734 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
735
736 TYPE ship_to_site_id_LIST_TYPE IS TABLE OF
737 MRP_SR_ASSIGNMENTS.ship_to_site_id%TYPE
738 INDEX BY BINARY_INTEGER;
739 NUM_COL2_ORIG_LIST ship_to_site_id_LIST_TYPE;
740 NUM_COL2_NEW_LIST ship_to_site_id_LIST_TYPE;
741
742 l_profile_val VARCHAR2(30);
743 CURSOR merged_records IS
744 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
745 ,yt.ASSIGNMENT_ID
746 ,yt.customer_id
747 ,yt.ship_to_site_id
748 FROM MRP_SR_ASSIGNMENTS yt, ra_customer_merges m
749 WHERE (
750 yt.customer_id = m.DUPLICATE_ID
751 OR yt.ship_to_site_id = m.DUPLICATE_SITE_ID
752 ) AND yt.assignment_type in (4,5,6)
753 AND m.process_flag = 'N'
754 AND m.request_id = req_id
755 AND m.set_number = set_num;
756 l_last_fetch BOOLEAN := FALSE;
757 l_count NUMBER;
758 BEGIN
759 IF process_mode='LOCK' THEN
760 NULL;
761 ELSE
762 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
763 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','MRP_SR_ASSIGNMENTS',FALSE);
764 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
765 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
766
767 open merged_records;
768 LOOP
769 FETCH merged_records BULK COLLECT INTO
770 MERGE_HEADER_ID_LIST
771 , PRIMARY_KEY_ID_LIST
772 , NUM_COL1_ORIG_LIST
773 , NUM_COL2_ORIG_LIST
774 limit 1000
775 ;
776 IF merged_records%NOTFOUND THEN
777 l_last_fetch := TRUE;
778 END IF;
779 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
780 exit;
781 END IF;
782 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
783 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
784 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
785 END LOOP;
786 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
787 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
788 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
789 MERGE_LOG_ID,
790 TABLE_NAME,
791 MERGE_HEADER_ID,
792 PRIMARY_KEY_ID,
793 NUM_COL1_ORIG,
794 NUM_COL1_NEW,
795 NUM_COL2_ORIG,
796 NUM_COL2_NEW,
797 ACTION_FLAG,
798 REQUEST_ID,
799 CREATED_BY,
800 CREATION_DATE,
801 LAST_UPDATE_LOGIN,
802 LAST_UPDATE_DATE,
803 LAST_UPDATED_BY
804 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
805 'MRP_SR_ASSIGNMENTS',
806 MERGE_HEADER_ID_LIST(I),
807 PRIMARY_KEY_ID_LIST(I),
808 NUM_COL1_ORIG_LIST(I),
809 NUM_COL1_NEW_LIST(I),
810 NUM_COL2_ORIG_LIST(I),
811 NUM_COL2_NEW_LIST(I),
812 'U',
813 req_id,
814 hz_utility_pub.CREATED_BY,
815 hz_utility_pub.CREATION_DATE,
816 hz_utility_pub.LAST_UPDATE_LOGIN,
817 hz_utility_pub.LAST_UPDATE_DATE,
818 hz_utility_pub.LAST_UPDATED_BY
819 );
820
821 END IF;
822 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
823 UPDATE MRP_SR_ASSIGNMENTS yt SET
824 customer_id=NUM_COL1_NEW_LIST(I)
825 ,ship_to_site_id=NUM_COL2_NEW_LIST(I)
826 , LAST_UPDATE_DATE=SYSDATE
827 , last_updated_by=arp_standard.profile.user_id
828 , last_update_login=arp_standard.profile.last_update_login
829 , REQUEST_ID=req_id
830 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
831 , PROGRAM_ID=arp_standard.profile.program_id
832 , PROGRAM_UPDATE_DATE=SYSDATE
833 WHERE ASSIGNMENT_ID=PRIMARY_KEY_ID_LIST(I)
834 ;
835 l_count := l_count + SQL%ROWCOUNT;
836 IF l_last_fetch THEN
837 EXIT;
838 END IF;
839 END LOOP;
840
841 arp_message.set_name('AR','AR_ROWS_UPDATED');
842 arp_message.set_token('NUM_ROWS',to_char(l_count));
843 END IF;
844 EXCEPTION
845 WHEN OTHERS THEN
846 arp_message.set_line( 'MRP_SA');
847 RAISE;
848 END MRP_SA;
849
850
851 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
852
853 PROCEDURE MERGE (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
854 BEGIN
855
856 arp_message.set_line( 'MRPP_CMERGE_FCST.MERGE()+' );
857
858 MRP_FD( req_id, set_num, process_mode );
859 MRP_FDE( req_id, set_num, process_mode );
860 MRP_FU( req_id, set_num, process_mode );
861 MRP_SOU( req_id, set_num, process_mode);
862 MRP_SA( req_id, set_num, process_mode); /* Bug 1848916 */
863
864 arp_message.set_line( 'MRPP_CMERGE_FCST.MERGE()-' );
865
866 END MERGE;
867
868 end MRPP_CMERGE_FCST;