ProcName Varchar(50) Not Null,
       MasterTable Varchar(30) Null,
       RcvFlag Char(1) Not Null default '0')
GO
CREATE TABLE RcvDownTables(
       Ord Int Not Null,
       DispName Varchar(30) Not Null,
       TableName Varchar(30) Not Null,
       KeyName Varchar(30) Not Null, 
       Sortfmt Varchar(50) Not Null,
       ProcName Varchar(50) Not Null,
       MasterTable Varchar(30) Null,
       RcvFlag Char(1) Not Null default '0')
GO
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(1,'deldata','記錄刪除信息','deldate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(2,'team','柜組信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(3,'class','類別信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(4,'vendor','供應商信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(5,'cust','客戶信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(6,'worktype','工種信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,SendFlag) Values(7,'staff','員工信息','modifydate','datetime','deptno','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(8,'membercard','會員卡信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(9,'goods','商品資料信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,SendFlag) Values(10,'import','進貨單據流水','receivedate','datetime',
'select * from import where auditflag=''1''','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,SqlStat1,SendFlag) Values(11,'alloc','調撥單據流水','receivedate','datetime','destdept;srcdept',
'select * from alloc where auditflag=''1''','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,SendFlag) Values(12,'sale','批發單據流水','receivedate','datetime',
'select * from sale where auditflag=''1''','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,SendFlag) Values(13,'loss','損益單據流水','receivedate','datetime',
'select * from loss where auditflag=''1''','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,ApartField,SendFlag) Values(14,'adjust','調價單據流水','receivedate','datetime',
'select a.*,deptno=b.deptno,receivedate=a.receivedate,alldepart=b.alldepart from adjust a,adjustdetail b where a.adjustno=b.adjustno and a.auditflag=''1''','deptno;AllDepart=''1''','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(15,'req','要貨流水','receivedate','datetime','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,SendFlag) Values(16,'orders','訂貨單據流水','receivedate','datetime','destdept','2')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,MasterTable,SqlStat1,SendFlag) Values(17,'importdetail','進貨明細流水','receivedate','datetime','import',
'select a.*,receivedate=b.receivedate from importdetail a,import b where a.importno=b.importno and b.auditflag=''1''','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,ApartField,MasterTable,SendFlag) Values(18,'allocdetail','調撥明細流水','receivedate','datetime',
'select a.*,destdept=b.destdept,srcdept=b.srcdept,receivedate=b.receivedate from allocdetail a,alloc b where a.allocno=b.allocno and b.auditflag=''1''','destdept;srcdept','alloc','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,MasterTable,SqlStat1,SendFlag) Values(19,'saledetail','批發明細流水','receivedate','datetime','sale',
'select a.*,receivedate=b.receivedate from saledetail a,sale b where a.saleno=b.saleno and b.auditflag=''1''','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,MasterTable,SqlStat1,SendFlag) Values(20,'lossdetail','損益明細流水','receivedate','datetime','loss',
'select a.*,receivedate=b.receivedate from lossdetail a,loss b where a.lossno=b.lossno and b.auditflag=''1''','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,MasterTable,SqlStat1,SendFlag) Values(21,'adjustdetail','調價明細流水','receivedate','datetime','deptno;AllDepart=''1''','adjust',
'select a.*,deptno=a.deptno,receivedate=b.receivedate,alldepart=a.alldepart from adjustdetail a,adjust b where a.adjustno=b.adjustno and b.auditflag=''1''','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,MasterTable,SqlStat1,ApartField,SendFlag) Values(23,'orderdetail','訂貨單明細流水','receivedate','datetime','orders',
'select a.*,destdept=b.destdept,receivedate=b.receivedate from orderdetail a,orders b where a.orderno=b.orderno','destdept','2')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(24,'deptretail','門店單品日銷售','retaildate','datetime','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(25,'cardconsume','卡消費流水','modifydate','datetime','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,ApartField,SFinWO,SendFlag) Values(26,'staffauth','員工功能權限表','mdate','datetime',
'select a.*,deptno=b.deptno,mdate=a.modifydate from staffauth a,staff b where a.operator=b.operator','deptno','a.modifydate','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(27,'paymode','付款方式信息','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(28,'userdeffield','用戶自定義字段表','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,SendFlag) Values(29,'ordersrights','訂貨權限表','modifydate','datetime','deptno','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,SendFlag) Values(30,'reachrights','到貨權限表','modifydate','datetime','deptno','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,SendFlag) Values(31,'adjustrights','調價權限表','modifydate','datetime','deptno','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(32,'custprice','客戶批價表','modifydate','datetime','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(33,'vendorprice','供應商進價表','modifydate','datetime','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(34,'category','品類碼表','modifydate','datetime','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,ApartField,SendFlag) Values(35,'topandbottom','庫存上下限表','modifydate','datetime','deptno','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(36,'deptstock','部門歷史門庫表','modifydate','datetime','0')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,ApartField,SFinWO,SendFlag) Values(37,'grouprights','員工數據權限表','mdate','datetime',
'select a.*,deptno=b.deptno,mdate=a.modifydate from grouprights a,staff b where a.operator=b.operator','deptno','a.modifydate','1')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SendFlag) Values(38,'depttablerights','部門表權限字段','modifydate','datetime','2')
insert SendDownTables(Ord,TableName,DispName,SearchField,FieldType,SqlStat1,SqlStat2,ApartField,SendFlag) Values(38,'alloc_all','調撥流水','receivedate','datetime',
'select a.*,InputDate,AllocDate,AuditDate,ReceiptNo,Operator,Employee,DestDept,SrcDept,InputDept,Cost,
TNTaxAmt=b.NTaxAmt,TAmt=b.Amt,AuditFlag,AuditOperator,ReceiveDate,BusinessFlag,ReceiptFlag,Remark ',
'from allocdetail a,alloc b where a.allocno=b.allocno and b.auditflag=''1''','destdept;srcdept','1')
go
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('0','刪除信息','com_deldata','tablename,key1,key2,key3','tablename a,key1 a,key2 a,key3 a,deldate d','','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('1','柜組信息','team','groupno','groupno a,modifydate d','up_rcvteam','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('2','類別信息','class','classno','classno a,modifydate d','up_rcvclass','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('3','付款方式信息','paymode','paymode','paymode a,modifydate d','up_rcvpaymode','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('4','工種信息','worktype','worktype','worktype a,modifydate d','up_rcvworktype','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('5','品類碼表','category','categoryno','categoryno a,modifydate d','up_rcvcategory','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('5','供應商信息','vendor','vendorno','vendorno a,modifydate d','up_rcvvendor','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('6','商品資料信息','goods','goodsno','goodsno a,modifydate d','up_rcvgoods','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('8','客戶信息','cust','custno','custno a,modifydate d','up_rcvcust','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('9','員工信息','staff','operator','operator a,modifydate d','up_rcvstaff','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('10','會員卡信息','membercard','cardno','cardno a,modifydate d','up_rcvmembercard','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('12','用戶自定義字段表','userdeffield','tablename,fieldname','tablename a,fieldname a,modifydate d','up_rcvuserdeffield','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('13','調價單據流水','adjust','adjustno','adjustno a,receivedate d','up_rcvadjust','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('14','進貨單據流水','import','importno','importno a,receivedate d','up_rcvimport','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('15','調撥單據流水','alloc','allocno','allocno a,receivedate d','up_rcvalloc','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('16','批發單據流水','sale','saleno','saleno a,receivedate d','up_rcvsale','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('17','損益單據流水','loss','lossno','lossno a,receivedate d','up_rcvloss','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('18','訂貨單據流水','orders','orderno','orderno a,receivedate d','up_rcvorders','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('19','要貨流水','req','receiptno,goodsno','receiptno a,goodsno a,receivedate d','up_rcvreq','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag,MasterTable) values('20','調價明細流水','adjustdetail','deptno,adjustno,goodsno','deptno a,adjustno a,goodsno a,receivedate d','up_rcvadjustdetail','1','adjust')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag,MasterTable) values('21','進貨明細流水','importdetail','importno,goodsno','importno a,goodsno a,receivedate d','up_rcvimportdetail','0','import')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag,MasterTable) values('22','調撥明細流水','allocdetail','allocno,goodsno','allocno a,goodsno a,receivedate d','up_rcvallocdetail','1','alloc')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag,MasterTable) values('23','批發明細流水','saledetail','saleno,goodsno','saleno a,goodsno a,receivedate d','up_rcvsaledetail','0','sale')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag,MasterTable) values('24','損益明細流水','lossdetail','lossno,goodsno','lossno a,goodsno a,receivedate d','up_rcvlossdetail','0','loss')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag,MasterTable) values('25','訂貨單明細流水','orderdetail','orderno,goodsno','orderno a,goodsno a,receivedate d','up_rcvorderdetail','1','orders')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('26','門店單品日銷售','deptretail','deptno,goodsno,retaildate','detpno a,goodsno a,retaildate d','up_rcvdeptretail','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('27','卡消費流水','cardconsume','deptno,cardno,modifydate','deptno a,cardno a,modifydate d','up_rcvcardconsume','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('28','訂貨權限表','ordersrights','deptno,goodsno','deptno a,goodsno a,modifydate d','up_rcvordersrights','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('29','到貨權限表','reachrights','deptno,goodsno','deptno a,goodsno a,modifydate d','up_rcvreachrights','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('30','調價權限表','adjustrights','deptno,goodsno','deptno a,goodsno a,modifydate d','up_rcvadjustrights','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('31','客戶批價表','custprice','custno,goodsno','custno a,goodsno a,modifydate d','up_rcvcustprice','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('32','供應商進價表','vendorprice','vendorno,goodsno','vendorno a,goodsno a,modifydate d','up_rcvvendorprice','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('33','庫存上下限表','topandbottom','deptno,goodsno','deptno a,goodsno a,modifydate d','up_rcvtopandbottom','0')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('34','部門歷史門庫表','deptstock','year,month,deptno,goodsno','year a,month a,deptno a,goodsno a,modifydate d','up_rcvdeptstock','1')
insert RcvUpTables(Ord,DispName,TableName,KeyName,SortFmt,ProcName,RcvFlag) values('35','員工功能權限表','staffauth','operator,func','operator a,func a,modifydate d','up_rcvstaffauth','1')