[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS_UPDC
Source
1 PACKAGE BODY IGI_CIS_UPDC AS
2 /* $Header: igicisdb.pls 115.15 2003/12/17 13:36:28 hkaniven noship $ */
3
4
5 p_effective_date DATE;
6
7
8 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 l_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
10 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
12 l_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 l_error_level NUMBER := FND_LOG.LEVEL_ERROR;
14 l_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 l_path VARCHAR2(50) := 'IGI.PLSQL.igicisdb.IGI_CIS_UPDC.';
16
17
18 /*PROCEDURE WriteLog ( pp_mesg in varchar2 , pp_debug_mode in boolean := FALSE ) IS
19 BEGIN
20 IF pp_debug_mode THEN
21 Fnd_file.put_line(fnd_file.log,'============================================================' );
22 Fnd_file.put_line( fnd_file.log , pp_mesg ) ;
23 ELSE
24 Null;
25 END IF;
26 END; */
27
28
29 PROCEDURE Debug ( p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2 ) is
30 BEGIN
31 IF (p_level >= l_debug_level ) THEN
32 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
33 END IF;
34 END ;
35
36
37 PROCEDURE Validate_dates (p_cert_start_date IN DATE
38 ,p_cert_end_date IN DATE
39 ,p_effective_date IN DATE
40 ,p_cur_start_date OUT NOCOPY DATE
41 ,p_cur_end_date OUT NOCOPY DATE
42 ,p_new_start_date OUT NOCOPY DATE
43 ,p_new_end_date OUT NOCOPY DATE
44 ,p_special_case OUT NOCOPY BOOLEAN) IS
45 l_message varchar2(100);
46 BEGIN
47 p_special_case := FALSE;
48
49
50 /* Effective date between start and end dates */
51
52 IF p_cert_start_date < p_effective_date AND
53 (p_cert_end_date >= p_effective_date OR p_cert_end_date IS NULL)
54 THEN
55
56 p_cur_start_date := p_cert_start_date;
57 p_cur_end_date := p_effective_date - 1;
58 p_new_start_date := p_effective_date;
59 p_new_end_date := p_cert_end_date;
60 END IF;
61
62 /* Effective date equal to start date and both are not null */
63
64 IF p_cert_end_date IS NOT NULL AND
65 p_cert_start_date = p_effective_date AND
66 p_cert_end_date > p_effective_date
67 THEN
68 p_cur_start_date := p_effective_date;
69 p_cur_end_date := p_cert_end_date;
70 p_new_start_date := null ; --p_effective_date ;
71 p_new_end_date := null ; --p_cert_end_date;
72
73 END IF;
74
75
76 /* Effective date equal to start date equal to end date */
77
78
79 IF p_cert_start_date IS NOT NULL AND
80 p_cert_end_date IS NOT NULL AND
81 p_cert_start_date = p_effective_date AND
82 p_cert_end_date = p_effective_date
83 THEN
84 p_special_case := TRUE;
85
86 END IF;
87
88
89 END validate_dates;
90
91 PROCEDURE GENERATE_TAX_RATE_ID (p_tax_rate_id OUT NOCOPY NUMBER) IS
92 CURSOR c_gen_id IS
93 SELECT ap_awt_tax_rates_s.NEXTVAL FROM SYS.DUAL;
94 INVALIDSEQ Exception;
95 l_message varchar2(100) ;
96 BEGIN
97 OPEN c_gen_id;
98 FETCH c_gen_id INTO p_tax_rate_id;
99 IF c_gen_id%NOTFOUND THEN
100 Raise INVALIDSEQ;
101 END IF;
102 CLOSE c_gen_id;
103 EXCEPTION
104 WHEN INVALIDSEQ THEN
105 Fnd_message.set_name('IGI','IGI_CIS_MISSING_SEQ');
106 l_message:=fnd_message.get;
107 Debug(l_excep_level, 'GENERATE_TAX_RATE_ID', l_message);
108
109 END GENERATE_TAX_RATE_ID;
110
111 PROCEDURE upd_cis_cert_type_perc ( Retcode OUT NOCOPY NUMBER ,
112 Errbuf OUT NOCOPY VARCHAR2 ,
113 P_mode VARCHAR2,
114 P_current_certificate_type VARCHAR2,
115 P_effective_date1 VARCHAR2,
116 P_new_percentage NUMBER := NULL,
117 P_new_certificate_type VARCHAR2 := NULL
118
119 ) IS
120
121
122 /*cursor to select awt data for updation*/
123
124 CURSOR c_certificate IS
125 SELECT rowid ,
126 tax_rate_id ,
127 tax_name,
128 tax_rate,
129 rate_type,
130 start_date,
131 end_date,
132 start_amount,
133 end_amount,
134 last_update_date,
135 last_updated_by,
136 last_update_login,
137 creation_date,
138 created_by,
139 vendor_id,
140 vendor_site_id,
141 invoice_num,
142 certificate_number,
143 certificate_type,
144 comments,
145 priority,
146 attribute_category,
147 attribute1,
148 attribute2,
149 attribute3,
150 attribute4,
151 attribute5,
152 attribute6,
153 attribute7,
154 attribute8,
155 attribute9,
156 attribute10,
157 attribute11,
158 attribute12,
159 attribute13,
160 attribute14,
161 attribute15,
162 org_id
163 FROM ap_awt_tax_rates
164 WHERE tax_name = IGI_CIS_GET_PROFILE.Cis_Tax_Code
165 AND certificate_type = p_current_certificate_type ;
166
167 CURSOR c1 ( c_tax_rate_id number )IS
168 SELECT tax_rate_id , ni_number
169 FROM igi_cis_cert_ni_numbers
170 WHERE TAX_RATE_ID = c_tax_rate_id ;
171
172
173 CURSOR c2 (c_start_date date, c_end_date date , c_vendor_id number , c_tax_rate_id number ) is
174 SELECT 'X' from ap_awt_tax_rates
175 WHERE tax_name = IGI_CIS_GET_PROFILE.Cis_Tax_Code
176 AND ((certificate_type = decode(p_mode , 'Percentages' , p_current_certificate_type , p_new_certificate_type)) OR (certificate_type = p_current_certificate_type))
177 AND vendor_id = c_vendor_id
178 AND tax_rate_id <> c_tax_rate_id
179 AND ( ( start_date <= c_start_date AND end_date >= c_end_date) OR
180 (start_date >= c_start_date AND end_date <= c_end_date) OR
181 (c_start_date BETWEEN start_date AND end_date) OR
182 (c_end_date BETWEEN start_date AND end_date) ) ;
183
184 CURSOR C3 (c_vendor_id number )IS
185 SELECT vendor_name FROM po_vendors
186 WHERE vendor_id = c_vendor_id ;
187
188 CURSOR C4(c_vendor_site_id number , c_org_id number , c_vendor_id number) IS
189 SELECT vendor_site_code FROM po_vendor_sites
190 WHERE vendor_site_id = c_vendor_site_id
191 AND org_id = c_org_id
192 AND vendor_id = c_vendor_id;
193
194 l3 c3%rowtype;
195 l4 c4%rowtype;
196 l_message varchar2(1000);
197 l_row_id varchar2 (30);
198 l_tax_rate_id number;
199 l1 c1%rowtype;
200 l2 c2%rowtype;
201 l_certificate c_certificate%rowtype;
202 l_curr_start_date date;
203 l_curr_end_date date;
204 l_new_start_date date;
205 l_new_end_date date;
206 l_special_case boolean := FALSE;
207 l_vendor_site_code varchar2(15);
208 l_vendor_name po_vendors.vendor_name%TYPE;
209 --l_vendor_name varchar2(240) ;UTF changes Bug No. 2524214
210 l_tax_rate number ;
211 l_certificate_type varchar2(25);
212 l_vendor_site varchar2(100) ;
213 l_call_seq varchar2(100) ;
214
215 /* Bug 3085887 rgopalan 7-AUG-2003 */
216 /* This needs to be removed for 11ix MOAC */
217 l_org_id number := FND_PROFILE.VALUE('ORG_ID');
218
219 l_changed boolean := FALSE ;
220
221 UNPROCESSED Exception ;
222 INEXISTENT Exception ;
223 INVALID Exception;
224
225 BEGIN
226
227 p_effective_date:=to_date(p_effective_date1,'YYYY/MM/DD HH24:MI:SS');
228
229 /*Checking the modes and corresponding parameters*/
230
231
232
233 IF p_mode = 'Percentages' THEN
234 IF (p_new_percentage IS NULL ) THEN
235 Raise INVALID;
236
237 END IF;
238
239 IF (p_new_certificate_type IS NOT NULL ) THEN
240
241 Fnd_message.set_name('IGI','IGI_CIS_VALUES_NOT_MATCH');
242 Fnd_message.set_token('mode', p_mode);
243 Fnd_message.set_token('other_mode', 'Certificate Type' );
244 l_message:=Fnd_message.get;
245 Debug(l_state_level, 'upd_cis_cert_type_perc.msg1', l_message);
246 END IF;
247
248 ELSE /*else for p_mode*/
249 IF p_new_certificate_type IS NULL THEN
250 Raise INVALID;
251 END IF;
252 IF (p_new_percentage IS NOT NULL ) THEN
253
254 Fnd_message.set_name('IGI','IGI_CIS_VALUES_NOT_MATCH');
255 Fnd_message.set_token('mode', p_mode);
256 Fnd_message.set_token('other_mode', 'Percentage' );
257 l_message:=Fnd_message.get;
258 Debug(l_state_level, 'upd_cis_cert_type_perc.msg2', l_message);
259 END IF;
260 END IF;
261
262
263 /*Finish checking the modes and corresponding parameters*/
264
265
266 OPEN c_certificate;
267 FETCH c_certificate INTO l_certificate;
268 IF c_certificate%NOTFOUND THEN
269 Raise INEXISTENT;
270 END IF;
271 CLOSE c_certificate;
272 /*Start Processing*/
273 /*Loop for all the values selected*/
274
275 FOR l_certificate IN c_certificate
276
277 LOOP ------------------------------------------ For Loop----- Starts-
278
279 BEGIN
280
281
282
283
284 IF ( ( (l_certificate.end_date >=p_effective_date ) OR (l_certificate.end_date IS NULL )) AND l_certificate.start_date <= p_effective_date) THEN
285
286 validate_dates (l_certificate.start_date
287 ,l_certificate.end_date
288 ,p_effective_date
289 ,l_curr_start_date
290 ,l_curr_end_date
291 ,l_new_start_date
292 ,l_new_end_date
293 ,l_special_case);
294 END IF;
295
296
297
298 /* Update /Insert in case the date does not fall under the special case> */
299
300 IF NOT (l_special_case) THEN ------------------------------------------------------------(1)
301
302
303 OPEN c3 (l_certificate.vendor_id);
304 FETCH c3 INTO l3;
305 CLOSE c3;
306 l_vendor_name := l3.vendor_name ;
307
308
309 OPEN c4 (l_certificate.vendor_site_id ,l_certificate.org_id , l_certificate.vendor_id );
310 FETCH c4 INTO l4;
311 CLOSE c4;
312 l_vendor_site_code := l4.vendor_site_code ;
313
314
315
316 OPEN c2(l_certificate.start_date,l_certificate.end_date ,l_certificate.vendor_id , l_certificate.tax_rate_id);
317 FETCH c2 INTO l2;
318
319
320
321 IF c2%NOTFOUND THEN -------------------------------------------------------------(2)
322
323
324 /* The Certificate does not have overlapaping dates*/
325
326
327 IF p_mode = 'Percentages' THEN
328 l_tax_rate := p_new_percentage;
329 l_certificate_type := l_certificate.certificate_type;
330 ELSE
331 l_tax_rate:=L_certificate.tax_rate;
332 l_certificate_type:=p_new_certificate_type;
333 END IF;
334
335 /* End of mode analysis*/
336 /* To update old record and insert in case of breaking an existing period */
337
338 IF ( ((l_certificate.end_date >= p_effective_date) OR (l_certificate.end_date IS NULL )) and l_certificate.start_date < p_effective_date) THEN ---------(3)
339
340 Generate_tax_rate_id(l_tax_rate_id);
341
342
343 UPDATE ap_awt_tax_rates
344 SET end_date = l_curr_end_date
345 WHERE rowid = l_certificate.rowid;
346
347 Fnd_message.set_name('IGI','IGI_CIS_UPDATED_CERT');
348 Fnd_message.set_token('vendor_name',l_vendor_name);
349 Fnd_message.set_token('vendor_site',l_vendor_site_code);
350 Fnd_message.set_token('tax_group', IGI_CIS_GET_PROFILE. Cis_Tax_group);
351 Fnd_message.set_token('tax_name',l_certificate.tax_name);
352 Fnd_message.set_token('cert_type',l_certificate.certificate_type);
353 Fnd_message.set_token('cert_number',l_certificate.certificate_number);
354 Fnd_message.set_token ('tax_rate',l_certificate.tax_rate ) ;
355 Fnd_message.set_token('eff_from_date',l_certificate.start_date);
356 Fnd_message.set_token('eff_to_date',l_curr_end_date);
357 l_message:=Fnd_message.get;
358 Debug(l_state_level, 'upd_cis_cert_type_perc', l_message);
359
360 IF ((p_new_certificate_type = 'CIS4P' OR p_new_certificate_type = 'CIS6') and (p_mode = 'Types')) THEN ---------------------(61)
361
362 Fnd_message.set_name( 'IGI','IGI_CIS_NI_NUMBER_REQ') ;
363 l_message:=Fnd_message.get;
364 Debug(l_state_level, 'upd_cis_cert_type_perc', l_message);
365 END IF; -------------------------------------(61)
366
367
368
369 INSERT INTO ap_awt_tax_rates
370 (tax_rate_id,
371 tax_name,
372 tax_rate,
373 rate_type,
374 start_date,
375 end_date,
376 start_amount,
377 end_amount,
378 last_update_date,
379 last_updated_by,
380 last_update_login,
381 creation_date,
382 created_by,
383 vendor_id,
384 vendor_site_id,
385 invoice_num,
386 certificate_number,
387 certificate_type,
388 comments,
389 priority,
390 attribute_category,
391 attribute1,
392 attribute2,
393 attribute3,
394 attribute4,
395 attribute5,
396 attribute6,
397 attribute7,
398 attribute8,
399 attribute9,
400 attribute10,
401 attribute11,
402 attribute12,
403 attribute13,
404 attribute14,
405 attribute15,
406 org_id)
407 VALUES
408 (l_tax_rate_id,
409 l_certificate.tax_name,
410 l_tax_rate,
411 l_certificate.rate_type,
412 l_new_start_date,
413 l_new_end_date,
414 l_certificate.start_amount,
415 l_certificate.end_amount,
416 sysdate,
417 fnd_global.user_id,
418 fnd_global.login_id,
419 sysdate ,
420 fnd_global.user_id,
421 l_certificate.vendor_id,
422 l_certificate.vendor_site_id,
423 l_certificate.invoice_num,
424 l_certificate.certificate_number,
425 l_certificate_type,
426 l_certificate.comments,
427 l_certificate.priority,
428 l_certificate.attribute_category,
429 l_certificate.attribute1,
430 l_certificate.attribute2,
431 l_certificate.attribute3,
432 l_certificate.attribute4,
433 l_certificate.attribute5,
434 l_certificate.attribute6,
435 l_certificate.attribute7,
436 l_certificate.attribute8,
437 l_certificate.attribute9,
438 l_certificate.attribute10,
439 l_certificate.attribute11,
440 l_certificate.attribute12,
441 l_certificate.attribute13,
442 l_certificate.attribute14,
443 l_certificate.attribute15,
444 l_certificate.org_id);
445
446 Fnd_message.set_name('IGI','IGI_CIS_UPDATED_CERT');
447 Fnd_message.set_token('vendor_name',l_vendor_name);
448 Fnd_message.set_token('vendor_site',l_vendor_site_code);
449 Fnd_message.set_token('tax_group', IGI_CIS_GET_PROFILE.Cis_Tax_group);
450 Fnd_message.set_token('tax_name',l_certificate.tax_name);
451 Fnd_message.set_token('cert_type',l_certificate_type);
452 Fnd_message.set_token('cert_number' ,l_certificate.certificate_number);
453 Fnd_message.set_token('tax_rate' ,l_tax_rate);
454 Fnd_message.set_token('eff_from_date',l_new_start_date);
455 Fnd_message.set_token('eff_to_date',l_new_end_date);
456 L_message:=Fnd_message.get;
457 Debug(l_state_level, 'upd_cis_cert_type_perc.msg1', l_message);
458
459 /* Prepare a new record for the new percentage/new certificate type */
460
461 SELECT rowid into l_row_id FROM ap_awt_tax_rates WHERE tax_rate_id=l_tax_rate_id ;
462 /* Modified the cursor parameter value to take the old tax rate id for retrieving the ni number
463 instead of the current tax rate id Bug 2443964 solakshm*/
464 OPEN c1(l_certificate.tax_rate_id);
465 FETCH c1 INTO l1;
466 CLOSE c1;
467 l_call_seq := 'CIS :Update Certificate Percentages' ;
468
469 /* table handler for igi_cis_cert_ni_numbers_all */
470
471 igi_cis_cert_ni_numbers_pkg.insert_row
472 (l_row_id
473 ,l_org_id /* Bug 3085887 11.5.10 MOAC change */
474 ,l_tax_rate_id
475 ,l1.ni_number
476 ,sysdate
477 ,fnd_global.user_id
478 ,sysdate
479 ,fnd_global.user_id
480 ,fnd_global.login_id
481 ,l_call_seq );
482
483 END IF; ------------------------------------------------------------------------------------(3)
484
485
486 /* End of insert into ap_awt_tax_rates and igi_cis_ni_numbers_all*/
487
488 /* If the effective date does not break a period then update the certificate for future active certificates*/
489
490 IF l_certificate.start_date >= p_effective_date THEN ------------------------(4)
491
492
493 UPDATE ap_awt_tax_rates
494 SET tax_rate = l_tax_rate,
495 Certificate_type= l_certificate_type
496 WHERE rowid= l_certificate.rowid;
497
498
499
500
501 Fnd_message.set_name('IGI','IGI_CIS_UPDATED_CERT');
502
503 Fnd_message.set_token('vendor_name',l_vendor_name);
504 Fnd_message.set_token('vendor_site' ,l_vendor_site_code);
505 Fnd_message.set_token('tax_group', IGI_CIS_GET_PROFILE. Cis_Tax_group);
506 Fnd_message.set_token('tax_name',l_certificate.tax_name);
507 Fnd_message.set_token( 'cert_type',l_certificate_type);
508 Fnd_message.set_token( 'cert_number',l_certificate.certificate_number);
509 Fnd_message.set_token('tax_rate',l_tax_rate);
510 Fnd_message.set_token('eff_from_date',l_certificate.start_date);
511 Fnd_message.set_token('eff_to_date',l_certificate.end_date);
512 l_message := Fnd_message.get;
513 Debug(l_state_level, 'upd_cis_cert_type_perc.msg2', l_message);
514
515 IF ( (p_new_certificate_type = 'CIS4P' OR p_new_certificate_type = 'CIS6' ) and ( p_mode = 'Types' )) THEN ---------------------(62)
516 Fnd_message.set_name( 'IGI','IGI_CIS_NI_NUMBER_REQ') ;
517 L_message:=fnd_message.get;
518 Debug(l_state_level, 'upd_cis_cert_type_perc', l_message);
519 END IF; -------------------------------------(62)
520
521
522 END IF; --------------------------------------------------------------------------(4)
523
524 /* End of Update*/
525
526
527
528
529
530 ELSE /* Overlapping Certificates found*/ --------------------------------------------------------(2)
531
532 close c2;
533
534 RAISE UNPROCESSED;
535
536 END IF; ------------------------------------------------------------------------------------------(2)
537
538 /* End of c2%NOT FOUND*/
539
540
541 close c2;
542
543 ELSE ------------------------------------------------------------------------------------------------(1)
544
545 raise UNPROCESSED;
546
547
548 END IF; ----------------------------------------------------------------------------------------------(1)
549
550 /* not a special case*/
551
552 EXCEPTION
553
554 WHEN UNPROCESSED THEN
555 Fnd_message.set_name('IGI','IGI_CIS_REC_NOT_PROCESSED');
556 Fnd_message.set_token('vendor_name',l_vendor_name);
557 Fnd_message.set_token('vendor_site',l_vendor_site_code);
558 Fnd_message.set_token('tax_group', IGI_CIS_GET_PROFILE. Cis_Tax_group);
559 Fnd_message.set_token('tax_name',l_certificate.tax_name);
560 Fnd_message.set_token('cert_type',l_certificate.certificate_type);
561 Fnd_message.set_token('cert_number' ,l_certificate.certificate_number);
562 Fnd_message.set_token('tax_rate',l_certificate.tax_rate);
563 Fnd_message.set_token('eff_from_date', l_certificate.start_date);
564 fnd_message.set_token('eff_to_date',l_certificate.end_date);
565 L_message:=fnd_message.get;
566 Debug(l_excep_level, 'upd_cis_cert_type_perc', l_message);
567
568 end ;
569
570 End loop ;
571
572
573 EXCEPTION
574
575 WHEN INEXISTENT THEN
576 Fnd_message.set_name('IGI', 'IGI_CIS_REC_NOT_EXISTS');
577 L_message:=fnd_message.get;
578 Debug(l_excep_level, 'upd_cis_cert_type_perc', l_message);
579 WHEN INVALID THEN
580 Fnd_message.set_name('IGI','IGI_CIS_INVALID_ARGS');
581 L_message:=fnd_message.get;
582 Debug(l_excep_level, 'upd_cis_cert_type_perc', l_message);
583 END upd_cis_cert_type_perc ;
584
585 END IGI_CIS_UPDC;