APPS.PAY_UPGRADE_DEFINITIONS_PKG SQL Statements

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

Line: 9

	select null from pay_upgrade_definitions
		where upper(short_name) = upper(p_short_name);

Line: 88

select null
from fnd_territories
where territory_code = p_legislation_code ;

Line: 113

PROCEDURE chk_delete ( p_upgrade_definition_id in number )
is
--
cursor csr_delete is
	select null from pay_upgrade_status
	where
	upgrade_definition_id = p_upgrade_definition_id ;

Line: 122

l_proc   varchar2(100) := g_package || 'chk_delete';

Line: 128

  open csr_delete;

Line: 129

  fetch csr_delete into l_exists ;

Line: 131

  if csr_delete%found then
    close csr_delete;

Line: 136

  close csr_delete;

Line: 140

END chk_delete;

Line: 143

PROCEDURE Insert_Row (
	 P_SHORT_NAME              in            VARCHAR2
        ,P_NAME			           in		     VARCHAR2
	,P_DESCRIPTION             in            VARCHAR2
	,P_LEGISLATION_CODE        in            VARCHAR2   default null
	,P_UPGRADE_LEVEL           in            VARCHAR2
	,P_CRITICALITY             in            VARCHAR2
	,P_FAILURE_POINT           in            VARCHAR2
	,P_LEGISLATIVELY_ENABLED   in            VARCHAR2
	,P_UPGRADE_PROCEDURE       in            VARCHAR2
	,P_THREADING_LEVEL         in            VARCHAR2
	,P_UPGRADE_METHOD          in            VARCHAR2
	,P_QUALIFYING_PROCEDURE    in            VARCHAR2   default null
	,P_OWNER_APPL_ID           in            NUMBER     default null
        ,P_FIRST_PATCHSET          in            VARCHAR2   default null
        ,P_VALIDATE_CODE           in            VARCHAR2   default null
        ,P_ADDITIONAL_INFO         in            VARCHAR2   default null
	,P_LAST_UPDATE_DATE	       in	         DATE
	,P_LAST_UPDATED_BY	       in	         NUMBER
	,P_LAST_UPDATE_LOGIN	   in	         NUMBER
	,P_CREATED_BY		       in	         NUMBER
	,P_CREATION_DATE	       in	         DATE
	,P_UPGRADE_DEFINITION_ID   out nocopy    NUMBER
) is
--
l_upgrade_definition_id PAY_UPGRADE_DEFINITIONS.UPGRADE_DEFINITION_ID%TYPE;

Line: 170

l_proc   varchar2(100) := g_package || 'Insert_Row';

Line: 173

	select null from PAY_UPGRADE_DEFINITIONS
	    where upgrade_definition_id = l_upgrade_definition_id;

Line: 177

	select pay_upgrade_definitions_s.nextval from dual;

Line: 252

    insert into PAY_UPGRADE_DEFINITIONS (
	UPGRADE_DEFINITION_ID,
	SHORT_NAME,
	NAME,
	DESCRIPTION,
	LEGISLATION_CODE,
	UPGRADE_LEVEL,
	CRITICALITY,
	FAILURE_POINT,
	LEGISLATIVELY_ENABLED,
	UPGRADE_PROCEDURE,
	THREADING_LEVEL,
	UPGRADE_METHOD,
	QUALIFYING_PROCEDURE,
	OWNER_APPLICATION_ID,
        FIRST_PATCHSET,
        VALIDATE_CODE,
        ADDITIONAL_INFO,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
	) values (
	l_upgrade_definition_id,
	p_short_name,
	p_name,
	p_description,
	p_legislation_code,
	p_upgrade_level,
	p_criticality,
	p_failure_point,
	p_legislatively_enabled,
	p_upgrade_procedure,
	p_threading_level,
	p_upgrade_method,
	p_qualifying_procedure,
	p_owner_appl_id,
        p_first_patchset,
        p_validate_code,
        p_additional_info,
	p_creation_date,
	p_created_by,
	p_last_update_date,
	p_last_updated_by,
	p_last_update_login   );

Line: 299

	insert into PAY_UPGRADE_DEFINITIONS_TL (
	    UPGRADE_DEFINITION_ID,
    	    LANGUAGE,
	    SOURCE_LANG,
	    NAME,
	    DESCRIPTION,
	    ADDITIONAL_INFO,
	    LAST_UPDATE_DATE,
	    LAST_UPDATED_BY,
	    LAST_UPDATE_LOGIN,
	    CREATED_BY,
	    CREATION_DATE
	    ) select
	    l_upgrade_definition_id,
            l.language_code,
            userenv('lang'),
	    p_name,
    	    p_description,
    	    p_additional_info,
	    p_last_update_date,
	    p_last_updated_by,
	    p_last_update_login,
	    p_created_by,
	    p_creation_date
		    from FND_LANGUAGES L
		    where L.INSTALLED_FLAG in ('I', 'B')
		    and not exists
		    (select NULL
			    from PAY_UPGRADE_DEFINITIONS_TL T
			    where T.UPGRADE_DEFINITION_ID = l_UPGRADE_DEFINITION_ID
			    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 346

END Insert_Row;

Line: 348

PROCEDURE Update_Row (
	  P_UPGRADE_DEFINITION_ID  in            NUMBER
        , P_CRITICALITY            in            VARCHAR2
	, P_FAILURE_POINT          in            VARCHAR2
        , P_UPGRADE_PROCEDURE      in            VARCHAR2
        , P_DESCRIPTION            in            VARCHAR2
	, P_QUALIFYING_PROCEDURE   in            VARCHAR2
	, P_OWNER_APPL_ID          in            NUMBER
        , P_FIRST_PATCHSET         in            VARCHAR2
        , P_VALIDATE_CODE          in            VARCHAR2
        , P_ADDITIONAL_INFO        in            VARCHAR2
        , P_LAST_UPDATE_DATE       in            DATE
	, P_LAST_UPDATED_BY        in            NUMBER
	, P_LAST_UPDATE_LOGIN      in            NUMBER
) is
--
l_exists varchar2(1);

Line: 365

l_proc   varchar2(100) := g_package || 'Update_Row';

Line: 391

     update PAY_UPGRADE_DEFINITIONS set
            description = p_description,
	    criticality = p_criticality,
	    failure_point = p_failure_point,
	    upgrade_procedure = p_upgrade_procedure,
            qualifying_procedure = p_qualifying_procedure,
            owner_application_id = p_owner_appl_id,
            first_patchset = p_first_patchset,
            validate_code = p_validate_code,
            additional_info = p_additional_info,
	    last_update_date = p_last_update_date,
	    last_updated_by = p_last_updated_by,
	    last_update_login = p_last_update_login
	 where upgrade_definition_id = p_upgrade_definition_id;

Line: 410

     update PAY_UPGRADE_DEFINITIONS_TL set
	    description = p_description,
	    additional_info = p_additional_info,
	    last_update_date = p_last_update_date,
	    last_updated_by = p_last_updated_by,
	    last_update_login = p_last_update_login,
	    source_lang = userenv('lang')
         where upgrade_definition_id = p_upgrade_definition_id
	   and userenv('lang') in (language, source_lang);

Line: 426

END Update_Row;

Line: 445

   select
      SHORT_NAME,
      NAME,
      LEGISLATION_CODE,
      UPGRADE_LEVEL,
      CRITICALITY,
      FAILURE_POINT,
      LEGISLATIVELY_ENABLED,
      UPGRADE_PROCEDURE,
      THREADING_LEVEL,
      DESCRIPTION,
      UPGRADE_METHOD,
      QUALIFYING_PROCEDURE
      from PAY_UPGRADE_DEFINITIONS
	where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID
	      for update of UPGRADE_DEFINITION_ID nowait;

Line: 463

   select
      NAME,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
      from PAY_UPGRADE_DEFINITIONS_TL
	    where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID
	      and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
		      for update of UPGRADE_DEFINITION_ID nowait;

Line: 486

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

Line: 531

PROCEDURE Delete_Row (
	P_UPGRADE_DEFINITION_ID in NUMBER
)is
--
l_proc   varchar2(100) := g_package || 'Delete_Row';

Line: 543

  chk_delete ( p_upgrade_definition_id );

Line: 545

  delete from PAY_UPGRADE_LEGISLATIONS
  where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID;

Line: 548

  delete from PAY_UPGRADE_DEFINITIONS_TL
  where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID;

Line: 555

  delete from PAY_UPGRADE_DEFINITIONS
  where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID;

Line: 564

END Delete_Row;

Line: 570

  delete from PAY_UPGRADE_DEFINITIONS_TL T
  where not exists
    (select NULL
    from PAY_UPGRADE_DEFINITIONS B
    where B.UPGRADE_DEFINITION_ID = T.UPGRADE_DEFINITION_ID
    );

Line: 577

  update PAY_UPGRADE_DEFINITIONS_TL T set (
      NAME,
      DESCRIPTION
    ) = (select
      B.NAME,
      B.DESCRIPTION
    from PAY_UPGRADE_DEFINITIONS_TL B
    where B.UPGRADE_DEFINITION_ID = T.UPGRADE_DEFINITION_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.UPGRADE_DEFINITION_ID,
      T.LANGUAGE
  ) in (select
      SUBT.UPGRADE_DEFINITION_ID,
      SUBT.LANGUAGE
    from PAY_UPGRADE_DEFINITIONS_TL SUBB, PAY_UPGRADE_DEFINITIONS_TL SUBT
    where SUBB.UPGRADE_DEFINITION_ID = SUBT.UPGRADE_DEFINITION_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.NAME <> SUBT.NAME
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
  ));

Line: 599

  insert into PAY_UPGRADE_DEFINITIONS_TL (
    DESCRIPTION,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATED_BY,
    CREATION_DATE,
    UPGRADE_DEFINITION_ID,
    NAME,
    LANGUAGE,
    SOURCE_LANG
  ) select /*+ ORDERED */
    B.DESCRIPTION,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.UPGRADE_DEFINITION_ID,
    B.NAME,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from PAY_UPGRADE_DEFINITIONS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from PAY_UPGRADE_DEFINITIONS_TL T
    where T.UPGRADE_DEFINITION_ID = B.UPGRADE_DEFINITION_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 658

l_last_updated_by    PAY_UPGRADE_DEFINITIONS.LAST_UPDATED_BY%TYPE;

Line: 659

l_last_update_login  PAY_UPGRADE_DEFINITIONS.LAST_UPDATE_LOGIN%TYPE;

Line: 660

l_last_update_date   PAY_UPGRADE_DEFINITIONS.LAST_UPDATE_DATE%TYPE;

Line: 663

   select  upgrade_definition_id
      from   pay_upgrade_definitions
	      where  upper(short_name) = upper(p_short_name) ;

Line: 668

        select application_id
        from fnd_application
        where upper(application_short_name) = upper(P_OWNER_APPL_SHORT_NAME);

Line: 693

  l_last_update_date  := l_sysdate;

Line: 694

  l_last_updated_by   := fnd_global.user_id;

Line: 695

  l_last_update_login := fnd_global.login_id;

Line: 716

     Update_Row (
	  P_UPGRADE_DEFINITION_ID  =>  l_upgrade_definition_id
        , P_CRITICALITY            =>  p_criticality
        , P_FAILURE_POINT          =>  p_failure_point
        , P_UPGRADE_PROCEDURE      =>  p_upgrade_procedure
        , P_DESCRIPTION            =>  p_description
        , P_QUALIFYING_PROCEDURE   =>  p_qualifying_procedure
        , P_OWNER_APPL_ID          =>  l_appl_id
        , P_FIRST_PATCHSET         =>  p_first_patchset
        , P_VALIDATE_CODE          =>  p_validate_code
        , P_ADDITIONAL_INFO        =>  p_additional_info
        , P_LAST_UPDATE_DATE       =>  l_last_update_date
        , P_LAST_UPDATED_BY        =>  l_last_updated_by
        , P_LAST_UPDATE_LOGIN      =>  l_last_update_login
       );

Line: 738

     Insert_Row (
  	 P_SHORT_NAME              =>  p_short_name
        ,P_NAME			           =>  p_name
	,P_DESCRIPTION             =>  p_description
	,P_LEGISLATION_CODE        =>  p_legislation_code
	,P_UPGRADE_LEVEL           =>  p_upgrade_level
	,P_CRITICALITY             =>  p_criticality
	,P_FAILURE_POINT           =>  p_failure_point
	,P_LEGISLATIVELY_ENABLED   =>  p_legislatively_enabled
	,P_UPGRADE_PROCEDURE       =>  p_upgrade_procedure
	,P_THREADING_LEVEL         =>  p_threading_level
	,P_UPGRADE_METHOD          =>  p_upgrade_method
        ,P_QUALIFYING_PROCEDURE    =>  p_qualifying_procedure
        ,P_OWNER_APPL_ID          =>  l_appl_id
        ,P_FIRST_PATCHSET         =>  p_first_patchset
        ,P_VALIDATE_CODE          =>  p_validate_code
        ,P_ADDITIONAL_INFO        =>  p_additional_info
	,P_LAST_UPDATE_DATE	       =>  l_last_update_date
	,P_LAST_UPDATED_BY	       =>  l_last_updated_by
	,P_LAST_UPDATE_LOGIN	   =>  l_last_update_login
	,P_CREATED_BY		       =>  l_created_by
	,P_CREATION_DATE	       =>  l_creation_date
	,P_UPGRADE_DEFINITION_ID   =>  l_upgrade_definition_id
       );

Line: 778

l_last_updated_by   number;

Line: 779

l_last_update_login number;

Line: 780

l_last_update_date  date;

Line: 798

  l_last_updated_by   := fnd_global.user_id;

Line: 799

  l_last_update_login := fnd_global.login_id;

Line: 800

  l_last_update_date  := sysdate;

Line: 803

  UPDATE pay_upgrade_definitions_tl
    SET name = nvl(p_name,name),
	description = nvl(p_description,description),
	additional_info = nvl(p_additional_info, additional_info),
        last_update_date  = l_last_update_date,
        last_updated_by   = l_last_updated_by,
        last_update_login = l_last_update_login,
        source_lang = userenv('LANG')
  WHERE userenv('LANG') IN (language,source_lang)
    AND upgrade_definition_id in
        (SELECT pud.upgrade_definition_id
           FROM pay_upgrade_definitions pud
           WHERE upper(p_short_name) = upper(pud.short_name) );