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