[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_ACK_PVT
Source
1 PACKAGE BODY OE_BULK_ACK_PVT AS
2 /* $Header: OEBVACKB.pls 120.2.12010000.2 2008/11/18 13:18:04 smusanna ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_ACK_PVT';
6
7 PROCEDURE Insert_Rejected_Lines_Ack(p_batch_id IN NUMBER,
8 p_order_source_id IN NUMBER,
9 p_orig_sys_document_ref IN VARCHAR2);
10
11 FUNCTION Get_Address_ID(p_key IN NUMBER
12 ,p_site_use_code IN VARCHAR2)
13 RETURN NUMBER
14 IS
15 l_c_index NUMBER;
16 BEGIN
17
18 IF p_key IS NULL THEN
19 RETURN NULL;
20 END IF;
21
22 IF p_site_use_code = 'SHIP_TO' THEN
23
24 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
25 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ADDRESS_ID;
26
27 ELSIF p_site_use_code = 'BILL_TO' THEN
28
29 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
30 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).ADDRESS_ID;
31
32 ELSIF p_site_use_code = 'SHIP_FROM' THEN
33
34 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
35 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).ADDRESS_ID;
36
37 ELSIF p_site_use_code = 'SOLD_TO' THEN
38
39 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
40 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).ADDRESS_ID;
41
42 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
43
44 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
45 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).ADDRESS_ID;
46
47 END IF;
48
49 END Get_Address_ID;
50
51 FUNCTION Get_Address1(p_key IN NUMBER
52 ,p_site_use_code IN VARCHAR2)
53 RETURN VARCHAR2
54 IS
55 l_c_index NUMBER;
56 BEGIN
57
58 IF p_key IS NULL THEN
59 RETURN NULL;
60 END IF;
61
62 IF p_site_use_code = 'SHIP_TO' THEN
63
64 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
65 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ADDRESS1;
66
67 ELSIF p_site_use_code = 'BILL_TO' THEN
68
69 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
70 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).ADDRESS1;
71
72 ELSIF p_site_use_code = 'SHIP_FROM' THEN
73
74 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
75 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).ADDRESS1;
76
77 ELSIF p_site_use_code = 'SOLD_TO' THEN
78
79 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
80 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).ADDRESS1;
81
82 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
83
84 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
85 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).ADDRESS1;
86
87 END IF;
88
89 END Get_ADDRESS1;
90
91 FUNCTION Get_Address2(p_key IN NUMBER
92 ,p_site_use_code IN VARCHAR2)
93 RETURN VARCHAR2
94 IS
95 l_c_index NUMBER;
96 BEGIN
97
98 IF p_key IS NULL THEN
99 RETURN NULL;
100 END IF;
101
102 IF p_site_use_code = 'SHIP_TO' THEN
103
104 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
105 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ADDRESS2;
106
107 ELSIF p_site_use_code = 'BILL_TO' THEN
108
109 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
110 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).ADDRESS2;
111
112 ELSIF p_site_use_code = 'SHIP_FROM' THEN
113
114 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
115 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).ADDRESS2;
116
117 ELSIF p_site_use_code = 'SOLD_TO' THEN
118
119 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
120 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).ADDRESS2;
121
122 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
123
124 l_c_index := OE_BULK_CACHE.Load_Sold_To_SITE(p_key,'N','Y');
125 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).ADDRESS2;
126
127 END IF;
128
129 END Get_ADDRESS2;
130
131 FUNCTION Get_Address3(p_key IN NUMBER
132 ,p_site_use_code IN VARCHAR2)
133 RETURN VARCHAR2
134 IS
135 l_c_index NUMBER;
136 BEGIN
137
138 IF p_key IS NULL THEN
139 RETURN NULL;
140 END IF;
141
142 IF p_site_use_code = 'SHIP_TO' THEN
143
144 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
145 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ADDRESS3;
146
147 ELSIF p_site_use_code = 'BILL_TO' THEN
148
149 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
150 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).ADDRESS3;
151
152 ELSIF p_site_use_code = 'SHIP_FROM' THEN
153
154 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
155 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).ADDRESS3;
156
157 ELSIF p_site_use_code = 'SOLD_TO' THEN
158
159 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
160 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).ADDRESS3;
161
162 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
163
164 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
165 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).ADDRESS3;
166
167 END IF;
168
169 END Get_ADDRESS3;
170
171 FUNCTION Get_Address4(p_key IN NUMBER
172 ,p_site_use_code IN VARCHAR2)
173 RETURN VARCHAR2
174 IS
175 l_c_index NUMBER;
176 BEGIN
177
178 IF p_key IS NULL THEN
179 RETURN NULL;
180 END IF;
181
182 IF p_site_use_code = 'SHIP_TO' THEN
183
184 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
185 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ADDRESS4;
186
187 ELSIF p_site_use_code = 'BILL_TO' THEN
188
189 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
190 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).ADDRESS4;
191
192 ELSIF p_site_use_code = 'SHIP_FROM' THEN
193
194 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
195 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).ADDRESS4;
196
197 ELSIF p_site_use_code = 'SOLD_TO' THEN
198
199 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
200 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).ADDRESS4;
201
202 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
203
204 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
205 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).ADDRESS4;
206
207 END IF;
208
209 END Get_ADDRESS4;
210
211 FUNCTION Get_State(p_key IN NUMBER
212 ,p_site_use_code IN VARCHAR2)
213 RETURN VARCHAR2
214 IS
215 l_c_index NUMBER;
216 BEGIN
217
218 IF p_key IS NULL THEN
219 RETURN NULL;
220 END IF;
221
222 IF p_site_use_code = 'SHIP_TO' THEN
223
224 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
225 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).STATE;
226
227 ELSIF p_site_use_code = 'BILL_TO' THEN
228
229 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
230 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).STATE;
231
232 ELSIF p_site_use_code = 'SHIP_FROM' THEN
233
234 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
235 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).STATE;
236
237 ELSIF p_site_use_code = 'SOLD_TO' THEN
238
239 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
240 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).STATE;
241
242 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
243
244 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
245 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).STATE;
246
247 END IF;
248
249 END Get_STATE;
250
251 FUNCTION Get_City(p_key IN NUMBER
252 ,p_site_use_code IN VARCHAR2)
253 RETURN VARCHAR2
254 IS
255 l_c_index NUMBER;
256 BEGIN
257
258 IF p_key IS NULL THEN
259 RETURN NULL;
260 END IF;
261
262 IF p_site_use_code = 'SHIP_TO' THEN
263
264 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
265 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).CITY;
266
267 ELSIF p_site_use_code = 'BILL_TO' THEN
268
269 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
270 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).CITY;
271
272 ELSIF p_site_use_code = 'SHIP_FROM' THEN
273
274 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
275 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).CITY;
276
277 ELSIF p_site_use_code = 'SOLD_TO' THEN
278
279 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
280 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).CITY;
281
282 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
283
284 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
285 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).CITY;
286
287 END IF;
288
289 END Get_CITY;
290
291 FUNCTION Get_Zip(p_key IN NUMBER
292 ,p_site_use_code IN VARCHAR2)
293 RETURN VARCHAR2
294 IS
295 l_c_index NUMBER;
296 BEGIN
297
298 IF p_key IS NULL THEN
299 RETURN NULL;
300 END IF;
301
302 IF p_site_use_code = 'SHIP_TO' THEN
303
304 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
305 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ZIP;
306
307 ELSIF p_site_use_code = 'BILL_TO' THEN
308
309 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
310 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).ZIP;
311
312 ELSIF p_site_use_code = 'SHIP_FROM' THEN
313
314 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
315 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).ZIP;
316
317 ELSIF p_site_use_code = 'SOLD_TO' THEN
318
319 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
320 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).ZIP;
321
322 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
323
324 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
325 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).ZIP;
326
327 END IF;
328
329 END Get_ZIP;
330
331 FUNCTION Get_Country(p_key IN NUMBER
332 ,p_site_use_code IN VARCHAR2)
333 RETURN VARCHAR2
334 IS
335 l_c_index NUMBER;
336 BEGIN
337
338 IF p_key IS NULL THEN
339 RETURN NULL;
340 END IF;
341
342 IF p_site_use_code = 'SHIP_TO' THEN
343
344 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
345 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).COUNTRY;
346
347 ELSIF p_site_use_code = 'BILL_TO' THEN
348
349 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
350 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).COUNTRY;
351
352 ELSIF p_site_use_code = 'SHIP_FROM' THEN
353
354 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
355 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).COUNTRY;
356
357 ELSIF p_site_use_code = 'SOLD_TO' THEN
358
359 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
360 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).COUNTRY;
361
362 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
363
364 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
365 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).COUNTRY;
366
367 END IF;
368
369 END Get_COUNTRY;
370
371 FUNCTION Get_County(p_key IN NUMBER
372 ,p_site_use_code IN VARCHAR2)
373 RETURN VARCHAR2
374 IS
375 l_c_index NUMBER;
376 BEGIN
377
378 IF p_key IS NULL THEN
379 RETURN NULL;
380 END IF;
381
382 IF p_site_use_code = 'SHIP_TO' THEN
383
384 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
385 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).COUNTY;
386
387 ELSIF p_site_use_code = 'BILL_TO' THEN
388
389 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
390 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).COUNTY;
391
392 ELSIF p_site_use_code = 'SHIP_FROM' THEN
393
394 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
395 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).COUNTY;
396
397 ELSIF p_site_use_code = 'SOLD_TO' THEN
398
399 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
400 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).COUNTY;
401
402 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
403
404 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
405 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).COUNTY;
406
407 END IF;
408
409 END Get_COUNTY;
410
411 FUNCTION Get_Province(p_key IN NUMBER
412 ,p_site_use_code IN VARCHAR2)
413 RETURN VARCHAR2
414 IS
415 l_c_index NUMBER;
416 BEGIN
417
418 IF p_key IS NULL THEN
419 RETURN NULL;
420 END IF;
421
422 IF p_site_use_code = 'SHIP_TO' THEN
423
424 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
425 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).PROVINCE;
426
427 ELSIF p_site_use_code = 'BILL_TO' THEN
428
429 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
430 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).PROVINCE;
431
432 ELSIF p_site_use_code = 'SHIP_FROM' THEN
433
434 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
435 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).PROVINCE;
436
437 ELSIF p_site_use_code = 'SOLD_TO' THEN
438
439 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
440 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).PROVINCE;
441
442 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
443
444 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
445 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).PROVINCE;
446
447 END IF;
448
449 END Get_PROVINCE;
450
451 FUNCTION Get_Location(p_key IN NUMBER
452 ,p_site_use_code IN VARCHAR2)
453 RETURN VARCHAR2
454 IS
455 l_c_index NUMBER;
456 BEGIN
457
458 IF p_key IS NULL THEN
459 RETURN NULL;
460 END IF;
461
462 IF p_site_use_code = 'SHIP_TO' THEN
463
464 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
465 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).LOCATION;
466
467 ELSIF p_site_use_code = 'BILL_TO' THEN
468
469 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
470 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).LOCATION;
471
472 ELSIF p_site_use_code = 'SHIP_FROM' THEN
473
474 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
475 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).LOCATION;
476
480 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).LOCATION;
477 ELSIF p_site_use_code = 'SOLD_TO' THEN
478
479 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
481
482 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
483
484 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
485 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).LOCATION;
486 END IF;
487
488 END Get_LOCATION;
489
490 FUNCTION Get_EDI_Location(p_key IN NUMBER
491 ,p_site_use_code IN VARCHAR2)
492 RETURN VARCHAR2
493 IS
494 l_c_index NUMBER;
495 BEGIN
496
497 IF p_key IS NULL THEN
498 RETURN NULL;
499 END IF;
500
501 IF p_site_use_code = 'SHIP_TO' THEN
502
503 l_c_index := OE_BULK_CACHE.Load_Ship_To(p_key,'N','Y');
504 RETURN OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).EDI_LOCATION_CODE;
505
506 ELSIF p_site_use_code = 'BILL_TO' THEN
507
508 l_c_index := OE_BULK_CACHE.Load_Invoice_To(p_key,'N','Y');
509 RETURN OE_BULK_CACHE.G_INVOICE_TO_TBL(l_c_index).EDI_LOCATION_CODE;
510
511 ELSIF p_site_use_code = 'SHIP_FROM' THEN
512
513 l_c_index := OE_BULK_CACHE.Load_Ship_From(p_key);
514 RETURN OE_BULK_CACHE.G_SHIP_FROM_TBL(l_c_index).EDI_LOCATION_CODE;
515
516 ELSIF p_site_use_code = 'SOLD_TO' THEN
517
518 l_c_index := OE_BULK_CACHE.Load_Sold_To(p_key,'N','Y');
519 RETURN OE_BULK_CACHE.G_SOLD_TO_TBL(l_c_index).EDI_LOCATION_CODE;
520
521 ELSIF p_site_use_code = 'SOLD_TO_SITE' THEN
522
523 l_c_index := OE_BULK_CACHE.Load_Sold_To_Site(p_key,'N','Y');
524 RETURN OE_BULK_CACHE.G_SOLD_TO_SITE_TBL(l_c_index).EDI_LOCATION_CODE;
525 END IF;
526
527 END Get_EDI_LOCATION;
528
529 PROCEDURE Insert_Header_Acks
530 (p_header_id IN NUMBER
531 )
532 IS
533 BEGIN
534
535 INSERT INTO OE_HEADER_ACKS
536 (ACCOUNTING_RULE
537 ,ACCOUNTING_RULE_ID
538 ,ACCOUNTING_RULE_DURATION
539 ,ACKNOWLEDGMENT_FLAG
540 ,AGREEMENT
541 ,AGREEMENT_ID
542 ,AGREEMENT_NAME
543 ,ATTRIBUTE1
544 ,ATTRIBUTE2
545 ,ATTRIBUTE3
546 ,ATTRIBUTE4
547 ,ATTRIBUTE5
548 ,ATTRIBUTE6
549 ,ATTRIBUTE7
550 ,ATTRIBUTE8
551 ,ATTRIBUTE9
552 ,ATTRIBUTE10
553 ,ATTRIBUTE11
554 ,ATTRIBUTE12
555 ,ATTRIBUTE13
556 ,ATTRIBUTE14
557 ,ATTRIBUTE15
558 ,ATTRIBUTE16 --For bug 2184255
559 ,ATTRIBUTE17
560 ,ATTRIBUTE18
561 ,ATTRIBUTE19
562 ,ATTRIBUTE20
563 ,FIRST_ACK_CODE
564 ,LAST_ACK_CODE
565 ,FIRST_ACK_DATE
566 ,LAST_ACK_DATE
567 ,BUYER_SELLER_FLAG
568 ,BOOKED_FLAG
569 ,CANCELLED_FLAG
570 -- ,CLOSED_FLAG
571 ,CHANGE_DATE
572 ,CHANGE_SEQUENCE
573 ,CONTEXT
574 ,CONVERSION_RATE
575 ,CONVERSION_RATE_DATE
576 ,CONVERSION_TYPE
577 ,CONVERSION_TYPE_CODE
578 ,CREATED_BY
579 ,CREATION_DATE
580 ,CUST_PO_NUMBER
581 -- ,CUSTOMER_ID
582 ,CUSTOMER_NAME
583 ,CUSTOMER_NUMBER
584 -- ,DELIVER_TO_CONTACT
585 ,DELIVER_TO_CONTACT_ID
586 ,DELIVER_TO_CUSTOMER
587 ,DELIVER_TO_CUSTOMER_NUMBER
588 -- ,DELIVER_TO_ORG
589 ,DELIVER_TO_ORG_ID
590 -- ,DEMAND_CLASS
591 ,DEMAND_CLASS_CODE
592 ,EARLIEST_SCHEDULE_LIMIT
593 -- ,ERROR_FLAG
594 ,EXPIRATION_DATE
595 ,FOB_POINT
596 ,FOB_POINT_CODE
597 ,FREIGHT_CARRIER_CODE
598 ,FREIGHT_TERMS
599 ,FREIGHT_TERMS_CODE
600 ,GLOBAL_ATTRIBUTE_CATEGORY
601 ,GLOBAL_ATTRIBUTE1
602 ,GLOBAL_ATTRIBUTE10
603 ,GLOBAL_ATTRIBUTE11
604 ,GLOBAL_ATTRIBUTE12
605 ,GLOBAL_ATTRIBUTE13
606 ,GLOBAL_ATTRIBUTE14
607 ,GLOBAL_ATTRIBUTE15
608 ,GLOBAL_ATTRIBUTE16
609 ,GLOBAL_ATTRIBUTE17
610 ,GLOBAL_ATTRIBUTE18
611 ,GLOBAL_ATTRIBUTE19
612 ,GLOBAL_ATTRIBUTE2
613 ,GLOBAL_ATTRIBUTE20
614 ,GLOBAL_ATTRIBUTE3
615 ,GLOBAL_ATTRIBUTE4
616 ,GLOBAL_ATTRIBUTE5
617 ,GLOBAL_ATTRIBUTE6
618 ,GLOBAL_ATTRIBUTE7
619 ,GLOBAL_ATTRIBUTE8
620 ,GLOBAL_ATTRIBUTE9
621 -- ,TP_CONTEXT
622 -- ,TP_ATTRIBUTE1
623 -- ,TP_ATTRIBUTE2
624 -- ,TP_ATTRIBUTE3
625 -- ,TP_ATTRIBUTE4
626 -- ,TP_ATTRIBUTE5
627 -- ,TP_ATTRIBUTE6
628 -- ,TP_ATTRIBUTE7
629 -- ,TP_ATTRIBUTE8
630 -- ,TP_ATTRIBUTE9
631 -- ,TP_ATTRIBUTE10
632 -- ,TP_ATTRIBUTE11
633 -- ,TP_ATTRIBUTE12
634 -- ,TP_ATTRIBUTE13
635 -- ,TP_ATTRIBUTE14
636 -- ,TP_ATTRIBUTE15
637 ,HEADER_ID
638 -- ,HEADER_PO_CONTEXT
639 -- ,INTERFACE_STATUS
640 ,INVOICE_ADDRESS_1
641 ,INVOICE_ADDRESS_2
642 ,INVOICE_ADDRESS_3
646 ,INVOICE_COUNTY
643 ,INVOICE_ADDRESS_4
644 ,INVOICE_CITY
645 ,INVOICE_COUNTRY
647 ,INVOICE_POSTAL_CODE
648 ,INVOICE_PROVINCE_INT
649 -- ,INVOICE_SITE
650 ,INVOICE_SITE_CODE
651 ,INVOICE_STATE
652 -- ,INVOICE_TO_CONTACT
653 ,INVOICE_TO_CONTACT_FIRST_NAME
654 ,INVOICE_TO_CONTACT_ID
655 ,INVOICE_TO_CONTACT_LAST_NAME
656 ,INVOICE_TO_ORG
657 ,INVOICE_TO_ORG_ID
658 -- ,INVOICE_TOLERANCE_ABOVE
659 -- ,INVOICE_TOLERANCE_BELOW
660 ,INVOICING_RULE
661 ,INVOICING_RULE_ID
662 ,LAST_UPDATE_DATE
663 ,LAST_UPDATE_LOGIN
664 ,LAST_UPDATED_BY
665 ,LATEST_SCHEDULE_LIMIT
666 ,OPEN_FLAG
667 -- ,OPERATION_CODE
668 ,ORDER_DATE_TYPE_CODE
669 ,ORDER_NUMBER
670 ,ORDER_SOURCE
671 ,ORDER_SOURCE_ID
672 ,ORDER_TYPE
673 ,ORDER_TYPE_ID
674 -- ,ORDERED_BY_CONTACT_FIRST_NAME
675 -- ,ORDERED_BY_CONTACT_LAST_NAME
676 ,ORDERED_DATE
677 ,ORG_ID
678 ,ORIG_SYS_DOCUMENT_REF
679 ,PACKING_INSTRUCTIONS
680 ,PARTIAL_SHIPMENTS_ALLOWED
681 ,PAYMENT_TERM
682 ,PAYMENT_TERM_ID
683 -- ,PO_ATTRIBUTE_1
684 -- ,PO_ATTRIBUTE_2
685 -- ,PO_ATTRIBUTE_3
686 -- ,PO_ATTRIBUTE_4
687 -- ,PO_ATTRIBUTE_5
688 -- ,PO_ATTRIBUTE_6
689 -- ,PO_ATTRIBUTE_7
690 -- ,PO_ATTRIBUTE_8
691 -- ,PO_ATTRIBUTE_9
692 -- ,PO_ATTRIBUTE_10
693 -- ,PO_ATTRIBUTE_11
694 -- ,PO_ATTRIBUTE_12
695 -- ,PO_ATTRIBUTE_13
696 -- ,PO_ATTRIBUTE_14
697 -- ,PO_ATTRIBUTE_15
698 -- ,PO_REVISION_DATE
699 ,PRICE_LIST
700 ,PRICE_LIST_ID
701 ,PRICING_DATE
702 ,PROGRAM
703 ,PROGRAM_APPLICATION
704 ,PROGRAM_APPLICATION_ID
705 ,PROGRAM_ID
706 ,PROGRAM_UPDATE_DATE
707 -- ,RELATED_PO_NUMBER
708 -- ,REMAINDER_ORDERS_ALLOWED
709 ,REQUEST_DATE
710 ,REQUEST_ID
711 ,RETURN_REASON_CODE
712 ,SALESREP
713 ,SALESREP_ID
714 ,SHIP_FROM_ORG
715 ,SHIP_FROM_ORG_ID
716 ,SHIP_TO_ADDRESS_1
717 ,SHIP_TO_ADDRESS_2
718 ,SHIP_TO_ADDRESS_3
719 ,SHIP_TO_ADDRESS_4
720 ,SHIP_TO_CITY
721 ,SHIP_TO_CONTACT
722 ,SHIP_TO_CONTACT_FIRST_NAME
723 ,SHIP_TO_CONTACT_ID
724 ,SHIP_TO_CONTACT_LAST_NAME
725 ,SHIP_TO_COUNTRY
726 ,SHIP_TO_COUNTY
727 -- ,SHIP_TO_CUSTOMER
728 -- ,SHIP_TO_CUSTOMER_NUMBER
729 ,SHIP_TO_ORG
730 ,SHIP_TO_ORG_ID
731 ,SHIP_TO_POSTAL_CODE
732 ,SHIP_TO_PROVINCE
733 -- ,SHIP_TO_SITE_INT
734 ,SHIP_TO_STATE
735 ,SHIP_TOLERANCE_ABOVE
736 ,SHIP_TOLERANCE_BELOW
737 -- ,SHIPMENT_PRIORITY
738 ,SHIPMENT_PRIORITY_CODE
739 -- ,SHIPMENT_PRIORITY_CODE_INT
740 ,SHIPPING_INSTRUCTIONS
741 -- ,SHIPPING_METHOD
742 ,SHIPPING_METHOD_CODE
743 ,SOLD_FROM_ORG
744 ,SOLD_FROM_ORG_ID
745 ,SOLD_TO_CONTACT
746 ,SOLD_TO_CONTACT_ID
747 ,SOLD_TO_ORG
748 ,SOLD_TO_ORG_ID
749 ,SOURCE_DOCUMENT_ID
750 ,SOURCE_DOCUMENT_TYPE_ID
751 -- ,SUBMISSION_DATETIME
752 ,TAX_EXEMPT_FLAG
753 ,TAX_EXEMPT_NUMBER
754 ,TAX_EXEMPT_REASON
755 ,TAX_EXEMPT_REASON_CODE
756 ,TAX_POINT
757 ,TAX_POINT_CODE
758 -- ,TRANSACTIONAL_CURR
759 ,TRANSACTIONAL_CURR_CODE
760 ,VERSION_NUMBER
761 ,ship_to_edi_location_code
762 ,sold_to_edi_location_code
763 ,BILL_TO_EDI_LOCATION_CODE
764 ,Customer_payment_term
765 ,SOLD_TO_ADDRESS1
766 ,SOLD_TO_ADDRESS2
767 ,SOLD_TO_ADDRESS3
768 ,SOLD_TO_ADDRESS4
769 ,SOLD_TO_CITY
770 ,SOLD_TO_POSTAL_CODE
771 ,SOLD_TO_COUNTRY
772 ,SOLD_TO_STATE
773 ,SOLD_TO_COUNTY
774 ,SOLD_TO_PROVINCE
775 ,SOLD_TO_CONTACT_LAST_NAME
776 ,SOLD_TO_CONTACT_FIRST_NAME
777 ,ORDER_CATEGORY_CODE
778 ,ship_from_edi_location_code
779 ,SHIP_FROM_ADDRESS_1
780 ,SHIP_FROM_ADDRESS_2
781 ,SHIP_FROM_ADDRESS_3
782 ,SHIP_FROM_CITY
783 ,SHIP_FROM_POSTAL_CODE
784 ,SHIP_FROM_COUNTRY
785 ,SHIP_FROM_REGION1
786 ,SHIP_FROM_REGION2
787 ,SHIP_FROM_REGION3
788 ,SHIP_FROM_ADDRESS_ID
789 ,SOLD_TO_ADDRESS_ID
790 ,SHIP_TO_ADDRESS_ID
791 ,INVOICE_ADDRESS_ID
792 ,SHIP_TO_ADDRESS_CODE
793 ,XML_MESSAGE_ID
794 ,SOLD_TO_SITE_USE_ID
795 ,SOLD_TO_LOCATION_ADDRESS1
796 ,SOLD_TO_LOCATION_ADDRESS2
797 ,SOLD_TO_LOCATION_ADDRESS3
798 ,SOLD_TO_LOCATION_ADDRESS4
799 ,SOLD_TO_LOCATION_CITY
800 ,SOLD_TO_LOCATION_POSTAL_CODE
801 ,SOLD_TO_LOCATION_COUNTRY
802 ,SOLD_TO_LOCATION_STATE
803 ,SOLD_TO_LOCATION_COUNTY
804 ,SOLD_TO_LOCATION_PROVINCE
805 )
806 SELECT /*+ PUSH_PRED(salesrep) */
807 accrule.NAME -- ACCOUNTING_RULE
808 , h.ACCOUNTING_RULE_ID
809 , h.ACCOUNTING_RULE_DURATION
813 ,'' -- AGREEMENT_NAME
810 ,'' -- acknowledgment_flag
811 , agr.NAME -- AGREEMENT
812 , h.AGREEMENT_ID
814 , h.ATTRIBUTE1
815 , h.ATTRIBUTE2
816 , h.ATTRIBUTE3
817 , h.ATTRIBUTE4
818 , h.ATTRIBUTE5
819 , h.ATTRIBUTE6
820 , h.ATTRIBUTE7
821 , h.ATTRIBUTE8
822 , h.ATTRIBUTE9
823 , h.ATTRIBUTE10
824 , h.ATTRIBUTE11
825 , h.ATTRIBUTE12
826 , h.ATTRIBUTE13
827 , h.ATTRIBUTE14
828 , h.ATTRIBUTE15
829 , h.ATTRIBUTE16 -- for bug 2184255
830 , h.ATTRIBUTE17
831 , h.ATTRIBUTE18
832 , h.ATTRIBUTE19
833 , h.ATTRIBUTE20
834 , decode(h.order_source_id,20,'0','AT') -- FIRST_ACK_CODE
835 , h.LAST_ACK_CODE
836 , h.FIRST_ACK_DATE
837 , h.LAST_ACK_DATE
838 , 'B' -- BUYER_SELLER_FLAG
839 , h.BOOKED_FLAG
840 , h.CANCELLED_FLAG
841 , '' -- CHANGE_DATE
842 , h.CHANGE_SEQUENCE
843 , h.CONTEXT
844 , h.CONVERSION_RATE
845 , h.CONVERSION_RATE_DATE
846 , conv_type.USER_CONVERSION_TYPE -- CONVERSION_TYPE
847 , h.CONVERSION_TYPE_CODE
848 , h.CREATED_BY
849 , h.CREATION_DATE
850 , h.CUST_PO_NUMBER
851 , NULL -- INVOICE_TO_CUSTOMER_NAME
852 , NULL -- INVOICE_TO_CUSTOMER_NUMBER
853 , h.DELIVER_TO_CONTACT_ID
854 , NULL -- l_header_val_rec.DELIVER_TO_CUSTOMER_NAME
855 , NULL -- l_header_val_rec.DELIVER_TO_CUSTOMER_NUMBER
856 , h.DELIVER_TO_ORG_ID
857 , h.DEMAND_CLASS_CODE
858 , h.EARLIEST_SCHEDULE_LIMIT
859 , h.EXPIRATION_DATE
860 , fob.MEANING -- FOB_POINT
861 , h.FOB_POINT_CODE
862 , h.FREIGHT_CARRIER_CODE
863 , ft.MEANING -- FREIGHT_TERMS
864 , h.FREIGHT_TERMS_CODE
865 , h.GLOBAL_ATTRIBUTE_CATEGORY
866 , h.GLOBAL_ATTRIBUTE1
867 , h.GLOBAL_ATTRIBUTE10
868 , h.GLOBAL_ATTRIBUTE11
869 , h.GLOBAL_ATTRIBUTE12
870 , h.GLOBAL_ATTRIBUTE13
871 , h.GLOBAL_ATTRIBUTE14
872 , h.GLOBAL_ATTRIBUTE15
873 , h.GLOBAL_ATTRIBUTE16
874 , h.GLOBAL_ATTRIBUTE17
875 , h.GLOBAL_ATTRIBUTE18
876 , h.GLOBAL_ATTRIBUTE19
877 , h.GLOBAL_ATTRIBUTE2
878 , h.GLOBAL_ATTRIBUTE20
879 , h.GLOBAL_ATTRIBUTE3
880 , h.GLOBAL_ATTRIBUTE4
881 , h.GLOBAL_ATTRIBUTE5
882 , h.GLOBAL_ATTRIBUTE6
883 , h.GLOBAL_ATTRIBUTE7
884 , h.GLOBAL_ATTRIBUTE8
885 , h.GLOBAL_ATTRIBUTE9
886 -- h.TP_CONTEXT
887 -- h.TP_ATTRIBUTE1
888 -- h.TP_ATTRIBUTE2
889 -- h.TP_ATTRIBUTE3
890 -- h.TP_ATTRIBUTE4
891 -- h.TP_ATTRIBUTE5
892 -- h.TP_ATTRIBUTE6
893 -- h.TP_ATTRIBUTE7
894 -- h.TP_ATTRIBUTE8
895 -- h.TP_ATTRIBUTE9
896 -- h.TP_ATTRIBUTE10
897 -- h.TP_ATTRIBUTE11
898 -- h.TP_ATTRIBUTE12
899 -- h.TP_ATTRIBUTE13
900 -- h.TP_ATTRIBUTE14
901 -- h.TP_ATTRIBUTE15
902 , h.HEADER_ID--number
903 , Get_Address1(h.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_ADDRESS1
904 , Get_Address2(h.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_ADDRESS2
905 , Get_Address3(h.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_ADDRESS3
906 , Get_Address4(h.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_ADDRESS4
907 , Get_City(h.invoice_to_org_id,'BILL_TO') -- INVOICE_to_city
908 , Get_Country(h.invoice_to_org_id,'BILL_TO') -- INVOICE_to_country
909 , Get_Country(h.invoice_to_org_id,'BILL_TO') -- INVOICE_to_county
910 , Get_Zip(h.invoice_to_org_id,'BILL_TO') -- INVOICE_to_zip
911 , Get_Province(h.invoice_to_org_id,'BILL_TO') -- INVOICE_to_province
912 , Get_Location(h.invoice_to_org_id,'BILL_TO') -- INVOICE_to_location
913 , Get_State(h.invoice_to_org_id,'BILL_TO') -- INVOICE_to_state
914 , inv_party.PERSON_first_name -- invoice_to_contact_first_name
915 , h.INVOICE_TO_CONTACT_ID
916 , inv_party.PERSON_last_name -- invoice_to_contact_last_name
917 , Get_Location(h.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_ORG
918 , h.INVOICE_TO_ORG_ID
919 , invrule.NAME -- INVOICING_RULE
920 , h.INVOICING_RULE_ID
921 , h.LAST_UPDATE_DATE
922 , h.LAST_UPDATE_LOGIN
923 , h.LAST_UPDATED_BY
924 , h.LATEST_SCHEDULE_LIMIT
925 , h.OPEN_FLAG
926 , h.ORDER_DATE_TYPE_CODE
927 , h.ORDER_NUMBER
928 , os.NAME -- ORDER_SOURCE
929 , h.ORDER_SOURCE_ID
930 , ot.NAME -- ORDER_TYPE
931 , h.ORDER_TYPE_ID
932 , h.ORDERED_DATE
933 , h.ORG_ID
934 , h.ORIG_SYS_DOCUMENT_REF
935 , h.PACKING_INSTRUCTIONS
936 , h.PARTIAL_SHIPMENTS_ALLOWED
937 , term.NAME -- PAYMENT_TERM
938 , h.PAYMENT_TERM_ID
939 , pl.NAME -- PRICE_LIST
940 , h.PRICE_LIST_ID
941 , h.PRICING_DATE
942 , '' -- PROGRAM
943 , '' -- PROGRAM_APPLICATION
944 , h.PROGRAM_APPLICATION_ID
945 , h.PROGRAM_ID
949 , h.RETURN_REASON_CODE
946 , h.PROGRAM_UPDATE_DATE
947 , h.REQUEST_DATE
948 , h.REQUEST_ID
950 , salesrep.NAME -- SALESREP
951 , h.SALESREP_ID
952 , Get_Location(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_ORG
953 , h.SHIP_FROM_ORG_ID
954 , Get_Address1(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_address1
955 , Get_Address2(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_address2
956 , Get_Address3(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_address3
957 , Get_Address4(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_address4
958 , Get_City(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_city
959 , Get_Address1(h.ship_to_org_id,'SHIP_TO') -- SHIP_TO_CONTACT
960 , ship_party.PERSON_FIRST_NAME -- SHIP_TO_CONTACT_FIRST_NAME
961 , h.SHIP_TO_CONTACT_ID
962 , ship_party.PERSON_LAST_NAME -- SHIP_TO_CONTACT_LAST_NAME
963 , Get_Country(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_country
964 , Get_County(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_county
965 , Get_Location(h.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ORG
966 , h.SHIP_TO_ORG_ID
967 , Get_Zip(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_zip
968 , Get_Province(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_province
969 , Get_State(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_state
970 , h.SHIP_TOLERANCE_ABOVE
971 , h.SHIP_TOLERANCE_BELOW
972 , h.SHIPMENT_PRIORITY_CODE
973 , h.SHIPPING_INSTRUCTIONS
974 , h.SHIPPING_METHOD_CODE
975 , '' -- SOLD_FROM_ORG
976 , '' -- SOLD_FROM_ORG_ID
977 , Get_Address1(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_CONTACT
978 , h.SOLD_TO_CONTACT_ID
979 , Get_Address1(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_ORG
980 , h.SOLD_TO_ORG_ID
981 , h.SOURCE_DOCUMENT_ID
982 , h.SOURCE_DOCUMENT_TYPE_ID
983 , h.TAX_EXEMPT_FLAG
984 , h.TAX_EXEMPT_NUMBER
985 , tax_exempt_reason.MEANING -- TAX_EXEMPT_REASON
986 , h.TAX_EXEMPT_REASON_CODE
987 , tax_point.MEANING -- TAX_POINT
988 , h.TAX_POINT_CODE
989 , h.TRANSACTIONAL_CURR_CODE
990 , h.VERSION_NUMBER
991 , Get_EDI_Location(h.ship_to_org_id,'SHIP_TO') -- SHIP_to_edi_location_code
992 , Get_EDI_Location(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_EDI_LOCATION_CODE
993 , Get_EDI_Location(h.invoice_to_org_id,'BILL_TO') -- BILL_TO_EDI_LOCATION_CODE
994 , null -- CUSTOMER_PAYMENT_TERM
995 , Get_Address1(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_ADDRESS1
996 , Get_Address2(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_ADDRESS2
997 , Get_Address3(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_ADDRESS3
998 , Get_Address4(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_ADDRESS4
999 , Get_City(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_CITY
1000 , Get_Zip(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_ZIP
1001 , Get_Country(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_COUNTRY
1002 , Get_State(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_STATE
1003 , Get_County(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_COUNTY
1004 , Get_Province(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_PROVINCE
1005 , sold_party.PERSON_LAST_NAME -- SOLD_TO_CONTACT_LAST_NAME
1006 , sold_party.PERSON_FIRST_NAME -- SOLD_TO_CONTACT_FIRST_NAME
1007 , h.ORDER_CATEGORY_CODE
1008 , Get_EDI_Location(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_edi_location_code
1009 , Get_Address1(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_ADDRESS1
1010 , Get_Address2(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_ADDRESS2
1011 , Get_Address3(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_ADDRESS3
1012 , Get_City(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_CITY
1013 , Get_Zip(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_POSTAL_CODE
1014 , Get_Country(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_COUNTRY
1015 , NULL -- SHIP_FROM_REGION1
1016 , NULL -- SHIP_FROM_REGION2
1017 , NULL -- SHIP_FROM_REGION3
1018 , Get_Address_ID(h.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_ADDRESS_ID
1019 , Get_Address_ID(h.sold_to_org_id,'SOLD_TO') -- SOLD_TO_ADDRESS_ID
1020 , Get_Address_ID(h.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ADDRESS_ID
1021 , Get_Address_ID(h.invoice_to_org_id,'BILL_TO') -- INVOICE_ADDRESS_ID
1022 , Get_Location(h.ship_to_org_id,'SHIP_TO') -- SHIP_TO_LOCATION
1023 , h.XML_MESSAGE_ID
1024 , h.SOLD_TO_SITE_USE_ID
1025 , Get_Address1(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1026 , Get_Address2(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1027 , Get_Address3(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1028 , Get_Address4(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1029 , Get_City(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1030 , Get_Zip(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1031 , Get_Country(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1032 , Get_State(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1033 , Get_County(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1034 , Get_Province(h.sold_to_site_use_id, 'SOLD_TO_SITE')
1035 FROM OE_ORDER_HEADERS_ALL h
1036 , OE_ORDER_SOURCES os
1037 , OE_AGREEMENTS_V agr
1038 , OE_AR_LOOKUPS_V fob
1039 , OE_LOOKUPS ft
1040 , HZ_PARTIES sold_party
1041 , HZ_RELATIONSHIPS sold_rel
1042 , HZ_CUST_ACCOUNT_ROLES sold_role
1046 , HZ_CUST_ACCOUNT_ROLES ship_role
1043 , HZ_CUST_ACCOUNTS sold_acct
1044 , HZ_PARTIES ship_party
1045 , HZ_RELATIONSHIPS ship_rel
1047 , HZ_CUST_ACCOUNTS ship_acct
1048 , HZ_PARTIES inv_party
1049 , HZ_RELATIONSHIPS inv_rel
1050 , HZ_CUST_ACCOUNT_ROLES inv_role
1051 , HZ_CUST_ACCOUNTS inv_acct
1052 , RA_SALESREPS salesrep
1053 , OE_TRANSACTION_TYPES_TL ot
1054 , QP_LIST_HEADERS_TL pl
1055 , RA_TERMS_TL term
1056 , RA_RULES accrule
1057 , RA_RULES invrule
1058 , OE_AR_LOOKUPS_V tax_exempt_reason
1059 , OE_LOOKUPS tax_point
1060 , OE_GL_DAILY_CONVERSION_TYPES_V conv_type
1061 WHERE header_id = p_header_id
1062 AND os.order_source_id(+) = h.order_source_id
1063 AND agr.agreement_id(+) = h.agreement_id
1064 AND fob.lookup_type(+) = 'FOB'
1065 AND fob.lookup_code(+) = h.fob_point_code
1066 AND ft.lookup_type(+) = 'FREIGHT_TERMS'
1067 AND ft.lookup_code(+) = h.freight_terms_code
1068 AND salesrep.salesrep_id(+) = h.salesrep_id
1069 AND ot.transaction_type_id = h.order_type_id
1070 AND ot.language = userenv('LANG')
1071 AND pl.list_header_id(+) = h.price_list_id
1072 AND pl.language(+) = userenv('LANG')
1073 AND term.term_id(+) = h.payment_term_id
1074 AND term.language(+) = userenv('LANG')
1075 AND sold_role.cust_account_role_id(+) = h.sold_to_contact_id
1076 AND sold_role.role_type(+) = 'CONTACT'
1077 AND sold_role.cust_account_id(+) = h.sold_to_org_id
1078 AND sold_rel.party_id(+) = sold_role.party_id
1079 AND sold_party.party_id(+) = sold_rel.subject_id
1080 AND sold_acct.cust_account_id(+) = sold_role.cust_account_id
1081 AND nvl(sold_acct.party_id,1) = nvl(sold_rel.object_id,1)
1082 AND ship_role.cust_account_role_id(+) = h.ship_to_contact_id
1083 AND ship_role.role_type(+) = 'CONTACT'
1084 AND ship_role.cust_account_id(+) = h.ship_to_org_id
1085 AND ship_rel.party_id(+) = ship_role.party_id
1086 AND ship_party.party_id(+) = ship_rel.subject_id
1087 AND ship_acct.cust_account_id(+) = ship_role.cust_account_id
1088 AND nvl(ship_acct.party_id,1) = nvl(ship_rel.object_id,1)
1089 AND inv_role.cust_account_role_id(+) = h.invoice_to_contact_id
1090 AND inv_role.role_type(+) = 'CONTACT'
1091 AND inv_role.cust_account_id(+) = h.invoice_to_org_id
1092 AND inv_rel.party_id(+) = inv_role.party_id
1093 AND inv_party.party_id(+) = inv_rel.subject_id
1094 AND inv_acct.cust_account_id(+) = inv_role.cust_account_id
1095 AND nvl(inv_acct.party_id,1) = nvl(inv_rel.object_id,1)
1096 AND accrule.rule_id(+) = h.accounting_rule_id
1097 AND invrule.rule_id(+) = h.invoicing_rule_id
1098 AND tax_exempt_reason.lookup_type(+) = 'TAX_EXEMPT_REASON'
1099 AND tax_exempt_reason.lookup_code(+) = h.tax_exempt_reason_code
1100 AND tax_point.lookup_type(+) = 'TAX_POINT'
1101 AND tax_point.lookup_code(+) = h.tax_point_code
1102 AND conv_type.conversion_type(+) = h.conversion_type_code
1103 ;
1104
1105 END Insert_Header_Acks;
1106
1107 PROCEDURE Insert_Line_Acks
1108 (p_header_id IN NUMBER
1109 )
1110 IS
1111 BEGIN
1112
1113 INSERT INTO OE_LINE_ACKS
1114 (
1115 ACCOUNTING_RULE
1116 ,ACCOUNTING_RULE_ID
1117 ,ACCOUNTING_RULE_DURATION
1118 ,ACKNOWLEDGMENT_FLAG
1119 ,ACTUAL_ARRIVAL_DATE
1120 ,ACTUAL_SHIPMENT_DATE
1121 ,AGREEMENT
1122 ,AGREEMENT_ID
1123 ,ARRIVAL_SET_ID
1124 -- ,ARRIVAL_SET_NAME
1125 ,ATO_LINE_ID
1126 ,ATTRIBUTE1
1127 ,ATTRIBUTE10
1128 ,ATTRIBUTE11
1129 ,ATTRIBUTE12
1130 ,ATTRIBUTE13
1131 ,ATTRIBUTE14
1132 ,ATTRIBUTE15
1133 ,ATTRIBUTE16 --For bug 2184255
1134 ,ATTRIBUTE17
1135 ,ATTRIBUTE18
1136 ,ATTRIBUTE19
1137 ,ATTRIBUTE2
1138 ,ATTRIBUTE20
1139 ,ATTRIBUTE3
1140 ,ATTRIBUTE4
1141 ,ATTRIBUTE5
1142 ,ATTRIBUTE6
1143 ,ATTRIBUTE7
1144 ,ATTRIBUTE8
1145 ,ATTRIBUTE9
1146 ,AUTHORIZED_TO_SHIP_FLAG
1147 ,BUYER_SELLER_FLAG
1148 ,BOOKED_FLAG
1149 -- ,CALCULATE_PRICE_FLAG
1150 ,CANCELLED_FLAG
1151 ,CANCELLED_QUANTITY
1152 ,CHANGE_DATE
1153 ,CHANGE_SEQUENCE
1154 -- ,CLOSED_FLAG
1155 ,COMPONENT_CODE
1156 ,COMPONENT_NUMBER
1157 ,COMPONENT_SEQUENCE_ID
1158 ,CONFIG_DISPLAY_SEQUENCE
1159 -- ,CONFIG_LINE_REF
1160 ,CONFIGURATION_ID
1161 ,TOP_MODEL_LINE_ID
1162 ,CONTEXT
1163 ,CREATED_BY
1164 ,CREATION_DATE
1165 ,CUST_MODEL_SERIAL_NUMBER
1166 ,CUST_PO_NUMBER
1167 ,CUST_PRODUCTION_SEQ_NUM
1168 ,CUSTOMER_DOCK_CODE
1172 , CUSTOMER_JOB
1169 ,CUSTOMER_ITEM
1170 ,CUSTOMER_ITEM_ID -- Bug 4776870
1171 -- ,CUSTOMER_ITEM_REVISION 11/03
1173 ,CUSTOMER_PRODUCTION_LINE
1174 ,CUSTOMER_TRX_LINE_ID
1175 -- ,DELIVER_TO_CONTACT
1176 ,DELIVER_TO_CONTACT_ID
1177 ,DELIVER_TO_ORG
1178 ,DELIVER_TO_ORG_ID
1179 ,DELIVERY_LEAD_TIME
1180 ,DEMAND_BUCKET_TYPE
1181 ,DEMAND_BUCKET_TYPE_CODE
1182 -- ,DEMAND_CLASS
1183 ,DEMAND_CLASS_CODE
1184 -- ,DEMAND_STREAM
1185 ,DEP_PLAN_REQUIRED_FLAG
1186 -- ,DPW_ASSIGNED_FLAG
1187 ,EARLIEST_ACCEPTABLE_DATE
1188 ,EXPLOSION_DATE
1189 ,FIRST_ACK_CODE
1190 ,FIRST_ACK_DATE
1191 ,FOB_POINT
1192 ,FOB_POINT_CODE
1193 ,FREIGHT_CARRIER_CODE
1194 ,FREIGHT_TERMS
1195 ,FREIGHT_TERMS_CODE
1196 ,FULFILLED_QUANTITY
1197 -- ,FULFILLMENT_SET_ID
1198 -- ,FULFILLMENT_SET_NAME
1199 ,GLOBAL_ATTRIBUTE_CATEGORY
1200 ,GLOBAL_ATTRIBUTE1
1201 ,GLOBAL_ATTRIBUTE10
1202 ,GLOBAL_ATTRIBUTE11
1203 ,GLOBAL_ATTRIBUTE12
1204 ,GLOBAL_ATTRIBUTE13
1205 ,GLOBAL_ATTRIBUTE14
1206 ,GLOBAL_ATTRIBUTE15
1207 ,GLOBAL_ATTRIBUTE16
1208 ,GLOBAL_ATTRIBUTE17
1209 ,GLOBAL_ATTRIBUTE18
1210 ,GLOBAL_ATTRIBUTE19
1211 ,GLOBAL_ATTRIBUTE2
1212 ,GLOBAL_ATTRIBUTE20
1213 ,GLOBAL_ATTRIBUTE3
1214 ,GLOBAL_ATTRIBUTE4
1215 ,GLOBAL_ATTRIBUTE5
1216 ,GLOBAL_ATTRIBUTE6
1217 ,GLOBAL_ATTRIBUTE7
1218 ,GLOBAL_ATTRIBUTE8
1219 ,GLOBAL_ATTRIBUTE9
1220 ,HEADER_ID
1221 ,INDUSTRY_ATTRIBUTE1
1222 ,INDUSTRY_ATTRIBUTE10
1223 ,INDUSTRY_ATTRIBUTE11
1224 ,INDUSTRY_ATTRIBUTE12
1225 ,INDUSTRY_ATTRIBUTE13
1226 ,INDUSTRY_ATTRIBUTE14
1227 ,INDUSTRY_ATTRIBUTE15
1228 ,INDUSTRY_ATTRIBUTE16
1229 ,INDUSTRY_ATTRIBUTE17
1230 ,INDUSTRY_ATTRIBUTE18
1231 ,INDUSTRY_ATTRIBUTE19
1232 ,INDUSTRY_ATTRIBUTE2
1233 ,INDUSTRY_ATTRIBUTE20
1234 ,INDUSTRY_ATTRIBUTE21
1235 ,INDUSTRY_ATTRIBUTE22
1236 ,INDUSTRY_ATTRIBUTE23
1237 ,INDUSTRY_ATTRIBUTE24
1238 ,INDUSTRY_ATTRIBUTE25
1239 ,INDUSTRY_ATTRIBUTE26
1240 ,INDUSTRY_ATTRIBUTE27
1241 ,INDUSTRY_ATTRIBUTE28
1242 ,INDUSTRY_ATTRIBUTE29
1243 ,INDUSTRY_ATTRIBUTE3
1244 ,INDUSTRY_ATTRIBUTE30
1245 ,INDUSTRY_ATTRIBUTE4
1246 ,INDUSTRY_ATTRIBUTE5
1247 ,INDUSTRY_ATTRIBUTE6
1248 ,INDUSTRY_ATTRIBUTE7
1249 ,INDUSTRY_ATTRIBUTE8
1250 ,INDUSTRY_ATTRIBUTE9
1251 ,INDUSTRY_CONTEXT
1252 ,TP_CONTEXT
1253 ,TP_ATTRIBUTE1
1254 ,TP_ATTRIBUTE2
1255 ,TP_ATTRIBUTE3
1256 ,TP_ATTRIBUTE4
1257 ,TP_ATTRIBUTE5
1258 ,TP_ATTRIBUTE6
1259 ,TP_ATTRIBUTE7
1260 ,TP_ATTRIBUTE8
1261 ,TP_ATTRIBUTE9
1262 ,TP_ATTRIBUTE10
1263 ,TP_ATTRIBUTE11
1264 ,TP_ATTRIBUTE12
1265 ,TP_ATTRIBUTE13
1266 ,TP_ATTRIBUTE14
1267 ,TP_ATTRIBUTE15
1268 ,INTMED_SHIP_TO_CONTACT_ID
1269 ,INTMED_SHIP_TO_ORG_ID
1270 ,INVENTORY_ITEM
1271 ,INVENTORY_ITEM_ID
1272 -- ,INVOICE_COMPLETE_FLAG 11/03
1273 -- ,INVOICE_SET_ID
1274 -- ,INVOICE_SET_NAME
1275 -- ,INVOICE_NUMBER
1276 ,INVOICE_TO_CONTACT
1277 ,INVOICE_TO_CONTACT_ID
1278 ,INVOICE_TO_ORG
1279 ,INVOICE_TO_ORG_ID
1280 -- ,INVOICE_TOLERANCE_ABOVE
1281 -- ,INVOICE_TOLERANCE_BELOW
1282 ,INVOICING_RULE
1283 ,INVOICING_RULE_ID
1284 ,ITEM_INPUT
1285 ,ITEM_REVISION
1286 ,ITEM_TYPE_CODE
1287 ,LAST_ACK_CODE
1288 ,LAST_ACK_DATE
1289 ,LAST_UPDATE_DATE
1290 ,LAST_UPDATE_LOGIN
1291 ,LAST_UPDATED_BY
1292 ,LATEST_ACCEPTABLE_DATE
1293 ,LINE_CATEGORY_CODE
1294 ,LINE_ID
1295 ,LINE_NUMBER
1296 -- ,LINE_PO_CONTEXT
1297 ,LINE_TYPE
1298 ,LINE_TYPE_ID
1299 ,LINK_TO_LINE_ID
1300 -- ,LINK_TO_LINE_REF
1301 -- ,LOT
1302 ,MODEL_GROUP_NUMBER
1303 ,OPEN_FLAG
1304 -- ,OPERATION_CODE
1305 ,OPTION_FLAG
1306 ,OPTION_NUMBER
1307 ,ORDER_QUANTITY_UOM
1308 ,ORDER_SOURCE_ID
1309 ,ORDERED_QUANTITY
1310 ,ORG_ID
1311 ,ORIG_SYS_DOCUMENT_REF
1312 ,ORIG_SYS_LINE_REF
1313 ,ORIG_SYS_SHIPMENT_REF
1314 ,OVER_SHIP_REASON_CODE
1315 ,OVER_SHIP_RESOLVED_FLAG
1316 ,PAYMENT_TERM
1317 ,PAYMENT_TERM_ID
1318 ,PRICE_LIST
1319 ,PRICE_LIST_ID
1320 ,PRICING_ATTRIBUTE1
1321 ,PRICING_ATTRIBUTE10
1322 ,PRICING_ATTRIBUTE2
1326 ,PRICING_ATTRIBUTE6
1323 ,PRICING_ATTRIBUTE3
1324 ,PRICING_ATTRIBUTE4
1325 ,PRICING_ATTRIBUTE5
1327 ,PRICING_ATTRIBUTE7
1328 ,PRICING_ATTRIBUTE8
1329 ,PRICING_ATTRIBUTE9
1330 ,PRICING_CONTEXT
1331 ,PRICING_DATE
1332 ,PRICING_QUANTITY
1333 ,PRICING_QUANTITY_UOM
1334 -- ,PROGRAM
1335 -- ,PROGRAM_APPLICATION
1336 ,PROGRAM_APPLICATION_ID
1337 ,PROGRAM_ID
1338 ,PROGRAM_UPDATE_DATE
1339 ,PROJECT
1340 ,PROJECT_ID
1341 ,PROMISE_DATE
1342 -- ,REFERENCE_HEADER
1343 ,REFERENCE_HEADER_ID
1344 -- ,REFERENCE_LINE
1345 ,REFERENCE_LINE_ID
1346 ,REFERENCE_TYPE
1347 -- ,RELATED_PO_NUMBER
1348 ,REQUEST_DATE
1349 ,REQUEST_ID
1350 -- ,RESERVED_QUANTITY
1351 ,RETURN_ATTRIBUTE1
1352 ,RETURN_ATTRIBUTE10
1353 ,RETURN_ATTRIBUTE11
1354 ,RETURN_ATTRIBUTE12
1355 ,RETURN_ATTRIBUTE13
1356 ,RETURN_ATTRIBUTE14
1357 ,RETURN_ATTRIBUTE15
1358 ,RETURN_ATTRIBUTE2
1359 ,RETURN_ATTRIBUTE3
1360 ,RETURN_ATTRIBUTE4
1361 ,RETURN_ATTRIBUTE5
1362 ,RETURN_ATTRIBUTE6
1363 ,RETURN_ATTRIBUTE7
1364 ,RETURN_ATTRIBUTE8
1365 ,RETURN_ATTRIBUTE9
1366 ,RETURN_CONTEXT
1367 ,RETURN_REASON_CODE
1368 ,RLA_SCHEDULE_TYPE_CODE
1369 ,SALESREP_ID
1370 ,SALESREP
1371 ,SCHEDULE_ARRIVAL_DATE
1372 ,SCHEDULE_SHIP_DATE
1373 -- ,SCHEDULE_ITEM_DETAIL
1374 ,SCHEDULE_STATUS_CODE
1375 ,SHIP_FROM_ORG
1376 ,SHIP_FROM_ORG_ID
1377 ,SHIP_MODEL_COMPLETE_FLAG
1378 ,SHIP_SET_ID
1379 -- ,SHIP_SET_NAME
1380 ,SHIP_TO_ADDRESS1
1381 ,SHIP_TO_ADDRESS2
1382 ,SHIP_TO_ADDRESS3
1383 ,SHIP_TO_ADDRESS4
1384 ,SHIP_TO_CITY
1385 ,SHIP_TO_CONTACT
1386 -- ,SHIP_TO_CONTACT_AREA_CODE1
1387 -- ,SHIP_TO_CONTACT_AREA_CODE2
1388 -- ,SHIP_TO_CONTACT_AREA_CODE3
1389 ,SHIP_TO_CONTACT_FIRST_NAME
1390 ,SHIP_TO_CONTACT_ID
1391 -- ,SHIP_TO_CONTACT_JOB_TITLE
1392 ,SHIP_TO_CONTACT_LAST_NAME
1393 ,SHIP_TO_COUNTRY
1394 ,SHIP_TO_COUNTY
1395 ,SHIP_TO_ORG
1396 ,SHIP_TO_ORG_ID
1397 ,SHIP_TO_POSTAL_CODE
1398 ,SHIP_TO_STATE
1399 ,SHIP_TOLERANCE_ABOVE
1400 ,SHIP_TOLERANCE_BELOW
1401 ,SHIPMENT_NUMBER
1402 ,SHIPMENT_PRIORITY
1403 ,SHIPMENT_PRIORITY_CODE
1404 ,SHIPPED_QUANTITY
1405 -- ,SHIPPING_METHOD
1406 ,SHIPPING_METHOD_CODE
1407 ,SHIPPING_QUANTITY
1408 ,SHIPPING_QUANTITY_UOM
1409 -- ,SOLD_FROM_ORG
1410 -- ,SOLD_FROM_ORG_ID
1411 ,SOLD_TO_ORG
1412 ,SOLD_TO_ORG_ID
1413 ,SORT_ORDER
1414 ,SOURCE_DOCUMENT_ID
1415 ,SOURCE_DOCUMENT_LINE_ID
1416 ,SOURCE_DOCUMENT_TYPE_ID
1417 ,SOURCE_TYPE_CODE
1418 ,SPLIT_FROM_LINE_ID
1419 -- ,SUBINVENTORY
1420 -- ,SUBMISSION_DATETIME
1421 ,TASK
1422 ,TASK_ID
1423 -- ,TAX
1424 ,TAX_CODE
1425 ,TAX_DATE
1426 ,TAX_EXEMPT_FLAG
1427 ,TAX_EXEMPT_NUMBER
1428 ,TAX_EXEMPT_REASON
1429 ,TAX_EXEMPT_REASON_CODE
1430 ,TAX_POINT
1431 ,TAX_POINT_CODE
1432 ,TAX_RATE
1433 ,TAX_VALUE
1434 ,UNIT_LIST_PRICE
1435 ,UNIT_SELLING_PRICE
1436 ,VEH_CUS_ITEM_CUM_KEY_ID
1437 ,VISIBLE_DEMAND_FLAG
1438 ,split_from_line_ref
1439 ,SHIP_TO_EDI_LOCATION_CODE
1440 ,Service_Txn_Reason_Code
1441 ,Service_Txn_Comments
1442 ,Service_Duration
1443 ,Service_Start_Date
1444 ,Service_End_Date
1445 ,Service_Coterminate_Flag
1446 ,Service_Number
1447 ,Service_Period
1448 ,Service_Reference_Type_Code
1449 ,Service_Reference_Line_Id
1450 ,Service_Reference_System_Id
1451 ,Credit_Invoice_Line_Id
1452 ,Ship_to_Province
1453 ,Invoice_Province
1454 ,Bill_to_Edi_Location_Code
1455 ,Invoice_City
1456 ,ship_from_edi_location_code
1457 ,SHIP_FROM_ADDRESS_1
1458 ,SHIP_FROM_ADDRESS_2
1459 ,SHIP_FROM_ADDRESS_3
1460 ,SHIP_FROM_CITY
1461 ,SHIP_FROM_POSTAL_CODE
1462 ,SHIP_FROM_COUNTRY
1463 ,SHIP_FROM_REGION1
1464 ,SHIP_FROM_REGION2
1465 ,SHIP_FROM_REGION3
1466 ,SHIP_FROM_ADDRESS_ID
1467 ,SHIP_TO_ADDRESS_ID
1468 ,SHIP_TO_ADDRESS_CODE
1469 ,service_reference_line
1470 ,service_reference_order
1471 ,service_reference_system
1472 -- ,order_source
1473 ,customer_line_number
1474 ,user_item_description
1475 )
1476 SELECT /*+ PUSH_PRED(salesrep) */
1480 , '' -- ACKNOWLEDGMENT_FLAG
1477 accrule.NAME -- ACCOUNTING_RULE
1478 , L.ACCOUNTING_RULE_ID
1479 , L.ACCOUNTING_RULE_DURATION
1481 , L.ACTUAL_ARRIVAL_DATE
1482 , L.ACTUAL_SHIPMENT_DATE
1483 , agr.NAME -- AGREEMENT
1484 , L.AGREEMENT_ID
1485 , L.ARRIVAL_SET_ID
1486 -- , L.ARRIVAL_SET_NAME
1487 , L.ATO_LINE_ID
1488 , L.ATTRIBUTE1
1489 , L.ATTRIBUTE10
1490 , L.ATTRIBUTE11
1491 , L.ATTRIBUTE12
1492 , L.ATTRIBUTE13
1493 , L.ATTRIBUTE14
1494 , L.ATTRIBUTE15
1495 , L.ATTRIBUTE16 --For bug 2184255
1496 , L.ATTRIBUTE17
1497 , L.ATTRIBUTE18
1498 , L.ATTRIBUTE19
1499 , L.ATTRIBUTE2
1500 , L.ATTRIBUTE20
1501 , L.ATTRIBUTE3
1502 , L.ATTRIBUTE4
1503 , L.ATTRIBUTE5
1504 , L.ATTRIBUTE6
1505 , L.ATTRIBUTE7
1506 , L.ATTRIBUTE8
1507 , L.ATTRIBUTE9
1508 , L.AUTHORIZED_TO_SHIP_FLAG
1509 , 'B' -- p_buyer_seller_flag
1510 , L.BOOKED_FLAG
1511 -- , L.CALCULATE_PRICE_FLAG
1512 , L.CANCELLED_FLAG
1513 , L.CANCELLED_QUANTITY
1514 , '' -- CHANGE_DATE
1515 , L.CHANGE_SEQUENCE
1516 -- , L.CLOSED_FLAG
1517 , L.COMPONENT_CODE
1518 , L.COMPONENT_NUMBER
1519 , L.COMPONENT_SEQUENCE_ID
1520 , L.CONFIG_DISPLAY_SEQUENCE
1521 -- , L.CONFIG_LINE_REF
1522 , L.CONFIGURATION_ID
1523 , L.TOP_MODEL_LINE_ID
1524 , L.CONTEXT
1525 , L.CREATED_BY
1526 , L.CREATION_DATE
1527 , L.CUST_MODEL_SERIAL_NUMBER
1528 , L.CUST_PO_NUMBER
1529 , L.CUST_PRODUCTION_SEQ_NUM
1530 , L.CUSTOMER_DOCK_CODE
1531 , L.ORDERED_ITEM
1532 , L.ORDERED_ITEM_ID -- Bug 4776870
1533 -- , L.CUSTOMER_ITEM_REVISION
1534 , L.CUSTOMER_JOB
1535 , L.CUSTOMER_PRODUCTION_LINE
1536 , L.CUSTOMER_TRX_LINE_ID
1537 -- , L.DELIVER_TO_CONTACT
1538 , L.DELIVER_TO_CONTACT_ID
1539 , NULL -- DELIVER_TO_ORG
1540 , L.DELIVER_TO_ORG_ID
1541 , L.DELIVERY_LEAD_TIME
1542 , NULL -- DEMAND_BUCKET_TYPE
1543 , L.DEMAND_BUCKET_TYPE_CODE
1544 -- , L.DEMAND_CLASS
1545 , L.DEMAND_CLASS_CODE
1546 -- , L.DEMAND_STREAM
1547 , L.DEP_PLAN_REQUIRED_FLAG
1548 , L.EARLIEST_ACCEPTABLE_DATE
1549 , L.EXPLOSION_DATE
1550 -- , decode(L.order_source_id,20,'0','IA') -- FIRST_ACK_CODE
1551 , decode(L.order_source_id,20,'0',
1552 decode ( L.customer_item_net_price, NULL, 'IA',
1553 FND_API.G_MISS_NUM, 'IA',
1554 L.unit_selling_price, 'IA',
1555 'IP') ) -- bug 4767509
1556 , L.FIRST_ACK_DATE
1557 , substr(ft.MEANING,1,30) -- FOB_POINT
1558 , L.FOB_POINT_CODE
1559 , L.FREIGHT_CARRIER_CODE
1560 , ft.MEANING -- FREIGHT_TERMS
1561 , L.FREIGHT_TERMS_CODE
1562 , L.FULFILLED_QUANTITY
1563 -- , L.FULFILLMENT_SET_ID
1564 -- , L.FULFILLMENT_SET_NAME
1565 , L.GLOBAL_ATTRIBUTE_CATEGORY
1566 , L.GLOBAL_ATTRIBUTE1
1567 , L.GLOBAL_ATTRIBUTE10
1568 , L.GLOBAL_ATTRIBUTE11
1569 , L.GLOBAL_ATTRIBUTE12
1570 , L.GLOBAL_ATTRIBUTE13
1571 , L.GLOBAL_ATTRIBUTE14
1572 , L.GLOBAL_ATTRIBUTE15
1573 , L.GLOBAL_ATTRIBUTE16
1574 , L.GLOBAL_ATTRIBUTE17
1575 , L.GLOBAL_ATTRIBUTE18
1576 , L.GLOBAL_ATTRIBUTE19
1577 , L.GLOBAL_ATTRIBUTE2
1578 , L.GLOBAL_ATTRIBUTE20
1579 , L.GLOBAL_ATTRIBUTE3
1580 , L.GLOBAL_ATTRIBUTE4
1581 , L.GLOBAL_ATTRIBUTE5
1582 , L.GLOBAL_ATTRIBUTE6
1583 , L.GLOBAL_ATTRIBUTE7
1584 , L.GLOBAL_ATTRIBUTE8
1585 , L.GLOBAL_ATTRIBUTE9
1586 , L.HEADER_ID
1587 , L.INDUSTRY_ATTRIBUTE1
1588 , L.INDUSTRY_ATTRIBUTE10
1589 , L.INDUSTRY_ATTRIBUTE11
1590 , L.INDUSTRY_ATTRIBUTE12
1591 , L.INDUSTRY_ATTRIBUTE13
1592 , L.INDUSTRY_ATTRIBUTE14
1593 , L.INDUSTRY_ATTRIBUTE15
1594 , L.INDUSTRY_ATTRIBUTE16
1595 , L.INDUSTRY_ATTRIBUTE17
1596 , L.INDUSTRY_ATTRIBUTE18
1597 , L.INDUSTRY_ATTRIBUTE19
1598 , L.INDUSTRY_ATTRIBUTE2
1599 , L.INDUSTRY_ATTRIBUTE20
1600 , L.INDUSTRY_ATTRIBUTE21
1601 , L.INDUSTRY_ATTRIBUTE22
1602 , L.INDUSTRY_ATTRIBUTE23
1603 , L.INDUSTRY_ATTRIBUTE24
1604 , L.INDUSTRY_ATTRIBUTE25
1605 , L.INDUSTRY_ATTRIBUTE26
1606 , L.INDUSTRY_ATTRIBUTE27
1607 , L.INDUSTRY_ATTRIBUTE28
1608 , L.INDUSTRY_ATTRIBUTE29
1609 , L.INDUSTRY_ATTRIBUTE3
1610 , L.INDUSTRY_ATTRIBUTE30
1611 , L.INDUSTRY_ATTRIBUTE4
1612 , L.INDUSTRY_ATTRIBUTE5
1613 , L.INDUSTRY_ATTRIBUTE6
1614 , L.INDUSTRY_ATTRIBUTE7
1615 , L.INDUSTRY_ATTRIBUTE8
1616 , L.INDUSTRY_ATTRIBUTE9
1617 , L.INDUSTRY_CONTEXT
1618 , L.TP_CONTEXT
1619 , L.TP_ATTRIBUTE1
1620 , L.TP_ATTRIBUTE2
1624 , L.TP_ATTRIBUTE6
1621 , L.TP_ATTRIBUTE3
1622 , L.TP_ATTRIBUTE4
1623 , L.TP_ATTRIBUTE5
1625 , L.TP_ATTRIBUTE7
1626 , L.TP_ATTRIBUTE8
1627 , L.TP_ATTRIBUTE9
1628 , L.TP_ATTRIBUTE10
1629 , L.TP_ATTRIBUTE11
1630 , L.TP_ATTRIBUTE12
1631 , L.TP_ATTRIBUTE13
1632 , L.TP_ATTRIBUTE14
1633 , L.TP_ATTRIBUTE15
1634 , L.INTMED_SHIP_TO_CONTACT_ID
1635 , L.INTMED_SHIP_TO_ORG_ID
1636 , item.CONCATENATED_SEGMENTS -- INVENTORY_ITEM
1637 , L.INVENTORY_ITEM_ID
1638 -- , L.INVOICE_COMPLETE_FLAG 11/03
1639 -- , L.INVOICE_SET_ID
1640 -- , L.INVOICE_SET_NAME
1641 -- , L.INVOICE_NUMBER
1642 , NULL -- INVOICE_TO_CONTACT
1643 , L.INVOICE_TO_CONTACT_ID
1644 , Get_Location(l.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_ORG
1645 , L.INVOICE_TO_ORG_ID
1646 -- , ???().INVOICE_TOLERANCE_ABOVE
1647 -- , ???().INVOICE_TOLERANCE_BELOW
1648 , invrule.NAME -- INVOICING_RULE
1649 , L.INVOICING_RULE_ID
1650 , L.ORDERED_ITEM
1651 , L.ITEM_REVISION
1652 , L.item_identifier_type -- ITEM_TYPE_CODE
1653 , L.LAST_ACK_CODE
1654 , L.LAST_ACK_DATE
1655 , L.LAST_UPDATE_DATE
1656 , L.LAST_UPDATE_LOGIN
1657 , L.LAST_UPDATED_BY
1658 , L.LATEST_ACCEPTABLE_DATE
1659 , L.LINE_CATEGORY_CODE
1660 , L.LINE_ID
1661 , L.LINE_NUMBER
1662 -- , L.LINE_PO_CONTEXT
1663 , lt.NAME -- LINE_TYPE
1664 , L.LINE_TYPE_ID
1665 , L.LINK_TO_LINE_ID
1666 -- , L.LINK_TO_LINE_REF
1667 -- , ???().LOT
1668 , L.MODEL_GROUP_NUMBER
1669 , L.OPEN_FLAG
1670 -- , L.OPERATION
1671 , L.OPTION_FLAG
1672 , L.OPTION_NUMBER
1673 , L.ORDER_QUANTITY_UOM
1674 , L.ORDER_SOURCE_ID
1675 , L.ORDERED_QUANTITY
1676 , L.ORG_ID
1677 , L.ORIG_SYS_DOCUMENT_REF
1678 , L.ORIG_SYS_LINE_REF
1679 , L.ORIG_SYS_SHIPMENT_REF
1680 , L.OVER_SHIP_REASON_CODE
1681 , L.OVER_SHIP_RESOLVED_FLAG
1682 , term.NAME -- PAYMENT_TERM
1683 , L.PAYMENT_TERM_ID
1684 , pl.NAME -- PRICE_LIST
1685 , L.PRICE_LIST_ID
1686 , L.PRICING_ATTRIBUTE1
1687 , L.PRICING_ATTRIBUTE10
1688 , L.PRICING_ATTRIBUTE2
1689 , L.PRICING_ATTRIBUTE3
1690 , L.PRICING_ATTRIBUTE4
1691 , L.PRICING_ATTRIBUTE5
1692 , L.PRICING_ATTRIBUTE6
1693 , L.PRICING_ATTRIBUTE7
1694 , L.PRICING_ATTRIBUTE8
1695 , L.PRICING_ATTRIBUTE9
1696 , L.PRICING_CONTEXT
1697 , L.PRICING_DATE
1698 , L.PRICING_QUANTITY
1699 , L.PRICING_QUANTITY_UOM
1700 -- , ???().PROGRAM
1701 -- , ???().PROGRAM_APPLICATION
1702 , L.PROGRAM_APPLICATION_ID
1703 , L.PROGRAM_ID
1704 , L.PROGRAM_UPDATE_DATE
1705 , PJM_PROJECT.ALL_PROJ_IDTONUM(l.project_id) -- PROJECT
1706 , L.PROJECT_ID
1707 , L.PROMISE_DATE
1708 -- , L.REFERENCE_HEADER
1709 , L.REFERENCE_HEADER_ID
1710 -- , L.REFERENCE_LINE
1711 , L.REFERENCE_LINE_ID
1712 , L.REFERENCE_TYPE
1713 , L.REQUEST_DATE
1714 , L.REQUEST_ID
1715 -- BULK Import does not reserve lines therefore not setting
1716 -- reserved quantity on lines acknowledgments
1717 -- , L.RESERVED_QUANTITY
1718 , L.RETURN_ATTRIBUTE1
1719 , L.RETURN_ATTRIBUTE10
1720 , L.RETURN_ATTRIBUTE11
1721 , L.RETURN_ATTRIBUTE12
1722 , L.RETURN_ATTRIBUTE13
1723 , L.RETURN_ATTRIBUTE14
1724 , L.RETURN_ATTRIBUTE15
1725 , L.RETURN_ATTRIBUTE2
1726 , L.RETURN_ATTRIBUTE3
1727 , L.RETURN_ATTRIBUTE4
1728 , L.RETURN_ATTRIBUTE5
1729 , L.RETURN_ATTRIBUTE6
1730 , L.RETURN_ATTRIBUTE7
1731 , L.RETURN_ATTRIBUTE8
1732 , L.RETURN_ATTRIBUTE9
1733 , L.RETURN_CONTEXT
1734 , L.RETURN_REASON_CODE
1735 , L.RLA_SCHEDULE_TYPE_CODE
1736 , L.SALESREP_ID
1737 , salesrep.NAME -- SALESREP
1738 , L.SCHEDULE_ARRIVAL_DATE
1739 , L.SCHEDULE_SHIP_DATE
1740 -- , L.SCHEDULE_ITEM_DETAIL
1741 , L.SCHEDULE_STATUS_CODE
1742 , Get_Location(l.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_ORG
1743 , L.SHIP_FROM_ORG_ID
1744 , L.SHIP_MODEL_COMPLETE_FLAG
1745 , L.SHIP_SET_ID
1746 -- , L.SHIP_SET_NAME
1747 , Get_Address1(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ADDRESS1
1748 , Get_Address2(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ADDRESS2
1749 , Get_Address3(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ADDRESS3
1750 , Get_Address4(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ADDRESS4
1751 , Get_City(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_CITY
1752 , NULL -- SHIP_TO_CONTACT
1753 -- , L.SHIP_TO_CONTACT_AREA_CODE1
1754 -- , L.SHIP_TO_CONTACT_AREA_CODE2
1755 -- , L.SHIP_TO_CONTACT_AREA_CODE3
1759 , ship_party.PERSON_LAST_NAME -- SHIP_TO_CONTACT_LAST_NAME
1756 , ship_party.PERSON_FIRST_NAME -- SHIP_TO_CONTACT_FIRST_NAME
1757 , L.SHIP_TO_CONTACT_ID
1758 -- , L.SHIP_TO_CONTACT_JOB_TITLE
1760 , Get_Country(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_COUNTRY
1761 , Get_County(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_COUNTY
1762 , Get_Location(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ORG
1763 , L.SHIP_TO_ORG_ID
1764 , Get_Zip(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_zip
1765 , Get_State(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_STATE
1766 , L.SHIP_TOLERANCE_ABOVE
1767 , L.SHIP_TOLERANCE_BELOW
1768 , L.SHIPMENT_NUMBER
1769 , sp.MEANING -- SHIPMENT_PRIORITY
1770 , L.SHIPMENT_PRIORITY_CODE
1771 , L.SHIPPED_QUANTITY
1772 -- , L.SHIPPING_METHOD
1773 , L.SHIPPING_METHOD_CODE
1774 , L.SHIPPING_QUANTITY
1775 , L.SHIPPING_QUANTITY_UOM
1776 -- , ???().SOLD_FROM_ORG
1777 -- , ???().SOLD_FROM_ORG_ID
1778 , NULL -- SOLD_TO_ORG
1779 , L.SOLD_TO_ORG_ID
1780 , L.SORT_ORDER
1781 , L.SOURCE_DOCUMENT_ID
1782 , L.SOURCE_DOCUMENT_LINE_ID
1783 , L.SOURCE_DOCUMENT_TYPE_ID
1784 , L.SOURCE_TYPE_CODE
1785 , L.SPLIT_FROM_LINE_ID
1786 -- , ???.SUBINVENTORY
1787 -- , ???.SUBMISSION_DATETIME
1788 , PJM_PROJECT.ALL_TASK_IDTONUM(l.task_id) -- TASK
1789 , L.TASK_ID
1790 -- , L.TAX
1791 , L.TAX_CODE
1792 , L.TAX_DATE
1793 , L.TAX_EXEMPT_FLAG
1794 , L.TAX_EXEMPT_NUMBER
1795 , tax_exempt_reason.MEANING -- TAX_EXEMPT_REASON
1796 , L.TAX_EXEMPT_REASON_CODE
1797 , tax_point.MEANING -- TAX_POINT
1798 , L.TAX_POINT_CODE
1799 , L.TAX_RATE
1800 , L.TAX_VALUE
1801 , L.UNIT_LIST_PRICE
1802 , L.UNIT_SELLING_PRICE
1803 , L.VEH_CUS_ITEM_CUM_KEY_ID
1804 , L.VISIBLE_DEMAND_FLAG
1805 -- BULK Import is only for creates of complete orders, splits
1806 -- is NOT supported!
1807 , NULL -- L.split_from_line_ref
1808 , Get_EDI_Location(L.ship_to_org_id,'SHIP_TO') -- SHIP_TO_EDI_LOCATION_CODE
1809 , L.Service_Txn_Reason_Code
1810 , L.Service_Txn_Comments
1811 , L.Service_Duration
1812 , L.Service_Start_Date
1813 , L.Service_End_Date
1814 , L.Service_Coterminate_Flag
1815 , L.Service_Number
1816 , L.Service_Period
1817 , L.Service_Reference_Type_Code
1818 , L.Service_Reference_Line_Id
1819 , L.Service_Reference_System_Id
1820 , L.Credit_Invoice_Line_Id
1821 , Get_Province(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_Province
1822 , Get_Province(l.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_Province
1823 , Get_EDI_Location(l.invoice_to_org_id,'BILL_TO') -- Bill_to_Edi_Location_Code
1824 , Get_City(l.invoice_to_org_id,'BILL_TO') -- INVOICE_TO_City
1825 , Get_EDI_Location(l.ship_from_org_id,'SHIP_FROM') -- ship_from_edi_location_code
1826 , Get_Address1(l.ship_from_org_id,'SHIP_FROM')
1827 , Get_Address2(l.ship_from_org_id,'SHIP_FROM')
1828 , Get_Address3(l.ship_from_org_id,'SHIP_FROM')
1829 , Get_City(l.ship_from_org_id,'SHIP_FROM')
1830 , Get_Zip(l.ship_from_org_id,'SHIP_FROM') -- POSTAL_CODE
1831 , Get_Country(l.ship_from_org_id,'SHIP_FROM')
1832 , NULL -- SHIP_FROM_REGION1
1833 , NULL -- SHIP_FROM_REGION2
1834 , NULL -- SHIP_FROM_REGION3
1835 , Get_Address_ID(l.ship_from_org_id,'SHIP_FROM') -- SHIP_FROM_ADDRESS_ID
1836 , Get_Address_ID(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_ADDRESS_ID
1837 , Get_Location(l.ship_to_org_id,'SHIP_TO') -- SHIP_TO_LOCATION
1838 -- BULK Import does not support service lines therefore pass
1839 -- NULL in these service value fields!
1840 , NULL -- L.Service_reference_line
1841 , NULL -- L.Service_reference_order
1842 , NULL -- L.Service_reference_system
1843 -- , l_line_val_rec.order_source
1844 , L.customer_line_number
1845 , L.user_item_description
1846 FROM OE_ORDER_LINES L
1847 , OE_ORDER_SOURCES os
1848 , OE_AGREEMENTS_V agr
1849 , OE_AR_LOOKUPS_V fob
1850 , OE_LOOKUPS ft
1851 , OE_LOOKUPS sp
1852 , HZ_PARTIES ship_party
1853 , HZ_RELATIONSHIPS ship_rel
1854 , HZ_CUST_ACCOUNT_ROLES ship_role
1855 , HZ_CUST_ACCOUNTS ship_acct
1856 , RA_SALESREPS salesrep
1857 , OE_TRANSACTION_TYPES_TL lt
1858 , QP_LIST_HEADERS_TL pl
1859 , RA_TERMS_TL term
1860 , RA_RULES accrule
1861 , RA_RULES invrule
1862 , OE_AR_LOOKUPS_V tax_exempt_reason
1863 , OE_LOOKUPS tax_point
1864 , MTL_SYSTEM_ITEMS_VL item
1865 WHERE L.header_id = p_header_id
1869 AND fob.lookup_code(+) = l.fob_point_code
1866 AND os.order_source_id(+) = l.order_source_id
1867 AND agr.agreement_id(+) = l.agreement_id
1868 AND fob.lookup_type(+) = 'FOB'
1870 AND ft.lookup_type(+) = 'FREIGHT_TERMS'
1871 AND ft.lookup_code(+) = l.freight_terms_code
1872 AND sp.lookup_type(+) = 'SHIPMENT_PRIORITY'
1873 AND sp.lookup_code(+) = l.shipment_priority_code
1874 AND salesrep.salesrep_id(+) = l.salesrep_id
1875 AND lt.transaction_type_id = l.line_type_id
1876 AND lt.language = userenv('LANG')
1877 AND pl.list_header_id(+) = l.price_list_id
1878 AND pl.language(+) = userenv('LANG')
1879 AND term.term_id(+) = l.payment_term_id
1880 AND term.language(+) = userenv('LANG')
1881 AND ship_role.cust_account_role_id(+) = L.ship_to_contact_id
1882 AND ship_role.role_type(+) = 'CONTACT'
1883 AND ship_role.cust_account_id(+) = L.ship_to_org_id
1884 AND ship_rel.party_id(+) = ship_role.party_id
1885 AND ship_party.party_id(+) = ship_rel.subject_id
1886 AND ship_acct.cust_account_id(+) = ship_role.cust_account_id
1887 AND nvl(ship_acct.party_id,1) = nvl(ship_rel.object_id,1)
1888 AND accrule.rule_id(+) = L.accounting_rule_id
1889 AND invrule.rule_id(+) = L.invoicing_rule_id
1890 AND tax_exempt_reason.lookup_type(+) = 'TAX_EXEMPT_REASON'
1891 AND tax_exempt_reason.lookup_code(+) = L.tax_exempt_reason_code
1892 AND tax_point.lookup_type(+) = 'TAX_POINT'
1893 AND tax_point.lookup_code(+) = L.tax_point_code
1894 AND item.inventory_item_id = L.inventory_item_id
1895 AND item.organization_id = OE_BULK_ORDER_PVT.G_ITEM_ORG
1896 ;
1897
1898 END Insert_Line_Acks;
1899
1900 PROCEDURE Process_Acknowledgments
1901 (p_batch_id IN NUMBER
1902 ,x_return_status OUT NOCOPY VARCHAR2)
1903 IS
1904 l_header_count NUMBER;
1905 l_header_id NUMBER;
1906 BEGIN
1907
1908 IF OE_GLOBALS.G_EC_INSTALLED <> 'Y' THEN
1909 oe_debug_pub.add('EC not installed - No ACK required',1);
1910 RETURN;
1911 END IF;
1912
1913 -- Can we somehow mark those orders during entity validation
1914 -- where customers are EDI enabled?
1915 -- maybe, use first_ack_code to identify such orders
1916
1917 -- For such orders, do not process acks IF:
1918 -- 1. Any one line in the order exists that is NOT scheduled
1919 -- (schedule_ship_date IS NULL)
1920
1921 l_header_count := OE_BULK_ORDER_PVT.G_HEADER_REC.HEADER_ID.COUNT;
1922
1923 FOR I IN 1..l_header_count LOOP
1924
1925 IF OE_BULK_ORDER_PVT.G_HEADER_REC.first_ack_code(i) = 'X'
1926 AND nvl(OE_BULK_ORDER_PVT.G_HEADER_REC.lock_control(i), 0) NOT IN (-99, -98, -97)
1927 AND OE_BULK_ORDER_PVT.G_HEADER_REC.booked_flag(i) = 'Y'
1928 THEN
1929
1930 l_header_id := OE_BULK_ORDER_PVT.G_HEADER_REC.HEADER_ID(i);
1931
1932 Insert_Header_Acks(l_header_id);
1933
1934 Insert_Line_Acks(l_header_id);
1935
1936 -- Check to see if there are any Rejected lines for the
1937 -- processed headers
1938 Insert_Rejected_Lines_Ack(p_batch_id,
1939 OE_BULK_ORDER_PVT.G_HEADER_REC.order_source_id(i),
1940 OE_BULK_ORDER_PVT.G_HEADER_REC.orig_sys_document_ref(i));
1941
1942 END IF;
1943
1944 END LOOP;
1945
1946 EXCEPTION
1947 WHEN OTHERS THEN
1948 OE_BULK_MSG_PUB.Add_Exc_Msg
1949 ( G_PKG_NAME
1950 , 'Process_Acknowledgments'
1951 );
1952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1953 END Process_Acknowledgments;
1954
1955 PROCEDURE Insert_Rejected_Lines_Ack(p_batch_id IN NUMBER,
1956 p_order_source_id IN NUMBER,
1957 p_orig_sys_document_ref IN VARCHAR2)
1958 IS
1959 BEGIN
1960
1961 INSERT INTO OE_LINE_ACKS
1962 (
1963 ACCOUNTING_RULE
1964 ,ACCOUNTING_RULE_ID
1965 ,ACCOUNTING_RULE_DURATION
1966 ,ACKNOWLEDGMENT_FLAG
1967 ,ACTUAL_ARRIVAL_DATE
1968 ,ACTUAL_SHIPMENT_DATE
1969 ,AGREEMENT
1970 ,AGREEMENT_ID
1971 ,ARRIVAL_SET_ID
1972 ,ATO_LINE_ID
1973 ,ATTRIBUTE1
1974 ,ATTRIBUTE10
1975 ,ATTRIBUTE11
1976 ,ATTRIBUTE12
1977 ,ATTRIBUTE13
1978 ,ATTRIBUTE14
1979 ,ATTRIBUTE15
1980 ,ATTRIBUTE16 --For bug 2184255
1981 ,ATTRIBUTE17
1982 ,ATTRIBUTE18
1983 ,ATTRIBUTE19
1984 ,ATTRIBUTE2
1985 ,ATTRIBUTE20
1986 ,ATTRIBUTE3
1987 ,ATTRIBUTE4
1988 ,ATTRIBUTE5
1989 ,ATTRIBUTE6
1990 ,ATTRIBUTE7
1991 ,ATTRIBUTE8
1992 ,ATTRIBUTE9
1993 ,AUTHORIZED_TO_SHIP_FLAG
1994 ,BUYER_SELLER_FLAG
1995 ,BOOKED_FLAG
1996 ,CANCELLED_FLAG
1997 ,CANCELLED_QUANTITY
1998 ,CHANGE_DATE
1999 ,CHANGE_SEQUENCE
2000 ,COMPONENT_CODE
2001 ,COMPONENT_NUMBER
2005 ,TOP_MODEL_LINE_ID
2002 ,COMPONENT_SEQUENCE_ID
2003 ,CONFIG_DISPLAY_SEQUENCE
2004 ,CONFIGURATION_ID
2006 ,CONTEXT
2007 ,CREATED_BY
2008 ,CREATION_DATE
2009 ,CUST_MODEL_SERIAL_NUMBER
2010 ,CUST_PO_NUMBER
2011 ,CUST_PRODUCTION_SEQ_NUM
2012 ,CUSTOMER_DOCK_CODE
2013 ,CUSTOMER_ITEM
2014 , CUSTOMER_JOB
2015 ,CUSTOMER_PRODUCTION_LINE
2016 ,CUSTOMER_TRX_LINE_ID
2017 ,DELIVER_TO_CONTACT_ID
2018 ,DELIVER_TO_ORG
2019 ,DELIVER_TO_ORG_ID
2020 ,DELIVERY_LEAD_TIME
2021 ,DEMAND_BUCKET_TYPE
2022 ,DEMAND_BUCKET_TYPE_CODE
2023 ,DEMAND_CLASS_CODE
2024 ,DEP_PLAN_REQUIRED_FLAG
2025 ,EARLIEST_ACCEPTABLE_DATE
2026 ,EXPLOSION_DATE
2027 ,FIRST_ACK_CODE
2028 ,FIRST_ACK_DATE
2029 ,FOB_POINT
2030 ,FOB_POINT_CODE
2031 ,FREIGHT_CARRIER_CODE
2032 ,FREIGHT_TERMS
2033 ,FREIGHT_TERMS_CODE
2034 ,FULFILLED_QUANTITY
2035 ,GLOBAL_ATTRIBUTE_CATEGORY
2036 ,GLOBAL_ATTRIBUTE1
2037 ,GLOBAL_ATTRIBUTE10
2038 ,GLOBAL_ATTRIBUTE11
2039 ,GLOBAL_ATTRIBUTE12
2040 ,GLOBAL_ATTRIBUTE13
2041 ,GLOBAL_ATTRIBUTE14
2042 ,GLOBAL_ATTRIBUTE15
2043 ,GLOBAL_ATTRIBUTE16
2044 ,GLOBAL_ATTRIBUTE17
2045 ,GLOBAL_ATTRIBUTE18
2046 ,GLOBAL_ATTRIBUTE19
2047 ,GLOBAL_ATTRIBUTE2
2048 ,GLOBAL_ATTRIBUTE20
2049 ,GLOBAL_ATTRIBUTE3
2050 ,GLOBAL_ATTRIBUTE4
2051 ,GLOBAL_ATTRIBUTE5
2052 ,GLOBAL_ATTRIBUTE6
2053 ,GLOBAL_ATTRIBUTE7
2054 ,GLOBAL_ATTRIBUTE8
2055 ,GLOBAL_ATTRIBUTE9
2056 ,HEADER_ID
2057 ,INDUSTRY_ATTRIBUTE1
2058 ,INDUSTRY_ATTRIBUTE10
2059 ,INDUSTRY_ATTRIBUTE11
2060 ,INDUSTRY_ATTRIBUTE12
2061 ,INDUSTRY_ATTRIBUTE13
2062 ,INDUSTRY_ATTRIBUTE14
2063 ,INDUSTRY_ATTRIBUTE15
2064 ,INDUSTRY_ATTRIBUTE16
2065 ,INDUSTRY_ATTRIBUTE17
2066 ,INDUSTRY_ATTRIBUTE18
2067 ,INDUSTRY_ATTRIBUTE19
2068 ,INDUSTRY_ATTRIBUTE2
2069 ,INDUSTRY_ATTRIBUTE20
2070 ,INDUSTRY_ATTRIBUTE21
2071 ,INDUSTRY_ATTRIBUTE22
2072 ,INDUSTRY_ATTRIBUTE23
2073 ,INDUSTRY_ATTRIBUTE24
2074 ,INDUSTRY_ATTRIBUTE25
2075 ,INDUSTRY_ATTRIBUTE26
2076 ,INDUSTRY_ATTRIBUTE27
2077 ,INDUSTRY_ATTRIBUTE28
2078 ,INDUSTRY_ATTRIBUTE29
2079 ,INDUSTRY_ATTRIBUTE3
2080 ,INDUSTRY_ATTRIBUTE30
2081 ,INDUSTRY_ATTRIBUTE4
2082 ,INDUSTRY_ATTRIBUTE5
2083 ,INDUSTRY_ATTRIBUTE6
2084 ,INDUSTRY_ATTRIBUTE7
2085 ,INDUSTRY_ATTRIBUTE8
2086 ,INDUSTRY_ATTRIBUTE9
2087 ,INDUSTRY_CONTEXT
2088 ,TP_CONTEXT
2089 ,TP_ATTRIBUTE1
2090 ,TP_ATTRIBUTE2
2091 ,TP_ATTRIBUTE3
2092 ,TP_ATTRIBUTE4
2093 ,TP_ATTRIBUTE5
2094 ,TP_ATTRIBUTE6
2095 ,TP_ATTRIBUTE7
2096 ,TP_ATTRIBUTE8
2097 ,TP_ATTRIBUTE9
2098 ,TP_ATTRIBUTE10
2099 ,TP_ATTRIBUTE11
2100 ,TP_ATTRIBUTE12
2101 ,TP_ATTRIBUTE13
2102 ,TP_ATTRIBUTE14
2103 ,TP_ATTRIBUTE15
2104 ,INTMED_SHIP_TO_CONTACT_ID
2105 ,INTMED_SHIP_TO_ORG_ID
2106 ,INVENTORY_ITEM
2107 ,INVENTORY_ITEM_ID
2108 ,INVOICE_TO_CONTACT
2109 ,INVOICE_TO_CONTACT_ID
2110 ,INVOICE_TO_ORG
2111 ,INVOICE_TO_ORG_ID
2112 ,INVOICING_RULE
2113 ,INVOICING_RULE_ID
2114 ,ITEM_INPUT
2115 ,ITEM_REVISION
2116 ,ITEM_TYPE_CODE
2117 ,LAST_ACK_CODE
2118 ,LAST_ACK_DATE
2119 ,LAST_UPDATE_DATE
2120 ,LAST_UPDATE_LOGIN
2121 ,LAST_UPDATED_BY
2122 ,LATEST_ACCEPTABLE_DATE
2123 ,LINE_CATEGORY_CODE
2124 ,LINE_ID
2125 ,LINE_NUMBER
2126 ,LINE_TYPE
2127 ,LINE_TYPE_ID
2128 ,LINK_TO_LINE_ID
2129 ,MODEL_GROUP_NUMBER
2130 ,OPEN_FLAG
2131 ,OPERATION_CODE
2132 ,OPTION_FLAG
2133 ,OPTION_NUMBER
2134 ,ORDER_QUANTITY_UOM
2135 ,ORDER_SOURCE_ID
2136 ,ORDERED_QUANTITY
2137 ,ORG_ID
2138 ,ORIG_SYS_DOCUMENT_REF
2139 ,ORIG_SYS_LINE_REF
2140 ,ORIG_SYS_SHIPMENT_REF
2141 ,OVER_SHIP_REASON_CODE
2142 ,OVER_SHIP_RESOLVED_FLAG
2143 ,PAYMENT_TERM
2144 ,PAYMENT_TERM_ID
2145 ,PRICE_LIST
2146 ,PRICE_LIST_ID
2147 ,PRICING_ATTRIBUTE1
2148 ,PRICING_ATTRIBUTE10
2149 ,PRICING_ATTRIBUTE2
2150 ,PRICING_ATTRIBUTE3
2151 ,PRICING_ATTRIBUTE4
2152 ,PRICING_ATTRIBUTE5
2153 ,PRICING_ATTRIBUTE6
2154 ,PRICING_ATTRIBUTE7
2155 ,PRICING_ATTRIBUTE8
2156 ,PRICING_ATTRIBUTE9
2160 ,PRICING_QUANTITY_UOM
2157 ,PRICING_CONTEXT
2158 ,PRICING_DATE
2159 ,PRICING_QUANTITY
2161 ,PROGRAM_APPLICATION_ID
2162 ,PROGRAM_ID
2163 ,PROGRAM_UPDATE_DATE
2164 ,PROJECT
2165 ,PROJECT_ID
2166 ,PROMISE_DATE
2167 ,REFERENCE_HEADER_ID
2168 ,REFERENCE_LINE_ID
2169 ,REFERENCE_TYPE
2170 ,REQUEST_DATE
2171 ,REQUEST_ID
2172 ,RESERVED_QUANTITY
2173 ,RETURN_ATTRIBUTE1
2174 ,RETURN_ATTRIBUTE10
2175 ,RETURN_ATTRIBUTE11
2176 ,RETURN_ATTRIBUTE12
2177 ,RETURN_ATTRIBUTE13
2178 ,RETURN_ATTRIBUTE14
2179 ,RETURN_ATTRIBUTE15
2180 ,RETURN_ATTRIBUTE2
2181 ,RETURN_ATTRIBUTE3
2182 ,RETURN_ATTRIBUTE4
2183 ,RETURN_ATTRIBUTE5
2184 ,RETURN_ATTRIBUTE6
2185 ,RETURN_ATTRIBUTE7
2186 ,RETURN_ATTRIBUTE8
2187 ,RETURN_ATTRIBUTE9
2188 ,RETURN_CONTEXT
2189 ,RETURN_REASON_CODE
2190 ,RLA_SCHEDULE_TYPE_CODE
2191 ,SALESREP_ID
2192 ,SALESREP
2193 ,SCHEDULE_ARRIVAL_DATE
2194 ,SCHEDULE_SHIP_DATE
2195 ,SCHEDULE_STATUS_CODE
2196 ,SHIP_FROM_ORG
2197 ,SHIP_FROM_ORG_ID
2198 ,SHIP_MODEL_COMPLETE_FLAG
2199 ,SHIP_SET_ID
2200 ,SHIP_TO_ADDRESS1
2201 ,SHIP_TO_ADDRESS2
2202 ,SHIP_TO_ADDRESS3
2203 ,SHIP_TO_ADDRESS4
2204 ,SHIP_TO_CITY
2205 ,SHIP_TO_CONTACT
2206 ,SHIP_TO_CONTACT_FIRST_NAME
2207 ,SHIP_TO_CONTACT_ID
2208 ,SHIP_TO_CONTACT_LAST_NAME
2209 ,SHIP_TO_COUNTRY
2210 ,SHIP_TO_COUNTY
2211 ,SHIP_TO_ORG
2212 ,SHIP_TO_ORG_ID
2213 ,SHIP_TO_POSTAL_CODE
2214 ,SHIP_TO_STATE
2215 ,SHIP_TOLERANCE_ABOVE
2216 ,SHIP_TOLERANCE_BELOW
2217 ,SHIPMENT_NUMBER
2218 ,SHIPMENT_PRIORITY
2219 ,SHIPMENT_PRIORITY_CODE
2220 ,SHIPPED_QUANTITY
2221 ,SHIPPING_METHOD_CODE
2222 ,SHIPPING_QUANTITY
2223 ,SHIPPING_QUANTITY_UOM
2224 ,SOLD_TO_ORG
2225 ,SOLD_TO_ORG_ID
2226 ,SORT_ORDER
2227 ,SOURCE_DOCUMENT_ID
2228 ,SOURCE_DOCUMENT_LINE_ID
2229 ,SOURCE_DOCUMENT_TYPE_ID
2230 ,SOURCE_TYPE_CODE
2231 ,SPLIT_FROM_LINE_ID
2232 ,TASK
2233 ,TASK_ID
2234 ,TAX_CODE
2235 ,TAX_DATE
2236 ,TAX_EXEMPT_FLAG
2237 ,TAX_EXEMPT_NUMBER
2238 ,TAX_EXEMPT_REASON
2239 ,TAX_EXEMPT_REASON_CODE
2240 ,TAX_POINT
2241 ,TAX_POINT_CODE
2242 ,TAX_RATE
2243 ,TAX_VALUE
2244 ,UNIT_LIST_PRICE
2245 ,UNIT_SELLING_PRICE
2246 ,VEH_CUS_ITEM_CUM_KEY_ID
2247 ,VISIBLE_DEMAND_FLAG
2248 ,split_from_line_ref
2249 ,SHIP_TO_EDI_LOCATION_CODE
2250 ,Service_Txn_Reason_Code
2251 ,Service_Txn_Comments
2252 ,Service_Duration
2253 ,Service_Start_Date
2254 ,Service_End_Date
2255 ,Service_Coterminate_Flag
2256 ,Service_Number
2257 ,Service_Period
2258 ,Service_Reference_Type_Code
2259 ,Service_Reference_Line_Id
2260 ,Service_Reference_System_Id
2261 ,Credit_Invoice_Line_Id
2262 ,Ship_to_Province
2263 ,Invoice_Province
2264 ,Bill_to_Edi_Location_Code
2265 ,Invoice_City
2266 ,ship_from_edi_location_code
2267 ,SHIP_FROM_ADDRESS_1
2268 ,SHIP_FROM_ADDRESS_2
2269 ,SHIP_FROM_ADDRESS_3
2270 ,SHIP_FROM_CITY
2271 ,SHIP_FROM_POSTAL_CODE
2272 ,SHIP_FROM_COUNTRY
2273 ,SHIP_FROM_REGION1
2274 ,SHIP_FROM_REGION2
2275 ,SHIP_FROM_REGION3
2276 ,SHIP_FROM_ADDRESS_ID
2277 ,SHIP_TO_ADDRESS_ID
2278 ,SHIP_TO_ADDRESS_CODE
2279 ,service_reference_line
2280 ,service_reference_order
2281 ,service_reference_system
2282 ,customer_line_number
2283 ,user_item_description
2284 )
2285 SELECT l.accounting_rule,
2286 l.accounting_rule_id,
2287 l.accounting_rule_duration,
2288 '', -- ACKNOWLEDGMENT_FLAG
2289 l.actual_arrival_date,
2290 NULL, -- ACTUAL_SHIPMENT_DATE
2291 l.agreement,
2292 l.agreement_id,
2293 NULL, -- ARRIVAL_SET_ID
2294 l.ato_line_id,
2295 l.attribute1,
2296 l.attribute10,
2297 l.attribute11,
2298 l.attribute12,
2299 l.attribute13,
2300 l.attribute14,
2301 l.attribute15,
2302 l.attribute16, --For bug 2184255
2303 l.attribute17,
2304 l.attribute18,
2305 l.attribute19,
2306 l.attribute2,
2307 l.attribute20,
2308 l.attribute3,
2309 l.attribute4,
2310 l.attribute5,
2311 l.attribute6,
2312 l.attribute7,
2313 l.attribute8,
2314 l.attribute9,
2315 NULL, -- AUTHORIZED_TO_SHIP_FLAG
2316 'B', -- BUYER_SELLER_FLAG
2320 '', -- CHANGE_DATE
2317 NULL, -- BOOKED_FLAG
2318 l.cancelled_flag,
2319 l.cancelled_quantity,
2321 NULL, -- CHANGE_SEQUENCE
2322 l.component_code,
2323 NULL, -- COMPONENT_NUMBER
2324 l.component_sequence_id,
2325 NULL, -- CONFIG_DISPLAY_SEQUENCE
2326 NULL, -- CONFIGURATION_ID
2327 NULL, -- TOP_MODEL_LINE_ID
2328 l.CONTEXT,
2329 NULL, -- CREATED_BY
2330 NULL, -- CREATION_DATE
2331 l.cust_model_serial_number,
2332 NULL, -- CUST_PO_NUMBER
2333 NULL, -- CUST_PRODUCTION_SEQ_NUM
2334 l.customer_dock_code,
2335 NULL, -- CUSTOMER_ITEM
2336 l.customer_job,
2337 l.customer_production_line,
2338 NULL, -- CUSTOMER_TRX_LINE_ID
2339 l.deliver_to_contact_id,
2340 l.deliver_to_org,
2341 l.deliver_to_org_id,
2342 l.delivery_lead_time,
2343 l.demand_bucket_type,
2344 l.demand_bucket_type_code,
2345 l.demand_class_code,
2346 NULL, -- DEP_PLAN_REQUIRED_FLAG
2347 l.earliest_acceptable_date,
2348 l.explosion_date,
2349 'IR', -- FIRST_ACK_CODE
2350 NULL, -- FIRST_ACK_DATE
2351 l.fob_point,
2352 l.fob_point_code,
2353 l.freight_carrier_code,
2354 l.freight_terms,
2355 l.freight_terms_code,
2356 l.fulfilled_quantity,
2357 l.global_attribute_category,
2358 l.global_attribute1,
2359 l.global_attribute10,
2360 l.global_attribute11,
2361 l.global_attribute12,
2362 l.global_attribute13,
2363 l.global_attribute14,
2364 l.global_attribute15,
2365 l.global_attribute16,
2366 l.global_attribute17,
2367 l.global_attribute18,
2368 l.global_attribute19,
2369 l.global_attribute20,
2370 l.global_attribute2,
2371 l.global_attribute3,
2372 l.global_attribute4,
2373 l.global_attribute5,
2374 l.global_attribute6,
2375 l.global_attribute7,
2376 l.global_attribute8,
2377 l.global_attribute9,
2378 NULL, -- header_id
2379 l.industry_attribute1,
2380 l.industry_attribute10,
2381 l.industry_attribute11,
2382 l.industry_attribute12,
2383 l.industry_attribute13,
2384 l.industry_attribute14,
2385 l.industry_attribute15,
2386 l.industry_attribute16,
2387 l.industry_attribute17,
2388 l.industry_attribute18,
2389 l.industry_attribute19,
2390 l.industry_attribute2,
2391 l.industry_attribute20,
2392 l.industry_attribute21,
2393 l.industry_attribute22,
2394 l.industry_attribute23,
2395 l.industry_attribute24,
2396 l.industry_attribute25,
2397 l.industry_attribute26,
2398 l.industry_attribute27,
2399 l.industry_attribute28,
2400 l.industry_attribute29,
2401 l.industry_attribute3,
2402 l.industry_attribute30,
2403 l.industry_attribute4,
2404 l.industry_attribute5,
2405 l.industry_attribute6,
2406 l.industry_attribute7,
2407 l.industry_attribute8,
2408 l.industry_attribute9,
2409 l.industry_context,
2410 l.TP_CONTEXT,
2411 l.TP_ATTRIBUTE1,
2412 l.TP_ATTRIBUTE2,
2413 l.TP_ATTRIBUTE3,
2414 l.TP_ATTRIBUTE4,
2415 l.TP_ATTRIBUTE5,
2416 l.TP_ATTRIBUTE6,
2417 l.TP_ATTRIBUTE7,
2418 l.TP_ATTRIBUTE8,
2419 l.TP_ATTRIBUTE9,
2420 l.TP_ATTRIBUTE10,
2421 l.TP_ATTRIBUTE11,
2422 l.TP_ATTRIBUTE12,
2423 l.TP_ATTRIBUTE13,
2424 l.TP_ATTRIBUTE14,
2425 l.TP_ATTRIBUTE15,
2426 NULL, -- INTMED_SHIP_TO_CONTACT_ID
2427 NULL, -- INTMED_SHIP_TO_ORG_ID
2428 l.inventory_item,
2429 l.inventory_item_id,
2430 l.invoice_to_contact,
2431 l.invoice_to_contact_id,
2432 l.invoice_to_org,
2433 l.invoice_to_org_id,
2434 l.invoicing_rule,
2435 l.invoicing_rule_id,
2436 NULL, -- ITEM_INPUT
2437 l.item_revision,
2438 NULL, -- ITEM_TYPE_CODE
2439 NULL, -- LAST_ACK_CODE
2440 NULL, -- LAST_ACK_DATE
2441 sysdate, -- LAST_UPDATE_DATE
2442 1, -- LAST_UPDATE_LOGIN
2443 1, -- LAST_UPDATED_BY
2444 l.latest_acceptable_date,
2445 NULL, -- LINE_CATEGORY_CODE
2446 NULL, -- LINE_ID
2447 l.line_number,
2448 l.line_type,
2449 l.line_type_id,
2450 NULL, -- LINK_TO_LINE_ID
2451 l.model_group_number,
2452 NULL, -- OPEN_FLAG
2453 nvl(l.operation_code,OE_GLOBALS.G_OPR_CREATE),
2454 l.option_flag,
2455 l.option_number,
2456 l.order_quantity_uom ,
2457 l.order_source_id,
2458 l.ordered_quantity,
2459 l.org_id,
2460 l.orig_sys_document_ref,
2461 l.orig_sys_line_ref,
2462 l.orig_sys_shipment_ref,
2463 NULL, -- OVER_SHIP_REASON_CODE
2464 NULL, -- OVER_SHIP_RESOLVED_FLAG
2465 l.payment_term,
2466 l.payment_term_id,
2467 l.price_list,
2468 l.price_list_id,
2469 l.pricing_attribute1,
2470 l.pricing_attribute10,
2471 l.pricing_attribute2,
2472 l.pricing_attribute3,
2473 l.pricing_attribute4,
2474 l.pricing_attribute5,
2475 l.pricing_attribute6,
2476 l.pricing_attribute7,
2480 l.pricing_date,
2477 l.pricing_attribute8,
2478 l.pricing_attribute9,
2479 l.pricing_context,
2481 l.pricing_quantity,
2482 l.pricing_quantity_uom,
2483 NULL, -- PROGRAM_APPLICATION_ID
2484 NULL, -- PROGRAM_ID
2485 sysdate, -- PROGRAM_UPDATE_DATE
2486 l.project,
2487 l.project_id,
2488 l.promise_date,
2489 l.reference_header_id,
2490 l.reference_line_id,
2491 l.reference_type,
2492 NULL, -- REQUEST_DATE
2493 l.request_id,
2494 NULL, -- RESERVED_QUANTITY
2495 l.return_attribute1,
2496 l.return_attribute10,
2497 l.return_attribute11,
2498 l.return_attribute12,
2499 l.return_attribute13,
2500 l.return_attribute14,
2501 l.return_attribute15,
2502 l.return_attribute2,
2503 l.return_attribute3,
2504 l.return_attribute4,
2505 l.return_attribute5,
2506 l.return_attribute6,
2507 l.return_attribute7,
2508 l.return_attribute8,
2509 l.return_attribute9,
2510 l.return_context,
2511 l.RETURN_REASON_CODE,
2512 NULL, -- RLA_SCHEDULE_TYPE_CODE
2513 l.SALESREP_ID,
2514 l.SALESREP,
2515 l.schedule_arrival_date,
2516 l.schedule_date,
2517 l.schedule_status_code,
2518 l.ship_from_org,
2519 l.ship_from_org_id,
2520 l.ship_model_complete_flag,
2521 NULL, -- SHIP_SET_ID
2522 l.ship_to_address1,
2523 l.ship_to_address2,
2524 l.ship_to_address3,
2525 l.ship_to_address4,
2526 l.SHIP_TO_CITY,
2527 l.ship_to_contact,
2528 l.SHIP_TO_CONTACT_FIRST_NAME,
2529 l.ship_to_contact_id,
2530 l.SHIP_TO_CONTACT_LAST_NAME,
2531 l.SHIP_TO_COUNTRY,
2532 l.SHIP_TO_COUNTY,
2533 l.sold_to_org,
2534 l.ship_to_org_id ,
2535 l.SHIP_TO_POSTAL_CODE,
2536 l.SHIP_TO_STATE,
2537 l.ship_tolerance_above,
2538 l.ship_tolerance_below,
2539 l.shipment_number,
2540 l.shipment_priority,
2541 l.shipment_priority_code,
2542 l.shipped_quantity,
2543 l.shipping_method_code,
2544 l.shipping_quantity,
2545 l.shipping_quantity_uom,
2546 l.sold_to_org,
2547 l.sold_to_org_id ,
2548 l.sort_order,
2549 NULL, -- SOURCE_DOCUMENT_ID
2550 NULL, -- SOURCE_DOCUMENT_LINE_ID
2551 NULL, -- SOURCE_DOCUMENT_TYPE_ID
2552 l.source_type_code,
2553 NULL, -- SPLIT_FROM_LINE_ID
2554 l.task,
2555 l.task_id,
2556 l.tax_code,
2557 l.tax_date,
2558 l.tax_exempt_flag,
2559 l.tax_exempt_number,
2560 l.tax_exempt_reason,
2561 l.tax_exempt_reason_code,
2562 l.tax_point,
2563 l.tax_point_code,
2564 NULL, -- TAX_RATE
2565 l.tax_value,
2566 l.unit_list_price,
2567 l.unit_selling_price,
2568 l.VEH_CUS_ITEM_CUM_KEY_ID,
2569 NULL, -- VISIBLE_DEMAND_FLAG
2570 l.split_from_line_ref,
2571 NULL, -- SHIP_TO_EDI_LOCATION_CODE
2572 l.SERVICE_TXN_REASON_CODE,
2573 l.SERVICE_TXN_COMMENTS,
2574 l.Service_Duration,
2575 l.Service_Start_Date,
2576 l.Service_end_Date,
2577 l.Service_Coterminate_Flag,
2578 l.Service_Number,
2579 l.Service_Period,
2580 l.Service_Reference_Type_Code,
2581 NULL, -- SERVICE_REFERENCE_LINE_ID
2582 NULL, -- Service_Reference_System_Id
2583 l.Credit_Invoice_Line_Id,
2584 NULL, -- Ship_to_Province
2585 NULL, -- Invoice_Province
2586 NULL, -- Bill_to_Edi_Location_Code
2587 NULL, -- Invoice_City
2588 NULL, -- ship_from_edi_location_code
2589 NULL, -- SHIP_FROM_ADDRESS_1
2590 NULL, -- SHIP_FROM_ADDRESS_2
2591 NULL, -- SHIP_FROM_ADDRESS_3
2592 NULL, -- SHIP_FROM_CITY
2593 NULL, -- SHIP_FROM_POSTAL_CODE
2594 NULL, -- SHIP_FROM_COUNTRY
2595 NULL, -- SHIP_FROM_REGION1
2596 NULL, -- SHIP_FROM_REGION2
2597 NULL, -- SHIP_FROM_REGION3
2598 NULL, -- SHIP_FROM_ADDRESS_ID
2599 NULL, -- SHIP_TO_ADDRESS_ID
2600 NULL, -- SHIP_TO_ADDRESS_CODE
2601 l.service_reference_line,
2602 l.service_reference_order,
2603 l.service_reference_system,
2604 l.customer_line_number,
2605 l.user_item_description
2606 FROM OE_LINES_IFACE_ALL l, OE_HEADERS_IFACE_ALL h
2607 WHERE h.batch_id = p_batch_id
2608 AND h.order_source_id = p_order_source_id
2609 AND h.orig_sys_document_ref = p_orig_sys_document_ref
2610 AND h.order_source_id = l.order_source_id
2611 AND h.orig_sys_document_ref = l.orig_sys_document_ref
2612 AND nvl(l.rejected_flag, 'N') = 'Y';
2613
2614 EXCEPTION
2615 WHEN OTHERS THEN
2616 OE_BULK_MSG_PUB.Add_Exc_Msg
2617 ( G_PKG_NAME
2618 , 'Insert_Rejected_Lines_Ack'
2619 );
2620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2621 END Insert_Rejected_Lines_Ack;
2622
2623
2624 END OE_BULK_ACK_PVT;