APPS.CN_RULESET_PVT SQL Statements

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

Line: 6

G_LAST_UPDATE_DATE     DATE 		     := Sysdate;

Line: 7

G_LAST_UPDATED_BY      NUMBER 		     := fnd_global.user_id;

Line: 10

G_LAST_UPDATE_LOGIN    NUMBER		     := fnd_global.login_id;

Line: 26

  SELECT name
    FROM cn_rules cr
   WHERE cr.rule_id <> -1002
     AND cr.ruleset_id = p_ruleset_id
     AND cr.org_id=p_org_id
     AND NOT EXISTS (SELECT 1
		       FROM cn_attribute_rules car
		      WHERE car.rule_id = cr.rule_id and
              car.org_id=cr.org_id);

Line: 37

   SELECT attr.ruleset_id ruleset_id,
          attr.rule_id rule_id,
          attr.column_id column_id ,
          attr.column_value column_value,
          attr.high_value,
          attr.low_value,
          cr.name rule_name,
          col.user_name,
          attr.org_id
     FROM cn_rules cr, cn_attribute_rules attr, cn_objects col
   WHERE cr.rule_id <> -1002
     and col.object_id = attr.column_id
     and col.table_id = -11803
     AND cr.ruleset_id = p_ruleset_id
     AND attr.rule_id = cr.rule_id
     and attr.ruleset_id = cr.ruleset_id
     and dimension_hierarchy_id is null and
     cr.org_id=attr.org_id and
     attr.org_id=col.org_id;

Line: 148

FUNCTION check_update_allowed
   ( p_ruleset_id  IN cn_rulesets.ruleset_id%TYPE,
    p_module_type  In cn_rulesets.module_type%TYPE,
    p_loading_status IN VARCHAR2,
    x_loading_status OUT NOCOPY VARCHAR2,
    x_org_id IN cn_rulesets.org_id%TYPE
    ) RETURN VARCHAR2 IS

      l_api_name CONSTANT VARCHAR2(30) := 'check_update_allowed';

Line: 161

	 SELECT count(1) cnt
	   FROM cn_rules_all_b
	   WHERE ruleset_id = p_ruleset_id
	   AND rule_id    <> -1002
	   AND org_id = l_env_org_id;

Line: 168

	 SELECT module_type
	   FROM cn_rulesets
	   WHERE ruleset_id = p_ruleset_id;

Line: 223

END check_update_allowed;

Line: 243

	 SELECT count(*) cnt
	   FROM cn_rulesets cnr
	   WHERE p_date BETWEEN cnr.start_date AND cnr.end_date
	   AND nvl(module_type,'X') = nvl(p_module_type,'X')
	   AND ruleset_id <> p_ruleset_id
       AND org_id=p_org_id;

Line: 254

	    SELECT count(*) cnt
	      FROM cn_rulesets cnr
	      WHERE cnr.start_date BETWEEN p_start_date AND p_end_date
	      AND cnr.end_date BETWEEN p_start_date AND p_end_date
	      AND nvl(module_type,'X') = nvl(p_module_type,'X')
	      AND ruleset_id <> p_ruleset_id and
          org_id=p_org_id;

Line: 437

   x_loading_status := 'CN_INSERTED';

Line: 483

   SELECT count(1)
     INTO l_count
     FROM cn_rulesets
     WHERE name = p_ruleset_rec.ruleset_name
     AND module_type = p_ruleset_rec.module_type
     AND start_date = p_ruleset_rec.start_date
     AND end_date = p_ruleset_rec.end_date and
     ORG_ID=p_ruleset_rec.org_id;

Line: 506

   SELECT Decode(p_ruleset_rec.ruleset_id, NULL, cn_rulesets_s.NEXTVAL,
		 p_ruleset_rec.ruleset_id)
     INTO l_ruleset_id
     FROM dual;

Line: 537

   cn_syin_rulesets_pkg.insert_row
     (
      x_rowid                          => l_rowid,
      x_ruleset_id                     => l_ruleset_id,
      x_end_date                       => p_ruleset_rec.end_date,
      x_ruleset_status                 => 'UNSYNC',
      x_destination_column_id          => -11980,
      x_repository_id                  => 100,
      x_start_date                     => p_ruleset_rec.start_date,
      x_name                           => p_ruleset_rec.ruleset_name,
      x_module_type                    => p_ruleset_rec.module_type,
      x_creation_date                  => sysdate,
      x_created_by                     => g_created_by,
      x_last_update_date               => sysdate,
      x_last_updated_by                => g_last_updated_by,
      x_last_update_login              => g_last_update_login,
      x_org_id                         =>p_ruleset_rec.org_id
      );

Line: 643

PROCEDURE Update_Ruleset
  ( p_api_version           	IN	NUMBER,
    p_init_msg_list		IN	VARCHAR2 := FND_API.G_FALSE,
    p_commit	    		IN  	VARCHAR2 := FND_API.G_FALSE,
    p_validation_level		IN  	NUMBER	 := FND_API.G_VALID_LEVEL_FULL,
    x_return_status	 OUT NOCOPY VARCHAR2,
    x_msg_count		 OUT NOCOPY NUMBER,
    x_msg_data		 OUT NOCOPY VARCHAR2,
    x_loading_status            OUT NOCOPY     VARCHAR2,
    p_old_ruleset_rec		IN OUT NOCOPY  CN_Ruleset_PVT.ruleset_rec_type,
    p_ruleset_rec		IN OUT NOCOPY  CN_Ruleset_PVT.ruleset_rec_type
    ) IS


  CURSOR l_ovn_csr IS
    SELECT nvl(object_version_number,1)
      FROM cn_rulesets
      WHERE ruleset_id = p_old_ruleset_rec.ruleset_id AND
      ORG_ID=p_old_ruleset_rec.org_id;

Line: 666

    SELECT count(1)
      FROM cn_rules_all_b
      WHERE ruleset_id = p_old_ruleset_rec.ruleset_id
      and rule_id <> -1002 and
      org_id = p_old_ruleset_rec.org_id;

Line: 672

       l_api_name		CONSTANT VARCHAR2(30)	:= 'Update_Ruleset';

Line: 685

     SELECT *
      FROM cn_rulesets
     WHERE ruleset_id = l_ruleset_id and
     org_id=l_org_id;

Line: 697

   SAVEPOINT Update_Ruleset;

Line: 715

   x_loading_status := 'CN_UPDATED';

Line: 725

   select name,
          start_date,
          end_date,
          module_type
    into p_old_ruleset_rec.ruleset_name,
         p_old_ruleset_rec.start_date,
         p_old_ruleset_rec.end_date,
         p_old_ruleset_rec.module_type
    from cn_rulesets
   where ruleset_id = p_ruleset_rec.ruleset_id and
   org_id=p_ruleset_rec.org_id;

Line: 790

      SELECT count(1)
	INTO l_count
	FROM cn_rulesets
	WHERE ruleset_id = p_old_ruleset_rec.ruleset_id and
    org_id=p_old_ruleset_rec.org_id;

Line: 868

    IF check_update_allowed
   ( p_old_ruleset_rec.ruleset_id,
     p_ruleset_rec.module_type,
     x_loading_status,
     x_loading_status,
     P_old_ruleset_rec.org_id) = fnd_api.g_true
   THEN
      RAISE fnd_api.g_exc_error;

Line: 879

      cn_syin_rulesets_pkg.update_row

	(
	 x_ruleset_id                     => p_old_ruleset_rec.ruleset_id,
         x_object_version_number	  => p_ruleset_rec.object_version_number,
	 x_end_date                       => p_ruleset_rec.end_date,
	 x_ruleset_status                 => 'UNSYNC',
	 x_destination_column_id          => -11980,
	 x_repository_id                  => 100,
	 x_start_date                     => p_ruleset_rec.start_date,
	 x_name                           => p_ruleset_rec.ruleset_name,
	 x_module_type                    => p_ruleset_rec.module_type,
	 x_last_update_date               => sysdate,
	 x_last_updated_by                => g_last_updated_by,
	 x_last_update_login              => g_last_update_login,
         x_org_id                         => p_ruleset_rec.org_id
     );

Line: 954

       cn_syin_rulesets_pkg.update_row(p_ruleset_rec.ruleset_id,
                                   l_get_ruleset_data_rec.object_version_number,
                                   'CONCFAIL',
                                   l_get_ruleset_data_rec.destination_column_id,
                                   l_get_ruleset_data_rec.repository_id,
                                   l_get_ruleset_data_rec.start_date,
                                   l_get_ruleset_data_rec.end_date,
                                   l_get_ruleset_data_rec.name,
                                   l_get_ruleset_data_rec.module_type,
                                   null,
                                   null,
                                   null,
                                   p_ruleset_rec.org_id);

Line: 992

      ROLLBACK TO Update_Ruleset;

Line: 1003

      ROLLBACK TO Update_Ruleset;

Line: 1013

      ROLLBACK TO Update_Ruleset;