[Home] [Help]
PACKAGE BODY: APPS.HRI_BPL_REC_PIPLN
Source
1 PACKAGE BODY hri_bpl_rec_pipln AS
2 /* $Header: hribrec.pkb 120.1.12000000.2 2007/04/12 12:07:17 smohapat noship $ */
3
4 TYPE g_status_rec_type IS RECORD
5 (stage_code VARCHAR2(30),
6 event_code VARCHAR2(30),
7 event_seq NUMBER);
8
9 TYPE g_event_rec_type IS RECORD
10 (stage_code VARCHAR2(30),
11 event_seq PLS_INTEGER);
12
13 TYPE g_status_tab_type IS TABLE OF g_status_rec_type
14 INDEX BY BINARY_INTEGER;
15
16 TYPE g_event_tab_type IS TABLE OF g_event_rec_type
17 INDEX BY VARCHAR2(30);
18
19 TYPE g_term_cache_tab_type IS TABLE OF varchar2(30) INDEX BY VARCHAR2(30);
20
21
22 g_status_formula_id NUMBER;
23 g_term_formula_id NUMBER;
24 g_status_cache g_status_tab_type;
25 g_term_cache_tab g_term_cache_tab_type;
26 g_event_cache g_event_tab_type;
27 g_cache_loaded BOOLEAN;
28
29
30 -- -----------------------------------------------------------------------------
31 -- Calculates event details for status type and caches them
32 -- -----------------------------------------------------------------------------
33 PROCEDURE cache_event_info
34 (p_system_status IN VARCHAR2,
35 p_user_status IN VARCHAR2,
36 p_status_id IN NUMBER,
37 p_stage_code OUT NOCOPY VARCHAR2,
38 p_event_code OUT NOCOPY VARCHAR2,
39 p_event_seq OUT NOCOPY NUMBER) IS
40
41 l_formula_input_tab hri_bpl_fast_formula_util.formula_param_type;
42 l_formula_output_tab hri_bpl_fast_formula_util.formula_param_type;
43 l_skip_event VARCHAR2(30);
44
45 BEGIN
46
47 -- Initialize formula input parameters
48 l_formula_input_tab('SYSTEM_ASG_STATUS') := p_system_status;
49 l_formula_input_tab('USER_ASG_STATUS') := p_user_status;
50
51 -- Extract outputs
52 BEGIN
53
54 -- Run formula
55 hri_bpl_fast_formula_util.run_formula
56 (p_formula_id => g_status_formula_id,
57 p_input_tab => l_formula_input_tab,
58 p_output_tab => l_formula_output_tab);
59
60 -- Set output values
61 p_stage_code := l_formula_output_tab('STAGE_CODE');
62 p_event_code := l_formula_output_tab('EVENT_CODE');
63 p_event_seq := l_formula_output_tab('EVENT_SEQ');
64 l_skip_event := l_formula_output_tab('SKIP_EVENT');
65
66 -- Trap exception if formula does not exists, or errors
67 EXCEPTION WHEN OTHERS THEN
68 null;
69 END;
70
71 -- If event is NULL (formula has not executed successfully), or
72 -- Event is unassigned but user has not chosen to skip it
73 -- then apply default classification
74 IF (p_event_code IS NULL OR
75 (p_event_code = 'NA_EDW' AND
76 l_skip_event = 'N')) THEN
77
78 IF p_system_status = 'ACTIVE_APL' THEN
79 p_event_code := 'APPL_STRT';
80 ELSIF p_system_status = 'INTERVIEW1' THEN
81 p_event_code := 'ASMT_INT1';
82 ELSIF p_system_status = 'INTERVIEW2' THEN
83 p_event_code := 'ASMT_INT2';
84 ELSIF p_system_status = 'OFFER' THEN
85 p_event_code := 'OFFR_EXTD';
86 ELSIF p_system_status = 'ACCEPTED' THEN
87 p_event_code := 'OFFR_ACPT';
88 ELSIF p_system_status = 'TERM_APL' THEN
89 p_event_code := 'APPL_TERM_INIT';
90 ELSE
91 p_event_code := 'NA_EDW';
92 END IF;
93
94 p_stage_code := g_event_cache(p_event_code).stage_code;
95 p_event_seq := g_event_cache(p_event_code).event_seq;
96
97 END IF;
98
99 -- Update Caches
100 g_status_cache(p_status_id).stage_code := p_stage_code;
101 g_status_cache(p_status_id).event_code := p_event_code;
102 g_status_cache(p_status_id).event_seq := p_event_seq;
103
104 -- Do not override seeded information
105 IF NOT g_event_cache.EXISTS(p_event_code) THEN
106 g_event_cache(p_event_code).stage_code := p_stage_code;
107 g_event_cache(p_event_code).event_seq := p_event_seq;
108 END IF;
109
110 END cache_event_info;
111
112
113 -- -----------------------------------------------------------------------------
114 -- Returns stage code
115 -- -----------------------------------------------------------------------------
116 FUNCTION get_stage_code
117 (p_system_status IN VARCHAR2,
118 p_user_status IN VARCHAR2,
119 p_status_id IN NUMBER)
120 RETURN VARCHAR2 IS
121
122 l_stage_code VARCHAR2(30);
123 l_event_code VARCHAR2(30);
124 l_event_seq NUMBER;
125
126 BEGIN
127
128 IF NOT g_status_cache.EXISTS(p_status_id) THEN
129
130 cache_event_info
131 (p_system_status => p_system_status,
132 p_user_status => p_user_status,
133 p_status_id => p_status_id,
134 p_stage_code => l_stage_code,
135 p_event_code => l_event_code,
136 p_event_seq => l_event_seq);
137
138 END IF;
139
140 RETURN g_status_cache(p_status_id).stage_code;
141
142 EXCEPTION WHEN OTHERS THEN
143
144 RETURN 'NON_PIPLN_STG';
145
146 END get_stage_code;
147
148
149 -- -----------------------------------------------------------------------------
150 -- Returns event code
151 -- -----------------------------------------------------------------------------
152 FUNCTION get_event_code
153 (p_system_status IN VARCHAR2,
154 p_user_status IN VARCHAR2,
155 p_status_id IN NUMBER)
156 RETURN VARCHAR2 IS
157
158 l_stage_code VARCHAR2(30);
159 l_event_code VARCHAR2(30);
160 l_event_seq NUMBER;
161
162 BEGIN
163
164 IF NOT g_status_cache.EXISTS(p_status_id) THEN
165
166 cache_event_info
167 (p_system_status => p_system_status,
168 p_user_status => p_user_status,
169 p_status_id => p_status_id,
170 p_stage_code => l_stage_code,
171 p_event_code => l_event_code,
172 p_event_seq => l_event_seq);
173
174 END IF;
175
176 RETURN g_status_cache(p_status_id).event_code;
177
178 EXCEPTION WHEN OTHERS THEN
179
180 RETURN 'NA_EDW';
181
182 END get_event_code;
183
184
185 -- -----------------------------------------------------------------------------
186 -- Loads event information cache
187 -- -----------------------------------------------------------------------------
188 PROCEDURE load_event_cache IS
189
190 CURSOR status_csr IS
191 SELECT
192 per_system_status system_status
193 ,assignment_status_type_id status_id
194 ,user_status user_status
195 FROM per_assignment_status_types
196 WHERE per_system_status IN
197 ('ACTIVE_APL','INTERVIEW1','INTERVIEW2','OFFER','ACCEPTED','TERM_APL');
198
199 l_stage_code VARCHAR2(30);
200 l_event_code VARCHAR2(30);
201 l_event_seq PLS_INTEGER;
202
203 BEGIN
204
205 FOR status_rec IN status_csr LOOP
206
207 cache_event_info
208 (p_system_status => status_rec.system_status,
209 p_user_status => status_rec.user_status,
210 p_status_id => status_rec.status_id,
211 p_stage_code => l_stage_code,
212 p_event_code => l_event_code,
213 p_event_seq => l_event_seq);
214
215 END LOOP;
216
217 END load_event_cache;
218
219
220 -- -----------------------------------------------------------------------------
221 -- Returns stage code for event
222 -- -----------------------------------------------------------------------------
223 FUNCTION get_stage_code(p_event_code IN VARCHAR2)
224 RETURN VARCHAR2 IS
225
226 BEGIN
227
228 IF NOT g_event_cache.EXISTS(p_event_code) AND
229 NOT g_cache_loaded THEN
230 load_event_cache;
231 END IF;
232
233 IF NOT g_event_cache.EXISTS(p_event_code) THEN
234 g_event_cache(p_event_code).stage_code := 'NON_PIPLN_STG';
235 g_event_cache(p_event_code).event_seq := -1;
236 END IF;
237
238 RETURN g_event_cache(p_event_code).stage_code;
239
240 END get_stage_code;
241
242
243 -- -----------------------------------------------------------------------------
244 -- Returns event sequence for event
245 -- -----------------------------------------------------------------------------
246 FUNCTION get_event_seq(p_event_code IN VARCHAR2)
247 RETURN NUMBER IS
248
249 BEGIN
250
251 IF NOT g_event_cache.EXISTS(p_event_code) AND
252 NOT g_cache_loaded THEN
253 load_event_cache;
254 END IF;
255
256 IF NOT g_event_cache.EXISTS(p_event_code) THEN
257 g_event_cache(p_event_code).stage_code := 'NON_PIPLN_STG';
258 g_event_cache(p_event_code).event_seq := -1;
259 END IF;
260
261 RETURN g_event_cache(p_event_code).event_seq;
262
263 EXCEPTION WHEN OTHERS THEN
264
265 RETURN -1;
266
267 END get_event_seq;
268
269
270 -- -----------------------------------------------------------------------------
271 -- Returns termination type (Voluntary or Involuntary) for an application
272 -- termination reason
273 -- -----------------------------------------------------------------------------
274 FUNCTION get_appl_term_type(p_appl_term_rsn IN VARCHAR2) RETURN VARCHAR2 IS
275
276 l_formula_input_tab hri_bpl_fast_formula_util.formula_param_type;
277 l_formula_output_tab hri_bpl_fast_formula_util.formula_param_type;
278
279 l_term_type varchar2(10);
280
281 BEGIN
282
283 -- Calculate from FF if cache is empty
284 IF NOT (g_term_cache_tab.exists(p_appl_term_rsn)) then
285
286 -- Initialize formula input parameters
287 l_formula_input_tab('TERMINATION_REASON') := p_appl_term_rsn;
288
289 -- Trap exceptions if formula fails or doesn't exist
290 BEGIN
291
292 -- Run formula
293 hri_bpl_fast_formula_util.run_formula
294 (p_formula_id => g_term_formula_id,
295 p_input_tab => l_formula_input_tab,
296 p_output_tab => l_formula_output_tab);
297
298 -- Set output values
299 l_term_type := l_formula_output_tab('TERMINATION_TYPE');
300
301 EXCEPTION WHEN OTHERS THEN
302
303 -- Default to Involuntary
304 l_term_type := 'I';
305
306 END;
307
308 -- cache return value
309 g_term_cache_tab(p_appl_term_rsn) := l_term_type;
310
311 ELSE
312
313 -- get cache value
314 l_term_type := g_term_cache_tab(p_appl_term_rsn);
315
316 END IF;
317
318 -- Return termination type
319 RETURN l_term_type;
320
321 END get_appl_term_type;
322
323
324 -- -----------------------------------------------------------------------------
325 -- Initialization
326 -- -----------------------------------------------------------------------------
327 BEGIN
328
329 g_event_cache('VAC_OPEN').stage_code := 'VAC_OPEN_STG';
330 g_event_cache('APPL_STRT').stage_code := 'INIT_APPL_STG';
331 g_event_cache('ASMT_STRT').stage_code := 'ASMT_STG';
332 g_event_cache('ASMT_INT1').stage_code := 'ASMT_STG';
333 g_event_cache('ASMT_INT2').stage_code := 'ASMT_STG';
334 g_event_cache('OFFR_EXTD').stage_code := 'OFFR_EXTD_STG';
335 g_event_cache('OFFR_ACPT').stage_code := 'STRT_PNDG_STG';
336 g_event_cache('APPL_TERM_INIT').stage_code := 'APPL_TERM_STG';
337 g_event_cache('APPL_TERM_ASMT').stage_code := 'APPL_TERM_STG';
338 g_event_cache('APPL_TERM_OFFR').stage_code := 'APPL_TERM_STG';
339 g_event_cache('APPL_TERM_ACPT').stage_code := 'APPL_TERM_STG';
340 g_event_cache('EMPL_HIRE').stage_code := 'HIRE_STG';
341 g_event_cache('EMPL_APR1').stage_code := 'HIRE_STG';
342 g_event_cache('EMPL_LOW1_END').stage_code := 'HIRE_STG';
343 g_event_cache('EMPL_TERM').stage_code := 'HIRE_STG';
344 g_event_cache('NA_EDW').stage_code := 'NON_PIPLN_STG';
345 g_event_cache('VAC_OPEN').event_seq := 100;
346 g_event_cache('APPL_STRT').event_seq := 200;
347 g_event_cache('ASMT_STRT').event_seq := 300;
348 g_event_cache('ASMT_INT1').event_seq := 400;
349 g_event_cache('ASMT_INT2').event_seq := 500;
350 g_event_cache('OFFR_EXTD').event_seq := 600;
351 g_event_cache('OFFR_ACPT').event_seq := 700;
352 g_event_cache('APPL_TERM_INIT').event_seq := 800;
353 g_event_cache('APPL_TERM_ASMT').event_seq := 900;
354 g_event_cache('APPL_TERM_OFFR').event_seq := 1000;
355 g_event_cache('APPL_TERM_ACPT').event_seq := 1100;
356 g_event_cache('EMPL_HIRE').event_seq := 1200;
357 g_event_cache('EMPL_APR1').event_seq := 1300;
358 g_event_cache('EMPL_LOW1_END').event_seq := 1400;
359 g_event_cache('EMPL_TERM').event_seq := 1500;
360 g_event_cache('NA_EDW').event_seq := -1;
361 g_status_formula_id := hri_bpl_fast_formula_util.fetch_setup_formula_id
362 (p_formula_name => 'HRI_MAP_REC_APPL_STATUS');
363 g_term_formula_id := hri_bpl_fast_formula_util.fetch_setup_formula_id
364 (p_formula_name => 'HRI_MAP_REC_APPL_TERM_TYPE');
365 g_cache_loaded := FALSE;
366
367 END hri_bpl_rec_pipln;