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:
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: --
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: )
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';
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
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:
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:
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:
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;
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:
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';
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
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
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:
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:
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;
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
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) >
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)
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;
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;
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..
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
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
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;
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
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
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;
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);
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:
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);
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)
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);
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)
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:
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;