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