实库存导入
select * from
T_CT_STORE_NEW_hjx
SELECT * FROM T_CT_STORE_NEW_hjx;
delete from T_CT_STORE_NEW_hjx
update T_CT_STORE_NEW_hjx set FACTORYID = '01' where nvl(FACTORYID,'0') = 0 ;
---SELECT * FROM t_ct_store_new
--delete from T_CT_STORE_NEW_hjx;--真几把慢
--truncate table T_CT_STORE_NEW_hjx;
SELECT * FROM T_CT_STORE_NEW_hjx where WAREQTY < 0 ;
delete from T_CT_STORE_NEW_hjx where WAREQTY < 0 ;
SELECT count(1) FROM T_CT_STORE_NEW_hjx where MAKEDATE is null ;
SELECT count(1) FROM T_CT_STORE_NEW_hjx where INVALIDATE is null ;
SELECT count(1) FROM T_CT_STORE_NEW_hjx where makeno is null ;
delete from T_CT_STORE_NEW_hjx where makeno is null ;
update T_CT_STORE_NEW_hjx set INVALIDATE = to_date('2099-12-31','yyyy-mm-dd') where INVALIDATE is null ;
---update T_CT_STORE_NEW_hjx set MAKEDATE = to_date('1970-01-01','yyyy-mm-dd') where MAKEDATE is null ;
update T_CT_STORE_NEW_hjx set MAKEDATE = old_sys_time where MAKEDATE is null ;
delete from T_CT_STORE_NEW_hjx where INVALIDATE < sysdate ;
SELECT count(1) FROM T_CT_STORE_NEW_hjx where busno is null ;
update T_CT_STORE_NEW_hjx set busno = '2000' where busno = '001'
SELECT count(1) FROM T_CT_STORE_NEW_hjx where busno = '2000'
SELECT count(1) FROM T_CT_STORE_NEW_hjx where warecode is null;
--更新的和一块了 其实不和一块更好一些
UPDATE T_CT_STORE_NEW_hjx a
SET wareid =
(SELECT wareid FROM t_ware_base b WHERE a.warecode = b.warecode),
vencusno =
(SELECT vencusno FROM t_vencus_base b WHERE a.vencuscode = b.vencuscode),
subitemid = nvl(subitemid, 0),
batid_type = '正常批次',
addpurprice = 0,
factoryid =
(select factoryid from t_ware b where a.warecode = b.warecode),
areacode =
(select areacode from t_ware b where a.warecode = b.warecode) ;
UPDATE T_CT_STORE_NEW_hjx a set STALLNO = '112000' where STALLNO = '0101' ;
SELECT * FROM s_bill where billcode = 'BAT'
update s_bill set SERIALNO = 1 where billcode = 'BAT';
/*ALTER TABLE T_CT_STORE_NEW_hjx ADD batid NUMBER ;*/
SELECT * FROM T_CT_STORE_NEW_hjx
UPDATE T_CT_STORE_NEW_hjx a SET a.batid=1+ROWNUM;
--UPDATE T_CT_STORE_NEW_hjx a SET a.batid = ROWNUM;
UPDATE T_CT_STORE_NEW_hjx a SET a.srcbatid = batid;
SELECT MAX(SRCBATID) FROM T_CT_STORE_NEW_hjx;
UPDATE T_CT_STORE_NEW_hjx a SET a.objbatid=80001+ROWNUM;
SELECT MAX(objbatid) FROM T_CT_STORE_NEW_hjx;
UPDATE s_bill SET serialno=160001 WHERE billcode='BAT';
select t.srcbatid,t.objbatid from T_CT_STORE_NEW_hjx t;
select batid,count(1) from T_CT_STORE_NEW_hjx group by batid having count(1)>1;
select * from T_CT_STORE_NEW_hjx where busno=2000;
update T_CT_STORE_NEW_hjx a set a.objbatid=SRCBATID where busno=2000;
update T_CT_STORE_NEW_hjx a set a.purprice=3.333333,a.addpurprice=3.333333 where a.purprice='3.3333333333E10'
------库存导入
SELECT paytype,cashtype,vencusno FROM T_CT_STORE_NEW_hjx
update T_CT_STORE_NEW_hjx set paytype = '01' where paytype is null ;
update T_CT_STORE_NEW_hjx set cashtype = '01' where cashtype is null ;
SELECT paytype,cashtype,vencusno FROM T_CT_STORE_NEW_hjx where vencusno is null
update T_CT_STORE_NEW_hjx set VENCUSNO = 701 where VENCUSNO is null ;
SELECT * FROM t_vencus where vencusname like '%苏印%'
truncate table t_init_store_d;
truncate table t_init_store_h;
SELECT * FROM t_init_store_h;
INSERT INTO tmp_disable_trigger VALUES ('t_init_store_h');--136
INSERT INTO t_init_store_h
(initno,
billcode,
compid,
busno,
vencusno,
buyer,
notes,
status,
execdate,
checker1,
checkbit1,
checker2,
checkbit2,
checker3,
checkbit3,
checker4,
checkbit4,
checker5,
checkbit5,
lastmodify,
lasttime,
paytype,
ownerid,
reckonerid,
ifmodify,
cashtype,
subitemid,
account_date)
SELECT /*f_get_serial('IACC', i.compid)*/
i.vencusno||i.subitemid||to_char(sysdate,'yyyymmdd')||i.compid ,
'IACC' AS billcode,
i.compid,
'2000' AS busno,
i.vencusno AS vencusno,
168 buyer,
'期初库存导入' notes,
1 AS status,
SYSDATE-1 execdate,
168 checker1,
1 checkbit1,
NULL AS checker2,
1 AS checkbit2,
NULL AS checker3,
1 AS checkbit3,
NULL AS checker4,
1 AS checkbit4,
NULL AS checker5,
1 AS checkbit5,
168 AS lastmodify,
SYSDATE AS lasttime,
/* nvl((SELECT MAX(paytype)
FROM t_vencus b
WHERE b.compid = i.compid
AND b.vencusno = i.vencusno),*/
/* f_get_sys_inicode(i.compid, '3051', NULL))*/
max(i.paytype) AS paytype,
'01' AS ownerid,
'01' AS reckonerid,
0 AS ifmodify,
/* nvl((SELECT MAX(cashtype)
FROM t_vencus b
WHERE b.compid = i.compid
AND b.vencusno = i.vencusno),
f_get_sys_inicode(i.compid, '3050', NULL))*/max(i.cashtype) AS cashtype,
i.subitemid,
SYSDATE account_date
FROM T_CT_STORE_NEW_hjx i
GROUP BY i.compid/*,i.busno*/,i.vencusno,i.subitemid;
DELETE tmp_disable_trigger WHERE table_name='t_init_store_h';
-- DELETE FROM t_init_store_h WHERE notes='期初库存导入'
-- 回填中间表单据号码
CREATE INDEX idx_init_store_ct ON t_init_store_h(compid,vencusno,notes) ;
UPDATE T_CT_STORE_NEW_hjx a
SET a.initno = /*'100010202412271'*/
(SELECT b.initno
FROM t_init_store_h b
WHERE a.compid = b.compid
AND a.vencusno = b.vencusno
AND a.subitemid = b.subitemid
AND b.notes='期初库存导入'
);
drop index idx_init_store_ct ;
-- SELECT * FROM cd_store_new_02151 WHERE INITNO IS NULL;
--UPDATE cd_store_new_02151 SET INITNO = '';
/* END LOOP;*/
SELECT * FROM t_init_store_d
INSERT INTO tmp_disable_trigger VALUES ('t_init_store_d');
-- 产生期初入库明细
INSERT INTO t_init_store_d---138135
(initno,
rowno,
wareid,
purprice,
purtax,
saletax,
stallno,
makeno,
makedate,
invalidate,
invalidate_char,
batid,
midqty,
factoryid,
notes,
maxqty,
wareqty,
areacode,
batch_createtime,
bak3,bak4)
SELECT c.initno,
row_number() over(PARTITION BY c.initno ORDER BY c.warecode, purprice),
c.wareid,
purprice,
MAX(nvl(c.purtax, 0)),
MAX(w.saletax),
c.stallno AS stallno,
nvl(makeno,'/'),
max(makedate)，
max(INVALIDATE),
to_char(max(invalidate),'yyyymmdd'),
-- to_date(MAX(nvl(makedate, '1990-01-01')), 'yyyy-mm-dd'),
--to_date(MAX(invalidate), 'yyyy-mm-dd'),
-- to_char(to_date(MAX(invalidate), 'yyyy-mm-dd'), 'yyyymmdd') invalidate_char,
c.batid,
MAX(w.midqty),
MAX(nvl(c.factoryid, w.factoryid)),
'期初库存导入' notes,
MAX(w.maxqty),
SUM(wareqty) wareqty,
MAX(nvl(c.areacode, w.areacode)),
MAX(old_sys_time),
MAX(c.busno) /*将同批商品默认采购员写入明细备用字段，以方便写入批次表。无其他用途*/
,c.objbatid
FROM T_CT_STORE_NEW_hjx c
JOIN t_ware w
ON w.wareid = c.wareid
--暂时注释，正式切换取消注释 AND w.compid = c.compid
-- WHERE c.invalidate <>'NULL'
GROUP BY c.initno,
c.wareid,
c.warecode,
c.purprice,
c.makeno,
c.batid,c.busno,c.objbatid, c.stallno ;
DELETE tmp_disable_trigger WHERE table_name='t_init_store_d';
SELECT * FROM t_init_store_d
/*
SELECT * FROM t_store_i
truncate table t_store_i
*/
--库存主表
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_store_h');--115845
insert into T_STORE_H
(COMPID,BUSNO,WAREID,SUMQTY,AUTOCOMPUTEMAXSTORE,AUTOCOMPUTEMINSTORE,MAXDAY,MINDAY,MAXSTORE,MINSTORE,
STOREPURPRICE,STAMP,LASTMDISTQTY,AUTODISTAPPLY,LASTM2QTY,LASTM3QTY,LASTMQTY,LASTYQTY,OOSDAYS,
SUMAWAITQTY,SUMPENDINGQTY,SUMAWAITQTY_NOBATCH,LASTAPPLYQTY,LASTDISTQTY,LOWESTQTY,ONLINEEXTQTY,ALLOCQTY,RESERVEQTY,SUMDEFECTQTY,SUMTESTQTY)
select a.compid,a.busno,a.wareid,sum(a.wareqty),2,1,0,0,0,0,
nvl(max(a.purprice),0),0,0,0,0,0,0,0,0,
0,0,0, 0,0,0,0,0,0,0,0
from T_CT_STORE_NEW_hjx a
left join s_busi b on a.busno=b.busno AND a.compid=b.compid
where a.wareid is not NULL
group by a.compid,a.busno,a.wareid;
DELETE FROM tmp_disable_trigger WHERE table_name = 't_store_h';
--SELECT * FROM t_store_h;
--SELECT * FROM T_CT_STORE_NEW_hjx WHERE wareid = 106053 AND busno=1000;
--库存明细表（1）
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_store_d');
---delete from T_STORE_D;
insert into T_STORE_D---
(COMPID,BUSNO,STALLNO,WAREID,BATID,WAREQTY,AWAITQTY,PENDINGQTY,LASTMAINTAINDATE,STAMP)
/*插入实际库存 */
select a.compid,a.busno,max(nvl(trim(c.stallno),'11'||a.busno)),a.wareid,a.objbatid,sum(a.wareqty),0,0,null,0
from T_CT_STORE_NEW_hjx a LEFT JOIN s_busi b ON a.busno=b.busno AND a.compid=b.compid
LEFT JOIN t_stall c ON a.busno=c.busno AND c.stallno=trim(a.stallno) AND a.compid=b.compid --AND c.stalltype=11
where a.wareid is not NULL group by a.compid,a.busno,a.wareid,a.objbatid ;
---138135
/*插入门店库存存在、总部库存不存在的记录，数量为0*/
insert into T_STORE_D---225616
(COMPID,BUSNO,STALLNO,WAREID,BATID,WAREQTY,AWAITQTY,PENDINGQTY,LASTMAINTAINDATE,STAMP)
select 2,2000/*总部仓库业务机构编码*/,max(a.stallno)/*总部仓库的货位编码*/,a.wareid,a.srcbatid,0/*数量为0*/,0,0,null,0
from T_CT_STORE_NEW_hjx a
where a.busno<>2000/*总部仓库业务机构编码*/
and a.wareid is not NULL
group by a.busno,a.wareid,a.srcbatid;
--133448
delete from tmp_disable_trigger where table_name='t_store_d';
/*------------------------------------------3、生成门店配送记录--------------------------------------------------*/
--配送记录（直营，加盟的分开）
----直营店
/* INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_dist_h');--0
INSERT INTO t_dist_h--75
(distno,compid,billcode,srcbusno,objbusno,pickupno,splitno,addrid,notes,lastmodify,
lasttime,status,execdate,checker1,checkbit1,checkbit2,checkbit3,
checkbit4,checkbit5,stamp,paytype,cashtype)
select \*max(d.batid)||*\'DIS'||d.busno||to_char(SYSDATE, 'yymmdd')\*25475005190403*\,1,'DIS',1000\**\,d.busno,null,null,null,
'期初库存导入','168',sysdate,1,sysdate,'168',1,0,0,0,0,0,'01','01'
from T_CT_STORE_NEW_hjx d,s_busi s where d.busno=s.busno AND d.busno not in (9999,3003,5002) AND s.orgtype=20 group by d.busno;\*主表为啥要以批次分组？？*\
DELETE FROM tmp_disable_trigger WHERE table_name = 't_dist_h';
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_dist_d');--0
INSERT INTO t_dist_d
(distno,srcstallno,objstallno,batid,wareid,wareqty,rowno,makeno,makedate,purprice,purtax,
saletax,srcsaleprice,objsaleprice,invalidate,objbusnoqty, monsaleqty,avgpurprice,distprice,
groupid,pstflag,storeqty,notes,objbatid,acceptedqty,noticedqty)
select 'DIS'||a.busno||to_char(SYSDATE, 'yymmdd') ,
'119999',max(a.stallno),max( a.batid ),a.wareid,sum(a.wareqty),row_number() OVER(PARTITION BY a.busno ORDER BY a.wareid) AS rowno,max(a.makeno),
max(makedate),nvl(max( a.purprice ),0),nvl(max(b.purtax),13),
max(b.saletax),max(ts.saleprice),max(ts.saleprice),max(invalidate),0,0,
max( a.purprice ),max(a.purprice),null,null,0,null,a.batid,sum(a.wareqty),sum(a.wareqty)
from T_CT_STORE_NEW_hjx a join t_ware b on a.wareid=b.wareid
join s_busi busi on a.busno=busi.busno and b.compid=busi.compid
-- join t_stall s on busi.busno=s.busno
left join t_ware_saleprice ts on b.wareid=ts.wareid
and b.compid=ts.compid and busi.salegroupid=ts.salegroupid
where \*s.stalltype='11' and*\ a.busno not in (9999,3003,5002) AND busi.orgtype=20
group by a.busno,a.wareid,a.batid ;
---111403
DELETE FROM tmp_disable_trigger WHERE table_name = 't_dist_d';*/
----加盟店
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_dist_h');--71
INSERT INTO t_dist_h --8
(distno,compid,billcode,srcbusno,objbusno,pickupno,splitno,addrid,notes,lastmodify,
lasttime,status,execdate,checker1,checkbit1,checkbit2,checkbit3,
checkbit4,checkbit5,stamp,paytype,cashtype)
select /*max(d.batid)||*/'ADD'||d.busno||to_char(SYSDATE, 'yymmdd')/*25475005190403*/,2,'ADD',2000/**/,d.busno,null,null,null,
'期初库存导入','168',sysdate-1,1,sysdate-1,'168',1,0,0,0,0,0,'01','01'
from T_CT_STORE_NEW_hjx d,s_busi s where d.busno=s.busno AND d.busno<>2000 AND s.orgtype=30 group by d.busno;/*主表为啥要以批次分组？？*/
DELETE FROM tmp_disable_trigger WHERE table_name = 't_dist_h';
--select * from t_dist_h
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_dist_d');--133448
INSERT INTO t_dist_d
(distno,srcstallno,objstallno,batid,wareid,wareqty,rowno,makeno,makedate,purprice,purtax,
saletax,srcsaleprice,objsaleprice,invalidate,objbusnoqty, monsaleqty,avgpurprice,distprice,
groupid,pstflag,storeqty,notes,objbatid,acceptedqty,noticedqty)
select 'ADD'||a.busno||to_char(SYSDATE, 'yymmdd') ,
'112000',max(a.stallno),max( a.srcbatid ),a.wareid,sum(a.wareqty),row_number() OVER(PARTITION BY a.busno ORDER BY a.wareid) AS rowno,nvl(max(a.makeno),'/'),
max(makedate),nvl(max(a.purprice),0),nvl(max(b.purtax),13),
nvl(max(b.saletax),13),max(ts.saleprice),max(ts.saleprice),max(invalidate),0,0,
max( a.purprice ),max(a.purprice),null,null,0,null,a.objbatid,sum(a.wareqty),sum(a.wareqty)
from T_CT_STORE_NEW_hjx a join t_ware b on a.wareid=b.wareid
join s_busi busi on a.busno=busi.busno and b.compid=busi.compid
-- join t_stall s on busi.busno=s.busno
left join t_ware_saleprice ts on b.wareid=ts.wareid
and b.compid=ts.compid and busi.salegroupid=ts.salegroupid
where /*s.stalltype='11' and*/ a.busno<>2000 AND busi.orgtype=30
group by a.busno,a.wareid,a.srcbatid,a.objbatid ;
---129678
DELETE FROM tmp_disable_trigger WHERE table_name = 't_dist_d';
--SELECT * FROM t_init_store_d
/*------------------------------------------4、生成入库批次记录--------------------------------------------------*/
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_store_i');--140208
insert into T_STORE_I
(BATID,COMPID,WAREID,BILLCODE,BILLNO,ROWNO,VENCUSNO,INITQTY,PURTAX,PURPRICE,MAKENO,BUYER,MAKEDATE,
INVALIDATE,INVALIDATE_CHAR,CREATETIME,MAXQTY,MIDQTY,SRCBATID,FACTORYID,DISTPRICE,FLAG1,FLAG2,
INITBUSNO,BACKPRICE,STERILEMAKENO,STERILEINVALIDATE,SETPURPRICE1,SETPURPRICE2,AREACODE,PAYTYPE,
OWNERID,INITOWNERID,ORIG_BATCHNO,ORIG_IDNO,PRODUCER,ORIG_BUYER,ISSTOPSELLING,SALENAME,STAMP,CASHTYPE,saletax,subitemid,reckonerid)
---门店目标批次
select trunc(a.bak4),b.compid,a.wareid,'ADD',b.initno,max(a.rowno),b.vencusno,sum(a.wareqty),max(a.purtax),max(a.purprice),
max(a.makeno),max(b.buyer),max(a.makedate),max(a.invalidate),max(a.invalidate_char),nvl(max(a.batch_createtime),sysdate),max(a.maxqty),
max(a.midqty),a.batid,max(a.factoryid),max(a.purprice),0,0,max(b.busno),max(a.purprice),max(a.sterileno),max(a.sterileinvalidate),
null,null,max(a.areacode),max(b.paytype),b.ownerid,b.ownerid,null,null,null,null,0,null,0,max(b.cashtype),max(a.saletax),b.subitemid,'01'
from t_init_store_d a left join t_init_store_h b on a.initno=b.initno
where /*b.compid =1 and*/ a.batid not in (select batid from T_STORE_I) AND b.notes='期初库存导入'
and trunc(a.bak3)<>2000
group by a.batid,trunc(a.bak4),a.wareid,b.vencusno,b.initno,b.ownerid,b.compid,b.subitemid
/*
SELECT * FROM t_init_store_d */
union all
---原批次
select a.batid,b.compid,a.wareid,'IACC',b.initno,max(a.rowno),b.vencusno,sum(a.wareqty),max(a.purtax),max(a.purprice),
max(a.makeno),max(b.buyer),max(a.makedate),max(a.invalidate),max(a.invalidate_char),nvl(max(a.batch_createtime),sysdate),max(a.maxqty),
max(a.midqty),a.batid,max(a.factoryid),max(a.purprice),0,0,max(b.busno),max(a.purprice),max(a.sterileno),max(a.sterileinvalidate),
null,null,max(a.areacode),max(b.paytype),b.ownerid,b.ownerid,null,null,null,null,0,null,0,max(b.cashtype),max(a.saletax),b.subitemid,'01'
from t_init_store_d a left join t_init_store_h b on a.initno=b.initno
where /*b.compid =1 and*/ a.batid not in (select batid from T_STORE_I) AND b.notes='期初库存导入'
group by a.batid,trunc(a.bak4),a.wareid,b.vencusno,b.initno,b.ownerid,b.compid,b.subitemid;
DELETE FROM tmp_disable_trigger WHERE table_name = 't_store_i';
----------------------
---------------
delete t_store_d_bak;
insert into t_store_d_bak--275731
(period,compid,busno,wareid,batid,stallno,wareqty,purprice,avgpurprice,saletax,purtax)
select '202502'/*正式切换修改*/,a.compid,a.busno,a.wareid,a.batid,a.stallno,a.wareqty,b.purprice,0,b.saletax,b.purtax
from t_store_d a ,t_store_i b where a.wareid=b.wareid and a.batid=b.batid --AND a.compid=1;
--------进销存
----入库单
insert into t_store_inout_list
select h.compid,h.execdate,h.busno,d.wareid,h.billcode,d.wareqty as oldqty,d.purprice as oldvagprice,
d.wareqty as inqty,d.purprice as newprice,d.wareqty* d.purprice as avgsum,d.wareqty * d.purprice as actsum,0 as difsum,
d.purprice as newavgprice,seq_store_inout_list.nextval as inoutid,h.vencusno as objname,d.batid as batid,d.initno as billno,
d.makeno as makeno,d.stallno,e.zoneno,0 as computestatus,h.execdate as computedate,'' as whlprice,d.rowno,
f_get_factoryname(d.factoryid) as factoryname,f_get_areacode(d.areacode) as areaname,
f_get_classname('18',d.wareid,h.compid) as classname,'' as account_date,d.purtax/*,'期初库存导入' as notes*/
from t_init_store_h h
LEFT JOIN t_init_store_d d
ON h.initno = d.initno
LEFT JOIN t_stall e ON d.stallno=e.stallno AND e.compid=h.compid
WHERE h.notes like '%期初库存导入%' /*AND d.batid = 37686*/;
---总部配送
insert into t_store_inout_list--133448
select h.compid,h.execdate,h.srcbusno,d.wareid,h.billcode,d.wareqty as oldqty,
d.purprice as oldvagprice,-1*d.wareqty as inqty,d.purprice as newprice,-1*d.wareqty * d.purprice as avgsum,
-1*d.wareqty * d.purprice as actsum,0 as difsum,d.purprice as newavgprice,seq_store_inout_list.nextval as inoutid,
h.objbusno as objname,d.batid as batid,d.distno as billno,d.makeno as makeno,d.srcstallno,'' as zoneno,0 as computestatus,
h.execdate as computedate,'' as whlprice,d.rowno ,f_get_factoryname(i.factoryid) as factoryname,
f_get_areacode(i.areacode) as areaname,f_get_classname('18',d.wareid,h.compid) as classname,'' as account_date,d.purtax/*,'期初库存导入' as notes*/
from t_dist_h h,t_dist_d d,t_store_i i
where h.distno = d.distno and h.notes like '%期初库存导入%' and d.wareid = i.wareid and d.batid = i.batid
and h.compid = i.compid and h.billcode IN ('DIS','ADD');
-----门店入库(配送单)
insert into t_store_inout_list--133448
select h.compid,h.execdate,h.objbusno,d.wareid,h.billcode ,0 as oldqty,
d.purprice as oldvagprice,d.wareqty as inqty,d.purprice as newprice,d.wareqty * d.purprice as avgsum,
d.wareqty * d.purprice as actsum,0 as difsum,d.purprice as newavgprice,seq_store_inout_list.nextval as inoutid,
h.objbusno as objname,d.OBJbatid,d.distno as billno,
d.makeno as makeno,d.objstallno as stallno,'' as zoneno,0 as computestatus,h.execdate as computedate,'' as whlprice,
d.rowno,f_get_factoryname(i.factoryid) as factoryname,f_get_areacode(i.areacode) as areaname,
f_get_classname('18',d.wareid,h.compid) as classname,'' as account_date,d.purtax/*,'期初库存导入' as notes*/
from t_dist_h h,t_dist_d d,t_store_i i
where h.distno = d.distno and h.notes like '%期初库存导入%' and h.billcode IN ('DIS','ADD')
and d.wareid = i.wareid and d.batid = i.batid and h.compid = i.compid;
-----门店入库(委托配送单)
insert into t_store_inout_list--0
select h.compid,h.execdate,h.objbusno,d.wareid,h.billcode ,0 as oldqty,
d.purprice as oldvagprice,d.wareqty as inqty,d.purprice as newprice,d.wareqty * d.purprice as avgsum,
d.wareqty * d.purprice as actsum,0 as difsum,d.purprice as newavgprice,seq_store_inout_list.nextval as inoutid,
h.objbusno as objname,d.objbatid as batid,d.distno as billno,
d.makeno as makeno,d.objstallno as stallno,'' as zoneno,0 as computestatus,h.execdate as computedate,'' as whlprice,
d.rowno,f_get_factoryname(i.factoryid) as factoryname,f_get_areacode(i.areacode) as areaname,
f_get_classname('18',d.wareid,h.compid) as classname,'' as account_date,d.purtax/*,'期初库存导入' as notes*/
from t_dist_h h,t_dist_d d,t_store_i i
where h.distno = d.distno and h.notes like '%期初库存导入%' and h.billcode='DET'
and d.wareid = i.wareid and d.objbatid = i.batid and h.compid = i.compid;
----批发销售单
insert into t_store_inout_list--0
select h.compid,h.execdate,h.busno,d.wareid,h.billcode,d.wareqty as oldqty,
d.purprice as oldvagprice,-1*d.wareqty as inqty,d.purprice as newprice,-1*d.wareqty * d.purprice as avgsum,
-1*d.wareqty * d.purprice as actsum,0 as difsum,d.purprice as newavgprice,seq_store_inout_list.nextval as inoutid,
h.vencusno as objname,d.batid as batid,d.batsaleno as billno,d.makeno as makeno,d.stallno,'' as zoneno,0 as computestatus,
h.execdate as computedate,d.whlprice as whlprice,d.rowno ,f_get_factoryname(i.factoryid) as factoryname,
f_get_areacode(i.areacode) as areaname,f_get_classname('18',d.wareid,h.compid) as classname,'' as account_date,d.purtax,'期初库存导入' as notes
from t_batsale_h h,t_batsale_d d,t_store_i i
where h.batsaleno=d.batsaleno and h.notes like '%期初库存导入%'
and d.wareid = i.wareid and d.batid = i.batid and h.compid = i.compid;
/*
-----入库单
begin
for inout in (select row_number() over( order by billno ) as idnum,a.billno,a.execdate,
a.execdate-24+(row_number() over( order by billno ))/(24*60) as execdate_new
from
(select distinct billno,execdate
from t_store_inout_list
where billcode = 'ACC' and notes='期初生成_广生德' \*AND trunc(Execdate)>=trunc(SYSDATE)*\) a)
loop
update t_store_inout_list
set execdate = inout.execdate_new
where t_store_inout_list.billno = inout.billno and t_store_inout_list.execdate = inout.execdate;
end loop;
end;
commit;
---配送单
begin
for inout in (select row_number() over( order by billno ) as idnum,a.billno,a.execdate,
a.execdate-13+(row_number() over( order by billno ))/(24*60) as execdate_new
from
(select distinct billno,execdate
from t_store_inout_list
where billcode = 'DIS' and notes='期初生成_广生德') a)
loop
update t_store_inout_list
set execdate = inout.execdate_new
where t_store_inout_list.billno = inout.billno and t_store_inout_list.execdate = inout.execdate;
end loop;
end;
commit;
----批发销售单
begin
for inout in (select row_number() over( order by billno ) as idnum,a.billno,a.execdate,
a.execdate-12+(row_number() over( order by billno ))/(24*60) as execdate_new
from
(select distinct billno,execdate
from t_store_inout_list
where billcode = 'WHL' and notes='期初生成_广生德') a)
loop
update t_store_inout_list
set execdate = inout.execdate_new
where t_store_inout_list.billno = inout.billno and t_store_inout_list.execdate = inout.execdate;
end loop;
end;
commit;
---委托配送单
begin
for inout in (select row_number() over( order by billno ) as idnum,a.billno,a.execdate,
a.execdate-11+(row_number() over( order by billno ))/(24*60) as execdate_new
from
(select distinct billno,execdate
from t_store_inout_list
where billcode = 'DET' and notes='期初生成_广生德') a)
loop
update t_store_inout_list
set execdate = inout.execdate_new
where t_store_inout_list.billno = inout.billno and t_store_inout_list.execdate = inout.execdate;
end loop;
end;
commit;
*/
MERGE INTO t_vendor_ware a
USING (SELECT b.vencusno,
b.wareid,
MAX(b.paytype) AS paytype,
MIN(b.purprice) AS purprice,
MAX(c.lastsaleprice) AS saleprice,
MAX(b.buyer) AS buyer,
MAX(b.createtime) AS createtime,
b.compid,
MAX(b.purtax) AS purtax,
MAX(b.cashtype) AS cashtype,
b.subitemid
FROM t_store_i b,
t_ware c
WHERE b.compid = c.compid
AND b.wareid = c.wareid
GROUP BY b.vencusno,
b.wareid,
b.compid,
b.subitemid) b
ON (a.compid = b.compid AND a.vendorno = b.vencusno AND a.wareid = b.wareid AND a.subitemid = b.subitemid)
WHEN NOT MATCHED THEN
INSERT
(vendorno,
wareid,
paytype,
purprice,
saleprice,
buyer,
backtype,
lastdate,
purstatus,
busno,
compid,
purtax,
cashtype,
subitemid)
VALUES
(b.vencusno,
b.wareid,
b.paytype,
b.purprice,
b.saleprice,
b.buyer,
0,
b.createtime,
1,
f_get_sys_inicode(compid, '3019', NULL),
b.compid,
b.purtax,
b.cashtype,
b.subitemid);
---更新单据编号中批次的编码
update s_bill a
set a.serialno =
(select max(batid) + 1 from t_store_i)
where billcode = 'BAT';
-----执行计算进销存的存储过程
begin
proc_store_inout_remain_opt;
end;
BEGIN
dbms_stats.gather_schema_stats(ownname=> 'H2',estimate_percent => 0.00001);
END;
select sum(/*i.purprice**/d.wareqty) from t_store_d d
join t_store_i i on d.wareid=i.wareid and d.batid=i.batid
where d.compid=2 ;--2032894.26
SELECT 8777088.77435012 -9241769.57435012 from dual
SELECT sum(purprice* wareqty) FROM T_CT_STORE_NEW_hjx where busno = 1001
select sum(i.purprice*d.wareqty) from t_store_d d
join t_store_i i on d.wareid=i.wareid and d.batid=i.batid
where d.compid=2 and d.busno = 1001
SELECT sum(wareqty ) FROM T_CT_STORE_NEW_hjx where wareid = '100271'
SELECT sum(wareqty) FROM t_store_d where busno = 1057
SELECT wareid , sum(wareqty ) , sum(purprice* wareqty) FROM T_CT_STORE_NEW_hjx where wareid not in (select wareid from t_store_i ) group by wareid
SELECT * FROM t_store_i where wareid = '100271'
SELECT * FROM t_store_d
select sum(i.purprice*d.wareqty) from t_store_d d
join t_store_i i on d.wareid=i.wareid and d.batid=i.batid
where d.compid=1 ;
select sum(a.进价*a.库存数量) from D_KKDR_TT a where a.compid=1;--9862982.606402
select 2032894.26-2032894.26 from dual;--数量
select 9862982.606402-9862982.606402 from dual;--金额
/*****************************************************************正式切换结束********************************************************/
/*
SELECT * FROM t_factory a WHERE LENGTH(a.factoryname)>80;
UPDATE t_store_inout_list a SET a.factoryname=TRIM(a.factoryname)
*/
零库存导入
==按照下面方式导入的时候需修改触发器TR_T_INIT_STORE_H== ==将t_dist_d表生成的时候下面 wareqty> 0 的条件去掉==
declare
v_initno t_init_store_h.initno%TYPE;
v_distno t_dist_h.distno%TYPE;
v_billcode t_dist_h.billcode%TYPE;
v_stamp t_store_i.stamp%TYPE;
v_count number;
v_ownerid t_ct_store_new.ownerid%TYPE;
BEGIN
/* --将中间表商品编码、供来单位编码内码填入
UPDATE t_ct_store_new a
SET wareid =
(SELECT wareid FROM t_ware_base b WHERE a.warecode = b.warecode),
vencusno =
(SELECT vencusno FROM t_vencus_base b WHERE a.vencuscode = b.vencuscode),
subitemid = nvl(subitemid, 0);*/
-- 屏蔽触发器
INSERT INTO tmp_disable_trigger VALUES ('t_init_store_h');
INSERT INTO tmp_disable_trigger VALUES ('t_init_store_d');
delete from T_CT_STORE_NEW_hjx where vencusno is null;
update T_CT_STORE_NEW_hjx set initno = null ;
--=============================================================================期初入库单开始==============
-- 产生期初入库单主表
FOR i IN (SELECT DISTINCT busno, compid, vencusno, subitemid, '01'as ownerid FROM T_CT_STORE_NEW_hjx where busno not in ('2000','1001')) LOOP
-- 取得单据号
v_initno := f_get_serial('IACC', i.compid);
v_ownerid := i.ownerid;
SELECT count(1)
INTO v_count
FROM s_busi s_busi
WHERE s_busi.busno = i.busno and s_busi.compid = i.compid and s_busi.status = 1;
INSERT INTO t_init_store_h
(initno, billcode, compid, busno, vencusno, buyer, notes, status, execdate, checker1, checkbit1,
checker2, checkbit2, checker3, checkbit3, checker4, checkbit4, checker5, checkbit5, lastmodify,
lasttime, paytype, ownerid, reckonerid, ifmodify, cashtype, subitemid, account_date)
SELECT v_initno, 'IACC' AS billcode, i.compid, i.busno AS busno, i.vencusno AS vencusno,
168 buyer, '期初库存导入零批次2次' notes, 0 AS status, SYSDATE execdate, 168 checker1, 1 checkbit1,
NULL AS checker2, 1 AS checkbit2, NULL AS checker3, 1 AS checkbit3, NULL AS checker4,
1 AS checkbit4, NULL AS checker5, 1 AS checkbit5, 168 AS lastmodify, SYSDATE AS lasttime,
nvl((SELECT MAX(paytype)
FROM t_vencus b
WHERE b.compid = i.compid AND b.vencusno = i.vencusno),
f_get_sys_inicode(i.compid, '3051', NULL)) AS paytype, i.ownerid AS ownerid,
'01' AS reckonerid, 0 AS ifmodify,
nvl((SELECT MAX(cashtype)
FROM t_vencus b
WHERE b.compid = i.compid AND b.vencusno = i.vencusno),
f_get_sys_inicode(i.compid, '3050', NULL)) AS cashtype, i.subitemid,
SYSDATE account_date
FROM dual;
-- 回填中间表单据号码
UPDATE T_CT_STORE_NEW_hjx
SET initno = v_initno
WHERE compid = i.compid AND vencusno = i.vencusno AND subitemid = i.subitemid and busno = i.busno;
END LOOP;
-- 产生期初入库明细
INSERT INTO t_init_store_d
(ORIG_BATCHNO,ORIG_IDNO,initno, rowno, wareid, purprice, purtax, saletax, stallno, makeno, makedate, invalidate,
invalidate_char, batid, midqty, factoryid, notes, maxqty, wareqty, areacode, batch_createtime, bak3)
SELECT c.ORIG_BATCHNO,c.ORIG_IDNO,c.initno, row_number() over(PARTITION BY c.initno ORDER BY c.warecode, purprice), c.wareid,
purprice, MAX(c.purtax), MAX(w.saletax),'11'||busno stallno, makeno,
MAX(makedate), MAX(invalidate), MAX(to_char(invalidate, 'yyyymmdd')) invalidate_char, 0,
MAX(w.midqty), MAX(nvl(c.factoryid,w.factoryid)), '期初库存导入零批次2次' notes, MAX(w.maxqty), SUM(wareqty) wareqty,
MAX(nvl(c.areacode,w.areacode)), MAX(old_sys_time), MAX(c.buyer) /*将同批商品默认采购员写入明细备用字段，以方便写入批次表。无其他用途*/
FROM T_CT_STORE_NEW_hjx c
JOIN t_ware w
ON w.compid = c.compid AND w.wareid = c.wareid
where c.busno not in ('2000','1001')
GROUP BY c.ORIG_BATCHNO,c.ORIG_IDNO,c.initno, c.wareid, c.warecode, purprice, makeno,busno;
DELETE FROM tmp_disable_trigger WHERE table_name = 't_init_store_h';
DELETE FROM tmp_disable_trigger WHERE table_name = 't_init_store_d';
END;
----------------更新表状态生成空批次-----------------------
/*
declare
begin
for rec in (select initno
from t_init_store_h
where notes = '期初库存导入零批次2次'
and status = 0) loop
update t_init_store_h set status = 1 where initno = rec.initno;
commit;
end loop;
end;*/
/*
SELECT * FROM T_CT_STORE_NEW_hjx
SELECT count(*) FROM t_init_store_h
update T_CT_STORE_NEW_hjx set initno = null*/