1 HANA-BW-改造自定义数据源
2 2020年5月13日
3 12:53
4 ZMM_TRAN_KONV改为hana触发器增量上载
5 源代码：
6 OPEN CURSOR WITH HOLD S_CURSOR FOR
7 SELECT A~EBELN
8 A~EBELP
9 B~KNUMV
10 A~EBELP AS KPOSN
11 FROM EKPO AS A
12 INNER JOIN EKKO AS B ON A~EBELN = B~EBELN
13 WHERE ( A~AEDAT IN L_R_AEDAT OR B~AEDAT IN L_R_AEDAT ) AND
14 A~EBELP IN L_R_EBELP AND
15 A~EBELN IN L_R_EBELN
16 .
17
18 ENDIF.
19
20 FETCH NEXT CURSOR S_CURSOR
21 APPENDING CORRESPONDING FIELDS
22 OF TABLE E_T_DATA
23 PACKAGE SIZE S_S_IF-MAXSIZE.
24
25 IF SY-SUBRC <> 0.
26 CLOSE CURSOR S_CURSOR.
27 RAISE NO_MORE_DATA.
28 ENDIF.
29
30 FIELD-SYMBOLS: <FS_ZSKONV> TYPE ZSKONV.
31
32 SELECT *
33 FROM KONV
34 INTO CORRESPONDING FIELDS OF TABLE LT_ZSKONV
35 FOR ALL ENTRIES IN E_T_DATA
36 WHERE KNUMV = E_T_DATA-KNUMV
37 AND KPOSN = E_T_DATA-KPOSN
38 .
39
40 SORT E_T_DATA BY KNUMV KPOSN .
41 E_T_DATA2 = E_T_DATA[] .
42 E_T_DATA[] = LT_ZSKONV .
43
44 SORT E_T_DATA2 BY KNUMV KPOSN .
45
46 LOOP AT E_T_DATA ASSIGNING <FS_ZSKONV> .
47 READ TABLE E_T_DATA2 INTO LS_ZSKONV WITH KEY KNUMV = <FS_ZSKONV>-KNUMV KPOSN = <FS_ZSKONV>-KPOSN BINARY SEARCH .
48 IF SY-SUBRC = 0.
49 <FS_ZSKONV>-EBELN = LS_ZSKONV-EBELN .
50 <FS_ZSKONV>-EBELP = LS_ZSKONV-EBELP .
51 ENDIF.
52 ENDLOOP.
53
54
55 S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.
56
57 " LT_EKPO = E_T_DATA .
58 "SORT LT_EKPO BY EBELN EBELP ETENR ASCENDING .
59
60
61 修改后：
62 1.在Repositiories处理：增加一张表【TA_BW_DELTA_EKPO.hdbtable】，为增量表，数据来源为触发器
63 // To define an HDB table with main sql type columns, you can use the following code.
64 // In the example below a schema should exist.
65
66
67 table.schemaName = "DELIDW";
68 table.tableType = COLUMNSTORE; // ROWSTORE is an alternative value
69
70
71 table.columns =
72 [
73 {name = "MANDT"; sqlType = NVARCHAR; length = 3;},
74 {name = "EBELN"; sqlType = NVARCHAR; length = 10;},
75 {name = "EBELP"; sqlType = NVARCHAR; length = 5;},
76 {name = "CREATEDATE"; sqlType = DATE;},
77 {name = "CREATETIME"; sqlType = TIME;},
78 {name = "ACTION"; sqlType = NVARCHAR; length = 10;}
79 ];
80
81 //table.primaryKey.pkcolumns = ["MANDT", "VBELN", "EBELP"];
82
83
84 ![]()
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99 2.在Systems处理：对EKPO做一个触发器：
100 CREATE TRIGGER "DELIDW"."MONITOR_ERP_DELTA_INSERT_RECORDS_EKPO"
101 AFTER INSERT ON "DELIDW"."EKPO" REFERENCING NEW ROW NEWROW FOR EACH ROW
102 BEGIN
103 INSERT INTO "DELIDW"."delidw.data.table::TA_BW_DELTA_EKPO" VALUES (
104 SESSION_CONTEXT('CLIENT'),
105 :newrow."EBELN",
106 :newrow."EBELP",
107 CURRENT_DATE,
108 CURRENT_TIME
109 'INSERT')
110 ;
111 END;
112
113 CREATE TRIGGER "DELIDW"."MONITOR_ERP_DELTA_UPDATE_RECORDS_EKPO"
114 AFTER UPDATE ON "DELIDW"."EKPO" REFERENCING NEW ROW NEWROW FOR EACH ROW
115 BEGIN
116 INSERT INTO "DELIDW"."delidw.data.table::TA_BW_DELTA_EKPO" VALUES (
117 SESSION_CONTEXT('CLIENT'),
118 :newrow."EBELN",
119 :newrow."EBELP",
120 CURRENT_DATE,
121 CURRENT_TIME
122 'UPDATE')
123 ;
124 END;
125
126 3.在Repositiories处理：做视图
127 /********* BEGIN PROCEDURE SCRIPT ************/
128 BEGIN
129
130 DECLARE DELTA_NUM INTEGER;
131
132 TAB_DELTA =
133 SELECT *
134 FROM "DELIDW"."delidw.data.table::TA_BW_DELTA_EKPO" DELTA_EKPO
135 WHERE "DELTA_EKPO"."MANDT" = SESSION_CONTEXT('CLIENT')
136 ;
137
138 SELECT COUNT(1) INTO DELTA_NUM FROM :TAB_DELTA;
139
140 IF DELTA_NUM > 0 THEN
141 TAB_EKPO =
142 SELECT "EKPO"."EBELN",
143 "EKPO"."EBELP",
144 "EKKO"."KNUMV",
145 "EKPO"."EBELP" AS KPOSN
146 FROM :TAB_DELTA DELTA_EKPO
147 INNER JOIN "EP1BWP"."EKPO" EKPO ON "DELTA_EKPO"."MANDT" = "EKPO"."MANDT"
148 AND "DELTA_EKPO"."EBELN" = "EKPO"."EBELN"
149 AND "DELTA_EKPO"."EBELP" = "EKPO"."EBELP"
150 INNER JOIN "EP1BWP"."EKKO" EKKO ON "EKPO"."MANDT" = "EKKO"."MANDT"
151 AND "EKPO"."EBELN" = "EKKO"."EBELN"
152 ;
153 ELSE
154 TAB_EKPO =
155 SELECT "EKPO"."EBELN",
156 "EKPO"."EBELP",
157 "EKKO"."KNUMV",
158 "EKPO"."EBELP" AS KPOSN
159 FROM "EP1BWP"."EKPO" EKPO
160 INNER JOIN "EP1BWP"."EKKO" EKKO ON "EKPO"."MANDT" = "EKKO"."MANDT"
161 AND "EKPO"."EBELN" = "EKKO"."EBELN"
162 WHERE "EKPO"."MANDT" = SESSION_CONTEXT('CLIENT')
163 ;
164 END IF;
165
166
167 TAB_KONV =
168 SELECT
169 --"KONV"."EBELN",
170 --"KONV"."EBELP",
171 "KONV"."KNUMV",
172 "KONV"."KPOSN",
173 "KONV"."STUNR",
174 "KONV"."ZAEHK",
175 "KONV"."KSCHL",
176 "KONV"."KDATU",
177 "KONV"."KAWRT",
178 "KONV"."KBETR",
179 "KONV"."WAERS",
180 "KONV"."KKURS",
181 "KONV"."KPEIN",
182 "KONV"."KMEIN",
183 "KONV"."KUMZA",
184 "KONV"."KUMNE"
185 FROM "EP1BWP"."KONV" KONV
186 INNER JOIN :TAB_EKPO EKPO ON "EKPO"."KNUMV" = "KONV"."KNUMV"
187 AND "EKPO"."KPOSN" = "KONV"."KPOSN"
188 WHERE "KONV"."MANDT" = SESSION_CONTEXT('CLIENT')
189 ;
190
191
192 VAR_OUT =
193 SELECT
194 "EKPO"."EBELN" ,
195 "EKPO"."EBELP" ,
196 "KONV"."KNUMV",
197 "KONV"."KPOSN",
198 "KONV"."STUNR",
199 "KONV"."ZAEHK",
200 "KONV"."KSCHL",
201 "KONV"."KDATU",
202 "KONV"."KAWRT",
203 "KONV"."KBETR",
204 "KONV"."WAERS",
205 "KONV"."KKURS",
206 "KONV"."KPEIN",
207 "KONV"."KMEIN",
208 "KONV"."KUMZA",
209 "KONV"."KUMNE"
210 FROM :TAB_KONV KONV
211 LEFT JOIN :TAB_EKPO EKPO ON "KONV"."KNUMV" = "EKPO"."KNUMV"
212 AND "KONV"."KPOSN" = "EKPO"."KPOSN"
213 ;
214 END /********* END PROCEDURE SCRIPT ************/
215 1. RSA3测试数据源
216
HANA-BW-改造自定义数据源
2020年5月13日
12:53
ZMM_TRAN_KONV改为hana触发器增量上载
源代码：
OPEN CURSOR WITH HOLD S_CURSOR FOR
SELECT A~EBELN
A~EBELP
B~KNUMV
A~EBELP AS KPOSN
FROM EKPO AS A
INNER JOIN EKKO AS B ON A~EBELN = B~EBELN
WHERE ( A~AEDAT IN L_R_AEDAT OR B~AEDAT IN L_R_AEDAT ) AND
A~EBELP IN L_R_EBELP AND
A~EBELN IN L_R_EBELN
.
ENDIF.
FETCH NEXT CURSOR S_CURSOR
APPENDING CORRESPONDING FIELDS
OF TABLE E_T_DATA
PACKAGE SIZE S_S_IF-MAXSIZE.
IF SY-SUBRC <> 0.
CLOSE CURSOR S_CURSOR.
RAISE NO_MORE_DATA.
ENDIF.
FIELD-SYMBOLS: <FS_ZSKONV> TYPE ZSKONV.
SELECT *
FROM KONV
INTO CORRESPONDING FIELDS OF TABLE LT_ZSKONV
FOR ALL ENTRIES IN E_T_DATA
WHERE KNUMV = E_T_DATA-KNUMV
AND KPOSN = E_T_DATA-KPOSN
.
SORT E_T_DATA BY KNUMV KPOSN .
E_T_DATA2 = E_T_DATA[] .
E_T_DATA[] = LT_ZSKONV .
SORT E_T_DATA2 BY KNUMV KPOSN .
LOOP AT E_T_DATA ASSIGNING <FS_ZSKONV> .
READ TABLE E_T_DATA2 INTO LS_ZSKONV WITH KEY KNUMV = <FS_ZSKONV>-KNUMV KPOSN = <FS_ZSKONV>-KPOSN BINARY SEARCH .
IF SY-SUBRC = 0.
<FS_ZSKONV>-EBELN = LS_ZSKONV-EBELN .
<FS_ZSKONV>-EBELP = LS_ZSKONV-EBELP .
ENDIF.
ENDLOOP.
S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.
" LT_EKPO = E_T_DATA .
"SORT LT_EKPO BY EBELN EBELP ETENR ASCENDING .
修改后：
1.在Repositiories处理：增加一张表【TA_BW_DELTA_EKPO.hdbtable】，为增量表，数据来源为触发器
// To define an HDB table with main sql type columns, you can use the following code.
// In the example below a schema should exist.
table.schemaName = "DELIDW";
table.tableType = COLUMNSTORE; // ROWSTORE is an alternative value
table.columns =
[
{name = "MANDT"; sqlType = NVARCHAR; length = 3;},
{name = "EBELN"; sqlType = NVARCHAR; length = 10;},
{name = "EBELP"; sqlType = NVARCHAR; length = 5;},
{name = "CREATEDATE"; sqlType = DATE;},
{name = "CREATETIME"; sqlType = TIME;},
{name = "ACTION"; sqlType = NVARCHAR; length = 10;}
];
//table.primaryKey.pkcolumns = ["MANDT", "VBELN", "EBELP"];
2.在Systems处理：对EKPO做一个触发器：
CREATE TRIGGER "DELIDW"."MONITOR_ERP_DELTA_INSERT_RECORDS_EKPO"
AFTER INSERT ON "DELIDW"."EKPO" REFERENCING NEW ROW NEWROW FOR EACH ROW
BEGIN
INSERT INTO "DELIDW"."delidw.data.table::TA_BW_DELTA_EKPO" VALUES (
SESSION_CONTEXT('CLIENT'),
:newrow."EBELN",
:newrow."EBELP",
CURRENT_DATE,
CURRENT_TIME
'INSERT')
;
END;
CREATE TRIGGER "DELIDW"."MONITOR_ERP_DELTA_UPDATE_RECORDS_EKPO"
AFTER UPDATE ON "DELIDW"."EKPO" REFERENCING NEW ROW NEWROW FOR EACH ROW
BEGIN
INSERT INTO "DELIDW"."delidw.data.table::TA_BW_DELTA_EKPO" VALUES (
SESSION_CONTEXT('CLIENT'),
:newrow."EBELN",
:newrow."EBELP",
CURRENT_DATE,
CURRENT_TIME
'UPDATE')
;
END;
3.在Repositiories处理：做视图
/********* BEGIN PROCEDURE SCRIPT ************/
BEGIN
DECLARE DELTA_NUM INTEGER;
TAB_DELTA =
SELECT *
FROM "DELIDW"."delidw.data.table::TA_BW_DELTA_EKPO" DELTA_EKPO
WHERE "DELTA_EKPO"."MANDT" = SESSION_CONTEXT('CLIENT')
;
SELECT COUNT(1) INTO DELTA_NUM FROM :TAB_DELTA;
IF DELTA_NUM > 0 THEN
TAB_EKPO =
SELECT "EKPO"."EBELN",
"EKPO"."EBELP",
"EKKO"."KNUMV",
"EKPO"."EBELP" AS KPOSN
FROM :TAB_DELTA DELTA_EKPO
INNER JOIN "EP1BWP"."EKPO" EKPO ON "DELTA_EKPO"."MANDT" = "EKPO"."MANDT"
AND "DELTA_EKPO"."EBELN" = "EKPO"."EBELN"
AND "DELTA_EKPO"."EBELP" = "EKPO"."EBELP"
INNER JOIN "EP1BWP"."EKKO" EKKO ON "EKPO"."MANDT" = "EKKO"."MANDT"
AND "EKPO"."EBELN" = "EKKO"."EBELN"
;
ELSE
TAB_EKPO =
SELECT "EKPO"."EBELN",
"EKPO"."EBELP",
"EKKO"."KNUMV",
"EKPO"."EBELP" AS KPOSN
FROM "EP1BWP"."EKPO" EKPO
INNER JOIN "EP1BWP"."EKKO" EKKO ON "EKPO"."MANDT" = "EKKO"."MANDT"
AND "EKPO"."EBELN" = "EKKO"."EBELN"
WHERE "EKPO"."MANDT" = SESSION_CONTEXT('CLIENT')
;
END IF;
TAB_KONV =
SELECT
--"KONV"."EBELN",
--"KONV"."EBELP",
"KONV"."KNUMV",
"KONV"."KPOSN",
"KONV"."STUNR",
"KONV"."ZAEHK",
"KONV"."KSCHL",
"KONV"."KDATU",
"KONV"."KAWRT",
"KONV"."KBETR",
"KONV"."WAERS",
"KONV"."KKURS",
"KONV"."KPEIN",
"KONV"."KMEIN",
"KONV"."KUMZA",
"KONV"."KUMNE"
FROM "EP1BWP"."KONV" KONV
INNER JOIN :TAB_EKPO EKPO ON "EKPO"."KNUMV" = "KONV"."KNUMV"
AND "EKPO"."KPOSN" = "KONV"."KPOSN"
WHERE "KONV"."MANDT" = SESSION_CONTEXT('CLIENT')
;
VAR_OUT =
SELECT
"EKPO"."EBELN" ,
"EKPO"."EBELP" ,
"KONV"."KNUMV",
"KONV"."KPOSN",
"KONV"."STUNR",
"KONV"."ZAEHK",
"KONV"."KSCHL",
"KONV"."KDATU",
"KONV"."KAWRT",
"KONV"."KBETR",
"KONV"."WAERS",
"KONV"."KKURS",
"KONV"."KPEIN",
"KONV"."KMEIN",
"KONV"."KUMZA",
"KONV"."KUMNE"
FROM :TAB_KONV KONV
LEFT JOIN :TAB_EKPO EKPO ON "KONV"."KNUMV" = "EKPO"."KNUMV"
AND "KONV"."KPOSN" = "EKPO"."KPOSN"
;
END /********* END PROCEDURE SCRIPT ************/
1. RSA3测试数据源