日期:2014-05-17 浏览次数:20840 次
CREATE OR REPLACE PROCEDURE SYN_SYS_USER(PARTY_ID IN VARCHAR2) IS
S_COUNT NUMBER ,
ORG_COUNT NUMBER,
ORG_COUNT2 NUMBER,
DI_SHI_CODE_TEMP VARCHAR2(50),
ATT1_TEMP VARCHAR2(50)
BEGIN
S_COUNT:=SELECT COUNT(*) FROM SYS_USER SU1 WHERE SU1.PRTY_ID=PARTY_ID;
IF (S_COUNT=0) THEN
ORG_COUNT:=SELECT COUNT(*) FROM CARD_PROVIDER WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID)
IF (ORG_COUNT>0) THEN
ATT1_TEMP:='C';
DI_SHI_CODE_TEMP:=SELECT ID FROM CARD_PROVIDER WHERE PROVIDER_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID);
END IF;
ORG_COUNT2:= SELECT COUNT(*) FROM LOCATION WHERE ORGANIZATION_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID);
IF (ORG_COUNT2>0) THEN
ATT1_TEMP:='D';
DI_SHI_CODE_TEMP:=SELECT ID FROM LOCATION WHERE ORGANIZATION_ID=(SELECT PA.COMPANY_ID FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID);
END IF;
INSERT INTO SYS_USER(ID, PARTY_ID, CURRENT_PASSWORD, ENABLED, USER_LOGIN_ID)
SELECT (SELECT MAX(SU.ID)+1 FROM SYS_USER SU) AS SUID,ULA.PARTY_ID,ULA.CURRENT_PASSWORD,ULA.ENABLED,ULA.PARTY_ID FROM USER_LOGIN_ALL ULA WHERE ULA.PARTY_ID=PARTY_ID;
COMMIT;
INSERT INTO SYS_USER_INFO(ID, USER_ID,LAST_NAME, MOBILEPHONE, TELEPHONE, GENDER,EMAIL,COMMENTS,IS_SEND_MSM,DI_SHI_CODE, ATT1)
SELECT (SELECT MAX(SUI.ID)+1 FROM SYS_USER_INFO SUI) AS SUIID,(SELECT SU2.ID FROM SYS_USER SU2 WHERE SU2.PARTY_ID=PARTY_ID) AS SU2ID,
PA.LAST_NAME,PA.MOBILEPHONE,PA.TELEPHONE,PA.GENDER,PA.EMAIL,PA.COMMENTS, PA.IS_SEND_MSM,DI_SHI_CODE_TEMP,ATT1_TEMP FROM PERSON_ALL PA WHERE PA.PARTY_ID=PARTY_ID;
COMMIT;
ELSE
FOR TEMP_USER IN (SELECT ULA.PARTY_ID, DECODE(ULA.ENABLED,'T',1,0) AS ENABLED,ULA.CURRENT_PASSWORD FROM USER_LOGIN_ALL ULA WHERE ULA.PARTY_ID=PARTY_ID)&nb