1 PACKAGE BODY PAY_BALANCES_UPLOAD_PKG AS
2 /* $Header: pybaluld.pkb 120.2 2006/07/21 10:03:51 tbattoo noship $ */
3
4 --Global cursor declarations
5
6 --Cursor to select business group id
7 cursor csr_bg_id(p_BG_NAME VARCHAR2) is
8 select business_group_id
9 from per_business_groups
10 where name =p_BG_NAME;
11
12 --Cursor to determine if session id is present in hr_owner_definitions
13 CURSOR csr_get_session_id IS
14 SELECT userenv('sessionid') from dual;
15
16 cursor csr_get_hr_sess_id(p_session_id HR_OWNER_DEFINITIONS.SESSION_ID%TYPE) is
17 select session_id
18 from hr_owner_definitions
19 where session_id=p_session_id;
20
21 --This procedure is used for uploading data into table PAY_BALANCE_CATEGORIES_F
22 --This procedure is called from pybalcat.lct configuration file
23 PROCEDURE PAY_BAL_CATF_LOAD_ROW
24 (p_CATEGORY_NAME IN VARCHAR2
25 ,p_EFFECTIVE_START_DATE IN VARCHAR2
26 ,p_EFFECTIVE_END_DATE IN VARCHAR2
27 ,p_LEGISLATION_CODE IN VARCHAR2
28 ,p_BUSINESS_GROUP_NAME IN VARCHAR2
29 ,p_SAVE_RUN_BALANCE_ENABLED IN VARCHAR2
30 ,p_USER_CATEGORY_NAME IN VARCHAR2
31 ,p_PBC_INFORMATION_CATEGORY IN VARCHAR2
32 ,p_PBC_INFORMATION1 IN VARCHAR2
33 ,p_PBC_INFORMATION2 IN VARCHAR2
34 ,p_PBC_INFORMATION3 IN VARCHAR2
35 ,p_PBC_INFORMATION4 IN VARCHAR2
36 ,p_PBC_INFORMATION5 IN VARCHAR2
37 ,p_PBC_INFORMATION6 IN VARCHAR2
38 ,p_PBC_INFORMATION7 IN VARCHAR2
39 ,p_PBC_INFORMATION8 IN VARCHAR2
40 ,p_PBC_INFORMATION9 IN VARCHAR2
41 ,p_PBC_INFORMATION10 IN VARCHAR2
42 ,p_PBC_INFORMATION11 IN VARCHAR2
43 ,p_PBC_INFORMATION12 IN VARCHAR2
44 ,p_PBC_INFORMATION13 IN VARCHAR2
45 ,p_PBC_INFORMATION14 IN VARCHAR2
46 ,p_PBC_INFORMATION15 IN VARCHAR2
47 ,p_PBC_INFORMATION16 IN VARCHAR2
48 ,p_PBC_INFORMATION17 IN VARCHAR2
49 ,p_PBC_INFORMATION18 IN VARCHAR2
50 ,p_PBC_INFORMATION19 IN VARCHAR2
51 ,p_PBC_INFORMATION20 IN VARCHAR2
52 ,p_PBC_INFORMATION21 IN VARCHAR2
53 ,p_PBC_INFORMATION22 IN VARCHAR2
54 ,p_PBC_INFORMATION23 IN VARCHAR2
55 ,p_PBC_INFORMATION24 IN VARCHAR2
56 ,p_PBC_INFORMATION25 IN VARCHAR2
57 ,p_PBC_INFORMATION26 IN VARCHAR2
58 ,p_PBC_INFORMATION27 IN VARCHAR2
59 ,p_PBC_INFORMATION28 IN VARCHAR2
60 ,p_PBC_INFORMATION29 IN VARCHAR2
61 ,p_PBC_INFORMATION30 IN VARCHAR2
62 ,p_OVN IN VARCHAR2
63 ,p_OWNER IN VARCHAR2
64 ) IS
65
66 l_balance_category_id PAY_BALANCE_CATEGORIES_F.BALANCE_CATEGORY_ID%TYPE;
67 l_category_name PAY_BALANCE_CATEGORIES_F.CATEGORY_NAME%TYPE;
68 l_effective_start_date PAY_BALANCE_CATEGORIES_F.EFFECTIVE_START_DATE%TYPE;
69 l_effective_end_date PAY_BALANCE_CATEGORIES_F.EFFECTIVE_END_DATE%TYPE;
70 l_legislation_code PAY_BALANCE_CATEGORIES_F.LEGISLATION_CODE%TYPE;
71 l_business_group_id PAY_BALANCE_CATEGORIES_F.BUSINESS_GROUP_ID%TYPE;
72 l_bus_grp_id PAY_BALANCE_CATEGORIES_F.BUSINESS_GROUP_ID%TYPE;
73 l_ovn PAY_BALANCE_CATEGORIES_F.OBJECT_VERSION_NUMBER%TYPE;
74 l_owner VARCHAR2(6);
75 l_nextval NUMBER;
76
77 cursor csr_sel_bal_category_all is
78 SELECT balance_category_id
79 ,category_name
80 ,effective_start_date
81 ,effective_end_date
82 ,legislation_code
83 ,business_group_id
84 ,object_version_number
85 ,DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
86 FROM PAY_BALANCE_CATEGORIES_F
87 WHERE category_name = p_CATEGORY_NAME
88 AND effective_start_date =to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD')
89 AND effective_end_date =to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD')
90 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);
91
92 cursor csr_sel_bal_category_mid is
93 SELECT balance_category_id
94 ,category_name
95 ,effective_start_date
96 ,effective_end_date
97 ,legislation_code
98 ,business_group_id
99 ,object_version_number
100 ,DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
101 FROM PAY_BALANCE_CATEGORIES_F
102 WHERE category_name = p_CATEGORY_NAME
103 AND effective_start_date =to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD')
104 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);
105
106 cursor csr_sel_bal_category_few is
107 SELECT balance_category_id
108 ,category_name
109 ,effective_start_date
110 ,effective_end_date
111 ,legislation_code
112 ,business_group_id
113 ,object_version_number
114 ,DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
115 FROM PAY_BALANCE_CATEGORIES_F
116 WHERE category_name = p_CATEGORY_NAME
117 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);
118
119 BEGIN
120
121 open csr_bg_id(p_BUSINESS_GROUP_NAME);
122 fetch csr_bg_id into l_bus_grp_id;
123 close csr_bg_id;
124
125 --Check if row in ldt is not in the table;
126 open csr_sel_bal_category_few;
127 fetch csr_sel_bal_category_few into l_balance_category_id,
128 l_category_name,l_effective_start_date,l_effective_end_date,
129 l_legislation_code,l_business_group_id,l_ovn,l_owner;
130 if(csr_sel_bal_category_few%found)then
131 open csr_sel_bal_category_all;
132 fetch csr_sel_bal_category_all into l_balance_category_id,
133 l_category_name,l_effective_start_date,l_effective_end_date,
134 l_legislation_code,l_business_group_id,l_ovn,l_owner;
135 if(csr_sel_bal_category_all%found) then
136 --need to perform a date-track correction or row already exists
137 if(p_OWNER='SEED') then
138 hr_general2.init_fndload(800,1);
139 else
140 hr_general2.init_fndload(800,-1);
141 end if;
142 update pay_balance_categories_f
143 set
144 save_run_balance_enabled = p_SAVE_RUN_BALANCE_ENABLED
145 ,user_category_name = p_user_category_name
146 ,pbc_information_category = p_PBC_INFORMATION_CATEGORY
147 ,pbc_information1 = p_pbc_information1
148 ,pbc_information2 = p_pbc_information2
149 ,pbc_information3 = p_pbc_information3
150 ,pbc_information4 = p_pbc_information4
151 ,pbc_information5 = p_pbc_information5
152 ,pbc_information6 = p_pbc_information6
153 ,pbc_information7 = p_pbc_information7
154 ,pbc_information8 = p_pbc_information8
155 ,pbc_information9 = p_pbc_information9
156 ,pbc_information10 = p_pbc_information10
157 ,pbc_information11 = p_pbc_information11
158 ,pbc_information12 = p_pbc_information12
159 ,pbc_information13 = p_pbc_information13
160 ,pbc_information14 = p_pbc_information14
161 ,pbc_information15 = p_pbc_information15
162 ,pbc_information16 = p_pbc_information16
163 ,pbc_information17 = p_pbc_information17
164 ,pbc_information18 = p_pbc_information18
165 ,pbc_information19 = p_pbc_information19
166 ,pbc_information20 = p_pbc_information20
167 ,pbc_information21 = p_pbc_information21
168 ,pbc_information22 = p_pbc_information22
169 ,pbc_information23 = p_pbc_information23
170 ,pbc_information24 = p_pbc_information24
171 ,pbc_information25 = p_pbc_information25
172 ,pbc_information26 = p_pbc_information26
173 ,pbc_information27 = p_pbc_information27
174 ,pbc_information28 = p_pbc_information28
175 ,pbc_information29 = p_pbc_information29
176 ,pbc_information30 = p_pbc_information30
177 ,object_version_number = l_ovn
178 where balance_category_id = l_balance_category_id;
179 close csr_sel_bal_category_all;
180
181 else
182
183 open csr_sel_bal_category_mid;
184 fetch csr_sel_bal_category_mid into l_balance_category_id,
185 l_category_name,l_effective_start_date,l_effective_end_date,
186 l_legislation_code,l_business_group_id,l_ovn,l_owner;
187 if(csr_sel_bal_category_mid%found) then
188 --need to perform a date-track update
189 if(p_OWNER='SEED') then
190 hr_general2.init_fndload(800,1);
191 else
192 hr_general2.init_fndload(800,-1);
193 end if;
194 update pay_balance_categories_f
195 set
196 effective_end_date = to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD')
197 ,save_run_balance_enabled = p_SAVE_RUN_BALANCE_ENABLED
198 ,user_category_name = p_user_category_name
199 ,pbc_information_category = p_PBC_INFORMATION_CATEGORY
200 ,pbc_information1 = p_pbc_information1
201 ,pbc_information2 = p_pbc_information2
202 ,pbc_information3 = p_pbc_information3
203 ,pbc_information4 = p_pbc_information4
204 ,pbc_information5 = p_pbc_information5
205 ,pbc_information6 = p_pbc_information6
206 ,pbc_information7 = p_pbc_information7
207 ,pbc_information8 = p_pbc_information8
208 ,pbc_information9 = p_pbc_information9
209 ,pbc_information10 = p_pbc_information10
210 ,pbc_information11 = p_pbc_information11
211 ,pbc_information12 = p_pbc_information12
212 ,pbc_information13 = p_pbc_information13
213 ,pbc_information14 = p_pbc_information14
214 ,pbc_information15 = p_pbc_information15
215 ,pbc_information16 = p_pbc_information16
216 ,pbc_information17 = p_pbc_information17
217 ,pbc_information18 = p_pbc_information18
218 ,pbc_information19 = p_pbc_information19
219 ,pbc_information20 = p_pbc_information20
220 ,pbc_information21 = p_pbc_information21
221 ,pbc_information22 = p_pbc_information22
222 ,pbc_information23 = p_pbc_information23
223 ,pbc_information24 = p_pbc_information24
224 ,pbc_information25 = p_pbc_information25
225 ,pbc_information26 = p_pbc_information26
226 ,pbc_information27 = p_pbc_information27
227 ,pbc_information28 = p_pbc_information28
228 ,pbc_information29 = p_pbc_information29
229 ,pbc_information30 = p_pbc_information30
230 ,object_version_number = l_ovn
231 where balance_category_id = l_balance_category_id;
232 else
233 --need to insert the new date-track row.
234 if(p_OWNER='SEED') then
235 hr_general2.init_fndload(800,1);
236 else
237 hr_general2.init_fndload(800,-1);
238 end if;
239 insert into
240 pay_balance_categories_f(balance_category_id,category_name,
241 effective_start_date,effective_end_date,legislation_code,
242 business_group_id,save_run_balance_enabled,user_category_name,
243 pbc_information_category,pbc_information1,pbc_information2,
244 pbc_information3,pbc_information4,pbc_information5,
245 pbc_information6,pbc_information7,pbc_information8,
246 pbc_information9,pbc_information10,pbc_information11,
247 pbc_information12,pbc_information13,pbc_information14,
248 pbc_information15,pbc_information16,pbc_information17,
249 pbc_information18, pbc_information19,pbc_information20,
250 pbc_information21,pbc_information22,pbc_information23,
251 pbc_information24,pbc_information25,pbc_information26 ,
252 pbc_information27,pbc_information28,pbc_information29,
253 pbc_information30,object_version_number)
254 Values
255 (l_balance_category_id,p_CATEGORY_NAME,
256 to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD'),
257 to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD'),
258 p_LEGISLATION_CODE,l_bus_grp_id,p_SAVE_RUN_BALANCE_ENABLED,
259 p_user_category_name,
260 p_PBC_INFORMATION_CATEGORY,p_PBC_INFORMATION1,
261 p_PBC_INFORMATION2,p_PBC_INFORMATION3,p_PBC_INFORMATION4,
262 p_PBC_INFORMATION5,p_PBC_INFORMATION6,p_PBC_INFORMATION7,
263 p_PBC_INFORMATION8,p_PBC_INFORMATION9,p_PBC_INFORMATION10,
264 p_PBC_INFORMATION11,p_PBC_INFORMATION12,p_PBC_INFORMATION13,
265 p_PBC_INFORMATION14,p_PBC_INFORMATION15,p_PBC_INFORMATION16,
266 p_PBC_INFORMATION17,p_PBC_INFORMATION18,p_PBC_INFORMATION19,
267 p_PBC_INFORMATION20,p_PBC_INFORMATION21,p_PBC_INFORMATION22,
268 p_PBC_INFORMATION23,p_PBC_INFORMATION24,p_PBC_INFORMATION25,
269 p_PBC_INFORMATION26,p_PBC_INFORMATION27,p_PBC_INFORMATION28,
270 p_PBC_INFORMATION29,p_PBC_INFORMATION30,to_number(p_OVN)
271 );
272 end if;
273 close csr_sel_bal_category_mid;
274 end if;
275 else
276 --row does not exist in the table. so insert the new row into the table
277 if(p_OWNER='SEED') then
278 hr_general2.init_fndload(800,1);
279 else
280 hr_general2.init_fndload(800,-1);
281 end if;
282
283 insert into
284 pay_balance_categories_f(balance_category_id,category_name,
285 effective_start_date,effective_end_date,legislation_code,
286 business_group_id,save_run_balance_enabled,user_category_name,
287 pbc_information_category,
288 pbc_information1,pbc_information2,pbc_information3,pbc_information4,
289 pbc_information5,pbc_information6,pbc_information7,pbc_information8,
290 pbc_information9,pbc_information10,pbc_information11,
291 pbc_information12,pbc_information13,pbc_information14,
292 pbc_information15,pbc_information16,pbc_information17,
293 pbc_information18, pbc_information19,pbc_information20,
294 pbc_information21,pbc_information22,pbc_information23,
295 pbc_information24,pbc_information25,pbc_information26 ,
296 pbc_information27,pbc_information28,pbc_information29,
297 pbc_information30,object_version_number)
298 Values
299 (pay_balance_categories_s.nextval,p_CATEGORY_NAME,
300 to_date(p_EFFECTIVE_START_DATE,'YYYY/MM/DD'),
301 to_date(p_EFFECTIVE_END_DATE,'YYYY/MM/DD'),
302 p_LEGISLATION_CODE,l_bus_grp_id,p_SAVE_RUN_BALANCE_ENABLED,
303 p_user_category_name,
304 p_PBC_INFORMATION_CATEGORY,p_PBC_INFORMATION1,p_PBC_INFORMATION2,
305 p_PBC_INFORMATION3,p_PBC_INFORMATION4,p_PBC_INFORMATION5,
306 p_PBC_INFORMATION6,p_PBC_INFORMATION7,p_PBC_INFORMATION8,
307 p_PBC_INFORMATION9,p_PBC_INFORMATION10,p_PBC_INFORMATION11,
308 p_PBC_INFORMATION12,p_PBC_INFORMATION13,p_PBC_INFORMATION14,
309 p_PBC_INFORMATION15,p_PBC_INFORMATION16,p_PBC_INFORMATION17,
310 p_PBC_INFORMATION18,p_PBC_INFORMATION19,p_PBC_INFORMATION20,
311 p_PBC_INFORMATION21,p_PBC_INFORMATION22,p_PBC_INFORMATION23,
312 p_PBC_INFORMATION24,p_PBC_INFORMATION25,p_PBC_INFORMATION26,
313 p_PBC_INFORMATION27,p_PBC_INFORMATION28,p_PBC_INFORMATION29,
314 p_PBC_INFORMATION30,to_number(p_OVN)
315 );
316 end if;
317 close csr_sel_bal_category_few;
318
319 END PAY_BAL_CATF_LOAD_ROW;
320
321 --This procedure is used for uploading data into table PAY_BALANCE_TYPES
322 --This is called from pybalcat.lct configuration file
323 PROCEDURE PAY_BAL_TYPES_LOAD_ROW
324 (p_CATEGORY_NAME IN VARCHAR2
325 ,p_EFFECTIVE_START_DATE IN VARCHAR2
326 ,p_EFFECTIVE_END_DATE IN VARCHAR2
327 ,p_LEGISLATION_CODE IN VARCHAR2
328 ,p_BALANCE_NAME IN VARCHAR2
329 ,p_BUSINESS_GROUP_NAME IN VARCHAR2
330 ,p_OWNER IN VARCHAR2 --added
331 ) IS
332
333 l_balance_type_id PAY_BALANCE_TYPES.BALANCE_TYPE_ID%TYPE;
334 l_balance_name PAY_BALANCE_TYPES.BALANCE_NAME%TYPE;
335 l_legislation_code PAY_BALANCE_TYPES.LEGISLATION_CODE%TYPE;
336 l_business_group_id PAY_BALANCE_TYPES.BUSINESS_GROUP_ID%TYPE;
337 l_ovn PAY_BALANCE_TYPES.object_version_number%TYPE;
341 --This cursor is used to select the balance type id
338 l_owner VARCHAR2(6);
339 l_balance_category_id PAY_BALANCE_CATEGORIES_F.BALANCE_CATEGORY_ID%TYPE;
340
342 cursor csr_sel_bal_type_id(p_BALANCE_NAME VARCHAR2,
343 p_LEGISLATION_CODE VARCHAR2,
344 p_bg_id NUMBER) is--added
345 SELECT balance_type_id
346 FROM PAY_BALANCE_TYPES
347 WHERE balance_name =p_BALANCE_NAME
348 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
349 and (business_group_id =p_bg_id or business_group_id is null);--added
350
351 --This cursor is used to select the balance category id
352 cursor csr_sel_bal_cat_id(p_CATEGORY_NAME VARCHAR2,
353 p_ESD VARCHAR2,
354 p_EED VARCHAR2,
355 p_LEGISLATION_CODE VARCHAR2, --Bug 5044079
356 p_bg_id NUMBER) is --Bug 5044079
357 select balance_category_id
358 from pay_balance_categories_f
359 where category_name =p_CATEGORY_NAME
360 and effective_start_date =to_date(p_ESD,'YYYY/MM/DD')
361 and effective_end_date =to_date(p_EED,'YYYY/MM/DD')
362 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1) --Bug 5044079
363 and (business_group_id =p_bg_id or business_group_id is null); --Bug 5044079
364
365 BEGIN
366
367 open csr_bg_id(p_BUSINESS_GROUP_NAME);
368 fetch csr_bg_id into l_business_group_id;
369 close csr_bg_id;
370
371 open csr_sel_bal_type_id(p_BALANCE_NAME,p_LEGISLATION_CODE,
372 l_business_group_id);
373 fetch csr_sel_bal_type_id into l_balance_type_id;
374
375 if (csr_sel_bal_type_id%found) then
376 open csr_sel_bal_cat_id(p_CATEGORY_NAME,p_EFFECTIVE_START_DATE,
377 p_EFFECTIVE_END_DATE,p_LEGISLATION_CODE,
378 l_business_group_id);
379 fetch csr_sel_bal_cat_id into l_balance_category_id;
380 if(csr_sel_bal_cat_id%found) then
381
382 if(p_OWNER='SEED') then
383 hr_general2.init_fndload(800,1);
384 else
385 hr_general2.init_fndload(800,-1);
386 end if;
387
388 update pay_balance_types
389 set balance_category_id =l_balance_category_id
390 where balance_type_id=l_balance_type_id;
391 end if;
392 close csr_sel_bal_cat_id;
393 end if;
394 close csr_sel_bal_type_id;
395
396 END PAY_BAL_TYPES_LOAD_ROW;
397
398 --This procedure is used for uploading data into PAY_BAL_ATTRIBUTE_DEFINITIONS
399 --This is called from pybalade.lct configuration file
400 PROCEDURE PAY_BAL_ADE_LOAD_ROW
401 (p_ATTRIBUTE_NAME IN VARCHAR2
402 ,p_LEGISLATION_CODE IN VARCHAR2
403 ,p_BUSINESS_GROUP_NAME IN VARCHAR2
404 ,p_ALTERABLE IN VARCHAR2
405 ,p_user_attribute_name IN VARCHAR2
406 ,p_OWNER IN VARCHAR2
407 ) IS
408
409 l_attribute_id PAY_BAL_ATTRIBUTE_DEFINITIONS.ATTRIBUTE_ID%TYPE;
410 l_attribute_name PAY_BAL_ATTRIBUTE_DEFINITIONS.ATTRIBUTE_NAME%TYPE;
411 l_legislation_code PAY_BAL_ATTRIBUTE_DEFINITIONS.LEGISLATION_CODE%TYPE;
412 l_business_group_id PAY_BAL_ATTRIBUTE_DEFINITIONS.BUSINESS_GROUP_ID%TYPE;
413 l_alterable PAY_BAL_ATTRIBUTE_DEFINITIONS.ALTERABLE%TYPE;
414 l_owner VARCHAR2(6);
415 l_temp_attribute_id PAY_BAL_ATTRIBUTE_DEFINITIONS.ATTRIBUTE_ID%TYPE;
416 l_session_id HR_OWNER_DEFINITIONS.SESSION_ID%TYPE;
417 l_session_id1 HR_OWNER_DEFINITIONS.SESSION_ID%TYPE;
418
419 --cursors to check if child rows exist
420 cursor csr_pba_id(p_attrib_id PAY_BAL_ATTRIBUTE_DEFINITIONS.ATTRIBUTE_ID%TYPE)
421 is
422 select attribute_id
423 from pay_balance_attributes
424 where attribute_id=p_attrib_id;
425
426 cursor csr_pbad_id(p_attrib_id PAY_BAL_ATTRIBUTE_DEFINITIONS.ATTRIBUTE_ID%TYPE)
427 is
428 select attribute_id
429 from pay_bal_attribute_defaults
430 where attribute_id=p_attrib_id;
431
432 --This cursor is used to select the attribute id
433 cursor csr_sel_attrib_id(p_ATTRIBUTE_NAME VARCHAR2,
434 p_LEGISLATION_CODE VARCHAR2,
435 p_bg_id NUMBER) is
436 SELECT attribute_id,
437 attribute_name,
438 alterable,
439 legislation_code,
440 DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
441 FROM PAY_BAL_ATTRIBUTE_DEFINITIONS
442 WHERE attribute_name = p_ATTRIBUTE_NAME
443 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
444 AND (business_group_id=p_bg_id OR business_group_id is NULL);
445
446 BEGIN
447 open csr_get_session_id;
448 fetch csr_get_session_id into l_session_id;
449 close csr_get_session_id;
450
451 open csr_get_hr_sess_id(l_session_id);
452 fetch csr_get_hr_sess_id into l_session_id1;
453 if(csr_get_hr_sess_id%notfound) then
454 hr_startup_data_api_support.create_owner_definition('PAY');
455 end if;
456 close csr_get_hr_sess_id;
457
458 --set user mode based on value of OWNER
459 if (p_OWNER ='SEED') then
460
461 if(p_BUSINESS_GROUP_NAME is null AND p_LEGISLATION_CODE is null)then
462 hr_startup_data_api_support.enable_startup_mode('GENERIC');
463
464 else
465 hr_startup_data_api_support.enable_startup_mode('STARTUP');
466 end if ;
467 --Need to set the AOL WHO columns properly. Hence call this
468 hr_general2.init_fndload(800,1);
469
470 elsif (p_OWNER='CUSTOM') then
471
475
472 if(p_BUSINESS_GROUP_NAME is null AND p_LEGISLATION_CODE is null)then
473
474 hr_startup_data_api_support.enable_startup_mode('GENERIC');
476 elsif(p_BUSINESS_GROUP_NAME is not null
477 AND p_LEGISLATION_CODE is null) then
478
479 hr_startup_data_api_support.enable_startup_mode('USER');
480
481 end if;
482 --Need to set the AOL WHO columns properly. Hence call this
483 hr_general2.init_fndload(800,-1);
484 end if;--Close end if for OWNER
485
486 open csr_bg_id(p_BUSINESS_GROUP_NAME);
487 fetch csr_bg_id into l_business_group_id;
488 close csr_bg_id;
489
490 open csr_sel_attrib_id(p_ATTRIBUTE_NAME,
491 p_LEGISLATION_CODE,l_business_group_id);
492 fetch csr_sel_attrib_id into
493 l_attribute_id,l_attribute_name,l_alterable,l_legislation_code,l_owner;
494
495 --value in the ldt is already present at the customer site
496 if(csr_sel_attrib_id%found) then
497 close csr_sel_attrib_id;
498 --ALTERABLE flag needs to be updated
499 if(l_alterable <> p_ALTERABLE) then
500 if((l_business_group_id is null and l_legislation_code is not
501 null) AND (l_ALTERABLE ='Y' and p_ALTERABLE='N')) then
502 --its a startup definition
503 --updating from Y to N ...so check if child rows exist
504 open csr_pba_id(l_attribute_id);
505 fetch csr_pba_id into l_temp_attribute_id;
506 close csr_pba_id;
507
508 if (l_temp_attribute_id is not NULL)then
509 --raise error ask to rerun the ldts after deleting the child
510 --rows
511 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
512 fnd_message.set_token('TABLE_NAME',
513 'PAY_BAL_ATTRIBUTE_DEFINITIONS');
514 fnd_message.raise_error;
515 end if;
516
517 open csr_pbad_id(l_attribute_id);
518 fetch csr_pbad_id into l_temp_attribute_id;
519 close csr_pbad_id;
520
521 if(l_temp_attribute_id is not NULL)then
522 --raise error ask to rerun the ldts after deleting the child
523 --rows
524 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
525 fnd_message.set_token('TABLE_NAME',
526 'PAY_BAL_ATTRIBUTE_DEFINITIONS');
527 fnd_message.raise_error;
528 end if;
529
530 update PAY_BAL_ATTRIBUTE_DEFINITIONS
531 set ALTERABLE =p_ALTERABLE
532 where attribute_id=l_attribute_id;
533
534 elsif((l_business_group_id is null and l_legislation_code is not
535 null) AND (l_ALTERABLE ='N' and p_ALTERABLE='Y')) then
536 --updating from N to Y...since child rows dont exist can do direct
537 --update
538
539 update PAY_BAL_ATTRIBUTE_DEFINITIONS
540 set ALTERABLE =p_ALTERABLE
541 where attribute_id=l_attribute_id;
542
543 end if;
544 end if;
545 else
546 PAY_BAL_ATTRIB_DEFINITION_API.create_bal_attrib_definition
547 (p_effective_date =>sysdate
548 ,p_attribute_name => p_ATTRIBUTE_NAME
549 ,p_business_group_id =>l_business_group_id
550 ,p_legislation_code =>p_LEGISLATION_CODE
551 ,p_alterable => p_ALTERABLE
552 ,p_user_attribute_name => p_user_attribute_name
553 ,p_attribute_id => l_attribute_id
554 );
555 close csr_sel_attrib_id;
556 end if;
557
558 END PAY_BAL_ADE_LOAD_ROW;
559
560
561 --This procedure is used for loading data into table PAY_BALANCE_ATTRIBUTES
562 --This procedure is called from pybalatt.lct configuration file
563 PROCEDURE PAY_BAL_ATT_LOAD_ROW
564 (p_ATTRIBUTE_NAME IN VARCHAR2
565 ,p_ATTR_DEFN_LEG_CODE IN VARCHAR2
566 ,p_LEGISLATION_CODE IN VARCHAR2
567 ,p_BALANCE_NAME IN VARCHAR2
568 ,p_BAL_LEG_CODE IN VARCHAR2
569 ,p_DIMENSION_NAME IN VARCHAR2
570 ,p_DIM_LEG_CODE IN VARCHAR2
571 ,p_BUSINESS_GROUP_NAME IN VARCHAR2
572 ,p_ATTR_DEFN_BUS_GROUP_NAME IN VARCHAR2
573 ,p_BAL_BUS_GROUP_NAME IN VARCHAR2
574 ,p_DIM_BUS_GROUP_NAME IN VARCHAR2
575 ,p_OWNER IN VARCHAR2
576 )IS
577
578 l_balance_attribute_id PAY_BALANCE_ATTRIBUTES.BALANCE_ATTRIBUTE_ID%TYPE;
579 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
580 l_bal_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
581 l_attr_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
582 l_attribute_id PAY_BALANCE_ATTRIBUTES.ATTRIBUTE_ID%TYPE;
583 l_balance_type_id PAY_BALANCE_TYPES.BALANCE_TYPE_ID%TYPE;
584 l_balance_dimension_id PAY_BALANCE_DIMENSIONS.BALANCE_DIMENSION_ID%TYPE;
585 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
586 l_legislation_code PAY_BALANCE_ATTRIBUTES.LEGISLATION_CODE%TYPE;
587 l_def_bal_leg_code PAY_DEFINED_BALANCES.LEGISLATION_CODE%TYPE;
588 l_def_bal_bg_id PAY_DEFINED_BALANCES.BUSINESS_GROUP_ID%TYPE;
589 l_owner VARCHAR2(6);
590 l_session_id HR_OWNER_DEFINITIONS.SESSION_ID%TYPE;
591 l_session_id1 HR_OWNER_DEFINITIONS.SESSION_ID%TYPE;
592
593 --This cursor is used to select the attribute id
594 cursor csr_sel_attrib_id(p_ATTRIBUTE_NAME VARCHAR2,
595 p_LEGISLATION_CODE VARCHAR2,
596 p_bg_id NUMBER) is
597 select attribute_id
598 from pay_bal_attribute_definitions
602
599 where attribute_name = p_ATTRIBUTE_NAME
600 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
601 and (business_group_id =p_bg_id or business_group_id is null);
603 --This cursor is used to select the defined balance id
604 cursor csr_sel_def_bal_id(p_balance_type_id NUMBER,
605 p_balance_dimension_id NUMBER,
606 p_LEGISLATION_CODE VARCHAR2,
607 p_bg_id NUMBER)is
608 select defined_balance_id
609 from pay_defined_balances
610 where balance_type_id =p_balance_type_id
611 and balance_dimension_id =p_balance_dimension_id
612 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
613 and (business_group_id =p_bg_id or business_group_id is null);
614
615 --This cursor is used to select the balance type id
616 cursor csr_sel_bal_type_id(p_BALANCE_NAME VARCHAR2,
617 p_LEGISLATION_CODE VARCHAR2,
618 p_bg_id NUMBER) is
619 select balance_type_id
620 from pay_balance_types
621 where balance_name =p_BALANCE_NAME
622 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
623 and (business_group_id =p_bg_id or business_group_id is null);
624
625 --This cursor is used to select the balance dimension id
626 cursor csr_sel_bal_dim_id(p_DIMENSION_NAME VARCHAR2,
627 p_LEGISLATION_CODE VARCHAR2,
628 p_bg_id NUMBER) is
629 select balance_dimension_id
630 from pay_balance_dimensions
631 where dimension_name =p_DIMENSION_NAME
632 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
633 and (business_group_id =p_bg_id or business_group_id is null);
634
635 BEGIN
636 open csr_get_session_id;
637 fetch csr_get_session_id into l_session_id;
638 close csr_get_session_id;
639
640 open csr_get_hr_sess_id(l_session_id);
641 fetch csr_get_hr_sess_id into l_session_id1;
642 if(csr_get_hr_sess_id%notfound) then
643
644 hr_startup_data_api_support.create_owner_definition('PAY');
645
646 end if;
647 close csr_get_hr_sess_id;
648
649 --set user mode based on value of OWNER
650 if (p_OWNER ='SEED') then
651
652 hr_startup_data_api_support.enable_startup_mode('STARTUP');
653 --Need to set the AOL WHO columns properly. Hence call this
654 hr_general2.init_fndload(800,1);
655
656 elsif (p_OWNER='CUSTOM') then
657
658 if(p_BUSINESS_GROUP_NAME is null AND p_LEGISLATION_CODE is null) then
659
660 hr_startup_data_api_support.enable_startup_mode('GENERIC');
661
662 elsif(p_BUSINESS_GROUP_NAME is not null
663 AND p_LEGISLATION_CODE is null) then
664
665 hr_startup_data_api_support.enable_startup_mode('USER');
666
667 end if;
668 --Need to set the AOL WHO columns properly. Hence call this
669 hr_general2.init_fndload(800,-1);
670 end if;
671
672 open csr_bg_id(p_BUSINESS_GROUP_NAME);
673 fetch csr_bg_id into l_business_group_id;
674 close csr_bg_id;
675
676 --Get the balance type id
677 open csr_bg_id(p_BAL_BUS_GROUP_NAME);
678 fetch csr_bg_id into l_bal_business_group_id;
679 close csr_bg_id;
680
681 open csr_sel_bal_type_id(p_BALANCE_NAME,
682 p_BAL_LEG_CODE,l_bal_business_group_id);
683 fetch csr_sel_bal_type_id into l_balance_type_id;
684 if (csr_sel_bal_type_id %notfound) then
685 null;
686 end if;
687 close csr_sel_bal_type_id;
688
689 --Get the balance dimension id
690 open csr_bg_id(p_DIM_BUS_GROUP_NAME);
691 fetch csr_bg_id into l_bal_business_group_id;
692 close csr_bg_id;
693
694 open csr_sel_bal_dim_id(p_DIMENSION_NAME,p_DIM_LEG_CODE,
695 l_bal_business_group_id);
696 fetch csr_sel_bal_dim_id into l_balance_dimension_id;
697 if (csr_sel_bal_dim_id%notfound) then
698 null;
699 end if;
700 close csr_sel_bal_dim_id;
701
702 --Get the defined balance id
703 open csr_sel_def_bal_id(l_balance_type_id,l_balance_dimension_id,
704 p_BAL_LEG_CODE,l_bal_business_group_id);
705 fetch csr_sel_def_bal_id into l_defined_balance_id;
706 close csr_sel_def_bal_id;
707
708 --Get the attribute id
709 open csr_bg_id(p_ATTR_DEFN_BUS_GROUP_NAME);
710 fetch csr_bg_id into l_attr_business_group_id;
711 close csr_bg_id;
712
713 open csr_sel_attrib_id(p_ATTRIBUTE_NAME,
714 p_ATTR_DEFN_LEG_CODE,
715 l_attr_business_group_id);
716 fetch csr_sel_attrib_id into l_attribute_id;
717 close csr_sel_attrib_id;
718
719 BEGIN
720
721 SELECT balance_attribute_id,
722 attribute_id,
723 defined_balance_id,
724 legislation_code,
725 DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
726 INTO l_balance_attribute_id,
727 l_attribute_id,
728 l_defined_balance_id,
729 l_legislation_code,
730 l_owner
731 FROM PAY_BALANCE_ATTRIBUTES
732 WHERE attribute_id = l_attribute_id
733 AND defined_balance_id = l_defined_balance_id
734 AND (business_group_id =l_business_group_id
735 OR business_group_id is null);
736
737 EXCEPTION WHEN NO_DATA_FOUND
738 THEN
739
740 open csr_sel_attrib_id(p_ATTRIBUTE_NAME,
741 p_ATTR_DEFN_LEG_CODE,
742 l_attr_business_group_id);
743 fetch csr_sel_attrib_id into l_attribute_id;
744
745 if (csr_sel_attrib_id%found) then
749 fetch csr_sel_def_bal_id into l_defined_balance_id;
746 open
747 csr_sel_def_bal_id(l_balance_type_id,l_balance_dimension_id,
748 p_BAL_LEG_CODE,l_bal_business_group_id);
750
751 if (csr_sel_def_bal_id%found) then
752
753 PAY_BALANCE_ATTRIBUTE_API.CREATE_BALANCE_ATTRIBUTE
754 (p_attribute_id =>l_attribute_id
755 ,p_defined_balance_id =>l_defined_balance_id
756 ,p_business_group_id =>l_business_group_id
757 ,p_legislation_code => p_LEGISLATION_CODE
758 ,p_balance_attribute_id =>l_balance_attribute_id
759 );
760
761 end if;
762 close csr_sel_def_bal_id;
763 end if;
764 close csr_sel_attrib_id;
765
766 END;
767
768 END PAY_BAL_ATT_LOAD_ROW;
769
770 --This procedure is used for loading data into table PAY_BAL_ATTRIBUTE_DEFAULTS
771 --This procedure is called from pybaladf.lct configuration file
772 PROCEDURE PAY_BAL_ADF_LOAD_ROW
773 (p_CATEGORY_NAME IN VARCHAR2
774 ,p_EFFECTIVE_START_DATE IN VARCHAR2
775 ,p_EFFECTIVE_END_DATE IN VARCHAR2
776 ,p_CAT_LEG_CODE IN VARCHAR2
777 ,p_LEGISLATION_CODE IN VARCHAR2
778 ,p_DIMENSION_NAME IN VARCHAR2
779 ,p_DIM_LEG_CODE IN VARCHAR2
780 ,p_ATTRIBUTE_NAME IN VARCHAR2
781 ,p_ATTR_LEG_CODE IN VARCHAR2
782 ,p_BUSINESS_GROUP_NAME IN VARCHAR2
783 ,p_CAT_BUS_GROUP_NAME IN VARCHAR2
784 ,p_DIM_BUS_GROUP_NAME IN VARCHAR2
785 ,p_ATTR_BUS_GROUP_NAME IN VARCHAR2
786 ,p_OWNER IN VARCHAR2
787 )IS
788
789 l_bal_attribute_default_id
790 PAY_BAL_ATTRIBUTE_DEFAULTS.BAL_ATTRIBUTE_DEFAULT_ID%TYPE;
791 l_balance_category_id PAY_BALANCE_CATEGORIES_F.BALANCE_CATEGORY_ID%TYPE;
792 l_legislation_code PAY_BAL_ATTRIBUTE_DEFAULTS.LEGISLATION_CODE%TYPE;
793 l_attribute_id PAY_BAL_ATTRIBUTE_DEFINITIONS.ATTRIBUTE_ID%TYPE;
794 l_balance_dimension_id PAY_BALANCE_DIMENSIONS.BALANCE_DIMENSION_ID%TYPE;
795 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
796 l_attr_bus_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
797 l_cat_bus_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
798 l_dim_bus_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
799 l_owner VARCHAR2(6);
800 l_session_id HR_OWNER_DEFINITIONS.SESSION_ID%TYPE;
801 l_session_id1 HR_OWNER_DEFINITIONS.SESSION_ID%TYPE;
802
803 --This cursor is used to select the balance dimension id
804 cursor csr_sel_bal_dim_id(p_DIMENSION_NAME VARCHAR2,
805 p_LEGISLATION_CODE VARCHAR2,
806 p_bg_id NUMBER) is
807 select balance_dimension_id
808 from pay_balance_dimensions
809 where dimension_name=p_DIMENSION_NAME
810 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
811 and (business_group_id =p_bg_id or business_group_id is null);
812
813 --This cursor is used to determine the attribute_id
814 cursor csr_sel_attrib_id(p_ATTRIBUTE_NAME VARCHAR2,
815 p_LEGISLATION_CODE VARCHAR2,
816 p_bg_id NUMBER) is
817 select attribute_id
818 from pay_bal_attribute_definitions
819 where attribute_name =p_ATTRIBUTE_NAME
820 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1)
821 and (business_group_id =p_bg_id or business_group_id is null);
822
823 --This cursor is used to determine the balance_category_id
824 cursor csr_sel_bal_cat_id(p_CATEGORY_NAME VARCHAR2,
825 p_ESD VARCHAR2,
826 p_EED VARCHAR2,
827 p_LEGISLATION_CODE VARCHAR2) is
828 select balance_category_id
829 from pay_balance_categories_f
830 where category_name =p_CATEGORY_NAME
831 and effective_start_date =to_date(p_ESD,'YYYY/MM/DD')
832 and effective_end_date =to_date(p_EED,'YYYY/MM/DD')
833 AND nvl(legislation_code,1) =nvl(p_LEGISLATION_CODE,1);
834
835 BEGIN
836
837 open csr_get_session_id;
838 fetch csr_get_session_id into l_session_id;
839 close csr_get_session_id;
840
841 open csr_get_hr_sess_id(l_session_id);
842 fetch csr_get_hr_sess_id into l_session_id1;
843 if(csr_get_hr_sess_id%notfound) then
844
845 hr_startup_data_api_support.create_owner_definition('PAY');
846 end if;
847 close csr_get_hr_sess_id;
848
849 --set user mode based on value of OWNER
850 if (p_OWNER ='SEED') then
851
852 hr_startup_data_api_support.enable_startup_mode('STARTUP');
853 --Need to set the AOL WHO columns properly. Hence call this
854 hr_general2.init_fndload(800,1);
855
856 elsif (p_OWNER='CUSTOM') then
857
858 if(p_BUSINESS_GROUP_NAME is null AND p_LEGISLATION_CODE is null) then
859 hr_startup_data_api_support.enable_startup_mode('GENERIC');
860
861 elsif(p_BUSINESS_GROUP_NAME is not null
862 AND p_LEGISLATION_CODE is null) then
863
864 hr_startup_data_api_support.enable_startup_mode('USER');
865
866 end if;
867 --Need to set the AOL WHO columns properly. Hence call this
868 hr_general2.init_fndload(800,-1);
869 end if;
870
871 open csr_bg_id(p_BUSINESS_GROUP_NAME);
872 fetch csr_bg_id into l_business_group_id;
873 close csr_bg_id;
874
875 --Get the balance category id
876 open csr_bg_id(p_CAT_BUS_GROUP_NAME);
877 fetch csr_bg_id into l_cat_bus_group_id;
878 close csr_bg_id;
879
880 open csr_sel_bal_cat_id(p_CATEGORY_NAME,p_EFFECTIVE_START_DATE,
881 p_EFFECTIVE_END_DATE,p_CAT_LEG_CODE);
882 fetch csr_sel_bal_cat_id into l_balance_category_id;
886 open csr_bg_id(p_DIM_BUS_GROUP_NAME);
883 close csr_sel_bal_cat_id;
884
885 --Get the balance dimension id
887 fetch csr_bg_id into l_dim_bus_group_id;
888 close csr_bg_id;
889
890 open csr_sel_bal_dim_id(p_DIMENSION_NAME,
891 p_DIM_LEG_CODE,l_dim_bus_group_id);
892 fetch csr_sel_bal_dim_id into l_balance_dimension_id;
893 close csr_sel_bal_dim_id;
894
895 --Get the attribute id
896 open csr_bg_id(p_ATTR_BUS_GROUP_NAME);
897 fetch csr_bg_id into l_attr_bus_group_id;
898 close csr_bg_id;
899
900 open csr_sel_attrib_id(p_ATTRIBUTE_NAME,
901 p_ATTR_LEG_CODE,l_attr_bus_group_id);
902 fetch csr_sel_attrib_id into l_attribute_id;
903 close csr_sel_attrib_id;
904
905 BEGIN
906
907 SELECT bal_attribute_default_id,
908 balance_category_id,
909 balance_dimension_id,
910 attribute_id,
911 legislation_code,
912 DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
913 INTO l_bal_attribute_default_id,
914 l_balance_category_id,
915 l_balance_dimension_id,
916 l_attribute_id,
917 l_legislation_code,
918 l_owner
919 FROM PAY_BAL_ATTRIBUTE_DEFAULTS
920 WHERE balance_category_id = l_balance_category_id
921 AND attribute_id = l_attribute_id
922 AND balance_dimension_id =l_balance_dimension_id;
923
924
925 EXCEPTION WHEN NO_DATA_FOUND
926 THEN
927
928 open csr_sel_bal_dim_id(p_DIMENSION_NAME,
929 p_DIM_LEG_CODE,l_dim_bus_group_id);
930 fetch csr_sel_bal_dim_id into l_balance_dimension_id;
931
932 if(csr_sel_bal_dim_id%found) then
933 open csr_sel_bal_cat_id(p_CATEGORY_NAME,p_EFFECTIVE_START_DATE,
934 p_EFFECTIVE_END_DATE,p_CAT_LEG_CODE);
935 fetch csr_sel_bal_cat_id into l_balance_category_id;
936
937 if(csr_sel_bal_cat_id%found) then
938 open csr_sel_attrib_id(p_ATTRIBUTE_NAME,
939 p_ATTR_LEG_CODE,l_attr_bus_group_id);
940 fetch csr_sel_attrib_id into l_attribute_id;
941
942 if(csr_sel_attrib_id%found) then
943
944 PAY_BAL_ATTRIBUTE_DEFAULT_API.create_bal_attribute_default
945 (p_balance_category_id =>l_balance_category_id
946 ,p_balance_dimension_id =>l_balance_dimension_id
947 ,p_attribute_id => l_attribute_id
948 ,p_business_group_id =>l_business_group_id
949 ,p_legislation_code =>p_LEGISLATION_CODE
950 ,p_bal_attribute_default_id =>l_bal_attribute_default_id
951 );
952 end if;
953 close csr_sel_attrib_id;
954 end if;
955 close csr_sel_bal_cat_id;
956 end if;
957 close csr_sel_bal_dim_id;
958
959 END;
960
961 END PAY_BAL_ADF_LOAD_ROW;
962 --
963 -- This procedure provides NLS support for pay_balance_categories_f
964 --
965 procedure translate_row_cat
966 (p_category_name in varchar2
967 ,p_user_category_name in varchar2
968 ,p_legislation_code in varchar2
969 ,p_bg_name in varchar2
970 ,p_owner in varchar2
971 )
972 is
973 --
974 cursor get_business_group_id
975 is
976 select business_group_id
977 from per_business_groups
978 where upper(name) = p_bg_name;
979 --
980 cursor get_bal_cat_id(p_bg_id number)
981 is
982 select balance_category_id
983 from pay_balance_categories_f
984 where category_name = p_category_name
985 and nvl(business_group_id, -1) = nvl(p_bg_id, -1)
986 and nvl(legislation_code, 'CORE') = nvl(p_legislation_code, 'CORE');
987 --
988 l_bg_id number;
989 l_bal_cat_id number;
990 --
991 BEGIN
992 if p_bg_name is not null then
993 open get_business_group_id;
994 fetch get_business_group_id into l_bg_id;
995 close get_business_group_id;
996 end if;
997 --
998 open get_bal_cat_id(l_bg_id);
999 fetch get_bal_cat_id into l_bal_cat_id;
1000 close get_bal_cat_id;
1001 hr_utility.trace('bal cat id is: '||to_char(l_bal_cat_id));
1002 --
1003 -- now determine what the who columns will be
1004 --
1005 if p_owner = 'SEED' then
1006 hr_general2.init_fndload(800,1);
1007 else
1008 hr_general2.init_fndload(800,-1);
1009 end if;
1010 --
1011 update pay_balance_categories_f
1012 set user_category_name = p_user_category_name
1013 where balance_category_id = l_bal_cat_id
1014 and userenv('LANG') = (select language_code
1015 from fnd_languages
1016 where installed_flag = 'B');
1017 --
1018 END translate_row_cat;
1019 --
1020 -- This procedure provides NLS support for pay_bal_attribute_definitions
1021 --
1022 procedure translate_row_attrib
1023 (p_attribute_name in varchar2
1024 ,p_user_attribute_name in varchar2
1025 ,p_legislation_code in varchar2
1026 ,p_bg_name in varchar2
1027 ,p_owner in varchar2
1028 )
1029 is
1030 --
1031 cursor get_business_group_id
1032 is
1033 select business_group_id
1034 from per_business_groups
1035 where upper(name) = p_bg_name;
1036 --
1037 cursor get_att_def_id(p_bg_id number)
1038 is
1039 select attribute_id
1040 from pay_bal_attribute_definitions
1041 where attribute_name = p_attribute_name
1042 and nvl(business_group_id, -1) = nvl(p_bg_id, -1)
1043 and nvl(legislation_code, 'CORE') = nvl(p_legislation_code, 'CORE');
1044 --
1045 l_bg_id number;
1046 l_att_def_id number;
1047 --
1048 BEGIN
1049 if p_bg_name is not null then
1050 open get_business_group_id;
1051 fetch get_business_group_id into l_bg_id;
1052 close get_business_group_id;
1053 end if;
1054 --
1055 open get_att_def_id(l_bg_id);
1056 fetch get_att_def_id into l_att_def_id;
1057 close get_att_def_id;
1058 hr_utility.trace('att def id is: '||to_char(l_att_def_id));
1059 --
1060 -- now determine what the who columns will be
1061 --
1062 if p_owner = 'SEED' then
1063 hr_general2.init_fndload(800,1);
1064 else
1065 hr_general2.init_fndload(800,-1);
1066 end if;
1067 --
1068 update pay_bal_attribute_definitions
1069 set user_attribute_name = p_user_attribute_name
1070 where attribute_id = l_att_def_id
1071 and userenv('LANG') = (select language_code
1072 from fnd_languages
1073 where installed_flag = 'B');
1074 --
1075 END translate_row_attrib;
1076 --
1077 END PAY_BALANCES_UPLOAD_PKG;
[Home] [Help]