1 PACKAGE BODY CSM_TXN_BILL_TYPES_EVENT_PKG AS
2 /* $Header: csmetbtb.pls 120.1 2005/07/25 00:26:50 trajasek noship $ */
3 g_all_palm_res_list asg_download.user_list;
4
5 /**
6 Refreshes the CSM_BUS_PROCESS_TXNS_ACC table by comparing with the
7 backend table for deletes, updates and inserts.
8 Refreshes for all the users
9 Also adds corresponding entries in to SDQ
10
11 MODIFICATION HOSTORY:
12 10/06/02 ANURAG added check before inserting into csm_txn_billing_types_acc
13 for already existing primary key value
14 */
15
16 procedure Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
17 p_message OUT NOCOPY VARCHAR2)
18 IS
19 PRAGMA AUTONOMOUS_TRANSACTION;
20 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
21 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
22 l_dsql varchar2(2048);
23 l_pub_item varchar2(30) := 'CSF_M_TXN_BILLING_TYPES';
24 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
25 l_mark_dirty boolean;
26 l_access_id CSM_BUS_PROCESS_TXNS_ACC.ACCESS_ID%TYPE;
27 l_language cs_transaction_types_tl.language%TYPE;
28 l_business_process_id CS_BUS_PROCESS_TXNS.BUSINESS_PROCESS_ID%TYPE;
29 l_txn_billing_type_id cs_txn_billing_types.txn_billing_type_id%TYPE;
30 l_transaction_type_id cs_txn_billing_types.transaction_type_id%TYPE;
31 l_tl_omfs_palm_resource_list asg_download.user_list;
32 l_single_access_id_list asg_download.access_list;
33 --a null list
34 l_null_access_list asg_download.access_list;
35 l_null_resource_list asg_download.user_list;
36 l_run_date DATE;
37 l_sqlerrno varchar2(20);
38 l_sqlerrmsg varchar2(2000);
39
40 CURSOR l_last_run_date_csr
41 IS
42 SELECT nvl(last_run_date, (sysdate - 365*50))
43 FROM jtm_con_request_data
44 WHERE package_name = 'CSM_TXN_BILL_TYPES_EVENT_PKG'
45 AND procedure_name = 'REFRESH_ACC';
46
47 CURSOR l_deletes_cur
48 IS
49 SELECT acc.access_id
50 FROM CSM_TXN_BILLING_TYPES_ACC acc
51 WHERE NOT EXISTS
52 (select tbt.TXN_BILLING_TYPE_ID
53 from cs_txn_billing_types tbt,
54 cs_transaction_types_b ttb,
55 cs_business_processes bpr,
56 cs_bus_process_txns bpt,
57 CS_BILLING_TYPE_CATEGORIES cbtc
58 where acc.txn_billing_type_id = tbt.txn_billing_type_id
59 AND acc.business_process_id = bpt.business_process_id
60 AND tbt.transaction_type_id = ttb.transaction_type_id
61 AND SYSDATE BETWEEN nvl(tbt.start_date_active, SYSDATE) AND nvl(tbt.end_date_active, SYSDATE)
62 AND SYSDATE BETWEEN nvl(ttb.start_date_active, SYSDATE) AND nvl(ttb.end_date_active, SYSDATE)
63 AND SYSDATE BETWEEN nvl(bpr.start_date_active, SYSDATE) AND nvl(bpr.end_date_active, SYSDATE)
64 AND SYSDATE BETWEEN nvl(bpt.start_date_active, SYSDATE) AND nvl(bpt.end_date_active, SYSDATE)
65 AND tbt.billing_type = cbtc.billing_type
66 AND cbtc.billing_category IN ( 'L', 'E', 'M' )
67 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
68 AND bpt.transaction_type_id = tbt.transaction_type_id
69 AND bpr.business_process_id = bpt.business_process_id
70 AND bpr.field_service_flag = 'Y'
71 );
72
73 l_temp_date date;
74
75 CURSOR l_txn_types_tl_cur (p_transaction_type_id IN cs_transaction_types_b.transaction_type_id%TYPE)
76 IS
77 SELECT tt_tl.LANGUAGE
78 FROM cs_transaction_types_tl tt_tl
79 WHERE tt_tl.transaction_type_id = p_transaction_type_id;
80
81 cursor record_exists_csr( p_txn_billing_type_id csm_txn_billing_types_acc.txn_billing_type_id%TYPE,
82 p_business_process_id csm_txn_billing_types_acc.business_process_id%TYPE)
83 is
84 select 1
85 from csm_txn_billing_types_acc
86 where txn_billing_type_id = p_txn_billing_type_id
87 and business_process_id = p_business_process_id;
88 l_dummy number;
89
90 BEGIN
91 -- get last conc program update date
92 OPEN l_last_run_date_csr;
93 FETCH l_last_run_date_csr INTO l_prog_update_date;
94 CLOSE l_last_run_date_csr;
95
96 -- conc program run date
97 l_run_date := SYSDATE;
98
99 --get all the OMFS Palm users
100 g_all_palm_res_list := CSM_UTIL_PKG.get_all_omfs_palm_res_list;
101
102 /****** DELETES **********/
103 --open the cursor
104 open l_deletes_cur;
105 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
106 LOOP
107 FETCH l_deletes_cur INTO l_access_id;
108 EXIT WHEN l_deletes_cur%NOTFOUND;
109
110 --mark dirty the SDQ for all users
111 FOR i IN 1 .. g_all_palm_res_list.COUNT LOOP
112 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
113 l_access_id,
114 g_all_palm_res_list(i),
115 ASG_DOWNLOAD.DEL,
116 SYSDATE);
117 END LOOP;
118
119 --remove from ACC
120 DELETE FROM CSM_TXN_BILLING_TYPES_ACC
121 WHERE ACCESS_ID = l_access_id;
122
123 END LOOP;
124 --close the cursor
125 close l_deletes_cur;
126
127 /******* UPDATES **********/
128 --generate sql for updates
129 l_dsql := 'select acc.access_id, tt_tl.language
130 from csm_txn_billing_types_acc acc,
131 cs_txn_billing_types tbt,
132 cs_transaction_types_b ttb,
133 cs_transaction_types_tl tt_tl,
134 cs_business_processes bpr,
135 cs_bus_process_txns bpt,
136 CS_BILLING_TYPE_CATEGORIES cbtc
137 where acc.txn_billing_type_id = tbt.txn_billing_type_id
138 AND acc.business_process_id = bpt.business_process_id
139 AND tbt.transaction_type_id = ttb.transaction_type_id
140 AND tbt.billing_type = cbtc.billing_type
141 AND cbtc.billing_category IN ( ''L'', ''E'', ''M'' )
142 AND tt_tl.transaction_type_id = tbt.transaction_type_id
143 AND bpt.transaction_type_id = tbt.transaction_type_id
144 AND SYSDATE BETWEEN nvl(bpt.start_date_active, SYSDATE) AND nvl(bpt.end_date_active, SYSDATE)
145 AND bpr.business_process_id = bpt.business_process_id
146 AND SYSDATE BETWEEN nvl(bpr.start_date_active, SYSDATE) AND nvl(bpr.end_date_active, SYSDATE)
147 AND bpr.field_service_flag = ''Y''
148 AND (tbt.last_update_date > :1
149 or ttb.last_update_date > :2
150 or tt_tl.last_update_date > :3
151 or bpr.last_update_date > :4
152 or bpt.last_update_date > :5
153 or cbtc.last_update_date > :6
154 )';
155
156
157 --open the cursor
158 open l_updates_cur for l_dsql USING l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date;
159 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
160 LOOP
161 FETCH l_updates_cur INTO l_access_id, l_language;
162 EXIT WHEN l_updates_cur%NOTFOUND;
163
164 --get the users with this language
165 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(l_language);
166
167 --nullify the access list
168 l_single_access_id_list := l_null_access_list;
169 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
170 l_single_access_id_list(i) := l_access_id;
171 END LOOP;
172
173 --mark dirty the SDQ for all users
174 IF l_single_access_id_list.count > 0 then
175 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
176 l_single_access_id_list, l_tl_omfs_palm_resource_list,
177 ASG_DOWNLOAD.UPD, sysdate);
178 END IF;
179
180 --update ACC
181 UPDATE csm_txn_billing_types_acc
182 SET LAST_UPDATE_DATE = l_run_date
183 WHERE ACCESS_ID = l_access_id;
184
185 END LOOP;
186
187 --close the cursor
188 close l_updates_cur;
189
190 /****** INSERTS **********/
191 --generate sql for inserts
192 l_dsql := 'select tbt.TXN_BILLING_TYPE_ID, bpt.business_process_id, tbt.transaction_type_id
193 from cs_txn_billing_types tbt,
194 cs_transaction_types_b ttb,
195 cs_business_processes bpr,
196 cs_bus_process_txns bpt,
197 CS_BILLING_TYPE_CATEGORIES cbtc
198 where tbt.transaction_type_id = ttb.transaction_type_id
199 AND SYSDATE BETWEEN nvl(tbt.start_date_active, SYSDATE) AND nvl(tbt.end_date_active, SYSDATE)
200 AND SYSDATE BETWEEN nvl(ttb.start_date_active, SYSDATE) AND nvl(ttb.end_date_active, SYSDATE)
201 AND SYSDATE BETWEEN nvl(bpr.start_date_active, SYSDATE) AND nvl(bpr.end_date_active, SYSDATE)
202 AND SYSDATE BETWEEN nvl(bpt.start_date_active, SYSDATE) AND nvl(bpt.end_date_active, SYSDATE)
203 AND tbt.billing_type = cbtc.billing_type
204 AND cbtc.billing_category IN ( ''L'', ''E'', ''M'' )
205 AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
206 AND bpt.transaction_type_id = tbt.transaction_type_id
207 AND bpr.business_process_id = bpt.business_process_id
208 AND bpr.field_service_flag = ''Y''
209 AND NOT EXISTS
210 (select 1
211 from csm_txn_billing_types_acc acc
212 where acc.txn_billing_type_id = tbt.txn_billing_type_id
213 and acc.business_process_id = bpt.business_process_id
214 )';
215
216 --open the cursor
217 open l_inserts_cur for l_dsql;
218 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
219 LOOP
220 FETCH l_inserts_cur INTO l_txn_billing_type_id, l_business_process_id, l_transaction_type_id;
221 EXIT WHEN l_inserts_cur%NOTFOUND;
222
223 --generate access_id
224 select csm_txn_billing_types_acc_s.NEXTVAL into l_access_id from dual;
225
226 FOR r_txn_types_tl_cur IN l_txn_types_tl_cur(l_transaction_type_id) LOOP
227 --get the users with this language
228 l_tl_omfs_palm_resource_list := l_null_resource_list;
229 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_txn_types_tl_cur.language);
230
231 --nullify the access list
232 l_single_access_id_list := l_null_access_list;
233 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
234 l_single_access_id_list(i) := l_access_id;
235 END LOOP;
236
237 --mark dirty the SDQ for all users
238 IF l_single_access_id_list.count > 0 THEN
239 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_TXN_BILLING_TYPES',
240 l_single_access_id_list, l_tl_omfs_palm_resource_list,
241 ASG_DOWNLOAD.INS, sysdate);
242 END IF;
243
244 --insert into
245 --check if the record exists
246 open record_exists_csr (l_txn_billing_type_id, l_business_process_id);
247 fetch record_exists_csr into l_dummy;
248
249 if record_exists_csr%notfound then
250 --insert if the value does not already exists
251 INSERT INTO csm_txn_billing_types_acc(access_id, txn_billing_type_id, business_process_id, CREATED_BY,
252 CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
253 VALUES (l_access_id,l_txn_billing_type_id, l_business_process_id, fnd_global.user_id, l_run_date,
254 fnd_global.user_id, l_run_date, fnd_global.login_id);
255
256 end if;
257 close record_exists_csr;
258 END LOOP;
259 END LOOP;
260 --close the cursor
261 close l_inserts_cur;
262
263 -- set the program update date in jtm_con_request_data to sysdate
264 UPDATE jtm_con_request_data
265 SET last_run_date = l_run_date
266 WHERE package_name = 'CSM_TXN_BILL_TYPES_EVENT_PKG'
267 AND procedure_name = 'REFRESH_ACC';
268
269 COMMIT;
270
271 p_status := 'FINE';
272 p_message := 'CSM_TXN_BILL_TYPES_EVENT_PKG.Refresh_Acc Executed successfully';
273
274 EXCEPTION
275 WHEN others THEN
276 if record_exists_csr%isopen then
277 close record_exists_csr;
278 end if;
279 l_sqlerrno := to_char(SQLCODE);
280 l_sqlerrmsg := substr(SQLERRM, 1,2000);
281 p_status := 'ERROR';
282 p_message := 'Error in CSM_TXN_BILL_TYPES_EVENT_PKG.Refresh_Acc :' || l_sqlerrno || ':' || l_sqlerrmsg;
283 ROLLBACK;
284 fnd_file.put_line(fnd_file.log, p_message);
285 END Refresh_Acc;
286 END CSM_TXN_BILL_TYPES_EVENT_PKG;
[Home] [Help]