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