解决方案 »
- 郁闷,执行触发器执行很久通不过,是什么问题?
- 有3张表,一张表a1是 有字段A ,B 另外一张表b1有有字段A,B,表c1有字段 B和其他,A表为基础更新B表C表,
- oracle语句 查询一周之内的信息
- 迷茫
- 取得远程存储过程的参数的类型和个数
- oracle里用什么命令格式从数据库A里把某一表连表结构和数据一起倒成一个文件,然后用什么命令格式把该文件倒到数据库B里(当然是倒文件的
- 欢迎大家讨论一下:查询月记录的最佳方法。
- oracle的增量备份如何实现?
- pb作前端oracle作後台開發出來的系統頒發時,客戶機是否可以不裝oracle客戶端??
- 请问那里有Oracle Designer/2000 下载?
- 新手提问,请教高人,关于Oracle中alert问题(急!)
- 在线急等谢谢先!
--
IF (v_ErrorNo = 0) THEN
begin
if(v_RootOrderNoFromSap is null) then
begin
INSERT INTO ProductionOrderRoutingMaterial
( RoutingMaterialID, RoutingDetailID, MaterialID, OutOfBom, Quantity, DeductionOption,Isdumps )
( SELECT OrderRoutingMaterialID_Seq.nextval
,por.RoutingDetailID,
prm.MaterialID,
prm.OutOfBom,
prm.Quantity,
prm.DeductionOption,
prm.Isdumps
FROM PartRoutingMaterial prm
JOIN PartRouting pr ON pr.RoutingDetailID = prm.RoutingDetailID
JOIN ProductionOrder po ON po.PartID = pr.PartID
JOIN ProductionOrderRouting por ON por.ProductionOrderID = po.ProductionOrderID AND por.Ordinal = pr.Ordinal
JOIN Part m on m.partid = prm.materialid
-- JOIN WorkSiteCategory wsc on wsc.WorkSiteID = PO.WorkSiteID and wsc.categoryid = m.categoryid
WHERE po.ProductionOrderID = v_ProductionOrderID );
end;
else
begin
Select WorkSiteID into v_WorkSiteID
from ProductionOrder
where ProductionOrderID =v_ProductionorderID;
if(v_WOrkSiteID>0)then
begin
/*INSERT INTO ProductionOrderRoutingMaterial
(RoutingMaterialID, RoutingDetailID, MaterialID, OutOfBom, Quantity, DeductionOption,Isdumps )
(SELECT OrderRoutingMaterialID_Seq.nextval
,M.RoutingDetailID,M.MaterialID,M.OutOfBom,M.Quantity,M.DeductionOption,M.Isdumps
From (Select distinct por.RoutingDetailID,
p.PartID as MaterialID,
0 as OutOfBom,
b.Quantity,
(case b.isserializer when 1 then 1
when 2 then 5 end) as DeductionOption,
o.Isdumps
From Bommapping b
join ProcedureOperation PO on b.operationid = po.operationid
join ProductionOrderRouting por on por.procedureoperationid = po.procedureoperationid
join ordersapbom o on b.ChildPartCode = o.partcode
join Part p on p.partCode = o.partcode and p.revisionNo = o.revisionno
join worksitepart wp on wp.partcode = P.PartCode
where b.parentpartcode=v_ParentPartCode and b.isserializer<>0
and por.productionorderid=v_ProductionOrderID -- and o.parentpartcode= v_ParentPartCode
and o.productionorderno = v_RootOrderNoFromSap and wp.worksiteid=v_WorkSiteID)M
);*/
/*the above sql commented out by WangJunwei on 2010-12-10 coz the b.Quantity logic don't fit into
the biz requirements.
*/
/*INSERT INTO ProductionOrderRoutingMaterial
(RoutingMaterialID, RoutingDetailID, MaterialID, OutOfBom, Quantity, DeductionOption,Isdumps )
(SELECT OrderRoutingMaterialID_Seq.nextval
,M.RoutingDetailID,M.MaterialID,M.OutOfBom,N.Quantity,M.DeductionOption,M.Isdumps
From (Select distinct por.RoutingDetailID,
p.PartID as MaterialID,
0 as OutOfBom,
b.Quantity,
(case b.isserializer when 1 then 1
when 2 then 5 end) as DeductionOption,
o.Isdumps
From Bommapping b
join ProcedureOperation PO on b.operationid = po.operationid
join ProductionOrderRouting por on por.procedureoperationid = po.procedureoperationid
join ordersapbom o on b.ChildPartCode = o.partcode
join Part p on p.partCode = o.partcode and p.revisionNo = o.revisionno
join worksitepart wp on wp.partcode = P.PartCode
where b.parentpartcode=v_ParentPartCode and b.isserializer<>0
and por.productionorderid=v_ProductionOrderID -- and o.parentpartcode= v_ParentPartCode
and o.productionorderno = v_RootOrderNoFromSap and wp.worksiteid=v_WorkSiteID)M
join
(select partid
,(sum(o.quantity)/ (select orderquantity from productionorder where productionorderno=v_RootOrderNoFromSap)) Quantity
from ordersapbom o
join part on o.partcode= part.partcode and o.revisionno=part.revisionno
where o.productionorderno=v_RootOrderNoFromSap
group by partid) N on M.materialID=N.partid --use N.Quantity
);*/ Select distinct por.RoutingDetailID,
por.productionorderid,
p.PartID as MaterialID,
0 as OutOfBom,
(case b.isserializer when 1 then 1
when 2 then 5 end) as DeductionOption,
--o.Isdumps
0 as Isdumps
bulk collect into v_routingmaterial
From Bommapping b
join ProcedureOperation PO on b.operationid = po.operationid
join ProductionOrderRouting por on por.procedureoperationid = po.procedureoperationid
join ordersapbom o on b.ChildPartCode = o.partcode
join Part p on p.partCode = o.partcode and p.revisionNo = o.revisionno
join worksitepart wp on wp.partcode = P.PartCode
where b.parentpartcode=v_ParentPartCode and b.isserializer<>0
and por.productionorderid=v_ProductionOrderID -- and o.parentpartcode= v_ParentPartCode
and o.productionorderno = v_RootOrderNoFromSap and wp.worksiteid=v_WorkSiteID;
FOR x IN 1..v_routingmaterial.Count
LOOP
v_poid := v_routingmaterial(x).productionorderid;
v_detailid := v_routingmaterial(x).RoutingDetailID;
v_materialid := v_routingmaterial(x).MaterialID;
v_outofbom := v_routingmaterial(x).OutOfBom;
v_deductionoption := v_routingmaterial(x).DeductionOption;
v_isdumps := v_routingmaterial(x).Isdumps;
select partcode into v_consumedpartcode from part where partid= v_materialid;
--dbms_output.put_line('='||v_consumedpartcode||'=');
select partcode into v_popartcode from part where partid=(select partid from productionorder where productionorderid=v_poid);
select productionorderno into v_pono from productionorder where productionorderid= v_poid;
-- ============================================================================http://www.szemba.cn/ 深圳MBA
v_bomqty := 0;
BOMPARTNUMBERS_GET(v_rootordernofromsap,v_popartcode,v_consumedpartcode,v_bomqty,v_ErrorNo);
--dbms_output.put_line(v_bomqty);
if (v_ErrorNo <> 0) then
return;
end if; /*if v_pono= v_rootordernofromsap then --SAP工单
begin
select nvl(sum(quantity),0) into v_qty from
(
select level,
sys_connect_by_path(partcode,'/') "PATH",
CONNECT_BY_ISLEAF "ISLEAF",
x.productionorderno,
x.parentpartcode,
x.partcode,
x.quantity
from (select * from ordersapbom where productionorderno = v_rootordernofromsap) x
START WITH parentpartcode = v_popartcode
CONNECT BY parentpartcode = PRIOR partcode
) y
where partcode= v_consumedpartcode;
end;
else
begin
select partcode into v_rootpopartcode from part where partid=(select min(partid) from productionorder where productionorderno= v_rootordernofromsap);
select nvl(sum(quantity),0) into v_qty from
(
select level,
sys_connect_by_path(partcode,'/') PATH,
CONNECT_BY_ISLEAF "ISLEAF",
x.productionorderno,
x.parentpartcode,
x.partcode,
x.quantity
from (select * from ordersapbom where productionorderno = v_rootordernofromsap) x
START WITH parentpartcode = v_rootpopartcode
CONNECT BY parentpartcode = PRIOR partcode
) y
where partcode= v_consumedpartcode
--and instr(PATH,'/'||v_popartcode||'/')=1 and instr(PATH, v_consumedpartcode)=length(PATH)
and regexp_like(PATH,'^(.)*\'|| v_popartcode||'/(.)*'|| v_consumedpartcode||'$');
end;
end if; select orderquantity into v_orderqty from productionorder where productionorderid= v_poid;
v_bomqty := v_qty/ v_orderqty;*/
-- ============================================================================ --dbms_output.put_line('='||v_detailid||'='||v_materialid||'='|| v_outofbom||'='|| v_bomqty||'='|| v_deductionoption||'='||v_isdumps);
if v_bomqty<>0 then --增加此判断条件 by WangJunwei on 2011-03-04
INSERT INTO ProductionOrderRoutingMaterial
(RoutingMaterialID, RoutingDetailID, MaterialID, OutOfBom, Quantity, DeductionOption,Isdumps )
values
(
OrderRoutingMaterialID_Seq.nextval,
v_detailid,
v_materialid,
v_outofbom,
v_bomqty,
v_deductionoption,
v_isdumps
);
end if;
END LOOP;
end;
end if;
end;
end if; select PartRoutingEcn, PartRoutingMaterialEcn
into v_PartRoutingEcn, v_PartRoutingMaterialEcn
from Part
where PartID = v_PartID; -- Copy ECNs
if (v_PartRoutingEcn is not null)
or (v_PartRoutingMaterialEcn is not null)
then
update ProductionOrder
set PartRoutingEcn = v_PartRoutingEcn
,PartRoutingMaterialEcn = v_PartRoutingMaterialEcn
where ProductionOrderID = v_ProductionOrderID;
end if; if (v_ChangeNo is not null) then
update ProductionOrder
set OrderRoutingEcn = iv_ChangeNo
where ProductionOrderID = v_ProductionOrderID
and nvl(OrderRoutingEcn, '-') != iv_ChangeNo; update ProductionOrder
set OrderRoutingMaterialEcn = iv_ChangeNo
where ProductionOrderID = v_ProductionOrderID
and nvl(OrderRoutingMaterialEcn, '-') != iv_ChangeNo; insert into OrderRoutingLog
(ProductionOrderID
,Ordinal
,ProcedureOperationID
,RoutingDetailID
,ProcessTargetID
,WorkUnitID
,ProcessConditionID
,IsPausePoint
,ChangeNo
,ChangeActionID
,ActionDate
,ActionByID
)
select ProductionOrderID
,Ordinal
,ProcedureOperationID
,RoutingDetailID
,ProcessTargetID
,WorkUnitID
,ProcessConditionID
,IsPausePoint
,iv_ChangeNo
,1
,v_ActionDate
,v_ActionByID
from ProductionOrderRouting
where ProductionOrderID = v_ProductionOrderID; insert into OrderRoutingMaterialLog
(RoutingMaterialID
,RoutingDetailID
,MaterialID
,Quantity
,MLotNoList
,DeductionOption
,OutOfBom
,CanSplit
,ChangeNo
,ChangeActionID
,ActionDate
,ActionByID
)
select
m.RoutingMaterialID
,m.RoutingDetailID
,m.MaterialID
,m.Quantity
,m.MLotNoList
,m.DeductionOption
,m.OutOfBom
,m.CanSplit
,iv_ChangeNo
,1
,v_ActionDate
,v_ActionByID
FROM ProductionOrderRoutingMaterial m
JOIN ProductionOrderRouting r ON r.RoutingDetailID = m.RoutingDetailID
WHERE r.ProductionOrderID = v_ProductionOrderID;
end if;
end;
END IF;
exception when others then
v_ErrorNo := sqlcode;
rollback;
END; 深圳MBA