APPS.CSM_USER_EVENT_PKG SQL Statements

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

Line: 24

SELECT 1					  --R12-Multiple Responsibility
FROM  asg_user_pub_resps aupr,
	  asg_user asu
WHERE aupr.user_name 		   = asu.user_name
AND   aupr.responsibility_id   = p_resp_id
AND   asu.user_id 			   = l_user_id
AND   aupr.pub_name			   = 'SERVICEP';

Line: 95

SELECT au.user_id
FROM asg_user au,
     asg_user_pub_resps  asg_resp
WHERE au.user_id <> p_user_id
AND au.enabled = 'Y'
AND asg_resp.pub_name = 'SERVICEP'
AND asg_resp.user_name = au.user_name;

Line: 205

IS  --SELECT tasks which are open and are closed and within history days
SELECT jt.task_id
FROM   JTF_TASKS_B jt,
       jtf_task_statuses_b jts
WHERE  jt.created_by = b_user_id
AND    jts.task_status_id = jt.task_status_id
AND    ( --task which are closed and within history days
	   	 (  jt.creation_date BETWEEN SYSDATE AND (SYSDATE - csm_profile_pkg.get_task_history_days(b_user_id))
      	   AND (   NVL(jts.cancelled_flag,'N')   = 'Y' OR NVL(jts.closed_flag,'N')     = 'Y'
           	 OR NVL(jts.rejected_flag,'N') 	= 'Y' OR NVL(jts.completed_flag, 'N') = 'Y'
		   	 )
	  	 )
         OR --task which are open
         (	 NOT (NVL(jts.cancelled_flag,'N') = 'Y' OR NVL(jts.closed_flag,'N') 	  = 'Y'
           OR NVL(jts.rejected_flag,'N') 	= 'Y' OR NVL(jts.completed_flag, 'N') = 'Y'
		       )
	     )
	  )
UNION	--select tasks which are created by the user and their correspdg SR is open
SELECT jt.task_id
FROM   JTF_TASKS_B jt
WHERE  jt.created_by = b_user_id
AND	   jt.source_object_type_code ='SR'
AND   EXISTS
	  (SELECT inc.incident_id
	   FROM   cs_incidents_all_b 	  inc,
       		  cs_incident_statuses_b ists
	   WHERE  inc.INCIDENT_STATUS_ID  = ists.INCIDENT_STATUS_ID
	   AND    NVL(ists.CLOSE_FLAG,'N')= 'N'
	   AND    inc.incident_id 		  = jt.source_object_id
	  );

Line: 270

SELECT incident_id
FROM   cs_incidents_all_b inc,
       cs_incident_statuses_b   ists
WHERE (
       inc.owner_group_id = b_grp_id
      OR
       csm_util_pkg.get_owner(inc.created_by)=b_user_id
      )
AND    inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
AND    inc.install_site_id IS NOT NULL
AND    NVL(ists.CLOSE_FLAG, 'N') <> 'Y';

Line: 285

SELECT GROUP_ID,USER_ID
FROM ASG_USER
WHERE USER_ID=b_user_id
AND   USER_ID=OWNER_ID;

Line: 329

SELECT jtf_ta.task_assignment_id  --get all the task assignments which are open and of type TASK
FROM   jtf_rs_resource_extns jtf_rs,
       jtf_task_assignments  jtf_ta,
       jtf_task_statuses_b 	 jts_jta
WHERE  jtf_rs.user_id 	   	  = p_user_id
AND    jtf_ta.resource_id 	  = jtf_rs.resource_id
AND    jts_jta.task_Status_id = jtf_ta.assignment_status_id
AND NOT( NVL(jts_jta.cancelled_flag, 'N')     = 'Y' OR NVL(jts_jta.closed_flag, 'N')  = 'Y'
     	  OR NVL(jts_jta.completed_flag, 'N') = 'Y' OR NVL(jts_jta.rejected_flag,'N') = 'Y')
AND EXISTS (

		    SELECT 1
		    FROM   jtf_tasks_b jt,
      			   jtf_task_statuses_b jts
		    WHERE 	jt.task_id = jtf_ta.task_id
			AND    jts.task_status_id = jt.task_status_id
			AND    jt.source_object_type_code ='TASK'
 			AND NOT (   NVL(jts.cancelled_flag, 'N')     = 'Y' OR NVL(jts.closed_flag, 'N')      = 'Y'
          		OR NVL(jts.completed_flag, 'N') 	  = 'Y' OR NVL(jts.rejected_flag,'N')     = 'Y'
	 			 )
 			)
UNION ALL--Select task assignments which are for open SRs
SELECT jtf_ta.task_assignment_id
FROM   jtf_rs_resource_extns jtf_rs,
       jtf_task_assignments jtf_ta,
	   JTF_TASKS_B jt
WHERE  jtf_rs.user_id 	   	  = p_user_id
AND    jt.task_id 			  = jtf_ta.task_id
AND    jtf_ta.resource_id 	  = jtf_rs.resource_id
AND    jt.source_object_type_code ='SR'
AND   EXISTS
	  (SELECT 'X'
	   FROM   cs_incidents_all_b 	  inc,
       		  cs_incident_statuses_b ists
	   WHERE  inc.INCIDENT_STATUS_ID  = ists.INCIDENT_STATUS_ID
	   AND    NVL(ists.CLOSE_FLAG,'N')= 'N'
	   AND    inc.incident_id 		  = jt.source_object_id
	  );

Line: 412

    DELETE FROM csm_user_inventory_org WHERE user_id = p_user_id;

Line: 414

    INSERT INTO csm_user_inventory_org (
      user_id, organization_id, last_update_date, last_updated_by,
      creation_date, created_by, category_set_id, category_id )
    VALUES (
      p_user_id, l_organization_id, SYSDATE, 1, SYSDATE, 1,
      l_category_set_id, l_category_id );

Line: 448

SELECT csp_inv_loc_assignment_id
FROM csp_inv_loc_assignments
WHERE resource_id = p_resource_id
AND resource_type = 'RS_EMPLOYEE'
AND SYSDATE BETWEEN nvl(effective_date_start, SYSDATE) AND nvl(effective_date_end, SYSDATE);

Line: 486

SELECT pla.location_id              location_id,
       pla.site_use_id              site_use_id,
       rcr.resource_id              resource_id,
       jtrs.user_id                 user_id,
       hps.party_site_id            party_site_id
FROM   po_location_associations_all pla,
       hz_cust_site_uses_all        csu,
       hz_cust_acct_sites_all       cas,
       csp_rs_cust_relations        rcr,
       jtf_rs_resource_extns        jtrs,
       hz_party_sites               hps,
       hz_locations                 hzl
WHERE  csu.site_use_id       = pla.site_use_id
AND    csu.site_use_code     = 'SHIP_TO'
AND    csu.cust_acct_site_id = cas.cust_acct_site_id
AND    csu.status            = 'A'
AND    cas.cust_account_id   = rcr.customer_id
AND    cas.status            = 'A'
AND    cas.party_site_id     = hps.party_site_id
AND    hps.location_id       = hzl.location_id
AND    jtrs.resource_id      = rcr.resource_id
AND    jtrs.resource_id      = p_resource_id;

Line: 552

SELECT hdr.requirement_header_id,
       jtrs.resource_id,
       jtrs.user_id
FROM   csp_requirement_headers hdr,
       jtf_rs_resource_extns jtrs
WHERE hdr.resource_id = jtrs.resource_id
AND   jtrs.resource_id = p_resource_id
AND NOT EXISTS
(SELECT 1
 FROM csm_req_headers_acc acc
 WHERE acc.requirement_header_id = hdr.requirement_header_id
 AND acc.user_id = jtrs.user_id
 );

Line: 601

SELECT hdr.requirement_header_id,
       hdr.resource_id,
       line.requirement_line_id,
       jtrs.user_id
FROM   csp_requirement_headers hdr,
       csp_requirement_lines line,
       jtf_rs_resource_extns jtrs
WHERE  hdr.requirement_header_id = line.requirement_header_id
AND    hdr.resource_id = jtrs.resource_id
AND    jtrs.resource_id = p_resource_id
AND NOT EXISTS
(SELECT 1
 FROM csm_req_lines_acc acc
 WHERE acc.requirement_line_id = line.requirement_line_id
 AND acc.user_id = jtrs.user_id
 );

Line: 680

SELECT resource_id
FROM asg_user
WHERE user_id = p_user_id;

Line: 704

  CSM_USER_EVENT_PKG.INSERT_ACC (p_user_id =>p_user_id
             ,x_return_status =>l_return_status
             ,x_error_message =>l_error_msg);

Line: 719

  CSM_GROUP_DOWNLOAD_PVT.INSERT_MY_GROUP (p_user_id=>p_user_id
                                    ,x_return_status=>l_return_status
                                    , x_error_message => l_error_msg);

Line: 739

  CSM_LOBS_EVENT_PKG.Insert_all_acc_records(p_user_id=>p_user_id);

Line: 831

     DELETE FROM CSM_DEBRIEF_LINES_ACC 	 	   WHERE user_id = p_user_id;

Line: 832

     DELETE FROM CSM_DEBRIEF_HEADERS_ACC 	   WHERE user_id = p_user_id;

Line: 833

     DELETE FROM CSM_ITEM_INSTANCES_ACC  	   WHERE user_id = p_user_id;

Line: 834

     DELETE FROM CSM_NOTES_ACC 			 	   WHERE user_id = p_user_id;

Line: 835

     DELETE FROM CSM_NOTIFICATIONS_ACC 	 	   WHERE user_id = p_user_id;

Line: 837

     DELETE FROM CSM_PARTIES_ACC 		 	   WHERE user_id = p_user_id;

Line: 838

     DELETE FROM CSM_PARTY_SITES_ACC 	 	   WHERE user_id = p_user_id;

Line: 839

     DELETE FROM csm_profile_option_values_acc WHERE user_id = p_user_id;

Line: 841

     DELETE FROM CSM_RS_RESOURCE_EXTNS_ACC 	   WHERE user_id = p_user_id;

Line: 845

     DELETE FROM CSM_TASK_ASSIGNMENTS_ACC 	   WHERE user_id = p_user_id;

Line: 847

     DELETE FROM CSM_TASKS_ACC 				   WHERE user_id = p_user_id;

Line: 848

     DELETE FROM CSM_INCIDENTS_ALL_ACC 		   WHERE user_id = p_user_id;

Line: 849

     DELETE FROM CSM_SR_CONTACTS_ACC 		   WHERE user_id = p_user_id;

Line: 850

     DELETE FROM CSM_CUSTOMIZATION_VIEWS_ACC   WHERE user_id = p_user_id;

Line: 851

     DELETE FROM CSM_PAGE_PERZ_DELTA_ACC 	   WHERE user_id = p_user_id;

Line: 853

	 DELETE FROM CSM_NEW_MESSAGES_ACC 		   WHERE user_id = p_user_id;

Line: 854

     DELETE FROM CSM_STATE_TRANSITIONS_ACC 	   WHERE user_id = p_user_id;

Line: 855

     DELETE FROM csm_mtl_system_items_acc 	   WHERE user_id = p_user_id;

Line: 856

     DELETE FROM csm_mtl_serial_numbers_acc    WHERE user_id = p_user_id;

Line: 857

     DELETE FROM csm_mtl_material_txn_acc 	   WHERE user_id = p_user_id;

Line: 859

     DELETE FROM csm_mtl_txn_lot_num_acc 	   WHERE user_id = p_user_id;

Line: 860

     DELETE FROM csm_mtl_unit_txn_acc 		   WHERE user_id = p_user_id;

Line: 861

     DELETE FROM csm_inv_loc_ass_acc 		   WHERE user_id = p_user_id;

Line: 862

     DELETE FROM csm_po_loc_ass_all_acc 	   WHERE user_id = p_user_id;

Line: 863

     DELETE FROM csm_req_lines_acc 			   WHERE user_id = p_user_id;

Line: 865

     DELETE FROM csm_req_headers_acc 		   WHERE user_id = p_user_id;

Line: 866

     DELETE FROM csm_system_items_acc 		   WHERE user_id = p_user_id;

Line: 867

     DELETE FROM csm_mtl_item_subinv_acc 	   WHERE user_id = p_user_id;

Line: 868

     DELETE FROM csm_mtl_sec_inv_acc 		   WHERE user_id = p_user_id;

Line: 869

     DELETE FROM csm_ii_relationships_acc 	   WHERE user_id = p_user_id;

Line: 871

     DELETE FROM csm_contr_headers_acc 		   WHERE user_id = p_user_id;

Line: 872

     DELETE FROM csm_contr_buss_processes_acc  WHERE user_id = p_user_id;

Line: 873

     DELETE FROM csm_contr_buss_txn_types_acc  WHERE user_id = p_user_id;

Line: 875

     DELETE FROM csm_service_history_acc 	   WHERE user_id = p_user_id;

Line: 876

     DELETE FROM csm_debrief_headers_acc 	   WHERE user_id = p_user_id;

Line: 878

     DELETE FROM csm_counters_acc 			   WHERE user_id = p_user_id;

Line: 879

     DELETE FROM csm_counter_values_acc 	   WHERE user_id = p_user_id;

Line: 880

     DELETE FROM csm_mtl_item_locations_acc    WHERE user_id = p_user_id;

Line: 881

     DELETE FROM csm_mtl_secondary_locators_acc WHERE user_id = p_user_id;

Line: 882

     DELETE FROM csm_mtl_onhand_qty_acc 	   WHERE user_id = p_user_id;

Line: 884

     DELETE FROM csm_user_inventory_org 	   WHERE user_id = p_user_id;

Line: 885

     DELETE FROM CSM_FND_LOBS_ACC 			   WHERE user_id = p_user_id;--Bug 4938130

Line: 886

	 DELETE FROM CSM_HZ_CUST_ACCOUNTS_ACC      WHERE user_id = p_user_id;--Bug 5213097

Line: 887

	 DELETE FROM CSM_SR_TYPE_MAPPING_ACC	   WHERE user_id = p_user_id;--Bug 5213097

Line: 888

	 DELETE FROM CSM_CSI_ITEM_ATTR_ACC		   WHERE user_id = p_user_id;--Bug 5213097

Line: 889

	 DELETE FROM CSM_HZ_LOCATIONS_ACC		   WHERE user_id = p_user_id;--Bug 5213097

Line: 891

	 DELETE FROM CSM_HZ_CONTACT_POINTS_ACC 	   WHERE user_id = p_user_id;

Line: 892

	 DELETE FROM CSM_COUNTER_PROP_VALUES_ACC   WHERE user_id = p_user_id;

Line: 893

	 DELETE FROM CSM_COUNTER_PROPERTIES_ACC	   WHERE user_id = p_user_id;

Line: 894

	 DELETE FROM CSM_HZ_RELATIONSHIPS_ACC 	   WHERE user_id = p_user_id;

Line: 895

	 DELETE FROM CSM_PARTY_ASSIGNMENT 	   WHERE user_id = p_user_id;

Line: 898

  CSM_GROUP_DOWNLOAD_PVT.DELETE_MY_GROUP (p_user_id=>p_user_id
                                    ,x_return_status=>l_return_status
                                    , x_error_message => l_error_msg);

Line: 902

  CSM_USER_EVENT_PKG.DELETE_ACC (p_user_id =>p_user_id
             ,x_return_status =>l_return_status
             ,x_error_message =>l_error_msg);

Line: 958

	 CSM_SERIAL_NUMBERS_EVENT_PKG.insert_mtl_serial_numbers(l_organization_id,TO_DATE('1','J'),p_resource_id,p_user_id);

Line: 974

PROCEDURE INSERT_ACC (p_user_id IN NUMBER
                                    ,x_return_status OUT NOCOPY VARCHAR2
                                    , x_error_message OUT NOCOPY VARCHAR2)
IS
l_sqlerrno 	VARCHAR(20);

Line: 993

SELECT USER_ID,OWNER_ID,GROUP_ID
FROM
ASG_USER
WHERE USER_ID=l_user_id;

Line: 1000

SELECT USER_ID
FROM
CSM_USER_ACC
WHERE USER_ID=l_user_id;

Line: 1007

SELECT USR.USER_ID,USR.OWNER_ID,ACC.ACCESS_ID
FROM   ASG_USER USR,
       CSM_USER_ACC ACC
WHERE  USR.OWNER_ID=l_owner_id
AND    USR.USER_ID = ACC.USER_ID;

Line: 1014

   CSM_UTIL_PKG.LOG('Entering INSERT_ACC for User_id ' || p_user_id,
     'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1023

    DELETE FROM CSM_USER_ACC WHERE USER_ID =l_user_id;

Line: 1026

   SELECT CSM_USER_ACC_S.NEXTVAL INTO l_access_id  FROM DUAL;

Line: 1028

   INSERT INTO CSM_USER_ACC(ACCESS_ID,USER_ID,OWNER_ID,COUNTER,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
   VALUES     (l_access_id,l_user_id,l_owner_id,1,sysdate,1,sysdate,1,1);

Line: 1039

   CSM_UTIL_PKG.LOG('User is inserted into the Access table: User_id ' || p_user_id,
     'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1046

        l_tab_user_id.DELETE;

Line: 1049

        l_tab_owner_id.DELETE;

Line: 1052

        l_tab_access_id.DELETE;

Line: 1059

          UPDATE CSM_USER_ACC
          SET OWNER_ID     = l_tab_owner_id(i),
          LAST_UPDATE_DATE = SYSDATE
          WHERE USER_ID = l_tab_user_id(i);

Line: 1078

   CSM_UTIL_PKG.LOG('Leaving INSERT_ACC for User_id ' || p_user_id,
     'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1084

	  l_error_msg := 'Exception in 	INSERT_ACC for User_id '
	  || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;

Line: 1087

	  CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);

Line: 1089

END INSERT_ACC;

Line: 1091

PROCEDURE DELETE_ACC (p_user_id IN NUMBER
                                    ,x_return_status OUT NOCOPY VARCHAR2
                                    , x_error_message OUT NOCOPY VARCHAR2)
IS
l_sqlerrno 	VARCHAR(20);

Line: 1106

SELECT ACCESS_ID,USER_ID,OWNER_ID
FROM
CSM_USER_ACC
WHERE USER_ID=l_user_id;

Line: 1113

   CSM_UTIL_PKG.LOG('Entering DELETE_ACC for User_id ' || p_user_id,
     'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1130

      DELETE FROM CSM_USER_ACC WHERE USER_ID =l_user_id;

Line: 1132

      CSM_UTIL_PKG.LOG('User is Deleted From the Access table for  User_id :' || p_user_id,
        'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1137

   CSM_UTIL_PKG.LOG('Leaving DELETE_ACC for User_id ' || p_user_id,
     'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1143

	  l_error_msg := 'Exception in 	DELETE_ACC for User_id '
	  || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;

Line: 1146

	  CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);

Line: 1148

END DELETE_ACC;

Line: 1151

  The following two apis are used for inserting/deleting
  non-group member records in CSM_USER_ACC for the (user,owner)
*/

PROCEDURE INSERT_ACC (p_user_id IN NUMBER,p_owner_id IN NUMBER)
IS
l_sqlerrno 	VARCHAR(20);

Line: 1169

SELECT ACCESS_ID
FROM CSM_USER_ACC
WHERE USER_ID=b_user_id
AND OWNER_ID = b_owner_id;

Line: 1175

   CSM_UTIL_PKG.LOG('Entering INSERT_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
     'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1182

     'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1183

     UPDATE CSM_USER_ACC SET COUNTER=COUNTER+1 ,LAST_UPDATE_DATE=sysdate WHERE ACCESS_ID=l_access_id;

Line: 1189

     SELECT CSM_USER_ACC_S.NEXTVAL INTO l_access_id  FROM DUAL;

Line: 1191

     INSERT INTO CSM_USER_ACC(ACCESS_ID,USER_ID,OWNER_ID,COUNTER,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
     VALUES     (l_access_id,p_user_id,p_owner_id,1,sysdate,1,sysdate,1,1);

Line: 1194

     CSM_UTIL_PKG.LOG('Record inserted into the Access table: (User_id,owner_id) : (' || p_user_id||','||p_owner_id||') and Marking dirty',
       'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1206

   CSM_UTIL_PKG.LOG('Leaving INSERT_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1211

	  l_error_msg := 'Exception in 	INSERT_ACC for User_id '
	  || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;

Line: 1214

	  CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);

Line: 1216

END INSERT_ACC;

Line: 1218

PROCEDURE DELETE_ACC (p_user_id IN NUMBER,p_owner_id IN NUMBER)
IS
l_sqlerrno 	VARCHAR(20);

Line: 1230

SELECT access_id,counter
FROM CSM_USER_ACC
WHERE USER_ID=b_user_id
AND OWNER_ID = b_owner_id;

Line: 1236

   CSM_UTIL_PKG.LOG('Entering DELETE_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
     'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1252

       DELETE FROM CSM_USER_ACC WHERE USER_ID =p_user_id
 	   AND OWNER_ID=p_owner_id;

Line: 1255

       UPDATE CSM_USER_ACC SET COUNTER=COUNTER-1 ,LAST_UPDATE_DATE=sysdate WHERE USER_ID =p_user_id
       AND OWNER_ID=p_owner_id;

Line: 1260

   CSM_UTIL_PKG.LOG('Leaving DELETE_ACC for User_id ' || p_user_id ||' and owner_id: '||p_owner_id,
     'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 1266

	  l_error_msg := 'Exception in 	DELETE_ACC for User_id '
	  || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;

Line: 1269

	  CSM_UTIL_PKG.LOG(l_error_msg,'CSM_USER_EVENT_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);

Line: 1271

END DELETE_ACC;