[Home] [Help]
PACKAGE BODY: APPS.PQP_COPY_UDT
Source
1 PACKAGE BODY pqp_copy_udt AS
2 /* $Header: pqpcpudt.pkb 115.2 2002/10/04 13:37:14 rtahilia noship $ */
3
4 -- Type Declaration
5 TYPE IdTab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
6
7 -- Package Collections Variables
8 t_ColId IdTab;
9 t_RowId IdTab;
10
11 -- Package Variables
12 g_package VARCHAR2(33) := ' PQP_COPY_UDT.';
13 g_target_bg_id NUMBER(15);
14 g_udt_prefix VARCHAR2(15);
15
16 -- ---------------------------------------------------------------------------+
17 -- |------------------------< ADD_ID >---------------------------------------|
18 -- ---------------------------------------------------------------------------+
19 PROCEDURE add_id(p_curr_id IN NUMBER
20 ,p_new_id IN NUMBER
21 ,p_type_flag IN VARCHAR2
22 ) IS
23
24 l_proc VARCHAR2(72) := g_package||'add_id';
25
26 BEGIN
27 hr_utility.set_location('Entering:'|| l_proc, 10);
28
29
30 IF p_type_flag ='C' THEN
31 -- Column
32 t_ColId(p_curr_id) := p_new_id;
33 ELSE -- p_type_flag = 'R'
34 -- Row
35 t_RowId(p_curr_id) := p_new_id;
36 END IF;
37
38 hr_utility.set_location('Leaving:'|| l_proc, 20);
39 END; -- add_id
40
41 -- ---------------------------------------------------------------------------+
42 -- |------------------------< GET_NEW_USER_ROW_ID >---------------------------|
43 -- ---------------------------------------------------------------------------+
44 FUNCTION get_new_pur_id(p_curr_pur_id IN NUMBER) RETURN NUMBER IS
45
46 l_new_pur_id NUMBER(15);
47
48 l_proc VARCHAR2(72) := g_package||'get_new_user_row_id';
49
50 BEGIN
51
52 hr_utility.set_location('Entering:'|| l_proc, 10);
53
54 l_new_pur_id := t_RowId(p_curr_pur_id);
55
56 hr_utility.set_location('Leaving:'|| l_proc, 20);
57
58 RETURN l_new_pur_id;
59
60 END; -- get_new_pur_id
61 -- ---------------------------------------------------------------------------+
62 -- |------------------------< COPY_TABLE >------------------------------------|
63 -- ---------------------------------------------------------------------------+
64 FUNCTION copy_table(p_curr_put_id IN NUMBER) RETURN NUMBER IS
65
66 CURSOR c_user_table IS
67 SELECT *
68 FROM pay_user_tables
69 WHERE user_table_id = p_curr_put_id;
70
71 r_user_table c_user_table%ROWTYPE;
72
73 l_new_put_id NUMBER(9);
74 l_row_id VARCHAR2(18) := NULL;
75 l_new_user_table_name pay_user_tables.user_table_name%TYPE;
76
77 l_proc VARCHAR2(72) := g_package||'copy_table';
78
79 BEGIN
80 hr_utility.set_location('Entering:'|| l_proc, 10);
81
82 For r_user_table in c_user_table
83 Loop
84
85
86 -- Add prefix to user table name
87 -- Moved after the if statement as part of the UTF8 changes
88 -- l_new_user_table_name := g_udt_prefix||'_'||r_user_table.user_table_name;
89
90 -- Changed for UTF8
91 -- if length(l_new_user_table_name) > 80 then
92
93 if length(g_udt_prefix||'_'||r_user_table.user_table_name) > 80 then
94 fnd_message.set_name('PQP', 'PQP_230565_UDT_NAME_TOO_LONG');
95 fnd_message.raise_error;
96 end if;
97
98 -- Add prefix to user table name
99 l_new_user_table_name := g_udt_prefix||'_'||r_user_table.user_table_name;
100
101 -- Insert new row in PAY_USER_TABLES
102 pay_user_tables_pkg.insert_row
103 (p_rowid => l_row_id -- IN OUT
104 ,p_user_table_id => l_new_put_id -- IN OUT
105 ,p_business_group_id => g_target_bg_id
106 ,p_legislation_code => NULL
107 ,p_legislation_subgroup => NULL
108 ,p_range_or_match => r_user_table.range_or_match
109 ,p_user_key_units => r_user_table.user_key_units
110 ,p_user_table_name => l_new_user_table_name
111 ,p_user_row_title => r_user_table.user_row_title
112 );
113
114 End Loop;
115
116 hr_utility.set_location('Leaving:'|| l_proc, 20);
117
118 Return l_new_put_id;
119
120 END; -- copy_table
121
122 -- ---------------------------------------------------------------------------+
123 -- |------------------------< COPY_COLUMNS >----------------------------------|
124 -- ---------------------------------------------------------------------------+
125 PROCEDURE copy_columns(p_curr_put_id IN NUMBER
126 ,p_new_put_id IN NUMBER
127 ) IS
128
129 CURSOR c_user_columns IS
130 SELECT *
131 FROM pay_user_columns
132 WHERE user_table_id = p_curr_put_id;
133
134 r_user_columns c_user_columns%ROWTYPE;
135
136 l_new_puc_id NUMBER(9);
137 l_row_id VARCHAR2(18) := NULL;
138 l_proc VARCHAR2(72) := g_package||'copy_columns';
139
140 BEGIN
141
142 hr_utility.set_location('Entering:'|| l_proc, 10);
143
144 For r_user_columns in c_user_columns
145 Loop
146
147 -- Check column name unique
148 pay_user_columns_pkg.check_unique_f
149 (p_rowid => l_row_id
150 ,p_user_column_name => r_user_columns.user_column_name
151 ,p_user_table_id => p_new_put_id
152 ,p_business_group_id => g_target_bg_id
153 ,p_legislation_code => NULL
154 );
155
156 -- Insert new row into PAY_USER_COLUMNS
157 pay_user_columns_pkg.insert_row
158 (p_rowid => l_row_id -- IN OUT
159 ,p_user_column_id => l_new_puc_id -- IN OUT
160 ,p_user_table_id => p_new_put_id
161 ,p_business_group_id => g_target_bg_id
162 ,p_legislation_code => NULL
163 ,p_legislation_subgroup => NULL
164 ,p_user_column_name => r_user_columns.user_column_name
165 ,p_formula_id => r_user_columns.formula_id
166 );
167
168 -- Add column to list of copied columns
169 add_id(p_curr_id => r_user_columns.user_column_id
170 ,p_new_id => l_new_puc_id
171 ,p_type_flag => 'C'
172 );
173
174 End Loop;
175
176 hr_utility.set_location('Leaving:'|| l_proc, 20);
177 END; -- copy_columns
178
179 -- ---------------------------------------------------------------------------+
180 -- |------------------------< COPY_ROWS >-------------------------------------|
181 -- ---------------------------------------------------------------------------+
182 PROCEDURE copy_rows(p_curr_put_id IN NUMBER
183 ,p_new_put_id IN NUMBER
184 ) IS
185
186 CURSOR c_user_rows IS
187 SELECT *
188 FROM pay_user_rows_f
189 WHERE user_table_id = p_curr_put_id
190 ORDER BY user_row_id, effective_start_date;
191
192 r_user_rows c_user_rows%ROWTYPE;
193
194 l_prev_src_pur_id NUMBER(15) := NULL;
195 l_cre_new_pur_id BOOLEAN := FALSE;
196 l_new_pur_id NUMBER(15);
197 l_row_id VARCHAR2(18) := NULL;
198
199 l_proc VARCHAR2(72) := g_package||'copy_rows';
200
201 BEGIN
202
203 hr_utility.set_location('Entering:'|| l_proc, 10);
204
205 For r_user_rows in c_user_rows
206 Loop
207
208 -- Bugfix : 2257831
209 -- Don't create a new PUR Id if its a datetrack changed row
210 IF NVL(l_prev_src_pur_id,-1) <> r_user_rows.user_row_id THEN
211 l_cre_new_pur_id := TRUE;
212 l_prev_src_pur_id := r_user_rows.user_row_id;
213 ELSE
214 l_cre_new_pur_id := FALSE;
215 END IF;
216
217 IF l_cre_new_pur_id THEN
218
219 -- Pre Insert step for PAY_USER_ROWS_F
220 pay_user_rows_pkg.pre_insert
221 (p_rowid => l_row_id
222 ,p_user_table_id => p_new_put_id
223 ,p_row_low_range_or_name => r_user_rows.row_low_range_or_name
224 ,p_user_row_id => l_new_pur_id -- OUT
225 ,p_business_group_id => g_target_bg_id
226 );
227
228 END IF;
229
230 -- Insert new row into PAY_USER_ROWS_F
231 INSERT INTO pay_user_rows_f
232 (user_row_id
233 ,effective_start_date
234 ,effective_end_date
235 ,business_group_id
236 ,legislation_code
237 ,user_table_id
238 ,row_low_range_or_name
239 ,display_sequence
240 ,legislation_subgroup
241 ,row_high_range
242 ,last_update_date
243 ,last_updated_by
244 ,last_update_login
245 ,created_by
246 ,creation_date
247 )
248 values
249 (l_new_pur_id
250 ,r_user_rows.effective_start_date
251 ,r_user_rows.effective_end_date
252 ,g_target_bg_id
253 ,NULL
254 ,p_new_put_id
255 ,r_user_rows.row_low_range_or_name
256 ,r_user_rows.display_sequence
257 ,NULL
258 ,r_user_rows.row_high_range
259 ,r_user_rows.last_update_date
260 ,r_user_rows.last_updated_by
261 ,r_user_rows.last_update_login
262 ,r_user_rows.created_by
263 ,r_user_rows.creation_date
264 );
265
266 -- Bugfix : 2257831
267 IF l_cre_new_pur_id THEN
268 -- Add row to list of copied rows
269 add_id(p_curr_id => r_user_rows.user_row_id
270 ,p_new_id => l_new_pur_id
271 ,p_type_flag => 'R'
272 );
273 END IF;
274 --
275 End Loop;
276
277 hr_utility.set_location('Leaving:'|| l_proc, 20);
278
279 END; -- copy_rows
280
281 -- ---------------------------------------------------------------------------+
282 -- |------------------------< COPY_COLUMN_INSTANCES >-------------------------|
283 -- ---------------------------------------------------------------------------+
284 PROCEDURE copy_column_instances IS
285
286 CURSOR c_user_ci(p_user_column_id IN NUMBER) IS
287 SELECT *
288 FROM pay_user_column_instances_f
289 WHERE user_column_id = p_user_column_id
290 ORDER BY user_column_instance_id, effective_start_date;
291
292 r_user_ci c_user_ci%ROWTYPE;
293
294 l_curr_puc_id NUMBER(15);
295 l_new_puc_id NUMBER(15);
296
297 l_new_pur_id NUMBER(15);
298
299 l_prev_src_puci_id NUMBER(15) := NULL;
300 l_cre_new_puci_id BOOLEAN := FALSE;
301 l_prev_new_puci_id NUMBER(15);
302
303 l_new_puci_id NUMBER(15);
304 l_row_id VARCHAR2(18) := NULL;
305
306 l_proc VARCHAR2(72) := g_package||'copy_column_instances';
307
308 BEGIN
309
310 hr_utility.set_location('Entering:'|| l_proc, 10);
311
312 For l_cntr IN nvl(t_ColId.FIRST,-1)..nvl(t_ColId.LAST,-2)
313 Loop -- 1
314
315 If t_ColId.EXISTS(l_cntr) then
316 l_new_puc_id := t_ColId(l_cntr);
317 l_curr_puc_id := l_cntr;
318
319 l_prev_src_puci_id := NULL;
320 l_cre_new_puci_id := FALSE;
321 l_prev_new_puci_id := NULL;
322
323 -- Copy all instances for this column
324 For r_user_ci IN c_user_ci(l_curr_puc_id)
325 Loop -- 2
326
327 -- Bugfix : 2257831
328 -- Don't create a new PUR Id if its a datetrack changed row
329 -- However, pay_user_column_instances_pkg.insert_row always
330 -- creates a new PUCI Id, so we will first insert a new row
331 -- and then update it with the PUCI Id of the previous row.
332 -- Disadvantage of this approach will be the wasting
333 -- of sequence values
334 IF NVL(l_prev_src_puci_id,-1) <> r_user_ci.user_column_instance_id THEN
335 l_cre_new_puci_id := TRUE;
336 l_prev_src_puci_id := r_user_ci.user_column_instance_id;
337 ELSE
338 l_cre_new_puci_id := FALSE;
339 END IF;
340
341 -- For the current USER_ROW_ID, find the corresponding new USER_ROW_ID
342 l_new_pur_id := get_new_pur_id(p_curr_pur_id => r_user_ci.user_row_id);
343
344 -- Insert into PAY_USER_COLUMN_INSTANCES_F
345 pay_user_column_instances_pkg.insert_row
346 (p_rowid => l_row_id -- IN OUT
347 ,p_user_column_instance_id => l_new_puci_id -- IN OUT
348 ,p_effective_start_date => r_user_ci.effective_start_date
349 ,p_effective_end_date => r_user_ci.effective_end_date
350 ,p_user_column_id => l_new_puc_id
351 ,p_user_row_id => l_new_pur_id
352 ,p_business_group_id => g_target_bg_id
353 ,p_legislation_code => NULL
354 ,p_legislation_subgroup => NULL
355 ,p_value => r_user_ci.value
356 );
357
358 -- Bugfix : 2257831
359 IF l_cre_new_puci_id THEN
360
361 l_prev_new_puci_id := l_new_puci_id;
362
363 ELSE
364
365 -- Update the newly inserted row with the PUCI Id of previous row
366 -- l_row_id stores the ROWID of the newly inserted row
367 pay_user_column_instances_pkg.update_row
368 (p_rowid => l_row_id -- IN, used to update row
369 ,p_user_column_instance_id => l_prev_new_puci_id
370 ,p_effective_start_date => r_user_ci.effective_start_date
371 ,p_effective_end_date => r_user_ci.effective_end_date
372 ,p_user_column_id => l_new_puc_id
373 ,p_user_row_id => l_new_pur_id
374 ,p_business_group_id => g_target_bg_id
375 ,p_legislation_code => NULL
376 ,p_legislation_subgroup => NULL
377 ,p_value => r_user_ci.value
378 );
379
380 END IF; -- l_cre_new_puci_id THEN
381 --
382 End Loop; -- 2
383 --
384 End if; -- t_ColId.EXISTS
385 --
386 End Loop; -- 1
387
388 hr_utility.set_location('Leaving:'|| l_proc, 20);
389
390 END; -- copy_column_instances
391
392 -- ---------------------------------------------------------------------------+
393 -- |------------------------< COPY_USER_TABLE >-------------------------------|
394 -- ---------------------------------------------------------------------------+
395 FUNCTION copy_user_table(p_curr_udt_id IN NUMBER
396 ,p_udt_prefix IN VARCHAR2
397 ,p_business_group_id IN NUMBER
398 ) RETURN NUMBER IS
399
400 l_new_put_id NUMBER(15);
401
402 l_proc VARCHAR2(72) := g_package||'copy_user_table';
403
404 BEGIN
405
406 hr_utility.set_location('Entering:'|| l_proc, 10);
407
408 -- Set package variables
409 g_target_bg_id := p_business_group_id;
410 g_udt_prefix := upper(p_udt_prefix);
411
412 -- Copy PAY_USER_TABLES data
413 l_new_put_id := copy_table(p_curr_put_id => p_curr_udt_id);
414
415 -- Copy PAY_USER_COLUMNS data
416 copy_columns(p_curr_put_id => p_curr_udt_id
417 ,p_new_put_id => l_new_put_id
418 );
419
420 -- Copy PAY_USER_ROWS_F data
421 copy_rows(p_curr_put_id => p_curr_udt_id
422 ,p_new_put_id => l_new_put_id
423 );
424
425 -- Copy PAY_USER_COLUMN_INSTANCES_F data
426 copy_column_instances;
427
428 hr_utility.set_location('Leaving:'|| l_proc, 20);
429
430 -- Return the Id of the new User Table
431 RETURN l_new_put_id;
432
433 END; -- copy_user_table
434
435 END pqp_copy_udt;