[Home] [Help]
PACKAGE BODY: APPS.PQH_TEM_SHD
Source
1 Package Body pqh_tem_shd as
2 /* $Header: pqtemrhi.pkb 120.5 2011/04/28 09:32:18 sidsaxen ship $ */
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,
407 create_flag = p_create_flag,
408 transaction_category_id = l_transaction_category_id,
409 under_review_flag = p_under_review_flag,
410 freeze_status_cd = p_freeze_status_cd,
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 --
478 -- Added the following code as a part of Zero Downtime Patching Project.
479 -- Code Starts Here.
480 --
481 per_ric_pkg.Chk_integrity (
482 p_entity_name=> 'PQH_TEMPLATES',
483 p_ref_entity=>'PQH_TRANSACTION_CATEGORIES',
484 p_ref_column_name=>'TRANSACTION_CATEGORY_ID',
485 p_ref_col_value_number=>l_transaction_category_id,
486 p_ref_col_value_varchar=>NULL,
487 p_ref_col_value_date=>NULL,
488 p_ref_type=> 'INS');
489 --
490 -- Code Ends Here
491 --
492 --
493 -- Insert row into the base table
494 --
495
496 insert into pqh_templates(
497 template_name,
498 template_id,
499 attribute_only_flag,
500 enable_flag,
501 create_flag,
502 transaction_category_id,
503 under_review_flag,
504 object_version_number,
505 last_update_date,
506 last_updated_by,
507 last_update_login,
508 created_by,
509 creation_date,
510 freeze_status_cd,
511 template_type_cd,
512 legislation_code,
513 short_name
514 )
515 Values(
516 p_template_name,
517 l_template_id,
518 p_attribute_only_flag,
519 p_enable_flag,
520 p_create_flag,
521 l_transaction_category_id,
522 p_under_review_flag,
523 1,
524 l_last_update_date,
525 l_last_updated_by,
526 l_last_update_login,
527 l_created_by,
528 l_creation_date,
529 p_freeze_status_cd,
530 p_template_type_cd,
531 p_legislation_code,
532 p_short_name
533 );
534 --
535
536 insert into pqh_templates_tl(
537 template_id,
538 template_name,
539 language,
540 source_lang,
541 created_by,
542 creation_date,
543 last_updated_by,
544 last_update_date ,
545 last_update_login
546 )
547 Select
548 l_template_id,
549 p_template_name,
550 l.language_code,
551 l_language ,
552 l_created_by,
553 l_creation_date,
554 l_last_updated_by,
555 l_last_update_date,
556 l_last_update_login
557 from fnd_languages l
558 where l.installed_flag in ('I','B')
559 and not exists (select null
560 from pqh_templates_tl ttl
561 where ttl.template_id = l_template_id
562 and ttl.language = l.language_code );
563 --
564 --
565 End if;
566 --
567 End;
568 --
569 End load_row;
570 --
571 -- ----------------------------------------------------------------------------
572 -- |-----------------------------< add_language >------------------------------|
573 -- ----------------------------------------------------------------------------
574 -- Procedure added as a fix for bug 5484366
575 --
576 Procedure ADD_LANGUAGE
577 is
578 begin
579 delete from PQH_TEMPLATES_TL T
580 where not exists
581 (select NULL
582 from PQH_TEMPLATES B
583 where B.TEMPLATE_ID = T.TEMPLATE_ID
584 );
585
586 update PQH_TEMPLATES_TL T set (
587 TEMPLATE_NAME
588 ) = (select
589 B.TEMPLATE_NAME
590 from PQH_TEMPLATES_TL B
591 where B.TEMPLATE_ID = T.TEMPLATE_ID
592 and B.LANGUAGE = T.SOURCE_LANG)
593 where (
594 T.TEMPLATE_ID,
595 T.LANGUAGE
596 ) in (select
597 SUBT.TEMPLATE_ID,
598 SUBT.LANGUAGE
599 from PQH_TEMPLATES_TL SUBB, PQH_TEMPLATES_TL SUBT
600 where SUBB.TEMPLATE_ID = SUBT.TEMPLATE_ID
601 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
602 and (SUBB.TEMPLATE_NAME <> SUBT.TEMPLATE_NAME
603 ));
604
605 insert into PQH_TEMPLATES_TL (
606 TEMPLATE_ID,
607 TEMPLATE_NAME,
608 LAST_UPDATE_DATE,
609 CREATION_DATE,
610 CREATED_BY,
611 LAST_UPDATE_LOGIN,
612 LAST_UPDATED_BY,
613 LANGUAGE,
614 SOURCE_LANG
615 ) select
616 B.TEMPLATE_ID,
617 B.TEMPLATE_NAME,
618 B.LAST_UPDATE_DATE,
619 B.CREATION_DATE,
620 B.CREATED_BY,
621 B.LAST_UPDATE_LOGIN,
622 B.LAST_UPDATED_BY,
623 L.LANGUAGE_CODE,
624 B.SOURCE_LANG
625 from PQH_TEMPLATES_TL B, FND_LANGUAGES L
626 where L.INSTALLED_FLAG in ('I', 'B')
627 and B.LANGUAGE = userenv('LANG')
628 and not exists
629 (select NULL
630 from PQH_TEMPLATES_TL T
631 where T.TEMPLATE_ID = B.TEMPLATE_ID
632 and T.LANGUAGE = L.LANGUAGE_CODE);
633 end ADD_LANGUAGE;
634 -- --
635 -- --
636 end pqh_tem_shd;