[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE cn_hierarchy_imp_v
SET status_code = p_status_code, error_code = p_error_code,
error_msg = p_error_msg
WHERE
(start_date =
Decode(p_hier_record.start_date,FND_API.G_MISS_CHAR,start_date,
p_hier_record.start_date)
)
AND
(Nvl(end_date,FND_API.g_miss_char) =
Decode(p_hier_record.end_date,
FND_API.G_MISS_CHAR,Nvl(end_date,FND_API.g_miss_char) ,
NULL,FND_API.g_miss_char,
p_hier_record.end_date)
)
AND
(hierarchy_name =
Decode(p_hier_record.hierarchy_name,FND_API.G_MISS_CHAR,hierarchy_name,
p_hier_record.hierarchy_name)
)
AND
(Upper(hierarchy_value) =
Decode(p_hier_record.hierarchy_value,FND_API.G_MISS_CHAR,
Upper(hierarchy_value),p_hier_record.hierarchy_value)
)
AND
(Upper(primary_key) =
Decode(p_hier_record.primary_key,FND_API.G_MISS_CHAR,Upper(primary_key),
p_hier_record.primary_key)
)
AND
(Upper(base_table_name) =
Decode(p_hier_record.base_table_name,FND_API.G_MISS_CHAR,
Upper(base_table_name),p_hier_record.base_table_name)
)
AND
(hierarchy_type=
Decode(p_hier_record.hierarchy_type,FND_API.G_MISS_CHAR,hierarchy_type,
p_hier_record.hierarchy_type)
)
AND
(imp_line_id =
Decode(p_hier_record.imp_line_id,FND_API.G_MISS_NUM,imp_line_id,
p_hier_record.imp_line_id)
)
AND (imp_header_id = p_hier_record.imp_header_id)
AND status_code = 'STAGE'
;
CN_IMPORT_PVT.update_imp_headers
(p_imp_header_id => p_hier_record.imp_header_id,
p_status_code => 'IMPORT_FAIL',
p_failed_row => x_failed_row,
p_processed_row => x_processed_row);
SELECT DISTINCT Upper(BASE_TABLE_NAME) BASE_TABLE_NAME,
Upper(primary_key) PRIMARY_KEY,
Upper(hierarchy_value) HIERARCHY_VALUE
INTO p_hier_record.base_table_name,p_hier_record.primary_key,
p_hier_record.hierarchy_value
FROM cn_hierarchy_imp_v
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND hierarchy_type = p_hier_record.hierarchy_type
;
SELECT table_id INTO x_base_table_id
FROM cn_obj_tables_v WHERE name = p_hier_record.base_table_name and org_id=p_org_id
;
SELECT column_id INTO x_primary_key_id
FROM cn_obj_columns_v
WHERE name = p_hier_record.primary_key AND table_id = x_base_table_id and org_id=p_org_id
;
SELECT column_id INTO x_hier_value_id
FROM cn_obj_columns_v
WHERE name = p_hier_record.hierarchy_value
AND table_id = x_base_table_id and org_id=p_org_id
;
SELECT dimension_id INTO x_dimension_id FROM cn_dimensions_vl
WHERE name = p_hier_record.hierarchy_type and org_id=p_org_id;
SELECT 1 INTO l_dummy FROM cn_base_tables_v
WHERE table_id = x_base_table_id
AND name = p_hier_record.base_table_name and org_id=p_org_id
;
SELECT 1 INTO l_dummy FROM cn_obj_columns_v
WHERE table_id = x_base_table_id
AND dimension_id = x_dimension_id
AND column_id = x_primary_key_id
AND primary_key = 'Y' and org_id=p_org_id
;
SELECT 1 INTO l_dummy FROM cn_obj_columns_v
WHERE table_id = x_base_table_id
AND column_id = x_hier_value_id
AND user_column_name = 'Y' and org_id=p_org_id
;
SELECT head_hierarchy_id INTO x_head_hierarchy_id
FROM cn_head_hierarchies
WHERE name = p_hier_record.hierarchy_name
AND dimension_id = p_dimension_id
AND org_id = p_org_id
;
SELECT dim_hierarchy_id INTO x_dim_hierarchy_id
FROM cn_dim_hierarchies
WHERE header_dim_hierarchy_id = p_head_hierarchy_id
AND org_id = p_org_id
AND start_date = To_date(p_hier_record.start_date, 'DD/MM/YYYY')
AND Nvl(end_date,FND_API.g_miss_date) =
Nvl(To_date(p_hier_record.end_date, 'DD/MM/YYYY'),FND_API.g_miss_date)
;
SELECT
imp_line_id,
imp_header_id,
status_code,
error_code,
record_num,
trim(parent_node_name) PARENT_NODE_NAME,
trim(default_node_flag) DEFAULT_NODE_FLAG,
trim(node_name) NODE_NAME
FROM CN_HIERARCHY_IMP_V
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND hierarchy_type = l_hier_type
AND BASE_TABLE_NAME = l_base_tbl
AND hierarchy_name = l_hier_name
AND start_date = l_start_date
AND Nvl(end_date,FND_API.g_miss_char) =
Nvl(l_end_date,FND_API.g_miss_char)
ORDER BY level_num
;
SELECT value_id INTO l_parent_value_id
FROM cn_hierarchy_nodes
WHERE dim_hierarchy_id = p_dim_hierarchy_id
AND org_id = p_org_id
AND name = l_hierarchy_node_csr.parent_node_name;
SELECT value_id INTO l_parent_value_id
FROM cn_hierarchy_nodes
WHERE dim_hierarchy_id = p_dim_hierarchy_id
AND org_id = p_org_id
AND external_id IS NULL;
('---- SELECT ' || p_hier_record.primary_key ||
' FROM ' || p_hier_record.base_table_name ||
' WHERE ' || p_hier_record.hierarchy_value ||
' = ''' || l_hierarchy_node_csr.node_name ||'''');
'SELECT ' || p_hier_record.primary_key ||
' FROM ' || p_hier_record.base_table_name ||
' WHERE ' || p_hier_record.hierarchy_value ||
' = :1' using l_hierarchy_node_csr.node_name;
SELECT value_id INTO l_value_id
FROM cn_hierarchy_nodes
WHERE dim_hierarchy_id = p_dim_hierarchy_id
AND org_id = p_org_id
AND name = l_hierarchy_node_csr.node_name
AND external_id = l_external_id;
SELECT 1 INTO l_dummy
FROM cn_hierarchy_edges
WHERE dim_hierarchy_id = p_dim_hierarchy_id
AND value_id = l_value_id and org_id=p_org_id
AND ((l_parent_value_id IS NULL AND parent_value_id IS NULL) OR
(l_parent_value_id IS NOT NULL AND
parent_value_id IS NOT NULL AND
parent_value_id = l_parent_value_id))
;
CN_IMPORT_PVT.update_imp_lines
(p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
p_status_code => 'COMPLETE',
p_error_code => '');
CN_IMPORT_PVT.update_imp_lines
(p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
p_status_code => 'FAIL',
p_error_code => l_error_code,
p_error_msg => l_message);
CN_IMPORT_PVT.update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'IMPORT_FAIL',
p_failed_row => x_failed_row,
p_processed_row => x_processed_row);
CN_IMPORT_PVT.update_imp_lines
(p_imp_line_id => l_hierarchy_node_csr.imp_line_id,
p_status_code => 'FAIL',
p_error_code => l_error_code,
p_error_msg => l_message);
CN_IMPORT_PVT.update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'IMPORT_FAIL',
p_failed_row => x_failed_row,
p_processed_row => x_processed_row);
SELECT imp_line_id,record_num FROM cn_hierarchy_imp_v
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND (hierarchy_type IS NULL OR BASE_TABLE_NAME IS NULL OR
primary_key IS NULL OR hierarchy_value IS NULL OR
hierarchy_name IS NULL OR start_date IS NULL OR
default_node_flag IS NULL OR node_name IS NULL)
;
SELECT imp_line_id,record_num FROM cn_hierarchy_imp_v
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND default_node_flag <> 'Y'
AND default_node_flag <> 'N'
;
SELECT DISTINCT
Upper(base_table_name) BASE_TABLE_NAME
FROM CN_HIERARCHY_IMP_V
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
;
SELECT DISTINCT
trim(hierarchy_type) HIERARCHY_TYPE
FROM CN_HIERARCHY_IMP_V
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
;
SELECT DISTINCT
trim(hierarchy_name) HIERARCHY_NAME,
trim(start_date) START_DATE,
trim(end_date) end_date
FROM CN_HIERARCHY_IMP_V
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND hierarchy_type = l_hier_type
;
SELECT DISTINCT
trim(start_date) START_DATE,
trim(end_date) end_date
FROM CN_HIERARCHY_IMP_V
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND hierarchy_type = l_hier_type
AND hierarchy_name = l_head_hier
;
SELECT name, status_code,server_flag,imp_map_id, source_column_num,
import_type_code
INTO l_imp_header.name ,l_imp_header.status_code ,
l_imp_header.server_flag, l_imp_header.imp_map_id,
l_imp_header.source_column_num,l_imp_header.import_type_code
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
CN_IMPORT_PVT.update_imp_lines
(p_imp_line_id => l_miss_required_csr.imp_line_id,
p_status_code => 'FAIL',
p_error_code => l_error_code,
p_error_msg => l_message);
CN_IMPORT_PVT.update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'IMPORT_FAIL',
p_failed_row => l_failed_row);
CN_IMPORT_PVT.update_imp_lines
(p_imp_line_id => l_err_dflt_node_csr.imp_line_id,
p_status_code => 'FAIL',
p_error_code => l_error_code,
p_error_msg => l_message);
CN_IMPORT_PVT.update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'IMPORT_FAIL',
p_failed_row => l_failed_row);
SELECT DISTINCT trim(hierarchy_type) hierarchy_type
INTO l_tmp
FROM CN_HIERARCHY_IMP_V
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND BASE_TABLE_NAME = l_base_table_csr.BASE_TABLE_NAME
;
SELECT trim(node_name),trim(parent_node_name)
INTO l_def_base_name,l_parent_node_name
FROM cn_hierarchy_imp_v
WHERE imp_header_id = p_imp_header_id
AND status_code = l_stage_status
AND hierarchy_type = l_hier_record.hierarchy_type
AND hierarchy_name = l_hier_record.hierarchy_name
AND start_date = l_hier_record.start_date
AND Nvl(end_date,FND_API.g_miss_char) =
Nvl(l_hier_record.end_date,FND_API.g_miss_char)
AND default_node_flag = 'Y'
;
SELECT name INTO l_def_base_name
FROM cn_hierarchy_nodes
WHERE dim_hierarchy_id = l_dim_hierarchy_id and org_id=p_org_id
AND external_id IS NULL ;
CN_IMPORT_PVT.update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'COMPLETE',
p_processed_row => l_processed_row,
p_failed_row => l_failed_row);