[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_RULES
Source
4 -----------------------------------------------------------------------------
1 package body PAY_DK_RULES as
2 /* $Header: pydkrule.pkb 120.24 2012/03/01 14:08:10 mkuppuch noship $ */
3
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 );
87
84 FETCH get_pp_details INTO p_third_party_id;
85 CLOSE get_pp_details;
86 END IF;
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
210 ,p_document_type VARCHAR2) IS
207 -------------------------------------------------------------------------------
208 PROCEDURE add_custom_xml (p_assignment_action_id NUMBER
209 ,p_action_information_category VARCHAR2
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',
223 SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1)||' ('||
224 SUBSTR(pai1.action_information9,1,INSTR(pai1.action_information9,':')-1)||')',
225 SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1)),
226 decode(pai1.action_information3,'PP',SUBSTR(pai.action_information4,
227 INSTR(pai.action_information4,',')+1)||' ('||SUBSTR(pai1.action_information9,1,
228 INSTR(pai1.action_information9,':')-1)||')',SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1))) Name
229 ,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE -- Changes for Bug 7229247
230 ,pai.action_information5 type
231 ,pai.action_information6 uom
232 ,fnd_number.canonical_to_number(pai1.action_information8) record_count -- Format Changes for Payslip Bug - 7229247 /* 9358829 */
233 --,sum(pai1.action_information4) value
234 ,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price -- Format Changes in Payslip Bug - 7229247
235 ,sum(fnd_number.canonical_to_number(pai1.action_information4)) value -- Format Changes for Payslip Bug - 7229247
236 FROM pay_action_information pai
237 ,pay_action_information pai1
238 ,pay_assignment_actions paa
239 WHERE pai.action_context_type = 'PA'
240 AND pai.action_information_category = p_pa_category
241 AND pai1.action_context_type = 'AAP'
242 AND pai.action_information5 <> 'F'
243 AND pai1.action_information3 <> 'F'
244 ---- Commented for performance fix
245 /* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
246 FROM pay_assignment_actions paa
247 WHERE paa.source_action_id = p_action_context_id
248 AND paa.assignment_id = pai1.assignment_id
249 )
250 OR pai1.action_context_id = p_action_context_id)*/
251 and pai1.action_information_category = p_aap_category
252 and pai.action_information2 = pai1.action_information1
253 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
254 and pai.action_context_id = paa.payroll_action_id
255 and pai1.action_context_id = paa.assignment_action_id
256 and paa.assignment_action_id = p_action_context_id
257 group by pai.action_information2
258 ,pai.action_information3
259 ,pai.action_information4
260 ,pai.action_information5
261 ,pai.action_information6
262 ,pai.action_information9 -- Format Changes for Payslip Bug - 7229247
263 --Information 3 and 9 added in group by clause to get the sum based on pension provider
264 ,pai1.action_information3
265 ,pai1.action_information9 -- Format Changes for Payslip Bug - 7229247
266 ,pai1.action_information10 /* 9358829 */
267 ,pai1.action_information8 -- Format Changes for Payslip Bug - 7229247
268 ORDER BY pai.action_information5 DESC,fnd_number.canonical_to_number(pai1.action_information10); /* 9358829 */
269
270
271
272 ----- cursor to get the element information for additional elements ----------------
273
274 /*Modified order by clause for bug fix 6165239*/
275
276 CURSOR csr_add_element_info(p_action_context_id NUMBER
277 ,p_pa_category VARCHAR2
278 ,p_aap_category VARCHAR2) IS
279 SELECT pai.action_information2 element_type_id
280 ,pai.action_information3 input_value_id
281 -- Changes for Payslip Format
282 -- Start
283 -- ,decode(pai1.action_information8,NULL,pai.action_information4,
284 -- pai.action_information4||'('||pai1.action_information8||')') Name
285 ,SUBSTR(pai.action_information4,INSTR(pai.action_information4,',')+1) Name
286 ,SUBSTR(pai.action_information4,1,INSTR(pai.action_information4,',')-1) CODE
287 ,fnd_number.canonical_to_number(pai1.action_information8) record_count
288 ,substr(pai1.action_information9,instr(pai1.action_information9,':',-1)+1) unit_price
289 -- End
290 ,pai.action_information5 type
291 ,pai.action_information6 uom
292 --,sum(pai1.action_information4) value
293 ,pai1.action_information4 value
294 FROM pay_action_information pai
295 ,pay_action_information pai1
296 ,pay_assignment_actions paa
297 WHERE pai.action_context_type = 'PA'
298 AND pai.action_information_category = p_pa_category
299 AND pai1.action_context_type = 'AAP'
300 AND pai.action_information5 = 'F'
301 AND pai1.action_information3 = 'F'
302 -- Commented for performance fix
303 /* AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
304 FROM pay_assignment_actions paa
305 WHERE paa.source_action_id = p_action_context_id
306 AND paa.assignment_id = pai1.assignment_id
307 )
308 OR pai1.action_context_id = p_action_context_id) */
309 and pai1.action_information_category = p_aap_category
310 and pai.action_information2 = pai1.action_information1
311 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
312 and pai.action_context_id = paa.payroll_action_id
313 and pai1.action_context_id = paa.assignment_action_id
314 and paa.assignment_action_id = p_action_context_id
315 group by pai.action_information2
316 ,pai.action_information3
317 ,pai.action_information4 -- Format Changes for Payslip Bug - 7229247
318 ,pai.action_information5
319 ,pai.action_information6
320 ,pai1.action_information4
321 ,pai1.action_information8 -- Format Changes for Payslip Bug - 7229247
322 ,pai1.action_information9 -- Format Changes for Payslip Bug - 7229247
323 -- ORDER BY pai.action_information5,pai1.action_information8 DESC;
324 ORDER BY pai.action_information5 DESC,fnd_number.canonical_to_number(pai1.action_information8);
325
326
327
328 -------- cursor to get the payroll information -----------------------------
329
330 CURSOR csr_payroll_info(p_action_context_id NUMBER
331 ,p_category VARCHAR2
332 ) IS
333
334 SELECT ppf.payroll_name payroll_name
335 ,ptp.period_name period_name
336 ,ptp.period_type period_type
337 ,ptp.start_date start_date
338 ,ptp.end_date end_date
339 --,pai.effective_date payment_date
340 ,ptp.default_dd_date payment_date
341 FROM per_time_periods ptp
342 ,pay_payrolls_f ppf
343 ,pay_action_information pai
344 WHERE ppf.payroll_id = ptp.payroll_id
345 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
346 AND ptp.time_period_id = pai.action_information16
347 AND pai.action_context_type = 'AAP'
348 AND pai.action_information_category = p_category
349 AND pai.action_context_id=p_action_context_id;
350
351
352
353 --AND paa.assignment_id = pai.assignment_id
354 --AND paa.assignment_action_id=p_action_context_id
355 --AND (pai.action_context_id = paa.assignment_action_id
356 -- or pai.action_context_id = paa.source_action_id);
357
358 /*AND (pai.action_context_id = p_action_context_id
359 OR pai.action_context_id = ( SELECT paa.source_action_id
360 FROM pay_assignment_actions paa
361 WHERE paa.assignment_action_id = p_action_context_id
362 AND paa.assignment_id = pai.Assignment_ID
363 ));*/
364
365 ---------------
366
367 l_total_earnings NUMBER := 0;
368 l_total_deductions NUMBER := 0;
369 l_total_pay NUMBER;
370 cntr_flex_col NUMBER;
371 l_flex_col_num NUMBER;
372 temp varchar2(100);
373 cntr number;
374 l_uom varchar2(240);
375 l_cntr_sql NUMBER;
376 sqlstr DBMS_SQL.VARCHAR2S;
377 csr NUMBER;
378 ret NUMBER;
379
380 ---------------
381
382 -- Private Procedure to build dynamic sql
383
384 PROCEDURE build_sql(p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
385 p_cntr IN OUT NOCOPY NUMBER,
386 p_string VARCHAR2) AS
387
388 l_proc_name varchar2(100);
389
390 BEGIN
391 p_sqlstr_tab(p_cntr) := p_string;
392 p_cntr := p_cntr + 1;
393 END;
394
395 ----------------
396
397 BEGIN
398
399
400 hr_utility.trace('Entering Pay_DK_RULES.add_custom_xml');
401 hr_utility.trace('p_assignment_action_id '|| p_assignment_action_id);
402 hr_utility.trace('p_action_information_category '|| p_action_information_category);
403 hr_utility.trace('p_document_type '|| p_document_type);
404
405
406 if ( (p_document_type = 'PAYSLIP') AND (p_action_information_category is null) ) then
407
408 hr_utility.trace('doc type is PAYSLIP and category is NULL ');
409
410 -- ELEMENT DETAILS
411
412 hr_utility.trace('ELEMENT DETAILS : start ');
413
414 -- Earning and Deduction Elements
415
416 hr_utility.trace('Earnings and deductions : start ');
417
418 FOR csr_element_info_rec IN csr_element_info (p_assignment_action_id,'EMEA ELEMENT DEFINITION','EMEA ELEMENT INFO') LOOP
419 --
420
421 hr_utility.trace('Inside FOR loop for csr_element_info. ');
422
423 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
424 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL) ;
425 --
426 IF csr_element_info_rec.type = 'E' THEN
427 l_total_earnings := l_total_earnings + fnd_number.canonical_to_number(nvl(csr_element_info_rec.value,0)) ;
428 END IF ;
429
430 IF csr_element_info_rec.type = 'D' THEN
431 l_total_deductions := l_total_deductions + fnd_number.canonical_to_number(nvl(csr_element_info_rec.value,0)) ;
432 END IF ;
433 --
434 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
435 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2',csr_element_info_rec.element_type_id );
436 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
437 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3',csr_element_info_rec.input_value_id );
438 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
439 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4',csr_element_info_rec.Name);
440 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
441 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5',csr_element_info_rec.type );
442
443 l_uom := hr_general.decode_lookup('UNITS',csr_element_info_rec.uom);
444
445 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
446 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6',l_uom );
447 --
448
449 --pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
450 -- load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4',csr_element_info_rec.value );
451 /* Changes made for Payslip Format Change Bug - 7229247 */
452 /*Start*/
453
454 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
455 load_xml('D', NULL, 'ELEMENT_CODE',csr_element_info_rec.CODE );
456 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
457 load_xml('D', NULL, 'NUM_UNITS',csr_element_info_rec.record_count );
458 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
459 load_xml('D',NULL, 'RATE_VAL',csr_element_info_rec.unit_price );
460
461 /*End*/
462
463 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
464 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4',fnd_number.canonical_to_number(csr_element_info_rec.value) );
465
466
467 --
468 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
469 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
470 --
471 END LOOP;
472 -- --
473
474 hr_utility.trace('Earnings and deductions : end ');
475
476 -- Additional Elements
477
478 hr_utility.trace('Additional Elements : start ');
479
480 FOR csr_element_info_rec IN csr_add_element_info (p_assignment_action_id,'EMEA ELEMENT DEFINITION','EMEA ELEMENT INFO') LOOP
481 --
482
483 hr_utility.trace('Inside FOR loop for csr_add_element_info. ');
484
485 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
486 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL) ;
487 --
488 --
489 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
490 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2',csr_element_info_rec.element_type_id );
491 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
492 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3',csr_element_info_rec.input_value_id );
493 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
494 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4',csr_element_info_rec.Name);
495 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
496 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5',csr_element_info_rec.type );
497
498 l_uom := hr_general.decode_lookup('UNITS',csr_element_info_rec.uom);
499
500 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
501 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6',l_uom );
502 --
503 /* Changes made for Payslip Format Change Bug - 7229247 */
504 /*Start*/
505
506 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
507 load_xml('D', NULL, 'ELEMENT_CODE',csr_element_info_rec.CODE );
508 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
509 load_xml('D', NULL, 'NUM_UNITS',csr_element_info_rec.record_count );
510 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
511 load_xml('D', NULL, 'RATE_VAL',csr_element_info_rec.unit_price );
512 /*End*/
513
514 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
515 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4',csr_element_info_rec.value );
516
517 --
518 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
519 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
520 --
521 END LOOP;
522 -- --
523 fnd_file.put_line(fnd_file.log,'This is the tested case2');
524
525
526 hr_utility.trace('Additional Elements : end ');
527
528 hr_utility.trace('ELEMENT DETAILS : end ');
529
530 -- PAYROLL PROCESSING INFORMATION
531
532 hr_utility.trace('PAYROLL PROCESSING INFORMATION : start ');
533
534
535 FOR payroll_info_rec IN csr_payroll_info(p_assignment_action_id , 'EMPLOYEE DETAILS' )
536 LOOP
537
538 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
539 load_xml('CS', NULL, 'PAYROLL PROCESSING INFORMATION', NULL) ;
540
541 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
542 load_xml('D', NULL, 'PAYROLL_NAME',payroll_info_rec.payroll_name );
543
544 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
545 load_xml('D', NULL, 'PERIOD_NAME',payroll_info_rec.period_name );
546
547 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
548 load_xml('D', NULL, 'PERIOD_TYPE',payroll_info_rec.period_type);
549
550 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
551 load_xml('D', NULL, 'START_DATE',payroll_info_rec.start_date );
552
553 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
554 load_xml('D', NULL, 'END_DATE',payroll_info_rec.end_date );
555
556 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
557 load_xml('D', NULL, 'PAYMENT_DATE',payroll_info_rec.payment_date );
558 --
559 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
560 load_xml('CE', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
561 --
562
563 END LOOP;
564
565 hr_utility.trace('PAYROLL PROCESSING INFORMATION : end ');
566
567 hr_utility.trace('SUMMARY OF PAYMENTS : start ');
568
569 -- SUMMARY OF PAYMENTS
570 l_total_pay := l_total_earnings - l_total_deductions ;
571 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
572 load_xml('CS', NULL, 'SUMMARY OF PAYMENTS', NULL);
573 --
574 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
575 load_xml('D', NULL, 'TOTAL_EARNINGS', fnd_number.canonical_to_number(l_total_earnings) );
576 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
577 load_xml('D', NULL, 'TOTAL_DEDUCTIONS', fnd_number.canonical_to_number(l_total_deductions) );
578 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
579 load_xml('D', NULL, 'TOTAL_PAY', fnd_number.canonical_to_number(l_total_pay) );
580 --
581 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
582 load_xml('CE', NULL, 'SUMMARY OF PAYMENTS', NULL);
583 --
584
585 hr_utility.trace('SUMMARY OF PAYMENTS : end ');
586
587 hr_utility.trace('BALANCE DETAILS : start ');
588
589 fnd_file.put_line(fnd_file.log,'This is the tested case1');
590
591 -- BALANCE DETAILS
592 --
593 l_cntr_sql := 1;
594
595 -- new
596 build_sql(sqlstr, l_cntr_sql, ' Begin FOR run_types_rec IN pay_dk_rules.csr_run_types ('||p_assignment_action_id||') LOOP ');
597 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 ');
598 -- end new
599 -- 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 ');
600 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); ');
601 FOR cntr in 1..30 LOOP
602
603 IF pay_dk_rules.flex_seg_enabled ('EMEA BALANCE DEFINITION', 'ACTION_INFORMATION'||cntr) THEN
604 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
605 pay_dk_rules.load_xml(''D'', ''EMEA BALANCE DEFINITION'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.a'||cntr||'); ');
606 END IF;
607
608 IF pay_dk_rules.flex_seg_enabled ('EMEA BALANCES', 'ACTION_INFORMATION'||cntr) THEN
609 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
610 pay_dk_rules.load_xml(''D'', ''EMEA BALANCES'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.aa'||cntr||'); ');
611 END IF;
612
613 END LOOP;
614 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); ');
615 -- new
616 build_sql(sqlstr, l_cntr_sql, ' END LOOP; ');
617 -- end new
618 build_sql(sqlstr, l_cntr_sql, ' END LOOP; End; ');
619 --
620 csr := DBMS_SQL.OPEN_CURSOR;
621 DBMS_SQL.PARSE(csr
622 ,sqlstr
623 ,sqlstr.first()
624 ,sqlstr.last()
625 ,FALSE
626 ,DBMS_SQL.V7);
627 ret := DBMS_SQL.EXECUTE(csr);
628 DBMS_SQL.CLOSE_CURSOR(csr);
629
630 hr_utility.trace('BALANCE DETAILS : end ');
631 --
632 end if;
633 --hr_utility.trace_off();
634
635 hr_utility.trace('Leaving Pay_DK_RULES.add_custom_xml');
636
637 Exception
638 when others then
639 hr_utility.trace('DK error message :'||sqlerrm);
640 Null ;
641
642 END add_custom_xml;
643
644 -----
645
646 -- Added functions for Batch Printing of Payslips
647
648 --------------------------------------------------------------------------
649 -- Returns any of the values from SEGMENT1, SEGMENT2, .. ,SEGMENT30
650 --------------------------------------------------------------------------
651 FUNCTION get_payslip_sort_order1 RETURN VARCHAR2 IS
652 BEGIN
653 --
654 RETURN 'SEGMENT2';
655 --
656 END get_payslip_sort_order1;
657
658
659 --------------------------------------------------------------------------
660 -- Returns any of the values ORGANIZATION_ID or ASSIGNMENT_NUMBER
661 --------------------------------------------------------------------------
662 FUNCTION get_payslip_sort_order2 RETURN VARCHAR2 IS
663 BEGIN
664 --
665 RETURN 'ORGANIZATION_ID';
666 --
667 END get_payslip_sort_order2;
668
669
670 --------------------------------------------------------------------------
671 -- get_payslip_sort_order3
672 -- Returns any of the values LAST_NAME, FIRST_NAME or FULL_NAME
673 --------------------------------------------------------------------------
674 FUNCTION get_payslip_sort_order3 RETURN VARCHAR2 IS
675 BEGIN
676 --
677 RETURN 'LAST_NAME';
678 --
679 END get_payslip_sort_order3;
680
681 /* 10156538 start */
682 procedure archinit(p_payroll_action_id in number) is
683
684 l_payroll_id NUMBER;
685 leg_param pay_payroll_actions.legislative_parameters%TYPE;
686 l_ppa_payroll_id pay_payroll_actions.payroll_id%TYPE;
687 l_key varchar2(30) := 'PAYROLL_ID=';
688 l_val pay_payroll_actions.legislative_parameters%TYPE;
689
690
691 begin
692
693
694 hr_utility.set_location('Entering procedure archinit' , 1);
695
696 SELECT legislative_parameters,payroll_id
697 INTO leg_param,l_ppa_payroll_id
698 FROM pay_payroll_actions
699 WHERE payroll_action_id = p_payroll_action_id ;
700
701
702 if instr(leg_param, l_key) <> 0 then
703 l_val := substr(leg_param, instr(leg_param, l_key));
704 if instr(l_val, ' ') = 0 then
705 l_payroll_id := substr(l_val, length(l_key)+1);
706 else
707 l_payroll_id := substr(l_val, length(l_key)+1, instr(l_val,' ') - length(l_key));
708 end if;
709 end if;
710
711 -- Update the Payroll Action with the Payroll ID
712 hr_utility.set_location('l_payroll_id'|| l_payroll_id , 2);
713 IF l_ppa_payroll_id IS NULL and l_payroll_id is not NULL THEN
714
715 UPDATE pay_payroll_actions
716 SET payroll_id = l_payroll_id
717 WHERE payroll_action_id = p_payroll_action_id;
718 hr_utility.set_location('After Update', 2);
719
720 END IF;
721
722
723
724 hr_utility.set_location('leaving procedure archinit' , 5);
725
726 end archinit;
727 /* 10156538 end */
728
729
730 END PAY_DK_RULES;