1 PACKAGE BODY CZ_IMP_IM_MAIN AS
2 /* $Header: cziimmnb.pls 120.1 2006/06/22 16:27:25 asiaston ship $ */
3
4
5 PROCEDURE CND_ITEM_MASTER ( inRUN_ID IN PLS_INTEGER,
6 COMMIT_SIZE IN PLS_INTEGER,
7 MAX_ERR IN PLS_INTEGER,
8 FAILED IN OUT NOCOPY PLS_INTEGER
9 ) IS
10 BEGIN
11 DECLARE
12 CURSOR c_imp_itemmaster IS
13 SELECT DELETED_FLAG, SRC_APPLICATION_ID, SRC_TYPE_CODE, DECIMAL_QTY_FLAG, ROWID
14 FROM CZ_IMP_ITEM_MASTER
15 WHERE REC_STATUS IS NULL
16 AND RUN_ID = inRUN_ID;
17 /* Internal vars */
18 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
19 nErrorCount PLS_INTEGER:=0; /*Error index */
20 nDups PLS_INTEGER:=0; /*Dupl records */
21 x_error BOOLEAN:=FALSE;
22
23
24 /*Cursor Var for Import */
25 p_imp_itemmaster c_imp_itemmaster%ROWTYPE;
26 x_imp_itemmaster_f BOOLEAN:=FALSE;
27
28 BEGIN
29
30 OPEN c_imp_itemmaster;
31 LOOP
32 FETCH c_imp_itemmaster INTO p_imp_itemmaster;
33 x_imp_itemmaster_f:=c_imp_itemmaster%FOUND;
34
35 EXIT WHEN NOT x_imp_itemmaster_f;
36 IF (FAILED >= MAX_ERR) THEN
37 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_MAIN.CND_ITEM_MASTER:MAX',11276,inRun_Id);
38 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
39 END IF;
40
41 IF (p_imp_itemmaster.DELETED_FLAG IS NULL) THEN
42 BEGIN
43 UPDATE CZ_IMP_ITEM_MASTER
44 SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG)
45 WHERE ROWID = p_imp_itemmaster.ROWID;
46 nCOmmitCount:=nCommitCount+1;
47 /* COMMIT if the buffer size is reached */
48 IF (nCommitCount>= COMMIT_SIZE) THEN
49 COMMIT;
50 nCommitCount:=0;
51 END IF;
52 EXCEPTION
53 WHEN OTHERS THEN
54 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_MASTER',11276,inRun_ID);
55 FAILED:=FAILED+1;
56 END;
57 END IF;
58
59 IF (p_imp_itemmaster.DECIMAL_QTY_FLAG IS NULL) THEN
60 BEGIN
61 UPDATE CZ_IMP_ITEM_MASTER SET DECIMAL_QTY_FLAG = '0'
62 WHERE ROWID = p_imp_itemmaster.ROWID;
63
64 nCOmmitCount:=nCommitCount+1;
65 /* COMMIT if the buffer size is reached */
66 IF (nCommitCount>= COMMIT_SIZE) THEN
67 COMMIT;
68 nCommitCount:=0;
69 END IF;
70 EXCEPTION
71 WHEN OTHERS THEN
72 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_MASTER',11276,inRun_ID);
73 FAILED:=FAILED+1;
74 END;
75 END IF;
76
77 IF (p_imp_itemmaster.SRC_APPLICATION_ID IS NULL AND p_imp_itemmaster.SRC_TYPE_CODE IS NULL) THEN
78 BEGIN
79 UPDATE CZ_IMP_ITEM_MASTER
80 SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
81 SRC_APPLICATION_ID = cnDefSrcAppId,
82 SRC_TYPE_CODE = cnDefSrcTypeCode
83 WHERE ROWID = p_imp_itemmaster.ROWID;
84 nCOmmitCount:=nCommitCount+1;
85 /* COMMIT if the buffer size is reached */
86 IF (nCommitCount>= COMMIT_SIZE) THEN
87 COMMIT;
88 nCommitCount:=0;
89 END IF;
90 EXCEPTION
91 WHEN OTHERS THEN
92 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_MASTER',11276,inRun_ID);
93 FAILED:=FAILED+1;
94 END;
95 ELSIF (p_imp_itemmaster.SRC_APPLICATION_ID IS NULL) THEN
96 BEGIN
97 UPDATE CZ_IMP_ITEM_MASTER
98 SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
99 SRC_APPLICATION_ID = cnDefSrcAppId
100 WHERE ROWID = p_imp_itemmaster.ROWID;
101 nCOmmitCount:=nCommitCount+1;
102 /* COMMIT if the buffer size is reached */
103 IF (nCommitCount>= COMMIT_SIZE) THEN
104 COMMIT;
105 nCommitCount:=0;
106 END IF;
107 EXCEPTION
108 WHEN OTHERS THEN
109 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_MASTER',11276,inRun_ID);
110 FAILED:=FAILED+1;
111 END;
112 ELSIF (p_imp_itemmaster.SRC_TYPE_CODE IS NULL) THEN
113 BEGIN
114 UPDATE CZ_IMP_ITEM_MASTER
115 SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
116 SRC_TYPE_CODE = cnDefSrcTypeCode
117 WHERE ROWID = p_imp_itemmaster.ROWID;
118 nCOmmitCount:=nCommitCount+1;
119 /* COMMIT if the buffer size is reached */
120 IF (nCommitCount>= COMMIT_SIZE) THEN
121 COMMIT;
122 nCommitCount:=0;
123 END IF;
124 EXCEPTION
125 WHEN OTHERS THEN
126 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_MASTER',11276,inRun_ID);
127 FAILED:=FAILED+1;
128 END;
129 END IF;
130
131 END LOOP;
132 CLOSE c_imp_itemmaster;
133
134 EXCEPTION
135 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
136 RAISE;
137 WHEN OTHERS THEN
138 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_MASTER',11276,inRun_ID);
139 END;
140
141 END CND_ITEM_MASTER ;
142
143
144 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
145 PROCEDURE CND_ITEM_PROPERTY_VALUE (inRUN_ID IN PLS_INTEGER,
146 COMMIT_SIZE IN PLS_INTEGER,
147 MAX_ERR IN PLS_INTEGER,
148 FAILED IN OUT NOCOPY PLS_INTEGER
149 ) IS
150 CURSOR c_imp_itempropertyvalue IS
151 SELECT a.DELETED_FLAG,a.PROPERTY_VALUE,a.PROPERTY_NUM_VALUE,
152 a.FSK_PROPERTY_1_1,a.FSK_PROPERTY_1_EXT,a.ORIG_SYS_REF,a.DISPOSITION,a.REC_STATUS,b.data_type
153 FROM CZ_IMP_ITEM_PROPERTY_VALUE a, cz_properties b
154 WHERE a.REC_STATUS IS NULL AND a.RUN_ID = inRUN_ID
155 AND (b.orig_sys_ref = a.fsk_property_1_1 OR b.orig_sys_ref = a.fsk_property_1_ext)
156 AND b.deleted_flag='0';
157
158 /* Internal vars */
159 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
160 nErrorCount PLS_INTEGER:=0; /*Error index */
161 nDups PLS_INTEGER:=0; /*Dupl records */
162 x_error BOOLEAN:=FALSE;
163 /*Cursor Var for Import */
164 p_imp_itempropertyvalue c_imp_itempropertyvalue%ROWTYPE;
165 x_imp_itempropertyvalue_f BOOLEAN:=FALSE;
166 xERROR BOOLEAN:=FALSE;
167
168 TYPE tPropertyValue IS TABLE OF cz_imp_item_property_value.property_value%TYPE INDEX BY BINARY_INTEGER;
169 TYPE tPropertyNumValue IS TABLE OF cz_imp_item_property_value.property_num_value%TYPE INDEX BY BINARY_INTEGER;
170 TYPE tFskProperty IS TABLE OF cz_imp_item_property_value.fsk_property_1_1%TYPE INDEX BY BINARY_INTEGER;
171 TYPE tFskPropertyExt IS TABLE OF cz_imp_item_property_value.fsk_property_1_ext%TYPE INDEX BY BINARY_INTEGER;
172 TYPE tFskItemMaster IS TABLE OF cz_imp_item_property_value.fsk_itemmaster_2_1%TYPE INDEX BY BINARY_INTEGER;
173 TYPE tDisposition IS TABLE OF cz_imp_item_property_value.disposition%TYPE INDEX BY BINARY_INTEGER;
174 TYPE tRecStatus IS TABLE OF cz_imp_item_property_value.rec_status%TYPE INDEX BY BINARY_INTEGER;
175 TYPE tDeletedFlag IS TABLE OF cz_imp_item_property_value.deleted_flag%TYPE INDEX BY BINARY_INTEGER;
176 TYPE tOrigSysRef IS TABLE OF cz_imp_item_property_value.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
177 TYPE tDataType IS TABLE OF cz_properties.data_type%TYPE INDEX BY BINARY_INTEGER;
178
179 iFskProperty tFskProperty;
180 iFskPropertyExt tFskPropertyExt;
181 iOrigSysRef tOrigSysRef;
182 iPropertyValue tPropertyValue;
183 iPropertyNumValue tPropertyNumValue;
184 iFskItemMaster tFskItemMaster;
185 iDisposition tDisposition;
186 iRecStatus tRecStatus;
187 iDeletedFlag tDeletedFlag;
188 iDataType tDataType;
189
190 st_time NUMBER;
191 end_time NUMBER;
192 loop_end_time NUMBER;
193 insert_end_time NUMBER;
194
195 BEGIN
196 OPEN c_imp_itempropertyvalue;
197 LOOP
198 iDeletedFlag.delete; iFskProperty.delete;
199 iPropertyValue.delete; iRecStatus.delete;
200 iPropertyNumValue.delete; iDisposition.delete;
201 iFskPropertyExt.delete; iDataType.delete;
202
203 FETCH c_imp_itempropertyvalue BULK COLLECT INTO
204 iDeletedFlag,iPropertyValue,iPropertyNumValue,
205 iFskProperty,iFskPropertyExt,iOrigSysRef,iDisposition,iRecStatus,iDataType
206 LIMIT COMMIT_SIZE;
207 EXIT WHEN iDeletedFlag.COUNT = 0 AND c_imp_itempropertyvalue%NOTFOUND;
208
209 IF iDeletedFlag.COUNT > 0 THEN
210
211 IF (FAILED >= MAX_ERR) THEN
212 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,
213 'CZ_IMP_IM_MAIN.CND_ITEM_PROPERTY_VALUE:MAX',11276,inRun_Id);
214 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
215 END IF;
216 FOR i IN iDeletedFlag.FIRST..iDeletedFlag.LAST LOOP
217 IF iDeletedFlag(i) IS NULL THEN
218 iDeletedFlag(i) := '0';
219 END IF;
220
221 IF (iPropertyValue(i) IS NULL AND iPropertyNumValue(i) IS NULL) THEN
222 iDisposition(i) := 'R';
223 iRecStatus(i) := 'FAIL';
224 FAILED := FAILED + 1;
225 xERROR:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_NULL_PROPVAL',
226 'PROPNAME',iFskProperty(i))||iFskPropertyExt(i),
227 1,'EXTR_ITEM_PROPERTY_VALUES',11276,inRun_ID);
228 END IF;
229 BEGIN
230 IF (iDataType(i) = 2 AND iPropertyValue(i) IS NOT NULL AND iPropertyNumValue(i) IS NULL) THEN
231 iPropertyNumValue(i) := TO_NUMBER(iPropertyValue(i));
232 iPropertyValue(i) := NULL;
233 END IF;
234 EXCEPTION
235 WHEN VALUE_ERROR THEN
236 iDisposition(i) := 'R';
237 iRecStatus(i) := 'FAIL';
238 FAILED := FAILED + 1;
239 xERROR:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_PROPVAL',
240 'PROPNAME',iFskProperty(i)||iFskPropertyExt(i),
241 'PROPVAL',iPropertyValue(i)),
242 1,'EXTR_ITEM_PROPERTY_VALUES',11276,inRun_ID);
243 END;
244 END LOOP;
245 FORALL i IN iDeletedFlag.FIRST..iDeletedFlag.LAST
246 UPDATE cz_imp_item_property_value
247 SET deleted_flag = iDeletedFlag(i),
248 property_value = iPropertyValue(i),
249 property_num_value = iPropertyNumValue(i),
250 disposition = iDisposition(i),
251 rec_status = iRecStatus(i)
252 WHERE orig_sys_ref = iOrigSysRef(i)
253 AND run_id = inRun_id
254 AND rec_status IS NULL;
255 END IF;
256 END LOOP;
257 CLOSE c_imp_itempropertyvalue;
258 EXCEPTION
259 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
260 RAISE;
261 WHEN OTHERS THEN
262 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_PROPERTY_VALUE',11276,inRun_ID);
263 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
264 END CND_ITEM_PROPERTY_VALUE ;
265 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
266
267 PROCEDURE CND_ITEM_TYPE ( inRUN_ID IN PLS_INTEGER,
268 COMMIT_SIZE IN PLS_INTEGER,
269 MAX_ERR IN PLS_INTEGER,
270 FAILED IN OUT NOCOPY PLS_INTEGER
271 ) IS
272 BEGIN
273 DECLARE
274 CURSOR c_imp_itemtype IS
275 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_ITEM_TYPE WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
276 /* Internal vars */
277 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
278 nErrorCount PLS_INTEGER:=0; /*Error index */
279 nDups PLS_INTEGER:=0; /*Dupl records */
280 x_error BOOLEAN:=FALSE;
281
282
283 /*Cursor Var for Import */
284 p_imp_itemtype c_imp_itemtype%ROWTYPE;
285 x_imp_itemtype_f BOOLEAN:=FALSE;
286
287 BEGIN
288 OPEN c_imp_itemtype;
289 LOOP
290 FETCH c_imp_itemtype INTO p_imp_itemtype;
291 x_imp_itemtype_f:=c_imp_itemtype%FOUND;
292
293 EXIT WHEN NOT x_imp_itemtype_f;
294 IF (FAILED >= MAX_ERR) THEN
295 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_MAIN.CND_ITEM_TYPE:MAX',11276,inRun_Id);
296 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
297 END IF;
298
299 IF (p_imp_itemtype.DELETED_FLAG IS NULL) THEN
300 BEGIN
301 UPDATE CZ_IMP_ITEM_TYPE SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_itemtype.ROWID;
302 nCOmmitCount:=nCommitCount+1;
303 /* COMMIT if the buffer size is reached */
304 IF (nCommitCount>= COMMIT_SIZE) THEN
305 COMMIT;
306 nCommitCount:=0;
307 END IF;
308 EXCEPTION
309 WHEN OTHERS THEN
310 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_TYPE',11276,inRun_ID);
311 FAILED:=FAILED+1;
312 END;
313 END IF;
314 END LOOP;
315 CLOSE c_imp_itemtype;
316
317 EXCEPTION
318 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
319 RAISE;
320 WHEN OTHERS THEN
321 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_TYPE',11276,inRun_ID);
322 END;
323
324 END CND_ITEM_TYPE;
325
326
327 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
328
329 PROCEDURE CND_ITEM_TYPE_PROPERTY ( inRUN_ID IN PLS_INTEGER,
330 COMMIT_SIZE IN PLS_INTEGER,
331 MAX_ERR IN PLS_INTEGER,
332 FAILED IN OUT NOCOPY PLS_INTEGER
333 ) IS
334 BEGIN
335 DECLARE
336 CURSOR c_imp_itemtypeprop IS
337 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_ITEM_TYPE_PROPERTY WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
338 /* Internal vars */
339 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
340 nErrorCount PLS_INTEGER:=0; /*Error index */
341 nDups PLS_INTEGER:=0; /*Dupl records */
342 x_error BOOLEAN:=FALSE;
343
344
345 /*Cursor Var for Import */
346 p_imp_itemtypeprop c_imp_itemtypeprop%ROWTYPE;
347 x_imp_itemtypeprop_f BOOLEAN:=FALSE;
348
349 BEGIN
350
351 OPEN c_imp_itemtypeprop;
352 LOOP
353 FETCH c_imp_itemtypeprop INTO p_imp_itemtypeprop;
354 x_imp_itemtypeprop_f:=c_imp_itemtypeprop%FOUND;
355
356 EXIT WHEN NOT x_imp_itemtypeprop_f;
357 IF (FAILED >= MAX_ERR) THEN
358 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_MAIN.CND_ITEM_TYPE_PROPERTY:MAX',11276,inRun_Id);
359 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
360 END IF;
361
362 IF (p_imp_itemtypeprop.DELETED_FLAG IS NULL) THEN
363 BEGIN
364 UPDATE CZ_IMP_ITEM_TYPE_PROPERTY SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_itemtypeprop.ROWID;
365 nCOmmitCount:=nCommitCount+1;
366 /* COMMIT if the buffer size is reached */
367 IF (nCommitCount>= COMMIT_SIZE) THEN
368 COMMIT;
369 nCommitCount:=0;
370 END IF;
371 EXCEPTION
372 WHEN OTHERS THEN
373 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_TYPE_PROPERTY',11276,inRun_ID);
374 FAILED:=FAILED+1;
375 END;
376 END IF;
377 END LOOP;
378 CLOSE c_imp_itemtypeprop;
379
380 EXCEPTION
381 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
382 RAISE;
383 WHEN OTHERS THEN
384 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_ITEM_TYPE_PROPERTY',11276,inRun_ID);
385 END;
386
387 END CND_ITEM_TYPE_PROPERTY;
388
389 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
390
391
392
393 PROCEDURE CND_PROPERTY ( inRUN_ID IN PLS_INTEGER,
394 COMMIT_SIZE IN PLS_INTEGER,
395 MAX_ERR IN PLS_INTEGER,
396 FAILED IN OUT NOCOPY PLS_INTEGER
397 ) IS
398 BEGIN
399 DECLARE
400 CURSOR c_imp_property IS
401 SELECT DELETED_FLAG, DATA_TYPE, ROWID
402 FROM CZ_IMP_property WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
403 /* Internal vars */
404 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
405 nErrorCount PLS_INTEGER:=0; /*Error index */
406 nDups PLS_INTEGER:=0; /*Dupl records */
407 x_error BOOLEAN:=FALSE;
408
409
410 /*Cursor Var for Import */
411 p_imp_property c_imp_property%ROWTYPE;
412 x_imp_property_f BOOLEAN:=FALSE;
413
414 BEGIN
415
416 OPEN c_imp_property;
417 LOOP
418 FETCH c_imp_property INTO p_imp_property;
419 x_imp_property_f:=c_imp_property%FOUND;
420
421 EXIT WHEN NOT x_imp_property_f;
422 IF (FAILED >= MAX_ERR) THEN
423 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_MAIN.CND_PROPERTY:MAX',11276,inRun_Id);
424 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
425 END IF;
426
427 IF (p_imp_property.DELETED_FLAG IS NULL) THEN
428 BEGIN
429 UPDATE CZ_IMP_property SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_property.ROWID;
430 nCOmmitCount:=nCommitCount+1;
431 /* COMMIT if the buffer size is reached */
432 IF (nCommitCount>= COMMIT_SIZE) THEN
433 COMMIT;
434 nCommitCount:=0;
435 END IF;
436 EXCEPTION
437 WHEN OTHERS THEN
438 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_PROPERTY',11276,inRun_ID);
439 FAILED:=FAILED+1;
440 END;
441 END IF;
442
443 IF (p_imp_property.DATA_TYPE IS NULL) THEN
444 BEGIN
445
446 UPDATE CZ_IMP_property SET DATA_TYPE = 4 WHERE ROWID = p_imp_property.ROWID;
447
448 nCOmmitCount:=nCommitCount+1;
449 /* COMMIT if the buffer size is reached */
450 IF (nCommitCount>= COMMIT_SIZE) THEN
451 COMMIT;
452 nCommitCount:=0;
453 END IF;
454 EXCEPTION
455 WHEN OTHERS THEN
456 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_PROPERTY',11276,inRun_ID);
457 FAILED:=FAILED+1;
458 END;
459 END IF;
460 END LOOP;
461 CLOSE c_imp_property;
462
463 EXCEPTION
464 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
465 RAISE;
466 WHEN OTHERS THEN
467 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.CND_PROPERTY',11276,inRun_ID);
468 END;
469
470 END CND_PROPERTY;
471
472 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
473 PROCEDURE MAIN_ITEM_MASTER ( inRUN_ID IN PLS_INTEGER,
474 COMMIT_SIZE IN PLS_INTEGER,
475 MAX_ERR IN PLS_INTEGER,
476 INSERTS IN OUT NOCOPY PLS_INTEGER,
477 UPDATES IN OUT NOCOPY PLS_INTEGER,
478 FAILED IN OUT NOCOPY PLS_INTEGER,
479 DUPS IN OUT NOCOPY PLS_INTEGER,
480 inXFR_GROUP IN VARCHAR2
481 ) IS
482 BEGIN
483 DECLARE
484 /* Internal vars */
485 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
486 nErrorCount PLS_INTEGER:=0; /*Error index */
487 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
488 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
489 nDups PLS_INTEGER:=0; /*Dupl records */
490 x_error BOOLEAN:=FALSE;
491 dummy CHAR(1);
492
493 st_time number;
494 end_time number;
495 loop_end_time number;
496 insert_end_time number;
497 d_str varchar2(255);
498
499 BEGIN
500
501 BEGIN
502 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
503
504 UPDATE CZ_XFR_RUN_INFOS SET
505 STARTED=SYSDATE,
506 LAST_ACTIVITY=SYSDATE
507 WHERE RUN_ID=inRUN_ID;
508
509 EXCEPTION
510 WHEN NO_DATA_FOUND THEN
511 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
512 VALUES(inRUN_ID,SYSDATE,SYSDATE);
513
514 WHEN OTHERS THEN
515 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.MAIN_ITEM_MASTER',11276,inRun_ID);
516 END;
517
518 if (CZ_IMP_ALL.get_time) then
519 st_time := dbms_utility.get_time();
520 end if;
521
522 CZ_IMP_IM_MAIN.CND_ITEM_MASTER (inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
523
524 if (CZ_IMP_ALL.get_time) then
525 end_time := dbms_utility.get_time();
526 d_str := inRun_id || ' CND item master :' || (end_time-st_time)/100.00;
527 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299);
528 end if;
529
530 if (CZ_IMP_ALL.get_time) then
531 st_time := dbms_utility.get_time();
532 end if;
533
534 CZ_IMP_IM_KRS.KRS_ITEM_MASTER (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
535
536 if (CZ_IMP_ALL.get_time) then
537 end_time := dbms_utility.get_time();
538 d_str := inRun_id || ' KRS item master :' || (end_time-st_time)/100.00;
539 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299);
540 end if;
541
542 if (CZ_IMP_ALL.get_time) then
543 st_time := dbms_utility.get_time();
544 end if;
545
546 CZ_IMP_IM_XFR.XFR_ITEM_MASTER (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
547
548 if (CZ_IMP_ALL.get_time) then
549 end_time := dbms_utility.get_time();
550 d_str := inRun_id || ' XFR item master :' || (end_time-st_time)/100.00;
551 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299);
552 end if;
553
554 /* Report Insert Errors */
555 IF (nXfrInsertCount<> INSERTS) THEN
556 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'IMP_IM.MAIN_ITEM_MASTER ',11276,inRun_ID);
557 END IF;
558
559 /* Report Update Errors */
560 IF (nXfrUpdateCount<> UPDATES) THEN
561 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'IMP_IM.MAIN_ITEM_MASTER ',11276,inRun_ID);
562 END IF;
563
564 /* Return the transferred number of rows and not the number of rows with keys resolved*/
565 INSERTS:=nXfrInsertCount;
566 UPDATES:=nXfrUpdateCount;
567
568 CZ_IMP_IM_MAIN.RPT_ITEM_MASTER (inRUN_ID);
569 END;
570 END MAIN_ITEM_MASTER ;
571
572 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
573
574 PROCEDURE MAIN_ITEM_PROPERTY_VALUE( inRUN_ID IN PLS_INTEGER,
575 COMMIT_SIZE IN PLS_INTEGER,
576 MAX_ERR IN PLS_INTEGER,
577 INSERTS IN OUT NOCOPY PLS_INTEGER,
578 UPDATES IN OUT NOCOPY PLS_INTEGER,
579 FAILED IN OUT NOCOPY PLS_INTEGER,
580 DUPS IN OUT NOCOPY PLS_INTEGER,
581 inXFR_GROUP IN VARCHAR2
582 ) IS
583 BEGIN
584 DECLARE
585 /* Internal vars */
586 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
587 nErrorCount PLS_INTEGER:=0; /*Error index */
588 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
589 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
590 nDups PLS_INTEGER:=0; /*Dupl records */
591 x_error BOOLEAN:=FALSE;
592 dummy CHAR(1);
593
594 st_time number;
595 end_time number;
596 loop_end_time number;
597 insert_end_time number;
598 d_str varchar2(255);
599
600 BEGIN
601
602 BEGIN
603 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
604
605 UPDATE CZ_XFR_RUN_INFOS SET
606 STARTED=SYSDATE,
607 LAST_ACTIVITY=SYSDATE
608 WHERE RUN_ID=inRUN_ID;
609
610 EXCEPTION
611 WHEN NO_DATA_FOUND THEN
612 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
613 VALUES(inRUN_ID,SYSDATE,SYSDATE);
614
615 WHEN OTHERS THEN
616 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE',11276,inRun_ID);
617 END;
618
619 if (CZ_IMP_ALL.get_time) then
620 st_time := dbms_utility.get_time();
621 end if;
622
623 CZ_IMP_IM_MAIN.CND_ITEM_PROPERTY_VALUE (inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
624
625 if (CZ_IMP_ALL.get_time) then
626 end_time := dbms_utility.get_time();
627 d_str := inRun_id || ' CND item prop val :' || (end_time-st_time)/100.00;
628 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299);
629 end if;
630
631
632 if (CZ_IMP_ALL.get_time) then
633 st_time := dbms_utility.get_time();
634 end if;
635
636 CZ_IMP_IM_KRS.KRS_ITEM_PROPERTY_VALUE (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
637
638 if (CZ_IMP_ALL.get_time) then
639 end_time := dbms_utility.get_time();
640 d_str := inRun_id || ' KRS item prop val :' || (end_time-st_time)/100.00;
641 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299);
642 end if;
643
644 if (CZ_IMP_ALL.get_time) then
645 st_time := dbms_utility.get_time();
646 end if;
647
648 CZ_IMP_IM_XFR.XFR_ITEM_PROPERTY_VALUE(inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
649
650 if (CZ_IMP_ALL.get_time) then
651 end_time := dbms_utility.get_time();
652 d_str := inRun_id || ' XFR item prop val :' || (end_time-st_time)/100.00;
653 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299);
654 end if;
655
656 /* Report Insert Errors */
657 IF (nXfrInsertCount<> INSERTS) THEN
658 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE',11276,inRun_ID);
659 END IF;
660
661 /* Report Update Errors */
662 IF (nXfrUpdateCount<> UPDATES) THEN
663 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE',11276,inRun_ID);
664 END IF;
665
666 /* Return the transferred number of rows and not the number of rows with keys resolved*/
667 INSERTS:=nXfrInsertCount;
668 UPDATES:=nXfrUpdateCount;
669
670 CZ_IMP_IM_MAIN.RPT_ITEM_PROPERTY_VALUE(inRUN_ID);
671 END;
672 END MAIN_ITEM_PROPERTY_VALUE ;
673
674 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
675
676 PROCEDURE MAIN_ITEM_TYPE ( inRUN_ID IN PLS_INTEGER,
677 COMMIT_SIZE IN PLS_INTEGER,
678 MAX_ERR IN PLS_INTEGER,
679 INSERTS IN OUT NOCOPY PLS_INTEGER,
680 UPDATES IN OUT NOCOPY PLS_INTEGER,
681 FAILED IN OUT NOCOPY PLS_INTEGER,
682 DUPS IN OUT NOCOPY PLS_INTEGER,
683 inXFR_GROUP IN VARCHAR2
684 ) IS
685 BEGIN
686 DECLARE
687 /* Internal vars */
688 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
689 nErrorCount PLS_INTEGER:=0; /*Error index */
690 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
691 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
692 nDups PLS_INTEGER:=0; /*Dupl records */
693 x_error BOOLEAN:=FALSE;
694 dummy CHAR(1);
695
696 st_time number;
697 end_time number;
698 loop_end_time number;
699 insert_end_time number;
700 d_str varchar2(255);
701
702 BEGIN
703
704 BEGIN
705 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
706
707 UPDATE CZ_XFR_RUN_INFOS SET
708 STARTED=SYSDATE,
709 LAST_ACTIVITY=SYSDATE
710 WHERE RUN_ID=inRUN_ID;
711
712 EXCEPTION
713 WHEN NO_DATA_FOUND THEN
714 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
715 VALUES(inRUN_ID,SYSDATE,SYSDATE);
716
717 WHEN OTHERS THEN
718 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.MAIN_ITEM_TYPE',11276,inRun_ID);
719 END;
720
721 if (CZ_IMP_ALL.get_time) then
722 st_time := dbms_utility.get_time();
723 end if;
724 CZ_IMP_IM_MAIN.CND_ITEM_TYPE (inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
725 if (CZ_IMP_ALL.get_time) then
726 end_time := dbms_utility.get_time();
727 d_str := inRun_id || ' CND item type :' || (end_time-st_time)/100.00;
728 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299);
729 end if;
730
731 if (CZ_IMP_ALL.get_time) then
732 st_time := dbms_utility.get_time();
733 end if;
734
735 CZ_IMP_IM_KRS.KRS_ITEM_TYPE (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
736
737 if (CZ_IMP_ALL.get_time) then
738 end_time := dbms_utility.get_time();
739 d_str := inRun_id || ' KRS item type :' || (end_time-st_time)/100.00;
740 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299);
741 end if;
742
743 if (CZ_IMP_ALL.get_time) then
744 st_time := dbms_utility.get_time();
745 end if;
746
747 CZ_IMP_IM_XFR.XFR_ITEM_TYPE (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
748
749 if (CZ_IMP_ALL.get_time) then
750 end_time := dbms_utility.get_time();
751 d_str := inRun_id || ' XFR item type :' || (end_time-st_time)/100.00;
752 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299);
753 end if;
754
755 /* Report Insert Errors */
756 IF (nXfrInsertCount<> INSERTS) THEN
757 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE',11276,inRun_ID);
758 END IF;
759
760 /* Report Update Errors */
761 IF (nXfrUpdateCount<> UPDATES) THEN
762 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE',11276,inRun_ID);
763 END IF;
764
765 /* Return the transferred number of rows and not the number of rows with keys resolved*/
766 INSERTS:=nXfrInsertCount;
767 UPDATES:=nXfrUpdateCount;
768
769 CZ_IMP_IM_MAIN.RPT_ITEM_TYPE(inRUN_ID);
770 END;
771 END MAIN_ITEM_TYPE ;
772
773 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
774
775 PROCEDURE MAIN_ITEM_TYPE_PROPERTY ( inRUN_ID IN PLS_INTEGER,
776 COMMIT_SIZE IN PLS_INTEGER,
777 MAX_ERR IN PLS_INTEGER,
778 INSERTS IN OUT NOCOPY PLS_INTEGER,
779 UPDATES IN OUT NOCOPY PLS_INTEGER,
780 FAILED IN OUT NOCOPY PLS_INTEGER,
781 DUPS IN OUT NOCOPY PLS_INTEGER,
782 inXFR_GROUP IN VARCHAR2
783 ) IS
784 BEGIN
785 DECLARE
786 /* Internal vars */
787 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
788 nErrorCount PLS_INTEGER:=0; /*Error index */
789 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
790 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
791 nDups PLS_INTEGER:=0; /*Dupl records */
792 x_error BOOLEAN:=FALSE;
793 dummy CHAR(1);
794
795 st_time number;
796 end_time number;
797 loop_end_time number;
798 insert_end_time number;
799 d_str varchar2(255);
800
801 BEGIN
802
803 BEGIN
804 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
805
806 UPDATE CZ_XFR_RUN_INFOS SET
807 STARTED=SYSDATE,
808 LAST_ACTIVITY=SYSDATE
809 WHERE RUN_ID=inRUN_ID;
810
811 EXCEPTION
812 WHEN NO_DATA_FOUND THEN
813 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
814 VALUES(inRUN_ID,SYSDATE,SYSDATE);
815
816 WHEN OTHERS THEN
817 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY',11276,inRun_ID);
818 END;
819
820 if (CZ_IMP_ALL.get_time) then
821 st_time := dbms_utility.get_time();
822 end if;
823
824 CZ_IMP_IM_MAIN.CND_ITEM_TYPE_PROPERTY (inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
825
826 if (CZ_IMP_ALL.get_time) then
827 end_time := dbms_utility.get_time();
828 d_str := inRun_id || ' CND item type prop :' || (end_time-st_time)/100.00;
829 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299);
830 end if;
831
832 if (CZ_IMP_ALL.get_time) then
833 st_time := dbms_utility.get_time();
834 end if;
835
836 CZ_IMP_IM_KRS.KRS_ITEM_TYPE_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
837
838 if (CZ_IMP_ALL.get_time) then
839 end_time := dbms_utility.get_time();
840 d_str := inRun_id || ' KRS item type prop :' || (end_time-st_time)/100.00;
841 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299);
842 end if;
843
844 if (CZ_IMP_ALL.get_time) then
845 st_time := dbms_utility.get_time();
846 end if;
847
848 CZ_IMP_IM_XFR.XFR_ITEM_TYPE_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
849
850 if (CZ_IMP_ALL.get_time) then
851 end_time := dbms_utility.get_time();
852 d_str := inRun_id || ' XFR item type prop :' || (end_time-st_time)/100.00;
853 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299);
854 end if;
855
856 /* Report Insert Errors */
857 IF (nXfrInsertCount<> INSERTS) THEN
858 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY',11276,inRun_ID);
859 END IF;
860
861 /* Report Update Errors */
862 IF (nXfrUpdateCount<> UPDATES) THEN
863 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY',11276,inRun_ID);
864 END IF;
865
866 /* Return the transferred number of rows and not the number of rows with keys resolved*/
867 INSERTS:=nXfrInsertCount;
868 UPDATES:=nXfrUpdateCount;
869
870 CZ_IMP_IM_MAIN.RPT_ITEM_TYPE_PROPERTY(inRUN_ID);
871 END;
872 END MAIN_ITEM_TYPE_PROPERTY ;
873
874
875
876 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
877
878 PROCEDURE MAIN_PROPERTY ( inRUN_ID IN PLS_INTEGER,
879 COMMIT_SIZE IN PLS_INTEGER,
880 MAX_ERR IN PLS_INTEGER,
881 INSERTS IN OUT NOCOPY PLS_INTEGER,
882 UPDATES IN OUT NOCOPY PLS_INTEGER,
883 FAILED IN OUT NOCOPY PLS_INTEGER,
884 DUPS IN OUT NOCOPY PLS_INTEGER,
885 inXFR_GROUP IN VARCHAR2,
886 p_rp_folder_id IN NUMBER
887 ) IS
888 BEGIN
889 DECLARE
890 /* Interal vars */
891 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
892 nErrorCount PLS_INTEGER:=0; /*Error index */
893 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
894 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
895 nDups PLS_INTEGER:=0; /*Dupl records */
896 x_error BOOLEAN:=FALSE;
897 dummy CHAR(1);
898
899 st_time number;
900 end_time number;
901 loop_end_time number;
902 insert_end_time number;
903 d_str varchar2(255);
904
905 BEGIN
906
907 BEGIN
908 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
909
910 UPDATE CZ_XFR_RUN_INFOS SET
911 STARTED=SYSDATE,
912 LAST_ACTIVITY=SYSDATE
913 WHERE RUN_ID=inRUN_ID;
914
915 EXCEPTION
916 WHEN NO_DATA_FOUND THEN
917 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
918 VALUES(inRUN_ID,SYSDATE,SYSDATE);
919
920 WHEN OTHERS THEN
921 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.MAIN_PROPERTY',11276,inRun_ID);
922 END;
923
924 if (CZ_IMP_ALL.get_time) then
925 st_time := dbms_utility.get_time();
926 end if;
927
928 CZ_IMP_IM_MAIN.CND_PROPERTY (inRun_ID,COMMIT_SIZE,MAX_ERR,FAILED);
929
930 if (CZ_IMP_ALL.get_time) then
931 end_time := dbms_utility.get_time();
932 d_str := inRun_id || ' CND property :' || (end_time-st_time)/100.00;
933 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'CND',11299);
934 end if;
935
936 if (CZ_IMP_ALL.get_time) then
937 st_time := dbms_utility.get_time();
938 end if;
939
940 CZ_IMP_IM_KRS.KRS_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
941
942 if (CZ_IMP_ALL.get_time) then
943 end_time := dbms_utility.get_time();
944 d_str := inRun_id || ' KRS property :' || (end_time-st_time)/100.00;
945 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'KRS',11299);
946 end if;
947
948 if (CZ_IMP_ALL.get_time) then
949 st_time := dbms_utility.get_time();
950 end if;
951
952 CZ_IMP_IM_XFR.XFR_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP, p_rp_folder_id);
953
954 if (CZ_IMP_ALL.get_time) then
955 end_time := dbms_utility.get_time();
956 d_str := inRun_id || ' XFR property :' || (end_time-st_time)/100.00;
957 x_ERROR:=CZ_UTILS.LOG_REPORT(d_str,1,'XFR',11299);
958 end if;
959
960 /* Report Insert Errors */
961 IF (nXfrInsertCount<> INSERTS) THEN
962 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_PROPERTY',11276,inRun_ID);
963 END IF;
964
965 /* Report Update Errors */
966 IF (nXfrUpdateCount<> UPDATES) THEN
967 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_PROPERTY',11276,inRun_ID);
968 END IF;
969
970 /* Return the transferred number of rows and not the number of rows with keys resolved*/
971 INSERTS:=nXfrInsertCount;
972 UPDATES:=nXfrUpdateCount;
973
974 CZ_IMP_IM_MAIN.RPT_PROPERTY(inRUN_ID);
975 END ;
976
977 END MAIN_PROPERTY ;
978
979 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
980 PROCEDURE RPT_ITEM_MASTER ( inRUN_ID IN PLS_INTEGER ) AS
981 x_error BOOLEAN:=FALSE;
982 v_table_name VARCHAR2(30) := 'CZ_ITEM_MASTERS';
983 v_ok VARCHAR2(4) := 'OK';
984 v_completed VARCHAR2(1) := '1';
985 BEGIN
986 BEGIN
987 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
988
989 EXCEPTION
990 WHEN NO_DATA_FOUND THEN NULL;
991 END;
992
993 DECLARE
994 CURSOR c_xfr_run_result IS
995 SELECT DISPOSITION,REC_STATUS,COUNT(*)
996 FROM CZ_IMP_item_master
997 WHERE RUN_ID = inRUN_ID
998 GROUP BY DISPOSITION,REC_STATUS;
999
1000 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
1001 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
1002 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
1003
1004 BEGIN
1005
1006 OPEN c_xfr_run_result;
1007 LOOP
1008 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1009 EXIT WHEN c_xfr_run_result%NOTFOUND;
1010
1011 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1012 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
1013
1014 END LOOP;
1015 CLOSE c_xfr_run_result;
1016 COMMIT;
1017
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_MASTER',11276,inRun_ID);
1021 END;
1022
1023 DECLARE
1024 nErrors PLS_INTEGER;
1025 CURSOR c_get_nErrors IS
1026 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1027 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
1028 AND IMP_TABLE=v_table_name;
1029 BEGIN
1030 OPEN c_get_nErrors;
1031 FETCH c_get_nErrors INTO nErrors;
1032 CLOSE c_get_nErrors;
1033 UPDATE CZ_XFR_RUN_INFOS
1034 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1035 COMPLETED=v_completed
1036 WHERE RUN_ID=inRUN_ID;
1037 COMMIT;
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_MASTER',11276,inRun_ID);
1041 END;
1042 EXCEPTION
1043 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1044 RAISE;
1045 WHEN OTHERS THEN
1046 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_MASTER',11276,inRun_ID);
1047 END;
1048
1049 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1050
1051 PROCEDURE RPT_ITEM_PROPERTY_VALUE ( inRUN_ID IN PLS_INTEGER ) AS
1052 x_error BOOLEAN:=FALSE;
1053 v_table_name VARCHAR2(30) := 'CZ_ITEM_PROPERTY_VALUES';
1054 v_ok VARCHAR2(4) := 'OK';
1055 v_completed VARCHAR2(1) := '1';
1056 BEGIN
1057 BEGIN
1058 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
1059
1060 EXCEPTION
1061 WHEN NO_DATA_FOUND THEN NULL;
1062 END;
1063
1064 DECLARE
1065 CURSOR c_xfr_run_result IS
1066 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1067 FROM CZ_IMP_item_property_value
1068 WHERE RUN_ID = inRUN_ID
1069 GROUP BY DISPOSITION,REC_STATUS;
1070
1071 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
1072 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
1073 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
1074
1075 BEGIN
1076
1077 OPEN c_xfr_run_result;
1078 LOOP
1079 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1080 EXIT WHEN c_xfr_run_result%NOTFOUND;
1081
1082 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1083 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
1084
1085 END LOOP;
1086 CLOSE c_xfr_run_result;
1087 COMMIT;
1088
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_PROPERTY_VALUE',11276,inRun_ID);
1092 END;
1093
1094 DECLARE
1095 nErrors PLS_INTEGER;
1096 CURSOR c_get_nErrors IS
1097 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1098 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
1099 AND IMP_TABLE=v_table_name;
1100 BEGIN
1101 OPEN c_get_nErrors;
1102 FETCH c_get_nErrors INTO nErrors;
1103 CLOSE c_get_nErrors;
1104 UPDATE CZ_XFR_RUN_INFOS
1105 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1106 COMPLETED=v_completed
1107 WHERE RUN_ID=inRUN_ID;
1108 COMMIT;
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_PROPERTY_VALUE',11276,inRun_ID);
1112 END;
1113 EXCEPTION
1114 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1115 RAISE;
1116 WHEN OTHERS THEN
1117 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_PROPERTY_VALUE',11276,inRun_ID);
1118 END;
1119 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1120
1121 PROCEDURE RPT_ITEM_TYPE ( inRUN_ID IN PLS_INTEGER ) AS
1122 x_error BOOLEAN:=FALSE;
1123 v_table_name VARCHAR2(30) := 'CZ_ITEM_TYPES';
1124 v_ok VARCHAR2(4) := 'OK';
1125 v_completed VARCHAR2(1) := '1';
1126 BEGIN
1127 BEGIN
1128 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
1129
1130 EXCEPTION
1131 WHEN NO_DATA_FOUND THEN NULL;
1132 END;
1133
1134 DECLARE
1135 CURSOR c_xfr_run_result IS
1136 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1137 FROM CZ_IMP_item_type
1138 WHERE RUN_ID = inRUN_ID
1139 GROUP BY DISPOSITION,REC_STATUS;
1140
1141 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
1142 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
1143 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
1144
1145 BEGIN
1146
1147 OPEN c_xfr_run_result;
1148 LOOP
1149 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1150 EXIT WHEN c_xfr_run_result%NOTFOUND;
1151
1152 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1153 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
1154
1155 END LOOP;
1156 CLOSE c_xfr_run_result;
1157 COMMIT;
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_TYPE',11276,inRun_ID);
1162 END;
1163
1164 DECLARE
1165 nErrors PLS_INTEGER;
1166 CURSOR c_get_nErrors IS
1167 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1168 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
1169 AND IMP_TABLE=v_table_name;
1170 BEGIN
1171 OPEN c_get_nErrors;
1172 FETCH c_get_nErrors INTO nErrors;
1173 CLOSE c_get_nErrors;
1174 UPDATE CZ_XFR_RUN_INFOS
1175 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1176 COMPLETED=v_completed
1177 WHERE RUN_ID=inRUN_ID;
1178 COMMIT;
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_TYPE',11276,inRun_ID);
1182 END;
1183 EXCEPTION
1184 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1185 RAISE;
1186 WHEN OTHERS THEN
1187 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_TYPE',11276,inRun_ID);
1188 END;
1189
1190 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1191
1192 PROCEDURE RPT_ITEM_TYPE_PROPERTY ( inRUN_ID IN PLS_INTEGER ) AS
1193 x_error BOOLEAN:=FALSE;
1194 v_table_name VARCHAR2(30) := 'CZ_ITEM_TYPE_PROPERTIES';
1195 v_ok VARCHAR2(4) := 'OK';
1196 v_completed VARCHAR2(1) := '1';
1197 BEGIN
1198 BEGIN
1199 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
1200
1201 EXCEPTION
1202 WHEN NO_DATA_FOUND THEN NULL;
1203 END;
1204
1205 DECLARE
1206 CURSOR c_xfr_run_result IS
1207 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1208 FROM CZ_IMP_item_type_property
1209 WHERE RUN_ID = inRUN_ID
1210 GROUP BY DISPOSITION,REC_STATUS;
1211
1212 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
1213 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
1214 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
1215
1216 BEGIN
1217
1218 OPEN c_xfr_run_result;
1219 LOOP
1220 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1221 EXIT WHEN c_xfr_run_result%NOTFOUND;
1222
1223 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1224 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
1225
1226 END LOOP;
1227 CLOSE c_xfr_run_result;
1228 COMMIT;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_TYPE_PROPERTY',11276,inRun_ID);
1233 END;
1234
1235 DECLARE
1236 nErrors PLS_INTEGER;
1237 CURSOR c_get_nErrors IS
1238 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1239 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
1240 AND IMP_TABLE=v_table_name;
1241 BEGIN
1242 OPEN c_get_nErrors;
1243 FETCH c_get_nErrors INTO nErrors;
1244 CLOSE c_get_nErrors;
1245 UPDATE CZ_XFR_RUN_INFOS
1246 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1247 COMPLETED=v_completed
1248 WHERE RUN_ID=inRUN_ID;
1249 COMMIT;
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_TYPE_PROPERTY',11276,inRun_ID);
1253 END;
1254 EXCEPTION
1255 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1256 RAISE;
1257 WHEN OTHERS THEN
1258 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_ITEM_TYPE_PROPERTY',11276,inRun_ID);
1259 END;
1260
1261 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1262 PROCEDURE RPT_PROPERTY ( inRUN_ID IN PLS_INTEGER ) AS
1263 x_error BOOLEAN:=FALSE;
1264 v_table_name VARCHAR2(30) := 'CZ_PROPERTIES';
1265 v_ok VARCHAR2(4) := 'OK';
1266 v_completed VARCHAR2(1) := '1';
1267 BEGIN
1268 BEGIN
1269 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
1270
1271 EXCEPTION
1272 WHEN NO_DATA_FOUND THEN NULL;
1273 END;
1274
1275 DECLARE
1276 CURSOR c_xfr_run_result IS
1277 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1278 FROM CZ_IMP_property
1279 WHERE RUN_ID = inRUN_ID
1280 GROUP BY DISPOSITION,REC_STATUS;
1281
1282 ins_disposition CZ_XFR_RUN_RESULTS.disposition%TYPE;
1283 ins_rec_status CZ_XFR_RUN_RESULTS.rec_status%TYPE ;
1284 ins_rec_count CZ_XFR_RUN_RESULTS.records%TYPE ;
1285
1286 BEGIN
1287
1288 OPEN c_xfr_run_result;
1289 LOOP
1290 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1291 EXIT WHEN c_xfr_run_result%NOTFOUND;
1292
1293 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1294 VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
1295
1296 END LOOP;
1297 CLOSE c_xfr_run_result;
1298 COMMIT;
1299
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_PROPERTY',11276,inRun_ID);
1303 END;
1304
1305 DECLARE
1306 nErrors PLS_INTEGER;
1307 CURSOR c_get_nErrors IS
1308 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1309 WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
1310 AND IMP_TABLE=v_table_name;
1311 BEGIN
1312 OPEN c_get_nErrors;
1313 FETCH c_get_nErrors INTO nErrors;
1314 CLOSE c_get_nErrors;
1315 UPDATE CZ_XFR_RUN_INFOS
1316 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1317 COMPLETED=v_completed
1318 WHERE RUN_ID=inRUN_ID;
1319 COMMIT;
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_PROPERTY',11276,inRun_ID);
1323 END;
1324 EXCEPTION
1325 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1326 RAISE;
1327 WHEN OTHERS THEN
1328 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_MAIN.RPT_PROPERTY',11276,inRun_ID);
1329 END;
1330
1331 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1332
1333 END CZ_IMP_IM_MAIN;
[Home] [Help]