[Home] [Help]
PACKAGE: APPS.BEN_CWB_POST_PROCESS
Source
1 PACKAGE BEN_CWB_POST_PROCESS AUTHID CURRENT_USER AS
2 /* $Header: bencwbpp.pkh 120.13.12010000.2 2008/08/05 14:37:57 ubhat ship $ */
3 /* ===========================================================================+
4 * Name
5 * Compensation Workbench Post Process
6 * Purpose
7 * This package is used to check validity of parameters passed in via SRS
8 * or via a PL/SQL function or procedure. This package will make a call
9 * to process compensation workbench enrollment for all comp. object for
10 * each person that their default enrollment date is over due.
11 *
12 * Version Date Author Comment
13 * -------+-----------+---------+----------------------------------------------
14 * 115.0 23-DEC-2002 aprabhak created
15 * 115.1 08-FEB-2002 aprabhak new version
16 * 115.2 08-MAR-2002 aprabhak added the p_audit_log param
17 * to compensation_object
18 * 115.3 11-MAR-2002 aprabhak changed the sequence of the personid in
19 * process routine.
20 * 115.4 25-JUN-2002 aprabhak Corrected the dbdrv comman for bug number
21 * #2429696
22 * 115.5 03-Sep-2002 maagrawa Removed private procedures.
23 * 115.6 02-Jan-04 aprabhak Global Budgeting
24 * 115.8 08-Mar-04 aprabhak Added missing person_name
25 * 115.9 04-Jun-04 aprabhak Corrected the threading issue
26 * 115.10 06-Mar-06 steotia Enhancing logging for new audit report
27 * and logging
28 * 115.11 06-mar-06 steotia same as above
29 * 115.12 22-Mar-06 steotia 5109850: taking in LE ocrd date as varchar2
30 * 115.13 27-Mar-06 steotia added elmnt_processing_type
31 * 115.14 16-Mar-06 steotia 5222874: missing data for recurring element
32 * 115.15 14-Jul-06 steotia Added force close of LE
33 * 115.16 13-Sep-06 steotia 5529091: String buffer overflow
34 * 115.17 20-Sep-2006 steotia 5531065: Using Performance Overrides (but
35 * only if used through SS)
36 * 115.18 04-Mar-2007 steotia 5505775: CWB Enhancement
37 * Introduced Person Selection Rule
38 * 115.19 25-Apr-2007 steotia Closing LE of placeholder mgs also.
39 * 115.21 25-Apr-2007 steotia postings on WS Rate Start Date enabled.
40 * 115.22 27-May-2008 sgnanama 7126872:Added g_is_cwb_component_plan which is
41 * used by salary api to distinguish unapproved
42 * proposal from cwb
43 * 115.23 10-Jun-2008 cakunuru Changed the dbdrv checkfile comment.
44 * ==========================================================================+
45 */
46 --
47 -- Global Cursors and Global variables.
48 --
49 TYPE g_number_type IS VARRAY (200) OF NUMBER;
50
51 TYPE g_cache_person_process_object IS RECORD (
52 person_id ben_person_actions.person_id%TYPE
53 , person_action_id ben_person_actions.person_action_id%TYPE
54 , object_version_number ben_person_actions.object_version_number%TYPE
55 , per_in_ler_id ben_person_actions.ler_id%TYPE
56 , non_person_cd ben_person_actions.non_person_cd%TYPE
57 );
58
59 TYPE g_exceution_params_rec IS RECORD (
60 persons_selected NUMBER (15) -- PER_SLCTD
61 , persons_proc_succ NUMBER (15) -- PER_PROC_SUCC
62 , persons_errored NUMBER (15) -- PER_ERR
63 , lf_evt_closed NUMBER (15) -- PER_PROC
64 , lf_evt_not_closed NUMBER (15) -- PER_UNPROC
65 , business_group_id NUMBER (15)
66 , benefit_action_id NUMBER (15)
67 , start_date DATE
68 , end_date DATE
69 , start_time VARCHAR (90)
70 , end_time VARCHAR (90)
71 );
72
73 g_exec_param_rec g_exceution_params_rec;
74
75 TYPE g_cwb_rpt_summary_rec IS RECORD (
76 person_id NUMBER (15)
77 , country_code VARCHAR2(30)
78 , person_name VARCHAR2(240)
79 , bg_id NUMBER (15)
80 , bg_name VARCHAR2 (240)
81 , status VARCHAR2 (2)
82 , lf_evt_closed VARCHAR2 (1)
83 , benefit_action_id NUMBER(15)
84 );
85
86 TYPE g_cwb_rpt_person_rec IS RECORD (
87 person_rate_id NUMBER (15)
88 , pl_id NUMBER (15)
89 , person_id NUMBER (15)
90 , group_per_in_ler_id NUMBER (15)
91 , oipl_id NUMBER (15)
92 , group_pl_id NUMBER (15)
93 , group_oipl_id NUMBER (15)
94 , full_name VARCHAR2 (240)
95 , emp_number VARCHAR2 (30)
96 , business_group_name VARCHAR2 (240)
97 , business_group_id NUMBER (15)
98 , manager_name VARCHAR2 (240)
99 , ws_mgr_id NUMBER (15)
100 , pl_name VARCHAR2 (240)
101 , opt_name VARCHAR2 (240)
102 , amount NUMBER
103 , units VARCHAR2 (30)
104 , performance_rating VARCHAR2 (30)
105 , assignment_changed VARCHAR2 (30)
106 , status VARCHAR2 (2)
107 , lf_evt_closed VARCHAR2 (1)
108 , error_or_warning_text VARCHAR2 (2000)
109 , benefit_action_id NUMBER(15)
110 , base_salary_currency VARCHAR2 (30)
111 , currency VARCHAR2 (30)
112 , base_salary NUMBER
113 , elig_salary NUMBER
114 , percent_of_elig_sal NUMBER
115 , base_sal_freq VARCHAR2(30)
116 , pay_ann_factor NUMBER
117 , pl_ann_factor NUMBER
118 , conversion_factor NUMBER
119 , adjusted_amount NUMBER
120 , prev_sal NUMBER
121 , pay_proposal_id NUMBER
122 , pay_basis_id NUMBER
123 , element_entry_id NUMBER
124 , amount_posted NUMBER
125 , exchange_rate NUMBER
126 , effective_date DATE
127 , reason VARCHAR2(240)
128 , eligibility VARCHAR2(30)
129 , fte_factor NUMBER
130 , element_input_value VARCHAR2(80) --sg
131 , ws_sub_acty_typ_cd VARCHAR2(30)
132 , assignment_id NUMBER
133 , element_entry_value_id NUMBER
134 , input_value_id NUMBER
135 , element_type_id NUMBER
136 , eev_screen_entry_value NUMBER
137 , uom_precision NUMBER
138 , posted_rating VARCHAR2(240)
139 , rating_type VARCHAR2(240)
140 , prior_job VARCHAR2(700)
141 , posted_job VARCHAR2(700)
142 , proposed_job VARCHAR2(700)
143 , prior_position VARCHAR2(240)
144 , posted_position VARCHAR2(240)
145 , proposed_position VARCHAR2(240)
146 , prior_grade VARCHAR2(240)
147 , posted_grade VARCHAR2(240)
148 , proposed_grade VARCHAR2(240)
149 , prior_group VARCHAR2(240)
150 , posted_group VARCHAR2(240)
151 , proposed_group VARCHAR2(240)
152 , prior_flex1 VARCHAR2(240)
153 , posted_flex1 VARCHAR2(240)
154 , proposed_flex1 VARCHAR2(240)
155 , prior_flex2 VARCHAR2(240)
156 , posted_flex2 VARCHAR2(240)
157 , proposed_flex2 VARCHAR2(240)
158 , prior_flex3 VARCHAR2(240)
159 , posted_flex3 VARCHAR2(240)
160 , proposed_flex3 VARCHAR2(240)
161 , prior_flex4 VARCHAR2(240)
162 , posted_flex4 VARCHAR2(240)
163 , proposed_flex4 VARCHAR2(240)
164 , prior_flex5 VARCHAR2(240)
165 , posted_flex5 VARCHAR2(240)
166 , proposed_flex5 VARCHAR2(240)
167 , prior_flex6 VARCHAR2(240)
168 , posted_flex6 VARCHAR2(240)
169 , proposed_flex6 VARCHAR2(240)
170 , prior_flex7 VARCHAR2(240)
171 , posted_flex7 VARCHAR2(240)
172 , proposed_flex7 VARCHAR2(240)
173 , prior_flex8 VARCHAR2(240)
174 , posted_flex8 VARCHAR2(240)
175 , proposed_flex8 VARCHAR2(240)
176 , prior_flex9 VARCHAR2(240)
177 , posted_flex9 VARCHAR2(240)
178 , proposed_flex9 VARCHAR2(240)
179 , prior_flex10 VARCHAR2(240)
180 , posted_flex10 VARCHAR2(240)
181 , proposed_flex10 VARCHAR2(240)
182 , prior_flex11 VARCHAR2(240)
183 , posted_flex11 VARCHAR2(240)
184 , proposed_flex11 VARCHAR2(240)
185 , prior_flex12 VARCHAR2(240)
186 , posted_flex12 VARCHAR2(240)
187 , proposed_flex12 VARCHAR2(240)
188 , prior_flex13 VARCHAR2(240)
189 , posted_flex13 VARCHAR2(240)
190 , proposed_flex13 VARCHAR2(240)
191 , prior_flex14 VARCHAR2(240)
192 , posted_flex14 VARCHAR2(240)
193 , proposed_flex14 VARCHAR2(240)
194 , prior_flex15 VARCHAR2(240)
195 , posted_flex15 VARCHAR2(240)
196 , proposed_flex15 VARCHAR2(240)
197 , prior_flex16 VARCHAR2(240)
198 , posted_flex16 VARCHAR2(240)
199 , proposed_flex16 VARCHAR2(240)
200 , prior_flex17 VARCHAR2(240)
201 , posted_flex17 VARCHAR2(240)
202 , proposed_flex17 VARCHAR2(240)
203 , prior_flex18 VARCHAR2(240)
204 , posted_flex18 VARCHAR2(240)
205 , proposed_flex18 VARCHAR2(240)
206 , prior_flex19 VARCHAR2(240)
207 , posted_flex19 VARCHAR2(240)
208 , proposed_flex19 VARCHAR2(240)
209 , prior_flex20 VARCHAR2(240)
210 , posted_flex20 VARCHAR2(240)
211 , proposed_flex20 VARCHAR2(240)
212 , prior_flex21 VARCHAR2(240)
213 , posted_flex21 VARCHAR2(240)
214 , proposed_flex21 VARCHAR2(240)
215 , prior_flex22 VARCHAR2(240)
216 , posted_flex22 VARCHAR2(240)
217 , proposed_flex22 VARCHAR2(240)
218 , prior_flex23 VARCHAR2(240)
219 , posted_flex23 VARCHAR2(240)
220 , proposed_flex23 VARCHAR2(240)
221 , prior_flex24 VARCHAR2(240)
222 , posted_flex24 VARCHAR2(240)
223 , proposed_flex24 VARCHAR2(240)
224 , prior_flex25 VARCHAR2(240)
225 , posted_flex25 VARCHAR2(240)
226 , proposed_flex25 VARCHAR2(240)
227 , prior_flex26 VARCHAR2(240)
228 , posted_flex26 VARCHAR2(240)
229 , proposed_flex26 VARCHAR2(240)
230 , prior_flex27 VARCHAR2(240)
231 , posted_flex27 VARCHAR2(240)
232 , proposed_flex27 VARCHAR2(240)
233 , prior_flex28 VARCHAR2(240)
234 , posted_flex28 VARCHAR2(240)
235 , proposed_flex28 VARCHAR2(240)
236 , prior_flex29 VARCHAR2(240)
237 , posted_flex29 VARCHAR2(240)
238 , proposed_flex29 VARCHAR2(240)
239 , prior_flex30 VARCHAR2(2000)
240 , posted_flex30 VARCHAR2(2000)
241 , proposed_flex30 VARCHAR2(2000)
242 , asgn_change_reason VARCHAR2(240)
243 , pending_workflow VARCHAR2(30)
244 , country_code VARCHAR2(30)
245 , lf_evt_ocrd_date DATE
246 , rating_date DATE
247 , new_sal NUMBER
248 , elmnt_processing_type VARCHAR2(30)
249 , prev_eev_screen_entry_value NUMBER
250 );
251
252 TYPE g_cache_cwb_rpt_person_rec IS TABLE OF g_cwb_rpt_person_rec
253 INDEX BY BINARY_INTEGER;
254
255 TYPE g_cache_cwb_rpt_summary_rec IS TABLE OF g_cwb_rpt_summary_rec
256 INDEX BY BINARY_INTEGER;
257
258
259 TYPE g_cache_person_process_rec IS TABLE OF g_cache_person_process_object
260 INDEX BY BINARY_INTEGER;
261
262 TYPE g_cache_group_options_rec IS TABLE OF VARCHAR2 (240)
263 INDEX BY BINARY_INTEGER;
264
265 TYPE g_cache_actual_plans_rec IS TABLE OF VARCHAR2 (240)
266 INDEX BY BINARY_INTEGER;
267
268 TYPE g_cache_actual_options_rec IS TABLE OF VARCHAR2 (240)
269 INDEX BY BINARY_INTEGER;
270
271 g_group_plan_name VARCHAR2 (240);
272 g_is_force_on_per VARCHAR2 (30);
273 g_cwb_rpt_person g_cwb_rpt_person_rec;
274 g_cache_cwb_rpt_person g_cache_cwb_rpt_person_rec;
275 g_cache_cwb_sum_person g_cache_cwb_rpt_summary_rec;
276 g_cache_person_process g_cache_person_process_rec;
277 g_cache_group_options g_cache_group_options_rec;
278 g_cache_actual_plans g_cache_actual_plans_rec;
279 g_cache_actual_options g_cache_actual_options_rec;
280 g_is_cwb_component_plan VARCHAR2 (30);
281
282 --
283 -- *************************************************************************
284 -- * << Procedure: Process >>
285 -- *************************************************************************
286 --
287 PROCEDURE process (
288 errbuf OUT NOCOPY VARCHAR2
289 , retcode OUT NOCOPY NUMBER
290 , p_effective_date IN VARCHAR2
291 , p_validate IN VARCHAR2
292 , p_pl_id IN NUMBER
293 , p_lf_evt_orcd_date IN VARCHAR2
294 , p_person_id IN NUMBER DEFAULT NULL
295 , p_manager_id IN NUMBER DEFAULT NULL
296 , p_employees_in_bg IN NUMBER DEFAULT NULL
297 , p_grant_price_val IN NUMBER DEFAULT NULL
298 , p_audit_log IN VARCHAR2 DEFAULT 'N'
299 , p_hidden_audit_log IN VARCHAR2
300 , p_debug_level IN VARCHAR2 DEFAULT 'L'
301 , p_bg_id IN NUMBER
302 , p_is_multi_thread IN VARCHAR2 DEFAULT 'Y'
303 , p_is_force_on_per IN VARCHAR2 DEFAULT 'N'
304 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
305 , p_person_selection_rule_id IN NUMBER DEFAULT NULL
306 , p_use_rate_start_date IN VARCHAR2 DEFAULT 'N'
307 );
308
309 PROCEDURE do_multithread (
310 errbuf OUT NOCOPY VARCHAR2
311 , retcode OUT NOCOPY NUMBER
312 , p_validate IN VARCHAR2 DEFAULT 'N'
316 , p_audit_log IN VARCHAR2 DEFAULT 'N'
313 , p_benefit_action_id IN NUMBER
314 , p_thread_id IN NUMBER
315 , p_effective_date IN VARCHAR2
317 , p_is_force_on_per IN VARCHAR2 DEFAULT 'N'
318 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
319 , p_use_rate_start_date IN VARCHAR2 DEFAULT 'N'
320 );
321 END;