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