select * from XML_Text
select * from XML_Text WHERE Answer is not null
SELECT * FROM #Request.prefix_db_lookup#.Agency where Natl_Provider_ID = '#mydoc.Assessment.NATL_PRVDR_ID.XmlText#' and status = 'active'
Select Agency_Login FROM #Request.prefix_db_lookup#.Agency WHERE Agency_Login = #variables.agencylogin#
INSERT IGNORE INTO #Request.prefix_db_lookup#.Agency
(Agency_Login,status,Agency_Name,Agency_State,Agency_Contact_Email,Natl_Provider_ID,SFW_ID,
Record_Creation_Date,Record_Created_By,Record_Mod_Date,Record_Mod_By,Super_ID,Coding_wizard)
VALUES
('#form.Agency_Login#','active','#mydoc.Assessment.SFW_NAME.XmlText#','#mydoc.Assessment.STATE_CD.XmlText#','#mydoc.Assessment.SFW_EMAIL_ADR.XmlText#',
'#mydoc.Assessment.NATL_PRVDR_ID.XmlText#','#mydoc.Assessment.SFW_ID.XmlText#',
#form.Record_Creation_Date#,'#form.Record_Created_By#',#form.Record_Mod_Date#,'#form.Record_Mod_By#',1,1)
SELECT Max(Agency_ID) AS ID, MAX(telephony_agencynumber) as maxnumber FROM #Request.prefix_db_lookup#.Agency
UPDATE #Request.prefix_db_lookup#.Agency set telephony_agencynumber = #Max.maxnumber# where Agency_id = #Max.id#
INSERT IGNORE INTO #Request.prefix_db_lookup#.AgencyStates( Agency_ID, State ) Values ( #Max.ID#, '#mydoc.Assessment.STATE_CD.XmlText#' )
CREATE DATABASE IF NOT EXISTS #agency_db#
CREATE TABLE #agency_db#.#tables[i]# LIKE agency_179.#tables[i]#
INSERT INTO #agency_db#.DocumentDesc (Agency_ID,DocumentType,Skill,Required,Description)
VALUES ('#Max.ID#','Employee','','1','Credentials/Qualifications/Resume'),
('#Max.ID#','Employee','HHA','1','Training Content'),
('#Max.ID#','Employee','HHA','1','Certificate of Training'),
('#Max.ID#','Employee','','1','Annual Performance Review'),
('#Max.ID#','Employee','','1','Independent Contractor Agreement'),
('#Max.ID#','Employee','','1','EMR Confidentiality Agreement'),
('#Max.ID#','Employee','','1','Competency Training ');
INSERT INTO #agency_db#.EventSubjects (Agency_ID,Subject,Created_By,Created_Date)
VALUES ('#Max.ID#','missing medical records','2',NOW()),
('#Max.ID#','equipment maintenance and/or operational problems','2',NOW()),
('#Max.ID#','inadequate supplies','2',NOW()),
('#Max.ID#','error (s) in medication administration, dosage, route, frequency','2',NOW()),
('#Max.ID#','error (s) in treatment','2',NOW()),
('#Max.ID#','failure of patient education material','2',NOW()),
('#Max.ID#','significant side effect (s) associated with medication','2',NOW()),
('#Max.ID#','significant side effect (s) associated with treatment','2',NOW()),
('#Max.ID#','error (s) in documentation/reporting','2',NOW()),
('#Max.ID#','patient or employee injury during care','2',NOW()),
('#Max.ID#','hazardous waste management violation','2',NOW()),
('#Max.ID#','fire or utility hazard','2',NOW()),
('#Max.ID#','workplace safety violation','2',NOW()),
('#Max.ID#','direct exposure to patient body fluid inclluding needle stick (s)','2',NOW()),
('#Max.ID#','violation of patient confidentiality, privacy, or security','2',NOW()),
('#Max.ID#','scheduling error','2',NOW());
INSERT IGNORE INTO #agency_db#.Payer (Agency_ID, Payer_Number,Provider_ID,PayerStatus,PayName,PayerPlan,InsurancePlan_or_ProgramName,
PayerType,PayerSource,Address1,Address2,City,State,Zip,Contact,Contact_Phone,Contact_Phone_Ext,
Output_Type,Submitter_ID,Submitter_Password,Receiver_ID,QB_Account,QB_Export,TimeAuthorization_Required,
UniqueAuth,Output_Action_Name,PriorAuthorization,TeamBill,IVANSorQuadax,IVANS_App,DDE_User,DDE_Password,
Ties_Associated_Assesment,Record_Creation_Date,Record_Created_By,TurboClaim)
VALUES ('#Max.ID#','15004','1234567','active','Medicare HH CGS J15 HHH','','','Home Health','','1 Cameron Hill Circle','Suite 0060','Chattanooga','TN','37402-0060','','877 299-4500','','Medicare','1234567','','15004','Private Insurance:PDN','0','0','1','0','0','0','','','','','1',Now(),'2','0');
INSERT IGNORE INTO #agency_db#.Revenue_Code
(Agency_ID,Payer_ID,Rev_Code,HCPCS,Tier,Measure,Visit_Type,Description,QB_Export,
RevenueCodeStatus,TimeAuthorization,Modifier,
GL_Account,Units,EClaim_Price,Balance,Quick_Books_Price,New_EClaim_Price)
VALUES
('#Max.ID#','1','424','G0151','0','0.25','Assessment','OASIS by PT','0','active','0','','','0','40','0','40','0'),
('#Max.ID#','1','421','G0151','0','0.25','Physical Therapy','Tx Visit','0','active','0','','','0','35','0','35','0'),
('#Max.ID#','1','421','G0157','0','0.25','Physical Therapy','PT Ass t Visit','0','active','0','','','0','35','0','35','0'),
('#Max.ID#','1','421','G0159','0','0.25','Physical Therapy','Maintenance Prgm','0','active','0','','','0','35','0','35','0'),
('#Max.ID#','1','424','G0151','0','0.25','Physical Therapy','Evaluation Visit','0','active','0','','','0','35','0','35','0'),
('#Max.ID#','1','431','G0152','0','0.25','Occupational Therapy','Evaluation Visit','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','431','G0152','0','0.25','Occupational Therapy','Tx Visit','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','431','G0158','0','0.25','Occupational Therapy','OT Ass t Visit','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','431','G0160','0','0.25','Occupational Therapy','Maintenance Prgm','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','441','G0153','0','0.25','Speech Therapy','Tx Visit','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','441','G0161','0','0.25','Speech Therapy','Maintenance Prgm','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','444','G0153','0','0.25','Speech Therapy','Tx Visit','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','551','G0299','0','0.25','Skilled Nursing','RN Visit','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','551','G0299','0','0.25','Assessment','OASIS by SN','0','active','0','','','0','40','0','40','0'),
('#Max.ID#','1','551','G0163','0','0.25','Skilled Nursing','Visit -Obsrvtn/Assmt','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','551','G0164','0','0.25','Skilled Nursing','Visit-Train/Educate','0','active','0','','','0','27.5','0','27.5','0'),
('#Max.ID#','1','571','G0156','0','0.25','Home Health Aide','HHA / CNA Visit','0','active','0','','','0','15','0','15','0'),
('#Max.ID#','1','551','G0300','0','0.25','Skilled Nursing','LPN visit','0','active','0','','','0','27.5','0','27.5','0');
Insert IGNORE into #agency_db#.Supply(Agency_ID,Supply,HCPCS,Revenue_Code,Units,Price,QB_Export,active)
values
('#Max.ID#','Adhesive remover wipes any type','A4365','270','0','2','0','Y'),
('#Max.ID#','4 x 4','A6230','270','0','2','0','Y'),
('#Max.ID#','Alcohol preps','A4245','270','0','2','0','Y'),
('#Max.ID#','Betadine or iodine swabs/wipes ,per box','A4247','270','0','2','0','Y'),
('#Max.ID#','Chux','T4554','270','0','2','0','Y'),
('#Max.ID#','nasal cannula','A4615','270','0','2','0','Y'),
('#Max.ID#','Blood testing supplies','A4770','270','0','2','0','Y'),
('#Max.ID#','Drain bag','A4357','270','0','2','0','Y'),
('#Max.ID#','Masks','0','270','0','2','0','Y'),
('#Max.ID#','Nonsterile applicators','0','270','0','2','0','Y'),
('#Max.ID#','Nonsterile gloves per 100','A4927','270','0','2','0','Y'),
('#Max.ID#','Specimen containers','0','270','0','2','0','Y'),
('#Max.ID#','Skin barrier ','A4362','270','0','2','0','Y'),
('#Max.ID#','Tape removal pads','0','270','0','2','0','Y'),
('#Max.ID#','Thermometer','0','270','0','2','0','Y'),
('#Max.ID#','Sterile dressings','0','270','0','2','0','Y'),
('#Max.ID#','Eye pads','A6410','270','0','2','0','Y'),
('#Max.ID#','Kling/Kerlix rolls','A6446','270','0','2','0','Y'),
('#Max.ID#','Sterile applicators','0','270','0','2','0','Y'),
('#Max.ID#','Sterile gauze/toppers','0','270','0','2','0','Y'),
('#Max.ID#','Sterile gloves per pair','A4930','270','0','2','0','Y'),
('#Max.ID#','Sterile solutions/ointments','0','270','0','2','0','Y'),
('#Max.ID#','Telfa pads','A6216','270','0','2','0','Y'),
('#Max.ID#','IV supplies','0','270','0','2','0','Y'),
('#Max.ID#','Ostomy supplies','0','270','0','2','0','Y'),
('#Max.ID#','Tubing (oxygen) per foot','A4616','270','0','2','0','Y'),
('#Max.ID#','Full length surgical stocking','A4510','270','0','2','0','Y'),
('#Max.ID#','Thigh length surg stocking','A4495','270','0','2','0','Y'),
('#Max.ID#','Below knee surgical stocking','A4500','270','0','2','0','Y'),
('#Max.ID#','Syringe with needle sterile 1cc or smaller','A4206','270','0','2','0','Y'),
('#Max.ID#','Lancets , per box of 100','A4259','270','0','2','0','Y'),
('#Max.ID#','Irrigation trays','0','270','0','2','0','Y'),
('#Max.ID#','Allevyn adhesive 3x3','A6212','270','0','2','0','Y'),
('#Max.ID#','Exu-dry Adult Buttocks Dressing (with Anti-Shear Layer)','A6253','270','0','2','0','Y'),
('#Max.ID#','Above knee surgical stocking','A4490','270','0','2','0','Y'),
('#Max.ID#','Adaptic','A6222','270','0','2','0','Y'),
('#Max.ID#','Hydrogel','A6248','270','0','2','0','Y'),
('#Max.ID#','Cotton tipped applicator','A4649','270','0','2','0','Y'),
('#Max.ID#','Allevyn adhesive 5x5','A6212','270','0','2','0','Y'),
('#Max.ID#','Allevyn adhesive 7x7','A6213','270','0','2','0','Y'),
('#Max.ID#','Allevyn adhesive 9x9','A6214','270','0','2','0','Y'),
('#Max.ID#','Allevyn adhesive heel','A6210','270','0','2','0','Y'),
('#Max.ID#','Needle any size each','A4215','270','0','2','0','Y'),
('#Max.ID#','Skin Barrier wipes box','A5120','270','0','2','0','Y'),
('#Max.ID#','Tubular dressing with or without elasticany widthper linear yard','A6457','270','0','2','0','Y'),
('#Max.ID#','Sterile water per 10ml','A4216','270','0','2','0','Y'),
('#Max.ID#','Alcohol/peroxide per pint','A4224','270','0','2','0','Y'),
('#Max.ID#','Kling/Kerlix rolls small','A6442','270','0','2','0','Y'),
('#Max.ID#','Ostomy clapany type replacement only ea','A4363','270','0','2','0','Y'),
('#Max.ID#','Ostomy skin barrier solid 4x4 or equivalent extended wear with built-in convexity each','A4411','270','0','2','0','Y'),
('#Max.ID#','Non waterproof tape per 18 sq. inches','A4450','270','0','2','0','Y'),
('#Max.ID#','Waterproof tape per 18 sq. inches','A4452','270','0','2','0','Y'),
('#Max.ID#','Pad bandage non-elastic, non-woven/non-knitted, > = 3 < 5 inches per roll','A6421','270','0','2','0','Y'),
('#Max.ID#','Conform bandage non-elastic knitted/woven non sterile width >= 3 < 5 inches per roll','A6422','270','0','2','0','Y'),
('#Max.ID#','Conform bandage non-elastic knitted/woven non-sterile width >= 5 inches per roll','A6424','270','0','2','0','Y'),
('#Max.ID#','Conform bandage non-elastic knitted/woven sterile >= 3 < 5 inches per roll','A6426','270','0','2','0','Y'),
('#Max.ID#','Conform bandage non-elastic knitted/woven sterile > =5 inches per roll','A6428','270','0','2','0','Y'),
('#Max.ID#','Light comp. Bandage elastic knitted/woven >= 3 < 5 inches per roll','A6430','270','0','2','0','Y'),
('#Max.ID#','LIght comp. Bandage elastic knitted/woven >= 5 inches per roll','A6432','270','0','2','0','Y'),
('#Max.ID#','Mod. Comp bandage elastic knitted/woven >= 3 < 5 inches per rol','A6434','270','0','2','0','Y'),
('#Max.ID#','HIgh comp bandage elastic knitted/woven > + 3 < 5 inches per roll','A6436','270','0','2','0','Y'),
('#Max.ID#','Self adher bandage, elastic, non-knitted/woven > = 3 < 5 inches per roll','A6438','270','0','2','0','Y'),
('#Max.ID#','A&D Barrier ointment','A6250','270','0','2','0','Y'),
('#Max.ID#','ABD pads Non-sterile','A6252','270','0','2','0','Y'),
('#Max.ID#','ABD pads, sterile','A6252','270','0','2','0','Y'),
('#Max.ID#','ABD/Combine Pads','A6252','270','0','2','0','Y'),
('#Max.ID#','Acticoat 7','A9270','270','0','2','0','Y'),
('#Max.ID#','Acticoat Absorbent Dressing (3/4 x 12 rope)','A6199','270','0','2','0','Y'),
('#Max.ID#','Acticoat Absorbent Dressing (4x5)','A6197','270','0','2','0','Y'),
('#Max.ID#','Allevyn sacrum','A6213','270','0','2','0','Y'),
('#Max.ID#','Band aid island dressing','A6251','270','0','2','0','Y'),
('#Max.ID#','Cloth adhesive tape','A4450','270','0','2','0','Y'),
('#Max.ID#','Conform stretch bandage','A6449','270','0','2','0','Y'),
('#Max.ID#','Duoderm (cover)','A6234','270','0','2','0','Y'),
('#Max.ID#','Exu-dry Incision Dressing (with anti-shear layer)','A6252','270','0','2','0','Y'),
('#Max.ID#','Medfix tape','A4452','270','0','2','0','Y'),
('#Max.ID#','Tegaderm transparent dressings','A6257','270','0','2','0','Y');
INSERT IGNORE INTO #agency_db#.CommunicationSubjects
(CommSub_ID,Agency_ID,Subject,Created_By,Created_Date,active)
VALUES
(3803,'#Max.ID#','admission phone call to patient','2',Now(),'Y'),
(3804,'#Max.ID#','caregiver update on patient status','2',Now(),'Y'),
(3805,'#Max.ID#','physician update on patient status','2',Now(),'Y'),
(3806,'#Max.ID#','patient s diagnoses','2',Now(),'Y'),
(3807,'#Max.ID#','assessment corrections needed','2',Now(),'Y'),
(3808,'#Max.ID#','change to physician orders','2',Now(),'Y'),
(3809,'#Max.ID#','home health aide supervision','2',Now(),'Y');
SELECT Agency_Login FROM hhapowerpath.Agency WHERE Agency_ID = '#Max.ID#'
INSERT IGNORE INTO hhapowerpath.Employee
(
Agency_ID,User_ID,Status,Username,Password,Level,Authority_Level,Last_Name,First_Name,Address1,Address2,City,State,Zip,
Home_Phone,Cell_Phone,mobiledomain,Beeper,Email,Birthdate,Skill,Skill2,Skill3,Block,
Skill_Long,Soc_Sec_No,Gender,PayFrequency,EmployeeType,telephony_employeenumber,PasswordReset,
ScheduleEdit,HireDate,CEU_Annual,Inservice_Annual,Professional,Drivers,Auto,Background,Physical,
CPR,TB,Latitude,Longitude,DistanceTravel,Login_id,SundayMaxVisit,MondayMaxVisit,TuesdayMaxVisit,
WednesdayMaxVisit,ThursdayMaxVisit,FridayMaxVisit,SaturdayMaxVisit,Daily_Mail_Date,Weekly_Mail_Date,2Week_Mail_Date,
Monthly_Mail_Date,License_Mail_Date,Securityquestionid,Answer,SendCrons,OutcmFrequency,SurveyFrequency,DashFrequency,
online_chat,DE_Comm,DE_Assess,DE_Notes,DE_Unapproved,DE_Outcomes,DE_Rollover_No,DE_Schedule_Wrong,DE_Trans_TA,DE_RAP_TA,
DE_Final_TA,DE_Order_TA,DE_Careplan_Items,DE_Progress_Items,DE_Act_Pts,DE_Act_Pts_MC,DE_AVG_Pt_Satisfaction,DE_AVG_Process,
DE_AVG_UtilM,DE_AVG_COMPARE,DE_AVG_Other_HHA,DE_AVG_Acute_Adm,DE_AVG_Index,AVG_Utilization,Pain_Better,Pain_Same,
Pain_Worse,SOB_Better,SOB_Same,SOB_Worse,Bath_Better,Bath_Same,Bath_Worse,Trans_Better,Trans_Same,Trans_Worse,
Amb_Better,Amb_Same,Amb_Worse,Med_Better,Med_Same,Med_Worse,Total_Better,Total_Same,Total_Worse,total_outcomes,
total_avg_hhcompare,total_avg_VBP,OMbetter,OMsame,OMworse,OMbetter_DC,OMworse_DC,OMsame_DC,Survey,Terminatdate,Notes,
Record_Creation_Date,Record_Created_By,Record_Mod_Date,Record_Mod_By,Password_Reset_Timing,Inservice_units,cont_units,
M0104,M1021,M1041,M1051,M1240,M1242,M1300,M1324,M1334,M1342,M1400,M1510,M1600,M1730,M1830,M1840,M1845,M1850,M1860,M1910,
M2010,M2015,M2020,M2102_a,M2102_b,M2102_c,M2102_d,M2102_e,M2102_f,M2102_g,M2250_b,M2250_e,M2250_f,M2250_g,M2300_1,
M2410_1,M2420,AvgVBP_SOC,
AvgVBP_DC,Avg5Star_SOC,Avg5Star_DC,AvgHHC_SOC,AvgHHC_DC,AvgPAE_SOC,AvgPAE_DC,Avg5Star_TDC,AvgVBP_TDC,AvgHHC_TDC,AvgPAE_TDC
)
VALUES
(
'#Max.ID#',2733,'active','#Agency_Login.Agency_Login#','Joy104',9,1,'De Castro','Joy','333 N. Madison Ave','','Joliet','IL',60435,
207-555-1212,815-405-0361,17,'','carey@myhomecarebiz.com','1957-11-19','SN','RN','LPN',0,'Skilled Nursing',111-11-1111, 'Female','Choose...','Employee',4,'',0,'2005-03-16',0,0,0,1,0,0,0,0,0,'undefined','ndefined','',754,'','','','','','','','','','','','2012-05-27',0,'','','Weekly','Choose...'
,'','',0,0,0,39,0,'',1,'','','','','',11,2,1,80,70,'','','',7,'',0,'','','','','','','','','','','','','','','','','','','','','',
'',0,'','','','','','','',
'No','','t4esting notes','2007-04-28',1415,'2016-09-21',2733,'',7,7,100,0,0,0,100,0,100,0,0,0,0,0,0,100,0,0,0,0,0,
100,100,0,0,0,0,0,0,0,0,0,100,100,100,100,0,0,0,0,0,29,22,59,45,0,0,'','','',''
)
SELECT MAX(Employee_ID) as MAX_EMPLOYEE_ID FROM hhapowerpath.Employee
INSERT IGNORE INTO #agency_db#.employee_auth
(
eaID,eaAuthority,eaSetup,eaSetSchedule,eaSetCareplanItems,eaSetCommSubjects,eaSetEmployees,
eaSetIncidentSubjects,eaSetPayers,eaSetPayrollCodes,eaSetPhysicians,eaSetReferralSrcs,eaSetRevenueCodes,
eaSetSupplies,eaSetAgyInfoUpdate,eaPayroll,eaBilling,eaOrders,eaAuthorizations,eaDataTransmit,eaType,
eaCareplan,eaLock,eaSchedule,eaClientEdit,eaNoteEdit,eaUndo,eaReports,eaStatistics,eaRole,eaPatientSurvey,
eaFileEdit,eaonlinechat
)
VALUES
(
#employee_ID.MAX_EMPLOYEE_ID#,1,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,
'Project Manager,Human Resources,Payroll,Billing,Intake,Clinical Field Staff,Scheduling,
Clinical Quality Assurance,Medical Records',1,1,1
)
SELECT * FROM
#Request.prefix_db_agency#.patient
WHERE
SSN_M0064 = '#mydoc.Assessment.M0064_SSN.XmlText#'
AND First_Name_M0040 = '#mydoc.Assessment.M0040_PAT_FNAME.XmlText#'
AND Last_Name_M0040 = '#mydoc.Assessment.M0040_PAT_LNAME.XmlText#'
AND DOB_M0066 = '#mydoc.Assessment.M0066_PAT_BIRTH_DT.XmlText#'
SELECT * FROM #Request.prefix_db_lookup#.zip_cbsa
WHERE ZIPCODE = '#mydoc.Assessment.M0060_PAT_ZIP.XmlText#'
SELECT * FROM #Request.prefix_db_lookup#.PECOS
JOIN #Request.prefix_db_lookup#.Physician on PECOS.NPI = Physician.Natl_Provider_Id
JOIN #Request.prefix_db_lookup#.Employee on Employee.First_name = PECOS.First_name and Employee.First_name = PECOS.First_name
WHERE
PECOS.NPI = '#mydoc.Assessment.M0018_PHYSICIAN_ID.XmlText#'
AND Employee.status = 'active'
AND Employee.EmployeeType = 'Physician'
Group By PECOS.NPI
UPDATE IGNORE #Request.prefix_db_agency#.patient
SET
Agency_ID = '#session.AgencyId#',Status = 'active',First_Name_M0040 = '#mydoc.Assessment.M0040_PAT_FNAME.XmlText#',
Middle_Initial_M0040 = '#mydoc.Assessment.M0040_PAT_MI.XmlText#','',
Last_Name_M0040 = '#mydoc.Assessment.M0040_PAT_LNAME.XmlText#','',
Suffix_M0040 = '#mydoc.Assessment.M0040_PAT_SUFFIX.XmlText#',State_M0050 = '#mydoc.Assessment.M0050_PAT_ST.XmlText#',
Zip_M0060 = '#mydoc.Assessment.M0060_PAT_ZIP.XmlText#',City = '#GetZ.CITY#',
Medicare_Number_M0063 = '#mydoc.Assessment.M0063_MEDICARE_NUM.XmlText#',Medicare_Number_NA_M0063 = 'NA - Not Applicable',
SSN_M0064 = '#mydoc.Assessment.M0064_SSN.XmlText#',SSN_M0064_NA = 'UK - Unknown or Not Available',
Medicaid_Number_M0065 = '#mydoc.Assessment.M0065_MEDICAID_NUM.XmlText#', Medicaid_Number_NA_M0065 = 'NA - Not Applicable',
Primary_ReferringPhys_ID_M0072 = '#M0018_PHYSICIAN_ID#', Primary_ReferringPhys_ID_NA = 'UK - Unknown',
Race_M0140 = '#race#',Current_Payment_Sources_M0150 = '#Current_Payment_Sources#',
DOB_M0066 ='#mydoc.Assessment.M0066_PAT_BIRTH_DT.XmlText#',Gender_M0069 = '#mydoc.Assessment.M0069_PAT_GENDER.XmlText#',WageIndex= '#GetZ.WageIndex#'
,UrbanRural = '#GetZ.UrbanRural#',Latitude = '#GetZ.Latitude#'0,Longitude = '#GetZ.Longitude#'0,Record_creation_Date = #CreateODBCDate(Now())#,Record_Created_By = '#Session.CS.EmployeeID#',Record_Mod_Date = #CreateODBCDate(Now())#,
Record_Mod_By = '#Session.CS.EmployeeID#'
WHERE Patient_ID = '#Getpatient.Patient_ID#'
INSERT IGNORE INTO #Request.prefix_db_agency#.patient
(
Agency_ID,Status,First_Name_M0040,
Middle_Initial_M0040,Last_Name_M0040,Suffix_M0040,State_M0050,Zip_M0060,City,
Medicare_Number_M0063,Medicare_Number_NA_M0063,
SSN_M0064,SSN_M0064_NA,
Medicaid_Number_M0065, Medicaid_Number_NA_M0065,
Primary_ReferringPhys_ID_M0072, Primary_ReferringPhys_ID_NA,
Race_M0140,Current_Payment_Sources_M0150,
DOB_M0066,Gender_M0069,WageIndex
,UrbanRural,Latitude,Longitude,Record_creation_Date,Record_Created_By,Record_Mod_Date,Record_Mod_By
)
VALUES
(
'#session.AgencyId#','active','#mydoc.Assessment.M0040_PAT_FNAME.XmlText#',
'#mydoc.Assessment.M0040_PAT_MI.XmlText#','',
'#mydoc.Assessment.M0040_PAT_LNAME.XmlText#','',
'#mydoc.Assessment.M0040_PAT_SUFFIX.XmlText#','#mydoc.Assessment.M0050_PAT_ST.XmlText#','#mydoc.Assessment.M0060_PAT_ZIP.XmlText#','#GetZ.CITY#',
'#mydoc.Assessment.M0063_MEDICARE_NUM.XmlText#','NA - Not Applicable',
'#mydoc.Assessment.M0064_SSN.XmlText#','UK - Unknown or Not Available',
'#mydoc.Assessment.M0065_MEDICAID_NUM.XmlText#','NA - Not Applicable',
'#M0018_PHYSICIAN_ID#', 'UK - Unknown',
'#race#','#Current_Payment_Sources#',
'#mydoc.Assessment.M0066_PAT_BIRTH_DT.XmlText#','#mydoc.Assessment.M0069_PAT_GENDER.XmlText#','#GetZ.WageIndex#'
,'#GetZ.UrbanRural#'
,'#GetZ.Latitude#',0
,'#GetZ.Longitude#',0
,#CreateODBCDate(Now())#,'#Session.CS.EmployeeID#',#CreateODBCDate(Now())#,'#Session.CS.EmployeeID#'
)
SELECT LAST_INSERT_ID() as LastInsertID
insert into #Request.prefix_db_agency#.assessment_Tracker
(
Agency_ID, Employee_ID, Patient_ID,Assessment_ID,Status
)
values
(
'#session.AgencyId#', '#Session.CS.EmployeeID#', '#Patient_ID#', 0,'active'
)
select LAST_INSERT_ID() as keyvalue
SELECT * FROM #Request.prefix_db_agency#.Assessment_New
WHERE
Assessment_Reason_M0100 = '#M0100#'
AND Assessment_Completed_M0090 = '#M0090#'
AND Patient_ID = '#Patient_ID#'
AND Deleted <> 1
UPDATE IGNORE #Request.prefix_db_agency#.Assessment_New
SET
Patient_ID = '#Patient_ID#' ,
Agency_Number = '#session.AgencyId#',
Agency_ID = '#session.AgencyId#',
OASIS_Version_Number = '#mydoc.Assessment.ITM_SET_VRSN_CD.XmlText#' ,
M0102_Physician_SOC = #CreateODBCDate(M0102)#,
M0104_Date_of_Referral = #CreateODBCDate(M0104)#,
Date_Most_Recent_M0903 = '#M0903_LAST_HOME_VISIT#',
Date_Discharge_M0906 = '#M0906_DC_TRAN_DTH_DT#',
HHAC_Agency_ID = '#mydoc.Assessment.HHA_AGENCY_ID.XmlText#',
HIPPS_Code = '#mydoc.Assessment.SUBM_HIPPS_CODE.XmlText#',
Physician_ID = '#M0018_PHYSICIAN_ID#',
Start_of_Care_M0030 = #CreateODBCDate(M0030)#,
Resump_of_Care_M0032 = #CreateODBCDate(M0032)#,
Resump_of_Care_NA_M0032 = '#M0032NA#',
Assessment_Completed_M0090 = #CreateODBCDate(M0090)#,
Inpatient_Discharge_Date_M0180 = #CreateODBCDate(M1005)#,
Inpatient_Discharge_Date_NA_M0180 = 'not applicable',
Transmission_Status = 'OASIS unlocked',
Record_Creation_Date = #CreateODBCDate(Now())#,
HHRG_175_Score = '#Replace(HHRG_175, "^", "", "ALL")#',
HHRG_230_Score = '#Replace(HHRG_230, "^", "", "ALL")#',
HHRG_250_Score = '#Replace(HHRG_250, "^", "", "ALL")#',
HHRG_390_Score = '#Replace(HHRG_390, "^", "", "ALL")#',
HHRG_420_Score = '#Replace(HHRG_420, "^", "", "ALL")#',
HHRG_440_Score = '#Replace(HHRG_175, "^", "", "ALL")#',
M0826_Ther_Need ='#M0826#' , M0826_NA = 'NA - Not Applicable',
Pressure_Ulcer_b_M0450 = '#mydoc.Assessment.M1311_NBR_PRSULC_STG2_A1.XmlText#',
Pressure_Ulcer_c_M0450 = '#mydoc.Assessment.M1311_NBR_PRSULC_STG3_B1.XmlText#',
Pressure_Ulcer_d_M0450 = '#mydoc.Assessment.M1311_NBR_PRSULC_STG4_C1.XmlText#',
Pressure_Ulcer_e_M0450 = '#mydoc.Assessment.M1311_NSTG_DRSG_D1.XmlText#',
M1308_d_2_Pressure = '#mydoc.Assessment.M1311_NSTG_CVRG_E1.XmlText#',
M1308_d_3_Pressure = '#mydoc.Assessment.M1311_NSTG_DEEP_TSUE_F1.XmlText#',
Certification_Start = #CreateODBCDate(Certify_start_date)#,
Certification_End = #CreateODBCDate(Certify_End_date)#,
M1060_height = '#M1060_HEIGHT_A#',
M1060_weight = '#M1060_WEIGHT_B#'
WHERE
Assessment_ID = #getassessment.Assessment_ID#
INSERT IGNORE INTO #Request.prefix_db_agency#.Assessment_New
(
Assessment_Tracker_ID,
Patient_ID,
Agency_Number,
Agency_ID,
OASIS_Version_Number,
M0102_Physician_SOC,
M0104_Date_of_Referral,
Date_Most_Recent_M0903,
Date_Discharge_M0906,
HHAC_Agency_ID,
HIPPS_Code,
Physician_ID,
Start_of_Care_M0030,
Resump_of_Care_M0032,
Resump_of_Care_NA_M0032,
Assessment_Completed_M0090,
Inpatient_Discharge_Date_M0180,
Inpatient_Discharge_Date_NA_M0180,
Transmission_Status,
Record_Creation_Date,
HHRG_175_Score,
HHRG_230_Score,
HHRG_250_Score,
HHRG_390_Score,
HHRG_420_Score,
HHRG_440_Score,
M0826_Ther_Need, M0826_NA,
Pressure_Ulcer_b_M0450,
Pressure_Ulcer_c_M0450,
Pressure_Ulcer_d_M0450,
Pressure_Ulcer_e_M0450,
M1308_d_2_Pressure,
M1308_d_3_Pressure,
Certification_Start,
Certification_End,
M1060_height,
M1060_weight,
cdate_cdarte
)
VALUES
(
'#LastInsertAssTracker.keyvalue#',
'#Patient_ID#',
'#session.AgencyId#',
'#session.AgencyId#',
'#mydoc.Assessment.ITM_SET_VRSN_CD.XmlText#',
#CreateODBCDate(M0102)#,
#CreateODBCDate(M0104)#,
'#M0903_LAST_HOME_VISIT#',
'#M0906_DC_TRAN_DTH_DT#',
'#mydoc.Assessment.HHA_AGENCY_ID.XmlText#',
'#mydoc.Assessment.SUBM_HIPPS_CODE.XmlText#','',
'#M0018_PHYSICIAN_ID#',
#CreateODBCDate(M0030)#,
#CreateODBCDate(M0032)#,
'#M0032NA#',
#CreateODBCDate(M0090)#,
#CreateODBCDate(M1005)#,'UK - Unknown',
'not applicable',
null,
'OASIS unlocked',
#CreateODBCDate(Now())#,
'#Replace(HHRG_175, "^", "", "ALL")#',
'#Replace(HHRG_230, "^", "", "ALL")#',
'#Replace(HHRG_250, "^", "", "ALL")#',
'#Replace(HHRG_390, "^", "", "ALL")#',
'#Replace(HHRG_420, "^", "", "ALL")#',
'#Replace(HHRG_440, "^", "", "ALL")#',
'#M0826#', 'NA - Not Applicable',
'#mydoc.Assessment.M1311_NBR_PRSULC_STG2_A1.XmlText#','',
'#mydoc.Assessment.M1311_NBR_PRSULC_STG3_B1.XmlText#','',
'#mydoc.Assessment.M1311_NBR_PRSULC_STG4_C1.XmlText#','',
'#mydoc.Assessment.M1311_NSTG_DRSG_D1.XmlText#','',
'#mydoc.Assessment.M1311_NSTG_CVRG_E1.XmlText#','',
'#mydoc.Assessment.M1311_NSTG_DEEP_TSUE_F1.XmlText#','',
#CreateODBCDate(Certify_start_date)#NULL,
#CreateODBCDate(Certify_End_date)#NULL
,'#M1060_HEIGHT_A#'
,'#M1060_WEIGHT_B#'
,now()
)
SELECT LAST_INSERT_ID() as LastInsertID
UPDATE #Request.prefix_db_lookup#.XML_Text SET Answer = '#Evaluate(Variables.original)#' WHERE Question = '#Question#'
SELECT Answer.Answer_label,Answer.Answer,Answer.Checkbox from #Request.prefix_db_lookup#.XML_Text
left outer join #Request.prefix_db_lookup#.Answer on (right(XML_Text.Answer,1) = left(Answer.Answer,1) OR (XML_Text.Answer = left(Answer.Answer,2)) OR (right(XML_Text.Answer,1) = Answer.Checkbox))
where XML_Text.ID = Answer.Parent_ID
AND (Answer.Type = '#thereason#' or Answer.Type = 'Assessment')
select * from #Request.prefix_db_agency#.Assessment_New WHERE Assessment_ID = #Assessment_ID#
Update IGNORE #Request.prefix_db_agency#.Assessment_New a
SET a.#Answer_label# = '#Replace(final_Answer, "^", "", "ALL")#'
WHERE a.Assessment_ID = #Assessment_ID#
Update IGNORE #Request.prefix_db_agency#.Assessment_New a
SET a.AssessmentType_New = 1,
a.Admission_Status='Complete'
WHERE a.Assessment_ID = #Assessment_ID#
SELECT Answer.Answer_label,Answer.Answer,Answer.Checkbox,XML_Text.Question from #Request.prefix_db_lookup#.XML_Text
left outer join #Request.prefix_db_lookup#.Answer on XML_Text.Question = Answer.Question
where XML_Text.ID = Answer.Parent_ID
AND Answer.Type = 'Diagnosis' ORDER BY XML_Text.ID ASC
UPDATE IGNORE #Request.prefix_db_agency#.Diagnosis SET
SEVERITY ='#Replace(Evaluate(Variables.sev), "^", "", "ALL")#'
ORDER BY Diagnosis_ID DESC LIMIT 1
INSERT IGNORE INTO #Request.prefix_db_agency#.Diagnosis
(
Assessment_ID,Assessment_Tracker_ID,Patient_ID,Agency_ID,Diagnosis,ICD,Code_Type,Pathway_Type,
Short_Diagnosis,Text_ICD, Clinical_Type
)
SELECT Assessment_ID,Assessment_Tracker_ID,Patient_ID,Agency_ID,Diagnosis,ICD,Code_Type,Pathway_Type,Short_Diagnosis,ICD,Pathway_ID from Dx_list_ICD10 LEFT OUTER JOIN #Request.prefix_db_agency#.Assessment_New on Assessment_New.Assessment_ID = #Assessment_ID#
WHERE Dx_list_ICD10.ICD like '#Replace(Evaluate(Variables.Diag), "^", "", "ALL")#%'
SELECT LAST_INSERT_ID() as LastInsertID
SELECT * FROM
#Request.prefix_db_lookup#.CW_VisPlan_Symptoms
WHERE
3Digit= LIMIT 1
SELECT * FROM
#Request.prefix_db_lookup#.Dx_list_ICD10_PDGM
WHERE
Dx_list_ICD10_PDGM.ICD=
SELECT * FROM
#Request.prefix_db_agency#.Teaching_Skills
WHERE
Patient_ID=
AND Assessment_ID=
AND ICD=
SELECT * FROM
#Request.prefix_db_lookup#.Teaching_Symptoms
WHERE
3Digit=
INSERT INTO #Request.prefix_db_agency#.Teaching_Skills
(
Patient_ID, Assessment_ID, ICD, 3Digit, Pathway_Type, Pathway_ID, Step_ID, Line_ID, Explanation, Created_Date, Created_by, Updated_Date, Updated_by, Diagnosis_ID, Order_ID, Title_Order_ID
)
VALUES
(
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
)
SELECT * FROM
#Request.prefix_db_lookup#.Teaching_Combo
WHERE
3Digit=
AND Pathway_ID IS NOT NULL
GROUP BY Pathway_ID
SELECT * FROM #
Request.prefix_db_lookup#.Teaching_Prevention
WHERE
Pathway_ID=
INSERT INTO #Request.prefix_db_agency#.Teaching_Skills
(
Patient_ID, Assessment_ID, ICD, 3Digit, Pathway_Type, Pathway_ID, Step_ID, Line_ID, Explanation, Created_Date, Created_by, Updated_Date, Updated_by, Diagnosis_ID, `Order_ID`, Title_Order_ID
)
VALUES
(
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
)
UPDATE IGNORE #Request.prefix_db_agency#.Diagnosis
SET
symptoms = '#insert_CW_Visit_Plans.Explanation#',
M0190_Link ='#M0190_Link#',
Dx_Order = #M0190_count#,
M0210_Link ='#M0210_Link#',
Dx_Order = #M0210_count#,
M0230_Link ='#M0230_Link#',
Dx_Order = #M0230_count#,
Secondary_Diagnosis = '1'
Primary_Diagnosis = '#Answer#'
WHERE
Diagnosis_ID = '#Diagnosis_ID#'
SELECT Answer.Answer_label,Answer.Answer,Answer.Checkbox from #Request.prefix_db_lookup#.XML_Text
left outer join #Request.prefix_db_lookup#.Answer on (right(XML_Text.Answer,1) = left(Answer.Answer,1) OR (XML_Text.Answer = left(Answer.Answer,2)) OR (right(XML_Text.Answer,1) =Answer.Checkbox))
where XML_Text.ID = Answer.Parent_ID
AND (Answer.Type = 'patient')
select * from #Request.prefix_db_agency#.patient WHERE Patient_ID = '#Patient_ID#'
Update IGNORE #Request.prefix_db_agency#.patient p
SET p.#Answer_label# = '#Replace(final_Answer, "^", "", "ALL")#'
WHERE p.Patient_ID = #Patient_ID#
UPDATE #Request.prefix_db_lookup#.XML_Text SET Answer = null
UPDATE #Request.prefix_db_lookup#.Agency SET coding_upload_count = coding_upload_count + 1 WHERE Agency_ID = '#session.AgencyId#'
UPDATE IGNORE #Request.prefix_db_agency#.Diagnosis
SET error_check =
WHERE Diagnosis_ID =