[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.2 2008/11/24 05:05:48 ankuagar 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,NULL,'AMOUNT') MIN_FC_BALANCE_OVERDUE_TYPE
613 , f_number(cpa.JGZZ_ATTRIBUTE3) MIN_INTEREST_CHARGE
614 , DECODE(cpa.jgzz_attribute7,NULL,
615 DECODE(cpa.interest_rate,NULL,NULL,'FIXED_RATE'),
616 'CHARGES_SCHEDULE') INTEREST_TYPE
617 , cs.schedule_id INTEREST_SCHEDULE_ID
618 , DECODE(cpa.jgzz_attribute3,NULL,NULL,'FIXED_AMOUNT') PENALTY_TYPE
619 , cpa.jgzz_attribute3 PENALTY_FIXED_AMOUNT
620 --{TCA Validation
621 , DECODE(cpa.jgzz_attribute7,NULL,cpa.interest_rate,NULL) interest_rate
622 --}
623 FROM hz_cust_prof_class_amts cpa,
624 ar_charge_schedules cs
625 WHERE cpa.rowid >= p_start_rowid
626 AND cpa.rowid <= p_end_rowid
627 AND cs.SCHEDULE_NAME(+) = DECODE(cpa.jgzz_attribute7,
628 'D','D_UPG_'||cpa.jgzz_attribute8,
629 'M','M_UPG_'||cpa.jgzz_attribute9,NULL);
630
631 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
632 l_EXCHANGE_RATE_TYPE DBMS_SQL.VARCHAR2_TABLE;
633 l_MIN_FC_INVOICE_OVERDUE_TYPE DBMS_SQL.VARCHAR2_TABLE;
634 l_MIN_FC_INVOICE_AMOUNT DBMS_SQL.NUMBER_TABLE;
635 l_MIN_FC_BALANCE_OVERDUE_TYPE DBMS_SQL.VARCHAR2_TABLE;
636 l_MIN_INTEREST_CHARGE DBMS_SQL.NUMBER_TABLE;
637 l_INTEREST_TYPE DBMS_SQL.VARCHAR2_TABLE;
638 l_INTEREST_SCHEDULE_ID DBMS_SQL.NUMBER_TABLE;
639 l_PENALTY_TYPE DBMS_SQL.VARCHAR2_TABLE;
640 l_PENALTY_FIXED_AMOUNT DBMS_SQL.NUMBER_TABLE;
641 l_interest_rate DBMS_SQL.NUMBER_TABLE;
642
643 g_bulk_fetch_rows NUMBER := 10000;
644 l_last_fetch BOOLEAN := FALSE;
645
646 BEGIN
647
648 /* ------ Initialize the rowid ranges ------ */
649 ad_parallel_updates_pkg.initialize_rowid_range(
650 ad_parallel_updates_pkg.ROWID_RANGE,
651 l_table_owner,
652 l_table_name,
653 l_script_name,
654 l_worker_id,
655 l_num_workers,
656 l_batch_size, 0);
657
658 /* ------ Get rowid ranges ------ */
659 ad_parallel_updates_pkg.get_rowid_range(
660 l_start_rowid,
661 l_end_rowid,
662 l_any_rows_to_process,
663 l_batch_size,
664 TRUE);
665
666 WHILE ( l_any_rows_to_process = TRUE )
667 LOOP
668
669 l_rows_processed := 0;
670
671 OPEN c(l_start_rowid, l_end_rowid);
672
673 LOOP
674
675 FETCH c BULK COLLECT INTO
676 l_rowid_tab ,
677 l_EXCHANGE_RATE_TYPE ,
678 l_MIN_FC_INVOICE_OVERDUE_TYPE,
679 l_MIN_FC_INVOICE_AMOUNT ,
680 l_MIN_FC_BALANCE_OVERDUE_TYPE,
681 l_MIN_INTEREST_CHARGE ,
682 l_INTEREST_TYPE ,
683 l_INTEREST_SCHEDULE_ID ,
684 l_PENALTY_TYPE ,
685 l_PENALTY_FIXED_AMOUNT ,
686 l_interest_rate
687 LIMIT g_bulk_fetch_rows;
688
689 IF c%NOTFOUND THEN
690 l_last_fetch := TRUE;
691 END IF;
692
693 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
694 EXIT;
695 END IF;
696
697 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
698 UPDATE hz_cust_prof_class_amts
699 SET EXCHANGE_RATE_TYPE = l_EXCHANGE_RATE_TYPE(i),
700 MIN_FC_INVOICE_OVERDUE_TYPE = l_MIN_FC_INVOICE_OVERDUE_TYPE(i),
701 MIN_FC_INVOICE_AMOUNT = l_MIN_FC_INVOICE_AMOUNT(i),
702 MIN_FC_BALANCE_OVERDUE_TYPE = l_MIN_FC_BALANCE_OVERDUE_TYPE(i),
703 MIN_INTEREST_CHARGE = l_MIN_INTEREST_CHARGE(i),
704 INTEREST_TYPE = l_INTEREST_TYPE(i),
705 INTEREST_SCHEDULE_ID = l_INTEREST_SCHEDULE_ID(i),
706 PENALTY_TYPE = l_PENALTY_TYPE(i),
707 PENALTY_FIXED_AMOUNT = l_PENALTY_FIXED_AMOUNT(i),
708 last_update_date = g_creation_date,
709 last_updated_by = -1,
710 interest_rate = l_interest_rate(i)
711 WHERE rowid = l_rowid_tab(i);
712
713 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
714
715 IF l_last_fetch THEN
716 EXIT;
717 END IF;
718
719 END LOOP;
720
721 CLOSE c;
722
723
724 ad_parallel_updates_pkg.processed_rowid_range(
725 l_rows_processed,
726 l_end_rowid);
727
728 commit;
729
730 ad_parallel_updates_pkg.get_rowid_range(
731 l_start_rowid,
732 l_end_rowid,
733 l_any_rows_to_process,
734 l_batch_size,
735 FALSE);
736
737 l_rows_processed := 0 ;
738
739 END LOOP ; /* end of WHILE loop */
740
741 EXCEPTION
742 WHEN NO_DATA_FOUND THEN
743 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_profile_class_amount');
744 RAISE;
745
746 WHEN OTHERS THEN
747 --arp_standard.debug('OTHERS EXCEPTION: upgrade_profile_class_amount');
748 RAISE;
749
750 END upgrade_profile_class_amount;
751
752
753 --Phase 7
754 PROCEDURE upgrade_profile_class
755 (l_table_owner IN VARCHAR2, -- AR
756 l_table_name IN VARCHAR2, -- HZ_CUST_PROFILE_CLASSES
757 l_script_name IN VARCHAR2, -- ar120lccpc.sql
758 l_worker_id IN VARCHAR2,
759 l_num_workers IN VARCHAR2,
760 l_batch_size IN VARCHAR2)
761 IS
762
763 l_start_rowid rowid;
764 l_end_rowid rowid;
765 l_any_rows_to_process boolean;
766 l_rows_processed number := 0;
767
768 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID) IS
769 SELECT /*+ ordered rowid(cp) */
770 cp.ROWID cp_rowid
771 , DECODE(JGZZ_ATTRIBUTE2,'LP','LATE',
772 'LO','OVERDUE_LATE',
773 'OI','OVERDUE','OVERDUE') LATE_CHARGE_CALCULATION_TRX
774 , DECODE(JGZZ_ATTRIBUTE9,'Y','Y','N') CREDIT_ITEMS_FLAG
775 , 'N' DISPUTED_TRANSACTIONS_FLAG
776 , DECODE(JGZZ_ATTRIBUTE1,'Y','INV','ADJ') LATE_CHARGE_TYPE
777 , f_NUMBER(JGZZ_ATTRIBUTE8) LATE_CHARGE_TERM_ID
778 , DECODE(cpa.dom,'M','MONTHLY','DAILY') INTEREST_CALCULATION_PERIOD
779 , DECODE(JGZZ_ATTRIBUTE5,'Y','Y','N') HOLD_CHARGED_INVOICES_FLAG
780 , 'N' MULTIPLE_INTEREST_RATES_FLAG
781 , f_date(JGZZ_ATTRIBUTE6) CHARGE_BEGIN_DATE
782 , DECODE(JGZZ_ATTRIBUTE1,'Y','Y',
783 NVL(INTEREST_CHARGES,'N')) INTEREST_CHARGES
784 , f_NUMBER(JGZZ_ATTRIBUTE4) Message_text_id
785 FROM hz_cust_profile_classes cp,
786 (SELECT MAX(jgzz_attribute7) dom,
787 PROFILE_CLASS_ID
788 FROM hz_cust_prof_class_amts
789 WHERE jgzz_attribute7 = 'M'
790 GROUP BY PROFILE_CLASS_ID) cpa
791 WHERE cp.rowid >= p_start_rowid
792 AND cp.rowid <= p_end_rowid
793 AND cp.PROFILE_CLASS_ID = cpa.PROFILE_CLASS_ID(+);
794
795 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
796 l_LATE_CHARGE_CALCULATION_TRX DBMS_SQL.VARCHAR2_TABLE;
797 l_CREDIT_ITEMS_FLAG DBMS_SQL.VARCHAR2_TABLE;
798 l_DISPUTED_TRANSACTIONS_FLAG DBMS_SQL.VARCHAR2_TABLE;
799 l_LATE_CHARGE_TYPE DBMS_SQL.VARCHAR2_TABLE;
800 l_LATE_CHARGE_TERM_ID DBMS_SQL.NUMBER_TABLE;
801 l_INTEREST_CALCULATION_PERIOD DBMS_SQL.VARCHAR2_TABLE;
802 l_HOLD_CHARGED_INVOICES_FLAG DBMS_SQL.VARCHAR2_TABLE;
803 l_MULTIPLE_INTEREST_RATES_FLAG DBMS_SQL.VARCHAR2_TABLE;
804 l_CHARGE_BEGIN_DATE DBMS_SQL.DATE_TABLE;
805 l_INTEREST_CHARGES DBMS_SQL.VARCHAR2_TABLE;
806 l_Message_text_id DBMS_SQL.NUMBER_TABLE;
807
808
809
810 g_bulk_fetch_rows NUMBER := 10000;
811 l_last_fetch BOOLEAN := FALSE;
812
813 BEGIN
814
815 /* ------ Initialize the rowid ranges ------ */
816 ad_parallel_updates_pkg.initialize_rowid_range(
817 ad_parallel_updates_pkg.ROWID_RANGE,
818 l_table_owner,
819 l_table_name,
820 l_script_name,
821 l_worker_id,
822 l_num_workers,
823 l_batch_size, 0);
824
825 /* ------ Get rowid ranges ------ */
826 ad_parallel_updates_pkg.get_rowid_range(
827 l_start_rowid,
828 l_end_rowid,
829 l_any_rows_to_process,
830 l_batch_size,
831 TRUE);
832
833
834
835 WHILE ( l_any_rows_to_process = TRUE )
836 LOOP
837
838 l_rows_processed := 0;
839
840 OPEN c(l_start_rowid, l_end_rowid);
841
842 LOOP
843
844 FETCH c BULK COLLECT INTO
845 l_rowid_tab,
846 l_LATE_CHARGE_CALCULATION_TRX,
847 l_CREDIT_ITEMS_FLAG,
848 l_DISPUTED_TRANSACTIONS_FLAG,
849 l_LATE_CHARGE_TYPE,
850 l_LATE_CHARGE_TERM_ID,
851 l_INTEREST_CALCULATION_PERIOD,
852 l_HOLD_CHARGED_INVOICES_FLAG,
853 l_MULTIPLE_INTEREST_RATES_FLAG,
854 l_CHARGE_BEGIN_DATE,
855 l_INTEREST_CHARGES,
856 l_Message_text_id
857 LIMIT g_bulk_fetch_rows;
858
859 IF c%NOTFOUND THEN
860 l_last_fetch := TRUE;
861 END IF;
862
863 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
864 EXIT;
865 END IF;
866
867 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
868 UPDATE hz_cust_profile_classes
869 SET LATE_CHARGE_CALCULATION_TRX = l_LATE_CHARGE_CALCULATION_TRX(i),
870 CREDIT_ITEMS_FLAG = l_CREDIT_ITEMS_FLAG(i),
871 DISPUTED_TRANSACTIONS_FLAG = l_DISPUTED_TRANSACTIONS_FLAG(i),
872 LATE_CHARGE_TYPE = l_LATE_CHARGE_TYPE(i),
873 LATE_CHARGE_TERM_ID = l_LATE_CHARGE_TERM_ID(i),
874 INTEREST_CALCULATION_PERIOD = l_INTEREST_CALCULATION_PERIOD(i),
875 HOLD_CHARGED_INVOICES_FLAG = l_HOLD_CHARGED_INVOICES_FLAG(i),
876 MULTIPLE_INTEREST_RATES_FLAG= l_MULTIPLE_INTEREST_RATES_FLAG(i),
877 CHARGE_BEGIN_DATE = l_CHARGE_BEGIN_DATE(i),
878 INTEREST_CHARGES = l_INTEREST_CHARGES(i),
879 Message_text_id = l_Message_text_id(i),
880 last_update_date = g_creation_date,
881 last_updated_by = -1
882 WHERE rowid = l_rowid_tab(i);
883
884 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
885
886 IF l_last_fetch THEN
887 EXIT;
888 END IF;
889
890 END LOOP;
891
892 CLOSE c;
893
894
895 ad_parallel_updates_pkg.processed_rowid_range(
896 l_rows_processed,
897 l_end_rowid);
898
899 commit;
900
901 ad_parallel_updates_pkg.get_rowid_range(
902 l_start_rowid,
903 l_end_rowid,
904 l_any_rows_to_process,
905 l_batch_size,
906 FALSE);
907
908 l_rows_processed := 0 ;
909
910 END LOOP ; /* end of WHILE loop */
911
912 EXCEPTION
913 WHEN NO_DATA_FOUND THEN
914 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_profile_class');
915 RAISE;
916
917 WHEN OTHERS THEN
918 --arp_standard.debug('OTHERS EXCEPTION: upgrade_profile_class');
919 RAISE;
920
921 END upgrade_profile_class;
922
923
924
925 --Upgrade ar_payment_schedules_for_adj
926 --Phase 5
927 PROCEDURE upgrade_ps_for_adj
928 (l_table_owner IN VARCHAR2, -- AR
929 l_table_name IN VARCHAR2, -- HZ_CUST_SITE_USES_ALL
930 l_script_name IN VARCHAR2, -- ar120lccsups.sql
931 l_worker_id IN VARCHAR2,
932 l_num_workers IN VARCHAR2,
933 l_batch_size IN VARCHAR2)
934 IS
935
936 l_start_rowid rowid;
937 l_end_rowid rowid;
938 l_any_rows_to_process boolean;
939 l_rows_processed number := 0;
940
941 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID) IS
942 SELECT trun.psch_rowid,
943 trun.csu_last_charge_date
944 FROM
945 (SELECT /*+ ordered rowid(csu) use_nl(psch,adj)
946 INDEX(psch ar_payment_schedules_n5)
947 INDEX(adj ar_adjustments_n3) */
948 adj.adjustment_id as adj_id,
949 psch.rowid as psch_rowid,
950 csu.last_accrue_charge_date as csu_last_charge_date,
951 rank () over (partition by adj.payment_schedule_id order by adjustment_id desc) as rk
952 FROM hz_cust_site_uses_all csu,
953 ar_payment_schedules_all psch,
954 ar_adjustments_all adj
955 WHERE csu.rowid >= p_start_rowid
956 AND csu.rowid <= p_end_rowid
957 AND csu.last_accrue_charge_date IS NOT NULL
958 AND csu.SITE_USE_ID = psch.CUSTOMER_SITE_USE_ID
959 AND psch.status = 'OP'
960 AND psch.last_charge_date IS NULL
961 AND psch.payment_schedule_id = adj.payment_schedule_id
962 AND adj.status = 'A'
963 AND adj.type = 'CHARGES'
964 ) trun
965 WHERE trun.rk = 1;
966
967 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
968 l_csu_last_charge_date DBMS_SQL.DATE_TABLE;
969
970 g_bulk_fetch_rows NUMBER := 10000;
971 l_last_fetch BOOLEAN := FALSE;
972
973 BEGIN
974
975 /* ------ Initialize the rowid ranges ------ */
976 ad_parallel_updates_pkg.initialize_rowid_range(
977 ad_parallel_updates_pkg.ROWID_RANGE,
978 l_table_owner,
979 l_table_name,
980 l_script_name,
981 l_worker_id,
982 l_num_workers,
983 l_batch_size, 0);
984
985 /* ------ Get rowid ranges ------ */
986 ad_parallel_updates_pkg.get_rowid_range(
987 l_start_rowid,
988 l_end_rowid,
989 l_any_rows_to_process,
990 l_batch_size,
991 TRUE);
992
993
994
995 WHILE ( l_any_rows_to_process = TRUE )
996 LOOP
997
998 l_rows_processed := 0;
999
1000 OPEN c(l_start_rowid, l_end_rowid);
1001
1002 LOOP
1003
1004 FETCH c BULK COLLECT INTO
1005 l_rowid_tab ,
1006 l_csu_last_charge_date
1007 LIMIT g_bulk_fetch_rows;
1008
1009 IF c%NOTFOUND THEN
1010 l_last_fetch := TRUE;
1011 END IF;
1012
1013 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
1014 EXIT;
1015 END IF;
1016
1017 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
1018 UPDATE ar_payment_schedules_all
1019 SET last_charge_date = l_csu_last_charge_date(i)
1020 WHERE rowid = l_rowid_tab(i);
1021
1022 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1023
1024 IF l_last_fetch THEN
1025 EXIT;
1026 END IF;
1027
1028 END LOOP;
1029
1030 CLOSE c;
1031
1032
1033 ad_parallel_updates_pkg.processed_rowid_range(
1034 l_rows_processed,
1035 l_end_rowid);
1036
1037 commit;
1038
1039 ad_parallel_updates_pkg.get_rowid_range(
1040 l_start_rowid,
1041 l_end_rowid,
1042 l_any_rows_to_process,
1043 l_batch_size,
1044 FALSE);
1045
1046 l_rows_processed := 0 ;
1047
1048 END LOOP ; /* end of WHILE loop */
1049
1050 EXCEPTION
1051 WHEN NO_DATA_FOUND THEN
1052 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_ps_for_adj');
1053 RAISE;
1054
1055 WHEN OTHERS THEN
1056 --arp_standard.debug('OTHERS EXCEPTION: upgrade_ps_for_adj');
1057 RAISE;
1058
1059 END upgrade_ps_for_adj;
1060
1061
1062
1063
1064
1065 --Phase 5
1066 PROCEDURE upgrade_lc_sysp
1067 (l_table_owner IN VARCHAR2, -- AR
1068 l_table_name IN VARCHAR2, -- AR_SYSTEM_PARAMETERS_ALL
1069 l_script_name IN VARCHAR2, -- ar120lcsysp.sql
1070 l_worker_id IN VARCHAR2,
1071 l_num_workers IN VARCHAR2,
1072 l_batch_size IN VARCHAR2)
1073 IS
1074
1075 l_start_rowid rowid;
1076 l_end_rowid rowid;
1077 l_any_rows_to_process boolean;
1078 l_rows_processed number := 0;
1079
1080 CURSOR c(p_start_rowid IN ROWID, p_end_rowid IN ROWID) IS
1081 SELECT /*+ ordered rowid(sysp) use_nl(rabatch,ractt)
1082 INDEX(rabatch ra_batch_sources_u1)
1083 INDEX(ractt ra_cust_trx_types_u1) */
1084 sysp.rowid ,
1085 rabatch.batch_source_id,
1086 ractt.cust_trx_type_id
1087 from ar_system_parameters_all sysp,
1088 ra_batch_sources_all rabatch,
1089 ra_cust_trx_types_all ractt
1090 WHERE sysp.org_id = rabatch.org_id(+)
1091 AND rabatch.name(+)= 'Interest Invoice'
1092 AND sysp.org_id = ractt.org_id(+)
1093 AND ractt.name(+) = 'Interest Invoice'
1094 AND sysp.rowid >= p_start_rowid
1095 AND sysp.rowid <= p_end_rowid;
1096
1097 l_rowid_tab DBMS_SQL.VARCHAR2_TABLE;
1098 l_batch_source_id DBMS_SQL.NUMBER_TABLE;
1099 l_cust_trx_type_id DBMS_SQL.NUMBER_TABLE;
1100
1101 g_bulk_fetch_rows NUMBER := 10000;
1102 l_last_fetch BOOLEAN := FALSE;
1103
1104 BEGIN
1105
1106 /* ------ Initialize the rowid ranges ------ */
1107 ad_parallel_updates_pkg.initialize_rowid_range(
1108 ad_parallel_updates_pkg.ROWID_RANGE,
1109 l_table_owner,
1110 l_table_name,
1111 l_script_name,
1112 l_worker_id,
1113 l_num_workers,
1114 l_batch_size, 0);
1115
1116 /* ------ Get rowid ranges ------ */
1117 ad_parallel_updates_pkg.get_rowid_range(
1118 l_start_rowid,
1119 l_end_rowid,
1120 l_any_rows_to_process,
1121 l_batch_size,
1122 TRUE);
1123
1124
1125
1126 WHILE ( l_any_rows_to_process = TRUE )
1127 LOOP
1128
1129 l_rows_processed := 0;
1130
1131 OPEN c(l_start_rowid, l_end_rowid);
1132
1133 LOOP
1134
1135 FETCH c BULK COLLECT INTO
1136 l_rowid_tab ,
1137 l_batch_source_id ,
1138 l_cust_trx_type_id
1139 LIMIT g_bulk_fetch_rows;
1140
1141 IF c%NOTFOUND THEN
1142 l_last_fetch := TRUE;
1143 END IF;
1144
1145 IF (l_rowid_tab.COUNT = 0) AND (l_last_fetch) THEN
1146 EXIT;
1147 END IF;
1148
1149 FORALL i IN l_rowid_tab.FIRST .. l_rowid_tab.LAST
1150 UPDATE ar_system_parameters_all
1151 SET late_charge_inv_type_id = l_cust_trx_type_id(i),
1152 late_charge_batch_source_id = l_batch_source_id(i),
1153 allow_late_charges = 'Y'
1154 WHERE rowid = l_rowid_tab(i);
1155
1156 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1157
1158 IF l_last_fetch THEN
1159 EXIT;
1160 END IF;
1161
1162 END LOOP;
1163
1164 CLOSE c;
1165
1166
1167 ad_parallel_updates_pkg.processed_rowid_range(
1168 l_rows_processed,
1169 l_end_rowid);
1170
1171 commit;
1172
1173 ad_parallel_updates_pkg.get_rowid_range(
1174 l_start_rowid,
1175 l_end_rowid,
1176 l_any_rows_to_process,
1177 l_batch_size,
1178 FALSE);
1179
1180 l_rows_processed := 0 ;
1181
1182 END LOOP ; /* end of WHILE loop */
1183
1184 EXCEPTION
1185 WHEN NO_DATA_FOUND THEN
1186 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_ps_for_adj');
1187 RAISE;
1188
1189 WHEN OTHERS THEN
1190 --arp_standard.debug('OTHERS EXCEPTION: upgrade_ps_for_adj');
1191 RAISE;
1192
1193 END upgrade_lc_sysp;
1194
1195
1196
1197
1198
1199 PROCEDURE upgrade_lc_site_use
1200 (l_table_owner IN VARCHAR2,
1201 l_table_name IN VARCHAR2,
1202 l_script_name IN VARCHAR2,
1203 l_worker_id IN VARCHAR2,
1204 l_num_workers IN VARCHAR2,
1205 l_batch_size IN VARCHAR2)
1206 IS
1207 l_start_rowid rowid;
1208 l_end_rowid rowid;
1209 l_any_rows_to_process boolean;
1210 l_rows_processed number := 0;
1211
1212 l_sys_date DATE;
1213 g_bulk_fetch_rows NUMBER := 10000;
1214 l_last_fetch BOOLEAN := FALSE;
1215 BEGIN
1216 /* ------ Initialize the rowid ranges ------ */
1217 ad_parallel_updates_pkg.initialize_rowid_range(
1218 ad_parallel_updates_pkg.ROWID_RANGE,
1219 l_table_owner,
1220 l_table_name,
1221 l_script_name,
1222 l_worker_id,
1223 l_num_workers,
1224 l_batch_size, 0);
1225
1226 /* ------ Get rowid ranges ------ */
1227 ad_parallel_updates_pkg.get_rowid_range(
1228 l_start_rowid,
1229 l_end_rowid,
1230 l_any_rows_to_process,
1231 l_batch_size,
1232 TRUE);
1233
1234
1235 SELECT sysdate INTO l_sys_date FROM sys.dual;
1236
1237 WHILE ( l_any_rows_to_process = TRUE )
1238 LOOP
1239
1240 l_rows_processed := 0;
1241
1242 INSERT INTO HZ_CUST_SITE_USES_ALL
1243 ( SITE_USE_ID ,
1244 CUST_ACCT_SITE_ID,
1245 SITE_USE_CODE ,
1246 PRIMARY_FLAG ,
1247 STATUS ,
1248 LOCATION ,
1249 ORG_ID ,
1250 OBJECT_VERSION_NUMBER,
1251 CREATED_BY_MODULE,
1252 LAST_UPDATE_DATE ,
1253 CREATION_DATE ,
1254 LAST_UPDATED_BY ,
1255 CREATED_BY ,
1256 LAST_UPDATE_LOGIN)
1257 SELECT HZ_CUST_SITE_USES_S.NEXTVAL,
1258 l.cust_acct_site_id,
1259 'LATE_CHARGE',
1260 'Y',
1261 'A',
1262 TO_CHAR(HZ_CUST_SITE_USES_S.CURRVAL),
1263 l.org_id,
1264 1,
1265 'AR_LATE_CHARGE_UPG',
1266 l_sys_date,
1267 l_sys_date,
1268 -1551,
1269 -1551,
1270 -1551
1271 FROM (SELECT cas.cust_acct_site_id,
1272 cas.org_id
1273 FROM hz_cust_acct_sites_all cas,
1274 hz_cust_site_uses_all csu
1275 WHERE cas.rowid >= l_start_rowid
1276 AND cas.rowid <= l_end_rowid
1277 AND cas.status = 'A'
1278 AND cas.cust_acct_site_id = csu.cust_acct_site_id
1279 AND csu.status = 'A'
1280 AND (( csu.site_use_code = 'STMTS'
1281 AND NOT EXISTS (SELECT NULL FROM hz_cust_site_uses_all b WHERE
1282 b.cust_acct_site_id = cas.cust_acct_site_id
1283 AND b.site_use_code = 'DUN'
1284 AND b.status = 'A'))
1285 OR csu.site_use_code = 'DUN')) l;
1286
1287
1288
1289 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1290
1291 ad_parallel_updates_pkg.processed_rowid_range(
1292 l_rows_processed,
1293 l_end_rowid);
1294
1295 commit;
1296
1297 ad_parallel_updates_pkg.get_rowid_range(
1298 l_start_rowid,
1299 l_end_rowid,
1300 l_any_rows_to_process,
1301 l_batch_size,
1302 FALSE);
1303
1304 END LOOP ; /* end of WHILE loop */
1305
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND THEN
1308 --arp_standard.debug('NO_DATA_FOUND EXCEPTION: upgrade_lc_site_use');
1309 RAISE;
1310
1311 WHEN OTHERS THEN
1312 --arp_standard.debug('OTHERS EXCEPTION: upgrade_lc_site_use');
1313 RAISE;
1314
1315 END upgrade_lc_site_use;
1316
1317
1318 END;