APPS.JTF_CAL_SHIFTS_PKG SQL Statements

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

Line: 3

procedure INSERT_ROW (
  X_ERROR out NOCOPY VARCHAR2,
  X_ROWID in out NOCOPY VARCHAR2,
  X_SHIFT_ID in out NOCOPY NUMBER,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_START_DATE_ACTIVE in DATE,
  X_END_DATE_ACTIVE in DATE,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_SHIFT_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is

	v_error CHAR := 'N';

Line: 37

  cursor C is select ROWID from JTF_CAL_SHIFTS_B
    where SHIFT_ID = X_SHIFT_ID;

Line: 85

		SELECT 	JTF_CAL_SHIFTS_S.nextval
		INTO	v_shift_id
		FROM	dual;

Line: 98

			  	    	'INSERT_ROW',
			  	    	'B',
			  	    	'C')
			        THEN
			              JTF_CAL_SHIFT_CUHK.insert_shift_pre
			              (
			              X_ERROR => X_ERROR,
			              X_ROWID => X_ROWID,
			              X_SHIFT_ID => X_SHIFT_ID,
			              X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
			              X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
			              X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
			              X_ATTRIBUTE1 => X_ATTRIBUTE1,
			              X_ATTRIBUTE2 => X_ATTRIBUTE2,
			              X_ATTRIBUTE3 => X_ATTRIBUTE3,
			              X_ATTRIBUTE4 => X_ATTRIBUTE4,
			              X_ATTRIBUTE5 => X_ATTRIBUTE5,
			              X_ATTRIBUTE6 => X_ATTRIBUTE6,
			              X_ATTRIBUTE7 => X_ATTRIBUTE7,
			              X_ATTRIBUTE8 => X_ATTRIBUTE8 ,
			              X_ATTRIBUTE9 => X_ATTRIBUTE9,
			              X_ATTRIBUTE10 => X_ATTRIBUTE10,
			              X_ATTRIBUTE11 => X_ATTRIBUTE11,
			              X_ATTRIBUTE12 => X_ATTRIBUTE12 ,
			              X_ATTRIBUTE13 => X_ATTRIBUTE13,
			              X_ATTRIBUTE14 => X_ATTRIBUTE14,
			              X_ATTRIBUTE15 => X_ATTRIBUTE15,
			              X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
			              X_SHIFT_NAME => X_SHIFT_NAME,
			              X_DESCRIPTION => X_DESCRIPTION,
			              X_CREATION_DATE => X_CREATION_DATE,
			              X_CREATED_BY => X_CREATED_BY,
			              X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
			              X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
			              X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
			              );

Line: 135

			              END IF;  -- End of User Hook Check for INSERT

Line: 139

			  insert into JTF_CAL_SHIFTS_B (
			    OBJECT_VERSION_NUMBER,
			    SHIFT_ID,
			    START_DATE_ACTIVE,
			    END_DATE_ACTIVE,
			    ATTRIBUTE1,
			    ATTRIBUTE2,
			    ATTRIBUTE3,
			    ATTRIBUTE4,
			    ATTRIBUTE5,
			    ATTRIBUTE6,
			    ATTRIBUTE7,
			    ATTRIBUTE8,
			    ATTRIBUTE9,
			    ATTRIBUTE10,
			    ATTRIBUTE11,
			    ATTRIBUTE12,
			    ATTRIBUTE13,
			    ATTRIBUTE14,
			    ATTRIBUTE15,
			    ATTRIBUTE_CATEGORY,
			    CREATION_DATE,
			    CREATED_BY,
			    LAST_UPDATE_DATE,
			    LAST_UPDATED_BY,
			    LAST_UPDATE_LOGIN
			  ) values (
			    1,
			    v_shift_id,
			    X_START_DATE_ACTIVE,
			    X_END_DATE_ACTIVE,
			    X_ATTRIBUTE1,
			    X_ATTRIBUTE2,
			    X_ATTRIBUTE3,
			    X_ATTRIBUTE4,
			    X_ATTRIBUTE5,
			    X_ATTRIBUTE6,
			    X_ATTRIBUTE7,
			    X_ATTRIBUTE8,
			    X_ATTRIBUTE9,
			    X_ATTRIBUTE10,
			    X_ATTRIBUTE11,
			    X_ATTRIBUTE12,
			    X_ATTRIBUTE13,
			    X_ATTRIBUTE14,
			    X_ATTRIBUTE15,
			    X_ATTRIBUTE_CATEGORY,
			    SYSDATE,
			   FND_GLOBAL.USER_ID,
			    SYSDATE,
			    FND_GLOBAL.USER_ID,
      	                  NULL
			  );

Line: 193

			  insert into JTF_CAL_SHIFTS_TL (
			    SHIFT_ID,
			    SHIFT_NAME,
			    DESCRIPTION,
			    CREATED_BY,
			    CREATION_DATE,
			    LAST_UPDATED_BY,
			    LAST_UPDATE_DATE,
			    LAST_UPDATE_LOGIN,
			    LANGUAGE,
			    SOURCE_LANG
			  ) select
			    v_shift_id,
			    X_SHIFT_NAME,
			    X_DESCRIPTION,
			    FND_GLOBAL.USER_ID,
			    SYSDATE,
			    FND_GLOBAL.USER_ID,
			    SYSDATE,
			    NULL,
			    L.LANGUAGE_CODE,
			    userenv('LANG')
			  from FND_LANGUAGES L
			  where L.INSTALLED_FLAG in ('I', 'B')
			  and not exists
			    (select NULL
			    from JTF_CAL_SHIFTS_TL T
			    where T.SHIFT_ID = X_SHIFT_ID
			    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 232

end INSERT_ROW;

Line: 258

  cursor c is select
      OBJECT_VERSION_NUMBER,
      START_DATE_ACTIVE,
      END_DATE_ACTIVE,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15,
      ATTRIBUTE_CATEGORY
    from JTF_CAL_SHIFTS_B
    where SHIFT_ID = X_SHIFT_ID
    for update of SHIFT_ID nowait;

Line: 283

  cursor c1 is select
      SHIFT_NAME,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from JTF_CAL_SHIFTS_TL
    where SHIFT_ID = X_SHIFT_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of SHIFT_ID nowait;

Line: 296

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

Line: 360

procedure UPDATE_ROW (
  X_ERROR out NOCOPY VARCHAR2,
  X_SHIFT_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER in OUT NOCOPY NUMBER,
  X_START_DATE_ACTIVE in DATE,
  X_END_DATE_ACTIVE in DATE,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_ATTRIBUTE15 in VARCHAR2,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_SHIFT_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

	v_error CHAR := 'N';

Line: 422

        	-- Add User Hook Check for UPDATE by Jane Wang on 01/25/02

        	IF jtf_usr_hks.ok_to_execute(
	    		'JTF_CAL_SHIFTS_PKG',
				'UPDATE_ROW',
				'B',
				'C')
			THEN
					JTF_CAL_SHIFT_CUHK.update_shift_pre
		            (X_ERROR => X_ERROR,
		            X_SHIFT_ID => X_SHIFT_ID,
		            X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
		            X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
		            X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
		            X_ATTRIBUTE1 => X_ATTRIBUTE1,
		            X_ATTRIBUTE2 => X_ATTRIBUTE2,
		            X_ATTRIBUTE3 => X_ATTRIBUTE3,
		            X_ATTRIBUTE4 => X_ATTRIBUTE4,
		            X_ATTRIBUTE5 => X_ATTRIBUTE5,
		            X_ATTRIBUTE6 => X_ATTRIBUTE6,
		            X_ATTRIBUTE7 => X_ATTRIBUTE7,
		            X_ATTRIBUTE8 => X_ATTRIBUTE8,
		            X_ATTRIBUTE9 => X_ATTRIBUTE9,
		            X_ATTRIBUTE10 => X_ATTRIBUTE10,
		            X_ATTRIBUTE11 => X_ATTRIBUTE11,
		            X_ATTRIBUTE12 => X_ATTRIBUTE12,
		            X_ATTRIBUTE13 => X_ATTRIBUTE13,
		            X_ATTRIBUTE14 => X_ATTRIBUTE14,
		            X_ATTRIBUTE15 => X_ATTRIBUTE15,
		            X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
		            X_SHIFT_NAME => X_SHIFT_NAME,
		            X_DESCRIPTION => X_DESCRIPTION,
		            X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
		            X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
		            X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
		            );

Line: 459

            END IF;  -- End of User Hook Check for UPDATE

Line: 464

		  update JTF_CAL_SHIFTS_B set
		    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
		    START_DATE_ACTIVE = X_START_DATE_ACTIVE,
		    END_DATE_ACTIVE = X_END_DATE_ACTIVE,
		    ATTRIBUTE1 = X_ATTRIBUTE1,
		    ATTRIBUTE2 = X_ATTRIBUTE2,
		    ATTRIBUTE3 = X_ATTRIBUTE3,
		    ATTRIBUTE4 = X_ATTRIBUTE4,
		    ATTRIBUTE5 = X_ATTRIBUTE5,
		    ATTRIBUTE6 = X_ATTRIBUTE6,
		    ATTRIBUTE7 = X_ATTRIBUTE7,
		    ATTRIBUTE8 = X_ATTRIBUTE8,
		    ATTRIBUTE9 = X_ATTRIBUTE9,
		    ATTRIBUTE10 = X_ATTRIBUTE10,
		    ATTRIBUTE11 = X_ATTRIBUTE11,
		    ATTRIBUTE12 = X_ATTRIBUTE12,
		    ATTRIBUTE13 = X_ATTRIBUTE13,
		    ATTRIBUTE14 = X_ATTRIBUTE14,
		    ATTRIBUTE15 = X_ATTRIBUTE15,
		    ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
		    LAST_UPDATE_DATE = SYSDATE,
		    LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
		    LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
		  where SHIFT_ID = X_SHIFT_ID;

Line: 493

		  update JTF_CAL_SHIFTS_TL set
		    SHIFT_NAME = X_SHIFT_NAME,
		    DESCRIPTION = X_DESCRIPTION,
		    LAST_UPDATE_DATE = SYSDATE,
		    LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
		    LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
		    SOURCE_LANG = userenv('LANG')
		  where SHIFT_ID = X_SHIFT_ID
		  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 507

end UPDATE_ROW;

Line: 513

 X_LAST_UPDATE_DATE in date,
 X_LAST_UPDATED_BY in number,
 X_LAST_UPDATE_LOGIN in number)
is
begin

Update JTF_CAL_SHIFTS_TL set
shift_name		= nvl(X_SHIFT_NAME,shift_name),
description		= nvl(X_DESCRIPTION,description),
last_update_date	= nvl(x_last_update_date,sysdate),
last_updated_by		= x_last_updated_by,
last_update_login	= 0,
source_lang		= userenv('LANG')
where shift_id		= X_SHIFT_ID
and userenv('LANG') in (LANGUAGE,SOURCE_LANG);

Line: 531

procedure DELETE_ROW (
  X_SHIFT_ID in NUMBER
) is
begin

 --commented the user hook code as this is not to be implemented
 -- start of comment
/*

    --  Add User Hook Check for DELETE by Jane Wang on 01/25/02

     IF jtf_usr_hks.ok_to_execute(
          'JTF_CAL_SHIFTS_PKG',
          'DELETE_ROW',
          'B',
          'C')
     THEN
          JTF_CAL_SHIFT_CUHK.delete_shift_pre
          (
           X_SHIFT_ID => X_SHIFT_ID
          );

Line: 552

    END IF;  -- End of User Hook for DELETE

Line: 556

  delete from JTF_CAL_SHIFTS_TL
  where SHIFT_ID = X_SHIFT_ID;

Line: 563

  delete from JTF_CAL_SHIFTS_B
  where SHIFT_ID = X_SHIFT_ID;

Line: 569

end DELETE_ROW;

Line: 574

  delete from JTF_CAL_SHIFTS_TL T
  where not exists
    (select NULL
    from JTF_CAL_SHIFTS_B B
    where B.SHIFT_ID = T.SHIFT_ID
    );

Line: 581

  update JTF_CAL_SHIFTS_TL T set (
      SHIFT_NAME,
      DESCRIPTION
    ) = (select
      B.SHIFT_NAME,
      B.DESCRIPTION
    from JTF_CAL_SHIFTS_TL B
    where B.SHIFT_ID = T.SHIFT_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.SHIFT_ID,
      T.LANGUAGE
  ) in (select
      SUBT.SHIFT_ID,
      SUBT.LANGUAGE
    from JTF_CAL_SHIFTS_TL SUBB, JTF_CAL_SHIFTS_TL SUBT
    where SUBB.SHIFT_ID = SUBT.SHIFT_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.SHIFT_NAME <> SUBT.SHIFT_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: 605

  insert into JTF_CAL_SHIFTS_TL (
    SHIFT_ID,
    SHIFT_NAME,
    DESCRIPTION,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.SHIFT_ID,
    B.SHIFT_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 JTF_CAL_SHIFTS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from JTF_CAL_SHIFTS_TL T
    where T.SHIFT_ID = B.SHIFT_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);