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