1 package jtf_dpf as
2 /* $Header: jtfdpfs.pls 120.1 2005/07/02 00:40:44 appldev ship $ */
3
4 -- created by sel12.sql
5 type dpf is record (
6 dpf_id jtf_dpf_lgcl_flow_params.logical_flow_id%type,
7 dpf_name jtf_dpf_logical_flows_b.logical_flow_name%type,
8 head_logical_asn fnd_application.application_short_name%type,
9 head_logical_name jtf_dpf_logical_pages_b.logical_page_name%type,
10 rtn_to_page_logical_asn fnd_application.application_short_name%type,
11 rtn_to_page_logical_name jtf_dpf_logical_pages_b.logical_page_name%type,
12 flow_finalizer_class jtf_dpf_logical_flows_b.flow_finalizer_class%type,
13 logical_flow_description
14 jtf_dpf_logical_flows_tl.logical_flow_description%type,
15 validate_flag jtf_dpf_logical_flows_b.validate_flag%type,
16 secure_flow_flag jtf_dpf_logical_flows_b.secure_flow_flag%type,
17 active_flag varchar2(1), -- jtf_dpf_logical_flows_b.enabled_clone_flag%type,
18 editable_flag varchar2(1));
19
20 -- created by sel13.sql
21 type logical is record(
22 logical_page_id jtf_dpf_logical_pages_b.logical_page_id%type,
23 logical_page_name jtf_dpf_logical_pages_b.logical_page_name%type,
24 logical_page_type jtf_dpf_logical_pages_b.logical_page_type%type,
25 logical_page_description
26 jtf_dpf_logical_pages_tl.logical_page_description%type,
27 page_controller_class jtf_dpf_logical_pages_b.page_controller_class%type,
28 page_permission_name jtf_dpf_logical_pages_b.page_permission_name%type,
29 def_phys_asn fnd_application.application_short_name%type,
30 def_phys_id number,
31 def_phys_name jtf_dpf_physical_pages_b.physical_page_name%type);
32
33 -- created by sel14.sql
34 type physical is record(
35 id number,
36 name jtf_dpf_physical_pages_b.physical_page_name%type,
37 descr jtf_dpf_physical_pages_tl.physical_page_description%type);
38
39 -- created by sel15.sql
40 type physical_non_default is record(
41 logical_name jtf_dpf_logical_pages_b.logical_page_name%type,
42 rule_eval_sequence jtf_dpf_lgcl_phy_rules.rule_eval_sequence%type,
43 rule_asn fnd_application.application_short_name%type,
44 rule_name jtf_dpf_rules_b.rule_name%type,
45 phys_asn fnd_application.application_short_name%type,
46 phys_id number,
47 phys_name jtf_dpf_physical_pages_b.physical_page_name%type);
48
49 -- sel16.sql
50 type rule is record(
51 rule_id jtf_dpf_rules_b.rule_id%type,
52 rule_name jtf_dpf_rules_b.rule_name%type,
53 rule_description jtf_dpf_rules_tl.rule_description%type,
54 rule_param_name jtf_dpf_rule_params.rule_param_name%type,
55 rule_param_value jtf_dpf_rule_params.rule_param_value%type,
56 rule_param_condition jtf_dpf_rule_params.rule_param_condition%type);
57
58 -- sel17.sql
59 type next_logical_default is record(
60 dpf_name jtf_dpf_logical_flows_b.logical_flow_name%type,
61 dpf_id jtf_dpf_logical_flows_b.logical_flow_id%type,
62 key_log_asn fnd_application.application_short_name%type,
63 key_log_name jtf_dpf_logical_pages_b.logical_page_name%type,
64 result_log_asn fnd_application.application_short_name%type,
65 result_log_name jtf_dpf_logical_pages_b.logical_page_name%type);
66
67 -- sel18.sql
68 type next_logical_non_default is record(
69 dpf_name jtf_dpf_logical_flows_b.logical_flow_name%type,
70 dpf_id jtf_dpf_logical_flows_b.logical_flow_id%type,
71 rule_asn fnd_application.application_short_name%type,
72 rule_name jtf_dpf_rules_b.rule_name%type,
73 key_log_asn fnd_application.application_short_name%type,
74 key_log_name jtf_dpf_logical_pages_b.logical_page_name%type,
75 result_log_asn fnd_application.application_short_name%type,
76 result_log_name jtf_dpf_logical_pages_b.logical_page_name%type);
77
78 type physical_attribs is record(
79 id number,
80 name jtf_dpf_phy_attribs.page_attribute_name%type,
81 value jtf_dpf_phy_attribs.page_attribute_value%type);
82
83 -- used to nad
84 type new_rule_param is record(
85 condition jtf_dpf_rule_params.rule_param_condition%type,
86 param_name jtf_dpf_rule_params.rule_param_name%type,
87 param_value jtf_dpf_rule_params.rule_param_value%type);
88
89 type new_rule_param_tbl is table of new_rule_param index by binary_integer;
90
91 type new_phys_non_def is record (rule_id number, physical_page_id number);
92 type new_phys_non_def_tbl is table of new_phys_non_def
93 index by binary_integer;
94 type new_next_log_non_def is record (rule_id number, logical_page_id number);
95 type new_next_log_non_def_tbl is table of new_next_log_non_def
96 index by binary_integer;
97 type new_phys_attribs is record(
98 name jtf_dpf_phy_attribs.page_attribute_name%type,
99 value jtf_dpf_phy_attribs.page_attribute_value%type);
100 type new_phys_attribs_tbl is table of new_phys_attribs
101 index by binary_integer;
102
103 type dpf_tbl is table of dpf index by binary_integer;
104 type logical_tbl is table of logical index by binary_integer;
105 type physical_tbl is table of physical index by binary_integer;
106 type physical_non_default_tbl is table of physical_non_default
107 index by binary_integer;
108 type rule_tbl is table of rule index by binary_integer;
109 type next_logical_default_tbl is table of next_logical_default
110 index by binary_integer;
111 type next_logical_non_default_tbl is table of next_logical_non_default
112 index by binary_integer;
113 type physical_attribs_tbl is table of physical_attribs
114 index by binary_integer;
115
116 -- type lang_rec is record(lang fnd_languages.language_code%type);
117
118 -- this procedure gets everything there is to know about a DPF,
119 -- per user language, in a single round trip!
120 -- Java will call this with descrs_only=true if we already has the DPF
121 -- data for the given 'asn', except not in this langauge. if descrs_only
122 -- is true, then we don't bother to write these 4 variables:
123 -- phys_non_def
124 -- next_log_def
125 -- next_log_non_def
126 -- phys_attrs
127 --
128 -- the p_lang parameter is the language code ('US', 'JP', etc...)
129 -- We allow the user of this interface to pass in an explicit parameter,
130 -- to work around repeated troubles for the java client who has a hard time
131 -- getting the a JDBC Connection with the right language handle. If the
132 -- language is not provided (i.e. if p_lang is null) then we use
133 -- userenv('lang'). In any case, we set p_lang to userenv('lang') before
134 -- returning.
135
136 procedure get (asn varchar2,
137 p_lang in out NOCOPY varchar2,
138 descrs_only boolean,
139 dpf out NOCOPY dpf_tbl,
140 log out NOCOPY logical_tbl,
141 phys out NOCOPY physical_tbl,
142 phys_non_def out NOCOPY physical_non_default_tbl,
143 rule out NOCOPY rule_tbl,
144 next_log_def out NOCOPY next_logical_default_tbl,
145 next_log_non_def out NOCOPY next_logical_non_default_tbl,
146 phys_atts out NOCOPY physical_attribs_tbl);
147
148 -- rule editing procedures
149 -- removes the rule and any rule_params that were stored under it. Has
150 -- no effect if the rule doesn't exist
151 procedure rule_delete(p_rule_id number);
152 -- change the rule specified by rule_id so that it is of the specified
153 -- application, name, and description. Has no effect
154 -- if no rule has p_rule_id
155 --
156 -- returns:
157 -- 1: successfully updated
158 -- 2: the proposed new name "p_new_name" is already taken
159 -- 3: the proposed new name "p_new_name" is null
160 type rule_update_rec is record(
161 p_new_asn fnd_application.application_short_name%type,
162 p_new_name jtf_dpf_rules_b.rule_name%type,
163 p_new_descr jtf_dpf_rules_tl.rule_description%type);
164
165 function rule_update(p_rule_id number,
166 upd rule_update_rec) return number;
167
168 -- creates the rule (ASN, NAME) if it doesn't already exist.
169 --
170 -- Returns one of the following values:
171 -- 1: new one successfully created
172 -- 2: new one not created: the given "p_new_name" is already taken
173 -- 3: new one not created: the given "p_new_name" is null
174 function rule_new(p_asn varchar2, p_name varchar2, p_descr varchar2,
175 rules new_rule_param_tbl) return number;
176
177 -- Sets the params of a rule rule_id. If there's no such rule,
178 -- then it has no effect.
179 procedure rule_set_params(p_rule_id number,
180 rules new_rule_param_tbl);
181
182 -- Physical editing procedures
183 -- remove the physical denoted by ppid. If there's no such physical,
184 -- then this has no effect.
185 procedure phys_delete(p_ppid number);
186
187 -- modify the phys denoted by ppid to have the new name and description
188 -- If there's no such physical, then this has no effect. Returns one
189 -- of:
190 --
191 -- 1: physical successfully updated
192 -- 3: physical not successfully updated: the given p_name is null or empty
193 type phys_update_rec is record(
194 p_new_asn fnd_application.application_short_name%type,
195 p_name jtf_dpf_physical_pages_b.physical_page_name%type,
196 p_descr jtf_dpf_physical_pages_tl.physical_page_description%type);
197
198 function phys_update(p_ppid number,
199 upd phys_update_rec) return number;
200
201 -- modify the physical (specified by its physical_id = p_ppid)
202 -- so that it has the given entries in table jtf_dpf_phy_attribs.
203 -- this replaces any previous entries. If p_new_ones is null or empty,
204 -- then the call has the effect of removing all physical_attribs
205 -- for the physical page
206 procedure phys_attribs_update(p_ppid number,
207 p_new_ones new_phys_attribs_tbl);
208
209 -- create a new phys. Returns one of the following values:
210 --
211 -- 1: new one successfully created
212 -- 3: new one not created: the given "p_name" is null or empty
213 function phys_new (p_asn varchar2, p_name varchar2, p_descr varchar2)
214 return number;
215
216 -- dpf editing procedures
217 -- delete_flow. Removes all rows with logical_page_flow from tables:
218 -- - jtf_dpf_lgcl_flow_params
219 -- - jtf_dpf_logical_flows
220 -- - jtf_dpf_lgcl_next_rules
221 procedure flow_delete(p_logical_flow_id number);
222
223 -- updates the components of the flow specified by p_logical_flow_id.
224 -- if p_new_header_logical_page_id is G_MISS_NUM, then it's not
225 -- altered. Likewise, if p_rtn_to_logical_page_id is G_MISS_NUM, then it's
226 -- not altered.
227 -- This function has no effect of there's no such flow as p_logical_flow_id.
228 --
229 -- returns
230 -- 1: successfully updated
231 -- 2: no update: the proposed new name "p_new_name" is already taken
232 -- 3: no update: the proposed new name "p_new_name" is null
233 -- 4: no update: the given "p_new_header_logical_page_id" or
234 -- "p_rtn_to_logical_page_id" (or both) is not G_MISS_NUM, and is bad
235 type flow_update_rec is record(
236 p_new_asn fnd_application.application_short_name%type,
237 p_new_name jtf_dpf_logical_flows_b.logical_flow_name%type,
238 p_new_flow_finalizer_class
239 jtf_dpf_logical_flows_b.flow_finalizer_class%type,
240 p_new_descr jtf_dpf_logical_flows_tl.logical_flow_description%type,
241 p_new_validate_flag varchar2(1),
242 p_new_secure_flow_flag varchar2(1),
243 p_new_header_logical_page_id
244 jtf_dpf_logical_pages_b.logical_page_id%type,
245 p_rtn_to_logical_page_id jtf_dpf_logical_pages_b.logical_page_id%type);
246
247 function flow_update(p_logical_flow_id number,
248 upd flow_update_rec) return number;
249
250 -- create a new Key Business Flow with the given info.
251 --
252 -- Returns one of the following values:
253 -- 1: new one successfully created
254 -- 2: new one not created: the given "p_new_name" is already taken
255 -- 3: new one not created: the given "p_new_name" is null
256 -- 4: new one not created: the given "p_new_header_logical_page_id"
257 -- or "p_rtn_to_logical_page_id" is bad
258 function flow_new(
259 p_new_asn varchar2,
260 p_new_name varchar2,
261 p_new_flow_finalizer_class varchar2,
262 p_new_descr varchar2,
263 p_new_validate_flag varchar2,
264 p_new_secure_flow_flag varchar2,
265 p_new_header_logical_page_id number,
266 p_rtn_to_logical_page_id number) return number;
267
268 -- create a new Key Business Flow with the same name as the given one.
269 -- writes the flow_id of the newly-created flow into p_new_flow_id.
270 --
271 -- Returns one of the following values:
272 -- 1: new one successfully created
273 -- 2: new one not created: the given "p_flow_id" not found
274 function flow_copy(p_flow_id number, p_new_flow_id out NOCOPY number) return number;
275
276 -- sets the given flow to 'active'. All other flows with the same
277 -- appid and flow_name become deactivated.
278 --
279 -- Returns one of the following values:
280 -- 1: new one successfully activated
281 -- 2: new one not created: the given "p_flow_id" not found
282 function flow_activate(p_flow_id number) return number;
283
284
285 -- logical editing procedures:
286 -- removes any instances with logical_page_id from tables:
287 -- - jtf_dpf_logical_pages
288 -- - jtf_dpf_lgcl_next_rules
289 -- - jtf_dpf_lgcl_phy_rules
290 -- removes the logical from JTF_DPF_LOGICAL_PAGES. Also removes
291 -- any jtf_dpf_lgcl_phy_rules with the same logical_page_id
292 procedure logical_delete(p_logical_page_id number);
293
294 -- update the jtf_dpf_logical_pages table so that the logical identified
295 -- by logical_page_id has the specified ASN, NAME, and DESCR.
296 -- Also updates the JTF_DPF_LGCL_PHY_RULES table,
297 -- so that the one default_next_flag='T' row it has for this logical_page_id
298 -- indicates the specified default_physical page
299 --
300 -- This does NOT update the defaultPhysical of the Logical if
301 -- p_default_physical_id is G_MISS_NUM.
302 --
303 -- returns
304 -- 1: successfully updated
305 -- 2: no update: the proposed new name "p_new_name" is already taken
306 -- 3: no update: the proposed new name "p_new_name" is null
307 -- 4: no update: new one not created: the given "p_default_physical_id"
308 -- is not G_MISS_NUM, and there's no such Physical
309 type logical_update_rec is record(
310 p_new_asn fnd_application.application_short_name%type,
311 p_new_name jtf_dpf_logical_pages_b.logical_page_name%type,
312 p_new_type jtf_dpf_logical_pages_b.logical_page_type%type,
313 p_new_descr jtf_dpf_logical_pages_tl.logical_page_description%type,
314 p_new_page_controller_class
315 jtf_dpf_logical_pages_b.page_controller_class%type,
316 p_new_page_permission_name
317 jtf_dpf_logical_pages_b.page_permission_name%type,
318 p_default_physical_id JTF_DPF_PHYSICAL_PAGES_B.PHYSICAL_PAGE_ID%type);
319
320 function logical_update(p_logical_page_id number,
321 upd logical_update_rec) return number;
322
323 -- creates a new logical with the designated parameters. Always inserts
324 -- 1 row into JTF_DPF_LOGICAL_PAGES and one row into JTF_DPF_LGCL_PHY_RULES
325 --
326 -- Returns one of the following values:
327 -- 1: new one successfully created
328 -- 2: new one not created: the given "p_new_name" is already taken
329 -- 3: new one not created: the given "p_new_name" is null
330 -- 4: new one not created: the given "p_default_physical_id" is bad
331 function logical_new(
332 p_new_asn varchar2,
333 p_new_name varchar2,
334 p_new_type varchar2,
335 p_new_descr varchar2,
336 p_new_page_controller_class varchar2,
337 p_new_page_permission_name varchar2,
338 p_default_physical_id JTF_DPF_PHYSICAL_PAGES_B.PHYSICAL_PAGE_ID%type)
339 return number;
340
341 -- updates table JTF_DPF_LGCL_PHY_RULES, so that the default_next_flag='F'
342 -- rows which it contains for this logical_page_id are the rules and
343 -- results specified by p_new_ones
344 procedure logical_set_non_default_phys(p_logical_page_id number,
345 p_new_ones new_phys_non_def_tbl);
346
347 -- set next_logicals
348 -- sets the default next logical of (p_flow_id, p_log_page_id) to
349 -- next_log_page_id. This might either update an existing
350 -- row in JTF_DPF_LGCL_NEXT_RULES or insert a new one.
351 -- if p_next_log_page_id is null, it means that the next logical
352 -- of (p_flow_id, p_log_page_id) is null, i.e. that there's
353 -- nothing after it (it's the last in the flow)
354 --
355 -- returns:
356 -- 1: successfully updated
357 -- 2: p_log_page_id is not found, or p_next_log_page_id
358 -- is both not null and not a valid logical_page_id
359 function next_logical_set_default(
360 p_flow_id jtf_dpf_lgcl_next_rules.logical_flow_id%type,
361 p_log_page_id jtf_dpf_lgcl_next_rules.logical_page_id%type,
362 p_next_log_page_id jtf_dpf_lgcl_next_rules.logical_next_page_id%type)
363 return number;
364
365 -- sets up the non-default next logical rules for (flow_id, log_page_id).
366 -- if there were already non-default rules for it, it removes them first
367 procedure next_logical_set_non_default(
368 p_flow_id jtf_dpf_lgcl_next_rules.logical_flow_id%type,
369 p_log_page_id jtf_dpf_lgcl_next_rules.logical_page_id%type,
370 p_new_ones new_next_log_non_def_tbl);
371
372 end;