CREATE OR REPLACE PROCEDURE SYSTEM.ServiceClientInsert
(
pServiceClientCode INVARCHAR,
pServiceClientName INNVARCHAR2,
pComputerName
INVARCHAR2,
pIsAccountClient INNUMBER,
pIsSubsidyClient INNUMBER,
pRet
OUT NUMBER
)
AS
vCnt NUMBER;
BEGIN
IF Right(pComputerName, 1) = '.' THEN
pComputerName := Left(pComputerName, LENGTH(pComputerName)-1);
pComputerName := UPPER(pComputerName);
END IF;
SELECT COUNT(*) INTO vCnt FROM ServiceClient WHERE
ServiceClientCode = pServiceClientCode OR
ServiceClientName = pServiceClientName OR
ComputerName = vComputerName;
IF vCnt > 0 THEN
pRet := RET_DUPLICATE();
RETURN;
END IF;
IF pIsAccountClient = 1 THEN
UPDATE ServiceClient SET IsAccountClient = 0;
END IF;
IF pIsSubsidyClient = 1 THEN
UPDATE ServiceClient SET IsSubsidyClient = 0;
END IF;
INSERT INTO ServiceClient(ServiceClientID, ServiceClientCode, ServiceClientName, ComputerName, IsAccountClient, IsSubsidyClient)
VALUES(SqServiceClient.NEXTVAL, pServiceClientCode, pServiceClientName, pComputerName, pIsAccountClient, pIsSubsidyClient);
COMMIT;
pRet := RET_OK();
EXCEPTION WHEN OTHERS THEN
pRet := RET_FAIL();
ROLLBACK;
END serviceclientinsert;
|
