1 PACKAGE BODY CZ_IMP_AC_MAIN AS
2 /* $Header: cziacmnb.pls 115.19 2002/12/03 14:44:34 askhacha ship $ */
3
4 PROCEDURE CND_CONTACT ( inRUN_ID IN PLS_INTEGER,
5 COMMIT_SIZE IN PLS_INTEGER,
6 MAX_ERR IN PLS_INTEGER,
7 FAILED OUT NOCOPY PLS_INTEGER
8 ) IS
9 BEGIN
10 DECLARE
11 CURSOR c_imp_contact IS
12 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_CONTACT WHERE REC_STATUS IS NULL
13 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_contact c_imp_contact%ROWTYPE;
24 x_imp_contact_f BOOLEAN:=FALSE;
25
26 BEGIN
27
28 OPEN c_imp_contact;
29 LOOP
30 FETCH c_imp_contact INTO p_imp_contact;
31 x_imp_contact_f:=c_imp_contact%FOUND;
32
33 EXIT WHEN(NOT x_imp_contact_f Or nFailed >= Max_Err);
34 IF (p_imp_contact.DELETED_FLAG IS NULL) THEN
35 BEGIN
36 UPDATE CZ_IMP_CONTACT
37 SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG)
38 WHERE ROWID = p_imp_contact.ROWID;
39 nCOmmitCount:=nCommitCount+1;
40 /* COMMIT if the buffer size is reached */
41 IF (nCommitCount>= COMMIT_SIZE) THEN
42 COMMIT;
43 nCommitCount:=0;
44 END IF;
45 EXCEPTION
46 WHEN OTHERS THEN
47 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_CONTACT',11276);
48 nFailed:=nFailed+1;
49 END;
50 END IF;
51 END LOOP;
52 CLOSE c_imp_contact;
53 FAILED:=nFailed;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_CONTACT',11276);
58 END;
59
60 END CND_CONTACT;
61
62
63 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
64
65 PROCEDURE CND_CUSTOMER ( inRUN_ID IN PLS_INTEGER,
66 COMMIT_SIZE IN PLS_INTEGER,
67 MAX_ERR IN PLS_INTEGER,
68 FAILED OUT NOCOPY PLS_INTEGER
69 ) IS
70 BEGIN
71 DECLARE
72 CURSOR c_imp_customer IS
73 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_CUSTOMER WHERE REC_STATUS IS NULL
74 AND RUN_ID = inRUN_ID;
75 /* Internal vars */
76 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
77 nErrorCount PLS_INTEGER:=0; /*Error index */
78 nFailed PLS_INTEGER:=0; /*Failed records */
79 nDups PLS_INTEGER:=0; /*Dupl records */
80 x_error BOOLEAN:=FALSE;
81
82
83 /*Cursor Var for Import */
84 p_imp_customer c_imp_customer%ROWTYPE;
85 x_imp_customer_f BOOLEAN:=FALSE;
86
87 BEGIN
88
89 OPEN c_imp_customer;
90 LOOP
91 FETCH c_imp_customer INTO p_imp_customer;
92 x_imp_customer_f:=c_imp_customer%FOUND;
93
94 EXIT WHEN(NOT x_imp_customer_f Or nFailed >= Max_Err);
95 IF (p_imp_customer.DELETED_FLAG IS NULL) THEN
96 BEGIN
97 UPDATE CZ_IMP_CUSTOMER
98 SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG)
99 WHERE ROWID = p_imp_customer.ROWID;
100 nCOmmitCount:=nCommitCount+1;
101 /* COMMIT if the buffer size is reached */
102 IF (nCommitCount>= COMMIT_SIZE) THEN
103 COMMIT;
104 nCommitCount:=0;
105 END IF;
106 EXCEPTION
107 WHEN OTHERS THEN
108 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_CUSTOMER',11276);
109 nFailed:=nFailed+1;
110 END;
111 END IF;
112 END LOOP;
113 CLOSE c_imp_customer;
114 FAILED:=nFailed;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_CUSTOMER',11276);
119 END;
120
121 END CND_CUSTOMER;
122
123 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
124
125 PROCEDURE CND_ADDRESS ( inRUN_ID IN PLS_INTEGER,
126 COMMIT_SIZE IN PLS_INTEGER,
127 MAX_ERR IN PLS_INTEGER,
128 FAILED OUT NOCOPY PLS_INTEGER
129 ) IS
130 BEGIN
131 DECLARE
132 CURSOR c_imp_address IS
133 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_ADDRESS WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
134 /* Internal vars */
135 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
136 nErrorCount PLS_INTEGER:=0; /*Error index */
137 nFailed PLS_INTEGER:=0; /*Failed records */
138 nDups PLS_INTEGER:=0; /*Dupl records */
139 x_error BOOLEAN:=FALSE;
140
141
142 /*Cursor Var for Import */
143 p_imp_address c_imp_address%ROWTYPE;
144 x_imp_address_f BOOLEAN:=FALSE;
145
146 BEGIN
147
148 OPEN c_imp_address;
149 LOOP
150 FETCH c_imp_address INTO p_imp_address;
151 x_imp_address_f:=c_imp_address%FOUND;
152
153 EXIT WHEN(NOT x_imp_address_f Or nFailed >= Max_Err);
154 IF (p_imp_address.DELETED_FLAG IS NULL) THEN
155 BEGIN
156 UPDATE CZ_IMP_ADDRESS SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_address.ROWID;
157 nCOmmitCount:=nCommitCount+1;
158 /* COMMIT if the buffer size is reached */
159 IF (nCommitCount>= COMMIT_SIZE) THEN
160 COMMIT;
161 nCommitCount:=0;
162 END IF;
163 EXCEPTION
164 WHEN OTHERS THEN
165 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_ADDRESS',11276);
166 nFailed:=nFailed+1;
167 END;
168 END IF;
169 END LOOP;
170 CLOSE c_imp_address;
171 FAILED:=nFailed;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_ADDRESS',11276);
176 END;
177
178 END CND_ADDRESS;
179
180 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
181 PROCEDURE CND_ADDRESS_USES(inRUN_ID IN PLS_INTEGER,
182 COMMIT_SIZE IN PLS_INTEGER,
183 MAX_ERR IN PLS_INTEGER,
184 FAILED OUT NOCOPY PLS_INTEGER
185 ) IS
186 BEGIN
187 DECLARE
188 CURSOR c_imp_address_uses IS
189 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_ADDRESS_USE
190 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
191 /* Internal vars */
192 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
193 nErrorCount PLS_INTEGER:=0; /*Error index */
194 nFailed PLS_INTEGER:=0; /*Failed records */
195 nDups PLS_INTEGER:=0; /*Duplicate records */
196 x_error BOOLEAN:=FALSE;
197
198 /*Cursor Var for Import */
199 p_imp_address_uses c_imp_address_uses%ROWTYPE;
200 x_imp_address_uses_f BOOLEAN:=FALSE;
201
202 BEGIN
203 OPEN c_imp_address_uses;
204 LOOP
205 FETCH c_imp_address_uses INTO p_imp_address_uses;
206 x_imp_address_uses_f:=c_imp_address_uses%FOUND;
207
208 EXIT WHEN(NOT x_imp_address_uses_f Or nFailed >= Max_Err);
209
210 IF(p_imp_address_uses.DELETED_FLAG IS NULL) THEN
211 BEGIN
212 UPDATE CZ_IMP_ADDRESS_USE
213 SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG)
214 WHERE ROWID = p_imp_address_uses.ROWID;
215
216 nCOmmitCount:=nCommitCount+1;
217 /* COMMIT if the buffer size is reached */
218 IF(nCommitCount>= COMMIT_SIZE) THEN
219 COMMIT;
220 nCommitCount:=0;
221 END IF;
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_ADDRESS_USES',11276);
226 nFailed:=nFailed+1;
227 END;
228 END IF;
229 END LOOP;
230 CLOSE c_imp_address_uses;
231 FAILED:=nFailed;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_ADDRESS_USES',11276);
236 END;
237 END CND_ADDRESS_USES;
238
239 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
240
241 PROCEDURE CND_CUSTOMER_END_USER ( inRUN_ID IN PLS_INTEGER,
242 COMMIT_SIZE IN PLS_INTEGER,
243 MAX_ERR IN PLS_INTEGER,
244 FAILED OUT NOCOPY PLS_INTEGER
245 ) IS
246 BEGIN
247 DECLARE
248 CURSOR c_imp_customerenduser IS
249 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_CUSTOMER_END_USER WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
250 /* Internal vars */
251 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
252 nErrorCount PLS_INTEGER:=0; /*Error index */
253 nFailed PLS_INTEGER:=0; /*Failed records */
254 nDups PLS_INTEGER:=0; /*Dupl records */
255 x_error BOOLEAN:=FALSE;
256
257
258 /*Cursor Var for Import */
259 p_imp_customerenduser c_imp_customerenduser%ROWTYPE;
260 x_imp_customerenduser_f BOOLEAN:=FALSE;
261
262 BEGIN
263
264 OPEN c_imp_customerenduser;
265 LOOP
266 FETCH c_imp_customerenduser INTO p_imp_customerenduser;
267 x_imp_customerenduser_f:=c_imp_customerenduser%FOUND;
268
269 EXIT WHEN(NOT x_imp_customerenduser_f Or nFailed >= Max_Err);
270 IF (p_imp_customerenduser.DELETED_FLAG IS NULL) THEN
271 BEGIN
272 UPDATE CZ_IMP_CUSTOMER_END_USER SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_customerenduser.ROWID;
273 nCOmmitCount:=nCommitCount+1;
274 /* COMMIT if the buffer size is reached */
275 IF (nCommitCount>= COMMIT_SIZE) THEN
276 COMMIT;
277 nCommitCount:=0;
278 END IF;
279 EXCEPTION
280 WHEN OTHERS THEN
281 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_CUSTOMER_END_USER',11276);
282 nFailed:=nFailed+1;
283 END;
284 END IF;
285 END LOOP;
286 CLOSE c_imp_customerenduser;
287 FAILED:=nFailed;
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_CUSTOMER',11276);
292 END;
293
294 END CND_CUSTOMER_END_USER;
295
296 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
297
298 PROCEDURE CND_END_USER( inRUN_ID IN PLS_INTEGER,
299 COMMIT_SIZE IN PLS_INTEGER,
300 MAX_ERR IN PLS_INTEGER,
301 FAILED OUT NOCOPY PLS_INTEGER
302 ) IS
303 BEGIN
304 DECLARE
305 CURSOR c_imp_enduser IS
306 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_END_USER WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
307 /* Internal vars */
308 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
309 nErrorCount PLS_INTEGER:=0; /*Error index */
310 nFailed PLS_INTEGER:=0; /*Failed records */
311 nDups PLS_INTEGER:=0; /*Dupl records */
312 x_error BOOLEAN:=FALSE;
313
314
315 /*Cursor Var for Import */
316 p_imp_enduser c_imp_enduser%ROWTYPE;
317 x_imp_enduser_f BOOLEAN:=FALSE;
318
319 BEGIN
320
321 OPEN c_imp_enduser;
322 LOOP
323 FETCH c_imp_enduser INTO p_imp_enduser;
324 x_imp_enduser_f:=c_imp_enduser%FOUND;
325
326 EXIT WHEN(NOT x_imp_enduser_f Or nFailed >= Max_Err);
327 IF (p_imp_enduser.DELETED_FLAG IS NULL) THEN
328 BEGIN
329 UPDATE CZ_IMP_END_USER SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_enduser.ROWID;
330 nCOmmitCount:=nCommitCount+1;
331 /* COMMIT if the buffer size is reached */
332 IF (nCommitCount>= COMMIT_SIZE) THEN
333 COMMIT;
334 nCommitCount:=0;
335 END IF;
336 EXCEPTION
337 WHEN OTHERS THEN
338 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_END_USER',11276);
339 nFailed:=nFailed+1;
340 END;
341 END IF;
342 END LOOP;
343 CLOSE c_imp_enduser;
344 FAILED:=nFailed;
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_END_USER',11276);
349
350 END;
351
352 END CND_END_USER;
353
354 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
355
356 PROCEDURE CND_END_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
357 COMMIT_SIZE IN PLS_INTEGER,
358 MAX_ERR IN PLS_INTEGER,
359 FAILED OUT NOCOPY PLS_INTEGER
360 ) IS
361 BEGIN
362 DECLARE
363 CURSOR c_imp_endusergroup IS
364 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_END_USER_GROUP WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
365 /* Internal vars */
366 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
367 nErrorCount PLS_INTEGER:=0; /*Error index */
368 nFailed PLS_INTEGER:=0; /*Failed records */
369 nDups PLS_INTEGER:=0; /*Dupl records */
370 x_error BOOLEAN:=FALSE;
371
372
373 /*Cursor Var for Import */
374 p_imp_endusergroup c_imp_endusergroup%ROWTYPE;
375 x_imp_endusergroup_f BOOLEAN:=FALSE;
376
377 BEGIN
378
379 OPEN c_imp_endusergroup;
380 LOOP
381 FETCH c_imp_endusergroup INTO p_imp_endusergroup;
382 x_imp_endusergroup_f:=c_imp_endusergroup%FOUND;
383
384 EXIT WHEN(NOT x_imp_endusergroup_f Or nFailed >= Max_Err);
385 IF (p_imp_endusergroup.DELETED_FLAG IS NULL) THEN
386 BEGIN
387 UPDATE CZ_IMP_END_USER_GROUP SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_endusergroup.ROWID;
388 nCOmmitCount:=nCommitCount+1;
389 /* COMMIT if the buffer size is reached */
390 IF (nCommitCount>= COMMIT_SIZE) THEN
391 COMMIT;
392 nCommitCount:=0;
393 END IF;
394 EXCEPTION
395 WHEN OTHERS THEN
396 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_END_USER_GROUP',11276);
397 nFailed:=nFailed+1;
398 END;
399 END IF;
400 END LOOP;
401 CLOSE c_imp_endusergroup;
402 FAILED:=nFailed;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_END_USER_GROUP',11276);
407 END;
408
409 END CND_END_USER_GROUP;
410
411 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
412
413 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
414
415 PROCEDURE CND_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
416 COMMIT_SIZE IN PLS_INTEGER,
417 MAX_ERR IN PLS_INTEGER,
418 FAILED OUT NOCOPY PLS_INTEGER
419 ) IS
420 BEGIN
421 DECLARE
422 CURSOR c_imp_usergroup IS
423 SELECT DELETED_FLAG, ROWID FROM CZ_IMP_USER_GROUP WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
424 /* Internal vars */
425 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
426 nErrorCount PLS_INTEGER:=0; /*Error index */
427 nFailed PLS_INTEGER:=0; /*Failed records */
428 nDups PLS_INTEGER:=0; /*Dupl records */
429 x_error BOOLEAN:=FALSE;
430
431
432 /*Cursor Var for Import */
433 p_imp_usergroup c_imp_usergroup%ROWTYPE;
434 x_imp_usergroup_f BOOLEAN:=FALSE;
435
436 BEGIN
437
438 OPEN c_imp_usergroup;
439 LOOP
440 FETCH c_imp_usergroup INTO p_imp_usergroup;
441 x_imp_usergroup_f:=c_imp_usergroup%FOUND;
442
443 EXIT WHEN(NOT x_imp_usergroup_f Or nFailed >= Max_Err);
444 IF (p_imp_usergroup.DELETED_FLAG IS NULL) THEN
445 BEGIN
446 UPDATE CZ_IMP_USER_GROUP SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_usergroup.ROWID;
447 nCOmmitCount:=nCommitCount+1;
448 /* COMMIT if the buffer size is reached */
449 IF (nCommitCount>= COMMIT_SIZE) THEN
450 COMMIT;
451 nCommitCount:=0;
452 END IF;
453 EXCEPTION
454 WHEN OTHERS THEN
455 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_USER_GROUP',11276);
456 nFailed:=nFailed+1;
457 END;
458 END IF;
459 END LOOP;
460 CLOSE c_imp_usergroup;
461 FAILED:=nFailed;
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.CND_USER_GROUP',11276);
466 END;
467
468 END CND_USER_GROUP;
469
470 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
471
472
473 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
474
475 PROCEDURE MAIN_CONTACT ( inRUN_ID IN PLS_INTEGER,
476 COMMIT_SIZE IN PLS_INTEGER,
477 MAX_ERR IN PLS_INTEGER,
478 INSERTS IN OUT NOCOPY PLS_INTEGER,
479 UPDATES IN OUT NOCOPY PLS_INTEGER,
480 FAILED IN OUT NOCOPY PLS_INTEGER,
481 DUPS IN OUT NOCOPY PLS_INTEGER,
482 inXFR_GROUP IN VARCHAR2
483 ) IS
484 BEGIN
485 DECLARE
486 /* Internal vars */
487 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
488 nErrorCount PLS_INTEGER:=0; /*Error index */
489 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
490 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
491 nFailed PLS_INTEGER:=0; /*Failed records */
492 nDups PLS_INTEGER:=0; /*Dupl records */
493 x_error BOOLEAN:=FALSE;
494 dummy CHAR(1);
495
496 BEGIN
497
498 BEGIN
499 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
500
501 UPDATE CZ_XFR_RUN_INFOS SET
502 STARTED=SYSDATE,
503 LAST_ACTIVITY=SYSDATE
504 WHERE RUN_ID=inRUN_ID;
505
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
509 VALUES(inRUN_ID,SYSDATE,SYSDATE);
510
511 WHEN OTHERS THEN
512 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_CONTACT',11276);
513 END;
514
515 CZ_IMP_AC_MAIN.CND_CONTACT (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
516 IF (nFailed=MAX_ERR) THEN
517 INSERTS:=0;
518 UPDATES:=0;
519 FAILED:=MAX_ERR;
520 DUPS:=0;
521 RETURN;
522 END IF;
523
524 CZ_IMP_AC_KRS.KRS_CONTACT (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
525
526 /* Make sure that the error count has not been reached */
527 IF(nFailed < MAX_ERR) THEN
528 CZ_IMP_AC_XFR.XFR_CONTACT (inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
529 /* Report Insert Errors */
530 IF (nXfrInsertCount<> INSERTS) THEN
531 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_AC.MAIN_CONTACT',11276);
532 END IF;
533
534 /* Report Update Errors */
535 IF (nXfrUpdateCount<> UPDATES) THEN
536 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_AC.MAIN_CONTACT',11276);
537 END IF;
538
539 /* Return the transferred number of rows and not the number of rows with keys resolved*/
540 INSERTS:=nXfrInsertCount;
541 UPDATES:=nXfrUpdateCount;
542
543 FAILED:=FAILED+nFailed;
544 ELSE
545 FAILED:=nFailed;
546 END IF;
547
548 CZ_IMP_AC_MAIN.RPT_CONTACT(inRUN_ID);
549 END ;
550 END MAIN_CONTACT ;
551
552 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
553
554 PROCEDURE MAIN_CUSTOMER ( inRUN_ID IN PLS_INTEGER,
555 COMMIT_SIZE IN PLS_INTEGER,
556 MAX_ERR IN PLS_INTEGER,
557 INSERTS IN OUT NOCOPY PLS_INTEGER,
558 UPDATES IN OUT NOCOPY PLS_INTEGER,
559 FAILED IN OUT NOCOPY PLS_INTEGER,
560 DUPS IN OUT NOCOPY PLS_INTEGER,
561 inXFR_GROUP IN VARCHAR2
562 ) IS
563 BEGIN
564 DECLARE
565 /* Internal vars */
566 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
567 nErrorCount PLS_INTEGER:=0; /*Error index */
568 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
569 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
570 nFailed PLS_INTEGER:=0; /*Failed records */
571 nDups PLS_INTEGER:=0; /*Dupl records */
572 x_error BOOLEAN:=FALSE;
573 dummy CHAR(1);
574
575 BEGIN
576 BEGIN
577 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
578
579 UPDATE CZ_XFR_RUN_INFOS SET
580 STARTED=SYSDATE,
581 LAST_ACTIVITY=SYSDATE
582 WHERE RUN_ID=inRUN_ID;
583
584 EXCEPTION
585 WHEN NO_DATA_FOUND THEN
586 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
587 VALUES(inRUN_ID,SYSDATE,SYSDATE);
588
589 WHEN OTHERS THEN
590 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_CUSTOMER',11276);
591 END;
592
593 CZ_IMP_AC_MAIN.CND_CUSTOMER (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
594 IF (nFailed=MAX_ERR) THEN
595 INSERTS:=0;
596 UPDATES:=0;
597 FAILED:=MAX_ERR;
598 DUPS:=0;
599 return;
600 END IF;
601 CZ_IMP_AC_KRS.KRS_CUSTOMER (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
602 /* Make sure that the error count has not been reached */
603 IF(nFailed < MAX_ERR) THEN
604 CZ_IMP_AC_XFR.XFR_CUSTOMER (inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
605 /* Report Insert Errors */
606 IF (nXfrInsertCount<> INSERTS) THEN
607 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_AC.MAIN_CUSTOMER ',11276);
608 END IF;
609 /* Report Update Errors */
610 IF (nXfrUpdateCount<> UPDATES) THEN
611 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_AC.MAIN_CUSTOMER ',11276);
612 END IF;
613 /* Return the transferred number of rows and not the number of rows with keys resolved*/
614 INSERTS:=nXfrInsertCount;
615 UPDATES:=nXfrUpdateCount;
616 FAILED:=FAILED+nFailed;
617 ELSE
618 FAILED:=nFailed;
619 END IF;
620
621 CZ_IMP_AC_MAIN.RPT_CUSTOMER(inRUN_ID);
622 END;
623 END MAIN_CUSTOMER ;
624
625 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
626
627 PROCEDURE MAIN_ADDRESS ( inRUN_ID IN PLS_INTEGER,
628 COMMIT_SIZE IN PLS_INTEGER,
629 MAX_ERR IN PLS_INTEGER,
630 INSERTS IN OUT NOCOPY PLS_INTEGER,
631 UPDATES IN OUT NOCOPY PLS_INTEGER,
632 FAILED IN OUT NOCOPY PLS_INTEGER,
633 DUPS IN OUT NOCOPY PLS_INTEGER,
634 inXFR_GROUP IN VARCHAR2
635 ) IS
636 BEGIN
637 DECLARE
638 /* Internal vars */
639 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
640 nErrorCount PLS_INTEGER:=0; /*Error index */
641 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
642 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
643 nFailed PLS_INTEGER:=0; /*Failed records */
644 nDups PLS_INTEGER:=0; /*Dupl records */
645 x_error BOOLEAN:=FALSE;
646 dummy CHAR(1);
647
648 BEGIN
649 BEGIN
650 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
651
652 UPDATE CZ_XFR_RUN_INFOS SET
653 STARTED=SYSDATE,
654 LAST_ACTIVITY=SYSDATE
655 WHERE RUN_ID=inRUN_ID;
656
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
660 VALUES(inRUN_ID,SYSDATE,SYSDATE);
661
662 WHEN OTHERS THEN
663 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_ADDRESS',11276);
664 END;
665
666 CZ_IMP_AC_MAIN.CND_ADDRESS (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
667 IF (nFailed=MAX_ERR) THEN
668 INSERTS:=0;
669 UPDATES:=0;
670 FAILED:=MAX_ERR;
671 DUPS:=0;
672 return;
673 END IF;
674 CZ_IMP_AC_KRS.KRS_ADDRESS (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
675 /* Make sure that the error count has not been reached */
676 IF(nFailed < MAX_ERR) THEN
677 CZ_IMP_AC_XFR.XFR_ADDRESS (inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
678 /* Report Insert Errors */
679 IF (nXfrInsertCount<> INSERTS) THEN
680 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_AC.MAIN_ADDRESS ',11276);
681 END IF;
682 /* Report Update Errors */
683 IF (nXfrUpdateCount<> UPDATES) THEN
684 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_AC.MAIN_ADDRESS ',11276);
685 END IF;
686 /* Return the transferred number of rows and not the number of rows with keys resolved*/
687 INSERTS:=nXfrInsertCount;
688 UPDATES:=nXfrUpdateCount;
689 FAILED:=FAILED+nFailed;
690 ELSE
691 FAILED:=nFailed;
692 END IF;
693
694 CZ_IMP_AC_MAIN.RPT_ADDRESS(inRUN_ID);
695 END;
696 END MAIN_ADDRESS ;
697
698 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
699
700 PROCEDURE MAIN_ADDRESS_USES(inRUN_ID IN PLS_INTEGER,
701 COMMIT_SIZE IN PLS_INTEGER,
702 MAX_ERR IN PLS_INTEGER,
703 INSERTS IN OUT NOCOPY PLS_INTEGER,
704 UPDATES IN OUT NOCOPY PLS_INTEGER,
705 FAILED IN OUT NOCOPY PLS_INTEGER,
706 DUPS IN OUT NOCOPY PLS_INTEGER,
707 inXFR_GROUP IN VARCHAR2
708 ) IS
709 BEGIN
710 DECLARE
711 /* Internal vars */
712 nCommitCount PLS_INTEGER:=0; /* COMMIT buffer index */
713 nErrorCount PLS_INTEGER:=0; /* Error index */
714 nXfrInsertCount PLS_INTEGER:=0; /* Inserts */
715 nXfrUpdateCount PLS_INTEGER:=0; /* Updates */
716 nFailed PLS_INTEGER:=0; /* Failed records */
717 nDups PLS_INTEGER:=0; /* Dupl records */
718 x_error BOOLEAN:=FALSE;
719 dummy CHAR(1);
720
721 BEGIN
722 BEGIN
723 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
724
725 UPDATE CZ_XFR_RUN_INFOS SET
726 STARTED=SYSDATE,
727 LAST_ACTIVITY=SYSDATE
728 WHERE RUN_ID=inRUN_ID;
729
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
733 VALUES(inRUN_ID,SYSDATE,SYSDATE);
734
735 WHEN OTHERS THEN
736 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_ADDRESS_USES',11276);
737 END;
738
739 CZ_IMP_AC_MAIN.CND_ADDRESS_USES(inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
740 IF(nFailed=MAX_ERR) THEN
741 INSERTS:=0;
742 UPDATES:=0;
743 FAILED:=MAX_ERR;
744 DUPS:=0;
745 return;
746 END IF;
747 CZ_IMP_AC_KRS.KRS_ADDRESS_USES(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
748 /* Make sure that the error count has not been reached */
749 IF(nFailed < MAX_ERR) THEN
750 CZ_IMP_AC_XFR.XFR_ADDRESS_USES(inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
751 /* Report Insert Errors */
752 IF(nXfrInsertCount<> INSERTS) THEN
753 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_AC.MAIN_ADDRESS_USES ',11276);
754 END IF;
755 /* Report Update Errors */
756 IF(nXfrUpdateCount<> UPDATES) THEN
757 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_AC.MAIN_ADDRESS_USES ',11276);
758 END IF;
759 /* Return the transferred number of rows and not the number of rows with keys resolved */
760 INSERTS:=nXfrInsertCount;
761 UPDATES:=nXfrUpdateCount;
762 FAILED:=FAILED+nFailed;
763 ELSE
764 FAILED:=nFailed;
765 END IF;
766
767 CZ_IMP_AC_MAIN.RPT_ADDRESS_USES(inRUN_ID);
768 END;
769 END MAIN_ADDRESS_USES;
770
771 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
772
773 PROCEDURE MAIN_CUSTOMER_END_USER ( inRUN_ID IN PLS_INTEGER,
774 COMMIT_SIZE IN PLS_INTEGER,
775 MAX_ERR IN PLS_INTEGER,
776 INSERTS IN OUT NOCOPY PLS_INTEGER,
777 UPDATES IN OUT NOCOPY PLS_INTEGER,
778 FAILED IN OUT NOCOPY PLS_INTEGER,
779 DUPS IN OUT NOCOPY PLS_INTEGER,
780 inXFR_GROUP IN VARCHAR2
781 ) IS
782 BEGIN
783 DECLARE
784 /* Internal vars */
785 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
786 nErrorCount PLS_INTEGER:=0; /*Error index */
787 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
788 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
789 nFailed PLS_INTEGER:=0; /*Failed records */
790 nDups PLS_INTEGER:=0; /*Dupl records */
791 x_error BOOLEAN:=FALSE;
792 dummy CHAR(1);
793
794 BEGIN
795 BEGIN
796 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
797
798 UPDATE CZ_XFR_RUN_INFOS SET
799 STARTED=SYSDATE,
800 LAST_ACTIVITY=SYSDATE
801 WHERE RUN_ID=inRUN_ID;
802
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
806 VALUES(inRUN_ID,SYSDATE,SYSDATE);
807
808 WHEN OTHERS THEN
809 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_CUSTOMER_END_USER',11276);
810 END;
811
812 CZ_IMP_AC_MAIN.CND_CUSTOMER_END_USER (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
813 IF (nFailed=MAX_ERR) THEN
814 INSERTS:=0;
815 UPDATES:=0;
816 FAILED:=MAX_ERR;
817 DUPS:=0;
818 return;
819 END IF;
820 CZ_IMP_AC_KRS.KRS_CUSTOMER_END_USER (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
821 /* Make sure that the error count has not been reached */
822 IF(nFailed < MAX_ERR) THEN
823 CZ_IMP_AC_XFR.XFR_CUSTOMER_END_USER (inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
824 /* Report Insert Errors */
825 IF (nXfrInsertCount<> INSERTS) THEN
826 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IMP_AC.MAIN_CUSTOMER_END_USER ',11276);
827 END IF;
828 /* Report Update Errors */
829 IF (nXfrUpdateCount<> UPDATES) THEN
830 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IMP_AC.MAIN_CUSTOMER_END_USER ',11276);
831 END IF;
832 /* Return the transferred number of rows and not the number of rows with keys resolved*/
833 INSERTS:=nXfrInsertCount;
834 UPDATES:=nXfrUpdateCount;
835 FAILED:=FAILED+nFailed;
836 ELSE
837 FAILED:=nFailed;
838 END IF;
839
840 CZ_IMP_AC_MAIN.RPT_CUSTOMER_END_USER(inRUN_ID);
841 END;
842 END MAIN_CUSTOMER_END_USER ;
843
844
845 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
846
847 PROCEDURE MAIN_END_USER ( inRUN_ID IN PLS_INTEGER,
848 COMMIT_SIZE IN PLS_INTEGER,
849 MAX_ERR IN PLS_INTEGER,
850 INSERTS IN OUT NOCOPY PLS_INTEGER,
851 UPDATES IN OUT NOCOPY PLS_INTEGER,
852 FAILED IN OUT NOCOPY PLS_INTEGER,
853 DUPS IN OUT NOCOPY PLS_INTEGER,
854 inXFR_GROUP IN VARCHAR2
855 ) IS
856 BEGIN
857 DECLARE
858 /* Internal vars */
859 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
860 nErrorCount PLS_INTEGER:=0; /*Error index */
861 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
862 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
863 nFailed PLS_INTEGER:=0; /*Failed records */
864 nDups PLS_INTEGER:=0; /*Dupl records */
865 x_error BOOLEAN:=FALSE;
866 dummy CHAR(1);
867
868 BEGIN
869
870 BEGIN
871 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
872
873 UPDATE CZ_XFR_RUN_INFOS SET
874 STARTED=SYSDATE,
875 LAST_ACTIVITY=SYSDATE
876 WHERE RUN_ID=inRUN_ID;
877
878 EXCEPTION
879 WHEN NO_DATA_FOUND THEN
880 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
881 VALUES(inRUN_ID,SYSDATE,SYSDATE);
882
883 WHEN OTHERS THEN
884 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_END_USER',11276);
885 END;
886
887 CZ_IMP_AC_MAIN.CND_END_USER (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
888 IF (nFailed=MAX_ERR) THEN
889 INSERTS:=0;
890 UPDATES:=0;
891 FAILED:=MAX_ERR;
892 DUPS:=0;
893 RETURN;
894 END IF;
895
896 CZ_IMP_AC_KRS.KRS_END_USER(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
897
898 /* Make sure that the error count has not been reached */
899 IF(nFailed < MAX_ERR) THEN
900 CZ_IMP_AC_XFR.XFR_END_USER(inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
901 /* Report Insert Errors */
902 IF (nXfrInsertCount<> INSERTS) THEN
903 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_AC.MAIN_END_USER ',11276);
904 END IF;
905
906 /* Report Update Errors */
907 IF (nXfrUpdateCount<> UPDATES) THEN
908 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_AC.MAIN_END_USER ',11276);
909 END IF;
910
911 /* Return the transferred number of rows and not the number of rows with keys resolved*/
912 INSERTS:=nXfrInsertCount;
913 UPDATES:=nXfrUpdateCount;
914
915 FAILED:=FAILED+nFailed;
916 ELSE
917 FAILED:=nFailed;
918 END IF;
919
920 CZ_IMP_AC_MAIN.RPT_END_USER(inRUN_ID);
921 END ;
922 END MAIN_END_USER;
923 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
924
925 PROCEDURE MAIN_END_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
926 COMMIT_SIZE IN PLS_INTEGER,
927 MAX_ERR IN PLS_INTEGER,
928 INSERTS IN OUT NOCOPY PLS_INTEGER,
929 UPDATES IN OUT NOCOPY PLS_INTEGER,
930 FAILED IN OUT NOCOPY PLS_INTEGER,
931 DUPS IN OUT NOCOPY PLS_INTEGER,
932 inXFR_GROUP IN VARCHAR2
933 ) IS
934 BEGIN
935 DECLARE
936 /* Internal vars */
937 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
938 nErrorCount PLS_INTEGER:=0; /*Error index */
939 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
940 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
941 nFailed PLS_INTEGER:=0; /*Failed records */
942 nDups PLS_INTEGER:=0; /*Dupl records */
943 x_error BOOLEAN:=FALSE;
944 dummy CHAR(1);
945
946 BEGIN
947
948 BEGIN
949 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
950
951 UPDATE CZ_XFR_RUN_INFOS SET
952 STARTED=SYSDATE,
953 LAST_ACTIVITY=SYSDATE
954 WHERE RUN_ID=inRUN_ID;
955
956 EXCEPTION
957 WHEN NO_DATA_FOUND THEN
958 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
959 VALUES(inRUN_ID,SYSDATE,SYSDATE);
960
961 WHEN OTHERS THEN
962 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_END_USER_GROUP',11276);
963 END;
964
965 CZ_IMP_AC_MAIN.CND_END_USER_GROUP (inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
966 IF (nFailed=MAX_ERR) THEN
967 INSERTS:=0;
968 UPDATES:=0;
969 FAILED:=MAX_ERR;
970 DUPS:=0;
971 RETURN;
972 END IF;
973
974 CZ_IMP_AC_KRS.KRS_END_USER_GROUP(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
975
976 /* Make sure that the error count has not been reached */
977 IF(nFailed < MAX_ERR) THEN
978 CZ_IMP_AC_XFR.XFR_END_USER_GROUP(inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
979 /* Report Insert Errors */
980 IF (nXfrInsertCount<> INSERTS) THEN
981 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_AC.MAIN_END_USER_GROUP ',11276);
982 END IF;
983
984 /* Report Update Errors */
985 IF (nXfrUpdateCount<> UPDATES) THEN
986 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_AC.MAIN_END_USER_GROUP ',11276);
987 END IF;
988
989 /* Return the transferred number of rows and not the number of rows with keys resolved*/
990 INSERTS:=nXfrInsertCount;
991 UPDATES:=nXfrUpdateCount;
992
993 FAILED:=FAILED+nFailed;
994 ELSE
995 FAILED:=nFailed;
996 END IF;
997
998 CZ_IMP_AC_MAIN.RPT_END_USER_GROUP(inRUN_ID);
999 END ;
1000 END MAIN_END_USER_GROUP;
1001 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1002
1003 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1004
1005 PROCEDURE MAIN_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
1006 COMMIT_SIZE IN PLS_INTEGER,
1007 MAX_ERR IN PLS_INTEGER,
1008 INSERTS IN OUT NOCOPY PLS_INTEGER,
1009 UPDATES IN OUT NOCOPY PLS_INTEGER,
1010 FAILED IN OUT NOCOPY PLS_INTEGER,
1011 DUPS IN OUT NOCOPY PLS_INTEGER,
1012 inXFR_GROUP IN VARCHAR2
1013 ) IS
1014 BEGIN
1015 DECLARE
1016 /* Internal vars */
1017 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
1018 nErrorCount PLS_INTEGER:=0; /*Error index */
1019 nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
1020 nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
1021 nFailed PLS_INTEGER:=0; /*Failed records */
1022 nDups PLS_INTEGER:=0; /*Dupl records */
1023 x_error BOOLEAN:=FALSE;
1024 dummy CHAR(1);
1025
1026 BEGIN
1027
1028 BEGIN
1029 SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
1030
1031 UPDATE CZ_XFR_RUN_INFOS SET
1032 STARTED=SYSDATE,
1033 LAST_ACTIVITY=SYSDATE
1034 WHERE RUN_ID=inRUN_ID;
1035
1036 EXCEPTION
1037 WHEN NO_DATA_FOUND THEN
1038 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
1039 VALUES(inRUN_ID,SYSDATE,SYSDATE);
1040
1041 WHEN OTHERS THEN
1042 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.MAIN_USER_GROUP',11276);
1043 END;
1044
1045 CZ_IMP_AC_MAIN.CND_USER_GROUP(inRun_ID,COMMIT_SIZE,MAX_ERR,nFailed);
1046 IF (nFailed=MAX_ERR) THEN
1047 INSERTS:=0;
1048 UPDATES:=0;
1049 FAILED:=MAX_ERR;
1050 DUPS:=0;
1051 RETURN;
1052 END IF;
1053
1054 CZ_IMP_AC_KRS.KRS_USER_GROUP(inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,nFailed,DUPS,inXFR_GROUP);
1055
1056 /* Make sure that the error count has not been reached */
1057 IF(nFailed < MAX_ERR) THEN
1058 CZ_IMP_AC_XFR.XFR_USER_GROUP (inRUN_ID,COMMIT_SIZE,MAX_ERR-nFailed,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
1059 /* Report Insert Errors */
1060 IF (nXfrInsertCount<> INSERTS) THEN
1061 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_AC.MAIN_USER_GROUP',11276);
1062 END IF;
1063
1064 /* Report Update Errors */
1065 IF (nXfrUpdateCount<> UPDATES) THEN
1066 x_error:=CZ_IMP_ALL.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_AC.MAIN_USER_GROUP ',11276);
1067 END IF;
1068
1069 /* Return the transferred number of rows and not the number of rows with keys resolved*/
1070 INSERTS:=nXfrInsertCount;
1071 UPDATES:=nXfrUpdateCount;
1072
1073 FAILED:=FAILED+nFailed;
1074 ELSE
1075 FAILED:=nFailed;
1076 END IF;
1077
1078 CZ_IMP_AC_MAIN.RPT_USER_GROUP(inRUN_ID);
1079 END ;
1080 END MAIN_USER_GROUP;
1081 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1082 PROCEDURE RPT_CONTACT ( inRUN_ID IN PLS_INTEGER ) AS
1083 x_error BOOLEAN:=FALSE;
1084 BEGIN
1085 BEGIN
1086 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_CONTACTS';
1087
1088 EXCEPTION
1089 WHEN NO_DATA_FOUND THEN NULL;
1090 END;
1091
1092 DECLARE
1093 CURSOR c_xfr_run_result IS
1094 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1095 FROM cz_imp_contact
1096 WHERE RUN_ID = inRUN_ID
1097 GROUP BY DISPOSITION,REC_STATUS;
1098
1099 ins_disposition cz_xfr_run_results.disposition%TYPE;
1100 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1101 ins_rec_count cz_xfr_run_results.records%TYPE ;
1102
1103 BEGIN
1104
1105 OPEN c_xfr_run_result;
1106 LOOP
1107 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1108 EXIT WHEN c_xfr_run_result%NOTFOUND;
1109
1110 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1111 VALUES(inRUN_ID,'CZ_CONTACTS',ins_disposition,ins_rec_status,ins_rec_count);
1112
1113 END LOOP;
1114 CLOSE c_xfr_run_result;
1115 COMMIT;
1116
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CONTACT',11276);
1120 END;
1121
1122 DECLARE
1123 nErrors PLS_INTEGER;
1124 CURSOR c_get_nErrors IS
1125 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1126 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1127 AND IMP_TABLE='CZ_CONTACTS';
1128 BEGIN
1129 OPEN c_get_nErrors;
1130 FETCH c_get_nErrors INTO nErrors;
1131 CLOSE c_get_nErrors;
1132 UPDATE CZ_XFR_RUN_INFOS
1133 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1134 COMPLETED='1'
1135 WHERE RUN_ID=inRUN_ID;
1136 COMMIT;
1137 EXCEPTION
1138 WHEN OTHERS THEN
1139 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CONTACT',11276);
1140 END;
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CONTACT',11276);
1144 END;
1145
1146 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1147 PROCEDURE RPT_CUSTOMER ( inRUN_ID IN PLS_INTEGER ) AS
1148 x_error BOOLEAN:=FALSE;
1149 BEGIN
1150 BEGIN
1151 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_CUSTOMERS';
1152
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN NULL;
1155 END;
1156
1157 DECLARE
1158 CURSOR c_xfr_run_result IS
1159 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1160 FROM cz_imp_customer
1161 WHERE RUN_ID = inRUN_ID
1162 GROUP BY DISPOSITION,REC_STATUS;
1163
1164 ins_disposition cz_xfr_run_results.disposition%TYPE;
1165 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1166 ins_rec_count cz_xfr_run_results.records%TYPE ;
1167
1168 BEGIN
1169
1170 OPEN c_xfr_run_result;
1171 LOOP
1172 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1173 EXIT WHEN c_xfr_run_result%NOTFOUND;
1174
1175 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1176 VALUES(inRUN_ID,'CZ_CUSTOMERS',ins_disposition,ins_rec_status,ins_rec_count);
1177
1178 END LOOP;
1179 CLOSE c_xfr_run_result;
1180 COMMIT;
1181
1182 EXCEPTION
1183 WHEN OTHERS THEN
1184 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CUSTOMER',11276);
1185 END;
1186
1187 DECLARE
1188 nErrors PLS_INTEGER;
1189 CURSOR c_get_nErrors IS
1190 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1191 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1192 AND IMP_TABLE='CZ_CUSTOMERS';
1193 BEGIN
1194 OPEN c_get_nErrors;
1195 FETCH c_get_nErrors INTO nErrors;
1196 CLOSE c_get_nErrors;
1197 UPDATE CZ_XFR_RUN_INFOS
1198 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1199 COMPLETED='1'
1200 WHERE RUN_ID=inRUN_ID;
1201 COMMIT;
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CUSTOMER',11276);
1205 END;
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CUSTOMER',11276);
1209 END;
1210
1211 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1212 PROCEDURE RPT_ADDRESS ( inRUN_ID IN PLS_INTEGER ) AS
1213 x_error BOOLEAN:=FALSE;
1214 BEGIN
1215 BEGIN
1216 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_ADDRESSES';
1217
1218 EXCEPTION
1219 WHEN NO_DATA_FOUND THEN NULL;
1220 END;
1221
1222 DECLARE
1223 CURSOR c_xfr_run_result IS
1224 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1225 FROM cz_imp_address
1226 WHERE RUN_ID = inRUN_ID
1227 GROUP BY DISPOSITION,REC_STATUS;
1228
1229 ins_disposition cz_xfr_run_results.disposition%TYPE;
1230 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1231 ins_rec_count cz_xfr_run_results.records%TYPE ;
1232
1233 BEGIN
1234
1235 OPEN c_xfr_run_result;
1236 LOOP
1237 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1238 EXIT WHEN c_xfr_run_result%NOTFOUND;
1239
1240 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1241 VALUES(inRUN_ID,'CZ_ADDRESSES',ins_disposition,ins_rec_status,ins_rec_count);
1242
1243 END LOOP;
1244 CLOSE c_xfr_run_result;
1245 COMMIT;
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_ADDRESS',11276);
1250 END;
1251
1252 DECLARE
1253 nErrors PLS_INTEGER;
1254 CURSOR c_get_nErrors IS
1255 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1256 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1257 AND IMP_TABLE='CZ_ADDRESSES';
1258 BEGIN
1259 OPEN c_get_nErrors;
1260 FETCH c_get_nErrors INTO nErrors;
1261 CLOSE c_get_nErrors;
1262 UPDATE CZ_XFR_RUN_INFOS
1263 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1264 COMPLETED='1'
1265 WHERE RUN_ID=inRUN_ID;
1266 COMMIT;
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_ADDRESS',11276);
1270 END;
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_ADDRESS',11276);
1274 END;
1275 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1276 PROCEDURE RPT_ADDRESS_USES ( inRUN_ID IN PLS_INTEGER ) AS
1277 x_error BOOLEAN:=FALSE;
1278 BEGIN
1279 BEGIN
1280 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_ADDRESS_USES';
1281
1282 EXCEPTION
1283 WHEN NO_DATA_FOUND THEN NULL;
1284 END;
1285
1286 DECLARE
1287 CURSOR c_xfr_run_result IS
1288 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1289 FROM cz_imp_address_use
1290 WHERE RUN_ID = inRUN_ID
1291 GROUP BY DISPOSITION,REC_STATUS;
1292
1293 ins_disposition cz_xfr_run_results.disposition%TYPE;
1294 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1295 ins_rec_count cz_xfr_run_results.records%TYPE ;
1296
1297 BEGIN
1298
1299 OPEN c_xfr_run_result;
1300 LOOP
1301 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1302 EXIT WHEN c_xfr_run_result%NOTFOUND;
1303
1304 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1305 VALUES(inRUN_ID,'CZ_ADDRESS_USES',ins_disposition,ins_rec_status,ins_rec_count);
1306
1307 END LOOP;
1308 CLOSE c_xfr_run_result;
1309 COMMIT;
1310
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_ADDRESS_USES',11276);
1314 END;
1315
1316 DECLARE
1317 nErrors PLS_INTEGER;
1318 CURSOR c_get_nErrors IS
1319 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1320 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1321 AND IMP_TABLE='CZ_ADDRESS_USES';
1322 BEGIN
1323 OPEN c_get_nErrors;
1324 FETCH c_get_nErrors INTO nErrors;
1325 CLOSE c_get_nErrors;
1326 UPDATE CZ_XFR_RUN_INFOS
1327 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1328 COMPLETED='1'
1329 WHERE RUN_ID=inRUN_ID;
1330 COMMIT;
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_ADDRESS_USES',11276);
1334 END;
1335 EXCEPTION
1336 WHEN OTHERS THEN
1337 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_ADDRESS_USES',11276);
1338 END;
1339
1340 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1341 PROCEDURE RPT_CUSTOMER_END_USER ( inRUN_ID IN PLS_INTEGER ) AS
1342 x_error BOOLEAN:=FALSE;
1343 BEGIN
1344 BEGIN
1345 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_CUSTOMER_END_USERS';
1346
1347 EXCEPTION
1348 WHEN NO_DATA_FOUND THEN NULL;
1349 END;
1350
1351 DECLARE
1352 CURSOR c_xfr_run_result IS
1353 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1354 FROM cz_imp_customer_end_user
1355 WHERE RUN_ID = inRUN_ID
1356 GROUP BY DISPOSITION,REC_STATUS;
1357
1358 ins_disposition cz_xfr_run_results.disposition%TYPE;
1359 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1360 ins_rec_count cz_xfr_run_results.records%TYPE ;
1361
1362 BEGIN
1363
1364 OPEN c_xfr_run_result;
1365 LOOP
1366 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1367 EXIT WHEN c_xfr_run_result%NOTFOUND;
1368
1369 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1370 VALUES(inRUN_ID,'CZ_CUSTOMER_END_USERS',ins_disposition,ins_rec_status,ins_rec_count);
1371
1372 END LOOP;
1373 CLOSE c_xfr_run_result;
1374 COMMIT;
1375
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CUSTOMER_END_USER',11276);
1379 END;
1380
1381 DECLARE
1382 nErrors PLS_INTEGER;
1383 CURSOR c_get_nErrors IS
1384 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1385 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1386 AND IMP_TABLE='CZ_CUSTOMER_END_USERS';
1387 BEGIN
1388 OPEN c_get_nErrors;
1389 FETCH c_get_nErrors INTO nErrors;
1390 CLOSE c_get_nErrors;
1391 UPDATE CZ_XFR_RUN_INFOS
1392 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1393 COMPLETED='1'
1394 WHERE RUN_ID=inRUN_ID;
1395 COMMIT;
1396 EXCEPTION
1397 WHEN OTHERS THEN
1398 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CUSTOMER_END_USER',11276);
1399 END;
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_CUSTOMER_END_USER',11276);
1403 END;
1404
1405 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1406 PROCEDURE RPT_END_USER ( inRUN_ID IN PLS_INTEGER ) AS
1407 x_error BOOLEAN:=FALSE;
1408 BEGIN
1409 BEGIN
1410 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_END_USERS';
1411
1412 EXCEPTION
1413 WHEN NO_DATA_FOUND THEN NULL;
1414 END;
1415
1416 DECLARE
1417 CURSOR c_xfr_run_result IS
1418 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1419 FROM cz_imp_end_user
1420 WHERE RUN_ID = inRUN_ID
1421 GROUP BY DISPOSITION,REC_STATUS;
1422
1423 ins_disposition cz_xfr_run_results.disposition%TYPE;
1424 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1425 ins_rec_count cz_xfr_run_results.records%TYPE ;
1426
1427 BEGIN
1428
1429 OPEN c_xfr_run_result;
1430 LOOP
1431 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1432 EXIT WHEN c_xfr_run_result%NOTFOUND;
1433
1434 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1435 VALUES(inRUN_ID,'CZ_END_USERS',ins_disposition,ins_rec_status,ins_rec_count);
1436
1437 END LOOP;
1438 CLOSE c_xfr_run_result;
1439 COMMIT;
1440
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_END_USER',11276);
1444 END;
1445
1446 DECLARE
1447 nErrors PLS_INTEGER;
1448 CURSOR c_get_nErrors IS
1449 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1450 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1451 AND IMP_TABLE='CZ_END_USERS';
1452 BEGIN
1453 OPEN c_get_nErrors;
1454 FETCH c_get_nErrors INTO nErrors;
1455 CLOSE c_get_nErrors;
1456 UPDATE CZ_XFR_RUN_INFOS
1457 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1458 COMPLETED='1'
1459 WHERE RUN_ID=inRUN_ID;
1460 COMMIT;
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_END_USER',11276);
1464 END;
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_END_USER',11276);
1468 END;
1469
1470 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1471 PROCEDURE RPT_END_USER_GROUP ( inRUN_ID IN PLS_INTEGER ) AS
1472 x_error BOOLEAN:=FALSE;
1473 BEGIN
1474 BEGIN
1475 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_END_USER_GROUPS';
1476
1477 EXCEPTION
1478 WHEN NO_DATA_FOUND THEN NULL;
1479 END;
1480
1481 DECLARE
1482 CURSOR c_xfr_run_result IS
1483 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1484 FROM cz_imp_end_user_group
1485 WHERE RUN_ID = inRUN_ID
1486 GROUP BY DISPOSITION,REC_STATUS;
1487
1488 ins_disposition cz_xfr_run_results.disposition%TYPE;
1489 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1490 ins_rec_count cz_xfr_run_results.records%TYPE ;
1491
1492 BEGIN
1493
1494 OPEN c_xfr_run_result;
1495 LOOP
1496 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1497 EXIT WHEN c_xfr_run_result%NOTFOUND;
1498
1499 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1500 VALUES(inRUN_ID,'CZ_END_USER_GROUPS',ins_disposition,ins_rec_status,ins_rec_count);
1501
1502 END LOOP;
1503 CLOSE c_xfr_run_result;
1504 COMMIT;
1505
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_END_USER_GROUP',11276);
1509 END;
1510
1511 DECLARE
1512 nErrors PLS_INTEGER;
1513 CURSOR c_get_nErrors IS
1514 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1515 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1516 AND IMP_TABLE='CZ_END_USER_GROUPS';
1517 BEGIN
1518 OPEN c_get_nErrors;
1519 FETCH c_get_nErrors INTO nErrors;
1520 CLOSE c_get_nErrors;
1521 UPDATE CZ_XFR_RUN_INFOS
1522 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1523 COMPLETED='1'
1524 WHERE RUN_ID=inRUN_ID;
1525 COMMIT;
1526 EXCEPTION
1527 WHEN OTHERS THEN
1528 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_END_USER_GROUP',11276);
1529 END;
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_END_USER_GROUP',11276);
1533 END;
1534
1535 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1536 PROCEDURE RPT_USER_GROUP ( inRUN_ID IN PLS_INTEGER ) AS
1537 x_error BOOLEAN:=FALSE;
1538 BEGIN
1539 BEGIN
1540 DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE='CZ_USER_GROUPS';
1541
1542 EXCEPTION
1543 WHEN NO_DATA_FOUND THEN NULL;
1544 END;
1545
1546 DECLARE
1547 CURSOR c_xfr_run_result IS
1548 SELECT DISPOSITION,REC_STATUS,COUNT(*)
1549 FROM cz_imp_user_group
1550 WHERE RUN_ID = inRUN_ID
1551 GROUP BY DISPOSITION,REC_STATUS;
1552
1553 ins_disposition cz_xfr_run_results.disposition%TYPE;
1554 ins_rec_status cz_xfr_run_results.rec_status%TYPE ;
1555 ins_rec_count cz_xfr_run_results.records%TYPE ;
1556
1557 BEGIN
1558
1559 OPEN c_xfr_run_result;
1560 LOOP
1561 FETCH c_xfr_run_result INTO ins_disposition,ins_rec_status,ins_rec_count;
1562 EXIT WHEN c_xfr_run_result%NOTFOUND;
1563
1564 INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
1565 VALUES(inRUN_ID,'CZ_USER_GROUPS',ins_disposition,ins_rec_status,ins_rec_count);
1566
1567 END LOOP;
1568 CLOSE c_xfr_run_result;
1569 COMMIT;
1570
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_USER_GROUP',11276);
1574 END;
1575
1576 DECLARE
1577 nErrors PLS_INTEGER;
1578 CURSOR c_get_nErrors IS
1579 SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
1580 WHERE REC_STATUS<>'OK' AND RUN_ID=inRUN_ID
1581 AND IMP_TABLE='CZ_USER_GROUPS';
1582 BEGIN
1583 OPEN c_get_nErrors;
1584 FETCH c_get_nErrors INTO nErrors;
1585 CLOSE c_get_nErrors;
1586 UPDATE CZ_XFR_RUN_INFOS
1587 SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
1588 COMPLETED='1'
1589 WHERE RUN_ID=inRUN_ID;
1590 COMMIT;
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_USER_GROUP',11276);
1594 END;
1595 EXCEPTION
1596 WHEN OTHERS THEN
1597 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.RPT_USER_GROUP',11276);
1598 END;
1599 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1600
1601 END CZ_IMP_AC_MAIN;
1602 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
[Home] [Help]