Untitled
user_7996555
plain_text
a month ago
4.8 kB
3
Indexable
Never
--cap nhat trang thai yeu cau nhan vien PROCEDURE UPDATE_ADDRESS_REQUEST (pCUS_ID NUMBER, pACTION NUMBER, pGROUPADDRESS_ID NUMBER) IS vID NUMBER; vROLE NUMBER; vCHECK NUMBER; BEGIN -- RAISE_APPLICATION_ERROR(-20001, 'INVALID USER'); IF pACTION = 1 THEN SELECT COUNT (*) INTO vID FROM CUS_SETTING_ADDRESS WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID; IF vID > 0 THEN SELECT PARENT_ID INTO vID FROM CUS_SETTING_ADDRESS WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID; SELECT CUS_ROLE INTO vROLE FROM CUS_CUSTOMER WHERE CUS_ID = vID; END IF; UPDATE CUS_SETTING_ADDRESS SET IS_ACTIVE = 1 WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID; UPDATE CUS_SETTING_ADDRESS SET IS_ACTIVE = 2 WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID <> pGROUPADDRESS_ID; UPDATE CUS_CUSTOMER SET PARNER = vID, CUS_ROLE = vROLE WHERE CUS_ID = pCUS_ID; UPDATE CUS_GROUPADDRESS SET ACTIVE = 'N' WHERE GROUPADDRESS_ID <> pGROUPADDRESS_ID AND CUS_ID = pCUS_ID AND ACTIVE = 'Y'; SELECT COUNT (*) INTO vCHECK FROM CUS_GROUPADDRESS WHERE GROUPADDRESS_ID = pGROUPADDRESS_ID AND CUS_ID = pCUS_ID; IF vCHECK > 0 THEN UPDATE CUS_GROUPADDRESS SET ACTIVE = 'Y', CUS_ID_NUMBER = vID WHERE GROUPADDRESS_ID = pGROUPADDRESS_ID AND CUS_ID = pCUS_ID; ELSE INSERT INTO CUS_GROUPADDRESS (GROUPADDRESS_ID, CUS_ID, POST_ID, NAME, VALUE, EVTPCODE, PHONE, ADDRESS, PROVINCE_ID, DISTRICT_ID, WARDS_ID, ROUTE, COUNTRY, TYPE, CREATEDBYUSERID, CREATEDONDATE, LASTMODIFIEDBYUSERID, LASTMODIFIEDONDATE, ACTIVE, CUS_ID_NUMBER) SELECT CUS_GROUPADDRESS_SEQ.NEXTVAL, CUS_ID, 0, DISPLAYNAME, 0, '', PHONE, ADDRESS, PROVINCE_ID, DISTRICT_ID, WARDS_ID, 0, 0, 2, CUS_ID, SYSDATE, CUS_ID, SYSDATE, 'Y', vID FROM CUS_CUSTOMER WHERE CUS_ID = pCUS_ID; END IF; ELSE --UPDATE CUS_SETTING_ADDRESS SET IS_ACTIVE = 1 WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID; UPDATE CUS_SETTING_ADDRESS SET IS_ACTIVE = 2 WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID; SELECT PARENT_ID INTO vCHECK FROM ERP_CUS.CUS_SETTING_ADDRESS WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID = pGROUPADDRESS_ID AND IS_ACTIVE = 2; IF vCHECK > 0 THEN UPDATE ERP_CUS.CUS_SETTING_ADDRESS SET IS_ACTIVE = 2 WHERE CUS_ID = pCUS_ID AND GROUPADDRESS_ID <> pGROUPADDRESS_ID AND IS_ACTIVE = 1 AND SETTING_PATH LIKE '!_' || vCHECK || '!_%' ESCAPE '!'; COMMIT; END IF; --UPDATE CUS_GROUPADDRESS SET ACTIVE = 'N' WHERE GROUPADDRESS_ID <> pGROUPADDRESS_ID AND CUS_ID = pCUS_ID AND ACTIVE = 'Y'; --UPDATE CUS_GROUPADDRESS SET ACTIVE = 'Y' WHERE GROUPADDRESS_ID = pGROUPADDRESS_ID AND CUS_ID = pCUS_ID AND ACTIVE = 'N'; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN RAISE; END;
Leave a Comment