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