1 PACKAGE BODY CZ_IMP_IM_KRS AS
2 /* $Header: cziimkrb.pls 120.5.12010000.2 2008/09/29 17:27:31 kksriram ship $ */
3
4
5 PROCEDURE KRS_ITEM_MASTER(inRUN_ID IN PLS_INTEGER,
6 COMMIT_SIZE IN PLS_INTEGER,
7 MAX_ERR IN PLS_INTEGER,
8 INSERTS OUT NOCOPY PLS_INTEGER,
9 UPDATES OUT NOCOPY PLS_INTEGER,
10 FAILED IN OUT NOCOPY PLS_INTEGER,
11 DUPS OUT NOCOPY PLS_INTEGER,
12 inXFR_GROUP IN VARCHAR2
13 ) IS
14 BEGIN
15 DECLARE
16 CURSOR c_imp_itemmaster(x_usesurr_itemtype pls_integer) IS
17 SELECT ORIG_SYS_REF, SRC_APPLICATION_ID, SRC_TYPE_CODE,
18 DECODE(x_usesurr_itemtype,0,FSK_ITEMTYPE_1_1,1,FSK_ITEMTYPE_1_EXT), ROWID
19 FROM CZ_IMP_ITEM_MASTER
20 WHERE REC_STATUS IS NULL AND RUN_ID=inRUN_ID
21 ORDER BY 1,2,ROWID;
22
23 v_settings_id VARCHAR2(40);
24 v_section_name VARCHAR2(30);
25
26 CURSOR C_ITEM_TYPE_ID IS
27 SELECT VALUE FROM CZ_DB_SETTINGS
28 WHERE SECTION_NAME=v_section_name
29 AND SETTING_ID=v_settings_id;
30
31 CURSOR C_ITEM_TYPE(nItemTypeId PLS_INTEGER) IS
32 SELECT 'F' FROM CZ_ITEM_TYPES
33 WHERE ITEM_TYPE_ID=nItemTypeId;
34
35 /* cursor's data found indicator */
36 x_imp_itemmaster_itemid_f BOOLEAN:=FALSE;
37 x_onl_itemtype_itemtypeid_f BOOLEAN:=FALSE;
38 x_onl_itemmaster_itemid_f BOOLEAN:=FALSE;
39 x_onl_default_itemtypeid_f BOOLEAN:=FALSE;
40 x_error BOOLEAN:=FALSE;
41 sDfltItemTypeId CZ_DB_SETTINGS.VALUE%TYPE;
42 nOnlItemId CZ_IMP_ITEM_MASTER.ITEM_ID%TYPE;
43 nOnlItemTypeId CZ_IMP_ITEM_MASTER.ITEM_TYPE_ID%TYPE;
44 nDfltItemTypeId CZ_IMP_ITEM_MASTER.ITEM_TYPE_ID%TYPE;
45 sImpOrigSysRef CZ_IMP_ITEM_MASTER.ORIG_SYS_REF%TYPE;
46 sFSKITEMTYPE CZ_IMP_ITEM_MASTER.FSK_ITEMTYPE_1_1%TYPE;
47 sLastFSK CZ_IMP_ITEM_MASTER.REF_PART_NBR%TYPE;
48 sThisFSK CZ_IMP_ITEM_MASTER.REF_PART_NBR%TYPE;
49 sRecStatus CZ_IMP_ITEM_MASTER.REC_STATUS%TYPE;
50 sDisposition CZ_IMP_ITEM_MASTER.DISPOSITION%TYPE;
51 nImpSrcApplicationId CZ_IMP_ITEM_MASTER.SRC_APPLICATION_ID%TYPE;
52 nImpSrcTypeCode CZ_IMP_ITEM_MASTER.SRC_TYPE_CODE%TYPE;
53
54 p_onl_itemtype_itemtypeid CHAR(1);
55 /* Internal vars */
56 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
57 nErrorCount PLS_INTEGER:=0; /*Error index */
58 nInsertCount PLS_INTEGER:=0; /*Inserts */
59 nUpdateCount PLS_INTEGER:=0; /*Updates */
60 nDups PLS_INTEGER:=0; /*Dupl records */
61
62 x_usesurr_itemtype PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_TYPES',inXFR_GROUP);
63 nAllocateBlock PLS_INTEGER:=1;
64 nAllocateCounter PLS_INTEGER;
65 nNextValue NUMBER;
66
67 thisRowId ROWID;
68
69 BEGIN
70
71 v_settings_id := 'OracleSequenceIncr';
72 v_section_name := 'SCHEMA';
73
74 BEGIN
75 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
76 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
77 EXCEPTION
78 WHEN OTHERS THEN
79 nAllocateBlock:=1;
80 END;
81 nAllocateCounter:=nAllocateBlock-1;
82
83 v_settings_id := 'IMPORT_ITEM_TYPE';
84 v_section_name := 'ORAAPPS_INTEGRATE';
85
86 OPEN C_ITEM_TYPE_ID;
87 FETCH C_ITEM_TYPE_ID INTO sDfltItemTypeId;
88 IF(C_ITEM_TYPE_ID%NOTFOUND)THEN
89 nOnlItemTypeId:=0;
90 ELSIF CZ_UTILS.ISNUM(sDfltItemTypeId)=TRUE THEN
91 nOnlItemTypeId:=TO_NUMBER(sDfltItemTypeId);
92 ELSE
93 nOnlItemTypeId:=0;
94 END IF;
95 CLOSE C_ITEM_TYPE_ID;
96
97 nDfltItemTypeId := nOnlItemTypeId;
98
99 OPEN C_ITEM_TYPE(nOnlItemTypeId);
100 FETCH C_ITEM_TYPE INTO p_onl_itemtype_itemtypeid;
101 x_onl_default_itemtypeid_f:=C_ITEM_TYPE%FOUND;
102 CLOSE C_ITEM_TYPE;
103
104 OPEN c_imp_itemmaster(x_usesurr_itemtype) ;
105 LOOP
106 /* COMMIT if the buffer size is reached */
107 IF(nCommitCount>= COMMIT_SIZE) THEN
108 BEGIN
109 COMMIT;
110 nCommitCount:=0;
111 END;
112 ELSE
113 nCOmmitCount:=nCommitCount+1;
114 END IF;
115
116 sImpOrigSysRef:=NULL; sfskitemtype:=NULL;
117 FETCH c_imp_itemmaster INTO sImpOrigSysRef,
118 nImpSrcApplicationId, nImpSrcTypeCode, sfskitemtype, thisRowId;
119 sThisFSK:=sImpOrigSysRef;
120 x_imp_itemmaster_itemid_f:=c_imp_itemmaster%FOUND;
121 EXIT WHEN NOT x_imp_itemmaster_itemid_f;
122
123 /* Check The Item Data from Online Dbase */
124 DECLARE
125 CURSOR c_onl_itemmaster IS
126 SELECT ITEM_ID FROM CZ_ITEM_MASTERS
127 WHERE ORIG_SYS_REF=sImpOrigSysRef
128 AND SRC_APPLICATION_ID=nImpSrcApplicationId;
129
130 BEGIN
131 OPEN c_onl_itemmaster;
132 nOnlItemId:=NULL;
133 FETCH c_onl_itemmaster INTO nOnlItemId;
134 x_onl_itemmaster_itemid_f:=c_onl_itemmaster%FOUND;
135 CLOSE c_onl_itemmaster;
136 END;
137
138 /* Make sure that Item_Type_Id exists for this item in ITEM_TYPE table */
139 /* The following if is necessary because DECODE does not take BOOLEAN vars */
140 DECLARE
141 CURSOR c_onl_itemtype_itemtypeid IS
142 SELECT ITEM_TYPE_ID FROM CZ_ITEM_TYPES WHERE ORIG_SYS_REF=sFSKITEMTYPE;
143 BEGIN
144 nOnlItemTypeId := nDfltItemTypeId;
145 IF(sFSKITEMTYPE IS NOT NULL)THEN
146 OPEN c_onl_itemtype_itemtypeid;
147 FETCH c_onl_itemtype_itemtypeid INTO nOnlItemTypeId;
148 x_onl_itemtype_itemtypeid_f:=c_onl_itemtype_itemtypeid%FOUND;
149 CLOSE c_onl_itemtype_itemtypeid;
150 END IF;
151 IF(NOT x_onl_itemtype_itemtypeid_f)THEN
152 x_onl_itemtype_itemtypeid_f:=x_onl_default_itemtypeid_f;
153 END IF;
154 END;
155
156 IF(NOT x_onl_itemtype_itemtypeid_f OR (sImpOrigSysRef IS NULL)) THEN
157 BEGIN
158 /* The record has Item ID but no Item_type_id */
159 FAILED:=FAILED+1;
160 /* Found ITEM_ID, mark record as Modify and insert the item_id */
161 IF(sImpOrigSysRef IS NULL)THEN
162 sRecStatus:='N9';
163 ELSIF(NOT x_onl_itemtype_itemtypeid_f AND x_usesurr_itemtype=1)THEN
164 sRecStatus:='F28';
165 ELSIF(NOT x_onl_itemtype_itemtypeid_f AND x_usesurr_itemtype=0)THEN
166 sRecStatus:='F27';
167 END IF;
168 sDisposition:='R';
169 END;
170 ELSE
171 /* ItemTypeID exists, so insert or update */
172 BEGIN
173 IF(sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) THEN
174 /* This is a duplicate record */
175 sRecStatus:='DUPL';
176 sDisposition:='R';
177 nDups:=nDups+1;
178 ELSE
179 BEGIN
180 sRecStatus:='PASS';
181 IF( x_onl_itemmaster_itemid_f)THEN
182 /* Update so save also the Product_line_id */
183 sDisposition:='M';
184 nUpdateCount:=nUpdateCount+1;
185 ELSE
186 /*Insert */
187 sDisposition:='I';
188 nInsertCount:=nInsertCount+1;
189 nAllocateCounter:=nAllocateCounter+1;
190 IF(nAllocateCounter=nAllocateBlock)THEN
191 nAllocateCounter:=0;
192 SELECT CZ_ITEM_MASTERS_S.NEXTVAL INTO nNextValue FROM DUAL;
193 END IF;
194 END IF;
195 END;
196 END IF;
197 END;
198 END IF;
199 UPDATE CZ_IMP_ITEM_MASTER
200 SET ITEM_ID=DECODE(sDISPOSITION,'R',ITEM_ID,'I',nNextValue+nAllocateCounter, nOnlItemId),
201 ITEM_TYPE_ID=DECODE(sDISPOSITION,'R',ITEM_TYPE_ID,nOnlItemTypeId),
202 DISPOSITION=sDisposition,
203 REC_STATUS=sRecStatus
204 WHERE ROWID = thisRowId;
205 sLastFSK:=sImpOrigSysRef;
206
207 /* Return if MAX_ERR is reached */
208 IF (FAILED >= MAX_ERR) THEN
209 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_KRS.KRS_ITEM_MASTER:MAX',11276,inRun_Id);
210 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
211 END IF;
212
213 sDisposition:=NULL; sRecStatus:=NULL;
214 END LOOP;
215
216 /* No more data in ITEM_MASTER */
217 CLOSE c_imp_itemmaster;
218 COMMIT;
219 INSERTS:=nInsertCount;
220 UPDATES:=nUpdateCount;
221 DUPS:=nDups;
222 EXCEPTION
223 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
224 RAISE;
225 WHEN OTHERS THEN
226 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_IM_KRS.KRS_ITEM_MASTER',11276,inRun_ID);
227 END;
228 END KRS_ITEM_MASTER;
229 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
230 PROCEDURE KRS_ITEM_PROPERTY_VALUE ( inRUN_ID IN PLS_INTEGER,
231 COMMIT_SIZE IN PLS_INTEGER,
232 MAX_ERR IN PLS_INTEGER,
233 INSERTS OUT NOCOPY PLS_INTEGER,
234 UPDATES OUT NOCOPY PLS_INTEGER,
235 FAILED IN OUT NOCOPY PLS_INTEGER,
236 DUPS OUT NOCOPY PLS_INTEGER,
237 inXFR_GROUP IN VARCHAR2
238 ) IS
239
240 TYPE tFskItemMaster21 IS TABLE OF cz_imp_item_property_value.fsk_itemmaster_2_1%TYPE INDEX BY BINARY_INTEGER;
241 TYPE tFskItemMaster2Ext IS TABLE OF cz_imp_item_property_value.fsk_itemmaster_2_EXT%TYPE INDEX BY BINARY_INTEGER;
242 TYPE tFskProperty11 IS TABLE OF cz_imp_item_property_value.fsk_property_1_1%TYPE INDEX BY BINARY_INTEGER;
243 TYPE tFskProperty1Ext IS TABLE OF cz_imp_item_property_value.fsk_property_1_EXT%TYPE INDEX BY BINARY_INTEGER;
244 TYPE tOrigSysRef IS TABLE OF cz_imp_item_property_value.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
245 TYPE tItemId IS TABLE OF cz_imp_item_property_value.item_id%TYPE INDEX BY BINARY_INTEGER;
246 TYPE tPropertyId IS TABLE OF cz_imp_item_property_value.property_id%TYPE INDEX BY BINARY_INTEGER;
247
248 iFskItemMaster21 tFskItemMaster21;
249 iFskItemMaster2Ext tFskItemMaster2Ext;
250 iFskProperty11 tFskProperty11;
251 iFskProperty1Ext tFskProperty1Ext;
252 iOrigSysRef tOrigSysRef;
253 iItemId tItemId;
254 iPropertyId tPropertyId;
255
256 x_usesurr_itempropertyvalue PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_PROPERTY_VALUES',inXFR_GROUP);
257 x_usesurr_property PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_PROPERTIES',inXFR_GROUP);
258 x_usesurr_itemmaster PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_MASTERS',inXFR_GROUP);
259
260 -- passing records
261 CURSOR C1 (x_usersurr_itemmaster PLS_INTEGER, x_usesurr_property PLS_INTEGER) IS
262 SELECT a.fsk_itemmaster_2_1,a.fsk_itemmaster_2_ext, a.fsk_property_1_1, a.fsk_property_1_ext,
263 b.item_id, c.property_id, a.orig_sys_ref
264 FROM cz_imp_item_property_value a, cz_item_masters b, cz_properties c
265 WHERE b.orig_sys_ref=DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
266 AND c.orig_sys_ref=DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
267 AND b.deleted_flag = '0'
268 AND c.deleted_flag = '0'
269 AND a.rec_status IS NULL
270 AND a.run_id=inRUN_ID;
271
272 -- invalid fsk_property
273 CURSOR C2 (x_usersurr_itemmaster PLS_INTEGER, x_usesurr_property PLS_INTEGER) IS
274 SELECT a.fsk_itemmaster_2_1,a.fsk_itemmaster_2_ext, a.fsk_property_1_1, a.fsk_property_1_ext,
275 a.orig_sys_ref, b.item_id
276 FROM cz_imp_item_property_value a, cz_item_masters b
277 WHERE b.orig_sys_ref=DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
278 AND b.deleted_flag = '0'
279 AND NOT EXISTS (SELECT NULL FROM cz_properties
280 WHERE orig_sys_ref = DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
281 AND deleted_flag = '0')
282 AND a.rec_status IS NULL
283 AND a.run_id=inRUN_ID;
284
285 -- invalid fsk_itemmaster
286 CURSOR C3 (x_usersurr_itemmaster PLS_INTEGER, x_usesurr_property PLS_INTEGER) IS
287 SELECT a.fsk_itemmaster_2_1,a.fsk_itemmaster_2_ext, a.fsk_property_1_1, a.fsk_property_1_ext,
288 a.orig_sys_ref, b.property_id
289 FROM cz_imp_item_property_value a, cz_properties b
290 WHERE b.orig_sys_ref=DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
291 AND b.deleted_flag = '0'
292 AND NOT EXISTS (SELECT NULL FROM cz_item_masters
293 WHERE orig_sys_ref = DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
294 AND deleted_flag = '0')
295 AND a.rec_status IS NULL
296 AND a.run_id=inRUN_ID;
297
298 -- invalid fsk_property and fsk_itemmaster
299 CURSOR C4 (x_usersurr_itemmaster PLS_INTEGER, x_usesurr_property PLS_INTEGER) IS
300 SELECT a.fsk_itemmaster_2_1,a.fsk_itemmaster_2_ext, a.fsk_property_1_1, a.fsk_property_1_ext, a.orig_sys_ref
301 FROM cz_imp_item_property_value a
302 WHERE NOT EXISTS (SELECT NULL FROM cz_item_masters
303 WHERE orig_sys_ref = DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
304 AND deleted_flag = '0')
305 AND NOT EXISTS (SELECT NULL FROM cz_properties
306 WHERE orig_sys_ref = DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
307 AND deleted_flag = '0')
308 AND a.rec_status IS NULL
309 AND a.run_id=inRUN_ID;
310
311 -- invalid fsk_localizedtext_3_1
312 CURSOR C5(x_usesurr_itempropertyvalue PLS_INTEGER) IS
313 SELECT orig_sys_ref FROM cz_imp_item_property_value a
314 WHERE run_id=inRUN_ID AND EXISTS(SELECT NULL FROM cz_imp_property
315 WHERE run_id=inRUN_ID AND property_id=a.property_id AND
316 data_type=8 AND deleted_flag='0')
317 AND NOT EXISTS (SELECT NULL FROM cz_imp_localized_texts
318 WHERE run_id=inRUN_ID AND orig_sys_ref = DECODE(x_usesurr_itempropertyvalue,0,a.FSK_LOCALIZEDTEXT_3_1,a.FSK_LOCALIZEDTEXT_3_EXT)
319 AND deleted_flag='0');
320
321 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
322 nErrorCount PLS_INTEGER:=0; /*Error index */
323 nInsertCount PLS_INTEGER:=0; /*Inserts */
324 nUpdateCount PLS_INTEGER:=0; /*Updates */
325 nDups PLS_INTEGER:=0; /*Duplicate records */
326
327 x_error BOOLEAN:=FALSE;
328 l_msg VARCHAR2(2000);
329
330 BEGIN
331
332 OPEN C1(x_usesurr_itemmaster, x_usesurr_property);
333 LOOP
334 iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
335 iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
336 FETCH C1 BULK COLLECT INTO iFskItemMaster21,iFskItemMaster2Ext,iFskProperty11,iFskProperty1Ext,
337 iItemId, iPropertyId, iOrigSysRef
338 LIMIT COMMIT_SIZE;
339 EXIT WHEN C1%NOTFOUND AND iOrigSysRef.COUNT = 0;
340
341 IF iOrigSysRef.COUNT > 0 THEN
342
343 FORALL i IN iOrigSysRef.FIRST..iOrigSysRef.LAST
344 UPDATE cz_imp_item_property_value a
345 SET item_id=iItemId(i),
346 property_id=iPropertyId(i),
347 disposition='M',
348 rec_status='PASS'
349 WHERE run_id=inRUN_ID
350 AND orig_sys_ref=iOrigSysRef(i)
351 AND EXISTS (SELECT NULL FROM cz_item_property_values
352 WHERE orig_sys_ref = a.orig_sys_ref);
353
354 UPDATES := UPDATES + SQL%ROWCOUNT;
355 COMMIT;
356
357 --------------------- New code ------------------------------------
358
359 FORALL i IN iOrigSysRef.FIRST..iOrigSysRef.LAST
360 UPDATE cz_imp_item_property_value a
361 SET property_num_value=(SELECT DISTINCT intl_text_id FROM cz_imp_localized_texts
362 WHERE run_id=inRUN_ID AND orig_sys_ref = a.FSK_LOCALIZEDTEXT_3_1
363 AND deleted_flag='0'),
364 disposition='M',
365 rec_status='PASS'
366 WHERE run_id=inRUN_ID
367 AND orig_sys_ref=iOrigSysRef(i)
368 AND EXISTS(SELECT NULL FROM cz_imp_property
369 WHERE run_id=inRUN_ID AND property_id=iPropertyId(i) AND
370 data_type=8 AND deleted_flag='0')
371 AND EXISTS (SELECT NULL FROM cz_imp_localized_texts
372 WHERE run_id=inRUN_ID AND orig_sys_ref = a.FSK_LOCALIZEDTEXT_3_1
373 AND deleted_flag='0');
374
375 COMMIT;
376
377 --------------------- End of new code ------------------------------
378
379 FORALL i IN iOrigSysRef.FIRST..iOrigSysRef.LAST
380 UPDATE cz_imp_item_property_value a
381 SET item_id=iItemId(i),
382 property_id=iPropertyId(i),
383 disposition='I',
384 rec_status='PASS'
385 WHERE run_id=inRUN_ID
386 AND orig_sys_ref=iOrigSysRef(i)
387 AND NOT EXISTS (SELECT NULL FROM cz_item_property_values
388 WHERE orig_sys_ref = a.orig_sys_ref);
389
390 INSERTS := INSERTS + SQL%ROWCOUNT;
391 COMMIT;
392 END IF;
393 END LOOP;
394 CLOSE C1;
395
396 OPEN C2(x_usesurr_itemmaster, x_usesurr_property);
397 LOOP
398 iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
399 iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
400 FETCH C2 BULK COLLECT INTO iFskItemMaster21,iFskItemMaster2Ext,iFskProperty11,iFskProperty1Ext,iOrigSysRef,iItemId
401 LIMIT COMMIT_SIZE;
402 EXIT WHEN C2%NOTFOUND AND iOrigSysRef.COUNT = 0;
403
404 IF iOrigSysRef.COUNT > 0 THEN
405
406 FORALL i IN iOrigSysRef.FIRST..iOrigSysRef.LAST
407 UPDATE cz_imp_item_property_value
408 SET item_id=iItemId(i),
409 disposition='R',
410 rec_status='I0P1'
411 WHERE run_id=inRUN_ID
412 AND orig_sys_ref=iOrigSysRef(i);
413
414 FAILED := FAILED + SQL%ROWCOUNT;
415 COMMIT;
416 END IF;
417 END LOOP;
418 CLOSE C2;
419
420 OPEN C3(x_usesurr_itemmaster, x_usesurr_property);
421 LOOP
422 iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
423 iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
424 FETCH C3 BULK COLLECT INTO iFskItemMaster21,iFskItemMaster2Ext,iFskProperty11,iFskProperty1Ext,iOrigSysRef,iPropertyId
425 LIMIT COMMIT_SIZE;
426 EXIT WHEN C3%NOTFOUND AND iOrigSysRef.COUNT = 0;
427 IF iOrigSysRef.COUNT > 0 THEN
428
429 FORALL i IN iOrigSysRef.FIRST..iOrigSysRef.LAST
430 UPDATE cz_imp_item_property_value
431 SET property_id=iPropertyId(i),
432 disposition='R',
433 rec_status='I1P0'
434 WHERE run_id=inRUN_ID
435 AND orig_sys_ref=iOrigSysRef(i);
436
437 FAILED := FAILED + SQL%ROWCOUNT;
438 COMMIT;
439 END IF;
440 END LOOP;
441 CLOSE C3;
442
443 OPEN C4(x_usesurr_itemmaster, x_usesurr_property);
444 LOOP
445 iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
446 iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
447 FETCH C4 BULK COLLECT INTO iFskItemMaster21,iFskItemMaster2Ext,iFskProperty11,iFskProperty1Ext,iOrigSysRef
448 LIMIT COMMIT_SIZE;
449 EXIT WHEN C4%NOTFOUND AND iOrigSysRef.COUNT = 0;
450 IF iOrigSysRef.COUNT > 0 THEN
451
452 FORALL i IN iOrigSysRef.FIRST..iOrigSysRef.LAST
453 UPDATE cz_imp_item_property_value
454 SET disposition='R',
455 rec_status='I1P1'
456 WHERE run_id=inRUN_ID
457 AND orig_sys_ref=iOrigSysRef(i);
458
459 FAILED := FAILED + SQL%ROWCOUNT;
460 COMMIT;
461 END IF;
462 END LOOP;
463 CLOSE C4;
464
465 OPEN C5(x_usesurr_itempropertyvalue);
466 LOOP
467 iOrigSysRef.delete;
468 FETCH C5 BULK COLLECT INTO iOrigSysRef
469 LIMIT COMMIT_SIZE;
470 EXIT WHEN C5%NOTFOUND AND iOrigSysRef.COUNT = 0;
471 IF iOrigSysRef.COUNT > 0 THEN
472
473 FORALL i IN iOrigSysRef.FIRST..iOrigSysRef.LAST
474 UPDATE cz_imp_item_property_value
475 SET disposition='R',
476 rec_status='I1P2'
477 WHERE run_id=inRUN_ID
478 AND orig_sys_ref=iOrigSysRef(i);
479
480 FAILED := FAILED + SQL%ROWCOUNT;
481 COMMIT;
482 END IF;
483 END LOOP;
484 CLOSE C5;
485
486
487 /* Check if any properties have been deleted in APPS */
488
489 UPDATE cz_item_property_values iv
490 SET deleted_flag = '1'
491 WHERE deleted_flag = '0'
492 AND NOT EXISTS
493 (SELECT NULL FROM cz_imp_item_property_value
494 WHERE run_id = inRUN_ID AND deleted_flag = '0'
495 AND item_id = iv.item_id
496 AND property_id = iv.property_id)
497 AND EXISTS
498 (SELECT NULL FROM cz_imp_item_master
499 WHERE item_id = iv.item_id
500 AND run_id = inRUN_ID)
501 AND EXISTS
502 (SELECT NULL FROM cz_properties
503 WHERE orig_sys_ref IS NOT NULL
504 AND deleted_flag = '0'
505 AND property_id = iv.property_id);
506 COMMIT;
507 EXCEPTION
508 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
509 RAISE;
510 WHEN OTHERS THEN
511 l_msg:=CZ_UTILS.GET_TEXT('CZ_IMP_OPERATION_FAILED','ERRORTEXT',SQLERRM);
512 x_error:=CZ_UTILS.LOG_REPORT(l_msg,1,'CZ_IMP_IM_KRS.KRS_ITEM_PROPERTY_VALUE',11276,inRun_ID);
513 RAISE CZ_ADMIN.IMP_UNEXP_SQL_ERROR;
514 END KRS_ITEM_PROPERTY_VALUE ;
515 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
516 PROCEDURE KRS_ITEM_TYPE ( inRUN_ID IN PLS_INTEGER,
517 COMMIT_SIZE IN PLS_INTEGER,
518 MAX_ERR IN PLS_INTEGER,
519 INSERTS OUT NOCOPY PLS_INTEGER,
520 UPDATES OUT NOCOPY PLS_INTEGER,
521 FAILED IN OUT NOCOPY PLS_INTEGER,
522 DUPS OUT NOCOPY PLS_INTEGER,
523 inXFR_GROUP IN VARCHAR2
524 ) IS
525 BEGIN
526 DECLARE
527 CURSOR c_imp_itemtype IS
528 SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_ITEM_TYPE
529 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
530 ORDER BY 1,ROWID;
531 /* cursor's data found indicators */
532 sImpName CZ_IMP_ITEM_TYPE.NAME%TYPE;
533 nItemTypeId CZ_IMP_ITEM_TYPE.ITEM_TYPE_ID%TYPE;
534 sLastFSK CZ_IMP_ITEM_TYPE.NAME%TYPE;
535 sThisFSK CZ_IMP_ITEM_TYPE.NAME%TYPE;
536 sRecStatus CZ_IMP_ITEM_TYPE.REC_STATUS%TYPE;
537 sDisposition CZ_IMP_ITEM_TYPE.DISPOSITION%TYPE;
538 /* Column Vars */
539 x_imp_itemtype_f BOOLEAN:=FALSE;
540 x_onl_itemtype_itemtypeid_f BOOLEAN:=FALSE;
541 x_error BOOLEAN:=FALSE;
542 /* Internal vars */
543 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
544 nErrorCount PLS_INTEGER:=0; /*Error index */
545 nInsertCount PLS_INTEGER:=0; /*Inserts */
546 nUpdateCount PLS_INTEGER:=0; /*Updates */
547 nDups PLS_INTEGER:=0; /*Duplicate records */
548
549 nAllocateBlock PLS_INTEGER:=1;
550 nAllocateCounter PLS_INTEGER;
551 nNextValue NUMBER;
552
553 thisRowId ROWID;
554
555 v_settings_id VARCHAR2(40);
556 v_section_name VARCHAR2(30);
557
558 BEGIN
559
560 v_settings_id := 'OracleSequenceIncr';
561 v_section_name := 'SCHEMA';
562
563 BEGIN
564 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
565 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
566 EXCEPTION
567 WHEN OTHERS THEN
568 nAllocateBlock:=1;
569 END;
570 nAllocateCounter:=nAllocateBlock-1;
571
572 /* This type casting is necessary to use decode stmt */
573 OPEN c_imp_itemtype;
574 LOOP
575 /* COMMIT if the buffer size is reached */
576 IF (nCommitCount>= COMMIT_SIZE) THEN
577 BEGIN
578 COMMIT;
579 nCommitCount:=0;
580 END;
581 ELSE
582 nCOmmitCount:=nCommitCount+1;
583 END IF;
584
585 sImpName:=NULL;
586 FETCH c_imp_itemtype INTO sImpName, thisRowId;
587 sThisFSK:=sImpName;
588 x_imp_itemtype_f:=c_imp_itemtype%FOUND;
589 EXIT WHEN NOT x_imp_itemtype_f;
590
591 /* Check if this is an insert or update */
592 DECLARE
593 CURSOR c_onl_itemtype_itemtypeid IS
594 SELECT ITEM_TYPE_ID FROM CZ_ITEM_TYPES WHERE ORIG_SYS_REF=sImpName;
595 BEGIN
596 OPEN c_onl_itemtype_itemtypeid ;
597 nItemTypeId:=NULL;
598 FETCH c_onl_itemtype_itemtypeid INTO nItemTypeId;
599 x_onl_itemtype_itemtypeid_f:=c_onl_itemtype_itemtypeid%FOUND;
600 CLOSE c_onl_itemtype_itemtypeid;
601 END;
602 /* All foreign keys are resolved */
603 IF(sImpName IS NULL ) THEN
604 BEGIN
605 /* The record has Item ID but no Item_type_id */
606 FAILED:=FAILED+1;
607 /* Found ITEM_ID, mark record as Modify and insert the item_id */
608 sRecStatus:='N14';
609 sDisposition:='R';
610 END;
611 ELSIF(sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) THEN
612 /* This is a duplicate record */
613 sRecStatus:='DUPL';
614 sDisposition:='R';
615 nDups:=nDups+1;
616 ELSE
617 BEGIN
618 sRecStatus:='PASS';
619 IF( x_onl_itemtype_itemtypeid_f)THEN
620 /* Update so save also the Product_line_id */
621 sDisposition:='M';
622 nUpdateCount:=nUpdateCount+1;
623 ELSE
624 /*Insert */
625 sDisposition:='I';
626 nInsertCount:=nInsertCount+1;
627 nAllocateCounter:=nAllocateCounter+1;
628 IF(nAllocateCounter=nAllocateBlock)THEN
629 nAllocateCounter:=0;
630 SELECT CZ_ITEM_TYPES_S.NEXTVAL INTO nNextValue FROM DUAL;
631 END IF;
632 END IF;
633 END;
634 END IF;
635
636 UPDATE CZ_IMP_ITEM_TYPE SET
637 ITEM_TYPE_ID=DECODE(sDISPOSITION,'R',ITEM_TYPE_ID,'I',nNextValue+nAllocateCounter, nItemTypeId ),
638 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
639 WHERE ROWID = thisRowId;
640 sLastFSK:=sImpName;
641
642 IF (FAILED >= MAX_ERR) THEN
643 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_KRS.KRS_ITEM_TYPE:MAX',11276,inRun_Id);
644 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
645 END IF;
646 sDisposition:=NULL; sRecStatus:=NULL;
647 END LOOP;
648 CLOSE c_imp_itemtype;
649 COMMIT;
650 INSERTS:=nInsertCount;
651 UPDATES:=nUpdateCount;
652 DUPS:=nDups;
653 EXCEPTION
654 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
655 RAISE;
656 WHEN OTHERS THEN
657 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_IM_KRS.KRS_ITEM_TYPE',11276,inRun_ID);
658 END;
659 END KRS_ITEM_TYPE;
660 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
661 PROCEDURE KRS_ITEM_TYPE_PROPERTY ( inRUN_ID IN PLS_INTEGER,
662 COMMIT_SIZE IN PLS_INTEGER,
663 MAX_ERR IN PLS_INTEGER,
664 INSERTS OUT NOCOPY PLS_INTEGER,
665 UPDATES OUT NOCOPY PLS_INTEGER,
666 FAILED IN OUT NOCOPY PLS_INTEGER,
667 DUPS OUT NOCOPY PLS_INTEGER,
668 inXFR_GROUP IN VARCHAR2
669 ) IS
670 BEGIN
671 DECLARE
672 CURSOR c_imp_itemtypeprop(x_usesurr_itemtype PLS_INTEGER,
673 x_usesurr_property PLS_INTEGER) IS
674 SELECT DECODE(x_usesurr_itemtype,0,FSK_ITEMTYPE_1_1,1,FSK_ITEMTYPE_1_EXT),
675 DECODE(x_usesurr_property,0,FSK_PROPERTY_2_1,1,FSK_PROPERTY_2_EXT), ROWID
676 FROM CZ_IMP_ITEM_TYPE_PROPERTY WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY 1, 2,ROWID;
677 /* cursor's data found indicators */
678 nOnlItemTypeId CZ_IMP_ITEM_TYPE_PROPERTY.ITEM_TYPE_ID%TYPE;
679 nOnlPropertyId CZ_IMP_ITEM_TYPE_PROPERTY.PROPERTY_ID%TYPE;
680 sFSKITEMTYPE CZ_IMP_ITEM_TYPE_PROPERTY.FSK_ITEMTYPE_1_1%TYPE;
681 sFSKPROPERTY CZ_IMP_ITEM_TYPE_PROPERTY.FSK_PROPERTY_2_1%TYPE;
682 sLastFSK1 CZ_IMP_ITEM_TYPE_PROPERTY.FSK_ITEMTYPE_1_1%TYPE;
683 sThisFSK1 CZ_IMP_ITEM_TYPE_PROPERTY.FSK_ITEMTYPE_1_1%TYPE;
684 sLastFSK2 CZ_IMP_ITEM_TYPE_PROPERTY.FSK_PROPERTY_2_1%TYPE;
685 sThisFSK2 CZ_IMP_ITEM_TYPE_PROPERTY.FSK_PROPERTY_2_1%TYPE;
686 sRecStatus CZ_IMP_ITEM_TYPE_PROPERTY.REC_STATUS%TYPE;
687 sDisposition CZ_IMP_ITEM_TYPE_PROPERTY.DISPOSITION%TYPE;
688 /* Column Vars */
689 x_imp_itemtypeprop_f BOOLEAN:=FALSE;
690 x_onl_itemtypeprop_f BOOLEAN:=FALSE;
691 x_onl_itemtype_itemtypeid_f BOOLEAN:=FALSE;
692 x_onl_property_propertyid_f BOOLEAN:=FALSE;
693 x_error BOOLEAN:=FALSE;
694 p_onl_itemtype_itemtypeid CHAR(1):='';
695 p_onl_property_propertyid CHAR(1):='';
696 p_onl_itemtypeprop CHAR(1):='';
697 /* Internal vars */
698 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
699 nErrorCount PLS_INTEGER:=0; /*Error index */
700 nInsertCount PLS_INTEGER:=0; /*Inserts */
701 nUpdateCount PLS_INTEGER:=0; /*Updates */
702 nDups PLS_INTEGER:=0; /*Duplicate records */
703 x_usesurr_itemtypeprop PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_TYPE_PROPERTIES',inXFR_GROUP);
704 /*Use surrogates */
705 x_usesurr_itemtype PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_ITEM_TYPES',inXFR_GROUP);
706 /*Use surrogates */
707 x_usesurr_property PLS_INTEGER:=CZ_UTILS.GET_PK_USEEXPANSION_FLAG('CZ_PROPERTIES',inXFR_GROUP);
708 /*Use surrogates */
709 thisRowId ROWID;
710 BEGIN
711
712 OPEN c_imp_itemtypeprop(x_usesurr_itemtypeprop,x_usesurr_property) ;
713
714 LOOP
715 /* COMMIT if the buffer size is reached */
716 IF (nCommitCount>= COMMIT_SIZE) THEN
717 BEGIN
718 COMMIT;
719 nCommitCount:=0;
720 END;
721 ELSE
722 nCOmmitCount:=nCommitCount+1;
723 END IF;
724 sFSKITEMTYPE:=NULL; sFSKITEMTYPE:=NULL ;
725 FETCH c_imp_itemtypeprop INTO sFSKITEMTYPE,sFSKPROPERTY,thisRowId ;
726 sThisFSK1:=sFSKITEMTYPE;
727 sThisFSK2:=sFSKPROPERTY;
728
729 x_imp_itemtypeprop_f:=c_imp_itemtypeprop%FOUND;
730 EXIT WHEN NOT x_imp_itemtypeprop_f;
731 DECLARE
732 CURSOR c_onl_itemtype_itemtypeid IS
733 SELECT ITEM_TYPE_ID FROM CZ_ITEM_TYPES WHERE ORIG_SYS_REF=sFSKITEMTYPE;
734 BEGIN
735 OPEN c_onl_itemtype_itemtypeid;
736 nOnlItemTypeId:=NULL;
737 FETCH c_onl_itemtype_itemtypeid INTO nOnlItemTypeId;
738 x_onl_itemtype_itemtypeid_f:=c_onl_itemtype_itemtypeid%FOUND;
739 CLOSE c_onl_itemtype_itemtypeid;
740 END;
741 DECLARE
742 CURSOR c_onl_property_propertyid IS
743 SELECT PROPERTY_ID FROM CZ_PROPERTIES WHERE ORIG_SYS_REF=sFSKPROPERTY;
744 BEGIN
745 OPEN c_onl_property_propertyid;
746 nOnlPropertyId:=NULL;
747 FETCH c_onl_property_propertyid INTO nOnlPropertyId;
748 x_onl_property_propertyid_f:=c_onl_property_propertyid%FOUND;
749 CLOSE c_onl_property_propertyid;
750 END;
751 /* Check if this is an insert or update */
752 DECLARE
753 CURSOR c_onl_itemtypeprop IS
754 SELECT 'X' FROM CZ_ITEM_TYPE_PROPERTIES
755 WHERE ITEM_TYPE_ID=nOnlItemTypeId AND PROPERTY_ID=nOnlPropertyId;
756 BEGIN
757 OPEN c_onl_itemtypeprop ;
758 FETCH c_onl_itemtypeprop INTO p_onl_itemtypeprop;
759 x_onl_itemtypeprop_f:=c_onl_itemtypeprop%FOUND;
760 CLOSE c_onl_itemtypeprop;
761 END;
762 IF(NOT x_onl_itemtype_itemtypeid_f OR NOT x_onl_property_propertyid_f) THEN
763 BEGIN
764 /* The record has Item ID but no Item_type_id */
765 FAILED:=FAILED+1;
766 IF(NOT x_onl_itemtype_itemtypeid_f AND x_usesurr_itemtype=1 AND sFSKITEMTYPE IS NULL) THEN
767 sRecStatus:='N23';
768 ELSIF(NOT x_onl_itemtype_itemtypeid_f AND x_usesurr_itemtype=1) THEN
769 sRecStatus:='F23';
770 ELSIF(NOT x_onl_itemtype_itemtypeid_f AND x_usesurr_itemtype=0 AND sFSKITEMTYPE IS NULL) THEN
771 sRecStatus:='N22';
772 ELSIF(NOT x_onl_itemtype_itemtypeid_f AND x_usesurr_itemtype=0) THEN
773 sRecStatus:='F22';
774 ELSIF(NOT x_onl_property_propertyid_f AND x_usesurr_property=1 AND sFSKPROPERTY IS NULL) THEN
775 sRecStatus:='N25';
776 ELSIF(NOT x_onl_property_propertyid_f AND x_usesurr_property=1) THEN
777 sRecStatus:='F25';
778 ELSIF(NOT x_onl_property_propertyid_f AND x_usesurr_property=0
779 AND sFSKPROPERTY IS NULL) THEN
780 sRecStatus:='N24';
781 ELSIF(NOT x_onl_property_propertyid_f AND x_usesurr_property=0) THEN
782 sRecStatus:='F24';
783 END IF;
784 sDisposition:='R';
785 END;
786 ELSE
787 /* ItemTypeID exists, so insert or update */
788 BEGIN
789 IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 AND
790 sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2) THEN
791 /* This is a duplicate record */
792 sRecStatus:='DUPL';
793 sDisposition:='R';
794 nDups:=nDups+1;
795 ELSE
796 BEGIN
797 sRecStatus:='PASS';
798 IF( x_onl_itemtypeprop_f)THEN
799 /* Update so save also the Product_line_id */
800 sDisposition:='M';
801 nUpdateCount:=nUpdateCount+1;
802 ELSE
803 /*Insert */
804 sDisposition:='I';
805 nInsertCount:=nInsertCount+1;
806 END IF;
807 END;
808 END IF;
809 END;
810 END IF;
811 UPDATE CZ_IMP_ITEM_TYPE_PROPERTY SET
812 ITEM_TYPE_ID=DECODE(sDISPOSITION,'R',ITEM_TYPE_ID,nOnlItemTypeId),
813 PROPERTY_ID=DECODE(sDISPOSITION,'R',PROPERTY_ID,nOnlPropertyId ),
814 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
815 WHERE ROWID = thisRowId;
816 sLastFSK1:=sFSKITEMTYPE;
817 sLastFSK2:=sFSKPROPERTY;
818
819 /* Return if MAX_ERR is reached */
820 IF (FAILED >= MAX_ERR) THEN
821 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_KRS.KRS_ITEM_TYPE_PROPERTY:MAX',11276,inRun_Id);
822 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
823 END IF;
824 sDisposition:=NULL; sRecStatus:=NULL;
825 END LOOP;
826 CLOSE c_imp_itemtypeprop;
827 /* Check if item type's property that's already been imported to CZ
828 has been deleted in APPS */
829 BEGIN
830 UPDATE CZ_ITEM_TYPE_PROPERTIES
831 SET DELETED_FLAG = '1'
832 WHERE ITEM_TYPE_ID IN (SELECT ITEM_TYPE_ID FROM CZ_IMP_ITEM_TYPE
833 WHERE RUN_ID = inRUN_ID
834 AND DELETED_FLAG = '0')
835 AND PROPERTY_ID NOT IN (SELECT PROPERTY_ID FROM CZ_IMP_ITEM_TYPE_PROPERTY
836 WHERE RUN_ID = inRUN_ID
837 AND ITEM_TYPE_ID = CZ_ITEM_TYPE_PROPERTIES.ITEM_TYPE_ID
838 AND DELETED_FLAG = '0')
839 AND PROPERTY_ID IN (SELECT PROPERTY_ID FROM CZ_PROPERTIES
840 WHERE ORIG_SYS_REF IS NOT NULL
841 AND DELETED_FLAG = '0')
842 AND ORIG_SYS_REF IS NOT NULL
843 AND DELETED_FLAG = '0';
844
845 UPDATE CZ_ITEM_PROPERTY_VALUES
846 SET DELETED_FLAG = '1'
847 WHERE ITEM_ID IN (SELECT IM.ITEM_ID
848 FROM CZ_ITEM_MASTERS IM, CZ_ITEM_TYPES IT,CZ_ITEM_TYPE_PROPERTIES ITP
849 WHERE IM.ITEM_TYPE_ID = IT.ITEM_TYPE_ID
850 AND IT.ITEM_TYPE_ID = ITP.ITEM_TYPE_ID
851 AND ITP.DELETED_FLAG = '1'
852 AND CZ_ITEM_PROPERTY_VALUES.PROPERTY_ID = ITP.PROPERTY_ID);
853
854 END;
855 COMMIT;
856 INSERTS:=nInsertCount;
857 UPDATES:=nUpdateCount;
858 DUPS:=nDups;
859 EXCEPTION
860 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
861 RAISE;
862 WHEN OTHERS THEN
863 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_IM_KRS.KRS_ITEM_TYPE_PROPERTY',11276,inRun_ID);
864 END;
865 END KRS_ITEM_TYPE_PROPERTY ;
866 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
867 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
868 PROCEDURE KRS_PROPERTY ( inRUN_ID IN PLS_INTEGER,
869 COMMIT_SIZE IN PLS_INTEGER,
870 MAX_ERR IN PLS_INTEGER,
871 INSERTS OUT NOCOPY PLS_INTEGER,
872 UPDATES OUT NOCOPY PLS_INTEGER,
873 FAILED IN OUT NOCOPY PLS_INTEGER,
874 DUPS OUT NOCOPY PLS_INTEGER,
875 inXFR_GROUP IN VARCHAR2
876 ) IS
877 BEGIN
878 DECLARE
879 CURSOR c_imp_property IS
880 SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_PROPERTY
881 WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
882 ORDER BY 1,ROWID;
883 /* cursor's data found indicator*/
884 sImpName CZ_IMP_PROPERTY.NAME%TYPE;
885 nPropertyId CZ_IMP_PROPERTY.PROPERTY_ID%TYPE;
886 nPropertyName CZ_IMP_PROPERTY.NAME%TYPE;
887 sLastFSK CZ_IMP_PROPERTY.NAME%TYPE;
888 sThisFSK CZ_IMP_PROPERTY.NAME%TYPE;
889 sRecStatus CZ_IMP_PROPERTY.REC_STATUS%TYPE;
890 sDisposition CZ_IMP_PROPERTY.DISPOSITION%TYPE;
891 /* Column Vars */
892 x_imp_property_f BOOLEAN:=FALSE;
893 x_onl_property_propertyid_f BOOLEAN:=FALSE;
894 x_onl_property_propertyname_f BOOLEAN:=FALSE;
895 x_error BOOLEAN:=FALSE;
896 /* Internal vars */
897 nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
898 nErrorCount PLS_INTEGER:=0; /*Error index */
899 nInsertCount PLS_INTEGER:=0; /*Inserts */
900 nUpdateCount PLS_INTEGER:=0; /*Updates */
901 nDups PLS_INTEGER:=0; /*Duplicate records */
902
903 nAllocateBlock PLS_INTEGER:=1;
904 nAllocateCounter PLS_INTEGER;
905 nNextValue NUMBER;
906
907 thisRowId ROWID;
908
909 v_settings_id VARCHAR2(40);
910 v_section_name VARCHAR2(30);
911
912 l_data_type CZ_PROPERTIES.DATA_TYPE%TYPE;
913
914 BEGIN
915
916 v_settings_id := 'OracleSequenceIncr';
917 v_section_name := 'SCHEMA';
918
919 BEGIN
920 SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
921 WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
922 EXCEPTION
923 WHEN OTHERS THEN
924 nAllocateBlock:=1;
925 END;
926 nAllocateCounter:=nAllocateBlock-1;
927
928 /* This type casting is necessary to use decode stmt */
929 OPEN c_imp_property;
930 LOOP
931 /* COMMIT if the buffer size is reached */
932 IF (nCommitCount>= COMMIT_SIZE) THEN
933 BEGIN
934 COMMIT;
935 nCommitCount:=0;
936 END;
937 ELSE
938 nCOmmitCount:=nCommitCount+1;
939 END IF;
940 sImpName:=NULL;
941 FETCH c_imp_property INTO sImpName, thisRowId;
942 sThisFSK:=sImpName;
943 x_imp_property_f:=c_imp_property%FOUND;
944 EXIT WHEN NOT x_imp_property_f;
945 /* Check if this is an insert or update */
946 DECLARE
947 CURSOR c_onl_property_propertyid IS
948 SELECT PROPERTY_ID, data_type FROM CZ_PROPERTIES WHERE ORIG_SYS_REF=sImpName;
949 BEGIN
950 OPEN c_onl_property_propertyid ;
951 nPropertyId:=NULL;
952 l_data_type := NULL;
953 FETCH c_onl_property_propertyid INTO nPropertyId, l_data_type;
954 x_onl_property_propertyid_f:=c_onl_property_propertyid%FOUND;
955 CLOSE c_onl_property_propertyid;
956 END;
957 /* All foreign keys are resolved */
958 IF(sImpName IS NULL ) THEN
959 BEGIN
960 /* The record has Item ID but no Item_type_id */
961 FAILED:=FAILED+1;
962 /* Found ITEM_ID, mark record as Modify and insert the item_id */
963 sRecStatus:='N17';
964 sDisposition:='R';
965 END;
966 ELSIF(sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) THEN
967 /* This is a duplicate record */
968 sRecStatus:='DUPL';
969 sDisposition:='R';
970 nDups:=nDups+1;
971 ELSE
972 BEGIN
973 sRecStatus:='PASS';
974 IF( x_onl_property_propertyid_f)THEN
975 /* Update so save also the Product_line_id */
976 sDisposition:='M';
977 nUpdateCount:=nUpdateCount+1;
978 ELSE
979 BEGIN
980 DECLARE
981 CURSOR c_onl_property_propertyname IS
982 SELECT NAME FROM CZ_PROPERTIES WHERE NAME=sImpName AND ORIG_SYS_REF IS NULL AND DELETED_FLAG=0;
983 BEGIN
984 OPEN c_onl_property_propertyname;
985 FETCH c_onl_property_propertyname INTO nPropertyName;
986 x_onl_property_propertyname_f:=c_onl_property_propertyname%FOUND;
987 CLOSE c_onl_property_propertyname;
988 END;
989 IF( x_onl_property_propertyname_f)THEN
990 sRecStatus:='DUPL';
991 sDisposition:='R';
992 nDups:=nDups+1;
993 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_PROPERTY_EXISTS','NAME',nPropertyName),1,'CZ_IMP_IM_KRS.KRS_PROPERTY',11276,inRun_Id);
994 CZ_IMP_ALL.setReturnCode(cz_imp_all.CONCURRENT_WARNING,CZ_UTILS.GET_TEXT('CZ_IMP_PROPERTY_EXISTS','NAME',nPropertyName));
995 ELSE
996 /*Insert */
997 sDisposition:='I';
998 nInsertCount:=nInsertCount+1;
999 nAllocateCounter:=nAllocateCounter+1;
1000 IF(nAllocateCounter=nAllocateBlock)THEN
1001 nAllocateCounter:=0;
1002 SELECT CZ_PROPERTIES_S.NEXTVAL INTO nNextValue FROM DUAL;
1003 END IF;
1004 END IF;
1005 END;
1006 END IF;
1007 END;
1008 END IF;
1009 UPDATE CZ_IMP_PROPERTY SET
1010 PROPERTY_ID=DECODE(sDISPOSITION,'R',PROPERTY_ID,'I',nNextValue+nAllocateCounter,nPropertyId),
1011 --
1012 --Bug #5162016 - use an existing but never before used field to store the on-line property data type.
1013 --For new properties this field will stay null.
1014 --
1015 REC_NBR = l_data_type,
1016 DISPOSITION=sDisposition, REC_STATUS=sRecStatus
1017 WHERE ROWID = thisRowId;
1018 sLastFSK:=sImpName;
1019 sDisposition:=NULL; sRecStatus:=NULL;
1020 IF (FAILED >= MAX_ERR) THEN
1021 x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,'CZ_IMP_IM_KRS.KRS_PROPERTY:MAX',11276,inRun_Id);
1022 RAISE CZ_ADMIN.IMP_MAXERR_REACHED;
1023 COMMIT;
1024 END IF;
1025 END LOOP;
1026 CLOSE c_imp_property;
1027 COMMIT;
1028 INSERTS:=nInsertCount;
1029 UPDATES:=nUpdateCount;
1030 DUPS:=nDups;
1031 EXCEPTION
1032 WHEN CZ_ADMIN.IMP_MAXERR_REACHED THEN
1033 x_error:=CZ_UTILS.LOG_REPORT('raised max_err',1,'CZ_IMP_IM_KRS.KRS_PROPERTY:MAX',11276,inRun_Id);
1034 RAISE;
1035 WHEN OTHERS THEN
1036 x_error:=CZ_UTILS.LOG_REPORT(SQLERRM,1,'CZ_IMP_IM_KRS.KRS_PROPERTY',11276,inRun_ID);
1037 END;
1038 END KRS_PROPERTY ;
1039 /*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<0>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
1040 END CZ_IMP_IM_KRS;
[Home] [Help]