APPS.AS_SALES_LEAD_RANKS_PVT SQL Statements

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

Line: 37

        SELECT rank_id
        FROM   AS_SALES_LEAD_RANKS_B
        WHERE  enabled_flag = 'Y'
               and ((min_score <= c_min and max_score >= c_min )
                    or (min_score <= c_max and max_score >= c_max));

Line: 44

	   SELECT count(*)
        FROM   AS_SALES_LEAD_RANKS_B
        WHERE  enabled_flag = 'Y'
               and ((min_score <= c_min and max_score >= c_min )
                    or (min_score <= c_max and max_score >= c_max));

Line: 129

        SELECT b.rank_id
        FROM   AS_SALES_LEAD_RANKS_B b, AS_SALES_LEAD_RANKS_TL tl
        WHERE  b.rank_id = tl.rank_id
               and b.enabled_flag = 'Y'
               and tl.meaning = c_meaning;

Line: 193

        SELECT rank_id
        FROM   AS_SALES_LEAD_RANKS_B
        WHERE  enabled_flag = 'Y'
        AND    min_score = c_precedence;

Line: 244

      ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
      THEN
          -- Hint: Validate data
          -- IF p_PRECEDENCE <> G_MISS_CHAR
          -- verify if data is valid
          -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;

Line: 289

        SELECT 1
        FROM   AS_SALES_LEADS
        WHERE  lead_rank_id = c_sales_lead_rank_id;

Line: 314

      IF (p_validation_mode = AS_UTILITY_PVT.G_UPDATE) THEN

	-- first check if profile is using this rating
      	if (p_sales_lead_rank_id = l_default_profile) then
      		AS_UTILITY_PVT.Set_Message(
                    p_msg_level     => FND_MSG_PUB.G_MSG_LVL_ERROR,
                    p_msg_name      => 'AS_USED_RANK');

Line: 348

    	l_sql_text := 'select 1 from pv_process_rules_b where process_type = ''LEAD_RATING'' and action_value = to_char(:p_sales_lead_rank_id)';

Line: 403

      SELECT 'X' FROM AS_SALES_LEAD_RANKS_B
      WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;

Line: 407

      SELECT MAX(MIN_SCORE) FROM AS_SALES_LEAD_RANKS_B;

Line: 522

     select as_sales_lead_ranks_s.nextval into l_rank_id from dual;

Line: 531

     AS_SALES_LEAD_RANKS_PKG.Insert_Row(
         x_RANK_ID            => l_sales_lead_rank_rec.RANK_ID,
         x_MIN_SCORE          => l_sales_lead_rank_rec.MIN_SCORE,
-- use the same min_score as the max_score as well. Ignore the max_score passed in

         x_MAX_SCORE          => l_sales_lead_rank_rec.MAX_SCORE,
         x_enabled_flag       => l_sales_lead_rank_rec.enabled_flag,
         x_meaning            => l_sales_lead_rank_rec.meaning,
         x_description        => l_sales_lead_rank_rec.description,
         x_creation_date      => sysdate,
         x_created_by         => FND_GLOBAL.user_id,
         X_LAST_UPDATE_DATE   => sysdate,
         X_LAST_UPDATED_BY    => FND_GLOBAL.user_id,
         X_LAST_UPDATE_LOGIN  => FND_GLOBAL.user_id
         );

Line: 587

PROCEDURE Update_Rank (
    p_api_version         IN NUMBER := 2.0,
    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_sales_lead_rank_rec IN AS_SALES_LEAD_RANKS_PUB.sales_lead_rank_rec_type)
IS
    l_api_name        CONSTANT VARCHAR2(30) := 'Update_Rank';

Line: 607

        SELECT enabled_flag, min_score FROM AS_SALES_LEAD_RANKS_B
        WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;

Line: 615

    CURSOR c_get_last_update IS
        SELECT last_update_date FROM AS_SALES_LEAD_RANKS_B
        WHERE rank_id = p_sales_lead_rank_rec.RANK_ID;

Line: 618

    l_last_update_date  DATE;

Line: 622

    SAVEPOINT     Update_Rank_PVT;

Line: 651

    IF (p_sales_lead_rank_rec.last_update_date is NULL or
        p_sales_lead_rank_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: 657

             FND_MESSAGE.Set_Token('COLUMN', 'LAST_UPDATE_DATE', FALSE);

Line: 663

    OPEN c_get_last_update;

Line: 664

    FETCH c_get_last_update into l_last_update_date;

Line: 665

    IF (c_get_last_update%NOTFOUND) THEN
        IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
        THEN
            FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');

Line: 672

        CLOSE c_get_last_update;

Line: 676

    CLOSE c_get_last_update;

Line: 678

    IF (p_sales_lead_rank_rec.last_update_date <> l_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: 713

		   p_validation_mode        => AS_UTILITY_PVT.G_UPDATE,
		   p_PRECEDENCE             => P_SALES_LEAD_RANK_Rec.MIN_SCORE,
	       p_sales_lead_rank_id     => P_SALES_LEAD_RANK_Rec.RANK_ID,
		   x_return_status          => x_return_status,
		   x_msg_count              => x_msg_count,
		   x_msg_data               => x_msg_data);

Line: 746

             P_Validation_mode     => AS_UTILITY_PVT.G_UPDATE,
             p_sales_lead_rank_rec => p_sales_lead_rank_rec,
             X_Return_Status       => x_return_status,
             X_Msg_Count           => x_msg_count,
             X_Msg_Data            => x_msg_data
             );

Line: 763

             P_Validation_mode     => AS_UTILITY_PVT.G_UPDATE,
             p_sales_lead_rank_id =>  p_sales_lead_rank_rec.rank_id,
             X_Return_Status       => x_return_status,
             X_Msg_Count           => x_msg_count,
             X_Msg_Data            => x_msg_data
             );

Line: 778

    AS_SALES_LEAD_RANKS_PKG.Update_Row(
        x_RANK_ID            => l_sales_lead_rank_rec.RANK_ID,
        x_MIN_SCORE          => l_sales_lead_rank_rec.MIN_SCORE,
        x_MAX_SCORE          => l_sales_lead_rank_rec.MAX_SCORE,
        x_enabled_flag       => l_sales_lead_rank_rec.enabled_flag,
        x_meaning            => l_sales_lead_rank_rec.meaning,
        x_description        => l_sales_lead_rank_rec.description,
        X_LAST_UPDATE_DATE   => sysdate,
        X_LAST_UPDATED_BY    => FND_GLOBAL.user_id,
        X_LAST_UPDATE_LOGIN  => FND_GLOBAL.user_id
        );

Line: 797

                            'Update LEAD_RANK_SCORE',
                            '',
                            FALSE,
                            p_sales_lead_rank_rec.rank_id,
                            p_sales_lead_rank_rec.min_score);

Line: 816

          ROLLBACK TO Update_Rank_PVT;

Line: 823

          ROLLBACK TO Update_Rank_PVT;

Line: 830

          ROLLBACK TO Update_Rank_PVT;

Line: 838

END Update_Rank;

Line: 841

Procedure Delete_Rank (
    p_api_version         IN NUMBER := 2.0,
    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_sales_lead_rank_id  IN NUMBER)
IS
    l_api_name        CONSTANT VARCHAR2(30) := 'Delete_Rank';

Line: 867

        SELECT 1
        FROM   AS_SALES_LEADS
        WHERE  lead_rank_id = c_sales_lead_rank_id;

Line: 873

        SELECT 'X' FROM AS_SALES_LEAD_RANKS_B
        WHERE rank_id = p_sales_lead_rank_id;

Line: 878

    SAVEPOINT     Delete_Rank_PVT;

Line: 961

	l_sql_text := 'select 1 from pv_process_rules_b where process_type = ''LEAD_RATING'' and action_value = to_char(:l_sales_lead_rank_id)';

Line: 978

    AS_SALES_LEAD_RANKS_PKG.Delete_Row(X_RANK_ID => l_sales_lead_rank_id);

Line: 994

          ROLLBACK TO Delete_Rank_PVT;

Line: 1001

          ROLLBACK TO Delete_Rank_PVT;

Line: 1008

          ROLLBACK TO Delete_Rank_PVT;

Line: 1016

END DELETE_RANK;

Line: 1019

PROCEDURE UPDATE_LEAD_RANK_SCORE(
  ERRBUF                  OUT NOCOPY VARCHAR2,
  RETCODE                 OUT NOCOPY VARCHAR2,
  X_LEAD_RANK_ID          IN         NUMBER,
  X_LEAD_RANK_SCORE       IN         NUMBER)
IS
BEGIN
    --Update the as_sales_leads.lead_rank_score
    Write_log (1, 'Updating the as_sales_leads.lead_rank_score');

Line: 1028

    UPDATE as_sales_leads sl
       SET sl.lead_rank_score = x_lead_rank_score
         , sl.last_update_date = sysdate
         , sl.last_updated_by = fnd_global.user_id
         , sl.last_update_login = fnd_global.conc_login_id
     WHERE sl.lead_rank_id = x_lead_rank_id;

Line: 1037

    UPDATE AS_ACCESSES_ALL acc
       SET acc.lead_rank_score = x_lead_rank_score
         , acc.last_update_date = sysdate
         , acc.last_updated_by = fnd_global.user_id
         , acc.last_update_login = fnd_global.conc_login_id
     WHERE acc.sales_lead_id IN
            (select sl.sales_lead_id
             from as_sales_leads sl
             where sl.lead_rank_id = x_lead_rank_id);

Line: 1052

         Write_log (1, 'Error in as_sales_lead_ranks_pvt.update_lead_rank_score');

Line: 1059

         Write_Log(1, 'Unexpected error in as_sales_lead_ranks_pvt.update_lead_rank_score');

Line: 1064

        Write_Log(1, 'Exception: others in as_sales_lead_ranks_pvt.update_lead_rank_score');

Line: 1067

end UPDATE_LEAD_RANK_SCORE;