eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_AE_RULES

Source


4        g_custom_context    pay_action_information.action_information_category%type;
1 PACKAGE BODY PAY_AE_RULES AS
2 /* $Header: pyaerule.pkb 120.1.12010000.3 2010/04/01 07:27:34 bkeshary ship $ */
3 
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 
86 
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
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,
171       ,sum(pai1.action_information4) value
168       pai.action_information4||'('||pai1.action_information8||')') Name
169       ,pai.action_information5 type
170       ,pai.action_information6 uom
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 /* Commented for Bug 9525527 */
228 /* GROUP BY pai.action_information2
229 	,pai.action_information3
230 	,pai.action_information4
231 	,pai.action_information5
232 	,pai.action_information6
233 	,pai1.action_information4
234 	,pai1.action_information8 */
235 ORDER BY pai.action_information4,pai.action_information5,pai1.action_information8 DESC;
236 
237 /* Cursor to fetch input value's name */
238 CURSOR csr_get_input_value (p_input_value_id NUMBER) IS
239 SELECT name
240 FROM pay_input_values_f
241 WHERE input_value_id = p_input_value_id;
242 
243 /* Cursor to fetch Balance Details */
244 CURSOR csr_balance_info(p_action_context_id   NUMBER) IS
245 SELECT pai.action_information4 Name
246       ,pai.action_information2 defined_balance_id
247       ,pai.action_information6 type
248       ,pai1.action_information4 value
249       ,pai1.action_information6 uom
250 FROM pay_action_information pai
251     ,pay_action_information pai1
252     ,pay_assignment_actions paa
253 WHERE pai.action_context_type       = 'PA'
254 AND pai.action_information_category = 'EMEA BALANCE DEFINITION'
255 AND pai1.action_context_type        = 'AAP'
256 AND pai1.action_information_category = 'EMEA BALANCES'
257 AND pai.action_information6 = 'OBAL'
258 AND pai1.action_information2 = 'OBAL'
259 AND pai.action_information2          = pai1.action_information1
260 AND pai.action_context_id            = paa.payroll_action_id
261 AND pai1.action_context_id           = paa.assignment_action_id
262 AND paa.assignment_action_id  in
263 			(SELECT paa1.assignment_action_id
264                         FROM pay_assignment_actions paa1
265 			WHERE paa1.source_action_id = p_action_context_id
266 			AND    paa1.action_status            = 'C'
267 			UNION
268 			SELECT p_action_context_id
269 			FROM dual )
270 ORDER BY pai.action_information5,pai1.action_information5 DESC;
271 
272 
273 l_xml            CLOB;
277 csr              NUMBER;
274 cntr_flex_col    NUMBER;
275 l_flex_col_num   NUMBER;
276 sqlstr           DBMS_SQL.VARCHAR2S;
278 ret              NUMBER;
279 l_cntr_sql       NUMBER;
280 l_total_pay      NUMBER;
281 l_total_earnings   NUMBER;
282 l_total_deductions NUMBER;
283 l_input_value    VARCHAR2(100);
284 
285 
286 PROCEDURE build_sql(p_sqlstr_tab    IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
287                     p_cntr          IN OUT NOCOPY NUMBER,
288                     p_string        VARCHAR2) AS
289     --
290     l_proc_name varchar2(100);
291     --
292 BEGIN
293     p_sqlstr_tab(p_cntr) := p_string;
294     p_cntr               := p_cntr + 1;
295 END;
296 
297    BEGIN
298 	l_flex_col_num := 30;
299 
300 	IF   p_action_information_category IS NULL AND p_document_type ='PAYSLIP' THEN
301 
302 		l_total_earnings:=0 ;
303 		l_total_deductions :=0;
304 		g_action_ctx_id     := p_assignment_action_id ;
305 
306 		FOR payroll_info_rec IN csr_payroll_info (p_assignment_action_id)
307 			LOOP
308 
309   				load_xml('CS', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
310 				load_xml('D', NULL, 'PAYROLL_NAME', payroll_info_rec.payroll_name );
311 				load_xml('D', NULL, 'PERIOD_NAME', payroll_info_rec.period_name);
312 				load_xml('D', NULL, 'PERIOD_TYPE', payroll_info_rec.period_type);
313 				load_xml('D', NULL, 'START_DATE', payroll_info_rec.start_date);
314 				load_xml('D', NULL, 'END_DATE', payroll_info_rec.end_date);
315 				load_xml('D', NULL, 'PAYMENT_DATE', payroll_info_rec.payment_date);
316 				load_xml('CE', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
317 
318 			END LOOP;
319     --
320   		FOR element_info_rec IN csr_element_info(p_assignment_action_id)
321 			LOOP
322 
323 				load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
324 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', element_info_rec.element_type_id);
325 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', element_info_rec.input_value_id);
326 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', element_info_rec.Name);
327 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', element_info_rec.type);
328 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', element_info_rec.uom);
329 				load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(element_info_rec.value));
330 				load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
331 
332 				       IF element_info_rec.type = 'E' THEN
333 					       l_total_earnings := fnd_number.canonical_to_number(l_total_earnings) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
334 				       ELSIF element_info_rec.type = 'D' THEN
335 					       l_total_deductions := fnd_number.canonical_to_number(l_total_deductions) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
336 				       END IF ;
337 					l_total_pay := l_total_earnings - l_total_deductions ;
338 
339 			END LOOP;
340 
341 		FOR add_element_info_rec IN csr_add_element_info(p_assignment_action_id)
342 			LOOP
343 
344 			OPEN csr_get_input_value(add_element_info_rec.input_value_id);
345 				FETCH csr_get_input_value INTO l_input_value;
346 			CLOSE csr_get_input_value;
347 
348 				load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
349 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', add_element_info_rec.element_type_id);
350 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', add_element_info_rec.input_value_id);
351 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', add_element_info_rec.Name);
352 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', add_element_info_rec.type);
353 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', add_element_info_rec.uom);
354 				load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION7', l_input_value);
355 				/* Bug 9242951 */
356 				IF add_element_info_rec.uom in ('N','M') THEN
357 				  load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(add_element_info_rec.value));
358 				ELSE
359 				  load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4',add_element_info_rec.value);
360 				END IF;
361 				load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
362 
363 			END LOOP;
364 
365 
366 		FOR balance_info_rec IN csr_balance_info(p_assignment_action_id)
367 			LOOP
368 
369 				load_xml('CS', NULL, 'BALANCE DETAILS', NULL);
370 				load_xml('D', 'EMEA BALANCE DEFINITION', 'ACTION_INFORMATION2', balance_info_rec.defined_balance_id);
371 				load_xml('D', 'EMEA BALANCE DEFINITION', 'ACTION_INFORMATION4', balance_info_rec.Name);
372 				load_xml('D', 'EMEA BALANCE DEFINITION', 'ACTION_INFORMATION6', balance_info_rec.type);
373 				load_xml('D', 'EMEA BALANCES', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(balance_info_rec.value));
374 				load_xml('D', 'EMEA BALANCES', 'ACTION_INFORMATION6', balance_info_rec.uom);
375 				load_xml('CE', NULL, 'BALANCE DETAILS', NULL);
376 
377 			END LOOP;
378 
379 			load_xml('CS', NULL, 'SUMMARY OF PAYMENTS', NULL);
380                         load_xml('D', NULL, 'TOTAL_EARNINGS', l_total_earnings);
381                         load_xml('D', NULL, 'TOTAL_DEDUCTIONS', l_total_deductions);
382                         load_xml('D', NULL, 'TOTAL_PAY', l_total_pay);
383 			load_xml('CE', NULL, 'SUMMARY OF PAYMENTS', NULL);
384 
385 
386 
387 
388 	END IF;
389 
390    END;
391 
392   PROCEDURE element_template_post_process
393     (p_template_id       IN NUMBER) AS
394   BEGIN
395 
396    hr_utility.set_location('Entering: post process',  10);
397     pay_ae_element_template_pkg.element_template_post_process(p_template_id);
401 END PAY_AE_RULES;398    hr_utility.set_location('Leaving: post process',  20);
399   END element_template_post_process;
400