[Home] [Help]
PACKAGE BODY: APPS.OE_UPDATE_ACK_UTIL
Source
1 PACKAGE BODY OE_Update_Ack_Util AS
2 /* $Header: OEXUACKB.pls 120.8.12020000.2 2012/07/03 10:05:05 amallik ship $ */
3
4 PROCEDURE Update_Header_Ack_First(
5 p_header_id IN NUMBER
6 ,p_ack_code IN VARCHAR2
7 ) is
8
9 l_api_name CONSTANT VARCHAR2(30) := 'Update_Header_Ack_First';
10 l_ack_code VARCHAR2(30);
11 l_order_source_id Number;
12 l_orig_sys_document_ref Varchar2(50);
13 l_orig_sys_line_ref Varchar2(50);
14 l_request_id Number;
15 l_em_message_id Number;
16 l_header_id NUMBER;
17 BEGIN
18 l_ack_code := 'AT';
19 Begin
20
21 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
22 -- bug 3392678
23 SELECT OE_XML_MESSAGE_SEQ_S.NEXTVAL
24 INTO l_em_message_id
25 FROM DUAL;
26 END IF;
27
28 Select order_source_id, orig_sys_document_ref, request_id
29 into l_order_source_id, l_orig_sys_document_ref, l_request_id
30 From oe_header_acks
31 Where header_id = p_header_id
32 And first_ack_date is null
33 And rownum = 1;
34
35 Delete oe_lines_interface
36 Where order_source_id = l_order_source_id
37 And orig_sys_document_ref = l_orig_sys_document_ref
38 And request_id = l_request_id
39 And rejected_flag = 'Y';
40
41 if sql%rowcount > 0 Then
42 Delete oe_headers_interface
43 Where order_source_id = l_order_source_id
44 And orig_sys_document_ref = l_orig_sys_document_ref
45 And request_id = l_request_id;
46 end if;
47
48 Exception
49 When Others then
50 oe_debug_pub.add('When Others excep=> ' || sqlerrm);
51 End;
52 -- Lock the Header Table before Updating for bug 4505695
53 -- Changing oe_order_headers to oe_order_headers_all for bug#13831068
54 SELECT header_id
55 INTO l_header_id
56 FROM oe_order_headers_all
57 WHERE header_id = p_header_id
58 FOR UPDATE nowait;
59
60 UPDATE oe_order_headers_all
61 SET first_ack_code = l_ack_code
62 , first_ack_date = sysdate
63 , lock_control = lock_control + 1
64 , xml_message_id = l_em_message_id
65 WHERE header_id = p_header_id;
66
67 UPDATE oe_header_acks
68 SET acknowledgment_flag = 'Y'
69 , first_ack_date = sysdate
70 WHERE header_id = p_header_id
71 AND first_ack_date is null;
72
73 EXCEPTION
74 WHEN OTHERS THEN NULL;
75
76 END Update_Header_Ack_first;
77
78
79 PROCEDURE Update_Header_Ack_Last(
80 p_header_id IN NUMBER
81 ,p_ack_code IN VARCHAR2
82 ) is
83
84 l_api_name CONSTANT VARCHAR2(30) := 'Update_Header_Ack_Last';
85 l_ack_code VARCHAR2(30);
86 l_em_message_id Number;
87 l_header_id NUMBER;
88 BEGIN
89 begin
90
91 IF OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
92 -- bug 3392678
93 SELECT OE_XML_MESSAGE_SEQ_S.NEXTVAL
94 INTO l_em_message_id
95 FROM DUAL;
96 END IF;
97
98 select last_ack_code
99 into l_ack_code
100 from oe_header_acks
101 where header_id = p_header_id
102 and first_ack_date is not null
103 and last_ack_code is null
104 and rownum = 1;
105 exception
106 when others then
107 l_ack_code := 'AT';
108 end;
109
110 --Lock the header table before Updating bug4505695
111 -- Changing oe_order_headers to oe_order_headers_all for bug#13831068
112 SELECT header_id
113 INTO l_header_id
114 FROM oe_order_headers_all
115 WHERE header_id = p_header_id
116 FOR UPDATE nowait;
117 UPDATE oe_order_headers_all
118 SET last_ack_code = l_ack_code
119 , last_ack_date = sysdate
120 , lock_control = lock_control + 1
121 , xml_message_id = l_em_message_id
122 WHERE header_id = p_header_id;
123
124 UPDATE oe_header_acks
125 SET acknowledgment_flag = 'Y'
126 , last_ack_date = sysdate
127 WHERE header_id = p_header_id
128 AND first_ack_date is not null
129 AND last_ack_date is null;
130
131 EXCEPTION
132 WHEN OTHERS THEN NULL;
133
134 END Update_Header_Ack_last;
135
136
137 PROCEDURE Update_Line_Ack_First(
138 p_header_id IN NUMBER
139 ,p_line_id IN NUMBER
140 ,p_ack_code IN VARCHAR2
141 ) is
142
143 l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Ack_First';
144 l_first_ack_date DATE := fnd_api.g_miss_date;
145 l_ack_code VARCHAR2(30);
146 l_header_id NUMBER;
147 BEGIN
148 begin
149 select first_ack_code
150 into l_ack_code
151 from oe_line_acks
152 where header_id = p_header_id
153 and line_id = p_line_id
154 and (first_ack_date is null
155 or first_ack_date = l_first_ack_date)
156 and rownum = 1;
157 exception
158 when others then
159 l_ack_code := 'IA'; --Changed AT to IA for bug4137350
160 end;
161
162 -- Lock the lines table before UPDATE for bug4505695
163 -- Changing oe_order_lines to oe_order_line_all for bug#13831068
164 SELECT header_id
165 INTO l_header_id
166 FROM oe_order_lines_all
167 WHERE line_id = p_line_id
168 FOR UPDATE nowait;
169
170 UPDATE oe_order_lines_all
171 SET first_ack_code = l_ack_code
172 , first_ack_date = sysdate
173 , lock_control = lock_control + 1
174 WHERE line_id = p_line_id;
175
176 UPDATE oe_line_acks
177 SET acknowledgment_flag = 'Y'
178 , first_ack_date = sysdate
179 WHERE header_id = p_header_id
180 AND line_id = p_line_id
181 AND (first_ack_date is null
182 OR first_ack_date = l_first_ack_date);
183 UPDATE oe_line_acks
184 SET acknowledgment_flag = 'Y'
185 , first_ack_date = sysdate
186 WHERE header_id = p_header_id
187 AND line_id is null
188 AND (first_ack_date is null
189 OR first_ack_date = l_first_ack_date);
190
191 EXCEPTION
192 WHEN OTHERS THEN NULL;
193
194 END Update_Line_Ack_first;
195
196
197 PROCEDURE Update_Line_Ack_Last(
198 p_header_id IN NUMBER
199 ,p_line_id IN NUMBER
200 ,p_ack_code IN VARCHAR2
201 ) is
202
203 l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Ack_Last';
204 l_ack_code VARCHAR2(30);
205 l_header_id NUMBER;
206
207 BEGIN
208 begin
209 select last_ack_code
210 into l_ack_code
211 from oe_line_acks
212 where header_id = p_header_id
213 and line_id = p_line_id
214 and first_ack_date is not null
215 and last_ack_date is null
216 and rownum = 1;
217 exception
218 when others then
219 l_ack_code := 'IA'; --Changed AT to IA for bug4137350
220 end;
221 -- Lock the lines table before UPDATE for bug 4505695
222 -- Changing oe_order_lines to oe_order_line_all for bug#13831068
223 SELECT header_id
224 INTO l_header_id
225 FROM oe_order_lines_all
226 WHERE line_id = p_line_id
227 FOR UPDATE nowait;
228
229 UPDATE oe_order_lines_all
230 SET last_ack_code = l_ack_code
231 , last_ack_date = sysdate
232 , lock_control = lock_control + 1
233 , first_ack_code = nvl(first_ack_code, l_ack_code)
234 , first_ack_date = nvl(first_ack_date,sysdate)
235 WHERE line_id = p_line_id;
236
237 UPDATE oe_line_acks
238 SET acknowledgment_flag = 'Y'
239 , last_ack_date = sysdate
240 WHERE header_id = p_header_id
241 AND line_id = p_line_id
242 AND first_ack_date is not null
243 AND last_ack_date is null;
244
245 EXCEPTION
246 WHEN OTHERS THEN NULL;
247
248 END Update_Line_Ack_Last;
249
250
251 -- Procedure to get values for Acknowledgments
252 -- This procedure will be called by a workflow which receives the write ack data event.
253 -- It will fetch all the values required to be sent on the Ack and then write the
254 -- data to the Ack tables.
255 -- To be delivered with Pack J
256
257 Procedure write_ack_data_values
258 (p_header_id In Number,
259 p_transaction_type In Varchar2,
260 x_return_status Out Nocopy Varchar2)
261 Is
262 --added end customer fields for bug 4034441
263 Cursor Header_Acks_Cur IS
264 Select Header_Id,
265 Org_Id,
266 Sold_To_Org_Id,
267 Ship_To_Org_Id,
268 Invoice_To_Org_Id,
269 Sold_To_Contact_Id,
270 Ship_To_Contact_Id,
271 Ship_From_Org_Id,
272 Order_Type_Id,
273 Price_List_Id,
274 Payment_Term_Id,
275 Salesrep_Id,
276 Fob_Point_Code,
277 Freight_Terms_Code,
278 Agreement_Id,
279 Conversion_Type_Code,
280 Tax_Exempt_Reason_Code,
281 Tax_Point_Code,
282 Invoicing_Rule_Id,
283 End_Customer_Id,
284 End_Customer_Contact_Id,
285 End_Customer_Site_Use_Id
286
287 From Oe_Header_Acks
288 Where Header_Id = p_header_id
289 And Acknowledgment_Flag Is Null
290 For Update;
291 --Added end customer fields for bug 4034441
292 Cursor Line_Acks_Cur Is
293 Select line_id,
294 ship_to_org_id,
295 invoice_to_org_id,
296 invoice_to_contact_id,
297 ship_from_org_id,
298 agreement_id,
299 price_list_id,
300 arrival_set_id,
301 accounting_rule_id,
302 fob_point_code,
303 freight_terms_code,
304 fulfillment_set_id,
305 inventory_item_id,
306 invoice_set_id,
307 invoicing_rule_id,
308 line_type_id,
309 order_source_id,
310 payment_term_id,
311 project_id,
312 salesrep_id,
313 ship_set_id,
314 ship_to_contact_id,
315 shipping_method_code,
316 task_id,
317 tax_code,
318 tax_exempt_reason_code,
319 tax_point_code,
320 line_type,
321 ship_to_address1,
322 ship_to_address2,
323 ship_to_address3,
324 ship_to_address4,
325 ship_to_country,
326 ship_to_state,
327 ship_to_postal_code,
328 ship_to_city,
329 ship_to_address_code,
330 ship_to_edi_location_code,
331 ship_to_org,
332 ship_from_address_1,
333 ship_from_address_2,
334 ship_from_address_3,
335 ship_from_city,
336 ship_from_postal_code,
337 ship_from_country,
338 ship_from_org,
339 ship_from_edi_location_code,
340 invoice_to_org,
341 invoice_city,
342 invoice_address_code,
343 agreement,
344 price_list,
345 arrival_set_name,
346 accounting_rule,
347 fob_point,
348 freight_terms,
349 fulfillment_set_name,
350 inventory_item,
351 invoice_set_name,
352 invoicing_rule,
353 payment_term,
354 project,
355 salesrep,
356 ship_set_name,
357 ship_to_contact,
358 ship_to_contact_first_name,
359 ship_to_contact_last_name,
360 shipping_method,
361 fob_point_code,
362 freight_terms_code,
363 shipping_method_code,
364 tax_code,
365 tax_point_code,
366 tax_exempt_reason_code,
367 task,
368 error_flag,
369 End_Customer_Id,
370 End_Customer_Contact_Id,
371 End_Customer_Site_Use_Id,
372 End_Customer_Name,
373 End_Customer_Number,
374 End_Customer_Contact,
375 End_Customer_Address1,
376 End_Customer_Address2,
377 End_Customer_Address3,
378 End_Customer_Address4,
379 End_Customer_City,
380 End_Customer_State,
381 End_Customer_Postal_Code,
382 End_Customer_Country
383 From Oe_Line_Acks
384 Where Header_id = p_header_id
385 And Acknowledgment_Flag Is Null
386 For Update;
387
388 l_debug_level Constant Number := Oe_Debug_Pub.g_debug_level;
389 l_return_status Varchar2(1) := FND_API.g_ret_sts_success;
390
391 l_sold_to_site_use_id Number;
392 l_sold_to_location Varchar2(40);
393 l_invoice_to_location Varchar2(40);
394 l_ship_from_location Varchar2(20);
395 l_ship_from_address_4 Varchar2(20);
396 l_ship_from_state Varchar2(20);
397 l_ship_from_org_id Number;
398
399 l_header_acks_rec Oe_Header_Acks%ROWTYPE;
400 l_line_acks_rec OE_Update_Ack_Util.Line_Rec_Type;
401 l_sold_to_org_id Number;
402 l_header_end_customer_location Varchar2(40);
403 l_line_end_customer_location Varchar2(40);
404
405 Begin
406
407 If l_debug_level > 0 Then
408 Oe_Debug_Pub.Add('Entering OE_Update_Ack_Util.write_ack_data_values');
409 Oe_Debug_Pub.Add('Header_id: ||p_header_id');
410 End If;
411
412 Open Header_Acks_Cur;
413 Fetch Header_Acks_Cur
414 Into l_header_acks_rec.header_id,
415 l_header_acks_rec.org_id,
416 l_header_acks_rec.sold_to_org_id,
417 l_header_acks_rec.ship_to_org_id,
418 l_header_acks_rec.invoice_to_org_id,
419 l_header_acks_rec.sold_to_contact_id,
420 l_header_acks_rec.ship_to_contact_id,
421 l_header_acks_rec.ship_from_org_id,
422 l_header_acks_rec.order_type_id,
423 l_header_acks_rec.price_list_id,
424 l_header_acks_rec.payment_term_id,
425 l_header_acks_rec.salesrep_id,
426 l_header_acks_rec.fob_point_code,
427 l_header_acks_rec.freight_terms_code,
428 l_header_acks_rec.agreement_id,
429 l_header_acks_rec.conversion_type_code,
430 l_header_acks_rec.tax_exempt_reason_code,
431 l_header_acks_rec.tax_point_code,
432 l_header_acks_rec.invoicing_rule_id,
433 l_header_acks_rec.End_Customer_Id,
434 l_header_acks_rec.End_Customer_Contact_Id,
435 l_header_acks_rec.End_Customer_Site_Use_Id;
436
437 If Header_Acks_Cur%NOTFOUND Then
438 Close Header_Acks_Cur;
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 Return;
441 End If;
442
443 -- Set the Org
444 --dbms_application_info.set_client_info(l_header_acks_rec.org_id);
445 mo_global.set_policy_context('S',l_header_acks_rec.org_id); --MOAC changes
446 l_sold_to_org_id := l_header_acks_rec.sold_to_org_id;
447 -- Start updating values in the rec structure
448
449 -- Get Sold To Information
450 Begin
451 Select /* MOAC_SQL_CHANGE*/ b.site_use_id
452 Into l_sold_to_site_use_id
453 From hz_cust_acct_sites a, hz_cust_site_uses_all b
454 Where a.cust_acct_site_id = b.cust_acct_site_id
455 And a.cust_account_id = l_header_acks_rec.sold_to_org_id
456 And b.org_id = a.org_id
457 And b.site_use_code = 'SOLD_TO'
458 And b.primary_flag = 'Y'
459 And b.status = 'A';
460
461 Oe_Xml_Process_Util.Get_Address_details
462 (p_site_use_id => l_sold_to_site_use_id,
463 p_site_use_code => 'SOLD_TO',
464 x_location => l_sold_to_location,
465 x_address1 => l_header_acks_rec.sold_to_address1,
466 x_address2 => l_header_acks_rec.sold_to_address2,
467 x_address3 => l_header_acks_rec.sold_to_address3,
468 x_address4 => l_header_acks_rec.sold_to_address4,
469 x_city => l_header_acks_rec.sold_to_city,
470 x_state => l_header_acks_rec.sold_to_state,
471 x_country => l_header_acks_rec.sold_to_country,
472 x_postal_code => l_header_acks_rec.sold_to_postal_code,
473 x_edi_location_code => l_header_acks_rec.sold_to_edi_location_code,
474 x_customer_name => l_header_acks_rec.sold_to_org,
475 x_return_status => l_return_status);
476
477 If l_header_acks_rec.sold_to_contact_id Is Not Null Then
478 Oe_Xml_Process_Util.Get_Contact_Details
479 (p_contact_id => l_header_acks_rec.sold_to_contact_id,
480 p_cust_acct_id => l_header_acks_rec.sold_to_org_id,
481 x_first_name => l_header_acks_rec.sold_to_contact_first_name,
482 x_last_name => l_header_acks_rec.sold_to_contact_last_name,
483 x_return_status => l_return_status);
484 End If;
485
486 Exception
487 When Others Then
488 If l_debug_level > 0 Then
489 Oe_Debug_Pub.Add('Error in getting sold to info '||sqlerrm);
490 End If;
491 End;
492
493 -- Get Ship To info
494 Begin
495 If l_header_acks_rec.ship_to_org_id Is Not Null Then
496 Oe_Xml_Process_Util.Get_Address_details
497 (p_site_use_id => l_header_acks_rec.ship_to_org_id,
498 p_site_use_code => 'SHIP_TO',
499 x_location => l_header_acks_rec.ship_to_address_code,
500 x_address1 => l_header_acks_rec.ship_to_address_1,
501 x_address2 => l_header_acks_rec.ship_to_address_2,
502 x_address3 => l_header_acks_rec.ship_to_address_3,
503 x_address4 => l_header_acks_rec.ship_to_address_4,
504 x_city => l_header_acks_rec.ship_to_city,
505 x_state => l_header_acks_rec.ship_to_state,
506 x_country => l_header_acks_rec.ship_to_country,
507 x_postal_code => l_header_acks_rec.ship_to_postal_code,
508 x_edi_location_code => l_header_acks_rec.ship_to_edi_location_code,
509 x_customer_name => l_header_acks_rec.ship_to_org,
510 x_return_status => l_return_status);
511 End If;
512 If l_header_acks_rec.ship_to_contact_id Is Not Null Then
513 Oe_Xml_Process_Util.Get_Contact_Details
514 (p_contact_id => l_header_acks_rec.ship_to_contact_id,
515 p_cust_acct_id => l_header_acks_rec.sold_to_org_id,
516 x_first_name => l_header_acks_rec.ship_to_contact_first_name,
517 x_last_name => l_header_acks_rec.ship_to_contact_last_name,
518 x_return_status => l_return_status);
519 End If;
520
521 Exception
522 When Others Then
523 If l_debug_level > 0 Then
524 Oe_Debug_Pub.Add('Error in getting ship to info '||sqlerrm);
525 End If;
526 End;
527
528 -- Get Invoice To info
529 Begin
530 If l_header_acks_rec.invoice_to_org_id Is Not Null Then
531 Oe_Xml_Process_Util.Get_Address_details
532 (p_site_use_id => l_header_acks_rec.invoice_to_org_id,
533 p_site_use_code => 'BILL_TO',
534 x_location => l_invoice_to_location,
535 x_address1 => l_header_acks_rec.invoice_address_1,
536 x_address2 => l_header_acks_rec.invoice_address_2,
537 x_address3 => l_header_acks_rec.invoice_address_3,
538 x_address4 => l_header_acks_rec.invoice_address_4,
539 x_city => l_header_acks_rec.invoice_city,
540 x_state => l_header_acks_rec.invoice_state,
541 x_country => l_header_acks_rec.invoice_country,
542 x_postal_code => l_header_acks_rec.invoice_postal_code,
543 x_edi_location_code => l_header_acks_rec.bill_to_edi_location_code,
544 x_customer_name => l_header_acks_rec.invoice_to_org,
545 x_return_status => l_return_status);
546 End If;
547 If l_header_acks_rec.invoice_to_contact_id Is Not Null Then
548 Oe_Xml_Process_Util.Get_Contact_Details
549 (p_contact_id => l_header_acks_rec.invoice_to_contact_id,
550 p_cust_acct_id => l_header_acks_rec.sold_to_org_id,
551 x_first_name => l_header_acks_rec.invoice_to_contact_first_name,
552 x_last_name => l_header_acks_rec.invoice_to_contact_last_name,
553 x_return_status => l_return_status);
554 End If;
555 Exception
556 When Others Then
557 If l_debug_level > 0 Then
558 Oe_Debug_Pub.Add('Error in getting invoice to info '||sqlerrm);
559 End If;
560 End;
561
562 -- Get Ship From info
563 Begin
564 If l_header_acks_rec.ship_from_org_id Is Not Null Then
565 Oe_Xml_Process_Util.Get_Address_details
566 (p_site_use_id => l_header_acks_rec.ship_from_org_id,
567 p_site_use_code => 'SHIP_FROM',
568 x_location => l_ship_from_location,
569 x_address1 => l_header_acks_rec.ship_from_address_1,
570 x_address2 => l_header_acks_rec.ship_from_address_2,
571 x_address3 => l_header_acks_rec.ship_from_address_3,
572 x_address4 => l_ship_from_address_4,
573 x_city => l_header_acks_rec.ship_from_city,
574 x_state => l_ship_from_state,
575 x_country => l_header_acks_rec.ship_from_country,
576 x_postal_code => l_header_acks_rec.ship_from_postal_code,
577 x_edi_location_code => l_header_acks_rec.ship_from_edi_location_code,
578 x_customer_name => l_header_acks_rec.ship_from_org,
579 x_return_status => l_return_status);
580 End If;
581 Exception
582 When Others Then
583 If l_debug_level > 0 Then
584 Oe_Debug_Pub.Add('Error in getting Ship From info '||sqlerrm);
585 End If;
586 End;
587
588 -- Get Order Type, Price List, Salesrep, FOB, tax
589 -- agreement, freight terms
590 Begin
591
592 If l_header_acks_rec.order_type_id Is Not Null Then
593 l_header_acks_rec.order_type := Oe_Id_To_Value.Order_Type
594 (p_order_type_id => l_header_acks_rec.order_type_id);
595 End If;
596 If l_header_acks_rec.price_list_id Is Not Null Then
597 l_header_acks_rec.price_list := Oe_Id_To_Value.Price_List
598 (p_price_list_id => l_header_acks_rec.price_list_id);
599 End If;
600 If l_header_acks_rec.payment_term_id Is Not Null Then
601 l_header_acks_rec.payment_term := Oe_Id_To_Value.Payment_Term
602 (p_payment_term_id => l_header_acks_rec.payment_term_id);
603 End If;
604 If l_header_acks_rec.salesrep_id Is Not Null Then
605 l_header_acks_rec.salesrep := Oe_Id_To_Value.Salesrep
606 (p_salesrep_id => l_header_acks_rec.salesrep_id);
607 End If;
608 If l_header_acks_rec.fob_point_code Is Not Null Then
609 l_header_acks_rec.fob_point := Oe_Id_To_Value.Fob_Point
610 (p_fob_point_code => l_header_acks_rec.fob_point_code);
611 End If;
612 If l_header_acks_rec.freight_terms_code Is Not Null Then
613 l_header_acks_rec.freight_terms := Oe_Id_To_Value.Freight_Terms
614 (p_freight_terms_code => l_header_acks_rec.freight_terms_code);
615 End If;
616 If l_header_acks_rec.agreement_id Is Not Null Then
617 l_header_acks_rec.agreement := Oe_Id_To_Value.Agreement
618 (p_agreement_id => l_header_acks_rec.agreement_id);
619 End If;
620 If l_header_acks_rec.conversion_type_code Is Not Null Then
621 l_header_acks_rec.conversion_type := Oe_Id_To_Value.Conversion_Type
622 (p_conversion_type_code => l_header_acks_rec.conversion_type_code);
623 End If;
624 If l_header_acks_rec.Tax_Exempt_Reason_code Is Not Null Then
625 l_header_acks_rec.tax_exempt_reason := Oe_Id_To_Value.Tax_Exempt_Reason
626 (p_tax_exempt_reason_code => l_header_acks_rec.tax_exempt_reason_code);
627 End If;
628 If l_header_acks_rec.tax_point_code Is Not Null Then
629 l_header_acks_rec.tax_point := Oe_Id_To_Value.Tax_Point
630 (p_tax_point_code => l_header_acks_rec.tax_point_code);
631 End If;
632 If l_header_acks_rec.invoicing_rule_id Is Not Null Then
633 l_header_acks_rec.invoicing_rule := Oe_Id_To_Value.Invoicing_Rule
634 (p_invoicing_rule_id => l_header_acks_rec.invoicing_rule_id);
635 End If;
636 --added for bug 40344441 start
637 If l_debug_level > 0 Then
638 Oe_Debug_Pub.Add('Before end customer derivation for header1');
639 End If;
640
641 If l_header_acks_rec.end_customer_id Is Not Null Then
642 Oe_Id_To_Value.End_Customer( p_end_customer_id => l_header_acks_rec.end_customer_id
643 , x_end_customer_name => l_header_acks_rec.end_customer_name
644 , x_end_customer_number => l_header_acks_rec.end_customer_number
645 );
646 End If;
647
648
649
650
651 If l_debug_level > 0 Then
652 Oe_Debug_Pub.Add('Before end customer derivation for header2');
653 End If;
654
655 If l_header_acks_rec.end_customer_contact_id Is Not Null Then
656 l_header_acks_rec.end_customer_contact := Oe_Id_To_Value.End_Customer_Contact(p_end_customer_contact_id => l_header_acks_rec.end_customer_contact_id);
657 End If;
658
659
660
661
662 If l_debug_level > 0 Then
663 Oe_Debug_Pub.Add('Before end customer derivation for header3');
664 End If;
665
666
667
668 If l_header_acks_rec.end_customer_site_use_id Is Not Null Then
669 OE_ID_TO_VALUE.End_Customer_Site_Use( p_end_customer_site_use_id => l_header_acks_rec.end_customer_site_use_id
670 , x_end_customer_address1 => l_header_acks_rec.end_customer_address1
671 , x_end_customer_address2 => l_header_acks_rec.end_customer_address2
672 , x_end_customer_address3 => l_header_acks_rec.end_customer_address3
673 , x_end_customer_address4 => l_header_acks_rec.end_customer_address4
674 , x_end_customer_location => l_header_end_customer_location
675 , x_end_customer_city => l_header_acks_rec.end_customer_city
676 , x_end_customer_state => l_header_acks_rec.end_customer_state
677 , x_end_customer_postal_code => l_header_acks_rec.end_customer_postal_code
678 , x_end_customer_country => l_header_acks_rec.end_customer_country
679 );
680 End If;
681
682 --added for bug 40344441 end
683 -- start of changes for bug 4489065
684
685 If l_debug_level > 0 Then
686 Oe_Debug_Pub.Add('Before ship to customer name derviation for header');
687 End If;
688
689 If l_header_acks_rec.ship_to_org_id Is Not Null Then
690 OE_ID_TO_VALUE.Ship_To_Customer_Name(p_ship_to_org_id => l_header_acks_rec.ship_to_org_id,
691 x_ship_to_customer_name => l_header_acks_rec.ship_to_customer);
692 End If;
693
694 If l_debug_level > 0 Then
695 Oe_Debug_Pub.Add('Before invoice to customer name derviation for header');
696 End If;
697
698 If l_header_acks_rec.invoice_to_org_id Is Not Null Then
699 OE_ID_TO_VALUE.Invoice_To_Customer_Name(p_invoice_to_org_id => l_header_acks_rec.invoice_to_org_id,
700 x_invoice_to_customer_name => l_header_acks_rec.invoice_customer);
701
702 End If;
703 -- end of changes for bug 4489065
704
705
706
707 Exception
708
709 When Others Then
710 If l_debug_level > 0 Then
711 Oe_Debug_Pub.Add('Error in getting other ids '||sqlerrm);
712 End If;
713
714 End;
715
716 Close Header_Acks_Cur;
717
718
719 -- Derive Ack Line Data
720 Open Line_Acks_Cur;
721 Fetch Line_Acks_Cur Bulk Collect
722 Into l_line_acks_rec.line_id,
723 l_line_acks_rec.ship_to_org_id,
724 l_line_acks_rec.invoice_to_org_id,
725 l_line_acks_rec.invoice_to_contact_id,
726 l_line_acks_rec.ship_from_org_id,
727 l_line_acks_rec.agreement_id,
728 l_line_acks_rec.price_list_id,
729 l_line_acks_rec.arrival_set_id,
730 l_line_acks_rec.accounting_rule_id,
731 l_line_acks_rec.fob_point_code,
732 l_line_acks_rec.freight_terms_code,
733 l_line_acks_rec.fulfillment_set_id,
734 l_line_acks_rec.inventory_item_id,
735 l_line_acks_rec.invoice_set_id,
736 l_line_acks_rec.invoicing_rule_id,
737 l_line_acks_rec.line_type_id,
738 l_line_acks_rec.order_source_id,
739 l_line_acks_rec.payment_term_id,
740 l_line_acks_rec.project_id,
741 l_line_acks_rec.salesrep_id,
742 l_line_acks_rec.ship_set_id,
743 l_line_acks_rec.ship_to_contact_id,
744 l_line_acks_rec.shipping_method_code,
745 l_line_acks_rec.task_id,
746 l_line_acks_rec.tax_code,
747 l_line_acks_rec.tax_exempt_reason_code,
748 l_line_acks_rec.tax_point_code,
749 l_line_acks_rec.line_type,
750 l_line_acks_rec.ship_to_address1,
751 l_line_acks_rec.ship_to_address2,
752 l_line_acks_rec.ship_to_address3,
753 l_line_acks_rec.ship_to_address4,
754 l_line_acks_rec.ship_to_country,
755 l_line_acks_rec.ship_to_state,
756 l_line_acks_rec.ship_to_postal_code,
757 l_line_acks_rec.ship_to_city,
758 l_line_acks_rec.ship_to_address_code,
759 l_line_acks_rec.ship_to_edi_location_code,
760 l_line_acks_rec.ship_to_org,
761 l_line_acks_rec.ship_from_address_1,
762 l_line_acks_rec.ship_from_address_2,
763 l_line_acks_rec.ship_from_address_3,
764 l_line_acks_rec.ship_from_city,
765 l_line_acks_rec.ship_from_postal_code,
766 l_line_acks_rec.ship_from_country,
767 l_line_acks_rec.ship_from_org,
768 l_line_acks_rec.ship_from_edi_location_code,
769 l_line_acks_rec.invoice_to_org,
770 l_line_acks_rec.invoice_city,
771 l_line_acks_rec.invoice_address_code,
772 l_line_acks_rec.agreement,
773 l_line_acks_rec.price_list,
774 l_line_acks_rec.arrival_set_name,
775 l_line_acks_rec.accounting_rule,
776 l_line_acks_rec.fob_point,
777 l_line_acks_rec.freight_terms,
778 l_line_acks_rec.fulfillment_set_name,
779 l_line_acks_rec.inventory_item,
780 l_line_acks_rec.invoice_set_name,
781 l_line_acks_rec.invoicing_rule,
782 l_line_acks_rec.payment_term,
783 l_line_acks_rec.project,
784 l_line_acks_rec.salesrep,
785 l_line_acks_rec.ship_set_name,
786 l_line_acks_rec.ship_to_contact,
787 l_line_acks_rec.ship_to_contact_first_name,
788 l_line_acks_rec.ship_to_contact_last_name,
789 l_line_acks_rec.shipping_method,
790 l_line_acks_rec.fob_point_code,
791 l_line_acks_rec.freight_terms_code,
792 l_line_acks_rec.shipping_method_code,
793 l_line_acks_rec.tax_code,
794 l_line_acks_rec.tax_point_code,
795 l_line_acks_rec.tax_exempt_reason_code,
796 l_line_acks_rec.task,
797 l_line_acks_rec.error_flag,
798 l_line_acks_rec.End_Customer_Id,
799 l_line_acks_rec.End_Customer_Contact_Id,
800 l_line_acks_rec.End_Customer_Site_Use_Id,
801 l_line_acks_rec.End_Customer_Name,
802 l_line_acks_rec.End_Customer_Number,
803 l_line_acks_rec.End_Customer_Contact,
804 l_line_acks_rec.End_Customer_Address1,
805 l_line_acks_rec.End_Customer_Address2,
806 l_line_acks_rec.End_Customer_Address3,
807 l_line_acks_rec.End_Customer_Address4,
808 l_line_acks_rec.End_Customer_City,
809 l_line_acks_rec.End_Customer_State,
810 l_line_acks_rec.End_Customer_Postal_Code,
811 l_line_acks_rec.End_Customer_Country;
812
813
814 Close Line_Acks_Cur;
815
816 If l_line_acks_rec.line_id.count > 0 Then
817
818 For i In l_line_acks_rec.line_id.First..l_line_acks_rec.line_id.Last Loop
819 If nvl(l_line_acks_rec.error_flag(i),'N') = 'N' Then
820 l_line_acks_rec.line_type(i) := OE_Id_To_Value.Line_Type
821 (p_line_type_id => l_line_acks_rec.line_type_id(i));
822
823 l_line_acks_rec.price_list(i) := OE_Id_To_Value.price_list
824 (p_price_list_id => l_line_acks_rec.price_list_id(i));
825
826 l_line_acks_rec.salesrep(i) := OE_Id_To_Value.salesrep
827 (p_salesrep_id => l_line_acks_rec.salesrep_id(i));
828
829 l_line_acks_rec.fob_point(i) := OE_Id_To_Value.Fob_Point
830 (p_Fob_Point_code => l_line_acks_rec.fob_point_code(i));
831
832 l_line_acks_rec.freight_terms(i) := OE_Id_To_Value.freight_terms
833 (p_freight_terms_code => l_line_acks_rec.freight_terms_code(i));
834
835 l_line_acks_rec.Agreement(i) := OE_Id_To_Value.Agreement
836 (p_agreement_id => l_line_acks_rec.agreement_id(i));
837
838 l_line_acks_rec.payment_term(i) := OE_Id_To_Value.payment_term
839 (p_payment_term_id => l_line_acks_rec.payment_term_id(i));
840
841 If l_debug_level > 0 Then
842 Oe_Debug_Pub.Add('Before end customer derivation for lines1');
843 End If;
844
845
846 Oe_Id_To_Value.End_Customer( p_end_customer_id => l_line_acks_rec.end_customer_id(i)
847 , x_end_customer_name => l_line_acks_rec.end_customer_name(i)
848 , x_end_customer_number => l_line_acks_rec.end_customer_number(i)
849 );
850
851
852 If l_debug_level > 0 Then
853 Oe_Debug_Pub.Add('Before end customer derivation for lines2');
854 End If;
855
856
857 l_line_acks_rec.end_customer_contact(i) := Oe_Id_To_Value.End_Customer_Contact(p_end_customer_contact_id => l_line_acks_rec.end_customer_contact_id(i));
858
859
860 If l_debug_level > 0 Then
861 Oe_Debug_Pub.Add('Before end customer derivation for lines3');
862 End If;
863
864 OE_ID_TO_VALUE.End_Customer_Site_Use( p_end_customer_site_use_id => l_line_acks_rec.end_customer_site_use_id(i)
865 , x_end_customer_address1 => l_line_acks_rec.end_customer_address1(i)
866 , x_end_customer_address2 => l_line_acks_rec.end_customer_address2(i)
867 , x_end_customer_address3 => l_line_acks_rec.end_customer_address3(i)
868 , x_end_customer_address4 => l_line_acks_rec.end_customer_address4(i)
869 , x_end_customer_location => l_line_end_customer_location
870 , x_end_customer_city => l_line_acks_rec.end_customer_city(i)
871 , x_end_customer_state => l_line_acks_rec.end_customer_state(i)
872 , x_end_customer_postal_code => l_line_acks_rec.end_customer_postal_code(i)
873 , x_end_customer_country => l_line_acks_rec.end_customer_country(i)
874 );
875
876 If l_debug_level > 0 Then
877 Oe_Debug_Pub.Add('after end customer derivation for lines');
878 End If;
879
880
881 -- Get address info for lines here
882
883 -- Get Ship To info
884 Begin
885 If l_line_acks_rec.ship_to_org_id(i) Is Not Null Then
886 Oe_Xml_Process_Util.Get_Address_details
887 (p_site_use_id => l_line_acks_rec.ship_to_org_id(i),
888 p_site_use_code => 'SHIP_TO',
889 x_location => l_line_acks_rec.ship_to_address_code(i),
890 x_address1 => l_line_acks_rec.ship_to_address1(i),
891 x_address2 => l_line_acks_rec.ship_to_address2(i),
892 x_address3 => l_line_acks_rec.ship_to_address3(i),
893 x_address4 => l_line_acks_rec.ship_to_address4(i),
894 x_city => l_line_acks_rec.ship_to_city(i),
895 x_state => l_line_acks_rec.ship_to_state(i),
896 x_country => l_line_acks_rec.ship_to_country(i),
897 x_postal_code => l_line_acks_rec.ship_to_postal_code(i),
898 x_edi_location_code => l_line_acks_rec.ship_to_edi_location_code(i),
899 x_customer_name => l_line_acks_rec.ship_to_org(i),
900 x_return_status => l_return_status);
901 End If;
902 If l_line_acks_rec.ship_to_contact_id(I) Is Not Null Then
903 Oe_Xml_Process_Util.Get_Contact_Details
904 (p_contact_id => l_line_acks_rec.ship_to_contact_id(i),
905 p_cust_acct_id => l_sold_to_org_id,
906 x_first_name => l_line_acks_rec.ship_to_contact_first_name(i),
907 x_last_name => l_line_acks_rec.ship_to_contact_last_name(i),
908 x_return_status => l_return_status);
909 End If;
910
911 Exception
912 When Others Then
913 If l_debug_level > 0 Then
914 Oe_Debug_Pub.Add('Error in getting ship to info line'||sqlerrm);
915 End If;
916 End;
917
918 /*
919 -- Get Invoice To info
920 Begin
921 If l_line_acks_rec.invoice_to_org_id(i) Is Not Null Then
922 Oe_Xml_Process_Util.Get_Address_details
923 (p_site_use_id => l_line_acks_rec.invoice_to_org_id(i),
924 p_site_use_code => 'BILL_TO',
925 x_location => l_line_acks_rec.invoice_address_code(i),
926 x_address1 => l_line_acks_rec.invoice_to_address1(i),
927 x_address2 => l_line_acks_rec.invoice_to_address2(i),
928 x_address3 => l_line_acks_rec.invoice_to_address3(i),
929 x_address4 => l_line_acks_rec.invoice_to_address4(i),
930 x_city => l_line_acks_rec.invoice_city(i),
931 x_state => l_line_acks_rec.invoice_to_state(i),
932 x_country => l_line_acks_rec.invoice_to_country(i),
933 x_postal_code => l_line_acks_rec.invoice_to_postal_code(i),
934 x_edi_location_code => l_line_acks_rec.bill_to_edi_location_code(i),
935 x_customer_name => l_line_acks_rec.invoice_to_org(i),
936 x_return_status => l_return_status);
937 End If;
938
939 Exception
940 When Others Then
941 If l_debug_level > 0 Then
942 Oe_Debug_Pub.Add('Error in getting invoice to info line'||sqlerrm);
943 End If;
944
945 End;
946 */
947
948 -- Get Ship From info
949 Begin
950 If l_line_acks_rec.ship_from_org_id Is Not Null Then
951 Oe_Xml_Process_Util.Get_Address_details
952 (p_site_use_id => l_line_acks_rec.ship_from_org_id(i),
953 p_site_use_code => 'SHIP_FROM',
954 x_location => l_ship_from_location,
955 x_address1 => l_line_acks_rec.ship_from_address_1(i),
956 x_address2 => l_line_acks_rec.ship_from_address_2(i),
957 x_address3 => l_line_acks_rec.ship_from_address_3(i),
958 x_address4 => l_ship_from_address_4,
959 x_city => l_line_acks_rec.ship_from_city(i),
960 x_state => l_ship_from_state,
961 x_country => l_line_acks_rec.ship_from_country(i),
962 x_postal_code => l_line_acks_rec.ship_from_postal_code(i),
963 x_edi_location_code => l_line_acks_rec.ship_from_edi_location_code(i),
964 x_customer_name => l_line_acks_rec.ship_from_org(i),
965 x_return_status => l_return_status);
966 End If;
967 Exception
968 When Others Then
969 If l_debug_level > 0 Then
970 Oe_Debug_Pub.Add('Error in getting Ship From info line'||sqlerrm);
971 End If;
972 End;
973
974 End If;
975 End Loop;
976
977 -- Insert Line Ack Records
978 FORALL i In l_line_acks_rec.line_id.First..l_line_acks_rec.line_id.last
979 Update Oe_Line_Acks
980 Set line_type = l_line_acks_rec.line_type(i),
981 price_list = l_line_acks_rec.price_list(i),
982 salesrep = l_line_acks_rec.salesrep(i),
983 fob_point = l_line_acks_rec.fob_point(i),
984 freight_terms = l_line_acks_rec.freight_terms(i),
985 Agreement = l_line_acks_rec.Agreement(i),
986 payment_term = l_line_acks_rec.payment_term(i),
987 ship_to_address1 = l_line_acks_rec.ship_to_address1(i),
988 ship_to_address2 = l_line_acks_rec.ship_to_address2(i),
989 ship_to_address3 = l_line_acks_rec.ship_to_address3(i),
990 ship_to_address4 = l_line_acks_rec.ship_to_address4(i),
991 ship_to_city = l_line_acks_rec.ship_to_city(i),
992 ship_to_state = l_line_acks_rec.ship_to_state(i),
993 ship_to_country = l_line_acks_rec.ship_to_country(i),
994 ship_to_postal_code = l_line_acks_rec.ship_to_postal_code(i),
995 ship_to_edi_location_code = l_line_acks_rec.ship_to_edi_location_code(i),
996 ship_to_address_code = l_line_acks_rec.ship_to_address_code(i),
997 ship_to_contact_first_name = l_line_acks_rec.ship_to_contact_first_name(i),
998 ship_to_contact_last_name = l_line_acks_rec.ship_to_contact_last_name(i),
999 invoice_to_org = l_line_acks_rec.invoice_to_org(i),
1000 invoice_city = l_line_acks_rec.invoice_city(i) ,
1001 end_customer_name = l_line_acks_rec.end_customer_name(i),
1002 end_customer_number = l_line_acks_rec.end_customer_number(i),
1003 end_customer_contact = l_line_acks_rec.end_customer_contact(i),
1004 end_customer_address1 = l_line_acks_rec.end_customer_address1(i),
1005 end_customer_address2 = l_line_acks_rec.end_customer_address2(i),
1006 end_customer_address3 = l_line_acks_rec.end_customer_address3(i),
1007 end_customer_address4 = l_line_acks_rec.end_customer_address4(i),
1008 end_customer_city = l_line_acks_rec.end_customer_city(i),
1009 end_customer_state = l_line_acks_rec.end_customer_state(i),
1010 end_customer_postal_code = l_line_acks_rec.end_customer_postal_code(i),
1011 end_customer_country = l_line_acks_rec.end_customer_country(i)
1012
1013 Where Header_Id = p_header_id
1014 And Acknowledgment_flag Is Null;
1015
1016 End If;
1017
1018 -- Update data in Acks table
1019 Update Oe_Header_Acks
1020 Set sold_to_address1 = l_header_acks_rec.sold_to_address1,
1021 sold_to_address2 = l_header_acks_rec.sold_to_address2,
1022 sold_to_address3 = l_header_acks_rec.sold_to_address3,
1023 sold_to_address4 = l_header_acks_rec.sold_to_address4,
1024 sold_to_city = l_header_acks_rec.sold_to_city,
1025 sold_to_state = l_header_acks_rec.sold_to_state,
1026 sold_to_country = l_header_acks_rec.sold_to_country,
1027 sold_to_postal_code = l_header_acks_rec.sold_to_postal_code,
1028 sold_to_edi_location_code = l_header_acks_rec.sold_to_edi_location_code,
1029 sold_to_org = l_header_acks_rec.sold_to_org,
1030 sold_to_contact_first_name = l_header_acks_rec.sold_to_contact_first_name,
1031 sold_to_contact_last_name = l_header_acks_rec.sold_to_contact_last_name,
1032 ship_to_address_code = l_header_acks_rec.ship_to_address_code,
1033 ship_to_address_1 = l_header_acks_rec.ship_to_address_1,
1034 ship_to_address_2 = l_header_acks_rec.ship_to_address_2,
1035 ship_to_address_3 = l_header_acks_rec.ship_to_address_3,
1036 ship_to_address_4 = l_header_acks_rec.ship_to_address_4,
1037 ship_to_city = l_header_acks_rec.ship_to_city,
1038 ship_to_state = l_header_acks_rec.ship_to_state,
1039 ship_to_country = l_header_acks_rec.ship_to_country,
1040 ship_to_postal_code = l_header_acks_rec.ship_to_postal_code,
1041 ship_to_edi_location_code = l_header_acks_rec.ship_to_edi_location_code,
1042 ship_to_org = l_header_acks_rec.ship_to_org,
1043 ship_to_contact_first_name = l_header_acks_rec.ship_to_contact_first_name,
1044 ship_to_contact_last_name = l_header_acks_rec.ship_to_contact_last_name,
1045 invoice_address_1 = l_header_acks_rec.invoice_address_1,
1046 invoice_address_2 = l_header_acks_rec.invoice_address_2,
1047 invoice_address_3 = l_header_acks_rec.invoice_address_3,
1048 invoice_address_4 = l_header_acks_rec.invoice_address_4,
1049 invoice_city = l_header_acks_rec.invoice_city,
1050 invoice_state = l_header_acks_rec.invoice_state,
1051 invoice_country = l_header_acks_rec.invoice_country,
1052 invoice_postal_code = l_header_acks_rec.invoice_postal_code,
1053 bill_to_edi_location_code = l_header_acks_rec.bill_to_edi_location_code,
1054 invoice_to_org = l_header_acks_rec.invoice_to_org,
1055 invoice_to_contact_first_name = l_header_acks_rec.invoice_to_contact_first_name,
1056 invoice_to_contact_last_name = l_header_acks_rec.invoice_to_contact_last_name,
1057 ship_from_address_1 = l_header_acks_rec.ship_from_address_1,
1058 ship_from_address_2 = l_header_acks_rec.ship_from_address_2,
1059 ship_from_address_3 = l_header_acks_rec.ship_from_address_3,
1060 ship_from_city = l_header_acks_rec.ship_from_city,
1061 ship_from_country = l_header_acks_rec.ship_from_country,
1062 ship_from_postal_code = l_header_acks_rec.ship_from_postal_code,
1063 ship_from_edi_location_code = l_header_acks_rec.ship_from_edi_location_code,
1064 ship_from_org = l_header_acks_rec.ship_from_org,
1065 order_type = l_header_acks_rec.order_type,
1066 price_list = l_header_acks_rec.price_list,
1067 payment_term = l_header_acks_rec.payment_term,
1068 salesrep = l_header_acks_rec.salesrep,
1069 fob_point = l_header_acks_rec.fob_point,
1070 freight_terms = l_header_acks_rec.freight_terms,
1071 agreement = l_header_acks_rec.agreement,
1072 conversion_type = l_header_acks_rec.conversion_type,
1073 tax_exempt_reason = l_header_acks_rec.tax_exempt_reason,
1074 tax_point = l_header_acks_rec.tax_point,
1075 invoicing_rule = l_header_acks_rec.invoicing_rule ,
1076 end_customer_name = l_header_acks_rec.end_customer_name,
1077 end_customer_number = l_header_acks_rec.end_customer_number,
1078 end_customer_contact = l_header_acks_rec.end_customer_contact,
1079 end_customer_address1 = l_header_acks_rec.end_customer_address1,
1080 end_customer_address2 = l_header_acks_rec.end_customer_address2,
1081 end_customer_address3 = l_header_acks_rec.end_customer_address3,
1082 end_customer_address4 = l_header_acks_rec.end_customer_address4,
1083 end_customer_city = l_header_acks_rec.end_customer_city,
1084 end_customer_state = l_header_acks_rec.end_customer_state,
1085 end_customer_postal_code = l_header_acks_rec.end_customer_postal_code,
1086 end_customer_country = l_header_acks_rec.end_customer_country,
1087 ship_to_customer = l_header_acks_rec.ship_to_customer,
1088 invoice_customer = l_header_acks_rec.invoice_customer
1089
1090 Where Header_Id = p_header_id
1091 And Acknowledgment_flag Is Null;
1092
1093
1094 x_return_status := FND_API.G_RET_STS_SUCCESS;
1095
1096 If l_debug_level > 0 Then
1097 Oe_Debug_Pub.Add('Exiting OE_Update_Ack_Util.write_ack_data_values');
1098 End If;
1099
1100 Exception
1101
1102 When Too_Many_Rows Then
1103 If l_debug_level > 0 Then
1104 Oe_Debug_Pub.Add('More than 1 ack header in Ack tables which is not acknowledged');
1105 End If;
1106
1107 When Others Then
1108 If l_debug_level > 0 Then
1109 Oe_Debug_Pub.Add('Others Exception in write_ack_data_values '||sqlerrm);
1110 End If;
1111
1112 x_return_status := FND_API.G_RET_STS_ERROR;
1113
1114 End write_ack_data_values;
1115
1116
1117 Procedure Derive_Ack_Values
1118 (p_itemtype In Varchar2,
1119 p_itemkey In Varchar2,
1120 p_actid In Number,
1121 p_funcmode In Varchar2,
1122 p_x_result In Out Nocopy Varchar2
1123 )
1124 Is
1125
1126 l_header_id Number;
1127 l_transaction_type Varchar2(4);
1128 l_transaction_subtype Varchar2(4);
1129 l_return_status Varchar2(1);
1130
1131 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1132 l_user_key Varchar2(240);
1133 l_orig_sys_document_ref Varchar2(50);
1134 l_change_sequence Varchar2(50);
1135 l_sold_to_org_id Number;
1136 l_order_number Number;
1137 l_order_source_id Number;
1138 l_org_id Number;
1139 l_xml_msg_id Number;
1140 l_order_type_id Number;
1141 l_message_text Varchar2(500);
1142
1143 Begin
1144
1145 If l_debug_level > 0 Then
1146 Oe_Debug_Pub.Add('In Derive_Ack_Values');
1147 End If;
1148
1149 Oe_Standard_Wf.Set_Msg_Context(p_actid);
1150
1151 l_header_id := wf_engine.GetItemAttrNumber
1152 (p_itemtype,
1153 p_itemkey,
1154 'HEADER_ID');
1155
1156 l_transaction_type := wf_engine.GetItemAttrText
1157 (p_itemtype,
1158 p_itemkey,
1159 'TRANSACTION_TYPE');
1160
1161 l_transaction_subtype := wf_engine.GetItemAttrText
1162 (p_itemtype,
1163 p_itemkey,
1164 'TRANSACTION_SUBTYPE');
1165
1166 l_orig_sys_document_ref := wf_engine.GetItemAttrText
1167 (p_itemtype,
1168 p_itemkey,
1169 'ORIG_SYS_DOCUMENT_REF');
1170
1171 l_change_sequence := wf_engine.GetItemAttrText
1172 (p_itemtype,
1173 p_itemkey,
1174 'CHANGE_SEQUENCE');
1175
1176 l_order_number := wf_engine.GetItemAttrNumber
1177 (p_itemtype,
1178 p_itemkey,
1179 'ORDER_NUMBER');
1180
1181 l_sold_to_org_id := wf_engine.GetItemAttrNumber
1182 (p_itemtype,
1183 p_itemkey,
1184 'SOLD_TO_ORG_ID');
1185
1186 l_xml_msg_id := wf_engine.GetItemAttrNumber
1187 (p_itemtype,
1188 p_itemkey,
1189 'XML_MESSAGE_ID');
1190
1191 l_order_type_id := wf_engine.GetItemAttrNumber
1192 (p_itemtype,
1193 p_itemkey,
1194 'ORDER_TYPE_ID');
1195
1196 l_org_id := wf_engine.GetItemAttrNumber
1197 (p_itemtype,
1198 p_itemkey,
1199 'ORG_ID');
1200
1201 l_order_source_id := wf_engine.GetItemAttrNumber
1202 (p_itemtype,
1203 p_itemkey,
1204 'ORDER_SOURCE_ID');
1205
1206 l_user_key := l_orig_sys_document_ref || ',' ||to_char(l_sold_to_org_id)||','||
1207 l_change_sequence||','||l_transaction_type;
1208
1209 -- start exception management
1210 OE_MSG_PUB.set_msg_context(
1211 p_entity_code => 'ELECMSG_'||p_itemtype
1212 ,p_entity_id => p_itemkey
1213 ,p_header_id => l_header_id
1214 ,p_line_id => null
1215 ,p_order_source_id => l_order_source_id
1216 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1217 ,p_orig_sys_document_line_ref => null
1218 ,p_orig_sys_shipment_ref => null
1219 ,p_change_sequence => l_change_sequence
1220 ,p_source_document_type_id => null
1221 ,p_source_document_id => null
1222 ,p_source_document_line_id => null );
1223 -- end exception management
1224
1225 wf_engine.SetItemUserKey(itemtype => 'OEXWFEDI',
1226 itemkey => p_itemkey,
1227 userkey => l_user_key);
1228
1229 If l_debug_level > 0 Then
1230 Oe_Debug_Pub.Add('Header Id = '||l_header_id);
1231 End If;
1232
1233 write_ack_data_values
1234 (p_header_id => l_header_id,
1235 p_transaction_type => l_transaction_type,
1236 x_return_status => l_return_status);
1237
1238 If l_return_status = FND_API.G_RET_STS_SUCCESS Then
1239 p_x_result := 'SUCCESS';
1240 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_TRIGGERED');
1241 fnd_message.set_token ('TRANSACTION', l_transaction_subtype);
1242 l_message_text := fnd_message.get;
1243 OE_Acknowledgment_Pub.Raise_Event_XMLInt (
1244 p_order_source_id => l_order_source_id,
1245 p_partner_document_num => l_orig_sys_document_ref,
1246 p_sold_to_org_id => l_sold_to_org_id,
1247 p_transaction_type => l_transaction_type,
1248 p_transaction_subtype => l_transaction_subtype,
1249 p_itemtype => 'OEXWFEDI',
1250 p_itemkey => p_itemkey,
1251 p_message_text => l_message_text,
1252 p_document_num => l_order_number,
1253 p_change_sequence => l_change_sequence,
1254 p_org_id => l_org_id,
1255 p_xmlg_document_id => l_xml_msg_id,
1256 p_order_type_id => l_order_type_id,
1257 p_doc_status => 'ACTIVE',
1258 p_header_id => l_header_id,
1259 p_processing_stage => 'OUTBOUND_SETUP',
1260 x_return_status => l_return_status);
1261 Else
1262 p_x_result := 'FAILURE';
1263 End If;
1264
1265 --OE_STANDARD_WF.Save_Messages;
1266 --OE_STANDARD_WF.Clear_Msg_Context;
1267
1268 If l_debug_level > 0 Then
1269 Oe_Debug_Pub.Add('End Derive_Ack_Values');
1270 End If;
1271
1272 Exception
1273
1274 When Others Then
1275 p_x_result := '#EXCEPTION';
1276 WF_CORE.Context('OE_UPDATE_ACK_UTIL', 'DERIVE_ACK_VALUES',
1277 p_itemtype, p_itemkey, p_actid, p_funcmode);
1278 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1279 p_itemtype => p_itemtype,
1280 p_itemkey => p_itemkey);
1281 OE_STANDARD_WF.Save_Messages;
1282 OE_STANDARD_WF.Clear_Msg_Context;
1283 RAISE;
1284
1285 End Derive_Ack_Values;
1286
1287 Procedure Raise_Derive_Ack_Data_event
1288 (p_transaction_type In Varchar2,
1289 p_header_id In Number,
1290 p_org_id In Number,
1291 p_orig_sys_document_ref In Varchar2,
1292 p_change_sequence In Varchar2,
1293 p_sold_to_org_id In Number,
1294 p_order_number In Number,
1295 p_xml_msg_id In Number,
1296 p_order_type_id In Number,
1297 p_order_source_id In Number,
1298 p_transaction_subtype In Varchar2,
1299 x_return_status Out Nocopy Varchar2
1300 )
1301 Is
1302
1303 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1304 l_event_name Varchar2(50);
1305 l_itemkey Number;
1306
1307 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1308 --Pragma AUTONOMOUS_TRANSACTION;
1309 l_return_status Varchar2(1);
1310
1311
1312 Begin
1313
1314 If l_debug_level > 0 Then
1315 Oe_Debug_Pub.Add('In Raise_Derive_Ack_Data_event');
1316 End If;
1317
1318 l_event_name := 'oracle.apps.ont.oi.edi_ack_values.create';
1319
1320 Select Oe_Xml_Message_Seq_S.nextval
1321 Into l_itemkey
1322 From dual;
1323
1324 wf_event.AddParameterToList(p_name=> 'TRANSACTION_TYPE',
1325 p_value=> p_transaction_type,
1326 p_parameterlist=> l_parameter_list);
1327
1328 wf_event.AddParameterToList(p_name=> 'TRANSACTION_SUBTYPE',
1329 p_value=> p_transaction_subtype,
1330 p_parameterlist=> l_parameter_list);
1331
1332 wf_event.AddParameterToList(p_name=> 'HEADER_ID',
1333 p_value=> p_header_id,
1334 p_parameterlist=> l_parameter_list);
1335
1336 wf_event.AddParameterToList(p_name=> 'ORG_ID',
1337 p_value=> p_org_id,
1338 p_parameterlist=> l_parameter_list);
1339
1340 wf_event.AddParameterToList(p_name=> 'ORIG_SYS_DOCUMENT_REF',
1341 p_value=> p_orig_sys_document_ref,
1342 p_parameterlist=> l_parameter_list);
1343
1344 wf_event.AddParameterToList(p_name=> 'CHANGE_SEQUENCE',
1345 p_value=> p_change_sequence,
1346 p_parameterlist=> l_parameter_list);
1347
1348 wf_event.AddParameterToList(p_name=> 'SOLD_TO_ORG_ID',
1349 p_value=> p_sold_to_org_id,
1350 p_parameterlist=> l_parameter_list);
1351
1352 wf_event.AddParameterToList(p_name=> 'ORDER_NUMBER',
1353 p_value=> p_order_number,
1354 p_parameterlist=> l_parameter_list);
1355
1356 wf_event.AddParameterToList(p_name=> 'XML_MESSAGE_ID',
1357 p_value=> p_xml_msg_id,
1358 p_parameterlist=> l_parameter_list);
1359
1360 wf_event.AddParameterToList(p_name=> 'ORDER_TYPE_ID',
1361 p_value=> p_order_type_id,
1362 p_parameterlist=> l_parameter_list);
1363
1364 wf_event.AddParameterToList(p_name=> 'ORDER_SOURCE_ID',
1365 p_value=> p_order_source_id,
1366 p_parameterlist=> l_parameter_list);
1367
1368 wf_event.raise( p_event_name => l_event_name,
1369 p_event_key => l_itemkey,
1370 p_parameters => l_parameter_list);
1371
1372
1373 l_parameter_list.DELETE;
1374 --Commit;
1375
1376 x_return_status := FND_API.G_RET_STS_SUCCESS;
1377
1378 /*
1379 OE_Acknowledgment_Pub.Raise_Event_XMLInt (
1380 p_order_source_id => p_order_source_id,
1381 p_partner_document_num => p_orig_sys_document_ref,
1382 p_sold_to_org_id => p_sold_to_org_id,
1383 p_transaction_type => p_transaction_type,
1384 p_transaction_subtype => p_transaction_subtype,
1385 p_itemtype => 'OEXWFEDI',
1386 p_itemkey => l_itemkey,
1387 p_message_text => 'Event to derive EDI Acknowledgment values raised successfully',
1388 p_document_num => p_order_number,
1389 p_change_sequence => p_change_sequence,
1390 p_org_id => p_org_id,
1391 p_xmlg_document_id => p_xml_msg_id,
1392 p_order_type_id => p_order_type_id,
1393 p_header_id => p_header_id,
1394 p_doc_status => 'ACTIVE',
1395 p_processing_stage => 'OUTBOUND_TRIGGERED',
1396 x_return_status => l_return_status);
1397 */
1398
1399 If l_debug_level > 0 Then
1400 Oe_Debug_Pub.Add('End Raise_Derive_Ack_Data_event');
1401 End If;
1402
1403 Exception
1404
1405 When Others Then
1406 x_return_status := FND_API.G_RET_STS_ERROR;
1407 If l_debug_level > 0 Then
1408 Oe_Debug_Pub.Add('Others exception in Raise_Derive_Ack_Data_event');
1409 Oe_Debug_Pub.Add('Error: '||sqlerrm);
1410 End If;
1411
1412 End Raise_Derive_Ack_Data_event;
1413
1414
1415 Procedure Oe_Edi_Selector
1416 ( p_itemtype in varchar2,
1417 p_itemkey in varchar2,
1418 p_actid in number,
1419 p_funcmode in varchar2,
1420 p_x_result in out Nocopy varchar2
1421 )
1422 Is
1423
1424 l_debug_level Constant Number := oe_debug_pub.g_debug_level;
1425 l_user_id Number ;
1426 l_resp_appl_id Number ;
1427 l_resp_id Number ;
1428 l_org_id Number;
1429 l_current_org_id Number;
1430 l_client_org_id Number;
1431
1432 Begin
1433
1434 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
1435
1436 If (p_funcmode = 'RUN') Then
1437 p_x_result := 'COMPLETE';
1438
1439 Elsif (p_funcmode = 'SET_CTX') Then
1440
1441 l_org_id := wf_engine.GetItemAttrNumber( p_itemtype
1442 , p_itemkey
1443 , 'ORG_ID'
1444 );
1445 If l_debug_level > 0 Then
1446 oe_debug_pub.add('l_org_id =>' || l_org_id);
1447 End If;
1448
1449 mo_global.set_policy_context(p_access_mode => 'S', p_org_id=>l_Org_Id);
1450 p_x_result := 'COMPLETE';
1451
1452 Elsif (p_funcmode = 'TEST_CTX') Then
1453 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
1454 l_org_id := wf_engine.GetItemAttrNumber( p_itemtype
1455 , p_itemkey
1456 , 'ORG_ID'
1457 );
1458 IF (NVL(mo_global.get_current_org_id,-99) <> l_Org_Id)
1459 THEN
1460 p_x_result := 'FALSE';
1461 ELSE
1462 p_x_result := 'TRUE';
1463 END IF;
1464
1465 End If;
1466
1467 Exception
1468 When Others Then
1469 Raise;
1470
1471 End Oe_Edi_Selector;
1472
1473
1474 END OE_Update_Ack_Util;