APPS.CSM_MTL_PARAMETERS_EVENT_PKG SQL Statements

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

Line: 44

l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 56

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

Line: 64

SELECT mtlp.organization_id
FROM mtl_parameters mtlp
WHERE  NOT EXISTS
(SELECT 1
 FROM csm_mtl_parameters_acc acc
 WHERE acc.organization_id = mtlp.organization_id
 );

Line: 75

SELECT acc.access_id, mtlp.organization_id
FROM mtl_parameters mtlp,
     csm_mtl_parameters_acc acc
WHERE (mtlp.creation_date < p_last_upd_date AND mtlp.last_update_date > p_last_upd_date)
AND acc.organization_id = mtlp.organization_id;

Line: 84

SELECT acc.access_id, acc.organization_id
FROM csm_mtl_parameters_acc acc
WHERE NOT EXISTS
(SELECT 1
 FROM mtl_parameters mtlp
 WHERE mtlp.organization_id = acc.organization_id
 );

Line: 96

 FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 122

     DELETE FROM csm_mtl_parameters_acc WHERE access_id = l_access_id;

Line: 126

  FOR r_mtl_parameters_upd_rec IN l_mtl_parameters_upd_csr(l_prog_update_date) LOOP

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

Line: 156

     SELECT csm_mtl_parameters_acc_s.nextval
     INTO l_access_id
     FROM dual;

Line: 173

     INSERT INTO csm_mtl_parameters_acc (access_id,
                                  organization_id,
                                  counter,
                                  created_by,
                                  creation_date,
                                  last_updated_by,
                                  last_update_date,
                                  last_update_login
                                  )
                          VALUES (l_access_id,
                                  r_mtl_parameters_ins_rec.organization_id,
                                  1,
                                  fnd_global.user_id,
                                  sysdate,
                                  fnd_global.user_id,
                                  sysdate,
                                  fnd_global.login_id
                                  );

Line: 195

  UPDATE jtm_con_request_data
  SET last_run_date = sysdate
  WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
    AND procedure_name = 'REFRESH_ACC';

Line: 218

l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 233

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

Line: 241

SELECT csm_mtl_parameters_acc_s.nextval as ACCESS_ID,b_user_id as USER_ID, from_organization_id,to_organization_id
FROM ( SELECT distinct mtsn.from_organization_id,mtsn.to_organization_id
       FROM MTL_INTERORG_PARAMETERS mtsn,csp_inv_loc_assignments inv
       WHERE b_res_id =inv.resource_id
       AND inv.organization_id in (mtsn.from_organization_id,mtsn.to_organization_id)
       AND NOT EXISTS (SELECT 1 FROM CSM_INTERORG_PARAMETERS_ACC acc
                       WHERE acc.from_organization_id = mtsn.from_organization_id
                       AND acc.to_organization_id = mtsn.to_organization_id
                       AND acc.user_id=b_user_id ));

Line: 254

SELECT acc.access_id,acc.user_id
FROM CSM_INTERORG_PARAMETERS_ACC acc,MTL_INTERORG_PARAMETERS mtsn
WHERE acc.from_organization_id = mtsn.from_organization_id
AND acc.to_organization_id = mtsn.to_organization_id
AND p_lrd < mtsn.last_update_date ;

Line: 263

SELECT acc.access_id,acc.user_id
FROM CSM_INTERORG_PARAMETERS_ACC acc
WHERE NOT EXISTS
(SELECT 1  FROM MTL_INTERORG_PARAMETERS mtsn
 WHERE mtsn.from_organization_id = acc.from_organization_id
 AND mtsn.to_organization_id = acc.to_organization_id
 )
UNION
SELECT acc.access_id,acc.user_id FROM CSM_INTERORG_PARAMETERS_ACC acc
WHERE NOT EXISTS
(SELECT 1  FROM  csp_inv_loc_assignments inv,asg_user au
 WHERE au.resource_id =inv.resource_id  AND au.user_id=acc.user_id AND inv.organization_id in (acc.from_organization_id,acc.to_organization_id));

Line: 282

 FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 286

  CSM_UTIL_PKG.LOG('Processing deletes', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);

Line: 291

		l_tab_access_id.DELETE;

Line: 292

		l_tab_user_id.DELETE;

Line: 297

		CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from CSM_INTERORG_PARAMETERS_ACC' ,
							 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC',FND_LOG.LEVEL_EVENT);

Line: 309

			DELETE FROM CSM_INTERORG_PARAMETERS_ACC WHERE access_id = l_tab_access_id(i);

Line: 316

  CSM_UTIL_PKG.LOG('Processing Updates', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);

Line: 319

  OPEN c_mtl_parameters_upd_csr(l_prog_update_date);

Line: 321

		l_tab_access_id.DELETE;

Line: 322

		l_tab_user_id.DELETE;

Line: 327

		CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records in CSM_INTERORG_PARAMETERS_ACC' ,
							 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC',FND_LOG.LEVEL_EVENT);

Line: 341

  CSM_UTIL_PKG.LOG('Processing inserts', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);

Line: 345

  FOR rec IN (SELECT user_id,resource_id from asg_user where MULTI_PLATFORM='Y' and enabled='Y')
  LOOP
       OPEN c_mtl_parameters_ins_csr(rec.USER_ID,rec.RESOURCE_ID);

Line: 349

			l_tab_access_id.DELETE;

Line: 350

			l_tab_user_id.DELETE;

Line: 351

            l_tab_from_org.DELETE;

Line: 352

            l_tab_to_org.DELETE;

Line: 357

			CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into CSM_INTERORG_PARAMETERS_ACC for user:'||rec.USER_ID ,
								 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC',FND_LOG.LEVEL_EVENT);

Line: 361

						  INSERT INTO CSM_INTERORG_PARAMETERS_ACC (access_id, user_id,from_organization_id, to_organization_id,
                                  counter, created_by, creation_date, last_updated_by, last_update_date )
                          VALUES (l_tab_access_id(i), l_tab_user_id(i),l_tab_from_org(i),l_tab_to_org(i),
                                  1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);

Line: 382

  UPDATE jtm_con_request_data
  SET last_run_date = sysdate
  WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
    AND procedure_name = 'REFRESH_INTERORG_ACC';

Line: 407

l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 413

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

Line: 428

SELECT csm_mtl_parameters_acc_s.nextval as ACCESS_ID,au.user_id, oft.organization_id,oft.freight_code,sm.ship_method_code
FROM ORG_FREIGHT_TL oft,asg_user au,WSH_CARRIER_SHIP_METHODS_V sm
WHERE oft.LANGUAGE=au.language
and nvl(oft.disable_date,sysdate+1) > sysdate
AND oft.organization_id=sm.organization_id(+)
AND oft.freight_code=sm.freight_code(+)
AND sm.enabled_flag(+) = 'Y'
AND sysdate between sm.start_date_active(+) AND nvl(sm.end_date_active(+), sysdate+1)
AND EXISTS(SELECT 1 FROM CSP_INV_LOC_ASSIGNMENTS ass
           WHERE ass.organization_id = oft.organization_id
           AND ass.RESOURCE_ID=au.resource_id)
AND NOT EXISTS(SELECT 1 FROM CSM_INV_ORG_FREIGHTS_ACC acc
               WHERE acc.organization_id = oft.organization_id
               AND acc.freight_code=oft.freight_code
               AND NVL(acc.ship_method_code,'X') = NVL(sm.ship_method_code,'X')
			   AND acc.user_id=au.user_id);

Line: 448

SELECT acc.access_id,acc.user_id
FROM CSM_INV_ORG_FREIGHTS_ACC acc,ORG_FREIGHT_TL oft,asg_user au
WHERE acc.organization_id = oft.organization_id
AND acc.freight_code=oft.freight_code
AND au.user_id=acc.user_id
AND oft.language=au.language
AND oft.last_update_date > b_lrd
UNION
SELECT acc.access_id,acc.user_id
FROM CSM_INV_ORG_FREIGHTS_ACC acc
WHERE acc.SHIP_METHOD_CODE  IN (
  SELECT FL.LOOKUP_CODE FROM FND_LOOKUP_VALUES_VL fl
  WHERE FL.LOOKUP_TYPE = 'SHIP_METHOD'
  AND FL.VIEW_APPLICATION_ID = 3
  AND FL.last_update_date > b_lrd);

Line: 467

SELECT acc.access_id,acc.user_id
FROM CSM_INV_ORG_FREIGHTS_ACC acc
WHERE NOT EXISTS (SELECT 1 FROM ORG_FREIGHT_TL oft,CSP_INV_LOC_ASSIGNMENTS ass,asg_user au
                  WHERE acc.organization_id = oft.organization_id
				  AND acc.freight_code=oft.freight_code
				  AND acc.user_id=au.user_id
				  AND nvl(oft.disable_date,sysdate+1) > sysdate
                  AND ass.organization_id = oft.organization_id
				  AND ass.RESOURCE_ID=au.resource_id)
UNION
(SELECT acc.access_id,acc.user_id
FROM CSM_INV_ORG_FREIGHTS_ACC acc
WHERE acc.ship_method_code IS NOT NULL
AND  NOT EXISTS (SELECT 1 FROM WSH_CARRIER_SHIP_METHODS_V sm
                  WHERE acc.organization_id = sm.organization_id
				  AND  acc.ship_method_code=sm.ship_method_code
				  AND acc.freight_code=sm.freight_code
				  AND SM.ENABLED_FLAG='Y'
				  AND sysdate between sm.start_date_active AND nvl(sm.end_date_active, sysdate+1))
UNION ALL
SELECT acc.access_id,acc.user_id
FROM CSM_INV_ORG_FREIGHTS_ACC acc
WHERE acc.ship_method_code IS NULL
AND EXISTS (SELECT 1 FROM WSH_CARRIER_SHIP_METHODS_V sm
            WHERE acc.organization_id = sm.organization_id
			AND acc.freight_code=sm.freight_code
			AND SM.ENABLED_FLAG='Y'
			AND sysdate between sm.start_date_active AND nvl(sm.end_date_active, sysdate+1)));

Line: 499

	 -- get last conc program update date
	 OPEN l_last_run_date_csr;

Line: 501

	 FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 504

  CSM_UTIL_PKG.LOG('Processing deletes', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);

Line: 509

		l_tab_access_id.DELETE;

Line: 510

		l_tab_user_id.DELETE;

Line: 515

		CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from CSM_INTERORG_FREIGHTS_ACC' ,
							 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC',FND_LOG.LEVEL_EVENT);

Line: 527

			DELETE FROM CSM_INV_ORG_FREIGHTS_ACC WHERE access_id = l_tab_access_id(i);

Line: 533

  CSM_UTIL_PKG.LOG('Processing Updates', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);

Line: 536

  OPEN c_inv_org_freights_upd_csr(l_prog_update_date);

Line: 538

		l_tab_access_id.DELETE;

Line: 539

		l_tab_user_id.DELETE;

Line: 544

		CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records in CSM_INTERORG_FREIGHTS_ACC' ,
							 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC',FND_LOG.LEVEL_EVENT);

Line: 558

  CSM_UTIL_PKG.LOG('Processing inserts', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);

Line: 563

		l_tab_access_id.DELETE;

Line: 564

		l_tab_user_id.DELETE;

Line: 565

		l_tab_org.DELETE;

Line: 566

		l_tab_freight.DELETE;

Line: 567

		l_tab_shipM.DELETE;

Line: 573

		CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into CSM_INTERORG_FREIGHTS_ACC' ,
							 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC',FND_LOG.LEVEL_EVENT);

Line: 577

					  INSERT INTO CSM_INV_ORG_FREIGHTS_ACC (access_id, user_id,organization_id, freight_code,ship_method_code,
							   created_by, creation_date, last_updated_by, last_update_date )
					  VALUES (l_tab_access_id(i), l_tab_user_id(i),l_tab_org(i),l_tab_freight(i),l_tab_shipM(i),
							  fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);

Line: 597

  UPDATE jtm_con_request_data
  SET last_run_date = sysdate
  WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
    AND procedure_name = 'REFRESH_FREIGHTS_ACC';