[Home] [Help]
PACKAGE BODY: APPS.CZ_IMP_IM_XFR
Source
1 PACKAGE BODY CZ_IMP_IM_XFR AS
2 /* $Header: cziimxfb.pls 120.8.12020000.2 2012/08/17 15:00:40 smanna ship $ */
3
4 G_BOM_APPLICATION_ID CONSTANT NUMBER := 702;
5
6 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
7 PROCEDURE XFR_ITEM_MASTER ( inRUN_ID IN PLS_INTEGER,
8 COMMIT_SIZE IN PLS_INTEGER,
9 MAX_ERR IN PLS_INTEGER,
10 INSERTS OUT NOCOPY PLS_INTEGER,
11 UPDATES OUT NOCOPY PLS_INTEGER,
12 FAILED IN OUT NOCOPY PLS_INTEGER,
13 NOCHANGE OUT NOCOPY PLS_INTEGER,
14 inXFR_GROUP IN VARCHAR2
15 ) IS
16 BEGIN
17 DECLARE CURSOR c_xfr_itemmaster IS
18 SELECT ITEM_ID,ITEM_TYPE_ID,DESC_TEXT,REF_PART_NBR,QUOTEABLE_FLAG,
19 LEAD_TIME,ITEM_STATUS,RUN_ID,REC_STATUS,DISPOSITION,DELETED_FLAG,
20 CHECKOUT_USER,USER_STR01,USER_STR02,
21 USER_STR03,USER_STR04,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
22 ORIG_SYS_REF,PRIMARY_UOM_CODE,DECIMAL_QTY_FLAG,
23 SRC_APPLICATION_ID, SRC_TYPE_CODE
24 FROM CZ_IMP_ITEM_MASTER
25 WHERE CZ_IMP_ITEM_MASTER.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
26
27 x_xfr_itemmaster_f BOOLEAN:=FALSE;
28 x_error BOOLEAN:=FALSE;
29
30 p_xfr_itemmaster c_xfr_itemmaster%ROWTYPE;
31
32 /* Internal vars */
33 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
34 nInsertCount PLS_INTEGER:=0; /*Inserts */
35 nUpdateCount PLS_INTEGER:=0; /*Updates */
36 nNoChangeCount PLS_INTEGER:=0; /*NoChangeCount */
37
38 NOUPDATE_DESC_TEXT NUMBER;
39 NOUPDATE_REF_PART_NBR NUMBER;
40 NOUPDATE_ORIG_SYS_REF NUMBER;
41 NOUPDATE_QUOTEABLE_FLAG NUMBER;
42 NOUPDATE_LEAD_TIME NUMBER;
43 NOUPDATE_ITEM_STATUS NUMBER;
44 NOUPDATE_DELETED_FLAG NUMBER;
45 NOUPDATE_USER_STR01 NUMBER;
46 NOUPDATE_USER_STR02 NUMBER;
47 NOUPDATE_USER_STR03 NUMBER;
48 NOUPDATE_USER_STR04 NUMBER;
49 NOUPDATE_USER_NUM01 NUMBER;
50 NOUPDATE_USER_NUM02 NUMBER;
51 NOUPDATE_USER_NUM03 NUMBER;
52 NOUPDATE_USER_NUM04 NUMBER;
53 NOUPDATE_CREATION_DATE NUMBER;
54 NOUPDATE_LAST_UPDATE_DATE NUMBER;
55 NOUPDATE_CREATED_BY NUMBER;
56 NOUPDATE_LAST_UPDATED_BY NUMBER;
57 NOUPDATE_SECURITY_MASK NUMBER;
58 NOUPDATE_CHECKOUT_USER NUMBER;
59 NOUPDATE_PRIMARY_UOM_CODE NUMBER;
60 NOUPDATE_ITEM_TYPE_ID NUMBER;
61 NOUPDATE_DECIMAL_QTY_FLAG NUMBER;
62
63 -- Make sure that the DataSet exists
64 BEGIN
65
66 -- Get the Update Flags for each column
67 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','DESC_TEXT',inXFR_GROUP);
68 NOUPDATE_REF_PART_NBR := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','REF_PART_NBR',inXFR_GROUP);
69 NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','ORIG_SYS_REF',inXFR_GROUP);
70 NOUPDATE_QUOTEABLE_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','QUOTEABLE_FLAG',inXFR_GROUP);
71 NOUPDATE_LEAD_TIME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','LEAD_TIME',inXFR_GROUP);
72 NOUPDATE_ITEM_STATUS := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','ITEM_STATUS',inXFR_GROUP);
73 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','DELETED_FLAG',inXFR_GROUP);
74 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_STR01',inXFR_GROUP);
75 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_STR02',inXFR_GROUP);
76 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_STR03',inXFR_GROUP);
77 NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_STR04',inXFR_GROUP);
78 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_NUM01',inXFR_GROUP);
79 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_NUM02',inXFR_GROUP);
80 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_NUM03',inXFR_GROUP);
81 NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','USER_NUM04',inXFR_GROUP);
82 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','CREATION_DATE',inXFR_GROUP);
83 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','LAST_UPDATE_DATE',inXFR_GROUP);
84 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','CREATED_BY',inXFR_GROUP);
85 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','LAST_UPDATED_BY',inXFR_GROUP);
86 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','SECURITY_MASK',inXFR_GROUP);
87 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','CHECKOUT_USER',inXFR_GROUP);
88 NOUPDATE_PRIMARY_UOM_CODE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','PRIMARY_UOM_CODE',inXFR_GROUP);
89 NOUPDATE_ITEM_TYPE_ID := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','ITEM_TYPE_ID',inXFR_GROUP);
90 NOUPDATE_DECIMAL_QTY_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_MASTERS','DECIMAL_QTY_FLAG',inXFR_GROUP);
91
92 OPEN c_xfr_itemmaster ;
93
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_itemmaster INTO p_xfr_itemmaster;
104 x_xfr_itemmaster_f:=c_xfr_itemmaster%FOUND;
105 EXIT WHEN NOT x_xfr_itemmaster_f;
106
107 IF ( FAILED >= Max_Err) THEN
108 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_XFR.XFR_ITEM_MASTER:MAX',11276,inRun_Id);
109 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
110 END IF;
111
112 IF (p_xfr_itemmaster.DISPOSITION = 'I') THEN
113 BEGIN
114 INSERT INTO CZ_ITEM_MASTERS
115 (ITEM_ID,ITEM_TYPE_ID,DESC_TEXT,REF_PART_NBR,
116 QUOTEABLE_FLAG,LEAD_TIME,ITEM_STATUS,USER_NUM01,
117 USER_NUM02,USER_NUM03,USER_NUM04,USER_STR01,
118 USER_STR02,USER_STR03,USER_STR04,CREATION_DATE,LAST_UPDATE_DATE,
119 DELETED_FLAG,CREATED_BY,LAST_UPDATED_BY,
120 SECURITY_MASK,CHECKOUT_USER,ORIG_SYS_REF,
121 PRIMARY_UOM_CODE,DECIMAL_QTY_FLAG,
122 SRC_APPLICATION_ID, SRC_TYPE_CODE)
123 VALUES
124 (p_xfr_itemmaster.ITEM_ID,p_xfr_itemmaster.ITEM_TYPE_ID,
125 p_xfr_itemmaster.DESC_TEXT,p_xfr_itemmaster.REF_PART_NBR,
126 p_xfr_itemmaster.QUOTEABLE_FLAG,p_xfr_itemmaster.LEAD_TIME,
127 p_xfr_itemmaster.ITEM_STATUS,p_xfr_itemmaster.USER_NUM01,
128 p_xfr_itemmaster.USER_NUM02,p_xfr_itemmaster.USER_NUM03,
129 p_xfr_itemmaster.USER_NUM04,p_xfr_itemmaster.USER_STR01,
130 p_xfr_itemmaster.USER_STR02,p_xfr_itemmaster.USER_STR03,
131 p_xfr_itemmaster.USER_STR04,SYSDATE,SYSDATE,
132 p_xfr_itemmaster.DELETED_FLAG,
133 1,1,NULL,
134 p_xfr_itemmaster.CHECKOUT_USER,
135 p_xfr_itemmaster.ORIG_SYS_REF,
136 p_xfr_itemmaster.PRIMARY_UOM_CODE,
137 NVL(p_xfr_itemmaster.DECIMAL_QTY_FLAG,'0'),
138 NVL(p_xfr_itemmaster.SRC_APPLICATION_ID,cnDefSrcAppId),
139 NVL(p_xfr_itemmaster.SRC_TYPE_CODE,cnDefSrcTypeCode));
140 nInsertCount:=nInsertCount+1;
141
142 UPDATE CZ_IMP_item_master
143 SET REC_STATUS='OK'
144 WHERE ITEM_ID=p_xfr_itemmaster.ITEM_ID AND RUN_ID=inRUN_ID
145 AND DISPOSITION='I';
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 FAILED:=FAILED +1;
150
151 UPDATE CZ_IMP_item_master
152 SET REC_STATUS='ERR'
153 WHERE ITEM_ID=p_xfr_itemmaster.ITEM_ID AND RUN_ID=inRUN_ID
154 AND DISPOSITION='I';
155
156 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_MASTER',11276,inRUN_ID);
157 END ;
158 ELSIF (p_xfr_itemmaster.DISPOSITION = 'M') THEN
159 BEGIN
160 UPDATE CZ_ITEM_MASTERS SET
161 ITEM_TYPE_ID=DECODE(NOUPDATE_ITEM_TYPE_ID,0,p_xfr_itemmaster.ITEM_TYPE_ID,ITEM_TYPE_ID),
162 DESC_TEXT=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_itemmaster.DESC_TEXT,DESC_TEXT),
163 REF_PART_NBR=DECODE(NOUPDATE_REF_PART_NBR,0, p_xfr_itemmaster.REF_PART_NBR,REF_PART_NBR),
164 ORIG_SYS_REF=DECODE(NOUPDATE_ORIG_SYS_REF,0, p_xfr_itemmaster.ORIG_SYS_REF,ORIG_SYS_REF),
165 QUOTEABLE_FLAG=DECODE(NOUPDATE_QUOTEABLE_FLAG,0,p_xfr_itemmaster.QUOTEABLE_FLAG,QUOTEABLE_FLAG),
166 LEAD_TIME=DECODE(NOUPDATE_LEAD_TIME,0,p_xfr_itemmaster.LEAD_TIME,LEAD_TIME),
167 ITEM_STATUS=DECODE(NOUPDATE_ITEM_STATUS,0,p_xfr_itemmaster.ITEM_STATUS,ITEM_STATUS),
168 DELETED_FLAG=DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_itemmaster.DELETED_FLAG,DELETED_FLAG),
169 USER_NUM01=DECODE(NOUPDATE_USER_NUM01,0,p_xfr_itemmaster.USER_NUM01,USER_NUM01),
170 USER_NUM02=DECODE(NOUPDATE_USER_NUM02,0,p_xfr_itemmaster.USER_NUM02,USER_NUM02),
171 USER_NUM03=DECODE(NOUPDATE_USER_NUM03,0,p_xfr_itemmaster.USER_NUM03,USER_NUM03),
172 USER_NUM04=DECODE(NOUPDATE_USER_NUM04,0,p_xfr_itemmaster.USER_NUM04,USER_NUM04),
173 USER_STR01=DECODE(NOUPDATE_USER_STR01,0,p_xfr_itemmaster.USER_STR01,USER_STR01),
174 USER_STR02=DECODE(NOUPDATE_USER_STR02,0,p_xfr_itemmaster.USER_STR02,USER_STR02),
175 USER_STR03=DECODE(NOUPDATE_USER_STR03,0,p_xfr_itemmaster.USER_STR03,USER_STR03),
176 USER_STR04=DECODE(NOUPDATE_USER_STR04,0,p_xfr_itemmaster.USER_STR04,USER_STR04),
177 CREATION_DATE=DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
178 LAST_UPDATE_DATE=DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
179 CREATED_BY=DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
180 LAST_UPDATED_BY=DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
181 SECURITY_MASK=DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
182 CHECKOUT_USER=DECODE(NOUPDATE_CHECKOUT_USER,0,p_xfr_itemmaster.CHECKOUT_USER,CHECKOUT_USER),
183 PRIMARY_UOM_CODE=DECODE(NOUPDATE_PRIMARY_UOM_CODE,0,p_xfr_itemmaster.PRIMARY_UOM_CODE,PRIMARY_UOM_CODE),
184 SRC_APPLICATION_ID=NVL(p_xfr_itemmaster.SRC_APPLICATION_ID,cnDefSrcAppId),
185 SRC_TYPE_CODE=NVL(p_xfr_itemmaster.SRC_TYPE_CODE,cnDefSrcTypeCode),
186 DECIMAL_QTY_FLAG=DECODE(NOUPDATE_DECIMAL_QTY_FLAG,0,p_xfr_itemmaster.DECIMAL_QTY_FLAG,DECIMAL_QTY_FLAG)
187 WHERE ITEM_ID=p_xfr_itemmaster.ITEM_ID;
188 IF(SQL%NOTFOUND) THEN
189 FAILED:=FAILED+1;
190 ELSE
191 nUpdateCount:=nUpdateCount+1;
192
193 UPDATE CZ_IMP_item_master
194 SET REC_STATUS='OK'
195 WHERE ITEM_ID=p_xfr_itemmaster.ITEM_ID AND RUN_ID=inRUN_ID
196 AND DISPOSITION='M';
197
198 END IF;
199 EXCEPTION
200 WHEN OTHERS THEN
201 FAILED:=FAILED +1;
202
203 UPDATE CZ_IMP_item_master
204 SET REC_STATUS='ERR'
205 WHERE ITEM_ID=p_xfr_itemmaster.ITEM_ID AND RUN_ID=inRUN_ID
206 AND DISPOSITION='M';
207
208 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_MASTER',11276,inRUN_ID);
209 END ;
210
211 ELSIF (p_xfr_itemmaster.DISPOSITION = 'N')THEN
212
213 UPDATE CZ_IMP_item_master
214 SET REC_STATUS='OK'
215 WHERE ITEM_ID=p_xfr_itemmaster.ITEM_ID AND RUN_ID=inRUN_ID
216 AND DISPOSITION='N';
217 nNoChangeCount:=nNoChangeCount+(sql%rowcount);
218
219 END IF;
220
221 END LOOP;
222 CLOSE c_xfr_itemmaster;
223 COMMIT;
224 INSERTS:=nInsertCount;
225 UPDATES:=nUpdateCount;
226 NOCHANGE:=nNoChangeCount;
227 EXCEPTION
228 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
229 RAISE;
230 WHEN OTHERS THEN
231 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_MASTER',11276,inRUN_ID);
232 END;
233 END XFR_ITEM_MASTER;
234 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
235
236 PROCEDURE XFR_ITEM_PROPERTY_VALUE ( inRUN_ID IN PLS_INTEGER,
237 COMMIT_SIZE IN PLS_INTEGER,
238 MAX_ERR IN PLS_INTEGER,
239 INSERTS OUT NOCOPY PLS_INTEGER,
240 UPDATES OUT NOCOPY PLS_INTEGER,
241
242 FAILED IN OUT NOCOPY PLS_INTEGER,
243 NOCHANGE OUT NOCOPY PLS_INTEGER,/*VIGRAMAK*/
244 inXFR_GROUP IN VARCHAR2
245 )IS
246 CURSOR c_xfr_itempropertyvalue IS -- sselahi
247 SELECT PROPERTY_ID,ITEM_ID,PROPERTY_VALUE,PROPERTY_NUM_VALUE,
248 RUN_ID,REC_STATUS,DISPOSITION,DELETED_FLAG,
252 NVL(FSK_ITEMMASTER_2_1, FSK_ITEMMASTER_2_EXT) AS FSK_ITEMMASTER,
249 CHECKOUT_USER,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
250 USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
251 ORIG_SYS_REF, NVL(FSK_PROPERTY_1_1, FSK_PROPERTY_1_EXT) AS FSK_PROPERTY,
253 SRC_APPLICATION_ID
254 FROM CZ_IMP_ITEM_PROPERTY_VALUE WHERE
255 RUN_ID=inRUN_ID AND REC_STATUS IN ('PASS','F3X');
256
257 x_xfr_itempropertyvalue_f BOOLEAN:=FALSE;
258 x_error BOOLEAN:=FALSE;
259
260 p_xfr_itempropertyvalue c_xfr_itempropertyvalue%ROWTYPE;
261
262 /* Internal vars */
263 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
264 nInsertCount PLS_INTEGER:=0; /*Inserts */
265 nUpdateCount PLS_INTEGER:=0; /*Updates */
266 nNoChangeCount PLS_INTEGER:=0; /*NoChangeCount */
267
268 NOUPDATE_PROPERTY_VALUE NUMBER;
269 NOUPDATE_DELETED_FLAG NUMBER;
270 NOUPDATE_CREATION_DATE NUMBER;
271 NOUPDATE_LAST_UPDATE_DATE NUMBER;
272 NOUPDATE_CREATED_BY NUMBER;
273 NOUPDATE_LAST_UPDATED_BY NUMBER;
274 NOUPDATE_SECURITY_MASK NUMBER;
275 NOUPDATE_CHECKOUT_USER NUMBER;
276 NOUPDATE_PROPERTY_NUM_VALUE NUMBER; -- sselahi
277
278 sOrigSysRef cz_item_property_values.orig_sys_ref%TYPE;
279 l_msg VARCHAR2(2000);
280
281 -- Make sure that the DataSet exists
282 BEGIN
283
284 -- Get the Update Flags for each column --sselahi
285 NOUPDATE_PROPERTY_VALUE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','PROPERTY_VALUE',inXFR_GROUP);
286 NOUPDATE_PROPERTY_NUM_VALUE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','PROPERTY_NUM_VALUE',inXFR_GROUP);
287 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','DELETED_FLAG',inXFR_GROUP);
288 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','CREATION_DATE',inXFR_GROUP);
289 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','LAST_UPDATE_DATE',inXFR_GROUP);
290 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','CREATED_BY',inXFR_GROUP);
291 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','LAST_UPDATED_BY',inXFR_GROUP);
292 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','SECURITY_MASK',inXFR_GROUP);
293 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_PROPERTY_VALUES','CHECKOUT_USER',inXFR_GROUP);
294
295
296 OPEN c_xfr_itempropertyvalue ;
297 LOOP
298 IF (nCommitCount>= COMMIT_SIZE) THEN
299 COMMIT;
300 nCommitCount:=0;
301 ELSE
302 nCOmmitCount:=nCommitCount+1;
303 END IF;
304 FETCH c_xfr_itempropertyvalue INTO p_xfr_itempropertyvalue;
305
306 x_xfr_itempropertyvalue_f:=c_xfr_itempropertyvalue%FOUND;
307 EXIT WHEN NOT x_xfr_itempropertyvalue_f;
308 IF ( FAILED >= Max_Err) THEN
309 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),
310 1,'CZ_IMP_IM_XFR.XFR_ITEM_PROPERTY_VALUE:MAX',11276,inRun_Id);
311 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
312 END IF;
313
314 IF(p_xfr_itempropertyvalue.ORIG_SYS_REF IS NOT NULL)THEN
315 sOrigSysRef := p_xfr_itempropertyvalue.ORIG_SYS_REF;
316 ELSE
317 sOrigSysRef := p_xfr_itempropertyvalue.FSK_ITEMMASTER || ':' || p_xfr_itempropertyvalue.FSK_PROPERTY;
318 END IF;
319
320 IF (p_xfr_itempropertyvalue.DISPOSITION = 'I') THEN
321 BEGIN
322 INSERT INTO CZ_ITEM_PROPERTY_VALUES (PROPERTY_ID,ITEM_ID,
323 PROPERTY_VALUE,PROPERTY_NUM_VALUE, --sselahi
324 /* USER_NUM01,USER_NUM02, USER_NUM03,
325 USER_NUM04,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
326 */
327 CREATION_DATE, LAST_UPDATE_DATE, DELETED_FLAG,
328 CREATED_BY, LAST_UPDATED_BY, SECURITY_MASK,
329 CHECKOUT_USER, ORIG_SYS_REF, SRC_APPLICATION_ID) VALUES
330 (p_xfr_itempropertyvalue.PROPERTY_ID,p_xfr_itempropertyvalue.ITEM_ID,
331 p_xfr_itempropertyvalue.PROPERTY_VALUE,
332 p_xfr_itempropertyvalue.PROPERTY_NUM_VALUE,
333 /*p_xfr_itempropertyvalue.USER_NUM01,p_xfr_itempropertyvalue.USER_NUM02,
334 p_xfr_itempropertyvalue.USER_NUM03,p_xfr_itempropertyvalue.USER_NUM04,
335 p_xfr_itempropertyvalue.USER_STR01,p_xfr_itempropertyvalue.USER_STR02,
336 p_xfr_itempropertyvalue.USER_STR03,p_xfr_itempropertyvalue.USER_STR04,*/
337 SYSDATE, SYSDATE, p_xfr_itempropertyvalue.DELETED_FLAG , 1, 1, NULL,
338 p_xfr_itempropertyvalue.CHECKOUT_USER, sOrigSysRef, p_xfr_itempropertyvalue.SRC_APPLICATION_ID);
339
340 nInsertCount:=nInsertCount+1;
341
342 UPDATE CZ_IMP_item_property_value
343 SET REC_STATUS='OK'
344 WHERE PROPERTY_ID=p_xfr_itempropertyvalue.PROPERTY_ID
345 AND ITEM_ID=p_xfr_itempropertyvalue.ITEM_ID AND RUN_ID=inRUN_ID
346 AND DISPOSITION='I' AND ORIG_SYS_REF=p_xfr_itempropertyvalue.ORIG_SYS_REF;
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 FAILED:=FAILED +1;
351
352 UPDATE CZ_IMP_item_property_value
353 SET REC_STATUS='ERR'
354 WHERE PROPERTY_ID=p_xfr_itempropertyvalue.PROPERTY_ID
355 AND ITEM_ID=p_xfr_itempropertyvalue.ITEM_ID AND RUN_ID=inRUN_ID
356 AND DISPOSITION='I' AND ORIG_SYS_REF=p_xfr_itempropertyvalue.ORIG_SYS_REF;
357
361 (p_xfr_itempropertyvalue.DISPOSITION = 'R' AND p_xfr_itempropertyvalue.REC_STATUS = 'F3X'))THEN
358 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_PROPERTY_VALUE',11276,inRUN_ID);
359 END ;
360 ELSIF (p_xfr_itempropertyvalue.DISPOSITION = 'M' OR
362 BEGIN -- sselahi
363 UPDATE CZ_ITEM_PROPERTY_VALUES SET
364 PROPERTY_VALUE=DECODE(NOUPDATE_PROPERTY_VALUE,0,p_xfr_itempropertyvalue.PROPERTY_VALUE,PROPERTY_VALUE),
365 PROPERTY_NUM_VALUE=DECODE(NOUPDATE_PROPERTY_NUM_VALUE,0,p_xfr_itempropertyvalue.PROPERTY_NUM_VALUE,PROPERTY_NUM_VALUE),
366 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_itempropertyvalue.DELETED_FLAG ,DELETED_FLAG),
367 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
368 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
369 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
370 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
371 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
372 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,NULL,CHECKOUT_USER),
373 ORIG_SYS_REF = sOrigSysRef
374 WHERE PROPERTY_ID=p_xfr_itempropertyvalue.PROPERTY_ID AND
375 ITEM_ID=p_xfr_itempropertyvalue.ITEM_ID;
376
377 IF(SQL%NOTFOUND) THEN
378 FAILED:=FAILED+1;
379 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_NOTFOUND_IPV','OSR', p_xfr_itempropertyvalue.orig_sys_ref);
380 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IM_XFR.XFR_ITEM_PROPERTY_VALUE',11276,inRUN_ID);
381 ELSE
382 IF(p_xfr_itempropertyvalue.REC_STATUS<>'F3X')THEN nUpdateCount:=nUpdateCount+1; END IF;
383
384 UPDATE CZ_IMP_item_property_value
385 SET REC_STATUS=DECODE(p_xfr_itempropertyvalue.REC_STATUS,'PASS','OK',p_xfr_itempropertyvalue.REC_STATUS)
386 WHERE PROPERTY_ID=p_xfr_itempropertyvalue.PROPERTY_ID
387 AND ITEM_ID=p_xfr_itempropertyvalue.ITEM_ID AND RUN_ID=inRUN_ID
388 AND DISPOSITION='M' AND ORIG_SYS_REF=p_xfr_itempropertyvalue.ORIG_SYS_REF;
389
390 END IF;
391 EXCEPTION
392 WHEN OTHERS THEN
393 FAILED:=FAILED +1;
394
395 UPDATE CZ_IMP_item_property_value
396 SET REC_STATUS='ERR'
397 WHERE PROPERTY_ID=p_xfr_itempropertyvalue.PROPERTY_ID
398 AND ITEM_ID=p_xfr_itempropertyvalue.ITEM_ID AND RUN_ID=inRUN_ID
399 AND DISPOSITION='M' AND ORIG_SYS_REF=p_xfr_itempropertyvalue.ORIG_SYS_REF;
400
401 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_PROPERTY_VALUE',11276,inRUN_ID);
402 END;
403 ELSIF (p_xfr_itempropertyvalue.DISPOSITION = 'N' ) THEN /*VIGRAMAK*/
404 UPDATE CZ_IMP_item_property_value
405 SET REC_STATUS=DECODE(p_xfr_itempropertyvalue.REC_STATUS,'PASS','OK',p_xfr_itempropertyvalue.REC_STATUS)
406 WHERE PROPERTY_ID=p_xfr_itempropertyvalue.PROPERTY_ID
407 AND ITEM_ID=p_xfr_itempropertyvalue.ITEM_ID AND RUN_ID=inRUN_ID
408 AND DISPOSITION='N' AND ORIG_SYS_REF=p_xfr_itempropertyvalue.ORIG_SYS_REF;
409
410 nNoChangeCount:=nNoChangeCount+(SQL%ROWCOUNT);
411
412
413
414 END IF;
415 END LOOP;
416 CLOSE c_xfr_itempropertyvalue;
417 COMMIT;
418 INSERTS:=nInsertCount;
419 UPDATES:=nUpdateCount;
420 NOCHANGE:=nNoChangeCount;
421 EXCEPTION
422 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
423 RAISE;
424 WHEN OTHERS THEN
425 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_PROPERTY_VALUE',11276,inRUN_ID);
426 END XFR_ITEM_PROPERTY_VALUE;
427
428 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
429
430 PROCEDURE XFR_ITEM_TYPE ( inRUN_ID IN PLS_INTEGER,
431 COMMIT_SIZE IN PLS_INTEGER,
432 MAX_ERR IN PLS_INTEGER,
433 INSERTS OUT NOCOPY PLS_INTEGER,
434 UPDATES OUT NOCOPY PLS_INTEGER,
435 FAILED IN OUT NOCOPY PLS_INTEGER,
436 NOCHANGE OUT NOCOPY PLS_INTEGER,
437 inXFR_GROUP IN VARCHAR2
438 )IS
439 BEGIN
440 DECLARE CURSOR c_xfr_itemtype IS
441 SELECT ITEM_TYPE_ID,DESC_TEXT,NAME,RUN_ID ,REC_STATUS ,DISPOSITION ,DELETED_FLAG ,
442 CHECKOUT_USER, USER_STR01,USER_STR02,USER_STR03,
443 USER_STR04,USER_NUM01,USER_NUM02, USER_NUM03, USER_NUM04,ORIG_SYS_REF,SRC_APPLICATION_ID
444 FROM CZ_IMP_ITEM_TYPE WHERE CZ_IMP_ITEM_TYPE.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
445 x_xfr_itemtype_f BOOLEAN:=FALSE;
446 x_error BOOLEAN:=FALSE;
447
448 p_xfr_itemtype c_xfr_itemtype%ROWTYPE;
449
450 /* Internal vars */
451 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
452 nInsertCount PLS_INTEGER:=0; /*Inserts */
453 nUpdateCount PLS_INTEGER:=0; /*Updates */
454 nNoChangeCount PLS_INTEGER:=0; /*NoChange in Records */
455
456 NOUPDATE_DESC_TEXT NUMBER;
457 NOUPDATE_NAME NUMBER;
458 NOUPDATE_DELETED_FLAG NUMBER;
459 NOUPDATE_USER_STR01 NUMBER;
460 NOUPDATE_USER_STR02 NUMBER;
461 NOUPDATE_USER_STR03 NUMBER;
462 NOUPDATE_USER_STR04 NUMBER;
463 NOUPDATE_USER_NUM01 NUMBER;
464 NOUPDATE_USER_NUM02 NUMBER;
465 NOUPDATE_USER_NUM03 NUMBER;
466 NOUPDATE_USER_NUM04 NUMBER;
467 NOUPDATE_CREATION_DATE NUMBER;
468 NOUPDATE_LAST_UPDATE_DATE NUMBER;
469 NOUPDATE_CREATED_BY NUMBER;
473
470 NOUPDATE_LAST_UPDATED_BY NUMBER;
471 NOUPDATE_SECURITY_MASK NUMBER;
472 NOUPDATE_CHECKOUT_USER NUMBER;
474 -- Make sure that the DataSet exists
475 BEGIN
476 -- Get the Update Flags for each column
477 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','DESC_TEXT',inXFR_GROUP);
478 NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','NAME',inXFR_GROUP);
479 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','DELETED_FLAG',inXFR_GROUP);
480 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_STR01',inXFR_GROUP);
481 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_STR02',inXFR_GROUP);
482 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_STR03',inXFR_GROUP);
483 NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_STR04',inXFR_GROUP);
484 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_NUM01',inXFR_GROUP);
485 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_NUM02',inXFR_GROUP);
486 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_NUM03',inXFR_GROUP);
487 NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','USER_NUM04',inXFR_GROUP);
488 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','CREATION_DATE',inXFR_GROUP);
489 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','LAST_UPDATE_DATE',inXFR_GROUP);
490 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','CREATED_BY',inXFR_GROUP);
491 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','LAST_UPDATED_BY',inXFR_GROUP);
492 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','SECURITY_MASK',inXFR_GROUP);
493 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_ITEM_TYPES','CHECKOUT_USER',inXFR_GROUP);
494 OPEN c_xfr_itemtype ;
495
496 LOOP
497 IF (nCommitCount>= COMMIT_SIZE) THEN
498 BEGIN
499 COMMIT;
500 nCommitCount:=0;
501 END;
502 ELSE
503 nCOmmitCount:=nCommitCount+1;
504 END IF;
505 FETCH c_xfr_itemtype INTO p_xfr_itemtype;
506
507 x_xfr_itemtype_f:=c_xfr_itemtype%FOUND;
508 EXIT WHEN NOT x_xfr_itemtype_f;
509 IF ( FAILED >= Max_Err) THEN
510 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_XFR.XFR_ITEM_TYPE:MAX',11276,inRun_Id);
511 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
512 END IF;
513
514 IF (p_xfr_itemtype.DISPOSITION = 'I') THEN
515 BEGIN
516 INSERT INTO CZ_ITEM_TYPES (ITEM_TYPE_ID,DESC_TEXT,NAME,USER_NUM01,USER_NUM02,
517 USER_NUM03,USER_NUM04,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
518 CREATION_DATE, LAST_UPDATE_DATE, DELETED_FLAG,
519 CREATED_BY, LAST_UPDATED_BY, SECURITY_MASK,
520 CHECKOUT_USER,ORIG_SYS_REF,SRC_APPLICATION_ID) VALUES
521 (p_xfr_itemtype.ITEM_TYPE_ID,p_xfr_itemtype.DESC_TEXT,p_xfr_itemtype.NAME,
522 p_xfr_itemtype.USER_NUM01,p_xfr_itemtype.USER_NUM02,p_xfr_itemtype.USER_NUM03,
523 p_xfr_itemtype.USER_NUM04, p_xfr_itemtype.USER_STR01,p_xfr_itemtype.USER_STR02,
524 p_xfr_itemtype.USER_STR03,p_xfr_itemtype.USER_STR04, SYSDATE, SYSDATE,
525 p_xfr_itemtype.DELETED_FLAG,
526 1, 1, NULL, p_xfr_itemtype.CHECKOUT_USER,
527 p_xfr_itemtype.ORIG_SYS_REF,p_xfr_itemtype.SRC_APPLICATION_ID);
528 nInsertCount:=nInsertCount+1;
529 BEGIN
530 UPDATE CZ_IMP_item_type
531 SET REC_STATUS='OK'
532 WHERE ITEM_TYPE_ID=p_xfr_itemtype.ITEM_TYPE_ID AND RUN_ID=inRUN_ID
533 AND DISPOSITION='I';
534 END;
535 EXCEPTION
536 WHEN OTHERS THEN
537 FAILED:=FAILED +1;
538 BEGIN
539 UPDATE CZ_IMP_item_type
540 SET REC_STATUS='ERR'
541 WHERE ITEM_TYPE_ID=p_xfr_itemtype.ITEM_TYPE_ID AND RUN_ID=inRUN_ID
542 AND DISPOSITION='I';
543 END;
544 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_TYPE',11276,inRUN_ID);
545 END ;
546 ELSIF (p_xfr_itemtype.DISPOSITION = 'M') THEN
547 BEGIN
548 UPDATE CZ_ITEM_TYPES SET DESC_TEXT=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_itemtype.DESC_TEXT, DESC_TEXT),
549 NAME=DECODE(NOUPDATE_NAME,0,p_xfr_itemtype.NAME,NAME),
550 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_itemtype.DELETED_FLAG ,DELETED_FLAG),
551 USER_NUM01= DECODE(NOUPDATE_USER_NUM01, 0,p_xfr_itemtype.USER_NUM01,USER_NUM01),
552 USER_NUM02= DECODE(NOUPDATE_USER_NUM02, 0,p_xfr_itemtype.USER_NUM02,USER_NUM02),
553 USER_NUM03= DECODE(NOUPDATE_USER_NUM03, 0,p_xfr_itemtype.USER_NUM03,USER_NUM03),
554 USER_NUM04= DECODE(NOUPDATE_USER_NUM04, 0,p_xfr_itemtype.USER_NUM04,USER_NUM04),
555 USER_STR01= DECODE(NOUPDATE_USER_STR01, 0,p_xfr_itemtype.USER_STR01,USER_STR01),
556 USER_STR02= DECODE(NOUPDATE_USER_STR02, 0,p_xfr_itemtype.USER_STR02,USER_STR02),
560 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
557 USER_STR03= DECODE(NOUPDATE_USER_STR03, 0,p_xfr_itemtype.USER_STR03,USER_STR03),
558 USER_STR04= DECODE(NOUPDATE_USER_STR04, 0,p_xfr_itemtype.USER_STR04,USER_STR04),
559 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
561 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
562 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
563 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
564 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,NULL,CHECKOUT_USER)
565 WHERE ITEM_TYPE_ID=p_xfr_itemtype.ITEM_TYPE_ID;
566 IF(SQL%NOTFOUND) THEN
567 FAILED:=FAILED+1;
568 ELSE
569 nUpdateCount:=nUpdateCount+1;
570 BEGIN
571 UPDATE CZ_IMP_item_type
572 SET REC_STATUS='OK'
573 WHERE ITEM_TYPE_ID=p_xfr_itemtype.ITEM_TYPE_ID AND RUN_ID=inRUN_ID
574 AND DISPOSITION='M';
575 END;
576 END IF;
577 EXCEPTION
578 WHEN OTHERS THEN
579 FAILED:=FAILED +1;
580 BEGIN
581 UPDATE CZ_IMP_item_type
582 SET REC_STATUS='ERR'
583 WHERE ITEM_TYPE_ID=p_xfr_itemtype.ITEM_TYPE_ID AND RUN_ID=inRUN_ID
584 AND DISPOSITION='M';
585 END;
586 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_TYPE',11276,inRUN_ID);
587 END ;
588
589 ELSIF (p_xfr_itemtype.DISPOSITION = 'N') THEN
590 UPDATE CZ_IMP_item_type
591 SET REC_STATUS='OK'
592 WHERE ITEM_TYPE_ID=p_xfr_itemtype.ITEM_TYPE_ID AND RUN_ID=inRUN_ID
593 AND DISPOSITION='N';
594
595 nNoChangeCount:=nNoChangeCount+(sql%ROWCOUNT);
596
597
598
599 END IF;
600
601 END LOOP;
602 CLOSE c_xfr_itemtype;
603 COMMIT;
604 INSERTS:=nInsertCount;
605 UPDATES:=nUpdateCount;
606 NOCHANGE:=nNoChangeCount;
607 EXCEPTION
608 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
609 RAISE;
610 WHEN OTHERS THEN
611 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_TYPE',11276,inRUN_ID);
612 END;
613 END XFR_ITEM_TYPE;
614
615 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
616
617 PROCEDURE XFR_ITEM_TYPE_PROPERTY ( inRUN_ID IN PLS_INTEGER,
618 COMMIT_SIZE IN PLS_INTEGER,
619 MAX_ERR IN PLS_INTEGER,
620 INSERTS OUT NOCOPY PLS_INTEGER,
621 UPDATES OUT NOCOPY PLS_INTEGER,
622 FAILED IN OUT NOCOPY PLS_INTEGER,
623 inXFR_GROUP IN VARCHAR2
624 )IS
625
626 BEGIN
627 DECLARE CURSOR c_xfr_itemtypeprop IS
628 SELECT ITEM_TYPE_ID,PROPERTY_ID,RUN_ID,REC_STATUS ,DISPOSITION ,DELETED_FLAG,
629 CHECKOUT_USER, USER_STR01,USER_STR02,USER_STR03,USER_STR04,USER_NUM01,
630 USER_NUM02,USER_NUM03, USER_NUM04, ORIG_SYS_REF,
631 NVL(FSK_ITEMTYPE_1_1, FSK_ITEMTYPE_1_EXT) AS FSK_ITEMTYPE,
632 NVL(FSK_PROPERTY_2_1, FSK_PROPERTY_2_EXT) AS FSK_PROPERTY,
633 SRC_APPLICATION_ID
634 FROM CZ_IMP_ITEM_TYPE_PROPERTY
635 WHERE CZ_IMP_ITEM_TYPE_PROPERTY.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
636 x_xfr_itemtypeprop_f BOOLEAN:=FALSE;
637 x_error BOOLEAN:=FALSE;
638
639 p_xfr_itemtypeprop c_xfr_itemtypeprop%ROWTYPE;
640
641 /* Internal vars */
642 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
643 nInsertCount PLS_INTEGER:=0; /*Inserts */
644 nUpdateCount PLS_INTEGER:=0; /*Updates */
645 NOUPDATE_DELETED_FLAG NUMBER;
646 NOUPDATE_CREATION_DATE NUMBER;
647 NOUPDATE_LAST_UPDATE_DATE NUMBER;
648 NOUPDATE_CREATED_BY NUMBER;
649 NOUPDATE_LAST_UPDATED_BY NUMBER;
650 NOUPDATE_SECURITY_MASK NUMBER;
651 NOUPDATE_CHECKOUT_USER NUMBER;
652
653 sOrigSysRef cz_item_type_properties.orig_sys_ref%TYPE;
654
655 -- Make sure that the DataSet exists
656 BEGIN
657 -- Get the Update Flags for each column
658 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('ITEM_TYPE_PROPERTY','DELETED_FLAG',inXFR_GROUP);
659 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('ITEM_TYPE_PROPERTY','CREATION_DATE',inXFR_GROUP);
660 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('ITEM_TYPE_PROPERTY','LAST_UPDATE_DATE',inXFR_GROUP);
661 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('ITEM_TYPE_PROPERTY','CREATED_BY',inXFR_GROUP);
662 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('ITEM_TYPE_PROPERTY','LAST_UPDATED_BY',inXFR_GROUP);
663 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('ITEM_TYPE_PROPERTY','SECURITY_MASK',inXFR_GROUP);
664 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('ITEM_TYPE_PROPERTY','CHECKOUT_USER',inXFR_GROUP);
665
666 OPEN c_xfr_itemtypeprop ;
667
668 LOOP
669 IF (nCommitCount>= COMMIT_SIZE) THEN
670 BEGIN
671 COMMIT;
672 nCommitCount:=0;
673 END;
674 ELSE
678
675 nCOmmitCount:=nCommitCount+1;
676 END IF;
677 FETCH c_xfr_itemtypeprop INTO p_xfr_itemtypeprop;
679 x_xfr_itemtypeprop_f:=c_xfr_itemtypeprop%FOUND;
680 EXIT WHEN NOT x_xfr_itemtypeprop_f;
681 IF ( FAILED >= Max_Err) THEN
682 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_XFR.XFR_ITEM_TYPE_PROPERTY:MAX',11276,inRun_Id);
683 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
684 END IF;
685
686 IF(p_xfr_itemtypeprop.ORIG_SYS_REF IS NOT NULL)THEN sOrigSysRef := p_xfr_itemtypeprop.ORIG_SYS_REF;
687 ELSE sOrigSysRef := p_xfr_itemtypeprop.FSK_ITEMTYPE || ':' || p_xfr_itemtypeprop.FSK_PROPERTY;
688 END IF;
689
690 IF (p_xfr_itemtypeprop.DISPOSITION = 'I') THEN
691 BEGIN
692 INSERT INTO CZ_ITEM_TYPE_PROPERTIES (ITEM_TYPE_ID,PROPERTY_ID,
693 /* USER_NUM01,USER_NUM02, USER_NUM03,USER_NUM04,USER_STR01,USER_STR02,USER_STR03,USER_STR04,*/
694 CREATION_DATE, LAST_UPDATE_DATE, DELETED_FLAG ,CREATED_BY, LAST_UPDATED_BY,
695 SECURITY_MASK, ORIG_SYS_REF, SRC_APPLICATION_ID) VALUES
696 (p_xfr_itemtypeprop.ITEM_TYPE_ID,p_xfr_itemtypeprop.PROPERTY_ID,
697 /* p_xfr_itemtypeprop.USER_NUM01,p_xfr_itemtypeprop.USER_NUM02,p_xfr_itemtypeprop.USER_NUM03,p_xfr_itemtypeprop.USER_NUM04,
698 p_xfr_itemtypeprop.USER_STR01,p_xfr_itemtypeprop.USER_STR02,p_xfr_itemtypeprop.USER_STR03,p_xfr_itemtypeprop.USER_STR04,
699 */ SYSDATE, SYSDATE, p_xfr_itemtypeprop.DELETED_FLAG , 1, 1, NULL, sOrigSysRef, p_xfr_itemtypeprop.SRC_APPLICATION_ID);
700 nInsertCount:=nInsertCount+1;
701 BEGIN
702 UPDATE CZ_IMP_item_type_property
703 SET REC_STATUS='OK'
704 WHERE ITEM_TYPE_ID=p_xfr_itemtypeprop.ITEM_TYPE_ID
705 AND PROPERTY_ID=p_xfr_itemtypeprop.PROPERTY_ID AND RUN_ID=inRUN_ID
706 AND DISPOSITION='I';
707 END;
708 EXCEPTION
709 WHEN OTHERS THEN
710 FAILED:=FAILED +1;
711 BEGIN
712 UPDATE CZ_IMP_item_type_property
713 SET REC_STATUS='ERR'
714 WHERE ITEM_TYPE_ID=p_xfr_itemtypeprop.ITEM_TYPE_ID
715 AND PROPERTY_ID=p_xfr_itemtypeprop.PROPERTY_ID AND RUN_ID=inRUN_ID
716 AND DISPOSITION='I';
717 END;
718 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_TYPE_PROP',11276,inRUN_ID);
719 END ;
720 ELSIF (p_xfr_itemtypeprop.DISPOSITION = 'M') THEN
721 BEGIN
722 UPDATE CZ_ITEM_TYPE_PROPERTIES SET
723 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_itemtypeprop.DELETED_FLAG ,DELETED_FLAG),
724 /* USER_NUM01= DECODE(NOUPDATE_USER_NUM01, 0,p_xfr_itemtypeprop.USER_NUM01,USER_NUM01),
725 USER_NUM02= DECODE(NOUPDATE_USER_NUM02, 0,p_xfr_itemtypeprop.USER_NUM02,USER_NUM02),
726 USER_NUM03= DECODE(NOUPDATE_USER_NUM03, 0,p_xfr_itemtypeprop.USER_NUM03,USER_NUM03),
727 USER_NUM04= DECODE(NOUPDATE_USER_NUM04, 0,p_xfr_itemtypeprop.USER_NUM04,USER_NUM04),
728 USER_STR01= DECODE(NOUPDATE_USER_STR01, 0,p_xfr_itemtypeprop.USER_STR01,USER_STR01),
729 USER_STR02= DECODE(NOUPDATE_USER_STR02, 0,p_xfr_itemtypeprop.USER_STR02,USER_STR02),
730 USER_STR03= DECODE(NOUPDATE_USER_STR03, 0,p_xfr_itemtypeprop.USER_STR03,USER_STR03),
731 USER_STR04= DECODE(NOUPDATE_USER_STR04, 0,p_xfr_itemtypeprop.USER_STR04,USER_STR04),*/
732 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
733 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
734 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
735 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
736 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
737 CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,NULL,CHECKOUT_USER),
738 ORIG_SYS_REF = sOrigSysRef
739 WHERE ITEM_TYPE_ID=p_xfr_itemtypeprop.ITEM_TYPE_ID AND
740 PROPERTY_ID=p_xfr_itemtypeprop.PROPERTY_ID;
741 IF(SQL%NOTFOUND) THEN
742 FAILED:=FAILED+1;
743 ELSE
744 nUpdateCount:=nUpdateCount+1;
745 BEGIN
746 UPDATE CZ_IMP_item_type_property
747 SET REC_STATUS='OK'
748 WHERE ITEM_TYPE_ID=p_xfr_itemtypeprop.ITEM_TYPE_ID
749 AND PROPERTY_ID=p_xfr_itemtypeprop.PROPERTY_ID AND RUN_ID=inRUN_ID
750 AND DISPOSITION='M';
751 END;
752 END IF;
753
754 EXCEPTION
755 WHEN OTHERS THEN
756 FAILED:=FAILED +1;
757 BEGIN
758 UPDATE CZ_IMP_item_type_property
759 SET REC_STATUS='ERR'
760 WHERE ITEM_TYPE_ID=p_xfr_itemtypeprop.ITEM_TYPE_ID
761 AND PROPERTY_ID=p_xfr_itemtypeprop.PROPERTY_ID AND RUN_ID=inRUN_ID
765 END ;
762 AND DISPOSITION='M';
763 END;
764 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_TYPE_PROP',11276,inRUN_ID);
766
767 END IF;
768
769 END LOOP;
770 CLOSE c_xfr_itemtypeprop;
771 COMMIT;
772 INSERTS:=nInsertCount;
773 UPDATES:=nUpdateCount;
774 EXCEPTION
775 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
776 RAISE;
777 WHEN OTHERS THEN
778 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_ITEM_TYPE_PROP',11276,inRUN_ID);
779 END;
780 END XFR_ITEM_TYPE_PROPerty;
781
782 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
783
784 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
785
786 PROCEDURE XFR_PROPERTY ( inRUN_ID IN PLS_INTEGER,
787 COMMIT_SIZE IN PLS_INTEGER,
788 MAX_ERR IN PLS_INTEGER,
789 INSERTS OUT NOCOPY PLS_INTEGER,
790 UPDATES OUT NOCOPY PLS_INTEGER,
791 FAILED IN OUT NOCOPY PLS_INTEGER,
792 NOCHANGE OUT NOCOPY PLS_INTEGER,
793 inXFR_GROUP IN VARCHAR2,
794 p_rp_folder_id IN NUMBER
795 ) IS
796 BEGIN
797 DECLARE CURSOR c_xfr_property IS
798 SELECT PROPERTY_ID,PROPERTY_UNIT,DESC_TEXT,NAME,DATA_TYPE,DEF_VALUE,DEF_NUM_VALUE,RUN_ID,
799 REC_STATUS,DISPOSITION,DELETED_FLAG,CHECKOUT_USER,
800 USER_STR01,USER_STR02,USER_STR03,USER_STR04,USER_NUM01,USER_NUM02,
801 USER_NUM03,USER_NUM04,ORIG_SYS_REF,SRC_APPLICATION_ID, rec_nbr
802 FROM CZ_IMP_property WHERE CZ_IMP_property.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
803 x_xfr_property_f BOOLEAN:=FALSE;
804 x_error BOOLEAN:=FALSE;
805
806 p_xfr_property c_xfr_property%ROWTYPE;
807
808 /* Internal vars */
809 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
810 nInsertCount PLS_INTEGER:=0; /*Inserts */
811 nUpdateCount PLS_INTEGER:=0; /*Updates */
812 nNoChangeCount PLS_INTEGER:=0; /*NoChange in record count */
813
814 l_def_num_value NUMBER;
815
816 NOUPDATE_PROPERTY_UNIT NUMBER;
817 NOUPDATE_DESC_TEXT NUMBER;
818 NOUPDATE_NAME NUMBER;
819 NOUPDATE_DATA_TYPE NUMBER;
820 NOUPDATE_DEF_VALUE NUMBER;
821 NOUPDATE_DEF_NUM_VALUE NUMBER;
822 NOUPDATE_DELETED_FLAG NUMBER;
823 NOUPDATE_USER_STR01 NUMBER;
824 NOUPDATE_USER_STR02 NUMBER;
825 NOUPDATE_USER_STR03 NUMBER;
826 NOUPDATE_USER_STR04 NUMBER;
827 NOUPDATE_USER_NUM01 NUMBER;
828 NOUPDATE_USER_NUM02 NUMBER;
829 NOUPDATE_USER_NUM03 NUMBER;
830 NOUPDATE_USER_NUM04 NUMBER;
831 NOUPDATE_CREATION_DATE NUMBER;
832 NOUPDATE_LAST_UPDATE_DATE NUMBER;
833 NOUPDATE_CREATED_BY NUMBER;
834 NOUPDATE_LAST_UPDATED_BY NUMBER;
835 NOUPDATE_SECURITY_MASK NUMBER;
836 NOUPDATE_CHECKOUT_USER NUMBER;
837
838 --Bug #5162016, batch commit support.
839
840 TYPE table_of_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
841 TYPE table_of_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
842
843 t_rowid table_of_rowid;
844 t_value table_of_number;
845
846 CURSOR c_item_prop IS
847 SELECT ROWID FROM cz_item_property_values
848 WHERE property_id = p_xfr_property.property_id;
849
850 CURSOR c_ps_prop IS
851 SELECT ROWID FROM cz_ps_prop_vals
852 WHERE property_id = p_xfr_property.property_id;
853
854 -- Make sure that the DataSet exists
855 BEGIN
856
857 -- Get the Update Flags for each column
858 NOUPDATE_PROPERTY_UNIT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','PROPERTY_UNIT',inXFR_GROUP);
859 NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','DESC_TEXT',inXFR_GROUP);
860 NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','NAME',inXFR_GROUP);
861 NOUPDATE_DATA_TYPE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','DATA_TYPE',inXFR_GROUP);
862 NOUPDATE_DEF_VALUE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','DEF_VALUE',inXFR_GROUP);
863 NOUPDATE_DEF_NUM_VALUE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','DEF_NUM_VALUE',inXFR_GROUP);
864 NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','DELETED_FLAG',inXFR_GROUP);
865 NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_STR01',inXFR_GROUP);
866 NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_STR02',inXFR_GROUP);
867 NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_STR03',inXFR_GROUP);
868 NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_STR04',inXFR_GROUP);
869 NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_NUM01',inXFR_GROUP);
870 NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_NUM02',inXFR_GROUP);
871 NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_NUM03',inXFR_GROUP);
872 NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','USER_NUM04',inXFR_GROUP);
873 NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','CREATION_DATE',inXFR_GROUP);
877 NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','SECURITY_MASK',inXFR_GROUP);
874 NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','LAST_UPDATE_DATE',inXFR_GROUP);
875 NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','CREATED_BY',inXFR_GROUP);
876 NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','LAST_UPDATED_BY',inXFR_GROUP);
878 NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PROPERTIES','CHECKOUT_USER',inXFR_GROUP);
879
880 OPEN c_xfr_property;
881
882 LOOP
883 IF (nCommitCount>= COMMIT_SIZE) THEN
884 BEGIN
885 COMMIT;
886 nCommitCount:=0;
887 END;
888 ELSE
889 nCOmmitCount:=nCommitCount+1;
890 END IF;
891 FETCH c_xfr_property INTO p_xfr_property;
892
893 x_xfr_property_f:=c_xfr_property%FOUND;
894 EXIT WHEN NOT x_xfr_property_f;
895 IF ( FAILED >= Max_Err) THEN
896 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_XFR.XFR_PROPERTY:MAX',11276,inRun_Id);
897 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
898 END IF;
899
900 IF (p_xfr_property.DISPOSITION = 'I') THEN
901 BEGIN
902 IF p_xfr_property.DATA_TYPE = 8 THEN
903 --###1
904 SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO l_def_num_value FROM dual;
905 FOR i IN(SELECT language_code FROM FND_LANGUAGES
906 WHERE installed_flag in( 'B', 'I'))
907 LOOP
908 INSERT INTO cz_localized_texts (intl_text_id, localized_str,
909 language, source_lang, deleted_flag,
910 creation_date, last_update_date, created_by,
911 last_updated_by,orig_sys_ref, model_id)
912 VALUES
913 (l_def_num_value,
914 p_xfr_property.DEF_VALUE,
915 i.language_code,
916 USERENV('LANG'),
917 '0',
918 SYSDATE, SYSDATE, -UID, -UID,p_xfr_property.ORIG_SYS_REF, 0);
919 END LOOP;
920 ELSE
921 l_def_num_value := p_xfr_property.DEF_NUM_VALUE;
922 END IF;
923
924 INSERT INTO CZ_PROPERTIES (PROPERTY_ID,PROPERTY_UNIT,DESC_TEXT,NAME,
925 DATA_TYPE,DEF_VALUE,DEF_NUM_VALUE,USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
926 USER_STR01,USER_STR02,USER_STR03,USER_STR04,CREATION_DATE,
927 LAST_UPDATE_DATE,DELETED_FLAG,CHECKOUT_USER,
928 CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,ORIG_SYS_REF,SRC_APPLICATION_ID) VALUES
929 (p_xfr_property.PROPERTY_ID,p_xfr_property.PROPERTY_UNIT,
930 p_xfr_property.DESC_TEXT,p_xfr_property.NAME,p_xfr_property.DATA_TYPE,
931 p_xfr_property.DEF_VALUE,
932 l_def_num_value,
933 p_xfr_property.USER_NUM01,
934 p_xfr_property.USER_NUM02,p_xfr_property.USER_NUM03,
935 p_xfr_property.USER_NUM04,p_xfr_property.USER_STR01,
936 p_xfr_property.USER_STR02,p_xfr_property.USER_STR03,
937 p_xfr_property.USER_STR04, SYSDATE, SYSDATE,
938 p_xfr_property.DELETED_FLAG,p_xfr_property.CHECKOUT_USER,
939 1, 1, NULL, p_xfr_property.ORIG_SYS_REF,p_xfr_property.SRC_APPLICATION_ID);
940 nInsertCount:=nInsertCount+1;
941 BEGIN
942 UPDATE CZ_IMP_property
943 SET REC_STATUS='OK'
944 WHERE PROPERTY_ID=p_xfr_property.PROPERTY_ID AND RUN_ID=inRUN_ID
945 AND DISPOSITION='I';
946 END;
947
948 -- insert into an entry into cz_rp_entires for this property
949 BEGIN
950 INSERT INTO cz_rp_entries
951 (object_id,object_type,enclosing_folder,name,description)
952 VALUES (p_xfr_property.property_id,
953 'PRP',
954 p_rp_folder_id,
955 p_xfr_property.name,
956 p_xfr_property.desc_text);
957 EXCEPTION
958 WHEN OTHERS THEN
959 x_error:=CZ_UTILS.LOG_REPORT('Insert into cz_rp_entries FAILED. PROPERTY_ID:'|| p_xfr_property.property_id||'. '||SQLERRM,1,'CZ_IM_XFR.XFR_PROPERTY' ,11276,inRUN_ID);
960 END;
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 FAILED:=FAILED +1;
965 BEGIN
966 UPDATE CZ_IMP_property
967 SET REC_STATUS='ERR'
971 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_PROPERTY',11276,inRUN_ID);
968 WHERE PROPERTY_ID=p_xfr_property.PROPERTY_ID AND RUN_ID=inRUN_ID
969 AND DISPOSITION='I';
970 END;
972 END ;
973
974 ELSIF (p_xfr_property.DISPOSITION = 'M') THEN
975 BEGIN
976
977 --Bug #5162016. The data type is allowed for update and is different in this import session.
978 --Need to move all property values for this property into the correct value field.
979
980 IF(NOUPDATE_DATA_TYPE = 0 AND p_xfr_property.rec_nbr <> p_xfr_property.data_type)THEN
981
982 IF(p_xfr_property.data_type = 4)THEN
983
984 --The property was numeric and has just been made text.
985
986 BEGIN
987 OPEN c_item_prop;
988 LOOP
989
990 t_rowid.DELETE;
991
992 FETCH c_item_prop BULK COLLECT INTO t_rowid LIMIT COMMIT_SIZE;
993 EXIT WHEN c_item_prop%NOTFOUND AND t_rowid.COUNT = 0;
994
995 FORALL i IN 1..t_rowid.COUNT
996 UPDATE cz_item_property_values SET
997 property_value = TO_CHAR(property_num_value),
998 property_num_value = NULL
999 WHERE ROWID = t_rowid(i);
1000
1001 COMMIT;
1002 END LOOP;
1003 CLOSE c_item_prop;
1004
1005 --This property may have been assigned to structure nodes directly.
1006
1007 OPEN c_ps_prop;
1008 LOOP
1009
1010 t_rowid.DELETE;
1011
1012 FETCH c_ps_prop BULK COLLECT INTO t_rowid LIMIT COMMIT_SIZE;
1013 EXIT WHEN c_ps_prop%NOTFOUND AND t_rowid.COUNT = 0;
1014
1015 FORALL i IN 1..t_rowid.COUNT
1016 UPDATE cz_ps_prop_vals SET
1017 data_value = TO_CHAR(data_num_value),
1018 data_num_value = NULL
1019 WHERE ROWID = t_rowid(i);
1020
1021 COMMIT;
1022 END LOOP;
1023 CLOSE c_ps_prop;
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 if c_item_prop%ISOPEN then
1028 CLOSE c_item_prop;
1029 end if;
1030 if c_ps_prop%ISOPEN then
1031 CLOSE c_ps_prop;
1032 end if;
1033 RAISE;
1034 END;
1035
1036 ELSIF(p_xfr_property.data_type = 2)THEN
1037
1038 --The property was text and has just been made numeric.
1039
1040 BEGIN
1041
1042 --Try to convert all possible values to numbers, if this fails, no updates
1043 --will be done.
1044
1045 SELECT TO_NUMBER(property_value) BULK COLLECT INTO t_value
1046 FROM cz_item_property_values
1047 WHERE property_id = p_xfr_property.property_id;
1048
1049 SELECT TO_NUMBER(data_value) BULK COLLECT INTO t_value
1050 FROM cz_ps_prop_vals
1051 WHERE property_id = p_xfr_property.property_id;
1052
1053 OPEN c_item_prop;
1054 LOOP
1055
1056 t_rowid.DELETE;
1057
1058 FETCH c_item_prop BULK COLLECT INTO t_rowid LIMIT COMMIT_SIZE;
1059 EXIT WHEN c_item_prop%NOTFOUND AND t_rowid.COUNT = 0;
1060
1061 FORALL i IN 1..t_rowid.COUNT
1062 UPDATE cz_item_property_values SET
1063 property_num_value = TO_NUMBER(property_value),
1064 property_value = NULL
1065 WHERE ROWID = t_rowid(i);
1066
1067 COMMIT;
1068 END LOOP;
1069 CLOSE c_item_prop;
1070
1071 --This property may have been assigned to structure nodes directly.
1072
1073 OPEN c_ps_prop;
1074 LOOP
1075
1076 t_rowid.DELETE;
1077
1078 FETCH c_ps_prop BULK COLLECT INTO t_rowid LIMIT COMMIT_SIZE;
1079 EXIT WHEN c_ps_prop%NOTFOUND AND t_rowid.COUNT = 0;
1080
1081 FORALL i IN 1..t_rowid.COUNT
1082 UPDATE cz_ps_prop_vals SET
1083 data_num_value = TO_NUMBER(data_value),
1084 data_value = NULL
1085 WHERE ROWID = t_rowid(i);
1086
1087 COMMIT;
1088 END LOOP;
1089 CLOSE c_ps_prop;
1090
1091 EXCEPTION
1092 WHEN INVALID_NUMBER THEN
1093 --'Property ''%PROPERTYNAME'' cannot be converted to ''Numeric'' data type because it
1094 -- has one or more non-numeric values.'
1095 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_CONVERT_PROP', 'PROPERTYNAME', p_xfr_property.NAME),
1096 1, 'CZ_IM_XFR.XFR_PROPERTY', 11276, inRUN_ID);
1097 if c_item_prop%ISOPEN then
1098 CLOSE c_item_prop;
1099 end if;
1100 if c_ps_prop%ISOPEN then
1101 CLOSE c_ps_prop;
1102 end if;
1103 RAISE;
1104 END;
1105 END IF;
1106 END IF;
1107
1108 IF p_xfr_property.DATA_TYPE = 8 THEN
1109 IF p_xfr_property.DEF_NUM_VALUE IS NULL THEN
1110 SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO l_def_num_value FROM dual;
1111 FOR i IN(SELECT language_code FROM FND_LANGUAGES
1112 WHERE installed_flag in( 'B', 'I'))
1113 LOOP
1117 last_updated_by,orig_sys_ref, model_id)
1114 INSERT INTO cz_localized_texts (intl_text_id, localized_str,
1115 language, source_lang, deleted_flag,
1116 creation_date, last_update_date, created_by,
1118 VALUES
1119 (l_def_num_value,
1120 p_xfr_property.DEF_VALUE,
1121 i.language_code,
1122 USERENV('LANG'),
1123 '0',
1124 SYSDATE, SYSDATE, -UID, -UID,p_xfr_property.ORIG_SYS_REF, 0);
1125 END LOOP;
1126 ELSE
1127 UPDATE cz_localized_texts
1128 SET localized_str=DECODE(NOUPDATE_DEF_VALUE,0,p_xfr_property.DEF_VALUE,localized_str)
1129 WHERE intl_text_id=p_xfr_property.DEF_NUM_VALUE;
1130 END IF;
1131 ELSE
1132 l_def_num_value := p_xfr_property.DEF_NUM_VALUE;
1133 END IF;
1134
1135 UPDATE CZ_PROPERTIES SET
1136 PROPERTY_UNIT=DECODE(NOUPDATE_PROPERTY_UNIT,0,p_xfr_property.PROPERTY_UNIT, PROPERTY_UNIT),
1137 DESC_TEXT=DECODE(NOUPDATE_DESC_TEXT,0,p_xfr_property.DESC_TEXT,DESC_TEXT),
1138 NAME=DECODE(NOUPDATE_NAME,0,p_xfr_property.NAME,NAME),
1139 DATA_TYPE=DECODE(NOUPDATE_DATA_TYPE,0,p_xfr_property.DATA_TYPE,DATA_TYPE),
1140 DEF_VALUE=DECODE(NOUPDATE_DEF_VALUE,0,p_xfr_property.DEF_VALUE,DEF_VALUE),
1141 DEF_NUM_VALUE=DECODE(NOUPDATE_DEF_NUM_VALUE,0,l_def_num_value,DEF_NUM_VALUE),
1142 DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,p_xfr_property.DELETED_FLAG ,DELETED_FLAG),
1143 USER_NUM01= DECODE(NOUPDATE_USER_NUM01, 0,p_xfr_property.USER_NUM01,USER_NUM01),
1144 USER_NUM02= DECODE(NOUPDATE_USER_NUM02, 0,p_xfr_property.USER_NUM02,USER_NUM02),
1145 USER_NUM03= DECODE(NOUPDATE_USER_NUM03, 0,p_xfr_property.USER_NUM03,USER_NUM03),
1146 USER_NUM04= DECODE(NOUPDATE_USER_NUM04, 0,p_xfr_property.USER_NUM04,USER_NUM04),
1147 USER_STR01= DECODE(NOUPDATE_USER_STR01, 0,p_xfr_property.USER_STR01,USER_STR01),
1148 USER_STR02= DECODE(NOUPDATE_USER_STR02, 0,p_xfr_property.USER_STR02,USER_STR02),
1149 USER_STR03= DECODE(NOUPDATE_USER_STR03, 0,p_xfr_property.USER_STR03,USER_STR03),
1150 USER_STR04= DECODE(NOUPDATE_USER_STR04, 0,p_xfr_property.USER_STR04,USER_STR04),
1151 CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
1152 LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
1153 CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
1154 LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
1155 SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
1156 CHECKOUT_USER= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,CHECKOUT_USER)
1157 WHERE PROPERTY_ID=p_xfr_property.PROPERTY_ID;
1158
1159 IF(SQL%NOTFOUND) THEN
1160 FAILED:=FAILED+1;
1161 ELSE
1162 nUpdateCount:=nUpdateCount+1;
1163 BEGIN
1164 UPDATE CZ_IMP_property
1165 SET REC_STATUS='OK'
1166 WHERE PROPERTY_ID=p_xfr_property.PROPERTY_ID AND RUN_ID=inRUN_ID
1167 AND DISPOSITION='M';
1168 END;
1169 -- update the entry for this property in cz_rp_entries
1170 BEGIN
1171 UPDATE cz_rp_entries
1172 SET name = p_xfr_property.name, description = p_xfr_property.desc_text
1173 WHERE object_id = p_xfr_property.property_id
1174 AND object_type = 'PRP';
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 x_error:=CZ_UTILS.LOG_REPORT('Update of PROPERTY_ID:'|| p_xfr_property.property_id||' in cz_rp_entries FAILED. '||SQLERRM,1,'CZ_IM_XFR.XFR_PROPERTY' ,11276,inRUN_ID);
1178 END;
1179 END IF;
1180 EXCEPTION WHEN INVALID_NUMBER THEN
1181 FAILED:=FAILED +1;
1182 BEGIN
1183 UPDATE CZ_IMP_property
1184 SET REC_STATUS='ERR'
1185 WHERE PROPERTY_ID=p_xfr_property.PROPERTY_ID AND RUN_ID=inRUN_ID
1186 AND DISPOSITION='M';
1187 END;
1188 WHEN OTHERS THEN
1189 FAILED:=FAILED +1;
1190 BEGIN
1191 UPDATE CZ_IMP_property
1192 SET REC_STATUS='ERR'
1193 WHERE PROPERTY_ID=p_xfr_property.PROPERTY_ID AND RUN_ID=inRUN_ID
1194 AND DISPOSITION='M';
1195 END;
1199 ELSIF (p_xfr_property.DISPOSITION = 'N') THEN
1196 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_PROPERTY',11276,inRUN_ID);
1197 END ;
1198
1200 BEGIN
1201 UPDATE CZ_IMP_property
1202 SET REC_STATUS='OK'
1203 WHERE PROPERTY_ID=p_xfr_property.PROPERTY_ID AND RUN_ID=inRUN_ID
1204 AND DISPOSITION='N';
1205 END;
1206 nNoChangeCount:=nNoChangeCount+(SQL%ROWCOUNT);
1207
1208
1209
1210 END IF;
1211
1212 END LOOP;
1213 CLOSE c_xfr_property;
1214 COMMIT;
1215 INSERTS:=nInsertCount;
1216 UPDATES:=nUpdateCount;
1217 NOCHANGE:=nNoChangeCount;
1218 EXCEPTION
1219 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1220 RAISE;
1221 WHEN OTHERS THEN
1222 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IM_XFR.XFR_PROPERTY',11276,inRUN_ID);
1223 END;
1224 END XFR_PROPERTY;
1225
1226 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1227 END CZ_IMP_IM_XFR;