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,
inXFR_GROUP IN VARCHAR2)
IS
BEGIN
DECLARE CURSOR C_XFR_PRICE_GROUP
IS
SELECT PRICE_GROUP_ID,DESC_TEXT,NAME,CURRENCY,RUN_ID,DISPOSITION,DELETED_FLAG,
USER_STR01,USER_STR02,USER_STR03,USER_STR04,
USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,CREATION_DATE,LAST_UPDATE_DATE,
CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER,ORIG_SYS_REF
FROM CZ_IMP_PRICE_GROUP WHERE CZ_IMP_PRICE_GROUP.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
NINSERTCOUNT PLS_INTEGER:=0;
NUPDATECOUNT PLS_INTEGER:=0;
NOUPDATE_DESC_TEXT NUMBER;
NOUPDATE_NAME NUMBER;
NOUPDATE_CURRENCY NUMBER;
NOUPDATE_DELETED_FLAG NUMBER;
NOUPDATE_USER_STR01 NUMBER;
NOUPDATE_USER_STR02 NUMBER;
NOUPDATE_USER_STR03 NUMBER;
NOUPDATE_USER_STR04 NUMBER;
NOUPDATE_USER_NUM01 NUMBER;
NOUPDATE_USER_NUM02 NUMBER;
NOUPDATE_USER_NUM03 NUMBER;
NOUPDATE_USER_NUM04 NUMBER;
NOUPDATE_CREATION_DATE NUMBER;
NOUPDATE_LAST_UPDATE_DATE NUMBER;
NOUPDATE_CREATED_BY NUMBER;
NOUPDATE_LAST_UPDATED_BY NUMBER;
NOUPDATE_SECURITY_MASK NUMBER;
NOUPDATE_CHECKOUT_USER NUMBER;
NOUPDATE_ORIG_SYS_REF NUMBER;
-- GET UPDATE FLAG FOR EACH COLUMN
NOUPDATE_DESC_TEXT := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','DESC_TEXT',inXFR_GROUP);
NOUPDATE_NAME := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','NAME',inXFR_GROUP);
NOUPDATE_CURRENCY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CURRENCY',inXFR_GROUP);
NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','DELETED_FLAG',inXFR_GROUP);
NOUPDATE_USER_STR01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR01',inXFR_GROUP);
NOUPDATE_USER_STR02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR02',inXFR_GROUP);
NOUPDATE_USER_STR03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR03',inXFR_GROUP);
NOUPDATE_USER_STR04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_STR04',inXFR_GROUP);
NOUPDATE_USER_NUM01 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM01',inXFR_GROUP);
NOUPDATE_USER_NUM02 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM02',inXFR_GROUP);
NOUPDATE_USER_NUM03 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM03',inXFR_GROUP);
NOUPDATE_USER_NUM04 := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','USER_NUM04',inXFR_GROUP);
NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CREATION_DATE',inXFR_GROUP);
NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','LAST_UPDATE_DATE',inXFR_GROUP);
NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CREATED_BY',inXFR_GROUP);
NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','LAST_UPDATED_BY',inXFR_GROUP);
NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','SECURITY_MASK',inXFR_GROUP);
NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','CHECKOUT_USER',inXFR_GROUP);
NOUPDATE_ORIG_SYS_REF := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICE_GROUPS','ORIG_SYS_REF',inXFR_GROUP);
INSERT INTO CZ_PRICE_GROUPS (PRICE_GROUP_ID,DESC_TEXT,NAME,CURRENCY,
DELETED_FLAG,USER_STR01,USER_STR02,USER_STR03,USER_STR04,
USER_NUM01,USER_NUM02,USER_NUM03,USER_NUM04,
CREATION_DATE,LAST_UPDATE_DATE,
CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER,
ORIG_SYS_REF)
VALUES
(P_XFR_PRICEGROUP.PRICE_GROUP_ID,P_XFR_PRICEGROUP.DESC_TEXT,P_XFR_PRICEGROUP.NAME,P_XFR_PRICEGROUP.CURRENCY,
P_XFR_PRICEGROUP.DELETED_FLAG,P_XFR_PRICEGROUP.USER_STR01,P_XFR_PRICEGROUP.USER_STR02,
P_XFR_PRICEGROUP.USER_STR03,P_XFR_PRICEGROUP.USER_STR04,
P_XFR_PRICEGROUP.USER_NUM01,P_XFR_PRICEGROUP.USER_NUM02,
P_XFR_PRICEGROUP.USER_NUM03,P_XFR_PRICEGROUP.USER_NUM04,
SYSDATE,SYSDATE,
1,1,NULL,P_XFR_PRICEGROUP.CHECKOUT_USER,
P_XFR_PRICEGROUP.ORIG_SYS_REF);
NINSERTCOUNT:=NINSERTCOUNT+1;
UPDATE CZ_IMP_price_group
SET REC_STATUS='OK'
WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_price_group
SET REC_STATUS='ERR'
WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
UPDATE CZ_PRICE_GROUPS
SET
PRICE_GROUP_ID=P_XFR_PRICEGROUP.PRICE_GROUP_ID,
DESC_TEXT= DECODE(NOUPDATE_DESC_TEXT,0,P_XFR_PRICEGROUP.DESC_TEXT,DESC_TEXT),
NAME= DECODE(NOUPDATE_NAME,0, P_XFR_PRICEGROUP.NAME ,NAME),
CURRENCY= DECODE(NOUPDATE_CURRENCY,0, P_XFR_PRICEGROUP.CURRENCY ,CURRENCY),
USER_NUM01= DECODE(NOUPDATE_USER_NUM01,0,P_XFR_PRICEGROUP.USER_NUM01,USER_NUM01),
USER_NUM02= DECODE(NOUPDATE_USER_NUM02,0,P_XFR_PRICEGROUP.USER_NUM02,USER_NUM02),
USER_NUM03= DECODE(NOUPDATE_USER_NUM03,0,P_XFR_PRICEGROUP.USER_NUM03,USER_NUM03),
USER_NUM04= DECODE(NOUPDATE_USER_NUM04,0,P_XFR_PRICEGROUP.USER_NUM04,USER_NUM04),
USER_STR01= DECODE(NOUPDATE_USER_STR01,0,P_XFR_PRICEGROUP.USER_STR01,USER_STR01),
USER_STR02= DECODE(NOUPDATE_USER_STR02,0,P_XFR_PRICEGROUP.USER_STR02,USER_STR02),
USER_STR03= DECODE(NOUPDATE_USER_STR03,0,P_XFR_PRICEGROUP.USER_STR03,USER_STR03),
USER_STR04= DECODE(NOUPDATE_USER_STR04,0,P_XFR_PRICEGROUP.USER_STR04,USER_STR04),
CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,P_XFR_PRICEGROUP.DELETED_FLAG ,DELETED_FLAG),
CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,P_XFR_PRICEGROUP.CHECKOUT_USER,CHECKOUT_USER),
ORIG_SYS_REF= DECODE(NOUPDATE_ORIG_SYS_REF,0,P_XFR_PRICEGROUP.ORIG_SYS_REF,ORIG_SYS_REF)
WHERE PRICE_GROUP_ID=P_XFR_PRICEGROUP.PRICE_GROUP_ID;
NUPDATECOUNT :=NUPDATECOUNT+1;
UPDATE CZ_IMP_price_group
SET REC_STATUS='OK'
WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_price_group
SET REC_STATUS='ERR'
WHERE PRICE_GROUP_ID=p_xfr_pricegroup.PRICE_GROUP_ID AND RUN_ID=inRUN_ID;
INSERTS:=NINSERTCOUNT;
UPDATES:=NUPDATECOUNT;
INSERTS OUT NOCOPY PLS_INTEGER,
UPDATES OUT NOCOPY PLS_INTEGER,
FAILED OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2)
IS
BEGIN
DECLARE CURSOR C_XFR_PRICE
IS
SELECT *
FROM CZ_IMP_PRICE
WHERE CZ_IMP_PRICE.RUN_ID = inRUN_ID AND REC_STATUS='PASS';
NINSERTCOUNT PLS_INTEGER:=0;
NUPDATECOUNT PLS_INTEGER:=0;
NOUPDATE_DELETED_FLAG NUMBER;
NOUPDATE_ITEM_PRICE NUMBER;
NOUPDATE_CREATION_DATE NUMBER;
NOUPDATE_LAST_UPDATE_DATE NUMBER;
NOUPDATE_CREATED_BY NUMBER;
NOUPDATE_LAST_UPDATED_BY NUMBER;
NOUPDATE_SECURITY_MASK NUMBER;
NOUPDATE_CHECKOUT_USER NUMBER;
-- GET UPDATE FLAG FOR EACH COLUMN
NOUPDATE_DELETED_FLAG := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','DELETED_FLAG',inXFR_GROUP);
NOUPDATE_ITEM_PRICE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','ITEM_PRICE',inXFR_GROUP);
NOUPDATE_CREATION_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CREATION_DATE',inXFR_GROUP);
NOUPDATE_LAST_UPDATE_DATE := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','LAST_UPDATE_DATE',inXFR_GROUP);
NOUPDATE_CREATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CREATED_BY',inXFR_GROUP);
NOUPDATE_LAST_UPDATED_BY := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','LAST_UPDATED_BY',inXFR_GROUP);
NOUPDATE_SECURITY_MASK := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','SECURITY_MASK',inXFR_GROUP);
NOUPDATE_CHECKOUT_USER := CZ_UTILS.GET_NOUPDATE_FLAG('CZ_PRICES','CHECKOUT_USER',inXFR_GROUP);
INSERT INTO CZ_PRICES ( ITEM_ID,PRICE_GROUP_ID,ITEM_PRICE,
DELETED_FLAG,CREATION_DATE,LAST_UPDATE_DATE,
CREATED_BY,LAST_UPDATED_BY,SECURITY_MASK,CHECKOUT_USER)
VALUES
(P_XFR_PRICE.ITEM_ID,P_XFR_PRICE.PRICE_GROUP_ID,
P_XFR_PRICE.ITEM_PRICE,
P_XFR_PRICE.DELETED_FLAG,
SYSDATE,SYSDATE,1,1,NULL,P_XFR_PRICE.CHECKOUT_USER);
NINSERTCOUNT:=NINSERTCOUNT+1;
UPDATE CZ_IMP_price
SET REC_STATUS='OK'
WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_price
SET REC_STATUS='ERR'
WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
UPDATE CZ_PRICES
SET
ITEM_PRICE= DECODE(NOUPDATE_ITEM_PRICE,0,P_XFR_PRICE.ITEM_PRICE,ITEM_PRICE),
CREATION_DATE= DECODE(NOUPDATE_CREATION_DATE,0,SYSDATE,CREATION_DATE),
LAST_UPDATE_DATE= DECODE(NOUPDATE_LAST_UPDATE_DATE,0,SYSDATE,LAST_UPDATE_DATE),
DELETED_FLAG= DECODE(NOUPDATE_DELETED_FLAG,0,P_XFR_PRICE.DELETED_FLAG ,DELETED_FLAG),
CREATED_BY= DECODE(NOUPDATE_CREATED_BY,0,1,CREATED_BY),
LAST_UPDATED_BY= DECODE(NOUPDATE_LAST_UPDATED_BY,0,1,LAST_UPDATED_BY),
SECURITY_MASK= DECODE(NOUPDATE_SECURITY_MASK,0,NULL,SECURITY_MASK),
CHECKOUT_USER= DECODE(NOUPDATE_CHECKOUT_USER,0,P_XFR_PRICE.CHECKOUT_USER,CHECKOUT_USER)
WHERE
PRICE_GROUP_ID= P_XFR_PRICE.PRICE_GROUP_ID AND
ITEM_ID= P_XFR_PRICE.ITEM_ID;
NUPDATECOUNT :=NUPDATECOUNT+1;
UPDATE CZ_IMP_price
SET REC_STATUS='OK'
WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
UPDATE CZ_IMP_price
SET REC_STATUS='ERR'
WHERE PRICE_GROUP_ID=p_xfr_price.PRICE_GROUP_ID
AND ITEM_ID=P_XFR_PRICE.ITEM_ID AND RUN_ID=inRUN_ID;
INSERTS:=NINSERTCOUNT;
UPDATES:=NUPDATECOUNT;