[Home] [Help]
108: --
109: l_current_time := SYSDATE;
110: l_user_id := fnd_global.user_id;
111: --
112: INSERT INTO HRI_CS_SUPH_ORGMGR_CT (
113: sup_business_group_id
114: ,sup_person_id
115: ,sup_assignment_id
116: ,sup_organization_id
148: ,l_user_id
149: ,l_user_id
150: ,l_user_id
151: ,SYSDATE
152: FROM hri_cs_suph ,
153: (SELECT hoi.organization_id organization_id,
154: NVL(to_number(hoi.org_information2), -1) manager_person_id ,
155: NVL(fnd_date.canonical_to_date(hoi.org_information3), hr_general.start_of_time) start_date,
156: NVL(fnd_date.canonical_to_date(hoi.org_information4), hr_general.end_of_time) end_date
167: AND oitbc.org_information_type = 'Organization Name Alias')) org_manager
168: WHERE sub_person_id = manager_person_id;
169: -- AND effective_start_date BETWEEN start_date AND end_date;
170: --
171: dbg(SQL%ROWCOUNT||' records inserted into HRI_CS_SUPH_ORGMGR_CT');
172: --
173: COMMIT;
174: --
175: dbg('Exiting process');
203: set_parameters;
204: --
205: -- Disable the WHO trigger
206: --
207: run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_SUPH_ORGMGR_CT_WHO DISABLE');
208: --
209: -- ---------------------------------------------------------------------------
210: -- Full Refresh Section
211: -- ---------------------------------------------------------------------------
219: -- Drop Indexes
220: --
221: hri_utl_ddl.log_and_drop_indexes(
222: p_application_short_name => 'HRI',
223: p_table_name => 'HRI_CS_SUPH_ORGMGR_CT',
224: p_table_owner => l_schema);
225: --
226: -- Truncate the table
227: --
224: p_table_owner => l_schema);
225: --
226: -- Truncate the table
227: --
228: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_SUPH_ORGMGR_CT';
229: --
230: END IF;
231: --
232: END IF;
263: dbg('Full Refresh selected - Creating indexes');
264: --
265: hri_utl_ddl.recreate_indexes(
266: p_application_short_name => 'HRI',
267: p_table_name => 'HRI_CS_SUPH_ORGMGR_CT',
268: p_table_owner => l_schema);
269: --
270: -- Collect the statistics only when the process is NOT invoked by a concurrent manager
271: --
271: --
272: IF fnd_global.conc_request_id is null THEN
273: --
274: dbg('Full Refresh selected - gathering stats');
275: fnd_stats.gather_table_stats(l_schema,'HRI_CS_SUPH_ORGMGR_CT');
276: --
277: END IF;
278: --
279: END IF;
288: --
289: -- Enable the WHO trigger on the fact table
290: --
291: dbg('Enabling the who trigger');
292: run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_SUPH_ORGMGR_CT_WHO ENABLE');
293: --
294: hri_bpl_conc_log.log_process_end(
295: p_status => TRUE
296: ,p_period_from => TRUNC(g_refresh_start_date)