APPS.AMS_TCOP_PERIODS_PKG SQL Statements

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

Line: 3

procedure INSERT_ROW (
	X_ROWID IN OUT NOCOPY VARCHAR2,
	X_PERIOD_ID IN NUMBER,
	X_NO_OF_DAYS IN NUMBER,
	X_ENABLED_FLAG IN VARCHAR2,
	X_PERIOD_NAME IN VARCHAR2,
	X_DESCRIPTION in VARCHAR2,
	X_LAST_UPDATE_DATE in DATE,
	X_LAST_UPDATED_BY in NUMBER,
	X_CREATION_DATE in DATE,
	X_CREATED_BY in NUMBER,
	X_LAST_UPDATE_LOGIN in NUMBER,
	X_OBJECT_VERSION_NUMBER in NUMBER
) is
  cursor c is select ROWID from AMS_TCOP_FR_PERIODS_B
    where PERIOD_ID = X_PERIOD_ID
    ;

Line: 20

  cursor ctl is select ROWID from AMS_TCOP_FR_PERIODS_TL
    where PERIOD_ID = X_PERIOD_ID
    ;

Line: 24

	insert into AMS_TCOP_FR_PERIODS_B (
		PERIOD_ID,
		NO_OF_DAYS,
		ENABLED_FLAG,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		CREATION_DATE,
		CREATED_BY,
		LAST_UPDATE_LOGIN,
		OBJECT_VERSION_NUMBER
	) values (
		X_PERIOD_ID,
		X_NO_OF_DAYS,
		X_ENABLED_FLAG,
		DECODE(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_LAST_UPDATE_DATE),
		DECODE(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATED_BY),
		DECODE(X_CREATION_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_CREATION_DATE),
		DECODE(X_CREATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_CREATED_BY),
		DECODE(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATE_LOGIN),
		X_OBJECT_VERSION_NUMBER
	);

Line: 54

	insert into AMS_TCOP_FR_PERIODS_TL (
		PERIOD_ID,
		PERIOD_NAME,
		DESCRIPTION,
		LANGUAGE,
		SOURCE_LANG,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		CREATION_DATE,
		CREATED_BY,
		LAST_UPDATE_LOGIN
	)
	SELECT
	     X_PERIOD_ID
	    ,X_PERIOD_NAME
	    ,DECODE(X_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,X_DESCRIPTION)
	    ,l.language_code
	    ,USERENV('lang')
	    ,DECODE(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_LAST_UPDATE_DATE)
	    ,DECODE(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATED_BY)
	    ,DECODE(X_CREATION_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_CREATION_DATE)
	    ,DECODE(X_CREATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_CREATED_BY)
	    ,DECODE(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATE_LOGIN)
  FROM fnd_languages l
  WHERE l.installed_flag IN ('I', 'B')
  AND NOT EXISTS
    (SELECT NULL
    FROM AMS_TCOP_FR_PERIODS_TL T
    WHERE T.PERIOD_ID = X_PERIOD_ID
    AND T.LANGUAGE = l.language_code);

Line: 93

end INSERT_ROW;

Line: 106

  cursor c is select
		OBJECT_VERSION_NUMBER,
		NO_OF_DAYS,
		ENABLED_FLAG,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		CREATION_DATE,
		CREATED_BY,
		LAST_UPDATE_LOGIN
    from AMS_TCOP_FR_PERIODS_B
    where PERIOD_ID = X_PERIOD_ID
    for update of PERIOD_ID nowait;

Line: 122

  CURSOR ctl IS SELECT
      PERIOD_NAME,
      DESCRIPTION,
      DECODE(LANGUAGE, USERENV('lang'), 'Y', 'N') baselang
    FROM AMS_TCOP_FR_PERIODS_TL
    WHERE PERIOD_ID = X_PERIOD_ID
    AND USERENV('lang') IN (LANGUAGE, source_lang)
    FOR UPDATE OF PERIOD_ID NOWAIT;

Line: 137

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 173

procedure UPDATE_ROW (
	X_PERIOD_ID IN NUMBER,
	X_OBJECT_VERSION_NUMBER in NUMBER,
	X_NO_OF_DAYS IN NUMBER,
	X_ENABLED_FLAG IN VARCHAR2,
	X_PERIOD_NAME IN VARCHAR2,
	X_DESCRIPTION in VARCHAR2,
	X_LAST_UPDATE_DATE in DATE,
	X_LAST_UPDATED_BY in NUMBER,
	X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
  update AMS_TCOP_FR_PERIODS_B set
		OBJECT_VERSION_NUMBER	= X_OBJECT_VERSION_NUMBER
		,NO_OF_DAYS				= X_NO_OF_DAYS
		,ENABLED_FLAG				= X_ENABLED_FLAG
		,LAST_UPDATE_DATE			= X_LAST_UPDATE_DATE
		,LAST_UPDATED_BY			= X_LAST_UPDATED_BY
		,LAST_UPDATE_LOGIN		= X_LAST_UPDATE_LOGIN
  where PERIOD_ID           = X_PERIOD_ID;

Line: 198

  update AMS_TCOP_FR_PERIODS_TL set
    PERIOD_NAME = X_PERIOD_NAME,
    DESCRIPTION = X_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = USERENV('LANG')
  where PERIOD_ID = X_PERIOD_ID
  and USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 212

end UPDATE_ROW;

Line: 215

procedure DELETE_ROW (
	X_PERIOD_ID IN NUMBER
) is
begin
	delete from AMS_TCOP_FR_PERIODS_TL
	where PERIOD_ID = X_PERIOD_ID;

Line: 226

	delete from AMS_TCOP_FR_PERIODS_B
		where PERIOD_ID = X_PERIOD_ID;

Line: 233

end DELETE_ROW;

Line: 248

l_last_updated_by number;

Line: 254

  select OBJECT_VERSION_NUMBER,
	 last_updated_by
  from   AMS_TCOP_FR_PERIODS_B
  where  PERIOD_ID =  X_PERIOD_ID;

Line: 260

  select 'x'
  from   AMS_TCOP_FR_PERIODS_B
  where  PERIOD_ID = X_PERIOD_ID;

Line: 265

  select 'x'
  from   AMS_TCOP_FR_PERIODS_TL
  where  PERIOD_ID = X_PERIOD_ID;

Line: 287

    AMS_TCOP_PERIODS_PKG.INSERT_ROW (
			X_ROWID			=>	l_row_id,
			X_PERIOD_ID		=>	X_PERIOD_ID,
			X_NO_OF_DAYS		=>	X_NO_OF_DAYS,
			X_ENABLED_FLAG		=>	X_ENABLED_FLAG,
			X_PERIOD_NAME		=>	X_PERIOD_NAME,
			X_DESCRIPTION		=>	X_DESCRIPTION,
			X_LAST_UPDATE_DATE	=>	sysdate,
			X_LAST_UPDATED_BY	=>	l_user_id,
			X_CREATION_DATE		=>	sysdate,
			X_CREATED_BY		=>	l_user_id,
			X_LAST_UPDATE_LOGIN	=>	0,
			X_OBJECT_VERSION_NUMBER =>	l_obj_verno
		);

Line: 305

   fetch c_obj_verno into l_obj_verno,l_last_updated_by;

Line: 308

   if (l_last_updated_by in (1,2,0) OR
       NVL(x_custom_mode,'PRESERVE')='FORCE') THEN

       AMS_TCOP_PERIODS_PKG.UPDATE_ROW(
		X_PERIOD_ID		=>	X_PERIOD_ID,
		X_NO_OF_DAYS		=>	X_NO_OF_DAYS,
		X_ENABLED_FLAG		=>	X_ENABLED_FLAG,
		X_PERIOD_NAME		=>	X_PERIOD_NAME,
		X_DESCRIPTION		=>	X_DESCRIPTION,
		X_LAST_UPDATE_DATE      =>    SYSDATE,
		X_LAST_UPDATED_BY       =>    l_user_id,
		X_LAST_UPDATE_LOGIN     =>    0,
		X_OBJECT_VERSION_NUMBER =>    l_obj_verno + 1
         );

Line: 334

    cursor c_last_updated_by is
    select last_updated_by
    from AMS_TCOP_FR_PERIODS_TL
    where PERIOD_ID = X_PERIOD_ID
    and  USERENV('LANG') = LANGUAGE;

Line: 340

    l_last_updated_by number;

Line: 344

  open c_last_updated_by;

Line: 345

  fetch c_last_updated_by into l_last_updated_by;

Line: 346

  close c_last_updated_by;

Line: 349

  if (l_last_updated_by in (1,2,0) OR
       NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
  -- Only update rows which have not been altered by user

     UPDATE AMS_TCOP_FR_PERIODS_TL
     SET description = X_DESCRIPTION,
      period_name = X_PERIOD_NAME,
      source_lang = USERENV('LANG'),
      last_update_date = SYSDATE,
      last_updated_by = DECODE(X_OWNER, 'SEED', 1,
			       'ORACLE',2,
			       'SYSADMIN',0 , -1),
      last_update_login = 0
    WHERE PERIOD_ID = X_PERIOD_ID
    AND USERENV('LANG') IN (LANGUAGE, source_lang);

Line: 373

  DELETE FROM AMS_TCOP_FR_PERIODS_TL T
  WHERE NOT EXISTS
    (SELECT NULL
     FROM AMS_TCOP_FR_PERIODS_B B
     WHERE B.PERIOD_ID = T.PERIOD_ID
    );

Line: 380

  UPDATE AMS_TCOP_FR_PERIODS_TL T SET (
      PERIOD_NAME,
      DESCRIPTION
    ) =
	 (SELECT
      T1.PERIOD_NAME,
      T1.DESCRIPTION
    FROM AMS_TCOP_FR_PERIODS_TL T1
    WHERE T1.PERIOD_ID = T.PERIOD_ID
    AND T1.LANGUAGE = T.SOURCE_LANG)
  WHERE (
      T.PERIOD_ID,
      T.LANGUAGE
   ) IN
   (SELECT
      subt.PERIOD_ID,
      subt.LANGUAGE
    FROM AMS_TCOP_FR_PERIODS_TL subb, AMS_TCOP_FR_PERIODS_TL subt
    WHERE subb.PERIOD_ID = subt.PERIOD_ID
    AND subb.LANGUAGE = subt.SOURCE_LANG
    AND (subb.PERIOD_NAME <> subt.PERIOD_NAME
      OR subb.DESCRIPTION <> subt.DESCRIPTION
      OR (subb.DESCRIPTION IS NULL AND subt.DESCRIPTION IS NOT NULL)
      OR (subb.DESCRIPTION IS NOT NULL AND subt.DESCRIPTION IS NULL)
  ));

Line: 406

  INSERT INTO AMS_TCOP_FR_PERIODS_TL (
	PERIOD_ID,
	PERIOD_NAME,
	DESCRIPTION,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATE_LOGIN,
	LANGUAGE,
	SOURCE_LANG
  )
  SELECT /*+ ordered */
	b.PERIOD_ID,
	b.PERIOD_NAME,
	b.DESCRIPTION,
	b.CREATED_BY,
	b.CREATION_DATE,
	b.LAST_UPDATED_BY,
	b.LAST_UPDATE_DATE,
	b.LAST_UPDATE_LOGIN,
	l.language_code,
	b.SOURCE_LANG
  FROM AMS_TCOP_FR_PERIODS_TL b, fnd_languages l
  WHERE l.installed_flag IN ('I', 'B')
  AND b.LANGUAGE = USERENV('lang')
  AND NOT EXISTS
    (SELECT NULL
    FROM AMS_TCOP_FR_PERIODS_TL T
    WHERE T.PERIOD_ID = b.PERIOD_ID
    AND T.LANGUAGE = l.language_code);