--创建触发器；
create or replace trigger FE44201B_EnquirySummary
after insert on FE44201B FOR EACH ROW --说明创建的是行级触发器
declare
PRAGMA AUTONOMOUS_TRANSACTION;--自治事务
V_OUTERRCODE VARCHAR2(200);
V_OUTERRMSG VARCHAR2(400);
begin
-- DBMS_OUTPUT.PUT_LINE(:new.qtukid); --INSERT INTO crpdta.fe441001 (acukid,aclitm) values (:new.qtukid,'1');
--arehoueInOut(:new.qtukid,:new.qtdate01,:new.qtdate02,:new.qtan8,:new.qtmcu,:new.qtmcu2,:new.qtsrp1,:new.qtsrp2,:new.qtsrp3,:new.qtdcto,:new.qtev01,V_OUTERRCODE,V_OUTERRMSG);
PE44201_EnquirySummary(:NEW.QTUKID,:NEW.QTDRQJ,:NEW.QTRP11,:NEW.QTAC25,:NEW.QTSRP1,V_OUTERRCODE,V_OUTERRMSG);
commit;
end FE44201B_EnquirySummary;
--------------------------------------------------------------
调用存储过程：
CREATE OR REPLACE PROCEDURE PE44201_EnquirySummary(V_UKID NUMBER, -- 唯一编号
V_DRQJ NUMBER, --日期
V_RP11 VARCHAR2, -- 区域
V_AC25 VARCHAR2, -- 门店分类
V_SRP1 VARCHAR2, -- 项目大类
V_OUTERRCODE OUT VARCHAR2, --错误代码（0：代表正确；负数：出错）
V_OUTERRMSG OUT VARCHAR2 --错误信息
)IS
PRAGMA AUTONOMOUS_TRANSACTION;
V_SQL VARCHAR2(4000);
V_TEMP1 VARCHAR2(500);
V_TEMP2 VARCHAR2(500);
V_ESUKID NUMBER;
BEGIN
IF V_UKID >100 THEN
V_ESUKID := V_UKID - 100;
DELETE FROM FE44201A WHERE ESUKID < V_ESUKID;
END IF;
V_TEMP1 :='';
V_TEMP2 :='';
IF TRIM(V_AC25)>' ' THEN
V_TEMP1 :=', F0101';
V_TEMP2 := V_TEMP2 ||'AND SDAN8 = ABAN8 AND ABAC25 = '''||V_AC25||''' ';
END IF;
IF TRIM(V_SRP1)>' ' THEN
V_TEMP2 := V_TEMP2 ||'AND IMSRP1 = '''||V_SRP1||''' ';
END IF;
V_SQL := 'INSERT INTO FE44201A (ESUKID,ESDRQJ,ESMCU,ESITM,ESUORG,ESUOR2,ESUOR3,ESUORD,ESSOQS,ESUOM,ESPRP7) '
--要货数量 SF单
||'SELECT '||V_UKID||','||V_DRQJ||',A.SDMCU,A.SDITM,SFUORG,S5UORG,(NVL(SFUORG,0)+NVL(S5UORG,0)+NVL(SHUORG,0)) UOR3, 0 ESUORD ,SOQS,A.IMUOM1,IBPRP7 FROM '
||'(SELECT SDMCU, SDITM, IMUOM1, SFUORG ,IBPRP7 '
||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(SFUORG) SFUORG '
||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN SFUORG * UMCNV1 / 10000000 ELSE SFUORG END SFUORG '
||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) SFUORG '
||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU AND MCRP11 = '''||V_RP11||''' '
||'AND SDDCTO = ''SF'' '||V_TEMP2||' AND SDUORG <> 0 '
||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM ) '
||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM ORDER BY SDMCU, IBPRP7, SDITM) A LEFT JOIN ('
--补货数量 S5单
||'SELECT SDMCU, SDITM, IMUOM1, S5UORG '
||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(S5UORG) S5UORG '
||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN S5UORG * UMCNV1 / 10000000 ELSE S5UORG END S5UORG '
||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) S5UORG '
||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU AND MCRP11 = '''||V_RP11||''' '
||'AND SDDCTO = ''S5'' '||V_TEMP2||' AND SDUORG <> 0 '
||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM) '
||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM '
||'ORDER BY SDMCU, IBPRP7, SDITM ) B ON A.SDITM=B.SDITM AND A.SDMCU=B.SDMCU LEFT JOIN ('
--直送要货数量 Sh单
||'SELECT SDMCU, SDITM, IMUOM1, SHUORG '
||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(SHUORG) SHUORG '
||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN SHUORG * UMCNV1 / 10000000 ELSE SHUORG END SHUORG '
||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) SHUORG '
||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU AND MCRP11 = '''||V_RP11||''' '
||'AND SDDCTO = ''SH'' '||V_TEMP2||' AND SDUORG <> 0 '
||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM) '
||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM '
||'ORDER BY SDMCU, IBPRP7, SDITM ) C ON A.SDITM=C.SDITM AND A.SDMCU=C.SDMCU LEFT JOIN ('
--数量合计
||'SELECT SDMCU, SDITM, IMUOM1, SOQS '
||'FROM (SELECT SDMCU, SDITM, IMUOM1, SUM(SOQS) SOQS '
||'FROM (SELECT SDMCU,SDITM,IMUOM1,CASE WHEN SDUOM <> IMUOM1 THEN SOQS * UMCNV1 / 10000000 ELSE SOQS END SOQS '
||'FROM (SELECT SDMCU, SDITM, SDUOM, IMUOM1, SUM(SDUORG) SOQS '
||'FROM F4211, F0006, F4101 '||V_TEMP1||' '
||'WHERE SDITM = IMITM AND SDLTTR < 980 AND SDDRQJ = '||V_DRQJ||' AND SDMCU = MCMCU AND SDDCTO IN (''S5'',''SF'', ''SH'') '
||'AND MCRP11 ='''||V_RP11||''' '||V_TEMP2||' AND SDCOMM = ''C'' AND SDUORG <> 0 '
||'GROUP BY SDMCU, SDITM, SDUOM, IMUOM1 '
||'ORDER BY SDMCU, SDITM, SDUOM, IMUOM1) U1 '
||'LEFT JOIN F41002 ON SDITM = UMITM AND SDUOM = UMUM AND IMUOM1 = UMRUM) '
||'GROUP BY SDMCU, SDITM, IMUOM1) U2 '
||'LEFT JOIN F4102 ON SDMCU = IBMCU AND SDITM = IBITM ORDER BY SDMCU, IBPRP7, SDITM ) D ON A.SDITM=D.SDITM AND A.SDMCU=D.SDMCU ';
dbms_output.put_line(V_SQL);
execute immediate V_SQL;
commit;
UPDATE FE44201A SET ESLITM =(SELECT IMLITM FROM F4101 WHERE IMITM=ESITM);
UPDATE FE44201A SET ESDC = (SELECT MCDC FROM F0006 WHERE ESMCU=MCMCU);
UPDATE FE44201A SET (ESDSC1,ESDSC2) = (SELECT IMDSC1,IMDSC2 FROM F4101 WHERE IMITM=ESITM);
commit;
EXCEPTION WHEN OTHERS THEN ROLLBACK; V_OUTERRCODE := SQLCODE; V_OUTERRMSG := SUBSTR(SQLERRM, 1, 255);
END PE44201_EnquirySummary;
