| 解决方案： |
此部分视图与2002年帐套数据表同名为caq打头，请检查后删除或改为其它名称，然后执行下面脚本即可： -------------------------------------------------------------------------------------------
CREATE view caq_serviceprice as select max(iperiod) as iperiod, max(cprocedureid) as cprocedureid, sum(itotalamo)/sum(iqua) as price from ca_samoct group by cprocedureid
go
-------------------------------------------------------------------------------------------
CREATE view caq_predepdata as select '专用' as cType,a.iperiod,a.cdeptid,0 as amotypeno,'0' as amotype , a.cmatid as amoid,a.iqua as curqua,a.imatqua as curamo,b.irestqua as endqua, b.iprice * b.irestqua as endamo from ca_mabsw a left join ca_enmmc b on a.cdeptid=b.cdeptid and a.cmatid=b.cmatid and a.iperiod = b.iperiod where a.cppid is null union SELECT '共用' ,a.iperiod, a.cdeptid, 0 AS amotypeno, '0' AS amotype, a.cmatid AS amoid, a.iqua AS curqua, a.imatqua AS curamo, b.irestqua AS endqua,b.iprice * b.irestqua AS endamo FROM ca_mabsw a LEFT JOIN ca_enmom b ON a.cdeptid = b.cdeptid AND a.cmatid = b.cmatid AND a.iperiod = b.iperiod where a.cppid is not null Union select '废品' ,max(a.iperiod),max(b.cdepcode),1,'0','-1',0,sum(irecamo),0,0 from ca_waspr a,productstructureex b where a.cppid = b.cpspcode group by cdepcode Union select '' ,iperiod,cdeptid,2,'3',camoid,0,imanamo,0,0 from ca_manct Union select '' ,a.iperiod,a.cdeptid,3,'2',a.cprocedureid,a.iqua,a.iqua* b.price,0,0 from ca_asscw a , caq_serviceprice b where a.iperiod=b.iperiod and a.cprocedureid=b.cprocedureid Union select '' ,max(iperiod),max(cdeptid),4,'4',max(cothamoid),0,sum(iothamo),0,0 from ca_onlia group by cdeptid,iperiod,cothamoid Union select '' ,iperiod,cdeptid,5,'1','',0,iDirectPay,0,0 from ca_dirma
go
-------------------------------------------------------------------------------------------
CREATE VIEW ca_inventory AS SELECT Inventory.cInvCode, Inventory.cInvName, Inventory.cInvStd,Inventory.iInvRCost, Inventory.cInvCCode, ComputationUnit.cComUnitName AS cInvM_Unit,InventoryClass.cInvCName FROM Inventory LEFT OUTER JOIN InventoryClass ON Inventory.cInvCCode = InventoryClass.cInvCCode LEFT OUTER JOIN ComputationUnit ON Inventory.cComunitCode = ComputationUnit.cComunitCode
go
-------------------------------------------------------------------------------------------
CREATE view caq_amoname as select distinct 1 as amotypeno,'0' as amotype,'材料费用' as amotypename,b.cinvcode as amoid, b.cinvname as amoname,b.cinvccode as cinvccode ,b.cinvcname as cinvcname, b.cinvstd as cinvstd,b.cinvm_unit as cunit from ca_amoct a,ca_inventory b where a.camoid= b.cinvcode Union select 2 as amotypeno,'0','材料费用','-1','废品回收','','','','' Union select 3 as amotypeno,'3','制造费用',camoid,camoname,'','','','' from caq_manufacture Union select 4 as amotypeno,'2','辅助费用',cprocedureid,cprocedurename,'','','','' from ca_predf where bisauxi<>0 Union select 5 as amotypeno,'4','其他费用',camoid,camoname,'','','','' from caq_others Union select 6 as amotypeno,'1','人工费用','','直接人工','','','',''
go
-------------------------------------------------------------------------------------------
CREATE view caq_depdata as select a.iperiod as 期间,a.cdeptid as 部门编码,b.cdepname as 部门名称, a.amotypeno as 费用类型序号,a.amotype as 费用类型编码,c.amotypename as 费用类型名称, a.cType as 材料类别, c.cinvccode as 存货分类编码,c.cinvcname as 存货分类名称, a.amoid as 费用明细编码,c.amoname as 费用明细名称, a.curqua as 本期发生数量, a.curamo as 本期发生金额, a.endqua as 期末盘存数量,a.endamo as 期末盘存金额 from caq_predepdata a,department b,caq_amoname c Where a.cdeptid = b.cDepCode and a.amotype=c.amotype and a.amoid=c.amoid
go
-------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CA_IDMAC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CA_IDMAC] GO CREATE TABLE [dbo].[CA_IDMAC] ( [cOutID] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL , [cOutName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , [bType] [bit] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[CA_IDMAC] ADD CONSTRAINT [aaaaaCA_IDMAC_PK] PRIMARY KEY NONCLUSTERED ( [cOutID] ) ON [PRIMARY]
GO
------------------------------------------------------------------------------------------- --2001年数据库中缺少视图GSP_QFHTZ
CREATE VIEW DBO.GSP_QFHTZ AS SELECT GSP_QYCKFHLIST.DARVDATE, GSP_QYCKFHLIST.CINVCODE, GSP_QYCKFHLIST.CCUSNAME, GSP_QYCKFHLIST.CINVNAME, GSP_QYCKFHLIST.CINVSTD, GSP_QYCKFHLIST.CBATCH, GSP_QYCKFHLIST.DPRODATE, GSP_QYCKFHLIST.CVALDATE, GSP_QYCKFHLIST.CENTERPRISE, INVENTORY.CADDRESS ,INVENTORY.CPREPARATIONTYPE ,GSP_QYCKFHLIST.CCOMUNITNAME, GSP_QYCKFHLIST.FQUANTITY, GSP_QYCKFHLIST.CCOMUNITNAMES, GSP_QYCKFHLIST.ICHANGRATE, GSP_QYCKFHLIST.FQUANTITYS, GSP_QYCKFHLIST.CRESULT, GSP_QYCKFHLIST.CMAKER, GSP_QYCKFHLIST.CVERIFIER FROM DBO.GSP_QYCKFHLIST INNER JOIN INVENTORY ON GSP_QYCKFHLIST.CINVCODE=INVENTORY.CINVCODE UNION ALL SELECT GSP_QFHRECORDLIST.DVDATE AS DARVDATE, GSP_QFHRECORDLIST.CINVCODE, GSP_QFHRECORDLIST.CCUSNAME AS CCUSNAME, GSP_QFHRECORDLIST.CINVNAME, GSP_QFHRECORDLIST.CINVSTD, GSP_QFHRECORDLIST.CBATCH, GSP_QFHRECORDLIST.DPRODATE, GSP_QFHRECORDLIST.CVALDATE, GSP_QFHRECORDLIST.CENTERPRISE, INVENTORY.CADDRESS ,INVENTORY.CPREPARATIONTYPE ,GSP_QFHRECORDLIST.CCOMUNITNAME, GSP_QFHRECORDLIST.FQTY AS FQUANTITY, GSP_QFHRECORDLIST.CCOMUNITNAMES, GSP_QFHRECORDLIST.ICHANGRATE, GSP_QFHRECORDLIST.FNUM AS FQUANTITYS, GSP_QFHRECORDLIST.CRESULT_T AS CRESULT, GSP_QFHRECORDLIST.CMAKER, GSP_QFHRECORDLIST.CVERIFIER FROM GSP_QFHRECORDLIST INNER JOIN INVENTORY ON GSP_QFHRECORDLIST.CINVCODE=INVENTORY.CINVCODE
GO |