[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
gs_dim_id_tab.DELETE;
gs_table_row_tab.DELETE;
gs_hier_dim_id_tab.DELETE;
gs_hier_obj_id_tab.DELETE;
gs_hier_obj_def_id_tab.DELETE;
DELETE fem_ld_dim_requests_gt;
DELETE fem_ld_hier_requests_gt;
SELECT fdb.dimension_id,
fdb.dimension_varchar_label
BULK COLLECT INTO gs_dim_id_tab,
gs_dim_label_tab
FROM fem_dim_load_dim_params fdldp,
fem_dimensions_b fdb
WHERE loader_obj_def_id = p_obj_def_id
AND fdb.dimension_id = fdldp.dimension_id;
SELECT ROWNUM,
fdb.dimension_id,
fdb.dimension_varchar_label,
hier_obj_id,
hier_obj_def_id
BULK COLLECT INTO gs_table_row_tab,
gs_hier_dim_id_tab,
gs_hier_dim_label_tab,
gs_hier_obj_id_tab,
gs_hier_obj_def_id_tab
FROM fem_dim_load_hier_params fdlhp,
fem_dimensions_b fdb
WHERE loader_obj_def_id = p_obj_def_id
AND fdb.dimension_id = fdlhp.dimension_id;
FOR dim_rec IN (SELECT request_id
FROM fem_ld_dim_requests_gt
WHERE request_id > 0)
LOOP
IF fnd_concurrent.wait_for_request(request_id=> dim_rec.request_id,
interval => c_interval,
max_wait => c_max_wait_time,
phase => l_phase,
status => l_status,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
message => l_message)
THEN
IF l_dev_phase || '.' || l_dev_status IN ('COMPLETE.NORMAL','COMPLETE.WARNING') THEN
UPDATE fem_ld_dim_requests_gt
SET status = 'Y'
WHERE request_id = dim_rec.request_id;
UPDATE fem_ld_dim_requests_gt
SET status = 'N'
WHERE request_id = dim_rec.request_id;
FOR hier_rec IN (SELECT DISTINCT request_id
FROM fem_ld_dim_requests_gt
WHERE request_id > 0)
LOOP
IF fnd_concurrent.wait_for_request(request_id=> hier_rec.request_id,
interval => c_interval,
max_wait => c_max_wait_time,
phase => l_phase,
status => l_status,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
message => l_message)
THEN
IF l_dev_phase || '.' || l_dev_status IN ('COMPLETE.NORMAL','COMPLETE.WARNING') THEN
UPDATE fem_ld_hier_requests_gt
SET status = 'Y'
WHERE request_id = hier_rec.request_id;
UPDATE fem_ld_hier_requests_gt
SET status = 'N'
WHERE request_id = hier_rec.request_id;
FOR dim_rec IN (SELECT dimension_id,
dim_intf_table_name
FROM fem_ld_dim_requests_gt )
LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT 1 FROM ' || dim_rec.dim_intf_table_name || ' WHERE ROWNUM = 1' INTO l_dummy;
UPDATE fem_ld_dim_requests_gt
SET request_id = l_request_id
WHERE dimension_id = dim_rec.dimension_id;
FOR hier_rec IN (SELECT DISTINCT
dimension_id,
dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name
FROM fem_ld_hier_requests_gt )
LOOP
fem_loader_eng_util_pkg.get_hier_loader_exec_mode(c_api_version,
c_false,
c_false,
c_true,
l_return_status,
l_msg_count,
l_msg_data,
hier_rec.dimension_id,
hier_rec.hierarchy_object_name,
l_hier_load_mode);
UPDATE fem_ld_hier_requests_gt
SET request_id = l_request_id
WHERE dimension_id = hier_rec.dimension_id
AND dimension_varchar_label = hier_rec.dimension_varchar_label
AND hierarchy_object_name = hier_rec.hierarchy_object_name
AND hier_obj_def_display_name = hier_rec.hier_obj_def_display_name;
INSERT INTO fem_ld_dim_requests_gt(dimension_id,
dimension_varchar_label,
dim_intf_table_name,
request_id,
status)
SELECT gs_dim_id_tab(i),
gs_dim_label_tab(i),
intf_member_b_table_name,
TO_NUMBER(NULL),
'N'
FROM fem_xdim_dimensions fxd
WHERE fxd.dimension_id = gs_dim_id_tab(i)
AND intf_member_b_table_name IS NOT NULL;
UPDATE fem_ld_dim_requests_gt
SET request_id = -10000;
SELECT object_definition_id
INTO g_hier_object_def_id
FROM fem_object_definition_vl d
WHERE d.object_id in (SELECT o.object_id
FROM fem_object_catalog_vl o
WHERE o.object_type_code = 'HIERARCHY_LOADER'
AND o.folder_id in (SELECT f.folder_id
FROM fem_user_folders f
WHERE f.user_id = fnd_global.user_id)
)
AND d.old_approved_copy_flag = 'N'
AND d.approval_status_code NOT IN ('SUBMIT_DELETE','SUBMIT_APPROVAL');
INSERT INTO fem_ld_hier_requests_gt(dimension_id,
dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name,
request_id,
table_row)
SELECT gs_hier_dim_id_tab(i),
gs_hier_dim_label_tab(i),
fht.hierarchy_object_name,
fht.hier_obj_def_display_name,
TO_NUMBER(NULL),
gs_table_row_tab(i)
FROM fem_hierarchies_t fht
WHERE fht.dimension_varchar_label = gs_hier_dim_label_tab(i)
AND gs_hier_obj_def_id_tab(i) = -1
AND gs_hier_obj_id_tab(i) = -1;
INSERT INTO fem_ld_hier_requests_gt(dimension_id,
dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name,
request_id,
table_row)
SELECT gs_hier_dim_id_tab(i),
gs_hier_dim_label_tab(i),
fht.hierarchy_object_name,
fht.hier_obj_def_display_name,
TO_NUMBER(NULL),
gs_table_row_tab(i)
FROM fem_hierarchies_t fht,
fem_object_catalog_vl focb,
fem_object_definition_vl fodb
WHERE fht.dimension_varchar_label = gs_hier_dim_label_tab(i)
AND fht.hierarchy_object_name = focb.object_name
AND focb.object_id = gs_hier_obj_id_tab(i)
AND fht.hier_obj_def_display_name = fodb.display_name
AND focb.object_id = fodb.object_id
AND fht.language = USERENV('LANG')
AND focb.object_type_code = 'HIERARCHY'
AND gs_hier_obj_def_id_tab(i) <> -1
AND gs_hier_obj_id_tab(i) = -1;
INSERT INTO fem_ld_hier_requests_gt(dimension_id,
dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name,
request_id,
table_row)
SELECT gs_hier_dim_id_tab(i),
gs_hier_dim_label_tab(i),
fht.hierarchy_object_name,
fht.hier_obj_def_display_name,
TO_NUMBER(NULL),
gs_table_row_tab(i)
FROM fem_hierarchies_t fht,
fem_hierarchies fh,
fem_object_catalog_vl focb,
fem_object_definition_vl fodb
WHERE fht.dimension_varchar_label = gs_hier_dim_label_tab(i)
AND fht.hierarchy_object_name = focb.object_name
AND focb.object_id = fh.hierarchy_obj_id
AND focb.object_id = gs_hier_obj_id_tab(i)
AND fht.hier_obj_def_display_name = fodb.display_name
AND focb.object_id = fodb.object_id
AND fodb.object_definition_id = gs_hier_obj_def_id_tab(i)
AND fht.language = USERENV('LANG')
AND gs_hier_obj_def_id_tab(i) <> -1
AND gs_hier_obj_id_tab(i) <> -1
AND focb.object_type_code = 'HIERARCHY';
SELECT COUNT(1)
INTO l_hier_count
FROM fem_ld_hier_requests_gt
WHERE ROWNUM = 1;
FOR dim_rec IN (SELECT dimension_id,
dim_intf_table_name,
dimension_varchar_label
FROM fem_ld_dim_requests_gt )
LOOP
fnd_file.put_line(FND_FILE.log, ' Dimension ID :: ' || dim_rec.dimension_id);
FOR hier_rec IN (SELECT dimension_id,
dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name,
table_row
FROM fem_ld_hier_requests_gt )
LOOP
fnd_file.put_line(FND_FILE.log, ' Table Row :: ' || hier_rec.table_row);
FOR dim_rec IN ( SELECT dimension_varchar_label,
request_id,
status
FROM fem_ld_dim_requests_gt)
LOOP
IF dim_rec.request_id > 0 THEN
IF dim_rec.status = 'Y' THEN
fnd_message.set_name('FEM','FEM_DLLDR_DIMENSION_LOADED');
FOR hier_rec IN (SELECT dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name,
request_id,
status
FROM fem_ld_hier_requests_gt
WHERE table_row = gs_table_row_tab(i))
LOOP
l_row_exists := TRUE;
SELECT object_name
INTO l_hier_str
FROM fem_object_catalog_tl
WHERE object_id = gs_hier_obj_id_tab(i)
AND language = l_lang;
SELECT object_name
INTO l_hier_name
FROM fem_object_catalog_tl
WHERE object_id = gs_hier_obj_id_tab(i)
AND language = l_lang;
SELECT display_name
INTO l_hier_def_name
FROM fem_object_definition_tl
WHERE object_definition_id = gs_hier_obj_def_id_tab(i)
AND language = l_lang;
DELETE fem_ld_dim_requests_gt;
DELETE fem_ld_hier_requests_gt;