[Home] [Help]
PACKAGE BODY: APPS.OE_PRICE_ORDER_PVT
Source
1 PACKAGE BODY OE_PRICE_ORDER_PVT AS
2 /* $Header: ONTHPROB.pls 120.4 2011/05/31 11:01:39 jmedaven ship $ */
3
4 Type Index_Tbl_Type is table of number
5 Index by Binary_Integer;
6 G_PASS_LINE_TBL Index_Tbl_Type;
7
8 -- AG change
9 G_LINE_INDEX_tbl QP_PREQ_GRP.pls_integer_type;
10 G_LINE_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
11 G_PRICING_EFFECTIVE_DATE_TBL QP_PREQ_GRP.DATE_TYPE ;
12 G_ACTIVE_DATE_FIRST_TBL QP_PREQ_GRP.DATE_TYPE ;
13 G_ACTIVE_DATE_FIRST_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
14 G_ACTIVE_DATE_SECOND_TBL QP_PREQ_GRP.DATE_TYPE ;
15 G_ACTIVE_DATE_SECOND_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE ;
16 G_LINE_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE ;
17 G_LINE_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
18 G_REQUEST_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
19 G_PRICED_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
20 G_UOM_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
21 G_PRICED_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
22 G_CURRENCY_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
23 G_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
24 G_PERCENT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
25 G_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
26 G_UPD_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
27 G_PROCESSED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
28 G_PRICE_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
29 G_LINE_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
30 G_PROCESSING_ORDER_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
31 G_ROUNDING_FACTOR_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
32 G_ROUNDING_FLAG_TBL QP_PREQ_GRP.FLAG_TYPE;
33 G_QUALIFIERS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
34 G_PRICING_ATTRS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
35 G_PRICE_LIST_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
36 G_PL_VALIDATED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
37 G_PRICE_REQUEST_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
38 G_USAGE_PRICING_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
39 G_LINE_CATEGORY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
40 G_PRICING_STATUS_CODE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
41 G_PRICING_STATUS_TEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
42 G_ATTR_LINE_INDEX_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
43 G_ATTR_LINE_DETAIL_INDEX_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
44 G_ATTR_VALIDATED_FLAG_tbl QP_PREQ_GRP.VARCHAR_TYPE;
45 G_ATTR_PRICING_CONTEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
46 G_ATTR_PRICING_ATTRIBUTE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
47 G_ATTR_ATTRIBUTE_LEVEL_tbl QP_PREQ_GRP.VARCHAR_TYPE;
48 G_ATTR_ATTRIBUTE_TYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
49 G_ATTR_APPLIED_FLAG_tbl QP_PREQ_GRP.VARCHAR_TYPE;
50 G_ATTR_PRICING_STATUS_CODE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
51 G_ATTR_PRICING_ATTR_FLAG_tbl QP_PREQ_GRP.VARCHAR_TYPE;
52 G_ATTR_LIST_HEADER_ID_tbl QP_PREQ_GRP.NUMBER_TYPE;
53 G_ATTR_LIST_LINE_ID_tbl QP_PREQ_GRP.NUMBER_TYPE;
54 G_ATTR_VALUE_FROM_tbl QP_PREQ_GRP.VARCHAR_TYPE;
55 G_ATTR_SETUP_VALUE_FROM_tbl QP_PREQ_GRP.VARCHAR_TYPE;
56 G_ATTR_VALUE_TO_tbl QP_PREQ_GRP.VARCHAR_TYPE;
57 G_ATTR_SETUP_VALUE_TO_tbl QP_PREQ_GRP.VARCHAR_TYPE;
58 G_ATTR_GROUPING_NUMBER_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
59 G_ATTR_NO_QUAL_IN_GRP_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
60 G_ATTR_COMP_OPERATOR_TYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
61 G_ATTR_PRICING_STATUS_TEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
62 G_ATTR_QUAL_PRECEDENCE_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
63 G_ATTR_DATATYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
64 G_ATTR_QUALIFIER_TYPE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
65 G_ATTR_PRODUCT_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
66 G_ATTR_EXCLUDER_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
67 G_ATTR_PRICING_PHASE_ID_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
68 G_ATTR_INCOM_GRP_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
69 G_ATTR_LDET_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
70 G_ATTR_MODIFIER_LEVEL_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
71 G_ATTR_PRIMARY_UOM_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
72 G_CATCHWEIGHT_QTY_TBL QP_PREQ_GRP.NUMBER_TYPE;
73 G_ACTUAL_ORDER_QTY_TBL QP_PREQ_GRP.NUMBER_TYPE;
74 G_LDET_LINE_DETAIL_INDEX QP_PREQ_GRP.PLS_INTEGER_TYPE;
75 G_LDET_LINE_DETAIL_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
76 G_LDET_PRICE_BREAK_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
77 G_LDET_LINE_INDEX QP_PREQ_GRP.NUMBER_TYPE;
78 G_LDET_LIST_HEADER_ID QP_PREQ_GRP.NUMBER_TYPE;
79 G_LDET_LIST_LINE_ID QP_PREQ_GRP.NUMBER_TYPE;
80 G_LDET_LIST_LINE_TYPE QP_PREQ_GRP.VARCHAR_TYPE;
81 G_LDET_LIST_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
82 G_LDET_PRICING_GROUP_SEQUENCE QP_PREQ_GRP.PLS_INTEGER_TYPE;
83 G_LDET_PRICING_PHASE_ID QP_PREQ_GRP.PLS_INTEGER_TYPE;
84 G_LDET_OPERATOR QP_PREQ_GRP.VARCHAR_TYPE;
85 G_LDET_OPERAND_VALUE QP_PREQ_GRP.VARCHAR_TYPE;
86 G_LDET_SUBSTITUTION_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
87 G_LDET_SUBSTITUTION_VALUE_FROM QP_PREQ_GRP.VARCHAR_TYPE;
88 G_LDET_SUBSTITUTION_VALUE_TO QP_PREQ_GRP.VARCHAR_TYPE;
89 G_LDET_PRICE_FORMULA_ID QP_PREQ_GRP.NUMBER_TYPE;
90 G_LDET_PRODUCT_PRECEDENCE QP_PREQ_GRP.PLS_INTEGER_TYPE;
91 G_LDET_INCOM_GRP_CODE QP_PREQ_GRP.VARCHAR_TYPE;
92 G_LDET_APPLIED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
93 G_LDET_AUTOMATIC_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
94 G_LDET_OVERRIDE_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
95 G_LDET_MODIFIER_LEVEL_CODE QP_PREQ_GRP.VARCHAR_TYPE;
96 G_LDET_BENEFIT_QTY QP_PREQ_GRP.NUMBER_TYPE;
97 G_LDET_BENEFIT_UOM_CODE QP_PREQ_GRP.VARCHAR_TYPE;
98 G_LDET_LIST_LINE_NO QP_PREQ_GRP.VARCHAR_TYPE;
99 G_LDET_ACCRUAL_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
100 G_LDET_ACCRUAL_CONVERSION_RATE QP_PREQ_GRP.NUMBER_TYPE;
101 G_LDET_ESTIM_ACCRUAL_RATE QP_PREQ_GRP.NUMBER_TYPE;
102 G_LDET_CHARGE_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
103 G_LDET_CHARGE_SUBTYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
104 G_LDET_LINE_QUANTITY QP_PREQ_GRP.NUMBER_TYPE;
105 G_LDET_UPDATED_FLAG QP_PREQ_GRP.VARCHAR_TYPE;
106 G_LDET_CALCULATION_CODE QP_PREQ_GRP.VARCHAR_TYPE;
107 G_LDET_CHANGE_REASON_CODE QP_PREQ_GRP.VARCHAR_TYPE;
108 G_LDET_CHANGE_REASON_TEXT QP_PREQ_GRP.VARCHAR_TYPE;
109 G_LDET_ACCUM_CONTEXT QP_PREQ_GRP.VARCHAR_TYPE; -- accum range break
110 G_LDET_ACCUM_ATTRIBUTE QP_PREQ_GRP.VARCHAR_TYPE; -- accum range break
111 G_LDET_ACCUM_FLAG QP_PREQ_GRP.VARCHAR_TYPE; -- accum range break
112 G_LDET_BREAK_UOM_CODE QP_PREQ_GRP.VARCHAR_TYPE; /* Proration*/
113 G_LDET_BREAK_UOM_CONTEXT QP_PREQ_GRP.VARCHAR_TYPE; /* Proration*/
114 G_LDET_BREAK_UOM_ATTRIBUTE QP_PREQ_GRP.VARCHAR_TYPE;
115 G_LDET_ADJUSTMENT_AMOUNT QP_PREQ_GRP.NUMBER_TYPE;
116
117 Procedure Preprocessing(
118 px_Header_Rec IN OUT NOCOPY OE_ORDER_PUB.Header_Rec_Type
119 ,px_Line_Rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
120 ) AS
121 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
122 BEGIN
123 -- Need both header rec and line rec to price the order
124
125 IF (px_line_rec.line_id IS NULL
126 OR px_line_rec.header_id IS NULL
127 OR px_line_rec.line_id = FND_API.G_MISS_NUM
128 OR px_line_rec.header_id = FND_API.G_MISS_NUM) THEN
129 RAISE FND_API.G_EXC_ERROR;
130 END IF;
131
132 IF (px_header_rec.header_id IS NULL
133 OR px_header_rec.header_id = FND_API.G_MISS_NUM
134 OR px_header_rec.order_type_id IS NULL
135 OR px_header_rec.order_type_id = FND_API.G_MISS_NUM
136 OR px_header_rec.transactional_curr_code IS NULL
137 OR px_header_rec.transactional_curr_code = FND_API.G_MISS_CHAR) THEN
138 px_header_rec := oe_header_util.query_row(px_line_rec.header_id);
139 END IF;
140
141 IF l_debug_level > 0 THEN
142 oe_debug_pub.add( 'PRICE_ORDER: SETTING REQUEST ID' , 1 ) ;
143 END IF;
144
145 G_PASS_LINE_TBL.delete;
146 qp_price_request_context.set_request_id;
147
148 IF l_debug_level > 0 THEN
149 oe_debug_pub.add( ' REQUEST ID IS : ' || QP_PREQ_GRP.G_REQUEST_ID , 1 ) ;
150 END IF;
151
152 -- Use the given header to price
153 OE_ORDER_PUB.g_hdr := px_header_rec;
154
155 END Preprocessing;
156
157 Procedure Insert_Manual_Adj(px_Line_Adj_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Adj_Tbl_Type
158 )
159 IS
160 i pls_integer;
161 j pls_integer;
162 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
163 BEGIN
164 -- HTML change
165
166 j := 1;
167 i := px_line_adj_Tbl.first;
168 while i is not null loop
169 IF ((px_line_adj_tbl(i).modifier_level_code='ORDER' OR G_PASS_LINE_TBL.exists(px_Line_Adj_Tbl(i).line_id)) AND px_line_adj_tbl(i).list_line_id IS NOT NULL and px_line_adj_tbl(i).applied_flag='Y') THEN
170
171 IF l_debug_level > 0 THEN
172 oe_debug_pub.add('adding adjustment:'||j);
173 END IF;
174
175 BEGIN
176 select
177 lhdr.LIST_TYPE_CODE,
178 ql.PRICING_GROUP_SEQUENCE,
179 ql.PRICING_PHASE_ID,
180 ql.ARITHMETIC_OPERATOR,
181 ql.PRODUCT_PRECEDENCE,
182 ql.INCOMPATIBILITY_GRP_CODE,
183 ql.LIST_LINE_NO,
184 ql.ACCRUAL_FLAG,
185 ql.ACCRUAL_CONVERSION_RATE,
186 ql.ESTIM_ACCRUAL_RATE,
187 ql.CHARGE_TYPE_CODE,
188 ql.CHARGE_SUBTYPE_CODE,
189 ql.OVERRIDE_FLAG
190 INTO
191 G_LDET_LIST_TYPE_CODE(j),
192 G_LDET_PRICING_GROUP_SEQUENCE(j),
193 G_LDET_PRICING_PHASE_ID(j),
194 G_LDET_OPERATOR(j),
195 G_LDET_PRODUCT_PRECEDENCE(j),
196 G_LDET_INCOM_GRP_CODE(j),
197 G_LDET_LIST_LINE_NO(j),
198 G_LDET_ACCRUAL_FLAG(j),
199 G_LDET_ACCRUAL_CONVERSION_RATE(j),
200 G_LDET_ESTIM_ACCRUAL_RATE(j),
201 G_LDET_CHARGE_TYPE_CODE(j),
202 G_LDET_CHARGE_SUBTYPE_CODE(j),
203 G_LDET_OVERRIDE_FLAG(j)
204 From
205 qp_list_lines ql,
206 qp_list_headers_b lhdr
207 Where ql.list_line_id = px_line_adj_tbl(i).list_line_id
208 and lhdr.list_header_id = px_line_adj_tbl(i).list_header_id;
209 EXCEPTION
210 WHEN OTHERS THEN
211 RAISE FND_API.G_EXC_ERROR;
212 END;
213
214 G_LDET_LINE_DETAIL_INDEX(j) := QP_PREQ_GRP.GET_LINE_DETAIL_INDEX;
215 G_LDET_LINE_DETAIL_TYPE_CODE(j) := NULL;
216 G_LDET_PRICE_BREAK_TYPE_CODE(j) := NULL;
217 G_LDET_LINE_INDEX(j) := px_line_adj_tbl(i).header_id + nvl(px_line_adj_tbl(i).line_id, 0);
218 G_LDET_LIST_HEADER_ID(j) := px_line_adj_tbl(i).list_header_id;
219 G_LDET_LIST_LINE_ID(j) := px_line_adj_tbl(i).list_line_id;
220 G_LDET_LIST_LINE_TYPE(j) := px_line_adj_Tbl(i).list_line_type_code;
221 G_LDET_OPERAND_VALUE(j) := px_line_adj_tbl(i).operand;
222 G_LDET_SUBSTITUTION_TYPE_CODE(j) := NULL;
223 G_LDET_SUBSTITUTION_VALUE_FROM(j) := NULL;
224 G_LDET_SUBSTITUTION_VALUE_TO(j) := NULL;
225 G_LDET_PRICE_FORMULA_ID(j) := NULL;
226 G_LDET_BENEFIT_QTY(j) := NULL;
227 G_LDET_BENEFIT_UOM_CODE(j) := NULL;
228 G_LDET_APPLIED_FLAG(j) := 'Y';
229 G_LDET_AUTOMATIC_FLAG(j) := nvl(px_line_adj_tbl(i).automatic_flag,'Y');
230 G_LDET_MODIFIER_LEVEL_CODE(j) := px_line_adj_tbl(i).modifier_level_code;
231 -- G_LDET_PROCESS_CODE(j),
232 G_LDET_LINE_QUANTITY(j) := 1;
233 G_LDET_UPDATED_FLAG(j) := px_line_adj_tbl(i).updated_flag;
234 G_LDET_CALCULATION_CODE(j) := NULL;
235 G_LDET_CHANGE_REASON_CODE(j) := NULL;
236 G_LDET_CHANGE_REASON_TEXT(j) := NULL;
237 G_LDET_ADJUSTMENT_AMOUNT(j) := px_line_adj_tbl(i).adjusted_amount;
238
239
240 G_LDET_ACCUM_CONTEXT(j) := NULL;
241 G_LDET_ACCUM_ATTRIBUTE(j) := NULL;
242 G_LDET_ACCUM_FLAG(j) := NULL;
243 G_LDET_BREAK_UOM_CODE(j) := NULL;
244 G_LDET_BREAK_UOM_CONTEXT(j) := NULL;
245 G_LDET_BREAK_UOM_ATTRIBUTE(j) := NULL;
246
247 IF l_debug_level > 0 THEN
248 oe_debug_pub.add('after adding adjustment:'||j);
249 END IF;
250
251 j := j + 1;
252
253 END IF;
254 i := px_line_adj_tbl.next(i);
255 end loop;
256 exception
257 when others then
258 IF l_debug_level > 0 THEN
259 oe_debug_pub.add('wrong in insert manual adj'||SQLERRM);
260 END IF;
261 RAISE FND_API.G_EXC_ERROR;
262 END Insert_Manual_Adj;
263
264 -- AG change --
265 procedure copy_Header_to_request(
266 p_header_rec OE_Order_PUB.Header_Rec_Type
267 ,p_Request_Type_Code varchar2
268 ,p_calculate_price_flag varchar2
269 ,px_line_index in out NOCOPY NUMBER
270 )
271 is
272 l_req_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
273 --l_line_index pls_integer := px_req_line_tbl.count;
274 --
275 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
276 --
277 begin
278 G_STMT_NO := 'copy_Header_to_request#10';
279 IF l_debug_level > 0 THEN
280 oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.COPY_HEADER_TO_REQUEST' , 1 ) ;
281 END IF;
282
283 --l_line_index := l_line_index+1;
284 px_line_index := px_line_index+1;
285
286
287 l_req_line_rec.CURRENCY_CODE := p_Header_rec.transactional_curr_code;
288 QP_PREQ_GRP.G_CURRENCY_CODE := l_req_line_rec.currency_code;
289 l_req_line_rec.PRICE_FLAG := p_calculate_price_flag;
290 l_req_line_rec.Active_date_first_type := 'ORD';
291 l_req_line_rec.Active_date_first := p_Header_rec.Ordered_date;
292
293 G_LINE_INDEX_TBL(px_line_index) := p_header_rec.header_id;
294 G_LINE_TYPE_CODE_TBL(px_line_index) := 'ORDER';
295 IF (p_header_rec.pricing_date is not null and p_header_rec.pricing_date <> FND_API.G_MISS_DATE) THEN
296 G_PRICING_EFFECTIVE_DATE_TBL(px_line_index)
297 := TRUNC(nvl(p_header_rec.pricing_date,sysdate));
298 ELSE
299 G_PRICING_EFFECTIVE_DATE_TBL(px_line_index)
300 := TRUNC(sysdate);
301 END IF;
302 IF (p_header_rec.ordered_date is not null and p_header_rec.ordered_date <> FND_API.G_MISS_DATE) THEN
303 G_ACTIVE_DATE_FIRST_TBL(px_line_index)
304 := TRUNC(p_header_rec.Ordered_date);
305 ELSE
306 G_ACTIVE_DATE_FIRST_TBL(px_line_index)
307 := TRUNC(sysdate);
308 END IF;
309 G_ACTIVE_DATE_FIRST_TYPE_TBL(px_line_index)
310 := 'ORD';
311 G_ACTIVE_DATE_SECOND_TBL(px_line_index)
312 := NULL;
313 G_ACTIVE_DATE_SECOND_TYPE_TBL(px_line_index)
314 := NULL;
315 G_LINE_QUANTITY_TBL(px_line_index) := NULL;
316 G_LINE_UOM_CODE_TBL(px_line_index) := NULL;
317 G_REQUEST_TYPE_CODE_TBL(px_line_index) := 'ONT';
318 G_PRICED_QUANTITY_TBL(px_line_index) := NULL;
319 G_UOM_QUANTITY_TBL(px_line_index) := NULL;
320 G_PRICED_UOM_CODE_TBL(px_line_index) := NULL;
321 G_CURRENCY_CODE_TBL(px_line_index) := p_header_rec.transactional_CURR_CODE;
322 G_UNIT_PRICE_TBL(px_line_index) := NULL;
323 G_PERCENT_PRICE_TBL(px_line_index) := NULL;
324 G_ADJUSTED_UNIT_PRICE_TBL(px_line_index) := NULL;
325 G_PROCESSED_FLAG_TBL(px_line_index) := QP_PREQ_GRP.G_NOT_PROCESSED;
326 G_PRICE_FLAG_TBL(px_line_index) := p_calculate_price_flag;
327 G_LINE_ID_TBL(px_line_index) := p_header_rec.header_id;
328 G_ROUNDING_FLAG_TBL(px_line_index)
329 := 'Q';
330 G_ROUNDING_FACTOR_TBL(px_line_index) := NULL;
331 G_PROCESSING_ORDER_TBL(px_line_index) := NULL;
332 G_PRICING_STATUS_CODE_tbl(px_line_index) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
333 G_PRICING_STATUS_TEXT_tbl(px_line_index) := NULL;
334
335 G_QUALIFIERS_EXIST_FLAG_TBL(px_line_index) :='N';
336 G_PRICING_ATTRS_EXIST_FLAG_TBL(px_line_index) :='N';
337 G_PRICE_LIST_ID_TBL(px_line_index) :=NULL;
338 G_PL_VALIDATED_FLAG_TBL(px_line_index) := 'N';
339 IF (p_header_rec.price_request_code is not null and p_header_rec.price_request_code <> FND_API.G_MISS_CHAR) THEN
340 G_PRICE_REQUEST_CODE_TBL(px_line_index) := p_header_rec.price_request_code;
341 ELSE
342 G_PRICE_REQUEST_CODE_TBL(px_line_index) := NULL;
343 END IF;
344 G_USAGE_PRICING_TYPE_TBL(px_line_index) :='REGULAR';
345 G_UPD_ADJUSTED_UNIT_PRICE_TBL(px_line_index) :=NULL;
346 G_LINE_CATEGORY_TBL(px_line_index):=NULL;
347 G_CATCHWEIGHT_QTY_TBL(px_line_index) := NULL;
348 G_ACTUAL_ORDER_QTY_TBL(px_line_index):=NULL;
349
350 IF l_debug_level > 0 THEN
351 oe_debug_pub.add( 'EXISTING OE_ORDER_PRICE_PVT.COPY_HEADER_TO_REQUEST' , 1 ) ;
352 END IF;
353
354 end copy_Header_to_request;
355
356 procedure copy_Line_to_request(
357 p_Line_rec OE_Order_PUB.Line_Rec_Type
358 --,px_req_line_tbl in out nocopy QP_PREQ_GRP.LINE_TBL_TYPE
359 ,p_pricing_events varchar2
360 ,p_request_type_code varchar2
361 ,p_honor_price_flag varchar2
362 ,px_line_index in out NOCOPY NUMBER
363 )
364 is
365 --l_line_index pls_integer := nvl(px_req_line_tbl.count,0);
366 l_uom_rate NUMBER;
367 v_discounting_privilege VARCHAR2(30);
368 l_item_type_code VARCHAR2(30);
369 l_item_rec OE_ORDER_CACHE.item_rec_type; --OPM 2434270
370 l_dummy VARCHAR2(30);
371 x_return_status VARCHAR2(30);
372 x_msg_count NUMBER;
373 x_msg_data VARCHAR2(2000);
374 x_secondary_quantity NUMBER;
375 x_secondary_uom_code VARCHAR2(3);
376 l_shipped_quantity2 NUMBER;
377 x_item_rec OE_Order_Cache.Item_Rec_Type;
378 l_UOM_QUANTITY NUMBER;
379 l_Calculate_Price_Flag varchar2(1);
380 --
381 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
382 --
383 begin
384
385 px_line_index := px_line_index + 1;
386 G_LINE_INDEX_TBL(px_line_index) := p_line_rec.header_id + p_line_rec.line_id;
387 G_LINE_TYPE_CODE_TBL(px_line_index) := 'LINE';
388
389 G_PRICING_EFFECTIVE_DATE_TBL(px_line_index):= TRUNC(nvl(p_line_rec.PRICING_DATE,sysdate));
390 G_ACTIVE_DATE_FIRST_TBL(px_line_index) := OE_Order_Pub.G_HDR.Ordered_date;
391 G_ACTIVE_DATE_FIRST_TYPE_TBL(px_line_index):= 'ORD';
392 IF (p_line_rec.schedule_ship_date is not null) THEN
393 G_ACTIVE_DATE_SECOND_TBL(px_line_index) := TRUNC(p_line_rec.schedule_ship_date);
394 G_ACTIVE_DATE_SECOND_TYPE_TBL(px_line_index):= 'SHIP';
395 ELSE
396 G_ACTIVE_DATE_SECOND_TBL(px_line_index) := NULL;
397 G_ACTIVE_DATE_SECOND_TYPE_TBL(px_line_index):= NULL;
398 END IF;
399 /*
400 IF l_debug_level > 0 THEN
401 oe_debug_pub.add( 'QUANTITY'||L_REQ_LINE_REC.LINE_QUANTITY||' '||L_REQ_LINE_REC.PRICED_QUANTITY , 3 ) ;
402 END IF;
403 IF l_debug_level > 0 THEN
404 oe_debug_pub.add( 'PRICE FLAG'||L_REQ_LINE_REC.PRICE_FLAG ) ;
405 END IF;
406 */
407 G_LINE_QUANTITY_TBL(px_line_index) := p_line_rec.ordered_quantity;
408 G_LINE_UOM_CODE_TBL(px_line_index) := p_line_rec.order_quantity_uom;
409 G_REQUEST_TYPE_CODE_TBL(px_line_index) := 'ONT';
410 G_PRICED_QUANTITY_TBL(px_line_index) := NULL; --p_line_rec.PRICING_QUANTITY;
411 IF (p_line_rec.item_type_code = 'SERVICE' AND p_line_rec.service_period IS NULL) THEN
412 l_UOM_QUANTITY := 0;
413 Elsif (p_Line_rec.service_period = p_Line_rec.Order_quantity_uom) Then
414 l_UOM_QUANTITY := p_Line_rec.service_duration;
415 Elsif (p_line_rec.service_period IS NOT NULL and p_line_rec.service_period <> FND_API.G_MISS_CHAR) THEN
416 INV_CONVERT.INV_UM_CONVERSION(From_Unit => p_Line_rec.service_period
417 ,To_Unit => p_Line_rec.Order_quantity_uom
418 ,Item_ID => p_Line_rec.Inventory_item_id
419 ,Uom_Rate => l_Uom_rate);
420 l_UOM_QUANTITY := p_Line_rec.service_duration * l_uom_rate;
421 End If;
422 G_UOM_QUANTITY_TBL(px_line_index) := l_UOM_QUANTITY;
423 G_PRICED_UOM_CODE_TBL(px_line_index) := p_line_rec.PRICING_QUANTITY_UOM;
424 G_CURRENCY_CODE_TBL(px_line_index) := oe_order_pub.g_hdr.TRANSACTIONAL_CURR_CODE;
425 G_UNIT_PRICE_TBL(px_line_index) := p_line_Rec.UNIT_LIST_PRICE_PER_PQTY;
426 G_PERCENT_PRICE_TBL(px_line_index) := NULL;
427 G_ADJUSTED_UNIT_PRICE_TBL(px_line_index) := p_line_rec.UNIT_SELLING_PRICE_PER_PQTY;
428 G_PROCESSED_FLAG_TBL(px_line_index) := QP_PREQ_GRP.G_NOT_PROCESSED;
429 G_PRICE_FLAG_TBL(px_line_index) := nvl(p_line_rec.CALCULATE_PRICE_FLAG,'Y');
430 G_LINE_ID_TBL(px_line_index) := p_line_rec.LINE_ID;
431 IF l_debug_level > 0 THEN
432 oe_debug_pub.add( 'LINE ID IN G_LINE_ID_TBL:'|| G_LINE_ID_TBL ( PX_LINE_INDEX ) ) ;
433 END IF;
434 G_ROUNDING_FLAG_TBL(px_line_index) := 'Q';
435 G_ROUNDING_FACTOR_TBL(px_line_index) := NULL;
436 G_PROCESSING_ORDER_TBL(px_line_index) := NULL;
437 G_PRICING_STATUS_CODE_tbl(px_line_index) := QP_PREQ_GRP.G_STATUS_UNCHANGED; -- AG
438 G_PRICING_STATUS_TEXT_tbl(px_line_index) := NULL;
439 G_QUALIFIERS_EXIST_FLAG_TBL(px_line_index) :='N';
440 G_PRICING_ATTRS_EXIST_FLAG_TBL(px_line_index) :='N';
441 G_PRICE_LIST_ID_TBL(px_line_index) :=p_line_rec.price_list_id;
442 G_PL_VALIDATED_FLAG_TBL(px_line_index) := 'N';
443 G_PRICE_REQUEST_CODE_TBL(px_line_index) := p_line_rec.price_request_code;
444 G_USAGE_PRICING_TYPE_TBL(px_line_index) :='REGULAR';
445 G_UPD_ADJUSTED_UNIT_PRICE_TBL(px_line_index) :=NULL;
446 G_LINE_CATEGORY_TBL(px_line_index) := p_line_rec.line_category_code;
447 G_catchweight_qty_tbl(px_line_index) := NULL;
448 g_actual_order_qty_tbl(px_line_index):= NULL;
449 IF l_debug_level > 0 THEN
450 oe_debug_pub.add( 'EXISTING OE_ORDER_PRICE_PVT.COPY_LINE_TO_REQUEST' , 1 ) ;
451 END IF;
452
453 end copy_Line_to_request;
454
455 Procedure Insert_lines(
456 p_Header_Rec IN OE_ORDER_PUB.Header_Rec_Type
457 , px_Line_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
458 , p_order_status_rec QP_UTIL_PUB.ORDER_LINES_STATUS_REC_TYPE
459 , p_pricing_events IN VARCHAR2
460 ) IS
461 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
462 l_pass_line_flag VARCHAR2(1);
463 l_check_line_flag VARCHAR2(1);
464 l_source_line_flag VARCHAR2(1);
465 l_line_rec OE_ORDER_PUB.Line_Rec_Type;
466 i pls_integer;
467 l_line_index PLS_INTEGER := 0;
468
469 BEGIN
470 i := px_Line_Tbl.FIRST;
471 While i is Not Null Loop
472 l_line_rec := px_Line_Tbl(i);
473 IF (l_line_rec.PRICING_DATE = FND_API.G_MISS_DATE) THEN
474 l_line_Rec.PRICING_DATE := sysdate;
475 END IF;
476
477 IF (l_line_rec.UNIT_LIST_PRICE_PER_PQTY = FND_API.G_MISS_NUM) THEN
478 IF (l_line_rec.UNIT_LIST_PRICE = FND_API.G_MISS_NUM) THEN
479 l_line_Rec.UNIT_LIST_PRICE := NULL;
480 END IF;
481 l_line_Rec.UNIT_LIST_PRICE_PER_PQTY := l_line_rec.UNIT_LIST_PRICE;
482 END IF;
483 If (l_line_rec.calculate_price_Flag is null or l_line_rec.calculate_price_flag = FND_API.G_MISS_CHAR) THEN
484 l_line_rec.calculate_price_flag := 'Y';
485 End IF;
486
487 If (l_line_rec.operation is null or l_line_rec.operation = FND_API.G_MISS_CHAR) THEN
488 l_line_rec.operation := oe_globals.g_opr_create;
489 End IF;
490
491 l_pass_line_flag := 'N';
492 l_check_line_flag := 'N';
493 l_source_line_flag := 'N';
494 IF (l_line_rec.calculate_price_flag = 'N' AND p_order_status_rec.summary_line_flag = 'Y')
495 THEN
496 l_pass_line_flag := 'Y';
497 ELSIF l_line_rec.calculate_price_flag <> 'N'
498 THEN
499 IF (p_order_status_rec.changed_lines_flag = 'Y' AND (
500 l_line_rec.operation = OE_GLOBALS.G_OPR_CREATE
501 OR l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE)
502 )
503 THEN
504 l_source_line_flag := 'Y';
505 l_pass_line_flag := 'Y';
506 ELSIF (p_order_status_rec.all_lines_flag = 'Y' AND
507 l_line_rec.operation <> OE_GLOBALS.G_OPR_DELETE)
508 THEN
509 l_check_line_flag := 'Y';
510 l_source_line_flag := 'Y';
511 END IF;
512 END IF;
513
514 IF (l_source_line_flag = 'Y')
515 THEN
516
517 OE_ORDER_PUB.g_line := l_line_rec;
518 QP_Attr_Mapping_PUB.Build_Contexts(
519 p_request_type_code => 'ONT',
520 --p_line_index => l_line_index,
521 p_line_index => l_line_rec.header_id
522 +l_line_rec.line_id,
523 p_pricing_type_code => 'L',
524 p_check_line_flag => l_check_line_flag,
525 p_pricing_event => p_pricing_events,
526 x_pass_line => l_pass_line_flag
527 );
528 END IF;
529
530 IF (l_pass_line_flag = 'Y')
531 THEN
532 copy_Line_to_request(
533 p_Line_rec => l_line_rec
534 ,p_pricing_events => p_pricing_events
535 ,p_request_type_code => 'ONT'
536 ,p_honor_price_flag => 'Y'
537 ,px_line_index => l_line_index
538 );
539
540 G_PASS_LINE_TBL(l_line_rec.line_id) := l_line_rec.line_id;
541 End If;
542
543 i := px_line_tbl.next(i);
544 END LOOP;
545
546
547 QP_Attr_Mapping_PUB.Build_Contexts(
548 p_request_type_code => 'ONT',
549 p_line_index=>oe_order_pub.g_hdr.header_id,
550 p_pricing_type_code => 'H'
551 );
552
553
554 copy_header_to_request(
555 p_Header_rec => p_Header_Rec
556 ,p_request_type_code => 'ONT'
557 ,p_calculate_price_flag => 'Y'
558 ,px_line_index => l_line_index
559 );
560
561 END Insert_Lines;
562
563
564 procedure Populate_Temp_Table
565 IS
566 l_return_status varchar2(1);
567 l_return_status_Text varchar2(240) ;
568 --
569 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
570 --
571 BEGIN
572 l_return_status := FND_API.G_RET_STS_SUCCESS;
573 IF l_debug_level > 0 THEN
574 oe_debug_pub.add( 'BEFORE DIRECT INSERT INTO TEMP TABLE: BULK INSERT'||G_LINE_INDEX_TBL.COUNT , 1 ) ;
575 END IF;
576 QP_PREQ_GRP.INSERT_LINES2
577 (p_LINE_INDEX => G_LINE_INDEX_TBL,
578 p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
579 p_PRICING_EFFECTIVE_DATE =>G_PRICING_EFFECTIVE_DATE_TBL,
580 p_ACTIVE_DATE_FIRST =>G_ACTIVE_DATE_FIRST_TBL,
581 p_ACTIVE_DATE_FIRST_TYPE =>G_ACTIVE_DATE_FIRST_TYPE_TBL,
582 p_ACTIVE_DATE_SECOND =>G_ACTIVE_DATE_SECOND_TBL,
583 p_ACTIVE_DATE_SECOND_TYPE =>G_ACTIVE_DATE_SECOND_TYPE_TBL,
584 p_LINE_QUANTITY => G_LINE_QUANTITY_TBL,
585 p_LINE_UOM_CODE => G_LINE_UOM_CODE_TBL,
586 p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
587 p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
588 p_PRICED_UOM_CODE => G_PRICED_UOM_CODE_TBL,
589 p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
590 p_UNIT_PRICE => G_UNIT_PRICE_TBL,
591 p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
592 p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
593 p_ADJUSTED_UNIT_PRICE =>G_ADJUSTED_UNIT_PRICE_TBL,
594 p_UPD_ADJUSTED_UNIT_PRICE =>G_UPD_ADJUSTED_UNIT_PRICE_TBL,
595 p_PROCESSED_FLAG =>G_PROCESSED_FLAG_TBL,
596 p_PRICE_FLAG =>G_PRICE_FLAG_TBL,
597 p_LINE_ID =>G_LINE_ID_TBL,
598 p_PROCESSING_ORDER =>G_PROCESSING_ORDER_TBL,
599 p_PRICING_STATUS_CODE =>G_PRICING_STATUS_CODE_tbl,
600 p_PRICING_STATUS_TEXT =>G_PRICING_STATUS_TEXT_tbl,
601 p_ROUNDING_FLAG =>G_ROUNDING_FLAG_TBL,
602 p_ROUNDING_FACTOR =>G_ROUNDING_FACTOR_TBL,
603 p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
604 p_PRICING_ATTRS_EXIST_FLAG =>G_PRICING_ATTRS_EXIST_FLAG_TBL,
605 p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
606 p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
607 p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
608 p_USAGE_PRICING_TYPE => G_USAGE_PRICING_TYPE_tbl,
609 p_line_category => G_LINE_CATEGORY_tbl,
610 p_catchweight_qty => G_CATCHWEIGHT_QTY_tbl,
611 p_actual_order_qty => G_ACTUAL_ORDER_QTY_TBL,
612 x_status_code =>l_return_status,
613 x_status_text =>l_return_status_text);
614
615 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
616 IF l_debug_level > 0 THEN
617 oe_debug_pub.add( 'WRONG IN INSERT_LINES2'||L_RETURN_STATUS_TEXT , 1 ) ;
618 END IF;
619 FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS'); --bug#7149497
620 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
621 OE_MSG_PUB.Add;
622 RAISE FND_API.G_EXC_ERROR;
623 END IF;
624
625 IF G_LDET_LINE_DETAIL_INDEX.count > 0 THEN
626 IF l_debug_level > 0 THEN
627 oe_debug_pub.add( 'inserting adjustments:'||G_LDET_LINE_DETAIL_INDEX.count);
628 oe_debug_pub.add('first:'||G_LDET_LINE_INDEX.first||' last:'||G_LDET_LINE_INDEX.last);
629 FOR i in G_LDET_LINE_DETAIL_INDEX.first..G_LDET_LINE_DETAIL_INDEX.last LOOP
630 oe_debug_pub.add('1:'||G_LDET_LINE_DETAIL_INDEX(i));
631 oe_debug_pub.add('2:'||G_LDET_LINE_DETAIL_TYPE_CODE(i));
632 oe_debug_pub.add('3:'||G_LDET_LINE_INDEX(i));
633 oe_debug_pub.add('4:'||G_LDET_PRICE_BREAK_TYPE_CODE(i));
634 oe_debug_pub.add('5:'||G_LDET_LIST_HEADER_ID(i));
635 oe_debug_pub.add('6:'||G_LDET_LIST_LINE_ID(i));
636 oe_debug_pub.add('7:'||G_LDET_LIST_LINE_TYPE(i));
637 oe_debug_pub.add('8:'||G_LDET_LIST_TYPE_CODE(i));
638 oe_debug_pub.add('9:'||G_LDET_PRICING_GROUP_SEQUENCE(i));
639 oe_debug_pub.add('10:'||G_LDET_PRICING_PHASE_ID(i));
640 oe_debug_pub.add('11:'||G_LDET_OPERATOR(i));
641 oe_debug_pub.add('12:'||G_LDET_OPERAND_VALUE(i));
642 oe_debug_pub.add('13:'||G_LDET_SUBSTITUTION_TYPE_CODE(i));
643 oe_debug_pub.add('14:'||G_LDET_SUBSTITUTION_VALUE_FROM(i));
644 oe_debug_pub.add('15:'||G_LDET_SUBSTITUTION_VALUE_TO(i));
645 oe_debug_pub.add('16:'||G_LDET_PRICE_FORMULA_ID(i));
646 oe_debug_pub.add('17:'||G_LDET_PRODUCT_PRECEDENCE(i));
647 oe_debug_pub.add('18:'||G_LDET_INCOM_GRP_CODE(i));
648 oe_debug_pub.add('19:'||G_LDET_APPLIED_FLAG(i));
649 oe_debug_pub.add('20:'||G_LDET_AUTOMATIC_FLAG(i));
650 oe_debug_pub.add('21:'||G_LDET_OVERRIDE_FLAG(i));
651 oe_debug_pub.add('22:'||G_LDET_MODIFIER_LEVEL_CODE(i));
652 oe_debug_pub.add('23:'||G_LDET_BENEFIT_QTY(i));
653 oe_debug_pub.add('24:'||G_LDET_BENEFIT_UOM_CODE(i));
654 oe_debug_pub.add('25:'||G_LDET_LIST_LINE_NO(i));
655 oe_debug_pub.add('26:'||G_LDET_ACCRUAL_FLAG(i));
656 oe_debug_pub.add('27:'||G_LDET_ACCRUAL_CONVERSION_RATE(i));
657 oe_debug_pub.add('28:'||G_LDET_ESTIM_ACCRUAL_RATE(i));
658 oe_debug_pub.add('29:'||G_LDET_CHARGE_TYPE_CODE(i));
659 oe_debug_pub.add('30:'||G_LDET_CHARGE_SUBTYPE_CODE(i));
660 oe_debug_pub.add('31:'||G_LDET_LINE_QUANTITY(i));
661 oe_debug_pub.add('32:'||G_LDET_UPDATED_FLAG(i));
662 oe_debug_pub.add('33:'||G_LDET_CALCULATION_CODE(i));
663 oe_debug_pub.add('34:'||G_LDET_CHANGE_REASON_CODE(i));
664 oe_debug_pub.add('35:'||G_LDET_CHANGE_REASON_TEXT(i));
665 oe_debug_pub.add('36:'||G_LDET_ACCUM_CONTEXT(i));
666 oe_debug_pub.add('37:'||G_LDET_ACCUM_ATTRIBUTE(i));
667 oe_debug_pub.add('38:'||G_LDET_ACCUM_FLAG(i));
668 oe_debug_pub.add('39:'||G_LDET_BREAK_UOM_CODE(i));
669 oe_debug_pub.add('40:'||G_LDET_BREAK_UOM_CONTEXT(i));
670 oe_debug_pub.add('41:'||G_LDET_BREAK_UOM_ATTRIBUTE(i));
671 END LOOP;
672 END IF;
673
674 QP_PREQ_GRP.INSERT_LDETS2
675 (p_LINE_DETAIL_INDEX => G_LDET_LINE_DETAIL_INDEX,
676 p_LINE_DETAIL_TYPE_CODE => G_LDET_LINE_DETAIL_TYPE_CODE,
677 p_PRICE_BREAK_TYPE_CODE => G_LDET_PRICE_BREAK_TYPE_CODE,
678 p_LINE_INDEX => G_LDET_LINE_INDEX,
679 p_LIST_HEADER_ID => G_LDET_LIST_HEADER_ID,
680 p_LIST_LINE_ID => G_LDET_LIST_LINE_ID,
681 p_LIST_LINE_TYPE_CODE => G_LDET_LIST_LINE_TYPE,
682 p_LIST_TYPE_CODE => G_LDET_LIST_TYPE_CODE,
683 p_PRICING_GROUP_SEQUENCE => G_LDET_PRICING_GROUP_SEQUENCE,
684 p_PRICING_PHASE_ID => G_LDET_PRICING_PHASE_ID,
685 p_OPERAND_CALCULATION_CODE => G_LDET_OPERATOR,
686 p_OPERAND_VALUE => G_LDET_OPERAND_VALUE,
687 p_SUBSTITUTION_TYPE_CODE => G_LDET_SUBSTITUTION_TYPE_CODE,
688 p_SUBSTITUTION_VALUE_FROM => G_LDET_SUBSTITUTION_VALUE_FROM,
689 p_SUBSTITUTION_VALUE_TO => G_LDET_SUBSTITUTION_vALUE_TO,
690 p_PRICE_FORMULA_ID => G_LDET_PRICE_FORMULA_ID,
691 p_PRODUCT_PRECEDENCE => G_LDET_PRODUCT_PRECEDENCE,
692 p_INCOMPATABLILITY_GRP_CODE => G_LDET_INCOM_GRP_CODE,
693 p_APPLIED_FLAG => G_LDET_APPLIED_FLAG,
694 p_AUTOMATIC_FLAG => G_LDET_AUTOMATIC_FLAG,
695 p_OVERRIDE_FLAG => G_LDET_OVERRIDE_FLAG,
696 p_MODIFIER_LEVEL_CODE => G_LDET_MODIFIER_LEVEL_CODE,
697 p_BENEFIT_QTY => G_LDET_BENEFIT_QTY,
698 p_BENEFIT_UOM_CODE => G_LDET_BENEFIT_UOM_CODE,
699 p_LIST_LINE_NO => G_LDET_LIST_LINE_NO,
700 p_ACCRUAL_FLAG => G_LDET_ACCRUAL_FLAG,
701 p_ACCRUAL_CONVERSION_RATE => G_LDET_ACCRUAL_CONVERSION_RATE,
702 p_ESTIM_ACCRUAL_RATE => G_LDET_ESTIM_ACCRUAL_RATE,
703 p_CHARGE_TYPE_CODE => G_LDET_CHARGE_TYPE_CODE,
704 p_CHARGE_SUBTYPE_CODE => G_LDET_CHARGE_SUBTYPE_CODE,
705 p_LINE_QUANTITY => G_LDET_LINE_QUANTITY,
706 p_UPDATED_FLAG => G_LDET_UPDATED_FLAG,
707 p_CALCULATION_CODE => G_LDET_CALCULATION_CODE,
708 p_CHANGE_REASON_CODE => G_LDET_CHANGE_REASON_CODE,
709 p_CHANGE_REASON_TEXT => G_LDET_CHANGE_REASON_TEXT,
710 p_ACCUM_CONTEXT => G_LDET_ACCUM_CONTEXT,
711 p_ACCUM_ATTRIBUTE => G_LDET_ACCUM_ATTRIBUTE,
712 p_ACCUM_FLAG => G_LDET_ACCUM_FLAG,
713 p_BREAK_UOM_CODE => G_LDET_BREAK_UOM_CODE,
714 p_BREAK_UOM_CONTEXT => G_LDET_BREAK_UOM_CONTEXT,
715 p_BREAK_UOM_ATTRIBUTE => G_LDET_BREAK_UOM_ATTRIBUTE,
716 x_status_code => l_return_status,
717 x_status_text => l_return_status_text);
718 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
719 IF l_debug_level > 0 THEN
720 oe_debug_pub.add( 'ERROR INSERTING INTO LINE DETAILS'||SQLERRM ) ;
721 END IF;
722 FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS');--bug#7149497
723 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
724 OE_MSG_PUB.Add;
725 raise fnd_api.g_exc_unexpected_error;
726 END IF;
727 END IF;
728
729 IF G_ATTR_LINE_INDEX_tbl.count > 0 THEN
730 QP_PREQ_GRP.INSERT_LINE_ATTRS2
731 ( G_ATTR_LINE_INDEX_tbl,
732 G_ATTR_LINE_DETAIL_INDEX_tbl ,
733 G_ATTR_ATTRIBUTE_LEVEL_tbl ,
734 G_ATTR_ATTRIBUTE_TYPE_tbl ,
735 G_ATTR_LIST_HEADER_ID_tbl ,
736 G_ATTR_LIST_LINE_ID_tbl ,
737 G_ATTR_PRICING_CONTEXT_tbl ,
738 G_ATTR_PRICING_ATTRIBUTE_tbl ,
739 G_ATTR_VALUE_FROM_tbl ,
740 G_ATTR_SETUP_VALUE_FROM_tbl ,
741 G_ATTR_VALUE_TO_tbl ,
742 G_ATTR_SETUP_VALUE_TO_tbl ,
743 G_ATTR_GROUPING_NUMBER_tbl ,
744 G_ATTR_NO_QUAL_IN_GRP_tbl ,
745 G_ATTR_COMP_OPERATOR_TYPE_tbl ,
746 G_ATTR_VALIDATED_FLAG_tbl ,
747 G_ATTR_APPLIED_FLAG_tbl ,
748 G_ATTR_PRICING_STATUS_CODE_tbl ,
749 G_ATTR_PRICING_STATUS_TEXT_tbl ,
750 G_ATTR_QUAL_PRECEDENCE_tbl ,
751 G_ATTR_DATATYPE_tbl ,
752 G_ATTR_PRICING_ATTR_FLAG_tbl ,
753 G_ATTR_QUALIFIER_TYPE_tbl ,
754 G_ATTR_PRODUCT_UOM_CODE_TBL ,
755 G_ATTR_EXCLUDER_FLAG_TBL ,
756 G_ATTR_PRICING_PHASE_ID_TBL ,
757 G_ATTR_INCOM_GRP_CODE_TBL,
758 G_ATTR_LDET_TYPE_CODE_TBL,
759 G_ATTR_MODIFIER_LEVEL_CODE_TBL,
760 G_ATTR_PRIMARY_UOM_FLAG_TBL,
761 l_return_status ,
762 l_return_status_text );
763
764 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
765 IF l_debug_level > 0 THEN
766 oe_debug_pub.add( 'ERROR INSERTING INTO LINE ATTRS'||SQLERRM ) ;
767 END IF;
768 FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS');--bug#7149497
769 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
770 OE_MSG_PUB.Add;
771 raise fnd_api.g_exc_unexpected_error;
772 END IF;
773
774 END IF;
775 IF l_debug_level > 0 THEN
776 oe_debug_pub.add( 'AFTER DIRECT INSERT INTO TEMP TABLE: BULK INSERT' , 1 ) ;
777 END IF;
778
779 EXCEPTION
780 WHEN OTHERS THEN
781 RAISE FND_API.G_EXC_ERROR;
782 END POPULATE_TEMP_TABLE;
783
784
785 /*+--------------------------------------------------------------------
786 |Reset_All_Tbls
787 |To Reset all pl/sql tables.
788 +--------------------------------------------------------------------
789 */
790 PROCEDURE Reset_All_Tbls
791 AS
792 --
793 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
794 --
795 BEGIN
796 G_LINE_INDEX_tbl.delete;
797 G_LINE_TYPE_CODE_TBL.delete ;
798 G_PRICING_EFFECTIVE_DATE_TBL.delete ;
799 G_ACTIVE_DATE_FIRST_TBL.delete ;
800 G_ACTIVE_DATE_FIRST_TYPE_TBL.delete ;
801 G_ACTIVE_DATE_SECOND_TBL.delete ;
802 G_ACTIVE_DATE_SECOND_TYPE_TBL.delete ;
803 G_LINE_QUANTITY_TBL.delete ;
804 G_LINE_UOM_CODE_TBL.delete ;
805 G_REQUEST_TYPE_CODE_TBL.delete ;
806 G_PRICED_QUANTITY_TBL.delete ;
807 G_UOM_QUANTITY_TBL.delete ;
808 G_PRICED_UOM_CODE_TBL.delete ;
809 G_CURRENCY_CODE_TBL.delete ;
810 G_UNIT_PRICE_TBL.delete ;
811 G_PERCENT_PRICE_TBL.delete ;
812 G_ADJUSTED_UNIT_PRICE_TBL.delete ;
813 G_PROCESSED_FLAG_TBL.delete ;
814 G_PRICE_FLAG_TBL.delete ;
815 G_LINE_ID_TBL.delete ;
816 G_PROCESSING_ORDER_TBL.delete ;
817 G_ROUNDING_FLAG_TBL.delete;
818 G_ROUNDING_FACTOR_TBL.delete ;
819 G_PRICING_STATUS_CODE_TBL.delete ;
820 G_PRICING_STATUS_TEXT_TBL.delete ;
821
822 G_ATTR_LINE_INDEX_tbl.delete;
823 G_ATTR_ATTRIBUTE_LEVEL_tbl.delete;
824 G_ATTR_VALIDATED_FLAG_tbl.delete;
825 G_ATTR_ATTRIBUTE_TYPE_tbl.delete;
826 G_ATTR_PRICING_CONTEXT_tbl.delete;
827 G_ATTR_PRICING_ATTRIBUTE_tbl.delete;
828 G_ATTR_APPLIED_FLAG_tbl.delete;
829 G_ATTR_PRICING_STATUS_CODE_tbl.delete;
830 G_ATTR_PRICING_ATTR_FLAG_tbl.delete;
831 G_ATTR_LIST_HEADER_ID_tbl.delete;
832 G_ATTR_LIST_LINE_ID_tbl.delete;
833 G_ATTR_VALUE_FROM_tbl.delete;
834 G_ATTR_SETUP_VALUE_FROM_tbl.delete;
835 G_ATTR_VALUE_TO_tbl.delete;
836 G_ATTR_SETUP_VALUE_TO_tbl.delete;
837 G_ATTR_GROUPING_NUMBER_tbl.delete;
838 G_ATTR_NO_QUAL_IN_GRP_tbl.delete;
839 G_ATTR_COMP_OPERATOR_TYPE_tbl.delete;
840 G_ATTR_VALIDATED_FLAG_tbl.delete;
841 G_ATTR_APPLIED_FLAG_tbl.delete;
842 G_ATTR_PRICING_STATUS_CODE_tbl.delete;
843 G_ATTR_PRICING_STATUS_TEXT_tbl.delete;
844 G_ATTR_QUAL_PRECEDENCE_tbl.delete;
845 G_ATTR_DATATYPE_tbl.delete;
846 G_ATTR_PRICING_ATTR_FLAG_tbl.delete ;
847 G_ATTR_QUALIFIER_TYPE_tbl.delete;
848 G_ATTR_PRODUCT_UOM_CODE_TBL.delete;
849 G_ATTR_EXCLUDER_FLAG_TBL.delete;
850 G_ATTR_PRICING_PHASE_ID_TBL.delete;
851 G_ATTR_INCOM_GRP_CODE_TBL.delete;
852 G_ATTR_LDET_TYPE_CODE_TBL.delete;
853 G_ATTR_MODIFIER_LEVEL_CODE_TBL.delete;
854 G_ATTR_PRIMARY_UOM_FLAG_TBL.delete;
855 G_LDET_LINE_DETAIL_INDEX.delete;
856 G_LDET_LINE_DETAIL_TYPE_CODE.delete;
857 G_LDET_PRICE_BREAK_TYPE_CODE.delete;
858 G_LDET_LINE_INDEX.delete;
859 G_LDET_LIST_HEADER_ID.delete;
860 G_LDET_LIST_LINE_ID.delete;
861 G_LDET_LIST_LINE_TYPE.delete;
862 G_LDET_LIST_TYPE_CODE.delete;
863 G_LDET_PRICING_GROUP_SEQUENCE.delete;
864 G_LDET_PRICING_PHASE_ID.delete;
865 G_LDET_OPERATOR.delete;
866 G_LDET_OPERAND_VALUE.delete;
867 G_LDET_SUBSTITUTION_TYPE_CODE.delete;
868 G_LDET_SUBSTITUTION_VALUE_FROM.delete;
869 G_LDET_SUBSTITUTION_VALUE_TO.delete;
870 G_LDET_PRICE_FORMULA_ID.delete;
871 -- G_LDET_PRICING_STATUS_CODE.delete;
872 G_LDET_PRODUCT_PRECEDENCE.delete;
873 G_LDET_INCOM_GRP_CODE.delete;
874 G_LDET_APPLIED_FLAG.delete;
875 G_LDET_AUTOMATIC_FLAG.delete;
876 G_LDET_OVERRIDE_FLAG.delete;
877 G_LDET_MODIFIER_LEVEL_CODE.delete;
878 G_LDET_BENEFIT_QTY.delete;
879 G_LDET_BENEFIT_UOM_CODE.delete;
880 G_LDET_LIST_LINE_NO.delete;
881 G_LDET_ACCRUAL_FLAG.delete;
882 G_LDET_ACCRUAL_CONVERSION_RATE.delete;
883 G_LDET_ESTIM_ACCRUAL_RATE.delete;
884 G_LDET_CHARGE_TYPE_CODE.delete;
885 G_LDET_CHARGE_SUBTYPE_CODE.delete;
886 -- G_LDET_PROCESS_CODE.delete;
887 G_LDET_LINE_QUANTITY.delete;
888 G_LDET_UPDATED_FLAG.delete; -- begin shu, fix Bug 2599822
889 G_LDET_CALCULATION_CODE.delete;
890 G_LDET_CHANGE_REASON_CODE.delete;
891 G_LDET_CHANGE_REASON_TEXT.delete;
892 G_LDET_ADJUSTMENT_AMOUNT.delete;
893
894 EXCEPTION
895 WHEN OTHERS THEN
896 IF l_debug_level > 0 THEN
897 oe_debug_pub.add( 'Reset all tables: '||SQLERRM , 1 ) ;
898 END IF;
899 END reset_all_tbls;
900
901 procedure Report_Engine_Errors(
902 x_return_status out nocopy Varchar2
903 , px_line_Tbl in out NOCOPY oe_Order_Pub.Line_Tbl_Type
904 , p_header_rec IN oe_Order_Pub.header_rec_type
905 )
906 is
907 l_line_rec oe_order_pub.line_rec_type;
908 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
909 i pls_Integer;
910 j pls_Integer:=0;
911 l_price_list Varchar2(240);
912 l_allow_negative_price Varchar2(30);
913 l_invalid_line Varchar2(1);
914 l_temp_line_rec oe_order_pub.line_rec_type;
915 l_request_id NUMBER;
916 vmsg Varchar2(2000);
917
918 cursor wrong_lines is
919 select line_id
920 , line_index
921 , line_type_code
922 , processed_code
923 , pricing_status_code
924 , pricing_status_text status_text
925 , unit_price
926 , adjusted_unit_price
927 , priced_quantity
928 , line_quantity
929 , priced_uom_code
930 from qp_preq_lines_tmp
931 where process_status <> 'NOT_VALID' and
932 (pricing_status_code not in
933 (QP_PREQ_GRP.G_STATUS_UNCHANGED,
934 QP_PREQ_GRP.G_STATUS_UPDATED,
935 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
936 'NOT_VALID')
937 OR (l_allow_negative_price = 'N' AND (unit_price<0 OR adjusted_unit_price<0)));
938 cursor wrong_book_lines is
939 select lines.line_id
940 , lines.unit_price
941 , lines.adjusted_unit_price
942 , lines.price_list_header_id
943 , lines.priced_quantity
944 , lines.line_quantity
945 , l.shipped_quantity
946 , l.header_id
947 from oe_order_lines l
948 , qp_preq_lines_tmp lines
949 where lines.line_id = l.line_id
950 and lines.line_type_code='LINE'
951 and l.booked_flag = 'Y'
952 and l.item_type_code NOT IN ('INCLUDED','CONFIG')
953 and (lines.unit_price is NULL
954 or lines.adjusted_unit_price is NULL
955 or lines.price_list_header_id is NULL)
956 and lines.process_status <> 'NOT_VALID'
957 ;
958 -- Bug 2079138: booked lines should always have price
959 --and lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_UPDATED
960 -- , QP_PREQ_GRP.G_STATUS_GSA_VIOLATION
961 -- );
962 --
963 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
964 --
965 begin
966 -- Update Order Lines
967 IF l_debug_level > 0 THEN
968 oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.REPORT_ENGINE_ERROR' , 1 ) ;
969 END IF;
970 G_STMT_NO := 'Report_Engine_Error#10';
971
972 l_allow_negative_price := nvl(fnd_profile.value('ONT_NEGATIVE_PRICING'),'N');
973 l_invalid_line := 'N';
974 For wrong_line in wrong_lines loop --i:= p_req_line_tbl.first;
975
976 J:= px_Line_Tbl.First;
977 While J is not null loop
978 IF l_debug_level > 0 THEN
979 oe_debug_pub.add( 'ERROR '||J||' LINE'||PX_LINE_TBL ( J ) .LINE_ID||' '||WRONG_LINE.LINE_ID ) ;
980 END IF;
981 If px_Line_Tbl(j).line_id = wrong_line.line_id
982 --or
983 -- J = wrong_line.line_index
984 then
985 l_line_rec := px_Line_Tbl(J);
986 IF l_debug_level > 0 THEN
987 oe_debug_pub.add( 'PRICE'||L_LINE_REC.UNIT_LIST_PRICE||'+'||L_LINE_REC.UNIT_LIST_PRICE_PER_PQTY ) ;
988 END IF;
989 exit;
990 End if;
991 J:= px_Line_Tbl.next(j);
992 end loop;
993
994 OE_MSG_PUB.set_msg_context
995 ( p_entity_code => 'LINE'
996 ,p_entity_id => l_line_rec.line_id
997 ,p_header_id => l_line_rec.header_id
998 ,p_line_id => l_line_rec.line_id
999 ,p_order_source_id => l_line_rec.order_source_id
1000 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
1001 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
1002 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
1003 ,p_change_sequence => l_line_rec.change_sequence
1004 ,p_source_document_type_id => l_line_rec.source_document_type_id
1005 ,p_source_document_id => l_line_rec.source_document_id
1006 ,p_source_document_line_id => l_line_rec.source_document_line_id
1007 );
1008
1009 IF l_debug_level > 0 THEN
1010 oe_debug_pub.add( 'THE STATUS'||WRONG_LINE.PRICING_STATUS_CODE||':'||WRONG_LINE.PROCESSED_CODE||':'||WRONG_LINE.STATUS_TEXT ) ;
1011 END IF;
1012 l_invalid_line := 'N';
1013 -- add message when the price list is found to be inactive
1014 IF wrong_line.line_Type_code ='LINE' and
1015 wrong_line.processed_code = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND Then
1016 IF l_debug_level > 0 THEN
1017 oe_debug_pub.add( 'PRICE LIST NOT FOUND' ) ;
1018 END IF;
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add( 'LINDA , INVALID PRICE LIST ' , 1 ) ;
1021 END IF;
1022 FND_MESSAGE.SET_NAME('ONT','ONT_NO_PRICE_LIST_FOUND');
1023 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
1024 FND_MESSAGE.SET_TOKEN('UOM',l_line_rec.Order_Quantity_uom);
1025 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1026 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1027 vmsg := FND_MESSAGE.GET;
1028 else
1029 OE_MSG_PUB.Add;
1030 end if;
1031 else
1032 OE_MSG_PUB.Add;
1033 end if;
1034
1035 l_invalid_line := 'Y';
1036 END IF;
1037
1038 if wrong_line.line_Type_code ='LINE' and
1039 wrong_line.pricing_status_code in ( QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST,
1040 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND,
1041 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR,
1042 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS,
1043 FND_API.G_RET_STS_UNEXP_ERROR,
1044 FND_API.G_RET_STS_ERROR,
1045 QP_PREQ_GRP.G_STATUS_CALC_ERROR,
1046 QP_PREQ_GRP.G_STATUS_UOM_FAILURE,
1047 QP_PREQ_GRP.G_STATUS_INVALID_UOM,
1048 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,
1049 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV,
1050 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP,
1051 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR)
1052 then
1053
1054 l_invalid_line := 'Y';
1055 Begin
1056 Select name into l_price_list
1057 from qp_list_headers_vl where
1058 list_header_id = l_line_rec.price_list_id;
1059 Exception When No_data_found then
1060 l_price_list := l_line_rec.price_list_id;
1061 End;
1062
1063 If wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST then
1064 IF l_debug_level > 0 THEN
1065 oe_debug_pub.add( 'INVALID ITEM/PRICE LIST COMBINATION'||L_LINE_REC.ORDERED_ITEM||L_LINE_REC.ORDER_QUANTITY_UOM||L_PRICE_LIST ) ;
1066 END IF;
1067 FND_MESSAGE.SET_NAME('ONT','OE_PRC_NO_LIST_PRICE');
1068 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
1069 FND_MESSAGE.SET_TOKEN('UNIT',l_line_rec.Order_Quantity_uom);
1070 FND_MESSAGE.SET_TOKEN('PRICE_LIST',l_Price_List);
1071 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1072 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1073 vmsg := FND_MESSAGE.GET;
1074 else
1075 OE_MSG_PUB.Add;
1076 end if;
1077 else
1078 OE_MSG_PUB.Add;
1079 end if;
1080 IF l_debug_level > 0 THEN
1081 oe_debug_pub.add( 'BEFORE CHECKING BOOK FLAG' ) ;
1082 END IF;
1083
1084 If nvl(l_line_rec.booked_flag,'X') = 'Y' Then
1085 IF l_debug_level > 0 THEN
1086 oe_debug_pub.add( ' EXCEPTION: PRICE LIST MISSING FOR BOOKED ORDER' ) ;
1087 END IF;
1088 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
1089 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_ORDER_UTIL.GET_ATTRIBUTE_NAME('UNIT_LIST_PRICE'));
1090 OE_MSG_PUB.ADD;
1091 RAISE FND_API.G_EXC_ERROR;
1092 End If;
1093 IF l_debug_level > 0 THEN
1094 oe_debug_pub.add( 'PRICE'||L_LINE_REC.UNIT_SELLING_PRICE||':'||L_LINE_REC.UNIT_LIST_PRICE ) ;
1095 END IF;
1096
1097 --Fix bug 1650637
1098 If (l_line_rec.unit_selling_price Is Not Null or
1099 l_line_rec.unit_list_price Is Not Null
1100 ) --AND NOT (p_control_rec.p_write_to_db)
1101 THEN
1102 l_line_rec.unit_selling_price := NULL;
1103 l_line_rec.unit_selling_price_per_pqty := NULL;
1104 l_line_rec.unit_list_price := NULL;
1105 l_line_rec.unit_list_price_per_pqty := NULL;
1106 END IF;
1107
1108
1109 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND Then
1110 IF l_debug_level > 0 THEN
1111 oe_debug_pub.add( 'PRICE LIST NOT FOUND' ) ;
1112 END IF;
1113 FND_MESSAGE.SET_NAME('ONT','ONT_NO_PRICE_LIST_FOUND');
1114 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
1115 FND_MESSAGE.SET_TOKEN('UOM',l_line_rec.Order_Quantity_uom);
1116 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1117 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1118 vmsg := FND_MESSAGE.GET;
1119 else
1120 OE_MSG_PUB.Add;
1121 end if;
1122 else
1123 OE_MSG_PUB.Add;
1124 end if;
1125 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_FORMULA_ERROR then
1126 IF l_debug_level > 0 THEN
1127 oe_debug_pub.add( 'ERROR IN FORMULA PROCESSING' ) ;
1128 END IF;
1129 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_ERROR_IN_FORMULA');
1130 FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
1131 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1132 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1133 vmsg := FND_MESSAGE.GET;
1134 else
1135 OE_MSG_PUB.Add;
1136 end if;
1137 else
1138 OE_MSG_PUB.Add;
1139 end if;
1140 Elsif wrong_line.pricing_status_code in
1141 ( QP_PREQ_GRP.G_STATUS_OTHER_ERRORS , FND_API.G_RET_STS_UNEXP_ERROR,
1142 FND_API.G_RET_STS_ERROR)
1143 then
1144 IF l_debug_level > 0 THEN
1145 oe_debug_pub.add( 'OTHER ERRORS PROCESSING' ) ;
1146 END IF;
1147 FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS');--bug#7149497
1148 FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
1149 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1150 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1151 vmsg := FND_MESSAGE.GET;
1152 else
1153 OE_MSG_PUB.Add;
1154 end if;
1155 else
1156 OE_MSG_PUB.Add;
1157 end if;
1158 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_UOM then
1159 IF l_debug_level > 0 THEN
1160 oe_debug_pub.add( 'INVALID UOM' ) ;
1161 END IF;
1162 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_UOM');
1163 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
1164 FND_MESSAGE.SET_TOKEN('UOM',l_line_rec.Order_Quantity_uom);
1165 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1166 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1167 vmsg := FND_MESSAGE.GET;
1168 else
1169 OE_MSG_PUB.Add;
1170 end if;
1171 else
1172 OE_MSG_PUB.Add;
1173 end if;
1174 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST then
1175 IF l_debug_level > 0 THEN
1176 oe_debug_pub.add( 'DUPLICATE PRICE LIST' ) ;
1177 END IF;
1178 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_DUPLICATE_PRICE_LIST');
1179
1180 Begin
1181 Select name into l_price_list
1182 from qp_list_headers_vl a,qp_list_lines b where
1183 b.list_line_id = to_number(substr(wrong_line.status_text,1,
1184 instr(wrong_line.status_text,',')-1))
1185 and a.list_header_id=b.list_header_id
1186 ;
1187 Exception When No_data_found then
1188 l_price_list := to_number(substr(wrong_line.status_text,1,
1189 instr(wrong_line.status_text,',')-1));
1190 When invalid_number then
1191 l_price_list := substr(wrong_line.status_text,1,
1192 instr(wrong_line.status_text,',')-1);
1193
1194 End;
1195
1196 FND_MESSAGE.SET_TOKEN('PRICE_LIST1',
1197 '( '||l_line_rec.Ordered_Item||' ) '||l_price_list);
1198 Begin
1199 Select name into l_price_list
1200 from qp_list_headers_vl a,qp_list_lines b where
1201 b.list_line_id =
1202 to_number(substr(wrong_line.status_text,
1203 instr(wrong_line.status_text,',')+1))
1204 and a.list_header_id=b.list_header_id ;
1205 Exception When No_data_found then
1206 l_price_list := to_number(substr(wrong_line.status_text,
1207 instr(wrong_line.status_text,',')+1));
1208 When invalid_number then
1209 l_price_list := substr(wrong_line.status_text,
1210 instr(wrong_line.status_text,',')+1);
1211 End;
1212 FND_MESSAGE.SET_TOKEN('PRICE_LIST2',l_price_list);
1213 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1214 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1215 vmsg := FND_MESSAGE.GET;
1216 else
1217 OE_MSG_PUB.Add;
1218 end if;
1219 else
1220 OE_MSG_PUB.Add;
1221 end if;
1222 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV then
1223 IF l_debug_level > 0 THEN
1224 oe_debug_pub.add( 'INVALID UOM CONVERSION' ) ;
1225 END IF;
1226 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_UOM_CONVERSION');
1227 FND_MESSAGE.SET_TOKEN('UOM_TEXT','( '||l_line_rec.Ordered_Item||' ) '||
1228 wrong_line.status_text);
1229 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1230 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1231 vmsg := FND_MESSAGE.GET;
1232 else
1233 OE_MSG_PUB.Add;
1234 end if;
1235 else
1236 OE_MSG_PUB.Add;
1237 end if;
1238 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_INCOMP then
1239 IF l_debug_level > 0 THEN
1240 oe_debug_pub.add( 'UNABLE TO RESOLVE INCOMPATIBILITY' ) ;
1241 END IF;
1242 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_INCOMP');
1243 FND_MESSAGE.SET_TOKEN('ERR_TEXT','( '||
1244 l_line_rec.Ordered_Item||' ) '||wrong_line.status_text);
1245 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1246 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1247 vmsg := FND_MESSAGE.GET;
1248 else
1249 OE_MSG_PUB.Add;
1250 end if;
1251 else
1252 OE_MSG_PUB.Add;
1253 end if;
1254 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR then
1255 IF l_debug_level > 0 THEN
1256 oe_debug_pub.add( 'ERROR WHILE EVALUATING THE BEST PRICE' ) ;
1257 END IF;
1258 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_BEST_PRICE_ERROR');
1259 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
1260 FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
1261 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1262 if l_line_rec.shipped_quantity is not null and l_line_rec.shipped_quantity <> FND_API.G_MISS_NUM then
1263 vmsg := FND_MESSAGE.GET;
1264 else
1265 OE_MSG_PUB.Add;
1266 end if;
1267 else
1268 OE_MSG_PUB.Add;
1269 end if;
1270 End if; /* wrong pricing status code */
1271
1272 --RAISE FND_API.G_EXC_ERROR;
1273 --btea begin if do not write to db, we still need to
1274 --return line and status code to the caller
1275 -- If Not p_control_rec.p_write_to_db Then
1276 l_line_rec.Header_id := p_header_rec.Header_id;
1277 l_line_rec.line_id := wrong_line.line_id;
1278 l_line_rec.unit_selling_price_per_pqty
1279 := wrong_line.adjusted_unit_price ;
1280 l_line_rec.unit_list_price_per_pqty
1281 := wrong_line.unit_price ;
1282 l_line_rec.pricing_quantity
1283 := wrong_line.priced_quantity ;
1284 l_line_rec.pricing_quantity_uom
1285 := wrong_line.priced_uom_code ;
1286 --use industry_attribute30 as the place holder to hold error status
1287 --since the line_rec doesn't have the place holder to hold error status
1288 l_line_rec.industry_attribute30
1289 := wrong_line.pricing_status_code;
1290 IF l_debug_level > 0 THEN
1291 oe_debug_pub.add( 'J IS '||J ) ;
1292 END IF;
1293 if (j<>0) THEN
1294 px_line_tbl(j) := l_line_rec;
1295 END IF;
1296
1297 IF l_debug_level > 0 THEN
1298 oe_debug_pub.add( 'PASSING BACK PRICE'||L_LINE_REC.UNIT_LIST_PRICE||' '||L_LINE_REC.UNIT_SELLING_PRICE ) ;
1299 END IF;
1300 IF l_debug_level > 0 THEN
1301 oe_debug_pub.add( 'LINE'||L_LINE_REC.HEADER_ID||'+'||L_LINE_REC.LINE_ID ) ;
1302 END IF;
1303 -- End If;
1304 --btea end
1305
1306 elsif
1307 wrong_line.line_Type_code ='LINE' and
1308 wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_OTHER_ERRORS
1309 Then
1310
1311 FND_MESSAGE.SET_NAME('ONT','OE_PRICING_ERROR');
1312 FND_MESSAGE.SET_TOKEN('ERR_TEXT','( '
1313 ||l_line_rec.Ordered_Item||' ) '||wrong_line.status_text);
1314 OE_MSG_PUB.Add;
1315 elsif wrong_line.line_type_code='LINE' and
1316 (wrong_line.unit_price <0 or wrong_line.adjusted_unit_price<0)
1317 Then
1318
1319 IF l_debug_level > 0 THEN
1320 oe_debug_pub.add( 'NEGATIVE LIST PRICE '||WRONG_LINE.UNIT_PRICE ||'OR SELLING PRICE '||WRONG_LINE.ADJUSTED_UNIT_PRICE ) ;
1321 END IF;
1322 FND_MESSAGE.SET_NAME('ONT','ONT_NEGATIVE_PRICE');
1323 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.Ordered_Item,l_line_rec.inventory_item_id));
1324 FND_MESSAGE.SET_TOKEN('LIST_PRICE',wrong_line.unit_price);
1325 FND_MESSAGE.SET_TOKEN('SELLING_PRICE',wrong_line.Adjusted_unit_price);
1326 OE_MSG_PUB.Add;
1327 IF l_debug_level > 0 THEN
1328 oe_debug_pub.add( 'BEFORE SHOWING NEGATIVE MODIFIERS MESSAGE' ) ;
1329 END IF;
1330 FND_MESSAGE.SET_NAME('ONT','ONT_NEGATIVE_MODIFIERS');
1331 --FND_MESSAGE.SET_TOKEN('LIST_LINE_NO',get_list_lines(wrong_line.line_id));
1332 OE_MSG_PUB.Add;
1333 IF l_debug_level > 0 THEN
1334 -- oe_debug_pub.add( 'MODIFIERS:'||GET_LIST_LINES ( WRONG_LINE.LINE_ID ) ) ;
1335 NULL;
1336 END IF;
1337 RAISE FND_API.G_EXC_ERROR;
1338
1339 end if;
1340
1341 end loop; /* wrong_lines cursor */
1342
1343 For book_line in wrong_book_lines loop
1344
1345 If book_line.adjusted_unit_price IS NULL Then
1346 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
1347 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1348 OE_Order_UTIL.Get_Attribute_Name('UNIT_SELLING_PRICE'));
1349 OE_MSG_PUB.ADD;
1350 End If;
1351
1352 If book_line.unit_price IS NULL
1353 Then
1354 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
1355 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1356 OE_Order_UTIL.Get_Attribute_Name('UNIT_LIST_PRICE'));
1357 IF l_debug_level > 0 THEN
1358 oe_debug_pub.add( ' ERROR: UNIT LIST PRICE CAN NOT BE NULL' ) ;
1359 END IF;
1360 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1361 if book_line.shipped_quantity is not null or book_line.shipped_quantity <> FND_API.G_MISS_NUM then
1362 vmsg := FND_MESSAGE.GET;
1363 -- pricing_errors_hold(book_line.header_id,book_line.line_id,vmsg);
1364 else
1365 OE_MSG_PUB.ADD;
1366 l_temp_line_rec.line_id := book_line.line_id;
1367 l_temp_line_rec.ordered_quantity := book_line.line_quantity;
1368 l_temp_line_rec.pricing_quantity := book_line.priced_quantity;
1369 --Oe_Order_Adj_Pvt.Reset_Fields(l_temp_line_rec);
1370 RAISE FND_API.G_EXC_ERROR;
1371 end if;
1372 else
1373 OE_MSG_PUB.ADD;
1374 end if;
1375 End If;
1376
1377 If book_line.price_list_header_id IS NULL Then
1378 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRUIBUTE');
1379 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_ORDER_UTIL.Get_Attribute_Name('PRICE_LIST_ID'));
1380 OE_MSG_PUB.ADD;
1381 IF l_debug_level > 0 THEN
1382 oe_debug_pub.add( ' ERROR: PRICE LIST ID CAN NOT BE NULL' ) ;
1383 END IF;
1384 RAISE FND_API.G_EXC_ERROR;
1385 END IF;
1386
1387 End loop; /* wrong booked lines */
1388 IF l_debug_level > 0 THEN
1389 oe_debug_pub.add( 'EXITING REPORT_ENGINE_ERRORS' ) ;
1390 END IF;
1391 End Report_Engine_Errors;
1392
1393 Procedure Calculate_Adjustments(
1394 px_Header_Rec IN OUT NOCOPY OE_ORDER_PUB.Header_Rec_Type
1395 ,px_Line_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
1396 ,px_Line_Adj_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Adj_Tbl_Type
1397 ,p_Line_Price_Att_Tbl IN Price_Att_Tbl_Type
1398 ,p_Action_Code IN VARCHAR2
1399 ,p_Pricing_Events IN VARCHAR2
1400 ,p_Simulation_Flag IN VARCHAR2
1401 ,p_Get_Freight_Flag IN VARCHAR2
1402 ,x_Return_Status OUT NOCOPY VARCHAR2 )
1403 IS
1404 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1405 l_order_status_rec QP_UTIL_PUB.ORDER_LINES_STATUS_REC_TYPE;
1406 BEGIN
1407
1408
1409 Reset_All_Tbls;
1410
1411 -- call QP API to determine whether or not to
1412 -- call lines to pricing engine
1413 QP_UTIL_PUB.Get_Order_Lines_Status(p_pricing_events,l_order_status_rec);
1414
1415 If l_order_status_rec.ALL_LINES_FLAG = 'Y' OR l_order_status_rec.CHANGED_LINES_FLAG = 'Y'
1416 OR l_order_status_rec.SUMMARY_LINE_FLAG = 'Y'
1417 Then
1418 Insert_Lines( px_Header_Rec
1419 , px_Line_Tbl
1420 , l_order_status_rec
1421 , p_Pricing_Events);
1422
1423 -- insert manual adjustments
1424 Insert_Manual_Adj(px_Line_Adj_Tbl);
1425
1426 Populate_Temp_Table;
1427 End if;
1428
1429 END Calculate_Adjustments;
1430
1431 Procedure Process_Adjustments(px_line_adj_tbl IN OUT NOCOPY oe_order_pub.line_adj_tbl_type
1432 ,p_Action_Code IN VARCHAR2
1433 ,x_Return_Status OUT NOCOPY VARCHAR2 )
1434 Is
1435 i PLS_INTEGER;
1436 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1437 Cursor adj_cur IS
1438 SELECT /*+ ORDERED USE_NL(ldets lines qh) */
1439 oe_price_adjustments_s.nextval price_adjustment_id
1440 , oe_order_pub.g_hdr.header_id header_id
1441 , ldets.automatic_flag automatic_flag
1442 , decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id) line_id
1443 , ldets.LIST_HEADER_ID list_header_id
1444 , ldets.LIST_LINE_ID list_line_id
1445 , ldets.LIST_LINE_TYPE_CODE list_line_type_code
1446 , NULL MODIFIER_MECHANISM_TYPE_CODE
1447 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL) modified_from
1448 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL) modified_to
1449 , ldets.UPDATED_FLAG
1450 , ldets.override_flag update_allowed
1451 , ldets.APPLIED_FLAG applied_flag
1452 , NULL CHANGE_REASON_CODE
1453 , NULL CHANGE_REASON_TEXT
1454 , ldets.order_qty_operand operand
1455 , ldets.operand_calculation_code arithmetic_operator
1456 , nvl(ldets.order_qty_adj_amt, 99) adjusted_amount
1457 , ldets.pricing_phase_id PRICING_PHASE_ID
1458 , ldets.CHARGE_TYPE_CODE charge_type_code
1459 , ldets.CHARGE_SUBTYPE_CODE charge_subtype_code
1460 , ldets.list_line_no list_line_no
1461 , qh.source_system_code source_system_code
1462 , ldets.benefit_qty benefit_qty
1463 , ldets.benefit_uom_code benefit_uom_code
1464 , ldets.expiration_date expiration_date
1465 , ldets.rebate_transaction_type_code rebate_transaction_Type_code
1466 , ldets.accrual_flag accrual_flag
1467 , ldets.line_quantity range_break_quantity
1468 , ldets.accrual_conversion_rate accrual_conversion_rate
1469 , ldets.pricing_group_sequence pricing_group_sequence
1470 , ldets.modifier_level_code modifier_level_code
1471 , ldets.price_break_type_code price_break_type_code
1472 , ldets.substitution_attribute substitution_attribute
1473 , ldets.proration_type_code proration_type_code
1474 , ldets.include_on_returns_flag include_on_returns_flag
1475 , ldets.OPERAND_value operand_per_pqty
1476 , ldets.adjustment_amount adjusted_amount_per_pqty
1477 , nvl(ldets.line_detail_type_code, 'DIS') line_detail_type_code
1478 FROM
1479 QP_LDETS_v ldets
1480 , QP_PREQ_LINES_TMP lines
1481 , QP_LIST_HEADERS_B QH
1482 WHERE
1483 ldets.list_header_id=qh.list_header_id
1484 AND ldets.process_code in (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_UNCHANGED)
1485 AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
1486 AND lines.process_status <> 'NOT_VALID'
1487 AND ldets.line_index=lines.line_index
1488 --AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1489 --AND nvl(ldets.automatic_flag,'N') = 'Y'
1490 -- or
1491 -- (ldets.list_line_type_code = 'FREIGHT_CHARGE'))
1492 AND ldets.created_from_list_type_code not in ('PRL','AGR')
1493 AND ldets.list_line_type_code<>'PLL'
1494 -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
1495 ;
1496 l_adj_tbl OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
1497 BEGIN
1498
1499 x_return_status := FND_API.G_RET_STS_SUCCESS;
1500 -- Populating QP line details table
1501
1502 i := 0;
1503 For adj in adj_cur loop
1504
1505 If (p_Action_Code = 'NONE' and adj.applied_flag = 'Y')
1506 OR (p_Action_Code = 'MANUAL' and adj.line_detail_type_code <> 'CHILD_DETAIL_LINE'
1507 and adj.applied_flag = 'N' and adj.automatic_flag = 'N'
1508 and adj.list_line_type_code in ('DIS', 'SUR'))
1509 Then
1510 i := i + 1;
1511 l_adj_tbl(i).price_adjustment_id := adj.price_adjustment_id;
1512 l_adj_tbl(i).HEADER_ID := adj.header_id;
1513 l_adj_tbl(i).AUTOMATIC_FLAG := adj.automatic_flag;
1514 l_adj_tbl(i).LINE_ID := adj.line_id;
1515 l_adj_tbl(i).LIST_HEADER_ID := adj.list_header_id;
1516 l_adj_tbl(i).LIST_LINE_ID := adj.list_line_id;
1517 l_adj_tbl(i).LIST_LINE_TYPE_CODE := adj.list_line_type_code;
1518 l_adj_tbl(i).MODIFIER_MECHANISM_TYPE_CODE := adj.modifier_mechanism_type_code;
1519 l_adj_tbl(i).MODIFIED_FROM := adj.modified_from;
1520 l_adj_tbl(i).MODIFIED_TO := adj.modified_to;
1521 l_adj_tbl(i).UPDATED_FLAG := adj.updated_Flag;
1522 l_adj_tbl(i).UPDATE_ALLOWED := adj.update_allowed;
1523 l_adj_tbl(i).APPLIED_FLAG := adj.applied_flag;
1524 l_adj_tbl(i).CHANGE_REASON_CODE := adj.change_reason_code;
1525 l_adj_tbl(i).CHANGE_REASON_TEXT := adj.change_reason_text;
1526 l_adj_tbl(i).operand := adj.operand;
1527 l_adj_tbl(i).Arithmetic_operator := adj.arithmetic_operator;
1528 l_adj_tbl(i).ADJUSTED_AMOUNT := adj.adjusted_amount;
1529 l_adj_tbl(i).PRICING_PHASE_ID := adj.pricing_phase_id;
1530 l_adj_tbl(i).CHARGE_TYPE_CODE := adj.charge_type_code;
1531 l_adj_tbl(i).CHARGE_SUBTYPE_CODE := adj.charge_subtype_code;
1532 l_adj_tbl(i).list_line_no := adj.list_line_no;
1533 l_adj_tbl(i).source_system_code := adj.source_system_code;
1534 l_adj_tbl(i).benefit_qty := adj.benefit_qty;
1535 l_adj_tbl(i).benefit_uom_code := adj.benefit_uom_code;
1536 -- l_adj_tbl(i).print_on_invoice_flag :=adj.print_on_invoice_flag;
1537 l_adj_tbl(i).expiration_date := adj.expiration_date;
1538 -- l_adj_tbl(i).rebate_transaction_type_code := adj.rebate_transaction_type_code;
1539 -- l_adj_tbl(i).rebate_transaction_reference := adj.rebate_transaction_reference;
1540 -- l_adj_tbl(i).rebate_payment_system_code := adj.rebate_payment_system_code;
1541 -- l_adj_tbl(i).redeemed_date
1542 -- l_adj_tbl(i).redeemed_flag
1543 l_adj_tbl(i).accrual_flag := adj.accrual_flag;
1544 l_adj_tbl(i).range_break_quantity := adj.range_break_quantity;
1545 l_adj_tbl(i).accrual_conversion_rate := adj.accrual_conversion_rate;
1546 l_adj_tbl(i).pricing_group_sequence :=adj.pricing_group_sequence;
1547 l_adj_tbl(i).modifier_level_code := adj.modifier_level_code;
1548 l_adj_tbl(i).price_break_type_code := adj.price_break_type_code;
1549 -- l_adj_tbl(i).substitution_attribute
1550 -- l_adj_tbl(i).proration_type_code
1551 -- ,l_adj_tbl(i).CREDIT_OR_CHARGE_FLAG
1552 -- ,l_adj_tbl(i).INCLUDE_ON_RETURNS_FLAG
1553 l_adj_tbl(i).OPERAND_PER_PQTY := adj.operand_per_pqty;
1554 l_adj_tbl(i).ADJUSTED_AMOUNT_PER_PQTY := adj.adjusted_amount_per_pqty;
1555 -- ,l_adj_tbl(i).LOCK_CONTROL*/
1556
1557 IF (p_Action_Code = 'MANUAL') THEN
1558 l_adj_tbl(i).change_reason_code := 'MANUAL';
1559 SELECT meaning
1560 INTO l_adj_tbl(i).change_reason_text
1561 FROM oe_lookups
1562 WHERE lookup_type = 'CHANGE_CODE'
1563 AND lookup_code = l_adj_tbl(i).change_reason_code;
1564 END IF;
1565
1566 IF (l_adj_tbl(i).list_header_id IS NOT NULL) THEN
1567 SELECT name
1568 INTO l_adj_tbl(i).attribute1
1569 from qp_list_headers_vl
1570 where list_header_id = l_adj_tbl(i).list_header_id;
1571 END IF;
1572
1573 IF (l_adj_tbl(i).arithmetic_operator IS NOT NULL) THEN
1574 SELECT meaning
1575 into l_adj_tbl(i).attribute2
1576 from qp_lookups
1577 where lookup_code = l_adj_tbl(i).arithmetic_operator
1578 and lookup_type = 'ARITHMETIC_OPERATOR';
1579 END IF;
1580
1581 IF (l_adj_tbl(i).list_line_type_code IS NOT NULL) THEN
1582 SELECT meaning
1583 into l_adj_tbl(i).attribute3
1584 from qp_lookups
1585 where lookup_code = l_adj_tbl(i).list_line_type_code
1586 and lookup_type = 'LIST_LINE_TYPE_CODE';
1587 END IF;
1588
1589 IF (l_adj_tbl(i).pricing_phase_id IS NOT NULL) THEN
1590 SELECT name
1591 into l_adj_tbl(i).attribute4
1592 from qp_pricing_phases
1593 where pricing_phase_id = l_adj_tbl(i).pricing_phase_id;
1594 END IF;
1595
1596 IF (l_adj_tbl(i).automatic_flag IS NOT NULL) THEN
1597 SELECT meaning
1598 into l_adj_tbl(i).attribute5
1599 from qp_lookups
1600 where lookup_code = l_adj_tbl(i).automatic_flag
1601 and lookup_type = 'YES_NO';
1602 END IF;
1603 end if; -- action code
1604 end loop;
1605 px_line_adj_tbl := l_adj_tbl;
1606
1607 IF l_debug_level > 0 THEN
1608 oe_debug_pub.add( 'EXITING OE_PRICE_ORDER_PVT.PROCESS_ADJUSTMENTS' , 1 ) ;
1609 END IF;
1610
1611
1612 EXCEPTION
1613 WHEN FND_API.G_EXC_ERROR THEN
1614
1615 --reseting global structure after engine call
1616 OE_Order_PUB.G_LINE := NULL;
1617 OE_Order_PUB.G_HDR := NULL;
1618 x_return_status := FND_API.G_RET_STS_ERROR;
1619
1620 IF l_debug_level > 0 THEN
1621 oe_debug_pub.add( 'EXITING PROCESS_ADJUSTMENTS WITH EXC ERROR' , 1 ) ;
1622 END IF;
1623 RAISE FND_API.G_EXC_ERROR;
1624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1625
1626 --reseting global structure after engine call
1627 OE_Order_PUB.G_LINE := NULL;
1628 OE_Order_PUB.G_HDR := NULL;
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 IF l_debug_level > 0 THEN
1631 oe_debug_pub.add( 'EXITING PROCESS_ADJUSTMENTS WITH UNEXPECTED ERROR' , 1 ) ;
1632 END IF;
1633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1634 WHEN OTHERS THEN
1635
1636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1637 --reseting global structure after engine call
1638 OE_Order_PUB.G_LINE := NULL;
1639 OE_Order_PUB.G_HDR := NULL;
1640 IF l_debug_level > 0 THEN
1641 oe_debug_pub.add( 'ERROR IN OE_PRICE_ORDER_PVT.Process_Adjustments' , 1 ) ;
1642 END IF;
1643 IF l_debug_level > 0 THEN
1644 oe_debug_pub.add( SQLERRM , 1 ) ;
1645 END IF;
1646
1647 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1648 THEN
1649 OE_MSG_PUB.Add_Exc_Msg
1650 ( G_PKG_NAME
1651 , 'Process_Adjustments'
1652 );
1653 END IF;
1654
1655 IF l_debug_level > 0 THEN
1656 oe_debug_pub.add( 'EXITING OE_PRICE_ORDER_PVT.PRICE_ORDER' , 1 ) ;
1657 END IF;
1658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1659
1660 END Process_Adjustments;
1661
1662 Procedure Call_Pricing_Engine(
1663 p_Pricing_Events IN VARCHAR2
1664 ,x_return_status OUT NOCOPY VARCHAR2
1665
1666 )
1667 IS
1668 l_return_status_text varchar(2000);
1669 l_control_rec QP_PREQ_GRP.control_record_type;
1670 l_set_of_books Oe_Order_Cache.Set_Of_Books_Rec_Type;
1671 --
1672 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1673 --
1674 BEGIN
1675 IF l_debug_level > 0 THEN
1676 oe_debug_pub.add( 'BEFORE QP_PREQ_PUB.PRICE_REQUEST' , 1 ) ;
1677 END IF;
1678
1679 l_control_rec.calculate_flag := 'Y';
1680 l_control_rec.simulation_flag := 'Y';
1681 l_control_rec.pricing_event := p_Pricing_Events;
1682 l_control_rec.temp_table_insert_flag := 'N';
1683 l_control_rec.check_cust_view_flag := 'N';
1684 l_control_rec.request_type_code := 'ONT';
1685 --now pricing take care of all the roundings.
1686 l_control_rec.rounding_flag := 'Q';
1687 --For multi_currency price list
1688 l_control_rec.use_multi_currency:='Y';
1689 l_control_rec.USER_CONVERSION_RATE:= OE_ORDER_PUB.G_HDR.CONVERSION_RATE;
1690 l_control_rec.USER_CONVERSION_TYPE:= OE_ORDER_PUB.G_HDR.CONVERSION_TYPE_CODE;
1691 l_set_of_books := Oe_Order_Cache.Load_Set_Of_Books;
1692 l_control_rec.FUNCTION_CURRENCY := l_set_of_books.currency_code;
1693
1694 -- added for freight rating.
1695 l_control_rec.get_freight_flag := 'N';
1696 IF l_debug_level > 0 THEN
1697 oe_debug_pub.add('get_freight_flag before calling pricing engine is: '||l_control_rec.get_freight_flag, 3 ) ;
1698 END IF;
1699
1700 -- IF (G_PASS_ALL_LINES in ('N', 'R')) THEN
1701 -- l_control_rec.full_pricing_call := 'N';
1702 -- ELSE
1703 l_control_rec.full_pricing_call := 'Y';
1704 -- END IF;
1705 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
1706 l_control_rec.manual_adjustments_call_flag := 'N';
1707 END IF;
1708
1709 G_STMT_NO := 'QP_PRICE_REQUEST_GRP';
1710 QP_PREQ_PUB.PRICE_REQUEST
1711 (p_control_rec => l_control_rec
1712 --,p_line_tbl => l_Req_line_tbl
1713 --,p_qual_tbl => l_Req_qual_tbl
1714 --,p_line_attr_tbl => l_Req_line_attr_tbl
1715 --,p_line_detail_tbl =>l_req_line_detail_tbl
1716 --,p_line_detail_qual_tbl =>l_req_line_detail_qual_tbl
1717 --,p_line_detail_attr_tbl =>l_req_line_detail_attr_tbl
1718 --,p_related_lines_tbl =>l_req_related_lines_tbl
1719 --,x_line_tbl =>x_req_line_tbl
1720 --,x_line_qual =>x_Req_qual_tbl
1721 --,x_line_attr_tbl =>x_Req_line_attr_tbl
1722 --,x_line_detail_tbl =>x_req_line_detail_tbl
1723 --,x_line_detail_qual_tbl =>x_req_line_detail_qual_tbl
1724 --,x_line_detail_attr_tbl =>x_req_line_detail_attr_tbl
1725 --,x_related_lines_tbl =>x_req_related_lines_tbl
1726 ,x_return_status =>x_return_status
1727 ,x_return_status_Text =>l_return_status_Text
1728 );
1729
1730 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1731 FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS');--bug#7149497
1732 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
1733 OE_MSG_PUB.Add;
1734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1735 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1736 FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS');--bug#7149497
1737 FND_MESSAGE.SET_TOKEN('ERR_TEXT',l_return_status_text);
1738 OE_MSG_PUB.Add;
1739 RAISE FND_API.G_EXC_ERROR;
1740 END IF;
1741
1742 EXCEPTION
1743 WHEN OTHERS THEN
1744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1745 END Call_Pricing_Engine;
1746
1747 Procedure Update_Lines(px_Line_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type)
1748 IS
1749 l_line_index pls_integer;
1750 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1751 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
1752 BEGIN
1753 l_line_index := px_line_tbl.first;
1754 while l_line_index is not null loop
1755 l_line_rec := px_line_tbl(l_line_index);
1756 IF l_debug_level > 0 THEN
1757 oe_debug_pub.add( 'LINE ID'||L_LINE_REC.LINE_ID ) ;
1758 END IF;
1759
1760 BEGIN
1761
1762 select /*+ INDEX(lines qp_preq_lines_tmp_n1) */
1763 nvl(lines.order_uom_selling_price, lines.ADJUSTED_UNIT_PRICE * lines.priced_quantity /l_line_rec.ordered_quantity)
1764 , nvl(lines.line_unit_price, lines.UNIT_PRICE * lines.priced_quantity/l_line_rec.ordered_quantity)
1765 , lines.ADJUSTED_UNIT_PRICE
1766 , lines.UNIT_PRICE
1767 , decode(lines.priced_quantity,-99999,l_line_rec.ordered_quantity
1768 ,lines.priced_quantity)
1769 , decode(lines.priced_quantity,-99999,l_line_rec.order_quantity_uom
1770 ,lines.priced_uom_code)
1771 , decode(lines.price_list_header_id,-9999,NULL,lines.price_list_header_id) --Bug#2830609
1772 , nvl(lines.percent_price, NULL)
1773 , nvl(lines.parent_price, NULL)
1774 , decode(lines.parent_price, NULL, 0, 0, 0,
1775 lines.adjusted_unit_price/lines.parent_price)
1776 INTO
1777 l_line_rec.UNIT_SELLING_PRICE
1778 , l_line_rec.UNIT_LIST_PRICE
1779 , l_line_rec.UNIT_SELLING_PRICE_PER_PQTY
1780 , l_line_rec.UNIT_LIST_PRICE_PER_PQTY
1781 , l_line_rec.PRICING_QUANTITY
1782 , l_line_rec.PRICING_QUANTITY_UOM
1783 , l_line_rec.PRICE_LIST_ID
1784 , l_line_rec.UNIT_LIST_PERCENT
1785 , l_line_rec.UNIT_PERCENT_BASE_PRICE
1786 , l_line_rec.UNIT_SELLING_PERCENT
1787 from qp_preq_lines_tmp lines
1788 where lines.line_id=l_line_rec.line_id
1789 and lines.line_type_code='LINE'
1790 and l_line_rec.ordered_quantity <> 0
1791 and lines.process_status <> 'NOT_VALID'
1792 and lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_GSA_VIOLATION,
1793 QP_PREQ_GRP.G_STATUS_UPDATED)
1794 ;
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797
1798 l_line_rec.pricing_attribute1 := SQLERRM;
1799 END;
1800
1801 px_line_tbl(l_line_index) := l_line_rec;
1802 l_line_index := px_line_tbl.next(l_line_index);
1803 END LOOP;
1804 END Update_Lines;
1805
1806 -- Price_Order is to simulate a OM pricing call to get the pricing information
1807 -- It assumes the caller will pass all the information of the order to the call
1808 -- For example,if your order has two lines and you only call with one line,
1809 -- the call will price as if there is only one line
1810 -- You can pass a line but not to reprice it by setting operation code to G_OPR_NONE
1811 -- You can pass a line but freeze its price by setting calculate price flag to 'N'
1812 -- This API assumes the following setting:
1813
1814 -- Request_Type_Code: 'ONT'
1815 -- Write_To_DB: FALSE
1816 -- Honor_Price_FLag: TRUE
1817 -- Calculate_flag: TRUE
1818 -- Simulation_Flag: TRUE
1819 -- Get_Freight_FLag: FALSE
1820 Procedure Price_Order(
1821 px_Header_Rec IN OUT NOCOPY OE_ORDER_PUB.Header_Rec_Type
1822 ,px_Line_Rec IN OUT NOCOPY OE_ORDER_PUB.Line_Rec_Type
1823 -- ,px_Line_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type
1824 ,px_Line_Adj_Tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Adj_Tbl_Type
1825 ,p_Line_Price_Att_Tbl IN Price_Att_Tbl_Type
1826 ,p_Action_Code IN VARCHAR2
1827 ,p_Pricing_Events IN VARCHAR2
1828 ,p_Simulation_Flag IN VARCHAR2
1829 ,p_Get_Freight_Flag IN VARCHAR2
1830 ,x_Return_Status OUT NOCOPY VARCHAR2
1831 ) AS
1832 l_any_frozen_line BOOLEAN;
1833 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1834 l_header_adj_tbl OE_ORDER_PUB.Header_ADJ_Tbl_Type;
1835 l_header_price_att_tbl OE_PRICE_ORDER_PVT.price_att_tbl_type;
1836 l_Line_Tbl OE_ORDER_PUB.Line_Tbl_Type;
1837 i PLS_INTEGER;
1838 l_control_rec OE_ORDER_PRICE_PVT.Control_Rec_Type;
1839 l_hdr_chg_count NUMBER; --TaxER
1840 l_org_id NUMBER(15):=MO_GLOBAL.Get_Current_Org_Id; --TaxER
1841 l_current_event number := 0; --TaxER
1842 l_tax_calculation_event_code number := 0; --TaxER
1843 l_header_rec OE_Order_PUB.Header_Rec_Type; --TaxER
1844 BEGIN
1845
1846 x_return_status := FND_API.G_RET_STS_SUCCESS;
1847
1848
1849 Preprocessing(px_Header_Rec
1850 , px_Line_Rec);
1851
1852 l_Line_Tbl(1) := px_Line_Rec;
1853 Calculate_Adjustments(px_Header_Rec
1854 , l_Line_Tbl
1855 , px_Line_Adj_Tbl
1856 , p_Line_Price_Att_Tbl
1857 , nvl(p_Action_Code, 'NONE')
1858 , p_Pricing_Events
1859 , p_Simulation_Flag
1860 , p_Get_Freight_Flag
1861 , x_Return_Status);
1862 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1863 IF l_debug_level > 0 THEN
1864 oe_debug_pub.add( 'CALCULATE ADJUSTMENTS ERROR' ) ;
1865 END IF;
1866 raise fnd_api.g_exc_error;
1867 END IF;
1868
1869 IF (G_PASS_LINE_TBL.count > 0) THEN
1870 Call_Pricing_Engine(p_Pricing_Events, x_Return_Status);
1871 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1872 IF l_debug_level > 0 THEN
1873 oe_debug_pub.add( 'CALCULATE ADJUSTMENTS ERROR' ) ;
1874 END IF;
1875 raise fnd_api.g_exc_error;
1876 END IF;
1877
1878 l_control_rec.p_write_to_db := FALSE;
1879 Report_Engine_Errors(x_return_status => x_Return_Status
1880 , px_line_tbl => l_line_tbl
1881 , p_header_rec => px_header_rec);
1882 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1883 IF l_debug_level > 0 THEN
1884 oe_debug_pub.add( 'CALCULATE ADJUSTMENTS ERROR' ) ;
1885 END IF;
1886 raise fnd_api.g_exc_error;
1887 END IF;
1888
1889 Update_Lines(l_Line_Tbl);
1890 Process_Adjustments(px_line_adj_tbl
1891 , nvl(p_Action_Code, 'NONE')
1892 , x_Return_Status);
1893 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1894 IF l_debug_level > 0 THEN
1895 oe_debug_pub.add( 'CALCULATE ADJUSTMENTS ERROR' ) ;
1896 END IF;
1897 raise fnd_api.g_exc_error;
1898 END IF;
1899
1900
1901 --TaxER start
1902 IF (NVL(oe_sys_parameters.value('OE_INVOICE_FREIGHT_AS_LINE',l_org_id), 'N') = 'Y') THEN
1903 BEGIN
1904
1905 IF OE_Order_Cache.g_header_rec.order_type_id is not null THEN
1906
1907 IF (OE_Order_Cache.g_order_type_rec.order_type_id = FND_API.G_MISS_NUM)
1908 OR (OE_Order_Cache.g_order_type_rec.order_type_id is null)
1909 OR (OE_Order_Cache.g_order_type_rec.order_type_id <> OE_Order_Cache.g_header_rec.Order_Type_id)
1910 THEN
1911 OE_Order_Cache.Load_Order_type(OE_Order_CACHE.g_header_rec.Order_Type_id) ;
1912 END IF ;
1913
1914 IF (OE_Order_Cache.g_order_type_rec.order_type_id = OE_Order_Cache.g_header_rec.Order_Type_id) THEN
1915 if (OE_Order_Cache.g_order_type_rec.tax_calculation_event_code = 'ENTERING') then
1916 l_tax_calculation_event_code := 0;
1917 elsif (OE_Order_Cache.g_order_type_rec.tax_calculation_event_code = 'BOOKING') then
1918 l_tax_calculation_event_code := 1;
1919 elsif (OE_Order_Cache.g_order_type_rec.tax_calculation_event_code = 'SHIPPING') then
1920 l_tax_calculation_event_code := 2;
1921 elsif (OE_Order_Cache.g_order_type_rec.tax_calculation_event_code = 'INVOICING') then
1922 l_tax_calculation_event_code := 3;
1923 else
1924 l_tax_calculation_event_code := -1;
1925 end if ;
1926 ELSE
1927 l_tax_calculation_event_code := 0 ;
1928 END IF ;
1929
1930
1931 END IF;
1932
1933 EXCEPTION
1934 WHEN NO_DATA_FOUND THEN
1935 l_tax_calculation_event_code := 0;
1936 WHEN OTHERS THEN
1937 IF l_debug_level > 0 THEN
1938 oe_debug_pub.add('ONTHPROB:Failed while trying to query up tax_calcualtion_event for order_type_id ');
1939 END IF;
1940
1941 RAISE;
1942 END;
1943
1944 l_header_rec := OE_Header_UTIL.Query_Row(p_header_id=> l_Line_Tbl(1).header_id);
1945
1946 IF nvl(l_header_rec.booked_flag, 'N') = 'Y' THEN
1947 l_current_event := 1; /* current event is booking or higher */
1948 END IF;
1949
1950 IF l_debug_level > 0 THEN
1951 oe_debug_pub.add('ONTHPROB:Tax Event Code :'||l_tax_calculation_event_code);
1952 oe_debug_pub.add('ONTHPROB:Current Event :'||l_current_event);
1953 END IF;
1954
1955
1956 IF l_current_event >= l_tax_calculation_event_code THEN
1957
1958 /* commented for bug#12598464
1959 SELECT Count(1)
1960 INTO l_hdr_chg_count
1961 FROM oe_price_adjustments
1962 WHERE header_id= l_Line_Tbl(1).header_id
1963 AND list_line_type_code='FREIGHT_CODE'
1964 AND line_id IS NULL;
1965
1966
1967 IF l_hdr_chg_count > 0 THEN End of comment for bug#12598464*/
1968 IF (OE_GLOBALS.G_UI_FLAG)
1969 AND nvl(FND_PROFILE.VALUE('ONT_DELAY_TAX_CALC'), 'N') = 'N'
1970 THEN
1971 OE_delayed_requests_Pvt.log_request(
1972 p_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
1973 p_entity_id => l_Line_Tbl(1).header_id,
1974 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
1975 p_requesting_entity_id => l_Line_Tbl(1).header_id,
1976 p_request_type => OE_GLOBALS.g_tax_line,
1977 x_return_status => x_return_status);
1978 ELSE
1979 OE_delayed_requests_Pvt.log_request(
1980 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1981 p_entity_id => l_Line_Tbl(1).header_id,
1982 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_HEADER,
1983 p_requesting_entity_id => l_Line_Tbl(1).header_id,
1984 p_request_type => OE_GLOBALS.g_tax_line,
1985 p_param1 => NULL,
1986 x_return_status => x_return_status);
1987 END IF;
1988 --Bug#12598464 END IF;
1989 END IF;
1990
1991 END IF;
1992 --TaxER end
1993
1994 px_line_rec := l_Line_Tbl(1);
1995
1996 if (px_line_rec.unit_list_price is null) THEN
1997 x_return_status := FND_API.G_RET_STS_ERROR;
1998 END IF;
1999 END IF;
2000
2001 EXCEPTION
2002 WHEN FND_API.G_EXC_ERROR THEN
2003
2004 --reseting global structure after engine call
2005 OE_Order_PUB.G_LINE := NULL;
2006 OE_Order_PUB.G_HDR := NULL;
2007 x_return_status := FND_API.G_RET_STS_ERROR;
2008
2009 IF l_debug_level > 0 THEN
2010 oe_debug_pub.add( 'EXITING PRICE_ORDER WITH EXC ERROR' , 1 ) ;
2011 END IF;
2012 RAISE FND_API.G_EXC_ERROR;
2013 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2014
2015 --reseting global structure after engine call
2016 OE_Order_PUB.G_LINE := NULL;
2017 OE_Order_PUB.G_HDR := NULL;
2018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2019 IF l_debug_level > 0 THEN
2020 oe_debug_pub.add( 'EXITING PRICE_ORDER WITH UNEXPECTED ERROR' , 1 ) ;
2021 END IF;
2022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2023 WHEN OTHERS THEN
2024
2025 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2026 --reseting global structure after engine call
2027 OE_Order_PUB.G_LINE := NULL;
2028 OE_Order_PUB.G_HDR := NULL;
2029 IF l_debug_level > 0 THEN
2030 oe_debug_pub.add( 'ERROR IN OE_PRICE_ORDER_PVT.PRICE_ORDER' , 1 ) ;
2031 END IF;
2032 IF l_debug_level > 0 THEN
2033 oe_debug_pub.add( SQLERRM , 1 ) ;
2034 END IF;
2035
2036 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2037 THEN
2038 OE_MSG_PUB.Add_Exc_Msg
2039 ( G_PKG_NAME
2040 , 'PRICE_ORDER'
2041 );
2042 END IF;
2043
2044 IF l_debug_level > 0 THEN
2045 oe_debug_pub.add( 'EXITING OE_PRICE_ORDER_PVT.PRICE_ORDER' , 1 ) ;
2046 END IF;
2047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2048 END Price_Order;
2049
2050 end OE_PRICE_ORDER_PVT;