1 package body AMW_ASSESS_PROCEDURE_STEPS_PKG as
2 /* $Header: amwtapsb.pls 120.0 2005/12/05 10:31:45 appldev noship $ */
3
4
5 procedure ADD_LANGUAGE
6 is
7 begin
8 delete from AMW_ASSESS_PROCEDURE_STEPS_TL T
9 where not exists
10 (select NULL
11 from AMW_ASSESS_PROCEDURE_STEPS_B B
12 where B.ASSESS_PROCEDURE_STEP_ID = T.ASSESS_PROCEDURE_STEP_ID
13 );
14
15 update AMW_ASSESS_PROCEDURE_STEPS_TL T set (
16 NAME,
17 DESCRIPTION
18 ) = (select
19 B.NAME,
20 B.DESCRIPTION
21 from AMW_ASSESS_PROCEDURE_STEPS_TL B
22 where B.ASSESS_PROCEDURE_STEP_ID = T.ASSESS_PROCEDURE_STEP_ID
23 and B.LANGUAGE = T.SOURCE_LANG)
24 where (
25 T.ASSESS_PROCEDURE_STEP_ID,
26 T.LANGUAGE
27 ) in (select
28 SUBT.ASSESS_PROCEDURE_STEP_ID,
29 SUBT.LANGUAGE
30 from AMW_ASSESS_PROCEDURE_STEPS_TL SUBB, AMW_ASSESS_PROCEDURE_STEPS_TL SUBT
31 where SUBB.ASSESS_PROCEDURE_STEP_ID = SUBT.ASSESS_PROCEDURE_STEP_ID
32 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
33 and (SUBB.NAME <> SUBT.NAME
34 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
35 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
36 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
37 ));
38
39 insert into AMW_ASSESS_PROCEDURE_STEPS_TL (
40 LAST_UPDATED_BY,
41 LAST_UPDATE_DATE,
42 CREATED_BY,
43 DESCRIPTION,
44 SOURCE_LANG,
45 CREATION_DATE,
46 LAST_UPDATE_LOGIN,
47 ASSESS_PROCEDURE_STEP_ID,
48 NAME,
49 LANGUAGE
50 ) select /*+ ORDERED */
51 B.LAST_UPDATED_BY,
52 B.LAST_UPDATE_DATE,
53 B.CREATED_BY,
54 B.DESCRIPTION,
55 B.SOURCE_LANG,
56 B.CREATION_DATE,
57 B.LAST_UPDATE_LOGIN,
58 B.ASSESS_PROCEDURE_STEP_ID,
59 B.NAME,
60 L.LANGUAGE_CODE
61 from AMW_ASSESS_PROCEDURE_STEPS_TL B, FND_LANGUAGES L
62 where L.INSTALLED_FLAG in ('I', 'B')
63 and B.LANGUAGE = userenv('LANG')
64 and not exists
65 (select NULL
66 from AMW_ASSESS_PROCEDURE_STEPS_TL T
67 where T.ASSESS_PROCEDURE_STEP_ID = B.ASSESS_PROCEDURE_STEP_ID
68 and T.LANGUAGE = L.LANGUAGE_CODE);
69 end ADD_LANGUAGE;
70
71
72 end AMW_ASSESS_PROCEDURE_STEPS_PKG;