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