[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_UNIT_TXN_ACC_PKG
Source
1 PACKAGE BODY CSM_MTL_UNIT_TXN_ACC_PKG AS
2 /* $Header: csmutacb.pls 120.0.12010000.2 2008/10/20 10:43:51 trajasek ship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_UNIT_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_UNIT_TXNS');
8 g_table_name CONSTANT VARCHAR2(30) := 'MTL_UNIT_TRANSACTIONS';
9 g_tasks_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_UNIT_TXN_ACC_S';
10 g_pk1_name CONSTANT VARCHAR2(30) := 'TRANSACTION_ID';
11 g_pk2_name CONSTANT VARCHAR2(30) := 'SERIAL_NUMBER';
12 g_old_user_id NUMBER; -- variable containing old user_id; populated in Pre_Update hook
13 g_debug_level NUMBER; -- debug level
14 TYPE TRANS_idTab IS TABLE OF MTL_UNIT_TRANSACTIONS.TRANSACTION_ID%TYPE INDEX BY BINARY_INTEGER;
15 TYPE SERIAL_idTab IS TABLE OF MTL_UNIT_TRANSACTIONS.SERIAL_NUMBER%TYPE INDEX BY BINARY_INTEGER;
16 l_transaction_id_lst TRANS_idTab;
17 l_serial_lst SERIAL_idTab;
18
19 PROCEDURE Insert_MTL_Unit_Trans( p_user_id NUMBER,
20 p_transaction_id NUMBER
21 )
22 IS
23 CURSOR c_mtl_unit_trans(b_transaction_id NUMBER)
24 IS
25 SELECT TRANSACTION_ID, SERIAL_NUMBER
26 FROM MTL_UNIT_TRANSACTIONS
27 WHERE TRANSACTION_ID = b_transaction_id;
28
29 BEGIN
30 CSM_UTIL_PKG.LOG
31 ( module => g_table_name
32 , message => p_transaction_id || 'Entering Insert_MTL_Unit_Trans Procedure'
33 , log_level => FND_LOG.LEVEL_STATEMENT);
34
35 IF l_transaction_id_lst.COUNT >0 THEN
36 l_transaction_id_lst.DELETE;
37 END IF;
38 IF l_serial_lst.COUNT >0 THEN
39 l_serial_lst.DELETE;
40 END IF;
41
42 /*** Retreive record assigned by Hook ***/
43 OPEN c_mtl_unit_trans( p_transaction_id);
44 FETCH c_mtl_unit_trans BULK COLLECT INTO l_transaction_id_lst,l_serial_lst;
45 CLOSE c_mtl_unit_trans;
46
47 IF l_transaction_id_lst.COUNT > 0 THEN
48 /*** Loop over all available records and put them in the acc table ***/
49 FOR i in 1..l_transaction_id_lst.COUNT LOOP
50 /*** Call common package to insert record into ACC table ***/
51 CSM_ACC_PKG.Insert_Acc
52 ( p_publication_item_names => g_publication_item_name
53 ,p_acc_table_name => g_acc_table_name
54 ,p_seq_name => g_tasks_seq_name
55 ,p_user_id => p_user_id
56 ,p_pk1_name => g_pk1_name
57 ,p_pk1_num_value => l_transaction_id_lst(i)
58 ,p_pk2_name => g_pk2_name
59 ,p_pk2_char_value => l_serial_lst(i)
60 );
61 END LOOP;
62 END IF;
63 IF l_transaction_id_lst.COUNT >0 THEN
64 l_transaction_id_lst.DELETE;
65 END IF;
66 IF l_serial_lst.COUNT >0 THEN
67 l_serial_lst.DELETE;
68 END IF;
69 CSM_UTIL_PKG.LOG
70 ( module => g_table_name
71 , message => 'Leaving Insert_MTL_Unit_Trans Procedure after processing tran id:' || p_transaction_id
72 , log_level => FND_LOG.LEVEL_STATEMENT);
73
74 END Insert_MTL_Unit_Trans;
75
76
77 PROCEDURE Update_MTL_Unit_Trans( p_user_id NUMBER,
78 p_transaction_id NUMBER
79 )
80 IS
81 BEGIN
82 /*** Not necassery because it will be an insert or delete updates will not be done from the csp_inv_loc_ass package ***/
83 /*** get debug level ***/
84 RETURN;
85 END Update_MTL_Unit_Trans;
86
87
88 PROCEDURE Delete_MTL_Unit_Trans( p_user_id NUMBER,
89 p_transaction_id NUMBER
90 )
91 IS
92 CURSOR c_mtl_unit_trans(b_transaction_id NUMBER, b_user_id NUMBER)
93 IS
94 SELECT TRANSACTION_ID, SERIAL_NUMBER
95 FROM CSM_MTL_UNIT_TXN_ACC
96 WHERE TRANSACTION_ID = b_transaction_id
97 AND USER_ID = b_user_id;
98
99 BEGIN
100
101 CSM_UTIL_PKG.LOG
102 ( module => g_table_name
103 , message => ' Entering Delete_MTL_trans_lot_num for Tran id : ' || p_transaction_id
104 , log_level => FND_LOG.LEVEL_STATEMENT);
105
106 IF l_transaction_id_lst.COUNT >0 THEN
107 l_transaction_id_lst.DELETE;
108 END IF;
109 IF l_serial_lst.COUNT >0 THEN
110 l_serial_lst.DELETE;
111 END IF;
112 /*** Retreive record assigned by Hook ***/
113 OPEN c_mtl_unit_trans( p_transaction_id , p_user_id);
114 FETCH c_mtl_unit_trans BULK COLLECT INTO l_transaction_id_lst , l_serial_lst;
115 CLOSE c_mtl_unit_trans;
116
117 IF l_transaction_id_lst.COUNT > 0 THEN
118 /*** Loop over all available records and put them in the acc table ***/
119 FOR i in 1..l_transaction_id_lst.COUNT LOOP
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 => l_transaction_id_lst(i)
127 ,p_pk2_name => g_pk2_name
128 ,p_pk2_char_value => l_serial_lst(i)
129 );
130 END LOOP;
131 END IF;
132
133 IF l_transaction_id_lst.COUNT >0 THEN
134 l_transaction_id_lst.DELETE;
135 END IF;
136 IF l_serial_lst.COUNT >0 THEN
137 l_serial_lst.DELETE;
138 END IF;
139 CSM_UTIL_PKG.LOG
140 ( module => g_table_name
141 , message => ' Leaving Delete_MTL_trans_lot_num for Tran id : ' || p_transaction_id
142 , log_level => FND_LOG.LEVEL_STATEMENT);
143
144 END Delete_MTL_Unit_Trans;
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
157 DELETE CSM_MTL_UNIT_TXN_ACC
158 WHERE user_id = p_user_id;
159
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161
162 CSM_UTIL_PKG.LOG
163 ( module => g_table_name
164 , message => 'Leaving DELETE_ALL_ACC_RECORDS'
165 , log_level => FND_LOG.LEVEL_STATEMENT);
166
167 EXCEPTION WHEN OTHERS THEN
168 /*** hook failed -> log error ***/
169 -- x_return_status := FND_API.G_RET_STS_ERROR;
170 x_return_status := FND_API.G_RET_STS_SUCCESS;
171 CSM_UTIL_PKG.LOG
172 ( module => g_table_name
173 , message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
174 , log_level => FND_LOG.LEVEL_ERROR);
175 fnd_msg_pub.Add_Exc_Msg('CSM_MTL_UNIT_TXN_ACC_PKG','PROCESS_ACC',sqlerrm);
176 END DELETE_ALL_ACC_RECORDS;
177
178
179 END CSM_MTL_UNIT_TXN_ACC_PKG;