[Home] [Help]
PACKAGE BODY: APPS.CSM_CUSTOMER_ACCOUNT_EVENT_PKG
Source
1 PACKAGE BODY CSM_CUSTOMER_ACCOUNT_EVENT_PKG AS
2 /* $Header: csmecatb.pls 120.2 2006/07/27 11:05:40 trajasek noship $ */
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 --PROCEDURE IMPLEMENTATION DETAILS
12 --This procedure will insert account id into the access table only for the party
13 --which is passed to this procedure and which is not already present in the CUST ACcount Access table
14 --Here the counter value for the ucstomer accoutns wont get increased even thought multiple instance of
15 --the party is present in the party Access table
16 PROCEDURE CUST_ACCOUNTS_INS (p_party_id NUMBER , p_user_id NUMBER)
17 IS
18 --CURSOR declarations
19 CURSOR c_cust_accnt_ins(c_party_id NUMBER , c_user_id NUMBER)
20 IS
21 SELECT hzc.CUST_ACCOUNT_ID
22 FROM HZ_CUST_ACCOUNTS hzc
23 WHERE hzc.PARTY_ID = c_party_id
24 AND NOT EXISTS
25 (
26 SELECT 1
27 FROM CSM_HZ_CUST_ACCOUNTS_ACC acc
28 WHERE acc.USER_ID = c_user_id
29 AND acc.CUST_ACCOUNT_ID = hzc.CUST_ACCOUNT_ID
30 );
31
32 --variable declarations
33 l_sqlerrno VARCHAR2(20);
34 l_sqlerrmsg VARCHAR2(2000);
35 p_message VARCHAR2(3000);
36
37 BEGIN
38
39 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);
40
41
42 FOR l_cust_accnt_rec IN c_cust_accnt_ins(p_party_id,p_user_id)
43 LOOP
44
45 CSM_ACC_PKG.Insert_Acc
46 (P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
47 ,P_ACC_TABLE_NAME => g_cst_accnt_acc_table_name
48 ,P_SEQ_NAME => g_cst_accnt_seq_name
49 ,P_PK1_NAME => g_cst_accnt_pk1_name
50 ,P_PK1_NUM_VALUE => l_cust_accnt_rec.CUST_ACCOUNT_ID
51 ,P_USER_ID => p_user_id
52 );
53
54 END LOOP;
55
56 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);
57
58 EXCEPTION
59 WHEN others THEN
60 l_sqlerrno := to_char(SQLCODE);
61 l_sqlerrmsg := substr(SQLERRM, 1,2000);
62 p_message := 'Exception in CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS Procedure :' || l_sqlerrno || ':' || l_sqlerrmsg;
63 CSM_UTIL_PKG.LOG(p_message, 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS',FND_LOG.LEVEL_EXCEPTION);
64 RAISE;
65
66 END CUST_ACCOUNTS_INS;
67 --update cannot be logically called from anywhere...
68 --but right now its planned to call from JTM LOOKUP PROGRAM
69
70 PROCEDURE CUST_ACCOUNTS_UPD (p_status OUT NOCOPY VARCHAR2,
71 p_message OUT NOCOPY VARCHAR2)
72 IS
73 PRAGMA AUTONOMOUS_TRANSACTION;
74 --CURSOR declarations
75 CURSOR c_cust_accnt_upd(c_last_run_date DATE)
76 IS
77 SELECT acc.access_id,acc.user_id
78 FROM HZ_CUST_ACCOUNTS hzc , CSM_HZ_CUST_ACCOUNTS_ACC acc
79 WHERE hzc.cust_account_id = acc.cust_account_id
80 AND hzc.LAST_UPDATE_DATE > c_last_run_date;
81
82 --cursor to get last run date from jtm_con_request_data
83 CURSOR csr_last_run_date
84 IS
85 SELECT nvl(last_run_date, (sysdate - 365*50) )
86 FROM jtm_con_request_data
87 WHERE package_name = 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG'
88 AND procedure_name = 'CUST_ACCOUNTS_UPD';
89
90 --variable declarations
91 l_cst_account_id HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
92 l_sqlerrno varchar2(20);
93 l_sqlerrmsg varchar2(2000);
94 l_markdirty BOOLEAN;
95 l_accessid_lst asg_download.access_list;
96 l_userid_lst asg_download.user_list;
97 l_last_run_date JTM_CON_REQUEST_DATA.LAST_RUN_DATE%TYPE;
98
99 BEGIN
100 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);
101
102 OPEN csr_last_run_date;
103 FETCH csr_last_run_date INTO l_last_run_date;
104 CLOSE csr_last_run_date;
105
106 OPEN c_cust_accnt_upd(l_last_run_date);
107 LOOP
108
109 IF l_userid_lst.COUNT > 0 THEN
110 l_userid_lst.DELETE;
111 END IF;
112 IF l_accessid_lst.COUNT > 0 THEN
113 l_accessid_lst.DELETE;
114 END IF;
115
116 FETCH c_cust_accnt_upd BULK COLLECT INTO l_accessid_lst,l_userid_lst LIMIT 50;
117 EXIT WHEN l_accessid_lst.COUNT=0;
118
119 l_markdirty := asg_download.mark_dirty( g_pub_item,l_accessid_lst
120 ,l_userid_lst, 'U', sysdate );
121
122 COMMIT;
123 END LOOP;
124
125 UPDATE jtm_con_request_data
126 SET last_run_date = sysdate
127 WHERE package_name = 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG'
128 AND procedure_name = 'CUST_ACCOUNTS_UPD';
129
130 COMMIT;
131 CLOSE c_cust_accnt_upd;
132 p_status := 'FINE';
133 p_message := 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD Executed successfully';
134 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);
135
136 EXCEPTION
137 WHEN others THEN
138 l_sqlerrno := to_char(SQLCODE);
139 l_sqlerrmsg := substr(SQLERRM, 1,2000);
140 p_status := 'ERROR';
141 p_message := 'Exception in CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD Procedure :' || l_sqlerrno || ':' || l_sqlerrmsg;
142 CSM_UTIL_PKG.LOG(p_message, 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_UPD',FND_LOG.LEVEL_EXCEPTION);
143 ROLLBACK;
144
145 END CUST_ACCOUNTS_UPD;
146
147 --PROCEDURE IMPLEMENTATION DETAILS
148 --While deleting the records from access table we are not considering the counter value
149 --in the access table this is because we do delete only for the accounts which
150 --doesnot have the corresponding party_id in the CSM_PARTIES_ACC Table.
151 --ie.The Delete depends only the pary acccess table.This delete procedure is called
152 --whenever the pary delete is called
153 PROCEDURE CUST_ACCOUNTS_DEL (p_party_id NUMBER , p_user_id NUMBER)
154 IS
155 --CURSOR declarations
156 CURSOR c_cust_accnt_del(c_party_id NUMBER , c_user_id NUMBER)
157 IS
158 SELECT acc.CUST_ACCOUNT_ID,acc.access_id
159 FROM CSM_HZ_CUST_ACCOUNTS_ACC acc
160 WHERE acc.USER_ID = c_user_id
161 AND NOT EXISTS
162 (
163 SELECT 1
164 FROM CSM_PARTIES_ACC acc
165 WHERE acc.USER_ID = c_user_id
166 AND acc.party_ID = c_party_id
167 );
168
169 --variable declarations
170 l_cst_account_id HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
171 l_sqlerrno varchar2(20);
172 l_sqlerrmsg varchar2(2000);
173 p_message VARCHAR(3000);
174 l_access_id CSM_HZ_CUST_ACCOUNTS_ACC.ACCESS_ID%TYPE;
175 l_markdirty BOOLEAN;
176 BEGIN
177
178 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);
179
180 --take all teh cust_account_id for the party which doesnot exists for the user_id
181 FOR l_cust_accnt_rec IN c_cust_accnt_del(p_party_id,p_user_id)
182 LOOP
183 l_access_id := l_cust_accnt_rec.access_id;
184 CSM_ACC_PKG.Delete_Acc
185 (P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
186 ,P_ACC_TABLE_NAME => g_cst_accnt_acc_table_name
187 ,P_PK1_NAME => g_cst_accnt_pk1_name
188 ,P_PK1_NUM_VALUE => l_cust_accnt_rec.CUST_ACCOUNT_ID
189 ,P_USER_ID => p_user_id
190 );
191
192 END LOOP;
193
194 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);
195
196 EXCEPTION
197 WHEN others THEN
198 l_sqlerrno := to_char(SQLCODE);
199 l_sqlerrmsg := substr(SQLERRM, 1,2000);
200 p_message := 'Exception in CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL Procedure : for accessid ' || l_access_id ||': with error' || l_sqlerrno || ':' || l_sqlerrmsg;
201 CSM_UTIL_PKG.LOG(p_message, 'CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL',FND_LOG.LEVEL_EXCEPTION);
202 RAISE;
203
204 END CUST_ACCOUNTS_DEL;
205
206 END CSM_CUSTOMER_ACCOUNT_EVENT_PKG;