sdic_acr_invoice.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wangwei  
    $Date: 2015-11-16 下午4:30:02  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select t1.* from (SELECT
                    sai.document_number,
                    cc.contract_number,
                    (select p.virtual_con_number from prj_project p where p.project_id=cc.project_id)
                    virtual_con_number,
                    bp.bp_name,
                    bp.tax_registry_num,
                    bp.invoice_bp_address_phone_num,
                    bp.invoice_phone_number,
                    bp.invoice_bank,
                    bp.invoice_bp_bank_account,
                    --sdic_invoice_pkg.get_cf_item_desc(sai.cashflow_id) cf_item_desc,
                    sai.cf_item_desc,
                    sai.specification,
                    sai.uom,
                    sai.quantity,
                    sai.price,
                    sai.net_due_amount,
                    sai.vat_rate,
                    sai.VAT_TYPE,
                     (select vv.code_value_name from sys_code_values_v vv where
                    vv.code='ACR_INVOICE_VAT_TYPE' 
                    and vv.code_value=sai.VAT_TYPE
                    and vv.code_enabled_flag='Y'
                    and vv.code_value_enabled_flag='Y') VAT_TYPE_desc,
                    sai.vat_due_amount,
                    sai.due_amount,
                    sai.invoice_date,
                    sai.invoice_number,
                    sai.invoice_code,
                    sai.net_invoice_amount,
                    sai.vat_invoice_amount,
                    sai.vat_invoice_rate,
                    sai.invoice_amount,
                    sai.import_flag,
                    ccc.times,
                    ccc.due_date,                                              
                    sai.invoice_id,
                    (select 'Y' from dual where round((select sum(nvl(li.invoice_amount,0))
                     from sdic_acr_invoice_line li 
                     where li.invoice_id=sai.invoice_id),2)=sai.due_amount) done_flag,
                      (select sum(nvl(li.invoice_amount,0))
                     from sdic_acr_invoice_line li 
                     where li.invoice_id=sai.invoice_id) done_invoice_amount,
                     sai.invoice_kind,
                   (select sv.code_value_name from sys_code_values_v sv where sv.code='ACR510_INVOICE_KIND'
                   and sv.code_value=sai.invoice_kind
                   and sv.code_enabled_flag='Y'
                   and sv.code_value_enabled_flag='Y') invoice_kind_des,
                   sdic_invoice_pkg.get_billing_times(p_invoice_id =>sai.invoice_id,
                             p_contract_id =>sai.contract_id,
                             p_cashflow_id =>sai.cashflow_id) billing_times,
                   sdic_invoice_pkg.get_not_billing_times(p_invoice_id =>sai.invoice_id,
                             p_contract_id =>sai.contract_id,
                             p_cashflow_id =>sai.cashflow_id) not_billing_times,
                   sdic_invoice_pkg.get_billing_amount(p_invoice_id =>sai.invoice_id,
                             p_contract_id =>sai.contract_id,
                             p_cashflow_id =>sai.cashflow_id) billing_amount,
                   sdic_invoice_pkg.get_not_billing_amount(p_invoice_id =>sai.invoice_id,
                             p_contract_id =>sai.contract_id,
                             p_cashflow_id =>sai.cashflow_id) not_billing_amount
                FROM
                    sdic_acr_invoice sai,
                    con_contract cc,
                    hls_bp_master bp,
                    con_contract_cashflow ccc
                WHERE
                    sai.contract_id=cc.contract_id AND
                    sai.bp_id      =bp.bp_id AND
                    ccc.cashflow_id=sai.cashflow_id
                    and nvl(sai.closed_flag,'N')<>'Y') t1
                    #WHERE_CLAUSE#
                    order by t1.due_date,t1.contract_number desc
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
        begin
           sdic_invoice_pkg.insert_pre_query(p_company_id =>${/session/@company_id},
                             p_user_id    =>${/session/@user_id});
        end;
        ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
          declare
          v_count_bp_flag number;
          v_count_vat_flag number;
e_bp_check_error   exception;
e_vat_check_error   exception;
begin
  select count(distinct ai.bp_id) into v_count_bp_flag  
  from sdic_acr_invoice ai where ai.invoice_id in (${:@invoice_id_str}) ;
  if v_count_bp_flag>1 then 
    raise e_bp_check_error;
  end if;
  select count(distinct ai.invoice_kind) into v_count_vat_flag  
  from sdic_acr_invoice ai where ai.invoice_id in(${:@invoice_id_str}) ;
  if v_count_vat_flag>1 then 
    raise e_vat_check_error;
  end if;
  exception
  when e_bp_check_error then
   sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '只能打印同一购货单位项下的发票！',
                                                  p_created_by              => ${/session/@user_id},
                                                  p_package_name            => 'sdic_acr_invoice_pkg',
                                                  p_procedure_function_name => 'check_bp');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  when e_vat_check_error then
     sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '只能打印相同的发票类型的单据！',
                                                  p_created_by              => ${/session/@user_id},
                                                  p_package_name            => 'sdic_acr_invoice_pkg',
                                                  p_procedure_function_name => 'check_bp');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
end;
]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="import_flag" queryExpression="nvl(t1.import_flag,&apos;N&apos;)=${@import_flag}"/>
        <bm:query-field name="contract_number" queryExpression="T1.contract_number LIKE ${@contract_number}"/>
        <bm:query-field name="bp_name" queryExpression="T1.bp_name LIKE ${@bp_name}"/>
        <bm:query-field name="document_number" queryExpression="T1.document_number LIKE ${@document_number}"/>
        <bm:query-field name="invoice_number" queryExpression="T1.invoice_number LIKE ${@invoice_number}"/>
        <bm:query-field name="invoice_bp_bank_account" queryExpression="T1.invoice_bp_bank_account LIKE ${@invoice_bp_bank_account}"/>
        <bm:query-field name="invoice_date_from" queryExpression="t1.invoice_date&gt;=trunc(to_date(${@invoice_date_from},&apos;yyyy-mm-dd&apos;))"/>
        <bm:query-field name="invoice_date_to" queryExpression="trunc(t1.invoice_date)&lt;=to_date(${@invoice_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="vat_type" queryExpression="T1.vat_type = ${@vat_type}"/>
        <bm:query-field name="invoice_id" queryExpression="T1.invoice_id = ${@invoice_id}"/>
        <bm:query-field name="virtual_con_number" queryExpression="T1.virtual_con_number like ${@virtual_con_number}"/>
        <bm:query-field name="done_flag" queryExpression="nvl(t1.done_flag,&apos;N&apos;)=${@done_flag}"/>
        <bm:query-field name="due_date_from" queryExpression="t1.due_date&gt;=trunc(to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;))"/>
        <bm:query-field name="due_date_to" queryExpression="trunc(t1.due_date)&lt;=to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>