[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_PER_PERSON
Source
1 PACKAGE BODY HRI_OPL_PER_PERSON AS
2 /* $Header: hripperdim.pkb 120.0.12000000.2 2007/04/12 13:27:38 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
19 TYPE g_per_work_phone_type
20 IS TABLE OF hri_cs_per_person_ct.per_work_phone%TYPE
21 INDEX BY BINARY_INTEGER;
22
23 TYPE g_per_work_location_type
24 IS TABLE OF hri_cs_per_person_ct.per_work_location%TYPE
25 INDEX BY BINARY_INTEGER;
26
27 TYPE g_adt_ppf_person_id_type
28 IS TABLE OF hri_cs_per_person_ct.adt_ppf_person_id%TYPE
29 INDEX BY BINARY_INTEGER;
30
31 TYPE g_adt_ppf_eff_start_date_type
32 IS TABLE OF hri_cs_per_person_ct.adt_ppf_effctv_start_date%TYPE
33 INDEX BY BINARY_INTEGER;
34
35 TYPE g_adt_ppf_eff_end_date_type
36 IS TABLE OF hri_cs_per_person_ct.adt_ppf_effctv_end_date%TYPE
37 INDEX BY BINARY_INTEGER;
38
39 TYPE g_per_buyer_flag_code_type
40 IS TABLE OF hri_cs_per_person_ct.per_buyer_flag_code%TYPE
41 INDEX BY BINARY_INTEGER;
42
43 TYPE g_per_date_of_birth_type
44 IS TABLE OF hri_cs_per_person_ct.per_date_of_birth%TYPE
45 INDEX BY BINARY_INTEGER;
46
47 TYPE g_per_middle_names_type
48 IS TABLE OF hri_cs_per_person_ct.per_middle_names%TYPE
49 INDEX BY BINARY_INTEGER;
50
51 TYPE g_per_known_as_type
52 IS TABLE OF hri_cs_per_person_ct.per_known_as%TYPE
53 INDEX BY BINARY_INTEGER;
54
55 TYPE g_per_honors_type
56 IS TABLE OF hri_cs_per_person_ct.per_honors%TYPE
57 INDEX BY BINARY_INTEGER;
58
59 TYPE g_per_pre_name_adjunct_type
60 IS TABLE OF hri_cs_per_person_ct.per_pre_name_adjunct%TYPE
61 INDEX BY BINARY_INTEGER;
62
63 TYPE g_per_apl_number_type
64 IS TABLE OF hri_cs_per_person_ct.per_apl_number%TYPE
65 INDEX BY BINARY_INTEGER;
66
67 TYPE g_per_emp_number_type
68 IS TABLE OF hri_cs_per_person_ct.per_emp_number%TYPE
69 INDEX BY BINARY_INTEGER;
70
71 TYPE g_per_cwk_number_type
72 IS TABLE OF hri_cs_per_person_ct.per_cwk_number%TYPE
73 INDEX BY BINARY_INTEGER;
74
75 TYPE g_per_apl_flag_crnt_code_type
76 IS TABLE OF hri_cs_per_person_ct.per_apl_flag_crnt_code%TYPE
77 INDEX BY BINARY_INTEGER;
78
79
80 TYPE g_per_emp_flag_crnt_code_type
81 IS TABLE OF hri_cs_per_person_ct.per_emp_flag_crnt_code%TYPE
82 INDEX BY BINARY_INTEGER;
83
84
85 TYPE g_per_cwk_flag_crnt_code_type
86 IS TABLE OF hri_cs_per_person_ct.per_cwk_flag_crnt_code%TYPE
87 INDEX BY BINARY_INTEGER;
88
89 TYPE g_per_person_name_lcl_type
90 IS TABLE OF hri_cs_per_person_ct.per_person_name_lcl%TYPE
91 INDEX BY BINARY_INTEGER;
92
93 TYPE g_per_first_name_type
94 IS TABLE OF hri_cs_per_person_ct.per_first_name%TYPE
95 INDEX BY BINARY_INTEGER;
96
97 TYPE g_per_place_of_birth_type
98 IS TABLE OF hri_cs_per_person_ct.per_place_of_birth%TYPE
99 INDEX BY BINARY_INTEGER;
100
101 TYPE g_per_last_name_prev_type
102 IS TABLE OF hri_cs_per_person_ct.per_last_name_prev%TYPE
103 INDEX BY BINARY_INTEGER;
104
105 TYPE g_per_order_by_type
106 IS TABLE OF hri_cs_per_person_ct.per_order_by%TYPE
107 INDEX BY BINARY_INTEGER;
108
109 TYPE g_per_person_name_gbl_type
110 IS TABLE OF hri_cs_per_person_ct.per_person_name_gbl%TYPE
111 INDEX BY BINARY_INTEGER;
112
113 TYPE g_per_last_name_type
114 IS TABLE OF hri_cs_per_person_ct.per_last_name%TYPE
115 INDEX BY BINARY_INTEGER;
116
117 TYPE g_per_wrkr_crnt_flag_code_type
118 IS TABLE OF hri_cs_per_person_ct.per_worker_crnt_flag_code%TYPE
119 INDEX BY BINARY_INTEGER;
120
121 TYPE g_per_country_of_birth_type
122 IS TABLE OF hri_cs_per_person_ct.per_country_of_birth%TYPE
123 INDEX BY BINARY_INTEGER;
124
125 TYPE g_per_date_of_death_type
126 IS TABLE OF hri_cs_per_person_ct.per_date_of_death%TYPE
127 INDEX BY BINARY_INTEGER;
128
129 TYPE g_per_work_email_type
130 IS TABLE OF hri_cs_per_person_ct.per_work_email%TYPE
131 INDEX BY BINARY_INTEGER;
132
133 TYPE g_per_title_type
134 IS TABLE OF hri_cs_per_person_ct.per_title%TYPE
135 INDEX BY BINARY_INTEGER;
136
137 TYPE g_per_suffix_type
138 IS TABLE OF hri_cs_per_person_ct.per_suffix%TYPE
139 INDEX BY BINARY_INTEGER;
140
141 TYPE g_per_person_name_type
142 IS TABLE OF hri_cs_per_person_ct.per_person_name%TYPE
143 INDEX BY BINARY_INTEGER;
144
145 TYPE g_per_person_pk_type
146 IS TABLE OF hri_cs_per_person_ct.per_person_pk%TYPE
147 INDEX BY BINARY_INTEGER;
148
149 TYPE g_per_marital_status_crnt_type
150 IS TABLE OF hri_cs_per_person_ct.per_marital_status_crnt%TYPE
151 INDEX BY BINARY_INTEGER;
152
153 TYPE g_per_gender_crnt_type
154 IS TABLE OF hri_cs_per_person_ct.per_gender_crnt%TYPE
155 INDEX BY BINARY_INTEGER;
156
157
158
159 --
160 -- @@ Code specific to this view/table below ENDS
161 --
162 --
163 -- PLSQL tables representing database table columns
164 --
165
166 g_per_work_phone g_per_work_phone_type ;
167 g_per_work_location g_per_work_location_type ;
168 g_adt_ppf_person_id g_adt_ppf_person_id_type ;
169 g_adt_ppf_effctv_start_date g_adt_ppf_eff_start_date_type ;
170 g_adt_ppf_effctv_end_date g_adt_ppf_eff_end_date_type ;
171 g_per_buyer_flag_code g_per_buyer_flag_code_type ;
172 g_per_date_of_birth g_per_date_of_birth_type ;
173 g_per_middle_names g_per_middle_names_type ;
174 g_per_known_as g_per_known_as_type ;
175 g_per_honors g_per_honors_type ;
176 g_per_pre_name_adjunct g_per_pre_name_adjunct_type ;
177 g_per_apl_number g_per_apl_number_type ;
178 g_per_emp_number g_per_emp_number_type ;
179 g_per_cwk_number g_per_cwk_number_type ;
180 g_per_apl_flag_crnt_code g_per_apl_flag_crnt_code_type ;
181 g_per_emp_flag_crnt_code g_per_emp_flag_crnt_code_type ;
182 g_per_cwk_flag_crnt_code g_per_cwk_flag_crnt_code_type ;
183 g_per_person_name_lcl g_per_person_name_lcl_type ;
184 g_per_first_name g_per_first_name_type ;
185 g_per_place_of_birth g_per_place_of_birth_type ;
186 g_per_last_name_prev g_per_last_name_prev_type ;
187 g_per_order_by g_per_order_by_type ;
188 g_per_person_name_gbl g_per_person_name_gbl_type ;
189 g_per_last_name g_per_last_name_type ;
190 g_per_worker_crnt_flag_code g_per_wrkr_crnt_flag_code_type ;
191 g_per_country_of_birth g_per_country_of_birth_type ;
192 g_per_date_of_death g_per_date_of_death_type ;
193 g_per_work_email g_per_work_email_type ;
194 g_per_title g_per_title_type ;
195 g_per_suffix g_per_suffix_type ;
196 g_per_person_name g_per_person_name_type ;
197 g_per_person_pk g_per_person_pk_type ;
198 g_per_marital_status_crnt g_per_marital_status_crnt_type;
199 g_per_gender_crnt g_per_gender_crnt_type;
200
201
202 --
203 -- Holds the range for which the collection is to be run.
204 --
205 g_start_date DATE;
206 g_end_date DATE;
207 g_full_refresh VARCHAR2(10);
208 --
209 -- The HRI schema
210 --
211 g_schema VARCHAR2(400);
212 --
213 -- Set to true to output to a concurrent log file
214 --
215 g_conc_request_flag BOOLEAN := FALSE;
216 --
217 -- Number of rows bulk processed at a time
218 --
219 g_chunk_size PLS_INTEGER;
220 --
221 -- End of time date
222 --
223 -- CONSTANTS
224 -- =========
225 --
226 -- @@ Code specific to this view/table below
227 -- @@ in the call to hri_bpl_conc_log.get_last_collect_to_date
228 -- @@ change param1/2 to be the concurrent program short name,
229 -- @@ and the target table name respectively.
230 --
231 g_target_table VARCHAR2(30) DEFAULT 'hri_cs_per_person_ct';
232 g_cncrnt_prgrm_shrtnm VARCHAR2(30) DEFAULT 'HRI_CS_PER_PERSON_CT';
233 --
234 -- @@ Code specific to this view/table below ENDS
235 --
236 -- constants that hold the value that indicates to full refresh or not.
237 --
238 g_is_full_refresh VARCHAR2(5) DEFAULT 'Y';
239 g_not_full_refresh VARCHAR2(5) DEFAULT 'N';
240
241 --
242 -- WHO Column data
243 --
244
245 g_sysdate DATE := sysdate;
246 g_user NUMBER(10):= fnd_global.user_id;
247
248 --
249 -- ----------------------------------------------------------------------------
250 -- Runs given sql statement dynamically
251 -- ----------------------------------------------------------------------------
252 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2)
253 IS
254 BEGIN
255
256 EXECUTE IMMEDIATE p_sql_stmt;
257
258 EXCEPTION WHEN OTHERS THEN
259
260 null;
261
262 END run_sql_stmt_noerr;
263
264 -- -------------------------------------------------------------------------
265 --
266 -- Inserts row into concurrent program log when the g_conc_request_flag has
267 -- been set to TRUE, otherwise does nothing
268 --
269
270 PROCEDURE output(p_text VARCHAR2)
271 IS
272 --
273 BEGIN
274 --
275 -- Write to the concurrent request log if called from a concurrent request
276 --
277 IF (g_conc_request_flag = TRUE) THEN
278 --
279 -- Put text to log file
280 --
281 fnd_file.put_line(FND_FILE.log, p_text);
282 --
283 END IF;
284 --
285 END output;
286 --
287 -- -------------------------------------------------------------------------
288 --
289 -- Recovers rows to insert when an exception occurs
290 --
291 PROCEDURE recover_insert_rows(p_stored_rows_to_insert NUMBER) IS
292
293 BEGIN
294 --
295 -- loop through rows still to insert one at a time
296 --
297 FOR i IN 1..p_stored_rows_to_insert LOOP
298 --
299 -- Trap unique constraint errors
300 --
301 BEGIN
302 --
303 -- @@ Code specific to this view/table below
304 -- @@ INTRUCTION TO DEVELOPER:
305 -- @@ 1/ For each column in your view put a column in the insert
306 -- @@ statement below.
307 -- @@ 2/ Prefix each column in the VALUE clause with g_
308 -- @@ 3/ make sure (i) is at the end of each column in the value clause
309 --
310
311 INSERT INTO hri_cs_per_person_ct(
312 per_work_phone
313 ,per_work_location
314 ,adt_ppf_person_id
315 ,adt_ppf_effctv_start_date
316 ,adt_ppf_effctv_end_date
317 ,per_buyer_flag_code
318 ,per_date_of_birth
319 ,per_middle_names
320 ,per_known_as
321 ,per_honors
322 ,per_pre_name_adjunct
323 ,per_apl_number
324 ,per_emp_number
325 ,per_cwk_number
326 ,per_apl_flag_crnt_code
327 ,per_emp_flag_crnt_code
328 ,per_cwk_flag_crnt_code
329 ,per_person_name_lcl
330 ,per_first_name
331 ,per_place_of_birth
332 ,per_last_name_prev
333 ,per_order_by
334 ,per_person_name_gbl
335 ,per_last_name
336 ,per_worker_crnt_flag_code
337 ,per_country_of_birth
338 ,per_date_of_death
339 ,per_work_email
340 ,per_title
341 ,per_suffix
342 ,per_person_name
343 ,per_person_pk
344 ,per_marital_status_crnt
345 ,per_gender_crnt
346 )
347 VALUES( g_per_work_phone(i)
348 ,g_per_work_location(i)
349 ,g_adt_ppf_person_id(i)
350 ,g_adt_ppf_effctv_start_date(i)
351 ,g_adt_ppf_effctv_end_date(i)
352 ,g_per_buyer_flag_code(i)
353 ,g_per_date_of_birth(i)
354 ,g_per_middle_names(i)
355 ,g_per_known_as(i)
356 ,g_per_honors(i)
357 ,g_per_pre_name_adjunct(i)
358 ,g_per_apl_number(i)
359 ,g_per_emp_number(i)
360 ,g_per_cwk_number(i)
361 ,g_per_apl_flag_crnt_code(i)
362 ,g_per_emp_flag_crnt_code(i)
363 ,g_per_cwk_flag_crnt_code(i)
364 ,g_per_person_name_lcl(i)
365 ,g_per_first_name(i)
366 ,g_per_place_of_birth(i)
367 ,g_per_last_name_prev(i)
368 ,g_per_order_by(i)
369 ,g_per_person_name_gbl(i)
370 ,g_per_last_name(i)
371 ,g_per_worker_crnt_flag_code(i)
372 ,g_per_country_of_birth(i)
373 ,g_per_date_of_death(i)
374 ,g_per_work_email(i)
375 ,g_per_title(i)
376 ,g_per_suffix(i)
377 ,g_per_person_name(i)
378 ,g_per_person_pk(i)
379 ,g_per_marital_status_crnt(i)
380 ,g_per_gender_crnt(i)
381 );
382 --
383 -- @@Code specific to this view/table below ENDS
384 --
385 EXCEPTION
386 --
387 WHEN OTHERS THEN
388 --
389 -- Probable overlap on date tracked assignment rows
390 --
391 --
392 output(sqlerrm);
393 output(sqlcode);
394 --
395 --
396 END;
397 --
398 END LOOP;
399 --
400 COMMIT;
401 --
402 END recover_insert_rows;
403 --
404 -- -------------------------------------------------------------------------
405 --
406 -- Bulk inserts rows from global temporary table to database table
407 --
408 PROCEDURE bulk_insert_rows(p_stored_rows_to_insert NUMBER) IS
409 --
410 BEGIN
411 --
412 -- insert chunk of rows
413 --
414 -- @@ Code specific to this view/table below
415 -- @@ INTRUCTION TO DEVELOPER:
416 -- @@ 1/ For each column in your view put a column in the insert statement
417 -- below.
418 -- @@ 2/ Prefix each column in the VALUE clause with g_
419 -- @@ 3/ make sure (i) is at the end of each column in the value clause
420 --
421 FORALL i IN 1..p_stored_rows_to_insert
422 INSERT INTO hri_cs_per_person_ct(
423 per_work_phone
424 ,per_work_location
425 ,adt_ppf_person_id
426 ,adt_ppf_effctv_start_date
427 ,adt_ppf_effctv_end_date
428 ,per_buyer_flag_code
429 ,per_date_of_birth
430 ,per_middle_names
431 ,per_known_as
432 ,per_honors
433 ,per_pre_name_adjunct
434 ,per_apl_number
435 ,per_emp_number
436 ,per_cwk_number
437 ,per_apl_flag_crnt_code
438 ,per_emp_flag_crnt_code
439 ,per_cwk_flag_crnt_code
440 ,per_person_name_lcl
441 ,per_first_name
442 ,per_place_of_birth
443 ,per_last_name_prev
444 ,per_order_by
445 ,per_person_name_gbl
446 ,per_last_name
447 ,per_worker_crnt_flag_code
448 ,per_country_of_birth
449 ,per_date_of_death
450 ,per_work_email
451 ,per_title
452 ,per_suffix
453 ,per_person_name
454 ,per_person_pk
455 ,per_marital_status_crnt
456 ,per_gender_crnt
457 )
458 VALUES(g_per_work_phone(i)
459 ,g_per_work_location(i)
460 ,g_adt_ppf_person_id(i)
461 ,g_adt_ppf_effctv_start_date(i)
462 ,g_adt_ppf_effctv_end_date(i)
463 ,g_per_buyer_flag_code(i)
464 ,g_per_date_of_birth(i)
465 ,g_per_middle_names(i)
466 ,g_per_known_as(i)
467 ,g_per_honors(i)
468 ,g_per_pre_name_adjunct(i)
469 ,g_per_apl_number(i)
470 ,g_per_emp_number(i)
471 ,g_per_cwk_number(i)
472 ,g_per_apl_flag_crnt_code(i)
473 ,g_per_emp_flag_crnt_code(i)
474 ,g_per_cwk_flag_crnt_code(i)
475 ,g_per_person_name_lcl(i)
476 ,g_per_first_name(i)
477 ,g_per_place_of_birth(i)
478 ,g_per_last_name_prev(i)
479 ,g_per_order_by(i)
480 ,g_per_person_name_gbl(i)
481 ,g_per_last_name(i)
482 ,g_per_worker_crnt_flag_code(i)
483 ,g_per_country_of_birth(i)
484 ,g_per_date_of_death(i)
485 ,g_per_work_email(i)
486 ,g_per_title(i)
487 ,g_per_suffix(i)
488 ,g_per_person_name(i)
489 ,g_per_person_pk(i)
490 ,g_per_marital_status_crnt(i)
491 ,g_per_gender_crnt(i)
492 );
493
494 --
495 -- @@Code specific to this view/table below ENDS
496 --
497 -- commit the chunk of rows
498 --
499 COMMIT;
500 --
501 EXCEPTION
502 --
503 WHEN OTHERS THEN
504 --
505 -- Probable unique constraint error
506 --
507 ROLLBACK;
508 --
509 recover_insert_rows(p_stored_rows_to_insert);
510 --
511 --
512 END bulk_insert_rows;
513 --
514 -- -------------------------------------------------------------------------
515 --
516 -- Loops through table and collects into table structure.
517 --
518 PROCEDURE Incremental_Update IS
519 --
520 BEGIN
521 --
522 -- @@ Code specific to this view/table below
523 -- @@ INTRUCTION TO DEVELOPER:
524 -- @@ 1/ Change the code below to reflect the columns in your view / table
525 -- @@ 2/ Change the FROM, INSERT, DELETE statements to point at the relevant
526 -- @@ source view / table
527 --
528 -- Insert completly new rows
529 --
530 -- log('Doing insert.');
531
532 INSERT INTO hri_cs_per_person_ct(
533 per_work_phone
534 ,per_work_location
535 ,adt_ppf_person_id
536 ,adt_ppf_effctv_start_date
537 ,adt_ppf_effctv_end_date
538 ,per_buyer_flag_code
539 ,per_date_of_birth
540 ,per_middle_names
541 ,per_known_as
542 ,per_honors
543 ,per_pre_name_adjunct
544 ,per_apl_number
545 ,per_emp_number
546 ,per_cwk_number
547 ,per_apl_flag_crnt_code
548 ,per_emp_flag_crnt_code
549 ,per_cwk_flag_crnt_code
550 ,per_person_name_lcl
551 ,per_first_name
552 ,per_place_of_birth
553 ,per_last_name_prev
554 ,per_order_by
555 ,per_person_name_gbl
556 ,per_last_name
557 ,per_worker_crnt_flag_code
558 ,per_country_of_birth
559 ,per_date_of_death
560 ,per_work_email
561 ,per_title
562 ,per_suffix
563 ,per_person_name
564 ,per_person_pk
565 ,per_marital_status_crnt
566 ,per_gender_crnt
567 )
568 SELECT NVL(per.work_telephone,'NA_EDW')
569 ,NVL(per.internal_location,'NA_EDW')
570 ,per.person_id
571 ,per.effective_start_date
572 ,per.effective_end_date
573 ,case
574 when poa.agent_id = per.person_id
575 and (
576 poa.end_date_active is null
577 or
578 trunc(sysdate) between poa.start_date_active
579 and poa.end_date_active
580 )
581 then 'Y'
582 else 'N'
583 end
584 ,NVL(per.date_of_birth,hr_general.start_of_time)
585 ,per.middle_names
586 ,per.known_as
587 ,per.honors
588 ,per.pre_name_adjunct
589 ,NVL(per.applicant_number,'NA_EDW')
590 ,NVL(per.employee_number,'NA_EDW')
591 ,NVL(per.npw_number,'NA_EDW')
592 ,per.current_applicant_flag
593 ,per.current_employee_flag
594 ,per.current_npw_flag
595 ,per.local_name
596 ,per.first_name
597 ,NVL(per.town_of_birth,'NA_EDW')
598 ,per.previous_last_name
599 ,per.order_name
600 ,per.global_name
601 ,per.last_name
602 ,decode(nvl(per.current_employee_flag,'N'),'Y','Y',current_npw_flag)
603 ,NVL(per.country_of_birth,'NA_EDW')
604 ,NVL(per.date_of_death,hr_general.end_of_time)
605 ,NVL(per.email_address,'NA_EDW')
606 ,per.title
607 ,per.suffix
608 ,per.first_name
609 ,per.person_id
610 ,NVL(per.marital_status,'NA_EDW')
611 ,NVL(per.sex,'NA_EDW')
612 FROM per_all_people_f per
613 ,po_agents poa
614 WHERE per.person_id = poa.agent_id(+)
615 AND TRUNC(sysdate) between per.effective_start_date and per.effective_end_date
616 AND NOT EXISTS (SELECT 'x'
617 FROM hri_cs_per_person_ct tbl
618 WHERE per.person_id = tbl.per_person_pk
619 );
620
621 -- log('Insert >'||TO_CHAR(sql%rowcount));
622 -- log('Doing update.');
623 --
624 --------------------------------------------------------
625 --Update Strategy for change in buyer status
626 --Nos of DB writes is limited to the rows changed only.
627 --------------------------------------------------------
628
629
630 UPDATE hri_cs_per_person_ct tbl
631 SET (per_buyer_flag_code) = (decode(tbl.per_buyer_flag_code, 'Y', 'N', 'Y'))
632 WHERE tbl.per_person_pk in
633 (SELECT ct.per_person_pk person_id
634 FROM (SELECT tbl.per_person_pk,
635 tbl.per_buyer_flag_code collected_flag,
636 case
637 when poa.agent_id = tbl.per_person_pk
638 and (
639 poa.end_date_active is null
640 or
641 trunc(sysdate) between poa.start_date_active
642 and poa.end_date_active
643 )
644 then 'Y'
645 else 'N'
646 end buyer_flag
647 FROM hri_cs_per_person_ct tbl, po_agents poa
648 WHERE tbl.per_person_pk = poa.agent_id(+)
649 )ct
650 WHERE ct.buyer_flag <> ct.collected_flag
651 );
652
653
654 -- Update CT with PAPF attribs
655
656 UPDATE hri_cs_per_person_ct tbl
657 SET ( per_work_phone
658 ,per_work_location
659 ,adt_ppf_person_id
660 ,adt_ppf_effctv_start_date
661 ,adt_ppf_effctv_end_date
662 ,per_date_of_birth
663 ,per_middle_names
664 ,per_known_as
665 ,per_honors
666 ,per_pre_name_adjunct
667 ,per_apl_number
668 ,per_emp_number
669 ,per_cwk_number
670 ,per_apl_flag_crnt_code
671 ,per_emp_flag_crnt_code
672 ,per_cwk_flag_crnt_code
673 ,per_person_name_lcl
674 ,per_first_name
675 ,per_place_of_birth
676 ,per_last_name_prev
677 ,per_order_by
678 ,per_person_name_gbl
679 ,per_last_name
680 ,per_worker_crnt_flag_code
681 ,per_country_of_birth
682 ,per_date_of_death
683 ,per_work_email
684 ,per_title
685 ,per_suffix
686 ,per_person_name
687 ,per_person_pk
688 ,per_marital_status_crnt
689 ,per_gender_crnt
690 )=
691 (SELECT per.work_telephone
692 ,per.internal_location
693 ,per.person_id
694 ,per.effective_start_date
695 ,per.effective_end_date
696 ,per.date_of_birth
697 ,per.middle_names
698 ,per.known_as
699 ,per.honors
700 ,per.pre_name_adjunct
701 ,per.applicant_number
702 ,per.employee_number
703 ,per.npw_number
704 ,per.current_applicant_flag
705 ,per.current_employee_flag
706 ,per.current_npw_flag
707 ,per.global_name
708 ,per.first_name
709 ,per.town_of_birth
710 ,per.previous_last_name
711 ,per.order_name
712 ,per.global_name
713 ,per.last_name
714 ,DECODE(nvl(per.current_employee_flag,'N'),'Y','Y',current_npw_flag)
715 ,per.country_of_birth
716 ,per.date_of_death
717 ,per.email_address
718 ,per.title
719 ,per.suffix
720 ,per.first_name
721 ,per.person_id
722 ,per.marital_status
723 ,per.sex
724 FROM per_all_people_f per
725 WHERE per.person_id = tbl.per_person_pk
726 AND TRUNC(sysdate)
727 between per.effective_start_date and per.effective_end_date
728 )
729 WHERE tbl.per_person_pk in
730 (SELECT per1.person_id
731 from per_all_people_f per1
732 where per1.last_update_date
733 between g_start_date and g_end_date
734 );
735 --
736 -- log('Update >'||TO_CHAR(sql%rowcount));
737 --
738 -- Delete rows that no longer exist in the source view.
739 --
740 -- log('Doing delete.');
741
742
743 DELETE
744 FROM hri_cs_per_person_ct tbl
745 WHERE NOT EXISTS (SELECT 'x'
746 FROM per_all_people_f per
747 WHERE per.person_id = tbl.per_person_pk
748 AND TRUNC(sysdate) BETWEEN
749 per.effective_start_date AND per.effective_end_date )
750 and tbl.per_person_pk <> -1;
751
752
753 -- log('Delete >'||TO_CHAR(sql%rowcount));
754 --
755 -- @@ Code specific to this view/table below ENDS
756 --
757 COMMIT;
758 -- log('Done incremental update.');
759 --
760 EXCEPTION
761 --
762 WHEN OTHERS THEN
763 --
764 Output('Failure in incremental update process.');
765 --
766 RAISE;
767 --
768 --
769
770 END;
771
772
773 --
774 -- -------------------------------------------------------------------------
775 --
776 --
777 -- Loops through table and collects into table structure.
778 --
779 PROCEDURE Full_Refresh IS
780 --
781 -- Select all from the source view for materialization
782 --
783 -- @@ Code specific to this view/table below
784 -- @@ INTRUCTION TO DEVELOPER:
785 -- @@ 1/ Change the select beloe to select all the columns from your view
786 -- @@ 2/ Change the FROM statement to point at the relevant source view
787 --
788 CURSOR source_view_csr IS
789 SELECT NVL(per.work_telephone,'NA_EDW')
790 ,NVL(per.internal_location,'NA_EDW')
791 ,per.person_id
792 ,per.effective_start_date
793 ,per.effective_end_date
794 ,case
795 when poa.agent_id = per.person_id
796 and (
797 poa.end_date_active is null
798 or
799 trunc(sysdate) between poa.start_date_active
800 and poa.end_date_active
801 )
802 then 'Y'
803 else 'N'
804 end
805 ,NVL(per.date_of_birth,hr_general.start_of_time)
806 ,per.middle_names
807 ,per.known_as
808 ,per.honors
809 ,per.pre_name_adjunct
810 ,NVL(per.applicant_number,'NA_EDW')
811 ,NVL(per.employee_number,'NA_EDW')
812 ,NVL(per.npw_number,'NA_EDW')
813 ,per.current_applicant_flag
814 ,per.current_employee_flag
815 ,per.current_npw_flag
816 ,per.local_name
817 ,per.first_name
818 ,NVL(per.town_of_birth,'NA_EDW')
819 ,per.previous_last_name
820 ,per.order_name
821 ,per.global_name
822 ,per.last_name
823 ,decode(nvl(per.current_employee_flag,'N'),'Y','Y',current_npw_flag)
824 ,NVL(per.country_of_birth,'NA_EDW')
825 ,NVL(per.date_of_death,hr_general.end_of_time)
826 ,NVL(per.email_address,'NA_EDW')
827 ,per.title
828 ,per.suffix
829 ,per.first_name
830 ,per.person_id
831 ,NVL(per.marital_status,'NA_EDW')
832 ,NVL(per.sex,'NA_EDW')
833 FROM per_all_people_f per,
834 po_agents poa
835 WHERE TRUNC(sysdate) between per.effective_start_date and per.effective_end_date
836 AND per.person_id = poa.agent_id(+);
837
838 --
839 -- @@Code specific to this view/table below ENDS
840 --
841 l_exit_main_loop BOOLEAN := FALSE;
842 l_rows_fetched PLS_INTEGER := g_chunk_size;
843 l_sql_stmt VARCHAR2(2000);
844 --
845 BEGIN
846 -- log('here ...');
847 --
848 -- Truncate the target table prior to full refresh.
849 --
850 l_sql_stmt := 'TRUNCATE TABLE ' || g_schema || '.'||g_target_table;
851 -- log('>'||l_sql_stmt||'<');
852 --
853 EXECUTE IMMEDIATE(l_sql_stmt);
854 -- log('truncated ...');
855
856
857 --Disable WHO TRIGGERS on table prior to full refresh
858
859 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_PER_PERSON_CT_WHO DISABLE');
860
861 -- Drop all the INDEXES on the table
862 hri_utl_ddl.log_and_drop_indexes
863 (p_application_short_name => 'HRI',
864 p_table_name => 'HRI_CS_PER_PERSON_CT',
865 p_table_owner => g_schema);
866
867
868
869 --
870 --Create an Unassigned row
871 --
872
873 insert into hri_cs_per_person_ct(
874 PER_PERSON_PK
875 ,PER_PERSON_NAME
876 ,PER_ORDER_BY
877 ,PER_PERSON_NAME_GBL
878 ,PER_PERSON_NAME_LCL
879 ,PER_FIRST_NAME
880 ,PER_LAST_NAME
881 ,PER_LAST_NAME_PREV
882 ,PER_MIDDLE_NAMES
883 ,PER_KNOWN_AS
884 ,PER_HONORS
885 ,PER_TITLE
886 ,PER_SUFFIX
887 ,PER_PRE_NAME_ADJUNCT
888 ,PER_APL_NUMBER
889 ,PER_EMP_NUMBER
890 ,PER_CWK_NUMBER
891 ,PER_APL_FLAG_CRNT_CODE
892 ,PER_EMP_FLAG_CRNT_CODE
893 ,PER_CWK_FLAG_CRNT_CODE
894 ,PER_WORKER_CRNT_FLAG_CODE
895 ,PER_BUYER_FLAG_CODE
896 ,PER_DATE_OF_BIRTH
897 ,PER_PLACE_OF_BIRTH
898 ,PER_COUNTRY_OF_BIRTH
899 ,PER_DATE_OF_DEATH
900 ,PER_WORK_EMAIL
901 ,PER_WORK_PHONE
902 ,PER_WORK_LOCATION
903 ,ADT_PPF_PERSON_ID
904 ,ADT_PPF_EFFCTV_START_DATE
905 ,ADT_PPF_EFFCTV_END_DATE
906 ,PER_MARITAL_STATUS_CRNT
907 ,PER_GENDER_CRNT )
908 select
909 id
910 ,id_char
911 ,NULL
912 ,id_char
913 ,id_char
914 ,id_char
915 ,id_char
916 ,id_char
917 ,id_char
918 ,NULL
919 ,NULL
920 ,NULL
921 ,NULL
922 ,NULL
923 ,id_char
924 ,id_char
925 ,id_char
926 ,id_char
927 ,id_char
928 ,id_char
929 ,id_char
930 ,id_char
931 ,hr_general.end_of_time
932 ,id_char
933 ,id_char
934 ,hr_general.end_of_time
935 ,id_char
936 ,id_char
937 ,id_char
938 ,id
939 ,hr_general.end_of_time
940 ,hr_general.end_of_time
941 ,id_char
942 ,id_char
943 from hri_unassigned ;
944
945 commit;
946
947 --
948 --
949 -- Write timing information to log
950 --
951 output('Truncated the table: ' ||
952 to_char(sysdate,'HH24:MI:SS'));
953 --
954 -- open main cursor
955 --
956 -- log('open cursor ...');
957 OPEN source_view_csr;
958 --
959 <<main_loop>>
960 LOOP
961 --
962 -- bulk fetch rows limit the fetch to value of g_chunk_size
963 --
964 -- @@ Code specific to this view/table below
965 -- @@ INTRUCTION TO DEVELOPER:
966 -- @@ Change the bulk collect below to select all the columns from your
967 -- @@ view
968 --
969 -- log('start fetch ...');
970 -- log('>'||TO_CHAR(g_chunk_size)||'<');
971 FETCH source_view_csr
972 BULK COLLECT INTO
973 g_per_work_phone
974 ,g_per_work_location
975 ,g_adt_ppf_person_id
976 ,g_adt_ppf_effctv_start_date
977 ,g_adt_ppf_effctv_end_date
978 ,g_per_buyer_flag_code
979 ,g_per_date_of_birth
980 ,g_per_middle_names
981 ,g_per_known_as
982 ,g_per_honors
983 ,g_per_pre_name_adjunct
984 ,g_per_apl_number
985 ,g_per_emp_number
986 ,g_per_cwk_number
987 ,g_per_apl_flag_crnt_code
988 ,g_per_emp_flag_crnt_code
989 ,g_per_cwk_flag_crnt_code
990 ,g_per_person_name_lcl
991 ,g_per_first_name
992 ,g_per_place_of_birth
993 ,g_per_last_name_prev
994 ,g_per_order_by
995 ,g_per_person_name_gbl
996 ,g_per_last_name
997 ,g_per_worker_crnt_flag_code
998 ,g_per_country_of_birth
999 ,g_per_date_of_death
1000 ,g_per_work_email
1001 ,g_per_title
1002 ,g_per_suffix
1003 ,g_per_person_name
1004 ,g_per_person_pk
1005 ,g_per_marital_status_crnt
1006 ,g_per_gender_crnt
1007 LIMIT g_chunk_size;
1008 -- log('finish fetch ...');
1009 --
1010 -- @@Code specific to this view/table below ENDS
1011 --
1012 -- check to see if the last row has been fetched
1013 --
1014 IF source_view_csr%NOTFOUND THEN
1015 --
1016 -- last row fetched, set exit loop flag
1017 --
1018 l_exit_main_loop := TRUE;
1019 --
1020 -- do we have any rows to process?
1021 --
1022 l_rows_fetched := MOD(source_view_csr%ROWCOUNT,g_chunk_size);
1023 --
1024 -- note: if l_rows_fetched > 0 then more rows are required to be
1025 -- processed and the l_rows_fetched will contain the exact number of
1026 -- rows left to process
1027 --
1028 IF l_rows_fetched = 0 THEN
1029 --
1030 -- no more rows to process so exit loop
1031 --
1032 EXIT main_loop;
1033 END IF;
1034 END IF;
1035 --
1036 -- bulk insert rows processed so far
1037 --
1038 -- log('call bulk ...');
1039 bulk_insert_rows (l_rows_fetched);
1040 -- log('end bulk ...');
1041 --
1042 -- exit loop if required
1043 --
1044 IF l_exit_main_loop THEN
1045 --
1046 EXIT main_loop;
1047 --
1048 END IF;
1049 --
1050 END LOOP;
1051 --
1052 CLOSE source_view_csr;
1053
1054 --Enable WHO TRIGGERS
1055
1056 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_PER_PERSON_CT_WHO ENABLE');
1057
1058 --Enable INDEX
1059
1060 hri_utl_ddl.recreate_indexes
1061 (p_application_short_name => 'HRI',
1062 p_table_name => 'HRI_CS_PER_PERSON_CT',
1063 p_table_owner => g_schema);
1064
1065 --
1066 -- log('End ...');
1067
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 --
1072 -- unexpected error has occurred so close down
1073 -- main bulk cursor if it is open
1074 --
1075 IF source_view_csr%ISOPEN THEN
1076 --
1077 CLOSE source_view_csr;
1078 --
1079 END IF;
1080 --
1081 -- re-raise error
1082 RAISE;
1083 --
1084 --
1085 END Full_Refresh;
1086 --
1087 -- -------------------------------------------------------------------------
1088 -- Checks what mode you are running in, and if g_full_refresh =
1089 -- g_is_full_refresh calls
1090 -- Full_Refresh procedure, otherwise Incremental_Update is called.
1091 --
1092 PROCEDURE Collect IS
1093 --
1094 BEGIN
1095 --
1096 -- If in full refresh mode chnage the dates so that the collection history
1097 -- is correctly maintained.
1098 --
1099 IF g_full_refresh = g_is_full_refresh THEN
1100 --
1101 g_start_date := hr_general.start_of_time;
1102 g_end_date := SYSDATE;
1103 --
1104 -- log('Doing full refresh.');
1105 Full_Refresh;
1106 --
1107 ELSE
1108 --
1109 -- log('Doing incremental update.');
1110 --
1111 -- If the passed in date range is NULL default it.
1112 --
1113 IF g_start_date IS NULL OR
1114 g_end_date IS NULL
1115 THEN
1116 -- log('Input dates NULL.');
1117 --
1118 g_start_date := fnd_date.displaydt_to_date(
1119 hri_bpl_conc_log.get_last_collect_to_date(
1120 g_cncrnt_prgrm_shrtnm
1121 ,g_target_table));
1122 --
1123 g_end_date := SYSDATE;
1124 -- log('start >'||TO_CHAR(g_start_date));
1125 -- log('end >'||TO_CHAR(g_end_date));
1126 -- log('Defaulted input DATES.');
1127 --
1128 END IF;
1129 --
1130 -- log('Calling incremental update.');
1131 Incremental_Update;
1132 -- log('Completed incremental update.');
1133 --
1134 END IF;
1135 --
1136 END Collect;
1137 --
1138 -- -------------------------------------------------------------------------
1139 -- Checks if the Target table is Empty
1140 --
1141 FUNCTION Target_table_is_Empty RETURN BOOLEAN IS
1142 --
1143 -- @@ Code specific to this view/table below
1144 -- @@ INTRUCTION TO DEVELOPER:
1145 -- @@ Change the table in the FROM clause below to be the same as your
1146 -- @@ target table.
1147 --
1148 CURSOR csr_recs_exist IS
1149 SELECT 'x'
1150 FROM hri_cs_per_person_ct;
1151 --
1152 -- @@ Code specific to this view/table ENDS
1153 --
1154 l_exists_chr VARCHAR2(1);
1155 l_exists BOOLEAN;
1156 --
1157 BEGIN
1158 --
1159 OPEN csr_recs_exist;
1160 --
1161 FETCH csr_recs_exist INTO l_exists_chr;
1162 --
1163 IF (csr_recs_exist%NOTFOUND)
1164 THEN
1165 --
1166 l_exists := TRUE;
1167 -- log('no data in table');
1168 --
1169 ELSE
1170 --
1171 l_exists := FALSE;
1172 -- log('data is in table');
1173 --
1174 END IF;
1175 --
1176 CLOSE csr_recs_exist;
1177 --
1178 RETURN l_exists;
1179 --
1180 EXCEPTION
1181 --
1182 WHEN OTHERS
1183 THEN
1184 --
1185 CLOSE csr_recs_exist;
1186 RAISE;
1187 --
1188 --
1189 END Target_table_is_Empty;
1190 --
1191 -- -------------------------------------------------------------------------
1192 --
1193 -- Main entry point to load the table.
1194 --
1195 PROCEDURE Load(p_chunk_size IN NUMBER,
1196 p_start_date IN VARCHAR2,
1197 p_end_date IN VARCHAR2,
1198 p_full_refresh IN VARCHAR2) IS
1199 --
1200 -- Variables required for table truncation.
1201 --
1202 l_dummy1 VARCHAR2(2000);
1203 l_dummy2 VARCHAR2(2000);
1204 --
1205 BEGIN
1206 --
1207 output('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
1208 --
1209 -- Set globals
1210 --
1211 g_start_date := to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
1212 g_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
1213
1214 --
1215 IF p_chunk_size IS NULL
1216 THEN
1217 --
1218 g_chunk_size := 500;
1219 --
1220 ELSE
1221 --
1222 g_chunk_size := p_chunk_size;
1223 --
1224 END IF;
1225 --
1226 IF p_full_refresh IS NULL
1227 THEN
1228 --
1229 g_full_refresh := g_not_full_refresh;
1230 --
1231 ELSE
1232 --
1233 g_full_refresh := p_full_refresh;
1234 --
1235 END IF;
1236 --
1237 -- If the target table is empty default to full refresh.
1238 --
1239 IF Target_table_is_Empty
1240 THEN
1241 --
1242 output('Target table '||g_target_table||
1243 ' is empty, so doing a full refresh.');
1244 -- log('Doing a full refresh....');
1245 --
1246 g_full_refresh := g_is_full_refresh;
1247
1248 --
1249 END IF;
1250 --
1251 -- log('p_chunk_size>'||TO_CHAR(g_chunk_size)||'<');
1252 -- Find the schema we are running in.
1253 --
1254 IF NOT fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, g_schema)
1255 THEN
1256 --
1257 -- Could not find the schema raising exception.
1258 --
1259 output('Could not find schema to run in.');
1260 --
1261 -- log('Could not find schema.');
1262 RAISE NO_DATA_FOUND;
1263 --
1264 END IF;
1265 --
1266 -- Update information about collection
1267 --
1268 -- log('Record process start.');
1269 /* double check correct val passed in below */
1270 hri_bpl_conc_log.record_process_start(g_cncrnt_prgrm_shrtnm);
1271 --
1272 -- Time at start
1273 --
1274 -- log('collect.');
1275 --
1276 -- Get HRI schema name - get_app_info populates l_schema
1277 --
1278 -- Insert new records
1279 --
1280 collect;
1281 -- log('collectED.');
1282 --
1283 -- Write timing information to log
1284 --
1285 output('Finished changes to the table: ' ||
1286 to_char(sysdate,'HH24:MI:SS'));
1287 --
1288 -- Gather index stats
1289 --
1290 -- log('gather stats.');
1291 fnd_stats.gather_table_stats(g_schema, g_target_table);
1292 --
1293 -- Write timing information to log
1294 --
1295 output('Gathered stats: ' ||
1296 to_char(sysdate,'HH24:MI:SS'));
1297 --
1298 -- log('log end.');
1299 hri_bpl_conc_log.log_process_end(
1300 p_status => TRUE,
1301 p_period_from => TRUNC(g_start_date),
1302 p_period_to => TRUNC(g_end_date),
1303 p_attribute1 => p_full_refresh,
1304 p_attribute2 => p_chunk_size);
1305 -- log('-END-');
1306 --
1307
1308 EXCEPTION
1309 --
1310 WHEN OTHERS
1311 THEN
1312 --
1313 ROLLBACK;
1314 RAISE;
1315 --
1316 --
1317 END Load;
1318 --
1319 -- -------------------------------------------------------------------------
1320 --
1321 -- Entry point to be called from the concurrent manager
1322 --
1323 PROCEDURE Load(errbuf OUT NOCOPY VARCHAR2,
1324 retcode OUT NOCOPY VARCHAR2,
1325 p_chunk_size IN NUMBER,
1326 p_start_date IN VARCHAR2,
1327 p_end_date IN VARCHAR2,
1328 p_full_refresh IN VARCHAR2)
1329 IS
1330 --
1331 BEGIN
1332 --
1333 -- Enable output to concurrent request log
1334 --
1335 g_conc_request_flag := TRUE;
1336 --
1337 load(p_chunk_size => p_chunk_size,
1338 p_start_date => p_start_date,
1339 p_end_date => p_end_date,
1340 p_full_refresh => p_full_refresh);
1341 --
1342 EXCEPTION
1343 --
1344 WHEN OTHERS THEN
1345 --
1346 errbuf := SQLERRM;
1347 retcode := SQLCODE;
1348 --
1349 --
1350 END load;
1351
1352 END HRI_OPL_PER_PERSON;