[Home] [Help]
PACKAGE BODY: APPS.CSI_INV_TXN_HOOK_PKG
Source
1 package body CSI_INV_TXN_HOOK_PKG AS
2 /* $Header: csiinvtb.pls 120.9 2006/06/19 17:42:17 jpwilson noship $ */
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 csi_t_gen_utility_pvt.add(p_message);
11 EXCEPTION
12 WHEN others THEN
13 null;
14 END debug;
15
16 PROCEDURE postTransaction(
17 p_header_id IN NUMBER,
18 p_transaction_id IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2)
20 IS
21 l_api_version NUMBER := 1.0;
22 l_commit VARCHAR2(1) := FND_API.G_FALSE;
23 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
24 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
25 l_msg_count NUMBER;
26 l_msg_data VARCHAR2(2000);
27 l_txn_error_id NUMBER;
28 l_trx_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
29 l_logical_trx_attr_values INV_DROPSHIP_GLOBALS.logical_trx_attr_tbl;
30 l_csi_trackable VARCHAR2(10);
31 l_file VARCHAR2(500);
32 l_error_message VARCHAR2(2000) := NULL ;
33 l_trx_return_status VARCHAR2(1) := NULL ;
34 l_bypass VARCHAR2(1) := NULL ;
35 l_error_code NUMBER ;
36 l_message_id NUMBER;
37 l_return_status VARCHAR2(30);
38 l_ds_return_status VARCHAR2(30);
39 l_xml_string VARCHAR2(2000);
40 l_type_id NUMBER;
41 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
42 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
43 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
44 l_csi_txn_name csi_txn_types.source_transaction_type%type := NULL;
45 l_source_type VARCHAR2(50):= NULL;
46 hook_error EXCEPTION;
47 bypass_error EXCEPTION;
48 no_mmt_rec_exists EXCEPTION;
49 l_log_trx_action_id NUMBER := NULL;
50 l_log_trx_source_type_id NUMBER := NULL;
51 l_log_trx_type_code NUMBER := NULL;
52 l_log_trx_id NUMBER := NULL;
53 l_ds_log_trx_id NUMBER := NULL;
54 j PLS_INTEGER := 0;
55 l_log_rec_count NUMBER := 0;
56 l_master_org_id NUMBER;
57 l_parent_org NUMBER;
58
59 CURSOR c_mtl_data is
60 SELECT inventory_item_id,
61 transaction_quantity,
62 source_code,
63 transaction_action_id,
64 transaction_type_id,
65 transaction_source_type_id,
66 ship_to_location_id,
67 organization_id,
68 transaction_id,
69 parent_transaction_id
70 FROM mtl_material_transactions
71 WHERE transaction_id = p_transaction_id;
72
73 r_mtl_data c_mtl_data%rowtype;
74
75 CURSOR c_type_class (pc_transaction_type_id NUMBER) is
76 SELECT type_class,
77 transaction_source_type_id,
78 nvl(location_required_flag,'N') location_required_flag
79 FROM mtl_trx_types_view
80 WHERE transaction_type_id = pc_transaction_type_id;
81
82 CURSOR c_wip_entity_type is
83 SELECT entity_type
84 FROM wip_entities we,
85 mtl_material_transactions mmt
86 WHERE mmt.transaction_source_id=we.wip_entity_id
87 AND mmt.transaction_id= p_transaction_id;
88
89 r_type_class c_type_class%rowtype;
90 l_wip_entity_type wip_entities.entity_type%type;
91
92 CURSOR c_parent_org (pc_par_txn_id IN NUMBER) is
93 SELECT organization_id
94 FROM mtl_material_transactions
95 WHERE transaction_id = pc_par_txn_id;
96
97 CURSOR c_ic_shipment (pc_parent_org IN NUMBER,pc_transaction_id IN NUMBER) is
98 SELECT transaction_id
99 FROM mtl_material_transactions
100 WHERE transaction_id = pc_transaction_id
101 AND organization_id = pc_parent_org;
102
103 BEGIN
104
105 -- Get CSI Txn Name for Error
106 l_csi_txn_name := csi_inv_trxs_pkg.get_inv_name(p_transaction_id);
107
108 -- Initialize to Success so Oracle Inventory processing will not error
109 x_return_status := l_fnd_success;
110
111 csi_t_gen_utility_pvt.build_file_name(
112 p_file_segment1 => 'csiinv',
113 p_file_segment2 => 'hook');
114
115 debug('***** start of ib hook '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')||' *****');
116
117 -- Get all Inventory Data. Making this into a cursor so any future problems
118 -- with no data found can be eliminated.
119
120 OPEN c_mtl_data;
121 FETCH c_mtl_data into r_mtl_data;
122 CLOSE c_mtl_data;
123
124 IF r_mtl_data.transaction_id IS NULL THEN
125 debug('mtl_transaction_id : '||p_transaction_id || ' does not exist in mmt table');
126 END IF;
127
128 -- Get Master Org to Check If Item is Trackable
129 SELECT master_organization_id
130 INTO l_master_org_id
131 FROM mtl_parameters
132 WHERE organization_id = r_mtl_data.organization_id;
133
134 --Check if item is CSI trackable
135 IF csi_item_instance_vld_pvt.is_trackable(
136 p_inv_item_id => r_mtl_data.inventory_item_id,
137 p_stack_err_msg => FALSE,
138 p_org_id => l_master_org_id) THEN
139 l_csi_trackable := 'TRUE';
140 ELSE
141 l_csi_trackable := 'FALSE';
142 END IF;
143
144 debug(' mtl_transaction_id : '||p_transaction_id);
145 debug(' mtl_parent_transaction_id : '||r_mtl_data.parent_transaction_id);
146 debug(' mtl_txn_type_id : '||r_mtl_data.transaction_type_id);
147 debug(' mtl_txn_action_id : '||r_mtl_data.transaction_action_id);
148 debug(' mtl_txn_src_type_id : '||r_mtl_data.transaction_source_type_id);
149 debug(' inventory_item_id : '||r_mtl_data.inventory_item_id);
150 debug(' csi_transaction_name : '||l_csi_txn_name);
151 debug(' is_item_trackable : '||l_csi_trackable);
152
153 IF (l_csi_trackable = 'TRUE') THEN
154
155 -- Get Type Class Code
156 OPEN c_type_class(r_mtl_data.transaction_type_id);
157 FETCH c_type_class into r_type_class;
158 CLOSE c_type_class;
159
160 debug(' mtl_type_class : '||r_type_class.type_class);
161 debug(' location_required_flag : '||r_type_class.location_required_flag);
162
163 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
164 csi_gen_utility_pvt.populate_install_param_rec;
165 END IF;
166
167 l_bypass := nvl(csi_datastructures_pub.g_install_param_rec.sfm_queue_bypass_flag,'N');
168
169 debug(' sfm_bypass_flag : '||l_bypass);
170
171 IF r_mtl_data.parent_transaction_id IS NOT NULL then
172
173 debug(' Parent Transction ID is not null so call INV API : '||r_mtl_data.parent_transaction_id);
174
175 inv_ds_logical_trx_info_pub.get_logical_attr_values(
176 l_ds_return_status,
177 l_msg_count,
178 l_msg_data,
179 l_logical_trx_attr_values,
180 l_api_version,
181 l_init_msg_list,
182 p_transaction_id);
183
184 l_log_rec_count := l_logical_trx_attr_values.count;
185
186 debug(' ds return status : '||l_ds_return_status);
187 debug(' logical records : '||l_log_rec_count);
188
189 IF l_ds_return_status = l_fnd_success AND l_logical_trx_attr_values.count > 0 THEN
190
191 -- Get the Org ID for the Parent
192
193 OPEN c_parent_org(l_logical_trx_attr_values(1).parent_transaction_id);
194 FETCH c_parent_org into l_parent_org;
195 CLOSE c_parent_org;
196
197 debug(' Parent Transaction ID Organization : '||l_parent_org);
198
199 FOR j in l_logical_trx_attr_values.first .. l_logical_trx_attr_values.last LOOP
200
201 IF (l_logical_trx_attr_values(j).transaction_action_id = 7 AND
202 l_logical_trx_attr_values(j).transaction_source_type_id = 2 AND
203 l_logical_trx_attr_values(j).logical_trx_type_code = 2) THEN
204
205 debug('Action is 7, Source is 2 and Logical Txn Type is 2');
206
207 l_log_trx_action_id := l_logical_trx_attr_values(j).transaction_action_id;
208 l_log_trx_source_type_id := l_logical_trx_attr_values(j).transaction_source_type_id;
209 l_log_trx_type_code := l_logical_trx_attr_values(j).logical_trx_type_code;
210 l_ds_log_trx_id := l_logical_trx_attr_values(j).transaction_id;
211
212 debug('Found Record for Sales Order Issue Transaction now find the I/C Shipment: '||l_logical_trx_attr_values(j).transaction_id);
213
214 IF l_logical_trx_attr_values.count > 2 THEN
215
216 FOR j in l_logical_trx_attr_values.first .. l_logical_trx_attr_values.last LOOP
217
218 IF (l_logical_trx_attr_values(j).transaction_action_id = 9 AND
219 l_logical_trx_attr_values(j).transaction_source_type_id = 13 AND
220 l_logical_trx_attr_values(j).logical_trx_type_code = 2) THEN
221
222 debug('Found Record for I/C Shipment see if this should be used for Processing: '||l_logical_trx_attr_values(j).transaction_id);
223
224 -- For Each I/C Shipment find the one that has the same org as the parent transaction id
225
226 OPEN c_ic_shipment (l_parent_org,l_logical_trx_attr_values(j).transaction_id);
227 FETCH c_ic_shipment into l_log_trx_id;
228 CLOSE c_ic_shipment;
229
230 IF l_log_trx_id is not null THEN
231 debug('Exiting Loop to find I/C Shipment .. Using '||l_log_trx_id||' for processing');
232 EXIT;
233 END IF;
234
235 END IF;
236 END LOOP;
237
238 ELSIF l_logical_trx_attr_values.count = 2 THEN
239 FOR j in l_logical_trx_attr_values.first .. l_logical_trx_attr_values.last LOOP
240
241 IF (l_logical_trx_attr_values(j).transaction_action_id = 26 AND
242 l_logical_trx_attr_values(j).transaction_source_type_id = 1 AND
243 l_logical_trx_attr_values(j).logical_trx_type_code = 2) THEN
244
245 debug('Found Record for PO Receipt this is a Regular Drop Ship but in Logical Mode');
246 debug('Get the Logical Sales Order Issue and use for Processing: '||l_ds_log_trx_id);
247 l_log_trx_id := l_ds_log_trx_id;
248
249 END IF;
250 END LOOP;
251 END IF;
252
253 ELSIF (l_logical_trx_attr_values(j).transaction_action_id = 11 AND
254 l_logical_trx_attr_values(j).transaction_source_type_id = 1 AND
255 l_logical_trx_attr_values(j).logical_trx_type_code = 2) THEN
256
257 debug('Found Record for PO Adjustment: '||l_logical_trx_attr_values(j).transaction_id);
258
259 l_log_trx_action_id := l_logical_trx_attr_values(j).transaction_action_id;
260 l_log_trx_source_type_id := l_logical_trx_attr_values(j).transaction_source_type_id;
261 l_log_trx_type_code := l_logical_trx_attr_values(j).logical_trx_type_code;
262 l_log_trx_id := l_logical_trx_attr_values(j).transaction_id;
263
264 END IF;
265 END LOOP;
266
267 debug(' logical_txn_id : '||l_log_trx_id);
268 debug(' logical_action_id : '||l_log_trx_action_id);
269 debug(' logical_src_type_id : '||l_log_trx_source_type_id);
270 debug(' logical_txn_type_code: '||l_log_trx_type_code);
271
272 END IF;
273 ELSE
274 debug(' Parent Transction ID is null so do not call the INV API');
275 END IF; -- parent_transaction_id check
276
277 debug(' deciding which transaction to publish based on action and source :- ');
278 -- Begin of IF statement to decide what Transaction to Publish
279
280 IF (r_mtl_data.transaction_action_id = 1 AND
281 r_mtl_data.transaction_source_type_id = 4 AND
282 r_mtl_data.transaction_type_id NOT IN (33,122,35,37,93) AND
283 r_type_class.location_required_flag = 'Y' AND
284 (r_type_class.type_class is null OR r_type_class.type_class <> 1)) THEN
285
286 l_type_id := 132;
287 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'ISSUE_TO_HZ_LOC'),'INV');
288 l_source_type := 'CSIISUHZ';
289
290 IF (l_bypass = 'N') THEN
291
292 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
293
294 -- Issue to HZ Location
295 savepoint CSIISUHZ;
296
297 XNP_CSIISUHZ_U.publish(
298 xnp$mtl_transaction_id => p_transaction_id,
299 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
300 xnp$organization_id => r_mtl_data.organization_id,
301 x_message_id => l_message_id, -- out parameter
302 x_error_code => l_error_code, -- out parameter
303 x_error_message => l_error_message);
304
305 IF (l_error_message is not null) THEN
306 rollback to CSIISUHZ;
307 debug('Failed to publish event CSIISUHZ: ' || l_error_message);
308 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
309 fnd_message.set_token('EVENT','CSIISUHZ');
310 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
311 l_error_message := fnd_message.get;
312 raise HOOK_ERROR;
313 END IF;
314 ELSE
315 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
316
317 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIISUHZ',
318 p_transaction_id,
319 l_trx_return_status,
320 l_trx_error_rec);
321
322 IF NOT l_trx_return_status = l_fnd_success THEN
323 raise BYPASS_ERROR;
324 END IF;
325 END IF;
326
327 ELSIF (r_mtl_data.transaction_action_id = 27 AND
328 r_mtl_data.transaction_source_type_id in (13,6,3) AND
329 r_mtl_data.transaction_type_id NOT IN (15,123,43,94) AND
330 r_type_class.location_required_flag = 'Y' AND
331 (r_type_class.type_class is null OR r_type_class.type_class <> 1)) THEN
332
333
334 l_type_id := 134;
335 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'MISC_RECEIPT_HZ_LOC'),'INV');
336 l_source_type := 'CSIMSRHZ';
337 IF (l_bypass = 'N') THEN
338 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
339 -- Misc Receipt from HZ Location
340
341 savepoint CSIMSRHZ;
342
343 XNP_CSIMSRHZ_U.publish(
344 xnp$mtl_transaction_id => p_transaction_id,
345 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
346 xnp$organization_id => r_mtl_data.organization_id,
347 x_message_id => l_message_id, -- out parameter
348 x_error_code => l_error_code, -- out parameter
349 x_error_message => l_error_message -- out parameter
350 );
351
352 if (l_error_message is not null) then
353 rollback to CSIMSRHZ;
354 debug('Failed to publish event CSIMSRHZ: ' || l_error_message);
355 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
356 fnd_message.set_token('EVENT','CSIMSRHZ');
357 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
358 l_error_message := fnd_message.get;
359 raise HOOK_ERROR;
360 end if;
361 ELSE
362 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
363 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIMSRHZ',
364 p_transaction_id,
365 l_trx_return_status,
366 l_trx_error_rec);
367
368 IF NOT l_trx_return_status = l_fnd_success THEN
369 raise BYPASS_ERROR;
370 END IF;
371 END IF;
372
373 ELSIF (r_mtl_data.transaction_action_id = 1 AND
374 r_mtl_data.transaction_source_type_id in (13,6,3) AND
375 r_mtl_data.transaction_type_id NOT IN (33,122,35,37,93) AND
376 r_type_class.location_required_flag = 'Y' AND
377 (r_type_class.type_class is null OR r_type_class.type_class <> 1))THEN
378
379 l_type_id := 133;
380 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'MISC_ISSUE_HZ_LOC'),'INV');
381 l_source_type := 'CSIMSIHZ';
382 IF (l_bypass = 'N') THEN
383 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
384 -- Misc Receipt from HZ Location
385
386 savepoint CSIMSIHZ;
387
388 XNP_CSIMSIHZ_U.publish(
389 xnp$mtl_transaction_id => p_transaction_id,
390 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
391 xnp$organization_id => r_mtl_data.organization_id,
392 x_message_id => l_message_id, -- out parameter
393 x_error_code => l_error_code, -- out parameter
394 x_error_message => l_error_message -- out parameter
395 );
396
397 if (l_error_message is not null) then
398 rollback to CSIMSIHZ;
399 debug('Failed to publish event CSIMSIHZ: ' || l_error_message);
400 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
401 fnd_message.set_token('EVENT','CSIMSIHZ');
402 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
403 l_error_message := fnd_message.get;
404 raise HOOK_ERROR;
405 end if;
406 ELSE
407 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
408 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIMSIHZ',
409 p_transaction_id,
410 l_trx_return_status,
411 l_trx_error_rec);
412
413 IF NOT l_trx_return_status = l_fnd_success THEN
414 raise BYPASS_ERROR;
415 END IF;
416 END IF;
417
418 ELSIF (l_log_trx_action_id = 7 AND
419 l_log_trx_source_type_id = 2 AND
420 l_log_trx_id IS NOT NULL)
421 THEN
422 ---Transactions fall in this category are :
423 --- Type Action ID Txn Type ID
424 ----------------------- ------------- ------------
425 --1. Logical Sales Order Issue 7 30
426
427 l_type_id := 51;
428 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'OM_SHIPMENT'),'ONT');
429 l_source_type := 'CSILOSHP';
430 IF (l_bypass = 'N') THEN
431 debug(' publishing '||l_source_type||' for transaction_id : '||l_log_trx_id);
432 debug('Transaction ID used to get data 2,7: '||p_transaction_id);
433 -- Logical Sales Order Issue
434
435 savepoint CSILOSHP;
436
437 XNP_CSILOSHP_U.publish(
438 xnp$mtl_transaction_id => l_log_trx_id,
439 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
440 xnp$organization_id => r_mtl_data.organization_id,
441 x_message_id => l_message_id, -- out parameter
442 x_error_code => l_error_code, -- out parameter
443 x_error_message => l_error_message -- out parameter
444 );
445
446 if (l_error_message is not null) then
447 rollback to CSILOSHP;
448 debug('Failed to publish event CSILOSHP: ' || l_error_message);
449 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
450 fnd_message.set_token('EVENT','CSILOSHP');
451 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
452 l_error_message := fnd_message.get;
453 raise HOOK_ERROR;
454 end if;
455 ELSE
456 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||l_log_trx_id);
457 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSILOSHP',
458 l_log_trx_id,
459 l_trx_return_status,
460 l_trx_error_rec);
461
462 IF NOT l_trx_return_status = l_fnd_success THEN
463 raise BYPASS_ERROR;
464 END IF;
465
466 END IF;
467
468
469 ELSIF (r_mtl_data.transaction_action_id = 1 AND
470 r_mtl_data.transaction_source_type_id = 4 AND
471 r_type_class.type_class = 1) -- Issue to Project
472 THEN
473 l_type_id := 113;
474 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'MOVE_ORDER_ISSUE_TO_PROJECT'),'INV');
475 l_source_type := 'CSIISUPT';
476 IF (l_bypass = 'N') THEN
477 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
478 -- Issue to Project Move Order
479
480 savepoint CSIISUPT;
481
482 XNP_CSIISUPT_U.publish(
483 xnp$mtl_transaction_id => p_transaction_id,
484 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
485 xnp$organization_id => r_mtl_data.organization_id,
486 x_message_id => l_message_id, -- out parameter
487 x_error_code => l_error_code, -- out parameter
488 x_error_message => l_error_message -- out parameter
489 );
490
491 if (l_error_message is not null) then
492 rollback to CSIISUPT;
493 debug('Failed to publish event CSIISUPT: ' || l_error_message);
494 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
495 fnd_message.set_token('EVENT','CSIISUPT');
496 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
497 l_error_message := fnd_message.get;
498 raise HOOK_ERROR;
499 end if;
500 ELSE
501 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
502 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIISUPT',
503 p_transaction_id,
504 l_trx_return_status,
505 l_trx_error_rec);
506
507 IF NOT l_trx_return_status = l_fnd_success THEN
508 raise BYPASS_ERROR;
509 END IF;
510
511 END IF;
512
513 ELSIF (r_mtl_data.transaction_action_id = 1 AND -- Misc. Issue to Project
514 -- Acct/Acct Alias, Inv
515 r_mtl_data.transaction_source_type_id in (3,6,13) AND
516 r_type_class.type_class = 1)
517 THEN
518 l_type_id := 121;
519 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'MISC_ISSUE_TO_PROJECT'),'INV');
520 l_source_type := 'CSIMSIPT';
521 IF (l_bypass = 'N') THEN
522 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
523 -- Miscellaneous Issue to Project
524
525 savepoint CSIMSIPT;
526
527 XNP_CSIMSIPT_U.publish(
528 xnp$mtl_transaction_id => p_transaction_id,
529 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
530 xnp$organization_id => r_mtl_data.organization_id,
531 x_message_id => l_message_id, -- out parameter
532 x_error_code => l_error_code, -- out parameter
533 x_error_message => l_error_message -- out parameter
534 );
535
536 if (l_error_message is not null) then
537 rollback to CSIMSIPT;
538 debug('Failed to publish event CSIMSIPT: ' || l_error_message);
539 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
540 fnd_message.set_token('EVENT','CSIMSIPT');
541 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
542 l_error_message := fnd_message.get;
543 raise HOOK_ERROR;
544 end if;
545 ELSE
546 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
547 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIMSIPT',
548 p_transaction_id,
549 l_trx_return_status,
550 l_trx_error_rec);
551
552 IF NOT l_trx_return_status = l_fnd_success THEN
553 raise BYPASS_ERROR;
554 END IF;
555
556 END IF;
557
558 ELSIF (r_mtl_data.transaction_action_id = 27 AND
559 r_mtl_data.transaction_source_type_id in (3,6,13) AND
560 r_type_class.type_class = 1) -- Misc Receipt from Project
561 -- Acct/Acct Alias, Inv
562 THEN
563 l_type_id := 120;
564 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'MISC_RECEIPT_FROM_PROJECT'),'INV');
565 l_source_type := 'CSIMSRPT';
566 IF (l_bypass = 'N') THEN
567 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
568 -- Miscellaneous Receipt from Project/Task
569
570 savepoint CSIMSRPT;
571
572 XNP_CSIMSRPT_U.publish(
573 xnp$mtl_transaction_id => p_transaction_id,
574 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
575 xnp$organization_id => r_mtl_data.organization_id,
576 x_message_id => l_message_id, -- out parameter
577 x_error_code => l_error_code, -- out parameter
578 x_error_message => l_error_message -- out parameter
579 );
580
581 if (l_error_message is not null) then
582 rollback to CSIMSRPT;
583 debug('Failed to publish event CSIMSRPT: ' || l_error_message);
584 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
585 fnd_message.set_token('EVENT','CSIMSRPT');
586 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
587 l_error_message := fnd_message.get;
588 raise HOOK_ERROR;
589 end if;
590 ELSE
591 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
592 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIMSRPT',
593 p_transaction_id,
594 l_trx_return_status,
595 l_trx_error_rec);
596
597 IF NOT l_trx_return_status = l_fnd_success THEN
598 raise BYPASS_ERROR;
599 END IF;
600
601 END IF;
602
603 ELSIF (r_mtl_data.transaction_action_id = 1 AND
604 r_mtl_data.transaction_source_type_id = 16)
605
606 THEN
607 ---Transactions fall in this category are :
608 --- Type Action ID Txn Type ID
609 ----------------------- ------------- ------------
610 --1. Project Contract Issue 1 77
611
612 l_type_id := 326;
613 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'PROJECT_CONTRACT_SHIPMENT'),'OKE');
614 l_source_type := 'CSIOKSHP';
615 IF (l_bypass = 'N') THEN
616 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
617 -- Project Contract Issues
618
619 savepoint CSIOKSHP;
620
621 XNP_CSIOKSHP_U.publish(
622 xnp$mtl_transaction_id => p_transaction_id,
623 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
624 xnp$organization_id => r_mtl_data.organization_id,
625 x_message_id => l_message_id, -- out parameter
626 x_error_code => l_error_code, -- out parameter
627 x_error_message => l_error_message -- out parameter
628 );
629
630 if (l_error_message is not null) then
631 rollback to CSIOKSHP;
632 debug('Failed to publish event CSIOKSHP: ' || l_error_message);
633 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
634 fnd_message.set_token('EVENT','CSIOKSHP');
635 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
636 l_error_message := fnd_message.get;
637 raise HOOK_ERROR;
638 end if;
639 ELSE
640 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
641 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIOKSHP',
642 p_transaction_id,
643 l_trx_return_status,
644 l_trx_error_rec);
645
646 IF NOT l_trx_return_status = l_fnd_success THEN
647 raise BYPASS_ERROR;
648 END IF;
649
650 END IF;
651
652 ELSIF (r_mtl_data.transaction_action_id = 1 AND
653 r_mtl_data.transaction_source_type_id = 2)
654 -- Changed to 2 from Txn Type ID 33
655 THEN
656 ---Transactions fall in this category are :
657 --- Type Action ID Txn Type ID
658 ----------------------- ------------- ------------
659 --1. Sales Order Issue 1 33
660
661 l_type_id := 51;
662 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'OM_SHIPMENT'),'ONT');
663 l_source_type := 'CSISOSHP';
664 IF (l_bypass = 'N') THEN
665 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
666 -- Sales Order Shipments
667
668 savepoint CSISOSHP;
669
670 XNP_CSISOSHP_U.publish(
671 xnp$mtl_transaction_id => p_transaction_id,
672 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
673 xnp$organization_id => r_mtl_data.organization_id,
674 x_message_id => l_message_id, -- out parameter
675 x_error_code => l_error_code, -- out parameter
676 x_error_message => l_error_message -- out parameter
677 );
678
679 if (l_error_message is not null) then
680 rollback to CSISOSHP;
681 debug('Failed to publish event CSISOSHP: ' || l_error_message);
682 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
683 fnd_message.set_token('EVENT','CSISOSHP');
684 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
685 l_error_message := fnd_message.get;
686 raise HOOK_ERROR;
687 end if;
688 ELSE
689 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
690 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSISOSHP',
691 p_transaction_id,
692 l_trx_return_status,
693 l_trx_error_rec);
694
695 IF NOT l_trx_return_status = l_fnd_success THEN
696 raise BYPASS_ERROR;
697 END IF;
698
699 END IF;
700
701 ELSIF (r_mtl_data.transaction_action_id = 1 AND
702 r_mtl_data.transaction_source_type_id = 8)
703 THEN
704 ---Transactions fall in this category are :
705 --- Type Action ID Txn Type ID
706 ----------------------- ------------- ------------
707 --1. Intrnl Ord Issue(Ship Conf) 1 34
708
709 l_type_id := 126;
710 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'ISO_ISSUE'),'ONT');
711 l_source_type := 'CSIINTIS';
712 IF (l_bypass = 'N') THEN
713 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
714 -- Internal Order Issue
715
716 savepoint CSIINTIS;
717
718 XNP_CSIINTIS_U.publish(
719 xnp$mtl_transaction_id => p_transaction_id,
720 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
721 xnp$organization_id => r_mtl_data.organization_id,
722 x_message_id => l_message_id, -- out parameter
723 x_error_code => l_error_code, -- out parameter
724 x_error_message => l_error_message -- out parameter
725 );
726
727 if (l_error_message is not null) then
728 rollback to CSIINTIS;
729 debug('Failed to publish event CSIINTIS: ' || l_error_message);
730 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
731 fnd_message.set_token('EVENT','CSIINTIS');
732 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
733 l_error_message := fnd_message.get;
734 raise HOOK_ERROR;
735 end if;
736 ELSE
737 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
738 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIINTIS',
739 p_transaction_id,
740 l_trx_return_status,
741 l_trx_error_rec);
742
743 IF NOT l_trx_return_status = l_fnd_success THEN
744 raise BYPASS_ERROR;
745 END IF;
746
747 END IF;
748
749 ELSIF (r_mtl_data.transaction_action_id = 27 AND
750 r_mtl_data.transaction_source_type_id = 12)
751 -- Changed to 12 from Txn Type ID 15
752 THEN
753 ---Transactions fall in this category are :
754 --- Type Action ID Txn Type ID
755 ----------------------- ------------- ------------
756 --1. RMA Receipt 27 15
757
758 l_type_id := 53;
759 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'RMA_SHIPMENT'),'ONT');
760 l_source_type := 'CSIRMARC';
761 IF (l_bypass = 'N') THEN
762 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
763 -- Return Material Authorization
764
765 savepoint CSIRMARC;
766
767 XNP_CSIRMARC_U.publish(
768 xnp$mtl_transaction_id => p_transaction_id,
769 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
770 xnp$organization_id => r_mtl_data.organization_id,
771 x_message_id => l_message_id, -- out parameter
772 x_error_code => l_error_code, -- out parameter
773 x_error_message => l_error_message -- out parameter
774 );
775
776 if (l_error_message is not null) then
777 rollback to CSIRMARC;
778 debug('Failed to publish event CSIRMARC: ' || l_error_message);
779 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
780 fnd_message.set_token('EVENT','CSIRMARC');
781 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
782 l_error_message := fnd_message.get;
783 raise HOOK_ERROR;
784 end if;
785 ELSE
786 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
787 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIRMARC',
788 p_transaction_id,
789 l_trx_return_status,
790 l_trx_error_rec);
791
792 IF NOT l_trx_return_status = l_fnd_success THEN
793 raise BYPASS_ERROR;
794 END IF;
795
796 END IF;
797
798 ELSIF (r_mtl_data.transaction_quantity > 0 AND -- Subinventory Transfer
799 r_mtl_data.transaction_action_id = 2)
800 OR (r_mtl_data.transaction_action_id = 28 AND -- Sales Order Staging
801 r_mtl_data.transaction_source_type_id = 2 AND
802 -- Changed to 2 from Txn ID 52
803 r_mtl_data.transaction_quantity > 0)
804 OR (r_mtl_data.transaction_action_id = 28 AND -- Intrnl SaleOrd Staging
805 r_mtl_data.transaction_source_type_id = 8 AND
806 r_mtl_data.transaction_quantity > 0)
807 -- Changed to 8 from Txn ID 53
808 -- changed this to > for bug 2384317
809 THEN
810 ---Transactions fall in this category are :
811 --- Type Action ID Txn Type ID
812 ----------------------- ------------- ------------
813 --1. Subinventory Transfer 2 2
814 --2. Cycle Count SubInv Xfer 2 5
815 --3. Physical Inv Xfer 2 9
816 --4. Internal Order Xfer 2 50
817 --5. Backflush Xfer 2 51
818 --6. Internal Order Pick 28 53
819 --7. Sales Order Pick 28 52
820 --8. Move Order Transfer 2 64
821 --9. Project Borrow 2 66
822 --10. Project Transfer 2 67
823 --11. Project Payback 2 68
824
825 l_type_id := 114;
826 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'SUBINVENTORY_TRANSFER'),'INV');
827 l_source_type := 'CSISUBTR';
828 IF (l_bypass = 'N') THEN
829 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
830 -- Subinventory Transfer and Sales Order Stanging
831
832 savepoint CSISUBTR;
833
834 XNP_CSISUBTR_U.publish(
835 xnp$mtl_transaction_id => p_transaction_id,
836 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
837 xnp$organization_id => r_mtl_data.organization_id,
838 x_message_id => l_message_id, -- out parameter
839 x_error_code => l_error_code, -- out parameter
840 x_error_message => l_error_message -- out parameter
841 );
842
843 if (l_error_message is not null) then
844 rollback to CSISUBTR;
845 debug('Failed to publish event CSISUBTR: ' || l_error_message);
846 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
847 fnd_message.set_token('EVENT','CSISUBTR');
848 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
849 l_error_message := fnd_message.get;
850 raise HOOK_ERROR;
851 end if;
852 ELSE
853 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
854 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSISUBTR',
855 p_transaction_id,
856 l_trx_return_status,
857 l_trx_error_rec);
858
859 IF NOT l_trx_return_status = l_fnd_success THEN
860 raise BYPASS_ERROR;
861 END IF;
862
863 END IF;
864
865 ELSIF (r_mtl_data.transaction_action_id = 12 AND -- Interorg Receipt
866 r_mtl_data.transaction_source_type_id = 13)
867 -- Changed to 13 from Txn ID 12
868
869 THEN
870 ---Transactions fall in this category are :
871 --- Type Action ID Txn Type ID
872 ----------------------- ------------- ------------
873 --1. InTransit Receipt 12 12
874
875 l_type_id := 144;
876 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'INTERORG_TRANS_RECEIPT'),'INV');
877 l_source_type := 'CSIORGTR';
878 IF (l_bypass = 'N') THEN
879 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
880 -- InterOrg In Transit Receipt Transaction
881
882 savepoint CSIORGTR;
883
884 XNP_CSIORGTR_U.publish(
885 xnp$mtl_transaction_id => p_transaction_id,
886 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
887 xnp$organization_id => r_mtl_data.organization_id,
888 x_message_id => l_message_id, -- out parameter
889 x_error_code => l_error_code, -- out parameter
890 x_error_message => l_error_message -- out parameter
891 );
892
893 if (l_error_message is not null) then
894 rollback to CSIORGTR;
895 debug('Failed to publish event CSIORGTR: ' || l_error_message);
896 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
897 fnd_message.set_token('EVENT','CSIORGTR');
898 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
899 l_error_message := fnd_message.get;
900 raise HOOK_ERROR;
901 end if;
902 ELSE
903 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
904 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIORGTR',
905 p_transaction_id,
906 l_trx_return_status,
907 l_trx_error_rec);
908
909 IF NOT l_trx_return_status = l_fnd_success THEN
910 raise BYPASS_ERROR;
911 END IF;
912
913 END IF;
914
915 ELSIF (r_mtl_data.transaction_action_id = 21 AND
916 r_mtl_data.transaction_source_type_id = 13) -- Interorg Shipment
917 -- Changed to 13 from Txn ID 21
918
919 THEN
920 ---Transactions fall in this category are :
921 --- Type Action ID Txn Type ID
922 ----------------------- ------------- ------------
923 --1. InTransit Shipment 21 21
924
925 l_type_id := 145;
926 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'INTERORG_TRANS_SHIPMENT'),'INV');
927 l_source_type := 'CSIORGTS';
928 IF (l_bypass = 'N') THEN
929 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
930 -- InterOrg In Transit Receipt Transaction
931
932 savepoint CSIORGTS;
933
934 XNP_CSIORGTS_U.publish(
935 xnp$mtl_transaction_id => p_transaction_id,
936 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
937 xnp$organization_id => r_mtl_data.organization_id,
938 x_message_id => l_message_id, -- out parameter
939 x_error_code => l_error_code, -- out parameter
940 x_error_message => l_error_message -- out parameter
941 );
942
943 if (l_error_message is not null) then
944 rollback to CSIORGTS;
945 debug('Failed to publish event CSIORGTS: ' || l_error_message);
946 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
947 fnd_message.set_token('EVENT','CSIORGTS');
948 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
949 l_error_message := fnd_message.get;
950 raise HOOK_ERROR;
951 end if;
952 ELSE
953 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
954 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIORGTS',
955 p_transaction_id,
956 l_trx_return_status,
957 l_trx_error_rec);
958
959 IF NOT l_trx_return_status = l_fnd_success THEN
960 raise BYPASS_ERROR;
961 END IF;
962
963 END IF;
964
965 ELSIF (r_mtl_data.transaction_action_id = 3 AND -- Direct Org Transfer
966 r_mtl_data.transaction_source_type_id = 13 AND
967 r_mtl_data.transaction_quantity > 0)
968 -- Changed to 13 from Txn ID 3
969 THEN
970 ---Transactions fall in this category are :
971 --- Type Action ID Txn Type ID
972 ----------------------- ------------- ------------
973 --1. Direct Org Transfer 3 3
974
975 l_type_id := 143;
976 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'INTERORG_DIRECT_SHIP'),'INV');
977 l_source_type := 'CSIORGDS';
978 IF (l_bypass = 'N') THEN
979 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
980 -- InterOrg Direct Shipment Transaction
981
982 savepoint CSIORGDS;
983
984 XNP_CSIORGDS_U.publish(
985 xnp$mtl_transaction_id => p_transaction_id,
986 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
987 xnp$organization_id => r_mtl_data.organization_id,
988 x_message_id => l_message_id, -- out parameter
989 x_error_code => l_error_code, -- out parameter
990 x_error_message => l_error_message -- out parameter
991 );
992
993 if (l_error_message is not null) then
994 rollback to CSIORGDS;
995 debug('Failed to publish event CSIORGDS: ' || l_error_message);
996 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
997 fnd_message.set_token('EVENT','CSIORGDS');
998 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
999 l_error_message := fnd_message.get;
1000 raise HOOK_ERROR;
1001 end if;
1002 ELSE
1003 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1004 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIORGDS',
1005 p_transaction_id,
1006 l_trx_return_status,
1007 l_trx_error_rec);
1008
1009 IF NOT l_trx_return_status = l_fnd_success THEN
1010 raise BYPASS_ERROR;
1011 END IF;
1012
1013 END IF;
1014
1015 ELSIF (r_mtl_data.transaction_action_id = 12 AND -- Int So In Trans Receipt
1016 r_mtl_data.transaction_source_type_id = 7)
1017 THEN
1018 ---Transactions fall in this category are :
1019 --- Type Action ID Txn Type ID
1020 ----------------------- ------------- ------------
1021 --1. Int Req Intr Rcpt 12 61
1022
1023 l_type_id := 131;
1024 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'ISO_REQUISITION_RECEIPT'),'INV');
1025 l_source_type := 'CSIINTSR';
1026 IF (l_bypass = 'N') THEN
1027 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1028 -- Internal Sales Order Receipt Transaction
1029
1030 savepoint CSIINTSR;
1031
1032 XNP_CSIINTSR_U.publish(
1033 xnp$mtl_transaction_id => p_transaction_id,
1034 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1035 xnp$organization_id => r_mtl_data.organization_id,
1036 x_message_id => l_message_id, -- out parameter
1037 x_error_code => l_error_code, -- out parameter
1038 x_error_message => l_error_message -- out parameter
1039 );
1040
1041 if (l_error_message is not null) then
1042 rollback to CSIINTSR;
1043 debug('Failed to publish event CSIINTSR: ' || l_error_message);
1044 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1045 fnd_message.set_token('EVENT','CSIINTSR');
1046 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1047 l_error_message := fnd_message.get;
1048 raise HOOK_ERROR;
1049 end if;
1050 ELSE
1051 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1052 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIINTSR',
1053 p_transaction_id,
1054 l_trx_return_status,
1055 l_trx_error_rec);
1056
1057 IF NOT l_trx_return_status = l_fnd_success THEN
1058 raise BYPASS_ERROR;
1059 END IF;
1060
1061 END IF;
1062
1063 ELSIF (r_mtl_data.transaction_action_id = 21 AND -- Int So In Trans Ship
1064 r_mtl_data.transaction_source_type_id = 8)
1065
1066 THEN
1067 ---Transactions fall in this category are :
1068 --- Type Action ID Txn Type ID
1069 ----------------------- ------------- ------------
1070 --1. Int Order Intr Ship 21 62
1071
1072 l_type_id := 130;
1073 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'ISO_SHIPMENT'),'INV');
1074 l_source_type := 'CSIINTSS';
1075 IF (l_bypass = 'N') THEN
1076 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1077 -- Internal Sales Order Shipment Transaction
1078
1079 savepoint CSIINTSS;
1080
1081 XNP_CSIINTSS_U.publish(
1082 xnp$mtl_transaction_id => p_transaction_id,
1083 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1084 xnp$organization_id => r_mtl_data.organization_id,
1085 x_message_id => l_message_id, -- out parameter
1086 x_error_code => l_error_code, -- out parameter
1087 x_error_message => l_error_message -- out parameter
1088 );
1089
1090 if (l_error_message is not null) then
1091 rollback to CSIINTSS;
1092 debug('Failed to publish event CSIINTSS: ' || l_error_message);
1093 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1094 fnd_message.set_token('EVENT','CSIINTSS');
1095 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1096 l_error_message := fnd_message.get;
1097 raise HOOK_ERROR;
1098 end if;
1099 ELSE
1100 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1101 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIINTSS',
1102 p_transaction_id,
1103 l_trx_return_status,
1104 l_trx_error_rec);
1105
1106 IF NOT l_trx_return_status = l_fnd_success THEN
1107 raise BYPASS_ERROR;
1108 END IF;
1109
1110 END IF;
1111
1112 ELSIF (r_mtl_data.transaction_action_id = 3 AND -- ISO Direct Shipment
1113 r_mtl_data.transaction_source_type_id in (7,8) AND
1114 r_mtl_data.transaction_quantity > 0)
1115
1116 THEN
1117 ---Transactions fall in this category are :
1118 --- Type Action ID Txn Type ID
1119 ----------------------- ------------- ------------
1120 --1. Int Order Direct Ship 3 54
1121
1122 l_type_id := 142;
1123 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'ISO_DIRECT_SHIP'),'INV');
1124 l_source_type := 'CSIINTDS';
1125 IF (l_bypass = 'N') THEN
1126 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1127 -- Internal Sales Order Direct Shipment Transaction
1128
1129 savepoint CSIINTDS;
1130
1131 XNP_CSIINTDS_U.publish(
1132 xnp$mtl_transaction_id => p_transaction_id,
1133 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1134 xnp$organization_id => r_mtl_data.organization_id,
1135 x_message_id => l_message_id, -- out parameter
1136 x_error_code => l_error_code, -- out parameter
1137 x_error_message => l_error_message -- out parameter
1138 );
1139
1140 if (l_error_message is not null) then
1141 rollback to CSIINTDS;
1142 debug('Failed to publish event CSIINTDS: ' || l_error_message);
1143 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1144 fnd_message.set_token('EVENT','CSIINTSS');
1145 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1146 l_error_message := fnd_message.get;
1147 raise HOOK_ERROR;
1148 end if;
1149 ELSE
1150 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1151 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIINTDS',
1152 p_transaction_id,
1153 l_trx_return_status,
1154 l_trx_error_rec);
1155
1156 IF NOT l_trx_return_status = l_fnd_success THEN
1157 raise BYPASS_ERROR;
1158 END IF;
1159
1160 END IF;
1161
1162 ELSIF r_mtl_data.transaction_action_id = 27 AND
1163 r_mtl_data.transaction_source_type_id = 1
1164 -- Changed to 1 from Txn Type ID 18
1165
1166 ---Transactions fall in this category are :
1167 --- Type Action ID Txn Type ID
1168 ----------------------- ------------- ------------
1169 --1. PO Receipt 27 18
1170
1171 THEN
1172 l_type_id := 112;
1173 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'PO_RECEIPT_INTO_INVENTORY'),'INV');
1174 l_source_type := 'CSIPOINV';
1175 IF (l_bypass = 'N') THEN
1176 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1177 -- PO Receipt into Inventory
1178
1179 savepoint CSIPOINV;
1180
1181 XNP_CSIPOINV_U.publish(
1182 xnp$mtl_transaction_id => p_transaction_id,
1183 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1184 xnp$organization_id => r_mtl_data.organization_id,
1185 x_message_id => l_message_id, -- out parameter
1186 x_error_code => l_error_code, -- out parameter
1187 x_error_message => l_error_message -- out parameter
1188 );
1189
1190 if (l_error_message is not null) then
1191 rollback to CSIPOINV;
1192 debug('Failed to publish event CSIPOINV: ' || l_error_message);
1193 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1194 fnd_message.set_token('EVENT','CSIPOINV');
1195 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1196 l_error_message := fnd_message.get;
1197 raise HOOK_ERROR;
1198 end if;
1199 ELSE
1200 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1201 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIPOINV',
1202 p_transaction_id,
1203 l_trx_return_status,
1204 l_trx_error_rec);
1205
1206 IF NOT l_trx_return_status = l_fnd_success THEN
1207 raise BYPASS_ERROR;
1208 END IF;
1209
1210 END IF;
1211
1212 ELSIF r_mtl_data.transaction_action_id = 4
1213
1214 ---Transactions fall in this category are :
1215 --- Type Action ID Txn Type ID
1216 ----------------------- ------------- ------------
1217 --1. Cycle Count Adjust (-/+) 4 4
1218
1219 THEN
1220 l_type_id := 119;
1221 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'CYCLE_COUNT'),'INV');
1222 l_source_type := 'CSICYCNT';
1223 IF (l_bypass = 'N') THEN
1224 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1225 -- Cycle Count
1226
1227 savepoint CSICYCNT;
1228
1229 XNP_CSICYCNT_U.publish(
1230 xnp$mtl_transaction_id => p_transaction_id,
1231 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1232 xnp$organization_id => r_mtl_data.organization_id,
1233 x_message_id => l_message_id, -- out parameter
1234 x_error_code => l_error_code, -- out parameter
1235 x_error_message => l_error_message -- out parameter
1236 );
1237
1238 if (l_error_message is not null) then
1239 rollback to CSICYCNT;
1240 debug('Failed to publish event CSICYCNT: ' || l_error_message);
1241 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1242 fnd_message.set_token('EVENT','CSICYCNT');
1243 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1244 l_error_message := fnd_message.get;
1245 raise HOOK_ERROR;
1246 end if;
1247 ELSE
1248 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1249 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSICYCNT',
1250 p_transaction_id,
1251 l_trx_return_status,
1252 l_trx_error_rec);
1253
1254 IF NOT l_trx_return_status = l_fnd_success THEN
1255 raise BYPASS_ERROR;
1256 END IF;
1257
1258 END IF;
1259
1260 ELSIF r_mtl_data.transaction_action_id = 8
1261
1262 ---Transactions fall in this category are :
1263 --- Type Action ID Txn Type ID
1264 ----------------------- ------------- ------------
1265 --1. Physical Inv Adjust(-/+) 8 8
1266
1267 THEN
1268 l_type_id := 118;
1269 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'PHYSICAL_INVENTORY'),'INV');
1270 l_source_type := 'CSIPHYIN';
1271 IF (l_bypass = 'N') THEN
1272 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1273 -- Physical Inventory
1274
1275 savepoint CSIPHYIN;
1276
1277 XNP_CSIPHYIN_U.publish(
1278 xnp$mtl_transaction_id => p_transaction_id,
1279 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1280 xnp$organization_id => r_mtl_data.organization_id,
1281 x_message_id => l_message_id, -- out parameter
1282 x_error_code => l_error_code, -- out parameter
1283 x_error_message => l_error_message -- out parameter
1284 );
1285
1286 if (l_error_message is not null) then
1287 rollback to CSIPHYIN;
1288 debug('Failed to publish event CSIPHYIN: ' || l_error_message);
1289 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1290 fnd_message.set_token('EVENT','CSIPHYIN');
1291 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1292 l_error_message := fnd_message.get;
1293 raise HOOK_ERROR;
1294 end if;
1295
1296 ELSE
1297 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1298 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIPHYIN',
1299 p_transaction_id,
1300 l_trx_return_status,
1301 l_trx_error_rec);
1302
1303 IF NOT l_trx_return_status = l_fnd_success THEN
1304 raise BYPASS_ERROR;
1305 END IF;
1306
1307 END IF;
1308
1309 ELSIF (r_mtl_data.transaction_action_id = 27 AND
1310 r_mtl_data.transaction_source_type_id in (4,13,6,3) AND
1311 r_mtl_data.transaction_type_id NOT IN (15,123,43,94) AND
1312 (r_type_class.type_class is null OR r_type_class.type_class <> 1)) OR
1313 (r_mtl_data.transaction_action_id = 29 AND
1314 r_mtl_data.transaction_quantity > 0 AND
1315 r_mtl_data.transaction_source_type_id = 1) OR -- + Int Adjustment
1316 -- + PO Adjustment
1317 -- + Ship Adjustment
1318 (l_log_trx_action_id = 11 AND
1319 r_mtl_data.transaction_quantity > 0 AND
1320 l_log_trx_source_type_id = 1 AND
1321 l_log_trx_type_code = 2) -- (+) Logical PO Adjustment
1322 THEN
1323 ---Transactions fall in this category are :
1324 --- Type Action ID Txn Type ID
1325 ----------------------- ------------- ------------
1326 --1. Account Receipt 27 40
1327 --2. Account Alias receipt 27 41
1328 --3. Miscellaneous Receipt 27 42
1329 --4. + PO Adjustment 29 71
1330 --5. + Int Req Adjust 29 72
1331 --6. + Shipment Rcpt Adjust 29 70
1332
1333 l_type_id := 117;
1334 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'MISC_RECEIPT'),'INV');
1335 l_source_type := 'CSIMSRCV';
1336 IF (l_bypass = 'N') THEN
1337 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1338 -- Miscellaneous Receipt
1339
1340 savepoint CSIMSRCV;
1341
1342 XNP_CSIMSRCV_U.publish(
1343 xnp$mtl_transaction_id => p_transaction_id,
1344 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1345 xnp$organization_id => r_mtl_data.organization_id,
1346 x_message_id => l_message_id, -- out parameter
1347 x_error_code => l_error_code, -- out parameter
1348 x_error_message => l_error_message -- out parameter
1349 );
1350
1351 if (l_error_message is not null) then
1352 rollback to CSIMSRCV;
1353 debug('Failed to publish event CSIMSRCV: ' || l_error_message);
1354 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1355 fnd_message.set_token('EVENT','CSIMSRCV');
1356 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1357 l_error_message := fnd_message.get;
1358 raise HOOK_ERROR;
1359 end if;
1360
1361 ELSE
1362 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1363 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIMSRCV',
1364 p_transaction_id,
1365 l_trx_return_status,
1366 l_trx_error_rec);
1367
1368 IF NOT l_trx_return_status = l_fnd_success THEN
1369 raise BYPASS_ERROR;
1370 END IF;
1371
1372 END IF;
1373
1374
1375 ELSIF (r_mtl_data.transaction_action_id = 1 AND
1376 r_mtl_data.transaction_source_type_id in (4,13,6,3) AND
1377 r_mtl_data.transaction_type_id NOT IN (33,122,35,37,93) AND
1378 (r_type_class.type_class is null OR r_type_class.type_class <> 1)) OR
1379 (r_mtl_data.transaction_action_id = 29 AND
1380 r_mtl_data.transaction_quantity < 0 AND
1381 r_mtl_data.transaction_source_type_id = 1) OR -- (-) PO Adjustment
1382 (r_mtl_data.transaction_action_id = 1 AND
1383 r_mtl_data.transaction_quantity < 0 AND
1384 r_mtl_data.transaction_source_type_id = 1) OR -- (-) Return to Vendor
1385 (l_log_trx_action_id = 11 AND
1386 r_mtl_data.transaction_quantity < 0 AND
1387 l_log_trx_source_type_id = 1 AND
1388 l_log_trx_type_code = 2) -- (-) Logical PO Adjustment
1389 THEN
1390 ---Transactions fall in this category are :
1391 --- Type Action ID Txn Type ID
1392 ----------------------- ------------- ------------
1393 --1. Account Alias Issue 1 31
1394 --2. Miscellaneous Issue 1 32
1395 --4. Return to Vendor (PO) 1 36
1396 --5. Account Issue 1 1
1397 --6. (-) PO Adjustment 29 71
1398 --7. (-) Int Req Adjust 29 72
1399 --8. (-) Shipment Rcp Adjust 29 70
1400 --9. Move Order Issue 1 63 (recheck)
1401
1402 --EXCLUDED TRANSACTIONS ARE
1403 -- 33 Sales order issue
1404 -- 35 WIP component issue
1405 -- 37 RMA Return
1406 -- 93 Field Service Usage
1407 -- 122 Issue to (User Defined Seeded)
1408
1409
1410 l_type_id := 116;
1411 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'MISC_ISSUE'),'INV');
1412 l_source_type := 'CSIMSISU';
1413 IF (l_bypass = 'N') THEN
1414 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1415 -- Miscellaneous Issue
1416
1417 savepoint CSIMSISU;
1418
1419 XNP_CSIMSISU_U.publish(
1420 xnp$mtl_transaction_id => p_transaction_id,
1421 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1422 xnp$organization_id => r_mtl_data.organization_id,
1423 x_message_id => l_message_id, -- out parameter
1424 x_error_code => l_error_code, -- out parameter
1425 x_error_message => l_error_message -- out parameter
1426 );
1427
1428 if (l_error_message is not null) then
1429 rollback to CSIMSISU;
1430 debug('Failed to publish event CSIMSISU: ' || l_error_message);
1431 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1432 fnd_message.set_token('EVENT','CSIMSISU');
1433 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1434 l_error_message := fnd_message.get;
1435 raise HOOK_ERROR;
1436
1437 end if;
1438 ELSE
1439 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1440 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIMSISU',
1441 p_transaction_id,
1442 l_trx_return_status,
1443 l_trx_error_rec);
1444
1445 IF NOT l_trx_return_status = l_fnd_success THEN
1446 raise BYPASS_ERROR;
1447 END IF;
1448
1449 END IF;
1450
1451 ELSIF (r_mtl_data.transaction_action_id = 32 AND
1452 r_mtl_data.transaction_source_type_id = 5)
1453 -- Changed to 5 from Txn Type ID 17
1454 THEN
1455 ---Transactions fall in this category are :
1456 --- Type Action ID Txn Type ID
1457 ----------------------- ------------- ------------
1458 --1. WIP Assembly Return 32 17
1459
1460 OPEN c_wip_entity_type;
1461 FETCH c_wip_entity_type into l_wip_entity_type;
1462 CLOSE c_wip_entity_type;
1463
1464
1465 IF l_wip_entity_type =10 AND r_mtl_data.transaction_type_id =1003 THEN
1466 l_type_id := 76; --new transaction type to support WIP_ByProduct Return
1467 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'WIP_BYPRODUCT_RETURN'),'INV');
1468 l_source_type := 'CSIWIPBR';
1469
1470 IF (l_bypass = 'N') THEN
1471 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1472 -- WIP By Product return Return
1473 savepoint CSIWIPBR;
1474 XNP_CSIWIPBR_U.publish( ----New message to be published
1475 xnp$mtl_transaction_id => p_transaction_id,
1476 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1477 xnp$organization_id => r_mtl_data.organization_id,
1478 x_message_id => l_message_id, -- out parameter
1479 x_error_code => l_error_code, -- out parameter
1480 x_error_message => l_error_message -- out parameter
1481 );
1482
1483 if (l_error_message is not null) then
1484 rollback to CSIWIPBR;
1485 debug('Failed to publish event CSIWIPBR: ' || l_error_message);
1486 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1487 fnd_message.set_token('EVENT','CSIWIPBR');
1488 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1489 l_error_message := fnd_message.get;
1490 raise HOOK_ERROR;
1491 end if;
1492 ELSE
1493 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1494 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPBR',
1495 p_transaction_id,
1496 l_trx_return_status,
1497 l_trx_error_rec);
1498
1499 IF NOT l_trx_return_status = l_fnd_success THEN
1500 raise BYPASS_ERROR;
1501 END IF;
1502
1503 END IF;
1504
1505
1506 ELSE
1507
1508 l_type_id := 74;
1509 l_source_type := 'CSIWIPAR';
1510 IF (l_bypass = 'N') THEN
1511 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1512 -- WIP Assembly Return
1513
1514 savepoint CSIWIPAR;
1515
1516 XNP_CSIWIPAR_U.publish(
1517 xnp$mtl_transaction_id => p_transaction_id,
1518 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1519 xnp$organization_id => r_mtl_data.organization_id,
1520 x_message_id => l_message_id, -- out parameter
1521 x_error_code => l_error_code, -- out parameter
1522 x_error_message => l_error_message -- out parameter
1523 );
1524
1525 if (l_error_message is not null) then
1526 rollback to CSIWIPAR;
1527 debug('Failed to publish event CSIWIPAR: ' || l_error_message);
1528 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1529 fnd_message.set_token('EVENT','CSIWIPAR');
1530 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1531 l_error_message := fnd_message.get;
1532 raise HOOK_ERROR;
1533 end if;
1534 ELSE
1535 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1536 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPAR',
1537 p_transaction_id,
1538 l_trx_return_status,
1539 l_trx_error_rec);
1540
1541 IF NOT l_trx_return_status = l_fnd_success THEN
1542 raise BYPASS_ERROR;
1543 END IF;
1544
1545 END IF;
1546 END IF;
1547
1548 ELSIF (r_mtl_data.transaction_action_id = 1 AND
1549 r_mtl_data.transaction_source_type_id = 5)
1550 -- Changed to 5 from Txn Type ID 35
1551 THEN
1552 ---Transactions fall in this category are :
1553 --- Type Action ID Txn Type ID
1554 ----------------------- ------------- ------------
1555 --1. WIP Component Issue 1 35
1556
1557 l_type_id := 71;
1558 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'WIP_ISSUE'),'INV');
1559 l_source_type := 'CSIWIPCI';
1560 IF (l_bypass = 'N') THEN
1561 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1562 -- WIP Component Issue
1563
1564 savepoint CSIWIPCI;
1565
1566 XNP_CSIWIPCI_U.publish(
1567 xnp$mtl_transaction_id => p_transaction_id,
1568 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1569 xnp$organization_id => r_mtl_data.organization_id,
1570 x_message_id => l_message_id, -- out parameter
1571 x_error_code => l_error_code, -- out parameter
1572 x_error_message => l_error_message -- out parameter
1573 );
1574
1575 if (l_error_message is not null) then
1576 rollback to CSIWIPCI;
1577 debug('Failed to publish event CSIWIPCI: ' || l_error_message);
1578 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1579 fnd_message.set_token('EVENT','CSIWIPCI');
1580 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1581 l_error_message := fnd_message.get;
1582 raise HOOK_ERROR;
1583 end if;
1584 ELSE
1585 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1586 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPCI',
1587 p_transaction_id,
1588 l_trx_return_status,
1589 l_trx_error_rec);
1590
1591 IF NOT l_trx_return_status = l_fnd_success THEN
1592 raise BYPASS_ERROR;
1593 END IF;
1594
1595 END IF;
1596
1597 ELSIF (r_mtl_data.transaction_action_id = 33 AND
1598 r_mtl_data.transaction_source_type_id = 5)
1599 -- Changed to 5 from Txn Type ID 38
1600 THEN
1601 ---Transactions fall in this category are :
1602 --- Type Action ID Txn Type ID
1603 ----------------------- ------------- ------------
1604 --1. WIP Neg Comp Issue 33 38
1605
1606 l_type_id := 72;
1607 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'WIP_RECEIPT'),'INV');
1608 l_source_type := 'CSIWIPNI';
1609 IF (l_bypass = 'N') THEN
1610 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1611 -- WIP Negative Component Issue
1612
1613 savepoint CSIWIPNI;
1614
1615 XNP_CSIWIPNI_U.publish(
1616 xnp$mtl_transaction_id => p_transaction_id,
1617 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1618 xnp$organization_id => r_mtl_data.organization_id,
1619 x_message_id => l_message_id, -- out parameter
1620 x_error_code => l_error_code, -- out parameter
1621 x_error_message => l_error_message -- out parameter
1622 );
1623
1624 if (l_error_message is not null) then
1625 rollback to CSIWIPNI;
1626 debug('Failed to publish event CSIWIPNI: ' || l_error_message);
1627 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1628 fnd_message.set_token('EVENT','CSIWIPNI');
1629 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1630 l_error_message := fnd_message.get;
1631 raise HOOK_ERROR;
1632 end if;
1633 ELSE
1634 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1635 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPNI',
1636 p_transaction_id,
1637 l_trx_return_status,
1638 l_trx_error_rec);
1639
1640 IF NOT l_trx_return_status = l_fnd_success THEN
1641 raise BYPASS_ERROR;
1642 END IF;
1643
1644 END IF;
1645
1646 ELSIF (r_mtl_data.transaction_action_id = 27 AND
1647 r_mtl_data.transaction_source_type_id = 5)
1648 -- Changed to 5 from Txn Type ID 43
1649 THEN
1650 ---Transactions fall in this category are :
1651 --- Type Action ID Txn Type ID
1652 ----------------------- ------------- ------------
1653 --1. WIP Component Return 27 43
1654
1655 l_type_id := 72;
1656 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'WIP_RECEIPT'),'INV');
1657 l_source_type := 'CSIWIPCR';
1658 IF (l_bypass = 'N') THEN
1659 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1660 -- WIP Component Return
1661
1662 savepoint CSIWIPCR;
1663
1664 XNP_CSIWIPCR_U.publish(
1665 xnp$mtl_transaction_id => p_transaction_id,
1666 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1667 xnp$organization_id => r_mtl_data.organization_id,
1668 x_message_id => l_message_id, -- out parameter
1669 x_error_code => l_error_code, -- out parameter
1670 x_error_message => l_error_message -- out parameter
1671 );
1672
1673 if (l_error_message is not null) then
1674 rollback to CSIWIPCR;
1675 debug('Failed to publish event CSIWIPCR: ' || l_error_message);
1676 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1677 fnd_message.set_token('EVENT','CSIWIPCR');
1678 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1679 l_error_message := fnd_message.get;
1680 raise HOOK_ERROR;
1681 end if;
1682 ELSE
1683 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1684 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPCR',
1685 p_transaction_id,
1686 l_trx_return_status,
1687 l_trx_error_rec);
1688
1689 IF NOT l_trx_return_status = l_fnd_success THEN
1690 raise BYPASS_ERROR;
1691 END IF;
1692
1693 END IF;
1694
1695 ELSIF (r_mtl_data.transaction_action_id = 31 AND
1696 r_mtl_data.transaction_source_type_id = 5)
1697 -- Changed to 5 from Txn Type ID 44
1698 THEN
1699 ---Transactions fall in this category are :
1700 --- Type Action ID Txn Type ID
1701 ----------------------- ------------- ------------
1702 --1. WIP Assy Completion 31 44
1703
1704 OPEN c_wip_entity_type;
1705 FETCH c_wip_entity_type into l_wip_entity_type;
1706 CLOSE c_wip_entity_type;
1707
1708 IF l_wip_entity_type =10 AND r_mtl_data.transaction_type_id =1002 THEN
1709
1710 l_type_id := 75; --new transaction type to support WIP By Product Completion
1711 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name, 'WIP_BYPRODUCT_COMPLETION '),'INV');
1712 l_source_type := 'CSIWIPBC ';
1713 IF (l_bypass = 'N') THEN
1714 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1715 -- WIP By Product Completion
1716 savepoint CSIWIPBC;
1717
1718 XNP_CSIWIPBC_U.publish( --New message to be published
1719 xnp$mtl_transaction_id => p_transaction_id,
1720 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1721 xnp$organization_id => r_mtl_data.organization_id,
1722 x_message_id => l_message_id, -- out parameter
1723 x_error_code => l_error_code, -- out parameter
1724 x_error_message => l_error_message -- out parameter
1725 );
1726
1727 if (l_error_message is not null) then
1728 rollback to CSIWIPBC;
1729 debug('Failed to publish event CSIWIPBC: ' || l_error_message);
1730 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1731 fnd_message.set_token('EVENT','CSIWIPBC');
1732 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1733 l_error_message := fnd_message.get;
1734 raise HOOK_ERROR;
1735 end if;
1736 ELSE
1737 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1738 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPBC',
1739 p_transaction_id,
1740 l_trx_return_status,
1741 l_trx_error_rec);
1742
1743 IF NOT l_trx_return_status = l_fnd_success THEN
1744 raise BYPASS_ERROR;
1745 END IF;
1746
1747 END IF;
1748
1749 ELSE
1750 l_type_id := 73;
1751 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'WIP_ASSEMBLY_COMPLETION'),'INV');
1752 l_source_type := 'CSIWIPAC';
1753 IF (l_bypass = 'N') THEN
1754 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1755 -- WIP Assembly Completion
1756
1757 savepoint CSIWIPAC;
1758
1759 XNP_CSIWIPAC_U.publish(
1760 xnp$mtl_transaction_id => p_transaction_id,
1761 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1762 xnp$organization_id => r_mtl_data.organization_id,
1763 x_message_id => l_message_id, -- out parameter
1764 x_error_code => l_error_code, -- out parameter
1765 x_error_message => l_error_message -- out parameter
1766 );
1767
1768 if (l_error_message is not null) then
1769 rollback to CSIWIPAC;
1770 debug('Failed to publish event CSIWIPAC: ' || l_error_message);
1771 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1772 fnd_message.set_token('EVENT','CSIWIPAC');
1773 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1774 l_error_message := fnd_message.get;
1775 raise HOOK_ERROR;
1776 end if;
1777 ELSE
1778 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1779 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPAC',
1780 p_transaction_id,
1781 l_trx_return_status,
1782 l_trx_error_rec);
1783
1784 IF NOT l_trx_return_status = l_fnd_success THEN
1785 raise BYPASS_ERROR;
1786 END IF;
1787
1788 END IF;
1789
1790 END IF;
1791
1792 ELSIF (r_mtl_data.transaction_action_id = 34 AND
1793 r_mtl_data.transaction_source_type_id = 5)
1794 -- Changed to 5 from Txn Type ID 48
1795 THEN
1796 ---Transactions fall in this category are :
1797 --- Type Action ID Txn Type ID
1798 ----------------------- ------------- ------------
1799 --1. WIP Neg Comp Return 34 48
1800
1801 l_type_id := 71;
1802 l_type_id := csi_inv_trxs_pkg.get_txn_type_id(nvl(l_csi_txn_name,'WIP_ISSUE'),'INV');
1803 l_source_type := 'CSIWIPNR';
1804 IF (l_bypass = 'N') THEN
1805 debug(' publishing '||l_source_type||' for transaction_id : '||p_transaction_id);
1806 -- WIP Negative Component Return
1807
1808 savepoint CSIWIPNR;
1809
1810 XNP_CSIWIPNR_U.publish(
1811 xnp$mtl_transaction_id => p_transaction_id,
1812 xnp$inventory_item_id => r_mtl_data.inventory_item_id,
1813 xnp$organization_id => r_mtl_data.organization_id,
1814 x_message_id => l_message_id, -- out parameter
1815 x_error_code => l_error_code, -- out parameter
1816 x_error_message => l_error_message -- out parameter
1817 );
1818
1819 if (l_error_message is not null) then
1820 rollback to CSIWIPNR;
1821 debug('Failed to publish event CSIWIPNR: ' || l_error_message);
1822 fnd_message.set_name('CSI','CSI_INV_HOOK_EVT_ERR');
1823 fnd_message.set_token('EVENT','CSIWIPNR');
1824 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1825 l_error_message := fnd_message.get;
1826 raise HOOK_ERROR;
1827 end if;
1828 ELSE
1829 debug(' bypassing the SFM queue '||l_source_type||' for transaction_id : '||p_transaction_id);
1830 CSI_INV_TXNSTUB_PKG.execute_trx_dpl('CSIWIPNR',
1831 p_transaction_id,
1832 l_trx_return_status,
1833 l_trx_error_rec);
1834
1835 IF NOT l_trx_return_status = l_fnd_success THEN
1836 raise BYPASS_ERROR;
1837 END IF;
1838
1839 END IF;
1840
1841 ELSE
1842 debug(' publishing non eib transaction : '||p_transaction_id);
1843 -- Not a core EIB trackable transaction.
1844 -- Call client extension to check against additional transaction types.
1845
1846 csi_client_ext_pub.mtl_post_transaction(
1847 p_transaction_id => p_transaction_id,
1848 x_return_status => l_return_status,
1849 x_error_message => l_error_message
1850 );
1851
1852 if (l_return_status <> l_fnd_success) then
1853 debug('Error occurred in MTL Post Transaction Client Extension: ' || l_error_message);
1854 fnd_message.set_name('CSI','CSI_INV_HOOK_CLIENT_EXT');
1855 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
1856 l_error_message := fnd_message.get;
1857 raise HOOK_ERROR;
1858 x_return_status := l_fnd_success;
1859 end if;
1860
1861 END IF;
1862
1863 -- EIB will handle all event errors internally, so return successful status
1864 x_return_status := l_fnd_success;
1865 END IF; -- if trackable item
1866
1867 x_return_status := l_fnd_success;
1868
1869 csi_t_gen_utility_pvt.build_file_name(
1870 p_file_segment1 => 'csiinv',
1871 p_file_segment2 => 'hook');
1872
1873 debug('***** end of ib hook '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')||' *****');
1874
1875 EXCEPTION
1876 WHEN HOOK_ERROR THEN
1877 debug('Raising HOOK_ERROR');
1878 debug('You have encountered an error in the CSI_INV_TXN_HOOK_PKG Procedure for Transaction: '||p_transaction_id);
1879 debug('Error Message: '||l_error_message);
1880
1881 csi_inv_trxs_pkg.build_error_string(l_xml_string,'MTL_TRANSACTION_ID',p_transaction_id);
1882
1883 l_trx_error_rec := csi_inv_trxs_pkg.Init_Txn_Error_Rec;
1884 l_trx_error_rec.transaction_id := NULL;
1885 l_trx_error_rec.message_id := l_message_id;
1886 l_trx_error_rec.error_text := l_error_message;
1887 l_trx_error_rec.source_type := l_source_type;
1888 l_trx_error_rec.source_id := p_transaction_id;
1889 l_trx_error_rec.message_string := l_xml_string;
1890 l_trx_error_rec.transaction_type_id := l_type_id;
1891 l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1892 l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1893
1894 csi_inv_trxs_pkg.log_csi_error(l_trx_error_rec);
1895
1896 x_return_status := l_fnd_success;
1897
1898 WHEN BYPASS_ERROR THEN
1899 debug('Raising BYPASS_ERROR');
1900 debug('You have encountered an error in the CSI_INV_TXN_HOOK_PKG Procedure for Transaction: '||p_transaction_id);
1901 debug('Error Message: '||l_trx_error_rec.error_text);
1902 csi_inv_trxs_pkg.build_error_string(l_xml_string,'MTL_TRANSACTION_ID',p_transaction_id);
1903
1904 l_trx_error_rec.transaction_id := NULL;
1905 l_trx_error_rec.message_id := NULL;
1906 l_trx_error_rec.source_type := l_source_type;
1907 l_trx_error_rec.source_id := l_trx_error_rec.source_id;
1908 l_trx_Error_Rec.processed_flag := CSI_INV_TRXS_PKG.G_TXN_ERROR;
1909 l_trx_error_rec.message_string := l_xml_string;
1910 l_trx_error_rec.transaction_type_id := l_type_id;
1911 l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1912 l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1913
1914 csi_inv_trxs_pkg.log_csi_error(l_trx_error_rec);
1915
1916 x_return_status := l_fnd_success;
1917
1918 WHEN OTHERS THEN
1919 x_return_status := l_fnd_success;
1920 fnd_message.set_name('CSI', 'CSI_INV_POST_TXN_EXCEPTION');
1921 fnd_message.set_token('SQL_ERROR', SQLERRM);
1922 l_error_message := fnd_message.get;
1923 debug('Raising OTHERS');
1924 debug('You have encountered an error in the CSI_INV_TXN_HOOK_PKG Procedure for Transaction: '||p_transaction_id);
1925 debug('Error Message: '||l_error_message);
1926
1927 csi_inv_trxs_pkg.build_error_string(l_xml_string,'MTL_TRANSACTION_ID',p_transaction_id);
1928 l_trx_error_rec := csi_inv_trxs_pkg.Init_Txn_Error_Rec;
1929 l_trx_error_rec.transaction_id := NULL;
1930 l_trx_error_rec.message_id := NULL;
1931 l_trx_error_rec.error_text := l_error_message;
1932 l_trx_error_rec.source_type := l_source_type;
1933 l_trx_error_rec.source_id := p_transaction_id;
1934 l_trx_error_rec.message_string := l_xml_string;
1935 l_trx_error_rec.transaction_type_id := l_type_id;
1936 l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1937 l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1938
1939 csi_inv_trxs_pkg.log_csi_error(l_trx_error_rec);
1940
1941 x_return_status := l_fnd_success;
1942
1943 END postTransaction;
1944
1945 END CSI_INV_TXN_HOOK_PKG;