GL Chart Of Account Query

 GL Chart Of Account Query

select  ffv.flex_value FLEX_VALUES
  ,ffvv.flex_value_meaning TRANSLATED_VALUE
,ffvt.description DESCRIPTION
,decode(ffv.summary_flag,'N',null,'*') PARENT
,ffhv.hierarchy_code GROUPS
,ffv.hierarchy_level LEVELS
   ,(decode(substr(to_char(ffv.compiled_value_attributes),1,1),'N','No','Yes')
||'.'||decode(substr(to_char(ffv.compiled_value_attributes),3,1),'N','No','Yes') 
||'.'||(select acct_type_desc2
from gl_acct_typ gat
   where gat.acct_type_code = substr(to_char(ffv.compiled_value_attributes),5,1) ) 
||'.'||decode(substr(to_char(ffv.compiled_value_attributes),7,1),'N','No','Yes') 
||'.'||decode(substr(to_char(ffv.compiled_value_attributes),9,1),'N','No','Yes')) QUALIFIERS
--,fdf.descriptive_flex_context_name
,(ffv.attribute10
||'.'||ffv.attribute3
||'.'||(SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE LOCATION_ID IS NOT NULL and ORGANIZATION_ID = ffv.attribute4)
||'.'||(Select fdf.descriptive_flex_context_name from fnd_descr_flex_contexts_vl fdf where ffv.value_category = fdf.descriptive_flex_context_code)
||'.'||ffv.attribute5) DFF_VALUES
from fnd_flex_values ffv
  ,fnd_flex_values_vl ffvv
,fnd_flex_values_tl ffvt
,fnd_flex_value_sets ffvs
,fnd_id_flex_segments fifs
,fnd_id_flex_structures_vl fifst
,fnd_flex_hierarchies_vl ffhv
where 1=1
AND fifst.application_id = 101
AND fifs.application_id = fifst.application_id
AND ffvv.flex_value_set_id = ffvs.flex_value_set_id
AND fifs.id_flex_code = fifst.id_flex_code
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffv.flex_value_id = ffvv.flex_value_id
and ffv.flex_value_set_id = ffvs.flex_value_set_id
and ffvs.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
and ffv.flex_value_set_id = ffvv.flex_value_set_id
--and ffvs.flex_value_set_id = 1014899
AND FIFS.FLEX_VALUE_SET_ID = NVL(:P_FLEX_VALUE_ID,FIFS.FLEX_VALUE_SET_ID)
and fifs.id_flex_code = 'GL#'
and ffv.structured_hierarchy_level = ffhv.hierarchy_id(+)

order by ffv.flex_value

SELECT ffvv.flex_value,ffvs.flex_value_set_name,ffvv.DESCRIPTION

     INTO v_type_val

     FROM apps.fnd_flex_values_vl ffvv, apps.fnd_flex_value_sets ffvs

    WHERE 1=1 

  AND ffvv.flex_value_set_id = ffvs.flex_value_set_id;

      --AND ffvs.flex_value_set_name = <Value Set Name>;

  

---------------------------------

SELECT   ffvv.flex_value,

ffvv.DESCRIPTION,

fifst.STRUCTURE_VIEW_NAME,

         fifst.CONCATENATED_SEGMENT_DELIMITER,

         fifs.application_column_name,

         fifs.segment_name,

         fifs.segment_num,

         ffs.flex_value_set_name

  FROM   fnd_id_flex_structures_vl fifst,

         fnd_id_flex_segments fifs,

         fnd_flex_value_sets ffs,

apps.fnd_flex_values_vl ffvv

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

AND ffvv.flex_value_set_id = ffs.flex_value_set_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and FFS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID

         and fifst.ID_FLEX_CODE = 'GL#';

 

 

 

SELECT   vsvls.flex_value , vsvls.description ,vsvls.ENABLED_FLAG , vsvls.SUMMARY_FLAG , 

vsvls.COMPILED_VALUE_ATTRIBUTES

 FROM  FND_ID_FLEX_SEGMENTS_VL SGMNT , FND_FLEX_VALUES_VL  vsvls

 WHERE SGMNT.ID_FLEX_CODE =  'GL#' 

AND  SGMNT.ID_FLEX_NUM = 503

and  SGMNT.FLEX_VALUE_SET_ID = vsvls.flex_value_set_id

and  SGMNT.SEGMENT_NUM = 4

---------------

SELECT   ffvv.flex_value,

ffvv.DESCRIPTION,

fifst.STRUCTURE_VIEW_NAME,

         fifs.segment_name,

         ffs.flex_value_set_name,

    ffvv.ENABLED_FLAG, 

ffvv.SUMMARY_FLAG

  FROM   fnd_id_flex_structures_vl fifst,

         fnd_id_flex_segments fifs,

         fnd_flex_value_sets ffs,

apps.fnd_flex_values_vl ffvv

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

AND ffvv.flex_value_set_id = ffs.flex_value_set_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and FFS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID

AND FIFS.FLEX_VALUE_SET_ID = NVL(,FIFS.FLEX_VALUE_SET_ID)

         and fifst.ID_FLEX_CODE = 'GL#';

 

----------------------

SELECT   fifs.segment_name,

  FROM   fnd_id_flex_structures_vl fifst,

         fnd_id_flex_segments fifs

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and fifst.ID_FLEX_CODE = 'GL#';

 

-----------------------------

SELECT   fifs.segment_name, FLEX_VALUE_SET_ID

  FROM   fnd_id_flex_structures_vl fifst, fnd_id_flex_segments fifs

 WHERE       1 = 1

         AND fifst.application_id = 101

         AND fifs.application_id = fifst.application_id

         AND fifs.id_flex_code = fifst.id_flex_code

         and FIFS.ID_FLEX_NUM = FIFST.ID_FLEX_NUM

         and fifst.ID_FLEX_CODE = 'GL#';