[Home] [Help]
PACKAGE BODY: APPS.BEN_BENEFICIARIES_PKG
Source
1 PACKAGE BODY BEN_BENEFICIARIES_PKG as
2 /* $Header: pebbe01t.pkb 115.0 99/07/17 18:46:20 porting ship $ */
3
4 ------------------------ Insert_Row ----------------------------
5
6 PROCEDURE Insert_Row(P_Rowid IN OUT VARCHAR2,
7 P_Beneficiary_Id IN OUT NUMBER,
8 P_Source_Type VARCHAR2,
9 P_Source_Id NUMBER,
10 P_Element_Entry_Id NUMBER,
11 P_Effective_Start_Date DATE,
12 P_Effective_End_Date DATE,
13 P_Benefit_Level VARCHAR2,
14 P_Proportion NUMBER ) IS
15 --
16 CURSOR C IS
17 SELECT rowid FROM ben_beneficiaries_f
18 WHERE beneficiary_id = P_Beneficiary_Id
19 AND element_entry_id = P_Element_Entry_Id;
20 --
21 CURSOR C2 IS
22 SELECT ben_beneficiaries_s.nextval
23 FROM sys.dual;
24
25 --
26 --
27 BEGIN
28 --
29 --
30 if (P_Beneficiary_Id is NULL) then
31 OPEN C2;
32 FETCH C2 INTO P_Beneficiary_Id;
33 CLOSE C2;
34 end if;
35 --
36 --
37 INSERT INTO ben_beneficiaries_f(
38 beneficiary_id,
39 source_type,
40 source_id,
41 element_entry_id,
42 effective_start_date,
43 effective_end_date,
44 benefit_level,
45 proportion
46 )
47 VALUES (
48 P_Beneficiary_Id,
49 P_Source_Type,
50 P_Source_Id,
51 P_Element_Entry_Id,
52 P_Effective_Start_Date,
53 P_Effective_End_Date,
54 P_Benefit_Level,
55 P_Proportion
56 );
57 --
58 --
59 OPEN C;
60 --
61 FETCH C INTO P_Rowid;
62 --
63 if (C%NOTFOUND) then
64 CLOSE C;
65 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
66 hr_utility.set_message_token('PROCEDURE','INSERT_ROW');
67 hr_utility.set_message_token('STEP','1');
68 hr_utility.raise_error;
69 end if;
70 --
71 CLOSE C;
72 --
73 END Insert_Row;
74 --
75 ------------------------ Lock_Row ----------------------------
76 --
77 PROCEDURE Lock_Row(P_Rowid VARCHAR2,
78 P_Beneficiary_Id NUMBER,
79 P_Source_Type VARCHAR2,
80 P_Source_Id NUMBER,
81 P_Element_Entry_Id NUMBER,
82 P_Effective_Start_Date DATE,
83 P_Effective_End_Date DATE,
84 P_Benefit_Level VARCHAR2,
85 P_Proportion NUMBER
86 ) IS
87 CURSOR C IS
88 SELECT *
89 FROM ben_beneficiaries_f
90 WHERE rowid = P_Rowid
91 FOR UPDATE of Beneficiary_Id NOWAIT;
92 --
93 Recinfo C%ROWTYPE;
94 --
95 --
96 BEGIN
97 --
98 OPEN C;
99 --
100 FETCH C INTO Recinfo;
101 --
102 if (C%NOTFOUND) then
103 CLOSE C;
104 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
105 hr_utility.set_message_token('PROCEDURE','LOCK_ROW');
106 hr_utility.set_message_token('STEP','1');
107 hr_utility.raise_error;
108 end if;
109 --
110 CLOSE C;
111 --
112 if ( ( (Recinfo.beneficiary_id = P_Beneficiary_Id)
113 OR ( (Recinfo.beneficiary_id IS NULL)
114 AND (P_Beneficiary_Id IS NULL)))
115 AND ( (Recinfo.source_type = P_Source_Type)
116 OR ( (Recinfo.source_type IS NULL)
117 AND (P_Source_Type IS NULL)))
118 AND ( (Recinfo.source_id = P_Source_Id)
119 OR ( (Recinfo.source_id IS NULL)
120 AND (P_Source_Id IS NULL)))
121 AND ( (Recinfo.element_entry_id = P_Element_Entry_Id)
122 OR ( (Recinfo.element_entry_id IS NULL)
123 AND (P_Element_Entry_Id IS NULL)))
124 AND ( (Recinfo.effective_start_date = P_Effective_Start_Date)
125 OR ( (Recinfo.effective_start_date IS NULL)
126 AND (P_Effective_Start_Date IS NULL)))
127 AND ( (Recinfo.effective_end_date = P_Effective_End_Date)
128 OR ( (Recinfo.effective_end_date IS NULL)
129 AND (P_Effective_End_Date IS NULL)))
130 AND ( (Recinfo.benefit_level = P_Benefit_Level)
131 OR ( (Recinfo.benefit_level IS NULL)
132 AND (P_Benefit_Level IS NULL)))
133 AND ( (Recinfo.proportion = P_Proportion)
134 OR ( (Recinfo.proportion IS NULL)
135 AND (P_Proportion IS NULL)))
136 ) then
137 return;
138 --
139 else
140 --
141 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
142 APP_EXCEPTION.Raise_Exception;
143 --
144 end if;
145 --
146 --
147 END Lock_Row;
148 --
149 ------------------------ Update_Row ----------------------------
150 --
151 PROCEDURE Update_Row(P_Rowid VARCHAR2,
152 P_Beneficiary_Id NUMBER,
153 P_Source_Type VARCHAR2,
154 P_Source_Id NUMBER,
155 P_Element_Entry_Id NUMBER,
156 P_Effective_Start_Date DATE,
157 P_Effective_End_Date DATE,
158 P_Benefit_Level VARCHAR2,
159 P_Proportion NUMBER
160 ) IS
161 BEGIN
162 --
163 UPDATE ben_beneficiaries_f
164 SET
165 beneficiary_id = P_Beneficiary_Id,
166 source_type = P_Source_Type,
167 source_id = P_Source_Id,
168 element_entry_id = P_Element_Entry_Id,
169 effective_start_date = P_Effective_Start_Date,
170 effective_end_date = P_Effective_End_Date,
171 benefit_level = P_Benefit_Level,
172 proportion = P_Proportion
173 WHERE rowid = P_Rowid;
174 --
175 --
176 if (SQL%NOTFOUND) then
177 --
178 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
179 hr_utility.set_message_token('PROCEDURE','UPDATE_ROW');
180 hr_utility.set_message_token('STEP','20');
181 hr_utility.raise_error;
182 --
183 end if;
184 --
185 END Update_Row;
186 --
187 ------------------------ Delete_Row ----------------------------
188
189 PROCEDURE Delete_Row(P_Rowid VARCHAR2) IS
190 BEGIN
191 DELETE FROM ben_beneficiaries_f
192 WHERE rowid = P_Rowid;
193
194 if (SQL%NOTFOUND) then
195 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
196 hr_utility.set_message_token('PROCEDURE','DELETE_ROW');
197 hr_utility.set_message_token('STEP','1');
198 hr_utility.raise_error;
199 end if;
200 END Delete_Row;
201
202 ----------------------- Test_Path_To_PERBEBEN ------------------
203 PROCEDURE Test_Path_To_PERBEBEN (P_Element_Entry_Id NUMBER) is
204 --
205 cursor c_chk_asg_benefit is
206 select 'OK'
207 from
208 ben_benefit_classifications ben,
209 pay_element_types_f ele,
210 fnd_sessions fnd,
211 per_assignments_f asg,
212 pay_element_links_f link,
213 pay_element_entries_f ent
214 where
215 ent.element_entry_id = P_element_entry_id
216 and ent.assignment_id = asg.assignment_id
217 and asg.primary_flag = 'Y'
218 and ent.element_link_id = link.element_link_id
219 and link.element_type_id = ele.element_type_id
220 and ele.benefit_classification_id = ben.benefit_classification_id
221 and ben.beneficiary_allowed_flag = 'Y'
222 and fnd.session_id = userenv('sessionid')
223 and
224 fnd.effective_date between ent.effective_start_date
225 and ent.effective_end_date
226 and fnd.effective_date between ele.effective_start_date
227 and ele.effective_end_date
228 and fnd.effective_date between link.effective_start_date
229 and link.effective_end_date
230 and fnd.effective_date between asg.effective_start_date
231 and asg.effective_end_date;
232 --
233 l_deps_allowed_for_primary_asg varchar2(20);
234
235 BEGIN
236
237 open c_chk_asg_benefit;
238
239 fetch c_chk_asg_benefit into l_deps_allowed_for_primary_asg;
240
241 if c_chk_asg_benefit%notfound then
242 close c_chk_asg_benefit;
243 hr_utility.set_message(801, 'PAY_7981_BEN_FORM_NOT_NAV');
244 hr_utility.raise_error;
245 end if;
246
247 close c_chk_asg_benefit;
248
249 END Test_Path_To_PERBEBEN;
250
251 END BEN_BENEFICIARIES_PKG;