[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jcspa.flex_segment_value Accounting_Unit,
jcspa.description Accounting_Unit_Name,
jcspa.company_name,
jcspa.organization_id,
jcspa.ent_quality,
jcspa.ent_industry,
ar.major_version || '.' || ar.minor_version || '.' ||
ar.tape_version software_version,
fcv.name,
jcspa.cnao_stand_ver
FROM JA_CN_SYSTEM_BANK_PARAMS_ALL_V jcspa,
gl_ledgers gl,
ad_releases ar,
fnd_currencies_vl fcv
WHERE ar.release_id = (SELECT max(release_id) FROM ad_releases)
AND fcv.currency_code = gl.currency_code
AND gl.ledger_id = ln_ledger_id
AND jcspa.legal_entity_id = ln_legal_entity_id
AND jcspa.flex_segment_value = lv_bsv;
SELECT ffv.flex_value sub_flex_value,
ffv.description,
ffv.parent_flex_value,
ffv.HIERARCHY_LEVEL
FROM (
SELECT v.flex_value_set_id,
h.parent_flex_value,
v.flex_value,
v.description,
v.summary_flag,
v.HIERARCHY_LEVEL
FROM fnd_flex_values_vl v,
fnd_flex_value_norm_hierarchy h,
fnd_flex_value_sets s
WHERE h.flex_value_set_id = v.flex_value_set_id
AND s.flex_value_set_id = v.flex_value_set_id
AND (((s.format_type = 'N') AND
(fnd_number.canonical_to_number(v.flex_value) BETWEEN
fnd_number.canonical_to_number(h.child_flex_value_low) AND
fnd_number.canonical_to_number(h.child_flex_value_high))) OR
((s.format_type IN ('D', 'T')) AND
(to_date(v.flex_value,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))) BETWEEN
to_date(h.child_flex_value_low,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))) AND
to_date(h.child_flex_value_high,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))))) OR
((s.format_type NOT IN ('N', 'D', 'T')) AND
(v.flex_value BETWEEN h.child_flex_value_low AND
h.child_flex_value_high)))
AND ((v.summary_flag = 'Y' AND h.range_attribute = 'P') OR
(v.summary_flag = 'N' AND h.range_attribute = 'C'))
)ffv where ffv.FLEX_VALUE_SET_ID =
(SELECT fifsv.FLEX_VALUE_SET_ID
FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = 'GL#'
AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
AND FSAV.APPLICATION_COLUMN_NAME =
fifsv.APPLICATION_COLUMN_NAME
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
AND fifsv.ID_FLEX_NUM in
(SELECT chart_of_accounts_id
FROM gl_ledgers gls
where gls.ledger_id = pn_ledger_id))
AND flex_value = pv_bsv
ORDER by flex_value;
select ffv.flex_value, ffv.description,ffv.HIERARCHY_LEVEL
into l_single_vale, l_single_desc,l_level
from fnd_flex_values_vl ffv
where ffv.FLEX_VALUE_SET_ID =
(SELECT fifsv.FLEX_VALUE_SET_ID
FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = 'GL#'
AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
AND FSAV.APPLICATION_COLUMN_NAME =
fifsv.APPLICATION_COLUMN_NAME
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
AND fifsv.ID_FLEX_NUM =
(SELECT chart_of_accounts_id
FROM gl_ledgers gls
where gls.ledger_id = pn_ledger_id))
and ffv.FLEX_VALUE = pv_bsv;
select ffh.flex_value_set_id, ffh.FLEX_VALUE, SUMMARY_FLAG
from FND_FLEX_VALUE_CHILDREN_V ffh
where ffh.flex_value_set_id = c_valueset_id
and ffh.PARENT_FLEX_VALUE = c_bsv
order by ffh.FLEX_VALUE;
SELECT fifsv.FLEX_VALUE_SET_ID
into l_flex_valueset_id
FROM FND_ID_FLEX_SEGMENTS_VL fifsv,
FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = 'GL#'
AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
AND fifsv.ID_FLEX_NUM in
(SELECT chart_of_accounts_id
FROM gl_ledgers gls
where gls.ledger_id = pn_ledger_id);
SELECT SUMMARY_FLAG
INTO lv_summary
FROM FND_FLEX_VALUE_SETS ffs, FND_FLEX_VALUES_VL ffv
WHERE ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
AND ffv.FLEX_VALUE_SET_ID = l_flex_valueset_id
AND ffv.FLEX_VALUE = l_bsv_temp;
SELECT DISTINCT CURRENCY_CODE, Description CURRENCY_NAME, Description_TL CURRENCY_ENG_NAME
FROM JA_CN_SI_CURRENCIES_GT;
SELECT fcv.currency_code, fcv.name currency_name
FROM fnd_currencies_vl fcv, GL_JE_LINES gjl, GL_JE_HEADERS gjh
WHERE fcv.currency_code = gjh.CURRENCY_CODE
AND gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = pn_ledger_id
AND gjh.period_name IN
(SELECT period_name
FROM gl_periods
WHERE PERIOD_SET_NAME =
(SELECT PERIOD_SET_NAME
FROM gl_ledgers
WHERE ledger_id = pn_ledger_id
AND PERIOD_YEAR = pn_accounting_year)
)
AND ja_cn_utility.get_balancing_segment(gjl.code_combination_id) =
pv_bsv;
SELECT fct.name
into l_currency_en
FROM FND_CURRENCIES_TL fct
WHERE fct.currency_code = v_row.currency_code
and language = 'US';
INSERT INTO JA_CN_SI_CURRENCIES_GT
(CURRENCY_CODE, Description, Description_tl)
VALUES
(v_row.currency_code, v_row.currency_name, l_currency_en);
SELECT GP.PERIOD_YEAR, GP.PERIOD_NUM, GP.START_DATE, GP.END_DATE
FROM GL_PERIODS GP, GL_LEDGERS GLD
WHERE GP.PERIOD_YEAR = ln_accounting_year
AND GP.PERIOD_SET_NAME = GLD.PERIOD_SET_NAME
AND GP.PERIOD_TYPE= GLD.ACCOUNTED_PERIOD_TYPE
AND GLD.LEDGER_ID = ln_ledger_id;
select accounting_struct_id, ACCOUNTING_STRUCTURE_FLEX_NUM
from ja_cn_system_bank_params_all jcs
WHERE jcs.ledger_id = pn_ledger_id --jcs.flex_vale_segment=pv_bsv
AND rownum=1
ORDER BY flex_segment_value;
select fnd_flex_ext.get_segs('JA',
'ACCT',
l_code.ACCOUNTING_STRUCTURE_FLEX_NUM,
l_code.ACCOUNTING_STRUCT_ID)
into l_rule
from dual;
select b.flex_value,
t.description,
NULL acc_level,
(select FLV.MEANING
from FND_LOOKUP_VALUES flv
where flv.lookup_type = 'ACCOUNT_TYPE'
and flv.view_APPLICATION_ID = 0
and flv.security_group_id = 0
and language = userenv('LANG')
and flv.lookup_code =
(substr(COMPILED_VALUE_ATTRIBUTES,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2) + 1,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 3) - 1 -
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2)))) acc_type,
(select FLV.MEANING
from FND_LOOKUP_VALUES flv
where flv.lookup_type = 'JA_CN_DEBIT_CREDIT'
and flv.view_APPLICATION_ID = 0
and flv.security_group_id = 0
and language = userenv('LANG')
and flv.lookup_code = (substr(COMPILED_VALUE_ATTRIBUTES,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 7) + 1))) balance_side,
to_char(nvl(b.START_DATE_ACTIVE,b.creation_date),'YYYYMMDD') START_DATE_ACTIVE,to_char(b.end_date_active,'YYYYMMDD') end_date_active
from FND_FLEX_VALUES_TL T, FND_FLEX_VALUES B
where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
and T.LANGUAGE = userenv('LANG')
and substr(COMPILED_VALUE_ATTRIBUTES,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5) + 1,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6) - 1 -
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5)) = 'I'
and b.FLEX_VALUE_SET_ID = c_valueset_id;
SELECT fifsv.FLEX_VALUE_SET_ID
into l_valueset_id
FROM FND_ID_FLEX_SEGMENTS_VL fifsv, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = 'GL#'
AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND fifsv.ID_FLEX_NUM in
(SELECT chart_of_accounts_id
FROM gl_ledgers gls
where gls.ledger_id = pn_ledger_id);
select count(1)
into l_parent_count
from FND_FLEX_VALUE_CHILDREN_V ffvc
where ffvc.flex_value_set_id = l_valueset_id
and ffvc.flex_value = l_inner.flex_value;
select b.flex_value,COMPILED_VALUE_ATTRIBUTES,
t.description,
NULL acc_level,
(select FLV.MEANING
from FND_LOOKUP_VALUES flv
where flv.lookup_type = 'ACCOUNT_TYPE'
and flv.view_APPLICATION_ID = 0
and flv.security_group_id = 0
and language = userenv('LANG')
and flv.lookup_code =
(substr(COMPILED_VALUE_ATTRIBUTES,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2) + 1,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 3) - 1 -
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2)))) acc_type,
(select FLV.MEANING
from FND_LOOKUP_VALUES flv
where flv.lookup_type = 'JA_CN_UOM'
and flv.view_APPLICATION_ID = 0
and flv.security_group_id = 0
and language = userenv('LANG')
and flv.lookup_code =
(substr(COMPILED_VALUE_ATTRIBUTES,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6) + 1,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 7) - 1 -
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6)))) account_unit,
(select FLV.MEANING
from FND_LOOKUP_VALUES flv
where flv.lookup_type = 'JA_CN_DEBIT_CREDIT'
and flv.view_APPLICATION_ID = 0
and flv.security_group_id = 0
and language = userenv('LANG')
and flv.lookup_code = (substr(COMPILED_VALUE_ATTRIBUTES,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 7) + 1))) balance_side,
to_char(nvl(b.START_DATE_ACTIVE,b.creation_date),'YYYYMMDD') START_DATE_ACTIVE,to_char(b.end_date_active,'YYYYMMDD') end_date_active
from FND_FLEX_VALUES_TL T, FND_FLEX_VALUES B
where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
and T.LANGUAGE = userenv('LANG')
and substr(COMPILED_VALUE_ATTRIBUTES,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5) + 1,
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6) - 1 -
instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5)) = 'E'
and b.FLEX_VALUE_SET_ID = c_flex_id;
SELECT fifsv.FLEX_VALUE_SET_ID
into l_valueset_id
FROM FND_ID_FLEX_SEGMENTS_VL fifsv, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = 'GL#'
AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND fifsv.ID_FLEX_NUM in
(SELECT chart_of_accounts_id
FROM gl_ledgers gls
where gls.ledger_id = pn_ledger_id);
select count(1)
into l_parent_count
from FND_FLEX_VALUE_CHILDREN_V ffvc
where ffvc.flex_value_set_id = l_valueset_id
and ffvc.flex_value = l_extra.flex_value;
select count(1)
into l_count
from (SELECT v.flex_value_set_id,
h.parent_flex_value,
v.flex_value,
v.description,
v.summary_flag
FROM fnd_flex_values_vl v,
fnd_flex_value_norm_hierarchy h,
fnd_flex_value_sets s
WHERE h.flex_value_set_id = v.flex_value_set_id
AND v.ENABLED_FLAG = 'Y'
AND s.flex_value_set_id = v.flex_value_set_id
AND (((s.format_type = 'N') AND
(fnd_number.canonical_to_number(v.flex_value) BETWEEN
fnd_number.canonical_to_number(h.child_flex_value_low) AND
fnd_number.canonical_to_number(h.child_flex_value_high))) OR
((s.format_type IN ('D', 'T')) AND
(to_date(v.flex_value,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))) BETWEEN
to_date(h.child_flex_value_low,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))) AND
to_date(h.child_flex_value_high,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))))) OR
((s.format_type NOT IN ('N', 'D', 'T')) AND
(v.flex_value BETWEEN h.child_flex_value_low AND
h.child_flex_value_high)))
AND ((v.summary_flag = 'Y' AND h.range_attribute = 'P') OR
(v.summary_flag = 'N' AND h.range_attribute = 'C'))) ffvc
where ffvc.flex_value_set_id = p_valueset
and ffvc.flex_value = l_temp;
select ffvc.parent_flex_value
into l_temp2
from (SELECT v.flex_value_set_id,
h.parent_flex_value,
v.flex_value,
v.description,
v.summary_flag
FROM fnd_flex_values_vl v,
fnd_flex_value_norm_hierarchy h,
fnd_flex_value_sets s
WHERE h.flex_value_set_id = v.flex_value_set_id
AND v.ENABLED_FLAG = 'Y'
AND s.flex_value_set_id = v.flex_value_set_id
AND (((s.format_type = 'N') AND
(fnd_number.canonical_to_number(v.flex_value) BETWEEN
fnd_number.canonical_to_number(h.child_flex_value_low) AND
fnd_number.canonical_to_number(h.child_flex_value_high))) OR
((s.format_type IN ('D', 'T')) AND
(to_date(v.flex_value,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))) BETWEEN
to_date(h.child_flex_value_low,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))) AND
to_date(h.child_flex_value_high,
(decode(s.maximum_size,
5,
'HH24:MI',
8,
'HH24:MI:SS',
9,
'DD-MON-RR',
11,
'DD-MON-YYYY',
15,
'DD-MON-RR HH24:MI',
17,
'DD-MON-YYYY HH24:MI',
18,
'DD-MON-RR HH24:MI:SS',
20,
'DD-MON-YYYY HH24:MI:SS'))))) OR
((s.format_type NOT IN ('N', 'D', 'T')) AND
(v.flex_value BETWEEN h.child_flex_value_low AND
h.child_flex_value_high)))
AND ((v.summary_flag = 'Y' AND h.range_attribute = 'P') OR
(v.summary_flag = 'N' AND h.range_attribute = 'C'))) ffvc
where ffvc.flex_value_set_id = p_valueset
and ffvc.flex_value = l_temp;