[Home] [Help]
PACKAGE BODY: APPS.OE_HEADER_ACK_UTIL
Source
1 PACKAGE BODY OE_Header_Ack_Util AS
2 /* $Header: OEXUHAKB.pls 120.4 2005/12/15 03:06:43 akyadav noship $ */
3
4
5 -- Global constant holding the package name
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_HEADER_ACK_UTIL';
8
9 -- { Start GET_ACK_CODE
10 FUNCTION GET_ACK_CODE (p_order_source_id NUMBER := 6,
11 p_reject_order VARCHAR2 := 'N',
12 p_transaction_type VARCHAR2 := NULL,
13 p_booked_flag VARCHAR2 := NULL,
14 p_header_id NUMBER DEFAULT NULL)
15 RETURN VARCHAR
16 IS
17 l_ack_code Varchar2(30);
18 l_hold_id NUMBER;
19 BEGIN
20 oe_debug_pub.add('Entering function GET_ACK_CODE');
21 oe_debug_pub.add('p_reject_order = '||p_reject_order);
22 oe_debug_pub.add('p_order_source_id = '||p_order_source_id);
23 If p_reject_order = 'N' Then
24 If p_order_source_id = 20 Then
25 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' THEN
26 If nvl(FND_PROFILE.VALUE('ONT_XML_ACCEPT_STATE'), 'ENTERED') = 'ENTERED' Then
27 Return '0';
28 Elsif nvl(p_booked_flag, 'N') = 'N' AND p_transaction_type = OE_Acknowledgment_Pub.G_TRANSACTION_POI Then
29 oe_debug_pub.add('return Pending');
30 Return '3'; -- pending
31 Else
32 oe_debug_pub.add('return Accepted - code is 0, post-110510');
33 Return '0';
34 End If;
35 Else
36 oe_debug_pub.add('return Accepted - code is 0, pre-110510');
37 Return '0';
38 End If;
39 Else
40 BEGIN
41 SELECT order_hold_id
42 INTO l_hold_id
43 FROM OE_ORDER_HOLDS
44 WHERE header_id = p_header_id
45 AND hold_release_id IS NULL ;
46 oe_debug_pub.add('Hold Applied on this order is :'|| l_hold_id, 3);
47
48 Return 'AH' ;
49
50 EXCEPTION
51 WHEN NO_DATA_FOUND
52 THEN
53 oe_debug_pub.add('No Holds found ', 3);
54 oe_debug_pub.add( 'Header id'||p_header_id);
55 Return 'AT' ;
56
57 WHEN TOO_MANY_ROWS
58 THEN
59 oe_debug_pub.add('Many Holds Applied on this order ', 3);
60 Return 'AH' ;
61 END ;
62 End If;
63 Elsif p_reject_order = 'Y' Then
64 If p_order_source_id = 20 Then
65 oe_debug_pub.add('return Rejected - code is 2');
66 Return '2';
67 Else
68 oe_debug_pub.add('return RJ');
69 Return 'RJ';
70 End If;
71 Else
72 oe_debug_pub.add('return Null in else');
73 Return Null;
74 End If;
75 END GET_ACK_CODE;
76 -- End GET_ACK_CODE }
77
78 PROCEDURE Insert_Row
79 ( p_header_rec IN OE_Order_Pub.Header_Rec_Type
80 , p_header_val_rec IN OE_Order_Pub.Header_Val_Rec_Type
81 , p_old_header_rec IN OE_Order_Pub.Header_Rec_Type
82 , p_old_header_val_rec IN OE_Order_Pub.Header_Val_Rec_Type
83 , p_reject_order IN VARCHAR2
84 , p_ack_type IN VARCHAR2 := NULL
85 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
86 )
87 IS
88 l_header_rec OE_Order_Pub.Header_Rec_Type;
89 l_header_val_rec OE_Order_Pub.Header_Val_Rec_Type;
90
91 l_count NUMBER;
92
93 BEGIN
94 IF p_reject_order = 'N' THEN
95 oe_debug_pub.add('before copying header acknowledgment reject N',3);
96 l_header_rec := p_header_rec;
97 l_header_val_rec := p_header_val_rec;
98 ELSE
99 oe_debug_pub.add('before copying header acknowledgment reject else',3);
100 l_header_rec := p_old_header_rec;
101 l_header_val_rec := p_old_header_val_rec;
102 END IF;
103
104 OE_Header_Util.Convert_Miss_To_Null (l_header_rec);
105
106 oe_debug_pub.add('First Ack Code is :'||l_header_rec.FIRST_ACK_CODE);
107 oe_debug_pub.add('First Ack Code is :'||p_header_rec.FIRST_ACK_CODE);
108 oe_debug_pub.add('Order Source Id :'|| l_header_rec.order_source_id);
109 oe_debug_pub.add('p_ack_type :'|| p_ack_type);
110 IF nvl(l_header_rec.FIRST_ACK_CODE, ' ') = ' ' THEN -- It is 855
111 -- Commented as part of 3A4 Change
112 -- l_header_rec.FIRST_ACK_CODE := 'AT'; -- RJ for rejected
113 If p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_POA then
114 l_header_rec.FIRST_ACK_CODE :=
115 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
116 p_reject_order => p_reject_order);
117 Elsif p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_CPO then
118 l_header_rec.FIRST_ACK_CODE :=
119 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
120 p_reject_order => p_reject_order);
121 -- 3A6 related
122 Elsif p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_SSO then
123 If l_header_rec.last_ack_code IS NULL Then
124 l_header_rec.FIRST_ACK_CODE := 'OPEN';
125 Else
126 l_header_rec.FIRST_ACK_CODE := l_header_rec.last_ack_code;
127 End if;
128 Elsif p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_CSO then
129 If l_header_rec.last_ack_code IS NULL Then
130 l_header_rec.FIRST_ACK_CODE := 'OPEN';
131 Else
132 l_header_rec.FIRST_ACK_CODE := l_header_rec.last_ack_code;
133 End if;
134 Elsif p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_POI then
135 l_header_rec.FIRST_ACK_CODE :=
136 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
137 p_reject_order => p_reject_order,
138 p_booked_flag => l_header_rec.booked_flag,
139 p_transaction_type => oe_acknowledgment_pub.G_TRANSACTION_POI
140 );
141 Elsif p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_CHO then
142 l_header_rec.FIRST_ACK_CODE :=
143 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
144 p_reject_order => p_reject_order);
145 Elsif l_header_rec.ORDER_SOURCE_ID <> 20 Then -- so that we still handle 855
146 l_header_rec.FIRST_ACK_CODE :=
147 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
148 p_reject_order => p_reject_order,
149 p_header_id => l_header_rec.header_id);
150 End if;
151 l_header_rec.FIRST_ACK_DATE := '';
152 l_header_rec.LAST_ACK_CODE := '';
153 l_header_rec.LAST_ACK_DATE := '';
154 ELSE
155 oe_debug_pub.add('trans is 865 :putting first_ack_date');
156 oe_debug_pub.add('First Ack Date :'|| to_char(p_old_header_rec.FIRST_ACK_DATE));
157 l_header_rec.FIRST_ACK_DATE := p_header_rec.FIRST_ACK_DATE;
158 -- Commented as part of 3A4 Change
159 -- l_header_rec.LAST_ACK_CODE := 'AT'; -- RJ for rejected
160 l_header_rec.LAST_ACK_CODE :=
161 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
162 p_reject_order => p_reject_order,
163 p_header_id => l_header_rec.header_id);
164 if l_header_rec.LAST_ACK_CODE = 'AT' then
165 l_header_rec.LAST_ACK_CODE := 'AC'; -- for 865, header ack code for accept should be
166 -- AC i.e. Acknowledge - With Detail and Changes
167 end if;
168 l_header_rec.LAST_ACK_DATE := '';
169 END IF;
170
171 BEGIN
172
173
174 if p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_SSO
175 OR p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_CSO
176 then
177
178 SELECT count(*) INTO l_count
179 FROM OE_HEADER_ACKS
180 WHERE header_id = l_header_rec.header_id
181 AND acknowledgment_flag Is Null
182 -- Change this condition once a type is inserted for POAO/POCAO
183 AND nvl(acknowledgment_type,'ALL') = nvl(p_ack_type,'ALL')
184 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
185 = nvl(l_header_rec.sold_to_org_id, FND_API.G_MISS_NUM)
186 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
187 = nvl(l_header_val_rec.sold_to_org, FND_API.G_MISS_CHAR)
188 AND nvl(change_sequence, FND_API.G_MISS_CHAR)
189 = nvl(l_header_rec.change_sequence, FND_API.G_MISS_CHAR)
190 AND request_id = l_header_rec.request_id;
191 else
192 SELECT count(*) INTO l_count
193 FROM OE_HEADER_ACKS
194 WHERE header_id = l_header_rec.header_id
195 AND acknowledgment_flag Is Null
196 -- Change this condition once a type is inserted for POAO/POCAO
197 AND nvl(acknowledgment_type,'ALL') = nvl(p_ack_type,'ALL')
198 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
199 = nvl(l_header_rec.sold_to_org_id, FND_API.G_MISS_NUM)
200 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
201 = nvl(l_header_val_rec.sold_to_org, FND_API.G_MISS_CHAR)
202 AND nvl(change_sequence, FND_API.G_MISS_CHAR)
203 = nvl(l_header_rec.change_sequence, FND_API.G_MISS_CHAR);
204
205 end if;
206 IF l_count > 0 THEN
207 OE_Header_Ack_Util.Delete_Row (p_header_id => l_header_rec.header_id,
208 p_ack_type => p_ack_type,
209 p_sold_to_org_id => l_header_rec.sold_to_org_id,
210 p_sold_to_org => l_header_val_rec.sold_to_org,
211 p_change_sequence => l_header_rec.change_sequence,
212 p_request_id => l_header_rec.request_id);
213 oe_debug_pub.add('Count is > 0, Calling Delete Row',3);
214 oe_debug_pub.add('Count is ' || l_count);
215 ELSE
216 oe_debug_pub.add('Count <= 0 for header_id, attempting delete by doc ref');
217 OE_Header_Ack_Util.Delete_Row (p_header_id => NULL,
218 p_ack_type => p_ack_type,
219
220 p_orig_sys_document_ref => l_header_rec.orig_sys_document_ref,
221 p_sold_to_org_id => l_header_rec.sold_to_org_id,
222 p_sold_to_org => l_header_val_rec.sold_to_org,
223 p_change_sequence => l_header_rec.change_sequence,
224 p_request_id => l_header_rec.request_id
225 );
226 END IF;
227
228
229 EXCEPTION WHEN OTHERS THEN
230 oe_debug_pub.add('Others exception is select from o_header_acks',3);
231 END;
232
233
234 oe_debug_pub.add('Source = '||l_header_rec.order_source_id);
235 oe_debug_pub.add('Ref = '||l_header_rec.orig_sys_document_ref);
236 oe_debug_pub.add('Headerid = '||l_header_rec.header_id);
237 oe_debug_pub.add('First Ack Code = '|| l_header_rec.first_ack_code,1);
238 IF ( l_header_rec.order_source_id <> FND_API.G_MISS_NUM
239 AND nvl(l_header_rec.order_source_id,0) <> 0
240 AND l_header_rec.orig_sys_document_ref <> FND_API.G_MISS_CHAR
241 AND nvl(l_header_rec.orig_sys_document_ref,' ') <> ' ')
242
243 OR (l_header_rec.header_id <> FND_API.G_MISS_NUM
244 AND nvl(l_header_rec.header_id,0) <> 0)
245 THEN
246 oe_debug_pub.add('inserting header ack record for'||
247 ' source id: ' ||to_char(l_header_rec.order_source_id)||
248 ', ref: ' ||l_header_rec.orig_sys_document_ref||
249 ', header id: '||to_char(l_header_rec.header_id));
250
251 /* Added substr for some of the values being inserted in the table. This is done to fix the bug 2237470 */
252 /*Bug2403389 : Reverted the changes made for Bug2237470 */
253 --Added end customer fields for bug 4034441
254 INSERT INTO OE_HEADER_ACKS
255 (ACCOUNTING_RULE
256 ,ACCOUNTING_RULE_ID
257 ,ACCOUNTING_RULE_DURATION
258 ,ACKNOWLEDGMENT_FLAG
259 ,AGREEMENT
260 ,AGREEMENT_ID
261 ,AGREEMENT_NAME
262 ,ATTRIBUTE1
263 ,ATTRIBUTE2
264 ,ATTRIBUTE3
265 ,ATTRIBUTE4
266 ,ATTRIBUTE5
267 ,ATTRIBUTE6
268 ,ATTRIBUTE7
269 ,ATTRIBUTE8
270 ,ATTRIBUTE9
271 ,ATTRIBUTE10
272 ,ATTRIBUTE11
273 ,ATTRIBUTE12
274 ,ATTRIBUTE13
275 ,ATTRIBUTE14
276 ,ATTRIBUTE15
277 ,ATTRIBUTE16 --For bug 2184255
278 ,ATTRIBUTE17
279 ,ATTRIBUTE18
280 ,ATTRIBUTE19
281 ,ATTRIBUTE20
282 ,FIRST_ACK_CODE
283 ,LAST_ACK_CODE
284 ,FIRST_ACK_DATE
285 ,LAST_ACK_DATE
286 ,BUYER_SELLER_FLAG
287 ,BOOKED_FLAG
288 ,CANCELLED_FLAG
289 -- ,CLOSED_FLAG
290 ,CHANGE_DATE
291 ,CHANGE_SEQUENCE
292 ,CONTEXT
293 ,CONVERSION_RATE
294 ,CONVERSION_RATE_DATE
295 ,CONVERSION_TYPE
296 ,CONVERSION_TYPE_CODE
297 ,CREATED_BY
298 ,CREATION_DATE
299 ,CUST_PO_NUMBER
300 -- ,CUSTOMER_ID
301 ,CUSTOMER_NAME
302 ,CUSTOMER_NUMBER
303 -- ,DELIVER_TO_CONTACT
304 ,DELIVER_TO_CONTACT_ID
305 ,DELIVER_TO_CUSTOMER
306 ,DELIVER_TO_CUSTOMER_NUMBER
307 -- ,DELIVER_TO_ORG
308 ,DELIVER_TO_ORG_ID
309 -- ,DEMAND_CLASS
310 ,DEMAND_CLASS_CODE
311 ,EARLIEST_SCHEDULE_LIMIT
312 -- ,ERROR_FLAG
313 ,EXPIRATION_DATE
314 ,FOB_POINT
315 ,FOB_POINT_CODE
316 ,FREIGHT_CARRIER_CODE
317 ,FREIGHT_TERMS
318 ,FREIGHT_TERMS_CODE
319 ,GLOBAL_ATTRIBUTE_CATEGORY
320 ,GLOBAL_ATTRIBUTE1
321 ,GLOBAL_ATTRIBUTE10
322 ,GLOBAL_ATTRIBUTE11
323 ,GLOBAL_ATTRIBUTE12
324 ,GLOBAL_ATTRIBUTE13
325 ,GLOBAL_ATTRIBUTE14
326 ,GLOBAL_ATTRIBUTE15
327 ,GLOBAL_ATTRIBUTE16
328 ,GLOBAL_ATTRIBUTE17
329 ,GLOBAL_ATTRIBUTE18
330 ,GLOBAL_ATTRIBUTE19
331 ,GLOBAL_ATTRIBUTE2
332 ,GLOBAL_ATTRIBUTE20
333 ,GLOBAL_ATTRIBUTE3
334 ,GLOBAL_ATTRIBUTE4
335 ,GLOBAL_ATTRIBUTE5
336 ,GLOBAL_ATTRIBUTE6
337 ,GLOBAL_ATTRIBUTE7
338 ,GLOBAL_ATTRIBUTE8
339 ,GLOBAL_ATTRIBUTE9
340 ,TP_CONTEXT
341 ,TP_ATTRIBUTE1
342 ,TP_ATTRIBUTE2
343 ,TP_ATTRIBUTE3
344 ,TP_ATTRIBUTE4
345 ,TP_ATTRIBUTE5
346 ,TP_ATTRIBUTE6
347 ,TP_ATTRIBUTE7
348 ,TP_ATTRIBUTE8
349 ,TP_ATTRIBUTE9
350 ,TP_ATTRIBUTE10
351 ,TP_ATTRIBUTE11
352 ,TP_ATTRIBUTE12
353 ,TP_ATTRIBUTE13
354 ,TP_ATTRIBUTE14
355 ,TP_ATTRIBUTE15
356 ,HEADER_ID
357 -- ,HEADER_PO_CONTEXT
358 -- ,INTERFACE_STATUS
359 ,INVOICE_ADDRESS_1
360 ,INVOICE_ADDRESS_2
361 ,INVOICE_ADDRESS_3
362 ,INVOICE_ADDRESS_4
363 ,INVOICE_CITY
364 ,INVOICE_COUNTRY
365 ,INVOICE_COUNTY
366 ,INVOICE_POSTAL_CODE
367 ,INVOICE_PROVINCE_INT
368 -- ,INVOICE_SITE
369 ,INVOICE_SITE_CODE
370 ,INVOICE_STATE
371 -- ,INVOICE_TO_CONTACT
372 ,INVOICE_TO_CONTACT_FIRST_NAME
373 ,INVOICE_TO_CONTACT_ID
374 ,INVOICE_TO_CONTACT_LAST_NAME
375 ,INVOICE_TO_ORG
376 ,INVOICE_TO_ORG_ID
377 -- ,INVOICE_TOLERANCE_ABOVE
378 -- ,INVOICE_TOLERANCE_BELOW
379 ,INVOICING_RULE
380 ,INVOICING_RULE_ID
381 ,LAST_UPDATE_DATE
382 ,LAST_UPDATE_LOGIN
383 ,LAST_UPDATED_BY
384 ,LATEST_SCHEDULE_LIMIT
385 ,OPEN_FLAG
386 -- ,OPERATION_CODE
387 ,ORDER_DATE_TYPE_CODE
388 ,ORDER_NUMBER
389 ,ORDER_SOURCE
390 ,ORDER_SOURCE_ID
391 ,ORDER_TYPE
392 ,ORDER_TYPE_ID
393 -- ,ORDERED_BY_CONTACT_FIRST_NAME
394 -- ,ORDERED_BY_CONTACT_LAST_NAME
395 ,ORDERED_DATE
396 ,ORG_ID
397 ,ORIG_SYS_DOCUMENT_REF
398 ,PACKING_INSTRUCTIONS
399 ,PARTIAL_SHIPMENTS_ALLOWED
400 ,PAYMENT_TERM
401 ,PAYMENT_TERM_ID
402 -- ,PO_ATTRIBUTE_1
403 -- ,PO_ATTRIBUTE_2
404 -- ,PO_ATTRIBUTE_3
405 -- ,PO_ATTRIBUTE_4
406 -- ,PO_ATTRIBUTE_5
407 -- ,PO_ATTRIBUTE_6
408 -- ,PO_ATTRIBUTE_7
409 -- ,PO_ATTRIBUTE_8
410 -- ,PO_ATTRIBUTE_9
411 -- ,PO_ATTRIBUTE_10
412 -- ,PO_ATTRIBUTE_11
413 -- ,PO_ATTRIBUTE_12
414 -- ,PO_ATTRIBUTE_13
415 -- ,PO_ATTRIBUTE_14
416 -- ,PO_ATTRIBUTE_15
417 -- ,PO_REVISION_DATE
418 ,PRICE_LIST
419 ,PRICE_LIST_ID
420 ,PRICING_DATE
421 ,PROGRAM
422 ,PROGRAM_APPLICATION
423 ,PROGRAM_APPLICATION_ID
424 ,PROGRAM_ID
425 ,PROGRAM_UPDATE_DATE
426 -- ,RELATED_PO_NUMBER
427 -- ,REMAINDER_ORDERS_ALLOWED
428 ,REQUEST_DATE
429 ,REQUEST_ID
430 ,RETURN_REASON_CODE
431 ,SALESREP
432 ,SALESREP_ID
433 ,SHIP_FROM_ORG
434 ,SHIP_FROM_ORG_ID
435 ,SHIP_TO_ADDRESS_1
436 ,SHIP_TO_ADDRESS_2
437 ,SHIP_TO_ADDRESS_3
438 ,SHIP_TO_ADDRESS_4
439 ,SHIP_TO_CITY
440 ,SHIP_TO_CONTACT
441 ,SHIP_TO_CONTACT_FIRST_NAME
442 ,SHIP_TO_CONTACT_ID
443 ,SHIP_TO_CONTACT_LAST_NAME
444 ,SHIP_TO_COUNTRY
445 ,SHIP_TO_COUNTY
446 ,SHIP_TO_CUSTOMER
447 -- ,SHIP_TO_CUSTOMER_NUMBER
448 ,SHIP_TO_ORG
449 ,SHIP_TO_ORG_ID
450 ,SHIP_TO_POSTAL_CODE
451 ,SHIP_TO_PROVINCE
452 -- ,SHIP_TO_SITE_INT
453 ,SHIP_TO_STATE
454 ,SHIP_TOLERANCE_ABOVE
455 ,SHIP_TOLERANCE_BELOW
456 -- ,SHIPMENT_PRIORITY
457 ,SHIPMENT_PRIORITY_CODE
458 -- ,SHIPMENT_PRIORITY_CODE_INT
459 ,SHIPPING_INSTRUCTIONS
460 -- ,SHIPPING_METHOD
461 ,SHIPPING_METHOD_CODE
462 ,SOLD_FROM_ORG
463 ,SOLD_FROM_ORG_ID
464 ,SOLD_TO_CONTACT
465 ,SOLD_TO_CONTACT_ID
466 ,SOLD_TO_ORG
467 ,SOLD_TO_ORG_ID
468 ,SOURCE_DOCUMENT_ID
469 ,SOURCE_DOCUMENT_TYPE_ID
470 -- ,SUBMISSION_DATETIME
471 ,TAX_EXEMPT_FLAG
472 ,TAX_EXEMPT_NUMBER
473 ,TAX_EXEMPT_REASON
474 ,TAX_EXEMPT_REASON_CODE
475 ,TAX_POINT
476 ,TAX_POINT_CODE
477 -- ,TRANSACTIONAL_CURR
478 ,TRANSACTIONAL_CURR_CODE
479 ,VERSION_NUMBER
480 ,ship_to_edi_location_code
481 ,sold_to_edi_location_code
482 ,BILL_TO_EDI_LOCATION_CODE
483 ,Customer_payment_term
484 ,SOLD_TO_ADDRESS1
485 ,SOLD_TO_ADDRESS2
486 ,SOLD_TO_ADDRESS3
487 ,SOLD_TO_ADDRESS4
488 ,SOLD_TO_CITY
489 ,SOLD_TO_POSTAL_CODE
490 ,SOLD_TO_COUNTRY
491 ,SOLD_TO_STATE
492 ,SOLD_TO_COUNTY
493 ,SOLD_TO_PROVINCE
494 ,SOLD_TO_CONTACT_LAST_NAME
495 ,SOLD_TO_CONTACT_FIRST_NAME
496 ,ORDER_CATEGORY_CODE
497 ,ship_from_edi_location_code
498 ,SHIP_FROM_ADDRESS_1
499 ,SHIP_FROM_ADDRESS_2
500 ,SHIP_FROM_ADDRESS_3
501 ,SHIP_FROM_CITY
502 ,SHIP_FROM_POSTAL_CODE
503 ,SHIP_FROM_COUNTRY
504 ,SHIP_FROM_REGION1
505 ,SHIP_FROM_REGION2
506 ,SHIP_FROM_REGION3
507 ,SHIP_FROM_ADDRESS_ID
508 ,SOLD_TO_ADDRESS_ID
509 ,SHIP_TO_ADDRESS_ID
510 ,INVOICE_ADDRESS_ID
511 ,SHIP_TO_ADDRESS_CODE
512 ,xml_message_id
513 ,acknowledgment_type
514 ,blanket_number -- For Blanket Ack chnages
515 ,sold_to_site_use_id
516 ,sold_to_location_address1
517 ,sold_to_location_address2
518 ,sold_to_location_address3
519 ,sold_to_location_address4
520 ,sold_to_location_city
521 ,sold_to_location_postal_code
522 ,sold_to_location_country
523 ,sold_to_location_state
524 ,sold_to_location_county
525 ,sold_to_location_province
526 -- start of additional quoting columns
527 ,transaction_phase_code
528 ,quote_number
529 ,quote_date
530 ,sales_document_name
531 ,user_status_code
532 -- end of additional quoting columns
533 -- { Distributer Order related change
534 ,end_customer_id
535 ,end_customer_contact_id
536 ,end_customer_site_use_id
537 ,ib_owner
538 ,ib_current_location
539 ,ib_installed_at_location
540 -- Distributer Order related change }
541 ,end_customer_name
542 ,end_customer_number
543 ,end_customer_contact
544 ,end_customer_address1
545 ,end_customer_address2
546 ,end_customer_address3
547 ,end_customer_address4
548 ,end_customer_city
549 ,end_customer_state
550 ,end_customer_postal_code
551 ,end_customer_country
552 ,INVOICE_CUSTOMER -- for bug 4489065
553
554 )
555 VALUES
556 (
557 l_header_val_rec.ACCOUNTING_RULE
558 , l_header_rec.ACCOUNTING_RULE_ID --number
559 , l_header_rec.ACCOUNTING_RULE_DURATION --number
560 ,'' -- acknowledgment_flag
561 , l_header_val_rec.AGREEMENT
562 , l_header_rec.AGREEMENT_ID --number
563 ,'' --AGREEMENT_NAME
564 , l_header_rec.ATTRIBUTE1
565 , l_header_rec.ATTRIBUTE2
566 , l_header_rec.ATTRIBUTE3
567 , l_header_rec.ATTRIBUTE4
568 , l_header_rec.ATTRIBUTE5
569 , l_header_rec.ATTRIBUTE6
570 , l_header_rec.ATTRIBUTE7
571 , l_header_rec.ATTRIBUTE8
572 , l_header_rec.ATTRIBUTE9
573 , l_header_rec.ATTRIBUTE10
574 , l_header_rec.ATTRIBUTE11
575 , l_header_rec.ATTRIBUTE12
576 , l_header_rec.ATTRIBUTE13
577 , l_header_rec.ATTRIBUTE14
578 , l_header_rec.ATTRIBUTE15
579 , l_header_rec.ATTRIBUTE16 -- for bug 2184255
580 , l_header_rec.ATTRIBUTE17
581 , l_header_rec.ATTRIBUTE18
582 , l_header_rec.ATTRIBUTE19
583 , l_header_rec.ATTRIBUTE20
584 , l_header_rec.FIRST_ACK_CODE
585 , l_header_rec.LAST_ACK_CODE
586 , l_header_rec.FIRST_ACK_DATE
587 , l_header_rec.LAST_ACK_DATE
588 , 'B' -- BUYER_SELLER_FLAG
589 , l_header_rec.BOOKED_FLAG
590 , l_header_rec.CANCELLED_FLAG
591 , '' -- CHANGE_DATE
592 , l_header_rec.CHANGE_SEQUENCE
593 , l_header_rec.CONTEXT
594 , l_header_rec.CONVERSION_RATE --number
595 , l_header_rec.CONVERSION_RATE_DATE
596 , l_header_val_rec.CONVERSION_TYPE
597 , l_header_rec.CONVERSION_TYPE_CODE
598 , l_header_rec.CREATED_BY -- number
599 , l_header_rec.CREATION_DATE
600 , l_header_rec.CUST_PO_NUMBER
601 , l_header_val_rec.SOLD_TO_ORG -- For bug 2701018
602 , l_header_val_rec.CUSTOMER_NUMBER
603 , l_header_rec.DELIVER_TO_CONTACT_ID--number
604 , l_header_val_rec.DELIVER_TO_CUSTOMER_NAME
605 , l_header_val_rec.DELIVER_TO_CUSTOMER_NUMBER
606 , l_header_rec.DELIVER_TO_ORG_ID-- number
607 , l_header_rec.DEMAND_CLASS_CODE
608 , l_header_rec.EARLIEST_SCHEDULE_LIMIT
609 , l_header_rec.EXPIRATION_DATE
610 , l_header_val_rec.FOB_POINT
611 , l_header_rec.FOB_POINT_CODE
612 , l_header_rec.FREIGHT_CARRIER_CODE
613 , l_header_val_rec.FREIGHT_TERMS
614 , l_header_rec.FREIGHT_TERMS_CODE
615 , l_header_rec.GLOBAL_ATTRIBUTE_CATEGORY
616 , l_header_rec.GLOBAL_ATTRIBUTE1
617 , l_header_rec.GLOBAL_ATTRIBUTE10
618 , l_header_rec.GLOBAL_ATTRIBUTE11
619 , l_header_rec.GLOBAL_ATTRIBUTE12
620 , l_header_rec.GLOBAL_ATTRIBUTE13
621 , l_header_rec.GLOBAL_ATTRIBUTE14
622 , l_header_rec.GLOBAL_ATTRIBUTE15
623 , l_header_rec.GLOBAL_ATTRIBUTE16
624 , l_header_rec.GLOBAL_ATTRIBUTE17
625 , l_header_rec.GLOBAL_ATTRIBUTE18
626 , l_header_rec.GLOBAL_ATTRIBUTE19
627 , l_header_rec.GLOBAL_ATTRIBUTE2
628 , l_header_rec.GLOBAL_ATTRIBUTE20
629 , l_header_rec.GLOBAL_ATTRIBUTE3
630 , l_header_rec.GLOBAL_ATTRIBUTE4
631 , l_header_rec.GLOBAL_ATTRIBUTE5
632 , l_header_rec.GLOBAL_ATTRIBUTE6
633 , l_header_rec.GLOBAL_ATTRIBUTE7
634 , l_header_rec.GLOBAL_ATTRIBUTE8
635 , l_header_rec.GLOBAL_ATTRIBUTE9
636 , l_header_rec.TP_CONTEXT
637 , l_header_rec.TP_ATTRIBUTE1
638 , l_header_rec.TP_ATTRIBUTE2
639 , l_header_rec.TP_ATTRIBUTE3
640 , l_header_rec.TP_ATTRIBUTE4
641 , l_header_rec.TP_ATTRIBUTE5
642 , l_header_rec.TP_ATTRIBUTE6
643 , l_header_rec.TP_ATTRIBUTE7
644 , l_header_rec.TP_ATTRIBUTE8
645 , l_header_rec.TP_ATTRIBUTE9
646 , l_header_rec.TP_ATTRIBUTE10
647 , l_header_rec.TP_ATTRIBUTE11
648 , l_header_rec.TP_ATTRIBUTE12
649 , l_header_rec.TP_ATTRIBUTE13
650 , l_header_rec.TP_ATTRIBUTE14
651 , l_header_rec.TP_ATTRIBUTE15
652 , l_header_rec.HEADER_ID--number
653 , l_header_val_rec.INVOICE_TO_ADDRESS1
654 , l_header_val_rec.INVOICE_TO_ADDRESS2
655 , l_header_val_rec.INVOICE_TO_ADDRESS3
656 , l_header_val_rec.INVOICE_TO_ADDRESS4
657 , l_header_val_rec.invoice_to_city
658 , l_header_val_rec.invoice_to_country
659 , l_header_val_rec.invoice_to_county
660 , l_header_val_rec.invoice_to_zip
661 , l_header_val_rec.invoice_to_province
662 , l_header_val_rec.invoice_to_location
663 , l_header_val_rec.invoice_to_state
664 , l_header_val_rec.invoice_to_contact_first_name
665 , l_header_rec.INVOICE_TO_CONTACT_ID--number
666 , l_header_val_rec.invoice_to_contact_last_name
667 , l_header_val_rec.INVOICE_TO_ORG--number
668 , l_header_rec.INVOICE_TO_ORG_ID--number
669 , l_header_val_rec.INVOICING_RULE
670 , l_header_rec.INVOICING_RULE_ID-- number
671 , l_header_rec.LAST_UPDATE_DATE
672 , l_header_rec.LAST_UPDATE_LOGIN-- number
673 , l_header_rec.LAST_UPDATED_BY-- number
674 , l_header_rec.LATEST_SCHEDULE_LIMIT
675 , l_header_rec.OPEN_FLAG
676 , l_header_rec.ORDER_DATE_TYPE_CODE
677 , l_header_rec.ORDER_NUMBER-- number
678 , l_header_val_rec.ORDER_SOURCE
679 , l_header_rec.ORDER_SOURCE_ID-- number
680 , l_header_val_rec.ORDER_TYPE
681 , l_header_rec.ORDER_TYPE_ID-- number
682 , l_header_rec.ORDERED_DATE
683 , l_header_rec.ORG_ID-- number
684 , l_header_rec.ORIG_SYS_DOCUMENT_REF
685 , l_header_rec.PACKING_INSTRUCTIONS
686 , l_header_rec.PARTIAL_SHIPMENTS_ALLOWED
687 , l_header_val_rec.PAYMENT_TERM
688 , l_header_rec.PAYMENT_TERM_ID-- number
689 , l_header_val_rec.PRICE_LIST
690 , l_header_rec.PRICE_LIST_ID-- number
691 , l_header_rec.PRICING_DATE
692 , '' -- PROGRAM
693 , '' -- PROGRAM_APPLICATION
694 , l_header_rec.PROGRAM_APPLICATION_ID -- number
695 , l_header_rec.PROGRAM_ID -- number
696 , l_header_rec.PROGRAM_UPDATE_DATE
697 , l_header_rec.REQUEST_DATE
698 , l_header_rec.REQUEST_ID -- number
699 , l_header_rec.RETURN_REASON_CODE
700 , l_header_val_rec.SALESREP
701 , l_header_rec.SALESREP_ID -- number
702 , l_header_val_rec.SHIP_FROM_ORG
703 , l_header_rec.SHIP_FROM_ORG_ID -- number
704 , l_header_val_rec.SHIP_TO_ADDRESS1
705 , l_header_val_rec.SHIP_TO_ADDRESS2
706 , l_header_val_rec.SHIP_TO_ADDRESS3
707 , l_header_val_rec.SHIP_TO_ADDRESS4
708 , l_header_val_rec.ship_to_city
709 , l_header_val_rec.SHIP_TO_CONTACT
710 , l_header_val_rec.SHIP_TO_CONTACT_FIRST_NAME
711 , l_header_rec.SHIP_TO_CONTACT_ID -- number
712 , l_header_val_rec.SHIP_TO_CONTACT_LAST_NAME
713 , l_header_val_rec.ship_to_country
714 , l_header_val_rec.ship_to_county
715 , l_header_val_rec.ship_to_customer_name
716 , l_header_val_rec.SHIP_TO_ORG
717 , l_header_rec.SHIP_TO_ORG_ID -- number
718 , l_header_val_rec.ship_to_zip
719 , l_header_val_rec.ship_to_province
720 , l_header_val_rec.ship_to_state
721 , l_header_rec.SHIP_TOLERANCE_ABOVE -- number
722 , l_header_rec.SHIP_TOLERANCE_BELOW -- number
723 , l_header_rec.SHIPMENT_PRIORITY_CODE
724 , l_header_rec.SHIPPING_INSTRUCTIONS
725 , l_header_rec.SHIPPING_METHOD_CODE
726 , '' -- SOLD_FROM_ORG
727 , '' -- SOLD_FROM_ORG_ID
728 , l_header_val_rec.SOLD_TO_CONTACT
729 , l_header_rec.SOLD_TO_CONTACT_ID -- number
730 , l_header_val_rec.SOLD_TO_ORG
731 , l_header_rec.SOLD_TO_ORG_ID -- number
732 , l_header_rec.SOURCE_DOCUMENT_ID -- number
733 , l_header_rec.SOURCE_DOCUMENT_TYPE_ID -- number
734 , l_header_rec.TAX_EXEMPT_FLAG
735 , l_header_rec.TAX_EXEMPT_NUMBER
736 , l_header_val_rec.TAX_EXEMPT_REASON
737 , l_header_rec.TAX_EXEMPT_REASON_CODE
738 , l_header_val_rec.TAX_POINT
739 , l_header_rec.TAX_POINT_CODE
740 , l_header_rec.TRANSACTIONAL_CURR_CODE
741 , l_header_rec.VERSION_NUMBER -- number
742 , l_header_rec.SHIP_TO_EDI_LOCATION_CODE
743 , l_header_rec.SOLD_TO_EDI_LOCATION_CODE
744 , l_header_rec.BILL_TO_EDI_LOCATION_CODE
745 , l_header_val_rec.CUSTOMER_PAYMENT_TERM
746 , l_header_val_rec.SOLD_TO_ADDRESS1
747 , l_header_val_rec.SOLD_TO_ADDRESS2
748 , l_header_val_rec.SOLD_TO_ADDRESS3
749 , l_header_val_rec.SOLD_TO_ADDRESS4
750 , l_header_val_rec.SOLD_TO_CITY
751 , l_header_val_rec.SOLD_TO_ZIP
752 , l_header_val_rec.SOLD_TO_COUNTRY
753 , l_header_val_rec.SOLD_TO_STATE
754 , l_header_val_rec.SOLD_TO_COUNTY
755 , l_header_val_rec.SOLD_TO_PROVINCE
756 , l_header_val_rec.SOLD_TO_CONTACT_LAST_NAME
757 , l_header_val_rec.SOLD_TO_CONTACT_FIRST_NAME
758 , l_header_rec.ORDER_CATEGORY_CODE
759 , l_header_rec.ship_from_edi_location_code
760 , l_header_val_rec.SHIP_FROM_ADDRESS1
761 , l_header_val_rec.SHIP_FROM_ADDRESS2
762 , l_header_val_rec.SHIP_FROM_ADDRESS3
763 , l_header_val_rec.SHIP_FROM_CITY
764 , l_header_val_rec.SHIP_FROM_POSTAL_CODE
765 , l_header_val_rec.SHIP_FROM_COUNTRY
766 , l_header_val_rec.SHIP_FROM_REGION1
767 , l_header_val_rec.SHIP_FROM_REGION2
768 , l_header_val_rec.SHIP_FROM_REGION3
769 , l_header_rec.SHIP_FROM_ADDRESS_ID
770 , l_header_rec.SOLD_TO_ADDRESS_ID
771 , l_header_rec.SHIP_TO_ADDRESS_ID
772 , l_header_rec.INVOICE_ADDRESS_ID
773 , l_header_val_rec.SHIP_TO_LOCATION
774 , l_header_rec.xml_message_id
775 , p_ack_type
776 , l_header_rec.blanket_number
777 , l_header_rec.sold_to_site_use_id
778 , l_header_val_rec.sold_to_location_address1
779 , l_header_val_rec.sold_to_location_address2
780 , l_header_val_rec.sold_to_location_address3
781 , l_header_val_rec.sold_to_location_address4
782 , l_header_val_rec.sold_to_location_city
783 , l_header_val_rec.sold_to_location_postal
784 , l_header_val_rec.sold_to_location_country
785 , l_header_val_rec.sold_to_location_state
786 , l_header_val_rec.sold_to_location_county
787 , l_header_val_rec.sold_to_location_province
788 -- start of additional quoting columns
789 , l_header_rec.transaction_phase_code
790 , l_header_rec.quote_number
791 , l_header_rec.quote_date
792 , l_header_rec.sales_document_name
793 , l_header_rec.user_status_code
794 -- end of additional quoting columns
795 -- { Distributer Order related change
796 , l_header_rec.end_customer_id
797 , l_header_rec.end_customer_contact_id
798 , l_header_rec.end_customer_site_use_id
799 , l_header_rec.ib_owner
800 , l_header_rec.ib_current_location
801 , l_header_rec.ib_installed_at_location
802 -- Distributer Order related change }
803 ,l_header_val_rec.end_customer_name
804 ,l_header_val_rec.end_customer_number
805 ,l_header_val_rec.end_customer_contact
806 ,l_header_val_rec.end_customer_site_address1
807 ,l_header_val_rec.end_customer_site_address2
808 ,l_header_val_rec.end_customer_site_address3
809 ,l_header_val_rec.end_customer_site_address4
810 ,l_header_val_rec.end_customer_site_city
811 ,l_header_val_rec.end_customer_site_state
812 ,l_header_val_rec.end_customer_site_postal_code
813 ,l_header_val_rec.end_customer_site_country
814 ,l_header_val_rec.invoice_to_customer_name -- for bug 4489065
815 );
816 ELSE
817 oe_debug_pub.add('Incomplete data for inserting header ack rec');
818 END IF;
819
820 oe_debug_pub.add('after inserting header acknowledgment record');
821
822 EXCEPTION
823
824 WHEN OTHERS THEN
825 oe_debug_pub.Add('Encountered Others Error Exception in OE_Header_Ack_Util.Insert_Row: '||sqlerrm);
826
827 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
828 THEN
829 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'OE_Header_Ack_Util.Insert_Row');
830 END IF;
831
832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
833 END Insert_Row;
834
835
836 Procedure Insert_Row
837 (p_header_rec In OE_Order_Pub.Header_Rec_Type,
838 x_ack_type Out Nocopy Varchar2,
839 x_return_status Out Nocopy Varchar2
840 )
841 Is
842
843 l_header_rec OE_Order_Pub.Header_Rec_Type := p_header_rec;
844 l_count Number;
845 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
846 l_ack_type Varchar2(3);
847
848 Begin
849
850 If nvl(l_header_rec.FIRST_ACK_CODE, ' ') = ' ' Then
851 -- Trans is 855
852 oe_debug_pub.add('trans is 855');
853 l_header_rec.First_Ack_Code :=
854 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
855 p_reject_order => 'N',
856 p_header_id => l_header_rec.header_id);
857
858 l_header_rec.FIRST_ACK_DATE := Null;
859 l_header_rec.LAST_ACK_CODE := Null;
860 l_header_rec.LAST_ACK_DATE := Null;
861 --l_ack_type := '855'; for bug4730258
862 x_ack_type := '855';
863 Else
864 -- Trans is 865
865 oe_debug_pub.add('trans is 865');
866 l_header_rec.Last_Ack_Code :=
867 Get_Ack_Code(p_order_source_id => l_header_rec.order_source_id,
868 p_reject_order => 'N',
869 p_header_id => l_header_rec.header_id);
870 If l_header_rec.Last_Ack_Code = 'AT' Then
871 l_header_rec.Last_Ack_Code := 'AC';
872 End If;
873 l_header_rec.Last_Ack_Date := Null;
874 --l_ack_type := '865'; for bug4730258
875 x_ack_type := '865';
876
877 End If;
878
879 Begin
880 Select count(*)
881 Into l_count
882 From Oe_Header_Acks
883 Where header_id = l_header_rec.header_id
884 And nvl(change_sequence,'ALL') = nvl(l_header_rec.change_sequence,'ALL')
885 And nvl(acknowledgment_type,'ALL') = nvl(l_ack_type,'ALL')
886 And acknowledgment_flag Is Null;
887
888 If l_count > 0 Then
889 oe_debug_pub.add('unacknowledged ack present, call delete');
890 --Commented code to delete records from oe_line_acks for bug 4730258
891 Delete From Oe_Header_Acks
892 Where header_id = l_header_rec.header_id
893 And nvl(change_sequence,'ALL') = nvl(l_header_rec.change_sequence,'ALL')
894 And nvl(acknowledgment_type,'ALL') = nvl(l_ack_type,'ALL')
895 And acknowledgment_flag Is Null;
896
897 End If;
898 Exception
899 When Others Then
900 Null;
901 End;
902
903 IF (l_header_rec.order_source_id <> FND_API.G_MISS_NUM
904 AND nvl(l_header_rec.order_source_id,0) <> 0
905 AND l_header_rec.orig_sys_document_ref <> FND_API.G_MISS_CHAR
906 AND nvl(l_header_rec.orig_sys_document_ref,' ') <> ' ')
907 OR (l_header_rec.header_id <> FND_API.G_MISS_NUM
908 AND nvl(l_header_rec.header_id,0) <> 0) Then
909
910 -- Insert data
911 Insert
912 Into Oe_Header_Acks
913 (ACCOUNTING_RULE_ID
914 ,ACCOUNTING_RULE_DURATION
915 ,ACKNOWLEDGMENT_FLAG
916 ,AGREEMENT_ID
917 ,AGREEMENT_NAME
918 ,ATTRIBUTE1
919 ,ATTRIBUTE2
920 ,ATTRIBUTE3
921 ,ATTRIBUTE4
922 ,ATTRIBUTE5
923 ,ATTRIBUTE6
924 ,ATTRIBUTE7
925 ,ATTRIBUTE8
926 ,ATTRIBUTE9
927 ,ATTRIBUTE10
928 ,ATTRIBUTE11
929 ,ATTRIBUTE12
930 ,ATTRIBUTE13
931 ,ATTRIBUTE14
932 ,ATTRIBUTE15
933 ,ATTRIBUTE16 --For bug 2184255
934 ,ATTRIBUTE17
935 ,ATTRIBUTE18
936 ,ATTRIBUTE19
937 ,ATTRIBUTE20
938 ,FIRST_ACK_CODE
939 ,LAST_ACK_CODE
940 ,FIRST_ACK_DATE
941 ,LAST_ACK_DATE
942 ,BUYER_SELLER_FLAG
943 ,BOOKED_FLAG
944 ,CANCELLED_FLAG
945 ,CHANGE_DATE
946 ,CHANGE_SEQUENCE
947 ,CONTEXT
948 ,CONVERSION_RATE
949 ,CONVERSION_RATE_DATE
950 ,CONVERSION_TYPE_CODE
951 ,CREATED_BY
952 ,CREATION_DATE
953 ,CUST_PO_NUMBER
954 ,DELIVER_TO_CONTACT_ID
955 ,DELIVER_TO_ORG_ID
956 ,DEMAND_CLASS_CODE
957 ,EARLIEST_SCHEDULE_LIMIT
958 ,EXPIRATION_DATE
959 ,FOB_POINT_CODE
960 ,FREIGHT_CARRIER_CODE
961 ,FREIGHT_TERMS_CODE
962 ,GLOBAL_ATTRIBUTE_CATEGORY
963 ,GLOBAL_ATTRIBUTE1
964 ,GLOBAL_ATTRIBUTE10
965 ,GLOBAL_ATTRIBUTE11
966 ,GLOBAL_ATTRIBUTE12
967 ,GLOBAL_ATTRIBUTE13
968 ,GLOBAL_ATTRIBUTE14
969 ,GLOBAL_ATTRIBUTE15
970 ,GLOBAL_ATTRIBUTE16
971 ,GLOBAL_ATTRIBUTE17
972 ,GLOBAL_ATTRIBUTE18
973 ,GLOBAL_ATTRIBUTE19
974 ,GLOBAL_ATTRIBUTE2
975 ,GLOBAL_ATTRIBUTE20
976 ,GLOBAL_ATTRIBUTE3
977 ,GLOBAL_ATTRIBUTE4
978 ,GLOBAL_ATTRIBUTE5
979 ,GLOBAL_ATTRIBUTE6
980 ,GLOBAL_ATTRIBUTE7
981 ,GLOBAL_ATTRIBUTE8
982 ,GLOBAL_ATTRIBUTE9
983 ,TP_CONTEXT
984 ,TP_ATTRIBUTE1
985 ,TP_ATTRIBUTE2
986 ,TP_ATTRIBUTE3
987 ,TP_ATTRIBUTE4
988 ,TP_ATTRIBUTE5
989 ,TP_ATTRIBUTE6
990 ,TP_ATTRIBUTE7
991 ,TP_ATTRIBUTE8
992 ,TP_ATTRIBUTE9
993 ,TP_ATTRIBUTE10
994 ,TP_ATTRIBUTE11
995 ,TP_ATTRIBUTE12
996 ,TP_ATTRIBUTE13
997 ,TP_ATTRIBUTE14
998 ,TP_ATTRIBUTE15
999 ,HEADER_ID
1000 ,INVOICE_TO_CONTACT_ID
1001 ,INVOICE_TO_ORG_ID
1002 ,INVOICING_RULE_ID
1003 ,LAST_UPDATE_DATE
1004 ,LAST_UPDATE_LOGIN
1005 ,LAST_UPDATED_BY
1006 ,LATEST_SCHEDULE_LIMIT
1007 ,OPEN_FLAG
1008 ,ORDER_DATE_TYPE_CODE
1009 ,ORDER_NUMBER
1010 ,ORDER_SOURCE_ID
1011 ,ORDER_TYPE_ID
1012 ,ORDERED_DATE
1013 ,ORG_ID
1014 ,ORIG_SYS_DOCUMENT_REF
1015 ,PACKING_INSTRUCTIONS
1016 ,PARTIAL_SHIPMENTS_ALLOWED
1017 ,PAYMENT_TERM_ID
1018 ,PRICE_LIST_ID
1019 ,PRICING_DATE
1020 ,PROGRAM
1021 ,PROGRAM_APPLICATION
1022 ,PROGRAM_APPLICATION_ID
1023 ,PROGRAM_ID
1024 ,PROGRAM_UPDATE_DATE
1025 ,REQUEST_DATE
1026 ,REQUEST_ID
1027 ,RETURN_REASON_CODE
1028 ,SALESREP_ID
1029 ,SHIP_FROM_ORG_ID
1030 ,SHIP_TO_CONTACT_ID
1031 ,SHIP_TO_ORG_ID
1032 ,SHIP_TOLERANCE_ABOVE
1033 ,SHIP_TOLERANCE_BELOW
1034 ,SHIPMENT_PRIORITY_CODE
1035 ,SHIPPING_INSTRUCTIONS
1036 ,SHIPPING_METHOD_CODE
1037 ,SOLD_FROM_ORG
1038 ,SOLD_FROM_ORG_ID
1039 ,SOLD_TO_CONTACT_ID
1040 ,SOLD_TO_ORG_ID
1041 ,SOURCE_DOCUMENT_ID
1042 ,SOURCE_DOCUMENT_TYPE_ID
1043 ,TAX_EXEMPT_FLAG
1044 ,TAX_EXEMPT_NUMBER
1045 ,TAX_EXEMPT_REASON_CODE
1046 ,TAX_POINT_CODE
1047 ,TRANSACTIONAL_CURR_CODE
1048 ,VERSION_NUMBER
1049 ,ORDER_CATEGORY_CODE
1050 ,xml_message_id
1051 ,acknowledgment_type
1052 ,blanket_number -- For Blanket Ack chnages
1053 ,sold_to_site_use_id
1054 -- start if additional quoting columns
1055 ,transaction_phase_code
1056 ,quote_number
1057 ,quote_date
1058 ,sales_document_name
1059 ,user_status_code
1060 -- end of additional quoting columns
1061 -- { Distributer Order related change
1062 ,end_customer_id
1063 ,end_customer_contact_id
1064 ,end_customer_site_use_id
1065 ,ib_owner
1066 ,ib_current_location
1067 ,ib_installed_at_location
1068 -- Distributer Order related change }
1069
1070 )
1071 Values
1072 ( l_header_rec.ACCOUNTING_RULE_ID --number
1073 , l_header_rec.ACCOUNTING_RULE_DURATION --number
1074 ,'' -- acknowledgment_flag
1075 , l_header_rec.AGREEMENT_ID --number
1076 ,'' --AGREEMENT_NAME
1077 , l_header_rec.ATTRIBUTE1
1078 , l_header_rec.ATTRIBUTE2
1079 , l_header_rec.ATTRIBUTE3
1080 , l_header_rec.ATTRIBUTE4
1081 , l_header_rec.ATTRIBUTE5
1082 , l_header_rec.ATTRIBUTE6
1083 , l_header_rec.ATTRIBUTE7
1084 , l_header_rec.ATTRIBUTE8
1085 , l_header_rec.ATTRIBUTE9
1086 , l_header_rec.ATTRIBUTE10
1087 , l_header_rec.ATTRIBUTE11
1088 , l_header_rec.ATTRIBUTE12
1089 , l_header_rec.ATTRIBUTE13
1090 , l_header_rec.ATTRIBUTE14
1091 , l_header_rec.ATTRIBUTE15
1092 , l_header_rec.ATTRIBUTE16 -- for bug 2184255
1093 , l_header_rec.ATTRIBUTE17
1094 , l_header_rec.ATTRIBUTE18
1095 , l_header_rec.ATTRIBUTE19
1096 , l_header_rec.ATTRIBUTE20
1097 , l_header_rec.FIRST_ACK_CODE
1098 , l_header_rec.LAST_ACK_CODE
1099 , l_header_rec.FIRST_ACK_DATE
1100 , l_header_rec.LAST_ACK_DATE
1101 , 'B' -- BUYER_SELLER_FLAG
1102 , l_header_rec.BOOKED_FLAG
1103 , l_header_rec.CANCELLED_FLAG
1104 , '' -- CHANGE_DATE
1105 , l_header_rec.CHANGE_SEQUENCE
1106 , l_header_rec.CONTEXT
1107 , l_header_rec.CONVERSION_RATE --number
1108 , l_header_rec.CONVERSION_RATE_DATE
1109 , l_header_rec.CONVERSION_TYPE_CODE
1110 , l_header_rec.CREATED_BY -- number
1111 , l_header_rec.CREATION_DATE
1112 , l_header_rec.CUST_PO_NUMBER
1113 , l_header_rec.DELIVER_TO_CONTACT_ID--number
1114 , l_header_rec.DELIVER_TO_ORG_ID-- number
1115 , l_header_rec.DEMAND_CLASS_CODE
1116 , l_header_rec.EARLIEST_SCHEDULE_LIMIT
1117 , l_header_rec.EXPIRATION_DATE
1118 , l_header_rec.FOB_POINT_CODE
1119 , l_header_rec.FREIGHT_CARRIER_CODE
1120 , l_header_rec.FREIGHT_TERMS_CODE
1121 , l_header_rec.GLOBAL_ATTRIBUTE_CATEGORY
1122 , l_header_rec.GLOBAL_ATTRIBUTE1
1123 , l_header_rec.GLOBAL_ATTRIBUTE10
1124 , l_header_rec.GLOBAL_ATTRIBUTE11
1125 , l_header_rec.GLOBAL_ATTRIBUTE12
1126 , l_header_rec.GLOBAL_ATTRIBUTE13
1127 , l_header_rec.GLOBAL_ATTRIBUTE14
1128 , l_header_rec.GLOBAL_ATTRIBUTE15
1129 , l_header_rec.GLOBAL_ATTRIBUTE16
1130 , l_header_rec.GLOBAL_ATTRIBUTE17
1131 , l_header_rec.GLOBAL_ATTRIBUTE18
1132 , l_header_rec.GLOBAL_ATTRIBUTE19
1133 , l_header_rec.GLOBAL_ATTRIBUTE2
1134 , l_header_rec.GLOBAL_ATTRIBUTE20
1135 , l_header_rec.GLOBAL_ATTRIBUTE3
1136 , l_header_rec.GLOBAL_ATTRIBUTE4
1137 , l_header_rec.GLOBAL_ATTRIBUTE5
1138 , l_header_rec.GLOBAL_ATTRIBUTE6
1139 , l_header_rec.GLOBAL_ATTRIBUTE7
1140 , l_header_rec.GLOBAL_ATTRIBUTE8
1141 , l_header_rec.GLOBAL_ATTRIBUTE9
1142 , l_header_rec.TP_CONTEXT
1143 , l_header_rec.TP_ATTRIBUTE1
1144 , l_header_rec.TP_ATTRIBUTE2
1145 , l_header_rec.TP_ATTRIBUTE3
1146 , l_header_rec.TP_ATTRIBUTE4
1147 , l_header_rec.TP_ATTRIBUTE5
1148 , l_header_rec.TP_ATTRIBUTE6
1149 , l_header_rec.TP_ATTRIBUTE7
1150 , l_header_rec.TP_ATTRIBUTE8
1151 , l_header_rec.TP_ATTRIBUTE9
1152 , l_header_rec.TP_ATTRIBUTE10
1153 , l_header_rec.TP_ATTRIBUTE11
1154 , l_header_rec.TP_ATTRIBUTE12
1155 , l_header_rec.TP_ATTRIBUTE13
1156 , l_header_rec.TP_ATTRIBUTE14
1157 , l_header_rec.TP_ATTRIBUTE15
1158 , l_header_rec.HEADER_ID--number
1159 , l_header_rec.INVOICE_TO_CONTACT_ID--number
1160 , l_header_rec.INVOICE_TO_ORG_ID--number
1161 , l_header_rec.INVOICING_RULE_ID-- number
1162 , l_header_rec.LAST_UPDATE_DATE
1163 , l_header_rec.LAST_UPDATE_LOGIN-- number
1164 , l_header_rec.LAST_UPDATED_BY-- number
1165 , l_header_rec.LATEST_SCHEDULE_LIMIT
1166 , l_header_rec.OPEN_FLAG
1167 , l_header_rec.ORDER_DATE_TYPE_CODE
1168 , l_header_rec.ORDER_NUMBER-- number
1169 , l_header_rec.ORDER_SOURCE_ID-- number
1170 , l_header_rec.ORDER_TYPE_ID-- number
1171 , l_header_rec.ORDERED_DATE
1172 , l_header_rec.ORG_ID-- number
1173 , l_header_rec.ORIG_SYS_DOCUMENT_REF
1174 , l_header_rec.PACKING_INSTRUCTIONS
1175 , l_header_rec.PARTIAL_SHIPMENTS_ALLOWED
1176 , l_header_rec.PAYMENT_TERM_ID-- number
1177 , l_header_rec.PRICE_LIST_ID-- number
1178 , l_header_rec.PRICING_DATE
1179 , '' -- PROGRAM
1180 , '' -- PROGRAM_APPLICATION
1181 , l_header_rec.PROGRAM_APPLICATION_ID -- number
1182 , l_header_rec.PROGRAM_ID -- number
1183 , l_header_rec.PROGRAM_UPDATE_DATE
1184 , l_header_rec.REQUEST_DATE
1185 , l_header_rec.REQUEST_ID -- number
1186 , l_header_rec.RETURN_REASON_CODE
1187 , l_header_rec.SALESREP_ID -- number
1188 , l_header_rec.SHIP_FROM_ORG_ID -- number
1189 , l_header_rec.SHIP_TO_CONTACT_ID -- number
1190 , l_header_rec.SHIP_TO_ORG_ID -- number
1191 , l_header_rec.SHIP_TOLERANCE_ABOVE -- number
1192 , l_header_rec.SHIP_TOLERANCE_BELOW -- number
1193 , l_header_rec.SHIPMENT_PRIORITY_CODE
1194 , l_header_rec.SHIPPING_INSTRUCTIONS
1195 , l_header_rec.SHIPPING_METHOD_CODE
1196 , '' -- SOLD_FROM_ORG
1197 , '' -- SOLD_FROM_ORG_ID
1198 , l_header_rec.SOLD_TO_CONTACT_ID -- number
1199 , l_header_rec.SOLD_TO_ORG_ID -- number
1200 , l_header_rec.SOURCE_DOCUMENT_ID -- number
1201 , l_header_rec.SOURCE_DOCUMENT_TYPE_ID -- number
1202 , l_header_rec.TAX_EXEMPT_FLAG
1203 , l_header_rec.TAX_EXEMPT_NUMBER
1204 , l_header_rec.TAX_EXEMPT_REASON_CODE
1205 , l_header_rec.TAX_POINT_CODE
1206 , l_header_rec.TRANSACTIONAL_CURR_CODE
1207 , l_header_rec.VERSION_NUMBER -- number
1208 , l_header_rec.ORDER_CATEGORY_CODE
1209 , l_header_rec.xml_message_id
1210 , l_ack_type
1211 , l_header_rec.blanket_number
1212 , l_header_rec.sold_to_site_use_id
1213 -- start of additional quoting columns
1214 , l_header_rec.transaction_phase_code
1215 , l_header_rec.quote_number
1216 , l_header_rec.quote_date
1217 , l_header_rec.sales_document_name
1218 , l_header_rec.user_status_code
1219 -- end of additional quoting columns
1220 -- { Distributer Order related change
1221 , l_header_rec.end_customer_id
1222 , l_header_rec.end_customer_contact_id
1223 , l_header_rec.end_customer_site_use_id
1224 , l_header_rec.ib_owner
1225 , l_header_rec.ib_current_location
1226 , l_header_rec.ib_installed_at_location
1227 -- Distributer Order related change }
1228 );
1229
1230 Else
1231 If l_debug_level > 0 Then
1232 Oe_Debug_Pub.Add('No data for Ack');
1233 End If;
1234 End If;
1235
1236 Exception
1237
1238 When Others Then
1239 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) Then
1240 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'OE_Header_Ack_Util.Insert_Row');
1241 End If;
1242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1243
1244 End Insert_Row;
1245
1246
1247
1248 PROCEDURE Delete_Row
1249 ( p_header_id IN NUMBER,
1250 p_ack_type IN Varchar2,
1251 p_orig_sys_document_ref IN Varchar2,
1252 p_sold_to_org_id IN NUMBER,
1253 p_sold_to_org In Varchar2,
1254 p_change_sequence IN Varchar2,
1255 p_request_id IN NUMBER
1256 )
1257 IS
1258 BEGIN
1259
1260 If p_header_id Is Not NULL Then
1261 oe_debug_pub.add('before deleting header acknowledgment ',3);
1262
1263 if p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_SSO
1264 OR p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_CSO
1265 then
1266
1267 DELETE FROM OE_HEADER_ACKS
1268 WHERE HEADER_ID = p_header_id
1269 AND ACKNOWLEDGMENT_FLAG Is Null
1270 -- Change this condition once a type is inserted for POAO/POCAO
1271 AND nvl(acknowledgment_type,'ALL') = nvl(p_ack_type,'ALL')
1272 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
1273 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
1274 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
1275 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
1276 AND nvl(change_sequence, FND_API.G_MISS_CHAR)
1277 = nvl(p_change_sequence, FND_API.G_MISS_CHAR)
1278 AND request_id = p_request_id;
1279 else
1280
1281 DELETE FROM OE_HEADER_ACKS
1282 WHERE HEADER_ID = p_header_id
1283 AND ACKNOWLEDGMENT_FLAG Is Null
1284 -- Change this condition once a type is inserted for POAO/POCAO
1285 AND nvl(acknowledgment_type,'ALL') = nvl(p_ack_type,'ALL')
1286 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
1287 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
1288 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
1289 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
1290 AND nvl(change_sequence, FND_API.G_MISS_CHAR)
1291 = nvl(p_change_sequence, FND_API.G_MISS_CHAR);
1292 end if;
1293
1294 Elsif p_orig_sys_document_ref Is Not NULL Then
1295 oe_debug_pub.add('before deleting header acknowledgment by orig_sys_document_Ref ',3);
1296
1297 if p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_SSO
1298 Or p_ack_type = oe_acknowledgment_pub.G_TRANSACTION_CSO
1299 then
1300 DELETE FROM OE_HEADER_ACKS
1301 WHERE ORIG_SYS_DOCUMENT_REF = p_orig_sys_document_ref
1302 AND ACKNOWLEDGMENT_TYPE = p_ack_type -- POI, CPO, etc
1303 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
1304 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
1305 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
1306 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
1307 AND nvl(change_sequence, FND_API.G_MISS_CHAR)
1308 = nvl(p_change_sequence, FND_API.G_MISS_CHAR)
1309 AND REQUEST_ID = p_request_id;
1310
1311
1312 else
1313 DELETE FROM OE_HEADER_ACKS
1314 WHERE ORIG_SYS_DOCUMENT_REF = p_orig_sys_document_ref
1315 AND ACKNOWLEDGMENT_TYPE = p_ack_type -- POI, CPO, etc
1316 AND nvl(sold_to_org_id, FND_API.G_MISS_NUM)
1317 = nvl(p_sold_to_org_id, FND_API.G_MISS_NUM)
1318 AND nvl(sold_to_org, FND_API.G_MISS_CHAR)
1319 = nvl(p_sold_to_org, FND_API.G_MISS_CHAR)
1320 AND nvl(change_sequence, FND_API.G_MISS_CHAR)
1321 = nvl(p_change_sequence, FND_API.G_MISS_CHAR);
1322
1323
1324 end if;
1325
1326 Else
1327 oe_debug_pub.add('not deleting any rows from oe_header_acks ',3);
1328
1329 End If;
1330
1331 EXCEPTION
1332
1333 WHEN OTHERS THEN
1334
1335 oe_debug_pub.Add('Encountered Others Error Exception in OE_Header_Ack_Util.Delete_Row: '||sqlerrm);
1336
1337 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1338 THEN
1339 FND_MSG_PUB.Add_Exc_Msg
1340 (G_PKG_NAME, 'OE_Header_Ack_Util.Delete_Row');
1341 END IF;
1342
1343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1344
1345 END Delete_Row;
1346
1347 END OE_Header_Ack_Util;