[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_PRICE_PVT
Source
1 PACKAGE BODY OE_BULK_PRICE_PVT AS
2 /* $Header: OEBVPRCB.pls 120.2.12010000.5 2009/01/08 15:45:40 smanian ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_PRICE_PVT';
5
6 PROCEDURE mark_header_error(p_header_index IN NUMBER,
7 p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE);
8
9 PROCEDURE Booking_Failed(p_index IN NUMBER, --bug 4558078
10 p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE);
11
12 ---------------------------------------------------------------------
13 -- PROCEDURE Insert_Adjustments
14 --
15 -- Inserts manual price adjustments for this bulk import batch,
16 -- from interface tables into oe_price_adjustments table.
17 -- This API should be called before Price_Orders to ensure that
18 -- manual adjustments are applied when pricing the order.
19 ---------------------------------------------------------------------
20
21 PROCEDURE Insert_Adjustments
22 (p_batch_id IN NUMBER
23 ,x_return_status OUT NOCOPY VARCHAR2)
24
25 IS
26 --
27 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
28 --
29 BEGIN
30
31 x_return_status := FND_API.G_RET_STS_SUCCESS;
32
33 INSERT INTO OE_PRICE_ADJUSTMENTS
34 (PRICE_ADJUSTMENT_ID
35 ,CREATION_DATE
36 ,CREATED_BY
37 ,LAST_UPDATE_DATE
38 ,LAST_UPDATED_BY
39 ,LAST_UPDATE_LOGIN
40 ,PROGRAM_APPLICATION_ID
41 ,PROGRAM_ID
42 ,PROGRAM_UPDATE_DATE
43 ,REQUEST_ID
44 ,HEADER_ID
45 ,DISCOUNT_ID
46 ,DISCOUNT_LINE_ID
47 ,AUTOMATIC_FLAG
48 ,PERCENT
49 ,LINE_ID
50 ,CONTEXT
51 ,ATTRIBUTE1
52 ,ATTRIBUTE2
53 ,ATTRIBUTE3
54 ,ATTRIBUTE4
55 ,ATTRIBUTE5
56 ,ATTRIBUTE6
57 ,ATTRIBUTE7
58 ,ATTRIBUTE8
59 ,ATTRIBUTE9
60 ,ATTRIBUTE10
61 ,ATTRIBUTE11
62 ,ATTRIBUTE12
63 ,ATTRIBUTE13
64 ,ATTRIBUTE14
65 ,ATTRIBUTE15
66 ,ORIG_SYS_DISCOUNT_REF
67 ,LIST_HEADER_ID
68 ,LIST_LINE_ID
69 ,LIST_LINE_TYPE_CODE
70 ,MODIFIER_MECHANISM_TYPE_CODE
71 ,MODIFIED_FROM
72 ,MODIFIED_TO
73 ,UPDATED_FLAG
74 ,UPDATE_ALLOWED
75 ,APPLIED_FLAG
76 ,CHANGE_REASON_CODE
77 ,CHANGE_REASON_TEXT
78 ,operand
79 ,Arithmetic_operator
80 ,COST_ID
81 ,TAX_CODE
82 ,TAX_EXEMPT_FLAG
83 ,TAX_EXEMPT_NUMBER
84 ,TAX_EXEMPT_REASON_CODE
85 ,PARENT_ADJUSTMENT_ID
86 ,INVOICED_FLAG
87 ,ESTIMATED_FLAG
88 ,INC_IN_SALES_PERFORMANCE
89 ,SPLIT_ACTION_CODE
90 ,ADJUSTED_AMOUNT
91 ,PRICING_PHASE_ID
92 ,CHARGE_TYPE_CODE
93 ,CHARGE_SUBTYPE_CODE
94 ,list_line_no
95 ,source_system_code
96 ,benefit_qty
97 ,benefit_uom_code
98 ,print_on_invoice_flag
99 ,expiration_date
100 ,rebate_transaction_type_code
101 ,rebate_transaction_reference
102 ,rebate_payment_system_code
103 ,redeemed_date
104 ,redeemed_flag
105 ,accrual_flag
106 ,range_break_quantity
107 ,accrual_conversion_rate
108 ,pricing_group_sequence
109 ,modifier_level_code
110 ,price_break_type_code
111 ,substitution_attribute
112 ,proration_type_code
113 ,CREDIT_OR_CHARGE_FLAG
114 ,INCLUDE_ON_RETURNS_FLAG
115 ,AC_CONTEXT
116 ,AC_ATTRIBUTE1
117 ,AC_ATTRIBUTE2
118 ,AC_ATTRIBUTE3
119 ,AC_ATTRIBUTE4
120 ,AC_ATTRIBUTE5
121 ,AC_ATTRIBUTE6
122 ,AC_ATTRIBUTE7
123 ,AC_ATTRIBUTE8
124 ,AC_ATTRIBUTE9
125 ,AC_ATTRIBUTE10
126 ,AC_ATTRIBUTE11
127 ,AC_ATTRIBUTE12
128 ,AC_ATTRIBUTE13
129 ,AC_ATTRIBUTE14
130 ,AC_ATTRIBUTE15
131 ,OPERAND_PER_PQTY
132 ,ADJUSTED_AMOUNT_PER_PQTY
133 ,LOCK_CONTROL
134 )
135 SELECT
136 OE_PRICE_ADJUSTMENTS_S.NEXTVAL
137 ,sysdate
138 ,FND_GLOBAL.USER_ID
139 ,sysdate
140 ,FND_GLOBAL.USER_ID
141 ,NULL
142 ,a.program_application_id
143 ,a.program_id
144 ,a.program_update_date
145 ,a.request_id
146 ,h.header_id
147 ,a.discount_id
148 ,a.discount_line_id
149 ,nvl(a.automatic_flag,ll.automatic_flag)
150 ,a.percent
151 ,l.line_id
152 ,a.context
153 ,a.attribute1
154 ,a.attribute2
155 ,a.attribute3
156 ,a.attribute4
157 ,a.attribute5
158 ,a.attribute6
159 ,a.attribute7
160 ,a.attribute8
161 ,a.attribute9
162 ,a.attribute10
163 ,a.attribute11
164 ,a.attribute12
165 ,a.attribute13
166 ,a.attribute14
167 ,a.attribute15
168 ,a.orig_sys_discount_ref
169 ,a.LIST_HEADER_ID
170 ,a.LIST_LINE_ID
171 ,nvl(a.LIST_LINE_TYPE_CODE,ll.list_line_type_code)
172 ,a.MODIFIER_MECHANISM_TYPE_CODE
173 ,a.MODIFIED_FROM
174 ,a.MODIFIED_TO
175 ,a.UPDATED_FLAG
176 ,nvl(a.UPDATE_ALLOWED,ll.override_flag)
177 ,a.APPLIED_FLAG
178 ,a.CHANGE_REASON_CODE
179 ,a.CHANGE_REASON_TEXT
180 ,nvl(a.operand,ll.operand)
181 ,nvl(a.arithmetic_operator,ll.arithmetic_operator)
182 ,a.COST_ID
183 ,a.TAX_CODE
184 ,NULL -- a.TAX_EXEMPT_FLAG
185 ,NULL -- a.TAX_EXEMPT_NUMBER
186 ,NULL -- a.TAX_EXEMPT_REASON_CODE
187 ,a.PARENT_ADJUSTMENT_ID
188 ,a.INVOICED_FLAG
189 ,nvl(a.ESTIMATED_FLAG
190 ,decode(ll.list_line_type_code,'FREIGHT_CHARGE','Y',NULL))
191 ,a.INC_IN_SALES_PERFORMANCE
192 ,NULL -- a.SPLIT_ACTION_CODE
193 ,a.ADJUSTED_AMOUNT
194 ,nvl(a.PRICING_PHASE_ID,ll.Pricing_phase_id)
195 ,nvl(a.CHARGE_TYPE_CODE,ll.charge_type_code)
196 ,nvl(a.CHARGE_SUBTYPE_CODE,ll.charge_subtype_code)
197 ,nvl(a.list_line_number,ll.list_line_no)
198 ,lh.source_system_code
199 ,ll.benefit_qty
200 ,ll.benefit_uom_code
201 ,ll.print_on_invoice_flag
202 ,ll.expiration_date
203 ,ll.rebate_transaction_type_code
204 ,NULL -- a.rebate_transaction_reference
205 ,NULL -- a.rebate_payment_system_code
206 ,NULL -- a.redeemed_date
207 ,NULL -- a.redeemed_flag
208 ,ll.accrual_flag
209 ,NULL -- a.range_break_quantity
210 ,ll.accrual_conversion_rate
211 ,ll.pricing_group_sequence
212 ,ll.modifier_level_code
213 ,ll.price_break_type_code
214 ,ll.substitution_attribute
215 ,ll.proration_type_code
216 ,a.credit_or_charge_flag
217 ,nvl(a.include_on_returns_flag,ll.include_on_returns_flag)
218 ,a.ac_context
219 ,a.ac_attribute1
220 ,a.ac_attribute2
221 ,a.ac_attribute3
222 ,a.ac_attribute4
223 ,a.ac_attribute5
224 ,a.ac_attribute6
225 ,a.ac_attribute7
226 ,a.ac_attribute8
227 ,a.ac_attribute9
228 ,a.ac_attribute10
229 ,a.ac_attribute11
230 ,a.ac_attribute12
231 ,a.ac_attribute13
232 ,a.ac_attribute14
233 ,a.ac_attribute15
234 ,nvl(a.OPERAND_PER_PQTY,ll.operand)
235 ,a.ADJUSTED_AMOUNT_PER_PQTY
236 ,1
237 FROM OE_PRICE_ADJS_IFACE_ALL a
238 , OE_ORDER_HEADERS h
239 , QP_LIST_HEADERS lh
240 , QP_LIST_LINES ll
241 , OE_ORDER_LINES_ALL l -- Changes for SQL Id 14876372
242 WHERE h.batch_id = p_batch_id
243 AND a.order_source_id = h.order_source_id
244 AND a.orig_sys_document_ref = h.orig_sys_document_ref
245 AND lh.list_header_id = a.list_header_id
246 AND ll.list_line_id = a.list_line_id
247 AND l.order_source_id(+) = a.order_source_id
248 AND l.orig_sys_document_ref(+) = a.orig_sys_document_ref
249 AND l.orig_sys_line_ref(+) = a.orig_sys_line_ref
250 ;
251
252 -- Cannot have OR with outer join operator, in any case - BULK
253 -- does not support shipment line creation!
254 -- OR l.orig_sys_shipment_ref(+) = a.orig_sys_shipment_ref
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 IF l_debug_level > 0 THEN
259 oe_debug_pub.add( 'OTHERS ERROR , INSERT_ADJUSTMENTS' ) ;
260 END IF;
261 IF l_debug_level > 0 THEN
262 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
263 END IF;
264 OE_BULK_MSG_PUB.Add_Exc_Msg
265 (G_PKG_NAME
266 ,'Insert_Adjustments');
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 END Insert_Adjustments;
269
270
271 ---------------------------------------------------------------------
272 -- PROCEDURE Price_Orders
273 --
274 -- Pricing for all orders in this batch. Currently, this API is NOT
275 -- BULK enabled and calls pricing integration API (oe_order_adj_pvt)
276 -- to price order by order. The integration API directly updates the
277 -- pricing fields on order lines table.
278 -- IN parameter -
279 -- p_header_rec: order headers in the batch
280 -- Modifying this procedure to do the credit checking for Orders in
281 -- a batch.
282 ---------------------------------------------------------------------
283
284 PROCEDURE Price_Orders
285 (p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
286 ,p_process_tax IN VARCHAR2 DEFAULT 'N'
287 ,x_return_status OUT NOCOPY VARCHAR2
288
289 )
290 IS
291 l_price_control_rec QP_PREQ_GRP.control_record_type;
292 l_request_rec oe_order_pub.request_rec_type;
293 l_line_tbl oe_order_pub.line_tbl_type;
294 l_multiple_events VARCHAR2(1);
295 l_book_failed VARCHAR2(1);
296 l_header_id NUMBER;
297 l_header_count NUMBER := p_header_rec.HEADER_ID.COUNT;
298 I NUMBER;
299 l_ec_installed VARCHAR2(1);
300 l_index NUMBER;
301 l_start_index NUMBER := 1;
302 --bug 4558078
303 l_msg_count NUMBER;
304 l_msg_data VARCHAR2(2000);
305 l_return_status VARCHAR2(1);
306
307 CURSOR c_price_attributes(l_header_id NUMBER) IS
308 SELECT line_id
309 ,price_list_id
310 ,unit_list_price
311 ,unit_selling_price
312 FROM OE_ORDER_LINES l
313 WHERE l.header_id = l_header_id;
314 --
315 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
316 --
317 BEGIN
318
319 x_return_status := FND_API.G_RET_STS_SUCCESS;
320
321 -- Set this global so that pricing integration will not fire security
322 -- checks for update of pricing fields back on order lines.
323 OE_GLOBALS.G_HEADER_CREATED := TRUE;
324
325 -- Set EDI installed status to 'N' so that acknowledgment records are
326 -- not created in the pricing call. These will be created via the
327 -- bulk acknowledgments API (OEBVACKB.pls) later during bulk import.
328 l_ec_installed := OE_GLOBALS.G_EC_INSTALLED;
329 OE_GLOBALS.G_EC_INSTALLED := 'N';
330
331 -- added for HVOP Tax project
332 IF p_process_tax = 'N' THEN
333 FOR I IN 1..l_header_count LOOP
334
335 l_header_id := p_header_rec.header_id(i);
336 l_book_failed := 'N'; --bug 4558078
337
338 IF l_debug_level > 0 THEN
339 oe_debug_pub.add( 'HEADER ID:'||L_HEADER_ID ) ;
340 END IF;
341
342 IF nvl(p_header_rec.lock_control(i),0) <> -99 AND
343 nvl(p_header_rec.lock_control(i),0) <> -98 AND
344 nvl(p_header_rec.lock_control(i),0) <> -97
345 THEN
346
347 IF l_debug_level > 0 THEN
348 oe_debug_pub.add( 'PRICE ORDER , HEADER ID:'||L_HEADER_ID ) ;
349 END IF;
350
351 IF OE_BULK_ORDER_PVT.G_PRICING_NEEDED = 'Y' THEN --bug 455807
352
353 IF p_header_rec.booked_flag(i) = 'Y' THEN
354 l_multiple_events := 'Y';
355 l_price_control_rec.pricing_event := 'BATCH,BOOK';
356 ELSE
357 l_multiple_events := 'N';
358 l_price_control_rec.pricing_event := 'BATCH';
359 END IF;
360
361 l_Price_Control_Rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
362 l_Price_Control_Rec.Simulation_Flag := 'N';
363
364
365 -- Changes for bug 4180619
366 BEGIN
367
368 OE_Order_Adj_Pvt.Price_Line
369 (x_return_status => x_return_status
370 ,p_Header_id => l_header_id
371 ,p_Request_Type_code=> 'ONT'
372 ,p_Control_rec => l_Price_Control_Rec
373 ,p_write_to_db => TRUE
374 ,p_request_rec => l_request_rec
375 ,p_multiple_events => l_multiple_events
376 -- Action code of 'PRICE_ORDER' forces integration to query all
377 -- lines and send to pricing engine. Else it would send only
378 -- changed lines but changed lines global is not populated in
379 -- bulk import.
380 -- In future, when bulk supports changes to existing orders, it
381 -- should populate changed lines table and for such orders, call
382 -- with action code of 'PRICE_LINE'.
383 ,p_action_code => 'PRICE_ORDER'
384 ,x_line_Tbl => l_Line_Tbl
385 );
386
387 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
388 OE_DEBUG_PUB.Add('UnExpected Error in Pricing '|| l_header_id,2);
389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
390 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
391 OE_DEBUG_PUB.Add('Expected Error in Pricing '|| l_header_id,2);
392 -- Mark the header for error
393 mark_header_error(i, p_header_rec);
394 p_header_rec.lock_control(i) := -99;
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396 END IF;
397
398 EXCEPTION
399 WHEN FND_API.G_EXC_ERROR THEN
400 IF l_debug_level > 0 then
401 OE_DEBUG_PUB.Add('Expected Error in Pricing '|| l_header_id,2);
402 End if;
403 mark_header_error(i,p_header_rec);
404 p_header_rec.lock_control(i) := -99;
405 x_return_status := FND_API.G_RET_STS_SUCCESS;
406
407 WHEN OTHERS THEN
408 IF l_debug_level > 0 THEN
409 OE_DEBUG_PUB.Add('Unexp Error in Pricing:'||l_header_id|| ' '||SqlErrm, 1);
410 End if;
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END;
414
415 END IF; -- IF OE_BULK_ORDER_PVT.G_PRICING_NEEDED = --bug 455807
416
417 IF p_header_rec.booked_flag(i) = 'Y'
418 AND nvl(p_header_rec.lock_control(i),0) <> -99
419 THEN
420
421 BEGIN
422 SELECT 'Y'
423 INTO l_book_failed
424 FROM OE_ORDER_LINES l
425 WHERE l.header_id = l_header_id
426 AND (l.price_list_id IS NULL
427 OR l.unit_list_price IS NULL
428 OR l.unit_selling_price IS NULL)
429 AND rownum = 1;
430 IF l_debug_level > 0 THEN
431 oe_debug_pub.add( 'DATA FOUND , BOOK FAILED' ) ;
432 END IF;
433 EXCEPTION
434 WHEN NO_DATA_FOUND THEN
435 l_book_failed := 'N';
436 END;
437
438 -- PLEASE ADD CODE HERE FOR COMPARING PRICE AND PAYMENT TERM
439 -- FROM POST_PROCESS IN OEXVIMSB.pls
440
441 IF l_book_failed = 'Y' THEN
442
443 FOR c1 IN c_price_attributes(l_header_id) LOOP
444
445 OE_BULK_MSG_PUB.set_msg_context(
446 p_entity_code => 'LINE'
447 ,p_entity_id => c1.line_id
448 ,p_header_id => l_header_id
449 ,p_line_id => c1.line_id
450 ,p_orig_sys_document_ref =>
451 p_header_rec.orig_sys_document_ref(i)
452 ,p_orig_sys_document_line_ref => null
453 ,p_source_document_id => null
454 ,p_source_document_line_id => null
455 ,p_order_source_id =>
456 p_header_rec.order_source_id(i)
457 ,p_source_document_type_id => null);
458
459 IF c1.price_list_id IS NULL THEN
460 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
461 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
462 OE_Order_UTIL.Get_Attribute_Name('PRICE_LIST_ID'));
463 OE_BULK_MSG_PUB.ADD;
464 END IF;
465
466
467 IF c1.unit_list_price IS NULL THEN
468 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
469 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
470 OE_Order_UTIL.Get_Attribute_Name('UNIT_LIST_PRICE'));
471 OE_BULK_MSG_PUB.ADD;
472 END IF;
473
474 IF c1.unit_selling_price IS NULL THEN
475 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
476 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
477 OE_Order_UTIL.Get_Attribute_Name('UNIT_SELLING_PRICE'));
478 OE_BULK_MSG_PUB.ADD;
479 END IF;
480
481
482 END LOOP;
483
484 Booking_Failed( i, p_header_rec); --bug 455807
485 ELSE
486
487 -- Also add message to indicate that order has been booked. At this
488 -- stage, booked flag should be set to 'Y' only if order passed all
489 -- booking validations including price related validations.
490 OE_BULK_MSG_PUB.set_msg_context(
491 p_entity_code => 'HEADER'
492 ,p_entity_id => l_header_id
493 ,p_header_id => l_header_id
494 ,p_line_id => null
495 ,p_orig_sys_document_ref =>
496 p_header_rec.orig_sys_document_ref(i)
497 ,p_orig_sys_document_line_ref => null
498 ,p_source_document_id => null
499 ,p_source_document_line_id => null
500 ,p_order_source_id =>
501 p_header_rec.order_source_id(i)
502 ,p_source_document_type_id => null
503 );
504 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_BOOKED');
505 OE_BULK_MSG_PUB.Add;
506
507 END IF; -- if book failed, populate errors else add message that
508 -- order is booked
509 --bug 455807
510 IF l_debug_level > 0 THEN
511 oe_debug_pub.add('G_CC_REQUIRED IS: '||OE_BULK_ORDER_PVT.G_CC_REQUIRED ) ;
512 oe_debug_pub.add('l_book_failed IS : '||l_book_failed ) ;
513 oe_debug_pub.add('G_REALTIME_CC_REQUIRED IS: '||OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED ) ;
514 END IF;
515
516 IF OE_BULK_ORDER_PVT.G_CC_REQUIRED = 'Y' AND l_book_failed = 'N' THEN
517
518 -- Update the booked flag only if real Time CC is required
519 -- else the booked_flag is already set on the record
520
521 IF OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED = 'Y' THEN
522 UPDATE oe_order_headers_all
523 SET booked_flag = p_header_rec.booked_flag(i)
524 WHERE header_id = p_header_rec.header_id(i);
525 END IF;
526
527 -- Do credit checking if needed for the order
528
529 IF OE_BULK_CACHE.IS_CC_REQUIRED(p_header_rec.order_type_id(i))
530 THEN
531
532 -- Call the Credit checking API
533 OE_Verify_Payment_PUB.Verify_Payment
534 ( p_header_id => p_header_rec.header_id(i)
535 , p_calling_action => 'BOOKING'
536 , p_delayed_request=> FND_API.G_FALSE
537 , p_msg_count => l_msg_count
538 , p_msg_data => l_msg_data
539 , p_return_status => l_return_status
540 );
541 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
542
543 Booking_Failed( i, p_header_rec);
544
545 END IF; --IF l_return_status <> FND_API.G_RET_STS_SUCC
546
547 END IF; -- IF OE_BULK_CACHE.IS_CC_REQUIRED(p_head
548
549 END IF; -- IF OE_BULK_ORDER_PVT.G_CC_REQUIRED --bug 455807
550 END IF; -- for booked orders, check for pricing attributes
551
552 END IF; -- price only orders without errors
553
554 END LOOP;
555
556 ELSIF p_process_tax = 'Y' THEN
557
558 FOR I IN 1..l_header_count LOOP
559
560 l_header_id := p_header_rec.header_id(i);
561 l_book_failed := 'N';
562
563 IF l_debug_level > 0 THEN
564 oe_debug_pub.add( 'HEADER ID:'||L_HEADER_ID ) ;
565 END IF;
566
567 IF nvl(p_header_rec.lock_control(i),0) <> -99 AND
568 nvl(p_header_rec.lock_control(i),0) <> -98 AND
569 nvl(p_header_rec.lock_control(i),0) <> -97
570 THEN
571
572 IF l_debug_level > 0 THEN
573 oe_debug_pub.add( 'PRICE ORDER , HEADER ID:'||L_HEADER_ID ) ;
574 END IF;
575
576 IF OE_BULK_ORDER_PVT.G_PRICING_NEEDED = 'Y' THEN
577
578 IF p_header_rec.booked_flag(i) = 'Y' THEN
579 l_multiple_events := 'Y';
580 l_price_control_rec.pricing_event := 'BATCH,BOOK';
581 ELSE
582 l_multiple_events := 'N';
583 l_price_control_rec.pricing_event := 'BATCH';
584 END IF;
585
586 l_Price_Control_Rec.calculate_flag :=
587 QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
588 l_Price_Control_Rec.Simulation_Flag := 'N';
589
590 --added for HVOP Tax project
591 OE_BULK_PRICE_PVT.G_Booking_Failed := FALSE;
592 OE_BULK_PRICE_PVT.G_Header_Index := I;
593
594 OE_Order_Adj_Pvt.Price_Line
595 (x_return_status => x_return_status
596 ,p_Header_id => l_header_id
597 ,p_Request_Type_code=> 'ONT'
598 ,p_Control_rec => l_Price_Control_Rec
599 ,p_write_to_db => TRUE
600 ,p_request_rec => l_request_rec
601 ,p_multiple_events => l_multiple_events
602 -- Action code of 'PRICE_ORDER' forces integration to query all
603 -- lines and send to pricing engine. Else it would send only
604 -- changed lines but changed lines global is not populated in
605 -- bulk import.
606 ,p_action_code => 'PRICE_ORDER'
607 ,x_line_Tbl => l_Line_Tbl
608 );
609
610 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
611 OR x_return_status = FND_API.G_RET_STS_ERROR )
612 THEN
613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
614 END IF;
615
616 END IF; -- IF OE_BULK_ORDER_PVT.G_PRICING_NEEDED =
617
618 IF p_header_rec.booked_flag(i) = 'Y' THEN
619
620 IF OE_BULK_PRICE_PVT.G_BOOKING_FAILED THEN
621 l_book_failed := 'Y';
622 ELSE
623 l_book_failed := 'N';
624 END IF;
625
626 IF l_book_failed = 'Y' THEN
627
628 FOR c1 IN c_price_attributes(l_header_id) LOOP
629
630 OE_BULK_MSG_PUB.set_msg_context(
631 p_entity_code => 'LINE'
632 ,p_entity_id => c1.line_id
633 ,p_header_id => l_header_id
634 ,p_line_id => c1.line_id
635 ,p_orig_sys_document_ref =>
636 p_header_rec.orig_sys_document_ref(i)
637 ,p_orig_sys_document_line_ref => null
638 ,p_source_document_id => null
639 ,p_source_document_line_id => null
640 ,p_order_source_id =>
641 p_header_rec.order_source_id(i)
642 ,p_source_document_type_id => null);
643
644 IF c1.price_list_id IS NULL THEN
645 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
646 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
647 OE_Order_UTIL.Get_Attribute_Name('PRICE_LIST_ID'));
648 OE_BULK_MSG_PUB.ADD;
649 END IF;
650
651 IF c1.unit_list_price IS NULL THEN
652 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
653 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
654 OE_Order_UTIL.Get_Attribute_Name('UNIT_LIST_PRICE'));
655 OE_BULK_MSG_PUB.ADD;
656 END IF;
657
658 IF c1.unit_selling_price IS NULL THEN
659 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
660 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
661 OE_Order_UTIL.Get_Attribute_Name('UNIT_SELLING_PRICE'));
662 OE_BULK_MSG_PUB.ADD;
663 END IF;
664
665
666 END LOOP;
667
668 Booking_Failed( i, p_header_rec);
669 ELSE
670 -- Also add message to indicate that order has been booked. At this
671 -- stage, booked flag should be set to 'Y' only if order passed all
672 -- booking validations including price related validations.
673 OE_BULK_MSG_PUB.set_msg_context(
674 p_entity_code => 'HEADER'
675 ,p_entity_id => l_header_id
676 ,p_header_id => l_header_id
677 ,p_line_id => null
678 ,p_orig_sys_document_ref =>
679 p_header_rec.orig_sys_document_ref(i)
680 ,p_orig_sys_document_line_ref => null
681 ,p_source_document_id => null
682 ,p_source_document_line_id => null
683 ,p_order_source_id =>
684 p_header_rec.order_source_id(i)
685 ,p_source_document_type_id => null
686 );
687 FND_MESSAGE.SET_NAME('ONT','OE_ORDER_BOOKED');
688 OE_BULK_MSG_PUB.Add;
689
690 END IF; -- if book failed, populate errors else add message that
691 -- order is booked
692
693 END IF; -- for booked orders, check for pricing attributes
694 END IF; -- price only orders without errors
695
696 END LOOP;
697
698 OE_BULK_TAX_UTIL.Calculate_Tax(p_post_insert => TRUE);
699
700 IF OE_BULK_ORDER_PVT.G_CC_REQUIRED = 'Y' THEN
701 FOR I IN 1..l_header_count LOOP
702
703 IF nvl(p_header_rec.lock_control(i),0) <> -99 AND
704 nvl(p_header_rec.lock_control(i),0) <> -98 AND
705 nvl(p_header_rec.lock_control(i),0) <> -97
706 THEN
707
708 IF l_debug_level > 0 THEN
709 oe_debug_pub.add( 'CREDIT CHECK ORDER , HEADER ID:'||L_HEADER_ID )
710 ;
711 oe_debug_pub.add('G_CC_REQUIRED IS: '||OE_BULK_ORDER_PVT.G_CC_REQUIRED ) ;
712 oe_debug_pub.add('l_book_failed IS : '||l_book_failed ) ;
713 oe_debug_pub.add('G_REALTIME_CC_REQUIRED IS: '||OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED ) ;
714 END IF;
715
716 IF p_header_rec.booked_flag(i) = 'Y' THEN
717
718 -- Update the booked flag only if real Time CC is required
719 -- else the booked_flag is already set on the record
720
721 IF OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED = 'Y' THEN
722 UPDATE oe_order_headers_all
723 SET booked_flag = p_header_rec.booked_flag(i)
724 WHERE header_id = p_header_rec.header_id(i);
725 END IF;
726
727 -- Do credit checking if needed for the order
728
729 IF OE_BULK_CACHE.IS_CC_REQUIRED(p_header_rec.order_type_id(i))
730 THEN
731
732 -- Call the Credit checking API
733 OE_Verify_Payment_PUB.Verify_Payment
734 ( p_header_id => p_header_rec.header_id(i)
735 , p_calling_action => 'BOOKING'
736 , p_delayed_request=> FND_API.G_FALSE
737 , p_msg_count => l_msg_count
738 , p_msg_data => l_msg_data
739 , p_return_status => l_return_status
740 );
741 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
742
743 Booking_Failed( i, p_header_rec);
744 END IF; --IF l_return_status <> FND_API.G_RET_STS_SUCC
745
746 END IF; -- IF OE_BULK_CACHE.IS_CC_REQUIRED(p_head
747
748 END IF; -- IF p_header_rec.booked_flag = 'Y'
749
750 END IF; -- price only orders without errors
751
752 END LOOP;
753 END IF; -- if g_cc_required
754 END IF; -- if p_process_tax...
755
756
757
758 OE_GLOBALS.G_EC_INSTALLED := l_ec_installed;
759
760 EXCEPTION
761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
762 OE_GLOBALS.G_EC_INSTALLED := l_ec_installed;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 WHEN OTHERS THEN
765 OE_GLOBALS.G_EC_INSTALLED := l_ec_installed;
766 OE_BULK_MSG_PUB.Add_Exc_Msg
767 (G_PKG_NAME
768 ,'Price_Orders');
769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
770 END Price_Orders;
771
772 --bug 455807 This procedure has been added
773 PROCEDURE Booking_Failed(p_index IN NUMBER,
774 p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE)
775 IS
776 l_start_index BINARY_INTEGER;
777 BEGIN
778 -- Update DB values
779 UPDATE OE_ORDER_LINES
780 SET booked_flag = 'N'
781 ,flow_status_code = 'ENTERED'
782 WHERE header_id = p_header_rec.header_id(p_index);
783
784 UPDATE OE_ORDER_HEADERS
785 SET booked_flag = 'N'
786 ,booked_date = NULL
787 ,flow_status_code = 'ENTERED'
788 WHERE header_id = p_header_rec.header_id(p_index);
789
790 -- Also, delete from DBI tables if booking fails
791 IF OE_BULK_ORDER_PVT.G_DBI_INSTALLED = 'Y' THEN
792 DELETE FROM ONT_DBI_CHANGE_LOG
793 WHERE header_id = p_header_rec.header_id(p_index);
794 END IF;
795
796 -- Un-set booking fields on global records
797 p_header_rec.booked_flag(p_index) := 'N';
798 l_start_index := 1;
799
800 FOR l_index IN l_start_index..OE_Bulk_Order_PVT.G_LINE_REC.HEADER_ID.COUNT LOOP
801 IF OE_Bulk_Order_PVT.G_LINE_REC.header_id(l_index) = p_header_rec.header_id(p_index)
802 THEN
803 OE_Bulk_Order_PVT.G_LINE_REC.booked_flag(l_index) := 'N';
804 ELSIF OE_Bulk_Order_PVT.G_LINE_REC.header_id(l_index) >
805 p_header_rec.header_id(p_index)
806 THEN
807 l_start_index := l_index;
808 EXIT;
809 END IF;
810 END LOOP;
811
812 END Booking_Failed;
813
814
815 PROCEDURE Update_Pricing_Attributes
816 (p_line_tbl IN OE_ORDER_PUB.LINE_TBL_TYPE
817 )
818 IS
819 l_last_index binary_integer;
820 BEGIN
821 oe_debug_pub.add(' In Update_Pricing_Attributes', 1);
822 oe_debug_pub.add(' table count :'|| p_line_tbl.count, 1 );
823
824 IF OE_BULK_PRICE_PVT.G_HEADER_INDEX IS NULL THEN
825 RETURN;
826 ELSE
827 l_last_index := NVL(OE_BULK_ORDER_PVT.G_HEADER_REC.start_line_index(OE_BULK_PRICE_PVT.G_HEADER_INDEX),1);
828 --NVL added to prevent pl/sql numeric/value error in for loop.bug7685103
829
830 FOR j in 1..p_line_tbl.count LOOP
831 FOR i IN l_last_index..
832 OE_BULK_ORDER_PVT.G_HEADER_REC.end_line_index(OE_BULK_PRICE_PVT.G_HEADER_INDEX)
833 LOOP
834 IF p_line_tbl(j).line_id = OE_BULK_ORDER_PVT.G_LINE_REC.line_id(i) THEN
835 -- match
836 IF
837 OE_BULK_ORDER_PVT.G_HEADER_REC.BOOKED_FLAG(OE_BULK_PRICE_PVT.G_HEADER_INDEX) = 'Y' AND
838 (p_line_tbl(j).unit_selling_price IS NULL OR
839 p_line_tbl(j).unit_list_price IS NULL OR
840 p_line_tbl(j).price_list_id IS NULL) THEN
841 OE_BULK_PRICE_PVT.G_BOOKING_FAILED := TRUE;
842 END IF;
843
844 OE_BULK_ORDER_PVT.G_LINE_REC.unit_selling_price(i) := p_line_tbl(j).unit_selling_price;
845 OE_BULK_ORDER_PVT.G_LINE_REC.unit_list_price(i) := p_line_tbl(j).unit_list_price;
846 OE_BULK_ORDER_PVT.G_LINE_REC.price_list_id(i) := p_line_tbl(j).price_list_id;
847
848 IF i = l_last_index THEN
849 -- increment search space
850 l_last_index := l_last_index + 1;
851 END IF;
852 END IF;
853 END LOOP;
854 END LOOP;
855 END IF;
856 oe_debug_pub.add(' Exit Update_Pricing_Attributes' ,1);
857
858 END Update_Pricing_Attributes;
859
860
861 PROCEDURE mark_header_error(p_header_index IN NUMBER,
862 p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE)
863 IS
864 error_count NUMBER := OE_Bulk_Order_Pvt.G_ERROR_REC.header_id.COUNT;
865 BEGIN
866 OE_DEBUG_PUB.Add('The error count is '|| error_count,2);
867 error_count := error_count + 1;
868
869 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id.EXTEND(1);
870 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id(error_count)
871 := p_header_rec.order_source_id(p_header_index);
872
873 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref.EXTEND(1);
874 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref(error_count)
875 := p_header_rec.orig_sys_document_ref(p_header_index);
876
877 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id.EXTEND(1);
878 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id(error_count)
879 := p_header_rec.header_id(p_header_index);
880 OE_DEBUG_PUB.Add(' Exiting mark_header_error ',2);
881
882 END mark_header_error;
883
884
885 END OE_BULK_PRICE_PVT;