<?xml version="1.0" encoding="UTF-8"?>
<ns1:model xmlns:ns2="leaf.database.local.oracle" xmlns:ns1="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features">
    <ns1:operations>
        <ns1:operation name="query">
            <ns1:parameters>
                <ns1:parameter name="account_set_id" dataType="java.lang.String"/>
            </ns1:parameters>
            <ns1:query-sql><![CDATA[
                   
                     
                     
                     
                      select parent_account_id, account_id, account_code_desc,account_code
                      from (select null as parent_account_id,
                                   v.account_id,
                                   v.account_code,
                                   (v.account_code || '-' ||
                                   (select fd.description_text
                                       from fnd_descriptions fd
                                      where v.description_id = fd.description_id
                                        and fd.language = ${/session/@lang})) account_code_desc
                              from gld_accounts v
                             where v.account_set_id = ${/parameter/@account_set_id}
                               and not exists (select 1
                                      from gld_account_hierarchy_detail t
                                     where t.account_id = v.account_id)
                            union
                            select t.parent_account_id,
                                   v2.account_id,
                                   v2.account_code,
                                   v2.account_code || '-' ||
                                   (select fd.description_text
                                      from fnd_descriptions fd
                                     where v2.description_id = fd.description_id
                                       and fd.language = ${/session/@lang}) account_code_desc
                              from gld_account_hierarchy_detail t,
                                   gld_accounts                 v,
                                   gld_accounts                 v2
                             where t.account_set_id = ${/parameter/@account_set_id}
                               and t.parent_account_id = v.account_id(+)
                               and v2.account_id = t.account_id)
                     order by account_code
            ]]></ns1:query-sql>
        </ns1:operation>
    </ns1:operations>
</ns1:model>
