select agency_id from #Request.prefix_db_lookup#.Agency where status='active' ORDER BY agency_id select agency_id from #Request.prefix_db_lookup#.Agency where status='active' AND Agency_ID = #url.Agency_ID# ORDER BY agency_id SELECT pPayer.*, pPtPayer.PtPayer_ID, Agency.Agency_Name, Agency.Agency_ID, Agency.Natl_Provider_ID, pPatients.Pt_Last,pPatients.Pt_First,pPatients.Pt_DOB,pPatients.Pt_Gender, pPtPayer.Sub_HIC,pPatients.Patient_ID FROM #Request.prefix_db_agency#.pPtPayer JOIN #Request.prefix_db_agency#.pPayer ON pPtPayer.Payer_ID = pPayer.Pay_ID AND pPayer.Status = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pPtPayer.Patient_ID AND pPatients.Status IN (0) JOIN #Request.prefix_db_lookup#.Agency ON Agency.Agency_ID = pPatients.Loc_ID WHERE pPtPayer.Status = 0 AND pPatients.Loc_ID = '#Eligibility[ind]#' GROUP BY pPtPayer.PtPayer_ID SELECT IFNULL(MAX(Elig_ID),0)+1 As pElig_ID FROM #Request.prefix_db_agency#.Eligibility ~" & chr(13)& chr(10) & "GS*HS*#Receiver_ID#*#Receiver_ID#*#DateFormat(NOW(),"yymmdd")#*#creationtime#*#Natl_Provider_ID#*X*005010X279A1~"& chr(13)& chr(10) & "ST*270*#Eligiunique_ID#*005010X279A1~"& chr(13)& chr(10) & "BHT*0022*13*#Eligiunique_ID#*#creationdate#*#creationtime#~"& chr(13)& chr(10) & "HL*1**20*1~" & chr(13)& chr(10)> Insert Into #Request.prefix_db_agency#.Eligibility (Patient_ID, PtPayer_ID, Status,batch,donot_display,line270,Verification_Date, Start_Date, Date_Create, Created_by, Date_Change, Change_by) VALUES ( , , , , , , , , , , , )