DBA Data[Home] [Help]

APPS.AS_INTEREST_PVT SQL Statements

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

Line: 217

  SELECT  1
  FROM  HZ_PARTIES CUST
  WHERE CUST.PARTY_TYPE in ('PERSON', 'ORGANIZATION', 'PARTY_RELATIONSHIP')
        AND CUST.STATUS IN ('A','I')
        AND party_id = X_Party_Id;
Line: 345

    l_interests_inserted  NUMBER := 0;  -- Number of successful inserts
Line: 356

    l_update_access_flag  VARCHAR2(1);
Line: 367

        select 1
        from AS_INTERESTS_ALL
        where customer_id = p_customer_id
        and interest_use_code = 'CONTACT_INTEREST'
        and product_category_id = p_product_category_id
        and product_cat_set_id = p_product_cat_set_id;
Line: 548

          AS_ACCESS_PUB.has_updateCustomerAccess
          ( p_api_version_number     => 2.0
           ,p_init_msg_list          => p_init_msg_list
           ,p_validation_level       => p_validation_level
           ,p_access_profile_rec     => p_access_profile_rec
           ,p_admin_flag             => p_admin_flag
           ,p_admin_group_id         => p_admin_group_id
           ,p_person_id              => l_identity_sales_member_rec.employee_person_id
           ,p_customer_id            => p_customer_id
           ,p_check_access_flag      => 'Y'
           ,p_identity_salesforce_id => p_identity_salesforce_id
           ,p_partner_cont_party_id  => NULL
           ,x_return_status         => l_return_status
           ,x_msg_count             => l_msg_count
           ,x_msg_data              => l_msg_data
           ,x_update_access_flag    => l_update_access_flag
          );
Line: 568

                AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateCustomerAccess fail');
Line: 573

          IF (l_update_access_flag <> 'Y') THEN
             IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
                FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
Line: 581

          AS_ACCESS_PUB.has_updateOpportunityAccess
          ( p_api_version_number     => 2.0
           ,p_init_msg_list          => p_init_msg_list
           ,p_validation_level       => p_validation_level
           ,p_access_profile_rec     => p_access_profile_rec
           ,p_admin_flag             => p_admin_flag
           ,p_admin_group_id         => p_admin_group_id
           ,p_person_id              => l_identity_sales_member_rec.employee_person_id
           ,p_opportunity_id         => p_lead_id
           ,p_check_access_flag      => 'Y'
           ,p_identity_salesforce_id => p_identity_salesforce_id
           ,p_partner_cont_party_id  => Null
           ,x_return_status          => p_return_status
           ,x_msg_count              => l_msg_count
           ,x_msg_data               => l_msg_data
           ,x_update_access_flag     => l_update_access_flag
          );
Line: 601

                AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
Line: 606

          IF (l_update_access_flag <> 'Y') THEN
             IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
                FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
Line: 681

          FND_MESSAGE.Set_Name('AS', 'Inserting Record');
Line: 690

        AS_INTERESTS_PKG.Insert_Row ( X_Rowid                       => l_rowid,
                                      X_Interest_Id                 => l_interest_tbl(l_curr_row).interest_id,
                                      X_Last_Update_Date            =>sysdate,
                                      X_Last_Updated_By             =>FND_GLOBAL.User_Id,
                                      X_Creation_Date               => SYSDATE,
                                      X_Created_By                  =>FND_GLOBAL.User_Id,
                                      X_Last_Update_Login           =>FND_GLOBAL.Conc_Login_Id,
                                      X_Request_Id                  => FND_GLOBAL.Conc_Request_Id,
                                      X_Program_Application_Id      => FND_GLOBAL.Prog_Appl_Id,
                                      X_Program_Id                  => FND_GLOBAL.Conc_Program_Id,
                                      X_Program_Update_Date         => SYSDATE,
                                      X_Interest_Use_Code           => p_interest_use_code,
                                      X_Interest_Type_Id            => l_interest_tbl(l_curr_row).Interest_Type_Id,
                                      X_Contact_Id                  => p_contact_id,
                                      X_Customer_Id                 => p_customer_id,
                                      X_Address_Id                  => p_address_id,
                                      X_Lead_Id                     => p_lead_id,
                                      X_Primary_Interest_Code_Id    => l_interest_tbl(l_curr_row).Primary_Interest_Code_Id,
                                      X_Secondary_Interest_Code_Id  => l_interest_tbl(l_curr_row).Secondary_Interest_Code_Id,
                                      X_Status_Code                 => l_interest_tbl(l_curr_row).Status_Code,
                                      X_Description                 => l_interest_tbl(l_curr_row).description,
                                      X_Attribute_Category          => l_interest_tbl(l_curr_row).Attribute_Category,
                                      X_Attribute1                  => l_interest_tbl(l_curr_row).Attribute1,
                                      X_Attribute2                  => l_interest_tbl(l_curr_row).Attribute2,
                                      X_Attribute3                  => l_interest_tbl(l_curr_row).Attribute3,
                                      X_Attribute4                  => l_interest_tbl(l_curr_row).Attribute4,
                                      X_Attribute5                  => l_interest_tbl(l_curr_row).Attribute5,
                                      X_Attribute6                  => l_interest_tbl(l_curr_row).Attribute6,
                                      X_Attribute7                  => l_interest_tbl(l_curr_row).Attribute7,
                                      X_Attribute8                  => l_interest_tbl(l_curr_row).Attribute8,
                                      X_Attribute9                  => l_interest_tbl(l_curr_row).Attribute9,
                                      X_Attribute10                 => l_interest_tbl(l_curr_row).Attribute10,
                                      X_Attribute11                 => l_interest_tbl(l_curr_row).Attribute11,
                                      X_Attribute12                 => l_interest_tbl(l_curr_row).Attribute12,
                                      X_Attribute13                 => l_interest_tbl(l_curr_row).Attribute13,
                                      X_Attribute14                 => l_interest_tbl(l_curr_row).Attribute14,
                                      X_Attribute15                 => l_interest_tbl(l_curr_row).Attribute15,
                                      X_Product_Category_Id         => l_interest_tbl(l_curr_row).Product_Category_Id,
                                      X_Product_Cat_Set_Id          => l_interest_tbl(l_curr_row).Product_Cat_Set_Id
                                      );
Line: 732

        l_interests_inserted := l_interests_inserted + 1;
Line: 832

      l_interests_inserted > 0
    THEN
      FND_MESSAGE.Set_Name('AS', 'API_ROWS_INSERTED');
Line: 836

      FND_MESSAGE.Set_Token('NUMBER', to_char(l_interests_inserted), FALSE);
Line: 957

PROCEDURE Update_Interest
(   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,
        p_identity_salesforce_id  IN    NUMBER,
    p_interest_rec      IN  INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
    p_interest_use_code IN  VARCHAR2,
     p_check_access_flag   IN  VARCHAR2,
     p_admin_flag          IN  VARCHAR2,
     p_admin_group_id      IN  NUMBER,
     p_access_profile_rec  IN  AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
    x_return_status     OUT NOCOPY  VARCHAR2,
    x_msg_count     OUT NOCOPY  NUMBER,
    x_msg_data      OUT NOCOPY  VARCHAR2,
    x_interest_id       OUT NOCOPY     NUMBER
) is

 l_api_name            CONSTANT VARCHAR2(30) := 'Update_Interest';
Line: 982

    l_last_update_date DATE;
Line: 983

    l_update_access_flag VARCHAR2(1);
Line: 989

    SELECT rowid, last_update_date
    FROM   as_interests
    WHERE  interest_id = p_interest_rec.interest_id
    FOR UPDATE of interest_Id  NOWAIT;
Line: 998

    select 1
    from AS_INTERESTS_ALL
    where customer_id = p_customer_id
    and interest_use_code = 'CONTACT_INTEREST'
    and product_category_id = p_product_category_id
    and product_cat_set_id = p_product_cat_set_id
    and interest_id <> p_interest_id;
Line: 1007

    l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpv.Update_Interest';
Line: 1011

    SAVEPOINT UPDATE_INTEREST_PVT;
Line: 1142

          AS_ACCESS_PUB.has_updateCustomerAccess
          ( p_api_version_number     => 2.0
           ,p_init_msg_list          => p_init_msg_list
           ,p_validation_level       => p_validation_level
           ,p_access_profile_rec     => p_access_profile_rec
           ,p_admin_flag             => p_admin_flag
           ,p_admin_group_id         => p_admin_group_id
           ,p_person_id              => l_identity_sales_member_rec.employee_person_id
           ,p_customer_id            => p_interest_rec.customer_id
           ,p_check_access_flag      => 'Y'
           ,p_identity_salesforce_id => p_identity_salesforce_id
           ,p_partner_cont_party_id  => NULL
           ,x_return_status         => l_return_status
           ,x_msg_count             => l_msg_count
           ,x_msg_data              => l_msg_data
           ,x_update_access_flag    => l_update_access_flag
          );
Line: 1162

                AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateCustomerAccess fail');
Line: 1167

          IF (l_update_access_flag <> 'Y') THEN
             IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
                FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
Line: 1175

          AS_ACCESS_PUB.has_updateOpportunityAccess
          ( p_api_version_number     => 2.0
          ,p_init_msg_list          => p_init_msg_list
           ,p_validation_level       => p_validation_level
           ,p_access_profile_rec     => p_access_profile_rec
           ,p_admin_flag             => p_admin_flag
           ,p_admin_group_id         => p_admin_group_id
           ,p_person_id              => l_identity_sales_member_rec.employee_person_id
           ,p_opportunity_id         => p_interest_rec.lead_id
           ,p_check_access_flag      => 'Y'
           ,p_identity_salesforce_id => p_identity_salesforce_id
           ,p_partner_cont_party_id  => Null
           ,x_return_status          => l_return_status
           ,x_msg_count              => l_msg_count
           ,x_msg_data               => l_msg_data
           ,x_update_access_flag     => l_update_access_flag
          );
Line: 1195

                AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
Line: 1200

          IF (l_update_access_flag <> 'Y') THEN
             IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
                FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
Line: 1255

    fetch get_interest_info_csr into l_rowid, l_last_update_date;
Line: 1258

    if (p_interest_rec.last_update_date is NULL
        or p_interest_rec.last_update_date = FND_API.G_MISS_DATE)
        then
        IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
        THEN
            FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
Line: 1264

             FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
Line: 1270

    if (l_last_update_date <> p_interest_rec.last_update_date)
    then
        IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
        THEN
            FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
Line: 1281

        AS_INTERESTS_PKG.Update_Row (     X_Rowid                       => l_rowid,
                                      X_Interest_Id                 => p_interest_rec.interest_id,
                                      X_Last_Update_Date            => SYSDATE,
                                      X_Last_Updated_By             => FND_GLOBAL.USER_ID,
                                      X_Last_Update_Login           => FND_GLOBAL.Conc_Login_Id,
                                      X_Request_Id                  => FND_GLOBAL.Conc_Request_Id,
                                      X_Program_Application_Id      => FND_GLOBAL.Prog_Appl_Id,
                                      X_Program_Id                  => FND_GLOBAL.Conc_Program_Id,
                                      X_Program_Update_Date         => SYSDATE,
                                      X_Interest_Use_Code           => p_interest_use_code,
                                      X_Interest_Type_Id            => p_interest_rec.Interest_Type_Id,
                                      X_Contact_Id                  => p_interest_rec.contact_id,
                                      X_Customer_Id                 => p_interest_rec.customer_id,
                                      X_Address_Id                  => p_interest_rec.address_id,
                                      X_Lead_Id                     => p_interest_rec.lead_id,
                                      X_Primary_Interest_Code_Id    => p_interest_rec.Primary_Interest_Code_Id,
                                      X_Secondary_Interest_Code_Id  => p_interest_rec.Secondary_Interest_Code_Id,
                                      X_Status_Code                 => p_interest_rec.Status_Code,
                                      X_Description                 => p_interest_rec.description,
                                      X_Attribute_Category          => p_interest_rec.Attribute_Category,
                                      X_Attribute1                  => p_interest_rec.Attribute1,
                                      X_Attribute2                  => p_interest_rec.Attribute2,
                                      X_Attribute3                  => p_interest_rec.Attribute3,
                                      X_Attribute4                  => p_interest_rec.Attribute4,
                                      X_Attribute5                  => p_interest_rec.Attribute5,
                                      X_Attribute6                  => p_interest_rec.Attribute6,
                                      X_Attribute7                  => p_interest_rec.Attribute7,
                                      X_Attribute8                  => p_interest_rec.Attribute8,
                                      X_Attribute9                  => p_interest_rec.Attribute9,
                                      X_Attribute10                 => p_interest_rec.Attribute10,
                                      X_Attribute11                 => p_interest_rec.Attribute11,
                                      X_Attribute12                 => p_interest_rec.Attribute12,
                                      X_Attribute13                 => p_interest_rec.Attribute13,
                                      X_Attribute14                 => p_interest_rec.Attribute14,
                                      X_Attribute15                 => p_interest_rec.Attribute15,
                                      X_Product_Category_Id         => p_interest_rec.Product_Category_Id,
                                      X_Product_Cat_Set_Id          => p_interest_rec.Product_Cat_Set_Id
                                      );
Line: 1380

             ROLLBACK TO UPDATE_INTEREST_PVT;
Line: 1386

               FND_MESSAGE.Set_Token('INFO', 'UPDATE_INTEREST', FALSE);
Line: 1425

  END Update_Interest;
Line: 1427

PROCEDURE Delete_Interest
(   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,
        p_identity_salesforce_id  IN    NUMBER,
    p_interest_rec      IN  INTEREST_REC_TYPE := G_MISS_INTEREST_REC,
    p_interest_use_code IN  VARCHAR2,
     p_check_access_flag   in  varchar2,
     p_admin_flag          in  varchar2,
     p_admin_group_id      in  number,
     p_access_profile_rec  IN  AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE,
    x_return_status     OUT NOCOPY  VARCHAR2,
    x_msg_count     OUT NOCOPY  NUMBER,
    x_msg_data      OUT NOCOPY  VARCHAR2
) is
cursor get_interest_info_csr(p_interest_id NUMBER) is
        select 1
        from as_interests_all
        where interest_id = p_interest_id;
Line: 1448

    l_api_name      CONSTANT VARCHAR2(30) := 'Delete_Interest';
Line: 1455

    l_update_access_flag varchar2(1);
Line: 1458

        l_module CONSTANT VARCHAR2(255) := 'as.plsql.intpv.Delete_Interest';
Line: 1461

    SAVEPOINT DELETE_INTEREST_PVT;
Line: 1549

       AS_ACCESS_PUB.has_updateCustomerAccess
       ( p_api_version_number     => 2.0
        ,p_init_msg_list          => p_init_msg_list
        ,p_validation_level       => p_validation_level
        ,p_access_profile_rec     => p_access_profile_rec
        ,p_admin_flag             => p_admin_flag
        ,p_admin_group_id         => p_admin_group_id
        ,p_person_id              => l_identity_sales_member_rec.employee_person_id
        ,p_customer_id            => p_interest_rec.customer_id
        ,p_check_access_flag      => 'Y'
        ,p_identity_salesforce_id => p_identity_salesforce_id
        ,p_partner_cont_party_id  => NULL
        ,x_return_status         => l_return_status
        ,x_msg_count             => x_msg_count
        ,x_msg_data              => x_msg_data
        ,x_update_access_flag    => l_update_access_flag
       );
Line: 1571

       IF (l_update_access_flag <> 'Y') THEN
          IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
             FND_MESSAGE.Set_Name('AS', 'API_NO_DELETE_PRIVILEGE');
Line: 1579

    AS_ACCESS_PUB.has_updateOpportunityAccess
       ( p_api_version_number     => 2.0
        ,p_init_msg_list          => p_init_msg_list
        ,p_validation_level       => p_validation_level
        ,p_access_profile_rec     => p_access_profile_rec
        ,p_admin_flag             => p_admin_flag
        ,p_admin_group_id         => p_admin_group_id
        ,p_person_id              => l_identity_sales_member_rec.employee_person_id
        ,p_opportunity_id         => p_interest_rec.lead_id
        ,p_check_access_flag      => 'Y'
        ,p_identity_salesforce_id => p_identity_salesforce_id
        ,p_partner_cont_party_id  => Null
        ,x_return_status          => l_return_status
        ,x_msg_count              => x_msg_count
        ,x_msg_data               => x_msg_data
        ,x_update_access_flag     => l_update_access_flag
       );
Line: 1599

             AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'has_updateOpportunityAccess fail');
Line: 1604

       IF (l_update_access_flag <> 'Y') THEN
          IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
             FND_MESSAGE.Set_Name('AS', 'API_NO_DELETE_PRIVILEGE');
Line: 1615

    delete from as_interests_all
    where interest_id = p_interest_rec.interest_id;
Line: 1700

end Delete_Interest;
Line: 1711

        SELECT  PRODUCT_CATEGORY_ID, PRODUCT_CAT_SET_ID
        FROM    AS_INTERESTS_ALL
        WHERE   INTEREST_ID = l_interest_id;
Line: 1857

      SELECT  'X'
      FROM  as_interest_types_b
      WHERE Interest_Type_Id = X_Int_Type_Id;
Line: 1863

      SELECT 'X'
      FROM  As_Interest_Codes_B Pic
      WHERE Pic.Interest_Type_Id = X_Int_Type_Id
        and Pic.Interest_Code_Id = X_Int_Code_Id
        and Pic.Parent_Interest_Code_Id Is Null;
Line: 1872

      SELECT 'X'
      FROM  As_Interest_Codes_B Sic
      WHERE Sic.Interest_Type_Id = X_Int_Type_Id
        And Sic.Interest_Code_Id = X_Sec_Int_Code_Id
        And Sic.Parent_Interest_Code_Id = X_Int_Code_Id;
Line: 1962

      SELECT  'X'
      FROM  As_Interest_Statuses
      WHERE Interest_Type_Id = X_Int_Type_Id
        And Interest_Status_Code = X_Int_Status_Code;
Line: 2009

      SELECT  'X'
      FROM  As_Interest_Statuses
      WHERE Product_Category_Id = X_Product_Category_Id
        And Product_Cat_Set_Id = X_Product_Cat_Set_Id
        And Interest_Status_Code = X_Int_Status_Code;