[Home] [Help]
PACKAGE BODY: APPS.CZ_IMP_AC_XFR
Source
1 PACKAGE BODY CZ_IMP_AC_XFR AS
2 /* $Header: cziacxfb.pls 115.14 2002/11/27 16:59:28 askhacha ship $ */
3
4 PROCEDURE XFR_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 inXFR_GROUP IN VARCHAR2
11 ) IS
12 BEGIN
13 DECLARE CURSOR c_xfr_contact IS
14 SELECT * FROM CZ_IMP_CONTACT
15 WHERE CZ_IMP_CONTACT.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
16 x_xfr_contact_f BOOLEAN:=FALSE;
17 x_error BOOLEAN:=FALSE;
18 p_xfr_contact c_xfr_contact%ROWTYPE;
19 /* Internal vars */
20 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
21 nInsertCount PLS_INTEGER:=0; /*Inserts */
22 nUpdateCount PLS_INTEGER:=0; /*Updates */
23 nFailed PLS_INTEGER:=0; /*Failed records */
24 NOUPDATE_CUSTOMER_ID NUMBER;
25 NOUPDATE_ADDRESS_ID NUMBER;
26 NOUPDATE_SALUTATION NUMBER;
27 NOUPDATE_FIRSTNAME NUMBER;
28 NOUPDATE_MI NUMBER;
29 NOUPDATE_LASTNAME NUMBER;
30 NOUPDATE_SUFFIX NUMBER;
31 NOUPDATE_TITLE NUMBER;
32 NOUPDATE_PHONE NUMBER;
33 NOUPDATE_ALT_PHONE NUMBER;
34 NOUPDATE_FAX NUMBER;
35 NOUPDATE_PAGER NUMBER;
36 NOUPDATE_CELLULAR NUMBER;
37 NOUPDATE_EMAIL_ADDR NUMBER;
38 NOUPDATE_NOTE NUMBER;
39 NOUPDATE_DELETED_FLAG NUMBER;
40 NOUPDATE_USER_STR01 NUMBER;
41 NOUPDATE_USER_STR02 NUMBER;
42 NOUPDATE_USER_STR03 NUMBER;
43 NOUPDATE_USER_STR04 NUMBER;
44 NOUPDATE_USER_NUM01 NUMBER;
45 NOUPDATE_USER_NUM02 NUMBER;
46 NOUPDATE_USER_NUM03 NUMBER;
47 NOUPDATE_USER_NUM04 NUMBER;
48 NOUPDATE_CREATION_DATE NUMBER;
49 NOUPDATE_LAST_UPDATE_DATE NUMBER;
50 NOUPDATE_CREATED_BY NUMBER;
51 NOUPDATE_LAST_UPDATED_BY NUMBER;
52 NOUPDATE_SECURITY_MASK NUMBER;
53 NOUPDATE_CHECKOUT_USER NUMBER;
54 NOUPDATE_PRIMARY_ROLE NUMBER;
55 NOUPDATE_ORIG_SYS_REF NUMBER;
56
57 -- Make sure that the DataSet exists
58 BEGIN
59 -- Get the Update Flags for each column
60 NOUPDATE_CUSTOMER_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','CUSTOMER_ID',inXFR_GROUP);
61 NOUPDATE_ADDRESS_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','ADDRESS_ID',inXFR_GROUP);
62 NOUPDATE_SALUTATION := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','SALUTATION',inXFR_GROUP);
63 NOUPDATE_FIRSTNAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','FIRSTNAME',inXFR_GROUP);
64 NOUPDATE_MI := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','MI',inXFR_GROUP);
65 NOUPDATE_LASTNAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','LASTNAME',inXFR_GROUP);
66 NOUPDATE_SUFFIX := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','SUFFIX',inXFR_GROUP);
67 NOUPDATE_TITLE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','TITLE',inXFR_GROUP);
68 NOUPDATE_PHONE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','PHONE',inXFR_GROUP);
69 NOUPDATE_ALT_PHONE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','ALT_PHONE',inXFR_GROUP);
70 NOUPDATE_FAX := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','FAX',inXFR_GROUP);
71 NOUPDATE_PAGER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','PAGER',inXFR_GROUP);
72 NOUPDATE_CELLULAR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','CELLULAR',inXFR_GROUP);
73 NOUPDATE_EMAIL_ADDR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','EMAIL_ADDR',inXFR_GROUP);
74 NOUPDATE_NOTE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','NOTE',inXFR_GROUP);
75 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','DELETED_FLAG',inXFR_GROUP);
76 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_STR01',inXFR_GROUP);
77 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_STR02',inXFR_GROUP);
78 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_STR03',inXFR_GROUP);
79 NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_STR04',inXFR_GROUP);
80 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_NUM01',inXFR_GROUP);
81 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_NUM02',inXFR_GROUP);
82 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_NUM03',inXFR_GROUP);
83 NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','USER_NUM04',inXFR_GROUP);
84 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','CREATION_DATE',inXFR_GROUP);
85 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','LAST_UPDATE_DATE',inXFR_GROUP);
86 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','CREATED_BY',inXFR_GROUP);
87 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','LAST_UPDATED_BY',inXFR_GROUP);
88 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','SECURITY_MASK',inXFR_GROUP);
89 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','CHECKOUT_USER',inXFR_GROUP);
90 NOUPDATE_PRIMARY_ROLE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','PRIMARY_ROLE',inXFR_GROUP);
91 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CONTACTS','ORIG_SYS_REF',inXFR_GROUP);
92
93 OPEN c_xfr_contact;
94 LOOP
95 IF (nCommitCount>= COMMIT_SIZE) THEN
96 BEGIN
97 COMMIT;
98 nCommitCount:=0;
99 END;
100 ELSE
101 nCOmmitCount:=nCommitCount+1;
102 END IF;
103 FETCH c_xfr_contact INTO p_xfr_contact;
104 x_xfr_contact_f:=c_xfr_contact%FOUND;
105 EXIT WHEN (NOT x_xfr_contact_f Or nFailed >= Max_Err);
106 IF (p_xfr_contact.DISPOSITION = 'I') THEN
107 BEGIN
108 INSERT INTO CZ_CONTACTS (
109 CONTACT_ID,CUSTOMER_ID,ADDRESS_ID,SALUTATION,FIRSTNAME,MI,LASTNAME,SUFFIX,
110 TITLE,PHONE,ALT_PHONE,FAX,PAGER,CELLULAR,EMAIL_ADDR,NOTE,
111 DELETED_FLAG,
112 USER_STR01,USER_STR02,USER_STR03,USER_STR04,
113 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
114 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,
115 CONTACT_HANDLE,CHECKOUT_USER,PRIMARY_ROLE,ORIG_SYS_REF) VALUES(
116 p_xfr_contact.CONTACT_ID,p_xfr_contact.CUSTOMER_ID,p_xfr_contact.ADDRESS_ID,
117 p_xfr_contact.SALUTATION,p_xfr_contact.FIRSTNAME,
118 p_xfr_contact.MI,p_xfr_contact.LASTNAME,p_xfr_contact.SUFFIX,
119 p_xfr_contact.TITLE,p_xfr_contact.PHONE,p_xfr_contact.ALT_PHONE,
120 p_xfr_contact.FAX,p_xfr_contact.PAGER,p_xfr_contact.CELLULAR,
121 p_xfr_contact.EMAIL_ADDR,p_xfr_contact.NOTE,
122 p_xfr_contact.DELETED_FLAG,
123 p_xfr_contact.USER_STR01,p_xfr_contact.USER_STR02,
124 p_xfr_contact.USER_STR03,p_xfr_contact.USER_STR04,
125 p_xfr_contact.USER_NUM01,p_xfr_contact.USER_NUM02,
126 p_xfr_contact.USER_NUM03,p_xfr_contact.USER_NUM04,
127 SYSDATE,SYSDATE, 1, 1, NULL,p_xfr_contact.CONTACT_HANDLE,
128 p_xfr_contact.CHECKOUT_USER,p_xfr_contact.PRIMARY_ROLE,
129 p_xfr_contact.ORIG_SYS_REF);
130 nInsertCount:=nInsertCount+1;
131 BEGIN
132 UPDATE CZ_IMP_contact
133 SET REC_STATUS='OK'
134 WHERE CONTACT_ID=p_xfr_contact.CONTACT_ID AND RUN_ID=inRUN_ID;
135 END;
136 EXCEPTION
137 WHEN OTHERS THEN
138 nFailed:=nFailed +1;
139 BEGIN
140 UPDATE CZ_IMP_contact
141 SET REC_STATUS='ERR'
142 WHERE CONTACT_ID=p_xfr_contact.CONTACT_ID AND RUN_ID=inRUN_ID;
143 END;
144 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_CONTACT',11276);
145 END ;
146 ELSIF (p_xfr_contact.DISPOSITION = 'M') THEN
147 BEGIN
148 UPDATE CZ_CONTACTS SET
149 CUSTOMER_ID=DECODE(NOUPDATE_CUSTOMER_ID,0, p_xfr_contact.CUSTOMER_ID ,CUSTOMER_ID),
150 ADDRESS_ID=DECODE(NOUPDATE_ADDRESS_ID,0, p_xfr_contact.ADDRESS_ID ,ADDRESS_ID),
151 SALUTATION=DECODE(NOUPDATE_SALUTATION,0, p_xfr_contact.SALUTATION ,SALUTATION),
152 FIRSTNAME=DECODE(NOUPDATE_FIRSTNAME,0, p_xfr_contact.FIRSTNAME ,FIRSTNAME),
153 MI=DECODE(NOUPDATE_MI,0, p_xfr_contact.MI ,MI),
154 LASTNAME=DECODE(NOUPDATE_LASTNAME,0, p_xfr_contact.LASTNAME ,LASTNAME),
155 SUFFIX=DECODE(NOUPDATE_SUFFIX,0, p_xfr_contact.SUFFIX ,SUFFIX),
156 TITLE=DECODE(NOUPDATE_TITLE,0, p_xfr_contact.TITLE ,TITLE),
157 PHONE=DECODE(NOUPDATE_PHONE,0, p_xfr_contact.PHONE ,PHONE),
158 ALT_PHONE=DECODE(NOUPDATE_ALT_PHONE,0, p_xfr_contact.ALT_PHONE ,ALT_PHONE),
159 FAX=DECODE(NOUPDATE_FAX,0, p_xfr_contact.FAX ,FAX),
160 PAGER=DECODE(NOUPDATE_PAGER,0, p_xfr_contact.PAGER ,PAGER),
161 CELLULAR=DECODE(NOUPDATE_CELLULAR,0, p_xfr_contact.CELLULAR ,CELLULAR),
162 EMAIL_ADDR=DECODE(NOUPDATE_EMAIL_ADDR,0, p_xfr_contact.EMAIL_ADDR ,EMAIL_ADDR),
163 NOTE=DECODE(NOUPDATE_NOTE,0, p_xfr_contact.NOTE ,NOTE),
164 CHECKOUT_USER=DECODE(NOUPDATE_CHECKOUT_USER,0,p_xfr_contact.CHECKOUT_USER,CHECKOUT_USER),
165 PRIMARY_ROLE=DECODE(NOUPDATE_PRIMARY_ROLE,0,p_xfr_contact.PRIMARY_ROLE,PRIMARY_ROLE),
166 ORIG_SYS_REF=DECODE(NOUPDATE_ORIG_SYS_REF,0,p_xfr_contact.ORIG_SYS_REF,ORIG_SYS_REF),
167 DELETED_FLAG=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_contact.DELETED_FLAG ,DELETED_FLAG),
168 USER_NUM01=DECODE(NOUPDATE_USER_NUM01,0,p_xfr_contact.USER_NUM01,USER_NUM01),
169 USER_NUM02=DECODE(NOUPDATE_USER_NUM02,0,p_xfr_contact.USER_NUM02,USER_NUM02),
170 USER_NUM03=DECODE(NOUPDATE_USER_NUM03,0,p_xfr_contact.USER_NUM03,USER_NUM03),
171 USER_NUM04=DECODE(NOUPDATE_USER_NUM04,0,p_xfr_contact.USER_NUM04,USER_NUM04),
172 USER_STR01=DECODE(NOUPDATE_USER_STR01,0,p_xfr_contact.USER_STR01,USER_STR01),
173 USER_STR02=DECODE(NOUPDATE_USER_STR02,0,p_xfr_contact.USER_STR02,USER_STR02),
174 USER_STR03=DECODE(NOUPDATE_USER_STR03,0,p_xfr_contact.USER_STR03,USER_STR03),
175 USER_STR04=DECODE(NOUPDATE_USER_STR04,0,p_xfr_contact.USER_STR04,USER_STR04),
176 CREATION_DATE=DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
177 LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
178 CREATED_BY=DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
179 LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
180 SECURITY_MASK=DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK)
181 WHERE CONTACT_ID=p_xfr_contact.CONTACT_ID;
182 IF(SQL%NOTFOUND) THEN
183 nFailed:=nFailed+1;
184 ELSE
185 nUpdateCount:=nUpdateCount+1;
186 BEGIN
187 UPDATE CZ_IMP_contact
188 SET REC_STATUS='OK'
189 WHERE CONTACT_ID=p_xfr_contact.CONTACT_ID AND RUN_ID=inRUN_ID;
190 END;
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 nFailed:=nFailed +1;
195 BEGIN
196 UPDATE CZ_IMP_contact
197 SET REC_STATUS='ERR'
198 WHERE CONTACT_ID=p_xfr_contact.CONTACT_ID AND RUN_ID=inRUN_ID;
199 END;
200 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_CONTACT',11276);
201 END ;
202 END IF;
203 END LOOP;
204 CLOSE c_xfr_contact;
205 COMMIT;
206 INSERTS:=nInsertCount;
207 UPDATES:=nUpdateCount;
208 FAILED:=nFailed;
209 EXCEPTION
213 END XFR_CONTACT;
210 WHEN OTHERS THEN
211 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_CONTACT',11276);
212 END;
214 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
215 PROCEDURE XFR_CUSTOMER ( inRUN_ID IN PLS_INTEGER,
216 COMMIT_SIZE IN PLS_INTEGER,
217 MAX_ERR IN PLS_INTEGER,
218 INSERTS OUT NOCOPY PLS_INTEGER,
219 UPDATES OUT NOCOPY PLS_INTEGER,
220 FAILED OUT NOCOPY PLS_INTEGER,
221 inXFR_GROUP IN VARCHAR2
222 ) IS
223 BEGIN
224 DECLARE CURSOR c_xfr_CUSTOMER IS
225 SELECT *
226 FROM CZ_IMP_CUSTOMER
227 WHERE CZ_IMP_CUSTOMER.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
228 x_xfr_CUSTOMER_f BOOLEAN:=FALSE;
229 x_error BOOLEAN:=FALSE;
230 p_xfr_CUSTOMER c_xfr_CUSTOMER%ROWTYPE;
231 -- Internal vars --
232 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
233 nInsertCount PLS_INTEGER:=0; -- Inserts --
234 nUpdateCount PLS_INTEGER:=0; -- Updates --
235 nFailed PLS_INTEGER:=0; -- Failed records --
236 NOUPDATE_CUSTOMER_NAME NUMBER;
237 NOUPDATE_PARENT_ID NUMBER;
238 NOUPDATE_DIVISION NUMBER;
239 NOUPDATE_NOTE NUMBER;
240 NOUPDATE_DESC_TEXT NUMBER;
241 NOUPDATE_CUSTOMER_STATUS NUMBER;
242 NOUPDATE_ORIG_SYS_REF NUMBER;
243 NOUPDATE_DELETED_FLAG NUMBER;
244 NOUPDATE_USER_STR01 NUMBER;
245 NOUPDATE_USER_STR02 NUMBER;
246 NOUPDATE_USER_STR03 NUMBER;
247 NOUPDATE_USER_STR04 NUMBER;
248 NOUPDATE_USER_NUM01 NUMBER;
249 NOUPDATE_USER_NUM02 NUMBER;
250 NOUPDATE_USER_NUM03 NUMBER;
251 NOUPDATE_USER_NUM04 NUMBER;
252 NOUPDATE_CREATION_DATE NUMBER;
253 NOUPDATE_LAST_UPDATE_DATE NUMBER;
254 NOUPDATE_CREATED_BY NUMBER;
255 NOUPDATE_LAST_UPDATED_BY NUMBER;
256 NOUPDATE_SECURITY_MASK NUMBER;
257 NOUPDATE_CHECKOUT_USER NUMBER;
258 NOUPDATE_WAREHOUSE_ID NUMBER;
259 NOUPDATE_PRICE_LIST_ID NUMBER;
260
261 -- Make sure that the DataSet exists
262 BEGIN
263 -- Get the Update Flags for each column
264 NOUPDATE_CUSTOMER_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','CUSTOMER_NAME',inXFR_GROUP);
265 NOUPDATE_PARENT_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','PARENT_ID',inXFR_GROUP);
266 NOUPDATE_DIVISION := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','DIVISION',inXFR_GROUP);
267 NOUPDATE_NOTE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','NOTE',inXFR_GROUP);
268 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','DESC_TEXT',inXFR_GROUP);
269 NOUPDATE_CUSTOMER_STATUS := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','CUSTOMER_STATUS',inXFR_GROUP);
270 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','ORIG_SYS_REF',inXFR_GROUP);
271 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','DELETED_FLAG',inXFR_GROUP);
272 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_STR01',inXFR_GROUP);
273 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_STR02',inXFR_GROUP);
274 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_STR03',inXFR_GROUP);
275 NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_STR04',inXFR_GROUP);
276 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_NUM01',inXFR_GROUP);
277 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_NUM02',inXFR_GROUP);
278 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_NUM03',inXFR_GROUP);
279 NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','USER_NUM04',inXFR_GROUP);
280 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','CREATION_DATE',inXFR_GROUP);
281 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','LAST_UPDATE_DATE',inXFR_GROUP);
282 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','CREATED_BY',inXFR_GROUP);
286 NOUPDATE_WAREHOUSE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','WAREHOUSE_ID',inXFR_GROUP);
283 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','LAST_UPDATED_BY',inXFR_GROUP);
284 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','SECURITY_MASK',inXFR_GROUP);
285 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','CHECKOUT_USER',inXFR_GROUP);
287 NOUPDATE_PRICE_LIST_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMERS','PRICE_LIST_ID',inXFR_GROUP);
288
289 OPEN c_xfr_CUSTOMER;
290 LOOP
291 IF (nCommitCount>= COMMIT_SIZE) THEN
292 BEGIN
293 COMMIT;
294 nCommitCount:=0;
295 END;
296 ELSE
297 nCOmmitCount:=nCommitCount+1;
298 END IF;
299 FETCH c_xfr_CUSTOMER INTO p_xfr_CUSTOMER;
300 x_xfr_CUSTOMER_f:=c_xfr_CUSTOMER%FOUND;
301 EXIT WHEN (NOT x_xfr_CUSTOMER_f Or nFailed >= Max_Err);
302 IF (p_xfr_CUSTOMER.DISPOSITION = 'I') THEN
303 BEGIN
304 INSERT INTO CZ_CUSTOMERS (
305 ORIG_SYS_REF,CUSTOMER_ID,CUSTOMER_NAME,PARENT_ID,
306 DIVISION,NOTE,DESC_TEXT,CUSTOMER_STATUS,DELETED_FLAG,
307 USER_STR01,USER_STR02,USER_STR03,
308 USER_STR04,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
309 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,
310 CHECKOUT_USER,WAREHOUSE_ID/*,PRICE_LIST_ID*/) VALUES(
311 p_xfr_CUSTOMER.ORIG_SYS_REF,p_xfr_CUSTOMER.CUSTOMER_ID,
312 p_xfr_CUSTOMER.CUSTOMER_NAME,p_xfr_CUSTOMER.PARENT_ID,
313 p_xfr_CUSTOMER.DIVISION,p_xfr_CUSTOMER.NOTE,p_xfr_CUSTOMER.DESC_TEXT,
314 p_xfr_CUSTOMER.CUSTOMER_STATUS,p_xfr_CUSTOMER.DELETED_FLAG ,
315 p_xfr_CUSTOMER.USER_STR01,
316 p_xfr_CUSTOMER.USER_STR02,p_xfr_CUSTOMER.USER_STR03,p_xfr_CUSTOMER.USER_STR04,
317 p_xfr_CUSTOMER.USER_NUM01,p_xfr_CUSTOMER.USER_NUM02,
318 p_xfr_CUSTOMER.USER_NUM03,p_xfr_CUSTOMER.USER_NUM04,
319 SYSDATE,SYSDATE, 1, 1, NULL,
320 p_xfr_CUSTOMER.CHECKOUT_USER,p_xfr_CUSTOMER.WAREHOUSE_ID/*,
321 p_xfr_CUSTOMER.PRICE_LIST_ID*/);
322 nInsertCount:=nInsertCount+1;
323 BEGIN
324 UPDATE CZ_IMP_customer
325 SET REC_STATUS='OK'
326 WHERE CUSTOMER_ID=p_xfr_customer.CUSTOMER_ID AND RUN_ID=inRUN_ID;
327 END;
328 EXCEPTION
329 WHEN OTHERS THEN
330 nFailed:=nFailed +1;
331 BEGIN
332 UPDATE CZ_IMP_customer
333 SET REC_STATUS='ERR'
334 WHERE CUSTOMER_ID=p_xfr_customer.CUSTOMER_ID AND RUN_ID=inRUN_ID;
335 END;
336 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_CUSTOMER',11276);
337 END ;
338 ELSIF (p_xfr_CUSTOMER.DISPOSITION = 'M') THEN
339 BEGIN
340 UPDATE CZ_CUSTOMERS SET
341 CUSTOMER_NAME=DECODE(NOUPDATE_CUSTOMER_NAME,0, p_xfr_CUSTOMER.CUSTOMER_NAME ,CUSTOMER_NAME),
342 PARENT_ID=DECODE(NOUPDATE_PARENT_ID,0, p_xfr_CUSTOMER.PARENT_ID ,PARENT_ID),
343 DIVISION=DECODE(NOUPDATE_DIVISION,0, p_xfr_CUSTOMER.DIVISION ,DIVISION),
344 NOTE=DECODE(NOUPDATE_NOTE,0, p_xfr_CUSTOMER.NOTE ,NOTE),
345 DESC_TEXT=DECODE(NOUPDATE_DESC_TEXT,0, p_xfr_CUSTOMER.DESC_TEXT ,DESC_TEXT),
346 CUSTOMER_STATUS=DECODE(NOUPDATE_CUSTOMER_STATUS,0, p_xfr_CUSTOMER.CUSTOMER_STATUS ,CUSTOMER_STATUS),
347 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_CUSTOMER.DELETED_FLAG ,DELETED_FLAG),
351 USER_NUM04= DECODE(NOUPDATE_USER_NUM04, 0,p_xfr_CUSTOMER.USER_NUM04,USER_NUM04),
348 USER_NUM01= DECODE(NOUPDATE_USER_NUM01, 0,p_xfr_CUSTOMER.USER_NUM01,USER_NUM01),
349 USER_NUM02= DECODE(NOUPDATE_USER_NUM02, 0,p_xfr_CUSTOMER.USER_NUM02,USER_NUM02),
350 USER_NUM03= DECODE(NOUPDATE_USER_NUM03, 0,p_xfr_CUSTOMER.USER_NUM03,USER_NUM03),
352 USER_STR01= DECODE(NOUPDATE_USER_STR01, 0,p_xfr_CUSTOMER.USER_STR01,USER_STR01),
353 USER_STR02= DECODE(NOUPDATE_USER_STR02, 0,p_xfr_CUSTOMER.USER_STR02,USER_STR02),
354 USER_STR03= DECODE(NOUPDATE_USER_STR03, 0,p_xfr_CUSTOMER.USER_STR03,USER_STR03),
355 USER_STR04= DECODE(NOUPDATE_USER_STR04, 0,p_xfr_CUSTOMER.USER_STR04,USER_STR04),
356 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER, 0,p_xfr_CUSTOMER.CHECKOUT_USER,CHECKOUT_USER),
357 WAREHOUSE_ID= DECODE(NOUPDATE_WAREHOUSE_ID, 0,p_xfr_CUSTOMER.WAREHOUSE_ID,WAREHOUSE_ID),
358 /*PRICE_LIST_ID= DECODE(NOUPDATE_PRICE_LIST_ID, 0,p_xfr_CUSTOMER.PRICE_LIST_ID,PRICE_LIST_ID),*/
359 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
360 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
361 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
362 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
363 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK)
364 WHERE CUSTOMER_ID=p_xfr_CUSTOMER.CUSTOMER_ID;
365 IF(SQL%NOTFOUND) THEN
366 nFailed:=nFailed+1;
367 ELSE
368 nUpdateCount:=nUpdateCount+1;
369 BEGIN
370 UPDATE CZ_IMP_customer
371 SET REC_STATUS='OK'
372 WHERE CUSTOMER_ID=p_xfr_customer.CUSTOMER_ID AND RUN_ID=inRUN_ID;
373 END;
374 END IF;
375 EXCEPTION
376 WHEN OTHERS THEN
377 nFailed:=nFailed +1;
378 BEGIN
379 UPDATE CZ_IMP_customer
380 SET REC_STATUS='ERR'
381 WHERE CUSTOMER_ID=p_xfr_customer.CUSTOMER_ID AND RUN_ID=inRUN_ID;
382 END;
383 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_CUSTOMER',11276);
384 END ;
385 END IF;
386 END LOOP;
387 CLOSE c_xfr_CUSTOMER;
388 COMMIT;
389 INSERTS:=nInsertCount;
390 UPDATES:=nUpdateCount;
391 FAILED:=nFailed;
392 EXCEPTION
393 WHEN OTHERS THEN
394 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_CUSTOMER',11276);
395 END;
396 END XFR_CUSTOMER;
397 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
398 PROCEDURE XFR_ADDRESS ( inRUN_ID IN PLS_INTEGER,
399 COMMIT_SIZE IN PLS_INTEGER,
400 MAX_ERR IN PLS_INTEGER,
401 INSERTS OUT NOCOPY PLS_INTEGER,
402 UPDATES OUT NOCOPY PLS_INTEGER,
403 FAILED OUT NOCOPY PLS_INTEGER,
404 inXFR_GROUP IN VARCHAR2
405 ) IS
406 BEGIN
407 DECLARE CURSOR c_xfr_ADDRESS IS
408 SELECT * FROM CZ_IMP_ADDRESS
409 WHERE CZ_IMP_ADDRESS.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
410 x_xfr_ADDRESS_f BOOLEAN:=FALSE;
411 x_error BOOLEAN:=FALSE;
415 nInsertCount PLS_INTEGER:=0; -- Inserts --
412 p_xfr_ADDRESS c_xfr_ADDRESS%ROWTYPE;
413 -- Internal vars --
414 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
416 nUpdateCount PLS_INTEGER:=0; -- Updates --
417 nFailed PLS_INTEGER:=0; -- Failed records --
418
419 NOUPDATE_CUSTOMER_ID NUMBER;
420 NOUPDATE_COUNTRY NUMBER;
421 NOUPDATE_ADDR_LINE1 NUMBER;
422 NOUPDATE_ADDR_LINE2 NUMBER;
423 NOUPDATE_CITY NUMBER;
424 NOUPDATE_POSTAL_CODE NUMBER;
425 NOUPDATE_STATE NUMBER;
426 NOUPDATE_PROVINCE NUMBER;
427 NOUPDATE_COUNTY NUMBER;
428 NOUPDATE_BILL_TO_FLAG NUMBER;
429 NOUPDATE_SHIP_TO_FLAG NUMBER;
430 NOUPDATE_ORIG_SYS_REF NUMBER;
431 NOUPDATE_CREATION_DATE NUMBER;
432 NOUPDATE_LAST_UPDATE_DATE NUMBER;
433 NOUPDATE_CREATED_BY NUMBER;
434 NOUPDATE_LAST_UPDATED_BY NUMBER;
435 NOUPDATE_DELETED_FLAG NUMBER;
436
437 -- Make sure that the DataSet exists
438 BEGIN
439 -- Get the Update Flags for each column
440 NOUPDATE_CUSTOMER_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','CUSTOMER_ID',inXFR_GROUP);
441 NOUPDATE_COUNTRY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','COUNTRY',inXFR_GROUP);
442 NOUPDATE_ADDR_LINE1 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','ADDR_LINE1',inXFR_GROUP);
443 NOUPDATE_ADDR_LINE2 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','ADDR_LINE2',inXFR_GROUP);
444 NOUPDATE_CITY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','CITY',inXFR_GROUP);
445 NOUPDATE_POSTAL_CODE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','POSTAL_CODE',inXFR_GROUP);
446 NOUPDATE_STATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','STATE',inXFR_GROUP);
447 NOUPDATE_PROVINCE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','PROVINCE',inXFR_GROUP);
448 NOUPDATE_COUNTY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','COUNTY',inXFR_GROUP);
449 NOUPDATE_BILL_TO_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','BILL_TO_FLAG',inXFR_GROUP);
450 NOUPDATE_SHIP_TO_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','SHIP_TO_FLAG',inXFR_GROUP);
451 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','ORIG_SYS_REF',inXFR_GROUP);
452 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','CREATION_DATE',inXFR_GROUP);
453 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','LAST_UPDATE_DATE',inXFR_GROUP);
454 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','CREATED_BY',inXFR_GROUP);
455 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','LAST_UPDATED_BY',inXFR_GROUP);
456 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESSES','DELETED_FLAG',inXFR_GROUP);
457
458 OPEN c_xfr_ADDRESS;
459 LOOP
460 IF (nCommitCount>= COMMIT_SIZE) THEN
461 BEGIN
462 COMMIT;
463 nCommitCount:=0;
464 END;
465 ELSE
466 nCOmmitCount:=nCommitCount+1;
467 END IF;
468 FETCH c_xfr_ADDRESS INTO p_xfr_ADDRESS;
469 x_xfr_ADDRESS_f:=c_xfr_ADDRESS%FOUND;
470 EXIT WHEN (NOT x_xfr_ADDRESS_f Or nFailed >= Max_Err);
471
472 IF (p_xfr_ADDRESS.DISPOSITION = 'I') THEN
473 BEGIN
474 INSERT INTO CZ_ADDRESSES (ADDRESS_ID,CUSTOMER_ID, COUNTRY,ADDR_LINE1,
475 ADDR_LINE2,CITY,POSTAL_CODE,STATE,PROVINCE,
476 COUNTY,BILL_TO_FLAG,SHIP_TO_FLAG,ORIG_SYS_REF,
477 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,DELETED_FLAG
478 ) VALUES(
479 p_xfr_ADDRESS.ADDRESS_ID,p_xfr_ADDRESS.CUSTOMER_ID,
480 p_xfr_ADDRESS.COUNTRY,
481 p_xfr_ADDRESS.ADDR_LINE1,p_xfr_ADDRESS.ADDR_LINE2,
482 p_xfr_ADDRESS.CITY,p_xfr_ADDRESS.POSTAL_CODE,p_xfr_ADDRESS.STATE,
483 p_xfr_ADDRESS.PROVINCE,p_xfr_ADDRESS.COUNTY,
484 p_xfr_ADDRESS.BILL_TO_FLAG,p_xfr_ADDRESS.SHIP_TO_FLAG,
485 p_xfr_ADDRESS.ORIG_SYS_REF,
486 SYSDATE,SYSDATE,1,1,p_xfr_ADDRESS.DELETED_FLAG
487 );
488 nInsertCount:=nInsertCount+1;
489 BEGIN
493 END;
490 UPDATE CZ_IMP_address
491 SET REC_STATUS='OK'
492 WHERE ADDRESS_ID=p_xfr_address.ADDRESS_ID AND RUN_ID=inRUN_ID;
494 EXCEPTION
495 WHEN OTHERS THEN
496 nFailed:=nFailed +1;
497 BEGIN
498 UPDATE CZ_IMP_address
499 SET REC_STATUS='ERR'
500 WHERE ADDRESS_ID=p_xfr_address.ADDRESS_ID AND RUN_ID=inRUN_ID;
501 END;
502 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_ADDRESS',11276);
503 END ;
504 ELSIF (p_xfr_ADDRESS.DISPOSITION = 'M') THEN
505 BEGIN
506 UPDATE CZ_ADDRESSES SET
507 CUSTOMER_ID=DECODE(NOUPDATE_CUSTOMER_ID,0, p_xfr_ADDRESS.CUSTOMER_ID ,CUSTOMER_ID),
508 COUNTRY=DECODE(NOUPDATE_COUNTRY,0, p_xfr_ADDRESS.COUNTRY ,COUNTRY),
509 ADDR_LINE1=DECODE(NOUPDATE_ADDR_LINE1,0, p_xfr_ADDRESS.ADDR_LINE1 ,ADDR_LINE1),
510 ADDR_LINE2=DECODE(NOUPDATE_ADDR_LINE2,0, p_xfr_ADDRESS.ADDR_LINE2,ADDR_LINE2),
511 CITY=DECODE(NOUPDATE_CITY,0, p_xfr_ADDRESS.CITY ,CITY),
512 POSTAL_CODE=DECODE(NOUPDATE_POSTAL_CODE,0, p_xfr_ADDRESS.POSTAL_CODE ,POSTAL_CODE),
513 STATE=DECODE(NOUPDATE_STATE,0, p_xfr_ADDRESS.STATE,STATE),
514 PROVINCE=DECODE(NOUPDATE_PROVINCE,0, p_xfr_ADDRESS.PROVINCE ,PROVINCE),
515 COUNTY=DECODE(NOUPDATE_COUNTY,0, p_xfr_ADDRESS.COUNTY,COUNTY),
516 BILL_TO_FLAG=DECODE(NOUPDATE_BILL_TO_FLAG,0, p_xfr_ADDRESS.BILL_TO_FLAG,BILL_TO_FLAG),
517 SHIP_TO_FLAG=DECODE(NOUPDATE_SHIP_TO_FLAG,0, p_xfr_ADDRESS.SHIP_TO_FLAG,SHIP_TO_FLAG),
518 ORIG_SYS_REF=DECODE(NOUPDATE_ORIG_SYS_REF,0, p_xfr_ADDRESS.ORIG_SYS_REF,ORIG_SYS_REF),
519 CREATION_DATE=DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
520 LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
521 CREATED_BY=DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
522 LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
523 DELETED_FLAG=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_ADDRESS.DELETED_FLAG,DELETED_FLAG)
524 WHERE ADDRESS_ID=p_xfr_ADDRESS.ADDRESS_ID;
525 IF(SQL%NOTFOUND) THEN
526 nFailed:=nFailed+1;
527 ELSE
528 nUpdateCount:=nUpdateCount+1;
529 BEGIN
530 UPDATE CZ_IMP_address
531 SET REC_STATUS='OK'
532 WHERE ADDRESS_ID=p_xfr_address.ADDRESS_ID AND RUN_ID=inRUN_ID;
533 END;
534 END IF;
535 EXCEPTION
536 WHEN OTHERS THEN
537 nFailed:=nFailed +1;
538 BEGIN
539 UPDATE CZ_IMP_address
540 SET REC_STATUS='ERR'
541 WHERE ADDRESS_ID=p_xfr_address.ADDRESS_ID AND RUN_ID=inRUN_ID;
542 END;
543 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_ADDRESS',11276);
544 END ;
545 END IF;
546 END LOOP;
547 CLOSE c_xfr_ADDRESS;
548 COMMIT;
549 INSERTS:=nInsertCount;
550 UPDATES:=nUpdateCount;
551 FAILED:=nFailed;
552 EXCEPTION
553 WHEN OTHERS THEN
554 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_ADDRESS',11276);
555 END;
556 END XFR_ADDRESS;
557 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
558 PROCEDURE XFR_ADDRESS_USES(inRUN_ID IN PLS_INTEGER,
559 COMMIT_SIZE IN PLS_INTEGER,
560 MAX_ERR IN PLS_INTEGER,
561 INSERTS OUT NOCOPY PLS_INTEGER,
562 UPDATES OUT NOCOPY PLS_INTEGER,
563 FAILED OUT NOCOPY PLS_INTEGER,
564 inXFR_GROUP IN VARCHAR2
568 CURSOR c_xfr_address_uses IS
565 ) IS
566 BEGIN
567 DECLARE
569 SELECT * FROM CZ_IMP_ADDRESS_USE
570 WHERE Run_ID=inRUN_ID AND rec_status='PASS';
571
572 x_xfr_address_uses_f BOOLEAN:=FALSE;
573 x_error BOOLEAN:=FALSE;
574 p_xfr_address_uses c_xfr_address_uses%ROWTYPE;
575
576 -- Internal vars --
577 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
578 nInsertCount PLS_INTEGER:=0; -- Inserts --
579 nUpdateCount PLS_INTEGER:=0; -- Updates --
580 nFailed PLS_INTEGER:=0; -- Failed records --
581
582 NOUPDATE_SITE_USE_CODE NUMBER;
583 NOUPDATE_ADDRESS_ID NUMBER;
584 NOUPDATE_WAREHOUSE_ID NUMBER;
585 NOUPDATE_ORIG_SYS_REF NUMBER;
586 NOUPDATE_CREATION_DATE NUMBER;
587 NOUPDATE_LAST_UPDATE_DATE NUMBER;
588 NOUPDATE_CREATED_BY NUMBER;
589 NOUPDATE_LAST_UPDATED_BY NUMBER;
590 NOUPDATE_DELETED_FLAG NUMBER;
591
592 -- Make sure that the DataSet exists
593 BEGIN
594 -- Get the Update Flags for each column
595 NOUPDATE_SITE_USE_CODE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','SITE_USER_CODE',inXFR_GROUP);
596 NOUPDATE_ADDRESS_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','ADDRESS_ID',inXFR_GROUP);
597 NOUPDATE_WAREHOUSE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','WAREHOUSE_ID',inXFR_GROUP);
598 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','ORIG_SYS_REF',inXFR_GROUP);
599 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','CREATION_DATE',inXFR_GROUP);
600 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','LAST_UPDATE_DATE',inXFR_GROUP);
601 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','CREATED_BY',inXFR_GROUP);
602 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','LAST_UPDATED_BY',inXFR_GROUP);
603 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ADDRESS_USES','DELETED_FLAG',inXFR_GROUP);
604
605 OPEN c_xfr_address_uses;
606 LOOP
607 IF(nCommitCount>= COMMIT_SIZE) THEN
608 BEGIN
609 COMMIT;
610 nCommitCount:=0;
611 END;
612 ELSE
613 nCOmmitCount:=nCommitCount+1;
614 END IF;
615
616 FETCH c_xfr_address_uses INTO p_xfr_address_uses;
617 x_xfr_address_uses_f:=c_xfr_address_uses%FOUND;
618 EXIT WHEN (NOT x_xfr_address_uses_f OR nFailed >= Max_Err);
619
620 IF(p_xfr_address_uses.disposition = 'I') THEN
621 BEGIN
622 INSERT INTO cz_address_uses (address_use_id, address_id, site_user_code,
623 warehouse_id, orig_sys_ref, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY,
624 deleted_flag)
625 VALUES
626 (p_xfr_address_uses.address_use_id,
627 p_xfr_address_uses.address_id,
628 p_xfr_address_uses.site_use_code,
629 p_xfr_address_uses.warehouse_id,
630 p_xfr_address_uses.orig_sys_ref,
631 sysdate, sysdate, 1, 1,
632 p_xfr_address_uses.deleted_flag);
633 nInsertCount:=nInsertCount+1;
634 BEGIN
635 UPDATE CZ_IMP_address_use
636 SET REC_STATUS='OK'
637 WHERE ADDRESS_USE_ID=p_xfr_address_uses.ADDRESS_USE_ID AND RUN_ID=inRUN_ID;
638 END;
639 EXCEPTION
640 WHEN OTHERS THEN
641 nFailed:=nFailed +1;
642 BEGIN
643 UPDATE CZ_IMP_address_use
644 SET REC_STATUS='ERR'
645 WHERE ADDRESS_USE_ID=p_xfr_address_uses.ADDRESS_USE_ID AND RUN_ID=inRUN_ID;
646 END;
647 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_ADDRESS_USES',11276);
648 END ;
649 ELSIF(p_xfr_address_uses.disposition = 'M') THEN
650 BEGIN
651 UPDATE cz_address_uses SET
652 address_id=DECODE(NOUPDATE_ADDRESS_ID,0,p_xfr_address_uses.address_id ,address_id),
653 site_user_code=DECODE(NOUPDATE_SITE_USE_CODE,0,p_xfr_address_uses.site_use_code,site_user_code),
654 warehouse_id=DECODE(NOUPDATE_WAREHOUSE_ID,0,p_xfr_address_uses.warehouse_id,warehouse_id),
655 orig_sys_ref=DECODE(NOUPDATE_ORIG_SYS_REF,0,p_xfr_address_uses.orig_sys_ref,orig_sys_ref),
656 CREATION_DATE=DECODE(NOUPDATE_CREATION_DATE,0,sysdate,CREATION_DATE),
657 LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,sysdate,LAST_UPDATE_DATE),
658 CREATED_BY=DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
659 LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
660 deleted_flag=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_address_uses.deleted_flag,deleted_flag)
661 WHERE address_use_id=p_xfr_address_uses.address_use_id;
662
663 IF(SQL%NOTFOUND) THEN
664 nFailed:=nFailed+1;
665 ELSE
666 nUpdateCount:=nUpdateCount+1;
667 BEGIN
668 UPDATE CZ_IMP_address_use
669 SET REC_STATUS='OK'
670 WHERE ADDRESS_USE_ID=p_xfr_address_uses.ADDRESS_USE_ID AND RUN_ID=inRUN_ID;
671 END;
672 END IF;
673 EXCEPTION
674 WHEN OTHERS THEN
675 nFailed:=nFailed +1;
676 BEGIN
677 UPDATE CZ_IMP_address_use
681 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_ADDRESS_USES',11276);
678 SET REC_STATUS='ERR'
679 WHERE ADDRESS_USE_ID=p_xfr_address_uses.ADDRESS_USE_ID AND RUN_ID=inRUN_ID;
680 END;
682 END ;
683 END IF;
684 END LOOP;
685
686 CLOSE c_xfr_address_uses;
687 COMMIT;
688 INSERTS:=nInsertCount;
689 UPDATES:=nUpdateCount;
690 FAILED:=nFailed;
691 EXCEPTION
692 WHEN OTHERS THEN
693 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_ADDRESS_USES',11276);
694 END;
695 END XFR_ADDRESS_USES;
696 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
697 PROCEDURE XFR_CUSTOMER_END_USER ( inRUN_ID IN PLS_INTEGER,
698 COMMIT_SIZE IN PLS_INTEGER,
699 MAX_ERR IN PLS_INTEGER,
700 INSERTS OUT NOCOPY PLS_INTEGER,
701 UPDATES OUT NOCOPY PLS_INTEGER,
702 FAILED OUT NOCOPY PLS_INTEGER,
703 inXFR_GROUP IN VARCHAR2
704 ) IS
705 BEGIN
706 DECLARE CURSOR c_xfr_CUSTOMER_END_USER IS
707 SELECT * FROM CZ_IMP_CUSTOMER_END_USER
708 WHERE CZ_IMP_CUSTOMER_END_USER.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
709 x_xfr_CUSTOMER_END_USER_f BOOLEAN:=FALSE;
710 x_error BOOLEAN:=FALSE;
711 p_xfr_CUSTOMER_END_USER c_xfr_CUSTOMER_END_USER%ROWTYPE;
712 -- Internal vars --
713 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
714 nInsertCount PLS_INTEGER:=0; -- Inserts --
715 nUpdateCount PLS_INTEGER:=0; -- Updates --
716 nFailed PLS_INTEGER:=0; -- Failed records --
717 NOUPDATE_DELETED_FLAG NUMBER;
718 NOUPDATE_CREATION_DATE NUMBER;
719 NOUPDATE_LAST_UPDATE_DATE NUMBER;
720 NOUPDATE_CREATED_BY NUMBER;
721 NOUPDATE_LAST_UPDATED_BY NUMBER;
722 NOUPDATE_SECURITY_MASK NUMBER;
723 NOUPDATE_CHECKOUT_USER NUMBER;
724 -- Make sure that the DataSet exists
725 BEGIN
726 -- Get the Update Flags for each column
727 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMER_END_USERS','DELETED_FLAG',inXFR_GROUP);
728 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMER_END_USERS','CREATION_DATE',inXFR_GROUP);
729 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMER_END_USERS','LAST_UPDATE_DATE',inXFR_GROUP);
730 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMER_END_USERS','CREATED_BY',inXFR_GROUP);
731 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMER_END_USERS','LAST_UPDATED_BY',inXFR_GROUP);
732 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMER_END_USERS','SECURITY_MASK',inXFR_GROUP);
733 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_CUSTOMER_END_USERS','CHECKOUT_USER',inXFR_GROUP);
734
735 OPEN c_xfr_CUSTOMER_END_USER;
736 LOOP
737 IF (nCommitCount>= COMMIT_SIZE) THEN
738 BEGIN
739 COMMIT;
740 nCommitCount:=0;
741 END;
742 ELSE
743 nCOmmitCount:=nCommitCount+1;
744 END IF;
745 FETCH c_xfr_CUSTOMER_END_USER INTO p_xfr_CUSTOMER_END_USER;
746 x_xfr_CUSTOMER_END_USER_f:=c_xfr_CUSTOMER_END_USER%FOUND;
747 EXIT WHEN (NOT x_xfr_CUSTOMER_END_USER_f Or nFailed >= Max_Err);
748 IF (p_xfr_CUSTOMER_END_USER.DISPOSITION = 'I') THEN
749 BEGIN
750 INSERT INTO CZ_CUSTOMER_END_USERS (
751 CUSTOMER_ID,END_USER_ID, DELETED_FLAG,
752 CREATION_DATE,
753 LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER) VALUES
754 (p_xfr_CUSTOMER_END_USER.CUSTOMER_ID,p_xfr_CUSTOMER_END_USER.END_USER_ID,
755 p_xfr_CUSTOMER_END_USER.DELETED_FLAG ,
756 SYSDATE,SYSDATE, 1, 1, NULL,p_xfr_CUSTOMER_END_USER.CHECKOUT_USER);
757 nInsertCount:=nInsertCount+1;
758 BEGIN
759 UPDATE CZ_IMP_customer_end_user
760 SET REC_STATUS='OK'
764 EXCEPTION
761 WHERE CUSTOMER_ID=p_xfr_customer_end_user.CUSTOMER_ID
762 AND END_USER_ID=p_xfr_CUSTOMER_END_USER.END_USER_ID AND RUN_ID=inRUN_ID;
763 END;
765 WHEN OTHERS THEN
766 nFailed:=nFailed +1;
767 BEGIN
768 UPDATE CZ_IMP_customer_end_user
769 SET REC_STATUS='ERR'
770 WHERE CUSTOMER_ID=p_xfr_customer_end_user.CUSTOMER_ID
771 AND END_USER_ID=p_xfr_CUSTOMER_END_USER.END_USER_ID AND RUN_ID=inRUN_ID;
772 END;
773 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.XFR_CUSTOMER_END_USER',11276);
774 END ;
775 ELSIF (p_xfr_CUSTOMER_END_USER.DISPOSITION = 'M') THEN
776 BEGIN
777 UPDATE CZ_CUSTOMER_END_USERS SET
778 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_CUSTOMER_END_USER.DELETED_FLAG ,DELETED_FLAG),
779 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
780 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
781 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
782 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
783 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
784 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,NULL,CHECKOUT_USER)
785 WHERE CUSTOMER_ID=p_xfr_CUSTOMER_END_USER.CUSTOMER_ID AND
786 END_USER_ID=p_xfr_CUSTOMER_END_USER.END_USER_ID;
787
788 IF(SQL%NOTFOUND) THEN
789 nFailed:=nFailed+1;
790 ELSE
791 nUpdateCount:=nUpdateCount+1;
792 BEGIN
793 UPDATE CZ_IMP_customer_end_user
794 SET REC_STATUS='OK'
795 WHERE CUSTOMER_ID=p_xfr_customer_end_user.CUSTOMER_ID
796 AND END_USER_ID=p_xfr_CUSTOMER_END_USER.END_USER_ID AND RUN_ID=inRUN_ID;
797 END;
798 END IF;
799 EXCEPTION
800 WHEN OTHERS THEN
801 nFailed:=nFailed +1;
802 BEGIN
803 UPDATE CZ_IMP_customer_end_user
804 SET REC_STATUS='ERR'
805 WHERE CUSTOMER_ID=p_xfr_customer_end_user.CUSTOMER_ID
806 AND END_USER_ID=p_xfr_CUSTOMER_END_USER.END_USER_ID AND RUN_ID=inRUN_ID;
807 END;
808 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.XFR_CUSTOMER_END_USER',11276);
809 END ;
810 END IF;
811 END LOOP;
812 CLOSE c_xfr_CUSTOMER_END_USER;
813 COMMIT;
814 INSERTS:=nInsertCount;
815 UPDATES:=nUpdateCount;
816 FAILED:=nFailed;
817 EXCEPTION
818 WHEN OTHERS THEN
819 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_AC.XFR_CUSTOMER_END_USER',11276);
820 END;
821 END XFR_CUSTOMER_END_USER;
822 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
823 PROCEDURE XFR_END_USER ( inRUN_ID IN PLS_INTEGER,
824 COMMIT_SIZE IN PLS_INTEGER,
825 MAX_ERR IN PLS_INTEGER,
826 INSERTS OUT NOCOPY PLS_INTEGER,
827 UPDATES OUT NOCOPY PLS_INTEGER,
828 FAILED OUT NOCOPY PLS_INTEGER,
829 inXFR_GROUP IN VARCHAR2
833 SELECT *
830 ) IS
831 BEGIN
832 DECLARE CURSOR c_xfr_enduser IS
834 FROM CZ_IMP_END_USER
835 WHERE CZ_IMP_END_USER.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
836 x_xfr_enduser_f BOOLEAN:=FALSE;
837 x_error BOOLEAN:=FALSE;
838 p_xfr_enduser c_xfr_enduser%ROWTYPE;
839 -- Internal vars --
840 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
841 nInsertCount PLS_INTEGER:=0; -- Inserts --
842 nUpdateCount PLS_INTEGER:=0; -- Updates --
843 nFailed PLS_INTEGER:=0; -- Failed records --
844 NOUPDATE_TITLE NUMBER;
845 NOUPDATE_LOGIN_NAME NUMBER;
846 NOUPDATE_LASTNAME NUMBER;
847 NOUPDATE_FIRSTNAME NUMBER;
848 NOUPDATE_MI NUMBER;
849 NOUPDATE_ALLOWABLE_DISCOUNT NUMBER;
850 NOUPDATE_DESC_TEXT NUMBER;
851 NOUPDATE_ADDR_LINE1 NUMBER;
852 NOUPDATE_ADDR_LINE2 NUMBER;
853 NOUPDATE_CITY NUMBER;
854 NOUPDATE_STATE NUMBER;
855 NOUPDATE_PROVINCE NUMBER;
856 NOUPDATE_COUNTY NUMBER;
857 NOUPDATE_ZIP NUMBER;
858 NOUPDATE_COUNTRY NUMBER;
859 NOUPDATE_PHONE NUMBER;
860 NOUPDATE_FAX NUMBER;
861 NOUPDATE_PAGER NUMBER;
862 NOUPDATE_CELLULAR NUMBER;
863 NOUPDATE_EMAIL_ADDR NUMBER;
864 NOUPDATE_DELETED_FLAG NUMBER;
865 NOUPDATE_USER_STR01 NUMBER;
866 NOUPDATE_USER_STR02 NUMBER;
867 NOUPDATE_USER_STR03 NUMBER;
868 NOUPDATE_USER_STR04 NUMBER;
869 NOUPDATE_USER_NUM01 NUMBER;
870 NOUPDATE_USER_NUM02 NUMBER;
871 NOUPDATE_USER_NUM03 NUMBER;
872 NOUPDATE_USER_NUM04 NUMBER;
873 NOUPDATE_CREATION_DATE NUMBER;
874 NOUPDATE_LAST_UPDATE_DATE NUMBER;
875 NOUPDATE_CREATED_BY NUMBER;
876 NOUPDATE_LAST_UPDATED_BY NUMBER;
877 NOUPDATE_SECURITY_MASK NUMBER;
878 NOUPDATE_CHECKOUT_USER NUMBER;
879 NOUPDATE_END_USER_ORG_ID NUMBER;
880 NOUPDATE_ORIG_SYS_REF NUMBER;
881 NOUPDATE_NAME NUMBER;
882
883 -- Make sure that the DataSet exists
884 BEGIN
885 -- Get the Update Flags for each column
886 NOUPDATE_TITLE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','TITLE',inXFR_GROUP);
887 NOUPDATE_LOGIN_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','LOGIN_NAME',inXFR_GROUP);
888 NOUPDATE_LASTNAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','LASTNAME',inXFR_GROUP);
889 NOUPDATE_FIRSTNAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','FIRSTNAME',inXFR_GROUP);
890 NOUPDATE_MI := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','MI',inXFR_GROUP);
891 NOUPDATE_ALLOWABLE_DISCOUNT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','ALLOWABLE_DISCOUNT',inXFR_GROUP);
892 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','DESC_TEXT',inXFR_GROUP);
893 NOUPDATE_ADDR_LINE1 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','ADDR_LINE1',inXFR_GROUP);
894 NOUPDATE_ADDR_LINE2 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','ADDR_LINE2',inXFR_GROUP);
895 NOUPDATE_CITY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','CITY',inXFR_GROUP);
896 NOUPDATE_STATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','STATE',inXFR_GROUP);
897 NOUPDATE_PROVINCE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','PROVINCE',inXFR_GROUP);
898 NOUPDATE_COUNTY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','COUNTY',inXFR_GROUP);
899 NOUPDATE_ZIP := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','ZIP',inXFR_GROUP);
900 NOUPDATE_COUNTRY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','COUNTRY',inXFR_GROUP);
901 NOUPDATE_PHONE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','PHONE',inXFR_GROUP);
902 NOUPDATE_FAX := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','FAX',inXFR_GROUP);
903 NOUPDATE_PAGER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','PAGER',inXFR_GROUP);
904 NOUPDATE_CELLULAR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','CELLULAR',inXFR_GROUP);
908 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','USER_STR02',inXFR_GROUP);
905 NOUPDATE_EMAIL_ADDR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','EMAIL_ADDR',inXFR_GROUP);
906 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','DELETED_FLAG',inXFR_GROUP);
907 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','USER_STR01',inXFR_GROUP);
909 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','USER_STR03',inXFR_GROUP);
910 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','USER_NUM01',inXFR_GROUP);
911 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','USER_NUM02',inXFR_GROUP);
912 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','USER_NUM03',inXFR_GROUP);
913 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','CREATION_DATE',inXFR_GROUP);
914 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','LAST_UPDATE_DATE',inXFR_GROUP);
915 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','CREATED_BY',inXFR_GROUP);
916 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','LAST_UPDATED_BY',inXFR_GROUP);
917 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','SECURITY_MASK',inXFR_GROUP);
918 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','CHECKOUT_USER',inXFR_GROUP);
919 NOUPDATE_END_USER_ORG_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','END_USER_ORG_ID',inXFR_GROUP);
920 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','ORIG_SYS_REF',inXFR_GROUP);
921 NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USERS','NAME',inXFR_GROUP);
922
923 OPEN c_xfr_enduser;
924 LOOP
925 IF (nCommitCount>= COMMIT_SIZE) THEN
926 BEGIN
927 COMMIT;
928 nCommitCount:=0;
929 END;
930 ELSE
931 nCOmmitCount:=nCommitCount+1;
932 END IF;
933 FETCH c_xfr_enduser INTO p_xfr_enduser;
934 x_xfr_enduser_f:=c_xfr_enduser%FOUND;
935 EXIT WHEN (NOT x_xfr_enduser_f Or nFailed >= Max_Err);
936 IF (p_xfr_enduser.DISPOSITION = 'I') THEN
937 BEGIN
938 INSERT INTO CZ_END_USERS (
939 END_USER_ID,TITLE,LOGIN_NAME,LASTNAME,FIRSTNAME,MI,
940 ALLOWABLE_DISCOUNT,DESC_TEXT,ADDR_LINE1,ADDR_LINE2,
941 CITY,STATE,PROVINCE,COUNTY,ZIP,COUNTRY,PHONE,FAX,PAGER,
942 CELLULAR,EMAIL_ADDR,
943 DELETED_FLAG,
944 USER_STR01,USER_STR02,USER_STR03,USER_STR04,
945 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
946 CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,
947 SECURITY_MASK,CHECKOUT_USER,
948 END_USER_ORG_ID,ORIG_SYS_REF,NAME) VALUES
949 (p_xfr_enduser.END_USER_ID,p_xfr_enduser.TITLE,
950 p_xfr_enduser.LOGIN_NAME,p_xfr_enduser.LASTNAME,
951 p_xfr_enduser.FIRSTNAME,p_xfr_enduser.MI,
952 p_xfr_enduser.ALLOWABLE_DISCOUNT,p_xfr_enduser.DESC_TEXT,
953 p_xfr_enduser.ADDR_LINE1,p_xfr_enduser.ADDR_LINE2,
954 p_xfr_enduser.CITY,p_xfr_enduser.STATE,p_xfr_enduser.PROVINCE,
955 p_xfr_enduser.COUNTY,p_xfr_enduser.ZIP,p_xfr_enduser.COUNTRY,
956 p_xfr_enduser.PHONE,p_xfr_enduser.FAX,p_xfr_enduser.PAGER,
957 p_xfr_enduser.CELLULAR, p_xfr_enduser.EMAIL_ADDR,
958 p_xfr_enduser.DELETED_FLAG,
959 p_xfr_enduser.USER_STR01,p_xfr_enduser.USER_STR02,
960 p_xfr_enduser.USER_STR03,p_xfr_enduser.USER_STR04,
961 p_xfr_enduser.USER_NUM01,p_xfr_enduser.USER_NUM02,
962 p_xfr_enduser.USER_NUM03,p_xfr_enduser.USER_NUM04,
963 SYSDATE,SYSDATE, 1, 1, NULL,p_xfr_enduser.CHECKOUT_USER,
964 p_xfr_enduser.END_USER_ORG_ID,
965 p_xfr_enduser.ORIG_SYS_REF,
966 p_xfr_enduser.NAME);
967 nInsertCount:=nInsertCount+1;
968 BEGIN
969 UPDATE CZ_IMP_end_user
970 SET REC_STATUS='OK'
974 EXCEPTION
971 WHERE END_USER_ID=p_xfr_enduser.END_USER_ID AND RUN_ID=inRUN_ID;
972 END;
973
975 WHEN OTHERS THEN
976 nFailed:=nFailed +1;
977 BEGIN
978 UPDATE CZ_IMP_end_user
979 SET REC_STATUS='ERR'
980 WHERE END_USER_ID=p_xfr_enduser.END_USER_ID AND RUN_ID=inRUN_ID;
981 END;
982 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_END_USER',11276);
983 END ;
984 ELSIF (p_xfr_enduser.DISPOSITION = 'M') THEN
985 BEGIN
986 UPDATE CZ_END_USERS SET
987 TITLE=DECODE(NOUPDATE_TITLE,0,p_xfr_enduser.TITLE,TITLE),
988 LOGIN_NAME=DECODE(NOUPDATE_LOGIN_NAME,0,p_xfr_enduser.LOGIN_NAME,LOGIN_NAME),
989 LASTNAME=DECODE(NOUPDATE_LASTNAME,0,p_xfr_enduser.LASTNAME,LASTNAME),
990 FIRSTNAME=DECODE(NOUPDATE_FIRSTNAME,0,p_xfr_enduser.FIRSTNAME,FIRSTNAME),
991 MI=DECODE(NOUPDATE_MI,0,p_xfr_enduser.MI,MI),
992 ALLOWABLE_DISCOUNT=DECODE(NOUPDATE_ALLOWABLE_DISCOUNT,0,p_xfr_enduser.ALLOWABLE_DISCOUNT,ALLOWABLE_DISCOUNT),
993 DESC_TEXT=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_enduser.DESC_TEXT,DESC_TEXT),
994 ADDR_LINE1=DECODE(NOUPDATE_ADDR_LINE1,0,p_xfr_enduser.ADDR_LINE1,ADDR_LINE1),
995 ADDR_LINE2=DECODE(NOUPDATE_ADDR_LINE2,0,p_xfr_enduser.ADDR_LINE2,ADDR_LINE2),
996 CITY =DECODE(NOUPDATE_CITY,0,p_xfr_enduser.CITY,CITY),
997 STATE=DECODE(NOUPDATE_STATE,0,p_xfr_enduser.STATE,STATE),
998 PROVINCE=DECODE(NOUPDATE_PROVINCE,0,p_xfr_enduser.PROVINCE,PROVINCE),
999 COUNTY =DECODE(NOUPDATE_COUNTY,0,p_xfr_enduser.COUNTY,COUNTY),
1000 ZIP=DECODE(NOUPDATE_ZIP,0,p_xfr_enduser.ZIP,ZIP),
1001 COUNTRY=DECODE(NOUPDATE_COUNTRY,0,p_xfr_enduser.COUNTRY,COUNTRY),
1002 PHONE=DECODE(NOUPDATE_PHONE,0,p_xfr_enduser.PHONE,PHONE),
1003 FAX=DECODE(NOUPDATE_FAX,0,p_xfr_enduser.FAX,FAX),
1004 PAGER =DECODE(NOUPDATE_PAGER,0,p_xfr_enduser.PAGER,PAGER),
1005 CELLULAR =DECODE(NOUPDATE_CELLULAR,0,p_xfr_enduser.CELLULAR,CELLULAR),
1006 EMAIL_ADDR =DECODE(NOUPDATE_EMAIL_ADDR,0,p_xfr_enduser.EMAIL_ADDR,EMAIL_ADDR),
1007 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_enduser.DELETED_FLAG ,DELETED_FLAG),
1008 USER_NUM01= DECODE(NOUPDATE_USER_NUM01, 0,p_xfr_enduser.USER_NUM01,USER_NUM01),
1009 USER_NUM02= DECODE(NOUPDATE_USER_NUM02, 0,p_xfr_enduser.USER_NUM02,USER_NUM02),
1010 USER_NUM03= DECODE(NOUPDATE_USER_NUM03, 0,p_xfr_enduser.USER_NUM03,USER_NUM03),
1011 USER_NUM04= DECODE(NOUPDATE_USER_NUM04, 0,p_xfr_enduser.USER_NUM04,USER_NUM04),
1012 USER_STR01= DECODE(NOUPDATE_USER_STR01, 0,p_xfr_enduser.USER_STR01,USER_STR01),
1013 USER_STR02= DECODE(NOUPDATE_USER_STR02, 0,p_xfr_enduser.USER_STR02,USER_STR02),
1014 USER_STR03= DECODE(NOUPDATE_USER_STR03, 0,p_xfr_enduser.USER_STR03,USER_STR03),
1015 USER_STR04= DECODE(NOUPDATE_USER_STR04, 0,p_xfr_enduser.USER_STR04,USER_STR04),
1016 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
1020 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
1017 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
1018 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
1019 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
1021 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,NULL,CHECKOUT_USER),
1022 END_USER_ORG_ID= DECODE(NOUPDATE_END_USER_ORG_ID,0,p_xfr_enduser.END_USER_ORG_ID,END_USER_ORG_ID),
1023 ORIG_SYS_REF= DECODE(NOUPDATE_ORIG_SYS_REF,0,p_xfr_enduser.ORIG_SYS_REF,ORIG_SYS_REF),
1024 NAME= DECODE(NOUPDATE_NAME,0,p_xfr_enduser.NAME,NAME)
1025 WHERE END_USER_ID=p_xfr_enduser.END_USER_ID;
1026 IF(SQL%NOTFOUND) THEN
1027 nFailed:=nFailed+1;
1028 ELSE
1029 nUpdateCount:=nUpdateCount+1;
1030 BEGIN
1031 UPDATE CZ_IMP_end_user
1032 SET REC_STATUS='OK'
1033 WHERE END_USER_ID=p_xfr_enduser.END_USER_ID AND RUN_ID=inRUN_ID;
1034 END;
1035 END IF;
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 nFailed:=nFailed +1;
1039 BEGIN
1040 UPDATE CZ_IMP_end_user
1041 SET REC_STATUS='ERR'
1042 WHERE END_USER_ID=p_xfr_enduser.END_USER_ID AND RUN_ID=inRUN_ID;
1043 END;
1044 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_END_USER',11276);
1045 END ;
1046 END IF;
1047 END LOOP;
1048 CLOSE c_xfr_enduser;
1049 COMMIT;
1050 INSERTS:=nInsertCount;
1051 UPDATES:=nUpdateCount;
1052 FAILED:=nFailed;
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_END_USER',11276);
1056 END;
1057 END XFR_END_USER;
1058 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1059 PROCEDURE XFR_END_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
1060 COMMIT_SIZE IN PLS_INTEGER,
1061 MAX_ERR IN PLS_INTEGER,
1062 INSERTS OUT NOCOPY PLS_INTEGER,
1063 UPDATES OUT NOCOPY PLS_INTEGER,
1064 FAILED OUT NOCOPY PLS_INTEGER,
1065 inXFR_GROUP IN VARCHAR2
1066 ) IS
1067 BEGIN
1068 DECLARE CURSOR c_xfr_endusergroup IS
1069 SELECT *
1070 FROM CZ_IMP_END_USER_GROUP WHERE CZ_IMP_END_USER_GROUP.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
1071 x_xfr_endusergroup_f BOOLEAN:=FALSE;
1072 x_error BOOLEAN:=FALSE;
1073 p_xfr_endusergroup c_xfr_endusergroup%ROWTYPE;
1074 -- Internal vars --
1075 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
1076 nInsertCount PLS_INTEGER:=0; -- Inserts --
1077 nUpdateCount PLS_INTEGER:=0; -- Updates --
1078 nFailed PLS_INTEGER:=0; -- Failed records --
1079 NOUPDATE_DATE_ADDED_USER NUMBER;
1080 NOUPDATE_USER_ADDEDBY NUMBER;
1081 NOUPDATE_GROUP_PRIORITY NUMBER;
1082 NOUPDATE_DELETED_FLAG NUMBER;
1083 NOUPDATE_CREATION_DATE NUMBER;
1084 NOUPDATE_LAST_UPDATE_DATE NUMBER;
1085 NOUPDATE_CREATED_BY NUMBER;
1086 NOUPDATE_LAST_UPDATED_BY NUMBER;
1087 NOUPDATE_SECURITY_MASK NUMBER;
1088 NOUPDATE_CHECKOUT_USER NUMBER;
1089
1090 -- Make sure that the DataSet exists
1091 BEGIN
1092 -- Get the Update Flags for each column
1096 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','DELETED_FLAG',inXFR_GROUP);
1093 NOUPDATE_DATE_ADDED_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','DATE_ADDED_USER',inXFR_GROUP);
1094 NOUPDATE_USER_ADDEDBY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','USER_ADDEDBY',inXFR_GROUP);
1095 NOUPDATE_GROUP_PRIORITY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','GROUP_PRIORITY',inXFR_GROUP);
1097 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','CREATION_DATE',inXFR_GROUP);
1098 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','LAST_UPDATE_DATE',inXFR_GROUP);
1099 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','CREATED_BY',inXFR_GROUP);
1100 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','LAST_UPDATED_BY',inXFR_GROUP);
1101 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','SECURITY_MASK',inXFR_GROUP);
1102 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_END_USER_GROUPS','CHECKOUT_USER',inXFR_GROUP);
1103
1104
1105 OPEN c_xfr_endusergroup;
1106 LOOP
1107 IF (nCommitCount>= COMMIT_SIZE) THEN
1108 BEGIN
1109 COMMIT;
1110 nCommitCount:=0;
1111 END;
1112 ELSE
1113 nCOmmitCount:=nCommitCount+1;
1114 END IF;
1115 FETCH c_xfr_endusergroup INTO p_xfr_endusergroup;
1116 x_xfr_endusergroup_f:=c_xfr_endusergroup%FOUND;
1117 EXIT WHEN (NOT x_xfr_endusergroup_f Or nFailed >= Max_Err);
1118 IF (p_xfr_endusergroup.DISPOSITION = 'I') THEN
1119 BEGIN
1120 INSERT INTO CZ_END_USER_GROUPS (END_USER_ID,
1121 USER_GROUP_ID,
1122 DATE_ADDED_USER,
1123 USER_ADDEDBY,
1124 GROUP_PRIORITY,
1125 DELETED_FLAG,
1126 CREATION_DATE,
1127 LAST_UPDATE_DATE,
1128 CREATED_BY,
1129 LAST_UPDATED_BY,
1130 SECURITY_MASK,
1131 CHECKOUT_USER)
1132 VALUES
1133 (p_xfr_endusergroup.END_USER_ID,
1134 p_xfr_endusergroup.USER_GROUP_ID,
1135 p_xfr_endusergroup.DATE_ADDED_USER,
1136 p_xfr_endusergroup.USER_ADDEDBY,
1137 p_xfr_endusergroup.GROUP_PRIORITY,
1138 p_xfr_endusergroup.DELETED_FLAG,
1139 SYSDATE,
1140 SYSDATE,
1141 1,
1142 1,
1143 NULL,
1144 p_xfr_endusergroup.CHECKOUT_USER);
1145 nInsertCount:=nInsertCount+1;
1146 BEGIN
1147 UPDATE CZ_IMP_end_user_group
1148 SET REC_STATUS='OK'
1149 WHERE END_USER_ID=p_xfr_endusergroup.END_USER_ID
1150 AND USER_GROUP_ID=p_xfr_endusergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1151 END;
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 nFailed:=nFailed +1;
1155 BEGIN
1156 UPDATE CZ_IMP_end_user_group
1157 SET REC_STATUS='ERR'
1158 WHERE END_USER_ID=p_xfr_endusergroup.END_USER_ID
1159 AND USER_GROUP_ID=p_xfr_endusergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1160 END;
1161 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_END_USER_GROUP',11276);
1162 END ;
1163 ELSIF (p_xfr_endusergroup.DISPOSITION = 'M') THEN
1164 BEGIN
1165 UPDATE CZ_END_USER_GROUPS SET DATE_ADDED_USER=DECODE(NOUPDATE_DATE_ADDED_USER,0,p_xfr_endusergroup.DATE_ADDED_USER,DATE_ADDED_USER),
1166 USER_ADDEDBY=DECODE(NOUPDATE_USER_ADDEDBY,0,p_xfr_endusergroup.USER_ADDEDBY,USER_ADDEDBY),
1170 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
1167 GROUP_PRIORITY=DECODE(NOUPDATE_GROUP_PRIORITY,0,p_xfr_endusergroup.GROUP_PRIORITY,GROUP_PRIORITY),
1168 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_endusergroup.DELETED_FLAG ,DELETED_FLAG),
1169 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
1171 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
1172 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
1173 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
1174 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,NULL,CHECKOUT_USER)
1175 WHERE END_USER_ID=p_xfr_endusergroup.END_USER_ID AND
1176 USER_GROUP_ID=p_xfr_endusergroup.USER_GROUP_ID;
1177 IF(SQL%NOTFOUND) THEN
1178 nFailed:=nFailed+1;
1179 ELSE
1180 nUpdateCount:=nUpdateCount+1;
1181 BEGIN
1182 UPDATE CZ_IMP_end_user_group
1183 SET REC_STATUS='OK'
1184 WHERE END_USER_ID=p_xfr_endusergroup.END_USER_ID
1185 AND USER_GROUP_ID=p_xfr_endusergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1186 END;
1187 END IF;
1188 EXCEPTION
1189 WHEN OTHERS THEN
1190 nFailed:=nFailed +1;
1191 BEGIN
1192 UPDATE CZ_IMP_end_user_group
1193 SET REC_STATUS='ERR'
1194 WHERE END_USER_ID=p_xfr_endusergroup.END_USER_ID
1195 AND USER_GROUP_ID=p_xfr_endusergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1196 END;
1197 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_END_USER_GROUP',11276);
1198 END ;
1199 END IF;
1200 END LOOP;
1201 CLOSE c_xfr_endusergroup;
1202 COMMIT;
1203 INSERTS:=nInsertCount;
1204 UPDATES:=nUpdateCount;
1205 FAILED:=nFailed;
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_END_USER_GROUP',11276);
1209 END;
1210 END XFR_END_USER_GROUP;
1211 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1212 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1213 PROCEDURE XFR_USER_GROUP ( inRUN_ID IN PLS_INTEGER,
1214 COMMIT_SIZE IN PLS_INTEGER,
1215 MAX_ERR IN PLS_INTEGER,
1216 INSERTS OUT NOCOPY PLS_INTEGER,
1217 UPDATES OUT NOCOPY PLS_INTEGER,
1218 FAILED OUT NOCOPY PLS_INTEGER,
1219 inXFR_GROUP IN VARCHAR2
1220 ) IS
1221 BEGIN
1222 DECLARE CURSOR c_xfr_usergroup IS
1223 SELECT *
1224 FROM CZ_IMP_USER_GROUP
1225 WHERE CZ_IMP_USER_GROUP.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
1226 x_xfr_usergroup_f BOOLEAN:=FALSE;
1227 x_error BOOLEAN:=FALSE;
1228 p_xfr_usergroup c_xfr_usergroup%ROWTYPE;
1229 -- Internal vars --
1230 nCommitCount PLS_INTEGER:=0; -- COMMIT buffer index --
1231 nInsertCount PLS_INTEGER:=0; -- Inserts --
1232 nUpdateCount PLS_INTEGER:=0; -- Updates --
1233 nFailed PLS_INTEGER:=0; -- Failed records --
1234 NOUPDATE_DESC_TEXT NUMBER;
1235 NOUPDATE_GROUP_NAME NUMBER;
1236 NOUPDATE_GROUP_DESC NUMBER;
1237 NOUPDATE_READ_AUTH NUMBER;
1238 NOUPDATE_CREATE_AUTH NUMBER;
1242 NOUPDATE_ALLOW_CONFIG_CHANGES NUMBER;
1239 NOUPDATE_DELETE_AUTH NUMBER;
1240 NOUPDATE_UPDATE_AUTH NUMBER;
1241 NOUPDATE_USER_GROUP_DISC_LIMIT NUMBER;
1243 NOUPDATE_DELETED_FLAG NUMBER;
1244 NOUPDATE_CREATION_DATE NUMBER;
1245 NOUPDATE_LAST_UPDATE_DATE NUMBER;
1246 NOUPDATE_CREATED_BY NUMBER;
1247 NOUPDATE_LAST_UPDATED_BY NUMBER;
1248 NOUPDATE_SECURITY_MASK NUMBER;
1249 NOUPDATE_CHECKOUT_USER NUMBER;
1250
1251 -- Make sure that the DataSet exists
1252 BEGIN
1253 -- Get the Update Flags for each column
1254 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','DESC_TEXT',inXFR_GROUP);
1255 NOUPDATE_GROUP_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','GROUP_NAME',inXFR_GROUP);
1256 NOUPDATE_GROUP_DESC := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','GROUP_DESC',inXFR_GROUP);
1257 NOUPDATE_READ_AUTH := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','READ_AUTH',inXFR_GROUP);
1258 NOUPDATE_CREATE_AUTH := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','CREATE_AUTH',inXFR_GROUP);
1259 NOUPDATE_DELETE_AUTH := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','DELETE_AUTH',inXFR_GROUP);
1260 NOUPDATE_UPDATE_AUTH := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','UPDATE_AUTH',inXFR_GROUP);
1261 NOUPDATE_USER_GROUP_DISC_LIMIT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','USER_GROUP_DISC_LIMIT',inXFR_GROUP);
1262 NOUPDATE_ALLOW_CONFIG_CHANGES := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','ALLOW_CONFIG_CHANGES',inXFR_GROUP);
1263 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','DELETED_FLAG',inXFR_GROUP);
1264 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','CREATION_DATE',inXFR_GROUP);
1265 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','LAST_UPDATE_DATE',inXFR_GROUP);
1266 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','CREATED_BY',inXFR_GROUP);
1267 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','LAST_UPDATED_BY',inXFR_GROUP);
1268 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','SECURITY_MASK',inXFR_GROUP);
1269 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_USER_GROUPS','CHECKOUT_USER',inXFR_GROUP);
1270
1271 OPEN c_xfr_usergroup;
1272 LOOP
1273 IF (nCommitCount>= COMMIT_SIZE) THEN
1274 BEGIN
1275 COMMIT;
1276 nCommitCount:=0;
1277 END;
1278 ELSE
1279 nCOmmitCount:=nCommitCount+1;
1280 END IF;
1281 FETCH c_xfr_usergroup INTO p_xfr_usergroup;
1282 x_xfr_usergroup_f:=c_xfr_usergroup%FOUND;
1283 EXIT WHEN (NOT x_xfr_usergroup_f Or nFailed >= Max_Err);
1284 IF (p_xfr_usergroup.DISPOSITION = 'I') THEN
1285 BEGIN
1286 INSERT INTO CZ_USER_GROUPS (
1287 USER_GROUP_ID,/*DESC_TEXT,*/
1288 GROUP_NAME,GROUP_DESC,READ_AUTH,CREATE_AUTH,DELETE_AUTH,
1289 UPDATE_AUTH,USER_GROUP_DISC_LIMIT,ALLOW_CONFIG_CHANGES,
1290 DELETED_FLAG,CREATION_DATE,LAST_UPDATE_DATE,
1291 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER) VALUES
1292 (
1293 p_xfr_usergroup.USER_GROUP_ID,/*p_xfr_usergroup.DESC_TEXT,*/
1294 p_xfr_usergroup.GROUP_NAME,p_xfr_usergroup.GROUP_DESC,
1295 p_xfr_usergroup.READ_AUTH,p_xfr_usergroup.CREATE_AUTH,
1296 p_xfr_usergroup.DELETE_AUTH,p_xfr_usergroup.UPDATE_AUTH,
1297 p_xfr_usergroup.USER_GROUP_DISC_LIMIT,p_xfr_usergroup.ALLOW_CONFIG_CHANGES,
1298 p_xfr_usergroup.DELETED_FLAG, SYSDATE,
1299 SYSDATE, 1, 1, NULL,p_xfr_usergroup.CHECKOUT_USER);
1300 nInsertCount:=nInsertCount+1;
1301 BEGIN
1302 UPDATE CZ_IMP_user_group
1303 SET REC_STATUS='OK'
1304 WHERE USER_GROUP_ID=p_xfr_usergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1305 END;
1306 EXCEPTION
1307 WHEN OTHERS THEN
1308 nFailed:=nFailed +1;
1309 BEGIN
1310 UPDATE CZ_IMP_user_group
1311 SET REC_STATUS='ERR'
1315 END ;
1312 WHERE USER_GROUP_ID=p_xfr_usergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1313 END;
1314 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_USER_GROUP',11276);
1316 ELSIF (p_xfr_usergroup.DISPOSITION = 'M') THEN
1317 BEGIN
1318 UPDATE CZ_USER_GROUPS SET
1319 /*DESC_TEXT=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_usergroup.DESC_TEXT,DESC_TEXT),*/
1320 GROUP_NAME=DECODE(NOUPDATE_GROUP_NAME,0,p_xfr_usergroup.GROUP_NAME,group_NAME),
1321 GROUP_DESC=DECODE(NOUPDATE_GROUP_DESC,0,p_xfr_usergroup.GROUP_DESC,group_DESC),
1322 READ_AUTH=DECODE(NOUPDATE_READ_AUTH,0,p_xfr_usergroup.READ_AUTH,READ_AUTH),
1323 CREATE_AUTH=DECODE(NOUPDATE_CREATE_AUTH,0,p_xfr_usergroup.CREATE_AUTH,CREATE_AUTH),
1324 DELETE_AUTH=DECODE(NOUPDATE_DELETE_AUTH,0,p_xfr_usergroup.DELETE_AUTH,DELETE_AUTH),
1325 UPDATE_AUTH=DECODE(NOUPDATE_UPDATE_AUTH,0,p_xfr_usergroup.UPDATE_AUTH,UPDATE_AUTH),
1326 USER_GROUP_DISC_LIMIT=DECODE(NOUPDATE_USER_GROUP_DISC_LIMIT,0,p_xfr_usergroup.USER_GROUP_DISC_LIMIT,USER_GROUP_DISC_LIMIT),
1327 ALLOW_CONFIG_CHANGES=DECODE(NOUPDATE_ALLOW_CONFIG_CHANGES,0,p_xfr_usergroup.ALLOW_CONFIG_CHANGES,ALLOW_CONFIG_CHANGES),
1328 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_usergroup.DELETED_FLAG ,DELETED_FLAG),
1329 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
1330 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
1331 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
1332 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
1333 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
1334 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,NULL,CHECKOUT_USER)
1335 WHERE USER_GROUP_ID=p_xfr_usergroup.USER_GROUP_ID;
1336 IF(SQL%NOTFOUND) THEN
1337 nFailed:=nFailed+1;
1338 ELSE
1339 nUpdateCount:=nUpdateCount+1;
1340 BEGIN
1341 UPDATE CZ_IMP_user_group
1342 SET REC_STATUS='OK'
1343 WHERE USER_GROUP_ID=p_xfr_usergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1344 END;
1345 END IF;
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 nFailed:=nFailed +1;
1349 BEGIN
1350 UPDATE CZ_IMP_user_group
1351 SET REC_STATUS='ERR'
1352 WHERE USER_GROUP_ID=p_xfr_usergroup.USER_GROUP_ID AND RUN_ID=inRUN_ID;
1353 END;
1354 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_USER_GROUP',11276);
1355 END ;
1356 END IF;
1357 END LOOP;
1358 CLOSE c_xfr_usergroup;
1359 COMMIT;
1360 INSERTS:=nInsertCount;
1361 UPDATES:=nUpdateCount;
1362 FAILED:=nFailed;
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 x_error:=CZ_IMP_ALL.REPORT(SQLERRM,1,'CZ_IMP_AC_XFR.XFR_USER_GROUP',11276);
1366 END;
1367 END XFR_USER_GROUP;
1368 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1369 END CZ_IMP_AC_XFR;