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