1 PACKAGE hr_efc_info AUTHID CURRENT_USER AS
2 /* $Header: hrefcinf.pkh 115.6 2002/12/02 18:26:43 apholt noship $ */
3 --
4 -- Exceptions
5 currency_null exception;
6 --
7 -- Globals
8 g_efc_message_line number := 0;
9 g_efc_error_app number := null;
10 g_efc_error_message varchar2(30) := null;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |----------------------------< get_bg >------------------------------------|
14 -- ----------------------------------------------------------------------------
15 --
16 -- Description:
17 -- Returns business_group_id from hr_efc_actions table, for the current
18 -- conversion process with status of 'P'. Note, only 1 conversion process
19 -- can occur at one time, hence there should only be one row with an
20 -- efc_action_status = 'P'.
21 --
22 -- Post Success:
23 -- The business_group_id for the current conversion is returned.
24 --
25 -- Post Failure:
26 -- An appropriate error message is raised.
27 --
28 -- ----------------------------------------------------------------------------
29 FUNCTION get_bg RETURN NUMBER;
30 --
31 -- ----------------------------------------------------------------------------
32 -- |--------------------------< get_bg_currency >-----------------------------|
33 -- ----------------------------------------------------------------------------
34 --
35 -- Description:
36 -- This function returns the currency for a given business_group.
37 --
38 -- Post success:
39 -- The business groups currency is returned.
40 --
41 -- Post Failure:
42 -- An error is raised if a currency cannot be found for the given business
43 -- group.
44 --
45 -- ----------------------------------------------------------------------------
46 FUNCTION get_bg_currency(p_bg NUMBER) RETURN VARCHAR2;
47 --
48 -- ----------------------------------------------------------------------------
49 -- |---------------------------< get_chunk >----------------------------------|
50 -- ----------------------------------------------------------------------------
51 --
52 -- Description:
53 -- Returns the chunk size previously set up by the efc_ask script
54 -- The chunk size is used by the efc scripts to set the commit point.
55 --
56 -- ----------------------------------------------------------------------------
57 FUNCTION get_chunk RETURN NUMBER;
58 --
59 -- ----------------------------------------------------------------------------
60 -- |------------------------< process_table >---------------------------------|
61 -- ----------------------------------------------------------------------------
62 --
63 -- Description:
64 -- Returns 'Y' if the business group currency is an EMU currency at system
65 -- date. This function is only called by scripts generated for those tables
66 -- which are of type BG.
67 --
68 -- ----------------------------------------------------------------------------
69 FUNCTION process_table(p_bg NUMBER) RETURN VARCHAR2;
70 --
71 -- ----------------------------------------------------------------------------
72 -- |-------------------< validate_currency_code >-----------------------------|
73 -- ----------------------------------------------------------------------------
74 --
75 -- Description:
76 -- This function takes a currency code as input, and then checks whether or
77 -- not the currency is an EMU currency. If so, the function will return
78 -- 'EMU', otherwise the function will return the currency code unchanged.
79 --
80 -- Parameters:
81 -- p_currency_code - the currency code to check
82 --
83 -- ----------------------------------------------------------------------------
84 FUNCTION validate_currency_code
85 (p_currency_code in VARCHAR2) RETURN varchar2;
86 --
87 -- ----------------------------------------------------------------------------
88 -- |---------------------< convert_abs_information >--------------------------|
89 -- ----------------------------------------------------------------------------
90 --
91 -- Description:
92 -- This function is specific to the PER_ABSENCE_ATTENDANCES table, and will
93 -- convert the amount either on a currency on the table, or against the
94 -- business_group's currency.
95 --
96 -- ----------------------------------------------------------------------------
97 FUNCTION convert_abs_information
98 (p_value IN varchar2
99 ,p_currency IN varchar2
100 ,p_bg IN number) RETURN varchar2;
101 --
102 -- ----------------------------------------------------------------------------
103 -- |---------------------< convert_aei_information >--------------------------|
104 -- ----------------------------------------------------------------------------
105 --
106 -- Description:
107 -- This function is specific to the PER_ASSIGNMENT_EXTRA_INFO table, and will
108 -- convert the amount either on a currency on the table, or against the
109 -- business_group's currency.
110 --
111 -- ----------------------------------------------------------------------------
112 FUNCTION convert_aei_information
113 (p_value IN varchar2
114 ,p_currency IN varchar2
115 ,p_bg IN number) RETURN varchar2;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |------------------------< convert_num_value >-----------------------------|
119 -- ----------------------------------------------------------------------------
120 --
121 -- Description:
122 -- This function is specific to the HR_SUMMARY table, and will
123 -- convert the amount against the business_group's currency.
124 --
125 -- ----------------------------------------------------------------------------
126 FUNCTION convert_num_value
127 (p_value IN VARCHAR2
128 ,p_bg IN NUMBER
129 ,p_context1 IN VARCHAR2
130 ,p_context2 IN VARCHAR2) RETURN varchar2;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |--------------------------< convert_ppy_value >---------------------------|
134 -- ----------------------------------------------------------------------------
135 --
136 -- Description:
137 -- This function is specific to the PAY_PRE_PAYMENTS table, and will convert
138 -- the amount against the given currency.
139 --
140 -- ----------------------------------------------------------------------------
141 FUNCTION convert_ppy_value
142 (p_value IN number
143 ,p_currency IN varchar2) RETURN number;
144 --
145 -- ----------------------------------------------------------------------------
146 -- |-----------------------< validate_total_workers >-------------------------|
147 -- ----------------------------------------------------------------------------
148 --
149 -- Description:
150 -- For a specific table, within a specific business group, this procedure
151 -- ensures that an insert/update is done using the same no. of total workers
152 -- as previously specified.
153 -- Also checks that no phases are running which should be complete.
154 -- ie. before executing a stage 20 process, all stage 10 processes should be
155 -- complete (assuming they have begun).
156 -- There is an assumption that all components are started with the same
157 -- number of workers.
158 --
159 -- ----------------------------------------------------------------------------
160 PROCEDURE validate_total_workers(p_action_id IN number
161 ,p_component_name IN varchar2
162 ,p_sub_step IN number
163 ,p_total_workers IN number
164 ,p_step IN varchar2 default 'C_UPDATE'
165 );
166 --
167 -- ----------------------------------------------------------------------------
168 -- |-----------------------< get_action_details >-----------------------------|
169 -- ----------------------------------------------------------------------------
170 --
171 -- Description:
172 -- This procedure queries the HR_EFC_ACTIONS table, and returns values likely
173 -- to be used by other processes.
174 --
175 -- ----------------------------------------------------------------------------
176 PROCEDURE get_action_details(p_efc_action_id OUT NOCOPY number
177 ,p_business_group_id OUT NOCOPY number
178 ,p_get_chunk OUT NOCOPY number
179 );
180 --
181 -- ----------------------------------------------------------------------------
182 -- |---------------------------< insert_line >--------------------------------|
183 -- ----------------------------------------------------------------------------
184 --
185 -- Description:
186 -- Adds the line to the actual database table.
187 --
188 -- ----------------------------------------------------------------------------
189 PROCEDURE insert_line(p_line VARCHAR2
190 ,p_line_num NUMBER default null);
191 -- ----------------------------------------------------------------------------
192 -- |-------------------------< add_output >-----------------------------------|
193 -- ----------------------------------------------------------------------------
194 --
195 -- Description:
196 -- Adds output to a specific table, for generating a report at a later date.
197 --
198 -- ----------------------------------------------------------------------------
199 PROCEDURE add_output(p_param1 IN VARCHAR2
200 ,p_param2 IN VARCHAR2
201 ,p_param3 IN VARCHAR2
202 ,p_param4 IN VARCHAR2
203 ,p_line IN OUT NOCOPY NUMBER);
204 --
205 -- ----------------------------------------------------------------------------
206 -- |-------------------------< add_header >-----------------------------------|
207 -- ----------------------------------------------------------------------------
208 --
209 -- Description:
210 -- Adds a header to the report table.
211 --
212 -- ----------------------------------------------------------------------------
213 PROCEDURE add_header(p_line IN OUT NOCOPY NUMBER);
214 --
215 -- ----------------------------------------------------------------------------
216 -- |-------------------------< validate_hr_summary >--------------------------|
217 -- ----------------------------------------------------------------------------
218 --
219 -- Description:
220 -- Validates the column values of teh HR_SUMMARY table.
221 --
222 -- ----------------------------------------------------------------------------
223 FUNCTION validate_hr_summary(p_colname VARCHAR2
224 ,p_item VARCHAR2
225 ,p_business_group_id NUMBER) RETURN VARCHAR2;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |-----------------------< chk_mapping_exists >-----------------------------|
229 -- ----------------------------------------------------------------------------
230 --
231 -- Description:
232 -- For payment types, checks whether a mapping exists for a particular
233 -- payment_type_id.
234 --
235 -- Returns 'Y' or 'N' depending on whether a mapping exists or not.
236 --
237 -- ----------------------------------------------------------------------------
238 FUNCTION chk_mapping_exists(p_payment_type_id IN number) RETURN varchar2;
239 --
240 -- ----------------------------------------------------------------------------
241 -- |---------------------------< find_map_id >--------------------------------|
242 -- ----------------------------------------------------------------------------
243 --
244 -- Description:
245 -- Given a particular payment_type_id, this procedure returns the new
246 -- payment_type_id, to be used after the conversion has taken place.
247 --
248 -- ----------------------------------------------------------------------------
249 FUNCTION find_map_id(p_payment_type_id IN number) RETURN number;
250 --
251 -- ----------------------------------------------------------------------------
252 -- |--------------------< insert_or_select_comp_row >-------------------------|
253 -- ----------------------------------------------------------------------------
254 --
255 -- Description:
256 -- This procedure is called at the start of all update scripts, and will check
257 -- whether or not a row exists in the HR_EFC_PROCESS_COMPONENTS table. If not,
258 -- one will be inserted.
259 -- Implemented using a locking mechanism, so that two scripts starting at an
260 -- identical time will not insert two rows.
261 --
262 -- ----------------------------------------------------------------------------
263 PROCEDURE insert_or_select_comp_row
264 (p_action_id IN number
265 ,p_process_component_name IN varchar2
266 ,p_table_name IN varchar2
267 ,p_total_workers IN number
268 ,p_worker_id IN number
269 ,p_step IN varchar2
270 ,p_sub_step IN number
271 ,p_process_component_id OUT NOCOPY number
272 );
273 --
274 -- ----------------------------------------------------------------------------
275 -- |-----------------< insert_or_select_worker_row >--------------------------|
276 -- ----------------------------------------------------------------------------
277 --
278 -- Description:
279 -- For a specific worker, inserts a row into the HR_EFC_WORKERS table, given
280 -- a specific action and component.
281 -- When the worker_process_status is C, on insert, then the table is not
282 -- being processed.
283 --
284 -- ----------------------------------------------------------------------------
285 PROCEDURE insert_or_select_worker_row
286 (p_efc_worker_id OUT NOCOPY number
287 ,p_status IN OUT NOCOPY varchar2
288 ,p_process_component_id IN number
289 ,p_process_component_name IN varchar2
290 ,p_action_id IN number
291 ,p_worker_number IN number
292 ,p_pk1 IN OUT NOCOPY number
293 ,p_pk2 IN OUT NOCOPY varchar2
294 ,p_pk3 IN OUT NOCOPY varchar2
295 ,p_pk4 IN OUT NOCOPY varchar2
296 ,p_pk5 IN OUT NOCOPY varchar2
297 );
298 --
299 -- ----------------------------------------------------------------------------
300 -- |--------------------------< add_audit_row >-------------------------------|
301 -- ----------------------------------------------------------------------------
302 --
303 -- Description:
304 -- Takes in a series of parameters to do with the column being converted, and
305 -- determines whether or not to write a row to the audit table.
306 --
307 -- ----------------------------------------------------------------------------
308 PROCEDURE add_audit_row (p_worker_id IN number
309 ,p_column_name IN varchar2
310 ,p_old_value IN varchar2
311 ,p_new_value IN varchar2
312 ,p_count IN OUT NOCOPY number
313 ,p_currency IN varchar2
314 ,p_last_curr IN OUT NOCOPY varchar2
315 ,p_commit IN OUT NOCOPY boolean);
316 --
317 -- ----------------------------------------------------------------------------
318 -- |-------------------------< flush_audit_details >--------------------------|
319 -- ----------------------------------------------------------------------------
320 --
321 -- Description:
322 -- Whenever we need to perform a commit, we must flush all counters and
323 -- currency details to the Audits table.
324 --
325 -- ----------------------------------------------------------------------------
326 PROCEDURE flush_audit_details
327 (p_efc_worker_id IN number
328 ,p_count IN OUT NOCOPY number
329 ,p_last_curr IN OUT NOCOPY varchar2
330 ,p_col_name IN varchar2
331 );
332 --
333 --
334 -- ----------------------------------------------------------------------------
335 -- |---------------------------< update_worker_row >--------------------------|
336 -- ----------------------------------------------------------------------------
337 --
338 -- Description:
339 -- For a particular worker entry in HR_EFC_WORKERS, this procedure updates the
340 -- primary key details, so that the EFC conversion process can be restarted.
341 --
342 -- ----------------------------------------------------------------------------
346 ,p_pk3 IN varchar2 default NULL
343 PROCEDURE update_worker_row(p_efc_worker_id IN number
344 ,p_pk1 IN number
345 ,p_pk2 IN varchar2 default NULL
347 ,p_pk4 IN varchar2 default NULL
348 ,p_pk5 IN varchar2 default NULL
349 );
350 --
351 --
352 -- ----------------------------------------------------------------------------
353 -- |-------------------------< complete_worker_row >--------------------------|
354 -- ----------------------------------------------------------------------------
355 --
356 -- Description:
357 -- For a particular worker entry in HR_EFC_WORKERS, this process sets the
358 -- status to 'C' to show that the worker has now completed the conversion of
359 -- its rows.
360 -- ----------------------------------------------------------------------------
361 PROCEDURE complete_worker_row(p_efc_worker_id IN number
362 ,p_pk1 IN number
363 ,p_pk2 IN varchar2 default null
364 ,p_pk3 IN varchar2 default null
365 ,p_pk4 IN varchar2 default null
366 ,p_pk5 IN varchar2 default null
367 );
368 --
369 --
370 -- ----------------------------------------------------------------------------
371 -- |---------------------------< check_abs_currency >-------------------------|
372 -- ----------------------------------------------------------------------------
373 --
374 -- Description:
375 -- This routine looks at the possible currencies for a particular column,
376 -- and returns the value of the currency which is actually used for the
377 -- conversion process. ie. if currency1 is null, we use currency2 to convert.
378 -- ----------------------------------------------------------------------------
379 FUNCTION check_abs_currency(p_currency IN varchar2
380 ,p_bg IN number) RETURN varchar2;
381 --
382 -- ----------------------------------------------------------------------------
383 -- |---------------------------< check_aei_currency >-------------------------|
384 -- ----------------------------------------------------------------------------
385 --
386 -- Description:
387 -- This routine looks at the possible currencies for a particular column,
388 -- and returns the value of the currency which is actually used for the
389 -- conversion process. ie. if currency1 is null, we use currency2 to convert.
390 -- ----------------------------------------------------------------------------
391 FUNCTION check_aei_currency(p_currency IN varchar2
392 ,p_bg IN number) RETURN varchar2;
393 --
394 -- ----------------------------------------------------------------------------
395 -- |------------------------< check_ppy_currency >----------------------------|
396 -- ----------------------------------------------------------------------------
397 --
398 -- Description:
399 -- Returns the currency used by the particular column in the PAY_PRE_PAYMENTS
400 -- table.
401 --
402 -- ----------------------------------------------------------------------------
403 FUNCTION check_ppy_currency(p_currency IN varchar2) RETURN varchar2;
404 --
405 -- ----------------------------------------------------------------------------
406 -- |-------------------------< check_num_currency >---------------------------|
407 -- ----------------------------------------------------------------------------
408 --
409 -- Description:
410 --
411 -- For the HR_SUMMARY table, returns the currency against which the columns
412 -- will be converted. Currently, this is the BG's currency.
413 --
414 -- ----------------------------------------------------------------------------
415 FUNCTION check_num_currency(p_bg IN NUMBER) RETURN varchar2;
416 --
417 -- ----------------------------------------------------------------------------
418 -- |-------------------------< check_opm_currency >---------------------------|
419 -- ----------------------------------------------------------------------------
420 --
421 -- Description:
422 --
423 -- Returns the currency used in the conversion of the column in
424 -- PAY_ORG_PAYMENT_METHODS_F.
425 --
426 -- ----------------------------------------------------------------------------
427 FUNCTION check_opm_currency(p_currency IN varchar2) RETURN varchar2;
428 --
429 -- ----------------------------------------------------------------------------
430 -- |-------------------------< check_pra_currency >---------------------------|
431 -- ----------------------------------------------------------------------------
432 --
433 -- Description:
434 --
435 -- Returns the currency used in the conversion of the column in
436 -- PAY_PAYROLL_ACTIONS.
437 --
438 -- ----------------------------------------------------------------------------
439 FUNCTION check_pra_currency(p_currency IN varchar2) RETURN varchar2;
440 --
441 -- ----------------------------------------------------------------------------
442 -- |------------------------< valid_budget_unit >-----------------------------|
443 -- ----------------------------------------------------------------------------
444 --
445 -- Description:
446 --
447 -- Validates the the unit of measure for the particular budget has either been
448 -- seeded by Oracle, or is customer specific, and thus indicates a money
449 -- amount.
450 --
451 -- ----------------------------------------------------------------------------
452 FUNCTION valid_budget_unit(p_uom IN VARCHAR2
453 ,p_business_group_id IN NUMBER) RETURN VARCHAR2;
454 --
455 -- ----------------------------------------------------------------------------
456 -- |----------------------< delete_action_history >---------------------------|
457 -- ----------------------------------------------------------------------------
458 --
459 -- Description:
460 -- This procedure is called by a script, some time after the conversion
461 -- process is complete. Given a certain efc_action_id, this script will delete
462 -- corresponding rows from all _efc tables, and also from
463 -- hr_efc_rounding_errors table.
464 --
465 -- Dynamic SQL is used to execute the generated SQL.
466 --
467 -- Data is not removed from pay_balance_types_efc and
468 -- pay_org_payment_methods_f_efc. When the pre_payments rounding error
469 -- adjustment script is executed in a future conversion run it may need to find
470 -- out which currency was used when the pre-payments process was originally
471 -- ran. This can occur when the pre-payment VALUE currency is different to
472 -- the BASE_CURRENCY_VALUE currency. This information is required as it can
473 -- affect rounding adjustments corrected and when exchange rate differences
474 -- should not be changed.
475 --
476 -- ----------------------------------------------------------------------------
477 PROCEDURE delete_action_history;
478 --
479 -- ----------------------------------------------------------------------------
480 -- |-----------------------< insert_rounding_row >----------------------------|
481 -- ----------------------------------------------------------------------------
482 --
483 -- Description:
484 -- This procedure is called during the running of the rounding scripts
485 -- and inserts rows into HR_EFC_ROUNDING_ERRORS table.
486 --
487 -- ----------------------------------------------------------------------------
488 PROCEDURE insert_rounding_row (p_action_id IN NUMBER
489 ,p_source_id IN NUMBER
490 ,p_source_table IN VARCHAR2
491 ,p_source_column IN VARCHAR2
492 ,p_rounding_amount IN NUMBER);
493 --
494 -- ----------------------------------------------------------------------------
495 -- |-------------------------< find_row_size >--------------------------------|
496 -- ----------------------------------------------------------------------------
497 --
498 -- Description:
499 -- Determines the size of a row, for a given table, by looking at the column
500 -- definitions for that table in ALL_TAB_COLUMNS.
501 -- Criteria for estimation are:
502 -- - If the column is VARCHAR2, and a currency column, size is 3 bytes
503 -- - If the column is VARCHAR2, size is (length of column)/3 bytes.
504 -- - If the column type is NUMBER, size is (length of column)/2 bytes.
505 --
506 -- ----------------------------------------------------------------------------
507 FUNCTION find_row_size(p_table IN VARCHAR2) return NUMBER;
508 --
509 -- ----------------------------------------------------------------------------
510 -- |--------------------------< clear_efc_report >----------------------------|
511 -- ----------------------------------------------------------------------------
512 --
513 -- Description:
514 -- This procedure is a cover for hr_api_user_hooks_utility.clear_hook_report.
515 --
516 -- ----------------------------------------------------------------------------
517 PROCEDURE clear_efc_report;
518 --
519 --
520 -- ----------------------------------------------------------------------------
521 -- |---------------------< process_cross_bg_data >----------------------------|
522 -- ----------------------------------------------------------------------------
523 --
524 -- Description:
525 -- Determines if data spanning business groups will be converted.
526 -- This will can be overriden by the function cust_process_cross_bg_data.
527 -- By default, the data will be converted, unless overridden.
528 --
529 -- ----------------------------------------------------------------------------
530 FUNCTION process_cross_bg_data RETURN varchar2;
531 --
532 END hr_efc_info;
[Home] [Help]