[Home] [Help]
PACKAGE BODY: APPS.QP_CUST_MERGE
Source
1 PACKAGE BODY QP_CUST_MERGE AS
2 /* $Header: QPXCMRGB.pls 120.0 2005/06/02 01:12:45 appldev noship $ */
3
4
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 (
60 yt.invoice_to_org_id = m.DUPLICATE_SITE_ID
61 OR yt.sold_to_org_id = m.DUPLICATE_ID
62 ) AND m.process_flag = 'N'
63 AND m.request_id = req_id
64 AND m.set_number = set_num;
65 l_last_fetch BOOLEAN := FALSE;
66 l_count NUMBER;
67 BEGIN
68 IF process_mode='LOCK' THEN
69 NULL;
70 ELSE
71 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
72 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OE_AGREEMENTS_B',FALSE);
73 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
74 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
75
76 open merged_records;
77 LOOP
78 FETCH merged_records BULK COLLECT INTO
79 MERGE_HEADER_ID_LIST
80 , PRIMARY_KEY_ID_LIST
81 , NUM_COL1_ORIG_LIST
82 , NUM_COL2_ORIG_LIST
83 limit 1000;
84 IF merged_records%NOTFOUND THEN
85 l_last_fetch := TRUE;
86 END IF;
87 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
88 exit;
89 END IF;
90 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
91 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST(I));
92 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
93 END LOOP;
94 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
95 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
96 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
97 MERGE_LOG_ID,
98 TABLE_NAME,
99 MERGE_HEADER_ID,
100 PRIMARY_KEY_ID,
101 NUM_COL1_ORIG,
102 NUM_COL1_NEW,
103 NUM_COL2_ORIG,
104 NUM_COL2_NEW,
105 ACTION_FLAG,
106 REQUEST_ID,
107 CREATED_BY,
108 CREATION_DATE,
109 LAST_UPDATE_LOGIN,
110 LAST_UPDATE_DATE,
111 LAST_UPDATED_BY
112 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
113 'OE_AGREEMENTS_B',
114 MERGE_HEADER_ID_LIST(I),
115 PRIMARY_KEY_ID_LIST(I),
116 NUM_COL1_ORIG_LIST(I),
117 NUM_COL1_NEW_LIST(I),
118 NUM_COL2_ORIG_LIST(I),
119 NUM_COL2_NEW_LIST(I),
120 'U',
121 req_id,
122 hz_utility_pub.CREATED_BY,
123 hz_utility_pub.CREATION_DATE,
124 hz_utility_pub.LAST_UPDATE_LOGIN,
125 hz_utility_pub.LAST_UPDATE_DATE,
126 hz_utility_pub.LAST_UPDATED_BY
127 );
128
129 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
130 UPDATE OE_AGREEMENTS_B yt SET
131 invoice_to_org_id=NUM_COL1_NEW_LIST(I)
132 ,sold_to_org_id=NUM_COL2_NEW_LIST(I)
133 , LAST_UPDATE_DATE=SYSDATE
134 , last_updated_by=arp_standard.profile.user_id
135 , last_update_login=arp_standard.profile.last_update_login
136 WHERE agreement_id=PRIMARY_KEY_ID_LIST(I)
137 ;
138 l_count := l_count + SQL%ROWCOUNT;
139 IF l_last_fetch THEN
140 EXIT;
141 END IF;
142 END LOOP;
143
144 arp_message.set_name('AR','AR_ROWS_UPDATED');
145 arp_message.set_token('NUM_ROWS',to_char(l_count));
146 END IF;
147 EXCEPTION
148 WHEN OTHERS THEN
149 arp_message.set_line( 'Agreement_Merge');
150 RAISE;
151 END Agreement_Merge;
152
153
154
155 /*-------------------------------------------------------------
156 |
157 | PROCEDURE
158 | Qualifier_Merge
159 | DESCRIPTION :
160 | Account merge procedure for the table, QP_QUALIFIERS
161 |
162 | NOTES:
163 | ******* Please delete these lines after modifications *******
164 | This account merge procedure was generated using a perl script.
165 |
166 | This is only suggested code. Please ensure that the code actually
167 | works for you.
168 |
169 | Please also address the additional notes inserted as comments in the
170 | code below.
171 | ******************************
172 |
173 |--------------------------------------------------------------*/
174
175 PROCEDURE Qualifier_Merge (
176 req_id NUMBER,
177 set_num NUMBER,
178 process_mode VARCHAR2) IS
179
180 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
181 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
182 INDEX BY BINARY_INTEGER;
183 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
184
185 TYPE qualifier_id_LIST_TYPE IS TABLE OF
186 QP_QUALIFIERS.qualifier_id%TYPE
187 INDEX BY BINARY_INTEGER;
188 PRIMARY_KEY_ID_LIST qualifier_id_LIST_TYPE;
189
190 TYPE qualifier_attr_value_LIST_TYPE IS TABLE OF
191 QP_QUALIFIERS.qualifier_attr_value%TYPE
192 INDEX BY BINARY_INTEGER;
193 VCHAR_COL1_ORIG_LIST qualifier_attr_value_LIST_TYPE;
194 VCHAR_COL1_NEW_LIST qualifier_attr_value_LIST_TYPE;
195
196 --Begin code added for Bug fix 3649761
197 VCHAR_COL2_ORIG_LIST qualifier_attr_value_LIST_TYPE;
198 VCHAR_COL2_NEW_LIST qualifier_attr_value_LIST_TYPE;
199
200 TYPE qualifier_context_LIST_TYPE IS TABLE OF
201 QP_QUALIFIERS.qualifier_context%TYPE
202 INDEX BY BINARY_INTEGER;
203 QUALIFIER_CONTEXT_LIST qualifier_context_LIST_TYPE;
204
205 TYPE qualifier_attribute_LIST_TYPE IS TABLE OF
206 QP_QUALIFIERS.qualifier_attribute%TYPE
207 INDEX BY BINARY_INTEGER;
208 QUALIFIER_ATTRIBUTE_LIST qualifier_attribute_LIST_TYPE;
209 --End code added for bug fix 3649761
210
211 l_profile_val VARCHAR2(30);
212 CURSOR merged_records IS
213 SELECT distinct CUSTOMER_MERGE_HEADER_ID
214 ,qualifier_id
215 ,qualifier_attr_value
216 --Added following 3 select list columns for bug fix 3649761
217 ,qualifier_attr_value
218 ,qualifier_context
219 ,qualifier_attribute
220 FROM QP_QUALIFIERS yt, ra_customer_merges m
221 WHERE
222 /* (
223 yt.qualifier_attr_value = to_char(m.DUPLICATE_SITE_ID)
224 )*/
225 -- above clause replaced by clause below for bug fix 3649761
226 (
227 yt.qualifier_attr_value = to_char(m.DUPLICATE_SITE_ID) AND
228 (yt.qualifier_context = 'CUSTOMER' AND
229 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE11' --Ship To
230 OR
231 yt.qualifier_context = 'CUSTOMER' AND
232 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE5' --Site Use
233 OR
234 yt.qualifier_context = 'CUSTOMER' AND
235 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE14' --Bill To
236 )
237 OR
238 yt.qualifier_attr_value = to_char(m.DUPLICATE_ID) AND
239 (yt.qualifier_context = 'CUSTOMER' AND
240 yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' --Customer Name
241 )
242 )
243 AND m.process_flag = 'N'
244 AND m.request_id = req_id
245 AND m.set_number = set_num;
246 l_last_fetch BOOLEAN := FALSE;
247 l_count NUMBER;
248 BEGIN
249 IF process_mode='LOCK' THEN
250 NULL;
251 ELSE
252 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
253 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','QP_QUALIFIERS',FALSE);
254 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
255 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
256
257 open merged_records;
258 LOOP
259 FETCH merged_records BULK COLLECT INTO
260 MERGE_HEADER_ID_LIST
261 , PRIMARY_KEY_ID_LIST
262 , VCHAR_COL1_ORIG_LIST
263 , VCHAR_COL2_ORIG_LIST --Added for bug fix 3649761
264 , QUALIFIER_CONTEXT_LIST
265 , QUALIFIER_ATTRIBUTE_LIST
266 limit 1000;
267 IF merged_records%NOTFOUND THEN
268 l_last_fetch := TRUE;
269 END IF;
270 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
271 exit;
272 END IF;
273 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
274 --Modified code for bug fix 3649761
275 IF (QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
276 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE2') --Customer Name
277 THEN
278 VCHAR_COL1_NEW_LIST(I) := to_char(HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(to_number(VCHAR_COL1_ORIG_LIST(I))));
279 VCHAR_COL2_ORIG_LIST(I) := NULL;
280 VCHAR_COL2_NEW_LIST(I) := NULL;
281 ELSIF ((QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
282 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE11') --Ship To
283 OR
284 (QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
285 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE5') --Site Use
286 OR
287 (QUALIFIER_CONTEXT_LIST(I) = 'CUSTOMER' AND
288 QUALIFIER_ATTRIBUTE_LIST(I) = 'QUALIFIER_ATTRIBUTE14') --Bill To
289 )
290 THEN
291 VCHAR_COL2_NEW_LIST(I) := to_char(HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(to_number(VCHAR_COL2_ORIG_LIST(I))));
292 VCHAR_COL1_ORIG_LIST(I) := NULL;
293 VCHAR_COL1_NEW_LIST(I) := NULL;
294 END IF;
295
296 END LOOP;
297 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
298 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
299 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
300 MERGE_LOG_ID,
301 TABLE_NAME,
302 MERGE_HEADER_ID,
303 PRIMARY_KEY_ID,
304 VCHAR_COL1_ORIG,
305 VCHAR_COL1_NEW,
306 --Added the following 2 columns for bug fix 3649761
307 VCHAR_COL2_ORIG,
308 VCHAR_COL2_NEW,
309 ACTION_FLAG,
310 REQUEST_ID,
311 CREATED_BY,
312 CREATION_DATE,
313 LAST_UPDATE_LOGIN,
314 LAST_UPDATE_DATE,
315 LAST_UPDATED_BY
316 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
317 'QP_QUALIFIERS',
318 MERGE_HEADER_ID_LIST(I),
319 PRIMARY_KEY_ID_LIST(I),
320 VCHAR_COL1_ORIG_LIST(I),
321 VCHAR_COL1_NEW_LIST(I),
322 --Added the following 2 columns for bug fix 3649761
323 VCHAR_COL2_ORIG_LIST(I),
324 VCHAR_COL2_NEW_LIST(I),
325 'U',
326 req_id,
327 hz_utility_pub.CREATED_BY,
328 hz_utility_pub.CREATION_DATE,
329 hz_utility_pub.LAST_UPDATE_LOGIN,
330 hz_utility_pub.LAST_UPDATE_DATE,
331 hz_utility_pub.LAST_UPDATED_BY
332 );
333
334 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
335 UPDATE QP_QUALIFIERS yt SET
336 --Modified code for bug fix 3649761
337 qualifier_attr_value=decode(nvl(VCHAR_COL1_NEW_LIST(I),'x'), 'x',
338 VCHAR_COL2_NEW_LIST(I),
339 VCHAR_COL1_NEW_LIST(I))
340 , LAST_UPDATE_DATE=SYSDATE
341 , last_updated_by=arp_standard.profile.user_id
345 , PROGRAM_ID=arp_standard.profile.program_id
342 , last_update_login=arp_standard.profile.last_update_login
343 , REQUEST_ID=req_id
344 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
346 , PROGRAM_UPDATE_DATE=SYSDATE
347 WHERE qualifier_id=PRIMARY_KEY_ID_LIST(I);
348 l_count := l_count + SQL%ROWCOUNT;
349 IF l_last_fetch THEN
350 EXIT;
351 END IF;
352 END LOOP;
353
354 arp_message.set_name('AR','AR_ROWS_UPDATED');
355 arp_message.set_token('NUM_ROWS',to_char(l_count));
356 END IF;
357 EXCEPTION
358 WHEN OTHERS THEN
359 arp_message.set_line( 'Qualifier_Merge');
360 RAISE;
361 END Qualifier_Merge;
362
363
364 PROCEDURE Merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) IS
365 BEGIN
366
367 arp_message.set_line('QP_CUST_MERGE.Merge()+');
368
369 Agreement_Merge(req_id, set_num, process_mode);
370 Qualifier_Merge(req_id, set_num, process_mode);
371
372 arp_message.set_line('QP_CUST_MERGE.Merge()-');
373
374 EXCEPTION
375 when others then
376 raise;
377
378 END Merge;
379
380 END QP_CUST_MERGE;