[Home] [Help]
PACKAGE BODY: APPS.CS_SR_ACCOUNT_MERGE_PKG
Source
1 PACKAGE BODY CS_SR_ACCOUNT_MERGE_PKG AS
2 /* $Header: cssramgb.pls 115.2 2004/01/22 01:38:40 spusegao noship $ */
3
4 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
5 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
6
7 TYPE NUM_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
8 TYPE VARCHAR2_30_TBL IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
9
10 PROCEDURE MERGE_CUST_ACCOUNTS (req_id IN NUMBER,
11 set_number IN NUMBER,
12 process_mode IN VARCHAR2 ) IS
13
14 message_text varchar2(255);
15 number_of_rows NUMBER;
16
17 BEGIN
18
19 ---Put the header in the report to identify the block to be run
20
21 arp_message.set_line('CRM_MERGE.SR_MERGE()+');
22
23 IF ( process_mode = 'LOCK' ) Then
24 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
25 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_ALL_B',FALSE );
26 ELSE
27 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
28 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_ALL_B',FALSE );
29
30 END IF;
31
32 ----Merge the CS_INCIDENTS table update the account_id
33
34 message_text := '***-- Procedure CS_MERGE_CUSTOMER_ACCOUNT_ID --**';
35 arp_message.set_line(message_text);
36
37 ---dbms_output.put_line('am going to call small proc');
38
39 CS_MERGE_CUST_ACCOUNT_ID( req_id, set_number, process_mode );
40
41 message_text := '***-- End CS_MERGE_CUSTOMER_ACCOUNT_ID --**';
42 arp_message.set_line(message_text);
43
44
45 ---Report that the process for CS_INCIDENTS is complete
46
47 IF ( process_mode = 'LOCK' ) Then
48 message_text := '** LOCKING completed for table CS_INCIDENTS_ALL_B **';
49 arp_message.set_line(message_text);
50 ELSE
51 message_text := '** MERGE completed for table CS_INCIDENTS_ALL_B **';
52 arp_message.set_line(message_text);
53 END IF;
54
55 arp_message.set_line('CRM_MERGE.SR_MERGE()-');
56
57 END MERGE_CUST_ACCOUNTS;
58
59 -- The following procedure merges the following columns from CS_INCIDENTS_ALL_B
60 -- account_id
61 -- bill_to_account_id - added for 11.5.9
62 -- ship_to_account_id - added for 11.5.9
63
64 PROCEDURE CS_MERGE_CUST_ACCOUNT_ID (
65 req_id NUMBER,
66 set_number NUMBER,
67 process_mode VARCHAR2) IS
68
69 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
70 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
71 INDEX BY BINARY_INTEGER;
72 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
73
74 TYPE INCIDENT_ID_LIST_TYPE IS TABLE OF
75 CS_INCIDENTS_ALL_B.INCIDENT_ID%TYPE
76 INDEX BY BINARY_INTEGER;
77 PRIMARY_KEY_ID_LIST INCIDENT_ID_LIST_TYPE;
78
79 TYPE ACCOUNT_ID_LIST_TYPE IS TABLE OF
80 CS_INCIDENTS_ALL_B.ACCOUNT_ID%TYPE
81 INDEX BY BINARY_INTEGER;
82 NUM_COL1_ORIG_LIST ACCOUNT_ID_LIST_TYPE;
83 NUM_COL1_NEW_LIST ACCOUNT_ID_LIST_TYPE;
84
85 TYPE BILL_TO_ACCOUNT_ID_LIST_TYPE IS TABLE OF
86 CS_INCIDENTS_ALL_B.BILL_TO_ACCOUNT_ID%TYPE
87 INDEX BY BINARY_INTEGER;
88 NUM_COL2_ORIG_LIST BILL_TO_ACCOUNT_ID_LIST_TYPE;
89 NUM_COL2_NEW_LIST BILL_TO_ACCOUNT_ID_LIST_TYPE;
90
91 TYPE SHIP_TO_ACCOUNT_ID_LIST_TYPE IS TABLE OF
92 CS_INCIDENTS_ALL_B.SHIP_TO_ACCOUNT_ID%TYPE
93 INDEX BY BINARY_INTEGER;
94 NUM_COL3_ORIG_LIST SHIP_TO_ACCOUNT_ID_LIST_TYPE;
95 NUM_COL3_NEW_LIST SHIP_TO_ACCOUNT_ID_LIST_TYPE;
96
97 CURSOR merged_records IS
98 SELECT distinct CUSTOMER_MERGE_HEADER_ID
99 ,INCIDENT_ID
100 ,ACCOUNT_ID
101 ,BILL_TO_ACCOUNT_ID
102 ,SHIP_TO_ACCOUNT_ID
103 ,LAST_UPDATE_PROGRAM_CODE
104 FROM CS_INCIDENTS_ALL_B yt,
105 ra_customer_merges m
106 WHERE ( yt.ACCOUNT_ID = m.DUPLICATE_ID
107 OR yt.BILL_TO_ACCOUNT_ID = m.DUPLICATE_ID
108 OR yt.SHIP_TO_ACCOUNT_ID = m.DUPLICATE_ID)
109 AND m.process_flag = 'N'
110 AND m.request_id = req_id
111 AND m.set_number = set_number;
112
113 CURSOR PARTY_CUR(p_cust_account_id NUMBER)is
114 SELECT PARTY_ID
115 FROM HZ_CUST_ACCOUNTS HCA
116 WHERE cust_account_id = p_cust_account_id;
117
118 CURSOR CUST_MERGE_CUR(req_id NUMBER, set_num NUMBER) IS
119 select CUSTOMER_ID, DUPLICATE_ID
120 from RA_CUSTOMER_MERGES RCM
121 Where rcm.request_id = req_id
122 And rcm.set_number = set_number
123 And rcm.process_flag = 'N';
124
125 l_profile_val VARCHAR2(30);
126 g_customer_id RA_CUSTOMER_MERGES.CUSTOMER_ID%TYPE;
127 g_duplicate_id RA_CUSTOMER_MERGES.DUPLICATE_ID%TYPE;
128
129 g_cust_party_id HZ_PARTIES.PARTY_ID%TYPE;
130 g_dup_party_id HZ_PARTIES.PARTY_ID%TYPE;
131
132 g_different_parties VARCHAR2(1) := 'N';
133 DIFFERENT_PARTIES EXCEPTION;
134 l_last_fetch BOOLEAN := FALSE;
135 l_count NUMBER;
136 l_last_update_program_code VARCHAR2_30_TBL;
137 l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
138 l_audit_id NUMBER ;
139 l_msg_count NUMBER;
140 l_msg_data VARCHAR2(1000);
141 l_return_status VARCHAR2(3);
142
143 BEGIN
144 IF process_mode='LOCK' THEN
145 NULL;
146 ELSE
147 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
148 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CS_INCIDENTS_ALL_B',FALSE);
149
150 HZ_ACCT_MERGE_UTIL.load_set(set_number, req_id);
151 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
152
153
154 Open CUST_MERGE_CUR(Req_Id, Set_Number);
155 Loop
156 Fetch CUST_MERGE_CUR
157 into g_customer_id,g_duplicate_id;
158
159 EXIT WHEN CUST_MERGE_CUR%NOTFOUND;
160
161 Open PARTY_CUR(g_customer_id);
162 Fetch PARTY_CUR into g_cust_party_id;
163 Close PARTY_CUR;
164
165 Open PARTY_CUR(g_duplicate_id);
166 Fetch PARTY_CUR into g_dup_party_id;
167 Close PARTY_CUR;
168
169 If g_cust_party_id <> g_dup_party_id Then
170 g_different_parties := 'Y';
171 End If;
172 End Loop;
173
174 Close CUST_MERGE_CUR;
175
176 open merged_records;
177
178 LOOP
179 FETCH merged_records BULK COLLECT INTO
180 MERGE_HEADER_ID_LIST
181 , PRIMARY_KEY_ID_LIST
182 , NUM_COL1_ORIG_LIST
183 , NUM_COL2_ORIG_LIST
184 , NUM_COL3_ORIG_LIST
185 , L_LAST_UPDATE_PROGRAM_CODE
186 limit 1000;
187
188 IF merged_records%NOTFOUND THEN
189 l_last_fetch := TRUE;
190 END IF;
191
192 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
193 exit;
194 END IF;
195
196 IF (MERGE_HEADER_ID_LIST.COUNT > 0 AND g_different_parties = 'Y' ) THEN
197 Close merged_records;
198 Raise DIFFERENT_PARTIES;
199 END IF;
200
201 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
202 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
203 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
204 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL3_ORIG_LIST(I));
205 END LOOP;
206
207 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
208
209 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
210 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
211 MERGE_LOG_ID, TABLE_NAME,
212 MERGE_HEADER_ID, PRIMARY_KEY_ID,
213 NUM_COL1_ORIG, NUM_COL1_NEW,
214 NUM_COL2_ORIG, NUM_COL2_NEW,
215 NUM_COL3_ORIG, NUM_COL3_NEW,
216 ACTION_FLAG, REQUEST_ID,
217 CREATED_BY, CREATION_DATE,
218 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE,
219 LAST_UPDATED_BY )
220 VALUES (
221 HZ_CUSTOMER_MERGE_LOG_s.nextval, 'CS_INCIDENTS_ALL_B',
222 MERGE_HEADER_ID_LIST(I), PRIMARY_KEY_ID_LIST(I),
223 NUM_COL1_ORIG_LIST(I), NUM_COL1_NEW_LIST(I),
224 NUM_COL2_ORIG_LIST(I), NUM_COL2_NEW_LIST(I),
225 NUM_COL3_ORIG_LIST(I), NUM_COL3_NEW_LIST(I),
226 'U', req_id,
227 hz_utility_pub.CREATED_BY, hz_utility_pub.CREATION_DATE,
228 hz_utility_pub.LAST_UPDATE_LOGIN, hz_utility_pub.LAST_UPDATE_DATE,
229 hz_utility_pub.LAST_UPDATED_BY );
230
231 END IF;
232
233 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
234 UPDATE CS_INCIDENTS_ALL_B yt SET
235 ACCOUNT_ID = NUM_COL1_NEW_LIST(I)
236 , BILL_TO_ACCOUNT_ID = NUM_COL2_NEW_LIST(I)
237 , SHIP_TO_ACCOUNT_ID = NUM_COL3_NEW_LIST(I)
238 , LAST_UPDATE_PROGRAM_CODE = 'ACCOUNT_MERGE'
239 , INCIDENT_LAST_MODIFIED_DATE = SYSDATE
240 , LAST_UPDATE_DATE = SYSDATE
241 , last_updated_by = arp_standard.profile.user_id
242 , last_update_login = arp_standard.profile.last_update_login
243 , REQUEST_ID = req_id
244 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
245 , PROGRAM_ID = arp_standard.profile.program_id
246 , PROGRAM_UPDATE_DATE = SYSDATE
247 WHERE INCIDENT_ID=PRIMARY_KEY_ID_LIST(I) ;
248
249 l_count := l_count + SQL%ROWCOUNT;
250 -- create audit record in cs_incidents_audit_b table for each service
251 -- request for which account_id, bill_to_account_id or ship_to_account_id is updated.
252
253 FOR i IN 1..PRIMARY_KEY_ID_LIST.COUNT
254
255 LOOP
256
257 CS_Servicerequest_UTIL.Prepare_Audit_Record (
258 p_api_version => 1,
259 p_request_id => PRIMARY_KEY_ID_LIST(I),
260 x_return_status => l_return_status,
261 x_msg_count => l_msg_count,
262 x_msg_data => l_msg_data,
263 x_audit_vals_rec => l_audit_vals_rec );
264
265 IF l_return_status <> FND_API.G_RET_STS_ERROR THEN
266
267 -- set the account_id /old_ account_id of audit record
268
269 IF NUM_COL1_ORIG_LIST(i) = NUM_COL1_NEW_LIST(i) THEN
270 l_audit_vals_rec.account_id := NUM_COL1_NEW_LIST(i) ;
271 l_audit_vals_rec.old_account_id := NUM_COL1_NEW_LIST(i);
272 ELSE
273 l_audit_vals_rec.account_id := NUM_COL1_NEW_LIST(i);
274 l_audit_vals_rec.old_account_id := NUM_COL1_ORIG_LIST(i);
275 END IF;
276
277 -- set the bill_to_account_id /old_bill_to_account_id of audit record
278
279 IF NUM_COL2_ORIG_LIST(i) = NUM_COL2_NEW_LIST(i) THEN
280 l_audit_vals_rec.bill_to_account_id := NUM_COL2_NEW_LIST(i);
281 l_audit_vals_rec.old_bill_to_account_id := NUM_COL2_NEW_LIST(i);
282 ELSE
283 l_audit_vals_rec.bill_to_account_id := NUM_COL2_NEW_LIST(i);
284 l_audit_vals_rec.old_bill_to_account_id := NUM_COL2_ORIG_LIST(i);
285 END IF;
286
287 -- set the customer_email_id /old_customer_email_id of audit record
288
289 IF NUM_COL3_ORIG_LIST(i) = NUM_COL2_NEW_LIST(i) THEN
290 l_audit_vals_rec.ship_to_account_id := NUM_COL2_NEW_LIST(i) ;
291 l_audit_vals_rec.old_ship_to_account_id := NUM_COL2_NEW_LIST(i);
292 ELSE
293 l_audit_vals_rec.ship_to_account_id := NUM_COL2_NEW_LIST(i);
294 l_audit_vals_rec.old_ship_to_account_id := NUM_COL2_ORIG_LIST(i) ;
295 END IF;
296
297 -- set the last_program_code/old_last_progream_code of audit record
298 l_audit_vals_rec.last_update_program_code := 'ACCOUNT_MERGE' ;
299 l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code (i);
300 l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
301 l_audit_vals_rec.updated_entity_id := PRIMARY_KEY_ID_LIST(I);
302 l_audit_vals_rec.entity_activity_code := 'U' ;
303 END IF;
304
305 CS_ServiceRequest_PVT.Create_Audit_Record (
306 p_api_version => 2.0,
307 x_return_status => l_return_status,
308 x_msg_count => l_msg_count,
309 x_msg_data => l_msg_data,
310 p_request_id => PRIMARY_KEY_ID_LIST(I),
311 p_audit_id => NULL,
312 p_audit_vals_rec => l_audit_vals_rec ,
313 p_user_id => G_USER_ID,
314 p_login_id => G_LOGIN_ID,
315 p_last_update_date => SYSDATE,
316 p_creation_date => SYSDATE,
317 p_comments => NULL,
318 x_audit_id => l_audit_id);
319
320 END LOOP;
321
322
323 IF l_last_fetch THEN
324 EXIT;
325 END IF;
326
327 END LOOP;
328
329 arp_message.set_name('AR','AR_ROWS_UPDATED');
330 arp_message.set_token('NUM_ROWS',to_char(l_count));
331 END IF;
332
333 EXCEPTION
334 WHEN DIFFERENT_PARTIES THEN
335 arp_message.set_name('CS','CS_ACCT_MERGE_NOT_ALLOWED');
336 RAISE;
337
338 WHEN OTHERS THEN
339 arp_message.set_line( 'CS_MERGE_CUST_ACCOUNT_ID');
340 RAISE;
341
342 END CS_MERGE_CUST_ACCOUNT_ID;
343
344 END CS_SR_ACCOUNT_MERGE_PKG ;
345