[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_ORGH_CT
Source
1 PACKAGE BODY hri_opl_orgh_ct AS
2 /* $Header: hriporghct.pkb 120.4.12000000.2 2007/04/12 13:27:15 smohapat noship $ */
3
4 /******************************************************************************/
5 /* */
6 /* OUTLINE / DEFINITIONS */
7 /* */
8 /* A chain is defined for a organization as a list starting with the */
9 /* organization which contains and successive higher level organizations */
10 /* finishing with the highest level (top) organization. */
11 /* */
12 /* IMPLEMENTATION LOGIC */
13 /* */
14 /* The organization hierarchy table is populated by carrying out the */
15 /* following steps: */
16 /* */
17 /* 1) Empty out existing table */
18 /* */
19 /* 2) Find the organization structure to collect. It will be the current */
20 /* version for: */
21 /* - Structure in HR:BIS Reporting Hierarchy profile, if this structure */
22 /* is global */
23 /* - If the profile is empty or not a global structure then default to */
24 /* the primary global structure */
25 /* */
26 /* 3) Collect chains for organization structure */
27 /* - Process hierarchy in default (tree walk) order */
28 /* - Maintain cache of chain for each node in tree walk */
29 /* - For each node loop through links in chain: */
30 /* - Calculate relative levels to supervisor organization */
31 /* - Insert link record (store in PL/SQL globals for bulk insert) */
32 /* - If bulk insert limit reached on rows to insert, then do bulk insert */
33 /* */
34 /* 4) Bulk Insert any remaining rows at end of process */
35 /******************************************************************************/
36
37 /* Information to be held for each link in a chain */
38 TYPE g_link_record_type IS RECORD
39 (business_group_id per_org_structure_elements.business_group_id%TYPE
40 ,organization_id per_org_structure_elements.organization_id_parent%TYPE
41 ,last_chng_date DATE);
42
43 /* Table type to hold information about the current chain */
44 TYPE g_chain_type IS TABLE OF g_link_record_type INDEX BY BINARY_INTEGER;
45
46 /* Simple table types */
47 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
48 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
49 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
50
51 /* PLSQL table of tables representing category structure table */
52 g_cs_ost_id g_number_tab_type;
53 g_cs_bgr_id g_number_tab_type;
54 g_cs_osv_id g_number_tab_type;
55 g_cs_sup_org_id g_number_tab_type;
56 g_cs_sup_level g_number_tab_type;
57 g_cs_sub_org_id g_number_tab_type;
58 g_cs_sub_org_prnt_id g_number_tab_type;
59 g_cs_sub_level g_number_tab_type;
60 g_cs_sub_rlt_lvl g_number_tab_type;
61 g_cs_last_chng g_date_tab_type;
62 g_cs_sup_sub1_id g_number_tab_type;
63 g_cs_sup_sub2_id g_number_tab_type;
64 g_cs_sup_sub3_id g_number_tab_type;
65 g_cs_sup_sub4_id g_number_tab_type;
66 g_cs_sup_sub5_id g_number_tab_type;
67 --
68 g_new_orgs_with_worker g_number_tab_type;
69 --
70 g_cs_rows_to_insert PLS_INTEGER; -- Number of CS rows to insert
71
72 /* Set to true to output to a concurrent log file */
73 g_conc_request_flag BOOLEAN := FALSE;
74
75 /* Number of rows bulk processed at a time */
76 g_chunk_size PLS_INTEGER;
77
78 /* Start / End of time dates */
79 g_start_of_time DATE := hr_general.start_of_time;
80 g_end_of_time DATE := hr_general.end_of_time;
81 g_user_id NUMBER;
82 g_sysdate DATE;
83
84 /******************************************************************************/
85 /* Inserts row into concurrent program log when the g_conc_request_flag has */
86 /* been set to TRUE, otherwise does nothing */
87 /******************************************************************************/
88 PROCEDURE output(p_text VARCHAR2)
89 IS
90
91 BEGIN
92
93 /* Write to the concurrent request log if called from a concurrent request */
94 IF (g_conc_request_flag = TRUE) THEN
95
96 /* Put text to log file */
97 fnd_file.put_line(FND_FILE.log, p_text);
98 END IF;
99
100 END output;
101
102
103 /******************************************************************************/
104 /* Runs given sql statement dynamically */
105 /******************************************************************************/
106 PROCEDURE run_sql_stmt_noerr(p_sql_stmt VARCHAR2) IS
107
108 BEGIN
109
110 EXECUTE IMMEDIATE p_sql_stmt;
111
112 EXCEPTION WHEN OTHERS THEN
113
114 output('Error running sql:');
115 output(SUBSTR(p_sql_stmt,1,230));
116
117 END run_sql_stmt_noerr;
118
119
120 /******************************************************************************/
121 /* Recovers CS rows to insert when an exception occurs */
122 /******************************************************************************/
123 PROCEDURE recover_insert_cs_rows IS
124
125 BEGIN
126 -- loop through rows still to insert one at a time
127 FOR i IN 1..g_cs_rows_to_insert LOOP
128
129 -- Trap unique constraint errors
130 BEGIN
131
132 INSERT INTO hri_cs_orgh_ct
133 (orgh_orghrchy_fk
134 ,orgh_global_flag
135 ,orgh_orghvrsn_fk
136 ,orgh_sup_organztn_fk
137 ,orgh_sup_level
138 ,orgh_sup_sub1_organztn_fk
139 ,orgh_sup_sub2_organztn_fk
140 ,orgh_sup_sub3_organztn_fk
141 ,orgh_sup_sub4_organztn_fk
142 ,orgh_organztn_fk
143 ,orgh_level
144 ,orgh_relative_level
145 ,orgh_adt_org_struct_id
146 ,orgh_adt_org_struct_version_id
147 ,orgh_sub_node_has_workers_flag
148 ,orgh_sub_org_has_workers_flag)
149 VALUES
150 (g_cs_ost_id(i)
151 ,'Y'
152 ,g_cs_osv_id(i)
153 ,g_cs_sup_org_id(i)
154 ,g_cs_sup_level(i)
155 ,g_cs_sup_sub1_id(i)
156 ,g_cs_sup_sub2_id(i)
157 ,g_cs_sup_sub3_id(i)
158 ,g_cs_sup_sub4_id(i)
159 ,g_cs_sub_org_id(i)
160 ,g_cs_sub_level(i)
161 ,g_cs_sub_rlt_lvl(i)
162 ,g_cs_ost_id(i)
163 ,g_cs_osv_id(i)
164 ,'N'
165 ,'N');
166
167 EXCEPTION
168 WHEN OTHERS THEN
169
170 /* Probable overlap on date tracked assignment rows */
171 output('Single insert error: ' || to_char(g_cs_sub_org_id(i)) ||
172 ' - ' || to_char(g_cs_sup_org_id(i)));
173 output('Inserting chain for: ' ||
174 to_char(g_cs_sub_org_id(i)) || ' in hierarchy version' ||
175 to_char(g_cs_osv_id(i)));
176 output(sqlerrm);
177 output(sqlcode);
178
179 END;
180
181 END LOOP;
182
183 -- commit
184 commit;
185
186 END recover_insert_cs_rows;
187
188
189 /******************************************************************************/
190 /* Bulk inserts rows from global temporary table to CS database table */
191 /******************************************************************************/
192 PROCEDURE bulk_insert_cs_rows IS
193
194 BEGIN
195 -- insert chunk of rows
196 FORALL i IN 1..g_cs_rows_to_insert
197 INSERT INTO hri_cs_orgh_ct
198 (orgh_orghrchy_fk
199 ,orgh_global_flag
200 ,orgh_orghvrsn_fk
201 ,orgh_sup_organztn_fk
202 ,orgh_sup_level
203 ,orgh_sup_sub1_organztn_fk
204 ,orgh_sup_sub2_organztn_fk
205 ,orgh_sup_sub3_organztn_fk
206 ,orgh_sup_sub4_organztn_fk
207 ,orgh_organztn_fk
208 ,orgh_level
209 ,orgh_relative_level
210 ,orgh_adt_org_struct_id
211 ,orgh_adt_org_struct_version_id
212 ,orgh_sub_node_has_workers_flag
213 ,orgh_sub_org_has_workers_flag
214 ,last_update_date
215 ,last_updated_by
216 ,last_update_login
217 ,created_by
218 ,creation_date)
219 VALUES
220 (g_cs_ost_id(i)
221 ,'Y'
222 ,g_cs_osv_id(i)
223 ,g_cs_sup_org_id(i)
224 ,g_cs_sup_level(i)
225 ,g_cs_sup_sub1_id(i)
226 ,g_cs_sup_sub2_id(i)
227 ,g_cs_sup_sub3_id(i)
228 ,g_cs_sup_sub4_id(i)
229 ,g_cs_sub_org_id(i)
230 ,g_cs_sub_level(i)
231 ,g_cs_sub_rlt_lvl(i)
232 ,g_cs_ost_id(i)
233 ,g_cs_osv_id(i)
234 ,'N'
235 ,'N'
236 ,g_sysdate
237 ,g_user_id
238 ,g_user_id
239 ,g_user_id
240 ,g_sysdate);
241
242 -- commit the chunk of rows
243 COMMIT;
244 EXCEPTION
245 WHEN OTHERS THEN
246
247 /* Unique constraint error */
248 rollback;
249 output('Warning - recovering (CS)');
250 recover_insert_cs_rows;
251
252 END bulk_insert_cs_rows;
253
254 /******************************************************************************/
255 /* Inserts row into global CS temporary table */
256 /******************************************************************************/
257 PROCEDURE insert_cs_row( p_sup_organization_id IN NUMBER
258 , p_sup_level IN NUMBER
259 , p_sup_sub1_id IN NUMBER
260 , p_sup_sub2_id IN NUMBER
261 , p_sup_sub3_id IN NUMBER
262 , p_sup_sub4_id IN NUMBER
263 , p_sub_organization_id IN NUMBER
264 , p_sub_level IN NUMBER
265 , p_ost_id IN NUMBER
266 , p_osv_id IN NUMBER
267 , p_bgr_id IN NUMBER
268 , p_sub_org_parent_id IN NUMBER
269 , p_last_ptntl_change IN DATE ) IS
270
271 BEGIN
272 -- increment the index
273 g_cs_rows_to_insert := g_cs_rows_to_insert + 1;
274 -- set the table structures
275 g_cs_sup_org_id(g_cs_rows_to_insert) := p_sup_organization_id;
276 g_cs_sup_level(g_cs_rows_to_insert) := p_sup_level;
277 g_cs_sup_sub1_id(g_cs_rows_to_insert) := p_sup_sub1_id;
278 g_cs_sup_sub2_id(g_cs_rows_to_insert) := p_sup_sub2_id;
279 g_cs_sup_sub3_id(g_cs_rows_to_insert) := p_sup_sub3_id;
280 g_cs_sup_sub4_id(g_cs_rows_to_insert) := p_sup_sub4_id;
281 g_cs_sub_org_id(g_cs_rows_to_insert) := p_sub_organization_id;
282 g_cs_sub_level(g_cs_rows_to_insert) := p_sub_level;
283 g_cs_sub_rlt_lvl(g_cs_rows_to_insert) := p_sub_level - p_sup_level;
284 g_cs_sub_org_prnt_id(g_cs_rows_to_insert) := p_sub_org_parent_id;
285 g_cs_ost_id(g_cs_rows_to_insert) := p_ost_id;
286 g_cs_bgr_id(g_cs_rows_to_insert) := p_bgr_id;
287 g_cs_osv_id(g_cs_rows_to_insert) := p_osv_id;
288 g_cs_last_chng(g_cs_rows_to_insert) := p_last_ptntl_change;
289
290 END insert_cs_row;
291
292
293 /******************************************************************************/
294 /* Updates all organizations in the organization hierarchy version starting */
295 /* with the top organization. */
296 /******************************************************************************/
297 PROCEDURE calculate_chains(p_top_org_id IN NUMBER
298 ,p_ost_id IN NUMBER
299 ,p_bgr_id IN NUMBER
300 ,p_osv_id IN NUMBER
301 ,p_start_date IN DATE) IS
302
303 /* Cursor picks out all organizations in the organization structure */
304 /* This cursor MUST return rows in the default order */
305 CURSOR organizations_csr IS
306 SELECT
307 hier.organization_id_child organization_id
308 ,hier.last_update_date last_update_date
309 ,LEVEL+1 actual_level
310 FROM (SELECT
311 ose.organization_id_child
312 ,ose.organization_id_parent
313 ,NVL(ose.last_update_date, g_start_of_time) last_update_date
314 FROM
315 per_org_structure_elements ose
316 WHERE ose.org_structure_version_id = p_osv_id) hier
317 START WITH hier.organization_id_parent = p_top_org_id
318 CONNECT BY PRIOR hier.organization_id_child = organization_id_parent;
319 /******************************/
320 /* DO NOT ADD ORDER BY CLAUSE */
321 /******************************/
322
323 -- Cache of links in current chain
324 l_crrnt_chain g_chain_type;
325
326 -- Current and previous levels - for maintaining cache
327 l_org_lvl PLS_INTEGER;
328 l_last_org_lvl PLS_INTEGER;
329
330 -- Organizations relative to supervisor organization for each link
331 l_sup_sub1_id NUMBER;
332 l_sup_sub2_id NUMBER;
333 l_sup_sub3_id NUMBER;
334 l_sup_sub4_id NUMBER;
335
336 BEGIN
337
338 /* Store details for top organization */
339 l_crrnt_chain(1).organization_id := p_top_org_id;
340 l_crrnt_chain(1).last_chng_date := p_start_date;
341
342 /* Insert chain */
343 insert_cs_row
344 (p_sup_organization_id => l_crrnt_chain(1).organization_id
345 ,p_sup_level => 1
346 ,p_sup_sub1_id => l_crrnt_chain(1).organization_id
347 ,p_sup_sub2_id => l_crrnt_chain(1).organization_id
348 ,p_sup_sub3_id => l_crrnt_chain(1).organization_id
349 ,p_sup_sub4_id => l_crrnt_chain(1).organization_id
350 ,p_sub_organization_id => l_crrnt_chain(1).organization_id
351 ,p_sub_level => 1
352 ,p_sub_org_parent_id => -1
353 ,p_ost_id => p_ost_id
354 ,p_bgr_id => p_bgr_id
355 ,p_osv_id => p_osv_id
356 ,p_last_ptntl_change => l_crrnt_chain(1).last_chng_date);
357
358 /* Loop through organizations in organization hierarchy version */
359 FOR org_rec IN organizations_csr LOOP
360
361 l_org_lvl := org_rec.actual_level;
362
363 IF (l_last_org_lvl > l_org_lvl) THEN
364 /* Reset end of chain */
365 FOR i IN l_org_lvl+1..l_last_org_lvl LOOP
366 l_crrnt_chain(i).organization_id := to_number(null);
367 END LOOP;
368 END IF;
369
370 l_crrnt_chain(l_org_lvl).organization_id := org_rec.organization_id;
371 l_crrnt_chain(l_org_lvl).last_chng_date :=
372 GREATEST(org_rec.last_update_date,
373 l_crrnt_chain(l_org_lvl - 1).last_chng_date);
374
375 /* Loop through links in (stored) chain of organizations */
376 FOR l_sup_lvl IN 1..l_org_lvl LOOP
377
378 /* Set relative levels */
379 IF (l_org_lvl > l_sup_lvl) THEN
380 l_sup_sub1_id := l_crrnt_chain(l_sup_lvl + 1).organization_id;
381 ELSE
382 l_sup_sub1_id := l_crrnt_chain(l_org_lvl).organization_id;
383 END IF;
384 IF (l_org_lvl > l_sup_lvl + 1) THEN
385 l_sup_sub2_id := l_crrnt_chain(l_sup_lvl + 2).organization_id;
386 ELSE
387 l_sup_sub2_id := l_crrnt_chain(l_org_lvl).organization_id;
388 END IF;
389 IF (l_org_lvl > l_sup_lvl + 2) THEN
390 l_sup_sub3_id := l_crrnt_chain(l_sup_lvl + 3).organization_id;
391 ELSE
392 l_sup_sub3_id := l_crrnt_chain(l_org_lvl).organization_id;
393 END IF;
394 IF (l_org_lvl > l_sup_lvl + 3) THEN
395 l_sup_sub4_id := l_crrnt_chain(l_sup_lvl + 4).organization_id;
396 ELSE
397 l_sup_sub4_id := l_crrnt_chain(l_org_lvl).organization_id;
398 END IF;
399
400 /* Insert chain into CS */
401 insert_cs_row
402 (p_sup_organization_id => l_crrnt_chain(l_sup_lvl).organization_id
403 ,p_sup_level => l_sup_lvl
404 ,p_sup_sub1_id => l_sup_sub1_id
405 ,p_sup_sub2_id => l_sup_sub2_id
406 ,p_sup_sub3_id => l_sup_sub3_id
407 ,p_sup_sub4_id => l_sup_sub4_id
408 ,p_sub_organization_id => l_crrnt_chain(l_org_lvl).organization_id
409 ,p_sub_level => l_org_lvl
410 ,p_sub_org_parent_id => l_crrnt_chain(l_org_lvl - 1).organization_id
411 ,p_ost_id => p_ost_id
412 ,p_bgr_id => p_bgr_id
413 ,p_osv_id => p_osv_id
414 ,p_last_ptntl_change => l_crrnt_chain(l_org_lvl).last_chng_date);
415
416 END LOOP; -- Links in stored chain
417
418 /* If the stored rows have reached a maximum, then insert them */
419 IF (g_cs_rows_to_insert > g_chunk_size) THEN
420 -- bulk insert rows processed so far
421 bulk_insert_cs_rows;
422 -- reset the index
423 g_cs_rows_to_insert := 0;
424 END IF;
425
426 l_last_org_lvl := l_org_lvl;
427
428 END LOOP; -- organizations in hierarchy version
429
430 EXCEPTION
431 WHEN OTHERS THEN
432
433 /* ORA 01436 - loop in tree walk */
434 IF (SQLCODE = -1436) THEN
435 output('Loop found for organization id: ' ||
436 to_char(p_top_org_id));
437 ELSE
438 /* Some other error */
439 RAISE;
440 END IF;
441
442 END calculate_chains;
443
444 /******************************************************************************/
445 /* Loops through organization structure versions */
446 /******************************************************************************/
447 PROCEDURE collect_org_structures IS
448
449 -- Pick out hierarchy version to use. Cursor returns up to two rows:
450 -- - Primary Global Structure (current version)
451 -- - Structure selected in profile (HR BIS Reporting Hierarchy) providing
452 -- this is a global hierarchy
453 CURSOR hrchy_csr(v_structure_id NUMBER) IS
454 SELECT
455 osv.org_structure_version_id
456 FROM
457 per_org_structure_versions osv
458 ,per_organization_structures ost
459 WHERE ost.organization_structure_id = osv.organization_structure_id
460 -- Primary Global
461 AND ((ost.primary_structure_flag = 'Y' AND osv.business_group_id IS NULL)
462 -- or, Profile
463 OR ost.organization_structure_id = v_structure_id)
464 AND trunc(sysdate) BETWEEN osv.date_from
465 AND NVL(osv.date_to, SYSDATE)
466 -- If returned, order structure from profile option first to override
467 -- default selection of primary global
468 ORDER BY DECODE(ost.organization_structure_id, v_structure_id, 1, 2);
469
470 /* Pick out top organization from the selected version */
471 CURSOR hrchy_version_csr(v_version_id NUMBER) IS
472 SELECT DISTINCT
473 ose.organization_id_parent top_org_id
474 ,osv.org_structure_version_id osv_id
475 ,osv.organization_structure_id ost_id
476 ,osv.version_number osv_no
477 ,ost.primary_structure_flag primary_flag
478 ,osv.date_from start_date
479 ,NVL(osv.date_to,g_end_of_time) end_date
480 ,osv.business_group_id bgr_id
481 FROM
482 per_org_structure_elements ose
483 ,per_org_structure_versions osv
484 ,per_organization_structures ost
485 WHERE osv.org_structure_version_id = ose.org_structure_version_id
486 AND ost.organization_structure_id = osv.organization_structure_id
487 AND osv.org_structure_version_id = v_version_id
488 AND NOT EXISTS
489 (SELECT NULL
490 FROM per_org_structure_elements ose2
491 WHERE ose2.org_structure_version_id = ose.org_structure_version_id
492 AND ose2.organization_id_child = ose.organization_id_parent);
493
494 l_profile_structure NUMBER;
495 l_structure_version NUMBER;
496
497 BEGIN
498
499 -- Get value of profile HR: BIS Reporting Hierarchy
500 l_profile_structure := fnd_profile.value('HR_BIS_REPORTING_HIERARCHY');
501
502 output('Structure: ' || to_char(l_profile_structure));
503
504 -- Get structure version to collect
505 OPEN hrchy_csr(l_profile_structure);
506 FETCH hrchy_csr INTO l_structure_version;
507 CLOSE hrchy_csr;
508
509 output('Version: ' || to_char(l_structure_version));
510
511 -- Start collection
512 IF (l_structure_version IS NOT NULL) THEN
513
514 -- initialise the g_cs_rows_to_insert
515 g_cs_rows_to_insert := 0;
516
517 -- Populate table
518 FOR hrchy_rec IN hrchy_version_csr(l_structure_version) LOOP
519
520 calculate_chains
521 (p_top_org_id => hrchy_rec.top_org_id
522 ,p_ost_id => hrchy_rec.ost_id
523 ,p_bgr_id => hrchy_rec.bgr_id
524 ,p_osv_id => hrchy_rec.osv_id
525 ,p_start_date => hrchy_rec.start_date);
526
527 END LOOP;
528
529 -- Insert any remaining stored rows
530 IF (g_cs_rows_to_insert > 0) THEN
531 bulk_insert_cs_rows;
532 END IF;
533
534 END IF;
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 -- unexpected error has occurred so close down
539 -- main bulk cursor if it is open
540 IF hrchy_version_csr%ISOPEN THEN
541 CLOSE hrchy_version_csr;
542 END IF;
543 -- re-raise error
544 RAISE;
545 END collect_org_structures;
546
547
548 /******************************************************************************/
549 /* Updates flag orgh_sub_org_has_workers_flag,orgh_node_has_workers_flag */
550 /******************************************************************************/
551
552 PROCEDURE upd_org_has_worker_flags_full IS
553 --
554 BEGIN
555 --
556 -- Time at flag update start
557 --
558 output('Flag update Start: ' || to_char(sysdate,'HH24:MI:SS'));
559 --
560 -- Enable parallel DML
561 --
562 run_sql_stmt_noerr('ALTER SESSION ENABLE PARALLEL DML');
563 --
564 output('Enabled parallel DML: ' || to_char(sysdate,'HH24:MI:SS'));
565 --
566 -- Update flag orgh_sub_org_has_workers_flag. This flag is set to 'Y' when
567 -- the subordinate organization has workers. Else, it is 'N'.
568 --
569 UPDATE /*+ PARALLEL */ hri_cs_orgh_ct orgh
570 SET orgh.orgh_sub_org_has_workers_flag = 'Y'
571 WHERE orgh.rowid IN (SELECT sub_org.rowid
572 FROM hri_cs_orgh_ct sub_org,
573 hri_cs_organztn_ct org_wrkrs
574 WHERE sub_org.orgh_organztn_fk = org_wrkrs.org_organztn_pk
575 AND org_wrkrs.org_has_workers_flag = 'Y'
576 );
577 --
578 COMMIT;
579 --
580 /* Write timing information to log */
581 output('orgh_sub_org_has_workers_flag updated: ' ||
582 to_char(sysdate,'HH24:MI:SS'));
583 --
584 -- Update flag orgh_node_has_workers_flag. This flag is set to 'Y' when
585 -- the supervisor organization has workers for any of its subordinate
586 -- organizations. Else, it is 'N'.
587 --
588 UPDATE /*+ PARALLEL(orgh) */ hri_cs_orgh_ct orgh
589 SET orgh.orgh_sub_node_has_workers_flag = 'Y'
590 WHERE orgh.rowid IN (
591 SELECT sup_org.rowid
592 FROM hri_cs_orgh_ct sup_org
593 WHERE EXISTS (SELECT null
594 FROM hri_cs_orgh_ct sub_org
595 WHERE sup_org.orgh_organztn_fk = sub_org.orgh_sup_organztn_fk
596 AND sub_org.orgh_sub_org_has_workers_flag = 'Y'));
597 --
598 COMMIT;
599 --
600 /* Write timing information to log */
601 output('orgh_node_has_workers_flag updated: ' ||
602 to_char(sysdate,'HH24:MI:SS'));
603 --
604 EXCEPTION
605 --
606 WHEN OTHERS THEN
607 RAISE;
608 --
609 END upd_org_has_worker_flags_full;
610 --
611 /******************************************************************************/
612 /* Updates flag orgh_sub_org_has_workers_flag,orgh_node_has_workers_flag */
613 /******************************************************************************/
614 --
615 PROCEDURE upd_org_has_worker_flags_incr
616 IS
617 BEGIN
618 --
619 output('Incremental flag update start: ' || to_char(sysdate,'HH24:MI:SS'));
620 --
621 IF g_new_orgs_with_worker.COUNT > 0 THEN
622 --
623 -- Update flag orgh_sub_org_has_workers_flag. This flag is set to 'Y' when
624 -- the subordinate organization has workers. Else, it is 'N'.
625 --
626 FORALL i IN g_new_orgs_with_worker.FIRST..g_new_orgs_with_worker.LAST
627 UPDATE hri_cs_orgh_ct orgh
628 SET orgh.orgh_sub_org_has_workers_flag = 'Y'
629 WHERE orgh.rowid IN (SELECT sub_org.rowid
630 FROM hri_cs_orgh_ct sub_org
631 WHERE sub_org.orgh_organztn_fk = g_new_orgs_with_worker(i)
632 );
633 --
634 COMMIT;
635 --
636 -- Write timing information to log
637 output('orgh_sub_org_has_workers_flag updated incrementally: ' ||
638 to_char(sysdate,'HH24:MI:SS'));
639 --
640 -- Update flag orgh_node_has_workers_flag. This flag is set to 'Y' when
641 -- the supervisor organization has workers for any of its subordinate
642 -- organizations. Else, it is 'N'.
643 --
644 FORALL i IN g_new_orgs_with_worker.FIRST..g_new_orgs_with_worker.LAST
645 UPDATE hri_cs_orgh_ct orgh
646 SET orgh.orgh_sub_node_has_workers_flag = 'Y'
647 WHERE orgh.rowid IN (
648 SELECT sup_org.rowid
649 FROM hri_cs_orgh_ct sup_org
650 WHERE EXISTS (SELECT null
651 FROM hri_cs_orgh_ct sub_org
652 WHERE sup_org.orgh_organztn_fk = sub_org.orgh_sup_organztn_fk
653 AND sub_org.orgh_organztn_fk = g_new_orgs_with_worker(i)
654 AND sub_org.orgh_sub_org_has_workers_flag = 'Y')
655 AND sup_org.orgh_sub_node_has_workers_flag = 'N');
656 --
657 COMMIT;
658 --
659 -- Write timing information to log */
660 output('orgh_node_has_workers_flag updated incrementally: ' ||
661 to_char(sysdate,'HH24:MI:SS'));
662 END IF;
663 --
664 output('Exiting worker flag update process: ' ||
665 to_char(sysdate,'HH24:MI:SS'));
666 --
667 EXCEPTION
668 --
669 WHEN OTHERS THEN
670 RAISE;
671 --
672
673 END upd_org_has_worker_flags_incr;
674 --
675 /******************************************************************************/
676 /* Main entry point to reload the organization hierarchy table */
677 /******************************************************************************/
678 PROCEDURE load( p_chunk_size IN NUMBER ) IS
679
680 l_sql_stmt VARCHAR2(2000);
681 l_dummy1 VARCHAR2(2000);
682 l_dummy2 VARCHAR2(2000);
683 l_schema VARCHAR2(400);
684
685 BEGIN
686
687 /* Set chunk size */
688 IF (p_chunk_size IS NULL) THEN
689 g_chunk_size := 1500;
690 ELSE
691 g_chunk_size := p_chunk_size;
692 END IF;
693 g_user_id := fnd_global.user_id;
694 g_sysdate := sysdate;
695
696 /* Time at start */
697 output('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
698
699 /* Get HRI schema name - get_app_info populates l_schema */
700 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
701
702 /* Empty out organization hierarchy tables */
703 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_ORGH_CT';
704 EXECUTE IMMEDIATE(l_sql_stmt);
705
706 /* Write timing information to log */
707 output('Truncated organization hierarchy tables: ' ||
708 to_char(sysdate,'HH24:MI:SS'));
709
710 /* Disable WHO trigger */
711 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ORGH_CT_WHO DISABLE');
712
713 /* Drop all the indexes on the table */
714 hri_utl_ddl.log_and_drop_indexes
715 (p_application_short_name => 'HRI',
716 p_table_name => 'HRI_CS_ORGH_CT',
717 p_table_owner => l_schema);
718
719 /* Write timing information to log */
720 output('Disabled indexes/WHO trigger: ' ||
721 to_char(sysdate,'HH24:MI:SS'));
722
723 /* Insert new organization hierarchy records */
724 collect_org_structures;
725
726 /* Write timing information to log */
727 output('Re-populated organization hierarchy table: ' ||
728 to_char(sysdate,'HH24:MI:SS'));
729
730 /* Recreate indexes */
731 hri_utl_ddl.recreate_indexes
732 (p_application_short_name => 'HRI',
733 p_table_name => 'HRI_CS_ORGH_CT',
734 p_table_owner => l_schema);
735
736 /* Update flags to determine if orgs/nodes have workers */
737
738 upd_org_has_worker_flags_full;
739
740 /* Enable WHO trigger */
741 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ORGH_CT_WHO ENABLE');
742
743 /* Write timing information to log */
744 output('Enabled indexes/WHO trigger: ' ||
745 to_char(sysdate,'HH24:MI:SS'));
746
747 END IF;
748
749 END load;
750
751 /******************************************************************************/
752 /* Load HRI_CS_ORGANZTN_CT table in full */
753 /******************************************************************************/
754
755 PROCEDURE load_org_with_workers_full IS
756
757 l_sql_stmt VARCHAR2(2000);
758 l_dummy1 VARCHAR2(2000);
759 l_dummy2 VARCHAR2(2000);
760 l_schema VARCHAR2(400);
761
762 BEGIN
763
764 /* Time at start */
765 output('Start Loading Org with workers table: ' || to_char(sysdate,'HH24:MI:SS'));
766 --
767 /* Get HRI schema name - get_app_info populates l_schema */
768 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
769
770 /* Empty out organization with workers table */
771 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_ORGANZTN_CT';
772 EXECUTE IMMEDIATE(l_sql_stmt);
773
774 /* Write timing information to log */
775 output('Truncated org with worker table: ' ||
776 to_char(sysdate,'HH24:MI:SS'));
777
778 /* Disable WHO trigger */
779 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ORGANZTN_CT_WHO DISABLE');
780
781 /* Drop all the indexes on the table */
782 hri_utl_ddl.log_and_drop_indexes
783 (p_application_short_name => 'HRI',
784 p_table_name => 'HRI_CS_ORGANZTN_CT',
785 p_table_owner => l_schema);
786
787 /* Write timing information to log */
788 output('Disabled indexes/WHO trigger: ' ||
789 to_char(sysdate,'HH24:MI:SS'));
790
791 /* Enable parallel DML */
792 run_sql_stmt_noerr('ALTER SESSION ENABLE PARALLEL DML');
793
794 /* Insert new org with worker records */
795 INSERT /*+ APPEND */ INTO HRI_CS_ORGANZTN_CT
796 (org_organztn_pk,
797 org_has_workers_flag,
798 last_update_date,
799 last_updated_by,
800 last_update_login,
801 created_by,
802 creation_date)
803 SELECT DISTINCT
804 organization_id org_organztn_pk
805 ,'Y' org_has_workers_flag
806 ,g_sysdate last_update_date
807 ,g_user_id last_updated_by
808 ,g_user_id last_update_login
809 ,g_user_id created_by
810 ,g_sysdate creation_date
811 FROM per_all_assignments_f;
812
813 COMMIT;
814
815 /* Write timing information to log */
816 output('Re-populated org with workers table: ' ||
817 to_char(sysdate,'HH24:MI:SS'));
818
819 /* Recreate indexes */
820 hri_utl_ddl.recreate_indexes
821 (p_application_short_name => 'HRI',
822 p_table_name => 'HRI_CS_ORGANZTN_CT',
823 p_table_owner => l_schema);
824
825 /* Enable WHO trigger */
826 run_sql_stmt_noerr('ALTER TRIGGER HRI_CS_ORGANZTN_CT_WHO ENABLE');
827
828 /* Write timing information to log */
829 output('Enabled indexes/WHO trigger: ' ||
830 to_char(sysdate,'HH24:MI:SS'));
831
832 END IF;
833
834 END load_org_with_workers_full;
835 --
836 /******************************************************************************/
837 /* Load HRI_CS_ORGANZTN_CT table incrementally */
838 /******************************************************************************/
839
840 PROCEDURE load_org_with_workers_incr
841 IS
842 CURSOR new_orgs_with_worker_csr
843 IS
844 --
845 SELECT DISTINCT asg.organization_id
846 FROM per_all_assignments_f asg
847 WHERE not exists (SELECT null
848 FROM hri_cs_organztn_ct org
849 WHERE org.org_organztn_pk = asg.organization_id);
850 --
851 BEGIN
852 --
853 output('Inside load_org_with_workers_incr: ' ||
854 to_char(sysdate,'HH24:MI:SS'));
855 --
856 OPEN new_orgs_with_worker_csr;
857 FETCH new_orgs_with_worker_csr BULK COLLECT INTO g_new_orgs_with_worker;
858 CLOSE new_orgs_with_worker_csr;
859 --
860 -- Insert records only if the cursor return records
861 --
862 IF g_new_orgs_with_worker.COUNT > 0 THEN
863 --
864 FORALL i IN g_new_orgs_with_worker.FIRST..g_new_orgs_with_worker.LAST
865 INSERT INTO HRI_CS_ORGANZTN_CT
866 (org_organztn_pk
867 ,org_has_workers_flag
868 ,last_update_date
869 ,last_updated_by
870 ,last_update_login
871 ,created_by
872 ,creation_date)
873 VALUES
874 (g_new_orgs_with_worker(i)
875 ,'Y'
876 ,g_sysdate
877 ,g_user_id
878 ,g_user_id
879 ,g_user_id
880 ,g_sysdate
881 );
882 --
883 output('Inserted records incrementally: ' ||
884 to_char(sysdate,'HH24:MI:SS'));
885 END IF;
886 --
887 output('Exiting load_org_with_workers_incr: ' ||
888 to_char(sysdate,'HH24:MI:SS'));
889 EXCEPTION
890 WHEN OTHERS THEN
891 -- unexpected error has occurred so close down
892 -- main bulk cursor if it is open
893 IF new_orgs_with_worker_csr%ISOPEN THEN
894 CLOSE new_orgs_with_worker_csr;
895 END IF;
896 -- re-raise error
897 RAISE;
898 END load_org_with_workers_incr;
899
900 /******************************************************************************/
901 /* Loads table to populate organization having workers */
902 /******************************************************************************/
903
904 PROCEDURE load_org_with_workers
905 IS
906 --
907 l_full_refresh VARCHAR2(30);
908 --
909 BEGIN
910
911 /* Determine full or incremental refresh */
912
913 l_full_refresh := hri_oltp_conc_param.get_parameter_value
914 (p_parameter_name => 'FULL_REFRESH',
915 p_process_table_name => 'HRI_CS_ORGANZTN_CT');
916
917 IF l_full_refresh = 'Y' THEN
918
919 /* Call main function to full refresh */
920 load_org_with_workers_full;
921
922 ELSE
923
924 /* Incrementally include new organizations with workers */
925
926 load_org_with_workers_incr;
927
928 END IF;
929
930 /* Log process end */
931 hri_bpl_conc_log.record_process_start('HRI_CS_ORGANZTN_CT');
932 hri_bpl_conc_log.log_process_end(
933 p_status => TRUE
934 ,p_period_from => TRUNC(SYSDATE)
935 ,p_period_to => TRUNC(SYSDATE)
936 ,p_attribute1 => l_full_refresh);
937
938 END load_org_with_workers;
939 --
940 /******************************************************************************/
941 /* Entry point to be called from the concurrent manager */
942 /******************************************************************************/
943 PROCEDURE load( errbuf OUT NOCOPY VARCHAR2,
944 retcode OUT NOCOPY VARCHAR2,
945 p_chunk_size IN NUMBER )
946
947 IS
948
949 l_full_refresh VARCHAR2(30);
950
951 BEGIN
952 --
953 g_user_id := fnd_global.user_id;
954 g_sysdate := sysdate;
955
956 /* Enable output to concurrent request log */
957 g_conc_request_flag := TRUE;
958
959 /* Load table to populate organizations with workers */
960
961 load_org_with_workers;
962
963 /* Determine full or incremental refresh */
964 l_full_refresh := hri_oltp_conc_param.get_parameter_value
965 (p_parameter_name => 'FULL_REFRESH',
966 p_process_table_name => 'HRI_CS_ORGH_CT');
967
968 IF l_full_refresh = 'Y' THEN
969
970 /* Call main function to full refresh */
971 load
972 (p_chunk_size => p_chunk_size);
973
974 ELSE
975
976 /* Incremental support for update of worker flags when a worker is */
977 /* assigned a organization not existing in table HRI_CS_ORGANZTN_CT */
978
979 upd_org_has_worker_flags_incr;
980
981 /* Incremental support still to be added if it can be done */
982 /* in a way to pick up new additions to structure only and */
983 /* not support changes/updates to existing relationships */
984 --
985 END IF;
986
987 /* Log process end */
988 hri_bpl_conc_log.record_process_start('HRI_CS_ORGH_CT');
989 hri_bpl_conc_log.log_process_end(
990 p_status => TRUE
991 ,p_period_from => TRUNC(SYSDATE)
992 ,p_period_to => TRUNC(SYSDATE)
993 ,p_attribute1 => l_full_refresh);
994
995 EXCEPTION
996 WHEN OTHERS THEN
997 errbuf := SQLERRM;
998 retcode := SQLCODE;
999
1000 END load;
1001
1002 END hri_opl_orgh_ct;