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