1 PACKAGE BODY JTS_SETUP_FLOW_HIEARCHY_PKG as
2 /* $Header: jtstcsfb.pls 115.3 2002/06/07 11:53:18 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------
6 -- Package name : JTS_SETUP_FLOW_HIEARCHY_PKG
7 -- Purpose : Setup Summary Hiearchy.
8 -- History : 27-Feb-02 Sung Ha Huh Created.
9 -- NOTE :
10 -- --------------------------------------------------------------------
11
12
13 FUNCTION GET_NEXT_FLOW_ID RETURN NUMBER IS
14 l_flow_id JTS_SETUP_FLOWS_B.flow_id%TYPE;
15 BEGIN
16 SELECT jts_setup_flows_b_s.nextval
17 INTO l_flow_id
18 FROM sys.dual;
19
20 return (l_flow_id);
21 EXCEPTION
22 WHEN OTHERS THEN
23 APP_EXCEPTION.RAISE_EXCEPTION;
24 END GET_NEXT_FLOW_ID;
25
26 FUNCTION GET_FLOW_ID(p_code IN VARCHAR2) RETURN NUMBER IS
27 l_flow_id JTS_SETUP_FLOWS_B.flow_id%TYPE := NULL;
28 BEGIN
29 SELECT flow_id
30 INTO l_flow_id
31 FROM JTS_SETUP_FLOWS_B
32 WHERE flow_code = p_code;
33 return (l_flow_id);
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 return NULL;
37 WHEN OTHERS THEN
38 APP_EXCEPTION.RAISE_EXCEPTION;
39 END GET_FLOW_ID;
40
41 -------------------------------------------------
42 -- This is for seeding the Flow hiearchy.
43 -- Inserts a flow. Parent_id is found by
44 -- the flow_code of the parent.
45 -- Precondition: Parents need to be inserted first
46 -- in to the tables.
47 -------------------------------------------------
48 PROCEDURE INSERT_ROW(
49 p_flow_code IN VARCHAR2,
50 p_flow_type IN VARCHAR2,
51 p_parent_code IN VARCHAR2,
52 p_has_child_flag IN VARCHAR2,
53 p_flow_sequence IN NUMBER,
54 p_overview_url IN VARCHAR2,
55 p_diagnostics_url IN VARCHAR2,
56 p_dpf_code IN VARCHAR2,
57 p_dpf_asn IN VARCHAR2,
58 p_num_steps IN NUMBER,
59 p_flow_name IN VARCHAR2,
60 p_created_by IN NUMBER,
61 p_last_updated_by IN NUMBER,
62 p_last_update_login IN NUMBER,
63 x_flow_id OUT NUMBER
64 ) IS
65 l_parent_id JTS_SETUP_FLOWS_B.parent_id%TYPE;
66 BEGIN
67
68 x_flow_id := get_next_flow_id;
69
70 l_parent_id := get_flow_id(p_parent_code);
71
72 insert into JTS_SETUP_FLOWS_B (
73 FLOW_ID,
74 FLOW_CODE,
75 FLOW_TYPE,
76 PARENT_ID,
77 HAS_CHILD_FLAG,
78 FLOW_SEQUENCE,
79 OVERVIEW_URL,
80 DIAGNOSTICS_URL,
81 DPF_CODE,
82 DPF_ASN,
83 NUM_STEPS,
84 OBJECT_VERSION_NUMBER,
85 CREATION_DATE,
86 CREATED_BY,
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN
90 ) values (
91 X_FLOW_ID,
92 p_flow_code,
93 P_FLOW_TYPE,
94 l_parent_id,
95 P_HAS_CHILD_FLAG,
96 P_FLOW_SEQUENCE,
97 P_OVERVIEW_URL,
98 P_DIAGNOSTICS_URL,
99 P_DPF_CODE,
100 P_DPF_ASN,
101 P_NUM_STEPS,
102 1,
103 sysdate,
104 p_created_by,
105 sysdate,
106 p_last_updated_by,
107 p_last_update_login
108 );
109
110 insert into JTS_SETUP_FLOWS_TL (
111 FLOW_ID,
112 FLOW_CODE,
113 FLOW_NAME,
114 CREATION_DATE,
115 CREATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_LOGIN,
119 LANGUAGE,
120 SOURCE_LANG
121 ) select
122 X_FLOW_ID,
123 p_flow_code,
124 P_FLOW_NAME,
125 sysdate,
126 p_created_by,
127 sysdate,
128 p_last_updated_by,
129 p_last_update_login,
130 L.LANGUAGE_CODE,
131 userenv('LANG')
132 from FND_LANGUAGES L
133 where L.INSTALLED_FLAG in ('I', 'B')
134 and not exists
135 (select NULL
136 from JTS_SETUP_FLOWS_TL T
137 where T.FLOW_ID = X_FLOW_ID
138 and T.LANGUAGE = L.LANGUAGE_CODE);
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 APP_EXCEPTION.RAISE_EXCEPTION;
143
144 END INSERT_ROW;
145
146 -------------------------------------------------
147 -- This is for seeding the Flow hiearchy.
148 -- Deletes a flow based on flow_code
149 -------------------------------------------------
150 PROCEDURE DELETE_ROW(p_flow_code IN VARCHAR2) IS
151 BEGIN
152 DELETE FROM jts_setup_flows_b
153 WHERE flow_code = p_flow_code;
154
155 DELETE FROM jts_setup_flows_tl
156 WHERE flow_code = p_flow_code;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 APP_EXCEPTION.RAISE_EXCEPTION;
161
162 END DELETE_ROW;
163
164 -------------------------------------------------
165 -- This is for seeding the Flow hiearchy.
166 -- Deletes a flow based on flow_id
167 -------------------------------------------------
168 PROCEDURE DELETE_ROW(p_flow_id IN NUMBER) IS
169 BEGIN
170 DELETE FROM jts_setup_flows_b
171 WHERE flow_id = p_flow_id;
172
173 DELETE FROM jts_setup_flows_tl
174 WHERE flow_id = p_flow_id;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 APP_EXCEPTION.RAISE_EXCEPTION;
179
180 END DELETE_ROW;
181
182 -------------------------------------------------
183 -- This is for seeding the Flow hiearchy.
184 -- Warning: Should be only used in exceptional cases
185 -- where updating is absolutely necessary
186 -- because of a setup error.
187 -- Users cannot call this procedure
188 -- Updates a flow
189 -------------------------------------------------
190 procedure UPDATE_ROW (
191 P_FLOW_CODE in VARCHAR2,
192 P_FLOW_TYPE in VARCHAR2,
193 P_PARENT_CODE in VARCHAR2,
194 P_HAS_CHILD_FLAG in VARCHAR2,
195 P_FLOW_SEQUENCE in NUMBER,
196 P_OVERVIEW_URL in VARCHAR2,
197 P_DIAGNOSTICS_URL in VARCHAR2,
198 P_DPF_CODE in VARCHAR2,
199 P_DPF_ASN in VARCHAR2,
200 P_NUM_STEPS in NUMBER,
201 P_FLOW_NAME in VARCHAR2,
202 P_LAST_UPDATE_DATE in DATE,
203 P_LAST_UPDATED_BY in NUMBER,
204 P_LAST_UPDATE_LOGIN in NUMBER
205 ) is
206 l_parent_id JTS_SETUP_FLOWS_B.parent_id%TYPE;
207 begin
208 l_parent_id := get_flow_id(p_parent_code);
209
210 update JTS_SETUP_FLOWS_B set
211 FLOW_TYPE = P_FLOW_TYPE,
212 PARENT_ID = L_PARENT_ID,
213 HAS_CHILD_FLAG = P_HAS_CHILD_FLAG,
214 FLOW_SEQUENCE = P_FLOW_SEQUENCE,
215 OVERVIEW_URL = P_OVERVIEW_URL,
216 DIAGNOSTICS_URL = P_DIAGNOSTICS_URL,
217 DPF_CODE = P_DPF_CODE,
218 DPF_ASN = P_DPF_ASN,
219 NUM_STEPS = P_NUM_STEPS,
220 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
223 where FLOW_CODE = P_FLOW_CODE;
224
225 if (sql%notfound) then
226 raise no_data_found;
227 end if;
228
229 update JTS_SETUP_FLOWS_TL set
230 FLOW_NAME = P_FLOW_NAME,
231 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
232 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
233 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
234 SOURCE_LANG = userenv('LANG')
235 where FLOW_CODE = P_FLOW_CODE
236 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
237
238 if (sql%notfound) then
239 raise no_data_found;
240 end if;
241 EXCEPTION
242 WHEN OTHERS THEN
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 end UPDATE_ROW;
245
246 -------------------------------------------------
247 -- This is for seeding the Flow hiearchy.
248 --
249 -- Translates the flow name
250 -------------------------------------------------
251 PROCEDURE TRANSLATE_ROW (
252 p_flow_code IN VARCHAR2,
253 p_owner IN VARCHAR2,
254 p_flow_name IN VARCHAR2
255 )
256 IS
257 BEGIN
258 update jts_setup_flows_tl set
259 flow_name = nvl(p_flow_name, flow_name),
260 source_lang = userenv('LANG'),
261 last_update_date = sysdate,
262 last_updated_by = decode(p_owner, 'SEED', 1, 0),
263 last_update_login = 0
264 where flow_code = p_flow_code
265 and userenv('LANG') in (language, source_lang);
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 APP_EXCEPTION.RAISE_EXCEPTION;
270 END TRANSLATE_ROW;
271
272 -------------------------------------------------
273 -- This is for seeding the Flow hiearchy.
274 --
275 -- Uploads a flow
276 -- If p_flow_id is not NULL and there is no flow with
277 -- such flow_id in the database, then a new flow_id will be used
278 -------------------------------------------------
279 PROCEDURE LOAD_ROW (
280 P_FLOW_CODE IN VARCHAR2,
281 P_OWNER IN VARCHAR2,
282 p_flow_type IN VARCHAR2,
283 p_parent_code IN VARCHAR2,
284 p_has_child_flag IN VARCHAR2,
285 p_flow_sequence IN NUMBER,
286 p_num_steps IN NUMBER,
287 P_OVERVIEW_URL in VARCHAR2,
288 P_DIAGNOSTICS_URL in VARCHAR2,
289 P_DPF_CODE in VARCHAR2,
290 P_DPF_ASN in VARCHAR2,
291 P_FLOW_NAME IN VARCHAR2
292 )
293 IS
294 l_user_id JTS_SETUP_FLOWS_B.created_by%TYPE := 0;
295 l_count number := 0;
296 l_flow_id JTS_SETUP_FLOWS_B.flow_id%TYPE;
297
298 BEGIN
299 if P_OWNER = 'SEED' then
300 l_user_id := 1;
301 end if;
302
303 select count(*)
304 into l_count
305 from jts_setup_flows_b
306 where flow_code = p_flow_code;
307
308
309 IF (l_count = 0) THEN --no flow with p_flow_code exists. Use p_flow_code to insert a new row
310 IF p_flow_code IS NOT NULL THEN --use a new flow_id
311 INSERT_ROW (
312 p_flow_code => p_flow_code,
313 p_flow_type => p_flow_type,
314 p_parent_code => p_parent_code,
315 p_has_child_flag => p_has_child_flag,
316 p_flow_sequence => p_flow_sequence,
317 p_overview_url => p_overview_url,
318 p_diagnostics_url => p_diagnostics_url,
319 p_dpf_code => p_dpf_code,
320 p_dpf_asn => p_dpf_asn,
321 p_num_steps => p_num_steps,
322 p_flow_name => p_flow_name,
323 p_created_by => l_user_id,
324 p_last_updated_by => l_user_id,
325 p_last_update_login => 1,
326 x_flow_id => l_flow_id
327 );
328 END IF;
329 ELSE --flow with p_flow_code exists, update
330
331 UPDATE_ROW (
332 P_FLOW_CODE => p_flow_code,
333 P_FLOW_TYPE => p_flow_type,
334 P_PARENT_CODE => p_parent_code,
335 P_HAS_CHILD_FLAG => p_has_child_flag,
336 P_FLOW_SEQUENCE => p_flow_sequence,
337 P_OVERVIEW_URL => p_overview_url,
338 P_DIAGNOSTICS_URL => p_diagnostics_url,
339 P_DPF_CODE => p_dpf_code,
340 P_DPF_ASN => p_dpf_asn,
341 P_NUM_STEPS => p_num_steps,
342 P_FLOW_NAME => p_flow_name,
343 P_LAST_UPDATE_DATE => sysdate,
344 P_LAST_UPDATED_BY => l_user_id,
345 P_LAST_UPDATE_LOGIN => 1
346 );
347 end if;
348 EXCEPTION
349 WHEN OTHERS THEN
350 APP_EXCEPTION.RAISE_EXCEPTION;
351 END LOAD_ROW;
352
353
354 procedure LOCK_ROW (
355 X_FLOW_ID in NUMBER,
356 X_FLOW_CODE in VARCHAR2,
357 X_FLOW_TYPE in VARCHAR2,
358 X_PARENT_ID in NUMBER,
359 X_HAS_CHILD_FLAG in VARCHAR2,
360 X_FLOW_SEQUENCE in NUMBER,
361 X_OVERVIEW_URL in VARCHAR2,
362 X_DIAGNOSTICS_URL in VARCHAR2,
363 X_DPF_CODE in VARCHAR2,
364 X_DPF_ASN in VARCHAR2,
365 X_NUM_STEPS in NUMBER,
366 X_SECURITY_GROUP_ID in NUMBER,
367 X_OBJECT_VERSION_NUMBER in NUMBER,
368 X_FLOW_NAME in VARCHAR2
369 ) is
370 cursor c is select
371 FLOW_TYPE,
372 PARENT_ID,
373 HAS_CHILD_FLAG,
374 FLOW_SEQUENCE,
375 OVERVIEW_URL,
376 DIAGNOSTICS_URL,
377 DPF_CODE,
378 DPF_ASN,
379 NUM_STEPS,
380 SECURITY_GROUP_ID,
381 OBJECT_VERSION_NUMBER
382 from JTS_SETUP_FLOWS_B
383 where FLOW_ID = X_FLOW_ID
384 and FLOW_CODE = X_FLOW_CODE
385 for update of FLOW_ID nowait;
386 recinfo c%rowtype;
387
388 cursor c1 is select
389 FLOW_NAME,
390 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
391 from JTS_SETUP_FLOWS_TL
392 where FLOW_ID = X_FLOW_ID
393 and FLOW_CODE = X_FLOW_CODE
394 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
395 for update of FLOW_ID nowait;
396 begin
397 open c;
398 fetch c into recinfo;
399 if (c%notfound) then
400 close c;
401 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
402 app_exception.raise_exception;
403 end if;
404 close c;
405 if ( ((recinfo.FLOW_TYPE = X_FLOW_TYPE)
406 OR ((recinfo.FLOW_TYPE is null) AND (X_FLOW_TYPE is null)))
407 AND ((recinfo.PARENT_ID = X_PARENT_ID)
408 OR ((recinfo.PARENT_ID is null) AND (X_PARENT_ID is null)))
409 AND ((recinfo.HAS_CHILD_FLAG = X_HAS_CHILD_FLAG)
410 OR ((recinfo.HAS_CHILD_FLAG is null) AND (X_HAS_CHILD_FLAG is null)))
411 AND (recinfo.FLOW_SEQUENCE = X_FLOW_SEQUENCE)
412 AND ((recinfo.OVERVIEW_URL = X_OVERVIEW_URL)
413 OR ((recinfo.OVERVIEW_URL is null) AND (X_OVERVIEW_URL is null)))
414 AND ((recinfo.DIAGNOSTICS_URL = X_DIAGNOSTICS_URL)
415 OR ((recinfo.DIAGNOSTICS_URL is null) AND (X_DIAGNOSTICS_URL is null)))
416 AND ((recinfo.DPF_CODE = X_DPF_CODE)
417 OR ((recinfo.DPF_CODE is null) AND (X_DPF_CODE is null)))
418 AND ((recinfo.DPF_ASN = X_DPF_ASN)
419 OR ((recinfo.DPF_ASN is null) AND (X_DPF_ASN is null)))
420 AND ((recinfo.NUM_STEPS = X_NUM_STEPS)
421 OR ((recinfo.NUM_STEPS is null) AND (X_NUM_STEPS is null)))
422 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
423 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
424 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
425 ) then
426 null;
427 else
428 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
429 app_exception.raise_exception;
430 end if;
431
432 for tlinfo in c1 loop
433 if (tlinfo.BASELANG = 'Y') then
434 if ( (tlinfo.FLOW_NAME = X_FLOW_NAME)
435 ) then
436 null;
437 else
438 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
439 app_exception.raise_exception;
440 end if;
441 end if;
442 end loop;
443 return;
444 end LOCK_ROW;
445
446
447 procedure ADD_LANGUAGE
448 is
449 begin
450 delete from JTS_SETUP_FLOWS_TL T
451 where not exists
452 (select NULL
453 from JTS_SETUP_FLOWS_B B
454 where B.FLOW_ID = T.FLOW_ID
455 and B.FLOW_CODE = T.FLOW_CODE
456 );
457
458 update JTS_SETUP_FLOWS_TL T set (
459 FLOW_NAME
460 ) = (select
461 B.FLOW_NAME
462 from JTS_SETUP_FLOWS_TL B
463 where B.FLOW_ID = T.FLOW_ID
464 and B.FLOW_CODE = T.FLOW_CODE
465 and B.LANGUAGE = T.SOURCE_LANG)
466 where (
467 T.FLOW_ID,
468 T.FLOW_CODE,
469 T.LANGUAGE
470 ) in (select
471 SUBT.FLOW_ID,
472 SUBT.FLOW_CODE,
473 SUBT.LANGUAGE
474 from JTS_SETUP_FLOWS_TL SUBB, JTS_SETUP_FLOWS_TL SUBT
475 where SUBB.FLOW_ID = SUBT.FLOW_ID
476 and SUBB.FLOW_CODE = SUBT.FLOW_CODE
477 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
478 and (SUBB.FLOW_NAME <> SUBT.FLOW_NAME
479 ));
480
481 insert into JTS_SETUP_FLOWS_TL (
482 FLOW_ID,
483 FLOW_CODE,
484 FLOW_NAME,
485 CREATION_DATE,
486 CREATED_BY,
487 LAST_UPDATE_DATE,
488 LAST_UPDATED_BY,
489 LAST_UPDATE_LOGIN,
490 SECURITY_GROUP_ID,
491 LANGUAGE,
492 SOURCE_LANG
493 ) select
494 B.FLOW_ID,
495 B.FLOW_CODE,
496 B.FLOW_NAME,
497 B.CREATION_DATE,
498 B.CREATED_BY,
499 B.LAST_UPDATE_DATE,
500 B.LAST_UPDATED_BY,
501 B.LAST_UPDATE_LOGIN,
502 B.SECURITY_GROUP_ID,
503 L.LANGUAGE_CODE,
504 B.SOURCE_LANG
505 from JTS_SETUP_FLOWS_TL B, FND_LANGUAGES L
506 where L.INSTALLED_FLAG in ('I', 'B')
507 and B.LANGUAGE = userenv('LANG')
508 and not exists
509 (select NULL
510 from JTS_SETUP_FLOWS_TL T
511 where T.FLOW_ID = B.FLOW_ID
512 and T.FLOW_CODE = B.FLOW_CODE
513 and T.LANGUAGE = L.LANGUAGE_CODE);
514 end ADD_LANGUAGE;
515
516 END JTS_SETUP_FLOW_HIEARCHY_PKG;
[Home] [Help]