[Home] [Help]
PACKAGE BODY: APPS.PER_IN_ENTRY_VALUE_LEG_HOOK
Source
1 PACKAGE BODY per_in_entry_value_leg_hook AS
2 /* $Header: peinlhee.pkb 120.6 2008/03/14 13:41:43 rsaharay ship $ */
3 --
4 -- Globals
5 --
6 g_package CONSTANT VARCHAR2(100) := 'per_in_entry_value_leg_hook.' ;
7 g_debug BOOLEAN ;
8 g_message_name VARCHAR2(30);
9 g_token_name pay_in_utils.char_tab_type;
10 g_token_value pay_in_utils.char_tab_type;
11
12 --------------------------------------------------------------------------
13 -- --
14 -- Name : CHECK_ENTRY_VALUE_INT --
15 -- Type : PROCEDURE --
16 -- Access : Private --
17 -- Description : Internal Procedure for IN localization --
18 -- Parameters : --
19 -- IN : p_effective_date DATE --
20 -- p_element_entry_id NUMBER --
21 -- p_effective_start_date DATE --
22 -- p_effective_end_date DATE --
23 -- p_calling_procedure VARCHAR2 --
24 -- OUT: --
25 -- p_message_name VARCHAR2 --
26 -- p_token_name PL/SQL table --
27 -- p_token_value PL/SQL table --
28 -- --
29 -- Change History : --
30 --------------------------------------------------------------------------
31 -- Rev# Date Userid Bug Description --
32 --------------------------------------------------------------------------
33 -- 1.0 07-Oct-04 lnagaraj 3866814 Created this procedure --
34 -- 1.1 17-Nov-04 aaagarwa 3997410 Added call to check_gratuity --
35 --------------------------------------------------------------------------
36 PROCEDURE check_entry_value_int(p_effective_date IN DATE
37 ,p_element_entry_id IN NUMBER
38 ,p_effective_start_date IN DATE
39 ,p_effective_end_date IN DATE
40 ,p_calling_procedure IN VARCHAR2
41 ,p_message_name OUT NOCOPY VARCHAR2
42 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
43 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
44 )
45 IS
46 l_procedure VARCHAR2(100);
47 l_message VARCHAR2(250);
48 BEGIN
49 l_procedure := g_package||'check_entry_value_int';
50 g_debug := hr_utility.debug_enabled;
51 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
52
53 IF g_debug THEN
54 pay_in_utils.trace('**************************************************','********************');
55 pay_in_utils.trace('p_effective_date ',p_effective_date );
56 pay_in_utils.trace('p_element_entry_id ',p_element_entry_id );
57 pay_in_utils.trace('p_effective_start_date',p_effective_start_date);
58 pay_in_utils.trace('p_effective_end_date ',p_effective_end_date );
59 pay_in_utils.trace('p_calling_procedure ',p_calling_procedure );
60 pay_in_utils.trace('**************************************************','********************');
61 END IF;
62
63 pay_in_utils.null_message(g_token_name, g_token_value);
64 g_message_name := 'SUCCESS';
65
66 --
67 -- Code for Checking element entries
68 --
69 per_in_perquisite_pkg.check_element_entry
70 (p_effective_date => p_effective_date
71 ,p_element_entry_id => p_element_entry_id
72 ,p_effective_start_date => p_effective_start_date
73 ,p_effective_end_date => p_effective_end_date
74 ,p_calling_procedure => p_calling_procedure
75 ,p_message_name => g_message_name
76 ,p_token_name => g_token_name
77 ,p_token_value => g_token_value);
78
79 pay_in_utils.set_location(g_debug,l_procedure,20);
80 IF g_message_name <> 'SUCCESS' then
81 IF g_debug THEN
82 pay_in_utils.trace('**************************************************','********************');
83 pay_in_utils.trace('g_message_name ',g_message_name);
84 pay_in_utils.trace('**************************************************','********************');
85 END IF;
86
87 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
88 RETURN;
89 END IF;
90
91 pay_in_termination_pkg.check_gratuity
92 (p_effective_date => p_effective_date
93 ,p_element_entry_id => p_element_entry_id
94 ,p_calling_procedure => p_calling_procedure
95 ,p_message_name => g_message_name
96 ,p_token_name => g_token_name
97 ,p_token_value => g_token_value);
98 --
99 IF g_debug THEN
100 pay_in_utils.trace('**************************************************','********************');
101 pay_in_utils.trace('g_message_name ',g_message_name);
102 pay_in_utils.trace('**************************************************','********************');
103 END IF;
104
105 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
106
107 END check_entry_value_int;
108 --------------------------------------------------------------------------
109 -- --
110 -- Name : CHECK_ENTRY_VALUE --
111 -- Type : PROCEDURE --
112 -- Access : Public --
113 -- Description : Procedure for IN localization --
114 -- Parameters : --
115 -- IN : p_effective_date DATE --
116 -- p_element_entry_id NUMBER --
117 -- p_effective_start_date DATE --
118 -- p_effective_end_date DATE --
119 -- OUT : N/A --
120 -- RETURN : N/A --
121 -- --
122 -- Change History : --
123 --------------------------------------------------------------------------
124 -- Rev# Date Userid Bug Description --
125 --------------------------------------------------------------------------
126 -- 1.0 07-Oct-04 lnagaraj 3839878 Created this procedure --
127 --------------------------------------------------------------------------
128 PROCEDURE check_entry_value(p_effective_date IN DATE
129 ,p_element_entry_id IN NUMBER
130 ,p_effective_start_date IN DATE
131 ,p_effective_end_date IN DATE
132 )
133 IS
134 l_procedure VARCHAR2(100);
135 l_message VARCHAR2(250);
136 --
137
138 --
139 BEGIN
140 --
141 l_procedure := g_package||'check_entry_value';
142 g_debug := hr_utility.debug_enabled;
143 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
144
145 IF g_debug THEN
146 pay_in_utils.trace('**************************************************','********************');
147 pay_in_utils.trace('p_effective_date ',p_effective_date );
148 pay_in_utils.trace('p_element_entry_id ',p_element_entry_id );
149 pay_in_utils.trace('p_effective_start_date',p_effective_start_date);
150 pay_in_utils.trace('p_effective_end_date ',p_effective_end_date );
151 pay_in_utils.trace('**************************************************','********************');
152 END IF;
153
154 g_message_name := 'SUCCESS';
155 pay_in_utils.null_message (g_token_name, g_token_value);
156 --
157 -- Check whether PAY is installed for India Localization
158 --
159 IF hr_utility.chk_product_install('Oracle Payroll','IN') THEN
160
161 pay_in_utils.set_location(g_debug,l_procedure,20);
162
163 check_entry_value_int(p_effective_date => p_effective_date
164 ,p_element_entry_id => p_element_entry_id
165 ,p_effective_start_date=> p_effective_start_date
166 ,p_effective_end_date => p_effective_end_date
167 ,p_calling_procedure => l_procedure
168 ,p_message_name => g_message_name
169 ,p_token_name => g_token_name
170 ,p_token_value => g_token_value
171 );
172 --
173 IF g_debug THEN
174 pay_in_utils.trace('**************************************************','********************');
175 pay_in_utils.trace('g_message_name ',g_message_name );
176 pay_in_utils.trace('**************************************************','********************');
177 END IF;
178
179 IF g_message_name <> 'SUCCESS' THEN
180 pay_in_utils.set_location(g_debug,l_procedure,30);
181 pay_in_utils.raise_message(800, g_message_name, g_token_name, g_token_value);
182 END IF;
183
184 END IF ;
185 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
186
187 END check_entry_value;
188
189 --------------------------------------------------------------------------
190 -- --
191 -- Name : CHECK_ENTRY_VALUE_DEL --
192 -- Type : PROCEDURE --
193 -- Access : Public --
194 -- Description : Procedure for IN localization --
195 -- Parameters : --
196 -- IN : p_effective_date DATE --
197 -- p_element_entry_id NUMBER --
198 -- p_effective_start_date DATE --
199 -- p_effective_end_date DATE --
200 -- p_assignment_id_o NUMBER --
201 -- OUT : N/A --
202 -- RETURN : N/A --
203 -- --
204 -- Change History : --
205 --------------------------------------------------------------------------
206 -- Rev# Date Userid Bug Description --
207 --------------------------------------------------------------------------
208 -- 1.0 23-Oct-07 sivanara 6469684 Created this procedure --
209 -- 2.0 25-oct-07 sivanara 6469684 Added parameter p_element_type_id_o--
210 -- 3.0 09-jan-07 sivanara 6391803 Check for Medical and LTC --
211 --------------------------------------------------------------------------
212 PROCEDURE check_entry_value_del( p_effective_date IN DATE
213 ,p_element_entry_id IN NUMBER
214 ,p_effective_start_date IN DATE
215 ,p_effective_end_date IN DATE
216 ,p_assignment_id_o IN NUMBER
217 ,p_element_type_id_o IN NUMBER
218 )
219 IS
220 l_procedure VARCHAR2(100);
221 l_message VARCHAR2(250);
222 l_element_entry_id NUMBER;
223 l_element_name pay_element_types_f.element_name %TYPE;
224 l_ele_name pay_element_types_f.element_name %TYPE;
225 l_check_assg_extra_info NUMBER ;
226
227 CURSOR c_element_name IS
228 SELECT pet.element_information1
229 FROM pay_element_types_f pet
230 WHERE pet.element_type_id = p_element_type_id_o
231 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
232
233 --
234
235 CURSOR c_other_loan_element(p_element_name VARCHAR2) IS
236 SELECT pee.element_entry_id
237 FROM pay_element_types_f pet
238 ,pay_element_entries_f pee
239 WHERE pet.element_type_id =pee.element_type_id
240 AND pee.assignment_id = p_assignment_id_o
241 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
242 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
243 AND pet.element_information1 = p_element_name;
244
245 CURSOR c_ele_name IS
246 SELECT pet.element_name
247 FROM pay_element_types_f pet
248 WHERE pet.element_type_id = p_element_type_id_o
249 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
250
251
252 CURSOR c_assg_extra_info IS
253 SELECT 1
254 FROM per_assignment_extra_info pae
255 WHERE assignment_id = p_assignment_id_o
256 AND ( (pae.aei_information_category = 'PER_IN_MEDICAL_BILLS' AND pae.aei_information10 = p_element_entry_id)
257 OR (pae.aei_information_category = 'PER_IN_MEDICAL_BILLS' AND pae.aei_information11 = p_element_entry_id)
258 OR (pae.aei_information_category = 'PER_IN_LTC_BILLS' AND pae.aei_information11 = p_element_entry_id));
259 --
260 BEGIN
261 --
262 l_procedure := g_package||'check_entry_value_del';
263 g_debug := hr_utility.debug_enabled;
264 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
265
266
267 IF g_debug THEN
268 pay_in_utils.trace('**************************************************','********************');
269 pay_in_utils.trace('p_effective_date ',p_effective_date );
270 pay_in_utils.trace('p_element_entry_id ',p_element_entry_id );
271 pay_in_utils.trace('p_effective_start_date',p_effective_start_date);
272 pay_in_utils.trace('p_effective_end_date ',p_effective_end_date );
273 pay_in_utils.trace('p_assignment_id_o ',p_assignment_id_o );
274 pay_in_utils.trace('**************************************************','********************');
275 END IF;
276
277 g_message_name := 'SUCCESS';
278 pay_in_utils.null_message (g_token_name, g_token_value);
279 --
280 -- Check whether PAY is installed for India Localization
281 --
282 IF hr_utility.chk_product_install('Oracle Payroll','IN') THEN
283
284 pay_in_utils.set_location(g_debug,l_procedure,20);
285
286
287 OPEN c_ele_name;
288 FETCH c_ele_name INTO l_ele_name;
289 CLOSE c_ele_name;
290
291
292 OPEN c_assg_extra_info ;
293 FETCH c_assg_extra_info INTO l_check_assg_extra_info ;
294 IF c_assg_extra_info%FOUND THEN
295 hr_utility.set_message(800, 'PER_IN_EXTRA_ASSG_INFO_LINK');
296 hr_utility.set_message_token('ELEMENT_NAME', l_ele_name);
297 hr_utility.raise_error;
298 END IF ;
299 CLOSE c_assg_extra_info ;
300
301 OPEN c_element_name;
302 FETCH c_element_name INTO l_element_name;
303 CLOSE c_element_name;
304
305
306 IF l_element_name = 'Loan at Concessional Rate' THEN
307
308 OPEN c_other_loan_element(l_element_name);
309 FETCH c_other_loan_element INTO l_element_entry_id;
310 CLOSE c_other_loan_element;
311
312 IF l_element_entry_id IS NULL THEN
313 RETURN;
314 END IF;
315
316 per_in_perquisite_pkg.check_element_entry
317 (p_effective_date => p_effective_date
318 ,p_element_entry_id => l_element_entry_id
319 ,p_effective_start_date => p_effective_start_date
320 ,p_effective_end_date => p_effective_end_date
321 ,p_calling_procedure => l_procedure
322 ,p_message_name => g_message_name
323 ,p_token_name => g_token_name
324 ,p_token_value => g_token_value);
325
326 --
327 IF g_debug THEN
328 pay_in_utils.trace('**************************************************','********************');
329 pay_in_utils.trace('g_message_name ',g_message_name );
330 pay_in_utils.trace('**************************************************','********************');
331 END IF;
332
333 IF g_message_name <> 'SUCCESS' THEN
334 pay_in_utils.set_location(g_debug,l_procedure,30);
335 pay_in_utils.raise_message(800, g_message_name, g_token_name, g_token_value);
336 END IF;
337 END IF ;
338 END IF ;
339 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
340
341 END check_entry_value_del;
342
343 END per_in_entry_value_leg_hook;