The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
CURSOR c_imp_itemmaster(x_usesurr_itemtype pls_integer) IS
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,
LEAD_TIME,ITEM_STATUS,DELETED_FLAG,DECIMAL_QTY_FLAG,
DECODE(x_usesurr_itemtype,0,FSK_ITEMTYPE_1_1,1,FSK_ITEMTYPE_1_EXT), ROWID
FROM CZ_IMP_ITEM_MASTER
WHERE REC_STATUS IS NULL AND RUN_ID=inRUN_ID
ORDER BY 1,2,ROWID;
SELECT VALUE FROM CZ_DB_SETTINGS
WHERE SECTION_NAME=v_section_name
AND SETTING_ID=v_settings_id;
SELECT 'F' FROM CZ_ITEM_TYPES
WHERE ITEM_TYPE_ID=nItemTypeId;
nImpDeletedFlag CZ_IMP_ITEM_MASTER.DELETED_FLAG%TYPE;
nOnlDeletedFlag CZ_IMP_ITEM_MASTER.DELETED_FLAG%TYPE;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
nImpSrcApplicationId, nImpSrcTypeCode,nImpItemId,nImpItemTypeId, nImpDescText, nImpRefPartNbr, nImpQuoFlag, nImpPriUOMCode, nImpLeadTime, nImpItemStatus, nImpDeletedFlag, nImpDecQtyFlag,sfskitemtype,thisRowId;
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
WHERE ORIG_SYS_REF=sImpOrigSysRef
AND SRC_APPLICATION_ID=nImpSrcApplicationId;
FETCH c_onl_itemmaster INTO nOnlItemId,nOnlItemTypeIdIB, nOnlDescText, nOnlRefPartNbr, nOnlQuoFlag, nOnlPriUOMCode, nOnlLeadTime, nOnlItemStatus, nOnlDeletedFlag, nOnlDecQtyFlag;
SELECT ITEM_TYPE_ID FROM CZ_ITEM_TYPES WHERE ORIG_SYS_REF=sFSKITEMTYPE;
/* Found ITEM_ID, mark record as Modify and insert the item_id */
IF(sImpOrigSysRef IS NULL)THEN
sRecStatus:='N9';
/* ItemTypeID exists, so insert or update */
BEGIN
IF(sLastFSK IS NOT NULL AND sLastFSK=sThisFSK) THEN
/* This is a duplicate record */
sRecStatus:='DUPL';
AND (nvl(nImpItemStatus,0)=nvl(nOnlItemStatus,0)) AND (nImpDeletedFlag=nOnlDeletedFlag)
AND (nImpDecQtyFlag=nOnlDecQtyFlag) AND (nOnlItemTypeIdIB=nOnlItemTypeId))THEN -- VR Comparing columns for setting disposition correctly
dispositionflag:=1;
/* Update so save also the Product_line_id */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_ITEM_MASTERS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_ITEM_MASTER
SET ITEM_ID=DECODE(sDISPOSITION,'R',ITEM_ID,'I',nNextValue+nAllocateCounter, nOnlItemId),
ITEM_TYPE_ID=DECODE(sDISPOSITION,'R',ITEM_TYPE_ID,nOnlItemTypeId),
DISPOSITION=sDisposition,
REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
TYPE tFskItemMaster21 IS TABLE OF cz_imp_item_property_value.fsk_itemmaster_2_1%TYPE INDEX BY BINARY_INTEGER;
SELECT a.fsk_itemmaster_2_1,a.fsk_itemmaster_2_ext, a.fsk_property_1_1, a.fsk_property_1_ext,
b.item_id, c.property_id, a.orig_sys_ref
FROM cz_imp_item_property_value a, cz_item_masters b, cz_properties c
WHERE b.orig_sys_ref=DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
AND c.orig_sys_ref=DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
AND b.deleted_flag = '0'
AND c.deleted_flag = '0'
AND a.rec_status IS NULL
AND a.run_id=inRUN_ID;
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, b.item_id
FROM cz_imp_item_property_value a, cz_item_masters b
WHERE b.orig_sys_ref=DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
AND b.deleted_flag = '0'
AND NOT EXISTS (SELECT NULL FROM cz_properties
WHERE orig_sys_ref = DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
AND deleted_flag = '0')
AND a.rec_status IS NULL
AND a.run_id=inRUN_ID;
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, b.property_id
FROM cz_imp_item_property_value a, cz_properties b
WHERE b.orig_sys_ref=DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
AND b.deleted_flag = '0'
AND NOT EXISTS (SELECT NULL FROM cz_item_masters
WHERE orig_sys_ref = DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
AND deleted_flag = '0')
AND a.rec_status IS NULL
AND a.run_id=inRUN_ID;
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
FROM cz_imp_item_property_value a
WHERE NOT EXISTS (SELECT NULL FROM cz_item_masters
WHERE orig_sys_ref = DECODE(x_usesurr_itemmaster,1,a.fsk_itemmaster_2_ext,a.fsk_itemmaster_2_1)
AND deleted_flag = '0')
AND NOT EXISTS (SELECT NULL FROM cz_properties
WHERE orig_sys_ref = DECODE(x_usesurr_property,1,a.fsk_property_1_ext,a.fsk_property_1_1)
AND deleted_flag = '0')
AND a.rec_status IS NULL
AND a.run_id=inRUN_ID;
SELECT orig_sys_ref FROM cz_imp_item_property_value a
WHERE run_id=inRUN_ID AND EXISTS(SELECT NULL FROM cz_imp_property
WHERE run_id=inRUN_ID AND property_id=a.property_id AND
data_type=8 AND deleted_flag='0')
AND NOT EXISTS (SELECT NULL FROM cz_imp_localized_texts
WHERE run_id=inRUN_ID AND orig_sys_ref = DECODE(x_usesurr_itempropertyvalue,0,a.FSK_LOCALIZEDTEXT_3_1,a.FSK_LOCALIZEDTEXT_3_EXT)
AND deleted_flag='0');
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
UPDATE cz_imp_item_property_value a
SET item_id=iItemId(i),
property_id=iPropertyId(i),
disposition='M',
rec_status='PASS'
WHERE run_id=inRUN_ID
AND orig_sys_ref=iOrigSysRef(i)
AND EXISTS (SELECT NULL FROM cz_item_property_values
WHERE orig_sys_ref = a.orig_sys_ref);
UPDATES := UPDATES + SQL%ROWCOUNT;
UPDATE cz_imp_item_property_value a
SET property_num_value=(SELECT DISTINCT intl_text_id FROM cz_imp_localized_texts
WHERE run_id=inRUN_ID AND orig_sys_ref = a.FSK_LOCALIZEDTEXT_3_1
AND deleted_flag='0'),
disposition='M',
rec_status='PASS'
WHERE run_id=inRUN_ID
AND orig_sys_ref=iOrigSysRef(i)
AND EXISTS(SELECT NULL FROM cz_imp_property
WHERE run_id=inRUN_ID AND property_id=iPropertyId(i) AND
data_type=8 AND deleted_flag='0')
AND EXISTS (SELECT NULL FROM cz_imp_localized_texts
WHERE run_id=inRUN_ID AND orig_sys_ref = a.FSK_LOCALIZEDTEXT_3_1
AND deleted_flag='0');
UPDATE cz_imp_item_property_value a
SET disposition='N',
rec_status='PASS' /*VIGRAMAK EDIT*/
WHERE run_id=inRUN_ID
AND disposition = 'M'
AND orig_sys_ref=iOrigSysRef(i)
AND EXISTS (SELECT NULL FROM cz_item_property_values
WHERE orig_sys_ref = a.orig_sys_ref
AND property_id= iPropertyId(i)
AND nvl(property_num_value,0) = nvl(a.property_num_value,0)
AND nvl(property_value,' ')=nvl(a.property_value,' '));
UPDATE cz_imp_item_property_value a
SET item_id=iItemId(i),
property_id=iPropertyId(i),
disposition='I',
rec_status='PASS'
WHERE run_id=inRUN_ID
AND orig_sys_ref=iOrigSysRef(i)
AND NOT EXISTS (SELECT NULL FROM cz_item_property_values
WHERE orig_sys_ref = a.orig_sys_ref);
INSERTS := INSERTS + SQL%ROWCOUNT;
iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
UPDATE cz_imp_item_property_value
SET item_id=iItemId(i),
disposition='R',
rec_status='I0P1'
WHERE run_id=inRUN_ID
AND orig_sys_ref=iOrigSysRef(i);
iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
UPDATE cz_imp_item_property_value
SET property_id=iPropertyId(i),
disposition='R',
rec_status='I1P0'
WHERE run_id=inRUN_ID
AND orig_sys_ref=iOrigSysRef(i);
iFskItemMaster21.delete; iFskItemMaster2Ext.delete; iFskProperty11.delete; iFskProperty1Ext.delete;
iOrigSysRef.delete; iItemId.delete; iPropertyId.delete;
UPDATE cz_imp_item_property_value
SET disposition='R',
rec_status='I1P1'
WHERE run_id=inRUN_ID
AND orig_sys_ref=iOrigSysRef(i);
iOrigSysRef.delete;
UPDATE cz_imp_item_property_value
SET disposition='R',
rec_status='I1P2'
WHERE run_id=inRUN_ID
AND orig_sys_ref=iOrigSysRef(i);
/* Check if any properties have been deleted in APPS */
UPDATE cz_item_property_values iv
SET deleted_flag = '1'
WHERE deleted_flag = '0'
AND NOT EXISTS
(SELECT NULL FROM cz_imp_item_property_value
WHERE run_id = inRUN_ID AND deleted_flag = '0'
AND item_id = iv.item_id
AND property_id = iv.property_id)
AND EXISTS
(SELECT NULL FROM cz_imp_item_master
WHERE item_id = iv.item_id
AND run_id = inRUN_ID)
AND EXISTS
(SELECT NULL FROM cz_properties
WHERE orig_sys_ref IS NOT NULL
AND deleted_flag = '0'
AND property_id = iv.property_id);
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS OUT NOCOPY PLS_INTEGER,
NOCHANGE IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
CURSOR c_imp_itemtype IS
SELECT ORIG_SYS_REF, ITEM_TYPE_ID,DESC_TEXT,NAME,DELETED_FLAG,SRC_APPLICATION_ID,ROWID FROM CZ_IMP_ITEM_TYPE
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY 1,ROWID;
nImpDeletedFlag CZ_IMP_ITEM_TYPE.DELETED_FLAG%TYPE;
nOnlDeletedFlag CZ_IMP_ITEM_TYPE.DELETED_FLAG%TYPE;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
FETCH c_imp_itemtype INTO sImpName,nImpItemType,nImpDescText,nImpModelName,nImpDeletedFlag,nImpSrcAppId,thisRowId;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_itemtype_itemtypeid IS
SELECT ITEM_TYPE_ID,DESC_TEXT,NAME,DELETED_FLAG,SRC_APPLICATION_ID,ORIG_SYS_REF
FROM CZ_ITEM_TYPES
WHERE ORIG_SYS_REF=sImpName;
FETCH c_onl_itemtype_itemtypeid INTO nItemTypeId,nOnlDescText,nOnlModelName,nOnlDeletedFlag,nOnlSrcAppId,nOnlName;
/* Found ITEM_ID, mark record as Modify and insert the item_id */
sRecStatus:='N14';
AND (nOnlDeletedFlag=nImpDeletedFlag) AND (nOnlSrcAppId=nImpSrcAppId) AND (nOnlName=sImpName)) THEN
dispositionflag:=1;
/* Update so save also the Product_line_id */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_ITEM_TYPES_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_ITEM_TYPE SET
ITEM_TYPE_ID=DECODE(sDISPOSITION,'R',ITEM_TYPE_ID,'I',nNextValue+nAllocateCounter, nItemTypeId ),
DISPOSITION=sDisposition, REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
CURSOR c_imp_itemtypeprop(x_usesurr_itemtype PLS_INTEGER,
x_usesurr_property PLS_INTEGER) IS
SELECT DECODE(x_usesurr_itemtype,0,FSK_ITEMTYPE_1_1,1,FSK_ITEMTYPE_1_EXT),
DECODE(x_usesurr_property,0,FSK_PROPERTY_2_1,1,FSK_PROPERTY_2_EXT), ROWID
FROM CZ_IMP_ITEM_TYPE_PROPERTY WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY 1, 2,ROWID;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT ITEM_TYPE_ID FROM CZ_ITEM_TYPES WHERE ORIG_SYS_REF=sFSKITEMTYPE;
SELECT PROPERTY_ID FROM CZ_PROPERTIES WHERE ORIG_SYS_REF=sFSKPROPERTY;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_itemtypeprop IS
SELECT 'X' FROM CZ_ITEM_TYPE_PROPERTIES
WHERE ITEM_TYPE_ID=nOnlItemTypeId AND PROPERTY_ID=nOnlPropertyId;
/* ItemTypeID exists, so insert or update */
BEGIN
IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 AND
sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2) THEN
/* This is a duplicate record */
sRecStatus:='DUPL';
/* Update so save also the Product_line_id */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
UPDATE CZ_IMP_ITEM_TYPE_PROPERTY SET
ITEM_TYPE_ID=DECODE(sDISPOSITION,'R',ITEM_TYPE_ID,nOnlItemTypeId),
PROPERTY_ID=DECODE(sDISPOSITION,'R',PROPERTY_ID,nOnlPropertyId ),
DISPOSITION=sDisposition, REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
has been deleted in APPS */
BEGIN
UPDATE CZ_ITEM_TYPE_PROPERTIES
SET DELETED_FLAG = '1'
WHERE ITEM_TYPE_ID IN (SELECT ITEM_TYPE_ID FROM CZ_IMP_ITEM_TYPE
WHERE RUN_ID = inRUN_ID
AND DELETED_FLAG = '0')
AND PROPERTY_ID NOT IN (SELECT PROPERTY_ID FROM CZ_IMP_ITEM_TYPE_PROPERTY
WHERE RUN_ID = inRUN_ID
AND ITEM_TYPE_ID = CZ_ITEM_TYPE_PROPERTIES.ITEM_TYPE_ID
AND DELETED_FLAG = '0')
AND PROPERTY_ID IN (SELECT PROPERTY_ID FROM CZ_PROPERTIES
WHERE ORIG_SYS_REF IS NOT NULL
AND DELETED_FLAG = '0')
AND ORIG_SYS_REF IS NOT NULL
AND DELETED_FLAG = '0';
UPDATE CZ_ITEM_PROPERTY_VALUES
SET DELETED_FLAG = '1'
WHERE ITEM_ID IN (SELECT IM.ITEM_ID
FROM CZ_ITEM_MASTERS IM, CZ_ITEM_TYPES IT,CZ_ITEM_TYPE_PROPERTIES ITP
WHERE IM.ITEM_TYPE_ID = IT.ITEM_TYPE_ID
AND IT.ITEM_TYPE_ID = ITP.ITEM_TYPE_ID
AND ITP.DELETED_FLAG = '1'
AND CZ_ITEM_PROPERTY_VALUES.PROPERTY_ID = ITP.PROPERTY_ID);
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS OUT NOCOPY PLS_INTEGER,
NOCHANGE OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
CURSOR c_imp_property IS
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
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY 1,ROWID;
nImpDeletedFlag CZ_IMP_PROPERTY.DELETED_FLAG%TYPE;
nOnlDeletedFlag CZ_IMP_PROPERTY.DELETED_FLAG%TYPE;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID=v_settings_id AND SECTION_NAME=v_section_name;
FETCH c_imp_property INTO sImpName, nImpPropertyId,nImpPropUnit,nImpDescText,nImpName,nImpDataType,nImpDefValue,nImpDeletedFlag,nImpDefNumValue,thisRowId;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_property_propertyid IS
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;
FETCH c_onl_property_propertyid INTO nPropertyId, l_data_type,nOnlPropUnit,nOnlDescText,nOnlName,nOnlDefValue,nOnlDeletedFlag,nOnlOrigSysRef,nOnlDefNumValue;
/* Found ITEM_ID, mark record as Modify and insert the item_id */
sRecStatus:='N17';
AND (nOnlDeletedFlag=nImpDeletedFlag) AND (nImpDescText=nOnlDescText) AND (nOnlName=nImpName)AND (nImpDataType=l_data_type)
AND (nvl(nImpDefValue,0)=nvl(nOnlDefValue,0)) AND (nvl(nImpDefNumValue,0)=nvl(nOnlDefNumValue,0)))THEN
dispositionflag:=1;
nUpdateCount:=nUpdateCount+1;
SELECT NAME FROM CZ_PROPERTIES WHERE NAME=sImpName AND ORIG_SYS_REF IS NULL AND DELETED_FLAG=0;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_PROPERTIES_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_PROPERTY SET
PROPERTY_ID=DECODE(sDISPOSITION,'R',PROPERTY_ID,'I',nNextValue+nAllocateCounter,nPropertyId),
--
--Bug #5162016 - use an existing but never before used field to store the on-line property data type.
--For new properties this field will stay null.
--
REC_NBR = l_data_type,
DISPOSITION=sDisposition,
REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;