[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Lists any errors GL API could have encountered when inserting data
* into FND Flex tables
* CHANGE HISTORY
* Who When What
* vnetan 08/30/2010 Created
* +=======================================================================+*/
PROCEDURE list_gl_api_errors
AS
l_n_msg_num fnd_new_messages.message_number%TYPE;
SELECT
message_number,
message_text
FROM fnd_new_messages
WHERE application_id=101
AND language_code = UserEnv('LANG')
AND message_name=cp_c_msg_name;
SELECT ffv.ROWID, ffv.*
FROM fnd_flex_values ffv
WHERE ffv.flex_value_set_id = g_value_set_id
AND ffv.flex_value = p_value;
SELECT hierarchy_id
INTO l_rollup_id
FROM fnd_flex_hierarchies ffh
WHERE ffh.flex_value_set_id = g_value_set_id
AND ffh.hierarchy_code = p_rollup_group;
SELECT
sv.Value FLEX_VALUE,
Decode(p_lang_code,
'AR' ,DESC_AR, 'CS' ,DESC_CS, 'D' ,DESC_D, 'DK' ,DESC_DK,
'E' ,DESC_E, 'EL' ,DESC_EL, 'ESA',DESC_ESA, 'F' ,DESC_F,
'FRC',DESC_FRC, 'HR' ,DESC_HR, 'HU' ,DESC_HU, 'I' ,DESC_I,
'IS' ,DESC_IS, 'IW' ,DESC_IW, 'JA' ,DESC_JA, 'KO' ,DESC_KO,
'LT' ,DESC_LT, 'N' ,DESC_N, 'NL' ,DESC_NL, 'PL' ,DESC_PL,
'PT' ,DESC_PT, 'PTB',DESC_PTB, 'RO' ,DESC_RO, 'RU' ,DESC_RU,
'S' ,DESC_S, 'SF' ,DESC_SF, 'SK' ,DESC_SK, 'SL' ,DESC_SL,
'TH' ,DESC_TH, 'TR' ,DESC_TR, 'US' ,DESC_US, 'ZHS',DESC_ZHS,
'ZHT',DESC_ZHT
) FLEX_DESC,
Decode(Upper(sv.enabled_flag),'TRUE','Y','FALSE','N',sv.enabled_flag) ENABLED_FLAG,
Decode(Upper(sv.summary_flag),'YES','Y','NO','N',sv.summary_flag) SUMMARY_FLAG,
Decode(Upper(sv.allow_budgeting),'TRUE','Y','FALSE','N',sv.allow_budgeting) ALLOW_BUDGETING,
Decode(Upper(sv.allow_posting),'TRUE','Y','FALSE','N',sv.allow_posting) ALLOW_POSTING,
Decode(Upper(sv.account_type),
'ASSET', 'A', 'LIABILITY', 'L', 'REVENUE', 'R', 'EXPENSE', 'E',
'BUDGETARY (CR)' ,'C', 'BUDGETARY (DR)', 'D', 'OWNER''S EQUITY', 'O',
sv.account_type) ACCOUNT_TYPE,
Decode(Upper(sv.reconcile),'YES','Y','NO','N',sv.reconcile) RECONCILE,
Decode(Upper(sv.third_party_ctrl_acct),'CUSTOMER', 'CUSTOMER',
'SUPPLIER', 'SUPPLIER',
'RESTRICT MANUAL JOURNALS','R',
'YES','Y','NO','N',
sv.third_party_ctrl_acct) THIRD_PARTY_CTRL_ACCT,
sv.start_date_active EFFECTIVE_FROM,
sv.end_date_active EFFECTIVE_TO,
sv.rollup_group ROLLUP_GROUP,
sv.hierarchy_level HIERARCHY_LEVEL
FROM gl_drm_segvalues_interface sv;
SELECT DISTINCT
h.Value flex_value,
h.parent_value parent_flex_value,
Decode(Upper(sv.summary_flag), 'YES','P','NO','C',sv.summary_flag) range_attribute,
Decode(Upper(svp.summary_flag), 'YES','Y','NO','N',svp.summary_flag) parent_summary
FROM
gl_drm_hierarchy_interface h,
gl_drm_segvalues_interface sv,
gl_drm_segvalues_interface svp
WHERE
h.depth > 1 AND /* 11843300 : Top Node depth is 1 and should be ignored */
h.value = sv.Value(+) AND
h.parent_value = svp.Value(+)
ORDER BY parent_flex_value;
SELECT fvq.*
FROM fnd_flex_validation_qualifiers fvq
WHERE flex_value_set_id = p_value_set_id
ORDER BY assignment_date, value_attribute_type;
SELECT
flex_value_set_id,
format_type,
maximum_size,
number_precision,
alphanumeric_allowed_flag,
uppercase_only_flag,
numeric_mode_enabled_flag,
minimum_value,
maximum_value
INTO
g_value_set_id,
l_vset_format,
l_max_length,
l_precision,
l_alpha_allowed,
l_uppercase_only,
l_zero_fill,
l_min_value,
l_max_value
FROM fnd_flex_value_sets
WHERE flex_value_set_name = g_value_set_name;
g_list_flex_vl_attr.DELETE;
g_flex_values_tbl.DELETE;
* Insert translations for Flex Value descriptions for all installed
* languages
* CHANGE HISTORY
* Who When What
* vnetan 07/31/2010 Created
* +=======================================================================+*/
PROCEDURE gl_translate_segvalues
AS
CURSOR c_installed_languages IS
SELECT l.language_code
FROM fnd_languages l
WHERE
l.installed_flag IN ('B','I') AND
l.language_code <> UserEnv('LANG');
SELECT Decode(rec_c_installed_languages.language_code,
'AR' ,DESC_AR, 'CS' ,DESC_CS, 'D' ,DESC_D, 'DK' ,DESC_DK,
'E' ,DESC_E, 'EL' ,DESC_EL, 'ESA',DESC_ESA, 'F' ,DESC_F,
'FRC',DESC_FRC, 'HR' ,DESC_HR, 'HU' ,DESC_HU, 'I' ,DESC_I,
'IS' ,DESC_IS, 'IW' ,DESC_IW, 'JA' ,DESC_JA, 'KO' ,DESC_KO,
'LT' ,DESC_LT, 'N' ,DESC_N, 'NL' ,DESC_NL, 'PL' ,DESC_PL,
'PT' ,DESC_PT, 'PTB',DESC_PTB, 'RO' ,DESC_RO, 'RU' ,DESC_RU,
'S' ,DESC_S, 'SF' ,DESC_SF, 'SK' ,DESC_SK, 'SL' ,DESC_SL,
'TH' ,DESC_TH, 'TR' ,DESC_TR, 'US' ,DESC_US, 'ZHS',DESC_ZHS,
'ZHT',DESC_ZHT)
INTO l_s_flex_desc_tl
FROM gl_drm_segvalues_interface sv
WHERE sv.value = g_flex_values_tbl(i).flex_value;
SELECT vl.flex_value_id
INTO l_n_flex_value_id
FROM
fnd_flex_values vl
WHERE
vl.flex_value_set_id = g_value_set_id AND
vl.flex_value = g_flex_values_tbl(i).flex_value;
UPDATE fnd_flex_values_tl
SET
description = l_s_flex_desc_tl,
source_lang = rec_c_installed_languages.language_code,
last_update_date = Trunc(SYSDATE)
WHERE
flex_value_id = l_n_flex_value_id AND
language = rec_c_installed_languages.language_code;
DELETE gl_drm_segvalues_interface;
DELETE gl_drm_hierarchy_interface;
DELETE gl_drm_segvalues_interface;
DELETE gl_drm_hierarchy_interface;
SELECT user_profile_option_name
FROM fnd_profile_options_tl
WHERE LANGUAGE = UserEnv('LANG')
AND profile_option_name = p_prof_option;
SELECT
FFV.flex_value TOP_NODE,
FFV.description
FROM FND_FLEX_VALUES_VL ffv
WHERE FFV.flex_value_set_id = p_value_set_id
AND FFV.summary_flag = 'Y'
AND NOT EXISTS
(SELECT 'X'
FROM FND_FLEX_VALUE_NORM_HIERARCHY fnh
WHERE FNH.flex_value_set_id = FFV.flex_value_set_id
AND FNH.range_attribute = 'P'
AND FFV.flex_value BETWEEN FNH.child_flex_value_low AND FNH.child_flex_value_high)
-- 1235002: first check if ranges are defined in the norm hierarchy table for this parent
AND EXISTS
(SELECT 'X'
FROM FND_FLEX_VALUE_NORM_HIERARCHY fnh1
WHERE FNH1.flex_value_set_id = FFV.flex_value_set_id
AND FNH1.parent_flex_value = FFV.flex_value
-- confirm that the ranges have at least one child node defined
AND EXISTS
(SELECT 'X'
FROM FND_FLEX_VALUES ffv1
WHERE FNH1.flex_value_set_id = FFV1.flex_value_set_id
AND (FFV1.summary_flag = 'N' AND FNH1.range_attribute = 'C' OR
FFV1.summary_flag = 'Y' AND FNH1.range_attribute = 'P')
AND FFV1.flex_value BETWEEN FNH1.child_flex_value_low AND FNH1.child_flex_value_high)
)
ORDER BY FFV.flex_value ASC;
SELECT
nh.parent_flex_value parent,
cv.flex_value child
FROM Fnd_Flex_Value_Norm_Hierarchy nh,
Fnd_Flex_Values cv
WHERE nh.flex_value_set_id = p_value_set_id
AND nh.flex_value_set_Id = cv.flex_value_set_id
AND cv.flex_value <> nh.parent_flex_value
AND(( nh.range_attribute = 'P' AND cv.summary_flag = 'Y')
OR( nh.range_attribute = 'C' AND cv.summary_flag = 'N'))
AND cv.flex_value BETWEEN nh.child_flex_value_low AND nh.child_flex_value_high
ORDER BY 1,2;
SELECT
ffv.flex_value_id,
ffv.flex_value,
ffv.enabled_flag,
ffv.summary_flag,
ffv.start_date_active,
ffv.end_date_active,
ffv.compiled_value_attributes,
ffh.hierarchy_code rollup_group,
ffv.hierarchy_level
FROM fnd_flex_values_vl ffv,
fnd_flex_hierarchies ffh
WHERE ffv.flex_value_set_id = p_value_set_id
AND ffv.flex_value_set_id = ffh.flex_value_set_id(+)
AND ffv.structured_hierarchy_level = ffh.hierarchy_id(+);
SELECT
segment_attribute_type,
value_attribute_type
FROM fnd_flex_validation_qualifiers
WHERE id_flex_code = cp_id_flex_code
AND id_flex_application_id = cp_id_flex_application_id
AND flex_value_set_id = cp_flex_value_set_id
ORDER BY assignment_date, value_attribute_type;
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('B','I');
SELECT flv.description
INTO l_description
FROM fnd_flex_values_tl flv
WHERE flv.flex_value_id = p_flex_value_id
AND flv.language = p_language
AND flv.source_lang = p_language;
SELECT *
INTO l_value_set_details
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_value_set_name;
SELECT c_drm_true
INTO l_natural_account
FROM fnd_flex_value_sets vs
WHERE vs.flex_value_set_id = l_value_set_details.flex_value_set_id
AND EXISTS
(SELECT 'X'
FROM fnd_flex_validation_qualifiers vq
WHERE vq.id_flex_code = 'GL#'
AND vq.id_flex_application_id = 101
AND vq.flex_value_set_id = vs.flex_value_set_id
AND vq.segment_attribute_type = 'GL_ACCOUNT');