[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_ADJ_PVT
Source
1 PACKAGE BODY OZF_OFFER_ADJ_PVT AS
2 /* $Header: ozfvoajb.pls 120.6.12000000.2 2007/03/21 10:12:03 nirprasa ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- Purpose
7 --
8 -- History
9 -- Mon Apr 03 2006:1/38 PM RSSHARMA Added procedure updateHeaderDate to update the start date
10 -- of the offer if the adjustment is a backdated adjustment.
11 -- Fri Aug 04 2006:3/36 PM RSSHARMA Fixed bug # 5439172. Pass start date also while end dating list lines for trade deal offers
12 -- Mon Aug 07 2006:7/56 PM RSSHARMA Fixed bug # 5439172. Fixed the getEndDate procedure
13 -- 3/21/2007 nirprasa Fixed bug # 5715744.
14 -- NOTE
15 --
16 -- End of Comments
17 -- ===============================================================
18 FUNCTION getAdjustmentId( p_offerAdjNewLineId IN NUMBER)
19 RETURN NUMBER
20 IS
21 CURSOR c_offerAdjustmentId(cp_offerAdjNewLineId NUMBER)
22 IS
23 SELECT offer_adjustment_id
24 FROM ozf_offer_adj_new_lines
25 WHERE offer_adj_new_line_id = cp_offerAdjNewLineId;
26 l_offerAdjustmentId NUMBER;
27 BEGIN
28 OPEN c_offerAdjustmentId(cp_offerAdjNewLineId => p_offerAdjNewLineId);
29 FETCH c_offerAdjustmentId INTO l_offerAdjustmentId;
30 IF c_offerAdjustmentId%NOTFOUND THEN
31 l_offerAdjustmentId := null;
32 END IF;
33 CLOSE c_offerAdjustmentId;
34 RETURN l_offerAdjustmentId;
35 END getAdjustmentId;
36
37 /**
38
39 */
40 FUNCTION getOfferType( p_offerAdjNewLineId NUMBER)
41 RETURN VARCHAR2
42 IS
43 CURSOR c_offerType(cp_offerAdjNewLineId NUMBER) IS
44 SELECT offer_type
45 FROM ozf_offers a, ozf_offer_adjustments_b b, ozf_offer_adj_new_lines c
46 WHERE
47 a.qp_list_header_id = b.list_header_id
48 AND b.offer_adjustment_id = c.offer_adjustment_id
49 AND c.offer_adj_new_line_id = cp_offerAdjNewLineId;
50 l_offerType VARCHAR2(30);
51 BEGIN
52 OPEN c_offerType(cp_offerAdjNewLineId => p_offerAdjNewLineId);
53 FETCH c_offerType INTO l_offerType;
54 IF c_offerType%NOTFOUND THEN
55 l_offerType := null;
56 END IF;
57 CLOSE c_offerType;
58 RETURN l_offerType;
59 END getOfferType;
60
61 /**
62 This function gets the modified discount for a list Line id
63 */
64 FUNCTION getModifiedDiscount
65 (
66 p_offerAdjustmentLineId IN NUMBER
67 )
68 RETURN NUMBER
69 IS
70 l_modifiedDiscount NUMBER;
71 CURSOR c_modifiedDiscount( cp_offerAdjustmentLineId NUMBER)IS
72 SELECT a.modified_discount
73 FROM ozf_offer_adjustment_lines a
74 WHERE a.offer_adjustment_line_id = cp_offerAdjustmentLineId;
75 BEGIN
76 OPEN c_modifiedDiscount(cp_offerAdjustmentLineId => p_offerAdjustmentLineId) ;
77 FETCH c_modifiedDiscount INTO l_modifiedDiscount;
78 IF c_modifiedDiscount%NOTFOUND THEN
79 l_modifiedDiscount := null;
80 END IF;
81 CLOSE C_modifiedDiscount;
82 RETURN l_modifiedDiscount;
83 END ;
84
85 FUNCTION getEffectiveDate
86 (
87 p_offerAdjustmentId NUMBER
88 )
89 RETURN DATE IS
90 l_effectiveDate DATE;
91 CURSOR c_effectiveDate(cp_offerAdjustmentId NUMBER) IS
92 SELECT effective_date
93 FROM ozf_offer_adjustments_b
94 WHERE offer_adjustment_id = cp_offerAdjustmentId;
95 BEGIN
96 l_effectiveDate := null;
97
98 OPEN c_effectiveDate(cp_offerAdjustmentId => p_offerAdjustmentId);
99 FETCH c_effectiveDate INTO l_effectiveDate;
100 IF c_effectiveDate%NOTFOUND THEN
101 l_effectiveDate := null;
102 END IF;
103 CLOSE c_effectiveDate;
104 return l_effectiveDate;
105 END getEffectiveDate;
106
107 FUNCTION getEndDate
108 (
109 p_offerAdjustmentId NUMBER
110 , p_listLineId NUMBER
111 )
112 RETURN DATE IS
113 l_headerStDt DATE := NULL;
114 l_lineStDt DATE := NULL;
115 l_effectiveDt DATE := NULL;
116 l_tmpDt DATE := NULL;
117 l_endDt DATE := NULL;
118 l_allDtNull VARCHAR2(1) := 'N';
119 BEGIN
120 select h.start_date_active
121 ,l.start_date_active
122 ,a.effective_date
123 INTO l_headerStDt
124 ,l_lineStDt
125 ,l_effectiveDt
126 FROM ozf_offer_adjustments_b a
127 ,qp_list_headers_b h
128 ,qp_list_lines l
129 WHERE a.list_header_id = h.list_header_id
130 and a.list_header_id = l.list_header_id
131 and l.list_line_id = p_listLineId
132 and a.offer_adjustment_id = p_offerAdjustmentId;
133 ozf_utility_pvt.debug_message('in getEndDate :-Adjustment Id:'||p_offerAdjustmentId || '-List Line Id:' || p_listLineId);
134
135 l_allDtNull := 'N';
136 IF (l_lineStDt is null) THEN
137 IF (l_headerStDt is null) THEN
138 l_tmpDt := l_effectiveDt;
139 l_allDtNull := 'Y';
140 ELSE
141 l_tmpDt := l_headerStDt;
142 END IF;
143 ELSE
144 l_tmpDt := l_lineStDt;
145 END IF;
146
147
148 IF ((l_tmpDt < l_effectiveDt) or (l_allDtNull = 'Y')) THEN
149 l_endDt := l_effectiveDt - 1;
150 ELSE
151 l_endDt := l_tmpDt;
152 END IF;
153 return l_endDt;
154 END getEndDate;
155
156
157 -------------------------------------------------------------------------------------------
158 -- Procedure :
159 -- Name : update_adj_lines
160 -- Updates the tiers and Discounts for Volume Offer tiers
161 -------------------------------------------------------------------------------------------
162
163 PROCEDURE createAdjLines
164 (
165 x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_count OUT NOCOPY NUMBER
167 ,x_msg_data OUT NOCOPY VARCHAR2
168 ,p_modifiers_tbl IN qp_modifiers_pub.modifiers_tbl_type
169 ,p_offerAdjustmentId IN NUMBER
170 )
171 IS
172 l_adj_line_rec OZF_Offer_Adj_Line_PVT.offadj_line_rec_type;
173 l_offer_adjustment_line_id NUMBER;
174 BEGIN
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176 -- loop thru the lines
177 -- for lines with operation = create , eleminate the dis lines for multi-tier
178 -- for these lines create adjustment_lines with created_from_adjustment= y
179
180 IF p_modifiers_tbl.COUNT > 0 THEN
181 FOR k IN p_modifiers_tbl.first..p_modifiers_tbl.last LOOP
182 IF p_modifiers_tbl.EXISTS(k) THEN
183
184 IF p_modifiers_tbl(k).operation <> 'CREATE' THEN
185 null;
186 ELSIF p_modifiers_tbl(k).list_line_type_code = 'DIS'
187 AND p_modifiers_tbl(k).modifier_parent_index <> FND_API.G_MISS_NUM
188 AND p_modifiers_tbl(k).modifier_parent_index IS NOT NULL
189 THEN
190 null;
191 ELSE
192 l_adj_line_rec := null;
193 l_adj_line_rec.offer_adjustment_id := p_offerAdjustmentId;
194 l_adj_line_rec.list_line_id := p_modifiers_tbl(k).list_line_id;
195 l_adj_line_rec.arithmetic_operator := p_modifiers_tbl(k).arithmetic_operator;
196 l_adj_line_rec.original_discount := p_modifiers_tbl(k).operand;
197 l_adj_line_rec.modified_discount := p_modifiers_tbl(k).operand;
198 l_adj_line_rec.list_header_id := p_modifiers_tbl(k).list_header_id;
199 l_adj_line_rec.created_from_adjustments := 'Y';
200 IF p_modifiers_tbl(k).list_line_type_code = 'PBH' THEN
201 l_adj_line_rec.modified_discount := -1;
202 l_adj_line_rec.original_discount := -1;
203 END IF;
204 OZF_Offer_Adj_Line_PVT.Create_Offer_Adj_Line(
205 p_api_version_number => 1.0
206 , p_init_msg_list => FND_API.G_FALSE
207 , p_commit => FND_API.G_FALSE
208 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
209 , x_return_status => x_return_status
210 , x_msg_count => x_msg_count
211 , x_msg_data => x_msg_data
212 -- p_offadj_line_rec IN offadj_line_rec_type := g_miss_offadj_line_rec,
213 , p_offadj_line_rec => l_adj_line_rec
214 , x_offer_adjustment_line_id => l_offer_adjustment_line_id
215 );
216
217 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
218 return;
219 END IF;
220 END IF;
221 END IF;
222 END LOOP;
223 END IF;
224 END createAdjLines;
225
226
227
228 PROCEDURE populateDisLines
229 (
230 x_return_status OUT NOCOPY VARCHAR2
231 ,x_msg_count OUT NOCOPY NUMBER
232 ,x_msg_data OUT NOCOPY VARCHAR2
233 ,p_pbhListLineId IN NUMBER
234 ,x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
235 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
236 )
237 IS
238 CURSOR c_disLines(cp_pbhListLineId NUMBER) IS
239 SELECT to_rltd_modifier_id
240 FROM qp_rltd_modifiers
241 WHERE from_rltd_modifier_id = cp_pbhListLineId
242 ORDER BY to_rltd_modifier_id asc;
243 i NUMBER;
244 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
245
246 BEGIN
247 -- initialize
248 -- loop thru. dis lines
249 -- populate discounts
250 -- populate pricing_attributes
251 -- merge pricing_attributes
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253 x_modifier_line_tbl.delete;
254 x_pricing_attr_tbl.delete;
255 l_pricing_attr_tbl.delete;
256 i := 2;
257 -- this method assumes that the pbh has been poopulated at index 1
258 FOR l_disLines IN c_disLines(cp_pbhListLineId => p_pbhListLineId) LOOP
259 l_pricing_attr_tbl.delete;
260 x_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
261 x_modifier_line_tbl(i).list_line_type_code := 'DIS';
262 x_modifier_line_tbl(i).start_date_active := null;
263 x_modifier_line_tbl(i).rltd_modifier_grp_type := 'PRICE BREAK';
264 x_modifier_line_tbl(i).rltd_modifier_grp_no := 1;
265 x_modifier_line_tbl(i).modifier_parent_index := 1;
266 OZF_VOLUME_OFFER_ADJ.populate_discounts
267 (
268 x_modifiers_rec => x_modifier_line_tbl(i)
269 , p_list_line_id => l_disLines.to_rltd_modifier_id
270 );
271 OZF_VOLUME_OFFER_ADJ.populate_pricing_attributes
272 (
273 x_pricing_attr_tbl => l_pricing_attr_tbl
274 , p_list_line_id => l_disLines.to_rltd_modifier_id
275 , p_index => i
276 );
277 OZF_VOLUME_OFFER_ADJ.merge_pricing_attributes
278 (
279 px_to_pricing_attr_tbl => x_pricing_attr_tbl
280 , p_from_pricing_attr_tbl => l_pricing_attr_tbl
281 );
282 i := i + 1;
283 END LOOP;
284 END populateDisLines;
285
286 PROCEDURE populatePbhStructure
287 (
288 x_return_status OUT NOCOPY VARCHAR2
289 ,x_msg_count OUT NOCOPY NUMBER
290 ,x_msg_data OUT NOCOPY VARCHAR2
291 ,p_pbhListLineId IN NUMBER
292 ,x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
293 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
294 )
295 IS
296 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
297 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
298 BEGIN
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300 OZF_VOLUME_OFFER_ADJ.populate_pbh_line
301 (
302 x_return_status => x_return_status
303 , x_msg_count => x_msg_count
304 , x_msg_data => x_msg_data
305 , p_listLineId => p_pbhListLineId
306 , x_modifier_line_tbl => x_modifier_line_tbl
307 , x_pricing_attr_tbl => x_pricing_attr_tbl
308 );
309 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
310 RAISE FND_API.G_EXC_ERROR;
311 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
313 END IF;
314 populateDisLines
315 (
316 x_return_status => x_return_status
317 , x_msg_count => x_msg_count
318 , x_msg_data => x_msg_data
319 , p_pbhListLineId => p_pbhListLineId
320 , x_modifier_line_tbl => l_modifier_line_tbl
321 , x_pricing_attr_tbl => l_pricing_attr_tbl
322 );
323 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
324 RAISE FND_API.G_EXC_ERROR;
325 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END IF;
328
329 OZF_VOLUME_OFFER_ADJ.merge_pricing_attributes
330 (
331 px_to_pricing_attr_tbl => x_pricing_attr_tbl
332 , p_from_pricing_attr_tbl => l_pricing_attr_tbl
333 );
334 OZF_VOLUME_OFFER_ADJ.merge_modifiers
335 (
336 px_to_modifier_line_tbl => x_modifier_line_tbl
337 , p_from_modifier_line_tbl => l_modifier_line_tbl
338 );
339
340 END populatePbhStructure;
341
342 /**
343 This procedure populates the discounts and start date into a PBH discount structure.
344 Since there are no keys to actually link the discounts to the structure, order by asc on to_rltd_modifier_id
345 in qp_rltd_modifiers is used during populating the original discount structure and here also, which serves as
346 the link
347 */
348 PROCEDURE processPbhStructure
349 (
350 x_return_status OUT NOCOPY VARCHAR2
351 ,x_msg_count OUT NOCOPY NUMBER
352 ,x_msg_data OUT NOCOPY VARCHAR2
353 ,p_pbhListLineId IN NUMBER
354 , p_offerAdjustmentId IN NUMBER
355 ,px_modifier_line_tbl IN OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
356 , px_pricing_attr_tbl IN OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
357 )
358 IS
359 CURSOR c_discounts(cp_offerAdjustmentId NUMBER, cp_pbhListLineId NUMBER) IS
360 SELECT
361 nvl(a.modified_discount,c.operand) discount
362 FROM
363 ozf_offer_adjustment_lines a, qp_rltd_modifiers b, qp_list_lines c
364 WHERE
365 a.list_line_id (+) = b.to_rltd_modifier_id
366 AND b.to_rltd_modifier_id = c.list_line_id
367 AND b.from_rltd_modifier_id = cp_pbhListLineId
368 AND a.offer_adjustment_id (+) = cp_offerAdjustmentId
369 ORDER BY b.to_rltd_modifier_id asc;
370 i NUMBER;
371 BEGIN
372 x_return_status := FND_API.G_RET_STS_SUCCESS;
373 i := 2;
374 FOR l_discounts IN c_discounts(cp_offerAdjustmentId => p_offerAdjustmentId, cp_pbhListLineId => p_pbhListLineId) LOOP
375 px_modifier_line_tbl(i).operand := l_discounts.discount;
376 i := i + 1;
377 END LOOP;
378 px_modifier_line_tbl(1).start_date_active := getEffectiveDate(p_offerAdjustmentId => p_offerAdjustmentId);
379 END processPbhStructure;
380
381
382 /**
383 Populates a limits record with limits data for a given limitId
384 */
385 PROCEDURE populate_limits_rec
386 (
387 x_limits_rec OUT NOCOPY QP_Limits_PUB.Limits_Rec_Type
388 , x_return_status OUT NOCOPY VARCHAR2
389 , p_limitId IN NUMBER
390 )
391 IS
392 CURSOR c_limits(cp_limitId NUMBER)
393 IS
394 SELECT *
395 FROM qp_limits
396 WHERE limit_id = cp_limitId;
397 BEGIN
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399 x_limits_rec := null;
400 FOR l_limits IN c_limits(cp_limitId => p_limitId) LOOP
401 x_limits_rec.attribute1 := l_limits.attribute1 ;
402 x_limits_rec.attribute10 := l_limits.attribute10 ;
403 x_limits_rec.attribute11 := l_limits.attribute11 ;
404 x_limits_rec.attribute12 := l_limits.attribute12 ;
405 x_limits_rec.attribute13 := l_limits.attribute13 ;
406 x_limits_rec.attribute14 := l_limits.attribute14 ;
407 x_limits_rec.attribute15 := l_limits.attribute15 ;
408 x_limits_rec.attribute2 := l_limits.attribute2 ;
409 x_limits_rec.attribute3 := l_limits.attribute3 ;
410 x_limits_rec.attribute4 := l_limits.attribute4 ;
411 x_limits_rec.attribute5 := l_limits.attribute5 ;
412 x_limits_rec.attribute6 := l_limits.attribute6 ;
413 x_limits_rec.attribute7 := l_limits.attribute7 ;
414 x_limits_rec.attribute8 := l_limits.attribute8 ;
415 x_limits_rec.attribute9 := l_limits.attribute9 ;
416 x_limits_rec.context := l_limits.context ;
417 x_limits_rec.limit_id := FND_API.G_MISS_NUM;
418 x_limits_rec.multival_attr1_type := l_limits.multival_attr1_type ;
419 x_limits_rec.multival_attr1_context := l_limits.multival_attr1_context ;
420 x_limits_rec.multival_attribute1 := l_limits.multival_attribute1 ;
421 x_limits_rec.multival_attr1_datatype := l_limits.multival_attr1_datatype ;
422 x_limits_rec.multival_attr2_type := l_limits.multival_attr2_type ;
423 x_limits_rec.multival_attr2_context := l_limits.multival_attr2_context ;
424 x_limits_rec.multival_attribute2 := l_limits.multival_attribute2 ;
425 x_limits_rec.multival_attr2_datatype := l_limits.multival_attr2_datatype ;
426
427 x_limits_rec.amount := l_limits.amount ;
428 x_limits_rec.limit_hold_flag := l_limits.limit_hold_flag ;
429 x_limits_rec.organization_flag := l_limits.organization_flag ;
430 x_limits_rec.operation := QP_GLOBALS.G_OPR_CREATE;
431 x_limits_rec.limit_level_code := l_limits.limit_level_code ;
432 x_limits_rec.basis := l_limits.basis ;
433 x_limits_rec.limit_number := l_limits.limit_number ;
434 x_limits_rec.limit_exceed_action_code:= FND_PROFILE.value('QP_LIMIT_EXCEED_ACTION');
435 --x_limits_rec.list_header_id := l_limits.list_header_id ;
436 END LOOP;
437 END populate_limits_rec;
438
439 /**
440 Populates additional information related to a list_line_id into a limits record.
441 */
442 PROCEDURE processLimitsRec
443 (
444 x_return_status OUT NOCOPY VARCHAR2
445 , x_msg_count OUT NOCOPY NUMBER
446 , x_msg_data OUT NOCOPY VARCHAR2
447 , px_limitsRec IN OUT NOCOPY QP_Limits_PUB.Limits_Rec_Type
448 , p_toListLineId IN NUMBER
449 )
450 IS
451 CURSOR c_listHeaderId (cp_listLineId NUMBER) IS
452 SELECT list_header_id
453 FROM qp_list_lines
454 WHERE list_line_id = cp_listLineId;
455 l_listHeaderId NUMBER;
456 BEGIN
457 x_return_status := FND_API.G_RET_STS_SUCCESS;
458 OPEN c_listHeaderId(cp_listLineId => p_toListLineId);
459 FETCH c_listHeaderId INTO l_listHeaderId;
460 CLOSE c_listHeaderId;
461 IF px_limitsRec.OPERATION IS NOT NULL OR px_limitsRec.OPERATION <> FND_API.G_MISS_CHAR THEN
462 px_limitsRec.list_line_id := p_toListLineId;
463 px_limitsRec.list_header_id := l_listHeaderId;
464 END IF;
465 END processLimitsRec;
466
467 /**
468 Copies limits data for a list_line_id into a new list_line_id
469 */
470 PROCEDURE copyLimits
471 (
472 x_return_status OUT NOCOPY VARCHAR2
473 , x_msg_count OUT NOCOPY NUMBER
474 , x_msg_data OUT NOCOPY VARCHAR2
475 ,p_fromListLineId IN NUMBER
476 , p_toListLineId IN NUMBER
477 )
478 IS
479 l_limitsRec QP_Limits_PUB.Limits_Rec_Type;
480 v_limits_rec QP_Limits_PUB.Limits_Rec_Type;
481 v_limits_val_rec QP_Limits_PUB.Limits_Val_Rec_Type;
482 v_limit_attrs_tbl QP_Limits_PUB.Limit_Attrs_Tbl_Type;
483 v_limit_attrs_val_tbl QP_Limits_PUB.Limit_Attrs_Val_Tbl_Type;
484 v_limit_balances_tbl QP_Limits_PUB.Limit_Balances_Tbl_Type;
485 v_limit_balances_val_tbl QP_Limits_PUB.Limit_Balances_Val_Tbl_Type;
486 BEGIN
487 -- initialize
488 -- populate limits records
489 -- process the limits records
490 -- create limits
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 FOR l_limits in (SELECT limit_id, list_line_id FROM qp_limits WHERE list_line_id = p_fromListLineId) LOOP
494 populate_limits_rec
495 (
496 x_limits_rec => l_limitsRec
497 , x_return_status => x_return_status
498 , p_limitId => l_limits.limit_id
499 );
500 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
501 RAISE FND_API.G_EXC_ERROR;
502 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505 processLimitsRec
506 (
507 x_return_status => x_return_status
508 , x_msg_count => x_msg_count
509 , x_msg_data => x_msg_data
510 , px_limitsRec => l_limitsRec
511 , p_toListLineId => p_toListLineId
512 );
513 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
514 RAISE FND_API.G_EXC_ERROR;
515 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
517 END IF;
518 QP_Limits_PUB.Process_Limits
519 ( p_init_msg_list => FND_API.g_true,
520 p_api_version_number => 1.0,
521 p_commit => FND_API.g_false,
522 x_return_status => x_return_status,
523 x_msg_count => x_msg_count,
524 x_msg_data => x_msg_data,
525 p_LIMITS_rec => l_limitsRec,
526 x_LIMITS_rec => v_LIMITS_rec,
527 x_LIMITS_val_rec => v_LIMITS_val_rec,
528 x_LIMIT_ATTRS_tbl => v_LIMIT_ATTRS_tbl,
529 x_LIMIT_ATTRS_val_tbl => v_LIMIT_ATTRS_val_tbl,
530 x_LIMIT_BALANCES_tbl => v_LIMIT_BALANCES_tbl,
531 x_LIMIT_BALANCES_val_tbl => v_LIMIT_BALANCES_val_tbl
532 );
533 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
534 return;
535 END IF;
536 END LOOP;
537
538 END copyLimits;
539
540 /**
541 Populates a qualifiers recors with Qualifier data for a given list_line_id
542 */
543 PROCEDURE populateQualifiers
544 (
545 x_qualifiers_tbl OUT NOCOPY QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
546 , p_listLineId IN NUMBER
547 )
548 IS
549 CURSOR c_qualifiers(cp_listLineId NUMBER)
550 IS
551 SELECT
552 qualifier_id
553 , qualifier_grouping_no
554 , qualifier_context
555 , qualifier_attribute
556 , qualifier_attr_value
557 , comparison_operator_code
558 , excluder_flag
559 , start_date_active
560 , end_date_active
561 , qualifier_precedence
562 , list_header_id
563 , list_line_id
564 , qualifier_attr_value_to
565 , context
566 , attribute1
567 , attribute2
568 , attribute3
569 , attribute4
570 , attribute5
571 , attribute6
572 , attribute7
573 , attribute8
574 , attribute9
575 , attribute10
576 , attribute11
577 , attribute12
578 , attribute13
579 , attribute14
580 , attribute15
581 , active_flag
582 FROM qp_qualifiers
583 WHERE list_line_id = cp_listLineId;
584 i NUMBER;
585 BEGIN
586 --x_return_status := FND_API.G_RET_STS_SUCCESS;
587 x_qualifiers_tbl.delete;
588 i := 1;
589 FOR l_qualifiers in c_qualifiers(cp_listLineId => p_listLineId) LOOP
590 x_qualifiers_tbl(i).qualifier_grouping_no := l_qualifiers.qualifier_grouping_no;
591 x_qualifiers_tbl(i).qualifier_context := l_qualifiers.qualifier_context;
592 x_qualifiers_tbl(i).qualifier_attribute := l_qualifiers.qualifier_attribute;
593 x_qualifiers_tbl(i).qualifier_attr_value := l_qualifiers.qualifier_attr_value;
594 x_qualifiers_tbl(i).comparison_operator_code:= l_qualifiers.comparison_operator_code;
595 x_qualifiers_tbl(i).excluder_flag := l_qualifiers.excluder_flag;
596 x_qualifiers_tbl(i).start_date_active := l_qualifiers.start_date_active;
597 x_qualifiers_tbl(i).end_date_active := l_qualifiers.end_date_active;
598 x_qualifiers_tbl(i).qualifier_precedence := l_qualifiers.qualifier_precedence;
599 x_qualifiers_tbl(i).list_header_id := l_qualifiers.list_header_id;
600 x_qualifiers_tbl(i).qualifier_attr_value_to := l_qualifiers.qualifier_attr_value_to;
601 x_qualifiers_tbl(i).context := l_qualifiers.context;
602 x_qualifiers_tbl(i).attribute1 := l_qualifiers.attribute1;
603 x_qualifiers_tbl(i).attribute2 := l_qualifiers.attribute2;
604 x_qualifiers_tbl(i).attribute3 := l_qualifiers.attribute3;
605 x_qualifiers_tbl(i).attribute4 := l_qualifiers.attribute4;
606 x_qualifiers_tbl(i).attribute5 := l_qualifiers.attribute5;
607 x_qualifiers_tbl(i).attribute6 := l_qualifiers.attribute6;
608 x_qualifiers_tbl(i).attribute7 := l_qualifiers.attribute7;
609 x_qualifiers_tbl(i).attribute8 := l_qualifiers.attribute8;
610 x_qualifiers_tbl(i).attribute9 := l_qualifiers.attribute9;
611 x_qualifiers_tbl(i).attribute10 := l_qualifiers.attribute10;
612 x_qualifiers_tbl(i).attribute11 := l_qualifiers.attribute11;
613 x_qualifiers_tbl(i).attribute12 := l_qualifiers.attribute12;
614 x_qualifiers_tbl(i).attribute13 := l_qualifiers.attribute13;
615 x_qualifiers_tbl(i).attribute14 := l_qualifiers.attribute14;
616 x_qualifiers_tbl(i).attribute15 := l_qualifiers.attribute15;
617 x_qualifiers_tbl(i).active_flag := l_qualifiers.active_flag;
618 i := i + 1;
619 END LOOP;
620 END populateQualifiers;
621 PROCEDURE processQualifierTable
622 (
623 px_qualifiers_tbl IN OUT NOCOPY QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
624 , p_listLineId IN NUMBER
625 )
626 IS
627 BEGIN
628 --x_return_status := FND_API.G_RET_STS_SUCCESS;
629 IF nvl(px_qualifiers_tbl.count,0) > 0 THEN
630 FOR i in px_qualifiers_tbl.first .. px_qualifiers_tbl.last LOOP
631 IF px_qualifiers_tbl.exists(i) THEN
632 px_qualifiers_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
633 px_qualifiers_tbl(i).list_line_id := p_listLineId;
634 END IF;
635 END LOOP;
636 END IF;
637
638 END processQualifierTable;
639
640 /**
641 Copies qualifiers of a qp list_line into a new qp list_line
642 */
643 PROCEDURE copyQualifiers
644 (
645 x_return_status OUT NOCOPY VARCHAR2
646 , x_msg_count OUT NOCOPY NUMBER
647 , x_msg_data OUT NOCOPY VARCHAR2
648 , p_fromListLineId IN NUMBER
649 , p_toListLineId IN NUMBER
650 )
651 IS
652 l_qualifiers_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
653 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
654 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
655 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
656 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
657 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
658 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
659 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
660 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
661 l_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
662 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
663 BEGIN
664 --initialize
665 -- populate qualifiers
666 -- populate the new list line into the qualifiers
667 -- create qualifiers
668 x_return_status := FND_API.G_RET_STS_SUCCESS;
669 l_qualifiers_tbl.delete;
670 populateQualifiers
671 (
672 x_qualifiers_tbl => l_qualifiers_tbl
673 , p_listLineId => p_fromListLineId
674 );
675 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
676 RAISE FND_API.G_EXC_ERROR;
677 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
679 END IF;
680 processQualifierTable
681 (
682 px_qualifiers_tbl => l_qualifiers_tbl
683 , p_listLineId => p_toListLineId
684 );
685 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
686 RAISE FND_API.G_EXC_ERROR;
687 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
689 END IF;
690 QP_Modifiers_PUB.process_modifiers(
691 p_api_version_number => 1.0,
692 p_init_msg_list => FND_API.G_FALSE,
693 p_return_values => FND_API.G_FALSE,
694 x_return_status => x_return_status,
695 x_msg_count => x_msg_count,
696 x_msg_data => x_msg_data,
697 p_qualifiers_tbl => l_qualifiers_tbl,
698 x_modifier_list_rec => v_modifier_list_rec,
699 x_modifier_list_val_rec => v_modifier_list_val_rec,
700 x_modifiers_tbl => v_modifiers_tbl,
701 x_modifiers_val_tbl => v_modifiers_val_tbl,
702 x_qualifiers_tbl => v_qualifiers_tbl,
703 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
704 x_pricing_attr_tbl => v_pricing_attr_tbl,
705 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
706 );
707 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
708 RAISE FND_API.G_EXC_ERROR;
709 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711 END IF;
712 END copyQualifiers;
713
714 /**
715 COpies qualifiers from old list_line_id to a new list_line_id for records in a table.
716 Note the procedure assumes that the old list_line_id is populated in comments column
717 and the new list_line_id is populated in the list_line_id column.
718 */
719 PROCEDURE copyQualifier
720 (
721 x_return_status OUT NOCOPY VARCHAR2
722 ,x_msg_count OUT NOCOPY NUMBER
723 ,x_msg_data OUT NOCOPY VARCHAR2
724 ,p_modifier_line_tbl IN qp_modifiers_pub.modifiers_tbl_type
725 )
726 IS
727 BEGIN
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729 IF nvl(p_modifier_line_tbl.count,0) > 0 THEN
730 FOR i in p_modifier_line_tbl.first .. p_modifier_line_tbl.last LOOP
731 IF p_modifier_line_tbl.exists(i) THEN
732 copyQualifiers
733 (
734 x_return_status => x_return_status
735 , x_msg_count => x_msg_count
736 , x_msg_data => x_msg_data
737 , p_fromListLineId => to_number( p_modifier_line_tbl(i).comments)
738 , p_toListLineId => p_modifier_line_tbl(i).list_line_id
739 );
740 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
741 return;
742 END IF;
743 END IF;
744 END LOOP;
745 END IF;
746 END copyQualifier;
747
748 /**
749 COpies Limits from old list_line_id to a new list_line_id for records in a table.
750 Note the procedure assumes that the old list_line_id is populated in comments column
751 and the new list_line_id is populated in the list_line_id column.
752 */
753 PROCEDURE copyLimit
754 (
755 x_return_status OUT NOCOPY VARCHAR2
756 , x_msg_count OUT NOCOPY NUMBER
757 , x_msg_data OUT NOCOPY VARCHAR2
758 ,p_modifier_line_tbl IN qp_modifiers_pub.modifiers_tbl_type
759 )
760 IS
761 BEGIN
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763 IF nvl(p_modifier_line_tbl.count,0) > 0 THEN
764 FOR i in p_modifier_line_tbl.first .. p_modifier_line_tbl.last LOOP
765 IF p_modifier_line_tbl.exists(i) THEN
766 copyLimits
767 (
768 x_return_status => x_return_status
769 , x_msg_count => x_msg_count
770 , x_msg_data => x_msg_data
771 , p_fromListLineId => to_number( p_modifier_line_tbl(i).comments)
772 , p_toListLineId => p_modifier_line_tbl(i).list_line_id
773 );
774 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
775 return;
776 END IF;
777 END IF;
778 END LOOP;
779 END IF;
780
781 END copyLimit;
782
783 /**
784 Copies a details of a pbh line into a new pbh line
785 */
786 PROCEDURE copyPbhLine
787 (
788 x_return_status OUT NOCOPY VARCHAR2
789 ,x_msg_count OUT NOCOPY NUMBER
790 ,x_msg_data OUT NOCOPY VARCHAR2
791 ,p_pbhListLineId IN NUMBER
792 ,p_offerAdjustmentId IN NUMBER
793 ,x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
794 )
795 IS
796
797 l_pbhLineId NUMBER;
798 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
799 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
800
801 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
802 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
803 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
804 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
805 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
806 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
807 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
808 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
809 BEGIN
810 -- initialise
811 -- populate lines
812 -- copy list line
813 -- copy qualifiers
814 -- copy limits
815 -- return created lines
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817 x_modifier_line_tbl.delete;
818 populatePbhStructure
819 (
820 x_return_status => x_return_status
821 ,x_msg_count => x_msg_count
822 ,x_msg_data => x_msg_data
823 ,p_pbhListLineId => p_pbhListLineId
824 ,x_modifier_line_tbl => l_modifier_line_tbl
825 , x_pricing_attr_tbl => l_pricing_attr_tbl
826 );
827 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
828 RAISE FND_API.G_EXC_ERROR;
829 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
831 END IF;
832 processPbhStructure
833 (
834 x_return_status => x_return_status
835 ,x_msg_count => x_msg_count
836 ,x_msg_data => x_msg_data
837 ,p_pbhListLineId => p_pbhListLineId
838 ,p_offerAdjustmentId => p_offerAdjustmentId
839 ,px_modifier_line_tbl => l_modifier_line_tbl
840 , px_pricing_attr_tbl => l_pricing_attr_tbl
841 );
842 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
843 RAISE FND_API.G_EXC_ERROR;
844 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 END IF;
847 QP_Modifiers_PUB.process_modifiers(
848 p_api_version_number => 1.0,
849 p_init_msg_list => FND_API.G_FALSE,
850 p_return_values => FND_API.G_FALSE,
851 x_return_status => x_return_status,
852 x_msg_count => x_msg_count,
853 x_msg_data => x_msg_data,
854 p_modifiers_tbl => l_modifier_line_tbl,
855 p_pricing_attr_tbl => l_pricing_attr_tbl,
856 x_modifier_list_rec => v_modifier_list_rec,
857 x_modifier_list_val_rec => v_modifier_list_val_rec,
858 x_modifiers_tbl => v_modifiers_tbl,
859 x_modifiers_val_tbl => v_modifiers_val_tbl,
860 x_qualifiers_tbl => v_qualifiers_tbl,
861 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
862 x_pricing_attr_tbl => v_pricing_attr_tbl,
863 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
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 copyQualifier
871 (
872 x_return_status => x_return_status
873 ,x_msg_count => x_msg_count
874 ,x_msg_data => x_msg_data
875 ,p_modifier_line_tbl => v_modifiers_tbl
876 );
877 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
878 RAISE FND_API.G_EXC_ERROR;
879 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
881 END IF;
882 copyLimit
883 (
884 x_return_status => x_return_status
885 ,x_msg_count => x_msg_count
886 ,x_msg_data => x_msg_data
887 ,p_modifier_line_tbl => v_modifiers_tbl
888 );
889 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
890 RAISE FND_API.G_EXC_ERROR;
891 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 END IF;
894 x_modifier_line_tbl := v_modifiers_tbl;
895 END copyPbhLine;
896
897 PROCEDURE processOldPbhLines
898 (
899 x_return_status OUT NOCOPY VARCHAR2
900 ,x_msg_count OUT NOCOPY NUMBER
901 ,x_msg_data OUT NOCOPY VARCHAR2
902 ,p_offerAdjustmentId IN NUMBER
903 )
904 IS
905 l_phbLineId NUMBER;
906 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
907 CURSOR c_pbhLines(cp_offerAdjustmentId NUMBER) IS
908 SELECT distinct b.from_rltd_modifier_id
909 FROM ozf_offer_adjustment_lines a, qp_rltd_modifiers b
910 WHERE a.list_line_id = b.to_rltd_modifier_id
911 AND a.offer_adjustment_id = cp_offerAdjustmentId;
912 BEGIN
913 -- initialize
914 -- end date line
915 -- copy line
916 -- relate lines
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918 FOR l_pbhLines IN c_pbhLines(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
919 OZF_VOLUME_OFFER_ADJ.end_qp_line
920 (
921 x_return_status => x_return_status
922 , x_msg_count => x_msg_count
923 , x_msg_data => x_msg_data
924 , p_listLineId => l_pbhLines.from_rltd_modifier_id
925 ,p_offerAdjustmentId => p_offerAdjustmentId
926 );
927 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
928 RAISE FND_API.G_EXC_ERROR;
929 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932 copyPbhLine
933 (
934 x_return_status => x_return_status
935 ,x_msg_count => x_msg_count
936 ,x_msg_data => x_msg_data
937 ,p_offerAdjustmentId => p_offerAdjustmentId
938 , p_pbhListLineId => l_pbhLines.from_rltd_modifier_id
939 ,x_modifier_line_tbl => l_modifier_line_tbl
940 );
941 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
942 RAISE FND_API.G_EXC_ERROR;
943 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945 END IF;
946
947 OZF_VOLUME_OFFER_ADJ.relate_lines
948 (
949 p_modifiers_tbl => l_modifier_line_tbl
950 , p_offer_adjustment_id => p_offerAdjustmentId
951 , x_return_status => x_return_status
952 , x_msg_count => x_msg_count
953 , x_msg_data => x_msg_data
954 );
955 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
956 RAISE FND_API.G_EXC_ERROR;
957 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
959 END IF;
960 END LOOP;
961 EXCEPTION
962 WHEN OTHERS THEN
963 FND_MSG_PUB.count_and_get(
964 p_encoded => FND_API.g_false
965 , p_count => x_msg_count
966 , p_data => x_msg_data
967 );
968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
969
970 END processOldPbhLines;
971
972
973 /**
974 Given the list line Id, this procedure populates the list_line details and pricing attribute details
975 in the modifier table and pricing attributes table respectively
976 Note the assumption here is that the input list_line_id is the primary list_line_id/
977 ie. for Multi-tier discount it is the PBH line id.
978 */
979 PROCEDURE populate_dis_qp_data
980 (
981 x_return_status OUT NOCOPY VARCHAR2
982 , x_msg_count OUT NOCOPY NUMBER
983 , x_msg_data OUT NOCOPY VARCHAR2
984 , p_listLineId IN NUMBER
985 , x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
986 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
987 )
988 IS
989 CURSOR c_listLineDetails(cp_listLineId NUMBER) IS
990 SELECT
991 list_line_id
992 FROM qp_list_lines
993 WHERE list_line_id = cp_listLineId;
994 i NUMBER;
995 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
996 BEGIN
997 -- initialize
998 -- loop thru. list Lines
999 -- populate list_lines progressively
1000 -- populate the new discounts and start date
1001 -- populate pricing_attributes
1002 -- merge pricing attributes -> required, since each pricing_attribute can have multiple pricing attributes
1003 -- so the API returning pricing attributes returns a table, each time you get a table it has to be merged with
1004 -- existing table
1005 x_return_status := FND_API.G_RET_STS_SUCCESS;
1006 x_modifier_line_tbl.delete;
1007 x_pricing_attr_tbl.delete;
1008 l_pricing_attr_tbl.delete;
1009 i := 1;
1010 FOR l_listLineDetails in c_listLineDetails(cp_listLineId => p_listLineId) LOOP
1011 l_pricing_attr_tbl.delete;
1012 x_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
1013 x_modifier_line_tbl(i).automatic_flag := 'Y';
1014 OZF_VOLUME_OFFER_ADJ.populate_discounts
1015 (
1016 x_modifiers_rec => x_modifier_line_tbl(i)
1017 , p_list_line_id => l_listLineDetails.list_line_id
1018 );
1019 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1020 RAISE FND_API.G_EXC_ERROR;
1021 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024 OZF_VOLUME_OFFER_ADJ.populate_pricing_attributes
1025 (
1026 x_pricing_attr_tbl => l_pricing_attr_tbl
1027 , p_list_line_id => p_listLineId
1028 , p_index => i
1029 );
1030 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1031 RAISE FND_API.G_EXC_ERROR;
1032 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1034 END IF;
1035 OZF_VOLUME_OFFER_ADJ.merge_pricing_attributes
1036 (
1037 px_to_pricing_attr_tbl => x_pricing_attr_tbl
1038 , p_from_pricing_attr_tbl => l_pricing_attr_tbl
1039 );
1040 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1041 RAISE FND_API.G_EXC_ERROR;
1042 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1044 END IF;
1045 i := i + 1;
1046 END LOOP;
1047 END populate_dis_qp_data;
1048
1049 PROCEDURE populate_adjustments_data
1050 (
1051 x_return_status OUT NOCOPY VARCHAR2
1052 ,x_msg_count OUT NOCOPY NUMBER
1053 ,x_msg_data OUT NOCOPY VARCHAR2
1054 ,x_modifier_line_tbl IN OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
1055 ,p_offerAdjustmentId IN NUMBER
1056 ,p_offerAdjustmentLineId IN NUMBER
1057 )
1058 IS
1059 BEGIN
1060 x_return_status := FND_API.G_RET_STS_SUCCESS;
1061 FOR i in x_modifier_line_tbl.first .. x_modifier_line_tbl.last LOOP
1062 IF nvl(x_modifier_line_tbl.count,0) > 0 THEN
1063 IF x_modifier_line_tbl.exists(i) THEN
1064 x_modifier_line_tbl(i).operand := getModifiedDiscount(p_offerAdjustmentLineId => p_offerAdjustmentLineId );
1065 x_modifier_line_tbl(i).start_date_active:= getEffectiveDate(p_offerAdjustmentId => p_offerAdjustmentId);
1066 END IF;
1067 END IF;
1068 END LOOP;
1069 END populate_adjustments_data;
1070 /**
1071 Creates a copy of a QP list line
1072 */
1073 PROCEDURE create_dis_line
1074 (
1075 x_return_status OUT NOCOPY VARCHAR2
1076 ,x_msg_count OUT NOCOPY NUMBER
1077 ,x_msg_data OUT NOCOPY VARCHAR2
1078 ,p_listLineId IN NUMBER
1079 ,x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
1080 ,x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
1081 , x_listLineId OUT NOCOPY NUMBER
1082 , p_offerAdjustmentLineId IN NUMBER
1083 , p_offerAdjustmentId IN NUMBER
1084 )
1085 IS
1086 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
1087 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
1088 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
1089 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
1090 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1091 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1092 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1093 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
1094 l_modifier_line_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
1095 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1096 BEGIN
1097 -- initialize
1098 -- populate data from qp_list_line
1099 -- populate adjustments data
1100 -- create qp data
1101 -- return created tables and pricing attributes
1102 x_return_status := FND_API.G_RET_STS_SUCCESS;
1103 l_modifier_line_tbl.delete;
1104 l_pricing_attr_tbl.delete;
1105
1106 populate_dis_qp_data
1107 (
1108 x_return_status => x_return_status
1109 , x_msg_count => x_msg_count
1110 , x_msg_data => x_msg_data
1111 , p_listLineId => p_listLineId
1112 , x_modifier_line_tbl => x_modifier_line_tbl
1113 , x_pricing_attr_tbl => x_pricing_attr_tbl
1114 );
1115 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1116 RAISE FND_API.G_EXC_ERROR;
1117 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1119 END IF;
1120 populate_adjustments_data
1121 (
1122 x_return_status => x_return_status
1123 ,x_msg_count => x_msg_count
1124 ,x_msg_data => x_msg_data
1125 ,x_modifier_line_tbl => x_modifier_line_tbl
1126 ,p_offerAdjustmentId => p_offerAdjustmentId
1127 ,p_offerAdjustmentLineId => p_offerAdjustmentLineId
1128 );
1129 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1130 RAISE FND_API.G_EXC_ERROR;
1131 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1133 END IF;
1134
1135 QP_Modifiers_PUB.process_modifiers(
1136 p_api_version_number => 1.0,
1137 p_init_msg_list => FND_API.G_FALSE,
1138 p_return_values => FND_API.G_FALSE,
1139 x_return_status => x_return_status,
1140 x_msg_count => x_msg_count,
1141 x_msg_data => x_msg_data,
1142 p_modifiers_tbl => x_modifier_line_tbl,
1143 p_pricing_attr_tbl => x_pricing_attr_tbl,
1144 x_modifier_list_rec => v_modifier_list_rec,
1145 x_modifier_list_val_rec => v_modifier_list_val_rec,
1146 x_modifiers_tbl => v_modifiers_tbl,
1147 x_modifiers_val_tbl => v_modifiers_val_tbl,
1148 x_qualifiers_tbl => v_qualifiers_tbl,
1149 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
1150 x_pricing_attr_tbl => v_pricing_attr_tbl,
1151 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
1152 );
1153 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1154 RAISE FND_API.G_EXC_ERROR;
1155 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1157 END IF;
1158
1159 x_modifier_line_tbl := v_modifiers_tbl;
1160 x_pricing_attr_tbl := v_pricing_attr_tbl;
1161
1162 IF nvl(x_modifier_line_tbl.count,0) > 0 THEN
1163 FOR i IN x_modifier_line_tbl.first .. x_modifier_line_tbl.last LOOP
1164 IF x_modifier_line_tbl.exists(i) THEN
1165 x_listLineId := x_modifier_line_tbl(i).list_line_id;
1166 END IF;
1167 END LOOP;
1168 END IF;
1169 /*OZF_OFFER_PVT.process_qp_list_lines
1170 (
1171 x_return_status OUT NOCOPY VARCHAR2
1172 ,x_msg_count OUT NOCOPY NUMBER
1173 ,x_msg_data OUT NOCOPY VARCHAR2
1174 ,p_offer_type IN VARCHAR2
1175 ,p_modifier_line_tbl IN MODIFIER_LINE_TBL_TYPE
1176 ,p_list_header_id IN NUMBER
1177 ,x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
1178 ,x_error_location OUT NOCOPY NUMBER
1179 );
1180 */
1181 END create_dis_line;
1182
1183
1184
1185
1186 /**
1187 This procedure copies a qp_list_line into a new list_line.
1188 It copies the qp_list_line, the pricing_attributes, including exclusions, the advanced options and the limits
1189 */
1190 PROCEDURE copyListLine
1191 (
1192 x_return_status OUT NOCOPY VARCHAR2
1193 , x_msg_count OUT NOCOPY NUMBER
1194 , x_msg_data OUT NOCOPY VARCHAR2
1195 , p_listLineId IN NUMBER
1196 , x_listLineId OUT NOCOPY NUMBER
1197 , p_offerAdjustmentLineId IN NUMBER
1198 , p_offerAdjustmentId IN NUMBER
1199 )
1200 IS
1201 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
1202 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1203 BEGIN
1204 -- initialise
1205 -- create Discount lines and pricing attributes(including exclusions)
1206 -- create qualifiers
1207 -- create limits
1208 x_return_status := FND_API.G_RET_STS_SUCCESS;
1209 l_modifier_line_tbl.delete;
1210 l_pricing_attr_tbl.delete;
1211 create_dis_line
1212 (
1213 x_return_status => x_return_status
1214 ,x_msg_count => x_msg_count
1215 ,x_msg_data => x_msg_data
1216 ,p_listLineId => p_listLineId
1217 ,x_modifier_line_tbl => l_modifier_line_tbl
1218 ,x_pricing_attr_tbl => l_pricing_attr_tbl
1219 , x_listLineId => x_listLineId
1220 , p_offerAdjustmentLineId => p_offerAdjustmentLineId
1221 , p_offerAdjustmentId => p_offerAdjustmentId
1222 );
1223 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1224 RAISE FND_API.G_EXC_ERROR;
1225 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1227 END IF;
1228 copyQualifiers
1229 (
1230 x_return_status => x_return_status
1231 ,x_msg_count => x_msg_count
1232 ,x_msg_data => x_msg_data
1233 ,p_fromListLineId => p_listLineId
1234 , p_toListLineId => x_listLineId
1235 );
1236 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1237 RAISE FND_API.G_EXC_ERROR;
1238 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1240 END IF;
1241 copyLimits
1242 (
1243 x_return_status => x_return_status
1244 ,x_msg_count => x_msg_count
1245 ,x_msg_data => x_msg_data
1246 ,p_fromListLineId => p_listLineId
1247 , p_toListLineId => x_listLineId
1248 );
1249 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1250 RAISE FND_API.G_EXC_ERROR;
1251 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1253 END IF;
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 FND_MSG_PUB.count_and_get(
1257 p_encoded => FND_API.g_false
1258 , p_count => x_msg_count
1259 , p_data => x_msg_data
1260 );
1261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1262 END copyListLine;
1263
1264
1265 /**
1266 Processes a simple Discount Line, ie a list_line_id of the type DIS.
1267 It end dates the given list_line_id with the Effective date of the adjustment.
1268 Creates a new Line with start Date as effective date of the Adjustment.
1269 Creates relation between the new and old lines.
1270 */
1271 PROCEDURE process_old_dis_discount
1272 (
1273 x_return_status OUT NOCOPY VARCHAR2
1274 ,x_msg_count OUT NOCOPY NUMBER
1275 ,x_msg_data OUT NOCOPY VARCHAR2
1276 ,p_listLineId IN NUMBER
1277 ,p_offerAdjustmentId IN NUMBER
1278 , p_offerAdjustmentLineId IN NUMBER
1279 )
1280 IS
1281 CURSOR c_listLineId(cp_offerAdjustmentLineId NUMBER) IS
1282 SELECT list_line_id
1283 FROM ozf_offer_adjustment_lines
1284 WHERE offer_adjustment_line_id = cp_offerAdjustmentLineId;
1285 l_listLineId NUMBER;
1286 BEGIN
1287 -- initialize
1288 -- end date existing line
1289 -- create new qp_list_line/s
1290 -- create new to old mapping
1291 x_return_status := FND_API.G_RET_STS_SUCCESS;
1292 -- check if list_line_id is passed in, in case not, get the list_line_id from the offer_adjustment_line_id, if cannot be located
1293 -- raise error
1294
1295 IF p_offerAdjustmentLineId IS NULL OR p_offerAdjustmentLineId = FND_API.G_MISS_NUM THEN
1296 OZF_Utility_PVT.Error_Message(p_message_name => 'INVALID_ADJUSTMENT_LINE');
1297 x_return_status := FND_API.g_ret_sts_error;
1298 RETURN;
1299 END IF;
1300 /*
1301 IF (p_listLineId IS NULL OR p_listLineId = FND_API.G_MISS_NUM) THEN
1302 OPEN c_listLineId(cp_offerAdjustmentLineId => p_offerAdjustmentLineId);
1303 FETCH c_listLineId INTO l_listLineId;
1304 IF c_listLineId%NOTFOUND THEN
1305 OZF_Utility_PVT.Error_Message(p_message_name => 'INVALID_LIST_LINE');
1306 x_return_status := FND_API.g_ret_sts_error;
1307 CLOSE c_listLineId;
1308 RETURN;
1309 END IF;
1310 CLOSE c_listLineId;
1311 p_listLineId := l_listLineId;
1312 END IF;
1313 */
1314 OZF_VOLUME_OFFER_ADJ.end_qp_line
1315 (
1316 x_return_status => x_return_status
1317 , x_msg_count => x_msg_count
1318 , x_msg_data => x_msg_data
1319 , p_listLineId => p_listLineId
1320 ,p_offerAdjustmentId => p_offerAdjustmentId
1321 );
1322 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1323 RAISE FND_API.G_EXC_ERROR;
1324 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1326 END IF;
1327 copyListLine
1328 (
1329 x_return_status => x_return_status
1330 , x_msg_count => x_msg_count
1331 , x_msg_data => x_msg_data
1332 , p_listLineId => p_listLineId
1333 , x_listLineId => l_listLineId
1334 , p_offerAdjustmentLineId => p_offerAdjustmentLineId
1335 ,p_offerAdjustmentId => p_offerAdjustmentId
1336 );
1337 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1338 RAISE FND_API.G_EXC_ERROR;
1339 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1341 END IF;
1342 OZF_VOLUME_OFFER_ADJ.relate_lines
1343 (
1344 p_from_list_line_id => p_listLineId
1345 , p_to_list_line_id => l_listLineId
1346 , p_offer_adjustment_id => p_offerAdjustmentId
1347 , x_return_status => x_return_status
1348 , x_msg_count => x_msg_count
1349 , x_msg_data => x_msg_data
1350 );
1351 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1352 RAISE FND_API.G_EXC_ERROR;
1353 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1355 END IF;
1356 END process_old_dis_discount;
1357
1358
1359
1360
1361 PROCEDURE processOldDisLines
1362 (
1363 x_return_status OUT NOCOPY VARCHAR2
1364 ,x_msg_count OUT NOCOPY NUMBER
1365 ,x_msg_data OUT NOCOPY VARCHAR2
1366 ,p_offerAdjustmentId IN NUMBER
1367 )
1368 IS
1369 CURSOR c_adjustmentLines(cp_offerAdjustmentId NUMBER)
1370 IS
1371 SELECT a.list_line_id, b.list_line_type_code , a.offer_adjustment_line_id
1372 FROM ozf_offer_adjustment_lines a, qp_list_lines b
1373 WHERE
1374 a.list_line_id = b.list_line_id
1375 AND b.qualification_ind in (0,2,4,6,8,10,12,14,16,18,20,22,24,26, 28, 30, 32)
1376 AND offer_adjustment_id = cp_offerAdjustmentId;
1377 BEGIN
1378 x_return_status := FND_API.G_RET_STS_SUCCESS;
1379 FOR l_adjustmentLines IN c_adjustmentLines(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
1380 process_old_dis_discount
1381 (
1382 x_return_status => x_return_status
1383 , x_msg_count => x_msg_count
1384 , x_msg_data => x_msg_data
1385 , p_listLineId => l_adjustmentLines.list_line_id
1386 , p_offerAdjustmentId => p_offerAdjustmentId
1387 , p_offerAdjustmentLineId => l_adjustmentLines.offer_adjustment_line_id
1388 );
1389 END LOOP;
1390 END processOldDisLines;
1391
1392 /**
1393 Process an Adjustment on an Off-Invoice or Accrual offers
1394 */
1395 PROCEDURE process_old_reg_discount
1396 (
1397 x_return_status OUT NOCOPY VARCHAR2
1398 ,x_msg_count OUT NOCOPY NUMBER
1399 ,x_msg_data OUT NOCOPY VARCHAR2
1400 ,p_offerAdjustmentId IN NUMBER
1401 )
1402 IS
1403 BEGIN
1404 -- initialize
1405 -- loop thru. adjustment lines
1406 -- for each adjustment line end date the existing qp_list_line and create new qp_list_line
1407 x_return_status := FND_API.G_RET_STS_SUCCESS;
1408 processOldDisLines
1409 (
1410 x_return_status => x_return_status
1411 , x_msg_count => x_msg_count
1412 , x_msg_data => x_msg_data
1413 , p_offerAdjustmentId => p_offerAdjustmentId
1414 );
1415 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1416 RAISE FND_API.G_EXC_ERROR;
1417 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1419 END IF;
1420 processOldPbhLines
1421 (
1422 x_return_status => x_return_status
1423 , x_msg_count => x_msg_count
1424 , x_msg_data => x_msg_data
1425 , p_offerAdjustmentId => p_offerAdjustmentId
1426 );
1427
1428 END process_old_reg_discount;
1429
1430 PROCEDURE populatePgDiscounts
1431 (
1432 x_return_status OUT NOCOPY VARCHAR2
1433 ,x_msg_count OUT NOCOPY NUMBER
1434 ,x_msg_data OUT NOCOPY VARCHAR2
1435 , p_listLineId IN NUMBER
1436 , x_pricing_attr_tbl OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_tbl_type
1437 , x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
1438 )
1439 IS
1440 /**
1441 This cursor probably may have issues and we may have to join qp_list_lines and put a filter on list_line_type_code = PRG
1442 */
1443 CURSOR c_getPrgLineId(cp_listLineId NUMBER) IS
1444 SELECT from_rltd_modifier_id
1445 FROM qp_rltd_modifiers
1446 WHERE rltd_modifier_grp_type = 'BENEFIT'
1447 AND rltd_modifier_grp_no = 1
1448 AND to_rltd_modifier_id = cp_listLineId;
1449
1450 l_getPrgLineId c_getPrgLineId%ROWTYPE;
1451 BEGIN
1452 x_modifier_line_tbl.delete;
1453 x_pricing_attr_tbl.delete;
1454
1455 populate_dis_qp_data
1456 (
1457 x_return_status => x_return_status
1458 , x_msg_count => x_msg_count
1459 , x_msg_data => x_msg_data
1460 , p_listLineId => p_listLineId
1461 , x_modifier_line_tbl => x_modifier_line_tbl
1462 , x_pricing_attr_tbl => x_pricing_attr_tbl
1463 );
1464
1465 IF nvl(x_modifier_line_tbl.count,0) > 0 THEN
1466 OPEN c_getPrgLineId(cp_listLineId => p_listLineId);
1467 FETCH c_getPrgLineId INTO l_getPrgLineId;
1468 CLOSE c_getPrgLineId;
1469 FOR i IN x_modifier_line_tbl.first .. x_modifier_line_tbl.last LOOP
1470 IF x_modifier_line_tbl.exists(i) THEN
1471 x_modifier_line_tbl(i).from_rltd_modifier_id := l_getPrgLineId.from_rltd_modifier_id;
1472 x_modifier_line_tbl(i).rltd_modifier_grp_type := 'BENEFIT';
1473 x_modifier_line_tbl(i).rltd_modifier_grp_no := 1;
1474 END IF;
1475 END LOOP;
1476 END IF;
1477 END populatePgDiscounts;
1478
1479 PROCEDURE processPgDiscounts
1480 (
1481 x_return_status OUT NOCOPY VARCHAR2
1482 ,x_msg_count OUT NOCOPY NUMBER
1483 ,x_msg_data OUT NOCOPY VARCHAR2
1484 , p_offerAdjustmentLineId IN NUMBER
1485 , p_offerAdjustmentId IN NUMBER
1486 , x_pricing_attr_tbl IN OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_tbl_type
1487 , x_modifier_line_tbl IN OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
1488 )
1489 IS
1490 BEGIN
1491 x_return_status := FND_API.G_RET_STS_SUCCESS;
1492 FOR i in x_modifier_line_tbl.first .. x_modifier_line_tbl.last LOOP
1493 IF x_modifier_line_tbl.exists(i) THEN
1494 x_modifier_line_tbl(i).operand := getModifiedDiscount(p_offerAdjustmentLineId => p_offerAdjustmentLineId);
1495 x_modifier_line_tbl(i).start_date_active := getEffectiveDate(p_offerAdjustmentId => p_offerAdjustmentId);
1496 END IF;
1497 END LOOP;
1498 END processPgDiscounts;
1499
1500 PROCEDURE createPgLine
1501 (
1502 x_return_status OUT NOCOPY VARCHAR2
1503 ,x_msg_count OUT NOCOPY NUMBER
1504 ,x_msg_data OUT NOCOPY VARCHAR2
1505 , p_listLineId IN NUMBER
1506 , x_listLineId OUT NOCOPY NUMBER
1507 , p_offerAdjustmentLineId IN NUMBER
1508 , p_offerAdjustmentId IN NUMBER
1509 , x_pricing_attr_tbl OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_tbl_type
1510 , x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
1511 )
1512 IS
1513 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
1514 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
1515 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
1516 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
1517 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1518 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1519 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1520 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
1521 BEGIN
1522 -- initialize
1523 -- populate modifiers table and pricing attribute table
1524 -- process modifiers table and pricing attribute table
1525 -- create qp data
1526 -- return list_line_id
1527 x_return_status := FND_API.G_RET_STS_SUCCESS;
1528 x_pricing_attr_tbl.delete;
1529 x_modifier_line_tbl.delete;
1530 populatePgDiscounts
1531 (
1532 x_return_status => x_return_status
1533 ,x_msg_count => x_msg_count
1534 ,x_msg_data => x_msg_data
1535 , p_listLineId => p_listLineId
1536 , x_pricing_attr_tbl => x_pricing_attr_tbl
1537 , x_modifier_line_tbl => x_modifier_line_tbl
1538 );
1539 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1540 RAISE FND_API.G_EXC_ERROR;
1541 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1543 END IF;
1544 processPgDiscounts
1545 (
1546 x_return_status => x_return_status
1547 ,x_msg_count => x_msg_count
1548 ,x_msg_data => x_msg_data
1549 , x_pricing_attr_tbl => x_pricing_attr_tbl
1550 , x_modifier_line_tbl => x_modifier_line_tbl
1551 , p_offerAdjustmentLineId => p_offerAdjustmentLineId
1552 , p_offerAdjustmentId => p_offerAdjustmentId
1553 );
1554 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1555 RAISE FND_API.G_EXC_ERROR;
1556 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1558 END IF;
1559
1560 QP_Modifiers_PUB.process_modifiers(
1561 p_api_version_number => 1.0,
1562 p_init_msg_list => FND_API.G_TRUE,
1563 p_return_values => FND_API.G_FALSE,
1564 x_return_status => x_return_status,
1565 x_msg_count => x_msg_count,
1566 x_msg_data => x_msg_data,
1567 p_modifiers_tbl => x_modifier_line_tbl,
1568 p_pricing_attr_tbl => x_pricing_attr_tbl,
1569 x_modifier_list_rec => v_modifier_list_rec,
1570 x_modifier_list_val_rec => v_modifier_list_val_rec,
1571 x_modifiers_tbl => v_modifiers_tbl,
1572 x_modifiers_val_tbl => v_modifiers_val_tbl,
1573 x_qualifiers_tbl => v_qualifiers_tbl,
1574 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
1575 x_pricing_attr_tbl => v_pricing_attr_tbl,
1576 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
1577 );
1578 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1579 RAISE FND_API.G_EXC_ERROR;
1580 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1582 END IF;
1583
1584 x_pricing_attr_tbl := v_pricing_attr_tbl;
1585 x_modifier_line_tbl := v_modifiers_tbl;
1586
1587 FOR i in v_modifiers_tbl.first .. v_modifiers_tbl.last LOOP
1588 IF v_modifiers_tbl.exists(i) THEN
1589 x_listLineId := v_modifiers_tbl(i).list_line_id;
1590 END IF;
1591 END LOOP;
1592 END createPgLine;
1593
1594
1595 PROCEDURE copyPGListLine
1596 (
1597 x_return_status OUT NOCOPY VARCHAR2
1598 ,x_msg_count OUT NOCOPY NUMBER
1599 ,x_msg_data OUT NOCOPY VARCHAR2
1600 , p_listLineId IN NUMBER
1601 , x_listLineId OUT NOCOPY NUMBER
1602 , p_offerAdjustmentLineId IN NUMBER
1603 ,p_offerAdjustmentId IN NUMBER
1604 )
1605 IS
1606 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
1607 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1608 BEGIN
1609 -- initialise
1610 -- create Discount lines and pricing attributes(including exclusions)
1611 -- create qualifiers
1612 -- create limits
1613 x_return_status := FND_API.G_RET_STS_SUCCESS;
1614 l_modifier_line_tbl.delete;
1615 l_pricing_attr_tbl.delete;
1616 createPgLine
1617 (
1618 x_return_status => x_return_status
1619 ,x_msg_count => x_msg_count
1620 ,x_msg_data => x_msg_data
1621 ,p_listLineId => p_listLineId
1622 ,x_modifier_line_tbl => l_modifier_line_tbl
1623 ,x_pricing_attr_tbl => l_pricing_attr_tbl
1624 , x_listLineId => x_listLineId
1625 , p_offerAdjustmentLineId => p_offerAdjustmentLineId
1626 , p_offerAdjustmentId => p_offerAdjustmentId
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 copyQualifiers
1634 (
1635 x_return_status => x_return_status
1636 ,x_msg_count => x_msg_count
1637 ,x_msg_data => x_msg_data
1638 ,p_fromListLineId => p_listLineId
1639 , p_toListLineId => x_listLineId
1640 );
1641 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1642 RAISE FND_API.G_EXC_ERROR;
1643 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1645 END IF;
1646 copyLimits
1647 (
1648 x_return_status => x_return_status
1649 ,x_msg_count => x_msg_count
1650 ,x_msg_data => x_msg_data
1651 ,p_fromListLineId => p_listLineId
1652 , p_toListLineId => x_listLineId
1653 );
1654 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1655 RAISE FND_API.G_EXC_ERROR;
1656 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1658 END IF;
1659
1660 END copyPGListLine;
1661
1662 PROCEDURE processOldPgDiscount
1663 (
1664 x_return_status OUT NOCOPY VARCHAR2
1665 ,x_msg_count OUT NOCOPY NUMBER
1666 ,x_msg_data OUT NOCOPY VARCHAR2
1667 ,p_offerAdjustmentLineId IN NUMBER
1668 , p_offerAdjustmentId in NUMBER
1669 , p_listLineId IN NUMBER
1670 )
1671 IS
1672 l_listLineId NUMBER;
1673 BEGIN
1674 -- initialize
1675 -- end date existing line
1676 -- create new qp_list_line/s
1677 -- create new to old mapping
1678 x_return_status := FND_API.G_RET_STS_SUCCESS;
1679 -- check if list_line_id is passed in, in case not, get the list_line_id from the offer_adjustment_line_id, if cannot be located
1680 -- raise error
1681 /*IF p_offerAdjustmentLineId IS NULL OR p_offerAdjustmentLineId = FND_API.G_MISS_NUM THEN
1682 OZF_Utility_PVT.Error_Message(p_message_name => 'INVALID_ADJUSTMENT_LINE');
1683 x_return_status := FND_API.g_ret_sts_error;
1684 RETURN;
1685 END IF;
1686 */
1687 OZF_VOLUME_OFFER_ADJ.end_qp_line
1688 (
1689 x_return_status => x_return_status
1690 , x_msg_count => x_msg_count
1691 , x_msg_data => x_msg_data
1692 , p_listLineId => p_listLineId
1693 ,p_offerAdjustmentId => p_offerAdjustmentId
1694 );
1695 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1696 RAISE FND_API.G_EXC_ERROR;
1697 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1699 END IF;
1700 copyPGListLine
1701 (
1702 x_return_status => x_return_status
1703 , x_msg_count => x_msg_count
1704 , x_msg_data => x_msg_data
1705 , p_listLineId => p_listLineId
1706 , x_listLineId => l_listLineId
1707 , p_offerAdjustmentLineId => p_offerAdjustmentLineId
1708 ,p_offerAdjustmentId => p_offerAdjustmentId
1709 );
1710 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1711 RAISE FND_API.G_EXC_ERROR;
1712 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1714 END IF;
1715 OZF_VOLUME_OFFER_ADJ.relate_lines
1716 (
1717 p_from_list_line_id => p_listLineId
1718 , p_to_list_line_id => l_listLineId
1719 , p_offer_adjustment_id => p_offerAdjustmentId
1720 , x_return_status => x_return_status
1721 , x_msg_count => x_msg_count
1722 , x_msg_data => x_msg_data
1723 );
1724 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1725 RAISE FND_API.G_EXC_ERROR;
1726 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1728 END IF;
1729 END processOldPgDiscount;
1730
1731 PROCEDURE process_old_pg_discount
1732 (
1733 x_return_status OUT NOCOPY VARCHAR2
1734 ,x_msg_count OUT NOCOPY NUMBER
1735 ,x_msg_data OUT NOCOPY VARCHAR2
1736 ,p_offerAdjustmentId IN NUMBER
1737 )
1738 IS
1739 CURSOR c_listLine(cp_offerAdjustmentId NUMBER)IS
1740 SELECT list_line_id, offer_adjustment_line_id
1741 FROM ozf_offer_adjustment_lines
1742 WHERE offer_adjustment_id = cp_offerAdjustmentId;
1743 BEGIN
1744 x_return_status := FND_API.G_RET_STS_SUCCESS;
1745 FOR l_listLine in c_listLine(cp_offerAdjustmentId => p_offerAdjustmentId ) LOOP
1746 processOldPgDiscount
1747 (
1748 x_return_status => x_return_status
1749 , x_msg_count => x_msg_count
1750 , x_msg_data => x_msg_data
1751 , p_offerAdjustmentId => p_offerAdjustmentId
1752 , p_offerAdjustmentLineId => l_listLine.offer_adjustment_line_id
1753 , p_listLineId => l_listLine.list_line_id
1754 );
1755 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1756 RAISE FND_API.G_EXC_ERROR;
1757 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1759 END IF;
1760 END LOOP;
1761 END process_old_pg_discount;
1762
1763
1764 PROCEDURE populateTdPricingAttr
1765 (
1766 px_modifier_line_rec IN OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_REC_TYPE
1767 , p_listLineId IN NUMBER
1768 ,x_return_status OUT NOCOPY VARCHAR2
1769 ,x_msg_count OUT NOCOPY NUMBER
1770 ,x_msg_data OUT NOCOPY VARCHAR2
1771 )
1772 IS
1773 BEGIN
1774 x_return_status := FND_API.G_RET_STS_SUCCESS;
1775 FOR l_pricingAttr IN (SELECT product_attribute, product_attr_value, product_uom_code,pricing_attribute, pricing_attr_value_from
1776 FROM qp_pricing_attributes
1777 WHERE list_line_id = p_listLineId
1778 AND excluder_flag = 'N')
1779 LOOP
1780 px_modifier_line_rec.product_attr := l_pricingAttr.product_attribute;
1781 px_modifier_line_rec.product_attr_val := l_pricingAttr.product_attr_value;
1782 px_modifier_line_rec.product_uom_code := l_pricingAttr.product_uom_code;
1783 px_modifier_line_rec.pricing_attr := l_pricingAttr.pricing_attribute;
1784 px_modifier_line_rec.pricing_attr_value_from := l_pricingAttr.pricing_attr_value_from;
1785 END LOOP;
1786 END populateTdPricingAttr;
1787
1788 PROCEDURE populateTdCrtLines
1789 (
1790 x_modifier_line_rec OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_REC_TYPE
1791 , p_listLineId IN NUMBER
1792 , x_return_status OUT NOCOPY VARCHAR2
1793 , x_msg_count OUT NOCOPY NUMBER
1794 , x_msg_data OUT NOCOPY VARCHAR2
1795 )
1796 IS
1797 CURSOR c_tdAdjustmentLines(cp_listLineId NUMBER) IS
1798 SELECT
1799 a.list_header_id
1800 , a.list_line_id
1801 , decode(nvl(a.accrual_flag,'N'),'N',a.arithmetic_operator, c.arithmetic_operator) operator
1802 , decode(nvl(a.accrual_flag,'N'),'Y',a.arithmetic_operator, c.arithmetic_operator) accrual_operator
1803 FROM
1804 qp_list_lines a
1805 , ozf_related_deal_lines b
1806 , qp_list_lines c
1807 WHERE
1808 a.list_line_id = b.MODIFIER_ID
1809 AND b.RELATED_MODIFIER_ID = c.list_line_id(+)
1810 AND a.list_line_id =cp_listLineId;
1811
1812 l_modifier_line_rec ozf_offer_pvt.MODIFIER_LINE_REC_TYPE;
1813 BEGIN
1814 x_return_status := FND_API.G_RET_STS_SUCCESS;
1815 FOR l_tdAdjustmentLines IN c_tdAdjustmentLines(cp_listLineId => p_listLineId )
1816 LOOP
1817 l_modifier_line_rec.operation := 'CREATE';
1818 l_modifier_line_rec.list_line_type_code := 'DIS';
1819 l_modifier_line_rec.list_header_id := l_tdAdjustmentLines.list_header_id;
1820 l_modifier_line_rec.end_date_active := null;
1821 l_modifier_line_rec.arithmetic_operator := l_tdAdjustmentLines.operator;
1822 -- l_modifier_line_rec.operand := l_tdAdjustmentLines.modified_discount;
1823 l_modifier_line_rec.qd_arithmetic_operator := l_tdAdjustmentLines.accrual_operator;
1824 -- l_modifier_line_rec.qd_operand := l_tdAdjustmentLines.modified_discount_td;
1825 populateTdPricingAttr
1826 (
1827 px_modifier_line_rec => l_modifier_line_rec
1828 , p_listLineId => p_listLineId
1829 ,x_return_status => x_return_status
1830 ,x_msg_count => x_msg_count
1831 ,x_msg_data => x_msg_data
1832 );
1833 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1834 return;
1835 END IF;
1836 END LOOP;
1837 x_modifier_line_rec := l_modifier_line_rec;
1838 END populateTdCrtLines;
1839
1840 PROCEDURE processTdCrtLines
1841 (
1842 px_modifier_line_rec IN OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_REC_TYPE
1843 , p_offerAdjustmentId IN NUMBER
1844 , p_listLineId IN NUMBER
1845 , x_return_status OUT NOCOPY VARCHAR2
1846 , x_msg_count OUT NOCOPY NUMBER
1847 , x_msg_data OUT NOCOPY VARCHAR2
1848 )
1849 IS
1850 CURSOR c_tdCrtLineDetails(cp_offerAdjustmentId NUMBER, cp_listLineId NUMBER) IS
1851 SELECT b.effective_date
1852 , nvl(a.modified_discount,a.original_discount) discount
1853 , nvl(a.modified_discount_td, a.original_discount_td) discount_td
1854 FROM ozf_offer_adjustment_lines a, ozf_offer_adjustments_b b
1855 WHERE a.offer_adjustment_id = b.offer_adjustment_id
1856 AND a.offer_adjustment_id = cp_offerAdjustmentId
1857 AND a.list_line_id = cp_listLineId;
1858 BEGIN
1859 x_return_status := FND_API.G_RET_STS_SUCCESS;
1860 FOR l_tdCrtLineDetails in c_tdCrtLineDetails(cp_offerAdjustmentId => p_offerAdjustmentId, cp_listLineId => p_listLineId) LOOP
1861 px_modifier_line_rec.start_date_active := getEffectiveDate(p_offerAdjustmentId => p_offerAdjustmentId);
1862 px_modifier_line_rec.operand := l_tdCrtLineDetails.discount;
1863 px_modifier_line_rec.qd_operand := l_tdCrtLineDetails.discount_td;
1864 END LOOP;
1865 END processTdCrtLines;
1866
1867 PROCEDURE populateTdUpdLines
1868 (
1869 x_modifier_line_rec OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_REC_TYPE
1870 , p_listLineId IN NUMBER
1871 ,x_return_status OUT NOCOPY VARCHAR2
1872 ,x_msg_count OUT NOCOPY NUMBER
1873 ,x_msg_data OUT NOCOPY VARCHAR2
1874 )
1875 IS
1876 CURSOR c_tdUpdAdjustmentLines(cp_listLineId NUMBER) IS
1877 SELECT
1878 decode(nvl(a.accrual_flag,'N'),'N',b.modifier_id,b.related_modifier_id) list_line_id
1879 , a.list_header_id
1880 , a.accrual_flag
1881 --, greatest(a.start_date_active, d.start_date_active) start_date_active
1882 , decode(nvl(a.accrual_flag,'N'),'N',a.arithmetic_operator, c.arithmetic_operator) arithmetic_operator
1883 , decode(nvl(a.accrual_flag,'N'),'Y',a.arithmetic_operator, c.arithmetic_operator) arithmetic_operator_td
1884 , decode(nvl(a.accrual_flag,'N'),'N',a.operand, c.operand) operand
1885 , decode(nvl(a.accrual_flag,'N'),'Y',a.operand,c.operand) operand_td
1886 , decode(nvl(a.accrual_flag,'N'),'Y',b.modifier_id, b.related_modifier_id) related_modifier_id
1887 , b.related_deal_lines_id
1888 , b.object_version_number
1889 , a.start_date_active
1890 FROM
1891 qp_list_lines a
1892 , ozf_related_deal_lines b
1893 , qp_list_lines c
1894 , qp_list_headers_b d
1895 WHERE
1896 a.list_line_id = b.modifier_id
1897 AND b.related_modifier_id = c.list_line_id(+)
1898 AND a.list_header_id = d.list_header_id
1899 AND a.list_line_id = cp_listLineId;
1900
1901 l_modifier_line_rec ozf_offer_pvt.MODIFIER_LINE_REC_TYPE;
1902 BEGIN
1903 x_return_status := FND_API.G_RET_STS_SUCCESS;
1904 FOR l_tdUpdAdjustmentLines IN c_tdUpdAdjustmentLines(cp_listLineId => p_listLineId)
1905 LOOP
1906 l_modifier_line_rec.operation := 'UPDATE';
1907 l_modifier_line_rec.list_line_id := l_tdUpdAdjustmentLines.list_line_id;
1908 l_modifier_line_rec.list_header_id := l_tdUpdAdjustmentLines.list_header_id;
1909 l_modifier_line_rec.inactive_flag := 'Y';
1910 l_modifier_line_rec.arithmetic_operator := l_tdUpdAdjustmentLines.arithmetic_operator;
1911 l_modifier_line_rec.operand := l_tdUpdAdjustmentLines.operand;
1912 l_modifier_line_rec.qd_arithmetic_operator := l_tdUpdAdjustmentLines.arithmetic_operator_td;
1913 l_modifier_line_rec.qd_operand := l_tdUpdAdjustmentLines.operand_td;
1914 l_modifier_line_rec.qd_list_line_id := l_tdUpdAdjustmentLines.related_modifier_id;
1915 l_modifier_line_rec.qd_related_deal_lines_id := l_tdUpdAdjustmentLines.related_deal_lines_id;
1916 l_modifier_line_rec.qd_object_version_number := l_tdUpdAdjustmentLines.object_version_number;
1917 l_modifier_line_rec.start_date_active := l_tdUpdAdjustmentLines.start_date_active;
1918 END LOOP;
1919 x_modifier_line_rec := l_modifier_line_rec;
1920 END populateTdUpdLines;
1921
1922 PROCEDURE processTdUpdLines
1923 (
1924 px_modifier_line_rec IN OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_REC_TYPE
1925 , p_offerAdjustmentId IN NUMBER
1926 , p_listLineId IN NUMBER
1927 ,x_return_status OUT NOCOPY VARCHAR2
1928 ,x_msg_count OUT NOCOPY NUMBER
1929 ,x_msg_data OUT NOCOPY VARCHAR2
1930 )
1931 IS
1932 BEGIN
1933 x_return_status := FND_API.G_RET_STS_SUCCESS;
1934 px_modifier_line_rec.end_date_active := getEndDate( p_offerAdjustmentId => p_offerAdjustmentId , p_listLineId => p_listLineId);
1935 END processTdUpdLines;
1936
1937
1938 PROCEDURE createTdLine
1939 (
1940 x_return_status OUT NOCOPY VARCHAR2
1941 ,x_msg_count OUT NOCOPY NUMBER
1942 ,x_msg_data OUT NOCOPY VARCHAR2
1943 ,p_offerAdjustmentId IN NUMBER
1944 , p_listLineId IN NUMBER
1945 , x_modifier_tbl OUT NOCOPY QP_MODIFIERS_PUB.modifiers_tbl_type
1946 )
1947
1948 IS
1949 l_modifier_line_tbl ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE;
1950 lx_modifier_tbl QP_MODIFIERS_PUB.modifiers_tbl_type;
1951 l_errorLocation NUMBER;
1952 BEGIN
1953 -- initialize
1954 -- populate td lines
1955 -- process td lines
1956 -- create discount rules
1957 -- return table created
1958 x_return_status := FND_API.G_RET_STS_SUCCESS;
1959 l_modifier_line_tbl.delete;
1960 populateTdCrtLines
1961 (
1962 x_modifier_line_rec => l_modifier_line_tbl(1)
1963 , p_listLineId => p_listLineId
1964 , x_return_status => x_return_status
1965 , x_msg_count => x_msg_count
1966 , x_msg_data => x_msg_data
1967 );
1968 ----dbms_output.put_line('Populated creat lines');
1969 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1970 RAISE FND_API.G_EXC_ERROR;
1971 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1973 END IF;
1974 processTdCrtLines
1975 (
1976 px_modifier_line_rec => l_modifier_line_tbl(1)
1977 , p_listLineId => p_listLineId
1978 , p_offerAdjustmentId => p_offerAdjustmentId
1979 , x_return_status => x_return_status
1980 , x_msg_count => x_msg_count
1981 , x_msg_data => x_msg_data
1982 );
1983 ----dbms_output.put_line('Processed creat lines:'|| l_modifier_line_tbl(1).list_line_id||':'||l_modifier_line_tbl(1).product_attr_val||l_modifier_line_tbl(1).list_header_id);
1984 --dbms_output.put_line('operand:'|| l_modifier_line_tbl(1).operand||':'||l_modifier_line_tbl(1).qd_operand);
1985 --dbms_output.put_line('Arithmetic operator:'|| l_modifier_line_tbl(1).arithmetic_operator||':'||l_modifier_line_tbl(1).qd_arithmetic_operator);
1986
1987 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1988 RAISE FND_API.G_EXC_ERROR;
1989 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1991 END IF;
1992 ozf_offer_pvt.process_qp_list_lines
1993 (
1994 x_return_status => x_return_status
1995 ,x_msg_count => x_msg_count
1996 ,x_msg_data => x_msg_data
1997 ,p_offer_type => 'DEAL'
1998 ,p_modifier_line_tbl => l_modifier_line_tbl
1999 ,p_list_header_id => l_modifier_line_tbl(1).list_header_id
2000 ,x_modifier_line_tbl => lx_modifier_tbl --QP_MODIFIERS_PUB.modifiers_tbl_type
2001 ,x_error_location => l_errorLocation
2002 );
2003 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2004 RAISE FND_API.G_EXC_ERROR;
2005 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2007 END IF;
2008
2009 x_modifier_tbl := lx_modifier_tbl;
2010
2011 EXCEPTION
2012 WHEN OTHERS THEN
2013 FND_MSG_PUB.count_and_get(
2014 p_encoded => FND_API.g_false
2015 , p_count => x_msg_count
2016 , p_data => x_msg_data
2017 );
2018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2019
2020 END createTdLine;
2021
2022 PROCEDURE populateTdExclusion
2023 (
2024 x_return_status OUT NOCOPY VARCHAR2
2025 ,x_msg_count OUT NOCOPY NUMBER
2026 ,x_msg_data OUT NOCOPY VARCHAR2
2027 , p_fromListLineId IN NUMBER
2028 ,x_pricing_attr_tbl OUT NOCOPY OZF_OFFER_PVT.PRICING_ATTR_TBL_TYPE
2029 )
2030 IS
2031 i NUMBER;
2032 CURSOR c_productAttributes(cp_listLineId NUMBER)
2033 IS
2034 SELECT product_attribute, product_attr_value , list_header_id
2035 FROM qp_pricing_attributes
2036 WHERE list_line_id = cp_listLineId
2037 and excluder_flag = 'Y';
2038 BEGIN
2039 x_return_status := FND_API.G_RET_STS_SUCCESS;
2040 x_pricing_attr_tbl.delete;
2041 i := 0;
2042 --dbms_output.put_line('inside loop1'||p_fromListLineId);
2043 FOR l_productAttributes IN c_productAttributes(cp_listLineId => p_fromListLineId)
2044 LOOP
2045 i := i + 1;
2046 --dbms_output.put_line('inside loop2'||i);
2047 x_pricing_attr_tbl(i).product_attribute := l_productAttributes.product_attribute;
2048 x_pricing_attr_tbl(i).product_attr_value := l_productAttributes.product_attr_value;
2049 -- x_pricing_attr_tbl(i).list_header_id := l_productAttributes.list_header_id;
2050 --dbms_output.put_line('inside loop2:'||x_pricing_attr_tbl(i).product_attribute);
2051 --dbms_output.put_line('inside loop2:'||x_pricing_attr_tbl(i).product_attr_value);
2052 END LOOP;
2053 ----dbms_output.put_line(sqlerrm);
2054 END populateTdExclusion;
2055
2056 PROCEDURE processTdExclusion
2057 (
2058 x_return_status OUT NOCOPY VARCHAR2
2059 ,x_msg_count OUT NOCOPY NUMBER
2060 ,x_msg_data OUT NOCOPY VARCHAR2
2061 ,p_toListLineId IN NUMBER
2062 ,px_pricing_attr_tbl IN OUT NOCOPY OZF_OFFER_PVT.PRICING_ATTR_TBL_TYPE
2063 )
2064 IS
2065 BEGIN
2066 x_return_status := FND_API.G_RET_STS_SUCCESS;
2067 IF nvl(px_pricing_attr_tbl.count,0) > 0 THEN
2068 FOR i in px_pricing_attr_tbl.first .. px_pricing_attr_tbl.last LOOP
2069 IF px_pricing_attr_tbl.exists(i) THEN
2070 px_pricing_attr_tbl(i).list_line_id := p_toListLineId;
2071 px_pricing_attr_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
2072 END IF;
2073 END LOOP;
2074 END IF;
2075 END processTdExclusion;
2076
2077
2078 PROCEDURE copyListLineExclusion
2079 (
2080 x_return_status OUT NOCOPY VARCHAR2
2081 ,x_msg_count OUT NOCOPY NUMBER
2082 ,x_msg_data OUT NOCOPY VARCHAR2
2083 ,p_fromListLineId IN NUMBER
2084 ,p_toListLineId IN NUMBER
2085 )
2086 IS
2087 l_pricing_attr_tbl OZF_OFFER_PVT.PRICING_ATTR_TBL_TYPE;
2088 l_errorLocation NUMBER;
2089 BEGIN
2090 -- initialize
2091 -- populate records
2092 --create exclusions
2093 x_return_status := FND_API.G_RET_STS_SUCCESS;
2094 l_pricing_attr_tbl.delete;
2095 populateTdExclusion
2096 (
2097 p_fromListLineId => p_fromListLineId
2098 ,x_pricing_attr_tbl => l_pricing_attr_tbl
2099 ,x_return_status => x_return_status
2100 ,x_msg_count => x_msg_count
2101 ,x_msg_data => x_msg_data
2102 );
2103 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2104 RAISE FND_API.G_EXC_ERROR;
2105 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2107 END IF;
2108 processTdExclusion
2109 (
2110 p_toListLineId => p_toListLineId
2111 ,px_pricing_attr_tbl => l_pricing_attr_tbl
2112 ,x_return_status => x_return_status
2113 ,x_msg_count => x_msg_count
2114 ,x_msg_data => x_msg_data
2115 );
2116 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2117 RAISE FND_API.G_EXC_ERROR;
2118 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2120 END IF;
2121 --dbms_output.put_line('Table cnt is :'||nvl(l_pricing_attr_tbl.count,0));
2122 OZF_OFFER_PVT.process_exclusions
2123 (
2124 p_init_msg_list => FND_API.G_FALSE
2125 ,p_api_version => 1.0
2126 ,p_commit => FND_API.G_FALSE
2127 ,x_return_status => x_return_status
2128 ,x_msg_count => x_msg_count
2129 ,x_msg_data => x_msg_data
2130 ,p_pricing_attr_tbl => l_pricing_attr_tbl
2131 ,x_error_location => l_errorLocation
2132 );
2133 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2134 RAISE FND_API.G_EXC_ERROR;
2135 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2137 END IF;
2138 EXCEPTION
2139 WHEN OTHERS THEN
2140 --dbms_output.put_line(sqlerrm);
2141 x_return_status := FND_API.G_RET_STS_ERROR;
2142 OE_MSG_PUB.count_and_get(
2143 p_count => x_msg_count
2144 , p_data => x_msg_data
2145 );
2146 END copyListLineExclusion;
2147
2148 /**
2149 COpies Line level limits from a given qp_list_line to another qp_list_line
2150 * p_listLineId List Line used to fetch the limits
2151 * p_modifier_tbl the Table used to fetch the newly created list lines into which the limits are to be copied
2152 */
2153 PROCEDURE copyTdLimits
2154 (
2155 x_return_status OUT NOCOPY VARCHAR2
2156 , x_msg_count OUT NOCOPY NUMBER
2157 , x_msg_data OUT NOCOPY VARCHAR2
2158 ,p_listLineId IN NUMBER
2159 , p_modifier_tbl IN QP_MODIFIERS_PUB.modifiers_tbl_type
2160 )
2161 IS
2162 BEGIN
2163 x_return_status := FND_API.G_RET_STS_SUCCESS;
2164 IF nvl(p_modifier_tbl.count,0) > 0 THEN
2165 FOR i in p_modifier_tbl.first .. p_modifier_tbl.last LOOP
2166 IF p_modifier_tbl.exists(i) THEN
2167 copyLimits
2168 (
2169 x_return_status => x_return_status
2170 , x_msg_count => x_msg_count
2171 , x_msg_data => x_msg_data
2172 , p_fromListLineId => p_listLineId
2173 , p_toListLineId => p_modifier_tbl(i).list_line_id
2174 );
2175 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2176 RAISE FND_API.G_EXC_ERROR;
2177 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2179 END IF;
2180 END IF;
2181 END LOOP;
2182 END IF;
2183 END copyTdLimits;
2184
2185 /**
2186 Copies Exclusions from a given qp_list_line to another qp_list_line
2187 * p_listLineId List Line used to fetch the Exclusions
2188 * p_modifier_tbl the Table used to fetch the newly created list lines into which the exclusions are to be copied
2189 */
2190 PROCEDURE copyTdExclusions
2191 (
2192 x_return_status OUT NOCOPY VARCHAR2
2193 ,x_msg_count OUT NOCOPY NUMBER
2194 ,x_msg_data OUT NOCOPY VARCHAR2
2195 , p_listLineId IN NUMBER
2196 , x_modifier_tbl IN QP_MODIFIERS_PUB.modifiers_tbl_type
2197 )
2198 IS
2199 BEGIN
2200 x_return_status := FND_API.G_RET_STS_SUCCESS;
2201 -- loop thru. modifiers tbl
2202 -- for each list_line_id in the table copy exclusion
2203 IF nvl(x_modifier_tbl.count,0) > 0 THEN
2204 FOR i in x_modifier_tbl.first .. x_modifier_tbl.last LOOP
2205 IF x_modifier_tbl.exists(i) THEN
2206 copyListLineExclusion
2207 (
2208 x_return_status => x_return_status
2209 ,x_msg_count => x_msg_count
2210 ,x_msg_data => x_msg_data
2211 ,p_fromListLineId => p_listLineId
2212 , p_toListLineId => x_modifier_tbl(i).list_line_id
2213 );
2214 --dbms_output.PUT_LINE('List line id passed in is:'||p_listLineId||':'||x_modifier_tbl(i).list_line_id);
2215 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2216 RAISE FND_API.G_EXC_ERROR;
2217 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2219 END IF;
2220 END IF;
2221 END LOOP;
2222 END IF;
2223 END copyTdExclusions;
2224
2225 /**
2226 Copies Qualifiers from a given qp_list_line to another qp_list_line
2227 * p_listLineId List Line used to fetch the Qualifiers
2228 * p_modifier_tbl the Table used to fetch the newly created list lines into which the Qualifiers are to be copied
2229 */
2230 PROCEDURE copyTdQualifiers
2231 (
2232 x_return_status OUT NOCOPY VARCHAR2
2233 ,x_msg_count OUT NOCOPY NUMBER
2234 ,x_msg_data OUT NOCOPY VARCHAR2
2235 , p_listLineId IN NUMBER
2236 , x_modifier_tbl IN QP_MODIFIERS_PUB.modifiers_tbl_type
2237 )
2238 IS
2239 BEGIN
2240 x_return_status := FND_API.G_RET_STS_SUCCESS;
2241 IF nvl(x_modifier_tbl.count,0) > 0 THEN
2242 FOR i in x_modifier_tbl.first .. x_modifier_tbl.last LOOP
2243 IF x_modifier_tbl.exists(i) THEN
2244 copyQualifiers
2245 (
2246 x_return_status => x_return_status
2247 , x_msg_count => x_msg_count
2248 , x_msg_data => x_msg_data
2249 , p_fromListLineId => p_listLineId
2250 , p_toListLineId => x_modifier_tbl(i).list_line_id
2251 );
2252 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2253 return;
2254 END IF;
2255 END IF;
2256 END LOOP;
2257 END IF;
2258 END copyTdQualifiers;
2259
2260
2261
2262 FUNCTION getTdLine(p_listLineId IN NUMBER)
2263 RETURN NUMBER
2264 IS
2265 CURSOR c_getTdLine(cp_listLineId NUMBER) IS
2266 SELECT related_modifier_id
2267 FROM ozf_related_deal_lines
2268 WHERE modifier_id = cp_listLineId;
2269 l_relatedLineId NUMBER;
2270 BEGIN
2271 OPEN c_getTdLine(cp_listLineId => p_listLineId);
2272 FETCH c_getTdLine INTO l_relatedLineId;
2273 IF c_getTdLine%NOTFOUND THEN
2274 l_relatedLineId := null;
2275 END IF;
2276 CLOSE c_getTdLine;
2277
2278 return l_relatedLineId;
2279 END getTdLine;
2280
2281
2282 /**
2283 Relates a trade deal Discount rule to a group of qp_list_lines. these qp_list_lines are actually the components of a single trade deal discount rule.
2284 * p_offerAdjustmentId The Adjustment under which the Relation was created
2285 * p_listLineId the primary list_line_id of the Trade Deal Discount Rule.
2286 * p_modifier_tbl the Table used to fetch the newly created list lines into which the Qualifiers are to be copied
2287 */
2288 PROCEDURE relateTdLines
2289 (
2290 x_return_status OUT NOCOPY VARCHAR2
2291 ,x_msg_count OUT NOCOPY NUMBER
2292 ,x_msg_data OUT NOCOPY VARCHAR2
2293 ,p_offerAdjustmentId IN NUMBER
2294 , p_listLineId IN NUMBER
2295 , p_modifier_tbl IN QP_MODIFIERS_PUB.modifiers_tbl_type
2296 )
2297 IS
2298 CURSOR c_accrualFlag(cp_listLineId NUMBER)
2299 IS
2300 SELECT accrual_flag
2301 FROM qp_list_lines
2302 WHERE list_line_id = cp_listLineId;
2303 BEGIN
2304 x_return_status := FND_API.G_RET_STS_SUCCESS;
2305 -- get accrual flag for given list line
2306 -- loop thru. the passed in table
2307 -- if the accrual flag for the list_line_id is the same as
2308 FOR l_accrualFlag IN c_accrualFlag(cp_listLineId => p_listLineId) LOOP
2309 IF nvl(P_modifier_tbl.count,0) > 0 THEN
2310 FOR j in p_modifier_tbl.first .. p_modifier_tbl.last LOOP
2311 IF p_modifier_tbl.exists(j) THEN
2312 IF nvl(l_accrualFlag.accrual_flag,'N') = nvl(p_modifier_tbl(j).accrual_flag,'N') THEN
2313 OZF_VOLUME_OFFER_ADJ.relate_lines
2314 (
2315 p_from_list_line_id => p_listLineId
2316 , p_to_list_line_id => p_modifier_tbl(j).list_line_id
2317 , p_offer_adjustment_id => p_offerAdjustmentId
2318 , x_return_status => x_return_status
2319 , x_msg_count => x_msg_count
2320 , x_msg_data => x_msg_data
2321 );
2322 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2323 RAISE FND_API.G_EXC_ERROR;
2324 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2326 END IF;
2327 IF getTdLine(p_listLineId) IS NOT NULL AND getTdLine(p_modifier_tbl(j).list_line_id) IS NOT NULL THEN
2328 OZF_VOLUME_OFFER_ADJ.relate_lines
2329 (
2330 p_from_list_line_id => getTdLine(p_listLineId)
2331 , p_to_list_line_id => getTdLine(p_modifier_tbl(j).list_line_id)
2332 , p_offer_adjustment_id => p_offerAdjustmentId
2333 , x_return_status => x_return_status
2334 , x_msg_count => x_msg_count
2335 , x_msg_data => x_msg_data
2336 );
2337 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2338 RAISE FND_API.G_EXC_ERROR;
2339 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2341 END IF;
2342 END IF;
2343 END IF;
2344 END IF;
2345 END LOOP;
2346 END IF;
2347 END LOOP;
2348 END relateTdLines;
2349
2350 /**
2351 Copies a list line into a new list line, with the start date as the effective date of the adjustmentid passed in
2352 */
2353 PROCEDURE copyTdLine
2354 (
2355 x_return_status OUT NOCOPY VARCHAR2
2356 ,x_msg_count OUT NOCOPY NUMBER
2357 ,x_msg_data OUT NOCOPY VARCHAR2
2358 ,p_offerAdjustmentId IN NUMBER
2359 , p_listLineId IN NUMBER
2360 , x_listLineId IN NUMBER
2361 , x_modifier_tbl OUT NOCOPY QP_MODIFIERS_PUB.modifiers_tbl_type
2362 )
2363 IS
2364 BEGIN
2365 -- initialize
2366 -- create discount rules
2367 -- copy exclusions
2368 -- copy qualifiers
2369 -- return list_line_id created
2370 -- return record created
2371 x_return_status := FND_API.G_RET_STS_SUCCESS;
2372 createTdLine
2373 (
2374 x_return_status => x_return_status
2375 ,x_msg_count => x_msg_count
2376 ,x_msg_data => x_msg_data
2377 ,p_offerAdjustmentId => p_offerAdjustmentId
2378 , p_listLineId => p_listLineId
2379 , x_modifier_tbl => x_modifier_tbl
2380 );
2381 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2382 RAISE FND_API.G_EXC_ERROR;
2383 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2385 END IF;
2386 copyTdExclusions
2387 (
2388 x_return_status => x_return_status
2389 ,x_msg_count => x_msg_count
2390 ,x_msg_data => x_msg_data
2391 , p_listLineId => p_listLineId
2392 , x_modifier_tbl => x_modifier_tbl
2393 );
2394 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2395 RAISE FND_API.G_EXC_ERROR;
2396 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2398 END IF;
2399 copyTdQualifiers
2400 (
2401 x_return_status => x_return_status
2402 ,x_msg_count => x_msg_count
2403 ,x_msg_data => x_msg_data
2404 , p_listLineId => p_listLineId
2405 , x_modifier_tbl => x_modifier_tbl
2406 );
2407 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2408 RAISE FND_API.G_EXC_ERROR;
2409 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2411 END IF;
2412 copyTdLimits
2413 (
2414 x_return_status => x_return_status
2415 ,x_msg_count => x_msg_count
2416 ,x_msg_data => x_msg_data
2417 , p_listLineId => p_listLineId
2418 , p_modifier_tbl => x_modifier_tbl
2419 );
2420 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2421 RAISE FND_API.G_EXC_ERROR;
2422 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2424 END IF;
2425 END copyTdLine;
2426 /**
2427 End Dates a Trade Deal Discount Rule with the effective date of the Adjustment id passed in
2428 */
2429 PROCEDURE endDateTdLine
2430 (
2431 x_return_status OUT NOCOPY VARCHAR2
2432 ,x_msg_count OUT NOCOPY NUMBER
2433 ,x_msg_data OUT NOCOPY VARCHAR2
2434 ,p_offerAdjustmentId IN NUMBER
2435 , p_listLineId IN NUMBER
2436 )
2437 IS
2438 l_modifier_line_tbl ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE;
2439 lx_modifier_tbl QP_MODIFIERS_PUB.modifiers_tbl_type;
2440 l_errorLocation NUMBER;
2441 BEGIN
2442 -- initialize
2443 -- populate end date lines
2444 -- process end date lines
2445 -- end date line
2446 x_return_status := FND_API.G_RET_STS_SUCCESS;
2447 ozf_utility_pvt.debug_message('before populateTdUpdLines :'||x_return_status);
2448 populateTdUpdLines
2449 (
2450 x_modifier_line_rec => l_modifier_line_tbl(1)
2451 , p_listLineId => p_listLineId
2452 ,x_return_status => x_return_status
2453 ,x_msg_count => x_msg_count
2454 ,x_msg_data => x_msg_data
2455 );
2456 ozf_utility_pvt.debug_message('after populateTdUpdLines :'||x_return_status);
2457 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2458 RAISE FND_API.G_EXC_ERROR;
2459 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2461 END IF;
2462 ozf_utility_pvt.debug_message('before processTdUpdLines :'||x_return_status);
2463
2464 processTdUpdLines
2465 (
2466 px_modifier_line_rec => l_modifier_line_tbl(1)
2467 , p_offerAdjustmentId => p_offerAdjustmentId
2468 , p_listLineId => p_listLineId
2469 ,x_return_status => x_return_status
2470 ,x_msg_count => x_msg_count
2471 ,x_msg_data => x_msg_data
2472 );
2473 ozf_utility_pvt.debug_message('after processTdUpdLines :'||x_return_status);
2474 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2475 RAISE FND_API.G_EXC_ERROR;
2476 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2478 END IF;
2479 ozf_utility_pvt.debug_message('before process_qp_list_lines :'||x_return_status);
2480 ozf_offer_pvt.process_qp_list_lines
2481 (
2482 x_return_status => x_return_status
2483 ,x_msg_count => x_msg_count
2484 ,x_msg_data => x_msg_data
2485 ,p_offer_type => 'DEAL'
2486 ,p_modifier_line_tbl => l_modifier_line_tbl
2487 ,p_list_header_id => l_modifier_line_tbl(1).list_header_id
2488 ,x_modifier_line_tbl => lx_modifier_tbl --QP_MODIFIERS_PUB.modifiers_tbl_type
2489 ,x_error_location => l_errorLocation
2490 );
2491 ozf_utility_pvt.debug_message('after process_qp_list_lines :'||x_return_status);
2492 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2493 RAISE FND_API.G_EXC_ERROR;
2494 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2496 END IF;
2497 EXCEPTION
2498 WHEN OTHERS THEN
2499 FND_MSG_PUB.count_and_get(
2500 p_encoded => FND_API.g_false
2501 , p_count => x_msg_count
2502 , p_data => x_msg_data
2503 );
2504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2505 END endDateTdLine;
2506
2507
2508 PROCEDURE processOldTdLine
2509 (
2510 x_return_status OUT NOCOPY VARCHAR2
2511 ,x_msg_count OUT NOCOPY NUMBER
2512 ,x_msg_data OUT NOCOPY VARCHAR2
2513 ,p_offerAdjustmentId IN NUMBER
2514 , p_listLineId IN NUMBER
2515 )
2516 IS
2517 l_modifier_tbl QP_MODIFIERS_PUB.modifiers_tbl_type;
2518 l_listLineId number;
2519 BEGIN
2520 x_return_status := FND_API.G_RET_STS_SUCCESS;
2521 ozf_utility_pvt.debug_message('before endDateTdLine :'||x_return_status);
2522 endDateTdLine
2523 (
2524 x_return_status => x_return_status
2525 ,x_msg_count => x_msg_count
2526 ,x_msg_data => x_msg_data
2527 ,p_offerAdjustmentId => p_offerAdjustmentId
2528 , p_listLineId => p_listLineId
2529 );
2530 ozf_utility_pvt.debug_message('after endDateTdLine :'||x_return_status);
2531 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2532 RAISE FND_API.G_EXC_ERROR;
2533 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2535 END IF;
2536 ozf_utility_pvt.debug_message('before copyTdLine :'||x_return_status);
2537 copyTdLine
2538 (
2539 x_return_status => x_return_status
2540 ,x_msg_count => x_msg_count
2541 ,x_msg_data => x_msg_data
2542 ,p_offerAdjustmentId => p_offerAdjustmentId
2543 , p_listLineId => p_listLineId
2544 , x_listLineId => l_listLineId
2545 , x_modifier_tbl => l_modifier_tbl
2546 );
2547 ozf_utility_pvt.debug_message('after copyTdLine :'||x_return_status);
2548
2549 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2550 RAISE FND_API.G_EXC_ERROR;
2551 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2553 END IF;
2554 ozf_utility_pvt.debug_message('before relateTdLine :'||x_return_status);
2555 relateTdLines
2556 (
2557 x_return_status => x_return_status
2558 ,x_msg_count => x_msg_count
2559 ,x_msg_data => x_msg_data
2560 ,p_offerAdjustmentId => p_offerAdjustmentId
2561 , p_listLineId => p_listLineId
2562 , p_modifier_tbl => l_modifier_tbl
2563 );
2564 ozf_utility_pvt.debug_message('after relateTdLine :'||x_return_status);
2565 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2566 RAISE FND_API.G_EXC_ERROR;
2567 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2569 END IF;
2570
2571 END ;
2572 /**
2573 Processes adjustment lines for Trade deal Adjustments
2574 */
2575 PROCEDURE process_old_td_discount
2576 (
2577 x_return_status OUT NOCOPY VARCHAR2
2578 ,x_msg_count OUT NOCOPY NUMBER
2579 ,x_msg_data OUT NOCOPY VARCHAR2
2580 ,p_offerAdjustmentId IN NUMBER
2581 )
2582 IS
2583 CURSOR c_adjLines(cp_offerAdjustmentId NUMBER) IS
2584 SELECT
2585 list_line_id
2586 FROM ozf_offer_adjustment_lines
2587 WHERE offer_adjustment_id = cp_offerAdjustmentId;
2588
2589 BEGIN
2590 -- initialize
2591 -- loop thru. all adjustment lines
2592 -- end date list_line
2593 -- copy list_line
2594 -- relate list_lines
2595 x_return_status := FND_API.G_RET_STS_SUCCESS;
2596 FOR l_adjLines in c_adjLines(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
2597 --dbms_output.put_line('Processiung :'||l_adjLines.list_line_id);
2598 ozf_utility_pvt.debug_message('before process old td line discounts :'||x_return_status);
2599
2600 processOldTdLine
2601 (
2602 x_return_status => x_return_status
2603 ,x_msg_count => x_msg_count
2604 ,x_msg_data => x_msg_data
2605 ,p_offerAdjustmentId => p_offerAdjustmentId
2606 , p_listLineId => l_adjLines.list_line_id
2607 );
2608 ozf_utility_pvt.debug_message('after process old td line discounts :'||x_return_status);
2609 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2610 RAISE FND_API.G_EXC_ERROR;
2611 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2613 END IF;
2614 END LOOP;
2615 END process_old_td_discount;
2616
2617 PROCEDURE process_old_discounts
2618 (
2619 x_return_status OUT NOCOPY VARCHAR2
2620 ,x_msg_count OUT NOCOPY NUMBER
2621 ,x_msg_data OUT NOCOPY VARCHAR2
2622 ,p_offerAdjustmentId IN NUMBER
2623 )
2624 IS
2625 CURSOR c_offerType(cp_offerAdjustmentId NUMBER) IS
2626 SELECT
2627 offer_type
2628 FROM ozf_offers a, ozf_offer_adjustments_b b
2629 WHERE a.qp_list_header_id = b.list_header_id
2630 AND b.offer_adjustment_id = cp_offerAdjustmentId;
2631 l_offerType c_offerType%ROWTYPE;
2632 BEGIN
2633 x_return_status := FND_API.G_RET_STS_SUCCESS;
2634 OPEN c_offerType(cp_offerAdjustmentId => p_offerAdjustmentId);
2635 FETCH c_offerType INTO l_offerType;
2636 CLOSE c_offerType;
2637 IF l_offerType.offer_type = 'DEAL' THEN
2638 ozf_utility_pvt.debug_message('before process old td discounts :'||x_return_status);
2639 process_old_td_discount
2640 (
2641 x_return_status => x_return_status
2642 , x_msg_count => x_msg_count
2643 , x_msg_data => x_msg_data
2644 , p_offerAdjustmentId => p_offerAdjustmentId
2645 );
2646 ozf_utility_pvt.debug_message('after process old td discounts :'||x_return_status);
2647 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2648 RAISE FND_API.G_EXC_ERROR;
2649 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2651 END IF;
2652 ELSIF l_offerType.offer_type = 'OID' THEN
2653 process_old_pg_discount
2654 (
2655 x_return_status => x_return_status
2656 , x_msg_count => x_msg_count
2657 , x_msg_data => x_msg_data
2658 , p_offerAdjustmentId => p_offerAdjustmentId
2659 );
2660 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2661 RAISE FND_API.G_EXC_ERROR;
2662 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2664 END IF;
2665 ELSIF l_offerType.offer_type = 'VOLUME_OFFER' THEN
2666 OZF_VOLUME_OFFER_ADJ.adjust_old_discounts
2667 (
2668 x_return_status => x_return_status
2669 ,x_msg_count => x_msg_count
2670 ,x_msg_data => x_msg_data
2671 ,p_offerAdjustmentId => p_offerAdjustmentId
2672 );
2673 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2674 RAISE FND_API.G_EXC_ERROR;
2675 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2677 END IF;
2678
2679 ELSIF l_offerType.offer_type IN ( 'ORDER','ACCRUAL','OFF_INVOICE') THEN
2680 process_old_reg_discount
2681 (
2682 x_return_status => x_return_status
2683 , x_msg_count => x_msg_count
2684 , x_msg_data => x_msg_data
2685 , p_offerAdjustmentId => p_offerAdjustmentId
2686 );
2687 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2688 RAISE FND_API.G_EXC_ERROR;
2689 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2691 END IF;
2692 END IF;
2693 END process_old_discounts;
2694 ---------------------------------------Process pg products--------------------
2695 PROCEDURE populateNewBuyProduct
2696 (
2697 x_return_status OUT NOCOPY VARCHAR2
2698 ,x_msg_count OUT NOCOPY NUMBER
2699 ,x_msg_data OUT NOCOPY VARCHAR2
2700 ,p_offerAdjNewLineId IN NUMBER
2701 , x_modifier_line_tbl OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE
2702 )
2703 IS
2704 CURSOR c_newBuy(cp_offerAdjNewLineId NUMBER) IS
2705 SELECT
2706 a.product_attribute
2707 , a.product_attr_value
2708 , a.uom_code
2709 , b.volume_from
2710 , b.volume_to
2711 , b.volume_type
2712 , b.end_date_active
2713 , c.effective_date
2714 , c.list_header_id
2715 FROM
2716 ozf_offer_adj_new_products a, ozf_offer_adj_new_lines b , ozf_offer_adjustments_b c
2717 WHERE a.offer_adj_new_line_id = b.offer_adj_new_line_id
2718 AND a.excluder_flag = 'N'
2719 AND b.offer_adjustment_id = c.offer_adjustment_id
2720 AND b.offer_adj_new_line_id = cp_offerAdjNewLineId;
2721 i NUMBER;
2722 BEGIN
2723 x_return_status := FND_API.G_RET_STS_SUCCESS;
2724 x_modifier_line_tbl.delete;
2725 i := 1;
2726 FOR l_newBuy in c_newBuy(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
2727 x_modifier_line_tbl(i).product_attr := l_newBuy.product_attribute;
2728 x_modifier_line_tbl(i).product_attr_val := l_newBuy.product_attr_value;
2729 x_modifier_line_tbl(i).product_uom_code := l_newBuy.uom_code;
2730 x_modifier_line_tbl(i).pricing_attr_value_from := l_newBuy.volume_from;
2731 x_modifier_line_tbl(i).pricing_attr := l_newBuy.volume_type;
2732 x_modifier_line_tbl(i).end_date_active := l_newBuy.end_date_active;
2733 x_modifier_line_tbl(i).start_date_active := l_newBuy.effective_date;
2734 x_modifier_line_tbl(i).list_header_id := l_newBuy.list_header_id;
2735 x_modifier_line_tbl(i).price_break_type_code := 'POINT';
2736 i := i+ 1;
2737 END LOOP;
2738 END populateNewBuyProduct;
2739
2740 PROCEDURE processNewBuyProduct
2741 (
2742 x_return_status OUT NOCOPY VARCHAR2
2743 ,x_msg_count OUT NOCOPY NUMBER
2744 ,x_msg_data OUT NOCOPY VARCHAR2
2745 ,p_offerAdjNewLineId IN NUMBER
2746 ,px_modifier_line_tbl IN OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE
2747 )
2748 IS
2749 BEGIN
2750 x_return_status := FND_API.G_RET_STS_SUCCESS;
2751 IF nvl(px_modifier_line_tbl.count,0) > 0 THEN
2752 FOR i in px_modifier_line_tbl.first .. px_modifier_line_tbl.last LOOP
2753 IF px_modifier_line_tbl.exists(i) THEN
2754 px_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
2755 px_modifier_line_tbl(i).list_line_type_code := 'RLTD';
2756 px_modifier_line_tbl(i).inactive_flag := 'Y';
2757 END IF;
2758 END LOOP;
2759 END IF;
2760 END processNewBuyProduct;
2761
2762 /**
2763 NOte that YOU cannot add new buy items to a PG offer if the modifier_level_code is LINE. This is a new QP Validation.
2764 So dont let the user add new buy lines to a PG offer and adjustment if the modifier level code is LINE
2765 Throw error QP_INVALID_PHASE_RLTD in the UI itself
2766 */
2767 PROCEDURE createNewBuyProduct
2768 (
2769 x_return_status OUT NOCOPY VARCHAR2
2770 ,x_msg_count OUT NOCOPY NUMBER
2771 ,x_msg_data OUT NOCOPY VARCHAR2
2772 ,p_offerAdjNewLineId IN NUMBER
2773 , x_modifier_tbl OUT NOCOPY QP_MODIFIERS_PUB.modifiers_tbl_type
2774 )
2775 IS
2776 l_modifier_line_tbl ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE;
2777 lx_modifier_tbl QP_MODIFIERS_PUB.modifiers_tbl_type;
2778 l_errorLocation NUMBER;
2779 BEGIN
2780 -- initilize
2781 -- populate data
2782 -- process data
2783 -- create
2784 -- return created table
2785 x_return_status := FND_API.G_RET_STS_SUCCESS;
2786 l_modifier_line_tbl.delete;
2787 lx_modifier_tbl.delete;
2788
2789 populateNewBuyProduct
2790 (
2791 x_return_status => x_return_status
2792 , x_msg_count => x_msg_count
2793 , x_msg_data => x_msg_data
2794 , p_offerAdjNewLineId => p_offerAdjNewLineId
2795 , x_modifier_line_tbl => l_modifier_line_tbl
2796 );
2797 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2798 RAISE FND_API.G_EXC_ERROR;
2799 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2801 END IF;
2802 processNewBuyProduct
2803 (
2804 x_return_status => x_return_status
2805 , x_msg_count => x_msg_count
2806 , x_msg_data => x_msg_data
2807 , p_offerAdjNewLineId => p_offerAdjNewLineId
2808 , px_modifier_line_tbl => l_modifier_line_tbl
2809 );
2810 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2811 RAISE FND_API.G_EXC_ERROR;
2812 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2814 END IF;
2815 OZF_OFFER_PVT.process_qp_list_lines
2816 (
2817 x_return_status => x_return_status
2818 ,x_msg_count => x_msg_count
2819 ,x_msg_data => x_msg_data
2820 ,p_offer_type => getOfferType(p_offerAdjNewLineId => p_offerAdjNewLineId)
2821 ,p_modifier_line_tbl => l_modifier_line_tbl
2822 ,p_list_header_id => l_modifier_line_tbl(1).list_header_id
2823 ,x_modifier_line_tbl => lx_modifier_tbl
2824 ,x_error_location => l_errorLocation
2825 );
2826 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2827 RAISE FND_API.G_EXC_ERROR;
2828 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2830 END IF;
2831 x_modifier_tbl := lx_modifier_tbl;
2832 EXCEPTION
2833 WHEN OTHERS THEN
2834 x_return_status := FND_API.G_RET_STS_ERROR;
2835 FND_MSG_PUB.count_and_get(
2836 p_encoded => FND_API.g_false
2837 , p_count => x_msg_count
2838 , p_data => x_msg_data
2839 );
2840 END createNewBuyProduct;
2841
2842 PROCEDURE processNewBuyProduct
2843 (
2844 x_return_status OUT NOCOPY VARCHAR2
2845 ,x_msg_count OUT NOCOPY NUMBER
2846 ,x_msg_data OUT NOCOPY VARCHAR2
2847 ,p_offerAdjNewLineId IN NUMBER
2848 )
2849 IS
2850 l_modifiers_tbl QP_MODIFIERS_PUB.modifiers_tbl_type;
2851 BEGIN
2852 -- initialize
2853 -- create new line
2854 -- create line in ozf_offer_adjustment_lines
2855 x_return_status := FND_API.G_RET_STS_SUCCESS;
2856 createNewBuyProduct
2857 (
2858 x_return_status => x_return_status
2859 ,x_msg_count => x_msg_count
2860 ,x_msg_data => x_msg_data
2861 ,p_offerAdjNewLineId => p_offerAdjNewLineId
2862 , x_modifier_tbl => l_modifiers_tbl
2863 );
2864 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2865 RAISE FND_API.G_EXC_ERROR;
2866 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2868 END IF;
2869 createAdjLines
2870 (
2871 x_return_status => x_return_status
2872 ,x_msg_count => x_msg_count
2873 ,x_msg_data => x_msg_data
2874 ,p_modifiers_tbl => l_modifiers_tbl
2875 ,p_offerAdjustmentId => getAdjustmentId( p_offerAdjNewLineId => p_offerAdjNewLineId)
2876 );
2877 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2878 RAISE FND_API.G_EXC_ERROR;
2879 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2881 END IF;
2882 EXCEPTION
2883 WHEN OTHERS THEN
2884 FND_MSG_PUB.count_and_get(
2885 p_encoded => FND_API.g_false
2886 , p_count => x_msg_count
2887 , p_data => x_msg_data
2888 );
2889 END processNewBuyProduct;
2890
2891 PROCEDURE processNewBuyProducts
2892 (
2893 x_return_status OUT NOCOPY VARCHAR2
2894 ,x_msg_count OUT NOCOPY NUMBER
2895 ,x_msg_data OUT NOCOPY VARCHAR2
2896 ,p_offerAdjustmentId IN NUMBER
2897 )
2898 IS
2899 CURSOR c_adjLines(cp_offerAdjustmentId NUMBER) IS
2900 SELECT offer_adj_new_line_id
2901 FROM ozf_offer_adj_new_lines
2902 WHERE tier_type <> 'DIS'
2903 AND offer_adjustment_id = cp_offerAdjustmentId;
2904 BEGIN
2905 -- initialize
2906 -- loop thru. individual lines and process each line
2907 x_return_status := FND_API.G_RET_STS_SUCCESS;
2908 FOR l_adjLines IN c_adjLines(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
2909 processNewBuyProduct
2910 (
2911 x_return_status => x_return_status
2912 ,x_msg_count => x_msg_count
2913 ,x_msg_data => x_msg_data
2914 ,p_offerAdjNewLineId => l_adjLines.offer_adj_new_line_id
2915 );
2916 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2917 RAISE FND_API.G_EXC_ERROR;
2918 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2920 END IF;
2921 END LOOP;
2922 END processNewBuyProducts;
2923 ----------------------------End PG Buy products------------------------------
2924 PROCEDURE populateNewGetProduct
2925 (
2926 x_return_status OUT NOCOPY VARCHAR2
2927 ,x_msg_count OUT NOCOPY NUMBER
2928 ,x_msg_data OUT NOCOPY VARCHAR2
2929 ,p_offerAdjNewLineId IN NUMBER
2930 ,x_modifier_line_tbl OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE
2931 )
2932 IS
2933 CURSOR c_newGet(cp_offerAdjNewLineId NUMBER) IS
2934 SELECT
2935 a.end_date_active
2936 , a.benefit_price_list_line_id
2937 , a.discount
2938 , a.discount_type
2939 , a.quantity
2940 , b.product_attribute
2941 , b.product_attr_value
2942 , b.uom_code
2943 , c.effective_date
2944 , c.list_header_id
2945 FROM ozf_offer_adj_new_lines a, ozf_offer_adj_new_products b, ozf_offer_adjustments_b c
2946 WHERE a.offer_adj_new_line_id = b.offer_adj_new_line_id
2947 AND a.offer_adjustment_id = c.offer_adjustment_id
2948 AND a.offer_adj_new_line_id = cp_offerAdjNewLineId;
2949
2950 i NUMBER;
2951 BEGIN
2952 x_return_status := FND_API.G_RET_STS_SUCCESS;
2953 x_modifier_line_tbl.delete;
2954 i := 1;
2955 FOR l_newGet IN c_newGet(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
2956 x_modifier_line_tbl(i).start_date_active := l_newGet.effective_date;
2957 x_modifier_line_tbl(i).end_date_active := l_newGet.end_date_active;
2958 x_modifier_line_tbl(i).list_header_id := l_newGet.list_header_id;
2959 x_modifier_line_tbl(i).product_attr := l_newGet.product_attribute;
2960 -- fix for bug # 5715744.
2961 --x_modifier_line_tbl(i).product_attr_val := l_newGet.product_attribute;
2962 x_modifier_line_tbl(i).product_attr_val := l_newGet.product_attr_value;
2963 -- end of bug # 5715744.
2964 x_modifier_line_tbl(i).benefit_price_list_line_id := l_newGet.benefit_price_list_line_id;
2965 x_modifier_line_tbl(i).benefit_uom_code := l_newGet.uom_code;
2966 x_modifier_line_tbl(i).operand := l_newGet.discount;
2967 x_modifier_line_tbl(i).arithmetic_operator := l_newGet.discount_type;
2968 x_modifier_line_tbl(i).benefit_qty := l_newGet.quantity;
2969 i := i + 1;
2970 END LOOP;
2971 END populateNewGetProduct;
2972
2973 PROCEDURE processNewGetProduct
2974 (
2975 x_return_status OUT NOCOPY VARCHAR2
2976 ,x_msg_count OUT NOCOPY NUMBER
2977 ,x_msg_data OUT NOCOPY VARCHAR2
2978 ,p_offerAdjNewLineId IN NUMBER
2979 ,px_modifier_line_tbl IN OUT NOCOPY ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE
2980 )
2981 IS
2982 BEGIN
2983 x_return_status := FND_API.G_RET_STS_SUCCESS;
2984 IF nvl(px_modifier_line_tbl.count,0) > 0 THEN
2985 FOR i in px_modifier_line_tbl.first .. px_modifier_line_tbl.last LOOP
2986 IF px_modifier_line_tbl.exists(i) THEN
2987 px_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
2988 px_modifier_line_tbl(i).list_line_type_code := 'DIS';
2989 px_modifier_line_tbl(i).inactive_flag := 'Y';
2990 --dbms_output.put_line('Dates :'||px_modifier_line_tbl(i).start_date_active||':'||px_modifier_line_tbl(i).end_date_active);
2991 END IF;
2992 END LOOP;
2993 END IF;
2994 END processNewGetProduct;
2995
2996 PROCEDURE createNewGetProduct
2997 (
2998 x_return_status OUT NOCOPY VARCHAR2
2999 ,x_msg_count OUT NOCOPY NUMBER
3000 ,x_msg_data OUT NOCOPY VARCHAR2
3001 ,p_offerAdjNewLineId IN NUMBER
3002 ,x_modifier_tbl OUT NOCOPY QP_MODIFIERS_PUB.modifiers_tbl_type
3003 )
3004 IS
3005 l_modifier_line_tbl ozf_offer_pvt.MODIFIER_LINE_Tbl_TYPE;
3006 lx_modifier_tbl QP_MODIFIERS_PUB.modifiers_tbl_type;
3007 l_errorLocation NUMBER;
3008 BEGIN
3009 -- initialize
3010 -- populate data in qp structures
3011 -- process data in qp structures
3012 -- create
3013 -- return the created table
3014 x_return_status := FND_API.G_RET_STS_SUCCESS;
3015 l_modifier_line_tbl.delete;
3016
3017 populateNewGetProduct
3018 (
3019 x_return_status => x_return_status
3020 ,x_msg_count => x_msg_count
3021 ,x_msg_data => x_msg_data
3022 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3023 ,x_modifier_line_tbl => l_modifier_line_tbl
3024 );
3025 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3026 RAISE FND_API.G_EXC_ERROR;
3027 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3028 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3029 END IF;
3030 processNewGetProduct
3031 (
3032 x_return_status => x_return_status
3033 ,x_msg_count => x_msg_count
3034 ,x_msg_data => x_msg_data
3035 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3036 ,px_modifier_line_tbl => l_modifier_line_tbl
3037 );
3038 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3039 RAISE FND_API.G_EXC_ERROR;
3040 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3042 END IF;
3043 OZF_OFFER_PVT.process_qp_list_lines
3044 (
3045 x_return_status => x_return_status
3046 ,x_msg_count => x_msg_count
3047 ,x_msg_data => x_msg_data
3048 ,p_offer_type => getOfferType(p_offerAdjNewLineId => p_offerAdjNewLineId)
3049 ,p_modifier_line_tbl => l_modifier_line_tbl
3050 ,p_list_header_id => l_modifier_line_tbl(1).list_header_id
3051 ,x_modifier_line_tbl => lx_modifier_tbl
3052 ,x_error_location => l_errorLocation
3053 );
3054 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3055 RAISE FND_API.G_EXC_ERROR;
3056 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3058 END IF;
3059 x_modifier_tbl := lx_modifier_tbl;
3060 END createNewGetProduct;
3061
3062 PROCEDURE processNewGetProduct
3063 (
3064 x_return_status OUT NOCOPY VARCHAR2
3065 ,x_msg_count OUT NOCOPY NUMBER
3066 ,x_msg_data OUT NOCOPY VARCHAR2
3067 ,p_offerAdjNewLineId IN NUMBER
3068 )
3069 IS
3070 l_modifiers_tbl QP_MODIFIERS_PUB.modifiers_tbl_type;
3071 BEGIN
3072 -- initialize
3073 -- create new get product
3074 -- create offer_adjustment_line
3075 x_return_status := FND_API.G_RET_STS_SUCCESS;
3076 l_modifiers_tbl.delete;
3077 createNewGetProduct
3078 (
3079 x_return_status => x_return_status
3080 ,x_msg_count => x_msg_count
3081 ,x_msg_data => x_msg_data
3082 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3083 ,x_modifier_tbl => l_modifiers_tbl
3084 );
3085 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3086 RAISE FND_API.G_EXC_ERROR;
3087 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3089 END IF;
3090 createAdjLines
3091 (
3092 x_return_status => x_return_status
3093 ,x_msg_count => x_msg_count
3094 ,x_msg_data => x_msg_data
3095 ,p_modifiers_tbl => l_modifiers_tbl
3096 ,p_offerAdjustmentId => getAdjustmentId( p_offerAdjNewLineId => p_offerAdjNewLineId)
3097 );
3098 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3099 RAISE FND_API.G_EXC_ERROR;
3100 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3102 END IF;
3103 EXCEPTION
3104 WHEN OTHERS THEN
3105 FND_MSG_PUB.count_and_get(
3106 p_encoded => FND_API.g_false
3107 , p_count => x_msg_count
3108 , p_data => x_msg_data
3109 );
3110 END processNewGetProduct;
3111
3112 PROCEDURE processNewGetProducts
3113 (
3114 x_return_status OUT NOCOPY VARCHAR2
3115 ,x_msg_count OUT NOCOPY NUMBER
3116 ,x_msg_data OUT NOCOPY VARCHAR2
3117 ,p_offerAdjustmentId IN NUMBER
3118 )
3119 IS
3120 CURSOR c_adjLines(cp_offerAdjustmentId NUMBER) IS
3121 SELECT offer_adj_new_line_id
3122 FROM ozf_offer_adj_new_lines
3123 WHERE tier_type = 'DIS'
3124 AND offer_adjustment_id = cp_offerAdjustmentId;
3125 BEGIN
3126 -- initialize
3127 -- loop thru. individual lines and process each line
3128 x_return_status := FND_API.G_RET_STS_SUCCESS;
3129 FOR l_adjLines IN c_adjLines(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
3130 processNewGetProduct
3131 (
3132 x_return_status => x_return_status
3133 ,x_msg_count => x_msg_count
3134 ,x_msg_data => x_msg_data
3135 ,p_offerAdjNewLineId => l_adjLines.offer_adj_new_line_id
3136 );
3137 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3138 RAISE FND_API.G_EXC_ERROR;
3139 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3141 END IF;
3142 END LOOP;
3143 END processNewGetProducts;
3144
3145 PROCEDURE processNewPgProducts
3146 (
3147 x_return_status OUT NOCOPY VARCHAR2
3148 ,x_msg_count OUT NOCOPY NUMBER
3149 ,x_msg_data OUT NOCOPY VARCHAR2
3150 ,p_offerAdjustmentId IN NUMBER
3151 )
3152 IS
3153 BEGIN
3154 x_return_status := FND_API.G_RET_STS_SUCCESS;
3155 -- initialize
3156 -- process new buy products
3157 -- process new get products
3158 processNewBuyProducts
3159 (
3160 x_return_status => x_return_status
3161 ,x_msg_count => x_msg_count
3162 ,x_msg_data => x_msg_data
3163 ,p_offerAdjustmentId => p_offerAdjustmentId
3164 );
3165 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3166 RAISE FND_API.G_EXC_ERROR;
3167 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3169 END IF;
3170 processNewGetProducts
3171 (
3172 x_return_status => x_return_status
3173 ,x_msg_count => x_msg_count
3174 ,x_msg_data => x_msg_data
3175 ,p_offerAdjustmentId => p_offerAdjustmentId
3176 );
3177 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3178 RAISE FND_API.G_EXC_ERROR;
3179 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3181 END IF;
3182 EXCEPTION
3183 WHEN OTHERS THEN
3184 x_return_status := FND_API.G_RET_STS_ERROR;
3185 FND_MSG_PUB.count_and_get(
3186 p_encoded => FND_API.g_false
3187 , p_count => x_msg_count
3188 , p_data => x_msg_data
3189 );
3190 END processNewPgProducts;
3191 ---------------end process pg products--------------------------
3192 PROCEDURE populateDisDiscounts
3193 (
3194 x_return_status OUT NOCOPY VARCHAR2
3195 ,x_msg_count OUT NOCOPY NUMBER
3196 ,x_msg_data OUT NOCOPY VARCHAR2
3197 ,p_offerAdjNewLineId IN NUMBER
3198 ,x_modifier_line_tbl OUT NOCOPY ozf_offer_pvt.modifier_line_tbl_type
3199 )
3200 IS
3201 CURSOR c_offerAdjustmentLines(cp_offerAdjNewLineId NUMBER)
3202 IS
3203 SELECT
3204 a.offer_adj_new_line_id
3205 , a.offer_adjustment_id
3206 , a.volume_from
3207 , a.volume_to
3208 , a.volume_type
3209 , a.discount
3210 , a.discount_type
3211 , a.tier_type
3212 , a.td_discount
3213 , a.td_discount_type
3214 , a.quantity
3215 , a.benefit_price_list_line_id
3216 , a.parent_adj_line_id
3217 , a.start_date_active
3218 , a.end_date_active
3219 , b.product_context
3220 , b.product_attribute
3221 , b.product_attr_value
3222 , b.excluder_flag
3223 , b.uom_code
3224 , c.list_header_id
3225 , c.effective_date
3226 FROM
3227 ozf_offer_adj_new_lines a, ozf_offer_adj_new_products b , ozf_offer_adjustments_b c
3228 WHERE a.offer_adj_new_line_id = b.offer_adj_new_line_id
3229 AND a.offer_adjustment_id = c.offer_adjustment_id
3230 and a.offer_adj_new_line_id = cp_offerAdjNewLineId;
3231 i NUMBER;
3232
3233 BEGIN
3234 -- initialize
3235 -- loop thru. the records and populate
3236 x_return_status := FND_API.G_RET_STS_SUCCESS;
3237 x_modifier_line_tbl.delete;
3238 i := 1;
3239 FOR l_offerAdjustmentLines IN c_offerAdjustmentLines(cp_offerAdjNewLineId => p_offerAdjNewLineId)
3240 LOOP
3241 x_modifier_line_tbl(i).list_header_id := l_offerAdjustmentLines.list_header_id;
3242 x_modifier_line_tbl(i).list_line_id := FND_API.G_MISS_NUM;
3243 x_modifier_line_tbl(i).list_line_type_code := l_offerAdjustmentLines.tier_type;
3244 x_modifier_line_tbl(i).operand := l_offerAdjustmentLines.discount;
3245 x_modifier_line_tbl(i).arithmetic_operator := l_offerAdjustmentLines.discount_type;
3246 x_modifier_line_tbl(i).product_attr := l_offerAdjustmentLines.product_attribute;
3247 x_modifier_line_tbl(i).product_attr_val := l_offerAdjustmentLines.product_attr_value;
3248 x_modifier_line_tbl(i).product_uom_code := l_offerAdjustmentLines.uom_code;
3249 x_modifier_line_tbl(i).pricing_attr := l_offerAdjustmentLines.volume_type;
3250 x_modifier_line_tbl(i).pricing_attr_value_from := l_offerAdjustmentLines.volume_from;
3251 x_modifier_line_tbl(i).inactive_flag := 'Y';
3252 x_modifier_line_tbl(i).pricing_attribute_id := FND_API.G_MISS_NUM;
3253
3254 END LOOP;
3255 END populateDisDiscounts;
3256
3257 PROCEDURE processDisDiscounts
3258 (
3259 x_return_status OUT NOCOPY VARCHAR2
3260 ,x_msg_count OUT NOCOPY NUMBER
3261 ,x_msg_data OUT NOCOPY VARCHAR2
3262 ,p_offerAdjNewLineId IN NUMBER
3263 ,px_modifier_line_tbl IN OUT NOCOPY ozf_offer_pvt.modifier_line_tbl_type
3264 )
3265 IS
3266 BEGIN
3267 x_return_status := FND_API.G_RET_STS_SUCCESS;
3268 IF nvl(px_modifier_line_tbl.count,0) > 0 THEN
3269 FOR i IN px_modifier_line_tbl.first .. px_modifier_line_tbl.last LOOP
3270 IF px_modifier_line_tbl.exists(i) THEN
3271 px_modifier_line_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
3272 px_modifier_line_tbl(i).start_date_active := getEffectiveDate(getAdjustmentId(p_offerAdjNewLineId => p_offerAdjNewLineId));
3273 END IF;
3274 END LOOP;
3275 END IF;
3276 END processDisDiscounts;
3277
3278 PROCEDURE processNewStProduct
3279 (
3280 x_return_status OUT NOCOPY VARCHAR2
3281 ,x_msg_count OUT NOCOPY NUMBER
3282 ,x_msg_data OUT NOCOPY VARCHAR2
3283 ,p_offerAdjNewLineId IN NUMBER
3284 )
3285 IS
3286 l_modifier_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
3287 l_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
3288 l_errorLocation NUMBER;
3289 BEGIN
3290 -- initialize
3291 -- populate discounts
3292 -- create qp_list_lines
3293 -- create ozf_offer_adjustment_lines with created from adjustments = y -- required only for processing with bugets
3294 -- need to look at relating offer_adj_new_line_id with the qp list_line_id created. may be useful later for audit reporting and Sarbines-Auxley compliance.
3295 x_return_status := FND_API.G_RET_STS_SUCCESS;
3296 l_modifier_line_tbl.delete;
3297 populateDisDiscounts
3298 (
3299 x_return_status => x_return_status
3300 ,x_msg_count => x_msg_count
3301 ,x_msg_data => x_msg_data
3302 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3303 ,x_modifier_line_tbl => l_modifier_line_tbl
3304 );
3305 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3306 RAISE FND_API.G_EXC_ERROR;
3307 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3309 END IF;
3310 processDisDiscounts
3311 (
3312 x_return_status => x_return_status
3313 ,x_msg_count => x_msg_count
3314 ,x_msg_data => x_msg_data
3315 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3316 ,px_modifier_line_tbl => l_modifier_line_tbl
3317 );
3318 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3319 RAISE FND_API.G_EXC_ERROR;
3320 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3322 END IF;
3323 OZF_OFFER_PVT.process_qp_list_lines
3324 (
3325 x_return_status => x_return_status
3326 ,x_msg_count => x_msg_count
3327 ,x_msg_data => x_msg_data
3328 ,p_offer_type => getOfferType(p_offerAdjNewLineId => p_offerAdjNewLineId)
3329 ,p_modifier_line_tbl => l_modifier_line_tbl
3330 ,p_list_header_id => l_modifier_line_tbl(1).list_header_id
3331 ,x_modifier_line_tbl => l_modifiers_tbl
3332 ,x_error_location => l_errorLocation
3333 );
3334 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3335 RAISE FND_API.G_EXC_ERROR;
3336 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3338 END IF;
3339 createAdjLines
3340 (
3341 x_return_status => x_return_status
3342 ,x_msg_count => x_msg_count
3343 ,x_msg_data => x_msg_data
3344 ,p_modifiers_tbl => l_modifiers_tbl
3345 ,p_offerAdjustmentId => getAdjustmentId( p_offerAdjNewLineId => p_offerAdjNewLineId)
3346 );
3347 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3348 RAISE FND_API.G_EXC_ERROR;
3349 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3351 END IF;
3352 END processNewStProduct;
3353
3354
3355 PROCEDURE populateAdvancedOptions
3356 (
3357 x_return_status OUT NOCOPY VARCHAR2
3358 ,x_msg_count OUT NOCOPY NUMBER
3359 ,x_msg_data OUT NOCOPY VARCHAR2
3360 ,p_listHeaderId IN NUMBER
3361 , px_modifier_line_rec IN OUT NOCOPY qp_modifiers_pub.modifiers_rec_type
3362 )
3363 IS
3364 CURSOR c_advOpt(cp_listHeaderId NUMBER) IS
3365 SELECT
3366 proration_type_code
3367 , product_precedence
3368 , pricing_group_sequence
3369 , pricing_phase_id
3370 , print_on_invoice_flag
3371 , incompatibility_grp_code
3372 FROM qp_list_lines
3373 WHERE list_header_id = cp_listHeaderId
3374 AND rownum < 2;
3375 BEGIN
3376 x_return_status := FND_API.G_RET_STS_SUCCESS;
3377 FOR l_advOpt IN c_advOpt(cp_listHeaderId => p_listHeaderId ) LOOP
3378 px_modifier_line_rec.proration_type_code := l_advOpt.proration_type_code;
3379 px_modifier_line_rec.product_precedence := l_advOpt.product_precedence;
3380 px_modifier_line_rec.pricing_group_sequence := l_advOpt.pricing_group_sequence;
3381 px_modifier_line_rec.pricing_phase_id := l_advOpt.pricing_phase_id;
3382 px_modifier_line_rec.print_on_invoice_flag := l_advOpt.print_on_invoice_flag;
3383 px_modifier_line_rec.incompatibility_grp_code := l_advOpt.incompatibility_grp_code;
3384 END LOOP;
3385 END populateAdvancedOptions;
3386
3387 PROCEDURE processPbhData
3388 (
3389 x_return_status OUT NOCOPY VARCHAR2
3390 ,x_msg_count OUT NOCOPY NUMBER
3391 ,x_msg_data OUT NOCOPY VARCHAR2
3392 ,p_offerAdjNewLineId IN NUMBER
3393 , px_modifier_line_rec IN OUT NOCOPY qp_modifiers_pub.modifiers_rec_type
3394 )
3395 IS
3396 CURSOR c_offerDetails(cp_offerAdjNewLineId NUMBER) IS
3397 SELECT
3398 b.effective_date
3399 , b.list_header_id
3400 , decode(c.offer_type,'ACCRUAL','Y','N') accrual_flag
3401 , c.modifier_level_code
3402 FROM
3403 ozf_offer_adj_new_lines a, ozf_offer_adjustments_b b, ozf_offers c
3404 WHERE a.offer_adjustment_id = b.offer_adjustment_id
3405 AND b.list_header_id = c.qp_list_header_id
3406 AND a.offer_adj_new_line_id = cp_offerAdjNewLineId;
3407 i NUMBER;
3408 BEGIN
3409 x_return_status := FND_API.G_RET_STS_SUCCESS;
3410 i := 1;
3411 FOR l_offerDetails IN c_offerDetails(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
3412 px_modifier_line_rec.operation := QP_GLOBALS.G_OPR_CREATE;
3413 px_modifier_line_rec.start_date_active := l_offerDetails.effective_date;
3414 px_modifier_line_rec.list_header_id := l_offerDetails.list_header_id;
3415 px_modifier_line_rec.accrual_flag := l_offerDetails.accrual_flag;
3416 -- x_modifiers_tbl(i).proration_type_code := l_advOpt.proration_type_code;
3417 -- x_modifiers_tbl(i).product_precedence := l_advOpt.product_precedence;
3418 px_modifier_line_rec.modifier_level_code := l_offerDetails.modifier_level_code;
3419 px_modifier_line_rec.price_break_type_code := 'POINT';
3420
3421 -- x_modifiers_tbl(i).pricing_group_sequence := l_advOpt.pricing_group_sequence;
3422 -- x_modifiers_tbl(i).pricing_phase_id := l_advOpt.pricing_phase_id;
3423 -- x_modifiers_tbl(i).print_on_invoice_flag := l_advOpt.print_on_invoice_flag;
3424 -- x_modifiers_tbl(i).incompatibility_grp_code := l_advOpt.incompatibility_grp_code;
3425 -- px_modifier_line_rec.price_break_type_code := l_offerDetails.price_break_type_code;
3426 populateAdvancedOptions
3427 (
3428 x_return_status => x_return_status
3429 ,x_msg_count => x_msg_count
3430 ,x_msg_data => x_msg_data
3431 ,p_listHeaderId => l_offerDetails.list_header_id
3432 , px_modifier_line_rec => px_modifier_line_rec
3433 );
3434 i := i + 1;
3435 END LOOP;
3436 END processPbhData;
3437
3438 PROCEDURE populatePbhPricingAttr
3439 (
3440 x_return_status OUT NOCOPY VARCHAR2
3441 ,x_msg_count OUT NOCOPY NUMBER
3442 ,x_msg_data OUT NOCOPY VARCHAR2
3443 ,p_offerAdjNewLineId IN NUMBER
3444 ,x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
3445 , p_index IN NUMBER
3446 )
3447 IS
3448 CURSOR c_productAttributes(cp_offerAdjNewLineId NUMBER) IS
3449 SELECT a.product_context
3450 , a.product_attribute
3451 , a.product_attr_value
3452 , a.uom_code
3453 , a.excluder_flag
3454 , b.volume_type
3455 FROM ozf_offer_adj_new_products a, ozf_offer_adj_new_lines b
3456 WHERE
3457 a.offer_adj_new_line_id = b.offer_adj_new_line_id
3458 AND a.offer_adj_new_line_id = cp_offerAdjNewLineId;
3459 i NUMBER;
3460 BEGIN
3461 x_return_status := FND_API.G_RET_STS_SUCCESS;
3462 i := 1;
3463 FOR l_productAttributes IN c_productAttributes(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
3464 x_pricing_attr_tbl(i).product_attribute_context := l_productAttributes.product_context;
3465 x_pricing_attr_tbl(i).product_attribute := l_productAttributes.product_attribute;
3466 x_pricing_attr_tbl(i).product_attr_value := l_productAttributes.product_attr_value;
3467 x_pricing_attr_tbl(i).product_uom_code := l_productAttributes.uom_code;
3468 x_pricing_attr_tbl(i).excluder_flag := l_productAttributes.excluder_flag;
3469 x_pricing_attr_tbl(i).pricing_attribute_context := 'VOLUME'; --l_mtLines.pricing_attribute_context;
3470 x_pricing_attr_tbl(i).pricing_attribute := l_productAttributes.volume_type;
3471 x_pricing_attr_tbl(i).comparison_operator_code := 'BETWEEN';
3472 x_pricing_attr_tbl(i).modifiers_index := p_index;
3473 x_pricing_attr_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
3474 x_pricing_attr_tbl(i).pricing_attribute_id := FND_API.G_MISS_NUM;
3475 x_pricing_attr_tbl(i).attribute_grouping_no := FND_API.G_MISS_NUM;
3476 i := i + 1;
3477 END LOOP;
3478 END populatePbhPricingAttr;
3479
3480 PROCEDURE populatePbhDiscounts
3481 (
3482 x_return_status OUT NOCOPY VARCHAR2
3483 ,x_msg_count OUT NOCOPY NUMBER
3484 ,x_msg_data OUT NOCOPY VARCHAR2
3485 ,p_offerAdjNewLineId IN NUMBER
3486 , x_modifier_line_rec OUT NOCOPY qp_modifiers_pub.modifiers_rec_type
3487 )
3488 IS
3489 CURSOR c_pbhData (cp_offerAdjNewLineId NUMBER) IS
3490 SELECT
3491 a.tier_type
3492 --, c.effective_date
3493 --, c.list_header_id
3494 --, d.modifier_level_code
3495 --, decode(d.offer_type, 'ACCRUAL','Y','N') accrual_flag
3496 , a.end_date_active
3497 FROM ozf_offer_adj_new_lines a
3498 WHERE a.offer_adj_new_line_id = cp_offerAdjNewLineId;
3499
3500 BEGIN
3501 x_return_status := FND_API.G_RET_STS_SUCCESS;
3502 FOR l_pbhData IN c_pbhData(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
3503 x_modifier_line_rec.operation := QP_GLOBALS.G_OPR_CREATE;
3504 x_modifier_line_rec.list_line_type_code := 'PBH';
3505 x_modifier_line_rec.automatic_flag := 'Y';
3506 -- x_modifiers_tbl(i).start_date_active := l_hdrLines.effective_date;
3507 x_modifier_line_rec.end_date_active := l_pbhData.end_date_active;
3508 -- x_modifiers_tbl(i).list_header_id := l_hdrLines.list_header_id;
3509 -- x_modifiers_tbl(i).accrual_flag := l_hdrLines.accrual_flag;
3510 -- x_modifiers_tbl(i).proration_type_code := l_advOpt.proration_type_code;
3511 -- x_modifiers_tbl(i).product_precedence := l_advOpt.product_precedence;
3512 -- x_modifiers_tbl(i).modifier_level_code := l_hdrLines.modifier_level_code;
3513 -- x_modifiers_tbl(i).pricing_group_sequence := l_advOpt.pricing_group_sequence;
3514 -- x_modifiers_tbl(i).pricing_phase_id := l_advOpt.pricing_phase_id;
3515 -- x_modifiers_tbl(i).print_on_invoice_flag := l_advOpt.print_on_invoice_flag;
3516 -- x_modifiers_tbl(i).incompatibility_grp_code := l_advOpt.incompatibility_grp_code;
3517 x_modifier_line_rec.price_break_type_code := 'POINT'; --
3518 processPbhData
3519 (
3520 x_return_status => x_return_status
3521 ,x_msg_count => x_msg_count
3522 ,x_msg_data => x_msg_data
3523 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3524 , px_modifier_line_rec => x_modifier_line_rec
3525 );
3526 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3527 RAISE FND_API.G_EXC_ERROR;
3528 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3530 END IF;
3531 END LOOP;
3532 END populatePbhDiscounts;
3533 /*
3534 PROCEDURE populateCDiscounts
3535 (
3536 x_return_status OUT NOCOPY VARCHAR2
3537 ,x_msg_count OUT NOCOPY NUMBER
3538 ,x_msg_data OUT NOCOPY VARCHAR2
3539 ,p_offerAdjNewLineId IN NUMBER
3540 , x_modifier_line_rec OUT NOCOPY qp_modifiers_pub.modifiers_rec_type
3541 )
3542 IS
3543 BEGIN
3544 x_return_status := FND_API.G_RET_STS_SUCCESS;
3545 -- initialize
3546 -- loop thru. discount lines and populate discounts for each line
3547
3548 END populateDisDiscounts;*/
3549
3550 PROCEDURE populateDisDiscounts
3551 (
3552 x_return_status OUT NOCOPY VARCHAR2
3553 ,x_msg_count OUT NOCOPY NUMBER
3554 ,x_msg_data OUT NOCOPY VARCHAR2
3555 ,p_offerAdjNewLineId IN NUMBER
3556 ,x_modifier_line_rec OUT NOCOPY qp_modifiers_pub.modifiers_rec_type
3557 )
3558 IS
3559 CURSOR c_discounts(cp_offerAdjNewLineId NUMBER) IS
3560 SELECT
3561 discount
3562 , discount_type
3563 , tier_type
3564 FROM ozf_offer_adj_new_lines
3565 WHERE offer_adj_new_line_id = cp_offerAdjNewLineId;
3566
3567 BEGIN
3568 x_return_status := FND_API.G_RET_STS_SUCCESS;
3569 FOR l_discounts IN c_discounts(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
3570
3571 x_modifier_line_rec.operation := QP_GLOBALS.G_OPR_CREATE;
3572 x_modifier_line_rec.list_line_type_code := l_discounts.tier_type;
3573 x_modifier_line_rec.automatic_flag := 'Y';
3574 x_modifier_line_rec.operand := l_discounts.discount;
3575 x_modifier_line_rec.arithmetic_operator := l_discounts.discount_type;
3576 x_modifier_line_rec.rltd_modifier_grp_type := 'PRICE BREAK';
3577 x_modifier_line_rec.rltd_modifier_grp_no := 1;
3578 x_modifier_line_rec.modifier_parent_index := 1;
3579 -- x_modifier_line_rec.price_break_type_code := 'POINT';
3580 processPbhData
3581 (
3582 x_return_status => x_return_status
3583 ,x_msg_count => x_msg_count
3584 ,x_msg_data => x_msg_data
3585 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3586 , px_modifier_line_rec => x_modifier_line_rec
3587 );
3588 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3589 RAISE FND_API.G_EXC_ERROR;
3590 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3592 END IF;
3593
3594 END LOOP;
3595 END populateDisDiscounts;
3596
3597 PROCEDURE populateDisPricingAttributes
3598 (
3599 x_return_status OUT NOCOPY VARCHAR2
3600 ,x_msg_count OUT NOCOPY NUMBER
3601 ,x_msg_data OUT NOCOPY VARCHAR2
3602 ,p_offerAdjNewLineId IN NUMBER
3603 , x_pricing_attr_rec OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Rec_Type
3604 , p_index IN NUMBER
3605 )
3606 IS
3607 CURSOR c_pricingAttributes(cp_offerAdjNewLineId NUMBER) IS
3608 SELECT a.product_context
3609 , a.product_attribute
3610 , a.product_attr_value
3611 , a.uom_code
3612 , a.excluder_flag
3613 , b.volume_type
3614 , b.volume_from
3615 , b.volume_to
3616 FROM ozf_offer_adj_new_products a, ozf_offer_adj_new_lines b
3617 WHERE
3618 a.offer_adj_new_line_id = b.parent_adj_line_id
3619 AND a.excluder_flag = 'N'
3620 AND b.offer_adj_new_line_id = cp_offerAdjNewLineId;
3621 BEGIN
3622 x_return_status := FND_API.G_RET_STS_SUCCESS;
3623 x_pricing_attr_rec := null;
3624 FOR l_pricingAttributes in c_pricingAttributes(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
3625 x_pricing_attr_rec.pricing_attribute_id := FND_API.G_MISS_NUM;
3626 x_pricing_attr_rec.attribute_grouping_no := FND_API.G_MISS_NUM;
3627 x_pricing_attr_rec.product_attribute_context := l_pricingAttributes.product_context;
3628 x_pricing_attr_rec.product_attribute := l_pricingAttributes.product_attribute;
3629 x_pricing_attr_rec.product_attr_value := l_pricingAttributes.product_attr_value;
3630 x_pricing_attr_rec.product_uom_code := l_pricingAttributes.uom_code;
3631 x_pricing_attr_rec.excluder_flag := l_pricingAttributes.excluder_flag;
3632 x_pricing_attr_rec.pricing_attribute_context := 'VOLUME';
3633 x_pricing_attr_rec.pricing_attribute := l_pricingAttributes.volume_type;
3634 x_pricing_attr_rec.pricing_attr_value_from := l_pricingAttributes.volume_from;
3635 x_pricing_attr_rec.pricing_attr_value_to := l_pricingAttributes.volume_to;
3636 x_pricing_attr_rec.comparison_operator_code := 'BETWEEN';
3637 x_pricing_attr_rec.modifiers_index := p_index;
3638 x_pricing_attr_rec.operation := QP_GLOBALS.G_OPR_CREATE;
3639 END LOOP;
3640 END populateDisPricingAttributes;
3641
3642
3643 PROCEDURE populateDisData
3644 (
3645 x_return_status OUT NOCOPY VARCHAR2
3646 ,x_msg_count OUT NOCOPY NUMBER
3647 ,x_msg_data OUT NOCOPY VARCHAR2
3648 ,p_offerAdjNewLineId IN NUMBER
3649 , x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
3650 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
3651 )
3652 IS
3653 CURSOR c_adjNewLines(cp_offerAdjNewLineId NUMBER) IS
3654 SELECT offer_adj_new_line_id
3655 FROM ozf_offer_adj_new_lines
3656 WHERE parent_adj_line_id = cp_offerAdjNewLineId;
3657 i NUMBER;
3658 BEGIN
3659 x_return_status := FND_API.G_RET_STS_SUCCESS;
3660 i := 2; -- hardcode since dis lines will always start at 2
3661 FOR l_adjNewLines IN c_adjNewLines(cp_offerAdjNewLineId => p_offerAdjNewLineId) LOOP
3662 populateDisDiscounts
3663 (
3664 x_return_status => x_return_status
3665 ,x_msg_count => x_msg_count
3666 ,x_msg_data => x_msg_data
3667 ,p_offerAdjNewLineId => l_adjNewLines.offer_adj_new_line_id
3668 , x_modifier_line_rec => x_modifier_line_tbl(i)
3669 );
3670 x_modifier_line_tbl(i).start_date_active := null;
3671 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3672 RAISE FND_API.G_EXC_ERROR;
3673 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675 END IF;
3676 populateDisPricingAttributes
3677 (
3678 x_return_status => x_return_status
3679 ,x_msg_count => x_msg_count
3680 ,x_msg_data => x_msg_data
3681 ,p_offerAdjNewLineId => l_adjNewLines.offer_adj_new_line_id
3682 , x_pricing_attr_rec => x_pricing_attr_tbl(i)
3683 , p_index => i
3684 );
3685 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3686 RAISE FND_API.G_EXC_ERROR;
3687 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3689 END IF;
3690 i := i + 1;
3691 END LOOP;
3692 -- populate discounts
3693 -- populate pricing attributes
3694 END populateDisData;
3695
3696
3697
3698 PROCEDURE populatePbhData
3699 (
3700 x_return_status OUT NOCOPY VARCHAR2
3701 ,x_msg_count OUT NOCOPY NUMBER
3702 ,x_msg_data OUT NOCOPY VARCHAR2
3703 ,p_offerAdjNewLineId IN NUMBER
3704 , x_modifier_line_rec OUT NOCOPY qp_modifiers_pub.modifiers_rec_type
3705 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
3706 )
3707 IS
3708 i NUMBER;
3709 BEGIN
3710 x_return_status := FND_API.G_RET_STS_SUCCESS;
3711 x_modifier_line_rec := null;
3712 x_pricing_attr_tbl.delete;
3713 i := 1;
3714 -- populate pbh discounts
3715 -- populate pbh product attributes
3716 populatePbhDiscounts
3717 (
3718 x_return_status => x_return_status
3719 ,x_msg_count => x_msg_count
3720 ,x_msg_data => x_msg_data
3721 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3722 , x_modifier_line_rec => x_modifier_line_rec
3723 );
3724 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3725 RAISE FND_API.G_EXC_ERROR;
3726 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3728 END IF;
3729 populatePbhPricingAttr
3730 (
3731 x_return_status => x_return_status
3732 ,x_msg_count => x_msg_count
3733 ,x_msg_data => x_msg_data
3734 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3735 ,x_pricing_attr_tbl => x_pricing_attr_tbl
3736 , p_index => 1
3737 );
3738 END populatePbhData;
3739
3740 PROCEDURE populateNewMtProducts
3741 (
3742 x_return_status OUT NOCOPY VARCHAR2
3743 ,x_msg_count OUT NOCOPY NUMBER
3744 ,x_msg_data OUT NOCOPY VARCHAR2
3745 ,p_offerAdjNewLineId IN NUMBER
3746 , x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
3747 , x_pricing_attr_tbl OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
3748 )
3749 IS
3750 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
3751 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
3752 BEGIN
3753 x_return_status := FND_API.G_RET_STS_SUCCESS;
3754 x_modifier_line_tbl.delete;
3755 x_pricing_attr_tbl.delete;
3756 l_modifier_line_tbl.delete;
3757 l_pricing_attr_tbl.delete;
3758 -- populate PBH header record
3759 -- populate populate DIS records
3760 populatePbhData
3761 (
3762 x_return_status => x_return_status
3763 ,x_msg_count => x_msg_count
3764 ,x_msg_data => x_msg_data
3765 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3766 , x_modifier_line_rec => x_modifier_line_tbl(1)
3767 , x_pricing_attr_tbl => x_pricing_attr_tbl
3768 );
3769 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3770 RAISE FND_API.G_EXC_ERROR;
3771 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3773 END IF;
3774 populateDisData
3775 (
3776 x_return_status => x_return_status
3777 ,x_msg_count => x_msg_count
3778 ,x_msg_data => x_msg_data
3779 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3780 , x_modifier_line_tbl => l_modifier_line_tbl
3781 , x_pricing_attr_tbl => l_pricing_attr_tbl
3782 );
3783 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3784 RAISE FND_API.G_EXC_ERROR;
3785 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3787 END IF;
3788 OZF_VOLUME_OFFER_ADJ.merge_pricing_attributes
3789 (
3790 px_to_pricing_attr_tbl => x_pricing_attr_tbl
3791 , p_from_pricing_attr_tbl => l_pricing_attr_tbl
3792 );
3793 OZF_VOLUME_OFFER_ADJ.merge_modifiers
3794 (
3795 px_to_modifier_line_tbl => x_modifier_line_tbl
3796 , p_from_modifier_line_tbl => l_modifier_line_tbl
3797 );
3798 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3799 RAISE FND_API.G_EXC_ERROR;
3800 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3801 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3802 END IF;
3803 END populateNewMtProducts;
3804
3805 /**
3806 Creates a multi-tier discount line in QP corresponding to data entered in ozf_offer_adj_new_lines and ozf_offer_adj_new_products
3807 * p_offerAdjNewLineId offer_adj_new_line_id for which the qp data is to be created
3808 * note as of now the price_break_type_code is not captured in the UI or in the database so it is hardcoded to point.
3809 */
3810 PROCEDURE createNewMtProduct
3811 (
3812 x_return_status OUT NOCOPY VARCHAR2
3813 ,x_msg_count OUT NOCOPY NUMBER
3814 ,x_msg_data OUT NOCOPY VARCHAR2
3815 , p_offerAdjNewLineId IN NUMBER
3816 , x_modifier_line_tbl OUT NOCOPY qp_modifiers_pub.modifiers_tbl_type
3817 )
3818 IS
3819 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
3820 l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
3821 V_MODIFIER_LIST_rec QP_MODIFIERS_PUB.Modifier_List_Rec_Type;
3822 V_MODIFIER_LIST_val_rec QP_MODIFIERS_PUB.Modifier_List_Val_Rec_Type;
3823 V_MODIFIERS_tbl QP_MODIFIERS_PUB.Modifiers_Tbl_Type;
3824 V_MODIFIERS_val_tbl QP_MODIFIERS_PUB.Modifiers_Val_Tbl_Type;
3825 V_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
3826 V_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
3827 V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
3828 V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
3829 BEGIN
3830 -- initialize
3831 -- populate records
3832 -- process data
3833 -- create qp data
3834 -- return created table
3835 x_return_status := FND_API.G_RET_STS_SUCCESS;
3836 l_modifier_line_tbl.delete;
3837 l_pricing_attr_tbl.delete;
3838
3839 populateNewMtProducts
3840 (
3841 x_return_status => x_return_status
3842 ,x_msg_count => x_msg_count
3843 ,x_msg_data => x_msg_data
3844 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3845 , x_modifier_line_tbl => l_modifier_line_tbl
3846 , x_pricing_attr_tbl => l_pricing_attr_tbl
3847 );
3848
3849 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3850 RAISE FND_API.G_EXC_ERROR;
3851 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3853 END IF;
3854 QP_Modifiers_PUB.process_modifiers(
3855 p_api_version_number => 1.0,
3856 p_init_msg_list => FND_API.G_FALSE,
3857 p_return_values => FND_API.G_FALSE,
3858 x_return_status => x_return_status,
3859 x_msg_count => x_msg_count,
3860 x_msg_data => x_msg_data,
3861 p_modifiers_tbl => l_modifier_line_tbl,
3862 p_pricing_attr_tbl => l_pricing_attr_tbl,
3863 x_modifier_list_rec => v_modifier_list_rec,
3864 x_modifier_list_val_rec => v_modifier_list_val_rec,
3865 x_modifiers_tbl => v_modifiers_tbl,
3866 x_modifiers_val_tbl => v_modifiers_val_tbl,
3867 x_qualifiers_tbl => v_qualifiers_tbl,
3868 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
3869 x_pricing_attr_tbl => v_pricing_attr_tbl,
3870 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
3871 );
3872 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3873 RAISE FND_API.G_EXC_ERROR;
3874 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3876 END IF;
3877 x_modifier_line_tbl := v_modifiers_tbl;
3878 EXCEPTION
3879 WHEN OTHERS THEN
3880 FND_MSG_PUB.count_and_get(
3881 p_encoded => FND_API.g_false
3882 , p_count => x_msg_count
3883 , p_data => x_msg_data
3884 );
3885 x_return_status := FND_API.G_RET_STS_ERROR;
3886 END createNewMtProduct;
3887
3888 /**
3889 Processes a Multi-tier offer_adjustment_line
3890 */
3891 PROCEDURE processNewMtProduct
3892 (
3893 x_return_status OUT NOCOPY VARCHAR2
3894 ,x_msg_count OUT NOCOPY NUMBER
3895 ,x_msg_data OUT NOCOPY VARCHAR2
3896 ,p_offerAdjNewLineId IN NUMBER
3897 )
3898 IS
3899 l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
3900 BEGIN
3901 -- initialize
3902 -- create a product qp data
3903 -- back create ozf_offer_adjustment_lines
3904 x_return_status := FND_API.G_RET_STS_SUCCESS;
3905 l_modifier_line_tbl.delete;
3906
3907 createNewMtProduct
3908 (
3909 x_return_status => x_return_status
3910 ,x_msg_count => x_msg_count
3911 ,x_msg_data => x_msg_data
3912 ,p_offerAdjNewLineId => p_offerAdjNewLineId
3913 , x_modifier_line_tbl => l_modifier_line_tbl
3914 );
3915 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3916 RAISE FND_API.G_EXC_ERROR;
3917 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3919 END IF;
3920 createAdjLines
3921 (
3922 x_return_status => x_return_status
3923 ,x_msg_count => x_msg_count
3924 ,x_msg_data => x_msg_data
3925 ,p_modifiers_tbl => l_modifier_line_tbl
3926 ,p_offerAdjustmentId => getAdjustmentId( p_offerAdjNewLineId => p_offerAdjNewLineId)
3927 );
3928 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3929 RAISE FND_API.G_EXC_ERROR;
3930 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3932 END IF;
3933 END processNewMtProduct;
3934
3935
3936 PROCEDURE processStRegProducts
3937 (
3938 x_return_status OUT NOCOPY VARCHAR2
3939 ,x_msg_count OUT NOCOPY NUMBER
3940 ,x_msg_data OUT NOCOPY VARCHAR2
3941 ,p_offerAdjustmentId IN NUMBER
3942 )
3943 IS
3944 CURSOR c_adjustmentProducts(cp_offerAdjustmentId NUMBER) IS
3945 SELECT offer_adj_new_line_id
3946 FROM ozf_offer_adj_new_lines
3947 WHERE tier_type = 'DIS'
3948 AND parent_adj_line_id IS NULL
3949 AND offer_adjustment_id = cp_offerAdjustmentId;
3950 BEGIN
3951 x_return_status := FND_API.G_RET_STS_SUCCESS;
3952 FOR l_adjustmentProducts IN c_adjustmentProducts(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
3953 processNewStProduct
3954 (
3955 x_return_status => x_return_status
3956 ,x_msg_count => x_msg_count
3957 ,x_msg_data => x_msg_data
3958 ,p_offerAdjNewLineId => l_adjustmentProducts.offer_adj_new_line_id
3959 );
3960 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3961 RAISE FND_API.G_EXC_ERROR;
3962 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3964 END IF;
3965 END LOOP;
3966 END processStRegProducts;
3967
3968 PROCEDURE processMtRegProducts
3969 (
3970 x_return_status OUT NOCOPY VARCHAR2
3971 ,x_msg_count OUT NOCOPY NUMBER
3972 ,x_msg_data OUT NOCOPY VARCHAR2
3973 ,p_offerAdjustmentId IN NUMBER
3974 )
3975 IS
3976 CURSOR c_adjustmentProducts(cp_offerAdjustmentId NUMBER)
3977 IS
3978 SELECT offer_adj_new_line_id
3979 FROM ozf_offer_adj_new_lines
3980 WHERE offer_adjustment_id = cp_offerAdjustmentId
3981 AND parent_adj_line_id IS NULL
3982 AND tier_type = 'PBH';
3983
3984 BEGIN
3985 x_return_status := FND_API.G_RET_STS_SUCCESS;
3986 FOR l_adjustmentProducts IN c_adjustmentProducts(cp_offerAdjustmentId => p_offerAdjustmentId) LOOP
3987 processNewMtProduct
3988 (
3989 x_return_status => x_return_status
3990 ,x_msg_count => x_msg_count
3991 ,x_msg_data => x_msg_data
3992 ,p_offerAdjNewLineId => l_adjustmentProducts.offer_adj_new_line_id
3993 );
3994 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3995 RAISE FND_API.G_EXC_ERROR;
3996 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3998 END IF;
3999 END LOOP;
4000 END processMtRegProducts;
4001
4002 PROCEDURE processNewRegProducts
4003 (
4004 x_return_status OUT NOCOPY VARCHAR2
4005 ,x_msg_count OUT NOCOPY NUMBER
4006 ,x_msg_data OUT NOCOPY VARCHAR2
4007 ,p_offerAdjustmentId IN NUMBER
4008 )
4009 IS
4010 BEGIN
4011 -- initialize
4012 -- process single tier lines
4013 -- process multi-tier lines
4014 x_return_status := FND_API.G_RET_STS_SUCCESS;
4015 processStRegProducts
4016 (
4017 x_return_status => x_return_status
4018 ,x_msg_count => x_msg_count
4019 ,x_msg_data => x_msg_data
4020 ,p_offerAdjustmentId => p_offerAdjustmentId
4021 );
4022 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4023 RAISE FND_API.G_EXC_ERROR;
4024 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4026 END IF;
4027 processMtRegProducts
4028 (
4029 x_return_status => x_return_status
4030 ,x_msg_count => x_msg_count
4031 ,x_msg_data => x_msg_data
4032 ,p_offerAdjustmentId => p_offerAdjustmentId
4033 );
4034 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4035 RAISE FND_API.G_EXC_ERROR;
4036 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4038 END IF;
4039 END processNewRegProducts;
4040
4041 PROCEDURE process_new_products
4042 (
4043 x_return_status OUT NOCOPY VARCHAR2
4044 ,x_msg_count OUT NOCOPY NUMBER
4045 ,x_msg_data OUT NOCOPY VARCHAR2
4046 ,p_offerAdjustmentId IN NUMBER
4047 )
4048 IS
4049 CURSOR c_offerType(cp_offerAdjustmentId NUMBER) IS
4050 SELECT
4051 offer_type
4052 FROM ozf_offers a, ozf_offer_adjustments_b b
4053 WHERE a.qp_list_header_id = b.list_header_id
4054 AND b.offer_adjustment_id = cp_offerAdjustmentId;
4055 l_offerType c_offerType%ROWTYPE;
4056 BEGIN
4057 x_return_status := FND_API.G_RET_STS_SUCCESS;
4058 OPEN c_offerType(cp_offerAdjustmentId => p_offerAdjustmentId);
4059 FETCH c_offerType INTO l_offerType;
4060 CLOSE c_offerType;
4061 IF l_offerType.offer_type = 'DEAL' THEN
4062 NULL; -- NO NEW PRODUCTS CURRENTLY SUPPORTED FOR TRADE DEAL OFFERS
4063 ELSIF l_offerType.offer_type = 'VOLUME_OFFER' THEN
4064 OZF_VOLUME_OFFER_ADJ.adjust_new_products
4065 (
4066 x_return_status => x_return_status
4067 ,x_msg_count => x_msg_count
4068 ,x_msg_data => x_msg_data
4069 ,p_offerAdjustmentId => p_offerAdjustmentId
4070 );
4071 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4072 RAISE FND_API.G_EXC_ERROR;
4073 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4075 END IF;
4076 ELSIF l_offerType.offer_type = 'OID' THEN
4077 processNewPgProducts
4078 (
4079 x_return_status => x_return_status
4080 , x_msg_count => x_msg_count
4081 , x_msg_data => x_msg_data
4082 , p_offerAdjustmentId => p_offerAdjustmentId
4083 );
4084 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4085 RAISE FND_API.G_EXC_ERROR;
4086 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4088 END IF;
4089 ELSIF l_offerType.offer_type IN ( 'ORDER','ACCRUAL','OFF_INVOICE') THEN
4090 processNewRegProducts
4091 (
4092 x_return_status => x_return_status
4093 , x_msg_count => x_msg_count
4094 , x_msg_data => x_msg_data
4095 , p_offerAdjustmentId => p_offerAdjustmentId
4096 );
4097 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4098 RAISE FND_API.G_EXC_ERROR;
4099 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4101 END IF;
4102 END IF;
4103 END process_new_products;
4104
4105 PROCEDURE updateHeaderDate
4106 (
4107 x_return_status OUT NOCOPY VARCHAR2
4108 ,x_msg_count OUT NOCOPY NUMBER
4109 ,x_msg_data OUT NOCOPY VARCHAR2
4110 ,p_offerAdjustmentId IN NUMBER
4111 )
4112 IS
4113 CURSOR c_dates(cp_offerAdjustmentId NUMBER) IS
4114 SELECT a.effective_date , b.start_date_active , a.list_header_id , c.offer_type , c.object_version_number
4115 FROM ozf_offer_adjustments_b a, qp_list_headers_b b , ozf_offers c
4116 WHERE a.list_header_id = b.list_header_id
4117 AND b.list_header_id = c.qp_list_header_id
4118 AND a.offer_adjustment_id = cp_offerAdjustmentId ;
4119
4120 l_offerType VARCHAR2(30);
4121 l_qpListHeaderId NUMBER;
4122 l_errorLocation NUMBER;
4123 l_modifier_rec ozf_offer_pvt.Modifier_LIST_Rec_Type;
4124 l_modifier_line_tbl ozf_offer_pvt.MODIFIER_LINE_TBL_TYPE;
4125 BEGIN
4126 x_return_status := FND_API.G_RET_STS_SUCCESS;
4127 FOR l_dates in c_dates(p_offerAdjustmentId) LOOP
4128
4129 IF l_dates.start_date_active IS NOT NULL AND l_dates.start_date_active <> FND_API.G_MISS_DATE THEN
4130 -- if effective date is before start_date of the offer update the start_date of the offer
4131 IF l_dates.effective_date < l_dates.start_date_active THEN
4132 l_modifier_rec.start_date_active := l_dates.effective_date;
4133 END IF;
4134 END IF;
4135 l_modifier_rec.qp_list_header_id := l_dates.list_header_id;
4136 l_modifier_rec.OFFER_OPERATION := Qp_Globals.G_OPR_UPDATE;
4137 l_modifier_rec.MODIFIER_OPERATION := Qp_Globals.G_OPR_UPDATE;
4138 l_modifier_rec.object_version_number := l_dates.object_version_number;
4139 l_offerType := l_dates.offer_type;
4140 OZF_OFFER_PVT.process_modifiers
4141 (
4142 p_init_msg_list => FND_API.g_false
4143 ,p_api_version => 1.0
4144 ,p_commit => FND_API.g_false
4145 ,x_return_status => x_return_status
4146 ,x_msg_count => x_msg_count
4147 ,x_msg_data => x_msg_data
4148 ,p_offer_type => l_offerType
4149 ,p_modifier_list_rec => l_modifier_rec
4150 ,p_modifier_line_tbl => l_modifier_line_tbl
4151 ,x_qp_list_header_id => l_qpListHeaderId
4152 ,x_error_location => l_errorLocation
4153 );
4154 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4155 RAISE FND_API.G_EXC_ERROR;
4156 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4158 END IF;
4159 END LOOP;
4160
4161 END updateHeaderDate;
4162 /**
4163 Processes an Adjustment.
4164 For a given adjustment.
4165 End dates old discounts and creates corresponding new discounts
4166 Create new disocunts for new products added thru. Adjustments
4167 Maps the old list_line_id to the new list_line_id
4168 */
4169 PROCEDURE process_adjustment
4170 (
4171 p_init_msg_list IN VARCHAR2 := FND_API.g_false
4172 ,p_api_version IN NUMBER
4173 ,p_commit IN VARCHAR2 := FND_API.g_false
4174 ,x_return_status OUT NOCOPY VARCHAR2
4175 ,x_msg_count OUT NOCOPY NUMBER
4176 ,x_msg_data OUT NOCOPY VARCHAR2
4177 ,p_offerAdjustmentId IN NUMBER
4178 )
4179 IS
4180 BEGIN
4181 -- initialize
4182 -- update header date
4183 -- process old discounts
4184 -- process new products
4185 x_return_status := FND_API.G_RET_STS_SUCCESS;
4186 updateHeaderDate
4187 (
4188 x_return_status => x_return_status
4189 ,x_msg_count => x_msg_count
4190 ,x_msg_data => x_msg_data
4191 ,p_offerAdjustmentId => p_offerAdjustmentId
4192 );
4193 ozf_utility_pvt.debug_message('GR Updated header date:'||x_return_status);
4194 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4195 RAISE FND_API.G_EXC_ERROR;
4196 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4198 END IF;
4199 process_old_discounts
4200 (
4201 x_return_status => x_return_status
4202 ,x_msg_count => x_msg_count
4203 ,x_msg_data => x_msg_data
4204 ,p_offerAdjustmentId => p_offerAdjustmentId
4205 );
4206 ozf_utility_pvt.debug_message('after process old discounts :'||x_return_status);
4207 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4208 RAISE FND_API.G_EXC_ERROR;
4209 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4211 END IF;
4212 process_new_products
4213 (
4214 x_return_status => x_return_status
4215 ,x_msg_count => x_msg_count
4216 ,x_msg_data => x_msg_data
4217 ,p_offerAdjustmentId => p_offerAdjustmentId
4218 );
4219 ozf_utility_pvt.debug_message('process new products :'||x_return_status);
4220 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4221 RAISE FND_API.G_EXC_ERROR;
4222 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4224 END IF;
4225 END process_adjustment;
4226
4227
4228 END OZF_OFFER_ADJ_PVT;