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