1 PACKAGE BODY igs_en_val_pig AS
2 /* $Header: IGSEN98B.pls 115.5 2003/07/08 10:42:19 ptandon noship $ */
3
4 FUNCTION enrf_get_pig_cp (p_person_id IN NUMBER,
5 p_which_cp IN VARCHAR2,
6 p_message OUT NOCOPY VARCHAR2)
7 RETURN NUMBER IS
8 ------------------------------------------------------------------
9 --Created by : Kiran Killamsetty, Oracle IDC
10 --Date created: 01-NOV-2002
11 --
12 --Purpose: Gets the min/max cp for a person at person id group level
13 --
14 --
15 --Known limitations/enhancements and/or remarks:
16 --
17 --Change History:
18 --Who When What
19 --amuthu 02-JAN-2003 There was a check to see if the step
20 -- was setup at PIG level and only then
21 -- the value would be returned. Changed
22 -- this to return the value irrespective
23 -- of the step being setup at PIG level
24 --ptandon 07-JUL-2003 Modified the cursor cur_pig to select
25 -- only those Person ID Groups for which
26 -- the person has not been end-dated.
27 -- Bug# 3038825
28 -------------------------------------------------------------------
29 CURSOR cur_pig IS SELECT DISTINCT group_id
30 FROM igs_pe_prsid_grp_mem
31 WHERE person_id = p_person_id AND
32 TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date,TRUNC(SYSDATE));
33 CURSOR cur_cp (cp_group_id igs_pe_prsid_grp_mem.group_id%TYPE)
34 IS SELECT prsid_max_cp,prsid_min_cp FROM igs_en_pig_cp_setup
35 WHERE group_id = cp_group_id;
36 rec_cp cur_cp%ROWTYPE;
37 l_count NUMBER;
38 l_ret_value NUMBER DEFAULT NULL;
39 l_cp_defined BOOLEAN;
40 BEGIN
41 p_message := NULL;
42 --Validating the input parameters.
43 IF p_person_id IS NULL OR
44 p_which_cp NOT IN ('MAX_CP','MIN_CP') THEN
45 RETURN NULL;
46 END IF;
47
48 l_count := 0;
49 --Getting the group id's for the given persons
50 FOR rec_pig IN cur_pig
51 LOOP
52
53 l_cp_defined := FALSE;
54 --Checking whether step defined for given person group.
55 OPEN cur_cp(rec_pig.group_id);
56 FETCH cur_cp INTO rec_cp;
57 IF cur_cp%FOUND THEN
58 l_cp_defined := TRUE;
59 IF p_which_cp = 'MAX_CP' THEN
60 l_ret_value := rec_cp.prsid_max_cp;
61 ELSE
62 l_ret_value := rec_cp.prsid_min_cp;
63 END IF;
64 END IF;
65 CLOSE cur_cp;
66
67 --if step is defined more than one group id;
68 IF l_cp_defined THEN
69 l_count := l_count +1;
70 IF l_count >1 THEN
71 p_message := 'IGS_EN_MORE_PIG_SETUP';
72 RETURN NULL;
73 END IF;
74 END IF;
75
76 END LOOP;
77 RETURN l_ret_value;
78 EXCEPTION
79 WHEN OTHERS THEN
80 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
81 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_val_pig.enrf_get_pig_cp');
82 IGS_GE_MSG_STACK.ADD;
83 App_Exception.Raise_Exception;
84 END enrf_get_pig_cp;
85
86 FUNCTION get_pig_notify_flag (p_step_type IN VARCHAR2,
87 p_person_id IN NUMBER,
88 p_message OUT NOCOPY VARCHAR2)
89 RETURN VARCHAR2 IS
90 ------------------------------------------------------------------
91 --Created by : Kiran Killamsetty, Oracle IDC
92 --Date created: 01-NOV-2002
93 --
94 --Purpose: Returns the step notification flag for a person at
95 -- person id group level.
96 --
97 --Known limitations/enhancements and/or remarks:
98 --
99 --Change History:
100 --Who When What
101 --ptandon 07-JUL-2003 Modified the cursor cur_pig to select
102 -- only those Person ID Groups for which
103 -- the person has not been end-dated.
104 -- Bug# 3038825
105 -------------------------------------------------------------------
106 CURSOR cur_pig IS SELECT DISTINCT group_id
107 FROM igs_pe_prsid_grp_mem
108 WHERE person_id = p_person_id AND
109 TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date,TRUNC(SYSDATE));
110 CURSOR cur_step(p_group_id igs_pe_prsid_grp_mem.group_id%TYPE)
111 IS SELECT s_enrolment_step_type,notification_flag FROM igs_en_pig_s_setup
112 WHERE group_id = p_group_id;
113 l_count NUMBER:= 0;
114 l_ret_value NUMBER DEFAULT NULL;
115 l_notification_flag igs_en_pig_s_setup.notification_flag%TYPE;
116 l_defined_step BOOLEAN;
117 BEGIN
118 p_message := NULL;
119 --Validating the input parameters.
120 IF p_person_id IS NULL OR
121 p_step_type IS NULL THEN
122 RETURN NULL;
123 END IF;
124
125 --Getting the group id's for the given persons
126 FOR rec_pig IN cur_pig
127 LOOP
128 --Checking whether step defined for given person group.
129 l_defined_step := FALSE;
130 FOR rec_step IN cur_step(rec_pig.group_id)
131 LOOP
132 l_defined_step := TRUE;
133 IF rec_step.s_enrolment_step_type = p_step_type THEN
134 l_notification_flag:=rec_step.notification_flag;
135 EXIT; --cur_step
136 END IF;
137 END LOOP;
138
139 --if step is defined more than one group id;
140 IF l_defined_step THEN
141 l_count := l_count +1;
142 IF l_count >1 THEN
143 p_message := 'IGS_EN_MORE_PIG_SETUP';
144 RETURN NULL;
145 END IF;
146 END IF;
147
148 END LOOP;
149 RETURN l_notification_flag;
150 EXCEPTION
151 WHEN OTHERS THEN
152 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
153 FND_MESSAGE.SET_TOKEN('NAME', 'igs_en_val_pig.get_pig_notify_flag');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END get_pig_notify_flag;
157
158
159 END igs_en_val_pig;
[Home] [Help]