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