[Home] [Help]
PACKAGE BODY: APPS.QP_COUPON_PVT
Source
1 PACKAGE BODY QP_COUPON_PVT AS
2 /* $Header: QPXVCPNB.pls 120.3.12000000.2 2007/04/28 14:11:35 dhgupta 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
430 END IF;*/
427 =decode(l_qualification_ind, 0, NULL, l_qualification_ind)
428 where list_line_id = l_list_line_id;
429
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 UPDATE qp_qualifiers
492 SET end_date_active = p_pricing_effective_date
493 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
494 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
495 AND nvl(comparison_operator_code,'=') = '='
496 AND qualifier_attr_Value in
497 (select coupon_id from qp_coupons where coupon_number=p_Coupon_number);
498
499 IF SQL%NOTFOUND THEN
500 RAISE QP_COUPON_QUALIFIER_NOT_FOUND;
501 END IF;
502
503 EXCEPTION
504
505 WHEN QP_COUPON_NOT_FOUND THEN
506
507 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_NUMBER');
508 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
509 x_return_status_txt := FND_MESSAGE.get;
510 x_return_status := FND_API.G_RET_STS_ERROR;
511
512 WHEN QP_COUPON_QUALIFIER_NOT_FOUND THEN
513
514 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_QUALIFIER');
515 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
516 x_return_status_txt := FND_MESSAGE.get;
517 x_return_status := FND_API.G_RET_STS_ERROR;
518
519 WHEN OTHERS THEN
520
521 x_return_status_txt := 'QP_COUPON_PVT.Mark_Coupon_Redeemed: '||SQLERRM;
522
523 x_return_status := FND_API.G_RET_STS_ERROR;
524
525 END Mark_Coupon_Redeemed;
526
527 -- This is going to be obsolete, should use the overloaded version
528 -- with argument p_coupon_number
529 PROCEDURE Mark_Coupon_Unredeemed(
530 p_coupon_id IN NUMBER
531 , x_return_status OUT NOCOPY VARCHAR2
532 , x_return_status_txt OUT NOCOPY VARCHAR2
533 ) IS
534 BEGIN
535
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 --dbms_output.put_line('unredeem coupon id: '||p_coupon_ID);
539
540 /*
541 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd1,QP_COUPONS_PK,COUPON_ID,1
542 */
543 UPDATE QP_COUPONS
544 SET redeemed_flag='N'
545 WHERE coupon_id = p_coupon_id;
546
547 -- Make Qualifier for the coupon Inactive
548 /*
549 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
550 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
551 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
552 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
553 */
554 UPDATE qp_qualifiers
555 SET end_date_active = NULL
556 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
557 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
558 AND nvl(comparison_operator_code,'=') = '='
559 AND qualifier_attr_Value=p_Coupon_ID;
560
561 EXCEPTION
562
563 WHEN OTHERS THEN
564
568 End Mark_Coupon_Unredeemed;
565 x_return_status_txt := 'QP_COUPON_PVT.Mark_Coupon_Unredeemed: '||SQLERRM;
566 x_return_status := FND_API.G_RET_STS_ERROR;
567
569
570 PROCEDURE Mark_Coupon_Unredeemed(
571 p_coupon_number IN VARCHAR2
572 , x_return_status OUT NOCOPY VARCHAR2
573 , x_return_status_txt OUT NOCOPY VARCHAR2
574 )
575 IS
576 BEGIN
577
578 x_return_status := FND_API.G_RET_STS_SUCCESS;
579
580 /*
581 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd1,-No Index Used-,NA,NA
582 */
583 UPDATE QP_COUPONS
584 SET redeemed_flag='N'
585 WHERE coupon_number = p_coupon_number;
586
587 IF SQL%NOTFOUND THEN
588 RAISE QP_COUPON_NOT_FOUND;
589 END IF;
590
591 -- Make Qualifier for the coupon Inactive
592 /*
593 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
594 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
595 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
596 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_upd2,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
597 */
598
599 /*
600 INDX,QP_COUPON_PVT.Mark_Coupon_Unredeemed.Mark_Coupon_Unredeemed_sel1,QP_COUPONS_PK,COUPON_ID,1
601 */
602 UPDATE qp_qualifiers
603 SET end_date_active = NULL
604 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
605 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
606 AND nvl(comparison_operator_code,'=') = '='
607 AND qualifier_attr_Value in
608 (select coupon_id from qp_coupons where coupon_number=p_Coupon_Number);
609
610 IF SQL%NOTFOUND THEN
611 RAISE QP_COUPON_QUALIFIER_NOT_FOUND;
612 END IF;
613
614 EXCEPTION
615
616 WHEN QP_COUPON_NOT_FOUND THEN
617
618 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_NUMBER');
619 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
620 x_return_status_txt := FND_MESSAGE.get;
621 x_return_status := FND_API.G_RET_STS_ERROR;
622
623 WHEN QP_COUPON_QUALIFIER_NOT_FOUND THEN
624
625 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_QUALIFIER');
626 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
627 x_return_status_txt := FND_MESSAGE.get;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629
630 WHEN OTHERS THEN
631
632 x_return_status_txt := 'QP_COUPON_PVT.Mark_Coupon_Unredeemed: '||SQLERRM;
633 x_return_status := FND_API.G_RET_STS_ERROR;
634
635 END Mark_Coupon_Unredeemed;
636
637 -- Procedure Purge_Coupon purges all redeemed and expired coupons
638 PROCEDURE Purge_Coupon(
639 x_return_status OUT NOCOPY VARCHAR2
640 , x_return_status_txt OUT NOCOPY VARCHAR2
641 )
642 IS
643 BEGIN
644
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646
647 /*
648 INDX,QP_COUPON_PVT.Purge_Coupon.Purge_Coupon_del1,-No Index Used-,NA,NA
649 */
650 DELETE FROM QP_COUPONS
651 WHERE redeemed_flag='Y'
652 OR expiration_date < sysdate;
653
654 EXCEPTION
655
656 WHEN OTHERS THEN
657
658 x_return_status_txt := 'QP_COUPON_PVT.Purge_Coupon: '||SQLERRM;
659
660 x_return_status := FND_API.G_RET_STS_ERROR;
661
662 END Purge_Coupon;
663
664 -- Procedure Delete_Coupon deletes the coupon
665 PROCEDURE Delete_Coupon(
666 p_coupon_number IN VARCHAR2
667 , x_return_status OUT NOCOPY VARCHAR2
668 , x_return_status_txt OUT NOCOPY VARCHAR2
669 )
670 IS
671 BEGIN
672
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674
675 -- Delete Qualifier for the coupon
676 /*
677 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
678 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
679 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
680 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
681 */
682
683 /*
684 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_sel1,QP_COUPONS_PK,COUPON_ID,1
685 */
686 DELETE FROM qp_qualifiers
687 WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
688 AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
689 AND nvl(comparison_operator_code,'=') = '='
690 AND qualifier_attr_Value in
691 (select coupon_id from qp_coupons where coupon_number=p_coupon_number);
692
693 IF SQL%NOTFOUND THEN
694 RAISE QP_COUPON_QUALIFIER_NOT_FOUND;
695 END IF;
696
697 /*
698 INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del2,-No Index Used-,NA,NA
699 */
700 DELETE FROM QP_COUPONS
701 WHERE Coupon_number = p_Coupon_number;
702
703 IF SQL%NOTFOUND THEN
704 RAISE QP_COUPON_NOT_FOUND;
705 END IF;
706
707 EXCEPTION
708
709 WHEN QP_COUPON_NOT_FOUND THEN
710
711 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_NUMBER');
715
712 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
713 x_return_status_txt := FND_MESSAGE.get;
714 x_return_status := FND_API.G_RET_STS_ERROR;
716 WHEN QP_COUPON_QUALIFIER_NOT_FOUND THEN
717
718 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_COUPON_QUALIFIER');
719 FND_MESSAGE.SET_TOKEN('COUPON_NUMBER', p_coupon_number);
720 x_return_status_txt := FND_MESSAGE.get;
721 x_return_status := FND_API.G_RET_STS_ERROR;
722
723 WHEN OTHERS THEN
724
725 x_return_status_txt := 'QP_COUPON_PVT.Delete_Coupon '||SQLERRM;
726
727 x_return_status := FND_API.G_RET_STS_ERROR;
728
729 END Delete_Coupon;
730
731 PROCEDURE Process_Coupon_Issue(
732 p_line_detail_index IN NUMBER
733 , p_pricing_phase_id IN NUMBER
734 , p_line_quantity IN NUMBER
735 , p_simulation_flag IN VARCHAR2
736 , x_return_status OUT NOCOPY VARCHAR2
737 , x_return_status_txt OUT NOCOPY VARCHAR2
738 ) IS
739
740 -- Get coupon issue line
741 /*
742 INDX,QP_COUPON_PVT.Process_Coupon_Issue.get_coupon_issue_lines,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
743
744 INDX,QP_COUPON_PVT.Process_Coupon_Issue.get_coupon_issue_lines,QP_LIST_LINES_PK,LIST_LINE_ID,1
745 */
746 CURSOR get_coupon_issue_lines IS
747 SELECT /*+ ORDERED USE_NL(b c) */
748 a.line_index,
749 b.line_detail_index,
750 c.price_break_type_code,
751 c.Expiration_Date,
752 c.expiration_period_start_date,
753 c.number_expiration_periods,
754 c.expiration_period_uom expiration_period_uom_code,
755 c.list_header_id,
756 c.list_line_id,
757 c.base_qty,
758 c.base_uom_code,
759 c.pricing_group_sequence,
760 c.list_line_no,
761 c.automatic_flag,
762 c.print_on_invoice_flag,
763 c.override_flag,
764 c.pricing_phase_id,
765 c.primary_uom_flag,
766 c.product_precedence,
767 b.created_from_list_type_code,
768 a.pricing_effective_date,
769 b.line_detail_type_code,
770 b.incompatability_grp_code,
771 b.process_code,
772 b.applied_flag,
773 b.modifier_level_code
774 FROM qp_npreq_lines_tmp a ,
775 qp_npreq_ldets_tmp b ,
776 QP_LIST_LINES c
777 WHERE a.LINE_INDEX = b.LINE_INDEX
778 AND b.LINE_DETAIL_INDEX = p_line_detail_index
779 AND b.CREATED_FROM_LIST_LINE_ID = c.LIST_LINE_ID
780 AND b.CREATED_FROM_LIST_LINE_TYPE
781 = QP_COUPON_PVT.G_COUPON_ISSUE_LINE_TYPE
782 AND b.PRICING_PHASE_ID = p_pricing_phase_id
783 AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
784
785 l_number_of_coupons NUMBER := 1;
786 l_coupon_number VARCHAR2(240);
787 l_coupon_id NUMBER;
788 l_line_detail_index PLS_INTEGER;
789 l_return_status VARCHAR2(1);
790 BEGIN
791 l_debug :=QP_PREQ_GRP.G_DEBUG_ENGINE;
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793
794 IF l_debug = FND_API.G_TRUE THEN
795 QP_PREQ_GRP.engine_debug ('Entering QP_COUPON_PVT.Process_Coupon_Issue...');
796 QP_PREQ_GRP.engine_debug ('p_line_quantity: '||p_line_quantity);
797 END IF; -- END IF l_debug
798
799 FOR i IN get_coupon_issue_lines
800 LOOP
801
802 IF (i.price_break_type_code = QP_PREQ_GRP.G_RECURRING_BREAK) THEN
803
804 IF l_debug = FND_API.G_TRUE THEN
805 QP_PREQ_GRP.engine_debug ('RECURRING BREAK ...');
806 QP_PREQ_GRP.engine_debug ('i.list_line_id: '||i.list_line_id);
807 END IF; -- END IF l_debug
808
809 qp_process_other_benefits_pvt.calculate_recurring_quantity(
810 i.list_line_id,
811 i.list_header_id,
812 i.line_index,
813 NULL,
814 l_number_of_coupons,
815 l_return_status,
816 x_return_status_txt
817 );
818
819 IF l_debug = FND_API.G_TRUE THEN
820 QP_PREQ_GRP.engine_debug ('l_number_of_coupons: '||l_number_of_coupons);
821 END IF; -- END IF l_debug
822
823 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
824 RAISE FND_API.G_EXC_ERROR;
825 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828
829 END IF;
830
831 IF l_debug = FND_API.G_TRUE THEN
832 QP_PREQ_GRP.engine_debug ('p_simulation_flag: '||p_simulation_flag);
833 END IF; -- END IF l_debug
834
835 FOR j IN 1..l_number_of_coupons
836 LOOP
837 -- 1. create a coupon if not a simulation
838 IF (p_simulation_flag = 'N') THEN
839
840 Insert_Coupon(i.list_line_id,
841 i.expiration_period_start_date,
842 i.expiration_date,
843 i.number_expiration_periods,
844 i.expiration_period_uom_code,
845 i.list_line_no,
849 l_return_status,
846 i.pricing_effective_date,
847 l_coupon_id,
848 l_coupon_number,
850 x_return_status_txt
851 );
852
853 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858
859 --DBMS_OUTPUT.PUT_LINE('Inserted Coupon ID: '||l_coupon_id);
860
861 -- 2. create a qualifier: how to qualify the coupon benefits
862 Create_Coupon_Qualifier(i.list_line_id,
863 l_coupon_id,
864 l_return_status,
865 x_return_status_txt
866 );
867
868 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
870 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
871 RAISE FND_API.G_EXC_ERROR;
872 END IF;
873
874 --DBMS_OUTPUT.PUT_LINE('Created Qualifier');
875 ELSE
876 l_coupon_number := NULL;
877
878 END IF; /* not simulation */
879
880 IF (J = 1) THEN
881
882 --DBMS_OUTPUT.PUT_LINE('Update Coupon No'||l_coupon_number);
883 /*
884 INDX,QP_COUPON_PVT.Process_Coupon_Issue.Process_Coupon_Issue_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
885 */
886 UPDATE qp_npreq_ldets_tmp
887 SET PROCESSED_FLAG = 'Y',
888 LIST_LINE_NO = l_coupon_number,
889 PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
890 PRICING_STATUS_TEXT = 'Coupon_Issue'
891 WHERE LINE_INDEX = i.LINE_INDEX
892 AND LINE_DETAIL_INDEX = i.LINE_DETAIL_INDEX;
893
894 ELSE
895
896 --SELECT max(line_detail_index)
897 --INTO l_line_detail_index
898 --FROM qp_npreq_ldets_tmp;
899
900 l_line_detail_index := QP_PREQ_GRP.GET_LINE_DETAIL_INDEX;
901
902 --DBMS_OUTPUT.PUT_LINE('max line detail'||l_line_detail_index);
903 -- 3. create adjustment line
904
905 INSERT INTO qp_npreq_ldets_tmp(
906 LINE_DETAIL_INDEX,
907 LINE_DETAIL_TYPE_CODE,
908 LINE_INDEX,
909 CREATED_FROM_LIST_HEADER_ID,
910 CREATED_FROM_LIST_LINE_ID,
911 CREATED_FROM_LIST_LINE_TYPE,
912 CREATED_FROM_LIST_TYPE_CODE,
913 PRICING_GROUP_SEQUENCE,
914 PROCESSED_FLAG,
915 AUTOMATIC_FLAG,
916 PRINT_ON_INVOICE_FLAG,
917 OVERRIDE_FLAG,
918 PRICING_PHASE_ID,
919 PRIMARY_UOM_FLAG,
920 PRODUCT_PRECEDENCE,
921 LIST_LINE_NO,
922 INCOMPATABILITY_GRP_CODE,
923 PROCESS_CODE,
924 APPLIED_FLAG,
925 MODIFIER_LEVEL_CODE,
926 PRICING_STATUS_CODE,
927 PRICING_STATUS_TEXT)
928 VALUES (l_line_detail_index,
929 i.line_detail_type_code,
930 i.line_index,
931 i.LIST_HEADER_ID,
932 i.LIST_LINE_ID,
933 G_COUPON_ISSUE_LINE_TYPE,
934 i.CREATED_FROM_LIST_TYPE_CODE,
935 i.PRICING_GROUP_SEQUENCE,
936 'Y',
937 i.AUTOMATIC_FLAG,
938 i.PRINT_ON_INVOICE_FLAG,
939 i.OVERRIDE_FLAG,
940 i.PRICING_PHASE_ID,
941 i.PRIMARY_UOM_FLAG,
942 i.PRODUCT_PRECEDENCE,
943 l_coupon_number,
944 i.incompatability_grp_code,
945 i.process_code,
946 i.applied_flag,
947 i.modifier_level_code,
948 QP_PREQ_GRP.G_STATUS_NEW,
949 'Coupon Issue');
950
951 --DBMS_OUTPUT.PUT_LINE('Inserted adjustment line');
952
953 END IF; /* J=1, first coupon */
954
955 END LOOP; /* number of coupons */
956
957 END LOOP; /* coupon issue lines */
958
959 EXCEPTION
960
961 WHEN FND_API.G_EXC_ERROR THEN
962
963 x_return_status := FND_API.G_RET_STS_ERROR;
964
965 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
966
967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
968
969 WHEN OTHERS THEN
970
971 x_return_status_txt := 'QP_COUPON_PVT.Process_Coupon_Issue: '||SQLERRM;
972
973 x_return_status := FND_API.G_RET_STS_ERROR;
974
975 END Process_Coupon_Issue;
976
977 PROCEDURE Redeem_Coupons(
978 p_simulation_flag IN VARCHAR2
979 , x_return_status OUT NOCOPY VARCHAR2
980 , x_return_status_txt OUT NOCOPY VARCHAR2
981 )
982 IS
983
984 /*
985 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_ldets_tmp_N7,PRICING_STATUS_CODE,1
986
990
987 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_line_attrs_tmp_N7,LINE_INDEX,1
988 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_line_attrs_tmp_N7,ATTRIBUTE_TYPE,2
989 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_line_attrs_tmp_N7,CONTEXT,3
991 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,QP_COUPONS_PK,COUPON_ID,1
992
993 INDX,QP_COUPON_PVT.Redeem_Coupons.get_coupons,qp_npreq_lines_tmp_U1,LINE_INDEX,1
994 */
995 CURSOR get_coupons_ldet IS
996 SELECT /*+ ordered index(qplat) index( qpl) index( qpd)*/ DISTINCT qpc.coupon_number, --5658579
997 qpl.pricing_effective_date
998 FROM qp_npreq_line_attrs_tmp qplat,
999 qp_npreq_ldets_tmp qpd,
1000 qp_npreq_lines_tmp qpl,
1001 qp_coupons qpc
1002 WHERE qplat.line_index = qpd.line_index
1003 and qpl.line_index = qpd.line_index
1004 and qplat.line_detail_index = qpd.line_detail_index
1005 and qplat.attribute_type= QP_PREQ_GRP.G_QUALIFIER_TYPE
1006 and qplat.context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
1007 and qplat.attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
1008 and nvl(qplat.comparison_operator_type_code,'=') = '='
1009 and qplat.attribute_level = QP_PREQ_GRP.G_LINE_LEVEL
1010 and qpd.created_from_list_line_type <> QP_COUPON_PVT.G_COUPON_ISSUE_LINE_TYPE
1011 and qpd.APPLIED_FLAG = QP_PREQ_GRP.G_YES
1012 and qpd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
1013 and qpc.coupon_id = to_number(qplat.value_from);
1014
1015 CURSOR get_coupons_line IS
1016 SELECT DISTINCT coupon_number,
1017 qpl.pricing_effective_date
1018 FROM qp_npreq_line_attrs_tmp qplat,
1019 qp_npreq_ldets_tmp qpd,
1020 qp_npreq_lines_tmp qpl,
1021 qp_qualifiers qpq,
1022 qp_coupons qpc
1023 WHERE qplat.line_index = qpl.line_index
1024 and qplat.line_detail_index is null
1025 and qplat.attribute_type = QP_PREQ_GRP.G_QUALIFIER_TYPE
1026 and qplat.context = QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
1027 and qplat.attribute = QP_COUPON_PVT.G_COUPON_QUALIFIER
1028 and nvl(qplat.comparison_operator_type_code,'=') = '='
1029 and qpq.qualifier_context = qplat.context
1030 and qpq.qualifier_attribute = qplat.attribute
1031 and qpq.QUALIFIER_ATTR_VALUE = qplat.value_from
1032 and qpc.coupon_id = to_number(qplat.value_from)
1033 and qpq.list_line_id = qpd.CREATED_FROM_LIST_LINE_ID
1034 and qpd.APPLIED_FLAG = QP_PREQ_PUB.G_YES
1035 and qpd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
1036
1037 l_return_status VARCHAR2(1);
1038 l_satis_quals_opt VARCHAR2(1);
1039
1040 BEGIN
1041
1042 l_debug :=QP_PREQ_GRP.G_DEBUG_ENGINE;
1043 x_return_status := FND_API.G_RET_STS_SUCCESS;
1044
1045 IF l_debug = FND_API.G_TRUE THEN
1046 QP_PREQ_GRP.engine_debug ('Entering QP_COUPON_PVT.Redeem_Coupons');
1047 END IF;
1048
1049 --dbms_output.put_line('inside redeem coupon');
1050 IF (p_simulation_flag = 'N') THEN
1051
1052 l_satis_quals_opt := nvl(fnd_profile.VALUE('QP_SATIS_QUALS_OPT'), 'Y');
1053 IF l_debug = FND_API.G_TRUE THEN
1054 QP_PREQ_GRP.engine_debug('QP_SATIS_QUALS_OPT: ' || l_satis_quals_opt);
1055 END IF;
1056
1057 -- [julin/4136528] drive off ldet attr if available (more performant),
1058 -- otherwise, use line attr and verify that coupon benefit (ldet) is applied.
1059 IF l_satis_quals_opt <> 'N' THEN
1060 FOR i IN get_coupons_ldet
1061 LOOP
1062 IF l_debug = FND_API.G_TRUE THEN
1063 QP_PREQ_GRP.engine_debug ('found coupon'||i.coupon_number);
1064 END IF;
1065
1066 Mark_Coupon_Redeemed(i.Coupon_Number
1067 , i.pricing_effective_date
1068 , l_return_status
1069 , x_return_status_txt);
1070
1071 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1072 RAISE FND_API.G_EXC_ERROR;
1073 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END IF;
1076
1077 END LOOP;
1078 ELSE
1079 FOR i IN get_coupons_line
1080 LOOP
1081 IF l_debug = FND_API.G_TRUE THEN
1082 QP_PREQ_GRP.engine_debug ('found coupon'||i.coupon_number);
1083 END IF;
1084
1085 Mark_Coupon_Redeemed(i.Coupon_Number
1086 , i.pricing_effective_date
1087 , l_return_status
1088 , x_return_status_txt);
1089
1090 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1091 RAISE FND_API.G_EXC_ERROR;
1092 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1094 END IF;
1095
1096 END LOOP;
1097 END IF; -- l_satis_quals_opt
1098
1099 END IF;
1100
1101 EXCEPTION
1102
1103 WHEN FND_API.G_EXC_ERROR THEN
1104
1105 x_return_status := FND_API.G_RET_STS_ERROR;
1106
1107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1108
1109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1110
1111 WHEN OTHERS THEN
1112
1113 x_return_status_txt := 'QP_COUPON_PVT.Redeem_Coupons: '||SQLERRM;
1114
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116
1117 END Redeem_Coupons;
1118
1119 PROCEDURE Set_Expiration_Dates(
1120 p_expiration_period_start_date IN OUT NOCOPY DATE
1121 , p_expiration_period_end_date IN OUT NOCOPY DATE
1122 , p_number_expiration_periods IN NUMBER
1123 , p_expiration_period_uom_code IN Varchar2
1124 , p_pricing_effective_date IN DATE
1125 , x_return_status OUT NOCOPY VARCHAR2
1126 , x_return_status_txt OUT NOCOPY VARCHAR2
1127 ) IS
1128 l_pricing_effective_date DATE := p_pricing_effective_date;
1129 BEGIN
1130
1131 x_return_status := FND_API.G_RET_STS_SUCCESS;
1132
1133 IF (p_expiration_period_end_date IS NULL) THEN
1134
1135 IF (l_pricing_effective_date IS NULL) THEN
1136 SELECT sysdate
1137 INTO l_pricing_effective_date
1138 FROM DUAL;
1139 END IF;
1140
1141 SELECT
1142 decode(p_expiration_period_uom_code,
1143 'YR', add_months(nvl(p_expiration_period_start_date,l_pricing_effective_date),
1144 12*p_number_expiration_periods),
1145 'MTH', add_months(nvl(p_expiration_period_start_date,l_pricing_effective_date),
1146 p_number_expiration_periods),
1147 'WK', nvl(p_expiration_period_start_date,l_pricing_effective_date)
1148 + 7 * p_number_expiration_periods,
1149 'HR', nvl(p_expiration_period_start_date,l_pricing_effective_date)
1150 + p_number_expiration_periods/24,
1151 'MIN', nvl(p_expiration_period_start_date,l_pricing_effective_date)
1152 + p_number_expiration_periods/1440,
1153 nvl(p_expiration_period_start_date,l_pricing_effective_date)
1154 + p_number_expiration_periods)
1155 INTO p_expiration_period_end_date
1156 FROM dual;
1157
1158 END IF;
1159
1160 EXCEPTION
1161
1162 WHEN OTHERS THEN
1163
1164 x_return_status_txt := 'QP_COUPON_PVT.Set_Expiration_Dates: '||SQLERRM;
1165
1166 x_return_status := FND_API.G_RET_STS_ERROR;
1167
1168 END Set_Expiration_Dates;
1169
1170 END QP_COUPON_PVT;