<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Harry
	$Date: 2018/10/17 14:26
	$Revision: 1.0
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" accessControlMode="false" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    journal_num BELNR_DCFL,
                    hd.period_year GJAHR_DCFL,
                    TO_CHAR(hd.journal_date, 'YYYYMMDD') BLDAT,
                    TO_CHAR(hd.journal_date, 'YYYYMMDD') BUDAT,
                    (SELECT
                    p.description
                    FROM
                    hls_document_type p
                    WHERE
                    p.document_type = hd.document_type
                    ) BLART,
                    hd.period_num MONAT,
                    'CNY' WAERS,
                    '' KURSF,
                    '' XBLNR,
                    '' BKTXT,
                    ln.reference2 XREF1_HD,
                    '' XREF2_HD,
                    ln.line_num BUZEI_DCFL,

                    case when decode((select count(*) from hls_journal_header h
                         where h.journal_header_id = hd.reversed_journal_header_id
                            and h.journal_date < to_date('2019-01-01', 'yyyy-mm-dd')
                            and h.sap_belnr is not null),0,'N','Y') = 'Y' then

                      TO_CHAR(decode(ln.reference1,'1','11','11','1','31','21','21','31','40','50','50',40),'FM09')
                    else
                      TO_CHAR(ln.reference1, 'FM09')
                    end BSCHL,

                    NVL(ln.reference7, (
                    (SELECT
                    v.account_code
                    FROM
                    gld_accounts_vl v
                    WHERE
                    v.account_id = ln.account_id
                    ))) NEWKO,
                    '' UMSKZ,
                    (SELECT
                    v.account_code
                    FROM
                    gld_accounts_vl v
                    WHERE
                    v.account_id = ln.account_id
                    ) HKONT,
                    to_char(NVL(ln.amount_dr, -ln.amount_cr),'FM9999999999990.00') WRBTR,
                    to_char(NVL(ln.amount_dr, -ln.amount_cr),'FM9999999999990.00') DMBTR,
                    NVL(ln.reference10, 'B110') GSBER,
                    ln.reference4 ZTERM,
                    to_char(to_date(ln.reference14,'YYYYMMDD'),'YYYYMMDD') ZFBDT,
                    null ZLSPR,
                    'T' ZLSCH,
                    nvl(nvl(nvl(ln.reference3,ln.reference11),ln.reference12),hd.journal_num) ZUONR,
                    ln.line_description SGTXT,
                    ln.reference15 BVTYP,
                    '' XNEGP,
                    ln.reference5 XREF2,
                    ln.reference6 XREF3,
                    '' VBEWA,
                    '' VBUND,
                    decode((SELECT
                    v.account_code
                    FROM
                    gld_accounts_vl v
                    WHERE
                    v.account_id = ln.account_id
                    ),'61350100','21002','71990005','21002','61310100','21001','61310400','21006',null) KOSTL,
                    ln.reference12 AUFNR,
                    ln.reference8 MWSKZ,
                    (
                    CASE
                        WHEN ln.reference1 BETWEEN 21 AND 39 AND ln.reference7 IS NOT NULL
                    THEN
                        (SELECT NVL(m.vender_thaw_flag, 'N') FROM hls_bp_master m WHERE m.re_lifnr = ln.reference7 and rownum = 1)
                    ELSE
                        'E'
                    END) VENDER_THAW_FLAG,


                    decode(ln.source_table,
                      'CSH_WRITE_OFF',
                      (SELECT 'C'
                         FROM dual
                        WHERE ln.source_id =
                              (SELECT MAX(w.write_off_id)
                                 FROM csh_write_off w
                                WHERE nvl(w.reversed_flag, 'N') = 'N'
                                  AND (w.contract_id, w.cf_item) =
                                      (SELECT w1.contract_id, w1.cf_Item
                                         FROM csh_write_off w1
                                        WHERE w1.write_off_id = ln.source_id
                                          AND nvl(w.reversed_flag, 'N') = 'N'
                                          AND w1.write_off_type IN
                                              ('RECEIPT_CREDIT', 'PAYMENT_DEBT')))
                          AND NOT EXISTS
                        (SELECT 1
                                 FROM csh_write_off w, con_contract_cashflow ccc
                                WHERE w.write_Off_id = ln.source_id
                                  AND w.contract_id = ccc.contract_id
                                  AND w.cf_Item = ccc.cf_item
                                  and ccc.cf_status = 'RELEASE'
                                  and ccc.due_amount !=0
                                  AND nvl(ccc.write_off_flag, 'NOT') <> 'FULL')),
                      NULL) AS XREF1,

                     (select u.user_name from sys_user u where u.user_id = hd.created_by) as ZZADACT
                FROM
                    hls_journal_header hd,
                    hls_journal_detail ln
                WHERE
                    hd.journal_header_id = ln.journal_header_id AND
                    hd.journal_header_id = ${@journal_header_id}
                ORDER BY
                    ln.line_num
                ]]>
            </bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                BEGIN
                    UPDATE
                        hls_journal_header t
                    SET
                        t.sap_belnr = ${@sap_belnr},
                        t.sap_gjahr = ${@sap_gjahr},
                        t.sap_bukrs = ${@sap_bukrs},
                        t.re_type   = ${@re_type},
                        t.re_meg    = ${@re_meg},
                        t.post_gl_status = decode(${@re_type},'E','N','Y')
                    WHERE
                        t.journal_header_id = ${@journal_header_id};

                update
                    hls_journal_detail d
                set
                    d.sap_buzei = ${@sap_buzei}
                where
                    d.journal_header_id = ${@journal_header_id} and
                    to_char(d.line_num,'FM009') = ${@dcfl_buzei};

                END;
                ]]>
            </bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                BEGIN
                    update
                        hls_bp_master m
                    set
                        m.vender_thaw_flag = 'Y'
                    where
                        m.re_lifnr = ${@newko};
                END;
                ]]>
            </bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>