[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_PAYREG_PKG
Source
1 PACKAGE BODY pay_mx_payreg_pkg AS
2 /* $Header: paymxreg.pkb 120.0.12020000.7 2013/03/05 08:06:50 jeisaac noship $ */
3
4 /*************************************************************************
5 +======================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +======================================================================+
10 Package Body Name : per_us_ipeds_pkg
11 Package File Name : peusiped.pkb
12 Description : This package contains functions which are used in Mexico
13 Payroll Register Report
14
15 Change List:
16 ------------
17
18 Name Date Version Bug Text
19 ----------- ------------ ------ --------- ------------------------------
20 nkjaladi 06-Feb-2013 120.0 14830976 Created
21 nkjaladi 07-Feb-2013 120.1 14830976 Enabled Dual Maintainance
22 nkjaladi 07-Feb-2013 120.2 14830976 In procedure Generate_XML
23 Changed the balance name from
24 'ISR Tax To Charge' to
25 'ISR Tax to Charge'
26 nkjaladi 20-Feb-2013 120.3 16364431 Modified procedure action_creation
27 16363434 sort_option, sort_action and
28 16364978 generate_xml_header
29 nkjaladi 22-Feb-2013 120.4 16364431 Modified procedure action_creation
30 16363434 to update serial number
31 16364978 accordingly and also xml_header
32 to display the sort option
33 meaning.
34 nkjaladi 26-Feb-2013 120.5 16364431 Modified procedure
35 generate_xml_header
36 jeisaac 05-Mar-2013 120.6 16405183 Modified procedure generate_xml.
37 Changed balance 'ISR Tax to Charge' to 'ISR Withheld'
38 *************************************************************************/
39
40 TYPE rec_entity_details IS RECORD
41 ( user_entity_name ff_user_entities.user_entity_name%TYPE
42 ,def_bal_id ff_user_entities.creator_id%TYPE
43 ,bal_value NUMBER);
44
45 TYPE entity_details_tab IS TABLE OF rec_entity_details INDEX BY BINARY_INTEGER;
46
47 g_pay_reg_defbal_details entity_details_tab;
48 g_pr_balance_value_tab pay_balance_pkg.t_balance_value_tab;
49 payroll_reg_xml_tbl xml_tbl;
50 EOL VARCHAR2(5);
51 g_package_name VARCHAR2(100) := 'PAY_MX_PAYREG_PKG';
52
53 --------------------------- range_cursor ---------------------------------
54 PROCEDURE range_cursor (pactid IN NUMBER
55 ,sqlstr OUT NOCOPY VARCHAR2) IS
56
57 leg_param pay_payroll_actions.legislative_parameters%TYPE;
58 l_consolidation_set_id NUMBER;
59 l_payroll_id NUMBER;
60 l_organization_id NUMBER;
61 l_location_id NUMBER;
62 l_person_id NUMBER;
63 l_leg_start_date DATE;
64 l_leg_end_date DATE;
65 l_business_group_id NUMBER;
66 l_payroll_text VARCHAR2(70);
67 l_consolidation_set_text VARCHAR2(50);
68 l_proc_name VARCHAR2(100) := g_package_name||'.RANGE_CURSOR';
69 BEGIN
70 --hr_utility.trace_on (NULL, 'Vineet');
71 fnd_file.put_line(FND_FILE.LOG,'pay_mx_payreg_pkg.range_cursor ');
72 hr_utility.set_location(l_proc_name,5);
73
74 SELECT legislative_parameters
75 INTO leg_param
76 FROM pay_payroll_actions ppa
77 WHERE ppa.payroll_action_id = pactid;
78
79 hr_utility.set_location(l_proc_name,10);
80 SELECT ppa.legislative_parameters,
81 pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters),
82 pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters),
83 pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters),
84 ppa.start_date,
85 ppa.effective_date,
86 ppa.business_group_id
87 INTO leg_param,
88 l_consolidation_set_id,
89 l_payroll_id,
90 l_organization_id,
91 l_leg_start_date,
92 l_leg_end_date,
93 l_business_group_id
94 FROM pay_payroll_actions ppa
95 WHERE ppa.payroll_action_id = pactid;
96
97 hr_utility.set_location(l_proc_name,15);
98 IF l_consolidation_set_id IS NOT NULL THEN
99 hr_utility.set_location(l_proc_name,20);
100 l_consolidation_set_text := 'and pa1.consolidation_set_id = ' || to_char(l_consolidation_set_id) ;
101 ELSE
102 hr_utility.set_location(l_proc_name,25);
103 l_consolidation_set_text := NULL;
104 END IF;
105
106 IF l_payroll_id IS NOT NULL THEN
107 hr_utility.set_location(l_proc_name,30);
108 l_payroll_text := 'and pa1.payroll_id = ' || to_char(l_payroll_id) ;
109 ELSE
110 hr_utility.set_location(l_proc_name,35);
111 l_payroll_text := null;
112 END IF;
113
114 hr_utility.set_location(l_proc_name,40);
115 sqlstr :=
116 'SELECT distinct asg.person_id
117 FROM pay_payroll_actions ppa,
118 pay_payroll_actions pa1,
119 pay_assignment_actions act,
120 per_assignments_f asg,
121 pay_payrolls_f ppf
122 WHERE ppa.payroll_action_id = :payroll_action_id
123 '||l_consolidation_set_text||'
124 '||l_payroll_text||'
125 and pa1.effective_date between ppa.start_date
126 and ppa.effective_date
127 and pa1.effective_date between asg.effective_start_date
128 and asg.effective_end_date
129 and pa1.action_type in (''P'',''U'',''V'')
130 and pa1.payroll_action_id = act.payroll_action_id
131 and asg.assignment_id = act.assignment_id
132 and act.action_status = ''C''
133 and asg.organization_id = nvl('''||l_organization_id||''',
134 asg.organization_id)
135 /*and asg.location_id = nvl('''||l_location_id||''',
136 asg.location_id)
137 and asg.person_id = nvl('''||l_person_id||''',
138 asg.person_id)*/
139 and asg.business_group_id +0 = ppa.business_group_id
140 and asg.payroll_id = ppf.payroll_id
141 and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
142 and ppf.payroll_id >=0
143 order by asg.person_id';
144
145 hr_utility.set_location('Exiting :'||l_proc_name,45);
146
147 END range_cursor;
148
149
150 ----------------------------- action_creation --------------------------------
151 PROCEDURE action_creation( pactid in number
152 ,stperson in number
153 ,endperson in number
154 ,chunk in number)
155 IS
156
157 CURSOR c_inputs(pactid NUMBER)
158 IS
159 SELECT pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id
160 ,pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id
161 ,pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id
162 ,pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id
163 ,pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id
164 ,pay_payrg_pkg.get_parameter('PASID',ppa.legislative_parameters) assignment_set_id
165 ,pay_payrg_pkg.get_parameter('L_E_ID',ppa.legislative_parameters) legal_employer_id
166 ,ppa.start_date start_date
167 ,ppa.effective_date effective_date
168 FROM pay_payroll_actions ppa
169 WHERE ppa.payroll_action_id = pactid;
170
171 CURSOR c_actions(
172 c_stperson number
173 ,c_endperson number
174 ,c_payroll_id number
175 ,c_consolidation_set_id number
176 ,c_tax_unit_id number
177 ,c_legal_emp_id number
178 ,c_organization_id number
179 ,c_person_id number
180 ,c_business_group_id number
181 ,c_start_date date
182 ,c_effective_date date
183 )
184 IS
185 SELECT /*+ ORDERED */
186 act.assignment_action_id,
187 act.assignment_id,
188 act.tax_unit_id,
189 ppa.action_type,
190 ppa.effective_date,
191 act.source_action_id,
192 nvl(ppa.start_date,ppa.effective_date)
193 FROM pay_payrolls_f ppf,
194 pay_payroll_actions ppa, /* pre-payments and reversals
195 payroll action id */
196 pay_assignment_actions act,
197 per_assignments_f paf
198 WHERE (c_payroll_id is NULL
199 OR ppa.payroll_id = c_payroll_id)
200 AND ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
201 ppa.consolidation_set_id)
202 AND ppa.effective_date >= c_start_date
203 AND nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
204 AND ppa.action_type IN ('P','U','V')
205 AND act.action_status = 'C'
206 AND act.payroll_action_id = ppa.payroll_action_id
207 AND ppa.business_group_id +0 = c_business_group_id
208 AND paf.assignment_id = act.assignment_id
209 AND (c_tax_unit_id IS NULL
210 OR act.tax_unit_id = c_tax_unit_id)
211 AND act.tax_unit_id IN (SELECT tax_unit_id
212 FROM per_mx_gres_legal_employer_v
213 WHERE legal_employer_id = c_legal_emp_id)
214 AND (c_organization_id IS NULL
215 OR paf.organization_id = c_organization_id)
216 AND (c_person_id IS NULL
217 OR paf.person_id = c_person_id)
218 AND paf.person_id BETWEEN c_stperson AND c_endperson
219 AND paf.business_group_id +0 = c_business_group_id
220 AND ppa.effective_date BETWEEN paf.effective_start_date
221 AND paf.effective_end_date
222 AND ppa.payroll_id = ppf.payroll_id
223 AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
224 AND ppf.payroll_id >= 0
225 ORDER BY act.assignment_action_id, act.assignment_id DESC;
226
227 -- This cursor will take care of the assignment set id parameter.
228 CURSOR c_actions_1(
229 c_stperson number,
230 c_endperson number ,
231 c_payroll_id number,
232 c_consolidation_set_id number,
233 c_tax_unit_id number,
234 c_legal_emp_id number,
235 c_organization_id number,
236 c_person_id number,
237 c_business_group_id number,
238 c_assignment_set_id number,
239 c_start_date date,
240 c_effective_date date
241 ) is
242 SELECT /*+ ORDERED */
243 act.assignment_action_id,
244 act.assignment_id,
245 act.tax_unit_id,
246 ppa.action_type,
247 ppa.effective_date,
248 act.source_action_id,
249 nvl(ppa.start_date,ppa.effective_date)
250 FROM hr_assignment_set_amendments hasa ,
251 per_assignments_f paf ,
252 pay_assignment_actions act ,
253 pay_payroll_actions ppa ,
254 pay_payrolls_f ppf
255 WHERE (c_payroll_id IS NULL
256 OR ppa.payroll_id = c_payroll_id)
257 AND ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
258 ppa.consolidation_set_id)
259 AND ppa.effective_date >= c_start_date
260 AND nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
261 AND ppa.action_type in ('P','U','V')
262 AND act.action_status = 'C'
263 AND act.payroll_action_id = ppa.payroll_action_id
264 AND ppa.business_group_id +0 = c_business_group_id
265 AND paf.assignment_id = act.assignment_id
266 AND (c_tax_unit_id IS NULL
267 OR act.tax_unit_id = c_tax_unit_id)
268 AND act.tax_unit_id IN (SELECT tax_unit_id
269 FROM per_mx_gres_legal_employer_v
270 WHERE legal_employer_id = c_legal_emp_id)
271 AND (c_organization_id IS NULL
272 OR paf.organization_id = c_organization_id)
273 AND (c_person_id IS NULL
274 OR paf.person_id = c_person_id)
275 AND hasa.assignment_set_id = c_assignment_set_id
276 AND hasa.assignment_id = paf.assignment_id
277 AND paf.person_id BETWEEN c_stperson AND c_endperson
278 AND paf.business_group_id +0 = c_business_group_id
279 AND ppa.effective_date BETWEEN paf.effective_start_date
280 AND paf.effective_end_date
281 AND ppa.payroll_id = ppf.payroll_id
282 AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
283 AND ppf.payroll_id >= 0
284 ORDER BY act.assignment_action_id, act.assignment_id;
285
286
287 /*****************************************************************
288 ** This cursor will get all the source actions for which the
289 ** assignment should get a deposit advice.
290 ** assignment action for each pre-payment (bug 890222) i.e.
291 ** Seperate Depsoit Advice for Seperate Check and Regular Run
292 *****************************************************************/
293 CURSOR c_payments (cp_pre_pymt_action_id IN NUMBER) IS
294 SELECT DISTINCT ppp.source_action_id
295 FROM pay_pre_payments ppp
296 WHERE ppp.assignment_action_id = cp_pre_pymt_action_id
297 ORDER BY ppp.source_action_id;
298
299 CURSOR c_payment_info (cp_pre_pymt_action_id IN NUMBER) IS
300 SELECT DISTINCT nvl(ppp.source_action_id,-999)
301 FROM pay_payment_information_v ppp
302 WHERE ppp.assignment_action_id = cp_pre_pymt_action_id
303 AND ppp.action_status = 'C'
304 ORDER BY 1;
305
306 CURSOR c_run_eff_date (cp_pre_pymt_action_id IN NUMBER) IS
307 SELECT ppa.effective_date,
308 ppa.action_type
309 FROM pay_action_interlocks pai,
310 pay_assignment_actions paa,
311 pay_payroll_actions ppa
312 WHERE pai.locking_action_id = cp_pre_pymt_action_id
313 AND paa.assignment_action_id = pai.locked_action_id
314 AND ppa.payroll_action_id = paa.payroll_action_id
315 AND ppa.action_type in ('R', 'Q', 'B');
316
317 CURSOR c_payroll_run (cp_pre_pymt_action_id IN NUMBER) IS
318 SELECT assignment_action_id, ppa.action_type
319 FROM pay_action_interlocks pai,
320 pay_assignment_actions paa,
321 pay_payroll_actions ppa
322 WHERE pai.locking_action_id = cp_pre_pymt_action_id
323 AND paa.assignment_action_id = pai.locked_action_id
324 AND ppa.payroll_action_id = paa.payroll_action_id
325 AND ((paa.run_type_id IS NULL and paa.source_action_id IS NULL) OR
326 (paa.run_type_id IS NOT NULL AND paa.source_action_id IS NOT NULL
327 AND paa.run_type_id IN
328 (SELECT prt.run_type_id
329 FROM pay_run_types_f prt
330 WHERE prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
331 AND prt.legislation_code = 'MX'
332 AND ppa.effective_date BETWEEN prt.effective_start_date
333 AND prt.effective_end_date)
334 )
335 )
336 ORDER BY assignment_action_id DESC;
337
338 CURSOR c_multi_ba_acts(cp_pre_pymt_action_id IN NUMBER) IS
339 SELECT assignment_action_id, ppa.action_type
340 FROM pay_action_interlocks pai,
341 pay_assignment_actions paa,
342 pay_payroll_actions ppa
343 WHERE pai.locking_action_id = cp_pre_pymt_action_id
344 AND paa.assignment_action_id = pai.locked_action_id
345 AND ppa.payroll_action_id = paa.payroll_action_id
346 AND ((paa.run_type_id IS NULL AND paa.source_action_id IS NULL) OR
347 (paa.run_type_id IS NOT NULL AND paa.source_action_id IS NOT NULL
348 AND paa.run_type_id IN
349 (SELECT prt.run_type_id
350 FROM pay_run_types_f prt
351 WHERE prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
352 AND prt.legislation_code = 'MX'
353 AND ppa.effective_date BETWEEN prt.effective_start_date
354 AND prt.effective_end_date)
355 )
356 )
357 AND ppa.action_type = 'B'
358 ORDER BY assignment_action_id DESC;
359
360 CURSOR c_payroll_def (cp_assignment_id IN NUMBER
361 ,cp_effective_date IN DATE) IS
362 SELECT multi_assignments_flag
363 FROM pay_payrolls_f ppf,
364 per_assignments_f paf
365 WHERE paf.payroll_id = ppf.payroll_id
366 AND cp_effective_date BETWEEN paf.effective_start_date
367 AND paf.effective_end_date
368 AND cp_effective_date BETWEEN ppf.effective_start_date
369 AND ppf.effective_end_date
370 AND paf.assignment_id = cp_assignment_id;
371
372
373 -- May need to add fetch of paa2.tax_unit_id and group by
374 -- this will corectly create the appropiate number of assignment
375 -- actions for the report. Also, pass tax_unit_id to this query
376 -- to only return the specific tu assignment actions.
377
378
379 CURSOR c_multi_asg_acts (cp_pre_pymt_action_id IN NUMBER) IS
380 SELECT max(paa2.assignment_action_id)
381 FROM pay_assignment_actions paa2, -- assignment_actions for slave payroll runs.
382 pay_assignment_actions paa1, -- assignment_action for master payroll run
383 pay_run_Types_f prt,
384 pay_payroll_actions ppa,
385 pay_action_interlocks pai
386 WHERE pai.locking_action_id = cp_pre_pymt_action_id
387 AND pai.locked_action_id = paa1.assignment_action_id
388 AND paa1.source_action_id IS NULL -- master assignment_action
389 AND paa1.assignment_action_id = paa2.source_action_id
390 AND paa1.payroll_action_id = paa2.payroll_action_id
391 AND paa2.run_type_id = prt.run_type_id
392 AND prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
393 AND prt.legislation_code = 'MX'
394 AND paa2.payroll_action_id = ppa.payroll_action_id
395 AND ppa.effective_date BETWEEN prt.effective_start_date
396 AND prt.effective_end_date;
397
398
399
400 -- May need to add fetch of paa2.tax_unit_id and group by
401 -- this will corectly create the appropiate number of assignment
402 -- actions for the report. Also, pass tax_unit_id to this query
403 -- to only return the specific tu assignment actions.
404
405 CURSOR c_multi_asg_rpt_acts (cp_pre_pymt_action_id IN NUMBER) IS
406 SELECT DISTINCT max(paa2.assignment_action_id)
407 FROM pay_assignment_actions paa2,
408 -- assignment_actions for slave payroll runs.
409 pay_assignment_actions paa1,
410 -- assignment_action for master payroll run
411 pay_run_Types_f prt,
412 pay_payroll_actions ppa,
413 pay_action_interlocks pai
414 WHERE pai.locking_action_id = cp_pre_pymt_action_id
415 AND pai.locked_action_id = paa1.assignment_action_id
416 AND paa1.source_action_id is null -- master assignment_action
417 AND paa1.assignment_action_id = paa2.source_action_id
418 AND paa1.payroll_action_id = paa2.payroll_action_id
419 AND paa2.run_type_id = prt.run_type_id
420 AND prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
421 AND prt.legislation_code = 'MX'
422 AND paa2.payroll_action_id = ppa.payroll_action_id
423 AND ppa.effective_date BETWEEN prt.effective_start_date
424 AND prt.effective_end_date
425 GROUP BY paa1.assignment_action_id;
426
427 CURSOR c_check_for_void (cp_pre_pymt_action_id IN NUMBER) IS
428 SELECT 'Y'
429 FROM pay_action_interlocks pai,
430 pay_assignment_actions paa,
431 pay_payroll_actions ppa
432 WHERE pai.locking_action_id = cp_pre_pymt_action_id
433 AND paa.assignment_action_id = pai.locked_action_id
434 AND ppa.payroll_action_id = paa.payroll_action_id
435 AND action_type = 'V';
436
437 CURSOR c_check_for_assig_act (cp_assig_id IN NUMBER) IS
438 SELECT assignment_action_id
439 FROM pay_assignment_actions
440 WHERE assignment_id = cp_assig_id
441 AND payroll_action_id = pactid;
442
443 lockingactid NUMBER;
444 lockedactid NUMBER;
445 assignid NUMBER;
446 greid NUMBER;
447 num NUMBER;
448 runactid NUMBER;
449 actiontype VARCHAR2(1);
450 serialno VARCHAR2(30);
451 l_leg_param pay_payroll_actions.legislative_parameters%TYPE;
452 l_asg_set_id NUMBER;
453 l_asg_flag VARCHAR2(10);
454 l_effective_date DATE;
455 l_start_date DATE;
456 l_multi_asg_flag VARCHAR(1);
457 l_source_action_id NUMBER;
458 ln_pre_pymt_action_id NUMBER;
459 ln_source_action_id NUMBER;
460 ln_prev_source_action_id NUMBER := null;
461 ln_master_action_id NUMBER;
462 lv_run_action_type VARCHAR2(30);
463 lv_sep_check VARCHAR2(1);
464 lv_multi_asg_flag VARCHAR2(1);
465 lv_source_action_id NUMBER;
466 l_asg_act_id NUMBER;
467 l_action_insert VARCHAR2(1);
468 l_void_action VARCHAR2(1);
469 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
470 l_location_id per_all_assignments_f.location_id%TYPE;
471 l_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
472 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
473 l_person_id per_all_assignments_f.person_id%TYPE;
474 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
475 l_organization_id per_all_assignments_f.organization_id%TYPE;
476 l_assignment_set_id hr_assignment_set_amendments.assignment_set_id%TYPE;
477 cp_start_date pay_payroll_actions.effective_date%TYPE;
478 cp_effective_date pay_payroll_actions.effective_date%TYPE;
479 l_run_eff_date DATE;
480 run_action_type VARCHAR2(30);
481 lv_max_run_flag VARCHAR2(1) := 'N' ;
482 lv_max_run_id NUMBER;
483 l_legal_emp_id NUMBER;
484 l_proc_name VARCHAR2(100) := g_package_name||'.ACTION_CREATION';
485 -- algorithm is quite similar to the other process cases,
486 -- but we have to take into account assignments and
487 -- personal payment methods.
488 BEGIN
489
490 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.action_creation ');
491 hr_utility.trace('pay_mx_payreg_pkg.action_creation ');
492 hr_utility.set_location('Entering '||l_proc_name,5);
493
494 SELECT legislative_parameters
495 INTO l_leg_param
496 FROM pay_payroll_actions ppa
497 WHERE ppa.payroll_action_id = pactid;
498
499 hr_utility.set_location(l_proc_name,10);
500
501 OPEN c_inputs( pactid);
502 FETCH c_inputs INTO l_payroll_id,
503 l_consolidation_set_id ,
504 l_tax_unit_id,
505 l_organization_id ,
506 l_business_group_id,
507 l_assignment_set_id,
508 l_legal_emp_id,
509 cp_start_date,
510 cp_effective_date ;
511 CLOSE c_inputs;
512
513 hr_utility.trace('stperson '||stperson);
514 hr_utility.trace('endperson '||endperson);
515 hr_utility.trace('l_payroll_id '||l_payroll_id);
516 hr_utility.trace('l_consolidation_set_id '||l_consolidation_set_id);
517 hr_utility.trace('l_tax_unit_id '||l_tax_unit_id);
518 hr_utility.trace('l_location_id '||l_location_id);
519 hr_utility.trace('l_organization_id '||l_organization_id);
520 hr_utility.trace('l_business_group_id '||l_business_group_id);
521 hr_utility.trace('l_assignment_set_id '||l_assignment_set_id);
522 hr_utility.trace('l_legal_emp_id '||l_legal_emp_id);
523 hr_utility.trace('cp_start_date '||cp_start_date);
524 hr_utility.trace('cp_effective_date '||cp_effective_date);
525
526 IF l_assignment_set_id IS NULL THEN
527 hr_utility.set_location(l_proc_name,15);
528 OPEN c_actions(stperson
529 ,endperson
530 ,l_payroll_id
531 ,l_consolidation_set_id
532 ,l_tax_unit_id
533 ,l_legal_emp_id
534 ,l_organization_id
535 ,l_person_id
536 ,l_business_group_id
537 ,cp_start_date
538 ,cp_effective_date);
539 ELSE
540 hr_utility.set_location(l_proc_name,20);
541 OPEN c_actions_1( stperson
542 ,endperson
543 ,l_payroll_id
544 ,l_consolidation_set_id
545 ,l_tax_unit_id
546 ,l_legal_emp_id
547 ,l_organization_id
548 ,l_person_id
549 ,l_business_group_id
550 ,l_assignment_set_id
551 ,cp_start_date
552 ,cp_effective_date);
553
554 END IF;
555
556 hr_utility.set_location(l_proc_name,25);
557 l_asg_set_id := pay_payrg_pkg.get_parameter('PASID',l_leg_param);
558 num := 0;
559
560 LOOP
561 hr_utility.set_location(l_proc_name,30);
562
563 IF l_assignment_set_id IS NULL THEN
564 hr_utility.set_location(l_proc_name,35);
565
566 FETCH c_actions INTO lockedactid
567 ,assignid
568 ,greid
569 ,actiontype
570 ,l_effective_date
571 ,l_source_action_id
572 ,l_start_date;
573
574 IF c_actions%found then
575 hr_utility.set_location(l_proc_name,40);
576 num := num + 1;
577 END IF;
578 EXIT WHEN c_actions%NOTFOUND;
579
580 ELSE
581 hr_utility.set_location(l_proc_name,45);
582 FETCH c_actions_1 INTO lockedactid
583 ,assignid
584 ,greid
585 ,actiontype
586 ,l_effective_date
587 ,l_source_action_id
588 ,l_start_date;
589
590 IF c_actions_1%found THEN
591 hr_utility.set_location(l_proc_name,50);
592 num := num + 1;
593 END IF;
594 EXIT WHEN c_actions_1%NOTFOUND;
595 END IF;
596
597 hr_utility.set_location(l_proc_name,55);
598 l_asg_flag := 'N';
599 IF l_asg_set_id IS NOT NULL THEN
600 hr_utility.set_location(l_proc_name,60);
601 l_asg_flag := hr_assignment_set.assignment_in_set(l_asg_set_id, assignid);
602 ELSE -- l_asg_set_id is null
603 hr_utility.set_location(l_proc_name,65);
604 l_asg_flag := 'Y';
605 END IF;
606
607 hr_utility.set_location(l_proc_name,70);
608 IF (actiontype = 'P'
609 OR actiontype = 'U') THEN
610
611 hr_utility.set_location(l_proc_name,75);
612 OPEN c_run_eff_date (lockedactid);
613
614 hr_utility.set_location(l_proc_name,80);
615 FETCH c_run_eff_date INTO l_run_eff_date,
616 run_action_type;
617
618 IF c_run_eff_date%NOTFOUND THEN
619 hr_utility.set_location(l_proc_name,85);
620 l_asg_flag := 'N';
621 END IF;
622 CLOSE c_run_eff_date;
623
624 hr_utility.set_location(l_proc_name,90);
625 IF l_run_eff_date BETWEEN cp_start_date AND cp_effective_date THEN
626 hr_utility.set_location(l_proc_name,95);
627 NULL;
628 ELSE
629 hr_utility.set_location(l_proc_name,100);
630 l_asg_flag := 'N';
631 END IF;
632 END IF;
633
634 hr_utility.set_location(l_proc_name,105);
635
636 IF l_asg_flag = 'Y' THEN
637 -- we need to insert one action for each of the
638 -- rows that we return from the cursor (i.e. one
639 -- for each assignment/pre-payment/reversal).
640 hr_utility.set_location(l_proc_name,110);
641
642 IF actiontype in ('P', 'U') THEN
643 hr_utility.set_location(l_proc_name,115);
644 OPEN c_payment_info (lockedactid);
645 LOOP
646 FETCH c_payment_info INTO ln_source_action_id;
647 EXIT WHEN c_payment_info%NOTFOUND;
648 IF ln_source_action_id = -999 THEN
649 hr_utility.set_location(l_proc_name,120);
650 SELECT pay_assignment_actions_s.nextval
651 INTO lockingactid
652 FROM dual;
653 hr_utility.set_location(l_proc_name,125);
654 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
655
656 hr_utility.set_location(l_proc_name,130);
657 hr_nonrun_asact.insint(lockingactid,lockedactid);
658
659 hr_utility.set_location(l_proc_name,135);
660 OPEN c_payroll_run (lockedactid);
661 FETCH c_payroll_run INTO ln_master_action_id,lv_run_action_type;
662 CLOSE c_payroll_run;
663
664 serialno := lv_run_action_type || to_char(ln_master_action_id);
665 -- update pay_assignment_actions serial_number with runactid.
666 UPDATE pay_assignment_actions
667 SET serial_number = serialno
668 WHERE assignment_action_id = lockingactid
669 AND tax_unit_id = greid;
670 hr_utility.set_location(l_proc_name,140);
671 -- Insert a row in pay_us_rpt_totals which includes
672 -- payroll_action_id,
673 -- report created assignment_action_id (lockingactid)
674 -- and "payroll run" assignment_action id.
675 INSERT INTO pay_us_rpt_totals
676 (session_id,
677 tax_unit_id,
678 location_id,
679 value1,
680 value2,
681 attribute30)
682 values(pactid,
683 pactid,
684 lockingactid,
685 ln_master_action_id,
686 assignid,
687 'MX_PYR');
688 hr_utility.set_location(l_proc_name,145);
689 ELSIF ln_source_action_id <> -999 THEN
690 hr_utility.set_location(l_proc_name,150);
691 -- #16364431 Start
692 SELECT pay_assignment_actions_s.nextval
693 INTO lockingactid
694 FROM dual;
695 hr_utility.set_location(l_proc_name,152);
696
697 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
698
699 hr_utility.set_location(l_proc_name,153);
700 -- #16364431 End
701 /**************************************************************
702 ** we need to insert one action for each of the rows that we
703 ** return from the cursor (i.e. one for each assignment/pre-payment source).
704 **************************************************************/
705 hr_nonrun_asact.insint(lockingactid,lockedactid);
706
707 hr_utility.set_location(l_proc_name,155);
708 serialno := actiontype || to_char(ln_source_action_id);
709 -- update pay_assignment_actions serial_number with runactid.
710 UPDATE pay_assignment_actions
711 SET serial_number = serialno
712 WHERE assignment_action_id = lockingactid
713 AND tax_unit_id = greid;
714 runactid := ln_source_action_id;
715 lv_sep_check := 'Y';
716 hr_utility.set_location(l_proc_name,160);
717 INSERT INTO pay_us_rpt_totals
718 (session_id,
719 tax_unit_id,
720 location_id,
721 value1,
722 value2,
723 attribute1,
724 attribute30)
725 VALUES(pactid,
726 pactid,
727 lockingactid,
728 runactid,
729 assignid,
730 lv_sep_check,
731 'MX_PYR');
732 hr_utility.set_location(l_proc_name,165);
733 END IF;
734 hr_utility.set_location(l_proc_name,170);
735 END LOOP;
736 CLOSE c_payment_info;
737 ELSE -- This is a void action.
738 hr_utility.set_location(l_proc_name,175);
739 SELECT pay_assignment_actions_s.nextval
740 INTO lockingactid
741 FROM dual;
742 hr_utility.set_location(l_proc_name,180);
743 -- insert the action record.
744 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
745
746 hr_utility.set_location(l_proc_name,190);
747 -- insert an interlock to this action.
748 hr_nonrun_asact.insint(lockingactid,lockedactid);
749 hr_utility.set_location(l_proc_name,200);
750
751 BEGIN
752 hr_utility.set_location(l_proc_name,205);
753 serialno := actiontype || to_char(lockedactid);
754 -- update pay_assignment_actions serial_number with runactid.
755 UPDATE pay_assignment_actions
756 SET serial_number = serialno
757 WHERE assignment_action_id = lockingactid
758 AND tax_unit_id = greid;
759 EXCEPTION
760 WHEN OTHERS THEN
761 hr_utility.set_location(l_proc_name,210);
762 NULL;
763 END;
764 hr_utility.set_location(l_proc_name,215);
765 -- Insert a row in pay_us_rpt_totals which includes
766 -- payroll_action_id,
767 -- report created assignment_action_id (lockingactid)
768 -- and "payroll run" assignment_action id.
769 INSERT INTO pay_us_rpt_totals
770 (session_id,
771 tax_unit_id,
772 location_id,
773 value1,
774 value2,
775 attribute30
776 )
777 VALUES(pactid,
778 pactid,
779 lockingactid,
780 lockedactid,
781 assignid,
782 'MX_PYR'
783 );
784 hr_utility.set_location(l_proc_name,220);
785 END IF; -- if action_type in ('P', 'U');
786 hr_utility.set_location(l_proc_name,225);
787 END IF; -- if l_asg_flag = 'Y'
788 hr_utility.set_location(l_proc_name,230);
789 END LOOP;
790
791 IF l_assignment_set_id is NULL then
792 -- hr_utility.trace('Closing c_actions');
793 hr_utility.set_location(l_proc_name,235);
794 CLOSE c_actions;
795 ELSE
796 -- hr_utility.trace('Closing c_actions_1');
797 hr_utility.set_location(l_proc_name,240);
798 CLOSE c_actions_1;
799 END IF;
800 --hr_utility.trace_off;
801 END action_creation;
802
803 -------------------------------- write_to_magtape ------------------------------
804
805 PROCEDURE write_to_magtape_lob(p_data VARCHAR2) IS
806 BEGIN
807 fnd_file.put_line(fnd_file.log,'pay_mx_payreg_pkg.write_to_magtape_lob ');
808 pay_core_files.write_to_magtape_lob (p_data);
809 END write_to_magtape_lob;
810
811 -------------------------------- load_xml_internal -----------------------------
812
813 PROCEDURE load_xml_internal ( p_node_type VARCHAR2
814 ,p_node VARCHAR2
815 ,p_data VARCHAR2) IS
816 l_proc_name VARCHAR2(100) := g_package_name||'.LOAD_XML_INTERNAL';
817 l_data VARCHAR2(240);
818 l_xml VARCHAR2(240);
819
820 BEGIN
821 fnd_file.put_line(fnd_file.log,'pay_mx_payreg_pkg.load_xml_internal ');
822 hr_utility.set_location('Entering:'||l_proc_name,10);
823
824 IF p_node_type = 'CS' THEN
825
826 l_xml := '<'||p_node||'>'||EOL;
827
828 ELSIF p_node_type = 'CE' THEN
829
830 l_xml := '</'||p_node||'>'||EOL;
831
832 ELSIF p_node_type = 'D' THEN
833
834 /* Handle special charaters in data */
835 l_data := REPLACE (p_data, '&', '&');
836 l_data := REPLACE (l_data, '>', '>');
837 l_data := REPLACE (l_data, '<', '<');
838 l_data := REPLACE (l_data, '''', ''');
839 l_data := REPLACE (l_data, '"', '"');
840 l_xml := '<'||p_node||'>'||l_data||'</'||p_node||'>'||EOL;
841
842 END IF;
843
844 write_to_magtape_lob (l_xml);
845
846 hr_utility.set_location('Leaving:'||l_proc_name,20);
847
848 END load_xml_internal;
849
850 -------------------------------- populate_xml_table -----------------------------
851
852 PROCEDURE populate_xml_table( name IN VARCHAR2
853 ,value IN VARCHAR2
854 ,type IN VARCHAR2 ) IS
855 ln_index NUMBER;
856
857 BEGIN
858 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.populate_xml_table ');
859 ln_index := payroll_reg_xml_tbl.COUNT;
860
861 payroll_reg_xml_tbl(ln_index).name := name;
862 payroll_reg_xml_tbl(ln_index).value := value;
863
864 END populate_xml_table;
865
866 ---------------------------------- generate_xml ---------------------------------
867 PROCEDURE generate_xml IS
868
869 CURSOR c_inputs(pactid number) is
870 SELECT pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
871 pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
872 pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id,
873 pay_payrg_pkg.get_parameter('RP_DM',ppa.legislative_parameters) dimension_id
874 FROM pay_payroll_actions ppa
875 WHERE ppa.payroll_action_id = pactid;
876
877 CURSOR c_get_balance_dimensions (dimtype varchar2)is
878 SELECT balance_dimension_id
879 FROM pay_balance_dimensions
880 WHERE database_item_suffix = dimtype
881 AND legislation_code = 'MX';
882
883 CURSOR c_get_assig_act_id (pactid number
884 ,assigactid number) is
885 SELECT fnd_number.canonical_to_number(value1)
886 FROM pay_us_rpt_totals
887 WHERE session_id = fnd_number.number_to_canonical(pactid)
888 AND location_id = fnd_number.number_to_canonical(assigactid)
889 AND attribute30 = 'MX_PYR';
890
891 CURSOR c_get_assig_id (pactid number
892 ,assigactid number) is
893 SELECT DISTINCT fnd_number.canonical_to_number(value2)
894 FROM pay_us_rpt_totals
895 WHERE session_id = fnd_number.number_to_canonical(pactid)
896 AND location_id = fnd_number.number_to_canonical(assigactid)
897 AND attribute30 = 'MX_PYR';
898
899 CURSOR c_get_def_bal_id is
900 SELECT DISTINCT fue_live.user_entity_name,
901 fue_live.creator_id,
902 0 tmp_bal_value
903 FROM pay_defined_balances pdb_call,
904 pay_balance_dimensions pbd,
905 pay_balance_types pbt,
906 ff_user_entities fue_live
907 WHERE pbt.balance_name IN ('Earnings'
908 ,'Deductions'
909 ,'Net Pay'
910 ,'Gross Earnings'
911 ,'Tax Deductions'
912 ,'ISR Withheld'
913 ,'ISR Calculated'
914 ,'ISR Exempt'
915 ,'ISR Subsidy for Employment'
916 ,'ISR Subsidy for Employment Paid'
917 ,'Employer State Tax Withheld'
918 ,'Social Security Quota EE'
919 ,'Social Security Quota ER'
920 )
921 AND pbt.balance_type_id = pdb_call.balance_type_id
922 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
923 AND pbd.database_item_suffix = '_ASG_GRE_RUN'
924 AND pbd.legislation_code = pbt.legislation_code
925 AND pbt.legislation_code = 'MX'
926 AND fue_live.creator_id = pdb_call.defined_balance_id
927 AND fue_live.creator_type = 'B'
928 ORDER BY fue_live.user_entity_name;
929
930 CURSOR c_get_def_bal_id_mtd is
931 SELECT DISTINCT fue_live.user_entity_name,
932 fue_live.creator_id,
933 0 tmp_bal_value
934 FROM pay_defined_balances pdb_call,
935 pay_balance_dimensions pbd,
936 pay_balance_types pbt,
937 ff_user_entities fue_live
938 WHERE pbt.balance_name IN ('Earnings'
939 ,'Deductions'
940 ,'Net Pay'
941 ,'Gross Earnings'
942 ,'Tax Deductions'
943 ,'ISR Withheld'
944 ,'ISR Calculated'
945 ,'ISR Exempt'
946 ,'ISR Subsidy for Employment'
947 ,'ISR Subsidy for Employment Paid'
948 ,'Employer State Tax Withheld'
949 ,'Social Security Quota EE'
950 ,'Social Security Quota ER'
951 )
952 AND pbt.balance_type_id = pdb_call.balance_type_id
953 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
954 AND pbd.database_item_suffix IN ('_ASG_GRE_MTD','_ASG_GRE_RUN')
955 AND pbd.legislation_code = pbt.legislation_code
956 AND pbt.legislation_code = 'MX'
957 AND fue_live.creator_id = pdb_call.defined_balance_id
958 AND fue_live.creator_type = 'B'
959 ORDER BY fue_live.user_entity_name;
960
961 CURSOR c_get_def_bal_id_ytd is
962 SELECT DISTINCT fue_live.user_entity_name,
963 fue_live.creator_id,
964 0 tmp_bal_value
965 FROM pay_defined_balances pdb_call,
966 pay_balance_dimensions pbd,
967 pay_balance_types pbt,
968 ff_user_entities fue_live
969 WHERE pbt.balance_name IN ('Earnings'
970 ,'Deductions'
971 ,'Net Pay'
972 ,'Gross Earnings'
973 ,'Tax Deductions'
974 ,'ISR Withheld'
975 ,'ISR Calculated'
976 ,'ISR Exempt'
977 ,'ISR Subsidy for Employment'
978 ,'ISR Subsidy for Employment Paid'
979 ,'Employer State Tax Withheld'
980 ,'Social Security Quota EE'
981 ,'Social Security Quota ER'
982 )
983 AND pbt.balance_type_id = pdb_call.balance_type_id
984 AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
985 AND pbd.database_item_suffix IN ('_ASG_GRE_YTD','_ASG_GRE_RUN')
986 AND pbd.legislation_code = pbt.legislation_code
987 AND pbt.legislation_code = 'MX'
988 AND fue_live.creator_id = pdb_call.defined_balance_id
989 AND fue_live.creator_type = 'B'
990 ORDER BY fue_live.user_entity_name;
991
992 CURSOR c_get_emp_name(assigid number) is
993 SELECT pap.full_name
994 ,paf.assignment_number
995 FROM per_assignments_f paf,
996 per_all_people_f pap
997 WHERE paf.assignment_id = assigid
998 AND pap.person_id = paf.person_id;
999
1000 CURSOR c_get_payroll_period (asg_action_id NUMBER) is
1001 SELECT ptp.start_date
1002 ,ptp.end_date
1003 FROM pay_payroll_actions ppa
1004 ,pay_assignment_actions paa
1005 ,per_time_periods ptp
1006 WHERE ppa.payroll_action_id = paa.payroll_action_id
1007 AND ptp.time_period_id = ppa.time_period_id
1008 AND paa.assignment_action_id = asg_action_id;
1009
1010 CURSOR c_get_payroll_details (asg_action_id NUMBER) is
1011 SELECT ppf.payroll_name
1012 ,pcs.consolidation_set_name
1013 FROM pay_payroll_actions ppa
1014 ,pay_assignment_actions paa
1015 ,pay_payrolls_f ppf
1016 ,pay_consolidation_sets pcs
1017 WHERE ppa.payroll_action_id = paa.payroll_action_id
1018 AND ppa.payroll_id = ppf.payroll_id
1019 AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1020 AND ppf.consolidation_set_id = pcs.consolidation_set_id
1021 AND paa.assignment_action_id = asg_action_id;
1022
1023 ln_tax_unit_id NUMBER;
1024 ln_organization_id NUMBER;
1025 ln_business_group_id NUMBER;
1026 ln_dimension_type NUMBER;
1027 ln_dimension_id_1 NUMBER;
1028 ln_dimension_id_2 NUMBER;
1029 ln_assignment_act_id NUMBER;
1030 ln_assig_id NUMBER;
1031 lv_assignment_number VARCHAR2(50);
1032 ln_payroll_act_id NUMBER;
1033 ln_curr_assign_act_id NUMBER;
1034 lv_person_name VARCHAR2(240);
1035 ld_period_start_date DATE;
1036 ld_period_end_date DATE;
1037 lv_payroll_name VARCHAR2(240);
1038 lv_consolidated_set_name VARCHAR2(240);
1039 lt_pr_bal_context_tab pay_balance_pkg.t_context_tab;
1040 lt_pr_bal_result_tab pay_balance_pkg.t_detailed_bal_out_tab;
1041 l_proc_name VARCHAR2(100) := g_package_name||'.GENERATE_XML';
1042 BEGIN
1043
1044 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.generate_xml ');
1045 hr_utility.set_location ('Entering'|| l_proc_name ,10);
1046
1047 ln_payroll_act_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1048 ln_curr_assign_act_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
1049 hr_utility.trace ('ln_payroll_act_id ='||ln_payroll_act_id );
1050 hr_utility.trace ('ln_curr_assign_act_id ='||ln_curr_assign_act_id );
1051
1052 OPEN c_inputs (ln_payroll_act_id);
1053 FETCH c_inputs INTO ln_tax_unit_id,
1054 ln_organization_id,
1055 ln_business_group_id,
1056 ln_dimension_type;
1057 CLOSE c_inputs;
1058 hr_utility.set_location ('Entering'|| l_proc_name ,10);
1059
1060 IF ln_tax_unit_id IS NULL THEN
1061 hr_utility.set_location (l_proc_name ,15);
1062 SELECT tax_unit_id
1063 INTO ln_tax_unit_id
1064 FROM pay_assignment_actions paa
1065 WHERE paa.payroll_Action_id = ln_payroll_act_id
1066 AND assignment_action_id = ln_curr_assign_act_id;
1067 END IF;
1068
1069 hr_utility.set_location (l_proc_name ,20);
1070 lt_pr_bal_context_tab(1).tax_unit_id := ln_tax_unit_id;
1071
1072 IF ln_dimension_type = 1 THEN
1073 hr_utility.set_location (l_proc_name ,25);
1074 OPEN c_get_def_bal_id ;
1075 FETCH c_get_def_bal_id BULK COLLECT INTO g_pay_reg_defbal_details;
1076 CLOSE c_get_def_bal_id;
1077
1078 ELSIF ln_dimension_type = 2 THEN
1079 hr_utility.set_location (l_proc_name ,30);
1080 OPEN c_get_def_bal_id_mtd;
1081 FETCH c_get_def_bal_id_mtd BULK COLLECT INTO g_pay_reg_defbal_details;
1082 CLOSE c_get_def_bal_id_mtd;
1083
1084 ELSIF ln_dimension_type = 3 THEN
1085 hr_utility.set_location (l_proc_name ,35);
1086 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1087 OPEN c_get_def_bal_id_ytd;
1088 FETCH c_get_def_bal_id_ytd BULK COLLECT INTO g_pay_reg_defbal_details;
1089 CLOSE c_get_def_bal_id_ytd;
1090 END IF;
1091
1092 hr_utility.set_location (l_proc_name ,40);
1093 FOR i IN g_pay_reg_defbal_details.first..g_pay_reg_defbal_details.last
1094 LOOP
1095 hr_utility.set_location ('Entering'|| l_proc_name ,45);
1096 g_pr_balance_value_tab(i).defined_balance_id := g_pay_reg_defbal_details(i).def_bal_id;
1097 END LOOP;
1098
1099 hr_utility.set_location (l_proc_name ,50);
1100 OPEN c_get_assig_id (ln_payroll_act_id
1101 ,ln_curr_assign_act_id);
1102 FETCH c_get_assig_id INTO ln_assig_id;
1103 CLOSE c_get_assig_id;
1104
1105 hr_utility.set_location (l_proc_name ,55);
1106 OPEN c_get_assig_act_id (ln_payroll_act_id
1107 ,ln_curr_assign_act_id);
1108 FETCH c_get_assig_act_id INTO ln_assignment_act_id;
1109 CLOSE c_get_assig_act_id;
1110
1111 hr_utility.set_location (l_proc_name ,60);
1112 OPEN c_get_emp_name (ln_assig_id);
1113 FETCH c_get_emp_name INTO lv_person_name, lv_assignment_number;
1114 CLOSE c_get_emp_name;
1115
1116 hr_utility.set_location (l_proc_name ,65);
1117 OPEN c_get_payroll_period(ln_assignment_act_id);
1118 FETCH c_get_payroll_period INTO ld_period_start_date, ld_period_end_date;
1119 CLOSE c_get_payroll_period;
1120
1121 hr_utility.set_location (l_proc_name ,70);
1122 OPEN c_get_payroll_details(ln_assignment_act_id);
1123 FETCH c_get_payroll_details INTO lv_payroll_name, lv_consolidated_set_name;
1124 CLOSE c_get_payroll_details;
1125
1126 hr_utility.set_location (l_proc_name ,75);
1127 pay_balance_pkg.get_value
1128 (p_assignment_action_id => ln_assignment_act_id
1129 ,p_defined_balance_lst => g_pr_balance_value_tab
1130 ,p_context_lst => lt_pr_bal_context_tab
1131 ,p_output_table => lt_pr_bal_result_tab
1132 );
1133 hr_utility.set_location (l_proc_name ,80);
1134
1135 FOR j IN g_pay_reg_defbal_details.first..g_pay_reg_defbal_details.last
1136 LOOP
1137 hr_utility.set_location (l_proc_name ,85);
1138 g_pay_reg_defbal_details(j).bal_value := nvl(lt_pr_bal_result_tab(j).balance_value,0);
1139 populate_xml_table (g_pay_reg_defbal_details(j).user_entity_name,g_pay_reg_defbal_details(j).bal_value,'TEXT');
1140 END LOOP;
1141
1142 hr_utility.set_location (l_proc_name ,90);
1143 load_xml_internal('CS','EMPLOYEE',NULL);
1144 load_xml_internal('D','ASSIGNMENT_NO',lv_assignment_number);
1145 load_xml_internal('D','EMPLOYEE_NAME',lv_person_name);
1146 load_xml_internal('D','PERIOD_START_DATE',to_char(ld_period_start_date,'DD-MON-YYYY'));
1147 load_xml_internal('D','PERIOD_END_DATE',to_char(ld_period_end_date,'DD-MON-YYYY'));
1148 load_xml_internal('D','PAYROLL_NAME',lv_payroll_name);
1149 load_xml_internal('D','CONS_SET_NAME',lv_consolidated_set_name);
1150 hr_utility.set_location (l_proc_name ,95);
1151
1152 FOR i IN payroll_reg_xml_tbl.FIRST..payroll_reg_xml_tbl.LAST LOOP
1153 hr_utility.set_location (l_proc_name ,100);
1154 load_xml_internal('D',payroll_reg_xml_tbl(i).name,payroll_reg_xml_tbl(i).value);
1155
1156 hr_utility.set_location (l_proc_name ,105);
1157 INSERT INTO pay_us_rpt_totals (session_id
1158 ,business_group_id
1159 ,tax_unit_id
1160 ,attribute1
1161 ,attribute2
1162 ,attribute3
1163 ,attribute4
1164 ,attribute5
1165 ,value1
1166 ,attribute30
1167 )
1168 VALUES (ln_payroll_act_id
1169 ,ln_business_group_id
1170 ,ln_tax_unit_id
1171 ,ln_payroll_act_id
1172 ,ln_curr_assign_act_id
1173 ,ln_assig_id
1174 ,ln_assignment_act_id
1175 ,payroll_reg_xml_tbl(i).name
1176 ,payroll_reg_xml_tbl(i).value
1177 ,'MX_PYR'
1178 );
1179 hr_utility.set_location (l_proc_name ,110);
1180 END LOOP;
1181
1182 hr_utility.set_location (l_proc_name ,115);
1183 load_xml_internal('CE','EMPLOYEE',NULL);
1184
1185 hr_utility.set_location (l_proc_name ,120);
1186 payroll_reg_xml_tbl.DELETE;
1187
1188 hr_utility.set_location ('Leaving '||l_proc_name ,120);
1189 END generate_xml;
1190
1191 ---------------------------------- generate_xml_header -------------------------------
1192 PROCEDURE generate_xml_header AS
1193
1194 CURSOR c_inputs(pactid NUMBER) IS
1195 SELECT pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id,
1196 pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id,
1197 pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
1198 pay_payrg_pkg.get_parameter('L_E_ID',ppa.legislative_parameters) legal_employer_id,
1199 pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
1200 pay_payrg_pkg.get_parameter('PASID',ppa.legislative_parameters) assignment_set_id,
1201 pay_payrg_pkg.get_parameter('P_S1', ppa.legislative_parameters) sort1,
1202 pay_payrg_pkg.get_parameter('RP_DM',ppa.legislative_parameters) dimension_id,
1203 ppa.start_date start_date,
1204 ppa.effective_date effective_date
1205 FROM pay_payroll_actions ppa
1206 WHERE ppa.payroll_action_id = pactid;
1207
1208 CURSOR c_get_payroll_name (cp_payroll_id NUMBER) IS
1209 SELECT payroll_name
1210 FROM pay_all_payrolls_f
1211 WHERE payroll_id = cp_payroll_id;
1212
1213 CURSOR c_get_consolidation_set_name (cp_consolidation_set_id number) IS
1214 SELECT consolidation_set_name
1215 FROM pay_consolidation_sets
1216 WHERE consolidation_set_id = cp_consolidation_set_id;
1217
1218 CURSOR c_get_gre_name (cp_tax_unit_id NUMBER) IS
1219 SELECT hou.name
1220 FROM hr_organization_units hou
1221 WHERE hou.organization_id = cp_tax_unit_id;
1222
1223 CURSOR c_get_le_id (cp_tax_unit_id NUMBER) IS
1224 SELECT hou.name
1225 FROM hr_organization_units hou,
1226 per_gen_hierarchy_nodes greghn,
1227 per_gen_hierarchy_nodes leghn
1228 WHERE greghn.entity_id = fnd_number.number_to_canonical(cp_tax_unit_id)
1229 AND leghn.hierarchy_node_id = greghn.parent_hierarchy_node_id
1230 AND hou.organization_id = leghn.entity_id;
1231
1232 CURSOR c_get_assig_set_id (cp_assignment_set_id NUMBER) IS
1233 SELECT assignment_set_name
1234 FROM hr_assignment_sets
1235 WHERE assignment_set_id = cp_assignment_set_id;
1236
1237 CURSOR c_get_dimension_type (cp_dimension_type NUMBER) IS
1238 SELECT flv.meaning
1239 FROM fnd_lookup_types flt ,
1240 fnd_lookup_values flv
1241 WHERE flt.lookup_type = 'MX_DIMENSION'
1242 AND flt.lookup_type = flv.lookup_type
1243 AND flv.lookup_code = cp_dimension_type
1244 AND flv.enabled_flag = 'Y'
1245 AND flv.language = USERENV('LANG'); -- #16364978 Added this condition
1246
1247 -- Added this to get sort parameter meanings
1248 CURSOR c_get_sort_option (cp_sort_option VARCHAR2) IS
1249 SELECT flv.meaning
1250 FROM fnd_lookup_types flt ,
1251 fnd_lookup_values flv
1252 WHERE flt.lookup_type = 'PAY_MX_REG_SORT_CODE'
1253 AND flt.lookup_type = flv.lookup_type
1254 AND flv.lookup_code = cp_sort_option
1255 AND flv.enabled_flag = 'Y'
1256 AND flv.language = USERENV('LANG');
1257
1258 CURSOR csr_get_le_tot(c_payroll_action_id NUMBER) IS
1259 SELECT attribute5 balance_name
1260 ,sum(value1) total
1261 FROM pay_us_rpt_totals
1262 WHERE attribute1 = fnd_number.number_to_canonical(c_payroll_action_id)
1263 AND attribute30 = 'MX_PYR'
1264 GROUP BY attribute5;
1265
1266 lv_buf VARCHAR2(2000);
1267 ln_payroll_action_id NUMBER;
1268 ln_payroll_id NUMBER;
1269 ln_consolidation_set_id NUMBER;
1270 ln_tax_unit_id NUMBER;
1271 ln_org_id NUMBER;
1272 ln_legal_emp_id NUMBER;
1273 ln_assignment_set_id NUMBER;
1274 lv_sort_1 VARCHAR2(30);
1275 ld_start_date pay_payroll_actions.effective_date%TYPE;
1276 ld_effective_date pay_payroll_actions.effective_date%TYPE;
1277 ln_dimension_id NUMBER;
1278 lv_payroll_name VARCHAR2(240);
1279 lv_consolidation_set_name VARCHAR2(240);
1280 lv_gre_name VARCHAR2(240);
1281 lv_le_name VARCHAR2(240);
1282 lv_org_name VARCHAR2(240);
1283 lv_assignment_set_name VARCHAR2(240);
1284 lv_dimension_name VARCHAR2(240); --#16363434 Changed from 30 to 240
1285 l_proc_name VARCHAR2(100) := g_package_name||'.GENERATE_XML_HEADER';
1286 lv_sort_option VARCHAR2(240);
1287
1288 BEGIN
1289 hr_utility.set_location ('Entering'|| l_proc_name ,10);
1290 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.generate_xml_header ');
1291 payroll_reg_xml_tbl.delete;
1292
1293 hr_utility.set_location ( l_proc_name ,15);
1294 ln_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1295
1296 hr_utility.set_location ( l_proc_name ,20);
1297
1298 OPEN c_inputs (ln_payroll_action_id);
1299 FETCH c_inputs INTO ln_payroll_id,
1300 ln_consolidation_set_id,
1301 ln_tax_unit_id,
1302 ln_legal_emp_id,
1303 ln_org_id,
1304 ln_assignment_set_id,
1305 lv_sort_1,
1306 ln_dimension_id,
1307 ld_start_date,
1308 ld_effective_date;
1309 CLOSE c_inputs;
1310
1311 hr_utility.set_location ( l_proc_name ,25);
1312 OPEN c_get_payroll_name (ln_payroll_id);
1313 FETCH c_get_payroll_name INTO lv_payroll_name;
1314 CLOSE c_get_payroll_name;
1315
1316 hr_utility.set_location ( l_proc_name ,30);
1317 OPEN c_get_consolidation_set_name (ln_consolidation_set_id);
1318 FETCH c_get_consolidation_set_name INTO lv_consolidation_set_name;
1319 CLOSE c_get_consolidation_set_name;
1320
1321 hr_utility.set_location ( l_proc_name ,35);
1322 OPEN c_get_gre_name (ln_tax_unit_id);
1323 FETCH c_get_gre_name INTO lv_gre_name;
1324 CLOSE c_get_gre_name;
1325
1326 hr_utility.set_location ( l_proc_name ,40);
1327 OPEN c_get_gre_name (ln_legal_emp_id);
1328 FETCH c_get_gre_name INTO lv_le_name;
1329 CLOSE c_get_gre_name;
1330
1331 hr_utility.set_location ( l_proc_name ,45);
1332 OPEN c_get_gre_name (ln_org_id);
1333 FETCH c_get_gre_name INTO lv_org_name;
1334 CLOSE c_get_gre_name;
1335
1336 hr_utility.set_location ( l_proc_name ,50);
1337 OPEN c_get_assig_set_id (ln_assignment_set_id);
1338 FETCH c_get_assig_set_id INTO lv_assignment_set_name;
1339 CLOSE c_get_assig_set_id;
1340
1341 hr_utility.set_location ( l_proc_name ,55);
1342 OPEN c_get_dimension_type (ln_dimension_id);
1343 FETCH c_get_dimension_type INTO lv_dimension_name;
1344 CLOSE c_get_dimension_type;
1345
1346 hr_utility.set_location ( l_proc_name ,57);
1347 OPEN c_get_sort_option (lv_sort_1);
1348 FETCH c_get_sort_option INTO lv_sort_option;
1349 CLOSE c_get_sort_option;
1350
1351
1352 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
1353
1354 hr_utility.set_location ( l_proc_name ,60);
1355 write_to_magtape_lob (lv_buf);
1356
1357 hr_utility.set_location ( l_proc_name ,65);
1358 populate_xml_table('START_DATE', ld_start_date,'TEXT');
1359 populate_xml_table('END_DATE',ld_effective_date,'TEXT');
1360 populate_xml_table('PAYROLL', lv_payroll_name,'TEXT');
1361 populate_xml_table('CONSOLIDATION_SET', lv_consolidation_set_name,'TEXT');
1362 populate_xml_table('P_GRE', lv_gre_name,'TEXT');
1363 populate_xml_table('LEGAL_EMPLOYER', lv_le_name,'TEXT');
1364 populate_xml_table('ORGANIZATION_NAME', lv_org_name,'TEXT');
1365 populate_xml_table('ASSIGNMENT_SET', lv_assignment_set_name,'TEXT');
1366 populate_xml_table('DIMENSION_ID', ln_dimension_id,'TEXT');
1367 populate_xml_table('DIMENSION', lv_dimension_name,'TEXT');
1368 populate_xml_table('SORT_OPTION_1', lv_sort_option,'TEXT');
1369 hr_utility.set_location ( l_proc_name ,70);
1370
1371 load_xml_internal('CS','REPORT_PARMETERS',NULL);
1372 hr_utility.set_location ( l_proc_name ,75);
1373
1374 FOR i IN payroll_reg_xml_tbl.FIRST..payroll_reg_xml_tbl.LAST
1375 LOOP
1376 hr_utility.set_location ( l_proc_name ,80);
1377 load_xml_internal('D',payroll_reg_xml_tbl(i).name,payroll_reg_xml_tbl(i).value);
1378 END LOOP;
1379
1380 hr_utility.set_location ( l_proc_name ,85);
1381 load_xml_internal('CE','REPORT_PARMETERS',NULL);
1382
1383 hr_utility.set_location ( l_proc_name ,90);
1384 payroll_reg_xml_tbl.DELETE;
1385
1386 FOR i IN csr_get_le_tot(ln_payroll_action_id)
1387 LOOP
1388 hr_utility.set_location ( l_proc_name ,95);
1389 load_xml_internal('D','LE_'||i.balance_name,i.total);
1390 END LOOP;
1391
1392 hr_utility.set_location ( 'Leaving'||l_proc_name ,100);
1393 END generate_xml_header;
1394
1395 ---------------------------------- generate_xml_footer ----------------------------------
1396 PROCEDURE generate_xml_footer AS
1397
1398 lv_buf VARCHAR2(8000);
1399 ln_payroll_action_id NUMBER;
1400 l_proc_name VARCHAR2(100) := g_package_name||'.GENERATE_XML_FOOTER';
1401 l_remove_act VARCHAR2(10);
1402 BEGIN
1403 hr_utility.set_location ( 'Entering'||l_proc_name ,10);
1404 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.generate_xml_footer ');
1405
1406 lv_buf := '</' ||
1407 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'),
1408 2);
1409
1410 write_to_magtape_lob (lv_buf);
1411
1412 hr_utility.set_location ( l_proc_name ,20);
1413 ln_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1414
1415 SELECT get_parameter('REMOVE_ACT', pa1.legislative_parameters)
1416 INTO l_remove_act
1417 FROM pay_payroll_actions pa1
1418 WHERE pa1.payroll_action_id = ln_payroll_action_id;
1419
1420 hr_utility.set_location ( l_proc_name ,30);
1421 IF (l_remove_act IS NULL OR l_remove_act = 'Y') THEN
1422 hr_utility.set_location ( l_proc_name ,40);
1423 DELETE FROM pay_us_rpt_totals
1424 WHERE session_id = ln_payroll_action_id
1425 AND attribute30 = 'MX_PYR';
1426 END IF;
1427
1428 hr_utility.set_location ( 'Leaving'||l_proc_name ,50);
1429
1430 END generate_xml_footer;
1431
1432 ---------------------------------- generate_gre_xml_header -------------------------------
1433 PROCEDURE generate_gre_xml_header AS
1434
1435
1436 CURSOR c_get_gre_name (cp_tax_unit_id number) IS
1437 SELECT hou.name
1438 FROM hr_organization_units hou
1439 WHERE hou.organization_id = cp_tax_unit_id;
1440
1441 CURSOR csr_get_gre_tot(c_payroll_action_id NUMBER
1442 ,c_tax_unit_id NUMBER) IS
1443 SELECT attribute5 balance_name
1444 ,sum(value1) total
1445 FROM pay_us_rpt_totals
1446 WHERE tax_unit_id = c_tax_unit_id
1447 AND attribute1 = fnd_number.number_to_canonical(c_payroll_action_id)
1448 AND attribute30 = 'MX_PYR'
1449 GROUP BY attribute5;
1450
1451 lv_buf VARCHAR2(8000);
1452 ln_payroll_action_id NUMBER;
1453 ln_tax_unit_id NUMBER;
1454 lv_gre_name VARCHAR2(30);
1455 l_proc_name VARCHAR2(100) := g_package_name||'.GENERATE_GRE_XML_HEADER';
1456
1457 BEGIN
1458 hr_utility.set_location ( 'Entering'||l_proc_name ,10);
1459 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.generate_gre_xml_header ');
1460 payroll_reg_xml_tbl.delete;
1461
1462 hr_utility.set_location (l_proc_name ,15);
1463 ln_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1464 ln_tax_unit_id := pay_magtape_generic.get_parameter_value('TRANSFER_GRE_ID');
1465
1466
1467 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_GRE_TAG');
1468 write_to_magtape_lob (lv_buf);
1469
1470 hr_utility.set_location (l_proc_name ,20);
1471 OPEN c_get_gre_name (ln_tax_unit_id);
1472 FETCH c_get_gre_name INTO lv_gre_name;
1473 CLOSE c_get_gre_name;
1474
1475 hr_utility.set_location (l_proc_name ,25);
1476 populate_xml_table('GRE_NAME', lv_gre_name,'TEXT');
1477
1478 hr_utility.set_location (l_proc_name ,30);
1479 FOR i IN payroll_reg_xml_tbl.FIRST..payroll_reg_xml_tbl.LAST LOOP
1480 hr_utility.set_location (l_proc_name ,35);
1481 load_xml_internal('D',payroll_reg_xml_tbl(i).name,payroll_reg_xml_tbl(i).value);
1482 END LOOP;
1483
1484 hr_utility.set_location (l_proc_name ,40);
1485 FOR i IN csr_get_gre_tot(ln_payroll_action_id,ln_tax_unit_id)
1486 LOOP
1487 hr_utility.set_location (l_proc_name ,45);
1488 load_xml_internal('D','GRE_'||i.balance_name,i.total);
1489 END LOOP;
1490
1491 payroll_reg_xml_tbl.DELETE;
1492 hr_utility.set_location ('Leaving'||l_proc_name ,50);
1493 END generate_gre_xml_header;
1494
1495 ---------------------------------- generate_xml_footer ----------------------------------
1496 PROCEDURE generate_gre_xml_footer AS
1497 lv_buf VARCHAR2(8000);
1498 l_proc_name VARCHAR2(100) := g_package_name||'.GENERATE_GRE_XML_FOOTER';
1499 BEGIN
1500 hr_utility.trace ('Entering generate_gre_xml_footer');
1501 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.generate_gre_xml_footer ');
1502 lv_buf := '</' ||
1503 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_GRE_TAG'),
1504 2);
1505
1506 write_to_magtape_lob (lv_buf);
1507
1508 hr_utility.trace ('Leaving generate_gre_xml_footer');
1509
1510 END generate_gre_xml_footer;
1511
1512 ---------------------------------- sort_action ----------------------------------
1513 PROCEDURE sort_action(
1514 payactid in varchar2, /* payroll action id */
1515 sqlstr in out nocopy varchar2, /* string holding the sql statement */
1516 len out nocopy number /* length of the sql string */
1517 ) is
1518
1519 l_sort_1 varchar2(30);
1520 l_sort_2 varchar2(30);
1521 l_sort_3 varchar2(30);
1522 l_proc_name VARCHAR2(100) := g_package_name||'.SORT_ACTION';
1523
1524 BEGIN
1525 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.sort_action ');
1526 hr_utility.set_location ('Entering'||l_proc_name ,10);
1527 SELECT pay_payrg_pkg.get_parameter('P_S1', ppa.legislative_parameters)
1528 INTO l_sort_1
1529 FROM pay_payroll_actions ppa
1530 WHERE ppa.payroll_action_id = payactid;
1531 --Modified the sort query for sorting by employee name and payroll name
1532 hr_utility.set_location ('Entering'||l_proc_name ,20);
1533 sqlstr :=
1534 'SELECT paa.rowid
1535 FROM pay_assignment_actions paa,
1536 pay_payroll_actions ppa
1537 WHERE ppa.payroll_action_id = :payactid
1538 and paa.payroll_action_id = ppa.payroll_action_id
1539 order by(pay_mx_payreg_pkg.sort_option (
1540 ''GRE'',
1541 paa.assignment_id,
1542 ppa.effective_date,
1543 paa.tax_unit_id)),
1544 (decode('''||l_sort_1||''',
1545 null, null,
1546 pay_mx_payreg_pkg.sort_option (
1547 '''||l_sort_1||''',
1548 paa.assignment_id,
1549 ppa.effective_date,
1550 paa.tax_unit_id))),
1551 (SELECT ppa2.effective_date
1552 FROM pay_payroll_actions ppa2,
1553 pay_assignment_actions paa2
1554 WHERE paa2.assignment_action_id = to_number(substr(paa.serial_number,2))
1555 and paa2.payroll_action_id = ppa2.payroll_action_id
1556 and ppa2.action_type in (''R'', ''Q'', ''V'', ''B'')
1557 )
1558 for update of paa.assignment_id';
1559
1560 len := LENGTH(sqlstr); -- return the length of the string.
1561 hr_utility.set_location ('Leaving'||l_proc_name ,20);
1562 END sort_action;
1563
1564 ----------------------------- get_parameter -------------------------------
1565 FUNCTION get_parameter(name in varchar2
1566 ,parameter_list varchar2)
1567 RETURN VARCHAR2
1568 IS
1569 start_ptr NUMBER;
1570 end_ptr NUMBER;
1571 token_val pay_payroll_actions.legislative_parameters%type;
1572 par_value pay_payroll_actions.legislative_parameters%type;
1573 l_proc_name VARCHAR2(100) := g_package_name||'.GET_PARAMETER';
1574 BEGIN
1575
1576 token_val := name || '=';
1577 start_ptr := INSTR(parameter_list, token_val) + LENGTH(token_val);
1578 end_ptr := INSTR(parameter_list, ' ',start_ptr);
1579
1580 /* if there is no spaces use then length of the string */
1581 IF end_ptr = 0 THEN
1582 end_ptr := LENGTH(parameter_list) + 1;
1583 END IF;
1584
1585 /* Did we find the token */
1586 IF INSTR(parameter_list, token_val) = 0 THEN
1587 par_value := NULL;
1588 ELSE
1589 par_value := SUBSTR(parameter_list, start_ptr, end_ptr - start_ptr);
1590 END IF;
1591
1592 RETURN par_value;
1593
1594 END get_parameter;
1595
1596 FUNCTION sort_option (c_option_name in varchar2
1597 ,c_assignment_id in number
1598 ,c_effective_date in date
1599 ,c_tax_unit_id in number)
1600 RETURN VARCHAR2
1601 IS
1602 return_val varchar2(240);
1603 l_proc_name VARCHAR2(100) := g_package_name||'.SORT_OPTION';
1604 BEGIN
1605 FND_FILE.PUT_LINE(FND_FILE.LOG,'pay_mx_payreg_pkg.sort_option ');
1606 hr_utility.set_location ('Entering '||l_proc_name ,10);
1607 --Modified the sort query for sorting by employee name(EMP_NAME) and
1608 --payroll name(PAY_NAME)
1609 /* IF c_option_name = 'GRE' THEN
1610 SELECT hou1.name
1611 INTO return_val
1612 FROM hr_all_organization_units hou1
1613 WHERE hou1.organization_id = c_tax_unit_id
1614 AND rownum = 1;
1615 ELSE
1616 SELECT DECODE(c_option_name,
1617 'Organization',hou.name,
1618 'Location',loc.location_code,
1619 NULL)
1620 INTO return_val
1621 FROM hr_all_organization_units hou,
1622 hr_locations_all loc,
1623 per_assignments_f paf
1624 WHERE paf.assignment_id = c_assignment_id
1625 AND c_effective_date BETWEEN
1626 paf.effective_start_date AND paf.effective_end_date
1627 AND hou.organization_id = paf.organization_id
1628 AND loc.location_id = paf.location_id
1629 AND rownum = 1;
1630 END IF; */
1631
1632 IF c_option_name = 'EMP_NAME' Then
1633 hr_utility.set_location ('Entering '||l_proc_name ,15);
1634 SELECT ppf.full_name
1635 INTO return_val
1636 FROM per_all_assignments_f paf
1637 ,per_all_people_f ppf
1638 WHERE paf.person_id = ppf.person_id
1639 AND c_effective_date BETWEEN
1640 paf.effective_start_date AND paf.effective_end_date
1641 AND c_effective_date BETWEEN
1642 ppf.effective_start_date AND ppf.effective_end_date
1643 AND paf.assignment_id = c_assignment_id
1644 AND rownum = 1;
1645 ELSIF c_option_name = 'PAY_NAME' Then
1646 hr_utility.set_location ('Entering '||l_proc_name ,20);
1647 SELECT ppf.payroll_name||' '||papf.full_name
1648 INTO return_val
1649 FROM per_all_assignments_f paf
1650 ,pay_payrolls_f ppf
1651 ,per_all_people_f papf
1652 WHERE paf.payroll_id = ppf.payroll_id
1653 AND c_effective_date BETWEEN
1654 paf.effective_start_date AND paf.effective_end_date
1655 AND c_effective_date BETWEEN
1656 ppf.effective_start_date AND ppf.effective_end_date
1657 AND paf.assignment_id = c_assignment_id
1658 AND paf.person_id = papf.person_id
1659 AND c_effective_date BETWEEN
1660 papf.effective_start_date AND papf.effective_end_date;
1661 ELSIF c_option_name = 'GRE' Then
1662 hr_utility.set_location ('Entering '||l_proc_name ,25);
1663 SELECT hou1.name
1664 INTO return_val
1665 FROM hr_all_organization_units hou1
1666 WHERE hou1.organization_id = c_tax_unit_id
1667 AND rownum = 1;
1668 END IF;
1669
1670 hr_utility.set_location ('Leaving '||l_proc_name ,30);
1671 RETURN return_val;
1672
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 hr_utility.set_location ('Exception:'||l_proc_name ,25);
1676 RETURN '1';
1677
1678 END sort_option;
1679
1680 END pay_mx_payreg_pkg;