APPS.PV_OFFER_PUB SQL Statements

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

Line: 131

   IF (p_operation NOT IN ('UPDATE', 'CREATE')) THEN
      Debug('Wrong p_operation type: ' || p_operation);

Line: 133

      Debug('p_operation must be either ''UPDATE'' or ''CREATE''');

Line: 167

   IF (p_operation = 'UPDATE' AND p_offer_id IS NULL) THEN
      Debug('Offer ID must be provided when the operation is UPDATE.');

Line: 179

   IF (p_operation = 'UPDATE') THEN
      -- ----------------------------------------------------------
      -- l_del_modifier_list_rec
      -- ----------------------------------------------------------
      FOR x IN (SELECT qp_list_header_id
                FROM   ozf_offers
                WHERE  offer_id = p_offer_id)
      LOOP
         l_qp_list_header_id                        := x.qp_list_header_id;

Line: 189

         l_del_modifier_list_rec.offer_operation    := 'DELETE';

Line: 197

      FOR x IN (SELECT a.offer_discount_line_id,
                       a.object_version_number,
                       b.off_discount_product_id
                FROM   ozf_offer_discount_lines  a,
                       ozf_discount_product_reln b
                WHERE  a.offer_discount_line_id = b.offer_discount_line_id AND
                       a.offer_id               = p_offer_id)
      LOOP
         l_del_discount_tbl(i).offer_discount_line_id  := x.offer_discount_line_id;

Line: 206

         l_del_discount_tbl(i).operation               := 'DELETE';

Line: 216

      FOR x IN (SELECT qualifier_id, object_version_number
                FROM   ozf_offer_qualifiers
                WHERE  offer_id = p_offer_id)
      LOOP
         l_del_na_qualifier_tbl(i).Qualifier_id          := x.qualifier_id;

Line: 221

         l_del_na_qualifier_tbl(i).operation             := 'DELETE';

Line: 231

	  -- If benefit failed validation and the user tries to re-activate it, delete
	  -- from ozf budget requests only records that are not in APPROVED status. when
	  -- recreating the offer, add only records from PV that are not in APPROVED status.
	  -- for bug# 3738487


	  l_budget_id_table  := JTF_NUMBER_TABLE();

Line: 241

      FOR x IN (SELECT activity_budget_id, status_code, budget_source_id
                FROM   ozf_act_budgets
                WHERE  arc_act_budget_used_by = 'OFFR' AND
                       act_budget_used_by_id  = l_qp_list_header_id)
      LOOP
         if(x.status_code ='APPROVED' ) then
			l_budget_id_table.extend();

Line: 252

			l_del_budget_tbl(i).operation     := 'DELETE';

Line: 320

   ELSIF (p_operation = 'UPDATE') THEN
      Debug('l_qp_list_header_id: ' || l_qp_list_header_id);

Line: 325

      l_modifier_list_rec.offer_operation    := 'UPDATE';

Line: 332

   IF (p_operation = 'UPDATE') THEN
		FOR i IN 1..p_budget_tbl.COUNT LOOP
			budget_id_found := 'N';

Line: 452

   FOR x IN (SELECT offer_id
             FROM   ozf_offers
             WHERE  qp_list_header_id = l_qp_list_header_id)
   LOOP
      x_offer_id := x.offer_id;

Line: 463

         UPDATE pv_ge_benefits_b
         SET    additional_info_1   = x_offer_id,
                benefit_status_code = 'PENDING'
         WHERE  benefit_id          = p_benefit_id;

Line: 469

         UPDATE pv_ge_benefits_b
         SET    benefit_status_code = 'PENDING'
         WHERE  benefit_id          = p_benefit_id;

Line: 481

   Debug('Update the offer to ACTIVE status..........................');

Line: 484

   l_modifier_list_rec.offer_operation    := 'UPDATE';

Line: 485

   l_modifier_list_rec.modifier_operation := 'UPDATE';