APPS.PER_ASS_STATUSES_PKG SQL Statements

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

Line: 12

  X_LAST_UPDATE_DATE out nocopy DATE,
  X_LAST_UPDATED_BY out nocopy NUMBER,
  X_LAST_UPDATE_LOGIN out nocopy NUMBER
) is
begin
  if X_OWNER = 'SEED' then
    X_CREATED_BY := 1;

Line: 19

    X_LAST_UPDATED_BY := 1;

Line: 22

    X_LAST_UPDATED_BY := 0;

Line: 25

  X_LAST_UPDATE_DATE := sysdate;

Line: 26

  X_LAST_UPDATE_LOGIN := 0;

Line: 39

    select ORG.ORGANIZATION_ID
    from HR_ALL_ORGANIZATION_UNITS ORG
        ,HR_ORGANIZATION_INFORMATION OI1
    where ORG.ORGANIZATION_ID = OI1.ORGANIZATION_ID
    and OI1.ORG_INFORMATION_CONTEXT = 'CLASS'
    and OI1.ORG_INFORMATION1 = 'HR_BG'
    and OI1.ORG_INFORMATION2 = 'Y'
    and ORG.NAME = X_NAME;

Line: 52

    select AST.ASSIGNMENT_STATUS_TYPE_ID
    from PER_ASSIGNMENT_STATUS_TYPES AST
    where AST.USER_STATUS = X_USER_STATUS
    and (  AST.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
        or (   AST.BUSINESS_GROUP_ID is null
           and X_BUSINESS_GROUP_ID is null))
    and (  AST.LEGISLATION_CODE = X_LEGISLATION_CODE
        or (   AST.LEGISLATION_CODE is null
           and X_LEGISLATION_CODE is null));

Line: 62

  select max(ASSIGNMENT_STATUS_TYPE_ID)
    from per_assignment_status_types;

Line: 66

    select PER_ASSIGNMENT_STATUS_TYPES_S.nextval
    from   dual;

Line: 127

 	select  1
 	from    per_assignment_status_types_tl ttl,
                per_assignment_status_types t
 	where   upper(ttl.user_status) =  upper(P_USER_STATUS)
 	and     nvl(t.business_group_id, nvl(P_BUSINESS_GROUP_ID, -9999) )
        	  =  nvl(P_BUSINESS_GROUP_ID, -9999)
 	and     nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
        	  =  nvl(P_LEGISLATION_CODE, 'XXX')
 	and     (P_ROWID        is null
        	 or P_ROWID    <> t.rowid)
        and     t.assignment_status_type_id = ttl.assignment_status_type_id
        and     ttl.LANGUAGE = userenv('LANG')
 	and     not exists (
         	select  null
         	from    per_ass_status_type_amends a
         	where   a.assignment_status_type_id =
					t.assignment_status_type_id
         	and     a.business_group_id + 0 = P_BUSINESS_GROUP_ID);

Line: 146

	select  1
	from    per_ass_status_type_amends_tl atl,
                per_ass_status_type_amends a
	where   upper(atl.user_status)    = upper(P_USER_STATUS)
	and     a.business_group_id + 0     = P_BUSINESS_GROUP_ID
        and     a.ass_status_type_amend_id = atl.ass_status_type_amend_id
        and     atl.LANGUAGE = userenv('LANG')
	and     (P_ROWID is null
	or      a.assignment_status_type_id <> P_ASSIGNMENT_STATUS_TYPE_ID);

Line: 177

PROCEDURE PRE_UPDATE(P_ACTIVE_FLAG         VARCHAR2,
                     P_DEFAULT_FLAG        VARCHAR2,
                     P_USER_STATUS         VARCHAR2,
                     P_PAY_SYSTEM_STATUS   VARCHAR2,
                     P_LAST_UPDATE_DATE    DATE,
                     P_LAST_UPDATED_BY     NUMBER,
                     P_LAST_UPDATE_LOGIN   NUMBER,
                     P_CREATED_BY          NUMBER,
                     P_CREATION_DATE       DATE,
                     P_ASS_STATUS_TYPE_ID  NUMBER,
                     P_AMENDMENT           VARCHAR2) IS
BEGIN
   update per_ass_status_type_amends a
      set    a.active_flag           = P_ACTIVE_FLAG,
             a.default_flag          = P_DEFAULT_FLAG,
             a.user_status           = P_USER_STATUS,
             a.pay_system_status     = P_PAY_SYSTEM_STATUS,
             a.last_update_date      = P_LAST_UPDATE_DATE,
             a.last_updated_by       = P_LAST_UPDATED_BY,
             a.last_update_login     = P_LAST_UPDATE_LOGIN,
             a.created_by            = P_CREATED_BY,
             a.creation_date         = P_CREATION_DATE
      where  a.ass_status_type_amend_id = P_ASS_STATUS_TYPE_ID;

Line: 201

   update per_ass_status_type_amends_tl atl
      set    atl.user_status           = P_USER_STATUS,
             atl.last_update_date      = P_LAST_UPDATE_DATE,
             atl.last_updated_by       = P_LAST_UPDATED_BY,
             atl.last_update_login     = P_LAST_UPDATE_LOGIN,
             atl.created_by            = P_CREATED_BY,
             atl.creation_date         = P_CREATION_DATE
      where  atl.ass_status_type_amend_id = P_ASS_STATUS_TYPE_ID
      and    atl.LANGUAGE = userenv('LANG');

Line: 211

END PRE_UPDATE;

Line: 212

PROCEDURE INSERT_AMENDS(P_ASS_STATUS_TYPE_AMEND_ID IN OUT NOCOPY NUMBER,
                        P_ASSIGNMENT_STATUS_TYPE_ID NUMBER,
                        P_BUSINESS_GROUP_ID         NUMBER,
                        P_ACTIVE_FLAG               VARCHAR2,
                        P_DEFAULT_FLAG              VARCHAR2,
                        P_USER_STATUS               VARCHAR2,
                        P_PAY_SYSTEM_STATUS         VARCHAR2,
                        P_PER_SYSTEM_STATUS         VARCHAR2,
                        P_LAST_UPDATE_DATE          DATE,
                        P_LAST_UPDATED_BY           NUMBER,
                        P_LAST_UPDATE_LOGIN         NUMBER,
                        P_CREATED_BY                NUMBER,
                        P_CREATION_DATE             DATE) IS
L_ID NUMBER;

Line: 227

       select per_ass_status_type_amends_s.nextval
       into   L_ID
       from sys.dual;

Line: 231

 insert into per_ass_status_type_amends(
                ASS_STATUS_TYPE_AMEND_ID,
        	ASSIGNMENT_STATUS_TYPE_ID,
        	BUSINESS_GROUP_ID,
        	ACTIVE_FLAG,
        	DEFAULT_FLAG,
        	USER_STATUS,
        	PAY_SYSTEM_STATUS,
        	PER_SYSTEM_STATUS,
        	LAST_UPDATE_DATE,
        	LAST_UPDATED_BY,
        	LAST_UPDATE_LOGIN,
        	CREATED_BY,
        	CREATION_DATE)
 values(
         P_ASS_STATUS_TYPE_AMEND_ID,
         P_ASSIGNMENT_STATUS_TYPE_ID,
         P_BUSINESS_GROUP_ID,
         P_ACTIVE_FLAG,
         P_DEFAULT_FLAG,
         P_USER_STATUS,
         P_PAY_SYSTEM_STATUS,
         P_PER_SYSTEM_STATUS,
         P_LAST_UPDATE_DATE,
         P_LAST_UPDATED_BY,
         P_LAST_UPDATE_LOGIN,
         P_CREATED_BY,
         P_CREATION_DATE);

Line: 260

insert into per_ass_status_type_amends_tl(
           ASS_STATUS_TYPE_AMEND_ID,
           LANGUAGE,
           SOURCE_LANG,
           USER_STATUS,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_LOGIN,
           CREATED_BY,
           CREATION_DATE)
select
           P_ASS_STATUS_TYPE_AMEND_ID,
           L.LANGUAGE_CODE,
           B.LANGUAGE_CODE,
           P_USER_STATUS,
           P_LAST_UPDATE_DATE,
           P_LAST_UPDATED_BY,
           P_LAST_UPDATE_LOGIN,
           P_CREATED_BY,
           P_CREATION_DATE
from FND_LANGUAGES L, FND_LANGUAGES B
where L.INSTALLED_FLAG in ('I', 'B')
  and B.INSTALLED_FLAG = 'B';

Line: 284

 END INSERT_AMENDS;

Line: 301

    select lookup_code status
    from   hr_lookups
    where  lookup_type = 'PER_ASS_SYS_STATUS'
    order  by lookup_code;

Line: 307

   select  t.per_system_status status
   from    per_assignment_status_types t
   where   t.per_system_status is not null
     and   t.default_flag = 'Y'
     and   t.active_flag  = 'Y'
     and   nvl(t.business_group_id, nvl(p_business_group_id, -9999) ) =
             nvl(p_business_group_id, -9999)
     and   nvl(t.legislation_code,  nvl(p_legislation_code, 'XXX') ) =
             nvl(p_legislation_code, 'XXX')
     and   not exists (select  null
                       from    per_ass_status_type_amends a
                       where   a.assignment_status_type_id =
				 t.assignment_status_type_id
                         and   a.business_group_id =
				 p_business_group_id)
   union all
   select a.per_system_status status
   from   per_ass_status_type_amends a
   where  a.per_system_status is not null
     and  a.default_flag      = 'Y'
     and  a.active_flag       = 'Y'
     and  a.business_group_id = p_business_group_id
   order by 1;

Line: 379

procedure INSERT_ROW (
  X_ROWID in out nocopy VARCHAR2,
  X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
  X_BUSINESS_GROUP_ID in NUMBER,
  X_LEGISLATION_CODE in VARCHAR2,
  X_ACTIVE_FLAG in VARCHAR2,
  X_DEFAULT_FLAG in VARCHAR2,
  X_PRIMARY_FLAG in VARCHAR2,
  X_PAY_SYSTEM_STATUS in VARCHAR2,
  X_PER_SYSTEM_STATUS in VARCHAR2,
  X_USER_STATUS in VARCHAR2,
  X_EXTERNAL_STATUS 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
  cursor C is select ROWID from PER_ASSIGNMENT_STATUS_TYPES
    where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;

Line: 400

  insert into PER_ASSIGNMENT_STATUS_TYPES (
    ASSIGNMENT_STATUS_TYPE_ID,
    BUSINESS_GROUP_ID,
    LEGISLATION_CODE,
    USER_STATUS,
    ACTIVE_FLAG,
    DEFAULT_FLAG,
    PRIMARY_FLAG,
    PAY_SYSTEM_STATUS,
    PER_SYSTEM_STATUS,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_ASSIGNMENT_STATUS_TYPE_ID,
    X_BUSINESS_GROUP_ID,
    X_LEGISLATION_CODE,
    X_USER_STATUS,
    X_ACTIVE_FLAG,
    X_DEFAULT_FLAG,
    X_PRIMARY_FLAG,
    X_PAY_SYSTEM_STATUS,
    X_PER_SYSTEM_STATUS,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 432

  insert into PER_ASSIGNMENT_STATUS_TYPES_TL (
    ASSIGNMENT_STATUS_TYPE_ID,
    USER_STATUS,
    EXTERNAL_STATUS,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATED_BY,
    CREATION_DATE,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_ASSIGNMENT_STATUS_TYPE_ID,
    X_USER_STATUS,
    X_EXTERNAL_STATUS,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_CREATED_BY,
    X_CREATION_DATE,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from PER_ASSIGNMENT_STATUS_TYPES_TL T
    where T.ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 470

end INSERT_ROW;

Line: 483

  cursor c is select
      BUSINESS_GROUP_ID,
      LEGISLATION_CODE,
      ACTIVE_FLAG,
      DEFAULT_FLAG,
      PRIMARY_FLAG,
      PAY_SYSTEM_STATUS,
      PER_SYSTEM_STATUS
    from PER_ASSIGNMENT_STATUS_TYPES
    where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
    for update of ASSIGNMENT_STATUS_TYPE_ID nowait;

Line: 496

  cursor c1 is select
      USER_STATUS,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from PER_ASSIGNMENT_STATUS_TYPES_TL
    where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of ASSIGNMENT_STATUS_TYPE_ID nowait;

Line: 508

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

Line: 544

procedure UPDATE_ROW (
  X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER,
  X_BUSINESS_GROUP_ID in NUMBER,
  X_LEGISLATION_CODE in VARCHAR2,
  X_ACTIVE_FLAG in VARCHAR2,
  X_DEFAULT_FLAG in VARCHAR2,
  X_PRIMARY_FLAG in VARCHAR2,
  X_PAY_SYSTEM_STATUS in VARCHAR2,
  X_PER_SYSTEM_STATUS in VARCHAR2,
  X_USER_STATUS in VARCHAR2,
  X_EXTERNAL_STATUS in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
  -- start of bug 5411889
-- added an if condition so that the seeded record status
-- is not changed when updating the record.

 if X_BUSINESS_GROUP_ID is not null then

   update PER_ASSIGNMENT_STATUS_TYPES set
    BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
    LEGISLATION_CODE = X_LEGISLATION_CODE,
    USER_STATUS = X_USER_STATUS,  -- Bug 2731841
    ACTIVE_FLAG = X_ACTIVE_FLAG,
    DEFAULT_FLAG = X_DEFAULT_FLAG,
    PRIMARY_FLAG = X_PRIMARY_FLAG,
    PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS,
    PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;

Line: 582

  update PER_ASSIGNMENT_STATUS_TYPES set
    BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
    LEGISLATION_CODE = X_LEGISLATION_CODE,
  -- USER_STATUS = X_USER_STATUS,  -- Bug 2731841
    ACTIVE_FLAG = X_ACTIVE_FLAG,
    DEFAULT_FLAG = X_DEFAULT_FLAG,
    PRIMARY_FLAG = X_PRIMARY_FLAG,
    PAY_SYSTEM_STATUS = X_PAY_SYSTEM_STATUS,
    PER_SYSTEM_STATUS = X_PER_SYSTEM_STATUS,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;

Line: 603

  update PER_ASSIGNMENT_STATUS_TYPES_TL set
    USER_STATUS = X_USER_STATUS,
    EXTERNAL_STATUS = X_EXTERNAL_STATUS,
    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 ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 616

end UPDATE_ROW;

Line: 618

procedure DELETE_ROW (
  X_ASSIGNMENT_STATUS_TYPE_ID in NUMBER
) is
begin
  delete from PER_ASSIGNMENT_STATUS_TYPES_TL
  where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;

Line: 629

  delete from PER_ASSIGNMENT_STATUS_TYPES
  where ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;

Line: 635

end DELETE_ROW;

Line: 648

  X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
  X_CUSTOM_MODE IN VARCHAR2 default null
)
is
  X_ROWID ROWID;

Line: 658

  X_LAST_UPDATED_BY NUMBER;

Line: 659

  X_LAST_UPDATE_LOGIN NUMBER;

Line: 661

  f_ludate  date;    -- entity update date in file

Line: 663

  db_ludate date;    -- entity update date in db

Line: 684

    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );*/

Line: 692

   f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);

Line: 693

     select LAST_UPDATED_BY, LAST_UPDATE_DATE
     into db_luby, db_ludate
     from PER_ASSIGNMENT_STATUS_TYPES
     where ASSIGNMENT_STATUS_TYPE_ID = TO_NUMBER(X_ASSIGNMENT_STATUS_TYPE_ID);

Line: 701

    UPDATE_ROW (
      X_ASSIGNMENT_STATUS_TYPE_ID,
      X_BUSINESS_GROUP_ID,
      X_LEGISLATION_CODE,
      X_ACTIVE_FLAG,
      X_DEFAULT_FLAG,
      X_PRIMARY_FLAG,
      X_PAY_SYSTEM_STATUS,
      X_PER_SYSTEM_STATUS,
      X_USER_STATUS,
      X_USER_STATUS,
      f_ludate,
      f_luby,
      0);

Line: 718

      INSERT_ROW (
        X_ROWID,
        X_ASSIGNMENT_STATUS_TYPE_ID,
        X_BUSINESS_GROUP_ID,
        X_LEGISLATION_CODE,
        X_ACTIVE_FLAG,
        X_DEFAULT_FLAG,
        X_PRIMARY_FLAG,
        X_PAY_SYSTEM_STATUS,
        X_PER_SYSTEM_STATUS,
        X_USER_STATUS,
        X_USER_STATUS,
        X_CREATION_DATE,
        X_CREATED_BY,
        f_ludate,
        f_luby,
        0);

Line: 744

  X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
  X_CUSTOM_MODE IN VARCHAR2 default null
)
is
  X_ASSIGNMENT_STATUS_TYPE_ID NUMBER;

Line: 753

  X_LAST_UPDATED_BY NUMBER;

Line: 754

  X_LAST_UPDATE_LOGIN NUMBER;

Line: 756

  f_ludate  date;    -- entity update date in file

Line: 758

  db_ludate date;    -- entity update date in db

Line: 773

    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );*/

Line: 782

  f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);

Line: 783

      select LAST_UPDATED_BY, LAST_UPDATE_DATE
      into db_luby, db_ludate
      from PER_ASSIGNMENT_STATUS_TYPES_TL
      where ASSIGNMENT_STATUS_TYPE_ID = TO_NUMBER(X_ASSIGNMENT_STATUS_TYPE_ID)
      and LANGUAGE=userenv('LANG');

Line: 792

  update PER_ASSIGNMENT_STATUS_TYPES_TL set
    USER_STATUS = X_USER_STATUS,
    EXTERNAL_STATUS = X_USER_STATUS,       -- Bug fix 3627126.
    LAST_UPDATE_DATE = db_ludate,
    LAST_UPDATED_BY = db_luby,
    LAST_UPDATE_LOGIN = 0,
    SOURCE_LANG = userenv('LANG')
  where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
  and ASSIGNMENT_STATUS_TYPE_ID = X_ASSIGNMENT_STATUS_TYPE_ID;

Line: 808

  delete from PER_ASSIGNMENT_STATUS_TYPES_TL T
  where not exists
    (select NULL
    from PER_ASSIGNMENT_STATUS_TYPES B
    where B.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
    );

Line: 815

  update PER_ASSIGNMENT_STATUS_TYPES_TL T set (
      USER_STATUS
    ) = (select
      B.USER_STATUS
    from PER_ASSIGNMENT_STATUS_TYPES_TL B
    where B.ASSIGNMENT_STATUS_TYPE_ID = T.ASSIGNMENT_STATUS_TYPE_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.ASSIGNMENT_STATUS_TYPE_ID,
      T.LANGUAGE
  ) in (select
      SUBT.ASSIGNMENT_STATUS_TYPE_ID,
      SUBT.LANGUAGE
    from PER_ASSIGNMENT_STATUS_TYPES_TL SUBB, PER_ASSIGNMENT_STATUS_TYPES_TL SUBT
    where SUBB.ASSIGNMENT_STATUS_TYPE_ID = SUBT.ASSIGNMENT_STATUS_TYPE_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.USER_STATUS <> SUBT.USER_STATUS
  ));

Line: 834

  insert into PER_ASSIGNMENT_STATUS_TYPES_TL (
    ASSIGNMENT_STATUS_TYPE_ID,
    USER_STATUS,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATED_BY,
    CREATION_DATE,
    LANGUAGE,
    SOURCE_LANG
  ) select /*+ INDEX(b)*/
    B.ASSIGNMENT_STATUS_TYPE_ID,
    B.USER_STATUS,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.CREATED_BY,
    B.CREATION_DATE,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from PER_ASSIGNMENT_STATUS_TYPES_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from PER_ASSIGNMENT_STATUS_TYPES_TL T
    where T.ASSIGNMENT_STATUS_TYPE_ID = B.ASSIGNMENT_STATUS_TYPE_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 865

  delete from PER_ASS_STATUS_TYPES_AMENDS_TL T
  where not exists
    (select NULL
    from PER_ASS_STATUS_TYPES_AMENDS B
    where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
    );

Line: 872

  update PER_ASS_STATUS_TYPES_AMENDS_TL T set (
      USER_STATUS
    ) = (select
      B.USER_STATUS
    from PER_ASS_STATUS_TYPES_AMENDS_TL B
    where B.ASS_STATUS_TYPE_AMEND_ID = T.ASS_STATUS_TYPE_AMEND_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.ASS_STATUS_TYPE_AMEND_ID,
      T.LANGUAGE
  ) in (select
      SUBT.ASS_STATUS_TYPE_AMEND_ID,
      SUBT.LANGUAGE
    from PER_ASS_STATUS_TYPES_AMENDS_TL SUBB, PER_ASS_STATUS_TYPES_AMENDS_TL SUBT
    where SUBB.ASS_STATUS_TYPE_AMEND_ID = SUBT.ASS_STATUS_TYPE_AMEND_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.USER_STATUS <> SUBT.USER_STATUS
  ));

Line: 891

  insert into PER_ASS_STATUS_TYPE_AMENDS_TL (
    ASS_STATUS_TYPE_AMEND_ID,
    USER_STATUS,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATED_BY,
    CREATION_DATE,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.ASS_STATUS_TYPE_AMEND_ID,
    B.USER_STATUS,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.CREATED_BY,
    B.CREATION_DATE,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from PER_ASS_STATUS_TYPE_AMENDS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from PER_ASS_STATUS_TYPE_AMENDS_TL T
    where T.ASS_STATUS_TYPE_AMEND_ID = B.ASS_STATUS_TYPE_AMEND_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 954

       SELECT  1
	 FROM  per_assignment_status_types_tl astt,
	       per_assignment_status_types ast
	 WHERE upper(astt.user_status)=upper(p_user_status)
	 AND   astt.assignment_status_type_id = ast.assignment_status_type_id
	 AND   astt.language = p_language
	 AND   (ast.assignment_status_type_id <> p_assignment_status_type_id
	       OR p_assignment_status_type_id IS NULL)
	 AND   (ast.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
	 ;