<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        t1.contract_number,
                        t1.search_term_1,
                        t2.invoice_number,
                        (SELECT
                            bp_name
                        FROM
                            hls_bp_master
                        WHERE
                            bp_id =
                            (SELECT
                                bp_id
                            FROM
                                con_contract_bp
                            WHERE
                                bp_category = 'VENDER' AND
                                contract_id = t1.contract_id AND
                                rownum      = 1
                            )
                        ) AS vender_name,
                        t2.document_number,
                        TO_CHAR(t2.invoice_date, 'yyyy-mm-dd') AS invoice_date,
                        t2.bp_name,
                        t2.total_amount,
                        DECODE(t2.invoice_kind, '0', '专用增票', '2', '普通增票', 'RECEIPT', '收据') AS invoice_kind,
                        (SELECT
                            a.code_value_name
                        FROM
                            sys_code_values_v a
                        WHERE
                            a.code_value            = t1.delivery_cycle AND
                            a.code                  = 'ACR522_AR_DELIVERY_CYCLE' AND
                            code_value_enabled_flag = 'Y' AND
                            code_enabled_flag       = 'Y'
                        ) AS delivery_cycle,
                        (SELECT
                            hb.description
                        FROM
                            HLS_BUSINESS_TYPE hb
                        WHERE
                            t1.business_type = hb.business_type
                        ) AS business_type,
                        TO_CHAR(t2.fnc_handover_date, 'YYYY-MM-DD') AS fnc_handover_date,
                        (SELECT
                            a.description
                        FROM
                            sys_user a
                        WHERE
                            a.user_id = t2.invoice_delivery_id
                        ) AS invoice_delivery_id,
                        TO_CHAR(t2.express_date, 'YYYY-MM-DD') AS express_date,
                        (SELECT
                            a.description
                        FROM
                            sys_user a
                        WHERE
                            a.user_id = t2.express_by
                        ) AS express_by,
                        TO_CHAR(t2.logment_date, 'YYYY-MM-DD') AS logment_date,
                        t2.express_num,
                        t2.delivery_address,
                        t2.express_note,
                        -- t4.specification,
                        (
                        SELECT
                            a.times
                        FROM
                            con_contract_cashflow a
                        WHERE
                            a.cashflow_id =
                            (SELECT
                                b.cashflow_id
                            FROM
                                acr_invoice_ln b
                            WHERE
                                b.invoice_hd_id = t2.invoice_hd_id AND
                                ROWNUM          = 1
                            )
                        ) AS times,
                        t2.express_status,
                        t2.invoice_hd_id,
                        (SELECT hd.description FROM hls_division hd WHERE hd.division = t1.division
                        ) AS division_n,
                        (SELECT
                            contact_person
                        FROM
                            hls_bp_master_contact_info
                        WHERE
                            bp_id  = t1.bp_id_tenant AND
                            rownum = 1
                        ) AS recipient,
                        (SELECT
                            cell_phone
                            ||'/'
                            ||phone
                        FROM
                            hls_bp_master_contact_info
                        WHERE
                            bp_id  = t1.bp_id_tenant AND
                            rownum = 1
                        ) AS recipient_tel,
                        (SELECT
                            bal.province_id_n
                        FROM
                            hls_bp_master_address_lv bal,
                            con_contract_bp cb
                        WHERE
                            cb.bp_id         = bal.bp_id AND
                            cb.contract_id   = t1.contract_id AND
                            cb.bp_category   = 'VENDER' AND
                            bal.address_type = 'DOC_SENT_ADDRESS' AND
                            cb.enabled_flag  = 'Y' AND
                            bal.enabled_flag = 'Y' AND
                            rownum           = 1
                        ) province_id_n,
                        (SELECT
                            bal.province_id
                        FROM
                            hls_bp_master_address_lv bal,
                            con_contract_bp cb
                        WHERE
                            cb.bp_id         = bal.bp_id AND
                            cb.contract_id   = t1.contract_id AND
                            cb.bp_category   = 'VENDER' AND
                            bal.address_type = 'DOC_SENT_ADDRESS' AND
                            cb.enabled_flag  = 'Y' AND
                            bal.enabled_flag = 'Y' AND
                            rownum           = 1
                        ) province_id,
                        t1.branch_company,
                        (SELECT
                            scv.code_value_name value_name
                        FROM
                            sys_code_values_v scv
                        WHERE
                            scv.code                    = 'BRANCH_COMPANY' AND
                            scv.code_value_enabled_flag = 'Y' AND
                            scv.code_value              = t1.branch_company
                        ) branch_company_n
                    FROM
                        con_contract t1,
                        acr_invoice_hd t2
                        -- con_contract_lease_item t4
                    WHERE
                        t1.contract_id     = t2.contract_id AND
                        t2.invoice_number IS NOT NULL AND
                        t2.invoice_kind   IN ('0','2') AND
                        t2.express_status  = 'CONFIRM' AND
                        (
                            (
                                (
                                   t1.DELIVERY_CYCLE <= TRUNC(SYSDATE) - TRUNC(t1.INCEPTION_OF_LEASE)
                                )
                            )
                            OR
                            (
                                t1.DELIVERY_CYCLE = 30
                            )
                            OR
                            (
                                t1.delivery_cycle  = 99999 AND
                                t1.contract_status = 'TERMINATE'
                            )
                        )
                        AND
                        t1.delivery_cycle IN (DECODE(${@monthly}, 'Y', 30, ''), DECODE(${@quarterly}, 'Y', 90, ''), DECODE(${@semiannually}, 'Y', 180, ''), DECODE(${@yearly}, 'Y', 360, ''), DECODE(${@terminally}, 'Y', 99999, ''))
                    ) t #WHERE_CLAUSE#
                ORDER BY
                    invoice_date DESC,
                    invoice_number ASC
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:parameters>
                <bm:parameter name="invoice_hd_id" dataType="java.lang.Long" required="true"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
                update acr_invoice_hd
                set express_by = ${/session/@user_id},
                	express_status = ${@express_status},
                	express_date = sysdate,
                	express_num = ${@express_num},
                	last_updated_by = ${/session/@user_id},
                	last_update_date = sysdate
                where invoice_hd_id = ${@invoice_hd_id}
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
	                acr_invoice_pkg.import_send_info(p_batch_id	=>${@header_id},
	                								 p_user_id	=>${/session/@user_id});
                end; 
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="search_term_1" queryExpression="t.search_term_1 like ${@search_term_1}"/>
        <bm:query-field name="invoice_number" queryExpression="t.invoice_number like ${@invoice_number}"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like ${@bp_name}"/>
        <bm:query-field name="specification" queryExpression="t.specification = ${@specification}"/>
        <bm:query-field name="business_type" queryExpression="t.business_type = ${@business_type}"/>
        <bm:query-field name="province_id" queryExpression="t.province_id = ${@province_id}"/>
        <bm:query-field name="province_id_n" queryExpression="t.province_id_n like ${@province_id_n}"/>
        <bm:query-field name="branch_company" queryExpression="t.branch_company = ${@branch_company}"/>
    </bm:query-fields>
</bm:model>
