[Home] [Help]
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,
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,
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;
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;
SELECT ITEM_ID FROM CZ_ITEM_MASTERS
WHERE ORIG_SYS_REF=sImpOrigSysRef
AND SRC_APPLICATION_ID=nImpSrcApplicationId;
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';
/* 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,
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 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,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
CURSOR c_imp_itemtype IS
SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_ITEM_TYPE
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY 1,ROWID;
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;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_itemtype_itemtypeid IS
SELECT ITEM_TYPE_ID FROM CZ_ITEM_TYPES WHERE ORIG_SYS_REF=sImpName;
/* Found ITEM_ID, mark record as Modify and insert the item_id */
sRecStatus:='N14';
/* 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,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
CURSOR c_imp_property IS
SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_PROPERTY
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY 1,ROWID;
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;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_property_propertyid IS
SELECT PROPERTY_ID, data_type FROM CZ_PROPERTIES WHERE ORIG_SYS_REF=sImpName;
/* Found ITEM_ID, mark record as Modify and insert the item_id */
sRecStatus:='N17';
/* Update so save also the Product_line_id */
sDisposition:='M';
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;