[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE fem_ld_interface_data_gt;
DELETE fem_ld_dim_requests_gt;
DELETE fem_ld_hier_requests_gt;
DELETE fem_ld_cal_periods_gt;
gs_table_row_tab.DELETE;
gs_table_name_tab.DELETE;
gs_sql_dup_tab.DELETE;
gs_sql_dup_indx_tab.DELETE;
gs_ss_tab.DELETE;
gs_ds_tab.DELETE;
gs_ledger_tab.DELETE;
gs_ss_code_tab.DELETE;
gs_ds_code_tab.DELETE;
gs_ledger_code_tab.DELETE;
gs_budget_code_tab.DELETE;
gs_enc_code_tab.DELETE;
gs_load_opt_tab.DELETE;
gs_ds_bal_code_tab.DELETE;
gs_cal_grp_tab.DELETE;
gs_sql_stmt_tab.DELETE;
g_b_dataset_code.DELETE;
g_e_dataset_code.DELETE;
g_budget_id.DELETE;
g_enc_type_id.DELETE;
g_ledger_id.DELETE;
g_ds_code.DELETE;
g_ss_code.DELETE;
g_invalid_ds_code.DELETE;
gs_valid_rows.DELETE;
gs_table_obj_def_id.DELETE;
g_inv_ledger.DELETE;
g_inv_dataset.DELETE;
g_inv_source_system.DELETE;
g_inv_ds_pd_flag.DELETE;
g_inv_table_name.DELETE;
g_inv_table_row.DELETE;
g_master_rec.DELETE;
g_cal_period_rec.DELETE;
g_interface_data_rec.DELETE;
DELETE fem_ld_interface_data_gt;
DELETE fem_ld_dim_requests_gt;
DELETE fem_ld_hier_requests_gt;
DELETE fem_ld_cal_periods_gt;
SELECT loader_type,
approval_status_code,
object_id
INTO g_loader_type,
l_approval_status,
g_object_id
FROM fem_data_loader_rules fdlr,
fem_object_definition_b fod
WHERE fdlr.loader_obj_id = fod.object_id
AND fod.object_definition_id = p_obj_def_id;
SELECT interface_table_name
INTO g_int_table_name
FROM fem_tables_b
WHERE table_name = 'FEM_BALANCES';
SELECT ROWNUM,
fdlp.table_name,
source_system_code,
dataset_code,
ledger_id,
load_option,
cal_period_grp_id,
'SELECT ledger_display_code,
dataset_display_code,
source_system_display_code,
cal_period_number,
calp_dim_grp_display_code,
cal_period_end_date,' || '''' ||
fdlp.table_name || '''' || ',' || 'TO_NUMBER(''' || ROWNUM || ''')' ||
' FROM ' dyn_sql_stmt,
fodb.object_id,
fodb.object_definition_id
BULK COLLECT INTO gs_table_row_tab,
gs_table_name_tab,
gs_ss_tab,
gs_ds_tab,
gs_ledger_tab,
gs_load_opt_tab,
gs_cal_grp_tab,
gs_sql_stmt_tab,
gs_table_obj_id,
gs_table_obj_def_id
FROM fem_data_loader_params fdlp,
fem_data_loader_objects fdlo,
fem_object_definition_b fodb
WHERE loader_obj_def_id = p_obj_def_id
AND fdlp.table_name = fdlo.table_name
AND fdlo.object_id = fodb.object_id;
SELECT ROWNUM,
table_name,
dataset_code,
ledger_id,
load_option,
cal_period_grp_id,
'SELECT cal_period_number,
cal_period_end_date,
cal_per_dim_grp_display_code,
ledger_display_code,
ds_balance_type_code,
budget_display_code,
encumbrance_type_code,' || '''' ||
table_name || '''' || ',' || 'TO_NUMBER(''' || ROWNUM || ''')' ||
' FROM ' dyn_sql_stmt,
1000 object_id
BULK COLLECT INTO gs_table_row_tab,
gs_table_name_tab,
gs_ds_tab,
gs_ledger_tab,
gs_load_opt_tab,
gs_cal_grp_tab,
gs_sql_stmt_tab,
gs_table_obj_id
FROM fem_data_loader_params
WHERE loader_obj_def_id = p_obj_def_id;
SELECT dimension_id
INTO g_ledger_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'LEDGER';
SELECT da.attribute_id
INTO g_cal_period_hier_attr
FROM fem_dim_attributes_b da,
fem_dim_attr_versions_b dav
WHERE da.dimension_id = g_ledger_dim_id
AND da.attribute_varchar_label = 'CAL_PERIOD_HIER_OBJ_DEF_ID'
AND dav.attribute_id = da.attribute_id
AND dav.default_version_flag = 'Y';
SELECT dimension_id
INTO g_cal_period_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'CAL_PERIOD';
SELECT da.attribute_id
INTO g_start_date_attr
FROM fem_dim_attributes_b da,
fem_dim_attr_versions_b dav
WHERE da.dimension_id = g_cal_period_dim_id
AND da.attribute_varchar_label = 'CAL_PERIOD_START_DATE'
AND dav.attribute_id = da.attribute_id
AND dav.default_version_flag = 'Y';
SELECT da.attribute_id
INTO g_end_date_attr
FROM fem_dim_attributes_b da,
fem_dim_attr_versions_b dav
WHERE da.dimension_id = g_cal_period_dim_id
AND da.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
AND dav.attribute_id = da.attribute_id
AND dav.default_version_flag = 'Y';
SELECT dimension_id
INTO g_dataset_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'DATASET';
SELECT da.attribute_id
INTO g_dataset_bal_attr
FROM fem_dim_attributes_b da,
fem_dim_attr_versions_b dav
WHERE da.dimension_id = g_dataset_dim_id
AND da.attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE'
AND dav.attribute_id = da.attribute_id
AND dav.default_version_flag = 'Y';
SELECT da.attribute_id
INTO g_production_attr
FROM fem_dim_attributes_b da,
fem_dim_attr_versions_b dav
WHERE da.dimension_id = g_dataset_dim_id
AND da.attribute_varchar_label = 'PRODUCTION_FLAG'
AND dav.attribute_id = da.attribute_id
AND dav.default_version_flag = 'Y';
SELECT dim.dimension_id
INTO g_budget_dim_id
FROM fem_dimensions_b dim
WHERE dim.dimension_varchar_label = 'BUDGET';
FOR dim_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=> 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_hier_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 DISTINCT
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 hier_obj_def_display_name = hier_rec.hier_obj_def_display_name
AND dimension_id = hier_rec.dimension_id;
SELECT 1.0
INTO l_dummy
FROM fem_ld_dim_requests_gt
WHERE table_name = gs_table_name_tab(i)
AND ROWNUM = 1;
INSERT INTO fem_ld_dim_requests_gt(dimension_id,
dimension_varchar_label,
table_name,
dim_intf_table_name,
request_id,
status)
SELECT fdb.dimension_id,
dimension_varchar_label,
gs_table_name_tab(i),
intf_member_b_table_name,
TO_NUMBER(NULL),
'N'
FROM fem_tab_columns_b ftcb,
fem_dimensions_b fdb,
fem_xdim_dimensions fxd
WHERE table_name = gs_table_name_tab(i)
AND fem_data_type_code = 'DIMENSION'
AND fdb.dimension_id = ftcb.dimension_id
AND fxd.dimension_id = fdb.dimension_id
AND intf_member_b_table_name IS NOT NULL;
,p_msg_text => 'EXCEPTION in build_dim_stages - insert into
fem_ld_dim_requests_gt');
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');
SELECT 1.0
INTO l_dummy
FROM fem_ld_hier_requests_gt
WHERE table_name = gs_table_name_tab(i)
AND ROWNUM = 1 ;
INSERT INTO fem_ld_hier_requests_gt(dimension_id,
dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name,
table_name,
request_id)
SELECT drt.dimension_id,
drt.dimension_varchar_label,
fht.hierarchy_object_name,
fht.hier_obj_def_display_name,
gs_table_name_tab(i),
TO_NUMBER(NULL)
FROM fem_ld_dim_requests_gt drt,
fem_hierarchies_t fht
WHERE table_name = gs_table_name_tab(i)
AND drt.dimension_varchar_label = fht.dimension_varchar_label;
,p_msg_text => 'EXCEPTION in build_dim_stages - insert into
fem_ld_hier_requests_gt');
SELECT interface_table_name
INTO l_int_table_name
FROM fem_tables_b
WHERE table_name = gs_table_name_tab(i);
EXECUTE IMMEDIATE 'SELECT 1 FROM ' || gs_table_name_tab(i) || ' WHERE ROWNUM=1';
INSERT INTO fem_ld_interface_data_gt
(ledger_display_code,
dataset_display_code,
source_system_display_code,
cal_period_number,
cal_period_level,
cal_period_end_date,
table_name,
table_row,
ds_production_valid_flag,
status)
VALUES
(l_ledger_tab(k),
l_dataset_tab(k),
l_source_system_tab(k),
l_cal_period_number_tab(k),
l_cal_period_level_tab(k),
l_cal_period_end_date_tab(k),
l_table_name_tab(k),
l_table_row_tab(k),
'N',
'INVALID');
INSERT INTO fem_ld_interface_data_gt
(ledger_display_code,
dataset_display_code,
source_system_display_code,
cal_period_number,
cal_period_level,
cal_period_end_date,
table_name,
table_row,
ds_production_valid_flag,
status)
SELECT
ledger_display_code,
dataset_display_code,
source_system_display_code,
cal_period_number,
cal_period_level,
cal_period_end_date,
table_name,
gs_table_row_tab(k),
ds_production_valid_flag,
status
FROM fem_ld_interface_data_gt
WHERE table_name = l_table_name
AND table_row = l_table_row
AND gs_table_name_tab(k) = l_table_name;
UPDATE fem_ld_interface_data_gt idt
SET ledger_id = (SELECT ledger_id
FROM fem_ledgers_b flb
WHERE idt.ledger_display_code = flb.ledger_display_code
AND personal_flag = 'N'
AND enabled_flag = 'Y'),
dataset_code = (SELECT dataset_code
FROM fem_datasets_b fdb
WHERE idt.dataset_display_code = fdb.dataset_display_code
AND personal_flag = 'N'
AND enabled_flag = 'Y'),
source_system_code = (SELECT source_system_code
FROM fem_source_systems_b fssb
WHERE idt.source_system_display_code = fssb.source_system_display_code
AND personal_flag = 'N'
AND enabled_flag = 'Y'),
(dimension_group_id,time_dimension_group_key) = (SELECT dimension_group_id, time_dimension_group_key
FROM fem_dimension_grps_b fdgb
WHERE idt.cal_period_level =
fdgb.dimension_group_display_code
AND personal_flag = 'N'
AND enabled_flag = 'Y');
,p_msg_text => ' Rows updated after ID population(DATA LOAD) :: ' || SQL%ROWCOUNT);
fnd_file.put_line(fnd_file.log, ' Rows updated after ID population :: ' || SQL%ROWCOUNT);
SELECT dim_attribute_varchar_member
INTO l_bal_type_code
FROM fem_datasets_attr
WHERE dataset_code = gs_ds_tab(i)
AND attribute_id = g_dataset_bal_attr;
INSERT INTO fem_ld_interface_data_gt
(ledger_display_code,
dataset_code,
balance_type_code,
budget_display_code,
encumbrance_type_code,
cal_period_number,
cal_period_level,
cal_period_end_date,
table_name,
table_row,
ds_production_valid_flag,
status)
VALUES
(l_ledger_tab(k),
gs_ds_tab(1),
l_ds_bal_code_tab(k),
l_budget_display_cd_tab(k),
l_encumbrance_type_cd_tab(k),
l_cal_period_number_tab(k),
l_cal_period_level_tab(k),
l_cal_period_end_date_tab(k),
l_table_name_tab(k),
l_table_row_tab(k),
'N',
'INVALID');
INSERT INTO fem_ld_interface_data_gt
(ledger_display_code,
dataset_code,
balance_type_code,
budget_display_code,
encumbrance_type_code,
cal_period_number,
cal_period_level,
cal_period_end_date,
table_name,
table_row,
ds_production_valid_flag,
status)
SELECT
ledger_display_code,
gs_ds_tab(k),
balance_type_code,
budget_display_code,
encumbrance_type_code,
cal_period_number,
cal_period_level,
cal_period_end_date,
table_name,
gs_table_row_tab(k),
ds_production_valid_flag,
status
FROM fem_ld_interface_data_gt
WHERE balance_type_code = l_table_name
AND table_row = l_table_row
AND gs_ds_bal_code_tab(k) = l_table_name;
UPDATE fem_ld_interface_data_gt idt
SET ledger_id = (SELECT ledger_id
FROM fem_ledgers_b flb
WHERE idt.ledger_display_code = flb.ledger_display_code
AND personal_flag = 'N'
AND enabled_flag = 'Y'),
dataset_display_code = (SELECT dataset_display_code
FROM fem_datasets_b fdb
WHERE idt.dataset_code = fdb.dataset_code
AND personal_flag = 'N'
AND enabled_flag = 'Y'),
(dimension_group_id,time_dimension_group_key) = (SELECT dimension_group_id, time_dimension_group_key
FROM fem_dimension_grps_b fdgb
WHERE idt.cal_period_level =
fdgb.dimension_group_display_code
AND fdgb.personal_flag = 'N'
AND fdgb.enabled_flag = 'Y');
,p_msg_text => ' Rows updated after ID population(LEDGER LOAD) :: ' || SQL%ROWCOUNT);
fnd_file.put_line(fnd_file.log, ' Rows updated after ID population :: ' || SQL%ROWCOUNT);
UPDATE fem_ld_interface_data_gt
SET ds_production_valid_flag = 'Y';
UPDATE fem_ld_interface_data_gt idt
SET ds_production_valid_flag = (SELECT DECODE(dim_attribute_varchar_member,'Y','N','Y')
FROM fem_datasets_attr fda
WHERE fda.attribute_id = g_production_attr
AND idt.dataset_code = fda.dataset_code);
FOR int_rec IN (SELECT ledger_id, dataset_code, ds_production_valid_flag, table_name, table_row
FROM fem_ld_interface_data_gt
ORDER BY table_row, table_name)
LOOP
fnd_file.put_line(FND_FILE.log, ' ==========================================================');
UPDATE fem_ld_interface_data_gt
SET status = 'VALID'
WHERE ledger_id = DECODE(gs_ledger_tab(i),-1,ledger_id,gs_ledger_tab(i))
AND dataset_code = DECODE(gs_ds_tab(i),-1,dataset_code,gs_ds_tab(i))
AND source_system_code = DECODE(gs_ss_tab(i),-1,source_system_code,gs_ss_tab(i))
AND dimension_group_id = DECODE(gs_cal_grp_tab(i),-1,dimension_group_id,gs_cal_grp_tab(i))
AND ds_production_valid_flag = 'Y'
AND table_row = gs_table_row_tab(i)
AND table_name = gs_table_name_tab(i);
UPDATE fem_ld_interface_data_gt
SET status = 'VALID'
WHERE ledger_id = DECODE(gs_ledger_tab(i),-1,ledger_id,gs_ledger_tab(i))
AND dimension_group_id = DECODE(gs_cal_grp_tab(i),-1,dimension_group_id,gs_cal_grp_tab(i))
AND ds_production_valid_flag = 'Y'
AND table_row = gs_table_row_tab(i)
AND table_name = gs_table_name_tab(i);
SELECT COUNT(*)
INTO l_dummy
FROM fem_ld_interface_data_gt
WHERE table_name = gs_table_name_tab(i)
AND table_row = gs_table_row_tab(i);
fnd_file.put_line(FND_FILE.log, ' After update ' || gs_table_name_tab(i) || ' COUNT(*) = ' || l_dummy);
fnd_file.put_line(FND_FILE.log, ' After update ' || gs_table_name_tab(i) || ' Valid = ' || gs_valid_rows(i));
FOR int_rec IN (SELECT ledger_id, dataset_code, ds_production_valid_flag, table_name, table_row, status
FROM fem_ld_interface_data_gt
ORDER BY table_row, table_name)
LOOP
fnd_file.put_line(FND_FILE.log, ' ==========================================================');
DELETE fem_ld_interface_data_gt
WHERE status <> 'VALID'
RETURNING ledger_display_code,
dataset_display_code,
source_system_display_code,
ds_production_valid_flag,
table_name,
table_row
BULK COLLECT INTO g_inv_ledger,
g_inv_dataset,
g_inv_source_system,
g_inv_ds_pd_flag,
g_inv_table_name,
g_inv_table_row;
SELECT COUNT(*)
INTO l_dummy
FROM fem_ld_interface_data_gt
WHERE budget_display_code IS NOT NULL;
UPDATE fem_ld_interface_data_gt idt
SET budget_id = (SELECT budget_id
FROM fem_budgets_b fdb
WHERE idt.budget_display_code = fdb.budget_display_code
AND personal_flag = 'N'
AND enabled_flag = 'Y')
WHERE budget_display_code IS NOT NULL;
SELECT COUNT(*)
INTO l_dummy
FROM fem_ld_interface_data_gt
WHERE encumbrance_type_code IS NOT NULL;
UPDATE fem_ld_interface_data_gt idt
SET encumbrance_type_id = (SELECT encumbrance_type_id
FROM fem_encumbrance_types_b fetb
WHERE fetb.enabled_flag = 'Y'
AND fetb.personal_flag = 'N'
AND idt.encumbrance_type_code = fetb.encumbrance_type_code)
WHERE encumbrance_type_code IS NOT NULL;
UPDATE fem_ld_interface_data_gt idt
SET calendar_id = (SELECT calendar_id
FROM fem_hierarchies fh,
fem_object_definition_b fodb,
fem_ledgers_attr fla
WHERE fh.hierarchy_obj_id = fodb.object_id
AND fodb.object_definition_id = fla.dim_attribute_numeric_member
AND fla.ledger_id = idt.ledger_id
AND fla.attribute_id = g_cal_period_hier_attr
AND fh.dimension_id = g_cal_period_dim_id);
UPDATE fem_ld_interface_data_gt idt
SET cal_period = TO_CHAR(idt.cal_period_end_date,'J') ||
LPAD(TO_CHAR(idt.cal_period_number),15,'0') ||
LPAD(TO_CHAR(idt.calendar_id),5,'0') ||
LPAD(TO_CHAR(idt.time_dimension_group_key),5,'0');
INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
SELECT gs_table_name_tab(i), gs_table_row_tab(i), gs_ledger_tab(i), fcpb.cal_period_id,
fcpb.dimension_group_id, 'VALID'
FROM fem_calendars_b fcb,
fem_dimension_grps_b fdgb,
fem_cal_periods_b fcpb
WHERE fcpb.calendar_id = fcb.calendar_id
AND fcpb.dimension_group_id = fdgb.dimension_group_id
AND EXISTS ( SELECT 1
FROM fem_ld_interface_data_gt idt
WHERE fdgb.dimension_group_id = idt.dimension_group_id
AND idt.ledger_id = gs_ledger_tab(i)
AND idt.dimension_group_id = gs_cal_grp_tab(i)
AND fcb.calendar_id = idt.calendar_id
AND fcpb.cal_period_id = idt.cal_period
AND table_name = gs_table_name_tab(i)
AND table_row = gs_table_row_tab(i) )
AND EXISTS ( SELECT 1
FROM fem_cal_periods_attr a,
fem_cal_periods_attr b
WHERE a.attribute_id = g_start_date_attr
AND b.attribute_id = g_end_date_attr
AND a.cal_period_id = fcpb.cal_period_id
AND a.cal_period_id = b.cal_period_id
AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
SELECT gs_table_name_tab(i), gs_table_row_tab(i), gs_ledger_tab(i), fcpb.cal_period_id,
fcpb.dimension_group_id, 'VALID'
FROM fem_calendars_b fcb,
fem_dimension_grps_b fdgb,
fem_cal_periods_b fcpb
WHERE fcpb.calendar_id = fcb.calendar_id
AND fcpb.dimension_group_id = fdgb.dimension_group_id
AND EXISTS ( SELECT 1
FROM fem_ld_interface_data_gt idt
WHERE fdgb.dimension_group_id = idt.dimension_group_id
AND idt.ledger_id = gs_ledger_tab(i)
AND fcb.calendar_id = idt.calendar_id
AND fcpb.cal_period_id = idt.cal_period
AND table_name = gs_table_name_tab(i)
AND table_row = gs_table_row_tab(i) )
AND EXISTS ( SELECT 1
FROM fem_cal_periods_attr a,
fem_cal_periods_attr b
WHERE a.attribute_id = g_start_date_attr
AND b.attribute_id = g_end_date_attr
AND a.cal_period_id = fcpb.cal_period_id
AND a.cal_period_id = b.cal_period_id
AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
SELECT gs_table_name_tab(i), gs_table_row_tab(i), idt.ledger_id, fcpb.cal_period_id,
fcpb.dimension_group_id, 'VALID'
FROM fem_calendars_b fcb,
fem_dimension_grps_b fdgb,
fem_cal_periods_b fcpb,
(SELECT DISTINCT ledger_id,
dimension_group_id,
calendar_id,
cal_period
FROM fem_ld_interface_data_gt
WHERE table_name = gs_table_name_tab(i)
AND table_row = gs_table_row_tab(i)) idt
WHERE fcpb.calendar_id = fcb.calendar_id
AND fcpb.dimension_group_id = fdgb.dimension_group_id
AND fdgb.dimension_group_id = idt.dimension_group_id
AND fcb.calendar_id = idt.calendar_id
AND fcpb.cal_period_id = idt.cal_period
AND EXISTS ( SELECT 1
FROM fem_cal_periods_attr a,
fem_cal_periods_attr b
WHERE a.attribute_id = g_start_date_attr
AND b.attribute_id = g_end_date_attr
AND a.cal_period_id = fcpb.cal_period_id
AND a.cal_period_id = b.cal_period_id
AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
INSERT INTO fem_ld_cal_periods_gt(table_name, table_row, ledger_id, cal_period_id, dim_grp_id, valid)
SELECT gs_table_name_tab(i), gs_table_row_tab(i), idt.ledger_id, fcpb.cal_period_id,
fcpb.dimension_group_id, 'VALID'
FROM fem_calendars_b fcb,
fem_dimension_grps_b fdgb,
fem_cal_periods_b fcpb,
(SELECT DISTINCT ledger_id,
calendar_id,
cal_period
FROM fem_ld_interface_data_gt
WHERE table_name = gs_table_name_tab(i)
AND table_row = gs_table_row_tab(i)
AND dimension_group_id = gs_cal_grp_tab(i)) idt
WHERE fcpb.calendar_id = fcb.calendar_id
AND fcpb.dimension_group_id = fdgb.dimension_group_id
AND fdgb.dimension_group_id = gs_cal_grp_tab(i)
AND fcb.calendar_id = idt.calendar_id
AND fcpb.cal_period_id = idt.cal_period
AND EXISTS ( SELECT 1
FROM fem_cal_periods_attr a,
fem_cal_periods_attr b
WHERE a.attribute_id = g_start_date_attr
AND b.attribute_id = g_end_date_attr
AND a.cal_period_id = fcpb.cal_period_id
AND a.cal_period_id = b.cal_period_id
AND fnd_date.date_to_canonical(a.date_assign_value) >= g_start_date
AND fnd_date.date_to_canonical(b.date_assign_value) <= g_end_date );
FOR cal_rec IN (SELECT ledger_id, table_name, table_row, cal_period_id, valid
FROM fem_ld_cal_periods_gt)
LOOP
fnd_file.put_line(FND_FILE.log, ' ==========================================================');
SELECT COUNT(1)
INTO l_dummy1
FROM fem_ld_cal_periods_gt
WHERE ROWNUM = 1;
SELECT DISTINCT idt.ledger_id, cal_period_id, dataset_code,
budget_id, encumbrance_type_id,
idt.ledger_display_code, idt.dataset_display_code,
idt.encumbrance_type_code, idt.budget_display_code
BULK COLLECT INTO g_ledger_id, g_cal_period_id, g_ds_code,
g_budget_id, g_enc_type_id,
gs_ledger_code_tab, gs_ds_code_tab, gs_budget_code_tab,
gs_enc_code_tab
FROM fem_ld_interface_data_gt idt,
fem_ld_cal_periods_gt cpt
WHERE idt.table_name = gs_table_name_tab(i)
AND idt.table_row = gs_table_row_tab(i)
AND idt.table_name = cpt.table_name
AND idt.table_row = cpt.table_row
AND idt.ledger_id = cpt.ledger_id;
SELECT DISTINCT idt.ledger_id, cal_period_id, source_system_code, dataset_code,
idt.ledger_display_code, idt.dataset_display_code,
idt.source_system_display_code
BULK COLLECT INTO g_ledger_id, g_cal_period_id, g_ss_code, g_ds_code,
gs_ledger_code_tab, gs_ds_code_tab, gs_ss_code_tab
FROM fem_ld_interface_data_gt idt,
fem_ld_cal_periods_gt cpt
WHERE idt.table_name = gs_table_name_tab(i)
AND idt.table_row = gs_table_row_tab(i)
AND idt.table_name = cpt.table_name
AND idt.table_row = cpt.table_row
AND idt.ledger_id = cpt.ledger_id;
FOR dim_rec IN ( SELECT dimension_id,
dimension_varchar_label,
table_name,
request_id,
status
FROM fem_ld_dim_requests_gt drt
WHERE table_name = p_table_name )
LOOP
IF l_table_name IS NULL THEN
l_table_name := dim_rec.table_name;
FOR hier_rec IN (SELECT dimension_varchar_label,
hierarchy_object_name,
hier_obj_def_display_name,
table_name,
request_id,
status
FROM fem_ld_hier_requests_gt
WHERE table_name = p_table_name )
LOOP
IF l_table_name IS NULL THEN
l_table_name := hier_rec.table_name;
,p_last_update_login => g_login_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
SELECT ledger_display_code
INTO gs_ledger_code_tab(i)
FROM fem_ledgers_b
WHERE ledger_id = gs_ledger_tab(i)
AND personal_flag = 'N'
AND enabled_flag = 'Y';
SELECT source_system_display_code
INTO gs_ss_code_tab(i)
FROM fem_source_systems_b
WHERE source_system_code = gs_ss_tab(i)
AND personal_flag = 'N'
AND enabled_flag = 'Y';
SELECT dataset_display_code
INTO gs_ds_code_tab(i)
FROM fem_datasets_b
WHERE dataset_code = gs_ds_tab(i)
AND personal_flag = 'N'
AND enabled_flag = 'Y';
SELECT dataset_display_code
INTO gs_ds_code_tab(i)
FROM fem_datasets_b
WHERE dataset_code = gs_ds_tab(i)
AND personal_flag = 'N'
AND enabled_flag = 'Y';