1 PACKAGE BODY hri_opl_posh AS
2 /* $Header: hrioposh.pkb 120.1 2005/06/08 02:53:23 anmajumd noship $ */
3
4 /******************************************************************************/
5 /* */
6 /* OUTLINE / DEFINITIONS */
7 /* */
8 /* A chain is defined for a position as a list starting with the position */
9 /* which contains and successive higher level positions finishing with the */
10 /* highest level (top) position. */
11 /* */
12 /* IMPLEMENTATION LOGIC */
13 /* */
14 /* The position hierarchy table is populated by carrying out the following */
15 /* steps: */
16 /* */
17 /* 1) Empty out existing table */
18 /* */
19 /* 2) Loop through a view containing top positions for each position */
20 /* hierarchy version */
21 /* */
22 /* i) Insert top position chain into the position hierarchy table */
23 /* ii) Insert chain for all positions in that position hierarchy version */
24 /* making use of the data structure to avoid recalculating the same */
25 /* 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_pos_structure_elements.business_group_id%TYPE
44 ,position_id per_pos_structure_elements.parent_position_id%TYPE);
45
46 /* Table type to hold information about the current chain */
47 TYPE g_chain_type IS TABLE OF g_link_record_type INDEX BY BINARY_INTEGER;
48
49 /* Global structure holding information about the current chain */
50 g_crrnt_chain g_chain_type;
51
52 /* Simple table types */
53 TYPE g_date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
54 TYPE g_number_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
55 TYPE g_varchar2_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
56
57 /* PLSQL table of tables representing database table */
58 g_dbtab_pst_id g_number_tab_type;
59 g_dbtab_prm_flg g_varchar2_tab_type;
60 g_dbtab_psv_id g_number_tab_type;
61 g_dbtab_vno_id g_number_tab_type;
62 g_dbtab_bgr_id g_number_tab_type;
63 g_dbtab_sup_pos_id g_number_tab_type;
64 g_dbtab_sup_level g_number_tab_type;
65 g_dbtab_sub_pos_id g_number_tab_type;
66 g_dbtab_sub_level g_number_tab_type;
67 g_dbtab_sub_rlt_lvl g_number_tab_type;
68 g_dbtab_start_date g_date_tab_type;
69 g_dbtab_end_date g_date_tab_type;
70
71 /* Global variables for current position structure version */
72 g_position_structure_id NUMBER;
73 g_pos_structure_version_id NUMBER;
74 g_version_number NUMBER;
75 g_effective_start_date NUMBER;
76 g_effective_end_date NUMBER;
77
78 /* Global tables for bulk fetch */
79 g_fetch_top_pos_id g_number_tab_type;
80 g_fetch_psv_id g_number_tab_type;
81 g_fetch_pst_id g_number_tab_type;
82 g_fetch_vno_id g_number_tab_type;
83 g_fetch_prm_flg g_varchar2_tab_type;
84 g_fetch_start_dt g_date_tab_type;
85 g_fetch_end_dt g_date_tab_type;
86 g_fetch_bgr_id g_number_tab_type;
87
88 g_stored_rows_to_insert PLS_INTEGER; -- Number of row to insert
89
90 /* Set to true to output to a concurrent log file */
91 g_conc_request_flag BOOLEAN := FALSE;
92
93 /* Number of rows bulk processed at a time */
94 g_chunk_size PLS_INTEGER;
95
96 /* End of time date */
97 g_end_of_time DATE := hr_general.end_of_time;
98
99 /******************************************************************************/
100 /* Inserts row into concurrent program log when the g_conc_request_flag has */
101 /* been set to TRUE, otherwise does nothing */
102 /******************************************************************************/
103 PROCEDURE output(p_text VARCHAR2)
104 IS
105
106 BEGIN
107
108 /* Write to the concurrent request log if called from a concurrent request */
109 IF (g_conc_request_flag = TRUE) THEN
110
111 /* Put text to log file */
112 fnd_file.put_line(FND_FILE.log, p_text);
113
114 END IF;
115
116 END output;
117
118 /******************************************************************************/
119 /* Recovers rows to insert when an exception occurs */
120 /******************************************************************************/
121 PROCEDURE recover_insert_rows IS
122
123 BEGIN
124 -- loop through rows still to insert one at a time
125 FOR i IN 1..g_stored_rows_to_insert LOOP
126
127 -- Trap unique constraint errors
128 BEGIN
129
130 INSERT INTO hri_cs_posh
131 (position_structure_id
132 ,primary_hierarchy_flag_code
133 ,pos_structure_version_id
134 ,version_number
135 ,business_group_id
136 ,sup_position_id
137 ,sup_level
138 ,sub_position_id
139 ,sub_level
140 ,sub_relative_level
141 ,effective_start_date
142 ,effective_end_date)
143 VALUES
144 (g_dbtab_pst_id(i)
145 ,g_dbtab_prm_flg(i)
146 ,g_dbtab_psv_id(i)
147 ,g_dbtab_vno_id(i)
148 ,g_dbtab_bgr_id(i)
149 ,g_dbtab_sup_pos_id(i)
150 ,g_dbtab_sup_level(i)
151 ,g_dbtab_sub_pos_id(i)
152 ,g_dbtab_sub_level(i)
153 ,g_dbtab_sub_rlt_lvl(i)
154 ,g_dbtab_start_date(i)
155 ,g_dbtab_end_date(i));
156
157 EXCEPTION
158 WHEN OTHERS THEN
159
160 /* Probable overlap on date tracked assignment rows */
161 output('Single insert error: ' || to_char(g_dbtab_sub_pos_id(i)) ||
162 ' - ' || to_char(g_dbtab_sup_pos_id(i)));
163 output('Inserting date range: ' ||
164 to_char(g_dbtab_start_date(i),'DD-MON-YYYY') || ' - ' ||
165 to_char(g_dbtab_end_date(i),'DD-MON-YYYY'));
166 output(sqlerrm);
167 output(sqlcode);
168
169 END;
170
171 END LOOP;
172
173 -- commit
174 commit;
175
176 END recover_insert_rows;
177
178 /******************************************************************************/
179 /* Bulk inserts rows from global temporary table to database table */
180 /******************************************************************************/
181 PROCEDURE bulk_insert_rows IS
182
183 BEGIN
184 -- insert chunk of rows
185 FORALL i IN 1..g_stored_rows_to_insert
186 INSERT INTO hri_cs_posh
187 (position_structure_id
188 ,primary_hierarchy_flag_code
189 ,pos_structure_version_id
190 ,version_number
191 ,business_group_id
192 ,sup_position_id
193 ,sup_level
194 ,sub_position_id
195 ,sub_level
196 ,sub_relative_level
197 ,effective_start_date
198 ,effective_end_date)
199 VALUES
200 (g_dbtab_pst_id(i)
201 ,g_dbtab_prm_flg(i)
202 ,g_dbtab_psv_id(i)
203 ,g_dbtab_vno_id(i)
204 ,g_dbtab_bgr_id(i)
205 ,g_dbtab_sup_pos_id(i)
206 ,g_dbtab_sup_level(i)
207 ,g_dbtab_sub_pos_id(i)
208 ,g_dbtab_sub_level(i)
209 ,g_dbtab_sub_rlt_lvl(i)
210 ,g_dbtab_start_date(i)
211 ,g_dbtab_end_date(i));
212 -- commit the chunk of rows
213 COMMIT;
214 EXCEPTION
215 WHEN OTHERS THEN
216
217 /* Unique constraint error */
218 rollback;
219 recover_insert_rows;
220
221 END bulk_insert_rows;
222
223 /******************************************************************************/
224 /* Inserts row into global temporary table */
225 /******************************************************************************/
226 PROCEDURE insert_row( p_business_group_id IN NUMBER
227 , p_sup_position_id IN NUMBER
228 , p_sup_level IN NUMBER
229 , p_sub_position_id IN NUMBER
230 , p_sub_level IN NUMBER
231 , p_index IN NUMBER ) IS
232
233 BEGIN
234 -- increment the index
235 g_stored_rows_to_insert := g_stored_rows_to_insert + 1;
236 -- set the table structures
237 g_dbtab_pst_id(g_stored_rows_to_insert) := g_fetch_pst_id(p_index);
238 g_dbtab_prm_flg(g_stored_rows_to_insert) := g_fetch_prm_flg(p_index);
239 g_dbtab_psv_id(g_stored_rows_to_insert) := g_fetch_psv_id(p_index);
240 g_dbtab_vno_id(g_stored_rows_to_insert) := g_fetch_vno_id(p_index);
241 g_dbtab_bgr_id(g_stored_rows_to_insert) := p_business_group_id;
242 g_dbtab_sup_pos_id(g_stored_rows_to_insert) := p_sup_position_id;
243 g_dbtab_sup_level(g_stored_rows_to_insert) := p_sup_level;
244 g_dbtab_sub_pos_id(g_stored_rows_to_insert) := p_sub_position_id;
245 g_dbtab_sub_level(g_stored_rows_to_insert) := p_sub_level;
246 g_dbtab_sub_rlt_lvl(g_stored_rows_to_insert) := p_sub_level - p_sup_level;
247 g_dbtab_start_date(g_stored_rows_to_insert) := g_fetch_start_dt(p_index);
248 g_dbtab_end_date(g_stored_rows_to_insert) := g_fetch_end_dt(p_index);
249 END insert_row;
250
251 /******************************************************************************/
252 /* Updates all positions in the position hierarchy version starting with the */
253 /* top position. */
254 /******************************************************************************/
255 PROCEDURE calculate_chains( p_index IN NUMBER ) IS
256
257 /* Cursor picks out all positions in the position structure */
258 /* This cursor MUST return rows in the default order */
259 CURSOR positions_csr IS
260 SELECT
261 hier.business_group_id business_group_id
262 ,hier.subordinate_position_id position_id
263 ,LEVEL+1 actual_level
264 FROM (SELECT
265 pse.business_group_id
266 ,pse.subordinate_position_id
267 ,pse.parent_position_id
268 FROM
269 per_pos_structure_elements pse
270 WHERE pse.pos_structure_version_id = g_fetch_psv_id(p_index)) hier
271 START WITH hier.parent_position_id = g_fetch_top_pos_id(p_index)
272 CONNECT BY PRIOR hier.subordinate_position_id = parent_position_id;
273 /******************************/
274 /* DO NOT ADD ORDER BY CLAUSE */
275 /******************************/
276
277 l_pos_lvl PLS_INTEGER;
278
279 BEGIN
280
281 /* Store details for top position */
282 g_crrnt_chain(1).business_group_id := g_fetch_bgr_id(p_index);
283 g_crrnt_chain(1).position_id := g_fetch_top_pos_id(p_index);
284
285 /* Insert chain */
286 insert_row
287 (p_business_group_id => g_crrnt_chain(1).business_group_id
288 ,p_sup_position_id => g_crrnt_chain(1).position_id
289 ,p_sup_level => 1
290 ,p_sub_position_id => g_crrnt_chain(1).position_id
291 ,p_sub_level => 1
292 ,p_index => p_index);
293
294 /* Loop through positions in position hierarchy veresion */
295 FOR pos_rec IN positions_csr LOOP
296
297 l_pos_lvl := pos_rec.actual_level;
298
299 g_crrnt_chain(l_pos_lvl).business_group_id := pos_rec.business_group_id;
300 g_crrnt_chain(l_pos_lvl).position_id := pos_rec.position_id;
301
302 /* Loop through links in (stored) chain of positions */
303 FOR l_sup_lvl IN 1..l_pos_lvl LOOP
304
305 /* Insert chain */
306 insert_row
307 (p_business_group_id => g_crrnt_chain(l_sup_lvl).business_group_id
308 ,p_sup_position_id => g_crrnt_chain(l_sup_lvl).position_id
309 ,p_sup_level => l_sup_lvl
310 ,p_sub_position_id => g_crrnt_chain(l_pos_lvl).position_id
311 ,p_sub_level => l_pos_lvl
312 ,p_index => p_index);
313
314 END LOOP; -- Links in stored chain
315
316 END LOOP; -- Positions in hierarchy version
317
318 EXCEPTION
319 WHEN OTHERS THEN
320
321 /* ORA 01436 - loop in tree walk */
322 IF (SQLCODE = -1436) THEN
323 output('Loop found for position id: ' ||
324 to_char(g_fetch_top_pos_id(p_index)));
325 ELSE
326 /* Some other error */
327 RAISE;
328 END IF;
329
330 END calculate_chains;
331
332 /******************************************************************************/
333 /* Loops through position structure versions */
334 /******************************************************************************/
335 PROCEDURE collect_pos_structures IS
336
337 /* Pick out all position structure versions and their top positions */
338 CURSOR hrchy_version_csr IS
339 SELECT DISTINCT
340 pse.parent_position_id
341 ,psv.pos_structure_version_id
342 ,psv.position_structure_id
343 ,psv.version_number
344 ,pst.primary_position_flag
345 ,psv.date_from
346 ,NVL(psv.date_to,g_end_of_time)
347 ,psv.business_group_id
348 FROM
349 per_pos_structure_elements pse
350 ,per_pos_structure_versions psv
351 ,per_position_structures pst
352 WHERE psv.pos_structure_version_id = pse.pos_structure_version_id
353 AND pst.position_structure_id = psv.position_structure_id
354 AND pst.primary_position_flag = 'Y'
355 AND NOT EXISTS
356 (SELECT NULL
357 FROM per_pos_structure_elements pse2
358 WHERE pse2.pos_structure_version_id = pse.pos_structure_version_id
359 AND pse2.subordinate_position_id = pse.parent_position_id);
360
361 l_return_code PLS_INTEGER;
362 l_exit_main_loop BOOLEAN := FALSE;
363 l_rows_fetched PLS_INTEGER := g_chunk_size;
364
365 BEGIN
366 -- initialise the g_stored_rows_to_insert
367 g_stored_rows_to_insert := 0;
368 -- open main cursor
369 OPEN hrchy_version_csr;
370 <<main_loop>>
371 LOOP
372 -- bulk fetch rows limit the fetch to value of g_chunk_size
373 FETCH hrchy_version_csr
374 BULK COLLECT INTO
375 g_fetch_top_pos_id,
376 g_fetch_psv_id,
377 g_fetch_pst_id,
378 g_fetch_vno_id,
379 g_fetch_prm_flg,
380 g_fetch_start_dt,
381 g_fetch_end_dt,
382 g_fetch_bgr_id
383 LIMIT g_chunk_size;
384 -- check to see if the last row has been fetched
385 IF hrchy_version_csr%NOTFOUND THEN
386 -- last row fetched, set exit loop flag
387 l_exit_main_loop := TRUE;
388 -- do we have any rows to process?
389 l_rows_fetched := MOD(hrchy_version_csr%ROWCOUNT,g_chunk_size);
390 -- note: if l_rows_fetched > 0 then more rows are required to be
391 -- processed and the l_rows_fetched will contain the exact number of
392 -- rows left to process
393 IF l_rows_fetched = 0 THEN
394 -- no more rows to process so exit loop
395 EXIT main_loop;
396 END IF;
397 END IF;
398
399 -- Loop through position hierarchy versions
400 FOR i IN 1..l_rows_fetched LOOP
401
402 calculate_chains( p_index => i );
403
404 END LOOP;
405 -- bulk insert rows processed so far
406 bulk_insert_rows;
407 -- reset the index
408 g_stored_rows_to_insert := 0;
409 -- exit loop if required
410 IF l_exit_main_loop THEN
411 EXIT main_loop;
412 END IF;
413 END LOOP;
414 CLOSE hrchy_version_csr;
415 EXCEPTION
416 WHEN OTHERS THEN
417 -- unexpected error has occurred so close down
418 -- main bulk cursor if it is open
419 IF hrchy_version_csr%ISOPEN THEN
420 CLOSE hrchy_version_csr;
421 END IF;
422 -- re-raise error
423 RAISE;
424 END collect_pos_structures;
425
426 /******************************************************************************/
427 /* Main entry point to reload the position hierarchy table */
428 /******************************************************************************/
429 PROCEDURE Load_all_positions( p_chunk_size IN NUMBER ) IS
430
431 l_sql_stmt VARCHAR2(2000);
432 l_dummy1 VARCHAR2(2000);
433 l_dummy2 VARCHAR2(2000);
434 l_schema VARCHAR2(400);
435
436 BEGIN
437
438 /* Set chunk size */
439 g_chunk_size := p_chunk_size;
440
441 /* Time at start */
442 output('PL/SQL Start: ' || to_char(sysdate,'HH24:MI:SS'));
443
444 /* Get HRI schema name - get_app_info populates l_schema */
445 IF fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema) THEN
446
447 /* Empty out position hierarchy table */
448 l_sql_stmt := 'TRUNCATE TABLE ' || l_schema || '.HRI_CS_POSH';
449 EXECUTE IMMEDIATE(l_sql_stmt);
450
451 /* Write timing information to log */
452 output('Truncated Position Hierarchy table: ' ||
453 to_char(sysdate,'HH24:MI:SS'));
454
455 /* Insert new position hierarchy records */
456 collect_pos_structures;
457
458 /* Write timing information to log */
459 output('Re-populated Position Hierarchy table: ' ||
460 to_char(sysdate,'HH24:MI:SS'));
461
462 /* Gather index stats */
463 fnd_stats.gather_table_stats(l_schema, 'HRI_CS_POSH');
464
465 /* Write timing information to log */
466 output('Gathered stats: ' ||
467 to_char(sysdate,'HH24:MI:SS'));
468
469 END IF;
470
471 END Load_all_positions;
472
473 /******************************************************************************/
474 /* Entry point to be called from the concurrent manager */
475 /******************************************************************************/
476 PROCEDURE load_all_positions( errbuf OUT NOCOPY VARCHAR2,
477 retcode OUT NOCOPY VARCHAR2,
478 p_chunk_size IN NUMBER )
479
480 IS
481
482 BEGIN
483
484 /* Enable output to concurrent request log */
485 g_conc_request_flag := TRUE;
486
487 /* Call main function */
488 load_all_positions
489 (p_chunk_size => p_chunk_size);
490
491 EXCEPTION
492 WHEN OTHERS THEN
493 errbuf := SQLERRM;
494 retcode := SQLCODE;
495
496 END load_all_positions;
497
498 END hri_opl_posh;
[Home] [Help]