[Home] [Help]
PACKAGE BODY: APPS.OE_XML_PROCESS_UTIL
Source
1 PACKAGE BODY OE_XML_PROCESS_UTIL AS
2 /* $Header: OEXUPOXB.pls 120.3 2006/02/15 22:42:36 ppnair noship $ */
3
4 Procedure Concat_Strings(
5 String1 IN VARCHAR2,
6 String2 IN VARCHAR2,
7 OUT_String OUT NOCOPY VARCHAR2
8
9 )
10 IS
11 --
12 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
13 --
14 Begin
15
16 OUT_String := String1 || String2;
17
18 End Concat_Strings;
19
20 Procedure Get_Ship_To_Org_Id(
21 p_address_id IN NUMBER,
22 x_ship_to_org_id OUT NOCOPY NUMBER
23
24 )
25 IS
26 l_site_use_id Number;
27 --
28 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
29 --
30 Begin
31 IF l_debug_level > 0 THEN
32 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_SHIP_TO_ORG_ID' ) ;
33 END IF;
34 SELECT site_use_id
35 INTO l_site_use_id
36 FROM hz_cust_site_uses_all a, hz_cust_acct_sites_all b
37 WHERE a.cust_acct_site_id = b.cust_acct_site_id
38 AND a.cust_acct_site_id = p_address_id
39 AND a.site_use_code = 'SHIP_TO'
40 AND a.status = 'A'
41 AND b.status ='A'; --bug 2752321
42
43 x_ship_to_org_id := l_site_use_id;
44 Exception
45 When Others Then
46 x_ship_to_org_id := NULL;
47 fnd_message.set_name ('ONT', 'OE_OI_ORG_NOT_FOUND');
48 fnd_message.set_token ('SITE_USAGE', 'SHIP-TO');
49 fnd_message.set_token ('ADDRESS_ID', p_address_id);
50 oe_msg_pub.add;
51 End Get_Ship_To_Org_Id;
52
53
54 Procedure Get_Bill_To_Org_Id(
55 p_address_id IN NUMBER,
56 x_bill_to_org_id OUT NOCOPY NUMBER
57
58 )
59 IS
60 l_site_use_id Number;
61 --
62 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
63 --
64 Begin
65 IF l_debug_level > 0 THEN
66 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_BILL_TO_ORG_ID' ) ;
67 END IF;
68
69 SELECT site_use_id
70 INTO l_site_use_id
71 FROM hz_cust_site_uses_all a, hz_cust_acct_sites_all b
72 WHERE a.cust_acct_site_id = b.cust_acct_site_id
73 AND a.cust_acct_site_id = p_address_id
74 AND a.site_use_code = 'BILL_TO'
75 AND a.status = 'A'
76 AND b.status ='A';--bug 2752321
77
78 x_bill_to_org_id := l_site_use_id;
79 Exception
80 When Others Then
81 x_bill_to_org_id := NULL;
82 fnd_message.set_name ('ONT', 'OE_OI_ORG_NOT_FOUND');
83 fnd_message.set_token ('SITE_USAGE', 'BILL-TO');
84 fnd_message.set_token ('ADDRESS_ID', p_address_id);
85 oe_msg_pub.add;
86 End Get_Bill_To_Org_Id;
87
88 Procedure Get_Sold_To_Org_Id(
89 p_address_id IN NUMBER,
90 x_sold_to_org_id OUT NOCOPY NUMBER
91
92 )
93 IS
94 l_sold_to_org_id Number;
95 --
96 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
97 --
98 Begin
99 IF l_debug_level > 0 THEN
100 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_SOLD_TO_ORG_ID' ) ;
101 END IF;
102
103 SELECT cust_account_id
104 INTO l_sold_to_org_id
105 FROM hz_cust_acct_sites_all
106 WHERE cust_acct_site_id = p_address_id
107 AND status = 'A';
108
109 x_sold_to_org_id := l_sold_to_org_id;
110 Exception
111 When Others Then
112 x_sold_to_org_id := NULL;
113 fnd_message.set_name ('ONT', 'OE_OI_ORG_NOT_FOUND');
114 fnd_message.set_token ('SITE_USAGE', 'SOLD-TO');
115 fnd_message.set_token ('ADDRESS_ID', p_address_id);
116 oe_msg_pub.add;
117 End Get_Sold_To_Org_Id;
118
119 Procedure Get_Sold_To_Edi_Loc(
120 p_sold_to_org_id IN Number,
121 x_edi_location_code OUT NOCOPY Varchar2,
122
123 x_sold_to_name OUT NOCOPY Varchar2
124
125 )
126 IS
127 --
128 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
129 --
130 Begin
131 IF l_debug_level > 0 THEN
132 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_SOLD_TO_EDI_LOC' ) ;
133 END IF;
134
135 Select /* MOAC_SQL_CHANGE */ a.ece_tp_location_code, c.party_name
136 Into x_edi_location_code, x_sold_to_name
137 From hz_cust_acct_sites a, hz_cust_site_uses_all b,
138 hz_parties c, hz_cust_accounts d
139 Where a.cust_acct_site_id = b.cust_acct_site_id
140 And a.cust_account_id = p_sold_to_org_id
141 And b.site_use_code = 'SOLD_TO'
142 And b.primary_flag = 'Y'
143 And b.status = 'A'
144 AND a.status = 'A' --bug 2752321
145 And a.cust_account_id = d.cust_account_id
146 And d.party_id = c.party_id;
147
148 IF l_debug_level > 0 THEN
149 oe_debug_pub.add( 'EXITING OE_XML_PROCESS_UTIL.GET_SOLD_TO_EDI_LOC' ) ;
150 END IF;
151 Exception
152 When Others Then
153 x_edi_location_code := Null;
154 fnd_message.set_name ('ONT', 'OE_OI_EDI_LOC_NOT_FOUND');
155 fnd_message.set_token ('EDI_LOCATION', 'SOLD-TO');
156 fnd_message.set_token ('SITE_USAGE', p_sold_to_org_id);
157 oe_msg_pub.add;
158
159 End Get_Sold_To_Edi_Loc;
160
161 -- API which will return all the address data based on the site_use_id
162 -- Will be called by both EDI and XML
163
164 Procedure Get_Address_Details
165 (p_site_use_id In Number,
166 p_site_use_code In Varchar2,
167 x_location Out NOCOPY Varchar2,
168 x_address1 Out NOCOPY Varchar2,
169 x_address2 Out NOCOPY Varchar2,
170 x_address3 Out NOCOPY Varchar2,
171 x_address4 Out NOCOPY Varchar2,
172 x_city Out NOCOPY Varchar2,
173 x_state Out NOCOPY Varchar2,
174 x_country Out NOCOPY Varchar2,
175 x_postal_code Out NOCOPY Varchar2,
176 x_edi_location_code Out NOCOPY Varchar2,
177 x_customer_name Out NOCOPY Varchar2,
178 x_return_status Out NOCOPY Varchar2
179 )
180 Is
181 l_debug_level Constant Number := Oe_Debug_Pub.g_debug_level;
182 Begin
183
184 If l_debug_level > 0 Then
185 Oe_Debug_Pub.Add('Entering get_address_details');
186 Oe_Debug_Pub.Add('Site Use Id = '||p_site_use_id);
187 Oe_Debug_Pub.Add('Site Use Code = '||p_site_use_code);
188 End If;
189
190 If p_site_use_code = 'SHIP_FROM' Then
191
192 Select hl.Location_Code,
193 hl.Address_Line_1,
194 hl.Address_Line_2,
195 hl.Address_Line_3,
196 hl.Town_Or_City,
197 hl.Country,
198 hl.postal_code,
199 hl.ece_tp_location_code,
200 hu.name
201 Into x_location,
202 x_address1,
203 x_address2,
204 x_address3,
205 x_city,
206 x_country,
207 x_postal_code,
208 x_edi_location_code,
209 x_customer_name
210 From hr_all_organization_units hu, hr_locations hl
211 Where hl.location_id = hu.location_id
212 And hu.organization_id = p_site_use_id;
213
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215
216 Else
217
218 Select /* MOAC_SQL_CHANGE */ Site.Location,
219 Loc.Address1,
220 Loc.Address2,
221 Loc.Address3,
222 Loc.Address4,
223 Loc.City,
224 Loc.State,
225 Loc.Country,
226 Loc.Postal_Code,
227 Acct_Site.ece_tp_location_code,
228 Party.Party_Name
229 Into x_location,
230 x_address1,
231 x_address2,
232 x_address3,
233 x_address4,
234 x_city,
235 x_state,
236 x_country,
237 x_postal_code,
238 x_edi_location_code,
239 x_customer_name
240 From Hz_Cust_Site_Uses Site,
241 Hz_Party_Sites Party_Site,
242 Hz_Locations Loc,
243 Hz_Cust_Acct_Sites_All Acct_Site,
244 Hz_Parties Party,
245 Hz_Cust_Accounts Cust_Accts
246 Where Site.Site_Use_Code = p_site_use_code
247 And Site.Cust_Acct_Site_Id = Acct_Site.Cust_Acct_Site_Id
248 And Acct_Site.Party_Site_Id = Party_Site.Party_Site_Id
249 And Party_Site.Location_Id = Loc.Location_Id
250 And Site.Site_Use_Id = p_site_use_id
251 And Acct_Site.Cust_Account_id = Cust_Accts.Cust_Account_id
252 And Party.Party_Id = Cust_Accts.Party_Id;
253
254 x_return_status := FND_API.G_RET_STS_SUCCESS;
255
256 End If;
257
258 If l_debug_level > 0 Then
259 Oe_Debug_Pub.Add('Exiting get_address_details');
260 End If;
261
262 Exception
263
264 When Others Then
265 If l_debug_level > 0 Then
266 Oe_Debug_Pub.Add('Unable to derive address values for Ack');
267 End If;
268 x_return_status := FND_API.G_RET_STS_ERROR;
269
270 End Get_Address_Details;
271
272 Procedure Get_Contact_Details
273 (p_contact_id In Number,
274 p_cust_acct_id In Number,
275 x_first_name Out NOCOPY Varchar2,
276 x_last_name Out NOCOPY Varchar2,
277 x_return_status Out NOCOPY Varchar2)
278 Is
279
280 l_debug_level Constant Number := Oe_Debug_Pub.g_debug_level;
281
282 Begin
283
284 If l_debug_level > 0 Then
285 Oe_Debug_Pub.Add('Entering Get_Contact_Details');
286 Oe_Debug_Pub.Add('Contact Id = '||p_contact_id);
287 Oe_Debug_Pub.Add('Cust Acct Id = '||p_cust_acct_id);
288 End If;
289
290 Select a.person_first_name, a.person_last_name
291 Into x_first_name, x_last_name
292 From hz_parties a, hz_relationships b,
293 hz_cust_account_roles c
294 Where c.cust_account_role_id = p_contact_id
295 And c.party_id = b.party_id
296 And b.subject_id = a.party_id
297 And b.subject_table_name = 'HZ_PARTIES'
298 And b.object_table_name = 'HZ_PARTIES'
299 And c.cust_account_id = p_cust_acct_id
300 And b.directional_flag = 'F';
301
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 Exception
305
306 When Others Then
307 If l_debug_level > 0 Then
308 Oe_Debug_Pub.Add('Unable to derive Contact info for Ack '||sqlerrm);
309 End If;
310 x_return_status := FND_API.G_RET_STS_ERROR;
311
312 End Get_Contact_Details;
313
314
315 Procedure Get_Ship_From_Edi_Loc(
316 p_ship_from_org_id IN Number,
317 x_edi_location_code OUT NOCOPY Varchar2
318
319 )
320 IS
321 --
322 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
323 --
324 Begin
325 IF l_debug_level > 0 THEN
326 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_SHIP_FROM_EDI_LOC' ) ;
327 END IF;
328
329 Select hl.ece_tp_location_code
330 Into x_edi_location_code
331 From hr_all_organization_units hu, hr_locations hl
332 Where hl.location_id = hu.location_id
333 And hu.organization_id = p_ship_from_org_id;
334
335 Exception
336 When Others Then
337 x_edi_location_code := Null;
338 fnd_message.set_name ('ONT', 'OE_OI_EDI_LOC_NOT_FOUND');
339 fnd_message.set_token ('EDI_LOCATION', 'SHIP-FROM');
340 fnd_message.set_token ('SITE_USAGE', p_ship_from_org_id);
341 oe_msg_pub.add;
342 End Get_Ship_From_Edi_Loc;
343
344 -- {
345 -- This function will get the Total of the Order or specific Line
346 Procedure Get_Order_Total(
347 p_header_id IN NUMBER,
348 p_line_id IN NUMBER,
349 p_total_type IN VARCHAR2,
350 x_order_line_total OUT NOCOPY NUMBER
351
352 )
353 IS
354 l_order_line_total NUMBER := 0;
355 --
356 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
357 --
358 BEGIN
359 l_order_line_total := oe_totals_grp.get_order_total
360 ( p_header_id => p_header_id,
361 p_line_id => p_line_id,
362 p_total_type => p_total_type);
363
364 x_order_line_total := l_order_line_total;
365 Exception
366 When Others Then
367 x_order_line_total := 0;
368 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
369 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_order_total');
370 End if;
371
372
373 End Get_Order_Total;
374 -- } End of procedure
375
376 -- { Start Get_Processing_Msgs
377 PROCEDURE Get_Processing_Msgs
378 ( p_request_id in varchar2,
379 p_order_source_id in number := 20,
380 p_orig_sys_document_ref in varchar2 := NULL,
381 p_orig_sys_line_ref in varchar2 := NULL,
382 p_ack_code in varchar2 := '0',
383 p_org_id in number := null,
384 x_error_text out nocopy varchar2,
385
386 x_result out nocopy varchar2
387
388 )
389 IS
390 CURSOR l_msg_cursor_hdr IS
391 SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
392 USE_NL (a b) */
393 a.order_source_id
394 , a.original_sys_document_ref
395 , b.message_text
396 FROM oe_processing_msgs a, oe_processing_msgs_tl b
397 WHERE a.request_id = p_request_id
398 AND a.order_source_id = p_order_source_id
399 AND a.original_sys_document_ref = p_orig_sys_document_ref
400 AND (a.org_id is null or a.org_id = p_org_id)
401 AND a.original_sys_document_line_ref is null
402 AND a.transaction_id = b.transaction_id
403 AND b.language = oe_globals.g_lang;
404
405 CURSOR l_msg_cursor_line IS
406 SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
407 USE_NL (a b) */
408 a.order_source_id
409 , a.original_sys_document_ref
410 , a.original_sys_document_line_ref
411 , b.message_text
412 FROM oe_processing_msgs a, oe_processing_msgs_tl b
413 WHERE a.request_id = p_request_id
414 AND a.order_source_id = p_order_source_id
415 AND a.original_sys_document_ref = p_orig_sys_document_ref
416 AND a.original_sys_document_line_ref = p_orig_sys_line_ref
417 AND (a.org_id is null or a.org_id = p_org_id)
418 AND a.transaction_id = b.transaction_id
419 AND b.language = oe_globals.g_lang;
420
421 l_message_text Varchar2(2000);
422 l_error_text Varchar2(4000);
423 l_order_source_id Number;
424 l_orig_sys_document_ref Varchar2(50);
425 l_orig_sys_line_ref Varchar2(50);
426
427 --
428 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
429 --
430 BEGIN
431
432 IF l_debug_level > 0 THEN
433 oe_debug_pub.add( 'ENTERING GET_PROCESSING_MSGS' ) ;
434 END IF;
435
436 -- { Start If p_ack_code
437 /* If p_ack_code = '0' Then
438 x_error_text := 'Accepted';
439 x_result := FND_API.G_RET_STS_SUCCESS;
440 IF l_debug_level > 0 THEN
441 oe_debug_pub.add( 'EXITING GET_PROCESSING_MSGS WITH ACCEPTED' ) ;
442 END IF;
443 Return;
444 End If;*/
445 -- End If p_ack_code }
446
447 -- { Start of if p_orig_sys_line_ref
448 If p_orig_sys_line_ref is NULL Then
449 OPEN l_msg_cursor_hdr;
450 LOOP
451 FETCH l_msg_cursor_hdr
452 INTO l_order_source_id
453 , l_orig_sys_document_ref
454 , l_message_text;
455 EXIT WHEN l_msg_cursor_hdr%NOTFOUND;
456
457 l_error_text := substr(l_error_text
458 ||','||to_char(l_order_source_id)
459 ||'/'||l_orig_sys_document_ref
460 ||' '||l_message_text, 1, 4000);
461 END LOOP;
462 CLOSE l_msg_cursor_hdr;
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add( 'EXITING GET_PROCESSING_MSGS WITH REJECTED HEADER' ) ;
465 oe_debug_pub.add( L_ERROR_TEXT ) ;
466 END IF;
467 Else
468 OPEN l_msg_cursor_line;
469 LOOP
470 FETCH l_msg_cursor_line
471 INTO l_order_source_id
472 , l_orig_sys_document_ref
473 , l_orig_sys_line_ref
474 , l_message_text;
475 EXIT WHEN l_msg_cursor_line%NOTFOUND;
476
477 l_error_text := substr(l_error_text
478 ||','||to_char(l_order_source_id)
479 ||'/'||l_orig_sys_document_ref
480 ||'/'||l_orig_sys_line_ref
481 ||' '||l_message_text, 1, 4000);
482 END LOOP;
483 CLOSE l_msg_cursor_line;
484 IF l_debug_level > 0 THEN
485 oe_debug_pub.add( 'EXITING GET_PROCESSING_MSGS WITH REJECTED LINE' ) ;
486 oe_debug_pub.add( L_ERROR_TEXT ) ;
487 END IF;
488 End If;
489 -- End of if p_orig_sys_line_ref }
490
491 If l_error_text IS NULL then
492 If p_ack_code = '0' Then
493 l_error_text := 'Accepted';
494 Elsif p_ack_code = '2' Then
495 -- If Failed without logging any error, at least Rejected code should go.
496 l_error_text := 'Rejected';
497 Elsif p_ack_code = '3' Then
498 l_error_text := 'Pending';
499 End If;
500 End If;
501 x_error_text := l_error_text;
502 x_result := FND_API.G_RET_STS_SUCCESS;
503 IF l_debug_level > 0 THEN
504 oe_debug_pub.add( 'EXITING GET_PROCESSING_MSGS' ) ;
505 END IF;
506 Exception
507 When Others Then
508 IF l_debug_level > 0 THEN
509 oe_debug_pub.add( 'OTHERS ERROR IN GETTING ERROR MESSAGE. SQLERR: ' || SQLERRM ) ;
510 END IF;
511 x_result := FND_API.G_RET_STS_UNEXP_ERROR;
512 IF l_debug_level > 0 THEN
513 oe_debug_pub.add( 'EXITING GET_PROCESSING_MSGS' ) ;
514 END IF;
515 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
516 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_processing_msgs');
517 End if;
518 END Get_Processing_Msgs;
519 -- End Get_Processing_Msgs}
520
521 Procedure Get_Sales_Person(
522 p_salesrep_id IN Number,
523 x_salesrep OUT NOCOPY Varchar2
524
525 )
526 IS
527 --
528 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
529 --
530 Begin
531 IF l_debug_level > 0 THEN
532 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_SALES_PERSON' ) ;
533 END IF;
534 x_salesrep := Oe_Id_To_Value.Salesrep(p_salesrep_id => p_salesrep_id);
535
536 Exception
537 When Others Then
538 x_salesrep := Null;
539 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
540 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_sales_person');
541 End if;
542
543 End Get_Sales_Person;
544
545 Procedure Get_Line_Ordered_Quantity
546 (
547 p_orig_sys_document_ref IN VARCHAR2,
548 p_orig_sys_line_ref IN VARCHAR2,
549 p_orig_sys_shipment_ref IN VARCHAR2,
550 p_order_source_id IN NUMBER,
551 p_sold_to_org_id IN NUMBER,
552 x_ordered_quantity OUT NOCOPY NUMBER
553
554 )
555 IS
556
557 l_customer_key_profile VARCHAR2(1) := 'N';
558
559 --
560 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
561 --
562 Begin
563 IF l_debug_level > 0 THEN
564 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_LINE_ORDERED_QUANTITY' ) ;
565 END IF;
566
567 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
568 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
569 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
570 IF l_debug_level > 0 THEN
571 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
572 END IF;
573 End If;
574
575 select ordered_quantity
576 into x_ordered_quantity
577 from oe_order_lines_all
578 where orig_sys_document_ref = p_orig_sys_document_ref
579 and orig_sys_line_ref = p_orig_sys_line_ref
580 and orig_sys_shipment_ref = p_orig_sys_shipment_ref
581 and order_source_id = p_order_source_id
582 and decode(l_customer_key_profile, 'Y',
583 nvl(sold_to_org_id, -999), 1)
584 = decode(l_customer_key_profile, 'Y',
585 nvl(p_sold_to_org_id, -999), 1);
586
587
588
589 Exception
590 When Others Then
591 x_ordered_quantity := NULL;
592 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
593 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_line_ordered_quantity');
594 End if;
595
596
597 End Get_Line_Ordered_Quantity;
598
599 Procedure Get_Line_Ordered_Quantity_UOM
600 (
601 p_orig_sys_document_ref IN VARCHAR2,
602 p_orig_sys_line_ref IN VARCHAR2,
603 p_orig_sys_shipment_ref IN VARCHAR2,
604 p_order_source_id IN NUMBER,
605 p_sold_to_org_id IN NUMBER,
606 x_ordered_quantity_uom OUT NOCOPY VARCHAR2
607
608 )
609 IS
610
611 l_customer_key_profile VARCHAR2(1) := 'N';
612
613 --
614 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
615 --
616 Begin
617 IF l_debug_level > 0 THEN
618 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.GET_LINE_ORDERED_QUANTITY_UOM' ) ;
619 END IF;
620
621
622 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
623 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
624 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
625 IF l_debug_level > 0 THEN
626 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
627 END IF;
628 End If;
629
630
631 select order_quantity_uom
632 into x_ordered_quantity_uom
633 from oe_order_lines_all
634 where orig_sys_document_ref = p_orig_sys_document_ref
635 and orig_sys_line_ref = p_orig_sys_line_ref
636 and orig_sys_shipment_ref = p_orig_sys_shipment_ref
637 and order_source_id = p_order_source_id
638 and decode(l_customer_key_profile, 'Y',
639 nvl(sold_to_org_id, -999), 1)
640 = decode(l_customer_key_profile, 'Y',
641 nvl(p_sold_to_org_id, -999), 1);
642
643
644
645 Exception
646 When Others Then
647 x_ordered_quantity_uom := NULL;
648 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
649 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_line_ordered_quantity_uom');
650 End if;
651
652
653 End Get_Line_Ordered_Quantity_UOM;
654
655 PROCEDURE Set_Cancelled_Flag
656 (p_orig_sys_document_ref in varchar2,
657 p_transaction_type in varchar2,
658 p_order_source_id in number,
659 p_sold_to_org_id in number,
660 p_change_sequence in varchar2,
661 p_org_id in number,
662 p_xml_message_id in number
663 )
664 is
665
666 l_customer_key_profile VARCHAR2(1) := 'N';
667
668 --
669 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
670 --
671 begin
672 IF l_debug_level > 0 THEN
673 oe_debug_pub.add( 'ENTERING OE_XML_PROCESS_UTIL.SET_CANCELLED_FLAG' ) ;
674 END IF;
675
676
677
678 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
679 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
680 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
681 IF l_debug_level > 0 THEN
682 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
683 END IF;
684 End If;
685
686
687
688 update oe_headers_iface_all
689 set cancelled_flag='Y'
690 where orig_sys_document_ref = p_orig_sys_document_ref
691 and order_source_id = p_order_source_id
692 and decode(l_customer_key_profile, 'Y',
693 nvl(sold_to_org_id, -999), 1)
694 = decode(l_customer_key_profile, 'Y',
695 nvl(p_sold_to_org_id, -999), 1)
696 and nvl(change_sequence, ' ')
697 = nvl(p_change_sequence, ' ')
698 and xml_transaction_type_code = p_transaction_type
699 and org_id = p_org_id
700 and xml_message_id = p_xml_message_id;
701
702 Exception
703 When Others Then
704 null;
705 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
706 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'set_cancelled_flag');
707 End if;
708 end set_cancelled_flag;
709
710 PROCEDURE Clear_Oe_Header_And_Line_Acks
711 (p_orig_sys_document_ref in varchar2,
712 p_ack_type in varchar2,
713 p_sold_to_org_id in number,
714 p_change_sequence in varchar2,
715 p_request_id in number
716 )
717 is
718
719 l_customer_key_profile VARCHAR2(1) := 'N';
720
721 --
722 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
723 --
724 begin
725
726 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
727 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
728 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
729 IF l_debug_level > 0 THEN
730 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
731 END IF;
732 End If;
733
734 Delete from OE_HEADER_ACKS
735 Where orig_sys_document_ref = p_orig_sys_document_ref
736 And acknowledgment_type = p_ack_type
737 And decode(l_customer_key_profile, 'Y',
738 nvl(sold_to_org_id, -999), 1)
739 = decode(l_customer_key_profile, 'Y',
740 nvl(p_sold_to_org_id, -999), 1)
741 And nvl(change_sequence, ' ')
742 = nvl(p_change_sequence, ' ')
743 And request_id = p_request_id;
744 IF l_debug_level > 0 THEN
745 oe_debug_pub.add( 'DELETED OE_HEADER_ACKS ENTRIES FOR ORIG_SYS_DOCUMENT_REF => ' || P_ORIG_SYS_DOCUMENT_REF ||
746 ' AND ACKNOWLEDGMENT_TYPE => ' || P_ACK_TYPE || ' AND REQUEST_ID => ' || P_REQUEST_ID ||
747 ' AND SOLD_TO_ORG_ID => ' || P_SOLD_TO_ORG_ID ||
748 ' AND CHANGE_SEQUENCE => ' || P_CHANGE_SEQUENCE);
749 END IF;
750
751
752 Delete from OE_LINE_ACKS
753 Where orig_sys_document_ref = p_orig_sys_document_ref
754 And acknowledgment_type = p_ack_type
755 And decode(l_customer_key_profile, 'Y',
756 nvl(sold_to_org_id, -999), 1)
757 = decode(l_customer_key_profile, 'Y',
758 nvl(p_sold_to_org_id, -999), 1)
759 And nvl(change_sequence, ' ')
760 = nvl(p_change_sequence, ' ')
761 And request_id = p_request_id;
762 IF l_debug_level > 0 THEN
763 oe_debug_pub.add( 'DELETED OE_LINE_ACKS ENTRIES FOR ORIG_SYS_DOCUMENT_REF => ' || P_ORIG_SYS_DOCUMENT_REF ||
764 ' AND ACKNOWLEDGMENT_TYPE => ' || P_ACK_TYPE || ' AND REQUEST_ID => ' || P_REQUEST_ID ||
765 ' AND SOLD_TO_ORG_ID => ' || P_SOLD_TO_ORG_ID ||
766 ' AND CHANGE_SEQUENCE => ' || P_CHANGE_SEQUENCE);
767 END IF;
768
769
770 Exception
771 When Others Then
772 null;
773 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
774 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'clear_oe_header_and_line_acks');
775 End if;
776 end Clear_Oe_Header_And_Line_Acks;
777
778 Procedure Derive_Line_Operation_Code
779 ( p_orig_sys_document_ref in varchar2,
780 p_orig_sys_line_ref in varchar2,
781 p_orig_sys_shipment_ref in varchar2,
782 p_order_source_id in number,
783 p_sold_to_org_id in number,
784 p_org_id in number,
785 x_operation_code OUT NOCOPY varchar2
786 )
787 Is
788 -- l_dummy number := NULL;
789 l_dummy varchar2 (50) := NULL;
790 l_customer_key_profile VARCHAR2(1) := 'N';
791
792
793 --
794 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
795 --
796
797 Begin
798
799
800 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
801 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
802 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
803 IF l_debug_level > 0 THEN
804 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
805 END IF;
806 End If;
807
808
809 Begin
810 Select orig_sys_document_ref
811 Into l_dummy
812 From oe_order_lines_all
813 Where orig_sys_document_ref = p_orig_sys_document_ref
814 And orig_sys_line_ref = p_orig_sys_line_ref
815 And orig_sys_shipment_ref = p_orig_sys_shipment_ref
816 And order_source_id = p_order_source_id
817 And decode(l_customer_key_profile, 'Y',
818 nvl(sold_to_org_id, -999), 1)
819 = decode(l_customer_key_profile, 'Y',
820 nvl(p_sold_to_org_id, -999), 1)
821 And org_id = p_org_id;
822
823 x_operation_code := 'UPDATE';
824 Exception
825 When NO_DATA_FOUND Then
826 x_operation_code := NULL;
827 When OTHERS Then
828 x_operation_code := NULL;
829 End;
830
831 IF l_debug_level > 0 THEN
832 oe_debug_pub.add( 'operation code is '|| x_operation_code ||' for orig_sys_document_ref ' || p_orig_sys_document_ref ||
833 ' orig_sys_line_ref ' || p_orig_sys_line_ref ||
834 ' orig_sys_shipment_ref ' || p_orig_sys_shipment_ref);
835 END IF;
836
837 End Derive_Line_Operation_Code;
838
839 Procedure Check_Rejected_Level
840 (p_header_ack_code In Varchar2,
841 p_line_ack_code In Varchar2,
842 p_shipment_ack_code In Varchar2,
843 p_response_profile In Varchar2,
844 p_ordered_quantity In Number,
845 p_response_flag In Varchar2,
846 p_level_code In Varchar2,
847 x_insert_flag Out Nocopy Varchar2)
848 IS
849 BEGIN
850 x_insert_flag := 'Y';
851 IF nvl(p_response_flag,'N') = 'N' OR nvl(p_response_profile,'N') = 'N' THEN
852 RETURN;
853 END IF;
854
855 IF p_level_code = 'H' THEN
856 IF nvl(p_header_ack_code,'0') = '2' THEN
857 x_insert_flag := 'N';
858 END IF;
859 ELSIF p_level_code = 'L' THEN
860 IF nvl(p_header_ack_code,'0') = '2' THEN
861 x_insert_flag := 'N';
862 ELSIF (nvl(p_line_ack_code,'0')= '2' OR nvl(p_shipment_ack_code,'0') = '2')
863 --AND nvl(p_response_profile,'N') = 'Y'
864 --AND nvl(p_response_flag,'N') = 'Y'
865 --AND nvl(p_ordered_quantity, FND_API.G_MISS_NUM) <> 0
866 THEN
867 x_insert_flag := 'N';
868 END IF;
869 END IF;
870 END Check_Rejected_Level;
871
872 Procedure Process_Response_Reject
873 (p_header_ack_code In Varchar2,
874 p_line_ack_code In Varchar2,
875 p_shipment_ack_code In Varchar2,
876 p_ordered_quantity In Number,
877 p_response_flag In Varchar2,
878 p_event_raised_flag In Varchar2,
879 p_level_code In Varchar2,
880 p_orig_sys_document_ref In Varchar2,
881 p_change_sequence In Varchar2,
882 p_org_id In Varchar2,
883 p_sold_to_org_id In Number,
884 p_xml_message_id In Number,
885 p_confirmation_flag In Varchar2,
886 p_confirmation_message In Varchar2,
887 x_insert_level Out Nocopy Varchar2,
888 x_raised_event Out Nocopy Varchar2)
889 IS
890 l_response_profile varchar2(10) := nvl(FND_PROFILE.VALUE('ONT_3A7_RESPONSE_REQUIRED'),'N');
891 l_insert_level varchar2(1) := 'Y';
892 l_raise_event varchar2(1) := 'N';
893 l_message_text varchar2(2000);
894 l_status varchar2(10);
895 l_return_status varchar2(10);
896 l_order_number number := NULL;
897 l_order_type_id number := NULL;
898 l_header_id number := NULL;
899 l_customer_key_profile VARCHAR2(1) := 'N';
900
901 BEGIN
902 IF nvl(p_response_flag,'N') = 'N' OR l_response_profile = 'N' THEN
903 x_insert_level := 'Y';
904 x_raised_event := 'N';
905 RETURN;
906 END IF;
907
908 Check_Rejected_Level
909 (p_header_ack_code => p_header_ack_code,
910 p_line_ack_code => p_line_ack_code,
911 p_shipment_ack_code => p_shipment_ack_code,
912 p_response_profile => l_response_profile,
913 p_ordered_quantity => p_ordered_quantity,
914 p_response_flag => p_response_flag,
915 p_level_code => p_level_code,
916 x_insert_flag => l_insert_level);
917
918 IF p_level_code = 'H' THEN
919 IF l_insert_level = 'N' THEN
920 l_raise_event := 'Y';
921 l_status := 'ERROR'; -- bug 3578502, CLN needs this recorded as an error
922 FND_MESSAGE.SET_NAME('ONT','OE_OI_CSO_REJECTED_HEADER');
923 l_message_text := FND_MESSAGE.GET;
924 END IF;
925 ELSIF p_level_code = 'L' THEN
926 IF l_insert_level = 'N'
927 AND p_event_raised_flag = 'N' THEN
928 l_raise_event := 'Y';
929 l_status := 'ACTIVE';
930 FND_MESSAGE.SET_NAME('ONT','OE_OI_CSO_REJECTED_LINE');
931 l_message_text := FND_MESSAGE.GET;
932 END IF;
933 END IF;
934
935 IF l_raise_event = 'Y' THEN
936 -- bug 3578502
937 -- CLN needs the order number, hence we fetch it here
938 IF p_level_code = 'H' THEN
939 Begin
940 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
941 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
942 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
943 End If;
944
945 Select order_number, order_type_id, header_id
946 Into l_order_number, l_order_type_id, l_header_id
947 From oe_order_headers_all
948 Where orig_sys_document_ref = p_orig_sys_document_ref
949 And order_source_id = 20
950 And decode(l_customer_key_profile, 'Y',
951 nvl(sold_to_org_id, -999), 1)
952 = decode(l_customer_key_profile, 'Y',
953 nvl(p_sold_to_org_id, -999), 1)
954 And org_id = p_org_id;
955 Exception
956 When Others Then
957 l_order_number := NULL;
958 l_order_type_id := NULL;
959 l_header_id := NULL;
960 End;
961
962 OE_Acknowledgment_Pub.Raise_CBOD_Out_Event
963 (p_orig_sys_document_ref => p_orig_sys_document_ref,
964 p_sold_to_org_id => p_sold_to_org_id,
965 p_change_sequence => p_change_sequence,
966 p_icn => p_xml_message_id,
967 p_transaction_type => 'CHO',
968 p_org_id => p_org_id,
969 p_confirmation_flag => p_confirmation_flag,
970 p_cbod_message_text => p_confirmation_message,
971 x_return_status => l_return_status);
972
973 END IF;
974 -- end bug 3578502
975
976 OE_Acknowledgment_Pub.Raise_Event_Xmlint
977 (p_order_source_id => 20,
978 p_partner_document_num => p_orig_sys_document_ref,
979 p_document_num => l_order_number,
980 p_order_type_id => l_order_type_id,
981 p_message_text => l_message_text,
982 p_change_sequence => p_change_sequence,
983 p_header_id => l_header_id,
984 p_itemkey => NULL, p_itemtype => NULL,
985 p_transaction_type => 'ONT',
986 p_transaction_subtype => 'CHO',
987 p_doc_status => l_status,
988 p_org_id => p_org_id,
989 p_sold_to_org_id => p_sold_to_org_id,
990 p_xmlg_icn => p_xml_message_id,
991 p_processing_stage => 'INBOUND_GATEWAY',
992 p_response_flag => p_response_flag,
993 x_return_status => l_return_status);
994
995
996
997 END IF;
998
999 x_insert_level := l_insert_level;
1000 IF p_event_raised_flag = 'N' THEN
1001 x_raised_event := l_raise_event;
1002 ELSE
1003 x_raised_event := 'Y'; -- it shd already be Y, so just make sure you don't set it to N
1004 END IF;
1005
1006 END Process_Response_Reject;
1007
1008 END OE_XML_PROCESS_UTIL;