[Home] [Help]
PACKAGE BODY: APPS.JTF_DPF_LOGICAL_FLOWS_PKG
Source
1 package body JTF_DPF_LOGICAL_FLOWS_PKG as
2 /* $Header: jtfdpffb.pls 120.2 2005/10/25 05:16:26 psanyal ship $ */
3 cursor find_match_with_owner(x_logical_flow_name varchar2,
4 x_application_id varchar2,
5 x_last_updated_by number) is
6 select logical_flow_id from jtf_dpf_logical_flows_b
7 where logical_flow_name=x_logical_flow_name and
8 application_id=x_application_id and
9 last_updated_by = x_last_updated_by
10 order by last_update_date;
11
12 -- same query, without the last_updated_by test
13 cursor find_match(x_logical_flow_name varchar2,
14 x_application_id varchar2) is
15 select logical_flow_id from jtf_dpf_logical_flows_b
16 where logical_flow_name=x_logical_flow_name and
17 application_id=x_application_id
18 order by last_update_date;
19
20 procedure INSERT_ROW (
21 X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
22 X_LOGICAL_FLOW_ID in NUMBER,
23 X_LOGICAL_FLOW_HEAD_ID in NUMBER,
24 X_LOGICAL_FLOW_NAME in VARCHAR2,
25 X_SECURE_FLOW_FLAG in VARCHAR2,
26 X_VALIDATE_FLAG in VARCHAR2,
27 X_APPLICATION_ID in NUMBER,
28 X_FLOW_FINALIZER_CLASS in VARCHAR2,
29 X_RETURN_TO_PAGE_ID in NUMBER,
30 X_BASE_FLOW_FLAG in VARCHAR2,
31 -- X_ENABLED_CLONE_FLAG in VARCHAR2,
32 X_OBJECT_VERSION_NUMBER in NUMBER,
33 X_LOGICAL_FLOW_DESCRIPTION in VARCHAR2,
34 X_CREATION_DATE in DATE,
35 X_CREATED_BY in NUMBER,
36 X_LAST_UPDATE_DATE in DATE,
37 X_LAST_UPDATED_BY in NUMBER,
38 X_LAST_UPDATE_LOGIN in NUMBER) is
39 l_flowid number;
40 l_enabled varchar2(1);
41 cursor C is select ROWID from JTF_DPF_LOGICAL_FLOWS_B
42 where LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID;
43 cursor any_others_with_same_name is
44 select logical_flow_id
45 from jtf_dpf_logical_flows_b
46 where logical_flow_name = x_logical_flow_name and
47 application_id = x_application_id;
48 begin
49 -- if there are any other flows of the same appid and name, then set
50 -- l_enabled to 'F', else set it to 'T'.
51 open any_others_with_same_name;
52 fetch any_others_with_same_name into l_flowid;
53 close any_others_with_same_name;
54
55 if l_flowid is null then
56 l_enabled := 'T';
57 else
58 l_enabled := 'F';
59 end if;
60
61 insert into JTF_DPF_LOGICAL_FLOWS_B (
62 LOGICAL_FLOW_ID,
63 LOGICAL_FLOW_HEAD_ID,
64 LOGICAL_FLOW_NAME,
65 SECURE_FLOW_FLAG,
66 VALIDATE_FLAG,
67 APPLICATION_ID,
68 FLOW_FINALIZER_CLASS,
69 RETURN_TO_PAGE_ID,
70 BASE_FLOW_FLAG,
71 ENABLED_CLONE_FLAG,
72 OBJECT_VERSION_NUMBER,
73 CREATION_DATE,
74 CREATED_BY,
75 LAST_UPDATE_DATE,
76 LAST_UPDATED_BY,
77 LAST_UPDATE_LOGIN
78 ) values (
79 X_LOGICAL_FLOW_ID,
80 X_LOGICAL_FLOW_HEAD_ID,
81 X_LOGICAL_FLOW_NAME,
82 X_SECURE_FLOW_FLAG,
83 X_VALIDATE_FLAG,
84 X_APPLICATION_ID,
85 X_FLOW_FINALIZER_CLASS,
86 X_RETURN_TO_PAGE_ID,
87 X_BASE_FLOW_FLAG,
88 l_enabled,
89 X_OBJECT_VERSION_NUMBER,
90 X_CREATION_DATE,
91 X_CREATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_LOGIN
95 );
96
97 insert into JTF_DPF_LOGICAL_FLOWS_TL (
98 LOGICAL_FLOW_ID,
99 LOGICAL_FLOW_DESCRIPTION,
100 CREATED_BY,
101 LAST_UPDATE_DATE,
102 LAST_UPDATED_BY,
103 LAST_UPDATE_LOGIN,
104 LANGUAGE,
105 SOURCE_LANG
106 ) select
107 X_LOGICAL_FLOW_ID,
108 X_LOGICAL_FLOW_DESCRIPTION,
109 X_CREATED_BY,
110 X_LAST_UPDATE_DATE,
111 X_LAST_UPDATED_BY,
112 X_LAST_UPDATE_LOGIN,
113 L.LANGUAGE_CODE,
114 userenv('LANG')
115 from FND_LANGUAGES L
116 where L.INSTALLED_FLAG in ('I', 'B')
117 and not exists
118 (select NULL
119 from JTF_DPF_LOGICAL_FLOWS_TL T
120 where T.LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID
121 and T.LANGUAGE = L.LANGUAGE_CODE);
122
123 open c;
124 fetch c into X_ROWID;
125 if (c%notfound) then
126 close c;
127 raise no_data_found;
128 end if;
129 close c;
130
131 end INSERT_ROW;
132
133 procedure LOCK_ROW (
134 X_LOGICAL_FLOW_ID in NUMBER,
135 X_LOGICAL_FLOW_HEAD_ID in NUMBER,
136 X_LOGICAL_FLOW_NAME in VARCHAR2,
137 X_SECURE_FLOW_FLAG in VARCHAR2,
138 X_VALIDATE_FLAG in VARCHAR2,
139 X_APPLICATION_ID in NUMBER,
140 X_FLOW_FINALIZER_CLASS in VARCHAR2,
141 X_RETURN_TO_PAGE_ID in NUMBER,
142 X_BASE_FLOW_FLAG in VARCHAR2,
143 X_ENABLED_CLONE_FLAG in VARCHAR2,
144 X_OBJECT_VERSION_NUMBER in NUMBER,
145 X_LOGICAL_FLOW_DESCRIPTION in VARCHAR2
146 ) is
147 cursor c is select
148 LOGICAL_FLOW_HEAD_ID,
149 LOGICAL_FLOW_NAME,
150 SECURE_FLOW_FLAG,
151 VALIDATE_FLAG,
152 APPLICATION_ID,
153 FLOW_FINALIZER_CLASS,
154 RETURN_TO_PAGE_ID,
155 BASE_FLOW_FLAG,
156 ENABLED_CLONE_FLAG,
157 OBJECT_VERSION_NUMBER
158 from JTF_DPF_LOGICAL_FLOWS_B
159 where LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID
160 for update of LOGICAL_FLOW_ID nowait;
161 recinfo c%rowtype;
162
163 cursor c1 is select
164 LOGICAL_FLOW_DESCRIPTION,
165 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
166 from JTF_DPF_LOGICAL_FLOWS_TL
167 where LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID
168 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
169 for update of LOGICAL_FLOW_ID nowait;
170 begin
171 open c;
172 fetch c into recinfo;
173 if (c%notfound) then
174 close c;
175 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
176 app_exception.raise_exception;
177 end if;
178 close c;
179 if ( (recinfo.LOGICAL_FLOW_HEAD_ID = X_LOGICAL_FLOW_HEAD_ID)
180 AND (recinfo.LOGICAL_FLOW_NAME = X_LOGICAL_FLOW_NAME)
181 AND (recinfo.SECURE_FLOW_FLAG = X_SECURE_FLOW_FLAG)
182 AND (recinfo.VALIDATE_FLAG = X_VALIDATE_FLAG)
183 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
184 AND ((recinfo.FLOW_FINALIZER_CLASS = X_FLOW_FINALIZER_CLASS)
185 OR ((recinfo.FLOW_FINALIZER_CLASS is null) AND (X_FLOW_FINALIZER_CLASS is null)))
186 AND ((recinfo.RETURN_TO_PAGE_ID = X_RETURN_TO_PAGE_ID)
187 OR ((recinfo.RETURN_TO_PAGE_ID is null) AND (X_RETURN_TO_PAGE_ID is null)))
188 AND (recinfo.BASE_FLOW_FLAG = X_BASE_FLOW_FLAG)
189 -- AND (recinfo.ENABLED_CLONE_FLAG = X_ENABLED_CLONE_FLAG)
190 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
191 ) then
192 null;
193 else
194 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
195 app_exception.raise_exception;
196 end if;
197
198 for tlinfo in c1 loop
199 if (tlinfo.BASELANG = 'Y') then
200 if ( ((tlinfo.LOGICAL_FLOW_DESCRIPTION = X_LOGICAL_FLOW_DESCRIPTION)
201 OR ((tlinfo.LOGICAL_FLOW_DESCRIPTION is null) AND (X_LOGICAL_FLOW_DESCRIPTION is null)))
202 ) then
203 null;
204 else
205 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
206 app_exception.raise_exception;
207 end if;
208 end if;
209 end loop;
210 return;
211 end LOCK_ROW;
212
213 procedure UPDATE_ROW (
214 X_LOGICAL_FLOW_ID in NUMBER,
215 X_LOGICAL_FLOW_HEAD_ID in NUMBER,
216 X_LOGICAL_FLOW_NAME in VARCHAR2,
217 X_SECURE_FLOW_FLAG in VARCHAR2,
218 X_VALIDATE_FLAG in VARCHAR2,
219 X_APPLICATION_ID in NUMBER,
220 X_FLOW_FINALIZER_CLASS in VARCHAR2,
221 X_RETURN_TO_PAGE_ID in NUMBER,
222 X_BASE_FLOW_FLAG in VARCHAR2,
223 -- X_ENABLED_CLONE_FLAG in VARCHAR2,
224 X_OBJECT_VERSION_NUMBER in NUMBER,
225 X_LOGICAL_FLOW_DESCRIPTION in VARCHAR2,
226 X_LAST_UPDATE_DATE in DATE,
227 X_LAST_UPDATED_BY in NUMBER,
228 X_LAST_UPDATE_LOGIN in NUMBER
229 ) is
230 begin
231 update JTF_DPF_LOGICAL_FLOWS_B set
232 LOGICAL_FLOW_HEAD_ID = X_LOGICAL_FLOW_HEAD_ID,
233 LOGICAL_FLOW_NAME = X_LOGICAL_FLOW_NAME,
234 SECURE_FLOW_FLAG = X_SECURE_FLOW_FLAG,
235 VALIDATE_FLAG = X_VALIDATE_FLAG,
236 APPLICATION_ID = X_APPLICATION_ID,
237 FLOW_FINALIZER_CLASS = X_FLOW_FINALIZER_CLASS,
238 RETURN_TO_PAGE_ID = X_RETURN_TO_PAGE_ID,
239 BASE_FLOW_FLAG = X_BASE_FLOW_FLAG,
240 -- ENABLED_CLONE_FLAG = X_ENABLED_CLONE_FLAG,
241 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
242 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
243 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
244 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
245 where LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID;
246
247 if (sql%notfound) then
248 raise no_data_found;
249 end if;
250
251 update JTF_DPF_LOGICAL_FLOWS_TL set
252 LOGICAL_FLOW_DESCRIPTION = X_LOGICAL_FLOW_DESCRIPTION,
253 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
254 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
255 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
256 SOURCE_LANG = userenv('LANG')
257 where LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID
258 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
259
260 if (sql%notfound) then
261 raise no_data_found;
262 end if;
263 end UPDATE_ROW;
264
265 procedure DELETE_ROW (
266 X_LOGICAL_FLOW_ID in NUMBER
267 ) is
268 begin
269 delete from JTF_DPF_LOGICAL_FLOWS_TL
270 where LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID;
271
272 if (sql%notfound) then
273 raise no_data_found;
274 end if;
275
276 delete from JTF_DPF_LOGICAL_FLOWS_B
277 where LOGICAL_FLOW_ID = X_LOGICAL_FLOW_ID;
278
279 if (sql%notfound) then
280 raise no_data_found;
281 end if;
282 end DELETE_ROW;
283
284 procedure ADD_LANGUAGE
285 is
286 begin
287 delete from JTF_DPF_LOGICAL_FLOWS_TL T
288 where not exists
289 (select NULL
290 from JTF_DPF_LOGICAL_FLOWS_B B
291 where B.LOGICAL_FLOW_ID = T.LOGICAL_FLOW_ID
292 );
293
294 update JTF_DPF_LOGICAL_FLOWS_TL T set (
295 LOGICAL_FLOW_DESCRIPTION
296 ) = (select
297 B.LOGICAL_FLOW_DESCRIPTION
298 from JTF_DPF_LOGICAL_FLOWS_TL B
299 where B.LOGICAL_FLOW_ID = T.LOGICAL_FLOW_ID
300 and B.LANGUAGE = T.SOURCE_LANG)
301 where (
302 T.LOGICAL_FLOW_ID,
303 T.LANGUAGE
304 ) in (select
305 SUBT.LOGICAL_FLOW_ID,
306 SUBT.LANGUAGE
307 from JTF_DPF_LOGICAL_FLOWS_TL SUBB, JTF_DPF_LOGICAL_FLOWS_TL SUBT
308 where SUBB.LOGICAL_FLOW_ID = SUBT.LOGICAL_FLOW_ID
309 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
310 and (SUBB.LOGICAL_FLOW_DESCRIPTION <> SUBT.LOGICAL_FLOW_DESCRIPTION
311 or (SUBB.LOGICAL_FLOW_DESCRIPTION is null and SUBT.LOGICAL_FLOW_DESCRIPTION is not null)
312 or (SUBB.LOGICAL_FLOW_DESCRIPTION is not null and SUBT.LOGICAL_FLOW_DESCRIPTION is null)
313 ));
314
315 insert into JTF_DPF_LOGICAL_FLOWS_TL (
316 LOGICAL_FLOW_ID,
317 LOGICAL_FLOW_DESCRIPTION,
318 CREATED_BY,
319 LAST_UPDATE_DATE,
320 LAST_UPDATED_BY,
321 LAST_UPDATE_LOGIN,
322 LANGUAGE,
323 SOURCE_LANG
324 ) select
325 B.LOGICAL_FLOW_ID,
326 B.LOGICAL_FLOW_DESCRIPTION,
327 B.CREATED_BY,
328 B.LAST_UPDATE_DATE,
329 B.LAST_UPDATED_BY,
330 B.LAST_UPDATE_LOGIN,
331 L.LANGUAGE_CODE,
332 B.SOURCE_LANG
333 from JTF_DPF_LOGICAL_FLOWS_TL B, FND_LANGUAGES L
334 where L.INSTALLED_FLAG in ('I', 'B')
335 and B.LANGUAGE = userenv('LANG')
336 and not exists
337 (select NULL
338 from JTF_DPF_LOGICAL_FLOWS_TL T
339 where T.LOGICAL_FLOW_ID = B.LOGICAL_FLOW_ID
340 and T.LANGUAGE = L.LANGUAGE_CODE);
341 end ADD_LANGUAGE;
342
343 function find_oldest_prefer_owned_by(x_logical_flow_name varchar2,
344 x_application_id varchar2, x_last_updated_by number) return number is
345 l_candidate number;
346 begin
347 -- if there's one in seed data (i.e. with l_updated_by = 1)
348 -- then return it
349 open find_match_with_owner(x_logical_flow_name, x_application_id,
350 x_last_updated_by);
351 fetch find_match_with_owner into l_candidate;
352 close find_match_with_owner;
353 if l_candidate is not null then return l_candidate; end if;
354
355 -- if there's any at all (seed data or not), then return it
356 open find_match(x_logical_flow_name, x_application_id);
357 fetch find_match into l_candidate;
358 close find_match;
359
360 return l_candidate;
361 end;
362
363 procedure insert_flow_params(
364 x_flow_id number,
365 x_parameter_name varchar2,
366 x_parameter_type varchar2,
367 x_parameter_sequence varchar2,
368 x_owner varchar2) is
369 l_user_id number;
370 begin
371 l_user_id := 0;
372 if x_owner = 'SEED' then l_user_id := 1; end if;
373
374 insert into jtf_dpf_lgcl_flow_params(
375 LOGICAL_FLOW_ID,
376 PARAMETER_NAME,
377 PARAMETER_TYPE,
378 PARAMETER_SEQUENCE,
379 OBJECT_VERSION_NUMBER,
380 CREATED_BY,
381 CREATION_DATE,
382 LAST_UPDATE_DATE,
383 LAST_UPDATED_BY,
384 LAST_UPDATE_LOGIN)
385 values (
386 x_flow_id,
387 x_parameter_name,
388 x_parameter_type,
389 x_parameter_sequence,
390 1,
391 l_user_id,
392 sysdate,
393 sysdate,
394 l_user_id,
395 0);
396 end;
397
398 procedure update_flow_params(
399 x_flow_id number,
400 x_parameter_name varchar2,
401 x_parameter_type varchar2,
402 x_parameter_sequence varchar2,
403 x_owner varchar2) is
404 l_user_id number;
405 begin
406 l_user_id := 0;
407 if x_owner = 'SEED' then l_user_id := 1; end if;
408 update jtf_dpf_lgcl_flow_params set
409 PARAMETER_TYPE = x_parameter_type,
410 PARAMETER_NAME = x_parameter_name,
411 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
412 LAST_UPDATE_DATE = sysdate,
413 LAST_UPDATED_BY = l_user_id,
414 LAST_UPDATE_LOGIN = 0
415 where logical_flow_id = x_flow_id and
416 parameter_sequence=x_parameter_sequence;
417 end;
418
419 procedure translate_row(
420 x_flow_name varchar2,
421 x_application_id varchar2,
422 x_flow_description varchar2,
423 x_owner varchar2
424 ) is
425 l_flow_id number;
426 l_user_id number;
427 begin
428 l_user_id := 0;
429 if x_owner = 'SEED' then l_user_id := 1; end if;
430
431 l_flow_id := find_oldest_prefer_owned_by(x_flow_name, x_application_id,
432 l_user_id);
433 update jtf_dpf_logical_flows_tl set
434 logical_flow_description = x_flow_description,
435 last_updated_by = l_user_id,
436 last_update_date = sysdate,
437 last_update_login = 0
438 where userenv('LANG') in (LANGUAGE, SOURCE_LANG) and
439 logical_flow_id = l_flow_id;
440 end;
441
442 procedure load_row(
443 X_APPLICATION_ID VARCHAR2,
444 X_LOGICAL_FLOW_NAME VARCHAR2,
445 X_HEAD_LOGICAL_PAGE_NAME VARCHAR2,
446 X_HEAD_LOGICAL_PAGE_APP_ID VARCHAR2,
447 X_SECURE_FLOW_FLAG VARCHAR2,
448 X_VALIDATE_FLAG VARCHAR2,
449 X_FLOW_FINALIZER_CLASS VARCHAR2,
450 X_RTN_TO_LOGICAL_PAGE_NAME VARCHAR2,
451 X_RTN_TO_LOGICAL_PAGE_APP_ID VARCHAR2,
452 X_BASE_FLOW_FLAG VARCHAR2,
453 -- X_ENABLED_CLONE_FLAG VARCHAR2,
454 X_LOGICAL_FLOW_DESCRIPTION VARCHAR2,
455 X_OWNER in VARCHAR2) is
456 t_user number;
457 t_rowid rowid;
458 t_header_id number;
459 t_return_to_id number;
460 l_new_flow_id number;
461 l_flow_id number;
462 l_counter number;
463 begin
464 t_user := 0;
465 if x_owner = 'SEED' then t_user := 1; end if;
466
467 t_header_id := jtf_dpf_logical_pages_pkg.find(
468 X_HEAD_LOGICAL_PAGE_NAME, X_HEAD_LOGICAL_PAGE_APP_ID);
469 t_return_to_id := jtf_dpf_logical_pages_pkg.find(
470 X_RTN_TO_LOGICAL_PAGE_NAME,
471 X_RTN_TO_LOGICAL_PAGE_APP_ID);
472
473 -- see whether there's already a Flow owned by t_user
474 open find_match_with_owner(x_logical_flow_name, x_application_id, t_user);
475 fetch find_match_with_owner into l_flow_id;
476 close find_match_with_owner;
477
478 if l_flow_id is null then
479 -- cons up a new flow_id, smaller than 10000
480 l_new_flow_id := null;
481 -- arsingh: prevent use of same id by different threads.
482 select JTF_DPF_LOGICAL_FLOWS_S.nextval into l_new_flow_id from dual;
483 -- select max(logical_flow_id) into l_new_flow_id from
484 -- jtf_dpf_logical_flows_b where logical_flow_id<10000;
485 -- if l_new_flow_id is null then
486 -- l_new_flow_id := 1;
487 -- else
488 -- l_new_flow_id := l_new_flow_id+1;
489 -- end if;
490
491 -- do an insert
492 insert_row(
493 X_ROWID => t_rowid,
494 X_LOGICAL_FLOW_ID => l_new_flow_id,
495 X_LOGICAL_FLOW_HEAD_ID => t_header_id,
496 X_LOGICAL_FLOW_NAME => X_LOGICAL_FLOW_NAME,
497 X_SECURE_FLOW_FLAG => X_SECURE_FLOW_FLAG,
498 X_VALIDATE_FLAG => X_VALIDATE_FLAG,
499 X_APPLICATION_ID => X_APPLICATION_ID,
500 X_FLOW_FINALIZER_CLASS => X_FLOW_FINALIZER_CLASS,
501 X_RETURN_TO_PAGE_ID => t_return_to_id,
502 X_BASE_FLOW_FLAG => X_BASE_FLOW_FLAG,
503 -- X_ENABLED_CLONE_FLAG => X_ENABLED_CLONE_FLAG,
504 X_OBJECT_VERSION_NUMBER => 1,
505 X_LOGICAL_FLOW_DESCRIPTION => X_LOGICAL_FLOW_DESCRIPTION,
506 X_CREATION_DATE => sysdate,
507 X_CREATED_BY => t_user,
508 X_LAST_UPDATE_DATE => sysdate,
509 X_LAST_UPDATED_BY => t_user,
510 X_LAST_UPDATE_LOGIN => 0);
511
512 -- if there's exactly one row with a flow wit this appid/flowname,
513 -- then make sure that row has enabled_clone_flag = 'T'
514 select count(*) into l_counter from jtf_dpf_logical_flows_b
515 where application_id = x_application_id and
516 logical_flow_name = x_logical_flow_name;
517 if l_counter = 1 then
518 update jtf_dpf_logical_flows_b set
519 enabled_clone_flag = 'T'
520 where application_id = x_application_id and
521 logical_flow_name = x_logical_flow_name;
522 end if;
523 else
524 update_row(
525 X_LOGICAL_FLOW_ID => l_flow_id,
526 X_LOGICAL_FLOW_HEAD_ID => t_header_id,
527 X_LOGICAL_FLOW_NAME => X_LOGICAL_FLOW_NAME,
528 X_SECURE_FLOW_FLAG => X_SECURE_FLOW_FLAG,
529 X_VALIDATE_FLAG => X_VALIDATE_FLAG,
530 X_APPLICATION_ID => X_APPLICATION_ID,
531 X_FLOW_FINALIZER_CLASS => X_FLOW_FINALIZER_CLASS,
532 X_RETURN_TO_PAGE_ID => t_return_to_id,
533 X_BASE_FLOW_FLAG => X_BASE_FLOW_FLAG,
534 -- X_ENABLED_CLONE_FLAG => X_ENABLED_CLONE_FLAG,
535 X_OBJECT_VERSION_NUMBER => 1,
536 X_LOGICAL_FLOW_DESCRIPTION => X_LOGICAL_FLOW_DESCRIPTION,
537 X_LAST_UPDATE_DATE => sysdate,
538 X_LAST_UPDATED_BY => t_user,
539 X_LAST_UPDATE_LOGIN => 0);
540 end if;
541 end;
542
543 procedure ins_upd_or_ign_lgcl_next_rules(
544 x_rule_eval_seq varchar2,
545 x_default_next_flag varchar2,
546 x_logical_flow_application_id varchar2,
547 x_logical_flow_name varchar2,
548 x_logical_page_application_id varchar2,
549 x_logical_page_name varchar2,
550 x_logical_next_page_app_id varchar2,
551 x_logical_next_page_name varchar2,
552 x_rule_application_id varchar2,
553 x_rule_name varchar2,
554 x_owner varchar2,
555 x_force_update_flag varchar2
556 ) is
557 l_user_id number := 0;
558 l_rule_id number;
559 l_logical_id number;
560 l_next_logical_id number;
561 l_flow_id number;
562 l_lnrid number;
563 l_last_updated_by number;
564 cursor another_default(x_flow_id number, x_logical_id number) is
565 select logical_next_rule_id
566 from jtf_dpf_lgcl_next_rules
567 where logical_flow_id = x_flow_id and
568 logical_page_id = x_logical_id and
569 default_next_flag='T';
570 cursor another_non_default(x_flow_id number, x_logical_id number,
571 x_seq number) is
572 select logical_next_rule_id
573 from jtf_dpf_lgcl_next_rules
574 where logical_flow_id = x_flow_id and
575 logical_page_id = x_logical_id and
576 default_next_flag='F' and
577 rule_eval_seq = x_seq;
578 begin
579 if x_owner = 'SEED' then l_user_id := 1; end if;
580
581 -- get ids for: the flow, the starting logical, the next logical,
582 -- and the rule_id
583 l_logical_id := jtf_dpf_logical_pages_pkg.find(
584 x_logical_page_name, x_logical_page_application_id);
585 l_next_logical_id := jtf_dpf_logical_pages_pkg.find(
586 x_logical_next_page_name, x_logical_next_page_app_id);
587 l_rule_id := jtf_dpf_rules_pkg.find(
588 x_rule_name, x_rule_application_id);
589 l_flow_id := jtf_dpf_logical_flows_pkg.find_oldest_prefer_owned_by(
590 x_logical_flow_name, x_logical_flow_application_id, l_user_id);
591
592 -- if we weren't called with force_update_flag='TRUE', and if
593 -- the flow in question is not owned by us, then just return
594 -- without doing anything
595 if x_force_update_flag is null and x_force_update_flag <> 'TRUE' then
596 select last_updated_by into l_last_updated_by
597 from jtf_dpf_logical_flows_b where logical_flow_id = l_flow_id;
598 if l_last_updated_by <> l_user_id then return; end if;
599 end if;
600
601 -- try to find a row which matches this one (to see whether we should
602 -- do an UPDATE rather than an INSERT). If there's such a row, then
603 -- l_lnrid will be the LOGICAL_NEXT_RULE_ID from table
604 -- JTF_DPF_LGCL_NEXT_RULES.
605 if 'T' = x_default_next_flag then
606 -- if there's already a default...
607 open another_default(l_flow_id, l_logical_id);
608 fetch another_default into l_lnrid;
609 close another_default;
610 else
611 -- if there's already a non-default...
612 open another_non_default(l_flow_id, l_logical_id, x_rule_eval_seq);
613 fetch another_non_default into l_lnrid;
614 close another_non_default;
615 end if;
616
617 if l_lnrid is not null then
618 update jtf_dpf_lgcl_next_rules set
619 LOGICAL_PAGE_ID = l_logical_id,
620 LOGICAL_NEXT_PAGE_ID = l_next_logical_id,
621 DEFAULT_NEXT_FLAG = x_default_next_flag,
622 RULE_EVAL_SEQ = x_rule_eval_seq,
623 LOGICAL_FLOW_ID = l_flow_id,
624 RULE_ID = l_rule_id,
625 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
626 LAST_UPDATE_DATE = sysdate,
627 LAST_UPDATED_BY = l_user_id,
628 LAST_UPDATE_LOGIN = 0
629 where logical_next_rule_id = l_lnrid;
630 else
631 -- arsingh: prevent use of same id by different threads.
632 select JTF_DPF_LGCL_NXT_RULES_S.nextval into l_lnrid from dual;
633 -- select max(logical_next_rule_id) into l_lnrid
634 -- from jtf_dpf_lgcl_next_rules where
635 -- logical_next_rule_id < 10000;
636 -- if l_lnrid is null then
637 -- l_lnrid := 1;
638 -- else
639 -- l_lnrid := l_lnrid + 1;
640 -- end if;
641
642 insert into jtf_dpf_lgcl_next_rules (
643 LOGICAL_NEXT_RULE_ID,
644 LOGICAL_PAGE_ID,
645 LOGICAL_NEXT_PAGE_ID,
646 DEFAULT_NEXT_FLAG,
647 RULE_EVAL_SEQ,
648 LOGICAL_FLOW_ID,
649 RULE_ID,
650 OBJECT_VERSION_NUMBER,
651 CREATED_BY,
652 CREATION_DATE,
653 LAST_UPDATE_DATE,
654 LAST_UPDATED_BY,
655 LAST_UPDATE_LOGIN)
656 values (
657 l_lnrid,
658 l_logical_id,
659 l_next_logical_id,
660 x_default_next_flag,
661 x_rule_eval_seq,
662 l_flow_id,
663 l_rule_id,
664 1,
665 l_user_id,
666 sysdate,
667 sysdate,
668 l_user_id,
669 0);
670 end if;
671 end;
672 end JTF_DPF_LOGICAL_FLOWS_PKG;