[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_RULES
Source
1 package body PAY_SE_RULES as
2 /* $Header: pyserule.pkb 120.7 2008/07/18 12:02:30 rmurahar noship $ */
3
4 g_custom_context pay_action_information.action_information_category%type;
5 g_action_ctx_id NUMBER;
6
7 -----------------------------------------------------------------------------
8 -- GET_MAIN_TAX_UNIT_ID fetches the Legal Employer Id of the Local Unit
9 -- of the Assignment Id
10 -----------------------------------------------------------------------------
11
12 PROCEDURE GET_MAIN_TAX_UNIT_ID
13 (p_assignment_id IN NUMBER
14 ,p_effective_date IN DATE
15 ,p_tax_unit_id OUT NOCOPY NUMBER ) IS
16
17 l_local_unit_id hr_soft_coding_keyflex.SEGMENT2%TYPE;
18 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
19
20 CURSOR c_local_unit_id IS
21 SELECT SCL.segment2 , business_group_id
22 FROM
23 per_all_assignments_f PAA,
24 hr_soft_coding_keyflex SCL
25 WHERE ASSIGNMENT_ID = p_assignment_id
26 AND PAA.soft_coding_keyflex_id = SCL.soft_coding_keyflex_id
27 AND p_effective_date BETWEEN PAA.effective_start_date AND PAA.effective_end_date ;
28
29 CURSOR c_tax_unit_id (p_business_group_id NUMBER , p_organization_id NUMBER) IS
30 SELECT hoi3.organization_id
31 FROM hr_organization_units o1
32 , hr_organization_information hoi1
33 , hr_organization_information hoi2
34 , hr_organization_information hoi3
35 WHERE o1.business_group_id =p_business_group_id
36 AND hoi1.organization_id = o1.organization_id
37 AND hoi1.organization_id = p_organization_id
38 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
39 AND hoi1.org_information_context = 'CLASS'
40 AND o1.organization_id = hoi2.org_information1
41 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
42 AND hoi2.organization_id = hoi3.organization_id
43 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
44 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
45 BEGIN
46 OPEN c_local_unit_id ;
47 FETCH c_local_unit_id
48 INTO l_local_unit_id , l_business_group_id ;
49 CLOSE c_local_unit_id ;
50 OPEN c_tax_unit_id (l_business_group_id , l_local_unit_id);
51 FETCH c_tax_unit_id
52 INTO p_tax_unit_id ;
53 CLOSE c_tax_unit_id;
54 EXCEPTION
55 WHEN others
56 THEN
57 p_tax_unit_id := NULL;
58 END GET_MAIN_TAX_UNIT_ID;
59 --
60
61
62 -----------------------------------------------------------------------------
63 -- Procedure : get_third_party_org_context
64 -- It fetches the third party context of the Assignment Id.
65 -----------------------------------------------------------------------------
66
67 PROCEDURE get_third_party_org_context
68 (p_asg_act_id IN NUMBER
69 ,p_ee_id IN NUMBER
70 ,p_third_party_id IN OUT NOCOPY NUMBER )
71 IS
72 l_third_party_id number;
73 l_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
74 l_local_unit_id hr_soft_coding_keyflex.SEGMENT2%TYPE ;
75 l_business_group_id per_all_assignments_f.business_group_id%TYPE ;
76 l_tax_unit_id hr_organization_units.organization_id%TYPE ;
77 l_pension_group_id hr_organization_information.org_information_id%TYPE;
78 l_pension_group hr_organization_information.org_information2%TYPE;
79 l_pension_type hr_organization_information.org_information1%TYPE;
80 l_pension_provider hr_organization_units.organization_id%TYPE ;
81
82 l_effective_date DATE;
83
84
85 CURSOR get_element_name(p_ee_id NUMBER) IS
86 SELECT pet.element_name
87 FROM pay_element_types pet,
88 pay_element_entries pee
89 WHERE pee.element_entry_id = p_ee_id
90 AND pee.element_type_id = pet.element_type_id;
91
92
93 CURSOR get_details(p_asg_act_id NUMBER ) IS
94 SELECT eev1.screen_entry_value screen_entry_value
95 FROM per_all_assignments_F asg1
96 ,per_all_assignments_F asg2
97 ,per_all_people_F per
98 ,pay_element_links_f el
99 ,pay_element_types_f et
100 ,pay_input_values_f iv1
101 ,pay_element_entries_F ee
102 ,pay_element_entry_values_F eev1
103 ,pay_assignment_actions pac
104 ,pay_payroll_actions ppa
105 WHERE per.person_id = asg1.person_id
106 AND ppa.BUSINESS_GROUP_ID = per.BUSINESS_GROUP_ID
107 and ppa.effective_date BETWEEN per.effective_start_date and per.effective_end_date
108 AND asg2.person_id = per.person_id
109 and ppa.BUSINESS_GROUP_ID = asg1.BUSINESS_GROUP_ID
110 and ppa.BUSINESS_GROUP_ID = asg2.BUSINESS_GROUP_ID
111 and ppa.effective_date BETWEEN asg1.effective_start_date and asg1.effective_end_date
112 and ppa.effective_date BETWEEN asg2.effective_start_date and asg2.effective_end_date
113 AND asg2.primary_flag = 'Y'
114 AND pac.assignment_action_id = p_asg_act_id
115 AND pac.payroll_action_id = ppa.payroll_action_id
116 AND asg1.assignment_id = pac.assignment_id
117 AND et.element_name = 'Court Order Information'
118 and ppa.effective_date BETWEEN et.effective_start_date and et.effective_end_date
119 AND et.legislation_code = 'SE'
120 AND iv1.element_type_id = et.element_type_id
121 AND iv1.name = 'Enforcement Office'
122 and ppa.effective_date BETWEEN iv1.effective_start_date and iv1.effective_end_date
123 AND el.business_group_id = per.business_group_id
124 AND el.element_type_id = et.element_type_id
125 and ppa.effective_date BETWEEN el.effective_start_date and el.effective_end_date
126 AND ee.assignment_id = asg2.assignment_id
127 AND ee.element_link_id = el.element_link_id
128 and ppa.effective_date BETWEEN ee.effective_start_date and ee.effective_end_date
129 AND eev1.element_entry_id = ee.element_entry_id
130 AND eev1.input_value_id = iv1.input_value_id
131 and ppa.effective_date BETWEEN eev1.effective_start_date and eev1.effective_end_date;
132
133
134
135
136 CURSOR c_effective_date(p_asg_act_id NUMBER ) IS
137 SELECT effective_date
138 FROM pay_payroll_actions ppa, pay_assignment_actions paa
139 WHERE paa.assignment_action_id = p_asg_act_id
140 AND paa.payroll_action_id = ppa.payroll_action_id ;
141
142
143 BEGIN
144 OPEN c_effective_date(p_asg_act_id);
145 FETCH c_effective_date INTO l_effective_date;
146 CLOSE c_effective_date;
147
148 OPEN get_element_name(p_ee_id);
149 FETCH get_element_name INTO l_element_name;
150 CLOSE get_element_name;
151
152 IF l_element_name = 'Court Order' THEN
153 OPEN get_details(p_asg_act_id );
154 FETCH get_details INTO p_third_party_id;
155 CLOSE get_details;
156
157 END IF;
158 IF p_third_party_id IS NULL THEN
159 p_third_party_id := -999;
160 END IF;
161
162
163 EXCEPTION
164 WHEN others THEN
165 NULL;
166
167 END get_third_party_org_context;
168
169 -------------------------------------------------------------------------------
170 -- flex_seg_enabled
171 -------------------------------------------------------------------------------
172 FUNCTION flex_seg_enabled(p_context_code VARCHAR2,
173 p_application_column_name VARCHAR2) RETURN BOOLEAN AS
174 --
175 CURSOR csr_seg_enabled IS
176 SELECT 'Y'
177 FROM fnd_descr_flex_col_usage_vl
178 WHERE descriptive_flexfield_name LIKE 'Action Information DF'
179 AND descriptive_flex_context_code = p_context_code
180 AND application_column_name LIKE p_application_column_name
181 AND enabled_flag = 'Y';
182 --
183 l_proc_name varchar2(100);
184 l_exists varchar2(1);
185 --
186 BEGIN
187 --
188 OPEN csr_seg_enabled;
189 FETCH csr_seg_enabled INTO l_exists;
190 CLOSE csr_seg_enabled;
191 --
192 IF l_exists = 'Y' THEN
193 RETURN (TRUE);
194 ELSE
195 RETURN (FALSE);
196 END IF;
197 --
198 END flex_seg_enabled;
199 --
200
201 PROCEDURE LOAD_XML (
202 P_NODE_TYPE varchar2,
203 P_CONTEXT_CODE varchar2,
204 P_NODE varchar2,
205 P_DATA varchar2
206 ) AS
207
208 CURSOR csr_get_tag_name IS
209 SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name
210 FROM fnd_descr_flex_col_usage_vl
211 WHERE descriptive_flexfield_name = 'Action Information DF'
212 AND descriptive_flex_context_code = p_context_code
213 AND application_column_name = UPPER (p_node);
214
215 CURSOR csr_get_chk_no IS
216 SELECT paa_chk.serial_number
217 FROM pay_assignment_actions paa_xfr,
218 pay_action_interlocks pai_xfr,
219 pay_action_interlocks pai_chk,
220 pay_assignment_actions paa_chk,
221 pay_payroll_actions ppa_chk
222 WHERE paa_xfr.assignment_action_id = pai_xfr.locking_action_id
223 AND pai_xfr.locked_action_id = pai_chk.locked_action_id
224 AND pai_chk.locking_action_id = paa_chk.assignment_action_id
225 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
226 AND ppa_chk.action_type = 'H'
227 AND paa_xfr.assignment_action_id = g_action_ctx_id;
228
229 l_tag_name varchar2(500);
230 l_chk_no pay_assignment_actions.serial_number%type;
231 l_data pay_action_information.action_information1%type;
232
233 PROCEDURE LOAD_XML_INTERNAL (
234 P_NODE_TYPE varchar2,
235 P_NODE varchar2,
236 P_DATA varchar2
237 ) AS
238
239 l_data pay_action_information.action_information1%type;
240
241 BEGIN
242
243 IF p_node_type = 'CS' THEN
244
245 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '<'||p_node||'>';
246
247 ELSIF p_node_type = 'CE' THEN
248
249 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '</'||p_node||'>';
250
251 ELSIF p_node_type in( 'D','DIRECT') THEN
252
253 /* Handle special charaters in data */
254 l_data := REPLACE (p_data, '&', '&');
255 l_data := REPLACE (l_data, '>', '>');
256 l_data := REPLACE (l_data, '<', '<');
257 l_data := REPLACE (l_data, '''', ''');
258 l_data := REPLACE (l_data, '"', '"');
259 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '<'||p_node||'>'||l_data||'</'||p_node||'>';
260 END IF;
261 END LOAD_XML_INTERNAL;
262
263
264 BEGIN
265
266 IF p_node_type = 'D' THEN
267
268 /* Fetch segment names */
269 OPEN csr_get_tag_name;
270 FETCH csr_get_tag_name INTO l_tag_name;
271 CLOSE csr_get_tag_name;
272
273 /* Fetch cheque number */
274 IF p_context_code = 'EMPLOYEE NET PAY DISTRIBUTION' AND
275 l_tag_name = 'CHECK_DEPOSIT_NUMBER' THEN
276 OPEN csr_get_chk_no;
277 FETCH csr_get_chk_no INTO l_chk_no;
278 CLOSE csr_get_chk_no;
279 END IF;
280 END IF;
281
282 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
283 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node, ' /', '__'));
284 IF p_node_type IN ('CS', 'CE') THEN
285 l_tag_name := nvl(g_custom_context, TRANSLATE(p_node, ' /', '__'));
286 END IF;
287 ELSE
288 l_tag_name := p_node;
289 END IF;
290
291 l_data := nvl(l_chk_no, p_data);
292 load_xml_internal (p_node_type, l_tag_name, l_data);
293 END LOAD_XML;
294
295
296 PROCEDURE add_custom_xml
297 (p_assignment_action_id number,
298 p_action_information_category varchar2,
299 p_document_type varchar2) as
300
301 CURSOR csr_payroll_info(p_action_context_id NUMBER
302 ,p_category1 VARCHAR2
303 ,p_category2 VARCHAR2
304 )IS
305
306 SELECT ppf.payroll_name payroll_name
307 ,ptp.period_name period_name
308 ,ptp.period_type period_type
309 ,ptp.start_date start_date
310 ,ptp.end_date end_date
311 ,pai.effective_date payment_date
312 FROM per_time_periods ptp
313 ,pay_payrolls_f ppf
314 ,pay_action_information pai
315 WHERE ppf.payroll_id = ptp.payroll_id
316 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
317 AND ptp.time_period_id = pai.action_information16
318 AND pai.action_context_type = 'AAP'
319 AND pai.action_information_category = p_category1
320 AND pai.action_context_id=p_action_context_id;
321
322 /*AND (pai.action_context_id = p_action_context_id
323 OR pai.action_context_id = ( SELECT paa.source_action_id
324 FROM pay_assignment_actions paa
325 WHERE paa.assignment_action_id = p_action_context_id
326 AND paa.assignment_id = pai.Assignment_ID
327 )); */
328
329
330 ----- cursor to get the element information for earnings and deductions elements ----------------
331 CURSOR csr_element_info(p_action_context_id NUMBER
332 ,p_pa_category VARCHAR2
333 ,p_aap_category VARCHAR2) IS
334 SELECT pai.action_information2 element_type_id
335 ,pai.action_information3 input_value_id
336 ,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
337 -- ,decode(pai1.action_information8,NULL,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1),
338 -- SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) ||'('||pai1.action_information8||')') Name
339 ,pai.action_information5 type
340 ,pai.action_information6 uom
341 ,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
342 ,pai1.action_information8 record_count
343 ,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price
344 ,pai1.action_information4 value
345 FROM pay_action_information pai
346 ,pay_action_information pai1
347 ,pay_assignment_actions paa
348 WHERE pai.action_context_type = 'PA'
349 AND pai.action_information_category = p_pa_category
350 AND pai1.action_context_type = 'AAP'
351 AND pai.action_information5 <> 'F'
352 AND pai1.action_information3 <> 'F'
353 -- Commented for performance fix
354 /* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
355 FROM pay_assignment_actions paa
356 WHERE paa.source_action_id = p_action_context_id
357 AND paa.assignment_id = pai1.assignment_id
358 )
359 OR pai1.action_context_id = p_action_context_id) */
360 and pai1.action_information_category = p_aap_category
361 and pai.action_information2 = pai1.action_information1
362 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
363 and pai.action_context_id = paa.payroll_action_id
364 and pai1.action_context_id = paa.assignment_action_id
365 and paa.assignment_action_id = p_action_context_id
366 ORDER BY pai.action_information2;
367 --group by pai.action_information2
368 -- ,pai.action_information3
369 -- ,pai.action_information4
370 -- ,pai.action_information5
371 -- ,pai.action_information6
372 -- ,pai1.action_information8
373 --ORDER BY pai.action_information5,pai1.action_information8 DESC;
374 /* CURSOR csr_element_info(p_action_context_id NUMBER
375 ,p_pa_category VARCHAR2
376 ,p_aap_category VARCHAR2) IS
377 SELECT pai.action_information2 element_type_id
378 ,pai.action_information3 input_value_id
379 ,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
380 -- ,decode(pai1.action_information8,NULL,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1),
381 -- SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) ||'('||pai1.action_information8||')') Name
382 ,pai.action_information5 type
383 ,pai.action_information6 uom
384 ,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
385 --,pai1.action_information8 record_count
386 --,sum(pai1.action_information4) value
387 ,pai1.action_information4 value
388 FROM pay_action_information pai
389 ,pay_action_information pai1
390 ,pay_assignment_actions paa
391 WHERE pai.action_context_type = 'PA'
392 AND pai.action_information_category = p_pa_category
393 AND pai1.action_context_type = 'AAP'
394 AND pai.action_information5 <> 'F'
395 AND pai1.action_information3 <> 'F'
396 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
397 FROM pay_assignment_actions paa
398 WHERE paa.source_action_id = p_action_context_id
399 AND paa.assignment_id = pai1.assignment_id
400 )
401 OR pai1.action_context_id = p_action_context_id)
402 and pai1.action_information_category = p_aap_category
403 and pai.action_information2 = pai1.action_information1
404 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
405 and pai.action_context_id = paa.payroll_action_id
406 and pai1.action_context_id = paa.assignment_action_id
407 --group by pai.action_information2
408 -- ,pai.action_information3
409 -- ,pai.action_information4
410 -- ,pai.action_information5
411 -- ,pai.action_information6
412 -- ,pai1.action_information8
413 ORDER BY pai.action_information5,pai1.action_information8 DESC;*/
414 /*SELECT pai.action_information2 element_type_id
415 ,pai.action_information3 input_value_id
416 ,decode(pai1.action_information8,NULL,pai.action_information4,
417 pai.action_information4||'('||pai1.action_information8||')') Name
418 ,pai.action_information5 type
419 ,pai.action_information6 uom
420 --,pai1.action_information8 record_count
421 ,sum(pai1.action_information4) value
422 FROM pay_action_information pai
423 ,pay_action_information pai1
424 ,pay_assignment_actions paa
425 WHERE pai.action_context_type = 'PA'
426 AND pai.action_information_category = p_pa_category
427 AND pai1.action_context_type = 'AAP'
428 AND pai.action_information5 <> 'F'
429 AND pai1.action_information3 <> 'F'
430 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
431 FROM pay_assignment_actions paa
432 WHERE paa.source_action_id = p_action_context_id
433 AND paa.assignment_id = pai1.assignment_id
434 )
435 OR pai1.action_context_id = p_action_context_id)
436 and pai1.action_information_category = p_aap_category
437 and pai.action_information2 = pai1.action_information1
438 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
439 and pai.action_context_id = paa.payroll_action_id
440 and pai1.action_context_id = paa.assignment_action_id
441 group by pai.action_information2
442 ,pai.action_information3
443 ,pai.action_information4
444 ,pai.action_information5
445 ,pai.action_information6
446 ,pai1.action_information8
447 ORDER BY pai.action_information5,pai1.action_information8 DESC;*/
448
449
450 ----- cursor to get the element information for additional elements ----------------
451
452 CURSOR csr_add_element_info(p_action_context_id NUMBER
453 ,p_pa_category VARCHAR2
454 ,p_aap_category VARCHAR2) IS
455 SELECT pai.action_information2 element_type_id
456 ,pai.action_information3 input_value_id
457 ,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
458 --,decode(pai1.action_information8,NULL,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1),
459 --SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) ||'('||pai1.action_information8||')') Name
460 ,pai.action_information5 type
461 ,pai.action_information6 uom
462 ,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
463 ,pai1.action_information8 record_count
464 ,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price
465 ,pai1.action_information4 value
466 FROM pay_action_information pai
467 ,pay_action_information pai1
468 ,pay_assignment_actions paa
469 WHERE pai.action_context_type = 'PA'
470 AND pai.action_information_category = p_pa_category
471 AND pai1.action_context_type = 'AAP'
472 AND pai.action_information5 = 'F'
473 AND pai1.action_information3 = 'F'
474 -- Commented for performance fix
475 /* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
476 FROM pay_assignment_actions paa
477 WHERE paa.source_action_id = p_action_context_id
478 AND paa.assignment_id = pai1.assignment_id
479 )
480 OR pai1.action_context_id = p_action_context_id) */
481 and pai1.action_information_category = p_aap_category
482 and pai.action_information2 = pai1.action_information1
483 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
484 and pai.action_context_id = paa.payroll_action_id
485 and pai1.action_context_id = paa.assignment_action_id
486 and paa.assignment_action_id = p_action_context_id
487 ORDER BY pai.action_information2;
488 --group by pai.action_information2
489 --,pai.action_information3
490 --,pai.action_information4
491 --,pai.action_information5
492 --,pai.action_information6
493 --,pai1.action_information4
494 --,pai1.action_information8
495 --ORDER BY pai.action_information5,pai1.action_information8 DESC;
496
497
498 l_xml CLOB;
499 cntr_flex_col NUMBER;
500 l_flex_col_num NUMBER;
501 sqlstr DBMS_SQL.VARCHAR2S;
502 csr NUMBER;
503 ret NUMBER;
504 l_cntr_sql NUMBER;
505 l_total_pay NUMBER;
506 l_total_earnings NUMBER;
507 l_total_deductions NUMBER;
508
509
510 PROCEDURE build_sql(p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
511 p_cntr IN OUT NOCOPY NUMBER,
512 p_string VARCHAR2) AS
513 --
514 l_proc_name varchar2(100);
515 --
516 BEGIN
517 p_sqlstr_tab(p_cntr) := p_string;
518 p_cntr := p_cntr + 1;
519 END;
520
521 BEGIN
522 --hr_utility.trace_on(null,'se_payslip');
523 --hr_utility.trace('Joined ==> ' );
524 --dbms_output.put_line(' inside pyserule');
525 l_flex_col_num := 30;
526
527 IF p_action_information_category IS NULL AND p_document_type ='PAYSLIP' THEN
528
529 l_total_earnings:=0 ;
530 l_total_deductions :=0;
531 g_action_ctx_id := p_assignment_action_id ;
532
533 FOR payroll_info_rec IN csr_payroll_info (p_assignment_action_id,'EMPLOYEE DETAILS','ADDL EMPLOYEE DETAILS')
534 LOOP
535
536 load_xml('CS', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
537 load_xml('D', NULL, 'PAYROLL_NAME', payroll_info_rec.payroll_name );
538 load_xml('D', NULL, 'PERIOD_NAME', payroll_info_rec.period_name);
539 load_xml('D', NULL, 'PERIOD_TYPE', payroll_info_rec.period_type);
540 load_xml('D', NULL, 'START_DATE', payroll_info_rec.start_date);
541 load_xml('D', NULL, 'END_DATE', payroll_info_rec.end_date);
542 load_xml('D', NULL, 'PAYMENT_DATE', payroll_info_rec.payment_date);
543 load_xml('CE', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
544
545 --load_xml('CS', NULL, 'SALARY CERTIFICATE', NULL);
546 load_xml('D', NULL, 'PERIOD_START_END', payroll_info_rec.start_date||' - '||payroll_info_rec.end_date);
547 load_xml('D', NULL, 'YTD_START_END', trunc(payroll_info_rec.start_date,'Y') ||' - '||payroll_info_rec.end_date);
548 load_xml('D', NULL, 'PREV_YTD_START_END', add_months(trunc(payroll_info_rec.start_date,'Y'), -12) ||' - '||LAST_DAY(ADD_MONTHS(trunc(payroll_info_rec.end_date,'Y'),-1)));
549 --load_xml('CE', NULL, 'SALARY CERTIFICATE', NULL);
550
551 END LOOP;
552
553 FOR element_info_rec IN csr_element_info(p_assignment_action_id , 'EMEA ELEMENT DEFINITION' , 'EMEA ELEMENT INFO')
554 LOOP
555 --hr_utility.trace('Inside ==> ' || element_info_rec.Name);
556 --hr_utility.trace('Inside ==> ' || element_info_rec.CODE);
557 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
558 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', element_info_rec.element_type_id);
559 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', element_info_rec.input_value_id);
560 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', element_info_rec.Name);
561 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', element_info_rec.type);
562 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', element_info_rec.uom);
563 --load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION7', element_info_rec.CODE);
564 load_xml('DIRECT', NULL, 'ELEMENT_CODE', element_info_rec.CODE);
565 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION8', element_info_rec.record_count);
566 load_xml('DIRECT', NULL, 'UNIT_PRICE', element_info_rec.unit_price);
567 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(element_info_rec.value));
568 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
569
570 IF element_info_rec.type = 'E' THEN
571 l_total_earnings := fnd_number.canonical_to_number(l_total_earnings) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
572 ELSIF element_info_rec.type = 'D' THEN
573 l_total_deductions := fnd_number.canonical_to_number(l_total_deductions) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
574 END IF ;
575 l_total_pay := l_total_earnings - l_total_deductions ;
576
577 END LOOP;
578
579 FOR add_element_info_rec IN csr_add_element_info(p_assignment_action_id , 'EMEA ELEMENT DEFINITION' , 'EMEA ELEMENT INFO')
580 LOOP
581
582 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
583 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', add_element_info_rec.element_type_id);
584 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', add_element_info_rec.input_value_id);
585 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', add_element_info_rec.Name);
586 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', add_element_info_rec.type);
587 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', add_element_info_rec.uom);
588 load_xml('DIRECT', NULL, 'ELEMENT_CODE', add_element_info_rec.CODE);
589 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION8', add_element_info_rec.record_count);
590 load_xml('DIRECT', NULL, 'UNIT_PRICE', add_element_info_rec.unit_price);
591 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(add_element_info_rec.value));
592
593 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
594
595 END LOOP;
596
597
598
599 load_xml('CS', NULL, 'SUMMARY OF PAYMENTS', NULL);
600 load_xml('D', NULL, 'TOTAL_EARNINGS', l_total_earnings);
601 load_xml('D', NULL, 'TOTAL_DEDUCTIONS', l_total_deductions);
602 load_xml('D', NULL, 'TOTAL_PAY', l_total_pay);
603 load_xml('CE', NULL, 'SUMMARY OF PAYMENTS', NULL);
604
605 -- BALANCE DETAILS
606 l_cntr_sql := 1;
607 build_sql(sqlstr, l_cntr_sql, ' Begin FOR csr_balance_info_rec IN pay_SE_rules.csr_balance_info ('||p_assignment_action_id||',''EMEA BALANCE DEFINITION'',''EMEA BALANCES'') LOOP ');
608 build_sql(sqlstr, l_cntr_sql, ' pay_SE_rules.load_xml(''CS'', NULL, ''BALANCE DETAILS'', NULL); ');
609 FOR cntr in 1..30
610 LOOP
611 IF flex_seg_enabled ('EMEA BALANCE DEFINITION', 'ACTION_INFORMATION'||cntr) THEN
612 build_sql(sqlstr, l_cntr_sql, ' pay_SE_rules.load_xml(''D'', ''EMEA BALANCE DEFINITION'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.a'||cntr||'); ');
613 END IF;
614 IF flex_seg_enabled ('EMEA BALANCES', 'ACTION_INFORMATION'||cntr) THEN
615 build_sql(sqlstr, l_cntr_sql, ' pay_SE_rules.load_xml(''D'', ''EMEA BALANCES'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.aa'||cntr||'); ');
616 END IF;
617 END LOOP;
618 build_sql(sqlstr, l_cntr_sql, ' pay_SE_rules.load_xml(''CE'', NULL, ''BALANCE DETAILS'', NULL); ');
619 build_sql(sqlstr, l_cntr_sql, ' END LOOP; End; ');
620 --
621 csr := DBMS_SQL.OPEN_CURSOR;
622 DBMS_SQL.PARSE(csr
623 ,sqlstr
624 ,sqlstr.first()
625 ,sqlstr.last()
626 ,FALSE
627 ,DBMS_SQL.V7);
628 ret := DBMS_SQL.EXECUTE(csr);
629 DBMS_SQL.CLOSE_CURSOR(csr);
630
631 --insert into clobtable(a,ID) values (l_xml,'PPS');
632 --commit;
633
634
635 END IF;
636
637 END;
638
639
640 -----------------------------------------------------------------------------
641 -- Procedure : get_source_number_context
642 -- It fetches the Absence type context of the EE Id.
643 -----------------------------------------------------------------------------
644
645 procedure get_source_text_context
646 (p_asg_act_id number
647 ,p_ee_id number
648 ,p_source_text in out nocopy varchar2)
649 is
650
651 CURSOR get_details IS
652 SELECT eev1.screen_entry_value screen_entry_value
653 FROM per_all_assignments_F asg1
654 ,per_all_assignments_F asg2
655 ,per_all_people_F per
656 ,pay_element_links_f el
657 ,pay_element_types_f et
658 ,pay_input_values_f iv1
659 ,pay_element_entries_F ee
660 ,pay_element_entry_values_F eev1
661 ,pay_assignment_actions pac
662 ,pay_payroll_actions ppa
663 WHERE per.person_id = asg1.person_id
664 AND ppa.BUSINESS_GROUP_ID = per.BUSINESS_GROUP_ID
665 and ppa.effective_date BETWEEN per.effective_start_date and per.effective_end_date
666 AND asg2.person_id = per.person_id
667 and ppa.BUSINESS_GROUP_ID = asg1.BUSINESS_GROUP_ID
668 and ppa.BUSINESS_GROUP_ID = asg2.BUSINESS_GROUP_ID
669 and ppa.effective_date BETWEEN asg1.effective_start_date and asg1.effective_end_date
670 and ppa.effective_date BETWEEN asg2.effective_start_date and asg2.effective_end_date
671 AND pac.assignment_action_id = p_asg_act_id
672 AND pac.payroll_action_id = ppa.payroll_action_id
673 AND asg1.assignment_id = pac.assignment_id
674 -- AND et.element_name = 'Absence Details'
675 and ppa.effective_date BETWEEN et.effective_start_date and et.effective_end_date
676 AND et.legislation_code = 'SE'
677 AND iv1.element_type_id = et.element_type_id
678 AND iv1.name = 'Absence Category'
679 and ppa.effective_date BETWEEN iv1.effective_start_date and iv1.effective_end_date
680 AND el.business_group_id = per.business_group_id
681 AND el.element_type_id = et.element_type_id
682 and ppa.effective_date BETWEEN el.effective_start_date and el.effective_end_date
683 AND ee.assignment_id = asg2.assignment_id
684 AND ee.element_link_id = el.element_link_id
685 AND eev1.element_entry_id = ee.element_entry_id
686 and ee.element_entry_id =p_ee_id
687 AND eev1.input_value_id = iv1.input_value_id;
688
689 l_Absence_Type varchar2(240);
690 begin
691
692 OPEN get_details;
693 FETCH get_details INTO p_source_text;
694 CLOSE get_details;
695
696 if p_source_text IS NULL then
697 p_source_text := null;
698 end if;
699
700
701 end get_source_text_context;
702
703 -----------------------------------------------------------------------------
704 -- Procedure : get_main_local_unit_id
705 -- It fetches the value of the Local Unit Id.
706 -----------------------------------------------------------------------------
707 PROCEDURE get_main_local_unit_id
708 (p_assignment_id IN NUMBER,
709 p_effective_date IN DATE ,
710 p_local_unit_id IN OUT NOCOPY VARCHAR2) IS
711
712 CURSOR c_local_unit_id(p_assignment_id NUMBER , p_effective_date DATE ) is
713 SELECT target.segment2
714 FROM
715 hr_soft_coding_keyflex target,
716 per_all_assignments_f ASSIGN,
717 fnd_id_flex_structures fstruct,
718 pay_legislation_rules leg
719 WHERE fstruct.id_flex_num = leg.rule_mode
720 AND fstruct.id_flex_code = 'SCL'
721 AND fstruct.application_id = 800
722 AND leg.legislation_code = 'SE'
723 AND fstruct.enabled_flag = 'Y'
724 AND leg.rule_type = 'S'
725 AND target.id_flex_num = fstruct.id_flex_num
726 AND ASSIGN.assignment_id = p_assignment_id
727 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
728 AND p_effective_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
729 AND target.enabled_flag = 'Y';
730
731 BEGIN
732 OPEN c_local_unit_id(p_assignment_id , p_effective_date ) ;
733 FETCH c_local_unit_id into p_local_unit_id ;
734 CLOSE c_local_unit_id;
735 EXCEPTION
736 WHEN others THEN
737 p_local_unit_id := NULL;
738
739 END get_main_local_unit_id;
740
741 END PAY_SE_RULES;