收款的时候判断是否有处方药，如果有要弹出医师药师录入账号密码校验
If Long(lastreturnvalue) <> 0 Then Return Long(lastreturnvalue)
//收款的时候判断是否有处方药，如果有要弹出医师药师录入账号密码校验
String ls_3071,ls_3072,ls_classcode,ls_busno
ls_busno = gnvo_app.of_getini("posset","busno","0")
ls_3071 = ','+ P19#is_3071 +','
ls_3072 = ','+ p19#is_3072 +','
//p33#ids_classcode.SetFilter("")
//p33#ids_classcode.SetFilter("classgroupno='"+p19#is_3070+"'")
//p33#ids_classcode.Filter()
Long i,ll_cfy,ll_find,ll_wareid
String ls_prescription_flag,ls_saleno
ll_cfy = 0
//ls_saleno = This.Object.saleno[This.GetRow()]
//ls_saleno = dw_show.GetItemString(i,'saleno')
For i = 1 To dw_show.RowCount()
// ll_wareid = dw_show.GetItemNumber(i,'wareid')
// ll_find = p33#ids_classcode.Find("wareid="+String(ll_wareid),1,p33#ids_classcode.RowCount())
// ls_classcode = p33#ids_classcode.GetItemString(ll_find,'classcode')
// ls_classcode = Mid(ls_classcode,1,4)
// ls_classcode = ','+ls_classcode+','
// If Pos(ls_3071,ls_classcode) > 0 Or Pos(ls_3072,ls_classcode) > 0 Then
// ll_cfy = 1
// Exit
// End If
ls_prescription_flag = dw_show.GetItemString(i,'prescription_flag')
ls_prescription_flag = ','+ls_prescription_flag+','
If Pos(ls_3071,ls_prescription_flag) > 0 Or Pos(ls_3072,ls_prescription_flag) > 0 Then
ll_cfy = 1
Exit
End If
Next
str_trans lstr_trans
window lw_win
String ls_sql[],ls_err,ls_ext_str1,ls_retparm
str_exec_args lstr_args[]
//本单有处方药，弹框登记
If ll_cfy = 1 Then
//取是否已审核
ls_ext_str1 = dw_h.GetItemString(1,'ext_str1')
If Not IsNull(ls_ext_str1) And Len(ls_ext_str1) > 0 Then
//If MessageBox('提示','本单含有处方药，当前已有审核药师记录，是否需要重新审核?', Question!, YesNo!) <> 1 then
Return 0
End If
//MessageBox('提示','本单有处方药销售，请药师进行审核登记',None!,OK!,1,3)
lstr_trans.ext1 = '130 ' + String(gl_userid)
gf_openwithparm(lw_win,'w_identity_check','w_response',lstr_trans)
ls_retparm = Message.StringParm //成功后返回的用户编码
If ls_retparm = "" Then
MessageBox('提示','没有赋予权限不允许销售处方药!')
Return -1
End If
//判断录入的药师是否是本门店药师
ls_sql[1] = "select count(1) from t_remote_doctor where userid = "+ls_retparm+" and instr(busno_docs,'"+ls_busno+"')>0"
lstr_args[1].DataType = {"number"}
If Not gnvo_datasource.of_exec(ls_sql , lstr_args , ls_err) Then
MessageBox("取是否药师失败",ls_err,stopsign!,OK!,1)
Return -1
End If
//2023年8月1日00点21分
//2023年11月6日取消注释
If lstr_args[1].ReturnValue[1] = 0 Then
MessageBox('提示','录入人员不是本门店驻店药师，不允许处方药审核！')
Return -1
End If
//审核成功，给界面审核药师赋值，使用ext_str1扩展字段
dw_h.SetItem(1,'ext_str1',ls_retparm)
ls_sql[2] = "begin " + '~r~n'
// ls_sql[0] = "select count(1) from t_remote_doctor where userid = "+ls_retparm+" and instr(busno_docs,'"+ls_busno+"')>0"
ls_sql[2] += "INSERT INTO cd_sale_cf (saleno, userid, checkbit) VALUES('"+is_saleno+"', '"+String(ls_retparm)+"',1) ;"+ '~r~n'
ls_sql[2] += "end;"
// lstr_args[0].DataType = {"number"}
If Not gnvo_datasource.of_exec(ls_sql, ls_err) Then
MessageBox("插入数据失败！",ls_err,stopsign!,OK!,1)
Return -1
End If
End If
Return 0
新增表格 cd_sale_cf
create table CD_SALE_CF
(
saleno VARCHAR2(40) not null,
userid VARCHAR2(10) not null,
checkbit NUMBER(10)
)
存储过程
CREATE OR REPLACE PROCEDURE CPROC_更新处方药师 AS
BEGIN
-- 更新t_sale_mes_customer表的notes字段
FOR rec IN
(SELECT saleno
FROM t_sale_mes_customer
WHERE YAOSHI IS NULL) -- 只更新尚未更新notes的记录
LOOP
DECLARE
v_userid VARCHAR2(100); -- 用于存储userid
BEGIN
-- 查询cd_sale_cf表，找到与t_sale_mes_customer表中saleno对应的userid
SELECT userid
INTO v_userid
FROM cd_sale_cf
WHERE saleno = rec.saleno
AND ROWNUM = 1; -- 假设saleno是唯一的，若可能多个记录，使用ROWNUM来限制
-- 更新t_sale_mes_customer表的notes字段
UPDATE t_sale_mes_customer
SET YAOSHI = nvl(v_userid,1)
WHERE saleno = rec.saleno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- 如果在cd_sale_cf表中没有找到对应的userid，不做处理
NULL;
WHEN OTHERS THEN
-- 捕获其他错误并记录
RAISE_APPLICATION_ERROR(-20001, '存储过程执行出错: ' || SQLERRM);
END;
END LOOP;
-- 提交事务
COMMIT;
END ;
增加作业
BEGIN
DBMS_SCHEDULER.create_job (
job_name => '更新处方药师', -- 作业名称
job_type => 'PLSQL_BLOCK', -- 作业类型为PL/SQL块
job_action => 'BEGIN CPROC_更新处方药师; END;', -- 调用存储过程
start_date => SYSTIMESTAMP, -- 作业开始时间
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', -- 每分钟执行一次
enabled => TRUE, -- 启动作业
comments => '每分钟执行一次更新客户表notes字段的作业'
);
END;
/
集成到方案里面的按钮隐藏
‘加一个postopen事件
方案号#控件名(去掉$$).visible=false
p25#cb_psd.visible=false’
库存导入
select * from
cd_store_new_0215
SELECT * FROM cd_store_new_0215;
update cd_store_new_0215 set FACTORYID =
---SELECT * FROM t_ct_store_new
--delete from cd_store_new_0215;--真几把慢
--truncate table cd_store_new_0215;
SELECT * FROM cd_store_new_0215 where WAREQTY < 0 ;
delete from cd_store_new_0215 where WAREQTY < 0 ;
SELECT count(1) FROM cd_store_new_0215 where MAKEDATE is null ;
SELECT count(1) FROM cd_store_new_0215 where INVALIDATE is null ;
SELECT count(1) FROM cd_store_new_0215 where makeno is null ;
delete from cd_store_new_0215 where makeno is null ;
update cd_store_new_0215 set INVALIDATE = to_date('2099-12-31','yyyy-mm-dd') where INVALIDATE is null ;
---update cd_store_new_0215 set MAKEDATE = to_date('1970-01-01','yyyy-mm-dd') where MAKEDATE is null ;
update cd_store_new_0215 set MAKEDATE = old_sys_time where MAKEDATE is null ;
delete from cd_store_new_0215 where INVALIDATE < sysdate ;
SELECT count(1) FROM cd_store_new_0215 where busno is null ;
update cd_store_new_0215 set busno = '2000' where busno = '001'
SELECT count(1) FROM cd_store_new_0215 where busno = '2000'
SELECT count(1) FROM cd_store_new_0215 where warecode is null;
--更新的和一块了 其实不和一块更好一些
UPDATE cd_store_new_0215 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 cd_store_new_0215 a set STALLNO = '112000' where STALLNO = '0101' ;
SELECT * FROM s_bill where billcode = 'BAT'
update s_bill set SERIALNO = 100000 where billcode = 'BAT';
/*ALTER TABLE cd_store_new_0215 ADD batid NUMBER ;*/
SELECT * FROM cd_store_new_0215
UPDATE cd_store_new_0215 a SET a.batid=100000+ROWNUM;
--UPDATE cd_store_new_0215 a SET a.batid = ROWNUM;
UPDATE cd_store_new_0215 a SET a.srcbatid = batid;
SELECT MAX(SRCBATID) FROM cd_store_new_0215;
UPDATE cd_store_new_0215 a SET a.objbatid=178358+ROWNUM;
SELECT MAX(objbatid) FROM cd_store_new_0215;
UPDATE s_bill SET serialno=256716 WHERE billcode='BAT';
select t.srcbatid,t.objbatid from cd_store_new_0215 t;
select batid,count(1) from cd_store_new_0215 group by batid having count(1)>1;
select * from cd_store_new_0215 where busno=2000;
update cd_store_new_0215 a set a.objbatid=SRCBATID where busno=2000;
update cd_store_new_0215 a set a.purprice=3.333333,a.addpurprice=3.333333 where a.purprice='3.3333333333E10'
------库存导入
SELECT paytype,cashtype,vencusno FROM cd_store_new_0215
update cd_store_new_0215 set paytype = '01' where paytype is null ;
update cd_store_new_0215 set cashtype = '01' where cashtype is null ;
SELECT paytype,cashtype,vencusno FROM cd_store_new_0215 where vencusno is null
update cd_store_new_0215 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,
'1000' 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 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 cd_store_new_0215 where wareid = '100271'
SELECT sum(wareqty) FROM t_store_d where busno = 1001
SELECT wareid , sum(wareqty ) , sum(purprice* wareqty) FROM cd_store_new_0215 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)
*/