[Home] [Help]
PACKAGE BODY: APPS.OTAFRUDT
Source
1 PACKAGE BODY OTAFRUDT AS
2 /* $Header: otafrudt.pkb 120.2 2005/10/31 05:37:55 hwinsor noship $ */
3
4 --------------------------------------------------------------------------------
5 -- the PROCEDURE - insert_table_data enters data into tables --
6 --------------------------------------------------------------------------------
7
8 PROCEDURE insert_table_data (P_BUSINESS_GROUP_ID_ITD IN number,
9 P_LEGISLATION_CODE_ITD IN varchar2,
10 P_APPLICATION_ID_ITD IN number,
11 P_RANGE_OR_MATCH_ITD IN varchar2,
12 P_USER_KEY_UNITS_ITD IN varchar2,
13 P_USER_TABLE_NAME_ITD IN varchar2,
14 P_USER_ROW_TITLE_ITD IN varchar2)
15 IS
16 v_table_rowid varchar2(100);
17 v_user_table_id number;
18 BEGIN
19 hr_utility.trace ('PROCEDURE insert_data entered.');
20
21 v_table_rowid := NULL;
22 v_user_table_id := NULL;
23
24 PAY_USER_TABLES_PKG.INSERT_ROW
25 (P_ROWID => v_table_rowid,
26 P_USER_TABLE_ID => v_user_table_id,
27 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID_ITD,
28 P_LEGISLATION_CODE => P_LEGISLATION_CODE_ITD,
29 P_LEGISLATION_SUBGROUP => NULL,
30 P_RANGE_OR_MATCH => P_RANGE_OR_MATCH_ITD,
31 P_USER_KEY_UNITS => P_USER_KEY_UNITS_ITD,
32 P_USER_TABLE_NAME => P_USER_TABLE_NAME_ITD,
33 P_USER_ROW_TITLE => P_USER_ROW_TITLE_ITD);
34
35 hr_utility.trace ('PROCEDURE insert_data exiting.');
36 END insert_table_data;
37
38
39 --------------------------------------------------------------------------------
40 -- procedure - create_table will insert seed data for French localisation --
41 -- it inserts for user defined tables --
42 --------------------------------------------------------------------------------
43
44 PROCEDURE create_table (P_BUSINESS_GROUP_ID_CT IN number,
45 P_APPLICATION_ID_CT IN number,
46 P_RANGE_OR_MATCH_CT IN varchar2,
47 P_USER_KEY_UNITS_CT IN varchar2,
48 P_USER_TABLE_NAME_CT IN varchar2,
49 P_USER_ROW_TITLE_CT IN varchar2)
50 IS
51 -- ensures table not already created for business group
52 CURSOR user_table_name_csr IS
53 SELECT user_table_name
54 FROM pay_user_tables
55 WHERE business_group_id = P_BUSINESS_GROUP_ID_CT
56 AND user_table_name = P_USER_TABLE_NAME_CT;
57
58 v_legislation_code per_business_groups.legislation_code%TYPE;
59 v_user_table_name varchar (100);
60 BEGIN
61
62 hr_utility.trace ('PROCEDURE create table entered.');
63
64 SELECT legislation_code
65 INTO v_legislation_code
66 FROM per_business_groups
67 WHERE business_group_id = P_BUSINESS_GROUP_ID_CT;
68 --
69 OPEN user_table_name_csr;
70 FETCH user_table_name_csr INTO v_user_table_name;
71 --
72 IF user_table_name_csr%NOTFOUND THEN
73 hr_utility.trace ('inserting data into '||P_USER_TABLE_NAME_CT||' from create_table.');
74 --
75 insert_table_data (P_BUSINESS_GROUP_ID_ITD => P_BUSINESS_GROUP_ID_CT,
76 P_LEGISLATION_CODE_ITD => v_legislation_code,
77 P_APPLICATION_ID_ITD => P_APPLICATION_ID_CT,
78 P_RANGE_OR_MATCH_ITD => P_RANGE_OR_MATCH_CT,
79 P_USER_KEY_UNITS_ITD => P_USER_KEY_UNITS_CT,
80 P_USER_TABLE_NAME_ITD => P_USER_TABLE_NAME_CT,
81 P_USER_ROW_TITLE_ITD => P_USER_ROW_TITLE_CT);
82 ELSE
83 hr_utility.trace ('TABLE ALREADY EXISTS - create_table abandoned.');
84 END IF;
85
86 hr_utility.trace ('PROCEDURE create table exiting.');
87
88 exception
89 when others then
90 null;
91 END create_table;
92
93 --------------------------------------------------------------------------------
94 -- PROCEDURE - create_column enters data into tables --
95 --------------------------------------------------------------------------------
96
97 PROCEDURE create_column (P_BUSINESS_GROUP_ID_CC IN number,
98 P_USER_TABLE_NAME_CC IN varchar2,
99 P_USER_COLUMN_NAME_CC IN varchar2)
100 IS
101 v_column_row_id varchar2 (100) := NULL;
102 v_user_column_id number := NULL;
103
104 v_user_table_id number;
105 v_legislation_code per_business_groups.legislation_code%TYPE;
106 v_user_column_name varchar2 (100);
107
108 CURSOR user_column_id_csr IS
109 SELECT user_column_id
110 FROM pay_user_columns
111 WHERE user_table_id =
112 (SELECT user_table_id
113 FROM pay_user_tables
114 WHERE business_group_id = P_BUSINESS_GROUP_ID_CC
115 AND user_table_name = P_USER_TABLE_NAME_CC)
116 AND user_column_name = P_USER_COLUMN_NAME_CC;
117
118 BEGIN
119 hr_utility.trace ('PROCEDURE create column entered.');
120
121 SELECT user_table_id
122 INTO v_user_table_id
123 FROM pay_user_tables
124 WHERE user_table_name = P_USER_TABLE_NAME_CC
125 AND business_group_id = P_BUSINESS_GROUP_ID_CC;
126 --
127 SELECT legislation_code
128 INTO v_legislation_code
129 FROM per_business_groups
130 WHERE business_group_id = P_BUSINESS_GROUP_ID_CC;
131 --
132 OPEN user_column_id_csr;
133 FETCH user_column_id_csr INTO v_user_column_id;
134 --
135 IF user_column_id_csr%NOTFOUND THEN
136 PAY_USER_COLUMNS_PKG.INSERT_ROW
137 (P_ROWID => v_column_row_id,
138 P_USER_COLUMN_ID => v_user_column_id,
139 P_USER_TABLE_ID => v_user_table_id,
140 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID_CC,
141 P_LEGISLATION_CODE => v_legislation_code,
142 P_LEGISLATION_SUBGROUP => NULL,
143 P_USER_COLUMN_NAME => P_USER_COLUMN_NAME_CC,
144 P_FORMULA_ID => NULL);
145 ELSE
146 --
147 hr_utility.trace ('column entry already exists, column not entered.');
148 --
149 END IF;
150
151 hr_utility.trace ('PROCEDURE create column exiting.');
152
153 END create_column;
154
155 --------------------------------------------------------------------------------
156 -- PROCEDURE - create_row enters row level data into tables --
157 --------------------------------------------------------------------------------
158
159 PROCEDURE create_row (P_BUSINESS_GROUP_ID_CR IN number,
160 P_USER_TABLE_NAME_CR IN varchar2,
161 P_USER_COLUMN_NAME_CR IN varchar2,
162 P_ROW_LOW_RANGE_OR_NAME_CR IN varchar2,
163 P_DISPLAY_SEQUENCE_CR IN number,
164 P_VALUE_CR IN varchar2)
165 IS
166 v_legislation_code per_business_groups.legislation_code%TYPE;
167 v_start_date date := TO_DATE ('01/01/1900', 'DD/MM/YYYY');
168 v_end_date date := TO_DATE ('31/12/4712', 'DD/MM/YYYY');
169 --
170 v_user_table_id number;
171 v_user_row_id number := NULL;
172 --
173 v_column_instance_rowid varchar2 (100) := NULL;
174 v_user_column_instance_id number := NULL;
175 v_user_column_id varchar2 (100);
176
177 CURSOR user_row_id_csr IS
178 SELECT user_row_id
179 FROM pay_user_rows_f
180 WHERE user_table_id = (SELECT user_table_id
181 FROM pay_user_tables
182 WHERE user_table_name = P_USER_TABLE_NAME_CR
183 AND business_group_id = P_BUSINESS_GROUP_ID_CR)
184 AND P_ROW_LOW_RANGE_OR_NAME_CR = row_low_range_or_name;
185
186 CURSOR row_instance_csr (p_user_row_id number, p_user_column_id number) IS
187 SELECT user_column_instance_id
188 FROM pay_user_column_instances_f
189 WHERE user_row_id = p_user_row_id
190 AND user_column_id = p_user_column_id;
191
192 BEGIN
193 hr_utility.trace ('PROCEDURE create row entered.');
194 --
195 SELECT user_table_id, legislation_code
196 INTO v_user_table_id, v_legislation_code
197 FROM pay_user_tables
198 WHERE user_table_name = P_USER_TABLE_NAME_CR
199 AND business_group_id = P_BUSINESS_GROUP_ID_CR;
200 --
201 OPEN user_row_id_csr;
202 FETCH user_row_id_csr INTO v_user_row_id;
203 --
204 IF user_row_id_csr%NOTFOUND THEN
205 --
206 SELECT pay_user_rows_s.nextval
207 INTO v_user_row_id
208 FROM dual;
209
210 -- this insertion creates the row
211
212 hr_utility.trace ('Inserting row '||P_ROW_LOW_RANGE_OR_NAME_CR);
213 hr_utility.trace (' INTO '||P_BUSINESS_GROUP_ID_CR);
214 INSERT INTO pay_user_rows_f (user_row_id,
215 effective_start_date,
216 effective_end_date,
217 business_group_id,
218 legislation_code,
219 user_table_id,
220 row_low_range_or_name,
221 display_sequence,
222 legislation_subgroup,
223 row_high_range)
224
225 VALUES (v_user_row_id,
226 v_start_date,
227 v_end_date,
228 P_BUSINESS_GROUP_ID_CR,
229 v_legislation_code,
230 v_user_table_id,
231 P_ROW_LOW_RANGE_OR_NAME_CR,
232 P_DISPLAY_SEQUENCE_CR,
233 NULL,
234 NULL);
235 --
236 hr_utility.set_location ('Created row '||P_ROW_LOW_RANGE_OR_NAME_CR,1);
237 --
238 ELSE
239 --
240 hr_utility.set_location ('row already exists, row not entered.', 202);
241 --
242 END IF;
243
244 CLOSE user_row_id_csr;
245
246 hr_utility.trace ('PROCEDURE create_row exiting');
247 --
248 SELECT user_column_id
249 INTO v_user_column_id
250 FROM pay_user_columns_v
251 WHERE user_column_name = P_USER_COLUMN_NAME_CR
252 AND user_table_id = v_user_table_id;
253 --
254 OPEN row_instance_csr (v_user_row_id, v_user_column_id);
255 FETCH row_instance_csr INTO v_user_column_instance_id;
256 IF row_instance_csr%NOTFOUND THEN
257 --
258 hr_utility.set_location ('inserting instance', 10);
259 PAY_USER_COLUMN_INSTANCES_PKG.INSERT_ROW
260 (P_ROWID => v_column_instance_rowid
261 ,P_USER_COLUMN_INSTANCE_ID => v_user_column_instance_id
262 ,P_EFFECTIVE_START_DATE => v_start_date
263 ,P_EFFECTIVE_END_DATE => v_end_date
264 ,P_USER_ROW_ID => v_user_row_id
265 ,P_USER_COLUMN_ID => v_user_column_id
266 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID_CR
267 ,P_LEGISLATION_CODE => v_legislation_code
268 ,P_LEGISLATION_SUBGROUP => NULL
269 ,P_VALUE => P_VALUE_CR);
270 --
271 ELSE
272 hr_utility.set_location ('instance already exists', 10);
273 END IF;
274 END create_row;
275
276 --------------------------------------------------------------------------------
277 -- PROCEDURE - create_from_lookup creates user defined tables from lookups --
278 --------------------------------------------------------------------------------
279
280 PROCEDURE create_from_lookup (P_BUSINESS_GROUP_ID IN varchar2,
281 P_REQUIRED_DEFAULTS IN varchar2,
282 P_DEFAULT_VALUE IN varchar2,
283 P_LOOKUP_TYPE IN varchar2,
284 P_USER_COLUMN_NAME IN varchar2,
285 P_USER_KEY_UNITS IN varchar2)
286 IS
287 v_lookup_code fnd_common_lookups.lookup_code%TYPE;
288 v_meaning fnd_common_lookups.meaning%TYPE;
289
290 v_sequence number := 10;
291
292 v_legislation_code varchar2 (10) := NULL; -- holds legislation code returned
293
294 CURSOR lookup_code_csr IS
295 SELECT lookup_code, meaning
296 FROM fnd_common_lookups
297 WHERE lookup_type = P_LOOKUP_TYPE;
298
299 BEGIN
300 hr_utility.trace ('PROCEDURE create_from_lookup entered.');
301
302 OPEN lookup_code_csr;
303 FETCH lookup_code_csr INTO v_lookup_code, v_meaning;
304 IF lookup_code_csr%FOUND THEN
305 hr_utility.set_location ('Valid lookup: '||P_LOOKUP_TYPE, 40);
306
307 create_table (P_BUSINESS_GROUP_ID_CT => P_BUSINESS_GROUP_ID,
308 P_APPLICATION_ID_CT => 800,
309 P_RANGE_OR_MATCH_CT => 'M',
310 P_USER_KEY_UNITS_CT => P_USER_KEY_UNITS,
311 P_USER_TABLE_NAME_CT => P_LOOKUP_TYPE,
312 P_USER_ROW_TITLE_CT => P_LOOKUP_TYPE);
313
314 hr_utility.set_location ('Adding: '||P_USER_COLUMN_NAME, 50);
315
316 create_column (P_BUSINESS_GROUP_ID_CC => P_BUSINESS_GROUP_ID,
317 P_USER_TABLE_NAME_CC => P_LOOKUP_TYPE,
318 P_USER_COLUMN_NAME_CC => P_USER_COLUMN_NAME);
319
320 LOOP
321 EXIT WHEN lookup_code_csr%NOTFOUND;
322
323 IF P_REQUIRED_DEFAULTS = 'NONE' THEN
324 hr_utility.trace ('sequence number: '||v_sequence);
325 create_row (P_BUSINESS_GROUP_ID_CR => P_BUSINESS_GROUP_ID,
326 P_USER_TABLE_NAME_CR => P_LOOKUP_TYPE,
327 P_USER_COLUMN_NAME_CR => P_USER_COLUMN_NAME,
328 P_ROW_LOW_RANGE_OR_NAME_CR => v_lookup_code,
329 P_DISPLAY_SEQUENCE_CR => v_sequence,
330 P_VALUE_CR => NULL);
331 ELSIF P_REQUIRED_DEFAULTS = 'QUICKCODE_VALUE' THEN
332 hr_utility.trace ('sequence number: '||v_sequence);
333 create_row (P_BUSINESS_GROUP_ID_CR => P_BUSINESS_GROUP_ID,
334 P_USER_TABLE_NAME_CR => P_LOOKUP_TYPE,
335 P_USER_COLUMN_NAME_CR => P_USER_COLUMN_NAME,
336 P_ROW_LOW_RANGE_OR_NAME_CR => v_lookup_code,
337 P_DISPLAY_SEQUENCE_CR => v_sequence,
338 P_VALUE_CR => v_meaning);
339 ELSE
340 hr_utility.trace ('sequence number: '||v_sequence);
341 create_row (P_BUSINESS_GROUP_ID_CR => P_BUSINESS_GROUP_ID,
342 P_USER_TABLE_NAME_CR => P_LOOKUP_TYPE,
343 P_USER_COLUMN_NAME_CR => P_USER_COLUMN_NAME,
344 P_ROW_LOW_RANGE_OR_NAME_CR => v_lookup_code,
345 P_DISPLAY_SEQUENCE_CR => v_sequence,
346 P_VALUE_CR => P_DEFAULT_VALUE);
347 END IF;
348 FETCH lookup_code_csr INTO v_lookup_code, v_meaning;
349 v_sequence := v_sequence + 10;
350 END LOOP;
351 END IF;
352 CLOSE lookup_code_csr;
353
354 hr_utility.trace ('PROCEDURE create_from_lookup exited.');
355
356 END;
357 --
358 procedure load_alternate_lookup (l_business_group_id in number)
359 is
360 BEGIN
361 -- this is no longer required for the 2005 2483
362 null;
363 ---------------------------------------------------------
364 -- creation of user defined rows from existing lookups --
365 ---------------------------------------------------------
366 -- create_from_lookup (P_BUSINESS_GROUP_ID => l_business_group_id,
367 -- P_REQUIRED_DEFAULTS => 'NONE',
368 -- P_DEFAULT_VALUE => NULL,
369 -- P_LOOKUP_TYPE => 'FR_EMPLOYEE_CATEGORY',
370 -- P_USER_COLUMN_NAME => 'BS_EMP_CAT',
371 -- P_USER_KEY_UNITS => 'T');
372 --
373 -- create_from_lookup (P_BUSINESS_GROUP_ID => l_business_group_id,
374 -- P_REQUIRED_DEFAULTS => 'NONE',
375 -- P_DEFAULT_VALUE => NULL,
376 -- P_LOOKUP_TYPE => 'ACTIVITY_CATEGORY',
377 -- P_USER_COLUMN_NAME => '2483_ACT_CAT',
378 -- P_USER_KEY_UNITS => 'T');
379 --
380 END load_alternate_lookup;
381 --
382 END OTAFRUDT;