CREATE OR REPLACE Procedure sRptRPT90000007002
(SysOrgCode In VarChar2,
SysUserId In Number,
SysUserCode In VarChar2,
SysUserName In VarChar2,
UParamjOrgCode in Varchar2,
UParamjDepCode in Varchar2,
UParamjBgnDate in Date,
UParamjEndDate in Date,
UParamjsLev in Varchar2,
Result_Set Out pxt_db.cCurType )
Is cOrgCode varchar2(20);
cDepCode varchar2(20);
cBgnDate varchar2(10);
cEndDate varchar2(10);
cLev varchar2(10);
cLen int ;
cSql varchar2(8000);
cOrgtype varchar2(20);
----初始化变量
cOrgCode:=:jOrgCode;
cDepCode:=:jDepCode||'%';
cBgnDate:=to_char(:jBgnDate,'YYYY-MM-DD');
cEndDate:=to_char(:jEndDate,'YYYY-MM-DD');
cLev:=:jsLev;
-----判断门店
Select OrgCode,OrgType Into cOrgCode,cOrgtype From tOrgManage Where OrgCode =cOrgCode ;
If cOrgtype In ('2002') Then
Raise_Application_Error(-20001,'请选择一个门店或分部');
End If;
-----判断日期
If cBgnDate>cEndDate then
Raise_application_Error(-20009,'开始日期应小于结束日期!');
End If;
--查询部门编码长度
Select sum(LvlLength) into clen
From tSysLevel
Where LvlType ='0' and LvlNo<=cLev and isused='1';
clen:=2*cLev;
cSql:= '
Select orgcode,DepCode,
Sum(Decode(JyMode,''0'',XsCount,0)) As JxXsCount,
Sum(Decode(JyMode,''0'',HXsTotal,0)) As JxHXsTotal,Sum(Decode(JyMode,''0'',WXsTotal,0)) As JxWXsTotal,
Sum(Decode(JyMode,''0'',HXsCost,0)) As JxHXsCost,Sum(Decode(JyMode,''0'',WXsCost,0)) As JxWXsCost,
Sum(Decode(JyMode,''0'',HMTotal,0)) As JxHMTotal,Sum(Decode(JyMode,''0'',WMTotal,0)) As JxWMTotal,
Sum(Decode(JyMode,''1'',XsCount,0)) As DxXsCount,
Sum(Decode(JyMode,''1'',HXsTotal,0)) As DxHXsTotal,Sum(Decode(JyMode,''1'',WXsTotal,0)) As DxWXsTotal,
Sum(Decode(JyMode,''1'',HXsCost,0)) As DxHXsCost,Sum(Decode(JyMode,''1'',WXsCost,0)) As DxWXsCost,
Sum(Decode(JyMode,''1'',HMTotal,0)) As DxHMTotal,Sum(Decode(JyMode,''1'',WMTotal,0)) As DxWMTotal,
Sum(Decode(JyMode,''2'',XsCount,0)) As LxXsCount,
Sum(Decode(JyMode,''2'',HXsTotal,0)) As LxHXsTotal,Sum(Decode(JyMode,''2'',WXsTotal,0)) As LxWXsTotal,
Sum(Decode(JyMode,''2'',HXsCost,0)) As LxHXsCost,Sum(Decode(JyMode,''2'',WXsCost,0)) As LxWXsCost,
Sum(Decode(JyMode,''2'',HMTotal,0)) As LxHMTotal,Sum(Decode(JyMode,''2'',WMTotal,0)) As LxWMTotal,
Sum(Decode(JyMode,''3'',XsCount,0)) As ZlXsCount,
Sum(Decode(JyMode,''3'',HXsTotal,0)) As ZlHXsTotal,Sum(Decode(JyMode,''3'',WXsTotal,0)) As ZlWXsTotal,
Sum(Decode(JyMode,''3'',HXsCost,0)) As ZlHXsCost,Sum(Decode(JyMode,''3'',WXsCost,0)) As ZlWXsCost,
Sum(Decode(JyMode,''3'',HMTotal,0)) As ZlHMTotal,Sum(Decode(JyMode,''3'',WMTotal,0)) As ZlWMTotal,
Sum(XsCount) As XsCount,Sum(HXsTotal) HXsTotal,Sum(WXsTotal) As WXsTotal,Sum(HXsCost) As HXsCost,
Sum(WXsCost) As WXsCost,Sum(HMTotal) As HMTotal,Sum(WMTotal) As WMTotal
From tLklBrcSal_Cust_DRpt A
where
OrgCode in ( select orgcode from torgmanage start with orgcode='''||cOrgCode||''' connect by prior orgcode=preorgcode)
And exists (Select * from Tusrrightorg where A.OrgCode =OrgCode and A.DepCode like DepCode||''%'' and DepCode like '''||cDepCode||''' and Userid ='''||SysUserId||''')
And DepCode like '''||cDepCode||'''
And RptDate between'''||cBgnDate||''' and '''||cEndDate||'''
Group by DepCode,orgcode ';
Open result_set For cSql:= '
Select a.OrgCode as 门店编码,(Select OrgName from torgManage where OrgCode =a.orgCode) as 门店名称,
substr(a.DepCode,1,cLen) as 部门编码,b.depname as 部门名称,JxXsCount As 经销数量,JxHXsTotal As 经销含税金额,JxWXsTotal As 经销无税金额,JxHXsCost As 经销含税成本,JxWXsCost As 经销无税成本,JxHMTotal As 经销含税毛利,JxWMTotal As 经销无税毛利,
Case JxHXsTotal When 0 Then 0 Else Round(JxHMTotal/JxHXsTotal,2) End As 经销毛利率, DxXsCount As 代销数量,DxHXsTotal As 代销含税金额,DxWXsTotal As 代销无税金额,DxHXsCost As 代销含税成本,DxWXsCost As 代销无税成本,DxHMTotal As 代销含税毛利,DxWMTotal As 代销无税毛利,
Case DxHXsTotal When 0 Then 0 Else Round(DxHMTotal/DxHXsTotal,2) End As 代销毛利率, LxXsCount As 联销数量,LxHXsTotal As 联销含税金额,LxWXsTotal As 联销无税金额,LxHXsCost As 联销含税成本,LxWXsCost As 联销无税成本,LxHMTotal As 联销含税毛利,LxWMTotal As 联销无税毛利,
Case LxHXsTotal When 0 Then 0 Else Round(LxHMTotal/LxHXsTotal,2) End As 联销毛利率, ZlXsCount As 租赁数量,ZlHXsTotal As 租赁含税金额,ZlWXsTotal As 租赁无税金额,ZlHXsCost As 租赁含税成本,ZlWXsCost As 租赁无税成本,ZlHMTotal As 租赁含税毛利,ZlWMTotal As 租赁无税毛利,
Case ZlHXsTotal When 0 Then 0 Else Round(ZlHMTotal/ZlHXsTotal,2) End As 租赁毛利率, XsCount As 合计数量,HXsTotal As 合计含税金额,WXsTotal As 合计无税金额,HXsCost As 合计含税成本,WXsCost As 合计无税成本,HMTotal As 合计含税毛利,WMTotal As 合计无税毛利,
Case HXsTotal When 0 Then 0 Else Round(HMTotal/HXsTotal,2) End As 合计毛利率
From('||cSql||') a ,tOrgDept b
where substr(a.DepCode,1,cLen)=b.DepCode and a.orgcode=b.orgcode
and exists(select 1 from tUsrRightOrg where userid=SysUserId and depcode=a.depcode and orgcode=a.orgcode)
Group by a.orgcode,substr(a.DepCode,1,cLen),b.depname
order by a.orgcode ';ENDsRptRPT90000007002;
提示:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:23文本:corgcode:= uparamjorgcode;错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:24文本:cdepcode:= uparamjdepcode | | ' % ';错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:25文本:cbgndate:= to_char(uparamjbgndate,'yyyy-mm-dd ');错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:26文本:cenddate:= to_char(uparamjenddate,'yyyy-mm-dd ');错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar行:27
(SysOrgCode In VarChar2,
SysUserId In Number,
SysUserCode In VarChar2,
SysUserName In VarChar2,
UParamjOrgCode in Varchar2,
UParamjDepCode in Varchar2,
UParamjBgnDate in Date,
UParamjEndDate in Date,
UParamjsLev in Varchar2,
Result_Set Out pxt_db.cCurType )
Is cOrgCode varchar2(20);
cDepCode varchar2(20);
cBgnDate varchar2(10);
cEndDate varchar2(10);
cLev varchar2(10);
cLen int ;
cSql varchar2(8000);
cOrgtype varchar2(20);
----初始化变量
cOrgCode:=:jOrgCode;
cDepCode:=:jDepCode||'%';
cBgnDate:=to_char(:jBgnDate,'YYYY-MM-DD');
cEndDate:=to_char(:jEndDate,'YYYY-MM-DD');
cLev:=:jsLev;
-----判断门店
Select OrgCode,OrgType Into cOrgCode,cOrgtype From tOrgManage Where OrgCode =cOrgCode ;
If cOrgtype In ('2002') Then
Raise_Application_Error(-20001,'请选择一个门店或分部');
End If;
-----判断日期
If cBgnDate>cEndDate then
Raise_application_Error(-20009,'开始日期应小于结束日期!');
End If;
--查询部门编码长度
Select sum(LvlLength) into clen
From tSysLevel
Where LvlType ='0' and LvlNo<=cLev and isused='1';
clen:=2*cLev;
cSql:= '
Select orgcode,DepCode,
Sum(Decode(JyMode,''0'',XsCount,0)) As JxXsCount,
Sum(Decode(JyMode,''0'',HXsTotal,0)) As JxHXsTotal,Sum(Decode(JyMode,''0'',WXsTotal,0)) As JxWXsTotal,
Sum(Decode(JyMode,''0'',HXsCost,0)) As JxHXsCost,Sum(Decode(JyMode,''0'',WXsCost,0)) As JxWXsCost,
Sum(Decode(JyMode,''0'',HMTotal,0)) As JxHMTotal,Sum(Decode(JyMode,''0'',WMTotal,0)) As JxWMTotal,
Sum(Decode(JyMode,''1'',XsCount,0)) As DxXsCount,
Sum(Decode(JyMode,''1'',HXsTotal,0)) As DxHXsTotal,Sum(Decode(JyMode,''1'',WXsTotal,0)) As DxWXsTotal,
Sum(Decode(JyMode,''1'',HXsCost,0)) As DxHXsCost,Sum(Decode(JyMode,''1'',WXsCost,0)) As DxWXsCost,
Sum(Decode(JyMode,''1'',HMTotal,0)) As DxHMTotal,Sum(Decode(JyMode,''1'',WMTotal,0)) As DxWMTotal,
Sum(Decode(JyMode,''2'',XsCount,0)) As LxXsCount,
Sum(Decode(JyMode,''2'',HXsTotal,0)) As LxHXsTotal,Sum(Decode(JyMode,''2'',WXsTotal,0)) As LxWXsTotal,
Sum(Decode(JyMode,''2'',HXsCost,0)) As LxHXsCost,Sum(Decode(JyMode,''2'',WXsCost,0)) As LxWXsCost,
Sum(Decode(JyMode,''2'',HMTotal,0)) As LxHMTotal,Sum(Decode(JyMode,''2'',WMTotal,0)) As LxWMTotal,
Sum(Decode(JyMode,''3'',XsCount,0)) As ZlXsCount,
Sum(Decode(JyMode,''3'',HXsTotal,0)) As ZlHXsTotal,Sum(Decode(JyMode,''3'',WXsTotal,0)) As ZlWXsTotal,
Sum(Decode(JyMode,''3'',HXsCost,0)) As ZlHXsCost,Sum(Decode(JyMode,''3'',WXsCost,0)) As ZlWXsCost,
Sum(Decode(JyMode,''3'',HMTotal,0)) As ZlHMTotal,Sum(Decode(JyMode,''3'',WMTotal,0)) As ZlWMTotal,
Sum(XsCount) As XsCount,Sum(HXsTotal) HXsTotal,Sum(WXsTotal) As WXsTotal,Sum(HXsCost) As HXsCost,
Sum(WXsCost) As WXsCost,Sum(HMTotal) As HMTotal,Sum(WMTotal) As WMTotal
From tLklBrcSal_Cust_DRpt A
where
OrgCode in ( select orgcode from torgmanage start with orgcode='''||cOrgCode||''' connect by prior orgcode=preorgcode)
And exists (Select * from Tusrrightorg where A.OrgCode =OrgCode and A.DepCode like DepCode||''%'' and DepCode like '''||cDepCode||''' and Userid ='''||SysUserId||''')
And DepCode like '''||cDepCode||'''
And RptDate between'''||cBgnDate||''' and '''||cEndDate||'''
Group by DepCode,orgcode ';
Open result_set For cSql:= '
Select a.OrgCode as 门店编码,(Select OrgName from torgManage where OrgCode =a.orgCode) as 门店名称,
substr(a.DepCode,1,cLen) as 部门编码,b.depname as 部门名称,JxXsCount As 经销数量,JxHXsTotal As 经销含税金额,JxWXsTotal As 经销无税金额,JxHXsCost As 经销含税成本,JxWXsCost As 经销无税成本,JxHMTotal As 经销含税毛利,JxWMTotal As 经销无税毛利,
Case JxHXsTotal When 0 Then 0 Else Round(JxHMTotal/JxHXsTotal,2) End As 经销毛利率, DxXsCount As 代销数量,DxHXsTotal As 代销含税金额,DxWXsTotal As 代销无税金额,DxHXsCost As 代销含税成本,DxWXsCost As 代销无税成本,DxHMTotal As 代销含税毛利,DxWMTotal As 代销无税毛利,
Case DxHXsTotal When 0 Then 0 Else Round(DxHMTotal/DxHXsTotal,2) End As 代销毛利率, LxXsCount As 联销数量,LxHXsTotal As 联销含税金额,LxWXsTotal As 联销无税金额,LxHXsCost As 联销含税成本,LxWXsCost As 联销无税成本,LxHMTotal As 联销含税毛利,LxWMTotal As 联销无税毛利,
Case LxHXsTotal When 0 Then 0 Else Round(LxHMTotal/LxHXsTotal,2) End As 联销毛利率, ZlXsCount As 租赁数量,ZlHXsTotal As 租赁含税金额,ZlWXsTotal As 租赁无税金额,ZlHXsCost As 租赁含税成本,ZlWXsCost As 租赁无税成本,ZlHMTotal As 租赁含税毛利,ZlWMTotal As 租赁无税毛利,
Case ZlHXsTotal When 0 Then 0 Else Round(ZlHMTotal/ZlHXsTotal,2) End As 租赁毛利率, XsCount As 合计数量,HXsTotal As 合计含税金额,WXsTotal As 合计无税金额,HXsCost As 合计含税成本,WXsCost As 合计无税成本,HMTotal As 合计含税毛利,WMTotal As 合计无税毛利,
Case HXsTotal When 0 Then 0 Else Round(HMTotal/HXsTotal,2) End As 合计毛利率
From('||cSql||') a ,tOrgDept b
where substr(a.DepCode,1,cLen)=b.DepCode and a.orgcode=b.orgcode
and exists(select 1 from tUsrRightOrg where userid=SysUserId and depcode=a.depcode and orgcode=a.orgcode)
Group by a.orgcode,substr(a.DepCode,1,cLen),b.depname
order by a.orgcode ';ENDsRptRPT90000007002;
提示:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:23文本:corgcode:= uparamjorgcode;错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:24文本:cdepcode:= uparamjdepcode | | ' % ';错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:25文本:cbgndate:= to_char(uparamjbgndate,'yyyy-mm-dd ');错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar符号“<标识符>”取代了“=”继续。行:26文本:cenddate:= to_char(uparamjenddate,'yyyy-mm-dd ');错误:pls-00103:符号“=”时遇到的期待下列之一:恒例外标识符> <<一个双引号的delimited-identifier >表long_双参考字符的时间间隔的日期二进制的国民性格nchar行:27
解决方案 »
- 数据库设计,外键?
- 请教高手!
- orcale表数据如何导出成文本或excel
- 急问:access数据库中的数据可以直接导到oracle吗?
- 求SQL语句,高手请进`````
- 问个问题,ORACLE中变量的长度最长是多少?
- 移动表空间出错?----请高手指点(急忙)
- sql server 中的语句在oracle 中的对应问题
- 谁能够提供完整通用ORACLE8数据库备份程序!谢谢
- oracle的表的trigger中怎么得到当前insert 或delete 或update的行的数据。
- 求oracle数据库软件和linux操作系统
- ORA-29532 ora_06512 请各位帮忙解决一下,谢谢~ 附图
(SysOrgCode In VarChar2,
SysUserId In Number,
SysUserCode In VarChar2,
SysUserName In VarChar2,
UParamjOrgCode in Varchar2,
UParamjDepCode in Varchar2,
UParamjBgnDate in Date,
UParamjEndDate in Date,
UParamjsLev in Varchar2,
Result_Set Out pxt_db.cCurType )
Is
begin
cOrgCode varchar2(20);
cDepCode varchar2(20);
cBgnDate varchar2(10);
cEndDate varchar2(10);
cLev varchar2(10);
cLen int ;
cSql varchar2(8000);
cOrgtype varchar2(20);
----初始化变量
cOrgCode:=:jOrgCode;
cDepCode:=:jDepCode||'%';
cBgnDate:=to_char(:jBgnDate,'YYYY-MM-DD');
cEndDate:=to_char(:jEndDate,'YYYY-MM-DD');
cLev:=:jsLev;
-----判断门店
...
end;
jOrgCode,jDepCode,jBgnDate,jEndDate ...这些是哪来的?
(SysOrgCode In VarChar2,
SysUserId In Number,
SysUserCode In VarChar2,
SysUserName In VarChar2,
UParamjOrgCode in Varchar2,
UParamjDepCode in Varchar2,
UParamjBgnDate in Date,
UParamjEndDate in Date,
UParamjsLev in Varchar2,
Result_Set Out pxt_db.cCurType )
Is cOrgCode varchar2(20);
cDepCode varchar2(20);
cBgnDate varchar2(10);
cEndDate varchar2(10);
cLev varchar2(10);
cLen int ;
cSql varchar2(8000);
cOrgtype varchar2(20);
----初始化变量
begin
....
end;
UParamjDepCode in Varchar2,
UParamjBgnDate in Date,
UParamjEndDate in Date,
UParamjsLev in Varchar2,
在cOrgCode:=:jOrgCode;
cDepCode:=:jDepCode||'%';
cBgnDate:=to_char(:jBgnDate,'YYYY-MM-DD');
cEndDate:=to_char(:jEndDate,'YYYY-MM-DD');
cLev:=:jsLev; 赋值时赋值符号后面的冒号会被编译成UParamjDepCode等