[Home] [Help]
PACKAGE BODY: APPS.MSD_CS_IDEN_LOAD_DATA
Source
1 package body msd_CS_IDEN_load_data as
2 /* $Header: msdcsidb.pls 115.5 2004/08/26 10:32:54 sudekuma ship $ */
3
4 Procedure load_row (
5 p_column_identifier in varchar2,
6 p_system_flag in varchar2,
7 p_description in varchar2,
8 p_identifier_type in varchar2,
9 p_user_prompt in varchar2,
10 p_owner in varchar2
11 ) is
12 Begin
13
14 Update_row(
15 p_column_identifier,
16 p_system_flag ,
17 p_description ,
18 p_identifier_type ,
19 p_user_prompt ,
20 p_owner );
21
22 Exception
23 when no_data_found then
24 Insert_row(
25 p_column_identifier,
26 p_system_flag ,
27 p_description ,
28 p_identifier_type ,
29 p_user_prompt ,
30 p_owner);
31 End;
32
33
34 Procedure Update_row (
35 p_column_identifier in varchar2,
36 p_system_flag in varchar2,
37 p_description in varchar2,
38 p_identifier_type in varchar2,
39 p_user_prompt in varchar2,
40 p_owner in varchar2
41 ) is
42
43
44 l_user number;
45 l_definition_id number;
46 Begin
47 if p_owner = 'SEED' then
48 l_user := 1;
49 else
50 l_user := 0;
51 end if;
52
53 update msd_cs_clmn_identifiers set
54 identifier_type = p_identifier_type,
55 system_flag = p_system_flag,
56 last_update_date = sysdate,
57 last_updated_by = l_user,
58 last_update_login = fnd_global.login_id
59 where
60 column_identifier = p_column_identifier;
61
62 if (sql%notfound) then
63 raise no_data_found;
64 end if;
65
66 update msd_cs_clmn_identifiers_TL set
67 description = p_description,
68 user_prompt = p_user_prompt,
69 LAST_UPDATE_DATE = sysdate,
70 LAST_UPDATED_BY = l_user,
71 LAST_UPDATE_LOGIN = fnd_global.login_id,
72 SOURCE_LANG = userenv('LANG')
73 where
74 column_identifier = p_column_identifier
75 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
76
77 if (sql%notfound) then
78
79 insert into msd_cs_clmn_identifiers_TL(
80 column_identifier,
81 description,
82 user_prompt,
83 language,
84 source_lang,
85 created_by,
86 creation_date,
87 last_updated_by,
88 last_update_date ,
89 last_update_login
90 )
91 Select
92 p_column_identifier,
93 p_description,
94 p_user_prompt,
95 l.language_code,
96 userenv('LANG'),
97 fnd_global.user_id,
98 sysdate,
99 fnd_global.user_id,
100 sysdate,
101 fnd_global.login_id
102 from fnd_languages l
103 where l.installed_flag in ('I','B');
104 /* and not exists (select null
105 from msd_cs_definitions_TL
106 and rtl.language = l.language_code );
107 */
108 end if;
109
110 End;
111
112 Procedure Insert_row (
113 p_column_identifier in varchar2,
114 p_system_flag in varchar2,
115 p_description in varchar2,
116 p_identifier_type in varchar2,
117 p_user_prompt in varchar2,
118 p_owner in varchar2
119 ) is
120
121
122 l_user number;
123 l_definition_id number;
124 Begin
125 if p_owner = 'SEED' then
126 l_user := 1;
127 else
128 l_user := 0;
129 end if;
130
131
132 insert into msd_cs_clmn_identifiers(
133 column_identifier,
134 system_flag,
135 identifier_type,
136 created_by,
137 creation_date,
138 last_updated_by,
139 last_update_date ,
140 last_update_login
141 )
142 values
143 (
144 p_column_identifier,
145 p_system_flag,
146 p_identifier_type,
147 l_user,
148 sysdate,
149 l_user,
150 sysdate,
151 fnd_global.login_id
152 );
153
154 insert into msd_cs_clmn_identifiers_TL(
155 column_identifier,
156 description,
157 user_prompt,
158 language,
159 source_lang,
160 created_by,
161 creation_date,
162 last_updated_by,
163 last_update_date ,
164 last_update_login
165 )
166 Select
167 p_column_identifier,
168 p_description,
169 p_user_prompt,
170 l.language_code,
171 userenv('LANG'),
172 fnd_global.user_id,
173 sysdate,
174 fnd_global.user_id,
175 sysdate,
176 fnd_global.login_id
177 from fnd_languages l
178 where l.installed_flag in ('I','B');
179 End;
180
181 Procedure translate_row (
182 p_column_identifier in varchar2,
183 p_description in varchar2,
184 p_user_prompt in varchar2,
185 p_owner in varchar2) is
186
187 l_user number:= 1;
188 Begin
189 if p_owner = 'SEED' then
190 l_user := 1;
191 else
192 l_user := 0;
193 end if;
194
195 update msd_cs_clmn_identifiers_TL set
196 description = p_description,
197 user_prompt = p_user_prompt,
198 LAST_UPDATE_DATE = sysdate,
199 LAST_UPDATED_BY = l_user,
200 LAST_UPDATE_LOGIN = fnd_global.login_id,
201 SOURCE_LANG = userenv('LANG')
202 where
203 column_identifier = p_column_identifier
204 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
205
206 End;
207
208 Procedure ADD_LANGUAGE
209 is
210 begin
211 delete from msd_cs_clmn_identifiers_TL T
212 where not exists
213 (select NULL
214 from msd_cs_clmn_identifiers B
215 where B.column_identifier = T.column_identifier
216 );
217
218 update msd_cs_clmn_identifiers_TL T set (
219 DESCRIPTION,user_prompt
220 ) = (select
221 B.DESCRIPTION, b.user_prompt
222 from msd_cs_clmn_identifiers_TL B
223 where B.column_identifier = T.column_identifier
224 and B.LANGUAGE = T.SOURCE_LANG)
225 where (
226 T.column_identifier,
227 T.LANGUAGE
228 ) in (select
229 SUBT.column_identifier,
230 SUBT.LANGUAGE
231 from msd_cs_clmn_identifiers_TL SUBB, msd_cs_clmn_identifiers_TL SUBT
232 where SUBB.column_identifier = SUBT.column_identifier
233 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
234 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
235 OR SUBB.USER_PROMPT <> SUBT.USER_PROMPT -- sudekuma bug # 3845894
236 ));
237
238 insert into msd_cs_clmn_identifiers_TL (
239 column_identifier,
240 description,
241 user_prompt,
242 CREATION_DATE,
243 CREATED_BY,
244 LAST_UPDATE_DATE,
245 LAST_UPDATED_BY,
246 LAST_UPDATE_LOGIN,
247 REQUEST_ID,
248 PROGRAM_APPLICATION_ID,
249 PROGRAM_ID,
250 PROGRAM_UPDATE_DATE,
251 LANGUAGE,
252 SOURCE_LANG
253 ) select
254 b.column_identifier,
255 b.description,
256 b.user_prompt,
257 B.CREATION_DATE,
258 B.CREATED_BY,
259 B.LAST_UPDATE_DATE,
260 B.LAST_UPDATED_BY,
261 B.LAST_UPDATE_LOGIN,
262 B.REQUEST_ID,
263 B.PROGRAM_APPLICATION_ID,
264 B.PROGRAM_ID,
265 B.PROGRAM_UPDATE_DATE,
266 L.LANGUAGE_CODE,
267 B.SOURCE_LANG
268 from msd_cs_clmn_identifiers_TL B, FND_LANGUAGES L
269 where L.INSTALLED_FLAG in ('I', 'B')
270 and B.LANGUAGE = userenv('LANG')
271 and not exists
272 (select NULL
273 from msd_cs_clmn_identifiers_TL T
274 where T.column_identifier = B.column_identifier
275 and T.LANGUAGE = L.LANGUAGE_CODE);
276
277 End ADD_LANGUAGE;
278
279
280 End;