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.PSB_PAY_ELEMENT_RATES_PVT SQL Statements

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

Line: 31


PROCEDURE INSERT_ROW
( p_api_version                 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_return_status               OUT  NOCOPY     VARCHAR2,
  p_msg_count                   OUT  NOCOPY     NUMBER,
  p_msg_data                    OUT  NOCOPY     VARCHAR2,
  --
  P_PAY_ELEMENT_RATE_ID              in      NUMBER,
  P_PAY_ELEMENT_OPTION_ID            in      NUMBER,
  P_PAY_ELEMENT_ID                   in      NUMBER,
  P_EFFECTIVE_START_DATE             in      DATE,
  P_EFFECTIVE_END_DATE               in      DATE,
  P_WORKSHEET_ID                     in      NUMBER,
  P_ELEMENT_VALUE_TYPE               in      VARCHAR2,
  P_ELEMENT_VALUE                    in      NUMBER,
  P_PAY_BASIS                        in      VARCHAR2,
  P_FORMULA_ID                       in      NUMBER,
  P_MAXIMUM_VALUE                    in      NUMBER,
  P_MID_VALUE                        in      NUMBER,
  P_MINIMUM_VALUE                    in      NUMBER,
  P_CURRENCY_CODE                    IN      VARCHAR2,
  P_LAST_UPDATE_DATE                 in      DATE,
  P_LAST_UPDATED_BY                  in      NUMBER,
  P_LAST_UPDATE_LOGIN                in      NUMBER,
  P_CREATED_BY                       in      NUMBER,
  P_CREATION_DATE                    in      DATE
) IS

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

Line: 66


     select ROWID from psb_pay_element_rates
     where pay_element_rate_id = p_pay_element_rate_id ;

Line: 73


  SAVEPOINT     INSERT_ROW_PVT;

Line: 97


  INSERT INTO psb_pay_element_rates
  (
  PAY_ELEMENT_RATE_ID         ,
  PAY_ELEMENT_ID              ,
  PAY_ELEMENT_OPTION_ID       ,
  EFFECTIVE_START_DATE        ,
  EFFECTIVE_END_DATE          ,
  WORKSHEET_ID                ,
  ELEMENT_VALUE_TYPE          ,
  ELEMENT_VALUE               ,
  PAY_BASIS               ,
  FORMULA_ID                  ,
  MAXIMUM_VALUE               ,
  MID_VALUE                   ,
  MINIMUM_VALUE               ,
  CURRENCY_CODE               ,
  LAST_UPDATE_DATE            ,
  LAST_UPDATED_BY             ,
  LAST_UPDATE_LOGIN           ,
  CREATED_BY                  ,
  CREATION_DATE
  )
  VALUES
  (
  P_PAY_ELEMENT_RATE_ID         ,
  P_PAY_ELEMENT_ID              ,
  P_PAY_ELEMENT_OPTION_ID       ,
  P_EFFECTIVE_START_DATE        ,
  P_EFFECTIVE_END_DATE          ,
  P_WORKSHEET_ID                ,
  P_ELEMENT_VALUE_TYPE          ,
  P_ELEMENT_VALUE               ,
  P_PAY_BASIS                   ,
  P_FORMULA_ID                  ,
  P_MAXIMUM_VALUE               ,
  P_MID_VALUE                   ,
  P_MINIMUM_VALUE               ,
  P_CURRENCY_CODE               ,
  P_LAST_UPDATE_DATE            ,
  P_LAST_UPDATED_BY             ,
  P_LAST_UPDATE_LOGIN           ,
  P_CREATED_BY                  ,
  P_CREATION_DATE
  );

Line: 165


     rollback to INSERT_ROW_PVT;

Line: 175


     rollback to INSERT_ROW_PVT;

Line: 185


     rollback to INSERT_ROW_PVT;

Line: 198


END INSERT_ROW;

Line: 202


PROCEDURE UPDATE_ROW
( p_api_version                 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_return_status               OUT  NOCOPY     VARCHAR2,
  p_msg_count                   OUT  NOCOPY     NUMBER,
  p_msg_data                    OUT  NOCOPY     VARCHAR2,
  --
  P_PAY_ELEMENT_RATE_ID              in      NUMBER,
  P_EFFECTIVE_START_DATE             in      DATE := FND_API.G_MISS_DATE,
  P_EFFECTIVE_END_DATE               in      DATE := FND_API.G_MISS_DATE,
  P_ELEMENT_VALUE_TYPE               in      VARCHAR2,
  P_ELEMENT_VALUE                    in      NUMBER,
  P_PAY_BASIS                        in      VARCHAR2,
  P_FORMULA_ID                       in      NUMBER,
  P_MAXIMUM_VALUE                    in      NUMBER,
  P_MID_VALUE                        in      NUMBER,
  P_MINIMUM_VALUE                    in      NUMBER,
  P_LAST_UPDATE_DATE                 in      DATE,
  P_LAST_UPDATED_BY                  in      NUMBER,
  P_LAST_UPDATE_LOGIN                in      NUMBER
) IS

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

Line: 233


  SAVEPOINT     UPDATE_ROW_PVT;

Line: 256


  UPDATE psb_pay_element_rates SET
  EFFECTIVE_START_DATE         =  DECODE(P_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, EFFECTIVE_START_DATE, P_EFFECTIVE_START_DATE),
  EFFECTIVE_END_DATE           =  DECODE(P_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, EFFECTIVE_END_DATE, P_EFFECTIVE_END_DATE),
  ELEMENT_VALUE_TYPE           =  P_ELEMENT_VALUE_TYPE         ,
  ELEMENT_VALUE                =  P_ELEMENT_VALUE              ,
  PAY_BASIS                    =  P_PAY_BASIS                  ,
  FORMULA_ID                   =  P_FORMULA_ID                 ,
  MAXIMUM_VALUE                =  P_MAXIMUM_VALUE              ,
  MID_VALUE                    =  P_MID_VALUE                  ,
  MINIMUM_VALUE                =  P_MINIMUM_VALUE              ,
  LAST_UPDATE_DATE             =  P_LAST_UPDATE_DATE           ,
  LAST_UPDATED_BY              =  P_LAST_UPDATED_BY            ,
  LAST_UPDATE_LOGIN            =  P_LAST_UPDATE_LOGIN
  WHERE PAY_ELEMENT_RATE_ID = P_PAY_ELEMENT_RATE_ID;

Line: 292


     rollback to UPDATE_ROW_PVT;

Line: 302


     rollback to UPDATE_ROW_PVT;

Line: 312


     rollback to UPDATE_ROW_PVT;

Line: 325


END UPDATE_ROW;

Line: 329


PROCEDURE DELETE_ROW
( p_api_version         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_return_status       OUT  NOCOPY     VARCHAR2,
  p_msg_count           OUT  NOCOPY     NUMBER,
  p_msg_data            OUT  NOCOPY     VARCHAR2,
  --
  P_PAY_ELEMENT_RATE_ID              in      NUMBER
) IS

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

Line: 348


  SAVEPOINT     DELETE_ROW_PVT;

Line: 368


  DELETE FROM psb_pay_element_rates
  WHERE pay_element_rate_id = p_pay_element_rate_id;

Line: 392


     rollback to DELETE_ROW_PVT;

Line: 402


     rollback to DELETE_ROW_PVT;

Line: 412


     rollback to DELETE_ROW_PVT;

Line: 424


END DELETE_ROW;

Line: 461


  CURSOR C IS SELECT * FROM PSB_PAY_ELEMENT_RATES
  WHERE pay_element_rate_id = p_pay_element_rate_id
  FOR UPDATE of PAY_ELEMENT_RATE_Id NOWAIT;

Line: 491


    FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');

Line: 603


PROCEDURE Delete_Element_Rates
( p_api_version       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_return_status     OUT  NOCOPY  VARCHAR2,
  p_msg_count         OUT  NOCOPY  NUMBER,
  p_msg_data          OUT  NOCOPY  VARCHAR2,
  p_worksheet_id      IN   NUMBER
) IS

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

Line: 621


  SAVEPOINT     Delete_Element_Rates_Pvt;

Line: 641


  delete from PSB_PAY_ELEMENT_RATES
   where worksheet_id = p_worksheet_id;

Line: 665


     rollback to Delete_Element_Rates_Pvt;

Line: 673


     rollback to Delete_Element_Rates_Pvt;

Line: 681


     rollback to Delete_Element_Rates_Pvt;

Line: 693


END Delete_Element_Rates;

Line: 724


    select psb_pay_element_rates_s.nextval RateID
      from dual;

Line: 728


    select pay_element_rate_id,
	   pay_element_id,
	   pay_element_option_id,
	   effective_start_date,
	   effective_end_date,
	   worksheet_id,
	   element_value_type,
	   element_value,
	   pay_basis,
	   formula_id,
	   maximum_value,
	   mid_value,
	   minimum_value,
	   currency_code
      from PSB_PAY_ELEMENT_RATES
     where nvl(pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
       and nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
       and nvl(currency_code, FND_API.G_MISS_CHAR) = nvl(p_currency_code, FND_API.G_MISS_CHAR)
       and ((((p_effective_end_date is not null)
	and ((effective_start_date <= p_effective_end_date)
	 and (effective_end_date is null))
	 or ((effective_start_date between p_effective_start_date and p_effective_end_date)
	  or (effective_end_date between p_effective_start_date and p_effective_end_date)
	 or ((effective_start_date < p_effective_start_date)
	 and (effective_end_date > p_effective_end_date)))))
       or ((p_effective_end_date is null)
       and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
       and pay_element_id = p_pay_element_id;

Line: 766


  l_updated_record         VARCHAR2(1);

Line: 854


    Insert_Row
	  (p_api_version => 1.0,
	   p_return_status => l_return_status,
	   p_msg_count => p_msg_count,
	   p_msg_data => p_msg_data,
	   p_pay_element_rate_id => l_pay_element_rate_id,
	   p_pay_element_option_id => p_pay_element_option_id,
	   p_pay_element_id => p_pay_element_id,
	   p_effective_start_date => p_effective_start_date,
	   p_effective_end_date => p_effective_end_date,
	   p_worksheet_id => p_worksheet_id,
	   p_element_value_type => p_element_value_type,
	   p_element_value => p_element_value,
	   p_pay_basis => p_pay_basis,
	   p_formula_id => p_formula_id,
	   p_maximum_value => p_maximum_value,
	   p_mid_value => p_mid_value,
	   p_minimum_value => p_minimum_value,
	   p_currency_code => p_currency_code,
	   p_last_update_date => sysdate,
	   p_last_updated_by => l_userid,
	   p_last_update_login => l_loginid,
	   p_created_by => l_userid,
	   p_creation_date => sysdate);

Line: 889


      l_updated_record := FND_API.G_FALSE;

Line: 895


	Update_Row
	      (p_api_version => 1.0,
	       p_return_status => l_return_status,
	       p_msg_count => p_msg_count,
	       p_msg_data => p_msg_data,
	       p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
	       p_effective_end_date => p_effective_end_date,
	       p_element_value_type => p_element_value_type,
	       p_element_value => p_element_value,
	       p_pay_basis => p_pay_basis,
	       p_formula_id => p_formula_id,
	       p_maximum_value => p_maximum_value,
	       p_mid_value => p_mid_value,
	       p_minimum_value => p_minimum_value,
	       p_last_update_date => sysdate,
	       p_last_updated_by => l_userid,
	       p_last_update_login => l_loginid);

Line: 926


	  Update_Row
		(p_api_version => 1.0,
		 p_return_status => l_return_status,
		 p_msg_count => p_msg_count,
		 p_msg_data => p_msg_data,
		 p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
		 p_effective_end_date => p_effective_start_date - 1,
		 p_element_value_type => g_element_rates(l_rate_index).element_value_type,
		 p_element_value => g_element_rates(l_rate_index).element_value,
		 p_pay_basis => g_element_rates(l_rate_index).pay_basis,
		 p_formula_id => g_element_rates(l_rate_index).formula_id,
		 p_maximum_value => g_element_rates(l_rate_index).maximum_value,
		 p_mid_value => g_element_rates(l_rate_index).mid_value,
		 p_minimum_value => g_element_rates(l_rate_index).minimum_value,
		 p_last_update_date => sysdate,
		 p_last_updated_by => l_userid,
		 p_last_update_login => l_loginid);

Line: 947


	    l_updated_record := FND_API.G_TRUE;

Line: 957


	  Update_Row
		(p_api_version => 1.0,
		 p_return_status => l_return_status,
		 p_msg_count => p_msg_count,
		 p_msg_data => p_msg_data,
		 p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
		 p_effective_start_date => p_effective_end_date + 1,
		 p_element_value_type => g_element_rates(l_rate_index).element_value_type,
		 p_element_value => g_element_rates(l_rate_index).element_value,
		 p_pay_basis => g_element_rates(l_rate_index).pay_basis,
		 p_formula_id => g_element_rates(l_rate_index).formula_id,
		 p_maximum_value => g_element_rates(l_rate_index).maximum_value,
		 p_mid_value => g_element_rates(l_rate_index).mid_value,
		 p_minimum_value => g_element_rates(l_rate_index).minimum_value,
		 p_last_update_date => sysdate,
		 p_last_updated_by => l_userid,
		 p_last_update_login => l_loginid);

Line: 978


	    l_updated_record := FND_API.G_TRUE;

Line: 991


	  Insert_Row
		(p_api_version => 1.0,
		 p_return_status => l_return_status,
		 p_msg_count => p_msg_count,
		 p_msg_data => p_msg_data,
		 p_pay_element_rate_id => l_pay_element_rate_id,
		 p_pay_element_option_id => p_pay_element_option_id,
		 p_pay_element_id => p_pay_element_id,
		 p_effective_start_date => p_effective_start_date,
		 p_effective_end_date => p_effective_end_date,
		 p_worksheet_id => p_worksheet_id,
		 p_element_value_type => p_element_value_type,
		 p_element_value => p_element_value,
		 p_pay_basis => p_pay_basis,
		 p_formula_id => p_formula_id,
		 p_maximum_value => p_maximum_value,
		 p_mid_value => p_mid_value,
		 p_minimum_value => p_minimum_value,
		 p_currency_code => p_currency_code,
		 p_last_update_date => sysdate,
		 p_last_updated_by => l_userid,
		 p_last_update_login => l_loginid,
		 p_created_by => l_userid,
		 p_creation_date => sysdate);

Line: 1031


	    if FND_API.to_Boolean(l_updated_record) then
	    begin

	      for c_Seq_Rec in c_Seq loop
		l_pay_element_rate_id := c_Seq_Rec.RateID;

Line: 1038


	      Insert_Row
		    (p_api_version => 1.0,
		     p_return_status => l_return_status,
		     p_msg_count => p_msg_count,
		     p_msg_data => p_msg_data,
		     p_pay_element_rate_id => l_pay_element_rate_id,
		     p_pay_element_option_id => g_element_rates(l_rate_index).pay_element_option_id,
		     p_pay_element_id => g_element_rates(l_rate_index).pay_element_id,
		     p_effective_start_date => p_effective_end_date + 1,
		     p_effective_end_date => g_element_rates(l_rate_index).effective_end_date,
		     p_worksheet_id => g_element_rates(l_rate_index).worksheet_id,
		     p_element_value_type => g_element_rates(l_rate_index).element_value_type,
		     p_element_value => g_element_rates(l_rate_index).element_value,
		     p_pay_basis => g_element_rates(l_rate_index).pay_basis,
		     p_formula_id => g_element_rates(l_rate_index).formula_id,
		     p_maximum_value => g_element_rates(l_rate_index).maximum_value,
		     p_mid_value => g_element_rates(l_rate_index).mid_value,
		     p_minimum_value => g_element_rates(l_rate_index).minimum_value,
		     p_currency_code => g_element_rates(l_rate_index).currency_code,
		     p_last_update_date => sysdate,
		     p_last_updated_by => l_userid,
		     p_last_update_login => l_loginid,
		     p_created_by => l_userid,
		     p_creation_date => sysdate);

Line: 1071


	      Update_Row
		    (p_api_version => 1.0,
		     p_return_status => l_return_status,
		     p_msg_count => p_msg_count,
		     p_msg_data => p_msg_data,
		     p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
		     p_effective_start_date => p_effective_end_date + 1,
		     p_effective_end_date => g_element_rates(l_rate_index).effective_end_date,
		     p_element_value_type => g_element_rates(l_rate_index).element_value_type,
		     p_element_value => g_element_rates(l_rate_index).element_value,
		     p_pay_basis => g_element_rates(l_rate_index).pay_basis,
		     p_formula_id => g_element_rates(l_rate_index).formula_id,
		     p_maximum_value => g_element_rates(l_rate_index).maximum_value,
		     p_mid_value => g_element_rates(l_rate_index).mid_value,
		     p_minimum_value => g_element_rates(l_rate_index).minimum_value,
		     p_last_update_date => sysdate,
		     p_last_updated_by => l_userid,
		     p_last_update_login => l_loginid);

Line: 1119


	Delete_Row
	      (p_api_version => 1.0,
	       p_return_status => l_return_status,
	       p_msg_count => p_msg_count,
	       p_msg_data => p_msg_data,
	       p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id);

Line: 1246


  SELECT count(*) INTO l_count_end_dates
  FROM   psb_pay_element_rates
  WHERE  pay_element_id = p_pay_element_id
  AND    (
	   p_pay_element_option_id IS NULL
	   OR
	   pay_element_option_id = p_pay_element_option_id
	 )
  AND    effective_end_date IS NULL ;

Line: 1266


    SELECT pay_element_rate_id, effective_start_date
    FROM   psb_pay_element_rates
    WHERE  pay_element_id = p_pay_element_id
    AND    (
	     p_pay_element_option_id IS NULL
	     OR
	     pay_element_option_id = p_pay_element_option_id
	   )
  )
  LOOP
    --

    SELECT count(*) INTO l_count
    FROM   psb_pay_element_rates
    WHERE  pay_element_id = p_pay_element_id
    AND    (
	     p_pay_element_option_id IS NULL
	     OR
	     pay_element_option_id = p_pay_element_option_id
	   )
    AND    pay_element_rate_id <> l_rates_rec.pay_element_rate_id
    AND    (
	      (
		effective_end_date IS NULL AND
		l_rates_rec.effective_start_date >= effective_start_date
	      )
	      OR
	      (
		l_rates_rec.effective_start_date
		BETWEEN effective_start_date AND effective_end_date
	      )
	   ) ;