CREATE GENERATOR GEN_EDITXEM;
CREATE GENERATOR GEN_WEBBATCHNOEM;
CREATE GENERATOR GEN_UPDATESEQNOEM;
 
CREATE TABLE EDITXEM 
(
  KEYNUM	VARCHAR(24),
  BATCHNO	INTEGER,
  MEDAID	CHAR(5),
  SWITCHMFNR	VARCHAR(6),
  ITEMNO	INTEGER,
  ACCOUNT	CHAR(20),
  SURNAME	VARCHAR(40),
  INITS	VARCHAR(30),
  PATIENT	VARCHAR(25),
  PROVIDER	CHAR(15),
  ACCNUM	CHAR(10),
  TXDATE	TIMESTAMP,
  QTY	NUMERIC(15, 4),
  CLAIMED	NUMERIC(15, 4),
  DISCAMT	NUMERIC(15, 4),
  NARR	CHAR(30),
  TARIFF	CHAR(9),
  DOCNO	VARCHAR(10),
  IDNO	VARCHAR(13),
  PRTRANNO	VARCHAR(20),
  AUTHNUM	CHAR(21),
  RESUB	CHAR(1),
  ICD10	VARCHAR(50),
  TREATINGDR	VARCHAR(15),
  PROVBATCH	VARCHAR(10),
  ADDITION	VARCHAR(12),
  SCRIPT	VARCHAR(50),
  NAPPI	VARCHAR(15),
  USERNAME	CHAR(20),
  CAPTUREDATE	TIMESTAMP,
  PROCESS	CHAR(1),
  PROCESSDATE	TIMESTAMP,
  PROCESSBY	CHAR(20),
  VALIDACCOUNT	VARCHAR(20),
  VALIDPROVIDER	VARCHAR(15),
  PROVIDERNAME	VARCHAR(100),
  WEBCLAIM	VARCHAR(1) DEFAULT 'F',
  PRACTICEID	VARCHAR(12),
  WEBTX	VARCHAR(24),
  LABORDERNAME	VARCHAR(100),
  LABORDERNO	VARCHAR(20),
  PMA		VARCHAR(5)
);

SET TERM ^ ;


CREATE TRIGGER TG_INSERT_EDITXEM FOR EDITXEM 
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF ((NEW.ICD10 IS NULL) AND ((NEW.TARIFF = '11001') OR (NEW.TARIFF = '93100') OR (NEW.TARIFF = '11081'))) THEN
  BEGIN
    NEW.ICD10 = 'Z01.0';
  END
  NEW.KEYNUM = GEN_ID(GEN_EDITXEM,1);
END
 ^

CREATE TRIGGER TG_UPDATE_EDITXEM FOR EDITXEM 
ACTIVE BEFORE UPDATE POSITION 0
as
begin
  IF ((OLD.PROCESS = 'L') AND (NEW.PROCESS = 'L')) THEN
  BEGIN
   EXCEPTION EXC_CLAIM_LOADED;
  END
  IF ((NEW.PROCESS = 'N') AND ((NEW.VALIDACCOUNT IS NULL) OR (NEW.VALIDPROVIDER IS NULL))) THEN
  BEGIN
    EXCEPTION EXC_INVALID_ACCPROV;
  END
end
 ^

CREATE PROCEDURE SP_EDITXPORTALEM 
(
  PAR_STATUS VARCHAR(1),
  PAR_LINESTATUS VARCHAR(1),
  PAR_UPDATESEQNO INTEGER,
  PAR_KEYNUM INTEGER,
  PAR_BATCHNO INTEGER,
  PAR_ITEMNO INTEGER,
  PAR_ACCOUNT VARCHAR(20),
  PAR_PROVIDER VARCHAR(15),
  PAR_TXDATE TIMESTAMP,
  PAR_QTY NUMERIC(15, 4),
  PAR_CLAIMED NUMERIC(15, 4),
  PAR_DISCAMT NUMERIC(15, 4),
  PAR_NARR VARCHAR(30),
  PAR_TARIFF VARCHAR(9),
  PAR_DOCNO VARCHAR(10),
  PAR_PRTRANNO	VARCHAR(20),
  PAR_AUTHNUM VARCHAR(21),
  PAR_ICD10 VARCHAR(50),
  PAR_SCRIPT VARCHAR(50),
  PAR_USERNAME CHAR(20),
  PAR_PRACTICEACCNO VARCHAR(10),
  PAR_ADDITION VARCHAR(12),
  PAR_ADMINISTRATOR_KEY INTEGER,
  PAR_SECONDARYEXAM VARCHAR(1),
  PAR_LABORDERNAME VARCHAR(100),
  PAR_LABORDERNO VARCHAR(20),
  PAR_PMA	VARCHAR(5)
)
RETURNS
(
  RET_UPDATESEQNO INTEGER,
  RET_BATCHNO INTEGER
)
AS
DECLARE VARIABLE KEYNUM 	 INTEGER;
DECLARE VARIABLE NEWWEBBATCHNO INTEGER;
DECLARE VARIABLE RECORDCOUNT   INTEGER;
DECLARE VARIABLE QEDICODE     VARCHAR(5);
DECLARE VARIABLE SURNAME   	VARCHAR(40);
DECLARE VARIABLE INITIALS   	VARCHAR(30);
DECLARE VARIABLE FIRSTNAME   	VARCHAR(25);
DECLARE VARIABLE IDNUMBER   	VARCHAR(13);
DECLARE VARIABLE PROVIDERNAME	VARCHAR(100);
DECLARE VARIABLE GROUPCODE    VARCHAR(5);
DECLARE VARIABLE MEMBERNO     VARCHAR(15);
BEGIN
IF (STRLEN(:PAR_PROVIDER) = 6) THEN
PAR_PROVIDER = '0'||:PAR_PROVIDER;
IF (PAR_STATUS = 'I') THEN
BEGIN
IF ((PAR_LINESTATUS = 'F') OR
((PAR_LINESTATUS = 'L') AND (PAR_UPDATESEQNO = 0))) THEN
BEGIN
RET_UPDATESEQNO = GEN_ID(GEN_UPDATESEQNOEM, 1);
PAR_UPDATESEQNO = RET_UPDATESEQNO;
END

SELECT M.QEDICODE, P.SURNAME, P.INITIALS, P.FIRSTNAMES, P.IDNUMBER, M.GROUPCODE, P.NUMBER
FROM PATIENT P
LEFT OUTER JOIN MEDAID M ON M.CODE = P.MEDAIDCODE
WHERE P.ACCOUNT = :PAR_ACCOUNT AND P.ADMINISTRATOR_KEY = :PAR_ADMINISTRATOR_KEY
INTO :QEDICODE, :SURNAME, :INITIALS, :FIRSTNAME, :IDNUMBER, :GROUPCODE, :MEMBERNO;
SELECT PRACNAME
FROM PROVIDER
WHERE PROVIDER = :PAR_PROVIDER
INTO :PROVIDERNAME;

IF (PAR_LABORDERNAME = '') THEN
  PAR_LABORDERNAME = NULL;
IF (PAR_LABORDERNO = '') THEN
  PAR_LABORDERNO = NULL;
IF (PAR_ICD10 = '') THEN
  PAR_ICD10 = NULL;
IF (PAR_AUTHNUM = '') THEN
  PAR_AUTHNUM = NULL;

INSERT INTO EDITXEM
(
KEYNUM, BATCHNO, MEDAID, SWITCHMFNR, ITEMNO, ACCOUNT, SURNAME, INITS, PATIENT, PROVIDER,
ACCNUM, TXDATE, QTY, CLAIMED, DISCAMT, NARR, TARIFF, DOCNO, IDNO, PRTRANNO, AUTHNUM,
RESUB,  ICD10, TREATINGDR, PROVBATCH, ADDITION, SCRIPT, NAPPI,
USERNAME, CAPTUREDATE, PROCESS, PROCESSDATE, PROCESSBY, VALIDACCOUNT, VALIDPROVIDER, PROVIDERNAME, PRACTICEID, WEBCLAIM,
LABORDERNAME, LABORDERNO, PMA
)
VALUES
(
-1, :PAR_UPDATESEQNO, :GROUPCODE, :QEDICODE, :PAR_ITEMNO, :MEMBERNO, :SURNAME, :INITIALS, :FIRSTNAME, :PAR_PROVIDER,
:PAR_PRACTICEACCNO, :PAR_TXDATE, :PAR_QTY, :PAR_CLAIMED, 0, :PAR_NARR, :PAR_TARIFF, :PAR_DOCNO, :IDNUMBER, :PAR_PRTRANNO, :PAR_AUTHNUM,
'F', :PAR_ICD10, :PAR_PROVIDER, NULL, :PAR_ADDITION, :PAR_SCRIPT, NULL,
:PAR_USERNAME, 'TODAY', 'N', NULL, NULL, :PAR_ACCOUNT, :PAR_PROVIDER, :PROVIDERNAME, NULL, 'T',
:PAR_LABORDERNAME, :PAR_LABORDERNO, :PAR_PMA
);
IF (PAR_LINESTATUS = 'L') THEN
BEGIN
NEWWEBBATCHNO = GEN_ID(GEN_WEBBATCHNOEM, 1);
UPDATE EDITXEM SET
BATCHNO = :NEWWEBBATCHNO
WHERE BATCHNO = :PAR_UPDATESEQNO AND WEBCLAIM = 'T';

IF ((PAR_SECONDARYEXAM = 'T') OR (PAR_SECONDARYEXAM = 'F')) THEN
BEGIN
SELECT COUNT(*)
FROM SECONDARYEXAM
WHERE ACCOUNT = :PAR_ACCOUNT
INTO :RECORDCOUNT;
/*IF (RECORDCOUNT = 0) THEN
BEGIN
INSERT INTO SECONDARYEXAM(BATCHNO, EXAMREQD, ACCOUNT, CREATED, PROVIDER)
VALUES (:NEWWEBBATCHNO, :PAR_SECONDARYEXAM, :PAR_ACCOUNT, 'TODAY', :PAR_PROVIDER);
END
*/
END
RET_BATCHNO = :NEWWEBBATCHNO;
END
IF (PAR_LINESTATUS = '') THEN
BEGIN
RET_UPDATESEQNO = PAR_UPDATESEQNO;
END
SUSPEND;
END
END
 ^