[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_SUPH_ORGMGR
Source
1 PACKAGE BODY HRI_OPL_SUPH_ORGMGR AS
2 /* $Header: hriosomh.pkb 120.1 2005/06/29 07:02:47 ddutta noship $ */
3 --
4 -- Global variables representing parameters
5 --
6 g_refresh_start_date DATE;
7 g_refresh_end_date DATE;
8 g_full_refresh VARCHAR2(5);
9 --
10 -- Global flag which determines whether debugging is turned on
11 --
12 g_debug_flag VARCHAR2(5);
13 --
14 -- Whether called from a concurrent program
15 --
16 g_concurrent_flag VARCHAR2(5);
17 -- ----------------------------------------------------------------------------
18 -- Inserts row into concurrent program log
19 --
20 --
21 PROCEDURE output(p_text VARCHAR2) IS
22 BEGIN
23 --
24 IF (g_concurrent_flag = 'Y') THEN
25 --
26 -- Write to the concurrent request log
27 --
28 fnd_file.put_line(fnd_file.log, p_text);
29 --
30 ELSE
31 --
32 hr_utility.trace(p_text);
33 --
34 END IF;
35 --
36 END output;
37 --
38 -- -----------------------------------------------------------------------------
39 -- Inserts row into concurrent program log if debugging is enabled
40 -- -----------------------------------------------------------------------------
41 --
42 PROCEDURE dbg(p_text VARCHAR2) IS
43 --
44 BEGIN
45 --
46 IF (g_debug_flag = 'Y') THEN
47 --
48 -- Write to output
49 --
50 output(p_text);
51 --
52 END IF;
53 --
54 END dbg;
55 --
56 -- ----------------------------------------------------------------------------
57 -- Runs given sql statement dynamically without raising an exception
58 -- ----------------------------------------------------------------------------
59 --
60 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
61 IS
62 --
63 BEGIN
64 --
65 EXECUTE IMMEDIATE p_sql_stmt;
66 --
67 EXCEPTION WHEN OTHERS THEN
68 --
69 output('Could not run the following sql:');
70 output(SUBSTR(p_sql_stmt,1,230));
71 --
72 END run_sql_stmt_noerr;
73 --
74 -- ----------------------------------------------------------------------------
75 -- SET_PARAMETERS
76 -- sets up parameters required for the process.
77 -- ----------------------------------------------------------------------------
78 --
79 PROCEDURE set_parameters IS
80 --
81 BEGIN
82 --
83 g_refresh_start_date := bis_common_parameters.get_global_start_date;
84 g_refresh_end_date := hr_general.end_of_time;
85 g_full_refresh := 'Y';
86 g_concurrent_flag := 'Y';
87 g_debug_flag := 'Y';
88 --
89 END set_parameters;
90 --
91 -- ----------------------------------------------------------------------------
92 -- PROCESS
93 -- Processes actions and inserts data into summary table
94 -- This procedure is executed for every person in a chunk
95 -- ----------------------------------------------------------------------------
96 --
97 PROCEDURE process(p_full_refresh_flag IN VARCHAR2)
98 IS
99 --
100 -- Variables to populate WHO Columns
101 --
102 l_current_time DATE;
103 l_user_id NUMBER;
104 --
105 BEGIN
106 --
107 dbg('Inside process');
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
117 ,sup_level
118 ,sub_business_group_id
119 ,sub_person_id
120 ,sub_assignment_id
121 ,sub_organization_id
122 ,sub_level
123 ,sub_relative_level
124 ,effective_start_date
125 ,effective_end_date
126 --
127 -- WHO Columns
128 --
129 ,last_update_date
130 ,last_update_login
131 ,last_updated_by
132 ,created_by
133 ,creation_date)
134 SELECT SUP_BUSINESS_GROUP_ID
135 ,SUP_PERSON_ID
136 ,SUP_ASSIGNMENT_ID
137 ,ORGANIZATION_ID SUP_ORGANIZATION_ID
138 ,SUP_LEVEL
139 ,SUB_BUSINESS_GROUP_ID
140 ,SUB_PERSON_ID
141 ,SUB_ASSIGNMENT_ID
142 ,ORGANIZATION_ID SUB_ORGANIZATION_ID
143 ,SUB_LEVEL
144 ,SUB_RELATIVE_LEVEL
145 ,EFFECTIVE_START_DATE
146 ,EFFECTIVE_END_DATE
147 ,SYSDATE
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
157 FROM hr_organization_information hoi
158 WHERE hoi.org_information_context = 'Organization Name Alias'
159 AND hoi.org_information2 IS NOT NULL
160 AND EXISTS ( SELECT NULL
161 FROM hr_org_info_types_by_class oitbc,
162 hr_organization_information org_info
163 WHERE org_info.organization_id = hoi.organization_id
164 AND org_info.org_information_context = 'CLASS'
165 AND org_info.org_information2 = 'Y'
166 AND oitbc.org_classification = org_info.org_information1
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');
176 --
177 EXCEPTION
178 WHEN OTHERS THEN
179 --
180 output(sqlerrm);
181 --
182 -- RAISE;
183 --
184 --
185 END process;
186 --
187 -- ----------------------------------------------------------------------------
188 -- PRE_PROCESS
189 -- ----------------------------------------------------------------------------
190 --
191 PROCEDURE PRE_PROCESS IS
192 --
193 l_dummy1 VARCHAR2(2000);
194 l_dummy2 VARCHAR2(2000);
195 l_schema VARCHAR2(400);
196 --
197 BEGIN
198 --
199 dbg('Inside pre_process');
200 --
201 -- Set up the parameters
202 --
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 -- ---------------------------------------------------------------------------
212 --
213 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
214 --
215 -- If it's a full refresh
216 --
217 IF (g_full_refresh = 'Y') THEN
218 --
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 --
228 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_SUPH_ORGMGR_CT';
229 --
230 END IF;
231 --
232 END IF;
233 --
234 --
235 END PRE_PROCESS;
236 --
237 -- ----------------------------------------------------------------------------
238 -- POST_PROCESS
239 -- It finishes the processing by updating the BIS_REFRESH_LOG table
240 -- ----------------------------------------------------------------------------
241 --
242 PROCEDURE post_process IS
243 --
244 l_dummy1 VARCHAR2(2000);
245 l_dummy2 VARCHAR2(2000);
246 l_schema VARCHAR2(400);
247 --
248 --
249 BEGIN
250 --
251 dbg('Inside post_process');
252 --
253 hri_bpl_conc_log.record_process_start('HRI_OPL_SUPH_ORGMGR');
254 --
255 -- Collect stats for full refresh
256 --
257 IF (g_full_refresh = 'Y') THEN
258 --
259 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
260 --
261 -- Create indexes
262 --
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 --
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;
280 --
281 ELSE
282 --
283 -- Incremental Refresh will be supported later.
284 --
285 NULL;
286 --
287 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)
297 ,p_period_to => TRUNC(SYSDATE)
298 ,p_attribute1 => g_full_refresh);
299 --
300 dbg('Exiting post_process');
301 --
302 END post_process;
303 --
304 -- ----------------------------------------------------------------------------
305 -- PROCESS
306 -- ----------------------------------------------------------------------------
307 --
308 PROCEDURE process(
309 errbuf OUT NOCOPY VARCHAR2
310 ,retcode OUT NOCOPY NUMBER
311 ,p_full_refresh_flag IN VARCHAR2)
312 IS
313 --
314 l_error_step NUMBER;
315 --
316 BEGIN
317 --
318 -- Initialize the global variables
319 --
320 pre_process;
321 --
322 -- Depending on the refresh type call the corresponding refresh program
323 --
324 IF g_full_refresh = 'Y' THEN
325 --
326 process(p_full_refresh_flag => g_full_refresh);
327 --
328 ELSE
329 --
330 -- Incremental Refresh will be supported later.
331 --
332 NULL;
333 --
334 END IF;
335 --
336 post_process;
337
338 errbuf := 'SUCCESS';
339 retcode := 0;
340 EXCEPTION
341 WHEN others THEN
342 output('Error encountered while processing ...');
343 output(sqlerrm);
344 errbuf := SQLERRM;
345 retcode := SQLCODE;
346 --
347 RAISE;
348 --
349 END process;
350
351 --
352 -- ----------------------------------------------------------------------------
353 -- LOAD_TABLE
354 -- This procedure can be called from the Test harness to populate the table.
355 -- ----------------------------------------------------------------------------
356 --
357 PROCEDURE load_table
358 IS
359 --
360 BEGIN
361 --
362 dbg('Inside load_table');
363 --
364 -- Call Pre Process
365 --
366 pre_process;
367 --
368 -- Call Process
369 --
370 process(p_full_refresh_flag => g_full_refresh);
371 --
372 -- Call Post Process
373 --
374 post_process;
375 --
376 dbg('Exiting load_table');
377 --
378 EXCEPTION
379 --
380 WHEN OTHERS THEN
381 --
382 output('Error in load_table = ');
383 output(SQLERRM);
384 RAISE;
385 --
386 END load_table;
387 --
388 END HRI_OPL_SUPH_ORGMGR;