[Home] [Help]
PACKAGE BODY: APPS.CSM_IB_TXN_SUB_TYPES_EVENT_PKG
Source
1 PACKAGE BODY CSM_IB_TXN_SUB_TYPES_EVENT_PKG AS
2 /* $Header: csmeibtb.pls 120.3 2006/09/05 07:02:36 trajasek noship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_all_palm_res_list asg_download.user_list;
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(3000);
23 l_pub_item varchar2(30) := 'CSF_M_TXN_SUB_TYPES';
24 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
25 l_mark_dirty boolean;
26 l_access_id CSM_IB_TXN_TYPES_ACC.ACCESS_ID%TYPE;
27 l_language cs_transaction_types_tl.language%TYPE;
28 l_sub_type_id csi_ib_txn_types.sub_type_id%TYPE;
29
30 l_tl_omfs_palm_resource_list asg_download.user_list;
31 l_single_access_id_list asg_download.access_list;
32 --a null list
33 l_null_access_list asg_download.access_list;
34 l_null_resource_list asg_download.user_list;
35 l_run_date DATE;
36 l_sqlerrno varchar2(20);
37 l_sqlerrmsg varchar2(2000);
38
39 CURSOR l_last_run_date_csr
40 IS
41 SELECT nvl(last_run_date, (sysdate - 365*50))
42 FROM jtm_con_request_data
43 WHERE package_name = 'CSM_IB_TXN_SUB_TYPES_EVENT_PKG'
44 AND procedure_name = 'REFRESH_ACC';
45
46 CURSOR l_deletes_cur
47 IS
48 SELECT acc.access_id
49 FROM csm_ib_txn_types_acc acc
50 WHERE NOT EXISTS
51 (SELECT cit.sub_type_id
52 FROM csi_ib_txn_types cit , csi_source_ib_types cst , csi_txn_types ctt ,
53 csi_instance_statuses cis , cs_transaction_types_b ttb
54 WHERE acc.sub_type_id = cit.sub_type_id
55 AND cit.cs_transaction_type_id = ttb.transaction_type_id(+)
56 AND ctt.source_application_id = 513
57 AND ctt.source_transaction_type = 'FIELD_SERVICE_REPORT'
58 AND cst.sub_type_id = cit.sub_type_id
59 AND ctt.transaction_type_id = cst.transaction_type_id
60 AND cit.src_status_id = cis.instance_status_id(+)
61 AND (NVL(cst.update_IB_flag, 'N') = 'N' --Non IB
62 OR ( cst.update_ib_flag = 'Y'
63 and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
64 and nvl(cis.terminated_flag, 'N') <> 'Y'
65 and (
66 (--Return IB
67 cit.src_change_owner_to_code = 'I'
68 and nvl(cit.parent_reference_reqd, 'N') = 'N'
69 and ttb.line_order_category_code ='RETURN'
70 and cit.src_change_owner = 'Y'
71 and nvl(cit.src_return_reqd, 'N') = 'N'
72
73 )
74 or (--Order IB
75 cit.src_change_owner_to_code = 'E'
76 --and cit.src_reference_reqd = 'Y'
77 and ttb.line_order_category_code='ORDER'
78 and cit.src_change_owner = 'Y'
79 and nvl(cit.src_return_reqd, 'N') = 'N'
80
81 )
82 or
83 (--Loaner IB
84 ttb.line_order_category_code='ORDER'
85 and NVL(cit.src_change_owner,'N') = 'N'
86 and nvl(cit.src_return_reqd, 'Y') = 'Y'
87 AND NVL(cit.src_change_owner_to_code,'N') ='N'
88
89 )
90
91 )
92 )
93 )
94 );
95
96 l_temp_date date;
97
98 CURSOR l_txn_types_tl_cur (p_transaction_type_id IN cs_transaction_types_b.transaction_type_id%TYPE)
99 IS
100 SELECT tt_tl.LANGUAGE
101 FROM cs_transaction_types_tl tt_tl
102 WHERE tt_tl.transaction_type_id = p_transaction_type_id;
103
104 cursor record_exists_csr( p_sub_type_id csi_ib_txn_types.sub_type_id%TYPE)
105 IS
106 SELECT 1
107 FROM csm_ib_txn_types_acc
108 WHERE sub_type_id = p_sub_type_id;
109
110 l_dummy number;
111
112 BEGIN
113 -- get last conc program update date
114 OPEN l_last_run_date_csr;
115 FETCH l_last_run_date_csr INTO l_prog_update_date;
116 CLOSE l_last_run_date_csr;
117
118 -- conc program run date
119 l_run_date := SYSDATE;
120
121 --get all the OMFS Palm users
122 g_all_palm_res_list := CSM_UTIL_PKG.get_all_omfs_palm_res_list;
123
124 /****** DELETES **********/
125 --open the cursor
126 open l_deletes_cur;
127 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
128 LOOP
129 FETCH l_deletes_cur INTO l_access_id;
130 EXIT WHEN l_deletes_cur%NOTFOUND;
131
132 --mark dirty the SDQ for all users
133 FOR i IN 1 .. g_all_palm_res_list.COUNT LOOP
134 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
135 l_access_id,
136 g_all_palm_res_list(i),
137 ASG_DOWNLOAD.DEL,
138 SYSDATE);
139 END LOOP;
140
141 --remove from ACC
142 DELETE FROM CSM_IB_TXN_TYPES_ACC
143 WHERE ACCESS_ID = l_access_id;
144
145 END LOOP;
146 --close the cursor
147 close l_deletes_cur;
148
149 /******* UPDATES **********/
150 --generate sql for updates
151 l_dsql := 'SELECT acc.access_id
152 FROM csm_ib_txn_types_acc acc ,
153 csi_ib_txn_types cit,
154 csi_source_ib_types cst ,
155 csi_txn_types ctt ,
156 csi_instance_statuses cis ,
157 cs_transaction_types_b ttb
158 WHERE acc.sub_type_id = cit.sub_type_id
159 AND cit.cs_transaction_type_id = ttb.transaction_type_id(+)
160 AND ctt.source_application_id = 513
161 AND ctt.source_transaction_type = ''FIELD_SERVICE_REPORT''
162 AND cst.sub_type_id = cit.sub_type_id
163 AND ctt.transaction_type_id = cst.transaction_type_id
164 AND cit.src_status_id = cis.instance_status_id(+)
165 AND (NVL(cst.update_IB_flag, ''N'') = ''N''
166 OR ( cst.update_ib_flag = ''Y''
167 and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
168 and nvl(cis.terminated_flag, ''N'') <> ''Y''
169 and (
170 (cit.src_change_owner_to_code = ''I''
171 and nvl(cit.parent_reference_reqd, ''N'') = ''N''
172 and ttb.line_order_category_code =''RETURN''
173 and cit.src_change_owner = ''Y''
174 and nvl(cit.src_return_reqd, ''N'') = ''N''
175
176 )
177 or
178 (cit.src_change_owner_to_code = ''E''
179 and ttb.line_order_category_code=''ORDER''
180 and cit.src_change_owner = ''Y''
181 and nvl(cit.src_return_reqd, ''N'') = ''N''
182
183 )
184 or (
185 ttb.line_order_category_code=''ORDER''
186 and NVL(cit.src_change_owner,''N'') = ''N''
187 and nvl(cit.src_return_reqd, ''Y'') = ''Y''
188 AND NVL(cit.src_change_owner_to_code,''N'') =''N''
189
190 )
191
192 )
193 )
194 )
195 AND (cit.last_update_date > :1
196 or cst.last_update_date > :2
197 or ctt.last_update_date > :3
198 or cis.last_update_date > :4
199 or ttb.last_update_date > :5
200 )';
201
202 --open the cursor
203 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;
204 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
205 LOOP
206 FETCH l_updates_cur INTO l_access_id;
207 EXIT WHEN l_updates_cur%NOTFOUND;
208
209 --get all Palm users
210 g_all_palm_res_list := CSM_UTIL_PKG.get_all_omfs_palm_res_list;
211
212 --nullify the access list
213 l_single_access_id_list := l_null_access_list;
214 FOR i in 1 .. g_all_palm_res_list.COUNT LOOP
215 l_single_access_id_list(i) := l_access_id;
216 END LOOP;
217
218 --mark dirty the SDQ for all users
219 IF l_single_access_id_list.count > 0 then
220 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
221 l_single_access_id_list, g_all_palm_res_list,
222 ASG_DOWNLOAD.UPD, sysdate);
223 END IF;
224
225 --update ACC
226 UPDATE CSM_IB_TXN_TYPES_ACC
227 SET LAST_UPDATE_DATE = l_run_date
228 WHERE ACCESS_ID = l_access_id;
229
230 END LOOP;
231
232 --close the cursor
233 close l_updates_cur;
234
235 /****** INSERTS **********/
236 --generate sql for inserts
237 l_dsql := 'SELECT cit.sub_type_id
238 FROM csi_ib_txn_types cit , csi_source_ib_types cst , csi_txn_types ctt ,
239 csi_instance_statuses cis , cs_transaction_types_b ttb
240 WHERE cit.cs_transaction_type_id = ttb.transaction_type_id(+)
241 AND ctt.source_application_id = 513
242 AND ctt.source_transaction_type = ''FIELD_SERVICE_REPORT''
243 AND cst.sub_type_id = cit.sub_type_id
244 AND ctt.transaction_type_id = cst.transaction_type_id
245 AND cit.src_status_id = cis.instance_status_id(+)
246 AND (NVL(cst.update_IB_flag, ''N'') = ''N''--Non IB
247 OR ( cst.update_ib_flag = ''Y''
248 and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
249 and nvl(cis.terminated_flag, ''N'') <> ''Y''
250 and ( (--Retirn IB
251 cit.src_change_owner_to_code = ''I''
252 and nvl(cit.parent_reference_reqd, ''N'') = ''N''
253 and ttb.line_order_category_code =''RETURN''
254 and cit.src_change_owner = ''Y''
255 and nvl(cit.src_return_reqd, ''N'') = ''N''
256
257 )
258 or (--Order IB
259 cit.src_change_owner_to_code = ''E''
260 and ttb.line_order_category_code=''ORDER''
261 and cit.src_change_owner = ''Y''
262 and nvl(cit.src_return_reqd, ''N'') = ''N''
263
264 )
265 or (--Loaner IB
266 ttb.line_order_category_code=''ORDER''
267 and NVL(cit.src_change_owner,''N'') = ''N''
268 and nvl(cit.src_return_reqd, ''Y'') = ''Y''
269 AND NVL(cit.src_change_owner_to_code,''N'') =''N''
270
271 )
272 )
273 )
274 )
275 AND NOT EXISTS
276 (SELECT 1
277 FROM csm_ib_txn_types_acc acc
278 WHERE acc.sub_type_id = cit.sub_type_id
279 ) ';
280
281 --open the cursor
282 open l_inserts_cur for l_dsql;
283 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
284 LOOP
285 FETCH l_inserts_cur INTO l_sub_type_id;
286 EXIT WHEN l_inserts_cur%NOTFOUND;
287
288 --generate access_id
289 select CSM_IB_TXN_TYPES_ACC_S.NEXTVAL into l_access_id from dual;
290
291 --get all Palm users
292 g_all_palm_res_list := CSM_UTIL_PKG.get_all_omfs_palm_res_list;
293
294 --nullify the access list
295 l_single_access_id_list := l_null_access_list;
296 FOR i in 1 .. g_all_palm_res_list.COUNT LOOP
297 l_single_access_id_list(i) := l_access_id;
298 END LOOP;
299
300 --mark dirty the SDQ for all users
301 IF l_single_access_id_list.count > 0 then
302 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource(l_pub_item,
303 l_single_access_id_list, g_all_palm_res_list,
304 ASG_DOWNLOAD.INS, sysdate);
305 END IF;
306
307 --insert into
308 --check if the record exists
309 OPEN record_exists_csr (l_sub_type_id);
310 FETCH record_exists_csr into l_dummy;
311
312 IF record_exists_csr%NOTFOUND then
313 --insert if the value does not already exists
314 INSERT INTO csm_ib_txn_types_acc(access_id, sub_type_id, CREATED_BY,
315 CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
316 VALUES (l_access_id,l_sub_type_id, fnd_global.user_id, l_run_date,
317 fnd_global.user_id, l_run_date, fnd_global.login_id);
318
319 END IF;
320 CLOSE record_exists_csr;
321
322 END LOOP;
323 --close the cursor
324 close l_inserts_cur;
325
326 -- set the program update date in jtm_con_request_data to sysdate
327 UPDATE jtm_con_request_data
328 SET last_run_date = l_run_date
329 WHERE package_name = 'CSM_IB_TXN_SUB_TYPES_EVENT_PKG'
330 AND procedure_name = 'REFRESH_ACC';
331
332 COMMIT;
333
334 p_status := 'FINE';
335 p_message := 'CSM_IB_TXN_SUB_TYPES_EVENT_PKG.Refresh_Acc Executed successfully';
336
337 EXCEPTION
338 WHEN others THEN
339 if record_exists_csr%isopen then
340 close record_exists_csr;
341 end if;
342 l_sqlerrno := to_char(SQLCODE);
343 l_sqlerrmsg := substr(SQLERRM, 1,2000);
344 p_status := 'ERROR';
345 p_message := 'Error in CSM_IB_TXN_SUB_TYPES_EVENT_PKG.Refresh_Acc: '|| l_sqlerrno || ':' || l_sqlerrmsg;
346 ROLLBACK;
347 fnd_file.put_line(fnd_file.log, 'CSM_IB_TXN_SUB_TYPES_EVENT_PKG.REFRESH_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
348 END Refresh_Acc;
349
350 END CSM_IB_TXN_SUB_TYPES_EVENT_PKG;