APPS.CN_SYIN_RULESETS_PKG SQL Statements

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

Line: 15

      SELECT name INTO X_column_name FROM cn_obj_columns_v
	WHERE column_id = X_column_id;

Line: 30

procedure INSERT_ROW
   (
   X_ROWID in out NOCOPY VARCHAR2,
   X_RULESET_ID in NUMBER := FND_API.G_MISS_NUM,
   X_RULESET_STATUS in VARCHAR2 := FND_API.G_MISS_CHAR,
   X_DESTINATION_COLUMN_ID in NUMBER := FND_API.G_MISS_NUM,
   X_REPOSITORY_ID in NUMBER := FND_API.G_MISS_NUM,
   X_NAME in VARCHAR2 := FND_API.G_MISS_CHAR,
   x_module_type IN VARCHAR2 := fnd_api.g_miss_char,
   x_start_date IN DATE := fnd_api.g_miss_date,
   x_end_date IN DATE := fnd_api.g_miss_date,
   X_CREATION_DATE in DATE := FND_API.G_MISS_DATE,
   X_CREATED_BY in NUMBER := FND_API.G_MISS_NUM,
   X_LAST_UPDATE_DATE in DATE := FND_API.G_MISS_DATE,
   X_LAST_UPDATED_BY in NUMBER := FND_API.G_MISS_NUM,
   X_LAST_UPDATE_LOGIN in NUMBER := FND_API.G_MISS_NUM,
   X_ORG_ID in NUMBER := FND_API.G_MISS_NUM)
  IS


     l_rowid ROWID;

Line: 55

  insert into CN_RULESETS_ALL_B
    (
     RULESET_STATUS,
     RULESET_ID,
     DESTINATION_COLUMN_ID,
     REPOSITORY_ID,
     start_date,
     end_date,
     module_type,
     CREATION_DATE,
     CREATED_BY,
     LAST_UPDATE_DATE,
     LAST_UPDATED_BY,
     LAST_UPDATE_LOGIN,
     OBJECT_VERSION_NUMBER,
     ORG_ID
     ) values
    (
     x_RULESET_STATUS,
     x_RULESET_ID,
     x_DESTINATION_COLUMN_ID,
     x_REPOSITORY_ID,
     x_start_date,
     x_end_date,
     x_module_type,
     x_CREATION_DATE,
     x_CREATED_BY,
     x_LAST_UPDATE_DATE,
     x_LAST_UPDATED_BY,
     x_LAST_UPDATE_LOGIN,
     1,
     x_org_id
     );

Line: 89

  insert into CN_RULESETS_ALL_TL (
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATION_DATE,
    NAME,
    RULESET_ID,
    LANGUAGE,
    SOURCE_LANG,
    ORG_ID
  ) select
    x_CREATED_BY,
    x_LAST_UPDATE_DATE,
    x_LAST_UPDATED_BY,
    x_LAST_UPDATE_LOGIN,
    x_CREATION_DATE,
    x_NAME,
    x_RULESET_ID,
    L.LANGUAGE_CODE,
    userenv('LANG'),
    x_org_id
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
 and not exists
    (select NULL
    from CN_RULESETS_ALL_TL T
    where T.RULESET_ID = x_RULESET_ID
    and T.LANGUAGE = L.language_code AND
    T.ORG_ID=x_org_id);

Line: 128

 cn_syin_rules_pkg.insert_row_into_cn_rules_only(
  x_rowid => l_rowid,
  X_RULE_ID => -1002,
  X_RULESET_ID => X_ruleset_id,
  X_NAME => 'BASE_RULE',
  X_ORG_ID => X_ORG_ID);

Line: 136

end INSERT_ROW;

Line: 140

procedure UPDATE_ROW
  (
  X_RULESET_ID in NUMBER,
  X_OBJECT_VERSION_NUMBER IN NUMBER,
  X_RULESET_STATUS in VARCHAR2,
  X_DESTINATION_COLUMN_ID in NUMBER,
  X_REPOSITORY_ID in NUMBER,
  x_start_date IN DATE,
  x_end_date IN DATE,
   X_NAME in VARCHAR2,
   x_module_type IN VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_ORG_ID in NUMBER
) is
BEGIN

  update CN_RULESETS_ALL_B set
    RULESET_STATUS = X_RULESET_STATUS,
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    DESTINATION_COLUMN_ID = X_DESTINATION_COLUMN_ID,
    REPOSITORY_ID = X_REPOSITORY_ID,
    start_date = x_start_date,
    end_date = x_end_date,
    module_type = x_module_type,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where RULESET_ID = x_ruleset_id  AND
  ORG_ID=X_ORG_ID;

Line: 176

  update CN_RULESETS_ALL_TL set
    NAME = X_NAME,
    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 RULESET_ID = X_RULESET_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG)  AND
   ORG_ID=X_ORG_ID;

Line: 189

end UPDATE_ROW;

Line: 191

procedure DELETE_ROW (
  X_RULESET_ID in NUMBER,
  X_ORG_ID IN NUMBER
) is
begin
  delete from CN_RULESETS_ALL_TL
  where RULESET_ID = x_ruleset_id  AND
  ORG_ID=X_ORG_ID;

Line: 204

  delete from CN_RULESETS_ALL_B
  where RULESET_ID = x_ruleset_id  AND
  ORG_ID=X_ORG_ID;

Line: 211

end DELETE_ROW;

Line: 216

  delete from CN_RULESETS_ALL_TL T
  where not exists
    (select NULL
    from CN_RULESETS_ALL_B B
    where B.RULESET_ID = T.ruleset_id
    and   B.ORG_ID = T.ORG_ID
    );

Line: 224

  update CN_RULESETS_ALL_TL T set (
      NAME
    ) = (select
      B.NAME
    from CN_RULESETS_ALL_TL B
    where B.RULESET_ID = T.RULESET_ID
    and B.LANGUAGE = T.source_lang
    and   B.ORG_ID = T.ORG_ID 	 )
  where (
      T.RULESET_ID,
      T.LANGUAGE
  ) in (select
      SUBT.RULESET_ID,
      SUBT.LANGUAGE
    from CN_RULESETS_ALL_TL SUBB, CN_RULESETS_ALL_TL SUBT
    where SUBB.RULESET_ID = SUBT.RULESET_ID
    and SUBB.LANGUAGE = SUBT.source_lang
    and   SUBB.ORG_ID = SUBT.ORG_ID
    and (SUBB.NAME <> SUBT.name
      or (SUBB.NAME is null and SUBT.NAME is not null)
      or (SUBB.NAME is not null and SUBT.NAME is null)
  ));

Line: 247

  insert into CN_RULESETS_ALL_TL (
    ORG_ID,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATION_DATE,
    NAME,
    RULESET_ID,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.ORG_ID,
    B.CREATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.CREATION_DATE,
    B.NAME,
    B.RULESET_ID,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from CN_RULESETS_ALL_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from CN_RULESETS_ALL_TL T
    where T.RULESET_ID = B.RULESET_ID
    and T.LANGUAGE = L.LANGUAGE_CODE
    and   T.ORG_ID = B.ORG_ID );

Line: 313

   UPDATE cn_rulesets_all_b SET
     destination_column_id = x_destination_column_id,
     repository_id = x_repository_id,
     ruleset_status = x_ruleset_status,
     last_update_date = sysdate,
     last_updated_by = user_id,
     last_update_login = 0
     WHERE ruleset_id = x_ruleset_id and org_id=x_org_id ;

Line: 324

      INSERT INTO cn_rulesets_all_b
	(RULESET_ID,
	 DESTINATION_COLUMN_ID,
	 REPOSITORY_ID,
	 RULESET_STATUS,
	 CREATION_DATE,
	 CREATED_BY,
	 LAST_UPDATE_DATE,
	 LAST_UPDATED_BY,
	 LAST_UPDATE_LOGIN,
	 ORG_ID
	 ) VALUES
	(X_RULESET_ID,
	 X_DESTINATION_COLUMN_ID,
	 X_REPOSITORY_ID,
	 X_RULESET_STATUS,
	 sysdate,
	 user_id,
	 sysdate,
	 user_id,
	 0,
	 x_org_id
	 );

Line: 349

   UPDATE cn_rulesets_all_tl  SET
     name = x_name,
     last_update_date = sysdate,
     last_updated_by = user_id,
     last_update_login = 0,
     source_lang = userenv('LANG')
     WHERE ruleset_id = x_ruleset_id AND org_id=x_org_id
     AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 360

      INSERT INTO cn_rulesets_all_tl
	(ruleset_id,
	 name,
	 creation_date,
	 created_by,
	 last_update_date,
	 last_updated_by,
	 last_update_login,
	 language,
	 source_lang,
	 org_id)
	SELECT
	x_ruleset_id,
	x_name,
	sysdate,
	user_id,
	sysdate,
	user_id,
	0,
	l.language_code,
	userenv('LANG'),
	x_org_id
	FROM fnd_languages l
	WHERE l.installed_flag IN ('I', 'B')
	AND NOT EXISTS
	(SELECT NULL
	 FROM cn_rulesets_all_tl t
	 WHERE t.ruleset_id = x_ruleset_id and t.org_id=x_org_id
	 AND t.language = l.language_code);

Line: 417

   UPDATE cn_rulesets_all_tl  SET
     name = x_name,
     last_update_date = sysdate,
     last_updated_by = user_id,
     last_update_login = 0,
     source_lang = userenv('LANG')
     WHERE ruleset_id = x_ruleset_id and org_id=x_org_id
     AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);