[Home] [Help]
PACKAGE BODY: APPS.AMS_PROD_TEMPLATE_PKG
Source
1 package body AMS_PROD_TEMPLATE_PKG as
2 /* $Header: amstptmb.pls 115.4 2003/03/11 00:26:00 mukumar ship $ */
3
4 procedure ADD_LANGUAGE
5 is
6 begin
7 delete from AMS_PROD_TEMPLATES_TL T
8 where not exists
9 (select NULL
10 from AMS_PROD_TEMPLATES_B B
11 where B.TEMPLATE_ID = T.TEMPLATE_ID
12 );
13
14 update AMS_PROD_TEMPLATES_TL T set (
15 TEMPLATE_NAME,
16 DESCRIPTION
17 ) = (select
18 B.TEMPLATE_NAME,
19 B.DESCRIPTION
20 from AMS_PROD_TEMPLATES_TL B
21 where B.TEMPLATE_ID = T.TEMPLATE_ID
22 and B.LANGUAGE = T.SOURCE_LANG)
23 where (
24 T.TEMPLATE_ID,
25 T.LANGUAGE
26 ) in (select
27 SUBT.TEMPLATE_ID,
28 SUBT.LANGUAGE
29 from AMS_PROD_TEMPLATES_TL SUBB, AMS_PROD_TEMPLATES_TL SUBT
30 where SUBB.template_ID = SUBT.template_ID
31 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
32 and (SUBB.TEMPLATE_NAME <> SUBT.TEMPLATE_NAME
33 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
34 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
35 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
36 ));
37
38 insert into AMS_PROD_TEMPLATES_TL (
39 template_ID,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_LOGIN,
45 template_NAME,
46 DESCRIPTION,
47 LANGUAGE,
48 SOURCE_LANG
49 ) select
50 B.template_ID,
51 B.LAST_UPDATE_DATE,
52 B.LAST_UPDATED_BY,
53 B.CREATION_DATE,
54 B.CREATED_BY,
55 B.LAST_UPDATE_LOGIN,
56 B.template_NAME,
57 B.DESCRIPTION,
58 L.LANGUAGE_CODE,
59 B.SOURCE_LANG
60 from AMS_PROD_TEMPLATES_TL B, FND_LANGUAGES L
61 where L.INSTALLED_FLAG in ('I', 'B')
62 and B.LANGUAGE = userenv('LANG')
63 and not exists
64 (select NULL
65 from AMS_PROD_TEMPLATES_TL T
66 where T.template_ID = B.template_ID
67 and T.LANGUAGE = L.LANGUAGE_CODE);
68
69 end ADD_LANGUAGE;
70
71
72 procedure TRANSLATE_ROW(
73 x_template_id in NUMBER
74 , x_template_name in VARCHAR2
75 , x_description in VARCHAR2
76 , x_owner in VARCHAR2
77 ) is
78 begin
79 update AMS_PROD_TEMPLATES_TL set
80 template_name = nvl(x_template_name, template_name),
81 description = nvl(x_description, description),
82 source_lang = userenv('LANG'),
83 last_update_date = sysdate,
84 last_updated_by = decode(x_owner, 'SEED', 1, 0),
85 last_update_login = 0
86 where template_id = x_template_id
87 and userenv('LANG') in (language, source_lang);
88
89 end TRANSLATE_ROW;
90
91
92 procedure LOAD_ROW(
93 X_TEMPLATE_ID IN NUMBER,
94 X_PRODUCT_SERVICE_FLAG IN VARCHAR2,
95 X_TEMPLATE_NAME IN VARCHAR2,
96 X_DESCRIPTION IN VARCHAR2 ,
97 X_Owner in VARCHAR2,
98 X_CUSTOM_MODE IN VARCHAR2
99
100 ) is
101
102 l_user_id number := 0;
103 l_obj_verno number;
104 l_dummy_char varchar2(1);
105 l_row_id varchar2(100);
106 l_template_id number;
107 l_db_luby_id number;
108
109 cursor c_obj_verno is
110 select object_version_number
111 from AMS_PROD_TEMPLATES_B
112 where template_id = X_TEMPLATE_ID;
113
114 cursor c_chk_temp_exists is
115 select 'x'
116 from AMS_PROD_TEMPLATES_B
117 where template_id = X_TEMPLATE_ID;
118
119 cursor c_get_tempid is
120 select AMS_PROD_TEMPLATES_B_S.nextval
121 from dual;
122
123 cursor c_db_data_details is
124 select last_updated_by, nvl(object_version_number,1)
125 from AMS_PROD_TEMPLATES_B
126 where template_id = X_TEMPLATE_ID;
127
128 BEGIN
129
130 if X_OWNER = 'SEED' then
131 l_user_id := 1;
132 elsif X_OWNER = 'ORACLE' THEN
133 l_user_id := 2;
134 elsif X_OWNER = 'SYSADMIN' THEN
135 l_user_id := 0;
136 end if ;
137
138 open c_chk_temp_exists;
139 fetch c_chk_temp_exists into l_dummy_char;
140
141 if c_chk_temp_exists%notfound
142 then
143 close c_chk_temp_exists;
144 if X_TEMPLATE_ID is null
145 then
146 open c_get_tempid;
147 fetch c_get_tempid into l_template_id;
148 close c_get_tempid;
149 else
150 l_template_id := X_TEMPLATE_ID;
151 end if;
152
153 l_obj_verno := 1;
154
155 INSERT INTO AMS_PROD_TEMPLATES_B(
156 template_id,
157 last_update_date,
158 last_updated_by,
159 creation_date,
160 created_by,
161 object_version_number,
162 last_update_login,
163 product_service_flag
164 ) VALUES (
165 l_template_id
166 ,SYSDATE
167 ,l_user_id
168 ,SYSDATE
169 ,l_user_id
170 ,1
171 ,0
172 ,X_PRODUCT_SERVICE_FLAG);
173
174 INSERT INTO AMS_PROD_TEMPLATES_TL(
175 template_id
176 ,language
177 ,source_lang
178 ,last_update_date
179 ,last_updated_by
180 ,last_update_login
181 ,creation_date
182 ,created_by
183 ,template_name
184 ,description
185 ) SELECT
186 l_template_id,
187 l.language_code,
188 USERENV('LANG'),
189 sysdate,
190 l_user_id,
191 0,
192 sysdate,
193 l_user_id,
194 X_TEMPLATE_NAME,
195 X_DESCRIPTION
196 FROM fnd_languages l
197 WHERE l.installed_flag IN ('I','B')
198 AND NOT EXISTS(
199 SELECT NULL
200 FROM AMS_PROD_TEMPLATES_TL t
201 WHERE t.template_id = DECODE( l_template_id, FND_API.g_miss_num, NULL, l_template_id)
202 AND t.language = l.language_code ) ;
203
204 else
205 close c_chk_temp_exists;
206 open c_db_data_details;
207 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
208 close c_db_data_details;
209
210 if (l_db_luby_id IN (1,2,0)
211 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
212 Update AMS_PROD_TEMPLATES_B
213 SET last_update_date = sysdate,
214 last_updated_by = l_user_id,
215 object_version_number = l_obj_verno +1,
216 last_update_login = 0,
217 product_service_flag = X_PRODUCT_SERVICE_FLAG
218 WHERE TEMPLATE_ID = X_TEMPLATE_ID
219 AND object_version_number = l_obj_verno;
220
221 UPDATE AMS_PROD_TEMPLATES_TL
222 SET template_name = X_TEMPLATE_NAME
223 ,description = X_DESCRIPTION
224 ,last_update_date = sysdate
225 ,last_updated_by = l_user_id
226 ,last_update_login = 0
227 ,source_lang = USERENV('LANG')
228 WHERE TEMPLATE_ID = X_TEMPLATE_ID
229 AND USERENV('LANG') IN (language, source_lang);
230 end if;
231 end if;
232
233 END LOAD_ROW;
234
235 end AMS_PROD_TEMPLATE_PKG;