[Home] [Help]
PACKAGE BODY: APPS.IGIRRPS
Source
4 l_debug_level number;
1 PACKAGE BODY IGIRRPS AS
2 -- $Header: igirrpsb.pls 120.8.12000000.1 2007/08/31 05:53:27 mbremkum ship $
3
5 l_state_level number;
6 l_proc_level number;
7 l_event_level number;
8 l_excep_level number;
9 l_error_level number;
10 l_unexp_level number;
11
12 ALREADY_SYNC_STATUS CONSTANT VARCHAR2(1) := 'Y';
13 NOT_SYNC_STATUS CONSTANT VARCHAR2(1) := 'N';
14 SYNCHRONIZED_STATUS CONSTANT VARCHAR2(1) := NOT_SYNC_STATUS;
15
16 CHARGE_STATUS CONSTANT IGI_RPI_STANDING_CHARGES_ALL.STATUS%TYPE
17 := 'ACTIVE';
18 INVOICING_RULE CONSTANT
19 IGI_RPI_STANDING_CHARGES_ALL.ADVANCE_ARREARS_IND%TYPE := 'ADVANCE';
20
21 --Commenting out WriteToLogFile as fnd_logging to be used bug 3199481 (Start)
22 /*
23 PROCEDURE WriteToLog ( pp_mesg in VARCHAR2) IS
24 IsdebugMode BOOLEAN := FALSE;
25 BEGIN
26 IF IsDebugMode THEN
27 FND_FILE.put_line( FND_FILE.log, pp_mesg );
28 ELSE
29 NULL;
30 END IF;
31 END;
32 */
33 --Commenting out WriteToLogFile as fnd_logging to be used bug 3199481 (End)
34
35
36 FUNCTION GetNewPrevDate ( pp_component in varchar2
37 , pp_factor in number
38 , pp_date in date )
39 return DATE IS
40 ld_date date;
41 BEGIN
42 ld_date := NULL;
43 SELECT DECODE(pp_component,'DAY' , ( TO_NUMBER(pp_factor)* -1 )+ pp_date
44 ,'WEEK' , ( TO_NUMBER(pp_factor)* -7 )+ pp_date
45 ,'MONTH' , ADD_MONTHS(pp_date,-1*TO_NUMBER(pp_factor))
46 ,'YEAR' , ADD_MONTHS(pp_date, TO_NUMBER(pp_factor)* -12 )
47 )
48 INTO ld_date
49 FROM SYS.DUAL
50 ;
51 return ld_date;
52 END GetNewPrevDate;
53
54 FUNCTION GetNewNextDate ( pp_component in varchar2
55 , pp_factor in number
56 , pp_date in date )
57 return DATE IS
58 ld_date date;
59 BEGIN
60 ld_date := NULL;
61 SELECT DECODE(pp_component,'DAY' , ( TO_NUMBER(pp_factor) )+ pp_date
62 ,'WEEK' , ( TO_NUMBER(pp_factor)* 7 )+ pp_date
63 ,'MONTH' , ADD_MONTHS(pp_date,TO_NUMBER(pp_factor))
64 ,'YEAR' , ADD_MONTHS(pp_date, TO_NUMBER(pp_factor)*12 )
65 )
66 INTO ld_date
67 FROM SYS.DUAL
68 ;
69 return ld_date;
70 END GetNewNextDate;
71
72 FUNCTION GetNewSchedPrevDate ( pp_schedule_id in number, pp_date in date )
73 return DATE IS
74 CURSOR c_sched IS
75 SELECT date1, date2, date3, date4
76 FROM igi_rpi_period_schedules
77 WHERE schedule_id = pp_schedule_id
78 AND nvl(enabled_flag,'Y') = 'Y';
79 l_Date date;
80 BEGIN
81 l_Date := NULL;
82 FOR l_s in C_sched LOOP
83
84 /* prev due date */
85
86 if to_date(to_char(l_s.date1,'DD/MM/')
87 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >=
88 pp_date
89 then
90 l_date := to_date(to_char(l_s.date4,'DD/MM/')
91 ||to_char(to_number(to_char(pp_date,'YYYY'))-1),'DD/MM/YYYY');
92 elsif to_date(to_char(l_s.date2,'DD/MM/')
93 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >=
94 pp_date
95 then
96 l_date := to_date(to_char(l_s.date1,'DD/MM/')
97 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
98 elsif to_date(to_char(l_s.date3,'DD/MM/')
99 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >=
100 pp_date
101 then
102 l_date := to_date(to_char(l_s.date2,'DD/MM/')
103 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
104 elsif to_date(to_char(l_s.date4,'DD/MM/')
105 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >=
106 pp_date
107 then
108 l_date := to_date(to_char(l_s.date3,'DD/MM/')
109 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
110 elsif to_date(to_char(l_s.date1,'DD/MM/')
111 ||to_char(to_number(to_char(pp_date,'YYYY'))+1),
112 'DD/MM/YYYY') >=
113 pp_date
114 then
115 l_date := to_date(to_char(l_s.date4,'DD/MM/')
116 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
117 end if;
118
119
120 END LOOP;
121
122 return l_date;
123 END GetNewSchedPrevDate;
124
125 FUNCTION GetNewSchedNextDate ( pp_schedule_id in number, pp_date in date )
126 return DATE IS
127 CURSOR c_sched IS
128 SELECT date1, date2, date3, date4
129 FROM igi_rpi_period_schedules
130 WHERE schedule_id = pp_schedule_id
131 AND nvl(enabled_flag,'Y') = 'Y';
132 l_Date date;
133 BEGIN
134 l_Date := NULL;
135 FOR l_s in C_sched LOOP
136
137 /* next due date */
138
139 if to_date(to_char(l_s.date1,'DD/MM/')
140 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >
141 pp_date
142 then
143 l_Date := to_date(to_char(l_s.date1,'DD/MM/')
144 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
145 elsif to_date(to_char(l_s.date2,'DD/MM/')
146 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >
147 pp_date
148 then
149 l_Date := to_date(to_char(l_s.date2,'DD/MM/')
150 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
151 elsif to_date(to_char(l_s.date3,'DD/MM/')
152 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >
153 pp_date
154 then
155 l_Date := to_date(to_char(l_s.date3,'DD/MM/')
156 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
157 elsif to_date(to_char(l_s.date4,'DD/MM/')
158 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY') >
159 pp_date
160 then
161 l_Date := to_date(to_char(l_s.date4,'DD/MM/')
162 ||to_char(pp_date,'YYYY'),'DD/MM/YYYY');
163 elsif to_date(to_char(l_s.date1,'DD/MM/')
164 ||to_char(to_number(to_char(pp_date,'YYYY'))+1),
165 'DD/MM/YYYY') >
166 pp_date
167 then
168 l_Date := to_date(to_char(l_s.date1,'DD/MM/')
169 ||to_char(to_number(to_char(pp_date,'YYYY'))+1),
170 'DD/MM/YYYY');
171 end if;
172
173 END LOOP;
174
175 return l_date;
176 END GetNewSchedNextDate;
177
178 FUNCTION GetNewPrevDate ( pp_standing_charge_id in number
179 , pp_date in date
180 )
181 RETURN DATE IS
182 CURSOR c_sc IS
183 SELECT sc.period_name
184 FROM igi_rpi_standing_charges sc
185 WHERE sc.standing_charge_id = pp_standing_charge_id
186 AND sc.set_of_books_id = ( select set_of_books_id
187 from ar_system_parameters )
188 AND sc.status = CHARGE_STATUS
189 ;
190 CURSOR C_periods ( pp_period_name in varchar2) IS
191 SELECT nvl(js.schedule_id,0) schedule_id
192 , jr.component
193 , jr.factor
194 FROM igi_rpi_component_periods jr
195 , igi_rpi_period_schedules js
196 WHERE jr.period_name = pp_period_name
197 AND jr.schedule_id = js.schedule_id
198 AND js.period_name = pp_period_name
199 UNION
200 SELECT 0 schedule_id, jr.component, jr.factor
201 FROM igi_rpi_component_periods jr
202 WHERE jr.period_name = pp_period_name
203 AND NOT EXISTS ( select 'x'
204 from igi_rpi_period_schedules js
205 where js.period_name = jr.period_name
206 and js.schedule_id = jr.schedule_id
207 )
208 ;
209
210
211 BEGIN
212 FOR l_sc in c_sc LOOP
213 FOR l_per in c_periods ( l_sc.period_name ) LOOP
214 IF l_per.schedule_id <> 0 THEN
215 return GetNewSchedPrevDate ( l_per.schedule_id
216 , pp_Date
217 );
218 ELSE
219 return GetNewPrevDate ( l_per.component
220 , l_per.factor
221 , pp_date );
222 END IF;
223
224 END LOOP;
225 END LOOP;
226 return NULL;
227 END GetNewPrevDate;
228
229 FUNCTION GetNewNextDate ( pp_standing_charge_id in number
230 , pp_date in date
231 )
232 RETURN DATE IS
233
234 CURSOR c_sc IS
235 SELECT sc.period_name
236 FROM igi_rpi_standing_charges sc
237 WHERE sc.standing_charge_id = pp_standing_charge_id
238 AND sc.set_of_books_id = ( select set_of_books_id
239 from ar_system_parameters )
240 AND sc.status = CHARGE_STATUS
241 ;
242 CURSOR C_periods ( pp_period_name in varchar2) IS
243 SELECT nvl(js.schedule_id,-1) schedule_id
244 , jr.component
245 , jr.factor
246 FROM igi_rpi_component_periods jr
247 , igi_rpi_period_schedules js
248 WHERE jr.period_name = pp_period_name
249 AND jr.schedule_id = js.schedule_id
250 AND js.period_name = pp_period_name
251 UNION
252 SELECT 0 schedule_id, jr.component, jr.factor
253 FROM igi_rpi_component_periods jr
254 WHERE jr.period_name = pp_period_name
255 AND NOT EXISTS ( select 'x'
256 from igi_rpi_period_schedules js
257 where js.period_name = jr.period_name
258 and js.schedule_id = jr.schedule_id )
259 ;
260 l_Date date;
261 BEGIN
262 l_Date := NULL;
263 FOR l_sc in c_sc LOOP
264 FOR l_per in c_periods ( l_sc.period_name ) LOOP
265 IF l_per.schedule_id <> 0 THEN
266 return GetNewSchedNextDate ( l_per.schedule_id
267 , pp_Date
268 );
269 ELSE
270 return GetNewNextDate ( l_per.component
271 , l_per.factor
272 , pp_date );
273 END IF;
274
275 END LOOP;
276 END LOOP;
280 PROCEDURE UpdateStandingCharges
277 return NULL;
278 END GetNewNextDate;
279
281 ( pp_standing_charge_id IN NUMBER
282 , pp_generate_sequence IN NUMBER )
283 IS
284 CURSOR C_UpdateStandingCharges (cp_standing_charge_id IN NUMBER
285 ,cp_generate_sequence IN NUMBER )
286 IS
287 SELECT sc.standing_charge_id , sc.rowid sc_rowid, sc.charge_reference,
288 sc.start_date, sc.standing_charge_date
289 , sc.end_date , sc.next_due_date , sc.previous_due_date
290 , sc.advance_arrears_ind
291 , nvl(sc.date_synchronized_flag,ALREADY_SYNC_STATUS) date_synchronized_flag
292 FROM igi_rpi_standing_charges sc
293 WHERE sc.standing_charge_id = cp_standing_charge_id
294 AND sc.generate_sequence = cp_generate_sequence
295 AND sc.set_of_books_id = ( select set_of_books_id
296 from ar_system_parameters )
297 AND sc.status = CHARGE_STATUS
298 ;
299 CURSOR c_lines ( cp_standing_charge_id in number) IS
300 select rowid row_id, start_date
301 from igi_rpi_line_details
302 where standing_charge_id = cp_standing_charge_id
303 and start_date is not null
304 ;
305 lv_update_sc C_UpdateStandingCharges%ROWTYPE;
306 ld_new_next_due_Date date;
307 ld_new_sc_date date; -- gl date
308 ld_new_ld_date date; -- line details start date
309
310 BEGIN
311 --WriteToLog ( ' Beginning of UpdateStandingCharges...');
312 -- bug 3199481, start block
313 IF (l_state_level >= l_debug_level) THEN
314 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg1',
315 ' Beginning of UpdateStandingCharges...');
316 END IF;
317 -- bug 3199481, end block
318 FOR lv_update_sc IN C_UpdateStandingCharges (pp_standing_charge_id,pp_generate_sequence)
319 LOOP
320
321 --WriteToLog ( ' ------------------------------------------------------- ');
322 --WriteToLog ( ' Standing Charge ID '|| lv_update_sc.standing_charge_id );
323 --WriteToLog ( ' Standing Charge Ref '|| lv_update_sc.charge_reference );
324 --WriteToLog ( ' Old Next Due Date '|| lv_update_sc.next_due_date );
325 --WriteToLog ( ' ------------------------------------------------------- ');
326
327 -- bug 3199481, start block
328 IF (l_state_level >= l_debug_level) THEN
329 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg2',
330 ' Standing Charge ID '|| lv_update_sc.standing_charge_id );
331 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg3',
332 ' Standing Charge Ref '|| lv_update_sc.charge_reference );
333 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg4',
337
334 ' Old Next Due Date '|| lv_update_sc.next_due_date );
335 END IF;
336 -- bug 3199481, end block
338 if lv_update_sc.date_synchronized_flag = SYNCHRONIZED_STATUS
339 then
340 ld_new_next_due_date := GetNewNextDate ( lv_update_sc.standing_charge_id
341 , lv_update_sc.next_due_date
342 );
343
344 /*Bug no 2688741 ,Fixed by shsaxena*/
345 IF (lv_update_sc.standing_charge_date IS NOT NULL) THEN
346 ld_new_sc_date := GetNewNextDate ( lv_update_sc.standing_charge_id
347 , lv_update_sc.standing_charge_date
348 );
349 ELSE
350 ld_new_sc_date := NULL;
351 END IF;
352 --WriteToLog ( ' New Next due Date '|| ld_new_next_due_date );
353 --WriteToLog ( ' New GL Date '|| ld_new_sc_date );
354
355 -- bug 3199481, start block
356 IF (l_state_level >= l_debug_level) THEN
357 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg5',
358 ' New Next due Date '|| ld_new_next_due_date );
359 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg6',
360 ' New GL Date '|| ld_new_sc_date );
361 END IF;
362 -- bug 3199481, end block
363
364 IF ld_new_next_due_date is NULL THEN
365 --WriteToLog ( 'New Next due date is null.');
366 -- bug 3199481, start block
367 IF (l_state_level >= l_debug_level) THEN
368 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg7',
369 'New Next due date is null.');
370 END IF;
371 -- bug 3199481, end block
372 return ;
373 END IF;
374
375 IF ld_new_sc_date is NULL THEN
376 --WriteToLog ( 'New GL date is null ');
377 -- bug 3199481, start block
378 IF (l_state_level >= l_debug_level) THEN
379 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg8',
380 'New GL date is null ');
381 END IF;
382 -- bug 3199481, end block
383 /*Bug no 2688741 Commented by shsaxena*/
384 -- return ;
385 END IF;
386
387 UPDATE IGI_RPI_STANDING_CHARGES
388 SET next_due_date = ld_new_next_due_date,
389 standing_charge_date = ld_new_sc_date,
390 previous_due_date = lv_update_sc.next_due_date,
391 date_synchronized_flag = ALREADY_SYNC_STATUS
392 WHERE ROWID = lv_update_sc.sc_rowid
393 ;
394
395 FOR l_lines in C_lines( lv_update_sc.standing_charge_id)
396 LOOP
397 ld_new_ld_date := GetNewNextDate
398 ( lv_update_sc.standing_charge_id
399 , l_lines.start_date
400 );
401 IF ld_new_next_due_date is NULL THEN
402 --WriteToLog ( 'New Next due date is null.');
403 -- bug 3199481, start block
404 IF (l_state_level >= l_debug_level) THEN
405 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg9',
406 'New Next due date is null.');
407 END IF;
408 -- bug 3199481, end block
409 return ;
410 END IF;
411
412 UPDATE igi_rpi_line_details
413 SET start_date = ld_new_ld_date
414 WHERE rowid = l_lines.row_id
415 ;
416
417 END LOOP;
418 end if;
419
420 END LOOP;
421 --WriteToLog ( 'End UpateStandingCharges...');
422 -- bug 3199481, start block
423 IF (l_state_level >= l_debug_level) THEN
424 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg10',
425 'End UpateStandingCharges...');
426 END IF;
427 -- bug 3199481, end block
428 END ;
429
430 PROCEDURE synchronize_dates ( errbuf OUT NOCOPY VARCHAR2
431 , retcode OUT NOCOPY NUMBER
432 , p_run_date1 in VARCHAR2
433 , p_set_of_books_id in NUMBER
434 , p_batch_source_id in NUMBER
435 , p_standing_charge_id in number
436 , p_undo_last_change in varchar2
437
438 ) IS
439 l_next_due_date date;
440 b_no_recs boolean;
441 lv_rowid varchar2(25);
442
443 /*Modified by panaraya Start*/
444 p_run_date DATE;
445 /*Modified by panaraya End */
446
447
448 CURSOR c_undo_sc IS
449 SELECT sc.standing_charge_id, sc.generate_sequence
450 , sc.batch_source_id
451 , sc.charge_reference
452 FROM igi_rpi_standing_charges sc
453 WHERE sc.set_of_books_id = p_set_of_books_id
454 AND sc.standing_charge_id = nvl( p_standing_charge_id
455 , sc.standing_charge_id )
456 AND sc.batch_source_id = nvl(p_batch_source_id,
457 sc.batch_source_id)
458 AND sc.status = CHARGE_STATUS
459 AND sc.next_due_date <= p_run_date
460 and sc.date_synchronized_flag = NOT_SYNC_STATUS
461 ;
462 -- CURSOR C_successful_trx (cp_batch_source_id in number) IS
463 CURSOR C_successful_trx (cp_batch_source_id in number, cp_standing_charge_id in number) IS /* Bug 3951039 agovil */
464 SELECT rct.interface_header_attribute1 trx_sc_id
465 , rct.interface_header_attribute2 trx_seq
466 , rct.customer_Trx_id
467 , rct.trx_date
468 , rsc.next_due_date
469 , rsc.advance_arrears_ind
470 , rsc.batch_source_id
471 , rsc.default_invoicing_rule
472 from ra_customer_trx rct
473 , igi_rpi_standing_charges rsc
474 where rct.batch_source_id = nvl(cp_batch_source_id,
475 rct.batch_source_id )
476 and rct.set_of_books_id = p_set_of_books_id
477 and to_char(rsc.standing_charge_id) = rct.interface_header_attribute1
478 and to_char(rsc.generate_sequence) = rct.interface_header_attribute2
479 and rsc.set_of_books_id = p_set_of_books_id
480 -- AND rsc.standing_charge_id = nvl( p_standing_charge_id
481 -- , rsc.standing_charge_id )
482 AND rsc.standing_charge_id = nvl( cp_standing_charge_id
483 , rsc.standing_charge_id ) /* BUG 3951309 agovil */
484 AND rsc.batch_source_id = nvl(cp_batch_source_id
485 , rsc.batch_source_id )
486 and trunc(rsc.next_due_date) <= trunc(p_run_date)
487 and rsc.date_synchronized_flag = NOT_SYNC_STATUS
488 and exists
489 ( select 'x'
490 from igi_ar_system_options
491 where rpi_header_context_code = rct.interface_header_context
492 )
493 and exists
494 ( select 'x'
495 from ra_customer_trx_lines rctl
496 where rctl.customer_trx_id = rct.customer_trx_id
497 )
498 ;
499
500 /*Added additional columns in line_det for retrieving price and effective date to update
501 the Standing Charges and Price History - RPI Enhancement.*/
502 cursor line_det ( cp_sc_id in number) is
503 select line_item_id,
504 revised_price,
505 revised_effective_date,
506 run_id,
507 org_id,
508 charge_item_number,
509 item_id,
510 price,
511 current_effective_date
512 from igi_rpi_line_details_all
513 where standing_charge_id = cp_sc_id
514 ;
515
516 BEGIN
517 b_no_recs := false;
518 p_run_date := to_date(p_run_date1,'YYYY/MM/DD HH24:MI:SS');
519 IF IGI_GEN.Is_Req_Installed('RPI') THEN
520 NULL;
521 ELSE
522 fnd_message.set_name( 'IGI', 'IGI_RPI_IS_DISABLED');
523 --Bug 3199481 (start)
524 If (l_unexp_level >= l_debug_level) then
525 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igirrps.synchronize_dates.Msg0',FALSE);
526 End if;
527 --Bug 3199481 (end)
528 errbuf := fnd_message.get;
529 retcode := 2;
530 return;
531 END IF;
532 /*Added p_undo_last_change = 'N' in the If condn since parameter passed from the requst is N
533 Added by Panarayaa for RPI Enhancement*/
534
535 IF (p_undo_last_change = 'NO'OR p_undo_last_change = 'N' ) THEN
536 null;
537 ELSE
538 FOR l_sc in c_undo_sc LOOP
539 b_no_recs := TRUE;
540
541 --WriteToLog ( ' Verifying Standing charge ref '||
542 -- l_sc.charge_reference );
543 --WriteToLog ( ' Generate sequence '||
544 -- l_sc.generate_sequence );
545
546 -- bug 3199481, start block
547 IF (l_state_level >= l_debug_level) THEN
548 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg1',
549 ' Verifying Standing charge ref '||
550 l_sc.charge_reference );
551 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg2',
552 ' Generate sequence '||
553 l_sc.generate_sequence );
554 END IF;
555 -- bug 3199481, end block
556 -- FOR l_s_trx in c_successful_trx ( l_sc.batch_source_id ) LOOP
557 FOR l_s_trx in c_successful_trx ( l_sc.batch_source_id, l_sc.standing_charge_id ) LOOP /* Bug 3951309 agovil */
558 b_no_recs := FALSE;
559 --WriteToLog ( ' Invoice Generated for this standing charge '||
560 -- l_sc.standing_charge_id );
561 -- bug 3199481, start block
562 IF (l_state_level >= l_debug_level) THEN
563 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg3',
564 ' Invoice Generated for this standing charge '||
565 l_sc.standing_charge_id );
566 END IF;
567 -- bug 3199481, end block
568 END LOOP;
569
570 IF b_no_recs THEN
571 --WriteToLog ( ' Evaluating interface information for '||
572 -- l_sc.standing_charge_id );
573 -- bug 3199481, start block
574 IF (l_state_level >= l_debug_level) THEN
575 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg4',
576 ' Evaluating interface information for '||
577 l_sc.standing_charge_id );
578 END IF;
579 -- bug 3199481, end block
580
581 declare
582 cursor c_xface is
583 SELECT interface_line_id
584 FROM ra_interface_lines
585 WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
586 AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
587 ;
588 begin
589 FOR l_xface in c_xface LOOP
593 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg5',
590 --WriteToLog ( ' Found the errors information.');
591 -- bug 3199481, start block
592 IF (l_state_level >= l_debug_level) THEN
594 ' Found the errors information.');
595 END IF;
596 -- bug 3199481, end block
597 delete from ra_interface_errors
598 where interface_line_id = l_xface.interface_line_id
599 ;
600 if sql%found then
601 --WriteToLog ( ' Deleted the errors information.');
602 -- bug 3199481, start block
603 IF (l_state_level >= l_debug_level) THEN
604 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg6',
605 ' Deleted the errors information.');
606 END IF;
607 -- bug 3199481, end block
608 end if;
609
610 END LOOP;
611
612 delete from ra_interface_salescredits
613 WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
614 AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
615 ;
616 if sql%found then
617 --WriteToLog ( ' Deleted the Sales information.');
618 -- bug 3199481, start block
619 IF (l_state_level >= l_debug_level) THEN
620 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg7',
621 ' Deleted the Sales information.');
622 END IF;
623 -- bug 3199481, end block
624 end if;
625 delete from ra_interface_distributions
626 WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
627 AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
628 ;
629 if sql%found then
630 --WriteToLog ( ' Deleted the Distribution information.');
631 -- bug 3199481, start block
632 IF (l_state_level >= l_debug_level) THEN
633 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg8',
634 ' Deleted the Distribution information.');
635 END IF;
636 -- bug 3199481, end block
637 end if;
638 delete from ra_interface_lines
639 WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
640 AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
641 ;
642 --WriteToLog ( ' Interface information deleted for '||
643 -- l_sc.standing_charge_id );
644 -- bug 3199481, start block
645 IF (l_state_level >= l_debug_level) THEN
646 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg9',
647 ' Interface information deleted for '||
648 l_sc.standing_charge_id );
649 END IF;
650 -- bug 3199481, end block
651 update igi_rpi_standing_charges
652 set date_synchronized_flag = ALREADY_SYNC_STATUS
653 where standing_charge_id = l_sc.standing_charge_id
654 and generate_sequence = l_sc.generate_sequence
655 and date_synchronized_flag = NOT_SYNC_STATUS
656 ;
657
658 --fnd_file.put_line ( fnd_file.log, ' Synchronization flag reset for '||
659 -- l_sc.charge_reference );
663 ' Synchronization flag reset for '||
660 -- bug 3199481, start block
661 IF (l_state_level >= l_debug_level) THEN
662 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg9',
664 l_sc.charge_reference );
665 END IF;
666 -- bug 3199481, end block
667 exception when others then null;
668 end;
669
670 END IF ;
671 END LOOP;
672 END IF;
673 /* process successful trx */
674 -- FOR l_trx in C_successful_trx (p_batch_source_id) LOOP
675 FOR l_trx in C_successful_trx (p_batch_source_id, p_standing_charge_id) LOOP /* Bug 3951309 agovil */
676
677 -- WriteToLog ( 'Trx Date = '|| l_trx.trx_date );
678 -- WriteToLog ( 'Next due Date = '|| l_trx.next_due_date );
679 -- WriteToLog ( 'Charge ID = '|| l_trx.trx_sc_id );
680 -- WriteToLog ( 'Sequence = '|| l_trx.trx_seq );
681
682 -- bug 3199481, start block
683 IF (l_state_level >= l_debug_level) THEN
684 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg10',
685 'Trx Date = '|| l_trx.trx_date );
686 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg11',
687 'Next due Date = '|| l_trx.next_due_date );
688 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg12',
689 'Charge ID = '|| l_trx.trx_sc_id );
690 FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.synchronize_dates.Msg13',
691 'Sequence = '|| l_trx.trx_seq );
692 END IF;
693 -- bug 3199481, end block
694
695 -- if trunc(l_trx.trx_date) = trunc(l_trx.next_due_date) then /* commented line for bug 3938731 agovil */
696 /* WriteToLog ( 'Trx Date = Nxt Due Date ...');*/
697 if nvl(l_trx.advance_arrears_ind, l_trx.DEFAULT_INVOICING_RULE ) = INVOICING_RULE then
698 /* WriteToLog ( 'Advance ... Updating...');*/
699 UpdateStandingCharges ( l_trx.trx_sc_id ,l_trx.trx_seq ) ;
700 end if;
701 -- end if; /* commented line for bug 3938731 agovil */
702
703 /* fetch new value of next due date*/
704 Select next_due_date
705 into l_next_due_date
706 From igi_rpi_standing_charges
707 Where standing_charge_id = l_trx.trx_sc_id
708 And generate_sequence = l_trx.trx_seq
709 ;
710 for line_rec in line_det ( l_trx.trx_sc_id ) loop
711
712 /*Added the IF condn and the TBH call for audit table IGI_RPI_LINE_AUDIT_DET_ALL
713 Added by Panaraya for RPI Enhancement - Start
714
715 Modified the condition for IF condn to (trunc(line_rec.revised_effective_date) <= trunc(p_run_date))
716 and the where condn for next due date in update statement
717 for Bug NO 2454958 */
718 IF (trunc(line_rec.revised_effective_date) <= trunc(p_run_date)) THEN
719
720 igi_rpi_line_audit_det_all_pkg.insert_row (
721 x_mode => 'R',
722 x_rowid => lv_rowid,
723 x_standing_charge_id => TO_NUMBER (l_trx.trx_sc_id),
724 x_line_item_id => TO_NUMBER (line_rec.LINE_ITEM_ID),
725 x_charge_item_number => TO_NUMBER (line_rec.CHARGE_ITEM_NUMBER),
726 x_item_id => TO_NUMBER (line_rec.ITEM_ID),
727 x_price => line_rec.REVISED_PRICE,
728 x_effective_date => line_rec.REVISED_EFFECTIVE_DATE,
729 x_revised_price => null,
730 x_revised_effective_date => null,
731 x_run_id => TO_NUMBER (line_rec.RUN_ID),
732 x_org_id => TO_NUMBER (line_rec.ORG_ID),
733 x_previous_price => line_rec.PRICE,
737 update igi_rpi_line_details_all
734 x_previous_effective_date => line_rec.CURRENT_EFFECTIVE_DATE
735
736 );
738 set previous_price = price,
739 previous_effective_date = current_effective_date,
740 price = revised_price,
741 current_effective_date = revised_effective_date,
742 revised_price = '',
743 revised_effective_date = ''
744 where line_item_id = line_rec.line_item_id
745 and trunc(revised_effective_date)
746 <= trunc(p_run_date);
747
748
749 END IF;
750 /*Added by Panaraya for RPI Enhancement - End */
751 end loop;
752
753 if nvl(l_trx.advance_arrears_ind,l_trx.default_invoicing_rule) = 'ARREARS' then
754 UpdateStandingCharges ( l_trx.trx_sc_id ,l_trx.trx_seq ) ;
755 end if;
756
757
758
759 END LOOP;
760 COMMIT;
761
762 errbuf := 'Normal Completion';
763 retcode := 0;
764
765 EXCEPTION WHEN OTHERS THEN
766 rollback;
767 errbuf := SQLERRM;
768 retcode := 2;
769 END ;
770
771 BEGIN
772
773 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
774 l_state_level := FND_LOG.LEVEL_STATEMENT;
775 l_proc_level := FND_LOG.LEVEL_PROCEDURE;
776 l_event_level := FND_LOG.LEVEL_EVENT;
777 l_excep_level := FND_LOG.LEVEL_EXCEPTION;
778 l_error_level := FND_LOG.LEVEL_ERROR;
779 l_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
780
781 END ;