[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_TXN_LOT_NUM_ACC_PKG
Source
1 PACKAGE BODY CSM_MTL_TXN_LOT_NUM_ACC_PKG AS
2 /* $Header: csmltacb.pls 120.0.12010000.2 2008/10/20 10:40:59 trajasek ship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_TXN_LOT_NUM_ACC';
6 g_publication_item_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
7 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_TXNS_LOT_NUM');
8 g_table_name CONSTANT VARCHAR2(30) := 'MTL_TRANSACTIONS_LOT_NUMBERS';
9 g_tasks_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_TXN_LOT_NUM_ACC_S';
10 g_pk1_name CONSTANT VARCHAR2(30) := 'TRANSACTION_ID';
11 g_pk2_name CONSTANT VARCHAR2(30) := 'LOT_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_TRANSACTION_LOT_NUMBERS.TRANSACTION_ID%TYPE INDEX BY BINARY_INTEGER;
15 TYPE LOT_idTab IS TABLE OF MTL_TRANSACTION_LOT_NUMBERS.LOT_NUMBER%TYPE INDEX BY BINARY_INTEGER;
16 l_transaction_id_lst TRANS_idTab;
17 l_lot_number_lst LOT_idTab;
18
19 PROCEDURE Insert_MTL_trans_lot_num
20 ( p_user_id NUMBER,
21 p_transaction_id NUMBER )
22 IS
23
24 CURSOR c_transaction_lot_number( b_transaction_id NUMBER )
25 IS
26 SELECT TRANSACTION_ID, LOT_NUMBER
27 FROM MTL_TRANSACTION_LOT_NUMBERS
28 WHERE TRANSACTION_ID = b_transaction_id;
29
30 BEGIN
31 CSM_UTIL_PKG.LOG
32 ( module => g_table_name
33 , message => 'Entering Insert_MTL_trans_lot_num Procedure for TRAN ID :' || p_transaction_id
34 , log_level => FND_LOG.LEVEL_STATEMENT);
35
36 IF l_transaction_id_lst.COUNT >0 THEN
37 l_transaction_id_lst.DELETE;
38 END IF;
39
40 IF l_lot_number_lst.COUNT >0 THEN
41 l_lot_number_lst.DELETE;
42 END IF;
43
44 /*** Retreive record assigned by Hook ***/
45 OPEN c_transaction_lot_number( p_transaction_id);
46 FETCH c_transaction_lot_number BULK COLLECT INTO l_transaction_id_lst,l_lot_number_lst;
47 CLOSE c_transaction_lot_number;
48
49 IF l_transaction_id_lst.COUNT > 0 THEN
50 /*** Loop over all available records and put them in the acc table ***/
51 FOR i in 1..l_transaction_id_lst.COUNT LOOP
52 /*** Call common package to insert record into ACC table ***/
53 CSM_ACC_PKG.Insert_Acc
54 ( p_publication_item_names => g_publication_item_name
55 ,p_acc_table_name => g_acc_table_name
56 ,p_seq_name => g_tasks_seq_name
57 ,p_user_id => p_user_id
58 ,p_pk1_name => g_pk1_name
59 ,p_pk1_num_value => l_transaction_id_lst(i)
60 ,p_pk2_name => g_pk2_name
61 ,p_pk2_char_value => l_lot_number_lst(i)
62 );
63 END LOOP;
64 END IF;
65
66 IF l_transaction_id_lst.COUNT >0 THEN
67 l_transaction_id_lst.DELETE;
68 END IF;
69 IF l_lot_number_lst.COUNT >0 THEN
70 l_lot_number_lst.DELETE;
71 END IF;
72 CSM_UTIL_PKG.LOG
73 ( module => g_table_name
74 , message => 'Leaving Insert_MTL_trans_lot_num Procedure for TRAN ID :' || p_transaction_id
75 , log_level => FND_LOG.LEVEL_STATEMENT);
76 END Insert_MTL_trans_lot_num;
77
78
79 PROCEDURE Update_MTL_trans_lot_num( p_user_id NUMBER,
80 p_transaction_id NUMBER)
81 IS
82 CURSOR c_transaction_lot_number(b_transaction_id NUMBER)
83 IS
84 SELECT TRANSACTION_ID, LOT_NUMBER
85 FROM MTL_TRANSACTION_LOT_NUMBERS
86 WHERE TRANSACTION_ID = b_transaction_id;
87
88 l_acc_id NUMBER;
89
90 BEGIN
91
92 CSM_UTIL_PKG.LOG
93 ( module => g_table_name
94 , message => ' Entering Update_MTL_trans_lot_num Procedure forTRAN ID :' || p_transaction_id
95 , log_level => FND_LOG.LEVEL_STATEMENT);
96
97 IF l_transaction_id_lst.COUNT >0 THEN
98 l_transaction_id_lst.DELETE;
99 END IF;
100 IF l_lot_number_lst.COUNT >0 THEN
101 l_lot_number_lst.DELETE;
102 END IF;
103
104 /*** Retreive record assigned by Hook ***/
105 OPEN c_transaction_lot_number( p_transaction_id);
106 FETCH c_transaction_lot_number BULK COLLECT INTO l_transaction_id_lst,l_lot_number_lst ;
107 CLOSE c_transaction_lot_number;
108
109 /*** Loop over all available records and put them in the acc table ***/
110 /*** Call common package to insert record into ACC table ***/
111 FOR i in 1..l_transaction_id_lst.COUNT LOOP
112 l_acc_id := CSM_ACC_PKG.Get_Acc_Id
113 ( P_ACC_TABLE_NAME => g_acc_table_name
114 ,p_pk1_name => g_pk1_name
115 ,p_pk1_num_value => l_transaction_id_lst(i)
116 ,p_pk2_name => g_pk2_name
117 ,p_pk2_char_value => l_lot_number_lst(i)
118 ,p_user_id => p_user_id);
119
120 IF l_acc_id = -1 THEN
121 /*** Record is not yet in ACC tables. Insert has to be done ***/
122 CSM_ACC_PKG.Insert_Acc
123 ( p_publication_item_names => g_publication_item_name
124 ,p_acc_table_name => g_acc_table_name
125 ,p_seq_name => g_tasks_seq_name
126 ,p_pk1_name => g_pk1_name
127 ,p_pk1_num_value => l_transaction_id_lst(i)
128 ,p_pk2_name => g_pk2_name
129 ,p_pk2_char_value => l_lot_number_lst(i)
130 ,p_user_id => p_user_id
131 );
132 ELSE
133 /*** Record is already in ACC. Only an update is required for re-sending ***/
134 CSM_ACC_PKG.Update_Acc
135 ( p_publication_item_names => g_publication_item_name
136 ,p_acc_table_name => g_acc_table_name
137 ,p_user_id => p_user_id
138 ,p_access_id => l_acc_id
139 );
140 END IF;
141 END LOOP;
142 /*** Succesfull looped through recordset ***/
143 IF l_transaction_id_lst.COUNT >0 THEN
144 l_transaction_id_lst.DELETE;
145 END IF;
146 IF l_lot_number_lst.COUNT >0 THEN
147 l_lot_number_lst.DELETE;
148 END IF;
149
150 CSM_UTIL_PKG.LOG
151 ( module => g_table_name
152 , message => ' Leaving Update_MTL_trans_lot_num Procedure for TRAN ID :' || p_transaction_id
153 , log_level => FND_LOG.LEVEL_STATEMENT);
154
155 END Update_MTL_trans_lot_num;
156
157
158 PROCEDURE Delete_MTL_trans_lot_num( p_user_id NUMBER,
159 p_transaction_id NUMBER )
160 IS
161 CURSOR c_transaction_lot_number( b_transaction_id NUMBER, b_user_id NUMBER)
162 IS
163 SELECT TRANSACTION_ID, LOT_NUMBER
164 FROM CSM_MTL_TXN_LOT_NUM_ACC
165 WHERE TRANSACTION_ID = b_transaction_id
166 AND USER_ID = b_user_id;
167
168 r_transaction_lot_number c_transaction_lot_number%ROWTYPE;
169
170 BEGIN
171
172 CSM_UTIL_PKG.LOG
173 ( module => g_table_name
174 , message => ' Entering Delete_MTL_trans_lot_num Procedure for TRAN ID :' || p_transaction_id
175 , log_level => FND_LOG.LEVEL_STATEMENT);
176
177 IF l_transaction_id_lst.COUNT >0 THEN
178 l_transaction_id_lst.DELETE;
179 END IF;
180 IF l_lot_number_lst.COUNT >0 THEN
181 l_lot_number_lst.DELETE;
182 END IF;
183
184 /*** Retreive record assigned by Hook ***/
185 OPEN c_transaction_lot_number( p_transaction_id, p_user_id);
186 FETCH c_transaction_lot_number BULK COLLECT INTO l_transaction_id_lst,l_lot_number_lst;
187 CLOSE c_transaction_lot_number;
188 /*** could not find assignment record -> exit ***/
189 IF l_transaction_id_lst.COUNT > 0 THEN
190 /*** Loop over all available records and put them in the acc table ***/
191 FOR i in 1..l_transaction_id_lst.COUNT LOOP
192 /*** Call common package to insert record into ACC table ***/
193 CSM_ACC_PKG.Delete_Acc
194 ( p_publication_item_names => g_publication_item_name
195 ,p_acc_table_name => g_acc_table_name
196 ,p_user_id => p_user_id
197 ,p_pk1_name => g_pk1_name
198 ,p_pk1_num_value => l_transaction_id_lst(i)
199 ,p_pk2_name => g_pk2_name
200 ,p_pk2_char_value => l_lot_number_lst(i)
201 );
202 END LOOP;
203 END IF;
204
205 IF l_transaction_id_lst.COUNT >0 THEN
206 l_transaction_id_lst.DELETE;
207 END IF;
208 IF l_lot_number_lst.COUNT >0 THEN
209 l_lot_number_lst.DELETE;
210 END IF;
211 CSM_UTIL_PKG.LOG
212 ( module => g_table_name
213 , message => ' Leaving Delete_MTL_trans_lot_num Procedure for TRAN ID :' || p_transaction_id
214 , log_level => FND_LOG.LEVEL_STATEMENT);
215
216 END Delete_MTL_trans_lot_num;
217
218 /*Delete all records for non-existing user ( e.g user was deleted )*/
219 PROCEDURE DELETE_ALL_ACC_RECORDS( p_user_id IN NUMBER
220 , x_return_status OUT NOCOPY VARCHAR2 )
221 IS
222 BEGIN
223 CSM_UTIL_PKG.LOG
224 ( module => g_table_name
225 , message => 'Entering DELETE_ALL_ACC_RECORDS'
226 , log_level => FND_LOG.LEVEL_STATEMENT);
227
228 DELETE CSM_MTL_TXN_LOT_NUM_ACC
229 WHERE USER_ID = p_user_id;
230
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 CSM_UTIL_PKG.LOG
234 ( module => g_table_name
235 , message => 'Leaving DELETE_ALL_ACC_RECORDS'
236 , log_level => FND_LOG.LEVEL_STATEMENT);
237
238 EXCEPTION WHEN OTHERS THEN
239 /*** hook failed -> log error ***/
240 -- x_return_status := FND_API.G_RET_STS_ERROR;
241 x_return_status := FND_API.G_RET_STS_SUCCESS;
242 CSM_UTIL_PKG.LOG
243 ( module => g_table_name
244 , message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
245 , log_level => FND_LOG.LEVEL_ERROR);
246 fnd_msg_pub.Add_Exc_Msg('CSM_MTL_TXN_LOT_NUM_ACC_PKG','PROCESS_ACC',sqlerrm);
247 END DELETE_ALL_ACC_RECORDS;
248
249 END CSM_MTL_TXN_LOT_NUM_ACC_PKG;