[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_MATERIAL_TXN_ACC_PKG
Source
1 PACKAGE BODY CSM_MTL_MATERIAL_TXN_ACC_PKG AS
2 /* $Header: csmmtacb.pls 120.5.12010000.2 2008/10/20 10:42:08 trajasek ship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_MATERIAL_TXN_ACC';
6 g_publication_item_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
7 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_MATERIAL_TXNS');
8 g_table_name CONSTANT VARCHAR2(30) := 'MTL_MATERIAL_TRANSACTIONS';
9 g_tasks_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_MATERIAL_TXN_ACC_S';
10 g_pk1_name CONSTANT VARCHAR2(30) := 'TRANSACTION_ID';
11
12 PROCEDURE Insert_MTL_Mat_Transaction(
13 p_user_id NUMBER,
14 p_transaction_id NUMBER
15 )
16 IS
17
18 BEGIN
19 CSM_UTIL_PKG.LOG
20 ( module => g_table_name
21 , message => 'Entering Insert_MTL_Mat_Transaction Procedure to process TRAN ID : ' || p_transaction_id
22 , log_level => FND_LOG.LEVEL_STATEMENT);
23
24 /*** Call common package to insert record into ACC table ***/
25 CSM_ACC_PKG.Insert_Acc
26 ( p_publication_item_names => g_publication_item_name
27 ,p_acc_table_name => g_acc_table_name
28 ,p_seq_name => g_tasks_seq_name
29 ,p_user_id => p_user_id
30 ,p_pk1_name => g_pk1_name
31 ,p_pk1_num_value => p_transaction_id
32 );
33
34 /*** Also insert Lot numebers and serial numbers into their respective acc tables ***/
35 CSM_MTL_TXN_LOT_NUM_ACC_PKG.Insert_MTL_trans_lot_num(
36 p_user_id => p_user_id,
37 p_transaction_id => p_transaction_id
38 );
39 CSM_MTL_UNIT_TXN_ACC_PKG.Insert_MTL_Unit_Trans(
40 p_user_id => p_user_id,
41 p_transaction_id => p_transaction_id
42 );
43 CSM_UTIL_PKG.LOG
44 ( module => g_table_name
45 , message => ' Leaving Insert_MTL_Mat_Transaction procedure for TRAN ID :' || p_transaction_id
46 , log_level => FND_LOG.LEVEL_STATEMENT);
47
48 END Insert_MTL_Mat_Transaction;
49
50
51 PROCEDURE Update_MTL_Mat_Transaction(
52 p_user_id NUMBER,
53 p_transaction_id NUMBER
54 )
55 IS
56 l_acc_id NUMBER;
57 BEGIN
58
59 CSM_UTIL_PKG.LOG
60 ( module => g_table_name
61 , message => ' Entering Update_CSP_Sec_Inventory procedure to process TRAN ID :'|| p_transaction_id
62 , log_level => FND_LOG.LEVEL_STATEMENT);
63
64 /*** Call common package to insert record into ACC table ***/
65 l_acc_id := CSM_ACC_PKG.Get_Acc_Id
66 ( P_ACC_TABLE_NAME => g_acc_table_name
67 ,P_PK1_NAME => g_pk1_name
68 ,P_PK1_NUM_VALUE => p_transaction_id
69 ,p_user_id => p_user_id);
70
71 IF l_acc_id = -1 THEN
72 /*** Record is not yet in ACC tables. Insert has to be done ***/
73 CSM_ACC_PKG.Insert_Acc
74 ( p_publication_item_names => g_publication_item_name
75 ,p_acc_table_name => g_acc_table_name
76 ,p_seq_name => g_tasks_seq_name
77 ,p_user_id => p_user_id
78 ,p_pk1_name => g_pk1_name
79 ,p_pk1_num_value => p_transaction_id
80 );
81 ELSE
82 /*** Record is already in ACC. Only an update is required for re-sending ***/
83 CSM_ACC_PKG.Update_Acc
84 ( p_publication_item_names => g_publication_item_name
85 ,p_acc_table_name => g_acc_table_name
86 ,p_user_id => p_user_id
87 ,p_access_id => l_acc_id
88 );
89 END IF;
90
91 /*** Also Update Lot numebers and serial numbers into their respective acc tables ***/
92 CSM_MTL_TXN_LOT_NUM_ACC_PKG.Update_MTL_trans_lot_num(
93 p_user_id => p_user_id,
94 p_transaction_id => p_transaction_id
95 );
96 CSM_MTL_UNIT_TXN_ACC_PKG.Update_MTL_Unit_Trans(
97 p_user_id => p_user_id,
98 p_transaction_id => p_transaction_id
99 );
100 CSM_UTIL_PKG.LOG
101 ( module => g_table_name
102 , message => ' Leaving Update_MTL_Mat_Transaction Procedure for TRAN ID :' || p_transaction_id
103 , log_level => FND_LOG.LEVEL_STATEMENT);
104
105 END Update_MTL_Mat_Transaction;
106
107
108 PROCEDURE Delete_MTL_Mat_Transaction(
109 p_user_id NUMBER,
110 p_transaction_id NUMBER
111 )
112 IS
113 BEGIN
114
115 CSM_UTIL_PKG.LOG
116 ( module => g_table_name
117 , message =>' Entering Delete_MTL_Mat_Transaction Procedure for TRAN ID :'|| p_transaction_id
118 , log_level => FND_LOG.LEVEL_STATEMENT);
119
120 /*** Call common package to insert record into ACC table ***/
121 CSM_ACC_PKG.Delete_Acc
122 ( p_publication_item_names => g_publication_item_name
123 ,p_acc_table_name => g_acc_table_name
124 ,p_user_id => p_user_id
125 ,p_pk1_name => g_pk1_name
126 ,p_pk1_num_value => p_transaction_id
127 );
128
129 /*** Also insert Lot numebers and serial numbers into their respective acc tables ***/
130 CSM_MTL_TXN_LOT_NUM_ACC_PKG.Delete_MTL_trans_lot_num(
131 p_user_id => p_user_id,
132 p_transaction_id => p_transaction_id
133 );
134 CSM_MTL_UNIT_TXN_ACC_PKG.Delete_MTL_Unit_Trans(
135 p_user_id => p_user_id,
136 p_transaction_id => p_transaction_id
137 );
138 CSM_UTIL_PKG.LOG
139 ( module => g_table_name
140 , message =>' Leaving Delete_MTL_Mat_Transaction Procedure for TRAN ID :'|| p_transaction_id
141 , log_level => FND_LOG.LEVEL_STATEMENT);
142
143 END Delete_MTL_Mat_Transaction;
144
145
146 /*Delete all records for non-existing user ( e.g user was deleted )*/
147 PROCEDURE DELETE_ALL_ACC_RECORDS( p_user_id IN NUMBER
148 , x_return_status OUT NOCOPY VARCHAR2 )
149 IS
150 BEGIN
151 CSM_UTIL_PKG.LOG
152 ( module => g_table_name
153 , message => 'Entering DELETE_ALL_ACC_RECORDS'
154 , log_level => FND_LOG.LEVEL_STATEMENT);
155
156 DELETE CSM_MTL_MATERIAL_TXN_ACC
157 WHERE user_id = p_user_id;
158
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160
161 CSM_UTIL_PKG.LOG
162 ( module => g_table_name
163 , message => 'Leaving DELETE_ALL_ACC_RECORDS'
164 , log_level => FND_LOG.LEVEL_STATEMENT);
165
166 EXCEPTION WHEN OTHERS THEN
167 /*** hook failed -> log error ***/
168 -- x_return_status := FND_API.G_RET_STS_ERROR;
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170 CSM_UTIL_PKG.LOG
171 ( module => g_table_name
172 , message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
173 , log_level => FND_LOG.LEVEL_ERROR);
174 fnd_msg_pub.Add_Exc_Msg('CSM_MTL_MATERIAL_TXN_ACC_PKG','PROCESS_ACC',sqlerrm);
175 END DELETE_ALL_ACC_RECORDS;
176
177
178 PROCEDURE Refresh_Mat_Txn_Acc(p_status OUT NOCOPY VARCHAR2,
179 p_message OUT NOCOPY VARCHAR2)
180 IS
181 PRAGMA AUTONOMOUS_TRANSACTION;
182
183 CURSOR l_last_run_date_csr IS
184 SELECT NVL(last_run_date, TO_DATE('1','J'))
185 FROM jtm_con_request_data
186 WHERE package_name = 'CSM_MTL_MATERIAL_TXN_ACC_PKG'
187 AND procedure_name = 'Refresh_Mat_Txn_Acc';
188
189 --Delete if either from or to subinv is not assigned to the mobile user any longer
190 --OR if the transaction is older than CSM: Purge Interval Setting profile value
191 CURSOR l_mat_delete_csr
192 IS
193 SELECT ACC.user_id
194 , ACC.TRANSACTION_ID
195 FROM CSM_MTL_MATERIAL_TXN_ACC ACC,
196 MTL_MATERIAL_TRANSACTIONS B
197 WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID
198 AND B.TRANSACTION_ACTION_ID = 2 --Subinventory transfer
199 AND (NOT EXISTS (SELECT 1
200 FROM csm_inv_loc_ass_acc cilaa,
201 csp_inv_loc_assignments cila
202 WHERE cilaa.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
203 AND cilaa.user_id = ACC.user_id
204 AND cila.subinventory_code = B.subinventory_code
205 AND cila.organization_id = B.organization_id)
206 OR
207 NOT EXISTS (SELECT 1
208 FROM csm_inv_loc_ass_acc cilaa,
209 csp_inv_loc_assignments cila
210 WHERE cilaa.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
211 AND cilaa.user_id = ACC.user_id
212 AND cila.subinventory_code = B.transfer_subinventory
213 AND cila.organization_id = B.transfer_organization_id)
214 OR
215 B.transaction_date < (sysdate - CSM_PROFILE_PKG.get_task_history_days(acc.user_id))
216 );
217
218 -- get the updates to mat trfr
219 CURSOR l_mat_update_csr (b_last_run_date IN DATE) IS
220 SELECT ACC.user_id
221 , ACC.TRANSACTION_ID
222 FROM CSM_MTL_MATERIAL_TXN_ACC ACC,
223 MTL_MATERIAL_TRANSACTIONS B
224 WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID
225 AND B.last_update_date > b_last_run_date;
226
227 --Insert if either from OR to subinv are assigned to the mobile user
228 CURSOR l_mat_insert_csr
229 IS
230 SELECT cilaa.user_id
231 , B.TRANSACTION_ID
232 FROM MTL_MATERIAL_TRANSACTIONS B,
233 csm_inv_loc_ass_acc cilaa,
234 csp_inv_loc_assignments cila_from
235 WHERE cilaa.csp_inv_loc_assignment_id = cila_from.csp_inv_loc_assignment_id
236 AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
237 AND cila_from.subinventory_code = B.subinventory_code
238 AND cila_from.organization_id = B.organization_id
239 AND NVL(cila_from.locator_id,0) = NVL(B.locator_id,0)
240 AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
241 WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID
242 AND ACC.USER_ID = cilaa.USER_ID)
243 AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id))
244 UNION ALL
245 SELECT cilaa.user_id
246 , B.TRANSACTION_ID
247 FROM MTL_MATERIAL_TRANSACTIONS B,
248 csm_inv_loc_ass_acc cilaa,
249 csp_inv_loc_assignments cila_to
250 WHERE cilaa.csp_inv_loc_assignment_id = cila_to.csp_inv_loc_assignment_id
251 AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
252 AND cila_to.subinventory_code = B.transfer_subinventory
253 AND cila_to.organization_id = B.transfer_organization_id
254 AND NVL(cila_to.locator_id,0) = NVL(B.transfer_locator_id,0)
255 AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id))
256 AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
257 WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID
258 AND ACC.USER_ID = cilaa.USER_ID);
259
260 l_last_run_date jtm_con_request_data.last_run_date%TYPE;
261
262 TYPE tran_idTab IS TABLE OF MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID%TYPE INDEX BY BINARY_INTEGER;
263
264 l_user_id_lst ASG_DOWNLOAD.USER_LIST;
265 l_tran_id_lst tran_idTab;
266
267 l_current_date DATE;
268 l_sqlerrno VARCHAR2(20);
269 l_sqlerrmsg VARCHAR2(2000);
270
271 l_dummy boolean;
272
273 BEGIN
274 CSM_UTIL_PKG.LOG('Entering CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc ',
275 g_table_name,FND_LOG.LEVEL_PROCEDURE);
276
277 l_current_date := SYSDATE;
278
279 -- get the last run date
280 OPEN l_last_run_date_csr;
281 FETCH l_last_run_date_csr INTO l_last_run_date;
282 CLOSE l_last_run_date_csr;
283
284 IF l_user_id_lst.COUNT > 0 THEN
285 l_user_id_lst.DELETE;
286 END IF;
287 IF l_tran_id_lst.COUNT > 0 THEN
288 l_tran_id_lst.DELETE;
289 END IF;
290
291 CSM_UTIL_PKG.LOG('Processing Material Trasaction Delete',
292 'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc',FND_LOG.LEVEL_STATEMENT);
293
294 --Processing Deletes
295 OPEN l_mat_delete_csr;
296 LOOP
297 FETCH l_mat_delete_csr BULK COLLECT INTO l_user_id_lst, l_tran_id_lst LIMIT 1000;
298 EXIT WHEN l_user_id_lst.COUNT = 0;
299
300 FOR i IN l_user_id_lst.FIRST..l_user_id_lst.LAST LOOP
301 Delete_MTL_Mat_Transaction (l_user_id_lst(i), l_tran_id_lst(i));
302 END LOOP;
303 COMMIT;
304 IF l_user_id_lst.COUNT > 0 THEN
305 l_user_id_lst.DELETE;
306 END IF;
307 IF l_tran_id_lst.COUNT > 0 THEN
308 l_tran_id_lst.DELETE;
309 END IF;
310
311 END LOOP;
312 CLOSE l_mat_delete_csr;
313
314 --Processing updates
315 IF l_user_id_lst.COUNT > 0 THEN
316 l_user_id_lst.DELETE;
317 END IF;
318 IF l_tran_id_lst.COUNT > 0 THEN
319 l_tran_id_lst.DELETE;
320 END IF;
321
322 CSM_UTIL_PKG.LOG('Processing Material Trasaction Update',
323 'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc',FND_LOG.LEVEL_STATEMENT);
324 OPEN l_mat_update_csr (l_last_run_date);
325 LOOP
326 FETCH l_mat_update_csr BULK COLLECT INTO l_user_id_lst , l_tran_id_lst LIMIT 1000;
327 EXIT WHEN l_user_id_lst.COUNT = 0;
328
329 FOR i IN l_user_id_lst.FIRST..l_user_id_lst.LAST LOOP
330 Update_MTL_Mat_Transaction (l_user_id_lst(i), l_tran_id_lst(i));
331 END LOOP;
332 COMMIT;
333 IF l_user_id_lst.COUNT > 0 THEN
334 l_user_id_lst.DELETE;
335 END IF;
336 IF l_tran_id_lst.COUNT > 0 THEN
337 l_tran_id_lst.DELETE;
338 END IF;
339
340 END LOOP;
341 CLOSE l_mat_update_csr;
342
343
344 --Process Inserts
345 IF l_user_id_lst.COUNT > 0 THEN
346 l_user_id_lst.DELETE;
347 END IF;
348 IF l_tran_id_lst.COUNT > 0 THEN
349 l_tran_id_lst.DELETE;
350 END IF;
351
352 CSM_UTIL_PKG.LOG('Processing Material Trasaction Insert',
353 'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc',FND_LOG.LEVEL_STATEMENT);
354
355 OPEN l_mat_insert_csr;
356 LOOP
357 FETCH l_mat_insert_csr BULK COLLECT INTO l_user_id_lst, l_tran_id_lst LIMIT 1000;
358 EXIT WHEN l_user_id_lst.COUNT = 0;
359
360 FOR i IN l_user_id_lst.FIRST..l_user_id_lst.LAST LOOP
361 Insert_MTL_Mat_Transaction (l_user_id_lst(i), l_tran_id_lst(i));
362 END LOOP;
363 COMMIT;
364 IF l_user_id_lst.COUNT > 0 THEN
365 l_user_id_lst.DELETE;
366 END IF;
367 IF l_tran_id_lst.COUNT > 0 THEN
368 l_tran_id_lst.DELETE;
369 END IF;
370
371 END LOOP;
372 CLOSE l_mat_insert_csr;
373 COMMIT;
374
375 -- update last_run_date
376 UPDATE jtm_con_request_data
377 SET last_run_date = l_current_date
378 WHERE package_name = 'CSM_MTL_MATERIAL_TXN_ACC_PKG'
379 AND procedure_name = 'REFRESH_MAT_TXN_ACC';
380
381 COMMIT;
382
383 p_status := 'FINE';
384 p_message := 'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc executed successfully';
385
386 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc ',
387 'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc',FND_LOG.LEVEL_PROCEDURE);
388 EXCEPTION
389 WHEN OTHERS THEN
390 l_sqlerrno := TO_CHAR(SQLCODE);
391 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
392 p_status := 'ERROR';
393 p_message := 'Error in CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc: ' || l_sqlerrno || ':' || l_sqlerrmsg;
394 ROLLBACK;
395 csm_util_pkg.LOG('CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
396 END Refresh_Mat_Txn_Acc;
397
398 --Called when a new user is created
399 PROCEDURE get_new_user_mat_txn(p_user_id IN NUMBER)
400 IS
401
402 --Insert if either from OR to subinv are assigned to the mobile user
403 CURSOR l_mat_insert_csr (b_user_id IN NUMBER) IS
404 SELECT B.TRANSACTION_ID
405 FROM MTL_MATERIAL_TRANSACTIONS B,
406 csm_inv_loc_ass_acc cilaa,
407 csp_inv_loc_assignments cila_from
408 WHERE cilaa.csp_inv_loc_assignment_id = cila_from.csp_inv_loc_assignment_id
409 AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
410 AND cila_from.subinventory_code = B.subinventory_code
411 AND cila_from.organization_id = B.organization_id
412 AND NVL(cila_from.locator_id,0) = NVL(B.locator_id,0)
413 AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
414 WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID)
415 AND cilaa.user_id = b_user_id
416 AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id))
417 UNION ALL
418 SELECT B.TRANSACTION_ID
419 FROM MTL_MATERIAL_TRANSACTIONS B,
420 csm_inv_loc_ass_acc cilaa,
421 csp_inv_loc_assignments cila_to
422 WHERE cilaa.csp_inv_loc_assignment_id = cila_to.csp_inv_loc_assignment_id
423 AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
424 AND cila_to.subinventory_code = B.transfer_subinventory
425 AND cila_to.organization_id = B.transfer_organization_id
426 AND NVL(cila_to.locator_id,0) = NVL(B.transfer_locator_id,0)
427 AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
428 WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID)
429 AND cilaa.user_id = b_user_id
430 AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id));
431
432 TYPE Tran_idTab IS TABLE OF MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID%TYPE INDEX BY BINARY_INTEGER;
433
434 l_tran_id_lst Tran_idTab;
435
436 l_sqlerrno VARCHAR2(20);
437 l_sqlerrmsg VARCHAR2(2000);
438
439 l_dummy boolean;
440
441 BEGIN
442 CSM_UTIL_PKG.LOG('Entering CSM_MTL_MATERIAL_TXN_ACC_PKG.get_new_user_mat_txn ',
443 g_table_name,FND_LOG.LEVEL_PROCEDURE);
444
445 OPEN l_mat_insert_csr(p_user_id);
446 LOOP
447 IF l_tran_id_lst.COUNT > 0 THEN
448 l_tran_id_lst.DELETE;
449 END IF;
450 FETCH l_mat_insert_csr BULK COLLECT INTO l_tran_id_lst LIMIT 1000;
451 EXIT WHEN l_tran_id_lst.COUNT = 0;
452
453 FOR i IN l_tran_id_lst.FIRST..l_tran_id_lst.LAST LOOP
454 Insert_MTL_Mat_Transaction (p_user_id, l_tran_id_lst(i));
455 END LOOP;
456
457 END LOOP;
458 CLOSE l_mat_insert_csr;
459
460 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_MATERIAL_TXN_ACC_PKG.get_new_user_mat_txn ',
461 'CSM_MTL_MATERIAL_TXN_ACC_PKG.get_new_user_mat_txn',FND_LOG.LEVEL_PROCEDURE);
462 EXCEPTION
463 WHEN OTHERS THEN
464 l_sqlerrno := TO_CHAR(SQLCODE);
465 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
466 ROLLBACK;
467 csm_util_pkg.LOG('CSM_MTL_MATERIAL_TXN_ACC_PKG.get_new_user_mat_txn ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
468 END get_new_user_mat_txn;
469
470 END CSM_MTL_MATERIAL_TXN_ACC_PKG;