[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BDGTS_HDCNT_ORGMGR
Source
1 PACKAGE BODY HRI_OPL_BDGTS_HDCNT_ORGMGR AS
2 /* $Header: hriobhom.pkb 120.1 2005/06/29 07:02:12 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_MDP_BDGTS_HDCNT_ORGMGR_CT (
113 orgmgr_id
114 ,budget_id
115 ,budget_aggregate
116 ,effective_start_date
117 ,effective_end_date
118 ,organization_id
119 ,job_id
120 ,position_id
121 ,grade_id
122 ,headcount_value
123 ,dr_headcount_value
124 --
125 -- WHO Columns
126 --
127 ,last_update_date
128 ,last_update_login
129 ,last_updated_by
130 ,created_by
131 ,creation_date)
132 SELECT orgmgr.sup_person_id ORGMGR_ID
133 ,budget_id BUDGET_ID
134 ,budget_aggregate BUDGET_AGGREGATE
135 ,GREATEST(period_start_date,orgmgr.effective_start_date) EFFECTIVE_START_DATE
136 ,LEAST(period_end_date,orgmgr.effective_end_date) EFFECTIVE_END_DATE
137 ,organization_id ORGANIZATION_ID
138 ,job_id JOB_ID
139 ,position_id POSITION_ID
140 ,grade_id GRADE_ID
141 ,budget_value HEADCOUNT_VALUE
142 ,budget_value * DECODE(orgmgr.sub_relative_level, 0, 1, 0) DR_HEADCOUNT_VALUE
143 ,SYSDATE
144 ,l_user_id
145 ,l_user_id
146 ,l_user_id
147 ,SYSDATE
148 FROM hri_mb_bdgts_ct bdgts
149 ,hri_cs_suph_orgmgr_ct orgmgr
150 WHERE bdgts.organization_id = orgmgr.sub_organization_id
151 AND bdgts.budget_measurement_type = 'HEAD';
152 --
153 dbg(SQL%ROWCOUNT||' records inserted into HRI_MDP_BDGTS_HDCNT_ORGMGR_CT');
154 dbg('Exiting process');
155 COMMIT;
156 --
157 EXCEPTION
158 WHEN OTHERS THEN
159 --
160 output(sqlerrm);
161 --
162 -- RAISE;
163 --
164 --
165 END process;
166 --
167 -- ----------------------------------------------------------------------------
168 -- PRE_PROCESS
169 -- ----------------------------------------------------------------------------
170 --
171 PROCEDURE PRE_PROCESS IS
172 --
173 l_dummy1 VARCHAR2(2000);
174 l_dummy2 VARCHAR2(2000);
175 l_schema VARCHAR2(400);
176 --
177 BEGIN
178 --
179 -- Record the process start
180 --
181 --
182 -- Set up the parameters
183 --
184 set_parameters;
185 --
186 -- Disable the WHO trigger
187 --
188 run_sql_stmt_noerr('ALTER TRIGGER HRI_MDP_BDGTS_HDCNT_ORGMGR_CT_WHO DISABLE');
189 --
190 -- ---------------------------------------------------------------------------
191 -- Full Refresh Section
192 -- ---------------------------------------------------------------------------
193 --
194 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
195 --
196 -- If it's a full refresh
197 --
198 IF (g_full_refresh = 'Y') THEN
199 --
200 -- Drop Indexes
201 --
202 hri_utl_ddl.log_and_drop_indexes(
203 p_application_short_name => 'HRI',
204 p_table_name => 'HRI_MDP_BDGTS_HDCNT_ORGMGR_CT',
205 p_table_owner => l_schema);
206 --
207 -- Truncate the table
208 --
209 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MDP_BDGTS_HDCNT_ORGMGR_CT';
210 --
211 END IF;
212 --
213 END IF;
214 --
215 --
216 END PRE_PROCESS;
217 --
218 -- ----------------------------------------------------------------------------
219 -- POST_PROCESS
220 -- It finishes the processing by updating the BIS_REFRESH_LOG table
221 -- ----------------------------------------------------------------------------
222 --
223 PROCEDURE post_process IS
224 --
225 l_dummy1 VARCHAR2(2000);
226 l_dummy2 VARCHAR2(2000);
227 l_schema VARCHAR2(400);
228 --
229 --
230 BEGIN
231 --
232 dbg('Inside post_process');
233 --
234 -- hri_bpl_conc_log.record_process_start('HRI_OPL_BDGTS_HDCNT_ORGMGR');
235 --
236 -- Collect stats for full refresh
237 --
238 IF (g_full_refresh = 'Y') THEN
239 --
240 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
241 --
242 -- Create indexes
243 --
244 dbg('Full Refresh selected - Creating indexes');
245 --
246 hri_utl_ddl.recreate_indexes(
247 p_application_short_name => 'HRI',
248 p_table_name => 'HRI_MDP_BDGTS_HDCNT_ORGMGR_CT',
249 p_table_owner => l_schema);
250 --
251 -- Collect the statistics only when the process is NOT invoked by a concurrent manager
252 --
253 IF fnd_global.conc_request_id is null THEN
254 --
255 dbg('Full Refresh selected - gathering stats');
256 fnd_stats.gather_table_stats(l_schema,'HRI_MDP_BDGTS_HDCNT_ORGMGR_CT');
257 --
258 END IF;
259 --
260 END IF;
261 --
262 ELSE
263 --
264 -- Incremental Refresh will be supported later.
265 --
266 NULL;
267 --
268 END IF;
269 --
270 -- Enable the WHO trigger on the fact table
271 --
272 dbg('Enabling the who trigger');
273 run_sql_stmt_noerr('ALTER TRIGGER HRI_MDP_BDGTS_HDCNT_ORGMGR_CT_WHO ENABLE');
274 --
275 hri_bpl_conc_log.log_process_end(
276 p_status => TRUE
277 ,p_period_from => TRUNC(g_refresh_start_date)
278 ,p_period_to => TRUNC(SYSDATE)
279 ,p_attribute1 => g_full_refresh);
280 --
281 dbg('Exiting post_process');
282 --
283 END post_process;
284 --
285 -- ----------------------------------------------------------------------------
286 -- PROCESS
287 -- ----------------------------------------------------------------------------
288 --
289 PROCEDURE process(
290 errbuf OUT NOCOPY VARCHAR2
291 ,retcode OUT NOCOPY NUMBER
292 ,p_full_refresh_flag IN VARCHAR2)
293 IS
294 --
295 l_error_step NUMBER;
296 --
297 BEGIN
298 --
299 -- Initialize the global variables
300 --
301 pre_process;
302 --
303 -- Depending on the refresh type call the corresponding refresh program
304 --
305 IF g_full_refresh = 'Y' THEN
306 --
307 process(p_full_refresh_flag => g_full_refresh);
308 --
309 ELSE
310 --
311 -- Incremental Refresh will be supported later.
312 --
313 NULL;
314 --
315 END IF;
316 --
317 post_process;
318
319 errbuf := 'SUCCESS';
320 retcode := 0;
321 EXCEPTION
322 WHEN others THEN
323 output('Error encountered while processing ...');
324 output(sqlerrm);
325 errbuf := SQLERRM;
326 retcode := SQLCODE;
327 --
328 RAISE;
329 --
330 END process;
331
332 --
333 -- ----------------------------------------------------------------------------
334 -- LOAD_TABLE
335 -- This procedure can be called from the Test harness to populate the table.
336 -- ----------------------------------------------------------------------------
337 --
338 PROCEDURE load_table
339 IS
340 --
341 BEGIN
342 --
343 dbg('Inside load_table');
344 --
345 -- Call Pre Process
346 --
347 pre_process;
348 --
349 -- Call Process
350 --
351 process(p_full_refresh_flag => g_full_refresh);
352 --
353 -- Call Post Process
354 --
355 post_process;
356 --
357 dbg('Exiting load_table');
358 --
359 EXCEPTION
360 --
361 WHEN OTHERS THEN
362 --
363 output('Error in load_table = ');
364 output(SQLERRM);
365 RAISE;
366 --
367 END load_table;
368 --
369 END HRI_OPL_BDGTS_HDCNT_ORGMGR;