APPS.CSL_JTF_RS_GRP_MEM_ACC_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 22

    SELECT group_id, resource_id
    FROM  jtf_rs_group_members
    WHERE DELETE_FLAG = 'N'
    AND   group_member_id = b_group_member_id;

Line: 32

    SELECT resource_id
    FROM   jtf_rs_group_members
    WHERE  group_id = b_group_id
    AND    delete_flag = 'N';

Line: 69

          'Group member record was deleted'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
        );

Line: 105

/*** Private procedure that inserts given group member related data for resource ***/
PROCEDURE Insert_ACC_Record
  ( p_group_member_id IN NUMBER
    ,p_resource_id    IN NUMBER
  )
IS
  /*** cursor to retrieve group member details ***/
  CURSOR c_group_member( b_group_member_id NUMBER ) IS
    SELECT rgm.resource_id
    ,      rxt.user_id
    FROM  jtf_rs_group_members  rgm
    ,     jtf_rs_resource_extns rxt
    WHERE rgm.resource_id = rxt.resource_id
    AND   rgm.group_member_id = b_group_member_id;

Line: 127

      , 'Entering Insert_ACC_Record'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      );

Line: 137

      , 'Inserting ACC record for resource id = ' || p_resource_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 141

  /** Insert the related resource id in Acc table **/
  JTM_HOOK_UTIL_PKG.Insert_Acc
    ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
    , P_ACC_TABLE_NAME          => g_acc_table_name
    , P_PK1_NAME                => g_pk1_name
    , P_PK1_NUM_VALUE           => p_group_member_id
    , P_RESOURCE_ID             => p_resource_id
    );

Line: 155

    /** Delete the resource extns ACC record **/
    CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Insert_Resource_Extns
     ( r_group_member.resource_id
     , p_resource_id
      );

Line: 161

    /*** Delete the fnd_user record ***/
    CSL_FND_USER_ACC_PKG.Insert_User
      ( r_group_member.user_id
      , p_resource_id
      );

Line: 174

      , 'Leaving Insert_ACC_Record'
     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
     );

Line: 178

END Insert_ACC_Record;

Line: 180

/*** Private procedure that deletes group member from acc table ***/
PROCEDURE Delete_ACC_Record
  ( p_group_member_id  IN NUMBER
   ,p_resource_id      IN NUMBER
  )
IS
  /*** cursor to retrieve group member details ***/
  CURSOR c_group_member( b_group_member_id NUMBER ) IS
    SELECT rgm.resource_id
    ,      rxt.user_id
    FROM  jtf_rs_group_members  rgm
    ,     jtf_rs_resource_extns rxt
    WHERE rgm.resource_id = rxt.resource_id
    AND   rgm.group_member_id = b_group_member_id;

Line: 201

      ,'Entering Delete_ACC_Record'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
     );

Line: 215

  /*** Delete group member ACC record ***/
  JTM_HOOK_UTIL_PKG.Delete_Acc
    ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
    , P_ACC_TABLE_NAME         => g_acc_table_name
    , P_PK1_NAME               => g_pk1_name
    , P_PK1_NUM_VALUE          => p_group_member_id
    , P_RESOURCE_ID            => p_resource_id
    );

Line: 229

    /** Delete the resource extns ACC record **/
    CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Delete_Resource_Extns
     ( r_group_member.resource_id
     , p_resource_id
      );

Line: 235

    /*** Delete the fnd_user record ***/
    CSL_FND_USER_ACC_PKG.Delete_User
      ( r_group_member.user_id
      , p_resource_id
      );

Line: 247

      , 'Leaving Delete_ACC_Record'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      );

Line: 251

END Delete_ACC_Record;

Line: 253

/*** Called after Group member Insert ***/
PROCEDURE  POST_INSERT_RS_GROUP_MEMBER
  (P_GROUP_MEMBER_ID      IN   JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE,
   P_GROUP_ID             IN   JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
   P_RESOURCE_ID          IN   JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
   X_RETURN_STATUS        OUT NOCOPY  VARCHAR2
  )
IS
  CURSOR c_group_resources ( b_group_id NUMBER ) IS
   SELECT group_member_id, resource_id
   FROM   jtf_rs_group_members
   WHERE  group_id = b_group_id
   AND    delete_flag = 'N';

Line: 278

     , 'Entering POST_INSERT_RS_GROUP_MEMBER hook'
     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 341

        Insert_ACC_Record
        ( p_group_member_id
         ,r_group_resources.resource_id
        );

Line: 353

           by the previous Insert_ACC_Record call (we don't need it twice).
        ***/
        IF r_group_resources.group_member_id <> p_group_member_id THEN
          /*** no -> replicate existing member to new mobile group member ***/
          IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
          THEN
            jtm_message_log_pkg.Log_Msg
             ( p_group_member_id
             , g_table_name
             , 'Replicating existing member record to new mobile resource group member. ' ||
               fnd_global.local_chr(10) || 'group_member_id = ' || r_group_resources.group_member_id
             , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 367

          Insert_ACC_Record
          ( r_group_resources.group_member_id
           ,p_resource_id
          );

Line: 381

     , 'Leaving POST_INSERT_RS_GROUP_MEMBER hook'
     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 392

      , 'Caught exception in POST_INSERT Procedure:' || fnd_global.local_chr(10) || sqlerrm
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 397

END POST_INSERT_RS_GROUP_MEMBER;

Line: 399

/* Called after Group member delete */
PROCEDURE PRE_DELETE_RS_GROUP_MEMBER
  (P_GROUP_ID             IN   JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
   P_RESOURCE_ID          IN   JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
   X_RETURN_STATUS        OUT NOCOPY  VARCHAR2
  )
IS
  CURSOR c_group_resources ( b_group_id NUMBER ) IS
   SELECT group_member_id, resource_id
   FROM   jtf_rs_group_members
   WHERE  group_id = b_group_id
   AND    delete_flag = 'N';

Line: 414

   SELECT group_member_id
   FROM   jtf_rs_group_members
   WHERE  group_id = b_group_id
   AND    resource_id = b_resource_id
   AND    delete_flag = 'N';

Line: 431

     , 'Entering PRE_DELETE_RS_GROUP_MEMBER hook'
     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 435

  /*** retrieve group_member_id for deleted member ***/
  OPEN c_group_member( p_group_id, p_resource_id );

Line: 454

  /*** is deleted group member a mobile resource? ***/
  IF JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
    THEN
      jtm_message_log_pkg.Log_Msg
       ( l_group_member_id
       , g_table_name
       , 'Deleted group member is mobile resource'
       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 471

       , 'Deleted group member is not a mobile resource'
       , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 492

      /*** yes -> delete deleted member for mobile user ***/
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
      THEN
        jtm_message_log_pkg.Log_Msg
         ( l_group_member_id
         , g_table_name
         , 'Deleting deleted member record for mobile resource_id = ' || r_group_resources.resource_id
         , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 502

      Delete_ACC_Record
      ( l_group_member_id
       ,r_group_resources.resource_id
      );

Line: 508

    /*** Is deleted group member a mobile resource? ***/
    IF l_is_mobile_resource THEN
      /*** Yes -> has this record already been deleted above? ***/
      IF l_group_member_id <> r_group_resources.group_member_id THEN
        /*** No -> delete existing group member for deleted mobile resource ***/
        IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
        THEN
          jtm_message_log_pkg.Log_Msg
           ( l_group_member_id
           , g_table_name
           , 'Deleting existing member record for deleted mobile group member'
           , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 522

        Delete_ACC_Record
        ( r_group_resources.group_member_id
         ,p_resource_id
        );

Line: 535

     , 'Deleted group_member_id = ' || l_group_member_id
     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 544

     , 'Leaving PRE_DELETE_RS_GROUP_MEMBER hook'
     , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 556

      , 'Caught exception in PRE_DELETE Procedure:' || fnd_global.local_chr(10) || sqlerrm
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 561

END PRE_DELETE_RS_GROUP_MEMBER;

Line: 564

PROCEDURE Delete_All_ACC_Records
  ( p_resource_id in NUMBER
  , x_return_status OUT NOCOPY varchar2
  )
IS

  CURSOR c_grp_mem_acc( b_resource_id NUMBER)
  IS
   SELECT acc.group_member_id
   FROM  jtm_jtf_rs_group_members_acc acc
   WHERE acc.resource_id = b_resource_id;

Line: 585

    , 'Entering Delete_All_ACC_Records'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 607

        , 'Calling Delete_ACC_Record'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 611

      Delete_ACC_Record
      ( r_grp_mem_acc.group_member_id
       ,p_resource_id
      );

Line: 624

      , v_message     => 'Leaving Delete_All_ACC_Records'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 634

      , 'Caught exception in Delete_All_ACC_Records:' || fnd_global.local_chr(10) || sqlerrm
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 638

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_RS_GRP_MEM_ACC_PKG','Delete_All_ACC_Records',sqlerrm);

Line: 641

END Delete_All_ACC_Records;

Line: 644

PROCEDURE Insert_All_ACC_Records
  ( p_resource_id IN NUMBER
  , x_return_status OUT NOCOPY VARCHAR2
  )
IS
  CURSOR c_jtf_rs_group_members (b_resource_id NUMBER) IS
   SELECT group_member_id
   FROM JTF_RS_GROUP_MEMBERS
   WHERE delete_flag = 'N'
   AND group_id IN (
     SELECT group_id
     FROM   jtf_rs_group_members
     WHERE  delete_flag = 'N'
     AND    RESOURCE_ID = b_resource_id);

Line: 667

    , 'Entering Insert_All_ACC_Records'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 683

    /*** Yes -> insert all replicateable records ***/
    FOR r_jtf_rs_group_members IN c_jtf_rs_group_members( p_resource_id )
    LOOP
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
        jtm_message_log_pkg.Log_Msg
        ( r_jtf_rs_group_members.group_member_id
        , g_table_name
        , 'Calling Insert_ACC_Record'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 694

      Insert_ACC_Record
      ( r_jtf_rs_group_members.group_member_id
      , p_resource_id
      );

Line: 706

      , v_message     => 'Leaving Insert_All_ACC_Records procedure for user: ' || p_resource_id
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 716

      , 'Caught exception in Delete_All_ACC_Records:' || fnd_global.local_chr(10) || sqlerrm
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 720

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_RS_GRP_MEM_ACC_PKG','Insert_All_ACC_Records',sqlerrm);

Line: 723

END Insert_All_ACC_Records;