麻烦大家帮忙看看这个SQL的意思，谢谢

Question

本帖最后由 chenou851123 于 2012-06-06 14:36:37 编辑

Answer 1

-- Creates the routing materials
   --
   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

Answer 2

 -- 针对BOMMAPPING里面的父物料子物料对应关系，计算该订单下的ORDERSAPBOM中父物料使用子物料数量
          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