"" AS ApproveBill,pAssessments.F300 AS HIPPS_Code,pAssessments.F177 as start_date, pAssessments.F178 as end_date,pAssessments.F26 AS Assessment_Reason_M0100, pAssessments.F177, '' AS TRANSTATUS, pAssessments.F26 AS Reason, "complete" AS Status, 0 AS Assessment_Number,pAssessments.Assmt_ID, pAssessments.F177 AS Certification_Start,pAssessments.F178 AS Certification_End,
DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) AS interval_30_day,pAssessments.F178, pAssessments.F300 AS HIPPS_Code, '' AS Homebound_Status, pPayer.Pay_ID AS Payer_ID, pPayer.Pay_ID AS PAYID, pPayer.Output as PAYOUT, pPayer.Pay_Name, "" AS notes ,pAssessments.Submitter_ID,
pAssessments.F290 AS UTN_1, pAssessments.F291 AS UTN_2, pAssessments.F200 AS Date_Sent,pAssessments.F199 AS Date_Received, pAssessments.F292 AS Sub_Status, pAssessments.F293 AS Sub_Status2, pAssessments.F293 AS Episode,pAssessments.Record_Created_By, pPtPayer.Sub_State
FROM #Request.prefix_db_agency#.pPatients
JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Patient_ID = pPatients.Patient_ID
JOIN #Request.prefix_db_agency#.pPtPayer on pPatients.Patient_ID = pPtPayer.Patient_ID
JOIN #Request.prefix_db_agency#.pPayer on pPtPayer.Payer_ID = pPayer.Pay_ID
WHERE pPatients.Status < 2
AND pAssessments.Status = 0
AND pPtPayer.status = 0
AND pPayer.Pay_Name = 'Medicare'
AND pAssessments.F26 like '1%'
AND pPayer.Pay_State IN ('IL','OH','TX','NC','FL','OK')
GROUP BY pAssessments.Assmt_ID
ORDER BY First_Name_M0040,pAssessments.Assmt_ID ASC
--->
SELECT pPatients.Pt_First AS First_Name,pPatients.Pt_Last AS Last_Name,PCR.*,pAssessments.Admit_ID,pAssessments.F26 AS Assessment_Reason_M0100
FROM #Request.prefix_db_agency#.PCR
JOIN (
SELECT MAX(PCR_ID) AS PCR_ID
FROM #Request.prefix_db_agency#.PCR
WHERE Status = 0
GROUP BY Patient_ID, Assmt_ID, Episode
) LatestPCR ON LatestPCR.PCR_ID = PCR.PCR_ID
JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = PCR.Assmt_ID
JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = PCR.Patient_ID
WHERE PCR.Status=0
AND (
PCR.PCR_Status = 'submitted'
OR PCR.PCR_Status IS NULL
OR PCR.PCR_Status = ''
OR PCR.PCR_Status = '0'
OR LOWER(TRIM(PCR.PCR_Status)) IN ('non-affirmed','non affirmed','non-affirm','non affirm')
OR (LOWER(TRIM(PCR.PCR_Status)) IN ('affirmed','affirm') AND IFNULL(TRIM(PCR.UTN), '') = '')
)
AND pAssessments.Status=0
AND pPatients.Status < 2
ORDER BY pPatients.Pt_First,pAssessments.Assmt_ID ASC
SELECT * FROM #Request.prefix_db_lookup#.pEmployee WHERE status = 0