[Home] [Help]
PACKAGE BODY: APPS.JTF_DPF
Source
1 package body jtf_dpf as
2 /* $Header: jtfdpfb.pls 120.1 2005/07/02 00:40:14 appldev ship $ */
3 procedure get_dpf_tbl(p_lang varchar2, asn varchar2,
4 dpf out NOCOPY dpf_tbl) is
5 ddindx binary_integer := 1;
6 t_head_log_asn fnd_application.application_short_name%type;
7 t_head_log_name jtf_dpf_logical_pages_b.logical_page_name%type;
8 t_rtn_log_asn fnd_application.application_short_name%type;
9 t_rtn_log_name jtf_dpf_logical_pages_b.logical_page_name%type;
10 t_log_descr jtf_dpf_logical_flows_tl.logical_flow_description%type;
11
12 cursor c1 (pp_asn varchar2) is
13 select
14 lf.logical_flow_id,
15 lf.logical_flow_name,
16 lf.logical_flow_head_id,
17 lf.return_to_page_id,
18 lf.flow_finalizer_class,
19 -- lf.logical_flow_description,
20 lf.validate_flag,
21 lf.secure_flow_flag,
22 lf.enabled_clone_flag,
23 lf.last_updated_by -- was: base_flow_flag
24 from jtf_dpf_logical_flows_b lf
25 where lf.application_id =
26 (select application_id from fnd_application fa2
27 where fa2.application_short_name = pp_asn);
28 begin
29 for c1_rec in c1(asn) loop
30 select logical_flow_description into t_log_descr
31 from jtf_dpf_logical_flows_tl lftl
32 where lftl.logical_flow_id = c1_rec.logical_flow_id
33 and lftl.language = p_lang;
34
35 select application_short_name
36 into t_head_log_asn
37 from fnd_application fa where fa.application_id =
38 (select lp.application_id from jtf_dpf_logical_pages_b lp
39 where lp.logical_page_id = c1_rec.logical_flow_head_id);
40
41 select lp2.logical_page_name
42 into t_head_log_name
43 from jtf_dpf_logical_pages_b lp2
44 where lp2.logical_page_id = c1_rec.logical_flow_head_id;
45
46 select application_short_name
47 into t_rtn_log_asn
48 from fnd_application fa where fa.application_id =
49 (select lp.application_id from jtf_dpf_logical_pages_b lp
50 where lp.logical_page_id = c1_rec.return_to_page_id);
51
52 select lp2.logical_page_name
53 into t_rtn_log_name
54 from jtf_dpf_logical_pages_b lp2
55 where lp2.logical_page_id = c1_rec.return_to_page_id;
56
57 -- create the new record at index ddindx
58 dpf(ddindx).dpf_id := c1_rec.logical_flow_id;
59 dpf(ddindx).dpf_name := c1_rec.logical_flow_name;
60 dpf(ddindx).head_logical_asn := t_head_log_asn;
61 dpf(ddindx).head_logical_name := t_head_log_name;
62 dpf(ddindx).rtn_to_page_logical_asn := t_rtn_log_asn;
63 dpf(ddindx).rtn_to_page_logical_name := t_rtn_log_name;
64 dpf(ddindx).flow_finalizer_class := c1_rec.flow_finalizer_class;
65 dpf(ddindx).logical_flow_description := t_log_descr;
66 dpf(ddindx).validate_flag := c1_rec.validate_flag;
67 dpf(ddindx).secure_flow_flag := c1_rec.secure_flow_flag;
68 dpf(ddindx).active_flag := c1_rec.enabled_clone_flag;
69
70 -- if c1_rec.base_flow_flag = 'T'
71 if c1_rec.last_updated_by = 1
72 then dpf(ddindx).editable_flag := 'F';
73 else dpf(ddindx).editable_flag := 'T';
74 end if;
75
76 -- increment ddindx
77 ddindx := ddindx + 1;
78 end loop;
79 end;
80 procedure get_logical_tbl(p_lang varchar2, asn varchar2,
81 log out NOCOPY logical_tbl) is
82 cursor c1 (pp_asn varchar2) is
83 select
84 lp.logical_page_name,
85 lp.logical_page_type,
86 -- lp.logical_page_description,
87 lp.page_controller_class,
88 lp.page_permission_name,
89 lp.logical_page_id
90 from jtf_dpf_logical_pages_b lp
91 where lp.application_id =
92 (select application_id from fnd_application fa2
93 where fa2.application_short_name = pp_asn);
94 t_asn fnd_application.application_short_name%type;
95 t_def_phys_id jtf_dpf_lgcl_phy_rules.physical_page_id%type;
96 t_phys_page_name jtf_dpf_physical_pages_b.physical_page_name%type;
97 ddindx binary_integer := 1;
98 t_log_page_descr jtf_dpf_logical_pages_tl.logical_page_description%type;
99 begin
100 for c1_rec in c1(asn) loop
101 select application_short_name
102 into t_asn
103 from fnd_application fa
104 where fa.application_id =
105 (select pp.application_id
106 from jtf_dpf_physical_pages_b pp
107 where pp.physical_page_id =
108 (select lpr.physical_page_id from jtf_dpf_lgcl_phy_rules lpr
109 where lpr.logical_page_id = c1_rec.logical_page_id and
110 lpr.default_page_flag='T'));
111
112 select logical_page_description into t_log_page_descr
113 from jtf_dpf_logical_pages_tl o
114 where o.logical_page_id = c1_rec.logical_page_id and
115 o.language = p_lang;
116
117 select lpr.physical_page_id
118 into t_def_phys_id
119 from jtf_dpf_lgcl_phy_rules lpr
120 where lpr.logical_page_id = c1_rec.logical_page_id and
121 lpr.default_page_flag = 'T';
122
123 select pp.physical_page_name
124 into t_phys_page_name
125 from jtf_dpf_physical_pages_b pp
126 where pp.physical_page_id =
127 (select lpr.physical_page_id from jtf_dpf_lgcl_phy_rules lpr
128 where lpr.logical_page_id = c1_rec.logical_page_id and
129 lpr.default_page_flag = 'T');
130
131 -- create the new record
132 log(ddindx).logical_page_id := c1_rec.logical_page_id;
133 log(ddindx).logical_page_name := c1_rec.logical_page_name;
134 log(ddindx).logical_page_type := c1_rec.logical_page_type;
135 log(ddindx).logical_page_description := t_log_page_descr;
136 -- log(ddindx).secure_page_flag := c1_rec.secure_page_flag;
137 log(ddindx).page_controller_class := c1_rec.page_controller_class;
138 log(ddindx).page_permission_name := c1_rec.page_permission_name;
139 log(ddindx).def_phys_asn := t_asn;
140 log(ddindx).def_phys_id := t_def_phys_id;
141 log(ddindx).def_phys_name := t_phys_page_name;
142
143 -- increment ddindx
144 ddindx := ddindx+1;
145 end loop;
146 null;
147 end;
148 procedure get_physical_tbl(p_lang varchar2, asn varchar2,
149 log out NOCOPY physical_tbl) is
150 cursor c1 (pp_asn varchar2) is
151 select
152 pp.physical_page_id,
153 pp.physical_page_name
154 -- pp.physical_page_description
155 from jtf_dpf_physical_pages_b pp
156 where pp.application_id =
157 (select fa.application_id from fnd_application fa
158 where fa.application_short_name = pp_asn);
159 ddindx binary_integer := 1;
160 t_phys_page_descr jtf_dpf_physical_pages_tl.physical_page_description%type;
161 begin
162 for c1_rec in c1(asn) loop
163 select physical_page_description into t_phys_page_descr
164 from jtf_dpf_physical_pages_tl ptl
165 where ptl.physical_page_id = c1_rec.physical_page_id and
166 ptl.language = p_lang;
167 -- create the new record
168 log(ddindx).id := c1_rec.physical_page_id;
169 log(ddindx).name := c1_rec.physical_page_name;
170 log(ddindx).descr := t_phys_page_descr;
171
172 -- increment the index
173 ddindx := ddindx + 1;
174 end loop;
175 end;
176 procedure get_physical_non_default_tbl(asn varchar2,
177 retval out NOCOPY physical_non_default_tbl) is
178 ddindx binary_integer := 1;
179 q2_c1 jtf_dpf_physical_pages_b.physical_page_name%type;
180 q2_c2 fnd_application.application_short_name%type;
181 q3_c1 jtf_dpf_rules_b.rule_name%type;
182 q3_c2 fnd_application.application_short_name%type;
183 cursor c1(pp_asn varchar2) is
184 select unique
185 lp.logical_page_name,
186 lpr.rule_eval_sequence,
187 lpr.physical_page_id,
188 lpr.rule_id
189 from jtf_dpf_logical_pages_b lp, jtf_dpf_lgcl_phy_rules lpr
190 where
191 lp.logical_page_id = lpr.logical_page_id and
192 lp.application_id =
193 (select application_id from fnd_application fa where
194 fa.application_short_name = pp_asn) and
195 lpr.default_page_flag = 'F'
196 order by lp.logical_page_name, lpr.rule_eval_sequence;
197 q2_c2_temp fnd_application.application_id%type;
198 q3_c2_temp fnd_application.application_id%type;
199 begin
200 for c1_rec in c1(asn) loop
201 select pp.physical_page_name,
202 pp.application_id
203 -- (select application_short_name from fnd_application fa2
204 -- where fa2.application_id = pp.application_id)
205 into q2_c1, q2_c2_temp
206 from jtf_dpf_physical_pages_b pp
207 where pp.physical_page_id = c1_rec.physical_page_id;
208
209 select application_short_name
210 into q2_c2
211 from fnd_application fa2 where fa2.application_id = q2_c2_temp;
212
213 select
214 r.rule_name,
215 r.application_id
216 -- (select application_short_name from fnd_application fa
217 -- where fa.application_id = r.application_id)
218 into q3_c1, q3_c2_temp
219 from jtf_dpf_rules_b r
220 where r.rule_id = c1_rec.rule_id;
221
222 select application_short_name into q3_c2 from fnd_application
223 where fnd_application.application_id = q3_c2_temp;
224
225 -- write contents of the record in retval(ddindx)
226 retval(ddindx).logical_name := c1_rec.logical_page_name;
227 retval(ddindx).rule_eval_sequence := c1_rec.rule_eval_sequence;
228 retval(ddindx).rule_asn := q3_c2;
229 retval(ddindx).rule_name := q3_c1;
230 retval(ddindx).phys_asn := q2_c2;
231 retval(ddindx).phys_id := c1_rec.physical_page_id;
232 retval(ddindx).phys_name := q2_c1;
233
234 -- increment ddindx
235 ddindx := ddindx+1;
236 end loop;
237 end;
238 procedure get_rule_tbl(p_lang varchar2, asn varchar2, retval out NOCOPY rule_tbl) is
239 cursor c1(pp_asn varchar2) is
240 select
241 r.rule_id,
242 r.rule_name,
243 -- r.rule_description,
244 rp.rule_param_name,
245 rp.rule_param_value,
246 rp.rule_param_condition
247 from jtf_dpf_rules_b r, jtf_dpf_rule_params rp
248 where
249 r.rule_id = rp.rule_id and
250 r.application_id =
251 (select application_id from fnd_application fa
252 where fa.application_short_name = pp_asn)
253 order by rp.rule_param_sequence;
254 ddindx binary_integer := 1;
255 t_rules_descr jtf_dpf_rules_tl.rule_description%type;
256 begin
257 for c1_rec in c1(asn) loop
258 -- create a new record
259 select rule_description into t_rules_descr
260 from jtf_dpf_rules_tl jrtl
261 where jrtl.rule_id = c1_rec.rule_id and
262 jrtl.language = p_lang;
263
264 retval(ddindx).rule_id := c1_rec.rule_id;
265 retval(ddindx).rule_name := c1_rec.rule_name;
266 retval(ddindx).rule_description := t_rules_descr;
267 retval(ddindx).rule_param_name := c1_rec.rule_param_name;
268 retval(ddindx).rule_param_value := c1_rec.rule_param_value;
269 retval(ddindx).rule_param_condition := c1_rec.rule_param_condition;
270
271 -- increment ddindx
272 ddindx := ddindx + 1;
273 end loop;
274 end;
275 procedure get_next_logical_default_tbl(asn varchar2,
276 retval out NOCOPY next_logical_default_tbl) is
277 cursor q1 (pp_asn varchar2) is
278 select logical_flow_name, logical_flow_id
279 from jtf_dpf_logical_flows_b lf
280 where lf.application_id =
281 (select application_id from fnd_application fa
282 where fa.application_short_name = pp_asn);
283
284 cursor q2 (pp_q1_c2 number) is
285 select logical_page_id, logical_next_page_id
286 from jtf_dpf_lgcl_next_rules lnr
287 where lnr.logical_flow_id = pp_q1_c2 and
288 lnr.default_next_flag='T';
289 ddindx binary_integer := 1;
290 q3_c1 fnd_application.application_short_name%type;
291 q3_c2 jtf_dpf_logical_pages_b.logical_page_name%type;
292 q4_c1 fnd_application.application_short_name%type;
293 q4_c2 jtf_dpf_logical_pages_b.logical_page_name%type;
294 q3_c1_temp fnd_application.application_id%type;
295 q4_c1_temp fnd_application.application_id%type;
296 begin
297 for q1_rec in q1(asn) loop
298 for q2_rec in q2(q1_rec.logical_flow_id) loop
299 select
300 lp.application_id,
301 -- (select application_short_name from fnd_application fa
302 -- where fa.application_id = lp.application_id),
303 lp.logical_page_name
304 into q3_c1_temp, q3_c2
305 from jtf_dpf_logical_pages_b lp
306 where lp.logical_page_id = q2_rec.logical_page_id;
307
308 select application_short_name into q3_c1
309 from fnd_application where application_id = q3_c1_temp;
310
311 select
312 lp.application_id,
313 -- (select application_short_name from fnd_application fa
314 -- where fa.application_id = lp.application_id),
315 lp.logical_page_name
316 into q4_c1_temp, q4_c2
317 from jtf_dpf_logical_pages_b lp
318 where lp.logical_page_id = q2_rec.logical_next_page_id;
319
320 select application_short_name into q4_c1
321 from fnd_application where application_id = q4_c1_temp;
322
323 -- write the data into a new record
324 retval(ddindx).dpf_name := q1_rec.logical_flow_name;
325 retval(ddindx).dpf_id := q1_rec.logical_flow_id;
326 retval(ddindx).key_log_asn := q3_c1;
327 retval(ddindx).key_log_name := q3_c2;
328 retval(ddindx).result_log_asn := q4_c1;
329 retval(ddindx).result_log_name := q4_c2;
330
331 -- increment the record index
332 ddindx := ddindx+1;
333 end loop;
334 end loop;
335 end;
336 procedure get_next_logical_non_def_tbl(asn varchar2,
337 retval out NOCOPY next_logical_non_default_tbl) is
338 cursor q1 (pp_asn varchar2) is
339 select logical_flow_name, logical_flow_id
340 from jtf_dpf_logical_flows_b lf
341 where lf.application_id =
342 (select application_id from fnd_application fa
343 where fa.application_short_name = pp_asn);
344
345 cursor q2 (pp_q1_c2 number) is
346 select
347 logical_page_id,
348 logical_next_page_id,
349 rule_eval_seq,
350 rule_id
351 from jtf_dpf_lgcl_next_rules lnr
352 where lnr.logical_flow_id = pp_q1_c2 and
353 lnr.default_next_flag='F'
354 order by lnr.rule_eval_seq;
355 ddindx binary_integer := 1;
356 q3_c1 fnd_application.application_short_name%type;
357 q3_c2 jtf_dpf_rules_b.rule_name%type;
358 q4_c1 fnd_application.application_short_name%type;
359 q4_c2 jtf_dpf_logical_pages_b.logical_page_name%type;
360 q5_c1 fnd_application.application_short_name%type;
361 q5_c2 jtf_dpf_logical_pages_b.logical_page_name%type;
362 q3_c1_temp fnd_application.application_id%type;
363 q4_c1_temp fnd_application.application_id%type;
364 q5_c1_temp fnd_application.application_id%type;
365 begin
366 for q1_rec in q1(asn) loop
367 for q2_rec in q2(q1_rec.logical_flow_id) loop
368 select
369 r.application_id,
370 -- (select application_short_name from fnd_application fa
371 -- where fa.application_id = r.application_id),
372 r.rule_name
373 into q3_c1_temp, q3_c2
374 from jtf_dpf_rules_b r
375 where r.rule_id = q2_rec.rule_id;
376
377 select application_short_name
378 into q3_c1
379 from fnd_application
380 where application_id = q3_c1_temp;
381
382 select
383 lp.application_id,
384 -- (select application_short_name from fnd_application fa
385 -- where fa.application_id = lp.application_id),
386 lp.logical_page_name
387 into q4_c1_temp, q4_c2
388 from jtf_dpf_logical_pages_b lp
389 where lp.logical_page_id = q2_rec.logical_page_id;
390
391 select application_short_name
392 into q4_c1
393 from fnd_application
394 where application_id = q4_c1_temp;
395
396 select
400 lp.logical_page_name
397 lp.application_id,
398 -- (select application_short_name from fnd_application fa
399 -- where fa.application_id = lp.application_id),
401 into q5_c1_temp, q5_c2
402 from jtf_dpf_logical_pages_b lp
403 where lp.logical_page_id = q2_rec.logical_next_page_id;
404
405 select application_short_name
406 into q5_c1
407 from fnd_application
408 where application_id = q5_c1_temp;
409
410 -- insert a new record into the table
411 retval(ddindx).dpf_name := q1_rec.logical_flow_name;
412 retval(ddindx).dpf_id := q1_rec.logical_flow_id;
413 retval(ddindx).rule_asn := q3_c1;
414 retval(ddindx).rule_name := q3_c2;
415 retval(ddindx).key_log_asn := q4_c1;
416 retval(ddindx).key_log_name := q4_c2;
417 retval(ddindx).result_log_asn := q5_c1;
418 retval(ddindx).result_log_name := q5_c2;
419
420 -- increment the index
421 ddindx := ddindx+1;
422 end loop;
423 end loop;
424 end;
425
426 procedure get_physical_attribs_tbl(asn varchar2,
427 retval out NOCOPY physical_attribs_tbl) is
428 cursor c1 (pp_asn varchar2) is
429 select pp.physical_page_id
430 from jtf_dpf_physical_pages_b pp
431 where pp.application_id =
432 (select fa.application_id from fnd_application fa
433 where fa.application_short_name = pp_asn);
434 ddindx binary_integer := 1;
435 begin
436 for c1_rec in c1(asn) loop
437 -- for any phy_attribs with c1_rec.physical_page_id...
438 for c2_rec in (select
439 pa.PHYSICAL_PAGE_ID, pa.PAGE_ATTRIBUTE_NAME,pa.PAGE_ATTRIBUTE_VALUE
440 from jtf_dpf_phy_attribs pa
441 where pa.physical_page_id = c1_rec.physical_page_id) loop
442 -- add a new record
443 retval(ddindx).id := c2_rec.physical_page_id;
444 retval(ddindx).name := c2_rec.page_attribute_name;
445 retval(ddindx).value := c2_rec.page_attribute_value;
446
447 -- incr the index
448 ddindx := ddindx + 1;
449 end loop;
450 end loop;
451 end;
452
453 procedure get (asn varchar2,
454 p_lang in out NOCOPY varchar2,
455 descrs_only boolean,
456 dpf out NOCOPY dpf_tbl,
457 log out NOCOPY logical_tbl,
458 phys out NOCOPY physical_tbl,
459 phys_non_def out NOCOPY physical_non_default_tbl,
460 rule out NOCOPY rule_tbl,
461 next_log_def out NOCOPY next_logical_default_tbl,
462 next_log_non_def out NOCOPY next_logical_non_default_tbl,
463 phys_atts out NOCOPY physical_attribs_tbl) is
464 l_lang_use fnd_languages.language_code%type;
465 l_lang_ret fnd_languages.language_code%type;
466 begin
467 select userenv('LANG') into l_lang_ret from dual;
468 if p_lang is null then
469 l_lang_use := l_lang_ret;
470 else
471 l_lang_use := p_lang;
472 end if;
473
474 get_dpf_tbl(l_lang_use, asn, dpf);
475 get_logical_tbl(l_lang_use, asn, log);
476 get_physical_tbl(l_lang_use, asn, phys);
477 if not descrs_only then
478 get_physical_non_default_tbl(asn, phys_non_def);
479 end if;
480 get_rule_tbl(l_lang_use, asn, rule);
481 if not descrs_only then
482 get_next_logical_default_tbl(asn, next_log_def);
483 end if;
484 if not descrs_only then
485 get_next_logical_non_def_tbl(asn, next_log_non_def);
486 end if;
487 if not descrs_only then
488 get_physical_attribs_tbl(asn, phys_atts);
489 end if;
490
491 p_lang := l_lang_ret;
492 end;
493
494 -- rule editing procedures
495 -- removes the rule and any rule_params that were stored under it. Has
496 -- no effect if the rule doesn't exist
497 procedure rule_delete(p_rule_id number) is
498 begin
499 delete from jtf_dpf_rule_params where rule_id = p_rule_id;
500 delete from jtf_dpf_rules_b where rule_id=p_rule_id;
501 delete from jtf_dpf_rules_tl where rule_id=p_rule_id;
502 -- commit;
503 end;
504
505 -- change the rule specified by rule_id so that it is of the specified
506 -- application, name, and description. Has no effect
507 -- if there's no such rule p_rule_id
508 function rule_update(p_rule_id number,
509 upd rule_update_rec) return number is
510 t_appid number;
511 existential number;
512 begin
513 if upd.p_new_name is null or 0 = length(upd.p_new_name) then
514 return 3;
515 end if;
516
517 select application_id into t_appid
518 from fnd_application where application_short_name = upd.p_new_asn;
519
520 -- is the proposed new name already taken? count the number
521 -- of rows which already have this name and appid, but which have a
522 -- different logical_flow_id
523 select count(*) into existential
524 from jtf_dpf_rules_b
525 where rule_id <> p_rule_id and
526 rule_name = upd.p_new_name and
527 application_id = t_appid;
528
529 if existential > 0 then return 2; end if;
530
531 update jtf_dpf_rules_b
532 set
533 application_id=t_appid,
534 rule_name = upd.p_new_name,
535 -- rule_description = p_new_descr,
536 object_version_number = OBJECT_VERSION_NUMBER+1,
537 last_update_date = sysdate,
538 last_updated_by = fnd_global.user_id,
539 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
543 update jtf_dpf_rules_tl
540 where rule_id = p_rule_id;
541
542 if upd.p_new_descr is null or fnd_api.g_miss_char <> upd.p_new_descr then
544 set rule_description = upd.p_new_descr,
545 -- object_version_number = OBJECT_VERSION_NUMBER+1,
546 last_update_date = sysdate,
547 last_updated_by = fnd_global.user_id,
548 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
549 where rule_id = p_rule_id and language = userenv('LANG');
550 end if;
551 return 1;
552 end;
553
554 function rule_new(p_asn varchar2, p_name varchar2, p_descr varchar2,
555 rules new_rule_param_tbl) return number is
556 counter number;
557 t_appid number;
558 t_ruleid number;
559 existential number;
560 t_rowid rowid;
561 begin
562 if p_name is null or 0 = length(p_name) then return 3; end if;
563
564 select application_id into t_appid
565 from fnd_application
566 where application_short_name = p_asn;
567
568 select count(*) into existential from jtf_dpf_rules_b
569 where rule_name = p_name and
570 application_id = t_appid;
571
572 if existential <> 0 then return 2; end if;
573
574 -- create a new rule
575 select jtf_dpf_rules_s.nextval into t_ruleid from dual;
576
577 JTF_DPF_RULES_PKG.INSERT_ROW(
578 X_ROWID => t_rowid,
579 X_RULE_ID => t_ruleid,
580 X_APPLICATION_ID => t_appid,
581 X_OBJECT_VERSION_NUMBER => 1,
582 X_RULE_NAME => p_name,
583 X_RULE_DESCRIPTION => p_descr,
584 X_CREATION_DATE => SYSDATE,
585 X_CREATED_BY => FND_GLOBAL.USER_ID,
586 X_LAST_UPDATE_DATE => SYSDATE,
587 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
588 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID);
589
590
591 -- insert into jtf_dpf_rules_b(
592 -- rule_id,
593 -- application_id, rule_name, -- rule_description,
594 -- OBJECT_VERSION_NUMBER,
595 -- CREATED_BY,
596 -- CREATION_DATE,
597 -- LAST_UPDATE_DATE,
598 -- LAST_UPDATED_BY,
599 -- LAST_UPDATE_LOGIN)
600 -- values(t_ruleid, t_appid, p_name, -- p_descr,
601 -- 1,
602 -- FND_GLOBAL.USER_ID,
603 -- SYSDATE,
604 -- SYSDATE,
605 -- FND_GLOBAL.USER_ID,
606 -- FND_GLOBAL.CONC_LOGIN_ID);
607 --
608 -- insert into jtf_dpf_rules_tl(
609 -- RULE_ID,
610 -- LANGUAGE,
611 -- SOURCE_LANG,
612 -- RULE_DESCRIPTION,
613 -- OBJECT_VERSION_NUMBER,
614 -- CREATED_BY,
615 -- CREATION_DATE,
616 -- LAST_UPDATE_DATE,
617 -- LAST_UPDATED_BY,
618 -- LAST_UPDATE_LOGIN)
619 -- values (
620 -- t_ruleid,
621 -- userenv('LANG'),
622 -- userenv('LANG'),
623 -- p_descr,
624 -- 1,
625 -- FND_GLOBAL.USER_ID,
626 -- SYSDATE,
627 -- SYSDATE,
628 -- FND_GLOBAL.USER_ID,
629 -- FND_GLOBAL.CONC_LOGIN_ID);
630
631
632 rule_set_params(t_ruleid, rules);
633 -- commit;
634 return 1;
635
636 end;
637
638 -- Sets the params of a rule rule_id. If there's no such rule,
639 -- then it has no effect. Removes the old rule_params efore
640 -- adding these.
641 -- it is not allowed to call this procedure with an empty or null
642 -- 'rules'.
643 procedure rule_set_params(p_rule_id number,
644 rules new_rule_param_tbl) is
645 idx binary_integer;
646 begin
647 delete from jtf_dpf_rule_params where rule_id = p_rule_id;
648 idx := rules.first;
649 while true loop
650 insert into jtf_dpf_rule_params(
651 rule_param_sequence,
652 rule_id,
653 rule_param_condition,
654 rule_param_name,
655 rule_param_value,
656 OBJECT_VERSION_NUMBER,
657 CREATED_BY,
658 CREATION_DATE,
659 LAST_UPDATE_DATE,
660 LAST_UPDATED_BY,
661 LAST_UPDATE_LOGIN)
662 values (
663 idx,
664 p_rule_id,
665 rules(idx).condition,
666 rules(idx).param_name,
667 rules(idx).param_value,
668 1,
669 FND_GLOBAL.USER_ID,
670 SYSDATE,
671 SYSDATE,
672 FND_GLOBAL.USER_ID,
673 FND_GLOBAL.CONC_LOGIN_ID);
674
675 if idx = rules.last then exit; end if;
676 idx := rules.next(idx);
677 end loop;
678 -- commit;
679 end;
680
681 -- Physical editing procedures
682 -- remove the physical denoted by ppid. If there's no such physical,
683 -- then this has no effect.
684 procedure phys_delete(p_ppid number) is
685 begin
686 delete from jtf_dpf_physical_pages_b where physical_page_id=p_ppid;
687 delete from jtf_dpf_physical_pages_tl where physical_page_id=p_ppid;
688 -- commit;
689 end;
690
691 function phys_update(p_ppid number,
692 upd phys_update_rec) return number is
693 t_appid number;
694 existential number;
695 begin
696 if upd.p_name is null or 0 = length(upd.p_name) then return 3; end if;
697
698 select application_id into t_appid
699 from fnd_application where application_short_name = upd.p_new_asn;
700
701 -- why was this ever here!? wird; I don't remember ever thinking
702 -- that this was the right rule for physicals...
706 -- select count(*) into existential
703 -- -- if there already exists a physical with this name and asn,
704 -- -- (other than the one we're being asked to update) then just
705 -- -- return '2' without touching the data
707 -- from jtf_dpf_physical_pages_b
708 -- where physical_page_id <> p_ppid and
709 -- physical_page_name = upd.p_name and
710 -- application_id = t_appid;
711 -- if existential > 0 then return 2; end if;
712
713 update jtf_dpf_physical_pages_b
714 set
715 application_id = t_appid,
716 physical_page_name = upd.p_name,
717 -- physical_page_description = p_descr,
718 object_version_number = OBJECT_VERSION_NUMBER+1,
719 last_update_date = sysdate,
720 last_updated_by = fnd_global.user_id,
721 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
722 where physical_page_id = p_ppid;
723
724 if upd.p_descr is null or fnd_api.g_miss_char <> upd.p_descr then
725 update jtf_dpf_physical_pages_tl
726 set
727 physical_page_description = upd.p_descr,
728 -- object_version_number = OBJECT_VERSION_NUMBER+1,
729 last_update_date = sysdate,
730 last_updated_by = fnd_global.user_id,
731 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
732 where physical_page_id = p_ppid and
733 language=userenv('LANG');
734 end if;
735 -- commit;
736 return 1;
737 end;
738
739 procedure phys_attribs_update(p_ppid number,
740 p_new_ones new_phys_attribs_tbl) is
741 idx binary_integer;
742 begin
743 -- remove any old ones
744 delete from jtf_dpf_phy_attribs where physical_page_id = p_ppid;
745
746 -- add the new ones, if any
747 if p_new_ones is not null and p_new_ones.count <> 0 then
748 idx := p_new_ones.first;
749 while true loop
750 insert into jtf_dpf_phy_attribs(
751 physical_page_id,
752 page_attribute_name,
753 page_attribute_value,
754 OBJECT_VERSION_NUMBER,
755 CREATED_BY,
756 CREATION_DATE,
757 LAST_UPDATE_DATE,
758 LAST_UPDATED_BY,
759 LAST_UPDATE_LOGIN)
760 values (
761 p_ppid,
762 p_new_ones(idx).name,
763 p_new_ones(idx).value,
764 1,
765 FND_GLOBAL.USER_ID,
766 SYSDATE,
767 SYSDATE,
768 FND_GLOBAL.USER_ID,
769 FND_GLOBAL.CONC_LOGIN_ID);
770 if idx = p_new_ones.last then exit; end if;
771 idx := p_new_ones.next(idx);
772 end loop;
773 end if;
774 -- commit;
775 end;
776
777 function phys_new (p_asn varchar2, p_name varchar2, p_descr varchar2)
778 return number is
779 t_appid number;
780 existential number;
781 t_phys_id jtf_dpf_physical_pages_b.physical_page_id%type;
782 t_rowid rowid;
783 begin
784 if p_name is null or 0 = length(p_name) then return 3; end if;
785
786 select application_id into t_appid
787 from fnd_application where application_short_name = p_asn;
788
789 -- why was this here!? we do allow more than one physical
790 -- with the same name!
791 -- -- if there already exists a physical with this name and asn,
792 -- -- then just return '2' without touching the data
793 -- select count(*) into existential
794 -- from jtf_dpf_physical_pages_b
795 -- where application_id = t_appid and
796 -- physical_page_name = p_name;
797 -- if existential > 0 then return 2; end if;
798
799 select jtf_dpf_physical_pages_s.nextval into t_phys_id from dual;
800
801 JTF_DPF_PHYSICAL_PAGES_PKG.insert_row(
802 X_ROWID => t_rowid,
803 X_PHYSICAL_PAGE_ID => t_phys_id,
804 X_PHYSICAL_PAGE_NAME => p_name,
805 X_APPLICATION_ID => t_appid,
806 X_OBJECT_VERSION_NUMBER => 1,
807 X_PHYSICAL_PAGE_DESCRIPTION => p_descr,
808 X_CREATION_DATE => SYSDATE,
809 X_CREATED_BY => FND_GLOBAL.USER_ID,
810 X_LAST_UPDATE_DATE => SYSDATE,
811 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
812 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID);
813
814 -- insert into jtf_dpf_physical_pages_b(
815 -- physical_page_id,
816 -- physical_page_name,
817 -- -- physical_page_description,
818 -- application_id,
819 -- OBJECT_VERSION_NUMBER,
820 -- CREATED_BY,
821 -- CREATION_DATE,
822 -- LAST_UPDATE_DATE,
823 -- LAST_UPDATED_BY,
824 -- LAST_UPDATE_LOGIN)
825 -- values (
826 -- t_phys_id,
827 -- p_name,
828 -- -- p_descr,
829 -- t_appid,
830 -- 1,
831 -- FND_GLOBAL.USER_ID,
832 -- SYSDATE,
833 -- SYSDATE,
834 -- FND_GLOBAL.USER_ID,
835 -- FND_GLOBAL.CONC_LOGIN_ID);
836 --
837 -- insert into jtf_dpf_physical_pages_tl(
838 -- PHYSICAL_PAGE_ID,
839 -- LANGUAGE,
840 -- SOURCE_LANG,
841 -- PHYSICAL_PAGE_DESCRIPTION,
842 -- OBJECT_VERSION_NUMBER,
843 -- CREATED_BY,
844 -- CREATION_DATE,
845 -- LAST_UPDATE_DATE,
846 -- LAST_UPDATED_BY,
847 -- LAST_UPDATE_LOGIN)
848 -- values (
849 -- t_phys_id,
850 -- userenv('LANG'),
851 -- userenv('LANG'),
852 -- p_descr,
853 -- 1,
854 -- FND_GLOBAL.USER_ID,
855 -- SYSDATE,
856 -- SYSDATE,
857 -- FND_GLOBAL.USER_ID,
858 -- FND_GLOBAL.CONC_LOGIN_ID);
859
863
860 -- commit;
861 return 1;
862 end;
864 -- dpf editing procedures
865 -- delete_flow. Removes all rows with logical_page_flow from tables:
866 -- - jtf_dpf_lgcl_flow_params
867 -- - jtf_dpf_logical_flows
868 -- - jtf_dpf_lgcl_next_rules
869 procedure flow_delete(p_logical_flow_id number) is
870 begin
871 delete from jtf_dpf_lgcl_flow_params
872 where logical_flow_id = p_logical_flow_id;
873 delete from jtf_dpf_logical_flows_b
874 where logical_flow_id = p_logical_flow_id;
875 delete from jtf_dpf_logical_flows_tl
876 where logical_flow_id = p_logical_flow_id;
877 delete from jtf_dpf_lgcl_next_rules
878 where logical_flow_id = p_logical_flow_id;
879 -- commit;
880 end;
881
882 function flow_update(p_logical_flow_id number,
883 upd flow_update_rec) return number is
884 t_appid number;
885 existential number;
886 current_name jtf_dpf_logical_flows_b.logical_flow_name%type;
887 begin
888 if upd.p_new_name is null or 0 = length(upd.p_new_name) then
889 return 3;
890 end if;
891
892 -- is either logical_page_id bad? The logical_page_id variables are
893 -- p_new_header_logical_page_id and p_rtn_to_logical_page_id. One of these
894 -- is 'bad' if it's not G_MISS_NUM and it doesn't point at a logical in
895 -- the jtf_dpf_logical_pages_b table.
896 --
897 -- if either is bad, then return 4.
898
899 if upd.p_new_header_logical_page_id is null or
900 upd.p_rtn_to_logical_page_id is null then
901 return 4;
902 end if;
903
904 if fnd_api.g_miss_num <> upd.p_new_header_logical_page_id then
905 select count(*) into existential
906 from jtf_dpf_logical_pages_b
907 where logical_page_id = upd.p_new_header_logical_page_id;
908 if existential = 0 then return 4; end if;
909 end if;
910
911 if fnd_api.g_miss_num <> upd.p_rtn_to_logical_page_id then
912 select count(*) into existential
913 from jtf_dpf_logical_pages_b
914 where logical_page_id = upd.p_rtn_to_logical_page_id;
915 if existential = 0 then return 4; end if;
916 end if;
917
918 select application_id into t_appid
919 from fnd_application where application_short_name = upd.p_new_asn;
920
921 -- is the proposed new name different from the current name, and yet
922 -- already taken? count the number of rows which already have this
923 -- name, but which have a different logical_flow_id
924 select logical_flow_name into current_name
925 from jtf_dpf_logical_flows_b
929 select count(*) into existential
926 where logical_flow_id = p_logical_flow_id;
927
928 if current_name <> upd.p_new_name then
930 from jtf_dpf_logical_flows_b
931 where logical_flow_id <> p_logical_flow_id and
932 logical_flow_name = upd.p_new_name and
933 application_id = t_appid;
934
935 if existential > 0 then return 2; end if;
936 end if;
937
938 update jtf_dpf_logical_flows_b
939 set
940 logical_flow_name = upd.p_new_name,
941 flow_finalizer_class = upd.p_new_flow_finalizer_class,
942 -- logical_flow_description = p_new_descr,
943 validate_flag = upd.p_new_validate_flag,
944 secure_flow_flag = upd.p_new_secure_flow_flag,
945 application_id = t_appid,
946 object_version_number = OBJECT_VERSION_NUMBER+1,
947 last_update_date = sysdate,
948 last_updated_by = fnd_global.user_id,
949 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
950 where logical_flow_id = p_logical_flow_id;
951
952 if upd.p_new_descr is null or fnd_api.g_miss_char <> upd.p_new_descr then
953 update jtf_dpf_logical_flows_tl
954 set
955 logical_flow_description = upd.p_new_descr,
956 -- object_version_number = OBJECT_VERSION_NUMBER+1,
957 last_update_date = sysdate,
958 last_updated_by = fnd_global.user_id,
959 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
960 where logical_flow_id = p_logical_flow_id and
961 language=userenv('LANG');
962 end if;
963
964 -- update logical_flow_head_id, unless the given is G_MISS_NUM
965 if fnd_api.g_miss_num <> upd.p_new_header_logical_page_id then
966 update jtf_dpf_logical_flows_b
967 set logical_flow_head_id = upd.p_new_header_logical_page_id
968 where logical_flow_id = p_logical_flow_id;
969 end if;
970
971 -- update return_to_page_id, unless the given is G_MISS_NUM
972 if fnd_api.g_miss_num <> upd.p_rtn_to_logical_page_id then
973 update jtf_dpf_logical_flows_b
974 set return_to_page_id = upd.p_rtn_to_logical_page_id
975 where logical_flow_id = p_logical_flow_id;
976 end if;
977
978 -- commit;
979 return 1;
980 end;
981
982 function flow_new(
983 p_new_asn varchar2,
984 p_new_name varchar2,
985 p_new_flow_finalizer_class varchar2,
986 p_new_descr varchar2,
987 p_new_validate_flag varchar2,
988 p_new_secure_flow_flag varchar2,
989 p_new_header_logical_page_id number,
990 p_rtn_to_logical_page_id number) return number is
991 t_appid number;
992 existential number;
993 log_id jtf_dpf_logical_flows_b.logical_flow_id%type;
994 t_rowid rowid;
995 begin
996 if p_new_name is null or 0 = length(p_new_name) then return 3; end if;
997
998 -- if there's no such logical_page_id as either
999 -- p_new_header_logical_page_id or p_rtn_to_logical_page_id,
1000 -- then return 4
1001 if p_new_header_logical_page_id is null or
1002 p_rtn_to_logical_page_id is null then
1003 return 4;
1004 end if;
1005 select count(*) into existential
1006 from jtf_dpf_logical_pages_b
1007 where logical_page_id = p_new_header_logical_page_id;
1008 if existential = 0 then return 4; end if;
1009 select count(*) into existential
1010 from jtf_dpf_logical_pages_b
1011 where logical_page_id = p_rtn_to_logical_page_id;
1012 if existential = 0 then return 4; end if;
1013
1014 select application_id into t_appid
1015 from fnd_application where application_short_name = p_new_asn;
1016
1017 select count(*) into existential
1018 from jtf_dpf_logical_flows_b
1019 where application_id = t_appid and
1020 logical_flow_name = p_new_name;
1021
1022 if existential <> 0 then return 2; end if;
1023
1024 select jtf_dpf_logical_flows_s.nextval into log_id from dual;
1025
1026 jtf_dpf_logical_flows_pkg.INSERT_ROW(
1027 X_ROWID => t_rowid,
1028 X_LOGICAL_FLOW_ID => log_id,
1029 X_LOGICAL_FLOW_HEAD_ID => p_new_header_logical_page_id,
1030 X_LOGICAL_FLOW_NAME => p_new_name,
1031 X_SECURE_FLOW_FLAG => p_new_secure_flow_flag,
1032 X_VALIDATE_FLAG => p_new_validate_flag,
1033 X_APPLICATION_ID => t_appid,
1034 X_FLOW_FINALIZER_CLASS => p_new_flow_finalizer_class,
1035 X_RETURN_TO_PAGE_ID => p_rtn_to_logical_page_id,
1036 X_BASE_FLOW_FLAG => 'F',
1037 -- X_ENABLED_CLONE_FLAG => 'T',
1038 X_OBJECT_VERSION_NUMBER => 1,
1039 X_LOGICAL_FLOW_DESCRIPTION => P_NEW_DESCR,
1040 X_CREATION_DATE => SYSDATE,
1041 X_CREATED_BY => FND_GLOBAL.USER_ID,
1042 X_LAST_UPDATE_DATE => SYSDATE,
1043 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1044 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID);
1045
1046 -- insert into jtf_dpf_logical_flows_b(
1047 -- logical_flow_id,
1048 -- logical_flow_head_id,
1049 -- return_to_page_id,
1050 -- logical_flow_name,
1051 -- -- logical_flow_description,
1052 -- validate_flag,
1053 -- secure_flow_flag,
1054 -- application_id,
1055 -- flow_finalizer_class,
1056 -- enabled_clone_flag,
1057 -- base_flow_flag,
1058 -- OBJECT_VERSION_NUMBER,
1059 -- CREATED_BY,
1060 -- CREATION_DATE,
1061 -- LAST_UPDATE_DATE,
1065 -- log_id,
1062 -- LAST_UPDATED_BY,
1063 -- LAST_UPDATE_LOGIN)
1064 -- values (
1066 -- p_new_header_logical_page_id,
1067 -- p_rtn_to_logical_page_id,
1068 -- p_new_name,
1069 -- -- p_new_descr,
1070 -- p_new_validate_flag,
1071 -- p_new_secure_flow_flag,
1072 -- t_appid,
1073 -- p_new_flow_finalizer_class,
1074 -- 'T',
1075 -- 'F',
1076 -- 1,
1077 -- FND_GLOBAL.USER_ID,
1078 -- SYSDATE,
1079 -- SYSDATE,
1080 -- FND_GLOBAL.USER_ID,
1081 -- FND_GLOBAL.CONC_LOGIN_ID);
1082 --
1083 -- insert into jtf_dpf_logical_flows_tl(
1084 -- logical_flow_id,
1085 -- language,
1086 -- source_lang,
1087 -- logical_flow_description,
1088 -- OBJECT_VERSION_NUMBER,
1089 -- CREATED_BY,
1090 -- CREATION_DATE,
1091 -- LAST_UPDATE_DATE,
1092 -- LAST_UPDATED_BY,
1093 -- LAST_UPDATE_LOGIN)
1094 -- values (
1095 -- log_id,
1096 -- userenv('LANG'),
1097 -- userenv('LANG'),
1098 -- p_new_descr,
1099 -- 1,
1100 -- FND_GLOBAL.USER_ID,
1101 -- SYSDATE,
1102 -- SYSDATE,
1103 -- FND_GLOBAL.USER_ID,
1104 -- FND_GLOBAL.CONC_LOGIN_ID);
1105
1106 -- commit;
1107 return 1;
1108 end;
1109
1110 function flow_copy(p_flow_id number, p_new_flow_id out NOCOPY number)
1111 return number is
1112 existential number;
1113 new_flow_id number;
1114 begin
1115 select count(*) into existential
1116 from jtf_dpf_logical_flows_b
1117 where logical_flow_id = p_flow_id;
1118
1119 if existential <> 1 then return 2; end if;
1120
1121 -- insert a single row into jtf_dpf_logical_flows_b and the
1122 -- same number of rows that're already in jtf_dpf_logical_flows_tl
1123 -- for the old p_flow_id.
1124
1125 select jtf_dpf_logical_flows_s.nextval into new_flow_id from dual;
1126
1127 p_new_flow_id := new_flow_id;
1128
1129 insert into jtf_dpf_logical_flows_b(
1130 LOGICAL_FLOW_ID,
1131 LOGICAL_FLOW_HEAD_ID,
1132 LOGICAL_FLOW_NAME,
1133 -- LOGICAL_FLOW_DESCRIPTION,
1134 SECURE_FLOW_FLAG,
1135 VALIDATE_FLAG,
1136 APPLICATION_ID,
1137 FLOW_FINALIZER_CLASS,
1138 RETURN_TO_PAGE_ID,
1139 ENABLED_CLONE_FLAG,
1140 BASE_FLOW_FLAG,
1141 OBJECT_VERSION_NUMBER,
1142 CREATED_BY,
1143 CREATION_DATE,
1144 LAST_UPDATE_DATE,
1145 LAST_UPDATED_BY,
1146 LAST_UPDATE_LOGIN)
1147 select
1148 new_flow_id,
1149 o.LOGICAL_FLOW_HEAD_ID,
1150 o.LOGICAL_FLOW_NAME,
1151 -- o.LOGICAL_FLOW_DESCRIPTION,
1152 o.SECURE_FLOW_FLAG,
1153 o.VALIDATE_FLAG,
1154 o.APPLICATION_ID,
1155 o.FLOW_FINALIZER_CLASS,
1156 o.RETURN_TO_PAGE_ID,
1157 'F',
1158 'F',
1159 1,
1160 FND_GLOBAL.USER_ID,
1161 SYSDATE,
1162 SYSDATE,
1163 FND_GLOBAL.USER_ID,
1164 FND_GLOBAL.CONC_LOGIN_ID
1165 from jtf_dpf_logical_flows_b o
1166 where o.logical_flow_id = p_flow_id;
1167
1168 insert into jtf_dpf_logical_flows_tl(
1169 logical_flow_id,
1170 language,
1171 source_lang,
1172 logical_flow_description,
1173 -- OBJECT_VERSION_NUMBER,
1174 CREATED_BY,
1175 -- CREATION_DATE,
1176 LAST_UPDATE_DATE,
1177 LAST_UPDATED_BY,
1178 LAST_UPDATE_LOGIN)
1179 select
1180 new_flow_id,
1181 o.language,
1182 o.source_lang,
1183 o.logical_flow_description,
1184 -- 1,
1185 FND_GLOBAL.USER_ID,
1186 -- SYSDATE,
1187 SYSDATE,
1188 FND_GLOBAL.USER_ID,
1189 FND_GLOBAL.CONC_LOGIN_ID
1190 from jtf_dpf_logical_flows_tl o
1191 where o.logical_flow_id = p_flow_id;
1192
1193 -- insert N new rows into table jtf_dpf_lgcl_next_rules with
1194 -- flow_id = new_flow_id, one for each row that's currently present for
1195 -- p_flow_id
1196
1197 insert into jtf_dpf_lgcl_next_rules(
1198 LOGICAL_NEXT_RULE_ID ,
1199 LOGICAL_PAGE_ID ,
1200 LOGICAL_NEXT_PAGE_ID ,
1201 DEFAULT_NEXT_FLAG ,
1202 RULE_EVAL_SEQ ,
1203 LOGICAL_FLOW_ID ,
1204 RULE_ID ,
1205 OBJECT_VERSION_NUMBER,
1206 CREATED_BY ,
1207 CREATION_DATE ,
1208 LAST_UPDATE_DATE ,
1209 LAST_UPDATED_BY ,
1210 LAST_UPDATE_LOGIN)
1211 select
1212 jtf_dpf_lgcl_nxt_rules_s.nextval,
1213 o.LOGICAL_PAGE_ID ,
1214 o.LOGICAL_NEXT_PAGE_ID ,
1215 o.DEFAULT_NEXT_FLAG ,
1216 o.RULE_EVAL_SEQ ,
1217 new_flow_id,
1218 o.RULE_ID ,
1219 1,
1220 FND_GLOBAL.USER_ID,
1221 SYSDATE,
1222 SYSDATE,
1223 FND_GLOBAL.USER_ID,
1224 FND_GLOBAL.CONC_LOGIN_ID
1225 from jtf_dpf_lgcl_next_rules o
1226 where o.logical_flow_id = p_flow_id;
1227
1228 return 1;
1229 end;
1230
1231 function flow_activate(p_flow_id number) return number is
1232 existential number;
1233 l_app_id number;
1234 l_flow_name jtf_dpf_logical_flows_b.logical_flow_name%type;
1235 begin
1236 select count(*) into existential
1237 from jtf_dpf_logical_flows_b
1238 where logical_flow_id = p_flow_id;
1239
1240 if existential <> 1 then return 2; end if;
1241
1242 select application_id, logical_flow_name
1243 into l_app_id, l_flow_name
1244 from jtf_dpf_logical_flows_b
1245 where logical_flow_id = p_flow_id;
1246
1247
1251 object_version_number = OBJECT_VERSION_NUMBER+1,
1248 -- deactivate all flows with the same appid and name as this one
1249 update jtf_dpf_logical_flows_b
1250 set enabled_clone_flag = 'F',
1252 last_update_date = sysdate,
1253 last_updated_by = fnd_global.user_id,
1254 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
1255 where application_id = l_app_id and logical_flow_name = l_flow_name;
1256
1257 -- activate this one
1258 update jtf_dpf_logical_flows_b
1259 set enabled_clone_flag = 'T',
1260 object_version_number = OBJECT_VERSION_NUMBER+1,
1261 last_update_date = sysdate,
1262 last_updated_by = fnd_global.user_id,
1263 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
1264 where logical_flow_id = p_flow_id;
1265
1266 return 1;
1267 end;
1268
1269 -- logical editing procedures:
1270 -- removes any instances with logical_page_id from tables:
1271 -- - jtf_dpf_logical_pages
1272 -- - jtf_dpf_lgcl_next_rules
1273 -- - jtf_dpf_lgcl_phy_rules
1274 -- removes the logical from JTF_DPF_LOGICAL_PAGES. Also removes
1275 -- any jtf_dpf_lgcl_phy_rules with the same logical_page_id
1276 procedure logical_delete(p_logical_page_id number) is
1277 begin
1278 delete from jtf_dpf_logical_pages_b
1279 where logical_page_id=p_logical_page_id;
1280 delete from jtf_dpf_logical_pages_tl
1281 where logical_page_id=p_logical_page_id;
1282 delete from jtf_dpf_lgcl_next_rules
1283 where logical_page_id=p_logical_page_id;
1284 delete from jtf_dpf_lgcl_phy_rules
1285 where logical_page_id=p_logical_page_id;
1286 -- commit;
1287 end;
1288
1289 function logical_update(p_logical_page_id number,
1290 upd logical_update_rec) return number is
1291 t_appid number;
1292 existential number;
1293 begin
1294 if upd.p_new_name is null or 0 = length(upd.p_new_name) then
1295 return 3;
1296 end if;
1297
1298 -- if there's no such phyiscal_id, then return 4
1299 if fnd_api.g_miss_num <> upd.p_default_physical_id then
1300 select count(*) into existential
1301 from jtf_dpf_physical_pages_b
1302 where physical_page_id = upd.p_default_physical_id;
1303
1304 if existential = 0 then return 4; end if;
1305 end if;
1306
1307 select application_id into t_appid
1308 from fnd_application where application_short_name = upd.p_new_asn;
1309
1310 -- is the proposed new name already taken? count the number
1311 -- of rows which already have this name, but which have a different
1312 -- logical_flow_id
1313 select count(*) into existential
1314 from jtf_dpf_logical_pages_b
1315 where logical_page_id <> p_logical_page_id and
1316 logical_page_name = upd.p_new_name and
1317 application_id = t_appid;
1318
1319 if existential > 0 then return 2; end if;
1320
1321 update jtf_dpf_logical_pages_b set
1322 logical_page_name = upd.p_new_name,
1323 logical_page_type = upd.p_new_type,
1324 application_id = t_appid,
1325 -- logical_page_description = upd.p_new_descr,
1326 page_controller_class = upd.p_new_page_controller_class,
1327 page_permission_name = upd.p_new_page_permission_name,
1328 object_version_number = OBJECT_VERSION_NUMBER+1,
1329 last_update_date = sysdate,
1330 last_updated_by = fnd_global.user_id,
1331 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
1332 where logical_page_id = p_logical_page_id;
1333
1334 if upd.p_new_descr is null or fnd_api.g_miss_char <> upd.p_new_descr then
1335 update jtf_dpf_logical_pages_tl set
1336 logical_page_description = upd.p_new_descr,
1337 -- object_version_number = OBJECT_VERSION_NUMBER+1,
1338 last_update_date = sysdate,
1339 last_updated_by = fnd_global.user_id,
1340 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID
1341 where logical_page_id = p_logical_page_id and
1342 language = userenv('LANG');
1343 end if;
1344
1345 if fnd_api.g_miss_num <> upd.p_default_physical_id then
1346 delete from jtf_dpf_lgcl_phy_rules where
1347 default_page_flag = 'T' and
1348 logical_page_id = p_logical_page_id;
1349
1350 insert into jtf_dpf_lgcl_phy_rules (
1351 logical_physical_id,
1352 logical_page_id,
1353 default_page_flag,
1354 physical_page_id,
1355 OBJECT_VERSION_NUMBER,
1356 CREATED_BY,
1357 CREATION_DATE,
1358 LAST_UPDATE_DATE,
1359 LAST_UPDATED_BY,
1360 LAST_UPDATE_LOGIN)
1361 values (
1362 jtf_dpf_lgcl_phy_rules_s.nextval,
1363 p_logical_page_id,
1364 'T',
1365 upd.p_default_physical_id,
1366 1,
1367 FND_GLOBAL.USER_ID,
1368 SYSDATE,
1369 SYSDATE,
1370 FND_GLOBAL.USER_ID,
1371 FND_GLOBAL.CONC_LOGIN_ID);
1372
1373 end if;
1374 -- commit;
1375 return 1;
1376 end;
1377
1378 function logical_new(
1379 p_new_asn varchar2,
1380 p_new_name varchar2,
1381 p_new_type varchar2,
1382 p_new_descr varchar2,
1383 p_new_page_controller_class varchar2,
1384 p_new_page_permission_name varchar2,
1385 p_default_physical_id JTF_DPF_PHYSICAL_PAGES_B.PHYSICAL_PAGE_ID%type)
1386 return number is
1387 t_appid number;
1388 t_new_logical_page_id number;
1389 t_rowid rowid;
1390 existential number;
1391 begin
1392 if p_new_name is null or 0 = length(p_new_name) then return 3; end if;
1393
1394 -- if there's no such phyiscal_id, then return 4
1398
1395 select count(*) into existential
1396 from jtf_dpf_physical_pages_b
1397 where physical_page_id = p_default_physical_id;
1399 if existential = 0 then return 4; end if;
1400
1401 select application_id into t_appid
1402 from fnd_application where application_short_name = p_new_asn;
1403
1404 -- if a logical with this name and appid already exist, then return 2
1405 select count(*) into existential
1406 from jtf_dpf_logical_pages_b
1407 where application_id = t_appid and
1408 logical_page_name = p_new_name;
1409
1410 if existential <> 0 then return 2; end if;
1411
1412 select jtf_dpf_logical_pages_s.nextval into t_new_logical_page_id
1413 from dual;
1414
1415 JTF_DPF_LOGICAL_PAGES_PKG.INSERT_ROW(
1416 X_ROWID => t_rowid,
1417 X_LOGICAL_PAGE_ID => t_new_logical_page_id,
1418 X_LOGICAL_PAGE_NAME => p_new_name,
1419 X_LOGICAL_PAGE_TYPE => p_new_type,
1420 X_APPLICATION_ID => t_appid,
1421 X_ENABLED_FLAG => 'T',
1422 X_PAGE_CONTROLLER_CLASS => p_new_page_controller_class,
1423 X_PAGE_PERMISSION_NAME => p_new_page_permission_name,
1424 X_OBJECT_VERSION_NUMBER => 1,
1425 X_LOGICAL_PAGE_DESCRIPTION => p_new_descr,
1426 X_CREATION_DATE => SYSDATE,
1427 X_CREATED_BY => FND_GLOBAL.USER_ID,
1428 X_LAST_UPDATE_DATE => SYSDATE,
1429 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1430 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID);
1431
1432 -- insert into jtf_dpf_logical_pages_b(
1433 -- enabled_flag,
1434 -- logical_page_id,
1435 -- logical_page_name,
1436 -- logical_page_type,
1437 -- application_id,
1438 -- -- logical_page_description,
1439 -- page_controller_class,
1440 -- page_permission_name,
1441 -- OBJECT_VERSION_NUMBER,
1442 -- CREATED_BY,
1443 -- CREATION_DATE,
1444 -- LAST_UPDATE_DATE,
1445 -- LAST_UPDATED_BY,
1446 -- LAST_UPDATE_LOGIN)
1447 -- values (
1448 -- 'T',
1449 -- jtf_dpf_logical_pages_s.nextval,
1450 -- p_new_name,
1451 -- p_new_type,
1452 -- t_appid,
1453 -- -- p_new_descr,
1454 -- p_new_page_controller_class,
1455 -- p_new_page_permission_name,
1456 -- 1,
1457 -- FND_GLOBAL.USER_ID,
1458 -- SYSDATE,
1459 -- SYSDATE,
1460 -- FND_GLOBAL.USER_ID,
1461 -- FND_GLOBAL.CONC_LOGIN_ID)
1462 -- returning logical_page_id into t_new_logical_page_id;
1463 --
1464 -- insert into jtf_dpf_logical_pages_tl(
1465 -- logical_page_id,
1466 -- language,
1467 -- source_lang,
1468 -- logical_page_description,
1469 -- OBJECT_VERSION_NUMBER,
1470 -- CREATED_BY,
1471 -- CREATION_DATE,
1472 -- LAST_UPDATE_DATE,
1473 -- LAST_UPDATED_BY,
1474 -- LAST_UPDATE_LOGIN)
1475 -- values (
1476 -- t_new_logical_page_id,
1477 -- userenv('LANG'),
1478 -- userenv('LANG'),
1479 -- p_new_descr,
1480 -- 1,
1481 -- FND_GLOBAL.USER_ID,
1482 -- SYSDATE,
1483 -- SYSDATE,
1484 -- FND_GLOBAL.USER_ID,
1485 -- FND_GLOBAL.CONC_LOGIN_ID);
1486
1487 insert into jtf_dpf_lgcl_phy_rules (
1488 logical_physical_id,
1489 logical_page_id,
1490 default_page_flag,
1491 physical_page_id,
1492 OBJECT_VERSION_NUMBER,
1493 CREATED_BY,
1494 CREATION_DATE,
1495 LAST_UPDATE_DATE,
1496 LAST_UPDATED_BY,
1497 LAST_UPDATE_LOGIN)
1498 values (
1499 jtf_dpf_lgcl_phy_rules_s.nextval,
1500 t_new_logical_page_id,
1501 'T',
1502 p_default_physical_id,
1503 1,
1504 FND_GLOBAL.USER_ID,
1505 SYSDATE,
1506 SYSDATE,
1507 FND_GLOBAL.USER_ID,
1508 FND_GLOBAL.CONC_LOGIN_ID);
1509
1510 -- commit;
1511 return 1;
1512 end;
1513
1514 -- updates table JTF_DPF_LGCL_PHY_RULES, so that the default_next_flag='F'
1515 -- rows which it contains for this logical_page_id are the rules and
1516 -- results specified by p_new_ones. It first throws out any old
1517 -- rows in the table.
1518 --
1519 -- This has the effect of removing the non-default rules if p_new_ones
1520 -- is either null or empty
1521 procedure logical_set_non_default_phys(p_logical_page_id number,
1522 p_new_ones new_phys_non_def_tbl) is
1523 idx binary_integer;
1524 begin
1525 delete from jtf_dpf_lgcl_phy_rules where
1526 logical_page_id = p_logical_page_id and
1527 default_page_flag='F';
1528
1529 if p_new_ones is not null and p_new_ones.count <> 0 then
1530 idx := p_new_ones.first;
1531 while true loop
1532 insert into jtf_dpf_lgcl_phy_rules (
1533 logical_physical_id,
1534 logical_page_id,
1535 default_page_flag,
1536 rule_eval_sequence,
1537 physical_page_id,
1538 rule_id,
1539 OBJECT_VERSION_NUMBER,
1540 CREATED_BY,
1541 CREATION_DATE,
1542 LAST_UPDATE_DATE,
1543 LAST_UPDATED_BY,
1544 LAST_UPDATE_LOGIN)
1545 values(
1546 jtf_dpf_lgcl_phy_rules_s.nextval,
1547 p_logical_page_id,
1548 'F',
1549 idx,
1550 p_new_ones(idx).physical_page_id,
1551 p_new_ones(idx).rule_id,
1552 1,
1553 FND_GLOBAL.USER_ID,
1554 SYSDATE,
1555 SYSDATE,
1556 FND_GLOBAL.USER_ID,
1557 FND_GLOBAL.CONC_LOGIN_ID);
1558
1559 if idx = p_new_ones.last then exit; end if;
1563 -- commit;
1560 idx := p_new_ones.next(idx);
1561 end loop;
1562 end if;
1564 end;
1565
1566 -- set next_logicals
1567 -- sets the default next logical of (flow_id, log_page_id) to
1568 -- next_log_page_id. This might either update an existing
1569 -- row in JTF_DPF_LGCL_NEXT_RULES or insert a new one
1570 -- if the new 'next' is null, it means there is no more next_logical
1571 -- for the given one.
1572 function next_logical_set_default(
1573 p_flow_id jtf_dpf_lgcl_next_rules.logical_flow_id%type,
1574 p_log_page_id jtf_dpf_lgcl_next_rules.logical_page_id%type,
1575 p_next_log_page_id jtf_dpf_lgcl_next_rules.logical_next_page_id%type)
1576 return number is
1577 existential number;
1578 begin
1579
1580 -- see if this is an error case; return '2' if either p_log_page_id
1581 -- isn't there, or if p_next_log_page_id is both not null and not there
1582 select count(*) into existential
1583 from jtf_dpf_logical_pages_b
1584 where logical_page_id = p_log_page_id;
1585
1586 if existential = 0 then return 2; end if;
1587
1588 if p_next_log_page_id is not null then
1589 select count(*) into existential
1590 from jtf_dpf_logical_pages_b
1591 where logical_page_id = p_next_log_page_id;
1592
1593 if existential = 0 then return 2; end if;
1594 end if;
1595
1596 -- not the error case! just do the update, then...
1597 if p_next_log_page_id is null then
1598 delete from jtf_dpf_lgcl_next_rules
1599 where logical_page_id = p_log_page_id and
1600 logical_flow_id = p_flow_id;
1601 else
1602 delete from jtf_dpf_lgcl_next_rules
1603 where default_next_flag = 'T' and
1604 logical_page_id = p_log_page_id and
1605 logical_flow_id = p_flow_id;
1606 insert into jtf_dpf_lgcl_next_rules (
1607 logical_next_rule_id,
1608 logical_page_id,
1609 logical_next_page_id,
1610 default_next_flag,
1611 logical_flow_id,
1612 OBJECT_VERSION_NUMBER,
1613 CREATED_BY,
1614 CREATION_DATE,
1615 LAST_UPDATE_DATE,
1616 LAST_UPDATED_BY,
1617 LAST_UPDATE_LOGIN)
1618 values (
1619 jtf_dpf_lgcl_nxt_rules_s.nextval,
1620 p_log_page_id,
1621 p_next_log_page_id,
1622 'T',
1623 p_flow_id,
1624 1,
1625 FND_GLOBAL.USER_ID,
1626 SYSDATE,
1627 SYSDATE,
1628 FND_GLOBAL.USER_ID,
1629 FND_GLOBAL.CONC_LOGIN_ID);
1630
1631 end if;
1632 -- commit;
1633 return 1;
1634 end;
1635
1636 -- sets up the non-default next logical rules for (flow_id, log_page_id).
1637 -- if there were already non-default rules for it, it removes them first
1638 procedure next_logical_set_non_default(
1639 p_flow_id jtf_dpf_lgcl_next_rules.logical_flow_id%type,
1640 p_log_page_id jtf_dpf_lgcl_next_rules.logical_page_id%type,
1641 p_new_ones new_next_log_non_def_tbl) is
1642 idx binary_integer;
1643 begin
1644 delete from jtf_dpf_lgcl_next_rules
1645 where default_next_flag = 'F' and
1646 logical_page_id = p_log_page_id and
1647 logical_flow_id = p_flow_id;
1648
1649 if p_new_ones is not null and p_new_ones.count <> 0 then
1650 idx := p_new_ones.first;
1651 while true loop
1652 insert into jtf_dpf_lgcl_next_rules(
1653 logical_next_rule_id,
1654 logical_page_id,
1655 logical_next_page_id,
1656 default_next_flag,
1657 rule_eval_seq,
1658 logical_flow_id,
1659 rule_id,
1660 OBJECT_VERSION_NUMBER,
1661 CREATED_BY,
1662 CREATION_DATE,
1663 LAST_UPDATE_DATE,
1664 LAST_UPDATED_BY,
1665 LAST_UPDATE_LOGIN)
1666 values (
1667 jtf_dpf_lgcl_nxt_rules_s.nextval,
1668 p_log_page_id,
1669 p_new_ones(idx).logical_page_id,
1670 'F',
1671 idx,
1672 p_flow_id,
1673 p_new_ones(idx).rule_id,
1674 1,
1675 FND_GLOBAL.USER_ID,
1676 SYSDATE,
1677 SYSDATE,
1678 FND_GLOBAL.USER_ID,
1679 FND_GLOBAL.CONC_LOGIN_ID);
1680
1681 if idx = p_new_ones.last then exit; end if;
1682 idx := p_new_ones.next(idx);
1683 end loop;
1684 end if;
1685 -- commit;
1686 end;
1687 end;