CREATE OR REPLACE PROCEDURE "HUXB0004" (a1 varchar2,a2 varchar2,b1 out number,b2 out number,b3 out number,b4 out number)
--通过输入订单号和产品名称,执行后得到生产该产品需购买原料所需金额,以及实际发生金额。
is
Wcgsl number default 0;
Wcgje number default 0;
Wllsl number default 0;
Wllje number default 0;
JCgsl number default 0;
Jcgje number default 0;
Jllsl number default 0;
Jllje number default 0;
JOne number default 0;
JAll number default 0;
begin--纬的原料采购
--纬的采购
select sum(Quantity),sum(money) into Wcgsl,Wcgje from(
select a.MaterialID as MaterialID,a.Quantity as OneQ,b.Quantity as NeedQ,c.OrderQuantity as OrderQ,d.price as price,(c.OrderQuantity/b.Quantity)*a.Quantity as Quantity,(c.OrderQuantity/b.Quantity)*a.Quantity*d.price as Money
from
(SELECT MaterialID,sum(Quantity) as Quantity
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2
group by MaterialID) a,
(select MaterialID as MaterialID,sum(Quantity) as Quantity
from SPL_BUYMTRNEEDSH where PO_SERIALNUM in (
SELECT d.serialnum
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=a1
AND a.PRODUCTID=a2)
group by MaterialID) b,
(SELECT MaterialID,sum(OrderQuantity) as OrderQuantity
FROM SPL_MTRORDERSHDETAILS
where SERIALNUM in (
select PO_SERIALNUM
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2)
group by MaterialID) c,
(SELECT distinct MaterialID,price
FROM SPL_MTRORDERSHDETAILS
where SERIALNUM in (
select PO_SERIALNUM
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2)) d
where a.MaterialID=b.MaterialID and b.MaterialID=c.MaterialID and c.MaterialID=d.MaterialID
order by a.MaterialID) ;--纬的领料
select sum(Quantity),sum(Money) into Wllsl,Wllje
from Mtr_AxisCardFShDetails
where ORDERID=a1 AND PRODUCTID=a2;--经的原料采购
--经轴上一个产品的数量
select sum(productquantity) into JOne
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2;--经所对应的所有产品的总数量
select sum(a.ProductQuantity) into JAll
from Scd_WarpPlanShDetails a, (
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) b
where a.WarpPlanID=b.WarpPlanID;--经的采购
select sum(Quantity) ,sum(money) into Jcgsl,Jcgje from(
select a.MaterialID as MaterialID,a.Quantity as OneQ,b.Quantity as NeedQ,c.OrderQuantity as OrderQ,d.price as price,(c.OrderQuantity/b.Quantity)*a.Quantity as Quantity,(c.OrderQuantity/b.Quantity)*a.Quantity*d.price as Money
from
(SELECT MaterialID,sum(Quantity) as Quantity
FROM OrderandBuy
WHERE ORDERID in (
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2)
group by MaterialID) a,
(select MaterialID as MaterialID,sum(Quantity) as Quantity
from SPL_BUYMTRNEEDSH a,
(SELECT d.serialnum
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID in
(select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) ) e
where a.PO_SERIALNUM = e.SERIALNUM
group by MaterialID) b,
(SELECT d.MaterialID,sum(d.OrderQuantity) as OrderQuantity
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d,(
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) e
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=e.WarpPlanID
group by d.MaterialID) c,
(SELECT distinct d.MaterialID as MaterialID,d.price as price
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d,(
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) e
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=e.WarpPlanID
) d
where a.MaterialID=b.MaterialID and b.MaterialID=c.MaterialID and c.MaterialID=d.MaterialID
order by a.MaterialID) ;
--经的领料select sum(a.Quantity) ,sum(a.Money) into Jllsl,Jllje
from Mtr_WarpAxisFShDetails a,Mtr_WarpAxisFSh b
where a.SheetID=b.SheetID and b.WarpAxisCardID in
(select WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2);
b1:=round(Wcgsl+Jcgsl*(JOne/JAll),2);
b2:=round(Wcgje+Jcgje*(JOne/JAll),2);
b3:=round(Wllsl+Jllsl*(JOne/JAll),2);
b4:=round(Wllje+Jllje*(JOne/JAll),2);b1:=nvl(b1,0);
b2:=nvl(b2,0);
b3:=nvl(b3,0);
b4:=nvl(b4,0);end ;
--通过输入订单号和产品名称,执行后得到生产该产品需购买原料所需金额,以及实际发生金额。
is
Wcgsl number default 0;
Wcgje number default 0;
Wllsl number default 0;
Wllje number default 0;
JCgsl number default 0;
Jcgje number default 0;
Jllsl number default 0;
Jllje number default 0;
JOne number default 0;
JAll number default 0;
begin--纬的原料采购
--纬的采购
select sum(Quantity),sum(money) into Wcgsl,Wcgje from(
select a.MaterialID as MaterialID,a.Quantity as OneQ,b.Quantity as NeedQ,c.OrderQuantity as OrderQ,d.price as price,(c.OrderQuantity/b.Quantity)*a.Quantity as Quantity,(c.OrderQuantity/b.Quantity)*a.Quantity*d.price as Money
from
(SELECT MaterialID,sum(Quantity) as Quantity
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2
group by MaterialID) a,
(select MaterialID as MaterialID,sum(Quantity) as Quantity
from SPL_BUYMTRNEEDSH where PO_SERIALNUM in (
SELECT d.serialnum
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=a1
AND a.PRODUCTID=a2)
group by MaterialID) b,
(SELECT MaterialID,sum(OrderQuantity) as OrderQuantity
FROM SPL_MTRORDERSHDETAILS
where SERIALNUM in (
select PO_SERIALNUM
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2)
group by MaterialID) c,
(SELECT distinct MaterialID,price
FROM SPL_MTRORDERSHDETAILS
where SERIALNUM in (
select PO_SERIALNUM
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2)) d
where a.MaterialID=b.MaterialID and b.MaterialID=c.MaterialID and c.MaterialID=d.MaterialID
order by a.MaterialID) ;--纬的领料
select sum(Quantity),sum(Money) into Wllsl,Wllje
from Mtr_AxisCardFShDetails
where ORDERID=a1 AND PRODUCTID=a2;--经的原料采购
--经轴上一个产品的数量
select sum(productquantity) into JOne
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2;--经所对应的所有产品的总数量
select sum(a.ProductQuantity) into JAll
from Scd_WarpPlanShDetails a, (
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) b
where a.WarpPlanID=b.WarpPlanID;--经的采购
select sum(Quantity) ,sum(money) into Jcgsl,Jcgje from(
select a.MaterialID as MaterialID,a.Quantity as OneQ,b.Quantity as NeedQ,c.OrderQuantity as OrderQ,d.price as price,(c.OrderQuantity/b.Quantity)*a.Quantity as Quantity,(c.OrderQuantity/b.Quantity)*a.Quantity*d.price as Money
from
(SELECT MaterialID,sum(Quantity) as Quantity
FROM OrderandBuy
WHERE ORDERID in (
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2)
group by MaterialID) a,
(select MaterialID as MaterialID,sum(Quantity) as Quantity
from SPL_BUYMTRNEEDSH a,
(SELECT d.serialnum
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID in
(select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) ) e
where a.PO_SERIALNUM = e.SERIALNUM
group by MaterialID) b,
(SELECT d.MaterialID,sum(d.OrderQuantity) as OrderQuantity
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d,(
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) e
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=e.WarpPlanID
group by d.MaterialID) c,
(SELECT distinct d.MaterialID as MaterialID,d.price as price
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d,(
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) e
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=e.WarpPlanID
) d
where a.MaterialID=b.MaterialID and b.MaterialID=c.MaterialID and c.MaterialID=d.MaterialID
order by a.MaterialID) ;
--经的领料select sum(a.Quantity) ,sum(a.Money) into Jllsl,Jllje
from Mtr_WarpAxisFShDetails a,Mtr_WarpAxisFSh b
where a.SheetID=b.SheetID and b.WarpAxisCardID in
(select WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2);
b1:=round(Wcgsl+Jcgsl*(JOne/JAll),2);
b2:=round(Wcgje+Jcgje*(JOne/JAll),2);
b3:=round(Wllsl+Jllsl*(JOne/JAll),2);
b4:=round(Wllje+Jllje*(JOne/JAll),2);b1:=nvl(b1,0);
b2:=nvl(b2,0);
b3:=nvl(b3,0);
b4:=nvl(b4,0);end ;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货