[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* i) Insert top organization chain into the organization hierarchy table */
/* ii) Insert chain for all organizations in that organization 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_org_structure_elements.business_group_id%TYPE
,organization_id per_org_structure_elements.organization_id_parent%TYPE
,last_chng_date DATE);
g_cs_rows_to_insert PLS_INTEGER; -- Number of CS rows 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 CS rows to insert when an exception occurs */
/******************************************************************************/
PROCEDURE recover_insert_cs_rows IS
BEGIN
-- loop through rows still to insert one at a time
FOR i IN 1..g_cs_rows_to_insert LOOP
-- Trap unique constraint errors
BEGIN
INSERT INTO hri_org_hrchy_summary
(organization_structure_id
,org_structure_version_id
,org_business_group_id
,organization_id
,organization_level
,sub_org_business_group_id
,sub_organization_id
,sub_organization_level
,sub_org_relative_level
,last_ptntl_change)
VALUES
(g_cs_ost_id(i)
,g_cs_osv_id(i)
,g_cs_bgr_id(i)
,g_cs_sup_org_id(i)
,g_cs_sup_level(i)
,g_cs_bgr_id(i)
,g_cs_sub_org_id(i)
,g_cs_sub_level(i)
,g_cs_sub_rlt_lvl(i)
,g_cs_last_chng(i));
output('Single insert error: ' || to_char(g_cs_sub_org_id(i)) ||
' - ' || to_char(g_cs_sup_org_id(i)));
output('Inserting chain for: ' ||
to_char(g_cs_sub_org_id(i)) || ' in hierarchy version' ||
to_char(g_cs_osv_id(i)));
END recover_insert_cs_rows;
/* Bulk inserts rows from global temporary table to CS database table */
/******************************************************************************/
PROCEDURE bulk_insert_cs_rows IS
BEGIN
-- insert chunk of rows
FORALL i IN 1..g_cs_rows_to_insert
INSERT INTO hri_org_hrchy_summary
(organization_structure_id
,org_structure_version_id
,org_business_group_id
,organization_id
,organization_level
,sub_org_business_group_id
,sub_organization_id
,sub_organization_level
,sub_org_relative_level
,last_ptntl_change)
VALUES
(g_cs_ost_id(i)
,g_cs_osv_id(i)
,g_cs_bgr_id(i)
,g_cs_sup_org_id(i)
,g_cs_sup_level(i)
,g_cs_bgr_id(i)
,g_cs_sub_org_id(i)
,g_cs_sub_level(i)
,g_cs_sub_rlt_lvl(i)
,g_cs_last_chng(i));
recover_insert_cs_rows;
END bulk_insert_cs_rows;
/* Inserts row into global CS temporary table */
/******************************************************************************/
PROCEDURE insert_cs_row( p_sup_organization_id IN NUMBER
, p_sup_level IN NUMBER
, p_sub_organization_id IN NUMBER
, p_sub_level IN NUMBER
, p_index IN NUMBER
, p_last_ptntl_change IN DATE ) IS
BEGIN
-- increment the index
g_cs_rows_to_insert := g_cs_rows_to_insert + 1;
g_cs_sup_org_id(g_cs_rows_to_insert) := p_sup_organization_id;
g_cs_sup_level(g_cs_rows_to_insert) := p_sup_level;
g_cs_sub_org_id(g_cs_rows_to_insert) := p_sub_organization_id;
g_cs_sub_level(g_cs_rows_to_insert) := p_sub_level;
g_cs_sub_rlt_lvl(g_cs_rows_to_insert) := p_sub_level - p_sup_level;
g_cs_ost_id(g_cs_rows_to_insert) := g_fetch_ost_id(p_index);
g_cs_bgr_id(g_cs_rows_to_insert) := g_fetch_bgr_id(p_index);
g_cs_osv_id(g_cs_rows_to_insert) := g_fetch_osv_id(p_index);
g_cs_last_chng(g_cs_rows_to_insert) := p_last_ptntl_change;
END insert_cs_row;
/* Updates all organizations in the organization hierarchy version starting */
/* with the top organization. */
/******************************************************************************/
PROCEDURE calculate_chains( p_index IN NUMBER ) IS
/* Cursor picks out all organizations in the organization structure */
/* This cursor MUST return rows in the default order */
CURSOR organizations_csr IS
SELECT
hier.organization_id_child organization_id
,hier.last_update_date last_update_date
,LEVEL+1 actual_level
FROM (SELECT
ose.organization_id_child
,ose.organization_id_parent
,NVL(ose.last_update_date, g_start_of_time) last_update_date
FROM
per_org_structure_elements ose
WHERE ose.org_structure_version_id = g_fetch_osv_id(p_index)) hier
START WITH hier.organization_id_parent = g_fetch_top_org_id(p_index)
CONNECT BY PRIOR hier.organization_id_child = organization_id_parent;
/* Insert chain */
insert_cs_row
(p_sup_organization_id => g_crrnt_chain(1).organization_id
,p_sup_level => 1
,p_sub_organization_id => g_crrnt_chain(1).organization_id
,p_sub_level => 1
,p_index => p_index
,p_last_ptntl_change => g_crrnt_chain(1).last_chng_date);
GREATEST(org_rec.last_update_date,
g_crrnt_chain(l_org_lvl - 1).last_chng_date);
/* Insert chain into CS */
insert_cs_row
(p_sup_organization_id => g_crrnt_chain(l_sup_lvl).organization_id
,p_sup_level => l_sup_lvl
,p_sub_organization_id => g_crrnt_chain(l_org_lvl).organization_id
,p_sub_level => l_org_lvl
,p_index => p_index
,p_last_ptntl_change => g_crrnt_chain(l_org_lvl).last_chng_date);
/* If the stored rows have reached a maximum, then insert them */
IF (g_cs_rows_to_insert > g_chunk_size) THEN
-- bulk insert rows processed so far
bulk_insert_cs_rows;
g_cs_rows_to_insert := 0;
SELECT /*+ USE_NL(ose ost) */ DISTINCT
ose.organization_id_parent
,osv.org_structure_version_id
,osv.organization_structure_id
,osv.version_number
,ost.primary_structure_flag
,osv.date_from
,NVL(osv.date_to,g_end_of_time)
,osv.business_group_id
FROM
per_org_structure_elements ose
,per_org_structure_versions osv
,per_organization_structures ost
WHERE osv.org_structure_version_id = ose.org_structure_version_id
AND ost.organization_structure_id = osv.organization_structure_id
-- AND ost.primary_structure_flag = 'Y'
AND NOT EXISTS
(SELECT NULL
FROM per_org_structure_elements ose2
WHERE ose2.org_structure_version_id = ose.org_structure_version_id
AND ose2.organization_id_child = ose.organization_id_parent);
g_cs_rows_to_insert := 0;
/* Insert any remaining stored rows */
IF (g_cs_rows_to_insert > 0) THEN
bulk_insert_cs_rows;
/* Insert new organization hierarchy records */
collect_org_structures;