1 PACKAGE BODY jai_cmn_rgm_terms_pkg AS
2 /* $Header: jai_cmn_rgm_term.plb 120.5.12020000.2 2012/12/06 14:32:56 zxin 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 09-Jul-2009 CSahoo for bug#8667768, File Version 120.1.12000000.2
17 added code in the procedure jai_cmn_rgm_terms_pkg.generate_term_schedules to to
18 calculate claim dates for the option "first day of financial year".
19
20 20-Mar-2010 Bo Li for the Flexible VAT Recovery Schedule
21 This ER is to enhance the existing recovery of VAT Taxes in multiple installments in more generic scenarios.
22
23 */
24
25 /***************************************************************************************************
26 CREATED BY : rallamse
27 CREATED DATE : 25-FEB-2005
28 ENHANCEMENT BUG :
29 PURPOSE : To provide and process claim term information
30 CALLED FROM :
31
32 ***************************************************************************************************/
33
34 /**************************************************
35 || Generate the instalment date
36 || Private procedure
37 ***************************************************/
38 PROCEDURE set_date
39 (
40 pn_interval_days IN NUMBER ,
41 pn_interval_months IN NUMBER ,
42 pd_date IN OUT NOCOPY DATE
43 )
44 IS
45
46 ln_last_day NUMBER ;
47 ln_day NUMBER ;
48
49 /* Added by Ramananda for bug#4407165 */
50 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_terms_pkg.set_date';
51
52
53 BEGIN
54
55 pd_date := add_months ( pd_date , pn_interval_months ) ;
56
57 ln_last_day := to_number ( to_char ( LAST_DAY ( pd_date ) , 'DD' ) ) ;
58
59 IF pn_interval_days > ln_last_day THEN
60
61 ln_day := ln_last_day ;
62
63 ELSE
64
65 ln_day := pn_interval_days ;
66
67 END IF ;
68
69 pd_date := pd_date + ln_day - to_number ( to_char ( pd_date , 'DD' ) ) ;
70
71
72 /* Added by Ramananda for bug#4407165 */
73 EXCEPTION
74 WHEN OTHERS THEN
75 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
76 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
77 app_exception.raise_exception;
78
79 END set_date ;
80
81
82 /**************************************************
86
83 || Insert values into jai_rgm_trm_schedules_t
84 || Private procedure
85 ***************************************************/
87 PROCEDURE insert_jai_rgm_trm_schedules_t
88 (
89 pn_schedule_id IN JAI_RGM_TRM_SCHEDULES_T.SCHEDULE_ID%TYPE ,
90 pn_instalment_no IN JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_NO%TYPE ,
91 pn_instalment_amt IN JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_AMOUNT%TYPE ,
92 pd_instalment_date IN JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_DATE%TYPE
93 )
94 IS
95
96 /* Added by Ramananda for bug#4407165 */
97 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_terms_pkg.insert_jai_rgm_trm_schedules_t';
98
99 BEGIN
100
101 INSERT INTO jai_rgm_trm_schedules_t
102 (
103 SCHEDULE_ID ,
104 INSTALLMENT_NO ,
105 INSTALLMENT_AMOUNT ,
106 INSTALLMENT_DATE ,
107 CREATION_DATE ,
108 CREATED_BY ,
109 LAST_UPDATE_DATE ,
110 LAST_UPDATE_LOGIN ,
111 LAST_UPDATED_BY
112 )
113 VALUES
114 (
115 pn_schedule_id ,
116 pn_instalment_no ,
117 pn_instalment_amt ,
118 pd_instalment_date ,
119 SYSDATE ,
120 999999 ,
121 SYSDATE ,
122 NULL ,
123 NULL
124 ) ;
125 /* Added by Ramananda for bug#4407165 */
126 EXCEPTION
127 WHEN OTHERS THEN
128 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
129 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
130 app_exception.raise_exception;
131
132 END insert_jai_rgm_trm_schedules_t ;
133
134 /**************************************************
135 || Generate the payment schedule based upon
136 || the term defined
137 ***************************************************/
138 PROCEDURE generate_term_schedules
139 (
140 p_term_id IN JAI_RGM_TERMS.TERM_ID%TYPE ,
141 p_amount IN NUMBER ,
142 p_register_date IN DATE ,
143 p_schedule_id OUT NOCOPY JAI_RGM_TRM_SCHEDULES_T.SCHEDULE_ID%TYPE ,
144 p_process_flag OUT NOCOPY VARCHAR2 ,
145 p_process_msg OUT NOCOPY VARCHAR2
146 )
147 IS
148
149 --added by eric ma for VAT FLEX RECOVERY (Bug9494633)on Mar-09-2010,Begin
150 -----------------------------------------------------------------------------
151 CURSOR cur_term_details ( pn_rgm_term_id NUMBER )
152 IS
153 SELECT
154 rgtm.term_id
155 , rgtm.number_of_instalments
156 , rgci.installment_number
157 , rgci.claim_interval
158 , rgci.day_of_claim
159 , rgci.month_of_claim
160 , rgci.day_of_month
161 , rgci.claim_percentage
162 FROM
163 jai_rgm_terms rgtm
164 , jai_rgm_term_installments rgci
165 WHERE rgtm.term_id = rgci.term_id
166 AND rgtm.term_id = pn_rgm_term_id
167 ORDER BY installment_number;
168 -----------------------------------------------------------------------------
169 --added by eric ma for VAT FLEX RECOVERY (Bug9494633)on Mar-09-2010,End
170
171 CURSOR cur_sequence
172 IS
173 SELECT
174 jai_rgm_trm_schedules_t_s.nextval
175 FROM
176 dual ;
177
178 --added by eric ma for VAT FLEX RECOVERY (Bug9494633) on Mar-09-2010,Begin
179 -----------------------------------------------------------------------------
180 ln_installment_amount NUMBER;
181 ln_total_installment_amount NUMBER;
182 ln_installment_number NUMBER;
183 ln_number_of_instalments NUMBER;
184 ld_register_date DATE;
185 ld_installment_date DATE;
186 lv_FIRST_DAY_OF_FISCAL_YEAR VARCHAR(20) :='01-APR-';
187 lv_LAST_DAY_OF_FISCAL_YEAR VARCHAR(20) :='31-MAR-';
188
189 ln_rem_months_to_curr_qrtr NUMBER;
190 ln_current_qrtr NUMBER;
191 ln_current_fiscal_year NUMBER;
192 ln_next_fiscal_year NUMBER;
193 ln_current_month NUMBER;
194 ln_month_number NUMBER;
195 ln_specify_qrtr NUMBER;
196 lv_month_of_claim VARCHAR2(10);
197 -----------------------------------------------------------------------------
198 --added by eric ma for VAT FLEX RECOVERY (Bug9494633) on Mar-09-2010,End
199
200 /*
201 || Declare the rows to be extracted from table
202 || based upon cursor
203 */
204 lv_start_period JAI_RGM_TERMS.start_period %TYPE ;
205 lv_no_of_instalments JAI_RGM_TERMS.number_of_instalments %TYPE ;
206 lv_instalment_freq JAI_RGM_TERMS.instalment_frequency %TYPE ;
207 lv_instalment_period JAI_RGM_TERMS.instalment_period %TYPE ;
208 ln_day_of_month JAI_RGM_TERMS.day_of_month %TYPE ;
209 lv_frequency_day JAI_RGM_TERMS.frequency_day %TYPE ;
210 lv_st_day_of_month JAI_RGM_TERMS.start_day_of_month %TYPE ;
211 lv_st_frequency_day JAI_RGM_TERMS.start_frequency_day %TYPE ;
212
213 ln_instalment_amt JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_AMOUNT%TYPE ; /* Amount for each instalment */
214 ld_start_date JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_DATE%TYPE ; /* Stores date for 1st instalment */
218 ln_st_interval_days NUMBER ; /* Based upon st_instalment_perio */
215 ld_next_date JAI_RGM_TRM_SCHEDULES_T.INSTALLMENT_DATE%TYPE ; /* Date for subsequenct instalments */
216
217 ln_interval_months NUMBER ; /* Based upon instalment_period */
219 ln_start_interval_months NUMBER ; /* Interval based upon start period */
220 ln_interval_days NUMBER ; /* Interval based upon frequency_day */
221 ln_start_date_month NUMBER ; /* Month of the Instalment start date */
222 ln_start_date_year NUMBER ; /* Year of the Instalment start date */
223 BEGIN
224
225 /*
226 || Validate the input parameters term_id and amount
227 */
228
229 IF NVL(p_term_id,0) = 0 THEN
230
231 p_process_flag := jai_constants.expected_error;
232 p_process_msg := 'jai_rgm_claims_pkg.prepare_term_schedules => Term Id cannot be NULL' ;
233 return;
234
235 END IF;
236
237 IF NVL(p_amount,-1) <= 0 THEN
238
239 p_process_flag := jai_constants.expected_error;
240 p_process_msg := 'jai_rgm_claims_pkg.prepare_term_schedules => Amount should not be NULL or Zero or Negative' ;
241 return;
242
243 END IF ;
244
245 --added by eric ma for VAT FLEX RECOVERY (Bug9494633) on Mar-09-2010,Begin
246 -----------------------------------------------------------------------------
247 ld_register_date := TRUNC(p_register_date);
248
249 OPEN cur_sequence ;
250 FETCH cur_sequence INTO p_schedule_id ;
251 CLOSE cur_sequence ;
252
253 FOR rec_cur_term_details IN cur_term_details(p_term_id)
254 LOOP
255
256 ln_installment_number := rec_cur_term_details.installment_number;
257 ln_number_of_instalments := rec_cur_term_details.number_of_instalments;
258
259
260 ln_installment_amount := p_amount * (rec_cur_term_details.claim_percentage/100);
261
262 IF (rec_cur_term_details.claim_interval = 'IMMEDIATE')
263 THEN
264 ld_installment_date := ld_register_date;
265 ELSIF (rec_cur_term_details.claim_interval = 'NEXT_MONTH')
266 THEN
267 IF rec_cur_term_details.day_of_claim = 'SAME_DAY'
268 THEN
269 --There are 2 cases
270 --1) current day of month does not exists in next month.
271 -- E.g: current day = Jan-30, Feb-30 is not validated.
272 --2) current day of month exists in next month.
273 -- E.g: current day = Feb-29, Mar-29 is reasonalbe
274
275 ld_installment_date := ADD_MONTHS(ld_register_date,1);
276
277 --If the ld_register_date is the last day of the month,
278 --add_months(,1)will return the last day of next month. e.g Feb-28 , next_month = Mar-31
279 --To avoid this bug, add the below logic comparing the current day of month
280 --with the max days of next month,
281 IF EXTRACT(DAY FROM ld_register_date)< EXTRACT(DAY FROM ADD_MONTHS(ld_register_date,1))
282 THEN
283 ld_installment_date := TO_DATE( EXTRACT(DAY FROM ld_register_date )||'-'||
284 EXTRACT(MONTH FROM ld_installment_date)||'-'||
285 EXTRACT(YEAR FROM ld_installment_date)
286 , 'DD-MM-YYYY'
287 );
288 END IF;
289 ELSIF rec_cur_term_details.day_of_claim = 'FIRST_DAY'
290 THEN
291 ld_installment_date := last_day(ld_register_date)+1;
292 ELSIF rec_cur_term_details.day_of_claim = 'LAST_DAY'
293 THEN
294 ld_installment_date := last_day(add_months(ld_register_date, 1));
295 ELSIF rec_cur_term_details.day_of_claim = 'OTHER_DAY'
296 THEN
297 -- there are 2 cases:
298 -- 1)specified other day >= last day of next month , use the last day of next month
299 -- 2)specified other day < last day of next month , use the specified day of next month
300
301
302 --Get the last_day of next month
303 --Handle case 1:
304 --IF the specified day >= last_day of next month,use the last_day of next month
305 ld_installment_date := last_day(add_months(ld_register_date, 1));
306
307 --Handle case 2:
308 --If the specified day < max days of next month , use the specified day
309 IF(rec_cur_term_details.day_of_month < EXTRACT(DAY FROM ld_installment_date))
310 THEN
311 ld_installment_date := TO_DATE( rec_cur_term_details.day_of_month ||'-'||
312 EXTRACT(MONTH FROM ld_installment_date)||'-'||
313 EXTRACT(YEAR FROM ld_installment_date)
314 , 'DD-MM-YYYY'
315 );
316 END IF;--(rec_cur_term_details.day_of_month < EXTRACT(DAY FROM ld_installment_date))
317 END IF; --(rec_cur_term_details.day_of_claim = 'SAME_DAY' )
318
319 ELSIF (rec_cur_term_details.claim_interval = 'NEXT_QRTR')
320 THEN
321 --get the reminder months to current quarter = 3- (month- (quater-1)*3)
322 --EXTRACT(MONTH FROM ld_register_date) is used for getting current month
323 --TO_NUMBER(TO_CHAR(ld_register_date,'Q') is used for getting current quarter
324 ln_rem_months_to_curr_qrtr:= 3*(TO_NUMBER(TO_CHAR(ld_register_date,'Q')))-(EXTRACT(MONTH FROM ld_register_date));
325
326
327 IF (rec_cur_term_details.day_of_claim = 'FIRST_DAY')
328 THEN
329 --first day of next quarter = last_day of this quarter +1
330 ld_installment_date := last_day(add_months(ld_register_date,ln_rem_months_to_curr_qrtr))+1;
334 ld_installment_date := last_day(add_months(ld_register_date,ln_rem_months_to_curr_qrtr+3));
331 ELSIF (rec_cur_term_details.day_of_claim = 'LAST_DAY')
332 THEN
333 --ln_rem_months_to_curr_qrtr +3 = last month of next quarter
335 END IF; --(rec_cur_term_details.day_of_claim = 'FIRST_DAY')
336
337 ELSIF (rec_cur_term_details.claim_interval = 'NEXT_FIN_YEAR')
338 THEN
339 --If the register day in the first quarter , the fiscal_year= current year -1,
340 --else fiscal_year= current year
341
342 --get the quarter number
343 ln_current_qrtr:= TO_NUMBER(TO_CHAR(ld_register_date,'Q'));
344
345 --calc the fiscal year
346 IF (ln_current_qrtr > 1)
347 THEN
348 ln_current_fiscal_year := EXTRACT(YEAR FROM ld_register_date);
349 ELSE
350 ln_current_fiscal_year := EXTRACT(YEAR FROM ld_register_date) -1;
351 END IF;
352
353 --calc the next fiscal year
354 ln_next_fiscal_year := ln_current_fiscal_year+1;
355
356 --first day of fiscal year = "APR-01-"|| fiscal_year ,
357 --last day of fiscal year = "MAR-31-"|| (fiscal_year+1)
358 --same day of fiscal year
359 --1) same day exist in the next fiscal year
360 --2) same day does not exist in the next fiscal year
361
362 IF (rec_cur_term_details.day_of_claim = 'FIRST_DAY')
363 THEN
364 ld_installment_date := TO_DATE(lv_FIRST_DAY_OF_FISCAL_YEAR || TO_CHAR(ln_next_fiscal_year) ,'DD-MM-YYYY');
365 ELSIF (rec_cur_term_details.day_of_claim = 'LAST_DAY')
366 THEN
367 ld_installment_date := TO_DATE(lv_LAST_DAY_OF_FISCAL_YEAR || TO_CHAR(ln_next_fiscal_year+1),'DD-MM-YYYY');
368 ELSIF (rec_cur_term_details.day_of_claim = 'SAME_DAY')
369 THEN
370 --get the same day of next month
371 ld_installment_date := ADD_MONTHS(ld_register_date,12);
372
373 --If the ld_register_date is the last day of the month,
374 --add_months(,12)will return the last day of next year. e.g Feb-28 , next_year_same day = Feb-29 (year is leap year)
375 --To avoid this bug, add the below logic comparing the current day of month
376 --with the max days of next year,
377 IF EXTRACT(DAY FROM ld_register_date)< EXTRACT(DAY FROM ADD_MONTHS(ld_register_date,12))
378 THEN
379 ld_installment_date := TO_DATE( EXTRACT(DAY FROM ld_register_date )||'-'||
380 EXTRACT(MONTH FROM ld_installment_date)||'-'||
381 EXTRACT(YEAR FROM ld_installment_date)
382 , 'DD-MM-YYYY'
383 );
384 END IF;
385
386 ELSIF (rec_cur_term_details.day_of_claim = 'OTHER_DAY')
387 THEN
388 --check the specify day is in the first quarter or not
389 --if it is in the fist quarter then next calendar year :=next fiscal year+1
390 --else next calendar year :=next fiscal year
391 IF rec_cur_term_details.month_of_claim = 'OTH'
392 THEN
393 ln_specify_qrtr := to_number(to_char(ld_register_date,'Q'));
394 ELSE
395 ln_specify_qrtr := to_number(to_char(to_date(rec_cur_term_details.month_of_claim,'MM'),'Q'));
396 END IF;
397
398 -- calc the next fiscal calendar year
399 -- If the specific month is in the first quarter,
400 -- the calendar year of next financial year is equal to ln_next_fiscal_year +1
401 -- in this situation we calculate the calendar year of next fiscal year
402 IF (ln_specify_qrtr = 1)
403 THEN
404 ln_next_fiscal_year := ln_next_fiscal_year+1;
405 END IF;
406
407 -- Get the speicify month
408 IF rec_cur_term_details.month_of_claim = 'OTH'
409 THEN
410 lv_month_of_claim := to_char(ld_register_date,'MON');
411 ELSE
412 lv_month_of_claim := rec_cur_term_details.month_of_claim;
413 END IF;
414
415 -- If the sepcific the day of month is greater than the last day of sepcific month
416 -- get the last day of sepcific month in the next fiscal year
417 IF (EXTRACT(DAY FROM last_day(to_date(lv_month_of_claim||'-'||ln_next_fiscal_year,'MM-YYYY')))
418 < rec_cur_term_details.day_of_month)
419 THEN
420
421 ld_installment_date := last_day(to_date(lv_month_of_claim||'-'||ln_next_fiscal_year,'MM-YYYY'));
422 ELSE
423
424 ld_installment_date := TO_DATE( rec_cur_term_details.day_of_month||'-'||
425 EXTRACT(MONTH FROM to_date(lv_month_of_claim,'MM'))||'-'||
426 ln_next_fiscal_year
427 , 'DD-MM-YYYY'
428 );
429 END IF;
430 END IF;
431
432 --If the current month< specified month , the fiscal year= current year -1
433 --if the specified month > =current month, so specified month is in current year . Add month directly
434 --if the specified month < current month , so specified month is in the next year .
435
436
437
438 ELSIF (rec_cur_term_details.claim_interval = 'SP_MONTH')
439 THEN
440 -- Get the speicify month
441 IF (rec_cur_term_details.month_of_claim = 'OTH')
442 THEN
443 lv_month_of_claim := to_char(ld_register_date,'MON');
444 ELSE
445 lv_month_of_claim := rec_cur_term_details.month_of_claim;
449 ln_month_number := EXTRACT(MONTH FROM to_date(lv_month_of_claim,'MM')) -ln_current_month ;
446 END IF;-- IF (rec_cur_term_details.month_of_claim = 'OTH')
447
448 ln_current_month:= EXTRACT(MONTH FROM ld_register_date);
450
451 --cal the year of installment_date for the specified month
452
453 --if the specified month > current month, so specified month should be in current year . Add month directly
454 --if the specified month < current month, so specified month should be in the next year .
455 --if the specified month = current month,
456 --for LAST_DAY AND SAME_DAY , the specified month is in the current year.
457 --for FIRST_DAY AND OTHER_DAY, compare the register day and specified day
458 --IF the current day is greater than the specified day, so specified month should be in the next year
459 --ELSE specified month is in the current year.
460
461 IF (ln_month_number >0)
462 THEN
463 ld_installment_date := add_months(ld_register_date,ln_month_number);
464 ELSIF (ln_month_number <0)
465 THEN
466 ld_installment_date := add_months(ld_register_date,ln_month_number+12);
467 ELSIF (ln_month_number =0)
468 THEN
469 IF ( rec_cur_term_details.day_of_claim ='LAST_DAY'
470 OR rec_cur_term_details.day_of_claim ='SAME_DAY'
471 )
472 THEN
473 ld_installment_date := ld_register_date;
474 END IF;--( rec_cur_term_details.day_of_claim ='LAST_DAY')
475
476 IF ( rec_cur_term_details.day_of_claim ='FIRST_DAY' )
477 THEN
478 --IF ld_register_date IS NOT THE FIRST DAY,so specified month should be in the next year .
479 --ELSE specified month is in the same year .
480 IF (EXTRACT(DAY FROM ld_register_date)>1)
481 THEN
482 ld_installment_date := add_months(ld_register_date,ln_month_number+12);
483 ELSE
484 ld_installment_date := ld_register_date;
485 END IF;--(EXTRACT(DAY FROM ld_register_date)>1)
486 END IF;--( rec_cur_term_details.day_of_claim ='FIRST_DAY' )
487
488 IF ( rec_cur_term_details.day_of_claim ='OTHER_DAY' )
489 THEN
490 --IF ld_register_date is greater than the specified day(the specified day is passed),so specified month should be in the next year .
491 --ELSE specified month is in the same year .
492 IF (EXTRACT(DAY FROM ld_register_date)>rec_cur_term_details.day_of_month)
493 THEN
494 ld_installment_date := add_months(ld_register_date,ln_month_number+12);
495 ELSE
496 ld_installment_date := ld_register_date;
497 END IF;--(EXTRACT(DAY FROM ld_register_date)>rec_cur_term_details.day_of_month)
498 END IF; --( rec_cur_term_details.day_of_claim ='OTHER_DAY' )
499 END IF;--(ln_month_number >0)
500
501 --cal the day of installment_date for the specified month
502 IF (rec_cur_term_details.day_of_claim = 'SAME_DAY')
503 THEN
504 --If the ld_register_date >= last day of specified month, use add_month() to calc installment day
505 --logic is already handled when calculating the year of installment_date
506
507 --If the ld_register_date < last day of specified month ,use the register day as installment day
508 IF (EXTRACT(DAY FROM ld_register_date)< EXTRACT(DAY FROM last_day(ld_installment_date)))
509 THEN
510 ld_installment_date := TO_DATE( EXTRACT(DAY FROM ld_register_date )||'-'||
511 EXTRACT(MONTH FROM ld_installment_date)||'-'||
512 EXTRACT(YEAR FROM ld_installment_date)
513 , 'DD-MM-YYYY'
514 );
515 END IF;--(EXTRACT(DAY FROM ld_register_date)< EXTRACT(DAY FROM last_day(ld_installment_date)))
516
517 ELSIF rec_cur_term_details.day_of_claim = 'FIRST_DAY'
518 THEN
519 --first_day of specified month = the last day of last month +1
520 --ld_installment_date is a day in the specified month and year
521 --add_month(,-1) get the last month
522 ld_installment_date := last_day(add_months(ld_installment_date,-1))+1;
523 ELSIF (rec_cur_term_details.day_of_claim = 'LAST_DAY')
524 THEN
525 -- last_day of specified month = last_day(specified_month)
526 --ld_installment_date is a day in the specified month and year
527 ld_installment_date := last_day(ld_installment_date);
528 ELSIF (rec_cur_term_details.day_of_claim = 'OTHER_DAY')
529 THEN
530 -- there are 2 cases:
531 -- 1)specified other day >= last day of specified month , use the last day of next month
532 -- 2)specified other day < last day of specified month , use the specified day of next month
533
534
535 --Get the last_day of specified month
536 --Handle case 1:
537 --IF the specified day >= last_day of specified month,use the last_day of next month
538 ld_installment_date := last_day(ld_installment_date);
539
540
541 --Handle case 2:
542 --If the specified day < max days of specified month , use the specified day
543 --override the variable ld_installment_date
544 IF(rec_cur_term_details.day_of_month < EXTRACT(DAY FROM ld_installment_date))
545 THEN
546 ld_installment_date := TO_DATE( rec_cur_term_details.day_of_month ||'-'||
547 EXTRACT(MONTH FROM ld_installment_date)||'-'||
548 EXTRACT(YEAR FROM ld_installment_date)
549 , 'DD-MM-YYYY'
550 );
554
551 END IF; --(rec_cur_term_details.day_of_month < EXTRACT(DAY FROM ld_installment_date))
552 END IF; --(rec_cur_term_details.day_of_claim = 'FIRST_DAY')
553 END IF;--(rec_cur_term_details.claim_interval = 'IMMEDIATE')
555 ld_register_date := ld_installment_date;
556
557
558 /* Insert into the temp table*/
559 insert_jai_rgm_trm_schedules_t( p_schedule_id
560 , ln_installment_number
561 , ln_installment_amount
562 , ld_installment_date
563 );
564 END LOOP;--rec_cur_term_details IN cur_term_details(p_term_id)
565 -----------------------------------------------------------------------------
566 --added by eric ma for VAT FLEX RECOVERY (Bug9494633) on Mar-09-2010,End
567
568
569 p_process_flag := jai_constants.successful ;
570 return ;
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 p_process_flag := jai_constants.unexpected_error ;
575 p_process_msg := 'Error Occured in jai_rgm_claims_pkg.prepare_term_schedules - ' || substr(sqlerrm,1,900) ;
576
577 END generate_term_schedules ;
578
579
580 /**************************************************
581 || Get the term defined for the assignment and
582 || if not defined return the default term
583 || If default term does not exist return Error
584 ***************************************************/
585
586 PROCEDURE get_term_id
587 (
588 p_regime_id IN JAI_RGM_TERM_ASSIGNS.REGIME_ID %TYPE ,
589 p_item_id IN NUMBER ,
590 p_organization_id IN JAI_RGM_TERM_ASSIGNS.ORGANIZATION_ID %TYPE ,
591 p_party_type IN JAI_RGM_TERM_ASSIGNS.ORGANIZATION_TYPE %TYPE ,
592 p_location_id IN JAI_RGM_TERM_ASSIGNS.LOCATION_ID %TYPE ,
593 p_term_id OUT NOCOPY JAI_RGM_TERM_ASSIGNS.TERM_ID %TYPE ,
594 p_process_flag OUT NOCOPY VARCHAR2 ,
595 p_process_msg OUT NOCOPY VARCHAR2
596 )
597 IS
598
599 CURSOR cur_get_regime_regno
600 ( p_regime_id NUMBER ,
601 p_org_id NUMBER ,
602 p_loc_id NUMBER ,
603 p_att_code jai_rgm_registrations.attribute_code%TYPE , --rchandan for bug#4428980
604 p_att_type_code jai_rgm_registrations.attribute_type_code%TYPE--rchandan for bug#4428980
605 )
606 IS
607 SELECT attribute_value
608 FROM JAI_RGM_ORG_REGNS_V jorrv
609 WHERE jorrv.regime_id = p_regime_id
610 AND jorrv.attribute_code = p_att_code
611 AND jorrv.attribute_type_code = p_att_type_code
612 AND jorrv.organization_id = p_org_id
613 AND jorrv.organization_type = p_party_type
614 AND jorrv.location_id = nvl ( p_loc_id , jorrv.location_id ) ;
615
616 CURSOR cur_get_regime_code ( p_regime_id NUMBER )
617 IS
618 SELECT regime_code
619 FROM JAI_RGM_DEFINITIONS
620 WHERE regime_id = p_regime_id ;
621
622 CURSOR cur_term_id
623 (
624 p_reg_id NUMBER ,
625 p_reg_item_class VARCHAR2 ,
626 p_reg_regno VARCHAR2 ,
627 p_org_id NUMBER ,
628 p_party_type VARCHAR2 ,
629 p_loc_id NUMBER
630 )
631 IS
632 SELECT term_id
633 FROM JAI_RGM_TERM_ASSIGNS
634 WHERE NVL ( location_id , p_loc_id ) = p_loc_id
635 AND NVL ( organization_type , p_party_type ) = p_party_type
636 AND NVL ( organization_id , p_org_id ) = p_org_id
637 AND NVL ( regime_regno , p_reg_regno ) = p_reg_regno
638 AND NVL ( regime_item_class , p_reg_item_class ) = p_reg_item_class
639 AND regime_id = p_reg_id ;
640
641
642 lv_reg_item_class JAI_RGM_TERM_ASSIGNS.REGIME_ITEM_CLASS%TYPE ;
643 lv_regime_regno JAI_RGM_TERM_ASSIGNS.REGIME_REGNO %TYPE ;
644 lv_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE %TYPE ;
645 lv_process_flag VARCHAR2(3) ;
646 lv_process_msg VARCHAR2(1000);
647
648 BEGIN
649
650 p_term_id := NULL ;
651 lv_reg_item_class := NULL ;
652 lv_regime_code := NULL ;
653 lv_process_flag := NULL ;
654 lv_process_msg := NULL ;
655
656 /*
657 || Validate the input parameters
658 */
659
660 IF p_regime_id IS NULL THEN
661
662 p_process_flag := jai_constants.expected_error;
663 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Regime ID cannot be NULL' ;
664 return;
665
666 END IF;
667
668 IF p_item_id IS NULL THEN
669
670 p_process_flag := jai_constants.expected_error;
671 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Item ID cannot be NULL' ;
672 return;
673
674 END IF ;
675
676 IF p_organization_id IS NULL THEN
677
678 p_process_flag := jai_constants.expected_error;
679 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Organization ID cannot be NULL' ;
680 return;
681
682 END IF ;
683
684 IF p_party_type IS NULL THEN
685
686 p_process_flag := jai_constants.expected_error;
690 END IF ;
687 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Party Type cannot be NULL' ;
688 return;
689
691
692
693 IF p_location_id IS NULL THEN
694
695 p_process_flag := jai_constants.expected_error;
696 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => Location ID cannot be NULL' ;
697 return;
698
699 END IF ;
700
701 OPEN cur_get_regime_regno
702 (
703 p_regime_id => p_regime_id ,
704 p_org_id => p_organization_id ,
705 p_loc_id => p_location_id,
706 p_att_code => 'REGISTRATION_NO', --rchandan for bug#4428980
707 p_att_type_code => 'PRIMARY' --rchandan for bug#4428980
708 ) ;
709 FETCH cur_get_regime_regno INTO lv_regime_regno ;
710 CLOSE cur_get_regime_regno ;
711
712
713 OPEN cur_get_regime_code ( p_regime_id ) ;
714 FETCH cur_get_regime_code INTO lv_regime_code ;
715 CLOSE cur_get_regime_code ;
716
717 jai_inv_items_pkg.jai_get_attrib
718 (
719 p_regime_code => lv_regime_code ,
720 p_organization_id => p_organization_id ,
721 p_inventory_item_id => p_item_id ,
722 p_attribute_code => 'ITEM CLASS' ,
723 p_attribute_value => lv_reg_item_class ,
724 p_process_flag => lv_process_flag ,
725 p_process_msg => lv_process_msg
726 );
727
728 IF ( lv_reg_item_class IS NULL ) OR ( lv_process_flag <> jai_constants.successful ) THEN
729
730 p_process_flag := jai_constants.expected_error;
731 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => jai_inv_items_pkg.jai_get_attrib => p_process_flag :: lv_process_msg' ;
732 return;
733
734 END IF ;
735
736
737 OPEN cur_term_id ( p_reg_id => p_regime_id ,
738 p_reg_item_class => lv_reg_item_class ,
739 p_reg_regno => lv_regime_regno ,
740 p_org_id => p_organization_id ,
741 p_party_type => p_party_type ,
742 p_loc_id => p_location_id
743 ) ;
744 FETCH cur_term_id INTO p_term_id ;
745 CLOSE cur_term_id ;
746
747 IF p_term_id IS NULL THEN
748
749 p_process_flag := jai_constants.expected_error ;
750 p_process_msg := 'jai_rgm_claims_pkg.get_term_id => No term defined' ;
751 RETURN ;
752
753 END IF ;
754
755 p_process_flag := jai_constants.successful ;
756 p_process_msg := NULL;
757
758 EXCEPTION
759 WHEN others THEN
760
761 p_process_flag := jai_constants.unexpected_error;
762 p_process_msg := 'JAI_CMN_RG_OTHERS.get_term_id => Error Occured : ' || substr(sqlerrm,1,1000) ;
763
764 END get_term_id ;
765
766 PROCEDURE set_term_in_use
767 (
768 p_term_id IN JAI_RGM_TERMS.TERM_ID%TYPE ,
769 p_process_flag OUT NOCOPY VARCHAR2 ,
770 p_process_msg OUT NOCOPY VARCHAR2
771 )
772 IS
773
774 CURSOR cur_term_flag ( cp_term_id NUMBER )
775 IS
776 SELECT term_in_use_flag
777 FROM jai_rgm_terms
778 WHERE term_id = p_term_id ;
779
780
781 lv_term_in_use_flag VARCHAR2(1) ;
782
783 BEGIN
784
785 /*
786 || Validate the input parameters
787 */
788
789 IF p_term_id IS NULL THEN
790
791 p_process_flag := jai_constants.expected_error;
792 p_process_msg := 'jai_rgm_claims_pkg.set_term_in_use_flag => Term ID cannot be NULL' ;
793 return;
794
795 END IF;
796
797 OPEN cur_term_flag ( cp_term_id => p_term_id );
798 FETCH cur_term_flag INTO lv_term_in_use_flag ;
799 IF NOT ( cur_term_flag%FOUND ) THEN
800
801 p_process_flag := jai_constants.expected_error;
802 p_process_msg := 'jai_rgm_claims_pkg.set_term_in_use_flag => Term ID does not exist in database' ;
803 CLOSE cur_term_flag ;
804 return;
805
806 END IF;
807 CLOSE cur_term_flag ;
808
809 IF lv_term_in_use_flag <> 'Y' THEN
810
811 UPDATE jai_rgm_terms
812 SET term_in_use_flag = 'Y' ,
813 last_update_date = sysdate ,
814 last_update_login = fnd_global.login_id ,
815 last_updated_by = fnd_global.user_id
816 WHERE term_id = p_term_id ;
817
818 END IF ;
819
820 p_process_flag := jai_constants.successful ;
821 p_process_msg := NULL;
822
823 EXCEPTION
824 WHEN others THEN
825
826 p_process_flag := jai_constants.unexpected_error;
827 p_process_msg := 'JAI_CMN_RG_OTHERS.set_term_in_use_flag => Error Occured : ' || substr(sqlerrm,1,1000) ;
828
829 END set_term_in_use ;
830
831 END jai_cmn_rgm_terms_pkg ;
[Home] [Help]