<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="HCL_ABC_INTERFACE_HD">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
               Select t1.*
                  From (Select ah.head_id,
                               ah.header_number,
                               ah.received_amount,
                               ah.due_date,
                               (Select Count(1)
                                  From ds_abc_interface_ln al
                                 Where al.head_id = ah.head_id) row_count,

                               nvl((Select to_char(Sum(nvl(ln.due_amount, 0)),
                                                  'FM999,999,999,990.00')
                                     From ds_abc_interface_ln ln
                                    Where ln.head_id = ah.head_id),
                                   0) amount_total,
                               (Select Count(1)
                                  From ds_abc_interface_ln al
                                 Where al.head_id = ah.head_id
                                   And al.withhold_status = 'BACK_SU') succe_count_total,
                               nvl((Select to_char(Sum(nvl(ln.received_amount, 0)),
                                                  'FM999,999,999,990.00')
                                     From ds_abc_interface_ln ln
                                    Where ln.head_id = ah.head_id
                                      And ln.withhold_status <> 'NEW'),
                                   0) succe_amount_total,
                               (Select Count(1)
                                  From ds_abc_interface_ln al
                                 Where al.head_id = ah.head_id
                                   And al.withhold_status = 'BACK_ER') fail_count_total,

                               nvl((Select to_char(Sum(nvl(ln.due_amount, 0) -
                                                      nvl(ln.received_amount, 0)),
                                                  'FM999,999,999,990.00')
                                     From ds_abc_interface_ln ln
                                    Where ln.head_id = ah.head_id
                                      And ln.withhold_status <> 'NEW'),
                                   0) fail_amount_total,
                               (Select Count(1)
                                  From ds_abc_interface_ln ln
                                 Where ln.head_id = ah.head_id
                                   And ln.withhold_status = 'BACK_CSH_ER') write_off_error_tota,
                               nvl((Select Sum(nvl(ln.due_amount, 0))
                                     From ds_abc_interface_ln ln
                                    Where ln.head_id = ah.head_id
                                      And ln.withhold_status = 'BACK_CSH_ER'),
                                   0) write_off_error_amount,
                               ah.created_by,
                               (Select su.description
                                  From sys_user su
                                 Where ah.created_by = su.user_id) user_name,
                               ah.status,
                               (Select sc.code_value_name
                                  From sys_code_values_v sc
                                 Where sc.code = 'DS_ABC_STATUS'
                                   And sc.code_value = ah.status) status_n,
                               ah.job_id,
                               (Select j.job_status
                                  From sch_concurrent_job j
                                 Where j.job_id = ah.job_id) job_status,
                               (Select v.code_value_name
                                  From sys_code_values_v v
                                 Where v.code = 'SCH_JOB_STATUS'
                                   And v.code_value =
                                       (Select j.job_status
                                          From sch_concurrent_job j
                                         Where j.job_id = ah.job_id)) As job_status_n
                          From ds_abc_interface_hd ah) t1 #WHERE_CLAUSE#
                 Order By t1.header_number Desc, t1.due_date Desc



                ]]></bm:query-sql>
        </bm:operation>

        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                 BEGIN

                     hls_ebank_abc_pub_pkg.main_create_write_off(p_head_id => ${@head_id},
                                                                 p_line_id => ${@line_id},
                                                                 p_user_id => ${/session/@user_id});
                 END;
             ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="head_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="HEAD_ID" prompt="HCL_ABC_INTERFACE_HD.HEAD_ID"/>
        <bm:field name="header_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="HEADER_NUMBER" prompt="HCL_ABC_INTERFACE_HD.HEADER_NUMBER"/>
        <!-- <bm:field name="file_path" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FILE_PATH" prompt="HCL_ABC_INTERFACE_HD.FILE_PATH"/> -->
        <bm:field name="due_date" databaseType="DATE" datatype="java.util.Date" physicalName="DUE_DATE" prompt="HCL_ABC_INTERFACE_HD.DUE_DATE"/>
        <bm:field name="row_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="ROW_COUNT" prompt="HCL_ABC_INTERFACE_HD.ROW_COUNT"/>
        <bm:field name="amount_total" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="AMOUNT_TOTAL" prompt="HCL_ABC_INTERFACE_HD.AMOUNT_TOTAL"/>
        <!--  <bm:field name="fail_count_total" databaseType="NUMBER" datatype="java.lang.Long" physicalName="FAIL_COUNT_TOTAL" prompt="HCL_ABC_INTERFACE_HD.FAIL_COUNT_TOTAL"/>
        <bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RECEIVED_AMOUNT" prompt="HCL_ABC_INTERFACE_HD.RECEIVED_AMOUNT"/> -->
        <bm:field name="user_name"/>
        <bm:field name="status"/>
        <bm:field name="status_n"/>
        <bm:field name="fail_count_total"/>
        <!--<bm:field name="write_off_error_total"/>-->
        <bm:field name="fail_amount_total"/>
        <bm:field name="job_id"/>
        <bm:field name="job_status"/>
        <bm:field name="job_status_n"/>
        <!--<bm:field name="write_off_error_amount"/>-->
        <bm:field name="succe_count_total"/>
        <bm:field name="succe_amount_total"/>

    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="header_number" queryExpression="t1.header_number like ${@header_number}"/>
        <bm:query-field name="due_date_from" queryExpression="t1.due_date &gt;= trunc(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;))"/>
        <bm:query-field name="due_date_to" queryExpression="trunc(t1.due_date) &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="user_id" queryExpression="t1.created_by=${@user_id}"/>
        <bm:query-field name="status" queryExpression="t1.status=${@status}"/>
    </bm:query-fields>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:primary-key>
        <bm:pk-field name="head_id"/>
    </bm:primary-key>
</bm:model>
