1 PACKAGE BODY pay_ins_custom_template AS
2 /* $Header: payinscstmplt.pkb 120.0.12010000.2 2008/10/15 10:19:58 kagangul noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name PAY_INS_CUSTOM_TEMPLATE
9
10 File payinscstmplt.pkb
11
12 Purpose The purpose of this package is to register the user defined custom
13 Templates into Payroll Tables i.e. PAY_REPORT_CATEGORUES,
14 PAY_REPORT_CATEGORY_COMPONENTS AND PAY_REPORT_VARIABLES.
15
16 Notes Currently this procedure supports the following concurrent programs
17 for which user defined custom templates can be registered :
18 1. Local Year End Interface Extract
19 2. Employee W-2 PDF
20 3. 1099R Information Return - PDF
21 4. Check Writer (XML)
22 5. Deposit Advice (XML)
23 6. RL1 PDF
24 7. RL2 PDF
25 Whenever any new concurrent programs is required to be added in this
26 category i.e if any new conc programs is decided to have the flexibility
27 of registering custom template, please edit the function GET_NAME.
28 If the Concurrent program's short name differs from the corresponding
29 data_source_code in table xdo_templates_b, this function needs one
30 'elsif' clause to be added for that new concurrent program.
31
32 Change History
33
34 Date User Id Version Description
35 ============================================================================
36 01-Sep-08 kagangul 115.0 Initial Version Created
37 01-Oct-08 kagangul 115.4 Modified the Cursor csr_report_group_id
38 to consider those Report Groups which
39 are seeded by Core Payroll
40 i.e. Legislation Code is NULL.
41 ============================================================================*/
42
43 DEBUG_MODE BOOLEAN := FALSE;
44
45 PROCEDURE insert_custom_template(errbuf out nocopy VARCHAR2,
46 retcode out nocopy NUMBER,
47 p_conc_prog IN VARCHAR2,
48 p_lookup_type_name IN VARCHAR2,
49 p_business_group_id IN NUMBER) IS
50
51 CURSOR csr_get_temp_codes(p_lookup_type IN VARCHAR) IS
52 SELECT lookup_code, meaning
53 FROM fnd_common_lookups
54 WHERE lookup_type = p_lookup_type
55 AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name = 'PAY')
56 AND sysdate between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate)
57 AND enabled_flag = 'Y';
58
59 CURSOR csr_temp_code_dtls(p_template_code IN VARCHAR,
60 p_concurrent_prog IN VARCHAR) IS
61 SELECT xtl.template_code, xtl.template_name, xb.template_type_code
62 FROM xdo_templates_b xb, xdo_templates_tl xtl
63 WHERE xb.template_code = xtl.template_code
64 AND xb.application_short_name = xtl.application_short_name
65 AND xtl.application_short_name = 'PAY'
66 AND xb.template_status = 'E'
67 AND xtl.language = USERENV('LANG')
68 /*AND xb.data_source_code = decode(p_concurrent_prog,'LOCALW2XML','LOCALW2MAG',
69 'PAYUSW2PDF','PAYUSW2',
70 'EMP_1099R_PDF','PAYUS1099R',p_conc_prog)*/
71 AND xb.data_source_code = GET_NAME(p_concurrent_prog)
72 AND xtl.template_code = p_template_code;
73
74 CURSOR csr_report_group_id(p_report_group_short_name IN VARCHAR,
75 p_legislation_code IN VARCHAR) IS
76 SELECT report_group_id
77 FROM pay_report_groups
78 WHERE short_name = p_report_group_short_name
79 AND business_group_id IS NULL
80 /*AND nvl(legislation_code,'US') = nvl(p_legislation_code,'US');*/
81 AND ((legislation_code = p_legislation_code) OR (legislation_code IS NULL));
82
83 CURSOR csr_report_category_id(p_report_group_id IN NUMBER,
84 p_category_short_name IN VARCHAR2,
85 p_business_group_id IN NUMBER
86 ) IS
87 SELECT report_category_id
88 FROM pay_report_categories
89 WHERE report_group_id = p_report_group_id
90 AND short_name = p_category_short_name
91 AND business_group_id = p_business_group_id
92 AND legislation_code IS NULL;
93
94 CURSOR csr_report_definition_id(p_report_group_id NUMBER) IS
95 SELECT report_definition_id
96 FROM pay_report_definitions
97 WHERE report_group_id = p_report_group_id;
98
99 CURSOR csr_report_cat_comp_id(p_report_category_id IN NUMBER,
100 p_report_definition_id IN NUMBER,
101 p_business_group_id IN NUMBER) IS
102 SELECT style_sheet_variable_id
103 FROM pay_report_category_components
104 WHERE report_category_id = p_report_category_id
105 AND report_definition_id = p_report_definition_id
106 AND business_group_id = p_business_group_id
107 AND legislation_code IS NULL;
108
109 CURSOR csr_report_variable_id(p_report_variable_id IN NUMBER,
110 p_business_group_id IN NUMBER) IS
111 SELECT report_variable_id
112 FROM pay_report_variables
113 WHERE report_variable_id = p_report_variable_id
114 AND business_group_id = p_business_group_id
115 AND legislation_code IS NULL;
116
117 lv_lookup_code fnd_common_lookups.lookup_code%TYPE;
118 lv_meaning fnd_common_lookups.meaning%TYPE;
119 lv_template_code xdo_templates_tl.template_code%TYPE;
120 lv_template_name xdo_templates_tl.template_name%TYPE;
121 lv_template_type_code xdo_templates_b.template_type_code%TYPE;
122 lv_rg_short_name fnd_common_lookups.description%TYPE;
123 lv_legislation_code hr_organization_information.org_information9%TYPE;
124 ln_report_group_id pay_report_groups.report_group_id%TYPE;
125 ln_report_category_id pay_report_categories.report_category_id%TYPE;
126 ln_report_category_id_new pay_report_categories.report_category_id%TYPE;
127 ln_report_definition_id pay_report_definitions.report_definition_id%TYPE;
128 ln_definition_id pay_report_definitions.report_definition_id%TYPE;
129 ln_style_sheet_id pay_report_category_components.style_sheet_variable_id%TYPE;
130 ln_report_variable_id pay_report_variables.report_variable_id%TYPE;
131 pn_report_variable_id pay_report_variables.report_variable_id%TYPE;
132 lv_lookup_type_meaning fnd_common_lookup_types.lookup_type_meaning%TYPE;
133 pn_report_category_comp_id pay_report_category_components.report_category_comp_id%TYPE;
134
135 BEGIN
136
137 fnd_file.put_line(fnd_file.log,'Starting ....');
138 lv_legislation_code := get_legislation_code(p_business_group_id);
139 fnd_file.put_line(fnd_file.log,'Business Group Id : ' || p_business_group_id);
140 fnd_file.put_line(fnd_file.log,'Legislation Code : ' || lv_legislation_code);
141
142 BEGIN
143 SELECT description INTO lv_rg_short_name
144 FROM fnd_common_lookups
145 WHERE lookup_type = 'GEN_CUST_TEMP_CONC_PROGS'
146 AND lookup_code = p_conc_prog
147 AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name = 'PAY')
148 AND sysdate between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate)
149 AND enabled_flag = 'Y';
150 fnd_file.put_line(fnd_file.log,'Report Group Short Name : ' || lv_rg_short_name);
151 EXCEPTION
152 WHEN OTHERS THEN
153 fnd_file.put_line(fnd_file.log,'Report Group Not Found : Please Contact Your Support Representative');
154 END;
155
156 BEGIN
157 SELECT lookup_type_meaning INTO lv_lookup_type_meaning
158 FROM fnd_common_lookup_types
159 WHERE lookup_type = p_lookup_type_name;
160 fnd_file.put_line(fnd_file.log,'+---------------------------------------------------------------------------+');
161 fnd_file.put_line(fnd_file.log,'New Category Name : ' || lv_lookup_type_meaning);
162 EXCEPTION
163 WHEN OTHERS THEN
164 fnd_file.put_line(fnd_file.log,'Report Category Not Found : Please Contact Your Support Representative');
165 END;
166
167 SELECT pay_report_categories_s.nextval INTO ln_report_category_id_new
168 FROM DUAL;
169 fnd_file.put_line(fnd_file.log,'New Category Id : ' || ln_report_category_id_new);
170 fnd_file.put_line(fnd_file.log,'+---------------------------------------------------------------------------+');
171
172 OPEN csr_get_temp_codes(p_lookup_type_name);
173 LOOP
174
175 FETCH csr_get_temp_codes INTO lv_lookup_code,lv_meaning;
176 EXIT WHEN csr_get_temp_codes%NOTFOUND;
177 fnd_file.put_line(fnd_file.log,'+---------------------------------------------------------------------------+');
178 fnd_file.put_line(fnd_file.log,'Registering Template : ' || lv_lookup_code);
179 OPEN csr_temp_code_dtls(lv_lookup_code,p_conc_prog);
180 FETCH csr_temp_code_dtls INTO lv_template_code, lv_template_name, lv_template_type_code;
181
182 IF csr_temp_code_dtls%FOUND THEN
183 fnd_file.put_line(fnd_file.log,'Template Name : ' || lv_template_name);
184 fnd_file.put_line(fnd_file.log,'Template Type : ' || lv_template_type_code);
185 OPEN csr_report_group_id(lv_rg_short_name,lv_legislation_code);
186 FETCH csr_report_group_id INTO ln_report_group_id;
187
188 IF csr_report_group_id%FOUND THEN
189 fnd_file.put_line(fnd_file.log,'Report Group Id : ' || ln_report_group_id);
190 IF DEBUG_MODE THEN
191 dbms_output.put_line('Before Opening Category Cursor : csr_report_category_id');
192 dbms_output.put_line('ln_report_group_id='||ln_report_group_id);
193 dbms_output.put_line('p_lookup_type_name='||p_lookup_type_name);
194 dbms_output.put_line('p_business_group_id='||p_business_group_id);
195 END IF;
196
197 OPEN csr_report_category_id(ln_report_group_id,p_lookup_type_name,p_business_group_id);
198 FETCH csr_report_category_id INTO ln_report_category_id;
199
200 IF DEBUG_MODE THEN
201 dbms_output.put_line('After Opening Category Cursor : csr_report_category_id');
202 dbms_output.put_line('ln_report_category_id='||ln_report_category_id);
203 END IF;
204
205 IF csr_report_category_id%FOUND THEN
206
207 OPEN csr_report_definition_id(ln_report_group_id);
208 LOOP
209 FETCH csr_report_definition_id INTO ln_report_definition_id;
210 EXIT WHEN csr_report_definition_id%NOTFOUND;
211
212 OPEN csr_report_cat_comp_id(ln_report_category_id,ln_report_definition_id,p_business_group_id);
213 FETCH csr_report_cat_comp_id INTO ln_style_sheet_id;
214 IF csr_report_cat_comp_id%FOUND THEN
215 fnd_file.put_line(fnd_file.log,'Removing Template For Definition Id : ' || ln_report_definition_id);
216 fnd_file.put_line(fnd_file.log,'Removing Template For Category Id : ' || ln_report_category_id);
217 fnd_file.put_line(fnd_file.log,'Removing Style Sheet Id : ' || ln_style_sheet_id);
218 OPEN csr_report_variable_id(ln_style_sheet_id,p_business_group_id);
219 FETCH csr_report_variable_id INTO ln_report_variable_id;
220 IF csr_report_variable_id%FOUND THEN
221 fnd_file.put_line(fnd_file.log,'Removing Variable Id : ' || ln_style_sheet_id);
222 IF DEBUG_MODE THEN
223 dbms_output.put_line('DELETE FROM pay_report_variables WHERE report_variable_id = ' || ln_report_variable_id);
224 ELSE
225 DELETE FROM pay_report_variables
226 WHERE report_variable_id = ln_report_variable_id;
227 END IF;
228 END IF;
229 CLOSE csr_report_variable_id;
230 IF DEBUG_MODE THEN
231 dbms_output.put_line('DELETE FROM pay_report_category_components WHERE style_sheet_variable_id = ' || ln_style_sheet_id);
232 ELSE
233 DELETE FROM pay_report_category_components
234 WHERE style_sheet_variable_id = ln_style_sheet_id;
235 END IF;
236 END IF;
237 CLOSE csr_report_cat_comp_id;
238 END LOOP;
239 CLOSE csr_report_definition_id;
240 END IF;
241 CLOSE csr_report_category_id;
242
243 ln_definition_id := get_definition_id(ln_report_group_id,lv_template_type_code,lv_template_code);
244
245 fnd_file.put_line(fnd_file.log,'+---------------------------------------------------------------------------+');
246 fnd_file.put_line(fnd_file.log,'Inserting Record Into PAY_REPORT_VARIABLES');
247 fnd_file.put_line(fnd_file.log,'Definition Id ' || ln_definition_id);
248 fnd_file.put_line(fnd_file.log,'Template Name ' || lv_template_name);
249 fnd_file.put_line(fnd_file.log,'Template Code ' || lv_template_code);
250 fnd_file.put_line(fnd_file.log,'Business Group Id ' || p_business_group_id);
251
252 insert_report_variable(p_report_definition_id => ln_definition_id,
253 p_definition_type => 'SS',
254 p_name => lv_template_name,
255 p_value => lv_template_code,
256 p_business_group_id => p_business_group_id,
257 p_report_variable_id => pn_report_variable_id);
258
259 fnd_file.put_line(fnd_file.log,'Report Variable Id ' || pn_report_variable_id);
260
261 fnd_file.put_line(fnd_file.log,'Inserting Record Into PAY_REPORT_CATEGORY_COMPONENTS');
262 fnd_file.put_line(fnd_file.log,'Category Id ' || ln_report_category_id_new);
263 fnd_file.put_line(fnd_file.log,'Definition Id ' || ln_definition_id);
264 fnd_file.put_line(fnd_file.log,'Style Sheet Id ' || pn_report_variable_id);
265 fnd_file.put_line(fnd_file.log,'Business Group Id ' || p_business_group_id);
266
267 insert_report_catg_comp(p_report_category_id => ln_report_category_id_new,
268 p_report_definition_id => ln_definition_id,
269 p_breakout_variable_id => NULL,
270 p_order_by_variable_id => NULL,
271 p_style_sheet_variable_id => pn_report_variable_id,
272 p_business_group_id => p_business_group_id,
273 p_report_category_comp_id => pn_report_category_comp_id);
274
275 fnd_file.put_line(fnd_file.log,'Category Component Id ' || pn_report_category_comp_id);
276
277 END IF;
278 CLOSE csr_report_group_id;
279 END IF;
280 CLOSE csr_temp_code_dtls;
281 END LOOP;
282 CLOSE csr_get_temp_codes;
283
284 IF DEBUG_MODE THEN
285 dbms_output.put_line('DELETE FROM pay_report_categories WHERE report_category_id = ' || ln_report_category_id);
286 ELSE
287 DELETE FROM pay_report_categories
288 WHERE report_category_id = ln_report_category_id;
289 END IF;
290
291 fnd_file.put_line(fnd_file.log,'+---------------------------------------------------------------------------+');
292 fnd_file.put_line(fnd_file.log,'Inserting Record Into PAY_REPORT_CATEGORIES');
293 fnd_file.put_line(fnd_file.log,'Report Group Id ' || ln_report_group_id);
294 fnd_file.put_line(fnd_file.log,'Category Name ' || lv_lookup_type_meaning);
295 fnd_file.put_line(fnd_file.log,'Category Short Name ' || p_lookup_type_name);
296 fnd_file.put_line(fnd_file.log,'Business Group Id ' || p_business_group_id);
297 fnd_file.put_line(fnd_file.log,'Category Id ' || ln_report_category_id_new);
298
299 insert_report_category(p_report_group_id => ln_report_group_id,
300 p_category_name => lv_lookup_type_meaning,
301 p_short_name => p_lookup_type_name,
302 p_legislation_code => NULL,
303 p_business_group_id => p_business_group_id,
304 p_report_category_id => ln_report_category_id_new);
305
306 fnd_file.put_line(fnd_file.log,'Template Registered Successfully');
307
308 END insert_custom_template;
309
310 FUNCTION get_legislation_code(p_business_group_id NUMBER)
311 RETURN VARCHAR2 IS
312
313 lv_legislation_code hr_organization_information.org_information9%TYPE;
314
315 BEGIN
316
317 BEGIN
318 SELECT org_information9 INTO lv_legislation_code
319 FROM hr_organization_information
320 WHERE org_information_context = 'Business Group Information'
321 AND organization_id = p_business_group_id;
322 EXCEPTION
323 WHEN OTHERS THEN
324 lv_legislation_code := NULL;
325 END;
326
327 RETURN lv_legislation_code;
328
329 END get_legislation_code;
330
331 FUNCTION get_definition_id(pn_report_group_id NUMBER, pv_template_type_code VARCHAR2,
332 pv_template_code VARCHAR2)
333 RETURN NUMBER IS
334
335 CURSOR csr_fetch_definition_id_one IS
336 SELECT report_definition_id
337 FROM pay_report_definitions
338 WHERE report_group_id = pn_report_group_id;
339
340 CURSOR csr_fetch_definition_id_mul IS
341 SELECT report_definition_id
342 FROM pay_report_definitions
343 WHERE report_group_id = pn_report_group_id
344 AND upper(report_name) LIKE '%' || upper(substr(pv_template_code,(instr(pv_template_code,'_',-1) + 1),(length(pv_template_code) - instr(pv_template_code,'_',-1)))) || '%';
345 --AND report_type = decode(pv_template_type_code,'ETEXT','EFT',pv_template_type_code);
346
347 ln_report_definition_id pay_report_definitions.report_definition_id%TYPE;
348 ln_tot_report_definitions NUMBER;
349
350 BEGIN
351
352 SELECT count(*) INTO ln_tot_report_definitions
353 FROM pay_report_definitions
354 WHERE report_group_id = pn_report_group_id;
355
356 IF ln_tot_report_definitions = 1 THEN
357 OPEN csr_fetch_definition_id_one;
358 FETCH csr_fetch_definition_id_one INTO ln_report_definition_id;
359
360 IF csr_fetch_definition_id_one%FOUND THEN
361 RETURN ln_report_definition_id;
362 END IF;
363 CLOSE csr_fetch_definition_id_one;
364 ELSE
365 OPEN csr_fetch_definition_id_mul;
366 FETCH csr_fetch_definition_id_mul INTO ln_report_definition_id;
367
368 IF csr_fetch_definition_id_mul%FOUND THEN
369 RETURN ln_report_definition_id;
370 END IF;
371 CLOSE csr_fetch_definition_id_mul;
372 END IF;
373
374 END get_definition_id;
375
376
377 PROCEDURE insert_report_variable(p_report_definition_id NUMBER,
378 p_definition_type VARCHAR2,
379 p_name VARCHAR2,
380 p_value VARCHAR2,
381 p_business_group_id NUMBER,
382 p_report_variable_id out nocopy NUMBER) IS
383
384 l_proc_name VARCHAR2(50);
385 BEGIN
386
390
387 l_proc_name := 'INSERT_REPORT_VARIABLE';
388 hr_utility.trace('Entering '||l_proc_name);
389 hr_utility.trace('Inserting report variable '|| p_name);
391 SELECT pay_report_variables_s.nextval INTO p_report_variable_id FROM DUAL;
392
393 IF DEBUG_MODE THEN
394 dbms_output.put_line('INSERT INTO pay_report_variables(report_variable_id,report_definition_id,definition_type,name,value,legislation_code,business_group_id) ' ||
395 'VALUES(' ||p_report_variable_id||','||p_report_definition_id||','||p_definition_type||','||p_name||','||p_value||','||NULL||','||p_business_group_id||')');
396 ELSE
397 INSERT INTO pay_report_variables(report_variable_id,
398 report_definition_id,
399 definition_type,
400 name,
401 value,
402 legislation_code,
403 business_group_id)
404 VALUES(p_report_variable_id,
405 p_report_definition_id,
406 p_definition_type,
407 p_name,
408 p_value,
409 NULL,
410 p_business_group_id);
411 END IF;
412
413 hr_utility.trace('Leaving '||l_proc_name);
414
415 END insert_report_variable;
416
417 PROCEDURE insert_report_catg_comp(p_report_category_id NUMBER,
418 p_report_definition_id NUMBER,
419 p_breakout_variable_id NUMBER,
420 p_order_by_variable_id NUMBER,
421 p_style_sheet_variable_id NUMBER,
422 p_business_group_id NUMBER,
423 p_report_category_comp_id out nocopy NUMBER) IS
424
425 l_proc_name VARCHAR2(50);
426
427 BEGIN
428
429 l_proc_name := 'INSERT_REPORT_CATEGORY_COMPONENT';
430 hr_utility.trace('Entering '||l_proc_name);
431 hr_utility.trace('Deleting report category component.');
432
433 SELECT pay_report_category_comp_s.nextval INTO p_report_category_comp_id FROM DUAL;
434
435 hr_utility.trace('Inserting report category component.');
436
437 IF DEBUG_MODE THEN
438 dbms_output.put_line('INSERT INTO PAY_REPORT_CATEGORY_COMPONENTS(report_category_comp_id,report_category_id,report_definition_id,breakout_variable_id,order_by_variable_id,style_sheet_variable_id,legislation_code,business_group_id) ' ||
439 'VALUES (' ||p_report_category_comp_id||','||p_report_category_id||','||p_report_definition_id||','||p_breakout_variable_id||','||p_order_by_variable_id||','||p_style_sheet_variable_id||','||NULL||','||
440 p_business_group_id||');');
441 ELSE
442 INSERT INTO PAY_REPORT_CATEGORY_COMPONENTS(report_category_comp_id,
443 report_category_id,
444 report_definition_id,
445 breakout_variable_id,
446 order_by_variable_id,
447 style_sheet_variable_id,
448 legislation_code,
449 business_group_id)
450 VALUES (p_report_category_comp_id,
451 p_report_category_id,
452 p_report_definition_id,
453 p_breakout_variable_id,
454 p_order_by_variable_id,
455 p_style_sheet_variable_id,
456 NULL,
457 p_business_group_id);
458 END IF;
459
460 hr_utility.trace('Leaving '||l_proc_name);
461
462 END insert_report_catg_comp;
463
464 PROCEDURE insert_report_category(p_report_group_id IN NUMBER,
465 p_category_name IN VARCHAR2,
466 p_short_name IN VARCHAR2,
467 p_legislation_code IN VARCHAR2,
468 p_business_group_id IN NUMBER,
469 p_report_category_id IN NUMBER) IS
470
471 l_proc_name VARCHAR2(50);
472
473 BEGIN
474
475 l_proc_name := 'INSERT_REPORT_CATEGORY';
476 hr_utility.trace('Entering '||l_proc_name);
477 hr_utility.trace('Inserting report category '|| p_short_name);
478
479 IF DEBUG_MODE THEN
480 dbms_output.put_line('INSERT INTO pay_report_categories(report_category_id,report_group_id,category_name,short_name,legislation_code,business_group_id) ' ||
481 'VALUES ('||p_report_category_id||','||p_report_group_id||','||p_category_name||','||p_short_name||','||p_legislation_code||','||p_business_group_id||');');
482 ELSE
483 INSERT INTO pay_report_categories(report_category_id,
484 report_group_id,
485 category_name,
486 short_name,
487 legislation_code,
488 business_group_id)
489 VALUES (p_report_category_id,
490 p_report_group_id,
491 p_category_name,
492 p_short_name,
493 p_legislation_code,
494 p_business_group_id);
495 END IF;
496
497 hr_utility.trace('Leaving '||l_proc_name);
498
499 END insert_report_category;
500
501 FUNCTION GET_NAME(p_conc_prog IN VARCHAR2)
502 -- This function accepts the short_name of a concurrent program
503 -- and returns the corresponding data_source_code in table
504 -- xdo_templates_b
505 RETURN VARCHAR2
506 IS
507
508 BEGIN
509
510 IF p_conc_prog = 'LOCALW2XML' THEN
511 RETURN 'LOCALW2MAG';
512 ELSIF p_conc_prog = 'PAYUSW2PDF' THEN
513 RETURN 'PAYUSW2';
514 ELSIF p_conc_prog = 'EMP_1099R_PDF' THEN
515 RETURN 'PAYUS1099R';
516 ELSE
517 RETURN p_conc_prog;
518 END IF;
519
520 END GET_NAME;
521
522 END pay_ins_custom_template;
[Home] [Help]