[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
rpad(fvs.flex_value_set_name, 30, ' ') vs_name,
ifs.id_flex_structure_name coa_name
from ( select distinct sas.chart_of_accounts_id
from fii_slg_assignments sas,
fii_source_ledger_groups slg
where slg.usage_code = 'DBI'
and slg.source_ledger_group_id = sas.source_ledger_group_id
) coa_list,
fii_dim_mapping_rules dmr,
fnd_flex_value_sets fvs,
fnd_id_flex_structures_v ifs
where coa_list.chart_of_accounts_id = dmr.chart_of_accounts_id
and dmr.dimension_short_name = p_dim_short_name
and not exists (
select 1
from fii_dim_norm_hierarchy dnh
where dnh.parent_flex_value_set_id = l_master_vs_id
and dnh.child_flex_value_set_id = dmr.flex_value_set_id1
and rownum = 1
)
and dmr.flex_value_set_id1 = fvs.flex_value_set_id
and dmr.chart_of_accounts_id = ifs.id_flex_num
and ifs.application_id = 101
and ifs.id_flex_code = 'GL#'
and ifs.enabled_flag = 'Y';
SELECT master_value_set_id
INTO l_master_vs_id
FROM fii_financial_dimensions
WHERE dimension_short_name = p_dim_short_name;
select count(*) cnt,
company_id,
cost_center_id
from fii_ccc_mgr_gt
where company_id is not null
and cost_center_id is not null
group by company_id, cost_center_id
having count(*) > 1;
select org.name organization,
com.flex_value company,
cc.flex_value cost_center,
per.full_name manager,
fnd_date.canonical_to_date(hoi.org_information3) eff_date
from fii_ccc_mgr_gt gt,
hr_all_organization_units org,
fnd_flex_values com,
fnd_flex_values cc,
per_all_people_f per,
hr_organization_information hoi
where gt.company_id = p_com_id
and gt.cost_center_id = p_cc_id
and gt.ccc_org_id = org.organization_id
and gt.company_id = com.flex_value_id
and gt.cost_center_id = cc.flex_value_id
and gt.manager = per.person_id
and hoi.org_information_context = 'Organization Name Alias'
and hoi.organization_id = gt.ccc_org_id;
SELECT 1
FROM dual
WHERE NOT EXISTS (
SELECT DISTINCT suph.sup_person_id
FROM (SELECT to_number (mgr_tbl.org_information2) manager,
ccc_tbl.organization_id ccc_org_id
FROM hr_organization_information ccc_tbl,
( SELECT organization_id, org_information2
FROM hr_organization_information b
WHERE org_information_context = 'Organization Name Alias'
AND nvl( fnd_date.canonical_to_date( org_information3 ),
sysdate + 1 ) <= sysdate
AND nvl( fnd_date.canonical_to_date( org_information4 ),
sysdate + 1 ) >= sysdate
) mgr_tbl,
hr_organization_information org,
fnd_flex_values fv1,
fnd_flex_values fv2
WHERE ccc_tbl.org_information_context = 'CLASS'
AND ccc_tbl.org_information1 = 'CC'
AND ccc_tbl.org_information2 = 'Y'
AND ccc_tbl.organization_id = mgr_tbl.organization_id (+)
AND org.org_information_context = 'Company Cost Center'
AND org.organization_id = ccc_tbl.organization_id
AND fv1.flex_value_set_id = org.org_information2
AND fv1.flex_value = org.org_information3
AND fv2.flex_value_set_id = org.org_information4
AND fv2.flex_value = org.org_information5) ct,
hri_cs_suph suph,
per_assignment_status_types ast
WHERE ct.manager = suph.sub_person_id
AND sysdate between suph.effective_start_date and suph.effective_end_date
AND suph.sup_assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND ct.manager = user_id);
SELECT security_profile_name
FROM per_security_profiles
WHERE security_profile_id NOT IN (SELECT security_profile_id
FROM per_organization_list);
SELECT DISTINCT employee_id
INTO l_user_id
FROM fnd_user a,
fii_cc_mgr_sup_v b
WHERE a.user_name = UPPER(p_user_name)
AND a.employee_id = b.id;
select 1
from gl_periods
where adjustment_period_flag = 'N'
and period_set_name = l_period_set_name
and period_type = l_period_type
and day between start_date and end_date;
select min(a.start_date), max(end_date)
from gl_periods a
where a.period_set_name = l_period_set_name
and a.period_type = l_period_type
and a.adjustment_period_flag = 'N'
and a.period_year = year;
select dimension_name, dimension_short_name
from fii_financial_dimensions_v
where dbi_enabled_flag = 'Y';
select 'R'
from dual
where not exists (select 1 from fii_fin_cat_type_assgns
where fin_cat_type_code = 'R')
union
select 'OE'
from dual
where not exists (select 1 from fii_fin_cat_type_assgns
where fin_cat_type_code = 'OE')
union
select 'TE'
from dual
where not exists (select 1 from fii_fin_cat_type_assgns
where fin_cat_type_code = 'TE')
union
select 'CGS'
from dual
where not exists (select 1 from fii_fin_cat_type_assgns
where fin_cat_type_code = 'CGS')
union
select 'DR'
from dual
where not exists (select 1 from fii_fin_cat_type_assgns
where fin_cat_type_code = 'DR');
select fv_co.flex_value company,
fv_co.flex_value_id com_id,
fv_cc.flex_value cost_center,
fv_cc.flex_value_id cc_id
from (select distinct company_id, cost_center_id
from fii_gl_je_summary_b) b,
fnd_flex_values fv_co,
fnd_flex_values fv_cc
where fv_co.flex_value_id = b.company_id
and fv_cc.flex_value_id = b.cost_center_id
and not exists (select 1 from hr_organization_information
where org_information_context = 'Company Cost Center'
and org_information2 = fv_co.flex_value_set_id
and org_information3 = fv_co.flex_value
and org_information4 = fv_cc.flex_value_set_id
and org_information5 = fv_cc.flex_value);
select fv_co.flex_value_id co_id,
fv_co.flex_value co,
org.organization_id,
org2.name
from (select distinct company_id
from fii_gl_je_summary_b) b,
fnd_flex_values fv_co,
hr_organization_information org,
hr_all_organization_units org2
where fv_co.flex_value_id = b.company_id
and org_information_context = 'Company Cost Center'
and org_information2 = fv_co.flex_value_set_id
and org_information3 = fv_co.flex_value
and org_information4 is null
and org_information5 is null
and org2.organization_id = org.organization_id;
select fv_co.flex_value co,
fv_cc.flex_value cc,
org.organization_id,
org2.name organization
from (select distinct company_id, cost_center_id
from fii_gl_je_summary_b) b,
fnd_flex_values fv_co,
fnd_flex_values fv_cc,
hr_organization_information org,
hr_all_organization_units org2
where fv_co.flex_value_id = b.company_id
and fv_cc.flex_value_id = b.cost_center_id
and org_information_context = 'Company Cost Center'
and org_information2 = fv_co.flex_value_set_id
and org_information3 = fv_co.flex_value
and org_information4 = fv_cc.flex_value_set_id
and org_information5 = fv_cc.flex_value
and org2.organization_id = org.organization_id
and not exists
(select 1
from hr_organization_information mgr
where mgr.org_information_context = 'Organization Name Alias'
and (nvl( fnd_date.canonical_to_date( mgr.org_information3 ),
sysdate + 1 ) <= sysdate
or nvl( fnd_date.canonical_to_date( mgr.org_information4 ),
sysdate + 1 ) >= sysdate)
and mgr.org_information2 is not null
and mgr.organization_id = org.organization_id);
select per2.first_name || ' ' || per2.last_name name,
org.name organization,
per2.effective_end_date
from
( select organization_id, org_information2
from hr_organization_information b
where org_information_context = 'Organization Name Alias'
and (nvl( fnd_date.canonical_to_date( org_information3 ),
sysdate + 1 ) <= sysdate
or nvl( fnd_date.canonical_to_date( org_information4 ),
sysdate + 1 ) >= sysdate)
and org_information2 is not null
) mgr_tbl,
hr_all_organization_units org,
per_all_people_f per2
where org.organization_id = mgr_tbl.organization_id
and per2.person_id = mgr_tbl.org_information2
and not exists (select 1
from per_all_people_f per
where per.person_id = mgr_tbl.org_information2
and per.effective_end_date > sysdate);
select distinct per.full_name, ast.per_system_status
from ( select organization_id, org_information2
from hr_organization_information b
where org_information_context = 'Organization Name Alias'
and (nvl( fnd_date.canonical_to_date( org_information3 ),
sysdate + 1 ) <= sysdate
or nvl( fnd_date.canonical_to_date( org_information4 ),
sysdate + 1 ) >= sysdate)
and org_information2 is not null) ct,
hri_cs_suph suph,
per_assignment_status_types ast,
per_all_people_f per
where ct.org_information2 = suph.sub_person_id
and per.person_id = suph.sub_person_id
and sysdate between suph.effective_start_date
and suph.effective_end_date
and suph.sup_assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status NOT IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
SELECT ccc_tbl.organization_id ccc_org_id,
rpad(hou.name, 40, ' '), fgj.company_id, fv1.flex_value co,
fgj.cost_center_id, fv2.flex_value cc
FROM hr_organization_information ccc_tbl,
( select organization_id, org_information2
from hr_organization_information
where org_information_context = 'Organization Name Alias'
and nvl( fnd_date.canonical_to_date( org_information3 ),
sysdate + 1 ) <= sysdate
and nvl( fnd_date.canonical_to_date( org_information4 ),
sysdate + 1 ) >= sysdate
) mgr_tbl,
hr_organization_information org,
hr_all_organization_units hou,
fnd_flex_values fv1,
fnd_flex_values fv2,
(select distinct company_id, cost_center_id
from fii_gl_je_summary_b) fgj
WHERE ccc_tbl.org_information_context = 'CLASS'
AND ccc_tbl.org_information1 = 'CC'
AND ccc_tbl.org_information2 = 'Y'
AND ccc_tbl.organization_id = mgr_tbl.organization_id (+)
AND org.org_information_context = 'Company Cost Center'
AND org.organization_id = ccc_tbl.organization_id
AND hou.organization_id = ccc_tbl.organization_id
AND fv1.flex_value_set_id = org.org_information2
AND fv1.flex_value = org.org_information3
AND fv2.flex_value_set_id = org.org_information4
AND fv2.flex_value = org.org_information5
AND fv1.flex_value_id = fgj.company_id
AND fv2.flex_value_id = fgj.cost_center_id
AND fgj.company_id IS NOT NULL
AND fgj.cost_center_id IS NOT NULL
AND mgr_tbl.org_information2 IS NULL;
SELECT b.flex_value_set_id, rpad(e.flex_value_set_name, 40, ' '),
c.fin_category_id, d.flex_value
FROM fnd_segment_attribute_values a,
fnd_id_flex_segments b,
(select distinct v.fin_category_id, v.chart_of_accounts_id
from
( select distinct fgj.fin_category_id, fgj.chart_of_accounts_id
from hr_organization_information ccc_tbl,
( select organization_id, org_information2
from hr_organization_information
where org_information_context = 'Organization Name Alias'
and nvl( fnd_date.canonical_to_date( org_information3 ),
sysdate + 1 ) <= sysdate
and nvl( fnd_date.canonical_to_date( org_information4 ),
sysdate + 1 ) >= sysdate
) mgr_tbl,
hr_organization_information org,
fnd_flex_values fv1,
fnd_flex_values fv2,
(select distinct company_id, cost_center_id, fin_category_id,
chart_of_accounts_id
from fii_gl_je_summary_b) fgj
where ccc_tbl.org_information_context = 'CLASS'
and ccc_tbl.org_information1 = 'CC'
and ccc_tbl.org_information2 = 'Y'
and ccc_tbl.organization_id = mgr_tbl.organization_id (+)
and org.org_information_context = 'Company Cost Center'
and org.organization_id = ccc_tbl.organization_id
and fv1.flex_value_set_id = org.org_information2
and fv1.flex_value = org.org_information3
and fv2.flex_value_set_id = org.org_information4
and fv2.flex_value = org.org_information5
and fv1.flex_value_id = fgj.company_id
and fv2.flex_value_id = fgj.cost_center_id) v
where not exists (select fcm.child_fin_cat_id
from fii_fin_cat_mappings fcm
where fcm.child_fin_cat_id = v.fin_category_id )) c,
fnd_flex_values d,
fnd_flex_value_sets e
WHERE a.application_id = 101
AND a.id_flex_code = 'GL#'
AND a.id_flex_num = c.chart_of_accounts_id
AND a.segment_attribute_type = 'GL_ACCOUNT'
AND a.attribute_value = 'Y'
AND b.application_id = a.application_id
AND b.id_flex_code = a.id_flex_code
AND b.id_flex_num = a.id_flex_num
AND b.application_column_name = a.application_column_name
AND d.flex_value_set_id = b.flex_value_set_id
AND d.flex_value_id = c.fin_category_id
AND e.flex_value_set_id = b.flex_value_set_id;
SELECT fvs.flex_value_set_id, fvs.flex_value_set_name,
fv.flex_value_id, fv.flex_value
FROM fnd_flex_values fv,
fnd_flex_value_sets fvs,
( SELECT DISTINCT user_dim1_id flex_value_id
FROM fii_gl_ccid_dimensions
MINUS
SELECT child_value_id flex_value_id
FROM fii_full_udd1_hiers
WHERE parent_value_id = child_value_id) udd1
WHERE fv.flex_value_id = udd1.flex_value_id
AND fvs.flex_value_set_id = fv.flex_value_set_id;
SELECT fvs.flex_value_set_id, fvs.flex_value_set_name,
fv.flex_value_id, fv.flex_value
FROM fnd_flex_values fv,
fnd_flex_value_sets fvs,
( SELECT distinct user_dim2_id flex_value_id
From fii_gl_ccid_dimensions
MINUS
SELECT child_value_id flex_value_id
FROM fii_full_udd2_hiers
WHERE parent_value_id = child_value_id) udd2
WHERE fv.flex_value_id = udd2.flex_value_id
AND fvs.flex_value_set_id = fv.flex_value_set_id;
SELECT b.flex_value_set_id, rpad(e.flex_value_set_name, 40, ' '),
c.fin_category_id, d.flex_value
FROM fnd_segment_attribute_values a,
fnd_id_flex_segments b,
(select v.fin_category_id, v.chart_of_accounts_id
from
(select distinct fgj.fin_category_id,
fgj.chart_of_accounts_id
from hr_organization_information ccc_tbl,
( select organization_id, org_information2
from hr_organization_information
where org_information_context = 'Organization Name Alias'
and nvl( fnd_date.canonical_to_date( org_information3 ),
sysdate + 1 ) <= sysdate
and nvl( fnd_date.canonical_to_date( org_information4 ),
sysdate + 1 ) >= sysdate
) mgr_tbl,
hr_organization_information org,
fnd_flex_values fv1,
fnd_flex_values fv2,
(select distinct company_id, cost_center_id, fin_category_id,
chart_of_accounts_id
from fii_gl_je_summary_b) fgj,
fii_fin_cat_mappings fcm
where ccc_tbl.org_information_context = 'CLASS'
and ccc_tbl.org_information1 = 'CC'
and ccc_tbl.org_information2 = 'Y'
and ccc_tbl.organization_id = mgr_tbl.organization_id (+)
and org.org_information_context = 'Company Cost Center'
and org.organization_id = ccc_tbl.organization_id
and fv1.flex_value_set_id = org.org_information2
and fv1.flex_value = org.org_information3
and fv2.flex_value_set_id = org.org_information4
and fv2.flex_value = org.org_information5
and fv1.flex_value_id = fgj.company_id
and fv2.flex_value_id = fgj.cost_center_id
and fcm.child_fin_cat_id = fgj.fin_category_id ) v
WHERE NOT EXISTS (select fct.fin_category_id
from fii_fin_cat_type_assgns fct
where fct.fin_category_id = v.fin_category_id))c,
fnd_flex_values d,
fnd_flex_value_sets e
WHERE a.application_id = 101
AND a.id_flex_code = 'GL#'
AND a.id_flex_num = c.chart_of_accounts_id
AND a.segment_attribute_type = 'GL_ACCOUNT'
AND a.attribute_value = 'Y'
AND b.application_id = a.application_id
AND b.id_flex_code = a.id_flex_code
AND b.id_flex_num = a.id_flex_num
AND b.application_column_name = a.application_column_name
AND d.flex_value_set_id = b.flex_value_set_id
AND d.flex_value_id = c.fin_category_id
AND e.flex_value_set_id = b.flex_value_set_id;
SELECT a.lookup_code,
decode(a.lookup_type,
'FII_PSI_ENCUM_TYPES_OBLIGATION', 'Obligation',
'FII_PSI_ENCUM_TYPES_COMMITMENT', 'Commitment') lookup_type
FROM fnd_lookup_values a
WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
'FII_PSI_ENCUM_TYPES_COMMITMENT')
AND a.view_application_id = 450
AND a.language = userenv('LANG')
AND upper(a.lookup_code) not in (select upper(encumbrance_type)
from gl_encumbrance_types);
SELECT object_name
FROM sys.dba_objects
WHERE object_name IN ( 'FII_GL_AGRT_SUM_MV', 'FII_GL_BASE_MAP_MV',
'FII_GL_BASE_MV', 'FII_GL_MGMT_CCC_MV',
'FII_GL_MGMT_SUM_MV', 'FII_GL_TREND_SUM_MV')
AND object_type = 'MATERIALIZED VIEW';
SELECT object_name
FROM sys.dba_objects
WHERE object_name IN ( 'FII_AP_HATY_XB_MV', 'FII_AP_HCAT_B_MV',
'FII_AP_HCAT_IB_MV', 'FII_AP_HCAT_I_MV',
'FII_AP_HHIST_B_MV', 'FII_AP_HHIST_IB_MV',
'FII_AP_HHIST_I_MV', 'FII_AP_HLIA_IB_MV',
'FII_AP_HLIA_I_MV', 'FII_AP_HLWAG_IB_MV',
'FII_AP_INV_B_MV', 'FII_AP_IVATY_B_MV',
'FII_AP_IVATY_XB_MV','FII_AP_LIA_B_MV',
'FII_AP_LIA_IB_MV', 'FII_AP_LIA_I_MV',
'FII_AP_LIA_KPI_MV', 'FII_AP_LIWAG_IB_MV',
'FII_AP_MGT_KPI_MV', 'FII_AP_PAID_XB_MV',
'FII_AP_PAYOL_XB_MV')
AND object_type = 'MATERIALIZED VIEW';
SELECT object_name
FROM sys.dba_objects
WHERE object_name IN ( 'FII_AR_BILLING_ACT_AGRT_MV',
'FII_AR_BILLING_ACT_BASE_MV',
'FII_AR_DIMENSIONS_MV',
'FII_AR_DISPUTES_AGRT_MV',
'FII_AR_DISPUTES_BASE_MV',
'FII_AR_NET_REC_AGRT_MV',
'FII_AR_NET_REC_BASE_MV',
'FII_AR_RCT_AGING_AGRT_MV',
'FII_AR_RCT_AGING_BASE_MV',
'FII_AR_REV_SUM_MV')
AND object_type = 'MATERIALIZED VIEW';
select rpad(dimension_name, 40), dimension_short_name, dbi_enabled_flag
from fii_financial_dimensions_v
where dimension_short_name in ('FII_LOB', 'GL_FII_FIN_ITEM')
order by (decode (dimension_short_name, 'FII_LOB', 1, 'GL_FII_FIN_ITEM', 2));
select rpad(dimension_name, 40), dimension_short_name, dbi_enabled_flag
from fii_financial_dimensions_v
where dimension_short_name in ('FII_COMPANIES', 'HRI_CL_ORGCC',
'FII_USER_DEFINED_1', 'GL_FII_FIN_ITEM')
order by (decode (dimension_short_name,
'FII_COMPANIES', 1,
'HRI_CL_ORGCC', 2,
'FII_USER_DEFINED_1', 3,
'GL_FII_FIN_ITEM', 4));
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'), sysdate
INTO l_start_time, l_sys_date
FROM dual;
SELECT banner
INTO l_db_version
FROM v$version
WHERE upper(banner) like 'ORACLE%';
SELECT period_year
INTO l_year
FROM gl_periods a
WHERE a.period_set_name = l_period_set_name
AND a.period_type = l_period_type
AND a.adjustment_period_flag = 'N'
AND l_day between a.start_date and a.end_date;
SELECT start_date
INTO l_day
FROM gl_periods
WHERE period_set_name = l_period_set_name
AND period_type = l_period_type
AND period_num = 1
AND period_year = l_year - 1;
SELECT year_start_date
INTO l_min_start_date
FROM gl_periods
WHERE period_set_name = l_period_set_name
AND period_type = l_period_type
AND period_year = l_year - 1
AND period_num = 1
AND adjustment_period_flag = 'N';
fii_util.put_line('DIAGNOSIS: Please make sure that the number of weeks in your Fiscal Years are between the ranges of 50 and 54 weeks. Otherwise, the DBI Update Time Dimension program will have issues.');
select dbi_enabled_flag
into l_udd_enabled_flag
from fii_financial_dimensions_v
where dimension_short_name = 'FII_USER_DEFINED_1';
select dbi_enabled_flag
into l_udd_enabled_flag
from fii_financial_dimensions_v
where dimension_short_name = 'FII_USER_DEFINED_2';
SELECT count(*)
INTO l_cnt
FROM sys.dba_objects
WHERE object_name = 'FII_ENCUM_TYPE_MAPPINGS';
SELECT count(*)
INTO l_cnt
FROM fnd_lookup_values a,
gl_encumbrance_types b
WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
'FII_PSI_ENCUM_TYPES_COMMITMENT')
AND a.view_application_id = 450
AND a.language = userenv('LANG')
AND upper(a.lookup_code) = upper(b.encumbrance_type);
l_stmt := 'SELECT COUNT(*) FROM '|| l_mv_name;
l_stmt := 'SELECT COUNT(*) FROM '|| l_mv_name;
l_stmt := 'SELECT COUNT(*) FROM '|| l_mv_name;
SELECT max(implementation_flag)
INTO l_enabled_flag
FROM bis_obj_properties
WHERE object_name in ('FII_GL_PROFIT_AND_LOSS_PAGE', 'FII_PL_BY_MGR_PAGE')
and object_type = 'PAGE';
SELECT implementation_flag
INTO l_enabled_flag
FROM bis_obj_properties
WHERE object_name = 'FII_EXP_MGMT_PAGE_P'
and object_type = 'PAGE';
SELECT implementation_flag
INTO l_enabled_flag
FROM bis_obj_properties
WHERE object_name = 'FII_EA_EXPENSE_ANALYSIS_PAGE'
and object_type = 'PAGE';
SELECT implementation_flag
INTO l_enabled_flag
FROM bis_obj_properties
WHERE object_name = 'FII_PSI_FUNDS_MANAGEMENT_PAGE'
and object_type = 'PAGE';
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
INTO l_end_time
FROM dual;