[Home] [Help]
PACKAGE BODY: APPS.DPP_ACCOUNT_MERGE_PVT
Source
1 PACKAGE BODY DPP_ACCOUNT_MERGE_PVT AS
2 /* $Header: dppvamgb.pls 120.1 2007/12/07 07:21:31 sdasan noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_ACCOUNT_MERGE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'dppvamgb.pls';
6 ------------------------------------------------------------------------------
7
8 /*-------------------------------------------------------------
9 |
10 | PROCEDURE
11 | merge_claim_account
12 | DESCRIPTION :
13 | Account merge procedure for the table, dpp_customer_claims_all
14 |
15 | NOTES:
16 |
17 |--------------------------------------------------------------*/
18
19 PROCEDURE merge_claim_account (
20 req_id NUMBER,
21 set_num NUMBER,
22 process_mode VARCHAR2) IS
23
24 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
25 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
26 INDEX BY BINARY_INTEGER;
27
28 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
29
30 TYPE CUSTOMER_INV_LINE_ID_LIST_TYPE IS TABLE OF
31 DPP_CUSTOMER_CLAIMS_ALL.CUSTOMER_INV_LINE_ID%TYPE
32 INDEX BY BINARY_INTEGER;
33
34 PRIMARY_KEY_ID1_LIST CUSTOMER_INV_LINE_ID_LIST_TYPE;
35
36 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
37 HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE
38 INDEX BY BINARY_INTEGER;
39
40 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
41 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
42
43 l_profile_val VARCHAR2(30);
44
45 CURSOR merged_records IS
46 SELECT DISTINCT m.customer_merge_header_id
47 ,yt.customer_inv_line_id
48 ,m.customer_id
49 FROM dpp_customer_claims_all yt, ra_customer_merges m
50 WHERE (
51 yt.cust_account_id = m.duplicate_id
52 ) AND m.process_flag = 'N'
53 AND m.request_id = req_id
54 AND m.set_number = set_num;
55
56 l_last_fetch BOOLEAN := FALSE;
57 l_count NUMBER;
58
59 BEGIN
60
61 IF process_mode='LOCK' THEN
62 NULL;
63 ELSE
64 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
65 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','dpp_customer_claims_all',FALSE);
66 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
67 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
68
69 open merged_records;
70 LOOP
71 FETCH merged_records BULK COLLECT INTO
72 MERGE_HEADER_ID_LIST
73 , PRIMARY_KEY_ID1_LIST
74 , NUM_COL1_ORIG_LIST
75 ;
76 IF merged_records%NOTFOUND THEN
77 l_last_fetch := TRUE;
78 END IF;
79 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
80 exit;
81 END IF;
82
83
84 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
85 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
86 END LOOP;
87
88 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
89 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
90 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
91 MERGE_LOG_ID,
92 TABLE_NAME,
93 MERGE_HEADER_ID,
94 PRIMARY_KEY_ID1,
95 NUM_COL1_ORIG,
96 NUM_COL1_NEW,
97 ACTION_FLAG,
98 REQUEST_ID,
99 CREATED_BY,
100 CREATION_DATE,
101 LAST_UPDATE_LOGIN,
102 LAST_UPDATE_DATE,
103 LAST_UPDATED_BY
104 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
105 'dpp_customer_claims_all',
106 MERGE_HEADER_ID_LIST(I),
107 PRIMARY_KEY_ID1_LIST(I),
108 NUM_COL1_ORIG_LIST(I),
109 NUM_COL1_NEW_LIST(I),
110 'U',
111 req_id,
112 hz_utility_pub.CREATED_BY,
113 hz_utility_pub.CREATION_DATE,
114 hz_utility_pub.LAST_UPDATE_LOGIN,
115 hz_utility_pub.LAST_UPDATE_DATE,
116 hz_utility_pub.LAST_UPDATED_BY
117 );
118
119 END IF;
120 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
121
122 UPDATE dpp_customer_claims_all yt SET
123 cust_account_id = TO_CHAR(NUM_COL1_NEW_LIST(I))
124 , LAST_UPDATE_DATE = SYSDATE
125 , last_updated_by = arp_standard.profile.user_id
126 , last_update_login = arp_standard.profile.last_update_login
127 WHERE customer_inv_line_id = PRIMARY_KEY_ID1_LIST(I);
128 l_count := l_count + SQL%ROWCOUNT;
129 IF l_last_fetch THEN
130 EXIT;
131 END IF;
132 END LOOP;
133
134 arp_message.set_name('AR','AR_ROWS_UPDATED');
135 arp_message.set_token('NUM_ROWS',to_char(l_count));
136 END IF;
137 EXCEPTION
138 WHEN OTHERS THEN
139 arp_message.set_line( 'merge_claim_account');
140 RAISE;
141 END merge_claim_account;
142
143 /*-------------------------------------------------------------
144 |
145 | PROCEDURE
146 | merge_claim_account_log
147 | DESCRIPTION :
148 | Account merge procedure for the table, dpp_customer_claims_log
149 |
150 | NOTES:
151 |
152 |--------------------------------------------------------------*/
153
154 PROCEDURE merge_claim_account_log (
155 req_id NUMBER,
156 set_num NUMBER,
157 process_mode VARCHAR2) IS
158
159 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
160 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
161 INDEX BY BINARY_INTEGER;
162
163 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
164
165 TYPE LOG_ID_LIST_TYPE IS TABLE OF
166 DPP_CUSTOMER_CLAIMS_LOG.LOG_ID%TYPE
167 INDEX BY BINARY_INTEGER;
168
169 PRIMARY_KEY_ID1_LIST LOG_ID_LIST_TYPE;
170
171 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
172 HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE
173 INDEX BY BINARY_INTEGER;
174
175 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
176 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
177
178 l_profile_val VARCHAR2(30);
179
180 CURSOR merged_records IS
181 SELECT DISTINCT m.customer_merge_header_id
182 ,yt.log_id
183 ,m.customer_id
184 FROM dpp_customer_claims_log yt, ra_customer_merges m
185 WHERE (
186 yt.cust_account_id = m.duplicate_id
187 ) AND m.process_flag = 'N'
188 AND m.request_id = req_id
189 AND m.set_number = set_num;
190
191 l_last_fetch BOOLEAN := FALSE;
192 l_count NUMBER;
193
194 BEGIN
195
196 IF process_mode='LOCK' THEN
197 NULL;
198 ELSE
199 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
200 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','dpp_customer_claims_log',FALSE);
201 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
202 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
203
204 open merged_records;
205 LOOP
206 FETCH merged_records BULK COLLECT INTO
207 MERGE_HEADER_ID_LIST
208 , PRIMARY_KEY_ID1_LIST
209 , NUM_COL1_ORIG_LIST
210 ;
211 IF merged_records%NOTFOUND THEN
212 l_last_fetch := TRUE;
213 END IF;
214 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
215 exit;
216 END IF;
217
218
219 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
220 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
221 END LOOP;
222
223 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
224 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
225 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
226 MERGE_LOG_ID,
227 TABLE_NAME,
228 MERGE_HEADER_ID,
229 PRIMARY_KEY_ID1,
230 NUM_COL1_ORIG,
231 NUM_COL1_NEW,
232 ACTION_FLAG,
233 REQUEST_ID,
234 CREATED_BY,
235 CREATION_DATE,
236 LAST_UPDATE_LOGIN,
237 LAST_UPDATE_DATE,
238 LAST_UPDATED_BY
239 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
240 'dpp_customer_claims_log',
241 MERGE_HEADER_ID_LIST(I),
242 PRIMARY_KEY_ID1_LIST(I),
243 NUM_COL1_ORIG_LIST(I),
244 NUM_COL1_NEW_LIST(I),
245 'U',
246 req_id,
247 hz_utility_pub.CREATED_BY,
248 hz_utility_pub.CREATION_DATE,
249 hz_utility_pub.LAST_UPDATE_LOGIN,
250 hz_utility_pub.LAST_UPDATE_DATE,
251 hz_utility_pub.LAST_UPDATED_BY
252 );
253
254 END IF;
255 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
256
257 UPDATE dpp_customer_claims_log yt SET
258 cust_account_id = TO_CHAR(NUM_COL1_NEW_LIST(I))
259 , LAST_UPDATE_DATE = SYSDATE
260 , last_updated_by = arp_standard.profile.user_id
261 , last_update_login = arp_standard.profile.last_update_login
262 WHERE log_id = PRIMARY_KEY_ID1_LIST(I);
263 l_count := l_count + SQL%ROWCOUNT;
264 IF l_last_fetch THEN
265 EXIT;
266 END IF;
267 END LOOP;
268
269 arp_message.set_name('AR','AR_ROWS_UPDATED');
270 arp_message.set_token('NUM_ROWS',to_char(l_count));
271 END IF;
272 EXCEPTION
273 WHEN OTHERS THEN
274 arp_message.set_line( 'merge_claim_account_log');
275 RAISE;
276 END merge_claim_account_log;
277
278
279 END DPP_ACCOUNT_MERGE_PVT;