APPS.PER_PERSON_TYPES_PKG SQL Statements

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

Line: 17

cursor csr_user_name  is select null
		         from   per_person_types_tl pttl,
                                per_person_types    pt
	                 where  pt.business_group_id = p_business_group_id
		         and    upper(pttl.user_person_type) = upper(p_user_person_type)
		         and    (pt.rowid <> p_rowid
		                 or  p_rowid is null)
                         and    pt.person_type_id = pttl.person_type_id
                         and    pttl.LANGUAGE = userenv('LANG');

Line: 55

cursor csr_system_name is select null
                          from hr_lookups lu, per_person_types pt
                          where pt.business_group_id = p_business_group_id
                          and   lu.meaning = p_system_name
                          and   lu.lookup_type = 'PERSON_TYPE'
                          and   lu.lookup_code = pt.system_person_type
                          and   pt.default_flag = 'Y'
                          and   p_default_flag = 'Y'
                          and (pt.rowid <> p_rowid
                          or   p_rowid is null);

Line: 87

PROCEDURE Insert_Row(X_Rowid                        IN OUT NOCOPY VARCHAR2,
                     X_Person_Type_Id               IN OUT NOCOPY NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Active_Flag                         VARCHAR2,
                     X_Default_Flag                        VARCHAR2,
		     X_System_Person_Type                  VARCHAR2,
		     X_System_Name                         VARCHAR2,
                     X_User_Person_Type                    VARCHAR2
 ) IS
   CURSOR C IS SELECT rowid FROM per_person_types
             WHERE person_type_id  = X_Person_Type_Id;

Line: 99

   CURSOR C2 IS SELECT per_person_types_s.nextval FROM sys.dual;

Line: 129

  INSERT INTO per_person_types(
          person_type_id,
          business_group_id,
          active_flag,
          default_flag,
          system_person_type,
          user_person_type
         ) VALUES (
          X_Person_Type_Id,
          X_Business_Group_Id,
          X_Active_Flag,
          X_Default_Flag,
          X_System_Person_Type,
          X_User_Person_Type

  );

Line: 167

  insert into PER_PERSON_TYPES_TL (
    PERSON_TYPE_ID,
    USER_PERSON_TYPE,
--    LAST_UPDATE_DATE,
--    LAST_UPDATED_BY,
--    LAST_UPDATE_LOGIN,
--    CREATED_BY,
--    CREATION_DATE,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_Person_Type_Id,
    X_User_Person_Type,
--    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_PERSON_TYPES_TL T
    where T.PERSON_TYPE_ID = X_Person_Type_Id
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 199

     hr_utility.set_message_token('PROCEDURE','Insert_Row');

Line: 204

END Insert_Row;

Line: 215

      SELECT ppt.person_type_id,
             ppt.business_group_id,
             ppt.active_flag,
             ppt.default_flag,
             ppt.system_person_type,
             ppt_tl.user_person_type
      FROM   per_person_types ppt,
             per_person_types_tl ppt_tl
      WHERE  ppt.rowid = X_Rowid
      AND    ppt.person_type_id = ppt_tl.person_type_id
      AND    ppt_tl.language = userenv('LANG')
      FOR UPDATE of ppt.person_type_id           NOWAIT;

Line: 229

  cursor c1 is select
      USER_PERSON_TYPE,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from PER_PERSON_TYPES_TL
    where PERSON_TYPE_ID = X_Person_Type_Id
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of PERSON_TYPE_ID nowait;

Line: 296

PROCEDURE Update_Row(X_Rowid                               VARCHAR2,
                     X_Person_Type_Id                      NUMBER,
                     X_Business_Group_Id                   NUMBER,
                     X_Active_Flag                         VARCHAR2,
                     X_Default_Flag                        VARCHAR2,
		     X_System_Person_Type                  VARCHAR2,
		     X_System_Name                         VARCHAR2,
                     X_User_Person_Type                    VARCHAR2
) IS

BEGIN

  UPDATE per_person_types
  SET

    person_type_id                            =    X_Person_Type_Id,
    business_group_id                         =    X_Business_Group_Id,
    active_flag                               =    X_Active_Flag,
    default_flag                              =    X_Default_Flag,
    system_person_type                        =    X_System_Person_Type,
    user_person_type                          =    X_User_Person_Type
  WHERE rowid = X_rowid;

Line: 321

     hr_utility.set_message_token('PROCEDURE','Update_Row');

Line: 327

  update PER_PERSON_TYPES_TL set
    USER_PERSON_TYPE = X_User_Person_Type,
--    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 PERSON_TYPE_ID = X_Person_Type_Id
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 341

END Update_Row;

Line: 343

PROCEDURE Delete_Row(X_Rowid          VARCHAR2,
		     X_Default_flag   varchar2,
		     X_Person_type_Id number) IS
BEGIN
   --
   if   X_Default_flag = 'Y' then
	hr_utility.set_message(801,'HR_6618_PERSON_TYPE_NO_DEL_DEF');

Line: 355

  Check_System_Delete(X_Person_type_Id);

Line: 359

  delete from PER_PERSON_TYPES_TL
  where PERSON_TYPE_ID = X_Person_Type_Id;

Line: 387

  DELETE FROM per_person_types
  WHERE  rowid = X_Rowid;

Line: 392

     hr_utility.set_message_token('PROCEDURE','Delete_Row');

Line: 396

END Delete_Row;

Line: 398

PROCEDURE Check_Delete (X_Business_Group_Id  NUMBER) IS
          System_Name   VARCHAR2(30);

Line: 403

          CURSOR C IS SELECT hr.meaning
                      from  hr_lookups hr
                      WHERE hr.lookup_type = 'PERSON_TYPE'
                      and not exists
	                  (select null
		           from per_person_types ppt
                           where hr.lookup_code = ppt.system_person_type
		           AND PPT.business_group_id = X_Business_Group_Id
		           AND   PPT.active_flag = 'Y'
		           AND   PPT.default_flag = 'Y'
			   HAVING COUNT(PPT.system_person_type) = 1);

Line: 429

END Check_Delete;

Line: 444

  SELECT hr.meaning
  FROM   hr_lookups hr
  WHERE  hr.lookup_type = 'PERSON_TYPE'
  AND    EXISTS
	 (SELECT null
          FROM   per_person_types ppt
          WHERE  hr.lookup_code = ppt.system_person_type
          AND    ppt.business_group_id = X_Business_Group_Id
          AND    ppt.active_flag = 'Y'
          AND    ppt.default_flag = 'Y'
          HAVING COUNT(PPT.system_person_type) < 1);

Line: 464

  SELECT hr.meaning
  FROM   hr_lookups hr
  WHERE  hr.lookup_type = 'PERSON_TYPE'
  AND    EXISTS
	 (SELECT null
          FROM   per_person_types ppt
          WHERE  hr.lookup_code = ppt.system_person_type
          AND    ppt.business_group_id = X_Business_Group_Id
          AND    ppt.active_flag = 'Y'
          AND    ppt.default_flag = 'Y'
          HAVING COUNT(PPT.system_person_type) > 1);

Line: 510

PROCEDURE Check_System_Delete(X_Person_Type_Id in NUMBER) IS
--
-- Cursor modifed to include the check on per_person_type_usages_f
-- Bug# 2561337
--
-- modified the cursor definition for better performance
-- Bug #3646157
cursor csr_system is
            select null
              from dual
            where exists (
                           (select null
                            from per_people_f
                            where person_type_id = X_person_type_id)
                          UNION
                           (select null
                            from per_person_type_usages_f
                            where person_type_id= X_person_type_id)
			 );

Line: 548

END Check_System_Delete;

Line: 564

    select org.organization_id
      from per_business_groups org
     where org.name = x_name;

Line: 573

    select ptp.person_type_id
          ,ptp.rowid
      from per_person_types ptp
     where ptp.user_person_type = x_user_person_type
       and ptp.business_group_id = x_business_group_id;

Line: 594

    UPDATE_ROW
      (X_ROWID => l_person_type.rowid
      ,X_PERSON_TYPE_ID => l_person_type.person_type_id
      ,X_BUSINESS_GROUP_ID => l_business_group.organization_id
      ,X_ACTIVE_FLAG => X_ACTIVE_FLAG
      ,X_DEFAULT_FLAG => X_DEFAULT_FLAG
      ,X_SYSTEM_PERSON_TYPE => X_SYSTEM_PERSON_TYPE
      ,X_SYSTEM_NAME => NULL
      ,X_USER_PERSON_TYPE => X_USER_PERSON_TYPE
      );

Line: 606

    INSERT_ROW
      (X_ROWID => l_person_type.rowid
      ,X_PERSON_TYPE_ID => l_person_type.person_type_id
      ,X_BUSINESS_GROUP_ID => l_business_group.organization_id
      ,X_ACTIVE_FLAG => X_ACTIVE_FLAG
      ,X_DEFAULT_FLAG => X_DEFAULT_FLAG
      ,X_SYSTEM_PERSON_TYPE => X_SYSTEM_PERSON_TYPE
      ,X_SYSTEM_NAME => NULL
      ,X_USER_PERSON_TYPE => X_USER_PERSON_TYPE
      );

Line: 631

    select ptp.person_type_id
      from per_person_types ptp
          ,per_business_groups org
     where ptp.business_group_id = org.organization_id
       and ptp.user_person_type = x_user_person_type
       and org.name = x_name;

Line: 644

  UPDATE per_person_types_tl
     SET user_person_type = X_USER_PERSON_TYPE
        ,last_update_date = SYSDATE
        ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
        ,last_update_login = 1
        ,source_lang = USERENV('LANG')
   WHERE USERENV('LANG') in (language,source_lang)
     AND person_type_id = l_person_type.person_type_id;

Line: 658

  delete from PER_PERSON_TYPES_TL T
  where not exists
    (select NULL
    from PER_PERSON_TYPES B
    where B.PERSON_TYPE_ID = T.PERSON_TYPE_ID
    );

Line: 665

  update PER_PERSON_TYPES_TL T set (
      USER_PERSON_TYPE
    ) = (select
      B.USER_PERSON_TYPE
    from PER_PERSON_TYPES_TL B
    where B.PERSON_TYPE_ID = T.PERSON_TYPE_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.PERSON_TYPE_ID,
      T.LANGUAGE
  ) in (select
      SUBT.PERSON_TYPE_ID,
      SUBT.LANGUAGE
    from PER_PERSON_TYPES_TL SUBB, PER_PERSON_TYPES_TL SUBT
    where SUBB.PERSON_TYPE_ID = SUBT.PERSON_TYPE_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.USER_PERSON_TYPE <> SUBT.USER_PERSON_TYPE
  ));

Line: 684

  insert into PER_PERSON_TYPES_TL (
    PERSON_TYPE_ID,
    USER_PERSON_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATED_BY,
    CREATION_DATE,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.PERSON_TYPE_ID,
    B.USER_PERSON_TYPE,
    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_PERSON_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_PERSON_TYPES_TL T
    where T.PERSON_TYPE_ID = B.PERSON_TYPE_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 751

       SELECT  1
	 FROM  per_person_types_tl pptt,
	       per_person_types ppt
	 WHERE upper(pptt.user_person_type)=upper(p_user_person_type)
	 AND   pptt.person_type_id = ppt.person_type_id
	 AND   pptt.language = p_language
	 AND   (ppt.person_type_id <> p_person_type_id OR p_person_type_id IS NULL)
	 AND   (ppt.business_group_id = p_bus_grp_id OR p_bus_grp_id IS NULL)
	 ;