APPS.QP_COUPON_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 30

           select 'X' qual_exists
           from qp_qualifiers
           where list_header_id=p_header_id
           and list_line_id= -1;

Line: 39

           select active_flag
		, list_type_code
           from qp_list_headers_b
           where list_header_id=p_header_id;

Line: 76

PROCEDURE update_qual_ind(p_list_header_id IN NUMBER,
                        p_list_line_id IN NUMBER,
			x_return_status OUT NOCOPY VARCHAR2,
			x_return_text OUT NOCOPY VARCHAR2) IS

/*
INDX,QP_COUPON_PVT.update_qual_ind.l_line_qual_exists_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
INDX,QP_COUPON_PVT.update_qual_ind.l_line_qual_exists_cur,QP_QUALIFIERS_N1,LIST_LINE_ID,2
*/
CURSOR l_line_qual_exists_cur(p_line_id NUMBER
			     ,p_header_id NUMBER) IS
           select 'Y'
           from qp_qualifiers q
           where q.list_header_id=p_header_id
           and q.list_line_id=p_line_id;

Line: 105

INDX,QP_COUPON_PVT.update_qual_ind.update_qual_ind_upd1,QP_LIST_LINES_PK,LIST_LINE_ID,1
*/
		update qp_list_lines qpl set
		qpl.qualification_ind =
		nvl(qpl.qualification_ind,0)+8
		where qpl.list_line_id=p_list_line_id
		returning qpl.qualification_ind into l_qual_ind;

Line: 114

INDX,QP_COUPON_PVT.update_qual_ind.update_qual_ind_upd2,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
*/
		update qp_pricing_attributes pra
	        set    pra.qualification_ind = l_qual_ind
       		where  pra.list_line_id = p_list_line_id;

Line: 125

x_return_text := 'QP_COUPON_PVT.update_qual_ind : '||SQLERRM;

Line: 127

END update_qual_ind;

Line: 135

PROCEDURE Insert_Coupon(
   p_issued_by_modifier_id      IN NUMBER
,  p_expiration_period_start_date                 IN DATE    := NULL
,  p_expiration_date            IN DATE    := NULL
,  p_number_expiration_periods  IN NUMBER  := NULL
,  p_expiration_period_uom_code IN VARCHAR2
,  p_user_def_coupon_number     IN VARCHAR2
,  p_pricing_effective_date   IN DATE
,  x_coupon_id                  OUT NOCOPY NUMBER
,  x_coupon_number              OUT NOCOPY VARCHAR2
,  x_return_status         OUT NOCOPY VARCHAR2
,  x_return_status_txt          OUT NOCOPY VARCHAR2
) IS
l_generated_coupon_number NUMBER;

Line: 159

    QP_PREQ_GRP.engine_debug ('Entering QP_COUPON_PVT.Insertr_Coupon ...');

Line: 199

  SELECT QP_GENERATED_COUPON_NO_S.nextval,
  QP_COUPONS_S.nextval
  INTO l_generated_coupon_number,
  x_coupon_id
  FROM dual;

Line: 217

  INSERT INTO QP_COUPONS(
   COUPON_ID,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_LOGIN,
   COUPON_NUMBER,
   USER_DEF_COUPON_NUMBER,
   GENERATED_COUPON_NUMBER,
   ISSUED_BY_MODIFIER_ID,
   EXPIRATION_DATE,
   START_DATE,
   REDEEMED_FLAG,
   ISSUED_DATE
   )
VALUES   (x_coupon_id,
   sysdate,
   fnd_global.user_id,
   sysdate,
   fnd_global.user_id,
   fnd_global.login_id,
   x_coupon_number,
   p_user_def_coupon_number,
   l_generated_coupon_number,
   p_issued_by_modifier_id,
   l_expiration_period_end_date,
   l_expiration_period_start_date,
   'N',
   nvl(p_pricing_effective_date,sysdate)
   );

Line: 261

     x_return_status_txt := 'QP_COUPON_PVT.Insert_Coupon: '||SQLERRM;

Line: 265

END Insert_Coupon;

Line: 306

   SELECT m.to_rltd_modifier_id list_line_id,
          l.list_header_id,
          l.qualification_ind
   INTO l_list_line_id, l_list_header_id,l_qualification_ind
   FROM qp_rltd_modifiers m, qp_list_lines l
   WHERE from_rltd_modifier_id = p_list_line_id
   AND m.to_rltd_modifier_id = l.list_line_id
   AND m.rltd_modifier_grp_type = QP_COUPON_PVT.G_COUPON_GRP_TYPE;

Line: 343

      INSERT INTO QP_QUALIFIERS (
		QUALIFIER_ID,
                CREATION_DATE,
                CREATED_BY,
                LAST_UPDATE_DATE,
                LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
                LIST_HEADER_ID,
                LIST_LINE_ID,
                COMPARISON_OPERATOR_CODE,
                QUALIFIER_CONTEXT,
		QUALIFIER_ATTRIBUTE,
                QUALIFIER_ATTR_VALUE,
                QUALIFIER_ATTR_VALUE_TO,
                QUALIFIER_GROUPING_NO,
                EXCLUDER_FLAG,
--changes made for bug 1755567
--included denormalised columns
		DISTINCT_ROW_COUNT,
		SEARCH_IND,
		HEADER_QUALS_EXIST_FLAG,
		QUALIFIER_GROUP_CNT,
		ACTIVE_FLAG,
		LIST_TYPE_CODE,
		QUAL_ATTR_VALUE_FROM_NUMBER,
		QUAL_ATTR_VALUE_TO_NUMBER,
                OTHERS_GROUP_CNT
     --ENH Upgrade BOAPI for orig_sys...ref RAVI
     ,ORIG_SYS_QUALIFIER_REF
     ,ORIG_SYS_LINE_REF
     ,ORIG_SYS_HEADER_REF
     )
                VALUES (
		QP_QUALIFIERS_S.nextval,
                sysdate,
                fnd_global.user_id,
                sysdate,
                fnd_global.user_id,
		fnd_global.login_id,
                l_list_header_id,
                l_list_line_id,
                '=',
                QP_PREQ_GRP.G_LIST_HEADER_CONTEXT,
                QP_COUPON_PVT.G_COUPON_QUALIFIER,
                to_char(p_coupon_id),
                NULL,
                qp_qualifier_group_no_s.nextval,
		'N',
		1,
		1,
		l_header_qual_exists_flag,
		(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
		l_active_flag,
		l_list_type_code,
		l_qual_attr_value_from_number,
		l_qual_attr_value_to_number,
                1
     --ENH Upgrade BOAPI for orig_sys...ref RAVI
     ,to_char(QP_QUALIFIERS_S.currval)
     ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_list_line_id)
     ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_list_header_id)
     );

Line: 406

	update_qual_ind(l_list_header_id,
			l_list_line_id,
			l_return_status,
			l_return_text);

Line: 416

        update qualification_ind because no there is a qualifier
        If set up form correctly creates dummy qualifier, this should never happen */
	   -- Not needed ... Also the old qualification indicators are no more applicable #1545351
     /* IF (l_qualification_ind = QP_PREQ_GRP.G_NO_QUAL_IND
        OR l_qualification_ind = QP_PREQ_GRP.G_NO_RLTD_QUAL_IND
        OR l_qualification_ind = QP_PREQ_GRP.G_NO_QUAL_PRIC_IND
        OR l_qualification_ind = QP_PREQ_GRP.G_BLIND_DISCOUNT_IND) THEN

        l_qualification_ind := l_qualification_ind-QP_PREQ_GRP.G_NO_QUAL_IND;

Line: 426

        update qp_list_lines set qualification_ind
            =decode(l_qualification_ind, 0, NULL, l_qualification_ind)
        where list_line_id = l_list_line_id;

Line: 472

    UPDATE QP_COUPONS
    SET redeemed_flag='Y'
    WHERE coupon_number = p_coupon_number;

Line: 493

   /* UPDATE qp_qualifiers
      SET end_date_active = p_pricing_effective_date
      WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
      AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
      AND nvl(comparison_operator_code,'=') = '='
      AND qualifier_attr_Value in
      (select coupon_id from qp_coupons where coupon_number=p_Coupon_number);*/

Line: 501

      UPDATE /*+ INDEX(qpq QP_QUALIFIERS_N4) */ qp_qualifiers qpq
      SET qpq.end_date_active = p_pricing_effective_date
      WHERE qpq.qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
      AND qpq.qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
      AND nvl(qpq.comparison_operator_code,'=') = '='
      AND qpq.qualifier_attr_Value in
      (select TO_CHAR(coupon_id) from qp_coupons where coupon_number=p_Coupon_number);

Line: 553

    UPDATE QP_COUPONS
    SET redeemed_flag='N'
    WHERE coupon_id = p_coupon_id;

Line: 567

    UPDATE qp_qualifiers
      SET end_date_active = NULL
      WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
      AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
      AND nvl(comparison_operator_code,'=') = '='
      AND qualifier_attr_Value=p_Coupon_ID;

Line: 575

    UPDATE /*+ INDEX(qpq QP_QUALIFIERS_N4) */ qp_qualifiers qpq
      SET qpq.end_date_active = NULL
      WHERE qpq.qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
      AND qpq.qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
      AND nvl(qpq.comparison_operator_code,'=') = '='
      AND qpq.qualifier_attr_Value=TO_CHAR(p_Coupon_ID);

Line: 604

    UPDATE QP_COUPONS
    SET redeemed_flag='N'
    WHERE coupon_number = p_coupon_number;

Line: 626

    UPDATE qp_qualifiers
      SET end_date_active = NULL
      WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
      AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
      AND nvl(comparison_operator_code,'=') = '='
      AND qualifier_attr_Value in
  (select coupon_id from qp_coupons where coupon_number=p_Coupon_Number);

Line: 635

   UPDATE /*+ INDEX(qpq QP_QUALIFIERS_N4) */ qp_qualifiers qpq
      SET qpq.end_date_active = NULL
      WHERE qpq.qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
      AND qpq.qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
      AND nvl(qpq.comparison_operator_code,'=') = '='
      AND qpq.qualifier_attr_Value in
        (select TO_CHAR(coupon_id) from qp_coupons where coupon_number=p_Coupon_Number);

Line: 683

  DELETE FROM QP_COUPONS
  WHERE redeemed_flag='Y'
  OR expiration_date < sysdate;

Line: 698

PROCEDURE Delete_Coupon(
  p_coupon_number                  IN VARCHAR2
,  x_return_status         OUT NOCOPY VARCHAR2
,  x_return_status_txt          OUT NOCOPY VARCHAR2
)
IS
BEGIN

  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 710

INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_CONTEXT,1
INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_ATTRIBUTE,2
INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,QUALIFIER_ATTR_VALUE,3
INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del1,QP_QUALIFIERS_N4,COMPARISON_OPERATOR_CODE,4
*/

/*
INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_sel1,QP_COUPONS_PK,COUPON_ID,1
*/
  DELETE FROM qp_qualifiers
  WHERE qualifier_context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
  AND qualifier_attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
  AND nvl(comparison_operator_code,'=') = '='
  AND qualifier_attr_Value in
      (select coupon_id from qp_coupons where coupon_number=p_coupon_number);

Line: 731

INDX,QP_COUPON_PVT.Delete_Coupon.Delete_Coupon_del2,-No Index Used-,NA,NA
*/
  DELETE FROM QP_COUPONS
  WHERE Coupon_number = p_Coupon_number;

Line: 758

     x_return_status_txt := 'QP_COUPON_PVT.Delete_Coupon '||SQLERRM;

Line: 762

END Delete_Coupon;

Line: 780

       SELECT  /*+ ORDERED USE_NL(b c) */
               a.line_index,
               b.line_detail_index,
               c.price_break_type_code,
               c.Expiration_Date,
               c.expiration_period_start_date,
               c.number_expiration_periods,
               c.expiration_period_uom expiration_period_uom_code,
               c.list_header_id,
               c.list_line_id,
               c.base_qty,
               c.base_uom_code,
               c.pricing_group_sequence,
               c.list_line_no,
               c.automatic_flag,
               c.print_on_invoice_flag,
               c.override_flag,
               c.pricing_phase_id,
               c.primary_uom_flag,
               c.product_precedence,
               b.created_from_list_type_code,
               a.pricing_effective_date,
			b.line_detail_type_code,
               b.incompatability_grp_code,
               b.process_code,
               b.applied_flag,
               b.modifier_level_code
	FROM	qp_npreq_lines_tmp a ,
                qp_npreq_ldets_tmp b ,
                QP_LIST_LINES c
	WHERE   a.LINE_INDEX = b.LINE_INDEX
	AND	b.LINE_DETAIL_INDEX = p_line_detail_index
	AND	b.CREATED_FROM_LIST_LINE_ID = c.LIST_LINE_ID
        AND     b.CREATED_FROM_LIST_LINE_TYPE
          = QP_COUPON_PVT.G_COUPON_ISSUE_LINE_TYPE
        AND     b.PRICING_PHASE_ID = p_pricing_phase_id
        AND     b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;

Line: 873

            Insert_Coupon(i.list_line_id,
                  i.expiration_period_start_date,
                  i.expiration_date,
                  i.number_expiration_periods,
                  i.expiration_period_uom_code,
                  i.list_line_no,
                  i.pricing_effective_date,
                  l_coupon_id,
                  l_coupon_number,
                  l_return_status,
                  x_return_status_txt
                 );

Line: 919

         UPDATE qp_npreq_ldets_tmp
         SET PROCESSED_FLAG = 'Y',
             LIST_LINE_NO = l_coupon_number,
             PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW,
             PRICING_STATUS_TEXT = 'Coupon_Issue'
         WHERE LINE_INDEX = i.LINE_INDEX
         AND LINE_DETAIL_INDEX = i.LINE_DETAIL_INDEX;

Line: 938

        INSERT INTO qp_npreq_ldets_tmp(
              LINE_DETAIL_INDEX,
              LINE_DETAIL_TYPE_CODE,
              LINE_INDEX,
              CREATED_FROM_LIST_HEADER_ID,
	      CREATED_FROM_LIST_LINE_ID,
              CREATED_FROM_LIST_LINE_TYPE,
              CREATED_FROM_LIST_TYPE_CODE,
              PRICING_GROUP_SEQUENCE,
              PROCESSED_FLAG,
              AUTOMATIC_FLAG,
              PRINT_ON_INVOICE_FLAG,
              OVERRIDE_FLAG,
              PRICING_PHASE_ID,
              PRIMARY_UOM_FLAG,
              PRODUCT_PRECEDENCE,
              LIST_LINE_NO,
              INCOMPATABILITY_GRP_CODE,
              PROCESS_CODE,
              APPLIED_FLAG,
              MODIFIER_LEVEL_CODE,
              PRICING_STATUS_CODE,
              PRICING_STATUS_TEXT)
	      VALUES (l_line_detail_index,
               i.line_detail_type_code,
               i.line_index,
               i.LIST_HEADER_ID,
               i.LIST_LINE_ID,
	       G_COUPON_ISSUE_LINE_TYPE,
               i.CREATED_FROM_LIST_TYPE_CODE,
               i.PRICING_GROUP_SEQUENCE,
               'Y',
               i.AUTOMATIC_FLAG,
               i.PRINT_ON_INVOICE_FLAG,
               i.OVERRIDE_FLAG,
               i.PRICING_PHASE_ID,
               i.PRIMARY_UOM_FLAG,
               i.PRODUCT_PRECEDENCE,
               l_coupon_number,
               i.incompatability_grp_code,
               i.process_code,
               i.applied_flag,
               i.modifier_level_code,
               QP_PREQ_GRP.G_STATUS_NEW,
               'Coupon Issue');

Line: 1029

SELECT /*+ ordered index(qplat) index( qpl)  index( qpd)*/ DISTINCT qpc.coupon_number,  --5658579
  qpl.pricing_effective_date
  FROM qp_npreq_line_attrs_tmp qplat,
       qp_npreq_ldets_tmp qpd,
       qp_npreq_lines_tmp qpl,
       qp_coupons qpc
  WHERE qplat.line_index = qpd.line_index
  and qpl.line_index = qpd.line_index
  and qplat.line_detail_index = qpd.line_detail_index
  and qplat.attribute_type= QP_PREQ_GRP.G_QUALIFIER_TYPE
  and qplat.context= QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
  and qplat.attribute=QP_COUPON_PVT.G_COUPON_QUALIFIER
  and nvl(qplat.comparison_operator_type_code,'=') = '='
  and qplat.attribute_level = QP_PREQ_GRP.G_LINE_LEVEL
  and qpd.created_from_list_line_type <> QP_COUPON_PVT.G_COUPON_ISSUE_LINE_TYPE
  and qpd.APPLIED_FLAG = QP_PREQ_GRP.G_YES
  and qpd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
  and qpc.coupon_id = to_number(qplat.value_from);

Line: 1049

  SELECT DISTINCT coupon_number,
  qpl.pricing_effective_date
  FROM qp_npreq_line_attrs_tmp qplat,
       qp_npreq_ldets_tmp qpd,
       qp_npreq_lines_tmp qpl,
       qp_qualifiers qpq,
       qp_coupons qpc
  WHERE qplat.line_index = qpl.line_index
  and qplat.line_detail_index is null
  and qplat.attribute_type = QP_PREQ_GRP.G_QUALIFIER_TYPE
  and qplat.context = QP_PREQ_GRP.G_LIST_HEADER_CONTEXT
  and qplat.attribute = QP_COUPON_PVT.G_COUPON_QUALIFIER
  and nvl(qplat.comparison_operator_type_code,'=') = '='
  and qpq.qualifier_context = qplat.context
  and qpq.qualifier_attribute = qplat.attribute
  and qpq.QUALIFIER_ATTR_VALUE = qplat.value_from
  and qpc.coupon_id = to_number(qplat.value_from)
  and qpq.list_line_id = qpd.CREATED_FROM_LIST_LINE_ID
  and qpd.APPLIED_FLAG = QP_PREQ_PUB.G_YES
  and qpd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;

Line: 1169

       SELECT sysdate
       INTO l_pricing_effective_date
       FROM DUAL;

Line: 1174

    SELECT
      decode(p_expiration_period_uom_code,
       'YR',  add_months(nvl(p_expiration_period_start_date,l_pricing_effective_date),
                     12*p_number_expiration_periods),
       'MTH', add_months(nvl(p_expiration_period_start_date,l_pricing_effective_date),
                     p_number_expiration_periods),
       'WK', nvl(p_expiration_period_start_date,l_pricing_effective_date)
                     + 7 * p_number_expiration_periods,
       'HR', nvl(p_expiration_period_start_date,l_pricing_effective_date)
                     + p_number_expiration_periods/24,
       'MIN', nvl(p_expiration_period_start_date,l_pricing_effective_date)
                     + p_number_expiration_periods/1440,
        nvl(p_expiration_period_start_date,l_pricing_effective_date)
                     + p_number_expiration_periods)
       INTO p_expiration_period_end_date
       FROM dual;