[Home] [Help]
PACKAGE BODY: APPS.PAP_CMERGE_BB1
Source
1 PACKAGE BODY PAP_CMERGE_BB1 AS
2 -- $Header: PAPCMR1B.pls 120.1 2005/08/05 00:27:56 rgandhi noship $
3 --
4 -- function to retrieve new and old customer id's.
5 --
6 FUNCTION RETRIEVE_AGMT_CUSTOMER_ID(
7 set_no IN NUMBER,
8 var_agmt_customer_id IN pa_agreements.customer_id%TYPE,
9 agmt_new_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE,/*file.sql.39*/
10 agmt_old_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE /*file.sql.39*/)
11 RETURN BOOLEAN IS
12 BEGIN
13 --
14 agmt_new_customer_id := NULL;
15 agmt_old_customer_id := NULL;
16 --
17 SELECT DISTINCT RACM.CUSTOMER_ID, RACM.DUPLICATE_ID INTO
18 agmt_new_customer_id, agmt_old_customer_id
19 FROM RA_CUSTOMER_MERGES RACM
20 WHERE RACM.DUPLICATE_ID = var_agmt_customer_id
21 AND RACM.PROCESS_FLAG = 'N'
22 AND RACM.SET_NUMBER = set_no;
23 --
24 RETURN TRUE;
25 --
26 EXCEPTION
27 WHEN NO_DATA_FOUND THEN
28 RETURN FALSE;
29 WHEN OTHERS THEN
30 RETURN FALSE;
31 END RETRIEVE_AGMT_CUSTOMER_ID;
32 --
33 FUNCTION CHECK_FOR_DUPLICATE_INDEX(
34 agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
35 var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
36 var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE )
37 --
38 RETURN BOOLEAN IS
39 dummy_customer_id pa_agreements.customer_id%TYPE;
40 dummy_agreement_num pa_agreements.agreement_num%TYPE;
41 dummy_agreement_type pa_agreements.agreement_type%TYPE;
42 --
43 BEGIN
44 --
45 SELECT CUSTOMER_ID, AGREEMENT_NUM, AGREEMENT_TYPE
46 INTO dummy_customer_id, dummy_agreement_num, dummy_agreement_type
47 FROM PA_AGREEMENTS PA
48 WHERE PA.CUSTOMER_ID = agmt_new_customer_id
49 AND PA.AGREEMENT_NUM = var_agmt_agreement_num
50 AND PA.AGREEMENT_TYPE = var_agmt_agreement_type;
51 --
52 RETURN TRUE;
53 --
54 EXCEPTION
55 --
56 WHEN NO_DATA_FOUND THEN
57 RETURN FALSE;
58 WHEN OTHERS THEN
59 RETURN FALSE;
60 --
61 END CHECK_FOR_DUPLICATE_INDEX;
62 --
63 FUNCTION UPDATE_FOR_DUPLICATE_INDEX(
64 agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
65 var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
66 var_agmt_agreement_id IN pa_agreements.agreement_id%TYPE,
67 var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE,
68 seq_index IN NUMBER,
69 duplicate_index_value OUT NOCOPY BOOLEAN ,/*file.sql.39*/
70 request_id IN Number,
71 cust_merge_head_id IN ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE)/*Added for TCA AUDIT */
72 --
73 RETURN BOOLEAN IS
74 --
75 /* Commented for enhancement 1593520
76 trunc_agreement_num VARCHAR2(20);
77 */
78 length_trunc_agreement_num NUMBER;
79 /* Commented for enhancement 1593520
80 final_agreement_num VARCHAR2(20);
81 */
82
83 /* Code change for enhancement 1593520 */
84 final_agreement_num PA_AGREEMENTS.agreement_num%TYPE;
85 trunc_agreement_num PA_AGREEMENTS.agreement_num%TYPE;
86 /* till here */
87 --
88 BEGIN
89 --
90 duplicate_index_value := FALSE;
91 trunc_agreement_num := RTRIM( var_agmt_agreement_num );
92 length_trunc_agreement_num := LENGTHB( trunc_agreement_num );
93 --
94 /* Commented out for enhancement 1593520 and rewritten just below this
95
96 if length_trunc_agreement_num <= 15 then
97 final_agreement_num := CONCAT(
98 SUBSTR(trunc_agreement_num, 1, length_trunc_agreement_num ), '???');
99 length_trunc_agreement_num := LENGTHB( final_agreement_num );
100 final_agreement_num := CONCAT(
101 SUBSTR(final_agreement_num, 1, length_trunc_agreement_num ),
102 TO_CHAR( seq_index) );
103 else
104 final_agreement_num := CONCAT(
105 SUBSTR( trunc_agreement_num,1, 15 ), '???' );
106 final_agreement_num := CONCAT(
107 SUBSTR(final_agreement_num, 1, 18 ), TO_CHAR( seq_index) );
108 end if;
109
110 Till here */
111
112 /* Changes starts from here for enhancement 1593520 */
113
114 if length_trunc_agreement_num <= 45 then
115 final_agreement_num := CONCAT(
116 SUBSTR(trunc_agreement_num, 1, length_trunc_agreement_num ), '???');
117 length_trunc_agreement_num := LENGTHB( final_agreement_num );
118 final_agreement_num := CONCAT(
119 SUBSTR(final_agreement_num, 1, length_trunc_agreement_num ),
120 TO_CHAR( seq_index) );
121 else
122 final_agreement_num := CONCAT(
123 SUBSTR( trunc_agreement_num,1, 45 ), '???' );
124 final_agreement_num := CONCAT(
125 SUBSTR(final_agreement_num, 1, 48 ), TO_CHAR( seq_index) );
126 end if;
127 /* till here */
128 --
129 /* Added for TCA audit */
130 IF pap_cmerge.g_audit_profile = 'Y' THEN
131 --
132 -- It inserts the data into HZ_CUSTOMER_MERGE_LOG table
133 -- for PA_AGREEMENTS table and stamps the new agreement
134 -- number and old agreement number.
135 --
136 INSERT INTO hz_customer_merge_log
137 ( MERGE_LOG_ID,
138 MERGE_HEADER_ID,
139 REQUEST_ID,
140 TABLE_NAME,
141 PRIMARY_KEY_ID,
142 VCHAR_COL1_ORIG,
143 VCHAR_COL1_NEW ,
144 ACTION_FLAG,
145 CREATED_BY,
146 CREATION_DATE ,
147 LAST_UPDATED_BY ,
148 LAST_UPDATE_DATE,
149 LAST_UPDATE_LOGIN )
150 VALUES(
151 HZ_CUSTOMER_MERGE_LOG_S.nextval,
152 cust_merge_head_id,
153 request_id,
154 'PA_AGREEMENTS_ALL',
155 var_agmt_agreement_id,
156 var_agmt_agreement_num,
157 final_agreement_num,
158 'U',
159 hz_utility_pub.CREATED_BY,
160 hz_utility_pub.CREATION_DATE,
161 hz_utility_pub.LAST_UPDATE_LOGIN,
162 hz_utility_pub.LAST_UPDATE_DATE,
163 hz_utility_pub.LAST_UPDATED_BY);
164 END IF;
165 /* End of TCA Audit */
166
167 UPDATE PA_AGREEMENTS PA -- bug 3891382.
168 SET PA.AGREEMENT_NUM = final_agreement_num
169 WHERE PA.CUSTOMER_ID = agmt_new_customer_id
170 AND PA.AGREEMENT_NUM = var_agmt_agreement_num
171 AND PA.AGREEMENT_TYPE = var_agmt_agreement_type;
172 --
173 RETURN TRUE;
174 --
175 EXCEPTION
176 --
177 WHEN DUP_VAL_ON_INDEX THEN
178 duplicate_index_value := TRUE;
179 RETURN FALSE;
180 WHEN NO_DATA_FOUND THEN
181 RETURN FALSE;
182 WHEN OTHERS THEN
183 RETURN FALSE;
184 --
185 END UPDATE_FOR_DUPLICATE_INDEX;
186 --
187 PROCEDURE MERGE_PA_AGREEMENTS ( req_id IN NUMBER, set_no IN NUMBER ) IS
188 CURSOR cursor_2 IS SELECT DISTINCT AG.CUSTOMER_ID,
189 ag.AGREEMENT_ID,
190 ag.AGREEMENT_NUM,
191 ag.AGREEMENT_TYPE,
192 RACM.CUSTOMER_ID,
193 RACM.CUSTOMER_MERGE_HEADER_ID
194 FROM pa_agreements ag,ra_customer_merges RACM --bug3891382
195 WHERE RACM.DUPLICATE_ID = AG.CUSTOMER_ID
196 AND RACM.PROCESS_FLAG = 'N'
197 AND RACM.SET_NUMBER = set_no
198 AND RACM.CUSTOMER_ID <> RACM.DUPLICATE_ID;
199 --
200
201 var_agmt_customer_id pa_agreements.customer_id%TYPE;
202 var_racm_customer_id ra_customer_merges.customer_id%TYPE;/*Added for TCA AUDIT */
203 var_cust_merge_header_id ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE;/*Added for TCA AUDIT */
204 var_agmt_agreement_id pa_agreements.agreement_id%TYPE; /* Added for Tca Audit*/
205 var_agmt_agreement_num pa_agreements.agreement_num%TYPE;
206 var_agmt_agreement_type pa_agreements.agreement_type%TYPE;
207 -- agmt_new_customer_id pa_agreements.customer_id%TYPE;
208 -- agmt_old_customer_id pa_agreements.customer_id%TYPE;
209 data_found BOOLEAN;
210 duplicate_index_value BOOLEAN;
211 seq_index NUMBER;
212 out_of_limit EXCEPTION;
213 total_record_upd_count NUMBER := 0;
214 --
215 BEGIN
216 --
217 -- update log file to indicate the module being executed.
218 --
219 ARP_MESSAGE.SET_LINE( 'PAP_CMERGE_BB2.MERGE_PA_AGREEMENTS()+' );
220 --
221 -- update log file to indicate the table being updated.
222 --
223 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
224 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_AGREEMENTS' );
225 --
226 OPEN cursor_2;
227 --
228 LOOP
229 --
230 -- fetch each row from pa_agreements table.
231 --
232 FETCH cursor_2 INTO var_agmt_customer_id,
233 var_agmt_agreement_id,
234 var_agmt_agreement_num,
235 var_agmt_agreement_type,
236 var_racm_customer_id,
237 var_cust_merge_header_id;
238 --
239 EXIT WHEN cursor_2%NOTFOUND;
240 --
241 seq_index := 0;
242 --
243 /*data_found := RETRIEVE_AGMT_CUSTOMER_ID( set_no,
244 var_agmt_customer_id,
245 agmt_new_customer_id,
246 agmt_old_customer_id );
247 --
248 if data_found then COMMENTED for TCA audit */
249 --
250 data_found := CHECK_FOR_DUPLICATE_INDEX( var_racm_customer_id,
251 var_agmt_agreement_num,
252 var_agmt_agreement_type );
253 if data_found then
254 --
255 <<loop_until_no_dup_index>>
256 --
257 seq_index := seq_index + 1;
258 --
259 if seq_index > 99 then
260 --
261 RAISE out_of_limit;
262 --
263 end if;
264 --
265 data_found := UPDATE_FOR_DUPLICATE_INDEX( var_racm_customer_id,
266 var_agmt_agreement_num,
267 var_agmt_agreement_id,
268 var_agmt_agreement_type,
269 seq_index,
270 duplicate_index_value ,
271 req_id,
272 var_cust_merge_header_id);/* Added for TCA audit */
273 if duplicate_index_value then
274 --
275 goto loop_until_no_dup_index;
276 --
277 end if;
278 --
279 end if;
280 --
281 /* Added for TCA audit */
282 --
283 IF pap_cmerge.g_audit_profile = 'Y' THEN
284 --
285 -- Inserts data into HZ_CUSTOMER_MERGE_LOG table for
286 -- PA_AGREEMENTS table and stamps agreement id with old
287 -- and new customer id .
288
289 INSERT INTO hz_customer_merge_log
290 ( MERGE_LOG_ID,
291 MERGE_HEADER_ID ,
292 REQUEST_ID,
293 TABLE_NAME,
294 PRIMARY_KEY_ID,
295 NUM_COL1_ORIG,
296 NUM_COL1_NEW ,
297 ACTION_FLAG,
298 CREATED_BY,
299 CREATION_DATE ,
300 LAST_UPDATED_BY ,
301 LAST_UPDATE_DATE,
302 LAST_UPDATE_LOGIN )
303 VALUES(
304 HZ_CUSTOMER_MERGE_LOG_S.nextval,
305 var_cust_merge_header_id,
306 req_id,
307 'PA_AGREEMENTS', -- bug 3891382.
308 var_agmt_agreement_id,
309 var_agmt_customer_id,
310 var_racm_customer_id,
311 'U',
312 hz_utility_pub.CREATED_BY,
313 hz_utility_pub.CREATION_DATE,
314 hz_utility_pub.LAST_UPDATE_LOGIN,
315 hz_utility_pub.LAST_UPDATE_DATE,
316 hz_utility_pub.LAST_UPDATED_BY);
317 END IF;
318
319 /*End of TCA audit*/
320
321 UPDATE pa_agreements PA -- bug 3891382.
322 SET CUSTOMER_ID = var_racm_customer_id,
323 LAST_UPDATE_DATE = SYSDATE,
324 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
325 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
326 WHERE
327 PA.CUSTOMER_ID = var_agmt_customer_id
328 AND PA.AGREEMENT_NUM = var_agmt_agreement_num
329 AND PA.AGREEMENT_TYPE = var_agmt_agreement_type;
330 --
331 total_record_upd_count := total_record_upd_count + SQL%ROWCOUNT;
332 --
333 /*end if; Commented for TCA audit*/
334 --
335 --
336 END LOOP;
337 --
338 CLOSE cursor_2;
339 --
340 -- update log file to indicate the total rows updated.
341 --
342 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
343 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
344 total_record_upd_count := 0;
345 --
346 -- update log file to indicate successful exit of this module.
347 --
348 ARP_MESSAGE.SET_LINE( 'PAP_CMERGE_BB2.MERGE_PA_AGREEMENTS()-' );
349 --
350 EXCEPTION
351 --
352 WHEN out_of_limit THEN
353 ARP_MESSAGE.SET_ERROR( 'Duplicate agreement number exceeding 99' );
354 ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS' );
355 RAISE;
356 WHEN OTHERS THEN
357 ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS' );
358 RAISE;
359 --
360 END MERGE_PA_AGREEMENTS;
361 --
362 END PAP_CMERGE_BB1;