1 Package Body hr_scl_upd as
2 /* $Header: hrsclrhi.pkb 115.3 2002/12/03 08:23:56 raranjan ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_scl_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------------< convert_defs >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure convert_defs(p_rec in out nocopy hr_scl_shd.g_rec_type) is
14 --
15 l_proc varchar2(72) := g_package||'convert_defs';
16 --
17 Begin
18 --
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 -- We must now examine each argument value in the
22 -- p_rec plsql record structure
23 -- to see if a system default is being used. If a system default
24 -- is being used then we must set to the 'current' argument value.
25 --
26 If (p_rec.concatenated_segments = hr_api.g_varchar2) then
27 p_rec.concatenated_segments :=
28 hr_scl_shd.g_old_rec.concatenated_segments;
29 End If;
30 If (p_rec.request_id = hr_api.g_number) then
31 p_rec.request_id :=
32 hr_scl_shd.g_old_rec.request_id;
33 End If;
34 If (p_rec.program_application_id = hr_api.g_number) then
35 p_rec.program_application_id :=
36 hr_scl_shd.g_old_rec.program_application_id;
37 End If;
38 If (p_rec.program_id = hr_api.g_number) then
39 p_rec.program_id :=
40 hr_scl_shd.g_old_rec.program_id;
41 End If;
42 If (p_rec.program_update_date = hr_api.g_date) then
43 p_rec.program_update_date :=
44 hr_scl_shd.g_old_rec.program_update_date;
45 End If;
46 If (p_rec.id_flex_num = hr_api.g_number) then
47 p_rec.id_flex_num :=
48 hr_scl_shd.g_old_rec.id_flex_num;
49 End If;
50 If (p_rec.summary_flag = hr_api.g_varchar2) then
51 p_rec.summary_flag :=
52 hr_scl_shd.g_old_rec.summary_flag;
53 End If;
54 If (p_rec.enabled_flag = hr_api.g_varchar2) then
55 p_rec.enabled_flag :=
56 hr_scl_shd.g_old_rec.enabled_flag;
57 End If;
58 If (p_rec.start_date_active = hr_api.g_date) then
59 p_rec.start_date_active :=
60 hr_scl_shd.g_old_rec.start_date_active;
61 End If;
62 If (p_rec.end_date_active = hr_api.g_date) then
63 p_rec.end_date_active :=
64 hr_scl_shd.g_old_rec.end_date_active;
65 End If;
66 If (p_rec.segment1 = hr_api.g_varchar2) then
67 p_rec.segment1 :=
68 hr_scl_shd.g_old_rec.segment1;
69 End If;
70 If (p_rec.segment2 = hr_api.g_varchar2) then
71 p_rec.segment2 :=
72 hr_scl_shd.g_old_rec.segment2;
73 End If;
74 If (p_rec.segment3 = hr_api.g_varchar2) then
75 p_rec.segment3 :=
76 hr_scl_shd.g_old_rec.segment3;
77 End If;
78 If (p_rec.segment4 = hr_api.g_varchar2) then
79 p_rec.segment4 :=
80 hr_scl_shd.g_old_rec.segment4;
81 End If;
82 If (p_rec.segment5 = hr_api.g_varchar2) then
83 p_rec.segment5 :=
84 hr_scl_shd.g_old_rec.segment5;
85 End If;
86 If (p_rec.segment6 = hr_api.g_varchar2) then
87 p_rec.segment6 :=
88 hr_scl_shd.g_old_rec.segment6;
89 End If;
90 If (p_rec.segment7 = hr_api.g_varchar2) then
91 p_rec.segment7 :=
92 hr_scl_shd.g_old_rec.segment7;
93 End If;
94 If (p_rec.segment8 = hr_api.g_varchar2) then
95 p_rec.segment8 :=
96 hr_scl_shd.g_old_rec.segment8;
97 End If;
98 If (p_rec.segment9 = hr_api.g_varchar2) then
99 p_rec.segment9 :=
100 hr_scl_shd.g_old_rec.segment9;
101 End If;
102 If (p_rec.segment10 = hr_api.g_varchar2) then
103 p_rec.segment10 :=
104 hr_scl_shd.g_old_rec.segment10;
105 End If;
106 If (p_rec.segment11 = hr_api.g_varchar2) then
107 p_rec.segment11 :=
108 hr_scl_shd.g_old_rec.segment11;
109 End If;
110 If (p_rec.segment12 = hr_api.g_varchar2) then
111 p_rec.segment12 :=
112 hr_scl_shd.g_old_rec.segment12;
113 End If;
114 If (p_rec.segment13 = hr_api.g_varchar2) then
115 p_rec.segment13 :=
116 hr_scl_shd.g_old_rec.segment13;
117 End If;
118 If (p_rec.segment14 = hr_api.g_varchar2) then
119 p_rec.segment14 :=
120 hr_scl_shd.g_old_rec.segment14;
121 End If;
122 If (p_rec.segment15 = hr_api.g_varchar2) then
123 p_rec.segment15 :=
124 hr_scl_shd.g_old_rec.segment15;
125 End If;
126 If (p_rec.segment16 = hr_api.g_varchar2) then
127 p_rec.segment16 :=
128 hr_scl_shd.g_old_rec.segment16;
129 End If;
130 If (p_rec.segment17 = hr_api.g_varchar2) then
131 p_rec.segment17 :=
132 hr_scl_shd.g_old_rec.segment17;
133 End If;
134 If (p_rec.segment18 = hr_api.g_varchar2) then
135 p_rec.segment18 :=
136 hr_scl_shd.g_old_rec.segment18;
137 End If;
138 If (p_rec.segment19 = hr_api.g_varchar2) then
139 p_rec.segment19 :=
140 hr_scl_shd.g_old_rec.segment19;
141 End If;
142 If (p_rec.segment20 = hr_api.g_varchar2) then
143 p_rec.segment20 :=
144 hr_scl_shd.g_old_rec.segment20;
145 End If;
146 If (p_rec.segment21 = hr_api.g_varchar2) then
147 p_rec.segment21 :=
148 hr_scl_shd.g_old_rec.segment21;
149 End If;
150 If (p_rec.segment22 = hr_api.g_varchar2) then
151 p_rec.segment22 :=
152 hr_scl_shd.g_old_rec.segment22;
153 End If;
154 If (p_rec.segment23 = hr_api.g_varchar2) then
155 p_rec.segment23 :=
156 hr_scl_shd.g_old_rec.segment23;
157 End If;
158 If (p_rec.segment24 = hr_api.g_varchar2) then
159 p_rec.segment24 :=
160 hr_scl_shd.g_old_rec.segment24;
161 End If;
162 If (p_rec.segment25 = hr_api.g_varchar2) then
163 p_rec.segment25 :=
164 hr_scl_shd.g_old_rec.segment25;
165 End If;
166 If (p_rec.segment26 = hr_api.g_varchar2) then
167 p_rec.segment26 :=
168 hr_scl_shd.g_old_rec.segment26;
169 End If;
170 If (p_rec.segment27 = hr_api.g_varchar2) then
171 p_rec.segment27 :=
172 hr_scl_shd.g_old_rec.segment27;
173 End If;
174 If (p_rec.segment28 = hr_api.g_varchar2) then
175 p_rec.segment28 :=
176 hr_scl_shd.g_old_rec.segment28;
177 End If;
178 If (p_rec.segment29 = hr_api.g_varchar2) then
179 p_rec.segment29 :=
180 hr_scl_shd.g_old_rec.segment29;
181 End If;
182 If (p_rec.segment30 = hr_api.g_varchar2) then
183 p_rec.segment30 :=
184 hr_scl_shd.g_old_rec.segment30;
185 End If;
186 --
187 hr_utility.set_location(' Leaving:'||l_proc, 10);
188 --
189 End convert_defs;
190 --
191 -- ----------------------------------------------------------------------------
192 -- |-------------------------------< upd_or_sel >-----------------------------|
193 -- ----------------------------------------------------------------------------
194 --
195 procedure upd_or_sel
196 (p_segment1 in varchar2 default hr_api.g_varchar2,
197 p_segment2 in varchar2 default hr_api.g_varchar2,
198 p_segment3 in varchar2 default hr_api.g_varchar2,
199 p_segment4 in varchar2 default hr_api.g_varchar2,
200 p_segment5 in varchar2 default hr_api.g_varchar2,
201 p_segment6 in varchar2 default hr_api.g_varchar2,
202 p_segment7 in varchar2 default hr_api.g_varchar2,
203 p_segment8 in varchar2 default hr_api.g_varchar2,
204 p_segment9 in varchar2 default hr_api.g_varchar2,
205 p_segment10 in varchar2 default hr_api.g_varchar2,
206 p_segment11 in varchar2 default hr_api.g_varchar2,
207 p_segment12 in varchar2 default hr_api.g_varchar2,
208 p_segment13 in varchar2 default hr_api.g_varchar2,
209 p_segment14 in varchar2 default hr_api.g_varchar2,
210 p_segment15 in varchar2 default hr_api.g_varchar2,
211 p_segment16 in varchar2 default hr_api.g_varchar2,
212 p_segment17 in varchar2 default hr_api.g_varchar2,
213 p_segment18 in varchar2 default hr_api.g_varchar2,
214 p_segment19 in varchar2 default hr_api.g_varchar2,
215 p_segment20 in varchar2 default hr_api.g_varchar2,
216 p_segment21 in varchar2 default hr_api.g_varchar2,
217 p_segment22 in varchar2 default hr_api.g_varchar2,
218 p_segment23 in varchar2 default hr_api.g_varchar2,
219 p_segment24 in varchar2 default hr_api.g_varchar2,
220 p_segment25 in varchar2 default hr_api.g_varchar2,
221 p_segment26 in varchar2 default hr_api.g_varchar2,
222 p_segment27 in varchar2 default hr_api.g_varchar2,
223 p_segment28 in varchar2 default hr_api.g_varchar2,
224 p_segment29 in varchar2 default hr_api.g_varchar2,
225 p_segment30 in varchar2 default hr_api.g_varchar2,
226 p_business_group_id in number,
227 p_request_id in number default hr_api.g_number,
228 p_program_application_id in number default hr_api.g_number,
229 p_program_id in number default hr_api.g_number,
230 p_program_update_date in date default hr_api.g_date,
231 p_soft_coding_keyflex_id in out nocopy number,
232 p_concatenated_segments out nocopy varchar2,
233 p_validate in boolean default false) is
234 --
235 l_proc varchar2(72) := g_package||'upd_or_sel';
236 l_rec hr_scl_shd.g_rec_type;
237 --
238 begin
239 hr_utility.set_location('Entering:'||l_proc, 5);
240 --
241 -- convert args into record format
242 --
243 l_rec :=
244 hr_scl_shd.convert_args
245 (p_soft_coding_keyflex_id,
246 null,
247 p_request_id,
248 p_program_application_id,
249 p_program_id,
250 p_program_update_date,
251 null,
252 null,
253 null,
254 null,
255 null,
256 p_segment1,
257 p_segment2,
258 p_segment3,
259 p_segment4,
260 p_segment5,
261 p_segment6,
262 p_segment7,
263 p_segment8,
264 p_segment9,
265 p_segment10,
266 p_segment11,
267 p_segment12,
268 p_segment13,
269 p_segment14,
270 p_segment15,
271 p_segment16,
272 p_segment17,
273 p_segment18,
274 p_segment19,
275 p_segment20,
276 p_segment21,
277 p_segment22,
278 p_segment23,
279 p_segment24,
280 p_segment25,
281 p_segment26,
282 p_segment27,
283 p_segment28,
284 p_segment29,
285 p_segment30);
286 --
287 -- check to see if we are updating a row
288 --
289 if hr_scl_shd.api_updating
290 (p_soft_coding_keyflex_id => l_rec.soft_coding_keyflex_id) then
291 --
292 hr_utility.set_location(l_proc, 10);
293 --
294 -- the current row exists and we have populated the g_old_rec
295 -- we must now build up the new record by converting the
296 -- arguments into a record structure and converting any of the
297 -- system default values
298 --
299 hr_scl_upd.convert_defs(p_rec => l_rec);
300 else
301 --
302 -- as we are actually doing an insert we need to reset the
303 -- arguments if they are using a system default value
304 -- to null otherwise the segments will have the system default
305 -- values when being inserted
306 --
307 if (l_rec.segment1 = hr_api.g_varchar2) then
308 l_rec.segment1 := null;
309 end if;
310 if (l_rec.segment2 = hr_api.g_varchar2) then
311 l_rec.segment2 := null;
312 end if;
313 if (l_rec.segment3 = hr_api.g_varchar2) then
314 l_rec.segment3 := null;
315 end if;
316 if (l_rec.segment4 = hr_api.g_varchar2) then
317 l_rec.segment4 := null;
318 end if;
319 if (l_rec.segment5 = hr_api.g_varchar2) then
320 l_rec.segment5 := null;
321 end if;
322 if (l_rec.segment6 = hr_api.g_varchar2) then
323 l_rec.segment6 := null;
324 end if;
325 if (l_rec.segment7 = hr_api.g_varchar2) then
326 l_rec.segment7 := null;
327 end if;
328 if (l_rec.segment8 = hr_api.g_varchar2) then
329 l_rec.segment8 := null;
330 end if;
331 if (l_rec.segment9 = hr_api.g_varchar2) then
332 l_rec.segment9 := null;
333 end if;
334 if (l_rec.segment10 = hr_api.g_varchar2) then
335 l_rec.segment10 := null;
336 end if;
337 if (l_rec.segment11 = hr_api.g_varchar2) then
338 l_rec.segment11 := null;
339 end if;
340 if (l_rec.segment12 = hr_api.g_varchar2) then
341 l_rec.segment12 := null;
342 end if;
343 if (l_rec.segment13 = hr_api.g_varchar2) then
344 l_rec.segment13 := null;
345 end if;
346 if (l_rec.segment14 = hr_api.g_varchar2) then
347 l_rec.segment14 := null;
348 end if;
349 if (l_rec.segment15 = hr_api.g_varchar2) then
350 l_rec.segment15 := null;
351 end if;
352 if (l_rec.segment16 = hr_api.g_varchar2) then
353 l_rec.segment16 := null;
354 end if;
355 if (l_rec.segment17 = hr_api.g_varchar2) then
356 l_rec.segment17 := null;
357 end if;
358 if (l_rec.segment18 = hr_api.g_varchar2) then
359 l_rec.segment18 := null;
360 end if;
361 if (l_rec.segment19 = hr_api.g_varchar2) then
362 l_rec.segment19 := null;
363 end if;
364 if (l_rec.segment20 = hr_api.g_varchar2) then
365 l_rec.segment20 := null;
366 end if;
367 if (l_rec.segment21 = hr_api.g_varchar2) then
368 l_rec.segment21 := null;
369 end if;
370 if (l_rec.segment22 = hr_api.g_varchar2) then
371 l_rec.segment22 := null;
372 end if;
373 if (l_rec.segment23 = hr_api.g_varchar2) then
374 l_rec.segment23 := null;
375 end if;
376 if (l_rec.segment24 = hr_api.g_varchar2) then
377 l_rec.segment24 := null;
378 end if;
379 if (l_rec.segment25 = hr_api.g_varchar2) then
380 l_rec.segment25 := null;
381 end if;
382 if (l_rec.segment26 = hr_api.g_varchar2) then
383 l_rec.segment26 := null;
384 end if;
385 if (l_rec.segment27 = hr_api.g_varchar2) then
386 l_rec.segment27 := null;
387 end if;
388 if (l_rec.segment28 = hr_api.g_varchar2) then
389 l_rec.segment28 := null;
390 end if;
391 if (l_rec.segment29 = hr_api.g_varchar2) then
392 l_rec.segment29 := null;
393 end if;
394 if (l_rec.segment30 = hr_api.g_varchar2) then
395 l_rec.segment30 := null;
396 end if;
397 if (l_rec.request_id = hr_api.g_number) then
398 l_rec.request_id := null;
399 end if;
400 if (l_rec.program_application_id = hr_api.g_number) then
401 l_rec.program_application_id := null;
402 end if;
403 if (l_rec.program_id = hr_api.g_number) then
404 l_rec.program_id := null;
405 end if;
406 if (l_rec.program_update_date = hr_api.g_date) then
407 l_rec.program_update_date := null;
408 end if;
409 end if;
410 hr_utility.set_location(l_proc, 15);
411 --
412 -- call the ins_or_sel process
413 --
414 hr_scl_ins.ins_or_sel
415 (p_segment1 => l_rec.segment1,
416 p_segment2 => l_rec.segment2,
417 p_segment3 => l_rec.segment3,
418 p_segment4 => l_rec.segment4,
419 p_segment5 => l_rec.segment5,
420 p_segment6 => l_rec.segment6,
421 p_segment7 => l_rec.segment7,
422 p_segment8 => l_rec.segment8,
423 p_segment9 => l_rec.segment9,
424 p_segment10 => l_rec.segment10,
425 p_segment11 => l_rec.segment11,
426 p_segment12 => l_rec.segment12,
427 p_segment13 => l_rec.segment13,
428 p_segment14 => l_rec.segment14,
429 p_segment15 => l_rec.segment15,
430 p_segment16 => l_rec.segment16,
431 p_segment17 => l_rec.segment17,
432 p_segment18 => l_rec.segment18,
433 p_segment19 => l_rec.segment19,
434 p_segment20 => l_rec.segment20,
435 p_segment21 => l_rec.segment21,
436 p_segment22 => l_rec.segment22,
437 p_segment23 => l_rec.segment23,
438 p_segment24 => l_rec.segment24,
439 p_segment25 => l_rec.segment25,
440 p_segment26 => l_rec.segment26,
441 p_segment27 => l_rec.segment27,
442 p_segment28 => l_rec.segment28,
443 p_segment29 => l_rec.segment29,
444 p_segment30 => l_rec.segment30,
445 p_business_group_id => p_business_group_id,
446 p_request_id => l_rec.request_id,
447 p_program_application_id => l_rec.program_application_id,
448 p_program_id => l_rec.program_id,
449 p_program_update_date => l_rec.program_update_date,
450 p_soft_coding_keyflex_id => l_rec.soft_coding_keyflex_id,
451 p_concatenated_segments => l_rec.concatenated_segments,
452 p_validate => p_validate);
453 --
454 -- set the out arguments
455 --
456 p_soft_coding_keyflex_id := l_rec.soft_coding_keyflex_id;
457 p_concatenated_segments := l_rec.concatenated_segments;
458 hr_utility.set_location(' Leaving:'||l_proc, 20);
459 --
460 end upd_or_sel;
461 --
462 end hr_scl_upd;