APPS.CSM_LOOKUP_EVENT_PKG SQL Statements

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

Line: 28

l_prog_update_date 			  jtm_con_request_data.last_run_date%TYPE;

Line: 43

SELECT 	nvl(last_run_date, (sysdate - 365*50))
FROM 	jtm_con_request_data
WHERE 	package_name   = 'CSM_LOOKUP_EVENT_PKG'
AND 	procedure_name = 'REFRESH_ACC';

Line: 51

SELECT 	val.lookup_type,
		val.view_application_id,
		val.lookup_code,
       	val.security_group_id,
		val.language,
		val.last_update_date
FROM 	fnd_lookup_values val
WHERE 	enabled_flag = 'Y'
AND (
    (val.view_application_id = 0   AND val.lookup_type  = 'JTF_RS_RESOURCE_TYPES' AND val.lookup_code IN ('RS_EMPLOYEE','RS_GROUP'))
OR
    (val.view_application_id = 0   AND val.lookup_type  = 'JTF_NOTE_STATUS')
OR  (val.view_application_id = 0   AND val.lookup_type  = 'JTF_NOTE_TYPE' AND
     (val.lookup_code in (select object_id
                          from jtf_object_mappings
                          where object_code like 'JTF_NOTE_TYPE%' and
                          source_object_code in ('SR', 'TASK', 'PARTY', 'CP', 'SD', 'OKS_COV_NOTE')) OR
        NOT EXISTS ( SELECT 1
                     FROM jtf_object_mappings
                     WHERE object_id = val.lookup_code)))
OR  (val.view_application_id = 170 AND val.lookup_type	 = 'REQUEST_PROBLEM_CODE')
OR  (val.view_application_id = 0   AND val.lookup_type 	 = 'CSF_MATERIAL_REASON')
OR  (val.view_application_id = 0   AND val.lookup_type 	 = 'CSF_EXPENSE_REASON')
OR  (val.view_application_id = 0   AND val.lookup_type 	 = 'CSF_LABOR_REASON')
OR  (val.view_application_id = 0   AND val.lookup_type   = 'CSP_RECOVERED_PART_DISP_CODE')
OR  (val.view_application_id = 170 AND val.lookup_type   = 'REQUEST_RESOLUTION_CODE')
OR 	(val.view_application_id = 170 AND val.lookup_type   = 'CS_CTR_MISC_READING_TYPE')
OR 	(val.view_application_id = 690 AND val.lookup_type	 = 'JTF_TASK_ESC_LEVEL')
OR 	(val.view_application_id = 222 AND val.lookup_type 	 = 'CREDIT_MEMO_REASON')
OR 	(val.view_application_id = 542 AND val.lookup_type 	 = 'CSI_ACCOUNTING_CLASS_CODE')
OR  (val.view_application_id = 542 AND val.lookup_type 	 = 'CSI_INST_TYPE_CODE')
OR  (val.view_application_id = 542 AND val.lookup_type 	 = 'CSI_COUNTER_DIRECTION_TYPE')
OR  (val.view_application_id = 542 AND val.lookup_type 	 = 'CSI_CTR_READING_RESET_TYPE')
OR  (val.view_application_id = 0   AND val.lookup_type   = 'ATCHMT_DOCUMENT_TYPE')
OR 	(val.view_application_id = 170 AND val.lookup_type	 = 'CS_SR_CONTACT_TYPE')
OR  (val.view_application_id = 542 AND val.lookup_type 	 = 'CSI_CTR_PROPERTY_LOV_TYPE')
OR  (val.view_application_id = 0   AND val.lookup_type 	 = 'YES_NO')
	  )
AND NOT EXISTS
 (SELECT 1
  FROM  csm_lookups_acc acc
  WHERE acc.lookup_type    	    = val.lookup_type
  AND 	acc.language 	   	    = val.language
  AND 	acc.lookup_code    	    = val.lookup_code
  AND 	acc.security_group_id   = val.security_group_id
  AND 	acc.view_application_id = val.view_application_id
  )
;

Line: 103

SELECT acc.access_id, val.lookup_type,
	   val.view_application_id,
	   val.lookup_code,
       val.security_group_id,
	   val.language,
	   val.last_update_date
FROM   fnd_lookup_values val,
       csm_lookups_acc acc
WHERE  acc.lookup_type 	        = val.lookup_type
AND    acc.language 	  	    = val.language
AND    acc.lookup_code 	  	    = val.lookup_code
AND    acc.security_group_id    = val.security_group_id
AND    acc.view_application_id  = val.view_application_id
AND    val.last_update_date     >= p_last_upd_date
AND    enabled_flag    		    = 'Y';

Line: 122

SELECT acc.access_id,
	   acc.lookup_type,
	   acc.view_application_id,
	   acc.lookup_code,
	   acc.security_group_id,
	   acc.language,
	   acc.last_update_date
FROM   csm_lookups_acc acc
WHERE NOT EXISTS
(SELECT 1
 FROM  fnd_lookup_values val
 WHERE acc.lookup_type          = val.lookup_type
 AND   acc.language 		    = val.language
 AND   acc.lookup_code 		    = val.lookup_code
 AND   acc.security_group_id    = val.security_group_id
 AND   acc.view_application_id  = val.view_application_id
 AND   val.enabled_flag = 'Y'
);

Line: 147

 FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 151

  FOR r_lookups_del_rec IN l_lookups_del_csr(l_prog_update_date) LOOP

     --get the users with this language
     l_tl_omfs_palm_resource_list := l_null_resource_list;

Line: 173

     DELETE FROM csm_lookups_acc WHERE access_id = l_access_id;

Line: 177

  FOR r_lookups_upd_rec IN l_lookups_upd_csr(l_prog_update_date) LOOP

     --get the users with this language
     l_tl_omfs_palm_resource_list := l_null_resource_list;

Line: 201

  FOR r_lookups_ins_rec IN l_lookups_ins_csr(l_prog_update_date) LOOP

     --get the users with this language
     l_tl_omfs_palm_resource_list := l_null_resource_list;

Line: 207

     SELECT csm_lookups_acc_s.nextval
     INTO l_access_id
     FROM dual;

Line: 224

     INSERT INTO csm_lookups_acc (access_id,
                                  lookup_type,
                                  language,
                                  lookup_code,
                                  security_group_id,
                                  view_application_id,
                                  created_by,
                                  creation_date,
                                  last_updated_by,
                                  last_update_date,
                                  last_update_login
                                  )
                          VALUES (l_access_id,
                                  r_lookups_ins_rec.lookup_type,
                                  r_lookups_ins_rec.language,
                                  r_lookups_ins_rec.lookup_code,
                                  r_lookups_ins_rec.security_group_id,
                                  r_lookups_ins_rec.view_application_id,
                                  fnd_global.user_id,
                                  l_run_date,
                                  fnd_global.user_id,
                                  l_run_date,
                                  fnd_global.login_id
                                  );

Line: 252

  UPDATE jtm_con_request_data
  SET 	 last_run_date  = l_run_date
  WHERE  package_name   = 'CSM_LOOKUP_EVENT_PKG'
    AND  procedure_name = 'REFRESH_ACC';