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