[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gvcd.dimension_id,
gvcd.value_set_id
BULK COLLECT INTO
l_dimension_vs_id
FROM fem_global_vs_combo_defs gvcd,
fem_ledgers_attr fla,
gcs_entities_attr gea,
fem_tab_column_prop ftcp,
fem_tab_columns_b ftcb,
gcs_data_sub_dtls gdsd,
fem_cal_periods_attr fcpa
WHERE gdsd.load_id = p_load_id
AND gea.entity_id = p_entity_id
AND gea.data_type_code = gdsd.balance_type_code
AND fcpa.cal_period_id = gdsd.cal_period_id
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, fcpa.date_assign_value)
AND fla.ledger_id = gea.ledger_id
AND fla.attribute_id = pLedgerVsComboAttr
AND fla.version_id = pLedgerVsComboVersion
AND ftcb.table_name = 'FEM_BALANCES'
AND ftcb.dimension_id = gvcd.dimension_id
AND ftcb.column_name = ftcp.column_name
AND ftcb.column_name <> 'INTERCOMPANY_ID'
AND ftcp.column_property_code = 'PROCESSING_KEY'
AND ftcp.table_name = ftcb.table_name
AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member;
SELECT DISTINCT gcr.child_entity_id
FROM gcs_cons_relationships gcr,
gcs_cons_eng_runs gcer,
fem_cal_periods_attr fcpa
WHERE gcr.parent_entity_id = p_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.hierarchy_id = gcer.hierarchy_id
AND gcer.run_name = p_run_name
AND gcer.MOST_RECENT_FLAG = 'Y'
AND fcpa.cal_period_id = gcer.cal_period_id
AND fcpa.attribute_id = pCalPeriodEndDateAttr
AND fcpa.version_id = pCalPeriodEndDateVersion
AND fcpa.date_assign_value BETWEEN gcr.start_date
AND NVL(gcr.end_date,fcpa.date_assign_value);
SELECT gdsd.currency_type_code,
gdsd.currency_code,
gdsd.balance_type_code,
gea.source_system_code,
fla_comp.dim_attribute_varchar_member entity_currency_code,
gdsd.entity_id
INTO l_currency_type_code,
l_currency_code,
l_balance_type_code,
l_source_system_code,
l_entity_currency_code,
l_entity_id
FROM gcs_data_sub_dtls gdsd,
gcs_entities_attr gea,
fem_ledgers_attr fla_comp,
fem_cal_periods_attr fcpa
WHERE gdsd.load_id = pXmlFileId
AND gea.entity_id = gdsd.entity_id
AND gea.data_type_code = gdsd.balance_type_code
AND fcpa.cal_period_id = gdsd.cal_period_id
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND NVL(gea.effective_end_date, fcpa.date_assign_value)
AND fla_comp.ledger_id = gea.ledger_id
AND fla_comp.attribute_id = pLedgerCurrAttr
AND fla_comp.version_id = pLedgerCurrVersion ;
dsSelectLiteral :=
'to_char(fb.ytd_debit_balance_e,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_debit_balance_e,'||
'to_char(fb.ytd_credit_balance_e,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_credit_balance_e,'||
'to_char(fb.ytd_balance_e,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_balance_e,'||
'to_char(fb.ytd_balance_f,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_balance_f,'||
' fb.currency_code currency_code1,'||
' fct.name currency_name1,';
dsSelectLiteral :=
'to_char(fb.ytd_debit_balance_e,:FORMAT_MASK) ytd_debit_balance_e,'||
'to_char(fb.ytd_credit_balance_e,:FORMAT_MASK) ytd_credit_balance_e,'||
'to_char(fb.ytd_balance_e,:FORMAT_MASK) ytd_balance_e,'||
'to_char(fb.ytd_balance_f,:FORMAT_MASK) ytd_balance_f,'||
':currency_code currency_code1,'||
':currency_name currency_name1,';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'dsSelectLiteral : ' || dsSelectLiteral);
SELECT decode(gvcd.value_set_id, fch_gvcd.value_set_id, 'MAPPED', 'UNMAPPED')
INTO l_map_flag
FROM fem_global_vs_combo_defs gvcd,
fem_ledgers_attr fla,
gcs_entities_attr gea,
fem_tab_column_prop ftcp,
fem_tab_columns_b ftcb,
gcs_system_options gso,
fem_global_vs_combo_defs fch_gvcd,
gcs_data_sub_dtls gdsd ,
fem_cal_periods_attr fcpa
WHERE gso.fch_global_vs_combo_id = fch_gvcd.global_vs_combo_id
AND gea.entity_id = gdsd.entity_id
AND gea.data_type_code = gdsd.balance_type_code
AND fcpa.cal_period_id = gdsd.cal_period_id
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND NVL(gea.effective_end_date, fcpa.date_assign_value)
AND fla.ledger_id = gea.ledger_id
AND fla.attribute_id = pLedgerVsComboAttr
AND fla.version_id = pLedgerVsComboVersion
AND ftcb.table_name = 'FEM_BALANCES'
AND ftcb.dimension_id = gvcd.dimension_id
AND ftcb.column_name = ftcp.column_name
AND ftcb.column_name <> 'INTERCOMPANY_ID'
AND ftcp.column_property_code = 'PROCESSING_KEY'
AND ftcp.table_name = ftcb.table_name
AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
AND gvcd.dimension_id = 8
AND fch_gvcd.dimension_id = 8
AND gdsd.load_id = pXmlFileId;
entityOrgsLiteral := ' AND EXISTS (SELECT 1 '||
' FROM fem_cctr_orgs_b cob, '||
' gcs_entity_cctr_orgs eco '||
' WHERE cob.value_set_id = :ORG_VALUE_SET_ID'||
' AND eco.entity_id = :ENTITY_ID'||
' AND eco.company_cost_center_org_id = cob.company_cost_center_org_id '||
' AND cob.company_cost_center_org_id = fb.company_cost_center_org_id)';
entityOrgsLiteral := ' AND EXISTS (SELECT 1 '||
' FROM fem_cctr_orgs_hier fcoh, '||
' fem_global_vs_combo_defs fgvscd_master, '||
' gcs_system_options gso, '||
' fem_global_vs_combo_defs fgvscd_child, '||
' fem_xdim_dimensions fxd, '||
' fem_object_definition_b fodb, '||
' gcs_entity_cctr_orgs feco '||
' WHERE fcoh.child_id = fb.company_cost_center_org_id '||
' AND fxd.dimension_id = 8 '||
' AND fodb.object_id = fxd.default_mvs_hierarchy_obj_id '||
' AND fcoh.hierarchy_obj_def_id = fodb.object_definition_id '||
' AND fgvscd_master.global_vs_combo_id = gso.fch_global_vs_combo_id '||
' AND fgvscd_master.dimension_id = 8 '||
-- hakumar 5350290: removed trailing tabs from SUB_GLOBAL_VS_COMBO_ID
' AND fgvscd_child.global_vs_combo_id = :SUB_GLOBAL_VS_COMBO_ID'||
' AND fgvscd_child.dimension_id = 8 '||
' AND fcoh.parent_value_set_id = fgvscd_master.value_set_id '||
' AND fcoh.child_value_set_id = fgvscd_child.value_set_id '||
' AND feco.entity_id = :ENTITY_ID'||
' AND fcoh.parent_id = feco.company_cost_center_org_id) ';
SELECT fla.dim_attribute_varchar_member,
fda.dim_attribute_varchar_member,
gea.ledger_id
INTO l_currency_code,
l_dataset_code,
l_ledger_id
FROM gcs_data_type_codes_vl gdtcb,
gcs_entities_attr gea,
fem_ledgers_attr fla,
fem_datasets_attr fda,
fem_cal_periods_attr fcpa
WHERE gea.data_type_code = gdtcb.data_type_code
AND fla.ledger_id = gea.ledger_id
AND fda.dataset_code = gdtcb.source_dataset_code
AND gea.entity_id = pEntityId
AND gea.data_type_code = pDataTypeCode
AND fcpa.cal_period_id = pCalPeriodId
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, fcpa.date_assign_value )
AND fla.attribute_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE').attribute_id
AND fla.version_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE').version_id
AND fda.attribute_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-DATASET_BALANCE_TYPE_CODE').attribute_id
AND fda.version_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-DATASET_BALANCE_TYPE_CODE').version_id ;
SELECT fibrd.actual_output_dataset_code
INTO l_source_datasetcode
FROM gcs_entities_attr gea ,
fem_object_definition_b fodb,
fem_intg_bal_rule_defs fibrd,
fem_cal_periods_attr fcpa
WHERE fodb.object_id = gea.balances_rule_id
AND fibrd.bal_rule_obj_def_id = fodb.object_definition_id
AND gea.entity_id = pEntityId
AND gea.data_type_code = pDataTypeCode
AND fcpa.cal_period_id = pCalPeriodId
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
SELECT fda.dim_attribute_numeric_member
INTO l_budget_vers_id
FROM fem_datasets_attr fda
WHERE fda.attribute_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-BUDGET_ID').attribute_id
AND fda.version_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-BUDGET_ID').version_id
AND fda.dataset_code = l_source_datasetcode ; -- datasetcode from previous query
SELECT fda.dim_attribute_numeric_member
INTO l_enc_type_id
FROM fem_datasets_attr fda
WHERE fda.attribute_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-ENCUMBRANCE_TYPE_ID').attribute_id
AND fda.version_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-ENCUMBRANCE_TYPE_ID').version_id
AND fda.dataset_code = l_source_datasetcode ; -- datasetcode from previous query
SELECT cal_period_name
INTO l_cal_period
FROM fem_cal_periods_tl
WHERE cal_period_id = pCalPeriodId --obtained from UI parameters
AND language = userenv('LANG');
SELECT application_column_name
INTO l_segment_column
FROM fnd_segment_attribute_values fsav ,
gl_sets_of_books gsob
WHERE fsav.id_flex_num = gsob.CHART_OF_ACCOUNTS_ID
AND fsav.segment_attribute_type = 'GL_BALANCING'
AND fsav.attribute_value = 'Y'
AND fsav.application_id = 101
AND fsav.id_flex_code = 'GL#'
AND gsob.set_of_books_id = l_ledger_id;
SELECT '''' ||LEDGER_NAME || ''''
INTO LedgerLiteral
FROM fem_ledgers_vl
WHERE ledger_id = l_ledger_id;
SELECT gvcd.value_set_id,
fch_gvcd.value_set_id,
decode(gvcd.global_vs_combo_id, fch_gvcd.global_vs_combo_id, 'MAPPED', 'UNMAPPED')
INTO l_global_vsid,
l_fch_vsid,
l_map_flag
FROM fem_global_vs_combo_defs gvcd,
fem_ledgers_attr fla,
gcs_entities_attr gea,
gcs_system_options gso,
fem_global_vs_combo_defs fch_gvcd,
gcs_data_sub_dtls gdsd,
fem_cal_periods_attr fcpa
WHERE gso.fch_global_vs_combo_id = fch_gvcd.global_vs_combo_id
AND fla.ledger_id = gea.ledger_id
AND fla.attribute_id = pLedgerVsComboAttr
AND fla.version_id = pLedgerVsComboVersion
AND gdsd.load_id = pLoadId
AND gea.entity_id = gdsd.entity_id
AND gea.data_type_code = gdsd.balance_type_code
AND fcpa.cal_period_id = gdsd.cal_period_id
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, fcpa.date_assign_value )
AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
AND gvcd.dimension_id = 8
AND fch_gvcd.dimension_id = 8 ;
entityOrgsLiteral := ' AND EXISTS (SELECT 1 '||
' FROM gcs_entity_cctr_orgs eco '||
' WHERE eco.entity_id =' ||pEntityId ||
' AND eco.company_cost_center_org_id = fb.company_cost_center_org_id) ';
SELECT fcpa.date_assign_value
INTO l_cal_pd_end_date
FROM fem_cal_periods_attr fcpa
WHERE fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.cal_period_id = pCalPeriodId ;
SELECT NVL(default_mvs_hierarchy_obj_id,-1)
INTO l_def_hier_id
FROM fem_xdim_dimensions
WHERE dimension_id = 8;
SELECT object_definition_id
INTO l_her_obj_def_id
FROM fem_object_definition_b
WHERE object_id = l_def_hier_id
AND l_cal_pd_end_date BETWEEN effective_start_date AND effective_end_date;
entityOrgsLiteral := ' AND fb.company_cost_center_org_id IN (SELECT ' ||
' child_id FROM fem_cctr_orgs_hier WHERE parent_value_set_id = ' || l_fch_vsid ||
' AND child_value_set_id = ' || l_global_vsid ||
' AND parent_id in (SELECT company_cost_center_org_id FROM gcs_entity_cctr_orgs'||
' WHERE entity_id = ' || pEntityId || ')' || ' AND HIERARCHY_OBJ_DEF_ID = ' || l_her_obj_def_id || ')';
SELECT application_column_name
FROM fnd_id_flex_segments fifs,
gcs_writeback_headers gwh,
gl_sets_of_books gsb
WHERE gwh.writeback_id = p_Xml_File_Id
AND gwh.ledger_id = gsb.set_of_books_id
AND fifs.id_flex_num = gsb.chart_of_accounts_id
AND id_flex_code = 'GL#'
AND application_id = 101
ORDER BY segment_num;
SELECT fcpa.number_assign_value view_period_year
FROM gcs_entry_headers geh,
fem_cal_periods_attr fcpa
WHERE geh.entry_id = pEntryId
AND fcpa.cal_period_id = pCalPeriodId
AND fcpa.attribute_id = pAccountingYrAttrId
AND fcpa.version_id = pAccountingYrVerId;
SELECT geh.year_to_apply_re
FROM gcs_entry_headers geh
WHERE geh.entry_id = pEntryId;