eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.GCS_TREATMENTS_PKG SQL Statements

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

Line: 5


PROCEDURE Insert_Row
(
 row_id	IN OUT NOCOPY			VARCHAR2,
 TREATMENT_ID				NUMBER,
 CONSOLIDATION_TYPE_CODE		VARCHAR2,
 ENABLED_FLAG				VARCHAR2,
 OPERATOR_LOW_CODE			VARCHAR2,
 OPERATOR_HIGH_CODE			VARCHAR2,
 LAST_UPDATE_DATE			DATE,
 LAST_UPDATED_BY			NUMBER,
 CREATION_DATE				DATE,
 CREATED_BY				NUMBER,
 LAST_UPDATE_LOGIN			NUMBER,
 OWNERSHIP_PERCENT_LOW			NUMBER,
 OWNERSHIP_PERCENT_HIGH			NUMBER,
 OBJECT_VERSION_NUMBER			NUMBER,
 DESCRIPTION				VARCHAR2,
 TREATMENT_NAME				varchar2

) IS

  CURSOR	treatment_row IS
    SELECT	rowid
    FROM	gcs_treatments_b cb
    WHERE	cb.TREATMENT_ID= insert_row.TREATMENT_ID;

Line: 35


 INSERT INTO gcs_treatments_b
 (
	 TREATMENT_ID,
	 CONSOLIDATION_TYPE_CODE,
	 ENABLED_FLAG,
	 OPERATOR_LOW_CODE,
	 OPERATOR_HIGH_CODE,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 OWNERSHIP_PERCENT_LOW,
	 OWNERSHIP_PERCENT_HIGH,
	 OBJECT_VERSION_NUMBER
)
SELECT
 TREATMENT_ID,
 CONSOLIDATION_TYPE_CODE,
 ENABLED_FLAG,
 OPERATOR_LOW_CODE,
 OPERATOR_HIGH_CODE,
 LAST_UPDATE_DATE,
 LAST_UPDATED_BY,
 CREATION_DATE,
 CREATED_BY,
 LAST_UPDATE_LOGIN,
 OWNERSHIP_PERCENT_LOW,
 OWNERSHIP_PERCENT_HIGH,
 OBJECT_VERSION_NUMBER

 FROM	dual
    WHERE	NOT EXISTS
		(SELECT	1
		 FROM	gcs_treatments_b cb
		 WHERE	cb.TREATMENT_ID= insert_row.TREATMENT_ID);

Line: 72


INSERT INTO gcs_treatments_TL
(
	 TREATMENT_ID,
	 LANGUAGE,
	 SOURCE_LANG,
	 TREATMENT_NAME,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_LOGIN,
	 DESCRIPTION
)
   SELECT
    TREATMENT_ID,
    userenv('LANG'),
    userenv('LANG'),
    TREATMENT_NAME,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    description

   FROM	dual
    WHERE	NOT EXISTS
		(SELECT	1
		 FROM	gcs_treatments_tl ctl
		 WHERE	ctl.TREATMENT_ID = insert_row.TREATMENT_ID
		 AND	ctl.language = userenv('LANG'));

Line: 112


  END Insert_Row;

Line: 117


  PROCEDURE Update_Row
  (
	 row_id	IN OUT NOCOPY			VARCHAR2,
	 TREATMENT_ID				NUMBER,
	 CONSOLIDATION_TYPE_CODE		VARCHAR2,
	 ENABLED_FLAG				VARCHAR2,
	 OPERATOR_LOW_CODE			VARCHAR2,
	 OPERATOR_HIGH_CODE			VARCHAR2,
	 LAST_UPDATE_DATE			DATE,
	 LAST_UPDATED_BY			NUMBER,
	 CREATION_DATE				DATE,
	 CREATED_BY				NUMBER,
	 LAST_UPDATE_LOGIN			NUMBER,
	 OWNERSHIP_PERCENT_LOW			NUMBER,
	 OWNERSHIP_PERCENT_HIGH			NUMBER,
	 OBJECT_VERSION_NUMBER			NUMBER,
	 DESCRIPTION				VARCHAR2,
	 TREATMENT_NAME				VARCHAR2
) IS
  BEGIN

     UPDATE	gcs_treatments_b cb
     SET
	     TREATMENT_ID=update_row.TREATMENT_ID,
	     CONSOLIDATION_TYPE_CODE=update_row.CONSOLIDATION_TYPE_CODE,
	     ENABLED_FLAG=update_row.ENABLED_FLAG,
	     OPERATOR_LOW_CODE=update_row.OPERATOR_LOW_CODE,
	     OPERATOR_HIGH_CODE=update_row.OPERATOR_HIGH_CODE,
	     LAST_UPDATE_DATE=update_row.LAST_UPDATE_DATE,
	     LAST_UPDATED_BY=update_row.LAST_UPDATED_BY,
	     CREATION_DATE=update_row.CREATION_DATE,
	     CREATED_BY=update_row.CREATED_BY,
	     LAST_UPDATE_LOGIN=update_row.LAST_UPDATE_LOGIN,
	     OWNERSHIP_PERCENT_LOW=update_row.OWNERSHIP_PERCENT_LOW,
	     OWNERSHIP_PERCENT_HIGH=update_row.OWNERSHIP_PERCENT_HIGH,
	     OBJECT_VERSION_NUMBER=update_row.OBJECT_VERSION_NUMBER

      WHERE		cb.TREATMENT_ID = update_row.TREATMENT_ID;

Line: 160


 INSERT INTO
 GCS_TREATMENTS_TL
 (
	  TREATMENT_ID,
	  LANGUAGE,
	  SOURCE_LANG,
	  TREATMENT_NAME,
	  LAST_UPDATE_DATE,
	  LAST_UPDATED_BY,
	  CREATION_DATE,
	  CREATED_BY,
	  LAST_UPDATE_LOGIN,
	  DESCRIPTION
 )
SELECT

	  TREATMENT_ID,
	  userenv('LANG'),
	  userenv('LANG'),
	  TREATMENT_NAME,
	  last_update_date,
	  last_updated_by,
	  creation_date,
	  created_by,
	  last_update_login,
	  description

FROM	dual
      WHERE	NOT EXISTS
  		(SELECT	1
  		 FROM		GCS_TREATMENTS_TL ctl
  		 WHERE	ctl.TREATMENT_ID = update_row.TREATMENT_ID
  		 AND		ctl.language = userenv('LANG'));

Line: 196


      UPDATE	GCS_TREATMENTS_TL ctl
      SET
	       LAST_UPDATE_DATE = update_row.LAST_UPDATE_DATE,
	       LAST_UPDATED_BY = update_row.LAST_UPDATED_BY,
	       CREATION_DATE = update_row.CREATION_DATE,
	       CREATED_BY = update_row.CREATED_BY,
	       LAST_UPDATE_LOGIN = update_row.LAST_UPDATE_LOGIN

      WHERE		ctl.TREATMENT_ID 	= update_row.TREATMENT_ID
      AND		ctl.language 		= userenv('LANG');

Line: 210


  END Update_Row;

Line: 221


 LAST_UPDATE_DATE			DATE,
 LAST_UPDATED_BY			NUMBER,
 CREATION_DATE				DATE,
 CREATED_BY				NUMBER,
 LAST_UPDATE_LOGIN			NUMBER,
 OWNERSHIP_PERCENT_LOW			NUMBER,
 OWNERSHIP_PERCENT_HIGH			NUMBER,
 OBJECT_VERSION_NUMBER			NUMBER,
 owner					VARCHAR2,
 custom_mode				VARCHAR2,
 DESCRIPTION				VARCHAR2,
 TREATMENT_NAME				VARCHAR2
 ) IS

    f_luby	NUMBER;	-- category owner in file

Line: 236


    f_ludate	DATE;	-- category update date in file

Line: 238


    db_ludate	DATE;	-- category update date in db

Line: 244


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

Line: 247


      SELECT	cb.last_updated_by, cb.last_update_date
      INTO	db_luby, db_ludate
      FROM	GCS_TREATMENTS_B cb
      WHERE	cb.TREATMENT_ID = load_row.TREATMENT_ID;

Line: 255


 update_row
 (
	 row_id=>row_id,
	 TREATMENT_ID=>TREATMENT_ID,
	 ENABLED_FLAG=>ENABLED_FLAG,
	 CONSOLIDATION_TYPE_CODE=>CONSOLIDATION_TYPE_CODE,
	 OPERATOR_LOW_CODE=>OPERATOR_LOW_CODE,
	 OPERATOR_HIGH_CODE=>OPERATOR_HIGH_CODE,
	 LAST_UPDATE_DATE=>f_ludate,
	 LAST_UPDATED_BY=>f_luby,
	 CREATION_DATE=>f_ludate,
	 CREATED_BY=>f_luby,
	 LAST_UPDATE_LOGIN=>0,
	 OWNERSHIP_PERCENT_LOW=>OWNERSHIP_PERCENT_LOW,
	 OWNERSHIP_PERCENT_HIGH=>OWNERSHIP_PERCENT_HIGH,
	 OBJECT_VERSION_NUMBER=>OBJECT_VERSION_NUMBER,
	 DESCRIPTION=>DESCRIPTION,
	 TREATMENT_NAME=>TREATMENT_NAME
);

Line: 278


 insert_row
 (
	 row_id=>row_id,
	 TREATMENT_ID=>TREATMENT_ID,
	 ENABLED_FLAG=>ENABLED_FLAG,
	 CONSOLIDATION_TYPE_CODE=>CONSOLIDATION_TYPE_CODE,
	 OPERATOR_LOW_CODE=>OPERATOR_LOW_CODE,
	 OPERATOR_HIGH_CODE=>OPERATOR_HIGH_CODE,
	 LAST_UPDATE_DATE=>f_ludate,
	 LAST_UPDATED_BY=>f_luby,
	 CREATION_DATE=>f_ludate,
	 CREATED_BY=>f_luby,
	 LAST_UPDATE_LOGIN=>0,
	 OWNERSHIP_PERCENT_LOW=>OWNERSHIP_PERCENT_LOW,
	 OWNERSHIP_PERCENT_HIGH=>OWNERSHIP_PERCENT_HIGH,
	 OBJECT_VERSION_NUMBER=>OBJECT_VERSION_NUMBER,
	 DESCRIPTION=>DESCRIPTION,
	 TREATMENT_NAME=>TREATMENT_NAME

);

Line: 310


	 LAST_UPDATE_DATE               DATE,
	 LAST_UPDATED_BY                NUMBER,
	 CREATION_DATE                  DATE,
	 CREATED_BY                     NUMBER,
	 LAST_UPDATE_LOGIN              NUMBER,
	 DESCRIPTION                    VARCHAR2,
	 owner                          VARCHAR2,
	 custom_mode                    VARCHAR2
  )  IS
    f_luby		NUMBER; -- category owner in file

Line: 320


    f_ludate	DATE;	-- category update date in file

Line: 322


    db_ludate	DATE;	-- category update date in db

Line: 326


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

Line: 329


      SELECT	ctl.last_updated_by, ctl.last_update_date
      INTO	db_luby, db_ludate
      FROM	GCS_TREATMENTS_TL ctl
      WHERE	ctl.TREATMENT_ID = translate_row.TREATMENT_ID
      AND	ctl.language = userenv('LANG');

Line: 338


        UPDATE
	GCS_TREATMENTS_TL ctl
        SET
		SOURCE_LANG= userenv('LANG'),
		TREATMENT_NAME=translate_row.TREATMENT_NAME,
		LAST_UPDATE_DATE=f_ludate,
		LAST_UPDATED_BY=f_luby,
		LAST_UPDATE_LOGIN=0,
		DESCRIPTION=translate_row.DESCRIPTION

        WHERE	ctl.TREATMENT_ID = translate_row.TREATMENT_ID
        AND		userenv('LANG') IN (ctl.language, ctl.source_lang);

Line: 361


   insert /*+ append parallel(tt) */ into
   GCS_TREATMENTS_TL tt
   (
	TREATMENT_ID    ,
	LANGUAGE         ,
	SOURCE_LANG      ,
	TREATMENT_NAME   ,
	LAST_UPDATE_DATE ,
	LAST_UPDATED_BY  ,
	CREATION_DATE    ,
	CREATED_BY       ,
	LAST_UPDATE_LOGIN,
	DESCRIPTION
  )

    select /*+ parallel(v) parallel(t) use_nl(t) */
    v.*
    from
    ( SELECT /*+ no_merge ordered parellel(b) */

        B.TREATMENT_ID     ,
	L.LANGUAGE_CODE    ,
	B.SOURCE_LANG      ,
	B.TREATMENT_NAME   ,
	B.LAST_UPDATE_DATE ,
	B.LAST_UPDATED_BY  ,
	B.CREATION_DATE    ,
	B.CREATED_BY        ,
	B.LAST_UPDATE_LOGIN  ,
	B.DESCRIPTION


  from GCS_TREATMENTS_TL B,
  FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  ) v, GCS_TREATMENTS_TL t
    where T.TREATMENT_ID(+) = v.TREATMENT_ID
    and T.LANGUAGE(+) = v.LANGUAGE_CODE
    and t.TREATMENT_ID IS NULL;