请给出完整的select语句，通常如果没有for update的select语句是不会被锁的。
ttl_wait_time不是数据字典的默认字段，所以，你截图的结果是通过什么SQL查出来的，也需要告知一下。
评论
有用 0-- Sp_OccupyStock_Rpt Star ***************************************************
-- 旧视图 sp_UseabledStock
-- 名称：各部门占用库存一览表
-- 功能描述：按产品、分支机构进行查询各营业部对库存的占用情况
-- ***************************************************************************
Procedure Sp_OccupyStock_Rpt(P_StartItm Varchar2,
P_EndItm Varchar2,
p_OrgName Varchar2 := Null,
P_Cur Out Sys_Refcursor) As
Begin
Delete T_BD_MATERIAL_TP;
--相关物料先写到临时表
Insert Into T_BD_MATERIAL_TP
(FMATERIALID,
FNUMBER,
FNAME,
FSPECIFICATION,
FUNITNAME,
FCONVERTDENOMINATOR,
FCONVERTNUMERATOR,
FPRICE)
select a.fmaterialid,
a.fnumber,
b2.fname,
b2.fspecification,
d.fname funitname,
u.FCONVERTDENOMINATOR,
u.FCONVERTNUMERATOR,
p.fprice
from t_bd_material a
inner join t_bd_material_l b2 on a.fmaterialid = b2.fmaterialid
Inner Join t_Bd_Materialbase b3 On b3.Fmaterialid = a.Fmaterialid
inner join t_bd_materialsale c on a.fmaterialid = c.fmaterialid
inner join t_bd_unit_l d on c.fsaleunitid = d.funitid
inner join T_BD_UNITCONVERTRATE u on b3.Fbaseunitid = u.Fdestunitid
and c.Fsaleunitid =
U.Fcurrentunitid
Left Join (Select rn, FSALEORGID, fmaterialid, FPRICE
From (Select row_number() over(Partition By t1.FSALEORGID, t2.fmaterialid Order By FMODIFYDATE Desc, t1.fid Desc) rn,
t1.FSALEORGID,
t2.fmaterialid,
t2.FPRICE
From T_SAL_PRICELIST t1
Inner Join T_SAL_PRICELISTENTRY t2 On t1.fid =
t2.fid
Where t1.FLIMITCUSTOMER <> '1'
And t1.FLIMITCUSTOMER <> '2'
And t2.FPRICE > 0
And t1.fdocumentstatus = 'C'
And t1.FFORBIDSTATUS = 'A'
And t2.FFORBIDSTATUS = 'A'
And Sysdate Between t1.FEFFECTIVEDATE And
t1.FEXPIRYDATE
And Sysdate Between t2.feffectivedate And
t2.fexpriydate) t
Where rn = 1) p on a.fmaterialid = p.fmaterialid
and a.fuseorgid = p.FSALEORGID
Where a.Fforbidstatus = 'A' --非禁用状态
And a.fuseorgid = 100001 --使用组织为：厦门银鹭食品集团有限公司
And a.fnumber >= P_StartItm --物料代码
And a.fnumber <= P_EndItm
And a.fmaterialid in
(select t2.fmaterialid From T_SAL_PRICELISTENTRY t2);
Open P_Cur For
select --*
t1.StockOrgName 分支机构,
t1.fnumber 物料代码,
t1.fname 物料名称,
t1.Fmodel 规格型号,
t1.FUnit 单位,
t1.FstockName 仓库,
t1.fprice 单价,
t1.finvqty 现有库存,
t1.finvqty - SUM(nvl(T2.LockQty, 0)) 可用库存,
SUM(T2.LockQty) 订单占用,
Sum(Case
When T2.FDeptId = 1974438 Then
T2.LockQty
Else
0
End) As "闽南营业部",
Sum(Case
When T2.FDeptId = 2722889 Then
T2.LockQty
Else
0
End) As "闽北营业部",
Sum(Case
When T2.FDeptId = 1974515 Then
T2.LockQty
Else
0
End) As "江西营业部",
Sum(Case
When T2.FDeptId = 1977639 Then
T2.LockQty
Else
0
End) As "粤东营业部",
Sum(Case
When T2.FDeptId = 1974547 Then
T2.LockQty
Else
0
End) As "浙江营业部",
Sum(Case
When T2.FDeptId = 1974548 Then
T2.LockQty
Else
0
End) As "安徽营业部",
Sum(Case
When T2.FDeptId = 3434081 Then
T2.LockQty
Else
0
End) As "湘东大区",
Sum(Case
When T2.FDeptId = 3434089 Then
T2.LockQty
Else
0
End) As "湘西大区",
Sum(Case
When T2.FDeptId = 3434053 Then
T2.LockQty
Else
0
End) As "鄂东大区",
Sum(Case
When T2.FDeptId = 3434075 Then
T2.LockQty
Else
0
End) As "鄂西大区",
Sum(Case
When T2.FDeptId = 1974639 Then
T2.LockQty
Else
0
End) As "云南营业部",
Sum(Case
When T2.FDeptId = 1974646 Then
T2.LockQty
Else
0
End) As "贵州营业部",
Sum(Case
When T2.FDeptId = 3434117 Then
T2.LockQty
Else
0
End) As "辽南大区",
Sum(Case
When T2.FDeptId = 3434109 Then
T2.LockQty
Else
0
End) As "辽北大区",
Sum(Case
When T2.FDeptId = 3434098 Then
T2.LockQty
Else
0
End) As "黑龙江大区",
Sum(Case
When T2.FDeptId = 3434106 Then
T2.LockQty
Else
0
End) As "吉林大区",
Sum(Case
When T2.FDeptId = 1878214 Then
T2.LockQty
Else
0
End) As "鲁北营业部",
Sum(Case
When T2.FDeptId = 1878261 Then
T2.LockQty
Else
0
End) As "河南营业部",
Sum(Case
When T2.FDeptId = 1878247 Then
T2.LockQty
Else
0
End) As "晋冀营业部",
Sum(Case
When T2.FDeptId = 1878230 Then
T2.LockQty
Else
0
End) As "京津营业部",
Sum(Case
When T2.FDeptId = 1878228 Then
T2.LockQty
Else
0
End) As "西北营业部",
Sum(Case
When T2.FDeptId = 1974662 Then
T2.LockQty
Else
0
End) As "广西营业部",
Sum(Case
When T2.FDeptId = 1974455 Then
T2.LockQty
Else
0
End) As "苏北营业部",
Sum(Case
When T2.FDeptId = 1974523 Then
T2.LockQty
Else
0
End) As "沪苏营业部",
Sum(Case
When T2.FDeptId = 1977666 Then
T2.LockQty
Else
0
End) As "粤西营业部",
Sum(Case
When T2.FDeptId = 1974617 Then
T2.LockQty
Else
0
End) As "四川营业部",
Sum(Case
When T2.FDeptId = 1974582 Then
T2.LockQty
Else
0
End) As "重庆营业部",
Sum(Case
When T2.FDeptId in (123285, 179907) Then
T2.LockQty
Else
0
End) As "公司"
from (
-----------------------现有库存
Select Case --substr(ma.fnumber,1,2)
When substr(ma.fnumber, 1, 2) = '20' and
substr(org.Fname, 1, 2) <> '营口' and
ma.fname not like '雀巢优活%' Then
substr(org.Fname, 1, 2) || '成品仓'
When substr(ma.fnumber, 1, 2) = '20' and
substr(org.Fname, 1, 2) <> '营口' and
ma.fname like '雀巢优活%' Then
substr(ma.Fname, 1, 2) || '水仓'
When substr(ma.fnumber, 1, 2) = '21' and
substr(org.Fname, 1, 2) <> '营口' Then
substr(org.Fname, 1, 2) || '促销仓'
When substr(ma.fnumber, 1, 2) = '12' and
substr(org.Fname, 1, 2) <> '营口' Then
substr(org.Fname, 1, 2) || '业务纸箱仓'
When substr(ma.fnumber, 1, 2) = '13' and
substr(org.Fname, 1, 2) <> '营口' Then
substr(org.Fname, 1, 2) || '业务纸箱仓'
When substr(org.Fname, 1, 2) = '营口' Then
substr(org.Fname, 1, 2) || '仓'
When substr(ma.fnumber, 1, 2) = '11' Then
substr(org.Fname, 1, 2) || '原料业务仓'
When substr(ma.fnumber, 1, 2) = '16' Then
substr(org.Fname, 1, 2) || '配件业务仓'
End FstockName,
org.forgid,
org.Fname StockOrgName,
aa.fmaterialId,
ma.fnumber,
ma.fname,
ma.fspecification FModel,
ma.funitname FUnit,
ma.fprice,
round(sum(nvl(aa.fbaseqty * ma.FCONVERTDENOMINATOR /
ma.FCONVERTNUMERATOR,
0)),
4) FInvQty,
'1' FAllowlock
From T_STK_INVENTORY aa
Inner Join t_bd_stock ab on ab.fstockid = aa.fstockid
Inner Join t_bd_stock_l ac on ac.fstockid = ab.fstockid
Inner Join t_org_Organizations_L org on org.forgid =
ab.F_YLD_OrgId
Inner Join T_Bd_Material_TP ma on ma.fmaterialid =
aa.fmaterialId
Where aa.fstockid in
(select fstockid from t_bd_stock where fallowlock = '1')
And aa.fstockid not in
(select fstockid from t_bd_stock where fGroup = 727050)
And aa.fstockstatusid in
(select fstockstatusid
from t_BD_StockStatus
where FNotGet = '0'
and FAvailableLock = '1') --and substr(ma.fnumber,1,2) in ('12','13','20','21')
And aa.fbaseqty > 0
And (Trim(p_OrgName) Is Null Or
Trim(p_OrgName) Is Not Null And
org.Fname Like '%' || p_OrgName || '%')
Group By org.forgid,
org.Fname,
aa.fmaterialId,
ma.fnumber,
ma.fname,
ma.fspecification,
ma.funitname,
ma.fprice
Union All
Select --ab.fnumber FstockNo
ac.fname FstockName,
org.forgid,
org.Fname StockOrgName,
aa.fmaterialId,
ma.fnumber,
ma.fname,
ma.fspecification FModel,
ma.funitname FUnit,
ma.fprice,
round(sum(nvl(aa.fbaseqty * ma.FCONVERTDENOMINATOR /
ma.FCONVERTNUMERATOR,
0)),
4) FInvQty,
'0' FAllowlock
From T_STK_INVENTORY aa
Inner Join t_bd_stock ab on ab.fstockid = aa.fstockid
Inner Join t_bd_stock_l ac on ac.fstockid = ab.fstockid
Inner Join t_org_Organizations_L org on org.forgid =
ab.F_YLD_OrgId
Inner Join T_Bd_Material_TP ma on ma.fmaterialid =
aa.fmaterialId
Where aa.fstockid in
(select fstockid from t_bd_stock where fallowlock = '0')
And aa.fstockid not in
(select fstockid from t_bd_stock where fGroup = 727050)
And aa.fstockstatusid in
(select fstockstatusid
from t_BD_StockStatus
where FNotGet = '0'
and FAvailableLock = '1') --and substr(ma.fnumber,1,2) in ('12','13')
And aa.fbaseqty > 0
and (ac.fname like '%纸箱%' or ac.fname like '%空罐%')
And (Trim(p_OrgName) Is Null Or
Trim(p_OrgName) Is Not Null And
org.Fname Like '%' || p_OrgName || '%')
Group By ab.fnumber,
ac.fname,
org.forgid,
org.Fname,
aa.fmaterialId,
ma.fnumber,
ma.fname,
ma.fspecification,
ma.funitname,
ma.fprice) T1,
-----------------------------------------------锁库量
(Select dp.fdeptid --部门内码
,
dpl.Fname FDeptName --部门名称
,
y.f_Yld_Itemid FMaterialid --物料内码
,
y.f_Yld_OrgId Forgid --分支机构内码
,
sum(nvl(y.F_YLD_Qty, 0)) LockQty --锁库数量
,
'1' FAllowlock --是否锁库
From Yl_T_STK_LockStock y --锁库表
Inner Join YL_T_EO_EOrder ord On y.f_Yld_Interid = ord.Fid --电子订单
Inner Join t_bd_customer cu On ord.f_Yl_Customerid =
cu.fcustid --客户
Inner Join t_bd_department dp On cu.F_YL_SALEDEPT =
dp.fdeptid --部门
Inner join t_bd_department_l dpl On dp.fdeptid = dpl.fdeptid
Inner join T_BD_MATERIAL_TP t1 On y.f_Yld_Itemid =
t1.fmaterialid --先在里层把物料限定
Where y.f_yld_stockid not in
(select fstockid from t_bd_stock where fGroup = 727050)
And y.F_YLD_Qty > 0
Group By dpl.Fname, y.f_Yld_Itemid, y.f_Yld_OrgId, dp.fdeptid) T2
Where t1.fmaterialid = t2.fmaterialid(+)
and t1.forgid = t2.forgid(+)
and t1.fallowlock = t2.fallowlock(+)
-- and substr(t1.fnumber,1,2) = '13'
Group by t1.StockOrgName,
t1.FstockName,
t1.fnumber,
t1.fname,
t1.Fmodel,
t1.FUnit,
t1.finvqty,
t1.fprice
Order by t1.fnumber, t1.StockOrgName;
End Sp_OccupyStock_Rpt;
-- Sp_OccupyStock_Rpt End *************************************************
评论 有用 0
https://obs-emcsapp-public.obs.cn-north-4.myhuaweicloud.com:443/cloudService#standard
评论 有用 0
