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