--添加通过自定义档案列表编码及档案编码查询主键

select bd_defdoc.pk_defdoc as defdoc --查询限额类费用类型主键

  from bd_defdoc, bd_defdoclist

 where bd_defdoc.pk_defdoclist = bd_defdoclist.pk_defdoclist

   and bd_defdoclist.code = 'F003'--费用类型自定义档案列表编码

   and bd_defdoc.code = '';--限额类费类型编码

--根据单据车辆名称主键查该车辆是否控制限额

 select (case

          when (bd_defdoc.shortname = '实报实销') then  --不限额车辆简称标识

           'false'

          else

           'true'

        end) as islimitcar

   from bd_defdoc, bd_defdoclist

  where nvl(bd_defdoc.dr, 0) = 0

    and nvl(bd_defdoclist.dr, 0) = 0

    and bd_defdoc.pk_defdoclist = bd_defdoclist.pk_defdoclist

    and bd_defdoclist.code = 'F057' --车辆信息自定义档案

    and bd_defdoc.pk_defdoc = '1001H210000000HHSNQY' ; --car_name对应的pk, select name from bd_defdoc  where pk_defdoc = '1001H210000000HHSNQY';

 --查询会计月

 select yearmth from bd_accperiodmonth where pk_accperiodmonth = '1001H2100000004ZIBPZ'  ;   --period

--车辆本期可报销金额N+1

  select sum(erm_car_limit_b.limit_e) as car_limit_e,

         sum(erm_car_limit_b.limit_l) as car_limit_l

          from erm_car_limit_b, erm_car_limit

         where nvl(erm_car_limit_b.dr, 0) = 0

           and nvl(erm_car_limit.dr, 0) = 0

           and erm_car_limit_b.pk_car_limit = erm_car_limit.pk_car_limit

           and erm_car_limit.kjni = ''

           and erm_car_limit.car = '1001H210000000HHSNQY'

           and erm_car_limit_b.kjy <= 7;  --month=6，b.kjy <= 7;这样子处理在12月份的时候即使是13也不用做特殊处理，因为也是统计到12个月的金额

-- 查询本月车辆限额量

select erm_car_limit_b.limit_e as car_limit_e,

       erm_car_limit_b.limit_l as car_limit_l

  from erm_car_limit_b, erm_car_limit

 where nvl(erm_car_limit_b.dr, 0) = 0

   and nvl(erm_car_limit.dr, 0) = 0

   and erm_car_limit_b.pk_car_limit = erm_car_limit.pk_car_limit

   and erm_car_limit.kjni = ''

   and erm_car_limit.car = '1001H210000000HHSNQY'    --PK_Car=1001H210000000HHSNQY

   and erm_car_limit_b.kjy = ''

--限额已执行量

SELECT nvl(SUM(case

                 when er_busitem.defitem12 = '1001H21000000042XVC6' then

                  0

                 else

                  er_busitem.amount

               end),

           0) amount

  from er_busitem

  LEFT outer JOIN er_bxzb

    ON er_bxzb.pk_jkbx = er_busitem.pk_jkbx

  LEFT outer JOIN bd_accperiodmonth mon

    ON mon.pk_accperiodmonth = er_busitem.DEFITEM16

 where er_busitem.tablecode = 'arap_bxbusitem'

 --  AND er_bxzb.djzt != 0

   AND er_bxzb.djzt != -1 --不包含作废数据

   AND er_busitem.pk_jkbx != 'null'

   AND er_busitem.defitem24 = '1001H210000000HHSNQY'   --PK_Car=1001H210000000HHSNQY

   AND mon.yearmth LIKE '2016%'  --YEAR=2016

   AND er_busitem.dr = 0

 --  AND er_bxzb.djbh != '" + djbh + "';   --修改增加如果单据存在单据编号，则表示是已保存的单据，计算执行量时扣去本带锯的执行量（本单据执行量在表体行字段扣除）

--select def.name from bd_defdoc def where def.pk_defdoc = '1001H21000000042XVC6';

--er_bxzb

--实体  md_class

select * from md_class where defaulttablename ='er_bxzb'

--实体属性 md_property

select * from md_property where classid ='45a846ce-5f39-4075-8cb9-90de50517af4'and displayname ='单据状态'

--枚举    id对应md_property中的datatype

select * from md_enumvalue where id ='aed2a08a-03a0-4db7-9ab1-c30e1dc1ca17'