acr_invoice_create.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: DJ
    $Date: 2013-7-4 下午5:06:06
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:fields>
        <bm:field name="record_id"/>
        <bm:field name="cashflow_id"/>
        <bm:field name="product_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PRODUCT_NAME"
                  prompt="ACR.PRODUCT_NAME"/>
        <bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PROJECT_ID"/>
        <bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="PROJECT_NUMBER" prompt="HLS.PROJECT_NUMBER"/>
        <bm:field name="project_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NAME"
                  prompt="HLS.PROJECT_NAME"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="CONTRACT_NUMBER" prompt="HLS.CONTRACT_NUMBER"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME"
                  prompt="HLS.CONTRACT_NAME"/>
        <bm:field name="invoice_kind" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="INVOICE_KIND"/>
        <bm:field name="invoice_kind_desc" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="INVOICE_KIND_DESC" prompt="ACR.INVOICE_TYPE"/>
        <bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="DUE_AMOUNT"
                  prompt="ACR.DUE_AMOUNT"/>
        <bm:field name="cf_billing_amount" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="CF_BILLING_AMOUNT" prompt="ACR.BILLING_AMOUNT"/>
        <bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="RECEIVED_AMOUNT" prompt="ACR.RECEIVED_AMOUNT"/>
        <bm:field name="billing_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="BILLING_AMOUNT"
                  prompt="ACR.THIS_BILLING_AMOUNT"/>
        <bm:field name="times" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TIMES"
                  prompt="HLS.PERIOD_NUMBER"/>
        <bm:field name="last_received_date" databaseType="DATE" datatype="java.util.Date"
                  physicalName="LAST_RECEIVED_DATE" prompt="ACR.RECEIVED_DATE"/>
        <bm:field name="cf_item" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CF_ITEM"
                  prompt="HLS.RECEIVE_PROJECT"/>
        <bm:field name="cf_item_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CF_ITEM_DESC"
                  prompt="HLS.RECEIVE_PROJECT"/>
        <bm:field name="currency" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY"
                  prompt="HLS.CURRENCY"/>
        <bm:field name="currency_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_DESC"
                  prompt="HLS.CURRENCY"/>
        <bm:field name="exchange_rate" databaseType="NUMBER" datatype="java.lang.Double" physicalName="EXCHANGE_RATE"
                  prompt="HLS.EXCHANGE_RATE"/>
        <bm:field name="exchange_rate_type" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="EXCHANGE_RATE_TYPE" prompt="HLS.EXCHANGE_RATE_TYPE"/>
        <bm:field name="exchange_rate_type_desc" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="EXCHANGE_RATE_TYPE_DESC" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
        <bm:field name="invoice_bp_tax_registry_num" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="billing_method" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="billing_way_n" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="billing_frequency_n" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="billing_object_name" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="invoice_title" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="object_taxpayer_type" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="invoice_bp_address_phone_num" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="invoice_bp_bank_account" databaseType="VARCHAR2" datatype="java.lang.String"/>

    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select t.cashflow_id,
                t.bp_name,--承租人姓名
               con.billing_method,--开票规则
               con.billing_way_n,--开票方式
               con.billing_frequency_n,--开票频率
               aic.billing_object_name,--开票对象
               t.invoice_title,--发票抬头
               aic.object_taxpayer_type,--纳税人类型
               t.invoice_bp_address_phone_num,--开票地址电话
               t.invoice_bp_bank_account,--开户行及账号
                t.record_id,
                t.billing_object,
                (select c.code_value_name
                from sys_code_values_v c
                where c.code = 'ACR510_BILL_DETAIL_METHOD'
                and c.code_value = t.billing_object) as billing_object_desc,
                t.due_amount,
                t.billing_amount,
                t.cf_billing_amount,
                t.received_amount,
                t.contract_id,
                con.contract_number,
                con.contract_name,
                con.project_id,
                con.project_number,
                con.project_name,
                t.invoice_kind,
                (select v.code_value_name
                from sys_code_values_v v
                where v.code = 'ACR510_INVOICE_KIND'
                and v.code_value = t.invoice_kind) as invoice_kind_desc,
                t.currency,
                (select c.currency_name
                from gld_currency_vl c
                where c.currency_code = t.currency) as currency_desc,
                1 as exchange_rate,
                'MANUAL' as exchange_rate_type,
                (select v.rate_type_desc
                from gld_exchange_rate_type_v v
                where v.rate_type_code = 'MANUAL') as exchange_rate_type_desc,
                cf.times,
                cf.last_received_date,
                t.cf_item,
                (select i.description
                from hls_cashflow_item i
                where i.cf_item = t.cf_item) as cf_item_desc,
                CASE
                WHEN con.division = '10' THEN
                (SELECT ci.oem_type
                FROM con_contract_lease_item ci
                WHERE ci.contract_id = con.contract_id
                AND ci.equipment_type = 'MAIN')|| nvl((SELECT ci.billing_desc
                FROM con_contract_cf_item ci
                WHERE ci.contract_id = con.contract_id
                AND ci.cf_item = t.cf_item),
                (SELECT iv.billing_desc
                FROM hls_cashflow_item_company_v iv
                WHERE iv.cf_item = t.cf_item
                AND iv.company_id = con.company_id))
                ELSE
                CASE
                WHEN t.cf_item IN (2,
                3,
                8,
                9,
                304,
                100,
                101) THEN
                (SELECT decode(con.division,
                '00',
                '挖掘机',
                '01',
                '装载机') || nvl((SELECT ci.billing_desc
                FROM con_contract_cf_item ci
                WHERE ci.contract_id = con.contract_id
                AND ci.cf_item = t.cf_item),
                (SELECT iv.billing_desc
                FROM hls_cashflow_item_company_v iv
                WHERE iv.cf_item = t.cf_item
                AND iv.company_id = con.company_id))
                FROM dual)
                ELSE
                nvl((SELECT ci.billing_desc
                FROM con_contract_cf_item ci
                WHERE ci.contract_id = con.contract_id
                AND ci.cf_item = t.cf_item),
                (SELECT iv.billing_desc
                FROM hls_cashflow_item_company_v iv
                WHERE iv.cf_item = t.cf_item
                AND iv.company_id = con.company_id))
                END
                END AS product_name,
                t.invoice_bp_tax_registry_num
                from acr_invoice_create_tmp t,
                con_contract_v con,
                con_contract_cashflow_all_v cf,
                acr_invoice_contract_v      aic
                where t.session_id = ${/session/@session_id}
                and t.contract_id = con.contract_id
                and t.cashflow_id = cf.cashflow_id
                and con.contract_id = aic.contract_id
                order by con.contract_id,cf.times,t.cf_item
                ]]>
            </bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                ACR_INVOICE_PKG.invoiceTmp_update(p_record_id => ${@record_id},
                p_invoice_kind => ${@invoice_kind},
                p_billing_amount => ${@billing_amount},
                p_product_name => ${@product_name},
                p_user_id => ${/session/@user_id});
                end;
                ]]>
            </bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                ACR_INVOICE_PKG.create_invoice(p_session_id => ${/session/@session_id},
                p_company_id => ${@company_id},
                p_group_billing_method => ${@group_billing_method},
                p_invoice_date => to_date(${@invoice_date},'yyyy-mm-dd'),
                p_accounting_date => to_date(${@accounting_date},'yyyy-mm-dd'),
                p_user_id => ${/session/@user_id},
                p_role_id => ${/session/@role_id});
                end;
                ]]>
            </bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>