[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DELETED_FLAG, SRC_APPLICATION_ID, SRC_TYPE_CODE, DECIMAL_QTY_FLAG, ROWID
FROM CZ_IMP_ITEM_MASTER
WHERE REC_STATUS IS NULL
AND RUN_ID = inRUN_ID;
IF (p_imp_itemmaster.DELETED_FLAG IS NULL) THEN
BEGIN
UPDATE CZ_IMP_ITEM_MASTER
SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG)
WHERE ROWID = p_imp_itemmaster.ROWID;
UPDATE CZ_IMP_ITEM_MASTER SET DECIMAL_QTY_FLAG = '0'
WHERE ROWID = p_imp_itemmaster.ROWID;
UPDATE CZ_IMP_ITEM_MASTER
SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
SRC_APPLICATION_ID = cnDefSrcAppId,
SRC_TYPE_CODE = cnDefSrcTypeCode
WHERE ROWID = p_imp_itemmaster.ROWID;
UPDATE CZ_IMP_ITEM_MASTER
SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
SRC_APPLICATION_ID = cnDefSrcAppId
WHERE ROWID = p_imp_itemmaster.ROWID;
UPDATE CZ_IMP_ITEM_MASTER
SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG),
SRC_TYPE_CODE = cnDefSrcTypeCode
WHERE ROWID = p_imp_itemmaster.ROWID;
SELECT a.DELETED_FLAG,a.PROPERTY_VALUE,a.PROPERTY_NUM_VALUE,
a.FSK_PROPERTY_1_1,a.FSK_PROPERTY_1_EXT,a.ORIG_SYS_REF,a.DISPOSITION,a.REC_STATUS,b.data_type
FROM CZ_IMP_ITEM_PROPERTY_VALUE a, cz_properties b
WHERE a.REC_STATUS IS NULL AND a.RUN_ID = inRUN_ID
AND (b.orig_sys_ref = a.fsk_property_1_1 OR b.orig_sys_ref = a.fsk_property_1_ext)
AND b.deleted_flag='0';
TYPE tDeletedFlag IS TABLE OF cz_imp_item_property_value.deleted_flag%TYPE INDEX BY BINARY_INTEGER;
iDeletedFlag tDeletedFlag;
insert_end_time NUMBER;
iDeletedFlag.delete; iFskProperty.delete;
iPropertyValue.delete; iRecStatus.delete;
iPropertyNumValue.delete; iDisposition.delete;
iFskPropertyExt.delete; iDataType.delete;
iDeletedFlag,iPropertyValue,iPropertyNumValue,
iFskProperty,iFskPropertyExt,iOrigSysRef,iDisposition,iRecStatus,iDataType
LIMIT COMMIT_SIZE;
EXIT WHEN iDeletedFlag.COUNT = 0 AND c_imp_itempropertyvalue%NOTFOUND;
IF iDeletedFlag.COUNT > 0 THEN
IF (FAILED >= MAX_ERR) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_MAXERR_REACHED'),1,
'CZ_IMP_IM_MAIN.CND_ITEM_PROPERTY_VALUE:MAX',11276,inRun_Id);
FOR i IN iDeletedFlag.FIRST..iDeletedFlag.LAST LOOP
IF iDeletedFlag(i) IS NULL THEN
iDeletedFlag(i) := '0';
FORALL i IN iDeletedFlag.FIRST..iDeletedFlag.LAST
UPDATE cz_imp_item_property_value
SET deleted_flag = iDeletedFlag(i),
property_value = iPropertyValue(i),
property_num_value = iPropertyNumValue(i),
disposition = iDisposition(i),
rec_status = iRecStatus(i)
WHERE orig_sys_ref = iOrigSysRef(i)
AND run_id = inRun_id
AND rec_status IS NULL;
SELECT DELETED_FLAG, ROWID FROM CZ_IMP_ITEM_TYPE WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
IF (p_imp_itemtype.DELETED_FLAG IS NULL) THEN
BEGIN
UPDATE CZ_IMP_ITEM_TYPE SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_itemtype.ROWID;
SELECT DELETED_FLAG, ROWID FROM CZ_IMP_ITEM_TYPE_PROPERTY WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
IF (p_imp_itemtypeprop.DELETED_FLAG IS NULL) THEN
BEGIN
UPDATE CZ_IMP_ITEM_TYPE_PROPERTY SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_itemtypeprop.ROWID;
SELECT DELETED_FLAG, DATA_TYPE, ROWID
FROM CZ_IMP_property WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID;
IF (p_imp_property.DELETED_FLAG IS NULL) THEN
BEGIN
UPDATE CZ_IMP_property SET DELETED_FLAG=DECODE(DELETED_FLAG,NULL,'0',DELETED_FLAG) WHERE ROWID = p_imp_property.ROWID;
UPDATE CZ_IMP_property SET DATA_TYPE = 4 WHERE ROWID = p_imp_property.ROWID;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
/* Internal vars */
nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
CZ_IMP_IM_KRS.KRS_ITEM_MASTER (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
CZ_IMP_IM_XFR.XFR_ITEM_MASTER (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
/* Report Insert Errors */
IF (nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'IMP_IM.MAIN_ITEM_MASTER ',11276,inRun_ID);
/* Report Update Errors */
IF (nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'IMP_IM.MAIN_ITEM_MASTER ',11276,inRun_ID);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
/* Internal vars */
nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
CZ_IMP_IM_KRS.KRS_ITEM_PROPERTY_VALUE (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
CZ_IMP_IM_XFR.XFR_ITEM_PROPERTY_VALUE(inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
/* Report Insert Errors */
IF (nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE',11276,inRun_ID);
/* Report Update Errors */
IF (nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_ITEM_PROPERTY_VALUE',11276,inRun_ID);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
/* Internal vars */
nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
CZ_IMP_IM_KRS.KRS_ITEM_TYPE (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
CZ_IMP_IM_XFR.XFR_ITEM_TYPE (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
/* Report Insert Errors */
IF (nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE',11276,inRun_ID);
/* Report Update Errors */
IF (nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE',11276,inRun_ID);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2
) IS
BEGIN
DECLARE
/* Internal vars */
nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
CZ_IMP_IM_KRS.KRS_ITEM_TYPE_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
CZ_IMP_IM_XFR.XFR_ITEM_TYPE_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP);
/* Report Insert Errors */
IF (nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY',11276,inRun_ID);
/* Report Update Errors */
IF (nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_ITEM_TYPE_PROPERTY',11276,inRun_ID);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
INSERTS IN OUT NOCOPY PLS_INTEGER,
UPDATES IN OUT NOCOPY PLS_INTEGER,
FAILED IN OUT NOCOPY PLS_INTEGER,
DUPS IN OUT NOCOPY PLS_INTEGER,
inXFR_GROUP IN VARCHAR2,
p_rp_folder_id IN NUMBER
) IS
BEGIN
DECLARE
/* Interal vars */
nCommitCount PLS_INTEGER:=0; /*COMMIT buffer index */
nXfrInsertCount PLS_INTEGER:=0; /*Inserts */
nXfrUpdateCount PLS_INTEGER:=0; /*Updates */
insert_end_time number;
SELECT 'X' INTO dummy FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=inRUN_ID;
UPDATE CZ_XFR_RUN_INFOS SET
STARTED=SYSDATE,
LAST_ACTIVITY=SYSDATE
WHERE RUN_ID=inRUN_ID;
INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY)
VALUES(inRUN_ID,SYSDATE,SYSDATE);
CZ_IMP_IM_KRS.KRS_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,INSERTS,UPDATES,FAILED,DUPS,inXFR_GROUP);
CZ_IMP_IM_XFR.XFR_PROPERTY (inRUN_ID,COMMIT_SIZE,MAX_ERR,nXfrInsertCount,nXfrUpdateCount,FAILED,inXFR_GROUP, p_rp_folder_id);
/* Report Insert Errors */
IF (nXfrInsertCount<> INSERTS) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INSERTERRORS','RESOLVED',to_char(INSERTS),'ACTUAL',to_char(nXfrInsertCount)),1,'CZ_IM_MAIN.MAIN_PROPERTY',11276,inRun_ID);
/* Report Update Errors */
IF (nXfrUpdateCount<> UPDATES) THEN
x_error:=CZ_UTILS.LOG_REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_UPDATEERRORS','RESOLVED',to_char(UPDATES),'ACTUAL',to_char(nXfrUpdateCount)),1,'CZ_IM_MAIN.MAIN_PROPERTY',11276,inRun_ID);
INSERTS:=nXfrInsertCount;
UPDATES:=nXfrUpdateCount;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM CZ_IMP_item_master
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM CZ_IMP_item_property_value
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM CZ_IMP_item_type
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM CZ_IMP_item_type_property
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;
DELETE FROM CZ_XFR_RUN_RESULTS WHERE RUN_ID=inRUN_ID AND IMP_TABLE=v_table_name;
SELECT DISPOSITION,REC_STATUS,COUNT(*)
FROM CZ_IMP_property
WHERE RUN_ID = inRUN_ID
GROUP BY DISPOSITION,REC_STATUS;
INSERT INTO CZ_XFR_RUN_RESULTS(RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
VALUES(inRUN_ID,v_table_name,ins_disposition,ins_rec_status,ins_rec_count);
SELECT SUM(NVL(RECORDS,0)) FROM CZ_XFR_RUN_RESULTS
WHERE REC_STATUS<>v_ok AND RUN_ID=inRUN_ID
AND IMP_TABLE=v_table_name;
UPDATE CZ_XFR_RUN_INFOS
SET TOTAL_ERRORS=NVL(TOTAL_ERRORS,0)+NVL(nErrors,0),
COMPLETED=v_completed
WHERE RUN_ID=inRUN_ID;