eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HRI_OPL_WRKFC_EVT_TYPE

Source


1 PACKAGE BODY hri_opl_wrkfc_evt_type AS
2 /* $Header: hriowevtdim.pkb 120.1.12000000.2 2007/04/12 13:23:03 smohapat noship $ */
3 
4 TYPE g_varchar2_idx_tab_type IS TABLE OF NUMBER INDEX BY VARCHAR2(240);
5 
6 g_evtypcmb_cache_tab      g_varchar2_idx_tab_type;
7 
8 -- -----------------------------------------------------------------------------
9 -- Truncates event combination table
10 -- -----------------------------------------------------------------------------
11 PROCEDURE truncate_evtypcmb_table IS
12 
13   l_dummy1        VARCHAR2(2000);
14   l_dummy2        VARCHAR2(2000);
15   l_schema        VARCHAR2(400);
16 
17 BEGIN
18 
19   -- Get HRI schema name - get_app_info populates l_schema
20   IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
21     EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_CL_WEVT_EVTYPCMB_CT';
22   END IF;
23 
24   -- Insert -1 row
25   INSERT INTO hri_cl_wevt_evtypcmb_ct
26    (etcb_evtypcmb_pk
27    ,etcb_evtypcmb_code
28    ,etcb_assgnmnt_chng_flag_code
29    ,etcb_salary_chng_flag_code
30    ,etcb_prfrtng_chng_flag_code
31    ,etcb_perfband_chng_flag_code
32    ,etcb_powband_chng_flag_code
33    ,etcb_hdc_gain_flag_code
34    ,etcb_hdc_loss_flag_code
35    ,etcb_hdc_chng_flag_code
36    ,etcb_fte_gain_flag_code
37    ,etcb_fte_loss_flag_code
38    ,etcb_fte_chng_flag_code
39    ,etcb_grd_chng_flag_code
40    ,etcb_job_chng_flag_code
41    ,etcb_pos_chng_flag_code
42    ,etcb_loc_chng_flag_code
43    ,etcb_org_chng_flag_code
44    ,etcb_mgrh_chng_flag_code
45    ,etcb_hire_flag_code
46    ,etcb_asg_start_flag_code
47    ,etcb_hire_or_start_flag_code
48    ,etcb_term_or_end_flag_code
49    ,etcb_term_vol_flag_code
50    ,etcb_term_invol_flag_code
51    ,etcb_term_flag_code
52    ,etcb_asg_end_flag_code
53    ,etcb_start_sspnsn_flag_code
54    ,etcb_end_sspnsn_flag_code
55    ,etcb_prmtn_flag_code)
56     VALUES
57      (-1
58      ,'N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N-N'
59      ,'N'
60      ,'N'
61      ,'N'
62      ,'N'
63      ,'N'
64      ,'N'
65      ,'N'
66      ,'N'
67      ,'N'
68      ,'N'
69      ,'N'
70      ,'N'
71      ,'N'
72      ,'N'
73      ,'N'
74      ,'N'
75      ,'N'
76      ,'N'
77      ,'N'
78      ,'N'
79      ,'N'
80      ,'N'
81      ,'N'
82      ,'N'
83      ,'N'
84      ,'N'
85      ,'N'
86      ,'N');
87 
88 END truncate_evtypcmb_table;
89 
90 -- -----------------------------------------------------------------------------
91 -- Translates an indicator (1/0) to a flag_code (Y/N)
92 -- -----------------------------------------------------------------------------
93 PROCEDURE ind_to_flag_code(p_indicator   IN NUMBER,
94                       p_flag_code        OUT NOCOPY VARCHAR2) IS
95 BEGIN
96 
97   IF (p_indicator = 1) THEN
98     p_flag_code := 'Y';
99   ELSE
100     p_flag_code := 'N';
101   END IF;
102 
103 END ind_to_flag_code;
104 
105 
106 -- -----------------------------------------------------------------------------
107 -- Inserts event combination record
108 -- -----------------------------------------------------------------------------
109 FUNCTION insert_evtypcmb(p_event_rec    IN evtypcmb_rec_type)
110            RETURN NUMBER IS
111 
112   l_evtypcmb_fk    NUMBER;
113 
114 BEGIN
115 
116   -- Get next sequence value
117   SELECT hri_cl_wevt_evtypcmb_ct_s.nextval
118   INTO l_evtypcmb_fk
119   FROM dual;
120 
121   -- Insert row
122   INSERT INTO hri_cl_wevt_evtypcmb_ct
123    (etcb_evtypcmb_pk
124    ,etcb_evtypcmb_code
125    ,etcb_assgnmnt_chng_flag_code
126    ,etcb_salary_chng_flag_code
127    ,etcb_prfrtng_chng_flag_code
128    ,etcb_perfband_chng_flag_code
129    ,etcb_powband_chng_flag_code
130    ,etcb_hdc_gain_flag_code
131    ,etcb_hdc_loss_flag_code
132    ,etcb_hdc_chng_flag_code
133    ,etcb_fte_gain_flag_code
134    ,etcb_fte_loss_flag_code
135    ,etcb_fte_chng_flag_code
136    ,etcb_grd_chng_flag_code
137    ,etcb_job_chng_flag_code
138    ,etcb_pos_chng_flag_code
139    ,etcb_loc_chng_flag_code
140    ,etcb_org_chng_flag_code
141    ,etcb_mgrh_chng_flag_code
142    ,etcb_hire_flag_code
143    ,etcb_asg_start_flag_code
144    ,etcb_hire_or_start_flag_code
145    ,etcb_term_or_end_flag_code
146    ,etcb_term_vol_flag_code
147    ,etcb_term_invol_flag_code
148    ,etcb_term_flag_code
149    ,etcb_asg_end_flag_code
150    ,etcb_start_sspnsn_flag_code
151    ,etcb_end_sspnsn_flag_code
152    ,etcb_prmtn_flag_code)
153     VALUES
154      (l_evtypcmb_fk
155      ,p_event_rec.evtypcmb_code
156      ,p_event_rec.assgnmnt_chng_flag_code
157      ,p_event_rec.salary_chng_flag_code
158      ,p_event_rec.prfrtng_chng_flag_code
159      ,p_event_rec.perfband_chng_flag_code
160      ,p_event_rec.powband_chng_flag_code
161      ,p_event_rec.hdc_gain_flag_code
162      ,p_event_rec.hdc_loss_flag_code
163      ,p_event_rec.hdc_chng_flag_code
164      ,p_event_rec.fte_gain_flag_code
165      ,p_event_rec.fte_loss_flag_code
166      ,p_event_rec.fte_chng_flag_code
167      ,p_event_rec.grd_chng_flag_code
168      ,p_event_rec.job_chng_flag_code
169      ,p_event_rec.pos_chng_flag_code
170      ,p_event_rec.loc_chng_flag_code
171      ,p_event_rec.org_chng_flag_code
172      ,p_event_rec.mgrh_chng_flag_code
173      ,p_event_rec.hire_flag_code
174      ,p_event_rec.asg_start_flag_code
175      ,p_event_rec.hire_or_start_flag_code
176      ,p_event_rec.term_or_end_flag_code
177      ,p_event_rec.term_vol_flag_code
178      ,p_event_rec.term_invol_flag_code
179      ,p_event_rec.term_flag_code
180      ,p_event_rec.asg_end_flag_code
181      ,p_event_rec.start_sspnsn_flag_code
182      ,p_event_rec.end_sspnsn_flag_code
183      ,p_event_rec.prmtn_flag_code);
184 
185 END insert_evtypcmb;
186 
187 
188 -- -----------------------------------------------------------------------------
189 -- Converts event combination record to a cache key
190 -- -----------------------------------------------------------------------------
191 FUNCTION get_evtypcmb_code(p_event_rec    IN evtypcmb_rec_type)
192            RETURN VARCHAR2 IS
193 
194   l_evtypcmb_code   VARCHAR2(240);
195 
196 BEGIN
197 
198   l_evtypcmb_code :=
199    p_event_rec.assgnmnt_chng_flag_code || '-' ||
200    p_event_rec.salary_chng_flag_code || '-' ||
201    p_event_rec.prfrtng_chng_flag_code || '-' ||
205    p_event_rec.hdc_loss_flag_code || '-' ||
202    p_event_rec.perfband_chng_flag_code || '-' ||
203    p_event_rec.powband_chng_flag_code || '-' ||
204    p_event_rec.hdc_gain_flag_code || '-' ||
206    p_event_rec.hdc_chng_flag_code || '-' ||
207    p_event_rec.fte_gain_flag_code || '-' ||
208    p_event_rec.fte_loss_flag_code || '-' ||
209    p_event_rec.fte_chng_flag_code || '-' ||
210    p_event_rec.grd_chng_flag_code || '-' ||
211    p_event_rec.job_chng_flag_code || '-' ||
212    p_event_rec.pos_chng_flag_code || '-' ||
213    p_event_rec.loc_chng_flag_code || '-' ||
214    p_event_rec.org_chng_flag_code || '-' ||
215    p_event_rec.mgrh_chng_flag_code || '-' ||
216    p_event_rec.hire_flag_code || '-' ||
217    p_event_rec.asg_start_flag_code || '-' ||
218    p_event_rec.hire_or_start_flag_code || '-' ||
219    p_event_rec.term_or_end_flag_code || '-' ||
220    p_event_rec.term_vol_flag_code || '-' ||
221    p_event_rec.term_invol_flag_code || '-' ||
222    p_event_rec.term_flag_code || '-' ||
223    p_event_rec.asg_end_flag_code || '-' ||
224    p_event_rec.start_sspnsn_flag_code || '-' ||
225    p_event_rec.end_sspnsn_flag_code || '-' ||
226    p_event_rec.prmtn_flag_code;
227 
228   RETURN l_evtypcmb_code;
229 
230 END get_evtypcmb_code;
231 
232 
233 -- -----------------------------------------------------------------------------
234 -- Lookups up event combination record
235 -- -----------------------------------------------------------------------------
236 FUNCTION lookup_evtypcmb_fk(p_event_rec    IN evtypcmb_rec_type)
237            RETURN NUMBER IS
238 
239   CURSOR evtypcmb_csr(v_evtypcmb_code  IN VARCHAR2) IS
240   SELECT etcb_evtypcmb_pk
241   FROM hri_cl_wevt_evtypcmb_ct
242   WHERE etcb_evtypcmb_code = v_evtypcmb_code;
243 
244   l_evtypcmb_fk    NUMBER;
245 
246 BEGIN
247 
248   -- Test whether combination key is already in cache
249   BEGIN
250 
251     -- Get value from cache
252     l_evtypcmb_fk := g_evtypcmb_cache_tab(p_event_rec.evtypcmb_code);
253 
254   -- Exception raised when cache miss
255   EXCEPTION WHEN OTHERS THEN
256 
257     -- Test whether combination exists in table
258     OPEN evtypcmb_csr(p_event_rec.evtypcmb_code);
259     FETCH evtypcmb_csr INTO l_evtypcmb_fk;
260     CLOSE evtypcmb_csr;
261 
262     -- If combination exists in table then set cache
263     IF (l_evtypcmb_fk IS NOT NULL) THEN
264       g_evtypcmb_cache_tab(p_event_rec.evtypcmb_code) := l_evtypcmb_fk;
265     END IF;
266 
267   END;
268 
269   RETURN l_evtypcmb_fk;
270 
271 END lookup_evtypcmb_fk;
272 
273 
274 -- -----------------------------------------------------------------------------
275 -- Main function to lookup, insert if necessary, and return key for event
276 -- combination.
277 -- -----------------------------------------------------------------------------
278 FUNCTION get_evtypcmb_fk(p_event_rec    IN evtypcmb_rec_type)
279            RETURN NUMBER IS
280 
281   l_evtypcmb_fk    NUMBER;
282 
283 BEGIN
284 
285   -- First lookup event combination to see if it already exists
286   l_evtypcmb_fk := lookup_evtypcmb_fk
287                          (p_event_rec => p_event_rec);
288 
289   -- Attempt insert of event combination if lookup failed
290   IF (l_evtypcmb_fk IS NULL) THEN
291 
292     -- Trap exception that might occur if a simultaneous insert occurs
296       l_evtypcmb_fk := insert_evtypcmb
293     -- by another thread
294     BEGIN
295 
297                              (p_event_rec => p_event_rec);
298 
299     EXCEPTION WHEN OTHERS THEN
300 
301       -- Retry lookup
302       l_evtypcmb_fk := lookup_evtypcmb_fk
303                              (p_event_rec => p_event_rec);
304 
305     END;
306 
307   END IF;
308 
309   RETURN l_evtypcmb_fk;
310 
311 END get_evtypcmb_fk;
312 
313 
314 -- -----------------------------------------------------------------------------
315 -- Entry point to function to lookup, insert if necessary, and return key
316 -- for event combination.
317 -- -----------------------------------------------------------------------------
318 FUNCTION get_evtypcmb_fk
319    (p_assignment_change_ind    IN NUMBER
320    ,p_salary_change_ind        IN NUMBER
321    ,p_perf_rating_change_ind   IN NUMBER
322    ,p_perf_band_change_ind     IN NUMBER
323    ,p_pow_band_change_ind      IN NUMBER
324    ,p_headcount_gain_ind       IN NUMBER
325    ,p_headcount_loss_ind       IN NUMBER
326    ,p_fte_gain_ind             IN NUMBER
327    ,p_fte_loss_ind             IN NUMBER
328    ,p_grade_change_ind         IN NUMBER
329    ,p_job_change_ind           IN NUMBER
330    ,p_position_change_ind      IN NUMBER
331    ,p_location_change_ind      IN NUMBER
332    ,p_organization_change_ind  IN NUMBER
333    ,p_supervisor_change_ind    IN NUMBER
334    ,p_worker_hire_ind          IN NUMBER
335    ,p_post_hire_asgn_start_ind IN NUMBER
336    ,p_pre_sprtn_asgn_end_ind   IN NUMBER
337    ,p_term_voluntary_ind       IN NUMBER
338    ,p_term_involuntary_ind     IN NUMBER
339    ,p_worker_term_ind          IN NUMBER
340    ,p_start_sspnsn_ind         IN NUMBER
341    ,p_end_sspnsn_ind           IN NUMBER
342    ,p_promotion_ind            IN NUMBER)
343         RETURN NUMBER IS
344 
345   l_evtypcmb_rec      evtypcmb_rec_type;
346   l_hire_or_start_ind      NUMBER;
347   l_term_or_end_ind        NUMBER;
348   l_hdc_chng_ind           NUMBER;
349   l_fte_chng_ind           NUMBER;
350   l_evtypcmb_fk            NUMBER;
351 
352 BEGIN
353 
354   -- Set derived indicators
355   l_hire_or_start_ind := p_worker_hire_ind + p_post_hire_asgn_start_ind;
356   l_term_or_end_ind   := p_worker_term_ind + p_pre_sprtn_asgn_end_ind;
357   l_hdc_chng_ind      := p_headcount_gain_ind + p_headcount_loss_ind;
358   l_fte_chng_ind      := p_fte_gain_ind + p_fte_loss_ind;
359 
360   -- Translate indicators to event combination record
361   ind_to_flag_code(p_assignment_change_ind,    l_evtypcmb_rec.assgnmnt_chng_flag_code);
362   ind_to_flag_code(p_salary_change_ind,        l_evtypcmb_rec.salary_chng_flag_code);
363   ind_to_flag_code(p_perf_rating_change_ind,   l_evtypcmb_rec.prfrtng_chng_flag_code);
364   ind_to_flag_code(p_perf_band_change_ind,     l_evtypcmb_rec.perfband_chng_flag_code);
365   ind_to_flag_code(p_pow_band_change_ind,      l_evtypcmb_rec.powband_chng_flag_code);
366   ind_to_flag_code(p_headcount_gain_ind,       l_evtypcmb_rec.hdc_gain_flag_code);
367   ind_to_flag_code(p_headcount_loss_ind,       l_evtypcmb_rec.hdc_loss_flag_code);
368   ind_to_flag_code(l_hdc_chng_ind,             l_evtypcmb_rec.hdc_chng_flag_code);
372   ind_to_flag_code(p_grade_change_ind,         l_evtypcmb_rec.grd_chng_flag_code);
369   ind_to_flag_code(p_fte_gain_ind,             l_evtypcmb_rec.fte_gain_flag_code);
370   ind_to_flag_code(p_fte_loss_ind,             l_evtypcmb_rec.fte_loss_flag_code);
371   ind_to_flag_code(l_fte_chng_ind,             l_evtypcmb_rec.fte_chng_flag_code);
373   ind_to_flag_code(p_job_change_ind,           l_evtypcmb_rec.job_chng_flag_code);
374   ind_to_flag_code(p_position_change_ind,      l_evtypcmb_rec.pos_chng_flag_code);
375   ind_to_flag_code(p_location_change_ind,      l_evtypcmb_rec.loc_chng_flag_code);
376   ind_to_flag_code(p_organization_change_ind,  l_evtypcmb_rec.org_chng_flag_code);
377   ind_to_flag_code(p_supervisor_change_ind,    l_evtypcmb_rec.mgrh_chng_flag_code);
378   ind_to_flag_code(p_worker_hire_ind,          l_evtypcmb_rec.hire_flag_code);
379   ind_to_flag_code(p_post_hire_asgn_start_ind, l_evtypcmb_rec.asg_start_flag_code);
380   ind_to_flag_code(l_hire_or_start_ind,        l_evtypcmb_rec.hire_or_start_flag_code);
381   ind_to_flag_code(l_term_or_end_ind,          l_evtypcmb_rec.term_or_end_flag_code);
382   ind_to_flag_code(p_term_voluntary_ind,       l_evtypcmb_rec.term_vol_flag_code);
383   ind_to_flag_code(p_term_involuntary_ind,     l_evtypcmb_rec.term_invol_flag_code);
384   ind_to_flag_code(p_worker_term_ind,          l_evtypcmb_rec.term_flag_code);
385   ind_to_flag_code(p_pre_sprtn_asgn_end_ind,   l_evtypcmb_rec.asg_end_flag_code);
386   ind_to_flag_code(p_start_sspnsn_ind,         l_evtypcmb_rec.start_sspnsn_flag_code);
387   ind_to_flag_code(p_end_sspnsn_ind,           l_evtypcmb_rec.end_sspnsn_flag_code);
391   l_evtypcmb_rec.evtypcmb_code := get_evtypcmb_code
388   ind_to_flag_code(p_promotion_ind,            l_evtypcmb_rec.prmtn_flag_code);
389 
390   -- Set event combination code
392                                    (p_event_rec => l_evtypcmb_rec);
393 
394   -- Call overloaded function with record
395   l_evtypcmb_fk := get_evtypcmb_fk
396                     (p_event_rec => l_evtypcmb_rec);
397 
398   RETURN l_evtypcmb_fk;
399 
400 END get_evtypcmb_fk;
401 
402 END hri_opl_wrkfc_evt_type;