[Home] [Help]
PACKAGE BODY: APPS.PAY_US_CONTR_DBI
Source
1 package body pay_us_contr_dbi as
2 /* $Header: pyuscont.pkb 120.2 2011/12/22 11:05:38 emunisek ship $ */
3 /*
4 --
5 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
6 --
7 /*
8 NAME
9 pyuscont.pkb
10 --
11 DESCRIPTION
12 Procedures required to create the startup data for US Benefit Contribution
13 Database Items, and procedures required to create the Database Items
14 Dynamically on creation of Input Values.
15 --
16 These procedures create the following objects
17 --
18 Routes
19 ------
20 US_CONTRIBUTION_VALUES
21 --
22 Database Items
23 --------------
24 <ENTITY_NAME>_BEN_EE_CONTR_VALUE
25 <ENTITY_NAME>_BEN_ER_CONTR_VALUE
26 --
27 Name Date Change Details
28 -------- ---------- -----------------------------------------------
29 A.Logue 14-FEB-2000 Utf8 Support. Input value name lengthened to 80.
30 A.Myers 13-FEB-1998 Knock on fix from bug 602851, extra parameter and logic
31 for extra parameter to hrdyndbi.insert_user_entity.
32 New Version: 110.1
33 rfine 24-NOV-1994 Suppressed index on business_group_id
34 rfine 05-OCT-1994 Prepended package name with 'PAY_' as per naming
35 standards.
36 mwcallag 09-DEC-1993 G334 : Benefit DB item names changed to be
37 <ELEMENT>_BEN_EE_CONTR_VALUE and
38 <ELEMENT>_BEN_ER_CONTR_VALUE. Legislation code
39 derived from from per_business_groups if the
40 Legislation code is null on the input value table.
41 mwcallag 30-NOV-1993 G259 : Routine modified to use externalised database
42 item creation procedures in package hrdyndbi.
43 JRhodes 05-Nov-1993 Added "and nvl(BC.contributions_used,'Y') = 'Y'"
44 to cater for Payroll Deductions generation of
45 DB items
46 mwcallag 02-NOV-1993 exception of no_data_found added to procedure
47 create_contr_items.
48 JRhodes 20-OCT-1993 Created.
49 SDoshi 22-MAR-1999 Flexible Dates Conversion
50 irgonzal 24-SEP-2001 Bug fix 2004226. Enabled index on per_business_groups
51 table by removing "+ 0" from the WHERE clause.
52 Modified create_contr_items procedure.
53 emunisek 17-NOV-2011 For Bug#13375560,replaced LONG datatype by ff_routes.text%TYPE
54
55 */
56 --
57 PROCEDURE create_usdbi_startup is
58 l_text ff_routes.text%TYPE;
59 l_date_earned_context_id number;
60 l_assign_id_context_id number;
61 l_payroll_context_id number;
62 l_payroll_action_context_id number;
63 l_org_pay_method_id number;
64 l_per_pay_method_id number;
65 l_organization_id number;
66 l_temp number;
67 --
68 -- ******** local procedure : insert_route_parameters ********
69 --
70 procedure insert_route_parameters
71 (
72 p_parameter_name in varchar2,
73 p_data_type in varchar2,
74 p_sequence_no in number
75 ) is
76 begin
77 hr_utility.set_location('pay_us_contr_dbi.insert_route_parameters', 1);
78 insert into ff_route_parameters
79 (route_id,
80 sequence_no,
81 parameter_name,
82 data_type,
83 route_parameter_id)
84 select ff_routes_s.currval,
85 p_sequence_no,
86 p_parameter_name,
87 p_data_type,
88 ff_route_parameters_s.nextval
89 from dual;
90 end insert_route_parameters;
91 --
92 -- ******** local procedure : insert_route_context_usages ********
93 --
94 procedure insert_route_context_usages
95 (
96 p_context_id in number,
97 p_sequence_no in number
98 ) is
99 begin
100 hr_utility.set_location('pay_us_contr_dbi.insert_route_context_usages', 1);
101 insert into ff_route_context_usages
102 (route_id,
103 context_id,
104 sequence_no)
105 select ff_routes_s.currval,
106 p_context_id,
107 p_sequence_no
108 from dual;
109 end insert_route_context_usages;
110 --
111 -- ************ Procedure : create_usdbi_startup ****************
112 --
113 BEGIN
114 --
115 -- get the context ids from the context table
116 --
117 hr_utility.set_location('pay_us_contr_dbi.create_usdbi_startup', 1);
118 select context_id
119 into l_assign_id_context_id
120 from ff_contexts
121 where context_name = 'ASSIGNMENT_ID';
122 --
123 hr_utility.set_location('pay_us_contr_dbi.create_usdbi_startup', 2);
124 select context_id
125 into l_date_earned_context_id
126 from ff_contexts
127 where context_name = 'DATE_EARNED';
128 --
129 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
130 -- +
131 -- input value route, name: US_CONTRIBUTION_VALUES +
132 -- +
133 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
134 --
135 l_text :=
136 'pay_input_values_f INPUTV,
137 pay_element_entry_values_f EEV,
138 pay_element_types_f ETYPE,
139 pay_element_links_f ELINK,
140 pay_element_entries_f EE,
141 ben_benefit_contributions_f BCONTR,
142 pay_input_values_f INPUTV2,
143 pay_element_entry_values_f EEV2
144 WHERE INPUTV.input_value_id = &U2
145 AND &B1 BETWEEN INPUTV.effective_start_date
146 AND INPUTV.effective_end_date
147 AND INPUTV.input_value_id = EEV.input_value_id
148 AND INPUTV.element_type_id = ETYPE.element_type_id
149 AND &B1 BETWEEN ETYPE.effective_start_date
150 AND ETYPE.effective_end_date
151 AND ETYPE.element_type_id = ELINK.element_type_id
152 AND &B1 BETWEEN ELINK.effective_start_date
153 AND ELINK.effective_end_date
154 AND EE.assignment_id = &B2
155 AND &B1 BETWEEN EE.effective_start_date
156 AND EE.effective_end_date
157 AND ELINK.element_link_id = EE.element_link_id
158 AND EE.entry_type = ''E''
159 AND EE.element_entry_id = EEV.element_entry_id
160 AND &B1 BETWEEN EEV.effective_start_date
161 AND EEV.effective_end_date
162 AND &B1 BETWEEN INPUTV2.effective_start_date
163 AND INPUTV2.effective_end_date
164 AND INPUTV2.input_value_id = EEV2.input_value_id
165 AND INPUTV2.element_type_id = ETYPE.element_type_id
166 AND EE.element_entry_id = EEV2.element_entry_id
167 AND &B1 BETWEEN EEV2.effective_start_date
168 AND EEV2.effective_end_date
169 AND &U1 = BCONTR.element_type_id(+)
170 AND &B1, BETWEEN BCONTR.effective_start_date(+)
171 AND BCONTR.effective_end_date(+)
172 AND BCONTR.coverage_type(+) = EEV2.screen_entry_value
173 AND upper(INPUTV2.name) = ''COVERAGE''';
174 --
175 hr_utility.set_location('pay_us_contr_dbi.create_usdbi_startup', 5);
176 select ff_routes_s.nextval
177 into l_temp
178 from dual;
179 --
180 -- now do the normal insert
181 --
182 hr_utility.set_location('pay_us_contr_dbi.create_usdbi_startup', 6);
183 insert into ff_routes
184 (route_id,
185 route_name,
186 user_defined_flag,
187 description,
188 text,
189 last_update_date,
190 last_updated_by,
191 last_update_login,
192 created_by,
193 creation_date)
194 values (ff_routes_s.currval,
195 'US_CONTRIBUTION_VALUES',
196 'N',
197 'route for contribution values using benefit contributions',
198 l_text,
199 sysdate,
200 0,
201 0,
202 0,
203 sysdate);
204 --
205 insert_route_parameters ('Element Type ID', 'N', 1);
206 insert_route_parameters ('Input value ID', 'N', 2);
207 insert_route_context_usages (l_date_earned_context_id, 1);
208 insert_route_context_usages (l_assign_id_context_id, 2);
209 --
210 END create_usdbi_startup;
211 --
212 -- ********** Procedure : create_contr_items ******************
213 --
214 PROCEDURE create_contr_items
215 (p_input_value_id IN NUMBER
216 ,p_effective_date IN DATE
217 ,p_start_string IN VARCHAR2
218 ,p_end_string IN VARCHAR2
219 ,p_data_type IN VARCHAR2
220 ) IS
221 --
222 l_input_value_name VARCHAR2(80);
223 l_business_group_id NUMBER;
224 l_legislation_code VARCHAR2(30);
225 l_element_name VARCHAR2(80);
226 l_element_type_id NUMBER;
227 l_route_name VARCHAR2(50);
228 l_not_found_flag VARCHAR2(1);
229 l_entity_name VARCHAR2(80);
230 l_entity_description VARCHAR2(80);
231 l_text VARCHAR2(240);
232 l_description VARCHAR2(80);
233 l_created_by NUMBER;
234 l_last_login NUMBER;
235 --
236 -- ******** local procedure : get_route ********
237 --
238 FUNCTION get_route
239 (p_route_name VARCHAR2
240 ) return NUMBER IS
241 --
242 l_route_id NUMBER;
243 --
244 BEGIN
245 --
246 hr_utility.set_location ('pay_us_contr_dbi.get_route', 1);
247 SELECT route_id
248 INTO l_route_id
249 FROM ff_routes
250 WHERE route_name = p_route_name;
251 --
252 return(l_route_id);
253 --
254 END get_route;
255 --
256 -- ******** local procedure : create_item ********
257 --
258 procedure create_item
259 (p_business_group_id in number
260 ,p_legislation_code in varchar2
261 ,p_route_name in varchar2
262 ,p_not_found_flag in varchar2
263 ,p_entity_name in varchar2
264 ,p_entity_description in varchar2
265 ,p_data_type in varchar2
266 ,p_text in varchar2
267 ,p_description in varchar2
268 ,p_creator_id in number
269 ,p_created_by in number
270 ,p_last_login in number
271 ,p_element_type_id in number
272 ,p_input_value_id in number
273 )
274 IS
275 l_record_inserted BOOLEAN;
276 --
277 BEGIN
278 --
279 -- create the user entity:
280 --
281 hrdyndbi.insert_user_entity (p_route_name,
282 p_entity_name,
283 p_entity_description,
284 p_not_found_flag,
285 'I',
286 p_creator_id,
287 p_business_group_id,
288 p_legislation_code,
289 p_created_by,
290 p_last_login,
291 l_record_inserted);
292 --
293 -- insert the parameter values:
294 --
295 IF l_record_inserted THEN
296 hrdyndbi.insert_parameter_value (p_element_type_id, 1);
297 hrdyndbi.insert_parameter_value (p_input_value_id, 2);
298 --
299 -- Insert the Database Item:
300 --
301 hrdyndbi.insert_database_item (p_entity_name,
302 'VALUE',
303 p_data_type,
304 p_text,
305 'Y',
306 p_description);
307 END IF;
308 END create_item;
309 --
310 -- ******** procedure : create_contr_items ********
311 --
312 BEGIN
313 --
314 --
315 -- retrieve details of the input value and element type
316 --
317 begin
318 hr_utility.set_location ('pay_us_contr_dbi.create_contr_items', 1);
319 select ET.element_type_id,
320 replace (ltrim (rtrim (upper (ET.element_name))), ' ', '_'),
321 replace (ltrim (rtrim (upper (IV.name))), ' ', '_'),
322 IV.business_group_id,
323 nvl (ltrim(rtrim(IV.legislation_code)),
324 ltrim(rtrim(BUSGP.legislation_code))),
325 IV.created_by,
326 IV.last_update_login
327 into l_element_type_id,
328 l_element_name,
329 l_input_value_name,
330 l_business_group_id,
331 l_legislation_code,
332 l_created_by,
333 l_last_login
334 from pay_input_values_f IV,
335 pay_element_types_f ET,
336 per_business_groups BUSGP,
337 ben_benefit_classifications BC
338 where IV.input_value_id = p_input_value_id
339 and p_effective_date between IV.effective_start_date
340 and IV.effective_end_date
341 and IV.element_type_id = ET.element_type_id
342 and p_effective_date between ET.effective_start_date
343 and ET.effective_end_date
344 and ET.benefit_classification_id = BC.benefit_classification_id(+)
345 and nvl(BC.contributions_used,'Y') = 'Y'
346 and BUSGP.business_group_id (+) = IV.business_group_id + 0; --#2004226
347 exception
348 when no_data_found then l_element_type_id := null;
349 end;
350 --
351 if (l_element_type_id is not null) and
352 (l_input_value_name = 'ER_CONTR' or l_input_value_name = 'EE_CONTR')
353 then
354 --
355 if l_input_value_name = 'ER_CONTR' then
356 --
357 l_route_name := 'US_CONTRIBUTION_VALUES';
358 l_entity_name := l_element_name || '_BEN_ER_CONTR';
359 l_entity_description := 'Entity for ' || l_route_name;
360 l_text := 'nvl(EEV.screen_entry_value,
361 BCONTR.employer_contribution)';
362 l_description := 'employers contribution';
363 else
364 --
365 l_route_name := 'US_CONTRIBUTION_VALUES';
366 l_entity_name := l_element_name || '_BEN_EE_CONTR';
367 l_entity_description := 'Entity for ' || l_route_name;
368 l_text := 'nvl(EEV.screen_entry_value,
369 BCONTR.employee_contribution)';
370 l_description := 'employees contribution';
371 end if;
372 --
373 -- Handle variable data type
374 --
375 l_text := 'min (' || p_start_string || l_text || p_end_string || ')';
376 --
377 hr_utility.set_location ('pay_us_contr_dbi.create_contr_items', 2);
378 create_item
379 (l_business_group_id
380 ,l_legislation_code
381 ,l_route_name
382 ,'Y' -- l_not_found_flag
383 ,l_entity_name
384 ,l_entity_description
385 ,p_data_type
386 ,l_text
387 ,l_description
388 ,p_input_value_id -- l_creator_id
389 ,l_created_by
390 ,l_last_login
391 ,l_element_type_id
392 ,p_input_value_id
393 );
394 --
395 end if;
396 END create_contr_items;
397 END pay_us_contr_dbi;