[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_SEC_INV_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_SEC_INV_EVENT_PKG
2 /* $Header: csmemsb.pls 120.1.12020000.2 2013/04/09 10:56:14 saradhak ship $*/
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_table_name1 CONSTANT VARCHAR2(30) := 'MTL_SEC_INVENTORIES';
16 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INV_ACC';
17 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INV_ACC_S';
18 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SEC_INVENTORIES');
20 g_pk1_name1 CONSTANT VARCHAR2(30) := 'SECONDARY_INVENTORY_NAME';
21 g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
22 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INVENTORIES';
23
24 PROCEDURE insert_mtl_sec_inventory( p_user_id NUMBER
25 , p_subinventory_code VARCHAR2
26 , p_organization_id NUMBER)
27 IS
28 l_sqlerrno varchar2(20);
29 l_sqlerrmsg varchar2(2000);
30
31 BEGIN
32 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory ',
33 'CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
34
35 CSM_ACC_PKG.Insert_Acc
36 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
37 ,P_ACC_TABLE_NAME => g_acc_table_name1
38 ,P_SEQ_NAME => g_acc_sequence_name1
39 ,P_PK1_NAME => g_pk1_name1
40 ,P_PK1_CHAR_VALUE => p_subinventory_code
41 ,P_PK2_NAME => g_pk2_name1
42 ,P_PK2_NUM_VALUE => p_organization_id
43 ,P_USER_ID => p_user_id
44 );
45
46 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory ',
47 'CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
48
49 EXCEPTION
50 WHEN others THEN
51 l_sqlerrno := to_char(SQLCODE);
52 l_sqlerrmsg := substr(SQLERRM, 1,2000);
53 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory: ' || l_sqlerrno || ':' || l_sqlerrmsg,
54 'CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory', FND_LOG.LEVEL_EXCEPTION);
55
56 END insert_mtl_sec_inventory;
57
58 PROCEDURE update_mtl_sec_inventory( p_user_id NUMBER
59 , p_subinventory_code VARCHAR2
60 , p_organization_id NUMBER)
61 IS
62 l_sqlerrno varchar2(20);
63 l_sqlerrmsg varchar2(2000);
64 l_access_id number;
65
66 BEGIN
67 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory ',
68 'CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
69
70 l_access_id := CSM_ACC_PKG.Get_Acc_Id
71 ( P_ACC_TABLE_NAME => g_acc_table_name1
72 ,P_PK1_NAME => g_pk1_name1
73 ,P_PK1_CHAR_VALUE => p_subinventory_code
74 ,P_PK2_NAME => g_pk2_name1
75 ,P_PK2_NUM_VALUE => p_organization_id
76 ,P_USER_ID => p_user_id
77 );
78
79 CSM_ACC_PKG.Update_Acc
80 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
81 ,P_ACC_TABLE_NAME => g_acc_table_name1
82 ,P_ACCESS_ID => l_access_id
83 ,P_USER_ID => p_user_id
84 );
85
86 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory ',
87 'CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
88
89 EXCEPTION
90 WHEN others THEN
91 l_sqlerrno := to_char(SQLCODE);
92 l_sqlerrmsg := substr(SQLERRM, 1,2000);
93 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory: ' || l_sqlerrno || ':' || l_sqlerrmsg,
94 'CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory', FND_LOG.LEVEL_EXCEPTION);
95
96 END update_mtl_sec_inventory;
97
98 PROCEDURE delete_mtl_sec_inventory( p_user_id NUMBER
99 , p_subinventory_code VARCHAR2
100 , p_organization_id NUMBER)
101 IS
102 l_sqlerrno varchar2(20);
103 l_sqlerrmsg varchar2(2000);
104
105 BEGIN
106 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory ',
107 'CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
108
109 CSM_ACC_PKG.Delete_Acc
110 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
111 ,P_ACC_TABLE_NAME => g_acc_table_name1
112 ,P_PK1_NAME => g_pk1_name1
113 ,P_PK1_CHAR_VALUE => p_subinventory_code
114 ,P_PK2_NAME => g_pk2_name1
115 ,P_PK2_NUM_VALUE => p_organization_id
116 ,P_USER_ID => p_user_id
117 );
118
119 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory ',
120 'CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
121
122 EXCEPTION
123 WHEN others THEN
124 l_sqlerrno := to_char(SQLCODE);
125 l_sqlerrmsg := substr(SQLERRM, 1,2000);
126 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory: ' || l_sqlerrno || ':' || l_sqlerrmsg,
127 'CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory', FND_LOG.LEVEL_EXCEPTION);
128
129 END delete_mtl_sec_inventory;
130
131 --used only for multi platform
132 --for others processed by CSM_INV_LOC_ASS_EVENT_PKG
133 PROCEDURE refresh_acc(p_status OUT NOCOPY VARCHAR2,
134 p_message OUT NOCOPY VARCHAR2)
135 IS
136 PRAGMA AUTONOMOUS_TRANSACTION;
137 l_sqlerrno varchar2(20);
138 l_sqlerrmsg varchar2(2000);
139 l_markdirty boolean;
140 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
141 l_run_date date;
142
143 TYPE l_num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
144 TYPE l_char_tab_type IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
145
146 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
147 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
148 l_tab_org_id l_num_tab_type;
149 l_tab_sub_code l_char_tab_type;
150
151 CURSOR l_last_run_date_csr
152 IS
153 SELECT nvl(last_run_date, (sysdate - 365*50))
154 FROM jtm_con_request_data
155 WHERE package_name = 'CSM_MTL_SEC_INV_EVENT_PKG'
156 AND procedure_name = 'REFRESH_ACC';
157
158
159 CURSOR c_del_cur IS
160 SELECT acc.access_id, acc.user_id
161 FROM csm_mtl_sec_inv_acc acc
162 WHERE not exists (Select 1 FROM CSM_INTERORG_PARAMETERS_ACC
163 WHERE acc.organization_id IN (FROM_ORGANIZATION_ID,TO_ORGANIZATION_ID) AND USER_ID=acc.user_id)
164 OR EXISTS(SELECT 1 FROM mtl_secondary_inventories msi WHERE acc.organization_id=msi.organization_id AND acc.secondary_inventory_name=msi.secondary_inventory_name
165 AND nvl(msi.disable_date,sysdate+1)<sysdate);
166
167 CURSOR l_upd_mtl_sec_inv_csr(p_last_upd_date IN date)
168 IS
169 SELECT acc.access_id, acc.user_id
170 FROM csp_sec_inventories csi,
171 csm_mtl_sec_inv_acc acc
172 WHERE csi.secondary_inventory_name = acc.secondary_inventory_name
173 AND csi.organization_id = acc.organization_id
174 AND csi.last_update_date >= p_last_upd_date;
175
176 CURSOR c_ins_cur IS -- sub inventories of dest org
177 select CSM_MTL_SEC_INV_ACC_S.nextval as access_id,au.user_id , csi.organization_id,csi.secondary_inventory_name
178 from csp_sec_inventories csi,asg_user au
179 where au.enabled='Y' and au.multi_platform='Y'
180 --AND csi.CONDITION_TYPE='G'
181 AND EXISTS(SELECT 1 FROM mtl_secondary_inventories msi WHERE csi.organization_id=msi.organization_id AND csi.secondary_inventory_name=msi.secondary_inventory_name
182 AND nvl(msi.disable_date,sysdate+1)>=sysdate)
183 AND EXISTS(SELECT 1 FROM CSM_INTERORG_PARAMETERS_ACC WHERE USER_ID=au.user_id AND csi.organization_id IN (FROM_ORGANIZATION_ID,TO_ORGANIZATION_ID))
184 AND NOT EXISTS(SELECT 1 FROM csm_mtl_sec_inv_acc acc WHERE csi.organization_id=acc.organization_id
185 AND csi.secondary_inventory_name=acc.secondary_inventory_name and acc.user_id=au.user_id);
186
187 BEGIN
188 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc ',
189 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_PROCEDURE);
190
191 -- data program is run
192 l_run_date := SYSDATE;
193
194 -- get last conc program update date
195 OPEN l_last_run_date_csr;
196 FETCH l_last_run_date_csr INTO l_prog_update_date;
197 CLOSE l_last_run_date_csr;
198
199 CSM_UTIL_PKG.LOG('Processing deletes', 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc', FND_LOG.LEVEL_PROCEDURE);
200 -- process all deletes
201 OPEN c_del_cur;
202 LOOP
203 l_tab_access_id.DELETE;
204 l_tab_user_id.DELETE;
205
206 FETCH c_del_cur BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
207 EXIT WHEN l_tab_access_id.COUNT = 0;
208
209 CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from csm_mtl_sec_inv_acc' ,
210 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
211
212 l_markdirty := asg_download.mark_dirty(
213 P_PUB_ITEM => g_pub_item
214 , p_accessList => l_tab_access_id
215 , p_userid_list => l_tab_user_id
216 , p_dml_type => 'D'
217 , P_TIMESTAMP => sysdate
218 );
219
220 FORALL i IN 1..l_tab_access_id.COUNT
221 DELETE FROM csm_mtl_sec_inv_acc WHERE access_id = l_tab_access_id(i);
222
223 COMMIT;
224 END LOOP;
225 CLOSE c_del_cur;
226
227
228 CSM_UTIL_PKG.LOG('Processing Updates', 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc', FND_LOG.LEVEL_PROCEDURE);
229 -- process all deletes
230 OPEN l_upd_mtl_sec_inv_csr(l_prog_update_date);
231 LOOP
232 l_tab_access_id.DELETE;
233 l_tab_user_id.DELETE;
234
235 FETCH l_upd_mtl_sec_inv_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
236 EXIT WHEN l_tab_access_id.COUNT = 0;
237
238 CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records from csm_mtl_sec_inv_acc' ,
239 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
240
241 l_markdirty := asg_download.mark_dirty(
242 P_PUB_ITEM => g_pub_item
243 , p_accessList => l_tab_access_id
244 , p_userid_list => l_tab_user_id
245 , p_dml_type => 'U'
246 , P_TIMESTAMP => sysdate
247 );
248 COMMIT;
249 END LOOP;
250 CLOSE l_upd_mtl_sec_inv_csr;
251
252 CSM_UTIL_PKG.LOG('Processing inserts', 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc', FND_LOG.LEVEL_PROCEDURE);
253 -- process all inserts
254
255 OPEN c_ins_cur;
256 LOOP
257 l_tab_access_id.DELETE;
258 l_tab_user_id.DELETE;
259 l_tab_org_id.DELETE;
260 l_tab_sub_code.DELETE;
261
262 FETCH c_ins_cur BULK COLLECT INTO l_tab_access_id, l_tab_user_id,l_tab_org_id,l_tab_sub_code LIMIT 1000;
263 EXIT WHEN l_tab_access_id.COUNT = 0;
264
265 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into CSM_INTERORG_FREIGHTS_ACC' ,
266 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EVENT);
267
268 FORALL i IN 1..l_tab_access_id.COUNT
269 INSERT INTO CSM_MTL_SEC_INV_ACC (access_id, user_id,organization_id, secondary_inventory_name,
270 counter, created_by, creation_date, last_updated_by, last_update_date ,lasT_update_login)
271 VALUES (l_tab_access_id(i), l_tab_user_id(i),l_tab_org_id(i),l_tab_sub_code(i),
272 1,fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date,1);
273
274 /*** push to oLite using asg_download ***/
275 -- do bulk makedirty
276 l_markdirty := asg_download.mark_dirty(
277 P_PUB_ITEM => g_pub_item
278 , p_accessList => l_tab_access_id
279 , p_userid_list => l_tab_user_id
280 , p_dml_type => 'I'
281 , P_TIMESTAMP => sysdate
282 );
283
284 COMMIT;
285 END LOOP;
286 CLOSE c_ins_cur;
287
288 -- set the program update date in jtm_con_request_data to sysdate
289 UPDATE jtm_con_request_data
290 SET last_run_date = l_run_date
291 WHERE package_name = 'CSM_MTL_SEC_INV_EVENT_PKG'
292 AND procedure_name = 'REFRESH_ACC';
293
294 COMMIT;
295
296 p_status := 'FINE';
297 p_message := 'CSM_MTL_SEC_INV_EVENT_PKG.Refresh_Acc Executed successfully';
298
299 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc ',
300 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_PROCEDURE);
301
302 EXCEPTION
303 WHEN others THEN
304 l_sqlerrno := to_char(SQLCODE);
305 l_sqlerrmsg := substr(SQLERRM, 1,2000);
306 p_status := 'ERROR';
307 p_message := 'Error in CSM_MTL_SEC_INV_EVENT_PKG.Refresh_Acc:' || l_sqlerrno || ':' || l_sqlerrmsg;
308 ROLLBACK;
309 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc: ' || l_sqlerrno || ':' || l_sqlerrmsg,
310 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EXCEPTION);
311 END refresh_acc;
312
313 END CSM_MTL_SEC_INV_EVENT_PKG;