Organization Hierarchy Creation.

CREATE OR REPLACE PACKAGE  XX_HR_ORG_HIERARCHY_PKG 
/******************************************************************************
*     PACKAGE NAME :XX_HR_ORG_HIERARCHY_PKG                                    *
*     PURPOSE      : Create org hierachy for organization                     *
*                                                                             *
*                                                                             *
*                                                                             *
*******************************************************************************/
--
--

PROCEDURE XX_HR_CREATE_HIERARCHY_ELEMENT 
                                          ( l_validate                  IN BOOLEAN
                                           ,l_organization_id_parent    IN NUMBER
                                           ,l_org_structure_version_id  IN NUMBER
                                           ,l_organization_id_child     IN NUMBER
                                           ,l_business_group_id         IN NUMBER
                                           ,l_effective_date            IN DATE
                                           ,l_pos_control_enabled_flag  IN VARCHAR2
                                           ,x_inactive_org_warning      IN OUT BOOLEAN
                                           ,x_org_structure_element_id  IN OUT NUMBER
                                          ,x_object_version_number     IN OUT NUMBER);
 PROCEDURE MAIN(  p_errorbuf OUT VARCHAR2
                 ,p_retcode  OUT NUMBER
                 ,p_mode     IN VARCHAR2) ;

END XX_HR_ORG_HIERARCHY_PKG;
/
                                           
CREATE OR REPLACE PACKAGE BODY XX_HR_ORG_HIERARCHY_PKG 
IS
/******************************************************************************
*     PACKAGE NAME :XX_HR_ORG_HIERARCHY_PKG                                    *
*     PURPOSE      : Create org hierachy for organization                     *
*                                                                             *
*                                                                             *
*                                                                             *
*******************************************************************************/
--
--
gc_pkg_name  VARCHAR2(40) := 'XX_HR_ORG_HIERARCHY_PKG';
gc_module    VARCHAR2(20) := 'Oracle HRMS';
g_business_group_id      NUMBER(1)     := apps.fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
--
--
PROCEDURE XX_HR_CREATE_HIERARCHY_ELEMENT 
                                          ( l_validate                  IN BOOLEAN
                                           ,l_organization_id_parent    IN NUMBER
                                           ,l_org_structure_version_id  IN NUMBER
                                           ,l_organization_id_child     IN NUMBER
                                           ,l_business_group_id         IN NUMBER
                                           ,l_effective_date            IN DATE
                                           ,l_pos_control_enabled_flag  IN VARCHAR2
                                           ,x_inactive_org_warning      IN OUT BOOLEAN
                                           ,x_org_structure_element_id  IN OUT NUMBER
                                          ,x_object_version_number     IN OUT NUMBER
                                        ) IS
--
--
lcroutine CONSTANT VARCHAR2 (400)
                           := gc_pkg_name || '.XX_HR_CREATE_HIERARCHY_ELEMENT' ;



BEGIN
 --       
 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_organization_id_parent -'||l_organization_id_parent||',l_org_structure_version_id -'||l_org_structure_version_id||',l_organization_id_child -'||l_organization_id_child||',l_business_group_id'||l_business_group_id||',l_effective_date -'||l_effective_date||',l_pos_control_enabled_flag-'||l_pos_control_enabled_flag);
 --      
 hr_hierarchy_element_api.create_hierarchy_element
                           (
                              p_validate                 => l_validate
                             ,p_organization_id_parent   => l_organization_id_parent
                             ,p_org_structure_version_id => l_org_structure_version_id
                             ,p_organization_id_child    => l_organization_id_child
                             ,p_business_group_id        => l_business_group_id
                             ,p_effective_date           => l_effective_date
                             ,p_pos_control_enabled_flag => l_pos_control_enabled_flag
                             ,p_inactive_org_warning     => x_inactive_org_warning
                             ,p_org_structure_element_id => x_org_structure_element_id
                             ,p_object_version_number    => x_object_version_number
                           );
--
--Debugging Message
      xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'Org Hierarchy Created Successfully '
                                              ||x_org_structure_element_id||' is Org Structure Element Id'
                            ,p_module => gc_module);
 --
 --
 EXCEPTION
   WHEN OTHERS THEN
 --
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error-'||SQLERRM);
--Debugging Message
      xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => SQLERRM
                            ,p_module => gc_module);
--
END XX_HR_CREATE_HIERARCHY_ELEMENT;
--
--
PROCEDURE XX_HR_DELETE_HIERARCHY_ELEMENT (l_validate                     IN BOOLEAN
                                         ,l_org_structure_element_id     IN NUMBER
                                         ,l_object_version_number        IN NUMBER
  ) IS
--
--
  lcroutine   CONSTANT VARCHAR2 (100)
                           := gc_pkg_name || '.XX_HR_DELETE_HIERARCHY_ELEMENT' ;
  BEGIN
  --
  --
    hr_hierarchy_element_api.delete_hierarchy_element
                                          (p_validate                 => l_validate
                                          ,p_org_structure_element_id => l_org_structure_element_id
                                          ,p_object_version_number    => l_object_version_number);
         xx_fnd_utils_pkg.LOG (
                               p_debug_level  => xx_fnd_utils_pkg.procedure_level
                              ,p_routine_name => lcroutine
                              ,p_debug_msg    => 'deleted the organization hierarchy element : '||l_org_structure_element_id
                              ,p_module       => gc_module);
 END XX_HR_DELETE_HIERARCHY_ELEMENT;
--
--
PROCEDURE XX_UPDATE_HIERARCHY_ELEMENT
                                  (l_validate                      IN     BOOLEAN 
                                  ,l_effective_date                IN     DATE
                                  ,l_org_structure_element_id      IN     NUMBER
                                  ,l_organization_id_parent        IN     NUMBER
                                  ,l_organization_id_child         IN     NUMBER
                                  ,l_pos_control_enabled_flag      IN     VARCHAR2
                                  ,l_object_version_number         IN OUT NOCOPY NUMBER
  ) IS
x_object_version_number          NUMBER;
lcroutine  CONSTANT VARCHAR2 (100)
                           := gc_pkg_name || '.XX_UPDATE_HIERARCHY_ELEMENT' ;
 --
 --
  BEGIN
     hr_hierarchy_element_api.update_hierarchy_element
                                    (p_validate                     => l_validate
                                    ,p_effective_date               => l_effective_date
                                    ,p_org_structure_element_id     => l_org_structure_element_id
                                    ,p_organization_id_parent       => l_organization_id_parent
                                    ,p_organization_id_child        => l_organization_id_child
                                    ,p_pos_control_enabled_flag     => l_pos_control_enabled_flag
                                    ,p_object_version_number        => l_object_version_number
                                   );
                                  
  EXCEPTION
     WHEN OTHERS THEN
       xx_fnd_utils_pkg.LOG (
         p_debug_level  => xx_fnd_utils_pkg.procedure_level
        ,p_routine_name => lcroutine
        ,p_debug_msg    => 'Unable to update the organization hierarchy element : '||SQLERRM
        ,p_module       => gc_module);

  END XX_UPDATE_HIERARCHY_ELEMENT;
--
--
FUNCTION XX_GET_ORG_ID( p_organization_name IN VARCHAR2) RETURN NUMBER IS
--
--
l_org_id     NUMBER;
lcroutine CONSTANT VARCHAR2 (100)
                           := gc_pkg_name || '.XX_GET_ORG_ID' ;
--
BEGIN
BEGIN
    SELECT organization_id
    INTO l_org_id
    FROM hr_all_organization_units 
    WHERE NAME= p_organization_name;

EXCEPTION
  WHEN OTHERS THEN
      l_org_id:=0;
END;
xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'Organization Id : '||l_org_id
                            ,p_module => gc_module);

RETURN l_org_id;
--
--
END XX_GET_ORG_ID;
--
--
FUNCTION IS_PARENT_EXISTS (p_organization_id         IN   NUMBER
                         ,p_check_org                IN   NUMBER
                         ,p_org_strct_version_id     IN   NUMBER
                         )
RETURN VARCHAR2
  IS
         l_flag   VARCHAR2 (2);
    --
    --
   BEGIN
    --
    --
      l_flag := 'Y';
      --
      --
      BEGIN
          SELECT DECODE (COUNT (organization_id_parent), 0, 'N', 'Y') INTO l_flag
          FROM
               (SELECT LEVEL, pose.organization_id_parent, pose.organization_id_child
                FROM per_org_structure_elements pose
                WHERE org_structure_version_id= p_org_strct_version_id
                START WITH organization_id_child =  p_organization_id
                CONNECT BY PRIOR pose.organization_id_parent = pose.organization_id_child
                 -- ORDER BY 1 commented by mahesh
                 )
           WHERE organization_id_parent = p_check_org;
       --
       --
      EXCEPTION
          WHEN OTHERS THEN
             RETURN 'N';
      END;
      --
      --
    
   RETURN l_flag;

 END IS_PARENT_EXISTS;
--
--
FUNCTION IS_CHILD_EXISTS(p_pernt_org_id IN NUMBER
                        ,p_child_org_id IN NUMBER,p_org_strct_version_id IN NUMBER
                        )
RETURN VARCHAR2 IS
--
--
l_flag  VARCHAR2(3);
--
--
BEGIN
--
--
  BEGIN
  --
  --
    SELECT
        DECODE(COUNT(*),0,'N','Y') INTO l_flag  FROM
        (  SELECT
        level, pose.organization_id_parent , pose.organization_id_child
   FROM per_org_structure_elements pose 
   where org_structure_version_id= p_org_strct_version_id
    START WITH organization_id_parent=p_pernt_org_id
   CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent 
   -- ORDER BY 1 commented by mahesh
   ) where organization_id_child = p_child_org_id ;
 EXCEPTION
    WHEN OTHERS THEN
      RETURN 'N';
 END;
--
--
RETURN l_flag;
--
--
END IS_CHILD_EXISTS;
--
--
FUNCTION GET_STRUCTURE_ELEMENT_ID(p_parent_org_id IN NUMBER ,p_organization_id IN NUMBER,p_org_structure_version_id IN NUMBER
 ,p_version_num IN NUMBER)
RETURN NUMBER IS
--
--
l_org_struc_element_id    NUMBER;
--
--
 BEGIN
   --
   --
   BEGIN
    --
     SELECT org_structure_element_id INTO l_org_struc_element_id FROM per_org_structure_elements pose
     ,PER_ORG_STRUCTURE_VERSIONS posv
      WHERE organization_id_child = p_organization_id
      AND   organization_id_parent = p_parent_org_id
      AND pose.org_structure_version_id=posv.org_structure_version_id
      AND VERSION_NUMBER=p_version_num
      AND pose.org_structure_version_id= p_org_structure_version_id;
   EXCEPTION
      WHEN OTHERS THEN
        l_org_struc_element_id :=0;
  END;
 --
 --
 RETURN l_org_struc_element_id;
--
END GET_STRUCTURE_ELEMENT_ID;
--
--
FUNCTION IS_ORG_EXIST(p_org_id IN NUMBER,p_org_struct_version_id IN NUMBER)  RETURN VARCHAR2  IS
  l_flag           VARCHAR2(3);
  --
  --
     BEGIN
       l_flag :='N';
         BEGIN
           SELECT decode(count(*),0,'N','Y') INTO l_flag  FROM per_org_structure_elements
            WHERE organization_id_parent = p_org_id
            AND  org_structure_version_id=p_org_struct_version_id;
          EXCEPTION
            WHEN OTHERS THEN
              l_flag:='N';
        END;
     --
     --
      Return l_flag;
END;
--
--
FUNCTION IS_USER_ACTIVE (p_org_id IN NUMBER) RETURN VARCHAR2 IS
l_flag        VARCHAR2(20);
  BEGIN
     l_flag:='N';
       BEGIN
        SELECT DECODE(COUNT(*),0,'N','Y') INTO l_flag FROM per_all_assignments_f WHERE organization_id=p_org_id
        AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
       EXCEPTION
          WHEN OTHERS THEN
             l_flag :='N';
      END;
    --
    --
    RETURN l_flag;
END;
--
--
PROCEDURE MAIN(  p_errorbuf OUT VARCHAR2
                ,p_retcode  OUT NUMBER
                ,p_mode     IN VARCHAR2) IS

l_parent_org_id   NUMBER;
l_child_org_id   NUMBER;
lcroutine CONSTANT VARCHAR2 (100)
                           := gc_pkg_name || '.MAIN' ;
l_org_strct_version_id       NUMBER;
l_x_inactive_org_warn          BOOLEAN;
l_x_org_structure_id           NUMBER;
x_object_version_number         NUMBER;
l_x_object_version_num         NUMBER;
l_mode                       BOOLEAN;
l_parent_exist               VARCHAR2(3);
l_child_exist                VARCHAR2(3);
l_child_org_count            NUMBER;
l_process_flag               VARCHAR2(3);
l_org_struct_element_id      NUMBER;
l_error_msg                  VARCHAR2(4000);
l_trans_date                 DATE;
l_count                            NUMBER;
l_org_exist                  VARCHAR2(3);
l_user_active                VARCHAR2(3);
l_total_count                NUMBER;
l_process_count              NUMBER;
l_error_count                NUMBER;
--
--
CURSOR cur_org_struct IS
SELECT *
FROM XX_HR_ORG_HIER_STRUCT_STG
WHERE STATUS IN ('NEW','VALIDATED')
ORDER BY sequence_number ASC;

BEGIN
--
--
   IF UPPER(p_mode) ='VERIFY' THEN
      l_mode:=TRUE;
   ELSE
      l_mode:=FALSE;
  END IF;
  --
  FOR rec_org_struct in cur_org_struct LOOP
  --
  --
     l_parent_org_id:= NULL;
     l_child_org_id:= NULL;
     l_org_strct_version_id:=0;
     l_parent_exist:='Y';
     l_process_flag:='Y';
     l_org_struct_element_id:=0;
     l_error_msg    :=NULL;
     x_object_version_number :=0;
     l_child_org_count  :=0;
     l_trans_date   := NULL;
     l_count   :=0;
     l_org_exist   :='N';
     l_user_active  :='N';
     --
     --
    
       IF rec_org_struct.newmode NOT IN ('CREATE','DELETE') THEN
       
         --  l_process_flag :='E';
           l_error_msg := ' Invalid Mode.';
          
           UPDATE XX_HR_ORG_HIER_STRUCT_STG
           SET STATUS ='ERROR'
              ,ERROR_MESSAGE=l_error_msg
           WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
          
        
        END IF;
        --
        --
        IF rec_org_struct.Hierarchy_Name IS NULL THEN
         --
         --
          l_error_msg:=l_error_msg||' - Invalid Hierarchy_Name';
          --      
           UPDATE XX_HR_ORG_HIER_STRUCT_STG
           SET STATUS ='ERROR'
               ,ERROR_MESSAGE=l_error_msg
           WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
           --
           --
          ELSE
           BEGIN
                SELECT   posv.org_structure_version_id,decode(date_to,null,trunc(sysdate),date_to-1)
                INTO l_org_strct_version_id, l_trans_date
                FROM    per_organization_structures pos,
                          per_org_structure_versions posv
                WHERE pos.name = rec_org_struct.Hierarchy_Name
                AND pos.business_group_id = g_business_group_id
                AND VERSION_NUMBER=rec_org_struct.version_no
                AND pos.organization_structure_id = posv.organization_structure_id;
                    -- AND SYSDATE BETWEEN date_from AND nvl(date_to,SYSDATE) ;
           EXCEPTION
               WHEN OTHERS THEN
                   l_org_strct_version_id:=0;
           END;
           --
           --
        END IF;
        --
          --
          --
          /*  xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'l_org_strct_version_id: '||l_org_strct_version_id
                            ,p_module => gc_module);*/--- Removed by mahesh
        --
        IF l_org_strct_version_id = 0 or l_org_strct_version_id is NULL THEN
         l_error_msg:=l_error_msg||' - Invalid Hierarchy_Name Or Invalid Version Number';
           --
           UPDATE XX_HR_ORG_HIER_STRUCT_STG
           SET STATUS ='ERROR'
             ,ERROR_MESSAGE=l_error_msg
           WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
           --
      END IF;
        --
        --
         l_parent_org_id:=XX_GET_ORG_ID(rec_org_struct.parent_organization_name);
         l_child_org_id:=XX_GET_ORG_ID(rec_org_struct.child_organization_name);
         --
         --
         --
         IF  l_parent_org_id IS NULL
           OR l_child_org_id IS NULL OR l_child_org_id=0 OR l_parent_org_id=0 THEN
              l_process_flag:='E';
            l_error_msg:=l_error_msg||' - Parent Or Child ORganization Doesnot exist.'; 
            UPDATE XX_HR_ORG_HIER_STRUCT_STG
            SET STATUS ='ERROR'
               ,ERROR_MESSAGE=l_error_msg
            WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
          END IF;
          --
          --
           IF l_parent_org_id = l_child_org_id   THEN
            l_process_flag:='E';
            l_error_msg:=l_error_msg||' - Invalid Realation Ship between Parent and Child';
             UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
             ,ERROR_MESSAGE=l_error_msg
             WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
           
          END IF;
         --
         --
         /* xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'rec_org_struct.newmode: '||rec_org_struct.newmode
                            ,p_module => gc_module);*/
                           
        l_org_struct_element_id:=GET_STRUCTURE_ELEMENT_ID(l_parent_org_id,l_child_org_id,l_org_strct_version_id,rec_org_struct.version_no);
      --                          
      IF rec_org_struct.newmode='CREATE' THEN
          IF l_parent_org_id <> 0
            AND l_child_org_id <>0 THEN
           
           l_parent_exist:=IS_PARENT_EXISTS(l_parent_org_id,l_child_org_id,l_org_strct_version_id);
           l_child_exist:=IS_CHILD_EXISTS(l_parent_org_id,l_child_org_id,l_org_strct_version_id);
          
           l_org_exist := IS_ORG_EXIST (l_parent_org_id,l_org_strct_version_id);
          --
          --
          /* xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'l_parent_exist: '||l_parent_exist
                            ,p_module => gc_module);*/
          --
          --
          /*  xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'l_child_exist: '||l_child_exist
                            ,p_module => gc_module);
            --
          --
            xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'l_process_flag: '||l_process_flag
                            ,p_module => gc_module);*/
          --
          select count(*) into l_count
          from PER_ORG_STRUCTURE_ELEMENTS
          where ORG_STRUCTURE_VERSION_ID=l_org_strct_version_id
          and ORGANIZATION_ID_PARENT = l_parent_org_id
          and ORGANIZATION_ID_CHILD =l_child_org_id;
          --
            IF l_count = 0 THEN
                  l_parent_exist :='N';
                  l_child_exist  :='N';
            END IF;
          --
           IF l_parent_exist ='Y'OR l_child_exist= 'Y' OR l_process_flag ='E'  THEN
            
            l_process_flag:='E';
            l_error_msg :=l_error_msg||' -  The organizations are Having exist parent child org relation ship';
          END IF;
      
      END IF;
             --
             --
          IF l_process_flag='Y' AND l_org_strct_version_id <>0
             and l_child_org_id <> 0 and l_parent_org_id <>0
             AND l_org_exist = 'Y' THEN
            --
              -- 
                --
                BEGIN
                 --
                /*   xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'l_parent_org_id : '||l_parent_org_id
                            ,p_module => gc_module);
          --
          --
            xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'l_child_org_id : '||l_child_org_id
                            ,p_module => gc_module);*/
                       --
                 
                         l_x_inactive_org_warn  := NULL;
                         l_x_org_structure_id   := NULL;
                         l_x_object_version_num := NULL;
                      --
                      --
         /*   BEGIN
                select decode(date_to,null,trunc(sysdate),date_to-1) INTO l_trans_date
                    from per_org_structure_versions where org_structure_version_id= l_org_strct_version_id;
                EXCEPTION WHEN OTHERS THEN
                 l_trans_date :=trunc(sysdate);
            END;*/
          
             XX_HR_CREATE_HIERARCHY_ELEMENT 
                                          ( l_validate                  => l_mode
                                           ,l_organization_id_parent    => l_parent_org_id
                                           ,l_org_structure_version_id  => l_org_strct_version_id
                                           ,l_organization_id_child     => l_child_org_id
                                           ,l_business_group_id         => g_business_group_id
                                           ,l_effective_date            => l_trans_date
                                           ,l_pos_control_enabled_flag  => 'N'
                                           ,x_inactive_org_warning      => l_x_inactive_org_warn
                                           ,x_org_structure_element_id  => l_x_org_structure_id
                                           ,x_object_version_number     => x_object_version_number
                                        );                                              
                --
                -- dbms_output.put_line(l_x_org_structure_id);
                   xx_fnd_utils_pkg.LOG (
                                       p_debug_level => xx_fnd_utils_pkg.procedure_level
                                       ,p_routine_name => lcroutine
                                       ,p_debug_msg => 'Hierarchy Created Successfully Org Structure Id : '||l_x_org_structure_id
                                       ,p_module => gc_module);
                  --
                  --
            
               IF (l_x_org_structure_id IS NOT NULL AND   l_mode = FALSE) AND l_process_flag='Y' THEN
                      --
                       UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='PROCESSED'
                        WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
                        --
               ELSIF l_x_org_structure_id IS  NULL AND   l_mode=FALSE THEN
                      
                      UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
                      ,ERROR_MESSAGE = 'Unable to create the Org Hierarchy'
                      WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
                ELSIF l_process_flag='E' and l_mode=TRUE THEN
                     UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
                     ,ERROR_MESSAGE='Invalid Organization'
                      WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
                ELSIF l_process_flag='Y' and l_mode = TRUE THEN
                     UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='VALIDATED'
                    --  ,ERROR_MESSAGE = x_inactive_org_warn
                      WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER
                      AND STATUS='NEW';
                END IF;
                 
              EXCEPTION WHEN OTHERS THEN
                xx_fnd_utils_pkg.LOG (
                                       p_debug_level => xx_fnd_utils_pkg.procedure_level
                                       ,p_routine_name => lcroutine
                                       ,p_debug_msg => 'Error while creating the org hierarchy : '
                                       ,p_module => gc_module);
                   
               /* l_error_msg :=SQLERRM;
                 UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
                      ,ERROR_MESSAGE = l_error_msg
                      WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER
                      AND STATUS='NEW';
                */
               
        END;
        
         --
         --
        ELSIF l_org_exist = 'N'  THEN
          UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
                      ,ERROR_MESSAGE=substr(l_error_msg||' - Parent Org does not exist in the Hierarchy',1,400)
                 WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER
                 AND STATUS='NEW';
        ELSE
          UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
                      ,ERROR_MESSAGE=substr(l_error_msg||' - Invalid Hierarchy_Name',1,400)
                 WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER
                 AND STATUS='NEW';
        END IF;
         ELSIF rec_org_struct.newmode='DELETE' THEN
           --
           --
           BEGIN
             --
             SELECT COUNT(*) INTO l_child_org_count
             FROM per_org_structure_elements
             WHERE organization_id_parent = l_child_org_id
             AND org_structure_element_id=l_org_struct_element_id;
          EXCEPTION WHEN OTHERS THEN
             l_child_org_count :=0;
            END;
           
            --
            --
              IF l_child_org_count > 0 THEN
             
             UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
                      ,ERROR_MESSAGE='Unable to delete the Child Organization, It is parent of other Organization.'
                 WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
          ELSE
            dbms_output.put_line(l_org_struct_element_id);
            l_user_active    :=IS_USER_ACTIVE(l_child_org_id);
             --
             IF l_org_struct_element_id IS NOT NULL AND l_user_active='N' THEN
              BEGIN
             -- l_x_object_version_num    :=0;
               xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => 'Deleting Org Structure element : '||l_org_struct_element_id
                            ,p_module => gc_module);
                           
                  XX_HR_DELETE_HIERARCHY_ELEMENT (l_validate                   => l_mode
                                                 ,l_org_structure_element_id   => l_org_struct_element_id
                                                 ,l_object_version_number      => l_x_object_version_num );
                    --
                    if l_mode = FALSE THEN
                   
                    UPDATE XX_HR_ORG_HIER_STRUCT_STG
                    SET STATUS ='PROCESSED'
                    WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
                    --
                    ELSE
                     --
                     UPDATE XX_HR_ORG_HIER_STRUCT_STG
             SET STATUS ='VALIDATED'
                     WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
                     --
                    END IF;
                    --
                 EXCEPTION WHEN OTHERS THEN
                xx_fnd_utils_pkg.LOG (
                                       p_debug_level => xx_fnd_utils_pkg.procedure_level
                                       ,p_routine_name => lcroutine
                                       ,p_debug_msg => 'Error while deleting the org hierarchy : '||SQLERRM
                                       ,p_module => gc_module);
                   UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'
                      ,ERROR_MESSAGE='Unable to Delete the Organization'
                 WHERE rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER
                 AND STATUS='NEW';
            END;
          ELSE  
             UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR',ERROR_MESSAGE='Unable To delete the Organization Invalid Version Number Or Organization have Active Employees'
                        WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
          END IF;
      END IF;
            --
            --
      /*    ELSIF rec_org_struct.newmode='UPDATE' THEN
            IF l_org_struct_element_id IS NOT NULL THEN
      
          BEGIN
         
             XX_UPDATE_HIERARCHY_ELEMENT
                                  (l_validate                      => l_mode
                                  ,l_effective_date                => trunc(sysdate)
                                  ,l_org_structure_element_id      => l_org_struct_element_id
                                  ,l_organization_id_parent        => l_parent_org_id
                                  ,l_organization_id_child         => l_child_org_id
                                  ,l_pos_control_enabled_flag      => 'N'
                                  ,l_object_version_number         => rec_org_struct.version_no
                               
                                );
                 UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='PROCESSED'
                    WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
              EXCEPTION WHEN OTHERS THEN
                  UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR'   --,ERROR_MESSAGE=SQLERRM
                        WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
          END;
          --
          --
          ELSE
           UPDATE XX_HR_ORG_HIER_STRUCT_STG SET STATUS ='ERROR',ERROR_MESSAGE='Invalid org structure element id'
                        WHERE  rec_org_struct.SEQUENCE_NUMBER=SEQUENCE_NUMBER;
          --
          --
      END IF;
    --
    --*/
    COMMIT;
    END IF;
   --
   END LOOP;
     
      UPDATE XX_HR_ORG_HIER_STRUCT_STG
      SET STATUS ='ERROR',ERROR_MESSAGE='Invalid Mode'
      WHERE NEWMODE IS NULL;
      --     
        COMMIT;
       
        SELECT count(*) into l_total_count FROM XX_HR_ORG_HIER_STRUCT_STG;
        --
        SELECT count(*) into l_error_count FROM XX_HR_ORG_HIER_STRUCT_STG WHERE STATUS ='ERROR' ;
        --
        IF l_mode = FALSE THEN
        SELECT count(*) into l_process_count FROM XX_HR_ORG_HIER_STRUCT_STG WHERE STATUS ='PROCESSED' ;
        ELSE
        SELECT count(*) into l_process_count FROM XX_HR_ORG_HIER_STRUCT_STG WHERE STATUS ='VALIDATED' ;
        END IF;
        --
        --
         xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => '***********************************************  '
                            ,p_module => gc_module);
         xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => '******* Record Summery ************************  '
                            ,p_module => gc_module);
         xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => '**** Total Records :  '||l_total_count
                            ,p_module => gc_module);
        xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => '**** Total Processed Records :  '||l_process_count
                            ,p_module => gc_module);
        xx_fnd_utils_pkg.LOG (
                            p_debug_level => xx_fnd_utils_pkg.procedure_level
                            ,p_routine_name => lcroutine
                            ,p_debug_msg => '**** Total Errored Records :  '||l_error_count
                            ,p_module => gc_module);
END MAIN;
--    
END XX_HR_ORG_HIERARCHY_PKG;