ALTER PROCEDURE "SP_POSTWEBPATIENTX" 
(
  "PAR_STATUS" VARCHAR(1),
  "PAR_LINESTATUS" VARCHAR(1),
  "PAR_UPDATESEQNO" INTEGER,
  "PAR_USERNAME" VARCHAR(20),
  "PAR_TREATPRACTICENO" VARCHAR(15),
  "PAR_ACCOUNT" VARCHAR(20),
  "PAR_NUMBER" VARCHAR(15),
  "PAR_DEPNUM" INTEGER,
  "PAR_EMACCOUNT_KEY" VARCHAR(7),
  "PAR_MEDICALAID" VARCHAR(10),
  "PAR_ADMINISTRATOR_KEY" INTEGER,
  "PAR_INITIALS" VARCHAR(30),
  "PAR_FIRSTNAMES" VARCHAR(25),
  "PAR_SURNAME" VARCHAR(40),
  "PAR_LABORDERNO" VARCHAR(20),
  "PAR_ITEMNO" INTEGER,
  "PAR_TXDATE" DATE,
  "PAR_TARIFF" VARCHAR(9),
  "PAR_QTY" NUMERIC(15, 4),
  "PAR_NARRATIVE" VARCHAR(40),
  "PAR_AMOUNT" NUMERIC(15, 4),
  "PAR_RSPHERE" VARCHAR(6),
  "PAR_RCYL" VARCHAR(6),
  "PAR_RADD" VARCHAR(6),
  "PAR_LSPHERE" VARCHAR(6),
  "PAR_LCYL" VARCHAR(6),
  "PAR_LADD" VARCHAR(6),
  "PAR_ICD10" VARCHAR(50)
)
RETURNS
(
  "RET_UPDATESEQNO" INTEGER,
  "RET_CLAIMNO" INTEGER,
  "RET_REJECT" VARCHAR(1),
  "RET_PAID" NUMERIC(15, 4)
)
AS
  DECLARE VARIABLE KEYNUM 	 INTEGER;
  DECLARE VARIABLE UPDATESEQNO   INTEGER;
  DECLARE VARIABLE RECORDCOUNT   INTEGER;
  DECLARE VARIABLE NEWCLAIMNO 	 INTEGER;

  DECLARE VARIABLE NOOFHISTTRANS INTEGER;
  DECLARE VARIABLE NOOFTRANS     INTEGER;

  DECLARE VARIABLE VAT   	 NUMERIC(15, 4);
  DECLARE VARIABLE REJECT   	 VARCHAR(1);
  DECLARE VARIABLE TARIFFAMT     NUMERIC(15, 4);
  DECLARE VARIABLE PROFIT        NUMERIC(15, 4);

  DECLARE VARIABLE DISPUTE       VARCHAR(5);
  DECLARE VARIABLE CONTRACTED    VARCHAR(5);
  DECLARE VARIABLE PEP           VARCHAR(5);
  DECLARE VARIABLE RESIGNED      DATE;
  DECLARE VARIABLE SUSPENDED     DATE;
  DECLARE VARIABLE RESUMED       DATE;
  DECLARE VARIABLE CLAIMBENEFIT  NUMERIC(15,4);
  DECLARE VARIABLE CLAIMPAID     NUMERIC(15,4);
  DECLARE VARIABLE FAMILYLIMIT   NUMERIC(15,4);
  DECLARE VARIABLE PAIDAMT       NUMERIC(15,4);
  DECLARE VARIABLE CREDITAMT     NUMERIC(15,4);
  DECLARE VARIABLE CONSULTTYPE   VARCHAR(20);
  DECLARE VARIABLE PATIENTAGE    INTEGER;
  DECLARE VARIABLE NONCONTRACTRATE NUMERIC(15,4);
  DECLARE VARIABLE CONTRACTRATE    NUMERIC(15,4);
  DECLARE VARIABLE VALIDTARIFF     VARCHAR(5);

BEGIN


  IF (PAR_STATUS = 'I') THEN
  BEGIN
      RET_REJECT = 'F';
      IF ((PAR_LINESTATUS = 'F') OR
          ((PAR_LINESTATUS = 'L') AND (PAR_UPDATESEQNO = 0))) THEN
      BEGIN
        RET_UPDATESEQNO = GEN_ID(GEN_UPDATESEQNO, 1);
        PAR_UPDATESEQNO = RET_UPDATESEQNO;
      END

      KEYNUM = GEN_ID(GEN_PATXKEY, 1);

      /*CHECK PROVIDER NOT IN DISPUTE OR NOT CONTRACTED*/

        SELECT DISPUTE,CONTRACTED,PEP FROM PROVIDER WHERE PROVIDER = :PAR_TREATPRACTICENO INTO :DISPUTE,:CONTRACTED,:PEP;

        IF ((UPPER(:DISPUTE) = 'TRUE') OR (UPPER(:CONTRACTED) = 'FALSE'))  THEN
        BEGIN
            RET_REJECT   = 'R';
            RET_PAID     = 0.00;
            REJECT       = 'R';
            CLAIMBENEFIT = 0;
            CLAIMPAID    = 0;
        END

        IF ((:PAR_TARIFF = '01PEP')  OR (:PAR_TARIFF = '02PEP') OR (:PAR_TARIFF = '03PEP')) THEN
        BEGIN
           IF (UPPER(:PEP) = 'FALSE') THEN
           BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
           END
        END

        /*CHECK IF MEMBER RESIGNED OR SUSPENDED*/
        SELECT RESIGN,SUSPENDED,RESUMED FROM PATIENT
        WHERE  ADMINISTRATOR_KEY = :PAR_ADMINISTRATOR_KEY AND ACCOUNT = :PAR_ACCOUNT INTO :RESIGNED,:SUSPENDED,:RESUMED;

        IF  (:RESIGNED IS NOT NULL) THEN
        BEGIN
           IF (:PAR_TXDATE > :RESIGNED) THEN
           BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;

           END
        END


        if ((:SUSPENDED IS NOT NULL) AND (:RESUMED IS NOT NULL)) THEN
        BEGIN
          IF ((:PAR_TXDATE >= :SUSPENDED) AND (:PAR_TXDATE <= :RESUMED)) THEN
          BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
          END
        END




     /* CHECK IF CLAIMS EXIST FOR THIS ACCOUNT*/
     IF (PAR_LINESTATUS = 'F') THEN
     BEGIN
        SELECT COUNT(*) FROM PATHIST WHERE ADMINISTRATOR_KEY = :PAR_ADMINISTRATOR_KEY AND ACCOUNT = :PAR_ACCOUNT  and reject in ('A','T') INTO :NOOFHISTTRANS;
        SELECT COUNT(*) FROM PATIENTX WHERE ADMINISTRATOR_KEY = :PAR_ADMINISTRATOR_KEY AND ACCOUNT = :PAR_ACCOUNT and reject in ('A','T') INTO :NOOFTRANS;
        IF ((NOOFHISTTRANS+NOOFTRANS) > 0) THEN
        BEGIN
          /*INSERT INTO EDITX*/
          RET_REJECT = 'S';
          RET_PAID = 0;
        END
     END




     /* BONI01-5-8 CHECK FAMILY LIMITS*/
      IF (PAR_ADMINISTRATOR_KEY = 9) THEN
      BEGIN

      IF ((PAR_MEDICALAID = 'BONI01') OR (PAR_MEDICALAID = 'BONI05') OR (PAR_MEDICALAID = 'BONI07') OR (PAR_MEDICALAID = 'BONI08')) THEN
      BEGIN
          IF ((PAR_MEDICALAID = 'BONI01') OR (PAR_MEDICALAID = 'BONI08')) THEN
          BEGIN
             FAMILYLIMIT = 5270;
          END
          ELSE
          IF (PAR_MEDICALAID = 'BONI05') THEN
          BEGIN
             FAMILYLIMIT = 4270;
          END
          ELSE
          IF (PAR_MEDICALAID = 'BONI07') THEN
          BEGIN
             FAMILYLIMIT = 5060;
          END

          FOR SELECT SUM(PAID),SUM(CREDIT),REJECT FROM PATHIST
          WHERE ADMINISTRATOR_KEY =  9
          AND REJECT IN ('A','T')
          AND TARIFF not in ('99999','01PEP','03PEP')
          AND NUMBER =  :PAR_NUMBER
          GROUP BY REJECT INTO :PAIDAMT,:CREDITAMT,:REJECT DO
          BEGIN
             IF (:REJECT = 'A') THEN
             BEGIN
               FAMILYLIMIT = FAMILYLIMIT - :PAIDAMT;
             END
             ELSE
             IF (:REJECT = 'T') THEN
             BEGIN
               FAMILYLIMIT = FAMILYLIMIT + :CREDITAMT;
             END
          END

          IF ((FAMILYLIMIT - PAR_AMOUNT) < 0 ) THEN
          BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
          END
      END

      IF(PAR_MEDICALAID = 'BONI04') THEN
      BEGIN
         If ((PAR_TARIFF <> '93900') and (PAR_TARIFF <> '93200') and (PAR_TARIFF <> '93300') and (PAR_TARIFF <> '93400') and (PAR_TARIFF <> '93800') and (PAR_TARIFF <> '11001') and (PAR_TARIFF <> '11081') and (PAR_TARIFF <> '90011') and (PAR_TARIFF <> '03PEP')) Then
         BEGIN
           RET_REJECT   = 'R';
           RET_PAID     = 0.00;
           REJECT       = 'R';
           CLAIMBENEFIT = 0;
           CLAIMPAID    = 0;
         END
      END
      IF ((PAR_MEDICALAID = 'BONI01') or  (PAR_MEDICALAID = 'BONI05') or (PAR_MEDICALAID = 'BONI07') or (PAR_MEDICALAID = 'BONI08')) Then
      BEGIN
        If ((PAR_TARIFF = '93800') or (PAR_TARIFF = '03PEP')) Then
        BEGIN
           RET_REJECT   = 'R';
           RET_PAID     = 0.00;
           REJECT       = 'R';
           CLAIMBENEFIT = 0;
           CLAIMPAID    = 0;
        END
      END
      if (PAR_TARIFF = '02PEP') Then
      begin
         RET_REJECT   = 'R';
         RET_PAID     = 0.00;
         REJECT       = 'R';
         CLAIMBENEFIT = 0;
         CLAIMPAID    = 0;
      end

      END /* END OF IF ADMINISTRATOR_KEY = 9 */


      IF  ((PAR_ADMINISTRATOR_KEY = 4) or (PAR_ADMINISTRATOR_KEY = 10) or (PAR_ADMINISTRATOR_KEY = 8)) Then
      begin
       if ((PAR_TARIFF = '01PEP') or  (PAR_TARIFF = '03PEP'))  Then
       begin
          RET_REJECT   = 'R';
          RET_PAID     = 0.00;
          REJECT       = 'R';
          CLAIMBENEFIT = 0;
          CLAIMPAID    = 0;
       END
      END

      /*SCRIPT CHECK*/

      /* PATIENT < 38 CANNOT HAVE BIFOLCAL OR MULTIFOCAL*/
       SELECT extract(year from CURRENT_DATE)-extract(year from BIRTHDAY) FROM PATIENT
       WHERE ADMINISTRATOR_KEY = :PAR_ADMINISTRATOR_KEY
       AND ACCOUNT = :PAR_ACCOUNT INTO :PATIENTAGE;
        /*SINGLE BVISION SPEHERE AND CYL REQUIRED */
        SELECT CONSULT FROM TARIFF WHERE CODE = :PAR_TARIFF INTO :CONSULTTYPE;

        IF (UPPER(CONSULTTYPE) = 'SINGLE-VISION') THEN
        BEGIN
           IF ((PAR_RSPHERE IS NULL) AND (PAR_LSPHERE IS NULL) AND (PAR_RCYL IS NULL) AND (PAR_LCYL IS NULL)) THEN
           BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
           END
        END
      /*MULTIFOCAL ADD IS REQUIRED */
        IF ((UPPER(CONSULTTYPE) = 'MULTI-FOCAL') OR (UPPER(CONSULTTYPE) = 'BIFOCAL-FOCAL')) THEN
        BEGIN
          IF (PATIENTAGE < 38) THEN
          BEGIN
            RET_REJECT   = 'R';
            RET_PAID     = 0.00;
            REJECT       = 'R';
            CLAIMBENEFIT = 0;
            CLAIMPAID    = 0;
          END
          ELSE
          IF ((PAR_RADD IS NULL) AND (PAR_LADD IS NULL)) THEN
          BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
           /* ADD IS REqUIRED*/
          END
          ELSE
          IF ((PAR_RADD IS NOT NULL) AND (PAR_RADD <= 1.00)) THEN
          BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
          END
          ELSE
          IF ((PAR_LADD IS NOT NULL) AND (PAR_LADD <= 1.00)) THEN
          BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
          END
        END

      //CHECK RE CLENS CLAIM AND LENS CLAIM AN FRAMES

        SELECT CONSULT FROM TARIFF WHERE CODE = :PAR_TARIFF INTO :CONSULTTYPE;   
      IF ((:CONSULT = 'FRAMES') OR (:CONSULT = 'SINGLE-VISION') OR (:CONSULT = 'MULTIFOCAL') OR (:CONSULT = 'BI-FOCAL)) tHEN
      BEGIN
        :CHECKFRAME = 0;
        SELECT COUNT(*) FROM PATIENTX A
        LEFT  OUTER JOIN TARIFFTABLE B ON B.CODE = A.CODE
        WHERE B.CONSULT = 'C-LENS'
        AND A.ADMINISTRATOR_KEY = :PAR_ADMINISTRATOR_KEY
        AND A.ACCOUNT = :PAR_ACCOUNT  INTO :CHECKFRAME;
        IF (:CHECKFRAME > 0) tHEN
        BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
        END
      END
      ELSE
      IF ((:CONSULT = 'C-LENS')) tHEN
      BEGIN
        :CHECKFRAME = 0;
        SELECT COUNT(*) FROM PATIENTX A
        LEFT  OUTER JOIN TARIFFTABLE B ON B.CODE = A.CODE
        WHERE B.CONSULT IN ('FRAME','SINGLE-VISION','MULTI-FOCAL','BI-FOCAL')
        AND A.ADMINISTRATOR_KEY = :PAR_ADMINISTRATOR_KEY
        AND A.ACCOUNT = :PAR_ACCOUNT  INTO :CHECKFRAME;
        IF (:CHECKFRAME > 0) tHEN
        BEGIN
             RET_REJECT   = 'R';
             RET_PAID     = 0.00;
             REJECT       = 'R';
             CLAIMBENEFIT = 0;
             CLAIMPAID    = 0;
        END
      END
          
      // CHECK THE AMOUNT CLAIMED VS THE RATE APPLICABLE
      SELECT RATE,CONTRACTRATE 
      FROM ALTARGROUP 
      WHERE SCHEME = :PAR_MEDICALAID
      AND CODE = :PAR_TARIFF
      INTO :NONCONTRACTRATE,:CONTRACTRATE,:VALIDTARIFF;
    
      IF (UPPER(:VALIDTARIFF) = 'FALSE') THEN
      BEGIN
        RET_REJECT   = 'R';
        RET_PAID     = 0.00;
        REJECT       = 'R';
        CLAIMBENEFIT = 0;
        CLAIMPAID    = 0;
      END
      ELSE
      IF (UPPER(:VALIDTARIFF) = 'TRUE') THEN
      BEGIN
      IF  (UPPER(:CONTRACTED) = 'FALSE')  THEN
        BEGIN
            IF (:PAR_AMOUNT <= :NONCONTRACTRATE) THEN
            BEGIN       
              RET_REJECT   = 'F';
              RET_PAID     = :PAR_AMOUNT;
              REJECT       = 'A';
              CLAIMBENEFIT = :PAR_AMOUNT;
              CLAIMPAID    = :PAR_AMOUNT;
            END
            ELSE
            IF (:PAR_AMOUNT > :NONCONTRACTRATE) THEN
            BEGIN       
              RET_REJECT   = 'P';
              RET_PAID     = :NONCONTRACTRATE;
              REJECT       = 'A';
              CLAIMBENEFIT = :NONCONTRACTRATE;
              CLAIMPAID    = :NONCONTRACRATE;
            END
        END

      IF  (UPPER(:CONTRACTED) = 'TRUE')  THEN
        BEGIN
            IF (:PAR_AMOUNT <= :CONTRACTRATE) THEN
            BEGIN       
              RET_REJECT   = 'F';
              RET_PAID     = :PAR_AMOUNT;
              REJECT       = 'A';
              CLAIMBENEFIT = :PAR_AMOUNT;
              CLAIMPAID    = :PAR_AMOUNT;
            END
            ELSE
            IF (:PAR_AMOUNT > :CONTRACTRATE) THEN
            BEGIN       
              RET_REJECT   = 'P';
              RET_PAID     = :CONTRACTRATE;
              REJECT       = 'A';
              CLAIMBENEFIT = :CONTRACTRATE;
              CLAIMPAID    = :CONTRACRATE;
            END
        END

      INSERT INTO PATIENTX
      (ACCOUNT,NUMBER,TXDATE,RECEIVEDATE,TARIFF,ACCNUM,QTY,TXTYPE,PROVIDER,NARR,
       TXDRCR,CAPTUREDATE,CLAIMED,BENEFIT,PAID,CREDIT,VATRATE,VAT,DISCPERC,DISCAMT,EXCESS,PROFIT,MEDAID,OKNUM,
       DEPNUM,REASON,CLAIMNO,RSPH,RCYL,RADD,LSPH,LCYL,LADD,LABNO,ICD10,ADMINISTRATOR_KEY,EDIBATCH,EDITRACE,
       HOLD,HELD,REJECT,PAYPROV,USERNAME,UPDATED,KEYNUM,SEQNO,ITEMNO,LEVYAMT,DEPOSIT,ADMINFEE,REVERSED,INITIALS,FIRSTNAMES,SURNAME,EXPORTED,TARIFFAMT,OVERRIDE)
      VALUES
      (:PAR_ACCOUNT, :PAR_NUMBER, :PAR_TXDATE, 'TODAY', :PAR_TARIFF, :PAR_EMACCOUNT_KEY, :PAR_QTY, 'TT', :PAR_TREATPRACTICENO, :PAR_NARRATIVE,
       'D', 'TODAY', :PAR_AMOUNT, :CLAIMBENEFIT, :CLAIMPAID, 0, 1, :VAT, 0, 0, 0, :PROFIT, :PAR_MEDICALAID, NULL,
       :PAR_DEPNUM, NULL, :PAR_UPDATESEQNO, :PAR_RSPHERE, :PAR_RCYL, :PAR_RADD, :PAR_LSPHERE, :PAR_LCYL, :PAR_LADD, :PAR_LABORDERNO, :PAR_ICD10, :PAR_ADMINISTRATOR_KEY, NULL, NULL,
       'False', 0, :REJECT, 'True', :PAR_USERNAME, 'N', :KEYNUM, 0, :PAR_ITEMNO, 0, 0, 0, 'F', :PAR_INITIALS, :PAR_FIRSTNAMES, :PAR_SURNAME, 'F', :TARIFFAMT, 'F') ;

      IF (PAR_LINESTATUS = 'L') THEN
      BEGIN
        NEWCLAIMNO = GEN_ID(GEN_CLAIMNO, 1);

        UPDATE PATIENTX SET
         CLAIMNO = :NEWCLAIMNO
        WHERE CLAIMNO = :UPDATESEQNO;

        RET_CLAIMNO = :NEWCLAIMNO;
      END

      IF (PAR_LINESTATUS = '') THEN
      BEGIN
        RET_UPDATESEQNO = PAR_UPDATESEQNO;
      END

  END

  SUSPEND;
END