[Home] [Help]
PACKAGE BODY: APPS.CSM_MATERIAL_TRANSACTION_PKG
Source
1 PACKAGE BODY CSM_MATERIAL_TRANSACTION_PKG AS
2 /* $Header: csmvmmtb.pls 120.0 2006/02/16 04:23:47 utekumal noship $ */
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSM_MATERIAL_TRANSACTION_PKG';
8 g_mat_pub_name CONSTANT VARCHAR2(30) := 'CSM_MTL_MATERIAL_TXNS';
9 g_lot_pub_name CONSTANT VARCHAR2(30) := 'CSM_MTL_TXNS_LOT_NUM';
10 g_unit_pub_name CONSTANT VARCHAR2(30) := 'CSM_MTL_UNIT_TXNS';
11
12 CURSOR c_material_transaction( b_user_name VARCHAR2, b_tranid NUMBER) is
13 SELECT *
14 FROM CSM_MTL_MATERIAL_TXNS_INQ
15 WHERE tranid$$ = b_tranid
16 AND clid$$cs = b_user_name;
17
18 CURSOR c_lot_number( b_user_name VARCHAR2, b_tranid NUMBER, b_transaction_id NUMBER) is
19 SELECT *
20 FROM CSM_MTL_TXNS_LOT_NUM_INQ
21 WHERE tranid$$ = b_tranid
22 AND clid$$cs = b_user_name
23 AND transaction_id = b_transaction_id;
24
25 CURSOR c_unit_transaction( b_user_name VARCHAR2, b_tranid NUMBER, b_transaction_id NUMBER) is
26 SELECT *
27 FROM CSM_MTL_UNIT_TXNS_INQ
28 WHERE tranid$$ = b_tranid
29 AND clid$$cs = b_user_name
30 AND transaction_id = b_transaction_id;
31
32 /***
33 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
34 ***/
35 PROCEDURE APPLY_INSERT
36 (
37 p_record IN c_material_transaction%ROWTYPE,
38 p_error_msg OUT NOCOPY VARCHAR2,
39 x_return_status IN OUT NOCOPY VARCHAR2
40 )
41 IS
42
43 l_lot_number c_lot_number%ROWTYPE;
44 l_serial_number c_unit_transaction%ROWTYPE;
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(240);
47 l_mat_txn_transfer_id NUMBER;
48 l_transaction_id NUMBER := NULL;
49
50 /*** Object needed for pushing data with new pk to client ***/
51 CURSOR c_transactions( b_transaction_set_id NUMBER ) IS
52 SELECT TRANSACTION_ID
53 , INVENTORY_ITEM_ID
54 , ORGANIZATION_ID
55 , SUBINVENTORY_CODE
56 FROM MTL_MATERIAL_TRANSACTIONS
57 WHERE TRANSACTION_SET_ID = b_transaction_set_id;
58
59 CURSOR c_resource( b_client_name VARCHAR2 ) IS
60 SELECT RESOURCE_ID
61 FROM ASG_USER
62 WHERE USER_NAME = b_client_name;
63 r_resource c_resource%ROWTYPE;
64
65 l_pub_item_name_mat CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
66 JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_MAT_TRANSACTIONS');
67 BEGIN
68 CSM_UTIL_PKG.LOG
69 ( module => g_object_name
70 , message => p_record.TRANSACTION_ID || ' Entering ' || g_object_name || '.APPLY_INSERT'
71 , log_level => FND_LOG.LEVEL_STATEMENT);
72
73 -- Initialization
74 l_transaction_id := p_record.transaction_id;
75
76 OPEN c_lot_number( p_record.clid$$cs, p_record.tranid$$, p_record.transaction_id );
77 FETCH c_lot_number INTO l_lot_number;
78 IF c_lot_number%NOTFOUND THEN
79 l_lot_number := NULL;
80 END IF;
81 CLOSE c_lot_number;
82
83 OPEN c_unit_transaction( p_record.clid$$cs, p_record.tranid$$, p_record.transaction_id );
84 FETCH c_unit_transaction INTO l_serial_number;
85 IF c_unit_transaction%NOTFOUND THEN
86 l_serial_number := NULL;
87 END IF;
88 CLOSE c_unit_transaction;
89
90 -- In the application the qty is inverted during insert to reflect the
91 -- same behaviour as when the record comes from apps. This inversion
92 -- should be undone in the wrapper hence transaction_quantity * -1
93 csp_transactions_pub.transact_material
94 ( p_api_version => 1.0
95 , p_init_msg_list => FND_API.G_TRUE
96 , p_commit => FND_API.G_FALSE
97 , px_transaction_id => l_transaction_id
98 , px_transaction_header_id => l_mat_txn_transfer_id
99 , p_inventory_item_id => p_record.inventory_item_id
100 , p_organization_id => p_record.organization_id
101 , p_subinventory_code => p_record.subinventory_code
102 , p_locator_id => p_record.locator_id
103 , p_lot_number => l_lot_number.lot_number
104 , p_revision => p_record.revision
105 , p_serial_number => l_serial_number.serial_number
106 , p_quantity => -(p_record.transaction_quantity)
107 , p_uom => p_record.transaction_uom
108 , p_source_id => NULL
109 , p_source_line_id => NULL
110 , p_transaction_type_id => p_record.transaction_type_id
111 , p_account_id => NULL
112 , p_transfer_to_subinventory => p_record.transfer_subinventory
113 , p_transfer_to_locator => p_record.transfer_locator_id
114 , p_transfer_to_organization => p_record.transfer_organization_id
115 , p_transaction_source_id => NULL
116 , p_trx_source_line_id => NULL
117 , p_reason_id => p_record.reason_id
118 , p_transaction_reference => p_record.transaction_reference
119 , x_return_status => x_return_status
120 , x_msg_count => l_msg_count
121 , x_msg_data => l_msg_data
122 );
123
124 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
125 /*** exception occurred in API -> return errmsg ***/
126 CSM_UTIL_PKG.LOG
127 ( module => g_object_name
128 , message => 'transaction_id ' || l_transaction_id || ' errored out with msg: ' || l_msg_data
129 , log_level => FND_LOG.LEVEL_PROCEDURE);
130
131 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
132 (
133 p_api_error => TRUE
134 );
135 END IF;
136
137 CSM_UTIL_PKG.LOG
138 ( module => g_object_name
139 , message => p_record.TRANSACTION_ID || ' Leaving ' || g_object_name || '.APPLY_INSERT'
140 , log_level => FND_LOG.LEVEL_STATEMENT);
141
142 EXCEPTION WHEN OTHERS THEN
143 CSM_UTIL_PKG.LOG
144 ( module => g_object_name
145 , message => p_record.TRANSACTION_ID || ' Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
146 , log_level => FND_LOG.LEVEL_ERROR);
147
148 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
149 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
150 (
151 p_api_error => TRUE
152 );
153
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 END APPLY_INSERT;
156
157 /***
158 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
159 ***/
160 PROCEDURE APPLY_UPDATE
161 (
162 p_record IN c_material_transaction%ROWTYPE,
163 p_error_msg OUT NOCOPY VARCHAR2,
164 x_return_status IN OUT NOCOPY VARCHAR2
165 ) IS
166 BEGIN
167 CSM_UTIL_PKG.LOG
168 ( module => g_object_name
169 , message => p_record.TRANSACTION_ID || ' Entering ' || g_object_name || '.APPLY_UPDATE'
170 , log_level => FND_LOG.LEVEL_STATEMENT);
171
172 -- There is no update possible so returning SUCCESS
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174
175 CSM_UTIL_PKG.LOG
176 ( module => g_object_name
177 , message => p_record.TRANSACTION_ID || ' Leaving ' || g_object_name || '.APPLY_UPDATE'
178 , log_level => FND_LOG.LEVEL_STATEMENT);
179
180 EXCEPTION WHEN OTHERS THEN
181 CSM_UTIL_PKG.LOG
182 ( module => g_object_name
183 , message => p_record.TRANSACTION_ID || ' Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
184 , log_level => FND_LOG.LEVEL_ERROR);
185
186 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
187 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
188 (
189 p_api_error => TRUE
190 );
191
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 END APPLY_UPDATE;
194
195 /***
196 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
197 ***/
198 PROCEDURE APPLY_RECORD
199 (
200 p_record IN c_material_transaction%ROWTYPE,
201 p_error_msg OUT NOCOPY VARCHAR2,
202 x_return_status IN OUT NOCOPY VARCHAR2
203 ) IS
204 BEGIN
205 /*** initialize return status and message list ***/
206 x_return_status := FND_API.G_RET_STS_SUCCESS;
207 FND_MSG_PUB.INITIALIZE;
208
209 CSM_UTIL_PKG.LOG
210 ( module => g_object_name
211 , message => 'Entering ' || g_object_name || '.APPLY_RECORD and processing TRANSACTION_ID = ' || p_record.TRANSACTION_ID || fnd_global.local_chr(10) ||
212 'DMLTYPE = ' || p_record.dmltype$$
213 , log_level => FND_LOG.LEVEL_STATEMENT);
214
215 IF p_record.dmltype$$='I' THEN
216 -- Process insert
217 APPLY_INSERT
218 (
219 p_record,
220 p_error_msg,
221 x_return_status
222 );
223 ELSE
224 -- Updates/Deletes are not supported for this entity
225 CSM_UTIL_PKG.LOG
226 ( module => g_object_name
227 , message => p_record.TRANSACTION_ID || ' Updates and Deletes is not supported for this entity'
228 , log_level => FND_LOG.LEVEL_ERROR);
229
230 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
231 (
232 p_message => 'CSL_DML_OPERATION'
233 , p_token_name1 => 'DML'
234 , p_token_value1 => p_record.dmltype$$
235 );
236
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 END IF;
239
240 CSM_UTIL_PKG.LOG
241 ( module => g_object_name
242 , message => p_record.TRANSACTION_ID || ' Leaving ' || g_object_name || '.APPLY_RECORD'
243 , log_level => FND_LOG.LEVEL_STATEMENT);
244
245 EXCEPTION WHEN OTHERS THEN
246 /*** defer record when any process exception occurs ***/
247 CSM_UTIL_PKG.LOG
248 ( module => g_object_name
249 , message => p_record.TRANSACTION_ID || ' Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
250 , log_level => FND_LOG.LEVEL_ERROR);
251
252 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
253 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
254 (
255 p_api_error => TRUE
256 );
257
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 END APPLY_RECORD;
260
261 /***
262 This procedure is called by CSM_UTIL_PKG when publication item MTL_MAT_TRANSACTIONS
263 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
264 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
265 public APIs.
266 ***/
267 PROCEDURE APPLY_CLIENT_CHANGES
268 (
269 p_user_name IN VARCHAR2,
270 p_tranid IN NUMBER,
271 p_debug_level IN NUMBER,
272 x_return_status IN OUT NOCOPY VARCHAR2
273 ) IS
274
275 l_process_status VARCHAR2(1);
276 l_error_msg VARCHAR2(4000);
277 BEGIN
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279
280 CSM_UTIL_PKG.LOG
281 ( module => g_object_name
282 , message => ' Entering ' || g_object_name || '.Apply_Client_Changes for user: ' ||
283 p_user_name || ' and tran id: ' || p_tranid
284 , log_level => FND_LOG.LEVEL_STATEMENT);
285
286 /*** loop through MTL_MAT_TRANSACTIONS records in inqueue ***/
287 FOR r_material_transaction IN c_material_transaction( p_user_name, p_tranid) LOOP
288
289 SAVEPOINT save_rec;
290
291 /*** apply record ***/
292 APPLY_RECORD
293 (
294 r_material_transaction
295 , l_error_msg
296 , l_process_status
297 );
298
299 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
300 /*** Yes -> reject record because of changed pk ***/
301 CSM_UTIL_PKG.LOG
302 ( module => g_object_name
303 , message => r_material_transaction.TRANSACTION_ID || ' Record successfully processed, rejecting record because pk is changed'
304 , log_level => FND_LOG.LEVEL_PROCEDURE);
305
306 CSM_UTIL_PKG.REJECT_RECORD
307 (
308 p_user_name,
309 p_tranid,
310 r_material_transaction.seqno$$,
311 r_material_transaction.transaction_id,
312 g_object_name,
313 g_mat_pub_name,
314 l_error_msg,
315 l_process_status
316 );
317
318 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
319 /*** Reject successfull now rejecting matching serial/lotnumber records ***/
320 CSM_UTIL_PKG.LOG
321 ( module => g_object_name
322 , message => r_material_transaction.TRANSACTION_ID || ' Record rejected, now rejecting available matching lot-/serialnumber records'
323 , log_level => FND_LOG.LEVEL_PROCEDURE);
324
325 FOR r_lot_number IN c_lot_number( p_user_name,
326 p_tranid,
327 r_material_transaction.transaction_id ) LOOP
328 CSM_UTIL_PKG.REJECT_RECORD
329 (
330 p_user_name,
331 r_lot_number.tranid$$,
332 r_lot_number.seqno$$,
333 r_lot_number.TRANSACTION_ID,
334 g_object_name,
335 g_lot_pub_name,
336 l_error_msg,
337 l_process_status
338 );
339 END LOOP;
340
341 FOR r_unit_transaction IN c_unit_transaction( p_user_name,
342 p_tranid,
343 r_material_transaction.transaction_id ) LOOP
344 CSM_UTIL_PKG.REJECT_RECORD
345 (
346 p_user_name,
347 r_unit_transaction.tranid$$,
348 r_unit_transaction.seqno$$,
349 r_unit_transaction.TRANSACTION_ID,
350 g_object_name,
351 g_unit_pub_name,
352 l_error_msg,
353 l_process_status
354 );
355 END LOOP;
356 END IF;
357
358 /*** was record processed successfully? ***/
359 /*IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN*/
360 /*** Yes -> delete record from inqueue ***/
361 CSM_UTIL_PKG.LOG
362 ( module => g_object_name
363 , message => r_material_transaction.TRANSACTION_ID || ' Record successfully processed, deleting from inqueue'
364 , log_level => FND_LOG.LEVEL_PROCEDURE);
365
366 CSM_UTIL_PKG.DELETE_RECORD
367 (
368 p_user_name,
369 p_tranid,
370 r_material_transaction.seqno$$,
371 r_material_transaction.TRANSACTION_ID,
372 g_object_name,
373 g_mat_pub_name,
374 l_error_msg,
375 l_process_status
376 );
377
378 /*** was delete successful? ***/
379 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
380 /*** no -> rollback ***/
381 CSM_UTIL_PKG.LOG
382 ( module => g_object_name
383 , message => r_material_transaction.TRANSACTION_ID || ' Deleting from inqueue failed, rolling back to savepoint'
384 , log_level => FND_LOG.LEVEL_PROCEDURE);
385
386 ROLLBACK TO save_rec;
387 END IF;
388
389 FOR r_lot_number IN c_lot_number( p_user_name, p_tranid, r_material_transaction.transaction_id ) LOOP
390 /* Delete matching contact record(s) */
391 CSM_UTIL_PKG.DELETE_RECORD
392 (
393 p_user_name,
394 p_tranid,
395 r_lot_number.seqno$$,
396 r_lot_number.TRANSACTION_ID,
397 g_object_name,
398 g_lot_pub_name,
399 l_error_msg,
400 l_process_status
401 );
402 /*** was delete successful? ***/
403 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
404 CSM_UTIL_PKG.LOG
405 ( module => g_object_name
406 , message => r_lot_number.tranid$$ || ' Deleting from inqueue failed, Defer and reject record'
407 , log_level => FND_LOG.LEVEL_PROCEDURE);
408
409 CSM_UTIL_PKG.DEFER_RECORD
410 (
411 p_user_name
412 , p_tranid
413 , r_lot_number.seqno$$
414 , r_lot_number.TRANSACTION_ID
415 , g_object_name
416 , g_lot_pub_name
417 , l_error_msg
418 , l_process_status
419 , r_lot_number.dmltype$$
420 );
421 END IF;
422 END LOOP;
423
424 FOR r_unit_transaction IN c_unit_transaction( p_user_name,
425 p_tranid,
426 r_material_transaction.transaction_id ) LOOP
427 /* Delete matching contact record(s) */
428 CSM_UTIL_PKG.DELETE_RECORD
429 (
430 p_user_name,
431 p_tranid,
432 r_unit_transaction.seqno$$,
433 r_unit_transaction.TRANSACTION_ID,
434 g_object_name,
435 g_unit_pub_name,
436 l_error_msg,
437 l_process_status
438 );
439 /*** was delete successful? ***/
440 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
441 CSM_UTIL_PKG.LOG
442 ( module => g_object_name || 'MTL_UNIT_TRANSACTIONS'
443 , message => r_unit_transaction.TRANSACTION_ID || ' Deleting from inqueue failed, Defer and reject record'
444 , log_level => FND_LOG.LEVEL_PROCEDURE);
445
446 CSM_UTIL_PKG.DEFER_RECORD
447 (
448 p_user_name
449 , p_tranid
450 , r_unit_transaction.seqno$$
451 , r_unit_transaction.TRANSACTION_ID
452 , g_object_name
453 , g_unit_pub_name
454 , l_error_msg
455 , l_process_status
456 , r_unit_transaction.dmltype$$
457 );
458 END IF;
459 END LOOP;
460
461 END IF;
462
463 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
464 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
465 CSM_UTIL_PKG.LOG
466 ( module => g_object_name
467 , message => r_material_transaction.TRANSACTION_ID || ' Record not processed successfully, deferring and rejecting record'
468 , log_level => FND_LOG.LEVEL_PROCEDURE);
469
470 CSM_UTIL_PKG.DEFER_RECORD
471 (
472 p_user_name
473 , p_tranid
474 , r_material_transaction.seqno$$
475 , r_material_transaction.TRANSACTION_ID
476 , g_object_name
477 , g_mat_pub_name
478 , l_error_msg
479 , l_process_status
480 , r_material_transaction.dmltype$$
481 );
482
483 /*** Was defer successful? ***/
484 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
485 /*** no -> rollback ***/
486 CSM_UTIL_PKG.LOG
487 ( module => g_object_name
488 , message => r_material_transaction.TRANSACTION_ID || ' Defer record failed, rolling back to savepoint'
489 , log_level => FND_LOG.LEVEL_PROCEDURE);
490
491 ROLLBACK TO save_rec;
492 END IF;
493
494 FOR r_lot_number IN c_lot_number( p_user_name, p_tranid, r_material_transaction.transaction_id ) LOOP
495 /* Defering matching contact record(s) */
496 CSM_UTIL_PKG.DEFER_RECORD
497 (
498 p_user_name
499 , p_tranid
500 , r_lot_number.seqno$$
501 , r_lot_number.TRANSACTION_ID
502 , g_object_name
503 , g_lot_pub_name
504 , l_error_msg
505 , l_process_status
506 , r_lot_number.dmltype$$
507 );
508 END LOOP;
509
510 FOR r_unit_transaction IN c_unit_transaction( p_user_name,
511 p_tranid,
512 r_material_transaction.transaction_id ) LOOP
513 /* Defering matching contact record(s) */
514 CSM_UTIL_PKG.DEFER_RECORD
515 (
516 p_user_name
517 , p_tranid
518 , r_unit_transaction.seqno$$
519 , r_unit_transaction.TRANSACTION_ID
520 , g_object_name
521 , g_unit_pub_name
522 , l_error_msg
523 , l_process_status
524 , r_unit_transaction.dmltype$$
525 );
526 END LOOP;
527 END IF;
528 END LOOP;
529
530 CSM_UTIL_PKG.LOG
531 ( module => g_object_name
532 , message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
533 , log_level => FND_LOG.LEVEL_STATEMENT);
534
535 EXCEPTION WHEN OTHERS THEN
536 /*** catch and log exceptions ***/
537 CSM_UTIL_PKG.LOG
538 ( module => g_object_name
539 , message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
540 , log_level => FND_LOG.LEVEL_ERROR);
541
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 END APPLY_CLIENT_CHANGES;
544
545 END CSM_MATERIAL_TRANSACTION_PKG;