--添加通过自定义档案列表编码及档案编码查询主键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 = '0040';--限额类费类型编码--根据单据车辆名称主键查该车辆是否控制限额 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 = '2016' 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 = '2016' and erm_car_limit.car = '1001H210000000HHSNQY' --PK_Car=1001H210000000HHSNQY and erm_car_limit_b.kjy = '6'--限额已执行量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_classselect * from md_class where defaulttablename ='er_bxzb'--实体属性 md_propertyselect * from md_property where classid ='45a846ce-5f39-4075-8cb9-90de50517af4'and displayname ='单据状态'--枚举 id对应md_property中的datatypeselect * from md_enumvalue where id ='aed2a08a-03a0-4db7-9ab1-c30e1dc1ca17'