1 PACKAGE BODY LNS_MERGE_PKG as
2 /* $Header: LNS_MERGE_B.pls 120.0 2005/05/31 17:54:59 appldev noship $ */
3
4
5 PROCEDURE MERGE_LOAN_HEADERS(p_entity_name IN VARCHAR2,
6 p_from_id IN NUMBER,
7 p_to_id IN OUT NOCOPY NUMBER,
8 p_from_fk_id IN NUMBER,
9 p_to_fk_id IN NUMBER,
10 p_parent_entity IN VARCHAR2,
11 p_batch_id IN NUMBER,
12 p_batch_party_id IN NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2)
14 IS
15 BEGIN
16
17 x_return_status := FND_API.G_RET_STS_SUCCESS;
18
19 /*
20 If the Parent has NOT changed(i.e. Parent getting transferred)
21 then nothing needs to be done. Set Merged To Id is same as Merged From Id
22 and return
23 */
24
25 IF p_from_FK_id = p_to_FK_id THEN
26 p_to_id := p_from_id;
27 RETURN;
28 END IF;
29
30 /*
31 If the Parent has changed(i.e. Parent is getting merged),
32 then transfer the dependent record to the new parent.
33 Before transferring check if a similar dependent record exists on the new parent.
34 If a duplicate exists then do not transfer and return the id of the duplicate record as the Merged To Id.
35 */
36
37 /* updating PRIMARY_BORROWER_ID column */
38 UPDATE LNS_LOAN_HEADERS_ALL
39 SET PRIMARY_BORROWER_ID = p_To_FK_id,
40 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
41 last_updated_by = HZ_UTILITY_V2PUB.user_id,
42 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
43 request_id = HZ_UTILITY_V2PUB.request_id,
44 program_id = HZ_UTILITY_V2PUB.program_id
45 WHERE PRIMARY_BORROWER_ID = p_from_fk_id;
46
47 /* updating CONTACT_PERS_PARTY_ID column */
48 UPDATE LNS_LOAN_HEADERS_ALL
49 SET CONTACT_PERS_PARTY_ID = p_To_FK_id,
50 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
51 last_updated_by = HZ_UTILITY_V2PUB.user_id,
52 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
53 request_id = HZ_UTILITY_V2PUB.request_id,
54 program_id = HZ_UTILITY_V2PUB.program_id
55 WHERE CONTACT_PERS_PARTY_ID = p_from_fk_id;
56
57 /* updating CONTACT_REL_PARTY_ID column */
58 UPDATE LNS_LOAN_HEADERS_ALL
59 SET CONTACT_REL_PARTY_ID = p_To_FK_id,
60 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
61 last_updated_by = HZ_UTILITY_V2PUB.user_id,
62 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
63 request_id = HZ_UTILITY_V2PUB.request_id,
64 program_id = HZ_UTILITY_V2PUB.program_id
65 WHERE CONTACT_REL_PARTY_ID = p_from_fk_id;
66
67 RETURN;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
72 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
73 FND_MSG_PUB.ADD;
74 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
75
76 END MERGE_LOAN_HEADERS;
77
78
79
80 PROCEDURE MERGE_PARTICIPANTS(p_entity_name IN VARCHAR2,
81 p_from_id IN NUMBER,
82 p_to_id IN OUT NOCOPY NUMBER,
83 p_from_fk_id IN NUMBER,
84 p_to_fk_id IN NUMBER,
85 p_parent_entity IN VARCHAR2,
86 p_batch_id IN NUMBER,
87 p_batch_party_id IN NUMBER,
88 x_return_status OUT NOCOPY VARCHAR2)
89 IS
90 BEGIN
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 /*
95 If the Parent has NOT changed(i.e. Parent getting transferred)
96 then nothing needs to be done. Set Merged To Id is same as Merged From Id
97 and return
98 */
99
100 IF p_from_FK_id = p_to_FK_id THEN
101 p_to_id := p_from_id;
102 RETURN;
103 END IF;
104
105 /*
106 If the Parent has changed(i.e. Parent is getting merged),
107 then transfer the dependent record to the new parent.
108 Before transferring check if a similar dependent record exists on the new parent.
109 If a duplicate exists then do not transfer and return the id of the duplicate record as the Merged To Id.
110 */
111
112 /* updating HZ_PARTY_ID column */
113 UPDATE LNS_PARTICIPANTS
114 SET HZ_PARTY_ID = p_To_FK_id,
115 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
116 last_updated_by = HZ_UTILITY_V2PUB.user_id,
117 last_update_login = HZ_UTILITY_V2PUB.last_update_login
118 WHERE HZ_PARTY_ID = p_from_fk_id;
119
120 /* updating CONTACT_PERS_PARTY_ID column */
121 UPDATE LNS_PARTICIPANTS
122 SET CONTACT_PERS_PARTY_ID = p_To_FK_id,
123 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
124 last_updated_by = HZ_UTILITY_V2PUB.user_id,
125 last_update_login = HZ_UTILITY_V2PUB.last_update_login
126 WHERE CONTACT_PERS_PARTY_ID = p_from_fk_id;
127
128 /* updating CONTACT_REL_PARTY_ID column */
129 UPDATE LNS_PARTICIPANTS
130 SET CONTACT_REL_PARTY_ID = p_To_FK_id,
131 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
132 last_updated_by = HZ_UTILITY_V2PUB.user_id,
133 last_update_login = HZ_UTILITY_V2PUB.last_update_login
134 WHERE CONTACT_REL_PARTY_ID = p_from_fk_id;
135
136 RETURN;
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
141 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
142 FND_MSG_PUB.ADD;
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144
145 END MERGE_PARTICIPANTS;
146
147
148
149 PROCEDURE MERGE_LOAN_HEADERS_ACC (
150 req_id NUMBER,
151 set_num NUMBER,
152 process_mode VARCHAR2)
153 IS
154
155 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
156 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
157 INDEX BY BINARY_INTEGER;
158 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
159
160 TYPE LOAN_ID_LIST_TYPE IS TABLE OF
161 LNS_LOAN_HEADERS.LOAN_ID%TYPE
162 INDEX BY BINARY_INTEGER;
163 PRIMARY_KEY_ID1_LIST LOAN_ID_LIST_TYPE;
164
165 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
166 LNS_LOAN_HEADERS.CUST_ACCOUNT_ID%TYPE
167 INDEX BY BINARY_INTEGER;
168 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
169 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
170
171 l_profile_val VARCHAR2(30);
172 CURSOR merged_records IS
173 SELECT distinct CUSTOMER_MERGE_HEADER_ID
174 ,LOAN_ID
175 ,CUST_ACCOUNT_ID
176 FROM LNS_LOAN_HEADERS yt, ra_customer_merges m
177 WHERE
178 (yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID)
179 AND m.process_flag = 'N'
180 AND m.request_id = req_id
181 AND m.set_number = set_num;
182 l_last_fetch BOOLEAN := FALSE;
183 l_count NUMBER;
184
185 BEGIN
186
187 IF process_mode='LOCK' THEN
188 NULL;
189 ELSE
190
191 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
192 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','LNS_LOAN_HEADERS',FALSE);
193 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
194 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
195
196 open merged_records;
197 LOOP
198 FETCH merged_records BULK COLLECT INTO
199 MERGE_HEADER_ID_LIST
200 , PRIMARY_KEY_ID1_LIST
201 , NUM_COL1_ORIG_LIST;
202
203 IF merged_records%NOTFOUND THEN
204 l_last_fetch := TRUE;
205 END IF;
206
207 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
208 exit;
209 END IF;
210
211 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
212 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
213 END LOOP;
214
215 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
216 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
217 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
218 MERGE_LOG_ID,
219 TABLE_NAME,
220 MERGE_HEADER_ID,
221 PRIMARY_KEY_ID1,
222 NUM_COL1_ORIG,
223 NUM_COL1_NEW,
224 ACTION_FLAG,
225 REQUEST_ID,
226 CREATED_BY,
227 CREATION_DATE,
228 LAST_UPDATE_LOGIN,
229 LAST_UPDATE_DATE,
230 LAST_UPDATED_BY)
231 VALUES
232 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
233 'LNS_LOAN_HEADERS',
234 MERGE_HEADER_ID_LIST(I),
235 PRIMARY_KEY_ID1_LIST(I),
236 NUM_COL1_ORIG_LIST(I),
237 NUM_COL1_NEW_LIST(I),
238 'U',
239 req_id,
240 hz_utility_pub.CREATED_BY,
241 hz_utility_pub.CREATION_DATE,
242 hz_utility_pub.LAST_UPDATE_LOGIN,
243 hz_utility_pub.LAST_UPDATE_DATE,
244 hz_utility_pub.LAST_UPDATED_BY);
245
246 END IF;
247
248 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
249 UPDATE LNS_LOAN_HEADERS yt SET
250 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
251 , LAST_UPDATE_DATE=SYSDATE
252 , last_updated_by=arp_standard.profile.user_id
253 , last_update_login=arp_standard.profile.last_update_login
254 , REQUEST_ID=req_id
255 , PROGRAM_ID=arp_standard.profile.program_id
256 WHERE LOAN_ID=PRIMARY_KEY_ID1_LIST(I);
257
258 l_count := l_count + SQL%ROWCOUNT;
259 IF l_last_fetch THEN
260 EXIT;
261 END IF;
262
263 END LOOP;
264
265 arp_message.set_name('AR','AR_ROWS_UPDATED');
266 arp_message.set_token('NUM_ROWS',to_char(l_count));
267
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 arp_message.set_line( 'MERGE_LOAN_HEADERS_ACC');
273 RAISE;
274 END MERGE_LOAN_HEADERS_ACC;
275
276
277 PROCEDURE MERGE_PARTICIPANTS_ACC (
278 req_id NUMBER,
279 set_num NUMBER,
280 process_mode VARCHAR2)
281 IS
282
283 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
284 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
285 INDEX BY BINARY_INTEGER;
286 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
287
288 TYPE PARTICIPANT_ID_LIST_TYPE IS TABLE OF
289 LNS_PARTICIPANTS.PARTICIPANT_ID%TYPE
290 INDEX BY BINARY_INTEGER;
291 PRIMARY_KEY_ID1_LIST PARTICIPANT_ID_LIST_TYPE;
292
293 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
294 LNS_PARTICIPANTS.CUST_ACCOUNT_ID%TYPE
295 INDEX BY BINARY_INTEGER;
296 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
297 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
298
299 l_profile_val VARCHAR2(30);
300 CURSOR merged_records IS
301 SELECT distinct CUSTOMER_MERGE_HEADER_ID
302 ,PARTICIPANT_ID
303 ,CUST_ACCOUNT_ID
304 FROM LNS_PARTICIPANTS yt, ra_customer_merges m
305 WHERE (
306 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
307 ) AND m.process_flag = 'N'
308 AND m.request_id = req_id
309 AND m.set_number = set_num;
310
311 l_last_fetch BOOLEAN := FALSE;
312 l_count NUMBER;
313
314 BEGIN
315 IF process_mode='LOCK' THEN
316 NULL;
317
318 ELSE
319
320 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
321 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','LNS_PARTICIPANTS',FALSE);
322 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
323 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
324
325 open merged_records;
326 LOOP
327 FETCH merged_records BULK COLLECT INTO
328 MERGE_HEADER_ID_LIST
329 , PRIMARY_KEY_ID1_LIST
330 , NUM_COL1_ORIG_LIST;
331
332 IF merged_records%NOTFOUND THEN
333 l_last_fetch := TRUE;
334 END IF;
335 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
336 exit;
337 END IF;
338 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
339 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
340 END LOOP;
341
342 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
343
344 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
345 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
346 MERGE_LOG_ID,
347 TABLE_NAME,
348 MERGE_HEADER_ID,
349 PRIMARY_KEY_ID1,
350 NUM_COL1_ORIG,
351 NUM_COL1_NEW,
352 ACTION_FLAG,
353 REQUEST_ID,
354 CREATED_BY,
355 CREATION_DATE,
356 LAST_UPDATE_LOGIN,
357 LAST_UPDATE_DATE,
358 LAST_UPDATED_BY
359 ) VALUES (
360 HZ_CUSTOMER_MERGE_LOG_s.nextval,
361 'LNS_PARTICIPANTS',
362 MERGE_HEADER_ID_LIST(I),
363 PRIMARY_KEY_ID1_LIST(I),
364 NUM_COL1_ORIG_LIST(I),
365 NUM_COL1_NEW_LIST(I),
366 'U',
367 req_id,
368 hz_utility_pub.CREATED_BY,
369 hz_utility_pub.CREATION_DATE,
370 hz_utility_pub.LAST_UPDATE_LOGIN,
371 hz_utility_pub.LAST_UPDATE_DATE,
372 hz_utility_pub.LAST_UPDATED_BY
373 );
374
375 END IF;
376
377 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
378 UPDATE LNS_PARTICIPANTS yt SET
379 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
380 , LAST_UPDATE_DATE=SYSDATE
381 , last_updated_by=arp_standard.profile.user_id
382 , last_update_login=arp_standard.profile.last_update_login
383 WHERE PARTICIPANT_ID=PRIMARY_KEY_ID1_LIST(I);
384
385 l_count := l_count + SQL%ROWCOUNT;
386 IF l_last_fetch THEN
387 EXIT;
388 END IF;
389
390 END LOOP;
391
392 arp_message.set_name('AR','AR_ROWS_UPDATED');
393 arp_message.set_token('NUM_ROWS',to_char(l_count));
394
395 END IF;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 arp_message.set_line( 'MERGE_PARTICIPANTS_ACC');
400 RAISE;
401 END MERGE_PARTICIPANTS_ACC;
402
403
404 END LNS_MERGE_PKG;
[Home] [Help]