[Home] [Help]
PACKAGE BODY: APPS.AR_LATE_CHARGE_UPG
Source
1 PACKAGE BODY ar_late_charge_upg AS
2 /* $Header: ARLCUPB.pls 120.12.12010000.3 2009/08/20 10:28:36 rviriyal ship $ */
3
4 g_creation_date DATE := SYSDATE;
5
6 FUNCTION f_number(p_val IN VARCHAR2) RETURN NUMBER IS
7 l_num NUMBER;
8 BEGIN
9 IF p_val IS NULL THEN
10 RETURN NULL;
11 ELSE
12 RETURN (TO_NUMBER(p_val));
13 END IF;
14 EXCEPTION
15 WHEN OTHERS THEN
16 RETURN (NULL);
17 END;
18
19 FUNCTION f_date(p_value IN VARCHAR2) RETURN DATE
20 IS
21 BEGIN
22 IF p_value IS NULL THEN
23 RETURN NULL;
24 ELSE
25 RETURN (TO_DATE(p_value,'RRRR/MM/DD HH24:MI:SS'));
26 END IF;
27 EXCEPTION
28 WHEN OTHERS THEN
29 RETURN NULL;
30 END;
31
32
33 --Phase 5
34 PROCEDURE upgrade_schedule
35 (l_table_owner IN VARCHAR2, -- JG
36 l_table_name IN VARCHAR2, -- JG_ZZ_II_INT_RATES_ALL
37 l_script_name IN VARCHAR2, -- ar120lcjgr.sql
38 l_worker_id IN VARCHAR2,
39 l_num_workers IN VARCHAR2,
40 l_batch_size IN VARCHAR2)
41 IS
42
43 l_start_rowid rowid;
44 l_end_rowid rowid;
45 l_any_rows_to_process boolean;
46 l_rows_processed number := 0;
47
48 l_status VARCHAR2(10);
49 l_industry VARCHAR2(10);
50 l_res BOOLEAN := FALSE;
51 no_global EXCEPTION;
52 BEGIN
53
54 l_res := FND_INSTALLATION.GET(7003,7003,l_status,l_industry);
55
56 IF NOT(l_res) THEN
57 RAISE no_global;
58 END IF;
59
60
61 /* ------ Initialize the rowid ranges ------ */
62 ad_parallel_updates_pkg.initialize_rowid_range(
63 ad_parallel_updates_pkg.ROWID_RANGE,
64 l_table_owner,
65 l_table_name,
66 l_script_name,
67 l_worker_id,
68 l_num_workers,
69 l_batch_size, 0);
70
71 /* ------ Get rowid ranges ------ */
72 ad_parallel_updates_pkg.get_rowid_range(
73 l_start_rowid,
74 l_end_rowid,
75 l_any_rows_to_process,
76 l_batch_size,
77 TRUE);
78
79 WHILE ( l_any_rows_to_process = TRUE )
80 LOOP
81
82 l_rows_processed := 0;
83
84 INSERT ALL
85 WHEN rk = 1 THEN
86 INTO ar_charge_schedules(
87 SCHEDULE_ID
88 ,SCHEDULE_NAME
89 ,SCHEDULE_DESCRIPTION
90 ,OBJECT_VERSION_NUMBER
91 ,CREATED_BY
92 ,CREATION_DATE
93 ,LAST_UPDATED_BY
94 ,LAST_UPDATE_DATE
95 ,LAST_UPDATE_LOGIN)
96 VALUES
97 (AR_CHARGE_SCHEDULES_S.NEXTVAL
98 ,DECODE(lookup_type,'JGZZ_INT_INV_DAILY_RATE','D_UPG_','M_UPG_')||lookup_code
99 ,jg_description
100 ,1
101 ,-1
102 ,g_creation_date
103 ,-1
104 ,g_creation_date
105 ,-1)
106 SELECT lookup_type
107 ,lookup_code
108 ,jg_meaning
109 ,jg_description
110 ,jg_enabled_flag
111 ,jg_start_date
112 ,jg_end_date
113 ,rk
114 FROM
115 (SELECT lookup_type as lookup_type
116 ,lookup_code as lookup_code
117 ,meaning as jg_meaning
118 ,description as jg_description
119 ,enabled_flag as jg_enabled_flag
120 ,start_date_active as jg_start_date
121 ,end_date_active as jg_end_date
122 ,rank () over
123 (partition by lookup_type,lookup_code order by start_date_active asc) as rk
124 FROM JG_ZZ_II_INT_RATES_ALL
125 WHERE lookup_type IN ('JGZZ_INT_INV_DAILY_RATE','JGZZ_INT_INV_MONTHLY_RATE')
126 AND rowid >= l_start_rowid
127 AND rowid <= l_end_rowid)
128 WHERE rk = 1;
129
130
131 INSERT ALL
132 WHEN 1 = 1 THEN
133 INTO ar_charge_schedule_hdrs(
134 SCHEDULE_HEADER_ID
135 ,SCHEDULE_ID
136 ,SCHEDULE_HEADER_TYPE
137 ,AGING_BUCKET_ID
138 ,START_DATE
139 ,END_DATE
140 ,STATUS
141 ,OBJECT_VERSION_NUMBER
142 ,CREATED_BY
143 ,CREATION_DATE
144 ,LAST_UPDATED_BY
145 ,LAST_UPDATE_DATE
146 ,LAST_UPDATE_LOGIN)
147 VALUES
148 (AR_CHARGE_SCHEDULE_HDRS_S.NEXTVAL
149 ,schedule_id
150 ,'RATE'
151 ,6
152 ,jg_start_date
153 ,jg_end_date
154 ,'A'
155 ,1
156 ,-1
157 ,g_creation_date
158 ,-1
159 ,g_creation_date
160 ,-1)
161 SELECT jg.lookup_type as lookup_type
162 ,jg.lookup_code as lookup_code
163 ,jg.meaning as jg_meaning
164 ,jg.description as jg_description
165 ,jg.enabled_flag as jg_enabled_flag
166 ,jg.start_date_active as jg_start_date
167 ,jg.end_date_active as jg_end_date
168 ,cs.SCHEDULE_ID as schedule_id
169 FROM JG_ZZ_II_INT_RATES_ALL jg,
170 ar_charge_schedules cs
171 WHERE jg.lookup_type IN ('JGZZ_INT_INV_DAILY_RATE', 'JGZZ_INT_INV_MONTHLY_RATE')
172 AND DECODE(jg.lookup_type,'JGZZ_INT_INV_DAILY_RATE','D_UPG_','M_UPG_')||jg.lookup_code = cs.SCHEDULE_NAME
173 AND jg.rowid >= l_start_rowid
174 AND jg.rowid <= l_end_rowid;
175
176 INSERT ALL
177 WHEN 1 = 1 THEN
178 INTO ar_charge_schedule_lines(
179 SCHEDULE_LINE_ID
180 ,SCHEDULE_HEADER_ID
181 ,SCHEDULE_ID
182 ,AGING_BUCKET_ID
183 ,AGING_BUCKET_LINE_ID
184 ,RATE
185 ,OBJECT_VERSION_NUMBER
186 ,CREATED_BY
187 ,CREATION_DATE
188 ,LAST_UPDATED_BY
189 ,LAST_UPDATE_DATE
190 ,LAST_UPDATE_LOGIN)
191 VALUES
192 (AR_CHARGE_SCHEDULE_LINES_S.NEXTVAL
193 ,schedule_header_id
194 ,schedule_id
195 ,6 --AGING_BUCKET_ID
196 ,32 --AGING_BUCKET_LINE_ID
197 ,f_number(jg_meaning)
198 ,1
199 ,-1
200 ,g_creation_date
201 ,-1
202 ,g_creation_date
203 ,-1)
204 SELECT jg.lookup_type as lookup_type
205 ,jg.lookup_code as lookup_code
206 ,jg.meaning as jg_meaning
207 ,jg.description as jg_description
208 ,jg.enabled_flag as jg_enabled_flag
209 ,jg.start_date_active as jg_start_date
210 ,jg.end_date_active as jg_end_date
211 ,ch.schedule_id as schedule_id
212 ,ch.schedule_header_id as schedule_header_id
213 FROM JG_ZZ_II_INT_RATES_ALL jg,
214 ar_charge_schedules cs,
215 ar_charge_schedule_hdrs ch
216 WHERE jg.lookup_type IN ('JGZZ_INT_INV_DAILY_RATE', 'JGZZ_INT_INV_MONTHLY_RATE')
217 AND DECODE(jg.lookup_type,'JGZZ_INT_INV_DAILY_RATE','D_UPG_','M_UPG_')||jg.lookup_code = cs.SCHEDULE_NAME
218 AND cs.schedule_id = ch.schedule_id
219 AND jg.start_date_active = ch.start_date
220 AND ((jg.end_date_active = ch.end_date) OR (jg.end_date_active IS NULL AND ch.end_date IS NULL))
221 AND jg.rowid >= l_start_rowid
222 AND jg.rowid <= l_end_rowid;
223
224 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
225
226 ad_parallel_updates_pkg.processed_rowid_range(
227 l_rows_processed,
228 l_end_rowid);
229
230 commit;
231
232 ad_parallel_updates_pkg.get_rowid_range(
233 l_start_rowid,
234 l_end_rowid,
235 l_any_rows_to_process,
236 l_batch_size,
237 FALSE);
238
239 l_rows_processed := 0 ;
240
241 END LOOP ; /* end of WHILE loop */
242
243 EXCEPTION
244 WHEN no_global THEN NULL;
245
246 END;
247
248
249 --Phase 6
250 PROCEDURE upgrade_profile_amount
251 (l_table_owner IN VARCHAR2, -- AR
252 l_table_name IN VARCHAR2, -- HZ_CUST_PROFILE_AMTS
253 l_script_name IN VARCHAR2, -- ar120lccpa.sql
254 l_worker_id IN VARCHAR2,
255 l_num_workers IN VARCHAR2,
256 l_batch_size IN VARCHAR2)
257 IS
258 l_start_rowid rowid;
259 l_end_rowid rowid;
260 l_any_rows_to_process boolean;
261 l_rows_processed number := 0;
262
263 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID)
264 IS
265 SELECT /*+ ordered rowid(cpa) */
266 cpa.rowid cpa_rowid
267 , cpa.JGZZ_ATTRIBUTE5 EXCHANGE_RATE_TYPE
268 , DECODE(
269 NVL(f_number(cpa.JGZZ_ATTRIBUTE4),
270 cpa.MIN_FC_INVOICE_AMOUNT),
271 NULL,NULL,'AMOUNT') MIN_FC_INVOICE_OVERDUE_TYPE
272 , NVL(f_number(cpa.JGZZ_ATTRIBUTE4),cpa.MIN_FC_INVOICE_AMOUNT) MIN_FC_INVOICE_AMOUNT
273 , DECODE(cpa.min_fc_balance_amount,NULL,NULL,'AMOUNT') MIN_FC_BALANCE_OVERDUE_TYPE
274 , f_number(cpa.JGZZ_ATTRIBUTE3) MIN_INTEREST_CHARGE
275 , DECODE(cpa.jgzz_attribute7,NULL,
276 DECODE(cpa.interest_rate,NULL, NULL,'FIXED_RATE'),
277 'CHARGES_SCHEDULE') INTEREST_TYPE
278 , cs.schedule_id INTEREST_SCHEDULE_ID
279 , DECODE(cpa.jgzz_attribute3,NULL,NULL,'FIXED_AMOUNT') PENALTY_TYPE
280 , cpa.jgzz_attribute3 PENALTY_FIXED_AMOUNT
281 --{TCA Validation
282 , DECODE(cpa.jgzz_attribute7,NULL,cpa.interest_rate,NULL) interest_rate
283 --}
284 FROM hz_cust_profile_amts cpa,
285 ar_charge_schedules cs
286 WHERE cpa.rowid >= p_start_rowid
287 AND cpa.rowid <= p_end_rowid
288 AND cs.SCHEDULE_NAME(+) = DECODE(cpa.jgzz_attribute7,
289 'D','D_UPG_'||cpa.jgzz_attribute8,
290 'M','M_UPG_'||cpa.jgzz_attribute9,NULL);
291
292 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
293 l_EXCHANGE_RATE_TYPE DBMS_SQL.VARCHAR2_TABLE;
294 l_MIN_FC_INVOICE_OVERDUE_TYPE DBMS_SQL.VARCHAR2_TABLE;
295 l_MIN_FC_INVOICE_AMOUNT DBMS_SQL.NUMBER_TABLE;
296 l_MIN_FC_BALANCE_OVERDUE_TYPE DBMS_SQL.VARCHAR2_TABLE;
297 l_MIN_INTEREST_CHARGE DBMS_SQL.NUMBER_TABLE;
298 l_INTEREST_TYPE DBMS_SQL.VARCHAR2_TABLE;
299 l_INTEREST_SCHEDULE_ID DBMS_SQL.NUMBER_TABLE;
300 l_PENALTY_TYPE DBMS_SQL.VARCHAR2_TABLE;
301 l_PENALTY_FIXED_AMOUNT DBMS_SQL.NUMBER_TABLE;
302 l_interest_rate DBMS_SQL.NUMBER_TABLE;
303
304 g_bulk_fetch_rows NUMBER := 10000;
305 l_last_fetch BOOLEAN := FALSE;
306
307 BEGIN
308
309 /* ------ Initialize the rowid ranges ------ */
310 ad_parallel_updates_pkg.initialize_rowid_range(
311 ad_parallel_updates_pkg.ROWID_RANGE,
312 l_table_owner,
313 l_table_name,
314 l_script_name,
315 l_worker_id,
316 l_num_workers,
317 l_batch_size, 0);
318
319 /* ------ Get rowid ranges ------ */
320 ad_parallel_updates_pkg.get_rowid_range(
321 l_start_rowid,
322 l_end_rowid,
323 l_any_rows_to_process,
324 l_batch_size,
325 TRUE);
326
327 WHILE ( l_any_rows_to_process = TRUE )
328 LOOP
329
330 l_rows_processed := 0;
331
332 OPEN c(l_start_rowid, l_end_rowid);
333
334 LOOP
335
336 FETCH c BULK COLLECT INTO
337 l_rowid_tab ,
338 l_EXCHANGE_RATE_TYPE ,
339 l_MIN_FC_INVOICE_OVERDUE_TYPE,
340 l_MIN_FC_INVOICE_AMOUNT ,
341 l_MIN_FC_BALANCE_OVERDUE_TYPE,
342 l_MIN_INTEREST_CHARGE ,
343 l_INTEREST_TYPE ,
344 l_INTEREST_SCHEDULE_ID ,
345 l_PENALTY_TYPE ,
346 l_PENALTY_FIXED_AMOUNT ,
347 l_interest_rate
348 LIMIT g_bulk_fetch_rows;
349
350 IF c%NOTFOUND THEN
351 l_last_fetch := TRUE;
352 END IF;
353
354 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
355 EXIT;
356 END IF;
357
358 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
359 UPDATE hz_cust_profile_amts
360 SET EXCHANGE_RATE_TYPE = l_EXCHANGE_RATE_TYPE(i),
361 MIN_FC_INVOICE_OVERDUE_TYPE = l_MIN_FC_INVOICE_OVERDUE_TYPE(i),
362 MIN_FC_INVOICE_AMOUNT = l_MIN_FC_INVOICE_AMOUNT(i),
363 MIN_FC_BALANCE_OVERDUE_TYPE = l_MIN_FC_BALANCE_OVERDUE_TYPE(i),
364 MIN_INTEREST_CHARGE = l_MIN_INTEREST_CHARGE(i),
365 INTEREST_TYPE = l_INTEREST_TYPE(i),
366 INTEREST_SCHEDULE_ID = l_INTEREST_SCHEDULE_ID(i),
367 PENALTY_TYPE = l_PENALTY_TYPE(i),
368 PENALTY_FIXED_AMOUNT = l_PENALTY_FIXED_AMOUNT(i),
369 interest_rate = l_interest_rate(i),
370 last_update_date = g_creation_date,
371 last_updated_by = -1
372 WHERE rowid = l_rowid_tab(i);
373
374 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
375
376 IF l_last_fetch THEN
377 EXIT;
378 END IF;
379
380 END LOOP;
381
382 CLOSE c;
383
384
385 ad_parallel_updates_pkg.processed_rowid_range(
386 l_rows_processed,
387 l_end_rowid);
388
389 commit;
390
391 ad_parallel_updates_pkg.get_rowid_range(
392 l_start_rowid,
393 l_end_rowid,
394 l_any_rows_to_process,
395 l_batch_size,
396 FALSE);
397
398 l_rows_processed := 0 ;
399
400 END LOOP ; /* end of WHILE loop */
401
402 EXCEPTION
403 WHEN NO_DATA_FOUND THEN
404 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_profile_amount');
405 RAISE;
406
407 WHEN OTHERS THEN
408 --arp_standard.debug('OTHERS EXCEPTION: upgrade_profile_amount');
409 RAISE;
410
411 END upgrade_profile_amount;
412
413
414 --Phase 7
415 PROCEDURE upgrade_profile
416 (l_table_owner IN VARCHAR2, -- AR
417 l_table_name IN VARCHAR2, -- HZ_CUSTOMER_PROFILES
418 l_script_name IN VARCHAR2, -- ar120lccp.sql
419 l_worker_id IN VARCHAR2,
420 l_num_workers IN VARCHAR2,
421 l_batch_size IN VARCHAR2)
422 IS
423
424 l_start_rowid rowid;
425 l_end_rowid rowid;
426 l_any_rows_to_process boolean;
427 l_rows_processed number := 0;
428
429 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID) IS
430 SELECT /*+ ordered rowid(cp) */
431 cp.ROWID cp_rowid
432 , DECODE(JGZZ_ATTRIBUTE2,'LP','LATE',
433 'LO','OVERDUE_LATE',
434 'OI','OVERDUE','OVERDUE') LATE_CHARGE_CALCULATION_TRX
435 , DECODE(JGZZ_ATTRIBUTE9,'Y','Y','N') CREDIT_ITEMS_FLAG
436 , 'N' DISPUTED_TRANSACTIONS_FLAG
437 , DECODE(JGZZ_ATTRIBUTE1,'Y','INV','ADJ') LATE_CHARGE_TYPE
438 , f_NUMBER(JGZZ_ATTRIBUTE8) LATE_CHARGE_TERM_ID
439 , DECODE(cpa.dom,'M','MONTHLY','DAILY') INTEREST_CALCULATION_PERIOD
440 , DECODE(JGZZ_ATTRIBUTE5,'Y','Y','N') HOLD_CHARGED_INVOICES_FLAG
441 , 'N' MULTIPLE_INTEREST_RATES_FLAG
442 , f_date(JGZZ_ATTRIBUTE6) CHARGE_BEGIN_DATE
443 , DECODE(JGZZ_ATTRIBUTE1,'Y','Y',
444 NVL(INTEREST_CHARGES,'N')) INTEREST_CHARGES
445 , f_NUMBER(JGZZ_ATTRIBUTE4) Message_text_id
446 , decode(cons_inv_flag,'Y', decode(cons_bill_level,NULL,'SITE',NULL), NULL) cons_bill_level
447 FROM hz_customer_profiles cp,
448 (SELECT MAX(jgzz_attribute7) dom,
449 CUST_ACCOUNT_PROFILE_ID
450 FROM hz_cust_profile_amts
451 WHERE jgzz_attribute7 = 'M'
452 GROUP BY CUST_ACCOUNT_PROFILE_ID) cpa
453 WHERE cp.rowid >= p_start_rowid
454 AND cp.rowid <= p_end_rowid
455 AND cp.CUST_ACCOUNT_PROFILE_ID = cpa.CUST_ACCOUNT_PROFILE_ID(+);
456
457 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
458 l_LATE_CHARGE_CALCULATION_TRX DBMS_SQL.VARCHAR2_TABLE;
459 l_CREDIT_ITEMS_FLAG DBMS_SQL.VARCHAR2_TABLE;
460 l_DISPUTED_TRANSACTIONS_FLAG DBMS_SQL.VARCHAR2_TABLE;
461 l_LATE_CHARGE_TYPE DBMS_SQL.VARCHAR2_TABLE;
462 l_LATE_CHARGE_TERM_ID DBMS_SQL.NUMBER_TABLE;
463 l_INTEREST_CALCULATION_PERIOD DBMS_SQL.VARCHAR2_TABLE;
464 l_HOLD_CHARGED_INVOICES_FLAG DBMS_SQL.VARCHAR2_TABLE;
465 l_MULTIPLE_INTEREST_RATES_FLAG DBMS_SQL.VARCHAR2_TABLE;
466 l_CHARGE_BEGIN_DATE DBMS_SQL.DATE_TABLE;
467 l_INTEREST_CHARGES DBMS_SQL.VARCHAR2_TABLE;
468 l_Message_text_id DBMS_SQL.NUMBER_TABLE;
469 l_cons_bill_level DBMS_SQL.VARCHAR2_TABLE;
470
471
472 g_bulk_fetch_rows NUMBER := 10000;
473 l_last_fetch BOOLEAN := FALSE;
474
475 BEGIN
476
477 /* ------ Initialize the rowid ranges ------ */
478 ad_parallel_updates_pkg.initialize_rowid_range(
479 ad_parallel_updates_pkg.ROWID_RANGE,
480 l_table_owner,
481 l_table_name,
482 l_script_name,
483 l_worker_id,
484 l_num_workers,
485 l_batch_size, 0);
486
487 /* ------ Get rowid ranges ------ */
488 ad_parallel_updates_pkg.get_rowid_range(
489 l_start_rowid,
490 l_end_rowid,
491 l_any_rows_to_process,
492 l_batch_size,
493 TRUE);
494
495
496
497 WHILE ( l_any_rows_to_process = TRUE )
498 LOOP
499
500 l_rows_processed := 0;
501
502 OPEN c(l_start_rowid, l_end_rowid);
503
504 LOOP
505
506 FETCH c BULK COLLECT INTO
507 l_rowid_tab,
508 l_LATE_CHARGE_CALCULATION_TRX,
509 l_CREDIT_ITEMS_FLAG,
510 l_DISPUTED_TRANSACTIONS_FLAG,
511 l_LATE_CHARGE_TYPE,
512 l_LATE_CHARGE_TERM_ID,
513 l_INTEREST_CALCULATION_PERIOD,
514 l_HOLD_CHARGED_INVOICES_FLAG,
515 l_MULTIPLE_INTEREST_RATES_FLAG,
516 l_CHARGE_BEGIN_DATE,
517 l_INTEREST_CHARGES,
518 l_Message_text_id,
519 l_cons_bill_level
520 LIMIT g_bulk_fetch_rows;
521
522 IF c%NOTFOUND THEN
523 l_last_fetch := TRUE;
524 END IF;
525
526 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
527 EXIT;
528 END IF;
529
530 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
531 UPDATE hz_customer_profiles
532 SET LATE_CHARGE_CALCULATION_TRX = l_LATE_CHARGE_CALCULATION_TRX(i),
533 CREDIT_ITEMS_FLAG = l_CREDIT_ITEMS_FLAG(i),
534 DISPUTED_TRANSACTIONS_FLAG = l_DISPUTED_TRANSACTIONS_FLAG(i),
535 LATE_CHARGE_TYPE = l_LATE_CHARGE_TYPE(i),
536 LATE_CHARGE_TERM_ID = l_LATE_CHARGE_TERM_ID(i),
537 INTEREST_CALCULATION_PERIOD = l_INTEREST_CALCULATION_PERIOD(i),
538 HOLD_CHARGED_INVOICES_FLAG = l_HOLD_CHARGED_INVOICES_FLAG(i),
539 MULTIPLE_INTEREST_RATES_FLAG= l_MULTIPLE_INTEREST_RATES_FLAG(i),
540 CHARGE_BEGIN_DATE = l_CHARGE_BEGIN_DATE(i),
541 INTEREST_CHARGES = l_INTEREST_CHARGES(i),
542 Message_text_id = l_Message_text_id(i),
543 last_update_date = g_creation_date,
544 last_updated_by = -1,
545 cons_bill_level = l_cons_bill_level(i)
546 WHERE rowid = l_rowid_tab(i);
547
548 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
549
550 IF l_last_fetch THEN
551 EXIT;
552 END IF;
553
554 END LOOP;
555
556 CLOSE c;
557
558
559 ad_parallel_updates_pkg.processed_rowid_range(
560 l_rows_processed,
561 l_end_rowid);
562
563 commit;
564
565 ad_parallel_updates_pkg.get_rowid_range(
566 l_start_rowid,
567 l_end_rowid,
568 l_any_rows_to_process,
569 l_batch_size,
570 FALSE);
571
572 l_rows_processed := 0 ;
573
574 END LOOP ; /* end of WHILE loop */
575
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_profile');
579 RAISE;
580
581 WHEN OTHERS THEN
582 --arp_standard.debug('OTHERS EXCEPTION: upgrade_profile');
583 RAISE;
584
585 END upgrade_profile;
586
587
588 --Phase 6
589 PROCEDURE upgrade_profile_class_amount
590 (l_table_owner IN VARCHAR2, -- AR
591 l_table_name IN VARCHAR2, -- HZ_CUST_PROF_CLASS_AMTS
592 l_script_name IN VARCHAR2, -- arlccpca.sql
593 l_worker_id IN VARCHAR2,
594 l_num_workers IN VARCHAR2,
595 l_batch_size IN VARCHAR2)
596 IS
597 l_start_rowid rowid;
598 l_end_rowid rowid;
599 l_any_rows_to_process boolean;
600 l_rows_processed number := 0;
601
602 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID)
603 IS
604 SELECT /*+ ordered rowid(cpa) */
605 cpa.rowid cpa_rowid
606 , cpa.JGZZ_ATTRIBUTE5 EXCHANGE_RATE_TYPE
607 , DECODE(
608 NVL(f_number(cpa.JGZZ_ATTRIBUTE4),
609 cpa.MIN_FC_INVOICE_AMOUNT),
610 NULL,NULL,'AMOUNT') MIN_FC_INVOICE_OVERDUE_TYPE
611 , NVL(f_number(cpa.JGZZ_ATTRIBUTE4),cpa.MIN_FC_INVOICE_AMOUNT) MIN_FC_INVOICE_AMOUNT
612 , DECODE(cpa.min_fc_balance_amount,NULL,
613 DECODE(cpa.min_fc_balance_percent,NULL,NULL,'PERCENT'),
614 'AMOUNT') MIN_FC_BALANCE_OVERDUE_TYPE
615 , f_number(cpa.JGZZ_ATTRIBUTE3) MIN_INTEREST_CHARGE
616 , DECODE(cpa.jgzz_attribute7,NULL,
617 DECODE(cpa.interest_rate,NULL,NULL,'FIXED_RATE'),
618 'CHARGES_SCHEDULE') INTEREST_TYPE
619 , cs.schedule_id INTEREST_SCHEDULE_ID
620 , DECODE(cpa.jgzz_attribute3,NULL,NULL,'FIXED_AMOUNT') PENALTY_TYPE
621 , cpa.jgzz_attribute3 PENALTY_FIXED_AMOUNT
622 --{TCA Validation
623 , DECODE(cpa.jgzz_attribute7,NULL,cpa.interest_rate,NULL) interest_rate
624 --}
625 FROM hz_cust_prof_class_amts cpa,
626 ar_charge_schedules cs
627 WHERE cpa.rowid >= p_start_rowid
628 AND cpa.rowid <= p_end_rowid
629 AND cs.SCHEDULE_NAME(+) = DECODE(cpa.jgzz_attribute7,
630 'D','D_UPG_'||cpa.jgzz_attribute8,
631 'M','M_UPG_'||cpa.jgzz_attribute9,NULL);
632
633 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
634 l_EXCHANGE_RATE_TYPE DBMS_SQL.VARCHAR2_TABLE;
635 l_MIN_FC_INVOICE_OVERDUE_TYPE DBMS_SQL.VARCHAR2_TABLE;
636 l_MIN_FC_INVOICE_AMOUNT DBMS_SQL.NUMBER_TABLE;
637 l_MIN_FC_BALANCE_OVERDUE_TYPE DBMS_SQL.VARCHAR2_TABLE;
638 l_MIN_INTEREST_CHARGE DBMS_SQL.NUMBER_TABLE;
639 l_INTEREST_TYPE DBMS_SQL.VARCHAR2_TABLE;
640 l_INTEREST_SCHEDULE_ID DBMS_SQL.NUMBER_TABLE;
641 l_PENALTY_TYPE DBMS_SQL.VARCHAR2_TABLE;
642 l_PENALTY_FIXED_AMOUNT DBMS_SQL.NUMBER_TABLE;
643 l_interest_rate DBMS_SQL.NUMBER_TABLE;
644
645 g_bulk_fetch_rows NUMBER := 10000;
646 l_last_fetch BOOLEAN := FALSE;
647
648 BEGIN
649
650 /* ------ Initialize the rowid ranges ------ */
651 ad_parallel_updates_pkg.initialize_rowid_range(
652 ad_parallel_updates_pkg.ROWID_RANGE,
653 l_table_owner,
654 l_table_name,
655 l_script_name,
656 l_worker_id,
657 l_num_workers,
658 l_batch_size, 0);
659
660 /* ------ Get rowid ranges ------ */
661 ad_parallel_updates_pkg.get_rowid_range(
662 l_start_rowid,
663 l_end_rowid,
664 l_any_rows_to_process,
665 l_batch_size,
666 TRUE);
667
668 WHILE ( l_any_rows_to_process = TRUE )
669 LOOP
670
671 l_rows_processed := 0;
672
673 OPEN c(l_start_rowid, l_end_rowid);
674
675 LOOP
676
677 FETCH c BULK COLLECT INTO
678 l_rowid_tab ,
679 l_EXCHANGE_RATE_TYPE ,
680 l_MIN_FC_INVOICE_OVERDUE_TYPE,
681 l_MIN_FC_INVOICE_AMOUNT ,
682 l_MIN_FC_BALANCE_OVERDUE_TYPE,
683 l_MIN_INTEREST_CHARGE ,
684 l_INTEREST_TYPE ,
685 l_INTEREST_SCHEDULE_ID ,
686 l_PENALTY_TYPE ,
687 l_PENALTY_FIXED_AMOUNT ,
688 l_interest_rate
689 LIMIT g_bulk_fetch_rows;
690
691 IF c%NOTFOUND THEN
692 l_last_fetch := TRUE;
693 END IF;
694
695 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
696 EXIT;
697 END IF;
698
699 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
700 UPDATE hz_cust_prof_class_amts
701 SET EXCHANGE_RATE_TYPE = l_EXCHANGE_RATE_TYPE(i),
702 MIN_FC_INVOICE_OVERDUE_TYPE = l_MIN_FC_INVOICE_OVERDUE_TYPE(i),
703 MIN_FC_INVOICE_AMOUNT = l_MIN_FC_INVOICE_AMOUNT(i),
704 MIN_FC_BALANCE_OVERDUE_TYPE = l_MIN_FC_BALANCE_OVERDUE_TYPE(i),
705 MIN_INTEREST_CHARGE = l_MIN_INTEREST_CHARGE(i),
706 INTEREST_TYPE = l_INTEREST_TYPE(i),
707 INTEREST_SCHEDULE_ID = l_INTEREST_SCHEDULE_ID(i),
708 PENALTY_TYPE = l_PENALTY_TYPE(i),
709 PENALTY_FIXED_AMOUNT = l_PENALTY_FIXED_AMOUNT(i),
710 last_update_date = g_creation_date,
711 last_updated_by = -1,
712 interest_rate = l_interest_rate(i)
713 WHERE rowid = l_rowid_tab(i);
714
715 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
716
717 IF l_last_fetch THEN
718 EXIT;
719 END IF;
720
721 END LOOP;
722
723 CLOSE c;
724
725
726 ad_parallel_updates_pkg.processed_rowid_range(
727 l_rows_processed,
728 l_end_rowid);
729
730 commit;
731
732 ad_parallel_updates_pkg.get_rowid_range(
733 l_start_rowid,
734 l_end_rowid,
735 l_any_rows_to_process,
736 l_batch_size,
737 FALSE);
738
739 l_rows_processed := 0 ;
740
741 END LOOP ; /* end of WHILE loop */
742
743 EXCEPTION
744 WHEN NO_DATA_FOUND THEN
745 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_profile_class_amount');
746 RAISE;
747
748 WHEN OTHERS THEN
749 --arp_standard.debug('OTHERS EXCEPTION: upgrade_profile_class_amount');
750 RAISE;
751
752 END upgrade_profile_class_amount;
753
754
755 --Phase 7
756 PROCEDURE upgrade_profile_class
757 (l_table_owner IN VARCHAR2, -- AR
758 l_table_name IN VARCHAR2, -- HZ_CUST_PROFILE_CLASSES
759 l_script_name IN VARCHAR2, -- ar120lccpc.sql
760 l_worker_id IN VARCHAR2,
761 l_num_workers IN VARCHAR2,
762 l_batch_size IN VARCHAR2)
763 IS
764
765 l_start_rowid rowid;
766 l_end_rowid rowid;
767 l_any_rows_to_process boolean;
768 l_rows_processed number := 0;
769
770 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID) IS
771 SELECT /*+ ordered rowid(cp) */
772 cp.ROWID cp_rowid
773 , DECODE(JGZZ_ATTRIBUTE2,'LP','LATE',
774 'LO','OVERDUE_LATE',
775 'OI','OVERDUE','OVERDUE') LATE_CHARGE_CALCULATION_TRX
776 , DECODE(JGZZ_ATTRIBUTE9,'Y','Y','N') CREDIT_ITEMS_FLAG
777 , 'N' DISPUTED_TRANSACTIONS_FLAG
778 , DECODE(JGZZ_ATTRIBUTE1,'Y','INV','ADJ') LATE_CHARGE_TYPE
779 , f_NUMBER(JGZZ_ATTRIBUTE8) LATE_CHARGE_TERM_ID
780 , DECODE(cpa.dom,'M','MONTHLY','DAILY') INTEREST_CALCULATION_PERIOD
781 , DECODE(JGZZ_ATTRIBUTE5,'Y','Y','N') HOLD_CHARGED_INVOICES_FLAG
782 , 'N' MULTIPLE_INTEREST_RATES_FLAG
783 , f_date(JGZZ_ATTRIBUTE6) CHARGE_BEGIN_DATE
784 , DECODE(JGZZ_ATTRIBUTE1,'Y','Y',
785 NVL(INTEREST_CHARGES,'N')) INTEREST_CHARGES
786 , f_NUMBER(JGZZ_ATTRIBUTE4) Message_text_id
787 FROM hz_cust_profile_classes cp,
788 (SELECT MAX(jgzz_attribute7) dom,
789 PROFILE_CLASS_ID
790 FROM hz_cust_prof_class_amts
791 WHERE jgzz_attribute7 = 'M'
792 GROUP BY PROFILE_CLASS_ID) cpa
793 WHERE cp.rowid >= p_start_rowid
794 AND cp.rowid <= p_end_rowid
795 AND cp.PROFILE_CLASS_ID = cpa.PROFILE_CLASS_ID(+);
796
797 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
798 l_LATE_CHARGE_CALCULATION_TRX DBMS_SQL.VARCHAR2_TABLE;
799 l_CREDIT_ITEMS_FLAG DBMS_SQL.VARCHAR2_TABLE;
800 l_DISPUTED_TRANSACTIONS_FLAG DBMS_SQL.VARCHAR2_TABLE;
801 l_LATE_CHARGE_TYPE DBMS_SQL.VARCHAR2_TABLE;
802 l_LATE_CHARGE_TERM_ID DBMS_SQL.NUMBER_TABLE;
803 l_INTEREST_CALCULATION_PERIOD DBMS_SQL.VARCHAR2_TABLE;
804 l_HOLD_CHARGED_INVOICES_FLAG DBMS_SQL.VARCHAR2_TABLE;
805 l_MULTIPLE_INTEREST_RATES_FLAG DBMS_SQL.VARCHAR2_TABLE;
806 l_CHARGE_BEGIN_DATE DBMS_SQL.DATE_TABLE;
807 l_INTEREST_CHARGES DBMS_SQL.VARCHAR2_TABLE;
808 l_Message_text_id DBMS_SQL.NUMBER_TABLE;
809
810
811
812 g_bulk_fetch_rows NUMBER := 10000;
813 l_last_fetch BOOLEAN := FALSE;
814
815 BEGIN
816
817 /* ------ Initialize the rowid ranges ------ */
818 ad_parallel_updates_pkg.initialize_rowid_range(
819 ad_parallel_updates_pkg.ROWID_RANGE,
820 l_table_owner,
821 l_table_name,
822 l_script_name,
823 l_worker_id,
824 l_num_workers,
825 l_batch_size, 0);
826
827 /* ------ Get rowid ranges ------ */
828 ad_parallel_updates_pkg.get_rowid_range(
829 l_start_rowid,
830 l_end_rowid,
831 l_any_rows_to_process,
832 l_batch_size,
833 TRUE);
834
835
836
837 WHILE ( l_any_rows_to_process = TRUE )
838 LOOP
839
840 l_rows_processed := 0;
841
842 OPEN c(l_start_rowid, l_end_rowid);
843
844 LOOP
845
846 FETCH c BULK COLLECT INTO
847 l_rowid_tab,
848 l_LATE_CHARGE_CALCULATION_TRX,
849 l_CREDIT_ITEMS_FLAG,
850 l_DISPUTED_TRANSACTIONS_FLAG,
851 l_LATE_CHARGE_TYPE,
852 l_LATE_CHARGE_TERM_ID,
853 l_INTEREST_CALCULATION_PERIOD,
854 l_HOLD_CHARGED_INVOICES_FLAG,
855 l_MULTIPLE_INTEREST_RATES_FLAG,
856 l_CHARGE_BEGIN_DATE,
857 l_INTEREST_CHARGES,
858 l_Message_text_id
859 LIMIT g_bulk_fetch_rows;
860
861 IF c%NOTFOUND THEN
862 l_last_fetch := TRUE;
863 END IF;
864
865 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
866 EXIT;
867 END IF;
868
869 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
870 UPDATE hz_cust_profile_classes
871 SET LATE_CHARGE_CALCULATION_TRX = l_LATE_CHARGE_CALCULATION_TRX(i),
872 CREDIT_ITEMS_FLAG = l_CREDIT_ITEMS_FLAG(i),
873 DISPUTED_TRANSACTIONS_FLAG = l_DISPUTED_TRANSACTIONS_FLAG(i),
874 LATE_CHARGE_TYPE = l_LATE_CHARGE_TYPE(i),
875 LATE_CHARGE_TERM_ID = l_LATE_CHARGE_TERM_ID(i),
876 INTEREST_CALCULATION_PERIOD = l_INTEREST_CALCULATION_PERIOD(i),
877 HOLD_CHARGED_INVOICES_FLAG = l_HOLD_CHARGED_INVOICES_FLAG(i),
878 MULTIPLE_INTEREST_RATES_FLAG= l_MULTIPLE_INTEREST_RATES_FLAG(i),
879 CHARGE_BEGIN_DATE = l_CHARGE_BEGIN_DATE(i),
880 INTEREST_CHARGES = l_INTEREST_CHARGES(i),
881 Message_text_id = l_Message_text_id(i),
882 last_update_date = g_creation_date,
883 last_updated_by = -1
884 WHERE rowid = l_rowid_tab(i);
885
886 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
887
888 IF l_last_fetch THEN
889 EXIT;
890 END IF;
891
892 END LOOP;
893
894 CLOSE c;
895
896
897 ad_parallel_updates_pkg.processed_rowid_range(
898 l_rows_processed,
899 l_end_rowid);
900
901 commit;
902
903 ad_parallel_updates_pkg.get_rowid_range(
904 l_start_rowid,
905 l_end_rowid,
906 l_any_rows_to_process,
907 l_batch_size,
908 FALSE);
909
910 l_rows_processed := 0 ;
911
912 END LOOP ; /* end of WHILE loop */
913
914 EXCEPTION
915 WHEN NO_DATA_FOUND THEN
916 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_profile_class');
917 RAISE;
918
919 WHEN OTHERS THEN
920 --arp_standard.debug('OTHERS EXCEPTION: upgrade_profile_class');
921 RAISE;
922
923 END upgrade_profile_class;
924
925
926
927 --Upgrade ar_payment_schedules_for_adj
928 --Phase 5
929 PROCEDURE upgrade_ps_for_adj
930 (l_table_owner IN VARCHAR2, -- AR
931 l_table_name IN VARCHAR2, -- HZ_CUST_SITE_USES_ALL
932 l_script_name IN VARCHAR2, -- ar120lccsups.sql
933 l_worker_id IN VARCHAR2,
934 l_num_workers IN VARCHAR2,
935 l_batch_size IN VARCHAR2)
936 IS
937
938 l_start_rowid rowid;
939 l_end_rowid rowid;
940 l_any_rows_to_process boolean;
941 l_rows_processed number := 0;
942
943 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID) IS
944 SELECT trun.psch_rowid,
945 trun.csu_last_charge_date
946 FROM
947 (SELECT /*+ ordered rowid(csu) use_nl(psch,adj)
948 INDEX(psch ar_payment_schedules_n5)
949 INDEX(adj ar_adjustments_n3) */
950 adj.adjustment_id as adj_id,
951 psch.rowid as psch_rowid,
952 csu.last_accrue_charge_date as csu_last_charge_date,
953 rank () over (partition by adj.payment_schedule_id order by adjustment_id desc) as rk
954 FROM hz_cust_site_uses_all csu,
955 ar_payment_schedules_all psch,
956 ar_adjustments_all adj
957 WHERE csu.rowid >= p_start_rowid
958 AND csu.rowid <= p_end_rowid
959 AND csu.last_accrue_charge_date IS NOT NULL
960 AND csu.SITE_USE_ID = psch.CUSTOMER_SITE_USE_ID
961 AND psch.status = 'OP'
962 AND psch.last_charge_date IS NULL
963 AND psch.payment_schedule_id = adj.payment_schedule_id
964 AND adj.status = 'A'
965 AND adj.type = 'CHARGES'
966 ) trun
967 WHERE trun.rk = 1;
968
969 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
970 l_csu_last_charge_date DBMS_SQL.DATE_TABLE;
971
972 g_bulk_fetch_rows NUMBER := 10000;
973 l_last_fetch BOOLEAN := FALSE;
974
975 BEGIN
976
977 /* ------ Initialize the rowid ranges ------ */
978 ad_parallel_updates_pkg.initialize_rowid_range(
979 ad_parallel_updates_pkg.ROWID_RANGE,
980 l_table_owner,
981 l_table_name,
982 l_script_name,
983 l_worker_id,
984 l_num_workers,
985 l_batch_size, 0);
986
987 /* ------ Get rowid ranges ------ */
988 ad_parallel_updates_pkg.get_rowid_range(
989 l_start_rowid,
990 l_end_rowid,
991 l_any_rows_to_process,
992 l_batch_size,
993 TRUE);
994
995
996
997 WHILE ( l_any_rows_to_process = TRUE )
998 LOOP
999
1000 l_rows_processed := 0;
1001
1002 OPEN c(l_start_rowid, l_end_rowid);
1003
1004 LOOP
1005
1006 FETCH c BULK COLLECT INTO
1007 l_rowid_tab ,
1008 l_csu_last_charge_date
1009 LIMIT g_bulk_fetch_rows;
1010
1011 IF c%NOTFOUND THEN
1012 l_last_fetch := TRUE;
1013 END IF;
1014
1015 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
1016 EXIT;
1017 END IF;
1018
1019 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
1020 UPDATE ar_payment_schedules_all
1021 SET last_charge_date = l_csu_last_charge_date(i)
1022 WHERE rowid = l_rowid_tab(i);
1023
1024 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1025
1026 IF l_last_fetch THEN
1027 EXIT;
1028 END IF;
1029
1030 END LOOP;
1031
1032 CLOSE c;
1033
1034
1035 ad_parallel_updates_pkg.processed_rowid_range(
1036 l_rows_processed,
1037 l_end_rowid);
1038
1039 commit;
1040
1041 ad_parallel_updates_pkg.get_rowid_range(
1042 l_start_rowid,
1043 l_end_rowid,
1044 l_any_rows_to_process,
1045 l_batch_size,
1046 FALSE);
1047
1048 l_rows_processed := 0 ;
1049
1050 END LOOP ; /* end of WHILE loop */
1051
1052 EXCEPTION
1053 WHEN NO_DATA_FOUND THEN
1054 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_ps_for_adj');
1055 RAISE;
1056
1057 WHEN OTHERS THEN
1058 --arp_standard.debug('OTHERS EXCEPTION: upgrade_ps_for_adj');
1059 RAISE;
1060
1061 END upgrade_ps_for_adj;
1062
1063
1064
1065
1066
1067 --Phase 5
1068 PROCEDURE upgrade_lc_sysp
1069 (l_table_owner IN VARCHAR2, -- AR
1070 l_table_name IN VARCHAR2, -- AR_SYSTEM_PARAMETERS_ALL
1071 l_script_name IN VARCHAR2, -- ar120lcsysp.sql
1072 l_worker_id IN VARCHAR2,
1073 l_num_workers IN VARCHAR2,
1074 l_batch_size IN VARCHAR2)
1075 IS
1076
1077 l_start_rowid rowid;
1078 l_end_rowid rowid;
1079 l_any_rows_to_process boolean;
1080 l_rows_processed number := 0;
1081
1082 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID) IS
1083 SELECT /*+ ordered rowid(sysp) use_nl(rabatch,ractt)
1084 INDEX(rabatch ra_batch_sources_u1)
1085 INDEX(ractt ra_cust_trx_types_u1) */
1086 sysp.rowid ,
1087 rabatch.batch_source_id,
1088 ractt.cust_trx_type_id
1089 from ar_system_parameters_all sysp,
1090 ra_batch_sources_all rabatch,
1091 ra_cust_trx_types_all ractt
1092 WHERE sysp.org_id = rabatch.org_id(+)
1093 AND rabatch.name(+)= 'Interest Invoice'
1094 AND sysp.org_id = ractt.org_id(+)
1095 AND ractt.name(+) = 'Interest Invoice'
1096 AND sysp.rowid >= p_start_rowid
1097 AND sysp.rowid <= p_end_rowid;
1098
1099 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
1100 l_batch_source_id DBMS_SQL.NUMBER_TABLE;
1101 l_cust_trx_type_id DBMS_SQL.NUMBER_TABLE;
1102
1103 g_bulk_fetch_rows NUMBER := 10000;
1104 l_last_fetch BOOLEAN := FALSE;
1105
1106 BEGIN
1107
1108 /* ------ Initialize the rowid ranges ------ */
1109 ad_parallel_updates_pkg.initialize_rowid_range(
1110 ad_parallel_updates_pkg.ROWID_RANGE,
1111 l_table_owner,
1112 l_table_name,
1113 l_script_name,
1114 l_worker_id,
1115 l_num_workers,
1116 l_batch_size, 0);
1117
1118 /* ------ Get rowid ranges ------ */
1119 ad_parallel_updates_pkg.get_rowid_range(
1120 l_start_rowid,
1121 l_end_rowid,
1122 l_any_rows_to_process,
1123 l_batch_size,
1124 TRUE);
1125
1126
1127
1128 WHILE ( l_any_rows_to_process = TRUE )
1129 LOOP
1130
1131 l_rows_processed := 0;
1132
1133 OPEN c(l_start_rowid, l_end_rowid);
1134
1135 LOOP
1136
1137 FETCH c BULK COLLECT INTO
1138 l_rowid_tab ,
1139 l_batch_source_id ,
1140 l_cust_trx_type_id
1141 LIMIT g_bulk_fetch_rows;
1142
1143 IF c%NOTFOUND THEN
1144 l_last_fetch := TRUE;
1145 END IF;
1146
1147 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
1148 EXIT;
1149 END IF;
1150
1151 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
1152 UPDATE ar_system_parameters_all
1153 SET late_charge_inv_type_id = l_cust_trx_type_id(i),
1154 late_charge_batch_source_id = l_batch_source_id(i),
1155 allow_late_charges = 'Y'
1156 WHERE rowid = l_rowid_tab(i);
1157
1158 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1159
1160 IF l_last_fetch THEN
1161 EXIT;
1162 END IF;
1163
1164 END LOOP;
1165
1166 CLOSE c;
1167
1168
1169 ad_parallel_updates_pkg.processed_rowid_range(
1170 l_rows_processed,
1171 l_end_rowid);
1172
1173 commit;
1174
1175 ad_parallel_updates_pkg.get_rowid_range(
1176 l_start_rowid,
1177 l_end_rowid,
1178 l_any_rows_to_process,
1179 l_batch_size,
1180 FALSE);
1181
1182 l_rows_processed := 0 ;
1183
1184 END LOOP ; /* end of WHILE loop */
1185
1186 EXCEPTION
1187 WHEN NO_DATA_FOUND THEN
1188 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_ps_for_adj');
1189 RAISE;
1190
1191 WHEN OTHERS THEN
1192 --arp_standard.debug('OTHERS EXCEPTION: upgrade_ps_for_adj');
1193 RAISE;
1194
1195 END upgrade_lc_sysp;
1196
1197
1198
1199
1200
1201 PROCEDURE upgrade_lc_site_use
1202 (l_table_owner IN VARCHAR2,
1203 l_table_name IN VARCHAR2,
1204 l_script_name IN VARCHAR2,
1205 l_worker_id IN VARCHAR2,
1206 l_num_workers IN VARCHAR2,
1207 l_batch_size IN VARCHAR2)
1208 IS
1209 l_start_rowid rowid;
1210 l_end_rowid rowid;
1211 l_any_rows_to_process boolean;
1212 l_rows_processed number := 0;
1213
1214 l_sys_date DATE;
1215 g_bulk_fetch_rows NUMBER := 10000;
1216 l_last_fetch BOOLEAN := FALSE;
1217 BEGIN
1218 /* ------ Initialize the rowid ranges ------ */
1219 ad_parallel_updates_pkg.initialize_rowid_range(
1220 ad_parallel_updates_pkg.ROWID_RANGE,
1221 l_table_owner,
1222 l_table_name,
1223 l_script_name,
1224 l_worker_id,
1225 l_num_workers,
1226 l_batch_size, 0);
1227
1228 /* ------ Get rowid ranges ------ */
1229 ad_parallel_updates_pkg.get_rowid_range(
1230 l_start_rowid,
1231 l_end_rowid,
1232 l_any_rows_to_process,
1233 l_batch_size,
1234 TRUE);
1235
1236
1237 SELECT sysdate INTO l_sys_date FROM sys.dual;
1238
1239 WHILE ( l_any_rows_to_process = TRUE )
1240 LOOP
1241
1242 l_rows_processed := 0;
1243
1244 INSERT INTO HZ_CUST_SITE_USES_ALL
1245 ( SITE_USE_ID ,
1246 CUST_ACCT_SITE_ID,
1247 SITE_USE_CODE ,
1248 PRIMARY_FLAG ,
1249 STATUS ,
1250 LOCATION ,
1251 ORG_ID ,
1252 OBJECT_VERSION_NUMBER,
1253 CREATED_BY_MODULE,
1254 LAST_UPDATE_DATE ,
1255 CREATION_DATE ,
1256 LAST_UPDATED_BY ,
1257 CREATED_BY ,
1258 LAST_UPDATE_LOGIN)
1259 SELECT HZ_CUST_SITE_USES_S.NEXTVAL,
1260 l.cust_acct_site_id,
1261 'LATE_CHARGE',
1262 'Y',
1263 'A',
1264 TO_CHAR(HZ_CUST_SITE_USES_S.CURRVAL),
1265 l.org_id,
1266 1,
1267 'AR_LATE_CHARGE_UPG',
1268 l_sys_date,
1269 l_sys_date,
1270 -1551,
1271 -1551,
1272 -1551
1273 FROM (SELECT cas.cust_acct_site_id,
1274 cas.org_id
1275 FROM hz_cust_acct_sites_all cas,
1276 hz_cust_site_uses_all csu
1277 WHERE cas.rowid >= l_start_rowid
1278 AND cas.rowid <= l_end_rowid
1279 AND cas.status = 'A'
1280 AND cas.cust_acct_site_id = csu.cust_acct_site_id
1281 AND csu.status = 'A'
1282 AND (( csu.site_use_code = 'STMTS'
1283 AND NOT EXISTS (SELECT NULL FROM hz_cust_site_uses_all b WHERE
1284 b.cust_acct_site_id = cas.cust_acct_site_id
1285 AND b.site_use_code = 'DUN'
1286 AND b.status = 'A'))
1287 OR csu.site_use_code = 'DUN')) l;
1288
1289
1290
1291 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1292
1293 ad_parallel_updates_pkg.processed_rowid_range(
1294 l_rows_processed,
1295 l_end_rowid);
1296
1297 commit;
1298
1299 ad_parallel_updates_pkg.get_rowid_range(
1300 l_start_rowid,
1301 l_end_rowid,
1302 l_any_rows_to_process,
1303 l_batch_size,
1304 FALSE);
1305
1306 END LOOP ; /* end of WHILE loop */
1307
1308 EXCEPTION
1309 WHEN NO_DATA_FOUND THEN
1310 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_lc_site_use');
1311 RAISE;
1312
1313 WHEN OTHERS THEN
1314 --arp_standard.debug('OTHERS EXCEPTION: upgrade_lc_site_use');
1315 RAISE;
1316
1317 END upgrade_lc_site_use;
1318
1319
1320 END;