[Home] [Help]
PACKAGE BODY: APPS.QP_MODIFIERS_PUB
Source
1 PACKAGE BODY QP_Modifiers_PUB AS
2 /* $Header: QPXPMLSB.pls 120.8 2006/10/25 08:22:25 nirmkuma 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
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 QP_Modifiers_PVT.Process_Modifiers
258 ( p_api_version_number => 1.0
259 , p_init_msg_list => p_init_msg_list
260 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
261 , p_commit => p_commit
262 , x_return_status => x_return_status
263 , x_msg_count => x_msg_count
264 , x_msg_data => x_msg_data
265 , p_control_rec => l_control_rec
266 , p_MODIFIER_LIST_rec => l_p_MODIFIER_LIST_rec
267 , p_MODIFIERS_tbl => l_p_MODIFIERS_tbl
268 , p_QUALIFIERS_tbl => l_p_QUALIFIERS_tbl
269 , p_PRICING_ATTR_tbl => l_p_PRICING_ATTR_tbl
270 , x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
271 , x_MODIFIERS_tbl => l_MODIFIERS_tbl
272 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
273 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
274 );
275
276
277
278 -- Load Id OUT parameters.
279
280 x_MODIFIER_LIST_rec := l_MODIFIER_LIST_rec;
281 x_MODIFIERS_tbl := l_MODIFIERS_tbl;
282 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
283 x_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl;
284
285 -- If p_return_values is TRUE then convert Ids to Values.
286
287 IF FND_API.to_Boolean(p_return_values) THEN
288
289 Id_To_Value
290 ( p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
291 , p_MODIFIERS_tbl => l_MODIFIERS_tbl
292 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
293 , p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
294 , x_MODIFIER_LIST_val_rec => x_MODIFIER_LIST_val_rec
295 , x_MODIFIERS_val_tbl => x_MODIFIERS_val_tbl
296 , x_QUALIFIERS_val_tbl => x_QUALIFIERS_val_tbl
297 , x_PRICING_ATTR_val_tbl => x_PRICING_ATTR_val_tbl
298 );
299
300 END IF;
301
302 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
303
304 If x_return_status <> 'S' AND l_control_rec.called_from_ui='N' THEN
305 Rollback TO QP_Process_Modifiers;
306 END IF;
307
308 oe_debug_pub.add('END process_modifiers in Public');
309 --dbms_output.put_line('END process_modifiers in Public');
310 EXCEPTION
311
312 WHEN FND_API.G_EXC_ERROR THEN
313
314 x_return_status := FND_API.G_RET_STS_ERROR;
315
316 -- Get message count and data
317
318 OE_MSG_PUB.Count_And_Get
319 ( p_count => x_msg_count
320 , p_data => x_msg_data
321 );
322
323 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
324 If l_control_rec.called_from_ui='N' THEN
325 Rollback TO QP_Process_Modifiers;
326 END IF;
327
328
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330
331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
332
333 -- Get message count and data
334
335 OE_MSG_PUB.Count_And_Get
336 ( p_count => x_msg_count
337 , p_data => x_msg_data
338 );
339
340 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
341 If l_control_rec.called_from_ui='N' THEN
342 Rollback TO QP_Process_Modifiers;
343 END IF;
344
345 WHEN OTHERS THEN
346
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
348
349 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
350 THEN
351 OE_MSG_PUB.Add_Exc_Msg
352 ( G_PKG_NAME
353 , 'Process_Modifiers'
354 );
355 END IF;
356
357 -- Get message count and data
358
359 OE_MSG_PUB.Count_And_Get
360 ( p_count => x_msg_count
361 , p_data => x_msg_data
362 );
363
364 --Roll Back the transaction if the return status is not success and it is called from API, bug #5345652
365 If l_control_rec.called_from_ui='N' THEN
366 Rollback TO QP_Process_Modifiers;
367 END IF;
368
369 END Process_Modifiers;
370
371 -- Start of Comments
372 -- API name Lock_Modifiers
373 -- Type Public
374 -- Function
375 --
376 -- Pre-reqs
377 --
378 -- Parameters
379 --
380 -- Version Current version = 1.0
381 -- Initial version = 1.0
382 --
383 -- Notes
384 --
385 -- End of Comments
386
387 PROCEDURE Lock_Modifiers
388 ( p_api_version_number IN NUMBER
389 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
390 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
391 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
392 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
393 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
394 , p_MODIFIER_LIST_rec IN Modifier_List_Rec_Type :=
395 G_MISS_MODIFIER_LIST_REC
396 , p_MODIFIER_LIST_val_rec IN Modifier_List_Val_Rec_Type :=
397 G_MISS_MODIFIER_LIST_VAL_REC
398 , p_MODIFIERS_tbl IN Modifiers_Tbl_Type :=
399 G_MISS_MODIFIERS_TBL
400 , p_MODIFIERS_val_tbl IN Modifiers_Val_Tbl_Type :=
401 G_MISS_MODIFIERS_VAL_TBL
402 , p_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type :=
403 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_TBL
404 , p_QUALIFIERS_val_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type :=
405 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_VAL_TBL
406 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type :=
407 G_MISS_PRICING_ATTR_TBL
408 , p_PRICING_ATTR_val_tbl IN Pricing_Attr_Val_Tbl_Type :=
409 G_MISS_PRICING_ATTR_VAL_TBL
410 , x_MODIFIER_LIST_rec OUT NOCOPY /* file.sql.39 change */ Modifier_List_Rec_Type
411 , x_MODIFIER_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Modifier_List_Val_Rec_Type
412 , x_MODIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Modifiers_Tbl_Type
413 , x_MODIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Modifiers_Val_Tbl_Type
414 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
415 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type
416 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
417 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
418 )
419 IS
420 l_api_version_number CONSTANT NUMBER := 1.0;
421 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Modifiers';
422 l_return_status VARCHAR2(1);
423 l_MODIFIER_LIST_rec Modifier_List_Rec_Type;
424 l_p_MODIFIER_LIST_rec Modifier_List_Rec_Type;
425 l_MODIFIERS_tbl Modifiers_Tbl_Type;
426 l_p_MODIFIERS_tbl Modifiers_Tbl_Type;
427 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
428 l_p_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
429 l_PRICING_ATTR_tbl Pricing_Attr_Tbl_Type;
430 l_p_PRICING_ATTR_tbl Pricing_Attr_Tbl_Type;
431 BEGIN
432
433 oe_debug_pub.add('BEGIN lock_modifiers in Public');
434 -- Standard call to check for call compatibility
435
436 IF NOT FND_API.Compatible_API_Call
437 ( l_api_version_number
438 , p_api_version_number
439 , l_api_name
440 , G_PKG_NAME
441 )
442 THEN
443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
444 END IF;
445
446 --added for moac
447 --Initialize MOAC and set org context to Multiple
448
449 IF MO_GLOBAL.get_access_mode is null THEN
450 MO_GLOBAL.Init('QP');
451 -- MO_GLOBAL.set_policy_context('M', null);--commented as MO_GLOBAL.Init will set_policy_context to 'M' or 'S' based on profile settings
452 END IF;--MO_GLOBAL
453
454
455
456 -- Perform Value to Id conversion
457
458 Value_To_Id
459 ( x_return_status => l_return_status
460 , p_MODIFIER_LIST_rec => p_MODIFIER_LIST_rec
461 , p_MODIFIER_LIST_val_rec => p_MODIFIER_LIST_val_rec
462 , p_MODIFIERS_tbl => p_MODIFIERS_tbl
463 , p_MODIFIERS_val_tbl => p_MODIFIERS_val_tbl
464 , p_QUALIFIERS_tbl => p_QUALIFIERS_tbl
465 , p_QUALIFIERS_val_tbl => p_QUALIFIERS_val_tbl
466 , p_PRICING_ATTR_tbl => p_PRICING_ATTR_tbl
467 , p_PRICING_ATTR_val_tbl => p_PRICING_ATTR_val_tbl
468 , x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
469 , x_MODIFIERS_tbl => l_MODIFIERS_tbl
470 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
471 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
472 );
473
474 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
476 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479
480
481 -- Call QP_Modifiers_PVT.Lock_Modifiers
482 l_p_MODIFIER_LIST_rec := l_MODIFIER_LIST_rec;
483 l_p_MODIFIERS_tbl := l_MODIFIERS_tbl;
484 l_p_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
485 l_p_PRICING_ATTR_tbl := l_PRICING_ATTR_tbl;
486 QP_Modifiers_PVT.Lock_Modifiers
487 ( p_api_version_number => 1.0
488 , p_init_msg_list => p_init_msg_list
489 , x_return_status => x_return_status
490 , x_msg_count => x_msg_count
491 , x_msg_data => x_msg_data
492 , p_MODIFIER_LIST_rec => l_p_MODIFIER_LIST_rec
493 , p_MODIFIERS_tbl => l_p_MODIFIERS_tbl
494 , p_QUALIFIERS_tbl => l_p_QUALIFIERS_tbl
495 , p_PRICING_ATTR_tbl => l_p_PRICING_ATTR_tbl
496 , x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
497 , x_MODIFIERS_tbl => l_MODIFIERS_tbl
498 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
499 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
500 );
501
502 -- Load Id OUT parameters.
503
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 -- If p_return_values is TRUE then convert Ids to Values.
510
511 IF FND_API.to_Boolean(p_return_values) THEN
512
513 Id_To_Value
514 ( p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
515 , p_MODIFIERS_tbl => l_MODIFIERS_tbl
516 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
517 , p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
518 , x_MODIFIER_LIST_val_rec => x_MODIFIER_LIST_val_rec
519 , x_MODIFIERS_val_tbl => x_MODIFIERS_val_tbl
520 , x_QUALIFIERS_val_tbl => x_QUALIFIERS_val_tbl
521 , x_PRICING_ATTR_val_tbl => x_PRICING_ATTR_val_tbl
522 );
523
524 END IF;
525
526 EXCEPTION
527
528 WHEN FND_API.G_EXC_ERROR THEN
529
530 x_return_status := FND_API.G_RET_STS_ERROR;
531
532 -- Get message count and data
533
534 OE_MSG_PUB.Count_And_Get
535 ( p_count => x_msg_count
536 , p_data => x_msg_data
537 );
538
539 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
540
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
542
543 -- Get message count and data
544
545 OE_MSG_PUB.Count_And_Get
546 ( p_count => x_msg_count
547 , p_data => x_msg_data
548 );
549
550 WHEN OTHERS THEN
551
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
553
554 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
555 THEN
556 OE_MSG_PUB.Add_Exc_Msg
557 ( G_PKG_NAME
558 , 'Lock_Modifiers'
559 );
560 END IF;
561
562 -- Get message count and data
563
564 OE_MSG_PUB.Count_And_Get
565 ( p_count => x_msg_count
566 , p_data => x_msg_data
567 );
568
569 oe_debug_pub.add('END lock_modifiers in Public');
570 END Lock_Modifiers;
571
572 -- Start of Comments
573 -- API name Get_Modifiers
574 -- Type Public
575 -- Function
576 --
577 -- Pre-reqs
578 --
579 -- Parameters
580 --
581 -- Version Current version = 1.0
582 -- Initial version = 1.0
583 --
584 -- Notes
585 --
586 -- End of Comments
587
588 PROCEDURE Get_Modifiers
589 ( p_api_version_number IN NUMBER
590 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
591 , p_return_values IN VARCHAR2 := FND_API.G_FALSE
592 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
593 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
594 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
595 , p_list_header_id IN NUMBER :=
596 FND_API.G_MISS_NUM
597 , p_list_header IN VARCHAR2 :=
598 FND_API.G_MISS_CHAR
599 , x_MODIFIER_LIST_rec OUT NOCOPY /* file.sql.39 change */ Modifier_List_Rec_Type
600 , x_MODIFIER_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Modifier_List_Val_Rec_Type
601 , x_MODIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Modifiers_Tbl_Type
602 , x_MODIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Modifiers_Val_Tbl_Type
603 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
604 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type
605 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
606 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
607 )
608 IS
609 l_api_version_number CONSTANT NUMBER := 1.0;
610 l_api_name CONSTANT VARCHAR2(30):= 'Get_Modifiers';
611 l_list_header_id NUMBER := p_list_header_id;
612 l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
613 l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
614 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
615 l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
616 BEGIN
617
618 oe_debug_pub.add('BEGIN get_modifiers in Public');
619 -- Standard call to check for call compatibility
620
621 IF NOT FND_API.Compatible_API_Call
622 ( l_api_version_number
623 , p_api_version_number
624 , l_api_name
625 , G_PKG_NAME
626 )
627 THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631 --added for moac
632 --Initialize MOAC and set org context to Multiple
633
634 IF MO_GLOBAL.get_access_mode is null THEN
635 MO_GLOBAL.Init('QP');
636 -- MO_GLOBAL.set_policy_context('M', null);--commented as MO_GLOBAL.Init will set_policy_context to 'M' or 'S' based on profile settings
637 END IF;--MO_GLOBAL
638
639
640
641 -- Standard check for Val/ID conversion
642
643 IF p_list_header = FND_API.G_MISS_CHAR
644 THEN
645
646 l_list_header_id := p_list_header_id;
647
648 ELSIF p_list_header_id <> FND_API.G_MISS_NUM THEN
649
650 l_list_header_id := p_list_header_id;
651
652 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
653 THEN
654
655 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
656 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_header');
657 OE_MSG_PUB.Add;
658
659 END IF;
660
661 ELSE
662
663 -- Convert Value to Id
664
665 l_list_header_id := QP_Value_To_Id.list_header
666 ( p_list_header => p_list_header
667 );
668
669 IF l_list_header_id = FND_API.G_MISS_NUM THEN
670 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
671 THEN
672
673 FND_MESSAGE.SET_NAME('QP','Invalid Business Object Value');
674 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_header');
675 OE_MSG_PUB.Add;
676
677 END IF;
678 END IF;
679
680 RAISE FND_API.G_EXC_ERROR;
681
682 END IF;
683
684
685 -- Call QP_Modifiers_PVT.Get_Modifiers
686
687 QP_Modifiers_PVT.Get_Modifiers
688 ( p_api_version_number => 1.0
689 , p_init_msg_list => p_init_msg_list
690 , x_return_status => x_return_status
691 , x_msg_count => x_msg_count
692 , x_msg_data => x_msg_data
693 , p_list_header_id => l_list_header_id
694 , x_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
695 , x_MODIFIERS_tbl => l_MODIFIERS_tbl
696 , x_QUALIFIERS_tbl => l_QUALIFIERS_tbl
697 , x_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
698 );
699
700 -- Load Id OUT parameters.
701
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 -- If p_return_values is TRUE then convert Ids to Values.
708
709 IF FND_API.TO_BOOLEAN(p_return_values) THEN
710
711 Id_To_Value
712 ( p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
713 , p_MODIFIERS_tbl => l_MODIFIERS_tbl
714 , p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
715 , p_PRICING_ATTR_tbl => l_PRICING_ATTR_tbl
716 , x_MODIFIER_LIST_val_rec => x_MODIFIER_LIST_val_rec
717 , x_MODIFIERS_val_tbl => x_MODIFIERS_val_tbl
718 , x_QUALIFIERS_val_tbl => x_QUALIFIERS_val_tbl
719 , x_PRICING_ATTR_val_tbl => x_PRICING_ATTR_val_tbl
720 );
721
722 END IF;
723
724 -- Set return status
725
726 x_return_status := FND_API.G_RET_STS_SUCCESS;
727
728 -- Get message count and data
729
730 OE_MSG_PUB.Count_And_Get
731 ( p_count => x_msg_count
732 , p_data => x_msg_data
733 );
734
735
736 EXCEPTION
737
738 WHEN FND_API.G_EXC_ERROR THEN
739
740 x_return_status := FND_API.G_RET_STS_ERROR;
741
742 -- Get message count and data
743
744 OE_MSG_PUB.Count_And_Get
745 ( p_count => x_msg_count
746 , p_data => x_msg_data
747 );
748
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750
751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
752
753 -- Get message count and data
754
755 OE_MSG_PUB.Count_And_Get
756 ( p_count => x_msg_count
757 , p_data => x_msg_data
758 );
759
760 WHEN OTHERS THEN
761
762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
763
764 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
765 THEN
766 OE_MSG_PUB.Add_Exc_Msg
767 ( G_PKG_NAME
768 , 'Get_Modifiers'
769 );
770 END IF;
771
772 -- Get message count and data
773
774 OE_MSG_PUB.Count_And_Get
775 ( p_count => x_msg_count
776 , p_data => x_msg_data
777 );
778
779 oe_debug_pub.add('END get_modifiers in Public');
780 END Get_Modifiers;
781
782 -- Procedure Id_To_Value
783
784 PROCEDURE Id_To_Value
785 ( p_MODIFIER_LIST_rec IN Modifier_List_Rec_Type
786 , p_MODIFIERS_tbl IN Modifiers_Tbl_Type
787 , p_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
788 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type
789 , x_MODIFIER_LIST_val_rec OUT NOCOPY /* file.sql.39 change */ Modifier_List_Val_Rec_Type
790 , x_MODIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ Modifiers_Val_Tbl_Type
791 , x_QUALIFIERS_val_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type
792 , x_PRICING_ATTR_val_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Val_Tbl_Type
793 )
794 IS
795 BEGIN
796
797 oe_debug_pub.add('BEGIN id_to_value in Public');
798 -- Convert MODIFIER_LIST
799
800 x_MODIFIER_LIST_val_rec := QP_Modifier_List_Util.Get_Values(p_MODIFIER_LIST_rec);
801
802 -- Convert MODIFIERS
803
804 FOR I IN 1..p_MODIFIERS_tbl.COUNT LOOP
805 x_MODIFIERS_val_tbl(I) :=
806 QP_Modifiers_Util.Get_Values(p_MODIFIERS_tbl(I));
807 END LOOP;
808
809 -- Convert QUALIFIERS
810
811 FOR I IN 1..p_QUALIFIERS_tbl.COUNT LOOP
812 x_QUALIFIERS_val_tbl(I) :=
813 QP_Qualifiers_Util.Get_Values(p_QUALIFIERS_tbl(I));
814 END LOOP;
815
816 -- Convert PRICING_ATTR
817
818 FOR I IN 1..p_PRICING_ATTR_tbl.COUNT LOOP
819 x_PRICING_ATTR_val_tbl(I) :=
820 QP_Pricing_Attr_Util.Get_Values(p_PRICING_ATTR_tbl(I));
821 END LOOP;
822
823 EXCEPTION
824
825 WHEN OTHERS THEN
826
827 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
828 THEN
829 OE_MSG_PUB.Add_Exc_Msg
830 ( G_PKG_NAME
831 , 'Id_To_Value'
832 );
833 END IF;
834
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836
837 oe_debug_pub.add('END id_to_value in Public');
838 END Id_To_Value;
839
840 -- Procedure Value_To_Id
841
842 PROCEDURE Value_To_Id
843 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
844 , p_MODIFIER_LIST_rec IN Modifier_List_Rec_Type
845 , p_MODIFIER_LIST_val_rec IN Modifier_List_Val_Rec_Type
846 , p_MODIFIERS_tbl IN Modifiers_Tbl_Type
847 , p_MODIFIERS_val_tbl IN Modifiers_Val_Tbl_Type
848 , p_QUALIFIERS_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
849 , p_QUALIFIERS_val_tbl IN QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type
850 , p_PRICING_ATTR_tbl IN Pricing_Attr_Tbl_Type
851 , p_PRICING_ATTR_val_tbl IN Pricing_Attr_Val_Tbl_Type
852 , x_MODIFIER_LIST_rec OUT NOCOPY /* file.sql.39 change */ Modifier_List_Rec_Type
853 , x_MODIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Modifiers_Tbl_Type
854 , x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
855 , x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ Pricing_Attr_Tbl_Type
856 )
857 IS
858 l_MODIFIER_LIST_rec Modifier_List_Rec_Type;
859 l_MODIFIERS_rec Modifiers_Rec_Type;
860 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
861 l_PRICING_ATTR_rec Pricing_Attr_Rec_Type;
862 l_index BINARY_INTEGER;
863 BEGIN
864
865 oe_debug_pub.add('START value_to_id in Public');
866
867 -- Init x_return_status.
868 x_return_status := FND_API.G_RET_STS_SUCCESS;
869
870
871 -- Convert MODIFIER_LIST
872
873 l_MODIFIER_LIST_rec := QP_Modifier_List_Util.Get_Ids
874 ( p_MODIFIER_LIST_rec => p_MODIFIER_LIST_rec
875 , p_MODIFIER_LIST_val_rec => p_MODIFIER_LIST_val_rec
876 );
877
878 x_MODIFIER_LIST_rec := l_MODIFIER_LIST_rec;
879
880 IF l_MODIFIER_LIST_rec.return_status = FND_API.G_RET_STS_ERROR THEN
881 x_return_status := FND_API.G_RET_STS_ERROR;
882 END IF;
883
884 -- Convert MODIFIERS
885
886 x_MODIFIERS_tbl := p_MODIFIERS_tbl;
887
888 l_index := p_MODIFIERS_val_tbl.FIRST;
889
890 WHILE l_index IS NOT NULL LOOP
891
892 l_MODIFIERS_rec := QP_Modifiers_Util.Get_Ids
893 ( p_MODIFIERS_rec => p_MODIFIERS_tbl(l_index)
894 , p_MODIFIERS_val_rec => p_MODIFIERS_val_tbl(l_index)
895 );
896
897 x_MODIFIERS_tbl(l_index) := l_MODIFIERS_rec;
898
899 IF l_MODIFIERS_rec.return_status = FND_API.G_RET_STS_ERROR THEN
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 END IF;
902
903 l_index := p_MODIFIERS_val_tbl.NEXT(l_index);
904
905 END LOOP;
906
907 -- Convert QUALIFIERS
908
909 x_QUALIFIERS_tbl := p_QUALIFIERS_tbl;
910
911 l_index := p_QUALIFIERS_val_tbl.FIRST;
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 -- Convert PRICING_ATTR
931
932 x_PRICING_ATTR_tbl := p_PRICING_ATTR_tbl;
933
934 l_index := p_PRICING_ATTR_val_tbl.FIRST;
935
936 WHILE l_index IS NOT NULL LOOP
937
938 l_PRICING_ATTR_rec := QP_Pricing_Attr_Util.Get_Ids
939 ( p_PRICING_ATTR_rec => p_PRICING_ATTR_tbl(l_index)
940 , p_PRICING_ATTR_val_rec => p_PRICING_ATTR_val_tbl(l_index)
941 );
942
943 x_PRICING_ATTR_tbl(l_index) := l_PRICING_ATTR_rec;
944
945 IF l_PRICING_ATTR_rec.return_status = FND_API.G_RET_STS_ERROR THEN
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 END IF;
948
949 l_index := p_PRICING_ATTR_val_tbl.NEXT(l_index);
950
951 END LOOP;
952
953 oe_debug_pub.add('END value_to_id in Public');
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_Modifiers_PUB;