MySql_version8.0_Midtables_to_NPHIES_views.sql

/*
Created		14/08/2023
Modified	
Project		Waseel
Model		
Company		
Author		Priya
Version		0.1
Database	Mysql 8.0
*/



CREATE OR REPLACE VIEW NPHIES_BENEFICIARY (BENEFICIARYID, PATIENTFILENO, FIRSTNAME, MIDDLENAME, LASTNAME,
		FULLNAME, DOB, GENDER, NATIONALITY, DOCUMENTID, DOCUMENTTYPE, CONTACTNUMBER, EHEALTHID,
		RESIDENCYTYPE, MARITALSTATUS, BLOODGROUP, PREFERREDLANGUAGE, EMAIL, ADDRESSLINE,
		ADDRESSSTREETNAME, ADDRESSCITY, ADDRESSDISTRICT, ADDRESSSTATE, ADDRESSPOSTALCODE, ADDRESSCOUNTRY, PROVCLAIMNO) AS
	SELECT ROW_NUMBER() OVER (ORDER BY PROVCLAIMNO) AS BENEFICIARYID, PATFILENO AS PATIENTFILENO, FIRSTNAME, MIDDLENAME, LASTNAME, FULLNAME,
		MEMBERDOB AS DOB, GENDER, NATIONALITY AS NATIONALITY, NATIONALID AS DOCUMENTID, NULL, NULL, NULL, NULL, NULL,
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PROVCLAIMNO
	FROM WSL_GENINFO;

CREATE OR REPLACE VIEW NPHIES_COVERAGE (COVERAGEID, MEMBERID, EXPIRYDATE, PAYERNPHIESID,
		TPANPHIESID, RELATIONWITHSUBSCRIBER, POLICYNUMBER, POLICYHOLDER, COVERAGETYPE, BENEFICIARYID, PROVCLAIMNO) AS
	SELECT NPHIES_BENEFICIARY.BENEFICIARYID AS COVERAGEID, MEMBERID AS MEMBERID, NULL, PAYERID AS PAYERNPHIESID, TPAID AS TPANPHIESID, NULL, POLICYNO AS POLICYNUMBER,
		NULL AS POLICYHOLDER, 'EHCPOL' AS COVERAGETYPE, NPHIES_BENEFICIARY.BENEFICIARYID AS BENEFICIARYID, WSL_GENINFO.PROVCLAIMNO
	FROM WSL_GENINFO LEFT JOIN NPHIES_BENEFICIARY ON WSL_GENINFO.PROVCLAIMNO=NPHIES_BENEFICIARY.PROVCLAIMNO;

CREATE OR REPLACE VIEW NPHIES_CLAIMINFO (PROVCLAIMNO, EPISODEID, ISNEWBORN, CLAIMTYPE,
		CLAIMSUBTYPE, PROVIDERNPHIESID, CLAIMCREATEDDATE, ACCOUNTINGPERIOD, BILLABLEPERIODSTART, BILLABLEPERIODEND,
		ELIGIBILITYRESPONSEID, ELIGIBILITYIDENTIFIERURL, ELIGIBILITYOFFLINEID, ELIGIBILITYOFFLINEDATE,
		PREAUTHOFFLINEDATE, PREAUTHRESPONSEID, PREAUTHIDENTIFIERURL, PAYEETYPE, PAYEEID, COVERAGEID, BENEFICIARYID, SUBSCRIBERID, TOTAL,ISREFERRAL,REFERRINGPROVIDERNAME , PRESCRIPTION) AS
	SELECT WSL_GENINFO.PROVCLAIMNO, WSL_GENINFO.PROVCLAIMNO AS EPISODEID, NULL, DEPTCODE AS CLAIMTYPE,
		CLAIMTYPE AS CLAIMSUBTYPE, PROVIDERID AS PROVIDERNPHIESID, CLAIMDATE AS CLAIMCREATEDDATE, CLAIMDATE AS ACCOUNTINGPERIOD, NULL,
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'provider' AS PAYEETYPE, PROVIDERID AS PAYEEID , NPHIES_COVERAGE.COVERAGEID AS COVERAGEID, 
		NPHIES_BENEFICIARY.BENEFICIARYID AS BENEFICIARYID,NULL, TOTCLAIMNETAMT AS TOTAL ,NULL AS ISREFERRAL,NULL AS REFERRINGPROVIDERNAME,NULL AS PRESCRIPTION
	FROM WSL_GENINFO LEFT JOIN NPHIES_BENEFICIARY ON WSL_GENINFO.PROVCLAIMNO=NPHIES_BENEFICIARY.PROVCLAIMNO 
	LEFT JOIN NPHIES_COVERAGE ON NPHIES_BENEFICIARY.BENEFICIARYID=NPHIES_COVERAGE.BENEFICIARYID;

CREATE OR REPLACE VIEW NPHIES_CLAIMDIAGNOSIS (PROVCLAIMNO, SEQUENCENO, DIAGNOSISCODE, DIAGNOSISDESC, 
		DIAGNOSISTYPE, ONADMISSION) AS
	SELECT PROVCLAIMNO, ROW_NUMBER() OVER (ORDER BY PROVCLAIMNO), DIAGNOSISCODE, DIAGNOSISDESC, NULL, NULL
	FROM WSL_CLAIM_DIAGNOSIS;

CREATE OR REPLACE VIEW NPHIES_CLAIMPREAUTHDETAILS (PROVCLAIMNO, PREAUTHREFNO) AS
	SELECT PROVCLAIMNO, APPREFNO AS PREAUTHREFNO
	FROM WSL_GENINFO WHERE APPREFNO IS NOT NULL AND APPREFNO != '';

CREATE OR REPLACE VIEW NPHIES_CLAIMCARETEAM(PROVCLAIMNO, SEQUENCENO, PHYSICIANID, PHYSICIANNAME, 
		PRACTITIONERROLE, CARETEAMROLE, CARETEAMQUALIFICATION) AS
	SELECT PROVCLAIMNO, ROW_NUMBER() OVER (ORDER BY PROVCLAIMNO), PHYID AS PHYSICIANID, PHYNAME AS PHYSICIANNAME, 
		'doctor', 'primary', DEPTCODE AS CARETEAMQUALIFICATION
	FROM WSL_GENINFO;

-- WSD.UNITSERVICETYPE AS SERVICETYPE, calculate factor, payershare, why not use TOTSERVICENETAMT
CREATE OR REPLACE VIEW NPHIES_CLAIMITEM(PROVCLAIMNO, INVOICENO, SEQUENCENO, SERVICETYPE, SERVICECODE, SERVICEDESC,
		NONSTANDARDCODE, NONSTANDARDDESC, UDI, ISPACKAGE, QUANTITY, QUANTITYCODE, UNITPRICE, 
		DISCOUNT, FACTOR, PATIENTSHARE, PAYERSHARE, TAX, NET, STARTDATE, ENDDATE,
		BODYSITECODE, SUBSITECODE, DRUGSELECTIONREASON, PRESCRIBEDDRUGCODE , PHARMACISTSELECTIONREASON, PHARMACISTSUBSTITUTE, REASONPHARMACISTSUBSTITUTE) AS
	SELECT WI.PROVCLAIMNO, WI.INVOICENO, ROW_NUMBER() OVER (ORDER BY PROVCLAIMNO), NULL AS SERVICETYPE, NULL AS SERVICECODE, NULL AS SERVICEDESC,
		WSD.SERVICECODE AS NONSTANDARDCODE, WSD.SERVICEDESC AS NONSTANDARDDESC, NULL AS UDI, 'false' AS ISPACKAGE,
		WSD.QTY AS QUANTITY, 'package' AS QUANTITYCODE, WSD.UNITSERVICEPRICE AS UNITPRICE, WSD.TOTSERVICEDISC AS DISCOUNT, NULL AS FACTOR, 
		WSD.TOTSERVICEPATSHARE AS PATIENTSHARE, (IFNULL(WSD.TOTSERVICEGRSAMT,0)-IFNULL(WSD.TOTSERVICEDISC,0)-IFNULL(WSD.TOTSERVICEPATSHARE,0)+IFNULL(WSD.TOTSERVICENETVATAMOUNT,0)) AS PAYERSHARE, 
		WSD.TOTSERVICENETVATAMOUNT AS TAX, NULL AS NET, WSD.SERVICEDATE AS STARTDATE,
		WSD.SERVICEDATE AS ENDDATE, WSD.TOOTHNO AS BODYSITECODE, NULL, NULL, NULL,
		NULL, 
        NULL, 
        NULL
	FROM WSL_INVOICES WI 
    LEFT JOIN WSL_SERVICE_DETAILS WSD ON WI.INVOICENO = WSD.INVOICENO
    LEFT JOIN NPHIES_CLAIMPREAUTHDETAILS ON NPHIES_CLAIMPREAUTHDETAILS.PROVCLAIMNO = WI.PROVCLAIMNO;

-- Supporting info 
DELIMITER $$

DROP PROCEDURE IF EXISTS `generate_supporting_info` $$
CREATE PROCEDURE `generate_supporting_info` ()
DETERMINISTIC
BEGIN

DECLARE var_table_count INT;
SELECT COUNT(*) INTO @var_table_count FROM information_schema.tables WHERE table_schema = 'wslmidtables' and TABLE_NAME = 'wsl_claim_attachment';
IF (@var_table_count > 0) THEN
SET @supporting_query = 'CREATE OR REPLACE VIEW NPHIES_CLAIMSUPPORTINGINFO (PROVCLAIMNO, SEQUENCENO, CATEGORY, REASON,
			SUPPORTINGVALUE, SUPPORTINGATTACHMENT, ATTACHMENTFILENAME, ATTACHMENTTYPE, CODE,
			TIMINGPERIODFROM, TIMINGPERIODTO) AS 
		SELECT PROVCLAIMNO AS PROVCLAIMNO , ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS SEQUENCENO, SUPPORTTYPE, NULL,SUPPORTINGVALUE, FILECONTENT, FILENAME,
		NULL AS ATTACHMENTTYPE,CODE,TIMINGPERIODFROM, TIMINGPERIODTO 
		FROM (
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "temperature" AS SUPPORTTYPE, NULL, 
			CONVERT(TEMPERATURE, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, "Cel" AS CODE, 
			NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO 
			FROM WSL_GENINFO WHERE TEMPERATURE IS NOT NULL
		UNION ALL 
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "chief-complaint" AS SUPPORTTYPE, NULL, MAINSYMPTOM AS SUPPORTINGVALUE,
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE MAINSYMPTOM IS NOT NULL AND MAINSYMPTOM != ''
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "vital-sign-weight" AS SUPPORTTYPE, NULL, 
			CONVERT(WEIGH, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, "kg" AS CODE, NULL AS TIMINGPERIODFROM, 
			NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE WEIGH IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "hospitalized" AS SUPPORTTYPE, NULL, NULL AS SUPPORTINGVALUE, NULL AS FILECONTENT, 
			NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, 
			ADMISSIONDATE AS TIMINGPERIODFROM, DISCHARGEDATE AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE ADMISSIONDATE IS NOT NULL AND DISCHARGEDATE IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "pulse" AS SUPPORTTYPE, NULL, CONVERT(PULSE, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT, 
			NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE PULSE IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "respiratory-rate" AS SUPPORTTYPE, NULL, CONVERT(RESPIRATORYRATE, CHAR) AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE RESPIRATORYRATE IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "info" AS SUPPORTTYPE, NULL, CONVERT(RADIOREPORT, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT,
			NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE RADIOREPORT IS NOT NULL AND RADIOREPORT != ''
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "vital-sign-systolic" AS SUPPORTTYPE, NULL, CONVERT(BLOODPRESSURE, CHAR) AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL AND BLOODPRESSURE != ''
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "vital-sign-diastolic" AS SUPPORTTYPE, NULL, CONVERT(BLOODPRESSURE, CHAR) AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL AND BLOODPRESSURE != ''
		UNION ALL
			select PROVCLAIMNO_temp AS PROVCLAIMNO, NULL AS SEQUENCENO, "lab-test" AS SUPPORTTYPE, NULL,
			case
			when LABCOMPCODE_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABCOMPDESC_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULT_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULTUNIT_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULTCOMMENT_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULTDESC_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			END AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, CODE_temp AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			from (
				SELECT labResult.PROVCLAIMNO AS PROVCLAIMNO_temp,labComponent.LABCOMPCODE AS LABCOMPCODE_temp, labComponent.LABCOMPDESC AS LABCOMPDESC_temp, 
				labComponent.LABRESULT AS LABRESULT_temp, labComponent.LABRESULTUNIT AS LABRESULTUNIT_temp, labComponent.LABRESULTCOMMENT AS LABRESULTCOMMENT_temp, labResult.LABDESC AS LABRESULTDESC_temp,
				labResult.LABTESTCODE AS CODE_temp
				FROM WSL_LAB_RESULT labResult LEFT JOIN WSL_LAB_COMPONENT labComponent ON labResult.LABTESTCODE=labComponent.LABTESTCODE and labResult.SERIAL=labComponent.SERIAL and labResult.PROVCLAIMNO=labComponent.PROVCLAIMNO) as s1
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "last-menstrual-period" AS SUPPORTTYPE, NULL, NULL AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, LASTMENSTRUATIONPERIOD AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
			FROM WSL_GENINFO WHERE LASTMENSTRUATIONPERIOD IS NOT NULL AND LASTMENSTRUATIONPERIOD != 0
		UNION ALL
		SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "attachment" AS SUPPORTTYPE, NULL, NULL AS SUPPORTINGVALUE, FILECONTENT AS FILECONTENT, 
		FILENAME AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO
		FROM WSL_CLAIM_ATTACHMENT WHERE FILENAME IS NOT NULL ) as supportingValue';

PREPARE my_query FROM @supporting_query;
EXECUTE my_query;

ELSE
SET @supporting_query = 'CREATE OR REPLACE VIEW NPHIES_CLAIMSUPPORTINGINFO (PROVCLAIMNO, SEQUENCENO, CATEGORY, REASON,
			SUPPORTINGVALUE, SUPPORTINGATTACHMENT, ATTACHMENTFILENAME, ATTACHMENTTYPE, CODE,
			TIMINGPERIODFROM, TIMINGPERIODTO,UNIT) AS
		SELECT PROVCLAIMNO AS PROVCLAIMNO , ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS SEQUENCENO, SUPPORTTYPE, NULL,SUPPORTINGVALUE, FILECONTENT, FILENAME,
		NULL AS ATTACHMENTTYPE, CODE, TIMINGPERIODFROM, TIMINGPERIODTO ,UNIT
		FROM (
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "temperature" AS SUPPORTTYPE, NULL, 
			CONVERT(TEMPERATURE, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, "Cel" AS CODE, 
			NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO , NULL AS UNIT
			FROM WSL_GENINFO WHERE TEMPERATURE IS NOT NULL
		UNION ALL 
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "chief-complaint" AS SUPPORTTYPE, NULL, MAINSYMPTOM AS SUPPORTINGVALUE,
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
			FROM WSL_GENINFO WHERE MAINSYMPTOM IS NOT NULL AND MAINSYMPTOM != ''
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "vital-sign-weight" AS SUPPORTTYPE, NULL, 
			CONVERT(WEIGH, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, "kg" AS CODE, NULL AS TIMINGPERIODFROM, 
			NULL AS TIMINGPERIODTO, NULL AS UNIT
			FROM WSL_GENINFO WHERE WEIGH IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "hospitalized" AS SUPPORTTYPE, NULL, NULL AS SUPPORTINGVALUE, NULL AS FILECONTENT, 
			NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, 
			ADMISSIONDATE AS TIMINGPERIODFROM, DISCHARGEDATE AS TIMINGPERIODTO,  NULL AS UNIT
			FROM WSL_GENINFO WHERE ADMISSIONDATE IS NOT NULL AND DISCHARGEDATE IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "pulse" AS SUPPORTTYPE, NULL, CONVERT(PULSE, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT, 
			NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO,  NULL AS UNIT
			FROM WSL_GENINFO WHERE PULSE IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "respiratory-rate" AS SUPPORTTYPE, NULL, CONVERT(RESPIRATORYRATE, CHAR) AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO,  NULL AS UNIT
			FROM WSL_GENINFO WHERE RESPIRATORYRATE IS NOT NULL
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "info" AS SUPPORTTYPE, NULL, CONVERT(RADIOREPORT, CHAR) AS SUPPORTINGVALUE, NULL AS FILECONTENT,
			NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO,  NULL AS UNIT
			FROM WSL_GENINFO WHERE RADIOREPORT IS NOT NULL AND RADIOREPORT != ''
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "vital-sign-systolic" AS SUPPORTTYPE, NULL, CONVERT(BLOODPRESSURE, CHAR) AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO,  NULL AS UNIT
			FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL AND BLOODPRESSURE != ''
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "vital-sign-diastolic" AS SUPPORTTYPE, NULL, CONVERT(BLOODPRESSURE, CHAR) AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO,  NULL AS UNIT
			FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL AND BLOODPRESSURE != ''
		UNION ALL
			select PROVCLAIMNO_temp AS PROVCLAIMNO, NULL AS SEQUENCENO, "lab-test" AS SUPPORTTYPE, NULL,
			case
			when LABCOMPCODE_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABCOMPDESC_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULT_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULTUNIT_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULTCOMMENT_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			when LABRESULTDESC_temp is not null THEN (CONCAT(''LABCODE: '', LABCOMPCODE_temp, '' ,LABDESC: '',  LABCOMPDESC_temp , '' ,LABRESULT: '', LABRESULT_temp, '' ,LABRESULTUNIT: '', LABRESULTUNIT_temp, '' ,LABRESULTCOMMENT: '', LABRESULTCOMMENT_temp, '' ,LABRESULTCDESC: '', LABRESULTDESC_temp))
			END AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, CODE_temp AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO,  NULL AS UNIT
			from (
				SELECT labResult.PROVCLAIMNO AS PROVCLAIMNO_temp,labComponent.LABCOMPCODE AS LABCOMPCODE_temp, labComponent.LABCOMPDESC AS LABCOMPDESC_temp, 
				labComponent.LABRESULT AS LABRESULT_temp, labComponent.LABRESULTUNIT AS LABRESULTUNIT_temp, labComponent.LABRESULTCOMMENT AS LABRESULTCOMMENT_temp, labResult.LABDESC AS LABRESULTDESC_temp,
				labResult.LABTESTCODE AS CODE_temp
				FROM WSL_LAB_RESULT labResult LEFT JOIN WSL_LAB_COMPONENT labComponent ON labResult.LABTESTCODE=labComponent.LABTESTCODE and labResult.SERIAL=labComponent.SERIAL and labResult.PROVCLAIMNO=labComponent.PROVCLAIMNO) as s1
		UNION ALL
			SELECT PROVCLAIMNO AS PROVCLAIMNO, NULL AS SEQUENCENO, "last-menstrual-period" AS SUPPORTTYPE, NULL, NULL AS SUPPORTINGVALUE, 
			NULL AS FILECONTENT, NULL AS FILENAME, NULL AS ATTACHMENTTYPE, NULL AS CODE, LASTMENSTRUATIONPERIOD AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO,  NULL AS UNIT
			FROM WSL_GENINFO WHERE LASTMENSTRUATIONPERIOD IS NOT NULL AND LASTMENSTRUATIONPERIOD != 0
	) as supportingValue';
PREPARE my_query FROM @supporting_query;
EXECUTE my_query;
END IF;

END $$
DELIMITER ;

call generate_supporting_info();

-- Accident details
CREATE OR REPLACE VIEW NPHIES_CLAIMACCIDENTDETAIL(PROVCLAIMNO, ACCIDENTTYPE, ACCIDENTDATE,
		ADDRESSSTREETNAME, ADDRESSCITY, ADDRESSSTATE, ADDRESSCOUNTRY) AS
	SELECT NULL AS PROVCLAIMNO, NULL, NULL, NULL, NULL, NULL, NULL
	FROM WSL_GENINFO where PROVCLAIMNO='-9999999999999';

CREATE OR REPLACE VIEW NPHIES_CLAIMVISIONPRESCRIPTION(PROVCLAIMNO, VISIONPRESCRIPTIONID, DATEWRITTEN,
		CARETEAMSEQUENCE, PRODUCT, EYE, SPHERE, CYLINDER, AXIS, PRISMAMOUNT,
		PRISMBASE, MULTIFOCALPOWER, LENSPOWER, LENSBACKCURVE, LENSDIAMETER, LENSDURATION,
		LENSCOLOR, LENSBRAND, LENSNOTE, LENSDURATIONUNIT) AS
	SELECT NULL AS PROVCLAIMNO, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
	FROM WSL_GENINFO where PROVCLAIMNO='-9999999999999';

CREATE OR REPLACE VIEW NPHIES_ITEMDIAGNOSIS(PROVCLAIMNO, DIAGNOSISSEQUENCENO, ITEMSEQUENCENO) AS
	SELECT NULL AS PROVCLAIMNO , NULL AS DIAGNOSISSEQUENCENO, NULL AS ITEMSEQUENCENO
    FROM WSL_GENINFO where PROVCLAIMNO='-9999999999999';

CREATE OR REPLACE VIEW NPHIES_ITEMCARETEAM(PROVCLAIMNO, CARETEAMSEQUENCENO, ITEMSEQUENCENO) AS
	SELECT NULL AS PROVCLAIMNO , NULL AS CARETEAMSEQUENCENO, NULL AS ITEMSEQUENCENO
    FROM WSL_GENINFO where PROVCLAIMNO='-9999999999999';

CREATE OR REPLACE VIEW NPHIES_ITEMSUPPORTINGINFO(PROVCLAIMNO, SUPPORTINGINFOSEQUENCENO, ITEMSEQUENCENO) AS
	SELECT NULL AS PROVCLAIMNO , NULL AS SUPPORTINGINFOSEQUENCENO, NULL AS ITEMSEQUENCENO
    FROM WSL_GENINFO where PROVCLAIMNO='-9999999999999';

CREATE OR REPLACE VIEW NPHIES_CLAIMITEMDETAILS(ITEMSEQUENCENO, PROVCLAIMNO, SEQUENCENO,
		SERVICETYPE, SERVICECODE, SERVICEDESC, NONSTANDARDCODE, NONSTANDARDDESC, UDI, 
		QUANTITY, QUANTITYCODE, UNITPRICE, TAX, NET , PHARMACISTSELECTIONREASON , PHARMACISTSUBSTITUTE ,REASONPHARMACISTSUBSTITUTE,PRESCRIBEDDRUGCODE) AS
	SELECT NULL, NULL AS PROVCLAIMNO, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
		NULL, NULL, NULL , NULL , NULL, NULL, NULL 
	FROM WSL_GENINFO where PROVCLAIMNO='-9999999999999';

CREATE OR REPLACE VIEW NPHIES_CLAIMENCOUNTERS(PROVCLAIMNO, ENCOUNTERID, ENCOUNTERSTARTDATE,
		ENCOUNTERENDDATE, ENCOUNTERCLASS, ENCOUNTERSERVICETYPE, PRIORITY, HOSPITALIZATIONORIGIN,
		HOSPITALADMISSIONSOURCE, HOSPITALREADMISSION, HOSPITALDISCHARGEDISPOSITION, SERVICEPROVIDER, ENCOUNTERSTATUS) AS
	SELECT NULL AS PROVCLAIMNO, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
	FROM WSL_GENINFO where PROVCLAIMNO='-9999999999999';