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.

APPS.WMS_OP_PLANS_PKG SQL Statements

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

Line: 5


PROCEDURE INSERT_ROW (
   x_rowid                          IN OUT nocopy VARCHAR2
  ,x_operation_plan_id         	    IN     NUMBER
  ,x_last_updated_by                IN     NUMBER
  ,x_last_update_date               IN     DATE
  ,x_created_by                     IN     NUMBER
  ,x_creation_date                  IN     DATE
  ,x_last_update_login              IN     NUMBER
  ,x_operation_plan_name	    IN     VARCHAR2
  ,x_language                       IN     VARCHAR2
  ,x_source_lang                    IN     VARCHAR2
  ,x_description                    IN     VARCHAR2
  ,x_system_task_type               IN     NUMBER
  ,x_organization_id                IN     NUMBER
  ,x_user_defined                   IN     VARCHAR2
  ,x_enabled_flag                   IN     VARCHAR2
  ,x_effective_date_from            IN     DATE
  ,x_effective_date_to              IN     DATE
  ,x_activity_type_id               IN     NUMBER
  ,x_common_to_all_org              IN     VARCHAR2
  ,x_plan_type_id                   IN     NUMBER
  ,x_attribute_category             IN     VARCHAR2 DEFAULT NULL
  ,x_attribute1                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute2                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute3                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute4                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute5                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute6                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute7                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute8                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute9                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute10                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute11                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute12                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute13                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute14                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute15                    IN     VARCHAR2 DEFAULT NULL
  ,x_default_flag                   IN     VARCHAR2
  ,x_template_flag                  IN     VARCHAR2
  ,x_crossdock_to_wip_flag          IN     VARCHAR2
  )IS
    CURSOR C IS SELECT ROWID FROM WMS_OP_PLANS_B
      WHERE operation_plan_id = x_operation_plan_id;

Line: 50


   INSERT INTO WMS_OP_PLANS_B (
       operation_plan_id
      ,last_updated_by
      ,last_update_date
      ,created_by
      ,creation_date
      ,last_update_login
      ,system_task_type
      ,organization_id
      ,user_defined
      ,enabled_flag
      ,effective_date_from
      ,effective_date_to
      ,activity_type_id
      ,common_to_all_org
      ,plan_type_id
      ,attribute_category
      ,attribute1
      ,attribute2
      ,attribute3
      ,attribute4
      ,attribute5
      ,attribute6
      ,attribute7
      ,attribute8
      ,attribute9
      ,attribute10
      ,attribute11
      ,attribute12
      ,attribute13
      ,attribute14
      ,attribute15
      ,default_flag
      ,template_flag
      ,crossdock_to_wip_flag
) values (
       x_operation_plan_id
      ,x_last_updated_by
      ,x_last_update_date
      ,x_created_by
      ,x_creation_date
      ,x_last_update_login
      ,x_system_task_type
      ,x_organization_id
      ,x_user_defined
      ,x_enabled_flag
      ,x_effective_date_from
      ,x_effective_date_to
      ,x_activity_type_id
      ,x_common_to_all_org
      ,x_plan_type_id
      ,x_attribute_category
      ,x_attribute1
      ,x_attribute2
      ,x_attribute3
      ,x_attribute4
      ,x_attribute5
      ,x_attribute6
      ,x_attribute7
      ,x_attribute8
      ,x_attribute9
      ,x_attribute10
      ,x_attribute11
      ,x_attribute12
      ,x_attribute13
      ,x_attribute14
      ,x_attribute15
      ,x_default_flag
      ,x_template_flag
      ,x_crossdock_to_wip_flag
     );

Line: 122


  insert into WMS_OP_PLANS_TL (
    operation_plan_id
   ,last_updated_by
   ,last_update_date
   ,created_by
   ,creation_date
   ,last_update_login
   ,operation_plan_name
   ,description
   ,language
   ,source_lang
  ) select
    x_operation_plan_id
   ,x_last_updated_by
   ,x_last_update_date
   ,x_created_by
   ,x_creation_date
   ,x_last_update_login
   ,x_operation_plan_name
   ,x_description
   ,l.language_code
   ,userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from WMS_OP_PLANS_TL T
    where T.OPERATION_PLAN_ID = X_OPERATION_PLAN_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 159


END INSERT_ROW;

Line: 162


PROCEDURE UPDATE_ROW (
   x_operation_plan_id         	    IN     NUMBER
  ,x_last_updated_by                IN     NUMBER
  ,x_last_update_date               IN     DATE
  ,x_last_update_login              IN     NUMBER
  ,x_operation_plan_name            IN     VARCHAR2
  ,x_language                       IN     VARCHAR2
  ,x_source_lang                    IN     VARCHAR2
  ,x_description                    IN     VARCHAR2
  ,x_system_task_type               IN     NUMBER
  ,x_organization_id                IN     NUMBER
  ,x_user_defined                   IN     VARCHAR2
  ,x_enabled_flag                   IN     VARCHAR2
  ,x_effective_date_from            IN     DATE
  ,x_effective_date_to              IN     DATE
  ,x_activity_type_id               IN     NUMBER
  ,x_common_to_all_org              IN     VARCHAR2
  ,x_plan_type_id                   IN     NUMBER
  ,x_attribute_category             IN     VARCHAR2 DEFAULT NULL
  ,x_attribute1                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute2                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute3                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute4                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute5                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute6                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute7                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute8                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute9                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute10                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute11                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute12                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute13                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute14                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute15                    IN     VARCHAR2 DEFAULT NULL
  ,x_default_flag                   IN     VARCHAR2
  ,x_template_flag                  IN     VARCHAR2
  ,x_crossdock_to_wip_flag          IN     VARCHAR2
  )IS
BEGIN
   UPDATE WMS_OP_PLANS_B SET
       last_updated_by 	= x_last_updated_by
      ,last_update_date = x_last_update_date
      ,last_update_login = x_last_update_login
      ,system_task_type = x_system_task_type
      ,organization_id = x_organization_id
      ,user_defined = x_user_defined
      ,enabled_flag = x_enabled_flag
      ,effective_date_from = x_effective_date_from
      ,effective_date_to  = x_effective_date_to
      ,activity_type_id = x_activity_type_id
      ,common_to_all_org = x_common_to_all_org
      ,plan_type_id = x_plan_type_id
      ,attribute_category = x_attribute_category
      ,attribute1 = x_attribute1
      ,attribute2 = x_attribute2
      ,attribute3 = x_attribute3
      ,attribute4 = x_attribute4
      ,attribute5 = x_attribute5
      ,attribute6 = x_attribute6
      ,attribute7 = x_attribute7
      ,attribute8 = x_attribute8
      ,attribute9 = x_attribute9
      ,attribute10 = x_attribute10
      ,attribute11 = x_attribute11
      ,attribute12 = x_attribute12
      ,attribute13 = x_attribute13
      ,attribute14 = x_attribute14
      ,attribute15 = x_attribute15
      ,default_flag = x_default_flag
      ,template_flag = x_template_flag
     ,crossdock_to_wip_flag = x_crossdock_to_wip_flag
   WHERE operation_plan_id = x_operation_plan_id;

Line: 239


  update WMS_OP_PLANS_TL set
    operation_plan_name = x_operation_plan_name,
    description = x_description,
    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 OPERATION_PLAN_ID = X_OPERATION_PLAN_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 249


END UPDATE_ROW;

Line: 254


  ,x_last_update_date               IN     VARCHAR2
  ,x_system_task_type               IN     NUMBER
  ,x_organization_id                IN     NUMBER
  ,x_user_defined                   IN     VARCHAR2
  ,x_enabled_flag                   IN     VARCHAR2
  ,x_effective_date_from            IN     DATE
  ,x_effective_date_to              IN     DATE
  ,x_activity_type_id               IN     NUMBER
  ,x_common_to_all_org              IN     VARCHAR2
  ,x_plan_type_id                   IN     NUMBER
  ,x_attribute_category             IN     VARCHAR2 DEFAULT NULL
  ,x_attribute1                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute2                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute3                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute4                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute5                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute6                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute7                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute8                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute9                     IN     VARCHAR2 DEFAULT NULL
  ,x_attribute10                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute11                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute12                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute13                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute14                    IN     VARCHAR2 DEFAULT NULL
  ,x_attribute15                    IN     VARCHAR2 DEFAULT NULL
  ,x_operation_plan_name            IN     VARCHAR2
  ,x_description                    IN     VARCHAR2
  ,x_default_flag                   IN     VARCHAR2
  ,x_template_flag                  IN     VARCHAR2
  ,x_crossdock_to_wip_flag          IN     VARCHAR2
  ,x_custom_mode 		    IN 	   VARCHAR2
  ) IS

      l_operation_plan_id   NUMBER;

Line: 291


      f_ludate  date;    -- entity update date in file

Line: 293


      db_ludate date;    -- entity update date in db

Line: 299


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

Line: 302


      select LAST_UPDATED_BY,LAST_UPDATE_DATE
      INTO db_luby,db_ludate
      from wms_op_plans_B where
      operation_plan_id = l_operation_plan_id ;

Line: 310


      WMS_OP_PLANS_PKG.update_row (
   		x_operation_plan_id         	 => l_operation_plan_id
  	       ,x_last_updated_by                => f_luby
  	       ,x_last_update_date               => f_ludate
               ,x_last_update_login              => 0
               ,x_operation_plan_name            => x_operation_plan_name
               ,x_language                       => NULL
               ,x_source_lang                    => NULL
               ,x_description                    => x_description
               ,x_system_task_type               => x_system_task_type
               ,x_organization_id                => x_organization_id
               ,x_user_defined                   => x_user_defined
               ,x_enabled_flag                   => x_enabled_flag
               ,x_effective_date_from            => x_effective_date_from
               ,x_effective_date_to              => x_effective_date_to
               ,x_activity_type_id               => x_activity_type_id
               ,x_common_to_all_org              => x_common_to_all_org
               ,x_plan_type_id                   => x_plan_type_id
               ,x_attribute_category             => x_attribute_category
               ,x_attribute1                     => x_attribute1
               ,x_attribute2                     => x_attribute2
               ,x_attribute3                     => x_attribute3
               ,x_attribute4                     => x_attribute4
               ,x_attribute5                     => x_attribute5
               ,x_attribute6                     => x_attribute6
               ,x_attribute7                     => x_attribute7
               ,x_attribute8                     => x_attribute8
               ,x_attribute9                     => x_attribute9
               ,x_attribute10                    => x_attribute10
               ,x_attribute11                    => x_attribute11
               ,x_attribute12                    => x_attribute12
               ,x_attribute13                    => x_attribute13
               ,x_attribute14                    => x_attribute14
               ,x_attribute15                    => x_attribute15
               ,x_default_flag			 => x_default_flag
	       ,x_template_flag			 => x_template_flag
	       ,x_crossdock_to_wip_flag          => x_crossdock_to_wip_flag
          );

Line: 352


	 WMS_OP_PLANS_PKG.insert_row (
                x_rowid                          => l_row_id
               ,x_operation_plan_id         	 => l_operation_plan_id
  	       ,x_last_updated_by                => f_luby
  	       ,x_last_update_date               => f_ludate
  	       ,x_created_by                     => f_luby
  	       ,x_creation_date                  => f_ludate
               ,x_last_update_login              => 0
               ,x_operation_plan_name            => x_operation_plan_name
               ,x_language                       => NULL
               ,x_source_lang                    => NULL
               ,x_description                    => x_description
               ,x_system_task_type               => x_system_task_type
               ,x_organization_id                => x_organization_id
               ,x_user_defined                   => x_user_defined
               ,x_enabled_flag                   => x_enabled_flag
               ,x_effective_date_from            => x_effective_date_from
               ,x_effective_date_to              => x_effective_date_to
               ,x_activity_type_id               => x_activity_type_id
               ,x_common_to_all_org              => x_common_to_all_org
               ,x_plan_type_id                   => x_plan_type_id
               ,x_attribute_category             => x_attribute_category
               ,x_attribute1                     => x_attribute1
               ,x_attribute2                     => x_attribute2
               ,x_attribute3                     => x_attribute3
               ,x_attribute4                     => x_attribute4
               ,x_attribute5                     => x_attribute5
               ,x_attribute6                     => x_attribute6
               ,x_attribute7                     => x_attribute7
               ,x_attribute8                     => x_attribute8
               ,x_attribute9                     => x_attribute9
               ,x_attribute10                    => x_attribute10
               ,x_attribute11                    => x_attribute11
               ,x_attribute12                    => x_attribute12
               ,x_attribute13                    => x_attribute13
               ,x_attribute14                    => x_attribute14
               ,x_attribute15                    => x_attribute15
               ,x_default_flag			 => x_default_flag
	       ,x_template_flag			 => x_template_flag
	       ,x_crossdock_to_wip_flag          => x_crossdock_to_wip_flag
         );

Line: 399


PROCEDURE delete_row (
  x_operation_plan_id  IN NUMBER
) IS

BEGIN

  delete from WMS_OP_PLANS_B
  where OPERATION_PLAN_ID = X_OPERATION_PLAN_ID;

Line: 412


  delete from WMS_OP_PLANS_TL
  where operation_plan_id = X_OPERATION_PLAN_ID;

Line: 419


END delete_row;

Line: 457


     cursor C IS SELECT
   system_task_type
  ,organization_id
  ,user_defined
  ,enabled_flag
  ,effective_date_from
  ,effective_date_to
  ,activity_type_id
  ,common_to_all_org
  ,plan_type_id
  ,attribute_category
  ,attribute1
  ,attribute2
  ,attribute3
  ,attribute4
  ,attribute5
  ,attribute6
  ,attribute7
  ,attribute8
  ,attribute9
  ,attribute10
  ,attribute11
  ,attribute12
  ,attribute13
  ,attribute14
  ,attribute15
  ,default_flag
  ,template_flag
  ,crossdock_to_wip_flag
       from wms_op_plans_b
	where operation_plan_id = x_operation_plan_id
  	for UPDATE of OPERATION_PLAN_ID NOWAIT;

Line: 492


cursor c1 is select
        operation_plan_name,
        description,
        decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
        from wms_op_plans_tl
        where operation_plan_id = x_operation_plan_id
        and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
        for UPDATE of OPERATION_PLAN_ID NOWAIT;

Line: 507


		FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');

Line: 626


   x_last_update_date         IN  VARCHAR2 ,
   x_operation_plan_name      IN  VARCHAR2 ,
   x_description              IN  VARCHAR2 ,
   x_custom_mode 	      IN  VARCHAR2
   ) IS

   f_luby    number;  -- entity owner in file

Line: 633


   f_ludate  date;    -- entity update date in file

Line: 635


   db_ludate date;    -- entity update date in db

Line: 641


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

Line: 643


   UPDATE wms_op_plans_b SET
     last_update_date        = Sysdate,
     last_updated_by         = Decode(x_owner, 'SEED', 1, 0),
     last_update_login       = 0
     WHERE operation_plan_id = fnd_number.canonical_to_number(x_operation_plan_id);

Line: 651


   select LAST_UPDATED_BY,LAST_UPDATE_DATE
   INTO db_luby,db_ludate
   from wms_op_plans_B where
    operation_plan_id = fnd_number.canonical_to_number(x_operation_plan_id);

Line: 659


   UPDATE wms_op_plans_tl SET
     operation_plan_name     = x_operation_plan_name,
     description             = x_description,
     last_update_date        = f_ludate,
     last_updated_by         = f_luby,
     last_update_login       = 0,
     source_lang             = userenv('LANG')
     WHERE operation_plan_id = fnd_number.canonical_to_number(x_operation_plan_id) AND userenv('LANG') IN (language, source_lang);

Line: 680


   delete from WMS_OP_PLANS_TL T
     where not exists
     (select NULL
      from WMS_OP_PLANS_B B
      where B.OPERATION_PLAN_ID = T.OPERATION_PLAN_ID
      );

Line: 687


   update WMS_OP_PLANS_TL T set (
      OPERATION_PLAN_NAME,
      DESCRIPTION
    ) = (select
      B.OPERATION_PLAN_NAME,
      B.DESCRIPTION
    from WMS_OP_PLANS_TL B
    where B.OPERATION_PLAN_ID = T.OPERATION_PLAN_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.OPERATION_PLAN_ID,
      T.LANGUAGE
  ) in (select
      SUBT.OPERATION_PLAN_ID,
      SUBT.LANGUAGE
    from WMS_OP_PLANS_TL SUBB, WMS_OP_PLANS_TL SUBT
    where SUBB.OPERATION_PLAN_ID = SUBT.OPERATION_PLAN_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.OPERATION_PLAN_NAME <> SUBT.OPERATION_PLAN_NAME
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
  ));

Line: 711


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