1 PACKAGE BODY igs_pe_vote_info_pkg AS
2 /* $Header: IGSNI50B.pls 120.3 2005/10/17 04:23:52 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_vote_info_all%RowType;
6 new_references igs_pe_vote_info_all%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_voter_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_voter_info IN VARCHAR2 DEFAULT NULL,
14 x_type_code IN VARCHAR2 DEFAULT NULL,
15 x_type_code_id IN NUMBER DEFAULT NULL,
16 x_voter_regn_st_date IN DATE DEFAULT NULL,
17 x_voter_regn_end_date IN DATE DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL,
23 x_org_id IN NUMBER DEFAULT NULL
24 ) AS
25
26 /*************************************************************
27 Created By : svenkata
28 Date Created By :15-MAY-200
29 Purpose : SETS 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_vote_info_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.voter_id := x_voter_id;
61 new_references.person_id := x_person_id;
62 new_references.voter_info := x_voter_info;
63 new_references.type_code := x_type_code;
64 new_references.type_code_id := x_type_code_id;
65 new_references.voter_regn_st_date := x_voter_regn_st_date;
66 new_references.voter_regn_end_date := x_voter_regn_end_date;
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74 new_references.last_update_date := x_last_update_date;
75 new_references.last_updated_by := x_last_updated_by;
76 new_references.last_update_login := x_last_update_login;
77 new_references.org_id := x_org_id;
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 : svenkata
86 Date Created By :15-MAY-200
87 Purpose : CHECKS CONSTRAINTS
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 : svenkata
110 Date Created By :15-MAY-200
111 Purpose : CHECKS FOR PARENT EXISTANCE
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
121 IF (((old_references.person_id = new_references.person_id)) OR
122 ((new_references.person_id IS NULL))) THEN
123 NULL;
124 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
125 new_references.person_id
126 ) THEN
127 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131
132 If NOT igs_lookups_view_pkg.get_pk_for_validation('VOTER_INFO' , new_references.voter_info) then
133 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 end if;
137
138
139 END Check_Parent_Existance;
140
141 FUNCTION Get_PK_For_Validation (
142 x_voter_id IN NUMBER
143 ) RETURN BOOLEAN AS
144
145 /*************************************************************
146 Created By : svenkata
147 Date Created By :15-MAY-200
148 Purpose : checks primary key
149 Know limitations, enhancements or remarks
150 Change History
151 Who When What
152
153 (reverse chronological order - newest change first)
154 ***************************************************************/
155
156 CURSOR cur_rowid IS
157 SELECT rowid
158 FROM igs_pe_vote_info_all
159 WHERE voter_id = x_voter_id
160 FOR UPDATE NOWAIT;
161
162 lv_rowid cur_rowid%RowType;
163
164 BEGIN
165
166 Open cur_rowid;
167 Fetch cur_rowid INTO lv_rowid;
168 IF (cur_rowid%FOUND) THEN
169 Close cur_rowid;
170 Return(TRUE);
171 ELSE
172 Close cur_rowid;
173 Return(FALSE);
174 END IF;
175 END Get_PK_For_Validation;
176
177 PROCEDURE Get_FK_Igs_Pe_Person (
178 x_person_id IN NUMBER
179 ) AS
180
181 /*************************************************************
182 Created By : svenkata
183 Date Created By :15-MAY-200
184 Purpose : CHECKS FOREIGN KEY
185 Know limitations, enhancements or remarks
186 Change History
187 Who When What
188
189 (reverse chronological order - newest change first)
190 ***************************************************************/
191
192 CURSOR cur_rowid IS
193 SELECT rowid
194 FROM igs_pe_vote_info_all
195 WHERE person_id = x_person_id ;
196
197 lv_rowid cur_rowid%RowType;
198
199 BEGIN
200
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_PPT_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_vote_info_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 lv_rowid cur_rowid%RowType;
241
242 BEGIN
243
244 IF new_references.voter_info='COUNTRY' THEN --Then type_code_id is null
245 Open cur_rowid(x_type_code,NULL);
246 ELSE
247 Open cur_rowid(NULL,x_type_code_id);
248 END IF;
249
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
263
264 PROCEDURE Check_Uniqueness AS
265 /*************************************************************
266 Created By :
267 Date Created By :
268 Purpose :
269 Know limitations, enhancements or remarks
270 Change History
271 Who When What
272
273 (reverse chronological order - newest change first)
274 ***************************************************************/
275
276 begin
277 IF Get_Uk_For_Validation (
278 new_references.person_id
279 ,new_references.type_code
280 ,new_references.type_code_id
281 ) THEN
282 Fnd_Message.Set_Name ('IGS', 'IGS_PE_VOTE_DUP_EXISTS');
283 IGS_GE_MSG_STACK.ADD;
284 app_exception.raise_exception;
285 END IF;
286 END Check_Uniqueness ;
287
288
289 PROCEDURE Before_DML (
290 p_action IN VARCHAR2,
291 x_rowid IN VARCHAR2 DEFAULT NULL,
292 x_voter_id IN NUMBER DEFAULT NULL,
293 x_person_id IN NUMBER DEFAULT NULL,
294 x_voter_info IN VARCHAR2 DEFAULT NULL,
295 x_type_code IN VARCHAR2 DEFAULT NULL,
296 x_type_code_id IN NUMBER DEFAULT NULL,
297 x_voter_regn_st_date IN DATE DEFAULT NULL,
298 x_voter_regn_end_date IN DATE DEFAULT NULL,
299 x_creation_date IN DATE DEFAULT NULL,
300 x_created_by IN NUMBER DEFAULT NULL,
301 x_last_update_date IN DATE DEFAULT NULL,
302 x_last_updated_by IN NUMBER DEFAULT NULL,
303 x_last_update_login IN NUMBER DEFAULT NULL,
304 x_org_id IN NUMBER DEFAULT NULL
305 ) AS
306 /*************************************************************
307 Created By :
308 Date Created By :
309 Purpose :
310 Know limitations, enhancements or remarks
311 Change History
312 Who When What
313
314 (reverse chronological order - newest change first)
315 ***************************************************************/
316
317 BEGIN
318
319 Set_Column_Values (
320 p_action,
321 x_rowid,
322 x_voter_id,
323 x_person_id,
324 x_voter_info,
325 x_type_code,
326 x_type_code_id,
327 x_voter_regn_st_date,
328 x_voter_regn_end_date,
329 x_creation_date,
330 x_created_by,
331 x_last_update_date,
332 x_last_updated_by,
333 x_last_update_login,
334 x_org_id
335 );
336
337 IF (p_action = 'INSERT') THEN
338 -- Call all the procedures related to Before Insert.
339 Null;
340 IF Get_Pk_For_Validation(
341 new_references.voter_id) THEN
342 Fnd_Message.Set_name('IGS','IGS_PE_VOTE_DUP_EXISTS');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 Check_Uniqueness;
347 Check_Constraints;
348 Check_Parent_Existance;
349 ELSIF (p_action = 'UPDATE') THEN
350 -- Call all the procedures related to Before Update.
351 Null;
352 Check_Uniqueness;
353 Check_Constraints;
354 Check_Parent_Existance;
355 ELSIF (p_action = 'DELETE') THEN
356 -- Call all the procedures related to Before Delete.
357 Null;
358 ELSIF (p_action = 'VALIDATE_INSERT') THEN
359 -- Call all the procedures related to Before Insert.
360 IF Get_PK_For_Validation (
361 new_references.voter_id) THEN
362 Fnd_Message.Set_name('IGS','IGS_PE_VOTE_DUP_EXISTS');
363 IGS_GE_MSG_STACK.ADD;
364 App_Exception.Raise_Exception;
365 END IF;
366 Check_Uniqueness;
367 Check_Constraints;
368 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
369 Check_Uniqueness;
370 Check_Constraints;
371 ELSIF (p_action = 'VALIDATE_DELETE') THEN
372 Null;
373 END IF;
374
375 END Before_DML;
376
377
378
379
380
381 PROCEDURE After_DML (
382 p_action IN VARCHAR2,
383 x_rowid IN VARCHAR2
384 ) IS
385 /*************************************************************
386 Created By : svenkata
387 Date Created By :15-MAY-200
388 Purpose : DOES AFTER DML VALIDATIONS
389 Know limitations, enhancements or remarks
390 Change History
391 Who When What
392
393 (reverse chronological order - newest change first)
394 ***************************************************************/
395
396 BEGIN
397
398 l_rowid := x_rowid;
399
400 IF (p_action = 'INSERT') THEN
401 -- Call all the procedures related to After Insert.
402 Null;
403 ELSIF (p_action = 'UPDATE') THEN
404 -- Call all the procedures related to After Update.
405 Null;
406 ELSIF (p_action = 'DELETE') THEN
407 -- Call all the procedures related to After Delete.
408 Null;
409 END IF;
410
411 END After_DML;
412
413 procedure INSERT_ROW (
414 X_ROWID in out NOCOPY VARCHAR2,
415 x_VOTER_ID IN OUT NOCOPY NUMBER,
416 x_PERSON_ID IN NUMBER,
417 x_VOTER_INFO IN VARCHAR2,
418 x_TYPE_CODE IN VARCHAR2,
419 x_VOTER_REGN_ST_DATE IN DATE,
420 x_VOTER_REGN_END_DATE IN DATE,
421 X_MODE in VARCHAR2 default 'R',
422 X_ORG_ID in NUMBER ,
423 x_TYPE_CODE_ID IN NUMBER DEFAULT NULL
424 ) AS
425 /*************************************************************
426 Created By : svenkata
427 Date Created By :15-MAY-200
428 Purpose : INSERTS A ROW
429 Know limitations, enhancements or remarks
430 Change History
431 Who When What
432
433 (reverse chronological order - newest change first)
434 ***************************************************************/
435
436 cursor C is select ROWID from igs_pe_vote_info_all
437 where VOTER_ID= X_VOTER_ID;
438 X_LAST_UPDATE_DATE DATE ;
439 X_LAST_UPDATED_BY NUMBER ;
440 X_LAST_UPDATE_LOGIN NUMBER ;
441 l_TYPE_CODE igs_pe_vote_info_all.type_code%TYPE;
442 l_TYPE_CODE_ID igs_pe_vote_info_all.type_code_id%TYPE;
443
444 begin
445 X_LAST_UPDATE_DATE := SYSDATE;
446 if(X_MODE = 'I') then
447 X_LAST_UPDATED_BY := 1;
448 X_LAST_UPDATE_LOGIN := 0;
449 elsif (X_MODE IN ('R', 'S')) then
450 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
451 if X_LAST_UPDATED_BY is NULL then
452 X_LAST_UPDATED_BY := -1;
453 end if;
454 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
455 if X_LAST_UPDATE_LOGIN is NULL then
456 X_LAST_UPDATE_LOGIN := -1;
457 end if;
458 else
459 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
460 IGS_GE_MSG_STACK.ADD;
461 app_exception.raise_exception;
462 end if;
463
464 SELECT IGS_PE_VOTE_INFO_S.NEXTVAL
465 INTO x_voter_id
466 FROM DUAL;
467
468 --l_type_code := x_type_code;
469 --l_type_code_id := x_type_code_id;
470
471 IF (x_VOTER_INFO='COUNTRY') THEN
472 l_TYPE_CODE_ID := NULL;
473 l_type_code := x_type_code;
474 ELSE
475 l_TYPE_CODE := NULL;
476 l_type_code_id := x_type_code_id;
477 END IF;
478
479 Before_DML(
480 p_action=>'INSERT',
481 x_rowid=>X_ROWID,
482 x_voter_id=>X_VOTER_ID,
483 x_person_id=>X_PERSON_ID,
484 x_voter_info=>X_VOTER_INFO,
485 x_type_code=>l_TYPE_CODE,
486 x_type_code_id=>l_TYPE_CODE_ID,
487 x_voter_regn_st_date=>X_VOTER_REGN_ST_DATE,
488 x_voter_regn_end_date=>X_VOTER_REGN_END_DATE,
489 x_creation_date=>X_LAST_UPDATE_DATE,
490 x_created_by=>X_LAST_UPDATED_BY,
491 x_last_update_date=>X_LAST_UPDATE_DATE,
492 x_last_updated_by=>X_LAST_UPDATED_BY,
493 x_last_update_login=>X_LAST_UPDATE_LOGIN,
494 x_org_id=>igs_ge_gen_003.get_org_id
495 );
496 IF (x_mode = 'S') THEN
497 igs_sc_gen_001.set_ctx('R');
498 END IF;
499 insert into igs_pe_vote_info_all (
500 VOTER_ID
501 ,PERSON_ID
502 ,VOTER_INFO
503 ,TYPE_CODE
504 ,TYPE_CODE_ID
505 ,VOTER_REGN_ST_DATE
506 ,VOTER_REGN_END_DATE
507 ,CREATION_DATE
508 ,CREATED_BY
509 ,LAST_UPDATE_DATE
510 ,LAST_UPDATED_BY
511 ,LAST_UPDATE_LOGIN
512 ,ORG_ID
513 ) values (
514 NEW_REFERENCES.VOTER_ID
515 ,NEW_REFERENCES.PERSON_ID
516 ,NEW_REFERENCES.VOTER_INFO
517 ,NEW_REFERENCES.TYPE_CODE
518 ,NEW_REFERENCES.TYPE_CODE_ID
519 ,NEW_REFERENCES.VOTER_REGN_ST_DATE
520 ,NEW_REFERENCES.VOTER_REGN_END_DATE
521 ,X_LAST_UPDATE_DATE
522 ,X_LAST_UPDATED_BY
523 ,X_LAST_UPDATE_DATE
524 ,X_LAST_UPDATED_BY
525 ,X_LAST_UPDATE_LOGIN
526 ,NEW_REFERENCES.ORG_ID
527 );
528 IF (x_mode = 'S') THEN
529 igs_sc_gen_001.unset_ctx('R');
530 END IF;
531
532 open c;
533 fetch c into X_ROWID;
534 if (c%notfound) then
535 close c;
536 raise no_data_found;
537 end if;
538 close c;
539 After_DML (
540 p_action => 'INSERT' ,
541 x_rowid => X_ROWID );
542 EXCEPTION
543 WHEN OTHERS THEN
544 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
545 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
546 fnd_message.set_token ('ERR_CD', SQLCODE);
547 igs_ge_msg_stack.add;
548 igs_sc_gen_001.unset_ctx('R');
549 app_exception.raise_exception;
550 ELSE
551 igs_sc_gen_001.unset_ctx('R');
552 RAISE;
553 END IF;
554
555 end INSERT_ROW;
556
557 procedure LOCK_ROW (
558 X_ROWID in VARCHAR2,
559 x_VOTER_ID IN NUMBER,
560 x_PERSON_ID IN NUMBER,
561 x_VOTER_INFO IN VARCHAR2,
562 x_TYPE_CODE IN VARCHAR2,
563 x_VOTER_REGN_ST_DATE IN DATE,
564 x_VOTER_REGN_END_DATE IN DATE ,
565 x_TYPE_CODE_ID IN NUMBER DEFAULT NULL
566 ) AS
567 /*************************************************************
568 Created By : svenkata
569 Date Created By :15-MAY-200
570 Purpose : LOCKS A ROW
571 Know limitations, enhancements or remarks
572 Change History
573 Who When What
574 skpandey 08-SEP-2005 Bug: 4593149
575 Description: Modified lock_row parameters
576 (reverse chronological order - newest change first)
577 ***************************************************************/
578
579 cursor c1 is select
580 PERSON_ID
581 , VOTER_INFO
582 , TYPE_CODE
583 , TYPE_CODE_ID
584 , VOTER_REGN_ST_DATE
585 , VOTER_REGN_END_DATE
586 from igs_pe_vote_info_all
587 where ROWID = X_ROWID
588 for update nowait;
589 tlinfo c1%rowtype;
590
591 l_TYPE_CODE igs_pe_vote_info_all.type_code%TYPE;
592 l_TYPE_CODE_ID igs_pe_vote_info_all.type_code_id%TYPE;
593
594 BEGIN
595 open c1;
596 fetch c1 into tlinfo;
597 if (c1%notfound) then
598 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
599 IGS_GE_MSG_STACK.ADD;
600 close c1;
601 app_exception.raise_exception;
602 return;
603 end if;
604 close c1;
605 --skpandey. Bug: 4593149
606 IF (x_VOTER_INFO='COUNTRY') THEN
607 l_TYPE_CODE_ID := NULL;
608 l_type_code := x_type_code;
609 ELSE
610 l_TYPE_CODE := NULL;
611 l_type_code_id := x_type_code_id;
612 END IF;
613
614 IF ( ( tlinfo.PERSON_ID = X_PERSON_ID)
615 AND (tlinfo.VOTER_INFO = X_VOTER_INFO)
616 AND (NVL(tlinfo.TYPE_CODE,' ') = NVL(l_type_code,' '))
617 AND (NVL(tlinfo.TYPE_CODE_ID,-1) = NVL(l_TYPE_CODE_ID,-1))
618 AND ((tlinfo.VOTER_REGN_ST_DATE = X_VOTER_REGN_ST_DATE)
619 OR ((tlinfo.VOTER_REGN_ST_DATE is null)
620 AND (X_VOTER_REGN_ST_DATE is null)))
621 AND ((tlinfo.VOTER_REGN_END_DATE = X_VOTER_REGN_END_DATE)
622 OR ((tlinfo.VOTER_REGN_END_DATE is null)
623 AND (X_VOTER_REGN_END_DATE is null)))
624 ) THEN
625 NULL;
626 ELSE
627 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
628 IGS_GE_MSG_STACK.ADD;
629 app_exception.raise_exception;
630 END IF;
631 RETURN;
632 END LOCK_ROW;
633
634 Procedure UPDATE_ROW (
635 X_ROWID in VARCHAR2,
636 x_VOTER_ID IN NUMBER,
637 x_PERSON_ID IN NUMBER,
638 x_VOTER_INFO IN VARCHAR2,
639 x_TYPE_CODE IN VARCHAR2,
640 x_VOTER_REGN_ST_DATE IN DATE,
641 x_VOTER_REGN_END_DATE IN DATE,
642 X_MODE in VARCHAR2 default 'R' ,
643 x_TYPE_CODE_ID IN NUMBER DEFAULT NULL
644 ) AS
645 /*************************************************************
646 Created By : svenkata
647 Date Created By :15-MAY-200
648 Purpose : UPDATES A ROW
649 Know limitations, enhancements or remarks
650 Change History
651 Who When What
652
653 (reverse chronological order - newest change first)
654 ***************************************************************/
655
656 X_LAST_UPDATE_DATE DATE ;
657 X_LAST_UPDATED_BY NUMBER ;
658 X_LAST_UPDATE_LOGIN NUMBER ;
659 l_TYPE_CODE igs_pe_vote_info_all.type_code%TYPE;
660 l_TYPE_CODE_ID igs_pe_vote_info_all.type_code_id%TYPE;
661
662 begin
663 X_LAST_UPDATE_DATE := SYSDATE;
664 if(X_MODE = 'I') then
665 X_LAST_UPDATED_BY := 1;
666 X_LAST_UPDATE_LOGIN := 0;
667 elsif (X_MODE IN ('R', 'S')) then
668 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
669 if X_LAST_UPDATED_BY is NULL then
670 X_LAST_UPDATED_BY := -1;
671 end if;
672 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
673 if X_LAST_UPDATE_LOGIN is NULL then
674 X_LAST_UPDATE_LOGIN := -1;
675 end if;
676 else
677 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
678 IGS_GE_MSG_STACK.ADD;
679 app_exception.raise_exception;
680 end if;
681
682 IF (x_VOTER_INFO='COUNTRY') THEN
683 l_TYPE_CODE_ID := NULL;
684 l_type_code := x_type_code;
685 ELSE
686 l_TYPE_CODE := NULL;
687 l_type_code_id := x_type_code_id;
688 END IF;
689
690 Before_DML(
691 p_action=>'UPDATE',
692 x_rowid=>X_ROWID,
693 x_voter_id=>X_VOTER_ID,
694 x_person_id=>X_PERSON_ID,
695 x_voter_info=>X_VOTER_INFO,
696 x_type_code=>l_TYPE_CODE,
697 x_type_code_id=>l_TYPE_CODE_ID,
698 x_voter_regn_st_date=>X_VOTER_REGN_ST_DATE,
699 x_voter_regn_end_date=>X_VOTER_REGN_END_DATE,
700 x_creation_date=>X_LAST_UPDATE_DATE,
701 x_created_by=>X_LAST_UPDATED_BY,
702 x_last_update_date=>X_LAST_UPDATE_DATE,
703 x_last_updated_by=>X_LAST_UPDATED_BY,
704 x_last_update_login=>X_LAST_UPDATE_LOGIN);
705 IF (x_mode = 'S') THEN
706 igs_sc_gen_001.set_ctx('R');
707 END IF;
708 update igs_pe_vote_info_all set
709 PERSON_ID = NEW_REFERENCES.PERSON_ID,
710 VOTER_INFO = NEW_REFERENCES.VOTER_INFO,
711 TYPE_CODE = NEW_REFERENCES.TYPE_CODE,
712 TYPE_CODE_ID = NEW_REFERENCES.TYPE_CODE_ID,
713 VOTER_REGN_ST_DATE = NEW_REFERENCES.VOTER_REGN_ST_DATE,
714 VOTER_REGN_END_DATE = NEW_REFERENCES.VOTER_REGN_END_DATE,
715 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
716 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
717 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
718 where ROWID = X_ROWID;
719 if (sql%notfound) then
720 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
721 igs_ge_msg_stack.add;
722 igs_sc_gen_001.unset_ctx('R');
723 app_exception.raise_exception;
724 end if;
725 IF (x_mode = 'S') THEN
726 igs_sc_gen_001.unset_ctx('R');
727 END IF;
728
729
730 After_DML (
731 p_action => 'UPDATE' ,
732 x_rowid => X_ROWID
733 );
734 EXCEPTION
735 WHEN OTHERS THEN
736 IF (SQLCODE = (-28115)) THEN
737 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
738 fnd_message.set_token ('ERR_CD', SQLCODE);
739 igs_ge_msg_stack.add;
740 igs_sc_gen_001.unset_ctx('R');
741 app_exception.raise_exception;
742 ELSE
743 igs_sc_gen_001.unset_ctx('R');
744 RAISE;
745 END IF;
746
747 end UPDATE_ROW;
748
749 procedure ADD_ROW (
750 X_ROWID in out NOCOPY VARCHAR2,
751 x_VOTER_ID IN OUT NOCOPY NUMBER,
752 x_PERSON_ID IN NUMBER,
753 x_VOTER_INFO IN VARCHAR2,
754 x_TYPE_CODE IN VARCHAR2,
755 x_VOTER_REGN_ST_DATE IN DATE,
756 x_VOTER_REGN_END_DATE IN DATE,
757 X_MODE in VARCHAR2 default 'R',
758 X_ORG_ID in NUMBER ,
759 x_TYPE_CODE_ID IN NUMBER DEFAULT NULL
760 ) AS
761 /*************************************************************
762 Created By : svenkata
763 Date Created By :15-MAY-200
764 Purpose : ADDS A ROW
765 Know limitations, enhancements or remarks
766 Change History
767 Who When What
768
769 (reverse chronological order - newest change first)
770 ***************************************************************/
771
772 cursor c1 is select ROWID from igs_pe_vote_info_all
773 where VOTER_ID= X_VOTER_ID;
774 begin
775 open c1;
776 fetch c1 into X_ROWID;
777 if (c1%notfound) then
778 close c1;
779 INSERT_ROW (
780 X_ROWID,
781 X_VOTER_ID,
782 X_PERSON_ID,
783 X_VOTER_INFO,
784 X_TYPE_CODE,
785 X_VOTER_REGN_ST_DATE,
786 X_VOTER_REGN_END_DATE,
787 X_MODE,
788 X_ORG_ID, X_TYPE_CODE_ID );
789 return;
790 end if;
791 close c1;
792 UPDATE_ROW (
793 X_ROWID,
794 X_VOTER_ID,
795 X_PERSON_ID,
796 X_VOTER_INFO,
797 X_TYPE_CODE,
798 X_VOTER_REGN_ST_DATE,
799 X_VOTER_REGN_END_DATE,
800 X_MODE , X_TYPE_CODE_ID);
801 end ADD_ROW;
802
803 procedure DELETE_ROW (
804 X_ROWID in VARCHAR2,
805 x_mode IN VARCHAR2
806 ) AS
807 /*************************************************************
808 Created By : svenkata
809 Date Created By :15-MAY-200
810 Purpose : DELETES A ROW
811 Know limitations, enhancements or remarks
812 Change History
813 Who When What
814
815 (reverse chronological order - newest change first)
816 ***************************************************************/
817
818 begin
819 Before_DML (
820 p_action => 'DELETE',
821 x_rowid => X_ROWID
822 );
823 IF (x_mode = 'S') THEN
824 igs_sc_gen_001.set_ctx('R');
825 END IF;
826 delete from igs_pe_vote_info_all
827 where ROWID = X_ROWID;
828 if (sql%notfound) then
829 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
830 igs_ge_msg_stack.add;
831 igs_sc_gen_001.unset_ctx('R');
832 app_exception.raise_exception;
833 end if;
834 IF (x_mode = 'S') THEN
835 igs_sc_gen_001.unset_ctx('R');
836 END IF;
837
838 After_DML (
839 p_action => 'DELETE',
840 x_rowid => X_ROWID
841 );
842 end DELETE_ROW;
843 END igs_pe_vote_info_pkg;
[Home] [Help]