1 PACKAGE BODY per_opl_orgh AS
2 /* $Header: perporgh.pkb 115.1 2004/06/17 03:25:22 vanantha 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) Loop through a view containing top organizations for each organization */
20 /* hierarchy version */
21 /* */
22 /* i) Insert top organization chain into the organization hierarchy table */
23 /* ii) Insert chain for all organizations in that organization hierarchy */
24 /* version making use of the data structure to avoid recalculating the */
25 /* same information twice */
26 /* */
27 /* 3) Global structures are used to: */
28 /* */
29 /* i) Bulk fetch the main loop */
30 /* ii) Bulk insert the chains into the hierarchy table */
31 /* iii) Store information about the current chain being processed */
32 /* iv) Keep a note of which chains have been processed on a particular */
33 /* date to avoid re-processing the same information */
34 /* v) Keep a note of the date each chain starts, so that the next time */
35 /* a chain is processed (on an earlier date) the end date is known */
36 /* vi) Store the terminated assignment status types so that it is quick to */
37 /* find out which are invalid at insert time */
38 /* */
39 /******************************************************************************/
40
41 /* Information to be held for each link in a chain */
42 TYPE g_link_record_type IS RECORD
43 (business_group_id PER_org_structure_elements.business_group_id%TYPE
44 ,organization_id PER_org_structure_elements.organization_id_parent%TYPE
45 ,last_chng_date DATE);
46
47 /* Table type to hold information about the current chain */
48 TYPE g_chain_type IS TABLE OF g_link_record_type INDEX BY BINARY_INTEGER;
49
50 /* Global structure holding information about the current chain */
51 g_crrnt_chain g_chain_type;
52
53 /* Simple table types */
54 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
55 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
56 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
57
58 /* PLSQL table of tables representing category structure table */
59 g_cs_ost_id g_number_tab_type;
60 g_cs_bgr_id g_number_tab_type;
61 g_cs_osv_id g_number_tab_type;
62 g_cs_sup_org_id g_number_tab_type;
63 g_cs_sup_level g_number_tab_type;
64 g_cs_sub_org_id g_number_tab_type;
65 g_cs_sub_level g_number_tab_type;
66 g_cs_sub_rlt_lvl g_number_tab_type;
67 g_cs_last_chng g_date_tab_type;
68
69 /* Global tables for bulk fetch */
70 g_fetch_top_org_id g_number_tab_type;
71 g_fetch_osv_id g_number_tab_type;
72 g_fetch_ost_id g_number_tab_type;
73 g_fetch_vno_id g_number_tab_type;
74 g_fetch_prm_flg g_varchar2_tab_type;
75 g_fetch_start_dt g_date_tab_type;
76 g_fetch_end_dt g_date_tab_type;
77 g_fetch_bgr_id g_number_tab_type;
78
79 g_cs_rows_to_insert PLS_INTEGER; -- Number of CS rows to insert
80
81 /* Set to true to output to a concurrent log file */
82 g_conc_request_flag BOOLEAN := FALSE;
83
84 /* Number of rows bulk processed at a time */
85 g_chunk_size PLS_INTEGER;
86
87 /* Start / End of time dates */
88 g_start_of_time DATE := hr_general.start_of_time;
89 g_end_of_time DATE := hr_general.end_of_time;
90
91 /******************************************************************************/
92 /* Inserts row into concurrent program log when the g_conc_request_flag has */
93 /* been set to TRUE, otherwise does nothing */
94 /******************************************************************************/
95 PROCEDURE output(p_text VARCHAR2)
96 IS
97
98 BEGIN
99
100 /* Write to the concurrent request log if called from a concurrent request */
101 IF (g_conc_request_flag = TRUE) THEN
102
103 /* Put text to log file */
104 fnd_file.put_line(FND_FILE.log, p_text);
105 END IF;
106
107 END output;
108
109 /******************************************************************************/
110 /* Recovers CS rows to insert when an exception occurs */
111 /******************************************************************************/
112 PROCEDURE recover_insert_cs_rows IS
113
114 BEGIN
115 -- loop through rows still to insert one at a time
116 FOR i IN 1..g_cs_rows_to_insert LOOP
117
118 -- Trap unique constraint errors
119 BEGIN
120
121 INSERT INTO /*+ PARALLEL(<table name>, DEFAULT, DEFAULT) */ PER_org_hrchy_summary
122 (organization_structure_id
123 ,org_structure_version_id
124 ,org_business_group_id
125 ,organization_id
126 ,organization_level
127 ,sub_org_business_group_id
128 ,sub_organization_id
129 ,sub_organization_level
130 ,sub_org_relative_level
131 ,last_ptntl_change)
132 VALUES
133 (g_cs_ost_id(i)
134 ,g_cs_osv_id(i)
135 ,g_cs_bgr_id(i)
136 ,g_cs_sup_org_id(i)
137 ,g_cs_sup_level(i)
138 ,g_cs_bgr_id(i)
139 ,g_cs_sub_org_id(i)
140 ,g_cs_sub_level(i)
141 ,g_cs_sub_rlt_lvl(i)
142 ,g_cs_last_chng(i));
143
144 EXCEPTION
145 WHEN OTHERS THEN
146
147 /* Probable overlap on date tracked assignment rows */
148 output('Single insert error: ' || to_char(g_cs_sub_org_id(i)) ||
149 ' - ' || to_char(g_cs_sup_org_id(i)));
150 output('Inserting chain for: ' ||
151 to_char(g_cs_sub_org_id(i)) || ' in hierarchy version' ||
152 to_char(g_cs_osv_id(i)));
153 output(sqlerrm);
154 output(sqlcode);
155
156 END;
157
158 END LOOP;
159
160 -- commit
161 commit;
162
163 END recover_insert_cs_rows;
164
165
166 /******************************************************************************/
167 /* Bulk inserts rows from global temporary table to CS database table */
168 /******************************************************************************/
169 PROCEDURE bulk_insert_cs_rows IS
170
171 BEGIN
172 -- insert chunk of rows
173 FORALL i IN 1..g_cs_rows_to_insert
174 INSERT INTO /*+ PARALLEL(<table name>, DEFAULT, DEFAULT) */ PER_org_hrchy_summary
175 (organization_structure_id
176 ,org_structure_version_id
177 ,org_business_group_id
178 ,organization_id
179 ,organization_level
180 ,sub_org_business_group_id
181 ,sub_organization_id
182 ,sub_organization_level
183 ,sub_org_relative_level
184 ,last_ptntl_change)
185 VALUES
186 (g_cs_ost_id(i)
187 ,g_cs_osv_id(i)
188 ,g_cs_bgr_id(i)
189 ,g_cs_sup_org_id(i)
190 ,g_cs_sup_level(i)
191 ,g_cs_bgr_id(i)
192 ,g_cs_sub_org_id(i)
193 ,g_cs_sub_level(i)
194 ,g_cs_sub_rlt_lvl(i)
195 ,g_cs_last_chng(i));
196 -- commit the chunk of rows
197 COMMIT;
198 EXCEPTION
199 WHEN OTHERS THEN
200
201 /* Unique constraint error */
202 rollback;
203 output('Warning - recovering (CS)');
204 recover_insert_cs_rows;
205
206 END bulk_insert_cs_rows;
207
208 /******************************************************************************/
209 /* Inserts row into global CS temporary table */
210 /******************************************************************************/
211 PROCEDURE insert_cs_row( p_sup_organization_id IN NUMBER
212 , p_sup_level IN NUMBER
213 , p_sub_organization_id IN NUMBER
214 , p_sub_level IN NUMBER
215 , p_index IN NUMBER
216 , p_last_ptntl_change IN DATE ) IS
217
218 BEGIN
219 -- increment the index
220 g_cs_rows_to_insert := g_cs_rows_to_insert + 1;
221 -- set the table structures
222 g_cs_sup_org_id(g_cs_rows_to_insert) := p_sup_organization_id;
223 g_cs_sup_level(g_cs_rows_to_insert) := p_sup_level;
224 g_cs_sub_org_id(g_cs_rows_to_insert) := p_sub_organization_id;
225 g_cs_sub_level(g_cs_rows_to_insert) := p_sub_level;
226 g_cs_sub_rlt_lvl(g_cs_rows_to_insert) := p_sub_level - p_sup_level;
227 g_cs_ost_id(g_cs_rows_to_insert) := g_fetch_ost_id(p_index);
228 g_cs_bgr_id(g_cs_rows_to_insert) := g_fetch_bgr_id(p_index);
229 g_cs_osv_id(g_cs_rows_to_insert) := g_fetch_osv_id(p_index);
230 g_cs_last_chng(g_cs_rows_to_insert) := p_last_ptntl_change;
231 END insert_cs_row;
232
233
234 /******************************************************************************/
235 /* Updates all organizations in the organization hierarchy version starting */
236 /* with the top organization. */
237 /******************************************************************************/
238 PROCEDURE calculate_chains( p_index IN NUMBER ) IS
239
240 /* Cursor picks out all organizations in the organization structure */
241 /* This cursor MUST return rows in the default order */
242 CURSOR organizations_csr IS
243 SELECT
244 hier.organization_id_child organization_id
245 ,hier.last_update_date last_update_date
246 ,LEVEL+1 actual_level
247 FROM (SELECT
248 ose.organization_id_child
249 ,ose.organization_id_parent
250 ,NVL(ose.last_update_date, g_start_of_time) last_update_date
251 FROM
252 PER_org_structure_elements ose
253 WHERE ose.org_structure_version_id = g_fetch_osv_id(p_index)) hier
254 START WITH hier.organization_id_parent = g_fetch_top_org_id(p_index)
255 CONNECT BY PRIOR hier.organization_id_child = organization_id_parent;
256 /******************************/
257 /* DO NOT ADD ORDER BY CLAUSE */
258 /******************************/
259
260 l_org_lvl PLS_INTEGER;
261 l_last_org_lvl PLS_INTEGER;
262
263 BEGIN
264
265 /* Store details for top organization */
266 g_crrnt_chain(1).organization_id := g_fetch_top_org_id(p_index);
267 g_crrnt_chain(1).last_chng_date := g_fetch_start_dt(p_index);
268
269 /* Insert chain */
270 insert_cs_row
271 (p_sup_organization_id => g_crrnt_chain(1).organization_id
272 ,p_sup_level => 1
273 ,p_sub_organization_id => g_crrnt_chain(1).organization_id
274 ,p_sub_level => 1
275 ,p_index => p_index
276 ,p_last_ptntl_change => g_crrnt_chain(1).last_chng_date);
277
278 /* Loop through organizations in organization hierarchy version */
279 FOR org_rec IN organizations_csr LOOP
280
281 l_org_lvl := org_rec.actual_level;
282
283 IF (l_last_org_lvl > l_org_lvl) THEN
284 /* Reset end of chain */
285 FOR i IN l_org_lvl+1..l_last_org_lvl LOOP
286 g_crrnt_chain(i).organization_id := to_number(null);
287 END LOOP;
288 END IF;
289
290 g_crrnt_chain(l_org_lvl).organization_id := org_rec.organization_id;
291 g_crrnt_chain(l_org_lvl).last_chng_date :=
292 GREATEST(org_rec.last_update_date,
293 g_crrnt_chain(l_org_lvl - 1).last_chng_date);
294
295 /* Loop through links in (stored) chain of organizations */
296 FOR l_sup_lvl IN 1..l_org_lvl LOOP
297
298 /* Insert chain into CS */
299 insert_cs_row
300 (p_sup_organization_id => g_crrnt_chain(l_sup_lvl).organization_id
301 ,p_sup_level => l_sup_lvl
302 ,p_sub_organization_id => g_crrnt_chain(l_org_lvl).organization_id
303 ,p_sub_level => l_org_lvl
304 ,p_index => p_index
305 ,p_last_ptntl_change => g_crrnt_chain(l_org_lvl).last_chng_date);
306
307 END LOOP; -- Links in stored chain
308
309 /* If the stored rows have reached a maximum, then insert them */
310 IF (g_cs_rows_to_insert > g_chunk_size) THEN
311 -- bulk insert rows processed so far
312 bulk_insert_cs_rows;
313 -- reset the index
314 g_cs_rows_to_insert := 0;
315 END IF;
316
317 l_last_org_lvl := l_org_lvl;
318
319 END LOOP; -- organizations in hierarchy version
320
321 EXCEPTION
322 WHEN OTHERS THEN
323
324 /* ORA 01436 - loop in tree walk */
325 IF (SQLCODE = -1436) THEN
326 output('Loop found for organization id: ' ||
327 to_char(g_fetch_top_org_id(p_index)));
328 ELSE
329 /* Some other error */
330 RAISE;
331 END IF;
332
333 END calculate_chains;
334
335 /******************************************************************************/
336 /* Loops through organization structure versions */
337 /******************************************************************************/
338 PROCEDURE collect_org_structures IS
339
340 /* Pick out all organization structure versions and their top organizations */
341 CURSOR hrchy_version_csr IS
342 SELECT /*+ use_NL(ost,osv)*/DISTINCT --Bug fix 3648736
343 ose.organization_id_parent
344 ,osv.org_structure_version_id
345 ,osv.organization_structure_id
346 ,osv.version_number
347 ,ost.primary_structure_flag
348 ,osv.date_from
349 ,NVL(osv.date_to,g_end_of_time)
350 ,osv.business_group_id
351 FROM
352 PER_org_structure_elements ose
353 ,PER_org_structure_versions osv
354 ,PER_organization_structures ost
355 WHERE osv.org_structure_version_id = ose.org_structure_version_id
356 AND ost.organization_structure_id = osv.organization_structure_id
357 AND (ost.business_group_id=osv.business_group_id --Bug fix 3648736
358 or ost.business_group_id is null
359 or osv.business_group_id is null)
360 AND (osv.business_group_id=ose.business_group_id
361 or osv.business_group_id is null
362 or ose.business_group_id is null)
363 -- AND ost.primary_structure_flag = 'Y'
364 AND NOT EXISTS
365 (SELECT NULL
366 FROM PER_org_structure_elements ose2
367 WHERE ose2.org_structure_version_id = ose.org_structure_version_id
368 AND ose2.organization_id_child = ose.organization_id_parent);
369
370 l_return_code PLS_INTEGER;
371 l_exit_main_loop BOOLEAN := FALSE;
372 l_rows_fetched PLS_INTEGER := g_chunk_size;
373
374 BEGIN
375 -- initialise the g_cs_rows_to_insert
376 g_cs_rows_to_insert := 0;
377 -- initialise the current chain for CSF structure
378 FOR i IN 1..15 LOOP
379 g_crrnt_chain(i).organization_id := to_number(null);
380 END LOOP;
381 -- open main cursor
382 OPEN hrchy_version_csr;
383 <<main_loop>>
384 LOOP
385 -- bulk fetch rows limit the fetch to value of g_chunk_size
386 FETCH hrchy_version_csr
387 BULK COLLECT INTO
388 g_fetch_top_org_id,
389 g_fetch_osv_id,
390 g_fetch_ost_id,
391 g_fetch_vno_id,
392 g_fetch_prm_flg,
393 g_fetch_start_dt,
394 g_fetch_end_dt,
395 g_fetch_bgr_id
396 LIMIT g_chunk_size;
397 -- check to see if the last row has been fetched
398 IF hrchy_version_csr%NOTFOUND THEN
399 -- last row fetched, set exit loop flag
400 l_exit_main_loop := TRUE;
401 -- do we have any rows to process?
402 l_rows_fetched := MOD(hrchy_version_csr%ROWCOUNT,g_chunk_size);
403 -- note: if l_rows_fetched > 0 then more rows are required to be
404 -- processed and the l_rows_fetched will contain the exact number of
405 -- rows left to process
406 IF l_rows_fetched = 0 THEN
407 -- no more rows to process so exit loop
408 EXIT main_loop;
409 END IF;
410 END IF;
411
412 -- Loop through organization hierarchy versions
413 FOR i IN 1..l_rows_fetched LOOP
414
415 calculate_chains( p_index => i );
416
417 END LOOP;
418 -- exit loop if required
419 IF l_exit_main_loop THEN
420 EXIT main_loop;
421 END IF;
422 END LOOP; -- main loop
423 CLOSE hrchy_version_csr;
424 /* Insert any remaining stored rows */
425 IF (g_cs_rows_to_insert > 0) THEN
426 bulk_insert_cs_rows;
427 END IF;
428 EXCEPTION
429 WHEN OTHERS THEN
430 -- unexpected error has occurred so close down
431 -- main bulk cursor if it is open
432 IF hrchy_version_csr%ISOPEN THEN
433 CLOSE hrchy_version_csr;
434 END IF;
435 -- re-raise error
436 RAISE;
437 END collect_org_structures;
438
439 /******************************************************************************/
440 /* Main entry point to reload the organization hierarchy table */
441 /******************************************************************************/
442 PROCEDURE load( p_chunk_size IN NUMBER ) IS
443
444 l_sql_stmt VARCHAR2(2000);
445 l_dummy1 VARCHAR2(2000);
446 l_dummy2 VARCHAR2(2000);
447 l_schema VARCHAR2(400);
448
449 BEGIN
450
451 /* Set chunk size */
452 g_chunk_size := p_chunk_size;
453
454 /* Time at start */
455 output('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
456
457 /* Get PER schema name - get_app_info populates l_schema */
458 IF fnd_installation.get_app_info('PER',l_dummy1, l_dummy2, l_schema) THEN
459
460 /* Empty out organization hierarchy tables */
461 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.PER_ORG_HRCHY_SUMMARY';
462 EXECUTE IMMEDIATE(l_sql_stmt);
463
464 /* Write timing information to log */
465 output('Truncated organization Hierarchy tables: ' ||
466 to_char(sysdate,'HH24:MI:SS'));
467
468 /* Insert new organization hierarchy records */
469 collect_org_structures;
470
471 /* Write timing information to log */
472 output('Re-populated organization Hierarchy table: ' ||
473 to_char(sysdate,'HH24:MI:SS'));
474
475 /* Gather index stats */
476 -- fnd_stats.gather_table_stats(l_schema, 'PER_CS_orgH');
477
478 /* Write timing information to log */
479 -- output('Gathered stats: ' ||
480 -- to_char(sysdate,'HH24:MI:SS'));
481
482 END IF;
483
484 END load;
485
486 /******************************************************************************/
487 /* Entry point to be called from the concurrent manager */
488 /******************************************************************************/
489 PROCEDURE load_all_organizations( errbuf OUT nocopy VARCHAR2,
490 retcode OUT nocopy VARCHAR2,
491 p_chunk_size IN NUMBER )
492
493 IS
494
495 BEGIN
496
497 /* Enable output to concurrent request log */
498 g_conc_request_flag := TRUE;
499
500 /* Call main function */
501 load(p_chunk_size => p_chunk_size);
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 errbuf := SQLERRM;
506 retcode := SQLCODE;
507
508 END load_all_organizations;
509
510 END PER_opl_orgh;
[Home] [Help]