[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_ACCT_MERGE_PKG
Source
1 PACKAGE BODY ISC_DBI_ACCT_MERGE_PKG AS
2 /* $Header: ISCACMGB.pls 120.1 2006/02/27 17:25:23 scheung noship $ */
3
4 PROCEDURE ISC_BOOK_SUM2_PDUE_F_AM (
5 req_id NUMBER,
6 set_num NUMBER,
7 process_mode VARCHAR2) IS
8
9 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
10 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
11 INDEX BY BINARY_INTEGER;
12 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
13
14 TYPE LINE_ID_LIST_TYPE IS TABLE OF
15 ISC_BOOK_SUM2_PDUE_F.LINE_ID%TYPE
16 INDEX BY BINARY_INTEGER;
17 PRIMARY_KEY_ID1_LIST LINE_ID_LIST_TYPE;
18
19 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
20 ISC_BOOK_SUM2_PDUE_F.CUSTOMER_ID%TYPE
21 INDEX BY BINARY_INTEGER;
22 NUM_COL0_NEW_LIST CUSTOMER_ID_LIST_TYPE;
23
24 TYPE SOLD_TO_ORG_ID_LIST_TYPE IS TABLE OF
25 ISC_BOOK_SUM2_PDUE_F.SOLD_TO_ORG_ID%TYPE
26 INDEX BY BINARY_INTEGER;
27 NUM_COL1_ORIG_LIST SOLD_TO_ORG_ID_LIST_TYPE;
28 NUM_COL1_NEW_LIST SOLD_TO_ORG_ID_LIST_TYPE;
29
30 TYPE SHIP_TO_ORG_ID_LIST_TYPE IS TABLE OF
31 ISC_BOOK_SUM2_PDUE_F.SHIP_TO_ORG_ID%TYPE
32 INDEX BY BINARY_INTEGER;
33 NUM_COL2_ORIG_LIST SHIP_TO_ORG_ID_LIST_TYPE;
34 NUM_COL2_NEW_LIST SHIP_TO_ORG_ID_LIST_TYPE;
35
36 l_profile_val VARCHAR2(30);
37 l_new_party_id HZ_CUST_ACCOUNTS.PARTY_ID%TYPE;
38 CURSOR merged_records IS
39 SELECT distinct CUSTOMER_MERGE_HEADER_ID
40 ,LINE_ID
41 ,SOLD_TO_ORG_ID
42 ,SHIP_TO_ORG_ID
43 FROM ISC_BOOK_SUM2_PDUE_F yt, ra_customer_merges m
44 WHERE (
45 yt.SOLD_TO_ORG_ID = m.DUPLICATE_ID
46 OR yt.SHIP_TO_ORG_ID = m.DUPLICATE_SITE_ID
47 ) AND m.process_flag = 'N'
48 AND m.request_id = req_id
49 AND m.set_number = set_num;
50 l_last_fetch BOOLEAN := FALSE;
51 l_count NUMBER;
52 BEGIN
53 IF process_mode='LOCK' THEN
54 NULL;
55 ELSE
56 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
57 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ISC_BOOK_SUM2_PDUE_F',FALSE);
58 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
59 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
60
61 open merged_records;
62 LOOP
63 FETCH merged_records BULK COLLECT INTO
64 MERGE_HEADER_ID_LIST
65 , PRIMARY_KEY_ID1_LIST
66 , NUM_COL1_ORIG_LIST
67 , NUM_COL2_ORIG_LIST
68 ;
69 IF merged_records%NOTFOUND THEN
70 l_last_fetch := TRUE;
71 END IF;
72 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
73 exit;
74 END IF;
75 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
76 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
77 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
78
79 select party_id into l_new_party_id
80 from hz_cust_accounts
81 where cust_account_id = NUM_COL1_NEW_LIST(I);
82 NUM_COL0_NEW_LIST(I) := l_new_party_id;
83
84 END LOOP;
85 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
86 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
87 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
88 MERGE_LOG_ID,
89 TABLE_NAME,
90 MERGE_HEADER_ID,
91 PRIMARY_KEY_ID1,
92 NUM_COL1_ORIG,
93 NUM_COL1_NEW,
94 NUM_COL2_ORIG,
95 NUM_COL2_NEW,
96 ACTION_FLAG,
97 REQUEST_ID,
98 CREATED_BY,
99 CREATION_DATE,
100 LAST_UPDATE_LOGIN,
101 LAST_UPDATE_DATE,
102 LAST_UPDATED_BY
103 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
104 'ISC_BOOK_SUM2_PDUE_F',
105 MERGE_HEADER_ID_LIST(I),
106 PRIMARY_KEY_ID1_LIST(I),
107 NUM_COL1_ORIG_LIST(I),
108 NUM_COL1_NEW_LIST(I),
109 NUM_COL2_ORIG_LIST(I),
110 NUM_COL2_NEW_LIST(I),
111 'U',
112 req_id,
113 hz_utility_pub.CREATED_BY,
114 hz_utility_pub.CREATION_DATE,
115 hz_utility_pub.LAST_UPDATE_LOGIN,
116 hz_utility_pub.LAST_UPDATE_DATE,
117 hz_utility_pub.LAST_UPDATED_BY
118 );
119
120 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
121 UPDATE ISC_BOOK_SUM2_PDUE_F yt SET
122 SOLD_TO_ORG_ID=NUM_COL1_NEW_LIST(I)
123 ,SHIP_TO_ORG_ID=NUM_COL2_NEW_LIST(I)
124 ,CUSTOMER_ID=NUM_COL0_NEW_LIST(I)
125 WHERE LINE_ID=PRIMARY_KEY_ID1_LIST(I)
126 ;
127 l_count := l_count + SQL%ROWCOUNT;
128 IF l_last_fetch THEN
129 EXIT;
130 END IF;
131 END LOOP;
132
133 arp_message.set_name('AR','AR_ROWS_UPDATED');
134 arp_message.set_token('NUM_ROWS',to_char(l_count));
135 END IF;
136 EXCEPTION
137 WHEN OTHERS THEN
138 arp_message.set_line( 'ISC_BOOK_SUM2_PDUE_F_AM');
139 RAISE;
140 END ISC_BOOK_SUM2_PDUE_F_AM;
141
142 PROCEDURE ISC_BOOK_SUM2_PDUE2_F_AM (
143 req_id NUMBER,
144 set_num NUMBER,
145 process_mode VARCHAR2) IS
146
147 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
148 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
149 INDEX BY BINARY_INTEGER;
150 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
151
152 TYPE LINE_ID_LIST_TYPE IS TABLE OF
153 ISC_BOOK_SUM2_PDUE2_F.LINE_ID%TYPE
154 INDEX BY BINARY_INTEGER;
155 PRIMARY_KEY_ID1_LIST LINE_ID_LIST_TYPE;
156
157 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
158 ISC_BOOK_SUM2_PDUE2_F.CUSTOMER_ID%TYPE
159 INDEX BY BINARY_INTEGER;
160 NUM_COL0_NEW_LIST CUSTOMER_ID_LIST_TYPE;
161
162 TYPE SOLD_TO_ORG_ID_LIST_TYPE IS TABLE OF
163 ISC_BOOK_SUM2_PDUE2_F.SOLD_TO_ORG_ID%TYPE
164 INDEX BY BINARY_INTEGER;
165 NUM_COL1_ORIG_LIST SOLD_TO_ORG_ID_LIST_TYPE;
166 NUM_COL1_NEW_LIST SOLD_TO_ORG_ID_LIST_TYPE;
167
168 TYPE SHIP_TO_ORG_ID_LIST_TYPE IS TABLE OF
169 ISC_BOOK_SUM2_PDUE2_F.SHIP_TO_ORG_ID%TYPE
170 INDEX BY BINARY_INTEGER;
171 NUM_COL2_ORIG_LIST SHIP_TO_ORG_ID_LIST_TYPE;
172 NUM_COL2_NEW_LIST SHIP_TO_ORG_ID_LIST_TYPE;
173
174 l_profile_val VARCHAR2(30);
175 l_new_party_id HZ_CUST_ACCOUNTS.PARTY_ID%TYPE;
176 CURSOR merged_records IS
177 SELECT distinct CUSTOMER_MERGE_HEADER_ID
178 ,LINE_ID
179 ,SOLD_TO_ORG_ID
180 ,SHIP_TO_ORG_ID
181 FROM ISC_BOOK_SUM2_PDUE2_F yt, ra_customer_merges m
182 WHERE (
183 yt.SOLD_TO_ORG_ID = m.DUPLICATE_ID
184 OR yt.SHIP_TO_ORG_ID = m.DUPLICATE_SITE_ID
185 ) AND m.process_flag = 'N'
186 AND m.request_id = req_id
187 AND m.set_number = set_num;
188 l_last_fetch BOOLEAN := FALSE;
189 l_count NUMBER;
190 BEGIN
191 IF process_mode='LOCK' THEN
192 NULL;
193 ELSE
194 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
195 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ISC_BOOK_SUM2_PDUE2_F',FALSE);
196 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
197 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
198
199 open merged_records;
200 LOOP
201 FETCH merged_records BULK COLLECT INTO
202 MERGE_HEADER_ID_LIST
203 , PRIMARY_KEY_ID1_LIST
204 , NUM_COL1_ORIG_LIST
205 , NUM_COL2_ORIG_LIST
206 ;
207 IF merged_records%NOTFOUND THEN
208 l_last_fetch := TRUE;
209 END IF;
210 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
211 exit;
212 END IF;
213 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
214 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
215 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
216
217 select party_id into l_new_party_id
218 from hz_cust_accounts
219 where cust_account_id = NUM_COL1_NEW_LIST(I);
220 NUM_COL0_NEW_LIST(I) := l_new_party_id;
221
222 END LOOP;
223 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
224 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
225 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
226 MERGE_LOG_ID,
227 TABLE_NAME,
228 MERGE_HEADER_ID,
229 PRIMARY_KEY_ID1,
230 NUM_COL1_ORIG,
231 NUM_COL1_NEW,
232 NUM_COL2_ORIG,
233 NUM_COL2_NEW,
234 ACTION_FLAG,
235 REQUEST_ID,
236 CREATED_BY,
237 CREATION_DATE,
238 LAST_UPDATE_LOGIN,
239 LAST_UPDATE_DATE,
240 LAST_UPDATED_BY
241 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
242 'ISC_BOOK_SUM2_PDUE2_F',
243 MERGE_HEADER_ID_LIST(I),
244 PRIMARY_KEY_ID1_LIST(I),
245 NUM_COL1_ORIG_LIST(I),
246 NUM_COL1_NEW_LIST(I),
247 NUM_COL2_ORIG_LIST(I),
248 NUM_COL2_NEW_LIST(I),
249 'U',
250 req_id,
251 hz_utility_pub.CREATED_BY,
252 hz_utility_pub.CREATION_DATE,
253 hz_utility_pub.LAST_UPDATE_LOGIN,
254 hz_utility_pub.LAST_UPDATE_DATE,
255 hz_utility_pub.LAST_UPDATED_BY
256 );
257
258 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
259 UPDATE ISC_BOOK_SUM2_PDUE2_F yt SET
260 SOLD_TO_ORG_ID=NUM_COL1_NEW_LIST(I)
261 ,SHIP_TO_ORG_ID=NUM_COL2_NEW_LIST(I)
262 ,CUSTOMER_ID=NUM_COL0_NEW_LIST(I)
263 WHERE LINE_ID=PRIMARY_KEY_ID1_LIST(I)
264 ;
265 l_count := l_count + SQL%ROWCOUNT;
266 IF l_last_fetch THEN
267 EXIT;
268 END IF;
269 END LOOP;
270
271 arp_message.set_name('AR','AR_ROWS_UPDATED');
272 arp_message.set_token('NUM_ROWS',to_char(l_count));
273 END IF;
274 EXCEPTION
275 WHEN OTHERS THEN
276 arp_message.set_line( 'ISC_BOOK_SUM2_PDUE2_F_AM');
277 RAISE;
278 END ISC_BOOK_SUM2_PDUE2_F_AM;
279
280 PROCEDURE ISC_BOOK_SUM2_BKORD_F_AM (
281 req_id NUMBER,
282 set_num NUMBER,
283 process_mode VARCHAR2) IS
284
285 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
286 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
287 INDEX BY BINARY_INTEGER;
288 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
289
290 TYPE LINE_ID_LIST_TYPE IS TABLE OF
291 ISC_BOOK_SUM2_BKORD_F.LINE_ID%TYPE
292 INDEX BY BINARY_INTEGER;
293 PRIMARY_KEY_ID1_LIST LINE_ID_LIST_TYPE;
294
295 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
296 ISC_BOOK_SUM2_BKORD_F.CUSTOMER_ID%TYPE
297 INDEX BY BINARY_INTEGER;
298 NUM_COL0_NEW_LIST CUSTOMER_ID_LIST_TYPE;
299
300 TYPE SOLD_TO_ORG_ID_LIST_TYPE IS TABLE OF
301 ISC_BOOK_SUM2_BKORD_F.SOLD_TO_ORG_ID%TYPE
302 INDEX BY BINARY_INTEGER;
303 NUM_COL1_ORIG_LIST SOLD_TO_ORG_ID_LIST_TYPE;
304 NUM_COL1_NEW_LIST SOLD_TO_ORG_ID_LIST_TYPE;
305
306 TYPE SHIP_TO_ORG_ID_LIST_TYPE IS TABLE OF
307 ISC_BOOK_SUM2_BKORD_F.SHIP_TO_ORG_ID%TYPE
308 INDEX BY BINARY_INTEGER;
309 NUM_COL2_ORIG_LIST SHIP_TO_ORG_ID_LIST_TYPE;
310 NUM_COL2_NEW_LIST SHIP_TO_ORG_ID_LIST_TYPE;
311
312 l_profile_val VARCHAR2(30);
313 l_new_party_id HZ_CUST_ACCOUNTS.PARTY_ID%TYPE;
314 CURSOR merged_records IS
315 SELECT distinct CUSTOMER_MERGE_HEADER_ID
316 ,LINE_ID
317 ,SOLD_TO_ORG_ID
318 ,SHIP_TO_ORG_ID
319 FROM ISC_BOOK_SUM2_BKORD_F yt, ra_customer_merges m
320 WHERE (
321 yt.SOLD_TO_ORG_ID = m.DUPLICATE_ID
322 OR yt.SHIP_TO_ORG_ID = m.DUPLICATE_SITE_ID
323 ) AND m.process_flag = 'N'
324 AND m.request_id = req_id
325 AND m.set_number = set_num;
326 l_last_fetch BOOLEAN := FALSE;
327 l_count NUMBER;
328 BEGIN
329 IF process_mode='LOCK' THEN
330 NULL;
331 ELSE
332 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
333 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ISC_BOOK_SUM2_BKORD_F',FALSE);
334 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
335 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
336
337 open merged_records;
338 LOOP
339 FETCH merged_records BULK COLLECT INTO
340 MERGE_HEADER_ID_LIST
341 , PRIMARY_KEY_ID1_LIST
342 , NUM_COL1_ORIG_LIST
343 , NUM_COL2_ORIG_LIST
344 ;
345 IF merged_records%NOTFOUND THEN
346 l_last_fetch := TRUE;
347 END IF;
348 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
349 exit;
350 END IF;
351 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
352 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
353 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
354
355 select party_id into l_new_party_id
356 from hz_cust_accounts
357 where cust_account_id = NUM_COL1_NEW_LIST(I);
358 NUM_COL0_NEW_LIST(I) := l_new_party_id;
359
360 END LOOP;
361 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
362 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
363 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
364 MERGE_LOG_ID,
365 TABLE_NAME,
366 MERGE_HEADER_ID,
367 PRIMARY_KEY_ID1,
368 NUM_COL1_ORIG,
369 NUM_COL1_NEW,
370 NUM_COL2_ORIG,
371 NUM_COL2_NEW,
372 ACTION_FLAG,
373 REQUEST_ID,
374 CREATED_BY,
375 CREATION_DATE,
376 LAST_UPDATE_LOGIN,
377 LAST_UPDATE_DATE,
378 LAST_UPDATED_BY
379 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
380 'ISC_BOOK_SUM2_BKORD_F',
381 MERGE_HEADER_ID_LIST(I),
382 PRIMARY_KEY_ID1_LIST(I),
383 NUM_COL1_ORIG_LIST(I),
384 NUM_COL1_NEW_LIST(I),
385 NUM_COL2_ORIG_LIST(I),
386 NUM_COL2_NEW_LIST(I),
387 'U',
388 req_id,
389 hz_utility_pub.CREATED_BY,
390 hz_utility_pub.CREATION_DATE,
391 hz_utility_pub.LAST_UPDATE_LOGIN,
392 hz_utility_pub.LAST_UPDATE_DATE,
393 hz_utility_pub.LAST_UPDATED_BY
394 );
395
396 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
397 UPDATE ISC_BOOK_SUM2_BKORD_F yt SET
398 SOLD_TO_ORG_ID=NUM_COL1_NEW_LIST(I)
399 ,SHIP_TO_ORG_ID=NUM_COL2_NEW_LIST(I)
400 ,CUSTOMER_ID=NUM_COL0_NEW_LIST(I)
401 WHERE LINE_ID=PRIMARY_KEY_ID1_LIST(I)
402 ;
403 l_count := l_count + SQL%ROWCOUNT;
404 IF l_last_fetch THEN
405 EXIT;
406 END IF;
407 END LOOP;
408
409 arp_message.set_name('AR','AR_ROWS_UPDATED');
410 arp_message.set_token('NUM_ROWS',to_char(l_count));
411 END IF;
412 EXCEPTION
413 WHEN OTHERS THEN
414 arp_message.set_line( 'ISC_BOOK_SUM2_BKORD_F_AM');
415 RAISE;
416 END ISC_BOOK_SUM2_BKORD_F_AM;
417
418
419 END ISC_DBI_ACCT_MERGE_PKG;