[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_PERSON_TYPE_CTGRY
Source
1 PACKAGE BODY HRI_OPL_PERSON_TYPE_CTGRY AS
2 /* $Header: hrioptct.pkb 120.5 2006/10/11 15:33:52 jtitmas noship $ */
3
4 /******************************************************************************/
5 /* This package populates the person type category table. This collection */
6 /* process provides the solution to the problems raised in bug 3829100. */
7 /* Customers can restrict the data in dbi based on person types by: */
8 /* */
9 /* 1. Create fast formulas */
10 /* Name = HRI_MAP_PERSON_TYPE */
11 /* FF Type = QuickPaint */
12 /* BG = Global FF defined in the setup bg. */
13 /* Can be overridden by formulas for particular BGs */
14 /* Inputs = USER_PERSON_TYPE, */
15 /* SYSTEM_PERSON_TYPE, */
16 /* PRIMARY_FLAG, */
17 /* EMPLOYMENT_CATEGORY */
18 /* Outputs = INCLUDE_IN_REPORTS (Y/N), */
19 /* WORKER_TYPE (based on lookup HRI_CL_WKTH_WKTYP), */
20 /* WORKER_TYPE_LVL1 (based on lookup HRI_CL_WKTH_LVL1), */
21 /* WORKER_TYPE_LVL2 (based on lookup HRI_CL_WKTH_LVL2), */
22 /* */
23 /* With this formula users will be able to categorize the EMP and CWK system */
24 /* person type for their setup. Furthermore if a certain person type should */
25 /* be excluded from reporting, the INCLUDE_IN_REPORTS output can be set to */
26 /* 'N'. */
27 /******************************************************************************/
28
29 -- Simple table types
30 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
31 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
32
33 -- -----------------------------------------------------------------------------
34 -- Inserts row into concurrent program log
35 -- -----------------------------------------------------------------------------
36 PROCEDURE output(p_text VARCHAR2) IS
37
38 BEGIN
39 HRI_BPL_CONC_LOG.output(p_text);
40 END output;
41
42 -- -----------------------------------------------------------------------------
43 -- Inserts row into concurrent program log if debugging is enabled
44 -- -----------------------------------------------------------------------------
45 PROCEDURE dbg(p_text VARCHAR2) IS
46
47 BEGIN
48 HRI_BPL_CONC_LOG.dbg(p_text);
49 END dbg;
50
51 -- ----------------------------------------------------------------------------
52 -- Runs given sql statement dynamically without raising an exception
53 -- ----------------------------------------------------------------------------
54 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 ) IS
55
56 BEGIN
57 EXECUTE IMMEDIATE p_sql_stmt;
58 EXCEPTION WHEN OTHERS THEN
59
60 dbg('error encountered in running the sql');
61 dbg(p_sql_stmt);
62 dbg(sqlerrm);
63
64 -- Bug 4105868: Collection Diagnostics
65 hri_bpl_conc_log.log_process_info
66 (p_msg_type => 'WARNING'
67 ,p_package_name => 'HRI_OPL_PERSON_TYPE_CTGRY'
68 ,p_msg_group => 'PRSN_TYP_CNGS'
69 ,p_msg_sub_group => 'RUN_SQL_STMT_NOERR'
70 ,p_sql_err_code => SQLCODE
71 ,p_note => SUBSTR(p_sql_stmt, 1, 3900));
72
73 END run_sql_stmt_noerr;
74
75 -- ----------------------------------------------------------------------------
76 -- Inserts records into base level HRI_CS_PRSNTYP_CT
77 -- ----------------------------------------------------------------------------
78 PROCEDURE insert_into_dim_levels(p_full_refresh IN VARCHAR2) IS
79
80 l_current_time DATE := SYSDATE;
81 l_user_id NUMBER := fnd_global.user_id;
82 l_sql_stmt VARCHAR2(32000);
83 l_incr_check VARCHAR2(4000);
84 l_lvl1_incr_check VARCHAR2(4000);
85 l_lvl2_incr_check VARCHAR2(4000);
86
87 BEGIN
88
89 -- Add an incremental check for running in incremental mode
90 IF (p_full_refresh = 'N') THEN
91
92 l_incr_check := '
93 WHERE NOT EXISTS
94 (SELECT null
95 FROM hri_cs_prsntyp_ct dim
96 WHERE dim.person_type_id = pty.person_type_id
97 AND dim.primary_flag_code = pty.primary_flag_code
98 AND dim.employment_category_code = pty.employment_category_code
99 AND dim.assignment_type_code = pty.assignment_type_code)';
100
101 l_lvl1_incr_check := '
102 AND NOT EXISTS
103 (SELECT null
104 FROM hri_cs_wkth_lvl1_ct dim
105 WHERE dim.wkth_lvl1_sk_pk = tab.wkth_lvl1_sk_pk)';
106
107 l_lvl2_incr_check := '
108 AND NOT EXISTS
109 (SELECT null
110 FROM hri_cs_wkth_lvl2_ct dim
111 WHERE dim.wkth_lvl2_sk_pk = tab.wkth_lvl2_sk_pk)';
112
113 END IF;
114
115 -- -----------------------------------------------------------------------------
116 -- Base Level
117 -- -----------------------------------------------------------------------------
118
119 -- Set dynamic SQL
120 l_sql_stmt :=
121 'INSERT INTO hri_cs_prsntyp_ct
122 (prsntyp_sk_pk
123 ,wkth_wktyp_sk_fk
124 ,wkth_lvl1_sk_fk
125 ,wkth_lvl2_sk_fk
126 ,wkth_wktyp_code
127 ,wkth_lvl1_code
128 ,wkth_lvl2_code
129 ,person_type_id
130 ,primary_flag_code
131 ,assignment_type_code
132 ,employment_category_code
133 ,include_flag_code
134 ,last_update_date
135 ,last_updated_by
136 ,last_update_login
137 ,created_by
138 ,creation_date)
139 SELECT
140 hri_cs_prsntyp_ct_s.nextval prsntyp_sk_pk
141 ,pty.wkth_wktyp_sk_fk
142 ,pty.wkth_lvl1_sk_fk
143 ,pty.wkth_lvl2_sk_fk
144 ,pty.wkth_wktyp_code
145 ,pty.wkth_lvl1_code
146 ,pty.wkth_lvl2_code
147 ,pty.person_type_id
148 ,pty.primary_flag_code
149 ,pty.assignment_type_code
150 ,pty.employment_category_code
151 ,pty.include_flag_code
152 ,:l_current_time
153 ,:l_user_id
154 ,:l_user_id
155 ,:l_user_id
156 ,:l_current_time
157 FROM
158 hri_cs_prsntyp_v pty' ||
159 l_incr_check;
160
161 EXECUTE IMMEDIATE l_sql_stmt USING
162 l_current_time,
163 l_user_id,
164 l_user_id,
165 l_user_id,
166 l_current_time;
167
168 -- Insert unassigned row in initial load only
169 IF (p_full_refresh = 'Y') THEN
170
171 INSERT INTO hri_cs_prsntyp_ct
172 (prsntyp_sk_pk
173 ,wkth_wktyp_sk_fk
174 ,wkth_lvl1_sk_fk
175 ,wkth_lvl2_sk_fk
176 ,wkth_wktyp_code
177 ,wkth_lvl1_code
178 ,wkth_lvl2_code
179 ,person_type_id
180 ,primary_flag_code
181 ,assignment_type_code
182 ,employment_category_code
183 ,include_flag_code
184 ,last_update_date
185 ,last_updated_by
186 ,last_update_login
187 ,created_by
188 ,creation_date)
189 SELECT
190 -1
191 ,'NA_EDW'
192 ,'NA_EDW-NA_EDW'
193 ,'NA_EDW-NA_EDW-NA_EDW'
194 ,'NA_EDW'
195 ,'NA_EDW'
196 ,'NA_EDW'
197 ,-1
198 ,'NA_EDW'
199 ,'NA_EDW'
200 ,'NA_EDW'
201 ,'N'
202 ,l_current_time
203 ,l_user_id
204 ,l_user_id
205 ,l_user_id
206 ,l_current_time
207 FROM dual;
208
209 END IF;
210
211 -- -----------------------------------------------------------------------------
212 -- Level 2
213 -- -----------------------------------------------------------------------------
214
215 -- Set dynamic SQL
216 l_sql_stmt :=
217 'INSERT INTO hri_cs_wkth_lvl2_ct
218 (wkth_lvl2_sk_pk
219 ,wkth_lvl1_sk_fk
220 ,wkth_wktyp_sk_fk
221 ,wkth_lvl2_code
222 ,wkth_lvl1_code
223 ,wkth_wktyp_code
224 ,last_update_date
225 ,last_updated_by
226 ,last_update_login
227 ,created_by
228 ,creation_date)
229 SELECT
230 tab.wkth_lvl2_sk_pk
231 ,tab.wkth_lvl1_sk_fk
232 ,tab.wkth_wktyp_sk_fk
233 ,tab.wkth_lvl2_code
234 ,tab.wkth_lvl1_code
235 ,tab.wkth_wktyp_code
236 ,:l_current_time
237 ,:l_user_id
238 ,:l_user_id
239 ,:l_user_id
240 ,:l_current_time
241 FROM
242 (SELECT
243 wktyp.lookup_code || ''-'' || lvl1.lookup_code || ''-'' || lvl2.lookup_code
244 wkth_lvl2_sk_pk
245 ,wktyp.lookup_code || ''-'' || lvl1.lookup_code wkth_lvl1_sk_fk
246 ,wktyp.lookup_code wkth_wktyp_sk_fk
247 ,lvl2.lookup_code wkth_lvl2_code
248 ,lvl1.lookup_code wkth_lvl1_code
249 ,wktyp.lookup_code wkth_wktyp_code
250 FROM
251 hr_standard_lookups lvl2
252 ,hr_standard_lookups lvl1
253 ,hr_standard_lookups wktyp
254 WHERE lvl2.lookup_type = ''HRI_CL_WKTH_LVL2''
255 AND lvl1.lookup_type = ''HRI_CL_WKTH_LVL1''
256 AND wktyp.lookup_type = ''HRI_CL_WKTH_WKTYP''
257 ) tab
258 WHERE EXISTS
259 (SELECT null
260 FROM hri_cs_prsntyp_ct ptyp
261 WHERE ptyp.wkth_lvl2_sk_fk = tab.wkth_lvl2_sk_pk
262 AND ptyp.wkth_lvl1_sk_fk = tab.wkth_lvl1_sk_fk
263 AND ptyp.wkth_wktyp_sk_fk = tab.wkth_wktyp_sk_fk)' ||
264 l_lvl2_incr_check;
265
266 EXECUTE IMMEDIATE l_sql_stmt USING
267 l_current_time,
268 l_user_id,
269 l_user_id,
270 l_user_id,
271 l_current_time;
272
273 -- -----------------------------------------------------------------------------
274 -- Level 1
275 -- -----------------------------------------------------------------------------
276
277 -- Set dynamic SQL
278 l_sql_stmt :=
279 'INSERT INTO hri_cs_wkth_lvl1_ct
280 (wkth_lvl1_sk_pk
281 ,wkth_wktyp_sk_fk
282 ,wkth_lvl1_code
283 ,wkth_wktyp_code
284 ,last_update_date
285 ,last_updated_by
286 ,last_update_login
287 ,created_by
288 ,creation_date)
289 SELECT
290 tab.wkth_lvl1_sk_pk
291 ,tab.wkth_wktyp_sk_fk
292 ,tab.wkth_lvl1_code
293 ,tab.wkth_wktyp_code
294 ,:l_current_time
295 ,:l_user_id
296 ,:l_user_id
297 ,:l_user_id
298 ,:l_current_time
299 FROM
300 (SELECT
301 wktyp.lookup_code || ''-'' || lvl1.lookup_code wkth_lvl1_sk_pk
302 ,wktyp.lookup_code wkth_wktyp_sk_fk
303 ,lvl1.lookup_code wkth_lvl1_code
304 ,wktyp.lookup_code wkth_wktyp_code
305 FROM
306 hr_standard_lookups lvl1
307 ,hr_standard_lookups wktyp
308 WHERE lvl1.lookup_type = ''HRI_CL_WKTH_LVL1''
309 AND wktyp.lookup_type = ''HRI_CL_WKTH_WKTYP''
310 ) tab
311 WHERE EXISTS
312 (SELECT null
313 FROM hri_cs_wkth_lvl2_ct ptyp
314 WHERE ptyp.wkth_lvl1_sk_fk = tab.wkth_lvl1_sk_pk
315 AND ptyp.wkth_wktyp_sk_fk = tab.wkth_wktyp_sk_fk)' ||
316 l_lvl1_incr_check;
317
318 EXECUTE IMMEDIATE l_sql_stmt USING
319 l_current_time,
320 l_user_id,
321 l_user_id,
322 l_user_id,
323 l_current_time;
324
325 END insert_into_dim_levels;
326
327 -- ----------------------------------------------------------------------------
328 -- Deletes records into base level HRI_CS_PRSNTYP_CT
329 -- ----------------------------------------------------------------------------
330 PROCEDURE delete_from_dim_levels(p_full_refresh IN VARCHAR2) IS
331
332 l_hri_schema VARCHAR2(300);
333 l_dummy1 VARCHAR2(2000);
334 l_dummy2 VARCHAR2(2000);
335
336 BEGIN
337
338 IF (p_full_refresh = 'N') THEN
339
340 -- Delete any obsolete person types
341 DELETE FROM hri_cs_prsntyp_ct dim
342 WHERE dim.prsntyp_sk_pk <> -1
343 AND dim.person_type_id NOT IN
344 (SELECT ppt.person_type_id
345 FROM per_person_types ppt
346 WHERE ppt.system_person_type IN ('EMP','CWK'));
347
348 -- Delete any obsolete employment categories
349 DELETE FROM hri_cs_prsntyp_ct dim
350 WHERE dim.employment_category_code <> 'NA_EDW'
351 AND dim.prsntyp_sk_pk <> -1
352 AND dim.employment_category_code NOT IN
353 (SELECT hrl.lookup_code
354 FROM hr_standard_lookups hrl
355 WHERE hrl.lookup_type IN ('EMP_CAT','CWK_ASG_CATEGORY')
356 AND hrl.enabled_flag = 'Y');
357
358 ELSE
359
360 -- Truncate the person type hierarchy table
361 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_hri_schema)) THEN
362 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_hri_schema || '.HRI_CS_PRSNTYP_CT';
363 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_hri_schema || '.HRI_CS_WKTH_LVL2_CT';
364 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_hri_schema || '.HRI_CS_WKTH_LVL1_CT';
365 END IF;
366
367 END IF;
368
369 END delete_from_dim_levels;
370
371 -- ----------------------------------------------------------------------------
372 -- Updates records into base level HRI_CS_PRSNTYP_CT (Incremental only)
373 -- ----------------------------------------------------------------------------
374 PROCEDURE update_dim_levels IS
375 --
376 -- PL/SQL table of updated person type records
377 --
378 TYPE l_number_tab_type IS TABLE OF hri_cs_geo_lochr_ct.location_id%TYPE;
379 l_upd_prsntyp_sks L_NUMBER_TAB_TYPE;
380 --
381 BEGIN
382 --
383 -- Update any changed records
384 --
385 UPDATE hri_cs_prsntyp_ct dim
386 SET
387 (wkth_wktyp_sk_fk
388 ,wkth_lvl1_sk_fk
389 ,wkth_lvl2_sk_fk
390 ,wkth_wktyp_code
391 ,wkth_lvl1_code
392 ,wkth_lvl2_code
393 ,include_flag_code) =
394 (SELECT
395 vw.wkth_wktyp_sk_fk
396 ,vw.wkth_lvl1_sk_fk
397 ,vw.wkth_lvl2_sk_fk
398 ,vw.wkth_wktyp_code
399 ,vw.wkth_lvl1_code
400 ,vw.wkth_lvl2_code
401 ,vw.include_flag_code
402 FROM hri_cs_prsntyp_v vw
403 WHERE vw.person_type_id = dim.person_type_id
404 AND vw.employment_category_code = dim.employment_category_code
405 AND vw.primary_flag_code = dim.primary_flag_code
406 AND vw.assignment_type_code = dim.assignment_type_code)
407 WHERE EXISTS
408 (SELECT null
409 FROM hri_cs_prsntyp_v vw
410 WHERE vw.person_type_id = dim.person_type_id
411 AND vw.employment_category_code = dim.employment_category_code
412 AND vw.primary_flag_code = dim.primary_flag_code
413 AND vw.assignment_type_code = dim.assignment_type_code
414 AND (vw.wkth_wktyp_code <> dim.wkth_wktyp_code
415 OR vw.wkth_lvl1_code <> dim.wkth_lvl1_code
416 OR vw.wkth_lvl2_code <> dim.wkth_lvl2_code
417 OR vw.include_flag_code <> dim.include_flag_code))
418 RETURNING dim.prsntyp_sk_pk BULK COLLECT INTO l_upd_prsntyp_sks;
419 --
420 -- If the person type details of any of the existing records is changed then
421 -- the corresponding changes should be refelected in the assingment delta table also
422 -- So insert the PRSNTYP_SK_PK of the updated records into the assingment delta table
423 -- so that the changes can be made to the assignment delta table by the incr process
424 --
425 IF (l_upd_prsntyp_sks.LAST > 0 AND
426 fnd_profile.value('HRI_IMPL_DBI') = 'Y') THEN
427 --
428 BEGIN
429 --
430 FORALL i IN 1..l_upd_prsntyp_sks.LAST SAVE EXCEPTIONS
431 INSERT INTO HRI_EQ_ASG_SUP_WRFC
432 (SOURCE_TYPE,
433 SOURCE_ID)
434 VALUES
435 ('PERSON_TYPE',
436 l_upd_prsntyp_sks(i));
437 --
438 EXCEPTION WHEN OTHERS THEN
439 --
440 dbg(sql%bulk_exceptions.count|| ' person type records already exists in the event queue ');
441 --
442 END;
443 --
444 END IF;
445 --
446 END update_dim_levels;
447
448 -- ----------------------------------------------------------------------------
449 -- Full Refresh Entry Point
450 -- ----------------------------------------------------------------------------
451 PROCEDURE full_refresh IS
452
453 BEGIN
454
455 -- 3601362 Disable the WHO Trigger
456 run_sql_stmt_noerr('ALTER TRIGGER hri_cs_prsntyp_ct_who DISABLE');
457
458 -- Truncate dimension levels
459 delete_from_dim_levels(p_full_refresh => 'Y');
460
461 -- Repopulate dimension levels
462 insert_into_dim_levels(p_full_refresh => 'Y');
463
464 -- Commit changes
465 COMMIT;
466
467 -- 3601362 Enable the WHO trigger
468 run_sql_stmt_noerr('ALTER TRIGGER hri_cs_prsntyp_ct_who ENABLE');
469
470 END full_refresh;
471
472 -- ----------------------------------------------------------------------------
473 -- Incremental refresh entry point
474 -- ----------------------------------------------------------------------------
475 PROCEDURE incr_refresh IS
476
477 BEGIN
478
479 -- Delete obsolete records from dimension levels
480 delete_from_dim_levels(p_full_refresh => 'N');
481
482 -- Insert new records into dimension levels
483 insert_into_dim_levels(p_full_refresh => 'N');
484
485 -- Update changed records in dimension levels
486 update_dim_levels;
487
488 -- Commit changes
489 COMMIT;
490
491 END incr_refresh;
492
493 END hri_opl_person_type_ctgry;