How I get all the children/parent of account in GL ?

Cesar Saveedra

Hello

I have the following SQL which is working fine, however I need the information at the lowest level . This SQL only provide the first child values but I have more child values.

Although I change the value to zero I still see only the first child on the hierarchy.

i.e. START WITH depth = 0

Thanks

SELECT    'FXE_I_823' key

         ,ftn.tree_code

          || REPLACE(SYS_CONNECT_BY_PATH(ftn.pk1_start_value

                                         || CHR(0)

                                         || ffvv.description

                                         ,'|')

                    ,CHR(0)

                    ,'|') tree_code

 FROM    fnd_tree_node     ftn

         ,fnd_flex_values_vl ffvv

 WHERE    1 = 1

  AND    ftn.pk1_start_value = ffvv.flex_value

  AND    ftn.tree_code = 'COA_ACCOUNT'

  AND    ffvv.value_category = 'COST CENTER'

  AND    CONNECT_BY_ISLEAF = 1

CONNECT BY PRIOR ftn.pk1_start_value = ftn.parent_pk1_value

START WITH depth = 1 -- root node is at depth zero

JohnW-Oracle

Hi Cesar,

This is not directly related to FAW but the product 'Oracle Fusion CX Sales Cloud Service' and 'Oracle Fusion General Ledger Cloud Service' have confirmed the fields CHILD_COUNT and DEPTH are are currently not available for extract using BICC for table FND_TREE_NODE.

This KM Article may help How To Find The Parent and Child Node Values In GL Account Hierarchy (Doc ID 2035391.1)

-John

Cesar Saveedra

Thanks for your help