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