[Home] [Help]
PACKAGE BODY: APPS.CSL_MTL_TRANS_LOT_NUM_ACC_PKG
Source
1 PACKAGE BODY CSL_MTL_TRANS_LOT_NUM_ACC_PKG AS
2 /* $Header: cslltacb.pls 115.11 2002/11/08 14:02:28 asiegers ship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_MTL_TRANS_LOT_NUM_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_TRANS_LOT_NUMBERS');
8 g_table_name CONSTANT VARCHAR2(30) := 'MTL_TRANSACTIONS_LOT_NUMBERS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'TRANSACTION_ID';
10 g_pk2_name CONSTANT VARCHAR2(30) := 'LOT_NUMBER';
11 g_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
12 g_debug_level NUMBER; -- debug level
13
14 PROCEDURE Insert_MTL_trans_lot_num(
15 p_resource_id NUMBER,
16 p_transaction_id NUMBER,
17 p_inventory_item_id NUMBER,
18 p_organization_id NUMBER
19 )
20 IS
21
22 CURSOR c_transaction_lot_number(
23 b_transaction_id NUMBER,
24 b_inventory_item_id NUMBER,
25 b_organization_id NUMBER
26 ) IS
27 SELECT TRANSACTION_ID, LOT_NUMBER
28 FROM MTL_TRANSACTION_LOT_NUMBERS
29 WHERE TRANSACTION_ID = b_transaction_id
30 AND INVENTORY_ITEM_ID = b_inventory_item_id
31 AND ORGANIZATION_ID = b_organization_id;
32
33 r_transaction_lot_number c_transaction_lot_number%ROWTYPE;
34
35 BEGIN
36 /*** get debug level ***/
37 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
38
39 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
40 jtm_message_log_pkg.Log_Msg
41 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
42 , v_object_name => g_table_name
43 , v_message => 'Entering Insert_MTL_trans_lot_num'
44 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
45 END IF;
46
47 /*** Retreive record assigned by Hook ***/
48 OPEN c_transaction_lot_number( p_transaction_id, p_inventory_item_id, p_organization_id);
49 FETCH c_transaction_lot_number INTO r_transaction_lot_number;
50 IF c_transaction_lot_number%NOTFOUND THEN
51 /*** could not find assignment record -> exit ***/
52 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
53 jtm_message_log_pkg.Log_Msg
54 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
55 , v_object_name => g_table_name
56 , v_message => 'Insert_MTL_trans_lot_num could not find lot number transactions for transaction :' || p_transaction_id ||
57 ' , ' || p_inventory_item_id || ' for resource id ' || p_resource_id
58 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
59 END IF;
60
61 CLOSE c_transaction_lot_number;
62 -- RETURN FALSE;
63 ELSE
64 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
65 jtm_message_log_pkg.Log_Msg
66 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
67 , v_object_name => g_table_name
68 , v_message => 'Inserting ACC record :' || p_transaction_id || ' , ' || p_inventory_item_id ||
69 ' for resource id ' || p_resource_id
70 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
71 END IF;
72
73 /*** Loop over all available records and put them in the acc table ***/
74 WHILE c_transaction_lot_number%FOUND LOOP
75 /*** Call common package to insert record into ACC table ***/
76 JTM_HOOK_UTIL_PKG.Insert_Acc
77 ( p_publication_item_names => g_publication_item_name
78 ,p_acc_table_name => g_acc_table_name
79 ,p_resource_id => p_resource_id
80 ,p_pk1_name => g_pk1_name
81 ,p_pk1_num_value => r_transaction_lot_number.TRANSACTION_ID
82 ,p_pk2_name => g_pk2_name
83 ,p_pk2_char_value => r_transaction_lot_number.LOT_NUMBER
84 );
85 FETCH c_transaction_lot_number INTO r_transaction_lot_number;
86 END LOOP;
87 END IF;
88
89 END Insert_MTL_trans_lot_num;
90
91
92 PROCEDURE Update_MTL_trans_lot_num(
93 p_resource_id NUMBER,
94 p_transaction_id NUMBER,
95 p_inventory_item_id NUMBER,
96 p_organization_id NUMBER
97 )
98 IS
99 CURSOR c_transaction_lot_number(
100 b_transaction_id NUMBER,
101 b_inventory_item_id NUMBER,
102 b_organization_id NUMBER
103 ) IS
104 SELECT TRANSACTION_ID, LOT_NUMBER
105 FROM MTL_TRANSACTION_LOT_NUMBERS
106 WHERE TRANSACTION_ID = b_transaction_id
107 AND INVENTORY_ITEM_ID = b_inventory_item_id
108 AND ORGANIZATION_ID = b_organization_id;
109
110 r_transaction_lot_number c_transaction_lot_number%ROWTYPE;
111
112 l_acc_id NUMBER;
113
114 BEGIN
115
116 /*** get debug level ***/
117 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
118
119 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
120 jtm_message_log_pkg.Log_Msg
121 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
122 , v_object_name => g_table_name
123 , v_message => 'Entering Update_MTL_trans_lot_num'
124 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
125 END IF;
126
127 /*** Retreive record assigned by Hook ***/
128 OPEN c_transaction_lot_number( p_transaction_id, p_inventory_item_id, p_organization_id);
129 FETCH c_transaction_lot_number INTO r_transaction_lot_number;
130 IF c_transaction_lot_number%NOTFOUND THEN
131 /*** could not find assignment record -> exit ***/
132 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
133 jtm_message_log_pkg.Log_Msg
134 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
135 , v_object_name => g_table_name
136 , v_message => 'Update_MTL_trans_lot_num could not find lot number transactions for transaction :' || p_transaction_id ||
137 ' , ' || p_inventory_item_id || ' for resource id ' || p_resource_id
138 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
139 END IF;
140
141 CLOSE c_transaction_lot_number;
142 ELSE
143 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
144 jtm_message_log_pkg.Log_Msg
145 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
146 , v_object_name => g_table_name
147 , v_message => 'Update ACC record :' || p_transaction_id || ' , ' || p_inventory_item_id ||
148 ' for resource id ' || p_resource_id
149 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
150 END IF;
151
152 /*** Loop over all available records and put them in the acc table ***/
153 WHILE c_transaction_lot_number%FOUND LOOP
154 /*** Call common package to insert record into ACC table ***/
155 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
156 ( P_ACC_TABLE_NAME => g_acc_table_name
157 ,p_pk1_name => g_pk1_name
158 ,p_pk1_num_value => r_transaction_lot_number.TRANSACTION_ID
159 ,p_pk2_name => g_pk2_name
160 ,p_pk2_char_value => r_transaction_lot_number.LOT_NUMBER
161 ,P_RESOURCE_ID => p_resource_id);
162
163
164 IF l_acc_id = -1 THEN
165 /*** Record is not yet in ACC tables. Insert has to be done ***/
166 JTM_HOOK_UTIL_PKG.Insert_Acc
167 ( p_publication_item_names => g_publication_item_name
168 ,p_acc_table_name => g_acc_table_name
169 ,p_pk1_name => g_pk1_name
170 ,p_pk1_num_value => r_transaction_lot_number.TRANSACTION_ID
171 ,p_pk2_name => g_pk2_name
172 ,p_pk2_char_value => r_transaction_lot_number.LOT_NUMBER
173 ,p_resource_id => p_resource_id
174 );
175 ELSE
176 /*** Record is already in ACC. Only an update is required for re-sending ***/
177 JTM_HOOK_UTIL_PKG.Update_Acc
178 ( p_publication_item_names => g_publication_item_name
179 ,p_acc_table_name => g_acc_table_name
180 ,p_resource_id => p_resource_id
181 ,p_access_id => l_acc_id
182 );
183 END IF;
184
185
186 FETCH c_transaction_lot_number INTO r_transaction_lot_number;
187 END LOOP;
188 /*** Succesfull looped through recordset ***/
189
190 END IF;
191
192 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
193 jtm_message_log_pkg.Log_Msg
194 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
195 , v_object_name => g_table_name
196 , v_message => 'Leaving Update_MTL_trans_lot_num'
197 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
198 END IF;
199
200
201 END Update_MTL_trans_lot_num;
202
203
204 PROCEDURE Delete_MTL_trans_lot_num(
205 p_resource_id NUMBER,
206 p_transaction_id NUMBER,
207 p_inventory_item_id NUMBER,
208 p_organization_id NUMBER
209 )
210 IS
211
212 CURSOR c_transaction_lot_number(
213 b_transaction_id NUMBER,
214 b_inventory_item_id NUMBER,
215 b_organization_id NUMBER
216 ) IS
217 SELECT TRANSACTION_ID, LOT_NUMBER
218 FROM MTL_TRANSACTION_LOT_NUMBERS
219 WHERE TRANSACTION_ID = b_transaction_id
220 AND INVENTORY_ITEM_ID = b_inventory_item_id
221 AND ORGANIZATION_ID = b_organization_id;
222
223 r_transaction_lot_number c_transaction_lot_number%ROWTYPE;
224
225 BEGIN
226
227 /*** get debug level ***/
228 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
229
230 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
231 jtm_message_log_pkg.Log_Msg
232 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
233 , v_object_name => g_table_name
234 , v_message => 'Entering Delete_MTL_trans_lot_num'
235 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
236 END IF;
237
238 /*** Retreive record assigned by Hook ***/
239 OPEN c_transaction_lot_number( p_transaction_id, p_inventory_item_id, p_organization_id);
240 FETCH c_transaction_lot_number INTO r_transaction_lot_number;
241 IF c_transaction_lot_number%NOTFOUND THEN
242 /*** could not find assignment record -> exit ***/
243 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
244 jtm_message_log_pkg.Log_Msg
245 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
246 , v_object_name => g_table_name
247 , v_message => 'Delete_MTL_trans_lot_num could not find lot number transactions for transaction :' || p_transaction_id ||
248 ' , ' || p_inventory_item_id || ' for resource id ' || p_resource_id
249 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
250 END IF;
251
252 CLOSE c_transaction_lot_number;
253 -- RETURN FALSE;
254 ELSE
255 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
256 jtm_message_log_pkg.Log_Msg
257 ( v_object_id => p_organization_id
258 , v_object_name => g_table_name
259 , v_message => 'Deleting ACC record :' || p_transaction_id || ' , ' || p_inventory_item_id ||
260 ' for resource id ' || p_resource_id
261 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
262 END IF;
263
264 /*** Loop over all available records and put them in the acc table ***/
265 WHILE c_transaction_lot_number%FOUND LOOP
266 /*** Call common package to insert record into ACC table ***/
267 JTM_HOOK_UTIL_PKG.Delete_Acc
268 ( p_publication_item_names => g_publication_item_name
269 ,p_acc_table_name => g_acc_table_name
270 ,p_resource_id => p_resource_id
271 ,p_pk1_name => g_pk1_name
272 ,p_pk1_num_value => r_transaction_lot_number.TRANSACTION_ID
273 ,p_pk2_name => g_pk2_name
274 ,p_pk2_char_value => r_transaction_lot_number.LOT_NUMBER
275 );
276 FETCH c_transaction_lot_number INTO r_transaction_lot_number;
277 END LOOP;
278 END IF;
279
280 END Delete_MTL_trans_lot_num;
281
282 /*Delete all records for non-existing user ( e.g user was deleted )*/
283 PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
284 , x_return_status OUT NOCOPY VARCHAR2 )
285 IS
286 BEGIN
287 /*** get debug level ***/
288 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
289 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
290 jtm_message_log_pkg.Log_Msg
291 ( p_resource_id
292 , g_table_name
293 , 'Entering DELETE_ALL_ACC_RECORDS'
294 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
295 );
296 END IF;
297
298 DELETE JTM_MTL_TRANS_LOT_NUM_ACC
299 WHERE RESOURCE_ID = p_resource_id;
300
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
304 jtm_message_log_pkg.Log_Msg
305 ( p_resource_id
306 , g_table_name
307 , 'Leaving DELETE_ALL_ACC_RECORDS'
308 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
309 );
310 END IF;
311 EXCEPTION WHEN OTHERS THEN
312 /*** hook failed -> log error ***/
313 -- x_return_status := FND_API.G_RET_STS_ERROR;
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
316 jtm_message_log_pkg.Log_Msg
317 ( v_object_id => 1
318 , v_object_name => g_table_name
319 , v_message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
320 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
321 END IF;
322 fnd_msg_pub.Add_Exc_Msg('CSL_MTL_TRANS_LOT_NUM_ACC_PKG','PROCESS_ACC',sqlerrm);
323 END DELETE_ALL_ACC_RECORDS;
324
325 END CSL_MTL_TRANS_LOT_NUM_ACC_PKG;