[Home] [Help]
PACKAGE BODY: APPS.PQH_ATT_SHD
Source
1 Package Body pqh_att_shd as
2 /* $Header: pqattrhi.pkb 120.7 2011/06/09 08:12:49 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_att_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
16 l_proc varchar2(72) := g_package||'constraint_error';
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 If (p_constraint_name = 'AVCON_14619306_ENABL_000') 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_ATTRIBUTES_FK2') 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_ATTRIBUTES_PK') Then
32 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
33 hr_utility.set_message_token('PROCEDURE', l_proc);
34 hr_utility.set_message_token('STEP','15');
35 hr_utility.raise_error;
36 Else
37 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
38 hr_utility.set_message_token('PROCEDURE', l_proc);
39 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
40 hr_utility.raise_error;
41 End If;
42 --
43 hr_utility.set_location(' Leaving:'||l_proc, 10);
44 End constraint_error;
45 --
46 -- ----------------------------------------------------------------------------
47 -- |-----------------------------< api_updating >-----------------------------|
48 -- ----------------------------------------------------------------------------
49 Function api_updating
50 (
51 p_attribute_id in number,
52 p_object_version_number in number
53 ) Return Boolean Is
54 --
55 --
56 -- Cursor selects the 'current' row from the HR Schema
57 --
58 Cursor C_Sel1 is
59 select
60 attribute_id,
61 attribute_name,
62 master_attribute_id,
63 master_table_route_id,
64 column_name,
65 column_type,
66 enable_flag,
67 width,
68 object_version_number,
69 region_itemname,
70 attribute_itemname,
71 decode_function_name
72 from pqh_attributes
73 where attribute_id = p_attribute_id;
74 --
75 l_proc varchar2(72) := g_package||'api_updating';
76 l_fct_ret boolean;
77 --
78 Begin
79 hr_utility.set_location('Entering:'||l_proc, 5);
80 --
81 If (
82 p_attribute_id is null and
83 p_object_version_number is null
84 ) Then
85 --
86 -- One of the primary key arguments is null therefore we must
87 -- set the returning function value to false
88 --
89 l_fct_ret := false;
90 Else
91 If (
92 p_attribute_id = g_old_rec.attribute_id and
93 p_object_version_number = g_old_rec.object_version_number
94 ) Then
95 hr_utility.set_location(l_proc, 10);
96 --
97 -- The g_old_rec is current therefore we must
98 -- set the returning function to true
99 --
100 l_fct_ret := true;
101 Else
102 --
103 -- Select the current row into g_old_rec
104 --
105 Open C_Sel1;
106 Fetch C_Sel1 Into g_old_rec;
107 If C_Sel1%notfound Then
108 Close C_Sel1;
109 --
110 -- The primary key is invalid therefore we must error
111 --
112 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
113 hr_utility.raise_error;
114 End If;
115 Close C_Sel1;
116 If (p_object_version_number <> g_old_rec.object_version_number) Then
117 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
118 hr_utility.raise_error;
119 End If;
120 hr_utility.set_location(l_proc, 15);
121 l_fct_ret := true;
122 End If;
123 End If;
124 hr_utility.set_location(' Leaving:'||l_proc, 20);
125 Return (l_fct_ret);
126 --
127 End api_updating;
128 --
129 -- ----------------------------------------------------------------------------
130 -- |---------------------------------< lck >----------------------------------|
131 -- ----------------------------------------------------------------------------
132 Procedure lck
133 (
134 p_attribute_id in number,
135 p_object_version_number in number
136 ) is
137 --
138 -- Cursor selects the 'current' row from the HR Schema
139 --
140 Cursor C_Sel1 is
141 select attribute_id,
142 attribute_name,
143 master_attribute_id,
144 master_table_route_id,
145 column_name,
146 column_type,
147 enable_flag,
148 width,
149 object_version_number,
150 region_itemname,
151 attribute_itemname,
152 decode_function_name
153 from pqh_attributes
154 where attribute_id = p_attribute_id
155 for update nowait;
156 --
157 l_proc varchar2(72) := g_package||'lck';
158 --
159 Begin
160 hr_utility.set_location('Entering:'||l_proc, 5);
161 --
162 -- Add any mandatory argument checking here:
163 -- Example:
164 -- hr_api.mandatory_arg_error
165 -- (p_api_name => l_proc,
166 -- p_argument => 'object_version_number',
167 -- p_argument_value => p_object_version_number);
171 If C_Sel1%notfound then
168 --
169 Open C_Sel1;
170 Fetch C_Sel1 Into g_old_rec;
172 Close C_Sel1;
173 --
174 -- The primary key is invalid therefore we must error
175 --
176 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
177 hr_utility.raise_error;
178 End If;
179 Close C_Sel1;
180 If (p_object_version_number <> g_old_rec.object_version_number) Then
181 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
182 hr_utility.raise_error;
183 End If;
184 --
185 hr_utility.set_location(' Leaving:'||l_proc, 10);
186 --
187 -- We need to trap the ORA LOCK exception
188 --
189 Exception
190 When HR_Api.Object_Locked then
191 --
192 -- The object is locked therefore we need to supply a meaningful
193 -- error message.
194 --
195 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
196 hr_utility.set_message_token('TABLE_NAME', 'pqh_attributes');
197 hr_utility.raise_error;
198 End lck;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |-----------------------------< convert_args >-----------------------------|
202 -- ----------------------------------------------------------------------------
203 Function convert_args
204 (
205 p_attribute_id in number,
206 p_attribute_name in varchar2,
207 p_master_attribute_id in number,
208 p_master_table_route_id in number,
209 p_column_name in varchar2,
210 p_column_type in varchar2,
211 p_enable_flag in varchar2,
212 p_width in number,
213 p_object_version_number in number,
214 p_region_itemname in varchar2,
215 p_attribute_itemname in varchar2,
216 p_decode_function_name in varchar2
217 )
218 Return g_rec_type is
219 --
220 l_rec g_rec_type;
221 l_proc varchar2(72) := g_package||'convert_args';
222 --
223 Begin
224 --
225 hr_utility.set_location('Entering:'||l_proc, 5);
226 --
227 -- Convert arguments into local l_rec structure.
228 --
229 l_rec.attribute_id := p_attribute_id;
230 l_rec.attribute_name := p_attribute_name;
231 l_rec.master_attribute_id := p_master_attribute_id;
232 l_rec.master_table_route_id := p_master_table_route_id;
233 l_rec.column_name := p_column_name;
234 l_rec.column_type := p_column_type;
235 l_rec.enable_flag := p_enable_flag;
236 l_rec.width := p_width;
237 l_rec.object_version_number := p_object_version_number;
238 l_rec.region_itemname := p_region_itemname;
239 l_rec.attribute_itemname := p_attribute_itemname;
240 l_rec.decode_function_name := p_decode_function_name;
241 --
242 -- Return the plsql record structure.
243 --
244 hr_utility.set_location(' Leaving:'||l_proc, 10);
245 Return(l_rec);
246 --
247 End convert_args;
248 --
249 -- ----------------------------------------------------------------------------
250 -- |-----------------------------< load_seed_row >---------------------------------|
251 -- ----------------------------------------------------------------------------
252 --
253 Procedure load_seed_row
254 ( p_upload_mode in varchar2
255 ,p_attribute_name in varchar2
256 ,p_master_attr_key_col_name in varchar2
257 ,p_master_attribute_col_name in varchar2
258 ,p_master_att_table_alias_name in varchar2
259 ,p_master_table_alias_name in varchar2
260 ,p_master_legislation_code in varchar2
261 ,p_key_column_name in varchar2
262 ,p_column_name in varchar2
263 ,p_column_type in varchar2
264 ,p_enable_flag in varchar2
265 ,p_width in number
266 ,p_refresh_col_name in varchar2
267 ,p_legislation_code in varchar2
268 ,p_region_itemname in varchar2
269 ,p_attribute_itemname in varchar2
270 ,p_decode_function_name in varchar2
271 ,p_last_update_date in varchar2
272 ,p_owner in varchar2
273 ) is
274 --
275 l_data_migrator_mode varchar2(1);
276 --
277 Begin
278 --
279 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
280 hr_general.g_data_migrator_mode := 'Y';
281
282 if (p_upload_mode = 'NLS') then
283 pqh_atl_upd.translate_row
284 ( p_attribute_name => p_attribute_name
285 ,p_att_col_name => p_key_column_name
286 ,p_att_master_table_alias_name => p_master_table_alias_name
287 ,p_legislation_code => p_legislation_code
288 ,p_owner => p_owner );
289 else
290 pqh_att_shd.load_row
291 ( p_attribute_name => p_attribute_name
292 ,p_master_attr_key_col_name => p_master_attr_key_col_name
293 ,p_master_attribute_col_name => p_master_attribute_col_name
294 ,p_master_att_table_alias_name => p_master_att_table_alias_name
295 ,p_master_table_alias_name => p_master_table_alias_name
296 ,p_master_legislation_code => p_master_legislation_code
297 ,p_column_name => p_column_name
301 ,p_width => p_width
298 ,p_key_column_name => p_key_column_name
299 ,p_column_type => p_column_type
300 ,p_enable_flag => p_enable_flag
302 ,p_refresh_col_name => p_refresh_col_name
303 ,p_legislation_code => p_legislation_code
304 ,p_region_itemname => p_region_itemname
305 ,p_attribute_itemname => p_attribute_itemname
306 ,p_decode_function_name => p_decode_function_name
307 ,p_last_update_date => p_last_update_date
308 ,p_owner => p_owner );
309 end if;
310
311 hr_general.g_data_migrator_mode := l_data_migrator_mode;
312 End;
313 -- ----------------------------------------------------------------------------
314 -- |-----------------------------< load_row >---------------------------------|
315 -- ----------------------------------------------------------------------------
316 --
317 Procedure load_row
318 ( p_attribute_name in varchar2
319 ,p_master_attr_key_col_name in varchar2
320 ,p_master_attribute_col_name in varchar2
321 ,p_master_att_table_alias_name in varchar2
322 ,p_master_table_alias_name in varchar2
323 ,p_master_legislation_code in varchar2
324 ,p_key_column_name in varchar2
325 ,p_column_name in varchar2
326 ,p_column_type in varchar2
327 ,p_enable_flag in varchar2
328 ,p_width in number
329 ,p_refresh_col_name in varchar2
330 ,p_legislation_code in varchar2
331 ,p_region_itemname in varchar2
332 ,p_attribute_itemname in varchar2
333 ,p_decode_function_name in varchar2
334 ,p_last_update_date in varchar2
335 ,p_owner in varchar2
336 ) is
337 --
338 --
339 l_effective_date date := sysdate ;
340 l_object_version_number number := 1;
341 l_language varchar2(30) ;
342 l_delete_attr_ranges_flag varchar2(30) := 'N';
343 --
344 l_attribute_id pqh_attributes.attribute_id%TYPE := 0 ;
345 l_master_attribute_id pqh_attributes.master_attribute_id%TYPE;
346 l_master_table_route_id pqh_attributes.master_table_route_id%TYPE;
347 l_master_att_table_route_id pqh_attributes.master_table_route_id%TYPE;
348 --
349 l_created_by pqh_attributes.created_by%TYPE;
350 l_last_updated_by pqh_attributes.last_updated_by%TYPE;
351 l_creation_date pqh_attributes.creation_date%TYPE;
352 l_last_update_date pqh_attributes.last_update_date%TYPE;
353 l_last_update_login pqh_attributes.last_update_login%TYPE;
354 --
355 cursor c1 is select userenv('LANG') from dual ;
356 --
357 --
358 -- developer key is column_name and master_table_alias
359 --
360 cursor csr_attribute_id(p_key_column_name IN VARCHAR2, p_table_id IN NUMBER,
361 p_legislation_code varchar2) is
362 select attribute_id,last_update_date
363 from pqh_attributes
364 where key_column_name = p_key_column_name
365 and nvl(legislation_code,'$$$') = nvl(p_legislation_code,'$$$')
366 and nvl(master_table_route_id,-999) = nvl(p_table_id, -999);
367 --
368 cursor csr_table_id (p_table_alias IN VARCHAR2) is
369 select table_route_id
370 from pqh_table_route
371 where table_alias = p_table_alias;
372 --
373 l_last_upd_in_db pqh_attributes.last_update_date%TYPE;
374 l_dummy pqh_attributes.last_update_date%TYPE;
375 --
376 --
377 --
378 Begin
379 --
380 -- key to ids
381 --
382 open c1;
383 fetch c1 into l_language ;
384 close c1;
385 --
386 open csr_table_id(p_table_alias => p_master_table_alias_name );
387 fetch csr_table_id into l_master_table_route_id;
388 close csr_table_id;
389 --
390 open csr_table_id(p_table_alias => p_master_att_table_alias_name );
391 fetch csr_table_id into l_master_att_table_route_id;
392 close csr_table_id;
393 --
394 open csr_attribute_id(p_key_column_name => p_key_column_name,
395 p_table_id => l_master_table_route_id,
396 p_legislation_code => p_legislation_code);
397 fetch csr_attribute_id into l_attribute_id,l_last_upd_in_db;
398 close csr_attribute_id;
399 --
400 open csr_attribute_id(p_key_column_name => p_master_attr_key_col_name,
401 p_table_id => l_master_att_table_route_id,
402 p_legislation_code => p_master_legislation_code);
403 fetch csr_attribute_id into l_master_attribute_id,l_dummy;
404 close csr_attribute_id;
405 --
406 If p_master_table_alias_name is not null and l_master_table_route_id is null then
407 --
408 hr_utility.set_message(8302,'PQH_INVALID_MASTER_TABLE_ROUTE');
409 hr_utility.raise_error;
410 --
411 End if;
412 --
413 -- populate WHO columns
414 --
415 /**
416 l_created_by := 1;
417 l_last_updated_by := -1;
418
419 l_creation_date := sysdate;
420 l_last_update_date := sysdate;
421 **/
422 l_last_update_login := 0;
423
424 l_created_by := fnd_load_util.owner_id(p_owner);
425 l_last_updated_by := fnd_load_util.owner_id(p_owner);
426 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
427 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
428 --
429 --
430 Begin
431 --
435
432 if l_attribute_id <> 0 then
433 -- row exits so update
434 If l_last_update_date > l_last_upd_in_db then
436 UPDATE pqh_attributes
437 SET attribute_name = p_attribute_name,
438 master_attribute_id = l_master_attribute_id,
439 master_table_route_id = l_master_table_route_id,
440 key_column_name = p_key_column_name,
441 column_name = nvl(p_column_name,p_key_column_name),
442 column_type = p_column_type,
443 enable_flag = p_enable_flag,
444 width = p_width,
445 refresh_col_name = p_refresh_col_name,
446 legislation_code = p_legislation_code,
447 region_itemname = p_region_itemname,
448 attribute_itemname = p_attribute_itemname,
449 decode_function_name = p_decode_function_name,
450 last_updated_by = l_last_updated_by,
451 last_update_date = l_last_update_date,
452 last_update_login = l_last_update_login
453 WHERE attribute_id = l_attribute_id ;
454 --AND NVL(last_updated_by,-1) in (-1,1);
455 -- update attributes
456
457 -- update the tl table
458 if (sql%found) then
459
460 UPDATE pqh_attributes_tl
461 SET attribute_name = p_attribute_name,
462 last_updated_by = l_last_updated_by,
463 last_update_date = l_last_update_date,
464 last_update_login = l_last_update_login,
465 source_lang = userenv('LANG')
466 WHERE attribute_id = l_attribute_id
467 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
468
469
470 if (sql%notfound) then
471 -- no row in TL table so insert row
472
473 insert into pqh_attributes_tl
474 ( attribute_id,
475 attribute_name,
476 language,
477 source_lang,
478 creation_date,
479 created_by,
480 last_update_date,
481 last_update_login,
482 last_updated_by
483 )
484 Select
485 l_attribute_id,
486 p_attribute_name,
487 L.LANGUAGE_CODE,
488 userenv('LANG'),
489 l_creation_date,
490 l_created_by,
491 l_last_update_date,
492 l_last_update_login,
493 l_last_updated_by
494 from FND_LANGUAGES L
495 where L.INSTALLED_FLAG in ('I', 'B')
496 and not exists
497 (select NULL
498 from pqh_attributes_tl T
499 where T.attribute_id = l_attribute_id
500 and T.LANGUAGE = L.LANGUAGE_CODE);
501
502
503 end if;
504
505 end if; -- sql%found for main table
506
507 End if;
508 else
509
510 -- insert into pqh_attributes and pqh_attributes_tl
511
512 select pqh_attributes_s.nextval into l_attribute_id from dual;
513
514
515 --
516 -- Added the following code as a part of Zero Downtime Patching Project.
517 -- Code Starts Here.
518 --
519 per_ric_pkg.Chk_integrity (
520 p_entity_name=>'PQH_ATTRIBUTES',
521 p_ref_entity=>'PQH_TABLE_ROUTE',
522 p_ref_column_name=>'TABLE_ROUTE_ID', -- changed for bug 12639523
523 p_ref_col_value_number=>l_master_table_route_id,
524 p_ref_col_value_varchar=>NULL,
525 p_ref_col_value_date=>NULL,
526 p_ref_type=> 'INS');
527 --
528 -- Code Ends Here
529 --
530
531 INSERT INTO pqh_attributes
532 (attribute_id ,
533 attribute_name,
534 master_attribute_id,
535 master_table_route_id ,
536 key_column_name,
537 column_name,
538 column_type,
539 enable_flag,
540 width,
541 refresh_col_name,
542 legislation_code,
543 region_itemname,
544 attribute_itemname,
545 decode_function_name,
546 created_by,
547 creation_date,
548 last_updated_by,
549 last_update_date ,
550 last_update_login,
551 object_version_number)
552 VALUES
553 (l_attribute_id,
554 p_attribute_name,
555 l_master_attribute_id,
556 l_master_table_route_id,
557 p_key_column_name,
558 nvl(p_column_name,p_key_column_name),
559 p_column_type,
560 p_enable_flag,
561 p_width,
562 p_refresh_col_name,
563 p_legislation_code,
564 p_region_itemname,
565 p_attribute_itemname,
566 p_decode_function_name,
567 l_created_by,
568 l_creation_date,
569 l_last_updated_by,
570 l_last_update_date,
571 l_last_update_login,
572 1 );
573
574 -- insert into tl table
575
576 insert into pqh_attributes_tl
577 ( attribute_id,
578 attribute_name,
579 language,
583 last_update_date,
580 source_lang,
581 creation_date,
582 created_by,
584 last_update_login,
585 last_updated_by
586 )
587 Select
588 l_attribute_id,
589 p_attribute_name,
590 L.LANGUAGE_CODE,
591 userenv('LANG'),
592 l_creation_date,
593 l_created_by,
594 l_last_update_date,
595 l_last_update_login,
596 l_last_updated_by
597 from FND_LANGUAGES L
598 where L.INSTALLED_FLAG in ('I', 'B')
599 and not exists
600 (select NULL
601 from pqh_attributes_tl T
602 where T.attribute_id = l_attribute_id
603 and T.LANGUAGE = L.LANGUAGE_CODE);
604
605
606 end if;
607 end;
608 end load_row;
609 --
610 -- ----------------------------------------------------------------------------
611 -- |-----------------------------< add_language >------------------------------|
612 -- ----------------------------------------------------------------------------
613 Procedure ADD_LANGUAGE
614 is
615 begin
616 delete from PQH_ATTRIBUTES_TL T
617 where not exists
618 (select NULL
619 from PQH_ATTRIBUTES B
620 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
621 );
622
623 update PQH_ATTRIBUTES_TL T set (
624 ATTRIBUTE_NAME
625 ) = (select
626 B.ATTRIBUTE_NAME
627 from PQH_ATTRIBUTES_TL B
628 where B.ATTRIBUTE_ID = T.ATTRIBUTE_ID
629 and B.LANGUAGE = T.SOURCE_LANG)
630 where (
631 T.ATTRIBUTE_ID,
632 T.LANGUAGE
633 ) in (select
634 SUBT.ATTRIBUTE_ID,
635 SUBT.LANGUAGE
636 from PQH_ATTRIBUTES_TL SUBB, PQH_ATTRIBUTES_TL SUBT
637 where SUBB.ATTRIBUTE_ID = SUBT.ATTRIBUTE_ID
638 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
639 and (SUBB.ATTRIBUTE_NAME <> SUBT.ATTRIBUTE_NAME
640 ));
641
642 insert into PQH_ATTRIBUTES_TL (
643 ATTRIBUTE_ID,
644 ATTRIBUTE_NAME,
645 LAST_UPDATE_DATE,
646 CREATION_DATE,
647 CREATED_BY,
648 LAST_UPDATE_LOGIN,
649 LAST_UPDATED_BY,
650 LANGUAGE,
651 SOURCE_LANG
652 ) select
653 B.ATTRIBUTE_ID,
654 B.ATTRIBUTE_NAME,
655 B.LAST_UPDATE_DATE,
656 B.CREATION_DATE,
657 B.CREATED_BY,
658 B.LAST_UPDATE_LOGIN,
659 B.LAST_UPDATED_BY,
660 L.LANGUAGE_CODE,
661 B.SOURCE_LANG
662 from PQH_ATTRIBUTES_TL B, FND_LANGUAGES L
663 where L.INSTALLED_FLAG in ('I', 'B')
664 and B.LANGUAGE = userenv('LANG')
665 and not exists
666 (select NULL
667 from PQH_ATTRIBUTES_TL T
668 where T.ATTRIBUTE_ID = B.ATTRIBUTE_ID
669 and T.LANGUAGE = L.LANGUAGE_CODE);
670 end ADD_LANGUAGE;
671 --
672 --
673 End pqh_att_shd;
674 --