[Home] [Help]
PACKAGE BODY: APPS.ECEPOI
Source
1 PACKAGE BODY ECEPOI AS
2 /* $Header: ECPOIB.pls 120.5.12010000.2 2008/11/24 17:26:01 akemiset ship $ */
3
4 PROCEDURE Process_POI_Inbound (
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);
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);
33 ec_debug.pl(3,'i_num_instances',i_num_instances);
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
181 WHERE address_id = p_address_id
182 AND status = 'A' AND
183 customer_id in (
184 Select p_customer_id from dual
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 ELSIF lcustomer_relations = 'Y' THEN
221 SELECT site_use_id
222 INTO l_site_use_id
223 From oe_invoice_to_orgs_v
224 WHERE address_id = p_address_id
225 AND
226 customer_id in (
227 Select p_customer_id from dual
228 union
229 select cust_account_id from
230 hz_cust_acct_relate_all where
231 related_cust_account_id = p_customer_id)
232 and rownum = 1;
233 END IF;
234 x_bill_to_org_id := l_site_use_id;
235 Exception
236 When Others Then
237 x_bill_to_org_id := NULL;
238 End Get_Bill_To_Org_Id;
239
240 -- Fix for the bug 2627330
241 Procedure Concat_Instructions(
242 String1 IN VARCHAR2,
243 String2 IN VARCHAR2,
244 String3 IN VARCHAR2,
245 String4 IN VARCHAR2,
246 String5 IN VARCHAR2,
247 Concat_String OUT NOCOPY VARCHAR2
248 )
249 IS
250 Begin
251 Concat_String := String1 || String2 || String3 || String4 || String5;
252
253 End Concat_Instructions;
254 -- Fix ends
255
256 FUNCTION EM_Transaction_Type
257 ( p_txn_code IN VARCHAR2
258 ) RETURN VARCHAR2
259 IS
260 l_transaction_type VARCHAR2(80);
261
262 BEGIN
263
264 IF p_txn_code IS NULL
265 THEN
266 RETURN NULL;
267 END IF;
268
269 SELECT MEANING
270 INTO l_transaction_type
271 FROM OE_LOOKUPS
272 WHERE LOOKUP_CODE = p_txn_code
273 AND LOOKUP_TYPE = 'ONT_ELECMSGS_TYPES';
274
275 RETURN l_transaction_type;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 RETURN NULL;
280
281 End EM_Transaction_Type;
282
283 PROCEDURE Raise_Event_Hist (
284 p_order_source_id IN Number,
285 p_orig_sys_document_ref IN Varchar2,
286 p_sold_to_org_id IN Number,
287 p_transaction_type IN Varchar2,
288 p_document_id IN Number,
289 p_change_sequence IN Varchar2,
290 p_order_number IN Number,
291 p_itemtype IN Varchar2,
292 p_itemkey IN Varchar2,
293 p_status IN Varchar2,
294 p_message_text IN Varchar2,
295 p_processing IN Varchar2,
296 p_xmlg_party_id IN Number,
297 p_xmlg_party_site_id IN Number,
298 p_order_type_id IN Number,
299 p_header_id IN Number,
300 p_org_id IN Number,
301 x_return_status OUT NOCOPY Varchar2
302 )
303 IS
304 l_eventkey NUMBER;
305 l_message_text VARCHAR2(2000);
306 l_transaction_type VARCHAR2(30) := p_transaction_type;
307 l_status VARCHAR2(240);
308 l_processing VARCHAR2(30) := p_processing;
309 l_parameter_list WF_PARAMETER_LIST_T := wf_parameter_list_t();
310 l_document_id NUMBER := p_document_id;
311 l_org_id NUMBER := p_org_id;
312 l_order_number NUMBER := p_order_number;
313 l_document_direction VARCHAR2(6);
314 l_release_level VARCHAR2(10);
315 l_integ_profile VARCHAR2(10) := nvl (FND_PROFILE.VALUE ('ONT_EM_INTEG_SOURCES'), 'XML');
316 l_order_processed_flag VARCHAR2(1);
317 BEGIN
318 BEGIN
319 -- this call is dynamic to prevent dependency issues for customers who
320 -- do not have the OE_Code_Control package
321 EXECUTE IMMEDIATE 'Begin :1 := OE_Code_Control.Get_Code_Release_Level; End;'
322 USING out l_release_level;
323 IF l_release_level < '110510' THEN
324 RETURN;
325 END IF;
326 EXCEPTION
327 WHEN OTHERS THEN
328 x_return_status := FND_API.G_RET_STS_SUCCESS;
329 RETURN;
330 END;
331
332 -- new profile to control which order sources can raise this event
333 -- note that by this point, we are already sure that OM is at 11i10
334 -- level, therefore the profile has to exist
335 IF l_integ_profile = 'XML' THEN
336 IF p_order_source_id <> 20 THEN
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338 return;
339 END IF;
340 ELSIF l_integ_profile = 'EDIXML' THEN
341 IF p_order_source_id NOT IN (20,6) THEN
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343 return;
344 END IF;
345 END IF;
346
347 EXECUTE IMMEDIATE 'Begin SELECT OE_XML_MESSAGE_SEQ_S.nextval INTO :1 FROM DUAL; end;'
348 USING out l_eventkey;
349
350 IF p_status IS NULL THEN
351 IF l_transaction_type IN ('855', '865') THEN
352 l_status := 'SUCCESS';
353 l_processing := 'OUTBOUND_SENT';
354 ELSE
355 l_status := 'ACTIVE';
356 l_processing := 'INBOUND_IFACE';
357 END IF;
358 END IF;
359 IF l_message_text IS NULL THEN
360 IF l_processing = 'INBOUND_IFACE' THEN
361 fnd_message.set_name ('ONT', 'OE_OI_IFACE');
362 ELSIF l_processing = 'OUTBOUND_SENT' THEN
363 fnd_message.set_name ('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
364 END IF;
365 fnd_message.set_token ('TRANSACTION', ECEPOI.EM_Transaction_Type (p_txn_code => l_transaction_type));
366 l_message_text := fnd_message.get;
367 END IF;
368 -----------------------------------------------------------
369 -- Non-CLN params
370 -----------------------------------------------------------
371
372 wf_event.AddParameterToList(p_name=> 'ORDER_SOURCE_ID',
373 p_value=> p_order_source_id,
374 p_parameterlist=> l_parameter_list);
375
376 wf_event.AddParameterToList(p_name=> 'SOLD_TO_ORG_ID',
377 p_value=> p_sold_to_org_id,
378 p_parameterlist=> l_parameter_list);
379
380 wf_event.AddParameterToList(p_name=> 'PROCESSING_STAGE',
381 p_value=> l_processing,
382 p_parameterlist=> l_parameter_list);
383
384 IF l_document_id IS NULL THEN
385 l_document_id := to_number(l_eventkey); -- reuse the eventkey if necessary
386 END IF;
387
388 IF l_transaction_type IN ('855','865') THEN
389 wf_event.AddParameterToList(p_name=> 'XMLG_DOCUMENT_ID',
390 p_value=> l_document_id,
391 p_parameterlist=> l_parameter_list);
392 l_order_processed_flag := 'Y';
393 ELSE
394 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_CONTROL_NUMBER',
395 p_value=> l_document_id,
396 p_parameterlist=> l_parameter_list);
397 l_order_processed_flag := 'N';
398 END IF;
399
400 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_TXN_TYPE',
404 wf_event.AddParameterToList(p_name=> 'XMLG_INTERNAL_TXN_SUBTYPE',
401 p_value=> 'ONT',
402 p_parameterlist=> l_parameter_list);
403
405 p_value=> l_transaction_type,
406 p_parameterlist=> l_parameter_list);
407
408 wf_event.AddParameterToList(p_name=> 'PARTNER_DOCUMENT_NO',
409 p_value=> p_orig_sys_document_ref,
410 p_parameterlist=> l_parameter_list);
411
412 wf_event.AddParameterToList(p_name=> 'DOCUMENT_NO',
413 p_value=> l_order_number,
414 p_parameterlist=> l_parameter_list);
415
416 wf_event.AddParameterToList(p_name=> 'DOCUMENT_REVISION_NO',
417 p_value=> p_change_sequence,
418 p_parameterlist=> l_parameter_list);
419
420 IF l_org_id IS NULL THEN
421 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
422 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
423 INTO l_org_id
424 FROM DUAL;
425 END IF;
426
427 wf_event.AddParameterToList(p_name=> 'ORG_ID',
428 p_value=> l_org_id,
429 p_parameterlist=> l_parameter_list);
430
431
432 wf_event.AddParameterToList(p_name=> 'ONT_DOC_STATUS',
433 p_value=> l_status,
434 p_parameterlist=> l_parameter_list);
435
436 wf_event.AddParameterToList(p_name=> 'MESSAGE_TEXT',
437 p_value=> l_message_text,
438 p_parameterlist=> l_parameter_list);
439
440 wf_event.AddParameterToList(p_name=> 'SUBSCRIBER_LIST',
441 p_value=> 'ONT',
442 p_parameterlist=> l_parameter_list);
443
444 wf_event.AddParameterToList(p_name=> 'ORDER_TYPE_ID',
445 p_value=> p_order_type_id,
446 p_parameterlist=> l_parameter_list);
447
448 wf_event.AddParameterToList(p_name=> 'HEADER_ID',
449 p_value=> p_header_id,
450 p_parameterlist=> l_parameter_list);
451
452 wf_event.AddParameterToList(p_name=> 'ORDER_PROCESSED_FLAG',
453 p_value=> l_order_processed_flag,
454 p_parameterlist=> l_parameter_list);
455
456 wf_event.raise( p_event_name => 'oracle.apps.ont.oi.xml_int.status',
457 p_event_key => l_eventkey,
458 p_parameters => l_parameter_list);
459
460 l_parameter_list.delete;
461
462 x_return_status := FND_API.G_RET_STS_SUCCESS ;
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
467
468 END Raise_Event_Hist;
469
470 PROCEDURE Get_Item_Description
471 ( p_org_id IN NUMBER
472 , p_item_identifier_type IN VARCHAR2
473 , p_inventory_item_id IN NUMBER
474 , p_ordered_item_id IN NUMBER
475 , p_sold_to_org_id IN NUMBER
476 , p_ordered_item IN VARCHAR2
477 , x_item_description OUT NOCOPY VARCHAR2
478 ) IS
479 l_organization_id NUMBER;
480 --
481 l_item_description VARCHAR2(240) := null ;
482
483 BEGIN
484 ec_debug.pl(3,'Entering Get_Item_Description()');
485 ec_debug.pl(3,'Item Identifier Type ',p_item_identifier_type);
486 ec_debug.pl(3,'Inventory Item ID ',p_inventory_item_id);
487 ec_debug.pl(3,'Ordered Item ID ',p_ordered_item_id);
488 ec_debug.pl(3,'Sold to Org ID ',p_sold_to_org_id);
489 ec_debug.pl(3,'Org ID ',p_org_id);
490 ec_debug.pl(3,'Ordered Item ',p_ordered_item);
491
492 l_organization_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', p_org_id);
493 ec_debug.pl(3,'Organization ID ',l_organization_ID);
494
495 IF (p_item_identifier_type) = 'INT' THEN
496 SELECT description
497 INTO x_item_description
498 FROM mtl_system_items_vl
499 WHERE inventory_item_id = p_inventory_item_id
500 AND organization_id = l_organization_id;
501
502 ELSIF NVL(p_item_identifier_type,'INT') = 'CUST' THEN
503 ec_debug.pl(3,'Ordered Item ID ',p_ordered_item_id);
504 ec_debug.pl(3,'Sold to Org ID ',p_sold_to_org_id);
505
506 SELECT citems.customer_item_desc
507 INTO l_item_description
508 FROM mtl_customer_items citems
509 WHERE citems.customer_item_id = p_ordered_item_id
510 AND citems.customer_id = p_sold_to_org_id;
511
512 IF l_item_description IS NULL THEN
513 SELECT sitems.description
514 INTO l_item_description
515 FROM mtl_system_items_vl sitems
516 WHERE sitems.inventory_item_id = p_inventory_item_id
517 AND sitems.organization_id = l_organization_id ;
518 END IF ;
519
520 x_item_description := l_item_description ;
521
522 ELSE
523 /*
524 SELECT sitems.description
525 INTO x_item_description
526 FROM mtl_system_items_vl sitems
527 WHERE sitems.inventory_item_id = p_inventory_item_id
528 AND sitems.organization_id = l_organization_id ;
529 */
530 SELECT NVL(items.description, sitems.description)
531 INTO x_item_description
532 FROM mtl_cross_reference_types types
533 , mtl_cross_references items
534 , mtl_system_items_vl sitems
535 WHERE types.cross_reference_type = items.cross_reference_type
536 AND items.inventory_item_id = sitems.inventory_item_id
537 AND sitems.organization_id = l_organization_id
538 AND sitems.inventory_item_id = p_inventory_item_id
539 AND items.cross_reference_type = p_item_identifier_type
540 AND items.cross_reference = p_ordered_item -- check that how ordered_item_id can be used
541 AND ROWNUM = 1;
542
543 END IF;
544 ec_debug.pl(3,'Item Description is : ',X_ITEM_DESCRIPTION ) ;
545 EXCEPTION
546 WHEN NO_DATA_FOUND THEN
547 ec_debug.pl(3,'When no data found then get desc from system items ',SQLERRM);
548 SELECT sitems.description
549 INTO x_item_description
550 FROM mtl_system_items_vl sitems
551 WHERE sitems.inventory_item_id = p_inventory_item_id
552 AND sitems.organization_id = l_organization_id ;
553 WHEN OTHERS THEN
554 oe_debug_pub.add('Unable to get Item Description '||SQLERRM,1);
555 NULL;
556 END Get_Item_Description;
557
558 END ECEPOI;