-- Create table
create global temporary table TTMPSALPACKPLU_TMPlsl
(
ORGCODE VARCHAR2(10),
SALENO VARCHAR2(20),
XSDATE DATE,
PAGENO NUMBER(10),
LNNO NUMBER(10),
PLUID NUMBER(19),
PLUCODE VARCHAR2(20),
PLUNAME VARCHAR2(40),
EXPLUCODE VARCHAR2(20),
BARCODE VARCHAR2(20),
UNIT VARCHAR2(6),
SPEC VARCHAR2(40),
PRICE NUMBER(19,4),
FSPRICE NUMBER(19,4),
XSCOUNT NUMBER(19,4),
YSTOTAL NUMBER(19,2),
SSTOTAL NUMBER(19,2),
YHTOTAL NUMBER(19,2),
XFTYPE VARCHAR2(1),
VIPCARDNO VARCHAR2(40),
CXBILLNO VARCHAR2(20),
HYBILLNO VARCHAR2(20)
)
On Commit Delete Rows;-- Create table
create global temporary table TTMPSALPACKMXPLU_TMPlsl
(
ORGCODE VARCHAR2(10),
SALENO VARCHAR2(20),
XSDATE DATE,
PAGENO NUMBER(10),
LNNO NUMBER(10),
PACKCODE VARCHAR2(20),
PLUID NUMBER(19),
PLUCODE VARCHAR2(20),
PLUNAME VARCHAR2(40),
EXPLUCODE VARCHAR2(20),
BARCODE VARCHAR2(20),
UNIT VARCHAR2(6),
SPEC VARCHAR2(40),
DEPID NUMBER(19),
DEPCODE VARCHAR2(10),
DEPNAME VARCHAR2(100),
PRICE NUMBER(19,4),
FSPRICE NUMBER(19,4),
XSCOUNT NUMBER(19,4),
SSTOTAL NUMBER(19,2),
XFTYPE VARCHAR2(1),
VIPCARDNO VARCHAR2(40),
CXBILLNO VARCHAR2(20),
HYBILLNO VARCHAR2(20)
)
on commit Delete rows;
-----整理商品包
Insert into TTMPSALPACKPLU_TMPlsl nologging (OrgCode,SaleNo,XsDate,PageNo,LnNo,PluID,PluCode,PluName,ExPluCode,
BarCode,Unit,Spec,
Price,FsPrice,XsCount,YsTotal,SsTotal,YhTotal,XfType,
VipCardNo,CxBillNo,HyBillNo)
Select A.OrgCode,A.SaleNo,A.XsDate,A.PageNo,A.LnNo,A.PluID,A.PluCode,A.PluName,A.ExPluCode,
A.BarCode,A.Unit,A.Spec,
A.Price,A.FsPrice,A.XsCount,A.YsTotal,A.SsTotal,A.YhTotal,A.XfType,
B.VipCardNo,A.CxBillNo,A.HyBillNo
From tSalSalePlu A,tSalSale B
Where A.TranType='1' and A.PackType='2'
and B.OrgCode=A.OrgCode and B.SaleNo=A.SaleNo;
--对商品包数据进行拆包
Insert into tTmpSalPackMxPlu_Tmplsl nologging (OrgCode,SaleNo,XsDate,PageNo,LnNo,
PackCode,PluID,PluCode,PluName,ExPluCode,BarCode,Unit,Spec,
DepID,DepCode,DepName,Price,FsPrice,XsCount,SsTotal,
XfType,VipCardNo,CxBillNo,HyBillNo)
Select A.OrgCode,A.SaleNo,A.XsDate,A.PageNo,A.LnNo,
A.PluID as PackCode,B.PluID,B.PluCode,B.PluName,B.ExPluCode,B.BarCode,B.Unit,B.Spec,
0 as DepID,null as DepCode,null as DepName,B.Price,B.Price as FsPrice,--下面更新部门和售价
A.XsCount*B.PackageCount as XsCount,Round(A.SsTotal*B.PriceRate/100.0,2) as SsTotal,
A.XfType,A.VipCardNo,A.CxBillNo,A.HyBillNo
From tTmpSalPackPlu_Tmplsl A,tSkuPluPackageBody B
Where B.PackageID=A.PluID;--更新拆包后明细商品的部门和售价
Update tTmpSalPackMxPlu_Tmplsl A
Set (DepID,DepCode,DepName,Price,FsPrice)
=(Select B.DepID,(Select DepCode From tOrgDept Where DepID=B.DepID) as DepCode,
(Select DepName From tOrgDept Where DepID=B.DepID) as DepName,
B.Price,B.Price
From tSkuPluEx B
where B.OrgCode=A.OrgCode and B.PluID=A.PluID)
Where Exists(Select 1 From tSkuPluEx B
where B.OrgCode=A.OrgCode and B.PluID=A.PluID);
select Rpt.OrgCode, Org.OrgName, SUBSTR(Rpt.DepCode, 1, 2) as DepCode, Dep.DepName as DepName,
sum(Rpt.XsCount) as XsCount,
sum(Rpt.YsTotal) as YsTotal,
sum(Rpt.SsTotal) as SsTotal,
sum(Rpt.YhTotal) as YhTotal
from ( Select ORGCODE ,SALENO,XSDATE,PAGENO,LNNO,PACKCODE,
PLUID,PLUCODE,PLUNAME,EXPLUCODE,BARCODE,UNIT,
SPEC,DEPID,DEPCODE,DEPNAME,PRICE,FSPRICE,XSCOUNT,
SSTOTAL,XFTYPE,FsPrice*XsCount-SsTotal As YhTotal,FsPrice*XsCount As YsTotal
from TTMPSALPACKMXPLU_TMPlsl
Union All
Select ORGCODE ,SALENO,XSDATE,PAGENO,LNNO,PACKCODE,
PLUID,PLUCODE,PLUNAME,EXPLUCODE,BARCODE,UNIT,
SPEC,DEPID,DEPCODE,DEPNAME,PRICE,FSPRICE,XSCOUNT,
SSTOTAL,XFTYPE ,YhTotal,YsTotal
from tSalSalePlu
Where Packtype<>'2'
) Rpt,
tOrgManage Org, tOrgDept Dep
where Rpt.OrgCode=Org.OrgCode
and Rpt.OrgCode=Dep.OrgCode
And Org.OrgCode=Dep.OrgCode
and SUBSTR(Rpt.DepCode, 1, 2)=Dep.DepCode and dep.isactive=1
And Exists (Select * From tSalSale S Where S.OrgCode=Rpt.OrgCode And S.saleNo=Rpt.saleNo And S.JzDate Is Null)---只查询未结账流水
Group by Rpt.OrgCode, Org.OrgName, SUBSTR(Rpt.DepCode, 1,2), Dep.DepName;drop table TTMPSALPACKPLU_TMPlsl;
drop table TTMPSALPACKMXPLU_TMPlsl;Commit ;如题
解决方案 »
- PLSQL无法远程连接!!!!!
- 如何分析2个用户下的对象的差异?
- 数据库服务器经常出现CPU占用率100%的情况,联入数据库的有40多个用户,主要在运行数据量比较大的报表时出现这个现象,怎么优化报表程序都难以改善
- 如何查询谁删除了某张表数据
- 分组排序的问题
- 使用database configuration assistant删除数据库后,managerment server无法刷新
- c#程序向ORACLE传SQL,SQL的字符串太长,出错,该如何更改ORACLE配置?
- 这句sql有问题吗?
- NO_EXPAND是怎么操作的
- Oracle错误:表达式类型错误
- 创建一个视图遇到问题,望给予帮肋!谢谢
- oracle的存储过程中关与锁技术的运用求助。
然后使用ADOQuery 或者Query,使用ExecSQL方法执行才可以!