生产计划的 MRP 运算，BOM 分解 存储过程 。

CREATE OR REPLACE PROCEDURE P_MPS_MRP(V_ACTIONTYPE IN VARCHAR2, V_INTMPSID IN VARCHAR2, V_PRODUCTID IN VARCHAR2, V_ERRMSG OUT VARCHAR2, V_RESULT OUT VARCHAR2, V_ERRCODE OUT VARCHAR2) IS /************************************************************************ 过程名称：MRP分解 过程描述： V_INTMPSID 生产计划主键 V_PRODUCTID 产品编号 V_ERRMSG 错误信息 V_RESULT 返回值 V_ERRCODE 错误代码 过程编写：刘天翼 编写时间： ************************************************************************/ BEGIN IF 'INSERT' = UPPER(V_ACTIONTYPE) THEN INSERT INTO T_MPS_MRP (F_INTMPSID, F_INTPARENTID, F_INTITEMID, F_NUMQUANTITY, F_INTYEAR, F_INTMONTH, F_INTWEEK) SELECT V_INTMPSID, B.P_FATHER, B.P_CHILD, SUM_STRBOMNUMBER * A.F_INTWEEKNUMBER AS F_NUMQUANTITY, A.INTYEAR, A.INTMONTH, A.F_INTWEEKNUMBER FROM (SELECT TO_CHAR(Y.F_DTMMONTH, 'YYYY') AS INTYEAR, TO_CHAR(Y.F_DTMMONTH, 'MM') AS INTMONTH, Y.F_INTWEEKLY, Y.F_INTWEEKNUMBER FROM T_MPSENTRY M, T_MPSCAPACITY Y WHERE Y.F_INTMPSENTRYID = M.F_INTMPSENTRYID AND M.F_INTMPSID = TO_NUMBER(V_INTMPSID) AND M.F_INTPRODUCTID = TO_NUMBER(V_PRODUCTID)) A, (SELECT M.F_INTITEMID AS P_FATHER, Y.F_INTITEMID AS P_CHILD, SUM(NVL(Y.F_STRBOMNUMBER, 0)) AS SUM_STRBOMNUMBER FROM T_BOMENTRY Y, T_BOM M WHERE M.F_INTBOMID = Y.F_INTBOMID START WITH M.F_INTITEMID = TO_NUMBER(V_PRODUCTID) CONNECT BY NOCYCLE M.F_INTITEMID = PRIOR Y.F_INTITEMID GROUP BY M.F_INTITEMID, Y.F_INTITEMID) B; ELSIF 'DELETE' = UPPER(V_ACTIONTYPE) THEN DELETE /*+ RULE */ FROM T_MPS_MRP WHERE F_INTMPSID = TO_NUMBER(V_INTMPSID); END IF; COMMIT; V_ERRMSG := ''; V_RESULT := 'true'; V_ERRCODE := '0'; /* 出现错误 EXCEPTION */ EXCEPTION WHEN OTHERS THEN ROLLBACK; V_ERRMSG := SQLERRM; V_RESULT := 'false'; V_ERRCODE := '1'; END;