[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_TXN_REASONS_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_TXN_REASONS_EVENT_PKG
2 /* $Header: csmemtrb.pls 120.1 2005/07/25 00:15:12 trajasek noship $*/
3 AS
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 g_all_palm_res_list asg_download.user_list;
16 g_pub_item varchar2(30) := 'CSM_MTL_TXN_REASONS';
17
18 PROCEDURE Refresh_mtl_txn_reasons_acc(p_status OUT NOCOPY VARCHAR2,
19 p_message OUT NOCOPY VARCHAR2)
20 IS
21 PRAGMA AUTONOMOUS_TRANSACTION;
22
23 l_null_resource_list asg_download.user_list;
24 l_single_access_id_list asg_download.access_list;
25 --a null list
26 l_null_access_list asg_download.access_list;
27 l_all_omfs_palm_resource_list asg_download.user_list;
28 l_null_palm_omfs_resource_list asg_download.user_list;
29 l_run_date date;
30 l_sqlerrno VARCHAR2(20);
31 l_sqlerrmsg varchar2(2000);
32 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
33 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
34 l_mark_dirty boolean;
35
36 CURSOR l_ins_mtl_txn_reasons_csr(p_lastrundate IN date)
37 IS
38 SELECT csm_mtl_txn_reasons_acc_s.nextval, reason_id
39 FROM mtl_transaction_reasons
40 WHERE NVL(disable_date, SYSDATE) >= SYSDATE
41 AND last_update_date >= nvl(p_lastrundate, last_update_date )
42 AND ( reason_id ) NOT IN (
43 SELECT reason_id
44 FROM csm_mtl_txn_reasons_acc
45 );
46
47 CURSOR l_upd_mtl_txn_reasons_csr( p_lastrundate IN DATE)
48 IS
49 SELECT access_id
50 FROM csm_mtl_txn_reasons_acc acc
51 , mtl_transaction_reasons mtr
52 WHERE mtr.reason_id = acc.reason_id
53 AND NVL(disable_date, SYSDATE) >= SYSDATE
54 AND mtr.last_update_date >= p_lastrundate;
55
56 CURSOR l_del_mtl_txn_reasons_csr( p_lastrundate IN DATE)
57 IS
58 SELECT access_id
59 FROM csm_mtl_txn_reasons_acc
60 WHERE reason_id IN
61 (SELECT reason_id
62 FROM mtl_transaction_reasons
63 WHERE last_update_date >= nvl(p_lastrundate, last_update_date)
64 AND NVL(disable_date, sysdate) < SYSDATE
65 )
66 UNION
67 SELECT access_id
68 FROM csm_mtl_txn_reasons_acc
69 WHERE reason_id NOT IN
70 (SELECT reason_id
71 FROM mtl_transaction_reasons
72 );
73
74 CURSOR l_last_run_date_csr
75 IS
76 SELECT nvl(last_run_date, (sysdate - 365*50))
77 FROM jtm_con_request_data
78 WHERE package_name = 'CSM_MTL_TXN_REASONS_EVENT_PKG'
79 AND procedure_name = 'REFRESH_MTL_TXN_REASONS_ACC';
80
81 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_txn_reasons_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
82 TYPE reason_id_tbl_typ IS TABLE OF mtl_transaction_reasons.reason_id%TYPE INDEX BY BINARY_INTEGER;
83
84 l_access_id_tbl access_id_tbl_typ;
85 l_reason_id_tbl reason_id_tbl_typ;
86
87 BEGIN
88
89 CSM_UTIL_PKG.LOG('Entering CSM_MTL_TXN_REASONS_EVENT_PKG.Refresh_mtl_txn_reasons_acc ',
90 'CSM_MTL_TXN_REASONS_EVENT_PKG.Refresh_mtl_txn_reasons_acc',FND_LOG.LEVEL_PROCEDURE);
91
92 -- data program is run
93 l_run_date := SYSDATE;
94
95 -- get last conc program update date
96 OPEN l_last_run_date_csr;
97 FETCH l_last_run_date_csr INTO l_prog_update_date;
98 CLOSE l_last_run_date_csr;
99
100 IF l_access_id_tbl.count > 0 THEN
101 l_access_id_tbl.delete;
102 END IF;
103
104 -- get resource list of all omfs palm users
105 l_all_omfs_palm_resource_list := l_null_palm_omfs_resource_list;
106 l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
107
108 CSM_UTIL_PKG.LOG('Entering deletes ', 'CSM_MTL_TXN_REASONS_EVENT_PKG.Refresh_mtl_txn_reasons_acc',FND_LOG.LEVEL_PROCEDURE);
109
110 -- process all deletes
111 OPEN l_del_mtl_txn_reasons_csr(l_prog_update_date);
112 FETCH l_del_mtl_txn_reasons_csr BULK COLLECT INTO l_access_id_tbl;
113 CLOSE l_del_mtl_txn_reasons_csr;
114
115 IF l_access_id_tbl.count > 0 THEN
116 FOR i IN 1..l_access_id_tbl.count LOOP
117 FOR j IN 1..l_all_omfs_palm_resource_list.count LOOP
118 l_mark_dirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
119 l_access_id_tbl(i),
120 l_all_omfs_palm_resource_list(j),
121 asg_download.del,
122 l_run_date);
123
124 END LOOP;
125 END LOOP;
126
127 -- bulk delete from acc table
128 FORALL i IN 1..l_access_id_tbl.count
129 DELETE FROM csm_mtl_txn_reasons_acc WHERE access_id = l_access_id_tbl(i);
130
131 l_access_id_tbl.delete;
132 END IF; -- end of process deletes
133
134 CSM_UTIL_PKG.LOG('Leaving deletes and entering updates', 'CSM_MTL_TXN_REASONS_EVENT_PKG.Refresh_mtl_txn_reasons_acc',FND_LOG.LEVEL_PROCEDURE);
135
136 -- process all updates
137 OPEN l_upd_mtl_txn_reasons_csr(l_prog_update_date);
138 FETCH l_upd_mtl_txn_reasons_csr BULK COLLECT INTO l_access_id_tbl;
139 CLOSE l_upd_mtl_txn_reasons_csr;
140
141 IF l_access_id_tbl.count > 0 THEN
142 FOR i IN 1..l_access_id_tbl.count LOOP
143 FOR j IN 1..l_all_omfs_palm_resource_list.count LOOP
144 l_mark_dirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
145 l_access_id_tbl(i),
146 l_all_omfs_palm_resource_list(j),
147 asg_download.upd,
148 l_run_date);
149
150 END LOOP;
151 END LOOP;
152
153 l_access_id_tbl.delete;
154 END IF; -- end of process updates
155
156 IF l_reason_id_tbl.count > 0 THEN
157 l_reason_id_tbl.delete;
158 END IF;
159
160 CSM_UTIL_PKG.LOG('Leaving updates and entering inserts', 'CSM_MTL_TXN_REASONS_EVENT_PKG.Refresh_mtl_txn_reasons_acc',FND_LOG.LEVEL_PROCEDURE);
161
162 --process all inserts
163 OPEN l_ins_mtl_txn_reasons_csr(l_prog_update_date);
164 FETCH l_ins_mtl_txn_reasons_csr BULK COLLECT INTO l_access_id_tbl, l_reason_id_tbl;
165 CLOSE l_ins_mtl_txn_reasons_csr;
166
167 -- logm('Insert Count: ' || l_access_id_tbl.count);
168
169 IF l_access_id_tbl.count > 0 THEN
170 FOR i IN 1..l_access_id_tbl.count LOOP
171 FOR j IN 1..l_all_omfs_palm_resource_list.count LOOP
172 l_mark_dirty := csm_util_pkg.MakeDirtyForResource(g_pub_item,
173 l_access_id_tbl(i),
174 l_all_omfs_palm_resource_list(j),
175 asg_download.ins,
176 l_run_date);
177
178 END LOOP;
179 END LOOP;
180
181 FORALL i IN 1..l_access_id_tbl.count
182 INSERT INTO csm_mtl_txn_reasons_acc (access_id, reason_id, created_by, creation_date, last_updated_by, last_update_date, last_update_login)
183 VALUES (l_access_id_tbl(i), l_reason_id_tbl(i), fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
184
185 l_access_id_tbl.delete;
186 END IF; -- end of process inserts
187
188 -- update last_run_date
189 UPDATE jtm_con_request_data
190 SET last_run_date = l_run_date
191 WHERE package_name = 'CSM_MTL_TXN_REASONS_EVENT_PKG'
192 AND procedure_name = 'REFRESH_MTL_TXN_REASONS_ACC';
193
194 COMMIT;
195
196 p_status := 'FINE';
197 p_message := 'CSM_MTL_TXN_REASONS_EVENT_PKG.REFRESH_MTL_TXN_REASONS_ACC Executed successfully';
198
199 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_TXN_REASONS_EVENT_PKG.Refresh_mtl_txn_reasons_acc ',
200 'CSM_MTL_TXN_REASONS_EVENT_PKG.Refresh_mtl_txn_reasons_acc',FND_LOG.LEVEL_PROCEDURE);
201
202 EXCEPTION
203 WHEN others THEN
204 l_sqlerrno := to_char(SQLCODE);
205 l_sqlerrmsg := substr(SQLERRM, 1,2000);
206 p_status := 'ERROR';
207 p_message := 'Error in CSM_MTL_TXN_REASONS_EVENT_PKG.REFRESH_MTL_TXN_REASONS_ACC: '|| l_sqlerrno || ':' || l_sqlerrmsg;
208 ROLLBACK;
209 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_TXN_REASONS_EVENT_PKG.REFRESH_MTL_TXN_REASONS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
210 'CSM_MTL_TXN_REASONS_EVENT_PKG.REFRESH_MTL_TXN_REASONS_ACC',FND_LOG.LEVEL_EXCEPTION);
211 END REFRESH_MTL_TXN_REASONS_ACC;
212
213 END CSM_MTL_TXN_REASONS_EVENT_PKG;