[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_MIL_SERVICES_PKG
Source
1 PACKAGE BODY igs_pe_mil_services_pkg AS
2 /* $Header: IGSNI59B.pls 120.3 2005/10/17 02:21:53 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PE_MIL_SERVICES_ALL%RowType;
6 new_references IGS_PE_MIL_SERVICES_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_milit_service_id IN NUMBER,-- DEFAULT NULL,
12 x_person_id IN NUMBER,-- DEFAULT NULL,
13 x_start_date IN DATE,-- DEFAULT NULL,
14 x_end_date IN DATE,-- DEFAULT NULL,
15 X_ORG_ID in NUMBER,-- DEFAULT NULL,
16 x_attribute_category IN VARCHAR2,-- DEFAULT NULL,
17 x_attribute1 IN VARCHAR2,-- DEFAULT NULL,
18 x_attribute2 IN VARCHAR2,-- DEFAULT NULL,
19 x_attribute3 IN VARCHAR2,-- DEFAULT NULL,
20 x_attribute4 IN VARCHAR2,-- DEFAULT NULL,
21 x_attribute5 IN VARCHAR2,-- DEFAULT NULL,
22 x_attribute6 IN VARCHAR2,-- DEFAULT NULL,
23 x_attribute7 IN VARCHAR2,-- DEFAULT NULL,
24 x_attribute8 IN VARCHAR2,-- DEFAULT NULL,
25 x_attribute9 IN VARCHAR2,-- DEFAULT NULL,
26 x_attribute10 IN VARCHAR2,-- DEFAULT NULL,
27 x_attribute11 IN VARCHAR2,-- DEFAULT NULL,
28 x_attribute12 IN VARCHAR2,-- DEFAULT NULL,
29 x_attribute13 IN VARCHAR2,-- DEFAULT NULL,
30 x_attribute14 IN VARCHAR2,-- DEFAULT NULL,
31 x_attribute15 IN VARCHAR2,-- DEFAULT NULL,
32 x_attribute16 IN VARCHAR2,-- DEFAULT NULL,
33 x_attribute17 IN VARCHAR2,-- DEFAULT NULL,
34 x_attribute18 IN VARCHAR2,-- DEFAULT NULL,
35 x_attribute19 IN VARCHAR2,-- DEFAULT NULL,
36 x_attribute20 IN VARCHAR2,-- DEFAULT NULL,
37 x_military_type_cd IN VARCHAR2,
38 x_separation_type_cd IN VARCHAR2,-- DEFAULT NULL,
39 x_assistance_type_cd IN VARCHAR2,-- DEFAULT NULL,
40 x_assistance_status_cd IN VARCHAR2,-- DEFAULT NULL,
41 x_creation_date IN DATE,-- DEFAULT NULL,
42 x_created_by IN NUMBER,-- DEFAULT NULL,
43 x_last_update_date IN DATE,-- DEFAULT NULL,
44 x_last_updated_by IN NUMBER,-- DEFAULT NULL,
45 x_last_update_login IN NUMBER-- DEFAULT NULL
46 ) AS
47 /***********************************************************
48
49 Created By : vvaitla
50
51 Date Created By : 2000/05/10
52
53 Purpose : To update,insert, add rows
54
55 Know limitations, enhancements or remarks
56
57 Change History
58
59 Who When What
60
61 ****************************************************************/
62
63 CURSOR cur_old_ref_values IS
64 SELECT *
65 FROM IGS_PE_MIL_SERVICES_ALL
66 WHERE rowid = x_rowid;
67
68 BEGIN
69
70 l_rowid := x_rowid;
71
72 -- Code for setting the Old and New Reference Values.
73 -- Populate Old Values.
74 Open cur_old_ref_values;
75 Fetch cur_old_ref_values INTO old_references;
76 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
77 Close cur_old_ref_values;
78 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
79 IGS_GE_MSG_STACK.ADD;
80 App_Exception.Raise_Exception;
81 Return;
82 END IF;
83 Close cur_old_ref_values;
84
85 -- Populate New Values.
86 new_references.milit_service_id := x_milit_service_id;
87 new_references.person_id := x_person_id;
88 new_references.start_date := x_start_date;
89 new_references.end_date := x_end_date;
90 new_references.org_id := x_org_id;
91 new_references.attribute_category := x_attribute_category;
92 new_references.attribute1 := x_attribute1;
93 new_references.attribute2 := x_attribute2;
94 new_references.attribute3 := x_attribute3;
95 new_references.attribute4 := x_attribute4;
96 new_references.attribute5 := x_attribute5;
97 new_references.attribute6 := x_attribute6;
98 new_references.attribute7 := x_attribute7;
99 new_references.attribute8 := x_attribute8;
100 new_references.attribute9 := x_attribute9;
101 new_references.attribute10 := x_attribute10;
102 new_references.attribute11 := x_attribute11;
103 new_references.attribute12 := x_attribute12;
104 new_references.attribute13 := x_attribute13;
105 new_references.attribute14 := x_attribute14;
106 new_references.attribute15 := x_attribute15;
107 new_references.attribute16 := x_attribute16;
108 new_references.attribute17 := x_attribute17;
109 new_references.attribute18 := x_attribute18;
110 new_references.attribute19 := x_attribute19;
111 new_references.attribute20 := x_attribute20;
112 new_references.military_type_cd := x_military_type_cd;
113 new_references.separation_type_cd := x_separation_type_cd;
114 new_references.assistance_type_cd := x_assistance_type_cd;
115 new_references.assistance_status_cd := x_assistance_status_cd;
116 IF (p_action = 'UPDATE') THEN
117 new_references.creation_date := old_references.creation_date;
118 new_references.created_by := old_references.created_by;
119 ELSE
120 new_references.creation_date := x_creation_date;
121 new_references.created_by := x_created_by;
122 END IF;
123 new_references.last_update_date := x_last_update_date;
124 new_references.last_updated_by := x_last_updated_by;
125 new_references.last_update_login := x_last_update_login;
126
127 END Set_Column_Values;
128
129
130 PROCEDURE BeforeRowInsertUpdate(
131 p_inserting IN BOOLEAN,
132 p_updating IN BOOLEAN,
133 p_deleting IN BOOLEAN
134 ) as
135 ------------------------------------------------------------------------------------------
136 --Created by : vredkar
137 --Date created: 18-JUL-2005
138 --
139 --Purpose:
140 --Known limitations/enhancements and/or remarks:
141 --
142 --Change History:
143 --Who When What
144 ----------------------------------------------------------------------------------------------
145 CURSOR validate_mil_dt(cp_person_id NUMBER) IS
146 SELECT birth_date
147 FROM IGS_PE_PERSON_BASE_V
148 WHERE person_id = cp_person_id ;
149
150 l_bth_dt IGS_PE_PERSON_BASE_V.birth_date%TYPE;
151
152 BEGIN
153 IF p_inserting OR p_updating THEN
154 OPEN validate_mil_dt(new_references.person_id);
155 FETCH validate_mil_dt INTO l_bth_dt;
156 CLOSE validate_mil_dt;
157
158 IF new_references.END_DATE IS NOT NULL AND new_references.END_DATE < new_references.START_DATE THEN
159 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_DATE');
160 IGS_GE_MSG_STACK.ADD;
161 APP_EXCEPTION.RAISE_EXCEPTION;
162
163 ELSIF l_bth_dt IS NOT NULL AND l_bth_dt > new_references.START_DATE THEN
164 FND_MESSAGE.SET_NAME('IGS','IGS_PE_SERVICE_STDT_LS_BRDT');
165 IGS_GE_MSG_STACK.ADD;
166 APP_EXCEPTION.RAISE_EXCEPTION;
167 END IF;
168
169 END IF;
170
171
172 END BeforeRowInsertUpdate;
173
174
175
176
177 PROCEDURE Check_Constraints (
178 Column_Name IN VARCHAR2 , -- DEFAULT NULL,
179 Column_Value IN VARCHAR2 ) AS
180 /***********************************************************
181
182 Created By : vvaitla
183
184 Date Created By : 2000/05/10
185
186 Purpose : To update,insert, add rows
187
188 Know limitations, enhancements or remarks
189
190 Change History
191
192 Who When What
193
194 ****************************************************************/
195 BEGIN
196
197 IF column_name IS NULL THEN
198 NULL;
199 END IF;
200
201 END Check_Constraints;
202
203
204 FUNCTION Get_UK_For_Validation (
205 x_person_id IN NUMBER,
206 x_military_type_cd IN VARCHAR2,
207 x_start_date IN DATE
208 )RETURN BOOLEAN AS
209
210 CURSOR cur_rowid IS
211 SELECT rowid
212 FROM IGS_PE_MIL_SERVICES_ALL
213 WHERE military_type_cd = x_military_type_cd
214 AND person_id = x_person_id
215 AND start_date = x_start_date
216 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
217 FOR UPDATE NOWAIT;
218
219 lv_rowid cur_rowid%RowType;
220
221 BEGIN
222
223 Open cur_rowid;
224 Fetch cur_rowid INTO lv_rowid;
225 IF (cur_rowid%FOUND) THEN
226 Close cur_rowid;
227 Return (TRUE);
228 ELSE
229 Close cur_rowid;
230 Return (FALSE);
231 END IF;
232
233 END Get_UK_For_Validation;
234
235 PROCEDURE Check_Uniqueness AS
236 Begin
237 IF Get_UK_For_Validation (
238 new_references.person_id,
239 new_references.military_type_cd,
240 new_references.start_date
241 )THEN
242 Fnd_Message.Set_Name ('IGS', 'IGS_PE_MILTARY_DUP_EXISTS');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END IF;
246 End Check_Uniqueness;
247
248 PROCEDURE Check_Parent_Existance AS
249 /***********************************************************
250
251 Created By : vvaitla
252
253 Date Created By : 2000/05/10
254
255 Purpose : To update,insert, add rows
256
257 Know limitations, enhancements or remarks
258
259 Change History
260
261 Who When What
262
263 ****************************************************************/
264 BEGIN
265
266 IF (((old_references.military_type_cd = new_references.military_type_cd)) OR
267 ((new_references.military_type_cd IS NULL))) THEN
268 NULL;
269
270 ELSIF NOT Igs_Lookups_view_pkg.Get_PK_For_Validation (
271 'PE_MIL_SEV_TYPE',new_references.military_type_cd
272 ) THEN
273 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
274 IGS_GE_MSG_STACK.ADD;
275 App_Exception.Raise_Exception;
276 END IF;
277
278 IF (((old_references.person_id = new_references.person_id)) OR
279 ((new_references.person_id IS NULL))) THEN
280 NULL;
281 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
282 new_references.person_id
283 ) THEN
284 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
285 IGS_GE_MSG_STACK.ADD;
286 App_Exception.Raise_Exception;
287 END IF;
288
289 IF (((old_references.separation_type_cd = new_references.separation_type_cd)) OR
290 ((new_references.separation_type_cd IS NULL))) THEN
291 NULL;
292 ELSIF NOT Igs_Lookups_view_pkg.Get_PK_For_Validation (
293 'PE_MIL_SEP_TYPE',new_references.separation_type_cd
294
295 ) THEN
296 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 END IF;
300
301 IF (((old_references.assistance_status_cd = new_references.assistance_status_cd)) OR
302 ((new_references.assistance_status_cd IS NULL))) THEN
303 NULL;
304 ELSIF NOT Igs_Lookups_view_pkg.Get_PK_For_Validation (
305 'PE_MIL_ASS_STATUS',new_references.assistance_status_cd
306 ) THEN
307 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
308 IGS_GE_MSG_STACK.ADD;
309 App_Exception.Raise_Exception;
310 END IF;
311
312 IF (((old_references.assistance_type_cd = new_references.assistance_type_cd)) OR
313 ((new_references.assistance_type_cd IS NULL))) THEN
314 NULL;
315 ELSIF NOT Igs_Lookups_view_pkg.Get_PK_For_Validation (
316 'PE_MIL_ASS_TYPE',new_references.assistance_type_cd
317
318 ) THEN
319 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
320 IGS_GE_MSG_STACK.ADD;
321 App_Exception.Raise_Exception;
322 END IF;
323
324 END Check_Parent_Existance;
325
326 FUNCTION Get_PK_For_Validation (
327 x_milit_service_id IN NUMBER
328 ) RETURN BOOLEAN AS
329 /***********************************************************
330
331 Created By : vvaitla
332
333 Date Created By : 2000/05/10
334
335 Purpose : To update,insert, add rows
336
337 Know limitations, enhancements or remarks
338
339 Change History
340
341 Who When What
342
343 ****************************************************************/
344
345 CURSOR cur_rowid IS
346 SELECT rowid
347 FROM IGS_PE_MIL_SERVICES_ALL
348 WHERE milit_service_id = x_milit_service_id
349 FOR UPDATE NOWAIT;
350
351 lv_rowid cur_rowid%RowType;
352
353 BEGIN
354
355 Open cur_rowid;
356 Fetch cur_rowid INTO lv_rowid;
357 IF (cur_rowid%FOUND) THEN
358 Close cur_rowid;
359 Return(TRUE);
360 ELSE
361 Close cur_rowid;
362 Return(FALSE);
363 END IF;
364 END Get_PK_For_Validation;
365
366 PROCEDURE Get_FK_Igs_Pe_Person (
367 x_person_id IN NUMBER
368 ) AS
369 /***********************************************************
370
371 Created By : vvaitla
372
373 Date Created By : 2000/05/10
374
375 Purpose : To update,insert, add rows
376
377 Know limitations, enhancements or remarks
378
379 Change History
380
381 Who When What
382
383 ****************************************************************/
384
385 CURSOR cur_rowid IS
386 SELECT rowid
387 FROM IGS_PE_MIL_SERVICES_ALL
388 WHERE person_id = x_person_id ;
389
390 lv_rowid cur_rowid%RowType;
391
392 BEGIN
393
394 Open cur_rowid;
395 Fetch cur_rowid INTO lv_rowid;
396 IF (cur_rowid%FOUND) THEN
397 Close cur_rowid;
398 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PMS_PP_FK');
399 IGS_GE_MSG_STACK.ADD;
400 App_Exception.Raise_Exception;
401 Return;
402 END IF;
403 Close cur_rowid;
404
405 END Get_FK_Igs_Pe_Person;
406
407
408 PROCEDURE Before_DML (
409 p_action IN VARCHAR2,
410 x_rowid IN VARCHAR2,-- DEFAULT NULL,
411 x_milit_service_id IN NUMBER,-- DEFAULT NULL,
412 x_person_id IN NUMBER,-- DEFAULT NULL,
413 x_military_type_cd IN VARCHAR2 ,--DEFAULT NULL,
414 x_start_date IN DATE,-- DEFAULT NULL,
415 x_end_date IN DATE ,--DEFAULT NULL,
416 x_separation_type_cd IN VARCHAR2,-- DEFAULT NULL,
417 x_assistance_type_cd IN VARCHAR2,-- DEFAULT NULL,
418 x_assistance_status_cd IN VARCHAR2 ,--DEFAULT NULL,
419 x_attribute_category IN VARCHAR2,-- DEFAULT NULL,
420 x_attribute1 IN VARCHAR2 ,--DEFAULT NULL,
421 x_attribute2 IN VARCHAR2,-- DEFAULT NULL,
422 x_attribute3 IN VARCHAR2 ,--DEFAULT NULL,
423 x_attribute4 IN VARCHAR2 ,--DEFAULT NULL,
424 x_attribute5 IN VARCHAR2 ,--DEFAULT NULL,
425 x_attribute6 IN VARCHAR2 ,--DEFAULT NULL,
426 x_attribute7 IN VARCHAR2 ,--DEFAULT NULL,
427 x_attribute8 IN VARCHAR2 ,--DEFAULT NULL,
428 x_attribute9 IN VARCHAR2 ,--DEFAULT NULL,
429 x_attribute10 IN VARCHAR2,-- DEFAULT NULL,
430 x_attribute11 IN VARCHAR2,-- DEFAULT NULL,
431 x_attribute12 IN VARCHAR2,-- DEFAULT NULL,
432 x_attribute13 IN VARCHAR2,-- DEFAULT NULL,
433 x_attribute14 IN VARCHAR2 ,--DEFAULT NULL,
434 x_attribute15 IN VARCHAR2,-- DEFAULT NULL,
435 x_attribute16 IN VARCHAR2,-- DEFAULT NULL,
436 x_attribute17 IN VARCHAR2,-- DEFAULT NULL,
437 x_attribute18 IN VARCHAR2,-- DEFAULT NULL,
438 x_attribute19 IN VARCHAR2,-- DEFAULT NULL,
439 x_attribute20 IN VARCHAR2,-- DEFAULT NULL,
440 X_ORG_ID in NUMBER,-- DEFAULT NULL,
441 x_creation_date IN DATE,-- DEFAULT NULL,
442 x_created_by IN NUMBER,-- DEFAULT NULL,
443 x_last_update_date IN DATE ,--DEFAULT NULL,
444 x_last_updated_by IN NUMBER,-- DEFAULT NULL,
445 x_last_update_login IN NUMBER --DEFAULT NULL
446 ) AS
447 /***********************************************************
448
449 Created By : vvaitla
450
451 Date Created By : 2000/05/10
452
453 Purpose : To update,insert, add rows
454
455 Know limitations, enhancements or remarks
456
457 Change History
458
459 Who When What
460
461 ****************************************************************/
462 BEGIN
463
464 Set_Column_Values (
465 p_action,
466 x_rowid,
467 x_milit_service_id,
468 x_person_id,
469 x_start_date,
470 x_end_date,
471 x_org_id,
472 x_attribute_category,
473 x_attribute1,
474 x_attribute2,
475 x_attribute3,
476 x_attribute4,
477 x_attribute5,
478 x_attribute6,
479 x_attribute7,
480 x_attribute8,
481 x_attribute9,
482 x_attribute10,
483 x_attribute11,
484 x_attribute12,
485 x_attribute13,
486 x_attribute14,
487 x_attribute15,
488 x_attribute16,
489 x_attribute17,
490 x_attribute18,
491 x_attribute19,
492 x_attribute20,
493 x_military_type_cd,
494 x_separation_type_cd,
495 x_assistance_type_cd,
496 x_assistance_status_cd,
497 x_creation_date,
498 x_created_by,
499 x_last_update_date,
500 x_last_updated_by,
501 x_last_update_login
502 );
503
504 IF (p_action = 'INSERT') THEN
505 -- Call all the procedures related to Before Insert.
506 BeforeRowInsertUpdate( TRUE, FALSE,FALSE );
507 Null;
508 IF Get_Pk_For_Validation(
509 new_references.milit_service_id) THEN
510 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
511 IGS_GE_MSG_STACK.ADD;
512 App_Exception.Raise_Exception;
513 END IF;
514 Check_Constraints;
515 Check_Uniqueness;
516 Check_Parent_Existance;
517 ELSIF (p_action = 'UPDATE') THEN
518 -- Call all the procedures related to Before Update.
519 BeforeRowInsertUpdate( FALSE,TRUE,FALSE );
520 Null;
521 Check_Constraints;
522 Check_Uniqueness;
523 Check_Parent_Existance;
524 ELSIF (p_action = 'DELETE') THEN
525 -- Call all the procedures related to Before Delete.
526 Null;
527 ELSIF (p_action = 'VALIDATE_INSERT') THEN
528 -- Call all the procedures related to Before Insert.
529 IF Get_PK_For_Validation (
530 new_references.milit_service_id) THEN
531 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
532 IGS_GE_MSG_STACK.ADD;
533 App_Exception.Raise_Exception;
534 END IF;
535 Check_Constraints;
536 Check_Uniqueness;
537 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
538 Check_Constraints;
539 Check_Uniqueness;
540 ELSIF (p_action = 'VALIDATE_DELETE') THEN
541 Null;
542 END IF;
543
544 END Before_DML;
545
546 PROCEDURE After_DML (
547 p_action IN VARCHAR2,
548 x_rowid IN VARCHAR2
549 ) IS
550 /***********************************************************
551
552 Created By : vvaitla
553
554 Date Created By : 2000/05/10
555
556 Purpose : To update,insert, add rows
557
558 Know limitations, enhancements or remarks
559
560 Change History
561
562 Who When What
563
564 ****************************************************************/
565 BEGIN
566
567 l_rowid := x_rowid;
568
569 IF (p_action = 'INSERT') THEN
570 -- Call all the procedures related to After Insert.
571 Null;
572 ELSIF (p_action = 'UPDATE') THEN
573 -- Call all the procedures related to After Update.
574 Null;
575 ELSIF (p_action = 'DELETE') THEN
576 -- Call all the procedures related to After Delete.
577 Null;
578 END IF;
579
580 END After_DML;
581
582 procedure INSERT_ROW (
583 X_ROWID in out NOCOPY VARCHAR2,
584 x_MILIT_SERVICE_ID IN OUT NOCOPY NUMBER,
585 x_PERSON_ID IN NUMBER,
586 x_MILITARY_TYPE_CD IN VARCHAR2,
587 x_START_DATE IN DATE,
588 x_END_DATE IN DATE,
589 x_SEPARATION_TYPE_CD IN VARCHAR2,
590 x_ASSISTANCE_TYPE_CD IN VARCHAR2,
591 x_ASSISTANCE_STATUS_CD IN VARCHAR2,
592 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
593 x_ATTRIBUTE1 IN VARCHAR2,
594 x_ATTRIBUTE2 IN VARCHAR2,
595 x_ATTRIBUTE3 IN VARCHAR2,
596 x_ATTRIBUTE4 IN VARCHAR2,
597 x_ATTRIBUTE5 IN VARCHAR2,
598 x_ATTRIBUTE6 IN VARCHAR2,
599 x_ATTRIBUTE7 IN VARCHAR2,
600 x_ATTRIBUTE8 IN VARCHAR2,
601 x_ATTRIBUTE9 IN VARCHAR2,
602 x_ATTRIBUTE10 IN VARCHAR2,
603 x_ATTRIBUTE11 IN VARCHAR2,
604 x_ATTRIBUTE12 IN VARCHAR2,
605 x_ATTRIBUTE13 IN VARCHAR2,
606 x_ATTRIBUTE14 IN VARCHAR2,
607 x_ATTRIBUTE15 IN VARCHAR2,
608 x_ATTRIBUTE16 IN VARCHAR2,
609 x_ATTRIBUTE17 IN VARCHAR2,
610 x_ATTRIBUTE18 IN VARCHAR2,
611 x_ATTRIBUTE19 IN VARCHAR2,
612 x_ATTRIBUTE20 IN VARCHAR2,
613 X_ORG_ID in NUMBER,
614 X_MODE in VARCHAR2 --default 'R'
615 ) AS
616 /***********************************************************
617
618 Created By : vvaitla
619
620 Date Created By : 2000/05/10
621
622 Purpose : To update,insert, add rows
623
624 Know limitations, enhancements or remarks
625
626 Change History
627
628 Who When What
629
630 ****************************************************************/
631 cursor C is select ROWID from IGS_PE_MIL_SERVICES_ALL
632 where MILIT_SERVICE_ID= X_MILIT_SERVICE_ID
633 ;
634 X_LAST_UPDATE_DATE DATE ;
635 X_LAST_UPDATED_BY NUMBER ;
636 X_LAST_UPDATE_LOGIN NUMBER ;
637 begin
638 X_LAST_UPDATE_DATE := SYSDATE;
639 if(X_MODE = 'I') then
640 X_LAST_UPDATED_BY := 1;
641 X_LAST_UPDATE_LOGIN := 0;
642 elsif (X_MODE IN ('R', 'S')) then
643 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
644 if X_LAST_UPDATED_BY is NULL then
645 X_LAST_UPDATED_BY := -1;
646 end if;
647 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
648 if X_LAST_UPDATE_LOGIN is NULL then
649 X_LAST_UPDATE_LOGIN := -1;
650 end if;
651 else
652 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
653 IGS_GE_MSG_STACK.ADD;
654 app_exception.raise_exception;
655 end if;
656 SELECT IGS_PE_MIL_SERVICE_S.NEXTVAL INTO X_MILIT_SERVICE_ID
657 FROM DUAL;
658 Before_DML(
659 p_action=>'INSERT',
660 x_rowid=>X_ROWID,
661 x_milit_service_id=>X_MILIT_SERVICE_ID,
662 x_person_id=>X_PERSON_ID,
663 x_start_date=>X_START_DATE,
664 x_end_date=>X_END_DATE,
665 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
666 x_attribute1=>X_ATTRIBUTE1,
667 x_attribute2=>X_ATTRIBUTE2,
668 x_attribute3=>X_ATTRIBUTE3,
669 x_attribute4=>X_ATTRIBUTE4,
670 x_attribute5=>X_ATTRIBUTE5,
671 x_attribute6=>X_ATTRIBUTE6,
672 x_attribute7=>X_ATTRIBUTE7,
673 x_attribute8=>X_ATTRIBUTE8,
674 x_attribute9=>X_ATTRIBUTE9,
675 x_attribute10=>X_ATTRIBUTE10,
676 x_attribute11=>X_ATTRIBUTE11,
677 x_attribute12=>X_ATTRIBUTE12,
678 x_attribute13=>X_ATTRIBUTE13,
679 x_attribute14=>X_ATTRIBUTE14,
680 x_attribute15=>X_ATTRIBUTE15,
681 x_attribute16=>X_ATTRIBUTE16,
682 x_attribute17=>X_ATTRIBUTE17,
683 x_attribute18=>X_ATTRIBUTE18,
684 x_attribute19=>X_ATTRIBUTE19,
685 x_attribute20=>X_ATTRIBUTE20,
686 x_military_type_cd=>X_MILITARY_TYPE_CD,
687 x_separation_type_cd=>X_SEPARATION_TYPE_CD,
688 x_assistance_type_cd=>X_ASSISTANCE_TYPE_CD,
689 x_assistance_status_cd=>X_ASSISTANCE_STATUS_CD,
690 x_org_id => igs_ge_gen_003.get_org_id,
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 insert into IGS_PE_MIL_SERVICES_ALL (
700 MILIT_SERVICE_ID
701 ,PERSON_ID
702 ,START_DATE
703 ,END_DATE
704 ,ORG_ID
705 ,ATTRIBUTE_CATEGORY
706 ,ATTRIBUTE1
707 ,ATTRIBUTE2
708 ,ATTRIBUTE3
709 ,ATTRIBUTE4
710 ,ATTRIBUTE5
711 ,ATTRIBUTE6
712 ,ATTRIBUTE7
713 ,ATTRIBUTE8
714 ,ATTRIBUTE9
715 ,ATTRIBUTE10
716 ,ATTRIBUTE11
717 ,ATTRIBUTE12
718 ,ATTRIBUTE13
719 ,ATTRIBUTE14
720 ,ATTRIBUTE15
721 ,ATTRIBUTE16
722 ,ATTRIBUTE17
723 ,ATTRIBUTE18
724 ,ATTRIBUTE19
725 ,ATTRIBUTE20
726 ,MILITARY_TYPE_CD
727 ,SEPARATION_TYPE_CD
728 ,ASSISTANCE_TYPE_CD
729 ,ASSISTANCE_STATUS_CD
730 ,CREATION_DATE
731 ,CREATED_BY
732 ,LAST_UPDATE_DATE
733 ,LAST_UPDATED_BY
734 ,LAST_UPDATE_LOGIN
735 ) values (
736 NEW_REFERENCES.MILIT_SERVICE_ID
737 ,NEW_REFERENCES.PERSON_ID
738 ,NEW_REFERENCES.START_DATE
739 ,NEW_REFERENCES.END_DATE
740 ,NEW_REFERENCES.ORG_ID
741 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
742 ,NEW_REFERENCES.ATTRIBUTE1
743 ,NEW_REFERENCES.ATTRIBUTE2
744 ,NEW_REFERENCES.ATTRIBUTE3
745 ,NEW_REFERENCES.ATTRIBUTE4
746 ,NEW_REFERENCES.ATTRIBUTE5
747 ,NEW_REFERENCES.ATTRIBUTE6
748 ,NEW_REFERENCES.ATTRIBUTE7
749 ,NEW_REFERENCES.ATTRIBUTE8
750 ,NEW_REFERENCES.ATTRIBUTE9
751 ,NEW_REFERENCES.ATTRIBUTE10
752 ,NEW_REFERENCES.ATTRIBUTE11
753 ,NEW_REFERENCES.ATTRIBUTE12
754 ,NEW_REFERENCES.ATTRIBUTE13
755 ,NEW_REFERENCES.ATTRIBUTE14
756 ,NEW_REFERENCES.ATTRIBUTE15
757 ,NEW_REFERENCES.ATTRIBUTE16
758 ,NEW_REFERENCES.ATTRIBUTE17
759 ,NEW_REFERENCES.ATTRIBUTE18
760 ,NEW_REFERENCES.ATTRIBUTE19
761 ,NEW_REFERENCES.ATTRIBUTE20
762 ,NEW_REFERENCES.MILITARY_TYPE_CD
763 ,NEW_REFERENCES.SEPARATION_TYPE_CD
764 ,NEW_REFERENCES.ASSISTANCE_TYPE_CD
765 ,NEW_REFERENCES.ASSISTANCE_STATUS_CD
766 ,X_LAST_UPDATE_DATE
767 ,X_LAST_UPDATED_BY
768 ,X_LAST_UPDATE_DATE
769 ,X_LAST_UPDATED_BY
770 ,X_LAST_UPDATE_LOGIN
771 );
772 IF (x_mode = 'S') THEN
773 igs_sc_gen_001.unset_ctx('R');
774 END IF;
775
776 open c;
777 fetch c into X_ROWID;
778 if (c%notfound) then
779 close c;
780 raise no_data_found;
781 end if;
782 close c;
783 After_DML (
784 p_action => 'INSERT' ,
785 x_rowid => X_ROWID );
786 EXCEPTION
787 WHEN OTHERS THEN
788 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
789 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
790 fnd_message.set_token ('ERR_CD', SQLCODE);
791 igs_ge_msg_stack.add;
792 igs_sc_gen_001.unset_ctx('R');
793 app_exception.raise_exception;
794 ELSE
795 igs_sc_gen_001.unset_ctx('R');
796 RAISE;
797 END IF;
798
799 end INSERT_ROW;
800
801 procedure LOCK_ROW (
802 X_ROWID in VARCHAR2,
803 x_MILIT_SERVICE_ID IN NUMBER,
804 x_PERSON_ID IN NUMBER,
805 x_MILITARY_TYPE_CD IN VARCHAR2,
806 x_START_DATE IN DATE,
807 x_END_DATE IN DATE,
808 x_SEPARATION_TYPE_CD IN VARCHAR2,
809 x_ASSISTANCE_TYPE_CD IN VARCHAR2,
810 x_ASSISTANCE_STATUS_CD IN VARCHAR2,
811 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
812 x_ATTRIBUTE1 IN VARCHAR2,
813 x_ATTRIBUTE2 IN VARCHAR2,
814 x_ATTRIBUTE3 IN VARCHAR2,
815 x_ATTRIBUTE4 IN VARCHAR2,
816 x_ATTRIBUTE5 IN VARCHAR2,
817 x_ATTRIBUTE6 IN VARCHAR2,
818 x_ATTRIBUTE7 IN VARCHAR2,
819 x_ATTRIBUTE8 IN VARCHAR2,
820 x_ATTRIBUTE9 IN VARCHAR2,
821 x_ATTRIBUTE10 IN VARCHAR2,
822 x_ATTRIBUTE11 IN VARCHAR2,
823 x_ATTRIBUTE12 IN VARCHAR2,
824 x_ATTRIBUTE13 IN VARCHAR2,
825 x_ATTRIBUTE14 IN VARCHAR2,
826 x_ATTRIBUTE15 IN VARCHAR2,
827 x_ATTRIBUTE16 IN VARCHAR2,
828 x_ATTRIBUTE17 IN VARCHAR2,
829 x_ATTRIBUTE18 IN VARCHAR2,
830 x_ATTRIBUTE19 IN VARCHAR2,
831 x_ATTRIBUTE20 IN VARCHAR2
832 ) AS
833 /***********************************************************
834
835 Created By : vvaitla
836
837 Date Created By : 2000/05/10
838
839 Purpose : To update,insert, add rows
840
841 Know limitations, enhancements or remarks
842
843 Change History
844
845 Who When What
846
847 ****************************************************************/
848 cursor c1 is select
849 PERSON_ID
850 , START_DATE
851 , END_DATE
852 , ATTRIBUTE_CATEGORY
853 , ATTRIBUTE1
854 , ATTRIBUTE2
855 , ATTRIBUTE3
856 , ATTRIBUTE4
857 , ATTRIBUTE5
858 , ATTRIBUTE6
859 , ATTRIBUTE7
860 , ATTRIBUTE8
861 , ATTRIBUTE9
862 , ATTRIBUTE10
863 , ATTRIBUTE11
864 , ATTRIBUTE12
865 , ATTRIBUTE13
866 , ATTRIBUTE14
867 , ATTRIBUTE15
868 , ATTRIBUTE16
869 , ATTRIBUTE17
870 , ATTRIBUTE18
871 , ATTRIBUTE19
872 , ATTRIBUTE20
873 , MILITARY_TYPE_CD
874 , SEPARATION_TYPE_CD
875 , ASSISTANCE_TYPE_CD
876 , ASSISTANCE_STATUS_CD
877 from IGS_PE_MIL_SERVICES_ALL
878 where ROWID = X_ROWID
879 for update nowait;
880 tlinfo c1%rowtype;
881 begin
882 open c1;
883 fetch c1 into tlinfo;
884 if (c1%notfound) then
885 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
886 IGS_GE_MSG_STACK.ADD;
887 close c1;
888 app_exception.raise_exception;
889 return;
890 end if;
891 close c1;
892 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
893 AND (tlinfo.START_DATE = X_START_DATE)
894 AND ((tlinfo.END_DATE = X_END_DATE)
895 OR ((tlinfo.END_DATE is null)
896 AND (X_END_DATE is null)))
897 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
898 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
899 AND (X_ATTRIBUTE_CATEGORY is null)))
900 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
901 OR ((tlinfo.ATTRIBUTE1 is null)
902 AND (X_ATTRIBUTE1 is null)))
903 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
904 OR ((tlinfo.ATTRIBUTE2 is null)
905 AND (X_ATTRIBUTE2 is null)))
906 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
907 OR ((tlinfo.ATTRIBUTE3 is null)
908 AND (X_ATTRIBUTE3 is null)))
909 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
910 OR ((tlinfo.ATTRIBUTE4 is null)
911 AND (X_ATTRIBUTE4 is null)))
912 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
913 OR ((tlinfo.ATTRIBUTE5 is null)
914 AND (X_ATTRIBUTE5 is null)))
915 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
916 OR ((tlinfo.ATTRIBUTE6 is null)
917 AND (X_ATTRIBUTE6 is null)))
918 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
919 OR ((tlinfo.ATTRIBUTE7 is null)
920 AND (X_ATTRIBUTE7 is null)))
921 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
922 OR ((tlinfo.ATTRIBUTE8 is null)
923 AND (X_ATTRIBUTE8 is null)))
924 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
925 OR ((tlinfo.ATTRIBUTE9 is null)
926 AND (X_ATTRIBUTE9 is null)))
927 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
928 OR ((tlinfo.ATTRIBUTE10 is null)
929 AND (X_ATTRIBUTE10 is null)))
930 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
931 OR ((tlinfo.ATTRIBUTE11 is null)
932 AND (X_ATTRIBUTE11 is null)))
933 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
934 OR ((tlinfo.ATTRIBUTE12 is null)
935 AND (X_ATTRIBUTE12 is null)))
936 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
937 OR ((tlinfo.ATTRIBUTE13 is null)
938 AND (X_ATTRIBUTE13 is null)))
939 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
940 OR ((tlinfo.ATTRIBUTE14 is null)
941 AND (X_ATTRIBUTE14 is null)))
942 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
943 OR ((tlinfo.ATTRIBUTE15 is null)
944 AND (X_ATTRIBUTE15 is null)))
945 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
946 OR ((tlinfo.ATTRIBUTE16 is null)
947 AND (X_ATTRIBUTE16 is null)))
948 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
949 OR ((tlinfo.ATTRIBUTE17 is null)
950 AND (X_ATTRIBUTE17 is null)))
951 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
952 OR ((tlinfo.ATTRIBUTE18 is null)
953 AND (X_ATTRIBUTE18 is null)))
954 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
955 OR ((tlinfo.ATTRIBUTE19 is null)
956 AND (X_ATTRIBUTE19 is null)))
957 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
958 OR ((tlinfo.ATTRIBUTE20 is null)
959 AND (X_ATTRIBUTE20 is null)))
960 AND ((tlinfo.MILITARY_TYPE_CD = X_MILITARY_TYPE_CD)
961 OR ((tlinfo.MILITARY_TYPE_CD is null)
962 AND (X_MILITARY_TYPE_CD is null)))
963 AND ((tlinfo.SEPARATION_TYPE_CD = X_SEPARATION_TYPE_CD)
964 OR ((tlinfo.SEPARATION_TYPE_CD is null)
965 AND (X_SEPARATION_TYPE_CD is null)))
966 AND ((tlinfo.ASSISTANCE_TYPE_CD = X_ASSISTANCE_TYPE_CD)
967 OR ((tlinfo.ASSISTANCE_TYPE_CD is null)
968 AND (X_ASSISTANCE_TYPE_CD is null)))
969 AND ((tlinfo.ASSISTANCE_STATUS_CD = X_ASSISTANCE_STATUS_CD)
970 OR ((tlinfo.ASSISTANCE_STATUS_CD is null)
971 AND (X_ASSISTANCE_STATUS_CD is null)))
972 ) then
973 null;
974 else
975 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
976 IGS_GE_MSG_STACK.ADD;
977 app_exception.raise_exception;
978 end if;
979 return;
980 end LOCK_ROW;
981
982 Procedure UPDATE_ROW (
983 X_ROWID in VARCHAR2,
984 x_MILIT_SERVICE_ID IN NUMBER,
985 x_PERSON_ID IN NUMBER,
986 x_MILITARY_TYPE_CD IN VARCHAR2,
987 x_START_DATE IN DATE,
988 x_END_DATE IN DATE,
989 x_SEPARATION_TYPE_CD IN VARCHAR2,
990 x_ASSISTANCE_TYPE_CD IN VARCHAR2,
991 x_ASSISTANCE_STATUS_CD IN VARCHAR2,
992 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
993 x_ATTRIBUTE1 IN VARCHAR2,
994 x_ATTRIBUTE2 IN VARCHAR2,
995 x_ATTRIBUTE3 IN VARCHAR2,
996 x_ATTRIBUTE4 IN VARCHAR2,
997 x_ATTRIBUTE5 IN VARCHAR2,
998 x_ATTRIBUTE6 IN VARCHAR2,
999 x_ATTRIBUTE7 IN VARCHAR2,
1000 x_ATTRIBUTE8 IN VARCHAR2,
1001 x_ATTRIBUTE9 IN VARCHAR2,
1002 x_ATTRIBUTE10 IN VARCHAR2,
1003 x_ATTRIBUTE11 IN VARCHAR2,
1004 x_ATTRIBUTE12 IN VARCHAR2,
1005 x_ATTRIBUTE13 IN VARCHAR2,
1006 x_ATTRIBUTE14 IN VARCHAR2,
1007 x_ATTRIBUTE15 IN VARCHAR2,
1008 x_ATTRIBUTE16 IN VARCHAR2,
1009 x_ATTRIBUTE17 IN VARCHAR2,
1010 x_ATTRIBUTE18 IN VARCHAR2,
1011 x_ATTRIBUTE19 IN VARCHAR2,
1012 x_ATTRIBUTE20 IN VARCHAR2,
1013 X_MODE in VARCHAR2 --default 'R'
1014 ) AS
1015 /***********************************************************
1016
1017 Created By : vvaitla
1018
1019 Date Created By : 2000/05/10
1020
1021 Purpose : To update,insert, add rows
1022
1023 Know limitations, enhancements or remarks
1024
1025 Change History
1026
1027 Who When What
1028
1029 ****************************************************************/
1030 X_LAST_UPDATE_DATE DATE ;
1031 X_LAST_UPDATED_BY NUMBER ;
1032 X_LAST_UPDATE_LOGIN NUMBER ;
1033 begin
1034 X_LAST_UPDATE_DATE := SYSDATE;
1035 if(X_MODE = 'I') then
1036 X_LAST_UPDATED_BY := 1;
1037 X_LAST_UPDATE_LOGIN := 0;
1038 elsif (X_MODE IN ('R', 'S')) then
1039 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1040 if X_LAST_UPDATED_BY is NULL then
1041 X_LAST_UPDATED_BY := -1;
1042 end if;
1043 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1044 if X_LAST_UPDATE_LOGIN is NULL then
1045 X_LAST_UPDATE_LOGIN := -1;
1046 end if;
1047 else
1048 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1049 IGS_GE_MSG_STACK.ADD;
1050 app_exception.raise_exception;
1051 end if;
1052 Before_DML(
1053 p_action=>'UPDATE',
1054 x_rowid=>X_ROWID,
1055 x_milit_service_id=>X_MILIT_SERVICE_ID,
1056 x_person_id=>X_PERSON_ID,
1057 x_start_date=>X_START_DATE,
1058 x_end_date=>X_END_DATE,
1059 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1060 x_attribute1=>X_ATTRIBUTE1,
1061 x_attribute2=>X_ATTRIBUTE2,
1062 x_attribute3=>X_ATTRIBUTE3,
1063 x_attribute4=>X_ATTRIBUTE4,
1064 x_attribute5=>X_ATTRIBUTE5,
1065 x_attribute6=>X_ATTRIBUTE6,
1066 x_attribute7=>X_ATTRIBUTE7,
1067 x_attribute8=>X_ATTRIBUTE8,
1068 x_attribute9=>X_ATTRIBUTE9,
1069 x_attribute10=>X_ATTRIBUTE10,
1070 x_attribute11=>X_ATTRIBUTE11,
1071 x_attribute12=>X_ATTRIBUTE12,
1072 x_attribute13=>X_ATTRIBUTE13,
1073 x_attribute14=>X_ATTRIBUTE14,
1074 x_attribute15=>X_ATTRIBUTE15,
1075 x_attribute16=>X_ATTRIBUTE16,
1076 x_attribute17=>X_ATTRIBUTE17,
1077 x_attribute18=>X_ATTRIBUTE18,
1078 x_attribute19=>X_ATTRIBUTE19,
1079 x_attribute20=>X_ATTRIBUTE20,
1080 x_military_type_cd=>X_MILITARY_TYPE_CD,
1081 x_separation_type_cd=>X_SEPARATION_TYPE_CD,
1082 x_assistance_type_cd=>X_ASSISTANCE_TYPE_CD,
1083 x_assistance_status_cd=>X_ASSISTANCE_STATUS_CD,
1084 x_org_id => igs_ge_gen_003.get_org_id,
1085 x_creation_date=>X_last_update_date,
1086 x_created_by=>X_LAST_UPDATED_BY,
1087 x_last_update_date=>X_LAST_UPDATE_DATE,
1088 x_last_updated_by=>X_LAST_UPDATED_BY,
1089 x_last_update_login=>X_LAST_UPDATE_LOGIN);
1090 IF (x_mode = 'S') THEN
1091 igs_sc_gen_001.set_ctx('R');
1092 END IF;
1093 update IGS_PE_MIL_SERVICES_ALL set
1094 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1095 -- MILITARY_TYPE_ID = NEW_REFERENCES.MILITARY_TYPE_ID,
1096 START_DATE = NEW_REFERENCES.START_DATE,
1097 END_DATE = NEW_REFERENCES.END_DATE,
1098 --SEPARATION_TYPE_ID = NEW_REFERENCES.SEPARATION_TYPE_ID,
1099 --ASSISTANCE_TYPE_ID = NEW_REFERENCES.ASSISTANCE_TYPE_ID,
1100 --ASSISTANCE_STATUS_ID = NEW_REFERENCES.ASSISTANCE_STATUS_ID,
1101 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1102 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1103 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1104 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1105 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1106 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1107 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1108 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1109 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1110 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1111 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1112 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1113 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1114 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1115 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1116 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1117 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1118 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1119 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1120 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1121 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1122 MILITARY_TYPE_CD = NEW_REFERENCES.MILITARY_TYPE_CD,
1123 SEPARATION_TYPE_CD = NEW_REFERENCES.SEPARATION_TYPE_CD,
1124 ASSISTANCE_TYPE_CD = NEW_REFERENCES.ASSISTANCE_TYPE_CD,
1125 ASSISTANCE_STATUS_CD = NEW_REFERENCES.ASSISTANCE_STATUS_CD,
1126 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1127 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1128 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1129 where ROWID = X_ROWID;
1130 if (sql%notfound) then
1131 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1132 igs_ge_msg_stack.add;
1133 igs_sc_gen_001.unset_ctx('R');
1134 app_exception.raise_exception;
1135 end if;
1136 IF (x_mode = 'S') THEN
1137 igs_sc_gen_001.unset_ctx('R');
1138 END IF;
1139
1140
1141 After_DML (
1142 p_action => 'UPDATE' ,
1143 x_rowid => X_ROWID
1144 );
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 IF (SQLCODE = (-28115)) THEN
1148 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1149 fnd_message.set_token ('ERR_CD', SQLCODE);
1150 igs_ge_msg_stack.add;
1151 igs_sc_gen_001.unset_ctx('R');
1152 app_exception.raise_exception;
1153 ELSE
1154 igs_sc_gen_001.unset_ctx('R');
1155 RAISE;
1156 END IF;
1157
1158 end UPDATE_ROW;
1159
1160 procedure ADD_ROW (
1161 X_ROWID in out NOCOPY VARCHAR2,
1162 x_MILIT_SERVICE_ID IN OUT NOCOPY NUMBER,
1163 x_PERSON_ID IN NUMBER,
1164 x_MILITARY_TYPE_CD IN VARCHAR2,
1165 x_START_DATE IN DATE,
1166 x_END_DATE IN DATE,
1167 x_SEPARATION_TYPE_CD IN VARCHAR2,
1168 x_ASSISTANCE_TYPE_CD IN VARCHAR2,
1169 x_ASSISTANCE_STATUS_CD IN VARCHAR2,
1170 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1171 x_ATTRIBUTE1 IN VARCHAR2,
1172 x_ATTRIBUTE2 IN VARCHAR2,
1173 x_ATTRIBUTE3 IN VARCHAR2,
1174 x_ATTRIBUTE4 IN VARCHAR2,
1175 x_ATTRIBUTE5 IN VARCHAR2,
1176 x_ATTRIBUTE6 IN VARCHAR2,
1177 x_ATTRIBUTE7 IN VARCHAR2,
1178 x_ATTRIBUTE8 IN VARCHAR2,
1179 x_ATTRIBUTE9 IN VARCHAR2,
1180 x_ATTRIBUTE10 IN VARCHAR2,
1181 x_ATTRIBUTE11 IN VARCHAR2,
1182 x_ATTRIBUTE12 IN VARCHAR2,
1183 x_ATTRIBUTE13 IN VARCHAR2,
1184 x_ATTRIBUTE14 IN VARCHAR2,
1185 x_ATTRIBUTE15 IN VARCHAR2,
1186 x_ATTRIBUTE16 IN VARCHAR2,
1187 x_ATTRIBUTE17 IN VARCHAR2,
1188 x_ATTRIBUTE18 IN VARCHAR2,
1189 x_ATTRIBUTE19 IN VARCHAR2,
1190 x_ATTRIBUTE20 IN VARCHAR2,
1191 X_ORG_ID in NUMBER,
1192 X_MODE in VARCHAR2 --default 'R'
1193 ) AS
1194 /***********************************************************
1195
1196 Created By : vvaitla
1197
1198 Date Created By : 2000/05/10
1199
1200 Purpose : To update,insert, add rows
1201
1202 Know limitations, enhancements or remarks
1203
1204 Change History
1205
1206 Who When What
1207
1208 ****************************************************************/
1209 cursor c1 is select ROWID from IGS_PE_MIL_SERVICES_ALL
1210 where MILIT_SERVICE_ID= X_MILIT_SERVICE_ID
1211 ;
1212 begin
1213 open c1;
1214 fetch c1 into X_ROWID;
1215 if (c1%notfound) then
1216 close c1;
1217 INSERT_ROW (
1218 X_ROWID,
1219 X_MILIT_SERVICE_ID,
1220 X_PERSON_ID,
1221 X_START_DATE,
1222 X_END_DATE,
1223 X_ATTRIBUTE_CATEGORY,
1224 X_ATTRIBUTE1,
1225 X_ATTRIBUTE2,
1226 X_ATTRIBUTE3,
1227 X_ATTRIBUTE4,
1228 X_ATTRIBUTE5,
1229 X_ATTRIBUTE6,
1230 X_ATTRIBUTE7,
1231 X_ATTRIBUTE8,
1232 X_ATTRIBUTE9,
1233 X_ATTRIBUTE10,
1234 X_ATTRIBUTE11,
1235 X_ATTRIBUTE12,
1236 X_ATTRIBUTE13,
1237 X_ATTRIBUTE14,
1238 X_ATTRIBUTE15,
1239 X_ATTRIBUTE16,
1240 X_ATTRIBUTE17,
1241 X_ATTRIBUTE18,
1242 X_ATTRIBUTE19,
1243 X_ATTRIBUTE20,
1244 X_MILITARY_TYPE_CD,
1245 X_SEPARATION_TYPE_CD,
1246 X_ASSISTANCE_TYPE_CD,
1247 X_ASSISTANCE_STATUS_CD,
1248 X_ORG_ID,
1249 X_MODE );
1250 return;
1251 end if;
1252 close c1;
1253 UPDATE_ROW (
1254 X_ROWID,
1255 X_MILIT_SERVICE_ID,
1256 X_PERSON_ID,
1257 X_START_DATE,
1258 X_END_DATE,
1259 X_ATTRIBUTE_CATEGORY,
1260 X_ATTRIBUTE1,
1261 X_ATTRIBUTE2,
1262 X_ATTRIBUTE3,
1263 X_ATTRIBUTE4,
1264 X_ATTRIBUTE5,
1265 X_ATTRIBUTE6,
1266 X_ATTRIBUTE7,
1267 X_ATTRIBUTE8,
1268 X_ATTRIBUTE9,
1269 X_ATTRIBUTE10,
1270 X_ATTRIBUTE11,
1271 X_ATTRIBUTE12,
1272 X_ATTRIBUTE13,
1273 X_ATTRIBUTE14,
1274 X_ATTRIBUTE15,
1275 X_ATTRIBUTE16,
1276 X_ATTRIBUTE17,
1277 X_ATTRIBUTE18,
1278 X_ATTRIBUTE19,
1279 X_ATTRIBUTE20,
1280 X_MILITARY_TYPE_CD,
1281 X_SEPARATION_TYPE_CD,
1282 X_ASSISTANCE_TYPE_CD,
1283 X_ASSISTANCE_STATUS_CD,
1284 X_MODE );
1285 end ADD_ROW;
1286
1287 procedure DELETE_ROW (
1288 X_ROWID in VARCHAR2,
1289 x_mode IN VARCHAR2
1290 ) AS
1291 /***********************************************************
1292
1293 Created By : vvaitla
1294
1295 Date Created By : 2000/05/10
1296
1297 Purpose : To update,insert, add rows
1298
1299 Know limitations, enhancements or remarks
1300
1301 Change History
1302
1303 Who When What
1304
1305 ****************************************************************/
1306 begin
1307 Before_DML (
1308 p_action => 'DELETE',
1309 x_rowid => X_ROWID
1310 );
1311 IF (x_mode = 'S') THEN
1312 igs_sc_gen_001.set_ctx('R');
1313 END IF;
1314 delete from IGS_PE_MIL_SERVICES_ALL
1315 where ROWID = X_ROWID;
1316 if (sql%notfound) then
1317 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1318 igs_ge_msg_stack.add;
1319 igs_sc_gen_001.unset_ctx('R');
1320 app_exception.raise_exception;
1321 end if;
1322 IF (x_mode = 'S') THEN
1323 igs_sc_gen_001.unset_ctx('R');
1324 END IF;
1325
1326 After_DML (
1327 p_action => 'DELETE',
1328 x_rowid => X_ROWID
1329 );
1330 end DELETE_ROW;
1331 END igs_pe_mil_services_pkg;