1 PACKAGE BODY AR_MCC_MERGE AS
2 /* $Header: ARXCMCRB.pls 120.0 2005/05/05 05:02:24 bdhotkar noship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2001 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| ARXCMCRB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Package body of AR_MCC_MERGE |
13 --| |
14 --| PUBLIC PROCEDURES |
15 --| Customer_Merge |
16 --| Note: This procedure will be called by the main Customer Merge(TCA) |
17 --| feature |
18 --| |
19 --| HISTORY |
20 --| 09/07/2001 apsndit Created |
21 --| Copied this file from OEXCMCRB.pls |
22 --+======================================================================*/
23
24 --=================
25 -- CONSTANTS
26 --=================
27 G_AR_MCC_MERGE VARCHAR2(30) := 'AR_MCC_MERGE';
28
29 --====================
30 -- Debug log variables
31 --====================
32 g_log_level NUMBER := NULL; -- 0 for manual test
33 g_log_mode VARCHAR2(3) := 'OFF'; -- possible values: OFF, SQL, SRS
34
35 --=====================================
36 -- PRIVATE VARIABLES
37 --=====================================
38 g_count NUMBER := 0;
39
40 --========================================================================
41 -- PROCEDURE : Log_Initialize PRIVATE
42 -- COMMENT : Initializes the log facility. It should be called from
43 -- the top level procedure of each concurrent program
44 --=======================================================================--
45 PROCEDURE Log_Initialize
46 IS
47 BEGIN
48 g_log_level := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
49 IF g_log_level IS NULL THEN
50 g_log_mode := 'OFF';
51 ELSE
52 /*Big Number3731144: Repalced FND_PROFILE.Value with ARP_GLOBAL.request_id */
53 IF ARP_GLOBAL.request_id IS NOT NULL THEN
54 g_log_mode := 'SRS';
55 ELSE
56 g_log_mode := 'SQL';
57 END IF;
58 END IF;
59
60 END Log_Initialize;
61
62
63 --========================================================================
64 -- PROCEDURE : Log PRIVATE
65 -- PARAMETERS: p_level IN priority of the message - from
66 -- highest to lowest:
67 -- -- G_LOG_ERROR
68 -- -- G_LOG_EXCEPTION
69 -- -- G_LOG_EVENT
70 -- -- G_LOG_PROCEDURE
71 -- -- G_LOG_STATEMENT
72 -- p_msg IN message to be print on the log
73 -- file
74 -- COMMENT : Add an entry to the log
75 --========================================================================
76 PROCEDURE Log
77 ( p_priority IN NUMBER
78 , p_msg IN VARCHAR2
79 )
80 IS
81 BEGIN
82 IF ((g_log_mode <> 'OFF') AND (p_priority >= g_log_level))
83 THEN
84 IF g_log_mode = 'SQL'
85 THEN
86 -- SQL*Plus session: uncomment the next line during unit test
87 -- DBMS_OUTPUT.put_line(p_msg);
88 NULL;
89 ELSE
90 -- Concurrent request
91 FND_FILE.put_line
92 ( FND_FILE.log
93 , p_msg
94 );
95 END IF;
96 END IF;
97 EXCEPTION
98 WHEN OTHERS THEN
99 NULL;
100 END Log;
101
102 --=========================================================================
103 -- PROCEDURE : Customer_merge PUBLIC
104 -- PARAMETERS: req_id IN NUMBER Concurrent process request id
105 -- set_number IN NUMBER Set Number
106 -- process_mode IN VARCHAR2 Process mode of the called
107 -- program
108 -- COMMENT : This procedure deletes the records from the table
109 -- HZ_CREDIT_USAGES for the corresponding customer Ids in
110 -- RA_CUSTOMER_MERGES
111 --=========================================================================
112 PROCEDURE Customer_Merge
113 ( req_id IN NUMBER
114 ,set_number IN NUMBER
115 ,process_mode IN VARCHAR2
116 ) IS
117
118 -- Cursor to get the customer account being merged
119 -- duplicate_id is the Id identifying the customer account that is
120 -- being merged
121 -- for a given request id, more than one customer account can exist
122 -- for a given set number, more than one customer account can exist
123 -- for a combination of request id and set number, more than one customer
124 -- account can exist. Verified from Gautam Prothia,dev mgr, TCA.
125 CURSOR customer_account_dup_cur IS
126 SELECT
127 DISTINCT duplicate_id
128 FROM
129 RA_CUSTOMER_MERGES
130 WHERE process_flag = 'N'
131 AND request_id = req_id
132 AND set_number = set_number;
133
134 -- Cursor to get the customer merge header id for the customer account
135 -- that is being merged
136 CURSOR customer_merge_header_cur(c_duplicate_id ra_customer_merges.duplicate_id%TYPE)
137 IS
138 SELECT
139 customer_merge_header_id
140 FROM
141 RA_CUSTOMER_MERGES
142 WHERE duplicate_id = c_duplicate_id;
143
144
145 -- cursor to lock the rows in hz_credit_usages
146 CURSOR check_credit_usage_cur IS
147 SELECT credit_usage_id
148 FROM
149 HZ_CREDIT_USAGES
150 WHERE cust_acct_profile_amt_id IN
151 (SELECT cust_acct_profile_amt_id
152 FROM HZ_CUST_PROFILE_AMTS
153 WHERE cust_account_id IN
154 (SELECT DISTINCT duplicate_id
155 FROM RA_CUSTOMER_MERGES
156 WHERE process_flag = 'N'
157 AND request_id = req_id
158 AND set_number = set_number))
159 FOR UPDATE OF credit_usage_id NOWAIT;
160
161 -- ====================================
162 -- Variables for BULK COLLECT operation
163 -- ====================================
164 TYPE credit_usage_tab IS TABLE OF
165 hz_credit_usages.credit_usage_id%TYPE;
166
167 TYPE credit_profile_amt_tab IS TABLE OF
168 hz_credit_usages.credit_profile_amt_id%TYPE;
169
170 TYPE cust_acct_profile_amt_tab IS TABLE OF
171 hz_credit_usages.cust_acct_profile_amt_id%TYPE;
172
173 TYPE profile_class_amount_tab IS TABLE OF
174 hz_credit_usages.profile_class_amount_id%TYPE;
175
176 TYPE credit_usage_rule_set_tab IS TABLE OF
177 hz_credit_usages.credit_usage_rule_set_id%TYPE;
178
179
180 l_credit_usage_ids credit_usage_tab;
181 l_credit_profile_amt_ids credit_profile_amt_tab;
182 l_cust_acct_profile_amt_ids cust_acct_profile_amt_tab;
183 l_profile_class_amount_ids profile_class_amount_tab;
184 l_credit_usage_rule_set_ids credit_usage_rule_set_tab;
185
186 -- index variables
187 l_min_usage_idx BINARY_INTEGER;
188 l_max_usage_idx BINARY_INTEGER;
189
190 --=================
191 -- LOCAL VARIABLES
192 --================
193 l_duplicate_id RA_CUSTOMER_MERGES.duplicate_id%TYPE;
194 l_customer_merge_header_id RA_CUSTOMER_MERGES.customer_merge_header_id%TYPE;
195 l_total_count NUMBER;
196 -- variable to store the account merge profile option value
197 l_audit_acct_merge_flag VARCHAR2(3) := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
198 -- Delete flag
199 l_delete_flag VARCHAR2(1);
200
201
202 -- initialize maximum batch size
203 l_max_batch_size NUMBER := 1000;
204
205 -- error text variables
206 l_errorcode NUMBER;
207 l_errortext VARCHAR2(200);
208
209 BEGIN
210
211 -- initialize log
212 AR_MCC_MERGE.Log_Initialize;
213
214 -- initialize the message stack
215 FND_MSG_PUB.Initialize;
216
217 arp_message.set_line('AR_MCC_MERGE.Customer_Merge()+');
218
219 AR_MCC_MERGE.Log
220 (AR_MCC_MERGE.G_LOG_PROCEDURE
221 ,'Start of Proc:Customer Merge'
222 );
223
224 IF process_mode = 'LOCK' THEN
225 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
226 arp_message.set_token('TABLE_NAME', 'HZ_CREDIT_USAGES', FALSE);
227
228 open check_credit_usage_cur;
229 close check_credit_usage_cur;
230
231 AR_MCC_MERGE.Log
232 (AR_MCC_MERGE.G_LOG_EVENT
233 ,'Rows locked from HZ_CREDIT_USAGES'
234 );
235 ELSE
236 arp_message.set_line('AR_MCC_MERGE.Customer_Merge()+');
237
238 -- Get each customer account being merged
239 FOR cust_dup IN customer_account_dup_cur LOOP
240
241 l_duplicate_id := cust_dup.duplicate_id;
242 AR_MCC_MERGE.Log
243 (AR_MCC_MERGE.G_LOG_EVENT
244 ,'Customer Account Id:' || to_char(l_duplicate_id)
245 );
246
247 -- Get Customer Merge Header Id
248 OPEN customer_merge_header_cur(l_duplicate_id);
249
250 FETCH customer_merge_header_cur
251 INTO l_customer_merge_header_id;
252
253 CLOSE customer_merge_header_cur;
254
255 AR_MCC_MERGE.Log
256 (AR_MCC_MERGE.G_LOG_EVENT
257 ,'Customer Merge Header Id:' || to_char(l_customer_merge_header_id)
258 );
259
260 -- Select statement to get the columns of the credit usage belongs
261 -- to customer account being merged
262 -- These column values are used to store in the LOG table
263 SELECT
264 credit_usage_id
265 , credit_profile_amt_id
266 , cust_acct_profile_amt_id
267 , profile_class_amount_id
268 , credit_usage_rule_set_id
269 BULK COLLECT INTO
270 l_credit_usage_ids
271 , l_credit_profile_amt_ids
272 , l_cust_acct_profile_amt_ids
273 , l_profile_class_amount_ids
274 , l_credit_usage_rule_set_ids
275 FROM HZ_CREDIT_USAGES
276 WHERE cust_acct_profile_amt_id IN
277 (SELECT cust_acct_profile_amt_id
278 FROM HZ_CUST_PROFILE_AMTS
279 WHERE cust_account_id = l_duplicate_id );
280
281 IF SQL%FOUND THEN
282 -- set the delete flag
283 l_delete_flag := 'Y';
284 -- intialize the index variables
285 l_min_usage_idx := l_credit_usage_ids.FIRST;
286 l_max_usage_idx := l_credit_usage_ids.LAST;
287 ELSE
288 l_delete_flag := 'N';
289 END IF;
290
291 AR_MCC_MERGE.Log
292 (AR_MCC_MERGE.G_LOG_EVENT
293 ,'Credit Usage Delete Flag:' || (l_delete_flag)
294 );
295
296 -- check audit account merge profile is enabled
297 -- if the profile is enabled, then the value being deleted to
298 -- be inserted into the LOG table
299 IF l_audit_acct_merge_flag = 'ON' THEN
300 IF l_delete_flag = 'Y' THEN
301 FORALL l_usage_idx IN l_min_usage_idx .. l_max_usage_idx
302 INSERT INTO hz_customer_merge_log
303 ( merge_log_id
304 , table_name
305 , merge_header_id
306 , primary_key_id
307 , del_col1
308 , del_col2
309 , del_col3
310 , del_col4
311 , action_flag
312 , request_id
313 , created_by
314 , creation_date
315 , last_update_login
316 , last_update_date
317 , last_updated_by
318 )
319 VALUES
320 ( HZ_CUSTOMER_MERGE_LOG_S.nextval
321 , 'HZ_CREDIT_USAGES'
322 , l_customer_merge_header_id
323 , l_credit_usage_ids(l_usage_idx)
324 , l_credit_profile_amt_ids(l_usage_idx)
325 , l_cust_acct_profile_amt_ids(l_usage_idx)
326 , l_profile_class_amount_ids(l_usage_idx)
327 , l_credit_usage_rule_set_ids(l_usage_idx)
328 , 'D'
329 , req_id
330 , hz_utility_pub.created_by
331 , hz_utility_pub.creation_date
332 , hz_utility_pub.last_update_login
333 , hz_utility_pub.last_update_date
334 , hz_utility_pub.last_updated_by
335 );
336
337 AR_MCC_MERGE.Log
338 (AR_MCC_MERGE.G_LOG_EVENT
339 ,'Records inserted in LOG table'
340 );
341
342 END IF; -- delete flag check
343
344 END IF; -- account merge audit profile
345
346 -- delete only when the records exists
347 IF l_delete_flag = 'Y' THEN
348 -- Delete the records in full
349 FORALL l_usage_idx IN l_min_usage_idx .. l_max_usage_idx
350 DELETE
351 FROM HZ_CREDIT_USAGES
352 WHERE credit_usage_id = l_credit_usage_ids(l_usage_idx);
353
354 -- number of rows deleted
355 -- summation of all %bulk_rowcount
356 g_count := sql%rowcount;
357 l_total_count := g_count;
358
359 AR_MCC_MERGE.Log
360 (AR_MCC_MERGE.G_LOG_EVENT
361 ,'Total rows deleted: ' || to_char(l_total_count)
362 );
363
364 -- Total number of rows deleted
365 arp_message.set_name('AR', 'AR_ROWS_DELETED');
366 arp_message.set_token('NUM_ROWS', to_char(l_total_count));
367
368 END IF; -- delete flag check
369
370 END LOOP; -- customer account loop
371
372 END IF; -- check for process mode
373
374 arp_message.set_line('AR_MCC_MERGE.Customer_Merge()-');
375
376 EXCEPTION
377 WHEN OTHERS THEN
378 arp_message.set_error('AR_MCC_MERGE.Customer_Merge');
379 l_errorcode := SQLCODE;
380 l_errortext := SUBSTR(SQLERRM, 1,200);
381 AR_MCC_MERGE.Log
382 (AR_MCC_MERGE.G_LOG_EXCEPTION
383 ,'Others:' || to_char(l_errorcode) || l_errortext
384 );
385 raise;
386
387 END Customer_Merge;
388
389
390
391 END AR_MCC_MERGE;
[Home] [Help]