1 PACKAGE per_cagr_utility_pkg AUTHID CURRENT_USER AS
2 /* $Header: pecgrutl.pkh 115.15 2003/09/03 11:34:37 ynegoro noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |----------------------< convert_uom_to_data_type >------------------------|
6 -- ----------------------------------------------------------------------------
7 --
8 -- Description:
9 --
10 -- Converts a UOM for an entitlement item into one of the following data
11 -- types (Number, Character, Date)
12 --
13 -- Post Success:
14 --
15 -- Post Failure: Error message raised
16 --
17 -- Developer Implementation Notes:
18 -- Internal development use only.
19 --
20 -- Access Status:
21 --
22 FUNCTION convert_uom_to_data_type
23 (p_uom IN per_cagr_entitlement_items.uom%TYPE) RETURN CHAR;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |--------------------------< chk_sql_statement >---------------------------|
27 -- ----------------------------------------------------------------------------
28 --
29 -- Description:
30 --
31 -- Checks that the sql statement passed in is valid. Thsi has been created for
32 -- a form to call when it creates a record group from the value set sql. This
33 -- procedure will check to ensure the sql is valid before the sql is assigned
34 -- to the record group.
35 --
36 -- Post Success:
37 --
38 -- Post Failure: Error message raised
39 --
40 -- Developer Implementation Notes:
41 -- Internal development use only.
42 --
43 -- Access Status:
44 --
45 PROCEDURE chk_sql_statement(p_sql_statement IN VARCHAR2);
46 --
47 -- ----------------------------------------------------------------------------
48 -- |--------------------------< chk_sql_statement >---------------------------|
49 -- ----------------------------------------------------------------------------
50 --
51 -- Description:
52 --
53 -- Returns the name of the eligibility profile for the criteria line
54 -- or the name of the ff for the entitlement
55 --
56 --
57 -- Post Success:
58 --
59 -- Post Failure:
60 --
61 -- Developer Implementation Notes:
62 -- Internal development use only.
63 --
64 -- Access Status:
65 --
66 FUNCTION get_elig_source(p_eligy_prfl_id in NUMBER
67 ,p_formula_id in NUMBER
68 ,p_effective_date in DATE) return VARCHAR2;
69 --
70 -- ----------------------------------------------------------------------------
71 -- |--------------------< multiple_entries_allowed >---------------------------|
72 -- ----------------------------------------------------------------------------
73 --
74 -- Description:
75 --
76 -- Returns TRUE if the element entry id passed into the function has been
77 -- setup to allow mulutiple entries (multiple_entries_allowed_flag = Y). If it
78 -- is not then FALSE is returned from this function.
79 --
80 -- Post Success: Returns TRUE or FALSE
81 --
82 -- Post Failure:
83 --
84 -- Developer Implementation Notes:
85 -- Internal development use only.
86 --
87 -- Access Status:
88 --
89 FUNCTION multiple_entries_allowed
90 (p_element_type_id IN pay_element_types_f.element_type_id%TYPE
91 ,p_effective_date IN DATE) RETURN BOOLEAN;
92 --
93 -- ----------------------------------------------------------------------------
94 -- |------------------------< get_cagr_request_id >----------------------------|
95 -- ----------------------------------------------------------------------------
96 --
97 -- Description:
98 --
99 -- Attemnpts to return the latest cagr_request_id for an assignment on or before
100 -- the effective date so that the user may view logs that relate to the run
101 -- which failed to return results. As there could be multiple requests generated
102 -- on a particular date this function returns the id of the latest request,
103 -- which represents the most recent run on or before the session date.
104 -- Called from PERWSCAR.fmb View_Log_Button.
105 --
106 -- Post Success: Returns cagr_request_id or NULL
107 --
108 -- Post Failure:
109 --
110 -- Developer Implementation Notes:
111 --
112 -- Access Status: Private.
113 --
114 FUNCTION get_cagr_request_id (p_assignment_id in NUMBER
115 ,p_effective_date in DATE) RETURN NUMBER;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |--------------------< set_mode_from_node_name >----------------------------|
119 -- ----------------------------------------------------------------------------
120 --
121 -- Description:
122 --
123 -- Returns the mode the form should run in based on node name
124 -- (as taskflow doesn't support additional form parameters).
125 --
126
127 -- Post Success: Returns mode - NORMAL or RETAINED
128 --
129 -- Post Failure:
130 --
131 -- Developer Implementation Notes:
132 --
133 -- Access Status:
134 --
135 FUNCTION set_mode_from_node_name (p_nav_node_usage_id in NUMBER) RETURN VARCHAR2;
136 --
137 -- ----------------------------------------------------------------------------
138 -- |-----------------------------< plan_name >--------------------------------|
139 -- ----------------------------------------------------------------------------
140 --
141 -- Description:
142 -- Returns the plan_name for the new plan, which is derived from a sequence
143 --
144 -- Post Success
145 --
146 -- Post Failure:
147 --
148 -- Developer Implementation Notes:
149 --
150 -- Access Status:
151 --
152 FUNCTION plan_name RETURN VARCHAR2;
153 --
154 -- ----------------------------------------------------------------------------
155 -- |-----------------------------< option_name >------------------------------|
156 -- ----------------------------------------------------------------------------
157 --
158 -- Description:
159 -- Returns the option_name for the option, which is derived from a sequence
160 --
161 -- Post Success
162 --
163 -- Post Failure:
164 --
165 -- Developer Implementation Notes:
166 --
167 -- Access Status:
168 --
169 FUNCTION option_name RETURN VARCHAR;
170 --
171 -- ----------------------------------------------------------------------------
172 -- |---------------------------< get_next_order_number >-----------------------|
173 -- ----------------------------------------------------------------------------
174 --
175 -- Description:
176 -- Returns the current ordr_num, plus 10, from the ben_oipl_f table for
177 -- the plan id passed in.
178 --
179 -- Post Success
180 --
181 -- Post Failure:
182 --
183 -- Developer Implementation Notes:
184 --
185 -- Access Status:
186 --
187 FUNCTION get_next_order_number(p_pl_id IN ben_oipl_f.pl_id%TYPE) RETURN NUMBER;
188 --
189 -- ----------------------------------------------------------------------------
190 -- |---------------------------< get_column_type >----------------------------|
191 -- ----------------------------------------------------------------------------
192 --
193 -- Description:
194 -- Returns the column_type for an entitlement item. If the entitlement
195 -- item has been defined as an Element Entry then the column type will be
196 -- derived from the input values table. Otherwise, the colum type is derived
197 -- from the entitlement items table.
198 --
199 -- Post Success
200 --
201 -- Post Failure:
202 --
203 -- Developer Implementation Notes:
204 --
205 -- Access Status:
206 --
207 FUNCTION get_column_type
208 (p_cagr_entitlement_item_id IN NUMBER
209 ,p_effective_date IN DATE)
210 RETURN VARCHAR2;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |------------------------------< put_log >-------------------------------|
214 -- ----------------------------------------------------------------------------
215 --
216 -- Description:
217 -- Places log message on stack.
218 --
219 -- Post Success
220 --
221 -- Post Failure:
222 --
223 -- Developer Implementation Notes:
224 --
225 -- Access Status:
226 --
227 PROCEDURE put_log (p_text IN VARCHAR2
228 ,p_priority IN NUMBER default 2);
229
230 -- ----------------------------------------------------------------------------
231 -- |---------------------------< write_log_file >-----------------------------|
232 -- ----------------------------------------------------------------------------
233 --
234 -- Description:
235 -- Writes text held in log stack to PER_CAGR_LOG table,
236 -- and also to host file system via FND_FILE, if g_run_from_SRS.
237 --
238 -- Post Success
239 --
240 -- Post Failure:
241 --
242 -- Developer Implementation Notes:
243 --
244 -- Access Status:
245 --
246 PROCEDURE write_log_file (p_cagr_request_id IN NUMBER);
247 --
248 -- ----------------------------------------------------------------------------
249 -- |-------------------------< log_and_raise_error >--------------------------|
250 -- ----------------------------------------------------------------------------
251 --
252 -- Description:
253 -- Accept an error code, log the error message, and raise the message to the
254 -- calling code.
255 --
256 -- Post Success
257 --
258 -- Post Failure:
259 --
260 -- Developer Implementation Notes:
261 --
262 -- Access Status:
263 --
264 PROCEDURE log_and_raise_error (p_error IN VARCHAR2
265 ,p_cagr_request_id IN NUMBER);
266
267 -- ----------------------------------------------------------------------------
268 -- |----------------------< create_formatted_log_file >---------------------|
269 -- ----------------------------------------------------------------------------
270 --
271 -- Description:
272 -- Accept cagr_request_id to query entries from per_cagr_log table and write a
273 -- log file to the file system.
274 --
275 -- Post Success
276 --
277 -- Post Failure:
278 --
279 -- Developer Implementation Notes:
280 --
281 -- Access Status:
282 --
283 PROCEDURE create_formatted_log_file (p_cagr_request_id IN NUMBER
284 ,p_filepath OUT NOCOPY VARCHAR2);
285
286
287 -- ----------------------------------------------------------------------------
288 -- |------------------------< remove_log_entries >---------------------------|
289 -- ----------------------------------------------------------------------------
290 --
291 -- Description:
292 -- Accept cagr_request_id and delete all records in the per_cagr_log table for
293 -- that foreign key value.
294 --
295 -- Post Success
296 --
297 -- Post Failure:
298 --
299 -- Developer Implementation Notes:
300 --
301 -- Access Status:
302 --
303 PROCEDURE remove_log_entries (p_cagr_request_id IN NUMBER);
304
305
306 -- ----------------------------------------------------------------------------
307 -- |------------------------< create_cagr_request >--------------------------|
308 -- ----------------------------------------------------------------------------
309 --
310 -- Description:
311 -- Accept cagr_request_id to query entries from per_cagr_log table and write a
312 -- log file to the file system.
313 --
314 -- Post Success
315 --
316 -- Post Failure:
317 --
318 -- Developer Implementation Notes:
319 --
320 -- Access Status:
321 --
322 PROCEDURE create_cagr_request (p_process_date IN DATE
323 ,p_operation_mode IN VARCHAR2
324 ,p_business_group_id IN NUMBER
325 ,p_assignment_id IN NUMBER
326 ,p_assignment_set_id IN NUMBER
327 ,p_collective_agreement_id IN NUMBER
328 ,p_collective_agreement_set_id IN NUMBER
329 ,p_payroll_id IN NUMBER
330 ,p_person_id IN NUMBER
331 ,p_entitlement_item_id IN NUMBER
332 ,p_parent_request_id IN NUMBER
333 ,p_commit_flag IN VARCHAR2
334 ,p_denormalise_flag IN VARCHAR2
335 ,p_cagr_request_id OUT NOCOPY NUMBER);
336 --
337 -- ----------------------------------------------------------------------------
338 -- |-----------------------< get_name_from_value_set >--------------------------|
339 -- ----------------------------------------------------------------------------
340 --
341 -- Description:
342 -- Returns the name of the id from the sql in the value set. (overload)
343 --
344 -- Post Success
345 --
346 -- Post Failure:
347 --
348 -- Developer Implementation Notes:
349 --
350 -- Access Status:
351 --
352 FUNCTION get_name_from_value_set
353 (p_flex_value_set_id IN NUMBER
354 ,p_business_group_id IN NUMBER
355 ,p_value IN CHAR) RETURN VARCHAR2;
356
357 --
358 -- ----------------------------------------------------------------------------
359 --
360 -- ----------------------------------------------------------------------------
361 -- |-----------------------< get_name_from_value_set >--------------------------|
362 -- ----------------------------------------------------------------------------
363 --
364 -- Description:
365 -- Returns the name of the id from the sql in the value set.
366 --
367 -- Post Success
368 --
369 -- Post Failure:
370 --
371 -- Developer Implementation Notes:
372 --
373 -- Access Status:
374 --
375 FUNCTION get_name_from_value_set
376 (p_cagr_entitlement_id IN NUMBER
377 ,p_value IN CHAR) RETURN VARCHAR2;
378
379 --
380 -- ----------------------------------------------------------------------------
381 -- |------------------------< get_Sql_from_vset_id >--------------------------|
382 -- ----------------------------------------------------------------------------
383 --
384 -- Description:
385 -- Returns the sql statement from the value set if passed in.
386 --
387 -- Post Success
388 --
389 -- Post Failure:
390 --
391 -- Developer Implementation Notes:
392 --
393 -- Access Status:
394 --
395 FUNCTION get_sql_from_vset_id(p_vset_id IN NUMBER) RETURN VARCHAR2;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |--------------------< get_collective_agreement_id >-----------------------|
399 -- ----------------------------------------------------------------------------
400 --
401 -- Description:
402 -- Returns the collective agreement id or null, for the assignment_id
403 -- and effective_date parameters supplied.
404 --
405 -- Post Success
406 --
407 -- Post Failure:
408 --
409 -- Developer Implementation Notes:
410 --
411 -- Access Status:
412 --
413 FUNCTION get_collective_agreement_id(p_assignment_id IN NUMBER
414 ,p_effective_date IN DATE) RETURN NUMBER;
415 --
416 -- -------------------------------------------------------------------
417 -- |--------------------< populate_current_asg > -----------------------|
418 -- -------------------------------------------------------------------
419 --
420 -- Description:
421 -- Returns the current assignment information for GSP
422 --
423 -- Post Success
424 --
425 -- Post Failure:
426 --
427 -- Developer Implementation Notes:
428 --
429 -- Access Status:
430 --
431 procedure populate_current_asg(
432 p_assignment_id IN NUMBER
433 ,p_sess IN date
434 ,p_grade_ladder_name IN OUT nocopy varchar2
435 ,p_grade_name IN OUT nocopy varchar2
436 ,p_step IN OUT nocopy varchar2
437 ,p_salary IN OUT nocopy varchar2
438 );
439 --
440
441 END per_cagr_utility_pkg;