[Home] [Help]
PACKAGE BODY: APPS.QP_COUPON_PVT
Source
1 PACKAGE BODY QP_COUPON_PVT AS
2 /* $Header: QPXVCPNB.pls 120.4.12010000.2 2009/12/24 08:20:08 dnema ship $ */
3
4 -- Global constant holding the package name
5
6 QP_COUPON_NOT_FOUND EXCEPTION;
7 QP_COUPON_MODIFIER_NOT_FOUND EXCEPTION;
8 QP_COUPON_QUALIFIER_NOT_FOUND EXCEPTION;
9 l_debug varchar2(3);
10
11
12 -----------------------------------------------------------------------
13 --Changes by spgopal
14 --Fix for bug 1755567
15 -----------------------------------------------------------------------
16
17 PROCEDURE Get_denormalized_qual_cols(p_list_header_id IN NUMBER,
18 p_list_line_id IN NUMBER,
19 x_active_flag OUT NOCOPY VARCHAR2,
20 x_list_type_code OUT NOCOPY VARCHAR2,
21 x_header_qual_exists_flag OUT NOCOPY VARCHAR2,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_return_text OUT NOCOPY VARCHAR2) IS
24
25 /*
26 INDX,QP_COUPON_PVT.Get_denormalized_qual_cols.l_qual_exists_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
27 INDX,QP_COUPON_PVT.Get_denormalized_qual_cols.l_qual_exists_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
28 */
29 CURSOR l_qual_exists_cur(p_header_id NUMBER) IS
30 select 'X' qual_exists
31 from qp_qualifiers
32 where list_header_id=p_header_id
33 and list_line_id= -1;
34
35 /*
36 INDX,QP_COUPON_PVT.Get_denormalized_qual_cols.l_list_hdr_dtls_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
37 */
38 CURSOR l_list_hdr_dtls_cur(p_header_id NUMBER) IS
39 select active_flag
40 , list_type_code
41 from qp_list_headers_b
42 where list_header_id=p_header_id;
43
44
45
46 l_qual_exists VARCHAR2(1) := FND_API.G_MISS_CHAR;
47
48 BEGIN
49 OPEN l_qual_exists_cur(p_list_header_id);
50 FETCH l_qual_exists_cur INTO l_qual_exists;
51 CLOSE l_qual_exists_cur;
52
53 IF l_qual_exists = 'X' THEN
54 x_header_qual_exists_flag := 'Y';
55 ELSE
56 x_header_qual_exists_flag := 'N';
57 END IF;
58
59 OPEN l_list_hdr_dtls_cur(p_list_header_id);
60 FETCH l_list_hdr_dtls_cur INTO x_active_flag, x_list_type_code;
61 CLOSE l_list_hdr_dtls_cur;
62
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 x_return_text := 'QP_COUPON_PVT.GET_DENORMALIZED_COLS SUCCESS';
65
66
67 EXCEPTION
68 When OTHERS THEN
69 x_return_status := FND_API.G_RET_STS_ERROR;
70 x_return_text := 'QP_COUPON_PVT.GET_DENORMALIZED_COLS: '||SQLERRM;
71
72
73 END Get_denormalized_qual_cols;
74
75
76 PROCEDURE update_qual_ind(p_list_header_id IN NUMBER,
77 p_list_line_id IN NUMBER,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_return_text OUT NOCOPY VARCHAR2) IS
80
81 /*
82 INDX,QP_COUPON_PVT.update_qual_ind.l_line_qual_exists_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
83 INDX,QP_COUPON_PVT.update_qual_ind.l_line_qual_exists_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
84 */
85 CURSOR l_line_qual_exists_cur(p_line_id NUMBER
86 ,p_header_id NUMBER) IS
87 select 'Y'
88 from qp_qualifiers q
89 where q.list_header_id=p_header_id
90 and q.list_line_id=p_line_id;
91
92
93 l_qual_ind NUMBER := FND_API.G_MISS_NUM;
94 l_line_qual_exists VARCHAR2(1) := QP_PREQ_GRP.G_NO;
95 BEGIN
96
97
98 OPEN l_line_qual_exists_cur(p_list_line_id, p_list_header_id);
99 FETCH l_line_qual_exists_cur INTO l_line_qual_exists;
100 CLOSE l_line_qual_exists_cur;
101
102 IF l_line_qual_exists <> 'Y' THEN
103
104 /*
105 INDX,QP_COUPON_PVT.update_qual_ind.update_qual_ind_upd1,QP_LIST_LINES_PK,LIST_LINE_ID,1
106 */
107 update qp_list_lines qpl set
108 qpl.qualification_ind =
109 nvl(qpl.qualification_ind,0)+8
110 where qpl.list_line_id=p_list_line_id
111 returning qpl.qualification_ind into l_qual_ind;
112
113 /*
114 INDX,QP_COUPON_PVT.update_qual_ind.update_qual_ind_upd2,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
115 */
116 update qp_pricing_attributes pra
117 set pra.qualification_ind = l_qual_ind
118 where pra.list_line_id = p_list_line_id;
119 END IF;
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 x_return_status := FND_API.G_RET_STS_ERROR;
125 x_return_text := 'QP_COUPON_PVT.update_qual_ind : '||SQLERRM;
126
127 END update_qual_ind;
128
129 -----------------------------------------------------------------------
130
131
132
133
134 -- Procedure Insert_Coupon creates a record in QP_COUPONS table
135 PROCEDURE Insert_Coupon(
136 p_issued_by_modifier_id IN NUMBER
137 , p_expiration_period_start_date IN DATE := NULL
138 , p_expiration_date IN DATE := NULL
139 , p_number_expiration_periods IN NUMBER := NULL
140 , p_expiration_period_uom_code IN VARCHAR2
141 , p_user_def_coupon_number IN VARCHAR2
142 , p_pricing_effective_date IN DATE
143 , x_coupon_id OUT NOCOPY NUMBER
144 , x_coupon_number OUT NOCOPY VARCHAR2
145 , x_return_status OUT NOCOPY VARCHAR2
146 , x_return_status_txt OUT NOCOPY VARCHAR2
147 ) IS
148 l_generated_coupon_number NUMBER;
149 l_expiration_period_start_date DATE := p_expiration_period_start_date;
150 l_expiration_period_end_date DATE := p_expiration_date;
151 l_return_status VARCHAR2(1);
152
153 BEGIN
154
155 l_debug :=QP_PREQ_GRP.G_DEBUG_ENGINE;
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158 IF l_debug = FND_API.G_TRUE THEN
159 QP_PREQ_GRP.engine_debug ('Entering QP_COUPON_PVT.Insertr_Coupon ...');
160 QP_PREQ_GRP.engine_debug ('p_issued_by_modifier_id: '||p_issued_by_modifier_id);
161 END IF; -- END IF l_debug
162
163 --DBMS_OUTPUT.PUT_LINE('Inside insert_coupon');
164 /* Coupon's effective dates:
165 * IF p_expiration_period_start_date is NULL, start date don't show on the coupon
166 * Expiration date must be speicified or calculated
167 * Per bug 1263673, We change the behavior to allow null expiration date
168 * so that user has more control
169 IF (p_expiration_date IS NULL AND
170 (p_number_expiration_periods IS NULL OR
171 p_expiration_period_uom_code IS NULL)) THEN
172
173 FND_MESSAGE.SET_NAME('QP', 'QP_EXPIRATION_DATE_NOT_SET');
174
175 x_return_status_txt := FND_MESSAGE.GET;
176
177 RAISE FND_API.G_EXC_ERROR;
178
179 END IF;
180 */
181
182 --DBMS_OUTPUT.PUT_LINE('before set expiration dates');
183 QP_COUPON_PVT.Set_Expiration_Dates(
184 l_expiration_period_start_date
185 , l_expiration_period_end_date
186 , p_number_expiration_periods
187 , p_expiration_period_uom_code
188 , p_pricing_effective_date
189 , l_return_status
190 , x_return_status_txt
191 );
192
193 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
194 RAISE FND_API.G_EXC_ERROR;
195 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
197 END IF;
198
199 SELECT QP_GENERATED_COUPON_NO_S.nextval,
200 QP_COUPONS_S.nextval
201 INTO l_generated_coupon_number,
202 x_coupon_id
203 FROM dual;
204
205 IF l_debug = FND_API.G_TRUE THEN
206 QP_PREQ_GRP.engine_debug ('coupon_id going to QP_COUPONS table (QP_COUPONS_S.nextval): '||x_coupon_id);
207 QP_PREQ_GRP.engine_debug ('l_generated_coupon_number(QP_GENERATED_COUPON_NO_S.nextval): '||l_generated_coupon_number);
208 QP_PREQ_GRP.engine_debug ('p_user_def_coupon_number: '||p_user_def_coupon_number);
209 END IF; -- END IF l_debug
210
211 x_coupon_number := p_user_def_coupon_number||l_generated_coupon_number;
212
213 IF l_debug = FND_API.G_TRUE THEN
214 QP_PREQ_GRP.engine_debug ('coupon_number going to QP_COUPONS table: '||x_coupon_number);
215 END IF; -- END IF l_debug
216
217 INSERT INTO QP_COUPONS(
218 COUPON_ID,
219 CREATION_DATE,
220 CREATED_BY,
221 LAST_UPDATE_DATE,
222 LAST_UPDATED_BY,
223 LAST_UPDATE_LOGIN,
224 COUPON_NUMBER,
225 USER_DEF_COUPON_NUMBER,
226 GENERATED_COUPON_NUMBER,
227 ISSUED_BY_MODIFIER_ID,
228 EXPIRATION_DATE,
229 START_DATE,
230 REDEEMED_FLAG,
231 ISSUED_DATE
232 )
233 VALUES (x_coupon_id,
234 sysdate,
235 fnd_global.user_id,
236 sysdate,
237 fnd_global.user_id,
238 fnd_global.login_id,
239 x_coupon_number,
240 p_user_def_coupon_number,
241 l_generated_coupon_number,
242 p_issued_by_modifier_id,
243 l_expiration_period_end_date,
244 l_expiration_period_start_date,
245 'N',
246 nvl(p_pricing_effective_date,sysdate)
247 );
248
249 EXCEPTION
250
251 WHEN FND_API.G_EXC_ERROR THEN
252
253 x_return_status := FND_API.G_RET_STS_ERROR;
254
255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
256
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258
259 WHEN OTHERS THEN
260
261 x_return_status_txt := 'QP_COUPON_PVT.Insert_Coupon: '||SQLERRM;
262
263 x_return_status := FND_API.G_RET_STS_ERROR;
264
265 END Insert_Coupon;
266
267 -- Procedure Create_Coupon_Qualifier creates a record in QP_QUALIFIERS table to say that
268 -- if your order quotes this coupon number, you can use the benefits in the coupon
269 PROCEDURE Create_Coupon_Qualifier(
270 p_list_line_id IN NUMBER
271 , p_coupon_id IN NUMBER
272 , x_return_status OUT NOCOPY VARCHAR2
273 , x_return_status_txt OUT NOCOPY VARCHAR2
274 ) IS
275 l_list_header_id NUMBER;
276 l_list_line_id NUMBER;
277 l_qualification_ind NUMBER;
278 l_qual_attr_value_from_number NUMBER := FND_API.G_MISS_NUM;
279 l_qual_attr_value_to_number NUMBER := FND_API.G_MISS_NUM;
280 l_list_type_code QP_LIST_HEADERS_B.LIST_TYPE_CODE%TYPE;
281 l_active_flag VARCHAR2(1);
282 l_header_qual_exists_flag VARCHAR2(1);
283 l_return_status VARCHAR2(1);
284 l_return_text VARCHAR2(100);
285
286 COUP_DENORMALIZED_COL_EXP EXCEPTION;
287 BEGIN
288
289 l_debug :=QP_PREQ_GRP.G_DEBUG_ENGINE;
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 IF l_debug = FND_API.G_TRUE THEN
293 QP_PREQ_GRP.engine_debug ('Entering QP_COUPON_PVT.Create_Coupon_Qualifier ...');
294 QP_PREQ_GRP.engine_debug ('p_list_line_id: '||p_list_line_id);
295 QP_PREQ_GRP.engine_debug ('p_coupon_id: '||p_coupon_id);
296 END IF; -- END IF l_debug
297
298
299 BEGIN
300 /*
301 INDX,QP_COUPON_PVT.Create_Coupon_Qualifier.Create_Coupon_Qualifier_sel1,QP_RLTD_MODIFIERS_N1,FROM_RLTD_MODIFIER_ID,1
302 INDX,QP_COUPON_PVT.Create_Coupon_Qualifier.Create_Coupon_Qualifier_sel1,QP_RLTD_MODIFIERS_N1,RLTD_MODIFIER_GRP_TYPE,2
303
304 INDX,QP_COUPON_PVT.Create_Coupon_Qualifier.Create_Coupon_Qualifier_sel1,QP_LIST_LINES_PK,LIST_LINE_ID,1
305 */
306 SELECT m.to_rltd_modifier_id list_line_id,
307 l.list_header_id,
308 l.qualification_ind
309 INTO l_list_line_id, l_list_header_id,l_qualification_ind
310 FROM qp_rltd_modifiers m, qp_list_lines l
311 WHERE from_rltd_modifier_id = p_list_line_id
312 AND m.to_rltd_modifier_id = l.list_line_id
313 AND m.rltd_modifier_grp_type = QP_COUPON_PVT.G_COUPON_GRP_TYPE;
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 RAISE QP_COUPON_MODIFIER_NOT_FOUND;
317 END;
318
319 IF l_debug = FND_API.G_TRUE THEN
320 QP_PREQ_GRP.engine_debug ('before calling Get_denormalized_qual_cols ...');
321 END IF; -- END IF l_debug
322
323 Get_denormalized_qual_cols(l_list_header_id,
324 l_list_line_id,
325 l_active_flag,
326 l_list_type_code,
327 l_header_qual_exists_flag,
328 l_return_status,
329 l_return_text);
330
331 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
332 RAISE COUP_DENORMALIZED_COL_EXP;
333 END IF;
334
335 l_qual_attr_value_from_number :=
336 qp_number.canonical_to_number(p_coupon_id);
337
338 l_qual_attr_value_to_number :=
339 qp_number.canonical_to_number(NULL);
340
341
342 /* Create a qualifier: "Coupon" = Coupon Number,context = 'Order'? */
343 INSERT INTO QP_QUALIFIERS (
344 QUALIFIER_ID,
345 CREATION_DATE,
346 CREATED_BY,
347 LAST_UPDATE_DATE,
348 LAST_UPDATED_BY,
349 LAST_UPDATE_LOGIN,
350 LIST_HEADER_ID,
351 LIST_LINE_ID,
352 COMPARISON_OPERATOR_CODE,
353 QUALIFIER_CONTEXT,
354 QUALIFIER_ATTRIBUTE,
355 QUALIFIER_ATTR_VALUE,
356 QUALIFIER_ATTR_VALUE_TO,
357 QUALIFIER_GROUPING_NO,
358 EXCLUDER_FLAG,
359 --changes made for bug 1755567
360 --included denormalised columns
361 DISTINCT_ROW_COUNT,
362 SEARCH_IND,
363 HEADER_QUALS_EXIST_FLAG,
364 QUALIFIER_GROUP_CNT,
365 ACTIVE_FLAG,
366 LIST_TYPE_CODE,
367 QUAL_ATTR_VALUE_FROM_NUMBER,
368 QUAL_ATTR_VALUE_TO_NUMBER,
369 OTHERS_GROUP_CNT
370 --ENH Upgrade BOAPI for orig_sys...ref RAVI
371 ,ORIG_SYS_QUALIFIER_REF
372 ,ORIG_SYS_LINE_REF
373 ,ORIG_SYS_HEADER_REF
374 )
375 VALUES (
376 QP_QUALIFIERS_S.nextval,
377 sysdate,
378 fnd_global.user_id,
379 sysdate,
380 fnd_global.user_id,
381 fnd_global.login_id,
382 l_list_header_id,
383 l_list_line_id,
384 '=',
385 QP_PREQ_GRP.G_LIST_HEADER_CONTEXT,
386 QP_COUPON_PVT.G_COUPON_QUALIFIER,
387 to_char(p_coupon_id),
388 NULL,
389 qp_qualifier_group_no_s.nextval,
390 'N',
391 1,
392 1,
393 l_header_qual_exists_flag,
394 (select count(*) from qp_qualifiers where list_line_id = l_list_line_id and qualifier_grouping_no = -1)+1, --[julin/5416713] accounting for -1 qualifiers
395 l_active_flag,
396 l_list_type_code,
397 l_qual_attr_value_from_number,
398 l_qual_attr_value_to_number,
399 1
400 --ENH Upgrade BOAPI for orig_sys...ref RAVI
401 ,to_char(QP_QUALIFIERS_S.currval)
402 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_list_line_id)
403 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_list_header_id)
404 );
405
406 update_qual_ind(l_list_header_id,
407 l_list_line_id,
408 l_return_status,
409 l_return_text);
410
411 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
412 RAISE COUP_DENORMALIZED_COL_EXP;
413 END IF;
414
415 /* In the rare case that there was no qualifier for the coupon benefit line,
416 update qualification_ind because no there is a qualifier
417 If set up form correctly creates dummy qualifier, this should never happen */
418 -- Not needed ... Also the old qualification indicators are no more applicable #1545351
419 /* IF (l_qualification_ind = QP_PREQ_GRP.G_NO_QUAL_IND
420 OR l_qualification_ind = QP_PREQ_GRP.G_NO_RLTD_QUAL_IND
421 OR l_qualification_ind = QP_PREQ_GRP.G_NO_QUAL_PRIC_IND
422 OR l_qualification_ind = QP_PREQ_GRP.G_BLIND_DISCOUNT_IND) THEN
423
424 l_qualification_ind := l_qualification_ind-QP_PREQ_GRP.G_NO_QUAL_IND;
425
426 update qp_list_lines set qualification_ind
427 =decode(l_qualification_ind, 0, NULL, l_qualification_ind)
428 where list_line_id = l_list_line_id;
429
430 END IF;*/
431
432 EXCEPTION
433
434 WHEN COUP_DENORMALIZED_COL_EXP THEN
435 x_return_status_txt := 'QP_COUPON_PVT.Create_Coupon_Qualifier: '||l_return_text;
436
437 x_return_status := FND_API.G_RET_STS_ERROR;
438
439 WHEN QP_COUPON_MODIFIER_NOT_FOUND THEN
440
441 fnd_message.set_name('QP', 'QP_COUPON_MODIFIER_NOT_FOUND');
442 fnd_message.set_token('ID_COLUMN', 'LIST_LINE_ID');
443 fnd_message.set_token('LIST_LINE_ID', p_list_line_id);
444 x_return_status_txt := fnd_message.get;
445
446 x_return_status := FND_API.G_RET_STS_ERROR;
447
448 WHEN OTHERS THEN
449
450 x_return_status_txt := 'QP_COUPON_PVT.Create_Coupon_Qualifier: '||SQLERRM;
451
452 x_return_status := FND_API.G_RET_STS_ERROR;
453
454 END Create_Coupon_Qualifier;
455
456 PROCEDURE Mark_Coupon_Redeemed(
457 p_coupon_number IN VARCHAR2
458 , p_pricing_effective_date IN DATE
459 , x_return_status OUT NOCOPY VARCHAR2
460 , x_return_status_txt OUT NOCOPY VARCHAR2
461 )
462 IS
463 BEGIN
464
465 x_return_status := FND_API.G_RET_STS_SUCCESS;
466
467 --dbms_output.put_line('passed in coupon number '||p_coupon_number);
468
469 /*
470 INDX,QP_COUPON_PVT.Mark_Coupon_Redeemed.Mark_Coupon_Redeemed_upd1,-No Index Used-,NA,NA
471 */
472 UPDATE QP_COUPONS
473 SET redeemed_flag='Y'
474 WHERE coupon_number = p_coupon_number;
475
476 IF SQL%NOTFOUND THEN
477 RAISE QP_COUPON_NOT_FOUND;
478 END IF;
479
480 -- Make Qualifier for the coupon Inactive
481 /*
482 INDX,QP_COUPON_PVT.Mark_Coupon_Redeemed.Mark_Coupon_Redeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
483 INDX,QP_COUPON_PVT.Mark_Coupon_Redeemed.Mark_Coupon_Redeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
484 INDX,QP_COUPON_PVT.Mark_Coupon_Redeemed.Mark_Coupon_Redeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
485 INDX,QP_COUPON_PVT.Mark_Coupon_Redeemed.Mark_Coupon_Redeemed_upd2,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
486 */
487
488 /*
489 INDX,QP_COUPON_PVT.Mark_Coupon_Redeemed.Mark_Coupon_Redeemed_sel1,QP_COUPONS_PK,COUPON_ID,1
490 */
491
492 -- Bug 9210291 - Replacing query to improve performance
493 /* UPDATE qp_qualifiers
494 SET end_date_active = p_pricing_effective_date
495 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
496 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
497 AND nvl(comparison_operator_code,'=') = '='
498 AND qualifier_attr_Value in
499 (select coupon_id from qp_coupons where coupon_number=p_Coupon_number);*/
500
501 UPDATE /*+ INDEX(qpq QP_QUALIFIERS_N4) */ qp_qualifiers qpq
502 SET qpq.end_date_active = p_pricing_effective_date
503 WHERE qpq.qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
504 AND qpq.qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
505 AND nvl(qpq.comparison_operator_code,'=') = '='
506 AND qpq.qualifier_attr_Value in
507 (select TO_CHAR(coupon_id) from qp_coupons where coupon_number=p_Coupon_number);
508
509 IF SQL%NOTFOUND THEN
510 RAISE QP_COUPON_QUALIFIER_NOT_FOUND;
511 END IF;
512
513 EXCEPTION
514
515 WHEN QP_COUPON_NOT_FOUND THEN
516
517 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_NUMBER');
518 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
519 x_return_status_txt := FND_MESSAGE.get;
520 x_return_status := FND_API.G_RET_STS_ERROR;
521
522 WHEN QP_COUPON_QUALIFIER_NOT_FOUND THEN
523
524 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_QUALIFIER');
525 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
526 x_return_status_txt := FND_MESSAGE.get;
527 x_return_status := FND_API.G_RET_STS_ERROR;
528
529 WHEN OTHERS THEN
530
531 x_return_status_txt := 'QP_COUPON_PVT.Mark_Coupon_Redeemed: '||SQLERRM;
532
533 x_return_status := FND_API.G_RET_STS_ERROR;
534
535 END Mark_Coupon_Redeemed;
536
537 -- This is going to be obsolete, should use the overloaded version
538 -- with argument p_coupon_number
539 PROCEDURE Mark_Coupon_Unredeemed(
540 p_coupon_id IN NUMBER
541 , x_return_status OUT NOCOPY VARCHAR2
542 , x_return_status_txt OUT NOCOPY VARCHAR2
543 ) IS
544 BEGIN
545
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 --dbms_output.put_line('unredeem coupon id: '||p_coupon_ID);
549
550 /*
551 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd1,QP_COUPONS_PK,COUPON_ID,1
552 */
553 UPDATE QP_COUPONS
554 SET redeemed_flag='N'
555 WHERE coupon_id = p_coupon_id;
556
557 -- Make Qualifier for the coupon Inactive
558 /*
559 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
560 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
561 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
562 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
563 */
564
565 -- Bug 9210291 - Replacing query to improve performance.
566 /*
567 UPDATE qp_qualifiers
568 SET end_date_active = NULL
569 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
570 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
571 AND nvl(comparison_operator_code,'=') = '='
572 AND qualifier_attr_Value=p_Coupon_ID;
573 */
574
575 UPDATE /*+ INDEX(qpq QP_QUALIFIERS_N4) */ qp_qualifiers qpq
576 SET qpq.end_date_active = NULL
577 WHERE qpq.qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
578 AND qpq.qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
579 AND nvl(qpq.comparison_operator_code,'=') = '='
580 AND qpq.qualifier_attr_Value=TO_CHAR(p_Coupon_ID);
581
582 EXCEPTION
583
584 WHEN OTHERS THEN
585
586 x_return_status_txt := 'QP_COUPON_PVT.Mark_Coupon_Unredeemed: '||SQLERRM;
587 x_return_status := FND_API.G_RET_STS_ERROR;
588
589 End Mark_Coupon_Unredeemed;
590
591 PROCEDURE Mark_Coupon_Unredeemed(
592 p_coupon_number IN VARCHAR2
593 , x_return_status OUT NOCOPY VARCHAR2
594 , x_return_status_txt OUT NOCOPY VARCHAR2
595 )
596 IS
597 BEGIN
598
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600
601 /*
602 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd1,-No Index Used-,NA,NA
603 */
604 UPDATE QP_COUPONS
605 SET redeemed_flag='N'
606 WHERE coupon_number = p_coupon_number;
607
608 IF SQL%NOTFOUND THEN
609 RAISE QP_COUPON_NOT_FOUND;
610 END IF;
611
612 -- Make Qualifier for the coupon Inactive
613 /*
614 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
615 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
616 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
617 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
618 */
619
620 /*
621 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_sel1,QP_COUPONS_PK,COUPON_ID,1
622 */
623
624 -- Bug 9210291 - Replacing query to improve performance.
625 /*
626 UPDATE qp_qualifiers
627 SET end_date_active = NULL
628 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
629 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
630 AND nvl(comparison_operator_code,'=') = '='
631 AND qualifier_attr_Value in
632 (select coupon_id from qp_coupons where coupon_number=p_Coupon_Number);
633 */
634
635 UPDATE /*+ INDEX(qpq QP_QUALIFIERS_N4) */ qp_qualifiers qpq
636 SET qpq.end_date_active = NULL
637 WHERE qpq.qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
638 AND qpq.qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
639 AND nvl(qpq.comparison_operator_code,'=') = '='
640 AND qpq.qualifier_attr_Value in
641 (select TO_CHAR(coupon_id) from qp_coupons where coupon_number=p_Coupon_Number);
642
643 IF SQL%NOTFOUND THEN
644 RAISE QP_COUPON_QUALIFIER_NOT_FOUND;
645 END IF;
646
647 EXCEPTION
648
649 WHEN QP_COUPON_NOT_FOUND THEN
650
651 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_NUMBER');
652 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
653 x_return_status_txt := FND_MESSAGE.get;
654 x_return_status := FND_API.G_RET_STS_ERROR;
655
656 WHEN QP_COUPON_QUALIFIER_NOT_FOUND THEN
657
658 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_QUALIFIER');
659 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
660 x_return_status_txt := FND_MESSAGE.get;
661 x_return_status := FND_API.G_RET_STS_ERROR;
662
663 WHEN OTHERS THEN
664
665 x_return_status_txt := 'QP_COUPON_PVT.Mark_Coupon_Unredeemed: '||SQLERRM;
666 x_return_status := FND_API.G_RET_STS_ERROR;
667
668 END Mark_Coupon_Unredeemed;
669
670 -- Procedure Purge_Coupon purges all redeemed and expired coupons
671 PROCEDURE Purge_Coupon(
672 x_return_status OUT NOCOPY VARCHAR2
673 , x_return_status_txt OUT NOCOPY VARCHAR2
674 )
675 IS
676 BEGIN
677
678 x_return_status := FND_API.G_RET_STS_SUCCESS;
679
680 /*
681 INDX,QP_COUPON_PVT.Purge_Coupon.Purge_Coupon_del1,-No Index Used-,NA,NA
682 */
683 DELETE FROM QP_COUPONS
684 WHERE redeemed_flag='Y'
685 OR expiration_date < sysdate;
686
687 EXCEPTION
688
689 WHEN OTHERS THEN
690
691 x_return_status_txt := 'QP_COUPON_PVT.Purge_Coupon: '||SQLERRM;
692
693 x_return_status := FND_API.G_RET_STS_ERROR;
694
695 END Purge_Coupon;
696
697 -- Procedure Delete_Coupon deletes the coupon
698 PROCEDURE Delete_Coupon(
699 p_coupon_number IN VARCHAR2
700 , x_return_status OUT NOCOPY VARCHAR2
701 , x_return_status_txt OUT NOCOPY VARCHAR2
702 )
703 IS
704 BEGIN
705
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707
708 -- Delete Qualifier for the coupon
709 /*
710 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
711 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
712 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
713 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
714 */
715
716 /*
717 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_sel1,QP_COUPONS_PK,COUPON_ID,1
718 */
719 DELETE FROM qp_qualifiers
720 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
721 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
722 AND nvl(comparison_operator_code,'=') = '='
723 AND qualifier_attr_Value in
724 (select coupon_id from qp_coupons where coupon_number=p_coupon_number);
725
726 IF SQL%NOTFOUND THEN
727 RAISE QP_COUPON_QUALIFIER_NOT_FOUND;
728 END IF;
729
730 /*
731 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del2,-No Index Used-,NA,NA
732 */
733 DELETE FROM QP_COUPONS
734 WHERE Coupon_number = p_Coupon_number;
735
736 IF SQL%NOTFOUND THEN
737 RAISE QP_COUPON_NOT_FOUND;
738 END IF;
739
740 EXCEPTION
741
742 WHEN QP_COUPON_NOT_FOUND THEN
743
744 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_NUMBER');
745 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
746 x_return_status_txt := FND_MESSAGE.get;
747 x_return_status := FND_API.G_RET_STS_ERROR;
748
749 WHEN QP_COUPON_QUALIFIER_NOT_FOUND THEN
750
751 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_QUALIFIER');
752 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
753 x_return_status_txt := FND_MESSAGE.get;
754 x_return_status := FND_API.G_RET_STS_ERROR;
755
756 WHEN OTHERS THEN
757
758 x_return_status_txt := 'QP_COUPON_PVT.Delete_Coupon '||SQLERRM;
759
760 x_return_status := FND_API.G_RET_STS_ERROR;
761
762 END Delete_Coupon;
763
764 PROCEDURE Process_Coupon_Issue(
765 p_line_detail_index IN NUMBER
766 , p_pricing_phase_id IN NUMBER
767 , p_line_quantity IN NUMBER
768 , p_simulation_flag IN VARCHAR2
769 , x_return_status OUT NOCOPY VARCHAR2
770 , x_return_status_txt OUT NOCOPY VARCHAR2
771 ) IS
772
773 -- Get coupon issue line
774 /*
775 INDX,QP_COUPON_PVT.Process_Coupon_Issue.get_coupon_issue_lines,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
776
777 INDX,QP_COUPON_PVT.Process_Coupon_Issue.get_coupon_issue_lines,QP_LIST_LINES_PK,LIST_LINE_ID,1
778 */
779 CURSOR get_coupon_issue_lines IS
780 SELECT /*+ ORDERED USE_NL(b c) */
781 a.line_index,
782 b.line_detail_index,
783 c.price_break_type_code,
784 c.Expiration_Date,
785 c.expiration_period_start_date,
786 c.number_expiration_periods,
787 c.expiration_period_uom expiration_period_uom_code,
788 c.list_header_id,
789 c.list_line_id,
790 c.base_qty,
791 c.base_uom_code,
792 c.pricing_group_sequence,
793 c.list_line_no,
794 c.automatic_flag,
795 c.print_on_invoice_flag,
796 c.override_flag,
797 c.pricing_phase_id,
798 c.primary_uom_flag,
799 c.product_precedence,
800 b.created_from_list_type_code,
801 a.pricing_effective_date,
802 b.line_detail_type_code,
803 b.incompatability_grp_code,
804 b.process_code,
805 b.applied_flag,
806 b.modifier_level_code
807 FROM qp_npreq_lines_tmp a ,
808 qp_npreq_ldets_tmp b ,
809 QP_LIST_LINES c
810 WHERE a.LINE_INDEX = b.LINE_INDEX
811 AND b.LINE_DETAIL_INDEX = p_line_detail_index
812 AND b.CREATED_FROM_LIST_LINE_ID = c.LIST_LINE_ID
813 AND b.CREATED_FROM_LIST_LINE_TYPE
814 = QP_COUPON_PVT.G_COUPON_ISSUE_LINE_TYPE
815 AND b.PRICING_PHASE_ID = p_pricing_phase_id
816 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
817
818 l_number_of_coupons NUMBER := 1;
819 l_coupon_number VARCHAR2(240);
820 l_coupon_id NUMBER;
821 l_line_detail_index PLS_INTEGER;
822 l_return_status VARCHAR2(1);
823 BEGIN
824 l_debug :=QP_PREQ_GRP.G_DEBUG_ENGINE;
825 x_return_status := FND_API.G_RET_STS_SUCCESS;
826
827 IF l_debug = FND_API.G_TRUE THEN
828 QP_PREQ_GRP.engine_debug ('Entering QP_COUPON_PVT.Process_Coupon_Issue...');
829 QP_PREQ_GRP.engine_debug ('p_line_quantity: '||p_line_quantity);
830 END IF; -- END IF l_debug
831
832 FOR i IN get_coupon_issue_lines
833 LOOP
834
835 IF (i.price_break_type_code = QP_PREQ_GRP.G_RECURRING_BREAK) THEN
836
837 IF l_debug = FND_API.G_TRUE THEN
838 QP_PREQ_GRP.engine_debug ('RECURRING BREAK ...');
839 QP_PREQ_GRP.engine_debug ('i.list_line_id: '||i.list_line_id);
840 END IF; -- END IF l_debug
841
842 qp_process_other_benefits_pvt.calculate_recurring_quantity(
843 i.list_line_id,
844 i.list_header_id,
845 i.line_index,
846 NULL,
847 l_number_of_coupons,
848 l_return_status,
849 x_return_status_txt
850 );
851
852 IF l_debug = FND_API.G_TRUE THEN
853 QP_PREQ_GRP.engine_debug ('l_number_of_coupons: '||l_number_of_coupons);
854 END IF; -- END IF l_debug
855
856 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
857 RAISE FND_API.G_EXC_ERROR;
858 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
860 END IF;
861
862 END IF;
863
864 IF l_debug = FND_API.G_TRUE THEN
865 QP_PREQ_GRP.engine_debug ('p_simulation_flag: '||p_simulation_flag);
866 END IF; -- END IF l_debug
867
868 FOR j IN 1..l_number_of_coupons
869 LOOP
870 -- 1. create a coupon if not a simulation
871 IF (p_simulation_flag = 'N') THEN
872
873 Insert_Coupon(i.list_line_id,
874 i.expiration_period_start_date,
875 i.expiration_date,
876 i.number_expiration_periods,
877 i.expiration_period_uom_code,
878 i.list_line_no,
879 i.pricing_effective_date,
880 l_coupon_id,
881 l_coupon_number,
882 l_return_status,
883 x_return_status_txt
884 );
885
886 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
888 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891
892 --DBMS_OUTPUT.PUT_LINE('Inserted Coupon ID: '||l_coupon_id);
893
894 -- 2. create a qualifier: how to qualify the coupon benefits
895 Create_Coupon_Qualifier(i.list_line_id,
896 l_coupon_id,
897 l_return_status,
898 x_return_status_txt
899 );
900
901 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
904 RAISE FND_API.G_EXC_ERROR;
905 END IF;
906
907 --DBMS_OUTPUT.PUT_LINE('Created Qualifier');
908 ELSE
909 l_coupon_number := NULL;
910
911 END IF; /* not simulation */
912
913 IF (J = 1) THEN
914
915 --DBMS_OUTPUT.PUT_LINE('Update Coupon No'||l_coupon_number);
916 /*
917 INDX,QP_COUPON_PVT.Process_Coupon_Issue.Process_Coupon_Issue_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
918 */
919 UPDATE qp_npreq_ldets_tmp
920 SET PROCESSED_FLAG = 'Y',
921 LIST_LINE_NO = l_coupon_number,
922 PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
923 PRICING_STATUS_TEXT = 'Coupon_Issue'
924 WHERE LINE_INDEX = i.LINE_INDEX
925 AND LINE_DETAIL_INDEX = i.LINE_DETAIL_INDEX;
926
927 ELSE
928
929 --SELECT max(line_detail_index)
930 --INTO l_line_detail_index
931 --FROM qp_npreq_ldets_tmp;
932
933 l_line_detail_index := QP_PREQ_GRP.GET_LINE_DETAIL_INDEX;
934
935 --DBMS_OUTPUT.PUT_LINE('max line detail'||l_line_detail_index);
936 -- 3. create adjustment line
937
938 INSERT INTO qp_npreq_ldets_tmp(
939 LINE_DETAIL_INDEX,
940 LINE_DETAIL_TYPE_CODE,
941 LINE_INDEX,
942 CREATED_FROM_LIST_HEADER_ID,
943 CREATED_FROM_LIST_LINE_ID,
944 CREATED_FROM_LIST_LINE_TYPE,
945 CREATED_FROM_LIST_TYPE_CODE,
946 PRICING_GROUP_SEQUENCE,
947 PROCESSED_FLAG,
948 AUTOMATIC_FLAG,
949 PRINT_ON_INVOICE_FLAG,
950 OVERRIDE_FLAG,
951 PRICING_PHASE_ID,
952 PRIMARY_UOM_FLAG,
953 PRODUCT_PRECEDENCE,
954 LIST_LINE_NO,
955 INCOMPATABILITY_GRP_CODE,
956 PROCESS_CODE,
957 APPLIED_FLAG,
958 MODIFIER_LEVEL_CODE,
959 PRICING_STATUS_CODE,
960 PRICING_STATUS_TEXT)
961 VALUES (l_line_detail_index,
962 i.line_detail_type_code,
963 i.line_index,
964 i.LIST_HEADER_ID,
965 i.LIST_LINE_ID,
966 G_COUPON_ISSUE_LINE_TYPE,
967 i.CREATED_FROM_LIST_TYPE_CODE,
968 i.PRICING_GROUP_SEQUENCE,
969 'Y',
970 i.AUTOMATIC_FLAG,
971 i.PRINT_ON_INVOICE_FLAG,
972 i.OVERRIDE_FLAG,
973 i.PRICING_PHASE_ID,
974 i.PRIMARY_UOM_FLAG,
975 i.PRODUCT_PRECEDENCE,
976 l_coupon_number,
977 i.incompatability_grp_code,
978 i.process_code,
979 i.applied_flag,
980 i.modifier_level_code,
981 QP_PREQ_GRP.G_STATUS_NEW,
982 'Coupon Issue');
983
984 --DBMS_OUTPUT.PUT_LINE('Inserted adjustment line');
985
986 END IF; /* J=1, first coupon */
987
988 END LOOP; /* number of coupons */
989
990 END LOOP; /* coupon issue lines */
991
992 EXCEPTION
993
994 WHEN FND_API.G_EXC_ERROR THEN
995
996 x_return_status := FND_API.G_RET_STS_ERROR;
997
998 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
999
1000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1001
1002 WHEN OTHERS THEN
1003
1004 x_return_status_txt := 'QP_COUPON_PVT.Process_Coupon_Issue: '||SQLERRM;
1005
1006 x_return_status := FND_API.G_RET_STS_ERROR;
1007
1008 END Process_Coupon_Issue;
1009
1010 PROCEDURE Redeem_Coupons(
1011 p_simulation_flag IN VARCHAR2
1012 , x_return_status OUT NOCOPY VARCHAR2
1013 , x_return_status_txt OUT NOCOPY VARCHAR2
1014 )
1015 IS
1016
1017 /*
1018 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_ldets_tmp_N7,PRICING_STATUS_CODE,1
1019
1020 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_line_attrs_tmp_N7,LINE_INDEX,1
1021 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_line_attrs_tmp_N7,ATTRIBUTE_TYPE,2
1022 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_line_attrs_tmp_N7,CONTEXT,3
1023
1024 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,QP_COUPONS_PK,COUPON_ID,1
1025
1026 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_lines_tmp_U1,LINE_INDEX,1
1027 */
1028 CURSOR get_coupons_ldet IS
1029 SELECT /*+ ordered index(qplat) index( qpl) index( qpd)*/ DISTINCT qpc.coupon_number, --5658579
1030 qpl.pricing_effective_date
1031 FROM qp_npreq_line_attrs_tmp qplat,
1032 qp_npreq_ldets_tmp qpd,
1033 qp_npreq_lines_tmp qpl,
1034 qp_coupons qpc
1035 WHERE qplat.line_index = qpd.line_index
1036 and qpl.line_index = qpd.line_index
1037 and qplat.line_detail_index = qpd.line_detail_index
1038 and qplat.attribute_type= QP_PREQ_GRP.G_QUALIFIER_TYPE
1039 and qplat.context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
1040 and qplat.attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
1041 and nvl(qplat.comparison_operator_type_code,'=') = '='
1042 and qplat.attribute_level = QP_PREQ_GRP.G_LINE_LEVEL
1043 and qpd.created_from_list_line_type <> QP_COUPON_PVT.G_COUPON_ISSUE_LINE_TYPE
1044 and qpd.APPLIED_FLAG = QP_PREQ_GRP.G_YES
1045 and qpd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1046 and qpc.coupon_id = to_number(qplat.value_from);
1047
1048 CURSOR get_coupons_line IS
1049 SELECT DISTINCT coupon_number,
1050 qpl.pricing_effective_date
1051 FROM qp_npreq_line_attrs_tmp qplat,
1052 qp_npreq_ldets_tmp qpd,
1053 qp_npreq_lines_tmp qpl,
1054 qp_qualifiers qpq,
1055 qp_coupons qpc
1056 WHERE qplat.line_index = qpl.line_index
1057 and qplat.line_detail_index is null
1058 and qplat.attribute_type = QP_PREQ_GRP.G_QUALIFIER_TYPE
1059 and qplat.context = QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
1060 and qplat.attribute = QP_COUPON_PVT.G_COUPON_QUALIFIER
1061 and nvl(qplat.comparison_operator_type_code,'=') = '='
1062 and qpq.qualifier_context = qplat.context
1063 and qpq.qualifier_attribute = qplat.attribute
1064 and qpq.QUALIFIER_ATTR_VALUE = qplat.value_from
1065 and qpc.coupon_id = to_number(qplat.value_from)
1066 and qpq.list_line_id = qpd.CREATED_FROM_LIST_LINE_ID
1067 and qpd.APPLIED_FLAG = QP_PREQ_PUB.G_YES
1068 and qpd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
1069
1070 l_return_status VARCHAR2(1);
1071 l_satis_quals_opt VARCHAR2(1);
1072
1073 BEGIN
1074
1075 l_debug :=QP_PREQ_GRP.G_DEBUG_ENGINE;
1076 x_return_status := FND_API.G_RET_STS_SUCCESS;
1077
1078 IF l_debug = FND_API.G_TRUE THEN
1079 QP_PREQ_GRP.engine_debug ('Entering QP_COUPON_PVT.Redeem_Coupons');
1080 END IF;
1081
1082 --dbms_output.put_line('inside redeem coupon');
1083 IF (p_simulation_flag = 'N') THEN
1084
1085 l_satis_quals_opt := nvl(fnd_profile.VALUE('QP_SATIS_QUALS_OPT'), 'Y');
1086 IF l_debug = FND_API.G_TRUE THEN
1087 QP_PREQ_GRP.engine_debug('QP_SATIS_QUALS_OPT: ' || l_satis_quals_opt);
1088 END IF;
1089
1090 -- [julin/4136528] drive off ldet attr if available (more performant),
1091 -- otherwise, use line attr and verify that coupon benefit (ldet) is applied.
1092 IF l_satis_quals_opt <> 'N' THEN
1093 FOR i IN get_coupons_ldet
1094 LOOP
1095 IF l_debug = FND_API.G_TRUE THEN
1096 QP_PREQ_GRP.engine_debug ('found coupon'||i.coupon_number);
1097 END IF;
1098
1099 Mark_Coupon_Redeemed(i.Coupon_Number
1100 , i.pricing_effective_date
1101 , l_return_status
1102 , x_return_status_txt);
1103
1104 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1105 RAISE FND_API.G_EXC_ERROR;
1106 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1108 END IF;
1109
1110 END LOOP;
1111 ELSE
1112 FOR i IN get_coupons_line
1113 LOOP
1114 IF l_debug = FND_API.G_TRUE THEN
1115 QP_PREQ_GRP.engine_debug ('found coupon'||i.coupon_number);
1116 END IF;
1117
1118 Mark_Coupon_Redeemed(i.Coupon_Number
1119 , i.pricing_effective_date
1120 , l_return_status
1121 , x_return_status_txt);
1122
1123 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1124 RAISE FND_API.G_EXC_ERROR;
1125 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1127 END IF;
1128
1129 END LOOP;
1130 END IF; -- l_satis_quals_opt
1131
1132 END IF;
1133
1134 EXCEPTION
1135
1136 WHEN FND_API.G_EXC_ERROR THEN
1137
1138 x_return_status := FND_API.G_RET_STS_ERROR;
1139
1140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1141
1142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1143
1144 WHEN OTHERS THEN
1145
1146 x_return_status_txt := 'QP_COUPON_PVT.Redeem_Coupons: '||SQLERRM;
1147
1148 x_return_status := FND_API.G_RET_STS_ERROR;
1149
1150 END Redeem_Coupons;
1151
1152 PROCEDURE Set_Expiration_Dates(
1153 p_expiration_period_start_date IN OUT NOCOPY DATE
1154 , p_expiration_period_end_date IN OUT NOCOPY DATE
1155 , p_number_expiration_periods IN NUMBER
1156 , p_expiration_period_uom_code IN Varchar2
1157 , p_pricing_effective_date IN DATE
1158 , x_return_status OUT NOCOPY VARCHAR2
1159 , x_return_status_txt OUT NOCOPY VARCHAR2
1160 ) IS
1161 l_pricing_effective_date DATE := p_pricing_effective_date;
1162 BEGIN
1163
1164 x_return_status := FND_API.G_RET_STS_SUCCESS;
1165
1166 IF (p_expiration_period_end_date IS NULL) THEN
1167
1168 IF (l_pricing_effective_date IS NULL) THEN
1169 SELECT sysdate
1170 INTO l_pricing_effective_date
1171 FROM DUAL;
1172 END IF;
1173
1174 SELECT
1175 decode(p_expiration_period_uom_code,
1176 'YR', add_months(nvl(p_expiration_period_start_date,l_pricing_effective_date),
1177 12*p_number_expiration_periods),
1178 'MTH', add_months(nvl(p_expiration_period_start_date,l_pricing_effective_date),
1179 p_number_expiration_periods),
1180 'WK', nvl(p_expiration_period_start_date,l_pricing_effective_date)
1181 + 7 * p_number_expiration_periods,
1182 'HR', nvl(p_expiration_period_start_date,l_pricing_effective_date)
1183 + p_number_expiration_periods/24,
1184 'MIN', nvl(p_expiration_period_start_date,l_pricing_effective_date)
1185 + p_number_expiration_periods/1440,
1186 nvl(p_expiration_period_start_date,l_pricing_effective_date)
1187 + p_number_expiration_periods)
1188 INTO p_expiration_period_end_date
1189 FROM dual;
1190
1191 END IF;
1192
1193 EXCEPTION
1194
1195 WHEN OTHERS THEN
1196
1197 x_return_status_txt := 'QP_COUPON_PVT.Set_Expiration_Dates: '||SQLERRM;
1198
1199 x_return_status := FND_API.G_RET_STS_ERROR;
1200
1201 END Set_Expiration_Dates;
1202
1203 END QP_COUPON_PVT;