[Home] [Help]
PACKAGE BODY: APPS.QP_CUST_MERGE
Source
4
1 PACKAGE BODY QP_CUST_MERGE AS
2 /* $Header: QPXCMRGB.pls 120.2 2010/12/31 12:54:39 kdurgasi ship $ */
3
5 /*-------------------------------------------------------------
6 |
7 | PROCEDURE
8 | Agreement_Merge
9 | DESCRIPTION :
10 | Account merge procedure for the table, OE_AGREEMENTS_B
11 |
12 | NOTES:
13 | ******* Please delete these lines after modifications *******
14 | This account merge procedure was generated using a perl script.
15 |
16 | This is only suggested code. Please ensure that the code actually
17 | works for you.
18 |
19 | Please also address the additional notes inserted as comments in the
20 | code below.
21 | ******************************
22 |
23 |--------------------------------------------------------------*/
24
25 PROCEDURE Agreement_Merge (
26 req_id NUMBER,
27 set_num NUMBER,
28 process_mode VARCHAR2) IS
29
30 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
31 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
32 INDEX BY BINARY_INTEGER;
33 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
34
35 TYPE agreement_id_LIST_TYPE IS TABLE OF
36 OE_AGREEMENTS_B.agreement_id%TYPE
37 INDEX BY BINARY_INTEGER;
38 PRIMARY_KEY_ID_LIST agreement_id_LIST_TYPE;
39
40 TYPE invoice_to_org_id_LIST_TYPE IS TABLE OF
41 OE_AGREEMENTS_B.invoice_to_org_id%TYPE
42 INDEX BY BINARY_INTEGER;
43 NUM_COL1_ORIG_LIST invoice_to_org_id_LIST_TYPE;
44 NUM_COL1_NEW_LIST invoice_to_org_id_LIST_TYPE;
45
46 TYPE sold_to_org_id_LIST_TYPE IS TABLE OF
47 OE_AGREEMENTS_B.sold_to_org_id%TYPE
48 INDEX BY BINARY_INTEGER;
49 NUM_COL2_ORIG_LIST sold_to_org_id_LIST_TYPE;
50 NUM_COL2_NEW_LIST sold_to_org_id_LIST_TYPE;
51
52 l_profile_val VARCHAR2(30);
53 CURSOR merged_records IS
54 SELECT distinct CUSTOMER_MERGE_HEADER_ID
55 ,agreement_id
56 ,invoice_to_org_id
57 ,sold_to_org_id
58 FROM OE_AGREEMENTS_B yt, ra_customer_merges m
59 WHERE yt.invoice_to_org_id = m.DUPLICATE_SITE_ID
60 AND m.process_flag = 'N'
61 AND m.request_id = req_id
62 AND m.set_number = set_num
63 UNION
64 SELECT distinct CUSTOMER_MERGE_HEADER_ID
65 ,agreement_id
66 ,invoice_to_org_id
67 ,sold_to_org_id
68 FROM OE_AGREEMENTS_B yt, ra_customer_merges m
69 WHERE yt.sold_to_org_id = m.DUPLICATE_ID
70 AND m.process_flag = 'N'
71 AND m.request_id = req_id
72 AND m.set_number = set_num;
73 l_last_fetch BOOLEAN := FALSE;
74 l_count NUMBER;
75 BEGIN
76 IF process_mode='LOCK' THEN
77 NULL;
78 ELSE
79 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
80 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OE_AGREEMENTS_B',FALSE);
81 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
82 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
83
84 open merged_records;
85 LOOP
86 FETCH merged_records BULK COLLECT INTO
87 MERGE_HEADER_ID_LIST
88 , PRIMARY_KEY_ID_LIST
89 , NUM_COL1_ORIG_LIST
90 , NUM_COL2_ORIG_LIST
91 limit 1000;
92 IF merged_records%NOTFOUND THEN
93 l_last_fetch := TRUE;
94 END IF;
95 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
96 exit;
97 END IF;
98 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
102 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
99 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST(I));
100 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
101 END LOOP;
103 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
104 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
105 MERGE_LOG_ID,
106 TABLE_NAME,
107 MERGE_HEADER_ID,
108 PRIMARY_KEY_ID,
109 NUM_COL1_ORIG,
110 NUM_COL1_NEW,
111 NUM_COL2_ORIG,
112 NUM_COL2_NEW,
113 ACTION_FLAG,
114 REQUEST_ID,
115 CREATED_BY,
116 CREATION_DATE,
117 LAST_UPDATE_LOGIN,
118 LAST_UPDATE_DATE,
119 LAST_UPDATED_BY
120 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
121 'OE_AGREEMENTS_B',
122 MERGE_HEADER_ID_LIST(I),
123 PRIMARY_KEY_ID_LIST(I),
124 NUM_COL1_ORIG_LIST(I),
125 NUM_COL1_NEW_LIST(I),
126 NUM_COL2_ORIG_LIST(I),
127 NUM_COL2_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; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
138 UPDATE OE_AGREEMENTS_B yt SET
139 invoice_to_org_id=NUM_COL1_NEW_LIST(I)
140 ,sold_to_org_id=NUM_COL2_NEW_LIST(I)
141 , LAST_UPDATE_DATE=SYSDATE
142 , last_updated_by=arp_standard.profile.user_id
143 , last_update_login=arp_standard.profile.last_update_login
144 WHERE agreement_id=PRIMARY_KEY_ID_LIST(I)
145 ;
146 l_count := l_count + SQL%ROWCOUNT;
147 IF l_last_fetch THEN
148 EXIT;
149 END IF;
150 END LOOP;
151
152 arp_message.set_name('AR','AR_ROWS_UPDATED');
153 arp_message.set_token('NUM_ROWS',to_char(l_count));
154 END IF;
155 EXCEPTION
156 WHEN OTHERS THEN
157 arp_message.set_line( 'Agreement_Merge');
158 RAISE;
159 END Agreement_Merge;
160
161 --Below procedure added for bug 8399386
162 /*-------------------------------------------------------------
163 |
164 | PROCEDURE : Check_Duplicate
165 | DESCRIPTION :
166 | Checks if duplicate qualifiers exist after a Customer Merge
167 | is done.
168 |
169 |--------------------------------------------------------------*/
170
171 PROCEDURE Check_Duplicate(p_qualifier_id IN number,p_qualifier_attr_value IN varchar2) IS
172 l_qualifier_id number;
173 l_temp_date DATE;
174 BEGIN
175 l_temp_date := trunc(sysdate);
176 BEGIN
177 SELECT a.qualifier_id
181 AND trunc(l_temp_date) between nvl(trunc(start_date_active), trunc(l_temp_date)) and
178 INTO l_qualifier_id
179 FROM qp_qualifiers a
180 WHERE a.qualifier_attr_value = to_char(p_qualifier_attr_value)
182 nvl(trunc(end_date_active), trunc(l_temp_date))
183 AND (a.qualifier_context,
184 a.qualifier_attribute,
185 nvl(a.list_header_id, -1),
186 nvl(a.list_line_id, -1),
187 nvl(qualifier_rule_id, -1),
188 a.qualifier_grouping_no) IN
189 (SELECT b.qualifier_context, b.qualifier_attribute,
190 nvl(b.list_header_id, -1),
191 nvl(b.list_line_id, -1),
192 nvl(qualifier_rule_id, -1),
193 b.qualifier_grouping_no
194 FROM qp_qualifiers b
195 WHERE b.qualifier_id = p_qualifier_id
196 AND b.qualifier_id <> a.qualifier_id)
197 AND rownum = 1;
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 l_qualifier_id := NULL;
202 END;
203 IF l_qualifier_id IS NOT NULL THEN /* Duplicate Exists. Therefore delete
204 the duplicate qualifier */
205 DELETE qp_qualifiers
206 WHERE qualifier_id = l_qualifier_id;
207 END IF;
208 END Check_Duplicate;
209 --End procedure for bug 8399386
210
211 /*-------------------------------------------------------------
212 |
213 | PROCEDURE
214 | Qualifier_Merge
215 | DESCRIPTION :
219 | ******* Please delete these lines after modifications *******
216 | Account merge procedure for the table, QP_QUALIFIERS
217 |
218 | NOTES:
220 | This account merge procedure was generated using a perl script.
221 |
222 | This is only suggested code. Please ensure that the code actually
223 | works for you.
224 |
225 | Please also address the additional notes inserted as comments in the
226 | code below.
227 | ******************************
228 |
229 |--------------------------------------------------------------*/
230
231 PROCEDURE Qualifier_Merge (
232 req_id NUMBER,
233 set_num NUMBER,
234 process_mode VARCHAR2) IS
235
236 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
237 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
238 INDEX BY BINARY_INTEGER;
239 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
240
241 TYPE qualifier_id_LIST_TYPE IS TABLE OF
242 QP_QUALIFIERS.qualifier_id%TYPE
243 INDEX BY BINARY_INTEGER;
244 PRIMARY_KEY_ID_LIST qualifier_id_LIST_TYPE;
245
246 TYPE qualifier_attr_value_LIST_TYPE IS TABLE OF
247 QP_QUALIFIERS.qualifier_attr_value%TYPE
248 INDEX BY BINARY_INTEGER;
249 VCHAR_COL1_ORIG_LIST qualifier_attr_value_LIST_TYPE;
250 VCHAR_COL1_NEW_LIST qualifier_attr_value_LIST_TYPE;
251
252 --Begin code added for Bug fix 3649761
253 VCHAR_COL2_ORIG_LIST qualifier_attr_value_LIST_TYPE;
254 VCHAR_COL2_NEW_LIST qualifier_attr_value_LIST_TYPE;
255
256 TYPE qualifier_context_LIST_TYPE IS TABLE OF
257 QP_QUALIFIERS.qualifier_context%TYPE
258 INDEX BY BINARY_INTEGER;
259 QUALIFIER_CONTEXT_LIST qualifier_context_LIST_TYPE;
260
261 TYPE qualifier_attribute_LIST_TYPE IS TABLE OF
262 QP_QUALIFIERS.qualifier_attribute%TYPE
263 INDEX BY BINARY_INTEGER;
264 QUALIFIER_ATTRIBUTE_LIST qualifier_attribute_LIST_TYPE;
265 --End code added for bug fix 3649761
266
267 l_profile_val VARCHAR2(30);
268 CURSOR merged_records IS
269 SELECT distinct CUSTOMER_MERGE_HEADER_ID
270 ,qualifier_id
271 ,qualifier_attr_value
272 --Added following 3 select list columns for bug fix 3649761
273 ,qualifier_attr_value
274 ,qualifier_context
275 ,qualifier_attribute
276 FROM QP_QUALIFIERS yt, ra_customer_merges m
277 WHERE
278 /* (
279 yt.qualifier_attr_value = to_char(m.DUPLICATE_SITE_ID)
280 )*/
281 -- above clause replaced by clause below for bug fix 3649761
282 (
283 yt.qualifier_attr_value = to_char(m.DUPLICATE_SITE_ID) AND
284 (yt.qualifier_context = 'CUSTOMER' AND
285 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE11' --Ship To
286 OR
287 yt.qualifier_context = 'CUSTOMER' AND
288 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE5' --Site Use
289 OR
290 yt.qualifier_context = 'CUSTOMER' AND
291 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE14' --Bill To
292 )
293 OR
294 yt.qualifier_attr_value = to_char(m.DUPLICATE_ID) AND
295 (yt.qualifier_context = 'CUSTOMER' AND
296 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' --Customer Name
297 )
298 )
299 AND m.process_flag = 'N'
300 AND m.request_id = req_id
301 AND m.set_number = set_num;
302 l_last_fetch BOOLEAN := FALSE;
303 l_count NUMBER;
304 BEGIN
305 IF process_mode='LOCK' THEN
306 NULL;
307 ELSE
308 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
309 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','QP_QUALIFIERS',FALSE);
310 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
311 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
312
313 open merged_records;
314 LOOP
315 FETCH merged_records BULK COLLECT INTO
316 MERGE_HEADER_ID_LIST
317 , PRIMARY_KEY_ID_LIST
318 , VCHAR_COL1_ORIG_LIST
319 , VCHAR_COL2_ORIG_LIST --Added for bug fix 3649761
320 , QUALIFIER_CONTEXT_LIST
321 , QUALIFIER_ATTRIBUTE_LIST
322 limit 1000;
323 IF merged_records%NOTFOUND THEN
324 l_last_fetch := TRUE;
325 END IF;
326 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
327 exit;
328 END IF;
329 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
330 --Modified code for bug fix 3649761
331 IF (QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
332 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE2') --Customer Name
333 THEN
334 VCHAR_COL1_NEW_LIST(I) := to_char(HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(to_number(VCHAR_COL1_ORIG_LIST(I))));
335 --check for duplicate smbalara 8203178 / 8399386
339 VCHAR_COL2_NEW_LIST(I) := NULL;
336 Check_Duplicate(PRIMARY_KEY_ID_LIST(I),VCHAR_COL1_NEW_LIST(I));
337
338 VCHAR_COL2_ORIG_LIST(I) := NULL;
340 ELSIF ((QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
341 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE11') --Ship To
342 OR
343 (QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
344 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE5') --Site Use
345 OR
346 (QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
347 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE14') --Bill To
348 )
349 THEN
350 VCHAR_COL2_NEW_LIST(I) := to_char(HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(to_number(VCHAR_COL2_ORIG_LIST(I))));
351
352 --check for duplicate smbalara 8203178 / 8399386
353 Check_Duplicate(PRIMARY_KEY_ID_LIST(I),VCHAR_COL2_NEW_LIST(I));
354
355 VCHAR_COL1_ORIG_LIST(I) := NULL;
356 VCHAR_COL1_NEW_LIST(I) := NULL;
357 END IF;
358
359 END LOOP;
360 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
361 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
362 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
363 MERGE_LOG_ID,
364 TABLE_NAME,
365 MERGE_HEADER_ID,
366 PRIMARY_KEY_ID,
367 VCHAR_COL1_ORIG,
368 VCHAR_COL1_NEW,
369 --Added the following 2 columns for bug fix 3649761
370 VCHAR_COL2_ORIG,
371 VCHAR_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 'QP_QUALIFIERS',
381 MERGE_HEADER_ID_LIST(I),
382 PRIMARY_KEY_ID_LIST(I),
383 VCHAR_COL1_ORIG_LIST(I),
384 VCHAR_COL1_NEW_LIST(I),
385 --Added the following 2 columns for bug fix 3649761
389 req_id,
386 VCHAR_COL2_ORIG_LIST(I),
387 VCHAR_COL2_NEW_LIST(I),
388 'U',
390 hz_utility_pub.CREATED_BY,
391 hz_utility_pub.CREATION_DATE,
392 hz_utility_pub.LAST_UPDATE_LOGIN,
393 hz_utility_pub.LAST_UPDATE_DATE,
394 hz_utility_pub.LAST_UPDATED_BY
395 );
396
397 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
398 UPDATE QP_QUALIFIERS yt SET
399 --Modified code for bug fix 3649761
400 qualifier_attr_value=decode(nvl(VCHAR_COL1_NEW_LIST(I),'x'), 'x',
401 VCHAR_COL2_NEW_LIST(I),
402 VCHAR_COL1_NEW_LIST(I))
403 , LAST_UPDATE_DATE=SYSDATE
404 , last_updated_by=arp_standard.profile.user_id
405 , last_update_login=arp_standard.profile.last_update_login
406 , REQUEST_ID=req_id
407 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
408 , PROGRAM_ID=arp_standard.profile.program_id
409 , PROGRAM_UPDATE_DATE=SYSDATE
410 WHERE qualifier_id=PRIMARY_KEY_ID_LIST(I);
411 l_count := l_count + SQL%ROWCOUNT;
412 IF l_last_fetch THEN
413 EXIT;
414 END IF;
415 END LOOP;
416
417 arp_message.set_name('AR','AR_ROWS_UPDATED');
418 arp_message.set_token('NUM_ROWS',to_char(l_count));
419 END IF;
420 EXCEPTION
421 WHEN OTHERS THEN
422 arp_message.set_line( 'Qualifier_Merge');
423 RAISE;
424 END Qualifier_Merge;
425
426
427 PROCEDURE Merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) IS
428 BEGIN
429
430 arp_message.set_line('QP_CUST_MERGE.Merge()+');
431
432 Agreement_Merge(req_id, set_num, process_mode);
433 Qualifier_Merge(req_id, set_num, process_mode);
434
435 arp_message.set_line('QP_CUST_MERGE.Merge()-');
436
437 EXCEPTION
438 when others then
439 raise;
440
441 END Merge;
442
443 END QP_CUST_MERGE;