1 PACKAGE BODY OE_BULK_VALUE_TO_ID AS
2 /* $Header: OEBSVIDB.pls 120.10 2006/10/26 23:34:35 sarsridh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Bulk_Value_To_Id';
5
6
7 ---------------------------------------------------------------------
8 -- FUNCTION Get_Contact_ID
9 -- Used to retrieve contact ID based on contact name and
10 -- contact organization. E.g. for ship_to_contact_id, p_site_use_id
11 -- should be ship_to_org_id
12 ---------------------------------------------------------------------
13
14 FUNCTION Get_Contact_ID
15 (p_contact IN VARCHAR2
16 ,p_site_use_id IN NUMBER
17 )
18 RETURN NUMBER
19 IS
20 l_id NUMBER;
21 --
22 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
23 --
24 BEGIN
25
26 SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
27 INTO l_id
28 FROM OE_CONTACTS_V CON
29 , HZ_CUST_ACCT_SITES CAS
30 , HZ_CUST_SITE_USES_ALL SITE
31 WHERE CON.NAME = p_contact
32 AND CON.CUSTOMER_ID = CAS.CUST_ACCOUNT_ID
33 AND CAS.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
34 AND CON.STATUS = 'A'
35 AND SITE.SITE_USE_ID = p_site_use_id;
36
37 RETURN l_id;
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 RETURN NULL;
42 END Get_Contact_ID;
43
44 --{ Bug 5054618
45 -- Function to get End Customer Site Use id
46
47 FUNCTION END_CUSTOMER_SITE
48 ( p_end_customer_site_address1 IN VARCHAR2
49 , p_end_customer_site_address2 IN VARCHAR2
50 , p_end_customer_site_address3 IN VARCHAR2
51 , p_end_customer_site_address4 IN VARCHAR2
52 , p_end_customer_site_location IN VARCHAR2
53 , p_end_customer_site_org IN VARCHAR2
54 , p_end_customer_id IN NUMBER
55 , p_end_customer_site_city IN VARCHAR2 DEFAULT NULL
56 , p_end_customer_site_state IN VARCHAR2 DEFAULT NULL
57 , p_end_customer_site_postalcode IN VARCHAR2 DEFAULT NULL
58 , p_end_customer_site_country IN VARCHAR2 DEFAULT NULL
59 , p_end_customer_site_use_code IN VARCHAR2 DEFAULT NULL
60 ) RETURN NUMBER
61 IS
62
63 -- cursor to get the site_id for end_customer
64 CURSOR c_site_use_id(in_end_customer_id number,in_end_customer_site_use_code varchar2) IS
65 SELECT site_use.site_use_id
66 FROM hz_locations loc,
67 hz_party_sites site,
68 hz_cust_acct_sites acct_site,
69 hz_cust_site_uses site_use
70 WHERE
71 site_use.cust_acct_site_id=acct_site.cust_acct_site_id
72 and acct_site.party_site_id=site.party_site_id
73 and site.location_id=loc.location_id
74 and site_use.status='A'
75 and acct_site.status='A' --bug 2752321
76 and acct_site.cust_account_id=in_end_customer_id
77 and loc.address1 = p_end_customer_site_address1
78 and nvl( loc.address2, fnd_api.g_miss_char) =
79 nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
80 and nvl( loc.address3, fnd_api.g_miss_char) =
81 nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
82 and nvl( loc.address4, fnd_api.g_miss_char) =
83 nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
84 and nvl( loc.city, fnd_api.g_miss_char) =
85 nvl( p_end_customer_site_city, fnd_api.g_miss_char)
86 and nvl( loc.state, fnd_api.g_miss_char) =
87 nvl( p_end_customer_site_state, fnd_api.g_miss_char)
88 and nvl( loc.postal_code, fnd_api.g_miss_char) =
89 nvl( p_end_customer_site_postalcode, fnd_api.g_miss_char)
90 and nvl( loc.country, fnd_api.g_miss_char) =
91 nvl( p_end_customer_site_country, fnd_api.g_miss_char)
92 and site_use.site_use_code = in_end_customer_site_use_code;
93
94 CURSOR c_site_use_id2(in_end_customer_id number,in_end_customer_site_use_code varchar2) IS
95 SELECT site_use.site_use_id
96 FROM hz_locations loc,
97 hz_party_sites site,
98 hz_cust_acct_sites acct_site,
99 hz_cust_site_uses site_use
100 WHERE loc.ADDRESS1 = p_end_customer_site_address1
101 AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
102 nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
103 AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
104 nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
105 AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
106 DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
107 DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
108 DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
109 nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
110 AND site_use.status = 'A'
111 AND acct_site.status ='A' --bug 2752321
112 AND acct_site.cust_account_id = p_end_customer_id
113 and site_use.site_use_code=in_end_customer_site_use_code
114 and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
115 and site.party_site_id=acct_site.party_site_id
116 and site.location_id=loc.location_id;
117
118
119 l_id number;
120
121 --
122 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
123 BEGIN
124
125 IF l_debug_level > 0 THEN
126 oe_debug_pub.add(' end customer site address1: in HVOP '||p_end_customer_site_address1);
127 oe_debug_pub.add(' address4: in HVOP '||p_end_customer_site_address4);
128 oe_debug_pub.add(' end_customer_id:in HVOP '||p_end_customer_id );
129 END IF;
130
131 IF nvl( p_end_customer_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
132 AND nvl( p_end_customer_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
133 AND nvl( p_end_customer_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
134 AND nvl( p_end_customer_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
135 AND nvl( p_end_customer_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
136 THEN
137 RETURN NULL;
138 END IF;
139
140
141 -- if no site_use_code passed in
142 -- try getting sites in the following preference
143 -- SOLD_TO, SHIP_TO, DELIVER_TO, BILL_TO
144 IF p_end_customer_site_use_code is null THEN
145
146 -- try for SOLD_TO
147 OPEN c_site_use_id(p_end_customer_id,'SOLD_TO');
148 FETCH c_site_use_id
149 INTO l_id;
150 IF c_site_use_id%FOUND then
151 CLOSE c_site_use_id;
152 return l_id;
153 ELSE
154 CLOSE c_site_use_id;
155 oe_Debug_pub.add(' q1');
156 OPEN c_site_use_id2(p_end_customer_id,'SOLD_TO');
157 FETCH c_site_use_id2
158 INTO l_id;
159 IF c_site_use_id2%FOUND then
160 CLOSE c_site_use_id2;
161 return l_id;
162 END IF;
163 CLOSE c_site_use_id2;
164 END IF;
165
166 -- try for SHIP_TO
167 OPEN c_site_use_id(p_end_customer_id,'SHIP_TO');
168 FETCH c_site_use_id
169 INTO l_id;
170 IF c_site_use_id%FOUND then
171 CLOSE c_site_use_id;
172 return l_id;
173 ELSE
174 CLOSE c_site_use_id;
175
176 OPEN c_site_use_id2(p_end_customer_id,'SHIP_TO');
177 FETCH c_site_use_id2
178 INTO l_id;
179 IF c_site_use_id2%FOUND then
180 CLOSE c_site_use_id2;
181 return l_id;
182 END IF;
183 CLOSE c_site_use_id2;
184 END IF;
185
186 -- try for DELIVER_TO
187 OPEN c_site_use_id(p_end_customer_id,'DELIVER_TO');
188 FETCH c_site_use_id
189 INTO l_id;
190 IF c_site_use_id%FOUND then
191 CLOSE c_site_use_id;
192 return l_id;
193 ELSE
194 CLOSE c_site_use_id;
195
196 OPEN c_site_use_id2(p_end_customer_id,'DELIVER_TO');
197 FETCH c_site_use_id2
198 INTO l_id;
199 IF c_site_use_id2%FOUND then
200 CLOSE c_site_use_id2;
201 return l_id;
202 END IF;
203 CLOSE c_site_use_id2;
204 END IF;
205
206 -- try for BILL_TO
207 OPEN c_site_use_id(p_end_customer_id,'BILL_TO');
208 FETCH c_site_use_id
209 INTO l_id;
210 IF c_site_use_id%FOUND then
211 CLOSE c_site_use_id;
212 return l_id;
213 ELSE
214 CLOSE c_site_use_id;
215
216 OPEN c_site_use_id2(p_end_customer_id,'BILL_TO');
217 FETCH c_site_use_id2
218 INTO l_id;
219 IF c_site_use_id2%FOUND then
220 CLOSE c_site_use_id2;
221 return l_id;
222 END IF;
223 CLOSE c_site_use_id2;
224 END IF;
225
226 -- nothing found, raise an error
227 raise NO_DATA_FOUND;
228
229 ELSE
230 -- site_use_code was passed in
231
232 OPEN c_site_use_id(p_end_customer_id,p_end_customer_site_use_code);
233 FETCH c_site_use_id
234 INTO l_id;
235 IF c_site_use_id%FOUND then
236 CLOSE c_site_use_id;
237 return l_id;
238 ELSE
239 CLOSE c_site_use_id;
240
241 OPEN c_site_use_id2(p_end_customer_id,p_end_customer_site_use_code);
242 FETCH c_site_use_id2
243 INTO l_id;
244 IF c_site_use_id2%FOUND then
245 CLOSE c_site_use_id2;
246 return l_id;
247 END IF;
248 CLOSE c_site_use_id2;
249 END IF;
250
251 -- no data found here, raise an error
252 raise NO_DATA_FOUND;
253
254 END IF;
255
256 EXCEPTION
257
258 WHEN NO_DATA_FOUND THEN
259
260 IF c_site_use_id%ISOPEN then
261 CLOSE c_site_use_id;
262 END IF;
263
264 IF c_site_use_id2%ISOPEN then
265 CLOSE c_site_use_id2;
266 END IF;
267
268 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
269 THEN
270
271 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
272 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_site_id');
273 OE_MSG_PUB.Add;
274
275 END IF;
276 RETURN FND_API.G_MISS_NUM;
277
278 WHEN OTHERS THEN
279
280 IF c_site_use_id%ISOPEN then
281 CLOSE c_site_use_id;
282 END IF;
283
284 IF c_site_use_id2%ISOPEN then
285 CLOSE c_site_use_id2;
286 END IF;
287
288 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
289 THEN
290 OE_MSG_PUB.Add_Exc_Msg
291 ( G_PKG_NAME
292 , 'end_cstomer_site_id'
293 );
294 END IF;
295
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297
298 END END_CUSTOMER_SITE;
299
300 -- Function to get end customer id
301
302 FUNCTION GET_END_CUSTOMER_CONTACT_ID
303 ( p_end_customer_contact IN VARCHAR2
304 , p_end_customer_id IN NUMBER
305 ) RETURN NUMBER IS
306
307 l_id NUMBER;
308 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
309 BEGIN
310
311 IF p_end_customer_contact IS NULL
312 THEN
313 RETURN NULL;
314 END IF;
315
316 SELECT CONTACT_ID
317 INTO l_id
318 FROM OE_CONTACTS_V
319 WHERE NAME = p_end_customer_contact
320 AND CUSTOMER_ID = p_end_customer_id;
321
322 RETURN l_id;
323
324 EXCEPTION
325
326 WHEN NO_DATA_FOUND THEN
327
328 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
329 THEN
330
331 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
332 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_contact_id');
333 OE_MSG_PUB.Add;
334
335 END IF;
336
337 RETURN FND_API.G_MISS_NUM;
338
339 WHEN OTHERS THEN
340
341 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
342 THEN
343 OE_MSG_PUB.Add_Exc_Msg
344 ( G_PKG_NAME
345 , 'End_customer_contact'
346 );
347 END IF;
348
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350
351 END GET_END_CUSTOMER_CONTACT_ID;
352 -- Bug 5054618}
353
354 ---------------------------------------------------------------------
355 -- PROCEDURE Headers
356 --
357 -- Value to ID conversions on header interface table for orders in
358 -- this batch.
359 -- It sets error_flag to 'Y' and appends ATTRIBUTE_STATUS column with a
360 -- number identifying each attribute that fails value to ID conversion.
361 ---------------------------------------------------------------------
362
363 PROCEDURE Headers(p_batch_id IN NUMBER)
364 IS
365 --
366 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
367
368 --bug 5620045
369 subqry_too_many_rows Exception;
370 PRAGMA EXCEPTION_INIT(subqry_too_many_rows, -01427);
371 --
372 --
373 BEGIN
374
375 -- There is one update SQL per attribute value to ID conversion.
376 -- NOTE that the sub-query needs to join back again to interface
377 -- tables so that for invalid data, it retrieves at least one row
378 -- with a null for the value column and populates error_flag,
379 -- attribute_status.
380
381 -- 1. ORDER_SOURCE : Not needed as order import already assigns request_ids
382 -- by order_source_id. For import, there cannot be any rows here where
383 -- ID column for order source is not populated but value column is.
384
385 -- 2. ORDER_TYPE
386
387 UPDATE OE_HEADERS_IFACE_ALL c
388 SET (ORDER_TYPE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
389 (SELECT b.ORDER_TYPE_ID,
390 DECODE(b.ORDER_TYPE_ID,NULL,'Y',NULL),
391 DECODE(b.ORDER_TYPE_ID,NULL,
392 c.ATTRIBUTE_STATUS||'002',c.ATTRIBUTE_STATUS)
393 FROM OE_ORDER_TYPES_V b, OE_HEADERS_IFACE_ALL d
394 WHERE d.order_type = b.name(+)
395 AND d.rowid = c.rowid
396 )
397 WHERE c.batch_id = p_batch_id
398 AND c.ORDER_TYPE_ID IS NULL
399 AND c.order_type IS NOT NULL;
400
401
402 -- 3. PRICE_LIST
403
404 UPDATE OE_HEADERS_IFACE_ALL c
405 SET (price_list_id,ERROR_FLAG,ATTRIBUTE_STATUS)=
406 (SELECT b.LIST_HEADER_ID,
407 DECODE(b.LIST_HEADER_ID,NULL,'Y',NULL),
408 DECODE(b.LIST_HEADER_ID,NULL,
409 c.ATTRIBUTE_STATUS||'003',c.ATTRIBUTE_STATUS)
410 FROM qp_list_headers_vl b, OE_HEADERS_IFACE_ALL d
411 WHERE d.price_list = b.name(+)
412 AND NVL(b.list_type_code,'PRL') IN ('PRL', 'AGR')
413 AND nvl(b.active_flag,'Y') = 'Y'
414 AND d.rowid = c.rowid
415 )
416 WHERE c.batch_id = p_batch_id
417 AND c.price_list_id IS NULL
418 AND c.price_list IS NOT NULL;
419
420
421 -- 4. CONVERSION_TYPE
422
423 UPDATE OE_HEADERS_IFACE_ALL c
424 SET (CONVERSION_TYPE_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
425 (SELECT b.CONVERSION_TYPE,
426 DECODE(b.CONVERSION_TYPE,NULL,'Y',NULL),
427 DECODE(b.CONVERSION_TYPE,NULL,
428 c.ATTRIBUTE_STATUS||'004',c.ATTRIBUTE_STATUS)
429 FROM OE_GL_DAILY_CONVERSION_TYPES_V b, OE_HEADERS_IFACE_ALL d
430 WHERE d.CONVERSION_TYPE = b.USER_CONVERSION_TYPE(+)
431 AND d.rowid = c.rowid
432 )
433 WHERE c.batch_id = p_batch_id
434 AND c.CONVERSION_TYPE_CODE IS NULL
435 AND c.CONVERSION_TYPE IS NOT NULL;
436
437
438 -- 5. SALESREP
439
440 UPDATE OE_HEADERS_IFACE_ALL c
441 SET (SALESREP_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
442 (SELECT /*+ PUSH_PRED(b) */ b.SALESREP_ID,
443 DECODE(b.SALESREP_ID,NULL,'Y',NULL),
444 DECODE(b.SALESREP_ID,NULL,
445 c.ATTRIBUTE_STATUS||'005',c.ATTRIBUTE_STATUS)
446 FROM RA_SALESREPS b, OE_HEADERS_IFACE_ALL d
447 WHERE d.SALESREP = b.NAME(+)
448 AND sysdate between NVL(start_date_active,sysdate)
449 and NVL(end_date_active,sysdate)
450 AND d.rowid = c.rowid
451 )
452 WHERE c.batch_id = p_batch_id
453 AND c.SALESREP_ID IS NULL
454 AND c.SALESREP IS NOT NULL;
455
456
457 -- 6. TAX_EXEMPT_REASON VARCHAR2(30)
458 -- eBTax changes
459 /* UPDATE OE_HEADERS_IFACE_ALL c
460 SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
461 (SELECT b.LOOKUP_CODE,
462 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
463 DECODE(b.LOOKUP_CODE,NULL,
464 c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
465 FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d
466 WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
467 AND b.LOOKUP_TYPE(+) = 'TAX_REASON'
468 AND d.rowid = c.rowid
469 )
470 WHERE c.batch_id = p_batch_id
471 AND c.TAX_EXEMPT_REASON_CODE IS NULL
472 AND c.TAX_EXEMPT_REASON IS NOT NULL;*/
473
474 UPDATE OE_HEADERS_IFACE_ALL c
475 SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
476 (SELECT b.LOOKUP_CODE,
477 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
478 DECODE(b.LOOKUP_CODE,NULL,
479 c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
480 FROM FND_LOOKUPS b, OE_HEADERS_IFACE_ALL d
481 WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
482 AND b.LOOKUP_TYPE(+) = 'ZX_EXEMPTION_REASON_CODE'
483 AND d.rowid = c.rowid)
484 WHERE c.batch_id = p_batch_id
485 AND c.TAX_EXEMPT_REASON_CODE IS NULL
486 AND c.TAX_EXEMPT_REASON IS NOT NULL;
487
488
489
490 -- 7. AGREEMENT VARCHAR2(50)
491
492 UPDATE OE_HEADERS_IFACE_ALL c
493 SET (AGREEMENT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
494 (SELECT b.AGREEMENT_ID,
495 DECODE(b.AGREEMENT_ID,NULL,'Y',NULL),
496 DECODE(b.AGREEMENT_ID,NULL,
497 c.ATTRIBUTE_STATUS||'007',c.ATTRIBUTE_STATUS)
498 FROM OE_AGREEMENTS_V b, OE_HEADERS_IFACE_ALL d
499 WHERE d.AGREEMENT = b.NAME(+)
500 AND sysdate between nvl(start_date_active, sysdate)
501 and nvl(end_date_active, sysdate)
502 AND d.rowid = c.rowid
503 )
504 WHERE c.batch_id = p_batch_id
505 AND c.AGREEMENT_ID IS NULL
506 AND c.AGREEMENT IS NOT NULL;
507
508
509
510 -- 8. INVOICING_RULE
511
512 UPDATE OE_HEADERS_IFACE_ALL c
513 SET (INVOICING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
514 (SELECT b.RULE_ID,
515 DECODE(b.RULE_ID,NULL,'Y',NULL),
516 DECODE(b.RULE_ID,NULL,
517 c.ATTRIBUTE_STATUS||'008',c.ATTRIBUTE_STATUS)
518 FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d
519 WHERE d.INVOICING_RULE = b.NAME(+)
520 AND b.STATUS(+) = 'A'
521 AND b.TYPE(+) = 'I'
522 AND d.rowid = c.rowid
523 )
524 WHERE c.batch_id = p_batch_id
525 AND c.INVOICING_RULE_ID IS NULL
526 AND c.INVOICING_RULE IS NOT NULL;
527
528 -- 9. ACCOUNTING_RULE VARCHAR2(30)
529
530 UPDATE OE_HEADERS_IFACE_ALL c
531 SET (ACCOUNTING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
532 (SELECT b.RULE_ID,
533 DECODE(b.RULE_ID,NULL,'Y',NULL),
534 DECODE(b.RULE_ID,NULL,
535 c.ATTRIBUTE_STATUS||'009',c.ATTRIBUTE_STATUS)
536 FROM OE_RA_RULES_V b, OE_HEADERS_IFACE_ALL d
537 WHERE d.ACCOUNTING_RULE = b.NAME(+)
538 AND b.STATUS(+) = 'A'
539 -- AND b.TYPE(+) = 'A' --we now allow variable accounting rules
540 AND d.rowid = c.rowid
541 )
542 WHERE c.batch_id = p_batch_id
543 AND c.ACCOUNTING_RULE_ID IS NULL
544 AND c.ACCOUNTING_RULE IS NOT NULL;
545
546 -- 10 PAYMENT_TERM VARCHAR2(30)
547
548 UPDATE OE_HEADERS_IFACE_ALL c
549 SET (PAYMENT_TERM_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
550 (SELECT b.TERM_ID,
551 DECODE(b.TERM_ID,NULL,'Y',NULL),
552 DECODE(b.TERM_ID,NULL,
553 c.ATTRIBUTE_STATUS||'010',c.ATTRIBUTE_STATUS)
554 FROM OE_RA_TERMS_V b, OE_HEADERS_IFACE_ALL d
555 WHERE d.PAYMENT_TERM = b.NAME(+)
556 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
557 AND NVL(END_DATE_ACTIVE, SYSDATE)
558 AND d.rowid = c.rowid
559 )
560 WHERE c.batch_id = p_batch_id
561 AND c.PAYMENT_TERM_ID IS NULL
562 AND c.PAYMENT_TERM IS NOT NULL;
563
564
565 -- 11. FREIGHT_TERMS VARCHAR2(30)
566
567 UPDATE OE_HEADERS_IFACE_ALL c
568 SET (FREIGHT_TERMS_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
569 (SELECT b.LOOKUP_CODE,
570 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
571 DECODE(b.LOOKUP_CODE,NULL,
572 c.ATTRIBUTE_STATUS||'011',c.ATTRIBUTE_STATUS)
573 FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
574 WHERE d.FREIGHT_TERMS = b.MEANING(+)
575 AND b.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
576 AND b.enabled_flag(+) = 'Y'
577 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
578 AND NVL(END_DATE_ACTIVE, SYSDATE)
579 AND d.rowid = c.rowid
580 )
581 WHERE c.batch_id = p_batch_id
582 AND c.FREIGHT_TERMS_CODE IS NULL
583 AND c.FREIGHT_TERMS IS NOT NULL;
584
585
586 -- 12. FOB_POINT VARCHAR2(30)
587
588 UPDATE OE_HEADERS_IFACE_ALL c
589 SET (FOB_POINT_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
590 (SELECT b.LOOKUP_CODE,
591 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
592 DECODE(b.LOOKUP_CODE,NULL,
593 c.ATTRIBUTE_STATUS||'012',c.ATTRIBUTE_STATUS)
594 FROM OE_AR_LOOKUPS_V b, OE_HEADERS_IFACE_ALL d
595 WHERE d.FOB_POINT = b.MEANING(+)
596 AND b.LOOKUP_TYPE(+) = 'FOB'
597 AND b.enabled_flag(+) = 'Y'
598 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
599 AND NVL(END_DATE_ACTIVE, SYSDATE)
600 AND d.rowid = c.rowid
601 )
602 WHERE c.batch_id = p_batch_id
603 AND c.FOB_POINT_CODE IS NULL
604 AND c.FOB_POINT IS NOT NULL;
605
606
607 -- 13. SOLD_TO_ORG
608 -- NOTE 1: Value to ID for sold to (customer) should be done before
609 -- other dependent fields like sites and contacts.
610 -- NOTE 2: Status field update is a union - if customer number is
611 -- supplied, it takes precedence over the name or sold to org value field.
612
613 --bug 5620045
614 --Catch subqry_too_many_rows exception
615 BEGIN
616 UPDATE OE_HEADERS_IFACE_ALL c
617 SET (SOLD_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
618 (SELECT b.CUST_ACCOUNT_ID,
619 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
620 DECODE(b.CUST_ACCOUNT_ID,NULL,
621 c.ATTRIBUTE_STATUS||'013',c.ATTRIBUTE_STATUS)
622 FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
623 WHERE d.CUSTOMER_NUMBER IS NOT NULL
624 AND d.CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
625 AND b.STATUS(+) = 'A'
626 AND d.rowid = c.rowid
627 UNION ALL
628 SELECT b.CUST_ACCOUNT_ID,
629 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
630 DECODE(b.CUST_ACCOUNT_ID,NULL,
631 c.ATTRIBUTE_STATUS||'013',c.ATTRIBUTE_STATUS)
632 FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
633 WHERE d.CUSTOMER_NUMBER IS NULL
634 AND d.SOLD_TO_ORG = e.PARTY_NAME(+)
635 AND b.STATUS(+) = 'A'
636 AND b.PARTY_ID(+) = e.PARTY_ID
637 AND d.rowid = c.rowid
638 )
639 WHERE c.batch_id = p_batch_id
640 AND c.SOLD_TO_ORG_ID IS NULL
641 AND (c.SOLD_TO_ORG IS NOT NULL OR
642 c.CUSTOMER_NUMBER IS NOT NULL);
643 EXCEPTION
644 WHEN subqry_too_many_rows THEN
645 UPDATE OE_HEADERS_IFACE_ALL c
646 SET ERROR_FLAG = 'Y',
647 ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'013'
648 WHERE c.batch_id = p_batch_id
649 AND c.SOLD_TO_ORG_ID IS NULL
650 AND c.SOLD_TO_ORG IS NOT NULL;
651 End;
652
653
654 -- Bulk Import does NOT check for customer relationships in
655 -- value to ID.
656 -- If value columns are provided for ship to or bill to,
657 -- 1) it would search in the addresses for ship to customer
658 -- or bill to customer if supplied.
659 -- 2) if ship to or bill to customer is not supplied, it will
660 -- search in addresses for the sold to customer
661 -- The validation to ensure that ship to and bill to customer
662 -- are valid as per customer relationships parameter will be
663 -- done later during bulk entity processing in OEBLHDRB/OEBLLINB.pls
664
665 -- Ship To and Invoice To customer should be passed only if
666 -- customer relationships is 'Y' or 'A'
667 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS <> 'N' THEN
668
669 -- First, value to ID conversions for ship to customer and
670 -- and invoice_to_customer
671
672 -- SHIP_TO_CUSTOMER
673 --bug 5620045
674 --catch subqry_too_many_rows
675 BEGIN
676 UPDATE OE_HEADERS_IFACE_ALL c
677 SET (SHIP_TO_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
678 (SELECT b.CUST_ACCOUNT_ID,
679 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
680 DECODE(b.CUST_ACCOUNT_ID,NULL,
681 d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
682 FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
683 WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL
684 AND d.SHIP_TO_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
685 AND b.STATUS(+) = 'A'
686 AND d.rowid = c.rowid
687 UNION ALL
688 SELECT b.CUST_ACCOUNT_ID,
689 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
690 DECODE(b.CUST_ACCOUNT_ID,NULL,
691 d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
692 FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
693 WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NULL
694 AND d.SHIP_TO_CUSTOMER = e.PARTY_NAME(+)
695 AND b.STATUS(+) = 'A'
696 AND b.PARTY_ID(+) = e.PARTY_ID
697 AND d.rowid = c.rowid
698 )
699 WHERE c.batch_id = p_batch_id
700 AND c.SHIP_TO_CUSTOMER_ID IS NULL
701 AND (c.SHIP_TO_CUSTOMER IS NOT NULL OR
702 c.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL);
703 EXCEPTION
704 WHEN subqry_too_many_rows THEN
705 UPDATE OE_HEADERS_IFACE_ALL c
706 SET ERROR_FLAG = 'Y',
707 ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'014'
708 WHERE c.batch_id = p_batch_id
709 AND c.SHIP_TO_CUSTOMER_ID IS NULL
710 AND c.SHIP_TO_CUSTOMER IS NOT NULL;
711 END;
712
713 -- INVOICE_TO_CUSTOMER
714 --bug 5620045
715 --catch subqry_too_many_rows
716 BEGIN
717 UPDATE OE_HEADERS_IFACE_ALL c
718 SET (INVOICE_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
719 (SELECT b.CUST_ACCOUNT_ID,
720 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
721 DECODE(b.CUST_ACCOUNT_ID,NULL,
722 d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
723 FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
724 WHERE d.INVOICE_CUSTOMER_NUMBER IS NOT NULL
725 AND d.INVOICE_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
726 AND b.STATUS(+) = 'A'
727 AND d.rowid = c.rowid
728 UNION ALL
729 SELECT b.CUST_ACCOUNT_ID,
730 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
731 DECODE(b.CUST_ACCOUNT_ID,NULL,
732 d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
733 FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
734 WHERE d.INVOICE_CUSTOMER_NUMBER IS NULL
735 AND d.INVOICE_CUSTOMER = e.PARTY_NAME(+)
736 AND b.STATUS(+) = 'A'
737 AND b.PARTY_ID(+) = e.PARTY_ID
738 AND d.rowid = c.rowid
739 )
740 WHERE c.batch_id = p_batch_id
741 AND c.INVOICE_CUSTOMER_ID IS NULL
742 AND (c.INVOICE_CUSTOMER IS NOT NULL OR
743 c.INVOICE_CUSTOMER_NUMBER IS NOT NULL);
744 EXCEPTION
745 WHEN subqry_too_many_rows THEN
746 UPDATE OE_HEADERS_IFACE_ALL c
747 SET ERROR_FLAG = 'Y',
748 ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'015'
749 WHERE c.batch_id = p_batch_id
750 AND c.INVOICE_CUSTOMER_ID IS NULL
751 AND c.INVOICE_CUSTOMER IS NOT NULL ;
752 END;
753
754 END IF;
755
756 -- 14. SHIP_TO_ORG
757
758 UPDATE OE_HEADERS_IFACE_ALL c
759 SET (SHIP_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
760 (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
761 DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
762 DECODE(b.ORGANIZATION_ID,NULL,
763 d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
764 FROM OE_SHIP_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d
765 WHERE d.ship_to_address1 = ADDRESS_LINE_1(+)
766 AND nvl(d.ship_to_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
767 AND nvl(d.ship_to_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
768 --AND nvl(d.ship_to_address4,'NIL') = ADDRESS_LINE_4
769 AND nvl(d.ship_to_city,'NIL') = nvl(town_or_city(+),'NIL')
770 AND nvl(d.ship_to_state,'NIL') = nvl(state(+),'NIL')
771 AND nvl(d.ship_to_postal_code,'NIL') = nvl(postal_code(+),'NIL')
772 AND nvl(d.ship_to_country,'NIL') = nvl(country(+),'NIL')
773 AND nvl(STATUS(+),'A') = 'A'
774 AND b.CUSTOMER_ID(+) = nvl(d.SHIP_TO_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
775 AND d.rowid = c.rowid
776 )
777 WHERE c.batch_id = p_batch_id
778 AND c.SHIP_TO_ORG_ID IS NULL
779 AND (c.SHIP_TO_ADDRESS1 IS NOT NULL
780 OR c.ship_to_address2 IS NOT NULL
781 OR c.ship_to_address3 IS NOT NULL
782 OR c.ship_to_address4 IS NOT NULL
783 OR c.ship_to_org IS NOT NULL
784 );
785
786 -- 15. INVOICE_TO_ORG
787
788 UPDATE OE_HEADERS_IFACE_ALL c
789 SET (INVOICE_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
790 (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
791 DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
792 DECODE(b.ORGANIZATION_ID,NULL,
793 d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
794 FROM OE_INVOICE_TO_ORGS_V b, OE_HEADERS_IFACE_ALL d
795 WHERE d.invoice_address1 = ADDRESS_LINE_1(+)
796 AND nvl(d.invoice_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
797 AND nvl(d.invoice_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
798 --AND nvl(d.invoice_address4,'NIL') = ADDRESS_LINE_4
799 AND nvl(d.invoice_city,'NIL') = nvl(town_or_city(+),'NIL')
800 AND nvl(d.invoice_state,'NIL') = nvl(state(+),'NIL')
801 AND nvl(d.invoice_postal_code,'NIL') = nvl(postal_code(+),'NIL')
802 AND nvl(d.invoice_country,'NIL') = nvl(country(+),'NIL')
803 AND nvl(STATUS(+),'A') = 'A'
804 AND b.CUSTOMER_ID(+) = nvl(d.INVOICE_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
805 AND d.rowid = c.rowid
806 )
807 WHERE c.batch_id = p_batch_id
808 AND c.INVOICE_TO_ORG_ID IS NULL
809 AND (c.invoice_address1 IS NOT NULL
810 OR c.invoice_address2 IS NOT NULL
811 OR c.invoice_address3 IS NOT NULL
812 OR c.invoice_address4 IS NOT NULL
813 OR c.invoice_to_org IS NOT NULL
814 );
815
816
817 -- DELIVER_TO_ORG No mapping columns in the interface table
818
819 -- 16. SOLD_TO_CONTACT VARCHAR2(30)
820
821 UPDATE OE_HEADERS_IFACE_ALL c
822 SET (SOLD_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
823 (SELECT DISTINCT b.CONTACT_ID,
824 DECODE(b.CONTACT_ID,NULL,'Y',NULL),
825 DECODE(b.CONTACT_ID,NULL,
826 c.ATTRIBUTE_STATUS||'016',c.ATTRIBUTE_STATUS)
827 FROM OE_CONTACTS_V b
828 WHERE c.SOLD_TO_CONTACT = b.NAME(+)
829 AND c.SOLD_TO_ORG_ID = b.CUSTOMER_ID(+)
830 )
831 WHERE c.batch_id = p_batch_id
832 AND c.SOLD_TO_CONTACT_ID IS NULL
833 AND c.SOLD_TO_CONTACT IS NOT NULL
834 AND c.SOLD_TO_ORG_ID IS NOT NULL;
835
836
837 -- 17. SHIP_TO_CONTACT VARCHAR2(30)
838
839 UPDATE OE_HEADERS_IFACE_ALL c
840 SET (SHIP_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
841 (SELECT Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),
842 DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,'Y',NULL),
843 DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,
844 c.ATTRIBUTE_STATUS||'017',c.ATTRIBUTE_STATUS)
845 FROM DUAL
846 )
847 WHERE c.batch_id = p_batch_id
848 AND c.SHIP_TO_CONTACT_ID IS NULL
849 AND c.SHIP_TO_CONTACT IS NOT NULL;
850
851
852 -- 18. INVOICE_TO_CONTACT VARCHAR2(30)
853
854 UPDATE OE_HEADERS_IFACE_ALL c
855 SET (INVOICE_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
856 (SELECT Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),
857 DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,'Y',NULL),
858 DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,
859 c.ATTRIBUTE_STATUS||'018',c.ATTRIBUTE_STATUS)
860 FROM DUAL
861 )
862 WHERE c.batch_id = p_batch_id
863 AND c.INVOICE_TO_CONTACT_ID IS NULL
864 AND c.INVOICE_TO_CONTACT IS NOT NULL;
865
866
867 -- 19. DELIVER_TO_CONTACT VARCHAR2(30)
868
869 UPDATE OE_HEADERS_IFACE_ALL c
870 SET (DELIVER_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
871 (SELECT Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),
872 DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,'Y',NULL),
873 DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,
874 c.ATTRIBUTE_STATUS||'019',c.ATTRIBUTE_STATUS)
875 FROM DUAL
876 )
877 WHERE c.batch_id = p_batch_id
878 AND c.DELIVER_TO_CONTACT_ID IS NULL
879 AND c.DELIVER_TO_CONTACT IS NOT NULL;
880
881
882 -- SHIPPING_METHOD_CODE VARCHAR2(30)
883 UPDATE OE_HEADERS_IFACE_ALL c
884 SET (SHIPPING_METHOD_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
885 (SELECT b.LOOKUP_CODE,
886 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
887 DECODE(b.LOOKUP_CODE,NULL,
888 c.ATTRIBUTE_STATUS||'024',c.ATTRIBUTE_STATUS)
889 FROM OE_SHIP_METHODS_V b, OE_HEADERS_IFACE_ALL d
890 WHERE d.SHIPPING_METHOD = b.MEANING(+)
891 AND b.enabled_flag(+) = 'Y'
892 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE(+), SYSDATE)
893 AND NVL(END_DATE_ACTIVE(+), SYSDATE)
894 AND d.rowid = c.rowid
895 )
896 WHERE c.batch_id = p_batch_id
897 AND c.SHIPPING_METHOD_CODE IS NULL
898 AND c.SHIPPING_METHOD IS NOT NULL;
899
900 -- FREIGHT_CARRIER_CODE: value column does not exist on interface tables
901
902 -- SALES_CHANNEL_CODE
903
904 UPDATE OE_HEADERS_IFACE_ALL c
905 SET (SALES_CHANNEL_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
906 (SELECT b.LOOKUP_CODE,
907 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
908 DECODE(b.LOOKUP_CODE,NULL,
909 c.ATTRIBUTE_STATUS||'022',c.ATTRIBUTE_STATUS)
910 FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
911 WHERE d.SALES_CHANNEL = b.MEANING(+)
912 AND b.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
913 AND b.enabled_flag(+) = 'Y'
914 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
915 AND NVL(END_DATE_ACTIVE, SYSDATE)
916 AND d.rowid = c.rowid
917 )
918 WHERE c.batch_id = p_batch_id
919 AND c.SALES_CHANNEL_CODE IS NULL
920 AND c.SALES_CHANNEL IS NOT NULL;
921
922 -- SHIPMENT_PRIORITY_CODE
923
924 UPDATE OE_HEADERS_IFACE_ALL c
925 SET (SHIPMENT_PRIORITY_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
926 (SELECT b.LOOKUP_CODE,
927 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
928 DECODE(b.LOOKUP_CODE,NULL,
929 c.ATTRIBUTE_STATUS||'023',c.ATTRIBUTE_STATUS)
930 FROM OE_LOOKUPS b, OE_HEADERS_IFACE_ALL d
931 WHERE d.SHIPMENT_PRIORITY = b.MEANING(+)
932 AND b.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
933 AND b.enabled_flag(+) = 'Y'
934 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
935 AND NVL(END_DATE_ACTIVE, SYSDATE)
936 AND d.rowid = c.rowid
937 )
938 WHERE c.batch_id = p_batch_id
939 AND c.SHIPMENT_PRIORITY_CODE IS NULL
940 AND c.SHIPMENT_PRIORITY IS NOT NULL;
941
942 -- Sold_to_site_use_id
943 --abghosh
944
945 IF OE_CODE_CONTROL.Code_Release_Level >='110510' THEN
946 UPDATE OE_HEADERS_IFACE_ALL c
947 SET (SOLD_TO_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
948 (SELECT SITE.SITE_USE_ID,
949 DECODE(SITE.SITE_USE_ID,null,'Y',null),
950 DECODE(SITE.SITE_USE_ID,null,d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
951
952 FROM HZ_CUST_SITE_USES_ALL SITE,
953 HZ_PARTY_SITES PARTY_SITE,
954 HZ_LOCATIONS LOC,
955 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
956 OE_HEADERS_IFACE_ALL d
957
958 WHERE SITE.SITE_USE_CODE ='SOLD_TO'
959 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
960 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
961 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
962 AND d.sold_to_location_address1= LOC.ADDRESS1(+)
963 AND nvl(d.sold_to_location_address2,'NIL')=nvl(LOC.ADDRESS2(+),'NIL')
964 AND nvl(d.sold_to_location_address3,'NIL')=nvl(LOC.ADDRESS3(+),'NIL')
965 AND nvl(d.sold_to_location_address4,'NIL')=nvl(LOC.ADDRESS4(+),'NIL')
966 AND nvl(d.sold_to_location_city,'NIL')=nvl(LOC.CITY(+),'NIL')
967 AND nvl(d.sold_to_location_state,'NIL')=nvl(LOC.STATE(+),'NIL')
968 AND nvl(d.sold_to_location_postal_code,'NIL')=nvl(LOC.POSTAL_CODE(+),'NIL')
969 AND nvl(d.sold_to_location_country,'NIL')=nvl(LOC.COUNTRY(+),'NIL')
970 AND nvl(SITE.STATUS,'A')='A'
971 AND nvl(ACCT_SITE.STATUS,'A')='A'
972 AND ACCT_SITE.CUST_ACCOUNT_ID(+)=d.SOLD_TO_ORG_ID
973 AND d.rowid=c.rowid
974 )
975 WHERE c.batch_id=p_batch_id
976 AND c.SOLD_TO_SITE_USE_ID IS NULL
977 AND (c.SOLD_TO_LOCATION_ADDRESS1 IS NOT NULL
978 OR c.SOLD_TO_LOCATION_ADDRESS2 IS NOT NULL
979 OR c.SOLD_TO_LOCATION_ADDRESS3 IS NOT NULL
980 OR c.SOLD_TO_LOCATION_ADDRESS4 IS NOT NULL
981 );
982
983 --{ Bug 5054618
984 --End customer changes for HVOP
985 --bug 5620045 catch subqry_too_many_rows
986 BEGIN
987 UPDATE OE_HEADERS_IFACE_ALL c
988 SET (END_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
989 (SELECT b.CUST_ACCOUNT_ID,
990 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
991 DECODE(b.CUST_ACCOUNT_ID,NULL,
992 d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS)
993 FROM HZ_CUST_ACCOUNTS b, OE_HEADERS_IFACE_ALL d
994 WHERE d.END_CUSTOMER_NUMBER IS NOT NULL AND d.END_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
995 AND b.STATUS(+) = 'A' AND d.rowid = c.rowid
996 UNION ALL
997 SELECT b.CUST_ACCOUNT_ID,
998 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
999 DECODE(b.CUST_ACCOUNT_ID,NULL,
1000 d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS) FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_HEADERS_IFACE_ALL d
1001 WHERE d.END_CUSTOMER_NUMBER IS NULL AND d.END_CUSTOMER_NAME = e.PARTY_NAME(+)
1002 AND b.STATUS(+) = 'A' AND b.PARTY_ID(+) = e.PARTY_ID
1003 AND d.rowid = c.rowid)
1004 WHERE c.batch_id = p_batch_id and c.END_CUSTOMER_ID IS NULL AND
1005 (c.END_CUSTOMER_NAME IS NOT NULL OR c.END_CUSTOMER_NUMBER IS NOT NULL);
1006 EXCEPTION
1007 WHEN subqry_too_many_rows THEN
1008 UPDATE OE_HEADERS_IFACE_ALL c
1009 SET ERROR_FLAG = 'Y',
1010 ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'025'
1011 WHERE c.batch_id = p_batch_id
1012 AND c.END_CUSTOMER_ID IS NULL
1013 AND c.END_CUSTOMER_NAME IS NOT NULL ;
1014 END;
1015
1016
1017
1018 UPDATE OE_HEADERS_IFACE_ALL c
1019 SET (END_CUSTOMER_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1020 (SELECT END_CUSTOMER_SITE(c.end_customer_address1,c.end_customer_address2,c.end_customer_address3,c.end_customer_address4,
1021 c.end_customer_location, NULL,c.end_customer_id,c.end_customer_city,
1022 c.end_customer_state,c.end_customer_postal_code,c.end_customer_country,NULL),
1023 DECODE(END_CUSTOMER_SITE(c.end_customer_address1,c.end_customer_address2,c.end_customer_address3,
1024 c.end_customer_address4,c.end_customer_location,NULL,
1025 c.end_customer_id,c.end_customer_city,c.end_customer_state,
1026 c.end_customer_postal_code,c.end_customer_country,
1027 NULL),NULL,'Y',NULL),
1028 DECODE(END_CUSTOMER_SITE(c.end_customer_address1,
1029 c.end_customer_address2,
1030 c.end_customer_address3,
1031 c.end_customer_address4,
1032 c.end_customer_location,
1033 NULL,
1034 c.end_customer_id,
1035 c.end_customer_city,
1036 c.end_customer_state,
1037 c.end_customer_postal_code,
1038 c.end_customer_country,
1039 NULL),NULL,c.ATTRIBUTE_STATUS||'027',c.ATTRIBUTE_STATUS)
1040 FROM DUAL)
1041 WHERE c.batch_id=p_batch_id
1042 AND c.END_CUSTOMER_SITE_USE_ID IS NULL AND c.END_CUSTOMER_ID IS NOT NULL
1043 AND (c.END_CUSTOMER_ADDRESS1 IS NOT NULL
1044 OR c.END_CUSTOMER_ADDRESS2 IS NOT NULL
1045 OR c.END_CUSTOMER_ADDRESS3 IS NOT NULL
1046 OR c.END_CUSTOMER_ADDRESS4 IS NOT NULL);
1047
1048
1049 UPDATE OE_HEADERS_IFACE_ALL c
1050 SET (END_CUSTOMER_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1051 (SELECT Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),
1052 DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),NULL,'Y',NULL),
1053 DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),NULL,c.ATTRIBUTE_STATUS||'026',c.ATTRIBUTE_STATUS)
1054 FROM DUAL) WHERE c.batch_id = p_batch_id and c.END_CUSTOMER_CONTACT_ID IS NULL AND c.END_CUSTOMER_CONTACT IS NOT NULL;
1055
1056 --Bug 5054618}
1057
1058 UPDATE OE_HEADERS_IFACE_ALL c
1059 SET (IB_OWNER_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1060 (SELECT b.LOOKUP_CODE,
1061 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1062 DECODE(b.LOOKUP_CODE,NULL,
1063 c.ATTRIBUTE_STATUS||'028',c.ATTRIBUTE_STATUS)
1064 FROM -- OE_LOOKUPS b,
1065 ( select lookup_code, meaning
1066 from OE_LOOKUPS
1067 where lookup_type In ('ITEM_OWNER', 'ONT_INSTALL_BASE')
1068 and enabled_flag = 'Y'
1069 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1070 AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1071 OE_HEADERS_IFACE_ALL d
1072 WHERE d.IB_OWNER= b.MEANING(+)
1073 --AND ( b.LOOKUP_TYPE(+) = 'ITEM_OWNER'
1074 --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1075 --AND b.enabled_flag(+) = 'Y'
1076 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1077 --AND NVL(END_DATE_ACTIVE, SYSDATE)
1078 AND d.rowid = c.rowid
1079 AND rownum = 1
1080 )
1081 WHERE c.batch_id = p_batch_id
1082 AND c.IB_OWNER_CODE IS NULL AND c.IB_OWNER IS NOT NULL;
1083
1084 UPDATE OE_HEADERS_IFACE_ALL c
1085 SET (IB_INSTALLED_AT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1086 (SELECT b.LOOKUP_CODE,
1087 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1088 DECODE(b.LOOKUP_CODE,NULL,
1089 c.ATTRIBUTE_STATUS||'029',c.ATTRIBUTE_STATUS)
1090 FROM -- OE_LOOKUPS b,
1091 ( select lookup_code, meaning
1092 from OE_LOOKUPS
1093 where lookup_type In ('ITEM_INSTALL_LOCATION', 'ONT_INSTALL_BASE')
1094 and enabled_flag = 'Y'
1095 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1096 AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1097 OE_HEADERS_IFACE_ALL d
1098 WHERE d.IB_INSTALLED_AT_LOCATION = b.MEANING(+)
1099 --AND ( b.LOOKUP_TYPE(+) = 'ITEM_INSTALL_LOCATION'
1100 --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1101 --AND b.enabled_flag(+) = 'Y'
1102 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1103 --AND NVL(END_DATE_ACTIVE, SYSDATE)
1104 AND d.rowid = c.rowid
1105 AND rownum = 1
1106 )
1107 WHERE c.batch_id = p_batch_id
1108 AND c.IB_INSTALLED_AT_LOCATION_CODE IS NULL
1109 AND c.IB_INSTALLED_AT_LOCATION IS NOT NULL;
1110
1111 UPDATE OE_HEADERS_IFACE_ALL c
1112 SET (IB_CURRENT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1113 (SELECT b.LOOKUP_CODE,
1114 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1115 DECODE(b.LOOKUP_CODE,NULL,
1116 c.ATTRIBUTE_STATUS||'030',c.ATTRIBUTE_STATUS)
1117 FROM -- OE_LOOKUPS b,
1118 ( select lookup_code, meaning
1119 from OE_LOOKUPS
1120 where lookup_type In ('ITEM_CURRENT_LOCATION', 'ONT_INSTALL_BASE')
1121 and enabled_flag = 'Y'
1122 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1123 AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1124 OE_HEADERS_IFACE_ALL d
1125 WHERE d.IB_CURRENT_LOCATION = b.MEANING(+)
1126 --AND ( b.LOOKUP_TYPE(+) = 'ITEM_CURRENT_LOCATION'
1127 --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1128 --AND b.enabled_flag(+) = 'Y'
1129 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1130 -- AND NVL(END_DATE_ACTIVE, SYSDATE)
1131 AND d.rowid = c.rowid
1132 AND rownum = 1
1133 )
1134 WHERE c.batch_id = p_batch_id
1135 AND c.IB_CURRENT_LOCATION_CODE IS NULL
1136 AND c.IB_CURRENT_LOCATION IS NOT NULL;
1137
1138
1139 -- OE_debug_pub.add('talking from value to id success');
1140
1141 END IF;
1142
1143 -- SHIP_FROM_ORG_ID: No Value to ID conversion in OEXSVIDB.pls!
1144
1145 -- TAX_EXEMPT_FLAG: Value column does not exist on interface tables
1146
1147 -- TAX_POINT_CODE: Unused column
1148
1149 -- PAYMENT_TYPE_CODE: Value column does not exist on interface tables
1150
1151 -- CREDIT_CARD_CODE: Not supported for BULK!
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 IF l_debug_level > 0 THEN
1156 oe_debug_pub.add( 'OTHERS ERROR , OE_BULK_VALUE_TO_ID.HEADERS' ) ;
1157 END IF;
1158 IF l_debug_level > 0 THEN
1159 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1160 END IF;
1161 OE_BULK_MSG_PUB.Add_Exc_Msg
1162 ( G_PKG_NAME
1163 , 'Headers'
1164 );
1165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166 END Headers;
1167
1168
1169 ---------------------------------------------------------------------
1170 -- PROCEDURE Lines
1171 --
1172 -- Value to ID conversions on lines interface table.
1173 -- It sets error_flag to 'Y' and appends ATTRIBUTE_STATUS column with a
1174 -- number identifying each attribute that fails value to ID conversion.
1175 ---------------------------------------------------------------------
1176
1177 PROCEDURE Lines(p_batch_id IN NUMBER)
1178 IS
1179 --
1180 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1181
1182 --bug 5620045
1183 subqry_too_many_rows Exception;
1184 PRAGMA EXCEPTION_INIT(subqry_too_many_rows, -01427);
1185 --
1186 BEGIN
1187
1188 -- LINE_TYPE
1189
1190 UPDATE OE_LINES_IFACE_ALL c
1191 SET (LINE_TYPE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1192 (SELECT b.transaction_type_id,
1193 DECODE(b.transaction_type_id,NULL,'Y',NULL),
1194 DECODE(b.transaction_type_id,NULL,
1195 c.ATTRIBUTE_STATUS||'020',c.ATTRIBUTE_STATUS)
1196 FROM OE_TRANSACTION_TYPES_TL b, OE_LINES_IFACE_ALL d
1197 WHERE d.LINE_TYPE = b.name(+)
1198 AND NVL(b.LANGUAGE,USERENV('LANG')) = USERENV('LANG')
1199 AND d.rowid = c.rowid
1200 )
1201 WHERE (order_source_id, orig_sys_document_ref) IN
1202 ( SELECT order_source_id, orig_sys_document_ref
1203 FROM OE_HEADERS_IFACE_ALL
1204 WHERE batch_id = p_batch_id)
1205 AND c.LINE_TYPE_ID IS NULL
1206 AND c.LINE_TYPE IS NOT NULL;
1207
1208 -- PRICE_LIST
1209
1210 UPDATE OE_LINES_IFACE_ALL c
1211 SET (price_list_id,ERROR_FLAG,ATTRIBUTE_STATUS)=
1212 (SELECT b.LIST_HEADER_ID,
1213 DECODE(b.LIST_HEADER_ID,NULL,'Y',NULL),
1214 DECODE(b.LIST_HEADER_ID,NULL,
1215 c.ATTRIBUTE_STATUS||'003',c.ATTRIBUTE_STATUS)
1216 FROM qp_list_headers_vl b, OE_LINES_IFACE_ALL d
1217 WHERE d.price_list = b.name(+)
1218 AND NVL(b.list_type_code,'PRL') IN ('PRL', 'AGR')
1219 AND nvl(b.active_flag,'Y') = 'Y'
1220 AND d.rowid = c.rowid
1221 )
1222 WHERE (order_source_id, orig_sys_document_ref) IN
1223 ( SELECT order_source_id, orig_sys_document_ref
1224 FROM OE_HEADERS_IFACE_ALL
1225 WHERE batch_id = p_batch_id)
1226 AND c.price_list_id IS NULL
1227 AND c.price_list IS NOT NULL;
1228
1229
1230 -- SALESREP
1231
1232 UPDATE OE_LINES_IFACE_ALL c
1233 SET ( SALESREP_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1234 (SELECT /*+ PUSH_PRED(b) */ b.SALESREP_ID,
1235 DECODE(b.SALESREP_ID,NULL,'Y',NULL),
1236 DECODE(b.SALESREP_ID,NULL,
1237 c.ATTRIBUTE_STATUS||'005',c.ATTRIBUTE_STATUS)
1238 FROM RA_SALESREPS b, OE_LINES_IFACE_ALL d
1239 WHERE d.SALESREP = b.NAME(+)
1240 AND sysdate between NVL(start_date_active,sysdate)
1241 and NVL(end_date_active,sysdate)
1242 AND d.rowid = c.rowid
1243 )
1244 WHERE (order_source_id, orig_sys_document_ref) IN
1245 ( SELECT order_source_id, orig_sys_document_ref
1246 FROM OE_HEADERS_IFACE_ALL
1247 WHERE batch_id = p_batch_id)
1248 AND c.SALESREP_ID IS NULL
1249 AND c.SALESREP IS NOT NULL;
1250
1251
1252
1253 -- TAX_EXEMPT_REASON VARCHAR2(30)
1254 -- eBTax changes
1255 /*UPDATE OE_LINES_IFACE_ALL c
1256 SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1257 (SELECT b.LOOKUP_CODE,
1258 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1259 DECODE(b.LOOKUP_CODE,NULL,
1260 c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
1261 FROM OE_AR_LOOKUPS_V b, OE_LINES_IFACE_ALL d
1262 WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
1263 AND b.LOOKUP_TYPE(+) = 'TAX_REASON'
1264 AND b.enabled_flag(+) = 'Y'
1265 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1266 AND NVL(END_DATE_ACTIVE, SYSDATE)
1267 AND d.rowid = c.rowid
1268 )
1269 WHERE (order_source_id, orig_sys_document_ref) IN
1270 ( SELECT order_source_id, orig_sys_document_ref
1271 FROM OE_HEADERS_IFACE_ALL
1272 WHERE batch_id = p_batch_id)
1273 AND c.TAX_EXEMPT_REASON_CODE IS NULL
1274 AND c.TAX_EXEMPT_REASON IS NOT NULL;*/
1275
1276 UPDATE OE_LINES_IFACE_ALL c
1277 SET (TAX_EXEMPT_REASON_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1278 (SELECT b.LOOKUP_CODE,
1279 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1280 DECODE(b.LOOKUP_CODE,NULL,
1281 c.ATTRIBUTE_STATUS||'006',c.ATTRIBUTE_STATUS)
1282 FROM FND_LOOKUPS b, OE_LINES_IFACE_ALL d
1283 WHERE d.TAX_EXEMPT_REASON = b.MEANING(+)
1284 AND b.LOOKUP_TYPE(+) = 'ZX_EXEMPTION_REASON_CODE'
1285 AND b.enabled_flag(+) = 'Y'
1286 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1287 AND NVL(END_DATE_ACTIVE, SYSDATE)
1288 AND d.rowid = c.rowid)
1289 WHERE (order_source_id, orig_sys_document_ref) IN
1290 (SELECT order_source_id, orig_sys_document_ref
1291 FROM OE_HEADERS_IFACE_ALL
1292 WHERE batch_id = p_batch_id)
1293 AND c.TAX_EXEMPT_REASON_CODE IS NULL
1294 AND c.TAX_EXEMPT_REASON IS NOT NULL;
1295
1296
1297
1298 -- AGREEMENT VARCHAR2(50)
1299
1300 UPDATE OE_LINES_IFACE_ALL c
1301 SET (AGREEMENT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1302 (SELECT b.AGREEMENT_ID,
1303 DECODE(b.AGREEMENT_ID,NULL,'Y',NULL),
1304 DECODE(b.AGREEMENT_ID,NULL,
1305 c.ATTRIBUTE_STATUS||'007',c.ATTRIBUTE_STATUS)
1306 FROM OE_AGREEMENTS_V b, OE_LINES_IFACE_ALL d
1307 WHERE d.AGREEMENT = b.NAME(+)
1308 AND sysdate between nvl(start_date_active, sysdate)
1309 and nvl(end_date_active, sysdate)
1310 AND d.rowid = c.rowid
1311 )
1312 WHERE (order_source_id, orig_sys_document_ref) IN
1313 ( SELECT order_source_id, orig_sys_document_ref
1314 FROM OE_HEADERS_IFACE_ALL
1315 WHERE batch_id = p_batch_id)
1316 AND c.AGREEMENT_ID IS NULL
1317 AND c.AGREEMENT IS NOT NULL;
1318
1319
1320
1321 -- INVOICING_RULE
1322
1323 UPDATE OE_LINES_IFACE_ALL c
1324 SET (INVOICING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1325 (SELECT b.RULE_ID,
1326 DECODE(b.RULE_ID,NULL,'Y',NULL),
1327 DECODE(b.RULE_ID,NULL,
1328 c.ATTRIBUTE_STATUS||'008',c.ATTRIBUTE_STATUS)
1329 FROM OE_RA_RULES_V b, OE_LINES_IFACE_ALL d
1330 WHERE d.INVOICING_RULE = b.NAME(+)
1331 AND b.STATUS(+) = 'A'
1332 AND b.TYPE(+) = 'I'
1333 AND d.rowid = c.rowid
1334 )
1335 WHERE (order_source_id, orig_sys_document_ref) IN
1336 ( SELECT order_source_id, orig_sys_document_ref
1337 FROM OE_HEADERS_IFACE_ALL
1338 WHERE batch_id = p_batch_id)
1339 AND c.INVOICING_RULE_ID IS NULL
1340 AND c.INVOICING_RULE IS NOT NULL;
1341
1342 -- ACCOUNTING_RULE VARCHAR2(30)
1343
1344 UPDATE OE_LINES_IFACE_ALL c
1345 SET (ACCOUNTING_RULE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1346 (SELECT b.RULE_ID,
1347 DECODE(b.RULE_ID,NULL,'Y',NULL),
1348 DECODE(b.RULE_ID,NULL,
1349 c.ATTRIBUTE_STATUS||'009',c.ATTRIBUTE_STATUS)
1350 FROM OE_RA_RULES_V b, OE_LINES_IFACE_ALL d
1351 WHERE d.ACCOUNTING_RULE = b.NAME(+)
1352 AND b.STATUS(+) = 'A'
1353 -- AND b.TYPE(+) = 'A' --we now allow variable accounting rules
1354 AND d.rowid = c.rowid
1355 )
1356 WHERE (order_source_id, orig_sys_document_ref) IN
1357 ( SELECT order_source_id, orig_sys_document_ref
1358 FROM OE_HEADERS_IFACE_ALL
1359 WHERE batch_id = p_batch_id)
1360 AND c.ACCOUNTING_RULE_ID IS NULL
1361 AND c.ACCOUNTING_RULE IS NOT NULL;
1362
1363 -- PAYMENT_TERM VARCHAR2(30)
1364
1365 UPDATE OE_LINES_IFACE_ALL c
1366 SET (PAYMENT_TERM_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1367 (SELECT b.TERM_ID,
1368 DECODE(b.TERM_ID,NULL,'Y',NULL),
1369 DECODE(b.TERM_ID,NULL,
1370 c.ATTRIBUTE_STATUS||'010',c.ATTRIBUTE_STATUS)
1371 FROM OE_RA_TERMS_V b, OE_LINES_IFACE_ALL d
1372 WHERE d.PAYMENT_TERM = b.NAME(+)
1373 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1374 AND NVL(END_DATE_ACTIVE, SYSDATE)
1375 AND d.rowid = c.rowid
1376 )
1377 WHERE (order_source_id, orig_sys_document_ref) IN
1378 ( SELECT order_source_id, orig_sys_document_ref
1379 FROM OE_HEADERS_IFACE_ALL
1380 WHERE batch_id = p_batch_id)
1381 AND c.PAYMENT_TERM_ID IS NULL
1382 AND c.PAYMENT_TERM IS NOT NULL;
1383
1384
1385 -- FREIGHT_TERMS VARCHAR2(30)
1386
1387 UPDATE OE_LINES_IFACE_ALL c
1388 SET (FREIGHT_TERMS_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1389 (SELECT b.LOOKUP_CODE,
1390 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1391 DECODE(b.LOOKUP_CODE,NULL,
1392 c.ATTRIBUTE_STATUS||'011',c.ATTRIBUTE_STATUS)
1393 FROM OE_LOOKUPS b, OE_LINES_IFACE_ALL d
1394 WHERE d.FREIGHT_TERMS = b.MEANING(+)
1395 AND b.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
1396 AND b.enabled_flag(+) = 'Y'
1397 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1398 AND NVL(END_DATE_ACTIVE, SYSDATE)
1399 AND d.rowid = c.rowid
1400 )
1401 WHERE (order_source_id, orig_sys_document_ref) IN
1402 ( SELECT order_source_id, orig_sys_document_ref
1403 FROM OE_HEADERS_IFACE_ALL
1404 WHERE batch_id = p_batch_id)
1405 AND c.FREIGHT_TERMS_CODE IS NULL
1406 AND c.FREIGHT_TERMS IS NOT NULL;
1407
1408
1409
1410 -- FOB_POINT VARCHAR2(30)
1411
1412 UPDATE OE_LINES_IFACE_ALL c
1413 SET (FOB_POINT_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1414 (SELECT b.LOOKUP_CODE,
1415 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1416 DECODE(b.LOOKUP_CODE,NULL,
1417 c.ATTRIBUTE_STATUS||'012',c.ATTRIBUTE_STATUS)
1418 FROM OE_AR_LOOKUPS_V b, OE_LINES_IFACE_ALL d
1419 WHERE d.FOB_POINT = b.MEANING(+)
1420 AND b.LOOKUP_TYPE(+) = 'FOB'
1421 AND b.enabled_flag(+) = 'Y'
1422 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1423 AND NVL(END_DATE_ACTIVE, SYSDATE)
1424 AND d.rowid = c.rowid
1425 )
1426 WHERE (order_source_id, orig_sys_document_ref) IN
1427 ( SELECT order_source_id, orig_sys_document_ref
1428 FROM OE_HEADERS_IFACE_ALL
1429 WHERE batch_id = p_batch_id)
1430 AND c.FOB_POINT_CODE IS NULL
1431 AND c.FOB_POINT IS NOT NULL;
1432
1433
1434 -- SOLD_TO_ORG -- Required as it is used later for invoice
1435 -- and ship to org
1436 UPDATE OE_LINES_IFACE_ALL c
1437 SET SOLD_TO_ORG_ID =
1438 (SELECT d.SOLD_TO_ORG_ID FROM OE_HEADERS_IFACE_ALL d, OE_LINES_IFACE_ALL e
1439 WHERE d.order_source_id = e.order_source_id
1440 AND d.orig_sys_document_ref = e.orig_sys_document_ref
1441 AND batch_id = p_batch_id
1442 AND d.sold_to_org_id IS NOT NULL
1443 AND c.rowid = e.rowid
1444 )
1445 WHERE (order_source_id, orig_sys_document_ref) IN
1446 ( SELECT order_source_id, orig_sys_document_ref
1447 FROM OE_HEADERS_IFACE_ALL
1448 WHERE batch_id = p_batch_id
1449 )
1450 AND c.SOLD_TO_ORG_ID IS NULL;
1451
1452
1453 -- DELIVER_TO_ORG No mapping columns in the interface table
1454
1455
1456 -- Ship To and Invoice To customer should be passed only if
1457 -- customer relationships is 'Y' or 'A'
1458 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS <> 'N' THEN
1459
1460 -- First, value to ID conversions for ship to customer and
1461 -- and invoice_to_customer
1462
1463 -- SHIP_TO_CUSTOMER
1464 --Enclosing the update in a block for bug 5620045
1465 BEGIN
1466 UPDATE OE_LINES_IFACE_ALL c
1467 SET (SHIP_TO_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1468 (SELECT b.CUST_ACCOUNT_ID,
1469 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
1470 DECODE(b.CUST_ACCOUNT_ID,NULL,
1471 d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
1472 FROM HZ_CUST_ACCOUNTS b, OE_LINES_IFACE_ALL d
1473 WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL
1474 AND d.SHIP_TO_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
1475 AND b.STATUS(+) = 'A'
1476 AND d.rowid = c.rowid
1477 UNION ALL
1478 SELECT b.CUST_ACCOUNT_ID,
1479 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
1480 DECODE(b.CUST_ACCOUNT_ID,NULL,
1481 d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
1482 FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_LINES_IFACE_ALL d
1483 WHERE d.SHIP_TO_CUSTOMER_NUMBER IS NULL
1484 AND d.SHIP_TO_CUSTOMER_NAME = e.PARTY_NAME(+)
1485 AND b.STATUS(+) = 'A'
1486 AND b.PARTY_ID(+) = e.PARTY_ID
1487 AND d.rowid = c.rowid
1488 )
1489 WHERE (order_source_id, orig_sys_document_ref) IN
1490 ( SELECT order_source_id, orig_sys_document_ref
1491 FROM OE_HEADERS_IFACE_ALL
1492 WHERE batch_id = p_batch_id)
1493 AND c.SHIP_TO_CUSTOMER_ID IS NULL
1494 AND (c.SHIP_TO_CUSTOMER_NAME IS NOT NULL OR
1495 c.SHIP_TO_CUSTOMER_NUMBER IS NOT NULL);
1496 EXCEPTION
1497 WHEN subqry_too_many_rows THEN
1498 UPDATE OE_LINES_IFACE_ALL c
1499 SET ERROR_FLAG = 'Y',
1500 ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'014'
1501 WHERE c.SHIP_TO_CUSTOMER_ID IS NULL
1502 AND c.SHIP_TO_CUSTOMER_NAME IS NOT NULL
1503 AND (order_source_id, orig_sys_document_ref) IN
1504 ( SELECT order_source_id, orig_sys_document_ref
1505 FROM OE_HEADERS_IFACE_ALL
1506 WHERE batch_id = p_batch_id) ;
1507
1508 END;
1509
1510 -- INVOICE_TO_CUSTOMER
1511 -- Catch subqry_too_many_rows exception bug 5620045
1512 BEGIN
1513 UPDATE OE_LINES_IFACE_ALL c
1514 SET (INVOICE_TO_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1515 (SELECT b.CUST_ACCOUNT_ID,
1516 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
1517 DECODE(b.CUST_ACCOUNT_ID,NULL,
1518 d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
1519 FROM HZ_CUST_ACCOUNTS b, OE_LINES_IFACE_ALL d
1520 WHERE d.INVOICE_TO_CUSTOMER_NUMBER IS NOT NULL
1521 AND d.INVOICE_TO_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
1522 AND b.STATUS(+) = 'A'
1523 AND d.rowid = c.rowid
1524 UNION ALL
1525 SELECT b.CUST_ACCOUNT_ID,
1526 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
1527 DECODE(b.CUST_ACCOUNT_ID,NULL,
1528 d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
1529 FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_LINES_IFACE_ALL d
1530 WHERE d.INVOICE_TO_CUSTOMER_NUMBER IS NULL
1531 AND d.INVOICE_TO_CUSTOMER_NAME = e.PARTY_NAME(+)
1532 AND b.STATUS(+) = 'A'
1533 AND b.PARTY_ID(+) = e.PARTY_ID
1534 AND d.rowid = c.rowid
1535 )
1536 WHERE (order_source_id, orig_sys_document_ref) IN
1537 ( SELECT order_source_id, orig_sys_document_ref
1538 FROM OE_HEADERS_IFACE_ALL
1539 WHERE batch_id = p_batch_id)
1540 AND c.INVOICE_TO_CUSTOMER_ID IS NULL
1541 AND (c.INVOICE_TO_CUSTOMER_NAME IS NOT NULL OR
1542 c.INVOICE_TO_CUSTOMER_NUMBER IS NOT NULL);
1543 EXCEPTION
1544 WHEN subqry_too_many_rows THEN
1545 UPDATE OE_LINES_IFACE_ALL c
1546 SET ERROR_FLAG = 'Y',
1547 ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'015'
1548 WHERE (order_source_id, orig_sys_document_ref) IN
1549 ( SELECT order_source_id, orig_sys_document_ref
1550 FROM OE_HEADERS_IFACE_ALL
1551 WHERE batch_id = p_batch_id)
1552 AND c.INVOICE_TO_CUSTOMER_ID IS NULL
1553 AND c.INVOICE_TO_CUSTOMER_NAME IS NOT NULL ;
1554 END;
1555
1556 END IF;
1557
1558 -- SHIP_TO_ORG
1559
1560 UPDATE OE_LINES_IFACE_ALL c
1561 SET (SHIP_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1562 (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
1563 DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
1564 DECODE(b.ORGANIZATION_ID,NULL,
1565 d.ATTRIBUTE_STATUS||'014',d.ATTRIBUTE_STATUS)
1566 FROM OE_SHIP_TO_ORGS_V b, OE_LINES_IFACE_ALL d
1567 WHERE d.ship_to_address1 = ADDRESS_LINE_1(+)
1568 AND nvl(d.ship_to_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
1569 AND nvl(d.ship_to_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
1570 --AND nvl(d.ship_to_address4,'NIL') = ADDRESS_LINE_4
1571 AND nvl(d.ship_to_city,'NIL') = nvl(town_or_city(+),'NIL')
1572 AND nvl(d.ship_to_state,'NIL') = nvl(state(+),'NIL')
1573 AND nvl(d.ship_to_postal_code,'NIL') = nvl(postal_code(+),'NIL')
1574 AND nvl(d.ship_to_country,'NIL') = nvl(country(+),'NIL')
1575 AND nvl(STATUS(+),'A') = 'A'
1576 AND b.CUSTOMER_ID(+) = nvl(d.SHIP_TO_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
1577 AND d.rowid = c.rowid
1578 )
1579 WHERE (order_source_id, orig_sys_document_ref) IN
1580 ( SELECT order_source_id, orig_sys_document_ref
1581 FROM OE_HEADERS_IFACE_ALL
1582 WHERE batch_id = p_batch_id)
1583 AND c.SHIP_TO_ORG_ID IS NULL
1584 AND (c.SHIP_TO_ADDRESS1 IS NOT NULL
1585 OR c.ship_to_address2 IS NOT NULL
1586 OR c.ship_to_address3 IS NOT NULL
1587 OR c.ship_to_address4 IS NOT NULL
1588 OR c.ship_to_org IS NOT NULL
1589 );
1590
1591 -- INVOICE_TO_ORG
1592
1593 UPDATE OE_LINES_IFACE_ALL c
1594 SET (INVOICE_TO_ORG_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1595 (SELECT /*+ PUSH_PRED(b) */ b.ORGANIZATION_ID,
1596 DECODE(b.ORGANIZATION_ID,NULL,'Y',NULL),
1597 DECODE(b.ORGANIZATION_ID,NULL,
1598 d.ATTRIBUTE_STATUS||'015',d.ATTRIBUTE_STATUS)
1599 FROM OE_INVOICE_TO_ORGS_V b, OE_LINES_IFACE_ALL d
1600 WHERE d.invoice_to_address1 = ADDRESS_LINE_1(+)
1601 AND nvl(d.invoice_to_address2,'NIL') = NVL(ADDRESS_LINE_2(+),'NIL')
1602 AND nvl(d.invoice_to_address3,'NIL') = nvl(ADDRESS_LINE_3(+),'NIL')
1603 --AND nvl(d.invoice_to_address4,'NIL') = ADDRESS_LINE_4
1604 AND nvl(d.invoice_to_city,'NIL') = nvl(town_or_city(+),'NIL')
1605 AND nvl(d.invoice_to_state,'NIL') = nvl(state(+),'NIL')
1606 AND nvl(d.invoice_to_postal_code,'NIL') = nvl(postal_code(+),'NIL')
1607 AND nvl(d.invoice_to_country,'NIL') = nvl(country(+),'NIL')
1608 AND nvl(STATUS(+),'A') = 'A'
1609 AND b.CUSTOMER_ID(+) = nvl(d.INVOICE_TO_CUSTOMER_ID,d.SOLD_TO_ORG_ID)
1610 AND d.rowid = c.rowid
1611 )
1612 WHERE (order_source_id, orig_sys_document_ref) IN
1613 ( SELECT order_source_id, orig_sys_document_ref
1614 FROM OE_HEADERS_IFACE_ALL
1615 WHERE batch_id = p_batch_id)
1616 AND c.INVOICE_TO_ORG_ID IS NULL
1617 AND (c.invoice_to_address1 IS NOT NULL
1618 OR c.invoice_to_address2 IS NOT NULL
1619 OR c.invoice_to_address3 IS NOT NULL
1620 OR c.invoice_to_address4 IS NOT NULL
1621 OR c.invoice_to_org IS NOT NULL
1622 );
1623
1624
1625 -- SHIP_TO_CONTACT VARCHAR2(30)
1626
1627 UPDATE OE_LINES_IFACE_ALL c
1628 SET (SHIP_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1629 (SELECT Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),
1630 DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,'Y',NULL),
1631 DECODE(Get_Contact_ID(c.SHIP_TO_CONTACT,c.SHIP_TO_ORG_ID),NULL,
1632 c.ATTRIBUTE_STATUS||'017',c.ATTRIBUTE_STATUS)
1633 FROM DUAL
1634 )
1635 WHERE (order_source_id, orig_sys_document_ref) IN
1636 ( SELECT order_source_id, orig_sys_document_ref
1637 FROM OE_HEADERS_IFACE_ALL
1638 WHERE batch_id = p_batch_id)
1639 AND c.SHIP_TO_CONTACT_ID IS NULL
1640 AND c.SHIP_TO_CONTACT IS NOT NULL;
1641
1642
1643 -- INVOICE_TO_CONTACT VARCHAR2(30)
1644
1645 UPDATE OE_LINES_IFACE_ALL c
1646 SET (INVOICE_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1647 (SELECT Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),
1648 DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,'Y',NULL),
1649 DECODE(Get_Contact_ID(c.INVOICE_TO_CONTACT,c.INVOICE_TO_ORG_ID),NULL,
1650 c.ATTRIBUTE_STATUS||'018',c.ATTRIBUTE_STATUS)
1651 FROM DUAL
1652 )
1653 WHERE (order_source_id, orig_sys_document_ref) IN
1654 ( SELECT order_source_id, orig_sys_document_ref
1655 FROM OE_HEADERS_IFACE_ALL
1656 WHERE batch_id = p_batch_id)
1657 AND c.INVOICE_TO_CONTACT_ID IS NULL
1658 AND c.INVOICE_TO_CONTACT IS NOT NULL;
1659
1660
1661 -- DELIVER_TO_CONTACT VARCHAR2(30)
1662
1663 UPDATE OE_LINES_IFACE_ALL c
1664 SET (DELIVER_TO_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1665 (SELECT Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),
1666 DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,'Y',NULL),
1667 DECODE(Get_Contact_ID(c.DELIVER_TO_CONTACT,c.DELIVER_TO_ORG_ID),NULL,
1668 c.ATTRIBUTE_STATUS||'019',c.ATTRIBUTE_STATUS)
1669 FROM DUAL
1670 )
1671 WHERE (order_source_id, orig_sys_document_ref) IN
1672 ( SELECT order_source_id, orig_sys_document_ref
1673 FROM OE_HEADERS_IFACE_ALL
1674 WHERE batch_id = p_batch_id)
1675 AND c.DELIVER_TO_CONTACT_ID IS NULL
1676 AND c.DELIVER_TO_CONTACT IS NOT NULL;
1677
1678 -- COMMITMENT - Not supported for BULK!
1679
1680
1681 -- SHIPMENT_PRIORITY_CODE
1682
1683 UPDATE OE_LINES_IFACE_ALL c
1684 SET (SHIPMENT_PRIORITY_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1685 (SELECT b.LOOKUP_CODE,
1686 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1687 DECODE(b.LOOKUP_CODE,NULL,
1688 c.ATTRIBUTE_STATUS||'023',c.ATTRIBUTE_STATUS)
1689 FROM OE_LOOKUPS b, OE_LINES_IFACE_ALL d
1690 WHERE d.SHIPMENT_PRIORITY = b.MEANING(+)
1691 AND b.LOOKUP_TYPE(+) = 'SHIPMENT_PRIORITY'
1692 AND b.enabled_flag(+) = 'Y'
1693 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1694 AND NVL(END_DATE_ACTIVE, SYSDATE)
1695 AND d.rowid = c.rowid
1696 )
1697 WHERE (order_source_id, orig_sys_document_ref) IN
1698 ( SELECT order_source_id, orig_sys_document_ref
1699 FROM OE_HEADERS_IFACE_ALL
1700 WHERE batch_id = p_batch_id)
1701 AND c.SHIPMENT_PRIORITY_CODE IS NULL
1702 AND c.SHIPMENT_PRIORITY IS NOT NULL;
1703
1704
1705 -- DEMAND_BUCKET_TYPE: No value to ID conversion in OEXSVIDB.pls
1706
1707 -- SHIPPING_METHOD_CODE VARCHAR2(30)
1708 UPDATE OE_LINES_IFACE_ALL c
1709 SET (SHIPPING_METHOD_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1710 (SELECT b.LOOKUP_CODE,
1711 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1712 DECODE(b.LOOKUP_CODE,NULL,
1713 c.ATTRIBUTE_STATUS||'024',c.ATTRIBUTE_STATUS)
1714 FROM OE_SHIP_METHODS_V b, OE_LINES_IFACE_ALL d
1715 WHERE d.SHIPPING_METHOD = b.MEANING(+)
1716 AND b.enabled_flag(+) = 'Y'
1717 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1718 AND NVL(END_DATE_ACTIVE, SYSDATE)
1719 AND d.rowid = c.rowid
1720 )
1721 WHERE (order_source_id, orig_sys_document_ref) IN
1722 ( SELECT order_source_id, orig_sys_document_ref
1723 FROM OE_HEADERS_IFACE_ALL
1724 WHERE batch_id = p_batch_id)
1725 AND c.SHIPPING_METHOD_CODE IS NULL
1726 AND c.SHIPPING_METHOD IS NOT NULL;
1727
1728 -- FREIGHT_CARRIER_CODE: value column does not exist on interface tables
1729
1730 -- INTERMED_SHIP_TO_CONTACT_ID: no columns on interface tables
1731
1732 -- INTERMED_SHIP_TO_ORG_ID: no columns on interface tables
1733
1734 -- INVENTORY_ITEM: Value to ID conversion in OEBLLINB, Get_Item_Info
1735
1736 -- ITEM_TYPE_CODE: Not needed - this is an INTERNAL field!
1737
1738 -- OVER_SHIP_REASON: Not applicable to order creation.
1739
1740 -- RETURN_REASON: Not supported for BULK!
1741
1742 -- COMMITMENT_ID: Not supported for BULK!
1743
1744 -- RLA_SCHEDULE_TYPE, VEH_CUS_ITEM_CUM_KEY: No Value to ID conversion in OEXSVIDB.pls!
1745
1746 -- SHIP_FROM_ORG_ID: No Value to ID conversion in OEXSVIDB.pls!
1747
1748 -- PROJECT_ID, TASK_ID: No Value to ID conversion in OEXSVIDB.pls!
1749
1750 -- TAX_EXEMPT_FLAG: Value column does not exist on interface tables
1751
1752 -- TAX_POINT_CODE: Unused column
1753
1754 --{Bug 5054618
1755 --End customer changes for HVOP
1756 -- bug 5620045
1757 -- Catch subqry_too_many_rows exception and add additional where clause
1758 -- to update only the records in the current batch
1759
1760 BEGIN
1761 UPDATE OE_LINES_IFACE_ALL c
1762 SET (END_CUSTOMER_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1763 (SELECT b.CUST_ACCOUNT_ID,
1764 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
1765 DECODE(b.CUST_ACCOUNT_ID,NULL,
1766 d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS)
1767 FROM HZ_CUST_ACCOUNTS b, OE_LINES_IFACE_ALL d
1768 WHERE d.END_CUSTOMER_NUMBER IS NOT NULL
1769 AND d.END_CUSTOMER_NUMBER = b.ACCOUNT_NUMBER(+)
1770 AND b.STATUS(+) = 'A' AND d.rowid = c.rowid
1771 UNION ALL
1772 SELECT b.CUST_ACCOUNT_ID,
1773 DECODE(b.CUST_ACCOUNT_ID,NULL,'Y',NULL),
1774 DECODE(b.CUST_ACCOUNT_ID,NULL,
1775 d.ATTRIBUTE_STATUS||'025',d.ATTRIBUTE_STATUS)
1776 FROM HZ_CUST_ACCOUNTS b, HZ_PARTIES e, OE_LINES_IFACE_ALL d
1777 WHERE d.END_CUSTOMER_NUMBER IS NULL
1778 AND d.END_CUSTOMER_NAME = e.PARTY_NAME(+)
1779 AND b.STATUS(+) = 'A' AND b.PARTY_ID(+) = e.PARTY_ID
1780 AND d.rowid = c.rowid)
1781 WHERE (order_source_id, orig_sys_document_ref) IN
1782 ( SELECT order_source_id, orig_sys_document_ref
1783 FROM OE_HEADERS_IFACE_ALL
1784 WHERE batch_id = p_batch_id)
1785 AND c.END_CUSTOMER_ID IS NULL
1786 AND (c.END_CUSTOMER_NAME IS NOT NULL
1787 OR c.END_CUSTOMER_NUMBER IS NOT NULL);
1788 EXCEPTION
1789 WHEN subqry_too_many_rows THEN
1790 UPDATE OE_LINES_IFACE_ALL c
1791 SET ERROR_FLAG = 'Y',
1792 ATTRIBUTE_STATUS = ATTRIBUTE_STATUS||'025'
1793 WHERE (order_source_id, orig_sys_document_ref) IN
1794 ( SELECT order_source_id, orig_sys_document_ref
1795 FROM OE_HEADERS_IFACE_ALL
1796 WHERE batch_id = p_batch_id)
1797 AND c.END_CUSTOMER_ID IS NULL
1798 AND c.END_CUSTOMER_NAME IS NOT NULL ;
1799
1800 END;
1801
1802
1803 --bug 5620045 added additional where clause to update records in current
1804 --batch
1805 UPDATE OE_LINES_IFACE_ALL c
1806 SET (END_CUSTOMER_CONTACT_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1807 (SELECT
1808 Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,c.END_CUSTOMER_ID),
1809 DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,
1810 c.END_CUSTOMER_ID),NULL,'Y',NULL),
1811 DECODE(Get_End_customer_Contact_ID(c.END_CUSTOMER_CONTACT,
1812 c.END_CUSTOMER_ID),NULL,
1813 c.ATTRIBUTE_STATUS||'026',c.ATTRIBUTE_STATUS)
1814 FROM DUAL)
1815 WHERE (order_source_id, orig_sys_document_ref) IN
1816 ( SELECT order_source_id, orig_sys_document_ref
1817 FROM OE_HEADERS_IFACE_ALL
1818 WHERE batch_id = p_batch_id)
1819 AND c.END_CUSTOMER_CONTACT_ID IS NULL
1820 AND c.END_CUSTOMER_CONTACT IS NOT NULL;
1821
1822
1823
1824 UPDATE OE_LINES_IFACE_ALL c
1825 SET (END_CUSTOMER_SITE_USE_ID,ERROR_FLAG,ATTRIBUTE_STATUS)=
1826 (SELECT END_CUSTOMER_SITE(c.end_customer_address1,
1827 c.end_customer_address2,c.end_customer_address3,
1828 c.end_customer_address4,c.end_customer_location, NULL,
1829 c.end_customer_id,c.end_customer_city, c.end_customer_state,
1830 c.end_customer_postal_code,c.end_customer_country,NULL),
1831 DECODE(END_CUSTOMER_SITE(c.end_customer_address1,
1832 c.end_customer_address2,c.end_customer_address3,
1833 c.end_customer_address4,c.end_customer_location,NULL,
1834 c.end_customer_id,c.end_customer_city,
1835 c.end_customer_state, c.end_customer_postal_code,
1836 c.end_customer_country, NULL),NULL,'Y',NULL),
1837 DECODE(END_CUSTOMER_SITE(c.end_customer_address1,
1838 c.end_customer_address2,
1839 c.end_customer_address3,
1840 c.end_customer_address4,
1841 c.end_customer_location,
1842 NULL,
1843 c.end_customer_id,
1844 c.end_customer_city,
1845 c.end_customer_state,
1846 c.end_customer_postal_code,
1847 c.end_customer_country,
1848 NULL),
1849 NULL,c.ATTRIBUTE_STATUS||'027',c.ATTRIBUTE_STATUS)
1850 FROM DUAL)
1851 WHERE (order_source_id, orig_sys_document_ref) IN
1852 ( SELECT order_source_id, orig_sys_document_ref
1853 FROM OE_HEADERS_IFACE_ALL
1854 WHERE batch_id = p_batch_id)
1855 AND c.END_CUSTOMER_SITE_USE_ID IS NULL AND c.END_CUSTOMER_ID IS NOT NULL
1856 AND (c.END_CUSTOMER_ADDRESS1 IS NOT NULL
1857 OR c.END_CUSTOMER_ADDRESS2 IS NOT NULL
1858 OR c.END_CUSTOMER_ADDRESS3 IS NOT NULL
1859 OR c.END_CUSTOMER_ADDRESS4 IS NOT NULL);
1860
1861
1862
1863 -- Bug 5054618}
1864 UPDATE OE_LINES_IFACE_ALL c
1865 SET (IB_OWNER_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1866 (SELECT b.LOOKUP_CODE,
1867 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1868 DECODE(b.LOOKUP_CODE,NULL,
1869 c.ATTRIBUTE_STATUS||'028',c.ATTRIBUTE_STATUS)
1870 FROM -- OE_LOOKUPS b,
1871 ( select lookup_code, meaning
1872 from OE_LOOKUPS
1873 where lookup_type In ('ITEM_OWNER', 'ONT_INSTALL_BASE')
1874 and enabled_flag = 'Y'
1875 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1876 AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1877 OE_LINES_IFACE_ALL d
1878 WHERE d.IB_OWNER = b.MEANING(+)
1879 --AND ( b.LOOKUP_TYPE(+) = 'ITEM_OWNER'
1880 --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1881 --AND b.enabled_flag(+) = 'Y'
1882 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1883 -- AND NVL(END_DATE_ACTIVE, SYSDATE)
1884 AND d.rowid = c.rowid
1885 AND rownum = 1
1886 )
1887 WHERE (order_source_id, orig_sys_document_ref) IN
1888 ( SELECT order_source_id, orig_sys_document_ref
1889 FROM OE_HEADERS_IFACE_ALL
1890 WHERE batch_id = p_batch_id)
1891 AND c.IB_OWNER_CODE IS NULL
1892 AND c.IB_OWNER IS NOT NULL;
1893
1894
1895 UPDATE OE_LINES_IFACE_ALL c
1896 SET (IB_INSTALLED_AT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1897 (SELECT b.LOOKUP_CODE,
1898 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1899 DECODE(b.LOOKUP_CODE,NULL,
1900 c.ATTRIBUTE_STATUS||'029',c.ATTRIBUTE_STATUS)
1901 FROM -- OE_LOOKUPS b,
1902 ( select lookup_code, meaning
1903 from OE_LOOKUPS
1904 where lookup_type In ('ITEM_INSTALL_LOCATION', 'ONT_INSTALL_BASE')
1905 and enabled_flag = 'Y'
1906 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1907 AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1908 OE_LINES_IFACE_ALL d
1909 WHERE d.IB_INSTALLED_AT_LOCATION = b.MEANING(+)
1910 --AND ( b.LOOKUP_TYPE(+) = 'ITEM_INSTALL_LOCATION'
1911 --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1912 --AND b.enabled_flag(+) = 'Y'
1913 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1914 -- AND NVL(END_DATE_ACTIVE, SYSDATE)
1915 AND d.rowid = c.rowid
1916 AND rownum = 1
1917 )
1918 WHERE (order_source_id, orig_sys_document_ref) IN
1919 ( SELECT order_source_id, orig_sys_document_ref
1920 FROM OE_HEADERS_IFACE_ALL
1921 WHERE batch_id = p_batch_id)
1922 AND c.IB_INSTALLED_AT_LOCATION_CODE IS NULL
1923 AND c.IB_INSTALLED_AT_LOCATION IS NOT NULL;
1924
1925 UPDATE OE_LINES_IFACE_ALL c
1926 SET (IB_CURRENT_LOCATION_CODE,ERROR_FLAG,ATTRIBUTE_STATUS)=
1927 (SELECT b.LOOKUP_CODE,
1928 DECODE(b.LOOKUP_CODE,NULL,'Y',NULL),
1929 DECODE(b.LOOKUP_CODE,NULL,
1930 c.ATTRIBUTE_STATUS||'030',c.ATTRIBUTE_STATUS)
1931 FROM -- OE_LOOKUPS b,
1932 ( select lookup_code, meaning
1933 from OE_LOOKUPS
1934 where lookup_type In ('ITEM_CURRENT_LOCATION', 'ONT_INSTALL_BASE')
1935 and enabled_flag = 'Y'
1936 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1937 AND NVL(END_DATE_ACTIVE, SYSDATE) ) b,
1938 OE_LINES_IFACE_ALL d
1939 WHERE d.IB_CURRENT_LOCATION = b.MEANING(+)
1940 --AND ( b.LOOKUP_TYPE(+) = 'ITEM_CURRENT_LOCATION'
1941 --OR b.LOOKUP_TYPE(+) = 'ONT_INSTALL_BASE' )
1942 --AND b.enabled_flag(+) = 'Y'
1943 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1944 -- AND NVL(END_DATE_ACTIVE, SYSDATE)
1945 AND d.rowid = c.rowid
1946 AND rownum = 1
1947 )
1948 WHERE (order_source_id, orig_sys_document_ref) IN
1949 ( SELECT order_source_id, orig_sys_document_ref
1950 FROM OE_HEADERS_IFACE_ALL
1951 WHERE batch_id = p_batch_id)
1952 AND c.IB_CURRENT_LOCATION_CODE IS NULL
1953 AND c.IB_CURRENT_LOCATION IS NOT NULL;
1954
1955
1956 EXCEPTION
1957 WHEN OTHERS THEN
1958 IF l_debug_level > 0 THEN
1959 oe_debug_pub.add( 'OTHERS ERROR , OE_BULK_VALUE_TO_ID.LINES' ) ;
1960 END IF;
1961 IF l_debug_level > 0 THEN
1962 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1963 END IF;
1964 OE_BULK_MSG_PUB.Add_Exc_Msg
1965 ( G_PKG_NAME
1966 , 'Lines'
1967 );
1968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1969 END Lines;
1970
1971
1972 ---------------------------------------------------------------------
1973 -- PROCEDURE Adjustments
1974 --
1975 -- Value to ID conversions on adjustments interface table.
1976 -- This procedure also does pre-processing/entity validation for
1977 -- adjustments.
1978 ---------------------------------------------------------------------
1979
1980 PROCEDURE Adjustments(p_batch_id IN NUMBER)
1981 IS
1982 l_msg_text VARCHAR2(2000);
1983 --
1984 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1985 --
1986 BEGIN
1987
1988 -- Value to ID conversions for adjustments
1989 -- List Header ID, List Line ID
1990
1991 UPDATE OE_PRICE_ADJS_INTERFACE a
1992 SET (LIST_HEADER_ID, ERROR_FLAG) =
1993 (SELECT b.list_header_id
1994 , decode(b.list_header_id,NULL,'Y',NULL)
1995 FROM QP_LIST_HEADERS_TL b
1996 WHERE b.NAME = a.list_name
1997 AND b.LANGUAGE = userenv('LANG')
1998 AND nvl(b.VERSION_NO,'x') = nvl(a.version_number,'x')
1999 )
2000 WHERE (order_source_id, orig_sys_document_ref) IN
2001 ( SELECT order_source_id, orig_sys_document_ref
2002 FROM OE_HEADERS_IFACE_ALL
2003 WHERE batch_id = p_batch_id)
2004 AND a.LIST_HEADER_ID IS NULL
2005 AND a.LIST_NAME IS NOT NULL;
2006
2007 UPDATE OE_PRICE_ADJS_INTERFACE a
2008 SET (LIST_LINE_ID, LIST_LINE_TYPE_CODE, ERROR_FLAG) =
2009 (SELECT b.list_line_id
2010 , b.list_line_type_code
2011 , decode(b.list_line_id,NULL,'Y',NULL)
2012 FROM QP_LIST_LINES b
2013 WHERE b.LIST_HEADER_ID = a.LIST_HEADER_ID
2014 AND b.LIST_LINE_NO = a.LIST_LINE_NUMBER)
2015 WHERE (order_source_id, orig_sys_document_ref) IN
2016 ( SELECT order_source_id, orig_sys_document_ref
2017 FROM OE_HEADERS_IFACE_ALL
2018 WHERE batch_id = p_batch_id)
2019 AND a.LIST_LINE_ID IS NULL
2020 AND a.LIST_HEADER_ID IS NOT NULL
2021 AND a.LIST_LINE_NUMBER IS NOT NULL;
2022
2023
2024 -- Entity Level Validations for Adjustments
2025
2026 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_BULK_NOT_SUPP_ADJ_ATTRIBS');
2027
2028 INSERT INTO OE_PROCESSING_MSGS
2029 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2030 ,line_id ,order_source_id ,original_sys_document_ref
2031 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2032 ,source_document_type_id ,source_document_id ,source_document_line_id
2033 ,attribute_code ,creation_date ,created_by ,last_update_date
2034 ,last_updated_by ,last_update_login ,program_application_id ,program_id
2035 ,program_update_date ,process_activity ,notification_flag ,type
2036 ,message_source_code ,language ,message_text, transaction_id
2037 )
2038 SELECT
2039 a.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
2040 ,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
2041 ,a.change_sequence ,NULL ,NULL ,NULL ,'LIST_LINE_TYPE_CODE'
2042 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2043 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
2044 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2045 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
2046 WHERE h.batch_id = p_batch_id
2047 AND a.order_source_id = h.order_source_id
2048 AND a.orig_sys_document_ref = h.orig_sys_document_ref
2049 AND (a.list_line_type_code NOT IN ('DIS','FREIGHT_CHARGE','SUR','PBH')
2050 OR (a.list_header_id IS NULL OR a.list_line_id IS NULL));
2051
2052 IF OE_Bulk_Validate.g_error_count = 0 THEN
2053 IF SQL%ROWCOUNT > 0 THEN
2054 OE_Bulk_Validate.g_error_count := 1;
2055 IF l_debug_level > 0 THEN
2056 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2057 END IF;
2058 END IF;
2059 END IF;
2060
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 IF l_debug_level > 0 THEN
2064 oe_debug_pub.add( 'OTHERS ERROR , OE_BULK_VALUE_TO_ID.ADJUSTMENTS' ) ;
2065 END IF;
2066 IF l_debug_level > 0 THEN
2067 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
2068 END IF;
2069 OE_BULK_MSG_PUB.Add_Exc_Msg
2070 ( G_PKG_NAME
2071 , 'Adjustments'
2072 );
2073 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2074 END Adjustments;
2075
2076
2077 ---------------------------------------------------------------------
2078 -- PROCEDURE Insert_Error_Messages
2079 --
2080 -- Following procedure will check the attribute_status from the interface table
2081 -- and will create records in OE_PROCESSING_MSGS for errored records.The
2082 -- attribute for which the value_to_id conversion failed will be marked with a
2083 -- specific number in attribute_status column for every record in the interface
2084 -- tables.
2085 ---------------------------------------------------------------------
2086
2087 PROCEDURE INSERT_ERROR_MESSAGES(p_batch_id NUMBER)
2088 IS
2089
2090 CURSOR C_ERR IS
2091 SELECT request_id ,
2092 order_source_id ,
2093 orig_sys_document_ref ,
2094 orig_sys_line_ref,
2095 orig_sys_shipment_ref ,
2096 change_sequence,
2097 attribute_status
2098 FROM OE_LINES_IFACE_ALL
2099 WHERE (order_source_id, orig_sys_document_ref) IN
2100 ( SELECT order_source_id, orig_sys_document_ref
2101 FROM OE_HEADERS_IFACE_ALL
2102 WHERE batch_id = p_batch_id)
2103 AND attribute_status IS NOT NULL
2104 UNION
2105 SELECT request_id ,
2106 order_source_id ,
2107 orig_sys_document_ref ,
2108 NULL,
2109 NULL ,
2110 change_sequence,
2111 attribute_status
2112 FROM OE_HEADERS_IFACE_ALL
2113 WHERE batch_id = p_batch_id
2114 AND attribute_status IS NOT NULL;
2115
2116 l_counter NUMBER :=0;
2117 l_first NUMBER :=0;
2118 l_attribute VARCHAR2(30);
2119 l_attribute_name VARCHAR2(240);
2120 l_substr VARCHAR2(3);
2121 l_msg_text VARCHAR2(2000);
2122 l_msg_data VARCHAR2(2000);
2123 --
2124 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2125 --
2126 BEGIN
2127
2128 l_msg_data := FND_MESSAGE.GET_STRING('ONT','OE_BULK_VALUE_TO_ID_ERROR');
2129
2130 FOR l_err in C_ERR LOOP
2131
2132 IF l_debug_level > 0 THEN
2133 oe_debug_pub.add( 'ATTR STATUS :'||L_ERR.ATTRIBUTE_STATUS ) ;
2134 END IF;
2135 l_counter := LENGTH(l_err.attribute_status)/3;
2136 IF l_counter < 1 THEN
2137 GOTO END_OF_LOOP;
2138 END IF;
2139
2140 l_first := 1;
2141 FOR i IN 1..l_counter LOOP
2142
2143 l_substr := SUBSTR(l_err.attribute_status,l_first,3);
2144
2145 IF l_substr = '001' THEN
2146 l_attribute := 'ORDER_SOURCE_ID';
2147 ELSIF l_substr = '002' THEN
2148 l_attribute := 'ORDER_TYPE_ID';
2149 ELSIF l_substr = '003' THEN
2150 l_attribute := 'PRICE_LIST_ID';
2151 ELSIF l_substr = '004' THEN
2152 l_attribute := 'CONVERSION_TYPE_CODE';
2153 ELSIF l_substr = '005' THEN
2154 l_attribute := 'SALESREP_ID';
2155 ELSIF l_substr = '006' THEN
2156 l_attribute := 'TAX_EXEMPT_REASON_CODE';
2157 ELSIF l_substr = '007' THEN
2158 l_attribute := 'AGREEMENT_ID';
2159 ELSIF l_substr = '008' THEN
2160 l_attribute := 'INVOICING_RULE_ID';
2161 ELSIF l_substr = '009' THEN
2162 l_attribute := 'ACCOUNTING_RULE_ID';
2163 ELSIF l_substr = '010' THEN
2164 l_attribute := 'PAYMENT_TERM_ID';
2165 ELSIF l_substr = '011' THEN
2166 l_attribute := 'FREIGHT_TERMS_CODE';
2167 ELSIF l_substr = '012' THEN
2168 l_attribute := 'FOB_POINT_CODE';
2169 ELSIF l_substr = '013' THEN
2170 l_attribute := 'SOLD_TO_ORG_ID';
2171 ELSIF l_substr = '014' THEN
2172 l_attribute := 'SHIP_TO_ORG_ID';
2173 ELSIF l_substr = '015' THEN
2174 l_attribute := 'INVOICE_TO_ORG_ID';
2175 ELSIF l_substr = '016' THEN
2176 l_attribute := 'SOLD_TO_CONTACT_ID';
2177 ELSIF l_substr = '017' THEN
2178 l_attribute := 'SHIP_TO_CONTACT_ID';
2179 ELSIF l_substr = '018' THEN
2180 l_attribute := 'INVOICE_TO_CONTACT_ID';
2181 ELSIF l_substr = '019' THEN
2182 l_attribute := 'DELIVER_TO_CONTACT_ID';
2183 ELSIF l_substr = '020' THEN
2184 l_attribute := 'LINE_TYPE_ID';
2185 ELSIF l_substr = '021' THEN
2186 l_attribute := 'COMMITMENT_ID';
2187 ELSIF l_substr = '022' THEN
2188 l_attribute := 'SALES_CHANNEL_CODE';
2189 ELSIF l_substr = '023' THEN
2190 l_attribute := 'SHIPMENT_PRIORITY_CODE';
2191 ELSIF l_substr = '024' THEN
2192 l_attribute := 'SHIPPING_METHOD_CODE';
2193 ELSIF l_substr = '025' THEN -- end customer changes(Bug 5054618)
2194 l_attribute := 'END_CUSTOMER_ID';
2195 ELSIF l_substr = '026' THEN
2196 l_attribute := 'END_CUSTOMER_CONTACT_ID';
2197 ELSIF l_substr = '028' THEN
2198 l_attribute := 'END_CUSTOMER_SITE_USE_ID';
2199 END IF;
2200
2201 l_attribute_name := OE_ORDER_UTIL.Get_Attribute_Name(l_attribute);
2202
2203 l_msg_text := l_msg_data|| ' '||l_attribute_name;
2204
2205 INSERT INTO OE_PROCESSING_MSGS
2206 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2207 ,line_id ,order_source_id ,original_sys_document_ref
2208 ,original_sys_document_line_ref ,orig_sys_shipment_ref
2209 ,change_sequence ,source_document_type_id ,source_document_id
2210 ,source_document_line_id ,attribute_code ,creation_date
2211 ,created_by ,last_update_date ,last_updated_by ,last_update_login
2212 ,program_application_id ,program_id ,program_update_date
2213 ,process_activity ,notification_flag ,type ,message_source_code
2214 ,language ,message_text, transaction_id
2215 )
2216 VALUES
2217 ( l_err.request_id,DECODE(l_err.ORIG_SYS_LINE_REF,NULL,'HEADER','LINE')
2218 , NULL ,NULL ,NULL ,NULL ,l_err.order_source_id
2219 , l_err.orig_sys_document_ref , l_err.ORIG_SYS_LINE_REF
2220 , l_err.orig_sys_shipment_ref , l_err.change_sequence ,NULL ,NULL
2221 , NULL ,l_attribute, sysdate ,FND_GLOBAL.USER_ID ,sysdate
2222 , FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL
2223 , NULL ,NULL ,NULL ,'C' ,USERENV('LANG')
2224 , l_msg_text, OE_MSG_ID_S.NEXTVAL
2225 );
2226
2227 l_first := l_first + 3;
2228
2229 OE_BULK_VALIDATE.G_ERROR_COUNT := 1;
2230
2231 END LOOP;
2232 <<END_OF_LOOP>>
2233 NULL;
2234 END LOOP;
2235
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 IF l_debug_level > 0 THEN
2239 oe_debug_pub.add( 'OTHERS ERROR , OE_BULK_VALUE_TO_ID.INSERT_ERROR_MESSAGES' ) ;
2240 END IF;
2241 IF l_debug_level > 0 THEN
2242 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
2243 END IF;
2244 OE_BULK_MSG_PUB.Add_Exc_Msg
2245 ( G_PKG_NAME
2246 , 'INSERT_ERROR_MESSAGES'
2247 );
2248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2249 END INSERT_ERROR_MESSAGES;
2250
2251 END OE_Bulk_Value_To_Id;
[Home] [Help]