1 PACKAGE BODY CSM_BUS_PROCESS_TXNS_EVENT_PKG AS
2 /* $Header: csmebptb.pls 120.1 2005/07/22 08:34:50 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
17 /**
18 Refreshes the CSM_BUS_PROCESS_TXNS_ACC table by comparing with the
19 backend table for deletes, updates and inserts.
20 Refreshes for all the users
21 Also adds corresponding entries in to SDQ
22 */
23 PROCEDURE Refresh_Acc(p_status OUT NOCOPY VARCHAR2,
24 p_message OUT NOCOPY VARCHAR2) AS
25 PRAGMA AUTONOMOUS_TRANSACTION;
26
27 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
28 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
29 l_dsql varchar2(2048);
30 l_pub_item varchar2(30) := 'CSF_M_TXN_BUS_PROCESSES';
31 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
32 l_sqlerrno VARCHAR2(20);
33 l_sqlerrmsg VARCHAR2(2000);
34
35 CURSOR l_last_run_date_csr(p_pub_item IN varchar2)
36 IS
37 SELECT nvl(last_run_date, (sysdate - 365*50) )
38 FROM jtm_con_request_data
39 WHERE package_name = 'CSM_BUS_PROCESS_TXNS_EVENT_PKG'
40 AND procedure_name = 'REFRESH_ACC';
41
42 CURSOR l_deletes_cur
43 IS
44 SELECT acc.access_id
45 FROM CSM_BUS_PROCESS_TXNS_ACC acc
46 WHERE NOT EXISTS
47 (SELECT bpt.BUSINESS_PROCESS_ID
48 FROM CS_BUS_PROCESS_TXNS bpt,
49 CS_BUSINESS_PROCESSES bpr
50 WHERE bpt.transaction_type_id = acc.transaction_type_id
51 AND bpt.business_process_id = acc.business_process_id
52 AND bpr.business_process_id = bpt.business_process_id
53 AND bpr.field_service_flag = 'Y');
54
55 CURSOR l_access_id_csr IS
56 select csm_bus_processes_acc_s.nextval FROM dual;
57
58
59 l_mark_dirty boolean;
60 l_bpr_last_update_date CS_BUSINESS_PROCESSES.LAST_UPDATE_DATE%TYPE;
61 l_bpt_last_update_date CS_BUS_PROCESS_TXNS.LAST_UPDATE_DATE%TYPE;
62 l_max_update_date CSM_BUS_PROCESS_TXNS_ACC.LAST_UPDATE_DATE%TYPE;
63
64 l_access_id CSM_BUS_PROCESS_TXNS_ACC.ACCESS_ID%TYPE;
65 l_business_process_id CS_BUS_PROCESS_TXNS.BUSINESS_PROCESS_ID%TYPE;
66 l_transaction_type_id CS_BUS_PROCESS_TXNS.TRANSACTION_TYPE_ID%TYPE;
67 l_run_date DATE;
68
69 BEGIN
70 -- get last conc program update date
71 OPEN l_last_run_date_csr(l_pub_item);
72 FETCH l_last_run_date_csr INTO l_prog_update_date;
73 CLOSE l_last_run_date_csr;
74
75 -- conc program run date
76 l_run_date := SYSDATE;
77
78 --get all the OMFS Palm users
79 g_all_palm_res_list := CSM_UTIL_PKG.get_all_omfs_palm_res_list;
80
81 /****** DELETES **********/
82 --Process deletes only if particular access_id is passed
83 --open the cursor
84 open l_deletes_cur;
85 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
86 LOOP
87 FETCH l_deletes_cur INTO l_access_id;
88 EXIT WHEN l_deletes_cur%NOTFOUND;
89
90 --mark dirty the SDQ for all users
91 FOR i IN 1 .. g_all_palm_res_list.COUNT LOOP
92 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_TXN_BUS_PROCESSES',
93 l_access_id,
94 g_all_palm_res_list(i),
95 ASG_DOWNLOAD.DEL,
96 SYSDATE);
97 END LOOP;
98
99 --remove from ACC
100 DELETE FROM CSM_BUS_PROCESS_TXNS_ACC
101 WHERE ACCESS_ID = l_access_id;
102
103 END LOOP;
104
105 --close the cursor
106 close l_deletes_cur;
107
108 /******* UPDATES **********/
109 --generate sql for updates
110 l_dsql :=
111 'SELECT acc.access_id, bpt.last_update_date, bpr.last_update_date
112 FROM CS_BUS_PROCESS_TXNS bpt,
113 CS_BUSINESS_PROCESSES bpr,
114 CSM_BUS_PROCESS_TXNS_ACC acc
115 WHERE bpr.business_process_id = bpt.business_process_id
116 AND bpt.transaction_type_id = acc.transaction_type_id
117 AND bpt.business_process_id = acc.business_process_id
118 AND (bpt.last_update_date > :1
119 or bpr.last_update_date > :2
120 )';
121
122 -- AND (bpt.last_update_date > ' || '''' || l_prog_update_date || '''' ||
123 -- ' or bpr.last_update_date > ' || '''' || l_prog_update_date || '''' ||
124 -- ')';
125
126
127 --open the cursor
128 open l_updates_cur for l_dsql USING l_prog_update_date, l_prog_update_date;
129 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
130 LOOP
131 FETCH l_updates_cur INTO l_access_id, l_bpt_last_update_date, l_bpr_last_update_date;
132 EXIT WHEN l_updates_cur%NOTFOUND;
133
134 --mark dirty the SDQ for all users
135 FOR i IN 1 .. g_all_palm_res_list.COUNT LOOP
136 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_TXN_BUS_PROCESSES',
137 l_access_id, g_all_palm_res_list(i),
138 ASG_DOWNLOAD.UPD, sysdate);
139 END LOOP;
140
141
142 --get the max update date
143 IF (l_bpt_last_update_date > l_bpr_last_update_date) THEN
144 l_max_update_date := l_bpt_last_update_date;
145 ELSE
146 l_max_update_date := l_bpr_last_update_date;
147 END IF;
148
149 --update ACC
150 UPDATE CSM_BUS_PROCESS_TXNS_ACC
151 SET LAST_UPDATE_DATE = l_run_date -- l_max_update_date
152 WHERE ACCESS_ID = l_access_id;
153
154 END LOOP;
155
156 --close the cursor
157 close l_updates_cur;
158
159 /****** INSERTS **********/
160 --generate sql for inserts
161 l_dsql :=
162 'SELECT bpt.business_process_id, bpt.transaction_type_id, bpt.last_update_date, bpr.last_update_date
163 FROM CS_BUS_PROCESS_TXNS bpt,
164 CS_BUSINESS_PROCESSES bpr
165 WHERE bpr.business_process_id = bpt.business_process_id
166 AND bpr.field_service_flag = ''Y''
167 AND NOT EXISTS
168 (SELECT access_id
169 FROM CSM_BUS_PROCESS_TXNS_ACC acc
170 WHERE bpt.transaction_type_id = acc.transaction_type_id
171 AND bpt.business_process_id = acc.business_process_id )';
172
173
174 /* IF p_access_id IS NOT NULL THEN
175 l_dsql := l_dsql
176 || ' AND CSM_UTIL_PKG.generate_numpk_fromstr(to_char(bpt.business_process_id) || ''.'' || to_char(bpt.transaction_type_id)) = '
177 || p_access_id;
178 END IF;
179 */
180 --open the cursor
181 open l_inserts_cur for l_dsql;
182 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
183 LOOP
184 FETCH l_inserts_cur INTO l_business_process_id, l_transaction_type_id, l_bpt_last_update_date, l_bpr_last_update_date;
185 EXIT WHEN l_inserts_cur%NOTFOUND;
186
187 --generate access_id
188 -- l_access_id := csm_util_pkg.generate_numpk_fromstr(to_char(l_business_process_id) || '.' || to_char(l_transaction_type_id));
189 -- get access_id from seq
190 --select csm_bus_processes_acc_s.nextval into l_access_id FROM dual;
191
192 OPEN l_access_id_csr ;
193 FETCH l_access_id_csr INTO l_access_id;
194 CLOSE l_access_id_csr;
195
196 --mark dirty the SDQ for all users
197 FOR i IN 1 .. g_all_palm_res_list.COUNT LOOP
198 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_TXN_BUS_PROCESSES',
199 l_access_id, g_all_palm_res_list(i),
200 ASG_DOWNLOAD.INS, sysdate);
201 END LOOP;
202
203 --get the max update date
204 IF (l_bpt_last_update_date > l_bpr_last_update_date) THEN
205 l_max_update_date := l_bpt_last_update_date;
206 ELSE
207 l_max_update_date := l_bpr_last_update_date;
208 END IF;
209
210 --insert into
211 INSERT INTO CSM_BUS_PROCESS_TXNS_ACC (ACCESS_ID, BUSINESS_PROCESS_ID,
212 TRANSACTION_TYPE_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
213 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
214 VALUES (l_access_id, l_business_process_id,
215 l_transaction_type_id, fnd_global.user_id, sysdate, fnd_global.user_id,
216 l_run_date, fnd_global.user_id);
217
218 END LOOP;
219
220 --close the cursor
221 close l_inserts_cur;
222
223 -- set the program update date in asg_pub_item to sysdate
224 UPDATE jtm_con_request_data
225 SET last_run_date = l_run_date
226 WHERE package_name = 'CSM_BUS_PROCESS_TXNS_EVENT_PKG'
227 AND procedure_name = 'REFRESH_ACC';
228
229 commit;
230
231 p_status := 'FINE';
232 p_message := 'CSM_BUS_PROCESS_TXNS_EVENT_PKG.Refresh_Acc Executed successfully';
233
234 EXCEPTION
235 WHEN others THEN
236 l_sqlerrno := to_char(SQLCODE);
237 l_sqlerrmsg := substr(SQLERRM, 1,2000);
238 p_status := 'ERROR';
239 p_message := 'Error in CSM_BUS_PROCESS_TXNS_EVENT_PKG.Refresh_Acc :' || l_sqlerrno || ':' || l_sqlerrmsg;
240 fnd_file.put_line(fnd_file.log, p_message);
241 ROLLBACK;
242 END Refresh_Acc;
243
244 END CSM_BUS_PROCESS_TXNS_EVENT_PKG;
[Home] [Help]