1 package body ENG_CHANGE_MGMT_TYPES_PKG as
2 /* $Header: ENGTYPEB.pls 115.2 2002/12/03 19:43:12 sshrikha noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
6 X_ENABLE_REV_ITEMS_FLAG in VARCHAR2,
7 X_ENABLE_TASKS_FLAG in VARCHAR2,
8 X_DISABLE_FLAG in VARCHAR2,
9 X_SEQUENCE_NUMBER in NUMBER,
10 X_NAME in VARCHAR2,
11 X_DESCRIPTION in VARCHAR2,
12 X_TAB_TEXT in VARCHAR2,
13 X_FORM_FUNCTION_NAME in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from ENG_CHANGE_MGMT_TYPES
21 where CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE
22 ;
23 begin
24 insert into ENG_CHANGE_MGMT_TYPES (
25 ENABLE_REV_ITEMS_FLAG,
26 ENABLE_TASKS_FLAG,
27 DISABLE_FLAG,
28 CHANGE_MGMT_TYPE_CODE,
29 SEQUENCE_NUMBER,
30 FORM_FUNCTION_NAME,
31 CREATION_DATE,
32 CREATED_BY,
33 LAST_UPDATE_DATE,
34 LAST_UPDATED_BY,
35 LAST_UPDATE_LOGIN
36 ) values (
37 X_ENABLE_REV_ITEMS_FLAG,
38 X_ENABLE_TASKS_FLAG,
39 X_DISABLE_FLAG,
40 X_CHANGE_MGMT_TYPE_CODE,
41 X_SEQUENCE_NUMBER,
42 X_FORM_FUNCTION_NAME,
43 X_CREATION_DATE,
44 X_CREATED_BY,
45 X_LAST_UPDATE_DATE,
46 X_LAST_UPDATED_BY,
47 X_LAST_UPDATE_LOGIN
48 );
49
50 insert into ENG_CHANGE_MGMT_TYPES_TL (
51 TAB_TEXT,
52 NAME,
53 CHANGE_MGMT_TYPE_CODE,
54 CREATED_BY,
55 CREATION_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATE_LOGIN,
59 DESCRIPTION,
60 LANGUAGE,
61 SOURCE_LANG
62 ) select
63 X_TAB_TEXT,
64 X_NAME,
65 X_CHANGE_MGMT_TYPE_CODE,
66 X_CREATED_BY,
67 X_CREATION_DATE,
68 X_LAST_UPDATED_BY,
69 X_LAST_UPDATE_DATE,
70 X_LAST_UPDATE_LOGIN,
71 X_DESCRIPTION,
72 L.LANGUAGE_CODE,
73 userenv('LANG')
74 from FND_LANGUAGES L
75 where L.INSTALLED_FLAG in ('I', 'B')
76 and not exists
77 (select NULL
78 from ENG_CHANGE_MGMT_TYPES_TL T
79 where T.CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE
80 and T.LANGUAGE = L.LANGUAGE_CODE);
81
82 open c;
83 fetch c into X_ROWID;
84 if (c%notfound) then
85 close c;
86 raise no_data_found;
87 end if;
88 close c;
89
90 end INSERT_ROW;
91
92 procedure LOCK_ROW (
93 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
94 X_ENABLE_REV_ITEMS_FLAG in VARCHAR2,
95 X_ENABLE_TASKS_FLAG in VARCHAR2,
96 X_DISABLE_FLAG in VARCHAR2,
97 X_SEQUENCE_NUMBER in NUMBER,
98 X_NAME in VARCHAR2,
99 X_DESCRIPTION in VARCHAR2,
100 X_TAB_TEXT in VARCHAR2,
101 X_FORM_FUNCTION_NAME in VARCHAR2
102 )
103 is
104 cursor c is select
105 ENABLE_REV_ITEMS_FLAG,
106 ENABLE_TASKS_FLAG,
107 DISABLE_FLAG,
108 SEQUENCE_NUMBER,
109 FORM_FUNCTION_NAME
110 from ENG_CHANGE_MGMT_TYPES
111 where CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE
112 for update of CHANGE_MGMT_TYPE_CODE nowait;
113 recinfo c%rowtype;
114
115 cursor c1 is select
116 NAME,
117 DESCRIPTION,
118 TAB_TEXT,
119 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
120 from ENG_CHANGE_MGMT_TYPES_TL
121 where CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE
122 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
123 for update of CHANGE_MGMT_TYPE_CODE nowait;
124 begin
125 open c;
126 fetch c into recinfo;
127 if (c%notfound) then
128 close c;
129 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
130 app_exception.raise_exception;
131 end if;
132 close c;
133 if ( ((recinfo.ENABLE_REV_ITEMS_FLAG = X_ENABLE_REV_ITEMS_FLAG)
134 OR ((recinfo.ENABLE_REV_ITEMS_FLAG is null) AND (X_ENABLE_REV_ITEMS_FLAG is null)))
135 AND ((recinfo.ENABLE_TASKS_FLAG = X_ENABLE_TASKS_FLAG)
136 OR ((recinfo.ENABLE_TASKS_FLAG is null) AND (X_ENABLE_TASKS_FLAG is null)))
137 AND ((recinfo.DISABLE_FLAG = X_DISABLE_FLAG)
138 OR ((recinfo.DISABLE_FLAG is null) AND (X_DISABLE_FLAG is null)))
139 AND ((recinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
140 OR ((recinfo.SEQUENCE_NUMBER is null) AND (X_SEQUENCE_NUMBER is null)))
141 AND ((recinfo.FORM_FUNCTION_NAME = X_FORM_FUNCTION_NAME)
142 OR ((recinfo.FORM_FUNCTION_NAME is null) AND (X_FORM_FUNCTION_NAME is null)))
143 ) then
144 null;
145 else
146 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
147 app_exception.raise_exception;
148 end if;
149
150 for tlinfo in c1 loop
151 if (tlinfo.BASELANG = 'Y') then
152 if ( ((tlinfo.NAME = X_NAME)
153 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
154 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
155 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
156 AND ((tlinfo.TAB_TEXT = X_TAB_TEXT)
157 OR ((tlinfo.TAB_TEXT is null) AND (X_TAB_TEXT is null)))
158 ) then
159 null;
160 else
161 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162 app_exception.raise_exception;
163 end if;
164 end if;
165 end loop;
166 return;
167 end LOCK_ROW;
168
169 procedure UPDATE_ROW (
170 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2,
171 X_ENABLE_REV_ITEMS_FLAG in VARCHAR2,
172 X_ENABLE_TASKS_FLAG in VARCHAR2,
173 X_DISABLE_FLAG in VARCHAR2,
174 X_SEQUENCE_NUMBER in NUMBER,
175 X_NAME in VARCHAR2,
176 X_DESCRIPTION in VARCHAR2,
177 X_TAB_TEXT in VARCHAR2,
178 X_FORM_FUNCTION_NAME in VARCHAR2,
179 X_LAST_UPDATE_DATE in DATE,
180 X_LAST_UPDATED_BY in NUMBER,
181 X_LAST_UPDATE_LOGIN in NUMBER
182 )
183 is
184 begin
185 update ENG_CHANGE_MGMT_TYPES set
186 ENABLE_REV_ITEMS_FLAG = X_ENABLE_REV_ITEMS_FLAG,
187 ENABLE_TASKS_FLAG = X_ENABLE_TASKS_FLAG,
188 DISABLE_FLAG = X_DISABLE_FLAG,
189 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
190 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
191 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
192 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
193 FORM_FUNCTION_NAME = X_FORM_FUNCTION_NAME
194 where CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE;
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199
200 update ENG_CHANGE_MGMT_TYPES_TL set
201 NAME = X_NAME,
202 DESCRIPTION = X_DESCRIPTION,
203 TAB_TEXT = X_TAB_TEXT,
204 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
205 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
206 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
207 SOURCE_LANG = userenv('LANG')
208 where CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE
209 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
210
211 if (sql%notfound) then
212 raise no_data_found;
213 end if;
214 end UPDATE_ROW;
215
216 procedure DELETE_ROW (
217 X_CHANGE_MGMT_TYPE_CODE in VARCHAR2
218 ) is
219 begin
220 delete from ENG_CHANGE_MGMT_TYPES_TL
221 where CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE;
222
223 if (sql%notfound) then
224 raise no_data_found;
225 end if;
226
227 delete from ENG_CHANGE_MGMT_TYPES
228 where CHANGE_MGMT_TYPE_CODE = X_CHANGE_MGMT_TYPE_CODE;
229
230 if (sql%notfound) then
231 raise no_data_found;
232 end if;
233 end DELETE_ROW;
234
235 procedure ADD_LANGUAGE
236 is
237 begin
238 delete from ENG_CHANGE_MGMT_TYPES_TL T
239 where not exists
240 (select NULL
241 from ENG_CHANGE_MGMT_TYPES B
242 where B.CHANGE_MGMT_TYPE_CODE = T.CHANGE_MGMT_TYPE_CODE
243 );
244
245 update ENG_CHANGE_MGMT_TYPES_TL T set (
246 NAME,
247 DESCRIPTION,
248 TAB_TEXT
249 ) = (select
250 B.NAME,
251 B.DESCRIPTION,
252 B.TAB_TEXT
253 from ENG_CHANGE_MGMT_TYPES_TL B
254 where B.CHANGE_MGMT_TYPE_CODE = T.CHANGE_MGMT_TYPE_CODE
255 and B.LANGUAGE = T.SOURCE_LANG)
256 where (
257 T.CHANGE_MGMT_TYPE_CODE,
258 T.LANGUAGE
259 ) in (select
260 SUBT.CHANGE_MGMT_TYPE_CODE,
261 SUBT.LANGUAGE
262 from ENG_CHANGE_MGMT_TYPES_TL SUBB, ENG_CHANGE_MGMT_TYPES_TL SUBT
263 where SUBB.CHANGE_MGMT_TYPE_CODE = SUBT.CHANGE_MGMT_TYPE_CODE
264 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
265 and (SUBB.NAME <> SUBT.NAME
266 or (SUBB.NAME is null and SUBT.NAME is not null)
267 or (SUBB.NAME is not null and SUBT.NAME is null)
268 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
269 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
270 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
271 or SUBB.TAB_TEXT <> SUBT.TAB_TEXT
272 or (SUBB.TAB_TEXT is null and SUBT.TAB_TEXT is not null)
273 or (SUBB.TAB_TEXT is not null and SUBT.TAB_TEXT is null)
274 ));
275
276 insert into ENG_CHANGE_MGMT_TYPES_TL (
277 TAB_TEXT,
278 NAME,
279 CHANGE_MGMT_TYPE_CODE,
280 CREATED_BY,
281 CREATION_DATE,
282 LAST_UPDATED_BY,
283 LAST_UPDATE_DATE,
284 LAST_UPDATE_LOGIN,
285 DESCRIPTION,
286 LANGUAGE,
287 SOURCE_LANG
288 ) select
289 B.TAB_TEXT,
290 B.NAME,
291 B.CHANGE_MGMT_TYPE_CODE,
292 B.CREATED_BY,
293 B.CREATION_DATE,
294 B.LAST_UPDATED_BY,
295 B.LAST_UPDATE_DATE,
296 B.LAST_UPDATE_LOGIN,
297 B.DESCRIPTION,
298 L.LANGUAGE_CODE,
299 B.SOURCE_LANG
300 from ENG_CHANGE_MGMT_TYPES_TL B, FND_LANGUAGES L
301 where L.INSTALLED_FLAG in ('I', 'B')
302 and B.LANGUAGE = userenv('LANG')
303 and not exists
304 (select NULL
305 from ENG_CHANGE_MGMT_TYPES_TL T
306 where T.CHANGE_MGMT_TYPE_CODE = B.CHANGE_MGMT_TYPE_CODE
307 and T.LANGUAGE = L.LANGUAGE_CODE);
308 end ADD_LANGUAGE;
309
310 end ENG_CHANGE_MGMT_TYPES_PKG;
[Home] [Help]