[Home] [Help]
PACKAGE BODY: APPS.OZF_ORDER_PRICE_PVT
Source
1 PACKAGE BODY OZF_ORDER_PRICE_PVT AS
2 /* $Header: ozfvorpb.pls 120.5 2011/09/14 09:02:39 nepanda ship $ */
3
4 -- Package name : OZF_ORDER_PRICE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_ORDER_PRICE_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvorpb.pls';
12
13 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
14 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
15 ---------------------------------------------------------------------
16 -- PROCEDURE
17 -- build_order_header
18 --
19 -- PURPOSE
20 -- This procedure use the input order information construct information
21 -- for an order header.
22 --
23 -- PARAMETERS
24 -- p_hdr in oe_order_pub.header_rec_type
25 -- x_return_status out varchar2
26 --
27 -- NOTES
28 ---------------------------------------------------------------------
29 PROCEDURE build_order_header(
30 p_hdr IN line_rec_type
31 ,x_return_status out NOCOPY varchar2)
32 IS
33 l_msg_data varchar2(2000);
34 l_msg_count number;
35 l_return_status varchar2(30);
36
37 l_msg_parameter_list WF_PARAMETER_LIST_T;
38
39 BEGIN
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42 IF OZF_DEBUG_HIGH_ON THEN
43 ozf_utility_PVT.debug_message('in build order header');
44 END IF;
45 -- Mapping, default and validation rules here
46 -- Since we can not use the OM defaulting rule. We just default order type based on the cust account id
47 /*
48 IF oe_order_pub.g_hdr.order_type_id is null or
49 oe_order_pub.g_hdr.order_type_id = FND_API.g_miss_num THEN
50
51 OPEN order_type_csr(oe_order_pub.g_hdr.sold_to_org_id);
52 FETCH order_type_csr into oe_order_pub.g_hdr.order_type_id;
53 CLOSE order_type_csr;
54 END IF;
55
56 OZF_CHARGEBACK_ATTRMAP_PUB.Create_Global_Header(
57 p_api_version => 1.0
58 ,p_init_msg_list => FND_API.G_FALSE
59 ,p_commit => FND_API.G_FALSE
60 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
61 ,x_return_status => l_return_status
62 ,x_msg_data => l_msg_data
63 ,x_msg_count => l_msg_count
64 ,xp_hdr => oe_order_pub.g_hdr
65 ,p_interface_id => p_hdr.chargeback_int_id
66 );
67 */
68
69 WF_EVENT.AddParameterToList(
70 p_name => 'INTERFACE_ID',
71 p_value => p_hdr.chargeback_int_id,
72 p_parameterlist => l_msg_parameter_list
73 );
74
75 -- bug 5331553 (+)
76 WF_EVENT.AddParameterToList(
77 p_name => 'RESALE_TABLE_TYPE',
78 p_value => p_hdr.resale_table_type,
79 p_parameterlist => l_msg_parameter_list
80 );
81 -- bug 5331553 (-)
82
83 WF_EVENT.raise (
84 p_event_name => 'oracle.apps.ozf.idsm.OMGHDR',
85 p_event_key => p_hdr.resale_table_type||to_char(p_hdr.chargeback_int_id)||'_'||to_char(sysdate,'YYYYMMDD HH24MISS'),
86 p_parameters => l_msg_parameter_list
87 );
88 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
89 RAISE FND_API.g_exc_unexpected_error;
90 END IF;
91 EXCEPTION
92 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94 WHEN OTHERS THEN
95 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
96 END build_order_header;
97
98 ---------------------------------------------------------------------
99 -- PROCEDURE
100 -- build_order_line
101 --
102 -- PURPOSE
103 -- This procedure use the input order information construct information
104 -- for an order header.
105 --
106 -- PARAMETERS
107 -- p_line in oe_order_pub.line_rec_type
108 -- x_return_status out NOCOPY varchar2
109 --
110 -- NOTES
111 ---------------------------------------------------------------------
112 PROCEDURE build_order_line(
113 p_line IN line_rec_type
114 ,x_return_status out NOCOPY varchar2)
115 IS
116 l_msg_data varchar2(2000);
117 l_msg_count number;
118 l_return_status varchar2(30);
119 l_msg_parameter_list WF_PARAMETER_LIST_T;
120 BEGIN
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122
123 IF OZF_DEBUG_HIGH_ON THEN
124 ozf_utility_PVT.debug_message('in build order line');
125 END IF;
126 -- Mapping, default and validation rules here
127 /*
128 OZF_CHARGEBACK_ATTRMAP_PUB.Create_Global_line(
129 p_api_version => 1.0
130 ,p_init_msg_list => FND_API.G_FALSE
131 ,p_commit => FND_API.G_FALSE
132 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
133 ,x_return_status => l_return_status
134 ,x_msg_data => l_msg_data
135 ,x_msg_count => l_msg_count
136 ,xp_line => oe_order_pub.g_line
137 ,p_interface_id => p_line.chargeback_int_id
138 );
139 */
140 WF_EVENT.AddParameterToList(
141 p_name => 'INTERFACE_ID',
142 p_value => p_line.chargeback_int_id,
143 p_parameterlist => l_msg_parameter_list
144 );
145
146 -- bug 5331553 (+)
147 WF_EVENT.AddParameterToList(
148 p_name => 'RESALE_TABLE_TYPE',
149 p_value => p_line.resale_table_type,
150 p_parameterlist => l_msg_parameter_list
151 );
152 -- bug 5331553 (-)
153
154 WF_EVENT.raise (
155 p_event_name => 'oracle.apps.ozf.idsm.OMGLINE',
156 p_event_key => p_line.resale_table_type||to_char(p_line.chargeback_int_id)||'_'||to_char(sysdate,'YYYYMMDD HH24MISS'),
157 p_parameters => l_msg_parameter_list
158 );
159 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
160 RAISE FND_API.g_exc_unexpected_error;
161 END IF;
162 EXCEPTION
163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
165 WHEN OTHERS THEN
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 END build_order_line;
168
169 ---------------------------------------------------------------------
170 -- PROCEDURE
171 -- Get_Order_Price
172 --
173 -- PURPOSE
174 -- Get_Order_Price
175 --
176 -- PARAMETERS
177 --
178 -- NOTES
179 --
180 ---------------------------------------------------------------------
181 PROCEDURE Get_Order_Price
182 (
183 p_api_version IN NUMBER
184 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
185 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
186 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
187
188 ,x_return_status OUT NOCOPY VARCHAR2
189 ,x_msg_data OUT NOCOPY VARCHAR2
190 ,x_msg_count OUT NOCOPY NUMBER
191 ,p_control_rec IN QP_PREQ_GRP.CONTROL_RECORD_TYPE
192 ,xp_line_tbl IN OUT NOCOPY LINE_REC_TBL_TYPE
193 ,x_ldets_tbl OUT NOCOPY LDETS_TBL_TYPE
194 ,x_related_lines_tbl OUT NOCOPY RLTD_LINE_TBL_TYPE
195 )IS
196 l_api_name CONSTANT VARCHAR2(30) := 'Get_Order_Price';
197 l_api_version CONSTANT NUMBER := 1.0;
198 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
199 --
200 l_return_status varchar2(30);
201 l_msg_data varchar2(2000);
202 l_msg_count number;
203
204 l_return_status_code varchar2(240);
205 l_price_return_msg varchar2(240);
206
207 I Number;
208 l_header_build boolean :=False;
209 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE := p_control_rec;
210
211 cursor cl_lines_tmp is
212 select *
213 from qp_preq_lines_tmp
214 order by line_index;
215
216 cursor cl_ldets_tmp is
217 select *
218 from qp_ldets_v
219 where pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
220
221 CURSOR cl_rltd_tmp IS
222 SELECT *
223 FROM QP_PREQ_RLTD_LINES_TMP
224 WHERE PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW
225 ORDER BY SETUP_VALUE_FROM;
226
227 cursor cl_line_attrs_tmp is
228 select *
229 from qp_preq_line_attrs_tmp_t;
230
231 G_LINE_INDEX_tbl QP_PREQ_GRP.pls_integer_type;
232 G_LINE_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
233 G_PRICING_EFFECTIVE_DATE_TBL QP_PREQ_GRP.DATE_TYPE ;
234 G_ACTIVE_DATE_FIRST_TBL QP_PREQ_GRP.DATE_TYPE ;
235 G_ACTIVE_DATE_FIRST_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
236 G_ACTIVE_DATE_SECOND_TBL QP_PREQ_GRP.DATE_TYPE ;
237 G_ACTIVE_DATE_SECOND_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE ;
238 G_LINE_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE ;
239 G_LINE_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
240 G_REQUEST_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
241 G_PRICED_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
242 G_PRICED_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
243 G_CURRENCY_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
244 G_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
245 G_PERCENT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
246 G_UOM_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
247 G_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
248 G_UPD_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
249 G_PROCESSED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
250 G_PRICE_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
251
252 --mkothari 13-dec-2006
253 G_LIST_PRICE_OVERRIDE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
254
255 G_LINE_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
256 G_PROCESSING_ORDER_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
257 G_PRICING_STATUS_CODE_tbl QP_PREQ_GRP.VARCHAR_TYPE;
258 G_PRICING_STATUS_TEXT_tbl QP_PREQ_GRP.VARCHAR_TYPE;
259 G_ROUNDING_FLAG_TBL QP_PREQ_GRP.FLAG_TYPE;
260 G_ROUNDING_FACTOR_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
261 G_QUALIFIERS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
262 G_PRICING_ATTRS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
263 G_PRICE_LIST_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
264 G_PL_VALIDATED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
265 G_PRICE_REQUEST_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
266 G_USAGE_PRICING_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
267 /*
268 G_LINE_CATEGORY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
269 G_CONTRACT_START_DATE_DEF_TBL QP_PREQ_GRP.DATE_TYPE;
270 G_CONTRACT_END_DATE_DEF_TBL QP_PREQ_GRP.DATE_TYPE;
271 G_LINE_UNIT_PRICE_DEF_TBL QP_PREQ_GRP.NUMBER_TYPE;
272 */
273 BEGIN
274 -- Standard begin of API savepoint
275 SAVEPOINT Get_Order_Price_pvt;
276 -- Standard call to check for call compatibility.
277 IF NOT FND_API.Compatible_API_Call (
278 l_api_version,
279 p_api_version,
280 l_api_name,
281 G_PKG_NAME)
282 THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285
286 -- Debug Message
287 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
288 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
289 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
290 FND_MSG_PUB.Add;
291 END IF;
292
293 --Initialize message list if p_init_msg_list is TRUE.
294 IF FND_API.To_Boolean (p_init_msg_list) THEN
295 FND_MSG_PUB.initialize;
296 END IF;
297
298 -- Initialize API return status to sucess
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300
301 IF OZF_DEBUG_HIGH_ON THEN
302 ozf_utility_PVT.debug_message('in get_order_price' );
303 END IF;
304
305 -- Default control record if necessary
306 IF l_control_rec.pricing_event is null THEN
307 l_control_rec.pricing_event := 'BATCH';
308 END IF;
309
310 IF l_control_rec.calculate_flag is null THEN
311 l_control_rec.calculate_flag := 'Y';
312 END IF;
313 IF l_control_rec.simulation_flag is null THEN
314 l_control_rec.simulation_flag := 'Y';
315 END IF;
316 /*
317 IF l_control_rec.source_order_amount_flag is NULL THEN
318 l_control_rec.source_order_amount_flag := 'Y';
319 END IF;
320
321 IF l_control_rec.GSA_CHECK_FLAG is null THEN
322 l_control_rec.GSA_CHECK_FLAG := 'N';
323 END IF;
324
325 IF l_control_rec.GSA_DUP_CHECK_FLAG is null THEN
326 l_control_rec.GSA_DUP_CHECK_FLAG := 'N';
327 END IF;
328 */
329 -- always read information from QP temp tables
330 l_control_rec.TEMP_TABLE_INSERT_FLAG := 'N';
331
332 IF l_control_rec.request_type_code is null THEN
333 l_control_rec.request_type_code := 'ONT';
334 END IF;
335
336 -- Bug 4665626 (+)
337 l_control_rec.rounding_flag := 'Q';
338 -- Bug 4665626 (-)
339
340 -- Construct the order
341 For I in xp_line_tbl.FIRST..xp_line_tbl.count LOOP
342 ozf_utility_pvt.write_conc_log('np I = '|| I );
343 ozf_utility_pvt.write_conc_log('np xp_line_tbl(I).line_type_code = '|| xp_line_tbl(I).line_type_code );
344 IF xp_line_tbl(I).line_type_code = G_HDR_TYPE OR
345 xp_line_tbl(I).line_type_code = G_LINE_TYPE THEN
346
347 -- Assign request infromation to the insert structure.
348 ozf_utility_pvt.write_conc_log('np xp_line_tbl(I).line_index = '|| xp_line_tbl(I).line_index );
349 IF xp_line_tbl(I).line_index is NULL THEN
350 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
351 FND_MESSAGE.set_name('OZF', 'OZF_ORD_LN_INDX_MISS');
352 FND_MSG_PUB.add;
353 END IF;
354 RAISE FND_API.G_EXC_ERROR;
355 ELSE
356 G_LINE_INDEX_TBL(I):=xp_line_tbl(I).line_index;
357 END IF;
358
359 IF xp_line_tbl(I).line_type_code is NULL THEN
360 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
361 FND_MESSAGE.set_name('OZF', 'OZF_ORD_LN_TYPCODE_MISS');
362 FND_MSG_PUB.add;
363 END IF;
364 RAISE FND_API.G_EXC_ERROR;
365 ELSE
366 G_LINE_TYPE_CODE_TBL(I):= xp_line_tbl(I).line_type_code;
367 END IF;
368
369 IF xp_line_tbl(I).PRICING_EFFECTIVE_DATE is NULL THEN
370 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
371 FND_MESSAGE.set_name('OZF', 'OZF_ORD_EFF_DATE_MISS');
372 FND_MSG_PUB.add;
373 END IF;
374 RAISE FND_API.G_EXC_ERROR;
375 ELSE
376 G_PRICING_EFFECTIVE_DATE_TBL(I):=xp_line_tbl(I).PRICING_EFFECTIVE_DATE;
377 END IF;
378
379 IF xp_line_tbl(I).ACTIVE_DATE_FIRST is null THEN
380 G_ACTIVE_DATE_FIRST_TBL(I):= sysdate;
381 ELSE
382 G_ACTIVE_DATE_FIRST_TBL(I):= xp_line_tbl(I).ACTIVE_DATE_FIRST;
383 END IF;
384
385 IF xp_line_tbl(I).ACTIVE_DATE_FIRST_TYPE is null THEN
386 G_ACTIVE_DATE_FIRST_TYPE_TBL(I):='NO TYPE';
387 ELSE
388 G_ACTIVE_DATE_FIRST_TYPE_TBL(I):= xp_line_tbl(I).ACTIVE_DATE_FIRST_TYPE;
389 END IF;
390
391 IF xp_line_tbl(I).ACTIVE_DATE_SECOND is NULL THEN
392 G_ACTIVE_DATE_SECOND_TBL(I):= sysdate;
393 ELSE
394 G_ACTIVE_DATE_SECOND_TBL(I):= xp_line_tbl(I).ACTIVE_DATE_SECOND;
395 END IF;
396
397 IF xp_line_tbl(I).ACTIVE_DATE_SECOND_TYPE is NULL THEN
398 G_ACTIVE_DATE_SECOND_TYPE_TBL(I) := 'NO TYPE';
399 ELSE
400 G_ACTIVE_DATE_SECOND_TYPE_TBL(I) := xp_line_tbl(I).ACTIVE_DATE_SECOND_TYPE;
401 END IF;
402
403 G_LINE_QUANTITY_TBL(I):= xp_line_tbl(I).LINE_QUANTITY;
404 G_LINE_UOM_CODE_TBL(I):= xp_line_tbl(I).LINE_UOM_CODE;
405
406 IF xp_line_tbl(I).REQUEST_TYPE_CODE IS NULL THEN
407 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
408 FND_MESSAGE.set_name('OZF', 'OZF_ORD_REQ_TYPCD_MISS');
409 FND_MSG_PUB.add;
410 END IF;
411 RAISE FND_API.G_EXC_ERROR;
412 ELSE
413 G_REQUEST_TYPE_CODE_TBL(I):= xp_line_tbl(I).REQUEST_TYPE_CODE;
414 END IF;
415
416 G_PRICED_QUANTITY_TBL(I):= xp_line_tbl(I).PRICED_QUANTITY;
417 G_PRICED_UOM_CODE_TBL(I):= xp_line_tbl(I).PRICED_UOM_CODE;
418 G_CURRENCY_CODE_TBL(I):= xp_line_tbl(I).CURRENCY_CODE;
419 G_UNIT_PRICE_TBL(I):= xp_line_tbl(I).UNIT_PRICE;
420 G_PERCENT_PRICE_TBL(I):= xp_line_tbl(I).PERCENT_PRICE;
421 G_UOM_QUANTITY_TBL(I):= xp_line_tbl(I).UOM_QUANTITY;
422 G_ADJUSTED_UNIT_PRICE_TBL(I):= xp_line_tbl(I).ADJUSTED_UNIT_PRICE;
423 G_UPD_ADJUSTED_UNIT_PRICE_TBL(I):= xp_line_tbl(I).UPD_ADJUSTED_UNIT_PRICE;
424 G_PROCESSED_FLAG_TBL(I):= xp_line_tbl(I).PROCESSED_FLAG;
425
426 IF xp_line_tbl(I).PRICE_FLAG is null THEN
427 G_PRICE_FLAG_TBL(I):= 'Y'; -- Apply all price and modifier to the request line.
428 ELSE
429 G_PRICE_FLAG_TBL(I):= xp_line_tbl(I).PRICE_FLAG;
430 END IF;
431
432 --mkothari 13-dec-2006
433 G_LIST_PRICE_OVERRIDE_TBL(I):= xp_line_tbl(I).LIST_PRICE_OVERRIDE_FLAG;
434
435 G_LINE_ID_TBL(I):= xp_line_tbl(I).line_Id;
436 G_PROCESSING_ORDER_TBL(I):= xp_line_tbl(I).PROCESSING_ORDER;
437
438 -- Always this value
439 G_PRICING_STATUS_CODE_tbl(I):= QP_PREQ_GRP.G_STATUS_UNCHANGED;
440
441 G_PRICING_STATUS_TEXT_tbl(I):= xp_line_tbl(I).PRICING_STATUS_TEXT;
442 G_ROUNDING_FLAG_TBL(I):= xp_line_tbl(I).ROUNDING_FLAG;
443 G_ROUNDING_FACTOR_TBL(I):= xp_line_tbl(I).ROUNDING_FACTOR;
444 G_QUALIFIERS_EXIST_FLAG_TBL(I):= xp_line_tbl(I).QUALIFIERS_EXIST_FLAG;
445 G_PRICING_ATTRS_EXIST_FLAG_TBL(I):= xp_line_tbl(I).PRICING_ATTRS_EXIST_FLAG;
446 G_PRICE_LIST_ID_TBL(I):= xp_line_tbl(I).PRICE_LIST_ID;
447 G_PL_VALIDATED_FLAG_TBL(I):= xp_line_tbl(I).PL_VALIDATED_FLAG;
448 G_PRICE_REQUEST_CODE_TBL(I):= xp_line_tbl(I).PRICE_REQUEST_CODE;
449 G_USAGE_PRICING_TYPE_tbl(I):= xp_line_tbl(I).USAGE_PRICING_TYPE;
450
451 /*
452 IF xp_line_tbl(I).LINE_CATEGORY = FND_API.G_MISS_CHAR THEN
453 G_LINE_CATEGORY_tbl(I):= null;
454 END IF;
455
456 -- We don't have to fill these columns
457 G_CONTRACT_START_DATE_DEF_TBL(I):= null;
458 G_CONTRACT_END_DATE_DEF_TBL(I) := null;
459 G_LINE_UNIT_PRICE_DEF_TBL(I) := null;
460 */
461
462 IF xp_line_tbl(I).line_type_code = G_HDR_TYPE THEN
463 IF OZF_DEBUG_LOW_ON THEN
464 ozf_utility_PVT.debug_message('before build header' );
465 END IF;
466 oe_order_pub.g_hdr := g_header_rec;
467 build_order_header( p_hdr => xp_line_tbl(I),
468 x_return_status => l_return_status);
469 IF l_return_status = FND_API.g_ret_sts_error THEN
470 RAISE FND_API.g_exc_error;
471 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
472 RAISE FND_API.g_exc_unexpected_error;
473 END IF;
474
475 QP_Attr_Mapping_PUB.Build_Contexts(
476 p_request_type_code => 'ONT',
477 p_line_index => xp_line_tbl(I).line_index,
478 p_pricing_type_code =>'H'
479 );
480 IF OZF_DEBUG_LOW_ON THEN
481 ozf_utility_PVT.debug_message('after build header' );
482 END IF;
483 ELSE
484 -- It has to be line
485 IF OZF_DEBUG_LOW_ON THEN
486 ozf_utility_PVT.debug_message('before build line' );
487 END IF;
488 oe_order_pub.g_hdr := g_header_rec; -- Fix for Bug 12760472
489 oe_order_pub.g_line := g_line_rec_tbl(I);
490 build_order_line( p_line => xp_line_tbl(I),
491 x_return_status => l_return_status);
492 IF l_return_status = FND_API.g_ret_sts_error THEN
493 RAISE FND_API.g_exc_error;
494 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
495 RAISE FND_API.g_exc_unexpected_error;
496 END IF;
497 QP_Attr_Mapping_PUB.Build_Contexts(
498 p_request_type_code => 'ONT',
499 p_line_index => xp_line_tbl(I).line_index,
500 p_pricing_type_code =>'L'
501 );
502 IF OZF_DEBUG_LOW_ON THEN
503 ozf_utility_PVT.debug_message('after build line' );
504 END IF;
505 END IF;
506 ELSE
507 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
508 FND_MESSAGE.set_name('OZF', 'OZF_ORD_LINE_TYPE_WRG');
509 FND_MSG_PUB.add;
510 END IF;
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513 ozf_utility_pvt.write_conc_log('kd: xp_line_tbl(I).chargeback_int_id- ' || xp_line_tbl(I).chargeback_int_id);
514 End LOOP;
515
516 IF OZF_DEBUG_LOW_ON THEN
517 ozf_utility_PVT.debug_message('Before insert_lines2' );
518 END IF;
519
520 QP_PREQ_GRP.INSERT_LINES2
521 (p_LINE_INDEX => G_LINE_INDEX_TBL,
522 p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
523 p_PRICING_EFFECTIVE_DATE =>G_PRICING_EFFECTIVE_DATE_TBL,
524 p_ACTIVE_DATE_FIRST =>G_ACTIVE_DATE_FIRST_TBL,
525 p_ACTIVE_DATE_FIRST_TYPE =>G_ACTIVE_DATE_FIRST_TYPE_TBL,
526 p_ACTIVE_DATE_SECOND =>G_ACTIVE_DATE_SECOND_TBL,
527 p_ACTIVE_DATE_SECOND_TYPE =>G_ACTIVE_DATE_SECOND_TYPE_TBL,
528 p_LINE_QUANTITY => G_LINE_QUANTITY_TBL,
529 p_LINE_UOM_CODE => G_LINE_UOM_CODE_TBL,
530 p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
531 p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
532 p_PRICED_UOM_CODE => G_PRICED_UOM_CODE_TBL,
533 p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
534 p_UNIT_PRICE => G_UNIT_PRICE_TBL,
535 p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
536 p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
537 p_ADJUSTED_UNIT_PRICE =>G_ADJUSTED_UNIT_PRICE_TBL,
538 p_UPD_ADJUSTED_UNIT_PRICE =>G_UPD_ADJUSTED_UNIT_PRICE_TBL,
539 p_PROCESSED_FLAG =>G_PROCESSED_FLAG_TBL,
540 p_PRICE_FLAG =>G_PRICE_FLAG_TBL,
541 p_LINE_ID =>G_LINE_ID_TBL,
542 p_PROCESSING_ORDER =>G_PROCESSING_ORDER_TBL,
543 p_PRICING_STATUS_CODE =>G_PRICING_STATUS_CODE_tbl,
544 p_PRICING_STATUS_TEXT =>G_PRICING_STATUS_TEXT_tbl,
545 p_ROUNDING_FLAG =>G_ROUNDING_FLAG_TBL,
546 p_ROUNDING_FACTOR =>G_ROUNDING_FACTOR_TBL,
547 p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
548 p_PRICING_ATTRS_EXIST_FLAG =>G_PRICING_ATTRS_EXIST_FLAG_TBL,
549 p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
550 p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
551 p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
552 p_USAGE_PRICING_TYPE =>G_USAGE_PRICING_TYPE_tbl,
553 --mkothari 13-dec-2006
554 p_LIST_PRICE_OVERRIDE_FLAG =>G_LIST_PRICE_OVERRIDE_TBL,
555 -- p_line_category =>G_LINE_CATEGORY_tbl,
556 x_status_code =>l_return_status_code,
557 x_status_text =>l_price_return_msg);
558
559 IF l_return_status_code <> FND_API.G_RET_STS_SUCCESS THEN
560 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
561 FND_MESSAGE.set_name('OZF', 'OZF_ORDER_INSERT_ERR');
562 FND_MSG_PUB.add;
563 IF OZF_DEBUG_LOW_ON THEN
564 ozf_utility_PVT.debug_message(l_price_return_msg);
565 END IF;
566 END IF;
567 RAISE FND_API.G_EXC_ERROR;
568 END IF;
569 IF OZF_DEBUG_LOW_ON THEN
570 ozf_utility_PVT.debug_message('After insert lines2' );
571 END IF;
572
573 ozf_utility_pvt.write_conc_log('kd: ordered_date- ' || OE_ORDER_PUB.G_HDR.ordered_date);
574
575 -- Call qp price engine
576 QP_PREQ_PUB.PRICE_REQUEST
577 (p_control_rec => l_control_rec,
578 x_return_status => l_return_status,
579 x_return_status_text => l_msg_data);
580
581 IF l_return_status = FND_API.g_ret_sts_error THEN
582 IF OZF_DEBUG_LOW_ON THEN
583 ozf_utility_PVT.debug_message('in error'||l_msg_data);
584 END IF;
585 RAISE FND_API.g_exc_error;
586 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
587 IF OZF_DEBUG_LOW_ON THEN
588 ozf_utility_PVT.debug_message('in un'||l_msg_data);
589 END IF;
590 RAISE FND_API.g_exc_unexpected_error;
591 END IF;
592 IF OZF_DEBUG_LOW_ON THEN
593 ozf_utility_PVT.debug_message('After price_request' );
594 END IF;
595 -- Populate the result from the temp tables.
596 -- We will be looking for adjusted_price and price adjustments
597 -- with automatic_flag := 'Y' and accrual_flag := 'Y' */
598
599 -- Line table
600
601 I:= 1;
602 for line in cl_lines_tmp
603 LOOP
604 xp_line_tbl(I).LINE_INDEX := line.line_index;
605 xp_line_tbl(I).LINE_ID := line.line_id;
606 xp_line_tbl(I).LINE_TYPE_CODE := line.line_type_code;
607 xp_line_tbl(I).PRICING_EFFECTIVE_DATE := line.PRICING_EFFECTIVE_DATE;
608 xp_line_tbl(I).ACTIVE_DATE_FIRST := line.START_DATE_ACTIVE_FIRST;
609 xp_line_tbl(I).ACTIVE_DATE_FIRST_TYPE := line.ACTIVE_DATE_FIRST_TYPE;
610 xp_line_tbl(I).ACTIVE_DATE_SECOND := line.START_DATE_ACTIVE_SECOND;
611 xp_line_tbl(I).ACTIVE_DATE_SECOND_TYPE := line.ACTIVE_DATE_SECOND_TYPE;
612 xp_line_tbl(I).LINE_QUANTITY := line.line_quantity;
613 xp_line_tbl(I).LINE_UOM_CODE := line.line_uom_code;
614 xp_line_tbl(I).REQUEST_TYPE_CODE := line.request_type_code;
615 xp_line_tbl(I).PRICED_QUANTITY := line.priced_quantity;
616 xp_line_tbl(I).PRICED_UOM_CODE := line.priced_uom_code;
617 xp_line_tbl(I).CURRENCY_CODE := line.currency_code;
618 xp_line_tbl(I).UNIT_PRICE := line.line_unit_price;
619 xp_line_tbl(I).PERCENT_PRICE := line.percent_price;
620 xp_line_tbl(I).UOM_QUANTITY := line.uom_quantity;
621 xp_line_tbl(I).ADJUSTED_UNIT_PRICE := line.ADJUSTED_UNIT_PRICE;
622 xp_line_tbl(I).UPD_ADJUSTED_UNIT_PRICE := line.UPDATED_ADJUSTED_UNIT_PRICE;
623 xp_line_tbl(I).PROCESSED_FLAG := line.PROCESSED_FLAG;
624 xp_line_tbl(I).PRICE_FLAG := line.price_flag;
625 --mkothari 13-dec-2006
626 xp_line_tbl(I).LIST_PRICE_OVERRIDE_FLAG := line.LIST_PRICE_OVERRIDE_FLAG;
627 xp_line_tbl(I).PROCESSING_ORDER := line.PROCESSING_ORDER;
628 xp_line_tbl(I).PRICING_STATUS_CODE := line.PRICING_STATUS_CODE;
629 xp_line_tbl(I).PRICING_STATUS_TEXT := line.PRICING_STATUS_TEXT;
630 xp_line_tbl(I).ROUNDING_FLAG := line.ROUNDING_FLAG;
631 xp_line_tbl(I).ROUNDING_FACTOR := line.ROUNDING_FACTOR;
632 xp_line_tbl(I).QUALIFIERS_EXIST_FLAG := line.QUALIFIERS_EXIST_FLAG;
633 xp_line_tbl(I).PRICING_ATTRS_EXIST_FLAG:= line.PRICING_ATTRS_EXIST_FLAG;
634 xp_line_tbl(I).PRICE_LIST_ID := line.price_list_header_id;
635 xp_line_tbl(I).PL_VALIDATED_FLAG := line.validated_flag;
636 xp_line_tbl(I).PRICE_REQUEST_CODE := line.price_request_code;
637 xp_line_tbl(I).USAGE_PRICING_TYPE := line.usage_pricing_type;
638 xp_line_tbl(I).LINE_CATEGORY := line.line_category;
639 I:= I+1;
640 END LOOP;
641
642 I:=1;
643 FOR ldets in cl_ldets_tmp
644 LOOP
645 x_ldets_tbl(I) := ldets;
646 I:=I+1;
647 END LOOP;
648
649 I:=1;
650 FOR rltd in cl_rltd_tmp
651 LOOP
652 x_related_lines_tbl(I) := rltd;
653 I:= I+1;
654 END LOOP;
655
656 -- Debug Message
657 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
658 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
659 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
660 FND_MSG_PUB.Add;
661 END IF;
662 --Standard call to get message count and if count=1, get the message
663 FND_MSG_PUB.Count_And_Get (
664 p_encoded => FND_API.G_FALSE,
665 p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668 IF OZF_DEBUG_HIGH_ON THEN
669 ozf_utility_PVT.debug_message('end of order price');
670 END IF;
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
673 ROLLBACK TO Get_Order_Price_pvt ;
674 x_return_status := FND_API.G_RET_STS_ERROR;
675 -- Standard call to get message count and if count=1, get the message
676 FND_MSG_PUB.Count_And_Get (
677 p_encoded => FND_API.G_FALSE,
678 p_count => x_msg_count,
679 p_data => x_msg_data
680 );
681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
682 ROLLBACK TO Get_Order_Price_pvt;
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 -- Standard call to get message count and if count=1, get the message
685 FND_MSG_PUB.Count_And_Get (
686 p_encoded => FND_API.G_FALSE,
687 p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690 WHEN OTHERS THEN
691 ROLLBACK TO Get_Order_Price_pvt;
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 -- Standard call to get message count and if count=1, get the message
694 FND_MSG_PUB.Count_And_Get (
695 p_encoded => FND_API.G_FALSE,
696 p_count => x_msg_count,
697 p_data => x_msg_data
698 );
699 END Get_Order_Price;
700
701 ---------------------------------------------------------------------
702 -- PROCEDURE
703 -- Purge_Pricing_Temp_table
704 --
705 -- PURPOSE
706 -- Purge Pricing Temporary tables
707 --
708 -- PARAMETERS
709 --
710 -- NOTES
711 --
712 ---------------------------------------------------------------------
713 PROCEDURE Purge_Pricing_Temp_table (
714 p_api_version IN NUMBER
715 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
716 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
717 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
718 ,x_return_status OUT NOCOPY VARCHAR2
719 ,x_msg_data OUT NOCOPY VARCHAR2
720 ,x_msg_count OUT NOCOPY NUMBER
721 ) is
722 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Pricing_Temp_table';
723 l_api_version CONSTANT NUMBER := 1.0;
724 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
725 Begin
726
727 SAVEPOINT PURGE_PRICING_TEMP;
728
729 -- Standard call to check for call compatibility.
730 IF NOT FND_API.Compatible_API_Call (
731 l_api_version,
732 p_api_version,
733 l_api_name,
734 G_PKG_NAME)
735 THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738 -- Debug Message
739 IF OZF_DEBUG_LOW_ON THEN
740 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
741 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
742 FND_MSG_PUB.Add;
743 END IF;
744
745 --Initialize message list if p_init_msg_list is TRUE.
746 IF FND_API.To_Boolean (p_init_msg_list) THEN
747 FND_MSG_PUB.initialize;
748 END IF;
749
750 -- Initialize API return status to sucess
751 x_return_status := FND_API.G_RET_STS_SUCCESS;
752
753 -- Debug Message
754 IF OZF_DEBUG_LOW_ON THEN
755 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
756 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
757 FND_MSG_PUB.Add;
758 END IF;
759
760 EXCEPTION
761 WHEN FND_API.G_EXC_ERROR THEN
762 ROLLBACK TO PURGE_PRICING_TEMP;
763 x_return_status := FND_API.G_RET_STS_ERROR;
764 -- Standard call to get message count and if count=1, get the message
765 FND_MSG_PUB.Count_And_Get (
766 p_encoded => FND_API.G_FALSE,
767 p_count => x_msg_count,
768 p_data => x_msg_data
769 );
770 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
771 ROLLBACK TO PURGE_PRICING_TEMP;
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 -- Standard call to get message count and if count=1, get the message
774 FND_MSG_PUB.Count_And_Get (
775 p_encoded => FND_API.G_FALSE,
776 p_count => x_msg_count,
777 p_data => x_msg_data
778 );
779 WHEN OTHERS THEN
780 ROLLBACK TO PURGE_PRICING_TEMP;
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 -- Standard call to get message count and if count=1, get the message
783 FND_MSG_PUB.Count_And_Get (
784 p_encoded => FND_API.G_FALSE,
785 p_count => x_msg_count,
786 p_data => x_msg_data
787 );
788 END Purge_Pricing_Temp_table;
789 END OZF_ORDER_PRICE_PVT;