1 package body EAM_SAFETY_STATUSES_PKG as
2 /* $Header: EAMSFSTB.pls 120.0.12010000.4 2010/03/24 14:30:47 vboddapa noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMSFSTB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_SAFETY_STATUSES_PKG
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 24-MAR-2010 vboddapa Initial Creation
21 ***************************************************************************/
22
23
24 --This procedure will insert new rows in EAM_SAFETY_USR_DEF_STATUSES_B and eam_safety_usr_def_statuses_tl tables
25 procedure INSERT_ROW (
26 X_ROWID in out NOCOPY VARCHAR2,
27 X_STATUS_ID in out NOCOPY NUMBER,
28 P_SEEDED_FLAG in VARCHAR2,
29 P_SYSTEM_STATUS in NUMBER,
30 P_ENABLED_FLAG in VARCHAR2,
31 P_USER_DEFINED_STATUS in VARCHAR2,
32 P_ENTITY_TYPE in NUMBER,
33 P_CREATION_DATE in DATE,
34 P_CREATED_BY in NUMBER,
35 P_LAST_UPDATE_DATE in DATE,
36 P_LAST_UPDATED_BY in NUMBER,
37 P_LAST_UPDATE_LOGIN in NUMBER
38 ) is
39
40 cursor C is select ROWID from eam_safety_usr_def_statuses_b
41 where STATUS_ID = X_STATUS_ID and
42 entity_type = P_ENTITY_TYPE;
43
44 CURSOR C2 IS SELECT eam_wo_statuses_b_s.nextval FROM sys.dual;
45
46 begin
47 if (X_Status_Id is NULL) then
48 OPEN C2;
49 FETCH C2 INTO X_Status_ID;
50 CLOSE C2;
51 end if;
52
53
54 insert into eam_safety_usr_def_statuses_b (
55 STATUS_ID,
56 SEEDED_FLAG,
57 ENTITY_TYPE,
58 SYSTEM_STATUS,
59 ENABLED_FLAG,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN
65 ) values (
66 X_STATUS_ID,
67 P_SEEDED_FLAG,
68 P_ENTITY_TYPE,
69 P_SYSTEM_STATUS,
70 P_ENABLED_FLAG,
71 P_CREATION_DATE,
72 P_CREATED_BY,
73 P_LAST_UPDATE_DATE,
74 P_LAST_UPDATED_BY,
75 P_LAST_UPDATE_LOGIN
76 );
77
78 IF( P_USER_DEFINED_STATUS IS NOT NULL) THEN
79 -- user_defined_status will be Null for seeded WIP statuses
80
81 insert into eam_safety_usr_def_statuses_tl (
82 LAST_UPDATE_LOGIN,
83 CREATION_DATE,
84 STATUS_ID,
85 USER_DEFINED_STATUS,
86 ENTITY_TYPE,
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 CREATED_BY,
90 LANGUAGE,
91 SOURCE_LANG
92 ) select
93 P_LAST_UPDATE_LOGIN,
94 P_CREATION_DATE,
95 X_STATUS_ID,
96 P_USER_DEFINED_STATUS,
97 P_ENTITY_TYPE,
98 P_LAST_UPDATE_DATE,
99 P_LAST_UPDATED_BY,
100 P_CREATED_BY,
101 L.LANGUAGE_CODE,
102 userenv('LANG')
103 from
104 FND_LANGUAGES L
105 where
106 L.INSTALLED_FLAG in ('I', 'B')
107 and not exists
108 (select NULL
109 from eam_safety_usr_def_statuses_tl T
110 where T.STATUS_ID = X_STATUS_ID
111 and T.LANGUAGE = L.LANGUAGE_CODE
112 and T.entity_type = P_ENTITY_TYPE);
113
114 open c;
115 fetch c into X_ROWID;
116 if (c%notfound) then
117 close c;
118 raise no_data_found;
119 end if;
120 close c;
121 END IF;
122 end INSERT_ROW;
123
124 --This procedure will update rows in EAM_SAFETY_USR_DEF_STATUSES_B and eam_safety_usr_def_statuses_tl tables
125 procedure UPDATE_ROW (
126 P_STATUS_ID in NUMBER,
127 P_SEEDED_FLAG in VARCHAR2,
128 P_SYSTEM_STATUS in NUMBER,
129 P_ENABLED_FLAG in VARCHAR2,
130 P_USER_DEFINED_STATUS in VARCHAR2,
131 P_ENTITY_TYPE in NUMBER,
132 P_LAST_UPDATE_DATE in DATE,
133 P_LAST_UPDATED_BY in NUMBER,
134 P_LAST_UPDATE_LOGIN in NUMBER,
135 P_MODE in VARCHAR2 DEFAULT 'FORMS'
136 ) is
137
138 l_user_id NUMBER;
139 l_resp_id NUMBER;
140 l_request_id NUMBER;
141 begin
142
143 update eam_safety_usr_def_statuses_b set
144 SEEDED_FLAG = P_SEEDED_FLAG,
145 SYSTEM_STATUS = P_SYSTEM_STATUS,
146 ENABLED_FLAG = P_ENABLED_FLAG,
147 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
148 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
149 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
150 where STATUS_ID = P_STATUS_ID and
151 ENTITY_TYPE = P_ENTITY_TYPE;
152
153 if (sql%notfound) then
154 raise no_data_found;
155 end if;
156
157 IF (P_USER_DEFINED_STATUS IS NOT NULL) THEN
158 -- user_defined_status will be Null for seeded WIP statuses
159
160 update eam_safety_usr_def_statuses_tl set
161 USER_DEFINED_STATUS = P_USER_DEFINED_STATUS,
162 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
163 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
165 SOURCE_LANG = userenv('LANG')
166 where STATUS_ID = P_STATUS_ID
167 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
168 and ENTITY_TYPE = P_ENTITY_TYPE;
169
170 if (sql%notfound) then
171 raise no_data_found;
172 end if;
173
174 IF(p_mode = 'FORMS') THEN
175 --Launch concurrent program to update work order intermedia index only when update_row is called from forms not during upgrade
176 l_user_id := fnd_global.user_id;
177 l_resp_id := fnd_global.resp_id;
178
179 IF (l_user_id IS NOT NULL AND l_resp_id IS NOT NULL) THEN
180 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,426,0);
181 END IF;
182
183 l_request_id := fnd_request.submit_request('EAM', 'EAMVTCIS', '',
184 to_char(sysdate, 'YYYY/MM/DD HH24:MI'),
185 FALSE,
186 '2',
187 '5',
188 TO_CHAR(p_status_id)
189 );
190 END IF;
191 END IF;
192
193 end UPDATE_ROW;
194
195
196
197 --This procedure will delete rows in EAM_SAFETY_USR_DEF_STATUSES_B and eam_safety_usr_def_statuses_tl tables
198 procedure DELETE_ROW (
199 P_STATUS_ID in NUMBER,
200 P_ENTITY_TYPE in NUMBER
201 ) is
202 begin
203
204 delete from eam_safety_usr_def_statuses_tl
205 where STATUS_ID = P_STATUS_ID
206 and ENTITY_TYPE = P_ENTITY_TYPE;
207
208 delete from eam_safety_usr_def_statuses_b
209 where STATUS_ID = P_STATUS_ID
210 and ENTITY_TYPE = P_ENTITY_TYPE;
211
212 if (sql%notfound) then
213 raise no_data_found;
214 end if;
215 end DELETE_ROW;
216
217 --This procedure will be called when a new langauge is installed. This will insert rows
218 -- in Eam_Wo_Statuses_TL table for the new langauge
219 procedure ADD_LANGUAGE
220 is
221 begin
222
223 delete from eam_safety_usr_def_statuses_tl T
224 where not exists
225 (select NULL
226 from eam_safety_usr_def_statuses_b B
227 where B.STATUS_ID = T.STATUS_ID
228 and B.ENTITY_TYPE = T.ENTITY_TYPE
229 );
230
231 update eam_safety_usr_def_statuses_tl T set (
232 USER_DEFINED_STATUS
233 ) = (select
234 B.USER_DEFINED_STATUS
235 from
236 eam_safety_usr_def_statuses_tl B
237 where
238 B.STATUS_ID = T.STATUS_ID
239 and B.LANGUAGE = T.SOURCE_LANG
240 and B.ENTITY_TYPE = T.ENTITY_TYPE
241 )
242 where (
243 T.STATUS_ID,
244 T.LANGUAGE,
245 T.ENTITY_TYPE
246 ) in (select
247 SUBT.STATUS_ID,
248 SUBT.LANGUAGE,
249 SUBT.ENTITY_TYPE
250 from eam_safety_usr_def_statuses_tl SUBB, eam_safety_usr_def_statuses_tl SUBT
251 where SUBB.STATUS_ID = SUBT.STATUS_ID
252 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
253 and SUBB.ENTITY_TYPE = SUBT.ENTITY_TYPE
254 and (SUBB.USER_DEFINED_STATUS <> SUBT.USER_DEFINED_STATUS
255 ));
256
257 insert into eam_safety_usr_def_statuses_tl (
258 LAST_UPDATE_LOGIN,
259 CREATION_DATE,
260 STATUS_ID,
261 USER_DEFINED_STATUS,
262 ENTITY_TYPE,
263 LAST_UPDATE_DATE,
264 LAST_UPDATED_BY,
265 CREATED_BY,
266 LANGUAGE,
267 SOURCE_LANG
268 ) select
269 B.LAST_UPDATE_LOGIN,
270 B.CREATION_DATE,
271 B.STATUS_ID,
272 B.USER_DEFINED_STATUS,
273 B.ENTITY_TYPE,
274 B.LAST_UPDATE_DATE,
275 B.LAST_UPDATED_BY,
276 B.CREATED_BY,
277 L.LANGUAGE_CODE,
278 B.SOURCE_LANG
279 from eam_safety_usr_def_statuses_tl B, FND_LANGUAGES L
280 where L.INSTALLED_FLAG in ('I', 'B')
281 and B.LANGUAGE = userenv('LANG')
282 and not exists
283 (select NULL
284 from eam_safety_usr_def_statuses_tl T
285 where T.STATUS_ID = B.STATUS_ID
286 and T.LANGUAGE = L.LANGUAGE_CODE
287 and T.ENTITY_TYPE = B.ENTITY_TYPE);
288
289 end ADD_LANGUAGE;
290
291 --This procedure will be called for all the langauges to translate the User_Defined_Status value
292 procedure TRANSLATE_ROW
293 ( P_STATUS_ID in NUMBER,
294 P_USER_DEFINED_STATUS in VARCHAR2,
295 P_ENTITY_TYPE in NUMBER,
296 P_OWNER in VARCHAR2,
297 P_LAST_UPDATE_DATE in VARCHAR2,
298 P_CUSTOM_MODE in VARCHAR2
299 ) IS
300
301 f_luby number; -- entity owner in file
302 f_ludate date; -- entity update date in file
303 db_luby number; -- entity owner in db
304 db_ludate date; -- entity update date in db
305
306 begin
307
308 IF (P_USER_DEFINED_STATUS IS NOT NULL) THEN
309 -- user_defined_status will be Null for seeded WIP statuses
310 f_luby := fnd_load_util.owner_id(P_OWNER);
311 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
312
313 select LAST_UPDATED_BY, LAST_UPDATE_DATE
314 into db_luby, db_ludate
315 from eam_safety_usr_def_statuses_tl
316 where STATUS_ID = P_STATUS_ID
317 and language = userenv('LANG')
318 and ENTITY_TYPE = P_ENTITY_TYPE;
319
320 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
321 db_ludate, P_CUSTOM_MODE)) then
322
323 update eam_safety_usr_def_statuses_tl set
324 user_defined_status = P_USER_DEFINED_STATUS,
325 last_update_date = f_ludate ,
326 last_updated_by = f_luby,
327 last_update_login = 0,
328 source_lang = userenv('LANG')
329 where STATUS_ID = P_STATUS_ID
330 and userenv('LANG') in (language, source_lang)
331 and ENTITY_TYPE = P_ENTITY_TYPE;
332
333 end if;
334 END IF;
335
336 exception
337 when no_data_found then
338 -- Do not insert missing translations, skip this row
339 null;
340 end TRANSLATE_ROW;
341
342 --This procedure will be called during upgarde of seeded statuses
343 procedure LOAD_ROW
344 (
345 X_STATUS_ID in out nocopy NUMBER,
346 P_SEEDED_FLAG in VARCHAR2,
347 P_SYSTEM_STATUS in NUMBER,
348 P_ENABLED_FLAG in VARCHAR2,
349 P_USER_DEFINED_STATUS in VARCHAR2,
350 P_ENTITY_TYPE in NUMBER,
351 P_OWNER in VARCHAR2,
352 P_LAST_UPDATE_DATE in VARCHAR2,
353 P_CUSTOM_MODE in VARCHAR2
354 ) IS
355
356 l_row_id varchar2(64);
357 f_luby number; -- entity owner in file
358 f_ludate date; -- entity update date in file
359 db_luby number; -- entity owner in db
360 db_ludate date; -- entity update date in db
361
362 begin
363
364 f_luby := fnd_load_util.owner_id(P_OWNER);
365 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
366
367 select LAST_UPDATED_BY, LAST_UPDATE_DATE
368 into db_luby, db_ludate
369 from eam_safety_usr_def_statuses_b
370 where status_id = X_STATUS_ID
371 and ENTITY_TYPE = P_ENTITY_TYPE;
372
373 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
374 db_ludate,P_CUSTOM_MODE)) then
375
376 UPDATE_ROW (X_STATUS_ID ,
377 P_SEEDED_FLAG ,
378 P_SYSTEM_STATUS ,
379 P_ENABLED_FLAG ,
380 P_USER_DEFINED_STATUS ,
381 P_ENTITY_TYPE,
382 f_ludate ,
383 f_luby ,
384 0,
385 'UPGRADE');
386
387 end if;
388
389 exception
390 when NO_DATA_FOUND then
391
392 INSERT_ROW (l_row_id ,
393 X_STATUS_ID ,
394 P_SEEDED_FLAG ,
395 P_SYSTEM_STATUS ,
396 P_ENABLED_FLAG ,
397 P_USER_DEFINED_STATUS ,
398 P_ENTITY_TYPE,
399 f_ludate ,
400 f_luby ,
401 f_ludate ,
402 f_luby ,
403 0 );
404
405 end LOAD_ROW;
406
407
408 end EAM_SAFETY_STATUSES_PKG;
[Home] [Help]