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

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

Line: 62


FUNCTION seed_needs_update(
	l_user_status_rec	IN	  User_Status_Rec_Type
) RETURN VARCHAR2;   -- FND_API.g_true/g_false

Line: 96


      SELECT ams_user_statuses_b_s.NEXTVAL
      FROM   dual;

Line: 99


      SELECT 1
      FROM   dual
      WHERE EXISTS (SELECT 1
                    FROM   ams_user_statuses_vl
                    WHERE  user_status_id = x_id);

Line: 171


   AMS_Utility_PVT.debug_message (l_full_name || ': Insert');

Line: 176


   INSERT INTO ams_user_statuses_b (
	   user_status_id,
	   -- standard who columns
 	   last_update_date,
 	   last_updated_by,
 	   creation_date,
 	   created_by,
	   last_update_login,
           object_version_number,
	   system_status_type,
           system_status_code,
	   enabled_flag,
           default_flag,
	   seeded_flag,
 	   start_date_active,
           end_date_active,
           application_id
	)
	VALUES (
	   l_user_status_rec.user_status_id,
	   -- standard who columns
	   SYSDATE,
	   FND_GLOBAL.User_Id,
	   SYSDATE,
	   FND_GLOBAL.User_Id,
	   FND_GLOBAL.Conc_Login_Id,
           1,    -- object_version_number
	   l_user_status_rec.system_status_type,
           l_user_status_rec.system_status_code,
 	   NVL (l_user_status_rec.enabled_flag, 'Y'),   -- Default is 'Y'
 	   NVL (l_user_status_rec.default_flag, 'N'),   -- Default is 'N'
	   NVL (l_user_status_rec.seeded_flag, 'N'),   -- Default is 'N'
 	   l_user_status_rec.start_date_active,
 	   l_user_status_rec.end_date_active,
           fnd_global.resp_appl_id -- added to capture application_id
	);

Line: 212


	INSERT INTO ams_user_statuses_tl (
 	   user_status_id,
           language,
 	   last_update_date,
 	   last_updated_by,
 	   creation_date,
 	   created_by,
 	   last_update_login,
 	   source_lang,
	   name,
 	   description
   )
   SELECT   l_user_status_rec.user_status_id,
	         l.language_code,
            -- standard who columns
	         SYSDATE,
	         FND_GLOBAL.User_Id,
	         SYSDATE,
	         FND_GLOBAL.User_Id,
	         FND_GLOBAL.Conc_Login_Id,
                 USERENV('LANG'),
	         l_user_status_rec.name,
	         l_user_status_rec.description
  	FROM     fnd_languages l
  	WHERE    l.installed_flag IN ('I', 'B')
  	AND NOT EXISTS (SELECT  NULL
    		          FROM    ams_user_statuses_tl t
    		          WHERE   t.user_status_id = l_user_status_rec.user_status_id
    		          AND     t.language = l.language_code);

Line: 295


PROCEDURE Update_User_Status (
   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,
   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,
   p_user_status_rec   IN  User_Status_Rec_Type
)
IS
   L_API_VERSION        CONSTANT NUMBER := 1.0;

Line: 307


   L_API_NAME           CONSTANT VARCHAR2(30) := 'Update_User_Status';

Line: 314


   SAVEPOINT Update_User_Status;

Line: 352


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

Line: 377


      UPDATE ams_user_statuses_b
      SET
            default_flag = 'N'
      WHERE system_status_type	= l_user_status_rec.system_status_type
        AND system_status_code	= l_user_status_rec.system_status_code;

Line: 390


		seed_needs_update(l_user_status_rec) = FND_API.g_true THEN
   -------------------------- update --------------------
   IF (AMS_DEBUG_HIGH_ON) THEN

   AMS_Utility_PVT.debug_message (l_full_name || ': Update');

Line: 409


	UPDATE ams_user_statuses_b
	SET
		last_update_date	= SYSDATE,
		last_updated_by 	= FND_GLOBAL.User_Id,
		last_update_login       = FND_GLOBAL.Conc_Login_Id,
		object_version_number   = object_version_number + 1,
	        system_status_type	= l_user_status_rec.system_status_type,
	        system_status_code	= l_user_status_rec.system_status_code,
		enabled_flag 		= NVL (l_user_status_rec.enabled_flag, 'Y'),
		default_flag            = NVL (l_user_status_rec.default_flag, 'N'),
		seeded_flag            = NVL (l_user_status_rec.seeded_flag, 'N'),
		start_date_active       = l_user_status_rec.start_date_active,
		end_date_active       = l_user_status_rec.end_date_active
	WHERE	user_status_id = l_user_status_rec.user_status_id
	        AND object_version_number = l_user_status_rec.object_version_number;

Line: 431


	UPDATE ams_user_statuses_tl
   SET
      last_update_date 	= SYSDATE,
		last_updated_by 	= FND_GLOBAL.User_Id,
		last_update_login = FND_GLOBAL.Conc_Login_Id,
    	source_lang    	= USERENV('LANG'),
		name   		= l_user_status_rec.name,
    	description 		= l_user_status_rec.description
  	WHERE user_status_id = l_user_status_rec.user_status_id
  	AND   USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 465


      ROLLBACK TO Update_User_Status;

Line: 473


      ROLLBACK TO Update_User_Status;

Line: 481


      ROLLBACK TO Update_User_Status;

Line: 492


END Update_User_Status;

Line: 498


PROCEDURE Delete_User_Status (
   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,
   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,
   p_user_status_id          IN  NUMBER,
   p_object_version    IN  NUMBER
)
IS
   CURSOR c_user_status IS
      SELECT   *
      FROM     ams_user_statuses_vl
      WHERE    user_status_id = p_user_status_id;

Line: 522


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

Line: 537


   SAVEPOINT Delete_User_Status;

Line: 557


   AMS_Utility_PVT.debug_message (l_full_name || ': Delete');

Line: 564


    DELETE FROM ams_user_statuses_tl
    WHERE  user_status_id = p_user_status_id;

Line: 574


    DELETE FROM ams_user_statuses_b
    WHERE  user_status_id = p_user_status_id
    AND    object_version_number = p_object_version;

Line: 603


      ROLLBACK TO Delete_User_Status;

Line: 611


      ROLLBACK TO Delete_User_Status;

Line: 619


      ROLLBACK TO Delete_User_Status;

Line: 630


END Delete_User_Status;

Line: 660


      SELECT object_version_number
      FROM   ams_user_statuses_vl
      WHERE  user_status_id = p_user_status_id
      AND    object_version_number = p_object_version
      FOR UPDATE NOWAIT;

Line: 993


   x_user_status_rec.last_update_date 	:= FND_API.g_miss_date;

Line: 994


   x_user_status_rec.last_updated_by 	:= FND_API.g_miss_num;

Line: 997


   x_user_status_rec.last_update_login 	:= FND_API.g_miss_num;

Line: 1020


      SELECT   *
      FROM     ams_user_statuses_vl
      WHERE    user_status_id = p_user_status_rec.user_status_id;

Line: 1113


				select 1 into l_count
				from ams_user_statuses_vl
				where user_status_id = l_user_status_rec.user_status_id
				and	  name = l_user_status_rec.name
				and   start_date_active = l_user_status_rec.start_date_active
				and   end_date_active = l_user_status_rec.end_Date_active
				and   system_status_type = l_user_status_rec.system_status_type
				and   system_status_code = l_user_status_rec.system_status_code
				and   seeded_flag = 'Y';

Line: 1128


				select 1 into l_count
				from ams_user_statuses_vl
				where user_status_id = l_user_status_rec.user_status_id
				and	  name = l_user_status_rec.name
				and   start_date_active = l_user_status_rec.start_date_active
				and   system_status_type = l_user_status_rec.system_status_type
				and   system_status_code = l_user_status_rec.system_status_code
				and   seeded_flag = 'Y';

Line: 1143


				select 1 into l_count
				from ams_user_statuses_vl
				where user_status_id = l_user_status_rec.user_status_id
				and	  name = l_user_status_rec.name
				and   system_status_type = l_user_status_rec.system_status_type
				and   system_status_code = l_user_status_rec.system_status_code
				and   seeded_flag = 'Y';

Line: 1170


FUNCTION seed_needs_update(
	l_user_status_rec	IN	  User_Status_Rec_Type
)
RETURN VARCHAR2
IS
  l_count NUMBER := 0;

Line: 1179


	select 1 into l_count
	from ams_user_statuses_vl
	where user_status_id = l_user_status_rec.user_status_id
	and	  enabled_flag = l_user_status_rec.enabled_flag
	and	  default_flag = l_user_status_rec.default_flag
	and   seeded_flag = 'Y';

Line: 1191


      RETURN FND_API.g_true;  -- needs update

Line: 1193


      RETURN FND_API.g_false;  -- doesnt need update

Line: 1195


END seed_needs_update;

Line: 1453


       l_stmt := 'SELECT count(1) from  '||l_table||' where user_status_id = :b1';

Line: 1458


        l_stmt := 'SELECT count(1) from  '||l_table||' where user_status_id = :b1';