The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED OUT NOCOPY PLS_INTEGER,
DUPS OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
/* cursor's data found indicators */
nOnlFSKItemId CZ_IMP_PRICE.ITEM_ID%TYPE;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT DECODE(x_usesurr_itemmaster,0,FSK_ITEMMASTER_1_1,1,FSK_ITEMMASTER_1_EXT),
DECODE(x_usesurr_pricegroup,0,FSK_PRICEGROUP_2_1,1,FSK_PRICEGROUP_2_EXT), ROWID
FROM CZ_IMP_PRICE
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY 1,2,ROWID;
SELECT ITEM_ID
FROM CZ_ITEM_MASTERS
WHERE ORIG_SYS_REF=sFSKITEMMASTER;
SELECT PRICE_GROUP_ID
FROM CZ_PRICE_GROUPS
WHERE ORIG_SYS_REF=sFSKPRICEGROUP;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_price IS
SELECT 'X' FROM CZ_PRICES
WHERE ITEM_ID=nOnlFSKItemId
AND PRICE_GROUP_ID=nOnlFSKPriceGroupId;
/* Mark record as Modify and insert the item_id */
IF (NOT x_onl_itemmaster_itemid_f AND x_usesurr_itemmaster=1 AND sFSKITEMMASTER IS NULL) THEN
sRecStatus:='N29';
/* 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 */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
UPDATE CZ_IMP_PRICE
set ITEM_ID=DECODE(sDISPOSITION,'R',ITEM_ID,nOnlFSKItemId),
PRICE_GROUP_ID=DECODE(sDISPOSITION,'R',PRICE_GROUP_ID,nOnlFSKPriceGroupId),
DISPOSITION=sDisposition, REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED OUT NOCOPY PLS_INTEGER,
DUPS OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
/* cursor's data found indicators */
sOrigSysRef CZ_IMP_PRICE_GROUP.NAME%TYPE;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_PRICE_GROUP
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY 1,ROWID;
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_pricegroup_prcgrpid IS
SELECT PRICE_GROUP_ID FROM CZ_PRICE_GROUPS
WHERE ORIG_SYS_REF=sOrigSysRef;
/* Update so save the record */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_PRICE_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_PRICE_GROUP
SET price_group_id = DECODE(sDISPOSITION,'R',PRICE_GROUP_ID,'I',nNextValue+nAllocateCounter, nPriceGroupId),
DISPOSITION=sDisposition, REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;