<?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>