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