[Home] [Help]
PACKAGE BODY: APPS.CSI_INV_TXNSTUB_PKG
Source
1 package body CSI_INV_TXNSTUB_PKG AS
2 -- $Header: csiinvhb.pls 120.7.12010000.5 2009/01/23 19:43:11 fli ship $
3
4 l_debug NUMBER := csi_t_gen_utility_pvt.g_debug_level;
5
6 PROCEDURE debug(
7 p_message IN varchar2)
8 IS
9 BEGIN
10 IF l_debug > 0 THEN
11 csi_t_gen_utility_pvt.add(p_message);
12 END IF;
13 END debug;
14
15 PROCEDURE execute_trx_dpl(p_transaction_type IN VARCHAR2,
16 p_transaction_id IN NUMBER,
17 x_trx_return_status OUT NOCOPY VARCHAR2,
18 x_trx_error_rec IN OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
19
20 IS
21 l_api_name VARCHAR2(100) := 'CSI_INV_TXNSTUB_PUB.EXECUTE_TRX_DPL';
22 l_api_version NUMBER := 1.0;
23 l_commit VARCHAR2(1) := FND_API.G_FALSE;
24 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
25 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
26 l_error_message VARCHAR2(4000);
27 l_error_code VARCHAR2(4000);
28 l_return_status VARCHAR2(1);
29 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
30 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
31 l_mtl_trx_rec CSI_INV_TRXS_PKG.MTL_TRX_TYPE;
32 l_trx_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
33 l_msg_count NUMBER;
34 l_msg_data VARCHAR2(4000);
35 l_txn_error_id NUMBER;
36 l_file VARCHAR2(500);
37 l_xml_string VARCHAR2(2000);
38 l_txn_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
39 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
40 e_dpl_error EXCEPTION;
41 csi_txn_exists EXCEPTION;
42 l_txn_type_id NUMBER := NULL;
43 l_use_parallel_mode VARCHAR2(1) := 'N'; --Code added for MACD enhancement
44 l_order_line_rec csi_order_ship_pub.order_line_rec; --Code added for MACD enhancement
45 l_item_control_rec csi_order_ship_pub.item_control_rec; --Code added for MACD enhancement
46 l_exists NUMBER; --Code added for MACD enhancement
47 l_link_to_line_id NUMBER; --Code added for MACD enhancement
48
49 CURSOR c_csi_inv_exist_txns (pc_transaction_id IN NUMBER) is
50 SELECT transaction_id
51 FROM csi_transactions
52 WHERE inv_material_transaction_id = pc_transaction_id;
53
54 r_csi_inv_exist_txns c_csi_inv_exist_txns%rowtype;
55
56 CURSOR c_csi_inv_exist_txns_and_items (pc_transaction_id IN NUMBER) IS
57 SELECT instance_id FROM csi_item_instances WHERE instance_id IN
58 (SELECT instance_id FROM csi_item_instances_h WHERE transaction_id IN
59 (SELECT transaction_id FROM csi_transactions WHERE inv_material_transaction_id = pc_transaction_id))
60 AND inventory_item_id IN
61 (SELECT inventory_item_id FROM mtl_material_transactions WHERE transaction_id = pc_transaction_id)
62 AND ROWNUM = 1;
63
64 r_csi_inv_exist_txns_and_items c_csi_inv_exist_txns_and_items%rowtype;
65
66 --modified for bug 7623208
67 CURSOR c_csi_soful_exist_txns (pc_transaction_id IN NUMBER) IS
68 SELECT transaction_id
69 FROM csi_transactions
70 WHERE source_line_ref_id = pc_transaction_id
71 AND transaction_type_id in (51,54,401)
72 AND source_header_ref IN
73 (SELECT order_number FROM oe_order_headers_all WHERE header_id IN
74 (SELECT header_id FROM oe_order_lines_all WHERE line_id = pc_transaction_id));
75
76 r_csi_soful_exist_txns c_csi_soful_exist_txns%rowtype;
77
78 BEGIN
79
80 l_error_message := NULL;
81 l_return_status := l_fnd_success;
82 l_trx_error_rec := x_trx_error_rec;
83
84 -- Set error rec for the MTL Transaction ID in case of unexp error
85 l_trx_error_rec.source_id := p_transaction_id;
86 l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
87 l_trx_error_rec.source_type := p_transaction_type;
88
89 IF (l_debug > 0) THEN
90 csi_t_gen_utility_pvt.build_file_name(p_file_segment1 => lower(p_transaction_type),
91 p_file_segment2 => p_transaction_id);
92 END IF;
93
94 -- Added for Cursor Optimization on CSI_INSTALL_PARAMETERS
95 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
96 csi_gen_utility_pvt.populate_install_param_rec;
97 END IF;
98
99 savepoint one;
100
101 debug('-------');
102 debug('In execute_trx_dpl for Transaction:'||p_transaction_type);
103 debug('In execute_trx_dpl for MTL Transaction ID:'||p_transaction_id);
104 debug('-------');
105
106 debug('Install Parameter Datastructure Populated');
107 debug(' Ownership Override: '||csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn);
108 debug(' SFM Flag: '||csi_datastructures_pub.g_install_param_rec.sfm_queue_bypass_flag);
109
110 -- Check to see if any CSI Transactions Exist. If it does then just exit if there is not
111 -- then process as normal
112
113 IF p_transaction_type in ('CSISOFUL','CSIRMAFL') THEN
114
115 OPEN c_csi_soful_exist_txns (p_transaction_id);
116 FETCH c_csi_soful_exist_txns into r_csi_soful_exist_txns;
117
118 IF c_csi_soful_exist_txns%FOUND THEN
119 csi_t_gen_utility_pvt.add('CSI Transaction Exists: '||r_csi_soful_exist_txns.transaction_id);
120 csi_t_gen_utility_pvt.add('Call exception to exit execute_trx_dpl');
121 RAISE csi_txn_exists;
122 ELSE
123 csi_t_gen_utility_pvt.add('No CSI Transaction Exists so Continue Processing');
124 END IF;
125 CLOSE c_csi_soful_exist_txns;
126
127 ELSE
128 OPEN c_csi_inv_exist_txns (p_transaction_id);
129 FETCH c_csi_inv_exist_txns into r_csi_inv_exist_txns;
130
131 IF c_csi_inv_exist_txns%FOUND THEN
132 OPEN c_csi_inv_exist_txns_and_items (p_transaction_id);
133 FETCH c_csi_inv_exist_txns_and_items into r_csi_inv_exist_txns_and_items;
134
135 IF c_csi_inv_exist_txns_and_items%FOUND THEN
136 csi_t_gen_utility_pvt.add('CSI Transaction Exists: '||r_csi_inv_exist_txns.transaction_id);
137 csi_t_gen_utility_pvt.add('Call exception to exit execute_trx_dpl');
138 RAISE csi_txn_exists;
139 ELSE
140 csi_t_gen_utility_pvt.add('No CSI Transaction for the same Inventory Item Exists so Continue Processing');
141 END IF;
142 CLOSE c_csi_inv_exist_txns_and_items;
143 ELSE
144 csi_t_gen_utility_pvt.add('No CSI Transaction Exists so Continue Processing');
145 END IF;
146 CLOSE c_csi_inv_exist_txns;
147
148 END IF;
149
150 IF p_transaction_type = 'CSIISUPT' THEN
151 debug('Before Transaction If for: '||p_transaction_type);
152 l_trx_error_rec.transaction_type_id := 113;
153 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MOVE_ORDER_ISSUE_TO_PROJECT','INV');
154 csi_inv_project_pkg.issue_to_project (p_transaction_id,
155 NULL,
156 l_return_status,
157 l_trx_error_rec);
158
159 debug('After Transaction If for: '||p_transaction_type);
160
161 IF NOT l_return_status = l_fnd_success THEN
162 RAISE e_dpl_error;
163 END IF;
164
165 ELSIF p_transaction_type = 'CSIMSIPT' THEN
166 debug('Before Transaction If for: '||p_transaction_type);
167 l_trx_error_rec.transaction_type_id := 121;
168 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_ISSUE_TO_PROJECT','INV');
169 csi_inv_project_pkg.misc_issue_projtask (p_transaction_id,
170 NULL,
171 l_return_status,
172 l_trx_error_rec);
173 debug('After Transaction If for: '||p_transaction_type);
174
175 IF NOT l_return_status = l_fnd_success THEN
176 RAISE e_dpl_error;
177 END IF;
178
179 ELSIF p_transaction_type = 'CSISUBTR' THEN
180 debug('Before Transaction If for: '||p_transaction_type);
181 l_trx_error_rec.transaction_type_id := 114;
182 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('SUBINVENTORY_TRANSFER','INV');
183 csi_inv_transfer_pkg.subinv_transfer (p_transaction_id,
184 NULL,
185 l_return_status,
186 l_trx_error_rec);
187 debug('After Transaction If for: '||p_transaction_type);
188
189 IF NOT l_return_status = l_fnd_success THEN
190 RAISE e_dpl_error;
191 END IF;
192
193 ELSIF p_transaction_type = 'CSIORGTR' THEN
194 debug('Before Transaction If for: '||p_transaction_type);
195 l_trx_error_rec.transaction_type_id := 144;
196 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('INTERORG_TRANS_RECEIPT','INV');
197 csi_inv_interorg_pkg.intransit_receipt (p_transaction_id,
198 NULL,
199 l_return_status,
200 l_trx_error_rec);
201
202 debug('After Transaction If for: '||p_transaction_type);
203
204 IF NOT l_return_status = l_fnd_success THEN
205 RAISE e_dpl_error;
206 END IF;
207
208 ELSIF p_transaction_type = 'CSIORGTS' THEN
209 debug('Before Transaction If for: '||p_transaction_type);
210 l_trx_error_rec.transaction_type_id := 145;
211 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('INTERORG_TRANS_SHIPMENT','INV');
212 csi_inv_interorg_pkg.intransit_shipment (p_transaction_id,
213 NULL,
214 l_return_status,
215 l_trx_error_rec);
216
217 debug('After Transaction If for: '||p_transaction_type);
218
219 IF NOT l_return_status = l_fnd_success THEN
220 RAISE e_dpl_error;
221 END IF;
222
223 ELSIF p_transaction_type = 'CSIORGDS' THEN
224 debug('Before Transaction If for: '||p_transaction_type);
225 l_trx_error_rec.transaction_type_id := 143;
226 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('INTERORG_DIRECT_SHIP','INV');
227 csi_inv_interorg_pkg.direct_shipment (p_transaction_id,
228 NULL,
229 l_return_status,
230 l_trx_error_rec);
231
232 debug('After Transaction If for: '||p_transaction_type);
233
234 IF NOT l_return_status = l_fnd_success THEN
235 RAISE e_dpl_error;
236 END IF;
237
238 ELSIF p_transaction_type = 'CSIINTSS' THEN
239 debug('Before Transaction If for: '||p_transaction_type);
240 l_trx_error_rec.transaction_type_id := 130;
241 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISO_SHIPMENT','INV');
242 csi_inv_iso_pkg.iso_shipment(p_transaction_id,
243 NULL,
244 l_return_status,
245 l_trx_error_rec);
246
247 debug('After Transaction If for: '||p_transaction_type);
248
249 IF NOT l_return_status = l_fnd_success THEN
250 RAISE e_dpl_error;
251 END IF;
252
253 ELSIF p_transaction_type = 'CSIINTSR' THEN
254 debug('Before Transaction If for: '||p_transaction_type);
255 l_trx_error_rec.transaction_type_id := 131;
256 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISO_REQUISITION_RECEIPT','INV');
257 csi_inv_iso_pkg.iso_receipt(p_transaction_id,
258 NULL,
259 l_return_status,
260 l_trx_error_rec);
261
262 debug('After Transaction If for: '||p_transaction_type);
263
264 IF NOT l_return_status = l_fnd_success THEN
265 RAISE e_dpl_error;
266 END IF;
267
268 ELSIF p_transaction_type = 'CSIINTDS' THEN
269 debug('Before Transaction If for: '||p_transaction_type);
270 l_trx_error_rec.transaction_type_id := 142;
271 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISO_DIRECT_SHIP','INV');
272 csi_inv_iso_pkg.iso_direct(p_transaction_id,
273 NULL,
274 l_return_status,
275 l_trx_error_rec);
276
277 debug('After Transaction If for: '||p_transaction_type);
278
279 IF NOT l_return_status = l_fnd_success THEN
280 RAISE e_dpl_error;
281 END IF;
282
283 ELSIF p_transaction_type = 'CSIPOINV' THEN
284 debug('Before Transaction If for: '||p_transaction_type);
285 l_trx_error_rec.transaction_type_id := 112;
286 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
287 csi_inv_trxs_pkg.receipt_inventory (p_transaction_id,
288 NULL,
289 l_return_status,
290 l_trx_error_rec);
291
292 debug('After Transaction If for: '||p_transaction_type);
293
294 IF NOT l_return_status = l_fnd_success THEN
295 RAISE e_dpl_error;
296 END IF;
297
298 ELSIF p_transaction_type = 'CSICYCNT' THEN
299 debug('Before Transaction If for: '||p_transaction_type);
300 l_trx_error_rec.transaction_type_id := 119;
301 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('CYCLE_COUNT','INV');
302 csi_inv_trxs_pkg.cycle_count (p_transaction_id,
303 NULL,
304 l_return_status,
305 l_trx_error_rec);
306
307 debug('After Transaction If for: '||p_transaction_type);
308
309 IF NOT l_return_status = l_fnd_success THEN
310 RAISE e_dpl_error;
311 END IF;
312
313 ELSIF p_transaction_type = 'CSIPHYIN' THEN
314 debug('Before Transaction If for: '||p_transaction_type);
315 l_trx_error_rec.transaction_type_id := 118;
316 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PHYSICAL_INVENTORY','INV');
317 csi_inv_trxs_pkg.physical_inventory (p_transaction_id,
318 NULL,
319 l_return_status,
320 l_trx_error_rec);
321
322 debug('After Transaction If for: '||p_transaction_type);
323
324 IF NOT l_return_status = l_fnd_success THEN
325 RAISE e_dpl_error;
326 END IF;
327
328 ELSIF p_transaction_type = 'CSIMSRCV' THEN
329 debug('Before Transaction If for: '||p_transaction_type);
330 l_trx_error_rec.transaction_type_id := 117;
331 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_RECEIPT','INV');
332 csi_inv_trxs_pkg.misc_receipt (p_transaction_id,
333 NULL,
334 l_return_status,
335 l_trx_error_rec);
336
337 debug('After Transaction If for: '||p_transaction_type);
338
339 IF NOT l_return_status = l_fnd_success THEN
340 RAISE e_dpl_error;
341 END IF;
342
343 ELSIF p_transaction_type = 'CSIMSISU' THEN
344 debug('Before Transaction If for: '||p_transaction_type);
345 l_trx_error_rec.transaction_type_id := 116;
346 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_ISSUE','INV');
347 csi_inv_trxs_pkg.misc_issue (p_transaction_id,
348 NULL,
349 l_return_status,
350 l_trx_error_rec);
351
352 debug('After Transaction If for: '||p_transaction_type);
353
354 IF NOT l_return_status = l_fnd_success THEN
355 RAISE e_dpl_error;
356 END IF;
357
358 ELSIF p_transaction_type = 'CSIMSRPT' THEN
359 debug('Before Transaction If for: '||p_transaction_type);
360 l_trx_error_rec.transaction_type_id := 120;
361 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_RECEIPT_FROM_PROJECT','INV');
362 csi_inv_project_pkg.misc_receipt_projtask (p_transaction_id,
363 NULL,
364 l_return_status,
365 l_trx_error_rec);
366
367 debug('After Transaction If for: '||p_transaction_type);
368
369 IF NOT l_return_status = l_fnd_success THEN
370 RAISE e_dpl_error;
371 END IF;
372
373 ELSIF p_transaction_type = 'CSIISUHZ' THEN
374 debug('Before Transaction If for: '||p_transaction_type);
375 l_trx_error_rec.transaction_type_id := 132;
376 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISSUE_TO_HZ_LOC','INV');
377 csi_inv_hz_pkg.issue_to_hz_loc (p_transaction_id,
378 NULL,
379 l_return_status,
380 l_trx_error_rec);
381
382 debug('After Transaction If for: '||p_transaction_type);
383
384 IF NOT l_return_status = l_fnd_success THEN
385 RAISE e_dpl_error;
386 END IF;
387
388 ELSIF p_transaction_type = 'CSIMSIHZ' THEN
389 debug('Before Transaction If for: '||p_transaction_type);
390 l_trx_error_rec.transaction_type_id := 133;
391 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_ISSUE_HZ_LOC','INV');
392 csi_inv_hz_pkg.misc_issue_hz_loc (p_transaction_id,
393 NULL,
394 l_return_status,
395 l_trx_error_rec);
396
397 debug('After Transaction If for: '||p_transaction_type);
398
399 IF NOT l_return_status = l_fnd_success THEN
400 RAISE e_dpl_error;
401 END IF;
402
403 ELSIF p_transaction_type = 'CSIMSRHZ' THEN
404 debug('Before Transaction If for: '||p_transaction_type);
405 l_trx_error_rec.transaction_type_id := 134;
406 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_RECEIPT_HZ_LOC','INV');
407 csi_inv_hz_pkg.misc_receipt_hz_loc (p_transaction_id,
408 NULL,
409 l_return_status,
410 l_trx_error_rec);
411
412 debug('After Transaction If for: '||p_transaction_type);
413
414 IF NOT l_return_status = l_fnd_success THEN
415 RAISE e_dpl_error;
416 END IF;
417
418 ELSIF p_transaction_type = 'CSISOFUL' THEN
419 debug('Before Transaction If for: '||p_transaction_type);
420 l_trx_error_rec.transaction_type_id := 51;
421 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('OM_SHIPMENT','ONT');
422 l_trx_error_rec.inv_material_transaction_id := null;
423
424 csi_order_fulfill_pub.order_fulfillment(
425 p_order_line_id => p_transaction_id,
426 p_message_id => NULL,
427 x_return_status => l_return_status,
428 px_trx_error_rec => l_trx_error_rec);
429
430 debug('After Transaction If for: '||p_transaction_type);
431
432 savepoint one;
433
434 IF NOT l_return_status = l_fnd_success THEN
435 RAISE e_dpl_error;
436 END IF;
437
438 ELSIF p_transaction_type = 'CSIRMAFL' THEN
439 debug('Before Transaction If for: '||p_transaction_type);
440 l_trx_error_rec.transaction_type_id := 54;
441 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('RMA_FULFILL','CSI');
442 l_trx_error_rec.inv_material_transaction_id := null;
443 csi_rma_fulfill_pub.rma_fulfillment(
444 p_rma_line_id => p_transaction_id,
445 p_message_id => NULL,
446 x_return_status => l_return_status,
447 px_trx_error_rec => l_trx_error_rec);
448
449 debug('After Transaction If for: '||p_transaction_type);
450
451 IF NOT l_return_status = l_fnd_success THEN
452 RAISE e_dpl_error;
453 END IF;
454
455 ELSIF p_transaction_type = 'CSISOSHP' THEN
456 debug('Before Transaction If for: '||p_transaction_type);
457 l_trx_error_rec.transaction_type_id := 51;
458 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('OM_SHIPMENT','ONT');
459
460 l_use_parallel_mode := NVL(fnd_profile.value('CSI_TXN_PARALLEL_MODE'), 'N'); --Code Added for MACD Enhancement
461
462 if (l_use_parallel_mode = 'N') then --Code Added for MACD Enhancement
463
464 csi_order_ship_pub.order_shipment(
465 p_mtl_transaction_id => p_transaction_id,
466 p_message_id => NULL,
467 x_return_status => l_return_status,
468 px_trx_error_rec => l_trx_error_rec);
469
470 else
471
472 --Code Added for MACD Enhancement starts here
473 -- Get the order line details
474 csi_utl_pkg.get_order_line_dtls(
475 p_mtl_transaction_id => p_transaction_id,
476 x_order_line_rec => l_order_line_rec,
477 x_return_status => l_return_status);
478
479 -- get item details
480 csi_utl_pkg.get_item_control_rec(
481 p_mtl_txn_id => p_transaction_id,
482 x_item_control_rec => l_item_control_rec,
483 x_return_status => l_return_status);
484
485 -- check if the order is linked to any other line
486 select link_to_line_id
487 into l_link_to_line_id
488 from oe_order_lines_all
489 where line_id = l_order_line_rec.order_line_id;
490
491 if (l_link_to_line_id is not null) then
492 begin
493 -- check rows being processed currently within order
494 select count(1)
495 into l_exists
496 from csi_batch_txn_lines
497 where order_header_id = l_order_line_rec.header_id
498 and (processed_flag = 1 or processed_flag = 2);
499 exception
500 when no_data_found then
501 l_exists := 0;
502 end;
503
504 if (l_exists = 1) then
505 insert into CSI_BATCH_TXN_LINES
506 (
507 BATCH_ID,
508 PROCESSED_FLAG,
509 ORDER_HEADER_ID,
510 ORDER_LINE_ID,
511 --ORGANIZATION_ID,
512 --INVENTORY_ITEM_ID,
513 TRANSACTION_TYPE,
514 TRANSACTION_TYPE_ID,
515 TRANSACTION_ID
516 --INSTANCE_ID,
517 --SERIAL_NUMBER
518 ,CREATION_DATE
519 ,CREATED_BY
520 ,LAST_UPDATE_DATE
521 ,LAST_UPDATED_BY
522 ) VALUES
523 (
524 -1,
525 0,
526 l_order_line_rec.header_id,
527 l_order_line_rec.order_line_id,
528 --NULL,
529 --l_item_control_rec.inventory_item_id,
530 p_transaction_type,
531 51,
532 p_transaction_id
533 --NULL,
534 --NULL
535 ,sysdate
536 ,fnd_global.user_id
537 ,sysdate
538 ,fnd_global.user_id
539 );
540
541 else
542 csi_order_ship_pub.order_shipment(
543 p_mtl_transaction_id => p_transaction_id,
544 p_message_id => NULL,
545 x_return_status => l_return_status,
546 px_trx_error_rec => l_trx_error_rec);
547
548 debug('After Transaction If for: '||p_transaction_type);
549
550 IF NOT l_return_status = l_fnd_success THEN
551 RAISE e_dpl_error;
552 END IF;
553
554 end if;
555 else
556 csi_order_ship_pub.order_shipment(
557 p_mtl_transaction_id => p_transaction_id,
558 p_message_id => NULL,
559 x_return_status => l_return_status,
560 px_trx_error_rec => l_trx_error_rec);
561
562 IF NOT l_return_status = l_fnd_success THEN
563 RAISE e_dpl_error;
564 END IF;
565
566 end if;
567
568
569 --Code Added for MACD Enhancement ends here
570 end if;
571
572
573 debug('After Transaction If for: '||p_transaction_type);
574
575 IF NOT l_return_status = l_fnd_success THEN
576 RAISE e_dpl_error;
577 END IF;
578
579 ELSIF p_transaction_type = 'CSIINTIS' THEN
580 debug('Before Transaction If for: '||p_transaction_type);
581 l_trx_error_rec.transaction_type_id := 126;
582 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISO_ISSUE','ONT');
583 csi_order_ship_pub.order_shipment(
584 p_mtl_transaction_id => p_transaction_id,
585 p_message_id => NULL,
586 x_return_status => l_return_status,
587 px_trx_error_rec => l_trx_error_rec);
588
589 debug('After Transaction If for: '||p_transaction_type);
590
591 IF NOT l_return_status = l_fnd_success THEN
592 RAISE e_dpl_error;
593 END IF;
594
595 ELSIF p_transaction_type = 'CSILOSHP' THEN
596 debug('Before Transaction If for: '||p_transaction_type);
597 l_trx_error_rec.transaction_type_id := 51;
598 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('OM_SHIPMENT','ONT');
599 csi_order_fulfill_pub.logical_drop_ship(
600 p_mtl_txn_id => p_transaction_id,
601 p_message_id => NULL,
602 x_return_status => l_return_status,
603 px_trx_error_rec => l_trx_error_rec);
604
605 debug('After Transaction If for: '||p_transaction_type);
606
607 IF NOT l_return_status = l_fnd_success THEN
608 RAISE e_dpl_error;
609 END IF;
610
611 ELSIF p_transaction_type = 'CSIOKSHP' THEN
612 debug('Before Transaction If for: '||p_transaction_type);
613 l_trx_error_rec.transaction_type_id := 326;
614 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PROJECT_CONTRACT_SHIPMENT','OKE');
615 csi_order_ship_pub.oke_shipment(
616 p_mtl_txn_id => p_transaction_id,
617 x_return_status => l_return_status,
618 px_trx_error_rec => l_trx_error_rec);
619
620 debug('After Transaction If for: '||p_transaction_type);
621
622 IF NOT l_return_status = l_fnd_success THEN
623 RAISE e_dpl_error;
624 END IF;
625
626 ELSIF p_transaction_type = 'CSIRMARC' THEN
627 debug('Before Transaction If for: '||p_transaction_type);
628 l_trx_error_rec.transaction_type_id := 53;
629 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('RMA_RECEIPT','ONT');
630 csi_rma_receipt_pub.rma_receipt(
631 p_mtl_txn_id => p_transaction_id,
632 p_message_id => null,
633 x_return_status => l_return_status,
634 px_trx_error_rec => l_trx_error_rec);
635
636 debug('After Transaction If for: '||p_transaction_type);
637
638 IF NOT l_return_status = l_fnd_success THEN
639 RAISE e_dpl_error;
640 END IF;
641
642 ELSIF p_transaction_type = 'CSIWIPCI' THEN
643
644 l_trx_error_rec.transaction_type_id := 71;
645 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('WIP_ISSUE','INV');
646
647 csi_wip_trxs_pkg.wip_comp_issue(
648 p_transaction_id => p_transaction_id,
649 p_message_id => NULL,
650 x_return_status => l_return_status,
651 px_trx_error_rec => l_trx_error_rec);
652
653 debug('After Transaction If for: '||p_transaction_type);
654
655 IF NOT l_return_status = l_fnd_success THEN
656 RAISE e_dpl_error;
657 END IF;
658
659 ELSIF p_transaction_type = 'CSIWIPNR' THEN
660
661 l_trx_error_rec.transaction_type_id := 71;
662 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('WIP_ISSUE','INV');
663
664 csi_wip_trxs_pkg.wip_neg_comp_return(
665 p_transaction_id => p_transaction_id,
666 p_message_id => NULL,
667 x_return_status => l_return_status,
668 px_trx_error_rec => l_trx_error_rec);
669
670 debug('After Transaction If for: '||p_transaction_type);
671
672 IF NOT l_return_status = l_fnd_success THEN
673 RAISE e_dpl_error;
674 END IF;
675
676 ELSIF p_transaction_type = 'CSIWIPAR' THEN
677
678 l_trx_error_rec.transaction_type_id := 74;
679 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('WIP_ASSEMBLY_RETURN','INV');
680
681 csi_wip_trxs_pkg.wip_assy_return(
682 p_transaction_id => p_transaction_id,
683 p_message_id => NULL,
684 x_return_status => l_return_status,
685 px_trx_error_rec => l_trx_error_rec);
686
687 debug('After Transaction If for: '||p_transaction_type);
688
689 IF NOT l_return_status = l_fnd_success THEN
690 RAISE e_dpl_error;
691 END IF;
692
693 --R12 Changes for OPM
694 ELSIF p_transaction_type = 'CSIWIPBR' THEN
695
696 l_trx_error_rec.transaction_type_id := 76;
697 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('WIP_BYPRODUCT_RETURN','INV');
698
699 csi_wip_trxs_pkg.wip_byproduct_return(
700 p_transaction_id => p_transaction_id,
701 p_message_id => NULL,
702 x_return_status => l_return_status,
703 px_trx_error_rec => l_trx_error_rec);
704
705 debug('After Transaction If for: '||p_transaction_type);
706
707 IF NOT l_return_status = l_fnd_success THEN
708 RAISE e_dpl_error;
709 END IF;
710
711
712 ELSIF ( p_transaction_type = 'CSIWIPCR' OR
713 p_transaction_type = 'CSIWIPNI' ) THEN
714 l_trx_error_rec.transaction_type_id := 72;
715 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('WIP_RECEIPT','INV');
716 csi_wip_trxs_pkg.wip_comp_receipt (
717 p_transaction_id => p_transaction_id,
718 p_message_id => NULL,
719 x_return_status => l_return_status,
720 px_trx_error_rec => l_trx_error_rec);
721
722 debug('After Transaction If for: '||p_transaction_type);
723
724 IF NOT l_return_status = l_fnd_success THEN
725 RAISE e_dpl_error;
726 END IF;
727
728 ELSIF p_transaction_type = 'CSIWIPAC' THEN
729 debug('Before Transaction If for: '||p_transaction_type);
730 l_trx_error_rec.transaction_type_id := 73;
731 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('WIP_ASSEMBLY_COMPLETION','INV');
732 csi_wip_trxs_pkg.wip_assy_completion(
733 p_transaction_id => p_transaction_id,
734 p_message_id => null,
735 x_return_status => l_return_status,
736 px_trx_error_rec => l_trx_error_rec);
737
738 debug('After Transaction If for: '||p_transaction_type);
739
740 IF NOT l_return_status = l_fnd_success THEN
741 RAISE e_dpl_error;
742 END IF;
743
744 --bnarayan added for OPM Changes
745 ELSIF p_transaction_type = 'CSIWIPBC' THEN --WIP By Product Completion
746 debug('Before Transaction If for: '||p_transaction_type);
747 l_trx_error_rec.transaction_type_id := 75;
748 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('WIP_BYPRODUCT_COMPLETION ','INV');
749 csi_wip_trxs_pkg.wip_byproduct_completion(
750 p_transaction_id => p_transaction_id,
751 p_message_id => null,
752 x_return_status => l_return_status,
753 px_trx_error_rec => l_trx_error_rec);
754
755 debug('After Transaction If for: '||p_transaction_type);
756
757 IF NOT l_return_status = l_fnd_success THEN
758 RAISE e_dpl_error;
759 END IF;
760
761 ELSE -- Not a valid type so retain existing CSI Error
762 debug('No Transaction Type was found so retain error that exists');
763 l_trx_error_rec.error_text := FND_API.G_MISS_CHAR;
764 l_return_status := l_fnd_error;
765 RAISE e_dpl_error;
766 END IF;
767
768 x_trx_error_rec := l_trx_error_rec;
769 x_trx_return_status := l_return_status;
770
771 EXCEPTION
772 WHEN csi_txn_exists THEN
773 IF (l_debug > 0) THEN
774 csi_t_gen_utility_pvt.add('In CSI_TXN_EXISTS in CSIINVHB - CSI Transaction Exists so Just
775 exit doing nothing');
776 END IF;
777 x_trx_return_status := l_fnd_success;
778 x_trx_error_rec.error_text := NULL;
779
780 WHEN e_dpl_error THEN
781 rollback to one;
782 debug('In e_dpl_error in CSIINVHB - Check CSI_TXN_ERRORS for Details');
783
784 x_trx_error_rec := l_trx_error_rec;
785 x_trx_return_status := l_return_status;
786
787 WHEN OTHERS THEN
788 rollback to one;
789 debug('In OTHERS in CSIINVHB - Check CSI_TXN_ERRORS for Details');
790
791 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
792 fnd_message.set_token('API_NAME',l_api_name);
793 fnd_message.set_token('SQL_ERROR',SQLERRM);
794
795 x_trx_error_rec := l_trx_error_rec;
796 x_trx_error_rec.error_text := fnd_message.get;
797 x_trx_error_rec.transaction_id := NULL;
798 x_trx_error_rec.transaction_type_id := l_trx_error_rec.transaction_type_id;
799 x_trx_error_rec.source_type := p_transaction_type;
800 x_trx_error_rec.source_id := p_transaction_id;
801 IF p_transaction_type IN ('CSISOFUL', 'CSIRMAFL') THEN
802 x_trx_error_rec.inv_material_transaction_id := null;
803 ELSE
804 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
805 END IF;
806 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
807 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
808
809 x_trx_return_status := l_fnd_unexpected;
810
811 END execute_trx_dpl;
812
813 END CSI_INV_TXNSTUB_PKG;