[Home] [Help]
PACKAGE BODY: APPS.OZF_ORDER_PRICE_PVT
Source
1 PACKAGE BODY OZF_ORDER_PRICE_PVT AS
2 /* $Header: ozfvorpb.pls 120.4 2006/12/15 03:23:23 mkothari noship $ */
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 IF xp_line_tbl(I).line_type_code = G_HDR_TYPE OR
343 xp_line_tbl(I).line_type_code = G_LINE_TYPE THEN
344
345 -- Assign request infromation to the insert structure.
346 IF xp_line_tbl(I).line_index is NULL THEN
347 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
348 FND_MESSAGE.set_name('OZF', 'OZF_ORD_LN_INDX_MISS');
349 FND_MSG_PUB.add;
350 END IF;
351 RAISE FND_API.G_EXC_ERROR;
352 ELSE
353 G_LINE_INDEX_TBL(I):=xp_line_tbl(I).line_index;
354 END IF;
355
356 IF xp_line_tbl(I).line_type_code is NULL THEN
357 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
358 FND_MESSAGE.set_name('OZF', 'OZF_ORD_LN_TYPCODE_MISS');
359 FND_MSG_PUB.add;
360 END IF;
361 RAISE FND_API.G_EXC_ERROR;
362 ELSE
363 G_LINE_TYPE_CODE_TBL(I):= xp_line_tbl(I).line_type_code;
364 END IF;
365
366 IF xp_line_tbl(I).PRICING_EFFECTIVE_DATE is NULL THEN
367 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
368 FND_MESSAGE.set_name('OZF', 'OZF_ORD_EFF_DATE_MISS');
369 FND_MSG_PUB.add;
370 END IF;
371 RAISE FND_API.G_EXC_ERROR;
372 ELSE
373 G_PRICING_EFFECTIVE_DATE_TBL(I):=xp_line_tbl(I).PRICING_EFFECTIVE_DATE;
374 END IF;
375
376 IF xp_line_tbl(I).ACTIVE_DATE_FIRST is null THEN
377 G_ACTIVE_DATE_FIRST_TBL(I):= sysdate;
378 ELSE
379 G_ACTIVE_DATE_FIRST_TBL(I):= xp_line_tbl(I).ACTIVE_DATE_FIRST;
380 END IF;
381
382 IF xp_line_tbl(I).ACTIVE_DATE_FIRST_TYPE is null THEN
383 G_ACTIVE_DATE_FIRST_TYPE_TBL(I):='NO TYPE';
384 ELSE
385 G_ACTIVE_DATE_FIRST_TYPE_TBL(I):= xp_line_tbl(I).ACTIVE_DATE_FIRST_TYPE;
386 END IF;
387
388 IF xp_line_tbl(I).ACTIVE_DATE_SECOND is NULL THEN
389 G_ACTIVE_DATE_SECOND_TBL(I):= sysdate;
390 ELSE
391 G_ACTIVE_DATE_SECOND_TBL(I):= xp_line_tbl(I).ACTIVE_DATE_SECOND;
392 END IF;
393
394 IF xp_line_tbl(I).ACTIVE_DATE_SECOND_TYPE is NULL THEN
395 G_ACTIVE_DATE_SECOND_TYPE_TBL(I) := 'NO TYPE';
396 ELSE
397 G_ACTIVE_DATE_SECOND_TYPE_TBL(I) := xp_line_tbl(I).ACTIVE_DATE_SECOND_TYPE;
398 END IF;
399
400 G_LINE_QUANTITY_TBL(I):= xp_line_tbl(I).LINE_QUANTITY;
401 G_LINE_UOM_CODE_TBL(I):= xp_line_tbl(I).LINE_UOM_CODE;
402
403 IF xp_line_tbl(I).REQUEST_TYPE_CODE IS NULL THEN
404 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
405 FND_MESSAGE.set_name('OZF', 'OZF_ORD_REQ_TYPCD_MISS');
406 FND_MSG_PUB.add;
407 END IF;
408 RAISE FND_API.G_EXC_ERROR;
409 ELSE
410 G_REQUEST_TYPE_CODE_TBL(I):= xp_line_tbl(I).REQUEST_TYPE_CODE;
411 END IF;
412
413 G_PRICED_QUANTITY_TBL(I):= xp_line_tbl(I).PRICED_QUANTITY;
414 G_PRICED_UOM_CODE_TBL(I):= xp_line_tbl(I).PRICED_UOM_CODE;
415 G_CURRENCY_CODE_TBL(I):= xp_line_tbl(I).CURRENCY_CODE;
416 G_UNIT_PRICE_TBL(I):= xp_line_tbl(I).UNIT_PRICE;
417 G_PERCENT_PRICE_TBL(I):= xp_line_tbl(I).PERCENT_PRICE;
418 G_UOM_QUANTITY_TBL(I):= xp_line_tbl(I).UOM_QUANTITY;
419 G_ADJUSTED_UNIT_PRICE_TBL(I):= xp_line_tbl(I).ADJUSTED_UNIT_PRICE;
420 G_UPD_ADJUSTED_UNIT_PRICE_TBL(I):= xp_line_tbl(I).UPD_ADJUSTED_UNIT_PRICE;
421 G_PROCESSED_FLAG_TBL(I):= xp_line_tbl(I).PROCESSED_FLAG;
422
423 IF xp_line_tbl(I).PRICE_FLAG is null THEN
424 G_PRICE_FLAG_TBL(I):= 'Y'; -- Apply all price and modifier to the request line.
425 ELSE
426 G_PRICE_FLAG_TBL(I):= xp_line_tbl(I).PRICE_FLAG;
427 END IF;
428
429 --mkothari 13-dec-2006
430 G_LIST_PRICE_OVERRIDE_TBL(I):= xp_line_tbl(I).LIST_PRICE_OVERRIDE_FLAG;
431
432 G_LINE_ID_TBL(I):= xp_line_tbl(I).line_Id;
433 G_PROCESSING_ORDER_TBL(I):= xp_line_tbl(I).PROCESSING_ORDER;
434
435 -- Always this value
436 G_PRICING_STATUS_CODE_tbl(I):= QP_PREQ_GRP.G_STATUS_UNCHANGED;
437
438 G_PRICING_STATUS_TEXT_tbl(I):= xp_line_tbl(I).PRICING_STATUS_TEXT;
439 G_ROUNDING_FLAG_TBL(I):= xp_line_tbl(I).ROUNDING_FLAG;
440 G_ROUNDING_FACTOR_TBL(I):= xp_line_tbl(I).ROUNDING_FACTOR;
441 G_QUALIFIERS_EXIST_FLAG_TBL(I):= xp_line_tbl(I).QUALIFIERS_EXIST_FLAG;
442 G_PRICING_ATTRS_EXIST_FLAG_TBL(I):= xp_line_tbl(I).PRICING_ATTRS_EXIST_FLAG;
443 G_PRICE_LIST_ID_TBL(I):= xp_line_tbl(I).PRICE_LIST_ID;
444 G_PL_VALIDATED_FLAG_TBL(I):= xp_line_tbl(I).PL_VALIDATED_FLAG;
445 G_PRICE_REQUEST_CODE_TBL(I):= xp_line_tbl(I).PRICE_REQUEST_CODE;
446 G_USAGE_PRICING_TYPE_tbl(I):= xp_line_tbl(I).USAGE_PRICING_TYPE;
447
448 /*
449 IF xp_line_tbl(I).LINE_CATEGORY = FND_API.G_MISS_CHAR THEN
450 G_LINE_CATEGORY_tbl(I):= null;
451 END IF;
452
453 -- We don't have to fill these columns
454 G_CONTRACT_START_DATE_DEF_TBL(I):= null;
455 G_CONTRACT_END_DATE_DEF_TBL(I) := null;
456 G_LINE_UNIT_PRICE_DEF_TBL(I) := null;
457 */
458
459 IF xp_line_tbl(I).line_type_code = G_HDR_TYPE THEN
460 IF OZF_DEBUG_LOW_ON THEN
461 ozf_utility_PVT.debug_message('before build header' );
462 END IF;
463 oe_order_pub.g_hdr := g_header_rec;
464 build_order_header( p_hdr => xp_line_tbl(I),
465 x_return_status => l_return_status);
466 IF l_return_status = FND_API.g_ret_sts_error THEN
467 RAISE FND_API.g_exc_error;
468 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
469 RAISE FND_API.g_exc_unexpected_error;
470 END IF;
471
472 QP_Attr_Mapping_PUB.Build_Contexts(
473 p_request_type_code => 'ONT',
474 p_line_index => xp_line_tbl(I).line_index,
475 p_pricing_type_code =>'H'
476 );
477 IF OZF_DEBUG_LOW_ON THEN
478 ozf_utility_PVT.debug_message('after build header' );
479 END IF;
480 ELSE
481 -- It has to be line
482 IF OZF_DEBUG_LOW_ON THEN
483 ozf_utility_PVT.debug_message('before build line' );
484 END IF;
485 oe_order_pub.g_line := g_line_rec_tbl(I);
486 build_order_line( p_line => xp_line_tbl(I),
487 x_return_status => l_return_status);
488 IF l_return_status = FND_API.g_ret_sts_error THEN
489 RAISE FND_API.g_exc_error;
490 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
491 RAISE FND_API.g_exc_unexpected_error;
492 END IF;
493 QP_Attr_Mapping_PUB.Build_Contexts(
494 p_request_type_code => 'ONT',
495 p_line_index => xp_line_tbl(I).line_index,
496 p_pricing_type_code =>'L'
497 );
498 IF OZF_DEBUG_LOW_ON THEN
499 ozf_utility_PVT.debug_message('after build line' );
500 END IF;
501 END IF;
502 ELSE
503 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
504 FND_MESSAGE.set_name('OZF', 'OZF_ORD_LINE_TYPE_WRG');
505 FND_MSG_PUB.add;
506 END IF;
507 RAISE FND_API.G_EXC_ERROR;
508 END IF;
509 End LOOP;
510
511 IF OZF_DEBUG_LOW_ON THEN
512 ozf_utility_PVT.debug_message('Before insert_lines2' );
513 END IF;
514
515 QP_PREQ_GRP.INSERT_LINES2
516 (p_LINE_INDEX => G_LINE_INDEX_TBL,
517 p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
518 p_PRICING_EFFECTIVE_DATE =>G_PRICING_EFFECTIVE_DATE_TBL,
519 p_ACTIVE_DATE_FIRST =>G_ACTIVE_DATE_FIRST_TBL,
520 p_ACTIVE_DATE_FIRST_TYPE =>G_ACTIVE_DATE_FIRST_TYPE_TBL,
521 p_ACTIVE_DATE_SECOND =>G_ACTIVE_DATE_SECOND_TBL,
522 p_ACTIVE_DATE_SECOND_TYPE =>G_ACTIVE_DATE_SECOND_TYPE_TBL,
523 p_LINE_QUANTITY => G_LINE_QUANTITY_TBL,
524 p_LINE_UOM_CODE => G_LINE_UOM_CODE_TBL,
525 p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
526 p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
527 p_PRICED_UOM_CODE => G_PRICED_UOM_CODE_TBL,
528 p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
529 p_UNIT_PRICE => G_UNIT_PRICE_TBL,
530 p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
531 p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
532 p_ADJUSTED_UNIT_PRICE =>G_ADJUSTED_UNIT_PRICE_TBL,
533 p_UPD_ADJUSTED_UNIT_PRICE =>G_UPD_ADJUSTED_UNIT_PRICE_TBL,
534 p_PROCESSED_FLAG =>G_PROCESSED_FLAG_TBL,
535 p_PRICE_FLAG =>G_PRICE_FLAG_TBL,
536 p_LINE_ID =>G_LINE_ID_TBL,
537 p_PROCESSING_ORDER =>G_PROCESSING_ORDER_TBL,
538 p_PRICING_STATUS_CODE =>G_PRICING_STATUS_CODE_tbl,
539 p_PRICING_STATUS_TEXT =>G_PRICING_STATUS_TEXT_tbl,
540 p_ROUNDING_FLAG =>G_ROUNDING_FLAG_TBL,
541 p_ROUNDING_FACTOR =>G_ROUNDING_FACTOR_TBL,
542 p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
543 p_PRICING_ATTRS_EXIST_FLAG =>G_PRICING_ATTRS_EXIST_FLAG_TBL,
544 p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
545 p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
546 p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
547 p_USAGE_PRICING_TYPE =>G_USAGE_PRICING_TYPE_tbl,
548 --mkothari 13-dec-2006
549 p_LIST_PRICE_OVERRIDE_FLAG =>G_LIST_PRICE_OVERRIDE_TBL,
550 -- p_line_category =>G_LINE_CATEGORY_tbl,
551 x_status_code =>l_return_status_code,
552 x_status_text =>l_price_return_msg);
553
554 IF l_return_status_code <> FND_API.G_RET_STS_SUCCESS THEN
555 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
556 FND_MESSAGE.set_name('OZF', 'OZF_ORDER_INSERT_ERR');
557 FND_MSG_PUB.add;
558 IF OZF_DEBUG_LOW_ON THEN
559 ozf_utility_PVT.debug_message(l_price_return_msg);
560 END IF;
561 END IF;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564 IF OZF_DEBUG_LOW_ON THEN
565 ozf_utility_PVT.debug_message('After insert lines2' );
566 END IF;
567
568
569
570 -- Call qp price engine
571 QP_PREQ_PUB.PRICE_REQUEST
572 (p_control_rec => l_control_rec,
573 x_return_status => l_return_status,
574 x_return_status_text => l_msg_data);
575
576 IF l_return_status = FND_API.g_ret_sts_error THEN
577 IF OZF_DEBUG_LOW_ON THEN
578 ozf_utility_PVT.debug_message('in error'||l_msg_data);
579 END IF;
580 RAISE FND_API.g_exc_error;
581 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
582 IF OZF_DEBUG_LOW_ON THEN
583 ozf_utility_PVT.debug_message('in un'||l_msg_data);
584 END IF;
585 RAISE FND_API.g_exc_unexpected_error;
586 END IF;
587 IF OZF_DEBUG_LOW_ON THEN
588 ozf_utility_PVT.debug_message('After price_request' );
589 END IF;
590 -- Populate the result from the temp tables.
591 -- We will be looking for adjusted_price and price adjustments
592 -- with automatic_flag := 'Y' and accrual_flag := 'Y' */
593
594 -- Line table
595
596 I:= 1;
597 for line in cl_lines_tmp
598 LOOP
599 xp_line_tbl(I).LINE_INDEX := line.line_index;
600 xp_line_tbl(I).LINE_ID := line.line_id;
601 xp_line_tbl(I).LINE_TYPE_CODE := line.line_type_code;
602 xp_line_tbl(I).PRICING_EFFECTIVE_DATE := line.PRICING_EFFECTIVE_DATE;
603 xp_line_tbl(I).ACTIVE_DATE_FIRST := line.START_DATE_ACTIVE_FIRST;
604 xp_line_tbl(I).ACTIVE_DATE_FIRST_TYPE := line.ACTIVE_DATE_FIRST_TYPE;
605 xp_line_tbl(I).ACTIVE_DATE_SECOND := line.START_DATE_ACTIVE_SECOND;
606 xp_line_tbl(I).ACTIVE_DATE_SECOND_TYPE := line.ACTIVE_DATE_SECOND_TYPE;
607 xp_line_tbl(I).LINE_QUANTITY := line.line_quantity;
608 xp_line_tbl(I).LINE_UOM_CODE := line.line_uom_code;
609 xp_line_tbl(I).REQUEST_TYPE_CODE := line.request_type_code;
610 xp_line_tbl(I).PRICED_QUANTITY := line.priced_quantity;
611 xp_line_tbl(I).PRICED_UOM_CODE := line.priced_uom_code;
612 xp_line_tbl(I).CURRENCY_CODE := line.currency_code;
613 xp_line_tbl(I).UNIT_PRICE := line.line_unit_price;
614 xp_line_tbl(I).PERCENT_PRICE := line.percent_price;
615 xp_line_tbl(I).UOM_QUANTITY := line.uom_quantity;
616 xp_line_tbl(I).ADJUSTED_UNIT_PRICE := line.ADJUSTED_UNIT_PRICE;
617 xp_line_tbl(I).UPD_ADJUSTED_UNIT_PRICE := line.UPDATED_ADJUSTED_UNIT_PRICE;
618 xp_line_tbl(I).PROCESSED_FLAG := line.PROCESSED_FLAG;
619 xp_line_tbl(I).PRICE_FLAG := line.price_flag;
620 --mkothari 13-dec-2006
621 xp_line_tbl(I).LIST_PRICE_OVERRIDE_FLAG := line.LIST_PRICE_OVERRIDE_FLAG;
622 xp_line_tbl(I).PROCESSING_ORDER := line.PROCESSING_ORDER;
623 xp_line_tbl(I).PRICING_STATUS_CODE := line.PRICING_STATUS_CODE;
624 xp_line_tbl(I).PRICING_STATUS_TEXT := line.PRICING_STATUS_TEXT;
625 xp_line_tbl(I).ROUNDING_FLAG := line.ROUNDING_FLAG;
626 xp_line_tbl(I).ROUNDING_FACTOR := line.ROUNDING_FACTOR;
627 xp_line_tbl(I).QUALIFIERS_EXIST_FLAG := line.QUALIFIERS_EXIST_FLAG;
628 xp_line_tbl(I).PRICING_ATTRS_EXIST_FLAG:= line.PRICING_ATTRS_EXIST_FLAG;
629 xp_line_tbl(I).PRICE_LIST_ID := line.price_list_header_id;
630 xp_line_tbl(I).PL_VALIDATED_FLAG := line.validated_flag;
631 xp_line_tbl(I).PRICE_REQUEST_CODE := line.price_request_code;
632 xp_line_tbl(I).USAGE_PRICING_TYPE := line.usage_pricing_type;
633 xp_line_tbl(I).LINE_CATEGORY := line.line_category;
634 I:= I+1;
635 END LOOP;
636
637 I:=1;
638 FOR ldets in cl_ldets_tmp
639 LOOP
640 x_ldets_tbl(I) := ldets;
641 I:=I+1;
642 END LOOP;
643
644 I:=1;
645 FOR rltd in cl_rltd_tmp
646 LOOP
647 x_related_lines_tbl(I) := rltd;
648 I:= I+1;
649 END LOOP;
650
651 -- Debug Message
652 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
653 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
654 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
655 FND_MSG_PUB.Add;
656 END IF;
657 --Standard call to get message count and if count=1, get the message
658 FND_MSG_PUB.Count_And_Get (
659 p_encoded => FND_API.G_FALSE,
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 );
663 IF OZF_DEBUG_HIGH_ON THEN
664 ozf_utility_PVT.debug_message('end of order price');
665 END IF;
666 EXCEPTION
667 WHEN FND_API.G_EXC_ERROR THEN
668 ROLLBACK TO Get_Order_Price_pvt ;
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 -- Standard call to get message count and if count=1, get the message
671 FND_MSG_PUB.Count_And_Get (
672 p_encoded => FND_API.G_FALSE,
673 p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
677 ROLLBACK TO Get_Order_Price_pvt;
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 -- Standard call to get message count and if count=1, get the message
680 FND_MSG_PUB.Count_And_Get (
681 p_encoded => FND_API.G_FALSE,
682 p_count => x_msg_count,
683 p_data => x_msg_data
684 );
685 WHEN OTHERS THEN
686 ROLLBACK TO Get_Order_Price_pvt;
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 -- Standard call to get message count and if count=1, get the message
689 FND_MSG_PUB.Count_And_Get (
690 p_encoded => FND_API.G_FALSE,
691 p_count => x_msg_count,
692 p_data => x_msg_data
693 );
694 END Get_Order_Price;
695
696 ---------------------------------------------------------------------
697 -- PROCEDURE
698 -- Purge_Pricing_Temp_table
699 --
700 -- PURPOSE
701 -- Purge Pricing Temporary tables
702 --
703 -- PARAMETERS
704 --
705 -- NOTES
706 --
707 ---------------------------------------------------------------------
708 PROCEDURE Purge_Pricing_Temp_table (
709 p_api_version IN NUMBER
710 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
711 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
712 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
713 ,x_return_status OUT NOCOPY VARCHAR2
714 ,x_msg_data OUT NOCOPY VARCHAR2
715 ,x_msg_count OUT NOCOPY NUMBER
716 ) is
717 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Pricing_Temp_table';
718 l_api_version CONSTANT NUMBER := 1.0;
719 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
720 Begin
721
722 SAVEPOINT PURGE_PRICING_TEMP;
723
724 -- Standard call to check for call compatibility.
725 IF NOT FND_API.Compatible_API_Call (
726 l_api_version,
727 p_api_version,
728 l_api_name,
729 G_PKG_NAME)
730 THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733 -- Debug Message
734 IF OZF_DEBUG_LOW_ON THEN
735 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
736 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
737 FND_MSG_PUB.Add;
738 END IF;
739
740 --Initialize message list if p_init_msg_list is TRUE.
741 IF FND_API.To_Boolean (p_init_msg_list) THEN
742 FND_MSG_PUB.initialize;
743 END IF;
744
745 -- Initialize API return status to sucess
746 x_return_status := FND_API.G_RET_STS_SUCCESS;
747
748 -- Debug Message
749 IF OZF_DEBUG_LOW_ON THEN
750 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
751 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
752 FND_MSG_PUB.Add;
753 END IF;
754
755 EXCEPTION
756 WHEN FND_API.G_EXC_ERROR THEN
757 ROLLBACK TO PURGE_PRICING_TEMP;
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 -- Standard call to get message count and if count=1, get the message
760 FND_MSG_PUB.Count_And_Get (
761 p_encoded => FND_API.G_FALSE,
762 p_count => x_msg_count,
763 p_data => x_msg_data
764 );
765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
766 ROLLBACK TO PURGE_PRICING_TEMP;
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 -- Standard call to get message count and if count=1, get the message
769 FND_MSG_PUB.Count_And_Get (
770 p_encoded => FND_API.G_FALSE,
771 p_count => x_msg_count,
772 p_data => x_msg_data
773 );
774 WHEN OTHERS THEN
775 ROLLBACK TO PURGE_PRICING_TEMP;
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777 -- Standard call to get message count and if count=1, get the message
778 FND_MSG_PUB.Count_And_Get (
779 p_encoded => FND_API.G_FALSE,
780 p_count => x_msg_count,
781 p_data => x_msg_data
782 );
783 END Purge_Pricing_Temp_table;
784 END OZF_ORDER_PRICE_PVT;