[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_VALIDATE
Source
1 PACKAGE BODY OE_BULK_VALIDATE AS
2 /* $Header: OEBSVATB.pls 120.9.12010000.2 2008/11/18 20:30:09 smusanna ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_VALIDATE';
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE Pre_Process
9 --
10 -- This API does all the order import pre-processing validations on
11 -- the interface tables for orders in this batch.
12 -- It will insert error messages for all validation failures.
13 ---------------------------------------------------------------------
14
15 PROCEDURE Pre_Process(p_batch_id IN NUMBER)
16 AS
17 l_msg_text VARCHAR2(2000);
18 --
19 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
20 --
21 BEGIN
22
23 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_BULK_REQD_HDR_ATTRIBUTES');
24 INSERT INTO OE_PROCESSING_MSGS
25 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
26 ,order_source_id ,original_sys_document_ref
27 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
28 ,attribute_code ,creation_date ,created_by ,last_update_date
29 ,last_updated_by ,last_update_login
30 ,program_application_id ,program_id,program_update_date
31 ,process_activity ,notification_flag ,type
32 ,message_source_code ,language
33 ,message_text, transaction_id
34 )
35 SELECT
36 request_id ,'HEADER' ,NULL ,NULL ,NULL ,NULL
37 ,order_source_id ,orig_sys_document_ref
38 ,NULL, NULL ,change_sequence
39 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
40 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
41 ,660 ,NULL ,NULL
42 ,NULL ,NULL ,NULL
43 ,'C' ,USERENV('LANG')
44 ,l_msg_text, OE_MSG_ID_S.NEXTVAL
45 FROM OE_HEADERS_IFACE_ALL
46 WHERE batch_id = p_batch_id
47 AND (order_source_id IS NULL OR orig_sys_document_ref IS NULL);
48
49 IF g_error_count = 0 THEN
50 IF SQL%ROWCOUNT > 0 THEN
51 g_error_count := 1;
52 IF l_debug_level > 0 THEN
53 oe_debug_pub.add( 'THE ERROR COUNT IS SET ???' ) ;
54 END IF;
55 END IF;
56 END IF;
57
58 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_OI_OPERATION_CODE');
59 INSERT INTO OE_PROCESSING_MSGS
60 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
61 ,order_source_id ,original_sys_document_ref
62 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
63 ,attribute_code ,creation_date ,created_by ,last_update_date
64 ,last_updated_by ,last_update_login
65 ,program_application_id ,program_id,program_update_date
66 ,process_activity ,notification_flag ,type
67 ,message_source_code ,language
68 ,message_text, transaction_id
69 )
70 SELECT
71 request_id ,'HEADER' ,NULL ,NULL ,NULL ,NULL
72 ,order_source_id ,orig_sys_document_ref
73 ,NULL, NULL ,change_sequence
74 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
75 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
76 ,660 ,NULL ,NULL
77 ,NULL ,NULL ,NULL
78 ,'C' ,USERENV('LANG')
79 ,l_msg_text, OE_MSG_ID_S.NEXTVAL
80 FROM OE_HEADERS_IFACE_ALL h
81 WHERE batch_id = p_batch_id
82 AND EXISTS (SELECT 'Y'
83 FROM OE_ORDER_HEADERS oh
84 WHERE oh.order_source_id = h.order_source_id
85 AND oh.orig_sys_document_ref = h.orig_sys_document_ref
86 );
87
88 IF g_error_count = 0 THEN
89 IF SQL%ROWCOUNT > 0 THEN
90 g_error_count := 1;
91 IF l_debug_level > 0 THEN
92 oe_debug_pub.add( 'THE ERROR COUNT IS SET now' ) ;
93 END IF;
94 END IF;
95 END IF;
96
97 -- comment out with config support change
98 /* l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_BULK_NOT_SUPP_HDR_ATTRIBS');
99 INSERT INTO OE_PROCESSING_MSGS
100 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
101 ,order_source_id ,original_sys_document_ref
102 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
103 ,attribute_code ,creation_date ,created_by ,last_update_date
104 ,last_updated_by ,last_update_login
105 ,program_application_id ,program_id,program_update_date
106 ,process_activity ,notification_flag ,type
107 ,message_source_code ,language
108 ,message_text, transaction_id
109 )
110 SELECT
111 h.request_id ,'HEADER' ,NULL ,NULL ,NULL ,NULL
112 ,order_source_id ,orig_sys_document_ref
113 ,NULL, NULL ,change_sequence
114 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
115 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
116 ,660 ,NULL ,NULL
117 ,NULL ,NULL ,NULL
118 ,'C' ,USERENV('LANG')
119 ,l_msg_text, OE_MSG_ID_S.NEXTVAL
120 FROM OE_HEADERS_IFACE_ALL h
121 WHERE h.batch_id = p_batch_id
122 AND ( h.payment_type_code = 'CREDIT_CARD'
123 OR h.order_source_id = 10 -- 'Internal' Orders
124 OR h.customer_preference_set_code IS NOT NULL -- Value here requires lines to be in sets!
125 OR h.return_reason_code IS NOT NULL -- for RETURN orders
126 -- Bug 3355762
127 -- Import of closed orders is not supported with HVOP
128 OR h.closed_flag = 'Y'
129 );
130
131 IF g_error_count = 0 THEN
132 IF SQL%ROWCOUNT > 0 THEN
133 g_error_count := 1;
134 IF l_debug_level > 0 THEN
135 oe_debug_pub.add( 'THE ERROR COUNT IS SET 1' ) ;
136 END IF;
137 END IF;
138 END IF;
139 */
140 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_OI_ORIG_SYS_LINE_REF');
141 INSERT INTO OE_PROCESSING_MSGS
142 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
143 ,order_source_id ,original_sys_document_ref
144 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
145 ,attribute_code ,creation_date ,created_by ,last_update_date
146 ,last_updated_by ,last_update_login
147 ,program_application_id ,program_id,program_update_date
148 ,process_activity ,notification_flag ,type
149 ,message_source_code ,language
150 ,message_text, transaction_id
151 )
152 SELECT
153 h.request_id ,'LINE' ,NULL ,NULL ,NULL ,NULL
154 ,l.order_source_id ,l.orig_sys_document_ref
155 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, l.change_sequence
156 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
157 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
158 ,660 ,NULL ,NULL
159 ,NULL ,NULL ,NULL
160 ,'C' ,USERENV('LANG')
161 ,l_msg_text, OE_MSG_ID_S.NEXTVAL
162 FROM OE_HEADERS_IFACE_ALL h, OE_LINES_IFACE_ALL l
163 WHERE h.batch_id = p_batch_id
164 AND h.order_source_id = l.order_source_id
165 AND h.orig_sys_document_ref = l.orig_sys_document_ref
166 AND l.orig_sys_line_ref IS NULL
167 AND l.orig_sys_shipment_ref IS NULL;
168
169 IF g_error_count = 0 THEN
170 IF SQL%ROWCOUNT > 0 THEN
171 g_error_count := 1;
172 IF l_debug_level > 0 THEN
173 oe_debug_pub.add( 'THE ERROR COUNT IS SET 2' ) ;
174 END IF;
175 END IF;
176 END IF;
177 -- comment out with config support change
178
179 /* l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_BULK_NOT_SUPP_LINE_ATTRIBS');
180 INSERT INTO OE_PROCESSING_MSGS
181 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
182 ,order_source_id ,original_sys_document_ref
183 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
184 ,attribute_code ,creation_date ,created_by ,last_update_date
185 ,last_updated_by ,last_update_login
186 ,program_application_id ,program_id,program_update_date
187 ,process_activity ,notification_flag ,type
188 ,message_source_code ,language
189 ,message_text, transaction_id
190 )
191 SELECT
192 h.request_id ,'LINE' ,NULL ,NULL ,NULL ,NULL
193 ,l.order_source_id ,l.orig_sys_document_ref
194 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, l.change_sequence
195 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
196 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
197 ,660 ,NULL ,NULL
198 ,NULL ,NULL ,NULL
199 ,'C' ,USERENV('LANG')
200 ,l_msg_text, OE_MSG_ID_S.NEXTVAL
201 FROM OE_HEADERS_IFACE_ALL h, OE_LINES_IFACE_ALL l
202 WHERE h.batch_id = p_batch_id
203 AND h.order_source_id = l.order_source_id
204 AND h.orig_sys_document_ref = l.orig_sys_document_ref
205 AND ( nvl(l.source_type_code,'INTERNAL') <> 'INTERNAL' -- Drop ships
206 OR (l.arrival_set_name IS NOT NULL OR l.ship_set_name IS NOT NULL)
207 OR l.commitment_id IS NOT NULL
208 OR l.return_reason_code IS NOT NULL -- for RETURN lines
209 OR l.override_atp_date_code IS NOT NULL
210 );
211
212 IF g_error_count = 0 THEN
213 IF SQL%ROWCOUNT > 0 THEN
214 g_error_count := 1;
215 IF l_debug_level > 0 THEN
216 oe_debug_pub.add( 'THE ERROR COUNT IS SET 3' ) ;
217 END IF;
218 END IF;
219 END IF;
220 */
221
222 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_OI_ORIG_SYS_DISCOUNT_REF');
223 INSERT INTO OE_PROCESSING_MSGS
224 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
225 ,order_source_id ,original_sys_document_ref
226 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
227 ,attribute_code ,creation_date ,created_by ,last_update_date
228 ,last_updated_by ,last_update_login
229 ,program_application_id ,program_id,program_update_date
230 ,process_activity ,notification_flag ,type
231 ,message_source_code ,language
232 ,message_text, transaction_id
233 )
234 SELECT
235 h.request_id ,decode(l.orig_sys_line_ref||l.orig_sys_shipment_ref,NULL,'HEADER_ADJ','LINE_ADJ')
236 ,NULL ,NULL ,NULL ,NULL
237 ,l.order_source_id ,l.orig_sys_document_ref
238 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, l.change_sequence
239 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
240 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
241 ,660 ,NULL ,NULL
242 ,NULL ,NULL ,NULL
243 ,'C' ,USERENV('LANG')
244 ,l_msg_text, OE_MSG_ID_S.NEXTVAL
245 FROM OE_HEADERS_IFACE_ALL h, OE_PRICE_ADJS_INTERFACE l
246 WHERE h.batch_id = p_batch_id
247 AND h.order_source_id = l.order_source_id
248 AND h.orig_sys_document_ref = l.orig_sys_document_ref
249 AND l.orig_sys_discount_ref IS NULL;
250
251 IF g_error_count = 0 THEN
252 IF SQL%ROWCOUNT > 0 THEN
253 g_error_count := 1;
254 IF l_debug_level > 0 THEN
255 oe_debug_pub.add( 'THE ERROR COUNT IS SET 4' ) ;
256 END IF;
257 END IF;
258 END IF;
259
260 IF l_debug_level > 0 THEN
261 oe_debug_pub.add( 'EXIT PRE_PROCESS , G_ERROR_COUNT: '||G_ERROR_COUNT ) ;
262 END IF;
263 EXCEPTION
264 WHEN OTHERS THEN
265 IF l_debug_level > 0 THEN
266 oe_debug_pub.add( 'OTHERS ERROR , PRE_PROCESS' ) ;
267 END IF;
268 IF l_debug_level > 0 THEN
269 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
270 END IF;
271 OE_BULK_MSG_PUB.ADD_Exc_Msg
272 ( G_PKG_NAME
273 , 'Pre_Process'
274 );
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END Pre_Process;
277
278
279 ---------------------------------------------------------------------
280 -- PROCEDURE Attributes
281 --
282 -- This API does all attribute validations on interface tables for
283 -- orders in this batch.
284 -- It will insert error messages for all validation failures.
285 ---------------------------------------------------------------------
286
287 PROCEDURE Attributes
288 (p_batch_id IN NUMBER
289 ,p_adjustments_exist IN VARCHAR2 DEFAULT 'N')
290 IS
291 l_msg_text VARCHAR2(2000);
292 l_msg_data VARCHAR2(2000);
293 l_count NUMBER;
294 l_org_id NUMBER;
295 --
296 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
297 --
298 BEGIN
299
300 -- Also, include calculate price attribute validation
301
302 -- Validations for following fields NOT NEEDED!
303 --
304 -- 1.These will be covered in Entity Validations:
305 -- Agreement, Ship From, Ship To Org, Invoice To Org, Deliver To Org
306 -- Sold To Contact, Ship To Contact, Invoice To Contact, Deliver To Contact
307 -- Order Type, Line Type, Tax Exempt Reason, Tax Exempt Number
308 -- Project
309 --
310 -- 2. These are internal fields and cannot be passed by the user:
311 -- Open Flag, Booked Flag, Cancelled Flag,Fulfilled Flag, Flow Status
312 -- Shipping Interfaced Flag, Shippable Flag, Item Type Code
313 --
314 -- 3. Values in following fields not supported in BULK mode:
315 -- Source Type: can only be 'INTERNAL'
316 -- Return Reason Code, Return Desc Flex: RETURNS not supported
317 -- Tax Point: unused
318 -- Commitment: not supported
319 -- Credit Card Code: IPayment not supported
320 -- Source Document Type: orders entered only via order import,
321 -- cannot have a source document type.
322 --
323
324
325 l_msg_data := FND_MESSAGE.GET_STRING('ONT','OE_BULK_INVALID_ATTRIBUTE');
326
327 l_org_id := MO_GLOBAL.Get_Current_Org_Id; --moac
328 -------------------------------------------------------------------
329 -- Attribute Validations for Headers and Lines
330 -------------------------------------------------------------------
331
332 l_msg_text := l_msg_data||
333 OE_ORDER_UTIL.Get_Attribute_Name('ACCOUNTING_RULE_ID');
334 --PP Revenue Recognition
335 --bug 4893057
336 --Included the new accounting rules for partial revenue recognition
337 INSERT INTO OE_PROCESSING_MSGS
338 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
339 ,line_id ,order_source_id ,original_sys_document_ref
340 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
341 ,source_document_type_id ,source_document_id ,source_document_line_id
342 ,attribute_code ,creation_date ,created_by ,last_update_date
343 ,last_updated_by ,last_update_login ,program_application_id ,program_id
344 ,program_update_date ,process_activity ,notification_flag ,type
345 ,message_source_code ,language ,message_text, transaction_id
346 )
347 SELECT
348 request_id , 'HEADER',NULL,NULL,NULL,NULL
349 ,order_source_id ,orig_sys_document_ref ,NULL
350 ,NULL ,change_sequence,NULL,NULL,NULL,'ACCOUNTING_RULE_ID'
351 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
352 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
353 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
354 FROM OE_HEADERS_IFACE_ALL b
355 WHERE batch_id = p_batch_id and
356 b.accounting_rule_id IS NOT NULL and
357 NOT EXISTS (SELECT RULE_ID
358 FROM OE_RA_RULES_V
359 WHERE RULE_ID = b.accounting_rule_id
360 AND STATUS = 'A'
361 AND TYPE in ('A', 'ACC_DUR','PP_DR_ALL','PP_DR_PP'));--bug 4893057
362
363 IF g_error_count = 0 THEN
364 IF SQL%ROWCOUNT > 0 THEN
365 g_error_count := 1;
366 IF l_debug_level > 0 THEN
367 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
368 END IF;
369 END IF;
370 END IF;
371
372 INSERT INTO OE_PROCESSING_MSGS
373 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
374 ,line_id ,order_source_id ,original_sys_document_ref
375 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
376 ,source_document_type_id ,source_document_id ,source_document_line_id
377 ,attribute_code ,creation_date ,created_by ,last_update_date
378 ,last_updated_by ,last_update_login ,program_application_id ,program_id
379 ,program_update_date ,process_activity ,notification_flag ,type
380 ,message_source_code ,language ,message_text, transaction_id
381 )
382 SELECT
383 request_id ,'LINE',NULL,NULL,NULL,NULL
384 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
385 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ACCOUNTING_RULE_ID'
386 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
387 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
388 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
389 FROM OE_LINES_IFACE_ALL b
390 WHERE (order_source_id, orig_sys_document_ref) IN
391 ( SELECT order_source_id, orig_sys_document_ref
392 FROM OE_HEADERS_IFACE_ALL
393 WHERE batch_id = p_batch_id)
394 AND b.accounting_rule_id IS NOT NULL and
395 NOT EXISTS (SELECT RULE_ID
396 FROM OE_RA_RULES_V
397 WHERE RULE_ID = b.accounting_rule_id
398 AND STATUS = 'A'
399 AND TYPE in ('A', 'ACC_DUR','PP_DR_ALL','PP_DR_PP'));--bug 4893057
400
401 IF g_error_count = 0 THEN
402 IF SQL%ROWCOUNT > 0 THEN
403 g_error_count := 1;
404 IF l_debug_level > 0 THEN
405 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
406 END IF;
407 END IF;
408 END IF;
409
410 l_msg_text := l_msg_data||
411 OE_ORDER_UTIL.Get_Attribute_Name('CONVERSION_TYPE_CODE');
412 INSERT INTO OE_PROCESSING_MSGS
413 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
414 ,line_id ,order_source_id ,original_sys_document_ref
415 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
416 ,source_document_type_id ,source_document_id ,source_document_line_id
417 ,attribute_code ,creation_date ,created_by ,last_update_date
418 ,last_updated_by ,last_update_login ,program_application_id ,program_id
419 ,program_update_date ,process_activity ,notification_flag ,type
420 ,message_source_code ,language ,message_text, transaction_id
421 )
422 SELECT
423 request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
424 ,order_source_id ,orig_sys_document_ref ,NULL ,NULL
425 ,change_sequence ,NULL ,NULL ,NULL ,'CONVERSION_TYPE'
426 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
427 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
428 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
429 FROM OE_HEADERS_IFACE_ALL b
430 WHERE batch_id = p_batch_id and
431 b.CONVERSION_TYPE_CODE IS NOT NULL and
432 NOT EXISTS (SELECT CONVERSION_TYPE
433 FROM OE_GL_DAILY_CONVERSION_TYPES_V a
434 WHERE CONVERSION_TYPE = b.CONVERSION_TYPE_CODE);
435
436 IF g_error_count = 0 THEN
437 IF SQL%ROWCOUNT > 0 THEN
438 g_error_count := 1;
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
441 END IF;
442 END IF;
443 END IF;
444
445
446 l_msg_text := l_msg_data||
447 OE_ORDER_UTIL.Get_Attribute_Name('DEMAND_CLASS_CODE');
448 INSERT INTO OE_PROCESSING_MSGS
449 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
450 ,line_id ,order_source_id ,original_sys_document_ref
451 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
452 ,source_document_type_id ,source_document_id ,source_document_line_id
453 ,attribute_code ,creation_date ,created_by ,last_update_date
454 ,last_updated_by ,last_update_login ,program_application_id ,program_id
455 ,program_update_date ,process_activity ,notification_flag ,type
456 ,message_source_code ,language ,message_text, transaction_id
457 )
458 SELECT
459 request_id , 'HEADER',NULL,NULL,NULL,NULL
460 ,order_source_id ,orig_sys_document_ref ,NULL
461 ,NULL ,change_sequence,NULL,NULL,NULL,'DEMAND_CLASS_CODE'
462 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
463 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
464 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
465 FROM OE_HEADERS_IFACE_ALL b
466 WHERE batch_id = p_batch_id and
467 b.demand_class_code IS NOT NULL and
468 NOT EXISTS (SELECT FLV.LOOKUP_CODE
469 from FND_LOOKUP_TYPES FLT, FND_LOOKUP_VALUES FLV
470 WHERE FLV.LOOKUP_TYPE = FLT.LOOKUP_TYPE
471 and FLV.SECURITY_GROUP_ID = FLT.SECURITY_GROUP_ID
472 and FLV.VIEW_APPLICATION_ID = FLT.VIEW_APPLICATION_ID
473 and FLV.LANGUAGE = userenv('LANG')
474 and FLV.VIEW_APPLICATION_ID = 3
475 and FLV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(FLV.LOOKUP_TYPE, FLV.VIEW_APPLICATION_ID)
476 AND FLV.LOOKUP_CODE = b.demand_class_code
477 AND FLV.LOOKUP_TYPE = 'DEMAND_CLASS'
478 AND FLT.APPLICATION_ID = 700
479 AND FLV.ENABLED_FLAG = 'Y'
480 AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE, SYSDATE)
481 AND NVL(FLV.END_DATE_ACTIVE, SYSDATE)
482 );
483
484 --NOT EXISTS (SELECT LOOKUP_CODE
485 -- FROM OE_FND_COMMON_LOOKUPS_V a
486 --WHERE LOOKUP_CODE = b.demand_class_code
487 --AND LOOKUP_TYPE = 'DEMAND_CLASS'
488 --AND APPLICATION_ID = 700
489 --AND ENABLED_FLAG = 'Y'
490 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
491 -- AND NVL(END_DATE_ACTIVE, SYSDATE))
492 --;
493
494 IF g_error_count = 0 THEN
495 IF SQL%ROWCOUNT > 0 THEN
496 g_error_count := 1;
497 IF l_debug_level > 0 THEN
498 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
499 END IF;
500 END IF;
501 END IF;
502
503 INSERT INTO OE_PROCESSING_MSGS
504 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
505 ,line_id ,order_source_id ,original_sys_document_ref
506 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
507 ,source_document_type_id ,source_document_id ,source_document_line_id
508 ,attribute_code ,creation_date ,created_by ,last_update_date
509 ,last_updated_by ,last_update_login ,program_application_id ,program_id
510 ,program_update_date ,process_activity ,notification_flag ,type
511 ,message_source_code ,language ,message_text, transaction_id
512 )
513 SELECT
514 request_id ,'LINE',NULL,NULL,NULL,NULL
515 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
516 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'DEMAND_CLASS_CODE'
517 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
518 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
519 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
520 FROM OE_LINES_IFACE_ALL b
521 WHERE (order_source_id, orig_sys_document_ref) IN
522 ( SELECT order_source_id, orig_sys_document_ref
523 FROM OE_HEADERS_IFACE_ALL
524 WHERE batch_id = p_batch_id)
525 AND b.demand_class_code IS NOT NULL and
526 NOT EXISTS (SELECT FLV.LOOKUP_CODE
527 from FND_LOOKUP_TYPES FLT, FND_LOOKUP_VALUES FLV
528 WHERE FLV.LOOKUP_TYPE = FLT.LOOKUP_TYPE
529 and FLV.SECURITY_GROUP_ID = FLT.SECURITY_GROUP_ID
530 and FLV.VIEW_APPLICATION_ID = FLT.VIEW_APPLICATION_ID
531 and FLV.LANGUAGE = userenv('LANG')
532 and FLV.VIEW_APPLICATION_ID = 3
533 and FLV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(FLV.LOOKUP_TYPE, FLV.VIEW_APPLICATION_ID)
534 AND FLV.LOOKUP_CODE = b.demand_class_code
535 AND FLV.LOOKUP_TYPE = 'DEMAND_CLASS'
536 AND FLT.APPLICATION_ID = 700
537 AND FLV.ENABLED_FLAG = 'Y'
538 AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE, SYSDATE)
539 AND NVL(FLV.END_DATE_ACTIVE, SYSDATE)
540 );
541
542
543 --NOT EXISTS (SELECT LOOKUP_CODE
544 -- FROM OE_FND_COMMON_LOOKUPS_V a
545 --WHERE LOOKUP_CODE = b.demand_class_code
546 --AND LOOKUP_TYPE = 'DEMAND_CLASS'
547 --AND APPLICATION_ID = 700
548 --AND ENABLED_FLAG = 'Y'
549 --AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
550 -- AND NVL(END_DATE_ACTIVE, SYSDATE));
551
552 IF g_error_count = 0 THEN
553 IF SQL%ROWCOUNT > 0 THEN
554 g_error_count := 1;
555 IF l_debug_level > 0 THEN
556 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
557 END IF;
558 END IF;
559 END IF;
560
561
562 l_msg_text := l_msg_data||
563 OE_ORDER_UTIL.Get_Attribute_Name('FOB_POINT_CODE');
564 INSERT INTO OE_PROCESSING_MSGS
565 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
566 ,line_id ,order_source_id ,original_sys_document_ref
567 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
568 ,source_document_type_id ,source_document_id ,source_document_line_id
569 ,attribute_code ,creation_date ,created_by ,last_update_date
570 ,last_updated_by ,last_update_login ,program_application_id ,program_id
571 ,program_update_date ,process_activity ,notification_flag ,type
572 ,message_source_code ,language ,message_text, transaction_id
573 )
574 SELECT
575 request_id , 'HEADER',NULL,NULL,NULL,NULL
576 ,order_source_id ,orig_sys_document_ref ,NULL
577 ,NULL ,change_sequence,NULL,NULL,NULL,'FOB_POINT'
578 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
579 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
580 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
581 FROM OE_HEADERS_IFACE_ALL b
582 WHERE batch_id = p_batch_id and
583 b.fob_point_code IS NOT NULL and
584 NOT EXISTS (SELECT LOOKUP_CODE
585 FROM OE_AR_LOOKUPS_V
586 WHERE LOOKUP_CODE = b.fob_point_code
587 AND LOOKUP_TYPE = 'FOB'
588 AND ENABLED_FLAG = 'Y'
589 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
590 AND NVL(END_DATE_ACTIVE, SYSDATE))
591 ;
592
593 IF g_error_count = 0 THEN
594 IF SQL%ROWCOUNT > 0 THEN
595 g_error_count := 1;
596 IF l_debug_level > 0 THEN
597 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
598 END IF;
599 END IF;
600 END IF;
601
602
603 INSERT INTO OE_PROCESSING_MSGS
604 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
605 ,line_id ,order_source_id ,original_sys_document_ref
606 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
607 ,source_document_type_id ,source_document_id ,source_document_line_id
608 ,attribute_code ,creation_date ,created_by ,last_update_date
609 ,last_updated_by ,last_update_login ,program_application_id ,program_id
610 ,program_update_date ,process_activity ,notification_flag ,type
611 ,message_source_code ,language ,message_text, transaction_id
612 )
613 SELECT
614 request_id ,'LINE',NULL,NULL,NULL,NULL
615 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
616 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'FOB_POINT_CODE'
617 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
618 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
619 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
620 FROM OE_LINES_IFACE_ALL b
621 WHERE (order_source_id, orig_sys_document_ref) IN
622 ( SELECT order_source_id, orig_sys_document_ref
623 FROM OE_HEADERS_IFACE_ALL
624 WHERE batch_id = p_batch_id)
625 AND b.fob_point_code IS NOT NULL and
626 NOT EXISTS (SELECT LOOKUP_CODE
627 FROM OE_AR_LOOKUPS_V
628 WHERE LOOKUP_CODE = b.fob_point_code
629 AND LOOKUP_TYPE = 'FOB'
630 AND ENABLED_FLAG = 'Y'
631 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
632 AND NVL(END_DATE_ACTIVE, SYSDATE));
633
634 IF g_error_count = 0 THEN
635 IF SQL%ROWCOUNT > 0 THEN
636 g_error_count := 1;
637 IF l_debug_level > 0 THEN
638 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
639 END IF;
640 END IF;
641 END IF;
642
643
644 l_msg_text := l_msg_data||
645 OE_ORDER_UTIL.Get_Attribute_Name('FREIGHT_TERMS_CODE');
646 INSERT INTO OE_PROCESSING_MSGS
647 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
648 ,line_id ,order_source_id ,original_sys_document_ref
649 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
650 ,source_document_type_id ,source_document_id ,source_document_line_id
651 ,attribute_code ,creation_date ,created_by ,last_update_date
652 ,last_updated_by ,last_update_login ,program_application_id ,program_id
653 ,program_update_date ,process_activity ,notification_flag ,type
654 ,message_source_code ,language ,message_text, transaction_id
655 )
656 SELECT
657 request_id , 'HEADER',NULL,NULL,NULL,NULL
658 ,order_source_id ,orig_sys_document_ref ,NULL
659 ,NULL ,change_sequence,NULL,NULL,NULL,'FREIGHT_TERMS_CODE'
660 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
661 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
662 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
663 FROM OE_HEADERS_IFACE_ALL b
664 WHERE batch_id = p_batch_id and
665 b.freight_terms_code IS NOT NULL and
666 NOT EXISTS (SELECT LOOKUP_CODE
667 FROM OE_LOOKUPS
668 WHERE LOOKUP_CODE = b.freight_terms_code
669 AND LOOKUP_TYPE = 'FREIGHT_TERMS'
670 AND ENABLED_FLAG = 'Y'
671 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
672 AND NVL(END_DATE_ACTIVE, SYSDATE))
673 ;
674
675 IF g_error_count = 0 THEN
676 IF SQL%ROWCOUNT > 0 THEN
677 g_error_count := 1;
678 IF l_debug_level > 0 THEN
679 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
680 END IF;
681 END IF;
682 END IF;
683
684 INSERT INTO OE_PROCESSING_MSGS
685 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
686 ,line_id ,order_source_id ,original_sys_document_ref
687 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
688 ,source_document_type_id ,source_document_id ,source_document_line_id
689 ,attribute_code ,creation_date ,created_by ,last_update_date
690 ,last_updated_by ,last_update_login ,program_application_id ,program_id
691 ,program_update_date ,process_activity ,notification_flag ,type
692 ,message_source_code ,language ,message_text, transaction_id
693 )
694 SELECT
695 request_id ,'LINE',NULL,NULL,NULL,NULL
696 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
697 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'FREIGHT_TERMS_CODE'
698 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
699 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
700 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
701 FROM OE_LINES_IFACE_ALL b
702 WHERE (order_source_id, orig_sys_document_ref) IN
703 ( SELECT order_source_id, orig_sys_document_ref
704 FROM OE_HEADERS_IFACE_ALL
705 WHERE batch_id = p_batch_id)
706 AND b.freight_terms_code IS NOT NULL and
707 NOT EXISTS (SELECT LOOKUP_CODE
708 FROM OE_LOOKUPS
709 WHERE LOOKUP_CODE = b.freight_terms_code
710 AND LOOKUP_TYPE = 'FREIGHT_TERMS'
711 AND ENABLED_FLAG = 'Y'
712 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
713 AND NVL(END_DATE_ACTIVE, SYSDATE));
714
715 IF g_error_count = 0 THEN
716 IF SQL%ROWCOUNT > 0 THEN
717 g_error_count := 1;
718 IF l_debug_level > 0 THEN
719 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
720 END IF;
721 END IF;
722 END IF;
723
724 l_msg_text := l_msg_data||
725 OE_ORDER_UTIL.Get_Attribute_Name('INVOICING_RULE_ID');
726 INSERT INTO OE_PROCESSING_MSGS
727 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
728 ,line_id ,order_source_id ,original_sys_document_ref
729 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
730 ,source_document_type_id ,source_document_id ,source_document_line_id
731 ,attribute_code ,creation_date ,created_by ,last_update_date
732 ,last_updated_by ,last_update_login ,program_application_id ,program_id
733 ,program_update_date ,process_activity ,notification_flag ,type
734 ,message_source_code ,language ,message_text, transaction_id
735 )
736 SELECT
737 request_id , 'HEADER',NULL,NULL,NULL,NULL
738 ,order_source_id ,orig_sys_document_ref ,NULL
739 ,NULL ,change_sequence,NULL,NULL,NULL,'INVOICING_RULE_ID'
740 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
741 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
742 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
743 FROM OE_HEADERS_IFACE_ALL b
744 WHERE batch_id = p_batch_id and
745 b.invoicing_rule_id IS NOT NULL and
746 NOT EXISTS (SELECT RULE_ID
747 FROM OE_RA_RULES_V
748 WHERE RULE_ID = b.invoicing_rule_id
749 AND STATUS = 'A'
750 AND TYPE = 'I')
751 ;
752
753 IF g_error_count = 0 THEN
754 IF SQL%ROWCOUNT > 0 THEN
755 g_error_count := 1;
756 IF l_debug_level > 0 THEN
757 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
758 END IF;
759 END IF;
760 END IF;
761
762 INSERT INTO OE_PROCESSING_MSGS
763 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
764 ,line_id ,order_source_id ,original_sys_document_ref
765 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
766 ,source_document_type_id ,source_document_id ,source_document_line_id
767 ,attribute_code ,creation_date ,created_by ,last_update_date
768 ,last_updated_by ,last_update_login ,program_application_id ,program_id
769 ,program_update_date ,process_activity ,notification_flag ,type
770 ,message_source_code ,language ,message_text, transaction_id
771 )
772 SELECT
773 request_id ,'LINE',NULL,NULL,NULL,NULL
774 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
775 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'INVOICING_RULE_ID'
776 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
777 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
778 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
779 FROM OE_LINES_IFACE_ALL b
780 WHERE (order_source_id, orig_sys_document_ref) IN
781 ( SELECT order_source_id, orig_sys_document_ref
782 FROM OE_HEADERS_IFACE_ALL
783 WHERE batch_id = p_batch_id)
784 AND b.invoicing_rule_id IS NOT NULL and
785 NOT EXISTS (SELECT RULE_ID
786 FROM OE_RA_RULES_V
787 WHERE RULE_ID = b.invoicing_rule_id
788 AND STATUS = 'A'
789 AND TYPE = 'I');
790
791 IF g_error_count = 0 THEN
792 IF SQL%ROWCOUNT > 0 THEN
793 g_error_count := 1;
794 IF l_debug_level > 0 THEN
795 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
796 END IF;
797 END IF;
798 END IF;
799
800
801 l_msg_text := l_msg_data||
802 OE_ORDER_UTIL.Get_Attribute_Name('ORDER_DATE_TYPE_CODE');
803 INSERT INTO OE_PROCESSING_MSGS
804 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
805 ,line_id ,order_source_id ,original_sys_document_ref
806 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
807 ,source_document_type_id ,source_document_id ,source_document_line_id
808 ,attribute_code ,creation_date ,created_by ,last_update_date
809 ,last_updated_by ,last_update_login ,program_application_id ,program_id
810 ,program_update_date ,process_activity ,notification_flag ,type
811 ,message_source_code ,language ,message_text, transaction_id
812 )
813 SELECT
814 request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
815 ,order_source_id ,orig_sys_document_ref ,NULL ,NULL
816 ,change_sequence ,NULL ,NULL ,NULL ,'ORDER_DATE_TYPE_CODE'
817 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
818 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
819 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
820 FROM OE_HEADERS_IFACE_ALL b
821 WHERE batch_id = p_batch_id
822 AND b.order_date_type_code IS NOT NULL
823 AND NOT EXISTS (SELECT LOOKUP_CODE
824 FROM OE_LOOKUPS a
825 WHERE LOOKUP_TYPE = 'REQUEST_DATE_TYPE'
826 AND LOOKUP_CODE = b.order_date_type_code
827 AND ENABLED_FLAG = 'Y'
828 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
829 AND NVL(END_DATE_ACTIVE, SYSDATE));
830
831 IF g_error_count = 0 THEN
832 IF SQL%ROWCOUNT > 0 THEN
833 g_error_count := 1;
834 IF l_debug_level > 0 THEN
835 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
836 END IF;
837 END IF;
838 END IF;
839
840
841 l_msg_text := l_msg_data||
842 OE_ORDER_UTIL.Get_Attribute_Name('PAYMENT_TERM_ID');
843 INSERT INTO OE_PROCESSING_MSGS
844 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
845 ,line_id ,order_source_id ,original_sys_document_ref
846 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
847 ,source_document_type_id ,source_document_id ,source_document_line_id
848 ,attribute_code ,creation_date ,created_by ,last_update_date
849 ,last_updated_by ,last_update_login ,program_application_id ,program_id
850 ,program_update_date ,process_activity ,notification_flag ,type
851 ,message_source_code ,language ,message_text, transaction_id
852 )
853 SELECT
854 request_id , 'HEADER',NULL,NULL,NULL,NULL
855 ,order_source_id ,orig_sys_document_ref ,NULL
856 ,NULL ,change_sequence,NULL,NULL,NULL,'PAYMENT_TERM_ID'
857 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
858 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
859 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
860 FROM OE_HEADERS_IFACE_ALL b
861 WHERE batch_id = p_batch_id and
862 b.payment_term_id IS NOT NULL and
863 NOT EXISTS (select a.TERM_ID
864 FROM OE_RA_TERMS_V a
865 WHERE TERM_ID = b.payment_term_id
866 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
867 AND NVL(END_DATE_ACTIVE, SYSDATE))
868 ;
869
870 IF g_error_count = 0 THEN
871 IF SQL%ROWCOUNT > 0 THEN
872 g_error_count := 1;
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
875 END IF;
876 END IF;
877 END IF;
878
879 INSERT INTO OE_PROCESSING_MSGS
880 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
881 ,line_id ,order_source_id ,original_sys_document_ref
882 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
883 ,source_document_type_id ,source_document_id ,source_document_line_id
884 ,attribute_code ,creation_date ,created_by ,last_update_date
885 ,last_updated_by ,last_update_login ,program_application_id ,program_id
886 ,program_update_date ,process_activity ,notification_flag ,type
887 ,message_source_code ,language ,message_text, transaction_id
888 )
889 SELECT
890 request_id ,'LINE',NULL,NULL,NULL,NULL
891 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
892 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'PAYMENT_TERM_ID'
893 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
894 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
895 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
896 FROM OE_LINES_IFACE_ALL b
897 WHERE (order_source_id, orig_sys_document_ref) IN
898 ( SELECT order_source_id, orig_sys_document_ref
899 FROM OE_HEADERS_IFACE_ALL
900 WHERE batch_id = p_batch_id)
901 AND b.payment_term_id IS NOT NULL and
902 NOT EXISTS (select a.TERM_ID
903 FROM OE_RA_TERMS_V a
904 WHERE TERM_ID = b.payment_term_id
905 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
906 AND NVL(END_DATE_ACTIVE, SYSDATE));
907
908 IF g_error_count = 0 THEN
909 IF SQL%ROWCOUNT > 0 THEN
910 g_error_count := 1;
911 IF l_debug_level > 0 THEN
912 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
913 END IF;
914 END IF;
915 END IF;
916
917
918 l_msg_text := l_msg_data||
919 OE_ORDER_UTIL.Get_Attribute_Name('PRICE_LIST_ID');
920 INSERT INTO OE_PROCESSING_MSGS
921 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
922 ,line_id ,order_source_id ,original_sys_document_ref
923 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
924 ,source_document_type_id ,source_document_id ,source_document_line_id
925 ,attribute_code ,creation_date ,created_by ,last_update_date
926 ,last_updated_by ,last_update_login ,program_application_id ,program_id
927 ,program_update_date ,process_activity ,notification_flag ,type
928 ,message_source_code ,language ,message_text, transaction_id
929 )
930 SELECT
931 request_id , 'HEADER',NULL,NULL,NULL,NULL
932 ,order_source_id ,orig_sys_document_ref ,NULL
933 ,NULL ,change_sequence,NULL,NULL,NULL,'PRICE_LIST_ID'
934 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
935 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
936 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
937 FROM OE_HEADERS_IFACE_ALL b
938 WHERE batch_id = p_batch_id and
939 b.price_list_id IS NOT NULL and
940 NOT EXISTS (select list_header_id
941 FROM qp_list_headers_vl
942 WHERE list_header_id = b.price_list_id
943 and list_type_code in ('PRL', 'AGR') and
944 nvl(active_flag,'Y') ='Y')
945 ;
946
947 IF g_error_count = 0 THEN
948 IF SQL%ROWCOUNT > 0 THEN
949 g_error_count := 1;
950 IF l_debug_level > 0 THEN
951 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
952 END IF;
953 END IF;
954 END IF;
955
956 INSERT INTO OE_PROCESSING_MSGS
957 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
958 ,line_id ,order_source_id ,original_sys_document_ref
959 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
960 ,source_document_type_id ,source_document_id ,source_document_line_id
961 ,attribute_code ,creation_date ,created_by ,last_update_date
962 ,last_updated_by ,last_update_login ,program_application_id ,program_id
963 ,program_update_date ,process_activity ,notification_flag ,type
964 ,message_source_code ,language ,message_text, transaction_id
965 )
966 SELECT
967 request_id ,'LINE',NULL,NULL,NULL,NULL
968 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
969 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'PRICE_LIST_ID'
970 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
971 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
972 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
973 FROM OE_LINES_IFACE_ALL b
974 WHERE (order_source_id, orig_sys_document_ref) IN
975 ( SELECT order_source_id, orig_sys_document_ref
976 FROM OE_HEADERS_IFACE_ALL
977 WHERE batch_id = p_batch_id)
978 AND b.price_list_id IS NOT NULL and
979 NOT EXISTS (select list_header_id
980 FROM qp_list_headers_vl
981 WHERE list_header_id = b.price_list_id
982 and list_type_code in ('PRL', 'AGR') and
983 nvl(active_flag,'Y') ='Y');
984
985 IF g_error_count = 0 THEN
986 IF SQL%ROWCOUNT > 0 THEN
987 g_error_count := 1;
988 IF l_debug_level > 0 THEN
989 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
990 END IF;
991 END IF;
992 END IF;
993
994 l_msg_text := l_msg_data||
995 OE_ORDER_UTIL.Get_Attribute_Name('SHIPMENT_PRIORITY_CODE');
996 INSERT INTO OE_PROCESSING_MSGS
997 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
998 ,line_id ,order_source_id ,original_sys_document_ref
999 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1000 ,source_document_type_id ,source_document_id ,source_document_line_id
1001 ,attribute_code ,creation_date ,created_by ,last_update_date
1002 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1003 ,program_update_date ,process_activity ,notification_flag ,type
1004 ,message_source_code ,language ,message_text, transaction_id
1005 )
1006 SELECT
1007 request_id , 'HEADER',NULL,NULL,NULL,NULL
1008 ,order_source_id ,orig_sys_document_ref ,NULL
1009 ,NULL ,change_sequence,NULL,NULL,NULL,'SHIPMENT_PRIORITY_CODE'
1010 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1011 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1012 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1013 FROM OE_HEADERS_IFACE_ALL b
1014 WHERE batch_id = p_batch_id and
1015 b.shipment_priority_code IS NOT NULL and
1016 NOT EXISTS (select LOOKUP_CODE
1017 FROM OE_LOOKUPS
1018 WHERE LOOKUP_CODE = b.shipment_priority_code
1019 AND LOOKUP_TYPE = 'SHIPMENT_PRIORITY'
1020 AND ENABLED_FLAG = 'Y'
1021 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1022 AND NVL(END_DATE_ACTIVE, SYSDATE))
1023 ;
1024
1025 IF g_error_count = 0 THEN
1026 IF SQL%ROWCOUNT > 0 THEN
1027 g_error_count := 1;
1028 IF l_debug_level > 0 THEN
1029 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1030 END IF;
1031 END IF;
1032 END IF;
1033
1034 INSERT INTO OE_PROCESSING_MSGS
1035 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1036 ,line_id ,order_source_id ,original_sys_document_ref
1037 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1038 ,source_document_type_id ,source_document_id ,source_document_line_id
1039 ,attribute_code ,creation_date ,created_by ,last_update_date
1040 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1041 ,program_update_date ,process_activity ,notification_flag ,type
1042 ,message_source_code ,language ,message_text, transaction_id
1043 )
1044 SELECT
1045 request_id ,'LINE',NULL,NULL,NULL,NULL
1046 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1047 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SHIPMENT_PRIORITY_CODE'
1048 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1049 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1050 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1051 FROM OE_LINES_IFACE_ALL b
1052 WHERE (order_source_id, orig_sys_document_ref) IN
1053 ( SELECT order_source_id, orig_sys_document_ref
1054 FROM OE_HEADERS_IFACE_ALL
1055 WHERE batch_id = p_batch_id)
1056 AND b.shipment_priority_code IS NOT NULL and
1057 NOT EXISTS (select LOOKUP_CODE
1058 FROM OE_LOOKUPS
1059 WHERE LOOKUP_CODE = b.shipment_priority_code
1060 AND LOOKUP_TYPE = 'SHIPMENT_PRIORITY'
1061 AND ENABLED_FLAG = 'Y'
1062 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1063 AND NVL(END_DATE_ACTIVE, SYSDATE));
1064
1065 IF g_error_count = 0 THEN
1066 IF SQL%ROWCOUNT > 0 THEN
1067 g_error_count := 1;
1068 IF l_debug_level > 0 THEN
1069 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1070 END IF;
1071 END IF;
1072 END IF;
1073
1074
1075 l_msg_text := l_msg_data||
1076 OE_ORDER_UTIL.Get_Attribute_Name('SHIPPING_METHOD_CODE');
1077 INSERT INTO OE_PROCESSING_MSGS
1078 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1079 ,line_id ,order_source_id ,original_sys_document_ref
1080 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1081 ,source_document_type_id ,source_document_id ,source_document_line_id
1082 ,attribute_code ,creation_date ,created_by ,last_update_date
1083 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1084 ,program_update_date ,process_activity ,notification_flag ,type
1085 ,message_source_code ,language ,message_text, transaction_id
1086 )
1087 SELECT
1088 request_id , 'HEADER',NULL,NULL,NULL,NULL
1089 ,order_source_id ,orig_sys_document_ref ,NULL
1090 ,NULL ,change_sequence,NULL,NULL,NULL,'SHIPPING_METHOD'
1091 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1092 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1093 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1094 FROM OE_HEADERS_IFACE_ALL b
1095 WHERE batch_id = p_batch_id and
1096 b.shipping_method_code IS NOT NULL and
1097 NOT EXISTS (select LOOKUP_CODE
1098 FROM OE_SHIP_METHODS_V
1099 WHERE lookup_code = b.shipping_method_code
1100 AND SYSDATE <= NVL(END_DATE_ACTIVE, SYSDATE))
1101 ;
1102
1103 IF g_error_count = 0 THEN
1104 IF SQL%ROWCOUNT > 0 THEN
1105 g_error_count := 1;
1106 IF l_debug_level > 0 THEN
1107 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1108 END IF;
1109 END IF;
1110 END IF;
1111
1112 INSERT INTO OE_PROCESSING_MSGS
1113 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1114 ,line_id ,order_source_id ,original_sys_document_ref
1115 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1116 ,source_document_type_id ,source_document_id ,source_document_line_id
1117 ,attribute_code ,creation_date ,created_by ,last_update_date
1118 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1119 ,program_update_date ,process_activity ,notification_flag ,type
1120 ,message_source_code ,language ,message_text, transaction_id
1121 )
1122 SELECT
1123 request_id ,'LINE',NULL,NULL,NULL,NULL
1124 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1125 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SHIPPING_METHOD_CODE'
1126 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1127 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1128 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1129 FROM OE_LINES_IFACE_ALL b
1130 WHERE (order_source_id, orig_sys_document_ref) IN
1131 ( SELECT order_source_id, orig_sys_document_ref
1132 FROM OE_HEADERS_IFACE_ALL
1133 WHERE batch_id = p_batch_id)
1134 AND b.shipping_method_code IS NOT NULL and
1135 NOT EXISTS (select LOOKUP_CODE
1136 FROM OE_SHIP_METHODS_V
1137 WHERE lookup_code = b.shipping_method_code
1138 AND SYSDATE <= NVL(END_DATE_ACTIVE, SYSDATE)) ;
1139
1140 IF g_error_count = 0 THEN
1141 IF SQL%ROWCOUNT > 0 THEN
1142 g_error_count := 1;
1143 IF l_debug_level > 0 THEN
1144 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1145 END IF;
1146 END IF;
1147 END IF;
1148
1149
1150 l_msg_text := l_msg_data||
1151 OE_ORDER_UTIL.Get_Attribute_Name('SOLD_TO_ORG_ID');
1152 INSERT INTO OE_PROCESSING_MSGS
1153 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1154 ,line_id ,order_source_id ,original_sys_document_ref
1155 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1156 ,source_document_type_id ,source_document_id ,source_document_line_id
1157 ,attribute_code ,creation_date ,created_by ,last_update_date
1158 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1159 ,program_update_date ,process_activity ,notification_flag ,type
1160 ,message_source_code ,language ,message_text, transaction_id
1161 )
1162 SELECT
1163 request_id , 'HEADER',NULL,NULL,NULL,NULL
1164 ,order_source_id ,orig_sys_document_ref ,NULL
1165 ,NULL ,change_sequence,NULL,NULL,NULL,'SOLD_TO_ORG'
1166 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1167 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1168 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1169 FROM OE_HEADERS_IFACE_ALL b
1170 WHERE batch_id = p_batch_id and
1171 b.sold_to_org_id IS NOT NULL and
1172 NOT EXISTS (select ORGANIZATION_ID
1173 FROM OE_SOLD_TO_ORGS_V
1174 WHERE ORGANIZATION_ID = b.sold_to_org_id
1175 AND STATUS = 'A'
1176 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1177 AND NVL(END_DATE_ACTIVE, SYSDATE))
1178 ;
1179
1180 IF g_error_count = 0 THEN
1181 IF SQL%ROWCOUNT > 0 THEN
1182 g_error_count := 1;
1183 IF l_debug_level > 0 THEN
1184 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1185 END IF;
1186 END IF;
1187 END IF;
1188
1189 INSERT INTO OE_PROCESSING_MSGS
1190 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1191 ,line_id ,order_source_id ,original_sys_document_ref
1192 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1193 ,source_document_type_id ,source_document_id ,source_document_line_id
1194 ,attribute_code ,creation_date ,created_by ,last_update_date
1195 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1196 ,program_update_date ,process_activity ,notification_flag ,type
1197 ,message_source_code ,language ,message_text, transaction_id
1198 )
1199 SELECT
1200 request_id ,'LINE',NULL,NULL,NULL,NULL
1201 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1202 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SOLD_TO_ORG_ID'
1203 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1204 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1205 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1206 FROM OE_LINES_IFACE_ALL b
1207 WHERE (order_source_id, orig_sys_document_ref) IN
1208 ( SELECT order_source_id, orig_sys_document_ref
1209 FROM OE_HEADERS_IFACE_ALL
1210 WHERE batch_id = p_batch_id)
1211 AND b.sold_to_org_id IS NOT NULL and
1212 NOT EXISTS (select ORGANIZATION_ID
1213 FROM OE_SOLD_TO_ORGS_V
1214 WHERE ORGANIZATION_ID = b.sold_to_org_id
1215 AND STATUS = 'A'
1216 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1217 AND NVL(END_DATE_ACTIVE, SYSDATE));
1218
1219 IF g_error_count = 0 THEN
1220 IF SQL%ROWCOUNT > 0 THEN
1221 g_error_count := 1;
1222 IF l_debug_level > 0 THEN
1223 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1224 END IF;
1225 END IF;
1226 END IF;
1227
1228
1229 l_msg_text := l_msg_data||
1230 OE_ORDER_UTIL.Get_Attribute_Name('TAX_EXEMPT_FLAG');
1231 INSERT INTO OE_PROCESSING_MSGS
1232 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1233 ,line_id ,order_source_id ,original_sys_document_ref
1234 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1235 ,source_document_type_id ,source_document_id ,source_document_line_id
1236 ,attribute_code ,creation_date ,created_by ,last_update_date
1237 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1238 ,program_update_date ,process_activity ,notification_flag ,type
1239 ,message_source_code ,language ,message_text, transaction_id
1240 )
1241 SELECT
1242 request_id , 'HEADER',NULL,NULL,NULL,NULL
1243 ,order_source_id ,orig_sys_document_ref ,NULL
1244 ,NULL ,change_sequence,NULL,NULL,NULL,'TAX_EXEMPT_FLAG'
1245 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1246 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1247 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1248 FROM OE_HEADERS_IFACE_ALL b
1249 WHERE batch_id = p_batch_id and
1250 b.tax_exempt_flag IS NOT NULL and
1251 NOT EXISTS (SELECT LOOKUP_CODE
1252 FROM OE_AR_LOOKUPS_V
1253 WHERE LOOKUP_CODE = b.tax_exempt_flag
1254 AND LOOKUP_TYPE = 'TAX_CONTROL_FLAG'
1255 AND ENABLED_FLAG = 'Y'
1256 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1257 AND NVL(END_DATE_ACTIVE, SYSDATE))
1258 ;
1259
1260 IF g_error_count = 0 THEN
1261 IF SQL%ROWCOUNT > 0 THEN
1262 g_error_count := 1;
1263 IF l_debug_level > 0 THEN
1264 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1265 END IF;
1266 END IF;
1267 END IF;
1268
1269 INSERT INTO OE_PROCESSING_MSGS
1270 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1271 ,line_id ,order_source_id ,original_sys_document_ref
1272 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1273 ,source_document_type_id ,source_document_id ,source_document_line_id
1274 ,attribute_code ,creation_date ,created_by ,last_update_date
1275 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1276 ,program_update_date ,process_activity ,notification_flag ,type
1277 ,message_source_code ,language ,message_text, transaction_id
1278 )
1279 SELECT
1280 request_id ,'LINE',NULL,NULL,NULL,NULL
1281 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1282 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'TAX_EXEMPT_FLAG'
1283 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1284 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1285 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1286 FROM OE_LINES_IFACE_ALL b
1287 WHERE (order_source_id, orig_sys_document_ref) IN
1288 ( SELECT order_source_id, orig_sys_document_ref
1289 FROM OE_HEADERS_IFACE_ALL
1290 WHERE batch_id = p_batch_id)
1291 AND b.tax_exempt_flag IS NOT NULL and
1292 NOT EXISTS (SELECT LOOKUP_CODE
1293 FROM OE_AR_LOOKUPS_V
1294 WHERE LOOKUP_CODE = b.tax_exempt_flag
1295 AND LOOKUP_TYPE = 'TAX_CONTROL_FLAG'
1296 AND ENABLED_FLAG = 'Y'
1297 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1298 AND NVL(END_DATE_ACTIVE, SYSDATE));
1299
1300 IF g_error_count = 0 THEN
1301 IF SQL%ROWCOUNT > 0 THEN
1302 g_error_count := 1;
1303 IF l_debug_level > 0 THEN
1304 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1305 END IF;
1306 END IF;
1307 END IF;
1308
1309
1310 l_msg_text := l_msg_data||
1311 OE_ORDER_UTIL.Get_Attribute_Name('TRANSACTIONAL_CURR_CODE');
1312 INSERT INTO OE_PROCESSING_MSGS
1313 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1314 ,line_id ,order_source_id ,original_sys_document_ref
1315 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1316 ,source_document_type_id ,source_document_id ,source_document_line_id
1317 ,attribute_code ,creation_date ,created_by ,last_update_date
1318 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1319 ,program_update_date ,process_activity ,notification_flag ,type
1320 ,message_source_code ,language ,message_text, transaction_id
1321 )
1322 SELECT
1323 request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
1324 ,order_source_id ,orig_sys_document_ref ,NULL ,NULL
1325 ,change_sequence ,NULL ,NULL ,NULL ,'TRANSACTIONAL_CURR_CODE'
1326 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1327 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
1328 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1329 FROM OE_HEADERS_IFACE_ALL b
1330 WHERE batch_id = p_batch_id and
1331 b.transactional_curr_code IS NOT NULL and
1332 NOT EXISTS (SELECT CURRENCY_CODE
1333 FROM OE_FND_CURRENCIES_V
1334 WHERE CURRENCY_CODE = b.transactional_curr_code
1335 AND CURRENCY_FLAG = 'Y'
1336 AND ENABLED_FLAG = 'Y'
1337 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1338 AND NVL(END_DATE_ACTIVE, SYSDATE));
1339
1340 IF g_error_count = 0 THEN
1341 IF SQL%ROWCOUNT > 0 THEN
1342 g_error_count := 1;
1343 IF l_debug_level > 0 THEN
1344 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1345 END IF;
1346 END IF;
1347 END IF;
1348
1349
1350 l_msg_text := l_msg_data||
1351 OE_ORDER_UTIL.Get_Attribute_Name('PAYMENT_TYPE_CODE');
1352 INSERT INTO OE_PROCESSING_MSGS
1353 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1354 ,line_id ,order_source_id ,original_sys_document_ref
1355 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1356 ,source_document_type_id ,source_document_id ,source_document_line_id
1357 ,attribute_code ,creation_date ,created_by ,last_update_date
1358 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1359 ,program_update_date ,process_activity ,notification_flag ,type
1360 ,message_source_code ,language ,message_text, transaction_id
1361 )
1362 SELECT
1363 request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
1364 ,order_source_id ,orig_sys_document_ref ,NULL ,NULL
1365 ,change_sequence ,NULL ,NULL ,NULL ,'PAYMENT_TYPE_CODE'
1366 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1367 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
1368 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1369 FROM OE_HEADERS_IFACE_ALL b
1370 WHERE batch_id = p_batch_id and
1371 b.payment_type_code IS NOT NULL and
1372 NOT EXISTS (SELECT LOOKUP_CODE
1373 FROM OE_LOOKUPS
1374 WHERE LOOKUP_CODE = b.payment_type_code
1375 AND LOOKUP_TYPE = 'PAYMENT TYPE'
1376 AND ENABLED_FLAG = 'Y'
1377 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1378 AND NVL(END_DATE_ACTIVE, SYSDATE));
1379
1380 IF g_error_count = 0 THEN
1381 IF SQL%ROWCOUNT > 0 THEN
1382 g_error_count := 1;
1383 IF l_debug_level > 0 THEN
1384 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1385 END IF;
1386 END IF;
1387 END IF;
1388
1389
1390 l_msg_text := l_msg_data||
1391 OE_ORDER_UTIL.Get_Attribute_Name('SALESREP_ID');
1392
1393 INSERT INTO OE_PROCESSING_MSGS
1394 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1395 ,line_id ,order_source_id ,original_sys_document_ref
1396 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1397 ,source_document_type_id ,source_document_id ,source_document_line_id
1398 ,attribute_code ,creation_date ,created_by ,last_update_date
1399 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1400 ,program_update_date ,process_activity ,notification_flag ,type
1401 ,message_source_code ,language ,message_text, transaction_id
1402 )
1403 SELECT /* MOAC_SQL_CHANGE */
1404 request_id , 'HEADER',NULL,NULL,NULL,NULL
1405 ,order_source_id ,orig_sys_document_ref ,NULL
1406 ,NULL ,change_sequence,NULL,NULL,NULL,'SALESREP_ID'
1407 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1408 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1409 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1410 FROM OE_HEADERS_IFACE_ALL b
1411 WHERE batch_id = p_batch_id
1412 AND b.salesrep_id IS NOT NULL
1413 AND NOT EXISTS (SELECT a.salesrep_id
1414 from ra_salesreps_all a
1415 where salesrep_id =b.salesrep_id
1416 and sysdate between NVL(start_date_active,sysdate)
1417 and NVL(end_date_active,sysdate))
1418 AND NOT EXISTS (SELECT jrs.salesrep_id
1419 from jtf_rs_salesreps jrs,
1420 jtf_rs_resource_extns jre
1421 where jrs.salesrep_id = b.salesrep_id
1422 and jrs.resource_id = jre.resource_id
1423 and jre.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
1424 and jrs.org_id = l_org_id
1425 /* and nvl(jrs.ORG_ID,nvl(to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',
1426 null,substrb(userenv('CLIENT_INFO'),1,10))),-99)) =
1427 nvl(to_number(decode(substrb(USERENV('CLIENT_INFO'),1,1),' ',null,
1428 substrb(userenv('CLIENT_INFO'),1,10))),-99) */
1429 and sysdate between nvl(jrs.start_date_active,sysdate)
1430 and nvl(jrs.end_date_active,sysdate))
1431 ;
1432
1433 IF g_error_count = 0 THEN
1434 IF SQL%ROWCOUNT > 0 THEN
1435 g_error_count := 1;
1436 IF l_debug_level > 0 THEN
1437 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1438 END IF;
1439 END IF;
1440 END IF;
1441
1442 INSERT INTO OE_PROCESSING_MSGS
1443 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1444 ,line_id ,order_source_id ,original_sys_document_ref
1445 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1446 ,source_document_type_id ,source_document_id ,source_document_line_id
1447 ,attribute_code ,creation_date ,created_by ,last_update_date
1448 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1449 ,program_update_date ,process_activity ,notification_flag ,type
1450 ,message_source_code ,language ,message_text, transaction_id
1451 )
1452 SELECT /* MOAC_SQL_CHANGE */
1453 request_id ,'LINE',NULL,NULL,NULL,NULL
1454 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1455 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SALESREP_ID'
1456 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1457 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1458 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1459 FROM OE_LINES_IFACE_ALL b
1460 WHERE (order_source_id, orig_sys_document_ref) IN
1461 ( SELECT order_source_id, orig_sys_document_ref
1462 FROM OE_HEADERS_IFACE_ALL
1463 WHERE batch_id = p_batch_id)
1464 AND b.salesrep_id IS NOT NULL
1465 AND NOT EXISTS (SELECT a.salesrep_id
1466 from ra_salesreps_all a
1467 where salesrep_id =b.salesrep_id
1468 and sysdate between NVL(start_date_active,sysdate)
1469 and NVL(end_date_active,sysdate))
1470 AND NOT EXISTS (SELECT jrs.salesrep_id
1471 from jtf_rs_salesreps jrs,
1472 jtf_rs_resource_extns jre
1473 where jrs.salesrep_id = b.salesrep_id
1474 and jrs.resource_id = jre.resource_id
1475 and jre.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
1476 and jrs.org_id = l_org_id
1477 /* and nvl(jrs.ORG_ID,nvl(to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',
1478 null,substrb(userenv('CLIENT_INFO'),1,10))),-99)) =
1479 nvl(to_number(decode(substrb(USERENV('CLIENT_INFO'),1,1),' ',null,
1480 substrb(userenv('CLIENT_INFO'),1,10))),-99) */
1481 and sysdate between nvl(jrs.start_date_active,sysdate)
1482 and nvl(jrs.end_date_active,sysdate))
1483 ;
1484
1485 IF g_error_count = 0 THEN
1486 IF SQL%ROWCOUNT > 0 THEN
1487 g_error_count := 1;
1488 IF l_debug_level > 0 THEN
1489 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1490 END IF;
1491 END IF;
1492 END IF;
1493
1494 l_msg_text := l_msg_data||
1495 OE_ORDER_UTIL.Get_Attribute_Name('SALES_CHANNEL_CODE');
1496 INSERT INTO OE_PROCESSING_MSGS
1497 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1498 ,line_id ,order_source_id ,original_sys_document_ref
1499 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1500 ,source_document_type_id ,source_document_id ,source_document_line_id
1501 ,attribute_code ,creation_date ,created_by ,last_update_date
1502 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1503 ,program_update_date ,process_activity ,notification_flag ,type
1504 ,message_source_code ,language ,message_text, transaction_id
1505 )
1506 SELECT
1507 request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
1508 ,order_source_id ,orig_sys_document_ref ,NULL ,NULL
1509 ,change_sequence ,NULL ,NULL ,NULL ,'SALES_CHANNEL_CODE'
1510 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1511 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
1512 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1513 FROM OE_HEADERS_IFACE_ALL b
1514 WHERE batch_id = p_batch_id and
1515 b.sales_channel_code IS NOT NULL and
1516 NOT EXISTS (SELECT LOOKUP_CODE
1517 FROM OE_LOOKUPS
1518 WHERE LOOKUP_CODE = b.sales_channel_code
1519 AND LOOKUP_TYPE = 'SALES_CHANNEL'
1520 AND ENABLED_FLAG = 'Y'
1521 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
1522 AND NVL(END_DATE_ACTIVE, SYSDATE));
1523
1524 IF g_error_count = 0 THEN
1525 IF SQL%ROWCOUNT > 0 THEN
1526 g_error_count := 1;
1527 IF l_debug_level > 0 THEN
1528 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1529 END IF;
1530 END IF;
1531 END IF;
1532
1533
1534 l_msg_text := l_msg_data||
1535 OE_ORDER_UTIL.Get_Attribute_Name('END_ITEM_UNIT_NUMBER');
1536 INSERT INTO OE_PROCESSING_MSGS
1537 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1538 ,line_id ,order_source_id ,original_sys_document_ref
1539 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1540 ,source_document_type_id ,source_document_id ,source_document_line_id
1541 ,attribute_code ,creation_date ,created_by ,last_update_date
1542 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1543 ,program_update_date ,process_activity ,notification_flag ,type
1544 ,message_source_code ,language ,message_text, transaction_id
1545 )
1546 SELECT
1547 request_id ,'LINE',NULL,NULL,NULL,NULL
1548 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1549 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'END_ITEM_UNIT_NUMBER'
1550 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1551 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1552 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1553 FROM OE_LINES_IFACE_ALL b
1554 WHERE (order_source_id, orig_sys_document_ref) IN
1555 ( SELECT order_source_id, orig_sys_document_ref
1556 FROM OE_HEADERS_IFACE_ALL
1557 WHERE batch_id = p_batch_id)
1558 AND b.end_item_unit_number IS NOT NULL and
1559 NOT EXISTS (SELECT unit_number
1560 FROM pjm_unit_numbers_lov_v
1561 WHERE unit_number = b.end_item_unit_number);
1562
1563 IF g_error_count = 0 THEN
1564 IF SQL%ROWCOUNT > 0 THEN
1565 g_error_count := 1;
1566 IF l_debug_level > 0 THEN
1567 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1568 END IF;
1569 END IF;
1570 END IF;
1571
1572 l_msg_text := l_msg_data||
1573 OE_ORDER_UTIL.Get_Attribute_Name('CALCULATE_PRICE_FLAG');
1574 INSERT INTO OE_PROCESSING_MSGS
1575 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1576 ,line_id ,order_source_id ,original_sys_document_ref
1577 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1578 ,source_document_type_id ,source_document_id ,source_document_line_id
1579 ,attribute_code ,creation_date ,created_by ,last_update_date
1580 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1581 ,program_update_date ,process_activity ,notification_flag ,type
1582 ,message_source_code ,language ,message_text, transaction_id
1583 )
1584 SELECT
1585 request_id ,'LINE',NULL,NULL,NULL,NULL
1586 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1587 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'END_ITEM_UNIT_NUMBER'
1588 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1589 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1590 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1591 FROM OE_LINES_IFACE_ALL b
1592 WHERE (order_source_id, orig_sys_document_ref) IN
1593 ( SELECT order_source_id, orig_sys_document_ref
1594 FROM OE_HEADERS_IFACE_ALL
1595 WHERE batch_id = p_batch_id)
1596 AND NVL(b.calculate_price_flag,'Y') NOT IN ('Y','P','N');
1597
1598 IF g_error_count = 0 THEN
1599 IF SQL%ROWCOUNT > 0 THEN
1600 g_error_count := 1;
1601 IF l_debug_level > 0 THEN
1602 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1603 END IF;
1604 END IF;
1605 END IF;
1606
1607 -- PROCESS HVOP pre_process validation from order import for UOM2 for Process
1608 -- same as in OEXVIMSB.pls
1609
1610
1611 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1612 -- INVCONV IF OE_Bulk_Order_PVT.G_PROCESS_INSTALLED_FLAG = 'Y' THEN
1613 -- PROCESS DUAL HVOP 1
1614 -- check for invalid item/warehouse combo INVCONV
1615
1616
1617 l_msg_text := l_msg_data||
1618 OE_ORDER_UTIL.Get_Attribute_Name('INVENTORY_ITEM_ID');
1619 INSERT INTO OE_PROCESSING_MSGS
1620 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1621 ,line_id ,order_source_id ,original_sys_document_ref
1622 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1623 ,source_document_type_id ,source_document_id ,source_document_line_id
1624 ,attribute_code ,creation_date ,created_by ,last_update_date
1625 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1626 ,program_update_date ,process_activity ,notification_flag ,type
1627 ,message_source_code ,language ,message_text, transaction_id
1628 )
1629 SELECT
1630 request_id ,'LINE',NULL,NULL,NULL,NULL
1631 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1632 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
1633 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1634 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1635 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1636 FROM OE_LINES_IFACE_ALL b
1637 WHERE (order_source_id, orig_sys_document_ref) IN
1638 ( SELECT order_source_id, orig_sys_document_ref
1639 FROM OE_HEADERS_IFACE_ALL
1640 WHERE batch_id = p_batch_id)
1641 -- Added to fix bug 5394064. Validate this ID only if populated
1642 AND b.inventory_item_id IS NOT NULL -- added to fix bug 5394064
1643 AND NOT EXISTS (
1644 SELECT tracking_quantity_ind
1645 FROM mtl_system_items
1646 WHERE organization_id = b.ship_from_org_id
1647 AND inventory_item_id = b.inventory_item_id
1648 );
1649
1650 IF g_error_count = 0 THEN
1651 IF SQL%ROWCOUNT > 0 THEN
1652 g_error_count := 1;
1653 IF l_debug_level > 0 THEN
1654 oe_debug_pub.add( 'PROCESS DUAL 0 THE ERROR COUNT IS SET' ) ;
1655 END IF;
1656 END IF;
1657 END IF;
1658
1659
1660 -- PROCESS DUAL HVOP 2. validation is ordered_quantity_uom2;
1661 -- if line non-dual then do not supply UOM2 OR secondary quantity
1662
1663 l_msg_text := l_msg_data||
1664 OE_ORDER_UTIL.Get_Attribute_Name('ORDERED_QUANTITY_UOM2');
1665 INSERT INTO OE_PROCESSING_MSGS
1666 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1667 ,line_id ,order_source_id ,original_sys_document_ref
1668 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1669 ,source_document_type_id ,source_document_id ,source_document_line_id
1670 ,attribute_code ,creation_date ,created_by ,last_update_date
1671 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1672 ,program_update_date ,process_activity ,notification_flag ,type
1673 ,message_source_code ,language ,message_text, transaction_id
1674 )
1675 SELECT
1676 request_id ,'LINE',NULL,NULL,NULL,NULL
1677 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1678 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
1679 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1680 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1681 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1682 FROM OE_LINES_IFACE_ALL b
1683 WHERE (order_source_id, orig_sys_document_ref) IN
1684 ( SELECT order_source_id, orig_sys_document_ref
1685 FROM OE_HEADERS_IFACE_ALL
1686 WHERE batch_id = p_batch_id)
1687 AND ( b.ordered_quantity_uom2 IS NOT NULL
1688 OR b.ordered_quantity2 IS NOT NULL )
1689
1690 AND EXISTS (
1691 SELECT tracking_quantity_ind
1692 FROM mtl_system_items
1693 WHERE organization_id = b.ship_from_org_id
1694 AND inventory_item_id = b.inventory_item_id
1695 and tracking_quantity_ind = 'P' );
1696
1697 IF g_error_count = 0 THEN
1698 IF SQL%ROWCOUNT > 0 THEN
1699 g_error_count := 1;
1700 IF l_debug_level > 0 THEN
1701 oe_debug_pub.add( 'PROCESS DUAL 1 THE ERROR COUNT IS SET' ) ;
1702 END IF;
1703 END IF;
1704 END IF;
1705
1706 -- PROCESS DUAL HVOP 3. if secondary UOM is entered,then item must be process and type 1 2 or 3
1707 -- AND secondary UOM must be same as on mtl_system_items
1708
1709
1710 l_msg_text := l_msg_data||
1711 OE_ORDER_UTIL.Get_Attribute_Name('ORDERED_QUANTITY_UOM2');
1712 INSERT INTO OE_PROCESSING_MSGS
1713 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1714 ,line_id ,order_source_id ,original_sys_document_ref
1715 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1716 ,source_document_type_id ,source_document_id ,source_document_line_id
1717 ,attribute_code ,creation_date ,created_by ,last_update_date
1718 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1719 ,program_update_date ,process_activity ,notification_flag ,type
1720 ,message_source_code ,language ,message_text, transaction_id
1721 )
1722 SELECT
1723 request_id ,'LINE',NULL,NULL,NULL,NULL
1724 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1725 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
1726 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1727 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1728 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1729 FROM OE_LINES_IFACE_ALL b
1730 WHERE (order_source_id, orig_sys_document_ref) IN
1731 ( SELECT order_source_id, orig_sys_document_ref
1732 FROM OE_HEADERS_IFACE_ALL
1733 WHERE batch_id = p_batch_id)
1734 AND ( b.ordered_quantity_uom2 IS NOT NULL )
1735 AND NOT EXISTS (
1736 SELECT tracking_quantity_ind
1737 FROM mtl_system_items
1738 WHERE organization_id = b.ship_from_org_id
1739 AND inventory_item_id = b.inventory_item_id
1740 and ( tracking_quantity_ind = 'PS' or
1741 ont_pricing_qty_source = 'S')
1742 and secondary_uom_code = b.ordered_quantity_uom2 );
1743
1744
1745 IF g_error_count = 0 THEN
1746 IF SQL%ROWCOUNT > 0 THEN
1747 g_error_count := 1;
1748 IF l_debug_level > 0 THEN
1749 oe_debug_pub.add( 'PROCESS DUAL 2 THE ERROR COUNT IS SET' ) ;
1750 END IF;
1751 END IF;
1752 END IF;
1753
1754
1755 -- PROCESS DUAL HVOP 4. if item is tracked in primary and secondary or item is priced in secondary
1756 -- then secondary UOM must be enterd -- INVCONV
1757
1758 l_msg_text := l_msg_data||
1759 OE_ORDER_UTIL.Get_Attribute_Name('ORDERED_QUANTITY_UOM2');
1760 INSERT INTO OE_PROCESSING_MSGS
1761 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1762 ,line_id ,order_source_id ,original_sys_document_ref
1763 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1764 ,source_document_type_id ,source_document_id ,source_document_line_id
1765 ,attribute_code ,creation_date ,created_by ,last_update_date
1766 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1767 ,program_update_date ,process_activity ,notification_flag ,type
1768 ,message_source_code ,language ,message_text, transaction_id
1769 )
1770 SELECT
1771 request_id ,'LINE',NULL,NULL,NULL,NULL
1772 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1773 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
1774 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1775 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1776 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1777 FROM OE_LINES_IFACE_ALL b
1778 WHERE (order_source_id, orig_sys_document_ref) IN
1779 ( SELECT order_source_id, orig_sys_document_ref
1780 FROM OE_HEADERS_IFACE_ALL
1781 WHERE batch_id = p_batch_id)
1782 AND ( b.ordered_quantity_uom2 IS NULL)
1783
1784 AND EXISTS (
1785 SELECT tracking_quantity_ind
1786 FROM mtl_system_items
1787 WHERE organization_id = b.ship_from_org_id
1788 AND inventory_item_id = b.inventory_item_id
1789 and ( tracking_quantity_ind = 'PS' or
1790 ont_pricing_qty_source = 'S')
1791 );
1792
1793
1794 IF g_error_count = 0 THEN
1795 IF SQL%ROWCOUNT > 0 THEN
1796 g_error_count := 1;
1797 IF l_debug_level > 0 THEN
1798 oe_debug_pub.add( 'PROCESS DUAL 3 THE ERROR COUNT IS SET' ) ;
1799 END IF;
1800 END IF;
1801 END IF;
1802
1803
1804
1805
1806 -- Process Dual HVOP 4 - need to insert here the pre_process validation from order import for grade -
1807 -- same as in OEXVIMSB.pls
1808 -- validation is preferred_grade; 1 if entered,item must be grade controlled;
1809 -- 2.if entered, grade must exist in mtl_grades
1810
1811 l_msg_text := l_msg_data||
1812 OE_ORDER_UTIL.Get_Attribute_Name('PREFERRED_GRADE');
1813 INSERT INTO OE_PROCESSING_MSGS
1814 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1815 ,line_id ,order_source_id ,original_sys_document_ref
1816 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1817 ,source_document_type_id ,source_document_id ,source_document_line_id
1818 ,attribute_code ,creation_date ,created_by ,last_update_date
1819 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1820 ,program_update_date ,process_activity ,notification_flag ,type
1821 ,message_source_code ,language ,message_text, transaction_id
1822 )
1823 SELECT
1824 request_id ,'LINE',NULL,NULL,NULL,NULL
1825 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
1826 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'PREFERRED_GRADE'
1827 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1828 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1829 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1830 FROM OE_LINES_IFACE_ALL b
1831 WHERE (order_source_id, orig_sys_document_ref) IN
1832 ( SELECT order_source_id, orig_sys_document_ref
1833 FROM OE_HEADERS_IFACE_ALL
1834 WHERE batch_id = p_batch_id)
1835 AND b.preferred_grade IS NOT NULL
1836 AND b.ship_from_org_id IS NOT NULL
1837 AND b.inventory_item_id IS NOT NULL
1838 AND ( NOT EXISTS (
1839 SELECT grade_control_flag
1840 FROM mtl_system_items
1841 WHERE organization_id = b.ship_from_org_id
1842 AND inventory_item_id = b.inventory_item_id
1843 and grade_control_flag = 'Y' )
1844
1845 OR NOT EXISTS (SELECT preferred_grade
1846 FROM mtl_grades_b
1847 WHERE grade_code = b.preferred_grade
1848 ) );
1849
1850
1851 IF g_error_count = 0 THEN
1852 IF SQL%ROWCOUNT > 0 THEN
1853 g_error_count := 1;
1854 IF l_debug_level > 0 THEN
1855 oe_debug_pub.add( 'PROCESS DUAL 4 THE ERROR COUNT IS SET' ) ;
1856 END IF;
1857 END IF;
1858 END IF;
1859
1860
1861 -- INVCONV END IF; -- IF OE_Bulk_Order_PVT.G_PROCESS_INSTALLED_FLAG = 'Y' THEN
1862
1863 -- QUOTING changes - validate trxn phase and version number if >= Pack J
1864 l_msg_text := l_msg_data||
1865 OE_ORDER_UTIL.Get_Attribute_Name('TRANSACTION_PHASE_CODE');
1866 INSERT INTO OE_PROCESSING_MSGS
1867 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1868 ,line_id ,order_source_id ,original_sys_document_ref
1869 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1870 ,source_document_type_id ,source_document_id ,source_document_line_id
1871 ,attribute_code ,creation_date ,created_by ,last_update_date
1872 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1873 ,program_update_date ,process_activity ,notification_flag ,type
1874 ,message_source_code ,language ,message_text, transaction_id
1875 )
1876 SELECT
1877 request_id , 'HEADER',NULL,NULL,NULL,NULL
1878 ,order_source_id ,orig_sys_document_ref ,NULL
1879 ,NULL ,change_sequence,NULL,NULL,NULL,'TRANSACTION_PHASE_CODE'
1880 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1881 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1882 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1883 FROM OE_HEADERS_IFACE_ALL b
1884 WHERE batch_id = p_batch_id and
1885 b.transaction_phase_code IS NOT NULL
1886 -- Only fulfillment orders are supported for HVOP
1887 AND nvl(b.transaction_phase_code,'F') <> 'F'
1888 ;
1889
1890 IF g_error_count = 0 THEN
1891 IF SQL%ROWCOUNT > 0 THEN
1892 g_error_count := 1;
1893 IF l_debug_level > 0 THEN
1894 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1895 END IF;
1896 END IF;
1897 END IF;
1898
1899 l_msg_text := l_msg_data||
1900 OE_ORDER_UTIL.Get_Attribute_Name('VERSION_NUMBER');
1901 INSERT INTO OE_PROCESSING_MSGS
1902 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1903 ,line_id ,order_source_id ,original_sys_document_ref
1904 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1905 ,source_document_type_id ,source_document_id ,source_document_line_id
1906 ,attribute_code ,creation_date ,created_by ,last_update_date
1907 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1908 ,program_update_date ,process_activity ,notification_flag ,type
1909 ,message_source_code ,language ,message_text, transaction_id
1910 )
1911 SELECT
1912 request_id , 'HEADER',NULL,NULL,NULL,NULL
1913 ,order_source_id ,orig_sys_document_ref ,NULL
1914 ,NULL ,change_sequence,NULL,NULL,NULL,'VERSION_NUMBER'
1915 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1916 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
1917 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1918 FROM OE_HEADERS_IFACE_ALL b
1919 WHERE batch_id = p_batch_id and
1920 b.version_number IS NOT NULL
1921 -- Version number cannot be negative or in decimals
1922 AND (b.version_number < 0
1923 OR mod(b.version_number,1) <> 0
1924 )
1925 ;
1926
1927 IF g_error_count = 0 THEN
1928 IF SQL%ROWCOUNT > 0 THEN
1929 g_error_count := 1;
1930 IF l_debug_level > 0 THEN
1931 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1932 END IF;
1933 END IF;
1934 END IF;
1935
1936 END IF; -- IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1937
1938 -------------------------------------------------------------------
1939 -- Attribute Validations for Price Adjustments
1940 -------------------------------------------------------------------
1941
1942 -- Validations for following adjustment columns in Value to ID:
1943 -- List Header ID, List Line ID, List Line Type Code
1944
1945 IF p_adjustments_exist = 'N' THEN
1946 RETURN;
1947 END IF;
1948
1949 l_msg_text := l_msg_data||
1950 OE_ORDER_UTIL.Get_Attribute_Name('APPLIED_FLAG');
1951 INSERT INTO OE_PROCESSING_MSGS
1952 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1953 ,line_id ,order_source_id ,original_sys_document_ref
1954 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1955 ,source_document_type_id ,source_document_id ,source_document_line_id
1956 ,attribute_code ,creation_date ,created_by ,last_update_date
1957 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1958 ,program_update_date ,process_activity ,notification_flag ,type
1959 ,message_source_code ,language ,message_text, transaction_id
1960 )
1961 SELECT
1962 h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
1963 ,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
1964 ,a.change_sequence ,NULL ,NULL ,NULL ,'APPLIED_FLAG'
1965 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
1966 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
1967 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
1968 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
1969 WHERE h.batch_id = p_batch_id
1970 AND a.order_source_id = h.order_source_id
1971 AND a.orig_sys_document_ref = h.orig_sys_document_ref
1972 AND a.APPLIED_FLAG IS NOT NULL
1973 AND a.applied_flag NOT IN ('Y','N');
1974
1975 IF g_error_count = 0 THEN
1976 IF SQL%ROWCOUNT > 0 THEN
1977 g_error_count := 1;
1978 IF l_debug_level > 0 THEN
1979 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
1980 END IF;
1981 END IF;
1982 END IF;
1983
1984
1985 l_msg_text := l_msg_data||
1986 OE_ORDER_UTIL.Get_Attribute_Name('INCLUDE_ON_RETURNS_FLAG');
1987 INSERT INTO OE_PROCESSING_MSGS
1988 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
1989 ,line_id ,order_source_id ,original_sys_document_ref
1990 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
1991 ,source_document_type_id ,source_document_id ,source_document_line_id
1992 ,attribute_code ,creation_date ,created_by ,last_update_date
1993 ,last_updated_by ,last_update_login ,program_application_id ,program_id
1994 ,program_update_date ,process_activity ,notification_flag ,type
1995 ,message_source_code ,language ,message_text, transaction_id
1996 )
1997 SELECT
1998 h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
1999 ,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
2000 ,a.change_sequence ,NULL ,NULL ,NULL ,'INCLUDE_ON_RETURNS_FLAG'
2001 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2002 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
2003 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2004 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
2005 WHERE h.batch_id = p_batch_id
2006 AND a.order_source_id = h.order_source_id
2007 AND a.orig_sys_document_ref = h.orig_sys_document_ref
2008 AND a.INCLUDE_ON_RETURNS_FLAG IS NOT NULL
2009 AND a.include_on_returns_flag NOT IN ('Y','N');
2010
2011 IF g_error_count = 0 THEN
2012 IF SQL%ROWCOUNT > 0 THEN
2013 g_error_count := 1;
2014 IF l_debug_level > 0 THEN
2015 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2016 END IF;
2017 END IF;
2018 END IF;
2019
2020
2021 l_msg_text := l_msg_data||
2022 OE_ORDER_UTIL.Get_Attribute_Name('CREDIT_OR_CHARGE_FLAG');
2023 INSERT INTO OE_PROCESSING_MSGS
2024 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2025 ,line_id ,order_source_id ,original_sys_document_ref
2026 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2027 ,source_document_type_id ,source_document_id ,source_document_line_id
2028 ,attribute_code ,creation_date ,created_by ,last_update_date
2029 ,last_updated_by ,last_update_login ,program_application_id ,program_id
2030 ,program_update_date ,process_activity ,notification_flag ,type
2031 ,message_source_code ,language ,message_text, transaction_id
2032 )
2033 SELECT
2034 h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
2035 ,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
2036 ,a.change_sequence ,NULL ,NULL ,NULL ,'CREDIT_OR_CHARGE_FLAG'
2037 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2038 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
2039 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2040 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
2041 WHERE h.batch_id = p_batch_id
2042 AND a.order_source_id = h.order_source_id
2043 AND a.orig_sys_document_ref = h.orig_sys_document_ref
2044 AND a.CREDIT_OR_CHARGE_FLAG IS NOT NULL
2045 AND NOT EXISTS (SELECT 'Y'
2046 FROM OE_LOOKUPS l
2047 WHERE LOOKUP_TYPE = 'CREDIT_OR_CHARGE_FLAG'
2048 AND LOOKUP_CODE = a.credit_or_charge_flag);
2049
2050 IF g_error_count = 0 THEN
2051 IF SQL%ROWCOUNT > 0 THEN
2052 g_error_count := 1;
2053 IF l_debug_level > 0 THEN
2054 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2055 END IF;
2056 END IF;
2057 END IF;
2058
2059
2060 l_msg_text := l_msg_data||
2061 OE_ORDER_UTIL.Get_Attribute_Name('CHARGE_TYPE_CODE');
2062 INSERT INTO OE_PROCESSING_MSGS
2063 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2064 ,line_id ,order_source_id ,original_sys_document_ref
2065 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2066 ,source_document_type_id ,source_document_id ,source_document_line_id
2067 ,attribute_code ,creation_date ,created_by ,last_update_date
2068 ,last_updated_by ,last_update_login ,program_application_id ,program_id
2069 ,program_update_date ,process_activity ,notification_flag ,type
2070 ,message_source_code ,language ,message_text, transaction_id
2071 )
2072 SELECT
2073 h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
2074 ,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
2075 ,a.change_sequence ,NULL ,NULL ,NULL ,'CHARGE_TYPE_CODE'
2076 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2077 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
2078 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2079 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
2080 WHERE h.batch_id = p_batch_id
2081 AND a.order_source_id = h.order_source_id
2082 AND a.orig_sys_document_ref = h.orig_sys_document_ref
2083 AND a.CHARGE_TYPE_CODE IS NOT NULL
2084 AND NOT EXISTS (SELECT 'Y'FROM
2085 fnd_lookup_values
2086 WHERE LOOKUP_CODE = a.charge_type_code and
2087 LANGUAGE = userenv('LANG') and
2088 VIEW_APPLICATION_ID = 665 and
2089 SECURITY_GROUP_ID = 0 and
2090 LOOKUP_TYPE = 'FREIGHT_COST_TYPE'
2091 );
2092
2093 IF g_error_count = 0 THEN
2094 IF SQL%ROWCOUNT > 0 THEN
2095 g_error_count := 1;
2096 IF l_debug_level > 0 THEN
2097 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2098 END IF;
2099 END IF;
2100 END IF;
2101
2102 l_msg_text := l_msg_data||
2103 OE_ORDER_UTIL.Get_Attribute_Name('LIST_HEADER_ID');
2104 INSERT INTO OE_PROCESSING_MSGS
2105 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2106 ,line_id ,order_source_id ,original_sys_document_ref
2107 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2108 ,source_document_type_id ,source_document_id ,source_document_line_id
2109 ,attribute_code ,creation_date ,created_by ,last_update_date
2110 ,last_updated_by ,last_update_login ,program_application_id ,program_id
2111 ,program_update_date ,process_activity ,notification_flag ,type
2112 ,message_source_code ,language ,message_text, transaction_id
2113 )
2114 SELECT
2115 h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
2116 ,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
2117 ,a.change_sequence ,NULL ,NULL ,NULL ,'LIST_HEADER_ID'
2118 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2119 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
2120 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2121 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
2122 WHERE h.batch_id = p_batch_id
2123 AND a.order_source_id = h.order_source_id
2124 AND a.orig_sys_document_ref = h.orig_sys_document_ref
2125 AND a.list_header_id IS NOT NULL
2126 AND NOT EXISTS (SELECT 'Y'
2127 FROM QP_LIST_HEADERS
2128 WHERE LIST_HEADER_ID = a.list_header_id
2129 );
2130
2131 IF g_error_count = 0 THEN
2132 IF SQL%ROWCOUNT > 0 THEN
2133 g_error_count := 1;
2134 IF l_debug_level > 0 THEN
2135 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2136 END IF;
2137 END IF;
2138 END IF;
2139
2140 l_msg_text := l_msg_data||
2141 OE_ORDER_UTIL.Get_Attribute_Name('LIST_LINE_ID');
2142 INSERT INTO OE_PROCESSING_MSGS
2143 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2144 ,line_id ,order_source_id ,original_sys_document_ref
2145 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2146 ,source_document_type_id ,source_document_id ,source_document_line_id
2147 ,attribute_code ,creation_date ,created_by ,last_update_date
2148 ,last_updated_by ,last_update_login ,program_application_id ,program_id
2149 ,program_update_date ,process_activity ,notification_flag ,type
2150 ,message_source_code ,language ,message_text, transaction_id
2151 )
2152 SELECT
2153 h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
2154 ,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
2155 ,a.change_sequence ,NULL ,NULL ,NULL ,'LIST_LINE_ID'
2156 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2157 ,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
2158 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2159 FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
2160 WHERE h.batch_id = p_batch_id
2161 AND a.order_source_id = h.order_source_id
2162 AND a.orig_sys_document_ref = h.orig_sys_document_ref
2163 AND a.list_line_id IS NOT NULL
2164 AND NOT EXISTS (SELECT 'Y'
2165 FROM QP_LIST_LINES
2166 WHERE LIST_LINE_ID = a.list_line_id
2167 AND LIST_HEADER_ID = a.list_header_id
2168 );
2169
2170 IF g_error_count = 0 THEN
2171 IF SQL%ROWCOUNT > 0 THEN
2172 g_error_count := 1;
2173 IF l_debug_level > 0 THEN
2174 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2175 END IF;
2176 END IF;
2177 END IF;
2178
2179 --{bug 5054618
2180 -- End Customer Change
2181 l_msg_text := l_msg_data||
2182 OE_ORDER_UTIL.Get_Attribute_Name('END_CUSTOMER_ID');
2183 INSERT INTO OE_PROCESSING_MSGS
2184 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2185 ,line_id ,order_source_id ,original_sys_document_ref
2186 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2187 ,source_document_type_id ,source_document_id ,source_document_line_id
2188 ,attribute_code ,creation_date ,created_by ,last_update_date
2189 ,last_updated_by ,last_update_login ,program_application_id ,program_id
2190 ,program_update_date ,process_activity ,notification_flag ,type
2191 ,message_source_code ,language ,message_text, transaction_id
2192 )
2193 SELECT
2194 request_id , 'HEADER',NULL,NULL,NULL,NULL
2195 ,order_source_id ,orig_sys_document_ref ,NULL
2196 ,NULL ,change_sequence,NULL,NULL,NULL,'END_CUSTOMER'
2197 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2198 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
2199 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2200 FROM OE_HEADERS_IFACE_ALL b
2201 WHERE batch_id = p_batch_id and
2202 b.sold_to_org_id IS NOT NULL and
2203 NOT EXISTS (select ORGANIZATION_ID
2204 FROM OE_SOLD_TO_ORGS_V
2205 WHERE ORGANIZATION_ID = b.sold_to_org_id
2206 AND STATUS = 'A'
2207 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
2208 AND NVL(END_DATE_ACTIVE, SYSDATE))
2209 ;
2210
2211 IF g_error_count = 0 THEN
2212 IF SQL%ROWCOUNT > 0 THEN
2213 g_error_count := 1;
2214 IF l_debug_level > 0 THEN
2215 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2216 END IF;
2217 END IF;
2218 END IF;
2219
2220 INSERT INTO OE_PROCESSING_MSGS
2221 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id
2222 ,line_id ,order_source_id ,original_sys_document_ref
2223 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2224 ,source_document_type_id ,source_document_id ,source_document_line_id
2225 ,attribute_code ,creation_date ,created_by ,last_update_date
2226 ,last_updated_by ,last_update_login ,program_application_id ,program_id
2227 ,program_update_date ,process_activity ,notification_flag ,type
2228 ,message_source_code ,language ,message_text, transaction_id
2229 )
2230 SELECT
2231 request_id ,'LINE',NULL,NULL,NULL,NULL
2232 ,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
2233 ,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'END_CUSTOMER_ID'
2234 ,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
2235 ,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
2236 ,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
2237 FROM OE_LINES_IFACE_ALL b
2238 WHERE (order_source_id, orig_sys_document_ref) IN
2239 ( SELECT order_source_id, orig_sys_document_ref
2240 FROM OE_HEADERS_IFACE_ALL
2241 WHERE batch_id = p_batch_id)
2242 AND b.sold_to_org_id IS NOT NULL and
2243 NOT EXISTS (select ORGANIZATION_ID
2244 FROM OE_SOLD_TO_ORGS_V
2245 WHERE ORGANIZATION_ID = b.sold_to_org_id
2246 AND STATUS = 'A'
2247 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
2248 AND NVL(END_DATE_ACTIVE, SYSDATE));
2249
2250
2251
2252 IF g_error_count = 0 THEN
2253 IF SQL%ROWCOUNT > 0 THEN
2254 g_error_count := 1;
2255 IF l_debug_level > 0 THEN
2256 oe_debug_pub.add( 'THE ERROR COUNT IS SET' ) ;
2257 END IF;
2258 END IF;
2259 END IF;
2260
2261 --bug 5054618}
2262
2263 IF l_debug_level > 0 THEN
2264 oe_debug_pub.add( 'EXIT ATTRIBUTES , G_ERROR_COUNT: '||G_ERROR_COUNT ) ;
2265 END IF;
2266 EXCEPTION
2267 WHEN OTHERS THEN
2268 IF l_debug_level > 0 THEN
2269 oe_debug_pub.add( 'OTHERS ERROR , VALIDATE.ATTRIBUTES' ) ;
2270 END IF;
2271 IF l_debug_level > 0 THEN
2272 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
2273 END IF;
2274 OE_BULK_MSG_PUB.ADD_Exc_Msg
2275 ( G_PKG_NAME
2276 , 'Attributes'
2277 );
2278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2279 END Attributes;
2280
2281
2282 ---------------------------------------------------------------------
2283 --
2284 -- PROCEDURE Validate_BOM
2285 --
2286 ---------------------------------------------------------------------
2287
2288 PROCEDURE Validate_BOM
2289
2290 IS
2291 l_msg_text VARCHAR2(2000);
2292
2293 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2294
2295 BEGIN
2296 IF l_debug_level > 0 THEN
2297 oe_debug_pub.add( 'ENTERING OE_BULK_VALIDATE.Validate_BOM ') ;
2298 END IF;
2299
2300
2301 -- Check if parent exists
2302
2303 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_BULK_CONFIG_MISS_PARENT');
2304
2305 INSERT INTO OE_PROCESSING_MSGS
2306 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
2307 ,order_source_id ,original_sys_document_ref
2308 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2309 ,attribute_code ,creation_date ,created_by ,last_update_date
2310 ,last_updated_by ,last_update_login
2311 ,program_application_id ,program_id,program_update_date
2312 ,process_activity ,notification_flag ,type
2313 ,message_source_code ,language
2314 ,message_text, transaction_id
2315 )
2316 SELECT
2317 OE_BULK_ORDER_PVT.G_REQUEST_ID,'LINE' ,NULL,NULL ,NULL ,l.line_id
2318 ,l.order_source_id ,l.orig_sys_document_ref
2319 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
2320 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
2321 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
2322 ,660 ,NULL ,NULL
2323 ,NULL ,NULL ,NULL
2324 ,'C' ,USERENV('LANG')
2325 ,l_msg_text || ' '|| l.ordered_item,
2326 OE_MSG_ID_S.NEXTVAL
2327 FROM oe_config_details_tmp L
2328 WHERE NVL(L.item_type_code, 'XXX') <> 'MODEL'
2329 AND NOT EXISTS (select L2.line_id
2330 From oe_config_details_tmp L2
2331 WHERE L2.component_code =
2332 substr(L.component_code,1,instr(L.component_code,'-',-1,1)-1)
2333 AND L2.top_model_line_id = L.top_model_line_id);
2334
2335
2336 IF OE_BULK_VALIDATE.g_error_count = 0 THEN
2337 IF SQL%ROWCOUNT > 0 THEN
2338 OE_BULK_VALIDATE.g_error_count := 1;
2339 END IF;
2340 END IF;
2341
2342
2343
2344 -- Check for min max quantities
2345
2346 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_BULK_CONFIG_QTY_RANGE');
2347
2348 INSERT INTO OE_PROCESSING_MSGS
2349 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
2350 ,order_source_id ,original_sys_document_ref
2351 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2352 ,attribute_code ,creation_date ,created_by ,last_update_date
2353 ,last_updated_by ,last_update_login
2354 ,program_application_id ,program_id,program_update_date
2355 ,process_activity ,notification_flag ,type
2356 ,message_source_code ,language
2357 ,message_text, transaction_id
2358 )
2359 SELECT
2360 OE_BULK_ORDER_PVT.G_REQUEST_ID ,'LINE' ,NULL,NULL ,NULL ,l.line_id
2361 ,l.order_source_id ,l.orig_sys_document_ref
2362 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
2363 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
2364 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
2365 ,660 ,NULL ,NULL
2366 ,NULL ,NULL ,NULL
2367 ,'C' ,USERENV('LANG')
2368 ,l_msg_text ||' '||l.ordered_item || '( '||
2369 l.low_quantity*L2.ordered_quantity
2370 || ' -> '|| l.high_quantity *L2.ordered_quantity || ')',
2371 OE_MSG_ID_S.NEXTVAL
2372 FROM oe_config_details_tmp L,
2373 oe_config_details_tmp L2
2374 WHERE L2.item_type_code = 'MODEL'
2375 AND L.top_model_line_id = L2.line_id
2376 AND L.ordered_quantity > 0
2377 AND L2.ordered_quantity > 0
2378 AND ( TRUNC(L.ordered_quantity/L2.ordered_quantity) < L.low_quantity OR
2379 TRUNC(L.ordered_quantity/L2.ordered_quantity) > L.high_quantity );
2380
2381 IF OE_BULK_VALIDATE.g_error_count = 0 THEN
2382 IF SQL%ROWCOUNT > 0 THEN
2383 OE_BULK_VALIDATE.g_error_count := 1;
2384 END IF;
2385 END IF;
2386
2387
2388
2389 -- Check for class has options
2390 l_msg_text := FND_MESSAGE.GET_STRING('ONT','OE_BULK_CONFIG_CLS_NO_OPTION');
2391
2392 INSERT INTO OE_PROCESSING_MSGS
2393 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
2394 ,order_source_id ,original_sys_document_ref
2395 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2396 ,attribute_code ,creation_date ,created_by ,last_update_date
2397 ,last_updated_by ,last_update_login
2398 ,program_application_id ,program_id,program_update_date
2399 ,process_activity ,notification_flag ,type
2400 ,message_source_code ,language
2401 ,message_text, transaction_id
2402 )
2403 SELECT
2404 OE_BULK_ORDER_PVT.G_REQUEST_ID, 'LINE' ,NULL,NULL ,NULL ,l.line_id
2405 ,l.order_source_id ,l.orig_sys_document_ref
2406 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
2407 ,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
2408 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
2409 ,660 ,NULL ,NULL
2410 ,NULL ,NULL ,NULL
2411 ,'C' ,USERENV('LANG')
2412 ,l_msg_text || ' '||l.ordered_item
2413 ,OE_MSG_ID_S.NEXTVAL
2414 FROM oe_config_details_tmp L
2415 WHERE L.BOM_ITEM_TYPE = 2
2416 AND L.ordered_quantity > 0
2417 AND NOT EXISTS ( -- fix bug 5687771
2418 select 1
2419 from oe_config_details_tmp L2
2420 where L2.top_model_line_id = L.top_model_line_id
2421 and substr(L2.component_code, 1, instr(l2.component_code,'-',-1)-1) =
2422 L.component_code );
2423
2424
2425 IF OE_BULK_VALIDATE.g_error_count = 0 THEN
2426 IF SQL%ROWCOUNT > 0 THEN
2427 OE_BULK_VALIDATE.g_error_count := 1;
2428 END IF;
2429 END IF;
2430
2431 -- Check for mutually exclusive options
2432
2433 -- existing message can used without specifying the token.
2434 l_msg_text := FND_MESSAGE.GET_STRING('ONT', 'OE_BULK_CONFIG_EXCLUSIVE_CLS');
2435
2436 INSERT INTO OE_PROCESSING_MSGS
2437 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
2438 ,order_source_id ,original_sys_document_ref
2439 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2440 ,attribute_code ,creation_date ,created_by ,last_update_date
2441 ,last_updated_by ,last_update_login
2442 ,program_application_id ,program_id,program_update_date
2443 ,process_activity ,notification_flag ,type
2444 ,message_source_code ,language
2445 ,message_text, transaction_id
2446 )
2447 SELECT
2448 OE_BULK_ORDER_PVT.G_REQUEST_ID,'LINE' ,NULL,NULL ,NULL ,l.line_id
2449 ,l.order_source_id ,l.orig_sys_document_ref
2450 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
2451 ,NULL, sysdate, FND_GLOBAL.USER_ID , sysdate
2452 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
2453 ,660 ,NULL ,NULL
2454 ,NULL ,NULL ,NULL
2455 ,'C' ,USERENV('LANG')
2456 ,l_msg_text || ' '||l.ordered_item
2457 ,OE_MSG_ID_S.NEXTVAL
2458 FROM oe_config_details_tmp L
2459 WHERE L.ITEM_TYPE_CODE in ('CLASS','MODEL')
2460 AND L.mutually_exclusive_options = 1
2461 AND 2 <= ( select count(line_id)
2462 From oe_config_details_tmp L2
2463 where --BUG 4586356 L2.item_type_code = 'OPTION'
2464 L.component_code = substr(L2.component_code, 1,
2465 instr(l2.component_code,'-',-1)-1)
2466 and L2.top_model_line_id = L.top_model_line_id);
2467
2468 IF OE_BULK_VALIDATE.g_error_count = 0 THEN
2469 IF SQL%ROWCOUNT > 0 THEN
2470 OE_BULK_VALIDATE.g_error_count := 1;
2471 END IF;
2472 END IF;
2473
2474 -- Check for mandatory classes
2475
2476 l_msg_text := FND_MESSAGE.GET_STRING('ONT', 'OE_BULK_CONFIG_MANDATORY_CLS');
2477
2478 INSERT INTO OE_PROCESSING_MSGS
2479 ( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
2480 ,order_source_id ,original_sys_document_ref
2481 ,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
2482 ,attribute_code ,creation_date ,created_by ,last_update_date
2483 ,last_updated_by ,last_update_login
2484 ,program_application_id ,program_id,program_update_date
2485 ,process_activity ,notification_flag ,type
2486 ,message_source_code ,language
2487 ,message_text, transaction_id
2488 )
2489 SELECT
2490 OE_BULK_ORDER_PVT.G_REQUEST_ID,'LINE' ,NULL,NULL ,NULL ,l.line_id
2491 ,l.order_source_id ,l.orig_sys_document_ref
2492 ,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
2493 ,NULL, sysdate, FND_GLOBAL.USER_ID , sysdate
2494 ,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
2495 ,660 ,NULL ,NULL
2496 ,NULL ,NULL ,NULL
2497 ,'C' ,USERENV('LANG')
2498 ,l_msg_text || ' '||l.ordered_item
2499 , OE_MSG_ID_S.NEXTVAL
2500 FROM oe_config_details_tmp L
2501 WHERE L.ITEM_TYPE_CODE = 'MODEL'
2502 AND EXISTS (select b2.component_code
2503 From bom_explosions b2
2504 Where b2.explosion_type = 'OPTIONAL'
2505 AND b2.top_bill_sequence_id = L.top_bill_sequence_id
2506 AND b2.plan_level >= 0
2507 AND b2.effectivity_date <= sysdate
2508 AND b2.disable_date > sysdate
2509 AND b2.bom_item_type IN ( 1, 2 ) -- Model, Class
2510 AND b2.optional = 2
2511 AND b2.component_code NOT IN (
2512 select component_code
2513 From oe_config_details_tmp L2
2514 Where L.top_model_line_id =
2515 L2.top_model_line_id)
2516 AND
2517 SUBSTR(B2.COMPONENT_CODE,1,INSTR(B2.COMPONENT_CODE,'-',-1,1)-1)
2518 IN ( select component_code
2519 From oe_config_details_tmp L3
2520 Where L.top_model_line_id =
2521 L3.top_model_line_id) );
2522
2523 IF OE_BULK_VALIDATE.g_error_count = 0 THEN
2524 IF SQL%ROWCOUNT > 0 THEN
2525 OE_BULK_VALIDATE.g_error_count := 1;
2526 END IF;
2527 END IF;
2528
2529
2530 IF l_debug_level > 0 THEN
2531 oe_debug_pub.add('LEAVING OE_BULK_VALIDATE.Validate_BOM' , 1);
2532 END IF;
2533
2534 EXCEPTION
2535 WHEN OTHERS THEN
2536 oe_debug_pub.add('Others Error, Validate_BOM');
2537 oe_debug_pub.add(substr(sqlerrm,1,240));
2538 OE_BULK_MSG_PUB.Add_Exc_Msg
2539 ( G_PKG_NAME,
2540 'Validate_BOM'
2541 );
2542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2543
2544 END Validate_BOM;
2545
2546
2547
2548 ---------------------------------------------------------------------
2549 -- PROCEDURE Mark_Interface_Error
2550 --
2551 -- This procedure sets error_flag on order header interface table
2552 -- if any entity of this order (header, line, adjustments etc.)
2553 -- fail pre-processing checks or attribute validation.
2554 ---------------------------------------------------------------------
2555
2556 PROCEDURE MARK_INTERFACE_ERROR(p_batch_id NUMBER)
2557 IS
2558 --
2559 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2560 --
2561 BEGIN
2562
2563 -- set the error flag for the configuration pre_process failure
2564 BEGIN
2565 UPDATE OE_HEADERS_IFACE_ALL h
2566 SET ERROR_FLAG = 'Y'
2567 ,ATTRIBUTE_STATUS = NULL
2568 WHERE batch_id = p_batch_id
2569 AND nvl(ERROR_FLAG, 'N') = 'N'
2570 AND EXISTS
2571 (SELECT 'Y'
2572 FROM OE_CONFIG_DETAILS_TMP t
2573 WHERE t.orig_sys_document_ref = h.ORIG_SYS_DOCUMENT_REF
2574 AND t.order_source_id = h.order_source_id
2575 AND nvl(t.lock_control, 0) = -99);
2576 EXCEPTION
2577 WHEN NO_DATA_FOUND THEN
2578 NULL;
2579 END;
2580
2581 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS :=
2582 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS + SQL%ROWCOUNT;
2583
2584 IF g_error_count >= 1 THEN
2585
2586 UPDATE OE_HEADERS_IFACE_ALL a
2587 SET ERROR_FLAG = 'Y'
2588 ,ATTRIBUTE_STATUS = NULL
2589 WHERE batch_id = p_batch_id
2590 AND nvl(ERROR_FLAG, 'N') = 'N'
2591 AND EXISTS
2592 (SELECT original_sys_document_line_ref
2593 FROM OE_PROCESSING_MSGS b
2594 WHERE b.original_sys_document_ref = a.ORIG_SYS_DOCUMENT_REF
2595 AND b.order_source_id = a.order_source_id
2596 AND b.request_id = OE_BULK_ORDER_PVT.G_REQUEST_ID); -- Added for Bug 6671781
2597
2598
2599 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS :=
2600 OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS + SQL%ROWCOUNT;
2601
2602
2603 BEGIN
2604
2605 UPDATE OE_LINES_IFACE_ALL c
2606 SET ATTRIBUTE_STATUS = NULL
2607 WHERE (order_source_id, orig_sys_document_ref) IN
2608 ( SELECT a.order_source_id, a.orig_sys_document_ref
2609 FROM OE_HEADERS_IFACE_ALL a, OE_PROCESSING_MSGS b
2610 WHERE a.batch_id = p_batch_id
2611 AND b.original_sys_document_ref = a.ORIG_SYS_DOCUMENT_REF
2612 AND b.order_source_id = a.order_source_id
2613 AND b.request_id = OE_BULK_ORDER_PVT.G_REQUEST_ID);--Added for bug 6830039
2614 EXCEPTION
2615 WHEN NO_DATA_FOUND THEN
2616 NULL;
2617 END;
2618
2619 ----------------------------------------
2620 --ADDED BY UMA ON 7/18/02
2621 ----------------------------------------
2622
2623 --OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS :=
2624 -- OE_BULK_ORDER_IMPORT_PVT.G_ERROR_ORDERS + SQL%ROWCOUNT;
2625
2626 END IF;
2627
2628 EXCEPTION
2629 WHEN OTHERS THEN
2630 IF l_debug_level > 0 THEN
2631 oe_debug_pub.add( 'OTHERS ERROR , MARK_INTERFACE_ERROR' ) ;
2632 END IF;
2633 IF l_debug_level > 0 THEN
2634 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
2635 END IF;
2636 OE_BULK_MSG_PUB.ADD_Exc_Msg
2637 ( G_PKG_NAME
2638 , 'MARK_INTERFACE_ERROR'
2639 );
2640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641 END MARK_INTERFACE_ERROR;
2642
2643 END OE_BULK_VALIDATE;