1 PACKAGE BODY QP_QPXPRMLS_XMLP_PKG AS
2 /* $Header: QPXPRMLSB.pls 120.2 2010/11/03 16:12:05 smuhamme noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 apf boolean;
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 apf := afterpform;
9 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
10 EXCEPTION
11 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
12 /*SRW.MESSAGE(1000
13 ,'Failed in BEFORE REPORT trigger')*/NULL;
14 RETURN (FALSE);
15 END;
16 DECLARE
17 L_COMPANY_NAME VARCHAR2(100);
18 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
19 BEGIN
20 SELECT
21 SOB.NAME,
22 SOB.CURRENCY_CODE
23 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
24 FROM
25 GL_SETS_OF_BOOKS SOB,
26 FND_CURRENCIES CUR
27 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
28 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
29 RP_COMPANY_NAME := L_COMPANY_NAME;
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 NULL;
33 END;
34 DECLARE
35 L_REPORT_NAME VARCHAR2(240);
36 BEGIN
37 SELECT
38 CP.USER_CONCURRENT_PROGRAM_NAME
39 INTO L_REPORT_NAME
40 FROM
41 FND_CONCURRENT_PROGRAMS_VL CP,
42 FND_CONCURRENT_REQUESTS CR
43 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
44 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
45 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
46 RP_REPORT_NAME := L_REPORT_NAME;
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 RP_REPORT_NAME := 'Modifier Details';
50 END;
51 BEGIN
52 SELECT
53 MEANING
54 INTO LP_ACTIVE_ONLY
55 FROM
56 FND_LOOKUPS
57 WHERE LOOKUP_TYPE = 'YES_NO'
58 AND LOOKUP_CODE = UPPER(SUBSTR(P_ACTIVE_MODIFIER_ONLY
59 ,1
60 ,1));
61 SELECT
62 MEANING
63 INTO LP_ACTIVE_LINES_ONLY
64 FROM
65 FND_LOOKUPS
66 WHERE LOOKUP_TYPE = 'YES_NO'
67 AND LOOKUP_CODE = UPPER(SUBSTR(P_ACTIVE_MODIFIER_LINES
68 ,1
69 ,1));
70 END;
71 RETURN (TRUE);
72 END BEFOREREPORT;
73
74 FUNCTION CF_LINE_TYPE_CODEFORMULA(LIST_TYPE_CODE IN VARCHAR2) RETURN CHAR IS
75 X_LINE_TYPE VARCHAR2(80);
76 CURSOR CUR_LINE_TYPE IS
77 SELECT
78 MEANING
79 FROM
80 QP_LOOKUPS
81 WHERE LOOKUP_TYPE = 'LIST_TYPE_CODE'
82 AND LOOKUP_CODE = LIST_TYPE_CODE;
83 BEGIN
84 OPEN CUR_LINE_TYPE;
85 FETCH CUR_LINE_TYPE
86 INTO X_LINE_TYPE;
87 CLOSE CUR_LINE_TYPE;
88 RETURN (X_LINE_TYPE);
89 END CF_LINE_TYPE_CODEFORMULA;
90
91 FUNCTION CF_PARENT_NUMBERFORMULA(PARENT_LIST_HEADER_ID IN NUMBER) RETURN CHAR IS
92 X_PARENT_NUMBER VARCHAR2(240);
93 CURSOR CUR_PARENT_NUM IS
94 SELECT
95 NAME
96 FROM
97 QP_LIST_HEADERS_VL
98 WHERE LIST_HEADER_ID = PARENT_LIST_HEADER_ID;
99 BEGIN
100 OPEN CUR_PARENT_NUM;
101 FETCH CUR_PARENT_NUM
102 INTO X_PARENT_NUMBER;
103 CLOSE CUR_PARENT_NUM;
104 RETURN (X_PARENT_NUMBER);
105 END CF_PARENT_NUMBERFORMULA;
106
107 FUNCTION CF_PARENT_VERSIONFORMULA(PARENT_LIST_HEADER_ID IN NUMBER) RETURN CHAR IS
108 X_PARENT_VERSION VARCHAR2(30);
109 CURSOR CUR_VERSION IS
110 SELECT
111 VERSION_NO
112 FROM
113 QP_LIST_HEADERS_VL
114 WHERE LIST_HEADER_ID = PARENT_LIST_HEADER_ID;
115 BEGIN
116 OPEN CUR_VERSION;
117 FETCH CUR_VERSION
118 INTO X_PARENT_VERSION;
119 CLOSE CUR_VERSION;
120 RETURN (X_PARENT_VERSION);
121 END CF_PARENT_VERSIONFORMULA;
122
123 FUNCTION CF_COUP_LIST_LINEFORMULA(LIST_LINE_ID1 IN NUMBER) RETURN CHAR IS
124 X_COUP_LIST_LINE VARCHAR2(30);
125 CURSOR CUR_COUP_LIST IS
126 SELECT
127 QLL.LIST_LINE_NO
128 FROM
129 QP_LIST_LINES QLL
130 WHERE QLL.LIST_LINE_ID = (
131 SELECT
132 QR.TO_RLTD_MODIFIER_ID
133 FROM
134 QP_RLTD_MODIFIERS QR,
135 QP_LIST_LINES QL
136 WHERE QR.FROM_RLTD_MODIFIER_ID = QL.LIST_LINE_ID
137 AND QR.FROM_RLTD_MODIFIER_ID = LIST_LINE_ID1
138 AND QR.RLTD_MODIFIER_GRP_TYPE = 'COUPON' );
139 BEGIN
140 OPEN CUR_COUP_LIST;
141 FETCH CUR_COUP_LIST
142 INTO X_COUP_LIST_LINE;
143 CLOSE CUR_COUP_LIST;
144 RETURN (X_COUP_LIST_LINE);
145 END CF_COUP_LIST_LINEFORMULA;
146
147 FUNCTION CF_QUALIFIER_ATTRIBUTEFORMULA(QUALIFIER_CONTEXT IN VARCHAR2
148 ,QUALIFIER_ATTRIBUTE IN VARCHAR2) RETURN CHAR IS
149 X_ATTRIBUTE_CODE VARCHAR2(80);
150 X_SEGMENT_NAME VARCHAR2(80);
151 BEGIN
152 /*SRW.MESSAGE(1
153 ,' - in qual_context - ')*/NULL;
154 QP_UTIL.GET_ATTRIBUTE_CODE('QP_ATTR_DEFNS_QUALIFIER'
155 ,QUALIFIER_CONTEXT
156 ,QUALIFIER_ATTRIBUTE
157 ,X_ATTRIBUTE_CODE
158 ,X_SEGMENT_NAME);
159 /*SRW.MESSAGE(1
160 ,'segment_name = ' || X_SEGMENT_NAME)*/NULL;
161 /*SRW.MESSAGE(1
162 ,'attribute_code = ' || X_ATTRIBUTE_CODE)*/NULL;
163 RETURN (X_ATTRIBUTE_CODE);
164 END CF_QUALIFIER_ATTRIBUTEFORMULA;
165
166 FUNCTION GET_SEGMENT_NAME(QUALIFIER_CONTEXT IN VARCHAR2
167 ,QUALIFIER_ATTRIBUTE IN VARCHAR2) RETURN CHAR IS
168 CURSOR CUR_GET_APPLICATION_ID(APP_SHORT_NAME IN VARCHAR2) IS
169 SELECT
170 APPLICATION_ID
171 FROM
172 FND_APPLICATION
173 WHERE APPLICATION_SHORT_NAME = APP_SHORT_NAME;
174 V_DFLEX_R FND_DFLEX.DFLEX_R;
175 V_SEGMENTS_DR FND_DFLEX.SEGMENTS_DR;
176 V_CONTEXT_R FND_DFLEX.CONTEXT_R;
177 BEGIN
178 OPEN CUR_GET_APPLICATION_ID('QP');
179 FETCH CUR_GET_APPLICATION_ID
180 INTO V_DFLEX_R.APPLICATION_ID;
181 CLOSE CUR_GET_APPLICATION_ID;
182 V_DFLEX_R.FLEXFIELD_NAME := 'QP_ATTR_DEFNS_QUALIFIER';
183 V_CONTEXT_R.FLEXFIELD := V_DFLEX_R;
184 V_CONTEXT_R.CONTEXT_CODE := QUALIFIER_CONTEXT;
185 FND_DFLEX.GET_SEGMENTS(V_CONTEXT_R
186 ,V_SEGMENTS_DR
187 ,TRUE);
188 IF (V_SEGMENTS_DR.NSEGMENTS > 0) THEN
189 FOR i IN 1 .. V_SEGMENTS_DR.NSEGMENTS LOOP
190 IF (V_SEGMENTS_DR.APPLICATION_COLUMN_NAME(I) = QUALIFIER_ATTRIBUTE) THEN
191 RETURN (V_SEGMENTS_DR.SEGMENT_NAME(I));
192 END IF;
193 END LOOP;
194 ELSE
195 RETURN (NULL);
196 END IF;
197 RETURN (NULL);
198 EXCEPTION
199 WHEN OTHERS THEN
200 RETURN (NULL);
201 END GET_SEGMENT_NAME;
202
203 FUNCTION GET_NUM_DATE_FROM_CANONICAL(P_DATATYPE IN VARCHAR2
204 ,P_VALUE IN VARCHAR2) RETURN VARCHAR2 IS
205 L_VARCHAR_OUT VARCHAR2(2000);
206 INVALID_DATA_TYPE EXCEPTION;
207 BEGIN
208 IF P_DATATYPE = 'N' THEN
209 L_VARCHAR_OUT := TO_CHAR(FND_NUMBER.CANONICAL_TO_NUMBER(P_VALUE));
210 ELSIF P_DATATYPE = 'X' THEN
211 L_VARCHAR_OUT := FND_DATE.DATE_TO_DISPLAYDATE(FND_DATE.CANONICAL_TO_DATE(P_VALUE));
212 ELSIF P_DATATYPE = 'Y' THEN
213 L_VARCHAR_OUT := FND_DATE.DATE_TO_DISPLAYDT(FND_DATE.CANONICAL_TO_DATE(P_VALUE));
214 ELSIF P_DATATYPE = 'C' THEN
215 L_VARCHAR_OUT := P_VALUE;
216 ELSE
217 RAISE INVALID_DATA_TYPE;
218 END IF;
219 RETURN L_VARCHAR_OUT;
220 EXCEPTION
221 WHEN INVALID_DATA_TYPE THEN
222 RETURN (NULL);
223 WHEN OTHERS THEN
224 RETURN (NULL);
225 END GET_NUM_DATE_FROM_CANONICAL;
226
227 /*FUNCTION GET_ATTR_VALUE_TO(P_FLEXFIELD_NAME IN VARCHAR2
228 ,P_CONTEXT_NAME IN VARCHAR2
229 ,P_SEGMENT_NAME IN VARCHAR2
230 ,P_ATTR_VALUE_TO IN VARCHAR2) RETURN VARCHAR2 IS
231 VSET FND_VSET.VALUESET_R;
232 FMT FND_VSET.VALUESET_DR;
233 FOUND BOOLEAN;
234 ROW NUMBER;
235 VALUE FND_VSET.VALUE_DR;
236 X_FORMAT_TYPE VARCHAR2(1);
237 X_VALIDATION_TYPE VARCHAR2(1);
238 X_VSID NUMBER;
239 X_ATTR_VALUE_CODE VARCHAR2(240);
240 L_ATTR_VALUE_TO VARCHAR2(2000);
241 BEGIN
242 QP_UTIL.GET_VALUESET_ID(P_FLEXFIELD_NAME
243 ,P_CONTEXT_NAME
244 ,P_SEGMENT_NAME
245 ,X_VSID
246 ,X_FORMAT_TYPE
247 ,X_VALIDATION_TYPE);
248 L_ATTR_VALUE_TO := GET_NUM_DATE_FROM_CANONICAL(QUALIFIER_DATATYPE
249 ,QUALIFIER_ATTR_VALUE_TO);
250 IF (COMPARISION_OPERATOR_CODE <> 'BETWEEN') AND (L_ATTR_VALUE_TO IS NOT NULL) THEN
251 IF X_VALIDATION_TYPE in ('F','I') AND X_VSID IS NOT NULL THEN
252 FND_VSET.GET_VALUESET(X_VSID
253 ,VSET
254 ,FMT);
255 FND_VSET.GET_VALUE_INIT(VSET
256 ,TRUE);
257 FND_VSET.GET_VALUE(VSET
258 ,ROW
259 ,FOUND
260 ,VALUE);
261 WHILE (FOUND) LOOP
262
263 IF L_ATTR_VALUE_TO = VALUE.VALUE THEN
264 X_ATTR_VALUE_CODE := VALUE.MEANING;
265 EXIT;
266 END IF;
267 FND_VSET.GET_VALUE(VSET
268 ,ROW
269 ,FOUND
270 ,VALUE);
271 END LOOP;
272 FND_VSET.GET_VALUE_END(VSET);
273 ELSE
274 X_ATTR_VALUE_CODE := L_ATTR_VALUE_TO;
275 END IF;
276 ELSE
277 X_ATTR_VALUE_CODE := L_ATTR_VALUE_TO;
278 END IF;
279 RETURN X_ATTR_VALUE_CODE;
280 EXCEPTION
281 WHEN OTHERS THEN
282 RETURN (NULL);
283 END GET_ATTR_VALUE_TO;*/
284
285 /*FUNCTION GET_ATTRIBUTE_VALUE(P_FLEXFIELD_NAME IN VARCHAR2
286 ,P_CONTEXT_NAME IN VARCHAR2
287 ,P_SEGMENT_NAME IN VARCHAR2
288 ,P_ATTR_VALUE IN VARCHAR2) RETURN VARCHAR2 IS
289 VSET FND_VSET.VALUESET_R;
290 FMT FND_VSET.VALUESET_DR;
291 FOUND BOOLEAN;
292 ROW NUMBER;
293 VALUE FND_VSET.VALUE_DR;
294 X_FORMAT_TYPE VARCHAR2(1);
295 X_VALIDATION_TYPE VARCHAR2(1);
296 X_VSID NUMBER;
297 X_ATTR_VALUE_CODE VARCHAR2(240);
298 L_ATTR_VALUE VARCHAR2(2000);
299 L_ID VARCHAR2(150);
300 L_VALUE VARCHAR2(150);
301 VALUE_VALID_IN_VALUESET BOOLEAN := FALSE;
302 BEGIN
303 QP_UTIL.GET_VALUESET_ID(P_FLEXFIELD_NAME
304 ,P_CONTEXT_NAME
305 ,P_SEGMENT_NAME
306 ,X_VSID
307 ,X_FORMAT_TYPE
308 ,X_VALIDATION_TYPE);
309 L_ATTR_VALUE := GET_NUM_DATE_FROM_CANONICAL(QUALIFIER_DATATYPE
310 ,QUALIFIER_ATTR_VALUE);
311 IF (COMPARISION_OPERATOR_CODE <> 'BETWEEN') AND (L_ATTR_VALUE IS NOT NULL) THEN
312 IF X_VALIDATION_TYPE in ('F','I') AND X_VSID IS NOT NULL THEN
313 IF X_VALIDATION_TYPE = 'I' THEN
314 FND_VSET.GET_VALUESET(X_VSID
315 ,VSET
316 ,FMT);
317 FND_VSET.GET_VALUE_INIT(VSET
318 ,TRUE);
319 FND_VSET.GET_VALUE(VSET
320 ,ROW
321 ,FOUND
322 ,VALUE);
323 IF FMT.HAS_ID THEN
324 WHILE (FOUND) LOOP
325
326 IF L_ATTR_VALUE = VALUE.ID THEN
327 X_ATTR_VALUE_CODE := VALUE.VALUE;
328 EXIT;
329 END IF;
330 FND_VSET.GET_VALUE(VSET
331 ,ROW
332 ,FOUND
333 ,VALUE);
334 END LOOP;
335 ELSE
336 WHILE (FOUND) LOOP
337
338 IF L_ATTR_VALUE = VALUE.VALUE THEN
339 X_ATTR_VALUE_CODE := L_ATTR_VALUE;
340 EXIT;
341 END IF;
342 FND_VSET.GET_VALUE(VSET
343 ,ROW
344 ,FOUND
345 ,VALUE);
346 END LOOP;
347 END IF;
348 FND_VSET.GET_VALUE_END(VSET);
349 ELSIF X_VALIDATION_TYPE = 'F' THEN
350 FND_VSET.GET_VALUESET(X_VSID
351 ,VSET
352 ,FMT);
353 IF (QP_UTIL.VALUE_EXISTS_IN_TABLE(VSET.TABLE_INFO
354 ,L_ATTR_VALUE
355 ,L_ID
356 ,L_VALUE)) THEN
357 IF FMT.HAS_ID THEN
358 IF L_ATTR_VALUE = L_ID THEN
359 X_ATTR_VALUE_CODE := L_VALUE;
360 VALUE_VALID_IN_VALUESET := TRUE;
361 END IF;
362 ELSE
363 IF L_ATTR_VALUE = L_VALUE THEN
364 X_ATTR_VALUE_CODE := L_ATTR_VALUE;
365 VALUE_VALID_IN_VALUESET := TRUE;
366 END IF;
367 END IF;
368 ELSE
369 VALUE_VALID_IN_VALUESET := FALSE;
370 END IF;
371 END IF;
372 ELSE
373 X_ATTR_VALUE_CODE := L_ATTR_VALUE;
374 END IF;
375 ELSE
376 X_ATTR_VALUE_CODE := L_ATTR_VALUE;
377 END IF;
378 RETURN X_ATTR_VALUE_CODE;
379 EXCEPTION
380 WHEN OTHERS THEN
381 RETURN (NULL);
382 END GET_ATTRIBUTE_VALUE;*/
383
384 FUNCTION CF_ATTR_VALUE_FROMFORMULA(QUALIFIER_CONTEXT IN VARCHAR2
385 ,QUALIFIER_ATTRIBUTE IN VARCHAR2
386 ,QUALIFIER_ATTR_VALUE IN VARCHAR2
387 ,COMPARISION_OPERATOR_CODE IN VARCHAR2
388 ,QUALIFIER_DATATYPE IN VARCHAR2
389 ,COMPARISION_OPERATOR_CODE1 IN VARCHAR2) RETURN CHAR IS
390 L_QUALIFIER_ATTR_VALUE VARCHAR2(240);
391 BEGIN
392 /*L_QUALIFIER_ATTR_VALUE := QP_UTIL.GET_ATTRIBUTE_VALUE(P_ATTR_VALUE
393 ,P_SEGMENT_NAME
394 ,P_CONTEXT_NAME
395 ,P_FLEXFIELD_NAME
396 ,QUALIFIER_DATATYPE
397 ,QUALIFIER_ATTR_VALUE
398 ,COMPARISION_OPERATOR_CODE);*/
399
400 L_QUALIFIER_ATTR_VALUE := qp_util.Get_Attribute_Value(p_FlexField_Name =>'QP_ATTR_DEFNS_QUALIFIER'
401 ,p_Context_Name => qualifier_context
402 ,p_segment_name => qualifier_attribute
403 ,p_attr_value => qualifier_attr_value
404 , p_comparison_operator_code =>comparision_operator_code);
405
406
407 IF (QUALIFIER_CONTEXT = 'ORDER' AND QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE5') THEN
408 BEGIN
409 SELECT
410 ORDER_NUMBER
411 INTO L_QUALIFIER_ATTR_VALUE
412 FROM
413 OE_BLANKET_HEADERS_ALL
414 WHERE HEADER_ID = QUALIFIER_ATTR_VALUE;
415 EXCEPTION
416 WHEN OTHERS THEN
417 NULL;
418 END;
419 ELSIF (QUALIFIER_CONTEXT = 'ORDER' AND QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE6') THEN
420 BEGIN
421 SELECT
422 H.ORDER_NUMBER || '-' || L.LINE_NUMBER
423 INTO L_QUALIFIER_ATTR_VALUE
424 FROM
425 OE_BLANKET_HEADERS_ALL H,
426 OE_BLANKET_LINES_ALL L
427 WHERE L.LINE_ID = QUALIFIER_ATTR_VALUE
428 AND H.HEADER_ID = L.HEADER_ID;
429 EXCEPTION
430 WHEN OTHERS THEN
431 NULL;
432 END;
433 END IF;
434 RETURN L_QUALIFIER_ATTR_VALUE;
435 END CF_ATTR_VALUE_FROMFORMULA;
436
437 FUNCTION CF_ATTR_VALUE_TOFORMULA(QUALIFIER_CONTEXT IN VARCHAR2
438 ,QUALIFIER_ATTRIBUTE IN VARCHAR2
439 ,QUALIFIER_ATTR_VALUE_TO IN VARCHAR2
440 ,COMPARISION_OPERATOR_CODE IN VARCHAR2
441 ,QUALIFIER_DATATYPE IN VARCHAR2
442 ,COMPARISION_OPERATOR_CODE1 IN VARCHAR2
443 ,QUALIFIER_ATTR_VALUE IN VARCHAR2) RETURN CHAR IS
444 L_QUALIFIER_ATTR_VALUE_TO VARCHAR2(240);
445 BEGIN
446 /*L_QUALIFIER_ATTR_VALUE_TO := QP_UTIL.GET_ATTRIBUTE_VALUE(P_ATTR_VALUE
447 ,P_SEGMENT_NAME
448 ,P_CONTEXT_NAME
449 ,P_FLEXFIELD_NAME
450 ,QUALIFIER_DATATYPE
451 ,QUALIFIER_ATTR_VALUE
452 ,COMPARISION_OPERATOR_CODE);*/
453
454 l_qualifier_attr_value_to := qp_util.Get_Attribute_Value(p_FlexField_Name =>'QP_ATTR_DEFNS_QUALIFIER'
455 ,p_Context_Name => qualifier_context
456 ,p_segment_name => qualifier_attribute
457 ,p_attr_value => qualifier_attr_value_to
458 , p_comparison_operator_code =>comparision_operator_code);
459
460 IF (QUALIFIER_CONTEXT = 'ORDER' AND QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE5') THEN
461 BEGIN
462 SELECT
463 ORDER_NUMBER
464 INTO L_QUALIFIER_ATTR_VALUE_TO
465 FROM
466 OE_BLANKET_HEADERS_ALL
467 WHERE HEADER_ID = QUALIFIER_ATTR_VALUE_TO;
468 EXCEPTION
469 WHEN OTHERS THEN
470 NULL;
471 END;
472 ELSIF (QUALIFIER_CONTEXT = 'ORDER' AND QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE6') THEN
473 BEGIN
474 SELECT
475 H.ORDER_NUMBER || '-' || L.LINE_NUMBER
476 INTO L_QUALIFIER_ATTR_VALUE_TO
477 FROM
478 OE_BLANKET_HEADERS_ALL H,
479 OE_BLANKET_LINES_ALL L
480 WHERE L.LINE_ID = QUALIFIER_ATTR_VALUE_TO
481 AND H.HEADER_ID = L.HEADER_ID;
482 EXCEPTION
483 WHEN OTHERS THEN
484 NULL;
485 END;
486 END IF;
487 RETURN L_QUALIFIER_ATTR_VALUE_TO;
488 END CF_ATTR_VALUE_TOFORMULA;
489
490 FUNCTION AFTERPFORM RETURN BOOLEAN IS
491 BEGIN
492 /*SRW.MESSAGE(99999
493 ,'$Header: QPXPRMLSB.pls 120.2 2010/11/03 16:12:05 smuhamme noship $')*/NULL;
494 IF (P_MODIFIER_NAME_FROM IS NOT NULL) AND (P_MODIFIER_NAME_TO IS NOT NULL) THEN
495 LP_MODIFIER_NAME := ' and a.description between :p_modifier_name_from and :p_modifier_name_to ';
496 ELSIF (P_MODIFIER_NAME_FROM IS NOT NULL) THEN
497 LP_MODIFIER_NAME := ' and a.description >= :p_modifier_name_from ';
498 ELSIF (P_MODIFIER_NAME_TO IS NOT NULL) THEN
499 LP_MODIFIER_NAME := ' and a.description <= :p_modifier_name_to ';
500 END IF;
501 IF (P_MODIFIER_NUMBER_FROM IS NOT NULL) AND (P_MODIFIER_NUMBER_TO IS NOT NULL) THEN
502 LP_MODIFIER_NUMBER := ' and a.name between :p_modifier_number_from and :p_modifier_number_to ';
503 ELSIF (P_MODIFIER_NUMBER_FROM IS NOT NULL) THEN
504 LP_MODIFIER_NUMBER := ' and a.name >= :p_modifier_number_from ';
505 ELSIF (P_MODIFIER_NUMBER_TO IS NOT NULL) THEN
506 LP_MODIFIER_NUMBER := ' and a.name <= :p_modifier_number_to ';
507 END IF;
508 IF (P_MODIFIER_VERSION_FROM IS NOT NULL) AND (P_MODIFIER_VERSION_TO IS NOT NULL) THEN
509 LP_MODIFIER_VERSION := ' and a.version_no between :p_modifier_version_from and :p_modifier_version_to ';
510 ELSIF (P_MODIFIER_VERSION_FROM IS NOT NULL) THEN
511 LP_MODIFIER_VERSION := ' and a.version_no >= :p_modifier_version_from ';
512 ELSIF (P_MODIFIER_VERSION_TO IS NOT NULL) THEN
513 LP_MODIFIER_VERSION := ' and a.version_no <= :p_modifier_version_to ';
514 END IF;
515 IF (P_ACTIVE_MODIFIER_ONLY IS NOT NULL) THEN
516 LP_ACTIVE_MODIFIER_ONLY := ' and a.active_flag = :p_active_modifier_only ';
517 END IF;
518 IF ((P_ACTIVE_MODIFIER_LINES IS NOT NULL) AND (UPPER(SUBSTR(P_ACTIVE_MODIFIER_LINES
519 ,1
520 ,1)) = 'Y')) THEN
521 LP_ACTIVE_MODIFIER_LINES_ONLY := ' and nvl(trunc(a.start_date_active),trunc(sysdate)) <= trunc(sysdate) ' || ' and nvl(trunc(a.end_date_active),trunc(sysdate)) >= trunc(sysdate) ';
522 END IF;
523 IF (P_START_DATE_ACTIVE IS NOT NULL) AND (P_END_DATE_ACTIVE IS NOT NULL) THEN
524 LP_ACTIVE_DATE := ' and a.start_date_active >= :p_start_date_active and a.end_date_active < (:p_end_date_active+1) ';
525 ELSIF (P_START_DATE_ACTIVE IS NOT NULL) THEN
526 LP_ACTIVE_DATE := ' and a.start_date_active >= :p_start_date_active ';
527 ELSIF (P_END_DATE_ACTIVE IS NOT NULL) THEN
528 LP_ACTIVE_DATE := ' and a.end_date_active < (:p_end_date_active+1) ';
529 END IF;
530 IF (P_CUSTOMER_ID IS NOT NULL) THEN
531 LP_CUSTOMER_ID := ' And Exists (SELECT NULL
532 FROM qp_qualifiers q
533 WHERE qualifier_context = ''CUSTOMER''
534 AND a.list_header_id = q.list_header_id
535 AND qualifier_attribute = ''QUALIFIER_ATTRIBUTE2''
536 AND qualifier_attr_value = :P_customer_id) ';
537 END IF;
538 IF (P_CUSTOMER_CLASS_ID IS NOT NULL) THEN
539 LP_CUSTOMER_CLASS_ID := ' And Exists (SELECT NULL
540 FROM qp_qualifiers q
541 WHERE qualifier_context = ''CUSTOMER''
542 AND a.list_header_id = q.list_header_id
543 AND qualifier_attribute = ''QUALIFIER_ATTRIBUTE1''
544 AND qualifier_attr_value = :P_customer_class_id) ';
545 END IF;
546 IF (P_PRICE_LIST_ID IS NOT NULL) THEN
547 LP_PRICE_LIST_ID := ' And Exists (SELECT NULL
548 FROM qp_qualifiers q
549 WHERE qualifier_context = ''MODLIST''
550 AND a.list_header_id = q.list_header_id
551 AND qualifier_attribute = ''QUALIFIER_ATTRIBUTE4''
552 AND qualifier_attr_value = :P_price_list_id) ';
553 END IF;
554 IF (P_ITEM_ID IS NOT NULL) THEN
555 LP_ITEM_ID := ' And Exists (SELECT NULL
556 FROM qp_pricing_attributes q
557 WHERE product_attribute_context = ''ITEM''
558 AND a.list_header_id = q.list_header_id
559 AND product_attribute = ''PRICING_ATTRIBUTE1''
560 AND product_attr_value = :P_item_id) ';
561 END IF;
562 IF (P_ITEM_CATEGORY_ID IS NOT NULL) THEN
563 LP_ITEM_CATEGORY_ID := ' And Exists (SELECT NULL
564 FROM qp_pricing_attributes q
565 WHERE product_attribute_context = ''ITEM''
566 AND a.list_header_id = q.list_header_id
567 AND product_attribute = ''PRICING_ATTRIBUTE2''
568 AND product_attr_value = :P_item_category_id) ';
569 END IF;
570 RETURN (TRUE);
571 END AFTERPFORM;
572
573 FUNCTION CF_NET_AMOUNT_CALCFORMULA(NET_AMOUNT_FLAG IN VARCHAR2) RETURN CHAR IS
574 X_NET_AMOUNT_CALC VARCHAR2(80);
575 CURSOR NET_AMOUNT IS
576 SELECT
577 MEANING
578 FROM
579 QP_LOOKUPS
580 WHERE LOOKUP_TYPE = 'QP_NET_AMOUNT_CALCULATION'
581 AND LOOKUP_CODE = NET_AMOUNT_FLAG;
582 BEGIN
583 OPEN NET_AMOUNT;
584 FETCH NET_AMOUNT
585 INTO X_NET_AMOUNT_CALC;
586 CLOSE NET_AMOUNT;
587 RETURN (X_NET_AMOUNT_CALC);
588 END CF_NET_AMOUNT_CALCFORMULA;
589
590 FUNCTION AFTERREPORT RETURN BOOLEAN IS
591 BEGIN
592 BEGIN
593 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
594 EXCEPTION
595 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
596 /*SRW.MESSAGE(1
597 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
598 RETURN (FALSE);
599 END;
600 RETURN (TRUE);
601 END AFTERREPORT;
602
603 FUNCTION CF_BENEFIT_PRICEFORMULA(BENEFIT_PRICE_LIST_LINE_ID IN NUMBER) RETURN NUMBER IS
604 X_OPERAND NUMBER;
605 CURSOR CUR_OPERAND IS
606 SELECT
607 OPERAND
608 FROM
609 QP_LIST_LINES
610 WHERE LIST_LINE_ID = BENEFIT_PRICE_LIST_LINE_ID;
611 BEGIN
612 OPEN CUR_OPERAND;
613 FETCH CUR_OPERAND
614 INTO X_OPERAND;
615 CLOSE CUR_OPERAND;
616 RETURN (X_OPERAND);
617 END CF_BENEFIT_PRICEFORMULA;
618
619 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
620 BEGIN
621 RETURN RP_REPORT_NAME;
622 END RP_REPORT_NAME_P;
623
624 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
625 BEGIN
626 RETURN RP_COMPANY_NAME;
627 END RP_COMPANY_NAME_P;
628
629 END QP_QPXPRMLS_XMLP_PKG;
[Home] [Help]