[Home] [Help]
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
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;
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,
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'
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)
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;
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)
948:
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);