[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* - Insert link record (store in PL/SQL globals for bulk insert) */
/* - If bulk insert limit reached on rows to insert, then do bulk insert */
/* */
/* 4) Bulk Insert any remaining rows at end of process */
/******************************************************************************/
/* 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_cs_orgh_ct
(orgh_orghrchy_fk
,orgh_global_flag
,orgh_orghvrsn_fk
,orgh_sup_organztn_fk
,orgh_sup_level
,orgh_sup_sub1_organztn_fk
,orgh_sup_sub2_organztn_fk
,orgh_sup_sub3_organztn_fk
,orgh_sup_sub4_organztn_fk
,orgh_organztn_fk
,orgh_level
,orgh_relative_level
,orgh_adt_org_struct_id
,orgh_adt_org_struct_version_id
,orgh_sub_node_has_workers_flag
,orgh_sub_org_has_workers_flag)
VALUES
(g_cs_ost_id(i)
,'Y'
,g_cs_osv_id(i)
,g_cs_sup_org_id(i)
,g_cs_sup_level(i)
,g_cs_sup_sub1_id(i)
,g_cs_sup_sub2_id(i)
,g_cs_sup_sub3_id(i)
,g_cs_sup_sub4_id(i)
,g_cs_sub_org_id(i)
,g_cs_sub_level(i)
,g_cs_sub_rlt_lvl(i)
,g_cs_ost_id(i)
,g_cs_osv_id(i)
,'N'
,'N');
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_cs_orgh_ct
(orgh_orghrchy_fk
,orgh_global_flag
,orgh_orghvrsn_fk
,orgh_sup_organztn_fk
,orgh_sup_level
,orgh_sup_sub1_organztn_fk
,orgh_sup_sub2_organztn_fk
,orgh_sup_sub3_organztn_fk
,orgh_sup_sub4_organztn_fk
,orgh_organztn_fk
,orgh_level
,orgh_relative_level
,orgh_adt_org_struct_id
,orgh_adt_org_struct_version_id
,orgh_sub_node_has_workers_flag
,orgh_sub_org_has_workers_flag
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
VALUES
(g_cs_ost_id(i)
,'Y'
,g_cs_osv_id(i)
,g_cs_sup_org_id(i)
,g_cs_sup_level(i)
,g_cs_sup_sub1_id(i)
,g_cs_sup_sub2_id(i)
,g_cs_sup_sub3_id(i)
,g_cs_sup_sub4_id(i)
,g_cs_sub_org_id(i)
,g_cs_sub_level(i)
,g_cs_sub_rlt_lvl(i)
,g_cs_ost_id(i)
,g_cs_osv_id(i)
,'N'
,'N'
,g_sysdate
,g_user_id
,g_user_id
,g_user_id
,g_sysdate);
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_sup_sub1_id IN NUMBER
, p_sup_sub2_id IN NUMBER
, p_sup_sub3_id IN NUMBER
, p_sup_sub4_id IN NUMBER
, p_sub_organization_id IN NUMBER
, p_sub_level IN NUMBER
, p_ost_id IN NUMBER
, p_osv_id IN NUMBER
, p_bgr_id IN NUMBER
, p_sub_org_parent_id 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_sup_sub1_id(g_cs_rows_to_insert) := p_sup_sub1_id;
g_cs_sup_sub2_id(g_cs_rows_to_insert) := p_sup_sub2_id;
g_cs_sup_sub3_id(g_cs_rows_to_insert) := p_sup_sub3_id;
g_cs_sup_sub4_id(g_cs_rows_to_insert) := p_sup_sub4_id;
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_sub_org_prnt_id(g_cs_rows_to_insert) := p_sub_org_parent_id;
g_cs_ost_id(g_cs_rows_to_insert) := p_ost_id;
g_cs_bgr_id(g_cs_rows_to_insert) := p_bgr_id;
g_cs_osv_id(g_cs_rows_to_insert) := p_osv_id;
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_top_org_id IN NUMBER
,p_ost_id IN NUMBER
,p_bgr_id IN NUMBER
,p_osv_id IN NUMBER
,p_start_date IN DATE) 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 = p_osv_id) hier
START WITH hier.organization_id_parent = p_top_org_id
CONNECT BY PRIOR hier.organization_id_child = organization_id_parent;
/* Insert chain */
insert_cs_row
(p_sup_organization_id => l_crrnt_chain(1).organization_id
,p_sup_level => 1
,p_sup_sub1_id => l_crrnt_chain(1).organization_id
,p_sup_sub2_id => l_crrnt_chain(1).organization_id
,p_sup_sub3_id => l_crrnt_chain(1).organization_id
,p_sup_sub4_id => l_crrnt_chain(1).organization_id
,p_sub_organization_id => l_crrnt_chain(1).organization_id
,p_sub_level => 1
,p_sub_org_parent_id => -1
,p_ost_id => p_ost_id
,p_bgr_id => p_bgr_id
,p_osv_id => p_osv_id
,p_last_ptntl_change => l_crrnt_chain(1).last_chng_date);
GREATEST(org_rec.last_update_date,
l_crrnt_chain(l_org_lvl - 1).last_chng_date);
/* Insert chain into CS */
insert_cs_row
(p_sup_organization_id => l_crrnt_chain(l_sup_lvl).organization_id
,p_sup_level => l_sup_lvl
,p_sup_sub1_id => l_sup_sub1_id
,p_sup_sub2_id => l_sup_sub2_id
,p_sup_sub3_id => l_sup_sub3_id
,p_sup_sub4_id => l_sup_sub4_id
,p_sub_organization_id => l_crrnt_chain(l_org_lvl).organization_id
,p_sub_level => l_org_lvl
,p_sub_org_parent_id => l_crrnt_chain(l_org_lvl - 1).organization_id
,p_ost_id => p_ost_id
,p_bgr_id => p_bgr_id
,p_osv_id => p_osv_id
,p_last_ptntl_change => l_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
osv.org_structure_version_id
FROM
per_org_structure_versions osv
,per_organization_structures ost
WHERE ost.organization_structure_id = osv.organization_structure_id
-- Primary Global
AND ((ost.primary_structure_flag = 'Y' AND osv.business_group_id IS NULL)
-- or, Profile
OR ost.organization_structure_id = v_structure_id)
AND trunc(sysdate) BETWEEN osv.date_from
AND NVL(osv.date_to, SYSDATE)
-- If returned, order structure from profile option first to override
-- default selection of primary global
ORDER BY DECODE(ost.organization_structure_id, v_structure_id, 1, 2);
/* Pick out top organization from the selected version */
CURSOR hrchy_version_csr(v_version_id NUMBER) IS
SELECT DISTINCT
ose.organization_id_parent top_org_id
,osv.org_structure_version_id osv_id
,osv.organization_structure_id ost_id
,osv.version_number osv_no
,ost.primary_structure_flag primary_flag
,osv.date_from start_date
,NVL(osv.date_to,g_end_of_time) end_date
,osv.business_group_id bgr_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 osv.org_structure_version_id = v_version_id
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;
IF (g_cs_rows_to_insert > 0) THEN
bulk_insert_cs_rows;
/* Updates flag orgh_sub_org_has_workers_flag,orgh_node_has_workers_flag */
/******************************************************************************/
PROCEDURE upd_org_has_worker_flags_full IS
--
BEGIN
--
-- Time at flag update start
--
output('Flag update Start: ' || to_char(sysdate,'HH24:MI:SS'));
UPDATE /*+ PARALLEL */ hri_cs_orgh_ct orgh
SET orgh.orgh_sub_org_has_workers_flag = 'Y'
WHERE orgh.rowid IN (SELECT sub_org.rowid
FROM hri_cs_orgh_ct sub_org,
hri_cs_organztn_ct org_wrkrs
WHERE sub_org.orgh_organztn_fk = org_wrkrs.org_organztn_pk
AND org_wrkrs.org_has_workers_flag = 'Y'
);
output('orgh_sub_org_has_workers_flag updated: ' ||
to_char(sysdate,'HH24:MI:SS'));
UPDATE /*+ PARALLEL(orgh) */ hri_cs_orgh_ct orgh
SET orgh.orgh_sub_node_has_workers_flag = 'Y'
WHERE orgh.rowid IN (
SELECT sup_org.rowid
FROM hri_cs_orgh_ct sup_org
WHERE EXISTS (SELECT null
FROM hri_cs_orgh_ct sub_org
WHERE sup_org.orgh_organztn_fk = sub_org.orgh_sup_organztn_fk
AND sub_org.orgh_sub_org_has_workers_flag = 'Y'));
output('orgh_node_has_workers_flag updated: ' ||
to_char(sysdate,'HH24:MI:SS'));
/* Updates flag orgh_sub_org_has_workers_flag,orgh_node_has_workers_flag */
/******************************************************************************/
--
PROCEDURE upd_org_has_worker_flags_incr
IS
BEGIN
--
output('Incremental flag update start: ' || to_char(sysdate,'HH24:MI:SS'));
UPDATE hri_cs_orgh_ct orgh
SET orgh.orgh_sub_org_has_workers_flag = 'Y'
WHERE orgh.rowid IN (SELECT sub_org.rowid
FROM hri_cs_orgh_ct sub_org
WHERE sub_org.orgh_organztn_fk = g_new_orgs_with_worker(i)
);
output('orgh_sub_org_has_workers_flag updated incrementally: ' ||
to_char(sysdate,'HH24:MI:SS'));
UPDATE hri_cs_orgh_ct orgh
SET orgh.orgh_sub_node_has_workers_flag = 'Y'
WHERE orgh.rowid IN (
SELECT sup_org.rowid
FROM hri_cs_orgh_ct sup_org
WHERE EXISTS (SELECT null
FROM hri_cs_orgh_ct sub_org
WHERE sup_org.orgh_organztn_fk = sub_org.orgh_sup_organztn_fk
AND sub_org.orgh_organztn_fk = g_new_orgs_with_worker(i)
AND sub_org.orgh_sub_org_has_workers_flag = 'Y')
AND sup_org.orgh_sub_node_has_workers_flag = 'N');
output('orgh_node_has_workers_flag updated incrementally: ' ||
to_char(sysdate,'HH24:MI:SS'));
output('Exiting worker flag update process: ' ||
to_char(sysdate,'HH24:MI:SS'));
/* Insert new organization hierarchy records */
collect_org_structures;
/* Update flags to determine if orgs/nodes have workers */
upd_org_has_worker_flags_full;
/* Insert new org with worker records */
INSERT /*+ APPEND */ INTO HRI_CS_ORGANZTN_CT
(org_organztn_pk,
org_has_workers_flag,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
SELECT DISTINCT
organization_id org_organztn_pk
,'Y' org_has_workers_flag
,g_sysdate last_update_date
,g_user_id last_updated_by
,g_user_id last_update_login
,g_user_id created_by
,g_sysdate creation_date
FROM per_all_assignments_f;
SELECT DISTINCT asg.organization_id
FROM per_all_assignments_f asg
WHERE not exists (SELECT null
FROM hri_cs_organztn_ct org
WHERE org.org_organztn_pk = asg.organization_id);
INSERT INTO HRI_CS_ORGANZTN_CT
(org_organztn_pk
,org_has_workers_flag
,last_update_date
,last_updated_by
,last_update_login
,created_by
,creation_date)
VALUES
(g_new_orgs_with_worker(i)
,'Y'
,g_sysdate
,g_user_id
,g_user_id
,g_user_id
,g_sysdate
);
output('Inserted records incrementally: ' ||
to_char(sysdate,'HH24:MI:SS'));
/* Incremental support for update of worker flags when a worker is */
/* assigned a organization not existing in table HRI_CS_ORGANZTN_CT */
upd_org_has_worker_flags_incr;
/* not support changes/updates to existing relationships */
--
END IF;