[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_DBI_ROE
Source
1 package body pay_ca_dbi_roe as
2 /* $Header: pycamagd.pkb 120.2 2011/12/21 04:26:00 emunisek ship $ */
3
4 /*===================================================================+
5 | Copyright (c) 1995 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +====================================================================+
9
10 Date Ver Name Description
11 ---- ---- -----------
12 27-AUG-1999 110.0 P. Ganguly Created.
13 23-NOV-1999 110.1 jgoswami Added function Create_format_item
14 29-NOV-1999 110.2 jgoswami Added code for setting
15 Update_flag = 'N'
16 in Create_format_item function
17 30-NOV-1999 110.3 jgoswami Update = 'N' for
18 ROE_TAX_UNIT_CITY
19 17-FEB-2000 115.0 P.Ganguly Changed the date format for
20 11i.
21 10-APR-2000 115.1 P.Ganguly Changed the route create
22 function, it updates if route
23 already exists (route text).
24 14-MAY-2000 115.2 P.Ganguly Changed the report_format_item
25 procedure.
26 29-JUN-2000 115.3 P.Ganguly Corrected the procedure to
27 create_user_entities, if the
28 user_entity already exists and
29 creator_type is 'SEH' then
30 update it to 'X', else create a
31 new one with 'X'.
32 15-JAN-2004 115.4 P.Ganguly 3353868 Fixed the statement which was
33 flagged in the 11.5.9 Drop 18
34 perf Repository.
35 29-Mar-2006 115.5 ssmukher 5042797 Fixed the performance issue by
36 removing the Full table scan
37 from the cursor query cur_database_item_exists.
38 Removed the use of UPPER function from the cursor query.
39 13-Dec-2011 115.6 emunisek 13484606 Changed parameter specification to
40 FF_ROUTES.TEXT%TYPE
41 ====================================================================*/
42
43 function create_route(
44 p_route_name varchar2,
45 p_description varchar2,
46 p_text ff_routes.text%TYPE) return number is
47 begin
48
49 declare
50
51 cursor cur_route_exists is
52 select route_id from ff_routes
53 where route_name = p_route_name;
54
55 cursor cur_ff_routes_s is
56 select ff_routes_s.nextval
57 from dual;
58
59 l_route_id ff_routes.route_id%TYPE;
60
61 begin
62
63 open cur_route_exists;
64
65 fetch cur_route_exists
66 into l_route_id;
67
68 if cur_route_exists%notfound then
69
70 close cur_route_exists;
71
72 open cur_ff_routes_s;
73 fetch cur_ff_routes_s
74 into l_route_id;
75 close cur_ff_routes_s;
76
77 insert into ff_routes
78 (
79 route_id,
80 route_name,
81 user_defined_flag,
82 description,
83 text,
84 last_update_date,
85 last_updated_by,
86 last_update_login,
87 created_by,
88 creation_date
89 )
90 values
91 (l_route_id,
92 p_route_name,
93 'N',
94 p_description,
95 p_text,
96 sysdate,
97 0,
98 0,
99 0,
100 sysdate);
101
102 return l_route_id;
103 else
104
105 close cur_route_exists;
106
107 hr_utility.trace(p_route_name || ' route already exists.');
108 hr_utility.trace('Updating route .... ' || p_route_name);
109
110 update ff_routes
111 set text = p_text
112 where route_id = l_route_id;
113
114 return l_route_id;
115
116 end if;
117 end ;
118 end create_route;
119
120 function create_user_entities(
121 p_user_entity_name varchar2,
122 p_route_id number,
123 p_notfound_allowed_flag varchar2,
124 p_entity_description varchar2) return number is
125 begin
126 declare
127
128 cursor cur_user_entity_exists is
129 select user_entity_id ,
130 creator_type
131 from ff_user_entities
132 where user_entity_name = p_user_entity_name
133 and legislation_code = 'CA';
134
135 cursor cur_user_entity_id is
136 select ff_user_entities_s.nextval
137 from dual;
138
139 l_user_entity_id number;
140 l_creator_type ff_user_entities.creator_type%TYPE;
141 begin
142 open cur_user_entity_exists;
143 fetch cur_user_entity_exists
144 into l_user_entity_id,
145 l_creator_type;
146
147 if cur_user_entity_exists%found then
148 close cur_user_entity_exists;
149 if l_creator_type = 'SEH' then
150 update ff_user_entities
151 set creator_type = 'X'
152 where user_entity_id = l_user_entity_id
153 and creator_type = 'SEH';
154 end if;
155 return l_user_entity_id;
156 else
157
158 close cur_user_entity_exists;
159
160 open cur_user_entity_id;
161 fetch cur_user_entity_id
162 into l_user_entity_id;
163 close cur_user_entity_id;
164
165 insert into ff_user_entities
166 (
167 user_entity_id,
168 business_group_id,
169 legislation_code,
170 route_id,
171 notfound_allowed_flag,
172 user_entity_name,
173 creator_id,
174 creator_type,
175 entity_description,
176 last_update_date,
177 last_updated_by,
178 last_update_login,
179 created_by,
180 creation_date
181 )
182 values
183 (
184 l_user_entity_id,
185 null,
186 'CA',
187 p_route_id,
188 'N',
189 p_user_entity_name ,
190 0,
191 'X',
192 p_entity_description,
193 sysdate,
194 0,
195 0,
196 0,
197 sysdate
198 );
199 return l_user_entity_id;
200 end if;
201
202 end;
203 end create_user_entities;
204
205
206 function create_route_parameters(
207 p_route_id number,
208 p_parameter_name varchar2,
209 p_sequence_no number,
210 p_data_type varchar2
211 ) return number is
212 begin
213 declare
214 cursor cur_route_parameter_exists is
215 select route_parameter_id from ff_route_parameters
216 where route_id= p_route_id
217 and parameter_name = p_parameter_name;
218
219 cursor cur_route_parameters is
220 select ff_route_parameters_s.nextval
221 from dual;
222
223 l_route_parameter_id ff_route_parameters.route_parameter_id%TYPE;
224 begin
225
226 open cur_route_parameter_exists;
227 fetch cur_route_parameter_exists
228 into l_route_parameter_id;
229
230 if cur_route_parameter_exists%found then
231
232 close cur_route_parameter_exists;
233
234 else
235
236 close cur_route_parameter_exists;
237
238 open cur_route_parameters;
239
240 fetch cur_route_parameters
241 into l_route_parameter_id;
242
243 close cur_route_parameters;
244
245 insert into ff_route_parameters
246 (route_parameter_id,
247 route_id,
248 data_type,
249 parameter_name,
250 sequence_no )
251 values
252 (l_route_parameter_id,
253 p_route_id,
254 p_data_type,
255 p_parameter_name,
256 p_sequence_no );
257
258 end if;
259
260 return l_route_parameter_id;
261
262 end;
263
264 end create_route_parameters;
265
266 procedure create_route_parameter_values(p_route_parameter_id number,
267 p_user_entity_id number,
268 p_parameter_value varchar2) is
269 begin
270
271 declare
272
273 cursor cur_route_parameter_values is
274 select 'x' from ff_route_parameter_values
275 where route_parameter_id = p_route_parameter_id
276 and user_entity_id = p_user_entity_id;
277
278 dummy char(1);
279
280 begin
281 open cur_route_parameter_values;
282 fetch cur_route_parameter_values
283 into dummy;
284
285 if cur_route_parameter_values%found then
286
287 close cur_route_parameter_values;
288
289 else
290
291 close cur_route_parameter_values;
292
293 insert into ff_route_parameter_values
294 (
295 route_parameter_id,
296 user_entity_id,
297 value,
298 last_update_date,
299 last_updated_by,
300 last_update_login,
301 created_by,
302 creation_date
303 )
304 values
305 (
306 p_route_parameter_id,
307 p_user_entity_id,
308 p_parameter_value,
309 sysdate,
310 0,
311 0,
312 0,
313 sysdate
314 );
315
316 end if;
317
318 end;
319
320 end create_route_parameter_values;
321
322 procedure create_route_context(
323 p_route_id number,
324 p_context_name varchar2,
325 p_sequence_no number) is
326 begin
327 declare
328
329 cursor cur_context_id is
330 select context_id from ff_contexts
331 where ltrim(rtrim(context_name)) = ltrim(rtrim(p_context_name));
332
333 l_context_id ff_contexts.context_id%TYPE;
334
335 cursor cur_route_context_usages is
336 select 'x'
337 from ff_route_context_usages
338 where route_id = p_route_id
339 and context_id = l_context_id;
340
341 dummy char(1);
342
343 begin
344 open cur_context_id;
345 fetch cur_context_id
346 into l_context_id;
347 close cur_context_id;
348
349 open cur_route_context_usages;
350 fetch cur_route_context_usages
351 into dummy;
355 close cur_route_context_usages;
352
353 if cur_route_context_usages%found then
354
356
357 else
358
359 close cur_route_context_usages;
360 insert into ff_route_context_usages
361 (
362 route_id,
363 context_id,
364 sequence_no
365 )
366 values
367 (
368 p_route_id,
369 l_context_id,
370 p_sequence_no
371 );
372
373 end if;
374
375 end;
376
377 end create_route_context;
378
379 function create_database_item(p_user_name varchar2,
380 p_user_entity_id number,
381 p_data_type varchar2,
382 p_definition_text varchar2,
383 p_null_allowed_flag varchar2,
384 p_description varchar2) return number is
385 begin
386 declare
387 cursor cur_database_item_exists is
388 select 'x'
389 from ff_database_items fdi
390 where fdi.user_name = p_user_name;
391
392 dummy char(1);
393 ret number(1);
394
395 begin
396 open cur_database_item_exists;
397
398 fetch cur_database_item_exists
399 into dummy;
400
401 if cur_database_item_exists%notfound then
402
403
404 close cur_database_item_exists;
405
406 insert into ff_database_items(
407 user_name,
408 user_entity_id,
409 data_type,
410 definition_text,
411 null_allowed_flag,
412 description,
413 last_update_date,
414 last_updated_by,
415 last_update_login,
416 created_by,
417 creation_date)
418 values (p_user_name,
419 p_user_entity_id,
420 p_data_type,
421 p_definition_text,
422 p_null_allowed_flag,
423 p_description,
424 sysdate,
425 0,
426 0,
427 0,
428 sysdate);
429
430 ret := 1;
431
432 else
433
434 close cur_database_item_exists;
435 ret := -1;
436
437 end if;
438
439 return ret;
440
441 end;
442
443 end create_database_item;
444
445 function create_format_item(p_user_name varchar2,
446 p_display_sequence number) return number is
447 begin
448 declare
449 start_of_time constant date := to_date('0001/01/01','YYYY/MM/DD');
450 end_of_time constant date := to_date('4712/12/31','YYYY/MM/DD');
451
452 l_user_entity_id ff_user_entities.user_entity_id%TYPE;
453 l_report_type pay_report_format_mappings_f.report_type%TYPE;
454 l_archive_type pay_report_format_items_f.archive_type%TYPE;
455 l_updatable_flag pay_report_format_items_f.updatable_flag%TYPE;
456
457 cursor cur_format_item is
458 select fue.user_entity_id
459 from ff_user_entities fue
460 where upper(fue.user_entity_name) = upper(p_user_name);
461
462 ret number := 0;
463
464 cursor cur_format_items_exists is
465 select 'x'
466 from pay_report_format_items_f
467 where report_type = 'ROE' and
468 report_qualifier = 'ROEQ' and
469 report_category = 'ROEC' and
470 user_entity_id = l_user_entity_id and
471 effective_start_date = start_of_time and
472 effective_end_date = end_of_time;
473
474 dummy varchar2(1);
475
476 begin
477 open cur_format_item;
478 fetch cur_format_item
479 into l_user_entity_id;
480
481 if cur_format_item%notfound then
482 close cur_format_item;
483 return -1;
484 else
485
486 close cur_format_item;
487
488 if p_user_name in ('ROE_PAYROLL_ID',
489 'ROE_ASSIGNMENT_ID',
490 'ROE_GRE_ID',
491 'PREV_ROE_DATE',
492 'ROE_DATE') then
493
494 l_archive_type := 'AAC';
495 l_updatable_flag := Null;
496
497 elsif p_user_name in ( 'ROE_TAX_UNIT_NAME',
498 'ROE_TAX_UNIT_PROVINCE',
499 'ROE_TAX_UNIT_POSTAL_CODE',
500 'ROE_TAX_UNIT_ADDRESS_LINE_1',
501 'ROE_TAX_UNIT_ADDRESS_LINE_2',
502 'ROE_TAX_UNIT_ADDRESS_LINE_3',
503 'ROE_TAX_UNIT_CITY',
504 'ROE_TAX_UNIT_COUNTRY',
505 'ROE_CANADA_EMPLOYER_IDENTIFICATION_ORG_BUSINESS_NUMBER',
506 'ROE_PER_SOCIAL_INSURANCE_NUMBER',
507 'ROE_PER_FIRST_NAME',
508 'ROE_PER_LAST_NAME') then
509
510 l_archive_type := 'AAP';
511 l_updatable_flag := 'N';
512
513 else
514
515 l_archive_type := 'AAP';
516 l_updatable_flag := 'Y';
517
518 end if;
519
520 open cur_format_items_exists;
521 fetch cur_format_items_exists into dummy;
522 if cur_format_items_exists%found then
523
524 close cur_format_items_exists;
525
526 update pay_report_format_items_f
527 set updatable_flag = l_updatable_flag,
528 archive_type = l_archive_type,
529 display_sequence = p_display_sequence
530 where
531 report_type = 'ROE' and
532 report_qualifier = 'ROEQ' and
533 report_category = 'ROEC' and
534 user_entity_id = l_user_entity_id and
535 effective_start_date = start_of_time and
536 effective_end_date = end_of_time;
537
538 ret := 1;
539
540 return ret;
541 else
542
543 close cur_format_items_exists;
544
545 insert into pay_report_format_items_f
546 (
547 report_type,
548 report_qualifier,
549 report_category,
550 user_entity_id,
551 effective_start_date,
552 effective_end_date,
553 archive_type,
554 updatable_flag,
555 display_sequence
556 )
557 values
558 (
559 'ROE',
560 'ROEQ',
561 'ROEC',
562 l_user_entity_id,
563 start_of_time,
564 end_of_time,
565 l_archive_type,
566 l_updatable_flag,
567 p_display_sequence
568 );
569
570 end if;
571
572 ret := 1;
573
574 return ret;
575
576 end if;
577 end;
578
579 end create_format_item;
580
581 end pay_ca_dbi_roe;
582