eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSM_CUSTOMER_ACCOUNT_EVENT_PKG

Source


1 PACKAGE BODY CSM_CUSTOMER_ACCOUNT_EVENT_PKG AS
2 /* $Header: csmecatb.pls 120.2.12020000.2 2013/04/09 06:23:47 saradhak ship $ */
3 g_cst_accnt_acc_table_name        CONSTANT VARCHAR2(30) := 'CSM_HZ_CUST_ACCOUNTS_ACC';
4 g_cst_accnt_table_name            CONSTANT VARCHAR2(30) := 'HZ_CUST_ACCOUNTS';
5 g_cst_accnt_seq_name              CONSTANT VARCHAR2(30) := 'CSM_HZ_CUST_ACCOUNTS_ACC_S' ;
6 g_cst_accnt_pk1_name              CONSTANT VARCHAR2(30) := 'CUST_ACCOUNT_ID';
7 g_pub_item               		  CONSTANT VARCHAR2(30) := 'CSM_HZ_CUST_ACCOUNTS';
8 g_accnt_pubi_name 			      CONSTANT CSM_ACC_PKG.t_publication_item_list :=
9   CSM_ACC_PKG.t_publication_item_list('CSM_HZ_CUST_ACCOUNTS');
10 
11 
12 TYPE l_num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13 
14 --download New account sites added
15 PROCEDURE CUST_ACCT_SITES_UPD(p_lrd IN DATE)   --CONC PROGRAM
16 IS
17 
18 CURSOR c_del_cur IS
19 select ACCESS_ID, USER_ID
20 FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC acc
21 WHERE NOT EXISTS(SELECT 1 from hz_cust_acct_sites_all hcas
22 				 WHERE acc.cust_acct_site_id=hcas.cust_acct_site_id
23 				 AND hcas.status='A'
24 				 AND EXISTS(SELECT 1 FROM hz_cust_site_uses_all hcsu
25 				 		    WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
26 						    AND hcsu.site_use_code='SHIP_TO'
27 						    AND hcsu.status='A')
28 				 AND EXISTS(SELECT 1 FROM CSM_PARTY_SITES_ACC ps
29 				            WHERE ps.PARTY_SITE_ID=hcas.party_site_id
30                             AND ps.user_id=acc.user_id));
31 
32 CURSOR c_ins_cur
33  IS
34 select CSM_HZ_CUST_ACCOUNTS_ACC_S.nextval as ACCESS_ID, hcas.cust_acct_site_id,ps.user_id
35 from hz_cust_acct_sites_all hcas,CSM_PARTY_SITES_ACC ps
36 WHERE ps.PARTY_SITE_ID=hcas.party_site_id
37 AND  hcas.status='A'
38 AND EXISTS(SELECT 1 FROM hz_cust_site_uses_all hcsu
39 		   WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
40 		   AND hcsu.site_use_code='SHIP_TO'
41 		   AND hcsu.status='A')
42 AND NOT EXISTS(SELECT 1 FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC acc
43                WHERE acc.cust_acct_site_id=hcas.cust_acct_site_id
44 			   AND acc.user_id=ps.user_id);
45 
46 l_tab_access_id   		ASG_DOWNLOAD.ACCESS_LIST;
47 l_tab_user_id 			ASG_DOWNLOAD.USER_LIST;
48 l_tab_cas_id            l_num_tab_type;
49 l_markdirty             BOOLEAN;
50 l_e VARCHAR2(4000);
51 l_r varchar2(10);
52 BEGIN
53 
54 
55   CSM_UTIL_PKG.LOG('Processing deletes', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD', FND_LOG.LEVEL_PROCEDURE);
56 
57   OPEN c_del_cur;
58   LOOP
59 		l_tab_access_id.DELETE;
60 		l_tab_user_id.DELETE;
61 
62 		FETCH c_del_cur BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
63 		EXIT WHEN l_tab_access_id.COUNT = 0;
64 
65 		CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from CSM_HZ_CUST_ACCT_SITES_ALL_ACC' ,
66 							 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD', FND_LOG.LEVEL_PROCEDURE);
67 
68 		l_markdirty := asg_download.mark_dirty(
69 				  P_PUB_ITEM         => 'CSM_HZ_CUST_ACCT_SITES_ALL'
70 				, p_accessList       => l_tab_access_id
71 				, p_userid_list      => l_tab_user_id
72 				, p_dml_type         => 'D'
73 				, P_TIMESTAMP        => sysdate
74 				);
75 
76 		FORALL i IN 1..l_tab_access_id.COUNT
77 			DELETE FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC WHERE access_id = l_tab_access_id(i);
78 
79 		COMMIT;
80   END LOOP;
81   CLOSE c_del_cur;
82 
83   CSM_UTIL_PKG.LOG('Processing Inserts', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD', FND_LOG.LEVEL_PROCEDURE);
84 
85 OPEN c_ins_cur;
86 LOOP
87 	l_tab_access_id.DELETE;
88 	l_tab_user_id.DELETE;
89 	l_tab_cas_id.DELETE;
90 
91  FETCH c_ins_cur BULK COLLECT INTO l_tab_access_id,l_tab_cas_id,l_tab_user_id LIMIT 1000;
92  EXIT WHEN l_tab_access_id.COUNT=0;
93 
94  FORALL I IN 1..l_tab_access_id.COUNT
95   INSERT INTO CSM_HZ_CUST_ACCT_SITES_ALL_ACC(ACCESS_ID,USER_ID,CUST_ACCT_SITE_ID,
96                                              COUNTER,created_by, creation_date, last_updated_by, last_update_date )
97   VALUES (l_tab_access_id(I),l_tab_user_id(I),l_tab_cas_id(I),
98           1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);
99 
100   l_markdirty := asg_download.mark_dirty(
101 						P_PUB_ITEM         => 'CSM_HZ_CUST_ACCT_SITES_ALL'
102 					  , p_accessList       => l_tab_access_id
103 					  , p_userid_list      => l_tab_user_id
104 					  , p_dml_type         => 'I'
105 					  , P_TIMESTAMP        => sysdate
106 					  );
107 
108   CSM_UTIL_PKG.LOG('Bulk Inserted ' || l_tab_access_id.count || ' records into CSM_HZ_CUST_ACCT_SITES_ALL_ACC' ,
109 							 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD',FND_LOG.LEVEL_PROCEDURE);
110   COMMIT;
111 END LOOP;
112 CLOSE c_ins_cur;
113 
114 CSM_UTIL_PKG.LOG('Leaving CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_UPD',FND_LOG.LEVEL_PROCEDURE);
115 
116 END CUST_ACCT_SITES_UPD;
117 
118 --insert cust acct sites of passed CUST_ACCOUNT_ID
119 PROCEDURE CUST_ACCT_SITES_INS(p_party_site_id IN NUMBER,p_org_id IN NUMBER,p_user_id IN NUMBER)
120 IS
121 l_markdirty             BOOLEAN;
122 
123 type r_cursor is REF CURSOR;
124 c_ins_cur r_cursor;
125 
126 l_tab_access_id   		ASG_DOWNLOAD.ACCESS_LIST;
127 l_tab_user_id 			ASG_DOWNLOAD.USER_LIST;
128 l_tab_cas_id            l_num_tab_type;
129 l_e VARCHAR2(4000);
130 l_r varchar2(10);
131 BEGIN
132 
133 CSM_UTIL_PKG.LOG('Entering CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS',FND_LOG.LEVEL_PROCEDURE);
134 
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
136 						from  hz_cust_acct_sites_all hcas
137 						where hcas.party_site_id = p_party_site_id
138 						AND hcas.status='A'  --download only active ones
139 						And ORG_ID=p_org_id
140 						AND EXISTS(SELECT 1 FROM hz_cust_site_uses_all hcsu
141 								   WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
142 								   AND hcsu.site_use_code='SHIP_TO'
143 								   AND hcsu.status='A')
144 						AND NOT EXISTS(SELECT 1 FROM CSM_HZ_CUST_ACCT_SITES_ALL_ACC acc
145 									   WHERE acc.cust_acct_site_id=hcas.cust_acct_site_id
146 									   AND acc.user_id=p_user_id);
147 
148 
149 	FETCH c_ins_cur BULK COLLECT INTO l_tab_access_id,l_tab_user_id,l_tab_cas_id;
150 
151 	FORALL I IN 1..l_tab_access_id.COUNT
152 	  INSERT INTO CSM_HZ_CUST_ACCT_SITES_ALL_ACC(ACCESS_ID,USER_ID,CUST_ACCT_SITE_ID,
153 												 COUNTER,created_by, creation_date, last_updated_by, last_update_date )
154 	  VALUES (l_tab_access_id(I),l_tab_user_id(I),l_tab_cas_id(I),
155 			  1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);
156 
157 	IF l_tab_access_id.COUNT > 0 THEN
158 		l_markdirty := asg_download.mark_dirty(
159 							P_PUB_ITEM         => 'CSM_HZ_CUST_ACCT_SITES_ALL'
160 						  , p_accessList       => l_tab_access_id
161 						  , p_userid_list      => l_tab_user_id
162 						  , p_dml_type         => 'I'
163 						  , P_TIMESTAMP        => sysdate
164 						  );
165 	END IF;
166 
167     CLOSE c_ins_cur;
168 
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);
170 
171 END CUST_ACCT_SITES_INS;
172 
173 --PROCEDURE IMPLEMENTATION DETAILS
174 --This procedure will insert account id into the access table only for the party
175 --which is passed to this procedure and which is not already present in the CUST ACcount Access table
176 --Here the counter value for the ucstomer accoutns wont get increased even thought multiple instance of
177 --the party is present in the party Access table
178 PROCEDURE CUST_ACCOUNTS_INS (p_party_id NUMBER , p_user_id NUMBER)
179 IS
180 --CURSOR declarations
181 CURSOR  c_cust_accnt_ins(c_party_id NUMBER , c_user_id NUMBER)
182 IS
183 SELECT 	hzc.CUST_ACCOUNT_ID
184 FROM 	HZ_CUST_ACCOUNTS hzc
185 WHERE 	hzc.PARTY_ID = c_party_id
186 AND NOT EXISTS
187 	(
188 	SELECT 	1
189 	FROM 	CSM_HZ_CUST_ACCOUNTS_ACC acc
190 	WHERE 	acc.USER_ID = c_user_id
191 	AND		acc.CUST_ACCOUNT_ID = hzc.CUST_ACCOUNT_ID
192 	);
193 
194 --variable declarations
195 l_sqlerrno 		VARCHAR2(20);
196 l_sqlerrmsg 	VARCHAR2(2000);
197 p_message		VARCHAR2(3000);
198 
199 BEGIN
200 
201     CSM_UTIL_PKG.LOG('Entering CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS',FND_LOG.LEVEL_EXCEPTION);
202 
203 
204 	FOR	l_cust_accnt_rec IN c_cust_accnt_ins(p_party_id,p_user_id)
205 	LOOP
206    		CSM_ACC_PKG.Insert_Acc
207     	(P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
208      	,P_ACC_TABLE_NAME         => g_cst_accnt_acc_table_name
209      	,P_SEQ_NAME               => g_cst_accnt_seq_name
210      	,P_PK1_NAME               => g_cst_accnt_pk1_name
211      	,P_PK1_NUM_VALUE          => l_cust_accnt_rec.CUST_ACCOUNT_ID
212      	,P_USER_ID                => p_user_id
213     	);
214 	END LOOP;
215 
216     CSM_UTIL_PKG.LOG('Leaving CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS',FND_LOG.LEVEL_EXCEPTION);
217 
218  EXCEPTION
219   WHEN others THEN
220      l_sqlerrno  := to_char(SQLCODE);
221      l_sqlerrmsg := substr(SQLERRM, 1,2000);
222      p_message   := 'Exception in CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS Procedure :' || l_sqlerrno || ':' || l_sqlerrmsg;
223      CSM_UTIL_PKG.LOG(p_message, 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS',FND_LOG.LEVEL_EXCEPTION);
224      RAISE;
225 
226 END CUST_ACCOUNTS_INS;
227 --update cannot be logically called from anywhere...
228 --but right now its planned to call from  JTM LOOKUP PROGRAM
229 
230 PROCEDURE CUST_ACCOUNTS_UPD (p_status OUT NOCOPY VARCHAR2,
231                        p_message OUT NOCOPY VARCHAR2)
232 IS
233 PRAGMA AUTONOMOUS_TRANSACTION;
234 --CURSOR declarations
235 CURSOR  c_cust_accnt_upd(c_last_run_date DATE)
236 IS
237 SELECT 	acc.access_id,acc.user_id
238 FROM 	HZ_CUST_ACCOUNTS hzc , CSM_HZ_CUST_ACCOUNTS_ACC acc
239 WHERE 	hzc.cust_account_id = acc.cust_account_id
240 AND 	hzc.LAST_UPDATE_DATE > c_last_run_date;
241 
242 --cursor to get last run date from jtm_con_request_data
243 CURSOR csr_last_run_date
244 IS
245 SELECT 	nvl(last_run_date, (sysdate - 365*50) )
246 FROM 	jtm_con_request_data
247 WHERE 	package_name 	= 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG'
248 AND 	procedure_name 	= 'CUST_ACCOUNTS_UPD';
249 
250 --variable declarations
251 l_cst_account_id 	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
252 l_sqlerrno 		varchar2(20);
253 l_sqlerrmsg 	varchar2(2000);
254 l_markdirty		BOOLEAN;
255 l_accessid_lst 	asg_download.access_list;
256 l_userid_lst 	asg_download.user_list;
257 l_last_run_date JTM_CON_REQUEST_DATA.LAST_RUN_DATE%TYPE;
258 
259 BEGIN
260     CSM_UTIL_PKG.LOG('Entering CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD',FND_LOG.LEVEL_EXCEPTION);
261 
262 	OPEN 	csr_last_run_date;
263 	FETCH	csr_last_run_date INTO l_last_run_date;
264 	CLOSE	csr_last_run_date;
265 
266 	OPEN c_cust_accnt_upd(l_last_run_date);
267 	LOOP
268 	    l_userid_lst.DELETE;
269 		l_accessid_lst.DELETE;
270 
271 		FETCH c_cust_accnt_upd BULK COLLECT INTO l_accessid_lst,l_userid_lst LIMIT 1000;
272 		EXIT WHEN l_accessid_lst.COUNT=0;
273 
274    		l_markdirty := asg_download.mark_dirty( g_pub_item,l_accessid_lst ,l_userid_lst, 'U', sysdate );
275 
276 		COMMIT;
277 	END LOOP;
278 
279 	FOR rec in (SELECT DISTINCT PARTY_ID,USER_ID    --any new accounts added ?
280 	            FROM HZ_CUST_ACCOUNTS hzc , CSM_HZ_CUST_ACCOUNTS_ACC acc
281                 WHERE hzc.cust_account_id = acc.cust_account_id
282                 AND EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS hzc2
283                             WHERE 	hzc2.PARTY_ID = hzc.PARTY_ID
284                             AND NOT EXISTS (SELECT  1 FROM 	CSM_HZ_CUST_ACCOUNTS_ACC acc2
285 	                                        WHERE 	acc2.USER_ID = acc.USER_ID
286 	                                        AND	acc2.CUST_ACCOUNT_ID = hzc2.CUST_ACCOUNT_ID )))
287 	LOOP
288 	  CUST_ACCOUNTS_INS(rec.PARTY_ID,rec.USER_ID);
289 	END LOOP;
290 
291 	CUST_ACCT_SITES_UPD(l_last_run_date);
292 
293 
294 	UPDATE 	jtm_con_request_data
295 	SET 	last_run_date   = sysdate
296 	WHERE 	package_name 	= 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG'
297 	AND 	procedure_name 	= 'CUST_ACCOUNTS_UPD';
298 
299 	COMMIT;
300 	CLOSE c_cust_accnt_upd;
301  	p_status := 'FINE';
302 	p_message :=  'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD Executed successfully';
303     CSM_UTIL_PKG.LOG('Leaving CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD',FND_LOG.LEVEL_EXCEPTION);
304 
305  EXCEPTION
306   WHEN others THEN
307      l_sqlerrno  := to_char(SQLCODE);
308      l_sqlerrmsg := substr(SQLERRM, 1,2000);
309      p_status 	 := 'ERROR';
310      p_message   := 'Exception in CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD Procedure :' || l_sqlerrno || ':' || l_sqlerrmsg;
311      CSM_UTIL_PKG.LOG(p_message, 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD',FND_LOG.LEVEL_EXCEPTION);
312      ROLLBACK;
313 
314 END CUST_ACCOUNTS_UPD;
315 
316 --PROCEDURE IMPLEMENTATION DETAILS
317 --While deleting the records from access table we are not considering the counter value
318 --in the access table this is because we do delete only for the accounts which
319 --doesnot have the corresponding party_id in the CSM_PARTIES_ACC Table.
320 --ie.The Delete depends only the pary acccess table.This delete procedure is called
321 --whenever the pary delete is called
322 PROCEDURE CUST_ACCOUNTS_DEL (p_party_id NUMBER , p_user_id NUMBER)
323 IS
324 --CURSOR declarations
325 CURSOR c_cust_accnt_del(c_party_id NUMBER , c_user_id NUMBER)
326 IS
327 SELECT 	acc.CUST_ACCOUNT_ID,acc.access_id
328 FROM 	CSM_HZ_CUST_ACCOUNTS_ACC acc
329 WHERE 	acc.USER_ID = c_user_id
330 AND NOT EXISTS
331 	(
332 	SELECT 	1
333 	FROM 	CSM_PARTIES_ACC acc
334 	WHERE 	acc.USER_ID  = c_user_id
335 	AND		acc.party_ID = c_party_id
336 	);
337 
338 --variable declarations
339 l_cst_account_id 	HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
340 l_sqlerrno 		varchar2(20);
341 l_sqlerrmsg 	varchar2(2000);
342 p_message		VARCHAR(3000);
343 l_access_id		CSM_HZ_CUST_ACCOUNTS_ACC.ACCESS_ID%TYPE;
344 l_markdirty		BOOLEAN;
345 BEGIN
346 
347     CSM_UTIL_PKG.LOG('Entering CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL',FND_LOG.LEVEL_EXCEPTION);
348 
349 	--take all teh cust_account_id for the party which doesnot exists for the user_id
350 	FOR	l_cust_accnt_rec IN c_cust_accnt_del(p_party_id,p_user_id)
351 	LOOP
352 		l_access_id := l_cust_accnt_rec.access_id;
353    		CSM_ACC_PKG.Delete_Acc
354     	(P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
355      	,P_ACC_TABLE_NAME         => g_cst_accnt_acc_table_name
356      	,P_PK1_NAME               => g_cst_accnt_pk1_name
357      	,P_PK1_NUM_VALUE          => l_cust_accnt_rec.CUST_ACCOUNT_ID
358      	,P_USER_ID                => p_user_id
359     	);
360 
361 	END LOOP;
362 
363     CSM_UTIL_PKG.LOG('Leaving CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL Package ', 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL',FND_LOG.LEVEL_EXCEPTION);
364 
365  EXCEPTION
366   WHEN others THEN
367      l_sqlerrno  := to_char(SQLCODE);
368      l_sqlerrmsg := substr(SQLERRM, 1,2000);
369      p_message   := 'Exception in CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL Procedure : for accessid ' || l_access_id ||': with error' || l_sqlerrno || ':' || l_sqlerrmsg;
370      CSM_UTIL_PKG.LOG(p_message, 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL',FND_LOG.LEVEL_EXCEPTION);
371      RAISE;
372 
373 END CUST_ACCOUNTS_DEL;
374 
375 END CSM_CUSTOMER_ACCOUNT_EVENT_PKG;