SET TERM ^;

CREATE PROCEDURE SP_CAPTUREPROVIDER
(
  PAR_STATUS		VARCHAR(1),
  PAR_PRACTICENO	VARCHAR(15),
  PAR_PRACTICENAME	VARCHAR(40),
  PAR_E2IDENTIFIER	VARCHAR(15),
  PAR_PHONE1		VARCHAR(15),
  PAR_PHONE2		VARCHAR(15),
  PAR_EMAIL		VARCHAR(60),
  PAR_POSTALADDR1	VARCHAR(30),
  PAR_POSTALADDR2	VARCHAR(30),
  PAR_POSTALADDR3	VARCHAR(30),
  PAR_POSTALPROVINCE	VARCHAR(20),
  PAR_POSTALPCODE	VARCHAR(4),
  PAR_PHYSADDR1		VARCHAR(30),
  PAR_PHYSADDR2		VARCHAR(30),
  PAR_PHYSADDR3		VARCHAR(30),
  PAR_PHYSPROVINCE	VARCHAR(20),
  PAR_PHYSPCODE		VARCHAR(4),
  PAR_BANKNAME		VARCHAR(30),
  PAR_BANKACCHOLDER	VARCHAR(40),
  PAR_BANKACCNO		VARCHAR(16),
  PAR_BANKSORTCODE	VARCHAR(8),
  PAR_OPTOMTITLE	VARCHAR(5),
  PAR_OPTOMFIRSTNAME	VARCHAR(20),
  PAR_OPTOMSURNAME	VARCHAR(40),
  PAR_OPTOMSAMDCNO	VARCHAR(15)
)
RETURNS
(
  RET_UNIQUEID		INTEGER
)
AS
  DECLARE VARIABLE UNIQUEID INTEGER;
BEGIN

  IF (PAR_STATUS = 'I') THEN
  BEGIN
    UNIQUEID =  GEN_ID(GEN_PROVIDER_UNIQUENO,1);

    INSERT INTO PROVIDER
    (PROVIDER, PRACNUM, ALTPRACNUM, PRACNAME, TELEPHONE1, FAX, EMAIL, ADD1, ADD2, ADD3, PROVINCE, PCODE, PHYSADD1, PHYSADD2, PHYSADD3,
     BANKNAME, ACCOUNTNAME, BANKACCOUNT, SORTCODE, TITLE, FIRSTNAME, SURNAME, SAMDCNO, UNIQUENO)
    VALUES
    (:PAR_PRACTICENO, :PAR_PRACTICENO, :PAR_E2IDENTIFIER, :PAR_PRACTICENAME, :PAR_PHONE1, :PAR_PHONE2, :PAR_EMAIL,
     :PAR_POSTALADDR1, :PAR_POSTALADDR2, :PAR_POSTALADDR3, :PAR_POSTALPROVINCE, :PAR_POSTALPCODE,
     :PAR_PHYSADDR1, :PAR_PHYSADDR2, :PAR_PHYSADDR3,
     :PAR_BANKNAME, :PAR_BANKACCHOLDER, :PAR_BANKACCNO, :PAR_BANKSORTCODE, :PAR_OPTOMTITLE, :PAR_OPTOMFIRSTNAME, :PAR_OPTOMSURNAME,
     :PAR_OPTOMSAMDCNO, :UNIQUEID);

    RET_UNIQUEID = :UNIQUEID;
    SUSPEND;
  END
  ELSE
  IF (PAR_STATUS = 'U') THEN
  BEGIN
    UPDATE PROVIDER SET
        ALTPRACNUM  = :PAR_E2IDENTIFIER,
	TELEPHONE1  = :PAR_PHONE1,
	FAX 	    = :PAR_PHONE2,
	EMAIL 	    = :PAR_EMAIL,
	ADD1        = :PAR_POSTALADDR1,
	ADD2 	    = :PAR_POSTALADDR2,
	ADD3 	    = :PAR_POSTALADDR3,
	PROVINCE    = :PAR_POSTALPROVINCE,
	PCODE       = :PAR_POSTALPCODE,
	PHYSADD1    = :PAR_PHYSADDR1,
	PHYSADD2    = :PAR_PHYSADDR1,
	PHYSADD3    = :PAR_PHYSADDR1,
	BANKNAME    = :PAR_BANKNAME,
	ACCOUNTNAME = :PAR_BANKACCHOLDER,
	BANKACCOUNT = :PAR_BANKACCNO,
	SORTCODE    = :PAR_BANKSORTCODE,
	TITLE       = :PAR_OPTOMTITLE,
	FIRSTNAME   = :PAR_OPTOMFIRSTNAME,
	SURNAME     = :PAR_OPTOMSURNAME,
	SAMDCNO     = :PAR_OPTOMSAMDCNO
     WHERE PRACNUM = :PAR_PRACTICENO;

     SELECT UNIQUENO
     FROM PROVIDER
     WHERE PRACNUM = :PAR_PRACTICENO
     INTO :UNIQUEID;

     RET_UNIQUEID = :UNIQUEID;

     SUSPEND;

  END
END
^

SET TERM ;^

GRANT EXECUTE ON PROCEDURE SP_CAPTUREPROVIDER TO PUBLIC;
GRANT INSERT, UPDATE ON PROVIDER TO PROCEDURE SP_CAPTUREPROVIDER;