[Home] [Help]
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;
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)';
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
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
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
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;
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);
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
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
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:
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
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
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:
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: -- ----------------------------------------------------------------------------