APPS.CSM_RESOURCE_EXTNS_EVENT_PKG SQL Statements

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

Line: 27

    CSM_ACC_PKG.Insert_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
                    , P_ACC_TABLE_NAME         => g_acc_table_name1
                    , P_SEQ_NAME               => g_acc_sequence_name1
                    , P_PK1_NAME               => g_pk1_name1
                    , P_PK1_NUM_VALUE          => p_resource_id
                    , P_USER_ID                => p_user_id
     );

Line: 59

    CSM_ACC_PKG.Delete_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
                    , P_ACC_TABLE_NAME         => g_acc_table_name1
                    , P_PK1_NAME               => g_pk1_name1
                    , P_PK1_NUM_VALUE          => p_resource_id
                    , P_USER_ID                => p_user_id
     );

Line: 83

 SELECT resource_id
 FROM ASG_USER
 WHERE useR_id=b_user_id;

Line: 87

Cursor c_delete(b_resource_id NUMBER) IS
 SELECT user_id
 FROM CSM_RS_RESOURCE_EXTNS_ACC
 WHERE resource_id=b_resource_id;

Line: 107

   FOR rec IN C_DELETE(l_resource_id)
   LOOP
    RESOURCE_EXTNS_ACC_D(l_resource_id,rec.user_id);

Line: 113

   DELETE FROM CSM_RS_RESOURCE_EXTNS_ACC WHERE resource_id=l_resource_id;

Line: 131

  SELECT APP_ID, RESPONSIBILITY_ID
  FROM ASG_USER
  WHERE USER_ID=p_user_id;

Line: 152

CURSOR c_delete_prof0(p_resource_id  NUMBER,p_user_id NUMBER) IS
 SELECT resource_id
 FROM   CSM_RS_RESOURCE_EXTNS_ACC ACC
 WHERE  user_id=p_user_id
 AND    resource_id <> p_resource_id
 AND    NOT EXISTS (SELECT 1
                    FROM  ASG_USER AU,
                          ASG_USER_PUB_RESPS AUPR
					WHERE AU.RESOURCE_ID=ACC.RESOURCE_ID
					AND   AU.user_name=AUPR.user_name
					AND   AUPR.pub_name='SERVICEP'
					AND   AU.ENABLED='Y');

Line: 165

CURSOR c_insert_prof0(p_user_id NUMBER) IS
 SELECT AU.resource_id
 FROM   ASG_USER AU,
        ASG_USER_PUB_RESPS AUPR
 WHERE  AU.enabled='Y'
 AND    AU.user_name=AUPR.user_name
 AND    AUPR.pub_name='SERVICEP'
 AND    AU.user_id <> p_user_id
 AND    NOT EXISTS(SELECT 1 FROM CSM_RS_RESOURCE_EXTNS_ACC ACC
                   WHERE ACC.USER_ID=p_user_ID
                   AND   ACC.RESOURCE_ID=AU.RESOURCE_ID);

Line: 178

CURSOR c_grp_members(p_resource_id NUMBER, p_user_id NUMBER) IS  --insert prof2
 SELECT DISTINCT jtf_rs.user_id,
                 jtf_rs.resource_id
 FROM jtf_rs_group_members jtf_rs_grp,
      jtf_rs_resource_extns jtf_rs
 WHERE EXISTS (SELECT 1
               FROM jtf_rs_group_members
               WHERE group_id = jtf_rs_grp.group_id
			   AND  resource_id = p_resource_id
               AND  delete_flag = 'N')
 AND jtf_rs.resource_id = jtf_rs_grp.resource_id
 AND jtf_rs.resource_id <> p_resource_id
 AND sysdate BETWEEN jtf_rs.start_date_active  AND NVL(jtf_rs.end_date_active,sysdate)
 AND jtf_rs.USER_ID IS NOT NULL
 AND jtf_rs.USER_NAME IS NOT NULL
 AND NOT EXISTS(SELECT 1 FROM  CSM_RS_RESOURCE_EXTNS_ACC ACC
                WHERE USER_ID=p_user_ID
                AND   ACC.RESOURCE_ID=jtf_rs.resource_id);

Line: 198

CURSOR c_delete_prof2(p_resource_id NUMBER,p_user_id NUMBER) IS --delete prof2
 SELECT resource_id
 FROM   CSM_RS_RESOURCE_EXTNS_ACC ACC
 WHERE  acc.user_id=p_user_id
 AND    NOT EXISTS ( SELECT 1
                     FROM jtf_rs_group_members jtf_rs_grp,
                          jtf_rs_resource_extns valid_fnd_user
                     WHERE EXISTS (SELECT 1
                                   FROM jtf_rs_group_members
                                   WHERE group_id = jtf_rs_grp.group_id
                                   AND   resource_id = p_resource_id
                                   AND   delete_flag = 'N')
                     AND jtf_rs_grp.resource_id= Acc.resource_id
					 AND valid_fnd_user.resource_id=Acc.resource_id
					 AND valid_fnd_user.USER_ID IS NOT NULL
    			     AND valid_fnd_user.USER_NAME IS NOT NULL);

Line: 228

    FOR user_rec IN c_delete_prof0(p_resource_id,p_user_id)
    LOOP
      RESOURCE_EXTNS_ACC_D(user_rec.resource_id,p_user_id);

Line: 233

    CSM_UTIL_PKG.LOG('INSERTING FOR PROFILE-0 for user id : ' || p_user_id,
                                   'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

Line: 235

    FOR user_rec IN c_insert_prof0(p_user_id)
    LOOP
      RESOURCE_EXTNS_ACC_I(user_rec.resource_id,p_user_id);

Line: 245

   FOR user_rec IN c_delete_prof2(p_resource_id,p_user_id)
   LOOP
     RESOURCE_EXTNS_ACC_D(user_rec.resource_id,p_user_id);

Line: 250

   CSM_UTIL_PKG.LOG('INSERTING FOR PROFILE "1" for user id : ' || p_user_id,
                                   'CSM_RESOURCE_EXTNS_EVENT_PKG.PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

Line: 278

  SELECT USER_ID,RESOURCE_ID
  FROM ASG_USER au,
       ASG_USER_PUB_RESPS aupr
  WHERE au.USER_NAME=aupr.USER_NAME
  AND   au.USER_ID=au.OWNER_ID
  AND   au.ENABLED='Y'
  AND   aupr.PUB_NAME='SERVICEP';

Line: 326

  SELECT DISTINCT au.user_id,
                  au.resource_id
  FROM jtf_rs_group_members jtf_rs_grp,
       ASG_user au,
       ASG_USER_PUB_RESPS aupr
  WHERE EXISTS (SELECT 1
                FROM jtf_rs_group_members
                WHERE group_id = jtf_rs_grp.group_id
                AND   resource_id = p_resource_id
                AND   delete_flag = 'N')
  AND   jtf_rs_grp.resource_id=au.resource_id
  AND   au.enabled='Y'
  AND   au.resource_id<>p_resource_id
  AND   au.USER_NAME=aupr.USER_NAME
  AND   aupr.PUB_NAME='SERVICEP';

Line: 344

  SELECT AU.user_id,AU.resource_id
  FROM ASG_USER AU,
       ASG_USER_PUB_RESPS aupr
  WHERE AU.ENABLED='Y'
  AND   AU.resource_id<>p_resource_id
  AND   au.USER_NAME=aupr.USER_NAME
  AND   aupr.PUB_NAME='SERVICEP';

Line: 374

   	   grp_mfs_user.delete;

Line: 411

SELECT au.resource_id, au.user_id
FROM jtf_rs_group_members grp,
     asg_user au,
     asg_user_pub_resps aupr
WHERE grp.group_id = p_group_id
AND   grp.resource_id = au.resource_id
AND   grp.delete_flag = 'N'
AND   au.enabled='Y'
AND   au.USER_NAME=aupr.USER_NAME
AND   aupr.PUB_NAME='SERVICEP'
AND   grp.resource_id <> p_resource_id
AND EXISTS (SELECT 1                            --12.1 -SHD BE A VALID FND USER
            FROM JTF_RS_RESOURCE_EXTNS
            WHERE RESOURCE_ID=p_resource_id
            AND USER_NAME IS NOT NULL
            AND USER_ID IS NOT NULL );

Line: 431

SELECT au.user_id
FROM   asg_user au,
       asg_user_pub_resps aupr
WHERE au.resource_id = p_resource_id
AND   au.USER_NAME=aupr.USER_NAME
AND   aupr.PUB_NAME='SERVICEP'
AND   au.enabled='Y';

Line: 459

        IF PROFILE_VALUE(l_user_id)=2 THEN   --0 means all palm users are already there so dont insert
         RESOURCE_EXTNS_ACC_I(r_group_members_rec.resource_id,l_user_id);

Line: 462

        IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN --0 means all palm users are already there so dont insert
         RESOURCE_EXTNS_ACC_I(p_resource_id,r_group_members_rec.user_id);

Line: 466

        IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN --insert if profile is set at grp level
          RESOURCE_EXTNS_ACC_I(p_resource_id,r_group_members_rec.user_id);

Line: 491

SELECT au.resource_id, au.user_id
FROM jtf_rs_group_members grp,
     asg_user au,
     asg_user_pub_resps aupr
WHERE grp.group_id = p_group_id
AND   grp.resource_id = au.resource_id
AND   grp.delete_flag = 'N'
AND   au.enabled='Y'
AND   au.USER_NAME=aupr.USER_NAME
AND   aupr.PUB_NAME='SERVICEP'
AND   grp.resource_id <> p_resource_id
AND EXISTS (SELECT 1                            --12.1 -SHD BE A VALID FND USER
            FROM JTF_RS_RESOURCE_EXTNS
            WHERE RESOURCE_ID=p_resource_id
            AND USER_NAME IS NOT NULL
            AND USER_ID IS NOT NULL);

Line: 511

SELECT au.user_id
FROM   asg_user au,
       asg_user_pub_resps aupr
WHERE au.resource_id = p_resource_id
AND   au.USER_NAME=aupr.USER_NAME
AND   aupr.PUB_NAME='SERVICEP'
AND   au.enabled='Y';

Line: 539

        IF PROFILE_VALUE(l_user_id)=2 THEN   --0 means all palm users shd be there so dont delete
         RESOURCE_EXTNS_ACC_D(r_group_members_rec.resource_id,l_user_id);

Line: 542

        IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN --0 means all palm users shd be there so dont delete
         RESOURCE_EXTNS_ACC_D(p_resource_id,r_group_members_rec.user_id);

Line: 545

	   ELSE --non palm user so delete if he leaves the grp and if the profile is set to grp level
        IF PROFILE_VALUE(r_group_members_rec.user_id)=2 THEN
          RESOURCE_EXTNS_ACC_D(p_resource_id,r_group_members_rec.user_id);