CREATE OR REPLACE TRIGGER BI_M_OUT_HITEM INSTEAD OF INSERT OR UPDATE OR DELETE ON M_OUT_HITEM REFERENCES OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE v_boxstatus NUMBER(10); v_isbox NUMBER(1); v_status NUMBER(10); v_id NUMBER(10); v_billtype VARCHAR2(80); v_product_id NUMBER(10); v_attributesetinstance_id NUMBER(10); v_box_qty NUMBER(10); v_tot_cqtyout NUMBER(10); v_pdanewid NUMBER(10); v_count NUMBER; v_count1 NUMBER; v_pda VARCHAR2(80); v_tot_bqty NUMBER(10); v_tot_bqtyout NUMBER(10); v_tot_cqty NUMBER(10); v_qty NUMBER(10); v_qtyout NUMBER(10); --create by tracy 20110228 BEGIN SELECT t.BOX_STATUS, t.isbox, t.STATUS INTO v_boxstatus, v_isbox, v_status FROM m_out t WHERE t.id = :NEW.m_out_id AND rownum = 1; IF v_boxstatus <> 1 AND v_isbox = 2 THEN raise_application_error(-20001, '出库单正在装箱，不能修改出库箱数！'); END IF; IF v_status = 2 THEN raise_application_error(-20001, '出库单已提交，不能修改出库箱数！'); END IF; v_billtype := chr(substr(:NEW.m_out_id, -2)); v_id := substr(:NEW.m_out_id, 0, length(:NEW.m_out_id) - 2); IF :OLD.TOT_cQTY > 0 AND round(:NEW.tot_cqtyout) < 0 THEN raise_application_error(-20201, '数量大于零，出库盒数不能为负数，请重新输入！'); END IF; BEGIN SELECT nvl(t3.qty, 0) INTO v_qty FROM m_outitem t3 WHERE t3.m_product_id = :NEW.m_product_id AND t3.m_attributesetinstance_id = :NEW.m_attributesetinstance_id AND t3.M_OUT_ID = :NEW.m_out_id; EXCEPTION WHEN no_data_found THEN v_qty := 0; END; IF UPDATING THEN SELECT m.box_qty INTO v_box_qty FROM m_product m WHERE m.id = :NEW.m_product_id; BEGIN SELECT nvl(t3.TOT_BQTYOUT, 0) INTO v_tot_bqtyout FROM m_out_xitem t3 WHERE t3.m_product_id = :NEW.m_product_id AND t3.m_attributesetinstance_id = :NEW.m_attributesetinstance_id AND t3.M_OUT_ID = :NEW.m_out_id; EXCEPTION WHEN no_data_found THEN v_tot_bqtyout := 0; END; /* SELECT m.TOT_CQTYOUT INTO v_TOT_CQTYOUT FROM m_out_hitem m WHERE m.M_OUT_ID = :NEW.M_OUT_ID AND m.M_PRODUCT_ID = :NEW.m_product_id AND m.M_ATTRIBUTESETINSTANCE_ID = :NEW.m_attributesetinstance_id; IF v_TOT_CQTYOUT < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF;*/ v_qtyout := v_tot_bqtyout * v_box_qty + round(:NEW.tot_cqtyout); IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; CASE WHEN v_billtype = 'A' THEN UPDATE M_SALE_HITEM t SET t.tot_cqtyout = round(:NEW.tot_cqtyout) WHERE t.id = :NEW.real_id; UPDATE m_saleitem t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t1.tot_cqtyout FROM M_SALE_HITEM t1 WHERE t.m_product_id = t1.m_product_id AND t.m_attributesetinstance_id = t1.m_attributesetinstance_id AND t1.id = :NEW.real_id) WHERE t.m_sale_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE m_saleitem t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_sale_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE m_sale t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_saleitem a WHERE a.m_sale_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'B' THEN UPDATE M_RETSALE_HITEM t SET t.Tot_Cqtyout = round(:NEW.Tot_Cqtyout) WHERE t.id = :NEW.real_id; UPDATE M_RET_SALEITEM t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_RETSALE_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_ret_sale_id = v_id) WHERE t.m_ret_sale_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE M_RET_SALEITEM t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_ret_sale_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE m_ret_sale t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_ret_saleitem a WHERE a.m_ret_sale_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'C' THEN UPDATE M_TRANSFER_HITEM t SET t.Tot_Cqtyout = round(:NEW.Tot_Cqtyout) WHERE t.id = :NEW.real_id; UPDATE m_transferitem t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_TRANSFER_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_transfer_id = v_id) WHERE t.m_transfer_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE m_transferitem t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist WHERE t.m_transfer_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE m_transfer t SET (t.tot_qtyout, t.tot_amtout_list) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list) FROM m_transferitem a WHERE a.m_transfer_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'D' THEN UPDATE M_RETSALE_HITEM t SET t.Tot_Cqtyout = round(:NEW.Tot_Cqtyout) WHERE t.id = :NEW.real_id; UPDATE M_RET_SALEITEM t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_RETSALE_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_ret_sale_id = v_id) WHERE t.m_ret_sale_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE M_RET_SALEITEM t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_ret_sale_id = v_id AND t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; UPDATE m_ret_sale t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_ret_saleitem a WHERE a.m_ret_sale_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'E' THEN UPDATE M_RETPUR_HITEM t SET t.Tot_Cqtyout = round(:NEW.Tot_Cqtyout) WHERE t.id = :NEW.real_id; UPDATE m_ret_puritem t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_RETPUR_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_ret_pur_id = v_id) WHERE t.m_ret_pur_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_ret_puritem t SET t.qtydiff = nvl(qty, 0) - nvl(t.qtyout, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_ret_pur_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_ret_pur t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_ret_puritem a WHERE a.m_ret_pur_id = v_id) WHERE t.id = v_id; END CASE; END IF; IF INSERTING THEN SELECT COUNT(*) INTO v_count FROM m_pdt_alias_con t WHERE t.m_pda_old_id = (SELECT a.id FROM m_product_alias a WHERE a.m_product_id = :NEW.m_product_id AND a.m_attributesetinstance_id = :NEW.m_attributesetinstance_id); IF v_count = 0 OR (v_billtype = 'B' OR v_billtype = 'D') THEN SELECT t.no, t.m_product_id, t.m_attributesetinstance_id INTO v_pda, v_product_id, v_attributesetinstance_id FROM m_product_alias t WHERE t.m_product_id = :NEW.m_product_id AND t.m_attributesetinstance_id = :NEW.m_attributesetinstance_id; ELSE SELECT t.m_pda_new_id INTO v_pdanewid FROM m_pdt_alias_con t WHERE t.m_pda_old_id = (SELECT a.id FROM m_product_alias a WHERE a.m_product_id = :NEW.m_product_id AND a.m_attributesetinstance_id = :NEW.m_attributesetinstance_id); SELECT t.no, t.m_product_id, t.m_attributesetinstance_id INTO v_pda, v_product_id, v_attributesetinstance_id FROM m_product_alias t WHERE t.id = v_pdanewid; END IF; SELECT m.box_qty INTO v_box_qty FROM m_product m WHERE m.id = v_product_id; BEGIN SELECT nvl(t3.TOT_BQTYOUT, 0) INTO v_tot_bqtyout FROM m_out_Xitem t3 WHERE t3.m_product_id = :NEW.m_product_id AND t3.m_attributesetinstance_id = :NEW.m_attributesetinstance_id AND t3.M_OUT_ID = :NEW.m_out_id; EXCEPTION WHEN no_data_found THEN v_tot_bqtyout := 0; END; CASE WHEN v_billtype = 'A' THEN SELECT COUNT(*) INTO v_count FROM M_SALEITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_sale_id = v_id; SELECT COUNT(*) INTO v_count1 FROM M_SALE_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_sale_id = v_id; IF v_count = 0 THEN raise_application_error(-20001, '款号：' || v_pda || ',在单据明细里不存在！'); ELSIF v_count <> 0 AND v_count1 = 0 THEN INSERT INTO M_SALE_HITEM (id, AD_CLIENT_ID, AD_ORG_ID, M_SALE_ID, M_PRODUCT_ID, TOT_CQTY, TOT_CQTYOUT, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) VALUES (get_sequences('M_SALE_HITEM'), round(:NEW.AD_CLIENT_ID), round(:NEW.AD_CLIENT_ID), v_id, round(:NEW.m_product_id), 0, round(:NEW.tot_cqtyout), round(:NEW.m_attributesetinstance_id), round(:NEW.OWNERID), round(:NEW.MODIFIERID), SYSDATE, SYSDATE, 'Y'); v_tot_cqtyout := round(:NEW.tot_cqtyout); IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; ELSIF v_count1 <> 0 THEN UPDATE M_SALE_HITEM M SET M.TOT_CQTYOUT = M.TOT_CQTYOUT + round(:NEW.tot_cqtyout) WHERE M.M_SALE_ID = V_ID AND m.m_product_id = v_product_id AND m.m_attributesetinstance_id = v_attributesetinstance_id; SELECT t.tot_cqty, nvl(t.tot_cqtyout, 0) INTO v_tot_cqty, v_tot_cqtyout FROM M_SALE_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_sale_id = v_id AND rownum = 1; IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; END IF; UPDATE m_saleitem t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t1.tot_cqtyout FROM M_SALE_HITEM t1 WHERE t.m_product_id = t1.m_product_id AND t.m_attributesetinstance_id = t1.m_attributesetinstance_id AND T1.M_SALE_ID = V_ID) WHERE t.m_sale_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_saleitem t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_sale_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_sale t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_saleitem a WHERE a.m_sale_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'B' THEN SELECT COUNT(*) INTO v_count FROM M_RET_SALEITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_sale_id = v_id; SELECT COUNT(*) INTO v_count1 FROM M_RETSALE_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_sale_id = v_id; IF v_count = 0 THEN raise_application_error(-20001, '款号：' || v_pda || ',在单据明细里不存在！'); ELSIF v_count <> 0 AND v_count1 = 0 THEN INSERT INTO M_RETSALE_HITEM (id, AD_CLIENT_ID, AD_ORG_ID, M_RET_SALE_ID, M_PRODUCT_ID, TOT_CQTY, TOT_CQTYOUT, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) VALUES (get_sequences('M_RETSALE_HITEM'), round(:NEW.AD_CLIENT_ID), round(:NEW.AD_CLIENT_ID), v_id, round(:NEW.m_product_id), 0, round(:NEW.tot_cqtyout), round(:NEW.m_attributesetinstance_id), round(:NEW.OWNERID), round(:NEW.MODIFIERID), SYSDATE, SYSDATE, 'Y'); v_tot_cqtyout := round(:NEW.tot_cqtyout); IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; ELSIF v_count1 <> 0 THEN UPDATE M_RETSALE_HITEM M SET M.TOT_CQTYOUT = M.TOT_CQTYOUT + round(:NEW.tot_cqtyout) WHERE M.M_RET_SALE_ID = V_ID AND m.m_product_id = v_product_id AND m.m_attributesetinstance_id = v_attributesetinstance_id; SELECT t.Tot_Cqty, nvl(t.Tot_Cqtyout, 0) INTO v_Tot_Cqty, v_tot_Cqtyout FROM M_RETSALE_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_sale_id = v_id AND rownum = 1; IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; END IF; UPDATE M_RET_SALEITEM t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_RETSALE_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_ret_sale_id = v_id) WHERE t.m_ret_sale_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE M_RET_SALEITEM t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_ret_sale_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_ret_sale t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_ret_saleitem a WHERE a.m_ret_sale_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'C' THEN --raise_application_error(-20001, :new.m_attributesetinstance_id); SELECT COUNT(*) INTO v_count FROM M_TRANSFERITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_transfer_id = v_id; SELECT COUNT(*) INTO v_count1 FROM M_TRANSFER_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_transfer_id = v_id; IF v_count = 0 THEN raise_application_error(-20001, '款号：' || v_pda || ',在单据明细里不存在！'); ELSIF v_count <> 0 AND v_count1 = 0 THEN INSERT INTO M_TRANSFER_HITEM (id, AD_CLIENT_ID, AD_ORG_ID, M_TRANSFER_ID, M_PRODUCT_ID, TOT_CQTY, TOT_CQTYOUT, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) VALUES (get_sequences('M_TRANSFER_HITEM'), round(:NEW.AD_CLIENT_ID), round(:NEW.AD_CLIENT_ID), v_id, round(:NEW.m_product_id), 0, round(:NEW.tot_cqtyout), round(:NEW.m_attributesetinstance_id), round(:NEW.OWNERID), round(:NEW.MODIFIERID), SYSDATE, SYSDATE, 'Y'); v_tot_cqtyout := round(:NEW.tot_cqtyout); IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; ELSIF v_count1 <> 0 THEN UPDATE M_TRANSFER_HITEM M SET M.TOT_CQTYOUT = M.TOT_CQTYOUT + round(:NEW.tot_cqtyout) WHERE M.M_TRANSFER_ID = V_ID AND m.m_product_id = v_product_id AND m.m_attributesetinstance_id = v_attributesetinstance_id; SELECT t.tot_cqty, nvl(t.tot_cqtyout, 0) INTO v_tot_cqty, v_tot_cqtyout FROM M_TRANSFER_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_transfer_id = v_id AND rownum = 1; IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; END IF; UPDATE m_transferitem t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_TRANSFER_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_transfer_id = v_id) WHERE t.m_transfer_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_transferitem t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist WHERE t.m_transfer_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_transfer t SET (t.tot_qtyout, t.tot_amtout_list) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list) FROM m_transferitem a WHERE a.m_transfer_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'D' THEN SELECT COUNT(*) INTO v_count FROM M_RET_SALEITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_sale_id = v_id; SELECT COUNT(*) INTO v_count1 FROM M_RETSALE_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_sale_id = v_id; IF v_count = 0 THEN raise_application_error(-20001, '款号：' || v_pda || ',在单据明细里不存在！'); ELSIF v_count <> 0 AND v_count1 = 0 THEN INSERT INTO M_RETSALE_HITEM (id, AD_CLIENT_ID, AD_ORG_ID, M_RET_SALE_ID, M_PRODUCT_ID, TOT_CQTY, TOT_CQTYOUT, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) VALUES (get_sequences('M_RETSALE_HITEM'), round(:NEW.AD_CLIENT_ID), round(:NEW.AD_CLIENT_ID), v_id, round(:NEW.m_product_id), 0, round(:NEW.tot_cqtyout), round(:NEW.m_attributesetinstance_id), round(:NEW.OWNERID), round(:NEW.MODIFIERID), SYSDATE, SYSDATE, 'Y'); v_tot_cqtyout := round(:NEW.tot_cqtyout); IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; ELSIF v_count1 <> 0 THEN UPDATE M_RETSALE_HITEM M SET M.TOT_CQTYOUT = M.TOT_CQTYOUT + round(:NEW.tot_cqtyout) WHERE M.M_RET_SALE_ID = V_ID AND m.m_product_id = v_product_id AND m.m_attributesetinstance_id = v_attributesetinstance_id; SELECT t.Tot_Cqty, nvl(t.Tot_Cqtyout, 0) INTO v_Tot_Cqty, v_tot_Cqtyout FROM M_RETSALE_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_sale_id = v_id AND rownum = 1; IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; END IF; UPDATE M_RET_SALEITEM t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_RETSALE_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_ret_sale_id = v_id) WHERE t.m_ret_sale_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE M_RET_SALEITEM t SET t.qtydiff = nvl(t.qtyout, 0) - nvl(qtyin, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_ret_sale_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_ret_sale t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_ret_saleitem a WHERE a.m_ret_sale_id = v_id) WHERE t.id = v_id; WHEN v_billtype = 'E' THEN SELECT COUNT(*) INTO v_count FROM m_ret_puritem t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_pur_id = v_id; SELECT COUNT(*) INTO v_count1 FROM M_RETPUR_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_pur_id = v_id; IF v_count = 0 THEN raise_application_error(-20001, '款号：' || v_pda || ',在单据明细里不存在！'); ELSIF v_count <> 0 AND v_count1 = 0 THEN INSERT INTO M_RETPUR_HITEM (id, AD_CLIENT_ID, AD_ORG_ID, M_RET_PUR_ID, M_PRODUCT_ID, TOT_CQTY, TOT_CQTYOUT, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) VALUES (get_sequences('M_RETPUR_HITEM'), round(:NEW.AD_CLIENT_ID), round(:NEW.AD_CLIENT_ID), v_id, round(:NEW.m_product_id), 0, round(:NEW.tot_cqtyout), round(:NEW.m_attributesetinstance_id), round(:NEW.OWNERID), round(:NEW.MODIFIERID), SYSDATE, SYSDATE, 'Y'); v_tot_cqtyout := round(:NEW.tot_cqtyout); IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; ELSIF v_count1 <> 0 THEN UPDATE M_RETPUR_HITEM M SET M.TOT_CQTYOUT = M.TOT_CQTYOUT + round(:NEW.tot_cqtyout) WHERE M.M_RET_PUR_ID = V_ID AND m.m_product_id = v_product_id AND m.m_attributesetinstance_id = v_attributesetinstance_id; SELECT t.tot_cqty, nvl(t.tot_cqtyout, 0) INTO v_tot_cqty, v_tot_cqtyout FROM M_RETPUR_HITEM t WHERE t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id AND t.m_ret_pur_id = v_id AND rownum = 1; IF v_tot_cqtyout < 0 THEN raise_application_error(-20201, '出库盒数小于0，不允许！'); END IF; v_qtyout := v_tot_bqtyout * v_box_qty + v_tot_cqtyout; IF v_qtyout > v_qty THEN raise_application_error(-20001, '款号：' || v_pda || ',实际出库数量大于单据数量，出库数量：' || v_qtyout || '，单据数量：' || v_qty || '，不允许！'); END IF; END IF; UPDATE m_ret_puritem t SET t.qtyout = (SELECT v_tot_bqtyout * v_box_qty + t2.tot_cqtyout FROM M_RETPUR_HITEM t2 WHERE t2.m_product_id = t.m_product_id AND t2.m_attributesetinstance_id = t.m_attributesetinstance_id AND t2.m_ret_pur_id = v_id) WHERE t.m_ret_pur_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_ret_puritem t SET t.qtydiff = nvl(qty, 0) - nvl(t.qtyout, 0), t.tot_amtout_list = nvl(t.qtyout, 0) * t.pricelist, t.tot_amtout_actual = nvl(t.qtyout, 0) * t.priceactual WHERE t.m_ret_pur_id = v_id AND t.m_product_id = v_product_id AND t.m_attributesetinstance_id = v_attributesetinstance_id; UPDATE m_ret_pur t SET (t.tot_qtyout, t.tot_amtout_list, t.tot_amtout_actual) = (SELECT SUM(a.qtyout), SUM(a.tot_amtout_list), SUM(a.tot_amtout_actual) FROM m_ret_puritem a WHERE a.m_ret_pur_id = v_id) WHERE t.id = v_id; END CASE; END IF; END;
