[Home] [Help]
PACKAGE BODY: APPS.PQH_TCT_SHD
Source
1 Package Body pqh_tct_shd as
2 /* $Header: pqtctrhi.pkb 120.4.12000000.2 2007/04/19 12:48:04 brsinha noship $ */
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 -- Insert row into the base table
583 --
584
585 insert into pqh_transaction_categories
586 ( transaction_category_id,
587 custom_wf_process_name,
588 custom_workflow_name,
589 form_name,
590 freeze_status_cd,
591 future_action_cd,
592 member_cd,
593 name,
594 short_name,
595 post_style_cd,
596 post_txn_function,
597 route_validated_txn_flag,
598 prevent_approver_skip,
599 workflow_enable_flag,
600 -- enable_flag,
601 timeout_days,
602 consolidated_table_route_id ,
603 -- business_group_id,
604 -- setup_type_cd,
605 master_table_route_id ,
606 created_by,
607 creation_date,
608 last_updated_by,
609 last_update_date ,
610 last_update_login,
611 object_version_number
612 )
613 Values
614 (l_transaction_category_id,
615 p_custom_wf_process_name,
616 p_custom_workflow_name,
617 p_form_name,
618 p_freeze_status_cd,
619 p_future_action_cd,
620 p_member_cd,
621 p_name,
622 p_short_name,
623 p_post_style_cd,
624 p_post_txn_function,
625 p_route_validated_txn_flag,
626 p_prevent_approver_skip,
627 p_workflow_enable_flag,
628 -- p_enable_flag,
629 p_timeout_days,
630 l_consolidated_table_route_id ,
631 -- l_business_group_id,
632 -- p_setup_type_cd,
633 l_master_table_route_id ,
634 l_created_by,
635 l_creation_date,
636 l_last_updated_by,
637 l_last_update_date,
638 l_last_update_login,
639 l_object_version_number
640 );
641
642 --
643 -- Insert row into the tl table
644 --
645 insert into pqh_transaction_categories_tl
646 ( transaction_category_id,
647 name,
648 language,
649 source_lang,
650 created_by,
651 creation_date,
652 last_updated_by,
653 last_update_date ,
654 last_update_login
655 )
656 Select
657 l_transaction_category_id,
658 p_name,
659 l.language_code,
660 l_language ,
661 l_created_by,
662 l_creation_date,
663 l_last_updated_by,
664 l_last_update_date,
665 l_last_update_login
666 from fnd_languages l
667 where l.installed_flag in ('I','B')
668 and not exists (select null
669 from pqh_transaction_categories_tl ctl
670 where ctl.transaction_category_id = l_transaction_category_id
671 and ctl.language = l.language_code );
672 --
673 --
674 --
675 End if;
676 --
677 End;
678 --
679 End load_row;
680 --
681 -- ----------------------------------------------------------------------------
682 -- |-----------------------------< add_language >------------------------------|
683 -- ----------------------------------------------------------------------------
684 -- Procedure added as a fix for bug 5484366
685 --
686 Procedure ADD_LANGUAGE
687 is
688 begin
689 delete from PQH_TRANSACTION_CATEGORIES_TL T
690 where not exists
691 (select NULL
692 from PQH_TRANSACTION_CATEGORIES B
693 where B.TRANSACTION_CATEGORY_ID = T.TRANSACTION_CATEGORY_ID
694 );
695
696 update PQH_TRANSACTION_CATEGORIES_TL T set (
697 NAME
698 ) = (select
699 B.NAME
700 from PQH_TRANSACTION_CATEGORIES_TL B
701 where B.TRANSACTION_CATEGORY_ID = T.TRANSACTION_CATEGORY_ID
702 and B.LANGUAGE = T.SOURCE_LANG)
703 where (
704 T.TRANSACTION_CATEGORY_ID,
705 T.LANGUAGE
706 ) in (select
707 SUBT.TRANSACTION_CATEGORY_ID,
708 SUBT.LANGUAGE
709 from PQH_TRANSACTION_CATEGORIES_TL SUBB, PQH_TRANSACTION_CATEGORIES_TL SUBT
710 where SUBB.TRANSACTION_CATEGORY_ID = SUBT.TRANSACTION_CATEGORY_ID
711 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
712 and (SUBB.NAME <> SUBT.NAME
713 ));
714
715 insert into PQH_TRANSACTION_CATEGORIES_TL (
716 TRANSACTION_CATEGORY_ID,
717 NAME,
718 LAST_UPDATE_DATE,
719 CREATION_DATE,
720 CREATED_BY,
721 LAST_UPDATE_LOGIN,
722 LAST_UPDATED_BY,
723 LANGUAGE,
724 SOURCE_LANG
725 ) select
726 B.TRANSACTION_CATEGORY_ID,
727 B.NAME,
728 B.LAST_UPDATE_DATE,
729 B.CREATION_DATE,
730 B.CREATED_BY,
731 B.LAST_UPDATE_LOGIN,
732 B.LAST_UPDATED_BY,
733 L.LANGUAGE_CODE,
734 B.SOURCE_LANG
735 from PQH_TRANSACTION_CATEGORIES_TL B, FND_LANGUAGES L
736 where L.INSTALLED_FLAG in ('I', 'B')
737 and B.LANGUAGE = userenv('LANG')
738 and not exists
739 (select NULL
740 from PQH_TRANSACTION_CATEGORIES_TL T
741 where T.TRANSACTION_CATEGORY_ID = B.TRANSACTION_CATEGORY_ID
742 and T.LANGUAGE = L.LANGUAGE_CODE);
743 end ADD_LANGUAGE;
744 -- --
745 -- --
746 end pqh_tct_shd;