金算盘-oracle常用SQL语句
金算盘-oracle常用SQL语句
-------------------------------------------------
金算盘总账数据迁移到金蝶的临时处理方案
因为没有开发金算盘直接对接金蝶的工具,目前只能采用临时迁移数据的方案
1.将金算盘数据库附加到对应的环境里
2.临时书写sql语句获取需要的数据(如果是oracle的数据库,要借用工具[Navicat Premium])
3.将获取的数据引出到excel文件
4.再将数据导入到金蝶中
注意:目前sql语句中不包含辅助核算
-------------------------------
--所属组织
select * from organization order by strorganizationcode
--
select lngorganizationid as 组织id , strorganizationcode as 组织编码 , strorganizationname as 组织名称
, strfullname as 组织完整名称
, intlevel 组织级次 , blnisdetail as 是否末级 , strdate 创建日期 , strstartdate as 启用日期
from organization
order by strorganizationcode
-------------------------------
--凭证主表
select * from voucher
--
select lngvoucherid as 凭证主表id , lngvouchertypeid as 凭证类型id , lngsourceorganizationid as 创建组织id
, intvoucherno as 凭证号
, intyear as 年度 , bytperiod 期间 , strdate as 制单日期 , lngtemplateid as 模板未知2 , intnumber as T01附件数
, lngoperatorid as 制单人id , lngcheckerid as 审核人id ,lngpostid as 过账人id
from voucher
-------------------------------
--凭证字
select * from vouchertype
--
select lngvouchertypeid as 凭证字id , strvouchertypecode as 凭证字编号 , strvouchertypename as 凭证字名称
from vouchertype
-------------------------------
--凭证明细
select * from voucherdetail
--
select lngvoucherdetailid as 凭证子表id , lngvoucherid as 凭证主表id , lngrowid as 分录号
, lngcurrencyid as 币别id
, lngaccountid as 科目编码id
, strremark as 摘要
, intdirection 借贷方_可能 , dblamount as 本币金额 ,dblcurrencyamount as 外币金额
, dblrate as 汇率 ,dblquantity as 数量
, dblprice as 单价 , lngclassid1 as 自定义项目类型1 , lngclassid2 as 自定义项目类型2 , lngcustomerid as 客户id
, lngdepartmentid as 部门id , lngemployeeid as 职员id , lngpaymentmethodid as 结算方式id
, lngorganizationid as 单据体所属组织id , strbusinessdate as 单据体制单日期
from voucherdetail
-------------------------------
--会计科目1
select * from account order by lngaccountid
--
select lngaccountid as 科目id , straccountcode as 科目编号 , straccountname as 科目名称 , strfullname as 科目全称 , intdirection as 借1贷负1
from account
-------------------------------
--会计科目2
select * from accountex order by lngaccountid
--
select lngvouchertypeid as 凭证字id , strvouchertypecode as 凭证字编号 , strvouchertypename as 凭证字名称
from vouchertype
-------------------------------
--币别种类
select * from currencys
--
select lngcurrencyid as 币别id , strcurrencycode as 币别编号 , strcurrencyname as 币别名称
from currencys
---******************************************************************************
---完整的凭证列表(不含核算项目)
create or replace view mdf_vw_凭证列表 as
(
select lngvoucherid as T01凭证主表id , lngvouchertypeid as T01凭证类型id , lngsourceorganizationid as T01创建组织id
, intvoucherno as T01凭证号
, intyear as T01年度 , bytperiod T01期间 , strdate as T01制单日期 , lngtemplateid as T01模板未知2 , intnumber as T01附件数
, lngoperatorid as T01制单人id , lngcheckerid as T01审核人id ,lngpostid as T01过账人id
-------------------
, T02凭证子表id , T02凭证主表id , T02分录号 , T02科目编码id , T02币别id , T02借贷方1借负1贷 , T02摘要 , T02汇率
, T02本币金额 , T02本币金额_借方 , T02本币金额_贷方 , T02外币金额_借方 , T02外币金额_贷方 , T02数量_借方 , T02数量_贷方
, T02单据体所属组织id , T02单据体制单日期 , T02结算方式id
, T02自定义项目类型1 , T02自定义项目类型2 , T02客户id , T02部门id , T02职员id
-------------------
, T03凭证字id , T03凭证字编号 , T03凭证字名称
, T04组织id , T04组织编码 , T04组织名称
, T05科目id , T05科目编号 , T05科目名称
, T06币别id , T06币别编号 , T06币别名称
from voucher T01
left join
(select lngvoucherdetailid as T02凭证子表id , lngvoucherid as T02凭证主表id , lngrowid as T02分录号 , lngaccountid as T02科目编码id
, lngcurrencyid as T02币别id , strremark as T02摘要 , dblrate as T02汇率
, intdirection T02借贷方1借负1贷 , dblamount as T02本币金额
, ( case when intdirection = 1 then dblamount else 0 end ) T02本币金额_借方
, ( case when intdirection = -1 then dblamount else 0 end ) T02本币金额_贷方
, dblcurrencyamount as T02外币金额
, ( case when intdirection = 1 then dblcurrencyamount else 0 end ) T02外币金额_借方
, ( case when intdirection = -1 then dblcurrencyamount else 0 end ) T02外币金额_贷方
, dblquantity as T02数量
, ( case when intdirection = 1 then dblquantity else 0 end ) T02数量_借方
, ( case when intdirection = -1 then dblquantity else 0 end ) T02数量_贷方
, dblprice as T02单价 , lngclassid1 as T02自定义项目类型1 , lngclassid2 as T02自定义项目类型2 , lngcustomerid as T02客户id
, lngdepartmentid as T02部门id , lngemployeeid as T02职员id , lngpaymentmethodid as T02结算方式id
, lngorganizationid as T02单据体所属组织id , strbusinessdate as T02单据体制单日期
from voucherdetail ) T02
on T01.lngvoucherid =T02.T02凭证主表id
left join
(select lngvouchertypeid as T03凭证字id , strvouchertypecode as T03凭证字编号 , strvouchertypename as T03凭证字名称
from vouchertype ) T03
on T01.lngvouchertypeid =T03.T03凭证字id
left join
(select lngorganizationid as T04组织id , strorganizationcode as T04组织编码 , strorganizationname as T04组织名称 ,strfullname as T04组织完整名称
, intlevel T04组织级次 , strdate T04创建日期 , strstartdate as T04启用日期
from organization ) T04
on T01.lngsourceorganizationid =T04.T04组织id
left join
(select lngaccountid as T05科目id , straccountcode as T05科目编号 , straccountname as T05科目名称 , strfullname as T05科目全称
, intdirection as T05借1贷负1
from account ) T05
on T02.T02科目编码id =T05.T05科目id
left join
(select lngcurrencyid as T06币别id , strcurrencycode as T06币别编号 , strcurrencyname as T06币别名称
from currencys ) T06
on T02.T02币别id =T06.T06币别id
)
--------------------------------------
----------------------------------------------------------
--发现民生企业账由不同的组织合并成的,还没找到规律
select * from mdf_vw_凭证列表
where T04组织名称 = '贵州黔海建设工程有限公司'
--and T01年度 = 2021
--where T02摘要 = '1.06债权债务转移' or T02摘要 = '支付银行账户维护费'
order by T01年度 , T01期间 , T01凭证类型id , T01凭证号 , T02分录号
----------------------------------------------------------
--整理格式
select T01期间,T01制单日期,T03凭证字编号,T01凭证号,T01附件数,T02摘要, T05科目编号
,T06币别编号,T02汇率,T02本币金额_借方,T02本币金额_贷方,T02外币金额_借方,T02外币金额_贷方,T02数量_借方,T02数量_贷方
,T01制单人id,T01审核人id,T01过账人id ,T02自定义项目类型1,T02自定义项目类型2,T02客户id,T02部门id,T02职员id
from mdf_vw_凭证列表
where T04组织名称 = '贵州黔海建设工程有限公司'
--and T01年度 = 2021
--where T02摘要 = '1.06债权债务转移' or T02摘要 = '支付银行账户维护费'
order by T01年度 , T01期间 , T01凭证类型id , T01凭证号 , T02分录号
----------------------------------------------------------------------------
drop table mdf_tb_凭证列表
create table mdf_tb_凭证列表 as select * from mdf_vw_凭证列表
----------------------------------------------------------
select * from mdf_tb_凭证列表
-------------------------------------------------
