成本管理

--建表脚本--

create table TM_CA_PRODUCTPERSTDCOSTDIFF(

FPRODUCTID int not null  comment '产品'

,FBASICUNITID int not null  comment '基本单位'

,FCOSTITEMID int not null  comment '成本项目'

,FMATERIALID int not null  comment '物料'

,FEXPENSEITEMID int not null  comment '费用项目'

,FREALUSEDHOUR decimal(23,10) not null  comment '实际成本耗用'

,FREALPRICE decimal(23,10) not null  comment '实际成本费率'

,FREALCOSTAMOUNT decimal(23,10) not null  comment '实际成本'

,FSTDUSEDHOUR decimal(23,10) not null  comment '标准成本耗用'

,FSTDPRICE decimal(23,10) not null  comment '标准成本费率'

,FSTDCOSTAMOUNT decimal(23,10) not null  comment '标准成本'

,FQUANTITYDIFF decimal(23,10) not null  comment '量差'

,FPRICEDIFF decimal(23,10) not null  comment '价差'

,FTOTALDIFFAMOUNT decimal(23,10) not null  comment '成本差异'

,FQTYDIGITS int not null  comment '单位精度'

,FPRICEDIGITS int not null  comment '单价精度'

,FAMOUNTDIGITS int not null  comment '金额精度'

,FEXPTYPE char(1) not null  comment '费用类型'

,FLINEDATATYPE int not null  comment '行类型'

,FIDENTITYID int not null  comment '行类型行号'

,FPARENTUNITID int not null  comment '父项物料单位'

,FPARENTCONVERTRATE decimal(23,10) not null  comment '物料单位转换率'

) comment = '报表：产品单位标准成本差异分析表临时...'

--查询--

select FPRODUCTID as "fproductid",FBASICUNITID as "fbasicunitid",FCOSTITEMID as "fcostitemid",FMATERIALID as "fmaterialid",FEXPENSEITEMID as "fexpenseitemid",FREALUSEDHOUR as "frealusedhour",FREALPRICE as "frealprice",FREALCOSTAMOUNT as "frealcostamount",FSTDUSEDHOUR as "fstdusedhour",FSTDPRICE as "fstdprice",FSTDCOSTAMOUNT as "fstdcostamount",FQUANTITYDIFF as "fquantitydiff",FPRICEDIFF as "fpricediff",FTOTALDIFFAMOUNT as "ftotaldiffamount",FQTYDIGITS as "fqtydigits",FPRICEDIGITS as "fpricedigits",FAMOUNTDIGITS as "famountdigits",FEXPTYPE as "fexptype",FLINEDATATYPE as "flinedatatype",FIDENTITYID as "fidentityid",FPARENTUNITID as "fparentunitid",FPARENTCONVERTRATE as "fparentconvertrate" from TM_CA_PRODUCTPERSTDCOSTDIFF

--查询(中文字段)--

select FPRODUCTID as "产品",FBASICUNITID as "基本单位",FCOSTITEMID as "成本项目",FMATERIALID as "物料",FEXPENSEITEMID as "费用项目",FREALUSEDHOUR as "实际成本耗用",FREALPRICE as "实际成本费率",FREALCOSTAMOUNT as "实际成本",FSTDUSEDHOUR as "标准成本耗用",FSTDPRICE as "标准成本费率",FSTDCOSTAMOUNT as "标准成本",FQUANTITYDIFF as "量差",FPRICEDIFF as "价差",FTOTALDIFFAMOUNT as "成本差异",FQTYDIGITS as "单位精度",FPRICEDIGITS as "单价精度",FAMOUNTDIGITS as "金额精度",FEXPTYPE as "费用类型",FLINEDATATYPE as "行类型",FIDENTITYID as "行类型行号",FPARENTUNITID as "父项物料单位",FPARENTCONVERTRATE as "物料单位转换率" from TM_CA_PRODUCTPERSTDCOSTDIFF

--INSERT脚本--

insert into TM_CA_PRODUCTPERSTDCOSTDIFF(FPRODUCTID,FBASICUNITID,FCOSTITEMID,FMATERIALID,FEXPENSEITEMID,FREALUSEDHOUR,FREALPRICE,FREALCOSTAMOUNT,FSTDUSEDHOUR,FSTDPRICE,FSTDCOSTAMOUNT,FQUANTITYDIFF,FPRICEDIFF,FTOTALDIFFAMOUNT,FQTYDIGITS,FPRICEDIGITS,FAMOUNTDIGITS,FEXPTYPE,FLINEDATATYPE,FIDENTITYID,FPARENTUNITID,FPARENTCONVERTRATE) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update TM_CA_PRODUCTPERSTDCOSTDIFF set FPRODUCTID=?,FBASICUNITID=?,FCOSTITEMID=?,FMATERIALID=?,FEXPENSEITEMID=?,FREALUSEDHOUR=?,FREALPRICE=?,FREALCOSTAMOUNT=?,FSTDUSEDHOUR=?,FSTDPRICE=?,FSTDCOSTAMOUNT=?,FQUANTITYDIFF=?,FPRICEDIFF=?,FTOTALDIFFAMOUNT=?,FQTYDIGITS=?,FPRICEDIGITS=?,FAMOUNTDIGITS=?,FEXPTYPE=?,FLINEDATATYPE=?,FIDENTITYID=?,FPARENTUNITID=?,FPARENTCONVERTRATE=? where 

--delete脚本--

delete from TM_CA_PRODUCTPERSTDCOSTDIFF where 

--给字段加备注--

alter table TM_CA_PRODUCTPERSTDCOSTDIFF comment '报表：产品单位标准成本差异分析表临时...';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FPRODUCTID int not null  comment '产品';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FBASICUNITID int not null  comment '基本单位';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FCOSTITEMID int not null  comment '成本项目';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FMATERIALID int not null  comment '物料';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FEXPENSEITEMID int not null  comment '费用项目';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FREALUSEDHOUR decimal(23,10) not null  comment '实际成本耗用';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FREALPRICE decimal(23,10) not null  comment '实际成本费率';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FREALCOSTAMOUNT decimal(23,10) not null  comment '实际成本';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FSTDUSEDHOUR decimal(23,10) not null  comment '标准成本耗用';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FSTDPRICE decimal(23,10) not null  comment '标准成本费率';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FSTDCOSTAMOUNT decimal(23,10) not null  comment '标准成本';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FQUANTITYDIFF decimal(23,10) not null  comment '量差';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FPRICEDIFF decimal(23,10) not null  comment '价差';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FTOTALDIFFAMOUNT decimal(23,10) not null  comment '成本差异';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FQTYDIGITS int not null  comment '单位精度';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FPRICEDIGITS int not null  comment '单价精度';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FAMOUNTDIGITS int not null  comment '金额精度';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FEXPTYPE char(1) not null  comment '费用类型';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FLINEDATATYPE int not null  comment '行类型';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FIDENTITYID int not null  comment '行类型行号';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FPARENTUNITID int not null  comment '父项物料单位';

alter table TM_CA_PRODUCTPERSTDCOSTDIFF modify column FPARENTCONVERTRATE decimal(23,10) not null  comment '物料单位转换率';

--建表脚本--

create table TM_CA_PRODUCTSTDCOSTRPT(

FPRODUCTID int not null  comment '产品'

,FBOMID int not null  comment 'bom内码'

,FBASICUNITID int not null  comment '基本单位'

,FCOSTITEMID int not null  comment '成本项目'

,FMATERIALID int not null  comment '物料'

,FEXPENSEITEMID int not null  comment '费用项目'

,FUNITUSEDHOUR decimal(23,10) not null  comment '成本耗用'

,FSTDPRICE decimal(23,10) not null  comment '费率'

,FSTDCOSTAMOUNT decimal(23,10) not null  comment '成本'

,FEXPTYPE char(1) not null  comment '费用类型'

,FLINEDATATYPE int not null  comment '行类型'

,FPARENTUNITID int not null  comment '父项物料单位'

,FPARENTCONVERTRATE decimal(23,10) not null  comment '物料单位转换率'

,FSONUNITID int not null  comment '子项物料单位'

,FIDENTITYID int not null  comment '自增id'

,FPRCCOSTAMOUNT decimal(23,10) not null  comment '计价单位标准成本'

,FSALEPRICEUNITID int not null  comment '销售计价单位'

,FBOMCOSTAMOUNT decimal(23,10) not null  comment 'bom单位标准成本'

) comment = '报表：产品标准成本临时表'

--查询--

select FPRODUCTID as "fproductid",FBOMID as "fbomid",FBASICUNITID as "fbasicunitid",FCOSTITEMID as "fcostitemid",FMATERIALID as "fmaterialid",FEXPENSEITEMID as "fexpenseitemid",FUNITUSEDHOUR as "funitusedhour",FSTDPRICE as "fstdprice",FSTDCOSTAMOUNT as "fstdcostamount",FEXPTYPE as "fexptype",FLINEDATATYPE as "flinedatatype",FPARENTUNITID as "fparentunitid",FPARENTCONVERTRATE as "fparentconvertrate",FSONUNITID as "fsonunitid",FIDENTITYID as "fidentityid",FPRCCOSTAMOUNT as "fprccostamount",FSALEPRICEUNITID as "fsalepriceunitid",FBOMCOSTAMOUNT as "fbomcostamount" from TM_CA_PRODUCTSTDCOSTRPT

--查询(中文字段)--

select FPRODUCTID as "产品",FBOMID as "bom内码",FBASICUNITID as "基本单位",FCOSTITEMID as "成本项目",FMATERIALID as "物料",FEXPENSEITEMID as "费用项目",FUNITUSEDHOUR as "成本耗用",FSTDPRICE as "费率",FSTDCOSTAMOUNT as "成本",FEXPTYPE as "费用类型",FLINEDATATYPE as "行类型",FPARENTUNITID as "父项物料单位",FPARENTCONVERTRATE as "物料单位转换率",FSONUNITID as "子项物料单位",FIDENTITYID as "自增id",FPRCCOSTAMOUNT as "计价单位标准成本",FSALEPRICEUNITID as "销售计价单位",FBOMCOSTAMOUNT as "bom单位标准成本" from TM_CA_PRODUCTSTDCOSTRPT

--INSERT脚本--

insert into TM_CA_PRODUCTSTDCOSTRPT(FPRODUCTID,FBOMID,FBASICUNITID,FCOSTITEMID,FMATERIALID,FEXPENSEITEMID,FUNITUSEDHOUR,FSTDPRICE,FSTDCOSTAMOUNT,FEXPTYPE,FLINEDATATYPE,FPARENTUNITID,FPARENTCONVERTRATE,FSONUNITID,FIDENTITYID,FPRCCOSTAMOUNT,FSALEPRICEUNITID,FBOMCOSTAMOUNT) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update TM_CA_PRODUCTSTDCOSTRPT set FPRODUCTID=?,FBOMID=?,FBASICUNITID=?,FCOSTITEMID=?,FMATERIALID=?,FEXPENSEITEMID=?,FUNITUSEDHOUR=?,FSTDPRICE=?,FSTDCOSTAMOUNT=?,FEXPTYPE=?,FLINEDATATYPE=?,FPARENTUNITID=?,FPARENTCONVERTRATE=?,FSONUNITID=?,FIDENTITYID=?,FPRCCOSTAMOUNT=?,FSALEPRICEUNITID=?,FBOMCOSTAMOUNT=? where 

--delete脚本--

delete from TM_CA_PRODUCTSTDCOSTRPT where 

--给字段加备注--

alter table TM_CA_PRODUCTSTDCOSTRPT comment '报表：产品标准成本临时表';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FPRODUCTID int not null  comment '产品';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FBOMID int not null  comment 'bom内码';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FBASICUNITID int not null  comment '基本单位';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FCOSTITEMID int not null  comment '成本项目';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FMATERIALID int not null  comment '物料';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FEXPENSEITEMID int not null  comment '费用项目';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FUNITUSEDHOUR decimal(23,10) not null  comment '成本耗用';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FSTDPRICE decimal(23,10) not null  comment '费率';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FSTDCOSTAMOUNT decimal(23,10) not null  comment '成本';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FEXPTYPE char(1) not null  comment '费用类型';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FLINEDATATYPE int not null  comment '行类型';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FPARENTUNITID int not null  comment '父项物料单位';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FPARENTCONVERTRATE decimal(23,10) not null  comment '物料单位转换率';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FSONUNITID int not null  comment '子项物料单位';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FIDENTITYID int not null  comment '自增id';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FPRCCOSTAMOUNT decimal(23,10) not null  comment '计价单位标准成本';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FSALEPRICEUNITID int not null  comment '销售计价单位';

alter table TM_CA_PRODUCTSTDCOSTRPT modify column FBOMCOSTAMOUNT decimal(23,10) not null  comment 'bom单位标准成本';

--建表脚本--

create table TM_CA_PROREALCOSTMATCQ(

FPRODUCTID int not null  comment '产品'

,FMATERIALID int not null  comment '子项物料'

,FCOMPLETEQTY decimal(23,10) not null  comment '耗用数量'

,FEXPTYPE char(1) not null  comment '费用类型'

) comment = '报表：产品单位标准成本差异实际物料耗...'

--查询--

select FPRODUCTID as "fproductid",FMATERIALID as "fmaterialid",FCOMPLETEQTY as "fcompleteqty",FEXPTYPE as "fexptype" from TM_CA_PROREALCOSTMATCQ

--查询(中文字段)--

select FPRODUCTID as "产品",FMATERIALID as "子项物料",FCOMPLETEQTY as "耗用数量",FEXPTYPE as "费用类型" from TM_CA_PROREALCOSTMATCQ

--INSERT脚本--

insert into TM_CA_PROREALCOSTMATCQ(FPRODUCTID,FMATERIALID,FCOMPLETEQTY,FEXPTYPE) values (?,?,?,?)

--UPDATE脚本--

update TM_CA_PROREALCOSTMATCQ set FPRODUCTID=?,FMATERIALID=?,FCOMPLETEQTY=?,FEXPTYPE=? where 

--delete脚本--

delete from TM_CA_PROREALCOSTMATCQ where 

--给字段加备注--

alter table TM_CA_PROREALCOSTMATCQ comment '报表：产品单位标准成本差异实际物料耗...';

alter table TM_CA_PROREALCOSTMATCQ modify column FPRODUCTID int not null  comment '产品';

alter table TM_CA_PROREALCOSTMATCQ modify column FMATERIALID int not null  comment '子项物料';

alter table TM_CA_PROREALCOSTMATCQ modify column FCOMPLETEQTY decimal(23,10) not null  comment '耗用数量';

alter table TM_CA_PROREALCOSTMATCQ modify column FEXPTYPE char(1) not null  comment '费用类型';

--建表脚本--

create table TM_CA_PROREALCOSTWHCQ(

FPRODUCTID int not null  comment '产品'

,FCOMPLETEHOUR decimal(23,10) not null  comment '工时'

,FCOMPLETEQTY decimal(23,10) not null  comment '完工数量'

) comment = '报表：产品单位标准成本差异实际完工&...'

--查询--

select FPRODUCTID as "fproductid",FCOMPLETEHOUR as "fcompletehour",FCOMPLETEQTY as "fcompleteqty" from TM_CA_PROREALCOSTWHCQ

--查询(中文字段)--

select FPRODUCTID as "产品",FCOMPLETEHOUR as "工时",FCOMPLETEQTY as "完工数量" from TM_CA_PROREALCOSTWHCQ

--INSERT脚本--

insert into TM_CA_PROREALCOSTWHCQ(FPRODUCTID,FCOMPLETEHOUR,FCOMPLETEQTY) values (?,?,?)

--UPDATE脚本--

update TM_CA_PROREALCOSTWHCQ set FPRODUCTID=?,FCOMPLETEHOUR=?,FCOMPLETEQTY=? where 

--delete脚本--

delete from TM_CA_PROREALCOSTWHCQ where 

--给字段加备注--

alter table TM_CA_PROREALCOSTWHCQ comment '报表：产品单位标准成本差异实际完工&...';

alter table TM_CA_PROREALCOSTWHCQ modify column FPRODUCTID int not null  comment '产品';

alter table TM_CA_PROREALCOSTWHCQ modify column FCOMPLETEHOUR decimal(23,10) not null  comment '工时';

alter table TM_CA_PROREALCOSTWHCQ modify column FCOMPLETEQTY decimal(23,10) not null  comment '完工数量';