[Home] [Help]
PACKAGE BODY: APPS.CSI_ONT_TXN_PUB
Source
1 package body csi_ont_txn_pub as
2 /* $Header: csipontb.pls 120.7.12010000.4 2009/12/21 23:46:21 devijay ship $ */
3
4 PROCEDURE debug(
5 p_message in varchar2)
6 IS
7 BEGIN
8 IF csi_t_gen_utility_pvt.g_debug_level > 0 THEN
9 csi_t_gen_utility_pvt.add(p_message);
10 END IF;
11 EXCEPTION
12 WHEN others THEN
13 null;
14 END debug;
15
16 /* -- Function added for bug 8651044
17 FUNCTION check_if_debrief_txn_src(
18 p_order_line_id IN NUMBER
19 ) RETURN BOOLEAN
20 IS
21 l_return_status BOOLEAN := FALSE;
22 l_dummy_order_line_id NUMBER;
23 BEGIN
24 SELECT ORDER_LINE_ID INTO l_dummy_order_line_id
25 FROM CS_ESTIMATE_DETAILS,OE_ORDER_LINES_ALL
26 WHERE CS_ESTIMATE_DETAILS.incident_ID = OE_ORDER_LINES_ALL.source_document_ID
27 AND CS_ESTIMATE_DETAILS.SOURCE_CODE = 'SD'
28 AND OE_ORDER_LINES_ALL.source_document_type_id = 7
29 AND oe_order_lines_all.line_id = p_order_line_id;
30
31 l_return_status := TRUE;
32 -- Return TRUE as the order line is debrief order
33 RETURN l_return_status;
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 l_return_status := FALSE;
37 return l_return_status;
38 WHEN TOO_MANY_ROWS THEN
39 l_return_status := TRUE;
40 return l_return_status;
41 WHEN OTHERS THEN
42 l_return_status := FALSE;
43 debug('Error in check_if_debrief_txn_src - ' || SQLERRM);
44 return l_return_status;
45 END check_if_debrief_txn_src;
46 */
47
48
49 PROCEDURE PostTransaction(
50 p_order_line_id IN NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_message_id OUT NOCOPY NUMBER,
53 x_error_code OUT NOCOPY NUMBER,
54 x_error_message OUT NOCOPY VARCHAR2)
55 IS
56
57 l_message_id number;
58 l_transactable_flag mtl_system_items_b.mtl_transactions_enabled_flag%TYPE; --bug6140021
59 l_org_id number;
60 l_order_id oe_order_lines_all.header_id%TYPE; --Added for MACD Enhancement
61 l_vld_organization_id number;
62 l_inventory_item_id number;
63 l_line_category_code oe_order_lines_all.line_category_code%TYPE;
64 l_shippable_flag mtl_system_items_b.shippable_item_flag%TYPE;
65 l_ordered_item varchar2(80);
66 l_item_type_code varchar2(30);
67 l_fulfilled_qty number;
68 l_shipped_qty number;
69 l_ato_line_id number;
70
71 l_receipt_node_found boolean;
72 l_processing_reqd varchar2(1) := 'Y';
73 l_bypass_flag varchar2(1) := 'N';
74
75 l_use_parallelmode VARCHAR2(1) := 'N'; --Added for MACD Enhancement
76 --l_interface_nship_flag VARCHAR2(1) := 'Y';
77
78 l_txn_type_id number;
79 l_txn_type varchar2(30);
80
81 publish_error exception;
82 bypass_error exception;
83 l_skip_reason varchar2(80);
84
85 l_return_status varchar2(1);
86 l_error_code number;
87 l_error_message varchar2(4000);
88 l_error_rec csi_datastructures_pub.transaction_error_rec;
89
90 CURSOR c_xnp_event_mgr_info IS
91 SELECT substr( service_parameters, instr(service_parameters, 'XDP_DQ_INIT_NUM_THREADS', 1)) service_parameters,
92 nvl(max_processes,-1) max_processes
93 FROM fnd_concurrent_queues
94 WHERE concurrent_queue_name = 'XDP_Q_EVENT_SVC'
95 AND application_id = 535;
96
97 l_service_parameters varchar2(2000);
98 l_start number;
99 l_end number;
100 l_num_threads number;
101 l_max_processes number;
102 l_om_session_key csi_utility_grp.config_session_key; --Added for MACD Enhancement
103 l_config_header_id oe_order_lines_all.config_header_id%type;--Added for MACD Enhancement
104 l_config_rev_nbr oe_order_lines_all.config_rev_nbr%type; --Added for MACD Enhancement
105 l_configuration_id oe_order_lines_all.configuration_id%type;--Added for MACD Enhancement
106 l_macd_processing boolean := FALSE; --Added for MACD Enhancement
107
108 BEGIN
109
110 x_return_status := fnd_api.g_ret_sts_success;
111
112 l_use_parallelmode := NVL(fnd_profile.value('CSI_TXN_PARALLEL_MODE'), 'N'); --Added for MACD Enhancement
113 -- l_use_parallelmode := 'Y';
114
115 csi_t_gen_utility_pvt.build_file_name(
116 p_file_segment1 => 'csiinv',
117 p_file_segment2 => 'hook');
118
119 csi_t_gen_utility_pvt.add('*****START ib node from workflow process :'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')); --Added for MACD Enhancement
120
121 debug('START ib node from workflow process :'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
122 debug(' order_line_id : '||p_order_line_id);
123
124 /* -- Bug 8651044
125 IF check_if_debrief_txn_src(p_order_line_id) THEN
126 debug('Skip posting transaction to avoid duplicate processing by workflow');
127 x_return_status := fnd_api.g_ret_sts_success;
128 -- Skiping the processing
129 l_processing_reqd := 'N';
130 l_skip_reason := 'Fulfilment already processed through debrief';
131 --RETURN;
132 END IF;
133 */
134 -- Check Item trackeable
135 BEGIN
136
137 SELECT org_id,
138 header_id, --Added for MACD Enhancement
139 inventory_item_id,
140 line_category_code,
141 ordered_item,
142 item_type_code,
143 fulfilled_quantity,
144 shipped_quantity,
145 shippable_flag,
146 config_header_id, --Added for MACD Enhancement
147 config_rev_nbr, --Added for MACD Enhancement
148 configuration_id, --Added for MACD Enhancement
149 ato_line_id
150 INTO l_org_id,
151 l_order_id, --Added for MACD Enhancement
152 l_inventory_item_id,
153 l_line_category_code,
154 l_ordered_item,
155 l_item_type_code,
156 l_fulfilled_qty,
157 l_shipped_qty,
158 l_shippable_flag,
159 l_config_header_id,--Added for MACD Enhancement
160 l_config_rev_nbr, --Added for MACD Enhancement
161 l_configuration_id,--Added for MACD Enhancement
162 l_ato_line_id
163 FROM oe_order_lines_all
164 WHERE line_id = p_order_line_id;
165
166 debug(' inventory_item_id : '||l_inventory_item_id);
167
168 l_vld_organization_id := oe_sys_parameters.value(
169 param_name => 'MASTER_ORGANIZATION_ID',
170 p_org_id => l_org_id);
171
172 debug(' om_vld_org_id : '||l_vld_organization_id);
173
174 EXCEPTION
175 WHEN no_data_found THEN
176
177 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
178 fnd_message.set_token('OE_LINE_ID', p_order_line_id);
179 fnd_msg_pub.add;
180 raise fnd_api.g_exc_error;
181 END;
182
183 IF NOT
184 csi_item_instance_vld_pvt.is_trackable(
185 p_inv_item_id => l_inventory_item_id,
186 p_org_id => l_vld_organization_id)
187 THEN
188 debug(' ib_trackable : FALSE');
189 l_processing_reqd := 'N';
190 l_skip_reason := 'non ib trackable';
191 END IF;
192 /*
193 -- Bug 8931748
194 -- Introduced a new profile value CSI: Interface Non-Shippable Items to IB
195 -- (CSI_INTERFACE_NON_SHIP_ITEMS) to allow users to specify whether to
196 -- interface non-shippable items to IB. As a part of this
197 -- requirement (as per document) to introduce IB node for
198 -- non shippable items in WF should be removed
199
200 debug(' shippable_item_flag : '||l_shippable_flag);
201 IF NVL(l_shippable_flag,'N') = 'N' THEN
202 -- Get profile value for CSI_INTERFACE_NON_SHIP_ITEMS
203 -- If CSI_INTERFACE_NON_SHIP_ITEMS = Y process continues
204 -- If CSI_INTERFACE_NON_SHIP_ITEMS = N skip processig
205 -- Default value for the flag is to interface
206 -- (ie) CSI_INTERFACE_NON_SHIP_ITEMS = Y
207
208 l_interface_nship_flag := NVL(fnd_profile.value('CSI_INTERFACE_NON_SHIP_ITEMS'), 'Y');
209 debug(' interface non-shippable : '||l_interface_nship_flag);
210 IF l_interface_nship_flag = 'N' THEN
211 -- Skipping
212 x_return_status := fnd_api.g_ret_sts_success;
213 l_processing_reqd := 'N';
214 l_skip_reason := 'CSI: Interface Non-Shippable Items set to N. Skipping Processing';
215 debug(l_skip_reason);
216 END IF; -- l_interface_nship_flag = N
217 END IF; -- NVL(l_shippable_flag,'N') = 'N'
218 -- End Bug 8931748
219
220 */
221 IF l_processing_reqd = 'Y' THEN
222
223 debug(' ib_trackable : TRUE');
224 debug(' line_category_code : '||l_line_category_code);
225 debug(' item_type_code : '||l_item_type_code);
226 debug(' ordered_item : '||l_ordered_item);
227 debug(' fulfilled_quantity : '||l_fulfilled_qty);
228 debug(' shipped_quantity : '||l_shipped_qty);
229 debug(' shippable_item_flag : '||l_shippable_flag);
230
231 -- Order Line Shippable item flag overrides the MSI - R12
232 /*
233 OUTBOUND:
234 OEL Shippable flag is N
235 - Regular non-shippable items :: shipped qty is NOT populated
236 - Shippable as well as non-shippable items in a configuration :: shipped qty is populated
237 (these shippable items could be option items in an ATO but NOT physically shipped )
238 OEL Shippable flag is Y
239 - Shippable items physically shipped :: shipped qty is populated
240 INBOUND:
241 OEL Shippable flag is N
242 - Regular non-shippable but returnable item cancellations :: shipped qty is NOT populated
243 OEL Shippable flag is Y
244 - Shippable items physically returned :: shipped qty is populated
245 - Regular shippable item but not physically returned :: shipped qty is NOT populated
246 */
247
248 --Code start for bug 6140021--
249 BEGIN
250 SELECT nvl(mtl_transactions_enabled_flag,'N')
251 INTO l_transactable_flag
252 FROM mtl_system_items
253 WHERE inventory_item_id = l_inventory_item_id
254 AND organization_id = l_vld_organization_id;
255 EXCEPTION
256 WHEN no_data_found THEN
257 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_INVALID');
258 fnd_message.set_token('ITEM_ID', l_inventory_item_id);
259 fnd_message.set_token('ORGANIZATION_ID',l_vld_organization_id);
260 fnd_msg_pub.add;
261 raise fnd_api.g_exc_error;
262 END;
263 --Code end for bug 6140021--
264
265 IF l_shippable_flag is null THEN
266 -- get the Shippable item flag if order line does not have it already
267
268 -- get the Shippable item flag
269 BEGIN
270 SELECT nvl(shippable_item_flag,'N')
271 INTO l_shippable_flag
272 FROM mtl_system_items
273 WHERE inventory_item_id = l_inventory_item_id
274 AND organization_id = l_vld_organization_id;
275 EXCEPTION
276 WHEN no_data_found THEN
277 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_INVALID');
278 fnd_message.set_token('ITEM_ID', l_inventory_item_id);
279 fnd_message.set_token('ORGANIZATION_ID',l_vld_organization_id);
280 fnd_msg_pub.add;
281 raise fnd_api.g_exc_error;
282 END;
283 END IF;
284
285 IF l_shippable_flag = 'N' THEN
286 IF l_line_category_code = 'ORDER' THEN
287 l_txn_type := 'CSISOFUL';
288 l_txn_type_id := 51;
289 ELSIF l_line_category_code = 'RETURN' THEN
290 l_txn_type := 'CSIRMAFL';
291 l_txn_type_id := 54;
292 END IF;
293 ELSIF l_shippable_flag = 'Y' THEN
294
295 l_processing_reqd := 'N';
296 l_skip_reason := 'shippable item';
297 IF nvl(l_shipped_qty, -999) = -999 THEN
298 IF l_line_category_code = 'RETURN' THEN
299 /* not required to check the RCV function anymore from R12 since the shippable quantity is going to handle this
300 l_receipt_node_found := wf_engine.activity_exist_in_process(
301 p_item_type => 'OEOL',
302 p_item_key => to_char(p_order_line_id),
303 p_activity_item_type => 'OEOL',
304 p_activity_name => 'RMA_RECEIVING_SUB');
305 IF NOT(l_receipt_node_found) THEN
306 l_processing_reqd := 'Y';
307 l_txn_type := 'CSIRMAFL';
308 l_txn_type_id := 54;
309 END IF;
310 */
311 l_processing_reqd := 'Y';
312 l_txn_type := 'CSIRMAFL';
313 l_txn_type_id := 54;
314 ELSE
315 -- Bill Only flows for the Demo / Lease Loan conversions... Bug 4996316
316 debug('Customer Product Conversion...');
317 l_processing_reqd := 'Y';
318 l_txn_type := 'CSISOFUL';
319 l_txn_type_id := 51;
320 END IF;
321 --start for 6140021
322 ELSE
323 IF l_transactable_flag = 'N' THEN
324 debug('Customer Product Conversion...when not transactable');
325 l_processing_reqd := 'Y';
326 l_txn_type := 'CSISOFUL';
327 l_txn_type_id := 51;
328 END IF;
329 --end for 6140021
330
331 END IF;
332 END IF;
333 END IF;
334
335 IF l_processing_reqd = 'Y' THEN
336
337 -- Bug 4939357 - Added code for Cursor Optimation and also for SFM Thread/Max Processes
338
339 -- SELECT nvl(sfm_queue_bypass_flag,'N')
340 -- INTO l_bypass_flag
341 -- FROM csi_install_parameters;
342
343 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
344 csi_gen_utility_pvt.populate_install_param_rec;
345 END IF;
346
347 l_bypass_flag := NVL(csi_datastructures_pub.g_install_param_rec.sfm_queue_bypass_flag,'N');
348
349 debug(' bypass_mode :'||l_bypass_flag);
350
351 l_error_rec.source_type := l_txn_type;
352 l_error_rec.source_id := p_order_line_id;
353 l_error_rec.transaction_type_id := l_txn_type_id;
354
355 IF l_bypass_flag = 'N' THEN
356
357 debug('publishing the message for event - '||l_txn_type);
358 debug('Checking SFM Manager Parameters');
359
360 OPEN c_xnp_event_mgr_info;
361 FETCH c_xnp_event_mgr_info INTO l_service_parameters, l_max_processes;
362 CLOSE c_xnp_event_mgr_info;
363
364 l_start := instr(l_service_parameters, '=', 1) + 1;
365 l_end := instr(l_service_parameters, ':', 1);
366
367 IF l_end = 0 THEN -- No more delimiters
368 l_num_threads := substr(l_service_parameters,l_start);
369 ELSE
370 l_num_threads := substr(l_service_parameters,l_start,(l_end - l_start));
371 END IF;
372
373 debug('SFM Manager Threads: '||l_num_threads);
374 debug('SFM Manager Max Processes: '||l_max_processes);
375
376 --Commented the below code for MACD Enhancement
377 /*
378 IF (l_num_threads <> 1) OR
379 (l_max_processes <> 1) THEN
380
381 debug('SFM Manager Processes or Threads are not 1 so raising exception and logging error..');
382 fnd_message.set_name('CSI','CSI_SFM_THREAD_MP_ERROR');
383 l_error_rec.error_text := fnd_message.get;
384 RAISE publish_error;
385 END IF;
386 */
387
388 IF l_txn_type = 'CSISOFUL' THEN
389
390 if l_use_parallelmode = 'N' then --Added for MACD Enhancement
391
392 XNP_CSISOFUL_U.publish(
393 xnp$order_line_id => p_order_line_id,
394 x_message_id => l_message_id,
395 x_error_code => l_error_code,
396 x_error_message => l_error_message);
397
398 IF l_error_message is not null THEN
399 RAISE publish_error;
400 END IF;
401
402 else
403 --Code Added for MACD Enhancement starts here
404 -- check for MACD orders
405 l_om_session_key.session_hdr_id := l_config_header_id;
406 l_om_session_key.session_rev_num := l_config_rev_nbr;
407 l_om_session_key.session_item_id := l_configuration_id;
408 l_macd_processing := csi_interface_pkg.check_macd_processing(
409 p_config_session_key => l_om_session_key,
410 x_return_status => l_return_status);
411
412 if (l_macd_processing) then
413
414 insert into CSI_BATCH_TXN_LINES
415 (
416 BATCH_ID,
417 PROCESSED_FLAG,
418 ORDER_HEADER_ID,
419 ORDER_LINE_ID,
420 --ORGANIZATION_ID,
421 --INVENTORY_ITEM_ID,
422 TRANSACTION_TYPE,
423 TRANSACTION_TYPE_ID,
424 TRANSACTION_ID
425 --INSTANCE_ID,
426 --SERIAL_NUMBER
427 ,CREATION_DATE
428 ,CREATED_BY
429 ,LAST_UPDATE_DATE
430 ,LAST_UPDATED_BY
431 )
432 VALUES
433 (
434 -1,
435 0,
436 L_ORDER_ID,
437 P_ORDER_LINE_ID,
438 --NULL,
439 --l_inventory_item_id,
440 l_txn_type,
441 l_txn_type_id,
442 NULL
443 --NULL,
444 --NULL
445 ,sysdate
446 ,fnd_global.user_id
447 ,sysdate
448 ,fnd_global.user_id
449 );
450 else
451 XNP_CSISOFUL_U.publish(
452 xnp$order_line_id => p_order_line_id,
453 x_message_id => l_message_id,
454 x_error_code => l_error_code,
455 x_error_message => l_error_message);
456
457 IF l_error_message is not null THEN
458 RAISE publish_error;
459 END IF;
460 end if;
461 end if;
462
463 --Code Added for MACD Enhancement ends here
464
465 ELSIF l_txn_type = 'CSIRMAFL' THEN
466
467 XNP_CSIRMAFL_U.publish(
468 xnp$rma_line_id => p_order_line_id,
469 x_message_id => l_message_id,
470 x_error_code => l_error_code,
471 x_error_message => l_error_message);
472
473 IF l_error_message is not null THEN
474 l_error_rec.error_text := l_error_message;
475 RAISE publish_error;
476 END IF;
477
478 END IF;
479
480 ELSE
481
482 debug('bypassing the sfm queue for - '||l_txn_type);
483
484 csi_inv_txnstub_pkg.execute_trx_dpl(
485 p_transaction_type => l_txn_type,
486 p_transaction_id => p_order_line_id,
487 x_trx_return_status => l_return_status,
488 x_trx_error_rec => l_error_rec);
489
490 IF l_return_status <> fnd_api.g_ret_sts_success THEN
491 RAISE bypass_error;
492 END IF;
493
494 END IF; -- bypass flag
495
496 ELSE
497 debug('skip fulfillment process - '||l_skip_reason);
498 END IF; -- processing reqd
499
500 debug('END ib node from workflow process :'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
501
502 EXCEPTION
503 WHEN bypass_error THEN
504 csi_t_gen_utility_pvt.build_file_name(
505 p_file_segment1 => 'csiinv',
506 p_file_segment2 => 'hook');
507 l_error_rec.inv_material_transaction_id := null;
508 debug(' bypass_error : '||l_error_rec.error_text);
509 csi_inv_trxs_pkg.log_csi_error(l_error_rec);
510 WHEN publish_error THEN
511 csi_t_gen_utility_pvt.build_file_name(
512 p_file_segment1 => 'csiinv',
513 p_file_segment2 => 'hook');
514 l_error_rec.inv_material_transaction_id := null;
515 debug(' publish_error :'||l_error_rec.error_text);
516 csi_inv_trxs_pkg.log_csi_error(l_error_rec);
517 WHEN others THEN
518 csi_t_gen_utility_pvt.build_file_name(
519 p_file_segment1 => 'csiinv',
520 p_file_segment2 => 'hook');
521 l_error_rec.inv_material_transaction_id := null;
522 l_error_rec.error_text := substr(sqlerrm, 1, 540);
523 debug(' other_error :'||l_error_rec.error_text);
524 csi_inv_trxs_pkg.log_csi_error(l_error_rec);
525 END PostTransaction;
526
527 END csi_ont_txn_pub;