APPS.OZF_VOLUME_CALCULATION_PUB SQL Statements

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

Line: 40

  SELECT precedence, offer_market_option_id
  FROM   ozf_offr_market_options
  WHERE  group_number = p_group_no
  AND    qp_list_header_id = p_list_header_id;

Line: 46

  SELECT combine_schedule_flag, volume_tracking_level_code
  FROM   ozf_offr_market_options
  WHERE  offer_id = p_offer_id
  AND    offer_market_option_id = p_offer_market_option_id;

Line: 52

  SELECT offer_discount_line_id
  FROM   ozf_qp_discounts
  WHERE  list_line_id = p_list_line_id;

Line: 57

  SELECT group_no, volume_track_type, combine_schedule_yn, pbh_line_id, prod_attribute, prod_attr_value
  FROM   ozf_order_group_prod
  WHERE  order_line_id = l_order_line_id
  AND    indirect_flag = p_indirect_flag
  AND    offer_id      = p_offer_id;

Line: 91

     select split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id =  p_order_line_id;

Line: 148

PROCEDURE insert_volume(
   p_init_msg_list     IN  VARCHAR2
  ,p_api_version       IN  NUMBER
  ,p_commit            IN  VARCHAR2
  ,x_return_status     OUT NOCOPY VARCHAR2
  ,x_msg_count         OUT NOCOPY NUMBER
  ,x_msg_data          OUT NOCOPY VARCHAR2
  ,p_volume_detail_rec IN  ozf_sales_transactions_pvt.sales_transaction_rec_type
  ,p_qp_list_header_id IN  NUMBER
  ,p_offer_id          IN  NUMBER
  ,p_indirect_flag     IN  VARCHAR2
  ,p_sign              IN  NUMBER)
IS
  CURSOR c_group_prod(p_offer_id NUMBER, p_line_id NUMBER, p_indirect_flag VARCHAR2) IS
  SELECT group_no, volume_track_type, combine_schedule_yn, pbh_line_id, volume_type, include_volume_flag
  FROM   ozf_order_group_prod
  WHERE  offer_id = p_offer_id
  AND    order_line_id = p_line_id
  AND    indirect_flag = p_indirect_flag;

Line: 169

  SELECT 'Y'
  FROM   ozf_volume_summary
  WHERE  offer_id = p_offer_id
  AND    group_no = p_group_no
  AND    pbh_line_id = p_pbh_lind_id;

Line: 176

  SELECT 'Y'
  FROM   ozf_volume_summary
  WHERE  offer_id = p_offer_id
  AND    individual_type = p_individual_type
  AND    individual_id = p_individual_id
  AND    pbh_line_id = p_pbh_line_id;

Line: 184

  SELECT offer_discount_line_id
  FROM   ozf_offer_discount_lines
  WHERE  offer_id = p_offer_id
  AND    tier_type = 'PBH';

Line: 190

  SELECT 'Y'
  FROM   ozf_volume_detail
  WHERE  offer_id = p_offer_id
  AND    source_code = p_source_code
  AND    order_line_id = p_line_id;

Line: 197

  SELECT NVL(transaction_currency_code, fund_request_curr_code)
  FROM   ozf_offers
  WHERE  offer_id = p_offer_id;

Line: 202

  SELECT reference_header_id, reference_line_id, line_category_code, return_context, return_attribute1, return_attribute2
  FROM   oe_order_lines_all
  WHERE  line_id = p_line_id;

Line: 208

  SELECT offer_id, qp_list_header_id, group_no, volume_track_type, combine_schedule_yn, pbh_line_id, volume_type, prod_attribute, prod_attr_value, apply_discount_flag, include_volume_flag, indirect_flag
  FROM   ozf_order_group_prod
  WHERE  offer_id = p_offer_id
  AND    order_line_id = p_line_id
  AND    indirect_flag = p_indirect_flag;

Line: 217

   SELECT exchange_rate_type
   FROM   ozf_sys_parameters_all
   WHERE  org_id = p_org_id;

Line: 231

  l_api_name            CONSTANT VARCHAR2(30) := 'insert_volume';

Line: 240

  ozf_utility_pvt.write_conc_log('========================= Insert Volume =========================');

Line: 259

        INSERT INTO ozf_order_group_prod
        (
           order_group_prod_id
          ,creation_date
          ,created_by
          ,last_update_date
          ,last_updated_by
          ,last_update_login
          ,order_line_id
          ,offer_id
          ,qp_list_header_id
          ,group_no
          ,volume_track_type
          ,combine_schedule_yn
          ,pbh_line_id
          ,volume_type
          ,prod_attribute
          ,prod_attr_value
          ,apply_discount_flag
          ,include_volume_flag
          ,indirect_flag
        )
        VALUES
        (  ozf_order_group_prod_s.NEXTVAL
          ,SYSDATE
          ,FND_GLOBAL.user_id
          ,SYSDATE
          ,FND_GLOBAL.user_id
          ,FND_GLOBAL.conc_login_id
          ,p_volume_detail_rec.line_id
          ,l_rma_ref_line_detail.offer_id
          ,l_rma_ref_line_detail.qp_list_header_id
          ,l_rma_ref_line_detail.group_no
          ,l_rma_ref_line_detail.volume_track_type
          ,l_rma_ref_line_detail.combine_schedule_yn
          ,l_rma_ref_line_detail.pbh_line_id
          ,l_rma_ref_line_detail.volume_type
          ,l_rma_ref_line_detail.prod_attribute
          ,l_rma_ref_line_detail.prod_attr_value
          ,l_rma_ref_line_detail.apply_discount_flag
          ,l_rma_ref_line_detail.include_volume_flag
          ,l_rma_ref_line_detail.indirect_flag
        );

Line: 313

       select split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id =  p_volume_detail_rec.line_id;

Line: 379

    INSERT INTO ozf_volume_detail
    (
       volume_detail_id
      ,creation_date
      ,created_by
      ,last_update_date
      ,last_updated_by
      ,last_update_login
      ,offer_id
      ,qp_list_header_id
      ,distributor_acct_id
      ,cust_account_id
      ,bill_to_site_use_id
      ,ship_to_site_use_id
      ,inventory_item_id
      ,volume_type
      ,uom_code
      ,currency_code
      ,volume
      ,group_no
      ,volume_track_type
      ,order_line_id
      ,transaction_date
      ,pbh_line_id
      ,include_volume_flag
      ,source_code
    )
    VALUES
    (
       ozf_volume_detail_s.NEXTVAL
      ,SYSDATE
      ,FND_GLOBAL.user_id
      ,SYSDATE
      ,FND_GLOBAL.user_id
      ,FND_GLOBAL.conc_login_id
      ,p_offer_id
      ,p_qp_list_header_id
      ,p_volume_detail_rec.sold_from_cust_account_id
      ,p_volume_detail_rec.sold_to_cust_account_id
      ,p_volume_detail_rec.bill_to_site_use_id
      ,p_volume_detail_rec.ship_to_site_use_id
      ,p_volume_detail_rec.inventory_item_id
      ,l_volume_type
      ,p_volume_detail_rec.uom_code
      ,l_currency_code
      ,l_current_volume
      ,l_group_no
      ,l_volume_track_type
      ,p_volume_detail_rec.line_id
      ,p_volume_detail_rec.transaction_date
      ,l_pbh_line_id
      ,l_include_volume
      ,p_volume_detail_rec.source_code
    );

Line: 442

      IF l_volume_exists = 'Y' THEN -- update group's volume
        IF l_combine_schedule_yn = 'Y' THEN -- update all pbh lines
          UPDATE ozf_volume_summary
          SET    group_volume = group_volume + l_current_volume,
                 last_update_date = SYSDATE,
                 last_updated_by = FND_GLOBAL.user_id,
                 last_update_login = FND_GLOBAL.conc_login_id
          WHERE  offer_id = p_offer_id
          AND    group_no = l_group_no;

Line: 451

        ELSE -- update one pbh line only
          UPDATE ozf_volume_summary
          SET    group_volume = group_volume + l_current_volume,
                 last_update_date = SYSDATE,
                 last_updated_by = FND_GLOBAL.user_id,
                 last_update_login = FND_GLOBAL.conc_login_id
          WHERE  offer_id = p_offer_id
          AND    group_no = l_group_no
          AND    pbh_line_id = l_pbh_line_id;

Line: 461

      ELSE -- insert group's volume
        IF l_combine_schedule_yn = 'Y' THEN -- insert all pbh lines
          FOR l_pbh_line IN c_pbh_lines(p_offer_id) LOOP
            INSERT INTO ozf_volume_summary
            (
               volume_summary_id
              ,creation_date
              ,created_by
              ,last_update_date
              ,last_updated_by
              ,last_update_login
              ,offer_id
              ,qp_list_header_id
              ,group_no
              ,group_volume
              ,pbh_line_id
            )
            VALUES
            (
               ozf_volume_summary_s.NEXTVAL
              ,SYSDATE
              ,FND_GLOBAL.user_id
              ,SYSDATE
              ,FND_GLOBAL.user_id
              ,FND_GLOBAL.conc_login_id
              ,p_offer_id
              ,p_qp_list_header_id
              ,l_group_no
              ,l_current_volume
              ,l_pbh_line.offer_discount_line_id
            );

Line: 493

        ELSE -- insert one pbh line
          INSERT INTO ozf_volume_summary
          (
             volume_summary_id
            ,creation_date
            ,created_by
            ,last_update_date
            ,last_updated_by
            ,last_update_login
            ,offer_id
            ,qp_list_header_id
            ,group_no
            ,group_volume
            ,pbh_line_id
          )
          VALUES
          (
             ozf_volume_summary_s.NEXTVAL
            ,SYSDATE
            ,FND_GLOBAL.user_id
            ,SYSDATE
            ,FND_GLOBAL.user_id
            ,FND_GLOBAL.conc_login_id
            ,p_offer_id
            ,p_qp_list_header_id
            ,l_group_no
            ,l_current_volume
            ,l_pbh_line_id
          );

Line: 534

    IF l_volume_exists = 'Y' THEN -- update distributor's volume
      UPDATE ozf_volume_summary
      SET    individual_volume = individual_volume + l_current_volume,
             last_update_date = SYSDATE,
             last_updated_by = FND_GLOBAL.user_id,
             last_update_login = FND_GLOBAL.conc_login_id
      WHERE  offer_id = p_offer_id
      AND    individual_type = 'DISTRIBUTOR'
      AND    individual_id = p_volume_detail_rec.sold_from_cust_account_id
      AND    pbh_line_id = l_pbh_line_id;

Line: 544

    ELSE -- insert distributor's volume
      INSERT INTO ozf_volume_summary
      (
         volume_summary_id
        ,creation_date
        ,created_by
        ,last_update_date
        ,last_updated_by
        ,last_update_login
        ,offer_id
        ,qp_list_header_id
        ,individual_type
        ,individual_id
        ,individual_volume
        ,pbh_line_id
      )
      VALUES
      (
         ozf_volume_summary_s.NEXTVAL
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,FND_GLOBAL.conc_login_id
        ,p_offer_id
        ,p_qp_list_header_id
        ,'DISTRIBUTOR'
        ,p_volume_detail_rec.sold_from_cust_account_id
        ,l_current_volume
        ,l_pbh_line_id
      );

Line: 584

    IF l_volume_exists = 'Y' THEN -- update customer's volume
      UPDATE ozf_volume_summary
      SET    individual_volume = individual_volume + l_current_volume,
             last_update_date = SYSDATE,
             last_updated_by = FND_GLOBAL.user_id,
             last_update_login = FND_GLOBAL.conc_login_id
      WHERE  offer_id = p_offer_id
      AND    individual_type = 'ACCOUNT'
      AND    individual_id = p_volume_detail_rec.sold_to_cust_account_id
      AND    pbh_line_id = l_pbh_line_id;

Line: 594

    ELSE -- insert customer's volume
      INSERT INTO ozf_volume_summary
      (
         volume_summary_id
        ,creation_date
        ,created_by
        ,last_update_date
        ,last_updated_by
        ,last_update_login
        ,offer_id
        ,qp_list_header_id
        ,individual_type
        ,individual_id
        ,individual_volume
        ,pbh_line_id
      )
      VALUES
      (
         ozf_volume_summary_s.NEXTVAL
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,FND_GLOBAL.conc_login_id
        ,p_offer_id
        ,p_qp_list_header_id
        ,'ACCOUNT'
        ,p_volume_detail_rec.sold_to_cust_account_id
        ,l_current_volume
        ,l_pbh_line_id
      );

Line: 633

    IF l_volume_exists = 'Y' THEN -- update bill_to's volume
      UPDATE ozf_volume_summary
      SET    individual_volume = individual_volume + l_current_volume,
             last_update_date = SYSDATE,
             last_updated_by = FND_GLOBAL.user_id,
             last_update_login = FND_GLOBAL.conc_login_id
      WHERE  offer_id = p_offer_id
      AND    individual_type = 'BILL_TO'
      AND    individual_id = p_volume_detail_rec.bill_to_site_use_id
      AND    pbh_line_id = l_pbh_line_id;

Line: 643

    ELSE -- insert bill_to's volume
      INSERT INTO ozf_volume_summary
      (
         volume_summary_id
        ,creation_date
        ,created_by
        ,last_update_date
        ,last_updated_by
        ,last_update_login
        ,offer_id
        ,qp_list_header_id
        ,individual_type
        ,individual_id
        ,individual_volume
        ,pbh_line_id
      )
      VALUES
      (
         ozf_volume_summary_s.NEXTVAL
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,FND_GLOBAL.conc_login_id
        ,p_offer_id
        ,p_qp_list_header_id
        ,'BILL_TO'
        ,p_volume_detail_rec.bill_to_site_use_id
        ,l_current_volume
        ,l_pbh_line_id
      );

Line: 682

    IF l_volume_exists = 'Y' THEN -- update ship_to's volume
      UPDATE ozf_volume_summary
      SET    individual_volume = individual_volume + l_current_volume,
             last_update_date = SYSDATE,
             last_updated_by = FND_GLOBAL.user_id,
             last_update_login = FND_GLOBAL.conc_login_id
      WHERE  offer_id = p_offer_id
      AND    individual_type = 'SHIP_TO'
      AND    individual_id = p_volume_detail_rec.ship_to_site_use_id
      AND    pbh_line_id = l_pbh_line_id;

Line: 692

    ELSE -- insert ship_to's volume
      INSERT INTO ozf_volume_summary
      (
         volume_summary_id
        ,creation_date
        ,created_by
        ,last_update_date
        ,last_updated_by
        ,last_update_login
        ,offer_id
        ,qp_list_header_id
        ,individual_type
        ,individual_id
        ,individual_volume
        ,pbh_line_id
      )
      VALUES
      (
         ozf_volume_summary_s.NEXTVAL
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,SYSDATE
        ,FND_GLOBAL.user_id
        ,FND_GLOBAL.conc_login_id
        ,p_offer_id
        ,p_qp_list_header_id
        ,'SHIP_TO'
        ,p_volume_detail_rec.ship_to_site_use_id
        ,l_current_volume
        ,l_pbh_line_id
      );

Line: 731

     ROLLBACK TO insert_volume;

Line: 741

END insert_volume;

Line: 758

  SELECT off.offer_id, off.qp_list_header_id
  FROM   ozf_offers off, ozf_funds_utilized_all_b utl
  WHERE  off.qp_list_header_id = utl.plan_id
  AND    off.offer_type = 'VOLUME_OFFER'
  AND    utl.plan_type = 'OFFR'
  AND    utl.order_line_id = p_order_line_id
 --AND    ((off.volume_offer_type = 'ACCRUAL' AND utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL'))
 -- Need to consider the backdated adjustment created for booked orders
 --changed for bug 6021635
  AND    ((off.volume_offer_type = 'ACCRUAL' AND (utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL') or (utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL','ADJUSTMENT')
  AND utl.price_adjustment_id=-1)))
          OR (off.volume_offer_type = 'OFF_INVOICE' AND utl.utilization_type = 'UTILIZED'))
--  AND    utl.utilization_type = DECODE(off.volume_offer_type, 'ACCRUAL', 'ACCRUAL', 'OFF_INVOICE', 'UTILIZED')
  AND    utl.object_type = p_object_type;

Line: 774

  SELECT off.offer_id, off.qp_list_header_id
  FROM   ozf_offers off, ozf_funds_utilized_all_b utl
  WHERE  off.qp_list_header_id = utl.plan_id
  AND    off.offer_type = 'VOLUME_OFFER'
  AND    utl.plan_type = 'OFFR'
  AND    utl.object_id = p_order_line_id
  AND    ((off.volume_offer_type = 'ACCRUAL' AND utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL'))
          OR (off.volume_offer_type = 'OFF_INVOICE' AND utl.utilization_type = 'UTILIZED'))
--  AND    utl.utilization_type = DECODE(off.volume_offer_type, 'ACCRUAL', 'ACCRUAL', 'OFF_INVOICE', 'UTILIZED')
  AND    utl.object_type = p_object_type;

Line: 786

  SELECT offer_id, qp_list_header_id
  FROM   ozf_offers
  WHERE  offer_type = 'VOLUME_OFFER'
  AND    qp_list_header_id = p_qp_list_header_id;

Line: 825

        insert_volume(
          p_init_msg_list     => p_init_msg_list
         ,p_api_version       => p_api_version
         ,p_commit            => p_commit
         ,x_return_status     => x_return_status
         ,x_msg_count         => x_msg_count
         ,x_msg_data          => x_msg_data
         ,p_volume_detail_rec => p_volume_detail_rec
         ,p_qp_list_header_id => l_offer_id_om.qp_list_header_id
         ,p_offer_id          => l_offer_id_om.offer_id
         ,p_indirect_flag     => l_indirect_flag
         ,p_sign              => l_sign);

Line: 844

      insert_volume(
        p_init_msg_list     => p_init_msg_list
       ,p_api_version       => p_api_version
       ,p_commit            => p_commit
       ,x_return_status     => x_return_status
       ,x_msg_count         => x_msg_count
       ,x_msg_data          => x_msg_data
       ,p_volume_detail_rec => p_volume_detail_rec
       ,p_qp_list_header_id => l_qp_list_header_id
       ,p_offer_id          => l_offer_id
       ,p_indirect_flag     => l_indirect_flag
       ,p_sign              => l_sign);

Line: 870

        insert_volume(
          p_init_msg_list     => p_init_msg_list
         ,p_api_version       => p_api_version
         ,p_commit            => p_commit
         ,x_return_status     => x_return_status
         ,x_msg_count         => x_msg_count
         ,x_msg_data          => x_msg_data
         ,p_volume_detail_rec => p_volume_detail_rec
         ,p_qp_list_header_id => l_offer_id_is.qp_list_header_id
         ,p_offer_id          => l_offer_id_is.offer_id
         ,p_indirect_flag     => l_indirect_flag
         ,p_sign              => l_sign);

Line: 889

      insert_volume(
        p_init_msg_list     => p_init_msg_list
       ,p_api_version       => p_api_version
       ,p_commit            => p_commit
       ,x_return_status     => x_return_status
       ,x_msg_count         => x_msg_count
       ,x_msg_data          => x_msg_data
       ,p_volume_detail_rec => p_volume_detail_rec
       ,p_qp_list_header_id => l_qp_list_header_id
       ,p_offer_id          => l_offer_id
       ,p_indirect_flag     => l_indirect_flag
       ,p_sign              => l_sign);

Line: 940

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    group_no = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date < p_transaction_date;

Line: 949

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    distributor_acct_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date < p_transaction_date;

Line: 958

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    cust_account_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date < p_transaction_date;

Line: 967

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    bill_to_site_use_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date < p_transaction_date;

Line: 976

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    ship_to_site_use_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date < p_transaction_date;

Line: 985

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    group_no = p_volume_track_id
  AND    transaction_date < p_transaction_date;

Line: 993

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    distributor_acct_id = p_volume_track_id
  AND    transaction_date < p_transaction_date;

Line: 1001

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    cust_account_id = p_volume_track_id
  AND    transaction_date < p_transaction_date;

Line: 1009

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    bill_to_site_use_id = p_volume_track_id
  AND    transaction_date < p_transaction_date;

Line: 1017

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    ship_to_site_use_id = p_volume_track_id
  AND    transaction_date < p_transaction_date;

Line: 1025

  SELECT volume_detail_id
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    source_code = p_source_code
  AND    order_line_id = p_order_line_id;

Line: 1036

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    group_no = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1046

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    distributor_acct_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1056

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    cust_account_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1075

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    cust_account_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    trunc(transaction_date) = trunc(p_transaction_date)
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1085

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    bill_to_site_use_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1095

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    ship_to_site_use_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1105

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    group_no = p_volume_track_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1114

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    distributor_acct_id = p_volume_track_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1123

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    cust_account_id = p_volume_track_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1132

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    bill_to_site_use_id = p_volume_track_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1141

  SELECT NVL(SUM(volume), 0)
  FROM   ozf_volume_detail
  WHERE  include_volume_flag = 'Y'
  AND    offer_id = p_offer_id
  AND    ship_to_site_use_id = p_volume_track_id
  AND    transaction_date = p_transaction_date
  AND    volume_detail_id <= p_volume_detail_id;

Line: 1150

  SELECT volume_offer_type
  FROM   ozf_offers
  WHERE  offer_id = p_offer_id;

Line: 1288

  SELECT group_volume
  FROM   ozf_volume_summary
  WHERE  offer_id = p_offer_id
  AND    group_no = p_group_no
  AND    pbh_line_id = p_pbh_line_id;

Line: 1295

  SELECT individual_volume
  FROM   ozf_volume_summary
  WHERE  offer_id = p_offer_id
  AND    individual_type = p_vol_track_type
  AND    individual_id = p_volume_track_id
  AND    pbh_line_id = p_pbh_line_id;

Line: 1303

  SELECT SUM(individual_volume)
  FROM   ozf_volume_summary
  WHERE  offer_id = p_offer_id
  AND    individual_type = p_vol_track_type
  AND    individual_id = p_volume_track_id;

Line: 1369

  SELECT billto_cust_account_id, bill_to_site_use_id, ship_to_site_use_id
  FROM   ozf_funds_utilized_all_b
  WHERE  (p_source_code = 'OM' AND object_type = 'ORDER' AND order_line_id = p_order_line_id)
  OR     (p_source_code = 'IS' AND object_type = 'TP_ORDER' AND object_id = p_order_line_id);

Line: 1375

  SELECT sold_from_cust_account_id
  FROM   ozf_resale_lines_all
  WHERE  resale_line_id = p_order_line_id;

Line: 1380

  SELECT offer_id, combine_schedule_yn, apply_discount_flag, group_no, volume_track_type, pbh_line_id
  FROM   ozf_order_group_prod
  WHERE  qp_list_header_id = l_qp_list_header_id
  AND    order_line_id = l_order_line_id
  AND    indirect_flag = DECODE(p_source_code, 'OM', 'O', 'IS', 'R');

Line: 1387

  SELECT a.volume_from
  FROM   ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
  WHERE  a.offer_discount_line_id = b.dis_offer_discount_id
  AND    b. pbh_offer_discount_id = p_pbh_line_id
  AND    b.offer_market_option_id = c.offer_market_option_id
  AND    c.offer_id = p_offer_id
  AND    c.group_number = p_group_no;

Line: 1432

     SELECT split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id =  p_order_line_id;

Line: 1519

  SELECT o.qp_list_header_id, o.offer_id
  FROM   ozf_offers o, qp_list_lines q
  WHERE  o.qp_list_header_id = q.list_header_id
  AND    q.list_line_id = p_list_line_id;

Line: 1525

  SELECT unit_selling_price, pricing_quantity, sold_to_org_id, ship_to_org_id, invoice_to_org_id, actual_shipment_date
  FROM   oe_order_lines_all
  WHERE  line_id = p_order_line_id;

Line: 1530

  SELECT quantity, amount, sold_from_cust_account_id, sold_to_cust_account_id, ship_to_site_use_id, bill_to_site_use_id, transaction_date
  FROM   ozf_sales_transactions
  WHERE  line_id = p_order_line_id;

Line: 1535

  SELECT quantity, quantity * selling_price, sold_from_cust_account_id, bill_to_cust_account_id, ship_to_site_use_id, bill_to_site_use_id, date_ordered
  FROM   ozf_resale_lines_int_all
  WHERE  resale_line_int_id = p_order_line_id;

Line: 1540

  SELECT apply_discount_flag, include_volume_flag
  FROM   ozf_offer_discount_products
  WHERE  product_context = 'ITEM'
  AND    product_attribute = p_prod_attribute
  AND    product_attr_value = p_prod_attr_value
  AND    offer_id = p_offer_id;

Line: 1548

  SELECT order_group_prod_id
  FROM   ozf_order_group_prod
  WHERE  order_line_id = p_line_id
  AND    offer_id = p_offer_id
  AND    indirect_flag = p_indirect_flag;

Line: 1555

  SELECT volume_type
  FROM   ozf_offer_discount_lines
  WHERE  offer_discount_line_id = p_pbh_line_id;

Line: 1560

  SELECT a.volume_from
  FROM   ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
  WHERE  a.offer_discount_line_id = b.dis_offer_discount_id
  AND    b. pbh_offer_discount_id = p_pbh_line_id
  AND    b.offer_market_option_id = c.offer_market_option_id
  AND    c.offer_id = p_offer_id
  AND    c.group_number = p_group_no;

Line: 1599

  INSERT INTO om_qp_temp(id, rec_req_type, access_date, line_index)
  VALUES(om_qp_temp_s.nextval, 'START', SYSDATE, -9999);

Line: 1604

  INSERT INTO om_qp_temp(
    id,
    rec_context,
    rec_attr,
    access_date,
    line_index,
    group_num,
    order_line_id,
    list_line_id)
  VALUES(om_qp_temp_s.nextval,
    p_accum_rec.context,
    p_accum_rec.attribute,
    sysdate,
    0,
    l_rec_count,
    p_order_line_id,
    p_list_line_id);

Line: 1623

    INSERT INTO om_qp_temp(
      id,
      access_date,
      line_index,
      attr_type,
      context,
      attr,
      attr_value,
      group_num,
      order_line_id,
      list_line_id)
    VALUES(
      om_qp_temp_s.nextval,
      sysdate,
      p_req_line_attrs_tbl(i).line_index,
      p_req_line_attrs_tbl(i).attribute_type,
      p_req_line_attrs_tbl(i).context,
      p_req_line_attrs_tbl(i).attribute,
      p_req_line_attrs_tbl(i).value,
      p_req_line_attrs_tbl(i).grouping_no,
      p_order_line_id,
      p_list_line_id);

Line: 1687

     select split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id =  p_order_line_id;

Line: 1709

    INSERT INTO ozf_order_group_prod
    (
       order_group_prod_id
      ,creation_date
      ,created_by
      ,last_update_date
      ,last_updated_by
      ,last_update_login
      ,order_line_id
      ,offer_id
      ,qp_list_header_id
      ,group_no
      ,volume_track_type
      ,combine_schedule_yn
      ,pbh_line_id
      ,volume_type
      ,prod_attribute
      ,prod_attr_value
      ,apply_discount_flag
      ,include_volume_flag
      ,indirect_flag
    )
    VALUES
    (  ozf_order_group_prod_s.NEXTVAL
      ,SYSDATE
      ,FND_GLOBAL.user_id
      ,SYSDATE
      ,FND_GLOBAL.user_id
      ,FND_GLOBAL.conc_login_id
      ,p_order_line_id
      ,l_offer_id
      ,l_list_header_id
      ,l_group_no
      ,l_vol_track_type
      ,l_combine_schedule
      ,l_pbh_line_id
      ,l_volume_type
      ,l_prod_attribute
      ,l_prod_attr_value
      ,l_apply_discount
      ,l_include_volume
      ,l_indirect_flag
    );

Line: 1753

    UPDATE ozf_order_group_prod
    SET    last_update_date    = SYSDATE,
           last_updated_by     = FND_GLOBAL.user_id,
           last_update_login   = FND_GLOBAL.conc_login_id,
           order_line_id       = p_order_line_id,
           offer_id            = l_offer_id,
           qp_list_header_id   = l_list_header_id,
           group_no            = l_group_no,
           volume_track_type   = l_vol_track_type,
           combine_schedule_yn = l_combine_schedule,
           pbh_line_id         = l_pbh_line_id,
           volume_type         = l_volume_type,
           prod_attribute      = l_prod_attribute,
           prod_attr_value     = l_prod_attr_value,
           apply_discount_flag = l_apply_discount,
           include_volume_flag = l_include_volume,
           indirect_flag       = l_indirect_flag
    WHERE  order_group_prod_id = l_id;

Line: 1875

  SELECT group_no
  FROM   ozf_volume_detail
  WHERE  offer_id = p_offer_id
  AND    cust_account_id = p_cust_account_id
  AND    transaction_date =
         (
         SELECT MAX(transaction_date)
         FROM   ozf_volume_detail
         WHERE  cust_account_id = p_cust_account_id
         AND    offer_id = p_offer_id
         );

Line: 1888

  SELECT a.beneficiary_party_id
  FROM   ozf_offr_market_options a, ozf_offr_market_options b
  WHERE  a.offer_market_option_id = b.offer_market_option_id
  AND    b.offer_id = p_offer_id
  AND    b.group_number = p_group_no;

Line: 1895

  SELECT beneficiary_party_id
  FROM   ozf_offr_market_options
  WHERE  offer_id = p_offer_id
  AND    group_number = p_group_no;

Line: 1919

PROCEDURE update_tracking_line
(
   p_init_msg_list     IN  VARCHAR2
  ,p_api_version       IN  NUMBER
  ,p_commit            IN  VARCHAR2
  ,x_return_status     OUT NOCOPY VARCHAR2
  ,x_msg_count         OUT NOCOPY NUMBER
  ,x_msg_data          OUT NOCOPY VARCHAR2
  ,p_list_header_id    IN  NUMBER
  ,p_interface_line_id IN  NUMBER
  ,p_resale_line_id    IN  NUMBER
)
IS
  l_api_name CONSTANT VARCHAR2(30) := 'update_tracking_line';

Line: 1934

  SAVEPOINT update_tracking_line;

Line: 1938

  UPDATE ozf_order_group_prod
  SET    order_line_id = p_resale_line_id,
         indirect_flag = 'R'
  WHERE  qp_list_header_id = p_list_header_id
  AND    order_line_id = p_interface_line_id
  AND    indirect_flag = 'I';

Line: 1948

     ROLLBACK TO update_tracking_line;

Line: 1958

END update_tracking_line;

Line: 1976

  select nvl(group_volume,0)  into l_group_volume from ozf_volume_summary
  where offer_id = p_offer_id
  and   group_no = p_group_number
  and   pbh_line_id = p_pbh_line_id;

Line: 1997

  select sum(volume) customer_volume
    into l_product_volume
    from ozf_volume_detail
  where offer_id = p_offer_id
    and cust_account_id = p_cust_account_id
    and pbh_line_id = p_pbh_line_id
    and bill_to_site_use_id = nvl(p_bill_to_id, bill_to_site_use_id)
    and ship_to_site_use_id = nvl(p_ship_to_id, ship_to_site_use_id);

Line: 2026

  select volume_from ||'-' || volume_to into l_volume_range
    from ozf_offer_discount_lines
   where offer_id = p_offer_id
     and parent_discount_line_id = p_pbh_line_id
     and l_volume between volume_from and volume_to;

Line: 2053

  select discount into l_actual_discount
    from ozf_offer_discount_lines
   where offer_id = p_offer_id
     and parent_discount_line_id = p_pbh_line_id
     and l_volume between volume_from and volume_to;

Line: 2076

  select c.volume_from ||'-' || c.volume_to into l_volume_range
    from ozf_offr_market_options a,
         ozf_market_preset_tiers b,
         ozf_offer_discount_lines c
   where a.offer_id = p_offer_id
     and b.offer_market_option_id = a.offer_market_option_id
     and a.group_number = p_group_no
     and b.pbh_offer_discount_id = p_pbh_line_id
     and c.offer_discount_line_id = b.dis_offer_discount_id;

Line: 2102

  select c.discount into l_actual_discount
    from ozf_offr_market_options a,
         ozf_market_preset_tiers b,
         ozf_offer_discount_lines c
   where a.offer_id = p_offer_id
     and b.offer_market_option_id = a.offer_market_option_id
     and a.group_number = p_group_no
     and b.pbh_offer_discount_id = p_pbh_line_id
     and c.offer_discount_line_id = b.dis_offer_discount_id;

Line: 2130

select qp_list_header_id into l_qp_list_header_id from ozf_offers where offer_id = p_offer_id;

Line: 2132

SELECT SUM(uti.plan_curr_amount) into l_payout_accrual
 FROM ozf_funds_utilized_all_b uti
WHERE uti.utilization_type IN ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
  AND plan_type = 'OFFR'
  AND plan_id = l_qp_list_header_id
  and product_id = p_item_id
  and cust_account_id = p_cust_account_id
  and bill_to_site_use_id = nvl(p_bill_to_id, bill_to_site_use_id)
  and ship_to_site_use_id = nvl(p_ship_to_id, ship_to_site_use_id)
  AND gl_posted_flag NOT in('N','F');