[Home] [Help]
PACKAGE BODY: APPS.PAY_AE_RULES
Source
1 PACKAGE BODY PAY_AE_RULES AS
2 /* $Header: pyaerule.pkb 120.1 2006/05/24 12:53:17 spendhar noship $ */
3
4 g_custom_context pay_action_information.action_information_category%type;
5 g_action_ctx_id NUMBER;
6
7 -------------------------------------------------------------------------------
8 -- flex_seg_enabled
9 -------------------------------------------------------------------------------
10 FUNCTION flex_seg_enabled(p_context_code VARCHAR2,
11 p_application_column_name VARCHAR2) RETURN BOOLEAN AS
12 --
13 CURSOR csr_seg_enabled IS
14 SELECT 'Y'
15 FROM fnd_descr_flex_col_usage_vl
16 WHERE descriptive_flexfield_name LIKE 'Action Information DF'
17 AND descriptive_flex_context_code = p_context_code
18 AND application_column_name LIKE p_application_column_name
19 AND enabled_flag = 'Y';
20 --
21 l_proc_name varchar2(100);
22 l_exists varchar2(1);
23 --
24 BEGIN
25 --
26 OPEN csr_seg_enabled;
27 FETCH csr_seg_enabled INTO l_exists;
28 CLOSE csr_seg_enabled;
29 --
30 IF l_exists = 'Y' THEN
31 RETURN (TRUE);
32 ELSE
33 RETURN (FALSE);
34 END IF;
35 --
36 END flex_seg_enabled;
37 --
38
39 PROCEDURE LOAD_XML (
40 P_NODE_TYPE varchar2,
41 P_CONTEXT_CODE varchar2,
42 P_NODE varchar2,
43 P_DATA varchar2
44 ) AS
45
46 CURSOR csr_get_tag_name IS
47 SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name
48 FROM fnd_descr_flex_col_usage_vl
49 WHERE descriptive_flexfield_name = 'Action Information DF'
50 AND descriptive_flex_context_code = p_context_code
51 AND application_column_name = UPPER (p_node);
52
53 CURSOR csr_get_chk_no IS
54 SELECT paa_chk.serial_number
55 FROM pay_assignment_actions paa_xfr,
56 pay_action_interlocks pai_xfr,
57 pay_action_interlocks pai_chk,
58 pay_assignment_actions paa_chk,
59 pay_payroll_actions ppa_chk
60 WHERE paa_xfr.assignment_action_id = pai_xfr.locking_action_id
61 AND pai_xfr.locked_action_id = pai_chk.locked_action_id
62 AND pai_chk.locking_action_id = paa_chk.assignment_action_id
63 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
64 AND ppa_chk.action_type = 'H'
65 AND paa_xfr.assignment_action_id = g_action_ctx_id;
66
67 l_tag_name varchar2(500);
68 l_chk_no pay_assignment_actions.serial_number%type;
69 l_data pay_action_information.action_information1%type;
70
71 PROCEDURE LOAD_XML_INTERNAL (
72 P_NODE_TYPE varchar2,
73 P_NODE varchar2,
74 P_DATA varchar2
75 ) AS
76
77 l_data pay_action_information.action_information1%type;
78
79 BEGIN
80
81 IF p_node_type = 'CS' THEN
82
83 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '<'||p_node||'>';
84
85 ELSIF p_node_type = 'CE' THEN
86
87 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '</'||p_node||'>';
88
89 ELSIF p_node_type = 'D' THEN
90
91 /* Handle special charaters in data */
92 l_data := REPLACE (p_data, '&', '&');
93 l_data := REPLACE (l_data, '>', '>');
94 l_data := REPLACE (l_data, '<', '<');
95 l_data := REPLACE (l_data, '''', ''');
96 l_data := REPLACE (l_data, '"', '"');
97 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '<'||p_node||'>'||l_data||'</'||p_node||'>';
98 END IF;
99 END LOAD_XML_INTERNAL;
100
101
102 BEGIN
103
104 IF p_node_type = 'D' THEN
105
106 /* Fetch segment names */
107 OPEN csr_get_tag_name;
108 FETCH csr_get_tag_name INTO l_tag_name;
109 CLOSE csr_get_tag_name;
110
111 /* Fetch cheque number */
112 IF p_context_code = 'EMPLOYEE NET PAY DISTRIBUTION' AND
113 l_tag_name = 'CHECK_DEPOSIT_NUMBER' THEN
114 OPEN csr_get_chk_no;
115 FETCH csr_get_chk_no INTO l_chk_no;
116 CLOSE csr_get_chk_no;
117 END IF;
118
119 END IF;
120
121 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
122 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node, ' /', '__'));
123 IF p_node_type IN ('CS', 'CE') THEN
124 l_tag_name := nvl(g_custom_context, TRANSLATE(p_node, ' /', '__'));
125 END IF;
126 ELSE
127 l_tag_name := p_node;
128 END IF;
129
130 l_data := nvl(l_chk_no, p_data);
131 load_xml_internal (p_node_type, l_tag_name, l_data);
132 END LOAD_XML;
133
134
135 PROCEDURE add_custom_xml
136 (p_assignment_action_id number,
137 p_action_information_category varchar2,
138 p_document_type varchar2) as
139
140 /* Cursor to fetch Payroll Processing Information */
141 CURSOR csr_payroll_info(p_action_context_id NUMBER) IS
142 SELECT ppf.payroll_name payroll_name
143 ,ptp.period_name period_name
144 ,ptp.period_type period_type
145 ,ptp.start_date start_date
146 ,ptp.end_date end_date
147 ,pai.effective_date payment_date
148 FROM per_time_periods ptp
149 ,pay_payrolls_f ppf
150 ,pay_action_information pai
151 WHERE ppf.payroll_id = ptp.payroll_id
152 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
153 AND ptp.time_period_id = pai.action_information16
154 AND pai.action_context_type = 'AAP'
155 AND pai.action_information_category = 'EMPLOYEE DETAILS'
156 AND (pai.action_context_id = p_action_context_id
157 OR pai.action_context_id = ( SELECT paa.source_action_id
158 FROM pay_assignment_actions paa
159 WHERE paa.assignment_action_id = p_action_context_id
160 -- AND paa.assignment_id = pai.assignment_id
161 ));
162
163 /* Cursor to fetch Element Details */
164 CURSOR csr_element_info(p_action_context_id NUMBER) IS
165 SELECT pai.action_information2 element_type_id
166 ,pai.action_information3 input_value_id
167 ,decode(pai1.action_information8,NULL,pai.action_information4,
168 pai.action_information4||'('||pai1.action_information8||')') Name
169 ,pai.action_information5 type
170 ,pai.action_information6 uom
171 ,sum(pai1.action_information4) value
172 FROM pay_action_information pai
173 ,pay_action_information pai1
174 ,pay_assignment_actions paa
175 WHERE pai.action_context_type = 'PA'
176 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
177 AND pai1.action_context_type = 'AAP'
178 AND pai.action_information5 <> 'F'
179 AND pai1.action_information3 <> 'F'
180 /*AND ( pai1.action_context_id IN ( SELECT paa.assignment_action_id
181 FROM pay_assignment_actions paa
182 WHERE paa.source_action_id = p_action_context_id
183 AND paa.assignment_id = pai1.assignment_id)
184 OR pai1.action_context_id = p_action_context_id)*/
185 and pai1.action_information_category = 'EMEA ELEMENT INFO'
186 and pai.action_information2 = pai1.action_information1
187 and pai.action_information3 = pai1.action_information2
188 and pai.action_context_id = paa.payroll_action_id
189 and pai1.action_context_id = paa.assignment_action_id
190 and paa.assignment_action_id = p_action_context_id
191 group by pai.action_information2
192 ,pai.action_information3
193 ,pai.action_information4
194 ,pai.action_information5
195 ,pai.action_information6
196 ,pai1.action_information8
197 ORDER BY pai.action_information5,pai1.action_information8 DESC;
198
199 /* Cursor to fetch Additional Information Elements Details */
200 CURSOR csr_add_element_info(p_action_context_id NUMBER) IS
201 SELECT pai.action_information2 element_type_id
202 ,pai.action_information3 input_value_id
203 ,decode(pai1.action_information8,NULL,pai.action_information4,
204 pai.action_information4||'('||pai1.action_information8||')') Name
205 ,pai.action_information5 type
206 ,pai.action_information6 uom
207 ,pai1.action_information4 value
208 FROM pay_action_information pai
209 ,pay_action_information pai1
210 ,pay_assignment_actions paa
211 WHERE pai.action_context_type = 'PA'
212 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
213 AND pai1.action_context_type = 'AAP'
214 AND pai.action_information5 = 'F'
215 AND pai1.action_information3 = 'F'
216 /*AND ( pai1.action_context_id IN ( SELECT paa.assignment_action_id
217 FROM pay_assignment_actions paa
218 WHERE paa.source_action_id = p_action_context_id
219 AND paa.assignment_id = pai1.assignment_id)
220 OR pai1.action_context_id = p_action_context_id)*/
221 AND pai1.action_information_category = 'EMEA ELEMENT INFO'
222 AND pai.action_information2 = pai1.action_information1
223 AND pai.action_information3 = pai1.action_information2
224 AND pai.action_context_id = paa.payroll_action_id
225 AND pai1.action_context_id = paa.assignment_action_id
226 AND paa.assignment_action_id = p_action_context_id
227 GROUP BY pai.action_information2
228 ,pai.action_information3
229 ,pai.action_information4
230 ,pai.action_information5
231 ,pai.action_information6
232 ,pai1.action_information4
233 ,pai1.action_information8
234 ORDER BY pai.action_information5,pai1.action_information8 DESC;
235
236 /* Cursor to fetch input value's name */
237 CURSOR csr_get_input_value (p_input_value_id NUMBER) IS
238 SELECT name
239 FROM pay_input_values_f
240 WHERE input_value_id = p_input_value_id;
241
242 /* Cursor to fetch Balance Details */
243 CURSOR csr_balance_info(p_action_context_id NUMBER) IS
244 SELECT pai.action_information4 Name
245 ,pai.action_information2 defined_balance_id
246 ,pai.action_information6 type
247 ,pai1.action_information4 value
248 ,pai1.action_information6 uom
249 FROM pay_action_information pai
250 ,pay_action_information pai1
251 ,pay_assignment_actions paa
252 WHERE pai.action_context_type = 'PA'
253 AND pai.action_information_category = 'EMEA BALANCE DEFINITION'
254 AND pai1.action_context_type = 'AAP'
255 AND pai1.action_information_category = 'EMEA BALANCES'
256 AND pai.action_information6 = 'OBAL'
257 AND pai1.action_information2 = 'OBAL'
258 AND pai.action_information2 = pai1.action_information1
259 AND pai.action_context_id = paa.payroll_action_id
260 AND pai1.action_context_id = paa.assignment_action_id
261 AND paa.assignment_action_id in
262 (SELECT paa1.assignment_action_id
263 FROM pay_assignment_actions paa1
264 WHERE paa1.source_action_id = p_action_context_id
265 AND paa1.action_status = 'C'
266 UNION
267 SELECT p_action_context_id
268 FROM dual )
269 ORDER BY pai.action_information5,pai1.action_information5 DESC;
270
271
272 l_xml CLOB;
273 cntr_flex_col NUMBER;
274 l_flex_col_num NUMBER;
275 sqlstr DBMS_SQL.VARCHAR2S;
276 csr NUMBER;
277 ret NUMBER;
278 l_cntr_sql NUMBER;
279 l_total_pay NUMBER;
280 l_total_earnings NUMBER;
281 l_total_deductions NUMBER;
282 l_input_value VARCHAR2(100);
283
284
285 PROCEDURE build_sql(p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
286 p_cntr IN OUT NOCOPY NUMBER,
287 p_string VARCHAR2) AS
288 --
289 l_proc_name varchar2(100);
290 --
291 BEGIN
292 p_sqlstr_tab(p_cntr) := p_string;
293 p_cntr := p_cntr + 1;
294 END;
295
296 BEGIN
297 l_flex_col_num := 30;
298
299 IF p_action_information_category IS NULL AND p_document_type ='PAYSLIP' THEN
300
301 l_total_earnings:=0 ;
302 l_total_deductions :=0;
303 g_action_ctx_id := p_assignment_action_id ;
304
305 FOR payroll_info_rec IN csr_payroll_info (p_assignment_action_id)
306 LOOP
307
308 load_xml('CS', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
309 load_xml('D', NULL, 'PAYROLL_NAME', payroll_info_rec.payroll_name );
310 load_xml('D', NULL, 'PERIOD_NAME', payroll_info_rec.period_name);
311 load_xml('D', NULL, 'PERIOD_TYPE', payroll_info_rec.period_type);
312 load_xml('D', NULL, 'START_DATE', payroll_info_rec.start_date);
313 load_xml('D', NULL, 'END_DATE', payroll_info_rec.end_date);
314 load_xml('D', NULL, 'PAYMENT_DATE', payroll_info_rec.payment_date);
315 load_xml('CE', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
316
317 END LOOP;
318 --
319 FOR element_info_rec IN csr_element_info(p_assignment_action_id)
320 LOOP
321
322 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
323 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', element_info_rec.element_type_id);
324 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', element_info_rec.input_value_id);
325 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', element_info_rec.Name);
326 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', element_info_rec.type);
327 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', element_info_rec.uom);
328 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(element_info_rec.value));
329 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
330
331 IF element_info_rec.type = 'E' THEN
332 l_total_earnings := fnd_number.canonical_to_number(l_total_earnings) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
333 ELSIF element_info_rec.type = 'D' THEN
334 l_total_deductions := fnd_number.canonical_to_number(l_total_deductions) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
335 END IF ;
336 l_total_pay := l_total_earnings - l_total_deductions ;
337
338 END LOOP;
339
340 FOR add_element_info_rec IN csr_add_element_info(p_assignment_action_id)
341 LOOP
342
343 OPEN csr_get_input_value(add_element_info_rec.input_value_id);
344 FETCH csr_get_input_value INTO l_input_value;
345 CLOSE csr_get_input_value;
346
347 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
348 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', add_element_info_rec.element_type_id);
349 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', add_element_info_rec.input_value_id);
350 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', add_element_info_rec.Name);
351 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', add_element_info_rec.type);
352 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', add_element_info_rec.uom);
353 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION7', l_input_value);
354 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(add_element_info_rec.value));
358
355 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
356
357 END LOOP;
359
360 FOR balance_info_rec IN csr_balance_info(p_assignment_action_id)
361 LOOP
362
363 load_xml('CS', NULL, 'BALANCE DETAILS', NULL);
364 load_xml('D', 'EMEA BALANCE DEFINITION', 'ACTION_INFORMATION2', balance_info_rec.defined_balance_id);
365 load_xml('D', 'EMEA BALANCE DEFINITION', 'ACTION_INFORMATION4', balance_info_rec.Name);
366 load_xml('D', 'EMEA BALANCE DEFINITION', 'ACTION_INFORMATION6', balance_info_rec.type);
367 load_xml('D', 'EMEA BALANCES', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(balance_info_rec.value));
368 load_xml('D', 'EMEA BALANCES', 'ACTION_INFORMATION6', balance_info_rec.uom);
369 load_xml('CE', NULL, 'BALANCE DETAILS', NULL);
370
371 END LOOP;
372
373 load_xml('CS', NULL, 'SUMMARY OF PAYMENTS', NULL);
374 load_xml('D', NULL, 'TOTAL_EARNINGS', l_total_earnings);
375 load_xml('D', NULL, 'TOTAL_DEDUCTIONS', l_total_deductions);
376 load_xml('D', NULL, 'TOTAL_PAY', l_total_pay);
377 load_xml('CE', NULL, 'SUMMARY OF PAYMENTS', NULL);
378
379
380
381
382 END IF;
383
384 END;
385
386 PROCEDURE element_template_post_process
387 (p_template_id IN NUMBER) AS
388 BEGIN
389
390 hr_utility.set_location('Entering: post process', 10);
391 pay_ae_element_template_pkg.element_template_post_process(p_template_id);
392 hr_utility.set_location('Leaving: post process', 20);
393 END element_template_post_process;
394
395 END PAY_AE_RULES;