[Home] [Help]
PACKAGE BODY: APPS.PQH_TCT_SHD
Source
1 Package Body pqh_tct_shd as
2 /* $Header: pqtctrhi.pkb 120.7 2011/04/28 09:34:04 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tct_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 = 'AVCON_15469327_FUTUR_000') 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 = 'AVCON_15469327_MEMBE_000') 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 = 'AVCON_15469327_POST__000') 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 = 'AVCON_15469327_ROUTE_000') 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_TRANSACTION_CATEGORIES_FK1') Then
43 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
44 hr_utility.set_message_token('PROCEDURE', l_proc);
45 hr_utility.set_message_token('STEP','25');
46 hr_utility.raise_error;
47 ElsIf (p_constraint_name = 'PQH_TRANSACTION_CATEGORIES_PK') Then
48 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
49 hr_utility.set_message_token('PROCEDURE', l_proc);
50 hr_utility.set_message_token('STEP','30');
51 hr_utility.raise_error;
52 ElsIf (p_constraint_name = 'PQH_TRANSACTION_CATEGORIES_FK2') Then
53 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
54 hr_utility.set_message_token('PROCEDURE', l_proc);
55 hr_utility.set_message_token('STEP','35');
56 hr_utility.raise_error;
57 ElsIf (p_constraint_name = 'PQH_TRANSACTION_CATEGORIES_UK') Then
58 hr_utility.set_message(8302, 'PQH_SS_DUPLICATE_SHORT_NAME');
59 hr_utility.raise_error;
60 Else
61 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
62 hr_utility.set_message_token('PROCEDURE', l_proc);
63 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
64 hr_utility.raise_error;
65 End If;
66 --
67 hr_utility.set_location(' Leaving:'||l_proc, 10);
68 End constraint_error;
69 --
70 -- ----------------------------------------------------------------------------
71 -- |-----------------------------< api_updating >-----------------------------|
72 -- ----------------------------------------------------------------------------
73 Function api_updating
74 (
75 p_transaction_category_id in number,
76 p_object_version_number in number
77 ) Return Boolean Is
78 --
79 --
80 -- Cursor selects the 'current' row from the HR Schema
81 --
82 Cursor C_Sel1 is
83 select
84 transaction_category_id,
85 custom_wf_process_name,
86 custom_workflow_name,
87 form_name,
88 freeze_status_cd,
89 future_action_cd,
90 member_cd,
91 name,
92 short_name,
93 post_style_cd,
94 post_txn_function,
95 route_validated_txn_flag,
96 prevent_approver_skip,
97 workflow_enable_flag,
98 enable_flag,
99 timeout_days,
100 object_version_number,
101 consolidated_table_route_id ,
102 business_group_id,
103 setup_type_cd,
104 master_table_route_id
105 from pqh_transaction_categories
106 where transaction_category_id = p_transaction_category_id;
107 --
108 l_proc varchar2(72) := g_package||'api_updating';
109 l_fct_ret boolean;
110 --
111 Begin
112 hr_utility.set_location('Entering:'||l_proc, 5);
113 --
114 If (
115 p_transaction_category_id is null and
116 p_object_version_number is null
117 ) Then
118 --
119 -- One of the primary key arguments is null therefore we must
120 -- set the returning function value to false
121 --
122 l_fct_ret := false;
123 Else
124 If (
125 p_transaction_category_id = g_old_rec.transaction_category_id and
126 p_object_version_number = g_old_rec.object_version_number
127 ) Then
128 hr_utility.set_location(l_proc, 10);
129 --
130 -- The g_old_rec is current therefore we must
131 -- set the returning function to true
132 --
133 l_fct_ret := true;
134 Else
135 --
136 -- Select the current row into g_old_rec
137 --
138 Open C_Sel1;
139 Fetch C_Sel1 Into g_old_rec;
140 If C_Sel1%notfound Then
141 Close C_Sel1;
142 --
143 -- The primary key is invalid therefore we must error
144 --
145 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
146 hr_utility.raise_error;
147 End If;
148 Close C_Sel1;
149 If (p_object_version_number <> g_old_rec.object_version_number) Then
150 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
151 hr_utility.raise_error;
152 End If;
153 hr_utility.set_location(l_proc, 15);
154 l_fct_ret := true;
155 End If;
156 End If;
157 hr_utility.set_location(' Leaving:'||l_proc, 20);
158 Return (l_fct_ret);
159 --
160 End api_updating;
161 --
162 -- ----------------------------------------------------------------------------
163 -- |---------------------------------< lck >----------------------------------|
164 -- ----------------------------------------------------------------------------
165 Procedure lck
166 (
167 p_transaction_category_id in number,
168 p_object_version_number in number
169 ) is
170 --
171 -- Cursor selects the 'current' row from the HR Schema
172 --
173 Cursor C_Sel1 is
174 select transaction_category_id,
175 custom_wf_process_name,
176 custom_workflow_name,
177 form_name,
178 freeze_status_cd,
179 future_action_cd,
180 member_cd,
181 name,
182 short_name,
183 post_style_cd,
184 post_txn_function,
185 route_validated_txn_flag,
186 prevent_approver_skip,
187 workflow_enable_flag,
188 enable_flag,
189 timeout_days,
190 object_version_number,
191 consolidated_table_route_id,
192 business_group_id,
193 setup_type_cd,
194 master_table_route_id
195 from pqh_transaction_categories
196 where transaction_category_id = p_transaction_category_id
197 for update nowait;
198 --
199 l_proc varchar2(72) := g_package||'lck';
200 --
201 Begin
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 --
204 -- Add any mandatory argument checking here:
205 -- Example:
206 -- hr_api.mandatory_arg_error
207 -- (p_api_name => l_proc,
208 -- p_argument => 'object_version_number',
209 -- p_argument_value => p_object_version_number);
210 --
211 Open C_Sel1;
212 Fetch C_Sel1 Into g_old_rec;
213 If C_Sel1%notfound then
214 Close C_Sel1;
215 --
216 -- The primary key is invalid therefore we must error
217 --
218 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
219 hr_utility.raise_error;
220 End If;
221 Close C_Sel1;
222 If (p_object_version_number <> g_old_rec.object_version_number) Then
223 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
224 hr_utility.raise_error;
225 End If;
226 --
227 hr_utility.set_location(' Leaving:'||l_proc, 10);
228 --
229 -- We need to trap the ORA LOCK exception
230 --
231 Exception
232 When HR_Api.Object_Locked then
233 --
234 -- The object is locked therefore we need to supply a meaningful
235 -- error message.
236 --
237 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
238 hr_utility.set_message_token('TABLE_NAME', 'pqh_transaction_categories');
239 hr_utility.raise_error;
240 End lck;
241 --
242 -- ----------------------------------------------------------------------------
243 -- |-----------------------------< convert_args >-----------------------------|
244 -- ----------------------------------------------------------------------------
245 Function convert_args
246 (
247 p_transaction_category_id in number,
248 p_custom_wf_process_name in varchar2,
249 p_custom_workflow_name in varchar2,
250 p_form_name in varchar2,
251 p_freeze_status_cd in varchar2,
252 p_future_action_cd in varchar2,
253 p_member_cd in varchar2,
254 p_name in varchar2,
255 p_short_name in varchar2,
256 p_post_style_cd in varchar2,
257 p_post_txn_function in varchar2,
258 p_route_validated_txn_flag in varchar2,
259 p_prevent_approver_skip in varchar2,
260 p_workflow_enable_flag in varchar2,
261 p_enable_flag in varchar2,
262 p_timeout_days in number,
263 p_object_version_number in number,
264 p_consolidated_table_route_id in number ,
265 p_business_group_id in number,
266 p_setup_type_cd in varchar2,
267 p_master_table_route_id in number
268 )
269 Return g_rec_type is
270 --
271 l_rec g_rec_type;
272 l_proc varchar2(72) := g_package||'convert_args';
273 --
274 Begin
275 --
276 hr_utility.set_location('Entering:'||l_proc, 5);
277 --
278 -- Convert arguments into local l_rec structure.
279 --
280 l_rec.transaction_category_id := p_transaction_category_id;
281 l_rec.custom_wf_process_name := p_custom_wf_process_name;
282 l_rec.custom_workflow_name := p_custom_workflow_name;
283 l_rec.form_name := p_form_name;
284 l_rec.freeze_status_cd := p_freeze_status_cd;
285 l_rec.future_action_cd := p_future_action_cd;
286 l_rec.member_cd := p_member_cd;
287 l_rec.name := p_name;
288 l_rec.short_name := p_short_name;
289 l_rec.post_style_cd := p_post_style_cd;
290 l_rec.post_txn_function := p_post_txn_function;
291 l_rec.route_validated_txn_flag := p_route_validated_txn_flag;
292 l_rec.prevent_approver_skip := p_prevent_approver_skip;
293 l_rec.workflow_enable_flag := p_workflow_enable_flag;
294 l_rec.enable_flag := p_enable_flag;
295 l_rec.timeout_days := p_timeout_days;
296 l_rec.object_version_number := p_object_version_number;
297 l_rec.consolidated_table_route_id := p_consolidated_table_route_id;
298 l_rec.business_group_id := p_business_group_id;
299 l_rec.setup_type_cd := p_setup_type_cd;
300 l_rec.master_table_route_id := p_master_table_route_id;
301 --
302 -- Return the plsql record structure.
303 --
304 hr_utility.set_location(' Leaving:'||l_proc, 10);
305 Return(l_rec);
306 --
307 End convert_args;
308 --
309 Procedure load_seed_row
310 (p_upload_mode in varchar2
311 ,p_name in varchar2
312 ,p_short_name in varchar2
313 ,p_custom_wf_process_name in varchar2
314 ,p_custom_workflow_name in varchar2
315 ,p_form_name in varchar2
316 ,p_freeze_status_cd in varchar2
317 ,p_future_action_cd in varchar2
318 ,p_member_cd in varchar2
319 ,p_post_style_cd in varchar2
320 ,p_post_txn_function in varchar2
321 ,p_route_validated_txn_flag in varchar2
322 ,p_prevent_approver_skip in varchar2
323 ,p_workflow_enable_flag in varchar2
324 ,p_timeout_days in number
325 ,p_consolid_table_alias in varchar2
326 -- ,p_business_group_alias in varchar2
327 ,p_setup_type_cd in varchar2
328 ,p_enable_flag in varchar2
329 ,p_master_table_alias in varchar2
330 ,p_owner in varchar2
331 ,p_last_update_date in varchar2
332 ) is
333 --
334 --
335 l_data_migrator_mode varchar2(1);
336 --
337 Begin
338 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
339 hr_general.g_data_migrator_mode := 'Y';
340
341 if (p_upload_mode = 'NLS') then
342 pqh_ctl_upd.translate_row (
343 p_short_name => p_short_name,
344 p_name => p_name ,
345 p_owner => p_owner);
346 else
347
348 pqh_tct_shd.load_row
349 (
350 p_custom_wf_process_name => p_custom_wf_process_name
351 ,p_custom_workflow_name => p_custom_workflow_name
352 ,p_form_name => p_form_name
353 ,p_freeze_status_cd => p_freeze_status_cd
354 ,p_future_action_cd => p_future_action_cd
355 ,p_member_cd => p_member_cd
356 ,p_name => p_name
357 ,p_short_name => p_short_name
358 ,p_post_style_cd => p_post_style_cd
359 ,p_post_txn_function => p_post_txn_function
360 ,p_route_validated_txn_flag => p_route_validated_txn_flag
361 ,p_workflow_enable_flag => p_workflow_enable_flag
362 ,p_timeout_days => p_timeout_days
363 ,p_consolid_table_alias => p_consolid_table_alias
364 ,p_master_table_alias => p_master_table_alias
365 ,p_setup_type_cd => p_setup_type_cd
366 ,p_enable_flag => p_enable_flag
367 ,p_prevent_approver_skip => p_prevent_approver_skip
368 ,p_owner => p_owner
369 ,p_last_update_date => p_last_update_date);
370 end if;
371 hr_general.g_data_migrator_mode := l_data_migrator_mode;
372 End;
373 --
374 -- ----------- Load Row -------------------------------------------
375 --
376 Procedure load_row
377 (
378 p_name in varchar2
379 ,p_short_name in varchar2
380 ,p_custom_wf_process_name in varchar2
381 ,p_custom_workflow_name in varchar2
382 ,p_form_name in varchar2
383 ,p_freeze_status_cd in varchar2
384 ,p_future_action_cd in varchar2
385 ,p_member_cd in varchar2
386 ,p_post_style_cd in varchar2
387 ,p_post_txn_function in varchar2
388 ,p_route_validated_txn_flag in varchar2
389 ,p_prevent_approver_skip in varchar2
390 ,p_workflow_enable_flag in varchar2
391 ,p_timeout_days in number
392 ,p_consolid_table_alias in varchar2
393 --,p_business_group_alias in varchar2
394 ,p_setup_type_cd in varchar2
395 ,p_enable_flag in varchar2
396 ,p_master_table_alias in varchar2
397 ,p_owner in varchar2
398 ,p_last_update_date in varchar2
399 ) is
400 --
401 l_effective_date date := sysdate ;
402 l_object_version_number number := 1;
403 l_language varchar2(30) ;
404
405 --
406 l_transaction_category_id pqh_transaction_categories.transaction_category_id%type := 0;
407 l_consolidated_table_route_id pqh_transaction_categories.consolidated_table_route_id%type;
408 l_master_table_route_id pqh_transaction_categories.master_table_route_id%type;
409 l_business_group_id hr_all_organization_units.business_group_id%type;
410 --
411 --
412 l_created_by pqh_transaction_categories.created_by%TYPE;
413 l_last_updated_by pqh_transaction_categories.last_updated_by%TYPE;
414 l_creation_date pqh_transaction_categories.creation_date%TYPE;
415 l_last_update_date pqh_transaction_categories.last_update_date%TYPE;
416 l_last_update_login pqh_transaction_categories.last_update_login%TYPE;
417 --
418 --
419 cursor c1 is select userenv('LANG') from dual ;
420 --
421 Cursor c2(p_table_alias in VARCHAR2) is
422 Select table_route_id
423 From pqh_table_route
424 Where table_alias = p_table_alias;
425 --
426 --
427 Cursor c3 is
428 Select transaction_category_id
429 From pqh_transaction_categories_vl
430 Where short_name = p_short_name and business_group_id IS NULL;
431 --
432 Cursor C_Sel1 is select pqh_transaction_categories_s.nextval from sys.dual;
433 --
434 --
435 /**
436 Cursor c4 is select business_group_id
437 from hr_all_organization_units
438 where name = p_business_group_alias ;
439 **/
440 --
441 BEGIN
442 --
443 open c1;
444 fetch c1 into l_language ;
445 close c1;
446 --
447 Open c2(p_table_alias => p_consolid_table_alias);
448 Fetch c2 into l_consolidated_table_route_id;
449 Close c2;
450 --
451 Open c2(p_table_alias => p_master_table_alias);
452 Fetch c2 into l_master_table_route_id;
453 Close c2;
454 --
455 Open c3;
456 Fetch c3 into l_transaction_category_id;
457 Close c3;
458 --
459 --
460 -- Open c4;
461 -- Fetch c4 into l_business_group_id;
462 -- Close c4;
463 --
464 --
465 -- populate WHO columns
466 --
467 /**
468 if p_owner = 'SEED' then
469 l_created_by := 1;
470 l_last_updated_by := -1;
471 else
472 l_created_by := 0;
473 l_last_updated_by := 0;
474 end if;
475 **/
476
477 l_last_updated_by := fnd_load_util.owner_id(p_owner);
478 l_created_by := fnd_load_util.owner_id(p_owner);
479 /**
480 l_creation_date := sysdate;
481 l_last_update_date := sysdate;
482 **/
483 l_creation_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
484 l_last_update_date := nvl(to_date(p_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
485 l_last_update_login := 0;
486
487 --
488
489 begin
490 --
491 If l_transaction_category_id <> 0 then
492 --
493 --
494 -- If there is a row for the transaction category
495 -- update the row in the base table
496 --
497 update pqh_transaction_categories
498 set
499 custom_wf_process_name = p_custom_wf_process_name,
500 custom_workflow_name = p_custom_workflow_name,
501 form_name = p_form_name,
502 freeze_status_cd = p_freeze_status_cd,
503 future_action_cd = p_future_action_cd,
504 member_cd = p_member_cd,
505 name = p_name,
506 short_name = p_short_name,
507 post_style_cd = p_post_style_cd,
508 post_txn_function = p_post_txn_function,
509 route_validated_txn_flag = p_route_validated_txn_flag,
510 prevent_approver_skip = p_prevent_approver_skip,
511 workflow_enable_flag = p_workflow_enable_flag,
512 -- enable_flag = p_enable_flag,
513 timeout_days = p_timeout_days,
514 last_updated_by = l_last_updated_by,
515 last_update_date = l_last_update_date,
516 last_update_login = l_last_update_login,
517 consolidated_table_route_id = l_consolidated_table_route_id,
518 -- business_group_id = l_business_group_id,
519 -- setup_type_cd = p_setup_type_cd,
520 master_table_route_id = l_master_table_route_id
521 WHERE transaction_category_id = l_transaction_category_id
522 AND NVL(last_updated_by,-1) in (l_last_updated_by,1,0,-1);
523 --
524 -- update the tl table
525 --
526 if (sql%found) then
527
528 UPDATE pqh_transaction_categories_tl
529 SET name = p_name,
530 last_updated_by = l_last_updated_by,
531 last_update_date = l_last_update_date,
532 last_update_login = l_last_update_login,
533 source_lang = userenv('LANG')
534 WHERE transaction_category_id = l_transaction_category_id
535 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
536
537 If (sql%notfound) then
538 -- no row in TL table so insert row
539
540 --
541 insert into pqh_transaction_categories_tl
542 ( transaction_category_id,
543 name,
544 language,
545 source_lang,
546 created_by,
547 creation_date,
548 last_updated_by,
549 last_update_date ,
550 last_update_login
551 )
552 Select
553 l_transaction_category_id,
554 p_name,
555 l.language_code,
556 l_language ,
557 l_created_by,
558 l_creation_date,
559 l_last_updated_by,
560 l_last_update_date,
561 l_last_update_login
562 from fnd_languages l
563 where l.installed_flag in ('I','B')
564 and not exists (select null
565 from pqh_transaction_categories_tl ctl
566 where ctl.transaction_category_id = l_transaction_category_id
567 and ctl.language = l.language_code );
568 --
569 End if;
570 --
571 end if; -- sql%found for main table
572
573 Else
574 --
575 -- Select the next sequence number
576 --
577 Open C_Sel1;
578 Fetch C_Sel1 Into l_transaction_category_id;
579 Close C_Sel1;
580 --
581 --
582 --
583 -- Added the following code as a part of Zero Downtime Patching Project.
584 -- Code Starts Here.
585 --
586 PER_RIC_PKG.chk_integrity (
587 p_entity_name=>'PQH_TRANSACTION_CATEGORIES',
588 p_ref_entity_info=>
589 PER_RIC_PKG.ref_entity_tbl(
590 PER_RIC_PKG.ref_info_rec('PQH_TABLE_ROUTE',PER_RIC_PKG.column_info_tbl(
591 PER_RIC_PKG.col_info_rec('TABLE_ROUTE_ID',NULL,l_consolidated_table_route_id, NULL))),
592 PER_RIC_PKG.ref_info_rec('PQH_TABLE_ROUTE',PER_RIC_PKG.column_info_tbl(
593 PER_RIC_PKG.col_info_rec('TABLE_ROUTE_ID',NULL,l_master_table_route_id, NULL)))),
594 p_ref_type=>'INS');
595 --
596 -- Code Ends Here
597 --
598 -- Insert row into the base table
599 --
600
601 insert into pqh_transaction_categories
602 ( transaction_category_id,
603 custom_wf_process_name,
604 custom_workflow_name,
605 form_name,
606 freeze_status_cd,
607 future_action_cd,
608 member_cd,
609 name,
610 short_name,
611 post_style_cd,
612 post_txn_function,
613 route_validated_txn_flag,
614 prevent_approver_skip,
615 workflow_enable_flag,
616 -- enable_flag,
617 timeout_days,
618 consolidated_table_route_id ,
619 -- business_group_id,
620 -- setup_type_cd,
621 master_table_route_id ,
622 created_by,
623 creation_date,
624 last_updated_by,
625 last_update_date ,
626 last_update_login,
627 object_version_number
628 )
629 Values
630 (l_transaction_category_id,
631 p_custom_wf_process_name,
632 p_custom_workflow_name,
633 p_form_name,
634 p_freeze_status_cd,
635 p_future_action_cd,
636 p_member_cd,
637 p_name,
638 p_short_name,
639 p_post_style_cd,
640 p_post_txn_function,
641 p_route_validated_txn_flag,
642 p_prevent_approver_skip,
643 p_workflow_enable_flag,
644 -- p_enable_flag,
645 p_timeout_days,
646 l_consolidated_table_route_id ,
647 -- l_business_group_id,
648 -- p_setup_type_cd,
649 l_master_table_route_id ,
650 l_created_by,
651 l_creation_date,
652 l_last_updated_by,
653 l_last_update_date,
654 l_last_update_login,
655 l_object_version_number
656 );
657
658 --
659 -- Insert row into the tl table
660 --
661 insert into pqh_transaction_categories_tl
662 ( transaction_category_id,
663 name,
664 language,
665 source_lang,
666 created_by,
667 creation_date,
668 last_updated_by,
669 last_update_date ,
670 last_update_login
671 )
672 Select
673 l_transaction_category_id,
674 p_name,
675 l.language_code,
676 l_language ,
677 l_created_by,
678 l_creation_date,
679 l_last_updated_by,
680 l_last_update_date,
681 l_last_update_login
682 from fnd_languages l
683 where l.installed_flag in ('I','B')
684 and not exists (select null
685 from pqh_transaction_categories_tl ctl
686 where ctl.transaction_category_id = l_transaction_category_id
687 and ctl.language = l.language_code );
688 --
689 --
690 --
691 End if;
692 --
693 End;
694 --
695 End load_row;
696 --
697 -- ----------------------------------------------------------------------------
698 -- |-----------------------------< add_language >------------------------------|
699 -- ----------------------------------------------------------------------------
700 -- Procedure added as a fix for bug 5484366
701 --
702 Procedure ADD_LANGUAGE
703 is
704 begin
705 delete from PQH_TRANSACTION_CATEGORIES_TL T
706 where not exists
707 (select NULL
708 from PQH_TRANSACTION_CATEGORIES B
709 where B.TRANSACTION_CATEGORY_ID = T.TRANSACTION_CATEGORY_ID
710 );
711
712 update PQH_TRANSACTION_CATEGORIES_TL T set (
713 NAME
714 ) = (select
715 B.NAME
716 from PQH_TRANSACTION_CATEGORIES_TL B
717 where B.TRANSACTION_CATEGORY_ID = T.TRANSACTION_CATEGORY_ID
718 and B.LANGUAGE = T.SOURCE_LANG)
719 where (
720 T.TRANSACTION_CATEGORY_ID,
721 T.LANGUAGE
722 ) in (select
723 SUBT.TRANSACTION_CATEGORY_ID,
724 SUBT.LANGUAGE
725 from PQH_TRANSACTION_CATEGORIES_TL SUBB, PQH_TRANSACTION_CATEGORIES_TL SUBT
726 where SUBB.TRANSACTION_CATEGORY_ID = SUBT.TRANSACTION_CATEGORY_ID
727 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
728 and (SUBB.NAME <> SUBT.NAME
729 ));
730
731 insert into PQH_TRANSACTION_CATEGORIES_TL (
732 TRANSACTION_CATEGORY_ID,
733 NAME,
734 LAST_UPDATE_DATE,
735 CREATION_DATE,
736 CREATED_BY,
737 LAST_UPDATE_LOGIN,
738 LAST_UPDATED_BY,
739 LANGUAGE,
740 SOURCE_LANG
741 ) select
742 B.TRANSACTION_CATEGORY_ID,
743 B.NAME,
744 B.LAST_UPDATE_DATE,
745 B.CREATION_DATE,
746 B.CREATED_BY,
747 B.LAST_UPDATE_LOGIN,
748 B.LAST_UPDATED_BY,
749 L.LANGUAGE_CODE,
750 B.SOURCE_LANG
751 from PQH_TRANSACTION_CATEGORIES_TL B, FND_LANGUAGES L
752 where L.INSTALLED_FLAG in ('I', 'B')
753 and B.LANGUAGE = userenv('LANG')
754 and not exists
755 (select NULL
756 from PQH_TRANSACTION_CATEGORIES_TL T
757 where T.TRANSACTION_CATEGORY_ID = B.TRANSACTION_CATEGORY_ID
758 and T.LANGUAGE = L.LANGUAGE_CODE);
759 end ADD_LANGUAGE;
760 -- --
761 -- --
762 end pqh_tct_shd;