[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_RULES
Source
1 package body PAY_DK_RULES as
2 /* $Header: pydkrule.pkb 120.11.12010000.3 2008/08/06 07:05:48 ubhat ship $ */
3
4 -----------------------------------------------------------------------------
5 -- Procedure : get_third_party_org_context
6 -- It fetches the third party context of the Assignment Id.
7 -----------------------------------------------------------------------------
8
9 /* Modified procedure get_third_party_org_context for pension changes */
10
11 PROCEDURE get_third_party_org_context
12 (p_asg_act_id IN NUMBER
13 ,p_ee_id IN NUMBER
14 ,p_third_party_id IN OUT NOCOPY NUMBER )
15 IS
16 l_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
17 /* Added effective date */
18 l_effective_date DATE;
19 /* Added for pension changes */
20 l_ele_type_id NUMBER;
21
22
23 /* Modified cursors to use effective date join */
24 /* Modified for pension changes to fetch element_type_id also */
25 CURSOR get_element_name(p_ee_id NUMBER , p_effective_date DATE ) IS
26 SELECT pet.element_name , pet.element_type_id
27 FROM pay_element_types pet
28 , pay_element_entries pee
29 WHERE pee.element_entry_id = p_ee_id
30 AND pee.element_type_id = pet.element_type_id
31 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
32 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
33
34 /* Modified cursors to use effective date join */
35 /* Modified for pension changes to fetch Pension provider Org ID from element entry values */
36 /*CURSOR get_pp_details( p_asg_act_id NUMBER, p_effective_date DATE ) IS
37 SELECT scl.segment2
38 FROM hr_soft_coding_keyflex scl
39 ,per_all_assignments paa
40 ,pay_assignment_actions pac
41 WHERE paa.assignment_id = pac.assignment_id
42 AND pac.assignment_action_id = p_asg_act_id
43 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
44 AND scl.enabled_flag = 'Y'
45 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date;*/
46
47 CURSOR get_pp_details( p_asg_act_id NUMBER, p_effective_date DATE , p_ele_type_id NUMBER) IS
48 SELECT screen_entry_value
49 FROM pay_element_entry_values_f peev
50 WHERE element_entry_id = p_ee_id
51 AND input_value_id IN
52 ( select input_value_id
53 from pay_input_values_f
54 where element_type_id = p_ele_type_id
55 and name = 'Third Party Payee')
56 and p_effective_date between peev.effective_start_date and peev.effective_end_date;
57
58
59 /* Added cursor to fetch effective date */
60 CURSOR c_effective_date(p_asg_act_id NUMBER ) IS
61 SELECT effective_date
62 FROM pay_payroll_actions ppa, pay_assignment_actions paa
63 WHERE paa.assignment_action_id = p_asg_act_id
64 AND paa.payroll_action_id = ppa.payroll_action_id ;
65
66
67 BEGIN
68
69 OPEN c_effective_date(p_asg_act_id);
70 FETCH c_effective_date INTO l_effective_date;
71 CLOSE c_effective_date;
72
73
74 OPEN get_element_name(p_ee_id, l_effective_date);
75 FETCH get_element_name INTO l_element_name, l_ele_type_id ;
76 CLOSE get_element_name;
77
78
79 --IF l_element_name = 'Pension' THEN
80 /* Added elements for pension changes */
81 IF l_element_name IN('Pension','Employer Pension','Retro Pension','Retro Employer Pension') THEN
82 /* Added l_ele_type_id for pension changes */
83 OPEN get_pp_details(p_asg_act_id, l_effective_date, l_ele_type_id );
84 FETCH get_pp_details INTO p_third_party_id;
85 CLOSE get_pp_details;
86 END IF;
87
88
89 /* Following check is disabled. Returning -999 is not correct, as this value will be validated */
90 /*
91 IF p_third_party_id IS NULL THEN
92 p_third_party_id := -999;
93 END IF;
94 */
95
96
97 EXCEPTION
98 WHEN others THEN
99 NULL;
100
101 END get_third_party_org_context;
102
103 --------------------------------------------------------------------------------
104 -- Name : LOAD_XML
105 -- Description : This Function returns the XML data with the tag names.
106 -- Parameters : P_NODE_TYPE This parameter can take one of these values: -
107 -- 1. CS - This signifies that string contained in
108 -- P_NODE parameter is start of container
109 -- node. P_DATA parameter is ignored in this
110 -- mode.
111 -- 2. CE - This signifies that string contained in
112 -- P_NODE parameter is end of container
113 -- node. P_DATA parameter is ignored in this
114 -- mode.
115 -- 3. D - This signifies that string contained in
116 -- P_NODE parameter is data node and P_DATA
117 -- carries actual data to be contained by
118 -- tag specified by P_NODE parameter.
119 --
120 -- P_CONTEXT_CODE Context code of Action Information DF.
121 --
122 -- P_NODE Name of XML tag, or, application column name of flex segment.
123 --
124 -- P_DATA Data to be contained by tag specified by P_NODE parameter.
125 -- P_DATA is not used unless P_NODE_TYPE = D.
126 --------------------------------------------------------------------------------
127 --
128 FUNCTION load_xml (p_node_type VARCHAR2,
129 p_context_code VARCHAR2,
130 p_node VARCHAR2,
131 p_data VARCHAR2) RETURN VARCHAR2 IS
132 --
133 CURSOR csr_get_tag_name IS
134 SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name
135 FROM fnd_descr_flex_col_usage_vl
136 WHERE descriptive_flexfield_name = 'Action Information DF'
137 AND descriptive_flex_context_code = p_context_code
138 AND application_column_name = UPPER (p_node);
139 --
140 l_tag_name VARCHAR2(500);
141 l_data pay_action_information.action_information1%TYPE;
142 l_node pay_action_information.action_information1%TYPE;
143 --
144 BEGIN
145 --
146 IF p_node_type = 'CS' THEN
147 l_node := TRANSLATE(p_node, ' /', '__');
148 RETURN '<'||l_node||'>' ;
149 ELSIF p_node_type = 'CE' THEN
150 l_node := TRANSLATE(p_node, ' /', '__');
151 RETURN '</'||l_node||'>';
152 ELSIF p_node_type = 'D' THEN
153 --
154 -- Fetch segment names
155 --
156 OPEN csr_get_tag_name;
157 FETCH csr_get_tag_name INTO l_tag_name;
158 CLOSE csr_get_tag_name;
159 --
160 l_node := nvl( l_tag_name,TRANSLATE(p_node, ' /', '__')) ;
161 /* Handle special charaters in data */
162 l_data := REPLACE (p_data, '\&', '\&');
163 l_data := REPLACE (l_data, '>', '\>');
164 l_data := REPLACE (l_data, '<', '\<');
165 l_data := REPLACE (l_data, '''', '\'');
166 l_data := REPLACE (l_data, '"', '\"');
167 --
168 RETURN '<'||l_node||'>'||l_data||'</'||l_node||'>';
169 END IF;
170 --
171 END load_xml;
172
173
174 -------------------------------------------------------------------------------
175 -- flex_seg_enabled
176 -------------------------------------------------------------------------------
177 FUNCTION flex_seg_enabled(p_context_code VARCHAR2,
178 p_application_column_name VARCHAR2) RETURN BOOLEAN AS
179 --
180 CURSOR csr_seg_enabled IS
181 SELECT 'Y'
182 FROM fnd_descr_flex_col_usage_vl
183 WHERE descriptive_flexfield_name LIKE 'Action Information DF'
184 AND descriptive_flex_context_code = p_context_code
185 AND application_column_name LIKE p_application_column_name
186 AND enabled_flag = 'Y';
187 --
188 l_proc_name varchar2(100);
189 l_exists varchar2(1);
190 --
191 BEGIN
192 --
193 OPEN csr_seg_enabled;
194 FETCH csr_seg_enabled INTO l_exists;
195 CLOSE csr_seg_enabled;
196 --
197 IF l_exists = 'Y' THEN
198 RETURN (TRUE);
199 ELSE
200 RETURN (FALSE);
201 END IF;
202 --
203 END flex_seg_enabled;
204 --
205 -------------------------------------------------------------------------------
206 -- add_custom_xml
207 -------------------------------------------------------------------------------
208 PROCEDURE add_custom_xml (p_assignment_action_id NUMBER
209 ,p_action_information_category VARCHAR2
210 ,p_document_type VARCHAR2) IS
211
212 ----- cursor to get the element information for earnings and deductions elements ----------------
213
214 /*Modified order by clause for bug fix 6165239*/
215
216 CURSOR csr_element_info(p_action_context_id NUMBER
217 ,p_pa_category VARCHAR2
218 ,p_aap_category VARCHAR2) IS
219 SELECT pai.action_information2 element_type_id
220 ,pai.action_information3 input_value_id
221 /* Change the decode to get the element name with pension provider */
222 ,decode(pai1.action_information8,NULL,decode(pai1.action_information3,'PP',pai.action_information4||' ('||pai1.action_information9||')',pai.action_information4),
223 decode(pai1.action_information3,'PP',pai.action_information4||' ('||pai1.action_information9||')',pai.action_information4)) Name
224 ,pai.action_information5 type
225 ,pai.action_information6 uom
226 --,pai1.action_information8 record_count
227 --,sum(pai1.action_information4) value
228 ,sum(fnd_number.canonical_to_number(pai1.action_information4)) value
229 FROM pay_action_information pai
230 ,pay_action_information pai1
231 ,pay_assignment_actions paa
232 WHERE pai.action_context_type = 'PA'
233 AND pai.action_information_category = p_pa_category
234 AND pai1.action_context_type = 'AAP'
235 AND pai.action_information5 <> 'F'
236 AND pai1.action_information3 <> 'F'
237 -- Commented for performance fix
238 /* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
239 FROM pay_assignment_actions paa
240 WHERE paa.source_action_id = p_action_context_id
241 AND paa.assignment_id = pai1.assignment_id
242 )
243 OR pai1.action_context_id = p_action_context_id)*/
244 and pai1.action_information_category = p_aap_category
245 and pai.action_information2 = pai1.action_information1
246 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
247 and pai.action_context_id = paa.payroll_action_id
248 and pai1.action_context_id = paa.assignment_action_id
249 and paa.assignment_action_id = p_action_context_id
250 group by pai.action_information2
251 ,pai.action_information3
252 ,pai.action_information4
253 ,pai.action_information5
254 ,pai.action_information6
255 --Information 3 and 9 added in group by clause to get the sum based on pension provider
256 ,pai1.action_information3
257 ,pai1.action_information9
258 ,pai1.action_information8
259 ORDER BY pai.action_information5 DESC,fnd_number.canonical_to_number(pai1.action_information8);
260
261 ----- cursor to get the element information for additional elements ----------------
262
263 /*Modified order by clause for bug fix 6165239*/
264
265 CURSOR csr_add_element_info(p_action_context_id NUMBER
266 ,p_pa_category VARCHAR2
267 ,p_aap_category VARCHAR2) IS
268 SELECT pai.action_information2 element_type_id
269 ,pai.action_information3 input_value_id
270 ,decode(pai1.action_information8,NULL,pai.action_information4,
271 pai.action_information4||'('||pai1.action_information8||')') Name
272 ,pai.action_information5 type
273 ,pai.action_information6 uom
274 --,pai1.action_information8 record_count
275 --,sum(pai1.action_information4) value
276 ,pai1.action_information4 value
277 FROM pay_action_information pai
278 ,pay_action_information pai1
279 ,pay_assignment_actions paa
280 WHERE pai.action_context_type = 'PA'
281 AND pai.action_information_category = p_pa_category
282 AND pai1.action_context_type = 'AAP'
283 AND pai.action_information5 = 'F'
284 AND pai1.action_information3 = 'F'
285 -- Commented for performance fix
286 /* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
287 FROM pay_assignment_actions paa
288 WHERE paa.source_action_id = p_action_context_id
289 AND paa.assignment_id = pai1.assignment_id
290 )
291 OR pai1.action_context_id = p_action_context_id) */
292 and pai1.action_information_category = p_aap_category
293 and pai.action_information2 = pai1.action_information1
294 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
295 and pai.action_context_id = paa.payroll_action_id
296 and pai1.action_context_id = paa.assignment_action_id
297 and paa.assignment_action_id = p_action_context_id
301 ,pai.action_information5
298 group by pai.action_information2
299 ,pai.action_information3
300 ,pai.action_information4
302 ,pai.action_information6
303 ,pai1.action_information4
304 ,pai1.action_information8
305 -- ORDER BY pai.action_information5,pai1.action_information8 DESC;
306 ORDER BY pai.action_information5 DESC,fnd_number.canonical_to_number(pai1.action_information8);
307
308
309
310 -------- cursor to get the payroll information -----------------------------
311
312 CURSOR csr_payroll_info(p_action_context_id NUMBER
313 ,p_category VARCHAR2
314 ) IS
315
316 SELECT ppf.payroll_name payroll_name
317 ,ptp.period_name period_name
318 ,ptp.period_type period_type
319 ,ptp.start_date start_date
320 ,ptp.end_date end_date
321 --,pai.effective_date payment_date
322 ,ptp.default_dd_date payment_date
323 FROM per_time_periods ptp
324 ,pay_payrolls_f ppf
325 ,pay_action_information pai
326 WHERE ppf.payroll_id = ptp.payroll_id
327 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
328 AND ptp.time_period_id = pai.action_information16
329 AND pai.action_context_type = 'AAP'
330 AND pai.action_information_category = p_category
331 AND pai.action_context_id=p_action_context_id;
332
333
334 ---------------
335
336 l_total_earnings NUMBER := 0;
337 l_total_deductions NUMBER := 0;
338 l_total_pay NUMBER;
339 cntr_flex_col NUMBER;
340 l_flex_col_num NUMBER;
341 temp varchar2(100);
342 cntr number;
343 l_uom varchar2(240);
344 l_cntr_sql NUMBER;
345 sqlstr DBMS_SQL.VARCHAR2S;
346 csr NUMBER;
347 ret NUMBER;
348
349 ---------------
350
351 -- Private Procedure to build dynamic sql
352
353 PROCEDURE build_sql(p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
354 p_cntr IN OUT NOCOPY NUMBER,
355 p_string VARCHAR2) AS
356
357 l_proc_name varchar2(100);
358
359 BEGIN
360 p_sqlstr_tab(p_cntr) := p_string;
361 p_cntr := p_cntr + 1;
362 END;
363
364 ----------------
365
366 BEGIN
367
368 -- hr_utility.trace_on(null,'dk_payslip');
369 hr_utility.trace('Entering Pay_DK_RULES.add_custom_xml');
370 hr_utility.trace('p_assignment_action_id '|| p_assignment_action_id);
371 hr_utility.trace('p_action_information_category '|| p_action_information_category);
372 hr_utility.trace('p_document_type '|| p_document_type);
373
374
375 if ( (p_document_type = 'PAYSLIP') AND (p_action_information_category is null) ) then
376
377 hr_utility.trace('doc type is PAYSLIP and category is NULL ');
378
379 -- ELEMENT DETAILS
380
381 hr_utility.trace('ELEMENT DETAILS : start ');
382
383 -- Earning and Deduction Elements
384
385 hr_utility.trace('Earnings and deductions : start ');
386
387 FOR csr_element_info_rec IN csr_element_info (p_assignment_action_id,'EMEA ELEMENT DEFINITION','EMEA ELEMENT INFO') LOOP
388 --
389
390 hr_utility.trace('Inside FOR loop for csr_element_info. ');
391
392 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
393 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL) ;
394 --
395 IF csr_element_info_rec.type = 'E' THEN
396 l_total_earnings := l_total_earnings + csr_element_info_rec.value ;
397 END IF ;
398
399 IF csr_element_info_rec.type = 'D' THEN
400 l_total_deductions := l_total_deductions + csr_element_info_rec.value ;
401 END IF ;
402 --
403 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
404 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2',csr_element_info_rec.element_type_id );
405 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
406 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3',csr_element_info_rec.input_value_id );
407 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
408 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4',csr_element_info_rec.Name);
409 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
410 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5',csr_element_info_rec.type );
411
412 l_uom := hr_general.decode_lookup('UNITS',csr_element_info_rec.uom);
413
414 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
415 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6',l_uom );
416 --
417
418 --pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
419 -- load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4',csr_element_info_rec.value );
420
421 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
425 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
422 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4',fnd_number.canonical_to_number(csr_element_info_rec.value) );
423
424 --
426 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
427 --
428 END LOOP;
429 -- --
430
431 hr_utility.trace('Earnings and deductions : end ');
432
433 -- Additional Elements
434
435 hr_utility.trace('Additional Elements : start ');
436
437 FOR csr_element_info_rec IN csr_add_element_info (p_assignment_action_id,'EMEA ELEMENT DEFINITION','EMEA ELEMENT INFO') LOOP
438 --
439
440 hr_utility.trace('Inside FOR loop for csr_add_element_info. ');
441
442 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
443 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL) ;
444 --
445 --
446 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
447 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2',csr_element_info_rec.element_type_id );
448 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
449 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3',csr_element_info_rec.input_value_id );
450 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
451 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4',csr_element_info_rec.Name);
452 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
453 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5',csr_element_info_rec.type );
454
455 l_uom := hr_general.decode_lookup('UNITS',csr_element_info_rec.uom);
456
457 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
458 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6',l_uom );
459 --
460
461 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
462 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4',csr_element_info_rec.value );
463
464 --
465 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
466 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
467 --
468 END LOOP;
469 -- --
470
471
472 hr_utility.trace('Additional Elements : end ');
473
474 hr_utility.trace('ELEMENT DETAILS : end ');
475
476 -- PAYROLL PROCESSING INFORMATION
477
478 hr_utility.trace('PAYROLL PROCESSING INFORMATION : start ');
479
480
481 FOR payroll_info_rec IN csr_payroll_info(p_assignment_action_id , 'EMPLOYEE DETAILS' )
482 LOOP
483
484 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
485 load_xml('CS', NULL, 'PAYROLL PROCESSING INFORMATION', NULL) ;
486
487 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
488 load_xml('D', NULL, 'PAYROLL_NAME',payroll_info_rec.payroll_name );
489
490 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
491 load_xml('D', NULL, 'PERIOD_NAME',payroll_info_rec.period_name );
492
493 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
494 load_xml('D', NULL, 'PERIOD_TYPE',payroll_info_rec.period_type);
495
496 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
497 load_xml('D', NULL, 'START_DATE',payroll_info_rec.start_date );
498
499 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
500 load_xml('D', NULL, 'END_DATE',payroll_info_rec.end_date );
501
502 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
503 load_xml('D', NULL, 'PAYMENT_DATE',payroll_info_rec.payment_date );
504 --
505 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
506 load_xml('CE', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
507 --
508
509 END LOOP;
510
511 hr_utility.trace('PAYROLL PROCESSING INFORMATION : end ');
512
513 hr_utility.trace('SUMMARY OF PAYMENTS : start ');
514
515 -- SUMMARY OF PAYMENTS
516 l_total_pay := l_total_earnings - l_total_deductions ;
517 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
518 load_xml('CS', NULL, 'SUMMARY OF PAYMENTS', NULL);
519 --
520 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
521 load_xml('D', NULL, 'TOTAL_EARNINGS', fnd_number.canonical_to_number(l_total_earnings) );
522 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
523 load_xml('D', NULL, 'TOTAL_DEDUCTIONS', fnd_number.canonical_to_number(l_total_deductions) );
524 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
525 load_xml('D', NULL, 'TOTAL_PAY', fnd_number.canonical_to_number(l_total_pay) );
526 --
527 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
528 load_xml('CE', NULL, 'SUMMARY OF PAYMENTS', NULL);
529 --
530
531 hr_utility.trace('SUMMARY OF PAYMENTS : end ');
532
533 hr_utility.trace('BALANCE DETAILS : start ');
534
535 -- BALANCE DETAILS
536 --
537 l_cntr_sql := 1;
538
539 -- new
540 build_sql(sqlstr, l_cntr_sql, ' Begin FOR run_types_rec IN pay_dk_rules.csr_run_types ('||p_assignment_action_id||') LOOP ');
541 build_sql(sqlstr, l_cntr_sql, ' FOR csr_balance_info_rec IN pay_dk_rules.csr_balance_info (run_types_rec.assignment_action_id,''EMEA BALANCE DEFINITION'',''EMEA BALANCES'') LOOP ');
542 -- end new
543 -- build_sql(sqlstr, l_cntr_sql, ' Begin FOR csr_balance_info_rec IN pay_dk_rules.csr_balance_info ('||p_assignment_action_id||',''EMEA BALANCE DEFINITION'',''EMEA BALANCES'') LOOP ');
544 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) := pay_dk_rules.load_xml(''CS'', NULL, ''BALANCE DETAILS'', NULL); ');
545 FOR cntr in 1..30 LOOP
546
547 IF pay_dk_rules.flex_seg_enabled ('EMEA BALANCE DEFINITION', 'ACTION_INFORMATION'||cntr) THEN
548 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
549 pay_dk_rules.load_xml(''D'', ''EMEA BALANCE DEFINITION'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.a'||cntr||'); ');
550 END IF;
551
552 IF pay_dk_rules.flex_seg_enabled ('EMEA BALANCES', 'ACTION_INFORMATION'||cntr) THEN
553 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
554 pay_dk_rules.load_xml(''D'', ''EMEA BALANCES'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.aa'||cntr||'); ');
555 END IF;
556
557 END LOOP;
558 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) := pay_dk_rules.load_xml(''CE'', NULL, ''BALANCE DETAILS'', NULL); ');
559 -- new
560 build_sql(sqlstr, l_cntr_sql, ' END LOOP; ');
561 -- end new
562 build_sql(sqlstr, l_cntr_sql, ' END LOOP; End; ');
563 --
564 csr := DBMS_SQL.OPEN_CURSOR;
565 DBMS_SQL.PARSE(csr
566 ,sqlstr
567 ,sqlstr.first()
568 ,sqlstr.last()
569 ,FALSE
570 ,DBMS_SQL.V7);
571 ret := DBMS_SQL.EXECUTE(csr);
572 DBMS_SQL.CLOSE_CURSOR(csr);
573
574 hr_utility.trace('BALANCE DETAILS : end ');
575 --
576 end if;
577 --hr_utility.trace_off();
578
579 hr_utility.trace('Leaving Pay_DK_RULES.add_custom_xml');
580
581 Exception
582 when others then
583 hr_utility.trace('DK error message :'||sqlerrm);
584 Null ;
585
586 END add_custom_xml;
587
588 -----
589
590 -- Added functions for Batch Printing of Payslips
591
592 --------------------------------------------------------------------------
593 -- Returns any of the values from SEGMENT1, SEGMENT2, .. ,SEGMENT30
594 --------------------------------------------------------------------------
595 FUNCTION get_payslip_sort_order1 RETURN VARCHAR2 IS
596 BEGIN
597 --
598 RETURN 'SEGMENT2';
599 --
600 END get_payslip_sort_order1;
601
602
603 --------------------------------------------------------------------------
604 -- Returns any of the values ORGANIZATION_ID or ASSIGNMENT_NUMBER
605 --------------------------------------------------------------------------
606 FUNCTION get_payslip_sort_order2 RETURN VARCHAR2 IS
607 BEGIN
608 --
609 RETURN 'ORGANIZATION_ID';
610 --
611 END get_payslip_sort_order2;
612
613
614 --------------------------------------------------------------------------
615 -- get_payslip_sort_order3
616 -- Returns any of the values LAST_NAME, FIRST_NAME or FULL_NAME
617 --------------------------------------------------------------------------
618 FUNCTION get_payslip_sort_order3 RETURN VARCHAR2 IS
619 BEGIN
620 --
621 RETURN 'LAST_NAME';
622 --
623 END get_payslip_sort_order3;
624
625
626 END PAY_DK_RULES;