[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_REC_VAC
Source
1 PACKAGE BODY HRI_OPL_REC_VAC AS
2 /* $Header: hriprvac.pkb 120.5.12000000.2 2007/04/12 13:28:25 smohapat 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 --
17
18 TYPE g_pos_position_type IS TABLE OF
19 hri_mb_rec_vacancy_ct.pos_position_fk%TYPE
20 INDEX BY BINARY_INTEGER;
21
22 TYPE g_rvac_vacncy_type IS TABLE OF
23 hri_mb_rec_vacancy_ct.rvac_vacncy_fk%TYPE
24 INDEX BY BINARY_INTEGER;
25
26 TYPE g_org_organztn_type IS TABLE OF
27 hri_mb_rec_vacancy_ct.org_organztn_fk%TYPE
28 INDEX BY BINARY_INTEGER;
29
30 TYPE g_org_organztn_mrgd_type IS TABLE OF
31 hri_mb_rec_vacancy_ct.org_organztn_mrgd_fk%TYPE
32 INDEX BY BINARY_INTEGER;
33
34 TYPE g_geo_location_type IS TABLE OF
35 hri_mb_rec_vacancy_ct.geo_location_fk%TYPE
36 INDEX BY BINARY_INTEGER;
37
38 TYPE g_job_job_type IS TABLE OF
39 hri_mb_rec_vacancy_ct.job_job_fk%TYPE
40 INDEX BY BINARY_INTEGER;
41
42 TYPE g_grd_grade_type IS TABLE OF
43 hri_mb_rec_vacancy_ct.grd_grade_fk%TYPE
44 INDEX BY BINARY_INTEGER;
45
46 TYPE g_time_day_vac_end_type IS TABLE OF
47 hri_mb_rec_vacancy_ct.time_day_vac_end_fk%TYPE
48 INDEX BY BINARY_INTEGER;
49
50 TYPE g_per_person_recr_type IS TABLE OF
51 hri_mb_rec_vacancy_ct.per_person_recr_fk%TYPE
52 INDEX BY BINARY_INTEGER;
53
54 TYPE g_per_person_rmgr_type IS TABLE OF
55 hri_mb_rec_vacancy_ct.per_person_rmgr_fk%TYPE
56 INDEX BY BINARY_INTEGER;
57
58 TYPE g_per_person_rsed_type IS TABLE OF
59 hri_mb_rec_vacancy_ct.per_person_rsed_fk%TYPE
60 INDEX BY BINARY_INTEGER;
61
62 TYPE g_per_person_mrgd_type IS TABLE OF
63 hri_mb_rec_vacancy_ct.per_person_mrgd_fk%TYPE
64 INDEX BY BINARY_INTEGER;
65
66 TYPE g_time_day_vac_strt_type IS TABLE OF
67 hri_mb_rec_vacancy_ct.time_day_vac_strt_fk%TYPE
68 INDEX BY BINARY_INTEGER;
69
70 TYPE g_number_of_openings_type IS TABLE OF
71 hri_mb_rec_vacancy_ct.number_of_openings%TYPE
72 INDEX BY BINARY_INTEGER;
73
74 TYPE g_budget_measurement_val_type IS TABLE OF
75 hri_mb_rec_vacancy_ct.budget_measurement_value%TYPE
76 INDEX BY BINARY_INTEGER;
77
78 TYPE g_adt_business_grp_id_type IS TABLE OF
79 hri_mb_rec_vacancy_ct.adt_business_group_id%TYPE
80 INDEX BY BINARY_INTEGER;
81
82 TYPE g_adt_vac_status_code_type IS TABLE OF
83 hri_mb_rec_vacancy_ct.adt_vacancy_status_code%TYPE
84 INDEX BY BINARY_INTEGER;
85
86 TYPE g_adt_budget_type_code_type IS TABLE OF
87 hri_mb_rec_vacancy_ct.adt_budget_type_code%TYPE
88 INDEX BY BINARY_INTEGER;
89
90 TYPE g_adt_vac_cat_code_type IS TABLE OF
91 hri_mb_rec_vacancy_ct.adt_vacancy_category_code%TYPE
92 INDEX BY BINARY_INTEGER;
93
94 TYPE g_sysdate_type IS TABLE OF
95 hri_mb_rec_vacancy_ct.CREATION_DATE %TYPE
96 INDEX BY BINARY_INTEGER;
97
98 TYPE g_user_id_type IS TABLE OF
99 hri_mb_rec_vacancy_ct.LAST_UPDATED_BY %TYPE
100 INDEX BY BINARY_INTEGER;
101
102
103 --
104 -- @@ Code specific to this view/table below ENDS
105 --
106 --
107 -- PLSQL tables representing database table columns
108 --
109 g_pos_position_fk g_pos_position_type;
110 g_rvac_vacncy_fk g_rvac_vacncy_type ;
111 g_org_organztn_fk g_org_organztn_type;
112 g_org_organztn_mrgd_fk g_org_organztn_type;
113 g_geo_location_fk g_geo_location_type;
114 g_job_job_fk g_job_job_type;
115 g_grd_grade_fk g_grd_grade_type;
116 g_time_day_vac_end_fk g_time_day_vac_end_type;
117 g_per_person_recr_fk g_per_person_recr_type;
118 g_per_person_rmgr_fk g_per_person_rmgr_type;
119 g_per_person_rsed_fk g_per_person_rsed_type;
120 g_per_person_mrgd_fk g_per_person_rsed_type;
121 g_time_day_vac_strt_fk g_time_day_vac_strt_type;
122 g_number_of_openings g_number_of_openings_type;
123 g_budget_measurement_value g_budget_measurement_val_type;
124 g_adt_business_group_id g_adt_business_grp_id_type;
125 g_adt_vacancy_status_code g_adt_vac_status_code_type;
126 g_adt_budget_type_code g_adt_budget_type_code_type;
127 g_adt_vacancy_category_code g_adt_vac_cat_code_type;
128 -- WHO Columns
129 g_sysdate g_sysdate_type;
130 g_user_id g_user_id_type;
131 --
132 -- Holds the range for which the collection is to be run.
133 --
134 g_start_date DATE;
135 g_end_date DATE;
136 g_end_of_time DATE;
137 g_full_refresh VARCHAR2(10);
138 --
139 -- The HRI schema
140 --
141 g_schema VARCHAR2(400);
142 --
143 -- Set to true to output to a concurrent log file
144 --
145 g_conc_request_flag BOOLEAN := FALSE;
146 --
147 -- Number of rows bulk processed at a time
148 --
149 g_chunk_size PLS_INTEGER;
150 --
151 -- End of time date
152 --
153 -- CONSTANTS
154 -- =========
155 --
156 -- @@ Code specific to this view/table below
157 -- @@ in the call to hri_bpl_conc_log.get_last_collect_to_date
158 -- @@ change param1/2 to be the concurrent program short name,
159 -- @@ and the target table name respectively.
160 --
161 g_target_table VARCHAR2(30) DEFAULT 'HRI_MB_REC_VACANCY_CT';
162 g_cncrnt_prgrm_shrtnm VARCHAR2(30) DEFAULT 'HRI_MB_REC_VACANCY_CT';
163 --
164 -- @@ Code specific to this view/table below ENDS
165 --
166 -- constants that hold the value that indicates to full refresh or not.
167 --
168 g_is_full_refresh VARCHAR2(5) DEFAULT 'Y';
169 g_not_full_refresh VARCHAR2(5) DEFAULT 'N';
170
171 --
172 -- ----------------------------------------------------------------------------
173 -- Runs given sql statement dynamically
174 -- ----------------------------------------------------------------------------
175 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2)
176 IS
177 BEGIN
178
179 EXECUTE IMMEDIATE p_sql_stmt;
180
181 EXCEPTION WHEN OTHERS THEN
182
183 null;
184
185 END run_sql_stmt_noerr;
186
187 -- -------------------------------------------------------------------------
188 --
189 --
190 -- Inserts row into concurrent program log when the g_conc_request_flag has
191 -- been set to TRUE, otherwise does nothing
192 --
193 PROCEDURE output(p_text VARCHAR2)
194 IS
195 --
196 BEGIN
197 --
198 -- Write to the concurrent request log if called from a concurrent request
199 --
200 IF (g_conc_request_flag = TRUE) THEN
201 --
202 -- Put text to log file
203 --
204 fnd_file.put_line(FND_FILE.log, p_text);
205 --
206 END IF;
207 --
208 END output;
209 --
210 -- -------------------------------------------------------------------------
211 --
212 -- Recovers rows to insert when an exception occurs
213 --
214 PROCEDURE recover_insert_rows(p_stored_rows_to_insert NUMBER) IS
215
216 BEGIN
217 --
218 -- loop through rows still to insert one at a time
219 --
220 FOR i IN 1..p_stored_rows_to_insert LOOP
221 --
222 -- Trap unique constraint errors
223 --
224 BEGIN
225 --
226 -- @@ Code specific to this view/table below
227 -- @@ INTRUCTION TO DEVELOPER:
228 -- @@ 1/ For each column in your view put a column in the insert
229 -- @@ statement below.
230 -- @@ 2/ Prefix each column in the VALUE clause with g_
231 -- @@ 3/ make sure (i) is at the end of each column in the value clause
232 --
233 INSERT INTO hri_mb_rec_vacancy_ct
234 (pos_position_fk
235 ,rvac_vacncy_fk
236 ,org_organztn_fk
237 ,org_organztn_mrgd_fk
238 ,geo_location_fk
239 ,job_job_fk
240 ,grd_grade_fk
241 ,time_day_vac_end_fk
242 ,per_person_recr_fk
243 ,per_person_rmgr_fk
244 ,per_person_rsed_fk
245 ,per_person_mrgd_fk
246 ,time_day_vac_strt_fk
247 ,vac_strt_date
248 ,number_of_openings
249 ,budget_measurement_value
250 ,adt_business_group_id
251 ,adt_vacancy_status_code
252 ,adt_budget_type_code
253 ,adt_vacancy_category_code
254 ,creation_date
255 ,created_by
256 ,last_updated_by
257 ,last_update_login
258 ,last_update_date
259 )
260 VALUES
261 (g_pos_position_fk(i)
262 ,g_rvac_vacncy_fk(i)
263 ,g_org_organztn_fk(i)
264 ,g_org_organztn_mrgd_fk(i)
265 ,g_geo_location_fk(i)
266 ,g_job_job_fk(i)
267 ,g_grd_grade_fk(i)
268 ,g_time_day_vac_end_fk(i)
269 ,g_per_person_recr_fk(i)
270 ,g_per_person_rmgr_fk(i)
271 ,g_per_person_rsed_fk(i)
272 ,g_per_person_mrgd_fk(i)
273 ,g_time_day_vac_strt_fk(i)
274 ,g_time_day_vac_strt_fk(i) -- for vac_start_date
275 ,g_number_of_openings(i)
276 ,g_budget_measurement_value(i)
277 ,g_adt_business_group_id(i)
278 ,g_adt_vacancy_status_code(i)
279 ,g_adt_budget_type_code(i)
280 ,g_adt_vacancy_category_code(i)
281 ,g_sysdate(i)
282 ,g_user_id(i)
283 ,g_user_id(i)
284 ,g_user_id(i)
285 ,g_sysdate(i)
286 );
287 --
288 -- @@Code specific to this view/table below ENDS
289 --
290 EXCEPTION
291 --
292 WHEN OTHERS THEN
293 --
294 -- Probable overlap on date tracked assignment rows
295 --
296 output('Single insert error: ' || to_char(g_rvac_vacncy_fk(i)) ||
297 ' - ' || to_char(g_pos_position_fk(i)));
298 --
299 output(sqlerrm);
300 output(sqlcode);
301 --
302 --
303 END;
304 --
305 END LOOP;
306 --
307 COMMIT;
308 --
309 END recover_insert_rows;
310 --
311 -- -------------------------------------------------------------------------
312 --
313 -- Bulk inserts rows from global temporary table to database table
314 --
315 PROCEDURE bulk_insert_rows(p_stored_rows_to_insert NUMBER) IS
316 --
317 BEGIN
318 --
319 -- insert chunk of rows
320 --
321 -- @@ Code specific to this view/table below
322 -- @@ INTRUCTION TO DEVELOPER:
323 -- @@ 1/ For each column in your view put a column in the insert statement
324 -- below.
325 -- @@ 2/ Prefix each column in the VALUE clause with g_
326 -- @@ 3/ make sure (i) is at the end of each column in the value clause
327 --
328 FORALL i IN 1..p_stored_rows_to_insert
329 INSERT INTO hri_mb_rec_vacancy_ct
330 (pos_position_fk
331 ,rvac_vacncy_fk
332 ,org_organztn_fk
333 ,org_organztn_mrgd_fk
334 ,geo_location_fk
335 ,job_job_fk
336 ,grd_grade_fk
337 ,time_day_vac_end_fk
338 ,per_person_recr_fk
339 ,per_person_rmgr_fk
340 ,per_person_rsed_fk
341 ,per_person_mrgd_fk
342 ,time_day_vac_strt_fk
343 ,vac_strt_date
344 ,number_of_openings
345 ,budget_measurement_value
346 ,adt_business_group_id
347 ,adt_vacancy_status_code
348 ,adt_budget_type_code
349 ,adt_vacancy_category_code
350 ,creation_date
351 ,created_by
352 ,last_updated_by
353 ,last_update_login
354 ,last_update_date
355 )
356 VALUES
357 (g_pos_position_fk(i)
358 ,g_rvac_vacncy_fk(i)
359 ,g_org_organztn_fk(i)
360 ,g_org_organztn_mrgd_fk(i)
361 ,g_geo_location_fk(i)
362 ,g_job_job_fk(i)
363 ,g_grd_grade_fk(i)
364 ,g_time_day_vac_end_fk(i)
365 ,g_per_person_recr_fk(i)
366 ,g_per_person_rmgr_fk(i)
367 ,g_per_person_rsed_fk(i)
368 ,g_per_person_mrgd_fk(i)
369 ,g_time_day_vac_strt_fk(i)
370 ,g_time_day_vac_strt_fk(i) -- for vac_start_date
371 ,g_number_of_openings(i)
372 ,g_budget_measurement_value(i)
373 ,g_adt_business_group_id(i)
374 ,g_adt_vacancy_status_code(i)
375 ,g_adt_budget_type_code(i)
376 ,g_adt_vacancy_category_code(i)
377 ,g_sysdate(i)
378 ,g_user_id(i)
379 ,g_user_id(i)
380 ,g_user_id(i)
381 ,g_sysdate(i)
382 );
383 --
384 -- @@Code specific to this view/table below ENDS
385 --
386 -- commit the chunk of rows
387 --
388 COMMIT;
389 --
390 EXCEPTION
391 --
392 WHEN OTHERS THEN
393 --
394 -- Probable unique constraint error
395 --
396 ROLLBACK;
397 --
398 recover_insert_rows(p_stored_rows_to_insert);
399 --
400 --
401 END bulk_insert_rows;
402 --
403 -- -------------------------------------------------------------------------
404 --
405 -- Loops through table and collects into table structure.
406 --
407 PROCEDURE Incremental_Update IS
408 --
409 BEGIN
410 --
411 -- @@ Code specific to this view/table below
412 -- @@ INTRUCTION TO DEVELOPER:
413 -- @@ 1/ Change the code below to reflect the columns in your view / table
414 -- @@ 2/ Change the FROM, INSERT, DELETE statements to point at the relevant
415 -- @@ source view / table
416 --
417 -- Insert completly new rows
418 --
419 -- log('Doing insert.');
420
421 INSERT INTO hri_mb_rec_vacancy_ct
422 (pos_position_fk
423 ,rvac_vacncy_fk
424 ,org_organztn_fk
425 ,org_organztn_mrgd_fk
426 ,geo_location_fk
427 ,job_job_fk
428 ,grd_grade_fk
429 ,time_day_vac_end_fk
430 ,per_person_recr_fk
431 ,per_person_rmgr_fk
432 ,per_person_rsed_fk
433 ,per_person_mrgd_fk
434 ,time_day_vac_strt_fk
435 ,vac_strt_date
436 ,number_of_openings
437 ,budget_measurement_value
438 ,adt_business_group_id
439 ,adt_vacancy_status_code
440 ,adt_budget_type_code
441 ,adt_vacancy_category_code
442 ,creation_date
443 ,created_by
444 ,last_updated_by
445 ,last_update_login
446 ,last_update_date
447 )
448 SELECT
449 NVL(vac.position_id, -1)
450 ,vac.vacancy_id
451 ,NVL(vac.organization_id, -1)
452 ,NVL(vac.organization_id, vac.business_group_id)
453 ,NVL(vac.location_id, -1)
454 ,NVL(vac.job_id, -1)
455 ,NVL(vac.grade_id, -1)
456 ,NVL(vac.date_to, g_end_of_time)
457 ,NVL(vac.recruiter_id,-1)
458 ,NVL(vac.manager_id, -1)
459 ,NVL(req.person_id, -1)
460 ,hri_opl_rec_cand_pipln.get_merged_person_fk
461 (NVL(vac.manager_id, -1)
462 ,NVL(vac.recruiter_id, -1)
463 ,NVL(req.person_id, -1)
464 ,NVL(vac.organization_id, -1)
465 ,vac.business_group_id)
466 ,vac.date_from
467 ,vac.date_from -- for vac_strt_date
468 ,vac.number_of_openings
469 ,vac.budget_measurement_value
470 ,vac.business_group_id
471 ,vac.status
472 ,vac.budget_measurement_type
473 ,vac.vacancy_category
474 ,sysdate
475 ,fnd_global.user_id
476 ,fnd_global.user_id
477 ,fnd_global.user_id
478 ,sysdate
479 FROM
480 per_all_vacancies vac
481 ,per_requisitions req
482 WHERE vac.last_update_date BETWEEN g_start_date AND g_end_date
483 AND vac.requisition_id = req.requisition_id
484 AND NOT EXISTS (SELECT 'x'
485 FROM hri_mb_rec_vacancy_ct tbl
486 WHERE vac.vacancy_id = tbl.rvac_vacncy_fk);
487
488
489
490 -- log('Insert >'||TO_CHAR(sql%rowcount));
491 -- log('Doing update.');
492 --
493
494 UPDATE hri_mb_rec_vacancy_ct tbl
495 SET (pos_position_fk
496 ,rvac_vacncy_fk
497 ,org_organztn_fk
498 ,org_organztn_mrgd_fk
499 ,geo_location_fk
500 ,job_job_fk
501 ,grd_grade_fk
502 ,time_day_vac_end_fk
503 ,per_person_recr_fk
504 ,per_person_rmgr_fk
505 ,per_person_rsed_fk
506 ,per_person_mrgd_fk
507 ,time_day_vac_strt_fk
508 ,vac_strt_date
509 ,number_of_openings
510 ,budget_measurement_value
511 ,adt_business_group_id
512 ,adt_vacancy_status_code
513 ,adt_budget_type_code
514 ,adt_vacancy_category_code
515 ,creation_date
516 ,created_by
517 ,last_updated_by
518 ,last_update_login
519 ,last_update_date
520 ) =
521 (SELECT NVL(vac.position_id, -1)
522 ,vac.vacancy_id
523 ,NVL(vac.organization_id, -1)
524 ,NVL(vac.organization_id, vac.business_group_id)
525 ,NVL(vac.location_id, -1)
526 ,NVL(vac.job_id, -1)
527 ,NVL(vac.grade_id, -1)
528 ,NVL(vac.date_to, g_end_of_time)
529 ,NVL(vac.recruiter_id,-1)
530 ,NVL(vac.manager_id, -1)
531 ,NVL(req.person_id, -1)
532 ,hri_opl_rec_cand_pipln.get_merged_person_fk
533 (NVL(vac.manager_id, -1)
534 ,NVL(vac.recruiter_id, -1)
535 ,NVL(req.person_id, -1)
536 ,NVL(vac.organization_id, -1)
537 ,vac.business_group_id)
538 ,vac.date_from
539 ,vac.date_from
540 ,vac.number_of_openings
541 ,vac.budget_measurement_value
542 ,vac.business_group_id
543 ,vac.status
544 ,vac.budget_measurement_type
545 ,vac.vacancy_category
546 ,sysdate
547 ,fnd_global.user_id
548 ,fnd_global.user_id
549 ,fnd_global.user_id
550 ,sysdate
551 FROM
552 per_all_vacancies vac
553 ,per_requisitions req
554 WHERE vac.last_update_date BETWEEN g_start_date
555 AND g_end_date
556 AND req.requisition_id = vac.requisition_id
557 AND vac.vacancy_id = tbl.rvac_vacncy_fk
558 )
559 WHERE (tbl.rvac_vacncy_fk)
560 IN
561 (SELECT vac.vacancy_id
562 FROM per_all_vacancies vac
563 WHERE vac.last_update_date BETWEEN g_start_date
564 AND g_end_date);
565
566 --
567 -- log('Update >'||TO_CHAR(sql%rowcount));
568 --
569 -- Delete rows that no longer exist in the source view.
570 --
571 -- log('Doing delete.');
572
573 DELETE
574 FROM hri_mb_rec_vacancy_ct tbl
575 WHERE tbl.rvac_vacncy_fk > 0
576 AND NOT EXISTS
577 (SELECT 'x'
578 FROM per_all_vacancies vac
579 WHERE vac.vacancy_id = tbl.rvac_vacncy_fk);
580
581
582 -- log('Delete >'||TO_CHAR(sql%rowcount));
583 --
584 -- @@ Code specific to this view/table below ENDS
585 --
586 COMMIT ;
587 -- log('Done incremental update.');
588 --
589 EXCEPTION
590 --
591 WHEN OTHERS THEN
592 --
593 Output('Failure in incremental update process.');
594 --
595 RAISE;
596 --
597 --
598 END;
599 --
600 -- -------------------------------------------------------------------------
601 --
602 --
603 -- Loops through table and collects into table structure.
604 --
605 PROCEDURE Full_Refresh IS
606 --
607 -- Select all from the source view for materialization
608 --
609 -- @@ Code specific to this view/table below
610 -- @@ INTRUCTION TO DEVELOPER:
611 -- @@ 1/ Change the select beloe to select all the columns from your view
612 -- @@ 2/ Change the FROM statement to point at the relevant source view
613 --
614 CURSOR source_view_csr IS
615 SELECT NVL(vac.position_id, -1)
616 ,vac.vacancy_id
617 ,NVL(vac.organization_id, -1)
618 ,NVL(vac.organization_id, vac.business_group_id)
619 ,NVL(vac.location_id, -1)
620 ,NVL(vac.job_id, -1)
621 ,NVL(vac.grade_id, -1)
622 ,NVL(vac.date_to, g_end_of_time)
623 ,NVL(vac.recruiter_id,-1)
624 ,NVL(vac.manager_id, -1)
625 ,NVL(req.person_id, -1)
626 ,hri_opl_rec_cand_pipln.get_merged_person_fk
627 (NVL(vac.manager_id, -1)
628 ,NVL(vac.recruiter_id, -1)
629 ,NVL(req.person_id, -1)
630 ,NVL(vac.organization_id, -1)
631 ,vac.business_group_id)
632 ,vac.date_from
633 ,vac.number_of_openings
634 ,vac.budget_measurement_value
635 ,vac.business_group_id
636 ,vac.status
637 ,vac.budget_measurement_type
638 ,vac.vacancy_category
639 ,sysdate
640 ,fnd_global.user_id
641 FROM
642 per_all_vacancies vac
643 ,per_requisitions req
644 WHERE req.requisition_id = vac.requisition_id;
645
646 --
647 -- @@Code specific to this view/table below ENDS
648 --
649 l_exit_main_loop BOOLEAN := FALSE;
650 l_rows_fetched PLS_INTEGER := g_chunk_size;
651 l_sql_stmt VARCHAR2(2000);
652 --
653 BEGIN
654 -- log('here ...');
655 --
656 -- Truncate the target table prior to full refresh.
657 --
658 l_sql_stmt := 'TRUNCATE TABLE ' || g_schema || '.'||g_target_table;
659 -- log('>'||l_sql_stmt||'<');
660 --
661 EXECUTE IMMEDIATE(l_sql_stmt);
662 -- log('truncateed ...');
663
664 --Disable WHO TRIGGERS on table prior to full refresh
665
666 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_REC_VACANCY_CT_WHO DISABLE');
667
668 -- Drop all the INDEXES on the table
669 hri_utl_ddl.log_and_drop_indexes
670 (p_application_short_name => 'HRI',
671 p_table_name => 'HRI_MB_REC_VACANCY_CT',
672 p_table_owner => g_schema);
673
674
675 --Disable INDEX on table prior to full refresh
676
677
678 --
679 -- Write timing information to log
680 --
681 output('Truncated the table: ' ||
682 to_char(sysdate,'HH24:MI:SS'));
683
684
685 --
686 -- Write timing information to log
687 --
688 output('Truncated the table: ' ||
689 to_char(sysdate,'HH24:MI:SS'));
690 --
691 -- open main cursor
692 --
693 -- log('open cursor ...');
694 OPEN source_view_csr;
695 --
696 <<main_loop>>
697 LOOP
698 --
699 -- bulk fetch rows limit the fetch to value of g_chunk_size
700 --
701 -- @@ Code specific to this view/table below
702 -- @@ INTRUCTION TO DEVELOPER:
703 -- @@ Change the bulk collect below to select all the columns from your
704 -- @@ view
705 --
706 -- log('start fetch ...');
707 -- log('>'||TO_CHAR(g_chunk_size)||'<');
708 FETCH source_view_csr
709 BULK COLLECT INTO
710 g_pos_position_fk
711 ,g_rvac_vacncy_fk
712 ,g_org_organztn_fk
713 ,g_org_organztn_mrgd_fk
714 ,g_geo_location_fk
715 ,g_job_job_fk
716 ,g_grd_grade_fk
717 ,g_time_day_vac_end_fk
718 ,g_per_person_recr_fk
719 ,g_per_person_rmgr_fk
720 ,g_per_person_rsed_fk
721 ,g_per_person_mrgd_fk
722 ,g_time_day_vac_strt_fk
723 ,g_number_of_openings
724 ,g_budget_measurement_value
725 ,g_adt_business_group_id
726 ,g_adt_vacancy_status_code
727 ,g_adt_budget_type_code
728 ,g_adt_vacancy_category_code
729 ,g_sysdate
730 ,g_user_id
731 LIMIT g_chunk_size;
732
733
734 -- log('finish fetch ...');
735 --
736 -- @@Code specific to this view/table below ENDS
737 --
738 -- check to see if the last row has been fetched
739 --
740 IF source_view_csr%NOTFOUND THEN
741 --
742 -- last row fetched, set exit loop flag
743 --
744 l_exit_main_loop := TRUE;
745 --
746 -- do we have any rows to process?
747 --
748 l_rows_fetched := MOD(source_view_csr%ROWCOUNT,g_chunk_size);
749 --
750 -- note: if l_rows_fetched > 0 then more rows are required to be
751 -- processed and the l_rows_fetched will contain the exact number of
752 -- rows left to process
753 --
754 IF l_rows_fetched = 0 THEN
755 --
756 -- no more rows to process so exit loop
757 --
758 EXIT main_loop;
759 END IF;
760 END IF;
761 --
762 -- bulk insert rows processed so far
763 --
764 -- log('call bulk ...');
765 bulk_insert_rows (l_rows_fetched);
766 -- log('end bulk ...');
767 --
768 -- exit loop if required
769 --
770 IF l_exit_main_loop THEN
771 --
772 EXIT main_loop;
773 --
774 END IF;
775 --
776 END LOOP;
777 --
778 CLOSE source_view_csr;
779 --
780 -- Insert an Unassigned Row.
781 --
782 insert into hri_mb_rec_vacancy_ct
783 ( time_day_vac_strt_fk
784 ,vac_strt_date
785 ,time_day_vac_end_fk
786 ,per_person_recr_fk
787 ,per_person_rmgr_fk
788 ,per_person_rsed_fk
789 ,per_person_mrgd_fk
790 ,org_organztn_fk
791 ,org_organztn_mrgd_fk
792 ,geo_location_fk
793 ,job_job_fk
794 ,grd_grade_fk
795 ,pos_position_fk
796 ,rvac_vacncy_fk
797 ,number_of_openings
798 ,budget_measurement_value
799 ,adt_business_group_id
800 ,adt_vacancy_status_code
801 ,adt_budget_type_code
802 ,adt_vacancy_category_code
803 ,creation_date
804 ,created_by
805 ,last_updated_by
806 ,last_update_login
807 ,last_update_date)
808 values
809 ( hr_general.start_of_time
810 ,to_date(null)
811 ,hr_general.end_of_time
812 ,-1
813 ,-1
814 ,-1
815 ,-1
816 ,-1
817 ,-1
818 ,-1
819 ,-1
820 ,-1
821 ,-1
822 ,-1
823 ,NULL
824 ,NULL
825 ,-1
826 ,hri_oltp_view_message.get_unassigned_msg
827 ,hri_oltp_view_message.get_unassigned_msg
828 ,hri_oltp_view_message.get_unassigned_msg
829 ,sysdate
830 ,fnd_global.user_id
831 ,fnd_global.user_id
832 ,fnd_global.user_id
833 ,sysdate
834 );
835
836
837 --Enable WHO TRIGGERS
838
839 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_REC_VACANCY_CT_WHO ENABLE');
840
841 --Enable INDEX
842
843 hri_utl_ddl.recreate_indexes
844 (p_application_short_name => 'HRI',
845 p_table_name => 'HRI_MB_REC_VACANCY_CT',
846 p_table_owner => g_schema);
847
848 -- log('End ...');
849 EXCEPTION
850 WHEN OTHERS THEN
851 --
852 -- unexpected error has occurred so close down
853 -- main bulk cursor if it is open
854 --
855 IF source_view_csr%ISOPEN THEN
856 --
857 CLOSE source_view_csr;
858 --
859 END IF;
860 --
861 -- re-raise error
862 RAISE;
863 --
864 --
865 END Full_Refresh;
866 --
867 -- -------------------------------------------------------------------------
868 -- Checks what mode you are running in, and if g_full_refresh =
869 -- g_is_full_refresh calls
870 -- Full_Refresh procedure, otherwise Incremental_Update is called.
871 --
872 PROCEDURE Collect IS
873 --
874 BEGIN
875 --
876 -- If in full refresh mode chnage the dates so that the collection history
877 -- is correctly maintained.
878 --
879 IF g_full_refresh = g_is_full_refresh THEN
880 --
881 g_start_date := hr_general.start_of_time;
882 g_end_date := SYSDATE;
883 --
884 -- log('Doing full refresh.');
885 Full_Refresh;
886 --
887 ELSE
888 --
889 -- log('Doing incremental update.');
890 --
891 -- If the passed in date range is NULL default it.
892 --
893 IF g_start_date IS NULL OR
894 g_end_date IS NULL
895 THEN
896 -- log('Input dates NULL.');
897 --
898 g_start_date := fnd_date.displaydt_to_date(
899 hri_bpl_conc_log.get_last_collect_to_date(
900 g_cncrnt_prgrm_shrtnm
901 ,g_target_table));
902 --
903 g_end_date := SYSDATE;
904 -- log('start >'||TO_CHAR(g_start_date));
905 -- log('end >'||TO_CHAR(g_end_date));
906 -- log('Defaulted input DATES.');
907 --
908 END IF;
909 --
910 -- log('Calling incremenatal update.');
911 Incremental_Update;
912 -- log('Called incremenatal update.');
913 --
914 END IF;
915 --
916 END Collect;
917 --
918 -- -------------------------------------------------------------------------
919 -- Checks if the Target table is Empty
920 --
921 FUNCTION Target_table_is_Empty RETURN BOOLEAN IS
922 --
923 -- @@ Code specific to this view/table below
924 -- @@ INTRUCTION TO DEVELOPER:
925 -- @@ Change the table in the FROM clause below to be the same as your
926 -- @@ target table.
927 --
928 CURSOR csr_recs_exist IS
929 SELECT 'x'
930 FROM hri_mb_rec_vacancy_ct;
931 --
932 -- @@ Code specific to this view/table ENDS
933 --
934 l_exists_chr VARCHAR2(1);
935 l_exists BOOLEAN;
936 --
937 BEGIN
938 --
939 OPEN csr_recs_exist;
940 --
941 FETCH csr_recs_exist INTO l_exists_chr;
942 --
943 IF (csr_recs_exist%NOTFOUND)
944 THEN
945 --
946 l_exists := TRUE;
947 -- log('no data in table');
948 --
949 ELSE
950 --
951 l_exists := FALSE;
952 -- log('data is in table');
953 --
954 END IF;
955 --
956 CLOSE csr_recs_exist;
957 --
958 RETURN l_exists;
959 --
960 EXCEPTION
961 --
962 WHEN OTHERS
963 THEN
964 --
965 CLOSE csr_recs_exist;
966 RAISE;
967 --
968 --
969 END Target_table_is_Empty;
970 --
971 -- -------------------------------------------------------------------------
972 --
973 -- Main entry point to load the table.
974 --
975 PROCEDURE Load(p_chunk_size IN NUMBER,
976 p_start_date IN VARCHAR2,
977 p_end_date IN VARCHAR2,
978 p_full_refresh IN VARCHAR2) IS
979 --
980 -- Variables required for table truncation.
981 --
982 l_dummy1 VARCHAR2(2000);
983 l_dummy2 VARCHAR2(2000);
984 --
985 BEGIN
986 --
987 output('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
988 --
989 -- Set globals
990 --
991 g_start_date := fnd_date.canonical_to_date(p_start_date);
992 g_end_date := fnd_date.canonical_to_date(p_end_date);
993 --
994 IF p_chunk_size IS NULL
995 THEN
996 --
997 g_chunk_size := 1500;
998 --
999 ELSE
1000 --
1001 g_chunk_size := p_chunk_size;
1002 --
1003 END IF;
1004 --
1005 IF p_full_refresh IS NULL
1006 THEN
1007 --
1008 g_full_refresh := g_not_full_refresh;
1009 --
1010 ELSE
1011 --
1012 g_full_refresh := p_full_refresh;
1013 --
1014 END IF;
1015 --
1016 -- If the target table is empty default to full refresh.
1017 --
1018 IF Target_table_is_Empty
1019 THEN
1020 --
1021 output('Target table '||g_target_table||
1022 ' is empty, so doing a full refresh.');
1023 -- log('Doing a full refresh....');
1024 --
1025 g_full_refresh := g_is_full_refresh;
1026 --
1027 END IF;
1028 --
1029 -- log('p_chunk_size>'||TO_CHAR(g_chunk_size)||'<');
1030 -- Find the schema we are running in.
1031 --
1032 IF NOT fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, g_schema)
1033 THEN
1034 --
1035 -- Could not find the schema raising exception.
1036 --
1037 output('Could not find schema to run in.');
1038 --
1039 -- log('Could not find schema.');
1040 RAISE NO_DATA_FOUND;
1041 --
1042 END IF;
1043 --
1044 -- Update information about collection
1045 --
1046 -- log('Record process start.');
1047 /* double check correct val passed in below */
1048 hri_bpl_conc_log.record_process_start(g_cncrnt_prgrm_shrtnm);
1049 --
1050 -- Time at start
1051 --
1052 -- log('collect.');
1053 --
1054 -- Get HRI schema name - get_app_info populates l_schema
1055 --
1056 -- Insert new records
1057 --
1058 collect;
1059 -- log('collectED.');
1060 --
1061 -- Write timing information to log
1062 --
1063 output('Finished changes to the table: ' ||
1064 to_char(sysdate,'HH24:MI:SS'));
1065 --
1066 -- Gather index stats
1067 --
1068 -- log('gather stats.');
1069 fnd_stats.gather_table_stats(g_schema, g_target_table);
1070 --
1071 -- Write timing information to log
1072 --
1073 output('Gathered stats: ' ||
1074 to_char(sysdate,'HH24:MI:SS'));
1075 --
1076 -- log('log end.');
1077 hri_bpl_conc_log.log_process_end(
1078 p_status => TRUE,
1079 p_period_from => TRUNC(g_start_date),
1080 p_period_to => TRUNC(g_end_date),
1081 p_attribute1 => p_full_refresh,
1082 p_attribute2 => p_chunk_size);
1083 -- log('-END-');
1084 --
1085 EXCEPTION
1086 --
1087 WHEN OTHERS
1088 THEN
1089 --
1090 ROLLBACK;
1091 RAISE;
1092 --
1093 --
1094 END Load;
1095 --
1096 -- -------------------------------------------------------------------------
1097 --
1098 -- Entry point to be called from the concurrent manager
1099 --
1100 PROCEDURE Load(errbuf OUT NOCOPY VARCHAR2,
1101 retcode OUT NOCOPY VARCHAR2)
1102 IS
1103 --
1104 l_full_refresh VARCHAR2(30);
1105 l_start_date VARCHAR2(40);
1106 --
1107 BEGIN
1108 --
1109 -- Enable output to concurrent request log
1110 --
1111 g_conc_request_flag := TRUE;
1112 g_end_of_time := hr_general.end_of_time;
1113 --
1114 -- Set parameters
1115 --
1116 l_full_refresh := hri_oltp_conc_param.get_parameter_value
1117 (p_parameter_name => 'FULL_REFRESH',
1118 p_process_table_name => 'HRI_MB_REC_VACANCY_CT');
1119 --
1120 -- Set the refresh start date
1121 --
1122 IF (l_full_refresh = 'Y') THEN
1123 l_start_date := hri_oltp_conc_param.get_parameter_value
1124 (p_parameter_name => 'FULL_REFRESH_FROM_DATE',
1125 p_process_table_name => 'HRI_MB_REC_VACANCY_CT');
1126 ELSE
1127 l_start_date := fnd_date.date_to_canonical
1128 (fnd_date.displaydt_to_date
1129 (hri_bpl_conc_log.get_last_collect_to_date
1130 ('HRI_MB_REC_VACANCY_CT','HRI_MB_REC_VACANCY_CT')));
1131 END IF;
1132
1133 load(p_chunk_size => to_number(null),
1134 p_start_date => l_start_date,
1135 p_end_date => fnd_date.date_to_canonical(sysdate),
1136 p_full_refresh => l_full_refresh);
1137 --
1138 EXCEPTION
1139 --
1140 WHEN OTHERS THEN
1141 --
1142 errbuf := SQLERRM;
1143 retcode := SQLCODE;
1144 --
1145 --
1146 END load;
1147 --
1148 END HRI_OPL_REC_VAC;