[Home] [Help]
PACKAGE BODY: APPS.CSL_MATERIAL_TRANSACTION_PKG
Source
1 PACKAGE BODY CSL_MATERIAL_TRANSACTION_PKG AS
2 /* $Header: cslvmmtb.pls 115.17 2002/11/08 14:00:33 asiegers ship $ */
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSL_MATERIAL_TRANSACTION_PKG';
8 g_pub_name CONSTANT VARCHAR2(30) := 'MTL_MAT_TRANSACTIONS';
9 g_debug_level NUMBER; -- debug level
10
11 CURSOR c_material_transaction( b_user_name VARCHAR2, b_tranid NUMBER) is
12 SELECT *
13 FROM CSL_MTL_MAT_TRANSACTIONS_INQ
14 WHERE tranid$$ = b_tranid
15 AND clid$$cs = b_user_name;
16
17 CURSOR c_lot_number( b_user_name VARCHAR2, b_tranid NUMBER, b_transaction_id NUMBER) is
18 SELECT *
19 FROM CSL_MTL_TRANS_LOT_NUM_INQ
20 WHERE tranid$$ = b_tranid
21 AND clid$$cs = b_user_name
22 AND transaction_id = b_transaction_id;
23
24 CURSOR c_unit_transaction( b_user_name VARCHAR2, b_tranid NUMBER, b_transaction_id NUMBER) is
25 SELECT *
26 FROM CSL_MTL_UNIT_TRANS_INQ
27 WHERE tranid$$ = b_tranid
28 AND clid$$cs = b_user_name
29 AND transaction_id = b_transaction_id;
30
31 /***
32 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
33 ***/
34 PROCEDURE APPLY_INSERT
35 (
36 p_record IN c_material_transaction%ROWTYPE,
37 p_error_msg OUT NOCOPY VARCHAR2,
38 x_return_status IN OUT NOCOPY VARCHAR2
39 )
40 IS
41
42 l_lot_number c_lot_number%ROWTYPE;
43 l_serial_number c_unit_transaction%ROWTYPE;
44 l_msg_count NUMBER;
45 l_msg_data VARCHAR2(240);
46 l_mat_txn_transfer_id NUMBER;
47 l_transaction_id NUMBER := NULL;
48
49 /*** Object needed for pushing data with new pk to client ***/
50 CURSOR c_transactions( b_transaction_set_id NUMBER ) IS
51 SELECT TRANSACTION_ID
52 , INVENTORY_ITEM_ID
53 , ORGANIZATION_ID
54 , SUBINVENTORY_CODE
55 FROM MTL_MATERIAL_TRANSACTIONS
56 WHERE TRANSACTION_SET_ID = b_transaction_set_id;
57
58 CURSOR c_resource( b_client_name VARCHAR2 ) IS
59 SELECT RESOURCE_ID
60 FROM ASG_USER
61 WHERE USER_NAME = b_client_name;
62 r_resource c_resource%ROWTYPE;
63
64 l_pub_item_name_mat CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
65 JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_MAT_TRANSACTIONS');
66 BEGIN
67 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
68 jtm_message_log_pkg.Log_Msg
69 ( v_object_id => p_record.TRANSACTION_ID
70 , v_object_name => g_object_name
71 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
72 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
73 END IF;
74
75 -- Initialization
76 l_transaction_id := p_record.transaction_id;
77
78 OPEN c_lot_number( p_record.clid$$cs, p_record.tranid$$, p_record.transaction_id );
79 FETCH c_lot_number INTO l_lot_number;
80 IF c_lot_number%NOTFOUND THEN
81 l_lot_number := NULL;
82 END IF;
83 CLOSE c_lot_number;
84
85 OPEN c_unit_transaction( p_record.clid$$cs, p_record.tranid$$, p_record.transaction_id );
86 FETCH c_unit_transaction INTO l_serial_number;
87 IF c_unit_transaction%NOTFOUND THEN
88 l_serial_number := NULL;
89 END IF;
90 CLOSE c_unit_transaction;
91
92 -- In the application the qty is inverted during insert to reflect the
93 -- same behaviour as when the record comes from apps. This inversion
94 -- should be undone in the wrapper hence transaction_quantity * -1
95 -- ( GG 05-FEB-2002 )
96 csp_transactions_pub.transact_material
97 ( p_api_version => 1.0
98 , p_init_msg_list => FND_API.G_TRUE
99 , p_commit => FND_API.G_FALSE
100 , px_transaction_id => l_transaction_id
101 , px_transaction_header_id => l_mat_txn_transfer_id
102 , p_inventory_item_id => p_record.inventory_item_id
103 , p_organization_id => p_record.organization_id
104 , p_subinventory_code => p_record.subinventory_code
105 , p_locator_id => p_record.locator_id
106 , p_lot_number => l_lot_number.lot_number
107 , p_revision => p_record.revision
108 , p_serial_number => l_serial_number.serial_number
109 , p_quantity => -(p_record.transaction_quantity)
110 , p_uom => p_record.transaction_uom
111 , p_source_id => NULL
112 , p_source_line_id => NULL
113 , p_transaction_type_id => p_record.transaction_type_id
114 , p_account_id => NULL
115 , p_transfer_to_subinventory => p_record.transfer_subinventory
116 , p_transfer_to_locator => p_record.transfer_locator_id
117 , p_transfer_to_organization => p_record.transfer_organization_id
118 , p_transaction_source_id => NULL
119 , p_trx_source_line_id => NULL
120 , p_reason_id => p_record.reason_id
121 , p_transaction_reference => p_record.transaction_reference
122 , x_return_status => x_return_status
123 , x_msg_count => l_msg_count
124 , x_msg_data => l_msg_data
125 );
126
127 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
128 /*** exception occurred in API -> return errmsg ***/
129 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
130 (
131 p_api_error => TRUE
132 );
133 ELSE
134 /*** Record was succesfull applied now push records with new PK to client ***/
135 /*** Use the transaction_set_id (px_transaction_header_id) because the
136 transaction_id is not returned by the INV API ***/
137 FOR r_transaction IN c_transactions( l_mat_txn_transfer_id ) LOOP
138 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
139 jtm_message_log_pkg.Log_Msg
140 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
141 , v_object_name => g_object_name
142 , v_message => 'Found new transaction_id ' || r_transaction.transaction_id||
143 ', Pushing these to the client'
144 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
145 END IF;
146
147 /*** Get the resource ***/
148 OPEN c_resource( p_record.CLID$$CS );
149 FETCH c_resource INTO r_resource;
150 IF c_resource%FOUND THEN
151
152 /*** Pushing record ***/
153 JTM_HOOK_UTIL_PKG.Insert_Acc
154 ( p_publication_item_names => l_pub_item_name_mat
155 ,p_acc_table_name => 'JTM_MTL_MAT_TRANS_ACC'
156 ,p_resource_id => r_resource.resource_id
157 ,p_pk1_name => 'TRANSACTION_ID'
158 ,p_pk1_num_value => r_transaction.TRANSACTION_ID
159 );
160 /*** Check if the record was serialized or lot controlled ***/
161 CSL_MTL_TRANS_LOT_NUM_ACC_PKG.Insert_MTL_trans_lot_num(
162 p_resource_id => r_resource.resource_id,
163 p_transaction_id => r_transaction.TRANSACTION_ID,
164 p_inventory_item_id => r_transaction.inventory_item_id,
165 p_organization_id => r_transaction.organization_id
166 );
167 CSL_MTL_UNIT_TRANS_ACC_PKG.Insert_MTL_Unit_Trans(
168 p_resource_id => r_resource.resource_id,
169 p_transaction_id => r_transaction.TRANSACTION_ID,
170 p_inventory_item_id => r_transaction.inventory_item_id,
171 p_organization_id => r_transaction.organization_id,
172 p_subinventory_code => r_transaction.subinventory_code
173 );
174 END IF;
175 CLOSE c_resource;
176
177 END LOOP;
178 END IF;
179
180 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
181 jtm_message_log_pkg.Log_Msg
182 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
183 , v_object_name => g_object_name
184 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
185 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
186 END IF;
187
188 EXCEPTION WHEN OTHERS THEN
189 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
190 jtm_message_log_pkg.Log_Msg
191 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
192 , v_object_name => g_object_name
193 , v_message => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
194 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
195 END IF;
196
197 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
198 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
199 (
200 p_api_error => TRUE
201 );
202
203 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
204 jtm_message_log_pkg.Log_Msg
205 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
206 , v_object_name => g_object_name
207 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
208 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
209 END IF;
210
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 END APPLY_INSERT;
213
214 /***
215 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
216 ***/
217 PROCEDURE APPLY_UPDATE
218 (
219 p_record IN c_material_transaction%ROWTYPE,
220 p_error_msg OUT NOCOPY VARCHAR2,
221 x_return_status IN OUT NOCOPY VARCHAR2
222 ) IS
223 BEGIN
224 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
225 jtm_message_log_pkg.Log_Msg
226 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
227 , v_object_name => g_object_name
228 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
229 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
230 END IF;
231
232 -- There is no update possible so returning SUCCESS
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234
235 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
236 /*** exception occurred in API -> return errmsg ***/
237 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
238 (
239 p_api_error => TRUE
240 );
241 END IF;
242
243 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
244 jtm_message_log_pkg.Log_Msg
245 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
246 , v_object_name => g_object_name
247 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
248 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
249 END IF;
250
251 EXCEPTION WHEN OTHERS THEN
252 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
253 jtm_message_log_pkg.Log_Msg
254 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
255 , v_object_name => g_object_name
256 , v_message => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
257 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
258 END IF;
259
260 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
261 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
262 (
263 p_api_error => TRUE
264 );
265
266 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
267 jtm_message_log_pkg.Log_Msg
268 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
269 , v_object_name => g_object_name
270 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
271 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
272 END IF;
273
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 END APPLY_UPDATE;
276
277 /***
278 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
279 ***/
280 PROCEDURE APPLY_RECORD
281 (
282 p_record IN c_material_transaction%ROWTYPE,
283 p_error_msg OUT NOCOPY VARCHAR2,
284 x_return_status IN OUT NOCOPY VARCHAR2
285 ) IS
286 BEGIN
287 /*** initialize return status and message list ***/
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289 FND_MSG_PUB.INITIALIZE;
290
291 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
292 jtm_message_log_pkg.Log_Msg
293 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
294 , v_object_name => g_object_name
295 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
296 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
297 END IF;
298
299 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
300 jtm_message_log_pkg.Log_Msg
301 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
302 , v_object_name => g_object_name
303 , v_message => 'Processing TRANSACTION_ID = ' || p_record.TRANSACTION_ID || fnd_global.local_chr(10) ||
304 'DMLTYPE = ' || p_record.dmltype$$
305 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
306 END IF;
307
308 IF p_record.dmltype$$='I' THEN
309 -- Process insert
310 APPLY_INSERT
311 (
312 p_record,
313 p_error_msg,
314 x_return_status
315 );
316 ELSIF p_record.dmltype$$='U' THEN
317 -- Process update
318 APPLY_UPDATE
319 (
320 p_record,
321 p_error_msg,
322 x_return_status
323 );
324 ELSIF p_record.dmltype$$='D' THEN
325 -- Process delete; not supported for this entity
326 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
327 jtm_message_log_pkg.Log_Msg
328 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
329 , v_object_name => g_object_name
330 , v_message => 'Delete is not supported for this entity'
331 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
332 END IF;
333
334 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
335 (
336 p_message => 'CSL_DML_OPERATION'
337 , p_token_name1 => 'DML'
338 , p_token_value1 => p_record.dmltype$$
339 );
340
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 ELSE
343 -- invalid dml type
344 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
345 jtm_message_log_pkg.Log_Msg
346 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
347 , v_object_name => g_object_name
348 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
349 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
350 END IF;
351
352 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
353 (
354 p_message => 'CSL_DML_OPERATION'
355 , p_token_name1 => 'DML'
356 , p_token_value1 => p_record.dmltype$$
357 );
358
359 x_return_status := FND_API.G_RET_STS_ERROR;
360 END IF;
364 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
361
362 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
363 jtm_message_log_pkg.Log_Msg
365 , v_object_name => g_object_name
366 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
367 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
368 END IF;
369 EXCEPTION WHEN OTHERS THEN
370 /*** defer record when any process exception occurs ***/
371 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
372 jtm_message_log_pkg.Log_Msg
373 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
374 , v_object_name => g_object_name
375 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
376 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
377 END IF;
378
379 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
380 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
381 (
382 p_api_error => TRUE
383 );
384
385 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
386 jtm_message_log_pkg.Log_Msg
387 ( v_object_id => p_record.TRANSACTION_ID -- put PK column here
388 , v_object_name => g_object_name
389 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
390 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
391 END IF;
392
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 END APPLY_RECORD;
395
396 /***
397 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item MTL_MAT_TRANSACTIONS
398 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
399 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
400 public APIs.
401 ***/
402 PROCEDURE APPLY_CLIENT_CHANGES
403 (
404 p_user_name IN VARCHAR2,
405 p_tranid IN NUMBER,
406 p_debug_level IN NUMBER,
407 x_return_status IN OUT NOCOPY VARCHAR2
408 ) IS
409
410 l_process_status VARCHAR2(1);
411 l_error_msg VARCHAR2(4000);
412 BEGIN
413 g_debug_level := p_debug_level;
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415
416 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
417 jtm_message_log_pkg.Log_Msg
418 ( v_object_id => null
419 , v_object_name => g_object_name
420 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
421 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
422 END IF;
423
424 /*** loop through MTL_MAT_TRANSACTIONS records in inqueue ***/
425 FOR r_material_transaction IN c_material_transaction( p_user_name, p_tranid) LOOP
426
427 SAVEPOINT save_rec;
428
429 /*** apply record ***/
430 APPLY_RECORD
431 (
432 r_material_transaction
433 , l_error_msg
434 , l_process_status
435 );
436
437 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
438 /*** Yes -> reject record because of changed pk ***/
439 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
440 jtm_message_log_pkg.Log_Msg
441 ( v_object_id => r_material_transaction.transaction_id
442 , v_object_name => g_object_name
443 , v_message => 'Record successfully processed, rejecting record because pk is changed'
444 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
445 END IF;
446
447 CSL_SERVICEL_WRAPPER_PKG.REJECT_RECORD
448 (
449 p_user_name,
450 p_tranid,
451 r_material_transaction.seqno$$,
452 r_material_transaction.transaction_id,
453 g_object_name,
454 g_pub_name,
455 l_error_msg,
456 l_process_status
457 );
458
459 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
460 /*** Reject successfull now rejecting matching serial/lotnumber records ***/
461 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
462 jtm_message_log_pkg.Log_Msg
463 ( v_object_id => r_material_transaction.transaction_id
464 , v_object_name => g_object_name
465 , v_message => 'Record rejected, now rejecting available matching lot-/serialnumber records'
466 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
467 END IF;
468
469 FOR r_lot_number IN c_lot_number( p_user_name,
470 p_tranid,
471 r_material_transaction.transaction_id ) LOOP
472 CSL_SERVICEL_WRAPPER_PKG.REJECT_RECORD
473 (
474 p_user_name,
475 r_lot_number.tranid$$,
476 r_lot_number.seqno$$,
477 r_lot_number.tranid$$,
478 g_object_name,
479 'MTL_TRANS_LOT_NUMBERS',
480 l_error_msg,
481 l_process_status
482 );
483 END LOOP;
484
485 FOR r_unit_transaction IN c_unit_transaction( p_user_name,
486 p_tranid,
487 r_material_transaction.transaction_id ) LOOP
491 r_unit_transaction.tranid$$,
488 CSL_SERVICEL_WRAPPER_PKG.REJECT_RECORD
489 (
490 p_user_name,
492 r_unit_transaction.seqno$$,
493 r_unit_transaction.tranid$$,
494 g_object_name,
495 'MTL_UNIT_TRANSACTIONS',
496 l_error_msg,
497 l_process_status
498 );
499 END LOOP;
500 END IF;
501
502 /*** was record processed successfully? ***/
503 /*IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN*/
504 /*** Yes -> delete record from inqueue ***/
505 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
506 jtm_message_log_pkg.Log_Msg
507 ( v_object_id => r_material_transaction.TRANSACTION_ID
508 , v_object_name => g_object_name
509 , v_message => 'Record successfully processed, deleting from inqueue'
510 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
511 END IF;
512
513 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
514 (
515 p_user_name,
516 p_tranid,
517 r_material_transaction.seqno$$,
518 r_material_transaction.TRANSACTION_ID,
519 g_object_name,
520 g_pub_name,
521 l_error_msg,
522 l_process_status
523 );
524
525 /*** was delete successful? ***/
526 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
527 /*** no -> rollback ***/
528 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
529 jtm_message_log_pkg.Log_Msg
530 ( v_object_id => r_material_transaction.TRANSACTION_ID
531 , v_object_name => g_object_name
532 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
533 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
534 END IF;
535 ROLLBACK TO save_rec;
536 END IF;
537
538 FOR r_lot_number IN c_lot_number( p_user_name, p_tranid, r_material_transaction.transaction_id ) LOOP
539 /* Delete matching contact record(s) */
540 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
541 (
542 p_user_name,
543 p_tranid,
544 r_lot_number.seqno$$,
545 r_lot_number.tranid$$,
546 g_object_name,
547 'MTL_TRANS_LOT_NUMBERS',
548 l_error_msg,
549 l_process_status
550 );
551 /*** was delete successful? ***/
552 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
553 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
554 jtm_message_log_pkg.Log_Msg
555 ( v_object_id => r_lot_number.tranid$$
556 , v_object_name => g_object_name || 'MTL_TRANS_LOT_NUMBERS'
557 , v_message => 'Deleting from inqueue failed, Defer and reject record'
558 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
559 END IF;
560 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
561 (
562 p_user_name
563 , p_tranid
564 , r_lot_number.seqno$$
565 , r_lot_number.tranid$$
566 , g_object_name
567 , 'MTL_TRANS_LOT_NUMBERS'
568 , l_error_msg
569 , l_process_status
570 );
571 END IF;
572 END LOOP;
573
574 FOR r_unit_transaction IN c_unit_transaction( p_user_name,
575 p_tranid,
576 r_material_transaction.transaction_id ) LOOP
577 /* Delete matching contact record(s) */
578 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
579 (
580 p_user_name,
581 p_tranid,
582 r_unit_transaction.seqno$$,
583 r_unit_transaction.tranid$$,
584 g_object_name,
585 'MTL_UNIT_TRANSACTIONS',
586 l_error_msg,
587 l_process_status
588 );
589 /*** was delete successful? ***/
590 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
591 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
592 jtm_message_log_pkg.Log_Msg
593 ( v_object_id => r_unit_transaction.tranid$$
594 , v_object_name => g_object_name || 'MTL_UNIT_TRANSACTIONS'
595 , v_message => 'Deleting from inqueue failed, Defer and reject record'
596 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
597 END IF;
598 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
599 (
600 p_user_name
601 , p_tranid
602 , r_unit_transaction.seqno$$
603 , r_unit_transaction.tranid$$
604 , g_object_name
605 , 'MTL_UNIT_TRANSACTIONS'
606 , l_error_msg
607 , l_process_status
608 );
609 END IF;
610 END LOOP;
611
612 END IF;
613
614 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
615 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
616 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
617 jtm_message_log_pkg.Log_Msg
618 ( v_object_id => r_material_transaction.TRANSACTION_ID
619 , v_object_name => g_object_name
620 , v_message => 'Record not processed successfully, deferring and rejecting record'
621 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
622 END IF;
623
624 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
625 (
626 p_user_name
627 , p_tranid
628 , r_material_transaction.seqno$$
629 , r_material_transaction.TRANSACTION_ID
630 , g_object_name
631 , g_pub_name
632 , l_error_msg
633 , l_process_status
634 );
635
636 /*** Was defer successful? ***/
637 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
638 /*** no -> rollback ***/
639 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
640 jtm_message_log_pkg.Log_Msg
641 ( v_object_id => r_material_transaction.TRANSACTION_ID
642 , v_object_name => g_object_name
643 , v_message => 'Defer record failed, rolling back to savepoint'
644 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
645 END IF;
646 ROLLBACK TO save_rec;
647 END IF;
648
649 FOR r_lot_number IN c_lot_number( p_user_name, p_tranid, r_material_transaction.transaction_id ) LOOP
650 /* Defering matching contact record(s) */
651 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
652 (
653 p_user_name
654 , p_tranid
655 , r_lot_number.seqno$$
656 , r_lot_number.tranid$$
657 , g_object_name
658 , 'MTL_TRANS_LOT_NUMBERS'
659 , l_error_msg
660 , l_process_status
661 );
662 END LOOP;
663
664 FOR r_unit_transaction IN c_unit_transaction( p_user_name,
665 p_tranid,
666 r_material_transaction.transaction_id ) LOOP
667 /* Defering matching contact record(s) */
668 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
669 (
670 p_user_name
671 , p_tranid
672 , r_unit_transaction.seqno$$
673 , r_unit_transaction.tranid$$
674 , g_object_name
675 , 'MTL_UNIT_TRANSACTIONS'
676 , l_error_msg
677 , l_process_status
678 );
679 END LOOP;
680 END IF;
681 END LOOP;
682
683 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
684 jtm_message_log_pkg.Log_Msg
685 ( v_object_id => null
686 , v_object_name => g_object_name
687 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
688 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
689 END IF;
690
691 EXCEPTION WHEN OTHERS THEN
692 /*** catch and log exceptions ***/
693 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
694 jtm_message_log_pkg.Log_Msg
695 ( v_object_id => null
696 , v_object_name => g_object_name
697 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
698 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
699 END IF;
700 x_return_status := FND_API.G_RET_STS_ERROR;
701 END APPLY_CLIENT_CHANGES;
702
703 END CSL_MATERIAL_TRANSACTION_PKG;