<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: LR  
    $Date: 2013-7-16 上午09:06:33  
    $Revision: 1.0  
    $Purpose: 
-->

<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT *
                FROM (Select
                          aa.invoice_apply_id,
                          ah.invoice_hd_wfl_id,
                          al.invoice_ln_wfl_id,
                          ah.contract_id,
                          ah.project_id,
                          ah.bp_id,
                          ah.invoice_kind,
                          al.cashflow_id,
                          al.cf_item,
                          ah.document_number,

                          (Select c.contract_number
                           From con_contract c
                           Where c.contract_id = ah.contract_id)              contract_number,

                          (Select c.search_term_1
                           from con_contract c
                           where c.contract_id = ah.contract_id)            pur_contract_number,

                             (Select p.project_number
                                from prj_project             p,
                                     prj_project_lease_item  ppli,
                                     con_contract_lease_item ccli,
                                     con_contract            cc
                               where p.project_id = ppli.source_project_id
                                 and ppli.project_lease_item_id =
                                     ccli.project_lease_item_id
                                 and ccli.contract_id = cc.contract_id
                                 and cc.contract_id = ah.contract_id) pur_number,

                             (Select v.code_value_name
                                from sys_code_values_v v, con_contract cc
                               where v.code = 'BUSINESS_TYPE'
                                 and v.code_value = cc.business_type
                                 and cc.contract_id = ah.contract_id) business_type,

                          (Select hb.bp_code
                           From hls_bp_master hb
                           Where hb.bp_id = ah.bp_id)                         bp_code,
                          ah.bp_name,
                          nvl(cwo.times,
                              (Select t.times
                               From con_contract_cashflow t
                               Where t.cashflow_id = al.cashflow_id))         times,

                             nvl((Select to_char(h.journal_date, 'yyyy-mm')
                                   From hls_journal_header h,
                                        hls_journal_detail l
                                  Where h.journal_header_id =
                                        l.journal_header_id
                                    And ((l.cashflow_id = al.cashflow_id and
                                        al.cf_item <> 9) or
                                        (al.ref_n04 = l.journal_line_id and
                                        al.cf_item = 9))
                                    And rownum = 1),
                                 (select to_char(t.invoice_date, 'yyyy-mm')
                                    from acr_invoice_hd t
                                   where t.invoice_hd_id = ah.invoice_hd_id)) journal_month,
                             (Select h.sbo_num
                                From hls_journal_header h,
                                     hls_journal_detail l
                               Where h.journal_header_id =
                                     l.journal_header_id
                                 And ((l.cashflow_id = al.cashflow_id and
                                     al.cf_item <> 9) or
                                     (al.ref_n04 = l.journal_line_id and
                                     al.cf_item = 9))
                                 And rownum = 1) journal_num,

                          al.tax_type_rate,
                          al.total_amount,
                          al.net_amount,
                          al.tax_amount,
                          decode((Select count(1)
                                  From csh_transaction ctr,csh_write_off cw
                                  Where ctr.transaction_id = cw.csh_transaction_id
                                    and al.cashflow_id = cw.cashflow_id
                                    and cw.reversed_flag='N'
                                    and cw.cf_item<>2
                                    and ctr.paid_byother_flag='T'),0,'F','T')        paid_byother_flag,
                          (Select v.code_value_name
                           From sys_code_values_v v
                           Where v.code = 'PAID_BYOTHER_FLAG'
                             And v.code_value =
                                 decode((Select count(1)
                                         From csh_transaction ctr,csh_write_off cw
                                         Where ctr.transaction_id = cw.csh_transaction_id
                                           and al.cashflow_id = cw.cashflow_id
                                           and cw.reversed_flag='N'
                                           and cw.cf_item<>2
                                           and ctr.paid_byother_flag='T'),0,'F','T'))        paid_byother_flag_n,
                          (case when  al.cf_item<>'9' then cwo.received_amount else
                              al.total_amount end)received_amount,
                          ah.invoice_hd_id,
                          decode(ah.invoice_kind,'0',(aa.invoice_apply_id+al.invoice_ln_wfl_id),'2',aa.invoice_apply_id)         file_id,
                          decode((Select count(1)
                               From csh_transaction ctr, csh_write_off cw
                               Where ctr.transaction_id = cw.csh_transaction_id
                              and al.cashflow_id = cw.cashflow_id
                                 and cw.reversed_flag='N'
                                 and cw.cf_item<>2
                              and nvl(ctr.guarantor_name_duty,'N')='Y'),0,'N','Y')  guarantor_name_duty,
                         (Select to_char(max(cw.write_off_date),'yyyy-mm-dd')
                           From csh_transaction ctr,csh_write_off cw
                           Where ctr.transaction_id = cw.csh_transaction_id
                             and al.cashflow_id = cw.cashflow_id
                             and cw.reversed_flag='N')  write_off_max_date,
                          (Select count(1)
                           From csh_transaction ctr, csh_write_off cw,con_contract_cashflow cco, acr_invoice_apply aaa
                          ,acr_invoice_hd_wfl ahh, acr_invoice_ln_wfl alw
                           Where ctr.transaction_id = cw.csh_transaction_id
                          and  aaa.invoice_apply_id = ahh.invoice_apply_id
                              And ahh.invoice_hd_wfl_id = alw.invoice_hd_wfl_id
                             and cco.cashflow_id = cw.cashflow_id
                             and cco.contract_id=cco.contract_id
                              and aaa.invoice_apply_id =aa.invoice_apply_id
                             And alw.cashflow_id = cco.cashflow_id
                             and cw.reversed_flag='N'
                             and ctr.paid_byother_flag='T'
                             and cw.cf_item<>2
                             and nvl(ctr.guarantor_name_duty,'N')='N')paid_byother_sum_flag,
                            (select aw.product_name from acr_invoice_batch_ln_wfl aw
                            where ah.invoice_hd_wfl_id = aw.invoice_hd_wfl_id
                             and rownum =1) product_name_n,
                           (select v.code_value_id from sys_code_values_v v
                           where
                            v.code = 'ACR510_PRODUCT_NAME'
                           And v.code_value = (select aw.product_name from acr_invoice_batch_ln_wfl aw
                            where ah.invoice_hd_wfl_id = aw.invoice_hd_wfl_id
                             and rownum =1)
                           and v.code_enabled_flag = 'Y'
                           and v.code_value_enabled_flag = 'Y') product_name_order,
                             null attachment

                      From acr_invoice_apply aa,
                          acr_invoice_hd_wfl ah,
                          acr_invoice_ln_wfl al,
                          con_contract_cashflow cwo,
                          sys_code_values_v v
                      Where aa.invoice_apply_id = ah.invoice_apply_id
                            And ah.invoice_hd_wfl_id = al.invoice_hd_wfl_id
                            And al.cashflow_id = cwo.cashflow_id(+)
                            and  v.code = 'ACR510_PRODUCT_NAME'
                           And v.code_value = al.product_name
                           and v.code_enabled_flag = 'Y'
                           and v.code_value_enabled_flag = 'Y'
                      order by product_name_order asc,cwo.contract_id desc,cwo.times asc) t1 #WHERE_CLAUSE# #ORDER_BY_CLAUSE#

                ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                    acr_invoice_wfl_pkg.acr_invoice_wfl_submit(p_invoice_apply_id   => ${@invoice_apply_id},
                                                               p_company_id         => ${/session/@company_id},
                                                               p_user_id            => ${/session/@user_id});
                end;
                ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number = ${@contract_number}"/>
        <bm:query-field name="pur_contract_number" queryExpression="t1.pur_contract_number = ${@pur_contract_number}"/>
        <bm:query-field name="pur_number" queryExpression="t1.pur_number = ${@pur_number}"/>
        <bm:query-field name="bp_code" queryExpression="t1.bp_code = ${@bp_code}"/>
        <bm:query-field name="journal_month" queryExpression="t1.journal_month = ${@journal_month}"/>
        <bm:query-field name="paid_byother_flag_n" queryExpression="t1.paid_byother_flag = ${@paid_byother_flag}"/>
        <bm:query-field name="product_name_n" queryExpression="t1.product_name_n = ${@product_name_n}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.invoice_apply_id = ${@invoice_apply_id}"/>
    </bm:data-filters>
</bm:model>
