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