[Home] [Help]
643: || ' END' || g_rtn
644: || ' WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN jfm.value' || g_rtn
645: || ' ELSE jfm.value || '' ('' || jfn.value || '')''' || g_rtn
646: || ' END) fmfn_order_by' || g_rtn
647: || ' FROM (SELECT distinct job_fmly_code,job_fnctn_code FROM hri_cs_jobh_ct) job,' || g_rtn
648: || '(' || l_fmly_sql || ') jfm,' || g_rtn
649: || '(' || l_fnctn_sql || ') jfn' || g_rtn
650: || 'WHERE job.job_fmly_code = jfm.id(+)' || g_rtn
651: || 'AND job.job_fnctn_code = jfn.id(+)';
652: --
653: --
654: /*
655: Removed the following line from the dynamic sql and modified with inline view
656: || 'FROM hri_cs_jobh_ct job,' || g_rtn
657: and replace with
658: || ' FROM (SELECT distinct job_fmly_code,job_fnctn_code FROM hri_cs_jobh_ct) job,' || g_rtn
659: */
660:
654: /*
655: Removed the following line from the dynamic sql and modified with inline view
656: || 'FROM hri_cs_jobh_ct job,' || g_rtn
657: and replace with
658: || ' FROM (SELECT distinct job_fmly_code,job_fnctn_code FROM hri_cs_jobh_ct) job,' || g_rtn
659: */
660:
661: l_view_sql :=
662: 'CREATE OR REPLACE FORCE VIEW hri_obi_cl_job_fmlyfnct_v
1011:
1012: BEGIN
1013:
1014: -- 3601362 Disable the WHO Trigger
1015: run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO DISABLE');
1016:
1017: -- Truncate table
1018: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_hri_schema || '.hri_cs_jobh_ct';
1019:
1014: -- 3601362 Disable the WHO Trigger
1015: run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO DISABLE');
1016:
1017: -- Truncate table
1018: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_hri_schema || '.hri_cs_jobh_ct';
1019:
1020: -- Get the job family and function column strings
1021: l_job_fnctn_column := get_column_string
1022: (p_job_type => 'JOB_FUNCTION',
1026: p_flex_type => get_flexfield_type('JOB_FAMILY'));
1027:
1028: -- Formulate the insert SQL statement
1029: l_sql_stmt :=
1030: 'INSERT INTO hri_cs_jobh_ct
1031: (job_id
1032: ,job_fmly_code
1033: ,job_fnctn_code
1034: ,last_update_date
1077: -- Commit
1078: COMMIT;
1079:
1080: -- Gather Stats
1081: fnd_stats.gather_table_stats(g_hri_schema, 'HRI_CS_JOBH_CT');
1082:
1083: -- 3601362 Enable the WHO trigger
1084: run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO ENABLE');
1085:
1080: -- Gather Stats
1081: fnd_stats.gather_table_stats(g_hri_schema, 'HRI_CS_JOBH_CT');
1082:
1083: -- 3601362 Enable the WHO trigger
1084: run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_JOBH_CT_WHO ENABLE');
1085:
1086: END collect_hierarchy_table;
1087: --
1088: -- ----------------------------------------------------------------------------
1095: l_current_time DATE := SYSDATE;
1096: l_user_id NUMBER := fnd_global.user_id;
1097:
1098: -- PL/SQL table of updated job records
1099: TYPE l_number_tab_type IS TABLE OF hri_cs_jobh_ct.job_id%TYPE;
1100: l_upd_job_ids L_NUMBER_TAB_TYPE;
1101:
1102: -- Variables for Dynamic SQL
1103: l_job_fmly_column VARCHAR2(1000);
1115: p_flex_type => get_flexfield_type('JOB_FAMILY'));
1116:
1117: -- Insert completely new rows
1118: l_sql_stmt :=
1119: 'INSERT INTO hri_cs_jobh_ct
1120: (job_id
1121: ,job_fmly_code
1122: ,job_fnctn_code
1123: ,last_update_date
1139: ,per_job_definitions pjd
1140: WHERE job.job_definition_id = pjd.job_definition_id
1141: AND NOT EXISTS
1142: (SELECT null
1143: FROM hri_cs_jobh_ct jobh
1144: WHERE jobh.job_id = job.job_id)';
1145:
1146: EXECUTE IMMEDIATE l_sql_stmt USING
1147: l_current_time
1154: COMMIT;
1155:
1156: -- Delete rows which no longer exist
1157: -- Bug 3347127 - don't delete unassigned row
1158: DELETE FROM hri_cs_jobh_ct jobh
1159: WHERE NOT EXISTS
1160: (SELECT null
1161: FROM per_jobs job
1162: WHERE job.job_id = jobh.job_id)
1165: -- Update changed rows
1166: -- 3943809 The job family and function values is determined by the function
1167: -- get_family_function_code based on the setup
1168: l_sql_stmt :=
1169: 'UPDATE hri_cs_jobh_ct jobh
1170: SET (job_fmly_code
1171: ,job_fnctn_code) =
1172: (SELECT
1173: ' || l_job_fmly_column || '