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;
没有难度却繁琐的一trigger
最新推荐文章于 2024-10-05 15:32:05 发布
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
