APPS.CN_DIM_HIERARCHIES_PVT SQL Statements

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

Line: 54

   select count(1) into l_count from cn_dimensions where name = p_name and org_id = p_org_id; --R12 MOAC change

Line: 61

   select count(1) into l_count_tl
     from cn_dimensions_all_tl T, fnd_languages L
   where name = p_name
      and org_id = p_org_id
      and T.language = L.language_code
      and L.INSTALLED_FLAG in ('I', 'B');

Line: 76

   cn_dimensions_pkg.insert_row
     (
      X_DIMENSION_ID      => x_dimension_id,
      X_DESCRIPTION       => p_description,-- Added for R12
      X_SOURCE_TABLE_ID   => p_base_table_id,
      X_NAME              => p_name,
      X_CREATION_DATE     => sysdate,
      X_CREATED_BY        => fnd_global.user_id,
      X_LAST_UPDATE_DATE  => sysdate,
      X_LAST_UPDATED_BY   => fnd_global.user_id,
      X_LAST_UPDATE_LOGIN => fnd_global.login_id,
      --R12 MOAC Changes--Start
      X_ORG_ID => p_org_id);

Line: 91

   CN_DIHY_TWO_API_PKG.Insert_Dimension
     (x_dimension_id,
      p_name,
      p_base_table_id,
      p_primary_key_id,
      p_user_column_id,
      --R12 MOAC Changes--Start
      p_org_id);

Line: 144

PROCEDURE Update_Hierarchy_Type
  (p_api_version                IN      NUMBER,     -- required
   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,
   p_dimension_id               IN      CN_DIMENSIONS.DIMENSION_ID%TYPE,
   p_name                       IN      CN_DIMENSIONS.NAME%TYPE,
   p_object_version_number      IN  OUT NOCOPY  CN_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE,
   --R12 MOAC Changes--Start
   p_org_id			IN		 CN_DIMENSIONS.ORG_ID%TYPE,
   --R12 MOAC Changes--End
   p_description                IN      CN_DIMENSIONS.DESCRIPTION%TYPE, -- Added for R12
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

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

Line: 165

   select source_table_id, object_version_number from cn_dimensions
    where dimension_id = p_dimension_id
		and org_id = p_org_id;         --R12 MOAC changes

Line: 172

   SAVEPOINT   Update_Hierarchy_Type;

Line: 196

      fnd_message.set_name('CN', 'CN_RECORD_DELETED');

Line: 203

   select count(1) into l_count from cn_dimensions
    where name = p_name and dimension_id <> p_dimension_id and org_id = p_org_id;  --R12 MOAC changes

Line: 223

   cn_dimensions_pkg.update_row
     (X_DIMENSION_ID      => p_dimension_id,
      X_DESCRIPTION       => p_description, --Added for R12
      X_SOURCE_TABLE_ID   => tlinfo.source_table_id,
      X_NAME              => p_name,
      X_LAST_UPDATE_DATE  => sysdate,
      X_LAST_UPDATED_BY   => fnd_global.user_id,
      X_LAST_UPDATE_LOGIN => fnd_global.login_id,
      --R12 MOAC Changes--Star
      X_ORG_ID => p_org_id,
      X_OBJECT_VERSION_NUMBER => p_object_version_number);

Line: 251

      ROLLBACK TO Update_Hierarchy_Type;

Line: 258

      ROLLBACK TO Update_Hierarchy_Type;

Line: 265

      ROLLBACK TO Update_Hierarchy_Type;

Line: 278

END Update_Hierarchy_Type;

Line: 281

PROCEDURE Delete_Hierarchy_Type
  (p_api_version                IN      NUMBER,     -- required
   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,
   p_dimension_id               IN      CN_DIMENSIONS.DIMENSION_ID%TYPE,
   --R12 MOAC Changes--Start
   p_org_id			IN		 CN_DIMENSIONS.ORG_ID%TYPE,
   --R12 MOAC Changes--End
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

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

Line: 299

   SAVEPOINT   Delete_Hierarchy_Type;

Line: 318

   cn_dimensions_pkg.delete_row
     (X_DIMENSION_ID => p_dimension_id,
     --R12 MOAC Changes--Start
	X_ORG_ID => p_org_id);

Line: 337

      ROLLBACK TO Delete_Hierarchy_Type;

Line: 344

      ROLLBACK TO Delete_Hierarchy_Type;

Line: 351

      ROLLBACK TO Delete_Hierarchy_Type;

Line: 364

END Delete_Hierarchy_Type;

Line: 409

   select count(1) into l_count from cn_head_hierarchies
    where name = p_name and dimension_id = p_dimension_id
    --R12 MOAC Changes--Start
    and org_id = p_org_id ;

Line: 423

   cn_head_hierarchies_all_pkg.insert_row
     (X_ROWID               => l_rowid,
      X_HEAD_HIERARCHY_ID   => x_head_hierarchy_id,
      X_DIMENSION_ID        => p_dimension_id,
      X_DESCRIPTION         => NULL, -- description not used
      X_NAME                => p_name,
      X_CREATION_DATE       => sysdate,
      X_CREATED_BY          => fnd_global.user_id,
      X_LAST_UPDATE_DATE    => sysdate,
      X_LAST_UPDATED_BY     => fnd_global.user_id,
      X_LAST_UPDATE_LOGIN   => fnd_global.login_id,
      --R12 MOAC Changes--Start
      X_ORG_ID => p_org_id);

Line: 481

PROCEDURE Update_Head_Hierarchy
  (p_api_version                IN      NUMBER,     -- required
   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,
   p_head_hierarchy_id          IN      CN_HEAD_HIERARCHIES.HEAD_HIERARCHY_ID%TYPE,
   p_name                       IN      CN_HEAD_HIERARCHIES.NAME%TYPE,
   p_object_version_number      IN   OUT NOCOPY   CN_HEAD_HIERARCHIES.OBJECT_VERSION_NUMBER%TYPE,
   --R12 MOAC Changes--Start
   p_org_id			IN		 CN_HEAD_HIERARCHIES.ORG_ID%TYPE,
   --R12 MOAC Changes--End
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

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

Line: 501

   select object_version_number, description, dimension_id
     from cn_head_hierarchies
    where head_hierarchy_id = p_head_hierarchy_id
    --R12 MOAC Changes--Start
    and org_id = p_org_id;

Line: 511

   SAVEPOINT   Update_Head_Hierarchy;

Line: 537

      fnd_message.set_name('CN', 'CN_RECORD_DELETED');

Line: 551

   select count(1) into l_count from cn_head_hierarchies
    where name = p_name and dimension_id = tlinfo.dimension_id
    --R12 MOAC Changes--Start
	and org_id = p_org_id
	--R12 MOAC Changes--End
      and head_hierarchy_id <> p_head_hierarchy_id;

Line: 563

   cn_head_hierarchies_all_pkg.update_row
     (X_HEAD_HIERARCHY_ID   => p_head_hierarchy_id,
      X_DIMENSION_ID        => tlinfo.dimension_id, -- leave unchanged
      X_DESCRIPTION         => tlinfo.description,  -- leave unchanged
      X_NAME                => p_name,
      X_LAST_UPDATE_DATE    => sysdate,
      X_LAST_UPDATED_BY     => fnd_global.user_id,
      X_LAST_UPDATE_LOGIN   => fnd_global.login_id,
      --R12 MOAC Changes--Start
      X_ORG_ID => p_org_id,
      X_OBJECT_VERSION_NUMBER => p_object_version_number);

Line: 590

      ROLLBACK TO Update_Head_Hierarchy;

Line: 597

      ROLLBACK TO Update_Head_Hierarchy;

Line: 604

      ROLLBACK TO Update_Head_Hierarchy;

Line: 617

END Update_Head_Hierarchy;

Line: 620

PROCEDURE Delete_Head_Hierarchy
  (p_api_version                IN      NUMBER,     -- required
   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,
   p_head_hierarchy_id          IN      CN_HEAD_HIERARCHIES.HEAD_HIERARCHY_ID%TYPE,
   --R12 MOAC Changes--Start
   p_org_id			IN		 CN_HEAD_HIERARCHIES.ORG_ID%TYPE,
 --R12 MOAC Changes--End
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

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

Line: 638

   SAVEPOINT   Delete_Head_Hierarchy;

Line: 657

   cn_head_hierarchies_all_pkg.delete_row (p_head_hierarchy_id,
   --R12 MOAC Changes--Start
    p_org_id);

Line: 674

      ROLLBACK TO Delete_Head_Hierarchy;

Line: 681

      ROLLBACK TO Delete_Head_Hierarchy;

Line: 688

      ROLLBACK TO Delete_Head_Hierarchy;

Line: 701

END Delete_Head_Hierarchy;

Line: 710

   select count(*)
   into l_count
   from cn_repositories
   where rev_class_hierarchy_id = p_head_hierarchy_id
   --R12 MOAC Changes--Start
   and org_id = p_org_id;

Line: 767

   select count(1) into l_count
     from cn_dim_hierarchies
    where header_dim_hierarchy_id = p_head_hierarchy_id
    --R12 MOAC Changes--Start
    and org_id = p_org_id
    --R12 MOAC Changes--End
     and (                (start_date <= p_start_date  and
	    nvl(end_date,p_start_date) >= p_start_date) OR
			   (start_date >= p_start_date  and
			    start_date <= nvl(p_end_date, start_date)));

Line: 792

   cn_dim_hierarchies_pkg.insert_row
     (x_header_dim_hierarchy_id   => p_head_hierarchy_id,
      x_start_date                => p_start_date,
      x_end_date                  => p_end_date,
      x_root_node                 => x_root_node,
      x_dim_hierarchy_id          => x_dim_hierarchy_id,
      --R12 MOAC Changes--Start
      x_org_id			=> p_org_id);

Line: 860

PROCEDURE Update_Dim_Hierarchy
  (p_api_version                IN      NUMBER,     -- required
   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,
   p_dim_hierarchy_id           IN      CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE,
   p_start_date                 IN      CN_DIM_HIERARCHIES.START_DATE%TYPE,
   p_end_date                   IN      CN_DIM_HIERARCHIES.END_DATE%TYPE,
   p_object_version_number      IN OUT NOCOPY      CN_DIM_HIERARCHIES.OBJECT_VERSION_NUMBER%TYPE,
   --R12 MOAC Changes--Start
   p_org_id			IN		 CN_DIM_HIERARCHIES.ORG_ID%TYPE,
 --R12 MOAC Changes--End
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

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

Line: 885

   SAVEPOINT   Update_Dim_Hierarchy;

Line: 907

   select header_dim_hierarchy_id, root_node
     into l_head_hierarchy_id, l_root_node
     from cn_dim_hierarchies
    where dim_hierarchy_id = p_dim_hierarchy_id
    --R12 MOAC Changes--Start
    and org_id = p_org_id;

Line: 922

   select count(1) into l_count
     from cn_dim_hierarchies
    where header_dim_hierarchy_id = l_head_hierarchy_id
    --R12 MOAC Changes--Start
	and org_id = p_org_id
	--R12 MOAC Changes--End
      and dim_hierarchy_id <> p_dim_hierarchy_id
      and (                (start_date <= p_start_date  and
	    nvl(end_date,p_start_date) >= p_start_date) OR
			   (start_date >= p_start_date  and
			    start_date <= nvl(p_end_date, start_date)));

Line: 942

    select start_date, end_date
    into l_old_start_date, l_old_end_date
    from cn_dim_hierarchies
    where dim_hierarchy_id = p_dim_hierarchy_id
    --R12 MOAC Changes--Start
    and org_id = p_org_id;

Line: 964

   cn_dim_hierarchies_pkg.update_row
     (x_dim_hierarchy_id          => p_dim_hierarchy_id,
      x_header_dim_hierarchy_id   => l_head_hierarchy_id,
      x_start_date                => p_start_date,
      x_end_date                  => p_end_date,
      x_root_node                 => l_root_node,
      x_object_version_number     => p_object_version_number,
      --R12 MOAC Changes--Start
      x_org_id				=> p_org_id);

Line: 991

      ROLLBACK TO Update_Dim_Hierarchy;

Line: 998

      ROLLBACK TO Update_Dim_Hierarchy;

Line: 1005

      ROLLBACK TO Update_Dim_Hierarchy;

Line: 1018

END Update_Dim_Hierarchy;

Line: 1021

PROCEDURE Delete_Dim_Hierarchy
  (p_api_version                IN      NUMBER,     -- required
   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,
   p_dim_hierarchy_id           IN      CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE,
   --R12 MOAC Changes--Start
   p_org_id			IN		 CN_DIM_HIERARCHIES.ORG_ID%TYPE,
 --R12 MOAC Changes--End
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

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

Line: 1042

   SAVEPOINT   Delete_Dim_Hierarchy;

Line: 1062

    select header_dim_hierarchy_id, start_date, end_date
    into l_head_hierarchy_id, l_start_date, l_end_date
    from cn_dim_hierarchies
    where dim_hierarchy_id = p_dim_hierarchy_id
    --R12 MOAC Changes--Start
    and org_id = p_org_id;

Line: 1086

   cn_dim_hierarchies_pkg.delete_row (p_dim_hierarchy_id);--,p_org_id);

Line: 1101

      ROLLBACK TO Delete_Dim_Hierarchy;

Line: 1108

      ROLLBACK TO Delete_Dim_Hierarchy;

Line: 1115

      ROLLBACK TO Delete_Dim_Hierarchy;

Line: 1128

END Delete_Dim_Hierarchy;

Line: 1157

      select h.start_date, h.end_date, r.name
	from cn_quota_rules r1, cn_quota_rules r2, cn_dim_explosion d,
	     cn_dim_hierarchies h, cn_quotas q, cn_revenue_classes r
	where r1.revenue_class_id = p_external_id
	  and r2.revenue_class_id = d.ancestor_external_id
	  AND r.revenue_class_id  = d.ancestor_external_id
	  and d.value_id = p_parent_value_id
	  and d.dim_hierarchy_id = h.dim_hierarchy_id
	  and h.dim_hierarchy_id = p_dim_hierarchy_id
	  and r1.quota_id = r2.quota_id
	  and r1.quota_id = q.quota_id
	  --R12 MOAC Changes--Start
	  and r1.org_id = r2.org_id
	  and r2.org_id = d.org_id
	  and d.org_id = h.org_id
	  and h.org_id = q.org_id
	  and q.org_id = r1.org_id
	  and r1.org_id =  p_org_id
	  and r.org_id = r1.org_id
	  --R12 MOAC Changes--End
	  and greatest(q.start_date, h.start_date) <=
	      least(nvl(q.end_date,l_max_date), nvl(h.end_date,l_max_date));

Line: 1203

      SELECT header_dim_hierarchy_id INTO l_head_hier_id
	FROM cn_dim_hierarchies
       WHERE dim_hierarchy_id = p_dim_hierarchy_id
       --R12 MOAC Changes--Start
       and org_id = p_org_id;

Line: 1226

      CN_DIHY_TWO_API_PKG.Insert_Edge
	(X_name                => p_name,
	 X_dim_hierarchy_id    => p_dim_hierarchy_id,
	 X_value_id            => x_value_id,
	 X_parent_value_id     => p_parent_value_id,
	 X_external_id	       => p_external_id,
	 X_hierarchy_api_id    => NULL, -- not used
	 --R12 MOAC Changes--Start
	 x_org_id			=> p_org_id);

Line: 1286

PROCEDURE Delete_Edge
  (p_api_version                IN      NUMBER,     -- required
   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,
   p_dim_hierarchy_id           IN      CN_HIERARCHY_EDGES.DIM_HIERARCHY_ID%TYPE,
   p_value_id                   IN      CN_HIERARCHY_EDGES.VALUE_ID%TYPE,
   p_parent_value_id            IN      CN_HIERARCHY_EDGES.PARENT_VALUE_ID%TYPE,
   --R12 MOAC Changes--Start
   p_org_id			IN		 CN_HIERARCHY_EDGES.ORG_ID%TYPE,
   --R12 MOAC Changes--End
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

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

Line: 1308

   SAVEPOINT   Delete_Edge;

Line: 1330

      select external_id into l_ext_id
	from cn_hierarchy_nodes
       where value_id = p_value_id
       --R12 MOAC Changes--Start
       and org_id = p_org_id;

Line: 1338

	 FND_MESSAGE.SET_NAME('CN', 'HIER_NO_DELETE_ROOT');

Line: 1345

   select count(1) into l_count
     from cn_hierarchy_edges
    where value_id = p_value_id
      and nvl(parent_value_id, -99) = nvl(p_parent_value_id, -99)
      and dim_hierarchy_id = p_dim_hierarchy_id
      --R12 MOAC Changes--Start
      and org_id = p_org_id;

Line: 1355

      fnd_message.set_name('CN', 'CN_RECORD_DELETED');

Line: 1360

   CN_DIHY_TWO_API_PKG.Cascade_Delete
     (X_value_id              => p_value_id,
      X_parent_value_id       => p_parent_value_id,
      X_dim_hierarchy_id      => p_dim_hierarchy_id,
      X_org_id			=>   p_org_id);

Line: 1379

      ROLLBACK TO Delete_Edge;

Line: 1386

      ROLLBACK TO Delete_Edge;

Line: 1393

      ROLLBACK TO Delete_Edge;

Line: 1406

END Delete_Edge;

Line: 1442

      select D.NAME       HIERARCHY_TYPE,
             O2.NAME      BASE_TABLE_NAME,
             O1.NAME      PRIMARY_KEY,
             O3.NAME      HIERARCHY_VALUE,
             H.NAME       HIERARCHY_NAME,
             M.START_DATE START_DATE,
             M.END_DATE   END_DATE,
             m.dim_hierarchy_id
	FROM cn_dimensions d, cn_objects o1, cn_objects o2, cn_objects o3,
	     cn_head_hierarchies h, cn_dim_hierarchies m
       WHERE o1.dimension_id = d.dimension_id
	 AND o1.table_id = d.source_table_id
	 AND o1.object_type = 'COL'
	 AND o1.primary_key = 'Y'
	 AND o2.object_id = o1.table_id
	 AND o3.table_id = o1.table_id
	 AND o3.object_type = 'COL'
	 AND o3.user_column_name = 'Y'
	 AND d.dimension_id = h.dimension_id
	 AND h.head_hierarchy_id = m.header_dim_hierarchy_id
	 AND	d.org_id = p_org_id
	 AND	o1.org_id = p_org_id
	 AND	o2.org_id = p_org_id
	 AND 	o3.org_id = p_org_id
	 AND 	h.org_id = p_org_id
	 AND	m.org_id = p_org_id
       ORDER BY 1, 5, 6;

Line: 1471

      select decode(child.external_id, null, 'Y', 'N') DEFAULT_NODE_FLAG,
             parent.name                               PARENT_NODE_NAME,
             child.name                                NODE_NAME,
             e.depth                                   LEVEL_NUM
	from cn_hierarchy_nodes child, cn_hierarchy_nodes parent,
	(select value_id, parent_value_id, level depth, dim_hierarchy_id
	   from cn_hierarchy_edges
 	  start with (parent_value_id is null and
		      dim_hierarchy_id = l_dim_hierarchy_id)
	connect by parent_value_id = prior value_id
	    and dim_hierarchy_id = l_dim_hierarchy_id) e
       where child.value_id = e.value_id
         and child.dim_hierarchy_id = e.dim_hierarchy_id
         and parent.value_id(+) = e.parent_value_id
	 and parent.dim_hierarchy_id(+) = e.dim_hierarchy_id;

Line: 1491

   SELECT h.name, h.import_type_code, t.view_name
     INTO l_name, l_type, l_view_name
     FROM cn_imp_headers h, cn_import_types t
    WHERE h.imp_header_id = p_imp_header_id
      AND t.import_type_code = h.import_type_code;

Line: 1551

   cn_import_client_pvt.Insert_Data
     (p_api_version                 => 1.0,
      p_imp_header_id               => p_imp_header_id,
      p_import_type_code            => l_type,
      p_table_name                  => l_view_name,
      p_col_names                   => l_col_names,
      p_data                        => l_data,
      p_row_count                   => l_rowcount,
      x_return_status               => l_return_status,
      x_msg_count                   => l_msg_count,
      x_msg_data                    => l_msg_data);

Line: 1564

      CN_IMPORT_PVT.update_imp_headers
	(p_imp_header_id => p_imp_header_id,
	 p_status_code => 'FAIL',
	 p_failed_row => l_rowcount);

Line: 1588

      CN_IMPORT_PVT.update_imp_headers
	(p_imp_header_id => p_imp_header_id,
	 p_status_code => 'COMPLETE',
	 p_processed_row => l_rowcount,
	 p_staged_row => l_rowcount,
	 p_failed_row => 0);

Line: 1596

      UPDATE cn_imp_lines
	SET status_code = 'COMPLETE'
	WHERE imp_header_id = p_imp_header_id
	;