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