1 PACKAGE hxc_preference_evaluation AUTHID CURRENT_USER AS
2 /* $Header: hxcpfevl.pkh 120.6 2008/02/29 13:58:10 asrajago ship $ */
3
4
5
6 TYPE resplist_rec IS RECORD
7 (
8 resp_id NUMBER,
9 start_date DATE,
10 stop_date DATE );
11
12 TYPE resplisttab IS TABLE OF resplist_rec ;
13
14
15
16 Type r_pref_node_value is Record
17 ( pref_hierarchy_id hxc_pref_hierarchies.pref_hierarchy_id%TYPE
18 ,pref_definition_id hxc_pref_hierarchies.pref_definition_id%TYPE
19 ,attribute1 varchar(150)
20 ,attribute2 varchar(150)
21 ,attribute3 varchar(150)
22 ,attribute4 varchar(150)
23 ,attribute5 varchar(150)
24 ,attribute6 varchar(150)
25 ,attribute7 varchar(150)
26 ,attribute8 varchar(150)
27 ,attribute9 varchar(150)
28 ,attribute10 varchar(150)
29 ,attribute11 varchar(150)
30 ,attribute12 varchar(150)
31 ,attribute13 varchar(150)
32 ,attribute14 varchar(150)
33 ,attribute15 varchar(150)
34 ,attribute16 varchar(150)
35 ,attribute17 varchar(150)
36 ,attribute18 varchar(150)
37 ,attribute19 varchar(150)
38 ,attribute20 varchar(150)
39 ,attribute21 varchar(150)
40 ,attribute22 varchar(150)
41 ,attribute23 varchar(150)
42 ,attribute24 varchar(150)
43 ,attribute25 varchar(150)
44 ,attribute26 varchar(150)
45 ,attribute27 varchar(150)
46 ,attribute28 varchar(150)
47 ,attribute29 varchar(150)
48 ,attribute30 varchar(150)
49 ,edit_allowed hxc_pref_hierarchies.edit_allowed%TYPE
50 ,displayed hxc_pref_hierarchies.displayed%TYPE
51 ,name hxc_pref_hierarchies.name%TYPE
52 ,top_level_parent_id hxc_pref_hierarchies.top_level_parent_id%TYPE
53 ,code hxc_pref_hierarchies.code%TYPE);
54
55 Type t_pref_node_value is Table of
56 r_pref_node_value
57 Index By BINARY_INTEGER;
58
59 Type r_pref_hier is Record
60 ( Start_Index Number
61 ,Stop_index Number
62 ,caching_time Date);
63
64 Type t_pref_hier is Table of
65 r_pref_hier
66 Index By BINARY_INTEGER; -- Index is pref_hierarchy_id of the topmost parent.
67
68 -- Tables to cache preference values
69 g_pref_hier_ct t_pref_hier;
70 g_pref_values_ct t_pref_node_value;
71
72 -- public types used to manipulate preference information
73
74 TYPE t_pref_table_row IS RECORD
75 ( preference_code hxc_pref_definitions.code%TYPE,
76 attribute1 VARCHAR(150),
77 attribute2 VARCHAR(150),
78 attribute3 VARCHAR(150),
79 attribute4 VARCHAR(150),
80 attribute5 VARCHAR(150),
81 attribute6 VARCHAR(150),
82 attribute7 VARCHAR(150),
83 attribute8 VARCHAR(150),
84 attribute9 VARCHAR(150),
85 attribute10 VARCHAR(150),
86 attribute11 VARCHAR(150),
87 attribute12 VARCHAR(150),
88 attribute13 VARCHAR(150),
89 attribute14 VARCHAR(150),
90 attribute15 VARCHAR(150),
91 attribute16 VARCHAR(150),
92 attribute17 VARCHAR(150),
93 attribute18 VARCHAR(150),
94 attribute19 VARCHAR(150),
95 attribute20 VARCHAR(150),
96 attribute21 VARCHAR(150),
97 attribute22 VARCHAR(150),
98 attribute23 VARCHAR(150),
99 attribute24 VARCHAR(150),
100 attribute25 VARCHAR(150),
101 attribute26 VARCHAR(150),
102 attribute27 VARCHAR(150),
103 attribute28 VARCHAR(150),
104 attribute29 VARCHAR(150),
105 attribute30 VARCHAR(150),
106 start_date DATE,
107 end_date DATE,
108 rule_evaluation_order hxc_resource_rules.rule_evaluation_order%TYPE,
109 edit_allowed hxc_pref_hierarchies.edit_allowed%TYPE,
110 displayed hxc_pref_hierarchies.displayed%TYPE,
111 name hxc_pref_hierarchies.name%TYPE);
112
113 g_maxloop NUMBER := 50000;
114 g_loop_count NUMBER;
115
116 TYPE t_pref_table IS TABLE OF
117 t_pref_table_row
118 INDEX BY BINARY_INTEGER;
119
120 TYPE t_resource_list IS TABLE OF
121 NUMBER(15)
122 INDEX BY BINARY_INTEGER;
123
124 -- global table to support function used in views.
125
126 g_pref_table t_pref_table;
127
128 -- Bulk Preference
129
130 -- Table holding index of pref values table for the corresponding resource_id
131 TYPE r_resource_pref_row IS RECORD
132 ( start_index number,
133 stop_index number);
134
135 TYPE t_resource_pref_table IS TABLE OF
136 r_resource_pref_row
137 INDEX BY BINARY_INTEGER;
138
139 -- Table holding index of pref sets table and pref values table
140 TYPE r_pref_sets_index_row is record
141 ( set_start number,
142 set_stop number,
143 result_start number,
144 result_stop number);
145
146 type t_pref_sets_index_table is table of
147 r_pref_sets_index_row
148 index by binary_integer;
149
150 -- Table holding Pref-REO sets
151 type r_pref_sets_row is record
152 ( reo number,
153 pref_hier_id number);
154
155 type t_pref_sets_table is table of
156 r_pref_sets_row
157 index by binary_integer;
158
159 -- Table holding all the rules associated to the resource_ids being evaluated.
160 type r_resource_elig_row is record
161 ( criteria_id number,
162 pref_hier_id number,
163 reo number
164 );
165
166 type t_resource_elig_table is table of
167 r_resource_elig_row
168 index by binary_integer;
169
170 -- Procedures / functions to get resource preferences
171
172 -- Function to get the value of a attribute of a preference code for a given resource
173 -- Example:
174 -- resource_preferences(10150,'TC_W_TCRD_LAYOUT',1);
175 --
176 FUNCTION resource_preferences(p_resource_id IN NUMBER,
177 p_pref_code in VARCHAR2,
178 p_attribute_n IN NUMBER,
179 p_evaluation_date IN DATE DEFAULT sysdate,
180 p_resp_id IN number default -99) RETURN VARCHAR2;
181
182
183 -- Procedure to get the values of all the attributes of a list of preferences for a given
184 -- resource
185 -- Example:
186 -- resource_preferences(10150,'TC_W_TCRD_LAYOUT,TS_PER_APPLICATION_SET',l_pref_table);
187 --
188 PROCEDURE resource_preferences(p_resource_id IN NUMBER,
189 p_pref_code_list in VARCHAR2,
190 p_pref_table IN OUT NOCOPY t_pref_table,
191 p_evaluation_date IN DATE DEFAULT sysdate,
192 p_resp_id IN NUMBER DEFAULT -99);
193
194
195 -- Procedure to get the value of all the attributes of all the preferences for a given
196 -- resource
197 -- Example:
198 -- resource_preferences(10150,l_pref_table,sysdate,-1,-1);
199 --
200 PROCEDURE resource_preferences(p_resource_id IN NUMBER,
201 p_pref_table IN OUT NOCOPY t_pref_table,
202 p_evaluation_date IN DATE DEFAULT sysdate,
203 p_user_id IN number DEFAULT fnd_global.user_id,
204 p_resp_id IN number DEFAULT -99,
205 p_ignore_user_id in BOOLEAN default FALSE,
206 p_ignore_resp_id in BOOLEAN default FALSE);
207
208 -- Bulk evaluation of preferences - calculates preferences for a list of resource_ids
209 -- this is a single date evaluation
210 -- does not consider responsibility and login based preferences.
211
212 procedure resource_prefs_bulk ( p_evaluation_date in date,
213 p_pref_table IN OUT NOCOPY t_pref_table,
214 p_resource_pref_table IN OUT NOCOPY t_resource_pref_table,
215 p_resource_sql in varchar2 );
216
217
218
219
220 -- Procedure to load global g_pref_table with preferences
221
222 PROCEDURE set_resource_preferences(p_resource_id IN NUMBER,
223 p_evaluation_date IN DATE DEFAULT sysdate );
224
225 -- same for range based prefs.
226 PROCEDURE set_resource_preferences(p_resource_id IN NUMBER,
227 p_start_evaluation_date DATE,
228 p_end_evaluation_date DATE);
229
230 -- Procedure to get preferences from the global g_pref_table. (Without recalculation)
231
232 FUNCTION get_resource_preferences(p_resource_id IN NUMBER,
233 p_pref_id IN NUMBER,
234 p_attn in VARCHAR2) RETURN VARCHAR2;
235
236
237 -- 1) Procedure to get the values of specified codes and attributes for a given resource.
238 -- Result is returned as a string.
239 -- Example:
240 -- resource_preferences(10150,'TC_W_TCRD_LAYOUT|1|2|3|,TS_PER_APPLICATION_SET|1|20|13|5|,
241 -- TC_W_TCRD_DISPLAY_DAYS|1|2|3|4|5|6|7|'));
242 -- This brings back the following string:
243 -- 3|4|5|1||||N|Y|Y|Y|Y|Y|N
244 --
245
246 FUNCTION resource_preferences(p_resource_id IN NUMBER,
247 p_pref_spec_list IN VARCHAR2,
248 p_evaluation_date IN DATE DEFAULT sysdate) RETURN VARCHAR2;
249
250 -- Same as 1), but allows you to specify the output separator
251 -- Named separately since this without default looks like the above function (ignoring the date/varchar
252 -- difference)
253
254 FUNCTION resource_pref_sep(p_resource_id IN NUMBER,
255 p_pref_spec_list IN VARCHAR2,
256 p_output_separator IN VARCHAR2,
257 p_evaluation_date IN DATE DEFAULT sysdate) RETURN VARCHAR2;
258
259 -- Further modification of 1) that will return a fatal error to calling code, letting the
260 -- caller handle the error. This is useful in Oracle Reports (where calling prefs from a
261 -- formula.
262
263 FUNCTION resource_pref_errcode(p_resource_id IN NUMBER,
264 p_pref_spec_list IN VARCHAR2,
265 p_message IN OUT NOCOPY VARCHAR,
266 p_evaluation_date IN DATE DEFAULT sysdate) RETURN VARCHAR2;
267
268 ----
269 -- Procedure to get the values of all the attributes of all the preferences for a
270 -- range of dates.
271 ----
272
273 PROCEDURE resource_preferences(p_resource_id in NUMBER,
274 p_start_evaluation_date DATE,
275 p_end_evaluation_date DATE,
276 p_pref_table IN OUT NOCOPY t_pref_table,
277 p_no_prefs_outside_asg IN BOOLEAN DEFAULT FALSE,
278 p_resp_id IN number default -99,
279 p_resp_appl_id IN NUMBER DEFAULT fnd_global.resp_appl_id,
280 p_ignore_resp_id in boolean default false);
281
282 -- Same as above but allows specification of preference code.
283 -- This filters the pref table for the specified preference and
284 -- sorts the preferences by start_date in ascending order.
285 -- (first item being the earliest date and so on).
286 -- The table produced is contiguous and starts at index 1
287 --
288 -- It also caches the whole preference table and only clears the cache when
289 --
290 -- i) the p_clear_cache param is TRUE
291 -- ii) the p_resource_id does not match the resource id in the cache
292 -- iii) the resource id matches but the evaluation dates do not
293
294 PROCEDURE resource_preferences(p_resource_id in NUMBER,
295 p_preference_code IN VARCHAR2,
296 p_start_evaluation_date DATE,
297 p_end_evaluation_date DATE,
298 p_sorted_pref_table IN OUT NOCOPY t_pref_table,
299 p_clear_cache BOOLEAN DEFAULT FALSE,
300 p_no_prefs_outside_asg IN BOOLEAN DEFAULT FALSE);
301
302 -- This is the overloaded version of the above procedure which allows
303 -- the user to pass their preference table. In this case the passed
304 -- pref table is substitued for the cached pref table
305
306 PROCEDURE resource_preferences(p_resource_id in NUMBER,
307 p_preference_code IN VARCHAR2,
308 p_start_evaluation_date DATE,
309 p_end_evaluation_date DATE,
313
310 p_sorted_pref_table IN OUT NOCOPY t_pref_table,
311 p_clear_cache BOOLEAN DEFAULT FALSE,
312 p_master_pref_table t_pref_table );
314 -- this clears the pref table cache when preference evaluation is finished
315 -- to allow memory saving.
316
317 PROCEDURE clear_sort_pref_table_cache;
318
319 ----
320 -- Supporting function to allow inquiries as to whether specific values have been used
321 -- in preference hierarchies. Useful for data integrity checking.
322 ----
323
324 FUNCTION num_hierarchy_occurances(p_preference_code IN VARCHAR2,
325 p_attributen IN NUMBER,
326 p_value IN VARCHAR2) RETURN NUMBER;
327
328 FUNCTION migration_mode
329 RETURN BOOLEAN;
330
331 PROCEDURE set_migration_mode (p_migration_mode IN BOOLEAN);
332
333 FUNCTION employment_ended (
334 p_person_id per_all_people_f.person_id%TYPE,
335 p_effective_date per_all_assignments_f.effective_start_date%TYPE
336 DEFAULT SYSDATE
337 )
338 RETURN BOOLEAN;
339
340 FUNCTION assignment_last_eff_dt (
344 )
341 p_person_id per_all_people_f.person_id%TYPE,
342 p_effective_date per_all_assignments_f.effective_start_date%TYPE
343 DEFAULT SYSDATE
345 RETURN per_all_assignments_f.effective_start_date%TYPE;
346
347 FUNCTION evaluation_date (
348 p_resource_id hxc_time_building_blocks.resource_id%TYPE,
349 p_evaluation_date DATE
350 )
351 RETURN DATE;
352
353 FUNCTION check_number(
354 p_string varchar2
355 )
356 RETURN number ;
357
358 FUNCTION return_version_id (
359 p_criteria hxc_resource_rules.eligibility_criteria_id%TYPE,
360 p_eligibility_type hxc_resource_rules.eligibility_criteria_type%TYPE
361 )
362 RETURN NUMBER;
363
364 FUNCTION get_tc_resp ( p_resource_id NUMBER,
365 p_evaluation_date DATE)
366 RETURN NUMBER;
367
368 PROCEDURE get_tc_resp ( p_resource_id IN NUMBER,
369 p_start_evaluation_date IN DATE,
370 p_end_evaluation_date IN DATE,
371 p_resp_id OUT NOCOPY NUMBER,
372 p_resp_appl_id OUT NOCOPY NUMBER) ;
373
374 PROCEDURE get_tc_resp ( p_resource_id IN NUMBER,
375 p_start_evaluation_date IN DATE,
376 p_end_evaluation_date IN DATE,
377 p_resplist OUT NOCOPY resplisttab ) ;
378
379
380 FUNCTION resource_preferences(p_resource_id IN NUMBER,
381 p_pref_code IN VARCHAR2,
382 p_attribute_n IN NUMBER,
383 p_resp_id IN NUMBER )
384 RETURN VARCHAR2;
385
386 END hxc_preference_evaluation;
[Home] [Help]