Every day will be the actual date of delivery in table ZMMJYHWJJ01 (ZSJJHQ) as the empty data import table DP_ ZMMJYHWJJ01 as not operating data for the use of logistics documents electronically,
Such as ZMMJYHWJJ01 data in the actual delivery date (ZSJJHQ) is not null, and DP_ZMMJYHWJJ01 - ZSJJHQ is empty, DP_ZMMJYHWJJ01 - RESERVESTR8 is empty, it will delete the data in table DP_ZMMJYHWJJ01, because in the ERP system has been finished Posting receipt is operating,
Stored procedure:
The CREATE OR REPLACE PROCEDURE ZMMJYHWJJ01_TO_DPZMMJYHWJJ01
AS
TYPE ZMMJYHWJJ01_TO_DPZMMJYHWJJ01 IS REF CURSOR;//define a reference cursor type
BENGIN
The OPEN SELECT_ZMMJYHWJJ01 FOR
SELECT * FROM ZMMJYHWJJ01 WHERE ZSJJHQ is null;//ZSJJHQ database field represents the actual delivery date
C_row SELECT_ZMMJYHWJJ01 % rowtype;
FOR c_row SELECT_ZMMJYHWJJ01 in
LOOP
INSERT INTO DP_ZMMJYHWJJ01 (ZFHTZDH, EBELN EBELP, GYSCH, MBLNR, ZEILE, GJAHR, BUKRS, WERKS, ZSJJHQ, c_rowJSL, MEINS, CHARG, REMARK, ZDJZT, UPGSTATUS, MATNR, ERNAM, ERNAMTXT, ZFHFQZ, ZFHFSJ, ZSHFQZ, ZFLAG, FLATXT, ZSHFSJ, LGORT, LGOBE, ZFQR, ZSPR)
VALUES (c_row ZFHTZDH, c_row EBELN, c_row. EBELP, c_row. GYSCH, c_row. MBLNR, c_row. ZEILE, c_row. GJAHR, c_row. BUKRS, c_row. WERKS, c_row. ZSJJHQ, c_row. C_rowJSL, c_row. MEINS, c_row. CHARG, c_row. The REMARK, c_row. ZDJZT, c_row. UPGSTATUS, c_row. MATNR, c_row. ERNAM, c_row. ERNAMTXT, c_row. ZFHFQZ, c_row. ZFHFSJ, c_row. ZSHFQZ, c_row. ZFLAG, c_row. FLATXT, c_row. ZSHFSJ, c_row. LGORT, c_row. LGOBE, c_row. ZFQR, c_row. ZSPR);
EXIT the WHEN SELECT_ZMMJYHWJJ01 % notfound;
END LOOP;
The CLOSE SELECT_ZMMJYHWJJ01;
OPRN SELECT_ZMMJYHWJJ02 FOR
SELECT * FROM ZMMJYHWJJ01 Z
LEFT the JOIN DP_ZMMJYHWJJ01 DP ON DP. ZFHTZDH=Z.Z FHTZDH AND DP. ZSJJHQ IS NULL AND DP. UPGSTATUS IS NULL//ZFHTZDH primary key ID field, UPGSTATUS operation logo
WHERE Z.Z SJJHQ IS NOT NULL;
D_row SELECT_ZMMJYHWJJ02 % rowtype;
FOR d_row SELECT_ZMMJYHWJJ02 IN
LOOP
The DELETE FROM DP_ZMMJYHWJJ01 WHERE d_row. ZFHTZDH=DP_ZMMJYHWJJ01. ZFHTZDH;
Exit the when SELECT_ZMMJYHWJJ02 % notfound;
END LOOP;
The CLOSE SELECT_ZMMJYHWJJ02;
END;
CodePudding user response:
What is your problem?CodePudding user response:
What problem? Didn't write!CodePudding user response:
6 fly up...CodePudding user response:
Why use vernier implementation?