[Home] [Help]
PACKAGE BODY: APPS.QP_LOCK_PRICELIST_PVT
Source
1 PACKAGE BODY QP_LOCK_PRICELIST_PVT AS
2 /* $Header: QPXLKPLB.pls 120.8 2006/05/16 23:57:20 srashmi ship $ */
3
4 /***************************************************************************
5 * Utility Procedures and Functions for Price Locking
6 ****************************************************************************/
7
8 --Function to query a Price List Line and if is a PBH also its child break lines
9 FUNCTION Query_Rows(p_list_header_id IN NUMBER,
10 p_list_line_id IN NUMBER)
11 RETURN QP_Price_List_PUB.Price_List_Line_Tbl_Type
12 IS
13 l_PRICE_LIST_LINE_rec QP_Price_List_PUB.Price_List_Line_Rec_Type;
14 l_PRICE_LIST_LINE_tbl QP_Price_List_PUB.Price_List_Line_Tbl_Type;
15
16 CURSOR price_list_line_cur(a_list_header_id NUMBER, a_list_line_id NUMBER)
17 IS
18 SELECT ACCRUAL_QTY
19 , ACCRUAL_UOM_CODE
20 , ARITHMETIC_OPERATOR
21 , ATTRIBUTE1
22 , ATTRIBUTE10
23 , ATTRIBUTE11
24 , ATTRIBUTE12
25 , ATTRIBUTE13
26 , ATTRIBUTE14
27 , ATTRIBUTE15
28 , ATTRIBUTE2
29 , ATTRIBUTE3
30 , ATTRIBUTE4
31 , ATTRIBUTE5
32 , ATTRIBUTE6
33 , ATTRIBUTE7
34 , ATTRIBUTE8
35 , ATTRIBUTE9
36 , AUTOMATIC_FLAG
37 , BASE_QTY
38 , BASE_UOM_CODE
39 , COMMENTS
40 , CONTEXT
41 , CREATED_BY
42 , CREATION_DATE
43 , EFFECTIVE_PERIOD_UOM
44 , END_DATE_ACTIVE
45 , ESTIM_ACCRUAL_RATE
46 , GENERATE_USING_FORMULA_ID
47 , INVENTORY_ITEM_ID
48 , LAST_UPDATED_BY
49 , LAST_UPDATE_DATE
50 , LAST_UPDATE_LOGIN
51 , LIST_HEADER_ID
52 , LIST_LINE_ID
53 , LIST_LINE_NO
54 , LIST_LINE_TYPE_CODE
55 , LIST_PRICE
56 , PRODUCT_PRECEDENCE
57 , MODIFIER_LEVEL_CODE
58 , NUMBER_EFFECTIVE_PERIODS
59 , OPERAND
60 , ORGANIZATION_ID
61 , OVERRIDE_FLAG
62 , PERCENT_PRICE
63 , PRICE_BREAK_TYPE_CODE
64 , PRICE_BY_FORMULA_ID
65 , PRIMARY_UOM_FLAG
66 , PRINT_ON_INVOICE_FLAG
67 , PROGRAM_APPLICATION_ID
68 , PROGRAM_ID
69 , PROGRAM_UPDATE_DATE
70 , REBATE_TRANSACTION_TYPE_CODE
71 , RELATED_ITEM_ID
72 , RELATIONSHIP_TYPE_ID
73 , REPRICE_FLAG
74 , REQUEST_ID
75 , REVISION
76 , REVISION_DATE
77 , REVISION_REASON_CODE
78 , START_DATE_ACTIVE
79 , SUBSTITUTION_ATTRIBUTE
80 , SUBSTITUTION_CONTEXT
81 , SUBSTITUTION_VALUE
82 , QUALIFICATION_IND
83 , RECURRING_VALUE -- block pricing
84 , CUSTOMER_ITEM_ID
85 , BREAK_UOM_CODE
86 , BREAK_UOM_CONTEXT
87 , BREAK_UOM_ATTRIBUTE
88 , CONTINUOUS_PRICE_BREAK_FLAG
89 FROM QP_LIST_LINES l
90 WHERE l.LIST_HEADER_ID = a_list_header_id
91 AND (l.LIST_LINE_ID = a_list_line_id OR
92 EXISTS (SELECT 'x'
93 FROM QP_RLTD_MODIFIERS
94 WHERE TO_RLTD_MODIFIER_ID = l.LIST_LINE_ID
95 AND FROM_RLTD_MODIFIER_ID = a_list_line_id)
96 );
97
98 BEGIN
99
100 --Loop over fetched records
101 IF (p_list_header_id IS NOT NULL) AND (p_list_line_id IS NOT NULL)
102 THEN
103
104 FOR l_rec IN price_list_line_cur (p_list_header_id, p_list_line_id)
105 LOOP
106 l_PRICE_LIST_LINE_rec.accrual_qty := l_rec.ACCRUAL_QTY;
107 l_PRICE_LIST_LINE_rec.accrual_uom_code := l_rec.ACCRUAL_UOM_CODE;
108 l_PRICE_LIST_LINE_rec.arithmetic_operator := l_rec.ARITHMETIC_OPERATOR;
109 l_PRICE_LIST_LINE_rec.attribute1 := l_rec.ATTRIBUTE1;
110 l_PRICE_LIST_LINE_rec.attribute10 := l_rec.ATTRIBUTE10;
111 l_PRICE_LIST_LINE_rec.attribute11 := l_rec.ATTRIBUTE11;
112 l_PRICE_LIST_LINE_rec.attribute12 := l_rec.ATTRIBUTE12;
113 l_PRICE_LIST_LINE_rec.attribute13 := l_rec.ATTRIBUTE13;
114 l_PRICE_LIST_LINE_rec.attribute14 := l_rec.ATTRIBUTE14;
115 l_PRICE_LIST_LINE_rec.attribute15 := l_rec.ATTRIBUTE15;
116 l_PRICE_LIST_LINE_rec.attribute2 := l_rec.ATTRIBUTE2;
117 l_PRICE_LIST_LINE_rec.attribute3 := l_rec.ATTRIBUTE3;
118 l_PRICE_LIST_LINE_rec.attribute4 := l_rec.ATTRIBUTE4;
119 l_PRICE_LIST_LINE_rec.attribute5 := l_rec.ATTRIBUTE5;
120 l_PRICE_LIST_LINE_rec.attribute6 := l_rec.ATTRIBUTE6;
121 l_PRICE_LIST_LINE_rec.attribute7 := l_rec.ATTRIBUTE7;
122 l_PRICE_LIST_LINE_rec.attribute8 := l_rec.ATTRIBUTE8;
123 l_PRICE_LIST_LINE_rec.attribute9 := l_rec.ATTRIBUTE9;
124 l_PRICE_LIST_LINE_rec.automatic_flag := l_rec.AUTOMATIC_FLAG;
125 l_PRICE_LIST_LINE_rec.base_qty := l_rec.BASE_QTY;
126 l_PRICE_LIST_LINE_rec.base_uom_code := l_rec.BASE_UOM_CODE;
127 l_PRICE_LIST_LINE_rec.comments := l_rec.COMMENTS;
128 l_PRICE_LIST_LINE_rec.context := l_rec.CONTEXT;
129 l_PRICE_LIST_LINE_rec.created_by := l_rec.CREATED_BY;
130 l_PRICE_LIST_LINE_rec.creation_date := l_rec.CREATION_DATE;
131 l_PRICE_LIST_LINE_rec.effective_period_uom := l_rec.EFFECTIVE_PERIOD_UOM;
132 l_PRICE_LIST_LINE_rec.end_date_active := l_rec.END_DATE_ACTIVE;
133 l_PRICE_LIST_LINE_rec.estim_accrual_rate := l_rec.ESTIM_ACCRUAL_RATE;
134 l_PRICE_LIST_LINE_rec.generate_using_formula_id :=
135 l_rec.GENERATE_USING_FORMULA_ID;
136 l_PRICE_LIST_LINE_rec.inventory_item_id := l_rec.INVENTORY_ITEM_ID;
137 l_PRICE_LIST_LINE_rec.last_updated_by := l_rec.LAST_UPDATED_BY;
138 l_PRICE_LIST_LINE_rec.last_update_date := l_rec.LAST_UPDATE_DATE;
139 l_PRICE_LIST_LINE_rec.last_update_login := l_rec.LAST_UPDATE_LOGIN;
140 l_PRICE_LIST_LINE_rec.list_header_id := l_rec.LIST_HEADER_ID;
141 l_PRICE_LIST_LINE_rec.list_line_id := l_rec.LIST_LINE_ID;
142 l_PRICE_LIST_LINE_rec.list_line_no := l_rec.LIST_LINE_NO;
143 l_PRICE_LIST_LINE_rec.list_line_type_code := l_rec.LIST_LINE_TYPE_CODE;
144 l_PRICE_LIST_LINE_rec.list_price := l_rec.LIST_PRICE;
145 l_PRICE_LIST_LINE_rec.product_precedence := l_rec.PRODUCT_PRECEDENCE;
146 l_PRICE_LIST_LINE_rec.modifier_level_code := l_rec.MODIFIER_LEVEL_CODE;
147 l_PRICE_LIST_LINE_rec.number_effective_periods :=
148 l_rec.NUMBER_EFFECTIVE_PERIODS;
149 l_PRICE_LIST_LINE_rec.operand := l_rec.OPERAND;
150 l_PRICE_LIST_LINE_rec.organization_id := l_rec.ORGANIZATION_ID;
151 l_PRICE_LIST_LINE_rec.override_flag := l_rec.OVERRIDE_FLAG;
152 l_PRICE_LIST_LINE_rec.percent_price := l_rec.PERCENT_PRICE;
153 l_PRICE_LIST_LINE_rec.price_break_type_code :=
154 l_rec.PRICE_BREAK_TYPE_CODE;
155 l_PRICE_LIST_LINE_rec.price_by_formula_id := l_rec.PRICE_BY_FORMULA_ID;
156 l_PRICE_LIST_LINE_rec.primary_uom_flag := l_rec.PRIMARY_UOM_FLAG;
157 l_PRICE_LIST_LINE_rec.print_on_invoice_flag :=
158 l_rec.PRINT_ON_INVOICE_FLAG;
159 l_PRICE_LIST_LINE_rec.program_application_id :=
160 l_rec.PROGRAM_APPLICATION_ID;
161 l_PRICE_LIST_LINE_rec.program_id := l_rec.PROGRAM_ID;
162 l_PRICE_LIST_LINE_rec.program_update_date := l_rec.PROGRAM_UPDATE_DATE;
163 l_PRICE_LIST_LINE_rec.rebate_trxn_type_code :=
164 l_rec.REBATE_TRANSACTION_TYPE_CODE;
165 l_PRICE_LIST_LINE_rec.related_item_id := l_rec.RELATED_ITEM_ID;
166 l_PRICE_LIST_LINE_rec.relationship_type_id := l_rec.RELATIONSHIP_TYPE_ID;
167 l_PRICE_LIST_LINE_rec.reprice_flag := l_rec.REPRICE_FLAG;
168 l_PRICE_LIST_LINE_rec.request_id := l_rec.REQUEST_ID;
169 l_PRICE_LIST_LINE_rec.revision := l_rec.REVISION;
170 l_PRICE_LIST_LINE_rec.revision_date := l_rec.REVISION_DATE;
171 l_PRICE_LIST_LINE_rec.revision_reason_code := l_rec.REVISION_REASON_CODE;
172 l_PRICE_LIST_LINE_rec.start_date_active := l_rec.START_DATE_ACTIVE;
173 l_PRICE_LIST_LINE_rec.substitution_attribute :=
174 l_rec.SUBSTITUTION_ATTRIBUTE;
175 l_PRICE_LIST_LINE_rec.substitution_context := l_rec.SUBSTITUTION_CONTEXT;
176 l_PRICE_LIST_LINE_rec.substitution_value := l_rec.SUBSTITUTION_VALUE;
177 l_PRICE_LIST_LINE_rec.qualification_ind := l_rec.QUALIFICATION_IND;
178 l_PRICE_LIST_LINE_rec.recurring_value := l_rec.RECURRING_VALUE;
179 l_PRICE_LIST_LINE_rec.customer_item_id := l_rec.CUSTOMER_ITEM_ID;
180 l_PRICE_LIST_LINE_rec.break_uom_code := l_rec.BREAK_UOM_CODE;
181 l_PRICE_LIST_LINE_rec.break_uom_context := l_rec.BREAK_UOM_CONTEXT;
182 l_PRICE_LIST_LINE_rec.break_uom_attribute := l_rec.BREAK_UOM_ATTRIBUTE;
183 l_PRICE_LIST_LINE_rec.continuous_price_break_flag := l_rec.CONTINUOUS_PRICE_BREAK_FLAG;
184
185 BEGIN
186 SELECT RLTD_MODIFIER_GRP_NO,
187 RLTD_MODIFIER_GRP_TYPE,
188 FROM_RLTD_MODIFIER_ID,
189 TO_RLTD_MODIFIER_ID,
190 RLTD_MODIFIER_ID
191 INTO l_PRICE_LIST_LINE_rec.rltd_modifier_group_no,
192 l_PRICE_LIST_LINE_rec.rltd_modifier_grp_type,
193 l_PRICE_LIST_LINE_rec.from_rltd_modifier_id,
194 l_PRICE_LIST_LINE_rec.to_rltd_modifier_id,
195 l_PRICE_LIST_LINE_rec.rltd_modifier_id
196 FROM QP_RLTD_MODIFIERS
197 WHERE TO_RLTD_MODIFIER_ID = l_rec.LIST_LINE_ID;
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 l_PRICE_LIST_LINE_rec.rltd_modifier_group_no := null;
202 l_PRICE_LIST_LINE_rec.rltd_modifier_grp_type := null;
203 l_PRICE_LIST_LINE_rec.from_rltd_modifier_id := null;
204 l_PRICE_LIST_LINE_rec.to_rltd_modifier_id := null;
205 l_PRICE_LIST_LINE_rec.rltd_modifier_id := null;
206 END;
207
208 l_PRICE_LIST_LINE_tbl(l_PRICE_LIST_LINE_tbl.COUNT+1) :=
209 l_PRICE_LIST_LINE_rec;
210
211 END LOOP;
212
213 END IF;
214
215 RETURN l_PRICE_LIST_LINE_tbl;
216
217 END Query_Rows;
218
219
220 PROCEDURE Get_Price_List
221 (p_list_header_id IN NUMBER,
222 p_list_line_id IN NUMBER,
223 x_PRICE_LIST_rec OUT NOCOPY /* file.sql.39 change */ QP_Price_List_PUB.Price_List_Rec_Type,
224 x_PRICE_LIST_LINE_tbl OUT NOCOPY /* file.sql.39 change */ QP_Price_List_PUB.Price_List_Line_Tbl_Type,
225 x_QUALIFIERS_tbl OUT NOCOPY /* file.sql.39 change */ Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type,
226 x_PRICING_ATTR_tbl OUT NOCOPY /* file.sql.39 change */ QP_Price_List_PUB.Pricing_Attr_Tbl_Type,
227 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
228 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
229 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
230 )
231 IS
232 l_PRICE_LIST_rec QP_Price_List_PUB.Price_List_Rec_Type;
233 l_PRICE_LIST_LINE_tbl QP_Price_List_PUB.Price_List_Line_Tbl_Type;
234 l_QUALIFIERS_tbl Qp_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
235 l_PRICING_ATTR_tbl QP_Price_List_PUB.Pricing_Attr_Tbl_Type;
236 l_x_PRICING_ATTR_tbl QP_Price_List_PUB.Pricing_Attr_Tbl_Type;
237
238 BEGIN
239 --Get Price List Header
240 l_PRICE_LIST_rec := QP_Price_List_Util.Query_Row
241 (p_list_header_id => p_list_header_id);
242
243 --Get Price List Lines
244 l_PRICE_LIST_LINE_tbl := Query_Rows
245 (p_list_header_id => l_PRICE_LIST_rec.list_header_id,
246 p_list_line_id => p_list_line_id);
247
248 --Loop over Price List Line's children
249 FOR i2 IN 1..l_PRICE_LIST_LINE_tbl.COUNT
250 LOOP
251 --Get Pricing Attributes
252 l_PRICING_ATTR_tbl := Qp_pll_pricing_attr_Util.Query_Rows
253 (p_list_line_id => l_PRICE_LIST_LINE_tbl(i2).list_line_id);
254
255 FOR i3 IN 1..l_PRICING_ATTR_tbl.COUNT
256 LOOP
257 l_PRICING_ATTR_tbl(i3).PRICE_LIST_LINE_Index := i2;
258 l_x_PRICING_ATTR_tbl
259 (l_x_PRICING_ATTR_tbl.COUNT + 1) := l_PRICING_ATTR_tbl(i3);
260 END LOOP;
261 END LOOP; --Loop over Price List Lines
262
263 --Get Qualifiers
264 l_QUALIFIERS_tbl := QP_Qualifiers_Util_Mod.Query_Rows
265 (p_list_header_id => l_PRICE_LIST_rec.list_header_id);
266
267 --Load out parameters
268 x_PRICE_LIST_rec := l_PRICE_LIST_rec;
269 x_PRICE_LIST_LINE_tbl := l_PRICE_LIST_LINE_tbl;
270 x_QUALIFIERS_tbl := l_QUALIFIERS_tbl;
271 x_PRICING_ATTR_tbl := l_x_PRICING_ATTR_tbl;
272
273 -- Set return status
274 x_return_status := FND_API.G_RET_STS_SUCCESS;
275
276 -- Get message count and data
277 fnd_msg_pub.Count_And_Get (p_count => x_msg_count,
278 p_data => x_msg_data);
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
283
284 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
285 fnd_log.STRING (fnd_log.level_procedure,
286 g_pkg_name || 'Get_Price_List',
287 'Others Exception: '||substr(sqlerrm, 1, 240));
288 END IF;
289
290 fnd_msg_pub.Add_Exc_Msg
291 ( G_PKG_NAME
292 , 'Get_Price_List'
293 , substr(sqlerrm, 1, 240)
294 );
295
296 --Get message count and data
297 fnd_msg_pub.Count_And_Get
298 ( p_count => x_msg_count
299 , p_data => x_msg_data
300 );
301
302 END Get_Price_List;
303
304
305
306 /***************************************************************************
307 * Lock_Price API
308 ***************************************************************************/
309 PROCEDURE Lock_Price (p_source_price_list_id IN NUMBER,
310 p_source_list_line_id IN NUMBER,
311 p_startup_mode IN VARCHAR2,
312 p_orig_system_header_ref IN VARCHAR2,
313 p_org_id IN NUMBER DEFAULT NULL,
314 p_commit IN VARCHAR2 DEFAULT 'F',
315 --added for OKS bug 4504825
316 x_locked_price_list_id OUT NOCOPY NUMBER,
317 x_locked_list_line_id OUT NOCOPY NUMBER,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2)
321 IS
322
323 l_source_system_code VARCHAR2(30);
324 l_locked_price_list_id NUMBER;
325 l_pte_code VARCHAR2(30);
326
327 p_control_rec QP_GLOBALS.Control_Rec_Type;
328
329 p_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
330 p_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
331 p_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
332
333 x_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
334 x_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
335 x_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
336 x_qualifiers_tbl QP_QUALIFIER_RULES_PUB.Qualifiers_Tbl_Type;
337
338 i number := 1;
339 ii number := 1;
340 j number := 1;
341 jj number := 1;
342 k number := 1;
343
344 l_name VARCHAR2(240);
345 l_blank NUMBER;
346 l_number_String VARCHAR2(240);
347
348 BEGIN
349
350 FND_PROFILE.GET('QP_SOURCE_SYSTEM_CODE',l_source_system_code);
351
352 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY',l_pte_code);
353
354 BEGIN
355 SELECT list_header_id
356 INTO l_locked_price_list_id
357 FROM qp_list_headers_b
358 WHERE locked_from_list_header_id = p_source_price_list_id
359 AND source_system_code = l_source_system_code
360 AND rownum = 1; --only one row is expected anyway.
361
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 l_locked_price_list_id := null;
365
366 END;
367
368 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
369 fnd_log.STRING (fnd_log.level_procedure,
370 g_pkg_name || '.Lock_Price',
371 'Entered procedure');
372 END IF;
373
374 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
375 fnd_log.STRING (fnd_log.level_procedure,
376 g_pkg_name || '.Lock_Price',
377 'p_source_price_list_id = '||p_source_price_list_id ||
378 ' and ' ||
379 'p_source_list_line_id = ' || p_source_list_line_id);
380 END IF;
381
382 --Get source price list and its child (all levels) records.
383 Get_Price_List(
384 p_list_header_id => p_source_price_list_id,
385 p_list_line_id => p_source_list_line_id,
386 x_PRICE_LIST_rec => x_price_list_rec,
387 x_PRICE_LIST_LINE_tbl => x_price_list_line_tbl,
388 x_QUALIFIERS_tbl => x_qualifiers_tbl,
389 x_PRICING_ATTR_tbl => x_pricing_attr_tbl,
390 x_return_status => x_return_status,
391 x_msg_count => x_msg_count,
392 x_msg_data => x_msg_data);
393
394 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
395 fnd_log.STRING (fnd_log.level_procedure,
396 g_pkg_name || '.Lock_Price',
397 'l_locked_price_list_id = '|| l_locked_price_list_id);
398 END IF;
399
400 IF l_locked_price_list_id IS NULL THEN
401 --Create new locked_price_list, line, etc.
402
403 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
404 fnd_log.STRING (fnd_log.level_procedure,
405 g_pkg_name || '.Lock_Price',
406 'l_locked_price_list_id is null ');
407 END IF;
408
409 --While copying the output records of the Get_Price_List call (which
410 --correspond to the source price list) to the input records for the
411 --Process_Price_List procedure call, retain only the source price list
412 --line and its children. Also, the source pricelist's qualifiers should
413 --not be copied since the the qualifiers won't be relevant to the locked
414 --price list and line.
415
416
417 --Copy the pricelist header record to the input record structure and set
418 --the appropriate column values.
419 p_price_list_rec := x_price_list_rec;
420 p_price_list_rec.list_header_id := FND_API.G_MISS_NUM;
421
422
423 IF (instr (p_price_list_rec.name, l_source_system_code || ' LOCKED') <> 0)
424 --The source price list itself is a locked price list
425 THEN
426 l_name := replace(p_price_list_rec.name, l_source_system_code ||
427 ' LOCKED');
428 --Strip the prefix upto LOCKED, not including the number
429 --component, if any, or blank separator.
430
431 l_blank := instr(l_name, ' '); --Get position of blank separator
432 --expected after the number component of LOCKED keyword.
433
434 IF l_blank = 1 THEN --blank occurs in first position of remaining
435 --string, so no number associated with 'LOCKED'
436 p_price_list_rec.name := l_source_system_code || ' LOCKED2 ' ||
437 substr(l_name, 2); --Start the numbering from 2
438
439 ELSIF l_blank > 1 THEN --A number component exists in prefix of a
440 --previously locked price list.
441 l_number_string := Substr(l_name, 1, l_blank - 1);
442 BEGIN
443 p_price_list_rec.name := l_source_system_code || ' LOCKED' ||
444 to_char(to_number(l_number_string) + 1) ||
445 ' ' || substr(l_name, l_blank + 1);
446 --Increase the number component in the prefix of
447 --locked price list by 1
448 EXCEPTION
449 WHEN OTHERS THEN -- Such as invalid number error due to
450 -- non-numeric chars present instead of the
451 -- expected number in a previously locked PL
452 p_price_list_rec.name := l_source_system_code || ' LOCKED ' ||
453 p_price_list_rec.name;
454 END;
455
456 ELSIF l_blank = 0 THEN --Blank separator not found, name does not follow
457 --naming convention of a previously locked price
458 --list.
459 p_price_list_rec.name := l_source_system_code || ' LOCKED ' ||
460 p_price_list_rec.name;
461 END IF;
462
463 ELSE -- Source price list is not a locked pricelist
464 p_price_list_rec.name := l_source_system_code || ' LOCKED ' ||
465 p_price_list_rec.name;
466 END IF;
467
468 p_price_list_rec.source_system_code := l_source_system_code;
469 p_price_list_rec.locked_from_list_header_id := p_source_price_list_id;
470 p_price_list_rec.pte_code := l_pte_code;
471 p_price_list_rec.list_source_code := p_startup_mode;
472 p_price_list_rec.orig_system_header_ref := p_orig_system_header_ref;
473 p_price_list_rec.start_date_active := null; --OKS requirement
474 p_price_list_rec.end_date_active := null; --OKS requirement
475 --added for MOAC
476 p_price_list_rec.org_id := nvl(p_org_id, QP_UTIL.get_org_id);
477 p_price_list_rec.global_flag := 'N'; --per OKS comments on bug 4725283,
478 --global_flag should be 'N'
479 p_price_list_rec.operation := QP_GLOBALS.G_OPR_CREATE;
480
481
482 --Copy the source list_line and its attributes to input record structures
483 --and set the appropriate column values.
484 FOR i IN x_price_list_line_tbl.FIRST..x_price_list_line_tbl.LAST
485 LOOP
486
487 IF x_price_list_line_tbl(i).list_line_id = p_source_list_line_id THEN
488
489 --We use index=1 (ii=1 at this point) to store the list line and
490 --then exit since there can only be one line with list_line_id =
491 --p_source_list_line_id.
492 p_price_list_line_tbl(ii) := x_price_list_line_tbl(i);
493 p_price_list_line_tbl(ii).operation := QP_GLOBALS.G_OPR_CREATE;
494 p_price_list_line_tbl(ii).list_line_id := FND_API.G_MISS_NUM;
495 p_price_list_line_tbl(ii).list_line_no := FND_API.G_MISS_CHAR;
496 p_price_list_line_tbl(ii).list_header_id := FND_API.G_MISS_NUM;
497 p_price_list_line_tbl(ii).start_date_active := null; --OKS requirement
498 p_price_list_line_tbl(ii).end_date_active := null; --OKS requirement
499
500
501 --For the list line whose id matches p_source_list_line_id, copy the
502 --pricing attributes.
503 FOR j IN x_pricing_attr_tbl.FIRST..x_pricing_attr_tbl.LAST
504 LOOP
505 IF x_pricing_attr_tbl(j).price_list_line_index = i AND
506 ((x_pricing_attr_tbl(j).pricing_attribute_context IS NULL) OR
507 NOT (x_pricing_attr_tbl(j).pricing_attribute_context = 'QP_INTERNAL'
508 AND
509 x_pricing_attr_tbl(j).pricing_attribute = 'PRICING_ATTRIBUTE1'))
510 --Only copy pricing attributes other than the 'List Line Id' attribute
511 THEN
512 --We store the pricing attributes for the source list line
513 --in a sequential manner, set the price_list_line_index to 1 since
514 --there will only be one locked list line id and increment jj.
515 p_pricing_attr_tbl(jj) := x_pricing_attr_tbl(j);
516 p_pricing_attr_tbl(jj).price_list_line_index := ii; -- ii = 1 now
517 p_pricing_attr_tbl(jj).operation := QP_GLOBALS.G_OPR_CREATE;
518 p_pricing_attr_tbl(jj).pricing_attribute_id := FND_API.G_MISS_NUM;
519 p_pricing_attr_tbl(jj).list_line_id := FND_API.G_MISS_NUM;
520 p_pricing_attr_tbl(jj).list_header_id := FND_API.G_MISS_NUM;
521 jj := jj + 1;
522 END IF;
523 END LOOP;--Loop to copy attributes of p_source_list_line_id
524
525 ii := ii + 1;
526 exit; --Exit loop after processing the list line and pricing attributes
527 --corresponding to the p_source_list_line_id
528
529 END IF; --If x_price_list_line_tbl(i).list_line_id=p_source_list_line_id
530
531 END LOOP; --Loop to copy p_source_list_line_id and its attributes
532
533
534 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
535 fnd_log.STRING (fnd_log.level_procedure,
536 g_pkg_name || '.Lock_Price',
537 'After loop to copy p_source_list_line_id and attrs');
538 END IF;
539
540
541 --If the p_source_list_line_id happens to be a PBH(price break header) type
542 --of list line, then copy the price break child lines and their attributes.
543 FOR i IN x_price_list_line_tbl.FIRST..x_price_list_line_tbl.LAST
544 LOOP
545
546 IF x_price_list_line_tbl(i).from_rltd_modifier_id = p_source_list_line_id
547 THEN
548 p_price_list_line_tbl(ii) := x_price_list_line_tbl(i);
549 p_price_list_line_tbl(ii).operation := QP_GLOBALS.G_OPR_CREATE;
550 p_price_list_line_tbl(ii).price_break_header_index := 1;
551 --since if p_source_list_line_id was a PBH then it
552 --would have an index of 1 in the input plsql table.
553 p_price_list_line_tbl(ii).list_line_id := FND_API.G_MISS_NUM;
554 p_price_list_line_tbl(ii).list_line_no := FND_API.G_MISS_CHAR;
555 p_price_list_line_tbl(ii).list_header_id := FND_API.G_MISS_NUM;
556 p_price_list_line_tbl(ii).from_rltd_modifier_id := FND_API.G_MISS_NUM;
557 p_price_list_line_tbl(ii).start_date_active := null; --OKS requirement
558 p_price_list_line_tbl(ii).end_date_active := null; --OKS requirement
559
560 --For the price break child lines of p_source_list_line_id, copy the
561 --pricing attributes.
562 FOR j IN x_pricing_attr_tbl.FIRST..x_pricing_attr_tbl.LAST
563 LOOP
564 IF x_pricing_attr_tbl(j).price_list_line_index = i THEN
565 --We store the pricing attributes for the child break lines of
566 --source list line in a sequential manner, set the
567 --price_list_line_index appropriately and increment jj.
568 p_pricing_attr_tbl(jj) := x_pricing_attr_tbl(j);
569 p_pricing_attr_tbl(jj).price_list_line_index := ii;
570 p_pricing_attr_tbl(jj).operation := QP_GLOBALS.G_OPR_CREATE;
571 p_pricing_attr_tbl(jj).pricing_attribute_id := FND_API.G_MISS_NUM;
572 p_pricing_attr_tbl(jj).list_line_id := FND_API.G_MISS_NUM;
573 p_pricing_attr_tbl(jj).list_header_id := FND_API.G_MISS_NUM;
574 jj := jj + 1;
575 END IF;
576 END LOOP; --loop to copy pricing attributes of child break line
577
578 ii := ii + 1;
579
580 END IF; --If list_line is a child of the source list line id
581
582 END LOOP; --Loop to copy any price break child lines and its attributes
583
584 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
585 fnd_log.STRING (fnd_log.level_procedure,
586 g_pkg_name || '.Lock_Price',
587 'After loop to copy price break child lines and attrs');
588 END IF;
589
590 --Set control flags
591 p_control_rec.controlled_operation := TRUE;
592
593 -- Instruct API to retain its caches
594 p_control_rec.clear_api_cache := FALSE;
595 p_control_rec.clear_api_requests := FALSE;
596
597 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
598 fnd_log.STRING (fnd_log.level_procedure,
599 g_pkg_name || '.Lock_Price',
600 'Before Process_Price_List 1');
601 END IF;
602
603 --Create locked price list
604 QP_LIST_HEADERS_PVT.Process_Price_List
605 ( p_api_version_number => 1
606 , p_init_msg_list => FND_API.G_FALSE
607 , p_commit => FND_API.G_FALSE
608 , p_control_rec => p_control_rec
609 , x_return_status => x_return_status
610 , x_msg_count => x_msg_count
611 , x_msg_data => x_msg_data
612 , p_PRICE_LIST_rec => p_price_list_rec
613 , p_PRICE_LIST_LINE_tbl => p_price_list_line_tbl
614 , p_PRICING_ATTR_tbl => p_pricing_attr_tbl
615 , x_PRICE_LIST_rec => x_price_list_rec
616 , x_PRICE_LIST_LINE_tbl => x_price_list_line_tbl
617 , x_QUALIFIERS_tbl => x_qualifiers_tbl
618 , x_PRICING_ATTR_tbl => x_pricing_attr_tbl
619 );
620
621 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
622 fnd_log.STRING (fnd_log.level_procedure,
623 g_pkg_name || '.Lock_Price',
624 'After Process_Price_List 1');
625 END IF;
626
627 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
628 fnd_log.STRING (fnd_log.level_procedure,
629 g_pkg_name || '.Lock_Price',
630 'x_return_status = ' || x_return_status);
631 END IF;
632
633 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
634 fnd_log.STRING (fnd_log.level_procedure,
635 g_pkg_name || '.Lock_Price',
636 'x_return_status = ' || x_msg_data);
637 END IF;
638
639 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
641 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
642 RAISE FND_API.G_EXC_ERROR;
643 END IF;
644
645 --Set output variables.
646 x_locked_price_list_id := x_price_list_rec.list_header_id;
647 x_locked_list_line_id := x_price_list_line_tbl(1).list_line_id;
648
649 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
650 fnd_log.STRING (fnd_log.level_procedure,
651 g_pkg_name || '.Lock_Price',
652 'x_locked_price_list_id = ' || x_locked_price_list_id ||
653 'and ' ||
654 'x_locked_list_line_id = ' || x_locked_list_line_id);
655 END IF;
656
657 --Call Process_Price_List API again to add list_line_id as a
658 --pricing attribute to the list_line_id locked from p_source_list_line_id
659
660 --Clear the price list line and pricing attributes plsql tables.
661 p_pricing_attr_tbl.delete;
662
663 --Populate one pricing attribute record for the LIST_LINE_ID attribute with
664 --appropriate values.
665 p_pricing_attr_tbl(1).pricing_attribute_id := FND_API.G_MISS_NUM;
666 p_pricing_attr_tbl(1).list_line_id := x_price_list_line_tbl(1).list_line_id;
667 p_pricing_attr_tbl(1).list_header_id :=
668 x_price_list_line_tbl(1).list_header_id;
669 p_pricing_attr_tbl(1).product_attribute :=
670 x_pricing_attr_tbl(1).product_attribute;
671 p_pricing_attr_tbl(1).product_attr_value :=
672 x_pricing_attr_tbl(1).product_attr_value;
673 p_pricing_attr_tbl(1).product_uom_code :=
674 x_pricing_attr_tbl(1).product_uom_code;
675 p_pricing_attr_tbl(1).pricing_attribute_context := 'QP_INTERNAL';
676 p_pricing_attr_tbl(1).pricing_attribute := 'PRICING_ATTRIBUTE1';
677 --Above context and attribute combination is for pricing attr LIST_LINE_ID.
678 p_pricing_attr_tbl(1).pricing_attr_value_from :=
679 to_char(x_price_list_line_tbl(1).list_line_id);
680 p_pricing_attr_tbl(1).comparison_operator_code := '=';
681 p_pricing_attr_tbl(1).excluder_flag := 'N';
682 p_pricing_attr_tbl(1).price_list_line_index := 1;
683 p_pricing_attr_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
684
685 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
686 fnd_log.STRING (fnd_log.level_procedure,
687 g_pkg_name || '.Lock_Price',
688 'Before Process_Price_List 2');
689 END IF;
690
691 --Add List Line Id as a pricing attribute to locked price list line.
692 QP_LIST_HEADERS_PVT.Process_Price_List
693 ( p_api_version_number => 1
694 , p_init_msg_list => FND_API.G_FALSE
695 , p_commit => FND_API.G_FALSE
696 , p_control_rec => p_control_rec
697 , x_return_status => x_return_status
698 , x_msg_count => x_msg_count
699 , x_msg_data => x_msg_data
700 , p_PRICING_ATTR_tbl => p_pricing_attr_tbl
701 , x_PRICE_LIST_rec => x_price_list_rec
702 , x_PRICE_LIST_LINE_tbl => x_price_list_line_tbl
703 , x_QUALIFIERS_tbl => x_qualifiers_tbl
704 , x_PRICING_ATTR_tbl => x_pricing_attr_tbl
705 );
706
707 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
708 fnd_log.STRING (fnd_log.level_procedure,
709 g_pkg_name || '.Lock_Price',
710 'After Process_Price_List 2');
711 END IF;
712
713 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
714 fnd_log.STRING (fnd_log.level_procedure,
715 g_pkg_name || '.Lock_Price',
716 'x_return_status = ' || x_return_status);
717 END IF;
718
719 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
720 fnd_log.STRING (fnd_log.level_procedure,
721 g_pkg_name || '.Lock_Price',
722 'x_return_status = ' || x_msg_data);
723 END IF;
724
725 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
728 RAISE FND_API.G_EXC_ERROR;
729 END IF;
730
731 ELSE --l_locked_price_list_id is not null
732
733 --Use existing locked_price_list to create locked line, etc.
734
735 --While copying the output records of the Get_Price_List call (which
736 --correspond to the source price list) to the input records for the
737 --Process_Price_List procedure call, retain only the source price list
738 --line and its children.
739
740
741 --Copy the source list_line and its attributes to input record structures
742 --and set the appropriate column values.
743 FOR i IN x_price_list_line_tbl.FIRST..x_price_list_line_tbl.LAST
744 LOOP
745
746 IF x_price_list_line_tbl(i).list_line_id = p_source_list_line_id THEN
747
748 --We use index=1 (ii=1 at this point) to store the list line and
749 --then exit since there can only be one line with list_line_id =
750 --p_source_list_line_id.
751 p_price_list_line_tbl(ii) := x_price_list_line_tbl(i);
752 p_price_list_line_tbl(ii).operation := QP_GLOBALS.G_OPR_CREATE;
753 p_price_list_line_tbl(ii).list_line_id := FND_API.G_MISS_NUM;
754 p_price_list_line_tbl(ii).list_line_no := FND_API.G_MISS_CHAR;
755 p_price_list_line_tbl(ii).list_header_id := l_locked_price_list_id;
756 p_price_list_line_tbl(ii).start_date_active := null; --OKS requirement
757 p_price_list_line_tbl(ii).end_date_active := null; --OKS requirement
758
759 --For the list line whose id matches p_source_list_line_id, copy the
760 --pricing attributes.
761 FOR j IN x_pricing_attr_tbl.FIRST..x_pricing_attr_tbl.LAST
762 LOOP
763 IF x_pricing_attr_tbl(j).price_list_line_index = i AND
764 ((x_pricing_attr_tbl(j).pricing_attribute_context IS NULL) OR
765 NOT (x_pricing_attr_tbl(j).pricing_attribute_context = 'QP_INTERNAL'
766 AND
767 x_pricing_attr_tbl(j).pricing_attribute = 'PRICING_ATTRIBUTE1'))
768 --Only copy pricing attributes other than the 'List Line Id' attribute
769 THEN
770 --We store the pricing attributes for the source list line
771 --in a sequential manner, set the price_list_line_index to 1 since
772 --there will only be one locked list line id and increment jj.
773 p_pricing_attr_tbl(jj) := x_pricing_attr_tbl(j);
774 p_pricing_attr_tbl(jj).price_list_line_index := ii; -- ii = 1 now
775 p_pricing_attr_tbl(jj).operation := QP_GLOBALS.G_OPR_CREATE;
776 p_pricing_attr_tbl(jj).pricing_attribute_id := FND_API.G_MISS_NUM;
777 p_pricing_attr_tbl(jj).list_line_id := FND_API.G_MISS_NUM;
778 p_pricing_attr_tbl(jj).list_header_id := l_locked_price_list_id;
779 jj := jj + 1;
780 END IF;
781 END LOOP;--Loop to copy attributes of p_source_list_line_id
782
783 ii := ii + 1;
784 exit; --Exit loop after processing the list line and pricing attributes
785 --corresponding to the p_source_list_line_id
786
787 END IF; --If x_price_list_line_tbl(i).list_line_id=p_source_list_line_id
788
789 END LOOP; --Loop to copy p_source_list_line_id and its attributes
790
791
792
793 --If the p_source_list_line_id happens to be a PBH(price break header) type
794 --of list line, then copy the price break child lines and their attributes.
795 FOR i IN x_price_list_line_tbl.FIRST..x_price_list_line_tbl.LAST
796 LOOP
797
798 IF x_price_list_line_tbl(i).from_rltd_modifier_id = p_source_list_line_id
799 THEN
800 p_price_list_line_tbl(ii) := x_price_list_line_tbl(i);
801 p_price_list_line_tbl(ii).operation := QP_GLOBALS.G_OPR_CREATE;
802 p_price_list_line_tbl(ii).price_break_header_index := 1;
803 --since if p_source_list_line_id was a PBH then it
804 --would have an index of 1 in the input plsql table.
805 p_price_list_line_tbl(ii).list_line_id := FND_API.G_MISS_NUM;
806 p_price_list_line_tbl(ii).list_line_no := FND_API.G_MISS_CHAR;
807 p_price_list_line_tbl(ii).from_rltd_modifier_id := FND_API.G_MISS_NUM;
808 p_price_list_line_tbl(ii).list_header_id := l_locked_price_list_id;
809 p_price_list_line_tbl(ii).start_date_active := null; --OKS requirement
810 p_price_list_line_tbl(ii).end_date_active := null; --OKS requirement
811
812 --For the price break child lines of p_source_list_line_id, copy the
813 --pricing attributes.
814 FOR j IN x_pricing_attr_tbl.FIRST..x_pricing_attr_tbl.LAST
815 LOOP
816 IF x_pricing_attr_tbl(j).price_list_line_index = i THEN
817 --We store the pricing attributes for the child break lines of
818 --source list line in a sequential manner, set the
819 --price_list_line_index appropriately and increment jj.
820 p_pricing_attr_tbl(jj) := x_pricing_attr_tbl(j);
821 p_pricing_attr_tbl(jj).price_list_line_index := ii;
822 p_pricing_attr_tbl(jj).operation := QP_GLOBALS.G_OPR_CREATE;
823 p_pricing_attr_tbl(jj).pricing_attribute_id := FND_API.G_MISS_NUM;
824 p_pricing_attr_tbl(jj).list_line_id := FND_API.G_MISS_NUM;
825 p_pricing_attr_tbl(jj).list_header_id := l_locked_price_list_id;
826 jj := jj + 1;
827 END IF;
828 END LOOP; --loop to copy pricing attributes of child break line
829
830 ii := ii + 1;
831
832 END IF; --If list_line is a child of the source list line id
833
834 END LOOP; --Loop to copy any price break child lines and its attributes
835
836 --Set control flags
837 p_control_rec.controlled_operation := TRUE;
838
839 -- Instruct API to retain its caches
840 p_control_rec.clear_api_cache := FALSE;
841 p_control_rec.clear_api_requests := FALSE;
842
843 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
844 fnd_log.STRING (fnd_log.level_procedure,
845 g_pkg_name || '.Lock_Price',
846 'Before Process_Price_List 3');
847 END IF;
848
849 --Create locked price list
850 QP_LIST_HEADERS_PVT.Process_Price_List
851 ( p_api_version_number => 1
852 , p_init_msg_list => FND_API.G_FALSE
853 , p_commit => FND_API.G_FALSE
854 , p_control_rec => p_control_rec
855 , x_return_status => x_return_status
856 , x_msg_count => x_msg_count
857 , x_msg_data => x_msg_data
858 , p_PRICE_LIST_rec => p_price_list_rec
859 , p_PRICE_LIST_LINE_tbl => p_price_list_line_tbl
860 , p_PRICING_ATTR_tbl => p_pricing_attr_tbl
861 , x_PRICE_LIST_rec => x_price_list_rec
862 , x_PRICE_LIST_LINE_tbl => x_price_list_line_tbl
863 , x_QUALIFIERS_tbl => x_qualifiers_tbl
864 , x_PRICING_ATTR_tbl => x_pricing_attr_tbl
865 );
866
867 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
868 fnd_log.STRING (fnd_log.level_procedure,
869 g_pkg_name || '.Lock_Price',
870 'After Process_Price_List 3');
871 END IF;
872
873 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
874 fnd_log.STRING (fnd_log.level_procedure,
875 g_pkg_name || '.Lock_Price',
876 'x_return_status = ' || x_return_status);
877 END IF;
878
879 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
880 fnd_log.STRING (fnd_log.level_procedure,
881 g_pkg_name || '.Lock_Price',
882 'x_return_status = ' || x_msg_data);
883 END IF;
884
885 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
888 RAISE FND_API.G_EXC_ERROR;
889 END IF;
890
891 --Set output variables.
892 x_locked_price_list_id := l_locked_price_list_id;
893 x_locked_list_line_id := x_price_list_line_tbl(1).list_line_id;
894
895 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
896 fnd_log.STRING (fnd_log.level_procedure,
897 g_pkg_name || '.Lock_Price',
898 'x_locked_price_list_id = ' || x_locked_price_list_id ||
899 'and ' ||
900 'x_locked_list_line_id = ' || x_locked_list_line_id);
901 END IF;
902
903
904 --Call Process_Price_List API again to add list_line_id as a
905 --pricing attribute to the list_line_id locked from p_source_list_line_id
906
907 --Clear the price list line and pricing attributes plsql tables.
908 p_pricing_attr_tbl.delete;
909
910 --Populate one pricing attribute record for the LIST_LINE_ID attribute with
911 --appropriate values.
912 p_pricing_attr_tbl(1).pricing_attribute_id := FND_API.G_MISS_NUM;
913 p_pricing_attr_tbl(1).list_line_id := x_price_list_line_tbl(1).list_line_id;
914 p_pricing_attr_tbl(1).list_header_id :=
915 x_price_list_line_tbl(1).list_header_id;
916 p_pricing_attr_tbl(1).product_attribute :=
917 x_pricing_attr_tbl(1).product_attribute;
918 p_pricing_attr_tbl(1).product_attr_value :=
919 x_pricing_attr_tbl(1).product_attr_value;
920 p_pricing_attr_tbl(1).product_uom_code :=
921 x_pricing_attr_tbl(1).product_uom_code;
922 p_pricing_attr_tbl(1).pricing_attribute_context := 'QP_INTERNAL';
923 p_pricing_attr_tbl(1).pricing_attribute := 'PRICING_ATTRIBUTE1';
924 --Above context and attribute combination is for pricing attr LIST_LINE_ID.
925 p_pricing_attr_tbl(1).pricing_attr_value_from :=
926 to_char(x_price_list_line_tbl(1).list_line_id);
927 p_pricing_attr_tbl(1).comparison_operator_code := '=';
928 p_pricing_attr_tbl(1).excluder_flag := 'N';
929 p_pricing_attr_tbl(1).price_list_line_index := 1;
930 p_pricing_attr_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
931
932 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
933 fnd_log.STRING (fnd_log.level_procedure,
934 g_pkg_name || '.Lock_Price',
935 'Before Process_Price_List 4');
936 END IF;
937
938 --Add List Line Id as a pricing attribute to locked price list line.
939 QP_LIST_HEADERS_PVT.Process_Price_List
940 ( p_api_version_number => 1
941 , p_init_msg_list => FND_API.G_FALSE
942 , p_commit => FND_API.G_FALSE
943 , p_control_rec => p_control_rec
944 , x_return_status => x_return_status
945 , x_msg_count => x_msg_count
946 , x_msg_data => x_msg_data
947 , p_PRICING_ATTR_tbl => p_pricing_attr_tbl
948 , x_PRICE_LIST_rec => x_price_list_rec
949 , x_PRICE_LIST_LINE_tbl => x_price_list_line_tbl
950 , x_QUALIFIERS_tbl => x_qualifiers_tbl
951 , x_PRICING_ATTR_tbl => x_pricing_attr_tbl
952 );
953
954 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
955 fnd_log.STRING (fnd_log.level_procedure,
956 g_pkg_name || '.Lock_Price',
957 'After Process_Price_List 4');
958 END IF;
959
960 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
961 fnd_log.STRING (fnd_log.level_procedure,
962 g_pkg_name || '.Lock_Price',
963 'x_return_status = ' || x_return_status);
964 END IF;
965
966 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
967 fnd_log.STRING (fnd_log.level_procedure,
968 g_pkg_name || '.Lock_Price',
969 'x_return_status = ' || x_msg_data);
970 END IF;
971
972 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
975 RAISE FND_API.G_EXC_ERROR;
976 END IF;
977
978 END IF; --l_locked_price_list_id is null
979
980 IF p_commit = 'T' THEN --for true (OKS Bug 4504825)
981 commit; -- OKS requires newly created locked PL Line (and PL if applicable)
982 -- to be committed conditionally. When called from Group API, OKS
983 -- may pass 'T' or 'F', but when called from Price List Form which
984 -- is integrated with the OKS Contract Authoring form, p_commit='T'.
985 END IF;
986
987 EXCEPTION
988
989 WHEN FND_API.G_EXC_ERROR THEN
990
991 x_return_status := FND_API.G_RET_STS_ERROR;
992
993 -- Get message count and data
994
995 FND_MSG_PUB.Count_And_Get
996 ( p_count => x_msg_count
997 , p_data => x_msg_data
998 );
999
1000 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1001
1002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1003
1004 -- Get message count and data
1005
1006 FND_MSG_PUB.Count_And_Get
1007 ( p_count => x_msg_count
1008 , p_data => x_msg_data
1009 );
1010
1011 WHEN OTHERS THEN
1012
1013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1014
1015 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1016 fnd_log.STRING (fnd_log.level_procedure,
1017 g_pkg_name || '.Lock_Price',
1018 'Others Exception : ' || substr(sqlerrm, 1, 240));
1019 END IF;
1020
1021 FND_MSG_PUB.Add_Exc_Msg
1022 ( G_PKG_NAME
1023 , 'Lock_Price'
1024 , substr(sqlerrm, 1, 240)
1025 );
1026
1027 -- Get message count and data
1028
1029 FND_MSG_PUB.Count_And_Get
1030 ( p_count => x_msg_count
1031 , p_data => x_msg_data
1032 );
1033
1034 END Lock_Price;
1035
1036
1037 END QP_LOCK_PRICELIST_PVT;