APPS.CN_RULE_PUB 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: 79

	SELECT rule_id
	  FROM cn_rules
	  WHERE ruleset_id = p_ruleset_id
	  AND name = p_rule_name
	  AND ORG_ID=p_org_id;

Line: 111

   x_loading_status := 'CN_INSERTED';

Line: 235

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

Line: 254

      SELECT ruleset_id
	INTO l_rule_rec_pvt.ruleset_id
	FROM cn_rulesets
	WHERE name = l_rule_rec.ruleset_name
	AND start_date = l_rule_rec.start_date
	AND end_date = l_rule_rec.end_date and
	org_id=l_rule_rec.org_id;

Line: 268

   SELECT count(1)
     INTO l_count
     FROM cn_revenue_classes
     WHERE name = l_rule_rec.revenue_class_name;

Line: 284

      SELECT revenue_class_id
	INTO l_rule_rec_pvt.revenue_class_id
	FROM cn_revenue_classes
	WHERE name = l_rule_rec.revenue_class_name;

Line: 295

   SELECT count(1)
     INTO l_count
     FROM gl_code_combinations
     WHERE code_combination_id = l_rule_rec.expense_ccid;

Line: 319

   SELECT count(1)
     INTO l_count
     FROM gl_code_combinations
     WHERE code_combination_id = l_rule_rec.liability_ccid;

Line: 341

   SELECT count(1)
     INTO l_count
     FROM cn_rules
     WHERE name = l_rule_rec.parent_rule_name
     AND ruleset_id = l_rule_rec_pvt.ruleset_id
     AND ORG_ID=l_rule_rec_pvt.ORG_ID;

Line: 371

      select cn_rules_s.nextval
        into l_rule_rec_pvt.rule_id
        from dual;

Line: 375

      SELECT Nvl(MAX(sequence_number) + 1, 1)
	INTO l_rule_rec_pvt.sequence_number
	FROM cn_rules_hierarchy
	WHERE ruleset_id = l_rule_rec_pvt.ruleset_id
	AND parent_rule_id = l_rule_rec_pvt.parent_rule_id and
	org_id=l_rule_rec_pvt.org_id;

Line: 616

PROCEDURE Update_Rule
  ( 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_rule_rec		IN OUT NOCOPY  CN_Rule_PUB.rule_rec_type,
    p_rule_rec			IN OUT NOCOPY  CN_Rule_PUB.rule_rec_type
    ) IS

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

Line: 648

	  SELECT cnrv.rule_id, cnrv.parent_rule_id
	    FROM cn_rules_v cnrv, cn_rules cnr1, cn_rules cnr2
	    WHERE cnr1.name = l_old_rule_rec.rule_name
	    AND cnr2.name = l_old_rule_rec.parent_rule_name
	    AND cnr1.ruleset_id = p_ruleset_id
	    AND cnr2.ruleset_id = p_ruleset_id
	    AND cnr1.rule_id = cnrv.rule_id
	    AND cnr2.rule_id = cnrv.parent_rule_id
	    and cnrv.org_id =cnr1.org_id
	    and cnr1.org_id = cnr2.org_id
	    and cnrv.org_id = p_org_id;

Line: 662

	  SELECT rule_id
	    FROM cn_rules
	    WHERE name = l_rule_rec.parent_rule_name
	    AND ruleset_id = p_ruleset_id
	    and org_id=p_org_id;

Line: 672

   SAVEPOINT Update_Rule;

Line: 690

   x_loading_status := 'CN_UPDATED';

Line: 706

				'UPDATE_RULE',
				'B',
				'C')
   THEN
     cn_rule_pub_cuhk.update_rule_pre
     (p_api_version           	=> p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_rec                => l_rule_rec,
      p_old_rule_rec            => l_old_rule_rec);

Line: 733

				'UPDATE_RULE',
				'B',
				'V')
   THEN
     cn_rule_pub_vuhk.update_rule_pre
     (p_api_version           	=> p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_rec                => l_rule_rec,
      p_old_rule_rec            => l_old_rule_rec);

Line: 846

   SELECT count(1)
     INTO l_count
     FROM cn_rulesets
     WHERE name = l_old_rule_rec.ruleset_name
     AND start_date = l_old_rule_rec.start_date
     AND end_date = l_old_rule_rec.end_date;

Line: 863

      SELECT ruleset_id,org_id
	INTO l_old_rule_rec_pvt.ruleset_id,l_old_rule_rec_pvt.org_id
	FROM cn_rulesets
	WHERE name = l_old_rule_rec.ruleset_name
	AND start_date = l_old_rule_rec.start_date
	AND end_date = l_old_rule_rec.end_date;

Line: 872

   SELECT count(1)
     INTO l_count
     FROM cn_rulesets
     WHERE name = l_rule_rec.ruleset_name
     AND start_date = l_rule_rec.start_date
     AND end_date = l_rule_rec.end_date;

Line: 889

      SELECT ruleset_id,org_id
	INTO l_rule_rec_pvt.ruleset_id,l_rule_rec_pvt.org_id
	FROM cn_rulesets
	WHERE name = l_rule_rec.ruleset_name
	AND start_date = l_rule_rec.start_date
	AND end_date = l_rule_rec.end_date;

Line: 898

   SELECT count(1)
     INTO l_count
     FROM cn_rules
     WHERE name = l_old_rule_rec.parent_rule_name
     AND ruleset_id = l_old_rule_rec_pvt.ruleset_id and
     org_id=l_old_rule_rec_pvt.org_id;

Line: 918

   SELECT count(1)
     INTO l_count
     FROM cn_rules
     WHERE name = l_rule_rec.parent_rule_name
     AND ruleset_id = l_rule_rec_pvt.ruleset_id
     and org_id=l_rule_rec_pvt.org_id;

Line: 941

      SELECT count(1)
	INTO l_count
	FROM cn_revenue_classes
	WHERE name = l_rule_rec.revenue_class_name;

Line: 957

	 SELECT revenue_class_id
	   INTO l_rule_rec_pvt.revenue_class_id
	   FROM cn_revenue_classes
	   WHERE name = l_rule_rec.revenue_class_name;

Line: 967

      SELECT count(1)
	INTO l_count
	FROM gl_code_combinations
	WHERE code_combination_id = l_rule_rec.expense_ccid;

Line: 990

      SELECT count(1)
	INTO l_count
	FROM gl_code_combinations
	WHERE code_combination_id = l_rule_rec.liability_ccid;

Line: 1025

		cn_rule_pvt.update_rule
		  (p_api_version           	=> p_api_version,
		   p_init_msg_list              => p_init_msg_list,
		   p_commit	    		=> p_commit,
		   p_validation_level     	=> p_validation_level,
		   x_return_status		=> x_return_status,
		   x_msg_count		        => x_msg_count,
		   x_msg_data		        => x_msg_data,
		   x_loading_status             => x_loading_status,
		   p_old_rule_rec               => l_old_rule_rec_pvt,
		   p_rule_rec                   => l_rule_rec_pvt);

Line: 1046

	   cn_rule_pvt.update_rule
	     (p_api_version           	=> p_api_version,
	      p_init_msg_list           => p_init_msg_list,
	      p_commit	    		=> p_commit,
	      p_validation_level     	=> p_validation_level,
	      x_return_status		=> x_return_status,
	      x_msg_count		=> x_msg_count,
	      x_msg_data		=> x_msg_data,
	      x_loading_status          => x_loading_status,
	      p_old_rule_rec            => l_old_rule_rec_pvt,
	      p_rule_rec                => l_rule_rec_pvt);

Line: 1082

				'UPDATE_RULE',
				'A',
				'V')
   THEN
     cn_rule_pub_vuhk.update_rule_post
     (p_api_version           	=> p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_rec                => l_rule_rec,
      p_old_rule_rec            => l_old_rule_rec);

Line: 1109

				'UPDATE_RULE',
				'A',
				'C')
   THEN
     cn_rule_pub_cuhk.update_rule_post
     (p_api_version           	=> p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_rec                => l_rule_rec,
      p_old_rule_rec            => l_old_rule_rec);

Line: 1138

				'UPDATE_RULE',
				'M',
				'M')
     THEN
      IF  cn_rule_pub_cuhk.ok_to_generate_msg
--	 (p_rule_rec     => l_rule_rec)
         (p_rule_name => l_rule_rec.rule_name)
	THEN

	 -- Clear bind variables
--	 XMLGEN.clearBindValues;

Line: 1214

      ROLLBACK TO Update_Rule;

Line: 1222

      ROLLBACK TO Update_Rule;

Line: 1231

      ROLLBACK TO Update_Rule;

Line: 1281

PROCEDURE Delete_Rule
  ( 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_rule_name			IN	cn_rules.name%TYPE,
    p_ruleset_name              IN      cn_rulesets.name%TYPE,
    p_ruleset_start_date        IN      cn_rulesets.start_date%TYPE,
    p_ruleset_end_date          IN      cn_rulesets.end_date%TYPE
    ) IS


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

Line: 1304

	  SELECT rule_id
	    FROM cn_rules
	    WHERE ruleset_id = p_ruleset_id
	    AND name = p_rule_name AND
	    org_id=p_org_id;

Line: 1326

   SAVEPOINT Delete_Rule;

Line: 1344

   x_loading_status := 'CN_DELETED';

Line: 1361

				'DELETE_RULE',
				'B',
				'C')
   THEN
     cn_rule_pub_cuhk.delete_rule_pre
     (p_api_version             => p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_name               => l_rule_name,
      p_ruleset_name            => l_ruleset_name,
      p_ruleset_start_date      => l_ruleset_start_date,
      p_ruleset_end_date        => l_ruleset_end_date);

Line: 1390

				'DELETE_RULE',
				'B',
				'V')
   THEN
     cn_rule_pub_vuhk.delete_rule_pre
     (p_api_version             => p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_name               => l_rule_name,
      p_ruleset_name            => l_ruleset_name,
      p_ruleset_start_date      => l_ruleset_start_date,
      p_ruleset_end_date        => l_ruleset_end_date);

Line: 1421

   SELECT COUNT(1)
     INTO l_count
     FROM cn_rulesets
     WHERE name = l_ruleset_name
     AND start_date = l_ruleset_start_date
     AND end_date = l_ruleset_end_date;

Line: 1440

      SELECT ruleset_id,org_id
	INTO l_ruleset_id,l_org_id
	FROM cn_rulesets
	WHERE name = l_ruleset_name
	AND start_date = l_ruleset_start_date
	AND end_date = l_ruleset_end_date;

Line: 1451

       cn_rule_pvt.delete_rule
	 (p_api_version         => p_api_version,
	  p_init_msg_list       => p_init_msg_list,
	  p_commit	    	=> p_commit,
	  p_validation_level    => p_validation_level,
	  x_return_status	=> x_return_status,
	  x_msg_count		=> x_msg_count,
	  x_msg_data		=> x_msg_data,
	  x_loading_status      => x_loading_status,
	  p_ruleset_id          => l_ruleset_id,
	  p_rule_id             => i.rule_id,
	  p_org_id              => l_org_id);

Line: 1485

				'DELETE_RULE',
				'A',
				'V')
   THEN
     cn_rule_pub_vuhk.delete_rule_post
     (p_api_version             => p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_name               => l_rule_name,
      p_ruleset_name            => l_ruleset_name,
      p_ruleset_start_date      => l_ruleset_start_date,
      p_ruleset_end_date        => l_ruleset_end_date);

Line: 1514

				'DELETE_RULE',
				'A',
				'C')
   THEN
     cn_rule_pub_cuhk.delete_rule_post
     (p_api_version             => p_api_version,
      p_init_msg_list           => p_init_msg_list,
      p_commit	    		=> p_commit,
      p_validation_level	=> p_validation_level,
      x_return_status		=> x_return_status,
      x_msg_count		=> x_msg_count,
      x_msg_data		=> x_msg_data,
      x_loading_status          => x_loading_status,
      p_rule_name               => l_rule_name,
      p_ruleset_name            => l_ruleset_name,
      p_ruleset_start_date      => l_ruleset_start_date,
      p_ruleset_end_date        => l_ruleset_end_date);

Line: 1545

				'DELETE_RULE',
				'M',
				'M')
     THEN
      IF  cn_rule_pub_cuhk.ok_to_generate_msg
      	(p_rule_name          => l_rule_name)
	THEN
	 -- Clear bind variables
--	 XMLGEN.clearBindValues;

Line: 1617

      ROLLBACK TO Delete_Rule;

Line: 1625

      ROLLBACK TO Delete_Rule;

Line: 1634

      ROLLBACK TO Delete_Rule;

Line: 1658

  select distinct(name) ruleName
   from cn_rules
   where rule_id in (
    select PARENT_RULE_ID
    from cn_rules_hierarchy
    where ruleset_id = p_ruleset_id
    connect by prior PARENT_RULE_ID = rule_id
    start with rule_id = p_rule_id);

Line: 1668

   select name
   from cn_rules
   where rule_id = p_rule_id;