APPS.CSM_CUSTOMER_ACCOUNT_EVENT_PKG SQL Statements

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

Line: 19

select ACCESS_ID, USER_ID
FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC acc
WHERE NOT EXISTS(SELECT 1 from hz_cust_acct_sites_all hcas
				 WHERE acc.cust_acct_site_id=hcas.cust_acct_site_id
				 AND hcas.status='A'
				 AND EXISTS(SELECT 1 FROM hz_cust_site_uses_all hcsu
				 		    WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
						    AND hcsu.site_use_code='SHIP_TO'
						    AND hcsu.status='A')
				 AND EXISTS(SELECT 1 FROM CSM_PARTY_SITES_ACC ps
				            WHERE ps.PARTY_SITE_ID=hcas.party_site_id
                            AND ps.user_id=acc.user_id));

Line: 34

select CSM_HZ_CUST_ACCOUNTS_ACC_S.nextval as ACCESS_ID, hcas.cust_acct_site_id,ps.user_id
from hz_cust_acct_sites_all hcas,CSM_PARTY_SITES_ACC ps
WHERE ps.PARTY_SITE_ID=hcas.party_site_id
AND  hcas.status='A'
AND EXISTS(SELECT 1 FROM hz_cust_site_uses_all hcsu
		   WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
		   AND hcsu.site_use_code='SHIP_TO'
		   AND hcsu.status='A')
AND NOT EXISTS(SELECT 1 FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC acc
               WHERE acc.cust_acct_site_id=hcas.cust_acct_site_id
			   AND acc.user_id=ps.user_id);

Line: 55

  CSM_UTIL_PKG.LOG('Processing deletes', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD', FND_LOG.LEVEL_PROCEDURE);

Line: 59

		l_tab_access_id.DELETE;

Line: 60

		l_tab_user_id.DELETE;

Line: 65

		CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from CSM_HZ_CUST_ACCT_SITES_ALL_ACC' ,
							 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD', FND_LOG.LEVEL_PROCEDURE);

Line: 77

			DELETE FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC WHERE access_id = l_tab_access_id(i);

Line: 83

  CSM_UTIL_PKG.LOG('Processing Inserts', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD', FND_LOG.LEVEL_PROCEDURE);

Line: 87

	l_tab_access_id.DELETE;

Line: 88

	l_tab_user_id.DELETE;

Line: 89

	l_tab_cas_id.DELETE;

Line: 95

  INSERT INTO CSM_HZ_CUST_ACCT_SITES_ALL_ACC(ACCESS_ID,USER_ID,CUST_ACCT_SITE_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_cas_id(I),
          1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);

Line: 108

  CSM_UTIL_PKG.LOG('Bulk Inserted ' || l_tab_access_id.count || ' records into CSM_HZ_CUST_ACCT_SITES_ALL_ACC' ,
							 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD',FND_LOG.LEVEL_PROCEDURE);

Line: 135

	OPEN c_ins_cur FOR select CSM_HZ_CUST_ACCOUNTS_ACC_S.nextval as ACCESS_ID, p_user_id,hcas.cust_acct_site_id
						from  hz_cust_acct_sites_all hcas
						where hcas.party_site_id = p_party_site_id
						AND hcas.status='A'  --download only active ones
						And ORG_ID=p_org_id
						AND EXISTS(SELECT 1 FROM hz_cust_site_uses_all hcsu
								   WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
								   AND hcsu.site_use_code='SHIP_TO'
								   AND hcsu.status='A')
						AND NOT EXISTS(SELECT 1 FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC acc
									   WHERE acc.cust_acct_site_id=hcas.cust_acct_site_id
									   AND acc.user_id=p_user_id);

Line: 152

	  INSERT INTO CSM_HZ_CUST_ACCT_SITES_ALL_ACC(ACCESS_ID,USER_ID,CUST_ACCT_SITE_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_cas_id(I),
			  1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);

Line: 169

CSM_UTIL_PKG.LOG('Leaving after insert of '||l_tab_access_id.COUNT||' records', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS',FND_LOG.LEVEL_PROCEDURE);

Line: 183

SELECT 	hzc.CUST_ACCOUNT_ID
FROM 	HZ_CUST_ACCOUNTS hzc
WHERE 	hzc.PARTY_ID = c_party_id
AND NOT EXISTS
	(
	SELECT 	1
	FROM 	CSM_HZ_CUST_ACCOUNTS_ACC acc
	WHERE 	acc.USER_ID = c_user_id
	AND		acc.CUST_ACCOUNT_ID = hzc.CUST_ACCOUNT_ID
	);

Line: 206

   		CSM_ACC_PKG.Insert_Acc
    	(P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
     	,P_ACC_TABLE_NAME         => g_cst_accnt_acc_table_name
     	,P_SEQ_NAME               => g_cst_accnt_seq_name
     	,P_PK1_NAME               => g_cst_accnt_pk1_name
     	,P_PK1_NUM_VALUE          => l_cust_accnt_rec.CUST_ACCOUNT_ID
     	,P_USER_ID                => p_user_id
    	);

Line: 237

SELECT 	acc.access_id,acc.user_id
FROM 	HZ_CUST_ACCOUNTS hzc , CSM_HZ_CUST_ACCOUNTS_ACC acc
WHERE 	hzc.cust_account_id = acc.cust_account_id
AND 	hzc.LAST_UPDATE_DATE > c_last_run_date;

Line: 245

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

Line: 268

	    l_userid_lst.DELETE;

Line: 269

		l_accessid_lst.DELETE;

Line: 279

	FOR rec in (SELECT DISTINCT PARTY_ID,USER_ID    --any new accounts added ?
	            FROM HZ_CUST_ACCOUNTS hzc , CSM_HZ_CUST_ACCOUNTS_ACC acc
                WHERE hzc.cust_account_id = acc.cust_account_id
                AND EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS hzc2
                            WHERE 	hzc2.PARTY_ID = hzc.PARTY_ID
                            AND NOT EXISTS (SELECT  1 FROM 	CSM_HZ_CUST_ACCOUNTS_ACC acc2
	                                        WHERE 	acc2.USER_ID = acc.USER_ID
	                                        AND	acc2.CUST_ACCOUNT_ID = hzc2.CUST_ACCOUNT_ID )))
	LOOP
	  CUST_ACCOUNTS_INS(rec.PARTY_ID,rec.USER_ID);

Line: 294

	UPDATE 	jtm_con_request_data
	SET 	last_run_date   = sysdate
	WHERE 	package_name 	= 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG'
	AND 	procedure_name 	= 'CUST_ACCOUNTS_UPD';

Line: 327

SELECT 	acc.CUST_ACCOUNT_ID,acc.access_id
FROM 	CSM_HZ_CUST_ACCOUNTS_ACC acc
WHERE 	acc.USER_ID = c_user_id
AND NOT EXISTS
	(
	SELECT 	1
	FROM 	CSM_PARTIES_ACC acc
	WHERE 	acc.USER_ID  = c_user_id
	AND		acc.party_ID = c_party_id
	);

Line: 353

   		CSM_ACC_PKG.Delete_Acc
    	(P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
     	,P_ACC_TABLE_NAME         => g_cst_accnt_acc_table_name
     	,P_PK1_NAME               => g_cst_accnt_pk1_name
     	,P_PK1_NUM_VALUE          => l_cust_accnt_rec.CUST_ACCOUNT_ID
     	,P_USER_ID                => p_user_id
    	);