UPDATE #Request.prefix_db_agency#.Invoices
SET claim_status = '#url.claimstatus#'
,Record_Modified_By = '#Session.EmployeeId#'
,Record_Modified_Date = #now()#
where Invoice_ID = '#url.Invoiceid#'
SELECT *
FROM #Request.prefix_db_agency#.claim_bill
WHERE Invoice_ID = '#url.inv#'
SELECT DISTINCT
e.Emp_Last AS Last_Name ,e.Emp_First AS First_Name ,e.Emp_ID,
a.Agency_Name,a.Agency_Address1,a.Agency_City,a.Agency_State,a.Agency_ZIP,a.Agency_Contact_Phone,a.Agent_FEIN, a.Natl_Provider_ID,
p.Pt_Agy_ID AS Agency_Assigned_ID, p.Pt_Agy_ID AS HI_Claim_Number, an.F25 AS Admission_Status,
p.Pt_Last AS Last_Name_M0040, p.Patient_ID,p.Pt_First AS First_Name_M0040, p.Pt_Middle AS Middle_Initial_M0040,
p.Pt_Street AS Street_Address
,p.Pt_Gender AS Gender_M0069, p.Pt_City AS City, p.Pt_State AS State_M0050,
p.Pt_Zip AS Zip_M0060, p.Pt_DOB AS DOB_M0066,"" AS MSA_Code, "" AS FIPS_Code, p.Pt_Agy_ID AS Agency_Assigned_ID,"" AS Lives_With_M0340, p.Pt_Race AS Race_M0140_NA, p.Pt_Phone AS Telephone,an.F26 AS Assessment_Reason_M0100,si.Visit_Date, an.F177 AS Certification_start,
pAdmit.M0030_START_CARE_DT AS Start_of_Care_M0030,an.F178 AS Certification_End, pAdmit.Admission_Source AS Source_of_Admission, an.F300 AS HIPPS
,"" AS Claim_Key, "" AS Information_Release, "" AS Admission_Description ,"" AS Homebound_Status
,"" AS HHRG_175_Score, "" AS HHRG_230_Score,"" AS HHRG_250_Score
,"" AS HHRG_390_Score, "" AS HHRG_420_Score, "" AS HHRG_440_Score,"" AS HHRG_450_Score
,an.F176 AS Assessment_Completed_M0090,an.Assmt_ID ,
pr.Pay_Name As PayName,
i.Invoice_ID, i.InvoiceTotal, i.PayerBalance,i.Assmt_ID ,i.errors,i.TOB,i.Internal_control_number,
i.claim_status,i.Error_description,
i.patient_id,pr.Pay_ID AS Payer_ID, pr.Submitter_ID, pr.Receiver_ID, pr.Pay_Name, pr.Payer_No AS Payer_Number, pr.Pay_Street AS PayAdd1,"" AS PayAdd2, pr.Pay_City AS PayCity, pr.Pay_State AS PayState, pr.Pay_State AS PayZip , 0 AS TurboClaim
FROM
#Request.prefix_db_agency#.Invoices i
JOIN #Request.prefix_db_agency#.pSchedules si ON i.patient_id = si.patient_id
JOIN #Request.prefix_db_agency#.pAssessments an ON i.Assmt_ID = an.Assmt_ID
JOIN #Request.prefix_db_agency#.pAdmit ON an.Admit_ID = pAdmit.Admit_ID
JOIN #Request.prefix_db_agency#.pPatients p ON p.patient_id = i.patient_id
JOIN #Request.prefix_db_agency#.pPayer pr ON pr.pay_id = i.patientpayer_id
JOIN #Request.prefix_db_lookup#.Agency a ON a.Agency_ID = i.Agency_ID
JOIN #Request.prefix_db_lookup#.pEmployee e ON e.Emp_ID = si.Emp_ID
WHERE i.invoice_id = '#url.inv#'
AND i.Agency_ID = '#Session.AgencyId#'
AND i.Deleted <> 1
AND pr.Status = '0'
AND i.InvoiceTotal > 0
AND p.status < 2
AND si.Status = 0
AND an.Status = 0
GROUP BY i.invoice_id
SELECT DISTINCT
i.Invoice_ID,i.InvoiceTotal, i.PayerBalance,i.Assmt_ID ,i.errors,i.Internal_control_number,
i.claim_status,i.Error_description,
i.patient_id,
ID,claim_bill.Type_of_Bill as TOB , Assignment_Accept ,Submitter_ID , Receiver_ID , Agency_Name ,Agent_FEIN , Agency_Contact ,Agency_Contact_Phone , PayName ,
ANPI as Natl_Provider_ID,Agency_Address1 ,Agency_City , Agency_State ,Agency_Zip , Last_Name_M0040 ,First_Name_M0040 , HI_Claim_Number ,Street_Address ,City ,
State_M0050 ,Zip_M0060 ,DOB_M0066 ,Gender_M0069 ,Payer_Number ,PayAdd1 ,PayCity ,PayState ,PayZip ,Certification_start ,claim_bill.Certification_end ,
Start_of_Care_M0030 ,Source_of_Admission ,Admission_Status ,Claim_Key ,HHRG_175_Score ,HHRG_230_Score ,HHRG_250_Score ,HHRG_390_Score ,
HHRG_420_Score ,HHRG_440_Score ,MSA_Code ,Referring_LastName as Last_Name,Referring_FirstName as First_Name ,Referring_PNPI ,Certifying_LastName ,Certifying_FirstName ,
Certifying_PNPI,Agency_Assigned_ID,Telephone, Assessment_Reason_M0100
FROM #Request.prefix_db_agency#.claim_bill
JOIN #Request.prefix_db_agency#.Invoices i ON claim_bill.Invoice_ID = i.Invoice_ID
WHERE i.invoice_id = '#url.inv#'
AND i.Agency_ID = '#Session.AgencyId#'
AND i.Deleted <> 1
AND i.InvoiceTotal > 0
GROUP BY i.invoice_id
SELECT DISTINCT si.Patient_ID,si.Assmt_ID,i.PatientPayer_ID,
count(s.Supply_Sched_ID) as total, si.Skill AS Visit_Type,si.Visit_Date,si.Total_Units, si.Total_Eclaim ,si.Schedule_ID,
i.Invoice_ID, i.InvoiceTotal, i.PayerBalance,s.Supply_Sched_ID,
sp.Supply, s.Revenue_Code as supply_Revcode, sp.HCPCS as Supply_HCPCS, s.Total_Units as Supply_Units, s.Visit_Date as supply_Visit_Date, s.Payer_ID, s.Total_Price,
rc.Rev_CodeID, rc.Visit_Type, rc.Description,
rc.Rev_Code, rc.HCPCS, rc.Payer_ID,rc.Status, rc.EClaim_Price, rc.Measure
FROM
#Request.prefix_db_agency#.Invoices i
JOIN #Request.prefix_db_agency#.pSchedules si on si.invoice_id = i.invoice_id
LEFT OUTER JOIN #Request.prefix_db_agency#.Supply_Sched s on s.Schedule_ID = si.Schedule_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.Supply sp on s.Supply_ID = sp.Supply_ID
RIGHT OUTER JOIN #Request.prefix_db_agency#.pRevenue_Code rc on rc.Rev_CodeID = si.Rev_ID
WHERE i.invoice_id = '#url.inv#'
AND i.Agency_ID = '#session.agencyid#'
AND si.invoice_id = i.invoice_id
AND si.Assmt_ID = i.Assmt_ID
AND si.status = 0
GROUP BY si.Schedule_ID
order by si.Visit_Date
HEALTH INSURANCE UB04 CLAIM FORM
INTERNAL CONTROL NUMBER :
#GetF.Internal_control_number#
