[Home] [Help]
PACKAGE BODY: APPS.JTS_FLOW_STEPS_PKG
Source
1 PACKAGE BODY JTS_FLOW_STEPS_PKG as
2 /* $Header: jtstcfsb.pls 115.1 2002/06/07 11:53:13 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------
6 -- Package name : JTS_FLOW_STEPS_PKG
7 -- Purpose : details of each flow step
8 -- History : 18-Apr-02 Shirley Zou Created.
9 -- NOTE :
10 -- --------------------------------------------------------------------
11
12
13 FUNCTION GET_NEXT_STEP_ID RETURN NUMBER IS
14 l_step_id JTS_FLOW_STEPS_B.step_id%TYPE;
15 BEGIN
16 SELECT jts.jts_flow_steps_b_s.nextval
17 INTO l_step_id
18 FROM sys.dual;
19
20 return (l_step_id);
21 EXCEPTION
22 WHEN OTHERS THEN
23 APP_EXCEPTION.RAISE_EXCEPTION;
24 END GET_NEXT_STEP_ID;
25
26 -------------------------------------------------
27 -- This is for seeding the Flow Step Details.
28 -- Inserts a step.
29 -------------------------------------------------
30 PROCEDURE INSERT_ROW(
31 p_setup_page IN VARCHAR2,
32 p_flow_id IN NUMBER,
33 p_mandatory_flag IN VARCHAR2,
34 p_concurrent_flag IN VARCHAR2,
35 p_step_sequence IN NUMBER,
36 p_step_name IN VARCHAR2,
37 p_description IN VARCHAR2,
38 p_impact IN VARCHAR2,
39 p_created_by IN NUMBER,
40 p_last_updated_by IN NUMBER,
41 p_last_update_login IN NUMBER,
42 x_step_id OUT NUMBER
43 ) IS
44 BEGIN
45
46 x_step_id := get_next_step_id;
47
48 insert into JTS_FLOW_STEPS_B (
49 SETUP_PAGE,
50 STEP_ID,
51 FLOW_ID,
52 MANDATORY_FLAG,
53 CONCURRENT_FLAG,
54 STEP_SEQUENCE,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN
60 ) values (
61 p_setup_page,
62 X_STEP_ID,
63 p_flow_id,
64 p_mandatory_flag,
65 p_concurrent_flag,
66 p_step_sequence,
67 sysdate,
68 p_created_by,
69 sysdate,
70 p_last_updated_by,
71 p_last_update_login
72 );
73
74 insert into JTS_FLOW_STEPS_TL (
75 STEP_ID,
76 STEP_NAME,
77 DESCRIPTION,
78 IMPACT,
79 CREATION_DATE,
80 CREATED_BY,
81 LAST_UPDATE_DATE,
82 LAST_UPDATED_BY,
83 LAST_UPDATE_LOGIN,
84 LANGUAGE,
85 SOURCE_LANG
86 ) select
87 X_STEP_ID,
88 p_step_name,
89 P_description,
90 p_impact,
91 sysdate,
92 p_created_by,
93 sysdate,
94 p_last_updated_by,
95 p_last_update_login,
96 L.LANGUAGE_CODE,
97 userenv('LANG')
98 from FND_LANGUAGES L
99 where L.INSTALLED_FLAG in ('I', 'B')
100 and not exists
101 (select NULL
102 from JTS_FLOW_STEPS_TL T
103 where T.STEP_ID = X_STEP_ID
104 and T.LANGUAGE = L.LANGUAGE_CODE);
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 APP_EXCEPTION.RAISE_EXCEPTION;
109
110 END INSERT_ROW;
111
112 -------------------------------------------------
113 -- This is for seeding the Flow Step Details
114 -- Deletes a step based on step_id
115 -------------------------------------------------
116 PROCEDURE DELETE_ROW(p_step_id IN NUMBER) IS
117 BEGIN
118 DELETE FROM jts_flow_steps_b
119 WHERE step_id = p_step_id;
120
121 DELETE FROM jts_flow_steps_tl
122 WHERE step_id = p_step_id;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 APP_EXCEPTION.RAISE_EXCEPTION;
127
128 END DELETE_ROW;
129
130 -------------------------------------------------
131 -- This is for seeding the Flow Step Details
132 -- Warning: Should be only used in exceptional cases
133 -- where updating is absolutely necessary
134 -- because of a setup error.
135 -- Users cannot call this procedure
136 -- Updates a step
137 -------------------------------------------------
138 procedure UPDATE_ROW (
139 p_setup_page IN VARCHAR2,
140 p_step_id IN NUMBER,
141 p_flow_id IN NUMBER,
142 p_mandatory_flag IN VARCHAR2,
143 p_concurrent_flag IN VARCHAR2,
144 p_step_sequence IN NUMBER,
145 p_step_name IN VARCHAR2,
146 p_description IN VARCHAR2,
147 p_impact IN VARCHAR2,
148 P_LAST_UPDATE_DATE in DATE,
149 P_LAST_UPDATED_BY in NUMBER,
150 P_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153
154 update JTS_FLOW_STEPS_B set
155 SETUP_PAGE = P_SETUP_PAGE,
156 FLOW_ID = P_FLOW_ID,
157 MANDATORY_FLAG = P_MANDATORY_FLAG,
158 CONCURRENT_FLAG = P_CONCURRENT_FLAG,
159 STEP_SEQUENCE = P_STEP_SEQUENCE,
160 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
163 where STEP_ID = P_STEP_ID;
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168
169 update JTS_FLOW_STEPS_TL set
170 STEP_NAME = P_STEP_NAME,
171 DESCRIPTION = P_DESCRIPTION,
172 IMPACT = P_IMPACT,
173 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
174 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
175 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
176 SOURCE_LANG = userenv('LANG')
177 where STEP_ID = P_STEP_ID
178 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183 EXCEPTION
184 WHEN OTHERS THEN
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 end UPDATE_ROW;
187
188 -------------------------------------------------
189 -- This is for seeding the Flow Step Details.
190 --
191 -- Translates the flow name
192 -------------------------------------------------
193 PROCEDURE TRANSLATE_ROW (
194 p_step_id IN NUMBER,
195 p_owner IN VARCHAR2,
196 p_step_name IN VARCHAR2,
197 p_description IN VARCHAR2,
198 p_impact IN VARCHAR2
199 )
200 IS
201 BEGIN
202 update jts_flow_steps_tl set
203 step_name = nvl(p_step_name, step_name),
204 description = nvl(p_description, description),
205 impact = nvl(p_impact, impact),
206 source_lang = userenv('LANG'),
207 last_update_date = sysdate,
208 last_updated_by = decode(p_owner, 'SEED', 1, 0),
209 last_update_login = 0
210 where step_id = p_step_id
211 and userenv('LANG') in (language, source_lang);
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 END TRANSLATE_ROW;
217
218 -------------------------------------------------
219 -- This is for seeding the Flow Step Details.
220 --
221 -- Uploads a step
222 -- If p_step_id is not NULL and there is no step with
223 -- such step_id in the database, then a new step_id will be used
224 -------------------------------------------------
225 PROCEDURE LOAD_ROW (
226 p_setup_page IN VARCHAR2,
227 p_step_id IN NUMBER,
228 p_flow_id IN NUMBER,
229 P_OWNER IN VARCHAR2,
230 p_mandatory_flag IN VARCHAR2,
231 p_concurrent_flag IN VARCHAR2,
232 p_step_sequence IN NUMBER,
233 p_step_name IN VARCHAR2,
234 p_description IN VARCHAR2,
235 P_impact in VARCHAR2
236 )
237 IS
238 l_user_id JTS_FLOW_STEPS_B.created_by%TYPE := 0;
239 l_count number := 0;
240 l_step_id JTS_FLOW_STEPS_B.step_id%TYPE;
241
242 BEGIN
243 if P_OWNER = 'SEED' then
244 l_user_id := 1;
245 end if;
246
247 select count(*)
248 into l_count
249 from jts_flow_steps_b
250 where step_id = p_step_id;
251
252
253 IF (l_count = 0) THEN --no step with step_id exists. Use p_step_id to insert a new row
254 IF p_step_id IS NOT NULL THEN --use a new step_id
255 INSERT_ROW (
256 p_setup_page => p_setup_page,
257 p_flow_id => p_flow_id,
258 p_mandatory_flag => p_mandatory_flag,
259 p_concurrent_flag => p_concurrent_flag,
260 p_step_sequence => p_step_sequence,
261 p_step_name => p_step_name,
262 p_description => p_description,
263 p_impact => p_impact,
264 p_created_by => l_user_id,
265 p_last_updated_by => l_user_id,
266 p_last_update_login => 1,
267 x_step_id => l_step_id
268 );
269 END IF;
270 ELSE --step with p_step_id exists, update
271
272 UPDATE_ROW (
273 p_setup_page => p_setup_page,
274 p_step_id => p_step_id,
275 p_flow_id => p_flow_id,
276 p_mandatory_flag => p_mandatory_flag,
277 p_concurrent_flag => p_concurrent_flag,
278 p_step_sequence => p_step_sequence,
279 p_step_name => p_step_name,
280 p_description => p_description,
281 p_impact => p_impact,
282 P_LAST_UPDATE_DATE => sysdate,
283 P_LAST_UPDATED_BY => l_user_id,
284 P_LAST_UPDATE_LOGIN => 1
285 );
286 end if;
287 EXCEPTION
288 WHEN OTHERS THEN
289 APP_EXCEPTION.RAISE_EXCEPTION;
290 END LOAD_ROW;
291
292
293 -------------------------------------------------
294 -- Lock Row
295 -------------------------------------------------
296 procedure LOCK_ROW (
297 X_STEP_ID in NUMBER,
298 X_FLOW_ID in NUMBER,
299 X_MANDATORY_FLAG in VARCHAR2,
300 X_CONCURRENT_FLAG in VARCHAR2,
301 X_STEP_SEQUENCE in NUMBER,
302 X_SECURITY_GROUP_ID in NUMBER,
303 X_SETUP_PAGE in VARCHAR2,
304 X_STEP_NAME in VARCHAR2,
305 X_DESCRIPTION in VARCHAR2,
306 X_IMPACT in VARCHAR2
307 ) is
308 cursor c is select
309 FLOW_ID,
310 MANDATORY_FLAG,
311 CONCURRENT_FLAG,
312 STEP_SEQUENCE,
313 SECURITY_GROUP_ID,
314 SETUP_PAGE
315 from JTS_FLOW_STEPS_B
316 where STEP_ID = X_STEP_ID
317 for update of STEP_ID nowait;
318 recinfo c%rowtype;
319
320 cursor c1 is select
321 STEP_NAME,
322 DESCRIPTION,
323 IMPACT,
324 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
325 from JTS_FLOW_STEPS_TL
326 where STEP_ID = X_STEP_ID
327 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
328 for update of STEP_ID nowait;
329 begin
330 open c;
331 fetch c into recinfo;
332 if (c%notfound) then
333 close c;
334 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
335 app_exception.raise_exception;
336 end if;
337 close c;
338 if ( (recinfo.FLOW_ID = X_FLOW_ID)
339 AND (recinfo.MANDATORY_FLAG = X_MANDATORY_FLAG)
340 AND (recinfo.CONCURRENT_FLAG = X_CONCURRENT_FLAG)
341 AND (recinfo.STEP_SEQUENCE = X_STEP_SEQUENCE)
342 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
343 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
344 AND ((recinfo.SETUP_PAGE = X_SETUP_PAGE)
345 OR ((recinfo.SETUP_PAGE is null) AND (X_SETUP_PAGE is null)))
346 ) then
347 null;
348 else
349 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
350 app_exception.raise_exception;
351 end if;
352
353 for tlinfo in c1 loop
354 if (tlinfo.BASELANG = 'Y') then
355 if ( (tlinfo.STEP_NAME = X_STEP_NAME)
356 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
357 AND (tlinfo.IMPACT = X_IMPACT)
358 ) then
359 null;
360 else
361 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
362 app_exception.raise_exception;
363 end if;
364 end if;
365 end loop;
366 return;
367 end LOCK_ROW;
368
369 -------------------------------------------------
370 -- Add Language
371 ------------------------------------------------
372 procedure ADD_LANGUAGE
373 is
374 begin
375 delete from JTS_FLOW_STEPS_TL T
376 where not exists
377 (select NULL
378 from JTS_FLOW_STEPS_B B
379 where B.STEP_ID = T.STEP_ID
380 );
381
382 update JTS_FLOW_STEPS_TL T set (
383 STEP_NAME,
384 DESCRIPTION,
385 IMPACT
386 ) = (select
387 B.STEP_NAME,
388 B.DESCRIPTION,
389 B.IMPACT
390 from JTS_FLOW_STEPS_TL B
391 where B.STEP_ID = T.STEP_ID
392 and B.LANGUAGE = T.SOURCE_LANG)
393 where (
394 T.STEP_ID,
395 T.LANGUAGE
396 ) in (select
397 SUBT.STEP_ID,
398 SUBT.LANGUAGE
399 from JTS_FLOW_STEPS_TL SUBB, JTS_FLOW_STEPS_TL SUBT
400 where SUBB.STEP_ID = SUBT.STEP_ID
401 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
402 and (SUBB.STEP_NAME <> SUBT.STEP_NAME
403 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
404 or SUBB.IMPACT <> SUBT.IMPACT
405 ));
406
407 insert into JTS_FLOW_STEPS_TL (
408 STEP_ID,
409 STEP_NAME,
410 DESCRIPTION,
411 IMPACT,
412 CREATION_DATE,
413 CREATED_BY,
414 LAST_UPDATE_DATE,
415 LAST_UPDATED_BY,
416 LAST_UPDATE_LOGIN,
417 SECURITY_GROUP_ID,
418 LANGUAGE,
419 SOURCE_LANG
420 ) select
421 B.STEP_ID,
422 B.STEP_NAME,
423 B.DESCRIPTION,
424 B.IMPACT,
425 B.CREATION_DATE,
426 B.CREATED_BY,
427 B.LAST_UPDATE_DATE,
428 B.LAST_UPDATED_BY,
429 B.LAST_UPDATE_LOGIN,
430 B.SECURITY_GROUP_ID,
431 L.LANGUAGE_CODE,
432 B.SOURCE_LANG
433 from JTS_FLOW_STEPS_TL B, FND_LANGUAGES L
434 where L.INSTALLED_FLAG in ('I', 'B')
435 and B.LANGUAGE = userenv('LANG')
436 and not exists
437 (select NULL
438 from JTS_FLOW_STEPS_TL T
439 where T.STEP_ID = B.STEP_ID
440 and T.LANGUAGE = L.LANGUAGE_CODE);
441 end ADD_LANGUAGE;
442
443 END JTS_FLOW_STEPS_PKG;