[Home] [Help]
PACKAGE BODY: APPS.QP_PRICE_LIST_PUB
Source
1 PACKAGE BODY QP_Price_List_PUB AS
2 /* $Header: QPXPPRLB.pls 120.8.12020000.2 2013/03/19 06:05:39 kdurgasi ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Price_List_PUB';
7
8 -- Forward declaration of Procedure Id_To_Value
9
10 PROCEDURE Id_To_Value
11 ( p_PRICE_LIST_rec IN Price_List_Rec_Type
12 , p_PRICE_LIST_LINE_tbl IN Price_List_Line_Tbl_Type
13 , p_QUALIFIERS_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
14 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type
15 , x_PRICE_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Val_Rec_Type
16 , x_PRICE_LIST_LINE_val_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Val_Tbl_Type
17 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type
18 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
19 );
20
21 -- Forward declaration of procedure Value_To_Id
22
23 PROCEDURE Value_To_Id
24 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
25 , p_PRICE_LIST_rec IN Price_List_Rec_Type
26 , p_PRICE_LIST_val_rec IN Price_List_Val_Rec_Type
27 , p_PRICE_LIST_LINE_tbl IN Price_List_Line_Tbl_Type
28 , p_PRICE_LIST_LINE_val_tbl IN Price_List_Line_Val_Tbl_Type
29 , p_QUALIFIERS_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
30 , p_QUALIFIERS_val_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type
31 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type
32 , p_PRICING_ATTR_val_tbl IN Pricing_Attr_Val_Tbl_Type
33 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Rec_Type
34 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Tbl_Type
35 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
36 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
37 );
38
39 -- Start of Comments
40 -- API name Process_Price_List
41 -- Type Public
42 -- Function
43 --
44 -- Pre-reqs
45 --
46 -- Parameters
47 --
48 -- Version Current version = 1.0
49 -- Initial version = 1.0
50 --
51 -- Notes
52 --
53 -- End of Comments
54
55 PROCEDURE Process_Price_List
56 ( p_api_version_number IN NUMBER
57 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
58 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
59 , p_commit IN VARCHAR2 := FND_API.G_FALSE
60 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
61 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
62 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
63 , p_PRICE_LIST_rec IN Price_List_Rec_Type :=
64 G_MISS_PRICE_LIST_REC
65 , p_PRICE_LIST_val_rec IN Price_List_Val_Rec_Type :=
66 G_MISS_PRICE_LIST_VAL_REC
67 , p_PRICE_LIST_LINE_tbl IN Price_List_Line_Tbl_Type :=
68 G_MISS_PRICE_LIST_LINE_TBL
69 , p_PRICE_LIST_LINE_val_tbl IN Price_List_Line_Val_Tbl_Type :=
70 G_MISS_PRICE_LIST_LINE_VAL_TBL
71 , p_QUALIFIERS_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type :=
72 G_MISS_QUALIFIERS_TBL
73 , p_QUALIFIERS_val_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type :=
74 G_MISS_QUALIFIERS_VAL_TBL
75 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type :=
76 G_MISS_PRICING_ATTR_TBL
77 , p_PRICING_ATTR_val_tbl IN Pricing_Attr_Val_Tbl_Type :=
78 G_MISS_PRICING_ATTR_VAL_TBL
79 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Rec_Type
80 , x_PRICE_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Val_Rec_Type
81 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Tbl_Type
82 , x_PRICE_LIST_LINE_val_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Val_Tbl_Type
83 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
84 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type
85 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
86 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
87 , p_check_duplicate_lines IN VARCHAR2 DEFAULT NULL --5018856, 5024801, 5024919
88 )
89 IS
90 l_api_version_number CONSTANT NUMBER := 1.0;
91 l_api_name CONSTANT VARCHAR2(30):= 'Process_Price_List';
92 l_control_rec QP_GLOBALS.Control_Rec_Type;
93 l_return_status VARCHAR2(1);
94 l_PRICE_LIST_rec Price_List_Rec_Type;
95 l_PRICE_LIST_LINE_tbl Price_List_Line_Tbl_Type;
96 l_QUALIFIERS_tbl Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
97 l_PRICING_ATTR_tbl Pricing_Attr_Tbl_Type;
98 l_installed_status VARCHAR2(1);
99 l_request_type_code VARCHAR2(3);
100 l_list_source_code VARCHAR2(10); -- bug#3599792
101
102 l_p_PRICE_LIST_rec Price_List_Rec_Type; --[prarasto]
103 l_p_PRICE_LIST_LINE_tbl Price_List_Line_Tbl_Type; --[prarasto]
104 l_p_QUALIFIERS_tbl Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type; --[prarasto]
105 l_p_PRICING_ATTR_tbl Pricing_Attr_Tbl_Type; --[prarasto]
106
107 BEGIN
108
109 --5018856, 5024801, 5024919
110 SAVEPOINT PPL;
111 QP_GLOBALS.G_CHECK_DUP_PRICELIST_LINES := p_check_duplicate_lines;
112 oe_debug_pub.add('G_CHECK_DUP_PRICELIST_LINES: '||QP_GLOBALS.G_CHECK_DUP_PRICELIST_LINES);
113
114 -- Standard call to check for call compatibility
115
116 IF NOT FND_API.Compatible_API_Call
117 ( l_api_version_number
118 , p_api_version_number
119 , l_api_name
120 , G_PKG_NAME
121 )
122 THEN
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END IF;
125
126 /* check for installation status ; if it's basic then this api
127 is not available */
128
129 l_installed_status := QP_UTIL.get_qp_status;
130
131 --added for moac
132 --Initialize MOAC and set org context to Multiple
133
134 IF MO_GLOBAL.get_access_mode is null THEN
135 MO_GLOBAL.Init('QP');
136 -- MO_GLOBAL.set_policy_context('M', null);--commented as MO_GLOBAL.Init will set_policy_context to 'M' or 'S' based on profile settings
137 END IF;--MO_GLOBAL
138
139
140 -- shulin, calls from FTE
141 IF (QP_PRL_LOADER_PUB.G_PROCESS_LST_REQ_TYPE='FTE' or QP_MOD_LOADER_PUB.G_PROCESS_LST_REQ_TYPE='FTE' ) THEN
142 l_request_type_code := 'FTE';
143 END IF;
144
145
146 /* Raise error if Rounding factor is NULL.Code added to fix bug # 1641559 */
147
148 IF p_PRICE_LIST_rec.rounding_factor IS NULL THEN
149 FND_MESSAGE.SET_NAME('QP','SO_PR_NO_ROUNDING_FACTOR');
150 OE_MSG_PUB.Add;
151 RAISE FND_API.G_EXC_ERROR;
152 END IF;
153
154 -- bug#3599792 / bug#3957078 / bug4261021
155 IF p_PRICE_LIST_LINE_tbl.COUNT <> 0 THEN
156 IF p_PRICE_LIST_LINE_tbl(1).list_header_id IS NOT NULL AND
157 p_PRICE_LIST_LINE_tbl(1).list_header_id <> FND_API.G_MISS_NUM THEN
158 BEGIN
159 select list_source_code into l_list_source_code
160 from qp_list_headers_b
161 where list_header_id = p_PRICE_LIST_LINE_tbl(1).list_header_id;
162 EXCEPTION
163 WHEN OTHERS THEN
164 oe_debug_pub.add('PRICELIST LINE list_header_id : '||p_PRICE_LIST_LINE_tbl(1).list_header_id);
165 IF p_PRICE_LIST_rec.list_source_code IS NOT NULL
166 AND p_PRICE_LIST_rec.list_source_code <> FND_API.G_MISS_CHAR
167 THEN
168 l_list_source_code := p_PRICE_LIST_rec.list_source_code;
169 END IF;
170 END;
171 ELSIF p_PRICE_LIST_LINE_tbl(1).list_line_id IS NOT NULL AND
172 p_PRICE_LIST_LINE_tbl(1).list_line_id <> FND_API.G_MISS_NUM THEN
173 BEGIN
174 select list_source_code into l_list_source_code
175 from qp_list_headers_b
176 where list_header_id = (select list_header_id from qp_list_lines
177 where list_line_id = p_PRICE_LIST_LINE_tbl(1).list_line_id);
178 EXCEPTION
179 WHEN OTHERS THEN
180 oe_debug_pub.add('PRICELIST LINE list_line_id : '|| p_PRICE_LIST_LINE_tbl(1).list_line_id);
181 IF p_PRICE_LIST_rec.list_source_code IS NOT NULL
182 AND p_PRICE_LIST_rec.list_source_code <> FND_API.G_MISS_CHAR
183 THEN
184 l_list_source_code := p_PRICE_LIST_rec.list_source_code;
185 END IF;
186 END;
187 ELSE
188 IF p_PRICE_LIST_rec.list_source_code IS NOT NULL
189 AND p_PRICE_LIST_rec.list_source_code <> FND_API.G_MISS_CHAR
190 THEN
191 l_list_source_code := p_PRICE_LIST_rec.list_source_code;
192 END IF;
193 END IF;
194 ELSE
195 IF p_PRICE_LIST_rec.list_source_code IS NOT NULL
196 AND p_PRICE_LIST_rec.list_source_code <> FND_API.G_MISS_CHAR
197 THEN
198 l_list_source_code := p_PRICE_LIST_rec.list_source_code;
199 END IF;
200 END IF;
201 -- shulin, allow FTE to use this api since it comes with advanced pricing
202 IF l_installed_status IN ('S', 'N') AND l_request_type_code <> 'FTE' THEN -- shulin, allow 'FTE' to use this API
203
204 IF l_installed_status = 'S'
205 AND nvl(l_list_source_code,'NULL') = QP_GLOBALS.G_ENTITY_BSO -- bug#3599792
206 THEN --bug#3385041
207 NULL;
208 ELSE
209 l_return_status := FND_API.G_RET_STS_ERROR;
210 FND_MESSAGE.SET_NAME('QP', 'QP_BASIC_PRICING_UNAVAILABLE');
211 OE_MSG_PUB.Add;
212 RAISE FND_API.G_EXC_ERROR;
213 END IF;
214
215 END IF;
216
217
218 -- Perform Value to Id conversion
219
220 Value_To_Id
221 ( x_return_status => l_return_status
222 , p_PRICE_LIST_rec => p_PRICE_LIST_rec
223 , p_PRICE_LIST_val_rec => p_PRICE_LIST_val_rec
224 , p_PRICE_LIST_LINE_tbl => p_PRICE_LIST_LINE_tbl
225 , p_PRICE_LIST_LINE_val_tbl => p_PRICE_LIST_LINE_val_tbl
226 , p_QUALIFIERS_tbl => p_QUALIFIERS_tbl
227 , p_QUALIFIERS_val_tbl => p_QUALIFIERS_val_tbl
228 , p_PRICING_ATTR_tbl => p_PRICING_ATTR_tbl
229 , p_PRICING_ATTR_val_tbl => p_PRICING_ATTR_val_tbl
230 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
231 , x_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
232 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
233 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
234 );
235
236 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241
242
243 --bug#5154678 Continuous Price Break Changes
244 FOR i in 1..l_PRICE_LIST_LINE_tbl.COUNT
245 LOOP
246 IF l_PRICE_LIST_LINE_tbl(i).list_line_type_code = 'PBH' AND
247 l_PRICE_LIST_LINE_tbl(i).operation = QP_GLOBALS.G_OPR_CREATE and nvl(FND_PROFILE.VALUE('QP_CONTINUOUS_PB'),'Y')='Y'
248 THEN
249 l_PRICE_LIST_LINE_tbl(i).continuous_price_break_flag := 'Y';
250 END IF;
251 END LOOP;
252
253 -- Call QP_LIST_HEADERS_PVT.Process_Price_List
254 -- mkarya - set the called_from_ui indicator to 'N', as QP_LIST_HEADERS_PVT.Process_Price_List is
255 -- being called from public package
256
257 l_control_rec.called_from_ui := 'N';
258
259 l_p_PRICE_LIST_rec := l_PRICE_LIST_rec; --[prarasto]
260 l_p_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl; --[prarasto]
261 l_p_QUALIFIERS_tbl := l_QUALIFIERS_tbl; --[prarasto]
262 l_p_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl; --[prarasto]
263
264 QP_LIST_HEADERS_PVT.Process_Price_List
265 ( p_api_version_number => 1.0
266 , p_init_msg_list => p_init_msg_list
267 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
268 , p_commit => p_commit
269 , x_return_status => x_return_status
270 , x_msg_count => x_msg_count
271 , x_msg_data => x_msg_data
272 , p_control_rec => l_control_rec
273 , p_PRICE_LIST_rec => l_p_PRICE_LIST_rec
274 , p_PRICE_LIST_LINE_tbl => l_p_PRICE_LIST_LINE_tbl
275 , p_QUALIFIERS_tbl => l_p_QUALIFIERS_tbl
276 , p_PRICING_ATTR_tbl => l_p_PRICING_ATTR_tbl
277 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
278 , x_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
279 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
280 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
281 );
282
283
284 -- Load Id OUT parameters.
285
286 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
287 x_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
288 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
289 x_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl;
290
291 -- If p_return_values is TRUE then convert Ids to Values.
292
293 IF FND_API.to_Boolean(p_return_values) THEN
294
295 Id_To_Value
296 ( p_PRICE_LIST_rec => l_PRICE_LIST_rec
297 , p_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
298 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
299 , p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
300 , x_PRICE_LIST_val_rec => x_PRICE_LIST_val_rec
301 , x_PRICE_LIST_LINE_val_tbl => x_PRICE_LIST_LINE_val_tbl
302 , x_QUALIFIERS_val_tbl => x_QUALIFIERS_val_tbl
303 , x_PRICING_ATTR_val_tbl => x_PRICING_ATTR_val_tbl
304 );
305
306 END IF;
307
308 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
309 If x_return_status <> 'S' AND l_control_rec.called_from_ui='N' THEN
310 Rollback TO PPL;
311 END IF;
312
313 EXCEPTION
314
315 WHEN FND_API.G_EXC_ERROR THEN
316
317 x_return_status := FND_API.G_RET_STS_ERROR;
318
319 -- Get message count and data
320
321 oe_msg_pub.Count_And_Get
322 ( p_count => x_msg_count
323 , p_data => x_msg_data
324 );
325
326 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
327 If l_control_rec.called_from_ui='N' THEN
328 Rollback TO PPL;
329 END IF;
330
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
334
335 -- Get message count and data
336
337 oe_msg_pub.Count_And_Get
338 ( p_count => x_msg_count
339 , p_data => x_msg_data
340 );
341
342 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
343 If l_control_rec.called_from_ui='N' THEN
344 Rollback TO PPL;
345 END IF;
346
347 WHEN OTHERS THEN
348
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
350
351 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
352 THEN
353 oe_msg_pub.Add_Exc_Msg
354 ( G_PKG_NAME
355 , 'Process_Price_List'
356 );
357 END IF;
358
359 -- Get message count and data
360
361 oe_msg_pub.Count_And_Get
362 ( p_count => x_msg_count
363 , p_data => x_msg_data
364 );
365
366 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
367 If l_control_rec.called_from_ui='N' THEN
368 Rollback TO PPL;
369 END IF;
370
371 END Process_Price_List;
372
373 -- Start of Comments
374 -- API name Lock_Price_List
375 -- Type Public
376 -- Function
377 --
378 -- Pre-reqs
379 --
380 -- Parameters
381 --
382 -- Version Current version = 1.0
383 -- Initial version = 1.0
384 --
385 -- Notes
386 --
387 -- End of Comments
388
389 PROCEDURE Lock_Price_List
390 ( p_api_version_number IN NUMBER
391 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
392 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
393 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
394 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
395 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
396 , p_PRICE_LIST_rec IN Price_List_Rec_Type :=
397 G_MISS_PRICE_LIST_REC
398 , p_PRICE_LIST_val_rec IN Price_List_Val_Rec_Type :=
399 G_MISS_PRICE_LIST_VAL_REC
400 , p_PRICE_LIST_LINE_tbl IN Price_List_Line_Tbl_Type :=
401 G_MISS_PRICE_LIST_LINE_TBL
402 , p_PRICE_LIST_LINE_val_tbl IN Price_List_Line_Val_Tbl_Type :=
403 G_MISS_PRICE_LIST_LINE_VAL_TBL
404 , p_QUALIFIERS_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type :=
405 G_MISS_QUALIFIERS_TBL
406 , p_QUALIFIERS_val_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type :=
407 G_MISS_QUALIFIERS_VAL_TBL
408 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type :=
409 G_MISS_PRICING_ATTR_TBL
410 , p_PRICING_ATTR_val_tbl IN Pricing_Attr_Val_Tbl_Type :=
411 G_MISS_PRICING_ATTR_VAL_TBL
412 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Rec_Type
413 , x_PRICE_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Val_Rec_Type
414 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Tbl_Type
415 , x_PRICE_LIST_LINE_val_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Val_Tbl_Type
416 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
417 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type
418 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
419 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
420 )
421 IS
422 l_api_version_number CONSTANT NUMBER := 1.0;
423 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Price_List';
424 l_return_status VARCHAR2(1);
425 l_PRICE_LIST_rec Price_List_Rec_Type;
426 l_PRICE_LIST_LINE_tbl Price_List_Line_Tbl_Type;
427 l_QUALIFIERS_tbl Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
428 l_PRICING_ATTR_tbl Pricing_Attr_Tbl_Type;
429
430 l_p_PRICE_LIST_rec Price_List_Rec_Type; --[prarasto]
431 l_p_PRICE_LIST_LINE_tbl Price_List_Line_Tbl_Type; --[prarasto]
432 l_p_QUALIFIERS_tbl Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type; --[prarasto]
433 l_p_PRICING_ATTR_tbl Pricing_Attr_Tbl_Type; --[prarasto]
434
435 BEGIN
436
437 -- Standard call to check for call compatibility
438
439 IF NOT FND_API.Compatible_API_Call
440 ( l_api_version_number
441 , p_api_version_number
442 , l_api_name
443 , G_PKG_NAME
444 )
445 THEN
446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
447 END IF;
448
449
450 --added for moac
451 --Initialize MOAC and set org context to Multiple
452
453 IF MO_GLOBAL.get_access_mode is null THEN
454 MO_GLOBAL.Init('QP');
455 -- MO_GLOBAL.set_policy_context('M', null);--commented as MO_GLOBAL.Init will set_policy_context to 'M' or 'S' based on profile settings
456 END IF;--MO_GLOBAL
457
458
459 -- Perform Value to Id conversion
460
461 Value_To_Id
462 ( x_return_status => l_return_status
463 , p_PRICE_LIST_rec => p_PRICE_LIST_rec
464 , p_PRICE_LIST_val_rec => p_PRICE_LIST_val_rec
465 , p_PRICE_LIST_LINE_tbl => p_PRICE_LIST_LINE_tbl
466 , p_PRICE_LIST_LINE_val_tbl => p_PRICE_LIST_LINE_val_tbl
467 , p_QUALIFIERS_tbl => p_QUALIFIERS_tbl
468 , p_QUALIFIERS_val_tbl => p_QUALIFIERS_val_tbl
469 , p_PRICING_ATTR_tbl => p_PRICING_ATTR_tbl
470 , p_PRICING_ATTR_val_tbl => p_PRICING_ATTR_val_tbl
471 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
472 , x_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
473 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
474 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
475 );
476
477 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482
483
484 -- Call QP_LIST_HEADERS_PVT.Lock_Price_List
485
486 l_p_PRICE_LIST_rec := l_PRICE_LIST_rec; --[prarasto]
487 l_p_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl; --[prarasto]
488 l_p_QUALIFIERS_tbl := l_QUALIFIERS_tbl; --[prarasto]
489 l_p_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl; --[prarasto]
490
491 QP_LIST_HEADERS_PVT.Lock_Price_List
492 ( p_api_version_number => 1.0
493 , p_init_msg_list => p_init_msg_list
494 , x_return_status => x_return_status
495 , x_msg_count => x_msg_count
496 , x_msg_data => x_msg_data
497 , p_PRICE_LIST_rec => l_p_PRICE_LIST_rec
498 , p_PRICE_LIST_LINE_tbl => l_p_PRICE_LIST_LINE_tbl
499 , p_QUALIFIERS_tbl => l_p_QUALIFIERS_tbl
500 , p_PRICING_ATTR_tbl => l_p_PRICING_ATTR_tbl
501 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
502 , x_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
503 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
504 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
505 );
506
507 -- Load Id OUT parameters.
508
509 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
510 x_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
511 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
512 x_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl;
513
514 -- If p_return_values is TRUE then convert Ids to Values.
515
516 IF FND_API.to_Boolean(p_return_values) THEN
517
518 Id_To_Value
519 ( p_PRICE_LIST_rec => l_PRICE_LIST_rec
520 , p_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
521 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
522 , p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
523 , x_PRICE_LIST_val_rec => x_PRICE_LIST_val_rec
524 , x_PRICE_LIST_LINE_val_tbl => x_PRICE_LIST_LINE_val_tbl
525 , x_QUALIFIERS_val_tbl => x_QUALIFIERS_val_tbl
526 , x_PRICING_ATTR_val_tbl => x_PRICING_ATTR_val_tbl
527 );
528
529 END IF;
530
531 EXCEPTION
532
533 WHEN FND_API.G_EXC_ERROR THEN
534
535 x_return_status := FND_API.G_RET_STS_ERROR;
536
537 -- Get message count and data
538
539 oe_msg_pub.Count_And_Get
540 ( p_count => x_msg_count
541 , p_data => x_msg_data
542 );
543
544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
545
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
547
548 -- Get message count and data
549
550 oe_msg_pub.Count_And_Get
551 ( p_count => x_msg_count
552 , p_data => x_msg_data
553 );
554
555 WHEN OTHERS THEN
556
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
558
559 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
560 THEN
561 oe_msg_pub.Add_Exc_Msg
562 ( G_PKG_NAME
563 , 'Lock_Price_List'
564 );
565 END IF;
566
567 -- Get message count and data
568
569 oe_msg_pub.Count_And_Get
570 ( p_count => x_msg_count
571 , p_data => x_msg_data
572 );
573
574 END Lock_Price_List;
575
576 -- Start of Comments
577 -- API name Get_Price_List
578 -- Type Public
579 -- Function
580 --
581 -- Pre-reqs
582 --
583 -- Parameters
584 --
585 -- Version Current version = 1.0
586 -- Initial version = 1.0
587 --
588 -- Notes
589 --
590 -- End of Comments
591
592 PROCEDURE Get_Price_List
593 ( p_api_version_number IN NUMBER
594 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
595 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
596 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
597 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
598 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
599 , p_list_header_id IN NUMBER :=
600 FND_API.G_MISS_NUM
601 , p_list_header IN VARCHAR2 :=
602 FND_API.G_MISS_CHAR
603 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Rec_Type
604 , x_PRICE_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Val_Rec_Type
605 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Tbl_Type
606 , x_PRICE_LIST_LINE_val_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Val_Tbl_Type
607 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
608 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type
609 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
610 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
611 )
612 IS
613 l_api_version_number CONSTANT NUMBER := 1.0;
614 l_api_name CONSTANT VARCHAR2(30):= 'Get_Price_List';
615 l_list_header_id NUMBER := p_list_header_id;
616 l_PRICE_LIST_rec QP_Price_List_PUB.Price_List_Rec_Type;
617 l_PRICE_LIST_LINE_tbl QP_Price_List_PUB.Price_List_Line_Tbl_Type;
618 l_QUALIFIERS_tbl Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
619 l_PRICING_ATTR_tbl QP_Price_List_PUB.Pricing_Attr_Tbl_Type;
620 BEGIN
621
622 -- Standard call to check for call compatibility
623
624 IF NOT FND_API.Compatible_API_Call
625 ( l_api_version_number
626 , p_api_version_number
627 , l_api_name
628 , G_PKG_NAME
629 )
630 THEN
631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
632 END IF;
633
634
635 --added for moac
636 --Initialize MOAC and set org context to Multiple
637
638 IF MO_GLOBAL.get_access_mode is null THEN
639 MO_GLOBAL.Init('QP');
640 -- MO_GLOBAL.set_policy_context('M', null);--commented as MO_GLOBAL.Init will set_policy_context to 'M' or 'S' based on profile settings
641 END IF;--MO_GLOBAL
642
643
644 -- Standard check for Val/ID conversion
645
646 IF p_list_header = FND_API.G_MISS_CHAR
647 THEN
648
649 l_list_header_id := p_list_header_id;
650
651 ELSIF p_list_header_id <> FND_API.G_MISS_NUM THEN
652
653 l_list_header_id := p_list_header_id;
654
655 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_SUCCESS)
656 THEN
657
658 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
659 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_header');
660 oe_msg_pub.Add;
661
662 END IF;
663
664 ELSE
665
666 -- Convert Value to Id
667
668 l_list_header_id := QP_Value_To_Id.list_header
669 ( p_list_header => p_list_header
670 );
671
672 IF l_list_header_id = FND_API.G_MISS_NUM THEN
673 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_ERROR)
674 THEN
675
676 FND_MESSAGE.SET_NAME('QP','Invalid Business Object Value');
677 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_header');
678 oe_msg_pub.Add;
679
680 END IF;
681 END IF;
682
683 RAISE FND_API.G_EXC_ERROR;
684
685 END IF;
686
687
688 -- Call QP_LIST_HEADERS_PVT.Get_Price_List
689
690 QP_LIST_HEADERS_PVT.Get_Price_List
691 ( p_api_version_number => 1.0
692 , p_init_msg_list => p_init_msg_list
693 , x_return_status => x_return_status
694 , x_msg_count => x_msg_count
695 , x_msg_data => x_msg_data
696 , p_list_header_id => l_list_header_id
697 , x_PRICE_LIST_rec => l_PRICE_LIST_rec
698 , x_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
699 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
700 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
701 );
702
703 -- Load Id OUT parameters.
704
705 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
706 x_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
707 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
708 x_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl;
709
710 -- If p_return_values is TRUE then convert Ids to Values.
711
712 IF FND_API.TO_BOOLEAN(p_return_values) THEN
713
714 Id_To_Value
715 ( p_PRICE_LIST_rec => l_PRICE_LIST_rec
716 , p_PRICE_LIST_LINE_tbl => l_PRICE_LIST_LINE_tbl
717 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
718 , p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
719 , x_PRICE_LIST_val_rec => x_PRICE_LIST_val_rec
720 , x_PRICE_LIST_LINE_val_tbl => x_PRICE_LIST_LINE_val_tbl
721 , x_QUALIFIERS_val_tbl => x_QUALIFIERS_val_tbl
722 , x_PRICING_ATTR_val_tbl => x_PRICING_ATTR_val_tbl
723 );
724
725 END IF;
726
727 -- Set return status
728
729 x_return_status := FND_API.G_RET_STS_SUCCESS;
730
731 -- Get message count and data
732
733 oe_msg_pub.Count_And_Get
734 ( p_count => x_msg_count
735 , p_data => x_msg_data
736 );
737
738
739 EXCEPTION
740
741 WHEN FND_API.G_EXC_ERROR THEN
742
743 x_return_status := FND_API.G_RET_STS_ERROR;
744
745 -- Get message count and data
746
747 oe_msg_pub.Count_And_Get
748 ( p_count => x_msg_count
749 , p_data => x_msg_data
750 );
751
752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
753
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
755
756 -- Get message count and data
757
758 oe_msg_pub.Count_And_Get
759 ( p_count => x_msg_count
760 , p_data => x_msg_data
761 );
762
763 WHEN OTHERS THEN
764
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
766
767 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
768 THEN
769 oe_msg_pub.Add_Exc_Msg
770 ( G_PKG_NAME
771 , 'Get_Price_List'
772 );
773 END IF;
774
775 -- Get message count and data
776
777 oe_msg_pub.Count_And_Get
778 ( p_count => x_msg_count
779 , p_data => x_msg_data
780 );
781
782 END Get_Price_List;
783
784 -- Procedure Id_To_Value
785
786 PROCEDURE Id_To_Value
787 ( p_PRICE_LIST_rec IN Price_List_Rec_Type
788 , p_PRICE_LIST_LINE_tbl IN Price_List_Line_Tbl_Type
789 , p_QUALIFIERS_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
790 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type
791 , x_PRICE_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Val_Rec_Type
792 , x_PRICE_LIST_LINE_val_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Val_Tbl_Type
793 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type
794 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
795 )
796 IS
797 BEGIN
798
799 -- Convert PRICE_LIST
800
801 x_PRICE_LIST_val_rec := QP_Price_List_Util.Get_Values(p_PRICE_LIST_rec);
802
803 -- Convert PRICE_LIST_LINE
804
805 FOR I IN 1..p_PRICE_LIST_LINE_tbl.COUNT LOOP
806 x_PRICE_LIST_LINE_val_tbl(I) :=
807 QP_Price_List_Line_Util.Get_Values(p_PRICE_LIST_LINE_tbl(I));
808 END LOOP;
809
810 -- Convert QUALIFIERS
811
812 FOR I IN 1..p_QUALIFIERS_tbl.COUNT LOOP
813 x_QUALIFIERS_val_tbl(I) :=
814 QP_Qualifiers_Util.Get_Values(p_QUALIFIERS_tbl(I));
815 END LOOP;
816
817
818 -- Convert PRICING_ATTR
819
820 FOR I IN 1..p_PRICING_ATTR_tbl.COUNT LOOP
821 x_PRICING_ATTR_val_tbl(I) :=
822 QP_pll_pricing_attr_Util.Get_Values(p_PRICING_ATTR_tbl(I));
823 END LOOP;
824
825 EXCEPTION
826
827 WHEN OTHERS THEN
828
829 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
830 THEN
831 oe_msg_pub.Add_Exc_Msg
832 ( G_PKG_NAME
833 , 'Id_To_Value'
834 );
835 END IF;
836
837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
838
839 END Id_To_Value;
840
841 -- Procedure Value_To_Id
842
843 PROCEDURE Value_To_Id
844 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
845 , p_PRICE_LIST_rec IN Price_List_Rec_Type
846 , p_PRICE_LIST_val_rec IN Price_List_Val_Rec_Type
847 , p_PRICE_LIST_LINE_tbl IN Price_List_Line_Tbl_Type
848 , p_PRICE_LIST_LINE_val_tbl IN Price_List_Line_Val_Tbl_Type
849 , p_QUALIFIERS_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
850 , p_QUALIFIERS_val_tbl IN Qp_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type
851 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type
852 , p_PRICING_ATTR_val_tbl IN Pricing_Attr_Val_Tbl_Type
853 , x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ Price_List_Rec_Type
854 , x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ Price_List_Line_Tbl_Type
855 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type
856 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
857 )
858 IS
859 l_PRICE_LIST_rec Price_List_Rec_Type;
860 l_PRICE_LIST_LINE_rec Price_List_Line_Rec_Type;
861 l_QUALIFIERS_rec Qp_Qualifier_Rules_Pub.Qualifiers_Rec_Type;
862 l_PRICING_ATTR_rec Pricing_Attr_Rec_Type;
863 l_index BINARY_INTEGER;
864 BEGIN
865
866 -- Init x_return_status.
867
868 x_return_status := FND_API.G_RET_STS_SUCCESS;
869
870 -- Convert PRICE_LIST
871
872 l_PRICE_LIST_rec := QP_Price_List_Util.Get_Ids
873 ( p_PRICE_LIST_rec => p_PRICE_LIST_rec
874 , p_PRICE_LIST_val_rec => p_PRICE_LIST_val_rec
875 );
876
877 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
878
879 IF l_PRICE_LIST_rec.return_status = FND_API.G_RET_STS_ERROR THEN
880 x_return_status := FND_API.G_RET_STS_ERROR;
881 END IF;
882
883 -- Convert PRICE_LIST_LINE
884
885 x_PRICE_LIST_LINE_tbl := p_PRICE_LIST_LINE_tbl;
886
887 l_index := p_PRICE_LIST_LINE_val_tbl.FIRST;
888
889 WHILE l_index IS NOT NULL LOOP
890
891 l_PRICE_LIST_LINE_rec := QP_Price_List_Line_Util.Get_Ids
892 ( p_PRICE_LIST_LINE_rec => p_PRICE_LIST_LINE_tbl(l_index)
893 , p_PRICE_LIST_LINE_val_rec => p_PRICE_LIST_LINE_val_tbl(l_index)
894 );
895
896 x_PRICE_LIST_LINE_tbl(l_index) := l_PRICE_LIST_LINE_rec;
897
898 IF l_PRICE_LIST_LINE_rec.return_status = FND_API.G_RET_STS_ERROR THEN
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 END IF;
901
902 l_index := p_PRICE_LIST_LINE_val_tbl.NEXT(l_index);
903
904 END LOOP;
905
906 -- Convert QUALIFIERS
907
908 x_QUALIFIERS_tbl := p_QUALIFIERS_tbl;
909
910 l_index := p_QUALIFIERS_val_tbl.FIRST;
911
912
913 WHILE l_index IS NOT NULL LOOP
914
915 l_QUALIFIERS_rec := QP_Qualifiers_Util.Get_Ids
916 ( p_QUALIFIERS_rec => p_QUALIFIERS_tbl(l_index)
917 , p_QUALIFIERS_val_rec => p_QUALIFIERS_val_tbl(l_index)
918 );
919
920 x_QUALIFIERS_tbl(l_index) := l_QUALIFIERS_rec;
921
922 IF l_QUALIFIERS_rec.return_status = FND_API.G_RET_STS_ERROR THEN
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 END IF;
925
926 l_index := p_QUALIFIERS_val_tbl.NEXT(l_index);
927
928 END LOOP;
929
930
931 -- Convert PRICING_ATTR
932
933 x_PRICING_ATTR_tbl := p_PRICING_ATTR_tbl;
934
935 l_index := p_PRICING_ATTR_val_tbl.FIRST;
936
937 WHILE l_index IS NOT NULL LOOP
938
939 l_PRICING_ATTR_rec := QP_pll_pricing_attr_Util.Get_Ids
940 ( p_PRICING_ATTR_rec => p_PRICING_ATTR_tbl(l_index)
941 , p_PRICING_ATTR_val_rec => p_PRICING_ATTR_val_tbl(l_index)
942 );
943
944 x_PRICING_ATTR_tbl(l_index) := l_PRICING_ATTR_rec;
945
946 IF l_PRICING_ATTR_rec.return_status = FND_API.G_RET_STS_ERROR THEN
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 END IF;
949
950 l_index := p_PRICING_ATTR_val_tbl.NEXT(l_index);
951
952 END LOOP;
953
954 EXCEPTION
955
956 WHEN OTHERS THEN
957
958 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
959 THEN
960 oe_msg_pub.Add_Exc_Msg
961 ( G_PKG_NAME
962 , 'Value_To_Id'
963 );
964 END IF;
965
966 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
967
968 END Value_To_Id;
969
970 END QP_Price_List_PUB;