[Home] [Help]
PACKAGE: APPS.BEN_CWB_BACKOUT_POST_PROCESS
Source
1 PACKAGE BEN_CWB_BACKOUT_POST_PROCESS AUTHID CURRENT_USER AS
2 /* $Header: bencwbbp.pkh 120.2.12020000.2 2013/02/04 06:20:22 sgnanama 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 backout compensation workbench postings for all comp.
10 * object for each person.
11 *
12 * Version Date Author Comment
13 * ---------+-----------+---------+----------------------------------------------
14 * 120.0 01-Oct-10 sgnanama ER:5257917
15 * 120.1 10-Jan-11 sgnanama Added param p_is_open_access
16 * 120.2 15-Feb-11 sgnanama send FYI Notification
17 * ==========================================================================+
18 */
19
20 --
21 -- Global Cursors and Global variables.
22 --
23 TYPE g_number_type IS VARRAY (200) OF NUMBER;
24
25 TYPE g_cache_person_process_object IS RECORD (
26 person_id ben_person_actions.person_id%TYPE
27 , person_action_id ben_person_actions.person_action_id%TYPE
28 , object_version_number ben_person_actions.object_version_number%TYPE
29 , per_in_ler_id ben_person_actions.ler_id%TYPE
30 , non_person_cd ben_person_actions.non_person_cd%TYPE
31 );
32
33 TYPE g_exceution_params_rec IS RECORD (
34 persons_selected NUMBER (15) -- PER_SLCTD
35 , persons_proc_succ NUMBER (15) -- PER_PROC_SUCC
36 , persons_errored NUMBER (15) -- PER_ERR
37 , lf_evt_closed NUMBER (15) -- PER_PROC
38 , lf_evt_not_closed NUMBER (15) -- PER_UNPROC
39 , business_group_id NUMBER (15)
40 , benefit_action_id NUMBER (15)
41 , start_date DATE
42 , end_date DATE
43 , start_time VARCHAR (90)
44 , end_time VARCHAR (90)
45 );
46
47 g_exec_param_rec g_exceution_params_rec;
48
49 TYPE g_cwb_rpt_summary_rec IS RECORD (
50 person_id NUMBER (15)
51 , country_code VARCHAR2(30)
52 , person_name VARCHAR2(240)
53 , bg_id NUMBER (15)
54 , bg_name VARCHAR2 (240)
55 , status VARCHAR2 (2)
56 , lf_evt_closed VARCHAR2 (1)
57 , benefit_action_id NUMBER(15)
58 );
59
60 TYPE g_cwb_rpt_person_rec IS RECORD (
61 person_rate_id NUMBER (15)
62 , pl_id NUMBER (15)
63 , person_id NUMBER (15)
64 , group_per_in_ler_id NUMBER (15)
65 , oipl_id NUMBER (15)
66 , group_pl_id NUMBER (15)
67 , group_oipl_id NUMBER (15)
68 , full_name VARCHAR2 (240)
69 , emp_number VARCHAR2 (30)
70 , business_group_name VARCHAR2 (240)
71 , business_group_id NUMBER (15)
72 , manager_name VARCHAR2 (240)
73 , ws_mgr_id NUMBER (15)
74 , pl_name VARCHAR2 (240)
75 , opt_name VARCHAR2 (240)
76 , amount NUMBER
77 , units VARCHAR2 (30)
78 , performance_rating VARCHAR2 (30)
79 , assignment_changed VARCHAR2 (30)
80 , status VARCHAR2 (2)
81 , lf_evt_closed VARCHAR2 (1)
82 , error_or_warning_text VARCHAR2 (2000)
83 , benefit_action_id NUMBER(15)
84 , base_salary_currency VARCHAR2 (30)
85 , currency VARCHAR2 (30)
86 , base_salary NUMBER
87 , elig_salary NUMBER
88 , percent_of_elig_sal NUMBER
89 , base_sal_freq VARCHAR2(30)
90 , pay_ann_factor NUMBER
91 , pl_ann_factor NUMBER
92 , conversion_factor NUMBER
93 , adjusted_amount NUMBER
94 , prev_sal NUMBER
95 , pay_proposal_id NUMBER
96 , pay_basis_id NUMBER
97 , element_entry_id NUMBER
98 , amount_posted NUMBER
99 , exchange_rate NUMBER
100 , effective_date DATE
101 , reason VARCHAR2(240)
102 , eligibility VARCHAR2(30)
103 , fte_factor NUMBER
104 , element_input_value VARCHAR2(80) --sg
105 , ws_sub_acty_typ_cd VARCHAR2(30)
106 , assignment_id NUMBER
107 , element_entry_value_id NUMBER
108 , input_value_id NUMBER
109 , element_type_id NUMBER
110 , eev_screen_entry_value NUMBER
111 , uom_precision NUMBER
112 , posted_rating VARCHAR2(240)
113 , rating_type VARCHAR2(240)
114 , prior_job VARCHAR2(700)
115 , posted_job VARCHAR2(700)
116 , proposed_job VARCHAR2(700)
117 , prior_position VARCHAR2(240)
118 , posted_position VARCHAR2(240)
119 , proposed_position VARCHAR2(240)
120 , prior_grade VARCHAR2(240)
121 , posted_grade VARCHAR2(240)
122 , proposed_grade VARCHAR2(240)
123 , prior_group VARCHAR2(240)
124 , posted_group VARCHAR2(240)
125 , proposed_group VARCHAR2(240)
126 , prior_flex1 VARCHAR2(240)
127 , posted_flex1 VARCHAR2(240)
128 , proposed_flex1 VARCHAR2(240)
129 , prior_flex2 VARCHAR2(240)
130 , posted_flex2 VARCHAR2(240)
131 , proposed_flex2 VARCHAR2(240)
132 , prior_flex3 VARCHAR2(240)
133 , posted_flex3 VARCHAR2(240)
134 , proposed_flex3 VARCHAR2(240)
135 , prior_flex4 VARCHAR2(240)
136 , posted_flex4 VARCHAR2(240)
137 , proposed_flex4 VARCHAR2(240)
138 , prior_flex5 VARCHAR2(240)
139 , posted_flex5 VARCHAR2(240)
140 , proposed_flex5 VARCHAR2(240)
141 , prior_flex6 VARCHAR2(240)
142 , posted_flex6 VARCHAR2(240)
143 , proposed_flex6 VARCHAR2(240)
144 , prior_flex7 VARCHAR2(240)
145 , posted_flex7 VARCHAR2(240)
146 , proposed_flex7 VARCHAR2(240)
147 , prior_flex8 VARCHAR2(240)
148 , posted_flex8 VARCHAR2(240)
149 , proposed_flex8 VARCHAR2(240)
150 , prior_flex9 VARCHAR2(240)
151 , posted_flex9 VARCHAR2(240)
152 , proposed_flex9 VARCHAR2(240)
153 , prior_flex10 VARCHAR2(240)
154 , posted_flex10 VARCHAR2(240)
155 , proposed_flex10 VARCHAR2(240)
156 , prior_flex11 VARCHAR2(240)
157 , posted_flex11 VARCHAR2(240)
158 , proposed_flex11 VARCHAR2(240)
159 , prior_flex12 VARCHAR2(240)
160 , posted_flex12 VARCHAR2(240)
161 , proposed_flex12 VARCHAR2(240)
162 , prior_flex13 VARCHAR2(240)
163 , posted_flex13 VARCHAR2(240)
164 , proposed_flex13 VARCHAR2(240)
165 , prior_flex14 VARCHAR2(240)
166 , posted_flex14 VARCHAR2(240)
167 , proposed_flex14 VARCHAR2(240)
168 , prior_flex15 VARCHAR2(240)
169 , posted_flex15 VARCHAR2(240)
170 , proposed_flex15 VARCHAR2(240)
171 , prior_flex16 VARCHAR2(240)
172 , posted_flex16 VARCHAR2(240)
173 , proposed_flex16 VARCHAR2(240)
174 , prior_flex17 VARCHAR2(240)
175 , posted_flex17 VARCHAR2(240)
176 , proposed_flex17 VARCHAR2(240)
177 , prior_flex18 VARCHAR2(240)
178 , posted_flex18 VARCHAR2(240)
179 , proposed_flex18 VARCHAR2(240)
180 , prior_flex19 VARCHAR2(240)
181 , posted_flex19 VARCHAR2(240)
182 , proposed_flex19 VARCHAR2(240)
183 , prior_flex20 VARCHAR2(240)
184 , posted_flex20 VARCHAR2(240)
185 , proposed_flex20 VARCHAR2(240)
186 , prior_flex21 VARCHAR2(240)
187 , posted_flex21 VARCHAR2(240)
188 , proposed_flex21 VARCHAR2(240)
189 , prior_flex22 VARCHAR2(240)
190 , posted_flex22 VARCHAR2(240)
191 , proposed_flex22 VARCHAR2(240)
192 , prior_flex23 VARCHAR2(240)
193 , posted_flex23 VARCHAR2(240)
194 , proposed_flex23 VARCHAR2(240)
195 , prior_flex24 VARCHAR2(240)
196 , posted_flex24 VARCHAR2(240)
197 , proposed_flex24 VARCHAR2(240)
198 , prior_flex25 VARCHAR2(240)
199 , posted_flex25 VARCHAR2(240)
200 , proposed_flex25 VARCHAR2(240)
201 , prior_flex26 VARCHAR2(240)
202 , posted_flex26 VARCHAR2(240)
203 , proposed_flex26 VARCHAR2(240)
204 , prior_flex27 VARCHAR2(240)
205 , posted_flex27 VARCHAR2(240)
206 , proposed_flex27 VARCHAR2(240)
207 , prior_flex28 VARCHAR2(240)
208 , posted_flex28 VARCHAR2(240)
209 , proposed_flex28 VARCHAR2(240)
210 , prior_flex29 VARCHAR2(240)
211 , posted_flex29 VARCHAR2(240)
212 , proposed_flex29 VARCHAR2(240)
213 , prior_flex30 VARCHAR2(2000)
214 , posted_flex30 VARCHAR2(2000)
215 , proposed_flex30 VARCHAR2(2000)
216 , asgn_change_reason VARCHAR2(240)
217 , pending_workflow VARCHAR2(30)
218 , country_code VARCHAR2(30)
219 , lf_evt_ocrd_date DATE
220 , rating_date DATE
221 , new_sal NUMBER
222 , elmnt_processing_type VARCHAR2(30)
223 , prev_eev_screen_entry_value NUMBER
224 );
225
226 TYPE g_cache_cwb_rpt_person_rec IS TABLE OF g_cwb_rpt_person_rec
227 INDEX BY BINARY_INTEGER;
228
229 TYPE g_cache_cwb_rpt_summary_rec IS TABLE OF g_cwb_rpt_summary_rec
230 INDEX BY BINARY_INTEGER;
231
232
233 TYPE g_cache_person_process_rec IS TABLE OF g_cache_person_process_object
234 INDEX BY BINARY_INTEGER;
235
236 TYPE g_cache_group_options_rec IS TABLE OF VARCHAR2 (240)
237 INDEX BY BINARY_INTEGER;
238
239 TYPE g_cache_actual_plans_rec IS TABLE OF VARCHAR2 (240)
240 INDEX BY BINARY_INTEGER;
241
242 TYPE g_cache_actual_options_rec IS TABLE OF VARCHAR2 (240)
243 INDEX BY BINARY_INTEGER;
244
245 g_group_plan_name VARCHAR2 (240);
246 g_is_force_on_per VARCHAR2 (30);
247 g_cwb_rpt_person g_cwb_rpt_person_rec;
248 g_cache_cwb_rpt_person g_cache_cwb_rpt_person_rec;
249 g_cache_cwb_sum_person g_cache_cwb_rpt_summary_rec;
250 g_cache_person_process g_cache_person_process_rec;
251 g_cache_group_options g_cache_group_options_rec;
252 g_cache_actual_plans g_cache_actual_plans_rec;
253 g_cache_actual_options g_cache_actual_options_rec;
254 g_is_cwb_component_plan VARCHAR2 (30);
255
256 --
257 -- *************************************************************************
258 -- * << Procedure: Process >>
259 -- *************************************************************************
260 --
261 PROCEDURE process (
262 errbuf OUT NOCOPY VARCHAR2
263 , retcode OUT NOCOPY NUMBER
264 , p_effective_date IN VARCHAR2
265 , p_validate IN VARCHAR2
266 , p_pl_id IN NUMBER
267 , p_lf_evt_orcd_date IN VARCHAR2
268 , p_person_id IN NUMBER DEFAULT NULL
269 , p_manager_id IN NUMBER DEFAULT NULL
270 , p_employees_in_bg IN NUMBER DEFAULT NULL
271 , p_audit_log IN VARCHAR2 DEFAULT 'N'
272 , p_hidden_audit_log IN VARCHAR2
273 , p_debug_level IN VARCHAR2 DEFAULT 'L'
274 , p_bg_id IN NUMBER
275 , p_is_multi_thread IN VARCHAR2 DEFAULT 'Y'
276 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
277 , p_person_selection_rule_id IN NUMBER DEFAULT NULL
278 , p_is_backout_perf IN VARCHAR2 DEFAULT 'N'
279 , p_local_plan_list IN VARCHAR2 DEFAULT NULL
280 , p_is_open_access IN VARCHAR2 DEFAULT 'N'
281 , p_send_fyi IN VARCHAR2 DEFAULT 'N'
282 );
283
284 PROCEDURE do_multithread (
285 errbuf OUT NOCOPY VARCHAR2
286 , retcode OUT NOCOPY NUMBER
287 , p_validate IN VARCHAR2 DEFAULT 'N'
288 , p_benefit_action_id IN NUMBER
289 , p_thread_id IN NUMBER
290 , p_effective_date IN VARCHAR2
291 , p_audit_log IN VARCHAR2 DEFAULT 'N'
292 , p_is_self_service IN VARCHAR2 DEFAULT 'N'
293 , p_is_backout_perf IN VARCHAR2 DEFAULT 'N'
294 );
295 END;