[Home] [Help]
136: End;
137:
138: procedure Adj_Debug (p_text IN VARCHAR2, p_level IN NUMBER:=5) As
139: --
140: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
141: --
142: Begin
143: If G_DEBUG Then
144: IF l_debug_level > 0 THEN
141: --
142: Begin
143: If G_DEBUG Then
144: IF l_debug_level > 0 THEN
145: oe_debug_pub.add( P_TEXT , P_LEVEL ) ;
146: END IF;
147: End If;
148: End Adj_Debug;
149:
173: instr(l_event_code1,',',1,rownum)-1 - instr(l_event_code1,',',1,rownum-1)))
174: FROM qp_event_phases
175: WHERE rownum < 7);
176: --
177: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
178: --
179: Begin
180:
181: For i in get_phases(p_pricing_event||',') Loop
180:
181: For i in get_phases(p_pricing_event||',') Loop
182:
183: IF l_debug_level > 0 THEN
184: oe_debug_pub.add( 'PRICING PHASE:'||I.PRICING_PHASE_ID ) ;
185: END IF;
186: IF l_debug_level > 0 THEN
187: oe_debug_pub.add( 'FREEZE OVERRIDE-NEW:'||I.POF ) ;
188: END IF;
183: IF l_debug_level > 0 THEN
184: oe_debug_pub.add( 'PRICING PHASE:'||I.PRICING_PHASE_ID ) ;
185: END IF;
186: IF l_debug_level > 0 THEN
187: oe_debug_pub.add( 'FREEZE OVERRIDE-NEW:'||I.POF ) ;
188: END IF;
189:
190: If (i.pof = 'Y' and i.pricing_phase_id <> 1) Then
191: IF l_debug_level > 0 THEN
188: END IF;
189:
190: If (i.pof = 'Y' and i.pricing_phase_id <> 1) Then
191: IF l_debug_level > 0 THEN
192: oe_debug_pub.add( 'FREEZE OVERRIDE IS Y' ) ;
193: END IF;
194: Return True;
195: End If;
196: End Loop;
217:
218: l_list_line_nos Varchar2(2000):=null;
219: l_separator Varchar2(1):='';
220: --
221: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
222: --
223: Begin
224: For i in List_Lines_no Loop
225: l_list_line_nos := i.name||':'||i.list_line_no||l_separator||l_list_line_nos;
238: IS
239: l_org_id NUMBER;
240: l_x_header_rec_oper VARCHAR2(30);
241: --
242: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
243: --
244: BEGIN
245:
246: l_org_id := OE_GLOBALS.G_ORG_ID;
265: RETURN;
266: END IF;
267:
268: IF l_debug_level > 0 THEN
269: oe_debug_pub.add( 'QUERYING HEADER'||P_HEADER_ID , 3 ) ;
270: END IF;
271: SELECT AGREEMENT_ID
272: , CUST_PO_NUMBER
273: , FREIGHT_TERMS_CODE
458: --AND ORDERED_QUANTITY <> 0 (--bug 3018537) commented for the FP bug 3335024
459: AND HEADER_ID = p_header_id;
460:
461: --
462: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
463: --
464: BEGIN
465:
466: IF l_debug_level > 0 THEN
463: --
464: BEGIN
465:
466: IF l_debug_level > 0 THEN
467: oe_debug_pub.add( 'IN QUERY LINES. GET_QP_STATUS:'||QP_UTIL.GET_QP_STATUS ) ;
468: END IF;
469: IF l_debug_level > 0 THEN
470: oe_debug_pub.add( 'G_RECURSION_MODE:'||OE_GLOBALS.G_RECURSION_MODE ) ;
471: END IF;
466: IF l_debug_level > 0 THEN
467: oe_debug_pub.add( 'IN QUERY LINES. GET_QP_STATUS:'||QP_UTIL.GET_QP_STATUS ) ;
468: END IF;
469: IF l_debug_level > 0 THEN
470: oe_debug_pub.add( 'G_RECURSION_MODE:'||OE_GLOBALS.G_RECURSION_MODE ) ;
471: END IF;
472: IF (QP_UTIL.GET_QP_STATUS = 'I' OR OE_GLOBALS.G_RECURSION_MODE <> FND_API.G_TRUE) THEN
473: OE_LINE_UTIL.query_rows(p_line_id=>p_line_id
474: , p_header_id => p_header_id
474: , p_header_id => p_header_id
475: , x_line_tbl => x_line_tbl
476: );
477: IF l_debug_level > 0 THEN
478: oe_debug_pub.add( ' X_LINE_TBL COUNT FROM OE_LINE_UTIL.QUERY_ROWS:'||X_LINE_TBL.COUNT ) ;
479: END IF;
480: --commented for the FP bug 3335024
481: /*
482: --bug 3289322, pl/sql error when count is zero
483: if (x_line_tbl.count > 0) then
484: --bug 3018537 begin
485: for i in x_line_tbl.first..x_line_tbl.last loop
486: if x_line_tbl(i).ordered_quantity = 0 then
487: oe_debug_pub.add(' Not passing line id:'||x_line_tbl(i).line_id||' to pricing engine -- 0 ord qty ');
488: x_line_tbl.delete(i);
489: end if;
490: end loop;
491: oe_debug_pub.add(' New x_line_tbl count:'||x_line_tbl.count);
487: oe_debug_pub.add(' Not passing line id:'||x_line_tbl(i).line_id||' to pricing engine -- 0 ord qty ');
488: x_line_tbl.delete(i);
489: end if;
490: end loop;
491: oe_debug_pub.add(' New x_line_tbl count:'||x_line_tbl.count);
492: end if;
493: --bug 3018537 end
494: */
495: RETURN;
500: l_entity := 'L';
501:
502: ELSIF nvl(p_header_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
503: IF l_debug_level > 0 THEN
504: oe_debug_pub.add( 'SETTING ENTITY TO H' ) ;
505: END IF;
506: l_entity := 'H';
507:
508: END IF;
513: l_org_id := OE_GLOBALS.G_ORG_ID;
514: end if;
515:
516: IF l_debug_level > 0 THEN
517: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.QUERY_LINES '||TO_CHAR ( L_ORG_ID ) , 1 ) ;
518: END IF;
519: IF l_debug_level > 0 THEN
520: oe_debug_pub.add( 'HEADER_ID:'||P_HEADER_ID||' LINE_ID:'||P_LINE_ID , 3 ) ;
521: END IF;
516: IF l_debug_level > 0 THEN
517: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.QUERY_LINES '||TO_CHAR ( L_ORG_ID ) , 1 ) ;
518: END IF;
519: IF l_debug_level > 0 THEN
520: oe_debug_pub.add( 'HEADER_ID:'||P_HEADER_ID||' LINE_ID:'||P_LINE_ID , 3 ) ;
521: END IF;
522:
523:
524: -- Loop over fetched records
599:
600:
601: -- Return fetched table
602: IF l_debug_level > 0 THEN
603: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.QUERY_LINES' , 1 ) ;
604: END IF;
605:
606: EXCEPTION
607:
635: )
636: IS
637: l_line_tbl OE_Order_PUB.Line_Tbl_Type;
638: --
639: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
640: --
641: BEGIN
642: IF l_debug_level > 0 THEN
643: oe_debug_pub.add( ' QUERY_LINE' ) ;
639: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
640: --
641: BEGIN
642: IF l_debug_level > 0 THEN
643: oe_debug_pub.add( ' QUERY_LINE' ) ;
644: END IF;
645: Query_Lines
646: ( p_line_id => p_line_id
647: , p_header_id => Null
649: );
650:
651: x_line_rec := l_line_tbl(1);
652: IF l_debug_level > 0 THEN
653: oe_debug_pub.add( ' LEAVING QUERY_LINE' ) ;
654: END IF;
655: END Query_Line;
656:
657: Function Enforce_list_Price
658: return varchar2
659: is
660: l_enforce_price_flag varchar2(1);
661: --
662: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
663: --
664: begin
665: IF l_debug_level > 0 THEN
666: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.ENFORCE_PRICE_LISTS_FLAG' , 1 ) ;
662: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
663: --
664: begin
665: IF l_debug_level > 0 THEN
666: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.ENFORCE_PRICE_LISTS_FLAG' , 1 ) ;
667: END IF;
668: begin
669:
670: G_STMT_NO := 'Enforce_Price_lists_Flag#10';
716: end ;
717: end if;
718:
719: IF l_debug_level > 0 THEN
720: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.ENFORCE_PRICE_LISTS_FLAG' , 1 ) ;
721: END IF;
722:
723: Return l_enforce_price_flag;
724:
727: Function Get_Rounding_factor(p_list_header_id number)
728: return number
729: is
730: --
731: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
732: --
733: begin
734: IF l_debug_level > 0 THEN
735: oe_debug_pub.add( 'ENTERING GET_ROUNDING_FACTOR'||G_ROUNDING_FACTOR_REC.ROUNDING_FACTOR , 3 ) ;
731: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
732: --
733: begin
734: IF l_debug_level > 0 THEN
735: oe_debug_pub.add( 'ENTERING GET_ROUNDING_FACTOR'||G_ROUNDING_FACTOR_REC.ROUNDING_FACTOR , 3 ) ;
736: END IF;
737: If g_rounding_factor_rec.list_header_id = p_list_header_id then
738: Return g_rounding_factor_rec.rounding_factor;
739: Else
745: g_rounding_factor_rec.rounding_factor:= Null;
746: End If;
747:
748: IF l_debug_level > 0 THEN
749: oe_debug_pub.add( 'ROUNDING FACTOR'||G_ROUNDING_FACTOR_REC.ROUNDING_FACTOR , 3 ) ;
750: END IF;
751: Return g_rounding_factor_rec.rounding_factor;
752:
753: End if;
829: px_Req_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
830: k NUMBER := px_line_attr_index;
831: l_attribute_type varchar2(30);
832: --
833: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
834: --
835: begin
836: G_STMT_NO := 'Append_asked_for#10';
837: IF l_debug_level > 0 THEN
834: --
835: begin
836: G_STMT_NO := 'Append_asked_for#10';
837: IF l_debug_level > 0 THEN
838: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.APPEND_ASKED_FOR' , 1 ) ;
839: END IF;
840: for asked_for_rec in asked_for_cur loop
841: If asked_for_rec.flex_title = 'QP_ATTR_DEFNS_PRICING' then
842: if asked_for_rec.PRICING_ATTRIBUTE1 is not null then
1687: i := px_req_line_attr_tbl.first;
1688: while i is not null loop
1689: k:=k+1;
1690: IF l_debug_level > 0 THEN
1691: oe_debug_pub.add( 'POPULATE LINE ATTRS'||K||' '||PX_REQ_LINE_ATTR_TBL ( I ) .PRICING_CONTEXT , 3 ) ;
1692: END IF;
1693: IF (px_req_line_attr_tbl(I).PRICING_CONTEXT = QP_PREQ_GRP.G_ITEM_CONTEXT) THEN
1694: l_attribute_type := QP_PREQ_GRP.G_PRODUCT_TYPE;
1695: ELSIF (px_req_line_attr_tbl(I).PRICING_CONTEXT = 'MODLIST') THEN
1698: l_attribute_type := QP_PREQ_GRP.G_PRICING_TYPE;
1699: END IF;
1700: G_ATTR_LINE_INDEX_tbl(k) := p_line_index; --p_header_id + nvl(p_line_id,0);
1701: IF l_debug_level > 0 THEN
1702: oe_debug_pub.add( 'LINE_INDEX:'||G_ATTR_LINE_INDEX_TBL ( K ) ) ;
1703: END IF;
1704: G_ATTR_LINE_DETAIL_INDEX_tbl(k) := NULL;
1705: G_ATTR_ATTRIBUTE_LEVEL_tbl(k) := QP_PREQ_GRP.G_LINE_LEVEL;
1706: G_ATTR_VALIDATED_FLAG_tbl(k) := px_Req_line_attr_Tbl(i).Validated_Flag; --'N';
1735: i := px_req_line_attr_tbl.next(i);
1736: end loop;
1737: px_line_attr_index := k;
1738: IF l_debug_level > 0 THEN
1739: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.APPEND_ASKED_FOR' , 1 ) ;
1740: END IF;
1741:
1742: end Append_asked_for;
1743:
1767: l_rate NUMBER;
1768: --bug 3968023 end
1769:
1770: --
1771: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1772: --
1773: Begin
1774: G_STMT_NO := 'Get_the_parent_Line#10';
1775: IF l_debug_level > 0 THEN
1772: --
1773: Begin
1774: G_STMT_NO := 'Get_the_parent_Line#10';
1775: IF l_debug_level > 0 THEN
1776: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.GET_THE_PARENT_LINE' , 1 ) ;
1777: END IF;
1778: line_Tbl_Index := px_Line_Tbl.First;
1779: While line_Tbl_Index is not null loop
1780: If px_Line_Tbl(line_Tbl_Index).line_Id = p_Reference_line_Id Then
1812: WHERE header_id = l_line_rec.header_id;
1813:
1814: IF l_debug_level > 0 THEN
1815:
1816: oe_debug_pub.add('pviprana: l_transactional_curr_code is ' || l_transactional_curr_code);
1817: oe_debug_pub.add('pviprana: l_set_of_books.currency_code' || l_set_of_books.currency_code);
1818: oe_debug_pub.add('pviprana: l_conversion_type_code' || l_conversion_type_code);
1819: oe_debug_pub.add('pviprana: l_conversion_rate_date ' || l_conversion_rate_date);
1820: oe_debug_pub.add('pviprana: l_conversion_rate' || l_conversion_rate);
1813:
1814: IF l_debug_level > 0 THEN
1815:
1816: oe_debug_pub.add('pviprana: l_transactional_curr_code is ' || l_transactional_curr_code);
1817: oe_debug_pub.add('pviprana: l_set_of_books.currency_code' || l_set_of_books.currency_code);
1818: oe_debug_pub.add('pviprana: l_conversion_type_code' || l_conversion_type_code);
1819: oe_debug_pub.add('pviprana: l_conversion_rate_date ' || l_conversion_rate_date);
1820: oe_debug_pub.add('pviprana: l_conversion_rate' || l_conversion_rate);
1821: END IF;
1814: IF l_debug_level > 0 THEN
1815:
1816: oe_debug_pub.add('pviprana: l_transactional_curr_code is ' || l_transactional_curr_code);
1817: oe_debug_pub.add('pviprana: l_set_of_books.currency_code' || l_set_of_books.currency_code);
1818: oe_debug_pub.add('pviprana: l_conversion_type_code' || l_conversion_type_code);
1819: oe_debug_pub.add('pviprana: l_conversion_rate_date ' || l_conversion_rate_date);
1820: oe_debug_pub.add('pviprana: l_conversion_rate' || l_conversion_rate);
1821: END IF;
1822:
1815:
1816: oe_debug_pub.add('pviprana: l_transactional_curr_code is ' || l_transactional_curr_code);
1817: oe_debug_pub.add('pviprana: l_set_of_books.currency_code' || l_set_of_books.currency_code);
1818: oe_debug_pub.add('pviprana: l_conversion_type_code' || l_conversion_type_code);
1819: oe_debug_pub.add('pviprana: l_conversion_rate_date ' || l_conversion_rate_date);
1820: oe_debug_pub.add('pviprana: l_conversion_rate' || l_conversion_rate);
1821: END IF;
1822:
1823: IF (nvl(l_transactional_curr_code, 'XXX') <> nvl(oe_order_pub.g_hdr.transactional_curr_code,'XXX')) THEN
1816: oe_debug_pub.add('pviprana: l_transactional_curr_code is ' || l_transactional_curr_code);
1817: oe_debug_pub.add('pviprana: l_set_of_books.currency_code' || l_set_of_books.currency_code);
1818: oe_debug_pub.add('pviprana: l_conversion_type_code' || l_conversion_type_code);
1819: oe_debug_pub.add('pviprana: l_conversion_rate_date ' || l_conversion_rate_date);
1820: oe_debug_pub.add('pviprana: l_conversion_rate' || l_conversion_rate);
1821: END IF;
1822:
1823: IF (nvl(l_transactional_curr_code, 'XXX') <> nvl(oe_order_pub.g_hdr.transactional_curr_code,'XXX')) THEN
1824: IF (nvl(l_transactional_curr_code, 'XXX') <> nvl(l_set_of_books.currency_code,'XXX') AND
1839: ELSE
1840:
1841: IF nvl(l_transactional_curr_code,'XXX') <> nvl(l_set_of_books.currency_code,'XXX') THEN
1842: IF l_debug_level > 0 THEN
1843: oe_debug_pub.add('pviprana: first conversion: conversion_type is '|| l_transactional_curr_code);
1844: END IF;
1845: gl_currency_api.convert_closest_amount
1846: ( x_from_currency => l_transactional_curr_code
1847: , x_to_currency => l_set_of_books.currency_code
1859: END IF;
1860:
1861: IF nvl(l_set_of_books.currency_code,'XXX') <> nvl(oe_order_pub.g_hdr.transactional_curr_code,'XXX') THEN
1862: IF l_debug_level > 0 THEN
1863: oe_debug_pub.add('pviprana: second conversion: conversion_type is '|| oe_order_pub.g_hdr.conversion_type_code);
1864: END IF;
1865: gl_currency_api.convert_closest_amount
1866: ( x_from_currency => l_set_of_books.currency_code
1867: , x_to_currency => oe_order_pub.g_hdr.transactional_curr_code
1878:
1879: END IF;
1880: px_Line_Tbl(line_Tbl_index).unit_list_price_per_pqty := px_Line_Tbl(line_Tbl_index).unit_list_price * px_Line_Tbl(line_Tbl_index).ordered_quantity / nvl(px_Line_Tbl(line_Tbl_index).pricing_quantity,px_Line_Tbl(line_Tbl_index).ordered_quantity);
1881: IF l_debug_level > 0 THEN
1882: oe_debug_pub.add('pviprana: unit_list_price_per_pqty '||px_Line_Tbl(line_Tbl_index).unit_list_price_per_pqty );
1883: END IF;
1884: END IF;
1885: END IF;
1886:
1889: Exception when No_Data_Found Then
1890: Null;
1891: WHEN OTHERS THEN
1892: IF l_debug_level > 0 THEN
1893: oe_debug_pub.add('pviprana: error while converting the parent lines unit_list_price to the service orders currency' );
1894: END IF;
1895: px_Line_Tbl(line_Tbl_index).unit_list_price := null;
1896: px_Line_Tbl(line_Tbl_index).unit_list_price_per_pqty := null;
1897:
1899: End If;
1900:
1901: BEGIN
1902: IF l_debug_level > 0 THEN
1903: oe_debug_pub.add( 'INSERT INTO RLTD LINES TBL FOR SERVICE' , 3 ) ;
1904: END IF;
1905:
1906: -- Added if condition for Bug 2604056
1907:
1924: ,x_status_text => l_status_text
1925: );
1926:
1927: IF l_status_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1928: oe_debug_pub.add('QP_PREQ_GRP.INSERT_RLTD_LINES2 has reported errors:'||SQLERRM);
1929: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1930: ELSIF l_status_code = FND_API.G_RET_STS_ERROR THEN
1931: oe_debug_pub.add('QP_PREQ_GRP.INSERT_RLTD_LINES2 has reported errors:'||SQLERRM);
1932: RAISE FND_API.G_EXC_ERROR;
1927: IF l_status_code = FND_API.G_RET_STS_UNEXP_ERROR THEN
1928: oe_debug_pub.add('QP_PREQ_GRP.INSERT_RLTD_LINES2 has reported errors:'||SQLERRM);
1929: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1930: ELSIF l_status_code = FND_API.G_RET_STS_ERROR THEN
1931: oe_debug_pub.add('QP_PREQ_GRP.INSERT_RLTD_LINES2 has reported errors:'||SQLERRM);
1932: RAISE FND_API.G_EXC_ERROR;
1933: END IF;
1934: ELSE --lower than 110510
1935: INSERT INTO QP_PREQ_RLTD_LINES_TMP
1955: End If;
1956: EXCEPTION
1957: WHEN OTHERS THEN
1958: IF l_debug_level > 0 THEN
1959: oe_debug_pub.add( 'ERROR INSERTING'||SQLERRM , 3 ) ;
1960: END IF;
1961: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1962: END;
1963: IF l_debug_level > 0 THEN
1960: END IF;
1961: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1962: END;
1963: IF l_debug_level > 0 THEN
1964: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.GET_THE_PARENT_LINE' , 1 ) ;
1965: END IF;
1966:
1967: End Get_the_parent_Line;
1968:
1985: adj2.line_id = p_line_id;
1986: l_prg_line_id NUMBER;
1987: l_rltd_price_adj_id NUMBER;
1988: --
1989: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1990: --
1991: Begin
1992: G_STMT_NO := 'Get_prg_Line#10';
1993: IF l_debug_level > 0 THEN
1990: --
1991: Begin
1992: G_STMT_NO := 'Get_prg_Line#10';
1993: IF l_debug_level > 0 THEN
1994: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.GET_PRG_LINES'||P_LINE_ID , 1 ) ;
1995: END IF;
1996:
1997: for i in prg_lines loop
1998: IF l_debug_level > 0 THEN
1995: END IF;
1996:
1997: for i in prg_lines loop
1998: IF l_debug_level > 0 THEN
1999: oe_debug_pub.add( 'PRG LINE ID = ' || I.PRG_LINE_ID ) ;
2000: END IF;
2001: BEGIN
2002: Query_line(i.prg_line_Id,L_Line_Rec );
2003: line_Tbl_index := px_line_tbl.last + 1;
2012: End;
2013: End LOOP;
2014:
2015: IF l_debug_level > 0 THEN
2016: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.GET_PRG_LINES' , 1 ) ;
2017: END IF;
2018:
2019: End Get_prg_Lines;
2020:
2038: service_reference_type_code='ORDER'and
2039: header_id=p_header_id and
2040: original_list_price is NULL;
2041:
2042: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2043: BEGIN
2044: IF l_debug_level > 0 THEN
2045: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.GET_SERVICE_LINES'||P_LINE_ID , 1 ) ;
2046: END IF;
2041:
2042: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2043: BEGIN
2044: IF l_debug_level > 0 THEN
2045: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.GET_SERVICE_LINES'||P_LINE_ID , 1 ) ;
2046: END IF;
2047:
2048: FOR I in service_lines loop
2049: IF l_debug_level > 0 THEN
2046: END IF;
2047:
2048: FOR I in service_lines loop
2049: IF l_debug_level > 0 THEN
2050: oe_debug_pub.add( 'SERVICE LINE ID = ' || I.LINE_ID ) ;
2051: END IF;
2052: line_Tbl_Index := px_Line_Tbl.First;
2053:
2054: While line_Tbl_Index is not null loop
2081: select modified_from from oe_price_adjustments
2082: where line_id=px_line_rec.line_id
2083: and list_line_type_code='IUE';
2084: --
2085: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2086: --
2087: begin
2088: IF l_debug_level > 0 THEN
2089: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.GET_ITEM_FOR_IUE' , 1 ) ;
2085: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2086: --
2087: begin
2088: IF l_debug_level > 0 THEN
2089: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.GET_ITEM_FOR_IUE' , 1 ) ;
2090: END IF;
2091: For Adj_rec in Adj_cur loop
2092: -- There is an item upgrade for this line
2093: px_line_rec.inventory_item_id := to_number(Adj_rec.modified_from);
2108: Exit;
2109: End Loop;
2110:
2111: IF l_debug_level > 0 THEN
2112: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.GET_ITEM_FOR_IUE' , 1 ) ;
2113: END IF;
2114: end Get_item_for_iue;
2115:
2116:
2122: PROCEDURE Reset_All_Tbls
2123: AS
2124: l_routine VARCHAR2(240):='QP_PREQ_GRP.Reset_All_Tbls';
2125: --
2126: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2127: --
2128: BEGIN
2129: G_LINE_INDEX_tbl.delete;
2130: G_LINE_TYPE_CODE_TBL.delete ;
2191: G_ADDED_PARENT_TBL.delete;
2192: EXCEPTION
2193: WHEN OTHERS THEN
2194: IF l_debug_level > 0 THEN
2195: oe_debug_pub.add( L_ROUTINE||': '||SQLERRM , 1 ) ;
2196: END IF;
2197: END reset_all_tbls;
2198:
2199: PROCEDURE UPDATE_GLOBAL(p_old_line_rec IN OE_ORDER_PUB.LINE_REC_TYPE,
2201: IS
2202: l_notify_index NUMBER;
2203: l_return_status VARCHAR2(1);
2204: l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
2205: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2206: --
2207: BEGIN
2208: l_line_rec:= p_line_rec;
2209: --need to update global picture when line changes
2207: BEGIN
2208: l_line_rec:= p_line_rec;
2209: --need to update global picture when line changes
2210: IF l_debug_level > 0 THEN
2211: oe_debug_pub.add(' Calling update_global_picture to register line changes');
2212: END IF;
2213:
2214: l_notify_index := NULL;
2215:
2222: x_return_status => l_return_status);
2223:
2224:
2225: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2226: oe_debug_pub.add(' Update_global_price called from oe_order_price_pvt.populate_line_tbl reports errors');
2227: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2228: END IF;
2229:
2230: IF l_notify_index IS NOT NULL THEN
2228: END IF;
2229:
2230: IF l_notify_index IS NOT NULL THEN
2231: IF l_debug_level > 0 THEN
2232: oe_debug_pub.add(' Global index obtained is:'||l_notify_index);
2233: oe_debug_pub.add(' Global OLD_LINE USP BEFORE ASSIGNMENT:'||OE_ORDER_UTIL.g_old_line_tbl(l_notify_index).unit_selling_price);
2234: oe_debug_pub.add(' p_old_line_rec.usp:'||p_old_line_rec.unit_selling_price);
2235: oe_debug_pub.add(' new line rec.usp:'||l_line_rec.unit_selling_price);
2236: END IF;
2229:
2230: IF l_notify_index IS NOT NULL THEN
2231: IF l_debug_level > 0 THEN
2232: oe_debug_pub.add(' Global index obtained is:'||l_notify_index);
2233: oe_debug_pub.add(' Global OLD_LINE USP BEFORE ASSIGNMENT:'||OE_ORDER_UTIL.g_old_line_tbl(l_notify_index).unit_selling_price);
2234: oe_debug_pub.add(' p_old_line_rec.usp:'||p_old_line_rec.unit_selling_price);
2235: oe_debug_pub.add(' new line rec.usp:'||l_line_rec.unit_selling_price);
2236: END IF;
2237: --update Global Picture directly
2230: IF l_notify_index IS NOT NULL THEN
2231: IF l_debug_level > 0 THEN
2232: oe_debug_pub.add(' Global index obtained is:'||l_notify_index);
2233: oe_debug_pub.add(' Global OLD_LINE USP BEFORE ASSIGNMENT:'||OE_ORDER_UTIL.g_old_line_tbl(l_notify_index).unit_selling_price);
2234: oe_debug_pub.add(' p_old_line_rec.usp:'||p_old_line_rec.unit_selling_price);
2235: oe_debug_pub.add(' new line rec.usp:'||l_line_rec.unit_selling_price);
2236: END IF;
2237: --update Global Picture directly
2238: --OE_ORDER_UTIL.g_old_line_tbl(l_notify_index):= p_old_line_rec;
2231: IF l_debug_level > 0 THEN
2232: oe_debug_pub.add(' Global index obtained is:'||l_notify_index);
2233: oe_debug_pub.add(' Global OLD_LINE USP BEFORE ASSIGNMENT:'||OE_ORDER_UTIL.g_old_line_tbl(l_notify_index).unit_selling_price);
2234: oe_debug_pub.add(' p_old_line_rec.usp:'||p_old_line_rec.unit_selling_price);
2235: oe_debug_pub.add(' new line rec.usp:'||l_line_rec.unit_selling_price);
2236: END IF;
2237: --update Global Picture directly
2238: --OE_ORDER_UTIL.g_old_line_tbl(l_notify_index):= p_old_line_rec;
2239:
2267:
2268:
2269:
2270: IF l_debug_level > 0 THEN
2271: oe_debug_pub.add('Global picture OLD USP is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE);
2272: oe_debug_pub.add('Global picture NEW USP is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE) ;
2273: oe_debug_pub.add('Global picture NEW OPR is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).operation);
2274: oe_debug_pub.add('Global picture OLD QTY is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2275: oe_debug_pub.add('Global picture NEW QTY is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2268:
2269:
2270: IF l_debug_level > 0 THEN
2271: oe_debug_pub.add('Global picture OLD USP is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE);
2272: oe_debug_pub.add('Global picture NEW USP is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE) ;
2273: oe_debug_pub.add('Global picture NEW OPR is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).operation);
2274: oe_debug_pub.add('Global picture OLD QTY is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2275: oe_debug_pub.add('Global picture NEW QTY is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2276: END IF;
2269:
2270: IF l_debug_level > 0 THEN
2271: oe_debug_pub.add('Global picture OLD USP is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE);
2272: oe_debug_pub.add('Global picture NEW USP is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE) ;
2273: oe_debug_pub.add('Global picture NEW OPR is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).operation);
2274: oe_debug_pub.add('Global picture OLD QTY is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2275: oe_debug_pub.add('Global picture NEW QTY is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2276: END IF;
2277: END IF;
2270: IF l_debug_level > 0 THEN
2271: oe_debug_pub.add('Global picture OLD USP is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE);
2272: oe_debug_pub.add('Global picture NEW USP is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE) ;
2273: oe_debug_pub.add('Global picture NEW OPR is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).operation);
2274: oe_debug_pub.add('Global picture OLD QTY is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2275: oe_debug_pub.add('Global picture NEW QTY is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2276: END IF;
2277: END IF;
2278:
2271: oe_debug_pub.add('Global picture OLD USP is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE);
2272: oe_debug_pub.add('Global picture NEW USP is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).UNIT_SELLING_PRICE) ;
2273: oe_debug_pub.add('Global picture NEW OPR is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).operation);
2274: oe_debug_pub.add('Global picture OLD QTY is:'||OE_ORDER_UTIL.G_OLD_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2275: oe_debug_pub.add('Global picture NEW QTY is:'||OE_ORDER_UTIL.G_LINE_TBL(L_NOTIFY_INDEX).ordered_quantity);
2276: END IF;
2277: END IF;
2278:
2279: END;
2290: l_check_sec BOOLEAN := FALSE;
2291: l_line_index NUMBER;
2292: l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
2293: --
2294: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2295: --
2296: l_validate_desc_flex varchar2(1) := 'N';
2297: Begin
2298:
2296: l_validate_desc_flex varchar2(1) := 'N';
2297: Begin
2298:
2299: IF l_debug_level > 0 THEN
2300: oe_debug_pub.add( 'INSIDE POPULATE_LINE_TBL:'||PX_LINE_TBL.COUNT , 3 ) ;
2301: END IF;
2302: l_line_index := px_line_tbl.first;
2303: while l_line_index is not null loop
2304: l_line_rec := px_line_tbl(l_line_index);
2301: END IF;
2302: l_line_index := px_line_tbl.first;
2303: while l_line_index is not null loop
2304: l_line_rec := px_line_tbl(l_line_index);
2305: oe_debug_pub.add('Ord Qty:'||l_line_rec.ordered_quantity, 3 ) ;
2306: if l_line_rec.ordered_quantity <> 0 then --bug 2823498
2307: BEGIN
2308: select /*+ INDEX(lines qp_preq_lines_tmp_n1) */
2309: nvl(lines.order_uom_selling_price, lines.ADJUSTED_UNIT_PRICE * nvl(lines.priced_quantity,l_line_rec.ordered_quantity)/l_line_rec.ordered_quantity)
2338: (QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
2339: QP_PREQ_GRP.G_STATUS_UPDATED);
2340:
2341: IF l_debug_level > 0 THEN
2342: oe_debug_pub.add( 'UNIT_SELLING_PRICE:'||L_LINE_REC.UNIT_SELLING_PRICE||' FOR LINE:'||L_LINE_REC.LINE_ID , 3 ) ;
2343: oe_debug_pub.add(' QTY:'||l_line_rec.ordered_quantity,3);
2344: oe_debug_pub.add( 'SCHEDULE_DATE'||L_LINE_REC.SCHEDULE_SHIP_DATE||':'||L_LINE_REC.SCHEDULE_ARRIVAL_DATE ) ;
2345: oe_debug_pub.add('New price list id : ' ||l_line_rec.price_list_id);
2346: oe_debug_pub.add('Old price list id : ' ||px_line_tbl(l_line_index).price_list_id);
2339: QP_PREQ_GRP.G_STATUS_UPDATED);
2340:
2341: IF l_debug_level > 0 THEN
2342: oe_debug_pub.add( 'UNIT_SELLING_PRICE:'||L_LINE_REC.UNIT_SELLING_PRICE||' FOR LINE:'||L_LINE_REC.LINE_ID , 3 ) ;
2343: oe_debug_pub.add(' QTY:'||l_line_rec.ordered_quantity,3);
2344: oe_debug_pub.add( 'SCHEDULE_DATE'||L_LINE_REC.SCHEDULE_SHIP_DATE||':'||L_LINE_REC.SCHEDULE_ARRIVAL_DATE ) ;
2345: oe_debug_pub.add('New price list id : ' ||l_line_rec.price_list_id);
2346: oe_debug_pub.add('Old price list id : ' ||px_line_tbl(l_line_index).price_list_id);
2347: END IF;
2340:
2341: IF l_debug_level > 0 THEN
2342: oe_debug_pub.add( 'UNIT_SELLING_PRICE:'||L_LINE_REC.UNIT_SELLING_PRICE||' FOR LINE:'||L_LINE_REC.LINE_ID , 3 ) ;
2343: oe_debug_pub.add(' QTY:'||l_line_rec.ordered_quantity,3);
2344: oe_debug_pub.add( 'SCHEDULE_DATE'||L_LINE_REC.SCHEDULE_SHIP_DATE||':'||L_LINE_REC.SCHEDULE_ARRIVAL_DATE ) ;
2345: oe_debug_pub.add('New price list id : ' ||l_line_rec.price_list_id);
2346: oe_debug_pub.add('Old price list id : ' ||px_line_tbl(l_line_index).price_list_id);
2347: END IF;
2348:
2341: IF l_debug_level > 0 THEN
2342: oe_debug_pub.add( 'UNIT_SELLING_PRICE:'||L_LINE_REC.UNIT_SELLING_PRICE||' FOR LINE:'||L_LINE_REC.LINE_ID , 3 ) ;
2343: oe_debug_pub.add(' QTY:'||l_line_rec.ordered_quantity,3);
2344: oe_debug_pub.add( 'SCHEDULE_DATE'||L_LINE_REC.SCHEDULE_SHIP_DATE||':'||L_LINE_REC.SCHEDULE_ARRIVAL_DATE ) ;
2345: oe_debug_pub.add('New price list id : ' ||l_line_rec.price_list_id);
2346: oe_debug_pub.add('Old price list id : ' ||px_line_tbl(l_line_index).price_list_id);
2347: END IF;
2348:
2349: --bug 3702538
2342: oe_debug_pub.add( 'UNIT_SELLING_PRICE:'||L_LINE_REC.UNIT_SELLING_PRICE||' FOR LINE:'||L_LINE_REC.LINE_ID , 3 ) ;
2343: oe_debug_pub.add(' QTY:'||l_line_rec.ordered_quantity,3);
2344: oe_debug_pub.add( 'SCHEDULE_DATE'||L_LINE_REC.SCHEDULE_SHIP_DATE||':'||L_LINE_REC.SCHEDULE_ARRIVAL_DATE ) ;
2345: oe_debug_pub.add('New price list id : ' ||l_line_rec.price_list_id);
2346: oe_debug_pub.add('Old price list id : ' ||px_line_tbl(l_line_index).price_list_id);
2347: END IF;
2348:
2349: --bug 3702538
2350: If nvl(l_line_rec.price_list_id,-9999) <> nvl(px_line_tbl(l_line_index).price_list_id,-9999) THEN
2357:
2358: EXCEPTION
2359: WHEN NO_DATA_FOUND THEN
2360: IF l_debug_level > 0 THEN
2361: oe_debug_pub.add( 'LINE '||L_LINE_REC.LINE_ID||' DID NOT GET NEW PRICE' ) ;
2362: END IF;
2363: END;
2364:
2365: --UPDATE_GLOBAL(p_old_line_rec=>px_line_tbl(l_line_index),p_line_rec=>l_line_rec);
2368: lx_old_line_price_tbl(l_line_index) := px_line_tbl(l_line_index);
2369: --bug 3702538
2370: px_line_tbl(l_line_index) := l_line_rec;
2371: --For Bug# 7695217
2372: oe_debug_pub.add('7695217 - l_line_rec.line_id - ' || l_line_rec.line_id);
2373: IF l_line_rec.line_id < 0 THEN
2374: l_call_lines := FALSE;
2375: END IF;
2376: --End of Bug# 7695217
2391:
2392: OE_GLOBALS.G_PRICING_RECURSION := 'Y';
2393:
2394: IF l_debug_level > 0 THEN
2395: oe_debug_pub.add( 'validate desc flex:'||OE_GLOBALS.g_validate_desc_flex) ;
2396: END IF;
2397: l_validate_desc_flex := OE_GLOBALS.g_validate_desc_flex;
2398: OE_GLOBALS.g_validate_desc_flex := 'N';
2399: OE_Order_Pvt.Lines
2409: RAISE FND_API.G_EXC_ERROR;
2410: END IF;
2411: OE_GLOBALS.g_validate_desc_flex := l_validate_desc_flex;
2412: IF l_debug_level > 0 THEN
2413: oe_debug_pub.add( 'reset validate desc flex:'||OE_GLOBALS.g_validate_desc_flex) ;
2414: END IF;
2415: OE_GLOBALS.G_PRICING_RECURSION := 'N';
2416: END IF;
2417: --bug 3702538
2427: l_old_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
2428: l_sec_result varchar2(1);
2429: l_return_status varchar2(1);
2430: --
2431: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2432: --
2433: l_old_change_reason varchar2(30);
2434: l_old_change_comments varchar2(2000);
2435: Begin
2433: l_old_change_reason varchar2(30);
2434: l_old_change_comments varchar2(2000);
2435: Begin
2436: IF l_debug_level > 0 THEN
2437: oe_debug_pub.add( 'INSIDE SECURITY_AND_GLOBAL_PICTURE' , 3 ) ;
2438: END IF;
2439:
2440: l_line_index := px_line_tbl.first;
2441: while l_line_index is not null loop
2441: while l_line_index is not null loop
2442: l_line_rec := px_line_tbl(l_line_index);
2443: l_old_line_rec := l_line_rec;
2444: IF l_debug_level > 0 THEN
2445: oe_debug_pub.add( 'LINE ID'||L_LINE_REC.LINE_ID ) ;
2446: END IF;
2447: -- bug 4866684 setting the msg context
2448: OE_MSG_PUB.set_msg_context
2449: ( p_entity_code => 'LINE'
2495: OR l_line_rec.operation= FND_API.G_MISS_CHAR) THEN
2496: l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2497: END IF;
2498: IF l_debug_level > 0 THEN
2499: oe_debug_pub.add( 'LINE PRICE'||L_LINE_REC.UNIT_SELLING_PRICE||L_LINE_REC.OPERATION||' '||L_OLD_LINE_REC.OPERATION , 3 ) ;
2500: END IF;
2501:
2502: --IF (NOT OE_GLOBALS.G_HEADER_CREATED ) THEN
2503: IF l_debug_level > 0 THEN
2500: END IF;
2501:
2502: --IF (NOT OE_GLOBALS.G_HEADER_CREATED ) THEN
2503: IF l_debug_level > 0 THEN
2504: oe_debug_pub.add(' Checking Security');
2505: END IF;
2506:
2507: --bug 2823794, the security api doesn't like delete operation.
2508: --it will raise error if a delete operation is passed.
2546: RAISE FND_API.G_EXC_ERROR;
2547: END IF;
2548:
2549: IF l_debug_level > 0 THEN
2550: oe_debug_pub.add( 'AFTER CHECK_SECURITY'||L_SEC_RESULT ) ;
2551: oe_debug_Pub.add( 'BEFORE UPDATE GLOBAL');
2552: END IF;
2553:
2554: IF p_write_to_db THEN
2547: END IF;
2548:
2549: IF l_debug_level > 0 THEN
2550: oe_debug_pub.add( 'AFTER CHECK_SECURITY'||L_SEC_RESULT ) ;
2551: oe_debug_Pub.add( 'BEFORE UPDATE GLOBAL');
2552: END IF;
2553:
2554: IF p_write_to_db THEN
2555: UPDATE_GLOBAL(p_old_line_rec=>l_old_line_rec,p_line_rec=>l_line_rec);
2555: UPDATE_GLOBAL(p_old_line_rec=>l_old_line_rec,p_line_rec=>l_line_rec);
2556: END IF;
2557:
2558: IF l_debug_level > 0 THEN
2559: oe_debug_pub.add(' AFTER UPDATE GLOBAL');
2560: oe_debug_pub.add('old SP :'||l_old_line_rec.unit_selling_price);
2561: oe_debug_pub.add('new SP :'||l_line_rec.unit_selling_price);
2562: oe_debug_pub.add('order_source_id:'||l_line_rec.order_source_id);
2563: oe_debug_pub.add('xml_transaction_type_code:'||l_line_rec.xml_transaction_type_code);
2556: END IF;
2557:
2558: IF l_debug_level > 0 THEN
2559: oe_debug_pub.add(' AFTER UPDATE GLOBAL');
2560: oe_debug_pub.add('old SP :'||l_old_line_rec.unit_selling_price);
2561: oe_debug_pub.add('new SP :'||l_line_rec.unit_selling_price);
2562: oe_debug_pub.add('order_source_id:'||l_line_rec.order_source_id);
2563: oe_debug_pub.add('xml_transaction_type_code:'||l_line_rec.xml_transaction_type_code);
2564: END IF;
2557:
2558: IF l_debug_level > 0 THEN
2559: oe_debug_pub.add(' AFTER UPDATE GLOBAL');
2560: oe_debug_pub.add('old SP :'||l_old_line_rec.unit_selling_price);
2561: oe_debug_pub.add('new SP :'||l_line_rec.unit_selling_price);
2562: oe_debug_pub.add('order_source_id:'||l_line_rec.order_source_id);
2563: oe_debug_pub.add('xml_transaction_type_code:'||l_line_rec.xml_transaction_type_code);
2564: END IF;
2565:
2558: IF l_debug_level > 0 THEN
2559: oe_debug_pub.add(' AFTER UPDATE GLOBAL');
2560: oe_debug_pub.add('old SP :'||l_old_line_rec.unit_selling_price);
2561: oe_debug_pub.add('new SP :'||l_line_rec.unit_selling_price);
2562: oe_debug_pub.add('order_source_id:'||l_line_rec.order_source_id);
2563: oe_debug_pub.add('xml_transaction_type_code:'||l_line_rec.xml_transaction_type_code);
2564: END IF;
2565:
2566: -- Moved to OE_ACKNOWLEDGMENT_PUB as part of 3417899 and 3412458
2559: oe_debug_pub.add(' AFTER UPDATE GLOBAL');
2560: oe_debug_pub.add('old SP :'||l_old_line_rec.unit_selling_price);
2561: oe_debug_pub.add('new SP :'||l_line_rec.unit_selling_price);
2562: oe_debug_pub.add('order_source_id:'||l_line_rec.order_source_id);
2563: oe_debug_pub.add('xml_transaction_type_code:'||l_line_rec.xml_transaction_type_code);
2564: END IF;
2565:
2566: -- Moved to OE_ACKNOWLEDGMENT_PUB as part of 3417899 and 3412458
2567: /* IF l_line_rec.unit_selling_price <> l_old_line_rec.unit_selling_price
2571: AND nvl(l_line_rec.xml_transaction_type_code, OE_Acknowledgment_Pub.G_TRANSACTION_CSO) = OE_Acknowledgment_Pub.G_TRANSACTION_CSO
2572: AND nvl(l_line_rec.booked_flag, 'X') = 'Y'
2573: AND l_line_rec.ordered_quantity <> 0 THEN
2574: IF l_debug_level > 0 THEN
2575: oe_debug_pub.add( 'calling OE_Acknowlegment_PUB.apply_3a7_hold', 2 ) ;
2576: END IF;
2577: OE_Acknowledgment_PUB.Apply_3A7_Hold(
2578: p_header_id => l_line_rec.header_id
2579: , p_line_id => l_line_rec.line_id
2579: , p_line_id => l_line_rec.line_id
2580: , p_sold_to_org_id => l_line_rec.sold_to_org_id
2581: , x_return_status => l_return_status);
2582: IF l_debug_level > 0 THEN
2583: oe_debug_pub.add( 'Return status after call to apply_3a7_hold:' || l_return_status, 2 ) ;
2584: END IF;
2585: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2587: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2592:
2593: EXCEPTION
2594: WHEN NO_DATA_FOUND THEN
2595: IF l_debug_level > 0 THEN
2596: oe_debug_pub.add( 'LINE '||L_LINE_REC.LINE_ID||' DID NOT GET NEW PRICE' ) ;
2597: END IF;
2598: END;
2599:
2600: l_line_index := px_line_tbl.next(l_line_index);
2600: l_line_index := px_line_tbl.next(l_line_index);
2601: END LOOP;
2602:
2603: IF l_debug_level > 0 THEN
2604: oe_debug_pub.add('Leaving SERURITY_AND_GLOBAL_PICTURE');
2605: END IF;
2606:
2607: END SECURITY_AND_GLOBAL_PICTURE;
2608:
2638: WHERE LINE_TYPE_CODE='LINE'
2639: AND PROCESS_STATUS <> 'NOT_VALID'
2640: AND PRICING_STATUS_CODE = (QP_PREQ_GRP.G_STATUS_UPDATED);
2641: --
2642: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2643: --
2644: --these variables are introduced for bug 3021992
2645: l_mod_lineid NUMBER;
2646: l_line_num VARCHAR2(10);
2645: l_mod_lineid NUMBER;
2646: l_line_num VARCHAR2(10);
2647: BEGIN
2648: IF l_debug_level > 0 THEN
2649: oe_debug_pub.add( 'GSA ENABLED?'||L_GSA_ENABLED_FLAG||' ACTION:'||L_GSA_VIOLATION_ACTION ) ;
2650: END IF;
2651: IF (l_GSA_Enabled_Flag = 'Y') THEN
2652:
2653: FOR i IN gsa_violators LOOP
2651: IF (l_GSA_Enabled_Flag = 'Y') THEN
2652:
2653: FOR i IN gsa_violators LOOP
2654: IF l_debug_level > 0 THEN
2655: oe_debug_pub.add( 'GSA VIOLATION LINE:'||I.LINE_ID , 3 ) ;
2656: END IF;
2657: l_mod_lineid := MOD(i.line_id,2147483647) ;
2658: IF l_gsa_violation_action = 'WARNING' THEN
2659:
2695: --for bug 2028480 Begin
2696: --check if hold released earlier for this line , if so, do not go
2697: --thru the holds logic
2698: IF l_debug_level > 0 THEN
2699: oe_debug_pub.add( 'HOLD ID :'||L_HOLD_SOURCE_REC.HOLD_ID ) ;
2700: END IF;
2701: Begin
2702: --changed select below to fix bug 3039915
2703: select 'Y' into l_gsa_released from
2712: when others then
2713: l_gsa_released := 'N';
2714: end;
2715: IF l_debug_level > 0 THEN
2716: oe_debug_pub.add( 'GSA RELEASED VALUE :'||L_GSA_RELEASED ) ;
2717: END IF;
2718: --for bug 2028480 end
2719: if l_gsa_released = 'N' then --for bug 2028480
2720: -- check if line already on gsa hold, place hold if not
2730: );
2731:
2732: If l_x_result_out = FND_API.G_FALSE then
2733: IF l_debug_level > 0 THEN
2734: oe_debug_pub.add( 'HOLD LINE WITH HEADER_ID:'||L_HEADER_ID||' LINE_ID: '||I.LINE_ID , 1 ) ;
2735: END IF;
2736: OE_HOLDS_PUB.Apply_Holds(
2737: p_api_version => 1.0
2738: ,p_hold_source_rec => l_hold_source_rec
2766:
2767: IF (l_gsa_violation_action = 'ERROR') THEN
2768: FOR i in updated_lines LOOP
2769: IF l_debug_level > 0 THEN
2770: oe_debug_pub.add( 'UPDATING LINE:'||I.LINE_ID||' RELEASING HOLD IF ANY' , 3 ) ;
2771: END IF;
2772: -- release hold if there is one
2773:
2774: If l_hold_source_rec.hold_id is null or
2804: END IF;
2805:
2806: If l_x_result_out = FND_API.G_TRUE then
2807: IF l_debug_level > 0 THEN
2808: oe_debug_pub.add( 'RELEASING GSA_HOLD ON LINE'||I.LINE_ID , 3 ) ;
2809: END IF;
2810: -- Hold is found , Release the hold.
2811:
2812: G_STMT_NO := 'Gsa_Check#20.35';
2827: );
2828:
2829: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2830: IF l_debug_level > 0 THEN
2831: oe_debug_pub.add( 'ERROR WHILE RELEASING GSA HOLD' ) ;
2832: END IF;
2833: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2834: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2835: IF l_debug_level > 0 THEN
2832: END IF;
2833: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2834: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2835: IF l_debug_level > 0 THEN
2836: oe_debug_pub.add( 'ERROR WHILE RELEASING GSA HOLD' ) ;
2837: END IF;
2838: RAISE FND_API.G_EXC_ERROR;
2839: END IF;
2840: End if; -- Release Hold
2849: px_header_rec IN OE_ORDER_PUB.HEADER_REC_TYPE
2850: )
2851: IS
2852: --
2853: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2854: l_price_request_code VARCHAR2(250);--bug 9777018
2855: BEGIN
2856: oe_debug_pub.add('Entering UPDATE_ORDER_HEADER with header_id: '|| px_header_rec.header_id); --bug 9777018
2857: -- NULL; --commented for bug 9777018
2852: --
2853: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2854: l_price_request_code VARCHAR2(250);--bug 9777018
2855: BEGIN
2856: oe_debug_pub.add('Entering UPDATE_ORDER_HEADER with header_id: '|| px_header_rec.header_id); --bug 9777018
2857: -- NULL; --commented for bug 9777018
2858: --/* --un-commented for bug 9777018: Start
2859: --The below code was commenetd for some un-known fix but due to this the PRC was being populated as NULL
2860: --for the Header Record and the request to reverse limits was not made.
2865: select price_request_code into l_price_request_code
2866: from qp_preq_lines_tmp
2867: where line_type_code='ORDER' and line_id=px_header_rec.header_id;
2868:
2869: oe_debug_pub.add(' l_price_request_code = '|| l_price_request_code);
2870:
2871: update oe_order_headers
2872: set price_request_code = l_price_request_code
2873: where header_id = px_header_rec.header_id;
2874: --bug 9777018: End
2875:
2876: END IF;
2877: --*/--un-commented forbug 9777018: End
2878: oe_debug_pub.add('Exiting UPDATE_ORDER_HEADER() ... ');
2879:
2880: --- bug 9777018: Start: commenting the exception handling so as to
2881: -- handle in Exception Block of Process_Adjustments()
2882: /*
2947: AND l.ordered_quantity <> 0 -- bug 3958480
2948: AND l.line_id = lines.line_id;
2949:
2950: --
2951: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2952: --
2953: l_notify_index NUMBER:=NULL;
2954: --bug 4332307
2955: l_header_id Number;
2999: when no_data_found then
3000: l_tax_event_code := 0;
3001: when others then
3002: IF l_debug_level > 0 THEN
3003: oe_debug_pub.add( 'REN: FAILED WHILE QUERY UP TAX_EVENT' ) ;
3004: END IF;
3005: RAISE;
3006:
3007: END;
3005: RAISE;
3006:
3007: END;
3008: IF l_debug_level > 0 THEN
3009: oe_debug_pub.add( 'TAX EVENT CODE'||L_TAX_EVENT_CODE ) ;
3010: END IF;
3011:
3012: for update_line in updated_lines loop
3013: IF l_debug_level > 0 THEN
3010: END IF;
3011:
3012: for update_line in updated_lines loop
3013: IF l_debug_level > 0 THEN
3014: oe_debug_pub.add( 'LOG REQUEST FOR UPDATED LINE '||UPDATE_LINE.LINE_ID ) ;
3015: oe_debug_pub.add('adjusted_unit_price : '||update_line.adjusted_unit_price);
3016: oe_debug_pub.add('order_uom_selling_price : '||update_line.ousp);
3017: oe_debug_pub.add('unit_selling_price_per_pqty : '||update_line.unit_selling_price_per_pqty);
3018: oe_debug_pub.add('unit_selling_price : '||update_line.usp);
3011:
3012: for update_line in updated_lines loop
3013: IF l_debug_level > 0 THEN
3014: oe_debug_pub.add( 'LOG REQUEST FOR UPDATED LINE '||UPDATE_LINE.LINE_ID ) ;
3015: oe_debug_pub.add('adjusted_unit_price : '||update_line.adjusted_unit_price);
3016: oe_debug_pub.add('order_uom_selling_price : '||update_line.ousp);
3017: oe_debug_pub.add('unit_selling_price_per_pqty : '||update_line.unit_selling_price_per_pqty);
3018: oe_debug_pub.add('unit_selling_price : '||update_line.usp);
3019: END IF;
3012: for update_line in updated_lines loop
3013: IF l_debug_level > 0 THEN
3014: oe_debug_pub.add( 'LOG REQUEST FOR UPDATED LINE '||UPDATE_LINE.LINE_ID ) ;
3015: oe_debug_pub.add('adjusted_unit_price : '||update_line.adjusted_unit_price);
3016: oe_debug_pub.add('order_uom_selling_price : '||update_line.ousp);
3017: oe_debug_pub.add('unit_selling_price_per_pqty : '||update_line.unit_selling_price_per_pqty);
3018: oe_debug_pub.add('unit_selling_price : '||update_line.usp);
3019: END IF;
3020: -- Delayed Requests to be logged if selling price change
3013: IF l_debug_level > 0 THEN
3014: oe_debug_pub.add( 'LOG REQUEST FOR UPDATED LINE '||UPDATE_LINE.LINE_ID ) ;
3015: oe_debug_pub.add('adjusted_unit_price : '||update_line.adjusted_unit_price);
3016: oe_debug_pub.add('order_uom_selling_price : '||update_line.ousp);
3017: oe_debug_pub.add('unit_selling_price_per_pqty : '||update_line.unit_selling_price_per_pqty);
3018: oe_debug_pub.add('unit_selling_price : '||update_line.usp);
3019: END IF;
3020: -- Delayed Requests to be logged if selling price change
3021: -- Tax, Verify_Payment, Commitment
3014: oe_debug_pub.add( 'LOG REQUEST FOR UPDATED LINE '||UPDATE_LINE.LINE_ID ) ;
3015: oe_debug_pub.add('adjusted_unit_price : '||update_line.adjusted_unit_price);
3016: oe_debug_pub.add('order_uom_selling_price : '||update_line.ousp);
3017: oe_debug_pub.add('unit_selling_price_per_pqty : '||update_line.unit_selling_price_per_pqty);
3018: oe_debug_pub.add('unit_selling_price : '||update_line.usp);
3019: END IF;
3020: -- Delayed Requests to be logged if selling price change
3021: -- Tax, Verify_Payment, Commitment
3022: --populate a temp l_line_rec, only fields necessary to get tax flag
3047: cust_type.cust_trx_type_id = cust_trx.cust_trx_type_id
3048: and cust_trx.customer_trx_id = update_line.commitment_id
3049: );
3050: IF l_debug_level > 0 THEN
3051: oe_debug_pub.add( 'L_COMMIT TAX FLAG: '||L_COMMT_TAX_FLAG , 1 ) ;
3052: END IF;
3053: exception
3054: when others then
3055: IF l_debug_level > 0 THEN
3052: END IF;
3053: exception
3054: when others then
3055: IF l_debug_level > 0 THEN
3056: oe_debug_pub.add( 'IN EXCEPTION COMMITMENT ' , 1 ) ;
3057: END IF;
3058: l_commt_tax_flag := 'N';
3059: end;
3060: end if;
3063:
3064: --l_tax_calculation_flag := 'Y';
3065:
3066: IF l_debug_level > 0 THEN
3067: oe_debug_pub.add( 'L_TAX_CALCULATION_FLAG'||L_TAX_CALCULATION_FLAG ) ;
3068: END IF;
3069:
3070: -- commented portion of the following condition for bug7306510
3071: -- with ebtax upkae in R12 ,meaning of ra_cust_trx_types.tax_calculation_flag has changed
3083: --update_line.tax_exempt_flag = 'R' OR l_commt_tax_flag = 'Y' )
3084: ) THEN
3085:
3086: IF l_debug_level > 0 THEN
3087: oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR TAXING' ) ;
3088: END IF;
3089: l_commt_tax_flag := 'N' ; --bug 2505961
3090: -- lkxu, make changes for bug 1581188
3091: IF (OE_GLOBALS.G_UI_FLAG)
3114: l_verify_payment := 'N';
3115: --IF OE_ORDER_PUB.G_HDR.PAYMENT_TYPE_CODE = 'CREDIT_CARD' THEN --CC Reversal ER#16014135 Commented
3116: IF OE_ORDER_PUB.G_HDR.PAYMENT_TYPE_CODE = 'CREDIT_CARD' AND OE_VERIFY_PAYMENT_PUB.Get_CC_Rev_Reauth_Code( oe_order_pub.g_hdr.header_id) IS NULL THEN --CC Reversal ER#16014135
3117: IF l_debug_level > 0 THEN
3118: oe_debug_pub.add( 'CREDIT CARD:'||UPDATE_LINE.ADJUSTED_UNIT_PRICE||'>'||UPDATE_LINE.UNIT_SELLING_PRICE_PER_PQTY||'?' ) ;
3119: END IF;
3120: IF update_line.adjusted_unit_price > update_line.unit_selling_price_per_pqty or
3121: nvl(update_line.ousp,0) > nvl(update_line.usp,0)
3122: THEN
3122: THEN
3123: -- Log Request if commitment id is NULL
3124: IF update_line.commitment_id is NULL THEN
3125: IF l_debug_level > 0 THEN
3126: oe_debug_pub.add( 'LOG VERIFY PAYMENT DELAYED REQUSET IN SELLING PRICE' ) ;
3127: END IF;
3128: l_verify_payment := 'Y';
3129: ELSE
3130: IF l_debug_level > 0 THEN
3127: END IF;
3128: l_verify_payment := 'Y';
3129: ELSE
3130: IF l_debug_level > 0 THEN
3131: oe_debug_pub.add( 'COMMITMENT:'||UPDATE_LINE.COMMITMENT_ID ) ;
3132: END IF;
3133: END IF;
3134: END IF;
3135: ELSE
3133: END IF;
3134: END IF;
3135: ELSE
3136: IF l_debug_level > 0 THEN
3137: oe_debug_pub.add( 'PAYMENT TYPE:'||OE_ORDER_PUB.G_HDR.PAYMENT_TYPE_CODE||' BOOKED?'||UPDATE_LINE.BOOKED_FLAG ) ;
3138: END IF;
3139: IF nvl(update_line.booked_flag,'X') = 'Y' THEN
3140: IF l_debug_level > 0 THEN
3141: oe_debug_pub.add( 'LOG VERIFY PAYMENT REQUEST' ) ;
3137: oe_debug_pub.add( 'PAYMENT TYPE:'||OE_ORDER_PUB.G_HDR.PAYMENT_TYPE_CODE||' BOOKED?'||UPDATE_LINE.BOOKED_FLAG ) ;
3138: END IF;
3139: IF nvl(update_line.booked_flag,'X') = 'Y' THEN
3140: IF l_debug_level > 0 THEN
3141: oe_debug_pub.add( 'LOG VERIFY PAYMENT REQUEST' ) ;
3142: END IF;
3143: l_verify_payment := 'Y';
3144: END IF;
3145: END IF;
3144: END IF;
3145: END IF;
3146: IF (l_verify_payment = 'Y' AND update_line.line_category_code <> 'RETURN') THEN
3147: IF l_debug_level > 0 THEN
3148: oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR VERIFY PAYMENT' ) ;
3149: END IF;
3150: --
3151: OE_delayed_requests_Pvt.log_request
3152: (p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
3162: END IF;
3163: END IF;
3164:
3165: IF l_debug_level > 0 THEN
3166: oe_debug_pub.add( 'COMMITMENT '||UPDATE_LINE.COMMITMENT_ID ) ;
3167: END IF;
3168:
3169: IF l_commitment_sequencing AND update_line.commitment_id IS NOT NULL THEN
3170: IF l_debug_level > 0 THEN
3167: END IF;
3168:
3169: IF l_commitment_sequencing AND update_line.commitment_id IS NOT NULL THEN
3170: IF l_debug_level > 0 THEN
3171: oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR COMMITMENT.' , 2 ) ;
3172: END IF;
3173: OE_Delayed_Requests_Pvt.Log_Request(
3174: p_entity_code => OE_GLOBALS.G_ENTITY_LINE,
3175: p_entity_id => update_line.line_id,
3184: END IF;
3185: END IF;
3186:
3187: IF l_debug_level > 0 THEN
3188: oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
3189: oe_debug_pub.add('new SP :'||update_line.new_selling_price);
3190: END IF;
3191:
3192: -- BLANKETS: log request to update blanket amounts if price changes
3185: END IF;
3186:
3187: IF l_debug_level > 0 THEN
3188: oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
3189: oe_debug_pub.add('new SP :'||update_line.new_selling_price);
3190: END IF;
3191:
3192: -- BLANKETS: log request to update blanket amounts if price changes
3193: -- BUG 2746595, send currency code as request_unique_key1 parameter to
3200: AND update_line.blanket_number IS NOT NULL
3201: THEN
3202:
3203: IF l_debug_level > 0 THEN
3204: oe_debug_pub.add('OEXVOPRB log blanket request');
3205: oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
3206: oe_debug_pub.add('new SP :'||update_line.new_selling_price);
3207: END IF;
3208:
3201: THEN
3202:
3203: IF l_debug_level > 0 THEN
3204: oe_debug_pub.add('OEXVOPRB log blanket request');
3205: oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
3206: oe_debug_pub.add('new SP :'||update_line.new_selling_price);
3207: END IF;
3208:
3209: OE_Order_Cache.Load_Order_Header(update_line.header_id);
3202:
3203: IF l_debug_level > 0 THEN
3204: oe_debug_pub.add('OEXVOPRB log blanket request');
3205: oe_debug_pub.add('old SP :'||update_line.unit_selling_price);
3206: oe_debug_pub.add('new SP :'||update_line.new_selling_price);
3207: END IF;
3208:
3209: OE_Order_Cache.Load_Order_Header(update_line.header_id);
3210: --for bug 3108881
3281:
3282: --bug 4332307
3283: IF l_log_mrg_hold_req = 'Y' THEN
3284: IF l_debug_level > 0 THEN
3285: oe_debug_pub.add( 'LOGGING DELAYED REQUEST FOR MARGIN HOLD FOR BOOKED HEADER_ID:'||l_header_id ) ;
3286: END IF;
3287: oe_delayed_requests_pvt.log_request(
3288: p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
3289: p_entity_id => l_header_id,
3298:
3299:
3300: IF (G_PASS_ALL_LINES in ('N', 'R')) THEN
3301: IF l_debug_level > 0 THEN
3302: oe_debug_pub.add( 'DID NOT PASS ALL LINES , LOG REQUEST' ) ;
3303: END IF;
3304: BEGIN
3305: SELECT processed_flag
3306: INTO l_order_adj_changed_flag
3306: INTO l_order_adj_changed_flag
3307: FROM qp_preq_lines_tmp
3308: WHERE line_type_code='ORDER' and price_flag='Y';
3309: IF l_debug_level > 0 THEN
3310: oe_debug_pub.add( 'ORDER ADJ CHANGED:'||L_ORDER_ADJ_CHANGED_FLAG ) ;
3311: END IF;
3312: IF (l_order_adj_changed_flag = 'C') THEN
3313: IF l_debug_level > 0 THEN
3314: oe_debug_pub.add( 'LOGGING REQUEST TO UPDATE ALL LINES FOR HEADER LEVEL ADJUSTMENT.' , 1 ) ;
3310: oe_debug_pub.add( 'ORDER ADJ CHANGED:'||L_ORDER_ADJ_CHANGED_FLAG ) ;
3311: END IF;
3312: IF (l_order_adj_changed_flag = 'C') THEN
3313: IF l_debug_level > 0 THEN
3314: oe_debug_pub.add( 'LOGGING REQUEST TO UPDATE ALL LINES FOR HEADER LEVEL ADJUSTMENT.' , 1 ) ;
3315: END IF;
3316: OE_DELAYED_REQUESTS_PVT.LOG_REQUEST(
3317: p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
3318: p_entity_id => oe_order_pub.g_hdr.header_id,
3324: END IF;
3325: EXCEPTION
3326: WHEN NO_DATA_FOUND THEN
3327: IF l_debug_level > 0 THEN
3328: oe_debug_pub.add( 'NO SUMMARY LINE WITH PRICE FLAG OF Y' ) ;
3329: END IF;
3330: NULL;
3331: WHEN OTHERS THEN
3332: IF l_debug_level > 0 THEN
3329: END IF;
3330: NULL;
3331: WHEN OTHERS THEN
3332: IF l_debug_level > 0 THEN
3333: oe_debug_pub.add( 'PROBLEM?'||SQLERRM ) ;
3334: END IF;
3335: END;
3336: END IF;
3337:
3335: END;
3336: END IF;
3337:
3338: IF l_debug_level > 0 THEN
3339: oe_debug_pub.add( 'AFTER LOG_REQUEST' , 3 ) ;
3340: END IF;
3341:
3342: EXCEPTION
3343: WHEN OTHERS THEN
3341:
3342: EXCEPTION
3343: WHEN OTHERS THEN
3344: IF l_debug_level > 0 THEN
3345: oe_debug_pub.add( 'WRONG IN LOG_REQUEST'||SQLERRM , 1 ) ;
3346: END IF;
3347: raise fnd_api.g_exc_error;
3348: End LOG_REQUEST;
3349:
3363: Select Waiting_Session,Holding_session,lock_type,MODE_HELD,MODE_REQUESTED,LOCK_ID1,LOCK_ID2
3364: From dba_waiters;
3365: */
3366: --
3367: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3368: --
3369: Begin
3370: --RT{
3371: IF nvl(G_PRICING_EVENT,';xx-') = 'RETROBILL' THEN
3368: --
3369: Begin
3370: --RT{
3371: IF nvl(G_PRICING_EVENT,';xx-') = 'RETROBILL' THEN
3372: oe_debug_pub.add('Calling update retrobill lines');
3373: LOG_REQUEST(px_line_tbl);
3374: Oe_Retrobill_Pvt.UPDATE_RETROBILL_LINES(G_RETROBILL_OPERATION);
3375: ELSE --RT
3376: IF l_debug_level > 0 THEN
3373: LOG_REQUEST(px_line_tbl);
3374: Oe_Retrobill_Pvt.UPDATE_RETROBILL_LINES(G_RETROBILL_OPERATION);
3375: ELSE --RT
3376: IF l_debug_level > 0 THEN
3377: oe_debug_pub.add( ' ENTERING OE_ORDER_PRICE_PVT.UPDATE_ORDER_LINES' ) ;
3378: END IF;
3379:
3380: SECURITY_AND_GLOBAL_PICTURE(px_line_tbl,p_write_to_db);
3381:
3406: and lines.line_type_code='LINE'
3407: )
3408: FOR UPDATE NOWAIT;
3409:
3410: oe_debug_pub.add('Selected for update');
3411:
3412: EXCEPTION
3413: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION
3414: THEN
3412: EXCEPTION
3413: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION
3414: THEN
3415: IF l_debug_level > 0 THEN
3416: oe_debug_pub.add('in lock record exception, someone else working on the row');
3417: END IF;
3418:
3419: FND_MESSAGE.Set_Name('ONT', 'OE_LINE_LOCKED');
3420: OE_MSG_PUB.Add;
3421: RAISE fnd_api.g_exc_unexpected_error;
3422: WHEN OTHERS
3423: THEN
3424: IF l_debug_level > 0 THEN
3425: oe_debug_pub.add('Exceptions have occured, Continuing with the update statement.');
3426: oe_debug_pub.add('SQLCODE: '||SQLCODE);
3427: oe_debug_pub.add('SQLERRM: '||SQLERRM);
3428: END IF;
3429: END;
3422: WHEN OTHERS
3423: THEN
3424: IF l_debug_level > 0 THEN
3425: oe_debug_pub.add('Exceptions have occured, Continuing with the update statement.');
3426: oe_debug_pub.add('SQLCODE: '||SQLCODE);
3427: oe_debug_pub.add('SQLERRM: '||SQLERRM);
3428: END IF;
3429: END;
3430: --Bug 13473036 ends
3423: THEN
3424: IF l_debug_level > 0 THEN
3425: oe_debug_pub.add('Exceptions have occured, Continuing with the update statement.');
3426: oe_debug_pub.add('SQLCODE: '||SQLCODE);
3427: oe_debug_pub.add('SQLERRM: '||SQLERRM);
3428: END IF;
3429: END;
3430: --Bug 13473036 ends
3431:
3429: END;
3430: --Bug 13473036 ends
3431:
3432: IF l_debug_level > 0 THEN
3433: oe_debug_pub.add( ' BEFORE UPDATING ORDER LINES' , 3 ) ;
3434: END IF;
3435: UPDATE OE_ORDER_LINES_all l
3436: SET (UNIT_SELLING_PRICE
3437: , UNIT_LIST_PRICE
3486: and lines.process_status <> 'NOT_VALID'
3487: and
3488: lines.line_type_code='LINE');
3489: IF l_debug_level > 0 THEN
3490: oe_debug_pub.add( ' LEAVING UPDATE_ORDER_LINES:'||SQL%ROWCOUNT , 3 ) ;
3491: END IF;
3492: x_num_changed_lines := SQL%ROWCOUNT;
3493: END IF; --RT
3494: EXCEPTION
3493: END IF; --RT
3494: EXCEPTION
3495: WHEN DEADLOCK_DETECTED THEN
3496: IF l_debug_level > 0 THEN
3497: oe_debug_pub.add( 'WRONG IN UPDATE_ORDER_LINES'||SQLERRM , 1 ) ;
3498: END IF;
3499: /*
3500: For i in get_lock_info Loop
3501: oe_debug_pub.add('Waiting_Session:'||i.waiting_Session||' Holding_session:'||i.Holding_session);
3497: oe_debug_pub.add( 'WRONG IN UPDATE_ORDER_LINES'||SQLERRM , 1 ) ;
3498: END IF;
3499: /*
3500: For i in get_lock_info Loop
3501: oe_debug_pub.add('Waiting_Session:'||i.waiting_Session||' Holding_session:'||i.Holding_session);
3502: oe_debug_pub.add('Lock_type:'||i.lock_type||' MODE_HELD:'||i.MODE_HELD||' MODE_REQUESTED'||i.MODE_REQUESTED);
3503: oe_debug_pub.add('Lock_ID1:'||i.Lock_ID1||' Lock_ID2:'||i.Lock_ID2);
3504: End Loop;
3505: */
3498: END IF;
3499: /*
3500: For i in get_lock_info Loop
3501: oe_debug_pub.add('Waiting_Session:'||i.waiting_Session||' Holding_session:'||i.Holding_session);
3502: oe_debug_pub.add('Lock_type:'||i.lock_type||' MODE_HELD:'||i.MODE_HELD||' MODE_REQUESTED'||i.MODE_REQUESTED);
3503: oe_debug_pub.add('Lock_ID1:'||i.Lock_ID1||' Lock_ID2:'||i.Lock_ID2);
3504: End Loop;
3505: */
3506: raise fnd_api.g_exc_unexpected_error;
3499: /*
3500: For i in get_lock_info Loop
3501: oe_debug_pub.add('Waiting_Session:'||i.waiting_Session||' Holding_session:'||i.Holding_session);
3502: oe_debug_pub.add('Lock_type:'||i.lock_type||' MODE_HELD:'||i.MODE_HELD||' MODE_REQUESTED'||i.MODE_REQUESTED);
3503: oe_debug_pub.add('Lock_ID1:'||i.Lock_ID1||' Lock_ID2:'||i.Lock_ID2);
3504: End Loop;
3505: */
3506: raise fnd_api.g_exc_unexpected_error;
3507: WHEN OTHERS THEN
3505: */
3506: raise fnd_api.g_exc_unexpected_error;
3507: WHEN OTHERS THEN
3508: IF l_debug_level > 0 THEN
3509: oe_debug_pub.add( 'WRONG IN UPDATE_ORDER_LINES'||SQLERRM , 1 ) ;
3510: END IF;
3511: raise fnd_api.g_exc_unexpected_error;
3512: End UPDATE_ORDER_LINES;
3513:
3529: l_index NUMBER;
3530: l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
3531:
3532: --
3533: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3534: --
3535: BEGIN
3536: IF l_debug_level > 0 THEN
3537: oe_debug_pub.add('l_booked_flag in delete dependents = '||l_booked_flag);
3533: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3534: --
3535: BEGIN
3536: IF l_debug_level > 0 THEN
3537: oe_debug_pub.add('l_booked_flag in delete dependents = '||l_booked_flag);
3538: END IF;
3539: If p_adj_id_tbl.count > 0 Then
3540: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
3541: --IF l_booked_flag = 'Y' THEN
3540: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
3541: --IF l_booked_flag = 'Y' THEN
3542: --IF oe_adv_price_pvt.check_notify_OC then
3543: IF l_debug_level > 0 THEN
3544: oe_debug_pub.add('Delete adjustments notify to OC');
3545: END IF;
3546: i := p_adj_id_tbl.first;
3547: while i is not null loop
3548: IF (p_line_id_tbl(i) is NOT NULL) THEN -- Line Level Adjustment 8415941
3546: i := p_adj_id_tbl.first;
3547: while i is not null loop
3548: IF (p_line_id_tbl(i) is NOT NULL) THEN -- Line Level Adjustment 8415941
3549: IF l_debug_level > 0 THEN
3550: oe_debug_pub.add('Line Level Adjustment 8415941 ');
3551: END IF;
3552: l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
3553: l_Line_Adj_rec.price_adjustment_id := p_adj_id_tbl(i);
3554: --bug 3882620
3560: l_Line_Adj_rec.applied_flag := p_applied_flag_tbl(i);
3561: l_Line_Adj_rec.adjusted_amount := p_adjusted_amount_tbl(i);
3562: l_Line_Adj_rec.last_update_date := sysdate;
3563: IF l_debug_level > 0 THEN
3564: oe_debug_pub.add('header_id:'||l_Line_Adj_rec.header_id||'line_id:'|| l_Line_Adj_rec.line_id);
3565: oe_debug_pub.add('list_line_id:'||l_Line_Adj_rec.list_line_id||'list_header_id:'||l_Line_Adj_rec.list_header_id);
3566: oe_debug_pub.add('last_update_date:'||l_Line_Adj_rec.last_update_date);
3567: END IF;
3568: --bug 3882620
3561: l_Line_Adj_rec.adjusted_amount := p_adjusted_amount_tbl(i);
3562: l_Line_Adj_rec.last_update_date := sysdate;
3563: IF l_debug_level > 0 THEN
3564: oe_debug_pub.add('header_id:'||l_Line_Adj_rec.header_id||'line_id:'|| l_Line_Adj_rec.line_id);
3565: oe_debug_pub.add('list_line_id:'||l_Line_Adj_rec.list_line_id||'list_header_id:'||l_Line_Adj_rec.list_header_id);
3566: oe_debug_pub.add('last_update_date:'||l_Line_Adj_rec.last_update_date);
3567: END IF;
3568: --bug 3882620
3569: OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
3562: l_Line_Adj_rec.last_update_date := sysdate;
3563: IF l_debug_level > 0 THEN
3564: oe_debug_pub.add('header_id:'||l_Line_Adj_rec.header_id||'line_id:'|| l_Line_Adj_rec.line_id);
3565: oe_debug_pub.add('list_line_id:'||l_Line_Adj_rec.list_line_id||'list_header_id:'||l_Line_Adj_rec.list_header_id);
3566: oe_debug_pub.add('last_update_date:'||l_Line_Adj_rec.last_update_date);
3567: END IF;
3568: --bug 3882620
3569: OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
3570: p_line_adj_rec =>l_line_adj_rec,
3572: p_old_line_adj_rec =>l_line_adj_rec,
3573: x_index => l_index,
3574: x_return_status => l_return_status);
3575: IF l_debug_level > 0 THEN
3576: oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS IS: ' || L_RETURN_STATUS ) ;
3577: END IF;
3578:
3579: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3580:
3578:
3579: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3580:
3581: IF l_debug_level > 0 THEN
3582: oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
3583: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS', 1 ) ;
3584: END IF;
3585: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3586: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3579: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3580:
3581: IF l_debug_level > 0 THEN
3582: oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
3583: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS', 1 ) ;
3584: END IF;
3585: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3586: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3587: IF l_debug_level > 0 THEN
3584: END IF;
3585: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3586: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3587: IF l_debug_level > 0 THEN
3588: oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS' ) ;
3589: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS', 1 ) ;
3590: END IF;
3591: RAISE FND_API.G_EXC_ERROR;
3592: END IF;
3585: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3586: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3587: IF l_debug_level > 0 THEN
3588: oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS' ) ;
3589: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS', 1 ) ;
3590: END IF;
3591: RAISE FND_API.G_EXC_ERROR;
3592: END IF;
3593: ELSE -- Header Level Adjustment
3591: RAISE FND_API.G_EXC_ERROR;
3592: END IF;
3593: ELSE -- Header Level Adjustment
3594: IF l_debug_level > 0 THEN
3595: oe_debug_pub.add('Header Level Adjustment 8415941 ');
3596: END IF;
3597:
3598: l_Header_Adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
3599: l_Header_Adj_rec.price_adjustment_id := p_adj_id_tbl(i);
3612: p_old_hdr_adj_rec =>l_header_adj_rec,
3613: x_index => l_index,
3614: x_return_status => l_return_status);
3615: IF l_debug_level > 0 THEN
3616: oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM Header Level Adj: ' || L_RETURN_STATUS ) ;
3617: END IF;
3618: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3619: IF l_debug_level > 0 THEN
3620: oe_debug_pub.add( 'Delete - UNEXPECTED ERROR' ) ;
3616: oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM Header Level Adj: ' || L_RETURN_STATUS ) ;
3617: END IF;
3618: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3619: IF l_debug_level > 0 THEN
3620: oe_debug_pub.add( 'Delete - UNEXPECTED ERROR' ) ;
3621: END IF;
3622: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3623: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3624: IF l_debug_level > 0 THEN
3621: END IF;
3622: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3623: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3624: IF l_debug_level > 0 THEN
3625: oe_debug_pub.add( 'Delete -Error' ) ;
3626: END IF;
3627: RAISE FND_API.G_EXC_ERROR;
3628: END IF;
3629:
3636:
3637: FORALL i IN p_adj_id_tbl.FIRST..p_adj_id_tbl.LAST
3638: DELETE FROM OE_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = p_adj_id_tbl(i);
3639: IF l_debug_level > 0 THEN
3640: oe_debug_pub.add( ' DELETED '||SQL%ROWCOUNT||' ATTRIBS' , 3 ) ;
3641: END IF;
3642:
3643: FORALL i IN p_adj_id_tbl.FIRST..p_adj_id_tbl.LAST
3644: DELETE FROM OE_PRICE_ADJ_ASSOCS WHERE price_adjustment_id = p_adj_id_tbl(i);
3642:
3643: FORALL i IN p_adj_id_tbl.FIRST..p_adj_id_tbl.LAST
3644: DELETE FROM OE_PRICE_ADJ_ASSOCS WHERE price_adjustment_id = p_adj_id_tbl(i);
3645: IF l_debug_level > 0 THEN
3646: oe_debug_pub.add( ' DELETED '||SQL%ROWCOUNT||' ASSOCS' , 3 ) ;
3647: END IF;
3648:
3649: FORALL i IN p_adj_id_tbl.FIRST..p_adj_id_tbl.LAST
3650: DELETE FROM OE_PRICE_ADJ_ASSOCS WHERE rltd_price_adj_id = p_adj_id_tbl(i);
3648:
3649: FORALL i IN p_adj_id_tbl.FIRST..p_adj_id_tbl.LAST
3650: DELETE FROM OE_PRICE_ADJ_ASSOCS WHERE rltd_price_adj_id = p_adj_id_tbl(i);
3651: IF l_debug_level > 0 THEN
3652: oe_debug_pub.add( ' DELETED '||SQL%ROWCOUNT||' RLTD ASSOCS' , 3 ) ;
3653: END IF;
3654:
3655: p_adj_id_tbl.delete;
3656: End If;
3675: l_list_header_id_tbl Number_Type;
3676: l_adjusted_amount_tbl Number_Type;
3677: l_list_line_id_tbl Number_Type;
3678: --bug 3882620
3679: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3680:
3681: BEGIN
3682:
3683: IF p_hdr_price_flag in ('Y', 'P') AND p_hdr_pricing_status_code IN
3723: l_applied_flag_tbl,
3724: l_adjusted_amount_tbl;
3725:
3726: IF l_debug_level > 0 THEN
3727: oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' HEADER LEVEL ADJUSTMENTS/CHARGES' , 3 ) ;
3728: END IF;
3729: DELETE_DEPENDENTS(l_adj_id_tbl,l_header_id_tbl,l_line_id_tbl,l_list_line_id_tbl,l_list_header_id_tbl,l_list_line_type_code_tbl,l_applied_flag_tbl,l_adjusted_amount_tbl);
3730: END IF;
3731: END DELETE_HDR_ADJS;
3752: l_list_header_id_tbl Number_Type;
3753: l_adjusted_amount_tbl Number_Type;
3754: l_list_line_id_tbl Number_Type;
3755: --bug 3882620
3756: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3757:
3758: BEGIN
3759:
3760: l_line_type_code := 'LINE';
3803: l_applied_flag_tbl,
3804: l_adjusted_amount_tbl;
3805:
3806: IF l_debug_level > 0 THEN
3807: oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' LINE LEVEL ADJUSTMENTS/CHARGES FOR LINE:'||ONE_LINE.LINE_ID , 3 ) ;
3808: END IF;
3809: DELETE_DEPENDENTS(l_adj_id_tbl,l_header_id_tbl,l_line_id_tbl,l_list_line_id_tbl,l_list_header_id_tbl,l_list_line_type_code_tbl,l_applied_flag_tbl,l_adjusted_amount_tbl);
3810: END LOOP;
3811:
3826: l_list_header_id_tbl Number_Type;
3827: l_adjusted_amount_tbl Number_Type;
3828: l_list_line_id_tbl Number_Type;
3829: --bug 3882620
3830: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3831:
3832: BEGIN
3833:
3834: select line_id,
3889: l_applied_flag_tbl,
3890: l_adjusted_amount_tbl;
3891:
3892: IF l_debug_level > 0 THEN
3893: oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' LINE LEVEL ADJUSTMENTS/CHARGES' , 3 ) ;
3894: END IF;
3895: DELETE_DEPENDENTS(l_adj_id_tbl,l_header_id_tbl,l_line_id_tbl,l_list_line_id_tbl,l_list_header_id_tbl,l_list_line_type_code_tbl,l_applied_flag_tbl,l_adjusted_amount_tbl);
3896:
3897: END DELETE_ONE_LINE_ADJS;
3906: )
3907: IS
3908:
3909: l_price_adjustment_id NUMBER;
3910: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3911:
3912: BEGIN
3913:
3914: IF l_debug_level > 0 THEN
3911:
3912: BEGIN
3913:
3914: IF l_debug_level > 0 THEN
3915: oe_debug_pub.add('entering procedure update_adj');
3916: oe_debug_pub.add('p_price_adjustment_id : '||p_price_adjustment_id);
3917: oe_debug_pub.add('p_line_detail_index : '||p_line_detail_index);
3918: END IF;
3919:
3912: BEGIN
3913:
3914: IF l_debug_level > 0 THEN
3915: oe_debug_pub.add('entering procedure update_adj');
3916: oe_debug_pub.add('p_price_adjustment_id : '||p_price_adjustment_id);
3917: oe_debug_pub.add('p_line_detail_index : '||p_line_detail_index);
3918: END IF;
3919:
3920: --bug 5497035
3913:
3914: IF l_debug_level > 0 THEN
3915: oe_debug_pub.add('entering procedure update_adj');
3916: oe_debug_pub.add('p_price_adjustment_id : '||p_price_adjustment_id);
3917: oe_debug_pub.add('p_line_detail_index : '||p_line_detail_index);
3918: END IF;
3919:
3920: --bug 5497035
3921: BEGIN
3926: WHERE price_adjustment_id = p_price_adjustment_id
3927: FOR UPDATE NOWAIT;
3928:
3929: IF l_debug_level > 0 Then
3930: oe_Debug_pub.add('Adjustment row successfully locked');
3931: END IF;
3932:
3933: EXCEPTION
3934: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3932:
3933: EXCEPTION
3934: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3935: IF l_debug_level > 0 THEN
3936: oe_debug_pub.add('in lock record exception, someone else working on the row');
3937: END IF;
3938: FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
3939: OE_MSG_PUB.Add;
3940: RAISE FND_API.G_EXC_ERROR;
3940: RAISE FND_API.G_EXC_ERROR;
3941:
3942: WHEN NO_DATA_FOUND THEN
3943: IF l_debug_level > 0 THEN
3944: oe_debug_pub.add('no_data_found, record lock exception');
3945: END IF;
3946: --bug 5709185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3947:
3948: WHEN OTHERS THEN
3946: --bug 5709185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3947:
3948: WHEN OTHERS THEN
3949: IF l_debug_level > 0 THEN
3950: oe_debug_pub.add('record lock exception, others');
3951: END IF;
3952: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3953:
3954: END;
4051: )
4052: where adj.price_adjustment_id = p_price_adjustment_id
4053: returning adj.list_line_id bulk collect into px_debug_upd_adj_tbl;
4054: IF l_debug_level > 0 THEN
4055: oe_debug_pub.add('exiting update_adj procedure');
4056: END IF;
4057: END update_adj;
4058: --bug 3836854
4059:
4223: l_hdr_pricing_status_code VARCHAR2(30);
4224: l_no_summary_line boolean := FALSE;
4225: ---bug 3740009
4226: --
4227: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4228: --
4229: BEGIN
4230:
4231: IF l_debug_level > 0 THEN
4228: --
4229: BEGIN
4230:
4231: IF l_debug_level > 0 THEN
4232: oe_debug_pub.add( 'INSIDE REFRESH_ADJS FOR LINE ID:'||P_LINE_ID|| ' EVENT:'||P_PRICING_EVENTS , 3 ) ;
4233: END IF;
4234:
4235: ---bug 3740009
4236: BEGIN
4247: where line_type_code = 'ORDER'
4248: and process_status <> 'NOT_VALID';
4249: EXCEPTION
4250: WHEN NO_DATA_FOUND THEN
4251: oe_debug_pub.add('No summary line');
4252: l_no_summary_line := TRUE;
4253: END;
4254: ---bug 3740009
4255:
4284: --pviprana: Debug messages to show the contents in the qp temp tables
4285: -- THE FOLLOWING CURSORS RETRIEVE ALL THE NEEDED VALUES FROM QP TEMP TABLES
4286: -- PLEASE PRINT THE NEEDED VALUES FOR DEBUGGING PURPOSES
4287: IF l_debug_level > 0 THEN
4288: oe_debug_pub.add('UPDATABLE ORDER LEVEL ADJUSTMENTS :');
4289: FOR updble_order_adj IN debug_updatable_order_adj LOOP
4290: oe_debug_pub.add( '************************************************************************');
4291: oe_debug_pub.add('LIST_LINE_ID : '||updble_order_adj.list_line_id);
4292: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_order_adj.order_qty_adj_amt);
4286: -- PLEASE PRINT THE NEEDED VALUES FOR DEBUGGING PURPOSES
4287: IF l_debug_level > 0 THEN
4288: oe_debug_pub.add('UPDATABLE ORDER LEVEL ADJUSTMENTS :');
4289: FOR updble_order_adj IN debug_updatable_order_adj LOOP
4290: oe_debug_pub.add( '************************************************************************');
4291: oe_debug_pub.add('LIST_LINE_ID : '||updble_order_adj.list_line_id);
4292: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_order_adj.order_qty_adj_amt);
4293: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_order_adj.adjustment_amount);
4294: oe_debug_pub.add( '************************************************************************');
4287: IF l_debug_level > 0 THEN
4288: oe_debug_pub.add('UPDATABLE ORDER LEVEL ADJUSTMENTS :');
4289: FOR updble_order_adj IN debug_updatable_order_adj LOOP
4290: oe_debug_pub.add( '************************************************************************');
4291: oe_debug_pub.add('LIST_LINE_ID : '||updble_order_adj.list_line_id);
4292: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_order_adj.order_qty_adj_amt);
4293: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_order_adj.adjustment_amount);
4294: oe_debug_pub.add( '************************************************************************');
4295: END LOOP;
4288: oe_debug_pub.add('UPDATABLE ORDER LEVEL ADJUSTMENTS :');
4289: FOR updble_order_adj IN debug_updatable_order_adj LOOP
4290: oe_debug_pub.add( '************************************************************************');
4291: oe_debug_pub.add('LIST_LINE_ID : '||updble_order_adj.list_line_id);
4292: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_order_adj.order_qty_adj_amt);
4293: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_order_adj.adjustment_amount);
4294: oe_debug_pub.add( '************************************************************************');
4295: END LOOP;
4296: oe_debug_pub.add('UPDATABLE LINE LEVEL ADJUSTMENTS :');
4289: FOR updble_order_adj IN debug_updatable_order_adj LOOP
4290: oe_debug_pub.add( '************************************************************************');
4291: oe_debug_pub.add('LIST_LINE_ID : '||updble_order_adj.list_line_id);
4292: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_order_adj.order_qty_adj_amt);
4293: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_order_adj.adjustment_amount);
4294: oe_debug_pub.add( '************************************************************************');
4295: END LOOP;
4296: oe_debug_pub.add('UPDATABLE LINE LEVEL ADJUSTMENTS :');
4297: FOR updble_line_adj IN debug_updatable_line_adj LOOP
4290: oe_debug_pub.add( '************************************************************************');
4291: oe_debug_pub.add('LIST_LINE_ID : '||updble_order_adj.list_line_id);
4292: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_order_adj.order_qty_adj_amt);
4293: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_order_adj.adjustment_amount);
4294: oe_debug_pub.add( '************************************************************************');
4295: END LOOP;
4296: oe_debug_pub.add('UPDATABLE LINE LEVEL ADJUSTMENTS :');
4297: FOR updble_line_adj IN debug_updatable_line_adj LOOP
4298: oe_debug_pub.add( '************************************************************************');
4292: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_order_adj.order_qty_adj_amt);
4293: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_order_adj.adjustment_amount);
4294: oe_debug_pub.add( '************************************************************************');
4295: END LOOP;
4296: oe_debug_pub.add('UPDATABLE LINE LEVEL ADJUSTMENTS :');
4297: FOR updble_line_adj IN debug_updatable_line_adj LOOP
4298: oe_debug_pub.add( '************************************************************************');
4299: oe_debug_pub.add('LIST_LINE_ID : '||updble_line_adj.list_line_id);
4300: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_line_adj.order_qty_adj_amt);
4294: oe_debug_pub.add( '************************************************************************');
4295: END LOOP;
4296: oe_debug_pub.add('UPDATABLE LINE LEVEL ADJUSTMENTS :');
4297: FOR updble_line_adj IN debug_updatable_line_adj LOOP
4298: oe_debug_pub.add( '************************************************************************');
4299: oe_debug_pub.add('LIST_LINE_ID : '||updble_line_adj.list_line_id);
4300: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_line_adj.order_qty_adj_amt);
4301: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_line_adj.adjustment_amount);
4302: oe_debug_pub.add( '************************************************************************');
4295: END LOOP;
4296: oe_debug_pub.add('UPDATABLE LINE LEVEL ADJUSTMENTS :');
4297: FOR updble_line_adj IN debug_updatable_line_adj LOOP
4298: oe_debug_pub.add( '************************************************************************');
4299: oe_debug_pub.add('LIST_LINE_ID : '||updble_line_adj.list_line_id);
4300: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_line_adj.order_qty_adj_amt);
4301: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_line_adj.adjustment_amount);
4302: oe_debug_pub.add( '************************************************************************');
4303: END LOOP;
4296: oe_debug_pub.add('UPDATABLE LINE LEVEL ADJUSTMENTS :');
4297: FOR updble_line_adj IN debug_updatable_line_adj LOOP
4298: oe_debug_pub.add( '************************************************************************');
4299: oe_debug_pub.add('LIST_LINE_ID : '||updble_line_adj.list_line_id);
4300: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_line_adj.order_qty_adj_amt);
4301: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_line_adj.adjustment_amount);
4302: oe_debug_pub.add( '************************************************************************');
4303: END LOOP;
4304: END IF;
4297: FOR updble_line_adj IN debug_updatable_line_adj LOOP
4298: oe_debug_pub.add( '************************************************************************');
4299: oe_debug_pub.add('LIST_LINE_ID : '||updble_line_adj.list_line_id);
4300: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_line_adj.order_qty_adj_amt);
4301: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_line_adj.adjustment_amount);
4302: oe_debug_pub.add( '************************************************************************');
4303: END LOOP;
4304: END IF;
4305:
4298: oe_debug_pub.add( '************************************************************************');
4299: oe_debug_pub.add('LIST_LINE_ID : '||updble_line_adj.list_line_id);
4300: oe_debug_pub.add('ORDER_QTY_ADJ_AMT: '||updble_line_adj.order_qty_adj_amt);
4301: oe_debug_pub.add('ADJUSTMENT_AMOUNT: '||updble_line_adj.adjustment_amount);
4302: oe_debug_pub.add( '************************************************************************');
4303: END LOOP;
4304: END IF;
4305:
4306: --bug 3836854
4305:
4306: --bug 3836854
4307: for i in upd_adj('ORDER') loop
4308: IF l_debug_level > 0 THEN
4309: oe_debug_pub.add('within upd_adj cursor for order');
4310: oe_debug_pub.add('l_hdr_price_flag : '||l_hdr_price_flag);
4311: IF l_no_summary_line THEN
4312: oe_debug_pub.add('l_no_summary_line : '|| 'TRUE');
4313: ELSE
4306: --bug 3836854
4307: for i in upd_adj('ORDER') loop
4308: IF l_debug_level > 0 THEN
4309: oe_debug_pub.add('within upd_adj cursor for order');
4310: oe_debug_pub.add('l_hdr_price_flag : '||l_hdr_price_flag);
4311: IF l_no_summary_line THEN
4312: oe_debug_pub.add('l_no_summary_line : '|| 'TRUE');
4313: ELSE
4314: oe_debug_pub.add('l_no_summary_line : '|| 'FALSE');
4308: IF l_debug_level > 0 THEN
4309: oe_debug_pub.add('within upd_adj cursor for order');
4310: oe_debug_pub.add('l_hdr_price_flag : '||l_hdr_price_flag);
4311: IF l_no_summary_line THEN
4312: oe_debug_pub.add('l_no_summary_line : '|| 'TRUE');
4313: ELSE
4314: oe_debug_pub.add('l_no_summary_line : '|| 'FALSE');
4315: END IF;
4316: END IF;
4310: oe_debug_pub.add('l_hdr_price_flag : '||l_hdr_price_flag);
4311: IF l_no_summary_line THEN
4312: oe_debug_pub.add('l_no_summary_line : '|| 'TRUE');
4313: ELSE
4314: oe_debug_pub.add('l_no_summary_line : '|| 'FALSE');
4315: END IF;
4316: END IF;
4317: IF nvl(l_hdr_price_flag,'N') <> 'N' OR NOT l_no_summary_line THEN
4318: IF l_debug_level > 0 THEN
4315: END IF;
4316: END IF;
4317: IF nvl(l_hdr_price_flag,'N') <> 'N' OR NOT l_no_summary_line THEN
4318: IF l_debug_level > 0 THEN
4319: oe_debug_pub.add('within if l_hdr_price_flag');
4320: END IF;
4321: update_adj(i.price_adjustment_id, i.line_detail_index , l_debug_upd_order_adj_tbl);
4322: IF l_debug_level > 0 THEN
4323: oe_debug_pub.add('after update_adj order');
4319: oe_debug_pub.add('within if l_hdr_price_flag');
4320: END IF;
4321: update_adj(i.price_adjustment_id, i.line_detail_index , l_debug_upd_order_adj_tbl);
4322: IF l_debug_level > 0 THEN
4323: oe_debug_pub.add('after update_adj order');
4324: END IF;
4325: END IF;
4326: IF l_debug_level > 0 THEN
4327: oe_debug_pub.add( 'UPDATED '||SQL%ROWCOUNT||' ORDER LEVEL ADJUSTMENTS' , 3 ) ;
4323: oe_debug_pub.add('after update_adj order');
4324: END IF;
4325: END IF;
4326: IF l_debug_level > 0 THEN
4327: oe_debug_pub.add( 'UPDATED '||SQL%ROWCOUNT||' ORDER LEVEL ADJUSTMENTS' , 3 ) ;
4328: END IF;
4329: end loop;
4330:
4331: for i in upd_adj('LINE') loop
4329: end loop;
4330:
4331: for i in upd_adj('LINE') loop
4332: IF l_debug_level > 0 THEN
4333: oe_debug_pub.add('within upd_adj cursor for line');
4334: END IF;
4335: update_adj(i.price_adjustment_id, i.line_detail_index, l_debug_upd_line_adj_tbl);
4336: IF l_debug_level > 0 THEN
4337: oe_debug_pub.add('after update_adj for line');
4333: oe_debug_pub.add('within upd_adj cursor for line');
4334: END IF;
4335: update_adj(i.price_adjustment_id, i.line_detail_index, l_debug_upd_line_adj_tbl);
4336: IF l_debug_level > 0 THEN
4337: oe_debug_pub.add('after update_adj for line');
4338: oe_debug_pub.add( 'UPDATED '||SQL%ROWCOUNT||' LINE LEVEL ADJUSTMENTS' , 3 ) ;
4339: END IF;
4340: end loop;
4341: --bug 3836854
4334: END IF;
4335: update_adj(i.price_adjustment_id, i.line_detail_index, l_debug_upd_line_adj_tbl);
4336: IF l_debug_level > 0 THEN
4337: oe_debug_pub.add('after update_adj for line');
4338: oe_debug_pub.add( 'UPDATED '||SQL%ROWCOUNT||' LINE LEVEL ADJUSTMENTS' , 3 ) ;
4339: END IF;
4340: end loop;
4341: --bug 3836854
4342:
4341: --bug 3836854
4342:
4343: --pviprana: printing the list_line_ids of order level adjustments that were updated
4344: IF l_debug_level > 0 THEN
4345: oe_debug_pub.add( '************************************************************************');
4346: oe_debug_pub.add('UPDATED ORDER LEVEL ADJ LIST LINE IDS ARE:');
4347: IF(l_debug_upd_order_adj_tbl.count > 0) THEN
4348: FOR i IN l_debug_upd_order_adj_tbl.FIRST..l_debug_upd_order_adj_tbl.LAST LOOP
4349: oe_debug_pub.add(l_debug_upd_order_adj_tbl(i));
4342:
4343: --pviprana: printing the list_line_ids of order level adjustments that were updated
4344: IF l_debug_level > 0 THEN
4345: oe_debug_pub.add( '************************************************************************');
4346: oe_debug_pub.add('UPDATED ORDER LEVEL ADJ LIST LINE IDS ARE:');
4347: IF(l_debug_upd_order_adj_tbl.count > 0) THEN
4348: FOR i IN l_debug_upd_order_adj_tbl.FIRST..l_debug_upd_order_adj_tbl.LAST LOOP
4349: oe_debug_pub.add(l_debug_upd_order_adj_tbl(i));
4350: END LOOP;
4345: oe_debug_pub.add( '************************************************************************');
4346: oe_debug_pub.add('UPDATED ORDER LEVEL ADJ LIST LINE IDS ARE:');
4347: IF(l_debug_upd_order_adj_tbl.count > 0) THEN
4348: FOR i IN l_debug_upd_order_adj_tbl.FIRST..l_debug_upd_order_adj_tbl.LAST LOOP
4349: oe_debug_pub.add(l_debug_upd_order_adj_tbl(i));
4350: END LOOP;
4351: END IF;
4352: oe_debug_pub.add( '************************************************************************');
4353: END IF;
4348: FOR i IN l_debug_upd_order_adj_tbl.FIRST..l_debug_upd_order_adj_tbl.LAST LOOP
4349: oe_debug_pub.add(l_debug_upd_order_adj_tbl(i));
4350: END LOOP;
4351: END IF;
4352: oe_debug_pub.add( '************************************************************************');
4353: END IF;
4354:
4355:
4356: IF l_debug_level > 0 THEN
4354:
4355:
4356: IF l_debug_level > 0 THEN
4357: --pviprana: printing the list_line_ids of line level adjustments that were updated
4358: oe_debug_pub.add( '************************************************************************');
4359: oe_debug_pub.add('UPDATED LINE LEVEL ADJ LIST LINE IDS ARE:');
4360: IF(l_debug_upd_line_adj_tbl.count > 0) THEN
4361: FOR i IN l_debug_upd_line_adj_tbl.FIRST..l_debug_upd_line_adj_tbl.LAST LOOP
4362: oe_debug_pub.add(l_debug_upd_line_adj_tbl(i));
4355:
4356: IF l_debug_level > 0 THEN
4357: --pviprana: printing the list_line_ids of line level adjustments that were updated
4358: oe_debug_pub.add( '************************************************************************');
4359: oe_debug_pub.add('UPDATED LINE LEVEL ADJ LIST LINE IDS ARE:');
4360: IF(l_debug_upd_line_adj_tbl.count > 0) THEN
4361: FOR i IN l_debug_upd_line_adj_tbl.FIRST..l_debug_upd_line_adj_tbl.LAST LOOP
4362: oe_debug_pub.add(l_debug_upd_line_adj_tbl(i));
4363: END LOOP;
4358: oe_debug_pub.add( '************************************************************************');
4359: oe_debug_pub.add('UPDATED LINE LEVEL ADJ LIST LINE IDS ARE:');
4360: IF(l_debug_upd_line_adj_tbl.count > 0) THEN
4361: FOR i IN l_debug_upd_line_adj_tbl.FIRST..l_debug_upd_line_adj_tbl.LAST LOOP
4362: oe_debug_pub.add(l_debug_upd_line_adj_tbl(i));
4363: END LOOP;
4364: END IF;
4365: oe_debug_pub.add( '************************************************************************');
4366: END IF;
4361: FOR i IN l_debug_upd_line_adj_tbl.FIRST..l_debug_upd_line_adj_tbl.LAST LOOP
4362: oe_debug_pub.add(l_debug_upd_line_adj_tbl(i));
4363: END LOOP;
4364: END IF;
4365: oe_debug_pub.add( '************************************************************************');
4366: END IF;
4367:
4368: IF (p_Calculate_Flag <> QP_PREQ_GRP.G_CALCULATE_ONLY) THEN
4369: l_stmt:=7;
4596: );
4597:
4598:
4599: IF l_debug_level > 0 THEN
4600: oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' , 3 ) ;
4601: END IF;
4602:
4603: /*Insert ASSO for header level adj
4604: * Comment out--Not possible to have header level adjustments with associations
4699: RLTD.PRICING_STATUS_CODE = 'N');
4700:
4701:
4702: IF l_debug_level > 0 THEN
4703: oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' PRICE ADJ ASSOCS' , 3 ) ;
4704: END IF;
4705:
4706: End If; -- insert_adj
4707:
4738: --AND LDETS.LINE_INDEX = oe_order_pub.g_hdr.header_id + l_line_id_tbl(i)
4739: --AND ADJ.PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
4740: ) ;
4741: IF l_debug_level > 0 THEN
4742: oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||' ATTRIBUTES' ) ;
4743: END IF;
4744: l_stmt:=10;
4745: FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
4746: INSERT INTO OE_PRICE_ADJ_ATTRIBS
4807: FROM OE_PRICE_ADJ_ATTRIBS
4808: WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
4809: ));
4810: IF l_debug_level > 0 THEN
4811: oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' CHANGED ATTRIBS' ) ;
4812: END IF;
4813: End If;
4814: close del_attribs1;
4815: l_stmt:=11;
4844: --AND LDETS.LINE_INDEX = ADJ.HEADER_ID
4845: --AND ADJ.PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i)
4846: ) ;
4847: IF l_debug_level > 0 THEN
4848: oe_debug_pub.add( 'DELETED '||SQL%ROWCOUNT||'ORDER ADJ LEVEL ATTRIBUTES' ) ;
4849: END IF;
4850: l_stmt:=12;
4851: FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
4852: INSERT INTO OE_PRICE_ADJ_ATTRIBS
5122: AND QPLINE.PROCESS_STATUS <> 'NOT_VALID'
5123: );
5124: --13582129 End
5125: IF l_debug_level > 0 THEN
5126: oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ATTRIBS' , 3 ) ;
5127: END IF;
5128: END IF;
5129:
5130: ELSE
5129:
5130: ELSE
5131: --RETOBILL REQUEST EVENT
5132: --Different handling..
5133: oe_debug_pub.add('Retro:Calling Oe_Retrobill_Pvt.Process_Retrobill_Adjustments,Operation:'||G_RETROBILL_OPERATION);
5134: Oe_Retrobill_Pvt.Process_Retrobill_Adjustments(G_RETROBILL_OPERATION);
5135: END IF;
5136: --RT}
5137:
5139: WHEN NO_DATA_FOUND THEN
5140: NULL;
5141: WHEN OTHERS THEN
5142: IF l_debug_level > 0 THEN
5143: oe_debug_pub.add( 'WRONG IN REFRESH_ADJ'||SQLERRM||L_STMT , 1 ) ;
5144: END IF;
5145: raise fnd_api.g_exc_error;
5146:
5147: End REFRESH_ADJS;
5159: is
5160: l_req_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
5161: --l_line_index pls_integer := px_req_line_tbl.count;
5162: --
5163: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5164: --
5165: begin
5166: G_STMT_NO := 'copy_Header_to_request#10';
5167: IF l_debug_level > 0 THEN
5164: --
5165: begin
5166: G_STMT_NO := 'copy_Header_to_request#10';
5167: IF l_debug_level > 0 THEN
5168: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.COPY_HEADER_TO_REQUEST' , 1 ) ;
5169: END IF;
5170:
5171: --l_line_index := l_line_index+1;
5172: px_line_index := px_line_index+1;
5262: G_LIST_PRICE_OVERRIDE_FLAG_TBL(px_line_index):=NULL;
5263: G_CHARGE_PERIODICITY_CODE_TBL(px_line_index):=NULL;
5264:
5265: IF l_debug_level > 0 THEN
5266: oe_debug_pub.add( 'EXISTING OE_ORDER_PRICE_PVT.COPY_HEADER_TO_REQUEST' , 1 ) ;
5267: END IF;
5268:
5269: end copy_Header_to_request;
5270:
5298: --16206165 CW End
5299:
5300:
5301: --
5302: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5303: --
5304: begin
5305: G_STMT_NO := 'copy_Line_to_request#10';
5306: IF l_debug_level > 0 THEN
5303: --
5304: begin
5305: G_STMT_NO := 'copy_Line_to_request#10';
5306: IF l_debug_level > 0 THEN
5307: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.COPY_LINE_TO_REQUEST' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
5308: END IF;
5309:
5310:
5311: --RT{
5310:
5311: --RT{
5312: IF p_line_rec.retrobill_request_id IS NOT NULL AND p_pricing_events <> 'RETROBILL' THEN
5313: --Do not price this retrobill line with other events
5314: oe_debug_pub.add( 'LEAVING OE_ORDER_PRICE_PVT.COPY_LINE_TO_REQUEST' , 1 ) ;
5315: RETURN;
5316: END IF;
5317: --RT}
5318:
5319:
5320:
5321: -- px_line_index := px_line_index+1;
5322: IF l_debug_level > 0 THEN
5323: oe_debug_pub.add( 'LINE ID:FROM COPY LINE TO REQUEST:'||P_LINE_REC.LINE_ID ) ;
5324: END IF;
5325: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
5326: px_req_line_tbl(px_line_index).line_id := p_Line_rec.line_id;
5327: px_req_line_tbl(px_line_index).LINE_TYPE_CODE := 'LINE';
5368:
5369: l_req_line_rec.CURRENCY_CODE :=
5370: OE_Order_PUB.g_hdr.transactional_curr_code;
5371: IF l_debug_level > 0 THEN
5372: oe_debug_pub.add( 'UNIT PRICE'||P_LINE_REC.UNIT_LIST_PRICE||L_REQ_LINE_REC.CURRENCY_CODE , 3 ) ;
5373: END IF;
5374: If p_Line_rec.unit_list_price_per_pqty <> FND_API.G_MISS_NUM Then
5375: l_req_line_rec.UNIT_PRICE := p_Line_rec.unit_list_price_per_pqty;
5376: Elsif p_line_rec.unit_list_price <> FND_API.G_MISS_NUM THEN
5382: l_req_line_rec.adjusted_unit_price := nvl(p_line_rec.unit_selling_price_per_pqty,
5383: nvl(p_line_rec.unit_selling_price,p_line_rec.unit_list_price));
5384: --bug 2650505
5385: IF l_debug_level > 0 THEN
5386: oe_debug_pub.add( 'ADJ PRICE 1:'||L_REQ_LINE_REC.ADJUSTED_UNIT_PRICE ) ;
5387: END IF;
5388: if l_req_line_rec.adjusted_unit_price = FND_API.G_MISS_NUM then
5389: l_req_line_rec.adjusted_unit_price := NULL;
5390: end if;
5433: ,p_ou_id => p_line_rec.org_id
5434: ,p_subscription_template_id => p_line_rec.service_subs_template_id
5435: ,p_inventory_item_id => p_line_rec.inventory_item_id );
5436: IF (l_debug_level > 0) THEN
5437: oe_debug_pub.add('After UOM derivation for Subscription Item');
5438: End If;
5439: End IF;
5440: -- sol_ord_er #16014165 end
5441: END IF;
5439: End IF;
5440: -- sol_ord_er #16014165 end
5441: END IF;
5442: IF (l_debug_level > 0) THEN
5443: oe_debug_pub.add('uom quantity:'||l_req_line_rec.uom_quantity, 3);
5444: oe_debug_pub.add('service start date:'||l_req_line_rec.contract_start_Date, 3);
5445: oe_debug_pub.add('service end date:'||l_req_line_rec.contract_end_date, 3);
5446: END IF;
5447: -- bug 4642002 end
5440: -- sol_ord_er #16014165 end
5441: END IF;
5442: IF (l_debug_level > 0) THEN
5443: oe_debug_pub.add('uom quantity:'||l_req_line_rec.uom_quantity, 3);
5444: oe_debug_pub.add('service start date:'||l_req_line_rec.contract_start_Date, 3);
5445: oe_debug_pub.add('service end date:'||l_req_line_rec.contract_end_date, 3);
5446: END IF;
5447: -- bug 4642002 end
5448: End If;
5441: END IF;
5442: IF (l_debug_level > 0) THEN
5443: oe_debug_pub.add('uom quantity:'||l_req_line_rec.uom_quantity, 3);
5444: oe_debug_pub.add('service start date:'||l_req_line_rec.contract_start_Date, 3);
5445: oe_debug_pub.add('service end date:'||l_req_line_rec.contract_end_date, 3);
5446: END IF;
5447: -- bug 4642002 end
5448: End If;
5449:
5535:
5536: End If;
5537:
5538: IF l_debug_level > 0 THEN
5539: oe_debug_pub.add( 'ITEM TYPE CODE'||P_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
5540: END IF;
5541:
5542: l_req_line_rec.Active_date_first_type := 'ORD';
5543: l_req_line_rec.Active_date_first := OE_Order_Pub.G_HDR.Ordered_date;
5551: If l_req_line_rec.currency_code is NULL
5552: or l_req_line_rec.currency_code = FND_API.G_MISS_CHAR
5553: THEN
5554: IF l_debug_level > 0 THEN
5555: oe_debug_pub.add( 'CURRENCY CODE IS NULL' , 4 ) ;
5556: END IF;
5557: RAISE FND_API.G_EXC_ERROR;
5558: -- Question: Pricing Engine should populate g_currency_code?
5559: End If;
5576: -- bug 3658057
5577: l_fulfilled_qty := NVL(p_line_rec.fulfilled_quantity, NVL(p_line_rec.shipped_quantity, NVL(p_line_rec.ordered_quantity, 0)));
5578:
5579: IF l_debug_level > 0 THEN
5580: oe_debug_pub.add('l_fulfilled_qty:'|| l_fulfilled_qty);
5581: END IF;
5582:
5583: IF oe_line_util.dual_uom_control -- INVCONV Process_Characteristics
5584: (p_line_rec.inventory_item_id
5589: -- IF l_item_rec.ont_pricing_qty_source = 1 THEN -- INVCONV price by quantity 2
5590: IF p_line_rec.fulfillment_base IS NULL THEN --16206165 CW
5591: IF l_item_rec.ont_pricing_qty_source = 'S' THEN -- INVCONV
5592: IF l_debug_level > 0 THEN
5593: oe_debug_pub.add( 'DUAL - ONT_PRICING_QTY_SOURCE = S IN OEXVOPRB.PLS ' ) ;
5594: END IF;
5595: l_req_line_rec.LINE_QUANTITY := p_Line_rec.Ordered_quantity2 ;
5596: l_req_line_rec.LINE_UOM_CODE := p_Line_rec.Ordered_quantity_uom2 ;
5597: G_catchweight_qty_tbl(px_line_index) := p_line_rec.shipped_quantity2;
5602: END IF;
5603:
5604:
5605: IF l_debug_level > 0 THEN
5606: oe_debug_pub.add('OM Order Qty:'||p_line_rec.ordered_quantity);
5607: oe_debug_pub.add('actual order qty :'|| g_actual_order_qty_tbl(px_line_index) ) ;
5608: END IF;
5609:
5610: else
5603:
5604:
5605: IF l_debug_level > 0 THEN
5606: oe_debug_pub.add('OM Order Qty:'||p_line_rec.ordered_quantity);
5607: oe_debug_pub.add('actual order qty :'|| g_actual_order_qty_tbl(px_line_index) ) ;
5608: END IF;
5609:
5610: else
5611: l_req_line_rec.LINE_QUANTITY := p_Line_rec.Ordered_quantity ;
5647: (p_item_id => p_Line_rec.inventory_item_id,
5648: p_from_uom_code => p_Line_rec.order_quantity_uom,
5649: p_to_uom_code => l_item_rec.primary_uom_code,
5650: p_from_qty => p_Line_rec.Ordered_quantity);
5651: oe_debug_pub.ADD('LINE_QUANTITY:'||l_req_line_rec.LINE_QUANTITY);
5652: l_req_line_rec.LINE_UOM_CODE := l_item_rec.primary_uom_code ;
5653:
5654: /** Eventhough Receiving Team is not supporting Fulfillment by Secondary , for referenced RMA the Credit Memo should not
5655: exceed Invoice, thats the reason the non-Referenced RMA pricing will be in sync with the base order pricing **/
5676: END IF;
5677:
5678:
5679: IF l_debug_level > 0 THEN
5680: oe_debug_pub.ADD('Catch Weight Re-Pricing Primary UOM:'||l_item_rec.primary_uom_code);
5681: oe_debug_pub.ADD('Catch Weight Re-Pricing Secondary UOM'||l_item_rec.secondary_uom_code);
5682: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered Quantity Uom:'||p_Line_rec.order_quantity_uom);
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5677:
5678:
5679: IF l_debug_level > 0 THEN
5680: oe_debug_pub.ADD('Catch Weight Re-Pricing Primary UOM:'||l_item_rec.primary_uom_code);
5681: oe_debug_pub.ADD('Catch Weight Re-Pricing Secondary UOM'||l_item_rec.secondary_uom_code);
5682: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered Quantity Uom:'||p_Line_rec.order_quantity_uom);
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5678:
5679: IF l_debug_level > 0 THEN
5680: oe_debug_pub.ADD('Catch Weight Re-Pricing Primary UOM:'||l_item_rec.primary_uom_code);
5681: oe_debug_pub.ADD('Catch Weight Re-Pricing Secondary UOM'||l_item_rec.secondary_uom_code);
5682: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered Quantity Uom:'||p_Line_rec.order_quantity_uom);
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5679: IF l_debug_level > 0 THEN
5680: oe_debug_pub.ADD('Catch Weight Re-Pricing Primary UOM:'||l_item_rec.primary_uom_code);
5681: oe_debug_pub.ADD('Catch Weight Re-Pricing Secondary UOM'||l_item_rec.secondary_uom_code);
5682: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered Quantity Uom:'||p_Line_rec.order_quantity_uom);
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5687: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req UOM:'||l_req_line_rec.LINE_UOM_CODE);
5680: oe_debug_pub.ADD('Catch Weight Re-Pricing Primary UOM:'||l_item_rec.primary_uom_code);
5681: oe_debug_pub.ADD('Catch Weight Re-Pricing Secondary UOM'||l_item_rec.secondary_uom_code);
5682: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered Quantity Uom:'||p_Line_rec.order_quantity_uom);
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5687: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req UOM:'||l_req_line_rec.LINE_UOM_CODE);
5688: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Shipping Qty:'||l_ref_line_rec.Shipping_quantity);
5681: oe_debug_pub.ADD('Catch Weight Re-Pricing Secondary UOM'||l_item_rec.secondary_uom_code);
5682: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered Quantity Uom:'||p_Line_rec.order_quantity_uom);
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5687: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req UOM:'||l_req_line_rec.LINE_UOM_CODE);
5688: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Shipping Qty:'||l_ref_line_rec.Shipping_quantity);
5689: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Ordered Qty:'||l_ref_line_rec.Ordered_quantity);
5682: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered Quantity Uom:'||p_Line_rec.order_quantity_uom);
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5687: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req UOM:'||l_req_line_rec.LINE_UOM_CODE);
5688: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Shipping Qty:'||l_ref_line_rec.Shipping_quantity);
5689: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Ordered Qty:'||l_ref_line_rec.Ordered_quantity);
5690: END IF;
5683: oe_debug_pub.ADD('Catch Weight Re-Pricing Ordered_quantity:'||p_Line_rec.Ordered_quantity);
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5687: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req UOM:'||l_req_line_rec.LINE_UOM_CODE);
5688: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Shipping Qty:'||l_ref_line_rec.Shipping_quantity);
5689: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Ordered Qty:'||l_ref_line_rec.Ordered_quantity);
5690: END IF;
5691:
5684: oe_debug_pub.ADD('Catch Weight Re-Pricing Catch Weight Qty:'||G_catchweight_qty_tbl(px_line_index));
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5687: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req UOM:'||l_req_line_rec.LINE_UOM_CODE);
5688: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Shipping Qty:'||l_ref_line_rec.Shipping_quantity);
5689: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Ordered Qty:'||l_ref_line_rec.Ordered_quantity);
5690: END IF;
5691:
5692: --16206165 CW end
5685: oe_debug_pub.ADD('Catch Weight Re-Pricing Actual Ordered Qty:'||g_actual_order_qty_tbl(px_line_index));
5686: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req Qty:'||l_req_line_rec.LINE_QUANTITY);
5687: oe_debug_pub.ADD('Catch Weight Re-Pricing Line Req UOM:'||l_req_line_rec.LINE_UOM_CODE);
5688: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Shipping Qty:'||l_ref_line_rec.Shipping_quantity);
5689: oe_debug_pub.ADD('Catch Weight Re-Pricing Ref Line Ordered Qty:'||l_ref_line_rec.Ordered_quantity);
5690: END IF;
5691:
5692: --16206165 CW end
5693:
5691:
5692: --16206165 CW end
5693:
5694: IF l_debug_level > 0 THEN
5695: oe_debug_pub.add( 'DUAL - PRICING QUANTITY IS : ' ||L_REQ_LINE_REC.LINE_QUANTITY ) ;
5696: END IF;
5697: IF l_debug_level > 0 THEN
5698: oe_debug_pub.add( 'DUAL - PRICING QUANTITY UOM IS : ' || L_REQ_LINE_REC.LINE_UOM_CODE )
5699: ;
5694: IF l_debug_level > 0 THEN
5695: oe_debug_pub.add( 'DUAL - PRICING QUANTITY IS : ' ||L_REQ_LINE_REC.LINE_QUANTITY ) ;
5696: END IF;
5697: IF l_debug_level > 0 THEN
5698: oe_debug_pub.add( 'DUAL - PRICING QUANTITY UOM IS : ' || L_REQ_LINE_REC.LINE_UOM_CODE )
5699: ;
5700: END IF;
5701: -- Pack J catchweight
5702: ELSIF OE_CODE_CONTROL.Code_Release_level >= '110510' THEN
5700: END IF;
5701: -- Pack J catchweight
5702: ELSIF OE_CODE_CONTROL.Code_Release_level >= '110510' THEN
5703: IF l_debug_level > 0 THEN
5704: oe_debug_pub.add('inventory_item_id:'|| p_line_rec.inventory_item_id);
5705: oe_debug_pub.add('ship_from_org_id :'|| p_line_rec.ship_from_org_id );
5706: END IF;
5707:
5708: IF (p_line_rec.inventory_item_id IS NOT NULL AND
5701: -- Pack J catchweight
5702: ELSIF OE_CODE_CONTROL.Code_Release_level >= '110510' THEN
5703: IF l_debug_level > 0 THEN
5704: oe_debug_pub.add('inventory_item_id:'|| p_line_rec.inventory_item_id);
5705: oe_debug_pub.add('ship_from_org_id :'|| p_line_rec.ship_from_org_id );
5706: END IF;
5707:
5708: IF (p_line_rec.inventory_item_id IS NOT NULL AND
5709: p_line_rec.inventory_item_id <> FND_API.G_MISS_NUM) AND
5712: x_item_rec := OE_Order_Cache.Load_Item (p_line_rec.inventory_item_id
5713: ,p_line_rec.ship_from_org_id);
5714: END IF;
5715: IF l_debug_level > 0 THEN
5716: oe_debug_pub.add('ont_pricing_qty_source:'|| l_item_rec.ont_pricing_qty_source);
5717: oe_debug_pub.add('tracking_quantity_ind:'|| l_item_rec.tracking_quantity_ind);
5718: oe_debug_pub.add('wms_enabled_flag:'|| l_item_rec.wms_enabled_flag);
5719: END IF;
5720: --IF x_item_rec.ont_pricing_qty_source = 1 AND -- INVCONV
5713: ,p_line_rec.ship_from_org_id);
5714: END IF;
5715: IF l_debug_level > 0 THEN
5716: oe_debug_pub.add('ont_pricing_qty_source:'|| l_item_rec.ont_pricing_qty_source);
5717: oe_debug_pub.add('tracking_quantity_ind:'|| l_item_rec.tracking_quantity_ind);
5718: oe_debug_pub.add('wms_enabled_flag:'|| l_item_rec.wms_enabled_flag);
5719: END IF;
5720: --IF x_item_rec.ont_pricing_qty_source = 1 AND -- INVCONV
5721: IF x_item_rec.ont_pricing_qty_source = 'S' AND -- INVCONV
5714: END IF;
5715: IF l_debug_level > 0 THEN
5716: oe_debug_pub.add('ont_pricing_qty_source:'|| l_item_rec.ont_pricing_qty_source);
5717: oe_debug_pub.add('tracking_quantity_ind:'|| l_item_rec.tracking_quantity_ind);
5718: oe_debug_pub.add('wms_enabled_flag:'|| l_item_rec.wms_enabled_flag);
5719: END IF;
5720: --IF x_item_rec.ont_pricing_qty_source = 1 AND -- INVCONV
5721: IF x_item_rec.ont_pricing_qty_source = 'S' AND -- INVCONV
5722: x_item_rec.tracking_quantity_ind = 'P' and
5726: END IF;
5727: IF p_Line_rec.Ordered_quantity2 IS NOT NULL THEN
5728: /*<< This should be possible for referenced returns if ordered qty2 is populated based on shipped_quantity2>> */
5729: IF l_debug_level > 0 THEN
5730: oe_debug_pub.add('Ordered_quantity2 is NOT null ');
5731: END IF;
5732: l_req_line_rec.LINE_QUANTITY := p_Line_rec.Ordered_quantity2 ;
5733: l_req_line_rec.LINE_UOM_CODE := p_Line_rec.Ordered_quantity_uom2 ;
5734: g_actual_order_qty_tbl(px_line_index) := l_fulfilled_qty; -- bug 3658057
5733: l_req_line_rec.LINE_UOM_CODE := p_Line_rec.Ordered_quantity_uom2 ;
5734: g_actual_order_qty_tbl(px_line_index) := l_fulfilled_qty; -- bug 3658057
5735: ELSE
5736: IF l_debug_level > 0 THEN
5737: oe_debug_pub.add('Ordered_quantity2 is null');
5738: END IF;
5739:
5740: l_dummy := WMS_CATCH_WEIGHT_GRP.Get_Default_Secondary_Quantity (
5741: p_api_version => 1.0
5749: , x_secondary_uom_code => x_secondary_uom_code --returns default catch wt uom
5750: );
5751:
5752: IF l_debug_level > 0 THEN
5753: oe_debug_pub.add('x_secondary_qauntity:'|| x_secondary_quantity);
5754: oe_debug_pub.add('x_secondary_uom_code:'|| x_secondary_uom_code);
5755: END IF;
5756: /* Populating x_secondary_uom_code with the value in x_item_rec, because wms api is returning null */
5757: x_secondary_uom_code := x_item_rec.secondary_uom_code;
5750: );
5751:
5752: IF l_debug_level > 0 THEN
5753: oe_debug_pub.add('x_secondary_qauntity:'|| x_secondary_quantity);
5754: oe_debug_pub.add('x_secondary_uom_code:'|| x_secondary_uom_code);
5755: END IF;
5756: /* Populating x_secondary_uom_code with the value in x_item_rec, because wms api is returning null */
5757: x_secondary_uom_code := x_item_rec.secondary_uom_code;
5758: IF l_debug_level > 0 THEN
5755: END IF;
5756: /* Populating x_secondary_uom_code with the value in x_item_rec, because wms api is returning null */
5757: x_secondary_uom_code := x_item_rec.secondary_uom_code;
5758: IF l_debug_level > 0 THEN
5759: oe_debug_pub.add('after populating from x_item_rec :');
5760: oe_debug_pub.add('x_secondary_uom_code : '||x_secondary_uom_code);
5761: END IF;
5762: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5763: IF l_debug_level > 0 THEN
5756: /* Populating x_secondary_uom_code with the value in x_item_rec, because wms api is returning null */
5757: x_secondary_uom_code := x_item_rec.secondary_uom_code;
5758: IF l_debug_level > 0 THEN
5759: oe_debug_pub.add('after populating from x_item_rec :');
5760: oe_debug_pub.add('x_secondary_uom_code : '||x_secondary_uom_code);
5761: END IF;
5762: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5763: IF l_debug_level > 0 THEN
5764: oe_debug_pub.add('Error getting secondary UOM/quantity from WMS API. Return Status: '||x_return_status||' msg_count:'||x_msg_count);
5760: oe_debug_pub.add('x_secondary_uom_code : '||x_secondary_uom_code);
5761: END IF;
5762: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5763: IF l_debug_level > 0 THEN
5764: oe_debug_pub.add('Error getting secondary UOM/quantity from WMS API. Return Status: '||x_return_status||' msg_count:'||x_msg_count);
5765: END IF;
5766: oe_msg_pub.transfer_msg_stack;
5767: RAISE FND_API.G_EXC_ERROR;
5768: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5766: oe_msg_pub.transfer_msg_stack;
5767: RAISE FND_API.G_EXC_ERROR;
5768: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5769: IF l_debug_level > 0 THEN
5770: oe_debug_pub.add('Error getting secondary UOM/quantity from WMS API. Return Status: '||x_return_status||' msg_count:'||x_msg_count);
5771: END IF;
5772: oe_msg_pub.transfer_msg_stack;
5773: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5774: END IF;
5779: g_actual_order_qty_tbl(px_line_index) := l_fulfilled_qty; -- bug3658057
5780: END IF; -- end check for ordered_quantity2
5781:
5782: IF l_debug_level > 0 THEN
5783: oe_debug_pub.add('fulfilled_quantity:'|| p_line_rec.fulfilled_quantity);
5784: oe_debug_pub.add('shipped_quantity:'|| p_line_rec.shipped_quantity);
5785: oe_debug_pub.add('shipped_quantity2 :'|| p_line_rec.shipped_quantity2);
5786: oe_debug_pub.add('source_type_code :'|| p_line_rec.source_type_code);
5787: oe_debug_pub.add('line_category_code :'|| p_line_rec.line_category_code);
5780: END IF; -- end check for ordered_quantity2
5781:
5782: IF l_debug_level > 0 THEN
5783: oe_debug_pub.add('fulfilled_quantity:'|| p_line_rec.fulfilled_quantity);
5784: oe_debug_pub.add('shipped_quantity:'|| p_line_rec.shipped_quantity);
5785: oe_debug_pub.add('shipped_quantity2 :'|| p_line_rec.shipped_quantity2);
5786: oe_debug_pub.add('source_type_code :'|| p_line_rec.source_type_code);
5787: oe_debug_pub.add('line_category_code :'|| p_line_rec.line_category_code);
5788: oe_debug_pub.add('reference_line_id :'|| p_line_rec.reference_line_id);
5781:
5782: IF l_debug_level > 0 THEN
5783: oe_debug_pub.add('fulfilled_quantity:'|| p_line_rec.fulfilled_quantity);
5784: oe_debug_pub.add('shipped_quantity:'|| p_line_rec.shipped_quantity);
5785: oe_debug_pub.add('shipped_quantity2 :'|| p_line_rec.shipped_quantity2);
5786: oe_debug_pub.add('source_type_code :'|| p_line_rec.source_type_code);
5787: oe_debug_pub.add('line_category_code :'|| p_line_rec.line_category_code);
5788: oe_debug_pub.add('reference_line_id :'|| p_line_rec.reference_line_id);
5789: END IF;
5782: IF l_debug_level > 0 THEN
5783: oe_debug_pub.add('fulfilled_quantity:'|| p_line_rec.fulfilled_quantity);
5784: oe_debug_pub.add('shipped_quantity:'|| p_line_rec.shipped_quantity);
5785: oe_debug_pub.add('shipped_quantity2 :'|| p_line_rec.shipped_quantity2);
5786: oe_debug_pub.add('source_type_code :'|| p_line_rec.source_type_code);
5787: oe_debug_pub.add('line_category_code :'|| p_line_rec.line_category_code);
5788: oe_debug_pub.add('reference_line_id :'|| p_line_rec.reference_line_id);
5789: END IF;
5790:
5783: oe_debug_pub.add('fulfilled_quantity:'|| p_line_rec.fulfilled_quantity);
5784: oe_debug_pub.add('shipped_quantity:'|| p_line_rec.shipped_quantity);
5785: oe_debug_pub.add('shipped_quantity2 :'|| p_line_rec.shipped_quantity2);
5786: oe_debug_pub.add('source_type_code :'|| p_line_rec.source_type_code);
5787: oe_debug_pub.add('line_category_code :'|| p_line_rec.line_category_code);
5788: oe_debug_pub.add('reference_line_id :'|| p_line_rec.reference_line_id);
5789: END IF;
5790:
5791: IF p_line_rec.shipped_quantity IS NOT NULL AND
5784: oe_debug_pub.add('shipped_quantity:'|| p_line_rec.shipped_quantity);
5785: oe_debug_pub.add('shipped_quantity2 :'|| p_line_rec.shipped_quantity2);
5786: oe_debug_pub.add('source_type_code :'|| p_line_rec.source_type_code);
5787: oe_debug_pub.add('line_category_code :'|| p_line_rec.line_category_code);
5788: oe_debug_pub.add('reference_line_id :'|| p_line_rec.reference_line_id);
5789: END IF;
5790:
5791: IF p_line_rec.shipped_quantity IS NOT NULL AND
5792: p_line_rec.shipped_quantity <> FND_API.G_MISS_NUM THEN
5816: G_catchweight_qty_tbl(px_line_index) :=l_shipped_quantity2;
5817: END IF; --check for reference_line_id
5818: ELSE /*<< raise error here if shipped_quantity exists but not shipped_quantity2>> -- This error should not be raised for returns and dropship orders. */
5819: IF l_debug_level > 0 THEN
5820: oe_debug_pub.add('Shipped_quantity2 is null and not dropship, return- Raise error');
5821: END IF;
5822: FND_MESSAGE.SET_NAME('ONT','ONT_CATCHWEIGHT_QTY2_REQUIRED');
5823: OE_MSG_PUB.Add;
5824: RAISE FND_API.G_EXC_ERROR;
5851: -- Override List Price
5852: END IF; -- bug 4938837
5853:
5854: IF l_debug_level > 0 THEN
5855: oe_debug_pub.add('list_price_override_flag:'||l_req_line_rec.list_price_override_flag);
5856: oe_debug_pub.add('unit_price:'||l_req_line_rec.unit_price);
5857: oe_debug_pub.add('line_unit_price:'||l_req_line_rec.line_unit_price);
5858: oe_debug_pub.add('unit_list_price_per_pqty:'||p_line_rec.unit_list_price_per_pqty);
5859: oe_debug_pub.add('original_list_price:'||p_line_rec.original_list_price||':unit_list_price:'||p_line_rec.unit_list_price);
5852: END IF; -- bug 4938837
5853:
5854: IF l_debug_level > 0 THEN
5855: oe_debug_pub.add('list_price_override_flag:'||l_req_line_rec.list_price_override_flag);
5856: oe_debug_pub.add('unit_price:'||l_req_line_rec.unit_price);
5857: oe_debug_pub.add('line_unit_price:'||l_req_line_rec.line_unit_price);
5858: oe_debug_pub.add('unit_list_price_per_pqty:'||p_line_rec.unit_list_price_per_pqty);
5859: oe_debug_pub.add('original_list_price:'||p_line_rec.original_list_price||':unit_list_price:'||p_line_rec.unit_list_price);
5860: END IF;
5853:
5854: IF l_debug_level > 0 THEN
5855: oe_debug_pub.add('list_price_override_flag:'||l_req_line_rec.list_price_override_flag);
5856: oe_debug_pub.add('unit_price:'||l_req_line_rec.unit_price);
5857: oe_debug_pub.add('line_unit_price:'||l_req_line_rec.line_unit_price);
5858: oe_debug_pub.add('unit_list_price_per_pqty:'||p_line_rec.unit_list_price_per_pqty);
5859: oe_debug_pub.add('original_list_price:'||p_line_rec.original_list_price||':unit_list_price:'||p_line_rec.unit_list_price);
5860: END IF;
5861: -- bug 2812566, set price_flag to be 'N' when line is cancelled
5854: IF l_debug_level > 0 THEN
5855: oe_debug_pub.add('list_price_override_flag:'||l_req_line_rec.list_price_override_flag);
5856: oe_debug_pub.add('unit_price:'||l_req_line_rec.unit_price);
5857: oe_debug_pub.add('line_unit_price:'||l_req_line_rec.line_unit_price);
5858: oe_debug_pub.add('unit_list_price_per_pqty:'||p_line_rec.unit_list_price_per_pqty);
5859: oe_debug_pub.add('original_list_price:'||p_line_rec.original_list_price||':unit_list_price:'||p_line_rec.unit_list_price);
5860: END IF;
5861: -- bug 2812566, set price_flag to be 'N' when line is cancelled
5862: IF p_line_rec.ordered_quantity = 0 THEN
5855: oe_debug_pub.add('list_price_override_flag:'||l_req_line_rec.list_price_override_flag);
5856: oe_debug_pub.add('unit_price:'||l_req_line_rec.unit_price);
5857: oe_debug_pub.add('line_unit_price:'||l_req_line_rec.line_unit_price);
5858: oe_debug_pub.add('unit_list_price_per_pqty:'||p_line_rec.unit_list_price_per_pqty);
5859: oe_debug_pub.add('original_list_price:'||p_line_rec.original_list_price||':unit_list_price:'||p_line_rec.unit_list_price);
5860: END IF;
5861: -- bug 2812566, set price_flag to be 'N' when line is cancelled
5862: IF p_line_rec.ordered_quantity = 0 THEN
5863: l_req_line_rec.PRICE_FLAG := 'N';
5863: l_req_line_rec.PRICE_FLAG := 'N';
5864: END IF;
5865:
5866: IF l_debug_level > 0 THEN
5867: oe_debug_pub.add( 'POPULATING BULK INSERT TABLES' , 4 ) ;
5868: oe_debug_pub.add('inserting line index:'||l_req_line_rec.line_index);
5869: END IF;
5870:
5871: G_LINE_INDEX_TBL(px_line_index) := l_req_line_rec.LINE_INDEX;
5864: END IF;
5865:
5866: IF l_debug_level > 0 THEN
5867: oe_debug_pub.add( 'POPULATING BULK INSERT TABLES' , 4 ) ;
5868: oe_debug_pub.add('inserting line index:'||l_req_line_rec.line_index);
5869: END IF;
5870:
5871: G_LINE_INDEX_TBL(px_line_index) := l_req_line_rec.LINE_INDEX;
5872: G_LINE_TYPE_CODE_TBL(px_line_index) := l_req_line_rec.LINE_TYPE_CODE;
5876: G_ACTIVE_DATE_SECOND_TBL(px_line_index) := TRUNC(l_req_line_rec.ACTIVE_DATE_SECOND);
5877: G_ACTIVE_DATE_SECOND_TYPE_TBL(px_line_index):= l_req_line_rec.ACTIVE_DATE_SECOND_TYPE;
5878: --l_req_line_rec.priced_quantity := NULL;
5879: IF l_debug_level > 0 THEN
5880: oe_debug_pub.add( 'QUANTITY'||L_REQ_LINE_REC.LINE_QUANTITY||' '||L_REQ_LINE_REC.PRICED_QUANTITY , 3 ) ;
5881: END IF;
5882: IF l_debug_level > 0 THEN
5883: oe_debug_pub.add( 'PRICE FLAG'||L_REQ_LINE_REC.PRICE_FLAG ) ;
5884: END IF;
5879: IF l_debug_level > 0 THEN
5880: oe_debug_pub.add( 'QUANTITY'||L_REQ_LINE_REC.LINE_QUANTITY||' '||L_REQ_LINE_REC.PRICED_QUANTITY , 3 ) ;
5881: END IF;
5882: IF l_debug_level > 0 THEN
5883: oe_debug_pub.add( 'PRICE FLAG'||L_REQ_LINE_REC.PRICE_FLAG ) ;
5884: END IF;
5885: G_LINE_QUANTITY_TBL(px_line_index) := l_req_line_rec.LINE_QUANTITY;
5886: G_LINE_UOM_CODE_TBL(px_line_index) := l_req_line_rec.LINE_UOM_CODE;
5887: G_REQUEST_TYPE_CODE_TBL(px_line_index) := l_req_line_rec.REQUEST_TYPE_CODE;
5891: G_CONTRACT_END_DATE_TBL(px_line_index) := l_req_line_rec.CONTRACT_END_DATE;
5892: G_PRICED_UOM_CODE_TBL(px_line_index) := l_req_line_rec.PRICED_UOM_CODE;
5893: G_CURRENCY_CODE_TBL(px_line_index) := l_req_line_rec.CURRENCY_CODE;
5894: IF l_debug_level > 0 THEN
5895: oe_debug_pub.add( 'UNIT PRICE'||L_REQ_LINE_REC.UNIT_PRICE||' '||L_REQ_LINE_REC.ADJUSTED_UNIT_PRICE ) ;
5896: END IF;
5897: G_UNIT_PRICE_TBL(px_line_index) := l_req_line_rec.unit_price; -- AG
5898: G_PERCENT_PRICE_TBL(px_line_index) := l_req_line_rec.PERCENT_PRICE;
5899: G_ADJUSTED_UNIT_PRICE_TBL(px_line_index) := l_req_line_rec.ADJUSTED_UNIT_PRICE;
5900: G_PROCESSED_FLAG_TBL(px_line_index) := QP_PREQ_GRP.G_NOT_PROCESSED;
5901: G_PRICE_FLAG_TBL(px_line_index) := l_req_line_rec.PRICE_FLAG;
5902: G_LINE_ID_TBL(px_line_index) := l_req_line_rec.LINE_ID;
5903: IF l_debug_level > 0 THEN
5904: oe_debug_pub.add( 'LINE ID IN G_LINE_ID_TBL:'|| G_LINE_ID_TBL ( PX_LINE_INDEX ) ) ;
5905: END IF;
5906: G_ROUNDING_FLAG_TBL(px_line_index) := G_ROUNDING_FLAG; -- AG
5907: G_ROUNDING_FACTOR_TBL(px_line_index) := l_req_line_rec.ROUNDING_FACTOR;
5908: G_PROCESSING_ORDER_TBL(px_line_index) := NULL;
5923: -- Bug3380345
5924: IF G_LIST_PRICE_OVERRIDE_FLAG_TBL(px_line_index) = 'Y' AND
5925: p_pricing_events = 'PRICE' THEN
5926: IF l_debug_level > 0 THEN
5927: oe_debug_pub.add( 'setting process code as UPDATED for override list price');
5928: END IF;
5929: G_PRICING_STATUS_CODE_tbl(px_line_index) := QP_PREQ_GRP.G_STATUS_UPDATED;
5930: END IF;
5931:
5930: END IF;
5931:
5932:
5933: IF l_debug_level > 0 THEN
5934: oe_debug_pub.add( 'EXISTING OE_ORDER_PRICE_PVT.COPY_LINE_TO_REQUEST' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
5935: END IF;
5936:
5937: end copy_Line_to_request;
5938:
5940: IS
5941: l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
5942: l_return_status_Text varchar2(240) ;
5943: --
5944: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5945: --
5946: BEGIN
5947: IF l_debug_level > 0 THEN
5948: oe_debug_pub.add( 'BEFORE DIRECT INSERT INTO TEMP TABLE: BULK INSERT'||G_LINE_INDEX_TBL.COUNT , 1 ) ;
5944: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5945: --
5946: BEGIN
5947: IF l_debug_level > 0 THEN
5948: oe_debug_pub.add( 'BEFORE DIRECT INSERT INTO TEMP TABLE: BULK INSERT'||G_LINE_INDEX_TBL.COUNT , 1 ) ;
5949: oe_debug_pub.add('G_CHARGE_PERIODICITY_CODE_TBL.Count:'||G_CHARGE_PERIODICITY_CODE_TBL.COUNT);
5950: END IF;
5951: QP_PREQ_GRP.INSERT_LINES2
5952: (p_LINE_INDEX => G_LINE_INDEX_TBL,
5945: --
5946: BEGIN
5947: IF l_debug_level > 0 THEN
5948: oe_debug_pub.add( 'BEFORE DIRECT INSERT INTO TEMP TABLE: BULK INSERT'||G_LINE_INDEX_TBL.COUNT , 1 ) ;
5949: oe_debug_pub.add('G_CHARGE_PERIODICITY_CODE_TBL.Count:'||G_CHARGE_PERIODICITY_CODE_TBL.COUNT);
5950: END IF;
5951: QP_PREQ_GRP.INSERT_LINES2
5952: (p_LINE_INDEX => G_LINE_INDEX_TBL,
5953: p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
5993: x_status_text =>l_return_status_text);
5994:
5995: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5996: IF l_debug_level > 0 THEN
5997: oe_debug_pub.add( 'WRONG IN INSERT_LINES2'||L_RETURN_STATUS_TEXT , 1 ) ;
5998: END IF;
5999: FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS'); --bug#7149497
6000: FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
6001: OE_MSG_PUB.Add;
6038: l_return_status_text );
6039:
6040: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
6041: IF l_debug_level > 0 THEN
6042: oe_debug_pub.add( 'ERROR INSERTING INTO LINE ATTRS'||SQLERRM ) ;
6043: END IF;
6044: FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS'); --bug#7149497
6045: FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
6046: OE_MSG_PUB.Add;
6048: END IF;
6049:
6050: END IF;
6051: IF l_debug_level > 0 THEN
6052: oe_debug_pub.add( 'AFTER DIRECT INSERT INTO TEMP TABLE: BULK INSERT' , 1 ) ;
6053: END IF;
6054:
6055: EXCEPTION
6056: WHEN OTHERS THEN
6064: ) RETURN VARCHAR2
6065: IS
6066: l_total_lines NUMBER;
6067: l_num_changed_lines NUMBER;
6068: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6069: i pls_integer;
6070: BEGIN
6071:
6072: -- only one changed line, query that line
6080: where header_id = p_header_id;
6081: EXCEPTION
6082: WHEN OTHERS THEN
6083: IF l_debug_level > 0 THEN
6084: oe_debug_pub.add('could not find out nocopy total number of lines!');
6085: END IF;
6086: RETURN 'N';
6087: END;
6088:
6087: END;
6088:
6089: IF (l_total_lines > OE_Line_Adj_Util.G_CHANGED_LINE_TBL.count) THEN
6090: IF l_debug_level > 0 THEN
6091: oe_debug_pub.add('total lines larger than changed '||l_total_lines
6092: ||' '||OE_Line_Adj_Util.G_CHANGED_LINE_TBL.count);
6093: END IF;
6094: RETURN 'N';
6095: ELSE
6093: END IF;
6094: RETURN 'N';
6095: ELSE
6096: IF l_debug_level > 0 THEN
6097: oe_debug_pub.add('total lines smaller than or equal to changed '||l_total_lines
6098: ||' '||OE_Line_Adj_Util.G_CHANGED_LINE_TBL.count);
6099: END IF;
6100:
6101: l_num_changed_lines := 0;
6107: i:= Oe_Line_Adj_Util.G_CHANGED_LINE_TBL.Next(i);
6108: End Loop;
6109:
6110: IF l_debug_level > 0 THEN
6111: oe_debug_pub.add('total changed lines in this order '||l_num_changed_lines);
6112: END IF;
6113: IF (l_total_lines = l_num_changed_lines) THEN
6114: RETURN 'Y';
6115: ELSE
6130: x_line_tbl OUT NOCOPY OE_ORDER_PUB.LINE_TBL_TYPE ) AS
6131: i PLS_INTEGER;
6132: l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
6133: j PLS_INTEGER;
6134: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6135: BEGIN
6136: i := Oe_Line_Adj_Util.G_CHANGED_LINE_TBL.FIRST;
6137: j := 1;
6138: While i is Not Null Loop
6138: While i is Not Null Loop
6139: Begin
6140: --bug 3020702
6141: IF l_debug_level > 0 THEN
6142: oe_debug_pub.add('header_id:'||Oe_Line_Adj_Util.G_CHANGED_LINE_TBL(i).header_id);
6143: END IF;
6144: if oe_line_adj_util.G_CHANGED_LINE_TBL(i).header_id = p_header_id then
6145:
6146: IF l_debug_level > 0 THEN
6143: END IF;
6144: if oe_line_adj_util.G_CHANGED_LINE_TBL(i).header_id = p_header_id then
6145:
6146: IF l_debug_level > 0 THEN
6147: oe_debug_pub.add('query line_id:'||Oe_Line_Adj_Util.G_CHANGED_LINE_TBL(i).line_id);
6148: END IF;
6149: query_line(Oe_Line_Adj_Util.G_CHANGED_LINE_TBL(i).line_id,l_line_rec);
6150: x_line_tbl(j):=l_line_rec;
6151: j:=j+1;
6149: query_line(Oe_Line_Adj_Util.G_CHANGED_LINE_TBL(i).line_id,l_line_rec);
6150: x_line_tbl(j):=l_line_rec;
6151: j:=j+1;
6152:
6153: oe_debug_pub.add('link to line id:'|| l_line_rec.link_to_line_id);
6154:
6155: LOOP
6156: IF l_line_rec.link_to_line_id IS NOT null
6157: and l_line_rec.link_to_line_id <> FND_API.G_MISS_NUM
6175: end if;
6176: Exception
6177: When no_data_found Then
6178: IF l_debug_level > 0 THEN
6179: Oe_Debug_Pub.add('No data found for line id:'||Oe_Line_Adj_Util.G_CHANGED_LINE_TBL(i).line_id);
6180: END IF;
6181: End;
6182: i:= Oe_Line_Adj_Util.G_CHANGED_LINE_TBL.Next(i);
6183: End Loop;
6231: l_order_status_rec QP_UTIL_PUB.ORDER_LINES_STATUS_REC_TYPE;
6232: l_total_lines NUMBER;
6233: G_INT_CHANGED_LINE_ON Varchar2(3):= nvl(FND_PROFILE.VALUE('ONT_INTERNAL_CHANGED_LINE'),'Y');
6234: l_header_id2 NUMBER;
6235: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6236: l_check_line_flag varchar2(1);
6237: l_pass_line varchar2(1);
6238: --2740845 begin
6239: l_agreement_name varchar2(240);
6246:
6247: begin
6248:
6249: IF l_debug_level > 0 THEN
6250: oe_debug_pub.add('Entering oe_order_price_pvt.calulate_adjustments', 0.5); -- debug level changed to 0.5 for bug 13435459
6251: END IF;
6252:
6253: reset_all_tbls;
6254: --DELETE FROM QP_PREQ_LINES_TMP;
6298: query_header( p_header_id => p_header_id, x_header_rec=>oe_order_pub.g_hdr);
6299: Exception when no_data_found then
6300: x_return_status := 'NOOP';
6301: IF l_debug_level > 0 THEN
6302: oe_debug_pub.add( G_STMT_NO||'INVALID HEADER_ID '||P_HEADER_ID , 1 ) ;
6303: END IF;
6304: Return;
6305: End;
6306:
6308: j:=1;
6309:
6310: Begin
6311:
6312: oe_debug_pub.add('Before querying lines for header:'||p_Header_id||' event:'||p_pricing_events);
6313: -- How to determine whether all lines needs to be passed to Pricing Engine?
6314: -- 1. When BOOK button is pressed, all lines needs to be passed
6315: -- because caller wants line information back
6316: -- 2. When this procedure is called for G_PRICE_ADJ delayed request, pass all
6342: x_order_status_rec =>l_order_status_rec);
6343: -- bug 9728497 end
6344:
6345:
6346: oe_debug_pub.add(' All_lines_flag returned from pricing:'||l_order_status_rec.all_lines_flag);
6347: oe_debug_pub.add(' Changed_lines_flag returned from pricing:'||l_order_status_rec.Changed_lines_flag);
6348:
6349:
6350: If l_order_status_rec.ALL_LINES_FLAG = 'Y'
6343: -- bug 9728497 end
6344:
6345:
6346: oe_debug_pub.add(' All_lines_flag returned from pricing:'||l_order_status_rec.all_lines_flag);
6347: oe_debug_pub.add(' Changed_lines_flag returned from pricing:'||l_order_status_rec.Changed_lines_flag);
6348:
6349:
6350: If l_order_status_rec.ALL_LINES_FLAG = 'Y'
6351: and nvl(OE_LINE_ADJ_UTIL.G_SEND_ALL_LINES_FOR_DSP,'Y') = 'Y'
6369: OR p_pricing_events = 'SHIP'
6370: Then
6371: IF Need_Query_All_Lines(p_header_id) = 'Y' THEN
6372: IF l_debug_level > 0 THEN
6373: oe_debug_pub.add(' Need query all lines because all lines are changed');
6374: END IF;
6375: G_PASS_ALL_LINES := 'Y';
6376: ELSE
6377: -------------------------------------------------------------------
6376: ELSE
6377: -------------------------------------------------------------------
6378: --Pricing says passing only changed lines, use query_line
6379: --------------------------------------------------------------------
6380: oe_debug_pub.add('Query individual line changed:'||OE_Line_Adj_Util.G_CHANGED_LINE_TBL.count);
6381:
6382: --bug4529937 have put the code to query for changed lines in a procedure
6383: Query_Changed_Lines(p_header_id => p_header_id,
6384: x_line_tbl => l_line_tbl);
6392: If G_PASS_ALL_LINES in ('Y', 'R') Then
6393: --bug4529937 if the call is from mass change api, and there are more lines remaining to be processed after the current set, we would just pass the changed lines to pricing engine
6394: IF oe_mass_change_pvt.Lines_Remaining = 'Y' THEN
6395: IF l_debug_level > 0 THEN
6396: oe_debug_pub.add(' Query only changed line');
6397: END IF;
6398: Query_Changed_Lines(p_header_id => p_header_id, x_line_tbl => l_line_tbl);
6399: l_all_lines_from_db := FALSE;
6400: ELSE
6398: Query_Changed_Lines(p_header_id => p_header_id, x_line_tbl => l_line_tbl);
6399: l_all_lines_from_db := FALSE;
6400: ELSE
6401: IF l_debug_level > 0 THEN
6402: oe_debug_pub.add(' Pass all the lines');
6403: END IF;
6404: query_lines(p_header_id => p_Header_id, p_line_id => Null, x_line_tbl => l_Line_Tbl);
6405: --1472635
6406: l_all_lines_from_db := True;
6405: --1472635
6406: l_all_lines_from_db := True;
6407: END IF;
6408: End If;
6409: oe_debug_pub.add('line_count after querying:'||l_Line_Tbl.count);
6410:
6411: /* debug statement
6412: For i in l_Line_Tbl.first .. l_Line_Tbl.last Loop
6413: oe_debug_pub.add('line id in l_line_tbl!:'||l_Line_Tbl(i).line_id);
6409: oe_debug_pub.add('line_count after querying:'||l_Line_Tbl.count);
6410:
6411: /* debug statement
6412: For i in l_Line_Tbl.first .. l_Line_Tbl.last Loop
6413: oe_debug_pub.add('line id in l_line_tbl!:'||l_Line_Tbl(i).line_id);
6414: End Loop; */
6415:
6416: Exception when no_data_found then
6417: -- No need to process this order
6415:
6416: Exception when no_data_found then
6417: -- No need to process this order
6418: x_return_status := 'NOOP';
6419: oe_debug_pub.add(G_STMT_NO||'Invalid header_id '||p_Header_id,1);
6420: Return;
6421: End;
6422:
6423: ELSE -- Query the line Record
6427: query_lines(p_line_id =>p_line_id, p_header_id => Null, x_line_tbl=>l_Line_Tbl );
6428: Exception when no_data_found then
6429: -- No need to process this line
6430: x_return_status := 'NOOP';
6431: oe_debug_pub.add(G_STMT_NO||'Invalid line_id '||p_line_id,1);
6432: Return;
6433: End ;
6434: Else
6435: l_Line_Tbl := px_line_Tbl;
6442: query_header(l_line_tbl(1).header_id, oe_order_pub.g_hdr);
6443: Exception when no_data_found then
6444: -- No need to process this order
6445: x_return_status := 'NOOP';
6446: oe_debug_pub.add(G_STMT_NO||'Invalid header_id '||l_line_Tbl(1).Header_id,1);
6447: Return;
6448: End ;
6449: Else
6450: --Do Nothing since the flag says that the global record has been set
6526: l_Line_Tbl(line_Tbl_Index).service_reference_type_code='CUSTOMER_PRODUCT' AND
6527: l_line_Tbl(line_Tbl_Index).cancelled_flag = 'N' AND
6528: l_Line_Tbl(line_Tbl_Index).service_reference_line_id IS NOT NULL
6529: THEN
6530: oe_debug_pub.add('1828553: Line is a customer product');
6531: OE_SERVICE_UTIL.Get_Cust_Product_Line_Id
6532: ( x_return_status => l_return_status
6533: , p_reference_line_id => l_Line_Tbl(line_Tbl_Index).service_reference_line_id
6534: , p_customer_id => l_Line_Tbl(line_Tbl_Index).sold_to_org_id
6534: , p_customer_id => l_Line_Tbl(line_Tbl_Index).sold_to_org_id
6535: , x_cust_product_line_id => l_order_line_id
6536: );
6537: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
6538: oe_debug_pub.add('1828553: Success');
6539: oe_debug_pub.add('1828553: Service line id is ' || l_order_line_id);
6540: l_service_reference_line_id := l_order_line_id;
6541: ELSE
6542: oe_debug_pub.add('Not able to retrieve cust product line id');
6535: , x_cust_product_line_id => l_order_line_id
6536: );
6537: IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
6538: oe_debug_pub.add('1828553: Success');
6539: oe_debug_pub.add('1828553: Service line id is ' || l_order_line_id);
6540: l_service_reference_line_id := l_order_line_id;
6541: ELSE
6542: oe_debug_pub.add('Not able to retrieve cust product line id');
6543: RAISE NO_DATA_FOUND;
6538: oe_debug_pub.add('1828553: Success');
6539: oe_debug_pub.add('1828553: Service line id is ' || l_order_line_id);
6540: l_service_reference_line_id := l_order_line_id;
6541: ELSE
6542: oe_debug_pub.add('Not able to retrieve cust product line id');
6543: RAISE NO_DATA_FOUND;
6544: END IF;
6545: ELSE
6546: l_service_reference_line_id := l_Line_Tbl(line_Tbl_Index).service_reference_line_id;
6545: ELSE
6546: l_service_reference_line_id := l_Line_Tbl(line_Tbl_Index).service_reference_line_id;
6547: END IF;
6548:
6549: oe_debug_pub.add('1828553: l_Service_Reference_Line_Id: '||l_Service_Reference_line_id);
6550:
6551: --3273289{
6552: If(l_Service_Reference_Line_Id is NOT NULL) THEN
6553: --bug 3968023 to call the procedure Get_The_Parent_Line only if the event contains phase 1
6553: --bug 3968023 to call the procedure Get_The_Parent_Line only if the event contains phase 1
6554: BEGIN
6555: l_event_in_phase1 := 'N';
6556: --- bug# 9436193 : Start : using dynamic sql
6557: oe_debug_pub.add(' l_event_in_phase1 = '||l_event_in_phase1||' p_pricing_events = '||p_pricing_events);
6558: l_sql := 'SELECT ''Y'' FROM qp_pricing_phases p, qp_event_phases e WHERE p.pricing_phase_id=e.pricing_phase_id'
6559: ||' AND p.pricing_phase_id = 1 AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate)) AND trunc(nvl(e.end_date_active,sysdate))'
6560: ||' AND e.pricing_event_code in ('''||replace(trim(p_pricing_events),',',''',''')||''') ' ;
6561: oe_debug_pub.add(' l_sql = '||l_sql);
6557: oe_debug_pub.add(' l_event_in_phase1 = '||l_event_in_phase1||' p_pricing_events = '||p_pricing_events);
6558: l_sql := 'SELECT ''Y'' FROM qp_pricing_phases p, qp_event_phases e WHERE p.pricing_phase_id=e.pricing_phase_id'
6559: ||' AND p.pricing_phase_id = 1 AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate)) AND trunc(nvl(e.end_date_active,sysdate))'
6560: ||' AND e.pricing_event_code in ('''||replace(trim(p_pricing_events),',',''',''')||''') ' ;
6561: oe_debug_pub.add(' l_sql = '||l_sql);
6562: EXECUTE IMMEDIATE l_sql INTO l_event_in_phase1;
6563: oe_debug_pub.add('after l_sql: l_event_in_phase1 =>> '||l_event_in_phase1);
6564: /*
6565: SELECT 'Y' INTO l_event_in_phase1
6559: ||' AND p.pricing_phase_id = 1 AND trunc(sysdate) BETWEEN trunc(nvl(e.end_date_active,sysdate)) AND trunc(nvl(e.end_date_active,sysdate))'
6560: ||' AND e.pricing_event_code in ('''||replace(trim(p_pricing_events),',',''',''')||''') ' ;
6561: oe_debug_pub.add(' l_sql = '||l_sql);
6562: EXECUTE IMMEDIATE l_sql INTO l_event_in_phase1;
6563: oe_debug_pub.add('after l_sql: l_event_in_phase1 =>> '||l_event_in_phase1);
6564: /*
6565: SELECT 'Y' INTO l_event_in_phase1
6566: FROM qp_pricing_phases p,
6567: qp_event_phases e
6578: px_Line_Tbl => l_Line_Tbl) ;
6579: END IF;
6580: EXCEPTION
6581: WHEN NO_DATA_FOUND THEN
6582: oe_debug_pub.add('
6584:
6585: WHEN OTHERS THEN
6586: oe_debug_pub.add('
6582: oe_debug_pub.add('
6583: null;
6584:
6585: WHEN OTHERS THEN
6586: oe_debug_pub.add('
6588: --bug 3968023
6589: END IF;
6590: --3273289}
6590: --3273289}
6591: End If; --- IF (OE_Order_PUB.G_LINE.Service_Reference_Line_Id <> FND_API.G_MISS_NUM
6592:
6593: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
6594: oe_debug_pub.add('calling get_prg_lines 5647210 added mass change');
6595: IF ((p_line_id IS NOT NULL and p_control_rec.p_write_to_db) OR oe_mass_change_pvt.Lines_Remaining = 'Y')
6596: THEN
6597: Get_PRG_Lines(l_line_tbl(line_tbl_index).line_id, l_line_tbl, line_tbl_index);
6598: -- 3529369 In the case overriding the list price of servicable line the service lines will be
6627: AND l_line_tbl(line_tbl_index).cancelled_flag = 'N')
6628: THEN
6629: l_any_frozen_line := TRUE;
6630: x_any_frozen_line := TRUE;
6631: oe_debug_pub.add('Any frozen line is true');
6632: IF l_line_tbl(line_tbl_index).calculate_price_flag = 'P' THEN
6633: l_completely_frozen := FALSE;
6634: END IF;
6635: ELSIF l_line_tbl(line_tbl_index).calculate_price_flag = 'Y' THEN
6645: THEN
6646: --Do nothing for these invalid combinations
6647: --RETROBILL event must have retrobill_request_id
6648: --Request None retrobill event with retrobill_request_id should be ignored
6649: oe_debug_pub.add('VOPRB:event'||p_pricing_events||'retrobill id:'||l_line_tbl(line_tbl_index).retrobill_request_id);
6650: NULL;
6651: ELSE
6652: --RT{
6653: IF l_line_tbl(line_tbl_index).retrobill_request_id IS NOT NULL THEN
6659: --RT}
6660:
6661: G_STMT_NO := 'Build_Context for Line';
6662: If l_bypass_pricing = 'Y' OR l_line_tbl(line_tbl_index).calculate_price_flag = 'X' Then
6663: oe_debug_pub.add('Bypassing the qualifier build',1);
6664: l_check_line_flag := 'N';
6665: l_pass_line := 'Y';
6666: l_line_index := l_line_index + 1;
6667: Else
6664: l_check_line_flag := 'N';
6665: l_pass_line := 'Y';
6666: l_line_index := l_line_index + 1;
6667: Else
6668: oe_debug_pub.add('Before QP_Attr_Mapping_PUB.Build_Contexts for line',1);
6669: IF (G_PASS_ALL_LINES = 'R' and
6670: NOT OE_LINE_ADJ_UTIL.G_CHANGED_LINE_TBL.exists(mod(l_line_Tbl(line_tbl_index).line_id,G_BINARY_LIMIT))
6671: and OE_CODE_CONTROL.Get_Code_Release_Level >= '110509')
6672: THEN
6692: --like to check the l_pass_line is needed #bug 289804100.
6693: --QP will need to unconditionally build sourcing.
6694: --BTEA
6695:
6696: oe_debug_pub.add('p_header_id:'||nvl(p_header_id,-100));
6697:
6698: IF p_header_id IS NULL THEN
6699: l_check_line_flag := 'N';
6700: END IF;
6700: END IF;
6701:
6702: -- bug 3643645
6703: l_line_index := l_line_index + 1;
6704: oe_debug_pub.add('before build_context:'||l_line_index, 5);
6705: QP_Attr_Mapping_PUB.Build_Contexts(
6706: p_request_type_code => 'ONT',
6707: p_line_index => l_line_index,
6708: p_pricing_type_code => 'L',
6715: l_line_index := l_line_index - 1;
6716: END IF;
6717:
6718: if (l_debug_level > 0) Then
6719: oe_debug_pub.add('after build_context:'||l_line_index);
6720: end if;
6721: End If; -- bypass pricing
6722:
6723: IF l_debug_level > 0 THEN
6720: end if;
6721: End If; -- bypass pricing
6722:
6723: IF l_debug_level > 0 THEN
6724: oe_debug_pub.add('check line'||l_check_line_flag||' pass line:'||l_pass_line);
6725: END IF;
6726:
6727:
6728: If l_check_line_flag = 'N' or l_pass_line = 'Y' Then
6801: l_message_displayed := TRUE;
6802: End If;
6803:
6804: l_any_frozen_line:=FALSE;
6805: oe_debug_pub.add('BCT:ONT_LINE_FROZEN');
6806:
6807: Elsif l_all_lines_from_db = False Then
6808: --1472635
6809: --Didn't query from db, need to do that to check if
6807: Elsif l_all_lines_from_db = False Then
6808: --1472635
6809: --Didn't query from db, need to do that to check if
6810: --all other previously save lines is frozen
6811: oe_debug_pub.add('BCT all line from db is false');
6812:
6813: If p_header_id is null then
6814: --ine_tbl_index := l_line_tbl.first;
6815: l_header_id := l_line_tbl(l_line_tbl.first).header_id;
6815: l_header_id := l_line_tbl(l_line_tbl.first).header_id;
6816: Else
6817: l_header_id := p_header_id;
6818: End If;
6819: oe_debug_pub.add('BCT order header id '||l_header_id);
6820:
6821: Begin
6822: BEGIN
6823:
6858: If nvl(l_calculate_price_flag,'X') not in ('N','P') Then
6859: l_calculate_price_flag:='Y';
6860: End If;
6861: ELSE
6862: oe_debug_pub.add('BCT:any_frozen_line is false');
6863: l_calculate_price_flag := 'Y';
6864: END IF;
6865: END IF; --end if for honor price flag check
6866:
6875: ,px_line_index => l_line_index);
6876:
6877: G_STMT_NO := 'Build_Context for Header';
6878: IF l_bypass_pricing = 'Y' Then
6879: oe_debug_pub.add('Bypassing the qualifier build',1);
6880: Else
6881: oe_debug_pub.add('Before QP_Attr_Mapping_PUB.Build_Contexts for Header',1);
6882: QP_Attr_Mapping_PUB.Build_Contexts(
6883: p_request_type_code => 'ONT',
6877: G_STMT_NO := 'Build_Context for Header';
6878: IF l_bypass_pricing = 'Y' Then
6879: oe_debug_pub.add('Bypassing the qualifier build',1);
6880: Else
6881: oe_debug_pub.add('Before QP_Attr_Mapping_PUB.Build_Contexts for Header',1);
6882: QP_Attr_Mapping_PUB.Build_Contexts(
6883: p_request_type_code => 'ONT',
6884: --p_line_index=> l_line_index,
6885: p_line_index=> l_line_index, --oe_order_pub.g_hdr.header_id,
6905: Exception
6906: when no_data_found then
6907: null;
6908: when others then
6909: Oe_Debug_Pub.Add('Error when querying asked_for:'||SQLERRM);
6910: End;
6911:
6912: G_STMT_NO := 'calculate_adjustments#180';
6913: -- AG change --
6919: px_line_tbl := l_line_tbl;
6920:
6921: EXCEPTION
6922: when FND_API.G_EXC_ERROR then
6923: oe_debug_pub.add('error in calculate adjustments'||G_STMT_NO, 2);
6924: RAISE FND_API.G_EXC_ERROR;
6925: when others then
6926: oe_debug_pub.add('others error in calculate adjustments'||G_STMT_NO,2);
6927: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6922: when FND_API.G_EXC_ERROR then
6923: oe_debug_pub.add('error in calculate adjustments'||G_STMT_NO, 2);
6924: RAISE FND_API.G_EXC_ERROR;
6925: when others then
6926: oe_debug_pub.add('others error in calculate adjustments'||G_STMT_NO,2);
6927: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6928: End Calculate_Adjustments;
6929:
6930:
6960: x_Req_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
6961: x_Req_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
6962: */
6963: --
6964: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6965: --
6966: BEGIN
6967: IF l_debug_level > 0 THEN
6968: oe_debug_pub.add( 'BEFORE QP_PREQ_PUB.PRICE_REQUEST' , 1 ) ;
6964: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6965: --
6966: BEGIN
6967: IF l_debug_level > 0 THEN
6968: oe_debug_pub.add( 'BEFORE QP_PREQ_PUB.PRICE_REQUEST' , 1 ) ;
6969: END IF;
6970:
6971: l_control_rec.calculate_flag := p_control_rec.p_calculate_flag;
6972: l_control_rec.simulation_flag := p_control_rec.p_simulation_flag;
6991:
6992: -- added for freight rating.
6993: l_control_rec.get_freight_flag := p_control_rec.p_get_freight_flag;
6994: IF l_debug_level > 0 THEN
6995: oe_debug_pub.add('get_freight_flag before calling pricing engine is: '||l_control_rec.get_freight_flag, 3 ) ;
6996: END IF;
6997:
6998: IF (G_PASS_ALL_LINES in ('N', 'R')) THEN
6999: l_control_rec.full_pricing_call := 'N';
7052: l_return_status varchar2(30);
7053: l_x_msg_count number;
7054: l_x_msg_data Varchar2(2000);
7055: l_x_result_out Varchar2(30);
7056: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7057:
7058: begin
7059:
7060: l_hold_source_rec.hold_id := G_SEEDED_PRICE_ERROR_HOLD_ID;
7062: l_hold_source_rec.Hold_Entity_code := 'O';
7063: l_hold_source_rec.header_id := p_header_id;
7064: l_hold_source_rec.line_id := p_line_id;
7065: IF l_debug_level > 0 THEN
7066: oe_debug_pub.add('p_line_id = '||p_line_id,1);
7067: END IF;
7068:
7069: OE_Holds_Pub.Check_Holds(
7070: p_api_version => 1.0
7081: );
7082:
7083: IF (l_return_status <> FND_API.g_ret_sts_success) THEN
7084: IF l_debug_level > 0 THEN
7085: oe_debug_pub.add(' OE_HOLD_PUB.Check_Holds returns unexpected error!');
7086: END IF;
7087: RAISE FND_API.G_EXC_ERROR;
7088: null;
7089: END IF;
7100: END IF;
7101:
7102: IF l_return_status = FND_API.g_ret_sts_success then
7103: IF l_debug_level > 0 THEN
7104: oe_debug_pub.add('just before showing formula error',1);
7105: END IF;
7106:
7107: FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERROR_HOLD');
7108: FND_MESSAGE.SET_TOKEN('ERR_TEXT',pmsg);
7129: return l_formula_name;
7130: close get_formula;
7131: Exception
7132: when no_data_found then
7133: oe_debug_pub.add('Formula name not found for the errorneous formula');
7134: return null;
7135: End;
7136:
7137: procedure Report_Engine_Errors(
7207: --and lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_UPDATED
7208: -- , QP_PREQ_GRP.G_STATUS_GSA_VIOLATION
7209: -- );
7210: --
7211: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7212: --
7213:
7214: l_booking_error varchar2(1) := 'N'; -- Bug 8236945
7215: begin
7214: l_booking_error varchar2(1) := 'N'; -- Bug 8236945
7215: begin
7216: -- Update Order Lines
7217: IF l_debug_level > 0 THEN
7218: oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.REPORT_ENGINE_ERROR' , 1 ) ;
7219: END IF;
7220: --bug 3696768
7221: IF G_IPL_ERRORS_TBL.count <> 0 THEN
7222: FOR I in reset_ipl_tbl LOOP
7238: , x_line_rec => l_line_rec);
7239: Exception when no_data_found then
7240: null;
7241: IF l_debug_level > 0 THEN
7242: oe_debug_pub.add( 'REPORT_ENGINE_ERROR QUERY_LINE , NO DATA FOUND' ) ;
7243: END IF;
7244: End;
7245: Else
7246: J:= px_Line_Tbl.First;
7245: Else
7246: J:= px_Line_Tbl.First;
7247: While J is not null loop
7248: IF l_debug_level > 0 THEN
7249: oe_debug_pub.add( 'ERROR '||J||' LINE'||PX_LINE_TBL ( J ) .LINE_ID||' '||WRONG_LINE.LINE_ID ) ;
7250: END IF;
7251: If px_Line_Tbl(j).line_id = wrong_line.line_id
7252: --or
7253: -- J = wrong_line.line_index
7253: -- J = wrong_line.line_index
7254: then
7255: l_line_rec := px_Line_Tbl(J);
7256: IF l_debug_level > 0 THEN
7257: oe_debug_pub.add( 'PRICE'||L_LINE_REC.UNIT_LIST_PRICE||'+'||L_LINE_REC.UNIT_LIST_PRICE_PER_PQTY ) ;
7258: END IF;
7259: exit;
7260: End if;
7261: J:= px_Line_Tbl.next(j);
7277: ,p_source_document_line_id => l_line_rec.source_document_line_id
7278: );
7279:
7280: IF l_debug_level > 0 THEN
7281: oe_debug_pub.add( 'THE STATUS'||WRONG_LINE.PRICING_STATUS_CODE||':'||WRONG_LINE.PROCESSED_CODE||':'||WRONG_LINE.STATUS_TEXT ) ;
7282: END IF;
7283: l_invalid_line := 'N';
7284: -- add message when the price list is found to be inactive
7285: /*IF wrong_line.line_Type_code ='LINE' and
7289: and
7290: G_IPL_ERRORS_TBL(MOD(l_line_rec.line_id,G_BINARY_LIMIT))<>l_line_rec.price_list_id)
7291: Then
7292: IF l_debug_level > 0 THEN
7293: oe_debug_pub.add( 'PRICE LIST NOT FOUND' ) ;
7294: END IF;
7295: IF l_debug_level > 0 THEN
7296: oe_debug_pub.add( 'INVALID PRICE LIST ' , 1 ) ;
7297: END IF;
7292: IF l_debug_level > 0 THEN
7293: oe_debug_pub.add( 'PRICE LIST NOT FOUND' ) ;
7294: END IF;
7295: IF l_debug_level > 0 THEN
7296: oe_debug_pub.add( 'INVALID PRICE LIST ' , 1 ) ;
7297: END IF;
7298: FND_MESSAGE.SET_NAME('ONT','ONT_NO_PRICE_LIST_FOUND');
7299: FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
7300: FND_MESSAGE.SET_TOKEN('UOM',l_line_rec.Order_Quantity_uom);
7337: End;
7338:
7339: If wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST then
7340: IF l_debug_level > 0 THEN
7341: oe_debug_pub.add( 'INVALID ITEM/PRICE LIST COMBINATION'||L_LINE_REC.ORDERED_ITEM||L_LINE_REC.ORDER_QUANTITY_UOM||L_PRICE_LIST ) ;
7342: END IF;
7343:
7344:
7345: IF not G_IPL_ERRORS_TBL.exists(MOD(l_line_rec.line_id,G_BINARY_LIMIT))
7369: G_IPL_ERRORS_TBL(MOD(l_line_rec.line_id,G_BINARY_LIMIT)):=l_line_rec.price_list_id;
7370: END IF;
7371:
7372: IF l_debug_level > 0 THEN
7373: oe_debug_pub.add( 'BEFORE CHECKING BOOK FLAG' ) ;
7374: END IF;
7375:
7376: If nvl(l_line_rec.booked_flag,'X') = 'Y' Then
7377: IF l_debug_level > 0 THEN
7374: END IF;
7375:
7376: If nvl(l_line_rec.booked_flag,'X') = 'Y' Then
7377: IF l_debug_level > 0 THEN
7378: oe_debug_pub.add( ' EXCEPTION: PRICE LIST MISSING FOR BOOKED ORDER' ) ;
7379: END IF;
7380: FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
7381: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_ORDER_UTIL.GET_ATTRIBUTE_NAME('UNIT_LIST_PRICE'));
7382: OE_MSG_PUB.ADD;
7387: l_booking_error := 'Y';
7388: END IF;
7389: End If;
7390: IF l_debug_level > 0 THEN
7391: oe_debug_pub.add( 'PRICE'||L_LINE_REC.UNIT_SELLING_PRICE||':'||L_LINE_REC.UNIT_LIST_PRICE ) ;
7392: END IF;
7393:
7394: --Fix bug 1650637
7395: If (l_line_rec.unit_selling_price Is Not Null or
7402: l_line_rec.unit_list_price_per_pqty := NULL;
7403: END IF;
7404:
7405: IF l_debug_level > 0 THEN
7406: oe_debug_pub.add('invalid price done');
7407: END IF;
7408: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND Then
7409: IF l_debug_level > 0 THEN
7410: oe_debug_pub.add( 'PRICE LIST NOT FOUND' ) ;
7406: oe_debug_pub.add('invalid price done');
7407: END IF;
7408: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND Then
7409: IF l_debug_level > 0 THEN
7410: oe_debug_pub.add( 'PRICE LIST NOT FOUND' ) ;
7411: END IF;
7412: FND_MESSAGE.SET_NAME('ONT','ONT_NO_PRICE_LIST_FOUND');
7413: FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
7414: FND_MESSAGE.SET_TOKEN('UOM',l_line_rec.Order_Quantity_uom);
7422: OE_MSG_PUB.Add;
7423: end if;
7424: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_FORMULA_ERROR then
7425: IF l_debug_level > 0 THEN
7426: oe_debug_pub.add( 'ERROR IN FORMULA PROCESSING' ) ;
7427: END IF;
7428: FND_MESSAGE.SET_NAME('ONT','ONT_PRC_ERROR_IN_FORMULA');
7429: FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text||','||get_formula(wrong_line.line_index));
7430: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
7440: ( QP_PREQ_GRP.G_STATUS_OTHER_ERRORS , FND_API.G_RET_STS_UNEXP_ERROR,
7441: FND_API.G_RET_STS_ERROR)
7442: then
7443: IF l_debug_level > 0 THEN
7444: oe_debug_pub.add( 'OTHER ERRORS PROCESSING' ) ;
7445: END IF;
7446: FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS'); --bug#7149497
7447: FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
7448: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
7455: OE_MSG_PUB.Add;
7456: end if;
7457: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_UOM then
7458: IF l_debug_level > 0 THEN
7459: oe_debug_pub.add( 'INVALID UOM' ) ;
7460: END IF;
7461: FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_UOM');
7462: FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
7463: FND_MESSAGE.SET_TOKEN('UOM',l_line_rec.Order_Quantity_uom);
7471: OE_MSG_PUB.Add;
7472: end if;
7473: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST then
7474: IF l_debug_level > 0 THEN
7475: oe_debug_pub.add( 'DUPLICATE PRICE LIST' ) ;
7476: END IF;
7477: FND_MESSAGE.SET_NAME('ONT','ONT_PRC_DUPLICATE_PRICE_LIST');
7478:
7479: Begin
7519: OE_MSG_PUB.Add;
7520: end if;
7521: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV then
7522: IF l_debug_level > 0 THEN
7523: oe_debug_pub.add( 'INVALID UOM CONVERSION' ) ;
7524: END IF;
7525: FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_UOM_CONVERSION');
7526: FND_MESSAGE.SET_TOKEN('UOM_TEXT','( '||l_line_rec.Ordered_Item||' ) '||
7527: wrong_line.status_text);
7535: OE_MSG_PUB.Add;
7536: end if;
7537: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_INCOMP then
7538: IF l_debug_level > 0 THEN
7539: oe_debug_pub.add( 'UNABLE TO RESOLVE INCOMPATIBILITY' ) ;
7540: END IF;
7541: FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_INCOMP');
7542: FND_MESSAGE.SET_TOKEN('ERR_TEXT','( '||
7543: l_line_rec.Ordered_Item||' ) '||wrong_line.status_text);
7551: OE_MSG_PUB.Add;
7552: end if;
7553: Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR then
7554: IF l_debug_level > 0 THEN
7555: oe_debug_pub.add( 'ERROR WHILE EVALUATING THE BEST PRICE' ) ;
7556: END IF;
7557: FND_MESSAGE.SET_NAME('ONT','ONT_PRC_BEST_PRICE_ERROR');
7558: FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
7559: FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
7586: --since the line_rec doesn't have the place holder to hold error status
7587: l_line_rec.industry_attribute30
7588: := wrong_line.pricing_status_code;
7589: IF l_debug_level > 0 THEN
7590: oe_debug_pub.add( 'J IS '||J ) ;
7591: END IF;
7592: if (j<>0) THEN
7593: px_line_tbl(j) := l_line_rec;
7594: END IF;
7593: px_line_tbl(j) := l_line_rec;
7594: END IF;
7595:
7596: IF l_debug_level > 0 THEN
7597: oe_debug_pub.add( 'PASSING BACK PRICE'||L_LINE_REC.UNIT_LIST_PRICE||' '||L_LINE_REC.UNIT_SELLING_PRICE ) ;
7598: END IF;
7599: IF l_debug_level > 0 THEN
7600: oe_debug_pub.add( 'LINE'||L_LINE_REC.HEADER_ID||'+'||L_LINE_REC.LINE_ID ) ;
7601: END IF;
7596: IF l_debug_level > 0 THEN
7597: oe_debug_pub.add( 'PASSING BACK PRICE'||L_LINE_REC.UNIT_LIST_PRICE||' '||L_LINE_REC.UNIT_SELLING_PRICE ) ;
7598: END IF;
7599: IF l_debug_level > 0 THEN
7600: oe_debug_pub.add( 'LINE'||L_LINE_REC.HEADER_ID||'+'||L_LINE_REC.LINE_ID ) ;
7601: END IF;
7602: End If;
7603: --btea end
7604:
7615: (wrong_line.unit_price <0 or wrong_line.adjusted_unit_price<0)
7616: Then
7617:
7618: IF l_debug_level > 0 THEN
7619: oe_debug_pub.add( 'NEGATIVE LIST PRICE '||WRONG_LINE.UNIT_PRICE ||'OR SELLING PRICE '||WRONG_LINE.ADJUSTED_UNIT_PRICE ) ;
7620: END IF;
7621: FND_MESSAGE.SET_NAME('ONT','ONT_NEGATIVE_PRICE');
7622: FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
7623: FND_MESSAGE.SET_TOKEN('LIST_PRICE',wrong_line.unit_price);
7623: FND_MESSAGE.SET_TOKEN('LIST_PRICE',wrong_line.unit_price);
7624: FND_MESSAGE.SET_TOKEN('SELLING_PRICE',wrong_line.Adjusted_unit_price);
7625: OE_MSG_PUB.Add;
7626: IF l_debug_level > 0 THEN
7627: oe_debug_pub.add( 'BEFORE SHOWING NEGATIVE MODIFIERS MESSAGE' ) ;
7628: END IF;
7629: l_list_line_no:=get_list_lines(wrong_line.line_id);
7630:
7631: IF l_list_line_no IS NOT NULL THEN
7634: OE_MSG_PUB.Add;
7635: END IF;
7636:
7637: IF l_debug_level > 0 THEN
7638: oe_debug_pub.add( 'MODIFIERS:'||GET_LIST_LINES ( WRONG_LINE.LINE_ID ) ) ;
7639: END IF;
7640:
7641: --place the line on invoicing hold to avoid stuck order it the ship quatity is there.
7642: IF l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM THEN
7640:
7641: --place the line on invoicing hold to avoid stuck order it the ship quatity is there.
7642: IF l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM THEN
7643: vmsg := FND_MESSAGE.GET;
7644: oe_debug_pub.add('vmsg = '||vmsg,1);
7645: pricing_errors_hold(l_line_rec.header_id,l_line_rec.line_id,vmsg);
7646: -- select oe_msg_request_id_s.nextval into l_request_id from dual;
7647: -- OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, l_request_id,'U');
7648: -- 4919922
7660: end if;
7661:
7662: IF wrong_line.line_type_code='ORDER' THEN
7663: if wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_FORMULA_ERROR then
7664: oe_debug_pub.add( 'ERROR IN ORDER LEVEL FORMULA PROCESSING' ) ;
7665: FND_MESSAGE.SET_NAME('ONT','ONT_PRC_ERROR_IN_FORMULA');
7666: FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text||','||get_formula(wrong_line.line_index));
7667: OE_MSG_PUB.Add;
7668: vmsg := FND_MESSAGE.GET;
7666: FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text||','||get_formula(wrong_line.line_index));
7667: OE_MSG_PUB.Add;
7668: vmsg := FND_MESSAGE.GET;
7669: else
7670: oe_debug_pub.add( 'OTHER ERRORS PROCESSING' ) ;
7671: FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS'); --bug#7149497
7672: FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
7673: OE_MSG_PUB.Add;
7674: vmsg := FND_MESSAGE.GET;
7678: If l_invalid_line = 'Y' Then
7679: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
7680: if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
7681: IF l_debug_level > 0 THEN
7682: oe_debug_pub.add('vmsg = '||vmsg,1);
7683: END IF;
7684: pricing_errors_hold(l_line_rec.header_id,l_line_rec.line_id,vmsg);
7685: -- select oe_msg_request_id_s.nextval into l_request_id from dual;
7686:
7693: OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
7694: END IF;
7695:
7696: else
7697: oe_debug_pub.add('has invalid line');
7698: l_temp_line_rec.line_id := Wrong_line.line_id;
7699: oe_debug_pub.add('has invalid line2');
7700: l_temp_line_rec.ordered_quantity := Wrong_line.line_quantity;
7701: oe_debug_pub.add('has invalid line3');
7695:
7696: else
7697: oe_debug_pub.add('has invalid line');
7698: l_temp_line_rec.line_id := Wrong_line.line_id;
7699: oe_debug_pub.add('has invalid line2');
7700: l_temp_line_rec.ordered_quantity := Wrong_line.line_quantity;
7701: oe_debug_pub.add('has invalid line3');
7702: l_temp_line_rec.pricing_quantity := Wrong_line.priced_quantity;
7703: oe_debug_pub.add('has invalid line4');
7697: oe_debug_pub.add('has invalid line');
7698: l_temp_line_rec.line_id := Wrong_line.line_id;
7699: oe_debug_pub.add('has invalid line2');
7700: l_temp_line_rec.ordered_quantity := Wrong_line.line_quantity;
7701: oe_debug_pub.add('has invalid line3');
7702: l_temp_line_rec.pricing_quantity := Wrong_line.priced_quantity;
7703: oe_debug_pub.add('has invalid line4');
7704: -- select oe_msg_request_id_s.nextval into l_request_id from dual;
7705:
7699: oe_debug_pub.add('has invalid line2');
7700: l_temp_line_rec.ordered_quantity := Wrong_line.line_quantity;
7701: oe_debug_pub.add('has invalid line3');
7702: l_temp_line_rec.pricing_quantity := Wrong_line.priced_quantity;
7703: oe_debug_pub.add('has invalid line4');
7704: -- select oe_msg_request_id_s.nextval into l_request_id from dual;
7705:
7706: -- OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, l_request_id,'U');
7707: -- 4919922
7711: END IF;
7712: OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
7713: END IF;
7714:
7715: oe_debug_pub.add('has invalid line 5');
7716: Oe_Order_Adj_Pvt.Reset_Fields(l_temp_line_rec);
7717: end if;
7718: else
7719: l_temp_line_rec.line_id := Wrong_line.line_id;
7732:
7733: Oe_Order_Adj_Pvt.Reset_Fields(l_temp_line_rec);
7734: end if;
7735: Else
7736: oe_debug_pub.add('No invalid line');
7737: l_invalid_line:='N';
7738: End If;
7739:
7740: end loop; /* wrong_lines cursor */
7753: FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
7754: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
7755: OE_Order_UTIL.Get_Attribute_Name('UNIT_LIST_PRICE'));
7756: IF l_debug_level > 0 THEN
7757: oe_debug_pub.add( ' ERROR: UNIT LIST PRICE CAN NOT BE NULL' ) ;
7758: END IF;
7759: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
7760: if book_line.shipped_quantity is not null or book_line.shipped_quantity <> FND_API.G_MISS_NUM then
7761: vmsg := FND_MESSAGE.GET;
7782: FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRUIBUTE');
7783: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_ORDER_UTIL.Get_Attribute_Name('PRICE_LIST_ID'));
7784: OE_MSG_PUB.ADD;
7785: IF l_debug_level > 0 THEN
7786: oe_debug_pub.add( ' ERROR: PRICE LIST ID CAN NOT BE NULL' ) ;
7787: END IF;
7788: -- Bug 8236945
7789: IF OE_GLOBALS.G_UI_FLAG THEN
7790: RAISE FND_API.G_EXC_ERROR;
7794: END IF;
7795:
7796: End loop; /* wrong booked lines */
7797: IF l_debug_level > 0 THEN
7798: oe_debug_pub.add( 'EXITING REPORT_ENGINE_ERRORS' ) ;
7799: END IF;
7800:
7801: -- Added for bug 8236945
7802: IF l_booking_error = 'Y' THEN
7838: l_msg_data VARCHAR2(2000);
7839:
7840:
7841: --
7842: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7843: --
7844: BEGIN
7845:
7846: SAVEPOINT PROCESS_ADJUSTMENTS;
7857: END IF;
7858:
7859: IF p_control_rec.p_write_to_db THEN
7860: IF l_debug_level > 0 THEN
7861: oe_debug_pub.add( 'WRITING TO DATABASE' , 3 ) ;
7862: END IF;
7863:
7864: CHECK_GSA;
7865:
7869: , l_num_changed_lines); moved after oe_adv_price_pvt.process_adv_modifiers */
7870:
7871: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
7872: IF l_debug_level > 0 THEN
7873: oe_debug_pub.add( 'CALLING OE_ADV_PRICE_PVT.PROCESS_ADV_MODIFIERS:'||OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL ) ;
7874: END IF;
7875: oe_adv_price_pvt.process_adv_modifiers
7876: (x_return_status => x_return_status,
7877: p_Control_Rec => p_Control_Rec,
7899: OR instr(p_pricing_events||',', 'BOOK') > 0
7900: OR instr(p_pricing_events||',', 'BATCH') > 0 then
7901: IF (l_num_changed_lines > 0) THEN
7902: IF l_debug_level > 0 THEN
7903: oe_debug_pub.add( 'SETTING CASCADING FLAG TO REFRESH ORDER' ) ;
7904: END IF;
7905: OE_GLOBALS.G_CASCADING_REQUEST_LOGGED := TRUE;
7906: END IF;
7907: End if;
7930:
7931: END IF;
7932:
7933: IF l_debug_level > 0 THEN
7934: oe_debug_pub.add( 'VOPRB RECURSION MODE'||OE_GLOBALS.G_RECURSION_MODE ) ;
7935: END IF;
7936:
7937: -- 2366123: execute delayed requests only when not called by UI
7938: -- IF (OE_GLOBALS.G_RECURSION_MODE <> FND_API.G_TRUE
7947: END IF;
7948:
7949: IF l_process_requests THEN
7950: IF l_debug_level > 0 THEN
7951: oe_debug_pub.add( 'VOPRB BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
7952: END IF;
7953:
7954: IF (oe_order_cache.g_header_rec.booked_flag = 'Y') Then
7955: l_process_ack := TRUE;
7964: , p_old_line_tbl => l_old_line_tbl
7965: );
7966:
7967: IF l_debug_level > 0 THEN
7968: oe_debug_pub.add( 'RETRURNED FROM PROCESS_REQUEST AND NOTIFY : '||X_RETURN_STATUS , 3 ) ;
7969: END IF;
7970:
7971: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
7972: RAISE FND_API.G_EXC_ERROR;
7976: END IF;
7977:
7978: /*AS per Jyothi Narayan, Process_Requests_And_Notify has notify call. No extra call is needed
7979: If l_debug_level > 0 THEN
7980: oe_debug_pub.add('l_booked_flag in process_adjustments = '||l_booked_flag); End If;
7981: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
7982: IF l_booked_flag = 'Y' AND p_control_rec.p_write_to_db = TRUE THEN
7983: IF NOT OE_GLOBALS.G_UI_FLAG AND OE_GLOBALS.G_RECURSION_MODE = 'N' THEN
7984: If l_debug_level > 0 THEN
7981: IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
7982: IF l_booked_flag = 'Y' AND p_control_rec.p_write_to_db = TRUE THEN
7983: IF NOT OE_GLOBALS.G_UI_FLAG AND OE_GLOBALS.G_RECURSION_MODE = 'N' THEN
7984: If l_debug_level > 0 THEN
7985: oe_debug_pub.add('Before calling OE_SERVICE_UTIL.Notify_OC in process adj');
7986:
7987: End If;
7988: OE_SERVICE_UTIL.Notify_OC
7989: ( p_api_version_number => 1.0
7994: , p_Line_Adj_tbl => OE_ORDER_UTIL.g_Line_Adj_tbl
7995: , p_old_Line_Adj_tbl => OE_ORDER_UTIL.g_old_Line_Adj_tbl);
7996:
7997: IF l_debug_level > 0 THEN
7998: oe_debug_pub.add( 'AFTER NOTIFY_OC API' , 1 ) ;
7999: END IF;
8000: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
8001: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8002: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
8012: WHEN FND_API.G_EXC_ERROR THEN
8013: x_return_status := FND_API.G_RET_STS_ERROR;
8014:
8015: IF l_debug_level > 0 THEN
8016: oe_debug_pub.add( 'EXITING PROCESS_ADJUSTMENTS WITH EXC ERROR with rollback' , 1 ) ;
8017: END IF;
8018: ROLLBACK TO SAVEPOINT PROCESS_ADJUSTMENTS;
8019:
8020: RAISE FND_API.G_EXC_ERROR;
8021: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8022: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8023:
8024: IF l_debug_level > 0 THEN
8025: oe_debug_pub.add( 'EXITING PROCESS_ADJUSTMENTS WITH UNEXPECTED ERROR with rollback' , 1 ) ;
8026: END IF;
8027: ROLLBACK TO SAVEPOINT PROCESS_ADJUSTMENTS;
8028: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8029: WHEN OTHERS THEN
8028: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8029: WHEN OTHERS THEN
8030: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8031: IF l_debug_level > 0 THEN
8032: oe_debug_pub.add( 'ERROR IN OE_ORDER_PRICE_PVT.PROCESS_ADJUSTMENTS with rollback' , 1 ) ;
8033: END IF;
8034: IF l_debug_level > 0 THEN
8035: oe_debug_pub.add( SQLERRM , 1 ) ;
8036: END IF;
8031: IF l_debug_level > 0 THEN
8032: oe_debug_pub.add( 'ERROR IN OE_ORDER_PRICE_PVT.PROCESS_ADJUSTMENTS with rollback' , 1 ) ;
8033: END IF;
8034: IF l_debug_level > 0 THEN
8035: oe_debug_pub.add( SQLERRM , 1 ) ;
8036: END IF;
8037:
8038: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
8039: THEN
8044: );
8045: END IF;
8046:
8047: IF l_debug_level > 0 THEN
8048: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.PROCESS_ADJUSTMENTS with rollback' , 1 ) ;
8049: END IF;
8050: ROLLBACK TO SAVEPOINT PROCESS_ADJUSTMENTS;
8051: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8052: IF l_debug_level > 0 THEN
8049: END IF;
8050: ROLLBACK TO SAVEPOINT PROCESS_ADJUSTMENTS;
8051: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8052: IF l_debug_level > 0 THEN
8053: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.PROCESS_ADJUSTMENTS' , 1 ) ;
8054: END IF;
8055: END Process_Adjustments;
8056:
8057: -- Price_Line is the main Pricing Integration API
8072: is
8073: l_any_frozen_line BOOLEAN;
8074: lx_header_rec OE_ORDER_PUB.HEADER_REC_TYPE;
8075: --
8076: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8077: --
8078: l_org_id NUMBER(15):=MO_GLOBAL.Get_Current_Org_Id; --TaxER
8079: l_hdr_chg_count NUMBER; --TaxER
8080: l_current_event number := 0; --TaxER
8083: l_line_rec OE_Order_PUB.Line_Rec_Type; --TaxER
8084: Begin
8085:
8086: IF l_debug_level > 0 THEN
8087: oe_debug_pub.add('ENTERING OE_ORDER_PRICE_PVT.PRICE_LINE', 0.5) ;--Added for bug 13435459
8088: oe_debug_pub.add('Version:'||get_version);
8089: END IF;
8090:
8091: --RT{
8084: Begin
8085:
8086: IF l_debug_level > 0 THEN
8087: oe_debug_pub.add('ENTERING OE_ORDER_PRICE_PVT.PRICE_LINE', 0.5) ;--Added for bug 13435459
8088: oe_debug_pub.add('Version:'||get_version);
8089: END IF;
8090:
8091: --RT{
8092: G_PRICING_EVENT:=p_pricing_events;
8091: --RT{
8092: G_PRICING_EVENT:=p_pricing_events;
8093: G_RETROBILL_OPERATION:=p_request_rec.param3;
8094:
8095: oe_debug_pub.add('Retrobill Operation:'||g_retrobill_operation);
8096: --RT}
8097:
8098: If OE_DEBUG_PUB.G_DEBUG = FND_API.G_TRUE Then
8099: G_DEBUG := TRUE;
8094:
8095: oe_debug_pub.add('Retrobill Operation:'||g_retrobill_operation);
8096: --RT}
8097:
8098: If OE_DEBUG_PUB.G_DEBUG = FND_API.G_TRUE Then
8099: G_DEBUG := TRUE;
8100: IF l_debug_level > 0 THEN
8101: oe_debug_pub.add( 'BCT G_DEBUG IS:'||OE_DEBUG_PUB.G_DEBUG ) ;
8102: END IF;
8097:
8098: If OE_DEBUG_PUB.G_DEBUG = FND_API.G_TRUE Then
8099: G_DEBUG := TRUE;
8100: IF l_debug_level > 0 THEN
8101: oe_debug_pub.add( 'BCT G_DEBUG IS:'||OE_DEBUG_PUB.G_DEBUG ) ;
8102: END IF;
8103: Else
8104: G_DEBUG := FALSE;
8105: End If;
8104: G_DEBUG := FALSE;
8105: End If;
8106:
8107: IF l_debug_level > 0 THEN
8108: oe_debug_pub.add( 'SETTING REQUEST ID' , 1 ) ;
8109: END IF;
8110:
8111: qp_price_request_context.set_request_id;
8112:
8110:
8111: qp_price_request_context.set_request_id;
8112:
8113: IF l_debug_level > 0 THEN
8114: oe_debug_pub.add( ' REQUEST ID IS : ' || QP_PREQ_GRP.G_REQUEST_ID , 1 ) ;
8115: END IF;
8116:
8117: G_IS_THERE_FREEZE_OVERRIDE:=TRUE;
8118: G_IS_THERE_FREEZE_OVERRIDE:=Check_Freeze_Override(p_pricing_events);
8117: G_IS_THERE_FREEZE_OVERRIDE:=TRUE;
8118: G_IS_THERE_FREEZE_OVERRIDE:=Check_Freeze_Override(p_pricing_events);
8119:
8120: IF l_debug_level > 0 THEN
8121: oe_debug_pub.add( 'PRICE_LINE:'||P_HEADER_ID||'+'||P_LINE_ID||'OF EVENT '||P_PRICING_EVENTS , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
8122: END IF;
8123: x_return_status := FND_API.G_RET_STS_SUCCESS;
8124:
8125: calculate_adjustments
8138: --l_Control_Rec.calculate_flag := 'N';
8139: -- Do no proceed , if there are no order lines
8140: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
8141: IF l_debug_level > 0 THEN
8142: oe_debug_pub.add( 'CALCULATE ADJUSTMENTS ERROR' ) ;
8143: END IF;
8144: raise fnd_api.g_exc_error;
8145: END IF;
8146:
8145: END IF;
8146:
8147: If x_return_status = 'NOOP' Then
8148: IF l_debug_level > 0 THEN
8149: oe_debug_pub.add( 'THERE ARE NO ORDER LINES FOR '||P_LINE_ID ) ;
8150: END IF;
8151: OE_Order_PUB.G_LINE := NULL;
8152: OE_Order_PUB.G_HDR := NULL;
8153: Return;
8153: Return;
8154: End If;
8155:
8156: IF l_debug_level > 0 THEN
8157: oe_debug_pub.add( 'CALLING PRICING ENGINE PUBLIC API' , 2 ) ;
8158: END IF;
8159: Call_Pricing_Engine(p_Control_Rec
8160: ,p_Pricing_Events
8161: ,x_return_status
8161: ,x_return_status
8162: );
8163: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
8164: IF l_debug_level > 0 THEN
8165: oe_debug_pub.add( 'PRICING ENGINE ERROR' ) ;
8166: END IF;
8167: raise fnd_api.g_exc_error;
8168: END IF;
8169:
8167: raise fnd_api.g_exc_error;
8168: END IF;
8169:
8170: IF l_debug_level > 0 THEN
8171: oe_debug_pub.add( 'BEFORE OE_ORDER_PRICE_PVT.PROCESS_ADJUSTMENTS' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
8172: END IF;
8173:
8174: process_adjustments
8175: (
8184: );
8185:
8186: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
8187: IF l_debug_level > 0 THEN
8188: oe_debug_pub.add( 'PROCESS ADJUSTMENTS ERROR' ) ;
8189: END IF;
8190: raise fnd_api.g_exc_error;
8191: END IF;
8192:
8238: WHEN NO_DATA_FOUND THEN
8239: l_tax_calculation_event_code := 0;
8240: WHEN OTHERS THEN
8241: IF l_debug_level > 0 THEN
8242: oe_debug_pub.add('OEXVOPRB:Failed while trying to query up tax_calcualtion_event for order_type_id ');
8243: END IF;
8244:
8245: RAISE;
8246:
8253: l_current_event := 1; /* current event is booking or higher */
8254: END IF;
8255:
8256: IF l_debug_level > 0 THEN
8257: oe_debug_pub.add('OEXVOPRB:Tax Event Code :'||l_tax_calculation_event_code);
8258: oe_debug_pub.add('OEXVOPRB:Current Event :'||l_current_event);
8259: END IF;
8260:
8261:
8254: END IF;
8255:
8256: IF l_debug_level > 0 THEN
8257: oe_debug_pub.add('OEXVOPRB:Tax Event Code :'||l_tax_calculation_event_code);
8258: oe_debug_pub.add('OEXVOPRB:Current Event :'||l_current_event);
8259: END IF;
8260:
8261:
8262: IF l_current_event >= l_tax_calculation_event_code THEN
8309: l_current_event := -999;
8310: END;
8311:
8312: IF l_debug_level > 0 THEN
8313: oe_debug_pub.add('OEXULADB:Tax Event Code :'||l_tax_calculation_event_code);
8314: oe_debug_pub.add('OEXULADB:Current Event :'||l_current_event);
8315: END IF;
8316:
8317: IF l_current_event >= l_tax_calculation_event_code THEN
8310: END;
8311:
8312: IF l_debug_level > 0 THEN
8313: oe_debug_pub.add('OEXULADB:Tax Event Code :'||l_tax_calculation_event_code);
8314: oe_debug_pub.add('OEXULADB:Current Event :'||l_current_event);
8315: END IF;
8316:
8317: IF l_current_event >= l_tax_calculation_event_code THEN
8318:
8345: OE_Order_PUB.G_LINE := NULL;
8346: OE_Order_PUB.G_HDR := NULL;
8347:
8348: IF l_debug_level > 0 THEN
8349: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.PRICE_LINE' , 0.5 ) ; -- debug level changed to 0.5 for bug 13435459
8350: END IF;
8351:
8352: Exception
8353: WHEN FND_API.G_EXC_ERROR THEN
8357: OE_Order_PUB.G_HDR := NULL;
8358: x_return_status := FND_API.G_RET_STS_ERROR;
8359:
8360: IF l_debug_level > 0 THEN
8361: oe_debug_pub.add( 'EXITING PRICE_LINE WITH EXC ERROR' , 1 ) ;
8362: END IF;
8363: RAISE FND_API.G_EXC_ERROR;
8364: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8365:
8367: OE_Order_PUB.G_LINE := NULL;
8368: OE_Order_PUB.G_HDR := NULL;
8369: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8370: IF l_debug_level > 0 THEN
8371: oe_debug_pub.add( 'EXITING PRICE_LINE WITH UNEXPECTED ERROR' , 1 ) ;
8372: END IF;
8373: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8374: WHEN OTHERS THEN
8375:
8377: --reseting global structure after engine call
8378: OE_Order_PUB.G_LINE := NULL;
8379: OE_Order_PUB.G_HDR := NULL;
8380: IF l_debug_level > 0 THEN
8381: oe_debug_pub.add( 'ERROR IN OE_ORDER_PRICE_PVT.PRICE_LINE' , 1 ) ;
8382: END IF;
8383: IF l_debug_level > 0 THEN
8384: oe_debug_pub.add( SQLERRM , 1 ) ;
8385: END IF;
8380: IF l_debug_level > 0 THEN
8381: oe_debug_pub.add( 'ERROR IN OE_ORDER_PRICE_PVT.PRICE_LINE' , 1 ) ;
8382: END IF;
8383: IF l_debug_level > 0 THEN
8384: oe_debug_pub.add( SQLERRM , 1 ) ;
8385: END IF;
8386:
8387: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
8388: THEN
8392: );
8393: END IF;
8394:
8395: IF l_debug_level > 0 THEN
8396: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.PRICE_LINE' , 1 ) ;
8397: END IF;
8398: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8399:
8400: IF l_debug_level > 0 THEN
8397: END IF;
8398: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8399:
8400: IF l_debug_level > 0 THEN
8401: oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.PRICE_LINE' , 1 ) ;
8402: END IF;
8403:
8404: End Price_Line;
8405: