[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_ELEMENT_TEMPLATE_PKG
Source
1 PACKAGE BODY pay_cn_element_template_pkg AS
2 /* $Header: pycneltp.pkb 120.0 2005/05/29 03:59 appldev noship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'pay_cn_element_template_pkg.';
5 g_debug BOOLEAN;
6 g_templates_setup t_results_setup_tab;
7 g_results_setup t_form_results_tab;
8 g_count NUMBER;
9
10 --------------------------------------------------------------------------
11 -- --
12 -- Name : GET_FORMULA_ID --
13 -- Type : PROCEDURE --
14 -- Access : Private --
15 -- Description : Procedure to a get the formula id based on the --
16 -- formula name and effective date --
17 -- --
18 -- Parameters : Inputs: p_formula_name VARCHAR2 --
19 -- p_effective_date DATE --
20 -- --
21 -- Change History : --
22 --------------------------------------------------------------------------
23 -- Rev# Date Userid Description --
24 --------------------------------------------------------------------------
25 -- 1.0 26-May-05 snekkala Created this Procedure --
26 --------------------------------------------------------------------------
27 FUNCTION get_formula_id
28 (p_effective_date IN VARCHAR2
29 ,p_formula_name IN VARCHAR2
30 )
31 RETURN NUMBER
32 IS
33 l_formula_id ff_formulas_f.formula_id%TYPE ;
34 BEGIN
35
36 SELECT formula_id
37 INTO l_formula_id
38 FROM ff_formulas_f
39 WHERE legislation_code = 'CN'
40 AND formula_name = p_formula_name
41 AND p_effective_date BETWEEN effective_start_Date AND effective_end_date;
42
43 RETURN l_formula_id;
44
45 END get_formula_id;
46
47 --------------------------------------------------------------------------
48 -- --
49 -- Name : INIT_CODE --
50 -- Type : PROCEDURE --
51 -- Access : Private --
52 -- Description : Procedure to a global structure with all the --
53 -- formula related meta-data --
54 -- --
55 -- Parameters : None --
56 -- --
57 -- Change History : --
58 --------------------------------------------------------------------------
59 -- Rev# Date Userid Description --
60 --------------------------------------------------------------------------
61 -- 1.0 21-Apr-05 snekkala Created this Procedure --
62 -- 1.1 06-May-05 snekkala Modified the procedure to remove Nested --
63 -- Tables --
64 --------------------------------------------------------------------------
65 PROCEDURE init_code
66 IS
67
68 BEGIN
69 --
70 -- Element Template Setup for Special Payments
71 --
72 g_templates_setup(1).template_name := 'Special Payments';
73 g_templates_setup(1).category := 'Special Payments';
74 g_templates_setup(1).formula_name := 'CN_SPECIAL_PAYMENTS';
75 g_templates_setup(1).status_rule_id := NULL;
76 g_templates_setup(1).fr_count := 11;
77 g_templates_setup(1).fr_set_index := 1;
78
79 g_results_setup(1).result_name := 'L_NORMAL_AMOUNT';
80 g_results_setup(1).result_rule_type := 'I';
81 g_results_setup(1).input_value_name := 'Pay Value';
82 g_results_setup(1).element_name := 'Special Payments Normal';
83 g_results_setup(1).severity_level := NULL;
84
85 g_results_setup(2).result_name := 'L_NORMAL_AMOUNT';
86 g_results_setup(2).result_rule_type := 'I';
87 g_results_setup(2).input_value_name := 'Process Normal Amount';
88 g_results_setup(2).element_name := 'Special Payments Normal';
89 g_results_setup(2).severity_level := NULL;
90
91 g_results_setup(3).result_name := 'L_JURISDICTION';
92 g_results_setup(3).result_rule_type := 'I';
93 g_results_setup(3).input_value_name := 'Jurisdiction';
94 g_results_setup(3).element_name := 'Special Payments Normal';
95 g_results_setup(3).severity_level := NULL;
96
97 g_results_setup(4).result_name := 'L_SEPARATE_AMOUNT';
98 g_results_setup(4).result_rule_type := 'I';
99 g_results_setup(4).input_value_name := 'Process Separate Amount';
100 g_results_setup(4).element_name := 'Special Payments Separate';
101 g_results_setup(4).severity_level := NULL;
102
103 g_results_setup(5).result_name := 'L_SEP_JURISDICTION';
104 g_results_setup(5).result_rule_type := 'I';
105 g_results_setup(5).input_value_name := 'Jurisdiction';
106 g_results_setup(5).element_name := 'Special Payments Separate';
107 g_results_setup(5).severity_level := NULL;
108
109 g_results_setup(6).result_name := 'L_SPREAD_AMOUNT';
110 g_results_setup(6).result_rule_type := 'I';
111 g_results_setup(6).input_value_name := 'Process Spread Amount';
112 g_results_setup(6).element_name := 'Special Payments Spread';
113 g_results_setup(6).severity_level := NULL;
114
115 g_results_setup(7).result_name := 'L_NUMBER_OF_PERIODS';
116 g_results_setup(7).result_rule_type := 'I';
117 g_results_setup(7).input_value_name := 'Number of Periods';
118 g_results_setup(7).element_name := 'Special Payments Spread';
119 g_results_setup(7).severity_level := NULL;
120
121 g_results_setup(8).result_name := 'L_BASE';
122 g_results_setup(8).result_rule_type := 'I';
123 g_results_setup(8).input_value_name := 'Base Value';
124 g_results_setup(8).element_name := 'Special Payments Spread';
125 g_results_setup(8).severity_level := NULL;
126
127 g_results_setup(9).result_name := 'L_SPR_JURISDICTION';
128 g_results_setup(9).result_rule_type := 'I';
129 g_results_setup(9).input_value_name := 'Jurisdiction';
130 g_results_setup(9).element_name := 'Special Payments Spread';
131 g_results_setup(9).severity_level := NULL;
132
133 g_results_setup(10).result_name := 'L_PAYMENT_AMOUNT';
134 g_results_setup(10).result_rule_type := 'D';
135 g_results_setup(10).input_value_name := 'Pay Value';
136 g_results_setup(10).element_name := NULL;
137 g_results_setup(10).severity_level := NULL;
138
139 g_results_setup(11).result_name := 'L_ERROR_MESSAGE';
140 g_results_setup(11).result_rule_type := 'M';
141 g_results_setup(11).input_value_name := NULL;
142 g_results_setup(11).element_name := NULL;
143 g_results_setup(11).severity_level := 'F';
144
145 --
146 -- Element Template Setup for yearly Tax Calculation
147 --
148 g_templates_setup(2).template_name := 'Variable Yearly Earnings';
149 g_templates_setup(2).category := 'Variable Yearly Earnings';
150 g_templates_setup(2).formula_name := 'CN_YRLY_EARNINGS_XFER';
151 g_templates_setup(2).status_rule_id := NULL;
152 g_templates_setup(2).fr_count := 5;
153 g_templates_setup(2).fr_set_index := 12;
154
155 g_results_setup(12).result_name := 'L_PREV_JURISDICTION';
156 g_results_setup(12).result_rule_type := 'I';
157 g_results_setup(12).input_value_name := 'Jurisdiction';
158 g_results_setup(12).element_name := 'Previous Year Variable Earnings';
159 g_results_setup(12).severity_level := NULL;
160
161 g_results_setup(13).result_name := 'L_PREV_YEAR_VARIABLE_EARNINGS';
162 g_results_setup(13).result_rule_type := 'I';
163 g_results_setup(13).input_value_name := 'Payment Amount';
164 g_results_setup(13).element_name := 'Previous Year Variable Earnings';
165 g_results_setup(13).severity_level := NULL;
166
167 g_results_setup(14).result_name := 'L_CURR_JURISDICTION';
168 g_results_setup(14).result_rule_type := 'I';
169 g_results_setup(14).input_value_name := 'Jurisdiction';
170 g_results_setup(14).element_name := 'Current Year Variable Earnings';
171 g_results_setup(14).severity_level := NULL;
172
173 g_results_setup(15).result_name := 'L_CURR_YEAR_VARIABLE_EARNINGS';
174 g_results_setup(15).result_rule_type := 'I';
175 g_results_setup(15).input_value_name := 'Payment Amount';
176 g_results_setup(15).element_name := 'Current Year Variable Earnings';
177 g_results_setup(15).severity_level := NULL;
178
179 g_results_setup(16).result_name := 'L_PAYMENT_AMOUNT';
180 g_results_setup(16).result_rule_type := 'D';
181 g_results_setup(16).input_value_name := 'Pay Value';
182 g_results_setup(16).element_name := NULL;
183 g_results_setup(16).severity_level := NULL;
184
185 g_count := 2;
186 END init_code;
187
188 --------------------------------------------------------------------------
189 -- --
190 -- Name : GET_RESULTS_SETUP --
191 -- Type : PROCEDURE --
192 -- Access : Private --
193 -- Description : Accepts the Template Name as input and return the --
194 -- Results Setup --
195 -- --
196 -- Parameters : None --
197 -- IN : p_template_name VARCHAR2 --
198 -- OUT : p_results_setup t_fr_setup_rec --
199 -- --
200 -- Change History : --
201 --------------------------------------------------------------------------
202 -- Rev# Date Userid Description --
203 --------------------------------------------------------------------------
204 -- 1.0 21-Apr-05 snekkala Created this Procedure --
205 -- 1.1 06-May-05 snekkala Modified the procedure to remove Nested --
206 -- Tables --
207 -- 1.2 26-May-05 snekkala Restructured the code. --
208 --------------------------------------------------------------------------
209 PROCEDURE get_results_setup
210 (p_template_name IN VARCHAR2
211 ,p_results_setup OUT NOCOPY t_fr_setup_rec
212 )
213 IS
214 l_procedure CONSTANT VARCHAR2(100):= g_package||'get_results_setup';
215
216 BEGIN
217 hr_cn_api.set_location(g_debug,'Entering : '||l_procedure,10);
218
219 init_code;
220
221 hr_cn_api.set_location(g_debug,l_procedure,20);
222
223 FOR i IN 1..g_count
224 LOOP
225
226 IF (g_templates_setup(i).template_name = p_template_name) THEN
227
228 hr_cn_api.set_location(g_debug,'CHINA: Found match '||i||' for Template '||p_template_name,25);
229
230 p_results_setup.template_name := g_templates_setup(i).template_name;
231 p_results_setup.category := g_templates_setup(i).category;
232 p_results_setup.formula_name := g_templates_setup(i).formula_name;
233 p_results_setup.fr_count := g_templates_setup(i).fr_count;
234 p_results_setup.status_rule_id := g_templates_setup(i).status_rule_id;
235 p_results_setup.fr_set_index := g_templates_setup(i).fr_set_index;
236
237 IF g_debug THEN
238 hr_utility.trace('CHINA: template_name : '||p_results_setup.template_name);
239 hr_utility.trace('CHINA: category : '||p_results_setup.category);
240 hr_utility.trace('CHINA: formula_name : '||p_results_setup.formula_name);
241 hr_utility.trace('CHINA: fr_count : '||p_results_setup.fr_count);
242 hr_utility.trace('CHINA: status_rule_id : '||p_results_setup.status_rule_id);
243 hr_utility.trace('CHINA: fr_set_index : '||p_results_setup.fr_set_index);
244 END IF ;
245 END IF;
246 END LOOP;
247
248 hr_cn_api.set_location(g_debug,'Leaving : '||l_procedure,30);
249
250 END get_results_setup;
251
252 --------------------------------------------------------------------------
253 -- --
254 -- Name : INS_FORM_RES_RULE --
255 -- Type : PROCEDURE --
256 -- Access : Private --
257 -- Description : Procedure to insert the formula result. --
258 -- --
259 -- Parameters : Inputs: p_business_group_id NUMBER --
260 -- p_effective_date DATE --
261 -- p_status_processing_rule_id NUMBER --
262 -- p_result_name VARCHAR2 --
263 -- p_result_rule_type VARCHAR2 --
264 -- p_element_name VARCHAR2 --
265 -- p_input_value_name VARCHAR2 --
266 -- p_severity_level VARCHAR2 --
267 -- p_element_type_id NUMBER --
268 -- --
269 -- Change History : --
270 --------------------------------------------------------------------------
271 -- Rev# Date Userid Description --
272 --------------------------------------------------------------------------
273 -- 1.0 26-May-05 snekkala Created this Procedure --
274 --------------------------------------------------------------------------
275 PROCEDURE ins_form_res_rule
276 (
277 p_business_group_id IN NUMBER,
278 p_effective_date IN DATE ,
279 p_status_processing_rule_id IN NUMBER,
280 p_result_name IN VARCHAR2,
281 p_result_rule_type IN VARCHAR2,
282 p_element_name IN VARCHAR2 DEFAULT NULL,
283 p_input_value_name IN VARCHAR2 DEFAULT NULL,
284 p_severity_level IN VARCHAR2 DEFAULT NULL,
285 p_element_type_id IN NUMBER DEFAULT NULL
286 )
287 IS
288
289 c_end_of_time CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
290 v_form_res_rule_id pay_formula_result_rules_f.formula_result_rule_id%TYPE;
291 l_input_value_id pay_formula_result_rules_f.input_value_id%TYPE;
292 l_element_type_id pay_element_types_f.element_type_id%TYPE;
293 BEGIN
294
295 IF p_result_rule_type = 'D' THEN
296
297 SELECT input_value_id
298 INTO l_input_value_id
299 FROM pay_input_values_f
300 WHERE p_effective_date BETWEEN effective_start_date
301 AND effective_end_date
302 AND business_group_id = p_business_group_id
303 AND element_type_id = p_element_type_id
304 AND NAME = p_input_value_name;
305
306 ELSIF p_result_rule_type = 'I' THEN
307
308 SELECT piv.input_value_id
309 , pet.element_type_id
310 INTO l_input_value_id
311 , l_element_type_id
312 FROM pay_input_values_f piv, pay_element_types_f pet
313 WHERE p_effective_date BETWEEN piv.effective_start_date
314 AND piv.effective_end_date
315 AND p_effective_date BETWEEN pet.effective_start_date
316 AND pet.effective_end_date
317 AND piv.legislation_code = 'CN'
318 AND pet.legislation_code = 'CN'
319 AND pet.element_name = p_element_name
320 AND piv.NAME = p_input_value_name
321 AND pet.element_type_id = piv.element_type_id;
322
323 END IF;
324
325 SELECT pay_formula_result_rules_s.nextval
326 INTO v_form_res_rule_id
327 FROM sys.dual;
328
329 INSERT INTO pay_formula_result_rules_f
330 (formula_result_rule_id,
331 effective_start_date,
332 effective_end_date,
333 business_group_id,
334 status_processing_rule_id,
335 result_name,
336 result_rule_type,
337 severity_level,
338 input_value_id,
339 last_update_date,
340 last_updated_by,
341 last_update_login,
342 created_by,
343 creation_date,
344 element_type_id)
345 VALUES
346 (v_form_res_rule_id,
347 p_effective_date,
348 c_end_of_time,
349 p_business_group_id,
350 p_status_processing_rule_id,
351 upper(p_result_name),
352 p_result_rule_type,
353 p_severity_level,
354 l_input_value_id,
355 trunc(sysdate),
356 -1,
357 -1,
358 -1,
359 trunc(sysdate),
360 decode(p_result_rule_type,'I',l_element_type_id,null));
361
362 END ins_form_res_rule;
363
364 --------------------------------------------------------------------------
365 -- --
366 -- Name : ELEMENT_TEMPLATE_POST_PROCESS --
367 -- Type : PROCEDURE --
368 -- Access : Public --
369 -- Description : Accepts the Template Name as input and return the --
370 -- Results Setup --
371 -- --
372 -- Parameters : None --
373 -- IN : p_template_id NUMBER --
374 -- --
375 -- Change History : --
376 --------------------------------------------------------------------------
377 -- Rev# Date Userid Description --
378 --------------------------------------------------------------------------
379 -- 1.0 21-Apr-05 snekkala Created this Procedure --
380 -- 1.0 06-May-05 snekkala Modified the code to remove nested tables --
381 --------------------------------------------------------------------------
382 PROCEDURE element_template_post_process
383 (p_template_id IN NUMBER)
384 IS
385
386 CURSOR csr_template IS
387 SELECT template_name
388 FROM pay_element_templates
389 WHERE template_id = p_template_id;
390
391 l_template_name pay_element_templates.template_name%TYPE;
392
393 CURSOR csr_set IS
394 SELECT pet.element_type_id
395 ,pet.business_group_id
396 ,pet.effective_start_date
397 ,pet.object_version_number
398 FROM pay_element_types_f pet
399 ,pay_element_templates tmp
400 WHERE pet.element_name = tmp.base_name
401 AND tmp.template_id = p_template_id;
402
403 l_element_type_id pay_element_types.element_type_id%TYPE;
404 l_business_group_id pay_element_types.business_group_id%TYPE;
405 l_effective_date DATE;
406 l_element_ovn pay_element_types_f.object_version_number%TYPE;
407
408 l_st_start_date DATE ;
409 l_st_end_date DATE ;
410 l_st_ovn pay_status_processing_rules_f.object_version_number%TYPE;
411 l_st_warn BOOLEAN ;
412 l_results_setup t_fr_setup_rec;
413 l_formula_setup t_form_results_tab;
414
415 l_procedure VARCHAR2 (100);
416 j PLS_INTEGER ;
417
418 BEGIN
419
420 g_debug := hr_utility.debug_enabled;
421 l_procedure := g_package ||'element_template_post_process';
422
423
424 hr_cn_api.set_location(g_debug,'Entering : '||l_procedure,10);
425 OPEN csr_template;
426 FETCH csr_template
427 INTO l_template_name;
428 CLOSE csr_template;
429
430 hr_cn_api.set_location(g_debug,l_procedure,20);
431 IF g_debug THEN
432 hr_utility.trace ('CHINA: Template Name : '||l_template_name);
433 END IF ;
434
435 get_results_setup
436 (p_template_name => l_template_name
437 ,p_results_setup => l_results_setup
438 );
439
440
441 hr_cn_api.set_location(g_debug,l_procedure,30);
442 OPEN csr_set;
443 FETCH csr_set
444 INTO l_element_type_id
445 ,l_business_group_id
446 ,l_effective_date
447 ,l_element_ovn;
448 CLOSE csr_set;
449
450 hr_cn_api.set_location(g_debug,l_procedure,40);
451
452 pay_status_processing_rule_api.create_status_process_rule
453 (
454 p_effective_date => l_effective_date
455 ,p_element_type_id => l_element_type_id
456 ,p_business_group_id => l_business_group_id
457 ,p_formula_id => get_formula_id(l_effective_date, l_results_setup.formula_name)
458 ,p_status_processing_rule_id => l_results_setup.status_rule_id
459 ,p_effective_start_date => l_st_start_date
460 ,p_effective_end_date => l_st_end_date
461 ,p_object_version_number => l_st_ovn
462 ,p_formula_mismatch_warning => l_st_warn
463 );
464
465 hr_cn_api.set_location(g_debug,l_procedure,50);
466
467 FOR i IN 1..l_results_setup.fr_count
468 LOOP
469
470 hr_cn_api.set_location(g_debug,l_procedure||'--'||i,55);
471
472 j := l_results_setup.fr_set_index+i-1;
473 hr_utility.trace('CHINA: Value of i is '||i);
474 hr_utility.trace('CHINA: Value of j is '||j);
475
476 l_formula_setup(i).result_name := g_results_setup(j).result_name ;
477 l_formula_setup(i).result_rule_type := g_results_setup(j).result_rule_type;
478 l_formula_setup(i).input_value_name := g_results_setup(j).input_value_name ;
479 l_formula_setup(i).element_name := g_results_setup(j).element_name ;
480 l_formula_setup(i).severity_level := g_results_setup(j).severity_level ;
481
482 IF g_debug THEN
483 hr_utility.trace('CHINA: result_name : '||l_formula_setup(i).result_name);
484 hr_utility.trace('CHINA: result_rule_type : '||l_formula_setup(i).result_rule_type);
485 hr_utility.trace('CHINA: input_value_name : '||l_formula_setup(i).input_value_name);
486 hr_utility.trace('CHINA: element_name : '||l_formula_setup(i).element_name);
487 hr_utility.trace('CHINA: severity_level : '||l_formula_setup(i).severity_level);
488 END IF;
489
490 ins_form_res_rule
491 (
492 p_business_group_id => l_business_group_id
493 ,p_effective_date => l_effective_date
497 ,p_result_name => l_formula_setup(i).result_name
494 ,p_status_processing_rule_id => l_results_setup.status_rule_id
495 ,p_input_value_name => l_formula_setup(i).input_value_name
496 ,p_element_name => l_formula_setup(i).element_name
498 ,p_result_rule_type => l_formula_setup(i).result_rule_type
499 ,p_severity_level => l_formula_setup(i).severity_level
500 ,p_element_type_id => l_element_type_id
501 );
502
503 END LOOP;
504
505 hr_cn_api.set_location(g_debug,'Leaving : '||l_procedure,60);
506
507 END element_template_post_process;
508
509 END pay_cn_element_template_pkg;