VIEW: APPS.JL_CO_GL_ACCOUNTS_V

Source

View Text - Preformatted

SELECT b.set_of_books_id set_of_books_id, b.chart_of_accounts_id chart_of_accounts_id, s.flex_value_set_id flex_value_set_id, v.flex_value account, vt.description description, substr(v.compiled_value_attributes, decode(instr(v.compiled_value_attributes,'A'),0, decode(instr(v.compiled_value_attributes,'E'),0, decode(instr(v.compiled_value_attributes,'L'),0, decode(instr(v.compiled_value_attributes,'O'),0, decode(instr(v.compiled_value_attributes,'R'),0,0, instr(v.compiled_value_attributes,'R')), instr(v.compiled_value_attributes,'O')), instr(v.compiled_value_attributes,'L')), instr(v.compiled_value_attributes,'E')), instr(v.compiled_value_attributes,'A')) ,1) account_type, DECODE(LENGTH(v.flex_value),1,1,2,2,4,3,4) level_num, SUBSTR(v.flex_value,1,1) level1, SUBSTR(v.flex_value,2,1) level2, SUBSTR(v.flex_value,3,2) level3, SUBSTR(v.flex_value,5,LENGTH(v.flex_value)-4) level4, SUBSTR(v.flex_value,1,2) level_account2, SUBSTR(v.flex_value,1,4) level_account3 FROM gl_sets_of_books b, fnd_flex_values v, fnd_flex_values_tl vt, fnd_id_flex_segments s WHERE v.flex_value_set_id = s.flex_value_set_id AND v.flex_value_id = vt.flex_value_id AND vt.language = 'US' AND v.enabled_flag = 'Y' AND (s.application_id, s.id_flex_code, s.id_flex_num, s.application_column_name) = (SELECT application_id, id_flex_code, id_flex_num, application_column_name FROM fnd_segment_attribute_values a WHERE id_flex_code = 'GL#' AND segment_attribute_type = 'GL_ACCOUNT' AND attribute_value = 'Y' AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name = 'SQLGL') AND id_flex_num = b.chart_of_accounts_id)

View Text - HTML Formatted

SELECT B.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, B.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
, S.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID
, V.FLEX_VALUE ACCOUNT
, VT.DESCRIPTION DESCRIPTION
, SUBSTR(V.COMPILED_VALUE_ATTRIBUTES
, DECODE(INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'A')
, 0
, DECODE(INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'E')
, 0
, DECODE(INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'L')
, 0
, DECODE(INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'O')
, 0
, DECODE(INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'R')
, 0
, 0
, INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'R'))
, INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'O'))
, INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'L'))
, INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'E'))
, INSTR(V.COMPILED_VALUE_ATTRIBUTES
, 'A'))
, 1) ACCOUNT_TYPE
, DECODE(LENGTH(V.FLEX_VALUE)
, 1
, 1
, 2
, 2
, 4
, 3
, 4) LEVEL_NUM
, SUBSTR(V.FLEX_VALUE
, 1
, 1) LEVEL1
, SUBSTR(V.FLEX_VALUE
, 2
, 1) LEVEL2
, SUBSTR(V.FLEX_VALUE
, 3
, 2) LEVEL3
, SUBSTR(V.FLEX_VALUE
, 5
, LENGTH(V.FLEX_VALUE)-4) LEVEL4
, SUBSTR(V.FLEX_VALUE
, 1
, 2) LEVEL_ACCOUNT2
, SUBSTR(V.FLEX_VALUE
, 1
, 4) LEVEL_ACCOUNT3
FROM GL_SETS_OF_BOOKS B
, FND_FLEX_VALUES V
, FND_FLEX_VALUES_TL VT
, FND_ID_FLEX_SEGMENTS S
WHERE V.FLEX_VALUE_SET_ID = S.FLEX_VALUE_SET_ID
AND V.FLEX_VALUE_ID = VT.FLEX_VALUE_ID
AND VT.LANGUAGE = 'US'
AND V.ENABLED_FLAG = 'Y'
AND (S.APPLICATION_ID
, S.ID_FLEX_CODE
, S.ID_FLEX_NUM
, S.APPLICATION_COLUMN_NAME) = (SELECT APPLICATION_ID
, ID_FLEX_CODE
, ID_FLEX_NUM
, APPLICATION_COLUMN_NAME
FROM FND_SEGMENT_ATTRIBUTE_VALUES A
WHERE ID_FLEX_CODE = 'GL#'
AND SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
AND ATTRIBUTE_VALUE = 'Y'
AND APPLICATION_ID = (SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'SQLGL')
AND ID_FLEX_NUM = B.CHART_OF_ACCOUNTS_ID)