[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_CMPTNC_LVL
Source
1 PACKAGE BODY HRI_OPL_CMPTNC_LVL AS
2 /* $Header: hripcmlv.pkb 120.1 2005/06/08 02:54:08 anmajumd noship $ */
3 --
4 -- Types required to support tables of column values.
5 --
6 -- @@ Code specific to this view/table below
7 -- @@ INTRUCTION TO DEVELOPER:
8 -- @@ 1/ For each column in your 'source view' create a TYPE in the format
9 -- @@ g_<col_name>_type. Each TYPE should be a table of 'target table.
10 -- @@ column'%TYPE indexed by binary_integer. i.e.:
11 -- @@
12 -- @@ TYPE g_<col_name>_type IS TABLE OF
13 -- @@ <target_table>%TYPE
14 -- @@ INDEX BY BINARY_INTEGER;
15 -- @@
16 -- *** This can be generated using */
17 -- SELECT
18 -- 'TYPE g_' || lower(column_name) || '_type IS TABLE OF ' ||
19 -- table_name || '.' || column_name || '%TYPE INDEX BY BINARY_INTEGER;'
20 -- FROM all_tab_columns
21 -- WHERE owner = 'HRI'
22 -- AND table_name = '<Table Name>'
23 -- ORDER BY column_id
24 --
25 TYPE g_date_tabtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;
26 TYPE g_number_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
27 TYPE g_varchar2_tabtype IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
28 --
29 -- @@ Code specific to this view/table below ENDS
30 --
31 --
32 -- PLSQL tables representing database table columns
33 --
34 g_row_ind g_number_tabtype;
35 g_no_level_ind g_number_tabtype;
36 g_scale_level_val g_number_tabtype;
37 g_min_scale_level g_number_tabtype;
38 g_max_scale_level g_number_tabtype;
39 g_rank_level_val g_number_tabtype;
40 g_min_rank_level g_number_tabtype;
41 g_max_rank_level g_number_tabtype;
42 g_nrmlzd_scale_lvl g_number_tabtype;
43 g_nrmlzd_rank_lvl g_number_tabtype;
44 g_competence_id g_number_tabtype;
45 g_scale_id g_number_tabtype;
46 g_level_id g_number_tabtype;
47 g_eval_mthd_code g_varchar2_tabtype;
48 g_rnwl_prd_freq g_number_tabtype;
49 g_rnwl_prd_unit g_varchar2_tabtype;
50 g_cert_reqrd_flag g_varchar2_tabtype;
51 g_scale_flag g_varchar2_tabtype;
52 g_scale_default_flag g_varchar2_tabtype;
53 g_last_change_date g_date_tabtype;
54 --
55 -- Holds the range for which the collection is to be run.
56 --
57 g_start_date DATE;
58 g_end_date DATE;
59 g_full_refresh VARCHAR2(10);
60 --
61 -- The HRI schema
62 --
63 g_schema VARCHAR2(400);
64 --
65 -- Set to true to output to a concurrent log file
66 --
67 g_conc_request_flag BOOLEAN := FALSE;
68 --
69 -- Number of rows bulk processed at a time
70 --
71 g_chunk_size PLS_INTEGER;
72 --
73 -- End of time date
74 --
75 -- CONSTANTS
76 -- =========
77 --
78 -- @@ Code specific to this view/table below
79 -- @@ in the call to hri_bpl_conc_log.get_last_collect_to_date
80 -- @@ change param1/2 to be the concurrent program short name,
81 -- @@ and the target table name respectively.
82 --
83 g_target_table VARCHAR2(30) DEFAULT 'HRI_CS_CMPTNC_LVL_CT';
84 g_cncrnt_prgrm_shrtnm VARCHAR2(30) DEFAULT 'HRICMPTNCLVL';
85 --
86 -- @@ Code specific to this view/table below ENDS
87 --
88 -- constants that hold the value that indicates to full refresh or not.
89 --
90 g_is_full_refresh VARCHAR2(5) DEFAULT 'Y';
91 g_not_full_refresh VARCHAR2(5) DEFAULT 'N';
92 --
93 -- -------------------------------------------------------------------------
94 --
95 -- Inserts row into concurrent program log when the g_conc_request_flag has
96 -- been set to TRUE, otherwise does nothing
97 --
98 PROCEDURE output(p_text VARCHAR2)
99 IS
100 --
101 BEGIN
102 --
103 -- Write to the concurrent request log if called from a concurrent request
104 --
105 IF (g_conc_request_flag = TRUE) THEN
106 --
107 -- Put text to log file
108 --
109 fnd_file.put_line(FND_FILE.log, p_text);
110 --
111 END IF;
112 --
113 END output;
114 --
115 -- -------------------------------------------------------------------------
116 --
117 -- Recovers rows to insert when an exception occurs
118 --
119 PROCEDURE recover_insert_rows(p_stored_rows_to_insert NUMBER) IS
120
121 BEGIN
122 --
123 -- loop through rows still to insert one at a time
124 --
125 FOR i IN 1..p_stored_rows_to_insert LOOP
126 --
127 -- Trap unique constraint errors
128 --
129 BEGIN
130 --
131 -- @@ Code specific to this view/table below
132 -- @@ INTRUCTION TO DEVELOPER:
133 -- @@ 1/ For each column in your view put a column in the insert
134 -- @@ statement below.
135 -- @@ 2/ Prefix each column in the VALUE clause with g_
136 -- @@ 3/ make sure (i) is at the end of each column in the value clause
137 --
138 INSERT INTO hri_cs_cmptnc_lvl_ct
139 (row_indicator
140 ,no_level_indicator
141 ,scale_level_value
142 ,min_scale_level_value
143 ,max_scale_level_value
144 ,rank_level_value
145 ,min_rank_level_value
146 ,max_rank_level_value
147 ,nrmlzd_scale_level_value
148 ,nrmlzd_rank_level_value
149 ,competence_id
150 ,scale_id
151 ,level_id
152 ,cmptnc_eval_mthd_code
153 ,cmptnc_rnwl_prd_freq_value
154 ,cmptnc_rnwl_prd_unit_code
155 ,cmptnc_cert_reqrd_flag_code
156 ,scale_flag_code
157 ,scale_dflt_flag_code
158 ,last_change_date)
159 VALUES
160 (g_row_ind(i)
161 ,g_no_level_ind(i)
162 ,g_scale_level_val(i)
163 ,g_min_scale_level(i)
164 ,g_max_scale_level(i)
165 ,g_rank_level_val(i)
166 ,g_min_rank_level(i)
167 ,g_max_rank_level(i)
168 ,g_nrmlzd_scale_lvl(i)
169 ,g_nrmlzd_rank_lvl(i)
170 ,g_competence_id(i)
171 ,g_scale_id(i)
172 ,g_level_id(i)
173 ,g_eval_mthd_code(i)
174 ,g_rnwl_prd_freq(i)
175 ,g_rnwl_prd_unit(i)
176 ,g_cert_reqrd_flag(i)
177 ,g_scale_flag(i)
178 ,g_scale_default_flag(i)
179 ,g_last_change_date(i));
180 --
181 -- @@Code specific to this view/table below ENDS
182 --
183 EXCEPTION
184 --
185 WHEN OTHERS THEN
186 --
187 -- @@ Code specific to this view/table below
188 -- @@ INTRUCTION TO DEVELOPER:
189 -- @@ 1/ Add a useful log message in the event of an insert failing
190 --
191 output('Single insert error: ' || to_char(g_competence_id(i)) ||
192 ' - ' || to_char(g_level_id(i)) ||
193 ' - ' || to_char(g_scale_id(i)));
194 --
195 output(sqlerrm);
196 output(sqlcode);
197 --
198 --
199 END;
200 --
201 END LOOP;
202 --
203 COMMIT;
204 --
205 END recover_insert_rows;
206 --
207 -- -------------------------------------------------------------------------
208 --
209 -- Bulk inserts rows from global temporary table to database table
210 --
211 PROCEDURE bulk_insert_rows(p_stored_rows_to_insert NUMBER) IS
212 --
213 BEGIN
214 --
215 -- insert chunk of rows
216 --
217 -- @@ Code specific to this view/table below
218 -- @@ INTRUCTION TO DEVELOPER:
219 -- @@ 1/ For each column in your view put a column in the insert statement
220 -- below.
221 -- @@ 2/ Prefix each column in the VALUE clause with g_
222 -- @@ 3/ make sure (i) is at the end of each column in the value clause
223 --
224 FORALL i IN 1..p_stored_rows_to_insert
225 INSERT INTO hri_cs_cmptnc_lvl_ct
226 (row_indicator
227 ,no_level_indicator
228 ,scale_level_value
229 ,min_scale_level_value
230 ,max_scale_level_value
231 ,rank_level_value
232 ,min_rank_level_value
233 ,max_rank_level_value
234 ,nrmlzd_scale_level_value
235 ,nrmlzd_rank_level_value
236 ,competence_id
237 ,scale_id
238 ,level_id
239 ,cmptnc_eval_mthd_code
240 ,cmptnc_rnwl_prd_freq_value
241 ,cmptnc_rnwl_prd_unit_code
242 ,cmptnc_cert_reqrd_flag_code
243 ,scale_flag_code
244 ,scale_dflt_flag_code
245 ,last_change_date)
246 VALUES
247 (g_row_ind(i)
248 ,g_no_level_ind(i)
249 ,g_scale_level_val(i)
250 ,g_min_scale_level(i)
251 ,g_max_scale_level(i)
252 ,g_rank_level_val(i)
253 ,g_min_rank_level(i)
254 ,g_max_rank_level(i)
255 ,g_nrmlzd_scale_lvl(i)
256 ,g_nrmlzd_rank_lvl(i)
257 ,g_competence_id(i)
258 ,g_scale_id(i)
259 ,g_level_id(i)
260 ,g_eval_mthd_code(i)
261 ,g_rnwl_prd_freq(i)
262 ,g_rnwl_prd_unit(i)
263 ,g_cert_reqrd_flag(i)
264 ,g_scale_flag(i)
265 ,g_scale_default_flag(i)
266 ,g_last_change_date(i));
267 --
268 -- @@Code specific to this view/table below ENDS
269 --
270 -- commit the chunk of rows
271 --
272 COMMIT;
273 --
274 EXCEPTION
275 --
276 WHEN OTHERS THEN
277 --
278 -- Probable unique constraint error
279 --
280 ROLLBACK;
281 --
282 recover_insert_rows(p_stored_rows_to_insert);
283 --
284 END bulk_insert_rows;
285 --
286 PROCEDURE Full_Refresh IS
287 --
288 -- Select all from the source view for materialization
289 --
290 -- @@ Code specific to this view/table below
291 -- @@ INTRUCTION TO DEVELOPER:
292 -- @@ 1/ Change the select beloe to select all the columns from your view
293 -- @@ 2/ Change the FROM statement to point at the relevant source view
294 --
295 CURSOR source_view_csr IS
296 SELECT
297 row_indicator
298 ,no_level_indicator
299 ,scale_level_value
300 ,min_scale_level_value
301 ,max_scale_level_value
302 ,rank_level_value
303 ,min_rank_level_value
304 ,max_rank_level_value
305 ,nrmlzd_scale_level_value
306 ,nrmlzd_rank_level_value
307 ,competence_id
308 ,scale_id
309 ,level_id
310 ,cmptnc_eval_mthd_code
311 ,cmptnc_rnwl_prd_freq_value
312 ,cmptnc_rnwl_prd_unit_code
313 ,cmptnc_cert_reqrd_flag_code
314 ,scale_flag_code
315 ,scale_dflt_flag_code
316 ,last_change_date
317 FROM hri_cs_cmptnc_lvl_v;
318 --
319 -- @@Code specific to this view/table below ENDS
320 --
321 l_exit_main_loop BOOLEAN := FALSE;
322 l_rows_fetched PLS_INTEGER := g_chunk_size;
323 l_sql_stmt VARCHAR2(2000);
324 --
325 BEGIN
326 -- log('here ...');
327 --
328 -- Truncate the target table prior to full refresh.
329 --
330 l_sql_stmt := 'TRUNCATE TABLE ' || g_schema || '.'||g_target_table;
331 -- log('>'||l_sql_stmt||'<');
332 --
333 EXECUTE IMMEDIATE(l_sql_stmt);
334 -- log('trunced ...');
335 --
336 -- Write timing information to log
337 --
338 output('Truncated the table: ' ||
339 to_char(sysdate,'HH24:MI:SS'));
340 --
341 -- open main cursor
342 --
343 -- log('open cursor ...');
344 OPEN source_view_csr;
345 --
346 <<main_loop>>
347 LOOP
348 --
349 -- bulk fetch rows limit the fetch to value of g_chunk_size
350 --
351 -- @@ Code specific to this view/table below
352 -- @@ INTRUCTION TO DEVELOPER:
353 -- @@ Change the bulk collect below to select all the columns from your
354 -- @@ view
355 --
356 -- log('start fetch ...');
357 -- log('>'||TO_CHAR(g_chunk_size)||'<');
358 FETCH source_view_csr
359 BULK COLLECT INTO
360 g_row_ind
361 ,g_no_level_ind
362 ,g_scale_level_val
363 ,g_min_scale_level
364 ,g_max_scale_level
365 ,g_rank_level_val
366 ,g_min_rank_level
367 ,g_max_rank_level
368 ,g_nrmlzd_scale_lvl
369 ,g_nrmlzd_rank_lvl
370 ,g_competence_id
371 ,g_scale_id
372 ,g_level_id
373 ,g_eval_mthd_code
374 ,g_rnwl_prd_freq
375 ,g_rnwl_prd_unit
376 ,g_cert_reqrd_flag
377 ,g_scale_flag
378 ,g_scale_default_flag
379 ,g_last_change_date
380 LIMIT g_chunk_size;
381 -- log('finish fetch ...');
382 --
383 -- @@Code specific to this view/table below ENDS
384 --
385 -- check to see if the last row has been fetched
386 --
387 IF source_view_csr%NOTFOUND THEN
388 --
389 -- last row fetched, set exit loop flag
390 --
391 l_exit_main_loop := TRUE;
392 --
393 -- do we have any rows to process?
394 --
395 l_rows_fetched := MOD(source_view_csr%ROWCOUNT,g_chunk_size);
396 --
397 -- note: if l_rows_fetched > 0 then more rows are required to be
398 -- processed and the l_rows_fetched will contain the exact number of
399 -- rows left to process
400 --
401 IF l_rows_fetched = 0 THEN
402 --
403 -- no more rows to process so exit loop
404 --
405 EXIT main_loop;
406 END IF;
407 END IF;
408 --
409 -- bulk insert rows processed so far
410 --
411 -- log('call bulk ...');
412 bulk_insert_rows (l_rows_fetched);
413 -- log('end bulk ...');
414 --
415 -- exit loop if required
416 --
417 IF l_exit_main_loop THEN
418 --
419 EXIT main_loop;
420 --
421 END IF;
422 --
423 END LOOP;
424 --
425 CLOSE source_view_csr;
426 --
427 -- log('End ...');
428 EXCEPTION
429 WHEN OTHERS THEN
430 --
431 -- unexpected error has occurred so close down
432 -- main bulk cursor if it is open
433 --
434 IF source_view_csr%ISOPEN THEN
435 --
436 CLOSE source_view_csr;
437 --
438 END IF;
439 --
440 -- re-raise error
441 RAISE;
442 --
443 --
444 END Full_Refresh;
445 --
446 -- -------------------------------------------------------------------------
447 -- Checks what mode you are running in, and if g_full_refresh =
448 -- g_is_full_refresh calls
449 -- Full_Refresh procedure, otherwise Incremental_Update is called.
450 --
451 PROCEDURE Collect IS
452 --
453 BEGIN
454 --
455 -- If in full refresh mode change the dates so that the collection history
456 -- is correctly maintained.
457 --
458 IF g_full_refresh = g_is_full_refresh THEN
459 --
460 IF (g_start_date IS NULL) THEN
461 g_start_date := hr_general.start_of_time;
462 END IF;
463 IF (g_end_date IS NULL) THEN
464 g_end_date := SYSDATE;
465 END IF;
466 --
467 -- log('Doing full refresh.');
468 Full_Refresh;
469 --
470 END IF;
471 --
472 END Collect;
473 --
474 -- -------------------------------------------------------------------------
475 -- Checks if the Target table is Empty
476 --
477 -- -------------------------------------------------------------------------
478 --
479 -- Main entry point to load the table.
480 --
481 PROCEDURE Load(p_chunk_size IN NUMBER,
482 p_start_date IN VARCHAR2,
483 p_end_date IN VARCHAR2,
484 p_full_refresh IN VARCHAR2) IS
485 --
486 -- Variables required for table truncation.
487 --
488 l_dummy1 VARCHAR2(2000);
489 l_dummy2 VARCHAR2(2000);
490 --
491 BEGIN
492 --
493 output('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
494 --
495 -- Set globals
496 --
497 g_start_date := to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
498 g_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
499 --
500 IF p_chunk_size IS NULL
501 THEN
502 --
503 g_chunk_size := 1000;
504 --
505 ELSE
506 --
507 g_chunk_size := p_chunk_size;
508 --
509 END IF;
510 --
511 IF p_full_refresh IS NULL
512 THEN
513 --
514 g_full_refresh := g_not_full_refresh;
515 --
516 ELSE
517 --
518 g_full_refresh := p_full_refresh;
519 --
520 END IF;
521 --
522 -- log('p_chunk_size>'||TO_CHAR(g_chunk_size)||'<');
523 -- Find the schema we are running in.
524 --
525 IF NOT fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, g_schema)
526 THEN
527 --
528 -- Could not find the schema raising exception.
529 --
530 output('Could not find schema to run in.');
531 --
532 -- log('Could not find schema.');
533 RAISE NO_DATA_FOUND;
534 --
535 END IF;
536 --
537 -- Update information about collection
538 --
539 -- log('Record process start.');
540 /* double check correct val passed in below */
541 hri_bpl_conc_log.record_process_start(g_cncrnt_prgrm_shrtnm);
542 --
543 -- Time at start
544 --
545 -- log('collect.');
546 --
547 -- Get HRI schema name - get_app_info populates l_schema
548 --
549 -- Insert new records
550 --
551 collect;
552 -- log('collectED.');
553 --
554 -- Write timing information to log
555 --
556 output('Finished changes to the table: ' ||
557 to_char(sysdate,'HH24:MI:SS'));
558 --
559 -- Gather index stats
560 --
561 -- log('gather stats.');
562 fnd_stats.gather_table_stats(g_schema, g_target_table);
563 --
564 -- Write timing information to log
565 --
566 output('Gathered stats: ' ||
567 to_char(sysdate,'HH24:MI:SS'));
568 --
569 -- log('log end.');
570 hri_bpl_conc_log.log_process_end(
571 p_status => TRUE,
572 p_period_from => TRUNC(g_start_date),
573 p_period_to => TRUNC(g_end_date),
574 p_attribute1 => p_full_refresh,
575 p_attribute2 => p_chunk_size);
576 -- log('-END-');
577 --
578 EXCEPTION
579 --
580 WHEN OTHERS
581 THEN
582 --
583 ROLLBACK;
584 RAISE;
585 --
586 --
587 END Load;
588 --
589 -- -------------------------------------------------------------------------
590 --
591 -- Entry point to be called from the concurrent manager
592 --
593 PROCEDURE Load(errbuf OUT NOCOPY VARCHAR2,
594 retcode OUT NOCOPY VARCHAR2,
595 p_chunk_size IN NUMBER,
596 p_start_date IN VARCHAR2,
597 p_end_date IN VARCHAR2,
598 p_full_refresh IN VARCHAR2)
599 IS
600 --
601 BEGIN
602 --
603 -- Enable output to concurrent request log
604 --
605 g_conc_request_flag := TRUE;
606 --
607 load(p_chunk_size => p_chunk_size,
608 p_start_date => p_start_date,
609 p_end_date => p_end_date,
610 p_full_refresh => p_full_refresh);
611 --
612 EXCEPTION
613 --
614 WHEN OTHERS THEN
615 --
616 errbuf := SQLERRM;
617 retcode := SQLCODE;
618 --
619 --
620 END load;
621 --
622 END HRI_OPL_CMPTNC_LVL;