[Home] [Help]
PACKAGE BODY: APPS.ECEPOI
Source
4 PROCEDURE Process_POI_Inbound (
1 PACKAGE BODY ECEPOI AS
2 /* $Header: OEPOIB.pls 120.1 2011/11/17 05:38:43 slagiset noship $ */
3
5 errbuf OUT NOCOPY varchar2,
6 retcode OUT NOCOPY varchar2,
7 i_file_path IN varchar2,
8 i_file_name IN varchar2,
9 i_debug_mode IN number,
10 i_run_import IN varchar2,
11 i_num_instances IN number default 1,
12 i_transaction_type IN varchar2,
13 i_map_id IN number,
14 i_data_file_characterset IN varchar2
15 )
16 IS
17 i_submit_id number;
18 i_run_id number;
19 i_map_type varchar2(40);
20
21 cEnabled VARCHAR2(1) := 'Y';
22 ece_transaction_disabled EXCEPTION;
23
24 begin
25 ec_debug.enable_debug(i_debug_mode);
26 ec_debug.pl(0,'EC','ECE_START_INBOUND','TRANSACTION_TYPE',i_transaction_type);
27 ec_debug.push('ECEPOI.PROCESS_POI_INBOUND');
28 ec_debug.pl(3,'i_file_path',i_file_path);
29 ec_debug.pl(3,'i_file_name',i_file_name);
33 ec_debug.pl(3,'i_num_instances',i_num_instances);
30 ec_debug.pl(3,'i_run_import',i_run_import);
31 ec_debug.pl(3,'i_map_id',i_map_id);
32 ec_debug.pl(3,'i_debug_mode',i_debug_mode);
34 ec_debug.pl(3,'i_data_file_characterset',i_data_file_characterset);
35 /* Check to see if the transaction is enabled. If not, abort */
36 fnd_profile.get('ECE_' || i_transaction_type || '_ENABLED',cEnabled);
37 IF cEnabled = 'N' THEN
38 RAISE ece_transaction_disabled;
39 END IF;
40
41 ec_debug.pl(0,'EC','ECE_BEGIN_STAGING','TRANSACTION_TYPE',i_transaction_type);
42
43 select map_type into i_map_type
44 from ece_mappings
45 where map_id = i_map_id
46 and enabled ='Y';
47
48 ec_inbound_stage.g_source_charset := i_data_file_characterset;
49
50 IF i_map_type = 'XML' THEN
51 ec_xml_utils.ec_xml_processor_in_generic (
52 i_map_id,
53 i_run_id,
54 i_file_path,
55 i_file_name
56 );
57 ELSE
58 ec_inbound_stage.load_data (
59 i_transaction_type,
60 i_file_name,
61 i_file_path,
62 i_map_id,
63 i_run_id
64 );
65 END IF;
66
67 ec_debug.pl(0,'EC','ECE_END_STAGING','TRANSACTION_TYPE',i_transaction_type);
68
69 /**
70 Initialize the Stack Table
71 **/
72 ec_utils.g_stack.DELETE;
73
74 ec_debug.pl(0,'EC','ECE_START_GENERIC_INBOUND','TRANSACTION_TYPE',i_transaction_type);
75 ece_inbound.process_run_inbound (
76 i_transaction_type => i_transaction_type,
77 i_run_id => i_run_id
78 );
79
80 ec_debug.pl(0,'EC','ECE_FINISH_GENERIC_INBOUND','TRANSACTION_TYPE',i_transaction_type);
81
82 IF i_Run_Import = 'Y' THEN
83 i_Submit_ID := fnd_request.submit_request (
84 application => 'ONT',
85 program => 'OEOIMP',
86 argument1 => NULL, -- --Operating_unit_id bug6918092
87 argument2 => '6', -- Order_Source_Id =6 for EDI
88 argument3 => '', -- Order Ref = all
89 argument4 => 'INSERT',-- Operation code = INSERT
90 argument5 => 'N', -- Validate_Only = 'N'
91 argument6 => '1', -- Debug Level = 1
92 argument7 => i_num_instances -- No. of Instance to run for OIMP
93 );
94
95 ec_debug.pl(0,'EC','ECE_EXECUTE_OPEN_INTERFACE',
96 'TRANSACTION_TYPE',i_transaction_type,
97 'REQUEST_ID',i_Submit_Id);
98 END IF;
99
100 COMMIT;
101 retcode := ec_utils.i_ret_code;
102
103 IF ec_mapping_utils.ec_get_trans_upgrade_status(i_transaction_type) = 'U' THEN
104 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
105 retcode := 1;
106 END IF;
107
108 ec_debug.pl(3,'i_submit_id',i_submit_id);
109 ec_debug.pl(3,'retcode',retcode);
110 ec_debug.pl(3,'errbuf',errbuf);
111 ec_debug.pop('ECEPOI.PROCESS_POI_INBOUND');
112
113 ec_debug.pl(0,'EC','ECE_END_INBOUND','TRANSACTION_TYPE',i_transaction_type);
114 ec_debug.disable_debug;
115
116 EXCEPTION
117 WHEN ece_transaction_disabled THEN
118 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED','TRANSACTION',i_transaction_type);
119 retcode := 1;
120 ec_debug.disable_debug;
121 ROLLBACK WORK;
122
123 WHEN EC_UTILS.PROGRAM_EXIT then
124 errbuf := ec_utils.i_errbuf;
125 retcode := ec_utils.i_ret_code;
126 ece_flatfile_pvt.print_attributes;
127 rollback work;
128 ec_debug.disable_debug;
129
130 WHEN OTHERS THEN
131 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
132 'ECEPOI.PROCESS_POI_INBOUND');
133 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
134 retcode := 2;
135 rollback work;
136 ece_flatfile_pvt.print_attributes;
137 ec_debug.disable_debug;
138
139 END PROCESS_POI_INBOUND;
140
141 Procedure Concat_Strings(
142 String1 IN VARCHAR2,
143 String2 IN VARCHAR2,
144 String3 IN VARCHAR2,
145 OUT_String OUT NOCOPY VARCHAR2
146 )
147 IS
148 Begin
149
150 OUT_String := String1 || String2 ||'.'||String3;
151
152 End Concat_Strings;
153
154 Procedure Get_Ship_To_Org_Id(
155 p_address_id IN NUMBER,
156 p_customer_id IN NUMBER,
157 x_ship_to_org_id OUT NOCOPY NUMBER
158 )
159 IS
160 l_site_use_id Number;
161 lcustomer_relations varchar2(1);
162 l_orgid number;
163 Begin
164 select su.org_id into l_orgid
165 from hz_cust_acct_sites st, hz_cust_site_uses_all su
166 where st.cust_acct_site_id = p_address_id
167 and su.cust_acct_site_id = st.cust_acct_site_id
168 and su.site_use_code= 'SHIP_TO' and rownum =1;
169 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG',l_orgid);
170 If nvl(lcustomer_relations ,'N') = 'N' Then
171 SELECT site_use_id
172 INTO l_site_use_id
173 FROM oe_ship_to_orgs_v
174 WHERE customer_id = p_customer_id
175 AND address_id = p_address_id
176 AND status = 'A';
177 ELSIF lcustomer_relations = 'Y' THEN
178 SELECT site_use_id
179 INTO l_site_use_id
180 From oe_ship_to_orgs_v
184 Select p_customer_id from dual
181 WHERE address_id = p_address_id
182 AND status = 'A' AND
183 customer_id in (
185 union
186 select cust_account_id from
187 hz_cust_acct_relate_all where
188 related_cust_account_id = p_customer_id)
189 and rownum = 1;
190 END IF;
191 x_ship_to_org_id := l_site_use_id;
192 Exception
193 When Others Then
194 x_ship_to_org_id := NULL;
195 End Get_Ship_To_Org_Id;
196
197
198 Procedure Get_Bill_To_Org_Id(
199 p_address_id IN NUMBER,
200 p_customer_id IN NUMBER,
201 x_bill_to_org_id OUT NOCOPY NUMBER
202 )
203 IS
204 l_site_use_id Number;
205 lcustomer_relations varchar2(1);
206 l_orgid number;
207 Begin
208 select su.org_id into l_orgid
209 from hz_cust_acct_sites st, hz_cust_site_uses_all su
210 where st.cust_acct_site_id = p_address_id
211 and su.cust_acct_site_id = st.cust_acct_site_id
212 and su.site_use_code= 'BILL_TO' and rownum =1;
213 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG',l_orgid);
214 If nvl(lcustomer_relations ,'N') = 'N' Then
215 SELECT site_use_id
216 INTO l_site_use_id
217 FROM oe_invoice_to_orgs_v
218 WHERE customer_id = p_customer_id
219 AND address_id = p_address_id
220 AND status = 'A'; --12645675 - Condition to check if the Site Usage is Active;
221 ELSIF lcustomer_relations = 'Y' THEN
222 SELECT site_use_id
223 INTO l_site_use_id
224 From oe_invoice_to_orgs_v
225 WHERE address_id = p_address_id
226 AND status = 'A' --12645675 - Condition to check if the Site Usage is Active
227 AND
228 customer_id in (
229 Select p_customer_id from dual
230 union
231 select cust_account_id from
232 hz_cust_acct_relate_all where
233 related_cust_account_id = p_customer_id)
234 and rownum = 1;
235 END IF;
236 x_bill_to_org_id := l_site_use_id;
237 Exception
238 When Others Then
239 x_bill_to_org_id := NULL;
240 End Get_Bill_To_Org_Id;
241
242 -- Fix for the bug 2627330
243 Procedure Concat_Instructions(
244 String1 IN VARCHAR2,
245 String2 IN VARCHAR2,
246 String3 IN VARCHAR2,
247 String4 IN VARCHAR2,
248 String5 IN VARCHAR2,
249 Concat_String OUT NOCOPY VARCHAR2
250 )
251 IS
252 Begin
253 Concat_String := String1 || String2 || String3 || String4 || String5;
254
255 End Concat_Instructions;
256 -- Fix ends
257
258 FUNCTION EM_Transaction_Type
259 ( p_txn_code IN VARCHAR2
260 ) RETURN VARCHAR2
261 IS
262 l_transaction_type VARCHAR2(80);
263
264 BEGIN
265
266 IF p_txn_code IS NULL
267 THEN
268 RETURN NULL;
269 END IF;
270
271 SELECT MEANING
272 INTO l_transaction_type
273 FROM OE_LOOKUPS
274 WHERE LOOKUP_CODE = p_txn_code
275 AND LOOKUP_TYPE = 'ONT_ELECMSGS_TYPES';
276
277 RETURN l_transaction_type;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 RETURN NULL;
282
283 End EM_Transaction_Type;
284
285 PROCEDURE Raise_Event_Hist (
286 p_order_source_id IN Number,
287 p_orig_sys_document_ref IN Varchar2,
288 p_sold_to_org_id IN Number,
289 p_transaction_type IN Varchar2,
290 p_document_id IN Number,
291 p_change_sequence IN Varchar2,
292 p_order_number IN Number,
293 p_itemtype IN Varchar2,
294 p_itemkey IN Varchar2,
295 p_status IN Varchar2,
296 p_message_text IN Varchar2,
297 p_processing IN Varchar2,
298 p_xmlg_party_id IN Number,
299 p_xmlg_party_site_id IN Number,
300 p_order_type_id IN Number,
301 p_header_id IN Number,
302 p_org_id IN Number,
303 x_return_status OUT NOCOPY Varchar2
304 )
305 IS
306 l_eventkey NUMBER;
307 l_message_text VARCHAR2(2000);
308 l_transaction_type VARCHAR2(30) := p_transaction_type;
309 l_status VARCHAR2(240);
310 l_processing VARCHAR2(30) := p_processing;
311 l_parameter_list WF_PARAMETER_LIST_T := wf_parameter_list_t();
312 l_document_id NUMBER := p_document_id;
313 l_org_id NUMBER := p_org_id;
314 l_order_number NUMBER := p_order_number;
315 l_document_direction VARCHAR2(6);
316 l_release_level VARCHAR2(10);
317 l_integ_profile VARCHAR2(10) := nvl (FND_PROFILE.VALUE ('ONT_EM_INTEG_SOURCES'), 'XML');
318 l_order_processed_flag VARCHAR2(1);
319 BEGIN
320 BEGIN
321 -- this call is dynamic to prevent dependency issues for customers who
322 -- do not have the OE_Code_Control package
323 EXECUTE IMMEDIATE 'Begin :1 := OE_Code_Control.Get_Code_Release_Level; End;'
324 USING out l_release_level;
325 IF l_release_level < '110510' THEN
326 RETURN;
327 END IF;
328 EXCEPTION
329 WHEN OTHERS THEN
333
330 x_return_status := FND_API.G_RET_STS_SUCCESS;
331 RETURN;
332 END;
334 -- new profile to control which order sources can raise this event
335 -- note that by this point, we are already sure that OM is at 11i10
336 -- level, therefore the profile has to exist
337 IF l_integ_profile = 'XML' THEN
338 IF p_order_source_id <> 20 THEN
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340 return;
341 END IF;
342 ELSIF l_integ_profile = 'EDIXML' THEN
343 IF p_order_source_id NOT IN (20,6) THEN
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345 return;
346 END IF;
347 END IF;
348
349 EXECUTE IMMEDIATE 'Begin SELECT OE_XML_MESSAGE_SEQ_S.nextval INTO :1 FROM DUAL; end;'
350 USING out l_eventkey;
351
352 IF p_status IS NULL THEN
353 IF l_transaction_type IN ('855', '865') THEN
354 l_status := 'SUCCESS';
355 l_processing := 'OUTBOUND_SENT';
356 ELSE
357 l_status := 'ACTIVE';
358 l_processing := 'INBOUND_IFACE';
359 END IF;
360 END IF;
361 IF l_message_text IS NULL THEN
362 IF l_processing = 'INBOUND_IFACE' THEN
363 fnd_message.set_name ('ONT', 'OE_OI_IFACE');
364 ELSIF l_processing = 'OUTBOUND_SENT' THEN
365 fnd_message.set_name ('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
366 END IF;
367 fnd_message.set_token ('TRANSACTION', ECEPOI.EM_Transaction_Type (p_txn_code => l_transaction_type));
368 l_message_text := fnd_message.get;
369 END IF;
370 -----------------------------------------------------------
371 -- Non-CLN params
372 -----------------------------------------------------------
373
374 wf_event.AddParameterToList(p_name=> 'ORDER_SOURCE_ID',
375 p_value=> p_order_source_id,
376 p_parameterlist=> l_parameter_list);
377
378 wf_event.AddParameterToList(p_name=> 'SOLD_TO_ORG_ID',
379 p_value=> p_sold_to_org_id,
380 p_parameterlist=> l_parameter_list);
381
382 wf_event.AddParameterToList(p_name=> 'PROCESSING_STAGE',
383 p_value=> l_processing,
384 p_parameterlist=> l_parameter_list);
385
386 IF l_document_id IS NULL THEN
387 l_document_id := to_number(l_eventkey); -- reuse the eventkey if necessary
388 END IF;
389
390 IF l_transaction_type IN ('855','865') THEN
391 wf_event.AddParameterToList(p_name=> 'XMLG_DOCUMENT_ID',
392 p_value=> l_document_id,
393 p_parameterlist=> l_parameter_list);
394 l_order_processed_flag := 'Y';
395 ELSE
396 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_CONTROL_NUMBER',
397 p_value=> l_document_id,
398 p_parameterlist=> l_parameter_list);
399 l_order_processed_flag := 'N';
400 END IF;
401
402 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_TXN_TYPE',
403 p_value=> 'ONT',
404 p_parameterlist=> l_parameter_list);
405
406 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_TXN_SUBTYPE',
407 p_value=> l_transaction_type,
408 p_parameterlist=> l_parameter_list);
409
410 wf_event.AddParameterToList(p_name=> 'PARTNER_DOCUMENT_NO',
411 p_value=> p_orig_sys_document_ref,
412 p_parameterlist=> l_parameter_list);
413
414 wf_event.AddParameterToList(p_name=> 'DOCUMENT_NO',
415 p_value=> l_order_number,
416 p_parameterlist=> l_parameter_list);
417
418 wf_event.AddParameterToList(p_name=> 'DOCUMENT_REVISION_NO',
419 p_value=> p_change_sequence,
420 p_parameterlist=> l_parameter_list);
421
422 IF l_org_id IS NULL THEN
423 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
424 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
425 INTO l_org_id
426 FROM DUAL;
427 END IF;
428
429 wf_event.AddParameterToList(p_name=> 'ORG_ID',
430 p_value=> l_org_id,
431 p_parameterlist=> l_parameter_list);
432
433
434 wf_event.AddParameterToList(p_name=> 'ONT_DOC_STATUS',
435 p_value=> l_status,
436 p_parameterlist=> l_parameter_list);
437
438 wf_event.AddParameterToList(p_name=> 'MESSAGE_TEXT',
439 p_value=> l_message_text,
440 p_parameterlist=> l_parameter_list);
441
442 wf_event.AddParameterToList(p_name=> 'SUBSCRIBER_LIST',
443 p_value=> 'ONT',
444 p_parameterlist=> l_parameter_list);
445
446 wf_event.AddParameterToList(p_name=> 'ORDER_TYPE_ID',
447 p_value=> p_order_type_id,
448 p_parameterlist=> l_parameter_list);
449
450 wf_event.AddParameterToList(p_name=> 'HEADER_ID',
451 p_value=> p_header_id,
452 p_parameterlist=> l_parameter_list);
453
454 wf_event.AddParameterToList(p_name=> 'ORDER_PROCESSED_FLAG',
458 wf_event.raise( p_event_name => 'oracle.apps.ont.oi.xml_int.status',
455 p_value=> l_order_processed_flag,
456 p_parameterlist=> l_parameter_list);
457
459 p_event_key => l_eventkey,
460 p_parameters => l_parameter_list);
461
462 l_parameter_list.delete;
463
464 x_return_status := FND_API.G_RET_STS_SUCCESS ;
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
469
470 END Raise_Event_Hist;
471
472 PROCEDURE Get_Item_Description
473 ( p_org_id IN NUMBER
474 , p_item_identifier_type IN VARCHAR2
475 , p_inventory_item_id IN NUMBER
476 , p_ordered_item_id IN NUMBER
477 , p_sold_to_org_id IN NUMBER
478 , p_ordered_item IN VARCHAR2
479 , x_item_description OUT NOCOPY VARCHAR2
480 ) IS
481 l_organization_id NUMBER;
482 --
483 l_item_description VARCHAR2(240) := null ;
484
485 BEGIN
486 ec_debug.pl(3,'Entering Get_Item_Description()');
487 ec_debug.pl(3,'Item Identifier Type ',p_item_identifier_type);
488 ec_debug.pl(3,'Inventory Item ID ',p_inventory_item_id);
489 ec_debug.pl(3,'Ordered Item ID ',p_ordered_item_id);
490 ec_debug.pl(3,'Sold to Org ID ',p_sold_to_org_id);
491 ec_debug.pl(3,'Org ID ',p_org_id);
492 ec_debug.pl(3,'Ordered Item ',p_ordered_item);
493
494 l_organization_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', p_org_id);
495 ec_debug.pl(3,'Organization ID ',l_organization_ID);
496
497 IF (p_item_identifier_type) = 'INT' THEN
498 SELECT description
499 INTO x_item_description
500 FROM mtl_system_items_vl
501 WHERE inventory_item_id = p_inventory_item_id
502 AND organization_id = l_organization_id;
503
504 ELSIF NVL(p_item_identifier_type,'INT') = 'CUST' THEN
505 ec_debug.pl(3,'Ordered Item ID ',p_ordered_item_id);
506 ec_debug.pl(3,'Sold to Org ID ',p_sold_to_org_id);
507
508 SELECT citems.customer_item_desc
509 INTO l_item_description
510 FROM mtl_customer_items citems
511 WHERE citems.customer_item_id = p_ordered_item_id
512 AND citems.customer_id = p_sold_to_org_id;
513
514 IF l_item_description IS NULL THEN
515 SELECT sitems.description
516 INTO l_item_description
517 FROM mtl_system_items_vl sitems
518 WHERE sitems.inventory_item_id = p_inventory_item_id
519 AND sitems.organization_id = l_organization_id ;
520 END IF ;
521
522 x_item_description := l_item_description ;
523
524 ELSE
525 /*
526 SELECT sitems.description
527 INTO x_item_description
528 FROM mtl_system_items_vl sitems
529 WHERE sitems.inventory_item_id = p_inventory_item_id
530 AND sitems.organization_id = l_organization_id ;
531 */
532 SELECT NVL(items.description, sitems.description)
533 INTO x_item_description
534 FROM mtl_cross_reference_types types
535 , mtl_cross_references items
536 , mtl_system_items_vl sitems
537 WHERE types.cross_reference_type = items.cross_reference_type
538 AND items.inventory_item_id = sitems.inventory_item_id
539 AND sitems.organization_id = l_organization_id
540 AND sitems.inventory_item_id = p_inventory_item_id
541 AND items.cross_reference_type = p_item_identifier_type
542 AND items.cross_reference = p_ordered_item -- check that how ordered_item_id can be used
543 AND ROWNUM = 1;
544
545 END IF;
546 ec_debug.pl(3,'Item Description is : ',X_ITEM_DESCRIPTION ) ;
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 ec_debug.pl(3,'When no data found then get desc from system items ',SQLERRM);
550 begin --bug7554911
551 SELECT sitems.description
552 INTO x_item_description
553 FROM mtl_system_items_vl sitems
554 WHERE sitems.inventory_item_id = p_inventory_item_id
555 AND sitems.organization_id = l_organization_id ;
556 EXCEPTION
557 WHEN OTHERS THEN
558 oe_debug_pub.add('Unable to get Item Description '||SQLERRM,1);
559 NULL;
560 END; --bug7554911
561 WHEN OTHERS THEN
562 oe_debug_pub.add('Unable to get Item Description '||SQLERRM,1);
563 NULL;
564 END Get_Item_Description;
565
566 END ECEPOI;