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