[Home] [Help]
PACKAGE BODY: APPS.CZ_IMP_AC_KRS
Source
1 PACKAGE BODY CZ_IMP_AC_KRS AS
2 --$Header: cziackrb.pls 115.16 2002/11/27 16:58:20 askhacha ship $
3
4 PROCEDURE KRS_CONTACT ( inRUN_ID IN PLS_INTEGER,
5 COMMIT_SIZE IN PLS_INTEGER,
6 MAX_ERR IN PLS_INTEGER,
7 INSERTS OUT NOCOPY PLS_INTEGER,
8 UPDATES OUT NOCOPY PLS_INTEGER,
9 FAILED OUT NOCOPY PLS_INTEGER,
10 DUPS OUT NOCOPY PLS_INTEGER,
11 inXFR_GROUP IN VARCHAR2
12 ) IS
13 BEGIN
14 DECLARE
15 CURSOR c_imp_contact IS
16 SELECT ORIG_SYS_REF , FSK_CUSTOMER_1_1, FSK_address_2_1, ROWID FROM CZ_IMP_CONTACT
17 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
18 ORDER BY ORIG_SYS_REF, FSK_CUSTOMER_1_1 , FSK_address_2_1, ROWID;
19
20 /* cursor's data found indicator */
21 x_imp_contact_f BOOLEAN:=FALSE;
22 x_onl_contact_f BOOLEAN:=FALSE;
23 x_onl_customer_customerid_f BOOLEAN:=FALSE;
24 x_onl_address_addressid_f BOOLEAN:=FALSE;
25 x_error BOOLEAN:=FALSE;
26 nOnlContactId CZ_IMP_CONTACT.CONTACT_ID%TYPE;
27 nOnlcustomerId CZ_IMP_CUSTOMER.CUSTOMER_ID%TYPE;
28 nOnladdressId CZ_IMP_ADDRESS.ADDRESS_ID%TYPE;
29 sImpOrigSysRef CZ_IMP_CONTACT.ORIG_SYS_REF%TYPE;
30 sFSKcustomer CZ_IMP_CONTACT.FSK_CUSTOMER_1_1%TYPE;
31 sFSKaddress CZ_IMP_CONTACT.FSK_ADDRESS_2_1%TYPE;
32 sLastFSK1 CZ_IMP_CONTACT.ORIG_SYS_REF%TYPE;
33 sThisFSK1 CZ_IMP_CONTACT.ORIG_SYS_REF%TYPE;
34 sLastFSK2 CZ_IMP_CONTACT.FSK_CUSTOMER_1_1%TYPE;
35 sThisFSK2 CZ_IMP_CONTACT.FSK_CUSTOMER_1_1%TYPE;
36 sLastFSK3 CZ_IMP_CONTACT.FSK_ADDRESS_2_1%TYPE;
37 sThisFSK3 CZ_IMP_CONTACT.FSK_ADDRESS_2_1%TYPE;
38 sRecStatus CZ_IMP_CONTACT.REC_STATUS%TYPE;
39 sDisposition CZ_IMP_CONTACT.DISPOSITION%TYPE;
40 /* Internal vars */
41 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
42 nErrorCount PLS_INTEGER:=0; /*Error index */
43 nInsertCount PLS_INTEGER:=0; /*Inserts */
44 nUpdateCount PLS_INTEGER:=0; /*Updates */
45 nFailed PLS_INTEGER:=0; /*Failed records */
46 nDups PLS_INTEGER:=0; /*Dupl records */
47 /* No surrogates for Contact, CUSTOMER and Address since all three tables use ORIG_SYS_REF */
48 nAllocateBlock PLS_INTEGER:=1;
49 nAllocateCounter PLS_INTEGER;
50 nNextValue NUMBER;
51
52 thisRowId ROWID;
53
54 BEGIN
55
56 BEGIN
57 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
58 WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
59 EXCEPTION
60 WHEN OTHERS THEN
61 nAllocateBlock:=1;
62 END;
63 nAllocateCounter:=nAllocateBlock-1;
64
65 /* This type casting is necessary to use decode stmt */
66 OPEN c_imp_contact ;
67
68 LOOP
69 /* COMMIT if the buffer size is reached */
70 IF (nCommitCount>= COMMIT_SIZE) THEN
71 BEGIN
72 COMMIT;
73 nCommitCount:=0;
74 END;
75 ELSE
76 nCOmmitCount:=nCommitCount+1;
77 END IF;
78
79 sImpOrigsysref:=NULL; sFSKcustomer:=NULL; sFSKaddress:=NULL;
80 FETCH c_imp_contact INTO sImpOrigSysref, sFSKcustomer, sFSKaddress, thisRowId;
81 sThisFSK1:=sImpOrigSysRef;
82 sThisFSK2:=sFSKcustomer;
83 sThisFSK3:=sFSKAddress;
84 x_imp_contact_f:=c_imp_contact%FOUND;
85
86 EXIT WHEN NOT x_imp_contact_f;
87 /* Check Online Dbase */
88 DECLARE
89 CURSOR c_onl_contact IS
90 SELECT CONTACT_ID FROM CZ_CONTACTS WHERE ORIG_SYS_REF=sImpOrigsysref;
91 BEGIN
92 OPEN c_onl_contact;
93 nOnlContactId:=NULL;
94 FETCH c_onl_contact INTO nOnlContactId;
95 x_onl_contact_f:=c_onl_contact%FOUND;
96 CLOSE c_onl_contact;
97 END;
98
99 DECLARE
100 CURSOR c_onl_customer_customerid IS
101 SELECT CUSTOMER_ID FROM CZ_CUSTOMERS WHERE ORIG_SYS_REF= sFSKcustomer;
102 BEGIN
103 OPEN c_onl_customer_customerid;
104 nOnlcustomerId:=NULL;
105 FETCH c_onl_customer_customerid INTO nOnlcustomerId;
106 x_onl_customer_customerid_f:=c_onl_customer_customerid%FOUND;
107 CLOSE c_onl_customer_customerid;
108 END;
109
110 DECLARE
111 CURSOR c_onl_address_addressid IS
112 SELECT ADDRESS_ID FROM CZ_ADDRESSES WHERE ORIG_SYS_REF= sFSKaddress;
113 BEGIN
114 OPEN c_onl_address_addressid;
115 nOnladdressId:=NULL;
116 FETCH c_onl_address_addressid INTO nOnladdressId;
117 x_onl_address_addressid_f:=c_onl_address_addressid%FOUND;
118 CLOSE c_onl_address_addressid;
119 END;
120
121 IF(NOT x_onl_customer_customerid_f OR (sFSKcustomer IS NULL) OR
122 NOT x_onl_address_addressid_f OR (sFSKAddress IS NULL) OR
123 (sImpOrigSysref IS NULL )) THEN
124 BEGIN
125 /* The record has no FSK or Surrogate key*/
126 nFailed:=nFailed+1;
127 IF (sImpOrigSysref IS NULL) THEN
128 sRecStatus:='N17';
129 ELSIF(NOT x_onl_customer_customerid_f AND sFSKcustomer IS NULL) THEN
130 sRecStatus:='N41';
131 ELSIF(NOT x_onl_customer_customerid_f) THEN
132 sRecStatus:='F41';
133 ELSIF(NOT x_onl_address_addressid_f AND sFSKaddress IS NULL) THEN
134 sRecStatus:='N44';
135 ELSIF(NOT x_onl_address_addressid_f) THEN
136 sRecStatus:='F44';
137 END IF;
138 sDisposition:='R';
139 END;
140 ELSE
141 BEGIN
142 IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 AND
143 sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2 AND
144 sLastFSK3 IS NOT NULL AND sLastFSK3=sThisFSK3) THEN
145 /* This is a duplicate record */
146 sRecStatus:='DUPL';
147 sDisposition:='R';
148 nDups:=nDups+1;
149 nFailed:=nFailed+1;
150 ELSE
151 BEGIN
152 sRecStatus:='PASS';
153 IF( x_onl_contact_f)THEN
154 /* Update */
155 sDisposition:='M';
156 nUpdateCount:=nUpdateCount+1;
157 ELSE
158 /*Insert */
159 sDisposition:='I';
160 /* Get PK for this record */
161 nInsertCount:=nInsertCount+1;
162 nAllocateCounter:=nAllocateCounter+1;
163 IF(nAllocateCounter=nAllocateBlock)THEN
164 nAllocateCounter:=0;
165 SELECT CZ_CONTACTS_S.NEXTVAL INTO nNextValue FROM DUAL;
166 END IF;
167 END IF;
168 END;
169 END IF;
170 END;
171 END IF;
172
173 UPDATE CZ_IMP_CONTACT SET
174 CONTACT_ID=DECODE(sDISPOSITION,'R',CONTACT_ID,'I', nNextValue+nAllocateCounter, nOnlContactId),
175 CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,nonlcustomerid),
176 ADDRESS_ID=DECODE(sDISPOSITION,'R',ADDRESS_ID,nonladdressid),
177 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
178 WHERE ROWID = thisRowId;
179 sLastFSK1:=sImpOrigSysref;
180 sLastFSK2:=sFSKcustomer;
181 sLastFSK3:=sFSKAddress;
182
183 /* Return if MAX_ERR is reached */
184 IF (nFailed >= MAX_ERR) THEN
185 EXIT;
186 END IF;
187 sDisposition:=NULL; sRecStatus:=NULL;
188 END LOOP;
189 /* No more data */
190 CLOSE c_imp_contact;
191
192 COMMIT;
193
194 INSERTS:=nInsertCount;
195 UPDATES:=nUpdateCount;
196 FAILED:=nFailed;
197 DUPS:=nDups;
198 EXCEPTION
199 WHEN OTHERS THEN
200 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_KRS.KRS_CONTACT',11276);
201 END;
202 END KRS_CONTACT;
203 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
204 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
205 /* *comment_label: */
206 /* 01.06.99 -- no parent-child relationships for this table */
207 /* -- no references to PRICE_GROUP table */
208 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
209 PROCEDURE KRS_CUSTOMER ( inRUN_ID IN PLS_INTEGER,
210 COMMIT_SIZE IN PLS_INTEGER,
211 MAX_ERR IN PLS_INTEGER,
212 INSERTS OUT NOCOPY PLS_INTEGER,
213 UPDATES OUT NOCOPY PLS_INTEGER,
214 FAILED OUT NOCOPY PLS_INTEGER,
215 DUPS OUT NOCOPY PLS_INTEGER,
216 inXFR_GROUP IN VARCHAR2
217 ) IS
218 BEGIN
219 DECLARE
220 CURSOR c_imp_customer (x_usesurr_pricegroup PLS_INTEGER) IS
221 SELECT ORIG_SYS_REF, DECODE(x_usesurr_pricegroup,0,FSK_PRICEGROUP_1_1,1,FSK_PRICEGROUP_1_EXT) ,FSK_CUSTOMER_2_1, ROWID
222 FROM CZ_IMP_CUSTOMER WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY 1,2,3, ROWID;
223
224 /* cursor's data found indicator */
225 x_imp_customer_f BOOLEAN:=FALSE;
226 x_onl_customer_f BOOLEAN:=FALSE;
227 x_onl_pricegroup_prgrpid_f BOOLEAN:=FALSE;
228 x_onl_customer_parentid_f BOOLEAN:=FALSE;
229 x_error BOOLEAN:=FALSE;
230 nOnlcustomerId CZ_IMP_CUSTOMER.CUSTOMER_ID%TYPE;
231 nOnlFskParentId CZ_IMP_CUSTOMER.PARENT_ID%TYPE;
232 nOnlFSKPriceGroupId CZ_IMP_CUSTOMER.PRICE_list_ID%TYPE;
233 sImpOrigsysref CZ_IMP_CUSTOMER.ORIG_SYS_REF%TYPE;
234 sFSKPRICEGROUP CZ_IMP_CUSTOMER.FSK_PRICEGROUP_1_1%TYPE;
235 sFSKCUSTOMER CZ_IMP_CUSTOMER.FSK_CUSTOMER_2_1%TYPE;
236 sLastFSK1 CZ_IMP_CUSTOMER.ORIG_SYS_REF%TYPE;
237 sThisFSK1 CZ_IMP_CUSTOMER.ORIG_SYS_REF%TYPE;
238 sLastFSK2 CZ_IMP_CUSTOMER.FSK_PRICEGROUP_1_1%TYPE;
239 sThisFSK2 CZ_IMP_CUSTOMER.FSK_PRICEGROUP_1_1%TYPE;
240 sLastFSK3 CZ_IMP_CUSTOMER.FSK_CUSTOMER_2_1%TYPE;
241 sThisFSK3 CZ_IMP_CUSTOMER.FSK_CUSTOMER_2_1%TYPE;
242 sRecStatus CZ_IMP_CUSTOMER.REC_STATUS%TYPE;
243 sDisposition CZ_IMP_CUSTOMER.DISPOSITION%TYPE;
244 /* Internal vars */
245 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
246 nErrorCount PLS_INTEGER:=0; /*Error index */
247 nInsertCount PLS_INTEGER:=0; /*Inserts */
248 nUpdateCount PLS_INTEGER:=0; /*Updates */
249 nFailed PLS_INTEGER:=0; /*Failed records */
250 nDups PLS_INTEGER:=0; /*Dupl records */
251 x_usesurr_pricegroup PLS_INTEGER:= /*Use surrogates */
252 CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_PRICE_GROUPS',inXFR_GROUP);
253 nAllocateBlock PLS_INTEGER:=1;
257
254 nAllocateCounter PLS_INTEGER;
255 nNextValue NUMBER;
256 thisRowId ROWID;
258 BEGIN
259
260 BEGIN
261 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
262 WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
263 EXCEPTION
264 WHEN OTHERS THEN
265 nAllocateBlock:=1;
266 END;
267 nAllocateCounter:=nAllocateBlock-1;
268
269 /* This type casting is necessary to use decode stmt */
270 OPEN c_imp_CUSTOMER(x_usesurr_pricegroup) ;
271
272 LOOP
273 /* COMMIT if the buffer size is reached */
274 IF (nCommitCount>= COMMIT_SIZE) THEN
275 BEGIN
276 COMMIT;
277 nCommitCount:=0;
278 END;
279 ELSE
280 nCOmmitCount:=nCommitCount+1;
281 END IF;
282
283 sImporigsysref:=NULL; sfskPriceGroup:=NULL; sfskCUSTOMER:=NULL;
284 FETCH c_imp_CUSTOMER INTO sImpOrigSysref, sfskPriceGroup, sfskCUSTOMER, thisRowId;
285 sThisFSK1:=sImpOrigSysref;
286 sThisFSK2:=sFSKPriceGroup;
287 sThisFSK3:=sFSKCUSTOMER;
288
289 x_imp_CUSTOMER_f:=c_imp_CUSTOMER%FOUND;
290
291 EXIT WHEN NOT x_imp_CUSTOMER_f;
292 /* Check Online Dbase */
293 DECLARE
294 CURSOR c_onl_CUSTOMER IS
295 SELECT CUSTOMER_ID FROM CZ_CUSTOMERS
296 WHERE ORIG_SYS_REF=sImpOrigSysref;
297 BEGIN
298 OPEN c_onl_CUSTOMER;
299 nOnlCUSTOMERId:=NULL;
300 FETCH c_onl_CUSTOMER INTO nOnlCUSTOMERId;
301 x_onl_CUSTOMER_f:=c_onl_CUSTOMER%FOUND;
302 CLOSE c_onl_CUSTOMER;
303 END;
304 /*------------------------------------01.06.99--------------------------------------------------
305 nOnlFSKPriceGroupid:=NULL;
306 DECLARE
307 CURSOR c_onl_pricegroup_prgrpid IS
308 SELECT PRICE_GROUP_ID FROM PRICE_GROUP WHERE NAME=sFSKPRICEGROUP;
309 BEGIN
310 OPEN c_onl_pricegroup_prgrpid;
311 nOnlFSKPriceGroupid:=NULL;
312 FETCH c_onl_pricegroup_prgrpid INTO nOnlFSKPriceGroupid;
313 x_onl_pricegroup_prgrpid_f:=c_onl_pricegroup_prgrpid%FOUND;
314 CLOSE c_onl_pricegroup_prgrpid;
315 END;
316 nOnlFSKParentid:=NULL;
317 DECLARE
318 CURSOR c_onl_CUSTOMER_parentid IS
319 SELECT CUSTOMER_ID FROM CUSTOMER WHERE ORIG_SYS_REF= sFSKCUSTOMER;
320 BEGIN
321 OPEN c_onl_CUSTOMER_parentid;
322 nOnlFSKParentid:=NULL;
323 FETCH c_onl_CUSTOMER_parentid INTO nOnlFSKParentid;
324 x_onl_CUSTOMER_parentid_f:=c_onl_CUSTOMER_parentid%FOUND;
325 CLOSE c_onl_CUSTOMER_parentid;
326 END;
327 ----------------------------------------------------------------------------------------------*/
328 x_onl_pricegroup_prgrpid_f:=TRUE;
329 x_onl_CUSTOMER_parentid_f:=TRUE;
330
331 IF(NOT x_onl_pricegroup_prgrpid_f OR
332 (NOT x_onl_CUSTOMER_parentid_f AND sFSKCUSTOMER IS NOT NULL) OR
333 (sImpOrigSysref IS NULL )) THEN
334 BEGIN
335 /* The record has CUSTOMERID but no Price GroupId or FSK CUSTOMERId */
336 nFailed:=nFailed+1;
337 IF (sImporigsysref IS NULL) THEN
338 sRecStatus:='N8';
339 ELSIF(NOT x_onl_pricegroup_prgrpid_f AND x_usesurr_pricegroup=1 AND sFSKPRICEGROUP IS NULL) THEN
340 sRecStatus:='N30';
341 ELSIF(NOT x_onl_pricegroup_prgrpid_f AND x_usesurr_pricegroup=1) THEN
342 sRecStatus:='F30';
343 ELSIF (NOT x_onl_pricegroup_prgrpid_f AND x_usesurr_pricegroup=0 AND sFSKPRICEGROUP IS NULL) THEN
344 sRecStatus:='N29';
345 ELSIF (NOT x_onl_pricegroup_prgrpid_f AND x_usesurr_pricegroup=0) THEN
346 sRecStatus:='F29';
347 ELSIF(NOT x_onl_CUSTOMER_parentid_f AND sFSKCUSTOMER IS NOT NULL) THEN
348 sRecStatus:='F32';
349 END IF;
350 sDisposition:='R';
351 END;
352 ELSE
353 /* insert or update */
354 BEGIN
355 IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 /*AND
356 sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2 AND
357 sLastFSK3 IS NOT NULL AND sLastFSK3=sThisFSK3 */) THEN
361 nDups:=nDups+1;
358 /* This is a duplicate record */
359 sRecStatus:='DUPL';
360 sDisposition:='R';
362 nFailed:=nFailed+1;
363 ELSE
364 BEGIN
365 sRecStatus:='PASS';
366 IF( x_onl_CUSTOMER_f)THEN
367 /* Update */
368 sDisposition:='M';
369 nUpdateCount:=nUpdateCount+1;
370 ELSE
371 /*Insert */
372 sDisposition:='I';
373 /* Get PK for this record */
374 nInsertCount:=nInsertCount+1;
375 nAllocateCounter:=nAllocateCounter+1;
376 IF(nAllocateCounter=nAllocateBlock)THEN
377 nAllocateCounter:=0;
378 SELECT CZ_CUSTOMERS_S.NEXTVAL INTO nNextValue FROM DUAL;
379 END IF;
380 END IF;
381 END;
382 END IF;
383 END;
384 END IF;
385
386 UPDATE CZ_IMP_CUSTOMER SET CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,'I',nNextValue+nAllocateCounter,nonlCUSTOMERid),
387 PRICE_LIST_ID=DECODE(sDISPOSITION,'R',PRICE_list_ID,nOnlFSKPriceGroupId),
388 PARENT_ID=DECODE(sDISPOSITION,'R',PARENT_ID,nonlFSKParentid),
389 DISPOSITION=sDisposition, REC_STATUS=sRecStatus WHERE ROWID = thisRowId;
390 sLastFSK1:=sImpOrigSysref;
391 sLastFSK2:=sFSKPriceGroup;
392 sLastFSK3:=sFSKCUSTOMER;
393
394 /* Return if MAX_ERR is reached */
395 IF (nFailed >= MAX_ERR) THEN
396 EXIT;
397 END IF;
398 sDisposition:=NULL; sRecStatus:=NULL;
399 END LOOP;
400 /* No more data */
401 CLOSE c_imp_CUSTOMER;
402
403 COMMIT;
404
405 INSERTS:=nInsertCount;
406 UPDATES:=nUpdateCount;
407 FAILED:=nFailed;
408 DUPS:=nDups;
409 EXCEPTION
410 WHEN OTHERS THEN
411 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_KRS.KRS_CUSTOMER',11276);
412 END;
413 END KRS_CUSTOMER;
414 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
415 PROCEDURE KRS_ADDRESS ( inRUN_ID IN PLS_INTEGER,
416 COMMIT_SIZE IN PLS_INTEGER,
417 MAX_ERR IN PLS_INTEGER,
418 INSERTS OUT NOCOPY PLS_INTEGER,
419 UPDATES OUT NOCOPY PLS_INTEGER,
420 FAILED OUT NOCOPY PLS_INTEGER,
421 DUPS OUT NOCOPY PLS_INTEGER,
422 inXFR_GROUP IN VARCHAR2
423 ) IS
424 BEGIN
425 DECLARE
426 CURSOR c_imp_address IS
427 SELECT ORIG_SYS_REF, FSK_CUSTOMER_1_1, ROWID FROM CZ_IMP_ADDRESS WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY ORIG_SYS_REF,FSK_CUSTOMER_1_1, ROWID;
428 /* cursor's data found indicators */
429 nOnlADDRESSId CZ_IMP_ADDRESS.ADDRESS_ID%TYPE;
430 nOnlFSKCUSTOMERID CZ_IMP_ADDRESS.FSK_CUSTOMER_1_1%TYPE;
431 sImpOrigSysRef CZ_IMP_ADDRESS.ORIG_SYS_REF%TYPE;
432 sFSKCUSTOMER11 CZ_IMP_ADDRESS.FSK_CUSTOMER_1_1%TYPE;
433 sLastFSK1 CZ_IMP_ADDRESS.ADDRESS_ID%TYPE;
434 sThisFSK1 CZ_IMP_ADDRESS.ADDRESS_ID%TYPE;
435 sRecStatus CZ_IMP_ADDRESS.REC_STATUS%TYPE;
436 sDisposition CZ_IMP_ADDRESS.DISPOSITION%TYPE;
437 /* Column Vars */
438 x_imp_address_f BOOLEAN:=FALSE;
439 x_onl_address_f BOOLEAN:=FALSE;
440 x_onl_CUSTOMER_CUSTOMERid_f BOOLEAN:=FALSE;
441 x_error BOOLEAN:=FALSE;
442 p_onl_CUSTOMER CHAR(1):='';
443 /* Internal vars */
444 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
445 nErrorCount PLS_INTEGER:=0; /*Error index */
446 nInsertCount PLS_INTEGER:=0; /*Inserts */
447 nUpdateCount PLS_INTEGER:=0; /*Updates */
451 CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ADDRESSES',inXFR_GROUP);
448 nFailed PLS_INTEGER:=0; /*Failed records */
449 nDups PLS_INTEGER:=0; /*Duplicate records */
450 x_usesurr PLS_INTEGER:= /*Use surrogates */
452 nAllocateBlock PLS_INTEGER:=1;
453 nAllocateCounter PLS_INTEGER;
454 nNextValue NUMBER;
455
456 thisRowId ROWID;
457
458 BEGIN
459
460 BEGIN
461 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
462 WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
463 EXCEPTION
464 WHEN OTHERS THEN
465 nAllocateBlock:=1;
466 END;
467 nAllocateCounter:=nAllocateBlock-1;
468
469 OPEN c_imp_address ;
470
471 LOOP
472 /* COMMIT if the buffer size is reached */
473 IF (nCommitCount>= COMMIT_SIZE) THEN
474 BEGIN
475 COMMIT;
476 nCommitCount:=0;
477 END;
478 ELSE
479 nCOmmitCount:=nCommitCount+1;
480 END IF;
481 sImpOrigSysRef:=NULL; sFSKCUSTOMER11:=NULL;
482 FETCH c_imp_address INTO sImporigsysref, sFSKCUSTOMER11, thisRowId;
483 sThisFSK1:=sImporigsysref;
484 x_imp_address_f:=c_imp_address%FOUND;
485
486 EXIT WHEN NOT x_imp_address_f;
487 DECLARE
488 CURSOR c_onl_address_addressid IS
489 SELECT ADDRESS_ID FROM CZ_ADDRESSES WHERE ORIG_SYS_REF=sImpOrigSysRef;
490 BEGIN
491 OPEN c_onl_address_addressid;
492 nOnlAddressId:=NULL;
493 FETCH c_onl_address_addressid INTO nOnladdressId;
494 x_onl_address_f:=c_onl_address_addressid%FOUND;
495 CLOSE c_onl_address_addressid;
496 END;
497 DECLARE
498 CURSOR c_onl_CUSTOMER_CUSTOMERid IS
499 SELECT CUSTOMER_ID FROM CZ_CUSTOMERS WHERE ORIG_SYS_REF=sFSKCUSTOMER11;
500 BEGIN
501 OPEN c_onl_CUSTOMER_CUSTOMERid ;
502 nOnlFSKCUSTOMERId:=NULL;
503 FETCH c_onl_CUSTOMER_CUSTOMERid INTO nOnlFSKCUSTOMERId;
504 x_onl_CUSTOMER_CUSTOMERid_f:=c_onl_CUSTOMER_CUSTOMERid%FOUND;
505 CLOSE c_onl_CUSTOMER_CUSTOMERid ;
506 END;
507 IF(NOT x_onl_CUSTOMER_CUSTOMERid_f ) THEN
508 BEGIN
509 /* The record has missing FSKs */
510 nFailed:=nFailed+1;
511 IF(sFSKCUSTOMER11 IS NULL) THEN
512 sRecStatus:='N28';
513 ELSE
514 sRecStatus:='F28';
515 END IF;
516 sDisposition:='R';
517 END;
518 ELSE
519 /* Insert or update */
520 BEGIN
521 IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1) THEN
522 /* This is a duplicate record */
523 sRecStatus:='DUPL';
524 sDisposition:='R';
525 nDups:=nDups+1;
526 nFailed:=nFailed+1;
527 ELSE
528 BEGIN
529 sRecStatus:='PASS';
530 IF( x_onl_address_f)THEN
531 /* Update */
532 sDisposition:='M';
533 nUpdateCount:=nUpdateCount+1;
534 ELSE
535 /*Insert */
536 sDisposition:='I';
537 nInsertCount:=nInsertCount+1;
538 nAllocateCounter:=nAllocateCounter+1;
539 IF(nAllocateCounter=nAllocateBlock)THEN
540 nAllocateCounter:=0;
541 SELECT CZ_ADDRESSES_S.NEXTVAL INTO nNextValue FROM DUAL;
542 END IF;
543 END IF;
544 END;
545 END IF;
546 END;
547 END IF;
548 UPDATE CZ_IMP_ADDRESS SET ADDRESS_ID=DECODE(sDISPOSITION,
549 'R',ADDRESS_ID,'I',nNextValue+nAllocateCounter,
550 nOnlADDRESSId),
551 CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,
555
552 nOnlFSKCUSTOMERId),DISPOSITION=sDisposition, REC_STATUS=sRecStatus
553 WHERE ROWID = thisRowId;
554 sLastFSK1:=sImporigsysref;
556 /* Return if MAX_ERR is reached */
557 IF (nFailed >= MAX_ERR) THEN
558 EXIT;
559 END IF;
560 sDisposition:=NULL; sRecStatus:=NULL;
561 END LOOP;
562 CLOSE c_imp_address;
563
564 COMMIT;
565
566 INSERTS:=nInsertCount;
567 UPDATES:=nUpdateCount;
568 FAILED:=nFailed;
569 DUPS:=nDups;
570 EXCEPTION
571 WHEN OTHERS THEN
572 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_KRS.KRS_ADDRESS',11276);
573 END;
574 END KRS_ADDRESS ;
575 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
576 PROCEDURE KRS_ADDRESS_USES(inRUN_ID IN PLS_INTEGER,
577 COMMIT_SIZE IN PLS_INTEGER,
578 MAX_ERR IN PLS_INTEGER,
579 INSERTS OUT NOCOPY PLS_INTEGER,
580 UPDATES OUT NOCOPY PLS_INTEGER,
581 FAILED OUT NOCOPY PLS_INTEGER,
582 DUPS OUT NOCOPY PLS_INTEGER,
583 inXFR_GROUP IN VARCHAR2
584 ) IS
585 BEGIN
586 DECLARE
587 CURSOR c_imp_address_uses IS
588 SELECT orig_sys_ref, fsk_address_1_1, ROWID
589 FROM CZ_IMP_ADDRESS_USE
590 WHERE rec_status IS NULL AND Run_ID = inRUN_ID
591 ORDER BY orig_sys_ref, fsk_address_1_1, ROWID;
592
593 /* cursor's data found indicator */
594 x_imp_address_uses_f BOOLEAN:=FALSE;
595 x_onl_address_uses_f BOOLEAN:=FALSE;
596 x_onl_address_addressid_f BOOLEAN:=FALSE;
597 x_error BOOLEAN:=FALSE;
598
599 nOnladdressusesId CZ_IMP_ADDRESS_USE.ADDRESS_USE_ID%TYPE;
600 nOnladdressId CZ_IMP_ADDRESS.ADDRESS_ID%TYPE;
601 sImpOrigSysRef CZ_IMP_ADDRESS_USE.ORIG_SYS_REF%TYPE;
602 sFSKaddress CZ_IMP_ADDRESS_USE.FSK_ADDRESS_1_1%TYPE;
603 sThisFSK1 CZ_IMP_ADDRESS_USE.ORIG_SYS_REF%TYPE;
604 sLastFSK1 CZ_IMP_ADDRESS_USE.ORIG_SYS_REF%TYPE;
605 sRecStatus CZ_IMP_ADDRESS_USE.REC_STATUS%TYPE;
606 sDisposition CZ_IMP_ADDRESS_USE.DISPOSITION%TYPE;
607
608 /* Internal vars */
609 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
610 nErrorCount PLS_INTEGER:=0; /*Error index */
611 nInsertCount PLS_INTEGER:=0; /*Inserts */
612 nUpdateCount PLS_INTEGER:=0; /*Updates */
613 nFailed PLS_INTEGER:=0; /*Failed records */
614 nDups PLS_INTEGER:=0; /*Dupl records */
615 nAllocateBlock PLS_INTEGER:=1;
616 nAllocateCounter PLS_INTEGER;
617 nNextValue NUMBER;
618
619 thisRowId ROWID;
620
621 BEGIN
622
623 BEGIN
624 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
625 WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
626 EXCEPTION
627 WHEN OTHERS THEN
628 nAllocateBlock:=1;
629 END;
630 nAllocateCounter:=nAllocateBlock-1;
631
632 OPEN c_imp_address_uses ;
633
634 LOOP
635 /* COMMIT if the buffer size is reached */
636 IF(nCommitCount>= COMMIT_SIZE) THEN
637 BEGIN
638 COMMIT;
639 nCommitCount:=0;
640 END;
641 ELSE
642 nCOmmitCount:=nCommitCount+1;
643 END IF;
644
645 sImpOrigSysRef:=NULL; sFSKaddress:=NULL;
646 FETCH c_imp_address_uses INTO sImpOrigSysRef, sFSKaddress, thisRowId;
647 sThisFSK1:=sImpOrigSysRef;
648 x_imp_address_uses_f:=c_imp_address_uses%FOUND;
649
650 EXIT WHEN NOT x_imp_address_uses_f;
651
652 /* Check the online database */
653 DECLARE
654 CURSOR c_onl_address_uses IS
655 SELECT address_use_id FROM CZ_ADDRESS_USES WHERE orig_sys_ref=sImpOrigSysRef;
656 BEGIN
657 OPEN c_onl_address_uses;
658 nOnladdressusesId:=NULL;
659 FETCH c_onl_address_uses INTO nOnladdressusesId;
660 x_onl_address_uses_f:=c_onl_address_uses%FOUND;
661 CLOSE c_onl_address_uses;
662 END;
663
664 DECLARE
665 CURSOR c_onl_address_addressid IS
666 SELECT address_id FROM CZ_ADDRESSES WHERE orig_sys_ref=sFSKaddress;
667 BEGIN
668 OPEN c_onl_address_addressid;
669 nOnladdressId:=NULL;
670 FETCH c_onl_address_addressid INTO nOnladdressId;
671 x_onl_address_addressid_f:=c_onl_address_addressid%FOUND;
672 CLOSE c_onl_address_addressid;
673 END;
674
675 IF(NOT x_onl_address_addressid_f OR (sFSKaddress IS NULL) OR
676 (sImpOrigSysRef IS NULL)) THEN
677 BEGIN
678 /* The record has no FSK or Surrogate key */
679 nFailed:=nFailed+1;
683 sRecStatus:='N5';
680 IF(sImpOrigSysRef IS NULL) THEN
681 sRecStatus:='N7';
682 ELSIF(NOT x_onl_address_addressid_f AND sFSKaddress IS NULL) THEN
684 ELSIF(NOT x_onl_address_addressid_f) THEN
685 sRecStatus:='F5';
686 END IF;
687 sDisposition:='R';
688 END;
689 ELSE
690 BEGIN
691 IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1) THEN
692 /* This is a duplicate record */
693 sRecStatus:='DUPL';
694 sDisposition:='R';
695 nDups:=nDups+1;
696 nFailed:=nFailed+1;
697 ELSE
698 BEGIN
699 sRecStatus:='PASS';
700 IF(x_onl_address_uses_f)THEN
701 /* Update */
702 sDisposition:='M';
703 nUpdateCount:=nUpdateCount+1;
704 ELSE
705 /*Insert */
706 sDisposition:='I';
707 nInsertCount:=nInsertCount+1;
708 nAllocateCounter:=nAllocateCounter+1;
709 IF(nAllocateCounter=nAllocateBlock)THEN
710 nAllocateCounter:=0;
711 SELECT CZ_ADDRESS_USES_S.NEXTVAL INTO nNextValue FROM DUAL;
712 END IF;
713 END IF;
714 END;
715 END IF;
716 END;
717 END IF;
718
719 UPDATE CZ_IMP_ADDRESS_USE SET
720 ADDRESS_USE_ID=DECODE(sDisposition,'R',ADDRESS_USE_ID,'I',nNextValue+nAllocateCounter,nOnladdressusesId),
721 ADDRESS_ID=DECODE(sDisposition,'R',ADDRESS_ID,nOnladdressid),
722 DISPOSITION=sDisposition,
723 REC_STATUS=sRecStatus
724 WHERE ROWID = thisRowId;
725
726 sLastFSK1:=sImpOrigSysref;
727
728 /* Return if MAX_ERR is reached */
729 IF(nFailed >= MAX_ERR) THEN
730 EXIT;
731 END IF;
732
733 sDisposition:=NULL; sRecStatus:=NULL;
734
735 END LOOP;
736 /* No more data */
737
738 CLOSE c_imp_address_uses;
739 COMMIT;
740
741 INSERTS:=nInsertCount;
742 UPDATES:=nUpdateCount;
743 FAILED:=nFailed;
744 DUPS:=nDups;
745
746 EXCEPTION
747 WHEN OTHERS THEN
748 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_KRS.KRS_ADDRESS_USES',11276);
749 END;
750 END KRS_address_uses;
751 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
752 PROCEDURE KRS_CUSTOMER_END_USER ( inRUN_ID IN PLS_INTEGER,
753 COMMIT_SIZE IN PLS_INTEGER,
754 MAX_ERR IN PLS_INTEGER,
755 INSERTS OUT NOCOPY PLS_INTEGER,
756 UPDATES OUT NOCOPY PLS_INTEGER,
757 FAILED OUT NOCOPY PLS_INTEGER,
758 DUPS OUT NOCOPY PLS_INTEGER,
759 inXFR_GROUP IN VARCHAR2
760 ) IS
761 BEGIN
762 DECLARE
763 CURSOR c_imp_CUSTOMERenduser(x_usesurr_enduser PLS_INTEGER) IS
764 SELECT FSK_CUSTOMER_1_1,DECODE(x_usesurr_enduser,0,FSK_ENDUSER_2_1,1,FSK_ENDUSER_2_EXT), ROWID FROM CZ_IMP_CUSTOMER_END_USER WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY 1,2, ROWID;
765 /* cursor's data found indicators */
766 sFSKCUSTOMER CZ_IMP_CUSTOMER_END_USER.CUSTOMER_ID%TYPE;
767 sFskEndUser CZ_IMP_CUSTOMER_END_USER.FSK_ENDUSER_2_1%TYPE;
768 nOnlCUSTOMERId CZ_IMP_CUSTOMER_END_USER.CUSTOMER_ID%TYPE;
769 nOnlEndUserId CZ_IMP_CUSTOMER_END_USER.END_USER_ID%TYPE;
770 sLastFSK1 CZ_IMP_CUSTOMER_END_USER.FSK_CUSTOMER_1_1%TYPE;
771 sThisFSK1 CZ_IMP_CUSTOMER_END_USER.FSK_CUSTOMER_1_1%TYPE;
772 sLastFSK2 CZ_IMP_CUSTOMER_END_USER.FSK_ENDUSER_2_1%TYPE;
773 sThisFSK2 CZ_IMP_CUSTOMER_END_USER.FSK_ENDUSER_2_1%TYPE;
774 sRecStatus CZ_IMP_CUSTOMER_END_USER.REC_STATUS%TYPE;
775 sDisposition CZ_IMP_CUSTOMER_END_USER.DISPOSITION%TYPE;
776 /* Column Vars */
777 x_imp_CUSTOMERenduser_f BOOLEAN:=FALSE;
778 x_onl_CUSTOMER_CUSTOMERid_f BOOLEAN:=FALSE;
779 x_onl_enduser_enduserid_f BOOLEAN:=FALSE;
780 x_onl_CUSTOMERenduser_f BOOLEAN:=FALSE;
781 x_error BOOLEAN:=FALSE;
782 p_onl_CUSTOMERenduser CHAR(1):='';
783 /* Internal vars */
784 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
785 nErrorCount PLS_INTEGER:=0; /*Error index */
786 nInsertCount PLS_INTEGER:=0; /*Inserts */
787 nUpdateCount PLS_INTEGER:=0; /*Updates */
791 CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_END_USERS',inXFR_GROUP);
788 nFailed PLS_INTEGER:=0; /*Failed records */
789 nDups PLS_INTEGER:=0; /*Duplicate records */
790 x_usesurr_enduser PLS_INTEGER:= /*Use surrogates */
792
793 thisRowId ROWID;
794
795 BEGIN
796
797 /* This type casting is necessary to use decode stmt */
798 OPEN c_imp_CUSTOMERenduser (x_usesurr_enduser) ;
799
800 LOOP
801 /* COMMIT if the buffer size is reached */
802 IF (nCommitCount>= COMMIT_SIZE) THEN
803 BEGIN
804 COMMIT;
805 nCommitCount:=0;
806 END;
807 ELSE
808 nCOmmitCount:=nCommitCount+1;
809 END IF;
810
811 sFSKCUSTOMER:=NULL; sFSKENDUSER:=NULL;
812 FETCH c_imp_CUSTOMERenduser INTO sFSKCUSTOMER, sFSKENDUSER, thisRowId ;
813 x_imp_CUSTOMERenduser_f:=c_imp_CUSTOMERenduser%FOUND;
814 sThisFSK1:=sFSKCUSTOMER;
815 sThisFSK2:=sFSKENDUSER;
816
817 EXIT WHEN NOT x_imp_CUSTOMERenduser_f;
818
819 DECLARE
820 CURSOR c_onl_CUSTOMER_CUSTOMERid IS
821 SELECT CUSTOMER_ID FROM CZ_CUSTOMERS WHERE ORIG_SYS_REF=sFSKCUSTOMER;
822 BEGIN
823 OPEN c_onl_CUSTOMER_CUSTOMERid;
824 nOnlCUSTOMERId:=NULL;
825 FETCH c_onl_CUSTOMER_CUSTOMERid INTO nOnlCUSTOMERId;
826 x_onl_CUSTOMER_CUSTOMERid_f:=c_onl_CUSTOMER_CUSTOMERid%FOUND;
827 CLOSE c_onl_CUSTOMER_CUSTOMERid;
828 END;
829 DECLARE
830 CURSOR c_onl_enduser_enduserid IS
831 SELECT END_USER_ID FROM CZ_END_USERS WHERE LOGIN_NAME=sFSKENDUSER;
832 BEGIN
833 OPEN c_onl_enduser_enduserid ;
834 nOnlEnduserId:=NULL;
835 FETCH c_onl_enduser_enduserid INTO nOnlenduserId;
836 x_onl_enduser_enduserid_f:=c_onl_enduser_enduserid%FOUND;
837 CLOSE c_onl_enduser_enduserid ;
838 END;
839 /* Check if this is an insert or update */
840 DECLARE
841 CURSOR c_onl_CUSTOMERenduser IS
842 SELECT 'X' FROM CZ_CUSTOMER_END_USERS WHERE CUSTOMER_ID=nOnlCUSTOMERId
843 AND END_USER_ID=nOnlEnduserId;
844 BEGIN
845 OPEN c_onl_CUSTOMERenduser ;
846 FETCH c_onl_CUSTOMERenduser INTO p_onl_CUSTOMERenduser;
847 x_onl_CUSTOMERenduser_f:=c_onl_CUSTOMERenduser%FOUND;
848 CLOSE c_onl_CUSTOMERenduser;
849 END;
850 IF(NOT x_onl_CUSTOMER_CUSTOMERid_f OR NOT x_onl_enduser_enduserid_f) THEN
851 BEGIN
852 /* The record has missing FSKs */
853 nFailed:=nFailed+1;
854 IF(NOT x_onl_CUSTOMER_CUSTOMERid_f AND sFSKCUSTOMER IS NULL) THEN
855 sRecStatus:='N13';
856 ELSIF(NOT x_onl_CUSTOMER_CUSTOMERid_f) THEN
857 sRecStatus:='F13';
858 ELSIF (NOT x_onl_enduser_enduserid_f AND x_usesurr_enduser=0 AND sFSKENDUSER IS NULL) THEN
859 sRecStatus:='N15';
860 ELSIF (NOT x_onl_enduser_enduserid_f AND x_usesurr_enduser=0) THEN
861 sRecStatus:='F15';
862 ELSIF(NOT x_onl_enduser_enduserid_f AND x_usesurr_enduser=1 AND sFSKENDUSER IS NULL) THEN
863 sRecStatus:='N14';
864 ELSIF(NOT x_onl_enduser_enduserid_f AND x_usesurr_enduser=1) THEN
865 sRecStatus:='F14';
866 END IF;
867 sDisposition:='R';
868 END;
869 ELSE
870 /* Insert or update */
871 BEGIN
872 IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 AND
873 sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2) THEN
874 /* This is a duplicate record */
875 sRecStatus:='DUPL';
876 sDisposition:='R';
877 nDups:=nDups+1;
878 nFailed:=nFailed+1;
879 ELSE
880 BEGIN
881 sRecStatus:='PASS';
885 nUpdateCount:=nUpdateCount+1;
882 IF( x_onl_CUSTOMERenduser_f)THEN
883 /* Update */
884 sDisposition:='M';
886 ELSE
887 /*Insert */
888 sDisposition:='I';
889 nInsertCount:=nInsertCount+1;
890 END IF;
891 END;
892 END IF;
893 END;
894 END IF;
895
896 UPDATE CZ_IMP_CUSTOMER_END_USER
897 SET CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,nOnlCUSTOMERId),
898 END_USER_ID=DECODE(sDISPOSITION,'R',END_USER_ID,nOnlendUserId),
899 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
900 WHERE ROWID = thisRowId;
901
902 sLastFSK1:=sFSKCUSTOMER;
903 sLastFSK2:=sFSKENDUSER;
904
905 /* Return if MAX_ERR is reached */
906 IF (nFailed >= MAX_ERR) THEN
907 EXIT;
908 END IF;
909 sDisposition:=NULL; sRecStatus:=NULL;
910 END LOOP;
911 CLOSE c_imp_CUSTOMERenduser;
912 COMMIT;
913
914 INSERTS:=nInsertCount;
915 UPDATES:=nUpdateCount;
916 FAILED:=nFailed;
917 DUPS:=nDups;
918 EXCEPTION
919 WHEN OTHERS THEN
920 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.KRS_CUSTOMER_END_USER',11276);
921 END;
922 END KRS_CUSTOMER_END_USER;
923
924 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
925 PROCEDURE KRS_END_USER (inRUN_ID IN PLS_INTEGER,
926 COMMIT_SIZE IN PLS_INTEGER,
927 MAX_ERR IN PLS_INTEGER,
928 INSERTS OUT NOCOPY PLS_INTEGER,
929 UPDATES OUT NOCOPY PLS_INTEGER,
930 FAILED OUT NOCOPY PLS_INTEGER,
931 DUPS OUT NOCOPY PLS_INTEGER,
932 inXFR_GROUP IN VARCHAR2
933 ) IS
934 BEGIN
935 DECLARE
936 CURSOR c_imp_enduser IS
937 SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_END_USER WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
938 ORDER BY 1, ROWID;
939
940 /* cursor's data found indicators */
941 sImpOrigSysRef CZ_IMP_END_USER.LOGIN_NAME%TYPE;
942 nUserId CZ_IMP_END_USER.END_USER_ID%TYPE;
943 sLastFSK CZ_IMP_END_USER.LOGIN_NAME%TYPE;
944 sThisFSK CZ_IMP_END_USER.LOGIN_NAME%TYPE;
945 sRecStatus CZ_IMP_END_USER.REC_STATUS%TYPE;
946 sDisposition CZ_IMP_END_USER.DISPOSITION%TYPE;
947 /* Column Vars */
948 x_imp_enduser_f BOOLEAN:=FALSE;
949 x_onl_enduser_userid_f BOOLEAN:=FALSE;
950 x_error BOOLEAN:=FALSE;
951 /* Internal vars */
952 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
953 nErrorCount PLS_INTEGER:=0; /*Error index */
954 nInsertCount PLS_INTEGER:=0; /*Inserts */
955 nUpdateCount PLS_INTEGER:=0; /*Updates */
956 nFailed PLS_INTEGER:=0; /*Failed records */
957 nDups PLS_INTEGER:=0; /*Duplicate records */
958 nAllocateBlock PLS_INTEGER:=1;
959 nAllocateCounter PLS_INTEGER;
960 nNextValue NUMBER;
961 sAutoCreateUsers CZ_DB_SETTINGS.VALUE%TYPE := 'NO';
962
963 thisRowId ROWID;
964
965 BEGIN
966
967 BEGIN
968 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
969 WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
970 EXCEPTION
971 WHEN OTHERS THEN
972 nAllocateBlock:=1;
973 END;
974 nAllocateCounter:=nAllocateBlock-1;
975 BEGIN
976 SELECT VALUE INTO sAutoCreateUsers FROM CZ_DB_SETTINGS
977 WHERE SETTING_ID='AUTOCREATE_IMPORTED_USERS' AND SECTION_NAME='ORAAPPS_INTEGRATE';
978 EXCEPTION
979 WHEN OTHERS THEN
980 sAutoCreateUsers:='NO';
981 END;
982
983 /* This type casting is necessary to use decode stmt */
984 OPEN c_imp_enduser;
985
986 LOOP
987 /* COMMIT if the buffer size is reached */
988 IF (nCommitCount>= COMMIT_SIZE) THEN
989 BEGIN
990 COMMIT;
991 nCommitCount:=0;
992 END;
993 ELSE
994 nCOmmitCount:=nCommitCount+1;
995 END IF;
999 sThisFSK:=sImpOrigSysRef;
996
997 sImpOrigSysRef:=NULL;
998 FETCH c_imp_enduser INTO sImpOrigSysRef, thisRowId;
1000 x_imp_enduser_f:=c_imp_enduser%FOUND;
1001
1002 EXIT WHEN NOT x_imp_enduser_f;
1003
1004 /* Check if this is an insert or update */
1005 DECLARE
1006 CURSOR c_onl_enduser_userid IS
1007 SELECT END_USER_ID FROM CZ_END_USERS WHERE ORIG_SYS_REF=sImpOrigSysRef;
1008 BEGIN
1009 OPEN c_onl_enduser_userid ;
1010 nUserId:=NULL;
1011 FETCH c_onl_enduser_userid INTO nUserId;
1012 x_onl_enduser_userid_f:=c_onl_enduser_userid%FOUND;
1013 CLOSE c_onl_enduser_userid;
1014 END;
1015 /* All foreign keys are resolved */
1016 IF(sImpOrigSysRef IS NULL)THEN
1017 BEGIN
1018 /* Error */
1019 nFailed:=nFailed+1;
1020 sRecStatus:='N43';
1021 sDisposition:='R';
1022 END;
1023 ELSIF(sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) THEN
1024 /* This is a duplicate record */
1025 sRecStatus:='DUPL';
1026 sDisposition:='R';
1027 nDups:=nDups+1;
1028 nFailed:=nFailed+1;
1029 ELSE
1030 BEGIN
1031 sRecStatus:='PASS';
1032 IF(x_onl_enduser_userid_f)THEN
1033 /* Update */
1034 sDisposition:='M';
1035 nUpdateCount:=nUpdateCount+1;
1036 ELSE
1037 /*Insert */
1038 sDisposition:='I';
1039 nInsertCount:=nInsertCount+1;
1040 nAllocateCounter:=nAllocateCounter+1;
1041 IF(nAllocateCounter=nAllocateBlock)THEN
1042 nAllocateCounter:=0;
1043 SELECT CZ_END_USERS_S.NEXTVAL INTO nNextValue FROM DUAL;
1044 END IF;
1045 END IF;
1046 END;
1047 END IF;
1048
1049 UPDATE CZ_IMP_END_USER SET
1050 END_USER_ID=DECODE(sDISPOSITION,'R',END_USER_ID,'I',nNextValue+nAllocateCounter,nUserId),
1051 DISPOSITION=sDisposition,REC_STATUS=sRecStatus
1052 WHERE ROWID = thisRowId;
1053 IF(sAutoCreateUsers='YES')THEN
1054 UPDATE CZ_IMP_END_USER SET
1055 LOGIN_NAME=DECODE(sDISPOSITION,'I',DECODE(LOGIN_NAME,NULL,USER||'_'||to_char(END_USER_ID),LOGIN_NAME),LOGIN_NAME)
1056 WHERE ROWID = thisRowId;
1057 END IF;
1058
1059 sLastFSK:=sImpOrigSysRef;
1060
1061 IF (nFailed >= MAX_ERR) THEN
1062 EXIT;
1063 END IF;
1064 sDisposition:=NULL; sRecStatus:=NULL;
1065 END LOOP;
1066 CLOSE c_imp_enduser;
1067 COMMIT;
1068
1069 INSERTS:=nInsertCount;
1070 UPDATES:=nUpdateCount;
1071 FAILED:=nFailed;
1072 DUPS:=nDups;
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_KRS.KRS_END_USER',11276);
1076 END;
1077 END KRS_END_USER;
1078 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1079 PROCEDURE KRS_END_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
1080 COMMIT_SIZE IN PLS_INTEGER,
1081 MAX_ERR IN PLS_INTEGER,
1082 INSERTS OUT NOCOPY PLS_INTEGER,
1083 UPDATES OUT NOCOPY PLS_INTEGER,
1084 FAILED OUT NOCOPY PLS_INTEGER,
1085 DUPS OUT NOCOPY PLS_INTEGER,
1086 inXFR_GROUP IN VARCHAR2
1087 ) IS
1088 BEGIN
1089 DECLARE
1090 CURSOR c_imp_endusergroup (x_usesurr_enduser PLS_INTEGER,
1091 x_usesurr_usergroup PLS_INTEGER) IS
1092 SELECT DECODE(x_usesurr_enduser,0,FSK_ENDUSER_1_1,1,FSK_ENDUSER_1_EXT),DECODE(x_usesurr_usergroup,0,FSK_USERGROUP_2_1,1,FSK_USERGROUP_2_EXT), ROWID
1093 FROM CZ_IMP_END_USER_GROUP
1094 WHERE REC_STATUS IS NULL AND RUN_ID=inRUN_ID ORDER BY 1,2,ROWID;
1095
1096 /* cursor's data found indicators */
1097 nOnlFSKUserId CZ_IMP_END_USER_GROUP.END_USER_ID%TYPE;
1098 nOnlFSKGroupId CZ_IMP_END_USER_GROUP.USER_GROUP_ID%TYPE;
1099 sFSKENDUSER CZ_IMP_END_USER_GROUP.FSK_ENDUSER_1_1%TYPE;
1100 sFSKUSERGROUP CZ_IMP_END_USER_GROUP.FSK_USERGROUP_2_1%TYPE;
1101 sLastFSK1 CZ_IMP_END_USER_GROUP.FSK_ENDUSER_1_1%TYPE;
1105 sRecStatus CZ_IMP_END_USER_GROUP.REC_STATUS%TYPE;
1102 sThisFSK1 CZ_IMP_END_USER_GROUP.FSK_ENDUSER_1_1%TYPE;
1103 sLastFSK2 CZ_IMP_END_USER_GROUP.FSK_USERGROUP_2_1%TYPE;
1104 sThisFSK2 CZ_IMP_END_USER_GROUP.FSK_USERGROUP_2_1%TYPE;
1106 sDisposition CZ_IMP_END_USER_GROUP.DISPOSITION%TYPE;
1107 /* Column Vars */
1108 x_imp_endusergroup_f BOOLEAN:=FALSE;
1109 x_onl_endusergroup_f BOOLEAN:=FALSE;
1110 x_onl_enduser_userid_f BOOLEAN:=FALSE;
1111 x_onl_usergroup_groupid_f BOOLEAN:=FALSE;
1112 x_error BOOLEAN:=FALSE;
1113 p_onl_endusergroup CHAR(1):='';
1114 /* Internal vars */
1115 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
1116 nErrorCount PLS_INTEGER:=0; /*Error index */
1117 nInsertCount PLS_INTEGER:=0; /*Inserts */
1118 nUpdateCount PLS_INTEGER:=0; /*Updates */
1119 nFailed PLS_INTEGER:=0; /*Failed records */
1120 nDups PLS_INTEGER:=0; /*Duplicate records */
1121 x_usesurr_enduser PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_END_USERS',inXFR_GROUP);
1122 x_usesurr_usergroup PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_USER_GROUPS',inXFR_GROUP);
1123
1124 thisRowId ROWID;
1125
1126 BEGIN
1127
1128 /* This type casting is necessary to use decode stmt */
1129 OPEN c_imp_endusergroup (x_usesurr_enduser, x_usesurr_usergroup);
1130
1131 LOOP
1132 /* COMMIT if the buffer size is reached */
1133 IF (nCommitCount>= COMMIT_SIZE) THEN
1134 BEGIN
1135 COMMIT;
1136 nCommitCount:=0;
1137 END;
1138 ELSE
1139 nCOmmitCount:=nCommitCount+1;
1140 END IF;
1141
1142 sFSKENDUSER:=NULL; sFSKUSERGROUP:=NULL;
1143 FETCH c_imp_endusergroup INTO sFSKENDUSER, sFSKUSERGROUP, thisRowId;
1144 sThisFSK1:=sFSKENDUSER;
1145 sThisFSK2:=sFSKUSERGROUP;
1146 x_imp_endusergroup_f:=c_imp_endusergroup%FOUND;
1147
1148 EXIT WHEN NOT x_imp_endusergroup_f;
1149
1150 DECLARE
1151 CURSOR c_onl_enduser_userid IS
1152 SELECT END_USER_ID FROM CZ_END_USERS WHERE ORIG_SYS_REF=sFSKENDUSER;
1153 BEGIN
1154 OPEN c_onl_enduser_userid;
1155 nOnlFSKUserId:=NULL;
1156 FETCH c_onl_enduser_userid INTO nOnlFSKUserId;
1157 x_onl_enduser_userid_f:=c_onl_enduser_userid%FOUND;
1158 CLOSE c_onl_enduser_userid;
1159 END;
1160 DECLARE
1161 CURSOR c_onl_usergroup_groupid IS
1162 SELECT USER_GROUP_ID FROM CZ_USER_GROUPS WHERE GROUP_NAME=sFSKUSERGROUP;
1163 BEGIN
1164 OPEN c_onl_usergroup_groupid ;
1165 nOnlFSKGroupId:=NULL;
1166 FETCH c_onl_usergroup_groupid INTO nOnlFSKGroupId;
1167 x_onl_usergroup_groupid_f:=c_onl_usergroup_groupid%FOUND;
1168 CLOSE c_onl_usergroup_groupid ;
1169 END;
1170 /* Check if this is an insert or update */
1171 DECLARE
1172 CURSOR c_onl_endusergroup IS
1173 SELECT 'X' FROM CZ_END_USER_GROUPS WHERE END_USER_ID=nOnlFSKUserId AND USER_GROUP_ID=nOnlFSKGroupId;
1174 BEGIN
1175 OPEN c_onl_endusergroup ;
1176 FETCH c_onl_endusergroup INTO p_onl_endusergroup;
1177 x_onl_endusergroup_f:=c_onl_endusergroup%FOUND;
1178 CLOSE c_onl_endusergroup;
1179 END;
1180 IF(NOT x_onl_enduser_userid_f OR NOT x_onl_usergroup_groupid_f) THEN
1181 BEGIN
1182 /* The record has missing FSKs */
1183 nFailed:=nFailed+1;
1184 IF(NOT x_onl_enduser_userid_f AND x_usesurr_enduser=1 AND sFSKENDUSER IS NULL) THEN
1185 sRecStatus:='N21';
1186 ELSIF(NOT x_onl_enduser_userid_f AND x_usesurr_enduser=1) THEN
1187 sRecStatus:='F21';
1188 ELSIF (NOT x_onl_enduser_userid_f AND x_usesurr_enduser=0 AND sFSKENDUSER IS NULL) THEN
1189 sRecStatus:='N20';
1190 ELSIF (NOT x_onl_enduser_userid_f AND x_usesurr_enduser=0) THEN
1191 sRecStatus:='F20';
1192 ELSIF(NOT x_onl_usergroup_groupid_f AND x_usesurr_usergroup=1 AND sFSKUSERGROUP IS NULL) THEN
1193 sRecStatus:='N23';
1194 ELSIF(NOT x_onl_usergroup_groupid_f AND x_usesurr_usergroup=1) THEN
1198 ELSIF(NOT x_onl_usergroup_groupid_f AND x_usesurr_usergroup=0) THEN
1195 sRecStatus:='F23';
1196 ELSIF(NOT x_onl_usergroup_groupid_f AND x_usesurr_usergroup=0 AND sFSKUSERGROUP IS NULL) THEN
1197 sRecStatus:='N22';
1199 sRecStatus:='F22';
1200 END IF;
1201 sDisposition:='R';
1202 END;
1203 ELSE
1204 /* Insert or update */
1205 BEGIN
1206 IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 AND
1207 sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2) THEN
1208 /* This is a duplicate record */
1209 sRecStatus:='DUPL';
1210 sDisposition:='R';
1211 nDups:=nDups+1;
1212 nFailed:=nFailed+1;
1213 ELSE
1214 BEGIN
1215 sRecStatus:='PASS';
1216 IF( x_onl_endusergroup_f)THEN
1217 /* Update */
1218 sDisposition:='M';
1219 nUpdateCount:=nUpdateCount+1;
1220 ELSE
1221 /*Insert */
1222 sDisposition:='I';
1223 nInsertCount:=nInsertCount+1;
1224 END IF;
1225 END;
1226 END IF;
1227 END;
1228 END IF;
1229
1230
1231 UPDATE CZ_IMP_END_USER_GROUP SET
1232 END_USER_ID=DECODE(sDISPOSITION,'R',END_USER_ID,nOnlFSKUserId),
1233 USER_GROUP_ID=DECODE(sDISPOSITION,'R',USER_GROUP_ID,nOnlFSKGroupId),
1234 DISPOSITION=sDisposition,
1235 REC_STATUS=sRecStatus
1236 WHERE ROWID = thisRowId;
1237
1238 sLastFSK1:=sFSKENDUSER;
1239 sLastFSK2:=sFSKUSERGROUP;
1240
1241 /* Return if MAX_ERR is reached */
1242 IF (nFailed >= MAX_ERR) THEN
1243 EXIT;
1244 END IF;
1245 sDisposition:=NULL; sRecStatus:=NULL;
1246 END LOOP;
1247 CLOSE c_imp_endusergroup;
1248 COMMIT;
1249
1250 INSERTS:=nInsertCount;
1251 UPDATES:=nUpdateCount;
1252 FAILED:=nFailed;
1253 DUPS:=nDups;
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_KRS.KRS_END_USER_GROUP',11276);
1257 END;
1258 END KRS_END_USER_GROUP ;
1259 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1260 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1261 PROCEDURE KRS_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
1262 COMMIT_SIZE IN PLS_INTEGER,
1263 MAX_ERR IN PLS_INTEGER,
1264 INSERTS OUT NOCOPY PLS_INTEGER,
1265 UPDATES OUT NOCOPY PLS_INTEGER,
1266 FAILED OUT NOCOPY PLS_INTEGER,
1267 DUPS OUT NOCOPY PLS_INTEGER,
1268 inXFR_GROUP IN VARCHAR2
1269 ) IS
1270 BEGIN
1271 DECLARE
1272 CURSOR c_imp_usergroup(x_usesurr_usergroup PLS_INTEGER) IS
1273 SELECT DECODE(x_usesurr_usergroup,0, GROUP_NAME,1,USER_STR03), ROWID FROM CZ_IMP_USER_GROUP WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY 1, ROWID;
1274
1275
1276 /* cursor's data found indicators */
1277 sImpName CZ_IMP_USER_GROUP.GROUP_NAME%TYPE;
1278 nGroupId CZ_IMP_USER_GROUP.USER_GROUP_ID%TYPE;
1279 sLastFSK CZ_IMP_USER_GROUP.GROUP_NAME%TYPE;
1280 sThisFSK CZ_IMP_USER_GROUP.GROUP_NAME%TYPE;
1281 sRecStatus CZ_IMP_USER_GROUP.REC_STATUS%TYPE;
1282 sDisposition CZ_IMP_USER_GROUP.DISPOSITION%TYPE;
1283 /* Column Vars */
1284 x_imp_usergroup_f BOOLEAN:=FALSE;
1285 x_onl_usergroup_groupid_f BOOLEAN:=FALSE;
1286 x_error BOOLEAN:=FALSE;
1287 /* Internal vars */
1288 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
1289 nErrorCount PLS_INTEGER:=0; /*Error index */
1290 nInsertCount PLS_INTEGER:=0; /*Inserts */
1294 x_usesurr_usergroup PLS_INTEGER:= /*Use surrogates */
1291 nUpdateCount PLS_INTEGER:=0; /*Updates */
1292 nFailed PLS_INTEGER:=0; /*Failed records */
1293 nDups PLS_INTEGER:=0; /*Duplicate records */
1295 CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_USER_GROUPS',inXFR_GROUP);
1296 nAllocateBlock PLS_INTEGER:=1;
1297 nAllocateCounter PLS_INTEGER;
1298 nNextValue NUMBER;
1299
1300 thisRowId ROWID;
1301
1302 BEGIN
1303
1304 BEGIN
1305 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
1306 WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 nAllocateBlock:=1;
1310 END;
1311 nAllocateCounter:=nAllocateBlock-1;
1312
1313 /* This type casting is necessary to use decode stmt */
1314 OPEN c_imp_usergroup (x_usesurr_usergroup) ;
1315
1316 LOOP
1317 /* COMMIT if the buffer size is reached */
1318 IF (nCommitCount>= COMMIT_SIZE) THEN
1319 BEGIN
1320 COMMIT;
1321 nCommitCount:=0;
1322 END;
1323 ELSE
1324 nCOmmitCount:=nCommitCount+1;
1325 END IF;
1326 sImpName:=NULL;
1327 FETCH c_imp_usergroup INTO sImpName, thisRowId;
1328 sThisFSK:=sImpName;
1329 x_imp_usergroup_f:=c_imp_usergroup%FOUND;
1330
1331 EXIT WHEN NOT x_imp_usergroup_f;
1332
1333 /* Check if this is an insert or update */
1334 DECLARE
1335 CURSOR c_onl_usergroup_groupid IS
1336 SELECT user_GROUP_ID FROM CZ_USER_GROUPS WHERE group_NAME=sImpName;
1337 BEGIN
1338 OPEN c_onl_usergroup_groupid ;
1339 nGroupId:=NULL;
1340 FETCH c_onl_usergroup_groupid INTO nGroupId;
1341 x_onl_usergroup_groupid_f:=c_onl_usergroup_groupid%FOUND;
1342 CLOSE c_onl_usergroup_groupid;
1343 END;
1344
1345 /* All foreign keys are resolved */
1346 IF(sImpName IS NULL) THEN
1347 BEGIN
1348 /* Error */
1349 nFailed:=nFailed+1;
1350 IF (x_usesurr_usergroup=1 ) THEN
1351 sRecStatus:='N25';
1352 ELSIF (x_usesurr_usergroup=0 ) THEN
1353 sRecStatus:='N2';
1354 END IF;
1355 sDisposition:='R';
1356 END;
1357 ELSIF(sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) THEN
1358 /* This is a duplicate record */
1359 sRecStatus:='DUPL';
1360 sDisposition:='R';
1361 nDups:=nDups+1;
1362 nFailed:=nFailed+1;
1363 ELSE
1364 BEGIN
1365 sRecStatus:='PASS';
1366 IF( x_onl_usergroup_groupid_f)THEN
1367 /* Update so save the record */
1368 sDisposition:='M';
1369 nUpdateCount:=nUpdateCount+1;
1370 ELSE
1371 /*Insert */
1372 sDisposition:='I';
1373 nInsertCount:=nInsertCount+1;
1374 nAllocateCounter:=nAllocateCounter+1;
1375 IF(nAllocateCounter=nAllocateBlock)THEN
1376 nAllocateCounter:=0;
1377 SELECT CZ_USER_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;
1378 END IF;
1379 END IF;
1380 END;
1381 END IF;
1382
1383 UPDATE CZ_IMP_USER_GROUP SET
1384 GROUP_NAME=DECODE(sDISPOSITION,'R',GROUP_NAME,sImpName),
1385 USER_GROUP_ID=DECODE(sDISPOSITION,'R',USER_GROUP_ID,'I',
1386 nNextValue+nAllocateCounter,nGroupId),
1387 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
1388 WHERE ROWID = thisRowId;
1389 sLastFSK:=sImpName;
1390
1391 IF (nFailed >= MAX_ERR) THEN
1392 EXIT;
1393 END IF;
1394 sDisposition:=NULL; sRecStatus:=NULL;
1395 END LOOP;
1396 CLOSE c_imp_usergroup;
1397 COMMIT;
1398
1399 INSERTS:=nInsertCount;
1400 UPDATES:=nUpdateCount;
1401 FAILED:=nFailed;
1402 DUPS:=nDups;
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_KRS.KRS_USER_GROUP',11276);
1406 END;
1408 END CZ_IMP_AC_KRS;
1407 END KRS_USER_GROUP;