eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OZF_PROMOTIONAL_OFFERS_PVT SQL Statements

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

Line: 90


      SELECT OZF_OFFERS_s.NEXTVAL
      FROM dual;

Line: 94


      SELECT 1
      FROM OZF_OFFERS
      WHERE OFFER_ID = l_id;

Line: 106


   SELECT start_date_active, end_date_active
     FROM qp_list_headers_b
    WHERE list_header_id = l_list_header_id;

Line: 111


   SELECT start_date_active
     FROM qp_list_headers_b
    WHERE list_header_id = p_offers_rec.qp_list_header_id;

Line: 235


    OZF_Promotional_Offers_PKG.Insert_Row(
        px_offer_id                   => l_offer_id,
        p_qp_list_header_id           => l_offers_rec.qp_list_header_id,
        p_offer_type                  => l_offers_rec.offer_type,
        p_offer_code                  => l_offers_rec.offer_code,
        p_activity_media_id           => l_offers_rec.activity_media_id,
        p_reusable                    => l_offers_rec.reusable,
        p_user_status_id              => l_offers_rec.user_status_id,
        p_owner_id                    => l_offers_rec.owner_id,
        p_wf_item_key                 => l_offers_rec.wf_item_key,
        p_customer_reference          => l_offers_rec.customer_reference,
        p_buying_group_contact_id     => l_offers_rec.buying_group_contact_id,
        p_last_update_date            => SYSDATE,
        p_last_updated_by             => FND_GLOBAL.user_id,
        p_creation_date               => SYSDATE,
        p_created_by                  => FND_GLOBAL.user_id,
        p_last_update_login           => FND_GLOBAL.conc_login_id,
        px_object_version_number      => l_object_version_number,
        p_perf_date_from              => l_offers_rec.perf_date_from,
        p_perf_date_to                => l_offers_rec.perf_date_to,
        p_status_code                 => l_offers_rec.status_code,
        p_status_date                 => l_offers_rec.status_date,
        p_modifier_level_code         => l_offers_rec.modifier_level_code,
        p_order_value_discount_type   => l_offers_rec.order_value_discount_type,
        p_offer_amount                => l_offers_rec.offer_amount,
        p_lumpsum_amount              => l_offers_rec.lumpsum_amount,
        p_lumpsum_payment_type        => l_offers_rec.lumpsum_payment_type,
        p_custom_setup_id             => l_offers_rec.custom_setup_id,
        p_security_group_id           => l_offers_rec.security_group_id,
        p_budget_amount_tc            => l_offers_rec.budget_amount_tc,
        p_budget_amount_fc            => l_offers_rec.budget_amount_fc,
        p_transaction_currency_Code   => l_offers_rec.transaction_currency_Code ,
        p_functional_currency_code    => l_offers_rec.functional_currency_code,
        p_distribution_type           => l_offers_rec.distribution_type,
        p_qualifier_id                => l_offers_rec.qualifier_id,
        p_qualifier_type              => l_offers_rec.qualifier_type,
	      p_account_closed_flag         => l_offers_rec.account_closed_flag,
        p_budget_offer_yn             => l_offers_rec.budget_offer_yn,
        p_break_type                  => l_offers_rec.break_type,
        p_retroactive                 => l_offers_rec.retroactive,
        p_volume_offer_type           => l_offers_rec.volume_offer_type,
        p_confidential_flag           => l_offers_rec.confidential_flag,
        p_budget_source_type          => l_offers_rec.budget_source_type,
        p_budget_source_id            => l_offers_rec.budget_source_id,
        p_source_from_parent          => l_offers_rec.source_from_parent,
        p_buyer_name                  => l_offers_rec.buyer_name,
        p_last_recal_date             => l_offers_rec.last_recal_date,
        p_date_qualifier              => FND_PROFILE.value('OZF_STORE_DATE_IN_QUALIFIERS'),
        p_autopay_flag                => l_offers_rec.autopay_flag,
        p_autopay_days                => l_offers_rec.autopay_days,
        p_autopay_method              => l_offers_rec.autopay_method,
        p_autopay_party_attr          => l_offers_rec.autopay_party_attr,
        p_autopay_party_id            => l_offers_rec.autopay_party_id,
        p_tier_level                  => l_offers_rec.tier_level,
        p_na_rule_header_id           => l_offers_rec.na_rule_header_id,
        p_beneficiary_account_id      => l_offers_rec.beneficiary_account_id,
        p_sales_method_flag           => l_offers_rec.sales_method_flag,
        p_org_id                      => l_offers_rec.org_id,
        p_fund_request_curr_code      => nvl(l_offers_rec.transaction_currency_Code,FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY')),
	/*ER11806714 added percent_discount column */
	p_percent_discount		    => l_offers_rec.percent_discount
        );

Line: 411


      SELECT *
      FROM ozf_offers
      WHERE qp_list_header_id = p_offers_rec.qp_list_header_id;

Line: 476


   IF p_offers_rec.last_update_date = FND_API.g_miss_date THEN
      x_complete_rec.last_update_date := l_offers_rec.last_update_date;

Line: 481


   IF p_offers_rec.last_updated_by = FND_API.g_miss_num THEN
      x_complete_rec.last_updated_by := l_offers_rec.last_updated_by;

Line: 496


   IF p_offers_rec.last_update_login = FND_API.g_miss_num THEN
      x_complete_rec.last_update_login := l_offers_rec.last_update_login;

Line: 695


PROCEDURE Update_Offers(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
    p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
    p_validation_level           IN  NUMBER       := FND_API.G_VALID_LEVEL_FULL,

    x_return_status              OUT NOCOPY  VARCHAR2,
    x_msg_count                  OUT NOCOPY  NUMBER,
    x_msg_data                   OUT NOCOPY  VARCHAR2,

    p_offers_rec               IN    offers_rec_type,
    x_object_version_number      OUT NOCOPY  NUMBER
    )

 IS

  CURSOR c_get_offers(p_offer_id NUMBER) IS
  SELECT object_version_number
    FROM  OZF_OFFERS
   WHERE qp_list_header_id = p_offer_id;

Line: 718


  SELECT owner_id
    FROM ozf_offers
   WHERE qp_list_header_id = l_list_header_id;

Line: 723


   SELECT q.start_date_active, o.start_date
     FROM qp_list_headers_b q, ozf_offers o
    WHERE o.qp_list_header_id = q.list_header_id
      AND q.list_header_id = p_offers_rec.qp_list_header_id;

Line: 728


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

Line: 746


      SAVEPOINT UPDATE_Offers_PVT;

Line: 770


         OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
              p_token_name   => 'INFO',
              p_token_value  => 'Offers') ;

Line: 824


          AMS_access_PVT.update_object_owner(
            p_api_version       => l_api_version_number,
            p_init_msg_list     => FND_API.g_false,
            p_commit            => FND_API.g_false,
            p_validation_level  => FND_API.g_valid_level_full,
            x_return_status     => x_return_status,
            x_msg_count         => x_msg_count,
            x_msg_data          => x_msg_data,
            p_object_type       => 'OFFR',
            p_object_id         => l_offers_rec.qp_list_header_id,
            p_resource_id       => l_offers_rec.owner_id,
            p_old_resource_id   => l_old_owner_id);

Line: 865


      OZF_Promotional_Offers_PKG.Update_Row(
          p_offer_id  => l_offers_rec.offer_id,
          p_qp_list_header_id  => l_offers_rec.qp_list_header_id,
          p_offer_type  => l_offers_rec.offer_type,
          p_offer_code  => l_offers_rec.offer_code,
          p_activity_media_id  => l_offers_rec.activity_media_id,
          p_reusable  => l_offers_rec.reusable,
          p_user_status_id  => l_offers_rec.user_status_id,
          p_owner_id  => l_offers_rec.owner_id,
          p_wf_item_key  => l_offers_rec.wf_item_key,
          p_customer_reference  => l_offers_rec.customer_reference,
          p_buying_group_contact_id  => l_offers_rec.buying_group_contact_id,
          p_last_update_date  => SYSDATE,
          p_last_updated_by  => FND_GLOBAL.user_id,
          p_last_update_login  => FND_GLOBAL.conc_login_id,
          p_object_version_number  => l_offers_rec.object_version_number,
          p_perf_date_from  => l_offers_rec.perf_date_from,
          p_perf_date_to  => l_offers_rec.perf_date_to,
          p_status_code  => l_offers_rec.status_code,
          p_status_date  => l_offers_rec.status_date,
          p_modifier_level_code  => l_offers_rec.modifier_level_code,
          p_order_value_discount_type  => l_offers_rec.order_value_discount_type,
          p_offer_amount  => l_offers_rec.offer_amount,
          p_lumpsum_amount  => l_offers_rec.lumpsum_amount,
          p_lumpsum_payment_type  => l_offers_rec.lumpsum_payment_type,
          p_custom_setup_id             => l_offers_rec.custom_setup_id,
          p_security_group_id           => l_offers_rec.security_group_id,
          p_budget_amount_tc            => l_offers_rec.budget_amount_tc,
          p_budget_amount_fc            => l_offers_rec.budget_amount_fc,
          p_transaction_currency_Code   => l_offers_rec.transaction_currency_Code ,
          p_functional_currency_code    => l_offers_rec.functional_currency_code,
          p_distribution_type           => l_offers_rec.distribution_type,
          p_qualifier_id                => l_offers_rec.qualifier_id,
          p_qualifier_type              => l_offers_rec.qualifier_type,
	        p_account_closed_flag         => l_offers_rec.account_closed_flag,
          p_budget_offer_yn             => l_offers_rec.budget_offer_yn,
          p_break_type                  => l_offers_rec.break_type,
          p_retroactive                 => l_offers_rec.retroactive,
          p_volume_offer_type           => l_offers_rec.volume_offer_type,
          p_confidential_flag           => l_offers_rec.confidential_flag,
          p_budget_source_type          => l_offers_rec.budget_source_type,
          p_budget_source_id            => l_offers_rec.budget_source_id,
          p_source_from_parent          => l_offers_rec.source_from_parent,
          p_buyer_name                  => l_offers_rec.buyer_name,
          p_last_recal_date             => l_last_recal_date,
          p_date_qualifier              => FND_API.G_MISS_CHAR,
          p_autopay_flag                => l_offers_rec.autopay_flag,
          p_autopay_days                => l_offers_rec.autopay_days,
          p_autopay_method              => l_offers_rec.autopay_method,
          p_autopay_party_attr          => l_offers_rec.autopay_party_attr,
          p_autopay_party_id            => l_offers_rec.autopay_party_id,
          p_tier_level                  => l_offers_rec.tier_level,
          p_na_rule_header_id           => l_offers_rec.na_rule_header_id,
          p_beneficiary_account_id      => l_offers_rec.beneficiary_account_id,
          p_sales_method_flag                => l_offers_rec.sales_method_flag,
          p_org_id                      => l_offers_rec.org_id,
          p_start_date                  => l_start_date,
	  /*ER11806714 added percent_discount column */
	  p_percent_discount		    => l_offers_rec.percent_discount,
	  p_fund_request_curr_code	=> nvl(l_offers_rec.transaction_currency_Code,FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY')) -- Bug 14138664
          );

Line: 949


     ROLLBACK TO UPDATE_Offers_PVT;

Line: 959


     ROLLBACK TO UPDATE_Offers_PVT;

Line: 969


     ROLLBACK TO UPDATE_Offers_PVT;

Line: 982


End Update_Offers;

Line: 985


PROCEDURE Delete_Offers(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
    p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,
    p_validation_level           IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
    x_return_status              OUT NOCOPY  VARCHAR2,
    x_msg_count                  OUT NOCOPY  NUMBER,
    x_msg_data                   OUT NOCOPY  VARCHAR2,
    p_offer_id                   IN  NUMBER,
    p_object_version_number      IN   NUMBER
    )

 IS
L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Offers';

Line: 1004


      SAVEPOINT DELETE_Offers_PVT;

Line: 1029


      OZF_Promotional_Offers_PKG.Delete_Row(
          p_OFFER_ID  => p_OFFER_ID);

Line: 1053


     ROLLBACK TO DELETE_Offers_PVT;

Line: 1063


     ROLLBACK TO DELETE_Offers_PVT;

Line: 1073


     ROLLBACK TO DELETE_Offers_PVT;

Line: 1085


End Delete_Offers;

Line: 1109


   SELECT OFFER_ID
   FROM OZF_OFFERS
   WHERE OFFER_ID = p_OFFER_ID
   AND object_version_number = p_object_version
   FOR UPDATE NOWAIT;

Line: 1252


  SELECT end_date_active
  FROM   qp_list_headers_b
  WHERE  list_header_id = p_offers_rec.qp_list_header_id;

Line: 1420


  SELECT 1
  FROM   ams_media_vl
  WHERE  media_type_code = 'DEAL'
  AND    media_id = l_id;

Line: 1831


                 p_validation_mode   => JTF_PLSQL_API.g_update,
                 x_return_status     => x_return_status
              );

Line: 1952


   SELECT system_status_code
     FROM ams_user_statuses_vl
    WHERE user_status_id = p_user_status_id
      AND system_status_type = 'OZF_OFFER_STATUS'
      AND enabled_flag = 'Y';

Line: 1984


   SELECT user_status_id
     FROM ams_user_statuses_vl
    WHERE system_status_type = 'OZF_OFFER_STATUS'
      AND system_status_code = 'DRAFT'
      AND default_flag = 'Y'
      AND enabled_flag = 'Y';