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