[Home] [Help]
PACKAGE BODY: APPS.OZF_VOLUME_OFFER_ADJ
Source
1 PACKAGE BODY OZF_VOLUME_OFFER_ADJ AS
2 /* $Header: ozfvvadb.pls 120.15 2012/01/05 08:59:37 bkunjan ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_VOLUME_OFFER_ADJ
7 -- Purpose
8 --
9 -- History
10 -- Tue Mar 14 2006:4/33 PM RSSHARMA Created
11 -- Mon Apr 03 2006:1/27 PM RSSHARMA Fixed end date Query for end dating lines
12 -- Tue Aug 15 2006:3/26 PM RSSHARMA Fixed bug # 5468261. Fixed query in populate_dis_lines, to outer join qp_rltd_deal_lines
13 -- with ozf_qp_discounts. The default tier for price breaks starting with "0" does not correspong to any ozf_qp_discount.
14 -- This outer join will enable selection of the default tier starting with "0".
15 -- Tue Aug 15 2006:6/17 PM RSSHARMA Contd # 5468261 fixes. Changed order of calling zero discounts. Also DO not create
16 -- relations for default line.
17 -- NOTE
18 -- Aug 11 2010 nirprasa bug 9900749: ADDING A NEW ITEM TO A VOLUME OFFER CAUSES THE BENEFICIARY FEATURE
19 -- NOT TO WORK
20 -- 8/11/2011 nepanda Fix for bug 12975394 - idsm backdating adjustment created a duplicate accrual
21 -- End of Comments
22 -- ===============================================================
23 PROCEDURE update_vo_tier
24 (
25 x_return_status OUT NOCOPY VARCHAR2
26 , x_msg_count OUT NOCOPY NUMBER
27 , x_msg_data OUT NOCOPY VARCHAR2
28 , p_offerDiscountLineId IN NUMBER
29 , p_offerAdjustmentId IN NUMBER
30 )
31 IS
32 l_vo_disc_rec OZF_Volume_Offer_disc_PVT.vo_disc_rec_type;
33 CURSOR c_discountLine(cp_offerDiscountLineId NUMBER, cp_offerAdjustmentId NUMBER) IS
34 SELECT a.object_version_number
35 , a.offer_id
36 , a.parent_discount_line_id
37 , b.modified_discount
38 FROM ozf_offer_discount_lines a, ozf_offer_adjustment_tiers b
39 WHERE a.offer_discount_line_id = b.offer_discount_line_id
40 AND b.offer_discount_line_id = cp_offerDiscountLineId
41 AND b.offer_adjustment_id = cp_offerAdjustmentId;
42 BEGIN
43 /*
44 initialize return rec
45 initialize record
46 populate the record
47 update VO discount tiers
48 */
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50 FOR l_discountLine IN c_discountLine(cp_offerDiscountLineId => p_offerDiscountLineId , cp_offerAdjustmentId => p_offerAdjustmentId )
51 LOOP
52 l_vo_disc_rec := null;
53 l_vo_disc_rec.offer_discount_line_id := p_offerDiscountLineId;
54 l_vo_disc_rec.offer_id := l_discountLine.offer_id;
55 l_vo_disc_rec.object_version_number := l_discountLine.object_version_number;
56 l_vo_disc_rec.discount := l_discountLine.modified_discount;
57 END LOOP;
58 OZF_Volume_Offer_disc_PVT.Update_vo_discount(
59 p_api_version_number => 1.0
60 , p_init_msg_list => FND_API.G_FALSE
61 , p_commit => FND_API.G_FALSE
62 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
63 , x_return_status => x_return_status
64 , x_msg_count => x_msg_count
65 , x_msg_data => x_msg_data
66 , p_vo_disc_rec => l_vo_disc_rec
67 );
68 END update_vo_tier;
69
70 PROCEDURE update_adj_vo_tiers
71 (
72 x_return_status OUT NOCOPY VARCHAR2
73 ,x_msg_count OUT NOCOPY NUMBER
74 ,x_msg_data OUT NOCOPY VARCHAR2
75 ,p_offerAdjustmentId IN NUMBER
76 )
77 IS
78 CURSOR c_adjustmentTiers(cp_offerAdjustmentId NUMBER)
79 IS
80 SELECT offer_discount_line_id
81 FROM ozf_offer_adjustment_tiers
82 WHERE offer_adjustment_id = cp_offerAdjustmentId;
83 BEGIN
84 -- initialize return status
85 -- loop thru. all adjustment tiers
86 -- for each adjustment tier update the discount line and handle exception
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88 FOR l_adjustmentTiers IN c_adjustmentTiers(cp_offerAdjustmentId => p_offerAdjustmentId)
89 LOOP
90 update_vo_tier
91 (
92 x_return_status => x_return_status
93 , x_msg_count => x_msg_count
94 , x_msg_data => x_msg_data
95 , p_offerDiscountLineId => l_adjustmentTiers.offer_discount_line_id
96 , p_offerAdjustmentId => p_offerAdjustmentId
97 );
98 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
99 RAISE FND_API.G_EXC_ERROR;
100 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103 END LOOP;
104 END update_adj_vo_tiers;
105
106 PROCEDURE end_qp_line
107 (
108 x_return_status OUT NOCOPY VARCHAR2
109 ,x_msg_count OUT NOCOPY NUMBER
110 ,x_msg_data OUT NOCOPY VARCHAR2
111 ,p_listLineId IN NUMBER
112 ,p_offerAdjustmentId IN NUMBER
113 )
114 IS
115 CURSOR c_listLines(cp_listLineId NUMBER)
116 IS
117 SELECT list_line_id, list_header_id , list_line_type_code, price_break_type_code, list_line_no
118 FROM qp_list_lines
119 WHERE list_line_id = cp_listLineId;
120 /*
121 Cursor works fine but the implementation is not satisfying, need to relook into the end date query
122 */
123 CURSOR c_endDate(cp_offerAdjustmentId NUMBER , cp_listLineId NUMBER)
124 IS
125 SELECT
126 decode(c.start_date_active
127 , null
128 , decode(d.start_date_active, null, a.effective_date -1 , decode(greatest(d.start_date_active,a.effective_date - 1) , d.start_date_active, d.start_date_active, a.effective_date -1 ))
129 , decode(greatest(c.start_date_active, a.effective_date -1 ), c.start_date_active , c.start_date_active ,a.effective_date - 1)
130 ) endDate
131 --greatest(nvl(d.start_date_active, a.effective_date - 5 ),nvl(d.start_date_active, a.effective_date - 5 ), a.effective_date - 1 ) endDate
132 , b.offer_type, b.qp_list_header_id
133 , c.start_date_active lineStartDate, d.start_date_active headerStartDate, a.effective_date adjEffectiveDate, b.custom_setup_id
134 FROM ozf_offer_adjustments_b a, ozf_offers b , qp_list_lines c , qp_list_headers_b d
135 WHERE
136 a.list_header_id = b.qp_list_header_id
137 AND b.qp_list_header_id = c.list_header_id
138 AND c.list_header_id = d.list_header_id
139 AND offer_adjustment_id = cp_offerAdjustmentId
140 AND c.list_line_id = cp_listLineId;
141 l_endDate c_endDate%ROWTYPE;
142
143 x_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
144 l_modifier_line_tbl qp_modifiers_pub.Modifiers_Tbl_Type;
145 l_errorLoc NUMBER;
146 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
147 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
148 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
149 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
150 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
151 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
152 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
153 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
154
155 -- Fix for bug 12975394
156 l_qualifiers_tbl OZF_OFFER_PVT.QUALIFIERS_TBL_TYPE;
157 x_error_location NUMBER;
158 x_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
159 l_request_line_id NUMBER;
160
161 BEGIN
162 x_return_status := FND_API.G_RET_STS_SUCCESS;
163 -- retrieve end_date into local record
164 -- initialize record
165 -- populate record
166 -- call process_qp_list_lines
167 --l_modifier_line_tbl.delete;
168 OPEN c_endDate(cp_offerAdjustmentId => p_offerAdjustmentId , cp_listLineId => p_listLineId);
169 FETCH c_endDate INTO l_endDate;
170 CLOSE c_endDate;
171 ozf_utility_pvt.debug_message('Dates are:end Date:'||l_endDate.endDate||':header start date:'||l_endDate.headerStartDate||':lineStartDate:'||l_endDate.lineStartDate);
172 FOR l_listLines IN c_listLines(cp_listLineId => p_listLineId)
173 LOOP
174
175 l_modifier_line_tbl(1).list_line_id := p_listLineId;
176 l_modifier_line_tbl(1).end_date_active := l_endDate.endDate;
177 l_modifier_line_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
178 l_modifier_line_tbl(1).list_line_type_code := l_listLines.list_line_type_code;
179 l_modifier_line_tbl(1).price_break_type_code:= l_listLines.price_break_type_code;
180 -- Fix for bug 12975394 :
181 -- Call process_market_qualifiers to add the qualifier to the line getting end dated and has the same end date
182 -- as that fo the new line's start date to avoid date overlap.
183 IF l_endDate.adjEffectiveDate = l_endDate.endDate THEN
184 l_qualifiers_tbl(1).operation := 'CREATE' ;
185 l_qualifiers_tbl(1).list_header_id := l_listLines.list_header_id;
186 l_qualifiers_tbl(1).list_line_id := p_listLineId;
187 l_qualifiers_tbl(1).qualifier_context := 'MODLIST' ;
188 l_qualifiers_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE10' ;
189 l_qualifiers_tbl(1).qualifier_attr_value := 'Y' ;
190 l_qualifiers_tbl(1).qualifier_attr_value_to := null ;
191 l_qualifiers_tbl(1).comparison_operator_code := '=' ;
192 l_qualifiers_tbl(1).qualifier_grouping_no := -1 ;
193 OZF_OFFER_PVT.process_market_qualifiers(
194 p_init_msg_list => FND_API.G_FALSE,
195 p_api_version => 1.0,
196 p_commit => FND_API.G_FALSE,
197 x_return_status => x_return_status,
198 x_msg_count => x_msg_count,
199 x_msg_data => x_msg_data,
200 p_qualifiers_tbl=> l_qualifiers_tbl,
201 x_error_location=> x_error_location,
202 x_qualifiers_tbl=> x_qualifiers_tbl
203 );
204 l_qualifiers_tbl.delete;
205 x_qualifiers_tbl.delete;
206 END IF;
207
208 --End date SDR Lines
209
210 ozf_utility_pvt.debug_message('l_endDate.custom_setup_id'||l_endDate.custom_setup_id);
211
212
213 IF l_endDate.custom_setup_id = 118 THEN
214
215 l_request_line_id := l_listLines.list_line_no;
216 ozf_utility_pvt.debug_message('l_request_line_id'|| l_request_line_id);
217
218
219 UPDATE ozf_sd_request_lines_all
220 SET end_date = l_endDate.endDate,
221 object_version_number = object_version_number + 1
222 WHERE request_line_id = l_request_line_id;
223
224 END IF;
225
226
227 END LOOP;
228 QP_Modifiers_PUB.process_modifiers(
229 p_api_version_number => 1.0,
230 p_init_msg_list => FND_API.G_FALSE,
231 p_return_values => FND_API.G_FALSE,
232 x_return_status => x_return_status,
233 x_msg_count => x_msg_count,
234 x_msg_data => x_msg_data,
235 p_modifiers_tbl => l_modifier_line_tbl,
236 x_modifier_list_rec => v_modifier_list_rec,
237 x_modifier_list_val_rec => v_modifier_list_val_rec,
238 x_modifiers_tbl => v_modifiers_tbl,
239 x_modifiers_val_tbl => v_modifiers_val_tbl,
240 x_qualifiers_tbl => v_qualifiers_tbl,
241 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
242 x_pricing_attr_tbl => v_pricing_attr_tbl,
243 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
244 );
245 END end_qp_line;
246
247 PROCEDURE end_date_qp_lines
248 (
249 x_return_status OUT NOCOPY VARCHAR2
250 ,x_msg_count OUT NOCOPY NUMBER
251 ,x_msg_data OUT NOCOPY VARCHAR2
252 ,p_offerAdjustmentId IN NUMBER
253 )
254 IS
255 CURSOR c_qpListLines(cp_offerAdjustmentId NUMBER)
256 IS
257 SELECT distinct parent_discount_line_id , c.list_line_id
258 FROM ozf_offer_adjustment_tiers a, ozf_offer_discount_lines b , ozf_qp_discounts c
259 WHERE a.offer_discount_line_id = b.offer_discount_line_id
260 AND b.parent_discount_line_id = c.offer_discount_line_id
261 AND a.offer_adjustment_id = cp_offerAdjustmentId;
262 BEGIN
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264 FOR l_qpListLines IN c_qpListLines(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
265 end_qp_line
266 (
267 x_return_status => x_return_status
268 ,x_msg_count => x_msg_count
269 ,x_msg_data => x_msg_data
270 ,p_listLineId => l_qpListLines.list_line_id
271 ,p_offerAdjustmentId => p_offerAdjustmentId
272 );
273 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
274 RAISE FND_API.G_EXC_ERROR;
275 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278 END LOOP;
279 END end_date_qp_lines;
280
281
282
283
284 PROCEDURE populate_discounts
285 (
286 x_modifiers_rec IN OUT NOCOPY Qp_Modifiers_Pub.modifiers_rec_type
287 , p_list_line_id IN NUMBER
288 )
289 IS
290 CURSOR c_discounts(cp_listLineId NUMBER) IS
291 SELECT
292 c.list_header_id
293 , c.arithmetic_operator
294 , c.operand
295 , c.list_line_id
296 , c.print_on_invoice_flag
297 , c.accrual_flag
298 , c.pricing_phase_id
299 , c.pricing_group_sequence
300 , c.incompatibility_grp_code
301 , c.product_precedence
302 , c.generate_using_formula_id
303 , c.price_by_formula_id
304 , c.context
305 , c.attribute1
306 , c.attribute2
307 , c.attribute3
308 , c.attribute4
309 , c.attribute5
310 , c.attribute6
311 , c.attribute7
312 , c.attribute8
313 , c.attribute9
314 , c.attribute10
315 , c.attribute11
316 , c.attribute12
317 , c.attribute13
318 , c.attribute14
319 , c.attribute15
320 , c.proration_type_code
321 , c.qualification_ind
322 , c.modifier_level_code
323 , c.automatic_flag
324 , c.override_flag
325 , c.price_break_type_code
326 , c.benefit_qty
327 , c.benefit_uom_code
328 , c.benefit_price_list_line_id
329 --, accum_context
330 , accum_attribute
331 --, accum_attr_run_src_flag
332 , list_line_type_code
333 FROM
334 qp_list_lines c
335 WHERE c.list_line_id = cp_listLineId;
336 i NUMBER := null;
337 l_modifiers_tbl Qp_Modifiers_Pub.modifiers_tbl_type;
338 BEGIN
339 --dbms_output.put_line('listLIneId1 is :'||p_list_line_id);
340 FOR l_discounts in c_discounts(cp_listLineId => p_list_line_id) LOOP
341 x_modifiers_rec.list_header_id := l_discounts.list_header_id;
342 x_modifiers_rec.list_line_type_code := l_discounts.list_line_type_code;
343 x_modifiers_rec.end_date_active := null;
344 x_modifiers_rec.arithmetic_operator := l_discounts.arithmetic_operator;
345 --dbms_output.put_line('arithmetic operator1 is :'||x_modifiers_rec.arithmetic_operator||' : '||l_discounts.arithmetic_operator);
346 x_modifiers_rec.price_break_type_code := l_discounts.price_break_type_code;
347 --dbms_output.put_line('Price break type code is :'||x_modifiers_rec.price_break_type_code||' : '||l_discounts.price_break_type_code);
348 ----------------------advanced options---------------------------------
349 x_modifiers_rec.print_on_invoice_flag := l_discounts.print_on_invoice_flag;
350 x_modifiers_rec.accrual_flag := l_discounts.accrual_flag;
351 x_modifiers_rec.pricing_phase_id := l_discounts.pricing_phase_id;
352 x_modifiers_rec.pricing_group_sequence := l_discounts.pricing_group_sequence;
353 x_modifiers_rec.incompatibility_grp_code := l_discounts.incompatibility_grp_code;
354 x_modifiers_rec.product_precedence := l_discounts.product_precedence;
355 x_modifiers_rec.proration_type_code := l_discounts.proration_type_code;
356 --------------------formulas------------------------------------------
357 x_modifiers_rec.price_by_formula_id := l_discounts.price_by_formula_id;
358 x_modifiers_rec.generate_using_formula_id:= l_discounts.generate_using_formula_id;
359 -------------------PG items-------------------------------------------
360 x_modifiers_rec.benefit_qty := l_discounts.benefit_qty;
361 x_modifiers_rec.benefit_uom_code := l_discounts.benefit_uom_code;
362 x_modifiers_rec.benefit_price_list_line_id:= l_discounts.benefit_price_list_line_id;
363 -------------------Flex Fields----------------------------------------
364 x_modifiers_rec.context := l_discounts.context;
365 x_modifiers_rec.attribute1 := l_discounts.attribute1;
366 x_modifiers_rec.attribute2 := l_discounts.attribute2;
367 x_modifiers_rec.attribute3 := l_discounts.attribute3;
368 x_modifiers_rec.attribute4 := l_discounts.attribute4;
369 x_modifiers_rec.attribute5 := l_discounts.attribute5;
370 x_modifiers_rec.attribute6 := l_discounts.attribute6;
371 x_modifiers_rec.attribute7 := l_discounts.attribute7;
372 x_modifiers_rec.attribute8 := l_discounts.attribute8;
373 x_modifiers_rec.attribute9 := l_discounts.attribute9;
374 x_modifiers_rec.attribute10 := l_discounts.attribute10;
375 x_modifiers_rec.attribute11 := l_discounts.attribute11;
376 x_modifiers_rec.attribute12 := l_discounts.attribute12;
377 x_modifiers_rec.attribute13 := l_discounts.attribute13;
378 x_modifiers_rec.attribute14 := l_discounts.attribute14;
379 x_modifiers_rec.attribute15 := l_discounts.attribute15;
380 x_modifiers_rec.modifier_level_code := l_discounts.modifier_level_code;
381 x_modifiers_rec.automatic_flag := l_discounts.automatic_flag;
382 x_modifiers_rec.override_flag := l_discounts.override_flag;
383 x_modifiers_rec.comments := p_list_line_id;
384 ---------------------Accumulation attributes ------------------------
385 x_modifiers_rec.accum_attribute := l_discounts.accum_attribute;
386 END LOOP;
387 END populate_discounts;
388
389 PROCEDURE populate_pricing_attributes
390 (
391 x_pricing_attr_tbl OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_tbl_type
392 , p_list_line_id IN NUMBER
393 , p_index IN NUMBER
394 )
395 IS
396 CURSOR c_pricingAttributes(cp_listLineId NUMBER) IS
397 SELECT
398 a.product_attribute_context
399 , a.product_attribute
400 , a.product_attr_value
401 , a.product_uom_code
402 , a.excluder_flag
403 , a.pricing_attr_value_from
404 , a.pricing_attr_value_to
405 , a.pricing_attribute_context
406 , a.pricing_attribute
407 , a.comparison_operator_code
408 FROM qp_pricing_attributes a
409 WHERE a.list_line_id = cp_listLineId;
410 i NUMBER := null;
411 BEGIN
412 --x_return_status := FND_API.G_RET_STS_SUCCESS;
413 x_pricing_attr_tbl.delete;
414 i := 1;
415 FOR l_pricingAttributes in c_pricingAttributes(cp_listLineId => p_list_line_id) LOOP
416 --dbms_output.put_line('ist line id :'||p_list_line_id||' :index :'||P_INDEX||' : Attr Value :'||l_pricingAttributes.product_attr_value||': Excluder :'||l_pricingAttributes.excluder_flag);
417 x_pricing_attr_tbl(i).product_attribute_context := l_pricingAttributes.product_attribute_context;
418 x_pricing_attr_tbl(i).product_attribute := l_pricingAttributes.product_attribute;
419 x_pricing_attr_tbl(i).product_attr_value := l_pricingAttributes.product_attr_value;
420 x_pricing_attr_tbl(i).product_uom_code := l_pricingAttributes.product_uom_code;
421 x_pricing_attr_tbl(i).excluder_flag := l_pricingAttributes.excluder_flag;
422 x_pricing_attr_tbl(i).pricing_attr_value_from := l_pricingAttributes.pricing_attr_value_from;
423 x_pricing_attr_tbl(i).pricing_attr_value_to := l_pricingAttributes.pricing_attr_value_to;
424 x_pricing_attr_tbl(i).pricing_attribute_context := l_pricingAttributes.pricing_attribute_context;
425 x_pricing_attr_tbl(i).pricing_attribute := l_pricingAttributes.pricing_attribute;
426 X_pricing_attr_tbl(i).operation := Qp_Globals.G_OPR_CREATE;
427 X_pricing_attr_tbl(i).comparison_operator_code := l_pricingAttributes.comparison_operator_code;
428 X_pricing_attr_tbl(i).modifiers_index := P_INDEX;
429 i := i + 1;
430 END LOOP;
431
432 END populate_pricing_attributes;
433
434 PROCEDURE populate_pbh_line
435 (
436 x_return_status OUT NOCOPY VARCHAR2
437 , x_msg_count OUT NOCOPY NUMBER
438 , x_msg_data OUT NOCOPY VARCHAR2
439 , p_listLineId IN NUMBER
440 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
441 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
442 )
443 IS
444 BEGIN
445 --dbms_output.put_line('IN populate pbh line');
446 x_return_status := FND_API.G_RET_STS_SUCCESS;
447 x_modifier_line_tbl.delete;
448 x_pricing_attr_tbl.delete;
449 x_modifier_line_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
450 x_modifier_line_tbl(1).list_line_type_code := 'PBH';
451 x_modifier_line_tbl(1).automatic_flag := 'Y';
452 --dbms_output.put_line('Before populate discounts');
453 populate_discounts
454 (
455 x_modifiers_rec => x_modifier_line_tbl(1)
456 , p_list_line_id => p_listLineId
457 );
458 --dbms_output.put_line('After populate discounts');
459 populate_pricing_attributes
460 (
461 x_pricing_attr_tbl => x_pricing_attr_tbl
462 , p_list_line_id => p_listLineId
463 , p_index => 1
464 );
465 END populate_pbh_line;
466
467 PROCEDURE merge_modifiers
468 (
469 p_to_modifier_line_tbl IN QP_MODIFIERS_PUB.Modifiers_Tbl_Type
470 , p_from_modifier_line_tbl IN QP_MODIFIERS_PUB.Modifiers_Tbl_Type
471 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
472 )
473 IS
474 BEGIN
475 x_modifier_line_tbl.delete;
476 for i in p_to_modifier_line_tbl.first .. p_to_modifier_line_tbl.last LOOP
477 IF p_to_modifier_line_tbl.exists(i) THEN
478 x_modifier_line_tbl(i) := p_to_modifier_line_tbl(i);
479 END IF;
480 END LOOP;
481 FOR i in p_from_modifier_line_tbl.first .. p_from_modifier_line_tbl.last LOOP
482 IF p_from_modifier_line_tbl.exists(i) THEN
483 x_modifier_line_tbl(x_modifier_line_tbl.count + 1) := p_from_modifier_line_tbl(i);
484 END IF;
485 END LOOP;
486 END merge_modifiers;
487
488 PROCEDURE merge_modifiers
489 (
490 px_to_modifier_line_tbl IN OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
491 , p_from_modifier_line_tbl IN QP_MODIFIERS_PUB.Modifiers_Tbl_Type
492 )
493 IS
494 BEGIN
495 --dbms_output.put_line('In Merge Modifiers:');
496 IF nvl(p_from_modifier_line_tbl.count,0) > 0 THEN
497 FOR i in p_from_modifier_line_tbl.first .. p_from_modifier_line_tbl.last LOOP
498 --dbms_output.put_line('Merge Modifiers:'||i);
499 IF p_from_modifier_line_tbl.exists(i) THEN
500 --dbms_output.put_line('Merge Modifiers:');
501 px_to_modifier_line_tbl(px_to_modifier_line_tbl.count + 1) := p_from_modifier_line_tbl(i);
502 END IF;
503 END LOOP;
504 END IF;
505 --dbms_output.put_line('End Merge modifiers');
506 END merge_modifiers;
507
508 PROCEDURE merge_pricing_attributes
509 (
510 p_to_pricing_attr_tbl IN QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
511 , p_from_pricing_attr_tbl IN QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
512 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
513 )
514 IS
515 BEGIN
516 x_pricing_attr_tbl.delete;
517 for i in p_to_pricing_attr_tbl.first .. p_to_pricing_attr_tbl.last LOOP
518 IF p_to_pricing_attr_tbl.exists(i) THEN
519 x_pricing_attr_tbl(i) := p_to_pricing_attr_tbl(i);
520 END IF;
521 END LOOP;
522 FOR i in p_from_pricing_attr_tbl.first .. p_from_pricing_attr_tbl.last LOOP
523 IF p_from_pricing_attr_tbl.exists(i) THEN
524 x_pricing_attr_tbl(x_pricing_attr_tbl.count + 1) := p_from_pricing_attr_tbl(i);
525 END IF;
526 END LOOP;
527 END merge_pricing_attributes;
528
529
530 PROCEDURE merge_pricing_attributes
531 (
532 px_to_pricing_attr_tbl IN OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
533 , p_from_pricing_attr_tbl IN QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
534 )
535 IS
536 BEGIN
537 IF nvl(p_from_pricing_attr_tbl.count,0) > 0 THEN
538 for i in p_from_pricing_attr_tbl.first .. p_from_pricing_attr_tbl.last LOOP
539 IF p_from_pricing_attr_tbl.exists(i) THEN
540 px_to_pricing_attr_tbl(px_to_pricing_attr_tbl.count+1) := p_from_pricing_attr_tbl(i);
541 END IF;
542 END LOOP;
543 END IF;
544 --dbms_output.put_line('end pricing attributes');
545 END merge_pricing_attributes;
546
547 PROCEDURE populate_dis_lines
548 (
549 x_return_status OUT NOCOPY VARCHAR2
550 , x_msg_count OUT NOCOPY NUMBER
551 , x_msg_data OUT NOCOPY VARCHAR2
552 , p_listLineId IN NUMBER
553 , p_offerAdjustmentId IN NUMBER
554 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
555 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
556 )
557 IS
558 CURSOR c_discountLines(cp_listLineId NUMBER , cp_offerAdjustmentId NUMBER)
559 IS
560 SELECT a.from_rltd_modifier_id, a.to_rltd_modifier_id, b.offer_discount_line_id , nvl(c.modified_discount,d.operand) discount , d.arithmetic_operator
561 FROM qp_rltd_modifiers a, ozf_qp_discounts b , ozf_offer_adjustment_tiers c , qp_list_lines d
562 WHERE a.to_rltd_modifier_id = b.list_line_id(+)
563 and b.offer_discount_line_id = c.offer_discount_line_id(+)
564 AND a.to_rltd_modifier_id = d.list_line_id
565 AND a.from_rltd_modifier_id = cp_listLineId
566 AND c.offer_adjustment_id(+) = cp_offerAdjustmentId;
567
568 /*SELECT to_rltd_modifier_id
569 FROM qp_rltd_modifiers
570 WHERE from_rltd_modifier_id = cp_listLineId;
571 */
572 i NUMBER;
573 l_pricingAttrTbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
574 BEGIN
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576 x_modifier_line_tbl.delete;
577 x_pricing_attr_tbl.delete;
578 l_pricingAttrTbl.delete;
579
580 i := 1;
581 FOR l_discountLines IN c_discountLines(cp_listLineId => p_listLineId , cp_offerAdjustmentId => p_offerAdjustmentId)
582 LOOP
583 x_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
584 x_modifier_line_tbl(i).list_line_type_code := 'DIS';
585 x_modifier_line_tbl(i).start_date_active := null;
586 x_modifier_line_tbl(i).rltd_modifier_grp_type := 'PRICE BREAK';
587 x_modifier_line_tbl(i).rltd_modifier_grp_no := 1;
588 x_modifier_line_tbl(i).modifier_parent_index := 1;
589 populate_discounts
590 (
591 x_modifiers_rec => x_modifier_line_tbl(i)
592 , p_list_line_id => l_discountLines.to_rltd_modifier_id
593 );
594 ----dbms_output.put_line('List Line Id for pricing attr:'||l_discountLines.to_rltd_modifier_id);
595 x_modifier_line_tbl(i).arithmetic_operator := l_discountLines.arithmetic_operator;
596 x_modifier_line_tbl(i).operand := l_discountLines.discount;
597 populate_pricing_attributes
598 (
599 x_pricing_attr_tbl => l_pricingAttrTbl
600 , p_list_line_id => l_discountLines.to_rltd_modifier_id
601 , p_index => i + 1 -- i + 1 the 1 for the row which is already populated for pbh line
602 );
603 merge_pricing_attributes
604 (
605 px_to_pricing_attr_tbl => x_pricing_attr_tbl
606 , p_from_pricing_attr_tbl => l_pricingAttrTbl
607 );
608 i := i + 1;
609 END LOOP;
610 END populate_dis_lines;
611
612 PROCEDURE populate_modifier_lines
613 (
614 x_return_status OUT NOCOPY VARCHAR2
615 , x_msg_count OUT NOCOPY NUMBER
616 , x_msg_data OUT NOCOPY VARCHAR2
617 , p_listLineId IN NUMBER
618 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
619 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
620 , p_offerAdjustmentId IN NUMBER
621 )
622 IS
623 pbh_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
624 dis_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
625 pbh_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
626 dis_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
627 CURSOR c_additionalDetails(cp_offerAdjustmentId NUMBER)
628 IS
629 SELECT
630 a.effective_date, a.list_header_id
631 FROM ozf_offer_adjustments_b a
632 WHERE a.offer_adjustment_id = cp_offerAdjustmentId;
633 l_additionalDetails c_additionalDetails%ROWTYPE;
634 BEGIN
635 x_return_status := FND_API.G_RET_STS_SUCCESS;
636 -- initialize table
637 -- populate pbh line
638 -- populate dis lines
639 -- merge the dis lines with pbh lines
640 x_modifier_line_tbl.delete;
641 pbh_pricing_attr_tbl.delete;
642 dis_pricing_attr_tbl.delete;
643 pbh_modifier_line_tbl.delete;
644 dis_modifier_line_tbl.delete;
645 populate_pbh_line
646 (
647 x_return_status => x_return_status
648 , x_msg_count => x_msg_count
649 , x_msg_data => x_msg_data
650 , p_listLineId => p_listLineId
651 , x_modifier_line_tbl => pbh_modifier_line_tbl
652 , x_pricing_attr_tbl => pbh_pricing_attr_tbl
653 );
654 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
655 RAISE FND_API.G_EXC_ERROR;
656 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
658 END IF;
659 OPEN c_additionalDetails(cp_offerAdjustmentId => p_offerAdjustmentId);
660 FETCH c_additionalDetails INTO l_additionalDetails;
661 CLOSE c_additionalDetails;
662 pbh_modifier_line_tbl(1).start_date_active := l_additionalDetails.effective_date;
663 ----dbms_output.put_line('Price break type code is1 :'||pbh_modifier_line_tbl(1).price_break_type_code||' : '||p_listLineId);
664 populate_dis_lines
665 (
666 x_return_status => x_return_status
667 , x_msg_count => x_msg_count
668 , x_msg_data => x_msg_data
669 , p_listLineId => p_listLineId
670 , p_offerAdjustmentId => p_offerAdjustmentId
671 , x_modifier_line_tbl => dis_modifier_line_tbl
672 , x_pricing_attr_tbl => dis_pricing_attr_tbl
673 );
674 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
675 RAISE FND_API.G_EXC_ERROR;
676 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
678 END IF;
679 merge_modifiers
680 (
681 p_to_modifier_line_tbl => pbh_modifier_line_tbl
682 , p_from_modifier_line_tbl => dis_modifier_line_tbl
683 , x_modifier_line_tbl => x_modifier_line_tbl
684 );
685 merge_pricing_attributes
686 (
687 p_to_pricing_attr_tbl => pbh_pricing_attr_tbl
688 , p_from_pricing_attr_tbl => dis_pricing_attr_tbl
689 , x_pricing_attr_tbl => x_pricing_attr_tbl
690 );
691 for i in x_modifier_line_tbl.first .. x_modifier_line_tbl.last LOOP
692 ----dbms_output.put_line('Counter:'||i);
693 IF x_modifier_line_tbl.exists(i) THEN
694 ----dbms_output.put_line('Parent Index'||x_modifier_line_tbl(i).modifier_parent_index);
695 null;
696 END IF;
697 END LOOP;
698 --dbms_output.put_line('================Pricing Attr---------------------');
699 for i in x_pricing_attr_tbl.first .. x_pricing_attr_tbl.last LOOP
700 IF x_pricing_attr_tbl.exists(i) THEN
701 ----dbms_output.put_line('Counter:'||i);
702 ----dbms_output.put_line('Index:'||x_pricing_attr_tbl(i).modifiers_index);
703 null;
704 END IF;
705 END LOOP;
706 /*x_modifier_line_tbl := pbh_modifier_line_tbl MULTISET UNION dis_modifier_line_tbl;
707 x_pricing_attr_tbl := pbh_pricing_attr_tbl MULTISET UNION dis_pricing_attr_tbl;
708 */
709 EXCEPTION
710 WHEN OTHERS THEN
711 null;
712 END populate_modifier_lines;
713
714 /**
715 Copies a qp_list_line and pricing attributes, with start date as the effective date of the adjustmentId passed in
716 */
717 PROCEDURE create_modifier_from_line
718 (
719 x_return_status OUT NOCOPY VARCHAR2
720 , x_msg_count OUT NOCOPY NUMBER
721 , x_msg_data OUT NOCOPY VARCHAR2
722 , p_offerAdjustmentId IN NUMBER
723 , p_listLineId IN NUMBER
724 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
725 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
726 )
727 IS
728 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
729 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
730 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
731 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
732 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
733 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
734 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
735 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
736 l_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
737 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
738 BEGIN
739 -- initialize return status, nested tables
740 -- for given adjustment id get the adjustment tiers
741 -- populate the qp_list_lines using the tiers and adjustment tiers
742 -- create the QP list lines
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744 x_modifier_line_tbl.delete;
745 x_pricing_attr_tbl.delete;
746 l_modifier_line_tbl.delete;
747 l_pricing_attr_tbl.delete;
748 populate_modifier_lines
749 (
750 x_return_status => x_return_status
751 , x_msg_count => x_msg_count
752 , x_msg_data => x_msg_data
753 , p_offerAdjustmentId => p_offerAdjustmentId
754 , p_listLineId => p_listLineId --l_tierHeader.list_line_id
755 , x_modifier_line_tbl => l_modifier_line_tbl
756 , x_pricing_attr_tbl => l_pricing_attr_tbl
757 );
758 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
759 RAISE FND_API.G_EXC_ERROR;
760 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
761 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
762 END IF;
763 QP_Modifiers_PUB.process_modifiers(
764 p_api_version_number => 1.0,
765 p_init_msg_list => FND_API.G_FALSE,
766 p_return_values => FND_API.G_FALSE,
767 x_return_status => x_return_status,
768 x_msg_count => x_msg_count,
769 x_msg_data => x_msg_data,
770 p_modifiers_tbl => l_modifier_line_tbl,
771 p_pricing_attr_tbl => l_pricing_attr_tbl,
772 x_modifier_list_rec => v_modifier_list_rec,
773 x_modifier_list_val_rec => v_modifier_list_val_rec,
774 x_modifiers_tbl => v_modifiers_tbl,
775 x_modifiers_val_tbl => v_modifiers_val_tbl,
776 x_qualifiers_tbl => v_qualifiers_tbl,
777 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
778 x_pricing_attr_tbl => v_pricing_attr_tbl,
779 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
780 );
781 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
782 RAISE FND_API.G_EXC_ERROR;
783 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
785 END IF;
786 x_modifier_line_tbl := v_modifiers_tbl;
787 x_pricing_attr_tbl := v_pricing_attr_tbl;
788 --dbms_output.put_line('Table sizes 1 are :'||v_modifiers_tbl.count||' : '||v_pricing_attr_tbl.count);
789 --dbms_output.put_line('Table sizes 2 are :'||x_modifier_line_tbl.count||' : '||x_pricing_attr_tbl.count);
790 END create_modifier_from_line;
791
792 /**
793 */
794 PROCEDURE map_ozf_qp_lines
795 (
796 x_return_status OUT NOCOPY VARCHAR2
797 ,x_msg_count OUT NOCOPY NUMBER
798 ,x_msg_data OUT NOCOPY VARCHAR2
799 ,p_offerAdjustmentId IN NUMBER
800 ,p_offerDiscountLineId IN NUMBER
801 ,p_modifier_line_tbl IN QP_MODIFIERS_PUB.Modifiers_Tbl_Type
802 ,p_pricing_attr_tbl IN QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
803 )
804 IS
805 l_qpDiscountsRec OZF_QP_DISCOUNTS_PVT.qp_discount_rec_type;
806 l_qpProductsRec OZF_QP_PRODUCTS_PVT.qp_product_rec_type;
807 l_qpDiscountId NUMBER;
808 l_qpProductId NUMBER;
809 l_objectVersion NUMBER;
810 l_prodObjectVersion NUMBER;
811
812 CURSOR c_discounts(cp_offerDiscountLineId NUMBER, cp_offerAdjustmentId NUMBER)
813 IS
814 SELECT discount
815 , tier_type
816 , offer_discount_line_id
817 FROM ozf_offer_discount_lines
818 WHERE offer_discount_line_id = cp_offerDiscountlineId
819 OR parent_discount_line_id = cp_offerDiscountLineId;
820 CURSOR c_products(cp_offerDiscountLineId NUMBER) IS
821 SELECT off_discount_product_id
822 , product_attribute
823 , product_attr_value
824 , excluder_flag
825 FROM ozf_offer_discount_products
826 WHERE offer_discount_line_id = cp_offerDiscountlineId;
827
828 BEGIN
829 -- initialize
830 -- loop thru the Discount Structure
831 -- for each discount line loop thru. the qp_modifiers table
832 -- check for discount equality, in case yes insert into ozf_qp_discounts table
833 -- similar thing for relating products
834 x_return_status := FND_API.G_RET_STS_SUCCESS;
835 --dbms_output.put_line('Map Ozf qp lines');
836 --dbms_output.put_line('Table counts are :'||p_modifier_line_tbl.count||' : '||p_pricing_attr_tbl.count);
837 FOR l_discounts in c_discounts(cp_offerDiscountLineId => p_offerDiscountLineId , cp_offerAdjustmentId => p_offerAdjustmentId)
838 LOOP
839 --dbms_output.put_line('OfferDiscount line Id :'||l_discounts.offer_discount_line_id);
840 FOR i IN p_modifier_line_tbl.first .. p_modifier_line_tbl.last LOOP
841 IF p_modifier_line_tbl.exists(i) THEN
842 --dbms_output.put_line('List line Id :'||p_modifier_line_tbl(i).list_line_id||' : '||p_modifier_line_tbl(i).list_line_type_code);
843 IF
844 (l_discounts.tier_type = p_modifier_line_tbl(i).list_line_type_code )
845 AND
846 (nvl(l_discounts.discount,0) = nvl(p_modifier_line_tbl(i).operand,0))
847 THEN
848 --dbms_output.put_line('ListLIneId:'||p_modifier_line_tbl(i).list_line_id||' :Discount Line Id :'||l_discounts.offer_discount_line_id);
849 l_qpDiscountsRec := null;
850 l_qpDiscountsRec.list_line_id := p_modifier_line_tbl(i).list_line_id;
851 l_qpDiscountsRec.offer_discount_line_id := l_discounts.offer_discount_line_id;
852 l_qpDiscountsRec.start_date := sysdate;
853 OZF_QP_DISCOUNTS_PVT. Create_ozf_qp_discount
854 (
855 p_api_version_number => 1.0
856 , p_init_msg_list => FND_API.G_FALSE
857 , p_commit => FND_API.G_FALSE
858 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
859 , x_return_status => x_return_status
860 , x_msg_count => x_msg_count
861 , x_msg_data => x_msg_data
862 , p_qp_disc_rec => l_qpDiscountsRec
863 , x_qp_discount_id => l_qpDiscountId
864 );
865 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
866 RAISE FND_API.G_EXC_ERROR;
867 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
869 END IF;
870 ELSE
871 null;
872 --dbms_output.put_line('No Discounts found for list line id:'||p_modifier_line_tbl(i).list_line_id||':'||l_discounts.offer_discount_line_id);
873 END IF;
874 END IF;
875 END LOOP;
876 END LOOP;
877
878 FOR l_products IN c_products(cp_offerDiscountLineId => p_offerDiscountLineId) LOOP
879 FOR i in p_pricing_attr_tbl.first .. p_pricing_attr_tbl.last LOOP
880 IF p_pricing_attr_tbl.exists(i) THEN
881 IF
882 (l_products.excluder_flag = p_pricing_attr_tbl(i).excluder_flag)
883 AND
884 (l_products.product_attribute = p_pricing_attr_tbl(i).product_attribute)
885 AND
886 (l_products.product_attr_value = p_pricing_attr_tbl(i).product_attr_value)
887 THEN
888 --dbms_output.put_line('Pricing Attribute id:'||p_pricing_attr_tbl(i).pricing_attribute_id||': Product Id :'||l_products.off_discount_product_id);
889 l_qpProductsRec.off_discount_product_id := l_products.off_discount_product_id;
890 l_qpProductsRec.pricing_attribute_id := p_pricing_attr_tbl(i).pricing_attribute_id;
891 OZF_QP_PRODUCTS_PVT.Create_ozf_qp_product(
892 p_api_version_number => 1.0
893 , p_init_msg_list => FND_API.G_FALSE
894 , p_commit => FND_API.G_FALSE
895 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
896 , x_return_status => x_return_status
897 , x_msg_count => x_msg_count
898 , x_msg_data => x_msg_data
899 , p_qp_product_rec => l_qpProductsRec
900 , x_qp_product_id => l_qpProductId
901 );
902 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
903 RAISE FND_API.G_EXC_ERROR;
904 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906 END IF;
907 ELSE
908 null;
909 --dbms_output.put_line('No Products found for pricing_attribute :'||p_pricing_attr_tbl(i).pricing_attribute_id||' : '||l_products.off_discount_product_id);
910 END IF;
911 END IF;
912 END LOOP;
913 END LOOP;
914 END map_ozf_qp_lines;
915
916 /**
917 Creates new qp_list_lines and qp_pricing_attributes
918 for a given offer_adjustment_id
919 */
920 PROCEDURE create_new_qp_lines
921 (
922 x_return_status OUT NOCOPY VARCHAR2
923 ,x_msg_count OUT NOCOPY NUMBER
924 ,x_msg_data OUT NOCOPY VARCHAR2
925 ,p_offerAdjustmentId IN NUMBER
926 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
927 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
928 )
929 IS
930 l_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
931 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
932 CURSOR c_tierHeader(cp_offerAdjustmentId NUMBER)
933 IS
934 SELECT distinct parent_discount_line_id , c.list_line_id
935 FROM ozf_offer_adjustment_tiers a, ozf_offer_discount_lines b , ozf_qp_discounts c , qp_list_lines d
936 WHERE a.offer_discount_line_id = b.offer_discount_line_id
937 AND b.parent_discount_line_id = c.offer_discount_line_id
938 AND c.list_line_id = d.list_line_id
939 AND d.list_line_type_code = 'PBH'
940 AND offer_adjustment_id = cp_offerAdjustmentId;
941 BEGIN
942 -- initialize
943 -- get all the tier Headers for which adjustments have been entered
944 -- for each tier header create QP Data, corresponding to data already entered in QP
945 -- merge the modifier tables created for each discount
946 x_return_status := FND_API.G_RET_STS_SUCCESS;
947 x_modifier_line_tbl.delete;
948 x_pricing_attr_tbl.delete;
949 FOR l_tierHeader in c_tierHeader(cp_offerAdjustmentId => p_offerAdjustmentId)
950 LOOP
951 l_modifier_line_tbl.delete;
952 l_pricing_attr_tbl.delete;
953 create_modifier_from_line
954 (
955 x_return_status => x_return_status
956 ,x_msg_count => x_msg_count
957 ,x_msg_data => x_msg_data
958 ,p_offerAdjustmentId => p_offerAdjustmentId
959 , p_listLineId => l_tierHeader.list_line_id--l_tierHeader.parent_discount_line_id
960 , x_modifier_line_tbl => l_modifier_line_tbl
961 , x_pricing_attr_tbl => l_pricing_attr_tbl
962 );
963 --dbms_output.put_line('Table sizes 3 are :'||l_modifier_line_tbl.count||' : '||l_pricing_attr_tbl.count);
964 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
965 RAISE FND_API.G_EXC_ERROR;
966 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
968 END IF;
969 map_ozf_qp_lines
970 (
971 x_return_status => x_return_status
972 ,x_msg_count => x_msg_count
973 ,x_msg_data => x_msg_data
974 ,p_offerAdjustmentId => p_offerAdjustmentId
975 ,p_offerDiscountLineId => l_tierHeader.parent_discount_line_id
976 ,p_modifier_line_tbl => l_modifier_line_tbl
977 ,p_pricing_attr_tbl => l_pricing_attr_tbl
978 );
979 /*map_ozf_qp_lines
980 (
981 x_return_status OUT NOCOPY VARCHAR2
982 ,x_msg_count OUT NOCOPY NUMBER
983 ,x_msg_data OUT NOCOPY VARCHAR2
984 ,p_offerAdjustmentId IN NUMBER
985 ,p_offerDiscountLineId IN NUMBER
986 ,p_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
987 ,p_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
988 )*/
989 merge_modifiers
990 (
991 px_to_modifier_line_tbl => x_modifier_line_tbl
992 , p_from_modifier_line_tbl => l_modifier_line_tbl
993 );
994 merge_pricing_attributes
995 (
996 px_to_pricing_attr_tbl => x_pricing_attr_tbl
997 , p_from_pricing_attr_tbl => l_pricing_attr_tbl
998 );
999 END LOOP;
1000 -- passback the created modifier lines and pricing attributes.
1001 END create_new_qp_lines;
1002
1003 /**
1004 Creates relation between two qp_list_lines for a given offer_adjustment_id
1005 */
1006 PROCEDURE relate_lines
1007 (
1008 p_from_list_line_id IN NUMBER
1009 , p_to_list_line_id IN NUMBER
1010 , p_offer_adjustment_id IN NUMBER
1011 , x_return_status OUT NOCOPY VARCHAR2
1012 , x_msg_count OUT NOCOPY NUMBER
1013 , x_msg_data OUT NOCOPY VARCHAR2
1014 )
1015 IS
1016 BEGIN
1017 x_return_status := FND_API.G_RET_STS_SUCCESS;
1018 INSERT INTO ozf_offer_adj_rltd_lines
1019 (
1020 OFFER_ADJ_RLTD_LINE_ID
1021 , OFFER_ADJUSTMENT_ID
1022 , FROM_LIST_LINE_ID
1023 , TO_LIST_LINE_ID
1024 , LAST_UPDATE_DATE
1025 , LAST_UPDATED_BY
1026 , CREATION_DATE
1027 , CREATED_BY
1028 , LAST_UPDATE_LOGIN
1029 , OBJECT_VERSION_NUMBER
1030 , security_group_id
1031 )
1032 VALUES
1033 (
1034 ozf_offer_adj_rltd_lines_s.nextval
1035 , p_offer_adjustment_id
1036 , p_from_list_line_id
1037 , p_to_list_line_id
1038 , sysdate
1039 , FND_GLOBAL.USER_ID
1040 , sysdate
1041 , FND_GLOBAL.USER_ID
1042 , FND_GLOBAL.CONC_LOGIN_ID
1043 , 1
1044 , null
1045 );
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 x_return_status := Fnd_Api.g_ret_sts_error ;
1049 END relate_lines;
1050
1051 PROCEDURE relate_lines
1052 (
1053 p_modifiers_tbl IN qp_modifiers_pub.modifiers_tbl_type
1054 , p_offer_adjustment_id IN NUMBER
1055 , x_return_status OUT NOCOPY VARCHAR2
1056 , x_msg_count OUT NOCOPY NUMBER
1057 , x_msg_data OUT NOCOPY VARCHAR2
1058 )
1059 IS
1060 BEGIN
1061 x_return_status := FND_API.G_RET_STS_SUCCESS;
1062 IF nvl(p_modifiers_tbl.count,0) > 0 THEN
1063 FOR k IN p_modifiers_tbl.first .. p_modifiers_tbl.last LOOP
1064 IF p_modifiers_tbl.exists(k) THEN
1065 IF p_modifiers_tbl(k).operation <> 'CREATE' THEN
1066 null;
1067 ELSE
1068 IF p_modifiers_tbl(k).comments IS NULL OR p_modifiers_tbl(k).comments = FND_API.G_MISS_CHAR THEN
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 return;
1071 ELSE
1072 relate_lines
1073 (
1074 p_from_list_line_id => to_number(p_modifiers_tbl(k).comments)
1075 , p_to_list_line_id => p_modifiers_tbl(k).list_line_id
1076 , p_offer_adjustment_id => p_offer_adjustment_id
1077 , x_return_status => x_return_status
1078 , x_msg_count => x_return_status
1079 , x_msg_data => x_return_status
1080 );
1081 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1082 return;
1083 END IF;
1084 END IF;
1085 END IF;
1086 END IF;
1087 END LOOP;
1088 END IF;
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 x_return_status := FND_API.G_RET_STS_ERROR;
1092 return;
1093 END relate_lines;
1094
1095
1096 PROCEDURE adjust_old_discounts
1097 (
1098 x_return_status OUT NOCOPY VARCHAR2
1099 ,x_msg_count OUT NOCOPY NUMBER
1100 ,x_msg_data OUT NOCOPY VARCHAR2
1101 ,p_offerAdjustmentId IN NUMBER
1102 )
1103 IS
1104 l_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
1105 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1106 BEGIN
1107 x_return_status := FND_API.G_RET_STS_SUCCESS;
1108 /*
1109 Corresponding to the discount changes, update the tier definitions.
1110 End Date the QP List lines corresponding to the tier definitions changed.
1111 Create new QP List lines with the updated tier definitions.(1)[1]
1112 Create new discount-tier mapping(1)
1113 *Create new product-product mapping
1114 */
1115 update_adj_vo_tiers
1116 (
1117 x_return_status => x_return_status
1118 ,x_msg_count => x_msg_count
1119 ,x_msg_data => x_msg_data
1120 ,p_offerAdjustmentId => p_offerAdjustmentId
1121 );
1122 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1123 RAISE FND_API.G_EXC_ERROR;
1124 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126 END IF;
1127 end_date_qp_lines
1128 (
1129 x_return_status => x_return_status
1130 ,x_msg_count => x_msg_count
1131 ,x_msg_data => x_msg_data
1132 ,p_offerAdjustmentId => p_offerAdjustmentId
1133 );
1134 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1135 RAISE FND_API.G_EXC_ERROR;
1136 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1138 END IF;
1139 create_new_qp_lines
1140 (
1141 x_return_status => x_return_status
1142 ,x_msg_count => x_msg_count
1143 ,x_msg_data => x_msg_data
1144 ,p_offerAdjustmentId => p_offerAdjustmentId
1145 , x_modifier_line_tbl => l_modifier_line_tbl
1146 , x_pricing_attr_tbl => l_pricing_attr_tbl
1147 );
1148 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1149 RAISE FND_API.G_EXC_ERROR;
1150 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1152 END IF;
1153 relate_lines
1154 (
1155 p_modifiers_tbl => l_modifier_line_tbl
1156 , p_offer_adjustment_id => p_offerAdjustmentId
1157 , x_return_status => x_return_status
1158 ,x_msg_count => x_msg_count
1159 , x_msg_data => x_msg_data
1160 );
1161 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1162 RAISE FND_API.G_EXC_ERROR;
1163 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1165 END IF;
1166 EXCEPTION
1167 WHEN FND_API.G_EXC_ERROR THEN
1168 x_return_status := FND_API.G_RET_STS_ERROR;
1169 -- Standard call to get message count and if count=1, get the message
1170 FND_MSG_PUB.Count_And_Get (
1171 p_encoded => FND_API.G_FALSE,
1172 p_count => x_msg_count,
1173 p_data => x_msg_data
1174 );
1175 OE_MSG_PUB.Count_And_Get (
1176 p_encoded => FND_API.G_FALSE,
1177 p_count => x_msg_count,
1178 p_data => x_msg_data
1179 );
1180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1182 -- Standard call to get message count and if count=1, get the message
1183 FND_MSG_PUB.Count_And_Get (
1184 p_encoded => FND_API.G_FALSE,
1185 p_count => x_msg_count,
1186 p_data => x_msg_data
1187 );
1188 OE_MSG_PUB.Count_And_Get (
1189 p_encoded => FND_API.G_FALSE,
1190 p_count => x_msg_count,
1191 p_data => x_msg_data
1192 );
1193 WHEN OTHERS THEN
1194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1195 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1196 THEN
1197 FND_MSG_PUB.Add_Exc_Msg( 'VOLUME_OFFER_ADJ','adjust_old_discounts');
1198 END IF;
1199 -- Standard call to get message count and if count=1, get the message
1200 FND_MSG_PUB.Count_And_Get (
1201 p_encoded => FND_API.G_FALSE,
1202 p_count => x_msg_count,
1203 p_data => x_msg_data
1204 );
1205 OE_MSG_PUB.Count_And_Get (
1206 p_encoded => FND_API.G_FALSE,
1207 p_count => x_msg_count,
1208 p_data => x_msg_data
1209 );
1210
1211 END adjust_old_discounts;
1212
1213 /**
1214 Old exclusions are not included in the new qp_list_lines created. Need to take care of that
1215 */
1216 PROCEDURE create_new_products
1217 (
1218 x_return_status OUT NOCOPY VARCHAR2
1219 ,x_msg_count OUT NOCOPY NUMBER
1220 ,x_msg_data OUT NOCOPY VARCHAR2
1221 ,p_offerAdjustmentId IN NUMBER
1222 )
1223 IS
1224 CURSOR c_products(cp_offerAdjustmentId NUMBER) IS
1225 SELECT
1226 a.offer_discount_line_id
1227 , a.off_discount_product_id
1228 , a.product_context
1229 , a.product_attribute
1230 , a.product_attr_value
1231 , a.excluder_flag
1232 , a.apply_discount_flag
1233 , a.include_volume_flag
1234 , b.offer_id
1235 , a.offer_adjustment_product_id
1236 , a.object_version_number
1237 FROM ozf_offer_adjustment_products a, ozf_offer_discount_lines b
1238 WHERE
1239 a.offer_discount_line_id = b.offer_discount_line_id
1240 AND offer_adjustment_id = cp_offerAdjustmentId;
1241
1242 l_productsRec OZF_Volume_Offer_disc_PVT.vo_prod_rec_type;
1243 l_objId NUMBER;
1244 BEGIN
1245 x_return_status := FND_API.G_RET_STS_SUCCESS;
1246 FOR l_products in c_products(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
1247 l_productsRec := null;
1248 l_productsRec.excluder_flag := l_products.excluder_flag;
1249 l_productsRec.offer_discount_line_id := l_products.offer_discount_line_id;
1250 l_productsRec.offer_id := l_products.offer_id;
1251 l_productsRec.product_context := l_products.product_context;
1252 l_productsRec.product_attribute := l_products.product_attribute;
1253 l_productsRec.product_attr_value := l_products.product_attr_value;
1254 l_productsRec.apply_discount_flag := l_products.apply_discount_flag;
1255 l_productsRec.include_volume_flag := l_products.include_volume_flag;
1256
1257 OZF_Volume_Offer_disc_PVT.Create_vo_Product(
1258 p_api_version_number => 1.0
1259 , p_init_msg_list => FND_API.G_FALSE
1260 , p_commit => FND_API.G_FALSE
1261 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1262 , x_return_status => x_return_status
1263 , x_msg_count => x_msg_count
1264 , x_msg_data => x_msg_data
1265 , p_vo_prod_rec => l_productsRec
1266 , x_off_discount_product_id => l_objId
1267 );
1268 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1269 RAISE FND_API.G_EXC_ERROR;
1270 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1272 END IF;
1273 --dbms_output.put_line('Created Product successfully:'||l_objId);
1274 --dbms_output.put_line('Updating :'||l_products.offer_adjustment_product_id);
1275 UPDATE ozf_offer_adjustment_products
1276 SET off_discount_product_id = l_objId , object_version_number = object_version_number + 1
1277 WHERE offer_adjustment_product_id = l_products.offer_adjustment_product_id;
1278 --AND object_version_number = l_products.object_version_number;
1279
1280 END LOOP;
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 x_return_status := FND_API.G_RET_STS_ERROR;
1284 -- Standard call to get message count and if count=1, get the message
1285 FND_MSG_PUB.Count_And_Get (
1286 p_encoded => FND_API.G_FALSE,
1287 p_count => x_msg_count,
1288 p_data => x_msg_data
1289 );
1290
1291 -- initialize
1292 -- query new products added in the adjustment
1293 -- insert the new products into ozf_offer_discount_products
1294 -- update ozf_offer_adjustment_products with the off_discount_product_id of the newly created product
1295 END create_new_products;
1296
1297
1298 PROCEDURE populate_advanced_options
1299 (
1300 p_listHeaderId IN NUMBER
1301 , x_modifiers_rec IN OUT NOCOPY Qp_Modifiers_Pub.modifiers_rec_type
1302 )
1303 IS
1304 CURSOR c_advOptions(cp_listHeaderId NUMBER)
1305 IS
1306 SELECT
1307 print_on_invoice_flag
1308 , accrual_flag
1309 , pricing_phase_id
1310 , pricing_group_sequence
1311 , incompatibility_grp_code
1312 , product_precedence
1313 , proration_type_code
1314 FROM qp_list_lines
1315 WHERE list_header_id = cp_listHeaderId
1316 AND rownum < 2;
1317 BEGIN
1318 --x_return_status := FND_API.G_RET_STS_SUCCESS;
1319 FOR l_advOptions IN c_advOptions(cp_listHeaderId => p_listHeaderId ) LOOP
1320 x_modifiers_rec.print_on_invoice_flag := l_advOptions.print_on_invoice_flag;
1321 x_modifiers_rec.accrual_flag := l_advOptions.accrual_flag;
1322 x_modifiers_rec.pricing_phase_id := l_advOptions.pricing_phase_id;
1323 x_modifiers_rec.pricing_group_sequence := l_advOptions.pricing_group_sequence;
1324 x_modifiers_rec.incompatibility_grp_code := l_advOptions.incompatibility_grp_code;
1325 x_modifiers_rec.product_precedence := l_advOptions.product_precedence;
1326 x_modifiers_rec.proration_type_code := l_advOptions.proration_type_code;
1327 END LOOP;
1328 END populate_advanced_options;
1329
1330
1331 /**
1332 This produre populates and returns a qp_modifier_rec given the offerDiscountLineId
1333 */
1334 PROCEDURE populate_discounts
1335 (
1336 x_modifiers_rec IN OUT NOCOPY Qp_Modifiers_Pub.modifiers_rec_type
1337 , p_offerDiscountLineId IN NUMBER
1338 )
1339 IS
1340 -- list header id
1341 -- modifierlevelCode
1342 -- accrual flag
1343 -- arithmetic operator
1344 -- price break type code
1345 -- start date active
1346 -- formula
1347 -- accumulation attribute
1348 CURSOR c_discountDetails(cp_offerDiscountLineId NUMBER ) IS
1349 SELECT
1350 c.list_header_id
1351 , b.modifier_level_code
1352 , decode(b.offer_type, 'ACCRUAL','Y','VOLUME_OFFER', decode(b.volume_offer_type,'ACCRUAL','Y','N'),'N') accrual_flag
1353 , nvl(a.discount_type,d.discount_type) discount_type
1354 , a.volume_break_type price_break_type_code
1355 , a.formula_id
1356 , a.discount
1357 , c.effective_date
1358 FROM
1359 ozf_offer_discount_lines a, ozf_offers b , ozf_offer_adjustments_b c , ozf_offer_discount_lines d
1360 WHERE
1361 a.offer_discount_line_id = cp_offerDiscountLineId
1362 AND a.offer_id = b.offer_id
1363 AND b.qp_list_header_id = c.list_header_id
1364 AND a.parent_discount_line_id = d.offer_discount_line_id(+);
1365 BEGIN
1366 --x_return_status := FND_API.G_RET_STS_SUCCESS;
1367 FOR l_discountDetails IN c_discountDetails(cp_offerDiscountLineId => p_offerDiscountLineId) LOOP
1368 x_modifiers_rec.list_header_id := l_discountDetails.list_header_id;
1369 x_modifiers_rec.end_date_active := null;
1370 x_modifiers_rec.arithmetic_operator := l_discountDetails.discount_type;
1371 x_modifiers_rec.operand := l_discountDetails.discount;
1372 -- --dbms_output.put_line('arithmetic operator1 is :'||x_modifiers_rec.arithmetic_operator||' : '||l_discounts.discount_type);
1373 x_modifiers_rec.price_break_type_code := l_discountDetails.price_break_type_code;
1374 -- --dbms_output.put_line('Price break type code is :'||x_modifiers_rec.price_break_type_code||' : '||l_discounts.volume_break_type);
1375 ----------------------advanced options---------------------------------
1376 x_modifiers_rec.accrual_flag := l_discountDetails.accrual_flag;
1377 --------------------formulas------------------------------------------
1378 x_modifiers_rec.price_by_formula_id := l_discountDetails.formula_id;
1379 x_modifiers_rec.generate_using_formula_id:= l_discountDetails.formula_id;
1380
1381 x_modifiers_rec.modifier_level_code := l_discountDetails.modifier_level_code;
1382 x_modifiers_rec.automatic_flag := 'Y';
1383 x_modifiers_rec.override_flag := 'N';
1384 x_modifiers_rec.comments := p_offerDiscountLineId;
1385 ---------------------Accumulation attributes ------------------------
1386 --fix for bug 9900749
1387 IF x_modifiers_rec.list_line_type_code = 'PBH' THEN
1388 x_modifiers_rec.accum_attribute := 'PRICING_ATTRIBUTE19';
1389 END IF;
1390 --end fix for bug 9900749
1391 populate_advanced_options
1392 (
1393 p_listHeaderId => l_discountDetails.list_header_id
1394 , x_modifiers_rec => x_modifiers_rec
1395 );
1396 END LOOP;
1397 END populate_discounts;
1398 PROCEDURE populate_pbh_line
1399 (
1400 x_return_status OUT NOCOPY VARCHAR2
1401 , x_msg_count OUT NOCOPY NUMBER
1402 , x_msg_data OUT NOCOPY VARCHAR2
1403 , p_offerDiscountLineId IN NUMBER
1404 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
1405 )
1406 IS
1407 BEGIN
1408 --dbms_output.put_line('IN populate pbh line');
1409 x_return_status := FND_API.G_RET_STS_SUCCESS;
1410 x_modifier_line_tbl.delete;
1411 --dbms_output.put_line('Before populate discounts1');
1412 x_modifier_line_tbl(1).operation := QP_GLOBALS.G_OPR_CREATE;
1413 x_modifier_line_tbl(1).list_line_type_code := 'PBH';
1414 x_modifier_line_tbl(1).automatic_flag := 'Y';
1415 populate_discounts
1416 (
1417 x_modifiers_rec => x_modifier_line_tbl(1)
1418 , p_offerDiscountLineId => p_offerDiscountLineId
1419 );
1420 --dbms_output.put_line('After populate discounts1');
1421 END populate_pbh_line;
1422
1423 PROCEDURE populate_pricing_attr
1424 (
1425 x_return_status OUT NOCOPY VARCHAR2
1426 , x_msg_count OUT NOCOPY NUMBER
1427 , x_msg_data OUT NOCOPY VARCHAR2
1428 , p_offerDiscountLineId IN NUMBER
1429 , x_pricing_attr_rec IN OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_rec_type
1430 , p_index IN NUMBER
1431 )
1432 IS
1433 CURSOR c_pricingAttr(cp_offerDiscountLineId NUMBER) IS
1434 SELECT
1435 nvl(a.uom_code,b.uom_code) uom_code
1436 , b. offer_discount_line_id
1437 , b.volume_from
1438 , b.volume_to
1439 , nvl( a.volume_type,b.volume_type) volume_type
1440 , nvl(b.volume_operator,'BETWEEN') comparison_operator_code
1441 FROM ozf_offer_discount_lines b , ozf_offer_discount_lines a
1442 WHERE b.offer_discount_line_id = cp_offerDiscountLineId
1443 AND b.parent_discount_line_id = a.offer_discount_line_id(+);
1444 BEGIN
1445 x_return_status := FND_API.G_RET_STS_SUCCESS;
1446 for l_pricingAttr in c_pricingAttr(cp_offerDiscountLineId => p_offerDiscountLineId) LOOP
1447 x_pricing_attr_rec.product_uom_code := l_pricingAttr.uom_code;
1448 x_pricing_attr_rec.pricing_attr_value_from := l_pricingAttr.volume_from;
1449 x_pricing_attr_rec.pricing_attr_value_to := l_pricingAttr.volume_to;
1450 x_pricing_attr_rec.pricing_attribute_context := 'VOLUME';
1451 x_pricing_attr_rec.pricing_attribute := l_pricingAttr.volume_type;
1452 x_pricing_attr_rec.comparison_operator_code := l_pricingAttr.comparison_operator_code;
1453 x_pricing_attr_rec.modifiers_index := p_index;
1454 x_pricing_attr_rec.operation := Qp_Globals.G_OPR_CREATE;
1455 END LOOP;
1456 END populate_pricing_attr;
1457
1458 FUNCTION getMinVolumeFrom
1459 (
1460 p_offDiscountProductId NUMBER
1461 )
1462 RETURN NUMBER
1463 IS
1464 l_volumeFrom NUMBER;
1465 CURSOR c_minVolume(cp_offDiscountProductId NUMBER)
1466 IS
1467 SELECT min(volume_from)
1468 FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
1469 WHERE a.parent_discount_line_id = b.offer_discount_line_id
1470 AND b.off_discount_product_id = cp_offDiscountProductId;
1471 BEGIN
1472 OPEN c_minVolume(cp_offDiscountProductId => p_offDiscountProductId);
1473 FETCH c_minVolume INTO l_volumeFrom;
1474 IF c_minVolume%NOTFOUND THEN
1475 l_volumeFrom := null;
1476 END IF;
1477 CLOSE c_minVolume;
1478 RETURN l_volumeFrom;
1479 END getMinVolumeFrom;
1480
1481 PROCEDURE populateZeroDiscount
1482 (
1483 x_return_status OUT NOCOPY VARCHAR2
1484 , x_msg_count OUT NOCOPY NUMBER
1485 , x_msg_data OUT NOCOPY VARCHAR2
1486 , p_offDiscountProductId IN NUMBER
1487 , p_offerDiscountLineId IN NUMBER
1488 , x_modifier_line_rec IN OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Rec_Type
1489 , x_pricing_attr_rec IN OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_rec_type
1490 )
1491 IS
1492 BEGIN
1493 populate_discounts
1494 (
1495 x_modifiers_rec => x_modifier_line_rec
1496 , p_offerDiscountLineId => p_offerDiscountLineId
1497 );
1498 populate_pricing_attr
1499 (
1500 x_return_status => x_return_status
1501 , x_msg_count => x_msg_count
1502 , x_msg_data => x_msg_data
1503 , p_offerDiscountLineId => p_offerDiscountLineId
1504 , x_pricing_attr_rec => x_pricing_attr_rec
1505 , p_index => 2
1506 );
1507 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1508 RAISE FND_API.G_EXC_ERROR;
1509 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1511 END IF;
1512 x_modifier_line_rec.operand := 0;
1513 x_modifier_line_rec.comments:= -1;
1514 x_pricing_attr_rec.pricing_attr_value_from := '0';
1515 x_pricing_attr_rec.pricing_attr_value_to := to_char(getMinVolumeFrom(p_offDiscountProductId => p_offDiscountProductId));
1516 END populateZeroDiscount;
1517
1518 PROCEDURE populate_dis_line
1519 (
1520 x_return_status OUT NOCOPY VARCHAR2
1521 , x_msg_count OUT NOCOPY NUMBER
1522 , x_msg_data OUT NOCOPY VARCHAR2
1523 , p_offerDiscountLineId IN NUMBER
1524 , p_offDiscountProductId IN NUMBER
1525 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
1526 , p_pricing_attr_rec IN Qp_Modifiers_Pub.pricing_attr_rec_type
1527 , x_pricing_attr_tbl OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_Tbl_type
1528 )
1529 IS
1530 CURSOR c_discountLines(cp_parentDiscountLineId NUMBER , cp_offDiscountProductId NUMBER)
1531 IS
1532 SELECT a.offer_discount_line_id , b.apply_discount_flag
1533 FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
1534 WHERE parent_discount_line_id = cp_parentDiscountLineId
1535 AND a.parent_discount_line_id = b.offer_discount_line_id
1536 AND b.off_discount_product_id = cp_offDiscountProductId;
1537
1538 i NUMBER;
1539 BEGIN
1540 x_return_status := FND_API.G_RET_STS_SUCCESS;
1541 x_modifier_line_tbl.delete;
1542 x_pricing_attr_tbl.delete;
1543 i := 2;
1544 IF getMinVolumeFrom(p_offDiscountProductId => p_offDiscountProductId) IS NOT NULL AND getMinVolumeFrom(p_offDiscountProductId => p_offDiscountProductId) > 0 THEN
1545 x_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
1546 x_modifier_line_tbl(i).list_line_type_code := 'DIS';
1547 x_modifier_line_tbl(i).start_date_active := null;
1548 x_modifier_line_tbl(i).rltd_modifier_grp_type := 'PRICE BREAK';
1549 x_modifier_line_tbl(i).rltd_modifier_grp_no := 1;
1550 x_modifier_line_tbl(i).modifier_parent_index := 1;
1551 x_pricing_attr_tbl(i) := p_pricing_attr_rec;
1552 populateZeroDiscount
1553 (
1554 x_return_status => x_return_status
1555 , x_msg_count => x_msg_count
1556 , x_msg_data => x_msg_data
1557 , x_modifier_line_rec => x_modifier_line_tbl(i)
1558 , p_offDiscountProductId => p_offDiscountProductId
1559 , p_offerDiscountLineId => p_offerDiscountLineId
1560 , x_pricing_attr_rec => x_pricing_attr_tbl(i)
1561 );
1562 i := i + 1;
1563 END IF;
1564 FOR l_discountLines IN c_discountLines(cp_parentDiscountLineId => p_offerDiscountLineId , cp_offDiscountProductId => p_offDiscountProductId) LOOP
1565 x_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
1566 x_modifier_line_tbl(i).list_line_type_code := 'DIS';
1567 x_modifier_line_tbl(i).start_date_active := null;
1568 x_modifier_line_tbl(i).rltd_modifier_grp_type := 'PRICE BREAK';
1569 x_modifier_line_tbl(i).rltd_modifier_grp_no := 1;
1570 x_modifier_line_tbl(i).modifier_parent_index := 1;
1571 x_pricing_attr_tbl(i) := p_pricing_attr_rec;
1572
1573 populate_discounts
1574 (
1575 x_modifiers_rec => x_modifier_line_tbl(i)
1576 , p_offerDiscountLineId => l_discountLines.offer_discount_line_id
1577 );
1578 IF l_discountLines.apply_discount_flag = 'N' THEN
1579 x_modifier_line_tbl(i).operand := 0;
1580 END IF;
1581
1582 populate_pricing_attr
1583 (
1584 x_return_status => x_return_status
1585 , x_msg_count => x_msg_count
1586 , x_msg_data => x_msg_data
1587 , p_offerDiscountLineId => l_discountLines.offer_Discount_Line_Id
1588 , x_pricing_attr_rec => x_pricing_attr_tbl(i)
1589 , p_index => i
1590 );
1591 i := i + 1;
1592 END LOOP;
1593 END populate_dis_line;
1594
1595
1596 PROCEDURE populate_discounts
1597 (
1598 x_return_status OUT NOCOPY VARCHAR2
1599 , x_msg_count OUT NOCOPY NUMBER
1600 , x_msg_data OUT NOCOPY VARCHAR2
1601 , p_offerDiscountLineId IN NUMBER
1602 , p_offDiscountProductId IN NUMBER
1603 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
1604 , p_pricing_attr_rec IN Qp_Modifiers_Pub.pricing_attr_Rec_type
1605 , x_pricing_attr_tbl OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_Tbl_type
1606 )
1607 IS
1608 l_dis_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
1609 l_pbh_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
1610 l_dis_pricing_attr_tbl Qp_Modifiers_Pub.pricing_attr_Tbl_type;
1611 l_pbh_pricing_attr_tbl Qp_Modifiers_Pub.pricing_attr_Tbl_type;
1612 BEGIN
1613 x_return_status := FND_API.G_RET_STS_SUCCESS;
1614 x_modifier_line_tbl.delete;
1615 x_pricing_attr_tbl.delete;
1616 l_dis_modifier_line_tbl.delete;
1617 l_pbh_modifier_line_tbl.delete;
1618 l_dis_pricing_attr_tbl.delete;
1619 l_pbh_pricing_attr_tbl.delete;
1620 populate_pbh_line
1621 (
1622 x_return_status => x_return_status
1623 , x_msg_count => x_msg_count
1624 , x_msg_data => x_msg_data
1625 , p_offerDiscountLineId => p_offerDiscountLineId
1626 , x_modifier_line_tbl => l_pbh_modifier_line_tbl
1627 );
1628 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1629 RAISE FND_API.G_EXC_ERROR;
1630 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1632 END IF;
1633 l_pbh_pricing_attr_tbl(1) := p_pricing_attr_rec;
1634 --dbms_output.put_line('l_pbh_pricing_attr_tbl(1) := p_pricing_attr_rec;'||l_pbh_pricing_attr_tbl(1).product_attr_value);
1635 l_pbh_pricing_attr_tbl(1).modifiers_index := 1;
1636 populate_pricing_attr
1637 (
1638 x_return_status => x_return_status
1639 , x_msg_count => x_msg_count
1640 , x_msg_data => x_msg_data
1641 , p_offerDiscountLineId => p_offerDiscountLineId
1642 , x_pricing_attr_rec => l_pbh_pricing_attr_tbl(1)
1643 , p_index => 1
1644 );
1645 --dbms_output.put_line('l_pbh_pricing_attr_tbl(1) := p_pricing_attr_rec;'||l_pbh_pricing_attr_tbl(1).product_attr_value);
1646 populate_dis_line
1647 (
1648 x_return_status => x_return_status
1649 , x_msg_count => x_msg_count
1650 , x_msg_data => x_msg_data
1651 , p_offerDiscountLineId => p_offerDiscountLineId
1652 , p_offDiscountProductId => p_offDiscountProductId
1653 , x_modifier_line_tbl => l_dis_modifier_line_tbl
1654 , p_pricing_attr_rec => p_pricing_attr_rec
1655 , x_pricing_attr_tbl => l_dis_pricing_attr_tbl
1656 );
1657 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1658 RAISE FND_API.G_EXC_ERROR;
1659 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1661 END IF;
1662 --dbms_output.put_line('After populate_dis_line');
1663 merge_modifiers
1664 (
1665 px_to_modifier_line_tbl => l_pbh_modifier_line_tbl
1666 , p_from_modifier_line_tbl => l_dis_modifier_line_tbl
1667 -- , x_modifier_line_tbl => x_modifier_line_tbl
1668 );
1669 x_modifier_line_tbl := l_pbh_modifier_line_tbl;
1670 merge_pricing_attributes
1671 (
1672 px_to_pricing_attr_tbl => l_pbh_pricing_attr_tbl
1673 , p_from_pricing_attr_tbl => l_dis_pricing_attr_tbl
1674 -- , x_pricing_attr_tbl =>x_pricing_attr_tbl
1675 );
1676 x_pricing_attr_tbl := l_pbh_pricing_attr_tbl;
1677 --dbms_output.put_line('end POpulate complete discounts');
1678 END populate_discounts;
1679
1680 /**
1681 Note not initializing the record to attribute_grouping_no leads to unexpected error cannot insert null into qp_pricing_attributes.attribute_grouping_no
1682 */
1683 /**
1684 This method populates product attributes ie. Product Attribute, Product Attr Value , excluder flag
1685 into a Qp_Modifiers_Pub.pricing_attr_rec_type record given the Product Id in ozf_offer_discount_products table
1686 */
1687 PROCEDURE populate_product_attributes
1688 (
1689 x_pricing_attr_rec OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_rec_type
1690 , p_offDiscountProductId IN NUMBER
1691 -- , p_index IN NUMBER
1692 )
1693 IS
1694 CURSOR c_productAttributes(cp_offDiscountProductId NUMBER) IS
1695 SELECT
1696 product_context
1697 , product_attribute
1698 , product_attr_value
1699 , excluder_flag
1700 , apply_discount_flag
1701 , include_volume_flag
1702 FROM
1703 ozf_offer_discount_products
1704 WHERE off_discount_product_id = cp_offDiscountProductId;
1705 BEGIN
1706 --x_return_status := FND_API.G_RET_STS_SUCCESS;
1707 x_pricing_attr_rec := null;
1708 --dbms_output.put_line('Product Id :'||p_offDiscountProductId);
1709 FOR l_productAttributes IN c_productAttributes(cp_offDiscountProductId => p_offDiscountProductId) LOOP
1710 x_pricing_attr_rec := null;
1711 x_pricing_attr_rec.pricing_attribute_id := FND_API.G_MISS_NUM;
1712 x_pricing_attr_rec.attribute_grouping_no := FND_API.G_MISS_NUM;
1713 x_pricing_attr_rec.product_attribute_context := l_productAttributes.product_context;
1714 x_pricing_attr_rec.product_attribute := l_productAttributes.product_attribute;
1715 x_pricing_attr_rec.product_attr_value := l_productAttributes.product_attr_value;
1716 -- x_pricing_attr_rec.product_uom_code := l_pricingAttributes.product_uom_code;
1717 x_pricing_attr_rec.excluder_flag := l_productAttributes.excluder_flag;
1718 /* x_pricing_attr_rec.pricing_attr_value_from := l_pricingAttributes.pricing_attr_value_from;
1719 x_pricing_attr_rec.pricing_attr_value_to := l_pricingAttributes.pricing_attr_value_to;
1720 x_pricing_attr_rec.pricing_attribute_context := l_pricingAttributes.pricing_attribute_context;
1721 x_pricing_attr_rec.pricing_attribute := l_pricingAttributes.pricing_attribute;
1722 */
1723 x_pricing_attr_rec.operation := Qp_Globals.G_OPR_CREATE;
1724 -- x_pricing_attr_rec.comparison_operator_code := l_pricingAttributes.comparison_operator_code;
1725 -- x_pricing_attr_rec.modifiers_index := P_INDEX;
1726 END LOOP;
1727 -- initialize
1728 -- loop thru. off_discount_products and populate pricing attributes
1729 END populate_product_attributes;
1730
1731 /**
1732 This procedure creates the OZF qp mapping given the offDiscountProductId.
1733 This is a procedure for mapping a single product in OZF to the corresponding structure in QP
1734 The logic and assumptions used here are:
1735 1. All the qp_list_lines are created with the offer_discount_line_id stored in the comments.
1736 2. The offDiscountProductId passed in is mapped to all the pricing_attribute_id 's passed in
1737 */
1738 PROCEDURE map_ozf_qp_data
1739 (
1740 p_offDiscountProductId IN NUMBER
1741 , p_modifiers_tbl IN QP_MODIFIERS_PUB.Modifiers_Tbl_Type
1742 , p_pricing_attr_tbl IN QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
1743 , x_return_status OUT NOCOPY VARCHAR2
1744 , x_msg_count OUT NOCOPY NUMBER
1745 , x_msg_data OUT NOCOPY VARCHAR2
1746 )
1747 IS
1748 l_qpDiscountsRec OZF_QP_DISCOUNTS_PVT.qp_discount_rec_type;
1749 l_qpProductsRec OZF_QP_PRODUCTS_PVT.qp_product_rec_type;
1750 l_qpDiscountId NUMBER;
1751 l_qpProductId NUMBER;
1752 l_objectVersion NUMBER;
1753 l_prodObjectVersion NUMBER;
1754
1755 BEGIN
1756 x_return_status := FND_API.G_RET_STS_SUCCESS;
1757 FOR i IN p_modifiers_tbl.first .. p_modifiers_tbl.last LOOP
1758 IF p_modifiers_tbl.exists(i) THEN
1759 IF p_modifiers_tbl(i).comments <> -1 THEN
1760 --dbms_output.put_line('Disocunt Mapping:'||i||' : '||p_modifiers_tbl(i).comments||' : '||p_modifiers_tbl(i).list_line_id);
1761 l_qpDiscountsRec := null;
1762 l_qpDiscountsRec.list_line_id := p_modifiers_tbl(i).list_line_id;
1763 l_qpDiscountsRec.offer_discount_line_id := p_modifiers_tbl(i).comments;
1764 l_qpDiscountsRec.start_date := sysdate;
1765 OZF_QP_DISCOUNTS_PVT.Create_ozf_qp_discount
1766 (
1767 p_api_version_number => 1.0
1768 , p_init_msg_list => FND_API.G_FALSE
1769 , p_commit => FND_API.G_FALSE
1770 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1771 , x_return_status => x_return_status
1772 , x_msg_count => x_msg_count
1773 , x_msg_data => x_msg_data
1774 , p_qp_disc_rec => l_qpDiscountsRec
1775 , x_qp_discount_id => l_qpDiscountId
1776 );
1777 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1778 RAISE FND_API.G_EXC_ERROR;
1779 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1781 END IF;
1782 END IF;
1783 END IF;
1784 END LOOP;
1785 FOR i in p_pricing_attr_tbl.first .. p_pricing_attr_tbl.last LOOP
1786 IF p_pricing_attr_tbl.exists(i) THEN
1787 --dbms_output.put_line('Product Mapping:'||i||' : '||p_offDiscountProductId||' : '||p_pricing_attr_tbl(i).pricing_attribute_id);
1788 l_qpProductsRec := null;
1789 l_qpProductsRec.off_discount_product_id := p_offDiscountProductId;
1790 l_qpProductsRec.pricing_attribute_id := p_pricing_attr_tbl(i).pricing_attribute_id;
1791 OZF_QP_PRODUCTS_PVT.Create_ozf_qp_product(
1792 p_api_version_number => 1.0
1793 , p_init_msg_list => FND_API.G_FALSE
1794 , p_commit => FND_API.G_FALSE
1795 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1796 , x_return_status => x_return_status
1797 , x_msg_count => x_msg_count
1798 , x_msg_data => x_msg_data
1799 , p_qp_product_rec => l_qpProductsRec
1800 , x_qp_product_id => l_qpProductId
1801 );
1802 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1803 RAISE FND_API.G_EXC_ERROR;
1804 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1806 END IF;
1807 END IF;
1808 END LOOP;
1809 END map_ozf_qp_data;
1810
1811 /**
1812 This procedure maps given offDiscountProductId to every pricing_attribute_id in the given pricing_attributes table
1813 */
1814 PROCEDURE map_ozf_qp_products
1815 (
1816 p_offDiscountProductId IN NUMBER
1817 , p_pricing_attr_tbl IN QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
1818 , x_return_status OUT NOCOPY VARCHAR2
1819 , x_msg_count OUT NOCOPY NUMBER
1820 , x_msg_data OUT NOCOPY VARCHAR2
1821 )
1822 IS
1823 l_qpProductsRec OZF_QP_PRODUCTS_PVT.qp_product_rec_type;
1824 l_qpProductId NUMBER;
1825 l_prodObjectVersion NUMBER;
1826 BEGIN
1827 x_return_status := FND_API.G_RET_STS_SUCCESS;
1828 FOR i in p_pricing_attr_tbl.first .. p_pricing_attr_tbl.last LOOP
1829 IF p_pricing_attr_tbl.exists(i) THEN
1830 l_qpProductsRec := null;
1831 l_qpProductsRec.off_discount_product_id := p_offDiscountProductId;
1832 l_qpProductsRec.pricing_attribute_id := p_pricing_attr_tbl(i).pricing_attribute_id;
1833 OZF_QP_PRODUCTS_PVT.Create_ozf_qp_product(
1834 p_api_version_number => 1.0
1835 , p_init_msg_list => FND_API.G_FALSE
1836 , p_commit => FND_API.G_FALSE
1837 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1838 , x_return_status => x_return_status
1839 , x_msg_count => x_msg_count
1840 , x_msg_data => x_msg_data
1841 , p_qp_product_rec => l_qpProductsRec
1842 , x_qp_product_id => l_qpProductId
1843 );
1844 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1845 RAISE FND_API.G_EXC_ERROR;
1846 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1848 END IF;
1849 END IF;
1850 END LOOP;
1851 END map_ozf_qp_products;
1852
1853 /**
1854 Creates exclusions in All QP Discount structires corresponding to the structure to which the current exclusion is made
1855 */
1856 PROCEDURE create_new_exclusions
1857 (
1858 p_offDiscountProductId IN NUMBER
1859 , x_return_status OUT NOCOPY VARCHAR2
1860 , x_msg_count OUT NOCOPY NUMBER
1861 , x_msg_data OUT NOCOPY VARCHAR2
1862 )
1863 IS
1864 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1865 i NUMBER;
1866 CURSOR c_qpListLines(cp_offDiscountProductId NUMBER)
1867 IS
1868 SELECT b.list_line_id, a.product_attribute, a.product_attr_value , c.list_header_id
1869 FROM ozf_offer_discount_products a, ozf_qp_discounts b , qp_list_lines c
1870 WHERE a.offer_discount_line_id = b.offer_discount_line_id
1871 AND b.list_line_id = c.list_line_id
1872 AND a.off_discount_product_id = p_offDiscountProductId;
1873
1874 l_errorLoc NUMBER;
1875 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
1876 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
1877 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
1878 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
1879 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1880 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1881 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1882 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
1883 BEGIN
1884 -- initialize
1885 -- retrieve all the qp_list_lines corresponding to the discount structure to which the exclusion is added.
1886 -- exclude this new product from all the qp discount structures.
1887 -- map the newly created qp_pricing_attributes to this exclusion product.
1888 x_return_status := FND_API.G_RET_STS_SUCCESS;
1889 l_pricing_attr_tbl.delete;
1890 i := 1;
1891 FOR l_qpListLines IN c_qpListLines(cp_offDiscountProductId => p_offDiscountProductId) LOOP
1892 l_pricing_attr_tbl(i).product_attribute_context := 'ITEM';
1893 l_pricing_attr_tbl(i).product_attribute := l_qpListLines.product_attribute;
1894 l_pricing_attr_tbl(i).product_attr_value := l_qpListLines.product_attr_value;
1895 l_pricing_attr_tbl(i).list_line_id := l_qpListLines.list_line_id;
1896 l_pricing_attr_tbl(i).list_header_id := l_qpListLines.list_header_id;
1897 l_pricing_attr_tbl(i).excluder_flag := 'Y';
1898 l_pricing_attr_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
1899 --dbms_output.put_line('Details are:'||l_qpListLines.list_line_id||':'||l_qpListLines.list_header_id||':'||i);
1900 i := i + 1;
1901 END LOOP;
1902 QP_Modifiers_PUB.process_modifiers(
1903 p_api_version_number => 1.0,
1904 p_init_msg_list => FND_API.G_TRUE,
1905 p_return_values => FND_API.G_TRUE,
1906 x_return_status => x_return_status,
1907 x_msg_count => x_msg_count,
1908 x_msg_data => x_msg_data,
1909 p_pricing_attr_tbl => l_pricing_attr_tbl,
1910 x_modifier_list_rec => v_modifier_list_rec,
1911 x_modifier_list_val_rec => v_modifier_list_val_rec,
1912 x_modifiers_tbl => v_modifiers_tbl,
1913 x_modifiers_val_tbl => v_modifiers_val_tbl,
1914 x_qualifiers_tbl => v_qualifiers_tbl,
1915 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
1916 x_pricing_attr_tbl => v_pricing_attr_tbl,
1917 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
1918 );
1919 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1920 RAISE FND_API.G_EXC_ERROR;
1921 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1923 END IF;
1924 map_ozf_qp_products
1925 (
1926 p_offDiscountProductId => p_offDiscountProductId
1927 , p_pricing_attr_tbl => v_pricing_attr_tbl
1928 , x_return_status => x_return_status
1929 , x_msg_count => x_msg_count
1930 , x_msg_data => x_msg_data
1931 );
1932 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1933 RAISE FND_API.G_EXC_ERROR;
1934 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1936 END IF;
1937 END create_new_exclusions;
1938
1939 /**
1940 this procedure populates modifiers with details related to adjustements
1941 As of now only the start_date_active
1942 The assumption is that this procedure is called only multi-tier lines
1943 FOr now this is used only for populating the start_date_active of new qp lines to be created
1944 */
1945 PROCEDURE process_modifiers_for_adj
1946 (
1947 x_return_status OUT NOCOPY VARCHAR2
1948 , x_msg_count OUT NOCOPY NUMBER
1949 , x_msg_data OUT NOCOPY VARCHAR2
1950 , p_offerAdjustmentId IN NUMBER
1951 , px_modifier_line_tbl IN OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
1952 )
1953 IS
1954 CURSOR c_adjDetails(cp_offerAdjustmentId NUMBER) IS
1955 SELECT
1956 effective_date
1957 FROM ozf_offer_adjustments_b
1958 WHERE offer_adjustment_id = cp_offerAdjustmentId;
1959 l_adjDetails c_adjDetails%ROWTYPE;
1960 BEGIN
1961 x_return_status := FND_API.G_RET_STS_SUCCESS;
1962 OPEN c_adjDetails(cp_offerAdjustmentId => p_offerAdjustmentId);
1963 FETCH c_adjDetails INTO l_adjDetails;
1964 CLOSE c_adjDetails;
1965 IF nvl(px_modifier_line_tbl.count,0) > 0 THEN
1966 FOR i in px_modifier_line_tbl.first .. px_modifier_line_tbl.last LOOP
1967 IF px_modifier_line_tbl.exists(i) THEN
1968 IF px_modifier_line_tbl(i).list_line_type_code = 'PBH' THEN
1969 px_modifier_line_tbl(i).start_date_active := l_adjDetails.effective_date;
1970 END IF;
1971 END IF;
1972 END LOOP;
1973 END IF;
1974 END process_modifiers_for_adj;
1975
1976 /**
1977 Creates QP discount Structures for products entered thru. Adjustments.
1978 As of Tue Mar 14 2006:11/21 AM this API only processes new products entered thru. the adjustments UI.
1979 The Updates to old products are not handled.
1980 Even if the old discounts will be processed it will only change the definition of the offer and not affect the Volume Calculations.
1981 */
1982 PROCEDURE create_new_qp_products
1983 (
1984 x_return_status OUT NOCOPY VARCHAR2
1985 ,x_msg_count OUT NOCOPY NUMBER
1986 ,x_msg_data OUT NOCOPY VARCHAR2
1987 ,p_offerAdjustmentId IN NUMBER
1988 , x_modifier_line_tbl OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
1989 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
1990 )
1991 IS
1992 CURSOR c_products(cp_offerAdjustmentId NUMBER) IS
1993 SELECT a.off_discount_product_id
1994 , a.offer_discount_line_id
1995 FROM ozf_offer_adjustment_products a
1996 WHERE offer_adjustment_id = cp_offerAdjustmentId
1997 AND product_attr_value IS NOT NULL
1998 AND excluder_flag = 'N';
1999
2000 CURSOR c_exclusions(cp_offerAdjustmentId NUMBER) IS
2001 SELECT a.off_discount_product_id
2002 , a.offer_discount_line_id
2003 FROM ozf_offer_adjustment_products a
2004 WHERE offer_adjustment_id = cp_offerAdjustmentId
2005 AND product_attr_value IS NOT NULL
2006 AND excluder_flag = 'Y';
2007
2008 l_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
2009 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
2010 l_pricing_attr_rec QP_MODIFIERS_PUB.Pricing_Attr_Rec_Type;
2011 i NUMBER;
2012 l_errorLoc NUMBER;
2013 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
2014 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
2015 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
2016 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
2017 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
2018 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
2019 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
2020 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
2021 BEGIN
2022 -- initialize
2023 -- query new products entered using adjustments.
2024 -- for each new product id get the offer_discount_line_id
2025 -- create the whole discount structure for the product
2026 -- map the new ozf_qp lines and products
2027 x_return_status := FND_API.G_RET_STS_SUCCESS;
2028 i := 1;
2029 l_modifier_line_tbl.delete;
2030 l_pricing_attr_tbl.delete;
2031 FOR l_products IN c_products(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
2032 -- populate discounts
2033 -- populate products
2034 -- create qp data
2035 -- map ozf_qp_data
2036 l_pricing_attr_rec := null;
2037 populate_product_attributes
2038 (
2039 x_pricing_attr_rec => l_pricing_attr_rec
2040 , p_offDiscountProductId => l_products.off_discount_product_id
2041 -- , p_index IN NUMBER
2042 );
2043 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2044 RAISE FND_API.G_EXC_ERROR;
2045 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2047 END IF;
2048 populate_discounts
2049 (
2050 x_return_status => x_return_status
2051 , x_msg_count => x_msg_count
2052 , x_msg_data => x_msg_data
2053 , p_offerDiscountLineId => l_products.offer_discount_line_id
2054 , p_offDiscountProductId => l_products.off_discount_product_id
2055 , x_modifier_line_tbl => l_modifier_line_tbl
2056 , x_pricing_attr_tbl => l_pricing_attr_tbl
2057 , p_pricing_attr_rec => l_pricing_attr_rec
2058 );
2059 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2060 RAISE FND_API.G_EXC_ERROR;
2061 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2063 END IF;
2064 process_modifiers_for_adj
2065 (
2066 x_return_status => x_return_status
2067 , x_msg_count => x_msg_count
2068 , x_msg_data => x_msg_data
2069 , px_modifier_line_tbl => l_modifier_line_tbl
2070 , p_offerAdjustmentId => p_offerAdjustmentId
2071 );
2072 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2073 RAISE FND_API.G_EXC_ERROR;
2074 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2076 END IF;
2077 QP_Modifiers_PUB.process_modifiers(
2078 p_api_version_number => 1.0,
2079 p_init_msg_list => FND_API.G_TRUE,
2080 p_return_values => FND_API.G_TRUE,
2081 x_return_status => x_return_status,
2082 x_msg_count => x_msg_count,
2083 x_msg_data => x_msg_data,
2084 p_modifiers_tbl => l_modifier_line_tbl,
2085 p_pricing_attr_tbl => l_pricing_attr_tbl,
2086 x_modifier_list_rec => v_modifier_list_rec,
2087 x_modifier_list_val_rec => v_modifier_list_val_rec,
2088 x_modifiers_tbl => v_modifiers_tbl,
2089 x_modifiers_val_tbl => v_modifiers_val_tbl,
2090 x_qualifiers_tbl => v_qualifiers_tbl,
2091 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
2092 x_pricing_attr_tbl => v_pricing_attr_tbl,
2093 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
2094 );
2095 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2096 RAISE FND_API.G_EXC_ERROR;
2097 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2099 END IF;
2100 --dbms_output.put_line('After Create QP Data');
2101
2102 map_ozf_qp_data
2103 (
2104 p_offDiscountProductId => l_products.off_discount_product_id
2105 , p_modifiers_tbl => v_modifiers_tbl
2106 , p_pricing_attr_tbl => v_pricing_attr_tbl
2107 , x_return_status => x_return_status
2108 , x_msg_count => x_msg_count
2109 , x_msg_data => x_msg_data
2110 );
2111 --dbms_output.put_line('After Map data');
2112 END LOOP;
2113 --dbms_output.put_line('Processing exclusions');
2114 FOR l_exclusions in c_exclusions(cp_offerAdjustmentId => p_offerAdjustmentId ) LOOP
2115 create_new_exclusions
2116 (
2117 p_offDiscountProductId => l_exclusions.off_discount_product_id
2118 , x_return_status => x_return_status
2119 , x_msg_count => x_msg_count
2120 , x_msg_data => x_msg_data
2121 );
2122 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2123 RAISE FND_API.G_EXC_ERROR;
2124 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2126 END IF;
2127 END LOOP;
2128 --dbms_output.put_line('End Create new qp products');
2129
2130 END create_new_qp_products;
2131
2132 /**
2133 This procedure processes the new products added to an offer using an offer Adjustment.
2134 */
2135 PROCEDURE adjust_new_products
2136 (
2137 x_return_status OUT NOCOPY VARCHAR2
2138 ,x_msg_count OUT NOCOPY NUMBER
2139 ,x_msg_data OUT NOCOPY VARCHAR2
2140 ,p_offerAdjustmentId IN NUMBER
2141 )
2142 IS
2143 l_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
2144 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
2145 BEGIN
2146 x_return_status := FND_API.G_RET_STS_SUCCESS;
2147 /*
2148 New Products are added to Particular discount tables, so discount table id is stored .
2149 Create new Product in OZF Tables
2150 Get the Discount table id for the new product and create QP list lines using the Discount table.(1)[1]
2151 Create new discount-tier mapping.(1)
2152 */
2153 create_new_products
2154 (
2155 x_return_status => x_return_status
2156 ,x_msg_count => x_msg_count
2157 ,x_msg_data => x_msg_data
2158 ,p_offerAdjustmentId => p_offerAdjustmentId
2159 );
2160 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2161 RAISE FND_API.G_EXC_ERROR;
2162 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2164 END IF;
2165 create_new_qp_products
2166 (
2167 x_return_status => x_return_status
2168 ,x_msg_count => x_msg_count
2169 ,x_msg_data => x_msg_data
2170 ,p_offerAdjustmentId => p_offerAdjustmentId
2171 , x_modifier_line_tbl => l_modifier_line_tbl
2172 , x_pricing_attr_tbl => l_pricing_attr_tbl
2173 );
2174 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2175 RAISE FND_API.G_EXC_ERROR;
2176 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2178 END IF;
2179 EXCEPTION
2180 WHEN FND_API.G_EXC_ERROR THEN
2181 x_return_status := FND_API.G_RET_STS_ERROR;
2182 -- Standard call to get message count and if count=1, get the message
2183 FND_MSG_PUB.Count_And_Get (
2184 p_encoded => FND_API.G_FALSE,
2185 p_count => x_msg_count,
2186 p_data => x_msg_data
2187 );
2188
2189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2191 -- Standard call to get message count and if count=1, get the message
2192 FND_MSG_PUB.Count_And_Get (
2193 p_encoded => FND_API.G_FALSE,
2194 p_count => x_msg_count,
2195 p_data => x_msg_data
2196 );
2197
2198 WHEN OTHERS THEN
2199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2200 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2201 THEN
2202 FND_MSG_PUB.Add_Exc_Msg( 'VOLUME_OFFER_ADJ','adjust_new_products');
2203 END IF;
2204 -- Standard call to get message count and if count=1, get the message
2205 FND_MSG_PUB.Count_And_Get (
2206 p_encoded => FND_API.G_FALSE,
2207 p_count => x_msg_count,
2208 p_data => x_msg_data
2209 );
2210 END adjust_new_products;
2211
2212
2213 /**
2214 This procedure processes a given adjustment.
2215 It sequencially processes the discount changes made thru. the adjustment.
2216 Then it processes the new products added thru. the adjustment.
2217 */
2218 PROCEDURE process_vo_adjustments
2219 (
2220 p_init_msg_list IN VARCHAR2 := FND_API.g_false
2221 ,p_api_version IN NUMBER
2222 ,p_commit IN VARCHAR2 := FND_API.g_false
2223 ,x_return_status OUT NOCOPY VARCHAR2
2224 ,x_msg_count OUT NOCOPY NUMBER
2225 ,x_msg_data OUT NOCOPY VARCHAR2
2226 ,p_offerAdjustmentId IN NUMBER
2227 )
2228 IS
2229 BEGIN
2230 -- initialize
2231 -- process discount changes
2232 -- process product additions.
2233 x_return_status := FND_API.G_RET_STS_SUCCESS;
2234
2235 adjust_old_discounts
2236 (
2237 x_return_status => x_return_status
2238 ,x_msg_count => x_msg_count
2239 ,x_msg_data => x_msg_data
2240 ,p_offerAdjustmentId => p_offerAdjustmentId
2241 );
2242 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2243 RAISE FND_API.G_EXC_ERROR;
2244 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2246 END IF;
2247 adjust_new_products
2248 (
2249 x_return_status => x_return_status
2250 ,x_msg_count => x_msg_count
2251 ,x_msg_data => x_msg_data
2252 ,p_offerAdjustmentId => p_offerAdjustmentId
2253 );
2254 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2255 RAISE FND_API.G_EXC_ERROR;
2256 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2258 END IF;
2259 END process_vo_adjustments;
2260 END OZF_VOLUME_OFFER_ADJ;