[Home] [Help]
PACKAGE BODY: APPS.PQH_RST_SHD
Source
1 Package Body pqh_rst_shd as
2 /* $Header: pqrstrhi.pkb 120.5 2011/04/28 09:38:46 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rst_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- ----------------------------------------------------------------------------
12 -- |---------------------------< constraint_error >---------------------------|
13 -- ----------------------------------------------------------------------------
14 Procedure constraint_error
15 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 hr_utility.set_location('Entering:'||l_proc, 5);
21 --
22 If (p_constraint_name = 'HR_ALL_ORGANIZATION_UNITS_FK1') Then
23 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
24 hr_utility.set_message_token('PROCEDURE', l_proc);
25 hr_utility.set_message_token('STEP','5');
26 hr_utility.raise_error;
27 ElsIf (p_constraint_name = 'PQH_RULE_SETS_FK2') Then
28 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
29 hr_utility.set_message_token('PROCEDURE', l_proc);
30 hr_utility.set_message_token('STEP','10');
31 hr_utility.raise_error;
32 ElsIf (p_constraint_name = 'PQH_RULE_SETS_FK3') Then
33 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
34 hr_utility.set_message_token('PROCEDURE', l_proc);
35 hr_utility.set_message_token('STEP','15');
36 hr_utility.raise_error;
37 ElsIf (p_constraint_name = 'PQH_RULE_SETS_FK4') Then
38 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
39 hr_utility.set_message_token('PROCEDURE', l_proc);
40 hr_utility.set_message_token('STEP','20');
41 hr_utility.raise_error;
42 ElsIf (p_constraint_name = 'PQH_RULE_SETS_UK') Then
43 hr_utility.set_message(8302, 'PQH_DUPL_SHORT_NAME');
44 hr_utility.raise_error;
45 ElsIf (p_constraint_name = 'RULE_SETS_PK') Then
46 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
47 hr_utility.set_message_token('PROCEDURE', l_proc);
48 hr_utility.set_message_token('STEP','30');
49 hr_utility.raise_error;
50 Else
51 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
52 hr_utility.set_message_token('PROCEDURE', l_proc);
53 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
54 hr_utility.raise_error;
55 End If;
56 --
57 hr_utility.set_location(' Leaving:'||l_proc, 10);
58 End constraint_error;
59 --
60 -- ----------------------------------------------------------------------------
61 -- |-----------------------------< api_updating >-----------------------------|
62 -- ----------------------------------------------------------------------------
63 Function api_updating
64 (
65 p_rule_set_id in number,
66 p_object_version_number in number
67 ) Return Boolean Is
68 --
69 --
70 -- Cursor selects the 'current' row from the HR Schema
71 --
72 Cursor C_Sel1 is
73 select
74 business_group_id,
75 rule_set_id,
76 rule_set_name,
77 organization_structure_id,
78 organization_id,
79 referenced_rule_set_id,
80 rule_level_cd,
81 object_version_number,
82 short_name,
83 rule_applicability,
84 rule_category,
85 starting_organization_id,
86 seeded_rule_flag,
87 status
88 from pqh_rule_sets
89 where rule_set_id = p_rule_set_id;
90 --
91 l_proc varchar2(72) := g_package||'api_updating';
92 l_fct_ret boolean;
93 --
94 Begin
95 hr_utility.set_location('Entering:'||l_proc, 5);
96 --
97 If (
98 p_rule_set_id is null and
99 p_object_version_number is null
100 ) Then
101 --
102 -- One of the primary key arguments is null therefore we must
103 -- set the returning function value to false
104 --
105 l_fct_ret := false;
106 Else
107 If (
108 p_rule_set_id = g_old_rec.rule_set_id and
109 p_object_version_number = g_old_rec.object_version_number
110 ) Then
111 hr_utility.set_location(l_proc, 10);
112 --
113 -- The g_old_rec is current therefore we must
114 -- set the returning function to true
115 --
116 l_fct_ret := true;
117 Else
118 --
119 -- Select the current row into g_old_rec
120 --
121 Open C_Sel1;
122 Fetch C_Sel1 Into g_old_rec;
123 If C_Sel1%notfound Then
124 Close C_Sel1;
125 --
126 -- The primary key is invalid therefore we must error
127 --
128 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
129 hr_utility.raise_error;
130 End If;
131 Close C_Sel1;
132 If (p_object_version_number <> g_old_rec.object_version_number) Then
133 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
134 hr_utility.raise_error;
135 End If;
136 hr_utility.set_location(l_proc, 15);
137 l_fct_ret := true;
138 End If;
139 End If;
140 hr_utility.set_location(' Leaving:'||l_proc, 20);
141 Return (l_fct_ret);
142 --
143 End api_updating;
144 --
145 -- ----------------------------------------------------------------------------
146 -- |---------------------------------< lck >----------------------------------|
147 -- ----------------------------------------------------------------------------
148 Procedure lck
149 (
150 p_rule_set_id in number,
151 p_object_version_number in number
152 ) is
153 --
154 -- Cursor selects the 'current' row from the HR Schema
155 --
156 Cursor C_Sel1 is
157 select business_group_id,
158 rule_set_id,
159 rule_set_name,
160 organization_structure_id,
161 organization_id,
162 referenced_rule_set_id,
163 rule_level_cd,
164 object_version_number,
165 short_name,
166 rule_applicability,
167 rule_category,
168 starting_organization_id,
169 seeded_rule_flag,
170 status
171 from pqh_rule_sets
172 where rule_set_id = p_rule_set_id
173 for update nowait;
174 --
175 l_proc varchar2(72) := g_package||'lck';
176 --
177 Begin
178 hr_utility.set_location('Entering:'||l_proc, 5);
179 --
180 -- Add any mandatory argument checking here:
181 -- Example:
182 -- hr_api.mandatory_arg_error
183 -- (p_api_name => l_proc,
184 -- p_argument => 'object_version_number',
185 -- p_argument_value => p_object_version_number);
186 --
187 Open C_Sel1;
188 Fetch C_Sel1 Into g_old_rec;
189 If C_Sel1%notfound then
190 Close C_Sel1;
191 --
192 -- The primary key is invalid therefore we must error
193 --
194 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
195 hr_utility.raise_error;
196 End If;
197 Close C_Sel1;
198 If (p_object_version_number <> g_old_rec.object_version_number) Then
199 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
200 hr_utility.raise_error;
201 End If;
202 --
203 hr_utility.set_location(' Leaving:'||l_proc, 10);
204 --
205 -- We need to trap the ORA LOCK exception
206 --
207 Exception
208 When HR_Api.Object_Locked then
209 --
210 -- The object is locked therefore we need to supply a meaningful
211 -- error message.
212 --
213 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
214 hr_utility.set_message_token('TABLE_NAME', 'pqh_rule_sets');
215 hr_utility.raise_error;
216 End lck;
217 --
218 -- ----------------------------------------------------------------------------
219 -- |-----------------------------< convert_args >-----------------------------|
220 -- ----------------------------------------------------------------------------
221 Function convert_args
222 (
223 p_business_group_id in number,
224 p_rule_set_id in number,
225 p_rule_set_name in varchar2,
226 p_organization_structure_id in number,
227 p_organization_id in number,
228 p_referenced_rule_set_id in number,
229 p_rule_level_cd in varchar2,
230 p_object_version_number in number,
231 p_short_name in varchar2,
232 p_rule_applicability in varchar2,
233 p_rule_category in varchar2,
234 p_starting_organization_id in number,
235 p_seeded_rule_flag in varchar2,
236 p_status in varchar2
237 )
238 Return g_rec_type is
239 --
240 l_rec g_rec_type;
241 l_proc varchar2(72) := g_package||'convert_args';
242 --
243 Begin
244 --
245 hr_utility.set_location('Entering:'||l_proc, 5);
246 --
247 -- Convert arguments into local l_rec structure.
248 --
249 l_rec.business_group_id := p_business_group_id;
250 l_rec.rule_set_id := p_rule_set_id;
251 l_rec.rule_set_name := p_rule_set_name;
252 l_rec.organization_structure_id := p_organization_structure_id;
253 l_rec.organization_id := p_organization_id;
254 l_rec.referenced_rule_set_id := p_referenced_rule_set_id;
255 l_rec.rule_level_cd := p_rule_level_cd;
256 l_rec.object_version_number := p_object_version_number;
257 l_rec.short_name := p_short_name;
258 l_rec.rule_applicability := p_rule_applicability;
259 l_rec.rule_category := p_rule_category;
260 l_rec.starting_organization_id := p_starting_organization_id;
261 l_rec.seeded_rule_flag := p_seeded_rule_flag;
262 l_rec.status := p_status;
263 --
264 -- Return the plsql record structure.
265 --
266 hr_utility.set_location(' Leaving:'||l_proc, 10);
267 Return(l_rec);
268 --
269 End convert_args;
270 --
271 --
272 Procedure load_row
273 (
274 p_short_name in varchar2
275 ,p_rule_set_name in varchar2
276 ,p_description in varchar2
277 ,p_referenced_rule_set_name in varchar2
278 ,p_rule_level_cd in varchar2
279 ,p_rule_category in varchar2
280 ,p_rule_applicability in varchar2
281 ,p_owner in varchar2
282 ,p_last_update_date in varchar2
283 ) is
284 --
285 l_effective_date date := sysdate ;
286 l_object_version_number number := 1;
287 l_language varchar2(30) ;
288
289 --
290 l_rule_set_id pqh_rule_sets.rule_set_id%type := 0;
291 l_referenced_rule_set_id pqh_rule_sets.referenced_rule_set_id%type;
292 --
293 --
294 l_created_by pqh_rule_sets.created_by%TYPE;
295 l_last_updated_by pqh_rule_sets.last_updated_by%TYPE;
296 l_creation_date pqh_rule_sets.creation_date%TYPE;
297 l_last_update_date pqh_rule_sets.last_update_date%TYPE;
298 l_last_update_login pqh_rule_sets.last_update_login%TYPE;
299 --
300 --
301 cursor c1 is select userenv('LANG') from dual ;
302 --
303 Cursor c5(p_short_name in varchar2) is
304 select rule_set_id
305 from pqh_rule_sets
306 where short_name = p_short_name ;
307 --
308 --
309 Cursor C_Sel1 is select pqh_rule_sets_s.nextval from sys.dual;
310 --
311 --
312 BEGIN
313 --
314 open c1;
315 fetch c1 into l_language ;
316 close c1;
317 --
318 Open c5(p_short_name => p_short_name);
319 Fetch c5 into l_rule_set_id;
320 Close c5;
321 --
322 Open c5(p_short_name => p_referenced_rule_set_name);
323 Fetch c5 into l_referenced_rule_set_id;
324 Close c5;
325 --
326 -- populate WHO columns
327 --
328 /**
329 if p_owner = 'SEED' then
330 l_created_by := 1;
331 l_last_updated_by := -1;
332 else
333 l_created_by := 0;
334 l_last_updated_by := -1;
335 end if;
336 **/
337 l_last_updated_by := fnd_load_util.owner_id(p_owner);
338 l_created_by := fnd_load_util.owner_id(p_owner);
339 /**
340 l_creation_date := sysdate;
341 l_last_update_date := sysdate;
342 **/
343 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
344 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
345 l_last_update_login := 0;
346 --
347 begin
348 --
349 If l_rule_set_id <> 0 then
350 --
351 -- If there is a row for the rule sets
352 -- update the row in the base table
353 --
357 short_name = p_short_name,
354 update pqh_rule_sets
355 set
356 rule_set_name = p_rule_set_name,
358 referenced_rule_set_id = l_referenced_rule_set_id,
359 rule_level_cd = p_rule_level_cd,
360 rule_category = p_rule_category,
361 rule_applicability = p_rule_applicability,
362 last_updated_by = l_last_updated_by,
363 last_update_date = l_last_update_date,
364 last_update_login = l_last_update_login,
365 seeded_rule_flag = 'Y'
366 where rule_set_id = l_rule_set_id
367 and nvl(last_updated_by, -1) in (l_last_updated_by,-1,0,1);
368 --
369 -- update the tl table
370 --
371 if sql%found then
372 UPDATE pqh_rule_sets_tl
373 SET rule_set_name = p_rule_set_name,
374 description = p_description,
375 last_updated_by = l_last_updated_by,
376 last_update_date = l_last_update_date,
377 last_update_login = l_last_update_login,
378 source_lang = userenv('LANG')
379 WHERE rule_set_id = l_rule_set_id
380 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
381
382 If (sql%notfound) then
383 -- no row in TL table so insert row
384
385 --
386 insert into pqh_rule_sets_tl(
387 rule_set_id,
388 rule_set_name,
389 description,
390 language,
391 source_lang,
392 created_by,
393 creation_date,
397 )
394 last_updated_by,
395 last_update_date ,
396 last_update_login
398 Select
399 l_rule_set_id,
400 p_rule_set_name,
401 p_description,
402 l.language_code,
403 l_language ,
404 l_created_by,
405 l_creation_date,
406 l_last_updated_by,
407 l_last_update_date,
408 l_last_update_login
409 from fnd_languages l
410 where l.installed_flag in ('I','B')
411 and not exists (select null
412 from pqh_rule_sets_tl rtl
413 where rtl.rule_set_id = l_rule_set_id
414 and rtl.language = l.language_code );
415 --
416 --
417 End if;
418
419 end if; -- sql%found
420 --
421 Else
422 --
423 -- Select the next sequence number
424 --
425 Open C_Sel1;
426 Fetch C_Sel1 Into l_rule_set_id;
427 Close C_Sel1;
428 --
429 -- Insert row into the base table
430 --
431
432 insert into pqh_rule_sets(
433 rule_set_id,
434 rule_set_name,
435 short_name,
436 referenced_rule_set_id,
437 rule_level_cd,
438 rule_category,
439 rule_applicability,
440 object_version_number,
441 created_by,
442 creation_date,
443 last_updated_by,
444 last_update_date ,
445 last_update_login,
446 seeded_rule_flag
447 )
448 Values(
449 l_rule_set_id,
450 p_rule_set_name,
451 p_short_name,
452 l_referenced_rule_set_id,
453 p_rule_level_cd,
454 p_rule_category,
455 p_rule_applicability,
456 l_object_version_number,
457 l_created_by,
458 l_creation_date,
459 l_last_updated_by,
460 l_last_update_date,
461 l_last_update_login,
462 'Y'
463 );
464
465 insert into pqh_rule_sets_tl(
466 rule_set_id,
467 rule_set_name,
468 description,
469 language,
470 source_lang,
471 created_by,
472 creation_date,
473 last_updated_by,
474 last_update_date ,
475 last_update_login
476 )
477 Select
478 l_rule_set_id,
479 p_rule_set_name,
480 p_description,
481 l.language_code,
482 l_language ,
483 l_created_by,
484 l_creation_date,
485 l_last_updated_by,
486 l_last_update_date,
487 l_last_update_login
488 from fnd_languages l
489 where l.installed_flag in ('I','B')
490 and not exists (select null
491 from pqh_rule_sets_tl rtl
492 where rtl.rule_set_id = l_rule_set_id
493 and rtl.language = l.language_code );
494 --
495 --
496 --
497 End if;
498 --
499 End;
500 --
501 End load_row;
502 --
503 Procedure load_seed_row
504 (
505 p_upload_mode in varchar2
506 ,p_short_name in varchar2
507 ,p_rule_set_name in varchar2
508 ,p_description in varchar2
509 ,p_referenced_rule_set_name in varchar2
510 ,p_rule_level_cd in varchar2
511 ,p_rule_category in varchar2
512 ,p_rule_applicability in varchar2
513 ,p_owner in varchar2
514 ,p_last_update_date in varchar2
515 ) is
516 --
517 l_data_migrator_mode varchar2(1);
518 --
519 Begin
520 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
521 hr_general.g_data_migrator_mode := 'Y';
522
523 if (p_upload_mode = 'NLS') then
524 pqh_rtl_upd.translate_row (
525 p_rule_set_name => p_rule_set_name,
526 p_description => p_description,
527 p_short_name => p_short_name ,
528 p_owner => p_owner);
529 else
530 pqh_rst_shd.load_row(
531 p_rule_set_name => p_rule_set_name
532 ,p_description => p_description
533 ,p_short_name => p_short_name
534 ,p_referenced_rule_set_name => p_referenced_rule_set_name
535 ,p_rule_level_cd => p_rule_level_cd
536 ,p_rule_category => p_rule_category
537 ,p_rule_applicability => p_rule_applicability
538 ,p_owner => p_owner
539 ,p_last_update_date => p_last_update_date);
540 end if;
541 hr_general.g_data_migrator_mode := l_data_migrator_mode;
542 End;
543 --
544 -- ----------------------------------------------------------------------------
545 -- |-----------------------------< add_language >------------------------------|
546 -- ----------------------------------------------------------------------------
547 -- Procedure added as a fix for bug 5484366
548
549 Procedure ADD_LANGUAGE
550 is
551 begin
552 delete from PQH_RULE_SETS_TL T
553 where not exists
554 (select NULL
555 from PQH_RULE_SETS B
556 where B.RULE_SET_ID = T.RULE_SET_ID
557 );
558
559 update PQH_RULE_SETS_TL T set (
560 RULE_SET_NAME
561 ) = (select
562 B.RULE_SET_NAME
563 from PQH_RULE_SETS_TL B
564 where B.RULE_SET_ID = T.RULE_SET_ID
565 and B.LANGUAGE = T.SOURCE_LANG)
566 where (
567 T.RULE_SET_ID,
568 T.LANGUAGE
569 ) in (select
570 SUBT.RULE_SET_ID,
571 SUBT.LANGUAGE
572 from PQH_RULE_SETS_TL SUBB, PQH_RULE_SETS_TL SUBT
573 where SUBB.RULE_SET_ID = SUBT.RULE_SET_ID
574 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
575 and (SUBB.RULE_SET_NAME <> SUBT.RULE_SET_NAME
576 ));
577
578 insert into PQH_RULE_SETS_TL (
579 RULE_SET_ID,
580 RULE_SET_NAME,
581 LAST_UPDATE_DATE,
582 CREATION_DATE,
583 CREATED_BY,
584 LAST_UPDATE_LOGIN,
585 LAST_UPDATED_BY,
586 LANGUAGE,
587 SOURCE_LANG
588 ) select
589 B.RULE_SET_ID,
590 B.RULE_SET_NAME,
591 B.LAST_UPDATE_DATE,
592 B.CREATION_DATE,
593 B.CREATED_BY,
594 B.LAST_UPDATE_LOGIN,
595 B.LAST_UPDATED_BY,
596 L.LANGUAGE_CODE,
597 B.SOURCE_LANG
598 from PQH_RULE_SETS_TL B, FND_LANGUAGES L
599 where L.INSTALLED_FLAG in ('I', 'B')
600 and B.LANGUAGE = userenv('LANG')
601 and not exists
602 (select NULL
603 from PQH_RULE_SETS_TL T
604 where T.RULE_SET_ID = B.RULE_SET_ID
605 and T.LANGUAGE = L.LANGUAGE_CODE);
606 end ADD_LANGUAGE;
607 -- --
608 -- --
609 end pqh_rst_shd;