1 PACKAGE BODY CZ_IMP_PR_MAIN AS
2 /* $Header: cziprmnb.pls 115.18 2002/12/03 14:47:45 askhacha ship $ */
3
4
5 PROCEDURE CND_PRICE ( inRUN_ID IN PLS_INTEGER,
6 COMMIT_SIZE IN PLS_INTEGER,
7 MAX_ERR IN PLS_INTEGER,
8 FAILED OUT NOCOPY PLS_INTEGER
9 ) IS
10 BEGIN
11 DECLARE
12 CURSOR c_imp_price IS
13 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_PRICE WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
14 /* Internal vars */
15 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
16 nErrorCount PLS_INTEGER:=0; /*Error index */
17 nFailed PLS_INTEGER:=0; /*Failed records */
18 nDups PLS_INTEGER:=0; /*Dupl records */
19 x_error BOOLEAN:=FALSE;
20
21
22 /*Cursor Var for Import */
23 p_imp_price c_imp_price%ROWTYPE;
24 x_imp_price_f BOOLEAN:=FALSE;
25
26 BEGIN
27
28 OPEN c_imp_price;
29 LOOP
30 FETCH c_imp_price INTO p_imp_price;
31 x_imp_price_f:=c_imp_price%FOUND;
32
33 EXIT WHEN(NOT x_imp_price_f Or nFailed >= Max_Err);
34 IF (p_imp_price.DELETED_FLAG IS NULL) THEN
35 BEGIN
36 UPDATE CZ_IMP_PRICE SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_price.ROWID;
37 nCOmmitCount:=nCommitCount+1;
38 /* COMMIT if the buffer size is reached */
39 IF (nCommitCount>= COMMIT_SIZE) THEN
40 COMMIT;
41 nCommitCount:=0;
42 END IF;
43 EXCEPTION
44 WHEN OTHERS THEN
45 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.CND_PRICE',11276);
46 nFailed:=nFailed+1;
47 END;
48 END IF;
49 END LOOP;
50 CLOSE c_imp_price;
51 FAILED:=nFailed;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.CND_PRICE',11276);
56 END;
57
58 END CND_PRICE;
59
60 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
61
62 PROCEDURE CND_PRICE_GROUP ( inRUN_ID IN PLS_INTEGER,
63 COMMIT_SIZE IN PLS_INTEGER,
64 MAX_ERR IN PLS_INTEGER,
65 FAILED OUT NOCOPY PLS_INTEGER
66 ) IS
67 BEGIN
68 DECLARE
69 CURSOR c_imp_pricegroup IS
70 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_PRICE_GROUP WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
71 /* Internal vars */
72 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
73 nErrorCount PLS_INTEGER:=0; /*Error index */
74 nFailed PLS_INTEGER:=0; /*Failed records */
75 nDups PLS_INTEGER:=0; /*Dupl records */
76 x_error BOOLEAN:=FALSE;
77
78
79 /*Cursor Var for Import */
80 p_imp_pricegroup c_imp_pricegroup%ROWTYPE;
81 x_imp_pricegroup_f BOOLEAN:=FALSE;
82
83 BEGIN
84
85 OPEN c_imp_pricegroup;
86 LOOP
87 FETCH c_imp_pricegroup INTO p_imp_pricegroup;
88 x_imp_pricegroup_f:=c_imp_pricegroup%FOUND;
89
90 EXIT WHEN(NOT x_imp_pricegroup_f Or nFailed >= Max_Err);
91 IF (p_imp_pricegroup.DELETED_FLAG IS NULL) THEN
92 BEGIN
93 UPDATE CZ_IMP_PRICE_GROUP SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_pricegroup.ROWID;
94 nCOmmitCount:=nCommitCount+1;
95 /* COMMIT if the buffer size is reached */
96 IF (nCommitCount>= COMMIT_SIZE) THEN
97 COMMIT;
98 nCommitCount:=0;
99 END IF;
100 EXCEPTION
101 WHEN OTHERS THEN
102 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.CND_PRICE_GROUP',11276);
103 nFailed:=nFailed+1;
104 END;
105 END IF;
106 END LOOP;
107 CLOSE c_imp_pricegroup;
108 FAILED:=nFailed;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.CND_PRICE_GROUP',11276);
113 END;
114
115 END CND_PRICE_GROUP;
116
117
118 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
119
120 PROCEDURE MAIN_PRICE ( inRUN_ID IN PLS_INTEGER,
121 COMMIT_SIZE IN PLS_INTEGER,
122 MAX_ERR IN PLS_INTEGER,
123 INSERTS IN OUT NOCOPY PLS_INTEGER,
124 UPDATES IN OUT NOCOPY PLS_INTEGER,
125 FAILED IN OUT NOCOPY PLS_INTEGER,
126 DUPS IN OUT NOCOPY PLS_INTEGER,
127 inXFR_GROUP IN VARCHAR2
128 ) IS
129 BEGIN
130 DECLARE
131 /* Internal vars */
132 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
133 nErrorCount PLS_INTEGER:=0; /*Error index */
134 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
135 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
136 nFailed PLS_INTEGER:=0; /*Failed records */
137 nDups PLS_INTEGER:=0; /*Dupl records */
138 x_error BOOLEAN:=FALSE;
139 dummy CHAR(1);
140
141 BEGIN
142
143 BEGIN
144 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
145
146 UPDATE CZ_XFR_RUN_INFOS SET
147 STARTED=SYSDATE,
148 LAST_ACTIVITY=SYSDATE
149 WHERE RUN_ID=inRUN_ID;
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
154 VALUES(inRUN_ID,SYSDATE,SYSDATE);
155
156 WHEN OTHERS THEN
157 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.MAIN_PRICE',11276);
158 END;
159
160 CZ_IMP_PR_MAIN.CND_PRICE (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
161 IF (nFailed=MAX_ERR) THEN
162 INSERTS:=0;
163 UPDATES:=0;
164 FAILED:=MAX_ERR;
165 DUPS:=0;
166 return;
167 END IF;
168
169 CZ_IMP_PR_KRS.KRS_PRICE (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
170
171 /* Make sure that the error count has not been reached */
172 IF(nFailed < MAX_ERR) THEN
173 CZ_IMP_PR_XFR.XFR_PRICE (inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
174 /* Report Insert Errors */
175 IF (nXfrInsertCount<> INSERTS) THEN
176 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_PR_MAIN.MAIN_PRICE ',11276);
177 END IF;
178
179 /* Report Update Errors */
180 IF (nXfrUpdateCount<> UPDATES) THEN
181 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_PR_MAIN.MAIN_PRICE',11276);
182 END IF;
183
184 /* Return the transferred number of rows and not the number of rows with keys resolved*/
185 INSERTS:=nXfrInsertCount;
186 UPDATES:=nXfrUpdateCount;
187
188 FAILED:=FAILED+nFailed;
189 ELSE
190 FAILED:=nFailed;
191 END IF;
192
193 CZ_IMP_PR_MAIN.RPT_PRICE(inRUN_ID);
194 END ;
195 END MAIN_PRICE ;
196 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
197
198 PROCEDURE MAIN_PRICE_GROUP ( inRUN_ID IN PLS_INTEGER,
199 COMMIT_SIZE IN PLS_INTEGER,
200 MAX_ERR IN PLS_INTEGER,
201 INSERTS IN OUT NOCOPY PLS_INTEGER,
202 UPDATES IN OUT NOCOPY PLS_INTEGER,
203 FAILED IN OUT NOCOPY PLS_INTEGER,
204 DUPS IN OUT NOCOPY PLS_INTEGER,
205 inXFR_GROUP IN VARCHAR2
206 ) IS
207 BEGIN
208 DECLARE
209 /* Internal vars */
210 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
211 nErrorCount PLS_INTEGER:=0; /*Error index */
212 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
213 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
214 nFailed PLS_INTEGER:=0; /*Failed records */
215 nDups PLS_INTEGER:=0; /*Dupl records */
216 x_error BOOLEAN:=FALSE;
217 dummy CHAR(1);
218 BEGIN
219
220 BEGIN
221 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
222
223 UPDATE CZ_XFR_RUN_INFOS SET
224 STARTED=SYSDATE,
225 LAST_ACTIVITY=SYSDATE
226 WHERE RUN_ID=inRUN_ID;
227
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
231 VALUES(inRUN_ID,SYSDATE,SYSDATE);
232
233 WHEN OTHERS THEN
234 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.MAIN_PRICE_GROUP',11276);
235 END;
236
237 CZ_IMP_PR_MAIN.CND_PRICE_GROUP (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
238 IF (nFailed=MAX_ERR) THEN
239 INSERTS:=0;
240 UPDATES:=0;
241 FAILED:=MAX_ERR;
242 DUPS:=0;
243 return;
244 END IF;
245
246 CZ_IMP_PR_KRS.KRS_PRICE_GROUP (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
247
248 /* Make sure that the error count has not been reached */
249 IF(nFailed < MAX_ERR) THEN
250 CZ_IMP_PR_XFR.XFR_PRICE_GROUP(inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
251 /* Report Insert Errors */
252 IF (nXfrInsertCount<> INSERTS) THEN
253 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_PR_MAIN.MAIN_PRICE_GROUP ',11276);
254 END IF;
255
256 /* Report Update Errors */
257 IF (nXfrUpdateCount<> UPDATES) THEN
258 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_PR_MAIN.MAIN_PRICE_GROUP ',11276);
259 END IF;
260
261 /* Return the transferred number of rows and not the number of rows with keys resolved*/
262 INSERTS:=nXfrInsertCount;
263 UPDATES:=nXfrUpdateCount;
264
265 FAILED:=FAILED+nFailed;
266 ELSE
267 FAILED:=nFailed;
268 END IF;
269
270 CZ_IMP_PR_MAIN.RPT_PRICE_GROUP(inRUN_ID);
271 END ;
272 END MAIN_PRICE_GROUP;
273 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
274 PROCEDURE RPT_PRICE ( inRUN_ID IN PLS_INTEGER ) AS
275 x_error BOOLEAN:=FALSE;
276 BEGIN
277 BEGIN
278 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_PRICES';
279
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN NULL;
282 END;
283
284 DECLARE
285 CURSOR c_xfr_run_result IS
286 SELECT DISPOSITION,REC_STATUS,COUNT(*)
287 FROM CZ_IMP_price
288 WHERE RUN_ID = inRUN_ID
289 GROUP BY DISPOSITION,REC_STATUS;
290
291 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
292 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
293 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
294
295 BEGIN
296
297 OPEN c_xfr_run_result;
298 LOOP
299 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
300 EXIT WHEN c_xfr_run_result%NOTFOUND;
301
302 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
303 VALUES(inRUN_ID,'CZ_PRICES',ins_disposition,ins_rec_status,ins_rec_count);
304
305 END LOOP;
306 CLOSE c_xfr_run_result;
307 COMMIT;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.RPT_PRICE',11276);
312 END;
313
314 DECLARE
315 nErrors PLS_INTEGER;
316 CURSOR c_get_nErrors IS
317 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
318 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
319 AND IMP_TABLE='CZ_PRICES';
320 BEGIN
321 OPEN c_get_nErrors;
322 FETCH c_get_nErrors INTO nErrors;
323 CLOSE c_get_nErrors;
324 UPDATE CZ_XFR_RUN_INFOS
325 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
326 COMPLETED='1'
327 WHERE RUN_ID=inRUN_ID;
328 COMMIT;
329 EXCEPTION
330 WHEN OTHERS THEN
331 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.RPT_PRICE',11276);
332 END;
333 EXCEPTION
334 WHEN OTHERS THEN
335 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.RPT_PRICE',11276);
336 END;
337
338 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
339 PROCEDURE RPT_PRICE_GROUP ( inRUN_ID IN PLS_INTEGER ) AS
340 x_error BOOLEAN:=FALSE;
341 BEGIN
342 BEGIN
343 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_PRICE_GROUPS';
344
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN NULL;
347 END;
348
349 DECLARE
350 CURSOR c_xfr_run_result IS
351 SELECT DISPOSITION,REC_STATUS,COUNT(*)
352 FROM CZ_IMP_price_group
353 WHERE RUN_ID = inRUN_ID
354 GROUP BY DISPOSITION,REC_STATUS;
355
356 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
357 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
358 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
359
360 BEGIN
361
362 OPEN c_xfr_run_result;
363 LOOP
364 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
365 EXIT WHEN c_xfr_run_result%NOTFOUND;
366
367 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
368 VALUES(inRUN_ID,'CZ_PRICE_GROUPS',ins_disposition,ins_rec_status,ins_rec_count);
369
370 END LOOP;
371 CLOSE c_xfr_run_result;
372 COMMIT;
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.RPT_PRICE_GROUP',11276);
377 END;
378
379 DECLARE
380 nErrors PLS_INTEGER;
381 CURSOR c_get_nErrors IS
382 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
383 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
384 AND IMP_TABLE='CZ_PRICE_GROUPS';
385 BEGIN
386 OPEN c_get_nErrors;
387 FETCH c_get_nErrors INTO nErrors;
388 CLOSE c_get_nErrors;
389 UPDATE CZ_XFR_RUN_INFOS
390 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
391 COMPLETED='1'
392 WHERE RUN_ID=inRUN_ID;
393 COMMIT;
394 EXCEPTION
395 WHEN OTHERS THEN
396 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.RPT_PRICE_GROUP',11276);
397 END;
398 EXCEPTION
399 WHEN OTHERS THEN
400 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_PR_MAIN.RPT_PRICE_GROUP',11276);
401 END;
402
403 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
404
405 END CZ_IMP_PR_MAIN;
[Home] [Help]