APPS.PER_CUSTOMIZED_RESTR_PKG SQL Statements

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

Line: 7

	 01-JUL-03      tvankayl       Modified table handles Insert_row,
				       Update_Row , Lock_ Row , Delete_row

				       1. prototypes were changed to follow
					  AOL standards.
				       2. DML operations were applied on
				          Translation table also.

					 Load_row and Translate_row were
				         modified to compensate for changes in
					 insert_row and update_row
 115.5    03-JUL-03      tvankayl       Removed unnecessary comments.
==============================================================================
                                                                            */

--------------------------------------------------------------------------------
g_dummy	number(1);	-- Dummy for cursor returns which are not needed

Line: 40

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

Line: 44

  	select  1
  	from    PAY_CUSTOMIZED_RESTRICTIONS pcr
         where   pcr.application_id = c1_p_appl_id
         and     pcr.form_name = P_FORM_NAME
         and     pcr.name = P_NAME
         and     pcr.legislation_code = P_LEGISLATION_CODE
  	and     (P_ROWID        is null
         	 or P_ROWID    <> pcr.rowid);

Line: 70

procedure INSERT_ROW (
  X_ROWID in out nocopy VARCHAR2,
  X_CUSTOMIZED_RESTRICTION_ID in out nocopy NUMBER,
  X_BUSINESS_GROUP_ID in NUMBER,
  X_LEGISLATION_CODE in VARCHAR2,
  X_APPLICATION_ID in NUMBER,
  X_FORM_NAME in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_NAME in VARCHAR2,
  X_COMMENTS in LONG,
  X_LEGISLATION_SUBGROUP in VARCHAR2,
  X_QUERY_FORM_TITLE in VARCHAR2,
  X_STANDARD_FORM_TITLE 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 PAY_CUSTOMIZED_RESTRICTIONS
    where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;

Line: 92

  cursor C_NEXTVAL is select PAY_CUSTOMIZED_RESTRICTIONS_S.NEXTVAL from SYS.DUAL;

Line: 102

  insert into PAY_CUSTOMIZED_RESTRICTIONS (
    CUSTOMIZED_RESTRICTION_ID,
    BUSINESS_GROUP_ID,
    LEGISLATION_CODE,
    APPLICATION_ID,
    FORM_NAME,
    ENABLED_FLAG,
    NAME,
    COMMENTS,
    LEGISLATION_SUBGROUP,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_CUSTOMIZED_RESTRICTION_ID,
    X_BUSINESS_GROUP_ID,
    X_LEGISLATION_CODE,
    X_APPLICATION_ID,
    X_FORM_NAME,
    X_ENABLED_FLAG,
    X_NAME,
    X_COMMENTS,
    X_LEGISLATION_SUBGROUP,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 134

  insert into PAY_CUSTOM_RESTRICTIONS_TL (
    CUSTOMIZED_RESTRICTION_ID,
    QUERY_FORM_TITLE,
    STANDARD_FORM_TITLE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_CUSTOMIZED_RESTRICTION_ID,
    X_QUERY_FORM_TITLE,
    X_STANDARD_FORM_TITLE,
    X_CREATED_BY,
    X_CREATION_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATE_LOGIN,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from PAY_CUSTOM_RESTRICTIONS_TL T
    where T.CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 170

                                   'per_customized_restr_pkg.insert_row');

Line: 178

end INSERT_ROW;

Line: 193

  cursor c is select
      BUSINESS_GROUP_ID,
      LEGISLATION_CODE,
      APPLICATION_ID,
      FORM_NAME,
      ENABLED_FLAG,
      NAME,
      COMMENTS,
      LEGISLATION_SUBGROUP
    from PAY_CUSTOMIZED_RESTRICTIONS
    where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
    for update of CUSTOMIZED_RESTRICTION_ID nowait;

Line: 207

  cursor c1 is select
      QUERY_FORM_TITLE,
      STANDARD_FORM_TITLE,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from PAY_CUSTOM_RESTRICTIONS_TL
    where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of CUSTOMIZED_RESTRICTION_ID nowait;

Line: 220

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

Line: 258

procedure UPDATE_ROW (
  X_CUSTOMIZED_RESTRICTION_ID in NUMBER,
  X_BUSINESS_GROUP_ID in NUMBER,
  X_LEGISLATION_CODE in VARCHAR2,
  X_APPLICATION_ID in NUMBER,
  X_FORM_NAME in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_NAME in VARCHAR2,
  X_COMMENTS in LONG,
  X_LEGISLATION_SUBGROUP in VARCHAR2,
  X_QUERY_FORM_TITLE in VARCHAR2,
  X_STANDARD_FORM_TITLE in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
  update PAY_CUSTOMIZED_RESTRICTIONS set
    BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
    LEGISLATION_CODE = X_LEGISLATION_CODE,
    APPLICATION_ID = X_APPLICATION_ID,
    FORM_NAME = X_FORM_NAME,
    ENABLED_FLAG = X_ENABLED_FLAG,
    NAME = X_NAME,
    COMMENTS = X_COMMENTS,
    LEGISLATION_SUBGROUP = X_LEGISLATION_SUBGROUP,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;

Line: 293

  update PAY_CUSTOM_RESTRICTIONS_TL set
    QUERY_FORM_TITLE = X_QUERY_FORM_TITLE,
    STANDARD_FORM_TITLE = X_STANDARD_FORM_TITLE,
    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 CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 305

	insert into PAY_CUSTOM_RESTRICTIONS_TL (
		        CUSTOMIZED_RESTRICTION_ID,
			QUERY_FORM_TITLE,
		        STANDARD_FORM_TITLE,
			CREATED_BY,
			CREATION_DATE,
			LAST_UPDATED_BY,
			LAST_UPDATE_DATE,
			LAST_UPDATE_LOGIN,
			LANGUAGE,
			SOURCE_LANG
	  ) select
		X_CUSTOMIZED_RESTRICTION_ID,
		X_QUERY_FORM_TITLE,
		X_STANDARD_FORM_TITLE,
		0 ,
		SYSDATE,
		X_LAST_UPDATED_BY,
		X_LAST_UPDATE_DATE,
		X_LAST_UPDATE_LOGIN,
		L.LANGUAGE_CODE,
		userenv('LANG')
	  from FND_LANGUAGES L
	where L.INSTALLED_FLAG in ('I', 'B')
	  and not exists
	    (select NULL
		    from PAY_CUSTOM_RESTRICTIONS_TL T
		    where T.CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
		    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 336

end UPDATE_ROW;

Line: 338

procedure DELETE_ROW (
  X_CUSTOMIZED_RESTRICTION_ID in NUMBER
) is
begin
  delete from PAY_CUSTOM_RESTRICTIONS_TL
  where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;

Line: 349

  delete from PAY_CUSTOMIZED_RESTRICTIONS
  where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;

Line: 355

end DELETE_ROW;

Line: 375

  l_last_update_date   PAY_CUSTOMIZED_RESTRICTIONS.last_update_date%TYPE       := SYSDATE;

Line: 376

  l_last_updated_by    PAY_CUSTOMIZED_RESTRICTIONS.last_updated_by%TYPE         := 0;

Line: 377

  l_last_update_login  PAY_CUSTOMIZED_RESTRICTIONS.last_update_login%TYPE       := 0;

Line: 384

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

Line: 389

 	select customized_restriction_id , comments , business_group_id
 	from    PAY_CUSTOMIZED_RESTRICTIONS pcr
        where   pcr.application_id = l_appl_id
        and     pcr.form_name = X_FORM_NAME
        and     pcr.name = X_NAME
	and     nvl(pcr.legislation_code,'XXX') = nvl(X_LEGISLATION_CODE,'XXX') ;

Line: 402

    l_last_updated_by := 1;

Line: 423

  UPDATE_ROW
      (	X_CUSTOMIZED_RESTRICTION_ID => l_cust_rest_id
      ,X_APPLICATION_ID   => l_appl_id
      ,X_FORM_NAME                => X_FORM_NAME
      ,X_NAME                     => X_NAME
      ,X_BUSINESS_GROUP_ID        => l_business_group_id
      ,X_LEGISLATION_CODE         => X_LEGISLATION_CODE
      ,X_ENABLED_FLAG             => X_ENABLED_FLAG
      ,X_QUERY_FORM_TITLE         => X_QUERY_FORM_TITLE
      ,X_STANDARD_FORM_TITLE      => X_STANDARD_FORM_TITLE
      ,X_COMMENTS                 => l_comments
      ,X_LEGISLATION_SUBGROUP     => X_LEGISLATION_SUBGROUP
      ,X_LAST_UPDATE_DATE         => l_last_update_date
      ,X_LAST_UPDATED_BY          => l_last_updated_by
      ,X_LAST_UPDATE_LOGIN        => l_last_update_login
      );

Line: 443

      INSERT_ROW
        (X_ROWID                    => l_rowid
	,X_CUSTOMIZED_RESTRICTION_ID => l_cust_rest_id
        ,X_APPLICATION_ID   => l_appl_id
        ,X_FORM_NAME                => X_FORM_NAME
        ,X_NAME                     => X_NAME
        ,X_BUSINESS_GROUP_ID        => null
        ,X_LEGISLATION_CODE         => X_LEGISLATION_CODE
        ,X_ENABLED_FLAG             => X_ENABLED_FLAG
        ,X_QUERY_FORM_TITLE         => X_QUERY_FORM_TITLE
        ,X_STANDARD_FORM_TITLE      => X_STANDARD_FORM_TITLE
	,X_COMMENTS                 => l_comments
        ,X_LEGISLATION_SUBGROUP     => X_LEGISLATION_SUBGROUP
        ,X_CREATED_BY               => l_created_by
        ,X_CREATION_DATE            => l_creation_date
        ,X_LAST_UPDATE_DATE         => l_last_update_date
        ,X_LAST_UPDATED_BY          => l_last_updated_by
        ,X_LAST_UPDATE_LOGIN        => l_last_update_login
        );

Line: 481

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

Line: 486

 	select customized_restriction_id
 	from    PAY_CUSTOMIZED_RESTRICTIONS pcr
        where   pcr.application_id = l_appl_id
        and     pcr.form_name = X_FORM_NAME
        and     pcr.name = X_NAME
	and     nvl(pcr.legislation_code,'XXX') = nvl(X_LEGISLATION_CODE,'XXX') ;

Line: 503

	UPDATE PAY_CUSTOM_RESTRICTIONS_TL
        SET
            QUERY_FORM_TITLE = X_QUERY_FORM_TITLE ,
	    STANDARD_FORM_TITLE = X_STANDARD_FORM_TITLE ,
	    LAST_UPDATE_DATE = sysdate ,
	    LAST_UPDATED_BY = decode(X_OWNER , 'SEED', 1, 0),
	    LAST_UPDATE_LOGIN = 0,
	    SOURCE_LANG = userenv('LANG')
	    where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
	       and  CUSTOMIZED_RESTRICTION_ID = l_cust_rest_id;

Line: 523

  delete from PAY_CUSTOM_RESTRICTIONS_TL T
  where not exists
    (select NULL
    from PAY_CUSTOMIZED_RESTRICTIONS B
    where B.CUSTOMIZED_RESTRICTION_ID = T.CUSTOMIZED_RESTRICTION_ID
    );

Line: 530

  update PAY_CUSTOM_RESTRICTIONS_TL T set (
      QUERY_FORM_TITLE,
      STANDARD_FORM_TITLE
    ) = (select
      B.QUERY_FORM_TITLE,
      B.STANDARD_FORM_TITLE
    from PAY_CUSTOM_RESTRICTIONS_TL B
    where B.CUSTOMIZED_RESTRICTION_ID = T.CUSTOMIZED_RESTRICTION_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.CUSTOMIZED_RESTRICTION_ID,
      T.LANGUAGE
  ) in (select
      SUBT.CUSTOMIZED_RESTRICTION_ID,
      SUBT.LANGUAGE
    from PAY_CUSTOM_RESTRICTIONS_TL SUBB, PAY_CUSTOM_RESTRICTIONS_TL SUBT
    where SUBB.CUSTOMIZED_RESTRICTION_ID = SUBT.CUSTOMIZED_RESTRICTION_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.QUERY_FORM_TITLE <> SUBT.QUERY_FORM_TITLE
      or SUBB.STANDARD_FORM_TITLE <> SUBT.STANDARD_FORM_TITLE
  ));

Line: 552

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