1 PACKAGE pay_template_ivs AUTHID CURRENT_USER AS
2 /* $Header: pyaddivs.pkh 115.2 2003/04/03 18:03:24 ekim ship $ */
3
4 /*
5 Copyright 1996
6 Oracle Corporation
7 Redwood Shores, California 94065
8 USA
9
10 Filename : pyaddivs.pkh
11
12 Description : API enabling addition of an input value
13 over the lifetime of an EXISTING element type.
14
15 Change History
16 ---------------
17 Date Name Vers Bug No Description
18 ----------- ---------- ----- ------- -----------------------------------
19 05-Aut-1996 hparicha 40.0 Created.
20 10-Jul-1997 mfender 110.1 added error checking.
21 03-Apr-2003 ekim 115.2 gscc warning fix.
22
23
24 ======================================================================
25
26 I. REQUIREMENTS DEFINITION
27
28 An ongoing upgrade issue with US Oracle Payroll has been needing to
29 change, fix, or enhance template Earnings and Deductions which
30 ALREADY EXIST on the customer account - ie. a live account.
31
32 The solution will be to provide the new or changed functionality
33 transparently to the customer - without interruption to normal operation
34 and without (or with minimal) manual intervention from the users.
35
36
37 II. CURRENT DIFFICULTIES, SCOPE DEFINITION
38
39 The major diffuculty with enhancing existing elements is the need to
40 add input values on elements. The API pay_db_pay_setup.create_input_value
41 performs a check preventing addition of an input value if run results exist
42 for the element. This has resulted in requiring the customer to
43 a. Rollback all existing payroll runs which included the existing element, OR
44 b. Create new elements such that the new functionality is enabled, and
45 replacing the use of existing elements with new ones.
46
47 Both options (a) and (b) are less than desirable, passable with
48 beta-customers, but not acceptable for live customers.
49
50 The upgrade solution provided here will enable delivery of new
51 functionality to existing earnings and deductions WITHOUT requiring
52 (a) or (b) from the customer.
53
54 When new functionality is added to an existing earning or deduction,
55 the functionality is enabled and will be operational for any NEW payroll
56 runs and quickpays processed on the customer account. Any existing runs
57 on the customer account are left intact but obviously will not have used
58 the new functionality. This solution/upgrade will enable the new
59 functionality to be operational for existing runs which are rolled back
60 and re-processed. This is handled by the upgrade which will
61 add new functionality over the lifetime of the element.
62
63
64 III. TECHNICAL DETAILS
65
66 The normal template generation code in packages pygenptx.pkb, pyusuidt.pkb,
67 pywatgen.pkb will handle the creation of the following rows for the new
68 input value on deductions templates:
69 PAY_INPUT_VALUES_F
70 PAY_BALANCE_FEEDS_F
71 PAY_FORMULA_RESULT_RULES_F
72 NOTE: These rows can be created at any time, regardless of existing
73 payroll runs on the live account.
74
75 The package pyusuiet.pkb does the same for earnings templates.
76
77 The new New_Input_Value API needs to retrofit [date-effective] rows in
78 the following tables:
79 PAY_LINK_INPUT_VALUES_F
80 PAY_ELEMENT_ENTRY_VALUES_F
81 PAY_RUN_RESULT_VALUES
82 NOTE: These are the rows created for and by payroll runs.
83
84
85 IV. ALGORITHM
86
87 For the element type having a new input value added, we need to find all
88 element links...
89
90 1a. Date-effective Element Links that exist for the element type on which
91 the input value is being added:
92 SELECT pel.element_link_id,
93 pel.effective_start_date,
94 pel.effective_end_date,
95 ...
96 INTO l_element_link_id,
97 l_effective_start_date,
98 l_effective_end_date,
99 ...
100 FROM pay_element_links_f pel,
101 pay_input_values_f piv
102 WHERE piv.input_value_id = p_iv_id -- ie. id of iv being added
103 AND pel.element_type_id = piv.element_type_id
104 ORDER BY pel.effective_start_date;
105
106
107 1b. Create SINGLE date-effective PAY_LINK_INPUT_VALUES_F row...lasting from
108 earliest (min) effective_start_date for element link...to latest (max)
109 eff end date of link...is this ideally the end of time? yes.
110 INSERT INTO pay_link_input_values_f (
111 element_link_id
112 input_value_id,
113 effective_start_date,
114 effective_end_date,
115 ...
116 )
117 VALUES (
118 l_element_link_id,
119 p_iv_id, -- ie. id of iv being added
120 l_effective_start_date,
121 l_effective_end_date,
122 ...
123 );
124
125
126 2a. For each link row found in 1a, need to find date-effective
127 Element Entries that exist for the element link:
128 SELECT pee.element_entry_id,
129 pee.effective_start_date,
130 pee.effective_end_date,
131 ...
132 INTO l_element_entry_id,
133 l_effective_start_date,
134 l_effective_end_date,
135 ...
136 FROM pay_element_entries_f pee
137 WHERE pee.element_link_id = l_element_link_id -- ie. link found in 1a.
138 ORDER BY pee.effective_start_date;
139
140
141 2b. Create date-effective PAY_ELEMENT_ENTRY_VALUES_F row...MULTIPLE ROWS.
142 INSERT INTO pay_element_entry_values_f (
143 element_entry_id
144 input_value_id,
145 effective_start_date,
146 effective_end_date,
147 screen_entry_value,
148 ...
149 )
150 VALUES (
151 l_element_entry_id,
152 p_iv_id, -- ie. id of iv being added
153 l_effective_start_date,
154 l_effective_end_date,
155 l_screen_entry_value, -- ie. = NULL or do we need to handle default
156 values and mandatory input values?
157 ...
158 );
159
160
161 3a. Run Results that exist for the element type on which the input value
162 is being added:
163 SELECT prr.run_result_id
164 INTO l_run_result_id
165 FROM pay_run_results prr
166 AND prr.element_type_id = l_element_type_id -- ie. ele w/new iv.
167 ORDER BY prr.run_result_id;
168
169
170 3b. Create PAY_RUN_RESULT_VALUES row...
171 INSERT INTO pay_run_results (
172 run_result_id,
173 input_value_id,
174 result_value
175 )
176 VALUES (
177 l_run_result_id,
178 p_iv_id, -- ie. id of iv being added
179 l_value -- ie. = NULL
180
181 ======================================================================
182 */
183
184 PROCEDURE chk_input_value(p_element_type_id in number,
185 p_legislation_code in varchar2,
186 p_val_start_date in date,
187 p_val_end_date in date,
188 p_insert_update_flag in varchar2,
189 p_input_value_id in number,
190 p_rowid in varchar2,
191 p_recurring_flag in varchar2,
192 p_mandatory_flag in varchar2,
193 p_hot_default_flag in varchar2,
194 p_standard_link_flag in varchar2,
195 p_classification_type in varchar2,
196 p_name in varchar2,
197 p_uom in varchar2,
198 p_min_value in varchar2,
199 p_max_value in varchar2,
200 p_default_value in varchar2,
201 p_lookup_type in varchar2,
202 p_formula_id in number,
203 p_generate_db_items_flag in varchar2,
204 p_warning_or_error in varchar2);
205
206 PROCEDURE ins_3p_input_values(p_val_start_date in date,
207 p_val_end_date in date,
208 p_element_type_id in number,
209 p_primary_classification_id in number,
210 p_input_value_id in number,
211 p_default_value in varchar2,
212 p_max_value in varchar2,
213 p_min_value in varchar2,
214 p_warning_or_error_flag in varchar2,
215 p_input_value_name in varchar2,
216 p_db_items_flag in varchar2,
217 p_costable_type in varchar2,
218 p_hot_default_flag in varchar2,
219 p_business_group_id in number,
220 p_legislation_code in varchar2,
221 p_startup_mode in varchar2);
222
223 PROCEDURE new_input_value (
224 p_element_type_id in number,
225 p_input_value_id in number,
226 p_costed_flag in varchar2 default 'N',
227 p_default_value in varchar2 default NULL,
228 p_max_value in varchar2 default NULL,
229 p_min_value in varchar2 default NULL,
230 p_warning_or_error in varchar2 default NULL);
231
232 END pay_template_ivs;
[Home] [Help]