[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RGM_TERMS_PKG
Source
1 PACKAGE BODY jai_cmn_rgm_terms_pkg AS
2 /* $Header: jai_cmn_rgm_term.plb 120.1 2005/07/20 12:57:35 avallabh ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_cmn_rgm_term -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.3
15 Modified the object to remove literals from DML statements and CURSORS.
16
17 */
18
19 /***************************************************************************************************
20 CREATED BY : rallamse
21 CREATED DATE : 25-FEB-2005
22 ENHANCEMENT BUG :
23 PURPOSE : To provide and process claim term information
24 CALLED FROM :
25
26 ***************************************************************************************************/
27
28 /**************************************************
29 || Generate the instalment date
30 || Private procedure
31 ***************************************************/
32 PROCEDURE set_date
33 (
34 pn_interval_days IN NUMBER ,
35 pn_interval_months IN NUMBER ,
36 pd_date IN OUT NOCOPY DATE
37 )
38 IS
39
40 ln_last_day NUMBER ;
41 ln_day NUMBER ;
42
43 /* Added by Ramananda for bug#4407165 */
44 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_terms_pkg.set_date';
45
46
47 BEGIN
48
49 pd_date := add_months ( pd_date , pn_interval_months ) ;
50
51 ln_last_day := to_number ( to_char ( LAST_DAY ( pd_date ) , 'DD' ) ) ;
52
53 IF pn_interval_days > ln_last_day THEN
54
55 ln_day := ln_last_day ;
56
57 ELSE
58
59 ln_day := pn_interval_days ;
60
61 END IF ;
62
63 pd_date := pd_date + ln_day - to_number ( to_char ( pd_date , 'DD' ) ) ;
64
65
66 /* Added by Ramananda for bug#4407165 */
67 EXCEPTION
68 WHEN OTHERS THEN
69 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
70 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
71 app_exception.raise_exception;
72
73 END set_date ;
74
75
76 /**************************************************
77 || Insert values into jai_rgm_trm_schedules_t
78 || Private procedure
79 ***************************************************/
80
81 PROCEDURE insert_jai_rgm_trm_schedules_t
82 (
83 pn_schedule_id IN JAI_RGM_TRM_SCHEDULES_T.SCHEDULE_ID%TYPE ,
84 pn_instalment_no IN JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_NO%TYPE ,
85 pn_instalment_amt IN JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_AMOUNT%TYPE ,
86 pd_instalment_date IN JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_DATE%TYPE
87 )
88 IS
89
90 /* Added by Ramananda for bug#4407165 */
91 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_terms_pkg.insert_jai_rgm_trm_schedules_t';
92
93 BEGIN
94
95 INSERT INTO jai_rgm_trm_schedules_t
96 (
97 SCHEDULE_ID ,
98 INSTALLMENT_NO ,
99 INSTALLMENT_AMOUNT ,
100 INSTALLMENT_DATE ,
101 CREATION_DATE ,
102 CREATED_BY ,
103 LAST_UPDATE_DATE ,
104 LAST_UPDATE_LOGIN ,
105 LAST_UPDATED_BY
106 )
107 VALUES
108 (
109 pn_schedule_id ,
110 pn_instalment_no ,
111 pn_instalment_amt ,
112 pd_instalment_date ,
113 SYSDATE ,
114 999999 ,
115 SYSDATE ,
116 NULL ,
117 NULL
118 ) ;
119 /* Added by Ramananda for bug#4407165 */
120 EXCEPTION
121 WHEN OTHERS THEN
122 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
123 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
124 app_exception.raise_exception;
125
126 END insert_jai_rgm_trm_schedules_t ;
127
128 /**************************************************
129 || Generate the payment schedule based upon
130 || the term defined
131 ***************************************************/
132 PROCEDURE generate_term_schedules
133 (
134 p_term_id IN JAI_RGM_TERMS.TERM_ID%TYPE ,
135 p_amount IN NUMBER ,
136 p_register_date IN DATE ,
137 p_schedule_id OUT NOCOPY JAI_RGM_TRM_SCHEDULES_T.SCHEDULE_ID%TYPE ,
138 p_process_flag OUT NOCOPY VARCHAR2 ,
139 p_process_msg OUT NOCOPY VARCHAR2
140 )
141 IS
142
143 CURSOR cur_term_details ( p_rgm_term_id NUMBER )
144 IS
145 SELECT
146 start_period ,
147 DECODE ( start_period, 'NEXT_MONTH' , 1 , 'NEXT_QRTR' , 3 , 'NEXT_FIN_YEAR' , 12 ) ,
148 start_day_of_month ,
149 start_frequency_day ,
150 number_of_instalments ,
151 instalment_frequency ,
152 instalment_period ,
153 DECODE ( instalment_period, 'MONTH' , 1 , 'QRTR' , 3 , 'YEAR' , 12 ) ,
154 day_of_month ,
155 frequency_day
156 FROM
157 jai_rgm_terms
158 WHERE
159 term_id = p_rgm_term_id ;
160
161 CURSOR cur_sequence
162 IS
163 SELECT
164 jai_rgm_trm_schedules_t_s.nextval
165 FROM
166 dual ;
167
168 /*
169 || Declare the rows to be extracted from table
170 || based upon cursor
171 */
172 lv_start_period JAI_RGM_TERMS.start_period %TYPE ;
173 lv_no_of_instalments JAI_RGM_TERMS.number_of_instalments %TYPE ;
174 lv_instalment_freq JAI_RGM_TERMS.instalment_frequency %TYPE ;
175 lv_instalment_period JAI_RGM_TERMS.instalment_period %TYPE ;
176 ln_day_of_month JAI_RGM_TERMS.day_of_month %TYPE ;
177 lv_frequency_day JAI_RGM_TERMS.frequency_day %TYPE ;
178 lv_st_day_of_month JAI_RGM_TERMS.start_day_of_month %TYPE ;
179 lv_st_frequency_day JAI_RGM_TERMS.start_frequency_day %TYPE ;
180
181 ln_instalment_amt JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_AMOUNT%TYPE ; /* Amount for each instalment */
182 ld_start_date JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_DATE%TYPE ; /* Stores date for 1st instalment */
183 ld_next_date JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_DATE%TYPE ; /* Date for subsequenct instalments */
184
185 ln_interval_months NUMBER ; /* Based upon instalment_period */
186 ln_st_interval_days NUMBER ; /* Based upon st_instalment_perio */
187 ln_start_interval_months NUMBER ; /* Interval based upon start period */
188 ln_interval_days NUMBER ; /* Interval based upon frequency_day */
189
190 BEGIN
191
192 /*
193 || Validate the input parameters term_id and amount
194 */
195
196 IF NVL(p_term_id,0) = 0 THEN
197
198 p_process_flag := jai_constants.expected_error;
199 p_process_msg := 'jai_rgm_claims_pkg.prepare_term_schedules => Term Id cannot be NULL' ;
200 return;
201
202 END IF;
203
204 IF NVL(p_amount,-1) <= 0 THEN
205
206 p_process_flag := jai_constants.expected_error;
207 p_process_msg := 'jai_rgm_claims_pkg.prepare_term_schedules => Amount should not be NULL or Zero or Negative' ;
208 return;
209
210 END IF ;
211
212 /*
213 || Check that the term exists based upon p_term_id
214 */
215
216 OPEN cur_term_details ( p_term_id ) ;
217 FETCH cur_term_details INTO lv_start_period ,
218 ln_start_interval_months ,
219 lv_st_day_of_month ,
220 lv_st_frequency_day ,
221 lv_no_of_instalments ,
222 lv_instalment_freq ,
223 lv_instalment_period ,
224 ln_interval_months ,
225 ln_day_of_month ,
226 lv_frequency_day ;
227
228 IF NOT ( cur_term_details % FOUND ) THEN
229
230 p_process_flag := jai_constants.expected_error ;
231 p_process_msg := 'jai_rgm_claims_pkg.prepare_term_schedules => Term is not defined in jai_rgm_terms' ;
232 return ;
233
234 END IF ;
235
236 CLOSE cur_term_details ;
237
238 /*
239 || Calculate each instalment amount based upon no_of_instalments
240 */
241
242 IF lv_no_of_instalments > 0 THEN
243
244 ln_instalment_amt := p_amount / lv_no_of_instalments ;
245
246 ELSE
247
248 p_process_flag := jai_constants.expected_error;
249 p_process_msg := 'jai_rgm_claims_pkg.prepare_term_schedules => Instalment Frequency is zero or negative ';
250 return;
251
252 END IF ;
253
254 ld_start_date := trunc ( p_register_date ) ;
255
256 IF lv_st_frequency_day = 'FIRST' THEN
257
258 ln_st_interval_days := 1 ;
259
260 ELSIF lv_st_frequency_day = 'SAME' THEN
261
262 ln_st_interval_days := to_number ( to_char ( ld_start_date , 'DD' ) );
263
264 ELSIF lv_st_frequency_day = 'LAST' THEN
265
266 ln_st_interval_days := 31 ;
267
268 ELSIF lv_st_frequency_day = 'OTHER' THEN
269
270 ln_st_interval_days := lv_st_day_of_month ;
271
272 END IF;
273
274 IF lv_frequency_day = 'FIRST' THEN
275
276 ln_interval_days := 1 ;
277
278 ELSIF lv_frequency_day = 'SAME' THEN
279
280 ln_interval_days := to_number ( to_char ( ld_start_date , 'DD' ) );
281
282 ELSIF lv_frequency_day = 'LAST' THEN
283
284 ln_interval_days := 31 ;
285
286 ELSIF lv_frequency_day = 'OTHER' THEN
287
288 ln_interval_days := ln_day_of_month ;
289
290 END IF;
291
292 IF lv_start_period <> 'IMMEDIATE' THEN
293
294 set_date
295 (
296 ln_st_interval_days ,
297 ln_start_interval_months ,
298 ld_start_date
299 ) ;
300
301 END IF ;
302
303 OPEN cur_sequence ;
304 FETCH cur_sequence INTO p_schedule_id ;
305 CLOSE cur_sequence ;
306
307 /*
308 || Insert the first instalment
309 || on start_date
310 */
311 insert_jai_rgm_trm_schedules_t
312 (
313 p_schedule_id ,
314 1 ,
315 ln_instalment_amt ,
316 ld_start_date
317 );
318
319 ld_next_date := ld_start_date ;
320
321 ln_interval_months := lv_instalment_freq * ln_interval_months ;
322
323 FOR cur_inst_no IN 2..lv_no_of_instalments
324 LOOP
325
326 set_date
327 (
328 ln_interval_days ,
329 ln_interval_months ,
330 ld_next_date
331 ) ;
332
333 /*
334 || Insert the subsequent instalments
335 */
336 insert_jai_rgm_trm_schedules_t
337 (
338 p_schedule_id ,
339 cur_inst_no ,
340 ln_instalment_amt ,
341 ld_next_date
342 ) ;
343
344 END LOOP ;
345
346 p_process_flag := jai_constants.successful ;
347 return ;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 p_process_flag := jai_constants.unexpected_error ;
352 p_process_msg := 'Error Occured in jai_rgm_claims_pkg.prepare_term_schedules - ' || substr(sqlerrm,1,900) ;
353
354 END generate_term_schedules ;
355
356
357 /**************************************************
358 || Get the term defined for the assignment and
359 || if not defined return the default term
360 || If default term does not exist return Error
361 ***************************************************/
362
363 PROCEDURE get_term_id
364 (
365 p_regime_id IN JAI_RGM_TERM_ASSIGNS.REGIME_ID %TYPE ,
366 p_item_id IN NUMBER ,
367 p_organization_id IN JAI_RGM_TERM_ASSIGNS.ORGANIZATION_ID %TYPE ,
368 p_party_type IN JAI_RGM_TERM_ASSIGNS.ORGANIZATION_TYPE %TYPE ,
369 p_location_id IN JAI_RGM_TERM_ASSIGNS.LOCATION_ID %TYPE ,
370 p_term_id OUT NOCOPY JAI_RGM_TERM_ASSIGNS.TERM_ID %TYPE ,
371 p_process_flag OUT NOCOPY VARCHAR2 ,
372 p_process_msg OUT NOCOPY VARCHAR2
373 )
374 IS
375
376 CURSOR cur_get_regime_regno
377 ( p_regime_id NUMBER ,
378 p_org_id NUMBER ,
379 p_loc_id NUMBER ,
380 p_att_code jai_rgm_registrations.attribute_code%TYPE , --rchandan for bug#4428980
381 p_att_type_code jai_rgm_registrations.attribute_type_code%TYPE--rchandan for bug#4428980
382 )
383 IS
384 SELECT attribute_value
385 FROM JAI_RGM_ORG_REGNS_V jorrv
386 WHERE jorrv.regime_id = p_regime_id
387 AND jorrv.attribute_code = p_att_code
388 AND jorrv.attribute_type_code = p_att_type_code
389 AND jorrv.organization_id = p_org_id
390 AND jorrv.organization_type = p_party_type
391 AND jorrv.location_id = nvl ( p_loc_id , jorrv.location_id ) ;
392
393 CURSOR cur_get_regime_code ( p_regime_id NUMBER )
394 IS
395 SELECT regime_code
396 FROM JAI_RGM_DEFINITIONS
397 WHERE regime_id = p_regime_id ;
398
399 CURSOR cur_term_id
400 (
401 p_reg_id NUMBER ,
402 p_reg_item_class VARCHAR2 ,
403 p_reg_regno VARCHAR2 ,
404 p_org_id NUMBER ,
405 p_party_type VARCHAR2 ,
406 p_loc_id NUMBER
407 )
408 IS
409 SELECT term_id
410 FROM JAI_RGM_TERM_ASSIGNS
411 WHERE NVL ( location_id , p_loc_id ) = p_loc_id
412 AND NVL ( organization_type , p_party_type ) = p_party_type
413 AND NVL ( organization_id , p_org_id ) = p_org_id
414 AND NVL ( regime_regno , p_reg_regno ) = p_reg_regno
415 AND NVL ( regime_item_class , p_reg_item_class ) = p_reg_item_class
416 AND regime_id = p_reg_id ;
417
418
419 lv_reg_item_class JAI_RGM_TERM_ASSIGNS.REGIME_ITEM_CLASS%TYPE ;
420 lv_regime_regno JAI_RGM_TERM_ASSIGNS.REGIME_REGNO %TYPE ;
421 lv_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE %TYPE ;
422 lv_process_flag VARCHAR2(3) ;
423 lv_process_msg VARCHAR2(1000);
424
425 BEGIN
426
427 p_term_id := NULL ;
428 lv_reg_item_class := NULL ;
432
429 lv_regime_code := NULL ;
430 lv_process_flag := NULL ;
431 lv_process_msg := NULL ;
433 /*
434 || Validate the input parameters
435 */
436
437 IF p_regime_id IS NULL THEN
438
439 p_process_flag := jai_constants.expected_error;
440 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Regime ID cannot be NULL' ;
441 return;
442
443 END IF;
444
445 IF p_item_id IS NULL THEN
446
447 p_process_flag := jai_constants.expected_error;
448 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Item ID cannot be NULL' ;
449 return;
450
451 END IF ;
452
453 IF p_organization_id IS NULL THEN
454
455 p_process_flag := jai_constants.expected_error;
456 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Organization ID cannot be NULL' ;
457 return;
458
459 END IF ;
460
461 IF p_party_type IS NULL THEN
462
463 p_process_flag := jai_constants.expected_error;
464 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Party Type cannot be NULL' ;
465 return;
466
467 END IF ;
468
469
470 IF p_location_id IS NULL THEN
471
472 p_process_flag := jai_constants.expected_error;
473 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Location ID cannot be NULL' ;
474 return;
475
476 END IF ;
477
478 OPEN cur_get_regime_regno
479 (
480 p_regime_id => p_regime_id ,
481 p_org_id => p_organization_id ,
482 p_loc_id => p_location_id,
483 p_att_code => 'REGISTRATION_NO', --rchandan for bug#4428980
484 p_att_type_code => 'PRIMARY' --rchandan for bug#4428980
485 ) ;
486 FETCH cur_get_regime_regno INTO lv_regime_regno ;
487 CLOSE cur_get_regime_regno ;
488
489
490 OPEN cur_get_regime_code ( p_regime_id ) ;
491 FETCH cur_get_regime_code INTO lv_regime_code ;
492 CLOSE cur_get_regime_code ;
493
494 jai_inv_items_pkg.jai_get_attrib
495 (
496 p_regime_code => lv_regime_code ,
497 p_organization_id => p_organization_id ,
498 p_inventory_item_id => p_item_id ,
499 p_attribute_code => 'ITEM CLASS' ,
500 p_attribute_value => lv_reg_item_class ,
501 p_process_flag => lv_process_flag ,
502 p_process_msg => lv_process_msg
503 );
504
505 IF ( lv_reg_item_class IS NULL ) OR ( lv_process_flag <> jai_constants.successful ) THEN
506
507 p_process_flag := jai_constants.expected_error;
508 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => jai_inv_items_pkg.jai_get_attrib => p_process_flag :: lv_process_msg' ;
509 return;
510
511 END IF ;
512
513
514 OPEN cur_term_id ( p_reg_id => p_regime_id ,
515 p_reg_item_class => lv_reg_item_class ,
516 p_reg_regno => lv_regime_regno ,
517 p_org_id => p_organization_id ,
518 p_party_type => p_party_type ,
519 p_loc_id => p_location_id
520 ) ;
521 FETCH cur_term_id INTO p_term_id ;
522 CLOSE cur_term_id ;
523
524 IF p_term_id IS NULL THEN
525
526 p_process_flag := jai_constants.expected_error ;
527 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => No term defined' ;
528 RETURN ;
529
530 END IF ;
531
532 p_process_flag := jai_constants.successful ;
533 p_process_msg := NULL;
534
535 EXCEPTION
536 WHEN others THEN
537
538 p_process_flag := jai_constants.unexpected_error;
539 p_process_msg := 'JAI_CMN_RG_OTHERS.get_term_id => Error Occured : ' || substr(sqlerrm,1,1000) ;
540
541 END get_term_id ;
542
543 PROCEDURE set_term_in_use
544 (
545 p_term_id IN JAI_RGM_TERMS.TERM_ID%TYPE ,
546 p_process_flag OUT NOCOPY VARCHAR2 ,
547 p_process_msg OUT NOCOPY VARCHAR2
548 )
549 IS
550
551 CURSOR cur_term_flag ( cp_term_id NUMBER )
552 IS
553 SELECT term_in_use_flag
554 FROM jai_rgm_terms
555 WHERE term_id = p_term_id ;
556
557
558 lv_term_in_use_flag VARCHAR2(1) ;
559
560 BEGIN
561
562 /*
563 || Validate the input parameters
564 */
565
566 IF p_term_id IS NULL THEN
567
568 p_process_flag := jai_constants.expected_error;
569 p_process_msg := 'jai_rgm_claims_pkg.set_term_in_use_flag => Term ID cannot be NULL' ;
570 return;
571
572 END IF;
573
574 OPEN cur_term_flag ( cp_term_id => p_term_id );
575 FETCH cur_term_flag INTO lv_term_in_use_flag ;
576 IF NOT ( cur_term_flag%FOUND ) THEN
577
578 p_process_flag := jai_constants.expected_error;
579 p_process_msg := 'jai_rgm_claims_pkg.set_term_in_use_flag => Term ID does not exist in database' ;
580 CLOSE cur_term_flag ;
581 return;
582
583 END IF;
584 CLOSE cur_term_flag ;
585
586 IF lv_term_in_use_flag <> 'Y' THEN
587
588 UPDATE jai_rgm_terms
589 SET term_in_use_flag = 'Y' ,
590 last_update_date = sysdate ,
594
591 last_update_login = fnd_global.login_id ,
592 last_updated_by = fnd_global.user_id
593 WHERE term_id = p_term_id ;
595 END IF ;
596
597 p_process_flag := jai_constants.successful ;
598 p_process_msg := NULL;
599
600 EXCEPTION
601 WHEN others THEN
602
603 p_process_flag := jai_constants.unexpected_error;
604 p_process_msg := 'JAI_CMN_RG_OTHERS.set_term_in_use_flag => Error Occured : ' || substr(sqlerrm,1,1000) ;
605
606 END set_term_in_use ;
607
608 END jai_cmn_rgm_terms_pkg ;