PROCEDURE SP_UPDATE_PRNTMDS(MDSID VARCHAR2, VOUT OUT INT) AS
VWEIGHT FLOAT;
VWEIGHT_ALL FLOAT;
VPKID VARCHAR2(30);
VPTYPE INT;
CURSOR CUR_REF_NODE IS
SELECT *
FROM camds_structure
WHERE CKID=MDSID
AND CREF_FLAG=1;
REF_NODE CUR_REF_NODE%ROWTYPE;
CURSOR CUR_REF_NODE_BROTHER IS
SELECT * FROM camds_structure WHERE CPSID = REF_NODE.CPSID;
REF_NODE_BROTHER CUR_REF_NODE_BROTHER%ROWTYPE;
BEGIN
OPEN CUR_REF_NODE;
LOOP
FETCH CUR_REF_NODE
INTO REF_NODE;
EXIT WHEN CUR_REF_NODE%NOTFOUND;
SELECT CKID,CNODE_TYPE
INTO VPKID,VPTYPE
FROM camds_structure
WHERE CSID = REF_NODE.CPSID;
VWEIGHT_ALL:=0;
IF VPTYPE =1 THEN
OPEN CUR_REF_NODE_BROTHER;
LOOP
FETCH CUR_REF_NODE_BROTHER INTO REF_NODE_BROTHER;
EXIT WHEN CUR_REF_NODE_BROTHER%NOTFOUND;
IF REF_NODE_BROTHER.CCONS_TYPE =1 THEN
SELECT DECODE(UPPER(C.CWEIGHT_UNIT),*KG*, 1000, *MG*, 1 / 1000,
1) * C.CMEA_WEIGHT_PER_ITEM * NVL(S.CQUANTITY, 0) -- ,C.CMEA_REUSE_RATE ,C.CMEA_RECYCLE_RATE
INTO VWEIGHT --,VREUSE ,VRECYCLE
FROM camds_component C, camds_structure S
WHERE C.CKID = S.CKID
AND S.CSID = REF_NODE_BROTHER.CSID;
VWEIGHT_ALL :=VWEIGHT_ALL+VWEIGHT; ---累计质量
ELSE
IF REF_NODE_BROTHER.CNODE_TYPE=2 THEN
SELECT DECODE(UPPER(S.CWEIGHT_UNIT),*KG*, 1000, *MG*, 1 / 1000,
1) * S.CWEIGHT --,C.CMEA_REUSE_RATE ,C.CMEA_RECYCLE_RATE
INTO VWEIGHT --,VREUSE ,VRECYCLE
FROM camds_semicomponent C, camds_structure S
WHERE C.CKID = S.CKID
AND S.CSID = REF_NODE_BROTHER.CSID;
ELSE
SELECT DECODE(UPPER(S.CWEIGHT_UNIT),*KG*, 1000, *MG*, 1 / 1000,
1) * S.CWEIGHT -- ,C.CREUSE_RATE ,C.CRECYCLE_RATE
INTO VWEIGHT --,VREUSE ,VRECYCLE
FROM camds_material C, camds_structure S
WHERE C.CKID = S.CKID
AND S.CSID = REF_NODE_BROTHER.CSID;
END IF;
VWEIGHT_ALL :=VWEIGHT_ALL+VWEIGHT; ---累计质量
END IF;
END LOOP;
CLOSE CUR_REF_NODE_BROTHER;
UPDATE camds_component
SET
CCAL_WEIGHT_PER_ITEM = DECODE(UPPER(CWEIGHT_UNIT),
*KG*,
1 / 1000,
*MG*,
1000,
1) * VWEIGHT_ALL
WHERE CKID = VPKID;
END IF;
END LOOP;
CLOSE CUR_REF_NODE;
VOUT := 1;
EXCEPTION
WHEN OTHERS THEN
VOUT := 0;
END;