K3Cloud 存货收发存汇总表二开优化

2023-12-05 03:52

本文主要是介绍K3Cloud 存货收发存汇总表二开优化,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

业务背景

  1. 相较于EAS或者苍穹,老的存货收发汇总表,不能显示按类型区分的出以及入数据,故按照EAS的样式对其进行优化。
  2. 此业务场景为固定税率,可更具现场实际情况确定。

示例代码

create procedure [dbo].[SP_hsd_sfchz] (
@datefrom datetime,@dateto datetime
) as
BEGIN

Set nocount on

CREATE TABLE #KD_SFC ([公司编码]           varchar(60)    null, [公司名称]           varchar(60)    null, [分组编码]           varchar(60)    null, [分组名称]           varchar(60)    null, [年]           varchar(60)    null, [月]           varchar(60)    null,[存货类别产品码]           varchar(60)    null, [产存货类别名称]           varchar(60)    null,[产品代码]           varchar(1000)    null, [产品名称]           varchar(100)    null,[规格]           varchar(150)    null, [单位编码]           varchar(60)    null,    [单位名称]           varchar(60)    null,             [换算]       decimal(18, 6) default 0,        [上月结存数量]   decimal(18, 6) default 0,       [上月结存不含税金额]   decimal(18, 6) default 0,           [上月结存含税金额]            decimal(18, 6) default 0,       [本期采购入库数量]   decimal(18, 6) default 0,[本期采购入库税率]   decimal(18, 6) default 0,                [本期采购入库不含税金额]   decimal(18, 6) default 0,           [本期采购入库含税金额]            decimal(18, 6) default 0,       [本期生产入库数量]   decimal(18, 6) default 0,        [本期生产入库不含税金额]   decimal(18, 6) default 0,           [本期生产入库含税金额]            decimal(18, 6) default 0,     [本期其他入库数量]   decimal(18, 6) default 0,        [本期其他入库不含税金额]   decimal(18, 6) default 0,           [本期其他入库含税金额]            decimal(18, 6) default 0,        [本期其他类型入库数量]   decimal(18, 6) default 0,        [本期其他类型入库不含税金额]   decimal(18, 6) default 0,           [本期其他类型入库含税金额]            decimal(18, 6) default 0,       [本期其入库数量合计]   decimal(18, 6) default 0,        [本期入库不含税金额合计]   decimal(18, 6) default 0,           [本期入库含税金额合计]            decimal(18, 6) default 0,       [本期销售出库数量]   decimal(18, 6) default 0,[本期销售出库税率]   decimal(18, 6) default 0,                [本期销售出库不含税金额]   decimal(18, 6) default 0,           [本期销售出库含税金额]            decimal(18, 6) default 0,[本期销售出库成本]            decimal(18, 6) default 0,[本期领料计划数量]   decimal(18, 6) default 0,            [本期领料出库数量]   decimal(18, 6) default 0, [本期领料计划含税金额]            decimal(18, 6) default 0,        [本期领料出库不含税金额]   decimal(18, 6) default 0,           [本期领料出库含税金额]            decimal(18, 6) default 0,     [本期其他出库数量]   decimal(18, 6) default 0,[数量偏差]   decimal(18, 6) default 0, [金额偏差]   decimal(18, 6) default 0,                [本期其他出库不含税金额]   decimal(18, 6) default 0,           [本期其他出库含税金额]            decimal(18, 6) default 0,        [本期其他类型出库数量]   decimal(18, 6) default 0,        [本期其他类型出库不含税金额]   decimal(18, 6) default 0,           [本期其他类型出库含税金额]            decimal(18, 6) default 0,       [本期出库数量合计]   decimal(18, 6) default 0,        [本期出库不含税金额合计]   decimal(18, 6) default 0,           [本期出库含税金额合计]            decimal(18, 6) default 0, [期末结存数量]   decimal(18, 6) default 0,        [期末含税单价]   decimal(18, 6) default 0,        [期末结存不含税金额]   decimal(18, 6) default 0,           [期末结存含税金额]            decimal(18, 6) default 0,                                          )
      CREATE TABLE #KD_SF ([公司编码]           varchar(100)    null, [公司名称]           varchar(100)    null, [存货类别产品码]           varchar(100)    null, [产存货类别名称]           varchar(100)    null,[产品代码]           varchar(100)    null, [产品名称]           varchar(100)    null,[规格]           varchar(150)    null, [单位编码]           varchar(100)    null,    [单位名称]           varchar(100)    null,  [本期采购入库数量]   decimal(18, 6) default 0,                   [本期采购入库税率]   decimal(18, 6) default 0,                [本期采购入库不含税金额]   decimal(18, 6) default 0,           [本期采购入库含税金额]            decimal(18, 6) default 0,       [本期生产入库数量]   decimal(18, 6) default 0,        [本期生产入库不含税金额]   decimal(18, 6) default 0,           [本期生产入库含税金额]            decimal(18, 6) default 0,     [本期其他入库数量]   decimal(18, 6) default 0,        [本期其他入库不含税金额]   decimal(18, 6) default 0,           [本期其他入库含税金额]            decimal(18, 6) default 0,          [本期销售出库数量]   decimal(18, 6) default 0,[本期销售出库税率]   decimal(18, 6) default 0,                [本期销售出库不含税金额]   decimal(18, 6) default 0,           [本期销售出库含税金额]            decimal(18, 6) default 0,[本期销售出库成本]            decimal(18, 6) default 0,[本期领料计划数量]   decimal(18, 6) default 0,            [本期领料出库数量]   decimal(18, 6) default 0, [本期领料计划含税金额]            decimal(18, 6) default 0,        [本期领料出库不含税金额]   decimal(18, 6) default 0,           [本期领料出库含税金额]            decimal(18, 6) default 0,     [本期其他出库数量]   decimal(18, 6) default 0,      [本期其他出库不含税金额]   decimal(18, 6) default 0,           [本期其他出库含税金额]            decimal(18, 6) default 0                                                         )

– 期初计算
insert into #KD_SFC([公司编码],[公司名称],[存货类别产品码],[产存货类别名称],[分组编码],[分组名称],[单位编码],[单位名称],[年],[月],[产品代码], [产品名称],[规格],[上月结存数量],[上月结存不含税金额],[本期其入库数量合计],[本期入库含税金额合计],[本期出库数量合计],[本期出库不含税金额合计])
select gs.fnumber,gsa.fname,fz.FNUMBER fzno,fza.fname fzname,fzd.FNUMBER fzano,fze.fname fzaname,unit.fnumber,unita.fname,cha.fyear year,cha.FPERIOD mon ,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh ,sum(ch.fqty) qty,sum(ch.FAMOUNT) amount,sum(ch.FCurrentInQty) rkqty,sum(ch.FCurrentInAmount) rkamount,sum(ch.FOutStockQty) ckqty,sum(ch. FCurrentOutAmount) ckamount
from T_HS_STOCKDIMENSION chb left join t_hs_balance_h ch on chb.FENTRYID=ch.FDIMEENTRYID
left join T_HS_OUTACCTG cha on cha.fid=ch.fid left join T_BD_Material_L wl on chb.FMASTERID=wl.FMATERIALID
left join T_BD_Material wla on chb.FMASTERID=wla.FMATERIALID left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_BD_MATERIALGROUP fzd on substring(wla.fnumber,0,6)=fzd.fnumber left join T_BD_MATERIALGROUP_L fze on fzd.FID=fze.fid
left join T_BD_MATERIALBASE wld on wld.FENTRYID=wl.FPKID left join t_BD_Stock kf on kf.FMASTERID=chb.FSTOCKID
left join T_BD_UNIT unit on wld.FBASEUNITID=unit.FUNITID left join T_BD_UNIT_l unita on wld.FBaseUnitId=unita.FUNITID
left join T_HS_CALDIMENSIONS org on cha.FDIMENSIONID=org.FDIMENSIONID left join T_ORG_ORGANIZATIONS gs on gs.FORGID=org.FFINORGID
left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=org.FFINORGID and gsa.FNAME like ‘%华世%’
where cha.fyear=str(year(‘2023-09-01’)) and cha.FPERIOD=str(month(‘2023-09-01’)) and org.FFINORGID=1 and gsa.FNAME is not null and ch.FEndInitKey=0
group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,fzd.FNUMBER,fze.fname,unit.fnumber,unita.fname,cha.fyear ,cha.FPERIOD ,wla.fnumber ,wl.fname,wl.FSPECIFICATION

UNion all

select gs.fnumber,gsa.fname,fz.FNUMBER fzno,fza.fname fzname,fzd.FNUMBER fzano,fze.fname fzaname,unit.fnumber,unita.fname,cha.fyear year,cha.FPERIOD mon ,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh ,sum(ch.fqty) qty,sum(ch.FAMOUNT) amount,sum(ch.FCurrentInQty) rkqty,sum(ch.FCurrentInAmount) rkamount,sum(ch.FOutStockQty) ckqty,sum(ch. FCurrentOutAmount) ckamount
from T_HS_STOCKDIMENSION chb left join t_hs_balance ch on chb.FENTRYID=ch.FDIMEENTRYID
left join T_HS_OUTACCTG cha on cha.fid=ch.fid left join T_BD_Material_L wl on chb.FMASTERID=wl.FMATERIALID left join T_BD_Material wla on chb.FMASTERID=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_BD_MATERIALGROUP fzd on substring(wla.fnumber,0,6)=fzd.fnumber left join T_BD_MATERIALGROUP_L fze on fzd.FID=fze.fid
left join T_BD_MATERIALBASE wld on wld.FENTRYID=wl.FPKID left join t_BD_Stock kf on kf.FMASTERID=chb.FSTOCKID left join T_BD_UNIT unit on wld.FBASEUNITID=unit.FUNITID left join T_BD_UNIT_l unita on wld.FBaseUnitId=unita.FUNITID
left join T_HS_CALDIMENSIONS org on cha.FDIMENSIONID=org.FDIMENSIONID left join T_ORG_ORGANIZATIONS gs on gs.FORGID=org.FFINORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=org.FFINORGID and gsa.FNAME like ‘%华世%’
where cha.fyear=str(year(‘2023-09-01’)) and cha.FPERIOD=str(month(‘2023-09-01’)) and org.FFINORGID=1 and gsa.FNAME is not null and ch.FEndInitKey=0
group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,fzd.FNUMBER,fze.fname,unit.fnumber,unita.fname,cha.fyear ,cha.FPERIOD ,wla.fnumber ,wl.fname,wl.FSPECIFICATION

—计算本期收发
insert into #KD_SF([公司编码],[公司名称],[存货类别产品码],[产存货类别名称],[产品代码],[产品名称],[规格],[单位编码],[单位名称],[本期生产入库数量],[本期生产入库不含税金额],[本期采购入库数量],[本期采购入库不含税金额],[本期采购入库含税金额],[本期其他入库数量],[本期其他入库不含税金额],[本期领料出库数量],[本期领料出库不含税金额],[本期销售出库数量],[本期销售出库不含税金额],[本期销售出库含税金额],[本期销售出库成本],[本期其他出库数量],[本期其他出库不含税金额])
select
tmp.gano,
tmp.gsname,
tmp.fzno,
tmp.fzname,
tmp.wlno,
tmp.wlname,
tmp.ggxh,
tmp.unitno,
tmp.unitname,
sum(tmp.scrkbaseqty) ,sum(tmp.scrkamount),
sum(tmp.cgrkqty),sum(tmp.cgrkamount),sum(tmp.cgrktaxamount),
sum(tmp.qtrkqty),sum(tmp.qtrkamount),
sum(tmp.llckqty),sum(tmp.llckamount),
sum(tmp.xsckqty),sum(tmp.xsckamount),sum(tmp.xscktaxamount),sum(tmp. xsckcb),
sum(tmp.qtckqty),sum(tmp.qtckamount)
from(
select
‘生产入库’ type,gs.fnumber gano,gsa.fname gsname,fz.FNUMBER fzno,fza.fname fzname,dep.fnumber cxno,dept.fname cxname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,sum(a.FBaseRealQty) scrkbaseqty,sum(a.FAMOUNT) scrkamount,0 cgrkqty,0 cgrkamount,0 cgrktaxamount,0 qtrkqty,0 qtrkamount,0 llckqty,0 llckamount,0 xsckqty,0 xsckamount,0 xscktaxamount,0 xsckcb,0 qtckqty,0 qtckamount
from T_PRD_INSTOCKENTRY a
left join T_PRD_INSTOCK b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=a.FWorkShopId
left join T_BD_DEPARTMENT dep on dep.FDEPTID=a.FWorkShopId
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FPRDORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FPRDORGID and gsa.FNAME like ‘%华世%’

where b.fdate>=‘2023-09-01’ and b.fdate<='2023-09-30’and b.FPRDORGID like ‘1%’

group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname

union all

select
‘采购入库’ type,gs.fnumber gano,gsa.fname gsname,fz.FNUMBER fzno,fza.fname fzname,dep.fnumber cxno,dept.fname cxname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,0 scrkbaseqty,0 scrkamount,sum(a.FRealQty) cgrkqty,sum(c.FBillAmount) cgrkamount, sum(c.FBillAllAmount) cgrktaxamount,0 qtrkqty,0 qtrkamount,0 llckqty,0 llckamount,0 xsckqty,0 xsckamount,0 xscktaxamount,0 xsckcb,0 qtckqty,0 qtckamount
from T_STK_INSTOCKENTRY a left join T_STK_INSTOCKFIN c on a.fid=c.fid
left join T_STK_INSTOCK b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=b.FStockDeptId
left join T_BD_DEPARTMENT dep on dep.FDEPTID=b.FStockDeptId
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FPurchaseOrgId left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FPurchaseOrgId and gsa.FNAME like ‘%华世%’

where b.fdate>=‘2023-09-01’ and b.fdate<='2023-09-30’and b.FPurchaseOrgId=1

group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname

union all

select
‘其他入库’ type,gs.fnumber gano,gsa.fname gsname,fz.FNUMBER fzno,fza.fname fzname,dep.fnumber cxno,dept.fname cxname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,0 scrkbaseqty,0 scrkamount,0 cgrkqty,0 cgrkamount,0 cgrktaxamount,sum(a.FQty) qtrkqty,sum(a.FAMOUNT) qtrkamount,0 llckqty,0 llckamount,0 xsckqty,0 xsckamount,0 xscktaxamount,0 xsckcb,0 qtckqty,0 qtckamount
from T_STK_MISCELLANEOUSENTRY a
left join T_STK_MISCELLANEOUS b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=b.FDEPTID
left join T_BD_DEPARTMENT dep on dep.FDEPTID=b.FDEPTID
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FStockOrgId left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FStockOrgId and gsa.FNAME like ‘%华世%’

where b.fdate>=‘2023-09-01’ and b.fdate<='2023-09-30’and b.FStockOrgId like ‘1%’

group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname

union all

select
‘其他出库’ type,gs.fnumber gano,gsa.fname gsname,fz.FNUMBER fzno,fza.fname fzname,dep.fnumber cxno,dept.fname cxname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,0 scrkbaseqty,0 scrkamount,0 cgrkqty,0 cgrkamount,0 cgrktaxamount,0 qtrkqty,0 qtrkamount,0 llckqty,0 llckamount,0 xsckqty,0 xsckamount,0 xscktaxamount,0 xsckcb,sum(a.FQty) qtckqty,sum(a.FAMOUNT) qtckamount
from T_STK_MISDELIVERYENTRY a
left join T_STK_MISDELIVERY b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=b.FDEPTID
left join T_BD_DEPARTMENT dep on dep.FDEPTID=b.FDEPTID
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FStockOrgId left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FStockOrgId and gsa.FNAME like ‘%华世%’

where b.fdate>=‘2023-09-01’ and b.fdate<='2023-09-30’and b.FStockOrgId like ‘1%’

group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname

union all

select
‘销售出库’ type,gs.fnumber gano,gsa.fname gsname,fz.FNUMBER fzno,fza.fname fzname,dep.fnumber cxno,dept.fname cxname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,0 scrkbaseqty,0 scrkamount,0 cgrkqty,0cgrkamount, 0 cgrktaxamount,0 qtrkqty,0 qtrkamount,0 llckqty,0 llckamount,sum(a.FRealQty) xsckqty,sum(c.FBillAmount) xsckamount,sum(c.FBillAllAmount) xscktaxamount,sum(c.FBillCostAmount_LC) xsckcb,0 qtckqty,0 qtckamount
from T_SAL_OUTSTOCKENTRY a left join T_SAL_OUTSTOCKFIN c on a.fid=c.fid
left join T_SAL_OUTSTOCK b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=b.FSaleDeptID
left join T_BD_DEPARTMENT dep on dep.FDEPTID=b.FSaleDeptID
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FSaleOrgId left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FSaleOrgId and gsa.FNAME like ‘%华世%’

where b.fdate>=‘2023-09-01’ and b.fdate<='2023-09-30’and b.FSaleOrgId=1

group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname

union all

select
‘领料出库’ type,gs.fnumber gano,gsa.fname gsname,fz.FNUMBER fzno,fza.fname fzname,dep.fnumber cxno,dept.fname cxname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,0 scrkbaseqty,0scrkamount,0 cgrkqty,0 cgrkamount,0 cgrktaxamount,0 qtrkqty,0 qtrkamount,sum(a.FActualQty) llckqty,sum(a.FAMOUNT) llckamount,0 xsckqty,0 xsckamount,0 xscktaxamount,0 xsckcb,0 qtckqty,0 qtckamount
from T_PRD_PICKMTRLDATA a
left join T_PRD_PICKMTRL b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=a.FWorkShopId
left join T_BD_DEPARTMENT dep on dep.FDEPTID=a.FWorkShopId
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FPRDORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FPRDORGID and gsa.FNAME like ‘%华世%’

where b.fdate>=‘2023-09-01’ and b.fdate<='2023-09-30’and b.FPRDORGID like ‘1%’

group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.fname,dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname
) tmp

group by tmp.gano,tmp.gsname,tmp.fzno,tmp.fzname,tmp.wlno,tmp.wlname,tmp.ggxh,tmp.unitno,tmp.unitname

order by tmp.gano,tmp.fzno,tmp.wlno

update #KD_SF set[本期采购入库税率]=[本期采购入库含税金额]/[本期采购入库不含税金额] where [本期采购入库不含税金额]<>0

update #KD_SF set[本期销售出库税率]=[本期销售出库含税金额]/[本期销售出库不含税金额] where [本期销售出库不含税金额]<>0

–数据更新

update #KD_SFC set [本期采购入库数量]=( select isnull([本期采购入库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期采购入库税率]= ( select isnull([本期采购入库税率],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期采购入库含税金额]=( select isnull([本期采购入库含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期采购入库不含税金额]= ( select isnull([本期采购入库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期生产入库数量]=( select isnull([本期生产入库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期生产入库不含税金额]= ( select isnull([本期生产入库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他入库数量]= ( select isnull([本期其他入库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他入库不含税金额]= ( select isnull([本期其他入库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库数量]= ( select isnull([本期销售出库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库税率]= ( select isnull([本期销售出库税率],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库不含税金额]= ( select isnull([本期销售出库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库含税金额]= ( select isnull([本期销售出库含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库成本]= ( select isnull([本期销售出库成本],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期领料出库数量]= ( select isnull([本期领料出库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期领料出库不含税金额]= ( select isnull([本期领料出库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他出库数量]= ( select isnull([本期其他出库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他出库不含税金额]= ( select isnull([本期其他出库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期采购入库数量]=( select isnull([本期采购入库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期采购入库税率]=( select isnull([本期采购入库税率],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期采购入库含税金额]= ( select isnull([本期采购入库含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期采购入库不含税金额]= ( select isnull([本期采购入库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期生产入库数量]=( select isnull([本期生产入库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期生产入库不含税金额]= (select isnull([本期生产入库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他入库数量]=( select isnull([本期其他入库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他入库不含税金额]=( select isnull([本期其他入库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库数量]=( select isnull([本期销售出库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库税率]=( select isnull([本期销售出库税率],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库不含税金额]=( select isnull([本期销售出库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库含税金额]= ( select isnull([本期销售出库含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期销售出库成本]= ( select isnull([本期销售出库成本],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期领料出库数量]= ( select isnull([本期领料出库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期领料出库不含税金额]=( select isnull([本期领料出库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他出库数量]=( select isnull([本期其他出库数量],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])
update #KD_SFC set [本期其他出库不含税金额]= ( select isnull([本期其他出库不含税金额],0) from #KD_SF where #KD_SF.[公司编码] =#KD_SFC.[公司编码] and #KD_SF.[产品代码] =#KD_SFC.[产品代码])

– null值处理
update #KD_SFC set [本期采购入库数量]=isnull([本期采购入库数量],0)
update #KD_SFC set [本期采购入库税率]= isnull([本期采购入库税率],0)
update #KD_SFC set [本期采购入库含税金额]=isnull([本期采购入库含税金额],0)
update #KD_SFC set [本期采购入库不含税金额]=isnull([本期采购入库不含税金额],0)
update #KD_SFC set [本期生产入库数量]=isnull([本期生产入库数量],0)
update #KD_SFC set [本期生产入库不含税金额]= isnull([本期生产入库不含税金额],0)
update #KD_SFC set [本期其他入库数量]= isnull([本期其他入库数量],0)
update #KD_SFC set [本期其他入库不含税金额]= isnull([本期其他入库不含税金额],0)
update #KD_SFC set [本期销售出库数量]= isnull([本期销售出库数量],0)
update #KD_SFC set [本期销售出库税率]= isnull([本期销售出库税率],0)
update #KD_SFC set [本期销售出库不含税金额]= isnull([本期销售出库不含税金额],0)
update #KD_SFC set [本期销售出库含税金额]=isnull([本期销售出库含税金额],0)
update #KD_SFC set [本期销售出库成本]= isnull([本期销售出库成本],0)
update #KD_SFC set [本期领料出库数量]= isnull([本期领料出库数量],0)
update #KD_SFC set [本期领料出库不含税金额]= isnull([本期领料出库不含税金额],0)
update #KD_SFC set [本期其他出库数量]=isnull([本期其他出库数量],0)
update #KD_SFC set [本期其他出库不含税金额]= isnull([本期其他出库不含税金额],0)
update #KD_SFC set [本期采购入库数量]=isnull([本期采购入库数量],0)
update #KD_SFC set [本期采购入库税率]=isnull([本期采购入库税率],0)
update #KD_SFC set [本期采购入库含税金额]= isnull([本期采购入库含税金额],0)
update #KD_SFC set [本期采购入库不含税金额]=isnull([本期采购入库不含税金额],0)
update #KD_SFC set [本期生产入库数量]=isnull([本期生产入库数量],0)
update #KD_SFC set [本期生产入库不含税金额]= isnull([本期生产入库不含税金额],0)
update #KD_SFC set [本期其他入库数量]=isnull([本期其他入库数量],0)
update #KD_SFC set [本期其他入库不含税金额]= isnull([本期其他入库不含税金额],0)
update #KD_SFC set [本期销售出库数量]=isnull([本期销售出库数量],0)
update #KD_SFC set [本期销售出库税率]=isnull([本期销售出库税率],0)
update #KD_SFC set [本期销售出库不含税金额]= isnull([本期销售出库不含税金额],0)
update #KD_SFC set [本期销售出库含税金额]=isnull([本期销售出库含税金额],0)
update #KD_SFC set [本期销售出库成本]= isnull([本期销售出库成本],0)
update #KD_SFC set [本期领料出库数量]= isnull([本期领料出库数量],0)
update #KD_SFC set [本期领料出库不含税金额]= isnull([本期领料出库不含税金额],0)
update #KD_SFC set [本期其他出库数量]=isnull([本期其他出库数量],0)
update #KD_SFC set [本期其他出库不含税金额]= isnull([本期其他出库不含税金额],0)

–更新税率

update #KD_SFC set [换算]= [本期采购入库税率]
update #KD_SFC set [换算]= [本期销售出库税率] where [本期采购入库税率]=0

–计算含税
update #KD_SFC set [上月结存含税金额]= [上月结存不含税金额]*[换算] where [换算]<>0
update #KD_SFC set [上月结存含税金额]= [上月结存不含税金额] where [换算]=0

update #KD_SFC set [本期生产入库含税金额]= [本期生产入库不含税金额]*[换算] where [换算]<>0
update #KD_SFC set [本期生产入库含税金额]= [本期生产入库不含税金额] where [换算]=0

update #KD_SFC set [本期其他入库含税金额]= [本期其他入库不含税金额]*[换算] where [换算]<>0
update #KD_SFC set [本期其他入库含税金额]= [本期其他入库不含税金额] where [换算]=0

update #KD_SFC set [本期入库含税金额合计]= [本期入库不含税金额合计]*[换算] where [换算]<>0
update #KD_SFC set [本期入库含税金额合计]= [本期入库不含税金额合计] where [换算]=0

update #KD_SFC set [本期领料出库含税金额]= [本期领料出库不含税金额]*[换算] where [换算]<>0
update #KD_SFC set [本期领料出库含税金额]= [本期领料出库不含税金额] where [换算]=0

update #KD_SFC set [本期其他出库含税金额]= [本期其他出库不含税金额]*[换算] where [换算]<>0
update #KD_SFC set [本期其他出库含税金额]= [本期其他出库不含税金额] where [换算]=0

update #KD_SFC set [本期出库含税金额合计]= [本期出库不含税金额合计]*[换算] where [换算]<>0
update #KD_SFC set [本期出库含税金额合计]= [本期出库不含税金额合计] where [换算]=0

–计算期末结存
update #KD_SFC set [期末结存数量]= [上月结存数量]+[本期其入库数量合计]-[本期出库数量合计]
update #KD_SFC set [期末结存不含税金额]=[上月结存不含税金额]+[本期入库不含税金额合计]-[本期出库不含税金额合计]
update #KD_SFC set [期末结存含税金额]=[上月结存含税金额]+[本期入库含税金额合计]-[本期出库含税金额合计]
update #KD_SFC set [期末含税单价]=[期末结存含税金额]/[期末结存数量] where [期末结存数量]<>0
select * from #KD_SFC

end

这篇关于K3Cloud 存货收发存汇总表二开优化的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/456041

相关文章

Deepseek使用指南与提问优化策略方式

《Deepseek使用指南与提问优化策略方式》本文介绍了DeepSeek语义搜索引擎的核心功能、集成方法及优化提问策略,通过自然语言处理和机器学习提供精准搜索结果,适用于智能客服、知识库检索等领域... 目录序言1. DeepSeek 概述2. DeepSeek 的集成与使用2.1 DeepSeek API

Tomcat高效部署与性能优化方式

《Tomcat高效部署与性能优化方式》本文介绍了如何高效部署Tomcat并进行性能优化,以确保Web应用的稳定运行和高效响应,高效部署包括环境准备、安装Tomcat、配置Tomcat、部署应用和启动T... 目录Tomcat高效部署与性能优化一、引言二、Tomcat高效部署三、Tomcat性能优化总结Tom

解读Redis秒杀优化方案(阻塞队列+基于Stream流的消息队列)

《解读Redis秒杀优化方案(阻塞队列+基于Stream流的消息队列)》该文章介绍了使用Redis的阻塞队列和Stream流的消息队列来优化秒杀系统的方案,通过将秒杀流程拆分为两条流水线,使用Redi... 目录Redis秒杀优化方案(阻塞队列+Stream流的消息队列)什么是消息队列?消费者组的工作方式每

Oracle查询优化之高效实现仅查询前10条记录的方法与实践

《Oracle查询优化之高效实现仅查询前10条记录的方法与实践》:本文主要介绍Oracle查询优化之高效实现仅查询前10条记录的相关资料,包括使用ROWNUM、ROW_NUMBER()函数、FET... 目录1. 使用 ROWNUM 查询2. 使用 ROW_NUMBER() 函数3. 使用 FETCH FI

C#使用HttpClient进行Post请求出现超时问题的解决及优化

《C#使用HttpClient进行Post请求出现超时问题的解决及优化》最近我的控制台程序发现有时候总是出现请求超时等问题,通常好几分钟最多只有3-4个请求,在使用apipost发现并发10个5分钟也... 目录优化结论单例HttpClient连接池耗尽和并发并发异步最终优化后优化结论我直接上优化结论吧,

Java内存泄漏问题的排查、优化与最佳实践

《Java内存泄漏问题的排查、优化与最佳实践》在Java开发中,内存泄漏是一个常见且令人头疼的问题,内存泄漏指的是程序在运行过程中,已经不再使用的对象没有被及时释放,从而导致内存占用不断增加,最终... 目录引言1. 什么是内存泄漏?常见的内存泄漏情况2. 如何排查 Java 中的内存泄漏?2.1 使用 J

MySQL不使用子查询的原因及优化案例

《MySQL不使用子查询的原因及优化案例》对于mysql,不推荐使用子查询,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,本文给大家... 目录不推荐使用子查询和JOIN的原因解决方案优化案例案例1:查询所有有库存的商品信息案例2:使用EX

MySQL中my.ini文件的基础配置和优化配置方式

《MySQL中my.ini文件的基础配置和优化配置方式》文章讨论了数据库异步同步的优化思路,包括三个主要方面:幂等性、时序和延迟,作者还分享了MySQL配置文件的优化经验,并鼓励读者提供支持... 目录mysql my.ini文件的配置和优化配置优化思路MySQL配置文件优化总结MySQL my.ini文件

正则表达式高级应用与性能优化记录

《正则表达式高级应用与性能优化记录》本文介绍了正则表达式的高级应用和性能优化技巧,包括文本拆分、合并、XML/HTML解析、数据分析、以及性能优化方法,通过这些技巧,可以更高效地利用正则表达式进行复杂... 目录第6章:正则表达式的高级应用6.1 模式匹配与文本处理6.1.1 文本拆分6.1.2 文本合并6

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义