1 PACKAGE BODY igs_pe_income_tax_pkg AS
2 /* $Header: IGSNI53B.pls 120.3 2005/10/17 04:23:31 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PE_INCOME_TAX_ALL%RowType;
6 new_references IGS_PE_INCOME_TAX_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_tax_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_tax_info IN VARCHAR2 DEFAULT NULL,
14 x_type_code IN VARCHAR2 DEFAULT NULL,
15 x_type_code_id IN NUMBER DEFAULT NULL,
16 x_start_date IN DATE DEFAULT NULL,
17 x_end_date IN DATE DEFAULT NULL,
18 X_ORG_ID in NUMBER DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL
24 ) AS
25
26 /*************************************************************
27 Created By :srachako
28 Date Created By :11-MAY-2000
29 Purpose :Set Column Values
30 Know limitations, enhancements or remarks
31 Change History
32 Who When What
33
34 (reverse chronological order - newest change first)
35 ***************************************************************/
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_PE_INCOME_TAX_ALL
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 Open cur_old_ref_values;
49 Fetch cur_old_ref_values INTO old_references;
50 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
51 Close cur_old_ref_values;
52 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
53 IGS_GE_MSG_STACK.ADD;
54 App_Exception.Raise_Exception;
55 Return;
56 END IF;
57 Close cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.tax_id := x_tax_id;
61 new_references.person_id := x_person_id;
62 new_references.tax_info := x_tax_info;
63 new_references.type_code := x_type_code;
64 new_references.type_code_id := x_type_code_id;
65 new_references.start_date := x_start_date;
66 new_references.end_date := x_end_date;
67 new_references.org_id := x_org_id;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78
79 END Set_Column_Values;
80
81 PROCEDURE Check_Constraints (
82 Column_Name IN VARCHAR2 DEFAULT NULL,
83 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
84 /*************************************************************
85 Created By :
86 Date Created By :
87 Purpose :
88 Know limitations, enhancements or remarks
89 Change History
90 Who When What
91
92 (reverse chronological order - newest change first)
93 ***************************************************************/
94
95 BEGIN
96
97 IF column_name IS NULL THEN
98 NULL;
99 NULL;
100 END IF;
101
102
103
104
105 END Check_Constraints;
106
107 PROCEDURE Check_Parent_Existance AS
108 /*************************************************************
109 Created By :srachako
110 Date Created By :11-MAY-2000
111 Purpose : Parent Existance Checking
112 Know limitations, enhancements or remarks
113 Change History
114 Who When What
115
116 (reverse chronological order - newest change first)
117 ***************************************************************/
118
119 BEGIN
120 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
121 'TAX_INFO',
122 new_references.TAX_INFO
123 ) THEN
124 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END IF;
128
129 IF (((old_references.person_id = new_references.person_id)) OR
130 ((new_references.person_id IS NULL))) THEN
131 NULL;
132 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
133 new_references.person_id
134 ) THEN
135 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139
140 END Check_Parent_Existance;
141
142 FUNCTION Get_PK_For_Validation (
143 x_tax_id IN NUMBER
144 ) RETURN BOOLEAN AS
145
146 /*************************************************************
147 Created By :srachako
148 Date Created By :11-MAY-2000
149 Purpose :Primary Key for Validation
150 Know limitations, enhancements or remarks
151 Change History
152 Who When What
153
154 (reverse chronological order - newest change first)
155 ***************************************************************/
156
157 CURSOR cur_rowid IS
158 SELECT rowid
159 FROM IGS_PE_INCOME_TAX_ALL
160 WHERE tax_id = x_tax_id
161 FOR UPDATE NOWAIT;
162
163 lv_rowid cur_rowid%RowType;
164
165 BEGIN
166
167 Open cur_rowid;
168 Fetch cur_rowid INTO lv_rowid;
169 IF (cur_rowid%FOUND) THEN
170 Close cur_rowid;
171 Return(TRUE);
172 ELSE
173 Close cur_rowid;
174 Return(FALSE);
175 END IF;
176 END Get_PK_For_Validation;
177
178 PROCEDURE Get_FK_Igs_Pe_Person (
179 x_person_id IN NUMBER
180 ) AS
181
182 /*************************************************************
183 Created By :srachako
184 Date Created By :11-MAY-2000
185 Purpose : Forign Key Check
186 Know limitations, enhancements or remarks
187 Change History
188 Who When What
189
190 (reverse chronological order - newest change first)
191 ***************************************************************/
192
193 CURSOR cur_rowid IS
194 SELECT rowid
195 FROM IGS_PE_INCOME_TAX_ALL
196 WHERE person_id = x_person_id ;
197
198 lv_rowid cur_rowid%RowType;
199
200 BEGIN
201 Open cur_rowid;
202 Fetch cur_rowid INTO lv_rowid;
203 IF (cur_rowid%FOUND) THEN
204 Close cur_rowid;
205 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PIT_PP_FK');
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception;
208 Return;
209 END IF;
210 Close cur_rowid;
211
212 END Get_FK_Igs_Pe_Person;
213
214
215 FUNCTION Get_UK_For_Validation (
216 x_person_id IN NUMBER,
217 x_type_code IN VARCHAR2,
218 x_type_code_id IN NUMBER
219 ) RETURN BOOLEAN AS
220
221 /*************************************************************
222 Created By :
223 Date Created By :
224 Purpose :
225 Know limitations, enhancements or remarks
226 Change History
227 Who When What
228
229 (reverse chronological order - newest change first)
230 ***************************************************************/
231
232 CURSOR cur_rowid(cp_type_code varchar2,cp_type_code_id number) IS
233 SELECT rowid
234 FROM igs_pe_income_tax_all
235 WHERE person_id = x_person_id
236 AND ((cp_type_code is null and cp_type_code_id is not null and NVL(type_code_id,-1) = cp_type_code_id)
237 OR (cp_type_code is not null and cp_type_code_id is null and NVL(type_code,' ') = cp_type_code))
238 AND ((l_rowid is null) or (rowid <> l_rowid))
239 ;
240
241 lv_rowid cur_rowid%RowType;
242
243 BEGIN
244
245 IF (new_references.tax_info = 'COUNTRY') THEN --Then type_code_id is null
246 Open cur_rowid(x_type_code,NULL);
247 ELSE
248 Open cur_rowid(NULL,x_type_code_id);
249 END IF;
250
251 Fetch cur_rowid INTO lv_rowid;
252 IF (cur_rowid%FOUND) THEN
253 Close cur_rowid;
254 return (true);
255 ELSE
256 close cur_rowid;
257 return(false);
258 END IF;
259 END Get_UK_For_Validation ;
260
261
262 PROCEDURE Check_Uniqueness AS
263 /*************************************************************
264 Created By :
265 Date Created By :
266 Purpose :
267 Know limitations, enhancements or remarks
268 Change History
269 Who When What
270
271 (reverse chronological order - newest change first)
272 ***************************************************************/
273
274 begin
275 IF Get_Uk_For_Validation (
276 new_references.person_id
277 ,new_references.type_code,new_references.type_code_id
278 ) THEN
279 Fnd_Message.Set_Name ('IGS', 'IGS_PE_INCTAX_DUP_EXISTS');
280 IGS_GE_MSG_STACK.ADD;
281 app_exception.raise_exception;
282 END IF;
283 END Check_Uniqueness ;
284
285
286 PROCEDURE Before_DML (
287 p_action IN VARCHAR2,
288 x_rowid IN VARCHAR2 DEFAULT NULL,
289 x_tax_id IN NUMBER DEFAULT NULL,
290 x_person_id IN NUMBER DEFAULT NULL,
291 x_tax_info IN VARCHAR2 DEFAULT NULL,
292 x_type_code IN VARCHAR2 DEFAULT NULL,
293 x_type_code_id IN NUMBER DEFAULT NULL,
294 x_start_date IN DATE DEFAULT NULL,
295 x_end_date IN DATE DEFAULT NULL,
296 X_ORG_ID in NUMBER DEFAULT NULL,
297 x_creation_date IN DATE DEFAULT NULL,
298 x_created_by IN NUMBER DEFAULT NULL,
299 x_last_update_date IN DATE DEFAULT NULL,
300 x_last_updated_by IN NUMBER DEFAULT NULL,
301 x_last_update_login IN NUMBER DEFAULT NULL
302 ) AS
303 /*************************************************************
304 Created By :
305 Date Created By :
306 Purpose :
307 Know limitations, enhancements or remarks
308 Change History
309 Who When What
310
311 (reverse chronological order - newest change first)
312 ***************************************************************/
313
314 BEGIN
315
316 Set_Column_Values (
317 p_action,
318 x_rowid,
319 x_tax_id,
320 x_person_id,
321 x_tax_info,
322 x_type_code,
323 x_type_code_id,
324 x_start_date,
325 x_end_date,
326 x_org_id,
327 x_creation_date,
328 x_created_by,
329 x_last_update_date,
330 x_last_updated_by,
331 x_last_update_login
332 );
333
334 IF (p_action = 'INSERT') THEN
335 -- Call all the procedures related to Before Insert.
336 Null;
337 IF Get_Pk_For_Validation(
338 new_references.tax_id) THEN
339 Fnd_Message.Set_name('IGS','IGS_PE_INCTAX_DUP_EXISTS');
340 IGS_GE_MSG_STACK.ADD;
341 App_Exception.Raise_Exception;
342 END IF;
343 Check_Uniqueness;
344 Check_Constraints;
345 Check_Parent_Existance;
346 ELSIF (p_action = 'UPDATE') THEN
347 -- Call all the procedures related to Before Update.
348 Null;
349 Check_Uniqueness;
350 Check_Constraints;
351 Check_Parent_Existance;
352 ELSIF (p_action = 'DELETE') THEN
353 -- Call all the procedures related to Before Delete.
354 Null;
355 ELSIF (p_action = 'VALIDATE_INSERT') THEN
356 -- Call all the procedures related to Before Insert.
357 IF Get_PK_For_Validation (
358 new_references.tax_id) THEN
359 Fnd_Message.Set_name('IGS','IGS_PE_INCTAX_DUP_EXISTS');
360 IGS_GE_MSG_STACK.ADD;
361 App_Exception.Raise_Exception;
362 END IF;
363 Check_Uniqueness;
364 Check_Constraints;
365 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
366 Check_Uniqueness;
367 Check_Constraints;
368 ELSIF (p_action = 'VALIDATE_DELETE') THEN
369 Null;
370 END IF;
371
372 END Before_DML;
373
374
375
376 PROCEDURE After_DML (
377 p_action IN VARCHAR2,
378 x_rowid IN VARCHAR2
379 ) IS
380 /*************************************************************
384 Know limitations, enhancements or remarks
381 Created By :srachako
382 Date Created By :11-MAY-2000
383 Purpose :After DML Operations
385 Change History
386 Who When What
387
388 (reverse chronological order - newest change first)
389 ***************************************************************/
390
391 BEGIN
392
393 l_rowid := x_rowid;
394
395 IF (p_action = 'INSERT') THEN
396 -- Call all the procedures related to After Insert.
397 Null;
398 ELSIF (p_action = 'UPDATE') THEN
399 -- Call all the procedures related to After Update.
400 Null;
401 ELSIF (p_action = 'DELETE') THEN
402 -- Call all the procedures related to After Delete.
403 Null;
404 END IF;
405
406 END After_DML;
407
408 procedure INSERT_ROW (
409 X_ROWID in out NOCOPY VARCHAR2,
410 x_TAX_ID IN OUT NOCOPY NUMBER,
411 x_PERSON_ID IN NUMBER,
412 x_TAX_INFO IN VARCHAR2,
413 x_TYPE_CODE IN VARCHAR2,
414 x_TYPE_CODE_ID IN NUMBER,
415 x_START_DATE IN DATE,
416 x_END_DATE IN DATE,
417 X_ORG_ID in NUMBER,
418 X_MODE in VARCHAR2 default 'R'
419 ) AS
420 /*************************************************************
421 Created By :srachako
422 Date Created By :11-MAY-2000
423 Purpose :For Inserting Values
424 Know limitations, enhancements or remarks
425 Change History
426 Who When What
427
428 (reverse chronological order - newest change first)
429 ***************************************************************/
430
431 cursor C is select ROWID from IGS_PE_INCOME_TAX_ALL
432 where TAX_ID= X_TAX_ID
433 ;
434 X_LAST_UPDATE_DATE DATE ;
435 X_LAST_UPDATED_BY NUMBER ;
436 X_LAST_UPDATE_LOGIN NUMBER ;
437 l_TYPE_CODE igs_pe_income_tax_all.type_code%TYPE;
438 l_TYPE_CODE_ID igs_pe_income_tax_all.type_code_id%TYPE;
439 begin
440 X_LAST_UPDATE_DATE := SYSDATE;
441 if(X_MODE = 'I') then
442 X_LAST_UPDATED_BY := 1;
443 X_LAST_UPDATE_LOGIN := 0;
444 elsif (X_MODE IN ('R', 'S')) then
445 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
446 if X_LAST_UPDATED_BY is NULL then
447 X_LAST_UPDATED_BY := -1;
448 end if;
449 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
450 if X_LAST_UPDATE_LOGIN is NULL then
451 X_LAST_UPDATE_LOGIN := -1;
452 end if;
453 else
454 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
455 IGS_GE_MSG_STACK.ADD;
456 app_exception.raise_exception;
457 end if;
458 SELECT IGS_PE_INCOME_TAX_S.NEXTVAL
459 INTO X_TAX_ID
460 FROM Dual;
461
462 IF (x_TAX_INFO='COUNTRY') THEN
463 l_TYPE_CODE_ID := NULL;
464 l_type_code := x_type_code;
465 ELSE
466 l_TYPE_CODE := NULL;
467 l_type_code_id := x_type_code_id;
468 END IF;
469
470 Before_DML(
471 p_action=>'INSERT',
472 x_rowid=>X_ROWID,
473 x_tax_id=>X_TAX_ID,
474 x_person_id=>X_PERSON_ID,
475 x_tax_info=>X_TAX_INFO,
476 x_type_code=>l_TYPE_CODE,
477 x_type_code_id=>l_TYPE_CODE_ID,
478 x_start_date=>X_START_DATE,
479 x_end_date=>X_END_DATE,
480 x_org_id => igs_ge_gen_003.get_org_id,
481 x_creation_date=>X_LAST_UPDATE_DATE,
482 x_created_by=>X_LAST_UPDATED_BY,
483 x_last_update_date=>X_LAST_UPDATE_DATE,
484 x_last_updated_by=>X_LAST_UPDATED_BY,
485 x_last_update_login=>X_LAST_UPDATE_LOGIN);
486
487
488 IF (x_mode = 'S') THEN
489 igs_sc_gen_001.set_ctx('R');
490 END IF;
491 insert into IGS_PE_INCOME_TAX_ALL (
492 TAX_ID
493 ,PERSON_ID
494 ,TAX_INFO
495 ,TYPE_CODE
496 ,TYPE_CODE_ID
497 ,START_DATE
498 ,END_DATE
499 ,ORG_ID
500 ,CREATION_DATE
501 ,CREATED_BY
502 ,LAST_UPDATE_DATE
503 ,LAST_UPDATED_BY
504 ,LAST_UPDATE_LOGIN
505 ) values (
506 NEW_REFERENCES.TAX_ID
507 ,NEW_REFERENCES.PERSON_ID
508 ,NEW_REFERENCES.TAX_INFO
509 ,NEW_REFERENCES.TYPE_CODE
510 ,NEW_REFERENCES.TYPE_CODE_ID
511 ,NEW_REFERENCES.START_DATE
512 ,NEW_REFERENCES.END_DATE
513 ,NEW_REFERENCES.ORG_ID
514 ,X_LAST_UPDATE_DATE
515 ,X_LAST_UPDATED_BY
516 ,X_LAST_UPDATE_DATE
517 ,X_LAST_UPDATED_BY
518 ,X_LAST_UPDATE_LOGIN
519 );
520 IF (x_mode = 'S') THEN
521 igs_sc_gen_001.unset_ctx('R');
522 END IF;
523
524
525
526 open c;
527 fetch c into X_ROWID;
528 if (c%notfound) then
529 close c;
530 raise no_data_found;
531 end if;
532 close c;
533 After_DML (
534 p_action => 'INSERT' ,
535 x_rowid => X_ROWID );
536 EXCEPTION
540 fnd_message.set_token ('ERR_CD', SQLCODE);
537 WHEN OTHERS THEN
538 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
539 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
541 igs_ge_msg_stack.add;
542 igs_sc_gen_001.unset_ctx('R');
543 app_exception.raise_exception;
544 ELSE
545 igs_sc_gen_001.unset_ctx('R');
546 RAISE;
547 END IF;
548
549 end INSERT_ROW;
550
551 procedure LOCK_ROW (
552 X_ROWID in VARCHAR2,
553 x_TAX_ID IN NUMBER,
554 x_PERSON_ID IN NUMBER,
555 x_TAX_INFO IN VARCHAR2,
556 x_TYPE_CODE IN VARCHAR2,
557 x_TYPE_CODE_ID IN NUMBER,
558 x_START_DATE IN DATE,
559 x_END_DATE IN DATE ) AS
560 /*************************************************************
561 Created By : srachako
562 Date Created By :11-MAY-2000
563 Purpose :For Locking Row
564 Know limitations, enhancements or remarks
565 Change History
566 Who When What
567 skpandey 23-SEP-2005 Bug: 4593149
568 Description: Added condition to avoid locking
569 (reverse chronological order - newest change first)
570 ***************************************************************/
571
572 cursor c1 is select
573 PERSON_ID
574 , TAX_INFO
575 , TYPE_CODE
576 , TYPE_CODE_ID
577 , START_DATE
578 , END_DATE
579 from IGS_PE_INCOME_TAX_ALL
580 where ROWID = X_ROWID
581 for update nowait;
582 tlinfo c1%rowtype;
583 l_TYPE_CODE igs_pe_income_tax_all.type_code%TYPE;
584 l_TYPE_CODE_ID igs_pe_income_tax_all.type_code_id%TYPE;
585 begin
586 open c1;
587 fetch c1 into tlinfo;
588 if (c1%notfound) then
589 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
590 IGS_GE_MSG_STACK.ADD;
591 close c1;
592 app_exception.raise_exception;
593 return;
594 end if;
595 close c1;
596
597 --skpandey.Bug#4593149
598 IF (x_TAX_INFO='COUNTRY') THEN
599 l_TYPE_CODE_ID := NULL;
600 l_type_code := x_type_code;
601 ELSE
602 l_TYPE_CODE := NULL;
603 l_type_code_id := x_type_code_id;
604 END IF;
605
606 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
607 AND (tlinfo.TAX_INFO = X_TAX_INFO)
608 AND (NVL(tlinfo.TYPE_CODE,' ') = NVL(l_type_code,' '))
609 AND (NVL(tlinfo.TYPE_CODE_ID,-1) = NVL(l_TYPE_CODE_ID,-1))
610 AND ((tlinfo.START_DATE = X_START_DATE)
611 OR ((tlinfo.START_DATE is null)
612 AND (X_START_DATE is null)))
613 AND ((tlinfo.END_DATE = X_END_DATE)
614 OR ((tlinfo.END_DATE is null)
615 AND (X_END_DATE is null)))
616 ) then
617 null;
618 else
619 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
620 IGS_GE_MSG_STACK.ADD;
621 app_exception.raise_exception;
622 end if;
623 return;
624 end LOCK_ROW;
625
626 Procedure UPDATE_ROW (
627 X_ROWID in VARCHAR2,
628 x_TAX_ID IN NUMBER,
629 x_PERSON_ID IN NUMBER,
630 x_TAX_INFO IN VARCHAR2,
631 x_TYPE_CODE IN VARCHAR2,
632 x_TYPE_CODE_ID IN NUMBER,
633 x_START_DATE IN DATE,
634 x_END_DATE IN DATE,
635 X_MODE in VARCHAR2 default 'R'
636 ) AS
637 /*************************************************************
638 Created By :srachako
639 Date Created By :11-MAY-2000
640 Purpose :For Updation of Row
641 Know limitations, enhancements or remarks
642 Change History
643 Who When What
644
645 (reverse chronological order - newest change first)
646 ***************************************************************/
647
648 X_LAST_UPDATE_DATE DATE ;
649 X_LAST_UPDATED_BY NUMBER ;
650 X_LAST_UPDATE_LOGIN NUMBER ;
651 l_TYPE_CODE igs_pe_income_tax_all.type_code%TYPE;
652 l_TYPE_CODE_ID igs_pe_income_tax_all.type_code_id%TYPE;
653 begin
654 X_LAST_UPDATE_DATE := SYSDATE;
655 if(X_MODE = 'I') then
656 X_LAST_UPDATED_BY := 1;
657 X_LAST_UPDATE_LOGIN := 0;
658 elsif (X_MODE IN ('R', 'S')) then
659 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
660 if X_LAST_UPDATED_BY is NULL then
661 X_LAST_UPDATED_BY := -1;
662 end if;
663 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
664 if X_LAST_UPDATE_LOGIN is NULL then
665 X_LAST_UPDATE_LOGIN := -1;
666 end if;
667 else
668 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
669 IGS_GE_MSG_STACK.ADD;
670 app_exception.raise_exception;
671 end if;
672
673 IF (x_TAX_INFO='COUNTRY') THEN
674 l_TYPE_CODE_ID := NULL;
675 l_type_code := x_type_code;
676 ELSE
677 l_TYPE_CODE := NULL;
678 l_type_code_id := x_type_code_id;
679 END IF;
680
681 Before_DML(
682 p_action=>'UPDATE',
683 x_rowid=>X_ROWID,
684 x_tax_id=>X_TAX_ID,
685 x_person_id=>X_PERSON_ID,
686 x_tax_info=>X_TAX_INFO,
687 x_type_code=>l_TYPE_CODE,
688 x_type_code_id=>l_TYPE_CODE_ID,
689 x_start_date=>X_START_DATE,
690 x_end_date=>X_END_DATE,
691 x_creation_date=>X_LAST_UPDATE_DATE,
692 x_created_by=>X_LAST_UPDATED_BY,
693 x_last_update_date=>X_LAST_UPDATE_DATE,
694 x_last_updated_by=>X_LAST_UPDATED_BY,
695 x_last_update_login=>X_LAST_UPDATE_LOGIN);
696 IF (x_mode = 'S') THEN
697 igs_sc_gen_001.set_ctx('R');
698 END IF;
699 update IGS_PE_INCOME_TAX_ALL set
700 PERSON_ID = NEW_REFERENCES.PERSON_ID,
701 TAX_INFO = NEW_REFERENCES.TAX_INFO,
702 TYPE_CODE = NEW_REFERENCES.TYPE_CODE,
703 TYPE_CODE_ID = NEW_REFERENCES.TYPE_CODE_ID,
704 START_DATE = NEW_REFERENCES.START_DATE,
705 END_DATE = NEW_REFERENCES.END_DATE,
706 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
707 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
708 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
709 where ROWID = X_ROWID;
710 if (sql%notfound) then
711 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
712 igs_ge_msg_stack.add;
713 igs_sc_gen_001.unset_ctx('R');
714 app_exception.raise_exception;
715 end if;
716 IF (x_mode = 'S') THEN
717 igs_sc_gen_001.unset_ctx('R');
718 END IF;
719
720
721 After_DML (
722 p_action => 'UPDATE' ,
723 x_rowid => X_ROWID
724 );
725 EXCEPTION
726 WHEN OTHERS THEN
727 IF (SQLCODE = (-28115)) THEN
728 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
729 fnd_message.set_token ('ERR_CD', SQLCODE);
730 igs_ge_msg_stack.add;
731 igs_sc_gen_001.unset_ctx('R');
732 app_exception.raise_exception;
733 ELSE
734 igs_sc_gen_001.unset_ctx('R');
735 RAISE;
736 END IF;
737
738 end UPDATE_ROW;
739 procedure ADD_ROW (
740 X_ROWID in out NOCOPY VARCHAR2,
741 x_TAX_ID IN OUT NOCOPY NUMBER,
742 x_PERSON_ID IN NUMBER,
743 x_TAX_INFO IN VARCHAR2,
744 x_TYPE_CODE IN VARCHAR2,
745 x_TYPE_CODE_ID IN NUMBER,
746 x_START_DATE IN DATE,
747 x_END_DATE IN DATE,
748 X_ORG_ID in NUMBER,
749 X_MODE in VARCHAR2 default 'R'
750 ) AS
751 /*************************************************************
752 Created By :srachako
753 Date Created By :11-MAY-2000
754 Purpose :for Insertion of Values
755 Know limitations, enhancements or remarks
756 Change History
757 Who When What
758
759 (reverse chronological order - newest change first)
760 ***************************************************************/
761
762 cursor c1 is select ROWID from IGS_PE_INCOME_TAX_ALL
763 where TAX_ID= X_TAX_ID
764 ;
765 begin
766 open c1;
767 fetch c1 into X_ROWID;
768 if (c1%notfound) then
769 close c1;
770
771
772
773 INSERT_ROW (
774 X_ROWID,
775 X_TAX_ID,
776 X_PERSON_ID,
777 X_TAX_INFO,
778 X_TYPE_CODE,
779 X_TYPE_CODE_ID,
780 X_START_DATE,
781 X_END_DATE,
782 X_ORG_ID,
783 X_MODE );
784 return;
785 end if;
786 close c1;
787 UPDATE_ROW (
788 X_ROWID,
789 X_TAX_ID,
790 X_PERSON_ID,
791 X_TAX_INFO,
792 X_TYPE_CODE,
793 X_TYPE_CODE_ID,
794 X_START_DATE,
795 X_END_DATE,
796 X_MODE );
797 end ADD_ROW;
798 procedure DELETE_ROW (
799 X_ROWID in VARCHAR2,
800 x_mode IN VARCHAR2
801 ) AS
802 /*************************************************************
803 Created By :srachako
804 Date Created By :11-MAY-2000
805 Purpose :for Deletion of Rows
806 Know limitations, enhancements or remarks
807 Change History
808 Who When What
809
810 (reverse chronological order - newest change first)
811 ***************************************************************/
812
813 begin
814 Before_DML (
815 p_action => 'DELETE',
816 x_rowid => X_ROWID
817 );
818 IF (x_mode = 'S') THEN
819 igs_sc_gen_001.set_ctx('R');
820 END IF;
821 delete from IGS_PE_INCOME_TAX_ALL
822 where ROWID = X_ROWID;
823 if (sql%notfound) then
824 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
825 igs_ge_msg_stack.add;
826 igs_sc_gen_001.unset_ctx('R');
827 app_exception.raise_exception;
828 end if;
829 IF (x_mode = 'S') THEN
830 igs_sc_gen_001.unset_ctx('R');
831 END IF;
832
833 After_DML (
834 p_action => 'DELETE',
835 x_rowid => X_ROWID
836 );
837 end DELETE_ROW;
838
839 END igs_pe_income_tax_pkg;
[Home] [Help]