DBA Data[Home] [Help]

APPS.CN_RULEATTRIBUTE_PVT SQL Statements

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

Line: 23

   SELECT *
      FROM cn_objects
     WHERE object_id = p_column_id
       AND table_id IN (-11803,-16134) AND
       ORG_ID=p_org_id;
Line: 33

   SELECT name
      FROM cn_rules
     WHERE rule_id = p_rule_id
       AND ruleset_id = p_ruleset_id AND
       ORG_ID=p_org_id;
Line: 40

   SELECT name
      FROM cn_rulesets
     WHERE ruleset_id = p_ruleset_id AND
     ORG_ID=p_org_id;
Line: 166

select
low_value,
high_value,
column_value,
not_flag
from cn_attribute_rules where attribute_rule_id=c_attribute_rule_id and org_id=c_org_id;
Line: 219

select
low_value,
high_value,
column_value,
not_flag
from cn_attribute_rules where attribute_rule_id=c_attribute_rule_id and org_id=c_org_id;
Line: 348

    SELECT object_id
      INTO l_object_column
      FROM cn_objects
     WHERE name = p_object_name AND org_id=p_org_id
       AND table_id IN (-11803,-16134);
Line: 368

    SELECT COUNT(*)
      INTO l_counter
      FROM cn_attribute_rules
     WHERE column_id = l_object_column
       AND column_value = p_value_1
       AND rule_id = p_rule_id and org_id=p_org_id
       AND   ((p_attribute_rule_id IS NOT NULL AND
                attribute_rule_id <> p_attribute_rule_id)
               OR
               (p_attribute_rule_id IS NULL))
       AND not_flag = nvl(l_not_flag,not_flag) ; -- Added Kumar
Line: 421

    SELECT COUNT(*)
      INTO l_counter
      FROM cn_attribute_rules
     WHERE column_id = l_object_column
       AND low_value = p_value_1
       AND high_value = p_value_2
       and org_id=p_org_id
      AND rule_id = p_rule_id
      AND   ((p_attribute_rule_id IS NOT NULL AND
                attribute_rule_id <> p_attribute_rule_id)
               OR
               (p_attribute_rule_id IS NULL))
      AND not_flag = nvl(l_not_flag,not_flag) ; -- Added Kumar;
Line: 462

    SELECT head_hierarchy_id
      INTO l_dim_hierarchy_id
      FROM cn_head_hierarchies
     WHERE head_hierarchy_id = p_value_1 and org_id=p_org_id;
Line: 480

    SELECT hn.value_id
      INTO l_hierarchy_value
      FROM cn_hierarchy_nodes hn,
           cn_dim_hierarchies dh
     --WHERE hn.name = p_value_2
     WHERE hn.value_id = p_value_2
       AND   hn.dim_hierarchy_id = dh.dim_hierarchy_id
       and hn.org_id=dh.org_id
       and hn.org_id=p_org_id
       AND dh.header_dim_hierarchy_id = l_dim_hierarchy_id;
Line: 503

    SELECT COUNT(*)
      INTO l_counter
      FROM cn_attribute_rules
     WHERE column_id = l_object_column
       AND dimension_hierarchy_id = l_dim_hierarchy_id
       AND column_value = l_hierarchy_value
       AND rule_id = p_rule_id
       and org_id=p_org_id
        AND   ((p_attribute_rule_id IS NOT NULL AND
                attribute_rule_id <> p_attribute_rule_id)
               OR
               (p_attribute_rule_id IS NULL))
       AND not_flag = nvl(l_not_flag,not_flag) ; -- Added Kumar;
Line: 635

  G_LAST_UPDATE_DATE     DATE                  := Sysdate;
Line: 636

  G_LAST_UPDATED_BY      NUMBER                := fnd_global.user_id;
Line: 639

  G_LAST_UPDATE_LOGIN    NUMBER                := fnd_global.login_id;
Line: 662

   x_loading_status := 'CN_INSERTED';
Line: 686

   /* This shouldn't be here. select from sequence
   IF (cn_api.chk_miss_null_num_para
       ( p_RuleAttribute_rec.attribute_rule_id,
         cn_api.get_lkup_meaning('RULE_ATTRIBUTE_ID', 'RULESET_TYPE'),
         x_loading_status,
         x_loading_status) = FND_API.G_TRUE )
     THEN
      RAISE fnd_api.g_exc_error;
Line: 731

   SELECT object_id
     INTO l_object_id
     FROM cn_objects
     WHERE name = p_RuleAttribute_rec.object_name and
     org_id=p_RuleAttribute_rec.org_id
     AND table_id IN (-11803,-16134);
Line: 738

   SELECT Nvl(p_RuleAttribute_rec.attribute_rule_id, cn_attribute_rules_s.NEXTVAL)
     INTO l_attribute_rule_id
     FROM dual;
Line: 762

      cn_syin_attr_rules_pkg.insert_row
        (l_attribute_rule_id,
         l_object_id,
         p_RuleAttribute_rec.value_1,
         NULL,
         NULL,
         NULL,
         p_RuleAttribute_rec.not_flag,
         p_RuleAttribute_rec.rule_id,
         p_RuleAttribute_rec.ruleset_id,
         g_last_update_date,
         g_last_updated_by,
         g_creation_date,
         g_created_by,
         g_last_update_login,
	 p_RuleAttribute_rec.org_id);
Line: 780

      cn_syin_attr_rules_pkg.insert_row
        (l_attribute_rule_id,
         l_object_id,
         NULL,
         p_RuleAttribute_rec.value_1,
         p_RuleAttribute_rec.value_2,
         NULL,
         p_RuleAttribute_rec.not_flag,
         p_RuleAttribute_rec.rule_id,
         p_RuleAttribute_rec.ruleset_id,
         g_last_update_date,
         g_last_updated_by,
         g_creation_date,
         g_created_by,
         g_last_update_login,
	 p_RuleAttribute_rec.org_id);
Line: 798

      SELECT head_hierarchy_id
        INTO l_dim_hierarchy_id
        FROM cn_head_hierarchies
        --WHERE name = p_RuleAttribute_rec.value_1;
Line: 803

      SELECT hn.value_id
        INTO l_hierarchy_value
        FROM cn_hierarchy_nodes hn,
        cn_dim_hierarchies dh
        --WHERE hn.name = p_RuleAttribute_rec.value_2
        WHERE hn.value_id = p_RuleAttribute_rec.value_2
        AND hn.dim_hierarchy_id = dh.dim_hierarchy_id
        AND dh.header_dim_hierarchy_id = l_dim_hierarchy_id
	AND hn.org_id=dh.org_id
	AND hn.org_id=p_RuleAttribute_rec.org_id;
Line: 814

      cn_syin_attr_rules_pkg.insert_row
        (l_attribute_rule_id,
         l_object_id,
         l_hierarchy_value,
         NULL,
         NULL,
         l_dim_hierarchy_id,
         p_RuleAttribute_rec.not_flag,
         p_RuleAttribute_rec.rule_id,
         p_RuleAttribute_rec.ruleset_id,
         g_last_update_date,
         g_last_updated_by,
         g_creation_date,
         g_created_by,
         g_last_update_login,
	 p_RuleAttribute_rec.org_id);
Line: 927

PROCEDURE Update_RuleAttribute
  ( 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_RuleAttribute_rec           IN OUT NOCOPY  CN_RuleAttribute_PVT.RuleAttribute_rec_type,
    p_RuleAttribute_rec           IN OUT NOCOPY  CN_RuleAttribute_PVT.RuleAttribute_rec_type
    ) IS

   l_api_name                  CONSTANT VARCHAR2(30) := 'Update_RuleAttribute';
Line: 955

  G_LAST_UPDATE_DATE     DATE                  := Sysdate;
Line: 956

  G_LAST_UPDATED_BY      NUMBER                := fnd_global.user_id;
Line: 959

  G_LAST_UPDATE_LOGIN    NUMBER                := fnd_global.login_id;
Line: 962

    SELECT object_version_number
      FROM cn_attribute_rules
      WHERE attribute_rule_id = p_ruleattribute_rec.attribute_rule_id
      AND ruleset_id = p_ruleattribute_rec.ruleset_id
      AND rule_id = p_ruleattribute_rec.rule_id
      AND ORG_ID=p_RuleAttribute_rec.org_id;
Line: 972

   SAVEPOINT Update_RuleAttribute;
Line: 990

   x_loading_status := 'CN_UPDATED';
Line: 1123

   SELECT object_id
     INTO l_object_id
     FROM cn_objects
     WHERE name = p_RuleAttribute_rec.object_name AND org_id=p_RuleAttribute_rec.org_id
     AND table_id IN (-11803,-16134);
Line: 1148

      cn_syin_attr_rules_pkg.update_row
         (/*l_attribute_rule_id*/ p_old_RuleAttribute_rec.attribute_rule_id,
         p_RuleAttribute_rec.object_version_number,
         l_object_id,
         p_RuleAttribute_rec.value_1,
         NULL,
         NULL,
         NULL,
         p_RuleAttribute_rec.not_flag,
         g_last_update_date,
         g_last_updated_by,
         g_last_update_login,
	 p_RuleAttribute_rec.org_id);
Line: 1164

      cn_syin_attr_rules_pkg.update_row
        (p_old_RuleAttribute_rec.attribute_rule_id ,
         p_RuleAttribute_rec.object_version_number,
         l_object_id,
         NULL,
         p_RuleAttribute_rec.value_1,
         p_RuleAttribute_rec.value_2,
         NULL,
         p_RuleAttribute_rec.not_flag,
         g_last_update_date,
         g_last_updated_by,
         g_last_update_login,
	 p_RuleAttribute_rec.org_id);
Line: 1183

      SELECT head_hierarchy_id
        INTO l_dim_hierarchy_id
        FROM cn_head_hierarchies
      WHERE head_hierarchy_id = p_RuleAttribute_rec.value_1
      AND org_id=p_RuleAttribute_rec.org_id;
Line: 1204

      SELECT hn.value_id
        INTO l_hierarchy_value
        FROM cn_hierarchy_nodes hn,
        cn_dim_hierarchies dh
        WHERE hn.value_id = p_RuleAttribute_rec.value_2
        AND hn.dim_hierarchy_id = dh.dim_hierarchy_id
        AND dh.header_dim_hierarchy_id = l_dim_hierarchy_id
	AND hn.org_id=dh.org_id
	AND hn.org_id=p_RuleAttribute_rec.org_id;
Line: 1227

      cn_syin_attr_rules_pkg.update_row
        (p_old_RuleAttribute_rec.attribute_rule_id,
         p_RuleAttribute_rec.object_version_number,
         l_object_id,
         l_hierarchy_value,
         NULL,
         NULL,
         l_dim_hierarchy_id,
         p_RuleAttribute_rec.not_flag,
         g_last_update_date,
         g_last_updated_by,
         g_last_update_login,
	 p_RuleAttribute_rec.org_id);
Line: 1268

      ROLLBACK TO Update_RuleAttribute;
Line: 1276

      ROLLBACK TO Update_RuleAttribute;
Line: 1285

      ROLLBACK TO Update_RuleAttribute;
Line: 1342

PROCEDURE Delete_RuleAttribute
( 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_ruleset_id              IN   cn_attribute_rules.ruleset_id%TYPE,
  p_rule_id                 IN   cn_attribute_rules.rule_id%TYPE,
  p_attribute_rule_id       IN   cn_attribute_rules.attribute_rule_id%TYPE,
  p_object_version_number   IN   cn_attribute_rules.object_version_number%TYPE
) IS


   l_api_name                   CONSTANT VARCHAR2(30) := 'Delete_RuleAttribute';
Line: 1368

  G_LAST_UPDATE_DATE     DATE                  := Sysdate;
Line: 1369

  G_LAST_UPDATED_BY      NUMBER                := fnd_global.user_id;
Line: 1372

  G_LAST_UPDATE_LOGIN    NUMBER                := fnd_global.login_id;
Line: 1375

    SELECT object_version_number
      FROM cn_attribute_rules
      WHERE attribute_rule_id = p_attribute_rule_id
      AND ruleset_id = p_ruleset_id
      AND rule_id = p_rule_id;
Line: 1384

  SAVEPOINT Delete_RuleAttribute;
Line: 1408

  SELECT COUNT(1)
    INTO l_count
    FROM cn_rule_attr_expression
    WHERE ((operand1 = p_attribute_rule_id
            AND operand1_ra_rae_flag = 'RA')
           OR
           (operand2 = p_attribute_rule_id
            AND operand2_ra_rae_flag = 'RA'))
    AND rule_id = p_rule_id;
Line: 1432

    SELECT COUNT(1)
    INTO l_count
    FROM cn_attribute_rules
    WHERE attribute_rule_id = p_attribute_rule_id
    AND ruleset_id = p_ruleset_id
    AND rule_id = p_rule_id;
Line: 1439

    select org_id into l_org_id
    FROM cn_attribute_rules
    WHERE attribute_rule_id = p_attribute_rule_id
    AND ruleset_id = p_ruleset_id
    AND rule_id = p_rule_id;
Line: 1476

  cn_syin_attr_rules_pkg.delete_row(p_attribute_rule_id);
Line: 1502

                ROLLBACK TO Delete_RuleAttribute;
Line: 1510

                ROLLBACK TO Delete_RuleAttribute;
Line: 1519

                ROLLBACK TO Delete_RuleAttribute;
Line: 1541

    l_select_stmt  VARCHAR2(4000);
Line: 1542

    l_select       VARCHAR2(4000);
Line: 1548

    l_select  := l_valueset_r.table_info.value_column_name ||' column_name, ' ||
     		      NVL(l_valueset_r.table_info.id_column_name, 'null') || ' column_id, ' ||
                  NVL(l_valueset_r.table_info.meaning_column_name, 'null') || ' column_meaning';
Line: 1560

   l_select_stmt := 'Select ' || l_select || ' from ' || l_from || ' ' || l_where ;
Line: 1562

   return l_select_stmt;
Line: 1575

                 x_select       OUT NOCOPY VARCHAR2,
                 x_from         OUT NOCOPY VARCHAR2,
                 x_where        OUT NOCOPY VARCHAR2) IS

    l_valueset_r   fnd_vset.valueset_r;
Line: 1582

    l_select_stmt  VARCHAR2(4000);
Line: 1591

     SELECT value_set_id
     FROM   cn_objects
     WHERE  object_id   = p_column_id
     AND    name        = p_column_name
     AND    object_type = 'COL'
     AND org_id=p_org_id
     AND    value_set_id IS NOT NULL;
Line: 1611

           x_select  := NVL(l_valueset_r.table_info.id_column_name,
                        l_valueset_r.table_info.value_column_name)
                        ||' column_id , '
                        ||l_valueset_r.table_info.value_column_name
                        ||' column_name ';
Line: 1620

		x_select := x_select || ', ' || NVL(l_valueset_r.table_info.meaning_column_name,
			 l_valueset_r.table_info.value_column_name) || ' meaning ' ;
Line: 1629

                        x_select:= NVL(l_valueset_r.table_info.id_column_name,
                        l_valueset_r.table_info.value_column_name)
                        ||' column_id , '
                        ||l_valueset_r.table_info.value_column_name
                        ||' column_name ';
Line: 1638

		x_select := x_select || ', ' || NVL(l_valueset_r.table_info.meaning_column_name,
			 l_valueset_r.table_info.value_column_name) || ' meaning ' ;
Line: 1651

            l_select_stmt := 'Select ' || x_select ||
                             ' from ' || x_from ||
			     ' ' || x_where ;
Line: 1656

            dbms_sql.parse(l_cursor_num,l_select_stmt,2);
Line: 1660

              x_select := ' 1 column_id , 2 column_name ';
Line: 1670

      x_select := ' 1 column_id , 2 column_name ';