[Home] [Help]
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 c_imp_contact IS
SELECT ORIG_SYS_REF , FSK_CUSTOMER_1_1, FSK_address_2_1, ROWID FROM CZ_IMP_CONTACT
WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID
ORDER BY ORIG_SYS_REF, FSK_CUSTOMER_1_1 , FSK_address_2_1, ROWID;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
SELECT CONTACT_ID FROM CZ_CONTACTS WHERE ORIG_SYS_REF=sImpOrigsysref;
SELECT CUSTOMER_ID FROM CZ_CUSTOMERS WHERE ORIG_SYS_REF= sFSKcustomer;
SELECT ADDRESS_ID FROM CZ_ADDRESSES WHERE ORIG_SYS_REF= sFSKaddress;
/* Update */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_CONTACTS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_CONTACT SET
CONTACT_ID=DECODE(sDISPOSITION,'R',CONTACT_ID,'I', nNextValue+nAllocateCounter, nOnlContactId),
CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,nonlcustomerid),
ADDRESS_ID=DECODE(sDISPOSITION,'R',ADDRESS_ID,nonladdressid),
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 c_imp_customer (x_usesurr_pricegroup PLS_INTEGER) IS
SELECT ORIG_SYS_REF, DECODE(x_usesurr_pricegroup,0,FSK_PRICEGROUP_1_1,1,FSK_PRICEGROUP_1_EXT) ,FSK_CUSTOMER_2_1, ROWID
FROM CZ_IMP_CUSTOMER WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY 1,2,3, ROWID;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
SELECT CUSTOMER_ID FROM CZ_CUSTOMERS
WHERE ORIG_SYS_REF=sImpOrigSysref;
SELECT PRICE_GROUP_ID FROM PRICE_GROUP WHERE NAME=sFSKPRICEGROUP;
SELECT CUSTOMER_ID FROM CUSTOMER WHERE ORIG_SYS_REF= sFSKCUSTOMER;
/* insert or update */
BEGIN
IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1 /*AND
sLastFSK2 IS NOT NULL AND sLastFSK2=sThisFSK2 AND
sLastFSK3 IS NOT NULL AND sLastFSK3=sThisFSK3 */) THEN
/* This is a duplicate record */
sRecStatus:='DUPL';
/* Update */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_CUSTOMERS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_CUSTOMER SET CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,'I',nNextValue+nAllocateCounter,nonlCUSTOMERid),
PRICE_LIST_ID=DECODE(sDISPOSITION,'R',PRICE_list_ID,nOnlFSKPriceGroupId),
PARENT_ID=DECODE(sDISPOSITION,'R',PARENT_ID,nonlFSKParentid),
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 c_imp_address IS
SELECT ORIG_SYS_REF, FSK_CUSTOMER_1_1, ROWID FROM CZ_IMP_ADDRESS WHERE REC_STATUS IS NULL AND RUN_ID = inRUN_ID ORDER BY ORIG_SYS_REF,FSK_CUSTOMER_1_1, ROWID;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
SELECT ADDRESS_ID FROM CZ_ADDRESSES WHERE ORIG_SYS_REF=sImpOrigSysRef;
SELECT CUSTOMER_ID FROM CZ_CUSTOMERS WHERE ORIG_SYS_REF=sFSKCUSTOMER11;
/* Insert or update */
BEGIN
IF(sLastFSK1 IS NOT NULL AND sLastFSK1=sThisFSK1) THEN
/* This is a duplicate record */
sRecStatus:='DUPL';
/* Update */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_ADDRESSES_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_ADDRESS SET ADDRESS_ID=DECODE(sDISPOSITION,
'R',ADDRESS_ID,'I',nNextValue+nAllocateCounter,
nOnlADDRESSId),
CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,
nOnlFSKCUSTOMERId),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 c_imp_address_uses IS
SELECT orig_sys_ref, fsk_address_1_1, ROWID
FROM CZ_IMP_ADDRESS_USE
WHERE rec_status IS NULL AND Run_ID = inRUN_ID
ORDER BY orig_sys_ref, fsk_address_1_1, ROWID;
nInsertCount PLS_INTEGER:=0; /*Inserts */
nUpdateCount PLS_INTEGER:=0; /*Updates */
SELECT VALUE INTO nAllocateBlock FROM CZ_DB_SETTINGS
WHERE SETTING_ID='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
SELECT address_use_id FROM CZ_ADDRESS_USES WHERE orig_sys_ref=sImpOrigSysRef;
SELECT address_id FROM CZ_ADDRESSES WHERE orig_sys_ref=sFSKaddress;
/* Update */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_ADDRESS_USES_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_ADDRESS_USE SET
ADDRESS_USE_ID=DECODE(sDisposition,'R',ADDRESS_USE_ID,'I',nNextValue+nAllocateCounter,nOnladdressusesId),
ADDRESS_ID=DECODE(sDisposition,'R',ADDRESS_ID,nOnladdressid),
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 c_imp_CUSTOMERenduser(x_usesurr_enduser PLS_INTEGER) IS
SELECT FSK_CUSTOMER_1_1,DECODE(x_usesurr_enduser,0,FSK_ENDUSER_2_1,1,FSK_ENDUSER_2_EXT), ROWID FROM CZ_IMP_CUSTOMER_END_USER 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 CUSTOMER_ID FROM CZ_CUSTOMERS WHERE ORIG_SYS_REF=sFSKCUSTOMER;
SELECT END_USER_ID FROM CZ_END_USERS WHERE LOGIN_NAME=sFSKENDUSER;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_CUSTOMERenduser IS
SELECT 'X' FROM CZ_CUSTOMER_END_USERS WHERE CUSTOMER_ID=nOnlCUSTOMERId
AND END_USER_ID=nOnlEnduserId;
/* 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_CUSTOMER_END_USER
SET CUSTOMER_ID=DECODE(sDISPOSITION,'R',CUSTOMER_ID,nOnlCUSTOMERId),
END_USER_ID=DECODE(sDISPOSITION,'R',END_USER_ID,nOnlendUserId),
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 c_imp_enduser IS
SELECT ORIG_SYS_REF, ROWID FROM CZ_IMP_END_USER 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='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
SELECT VALUE INTO sAutoCreateUsers FROM CZ_DB_SETTINGS
WHERE SETTING_ID='AUTOCREATE_IMPORTED_USERS' AND SECTION_NAME='ORAAPPS_INTEGRATE';
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_enduser_userid IS
SELECT END_USER_ID FROM CZ_END_USERS WHERE ORIG_SYS_REF=sImpOrigSysRef;
/* Update */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_END_USERS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_END_USER SET
END_USER_ID=DECODE(sDISPOSITION,'R',END_USER_ID,'I',nNextValue+nAllocateCounter,nUserId),
DISPOSITION=sDisposition,REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
UPDATE CZ_IMP_END_USER SET
LOGIN_NAME=DECODE(sDISPOSITION,'I',DECODE(LOGIN_NAME,NULL,USER||'_'||to_char(END_USER_ID),LOGIN_NAME),LOGIN_NAME)
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 c_imp_endusergroup (x_usesurr_enduser PLS_INTEGER,
x_usesurr_usergroup PLS_INTEGER) IS
SELECT DECODE(x_usesurr_enduser,0,FSK_ENDUSER_1_1,1,FSK_ENDUSER_1_EXT),DECODE(x_usesurr_usergroup,0,FSK_USERGROUP_2_1,1,FSK_USERGROUP_2_EXT), ROWID
FROM CZ_IMP_END_USER_GROUP
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 END_USER_ID FROM CZ_END_USERS WHERE ORIG_SYS_REF=sFSKENDUSER;
SELECT USER_GROUP_ID FROM CZ_USER_GROUPS WHERE GROUP_NAME=sFSKUSERGROUP;
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_endusergroup IS
SELECT 'X' FROM CZ_END_USER_GROUPS WHERE END_USER_ID=nOnlFSKUserId AND USER_GROUP_ID=nOnlFSKGroupId;
/* 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_END_USER_GROUP SET
END_USER_ID=DECODE(sDISPOSITION,'R',END_USER_ID,nOnlFSKUserId),
USER_GROUP_ID=DECODE(sDISPOSITION,'R',USER_GROUP_ID,nOnlFSKGroupId),
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 c_imp_usergroup(x_usesurr_usergroup PLS_INTEGER) IS
SELECT DECODE(x_usesurr_usergroup,0, GROUP_NAME,1,USER_STR03), ROWID FROM CZ_IMP_USER_GROUP 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='OracleSequenceIncr' AND SECTION_NAME='SCHEMA';
/* Check if this is an insert or update */
DECLARE
CURSOR c_onl_usergroup_groupid IS
SELECT user_GROUP_ID FROM CZ_USER_GROUPS WHERE group_NAME=sImpName;
/* Update so save the record */
sDisposition:='M';
nUpdateCount:=nUpdateCount+1;
/*Insert */
sDisposition:='I';
nInsertCount:=nInsertCount+1;
SELECT CZ_USER_GROUPS_S.NEXTVAL INTO nNextValue FROM DUAL;
UPDATE CZ_IMP_USER_GROUP SET
GROUP_NAME=DECODE(sDISPOSITION,'R',GROUP_NAME,sImpName),
USER_GROUP_ID=DECODE(sDISPOSITION,'R',USER_GROUP_ID,'I',
nNextValue+nAllocateCounter,nGroupId),
DISPOSITION=sDisposition, REC_STATUS=sRecStatus
WHERE ROWID = thisRowId;
INSERTS:=nInsertCount;
UPDATES:=nUpdateCount;