[Home] [Help]
PACKAGE BODY: APPS.PAY_EMP_ACTION_ARCH
Source
1 PACKAGE BODY pay_emp_action_arch AS
2 /* $Header: pyempxfr.pkb 120.10.12010000.4 2008/08/06 07:10:48 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_emp_action_arch
21
22 Description : This package populates the global data
23 for payslip in pay_action_information table.
24 The action_information_categories that it
25 populates are:
26 - EMPLOYEE DEATLS
27 - ADDRESS DETAILS
28 - EMPLOYEE NET PAY DISTRIBUTION
29 - EMPLOYEE ACCRUALS.
30
31 Uses :
32
33 Change List
34 -----------
35 Date Name Vers Bug No Description
36 ----------- ---------- ------ ------- --------------------------
37 27-JUL-2001 asasthan 115.0 Created.
38 19-AUG-2001 ahanda 115.1 Changed package to populate
39 Employer Address and Message
40 again the payroll_action_id.
41 Also, caching values so that
42 we can save DB calls.
43 Added debug stmt and formated
44 28-AUG-2001 asasthan 115.2 Changed net pay distribution
45 and employee details.
46 29-AUG-2001 asasthan 115.3 Addedc some hr_utility messages.
47 17-SEP-2001 asasthan 115.4 Added pre_payment_id in
48 action_information16 for
49 EMPLOYEE NET PAY DISTRIBUTION.
50 18-SEP-2001 asasthan 115.5 Added pre_payment_assignment_action
51 to EMPLOYEE NET PAY DISTRIBUTION.
52 08-OCT-2001 asasthan 115.6 c_net_pay now gets only rows where
53 defined_balance_id is not null
54 Third Party rows are not archived
55 15-OCT-2001 asasthan 115.7 c_net_pay now archives
56 org_payment_method_name
57 For Employee Other Infoormation
58 will archive only those balances
59 that have a value > 0
60 29-NOV-2001 asasthan 115.9 Changes for in-house Bug 2120442
61 #1 Added the parameter p_run_pactid
62 in get_personal_info
63 #2 pay_us_emp_payslip_accrual_web.
64 get_emp_net_accrual now
65 is being passed the payroll action
66 id of run('R') and not of ('P,'U')
67 12-DEC-2001 asasthan 115.10 Defaulted p_run_pactid in
68 get_personal_info
69 22-JAN-2002 ahanda 115.11 Changed package to take care
70 of Multi Assignment Processing.
71 22-JAN-2002 ahanda 115.12 Changed dbdrv command.
72 28-JAN-2002 ahanda 115.13 Changed Net Pay Dist. to store
73 Org and Personal Payment Method ID.
74 29-JAN-2002 ahanda 115.14 Added dbdrv commands.
75 19-FEB-2002 ahanda 115.15 Added functions
76 - get_multi_legislative_rule
77 - get_multi_assignment_flag.
78 24-APR-2002 ahanda 115.17 Changed cursor c_get_organization in
79 arch_pay_action_level_data for
80 performance.Disabled index on column
81 element_type_id for cursor
82 c_regular_salary and c_houly salary.
83 13-JUN-2002 vpandya 115.18 'Employer Address', now postal_code
84 archives in act_info12 and region_3
85 archives in act_ino11.
86 17-JUN-2002 ahanda 115.19 Added code to archive Steps for each
87 assignment.
88 16-JUL-2002 ahanda 115.20 Changed insert_rows_thro_api_process
89 to insert data only if PL/SQL table
90 is populated.
91 15-AUG-2002 ahanda 115.21 Changed get_proposed_emp_salary for
92 performance.
93 27-SEP-2002 sodhingr 115.22 Changed get_personal_information
94 for GSCC compliance (removed default
95 clause)
96 01-NOV-2002 ahanda 115.23 Changed func get_defined_balance_id
97 to not error out if defbal not found
98 09-DEC-2002 joward 115.24 MLS enabled grade name
99 23-DEC-2002 joward 115.25 MLS enabled job name
100 12-Feb-2003 ekim 115.27 Made performance change to:
101 c_regular_salary,
102 c_hourly_salary for bug 2792737.
103 13-Mar-2003 ekim 115.28 Modified procedure
104 insert_rows_thro_api_process to
105 check for assignment_id of the
106 passing pl/sql table when inserting
107 create_action_information.
108 14-Mar-2003 ekim 115.29 2750949 Increased the precision of
109 ln_proposed_salary and
110 ln_pay_annualization_factor
111 to (20,5) from (17,2)
112 04-Apr-2003 ekim 115.30 2879910 Changed to ln_pay_basis_id from
113 lv_pay_basis to get pay basis in
114 if statement.
115 2879931 Added a cursor to get employer
116 phone number : lv_er_phone_number
117 19-JUL-2003 ahanda 115.31 Changed code to archive dates in
118 canonical format.
119 28-Jul-2003 vpandya 115.32 3053917 Added a parameter p_ytd_balcall_aaid
120 in to get_personal_information, and
121 added p_ppp_source_action_id and
122 p_ytd_balcall_aaid in to
123 get_employee_other_info. Changed
124 code in these procedures.
125 20-Jan-2004 vpandya 115.33 3379865 Changed get_employee_other_info:
126 if p_ytd_balcall_aaid is null, then
127 use p_run_action_id to get balance.
128 23-Jan-2004 rsethupa 115.34 3354127 11.5.10 Performance Changes
129 28-MAR-2004 ahanda 115.35 3536375 Net Pay distribution code was
130 assuming that not null source action
131 is sep check. This has been changed
132 to check for run type to take care
133 of Payment Method by Run Type.
134 16-APR-2004 rsethupa 115.36 3311866 US SS Payslip currency Format Enh.
135 Changed code to archive currency
136 data in canonical format for action
137 info categories 'EMPLOYEE DETAILS',
138 'EMPLOYEE NET PAY DISTRIBUTION' and
139 'EMPLOYEE ACCRUALS'.
140 12-AUG-2004 ahanda 115.37 3575803 Changed code to use data earned for
141 Pay Rate and Annualization factor.
142 20-MAY-2005 sodhingr 115.38 4225799 Archiving the value in canonical
143 format for EMPLOYEE OTHERS
144 INFORMATION
145 24-SEP-2005 rdhingra 115.39 4365487 Changed query in
146 cursor c_element_details in
147 procedure get_employee_other_info
148 28-SEP-2005 suman 115.40 4520091 Modified the cursor definition c_addr_line
149 in procedures get_employee_addr and
150 get_org_address(inside procedure
151 arch_pay_action_level_data)
152 15-OCT-2005 ahanda 115.41 4676875 Changed archiving for EMPLOYEE OTHERS
153 INFORMATION to be converted to
154 canonical if UOM is M or N.
155 Changed cursor c_element_details to
156 remove join for element links.
157 Backed out changes for bug 4520091.
158 24-APR-2006 pragupta 115.42 5182166 Added a new procedure
159 get_3rdparty_pay_distribution to
160 archive the Third Party Payments.
161 07-MAY-2006 ahanda 115.43 5209228 Added a new overloaded procedure -
162 - arch_pay_action_level_data
163 to be called from de-init code
164 11-MAY-2006 ahanda 115.44 4685928 Modifed get_3rdparty_pay_distribution
165 to store the correct action ID
166 02-OCT-2006 ahanda 115.45 Archiving accrual_code in action_info7
167 08-DEC-2006 ahanda 115.46 5692161 Changed get_employee_other_info to
168 archive values if <> 0
169 14-FEB-2006 kvsankar 115.47 5707497 Modified get_employee_other_info
170 to archive Unit Of Measure
171 05-JUL-2007 sausingh 115.48 5635335 Modified a condition in the cursor
172 c_net_pay in the procedure
173 get_net_pay_distribution to archive the bank details
174 of employer if the payment type is check .
175 31-MAR-2008 asgugupt 115.49 5585331 Replaced parameter p_curr_eff_date
176 with p_date_earned while opening c_employee_details
177 cursor in get_personal_information procedure
178 18-JUL-2008 mikarthi 115.51 7115367 While Archiving "EMPLOYEE OTHER INFORMATION" details
179 context at which the information was defined is
180 archived in to Action Info 13
181 *******************************************************************/
182
183 /******************************************************************
184 ** Package Local Variables
185 ******************************************************************/
186 gv_package VARCHAR2(100);
187
188 /*********************************************************************
189 Name : set_error_message
190 Purpose : This function sets error message only if it has not been
191 set before and returns it back.
192 Arguments :
193 Notes :
194 *********************************************************************/
195 FUNCTION set_error_message( p_error_message in varchar2 ) RETURN varchar2 is
196 BEGIN
197 if gv_error_message is null then
198 gv_error_message := p_error_message;
199 end if;
200 return gv_error_message;
201 END;
202
203
204 /*********************************************************************
205 Name : get_defined_balance_id
206 Purpose : This function returns the defined_balance_id for a given
207 Balance ID and Dimension.
208 Arguments :
209 Notes :
210 *********************************************************************/
211 FUNCTION get_defined_balance_id (
212 p_balance_id in number
213 ,p_balance_dimension in varchar2
214 ,p_legislation_code in varchar2)
215 RETURN NUMBER
216 IS
217 cursor c_get_defined_balance_id(cp_balance_id in number
218 ,cp_bal_dim in varchar2
219 ,cp_legislation_code in varchar2 ) is
220 select pdb.defined_balance_id
221 from pay_defined_balances pdb,
222 pay_balance_dimensions pbd
223 where pdb.balance_type_id = cp_balance_id
224 and pbd.database_item_suffix = cp_bal_dim
225 and pbd.balance_dimension_id = pdb.balance_dimension_id
226 and ((pbd.legislation_code = cp_legislation_code and
227 pbd.business_group_id is null)
228 or (pbd.legislation_code is null and
229 pbd.business_group_id is not null));
230
231 ln_defined_balance_id NUMBER;
232 lv_error_message VARCHAR2(200);
233
234 BEGIN
235 hr_utility.trace('opened c_get_defined_balance');
236 open c_get_defined_balance_id(p_balance_id,
237 p_balance_dimension,
238 p_legislation_code);
239
240 fetch c_get_defined_balance_id into ln_defined_balance_id;
241 if c_get_defined_balance_id%notfound then
242 /*********************************************************
243 ** If defined_balance_id not found then return null.
244 ** This will happen for the Hours YTD balance
245 *********************************************************/
246 hr_utility.trace('Defined balance id not found for... ' );
247 hr_utility.trace(' p_balance_id = ' || p_balance_id);
248 hr_utility.trace(' p_balance_dimension = ' || p_balance_dimension);
249 hr_utility.trace(' p_legislation_code = ' || p_legislation_code);
250 end if;
251 close c_get_defined_balance_id;
252 hr_utility.trace('ln_defined_balance_id = ' || ln_defined_balance_id);
253 return ln_defined_balance_id ;
254
255 END get_defined_balance_id;
256
257
258 /*********************************************************************
259 Name : get_multi_legislative_rule
260 Purpose : This function returns the if the legislative rule is
261 enabled for multiple assignment.
262 Arguments :
263 Notes : This would be defaulted to 'N'
264 *********************************************************************/
265 FUNCTION get_multi_legislative_rule(p_legislation_code in varchar2)
266
267 RETURN VARCHAR2
268 IS
269
270 cursor c_leg_rule (cp_legislation_code in varchar2) is
271 select 'x'
272 from pay_legislative_field_info
273 where field_name = 'MULTI_ASSIGNMENTS_FLAG'
274 and legislation_code = cp_legislation_code
275 and rule_mode = 'Y';
276
277 lv_multi_leg_rule VARCHAR2(1);
278 lv_procedure_name VARCHAR2(50);
279 BEGIN
280 lv_multi_leg_rule := 'N';
281 lv_procedure_name := '.get_multi_legislative_rule' ;
282 hr_utility.set_location(gv_package || lv_procedure_name, 10);
283 open c_leg_rule(p_legislation_code);
284 fetch c_leg_rule into lv_multi_leg_rule;
285 if c_leg_rule%found then
286 lv_multi_leg_rule := 'Y';
287 else
288 lv_multi_leg_rule := 'N';
289 end if;
290 close c_leg_rule;
291
292 hr_utility.trace('lv_multi_leg_rule = ' || lv_multi_leg_rule);
293 hr_utility.set_location(gv_package || lv_procedure_name, 100);
294 return(lv_multi_leg_rule);
295
296 END get_multi_legislative_rule;
297
298 /*********************************************************************
299 Name : get_multi_assignment_flag
300 Purpose : This function returns the flag for multiple assignment
301 payment is enabled or not.
302 Arguments :
303 Notes : This would be defaulted to 'N'
304 *********************************************************************/
305 FUNCTION get_multi_assignment_flag(p_payroll_id in number
306 ,p_effective_date in date)
307
308 RETURN VARCHAR2
309 IS
310
311 cursor c_get_payroll_info(cp_payroll_id in number
312 ,cp_effective_date in date) is
313 select multi_assignments_flag
314 from pay_payrolls_f
315 where payroll_id = cp_payroll_id
316 and cp_effective_date between effective_start_Date
317 and effective_end_date
318 and multi_assignments_flag = 'Y';
319
320 lv_multi_asg_flag VARCHAR2(1);
321 lv_procedure_name VARCHAR2(50);
322
323 BEGIN
324 lv_multi_asg_flag := 'N';
325 lv_procedure_name := '.get_multi_assignment_flag' ;
326 hr_utility.set_location(gv_package || lv_procedure_name, 10);
327 open c_get_payroll_info(p_payroll_id, p_effective_date);
328 fetch c_get_payroll_info into lv_multi_asg_flag;
329 if c_get_payroll_info%found then
330 lv_multi_asg_flag := 'Y';
331 else
332 lv_multi_asg_flag := 'N';
333 end if;
334 close c_get_payroll_info;
335 hr_utility.set_location(gv_package || lv_procedure_name, 30);
336 hr_utility.trace('lv_multi_asg_flag = ' || lv_multi_asg_flag);
337 return lv_multi_asg_flag;
338
339 END get_multi_assignment_flag;
340
341
342 /******************************************************************
343 Name : initialization_process
344 Purpose : The procedure initializes the PL/SQL table -
345 pay_emp_action_arch.lrr_act_tab,
346 pay_emp_action_arch.ltr_ppa_arch and
347 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals.
348 Arguments :
349 Notes :
350 ******************************************************************/
351 PROCEDURE initialization_process
352 IS
353 lv_procedure_name VARCHAR2(100);
354
355 BEGIN
356 lv_procedure_name := '.initialization_process';
357 hr_utility.set_location(gv_package || lv_procedure_name, 10);
358
359 if pay_emp_action_arch.lrr_act_tab.count > 0 then
360 hr_utility.set_location(gv_package || lv_procedure_name, 20);
361 for i in pay_emp_action_arch.lrr_act_tab.first ..
362 pay_emp_action_arch.lrr_act_tab.last loop
363 pay_emp_action_arch.lrr_act_tab(i).action_context_id := null;
364 pay_emp_action_arch.lrr_act_tab(i).action_context_type := null;
365 pay_emp_action_arch.lrr_act_tab(i).action_info_category := null;
366 pay_emp_action_arch.lrr_act_tab(i).jurisdiction_code := null;
367 pay_emp_action_arch.lrr_act_tab(i).act_info1 := null;
368 pay_emp_action_arch.lrr_act_tab(i).act_info2 := null;
369 pay_emp_action_arch.lrr_act_tab(i).act_info3 := null;
370 pay_emp_action_arch.lrr_act_tab(i).act_info4 := null;
371 pay_emp_action_arch.lrr_act_tab(i).act_info5 := null;
372 pay_emp_action_arch.lrr_act_tab(i).act_info6 := null;
373 pay_emp_action_arch.lrr_act_tab(i).act_info7 := null;
374 pay_emp_action_arch.lrr_act_tab(i).act_info8 := null;
375 pay_emp_action_arch.lrr_act_tab(i).act_info9 := null;
376 pay_emp_action_arch.lrr_act_tab(i).act_info10 := null;
377 pay_emp_action_arch.lrr_act_tab(i).act_info11 := null;
378 pay_emp_action_arch.lrr_act_tab(i).act_info12 := null;
379 pay_emp_action_arch.lrr_act_tab(i).act_info13 := null;
380 pay_emp_action_arch.lrr_act_tab(i).act_info14 := null;
381 pay_emp_action_arch.lrr_act_tab(i).act_info15 := null;
382 pay_emp_action_arch.lrr_act_tab(i).act_info16 := null;
383 pay_emp_action_arch.lrr_act_tab(i).act_info17 := null;
384 pay_emp_action_arch.lrr_act_tab(i).act_info18 := null;
385 pay_emp_action_arch.lrr_act_tab(i).act_info19 := null;
386 pay_emp_action_arch.lrr_act_tab(i).act_info20 := null;
387 pay_emp_action_arch.lrr_act_tab(i).act_info21 := null;
388 pay_emp_action_arch.lrr_act_tab(i).act_info22 := null;
389 pay_emp_action_arch.lrr_act_tab(i).act_info23 := null;
390 pay_emp_action_arch.lrr_act_tab(i).act_info24 := null;
391 pay_emp_action_arch.lrr_act_tab(i).act_info25 := null;
392 pay_emp_action_arch.lrr_act_tab(i).act_info26 := null;
393 pay_emp_action_arch.lrr_act_tab(i).act_info27 := null;
394 pay_emp_action_arch.lrr_act_tab(i).act_info28 := null;
395 pay_emp_action_arch.lrr_act_tab(i).act_info29 := null;
396 pay_emp_action_arch.lrr_act_tab(i).act_info30 := null;
397 end loop;
398 end if;
399 pay_emp_action_arch.lrr_act_tab.delete;
400 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals.delete;
401 hr_utility.set_location(gv_package || lv_procedure_name, 30);
402
403 if pay_emp_action_arch.ltr_ppa_arch.count > 0 then
404 hr_utility.set_location(gv_package || lv_procedure_name, 40);
405 for i in pay_emp_action_arch.ltr_ppa_arch.first ..
406 pay_emp_action_arch.ltr_ppa_arch.last loop
407 pay_emp_action_arch.ltr_ppa_arch(i).action_context_id := null;
408 pay_emp_action_arch.ltr_ppa_arch(i).action_context_type := null;
409 pay_emp_action_arch.ltr_ppa_arch(i).action_info_category := null;
410 pay_emp_action_arch.ltr_ppa_arch(i).jurisdiction_code := null;
411 pay_emp_action_arch.ltr_ppa_arch(i).act_info1 := null;
412 pay_emp_action_arch.ltr_ppa_arch(i).act_info2 := null;
413 pay_emp_action_arch.ltr_ppa_arch(i).act_info3 := null;
414 pay_emp_action_arch.ltr_ppa_arch(i).act_info4 := null;
415 pay_emp_action_arch.ltr_ppa_arch(i).act_info5 := null;
416 pay_emp_action_arch.ltr_ppa_arch(i).act_info6 := null;
417 pay_emp_action_arch.ltr_ppa_arch(i).act_info7 := null;
418 pay_emp_action_arch.ltr_ppa_arch(i).act_info8 := null;
419 pay_emp_action_arch.ltr_ppa_arch(i).act_info9 := null;
420 pay_emp_action_arch.ltr_ppa_arch(i).act_info10 := null;
421 pay_emp_action_arch.ltr_ppa_arch(i).act_info11 := null;
422 pay_emp_action_arch.ltr_ppa_arch(i).act_info12 := null;
423 pay_emp_action_arch.ltr_ppa_arch(i).act_info13 := null;
424 pay_emp_action_arch.ltr_ppa_arch(i).act_info14 := null;
425 pay_emp_action_arch.ltr_ppa_arch(i).act_info15 := null;
426 pay_emp_action_arch.ltr_ppa_arch(i).act_info16 := null;
427 pay_emp_action_arch.ltr_ppa_arch(i).act_info17 := null;
428 pay_emp_action_arch.ltr_ppa_arch(i).act_info18 := null;
429 pay_emp_action_arch.ltr_ppa_arch(i).act_info19 := null;
430 pay_emp_action_arch.ltr_ppa_arch(i).act_info20 := null;
431 pay_emp_action_arch.ltr_ppa_arch(i).act_info21 := null;
432 pay_emp_action_arch.ltr_ppa_arch(i).act_info22 := null;
433 pay_emp_action_arch.ltr_ppa_arch(i).act_info23 := null;
434 pay_emp_action_arch.ltr_ppa_arch(i).act_info24 := null;
435 pay_emp_action_arch.ltr_ppa_arch(i).act_info25 := null;
436 pay_emp_action_arch.ltr_ppa_arch(i).act_info26 := null;
437 pay_emp_action_arch.ltr_ppa_arch(i).act_info27 := null;
438 pay_emp_action_arch.ltr_ppa_arch(i).act_info28 := null;
439 pay_emp_action_arch.ltr_ppa_arch(i).act_info29 := null;
440 pay_emp_action_arch.ltr_ppa_arch(i).act_info30 := null;
441 end loop;
442 end if;
443 pay_emp_action_arch.ltr_ppa_arch.delete;
444
445 hr_utility.set_location(gv_package || lv_procedure_name, 50);
446 END initialization_process;
447
448
449
450 /******************************************************************
451 Name : insert_rows_thro_api_process
452 Purpose :
453 Arguments :
454 Notes :
455 ******************************************************************/
456 PROCEDURE insert_rows_thro_api_process(
457 p_action_context_id in number
458 ,p_action_context_type in varchar2
459 ,p_assignment_id in number
460 ,p_tax_unit_id in number
461 ,p_curr_pymt_eff_date in date
462 ,p_tab_rec_data in pay_emp_action_arch.action_info_table
463 )
464
465 IS
466 l_action_information_id_1 NUMBER ;
467 l_object_version_number_1 NUMBER ;
468 lv_procedure_name VARCHAR2(100);
469
470 BEGIN
471 lv_procedure_name := '.insert_rows_thro_api_process';
472 hr_utility.set_location(gv_package || lv_procedure_name, 10);
473
474 if p_tab_rec_data.count > 0 then
475 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
476 hr_utility.trace('Defining category '||
477 p_tab_rec_data(i).action_info_category);
478 hr_utility.trace('action_context_id = '|| p_action_context_id);
479 hr_utility.trace('jurisdiction_code '||
480 p_tab_rec_data(i).jurisdiction_code);
481 hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
482 hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
483 hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
484 hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
485 hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
486 hr_utility.trace('act_info6 is'|| p_tab_rec_data(i).act_info6);
487 hr_utility.trace('act_info30 is'|| p_tab_rec_data(i).act_info30);
488
489 hr_utility.set_location(gv_package || '.' || lv_procedure_name, 30);
490 pay_action_information_api.create_action_information(
491 p_action_information_id => l_action_information_id_1,
492 p_object_version_number => l_object_version_number_1,
493 p_action_information_category
494 => p_tab_rec_data(i).action_info_category,
495 p_action_context_id => p_action_context_id,
496 p_action_context_type => p_action_context_type,
497 p_jurisdiction_code => p_tab_rec_data(i).jurisdiction_code,
498 p_assignment_id => nvl(p_tab_rec_data(i).assignment_id,
499 p_assignment_id),
500 p_tax_unit_id => p_tax_unit_id,
501 p_effective_date => p_curr_pymt_eff_date,
502 p_action_information1 => p_tab_rec_data(i).act_info1,
503 p_action_information2 => p_tab_rec_data(i).act_info2,
504 p_action_information3 => p_tab_rec_data(i).act_info3,
505 p_action_information4 => p_tab_rec_data(i).act_info4,
506 p_action_information5 => p_tab_rec_data(i).act_info5,
507 p_action_information6 => p_tab_rec_data(i).act_info6,
508 p_action_information7 => p_tab_rec_data(i).act_info7,
509 p_action_information8 => p_tab_rec_data(i).act_info8,
510 p_action_information9 => p_tab_rec_data(i).act_info9,
511 p_action_information10 => p_tab_rec_data(i).act_info10,
512 p_action_information11 => p_tab_rec_data(i).act_info11,
513 p_action_information12 => p_tab_rec_data(i).act_info12,
514 p_action_information13 => p_tab_rec_data(i).act_info13,
515 p_action_information14 => p_tab_rec_data(i).act_info14,
516 p_action_information15 => p_tab_rec_data(i).act_info15,
517 p_action_information16 => p_tab_rec_data(i).act_info16,
518 p_action_information17 => p_tab_rec_data(i).act_info17,
519 p_action_information18 => p_tab_rec_data(i).act_info18,
520 p_action_information19 => p_tab_rec_data(i).act_info19,
521 p_action_information20 => p_tab_rec_data(i).act_info20,
522 p_action_information21 => p_tab_rec_data(i).act_info21,
523 p_action_information22 => p_tab_rec_data(i).act_info22,
524 p_action_information23 => p_tab_rec_data(i).act_info23,
525 p_action_information24 => p_tab_rec_data(i).act_info24,
526 p_action_information25 => p_tab_rec_data(i).act_info25,
527 p_action_information26 => p_tab_rec_data(i).act_info26,
528 p_action_information27 => p_tab_rec_data(i).act_info27,
529 p_action_information28 => p_tab_rec_data(i).act_info28,
530 p_action_information29 => p_tab_rec_data(i).act_info29,
531 p_action_information30 => p_tab_rec_data(i).act_info30
532 );
533
534 end loop;
535 end if;
536 hr_utility.set_location(gv_package || lv_procedure_name, 50);
537 END insert_rows_thro_api_process;
538
539
540
541 /************************************************************
542 ** Function gets the proposed employee salary from
543 ** per_pay_proposals. If the Salary Proposal is not specified
544 ** then it checks the Salary Basis for the employee, find out
545 ** the element associated with the Salary Basis and get the
546 ** value from the run results for the given period.
547 ** If the element associated with the Salary Basis is Regular
548 ** wages, then we get the value for input value of 'Rate'
549 ************************************************************/
550 FUNCTION get_proposed_emp_salary (
551 p_assignment_id in number
552 ,p_pay_basis_id in number
553 ,p_pay_bases_name in varchar2
554 ,p_date_earned in date
555 )
556 RETURN VARCHAR2 IS
557
558 cursor c_salary_proposal (cp_assignment_id in number,
559 cp_date_earned in date) is
560 select ppp.proposed_salary_n
561 from per_pay_proposals ppp
562 where ppp.assignment_id = cp_assignment_id
563 and ppp.change_date =
564 (select max(change_date)
565 from per_pay_proposals ppp1
566 where ppp1.assignment_id = cp_assignment_id
567 and ppp1.approved = 'Y'
568 and ppp1.change_date <= cp_date_earned);
569
570
571 cursor c_bases_element (cp_pay_basis_id in number,
572 cp_date_earned in date) is
573 select piv.element_type_id, piv.input_value_id
574 from pay_input_values_f piv,
575 per_pay_bases ppb
576 where ppb.pay_basis_id = cp_pay_basis_id
577 and ppb.input_value_id = piv.input_value_id
578 and cp_date_earned between piv.effective_start_date
579 and piv.effective_end_date;
580
581 cursor c_regular_salary (cp_input_value_id in number,
582 cp_assignment_id in number,
583 cp_date_earned in date ) is
584 select prrv.result_value
585 from pay_run_results prr,
586 pay_run_result_values prrv,
587 pay_input_values_f piv,
588 pay_assignment_actions paa,
589 pay_payroll_actions ppa
590 where prr.element_type_id + 0 = piv.element_type_id
591 and prr.run_result_id = prrv.run_result_id
592 and prr.source_type = 'E'
593 and piv.input_value_id = prrv.input_value_id
594 and piv.input_value_id = cp_input_value_id
595 and ppa.effective_date between piv.effective_start_date
596 and piv.effective_end_date
597 and paa.assignment_action_id = prr.assignment_action_id
598 and paa.assignment_id = cp_assignment_id
599 and ppa.payroll_action_id = paa.payroll_action_id
600 and ppa.effective_date = cp_date_earned;
601
602 cursor c_hourly_salary (cp_element_type_id in number,
603 cp_input_value_name in varchar2,
604 cp_assignment_id in number,
605 cp_date_earned in date ) is
606 select prrv.result_value
607 from pay_run_results prr,
608 pay_run_result_values prrv,
609 pay_input_values_f piv,
610 pay_assignment_actions paa,
611 pay_payroll_actions ppa
612 where prr.element_type_id + 0 = piv.element_type_id
613 and prr.run_result_id = prrv.run_result_id
614 and prr.source_type = 'E'
615 and piv.input_value_id = prrv.input_value_id
616 and piv.element_type_id = cp_element_type_id
617 and piv.name = cp_input_value_name
618 and ppa.effective_date between piv.effective_start_date
619 and piv.effective_end_date
620 and paa.assignment_action_id = prr.assignment_action_id
621 and paa.assignment_id = cp_assignment_id
622 and ppa.payroll_action_id = paa.payroll_action_id
623 and ppa.effective_date = cp_date_earned;
624
625 ln_element_type_id NUMBER;
626 ln_input_value_id NUMBER;
627 ln_proposed_salary NUMBER;
628 lv_procedure_name VARCHAR2(100);
629
630 BEGIN
631 lv_procedure_name := 'get_proposed_emp_salary';
632
633 open c_salary_proposal(p_assignment_id,
634 p_date_earned);
635 fetch c_salary_proposal into ln_proposed_salary;
636 if c_salary_proposal%notfound then
637 open c_bases_element(p_pay_basis_id,
638 p_date_earned);
639 fetch c_bases_element into ln_element_type_id, ln_input_value_id;
640 if c_bases_element%found then
641 if p_pay_bases_name <> 'HOURLY' then
642 open c_regular_salary(ln_input_value_id,
643 p_assignment_id,
644 p_date_earned);
645 fetch c_regular_salary into ln_proposed_salary;
646 if c_regular_salary%notfound then
647 ln_proposed_salary := 0;
648 end if;
649 close c_regular_salary;
650 else
651 open c_hourly_salary(ln_element_type_id,
652 'Rate',
653 p_assignment_id,
654 p_date_earned);
655 fetch c_hourly_salary into ln_proposed_salary;
656 if c_hourly_salary%notfound then
657 ln_proposed_salary := 0;
658 end if;
659 close c_hourly_salary;
660 end if;
661 end if;
662 close c_bases_element;
663
664 end if;
665 close c_salary_proposal;
666
667 return (ln_proposed_salary);
668
669 END get_proposed_emp_salary;
670
671
672 /************************************************************
673 ** Gets the Annualized factor for the Payroll
674 ** i.e. frequency of the Payroll
675 ** e.g. Week = 52
676 ** Semi-Month = 24
677 ** Month = 12
678 ** Hourly = No of working hours/day * 365
679 ** No of working hours/week * 52
680 ** No of working hours/month * 12
681 ** No of working hours/year * 1
682 ************************************************************/
683 FUNCTION get_emp_annualization_factor (
684 p_pay_basis_id in number
685 ,p_period_type in varchar2
686 ,p_pay_bases_name in varchar2
687 ,p_assignment_id in number
688 ,p_date_earned in date
689 )
690 return number is
691
692 cursor c_salary_details (cp_pay_basis_id in number) is
693 select ppb.pay_annualization_factor
694 from per_pay_bases ppb
695 where ppb.pay_basis_id = cp_pay_basis_id;
696
697 cursor c_payroll (cp_period_type in varchar2) is
698 select ptpt.number_per_fiscal_year
699 from per_time_period_types ptpt
700 where ptpt.period_type = cp_period_type;
701
702 ln_pay_annualization_factor NUMBER;
703 lv_procedure_name VARCHAR2(100);
704
705 BEGIN
706 lv_procedure_name := 'get_emp_annualization_factor';
707
708 open c_salary_details(p_pay_basis_id);
709 fetch c_salary_details into ln_pay_annualization_factor;
710 if c_salary_details%found then
711
712 if p_pay_bases_name ='PERIOD' and
713 ln_pay_annualization_factor is null then
714
715 open c_payroll(p_period_type);
716 fetch c_payroll into ln_pay_annualization_factor;
717 close c_payroll;
718
719 elsif p_pay_bases_name = 'HOURLY' and
720 (p_assignment_id is not null and p_date_earned is not null) then
721
722 ln_pay_annualization_factor
723 := pay_us_employee_payslip_web.get_asgn_annual_hours
724 (p_assignment_id,
725 p_date_earned);
726 end if;
727 end if;
728 close c_salary_details;
729
730 return (ln_pay_annualization_factor);
731
732 END get_emp_annualization_factor;
733
734
735 /******************************************************************
736 Name : get_employee_other_info
737 Purpose :
738 Arguments :
739 Notes :
740 ******************************************************************/
741 PROCEDURE get_employee_other_info (p_run_action_id in number
742 ,p_assignment_id in number
743 ,p_organization_id in number
744 ,p_business_group_id in number
745 ,p_curr_pymt_eff_date in date
746 ,p_tax_unit_id in number
747 ,p_ppp_source_action_id in number
748 default null
749 ,p_ytd_balcall_aaid in number
750 default null
751 )
752 IS
753 cursor c_organization_info(cp_organization_id in number
754 ,cp_org_info_context in varchar2
755 ) is
756 select org_information1, org_information2,
757 org_information3, org_information4,
758 org_information5, org_information6,
759 org_information7
760 from hr_organization_information
761 where org_information_context = cp_org_info_context
762 and organization_id = cp_organization_id;
763
764 cursor c_element_details(cp_element_type_id number
765 ,cp_input_value_id number
766 ,cp_assignment_id number
767 ) is
768 select pet.element_name, peev.screen_entry_value,
769 piv.name, piv.uom
770 from pay_element_types_f pet,
771 pay_element_entries_f pee,
772 pay_element_entry_values_f peev,
773 pay_input_values_f piv
774 where pet.element_type_id = cp_element_type_id
775 and pet.element_type_id = pee.element_type_id
776 and pee.assignment_id = cp_assignment_id
777 and pee.element_entry_id = peev.element_entry_id
778 and peev.input_value_id = cp_input_value_id
779 and piv.input_value_id = peev.input_value_id
780 and p_curr_pymt_eff_date between pet.effective_start_date
781 and pet.effective_end_date
782 and p_curr_pymt_eff_date between pee.effective_start_date
783 and pee.effective_end_date
784 and p_curr_pymt_eff_date between peev.effective_start_date
785 and peev.effective_end_date
786 and p_curr_pymt_eff_date between piv.effective_start_date
787 and piv.effective_end_date;
788
789 cursor c_defined_balance_id(cp_balance_type_id number
790 ,cp_balance_dimension_id number
791 ) is
792 select pdb.defined_balance_id,
793 pbt.balance_name,
794 substr(pbd.database_item_suffix,2),
795 pbt.balance_uom
796 from pay_defined_balances pdb,
797 pay_balance_dimensions pbd,
798 pay_balance_types pbt
799 where pbt.balance_type_id = cp_balance_type_id
800 and pbd.balance_dimension_id = cp_balance_dimension_id
801 and pbt.balance_type_id = pdb.balance_type_id
802 and pbd.balance_dimension_id = pdb.balance_dimension_id;
803
804
805 ln_index number;
806 lv_info_type VARCHAR2(150);
807 lv_name VARCHAR2(150);
808 lv_display_name VARCHAR2(150);
809 lv_value_type VARCHAR2(150);
810 lv_value VARCHAR2(150);
811 lv_uom VARCHAR2(150);
812 ln_element_type_id number;
813 ln_defined_balance_id number;
814 ln_input_value_id number;
815 ln_balance_type_id number;
816 ln_balance_dimension_id number;
817 lv_message VARCHAR2(150);
818 lv_organization_fetch VARCHAR2(1);
819 lv_exists VARCHAR2(1);
820
821 ln_run_action_id number;
822 lv_procedure_name VARCHAR2(100);
823
824 BEGIN
825 lv_procedure_name := '.get_employee_other_info';
826 lv_name := null;
827 lv_display_name := null;
828 lv_value_type := null;
829 lv_value := null;
830 lv_organization_fetch := 'N';
831 lv_exists := 'N';
832 hr_utility.set_location(gv_package || lv_procedure_name, 10);
833 hr_utility.trace('p_run_action_id = ' || p_run_action_id);
834 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
835 hr_utility.trace('p_organization_id = ' || p_organization_id);
836 hr_utility.trace('p_business_group_id = ' || p_business_group_id);
837 hr_utility.trace('p_curr_pymt_eff_date = '|| p_curr_pymt_eff_date);
838 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);
839
840 open c_organization_info(p_organization_id,
841 'Organization:Payslip Info');
842 hr_utility.set_location(gv_package || lv_procedure_name, 20);
843 loop
844 fetch c_organization_info into lv_info_type
845 ,ln_element_type_id
846 ,ln_input_value_id
847 ,ln_balance_type_id
848 ,ln_balance_dimension_id
849 ,lv_message
850 ,lv_display_name;
851
852 if c_organization_info%notfound then
853 exit;
854 end if;
855
856 hr_utility.set_location(gv_package || lv_procedure_name, 30);
857 hr_utility.trace('lv_info_type ' || lv_info_type);
858 hr_utility.trace('ln_element_type_id ' || ln_element_type_id);
859 hr_utility.trace('ln_input_value_id ' || ln_input_value_id);
860 hr_utility.trace('ln_balance_type_id ' || ln_balance_type_id);
861 hr_utility.trace('ln_balance_dimension_id ' || ln_balance_dimension_id);
862 hr_utility.trace('lv_message ' || lv_message);
863 hr_utility.trace('lv_display_name ' ||lv_display_name);
864
865 lv_organization_fetch := 'Y' ;
866
867 if lv_info_type ='ELEMENT' then
868 hr_utility.set_location(gv_package || lv_procedure_name, 40);
869 open c_element_details(ln_element_type_id
870 ,ln_input_value_id
871 ,p_assignment_id
872 );
873 fetch c_element_details into lv_name,
874 lv_value,
875 lv_value_type,
876 lv_uom;
877 if c_element_details%found then
878 hr_utility.set_location(gv_package || lv_procedure_name, 50);
879 hr_utility.trace('lv_uom ' || lv_uom);
880
881 if lv_uom in ('M', 'N', 'I') then
882 lv_value := fnd_number.number_to_canonical(lv_value);
883 end if;
884 hr_utility.trace('lv_value ' || lv_value);
885 hr_utility.trace('lv_name ' || lv_info_type);
886 hr_utility.trace('lv_value_type ' || lv_value_type);
887
888 ln_index := pay_emp_action_arch.lrr_act_tab.count;
889
890 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
891 := 'EMPLOYEE OTHER INFORMATION';
892 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
893 := '00-000-0000';
894 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
895 := p_organization_id;
896 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
897 := lv_info_type;
898 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
899 := nvl(lv_display_name,lv_name) ;
900 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
901 := lv_value_type ;
902 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
903 := lv_value;
904 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
905 := ln_element_type_id;
906 pay_emp_action_arch.lrr_act_tab(ln_index).act_info8
907 := ln_input_value_id;
908 -- Bug 5707497
909 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
910 := lv_uom;
911 end if;
912 close c_element_details;
913
914 elsif lv_info_type = 'BALANCE' then
915 hr_utility.set_location(gv_package || lv_procedure_name, 60);
916 open c_defined_balance_id(ln_balance_type_id
917 ,ln_balance_dimension_id);
918 fetch c_defined_balance_id into ln_defined_balance_id,
919 lv_name,
920 lv_value_type,
921 lv_uom;
922 hr_utility.trace('lv_name ' || lv_info_type);
923 hr_utility.trace('lv_value_type ' || lv_value_type);
924 hr_utility.trace('ln_defined_balance_id ' || ln_defined_balance_id);
925
926 if c_defined_balance_id%found then
927
928 if p_ppp_source_action_id is not null then
929 ln_run_action_id := p_ppp_source_action_id;
930 else
931 if lv_value_type = 'ASG_PAYMENTS' then
932 ln_run_action_id := p_run_action_id;
933 else
934 ln_run_action_id := nvl(p_ytd_balcall_aaid,p_run_action_id);
935 end if;
936 end if;
937
938 hr_utility.trace('p_ppp_source_action_id '||
939 p_ppp_source_action_id);
940 hr_utility.trace('ln_run_action_id '|| ln_run_action_id);
941
942 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
943 lv_value := nvl(pay_balance_pkg.get_value(ln_defined_balance_id,
944 ln_run_action_id),0);
945 if lv_value <> 0 then
946 hr_utility.set_location(gv_package || lv_procedure_name, 110);
947 ln_index := pay_emp_action_arch.lrr_act_tab.count;
948
949 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
950 := 'EMPLOYEE OTHER INFORMATION';
951 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
952 := '00-000-0000';
953 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
954 := p_organization_id;
955 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
956 := lv_info_type;
957 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
958 := nvl(lv_display_name,lv_name) ;
959 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
960 := lv_value_type ;
961 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
962 := fnd_number.number_to_canonical(lv_value); /*bug 4225799*/
963 pay_emp_action_arch.lrr_act_tab(ln_index).act_info9
964 := ln_balance_type_id;
965 pay_emp_action_arch.lrr_act_tab(ln_index).act_info10
966 := ln_balance_dimension_id;
967 -- Bug 5707497
968 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
969 := lv_uom;
970 end if;
971 end if;
972 close c_defined_balance_id;
973
974 end if;
975
976 hr_utility.trace('ln_index in get_employee_other_info proc is '
977 || pay_emp_action_arch.lrr_act_tab.count);
978 hr_utility.trace('lv_info_type is'||lv_info_type);
979
980 end loop;
981 close c_organization_info;
982
983 if lv_organization_fetch = 'N' then
984 open c_organization_info(p_business_group_id,
985 'Business Group:Payslip Info');
986 hr_utility.trace('Opened for Business Group:Payslip Info');
987 loop
988 fetch c_organization_info into lv_info_type
989 ,ln_element_type_id
990 ,ln_input_value_id
991 ,ln_balance_type_id
992 ,ln_balance_dimension_id
993 ,lv_message
994 ,lv_display_name;
995
996 if c_organization_info%notfound then
997 exit;
998 end if;
999 hr_utility.set_location(gv_package || lv_procedure_name, 140);
1000 hr_utility.trace('lv_info_type ' || lv_info_type);
1001 hr_utility.trace('ln_element_type_id ' || ln_element_type_id);
1002 hr_utility.trace('ln_input_value_id ' || ln_input_value_id);
1003 hr_utility.trace('ln_balance_type_id ' || ln_balance_type_id);
1004 hr_utility.trace('ln_balance_dimension_id' || ln_balance_dimension_id);
1005 hr_utility.trace('lv_message ' || lv_message);
1006 hr_utility.trace('lv_display_name ' || lv_display_name);
1007
1008 if lv_info_type ='ELEMENT' then
1009 open c_element_details(ln_element_type_id
1010 ,ln_input_value_id
1011 ,p_assignment_id
1012 );
1013 fetch c_element_details into lv_name,
1014 lv_value,
1015 lv_value_type,
1016 lv_uom;
1017 --if c_element_details%notfound then
1018 if c_element_details%found then
1019
1020 hr_utility.trace('lv_uom ' || lv_uom);
1021 if lv_uom in ('M', 'N', 'I') then
1022 lv_value := fnd_number.number_to_canonical(lv_value);
1023 end if;
1024
1025 ln_index := pay_emp_action_arch.lrr_act_tab.count;
1026 hr_utility.trace('lv_name ' || lv_info_type);
1027 hr_utility.trace('lv_value ' || lv_value);
1028 hr_utility.trace('lv_value_type ' || lv_value_type);
1029
1030 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
1031 := 'EMPLOYEE OTHER INFORMATION';
1032 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1033 := '00-000-0000';
1034 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
1035 := p_business_group_id;
1036 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
1037 := lv_info_type;
1038 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
1039 := nvl(lv_display_name,lv_name) ;
1040 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
1041 := lv_value_type ;
1042 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
1043 := lv_value;
1044 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
1045 := ln_element_type_id;
1046 pay_emp_action_arch.lrr_act_tab(ln_index).act_info8
1047 := ln_input_value_id;
1048 -- Bug 5707497
1049 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
1050 := lv_uom;
1051 end if;
1052 close c_element_details;
1053 elsif lv_info_type = 'BALANCE' then
1054 hr_utility.set_location(gv_package || lv_procedure_name, 210);
1055
1056 open c_defined_balance_id(ln_balance_type_id
1057 ,ln_balance_dimension_id);
1058
1059 hr_utility.set_location(gv_package || lv_procedure_name, 220);
1060 fetch c_defined_balance_id into ln_defined_balance_id,
1061 lv_name,
1062 lv_value_type,
1063 lv_uom;
1064 hr_utility.trace('ln_balance_type_id' || ln_balance_type_id);
1065 hr_utility.trace('lv_name ' || lv_info_type);
1066 hr_utility.trace('lv_value_type ' || lv_value_type);
1067 hr_utility.trace('ln_defined_balance_id ' || ln_defined_balance_id);
1068 if c_defined_balance_id%found then
1069
1070 if p_ppp_source_action_id is not null then
1071 ln_run_action_id := p_ppp_source_action_id;
1072 else
1073 if lv_value_type = 'ASG_PAYMENTS' then
1074 ln_run_action_id := p_run_action_id;
1075 else
1076 ln_run_action_id := nvl(p_ytd_balcall_aaid,
1077 p_run_action_id);
1078 end if;
1079 end if;
1080
1081 hr_utility.trace('p_ppp_source_action_id '||
1082 p_ppp_source_action_id);
1083 hr_utility.trace('ln_run_action_id '|| ln_run_action_id);
1084
1085 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
1086
1087 lv_value := nvl(pay_balance_pkg.get_value(ln_defined_balance_id,
1088 ln_run_action_id),0);
1089 if lv_value <> 0 then
1090 ln_index := pay_emp_action_arch.lrr_act_tab.count;
1091
1092 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
1093 := 'EMPLOYEE OTHER INFORMATION';
1094 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1095 := '00-000-0000';
1096 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
1097 := p_business_group_id;
1098 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
1099 := lv_info_type;
1100 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
1101 := nvl(lv_display_name,lv_name) ;
1102 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
1103 := lv_value_type ;
1104 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
1105 := fnd_number.number_to_canonical(lv_value); /*bug 4225799*/
1106 pay_emp_action_arch.lrr_act_tab(ln_index).act_info9
1107 := ln_balance_type_id;
1108 pay_emp_action_arch.lrr_act_tab(ln_index).act_info10
1109 := ln_balance_dimension_id;
1110 -- Bug 5707497
1111 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
1112 := lv_uom;
1113 end if; -- lv_value > 0 then
1114 end if;
1115 close c_defined_balance_id;
1116
1117 end if;
1118 end loop;
1119 close c_organization_info;
1120 end if;
1121 END get_employee_other_info;
1122
1123
1124 /******************************************************************
1125 Name : get_employee_accruals
1126 Purpose :
1127 Arguments :
1128 Notes :
1129 ******************************************************************/
1130 PROCEDURE get_employee_accruals(p_assactid in number
1131 ,p_run_action_id in number
1132 ,p_assignment_id in number
1133 ,p_effective_date in date
1134 ,p_date_earned in date
1135 )
1136 IS
1137 ln_total_acc_category NUMBER;
1138 ln_index NUMBER;
1139 lv_procedure_name VARCHAR2(100);
1140
1141 BEGIN
1142 lv_procedure_name := 'get_employee_accruals';
1143 hr_utility.trace('Entered get_employee_accruals');
1144
1145 pay_us_emp_payslip_accrual_web.get_emp_net_accrual (
1146 p_assignment_action_id => p_run_action_id
1147 ,p_assignment_id => p_assignment_id
1148 ,p_cur_earned_date => p_date_earned
1149 ,p_total_acc_category => ln_total_acc_category
1150 );
1151
1152 if pay_us_emp_payslip_accrual_web.ltr_assignment_accruals.count > 0 then
1153 for i in pay_us_emp_payslip_accrual_web.ltr_assignment_accruals.first ..
1154 pay_us_emp_payslip_accrual_web.ltr_assignment_accruals.last loop
1155
1156 ln_index := pay_emp_action_arch.lrr_act_tab.count;
1157
1158 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
1159 := 'EMPLOYEE ACCRUALS';
1160 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1161 := '00-000-0000';
1162 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
1163 := pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_category;
1164 /* Bug 3311866*/
1165 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
1166 := fnd_number.number_to_canonical
1167 (pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_cur_value);
1168 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
1169 := fnd_number.number_to_canonical
1170 (pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_net_value);
1171 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
1172 := pay_us_emp_payslip_accrual_web.ltr_assignment_accruals(i).accrual_code;
1173 end loop;
1174 end if;
1175
1176 hr_utility.trace('Leaving get_employee_accruals');
1177
1178 END get_employee_accruals;
1179
1180
1181 /******************************************************************
1182 Name : get_organization_name
1183 Purpose :
1184 Arguments :
1185 Notes :
1186 ******************************************************************/
1187 FUNCTION get_organization_name(p_organization_id in number)
1188 RETURN varchar2 IS
1189
1190 cursor c_organization_name is
1191 select name
1192 from hr_organization_units
1193 where organization_id = p_organization_id;
1194
1195 lv_organization_name VARCHAR2(240);
1196 lv_exists VARCHAR2(1);
1197 ln_index NUMBER;
1198 lv_procedure_name VARCHAR2(100);
1199
1200 BEGIN
1201 lv_procedure_name := 'get_organization_name';
1202 lv_exists := 'N';
1203 hr_utility.trace('Entered get_organization_name');
1204 if pay_emp_action_arch.ltr_organizations.count > 0 then
1205 for i in pay_emp_action_arch.ltr_organizations.first ..
1206 pay_emp_action_arch.ltr_organizations.last loop
1207 if pay_emp_action_arch.ltr_organizations(i).id = p_organization_id then
1208 lv_organization_name := pay_emp_action_arch.ltr_organizations(i).name;
1209 lv_exists := 'Y';
1210 exit;
1211 end if;
1212 end loop;
1213 end if;
1214
1215 if lv_exists = 'N' then
1216 open c_organization_name;
1217 fetch c_organization_name into lv_organization_name;
1218 close c_organization_name;
1219 ln_index := pay_emp_action_arch.ltr_organizations.count;
1220 pay_emp_action_arch.ltr_organizations(ln_index).id := p_organization_id;
1221 pay_emp_action_arch.ltr_organizations(ln_index).name := lv_organization_name;
1222 end if;
1223
1224 hr_utility.trace('Leaving get_organization_name');
1225 return(lv_organization_name);
1226 EXCEPTION
1227 when others then
1228 hr_utility.trace('Error in ' || lv_procedure_name ||
1229 to_char(sqlcode) || '-' || sqlerrm);
1230 raise hr_utility.hr_error;
1231
1232 END get_organization_name ;
1233
1234
1235 /******************************************************************
1236 Name : get_location
1237 Purpose :
1238 Arguments :
1239 Notes :
1240 ******************************************************************/
1241 FUNCTION get_location(p_location_id in number)
1242
1243 RETURN varchar2 IS
1244
1245 cursor c_location(cp_location_id in number) is
1246 select location_code
1247 from hr_locations_all
1248 where location_id = cp_location_id;
1249
1250 lv_location_name VARCHAR2(240);
1251 lv_exists VARCHAR2(1);
1252 ln_index NUMBER;
1253 lv_procedure_name VARCHAR2(100);
1254
1255 BEGIN
1256 lv_procedure_name := 'get_location';
1257 lv_exists := 'N';
1258 hr_utility.trace('Entered get_location');
1259
1260 if pay_emp_action_arch.ltr_location.count > 0 then
1261 for i in pay_emp_action_arch.ltr_location.first ..
1262 pay_emp_action_arch.ltr_location.last loop
1263 if pay_emp_action_arch.ltr_location(i).id = p_location_id then
1264 lv_location_name := pay_emp_action_arch.ltr_location(i).name;
1265 lv_exists := 'Y';
1266 exit;
1267 end if;
1268 end loop;
1269 end if;
1270
1271 if lv_exists = 'N' then
1272 open c_location(p_location_id);
1273 fetch c_location into lv_location_name;
1274 close c_location;
1275 ln_index := pay_emp_action_arch.ltr_location.count;
1276 pay_emp_action_arch.ltr_location(ln_index).id := p_location_id;
1277 pay_emp_action_arch.ltr_location(ln_index).name := lv_location_name;
1278 end if;
1279
1280 hr_utility.trace('Leaving get_location');
1281
1282 return(lv_location_name);
1283 EXCEPTION
1284 when others then
1285 return(null);
1286
1287 END get_location ;
1288
1289
1290 /******************************************************************
1291 Name : get_job_name
1292 Purpose :
1293 Arguments :
1294 Notes :
1295 ******************************************************************/
1296 FUNCTION get_job_name(p_job_id in number
1297 ,p_effective_date in date)
1298 RETURN varchar2 IS
1299
1300 cursor c_job_name(cp_job_id in number
1301 ,cp_effective_date in date
1302 ) is
1303 select name
1304 from per_jobs_vl
1305 where job_id = cp_job_id
1306 and date_from <= cp_effective_date
1307 and nvl(date_to, cp_effective_date) >= cp_effective_date
1308 order by date_from desc;
1309
1310 lv_job_name VARCHAR2(240);
1311 lv_exists VARCHAR2(1);
1312 ln_index NUMBER;
1313 lv_procedure_name VARCHAR2(100);
1314
1315 BEGIN
1316 lv_procedure_name := 'get_job_name';
1317 lv_exists := 'N';
1318 hr_utility.trace('Entered get_job_name');
1319 if pay_emp_action_arch.ltr_jobs.count > 0 then
1320 for i in pay_emp_action_arch.ltr_jobs.first ..
1321 pay_emp_action_arch.ltr_jobs.last loop
1322 if pay_emp_action_arch.ltr_jobs(i).id = p_job_id then
1323 lv_job_name := pay_emp_action_arch.ltr_jobs(i).name;
1324 lv_exists := 'Y';
1325 exit;
1326 end if;
1327 end loop;
1328 end if;
1329
1330 if lv_exists = 'N' then
1331 open c_job_name(p_job_id, p_effective_date);
1332 fetch c_job_name into lv_job_name;
1333 close c_job_name;
1334 ln_index := pay_emp_action_arch.ltr_jobs.count;
1335 pay_emp_action_arch.ltr_jobs(ln_index).id := p_job_id;
1336 pay_emp_action_arch.ltr_jobs(ln_index).name := lv_job_name;
1337 end if;
1338
1339 hr_utility.trace('Leaving get_job_name');
1340 return(lv_job_name);
1341
1342 END get_job_name ;
1343
1344
1345 /******************************************************************
1346 Name : get_position
1347 Purpose :
1348 Arguments :
1349 Notes :
1350 ******************************************************************/
1351 FUNCTION get_position(p_position_id in number
1352 ,p_effective_date in date)
1353 RETURN varchar2 IS
1354
1355 cursor c_position_name(cp_position_id in number
1356 ,cp_effective_date in date) is
1357 select name
1358 from per_positions
1359 where position_id = cp_position_id
1360 and cp_effective_date between date_effective
1361 and nvl(date_end,cp_effective_date) ;
1362
1363 lv_position_name VARCHAR2(240);
1364 lv_exists VARCHAR2(1);
1365 ln_index NUMBER;
1366 lv_procedure_name VARCHAR2(100);
1367
1368 BEGIN
1369 lv_procedure_name := 'get_position';
1370 lv_exists := 'N';
1371 hr_utility.trace('Entered get_position');
1372 if pay_emp_action_arch.ltr_positions.count > 0 then
1373 for i in pay_emp_action_arch.ltr_positions.first ..
1374 pay_emp_action_arch.ltr_positions.last loop
1375 if pay_emp_action_arch.ltr_positions(i).id = p_position_id then
1376 lv_position_name := pay_emp_action_arch.ltr_positions(i).name;
1377 lv_exists := 'Y';
1378 exit;
1379 end if;
1380 end loop;
1381 end if;
1382
1383 if lv_exists = 'N' then
1384 open c_position_name(p_position_id, p_effective_date);
1385 fetch c_position_name into lv_position_name;
1386 close c_position_name;
1387 ln_index := pay_emp_action_arch.ltr_positions.count;
1388 pay_emp_action_arch.ltr_positions(ln_index).id := p_position_id;
1389 pay_emp_action_arch.ltr_positions(ln_index).name := lv_position_name;
1390 end if;
1391
1392 hr_utility.trace('Leaving get_position');
1393
1394 return(lv_position_name);
1395
1396 END get_position ;
1397
1398
1399 /******************************************************************
1400 Name : get_pay_basis
1401 Purpose :
1402 Arguments :
1403 Notes :
1404 ******************************************************************/
1405 FUNCTION get_pay_basis(p_pay_basis_id in number
1406 ,p_effective_date in date)
1407 RETURN varchar2 IS
1408
1409 cursor c_pay_basis(cp_pay_basis_id in number
1410 ,cp_effective_date in date) is
1411 select ppb.name
1412 from per_pay_bases ppb,
1413 pay_input_values_f piv
1414 where ppb.pay_basis_id = cp_pay_basis_id
1415 and piv.input_value_id = ppb.input_value_id
1416 and p_effective_date between piv.effective_start_date
1417 and piv.effective_end_date;
1418
1419 lv_pay_basis VARCHAR2(240);
1420 lv_exists VARCHAR2(1);
1421 ln_index number;
1422 lv_procedure_name VARCHAR2(100);
1423
1424 BEGIN
1425 lv_procedure_name := 'get_pay_basis';
1426 lv_exists := 'N';
1427 hr_utility.trace('Entered get_pay_basis');
1428 if pay_emp_action_arch.ltr_pay_basis.count > 0 then
1429 for i in pay_emp_action_arch.ltr_pay_basis.first ..
1430 pay_emp_action_arch.ltr_pay_basis.last loop
1431 if pay_emp_action_arch.ltr_pay_basis(i).id = p_pay_basis_id then
1432 lv_pay_basis := pay_emp_action_arch.ltr_pay_basis(i).name;
1433 lv_exists := 'Y';
1434 exit;
1435 end if;
1436 end loop;
1437 end if;
1438
1439 if lv_exists = 'N' then
1440 open c_pay_basis(p_pay_basis_id, p_effective_date);
1441 fetch c_pay_basis into lv_pay_basis;
1442 close c_pay_basis;
1443 ln_index := pay_emp_action_arch.ltr_pay_basis.count;
1444 pay_emp_action_arch.ltr_pay_basis(ln_index).id := p_pay_basis_id;
1445 pay_emp_action_arch.ltr_pay_basis(ln_index).name := lv_pay_basis;
1446 end if;
1447
1448 hr_utility.trace('Leaving get_pay_basis');
1449
1450 return(lv_pay_basis);
1451
1452 END get_pay_basis ;
1453
1454
1455 /******************************************************************
1456 Name : get_frequency
1457 Purpose :
1458 Arguments :
1459 Notes :
1460 ******************************************************************/
1461 FUNCTION get_frequency(p_frequency in varchar2
1462 ,p_effective_date in date
1463 )
1464 RETURN varchar2 IS
1465
1466 cursor c_get_asg_frequency(cp_frequency in varchar2
1467 ,cp_effective_date in date) is
1468 select meaning
1469 from hr_lookups hl
1470 where hl.lookup_type = 'FREQUENCY'
1471 and hl.enabled_flag = 'Y'
1472 and hl.lookup_code = cp_frequency
1473 and cp_effective_date between nvl(hl.start_date_active, cp_effective_date)
1474 and nvl(hl.end_date_active, cp_effective_date);
1475 lv_frequency_desc VARCHAR2(240);
1476 lv_procedure_name VARCHAR2(100);
1477
1478 BEGIN
1479 lv_procedure_name := 'get_frequency';
1480 hr_utility.trace('Entered get_frequency');
1481 open c_get_asg_frequency(p_frequency, p_effective_date);
1482 fetch c_get_asg_frequency into lv_frequency_desc;
1483 close c_get_asg_frequency;
1484
1485 hr_utility.trace('Leaving get_frequency');
1486 return(lv_frequency_desc);
1487
1488 END get_frequency ;
1489
1490
1491 /******************************************************************
1492 Name : get_grade
1493 Purpose :
1494 Arguments :
1495 Notes :
1496 ******************************************************************/
1497 FUNCTION get_grade(p_grade_id in number
1498 ,p_effective_date in date
1499 )
1500 RETURN varchar2 IS
1501
1502 cursor c_grade(cp_grade_id in number
1503 ,cp_effective_date in date) is
1504 select name
1505 from per_grades_vl
1506 where grade_id = cp_grade_id
1507 and date_from <= cp_effective_date
1508 and nvl(date_to, cp_effective_date) >= cp_effective_date;
1509
1510 lv_grade VARCHAR2(240);
1511 lv_exists VARCHAR2(1);
1512 ln_index number;
1513 lv_procedure_name VARCHAR2(100);
1514
1515 BEGIN
1516 lv_procedure_name := 'get_grade';
1517 lv_exists := 'N';
1518 hr_utility.trace('Entered get_grades');
1519 if pay_emp_action_arch.ltr_grades.count > 0 then
1520 for i in pay_emp_action_arch.ltr_grades.first ..
1521 pay_emp_action_arch.ltr_grades.last loop
1522 if pay_emp_action_arch.ltr_grades(i).id = p_grade_id then
1523 lv_grade := pay_emp_action_arch.ltr_grades(i).name;
1524 lv_exists := 'Y';
1525 exit;
1526 end if;
1527 end loop;
1528 end if;
1529
1530 if lv_exists = 'N' then
1531 open c_grade(p_grade_id, p_effective_date);
1532 fetch c_grade into lv_grade;
1533 close c_grade;
1534 ln_index := pay_emp_action_arch.ltr_grades.count;
1535 pay_emp_action_arch.ltr_grades(ln_index).id := p_grade_id;
1536 pay_emp_action_arch.ltr_grades(ln_index).name := lv_grade;
1537 end if;
1538
1539 return(lv_grade);
1540
1541 END get_grade ;
1542
1543
1544 /******************************************************************
1545 Name : get_bargaining_unit
1546 Purpose :
1547 Arguments :
1548 Notes :
1549 ******************************************************************/
1550 FUNCTION get_bargaining_unit(p_bargaining_unit in varchar2
1551 ,p_effective_date in date
1552 )
1553 RETURN varchar2 IS
1554
1555 cursor c_get_bargaining_unit(cp_bargaining_unit in varchar2
1556 ,cp_effective_date in date) is
1557 select meaning
1558 from hr_lookups hl
1559 where hl.lookup_type = 'BARGAINING_UNIT_CODE'
1560 and hl.enabled_flag = 'Y'
1561 and hl.lookup_code = cp_bargaining_unit
1562 and cp_effective_date between nvl(hl.start_date_active, cp_effective_date)
1563 and nvl(hl.end_date_active, cp_effective_date);
1564 lv_bargaining_unit VARCHAR2(240);
1565 lv_procedure_name VARCHAR2(100);
1566
1567 BEGIN
1568 lv_procedure_name := 'get_bargaining_unit';
1569 hr_utility.trace('Entered get_bargaining_unit');
1570 open c_get_bargaining_unit(p_bargaining_unit, p_effective_date);
1571 fetch c_get_bargaining_unit into lv_bargaining_unit;
1572 close c_get_bargaining_unit;
1573
1574 hr_utility.trace('Leaving get_bargaining_unit');
1575 return(lv_bargaining_unit);
1576
1577 END get_bargaining_unit ;
1578
1579
1580 /******************************************************************
1581 Name : get_collective_agreement
1582 Purpose :
1583 Arguments :
1584 Notes :
1585 ******************************************************************/
1586 FUNCTION get_collective_agreement(
1587 p_collective_agreement_id in number
1588 ,p_effective_date in date
1589 )
1590 RETURN varchar2 IS
1591
1592 cursor c_get_collective_agreement(cp_collective_agreement_id in number
1593 ,cp_effective_date in date) is
1594 select name
1595 from per_collective_agreements
1596 where collective_agreement_id = cp_collective_agreement_id
1597 and start_date <= cp_effective_date
1598 and nvl(end_date, cp_effective_date) >= cp_effective_date;
1599
1600 lv_collective_agreement VARCHAR2(240);
1601 lv_procedure_name VARCHAR2(100);
1602
1603 BEGIN
1604 lv_procedure_name := 'get_collective_agreement';
1605 hr_utility.trace('Entered get_collective_agreement');
1606 open c_get_collective_agreement(p_collective_agreement_id
1607 ,p_effective_date);
1608 fetch c_get_collective_agreement into lv_collective_agreement;
1609 close c_get_collective_agreement;
1610 hr_utility.trace('Leaving get_collective_agreement');
1611 return(lv_collective_agreement);
1612
1613 END get_collective_agreement ;
1614
1615
1616 /******************************************************************
1617 Name : get_contract
1618 Purpose :
1619 Arguments :
1620 Notes :
1621 ******************************************************************/
1622 FUNCTION get_contract(p_contract_id in number
1623 ,p_effective_date in date
1624 )
1625 RETURN varchar2 IS
1626
1627 cursor c_get_contract(cp_contract_id in number
1628 ,cp_effective_date in date) is
1629 select reference
1630 from per_contracts
1631 where contract_id = cp_contract_id
1632 and p_effective_date between effective_start_date
1633 and effective_end_date;
1634 lv_contract VARCHAR2(240);
1635 lv_procedure_name VARCHAR2(100);
1636
1637 BEGIN
1638 lv_procedure_name := 'get_contract';
1639 hr_utility.trace('Entered get_contract');
1640 open c_get_contract(p_contract_id, p_effective_date);
1641 fetch c_get_contract into lv_contract;
1642 close c_get_contract;
1643
1644 hr_utility.trace('Leaving get_contract');
1645 return(lv_contract);
1646 END get_contract ;
1647
1648
1649 /******************************************************************
1650 Name : get_hourly_salaried_code
1651 Purpose :
1652 Arguments :
1653 Notes :
1654 ******************************************************************/
1655 FUNCTION get_hourly_salaried_code(p_hourly_salaried_code in varchar2
1656 ,p_effective_date in date
1657 )
1658 RETURN varchar2 IS
1659
1660 cursor c_get_hourly_salaried_code(cp_hourly_salaried_code in varchar2
1661 ,cp_effective_date in date) is
1662 select hl.meaning
1663 from hr_lookups hl
1664 where hl.lookup_type='HOURLY_SALARIED_CODE'
1665 and hl.lookup_code = cp_hourly_salaried_code
1666 and hl.enabled_flag='Y'
1667 and cp_effective_date between
1668 nvl(hl.start_date_active, cp_effective_date) and
1669 nvl(hl.end_date_active, cp_effective_date);
1670
1671 lv_hourly_salaried_desc VARCHAR2(240);
1672 lv_procedure_name VARCHAR2(100);
1673
1674 BEGIN
1675 lv_procedure_name := 'get_hourly_salaried_code';
1676 hr_utility.trace('Entered get_hourly_salaried_code');
1677 open c_get_hourly_salaried_code(p_hourly_salaried_code
1678 ,p_effective_date);
1679 fetch c_get_hourly_salaried_code into lv_hourly_salaried_desc;
1680 close c_get_hourly_salaried_code;
1681
1682 hr_utility.trace('Leaving get_hourly_salaried_code');
1683 return(lv_hourly_salaried_desc);
1684
1685 END get_hourly_salaried_code ;
1686
1687
1688 /******************************************************************
1689 Name : get_shift
1690 Purpose :
1691 Arguments :
1692 Notes :
1693 ******************************************************************/
1694 FUNCTION get_shift(p_soft_coding_keyflex_id in number
1695 ,p_effective_date in date
1696 )
1697 RETURN varchar2 IS
1698
1699 cursor c_get_shift(cp_soft_coding_keyflex_id in number
1700 ,cp_effective_date in date) is
1701 select segment5
1702 from hr_soft_coding_keyflex
1703 where soft_coding_keyflex_id = cp_soft_coding_keyflex_id;
1704
1705 lv_shift_desc VARCHAR2(240);
1706 lv_shift_code VARCHAR2(240);
1707 lv_procedure_name VARCHAR2(100);
1708
1709 BEGIN
1710 lv_procedure_name := 'get_shift';
1711 hr_utility.trace('Entered get_shift');
1712 open c_get_shift(p_soft_coding_keyflex_id, p_effective_date);
1713 fetch c_get_shift into lv_shift_code;
1714 close c_get_shift;
1715
1716 hr_utility.trace('shift_code = ' || lv_shift_code);
1717 /* check this one out as it is legislation specific
1718 select hl.meaning
1719 into lv_shift_desc
1720 from hr_lookups hl
1721 where hl.lookup_type='US_SHIFTS'
1722 and hl.lookup_code = lv_shift_code
1723 and hl.enabled_flag='Y'
1724 and hl.application_id = 800
1725 and p_curr_eff_date between
1726 nvl(hl.start_date_active,p_curr_eff_date)
1727 and nvl(hl.end_date_active,p_curr_eff_date)
1728 order by meaning;
1729 */
1730 hr_utility.trace('Leaving get_shift');
1731 return(lv_shift_desc);
1732
1733 END get_shift ;
1734
1735
1736 /******************************************************************
1737 Name : get_employee_addr
1738 Purpose :
1739 Arguments :
1740 Notes :
1741 ******************************************************************/
1742 PROCEDURE get_employee_addr (p_person_id in number
1743 ,p_effective_date in date
1744 )
1745 IS
1746 cursor c_addr_line(cp_person_id in number
1747 ,cp_effective_date in date) is
1748 select address_line1,
1749 address_line2,
1750 address_line3,
1751 town_or_city,
1752 region_1,
1753 region_2,
1754 region_3,
1755 postal_code,
1756 country
1757 from per_addresses pa
1758 where pa.person_id = cp_person_id
1759 and pa.primary_flag = 'Y' --is address primary ?
1760 and cp_effective_date between pa.date_from
1761 and nvl(pa.date_to, cp_effective_date);
1762
1763 lv_ee_or_er VARCHAR2(150);
1764 lv_ee_address_line_1 VARCHAR2(240);
1765 lv_ee_address_line_2 VARCHAR2(240);
1766 lv_ee_address_line_3 VARCHAR2(240);
1767 lv_ee_town_or_city VARCHAR2(150);
1768 lv_ee_region_1 VARCHAR2(240);
1769 lv_ee_region_2 VARCHAR2(240);
1770 lv_ee_region_3 VARCHAR2(240);
1771 lv_ee_postal_code VARCHAR2(150);
1772 lv_ee_country VARCHAR2(150);
1773 ln_index NUMBER;
1774 lv_procedure_name VARCHAR2(100);
1775
1776 BEGIN
1777 lv_ee_or_er := 'Employee Address';
1778 lv_procedure_name := 'get_employee_addr';
1779 open c_addr_line(p_person_id, p_effective_date);
1780 fetch c_addr_line into lv_ee_address_line_1
1781 ,lv_ee_address_line_2
1782 ,lv_ee_address_line_3
1783 ,lv_ee_town_or_city
1784 ,lv_ee_region_1
1785 ,lv_ee_region_2
1786 ,lv_ee_region_3
1787 ,lv_ee_postal_code
1788 ,lv_ee_country;
1789 close c_addr_line;
1790
1791 ln_index := pay_emp_action_arch.lrr_act_tab.count;
1792
1793 hr_utility.trace('ln_index in get_employee_addr proc is '
1794 || pay_emp_action_arch.lrr_act_tab.count);
1795 hr_utility.trace('person_id is'||p_person_id);
1796
1797 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
1798 := 'ADDRESS DETAILS';
1799 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1800 := '00-000-0000';
1801 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
1802 := p_person_id;
1803 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
1804 := lv_ee_address_line_1 ;
1805 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
1806 := lv_ee_address_line_2;
1807 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
1808 := lv_ee_address_line_3;
1809 pay_emp_action_arch.lrr_act_tab(ln_index).act_info8
1810 := lv_ee_town_or_city;
1811 pay_emp_action_arch.lrr_act_tab(ln_index).act_info9
1812 := lv_ee_region_1;
1813 pay_emp_action_arch.lrr_act_tab(ln_index).act_info10
1814 := lv_ee_region_2;
1815 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
1816 := lv_ee_region_3 ;
1817 pay_emp_action_arch.lrr_act_tab(ln_index).act_info12
1818 := lv_ee_postal_code;
1819 pay_emp_action_arch.lrr_act_tab(ln_index).act_info13
1820 := lv_ee_country;
1821 pay_emp_action_arch.lrr_act_tab(ln_index).act_info14
1822 := lv_ee_or_er;
1823 hr_utility.trace('Leaving get_employee_addr');
1824 END get_employee_addr;
1825
1826
1827 /******************************************************************
1828 Name : get_net_pay_distribution
1829 Purpose :
1830 Arguments :
1831 Notes :
1832 ******************************************************************/
1833 PROCEDURE get_net_pay_distribution(
1834 p_pre_pay_action_id in number
1835 ,p_assignment_id in number
1836 ,p_curr_pymt_eff_date in date
1837 ,p_ppp_source_action_id in number
1838 )
1839 IS
1840
1841
1842 cursor c_net_pay(cp_pre_pay_action_id in number
1843 ,cp_assignment_id in number
1844 ,cp_curr_pymt_eff_date in date
1845 ,cp_ppp_source_action_id in number
1846 ) is
1847 select pea.segment1 seg1,
1848 pea.segment2 seg2,
1849 pea.segment3 seg3,
1850 pea.segment4 seg4,
1851 pea.segment5 seg5,
1852 pea.segment6 seg6,
1853 pea.segment7 seg7,
1854 pea.segment8 seg8,
1855 pea.segment9 seg9,
1856 pea.segment10 seg10,
1857 ppp.value amount,
1858 ppp.pre_payment_id,
1859 popm.org_payment_method_id,
1860 popm.org_payment_method_name,
1861 pppm.personal_payment_method_id
1862 from pay_assignment_actions paa,
1863 pay_pre_payments ppp,
1864 pay_org_payment_methods_f popm ,
1865 pay_personal_payment_methods_f pppm,
1866 pay_external_accounts pea
1867 where paa.assignment_action_id = cp_pre_pay_action_id
1868 and ppp.assignment_action_id = paa.assignment_action_id
1869 and paa.assignment_id = cp_assignment_id
1870 and ( ( ppp.source_action_id is null
1871 and cp_ppp_source_action_id is null)
1872 or
1873 -- is it a Normal or Process Separate specific
1874 -- Payments should be included in the Standard
1875 -- SOE. Only Separate Payments should be in
1876 -- a Separate SOE.
1877 (ppp.source_action_id is not null
1878 and cp_ppp_source_action_id is null
1879 and exists (
1880 select ''
1881 from pay_run_types_f prt,
1882 pay_assignment_actions paa_run,
1883 pay_payroll_actions ppa_run
1884 where paa_run.assignment_action_id
1885 = ppp.source_action_id
1886 and paa_run.payroll_action_id
1887 = ppa_run.payroll_action_id
1888 and paa_run.run_type_id = prt.run_type_id
1889 and prt.run_method in ('P', 'N')
1890 and ppa_run.effective_date
1891 between prt.effective_start_date
1892 and prt.effective_end_date
1893 )
1894 )
1895 or
1896 (cp_ppp_source_action_id is not null
1897 and ppp.source_action_id = cp_ppp_source_action_id)
1898 )
1899 and ppp.org_payment_method_id = popm.org_payment_method_id
1900 and popm.defined_balance_id is not null
1901 and pppm.personal_payment_method_id(+)
1902 = ppp.personal_payment_method_id
1903 and pea.external_account_id = nvl(pppm.external_account_id,popm.external_account_id)
1904 and cp_curr_pymt_eff_date between popm.effective_start_date
1905 and popm.effective_end_date
1906 and cp_curr_pymt_eff_date between nvl(pppm.effective_start_date,
1907 cp_curr_pymt_eff_date)
1908 and nvl(pppm.effective_end_date,
1909 cp_curr_pymt_eff_date);
1910
1911 ln_index NUMBER;
1912 lv_segment1 VARCHAR2(300);
1913 lv_segment2 VARCHAR2(300);
1914 lv_segment3 VARCHAR2(300);
1915 lv_segment4 VARCHAR2(300);
1916 lv_segment5 VARCHAR2(300);
1917 lv_segment6 VARCHAR2(300);
1918 lv_segment7 VARCHAR2(300);
1919 lv_segment8 VARCHAR2(300);
1920 lv_segment9 VARCHAR2(300);
1921 lv_segment10 VARCHAR2(300);
1922 ln_value NUMBER(15,2);
1923 ln_pre_payment_id NUMBER;
1924 ln_org_payment_method_id NUMBER;
1925 lv_org_payment_method_name VARCHAR2(300);
1926 ln_emp_payment_method_id NUMBER;
1927 lv_procedure_name VARCHAR2(100);
1928
1929 BEGIN
1930
1931
1932 open c_net_pay(p_pre_pay_action_id
1933 ,p_assignment_id
1934 ,p_curr_pymt_eff_date
1935 ,p_ppp_source_action_id);
1936 hr_utility.trace('Opened cursor get_net_pay_distribution ');
1937
1938 loop
1939 fetch c_net_pay into lv_segment1
1940 ,lv_segment2
1941 ,lv_segment3
1942 ,lv_segment4
1943 ,lv_segment5
1944 ,lv_segment6
1945 ,lv_segment7
1946 ,lv_segment8
1947 ,lv_segment9
1948 ,lv_segment10
1949 ,ln_value
1950 ,ln_pre_payment_id
1951 ,ln_org_payment_method_id
1952 ,lv_org_payment_method_name
1953 ,ln_emp_payment_method_id;
1954 hr_utility.trace('Fetched get_net_pay_distribution ');
1955 if c_net_pay%notfound then
1956 exit;
1957 end if;
1958
1959 ln_index := pay_emp_action_arch.lrr_act_tab.count;
1960
1961 hr_utility.trace('ln_index in get_net_pay_dist proc is '
1962 || pay_emp_action_arch.lrr_act_tab.count);
1963
1964 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
1965 := 'EMPLOYEE NET PAY DISTRIBUTION';
1966 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1967 := '00-000-0000';
1968 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
1969 := ln_org_payment_method_id;
1970 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
1971 := ln_emp_payment_method_id;
1972 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
1973 := null;
1974 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
1975 := lv_segment1;
1976 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
1977 := lv_segment2;
1978 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
1979 := lv_segment3;
1980 pay_emp_action_arch.lrr_act_tab(ln_index).act_info8
1981 := lv_segment4;
1982 pay_emp_action_arch.lrr_act_tab(ln_index).act_info9
1983 := lv_segment5;
1984 pay_emp_action_arch.lrr_act_tab(ln_index).act_info10
1985 := lv_segment6;
1986 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
1987 := lv_segment7 ;
1988 pay_emp_action_arch.lrr_act_tab(ln_index).act_info12
1989 := lv_segment8;
1990 pay_emp_action_arch.lrr_act_tab(ln_index).act_info13
1991 := lv_segment9;
1992 pay_emp_action_arch.lrr_act_tab(ln_index).act_info14
1993 := lv_segment10;
1994 pay_emp_action_arch.lrr_act_tab(ln_index).act_info15
1995 := ln_pre_payment_id;
1996 pay_emp_action_arch.lrr_act_tab(ln_index).act_info16
1997 := fnd_number.number_to_canonical(ln_value); /* Bug 3311866*/
1998 pay_emp_action_arch.lrr_act_tab(ln_index).act_info17
1999 := p_pre_pay_action_id;
2000 pay_emp_action_arch.lrr_act_tab(ln_index).act_info18
2001 := lv_org_payment_method_name;
2002 end loop;
2003 close c_net_pay;
2004 hr_utility.set_location(gv_package || lv_procedure_name,100);
2005 END get_net_pay_distribution;
2006
2007 /******************************************************************
2008 Name : get_tp_pay_distribution
2009 Purpose : Get the Third Party Pay Distribution
2010 Arguments :
2011 Notes :
2012 ******************************************************************/
2013 PROCEDURE get_3rdparty_pay_distribution(
2014 p_pre_pay_action_id in number
2015 ,p_assignment_id in number
2016 ,p_curr_pymt_eff_date in date
2017 ,p_ppp_source_action_id in number
2018 ,p_payroll_id in number
2019 )
2020 IS
2021
2022 cursor c_child_action (cp_pre_pay_action_id in number
2023 ,cp_assignment_id in number) is
2024 select paa.assignment_action_id
2025 from pay_assignment_actions paa
2026 where paa.source_action_id = cp_pre_pay_action_id
2027 and paa.assignment_id = cp_assignment_id
2028 and paa.action_status = 'C';
2029
2030 cursor c_third_party_pay(cp_pre_pay_action_id in number
2031 ,cp_assignment_id in number
2032 ,cp_curr_pymt_eff_date in date
2033 ,cp_ppp_source_action_id in number
2034 ) is
2035 select pea.segment1 seg1,
2036 pea.segment2 seg2,
2037 pea.segment3 seg3,
2038 pea.segment4 seg4,
2039 pea.segment5 seg5,
2040 pea.segment6 seg6,
2041 pea.segment7 seg7,
2042 pea.segment8 seg8,
2043 pea.segment9 seg9,
2044 pea.segment10 seg10,
2045 ppp.value amount,
2046 ppp.pre_payment_id,
2047 popm.org_payment_method_id,
2048 popm.org_payment_method_name,
2049 pppm.personal_payment_method_id
2050 from pay_assignment_actions paa,
2051 pay_pre_payments ppp,
2052 pay_org_payment_methods_f popm ,
2053 pay_personal_payment_methods_f pppm,
2054 pay_external_accounts pea
2055 where paa.assignment_action_id = cp_pre_pay_action_id
2056 and ppp.assignment_action_id = paa.assignment_action_id
2057 and paa.assignment_id = cp_assignment_id
2058 and ( ( ppp.source_action_id is null
2059 and cp_ppp_source_action_id is null)
2060 or
2061 -- is it a Normal or Process Separate specific
2062 -- Payments should be included in the Standard
2063 -- SOE. Only Separate Payments should be in
2064 -- a Separate SOE.
2065 (ppp.source_action_id is not null
2066 and cp_ppp_source_action_id is null
2067 and exists (
2068 select ''
2069 from pay_run_types_f prt,
2070 pay_assignment_actions paa_run,
2071 pay_payroll_actions ppa_run
2072 where paa_run.assignment_action_id
2073 = ppp.source_action_id
2074 and paa_run.payroll_action_id
2075 = ppa_run.payroll_action_id
2076 and paa_run.run_type_id = prt.run_type_id
2077 and prt.run_method in ('P', 'N')
2078 and ppa_run.effective_date
2079 between prt.effective_start_date
2080 and prt.effective_end_date
2081 )
2082 )
2083 or
2084 (cp_ppp_source_action_id is not null
2085 and ppp.source_action_id = cp_ppp_source_action_id)
2086 )
2087 and ppp.org_payment_method_id = popm.org_payment_method_id
2088 and popm.defined_balance_id is null
2089 and pppm.personal_payment_method_id(+)
2090 = ppp.personal_payment_method_id
2091 and pea.external_account_id(+) = pppm.external_account_id
2092 and cp_curr_pymt_eff_date between popm.effective_start_date
2093 and popm.effective_end_date
2094 and cp_curr_pymt_eff_date between nvl(pppm.effective_start_date,
2095 cp_curr_pymt_eff_date)
2096 and nvl(pppm.effective_end_date,
2097 cp_curr_pymt_eff_date);
2098
2099 ln_index NUMBER;
2100 lv_segment1 VARCHAR2(300);
2101 lv_segment2 VARCHAR2(300);
2102 lv_segment3 VARCHAR2(300);
2103 lv_segment4 VARCHAR2(300);
2104 lv_segment5 VARCHAR2(300);
2105 lv_segment6 VARCHAR2(300);
2106 lv_segment7 VARCHAR2(300);
2107 lv_segment8 VARCHAR2(300);
2108 lv_segment9 VARCHAR2(300);
2109 lv_segment10 VARCHAR2(300);
2110 ln_value NUMBER(15,2);
2111 ln_pre_payment_id NUMBER;
2112 ln_org_payment_method_id NUMBER;
2113 lv_org_payment_method_name VARCHAR2(300);
2114 ln_emp_payment_method_id NUMBER;
2115 k NUMBER;
2116
2117 TYPE actions_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2118 ltt_actions actions_tab;
2119
2120 lv_procedure_name VARCHAR2(100);
2121
2122 BEGIN
2123 lv_procedure_name := 'get_3rdparty_pay_distribution';
2124 hr_utility.set_location(gv_package || lv_procedure_name,10);
2125 hr_utility.trace('p_pre_pay_action_id = ' || p_pre_pay_action_id);
2126 hr_utility.trace('p_curr_pymt_eff_date = ' || p_curr_pymt_eff_date);
2127 hr_utility.trace('p_ppp_source_action_id = '|| p_ppp_source_action_id);
2128 k := 0;
2129
2130 -- Check if Multi assignment payment is enabled
2131 if pay_emp_action_arch.gv_multi_payroll_pymt is null then
2132 pay_emp_action_arch.gv_multi_payroll_pymt
2133 := pay_emp_action_arch.get_multi_assignment_flag(
2134 p_payroll_id => p_payroll_id
2135 ,p_effective_date => p_curr_pymt_eff_date);
2136 end if;
2137 hr_utility.set_location(gv_package || lv_procedure_name,20);
2138
2139 if nvl(pay_emp_action_arch.gv_multi_payroll_pymt, 'N') = 'Y' then
2140 -- If Multi Assignment Payment is enabled, get the child prepayment
2141 -- actions as payment information is stored against child.
2142 -- Insert this data in pl/sql table.
2143 for cval in c_child_action(p_pre_pay_action_id, p_assignment_id) loop
2144 ltt_actions(k) := cval.assignment_action_id;
2145 k := k + 1;
2146 end loop;
2147 hr_utility.set_location(gv_package || lv_procedure_name,30);
2148 else
2149 ltt_actions(k) := p_pre_pay_action_id;
2150 k := k + 1;
2151 hr_utility.set_location(gv_package || lv_procedure_name,40);
2152 end if;
2153
2154 -- Value of k will be zero only if the payroll is enabled for multi
2155 -- assignment payments and we are processing seperate check action.
2156 -- In this case, passed assignment action is added to pl/sql table.
2157 if k = 0 then
2158 ltt_actions(k) := p_pre_pay_action_id;
2159 end if;
2160
2161 for j in ltt_actions.first .. ltt_actions.last loop
2162 hr_utility.trace('assignment action = ' || ltt_actions(j));
2163 end loop;
2164
2165 for j in ltt_actions.first .. ltt_actions.last loop
2166 open c_third_party_pay(ltt_actions(j)
2167 ,p_assignment_id
2168 ,p_curr_pymt_eff_date
2169 ,p_ppp_source_action_id);
2170
2171 loop
2172 fetch c_third_party_pay into lv_segment1
2173 ,lv_segment2
2174 ,lv_segment3
2175 ,lv_segment4
2176 ,lv_segment5
2177 ,lv_segment6
2178 ,lv_segment7
2179 ,lv_segment8
2180 ,lv_segment9
2181 ,lv_segment10
2182 ,ln_value
2183 ,ln_pre_payment_id
2184 ,ln_org_payment_method_id
2185 ,lv_org_payment_method_name
2186 ,ln_emp_payment_method_id;
2187 hr_utility.trace('Fetched get_3rdparty_pay_distribution ');
2188 if c_third_party_pay%notfound then
2189 exit;
2190 end if;
2191
2192 ln_index := pay_emp_action_arch.lrr_act_tab.count;
2193
2194 hr_utility.trace('ln_index in get_3rdparty_pay_distribution proc is '
2195 || pay_emp_action_arch.lrr_act_tab.count);
2196
2197 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
2198 := 'EMPLOYEE THIRD PARTY PAYMENTS';
2199 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
2200 := '00-000-0000';
2201 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
2202 := ln_org_payment_method_id;
2203 hr_utility.trace('ln_org_payment_method_id'||ln_org_payment_method_id);
2204 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
2205 := ln_emp_payment_method_id;
2206 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
2207 := null;
2208 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5
2209 := lv_segment1;
2210 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
2211 := lv_segment2;
2212 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
2213 := lv_segment3;
2214 pay_emp_action_arch.lrr_act_tab(ln_index).act_info8
2215 := lv_segment4;
2216 pay_emp_action_arch.lrr_act_tab(ln_index).act_info9
2217 := lv_segment5;
2218 pay_emp_action_arch.lrr_act_tab(ln_index).act_info10
2219 := lv_segment6;
2220 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
2221 := lv_segment7 ;
2222 pay_emp_action_arch.lrr_act_tab(ln_index).act_info12
2223 := lv_segment8;
2224 pay_emp_action_arch.lrr_act_tab(ln_index).act_info13
2225 := lv_segment9;
2226 pay_emp_action_arch.lrr_act_tab(ln_index).act_info14
2227 := lv_segment10;
2228 pay_emp_action_arch.lrr_act_tab(ln_index).act_info15
2229 := ln_pre_payment_id;
2230 pay_emp_action_arch.lrr_act_tab(ln_index).act_info16
2231 := fnd_number.number_to_canonical(ln_value); /* Bug 3311866*/
2232 pay_emp_action_arch.lrr_act_tab(ln_index).act_info17
2233 := ltt_actions(j);
2234 pay_emp_action_arch.lrr_act_tab(ln_index).act_info18
2235 := lv_org_payment_method_name;
2236 end loop;
2237 close c_third_party_pay;
2238 end loop;
2239 hr_utility.set_location(gv_package || lv_procedure_name,100);
2240
2241 END get_3rdparty_pay_distribution;
2242
2243
2244
2245 PROCEDURE get_org_other_info(p_organization_id in number
2246 ,p_business_group_id in number)
2247 IS
2248 cursor c_get_other_info(cp_organization_id in number
2249 ,cp_org_information_context in varchar2) is
2250 select hri.org_information1,
2251 hri.org_information2, hri.org_information3,
2252 hri.org_information4, hri.org_information5,
2253 hri.org_information6, hri.org_information7,
2254 org_information_context ----Bug 7115367
2255 from hr_organization_information hri
2256 where hri.organization_id = cp_organization_id
2257 and hri.org_information_context = cp_org_information_context
2258 and hri.org_information1 = 'MESG';
2259
2260 lv_org_information1 hr_organization_information.org_information1%type;
2261 lv_org_information2 hr_organization_information.org_information2%type;
2262 lv_org_information3 hr_organization_information.org_information3%type;
2263 lv_org_information4 hr_organization_information.org_information4%type;
2264 lv_org_information5 hr_organization_information.org_information5%type;
2265 lv_org_information6 hr_organization_information.org_information6%type;
2266 lv_org_information7 hr_organization_information.org_information7%type;
2267 lv_org_information_cntxt hr_organization_information.org_information_context%type; ----Bug 7115367
2268
2269 ln_index NUMBER;
2270 lv_exists VARCHAR2(1);
2271 lv_procedure_name VARCHAR2(100);
2272
2273 BEGIN
2274 lv_procedure_name := '.get_org_other_info';
2275 lv_exists := 'N';
2276
2277 if p_organization_id is not null then
2278 open c_get_other_info(p_organization_id
2279 ,'Organization:Payslip Info') ;
2280 loop
2281 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2282 fetch c_get_other_info into lv_org_information1
2283 ,lv_org_information2
2284 ,lv_org_information3
2285 ,lv_org_information4
2286 ,lv_org_information5
2287 ,lv_org_information6
2288 ,lv_org_information7
2289 ,lv_org_information_cntxt;
2290 if c_get_other_info%notfound then
2291 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2292 exit;
2293 end if;
2294
2295
2296 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2297 if pay_emp_action_arch.ltr_ppa_arch.count > 0 then
2298 for i in pay_emp_action_arch.ltr_ppa_arch.first ..
2299 pay_emp_action_arch.ltr_ppa_arch.last loop
2300 if pay_emp_action_arch.ltr_ppa_arch(i).act_info1
2301 = p_organization_id and
2302 pay_emp_action_arch.ltr_ppa_arch(i).act_info2
2303 = 'MESG' and
2304 pay_emp_action_arch.ltr_ppa_arch(i).act_info6
2305 = lv_org_information6 then
2306 lv_exists := 'Y';
2307 exit;
2308 end if;
2309 end loop;
2310 end if;
2311
2312 if lv_exists = 'N' then
2313 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
2314 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
2315 := 'EMPLOYEE OTHER INFORMATION';
2316 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
2317 := '00-000-0000';
2318 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
2319 := p_organization_id;
2320 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info2
2321 := 'MESG';
2322 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info4
2323 := nvl(lv_org_information7,lv_org_information4) ;
2324 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info5
2325 := lv_org_information5;
2326 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info6
2327 := lv_org_information6;
2328 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info13
2329 := lv_org_information_cntxt; ----Bug 7115367
2330 end if;
2331 end loop ;
2332 close c_get_other_info;
2333 end if;
2334
2335 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2336 if p_business_group_id is not null then
2337 open c_get_other_info(p_business_group_id
2338 ,'Business Group:Payslip Info') ;
2339 loop
2340 hr_utility.set_location(gv_package || lv_procedure_name, 110);
2341 fetch c_get_other_info into lv_org_information1
2342 ,lv_org_information2
2343 ,lv_org_information3
2344 ,lv_org_information4
2345 ,lv_org_information5
2346 ,lv_org_information6
2347 ,lv_org_information7
2348 ,lv_org_information_cntxt; ----Bug 7115367
2349 if c_get_other_info%notfound then
2350 hr_utility.set_location(gv_package || lv_procedure_name, 120);
2351 exit;
2352 end if;
2353
2354 hr_utility.set_location(gv_package || lv_procedure_name, 130);
2355 if pay_emp_action_arch.ltr_ppa_arch.count > 0 then
2356 for i in pay_emp_action_arch.ltr_ppa_arch.first ..
2357 pay_emp_action_arch.ltr_ppa_arch.last loop
2358 if pay_emp_action_arch.ltr_ppa_arch(i).act_info1
2359 = p_business_group_id and
2360 pay_emp_action_arch.ltr_ppa_arch(i).act_info2
2361 = 'MESG' and
2362 pay_emp_action_arch.ltr_ppa_arch(i).act_info6
2363 = lv_org_information6 then
2364 lv_exists := 'Y';
2365 exit;
2366 end if;
2367 end loop;
2368 end if;
2369
2370 if lv_exists = 'N' then
2371 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
2372 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
2373 := 'EMPLOYEE OTHER INFORMATION';
2374 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
2375 := '00-000-0000';
2376 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
2377 := p_business_group_id;
2378 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info2
2379 := 'MESG';
2380 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info4
2381 := nvl(lv_org_information7,lv_org_information4) ;
2382 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info5
2383 := lv_org_information5 ;
2384 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info6
2385 := lv_org_information6;
2386 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info13
2387 := lv_org_information_cntxt; --Bug 7115367
2388 end if;
2389 end loop ;
2390 close c_get_other_info;
2391 end if;
2392 hr_utility.set_location(gv_package || lv_procedure_name, 140);
2393
2394 END get_org_other_info;
2395
2396
2397 PROCEDURE get_org_address(p_organization_id in number)
2398 IS
2399 cursor c_addr_line(cp_organization_id in number) is
2400 select address_line_1, address_line_2,
2401 address_line_3, town_or_city,
2402 region_1, region_2,
2403 region_3, postal_code,
2404 country, telephone_number_1
2405 from hr_locations hl,
2406 hr_organization_units hou
2407 where hou.organization_id = cp_organization_id
2408 and hou.location_id = hl.location_id;
2409
2410 lv_ee_or_er VARCHAR2(150);
2411 lv_er_address_line_1 VARCHAR2(240);
2412 lv_er_address_line_2 VARCHAR2(240);
2413 lv_er_address_line_3 VARCHAR2(240);
2414 lv_er_town_or_city VARCHAR2(150);
2415 lv_er_region_1 VARCHAR2(240);
2416 lv_er_region_2 VARCHAR2(240);
2417 lv_er_region_3 VARCHAR2(240);
2418 lv_er_postal_code VARCHAR2(150);
2419 lv_er_country VARCHAR2(240);
2420 lv_er_telephone VARCHAR2(150);
2421
2422 lv_exists VARCHAR2(1);
2423 ln_index NUMBER;
2424 lv_procedure_name VARCHAR2(100);
2425
2426 BEGIN
2427 lv_procedure_name := '.get_org_address';
2428 lv_ee_or_er := 'Employer Address';
2429 lv_exists := 'N';
2430 -- Get Employer address
2431 hr_utility.set_location(gv_package || lv_procedure_name, 210);
2432 if p_organization_id is null then
2433 return;
2434 end if;
2435 open c_addr_line(p_organization_id);
2436 fetch c_addr_line into lv_er_address_line_1
2437 ,lv_er_address_line_2
2438 ,lv_er_address_line_3
2439 ,lv_er_town_or_city
2440 ,lv_er_region_1
2441 ,lv_er_region_2
2442 ,lv_er_region_3
2443 ,lv_er_postal_code
2444 ,lv_er_country
2445 ,lv_er_telephone;
2446 close c_addr_line;
2447 hr_utility.set_location(gv_package || lv_procedure_name, 250);
2448
2449 if pay_emp_action_arch.ltr_ppa_arch.count > 0 then
2450 for i in pay_emp_action_arch.ltr_ppa_arch.first ..
2451 pay_emp_action_arch.ltr_ppa_arch.last loop
2452 if pay_emp_action_arch.ltr_ppa_arch(i).act_info1
2453 = p_organization_id and
2454 pay_emp_action_arch.ltr_ppa_arch(i).act_info14
2455 = 'Employer Address' then
2456 lv_exists := 'Y';
2457 exit;
2458 end if;
2459 end loop;
2460 end if;
2461
2462 if lv_exists = 'N' then
2463 hr_utility.set_location(gv_package || lv_procedure_name, 260);
2464 ln_index := pay_emp_action_arch.ltr_ppa_arch.count;
2465
2466 pay_emp_action_arch.ltr_ppa_arch(ln_index).action_info_category
2467 := 'ADDRESS DETAILS';
2468 pay_emp_action_arch.ltr_ppa_arch(ln_index).jurisdiction_code
2469 := '00-000-0000';
2470 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info1
2471 := p_organization_id;
2472 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info5
2473 := lv_er_address_line_1 ;
2474 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info6
2475 := lv_er_address_line_2;
2476 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info7
2477 := lv_er_address_line_3;
2478 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info8
2479 := lv_er_town_or_city;
2480 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info9
2481 := lv_er_region_1;
2482 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info10
2483 := lv_er_region_2;
2484 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info11
2485 := lv_er_region_3 ;
2486 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info12
2487 := lv_er_postal_code;
2488 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info13
2489 := lv_er_country;
2490 pay_emp_action_arch.ltr_ppa_arch(ln_index).act_info14
2491 := lv_ee_or_er;
2492 end if;
2493
2494 END get_org_address;
2495
2496 /******************************************************************
2497 Name : This procedure archives data at payroll action level.
2498 This would be called from the archive_data procedure
2499 (for the first chunk only). The
2500 action_infomration_categories archived by this are
2501 EMPLOYEE OTHER INFORMATION for MESG and
2502 ADDRESS DETAILS for Employer Address
2503 Arguments : p_payroll_action_id Archiver Payroll Action ID
2504 p_payroll_id Payroll ID
2505 p_effective_date End Date of Archiver
2506 Notes :
2507 ******************************************************************/
2508 PROCEDURE arch_pay_action_level_data(p_payroll_action_id in number
2509 ,p_payroll_id in number
2510 ,p_effective_date in date
2511 )
2512 IS
2513
2514 ln_organization_id NUMBER(15);
2515 ln_business_group_id NUMBER(15);
2516 lv_procedure_name VARCHAR2(100);
2517
2518 cursor c_get_organization(cp_payroll_id in number
2519 ,cp_effective_date in date
2520 ) is
2521 select /*+ index(paf PER_ASSIGNMENTS_F_N7)*/
2522 distinct paf.organization_id, -- Bug 3354127
2523 paf.business_group_id
2524 from per_all_assignments_f paf
2525 where paf.payroll_id = cp_payroll_id
2526 and cp_effective_date between paf.effective_start_date
2527 and paf.effective_end_date;
2528
2529 BEGIN
2530 lv_procedure_name := '.arch_pay_action_level_data';
2531 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2532 open c_get_organization(p_payroll_id, p_effective_date);
2533 loop
2534 fetch c_get_organization into ln_organization_id,
2535 ln_business_group_id;
2536 if c_get_organization%notfound then
2537 exit;
2538 end if;
2539
2540 get_org_other_info(ln_organization_id, ln_business_group_id);
2541 get_org_address(ln_organization_id);
2542
2543 end loop;
2544 close c_get_organization;
2545
2546 hr_utility.set_location(gv_package || lv_procedure_name, 140);
2547
2548 -- insert rows in pay_action_information table
2549 if pay_emp_action_arch.ltr_ppa_arch.count > 0 then
2550 insert_rows_thro_api_process(
2551 p_action_context_id => p_payroll_action_id
2552 ,p_action_context_type => 'PA'
2553 ,p_assignment_id => null
2554 ,p_tax_unit_id => null
2555 ,p_curr_pymt_eff_date => p_effective_date
2556 ,p_tab_rec_data => pay_emp_action_arch.ltr_ppa_arch
2557 );
2558 end if;
2559 EXCEPTION
2560 when others then
2561 hr_utility.trace('Error in ' || gv_package || '.' || lv_procedure_name || '-'
2562 || to_char(sqlcode) || '-' || sqlerrm);
2563 hr_utility.set_location(gv_package || lv_procedure_name, 130);
2564 raise hr_utility.hr_error;
2565
2566 END arch_pay_action_level_data;
2567
2568
2569 /******************************************************************
2570 Name : This procedure archives data at payroll action level.
2571 This is a overloaded function. The function above is
2572 needs to be called once and it gets all the Orgs and
2573 BG for an assignment assigned to the payroll passed
2574 to the procedure.
2575
2576 The procedure below needs to called from the de-init
2577 code i.e. it is the last procedure called by the
2578 archive process. This procedure is dependent on the
2579 assignment level archive data and archives Employer
2580 Address for the HR Organization, Tax Unit or
2581 Business Group assignemnt to the assignments archived.
2582
2583 The procedure also archives messages defined for a BG
2584 or Organization.
2585
2586 action_information_categories archived by this are
2587 EMPLOYEE OTHER INFORMATION for MESG and
2588 ADDRESS DETAILS for Employer Address
2589 Arguments : p_payroll_action_id Archiver Payroll Action ID
2590 p_effective_date End Date of Archiver
2591 Notes :
2592 ******************************************************************/
2593 PROCEDURE arch_pay_action_level_data(p_payroll_action_id in number
2594 ,p_effective_date in date
2595 )
2596 IS
2597
2598 cursor c_employer_info (cp_payroll_action_id in number) is
2599 select distinct
2600 nvl(pai.tax_unit_id, -1)
2601 ,pai.action_information2 organization_id
2602 from pay_action_information pai
2603 ,pay_assignment_actions paa
2604 where pai.action_information_category = 'EMPLOYEE DETAILS'
2605 and pai.action_context_type = 'AAP'
2606 and pai.action_context_id = paa.assignment_action_id
2607 and paa.payroll_Action_id = cp_payroll_action_id
2608 and paa.action_status = 'C';
2609
2610 cursor c_bg (cp_payroll_action_id in number) is
2611 select ppa.business_group_id
2612 from pay_payroll_actions ppa
2613 where ppa.payroll_action_id = cp_payroll_action_id;
2614
2615 ln_business_group_id NUMBER;
2616 ln_organization_id NUMBER;
2617 ln_tax_unit_id NUMBER;
2618
2619 lv_procedure_name VARCHAR2(100);
2620
2621 BEGIN
2622 lv_procedure_name := '.arch_pay_action_level_data_deinit';
2623 hr_utility.set_location(gv_package || lv_procedure_name, 1);
2624
2625 delete from pay_action_information pai
2626 where pai.action_context_id = p_payroll_action_id
2627 and pai.action_context_type = 'PA'
2628 and pai.action_information_category in ('EMPLOYEE OTHER INFORMATION',
2629 'ADDRESS DETAILS')
2630 and (pai.action_information14 = 'Employer Address' or
2631 pai.action_information2 = 'MESG');
2632
2633 /* Get Business Group ID */
2634 open c_bg(p_payroll_action_id);
2635 fetch c_bg into ln_business_group_id;
2636 close c_bg;
2637
2638 /* Archive Business Group Address and Address */
2639 get_org_other_info(null, ln_business_group_id);
2640 get_org_address(ln_business_group_id);
2641
2642 hr_utility.set_location(gv_package || lv_procedure_name, 5);
2643 /* Get all the Organization ID and Tax Unit ID for assignment
2644 archived by the archiver. For the ORganizations get any
2645 message which needs to be archived and also archive the
2646 address information */
2647 open c_employer_info(p_payroll_action_id);
2648 loop
2649 fetch c_employer_info into ln_tax_unit_id, ln_organization_id;
2650 if c_employer_info%notfound then
2651 exit;
2652 end if;
2653
2654 hr_utility.trace('Organization ID = ' || ln_organization_id);
2655 hr_utility.trace('Tax Unit ID = ' || ln_tax_unit_id);
2656 /* Archive Organization Message */
2657 get_org_other_info(ln_organization_id, null);
2658 get_org_address(ln_organization_id);
2659 if ln_organization_id <> ln_tax_unit_id and ln_tax_unit_id <> -1 then
2660 get_org_address(ln_tax_unit_id);
2661 end if;
2662
2663 end loop;
2664 close c_employer_info;
2665
2666 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2667
2668 -- insert rows in pay_action_information table
2669 if pay_emp_action_arch.ltr_ppa_arch.count > 0 then
2670 insert_rows_thro_api_process(
2671 p_action_context_id => p_payroll_action_id
2672 ,p_action_context_type => 'PA'
2673 ,p_assignment_id => null
2674 ,p_tax_unit_id => null
2675 ,p_curr_pymt_eff_date => p_effective_date
2676 ,p_tab_rec_data => pay_emp_action_arch.ltr_ppa_arch
2677 );
2678 end if;
2679 hr_utility.set_location(gv_package || lv_procedure_name, 150);
2680
2681 EXCEPTION
2682 when others then
2683 hr_utility.trace('Error in ' || gv_package || '.' || lv_procedure_name || '-'
2684 || to_char(sqlcode) || '-' || sqlerrm);
2685 hr_utility.set_location(gv_package || lv_procedure_name, 130);
2686 raise hr_utility.hr_error;
2687
2688 END arch_pay_action_level_data;
2689
2690
2691 PROCEDURE get_personal_information(
2692 p_payroll_action_id in number
2693 ,p_assactid in number
2694 ,p_assignment_id in number
2695 ,p_curr_pymt_ass_act_id in number
2696 ,p_curr_eff_date in date
2697 ,p_date_earned in date
2698 ,p_curr_pymt_eff_date in date
2699 ,p_tax_unit_id in number
2700 ,p_time_period_id in number
2701 ,p_ppp_source_action_id in number
2702 ,p_run_action_id in number
2703 ,p_ytd_balcall_aaid in number default null
2704 )
2705 IS
2706 cursor c_employee_details(cp_assignment_id in number
2707 , cp_curr_eff_date in date
2708 ) is
2709 select ppf.full_name,
2710 ppf.national_identifier,
2711 ppf.person_id,
2712 pps.date_start,
2713 ppf.employee_number,
2714 ppf.original_date_of_hire,
2715 pps.adjusted_svc_date,
2716 paf.assignment_number,
2717 paf.location_id,
2718 paf.organization_id,
2719 paf.job_id,
2720 paf.position_id,
2721 paf.pay_basis_id,
2722 paf.frequency,
2723 paf.grade_id,
2724 paf.bargaining_unit_code,
2725 paf.collective_agreement_id,
2726 paf.contract_id,
2727 paf.special_ceiling_step_id,
2728 paf.people_group_id,
2729 paf.normal_hours,
2730 paf.time_normal_start,
2731 paf.time_normal_finish,
2732 paf.business_group_id,
2733 paf.soft_coding_keyflex_id,
2734 paf.hourly_salaried_code
2735 from per_assignments_f paf,
2736 per_all_people_f ppf,
2737 per_periods_of_service pps
2738 where paf.person_id = ppf.person_id
2739 and paf.assignment_id = cp_assignment_id
2740 and cp_curr_eff_date between paf.effective_start_date
2741 and paf.effective_end_date
2742 and cp_curr_eff_date between ppf.effective_start_date
2743 and ppf.effective_end_date
2744 and pps.person_id = ppf.person_id
2745 and pps.date_start = (select max(pps1.date_start)
2746 from per_periods_of_service pps1
2747 where pps1.person_id = paf.person_id
2748 and pps1.date_start <= cp_curr_eff_date);
2749
2750 cursor c_period_details (cp_time_period_id in number) is
2751 select payroll_id, period_type, start_date, cut_off_date
2752 from per_time_periods
2753 where time_period_id = cp_time_period_id;
2754
2755 cursor c_step (cp_sp_ceil_step_id in number,
2756 cp_effective_date in date) is
2757 select count(*)
2758 from per_spinal_points psp,
2759 per_spinal_points psp2,
2760 per_spinal_point_steps_f psps,
2761 per_spinal_point_steps_f psps2
2762 where psps.step_id = cp_sp_ceil_step_id
2763 and psp.spinal_point_id = psps.spinal_point_id
2764 and psps.grade_spine_id = psps2.grade_spine_id
2765 and psp2.spinal_point_id = psps2.spinal_point_id
2766 and psp.sequence >= psp2.sequence
2767 and cp_effective_date between psps.effective_start_date
2768 and psps.effective_end_date
2769 and cp_effective_date between psps2.effective_start_date
2770 and psps2.effective_end_date
2771 group by psp.spinal_point,
2772 psps.step_id,
2773 psps.sequence,
2774 psps.effective_start_date,
2775 psps.effective_end_date;
2776
2777 CURSOR er_phone_number(cp_organization_id in number) IS
2778 select telephone_number_1
2779 from hr_locations hl,
2780 hr_organization_units hou
2781 where hou.organization_id = cp_organization_id
2782 and hou.location_id = hl.location_id;
2783
2784
2785 lv_full_name VARCHAR2(300);
2786 lv_national_identifier VARCHAR2(100);
2787 ln_person_id NUMBER;
2788 ln_index NUMBER;
2789 ld_date_start DATE;
2790 lv_employee_number VARCHAR2(50);
2791 ld_original_date_of_hire DATE;
2792 ld_adjusted_svc_date DATE;
2793 lv_assignment_number VARCHAR2(50);
2794 ln_location_id NUMBER;
2795 lv_location_code VARCHAR2(240);
2796 ln_organization_id NUMBER;
2797 ln_job_id NUMBER;
2798 ln_pay_basis_id NUMBER;
2799 lv_frequency VARCHAR2(30);
2800 ln_grade_id NUMBER;
2801 lv_bargaining_unit_code VARCHAR2(80);
2802 ln_collective_agreement_id NUMBER(9);
2803 ln_contract_id NUMBER;
2804 ln_special_ceiling_step_id NUMBER;
2805 ln_people_group_id NUMBER;
2806 ln_normal_hours NUMBER(22,3);
2807 lv_time_normal_start VARCHAR2(5) :=null;
2808 lv_time_normal_finish VARCHAR2(5) :=null;
2809 ln_position_id NUMBER;
2810 lv_position_name VARCHAR2(240) :=null;
2811 ln_soft_coding_keyflex_id NUMBER;
2812 lv_gre_name VARCHAR2(240) :=null;
2813 lv_er_phone_number VARCHAR2(240) :=null;
2814 ln_business_group_id NUMBER;
2815 lv_organization_name VARCHAR2(240) :=null;
2816 lv_job_name VARCHAR2(240) :=null;
2817 lv_pay_basis VARCHAR2(240) :=null;
2818 lv_frequency_desc VARCHAR2(240) :=null;
2819 lv_grade VARCHAR2(240);
2820 lv_bargaining_unit VARCHAR2(240);
2821 lv_collective_agreement VARCHAR2(240);
2822 lv_contract VARCHAR2(240);
2823 lv_progression_point VARCHAR2(240);
2824 lv_step VARCHAR2(240);
2825 lv_pay_calc_method VARCHAR2(240);
2826 lv_shift_desc VARCHAR2(240);
2827 lv_hourly_salaried_code VARCHAR2(240);
2828 lv_hourly_salaried_desc VARCHAR2(240);
2829
2830 ln_payroll_id NUMBER;
2831 lv_period_type VARCHAR2(240);
2832 ld_period_start_date DATE;
2833 ld_period_end_date DATE;
2834
2835 ln_proposed_salary NUMBER(20,5);
2836 ln_pay_annualization_factor NUMBER(20,5);
2837
2838 lv_exists VARCHAR2(1);
2839 ln_index1 number;
2840 lv_procedure_name VARCHAR2(100);
2841
2842 BEGIN
2843 lv_procedure_name := 'get_personal_information';
2844 lv_exists := 'N';
2845 hr_utility.trace('Entered get_personal_information');
2846 initialization_process;
2847
2848 hr_utility.trace('p_assactid = ' || p_assactid);
2849 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
2850 hr_utility.trace('p_curr_pymt_ass_act_id = ' || p_curr_pymt_ass_act_id);
2851 hr_utility.trace('p_curr_eff_date = ' || p_curr_eff_date);
2852 hr_utility.trace('p_date_earned = ' || p_date_earned);
2853 hr_utility.trace('p_curr_pymt_eff_date = ' || p_curr_pymt_eff_date);
2854 hr_utility.trace('p_tax_unit_id = ' || p_tax_unit_id);
2855 hr_utility.trace('p_time_period_id = ' || p_time_period_id);
2856 hr_utility.trace('p_run_action_id = ' || p_run_action_id);
2857 --Bug 5585331 starts here
2858 -- open c_employee_details(p_assignment_id,p_curr_eff_date);
2859 open c_employee_details(p_assignment_id,p_date_earned);
2860 --Bug 5585331 ends here
2861 hr_utility.trace('Opened c_employee_details of get_personal_information ');
2862 fetch c_employee_details into lv_full_name,
2863 lv_national_identifier,
2864 ln_person_id,
2865 ld_date_start,
2866 lv_employee_number,
2867 ld_original_date_of_hire,
2868 ld_adjusted_svc_date,
2869 lv_assignment_number,
2870 ln_location_id,
2871 ln_organization_id,
2872 ln_job_id,
2873 ln_position_id,
2874 ln_pay_basis_id,
2875 lv_frequency,
2876 ln_grade_id,
2877 lv_bargaining_unit_code,
2878 ln_collective_agreement_id,
2879 ln_contract_id,
2880 ln_special_ceiling_step_id,
2881 ln_people_group_id,
2882 ln_normal_hours,
2883 lv_time_normal_start,
2884 lv_time_normal_finish,
2885 ln_business_group_id,
2886 ln_soft_coding_keyflex_id,
2887 lv_hourly_salaried_code;
2888 if c_employee_details%notfound then
2889 hr_utility.raise_error;
2890 end if;
2891
2892 hr_utility.trace('Opening c_period_details');
2893 open c_period_details(p_time_period_id);
2894 fetch c_period_details into ln_payroll_id,
2895 lv_period_type,
2896 ld_period_start_date,
2897 ld_period_end_date;
2898 if c_period_details%notfound then
2899 hr_utility.trace('Time Period details not found for time_period_id '
2900 ||to_char(p_time_period_id));
2901 --hr_utility.raise_error;
2902 end if;
2903 close c_period_details;
2904
2905
2906 lv_gre_name := get_organization_name(p_tax_unit_id);
2907 lv_organization_name := get_organization_name(ln_organization_id);
2908
2909 if ln_job_id is not null then
2910 lv_job_name := get_job_name(ln_job_id
2911 ,p_curr_eff_date);
2912 end if ;
2913
2914 if ln_position_id is not null then
2915 lv_position_name := get_position(ln_position_id
2916 ,p_curr_eff_date);
2917 end if;
2918
2919 if ln_pay_basis_id is not null then
2920 lv_pay_basis := get_pay_basis(ln_pay_basis_id
2921 ,p_curr_eff_date);
2922 end if;
2923
2924 if ln_location_id is not null then
2925 lv_location_code := get_location(ln_location_id);
2926 end if;
2927
2928 ln_proposed_salary := get_proposed_emp_salary(p_assignment_id
2929 ,ln_pay_basis_id
2930 ,lv_pay_basis
2931 ,p_date_earned);
2932
2933 ln_pay_annualization_factor := get_emp_annualization_factor(
2934 ln_pay_basis_id
2935 ,lv_period_type
2936 ,lv_pay_basis
2937 ,p_assignment_id
2938 ,p_date_earned);
2939
2940 if lv_frequency is not null then
2941 lv_frequency_desc := get_frequency(lv_frequency
2942 ,p_curr_eff_date);
2943 end if;
2944
2945 if ln_grade_id is not null then
2946 lv_grade := get_grade(ln_grade_id
2947 ,p_curr_eff_date);
2948 end if;
2949
2950 if lv_bargaining_unit_code is not null then
2951 lv_bargaining_unit := get_bargaining_unit(lv_bargaining_unit_code
2952 ,p_curr_eff_date);
2953 end if;
2954
2955 if ln_collective_agreement_id is not null then
2956 lv_collective_agreement := get_collective_agreement(
2957 ln_collective_agreement_id
2958 ,p_curr_eff_date
2959 );
2960 end if;
2961
2962 if ln_contract_id is not null then
2963 lv_contract := get_contract(ln_contract_id
2964 ,p_curr_eff_date) ;
2965 end if;
2966
2967 if lv_hourly_salaried_code is not null then
2968 lv_hourly_salaried_desc := get_hourly_salaried_code(
2969 lv_hourly_salaried_code
2970 ,p_curr_eff_date) ;
2971 end if;
2972
2973 if ln_soft_coding_keyflex_id is not null then
2974 lv_shift_desc := get_shift( ln_soft_coding_keyflex_id
2975 ,p_curr_eff_date) ;
2976 end if;
2977
2978 open er_phone_number(ln_organization_id);
2979 fetch er_phone_number into lv_er_phone_number;
2980 close er_phone_number;
2981
2982 if ln_special_ceiling_step_id is not null then
2983 open c_step(ln_special_ceiling_step_id, p_curr_eff_date);
2984 fetch c_step into lv_step;
2985 close c_step;
2986 end if;
2987
2988 ln_index := pay_emp_action_arch.lrr_act_tab.count;
2989
2990 hr_utility.trace('ln_index in get_personal_information proc is '
2991 || pay_emp_action_arch.lrr_act_tab.count);
2992
2993 pay_emp_action_arch.lrr_act_tab(ln_index).action_info_category
2994 := 'EMPLOYEE DETAILS';
2995 pay_emp_action_arch.lrr_act_tab(ln_index).jurisdiction_code
2996 := '00-000-0000';
2997 pay_emp_action_arch.lrr_act_tab(ln_index).act_info1
2998 := lv_full_name;
2999 hr_utility.trace('lv_full_name is'||lv_full_name);
3000 pay_emp_action_arch.lrr_act_tab(ln_index).act_info2
3001 := ln_organization_id;
3002
3003 pay_emp_action_arch.lrr_act_tab(ln_index).act_info4
3004 := lv_national_identifier ;
3005
3006 hr_utility.trace('lv_national_identifier is'||lv_national_identifier);
3007
3008 pay_emp_action_arch.lrr_act_tab(ln_index).act_info5 := lv_pay_basis;
3009
3010 hr_utility.trace('lv_pay_basis is'||lv_pay_basis);
3011
3012 pay_emp_action_arch.lrr_act_tab(ln_index).act_info6
3013 := lv_frequency_desc;
3014
3015 hr_utility.trace('lv_frequency_desc is'||lv_frequency_desc);
3016
3017 pay_emp_action_arch.lrr_act_tab(ln_index).act_info7
3018 := lv_grade;
3019
3020 hr_utility.trace('lv_grade is'||lv_grade);
3021
3022 pay_emp_action_arch.lrr_act_tab(ln_index).act_info8
3023 := lv_bargaining_unit;
3024
3025 hr_utility.trace('lv_bargaining_unit is'||lv_bargaining_unit);
3026 pay_emp_action_arch.lrr_act_tab(ln_index).act_info9
3027 := lv_collective_agreement;
3028
3029 hr_utility.trace('lv_collective_agreement is'||lv_collective_agreement);
3030 pay_emp_action_arch.lrr_act_tab(ln_index).act_info10
3031 := lv_employee_number ;
3032
3033 pay_emp_action_arch.lrr_act_tab(ln_index).act_info11
3034 := fnd_date.date_to_canonical(ld_date_start);
3035
3036 pay_emp_action_arch.lrr_act_tab(ln_index).act_info12
3037 := fnd_date.date_to_canonical(ld_original_date_of_hire);
3038
3039 pay_emp_action_arch.lrr_act_tab(ln_index).act_info13
3040 := fnd_date.date_to_canonical(ld_adjusted_svc_date);
3041
3042 pay_emp_action_arch.lrr_act_tab(ln_index).act_info14
3043 := lv_assignment_number;
3044
3045 pay_emp_action_arch.lrr_act_tab(ln_index).act_info15
3046 := lv_organization_name;
3047 pay_emp_action_arch.lrr_act_tab(ln_index).act_info16
3048 := p_time_period_id;
3049 pay_emp_action_arch.lrr_act_tab(ln_index).act_info17
3050 := lv_job_name ;
3051 pay_emp_action_arch.lrr_act_tab(ln_index).act_info18
3052 := lv_gre_name;
3053 pay_emp_action_arch.lrr_act_tab(ln_index).act_info19
3054 := lv_position_name;
3055 pay_emp_action_arch.lrr_act_tab(ln_index).act_info20
3056 := lv_contract;
3057 pay_emp_action_arch.lrr_act_tab(ln_index).act_info21
3058 := lv_time_normal_start ;
3059 pay_emp_action_arch.lrr_act_tab(ln_index).act_info22
3060 := lv_time_normal_finish;
3061 pay_emp_action_arch.lrr_act_tab(ln_index).act_info23
3062 := lv_pay_calc_method;
3063 pay_emp_action_arch.lrr_act_tab(ln_index).act_info24
3064 := lv_shift_desc;
3065 pay_emp_action_arch.lrr_act_tab(ln_index).act_info25
3066 := lv_er_phone_number;
3067 pay_emp_action_arch.lrr_act_tab(ln_index).act_info26
3068 := lv_hourly_salaried_desc;
3069 pay_emp_action_arch.lrr_act_tab(ln_index).act_info27
3070 := lv_step ;
3071 pay_emp_action_arch.lrr_act_tab(ln_index).act_info28
3072 := fnd_number.number_to_canonical(ln_proposed_salary) ; /* Bug 3311866*/
3073 pay_emp_action_arch.lrr_act_tab(ln_index).act_info29
3074 := fnd_number.number_to_canonical(ln_pay_annualization_factor) ;
3075 pay_emp_action_arch.lrr_act_tab(ln_index).act_info30
3076 := lv_location_code ;
3077
3078 close c_employee_details;
3079
3080 get_employee_accruals(p_assactid => p_assactid
3081 ,p_run_action_id => p_run_action_id
3082 ,p_assignment_id => p_assignment_id
3083 ,p_effective_date => p_curr_pymt_eff_date
3084 ,p_date_earned => p_date_earned);
3085
3086 get_net_pay_distribution(p_pre_pay_action_id => p_curr_pymt_ass_act_id
3087 ,p_assignment_id => p_assignment_id
3088 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3089 ,p_ppp_source_action_id => p_ppp_source_action_id
3090 );
3091
3092 get_3rdparty_pay_distribution(p_pre_pay_action_id => p_curr_pymt_ass_act_id
3093 ,p_assignment_id => p_assignment_id
3094 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3095 ,p_ppp_source_action_id => p_ppp_source_action_id
3096 ,p_payroll_id => ln_payroll_id
3097 );
3098
3099 get_employee_other_info(p_run_action_id => p_curr_pymt_ass_act_id
3100 ,p_assignment_id => p_assignment_id
3101 ,p_organization_id => ln_organization_id
3102 ,p_business_group_id => ln_business_group_id
3103 ,p_curr_pymt_eff_date => p_date_earned
3104 ,p_tax_unit_id => p_tax_unit_id
3105 ,p_ppp_source_action_id => p_ppp_source_action_id
3106 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
3107 ) ;
3108
3109 get_employee_addr (ln_person_id
3110 ,p_curr_eff_date);
3111
3112
3113 if pay_emp_action_arch.lrr_act_tab.count > 0 then
3114 insert_rows_thro_api_process(
3115 p_action_context_id => p_assactid
3116 ,p_action_context_type=> 'AAP'
3117 ,p_assignment_id => p_assignment_id
3118 ,p_tax_unit_id => p_tax_unit_id
3119 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3120 ,p_tab_rec_data => pay_emp_action_arch.lrr_act_tab
3121 );
3122 end if;
3123
3124 END get_personal_information;
3125
3126 BEGIN
3127 gv_package := 'pay_emp_action_arch';
3128
3129 EXCEPTION
3130 when others then
3131 hr_utility.trace('Error in ' || gv_package ||
3132 to_char(sqlcode) || '-' || sqlerrm);
3133 raise hr_utility.hr_error;
3134
3135 END pay_emp_action_arch;