[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* i) Insert top position chain into the position hierarchy table */
/* ii) Insert chain for all positions in that position hierarchy version */
/* making use of the data structure to avoid recalculating the same */
/* information twice */
/* */
/* 3) Global structures are used to: */
/* */
/* i) Bulk fetch the main loop */
/* ii) Bulk insert the chains into the hierarchy table */
/* iii) Store information about the current chain being processed */
/* iv) Keep a note of which chains have been processed on a particular */
/* date to avoid re-processing the same information */
/* v) Keep a note of the date each chain starts, so that the next time */
/* a chain is processed (on an earlier date) the end date is known */
/* vi) Store the terminated assignment status types so that it is quick to */
/* find out which are invalid at insert time */
/* */
/******************************************************************************/
/* Information to be held for each link in a chain */
TYPE g_link_record_type IS RECORD
(business_group_id per_pos_structure_elements.business_group_id%TYPE
,position_id per_pos_structure_elements.parent_position_id%TYPE);
g_stored_rows_to_insert PLS_INTEGER; -- Number of row to insert
/* Inserts row into concurrent program log when the g_conc_request_flag has */
/* been set to TRUE, otherwise does nothing */
/******************************************************************************/
PROCEDURE output(p_text VARCHAR2)
IS
BEGIN
/* Write to the concurrent request log if called from a concurrent request */
IF (g_conc_request_flag = TRUE) THEN
/* Put text to log file */
fnd_file.put_line(FND_FILE.log, p_text);
/* Recovers rows to insert when an exception occurs */
/******************************************************************************/
PROCEDURE recover_insert_rows IS
BEGIN
-- loop through rows still to insert one at a time
FOR i IN 1..g_stored_rows_to_insert LOOP
-- Trap unique constraint errors
BEGIN
INSERT INTO hri_cs_posh
(position_structure_id
,primary_hierarchy_flag_code
,pos_structure_version_id
,version_number
,business_group_id
,sup_position_id
,sup_level
,sub_position_id
,sub_level
,sub_relative_level
,effective_start_date
,effective_end_date)
VALUES
(g_dbtab_pst_id(i)
,g_dbtab_prm_flg(i)
,g_dbtab_psv_id(i)
,g_dbtab_vno_id(i)
,g_dbtab_bgr_id(i)
,g_dbtab_sup_pos_id(i)
,g_dbtab_sup_level(i)
,g_dbtab_sub_pos_id(i)
,g_dbtab_sub_level(i)
,g_dbtab_sub_rlt_lvl(i)
,g_dbtab_start_date(i)
,g_dbtab_end_date(i));
output('Single insert error: ' || to_char(g_dbtab_sub_pos_id(i)) ||
' - ' || to_char(g_dbtab_sup_pos_id(i)));
output('Inserting date range: ' ||
to_char(g_dbtab_start_date(i),'DD-MON-YYYY') || ' - ' ||
to_char(g_dbtab_end_date(i),'DD-MON-YYYY'));
END recover_insert_rows;
/* Bulk inserts rows from global temporary table to database table */
/******************************************************************************/
PROCEDURE bulk_insert_rows IS
BEGIN
-- insert chunk of rows
FORALL i IN 1..g_stored_rows_to_insert
INSERT INTO hri_cs_posh
(position_structure_id
,primary_hierarchy_flag_code
,pos_structure_version_id
,version_number
,business_group_id
,sup_position_id
,sup_level
,sub_position_id
,sub_level
,sub_relative_level
,effective_start_date
,effective_end_date)
VALUES
(g_dbtab_pst_id(i)
,g_dbtab_prm_flg(i)
,g_dbtab_psv_id(i)
,g_dbtab_vno_id(i)
,g_dbtab_bgr_id(i)
,g_dbtab_sup_pos_id(i)
,g_dbtab_sup_level(i)
,g_dbtab_sub_pos_id(i)
,g_dbtab_sub_level(i)
,g_dbtab_sub_rlt_lvl(i)
,g_dbtab_start_date(i)
,g_dbtab_end_date(i));
recover_insert_rows;
END bulk_insert_rows;
/* Inserts row into global temporary table */
/******************************************************************************/
PROCEDURE insert_row( p_business_group_id IN NUMBER
, p_sup_position_id IN NUMBER
, p_sup_level IN NUMBER
, p_sub_position_id IN NUMBER
, p_sub_level IN NUMBER
, p_index IN NUMBER ) IS
BEGIN
-- increment the index
g_stored_rows_to_insert := g_stored_rows_to_insert + 1;
g_dbtab_pst_id(g_stored_rows_to_insert) := g_fetch_pst_id(p_index);
g_dbtab_prm_flg(g_stored_rows_to_insert) := g_fetch_prm_flg(p_index);
g_dbtab_psv_id(g_stored_rows_to_insert) := g_fetch_psv_id(p_index);
g_dbtab_vno_id(g_stored_rows_to_insert) := g_fetch_vno_id(p_index);
g_dbtab_bgr_id(g_stored_rows_to_insert) := p_business_group_id;
g_dbtab_sup_pos_id(g_stored_rows_to_insert) := p_sup_position_id;
g_dbtab_sup_level(g_stored_rows_to_insert) := p_sup_level;
g_dbtab_sub_pos_id(g_stored_rows_to_insert) := p_sub_position_id;
g_dbtab_sub_level(g_stored_rows_to_insert) := p_sub_level;
g_dbtab_sub_rlt_lvl(g_stored_rows_to_insert) := p_sub_level - p_sup_level;
g_dbtab_start_date(g_stored_rows_to_insert) := g_fetch_start_dt(p_index);
g_dbtab_end_date(g_stored_rows_to_insert) := g_fetch_end_dt(p_index);
END insert_row;
/* Updates all positions in the position hierarchy version starting with the */
/* top position. */
/******************************************************************************/
PROCEDURE calculate_chains( p_index IN NUMBER ) IS
/* Cursor picks out all positions in the position structure */
/* This cursor MUST return rows in the default order */
CURSOR positions_csr IS
SELECT
hier.business_group_id business_group_id
,hier.subordinate_position_id position_id
,LEVEL+1 actual_level
FROM (SELECT
pse.business_group_id
,pse.subordinate_position_id
,pse.parent_position_id
FROM
per_pos_structure_elements pse
WHERE pse.pos_structure_version_id = g_fetch_psv_id(p_index)) hier
START WITH hier.parent_position_id = g_fetch_top_pos_id(p_index)
CONNECT BY PRIOR hier.subordinate_position_id = parent_position_id;
/* Insert chain */
insert_row
(p_business_group_id => g_crrnt_chain(1).business_group_id
,p_sup_position_id => g_crrnt_chain(1).position_id
,p_sup_level => 1
,p_sub_position_id => g_crrnt_chain(1).position_id
,p_sub_level => 1
,p_index => p_index);
/* Insert chain */
insert_row
(p_business_group_id => g_crrnt_chain(l_sup_lvl).business_group_id
,p_sup_position_id => g_crrnt_chain(l_sup_lvl).position_id
,p_sup_level => l_sup_lvl
,p_sub_position_id => g_crrnt_chain(l_pos_lvl).position_id
,p_sub_level => l_pos_lvl
,p_index => p_index);
SELECT DISTINCT
pse.parent_position_id
,psv.pos_structure_version_id
,psv.position_structure_id
,psv.version_number
,pst.primary_position_flag
,psv.date_from
,NVL(psv.date_to,g_end_of_time)
,psv.business_group_id
FROM
per_pos_structure_elements pse
,per_pos_structure_versions psv
,per_position_structures pst
WHERE psv.pos_structure_version_id = pse.pos_structure_version_id
AND pst.position_structure_id = psv.position_structure_id
AND pst.primary_position_flag = 'Y'
AND NOT EXISTS
(SELECT NULL
FROM per_pos_structure_elements pse2
WHERE pse2.pos_structure_version_id = pse.pos_structure_version_id
AND pse2.subordinate_position_id = pse.parent_position_id);
g_stored_rows_to_insert := 0;
bulk_insert_rows;
g_stored_rows_to_insert := 0;
/* Insert new position hierarchy records */
collect_pos_structures;