USE [ccrsdb]
GO
/****** Object:  StoredProcedure [dbo].[CCN_Reconciliation_Parameter_Refactor1]    Script Date: 1/12/2019 10:43:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CCN_Reconciliation_Parameter_Refactor1] 
@PAYER_ID VARCHAR(20)
AS 
BEGIN


SELECT
  *
  FROM
           (SELECT                    
                                                --vf.validation_name,
                                                OBLIGATION.OBLIGATION_NUMBER,
                                                II.VENDOR_ID AS Vendor_ID,
                                                REFERRAL.REFERRAL_NUMBER As Referral_Number,
                                                DVC.CLAIM_ID AS CCN_Invoice_Number,
                                                CP.PROVIDER_NAME AS Provider,
                                                CP.NPI,
                                                CC.PAYER_ID As Payer_ID,
                                                CC.PAYER_TAX_ID AS Tax_ID,
                                                PREA.DESCRIPTION AS SEOC_ID,
                                                DVC.CLAIM_RECEIPT_DATE AS CCN_Invoice_Date_Received,
                                                D.DECISION AS Status,
                                                CLAIMLINADJ.PAID_AMOUNT As CCN_Invoice_Line_Amount,
                                                (CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT AS Batch_Invoice_Interests_Payment,
                                                II.INVOICE_DATE AS Batch_Invoice_Date_Received,
                                                II.INVOICE_NUMBER  AS Batch_Invoice_Number,
                                                FDCD.SERVICE_LINE_NUMBER AS CCN_Invoice_Line,
                                                II.PRINCIPAL_AMOUNT AS Principal_Amount,
                                                II.IPPS_CHECK_NUMBER  AS Check_Number,
                                                II.CHECK_DATE AS check_Date,
                                                NULL AS CCN_Invoice_Number_Amended,
                                                NULL AS Credit_Memo_Number,
                                                NULL AS Date_Received_Amended,
                                                NULL AS Original_Amount__Submited, 
                                                II.ADJUSTMENT_AMOUNT  AS Amended_Amount,
                                                NULL AS Net_Change_Amount,
                                                VF.CARC_CODE AS CARC,
                                                VF.RARC_CODE AS RARC,
                                                NULL AS CAGC,
                                                CASE 
							                       WHEN D.DECISION In ('PAID', 'BILL_SENT') THEN 'NA'
							                        WHEN D.DECISION ='PENDING_REVIEW' THEN VI.MESSAGE ELSE VF.MESSAGE 
						                        End AS Description
                                                             

                                         FROM F_DENTAL_CLAIM_DETAILS FDCD
                                                JOIN dbo.DECISION D ON FDCD.CLAIM_DETAIL_KEY = D.CLAIM_DETAIL_KEY AND FDCD.CLAIM_KEY = D.CLAIM_KEY 
                                                JOIN dbo.DIM_VA_CLAIM DVC ON FDCD.CLAIM_KEY = DVC.CLAIM_KEY  
                                                JOIN dbo.CLAIM_LINE_ADJUDICATION CLAIMLINADJ ON FDCD.CLAIM_DETAIL_KEY = CLAIMLINADJ.CLAIM_DETAIL_KEY_DT AND CLAIMLINADJ.PAYER_ID LIKE 'TPA_'
                                                JOIN dbo.CLAIM_PROVIDER CP ON DVC.CLAIM_KEY = CP.CLAIM_KEY AND CP.PROVIDER_TYPE ='RENDERING'
                                                JOIN dbo.CLAIM_COB CC ON DVC.CLAIM_KEY = CC.CLAIM_KEY AND CC.PAYER_ID LIKE 'TPA_'
                                                JOIN CCRA.REFERRAL REFERRAL ON DVC.REFERRAL_NUMBER = REFERRAL.REFERRAL_NUMBER
                                                JOIN CCRA.PREAUTHORIZATION PREA ON REFERRAL.REFERRAL_KEY = PREA.REFERRAL_KEY
                                                JOIN CCRA.PAYMENT_AUTHORITY PAYAUTH ON REFERRAL.PAYMENT_AUTHORITY_CODE = PAYAUTH.CODE
                                                JOIN CCRA.PAYMENT_AUTHORITY_OBLIGATION LINK ON PAYAUTH.PAYMENT_AUTHORITY_KEY = LINK.PAYMENT_AUTHORITY_KEY 
                                                JOIN CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
                                                left JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY 
												left JOIN IPPS.INVOICE_DETAILS ID ON  FDCD.CLAIM_KEY = ID.CLAIM_KEY AND FDCD.CLAIM_DETAIL_KEY = ID.CLAIM_DETAIL_KEY
                                                left JOIN IPPS.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
                                                Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_key And D.claim_detail_key = DS.claim_detail_key
												Left Join dbo.validation_info VI on D.decision_key = VI.decision_key
                                                --Left JOIN Credit_Memot_DETAILS ON FIMCD.claim_detail_key = Credit_Memot_DETAILS.Claim_detail_key 
                                                --Left JOIN Credit_Memot ON Credit_Memot_DETAILS.Credit_Memo_key = Credit_Memot.Credit_Memo_key

                                               
                         WHERE D.DECISION IN ('PAID', 'BILL_SENT') AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('Y') And DS.Printed = 'N'  or 
						       D.DECISION = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VI.validation_name  in ('DOLLAR_THRESHOLD_EXCEEDED') And DS.Printed = 'N' or
							   D.DECISION IN ( 'REJECTED', 'DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('N') And DS.Printed = 'N'     
                              

                                        
                  ) T
                                                        
                        GROUP BY
                                                T.OBLIGATION_NUMBER,
                                                T.Vendor_ID,
                                                T.Referral_Number,
                                                T.CCN_Invoice_Number,
                                                T.Provider,
                                                T.NPI,
                                                T.Payer_ID,
                                                T.Tax_ID,
                                                T.SEOC_ID,
                                                T.CCN_Invoice_Date_Received,
                                                T.Status,
                                                T.CCN_Invoice_Line_Amount,
                                                T.Batch_Invoice_Interests_Payment,
                                                T.Batch_Invoice_Date_Received,
                                                T.Batch_Invoice_Number,
                                                T.CCN_Invoice_Line,
                                                T. Principal_Amount,
                                                T.Check_Number,
                                                T.check_Date,
                                                T.CCN_Invoice_Number_Amended,
                                                T.Credit_Memo_Number,
                                                T.Date_Received_Amended,
                                                T.Original_Amount__Submited, 
                                                T.Amended_Amount,
                                                T.Net_Change_Amount,
                                                T.CARC,
                                                T.RARC,
                                                T.CAGC,
                                                T.Description


                                                
                                 


UNION --2

SELECT
  *
  FROM

                        (SELECT                   
                                                --vf.validation_name,
                                                OBLIGATION.OBLIGATION_NUMBER,
                                                II.VENDOR_ID AS Vendor_ID,
                                                REFERRAL.REFERRAL_NUMBER As Referral_Number,
                                                DVC.CLAIM_ID AS CCN_Invoice_Number,
                                                CP.PROVIDER_NAME AS Provider,
                                                CP.NPI,
                                                CC.PAYER_ID As Payer_ID,
                                                CC.PAYER_TAX_ID AS Tax_ID,
                                                PREA.DESCRIPTION AS SEOC_ID,
                                                DVC.CLAIM_RECEIPT_DATE AS CCN_Invoice_Date_Received,
                                                D.DECISION AS Status,
                                                CLAIMLINADJ.PAID_AMOUNT As CCN_Invoice_Line_Amount,
                                                (CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT AS Batch_Invoice_Interests_Payment,
                                                II.INVOICE_DATE AS Batch_Invoice_Date_Received,
                                                II.INVOICE_NUMBER  AS Batch_Invoice_Number,
                                                FIMCD.SERVICE_LINE_NUMBER AS CCN_Invoice_Line,
                                                II.PRINCIPAL_AMOUNT AS Principal_Amount,
                                                II.IPPS_CHECK_NUMBER  AS Check_Number,
                                                II.CHECK_DATE AS check_Date,
                                                NULL AS CCN_Invoice_Number_Amended,
                                                NULL AS Credit_Memo_Number,
                                                NULL AS Date_Received_Amended,
                                                NULL AS Original_Amount__Submited, 
                                                II.ADJUSTMENT_AMOUNT  AS Amended_Amount,
                                                NULL AS Net_Change_Amount,
                                                VF.CARC_CODE AS CARC,
                                                VF.RARC_CODE AS RARC,
                                                NULL AS CAGC,
                                                CASE 
							                       WHEN D.DECISION In ('PAID', 'BILL_SENT') THEN 'NA'
							                       WHEN D.DECISION ='PENDING_REVIEW' THEN VI.MESSAGE ELSE VF.MESSAGE 
						                        End AS Description
                                                 
                                                
                                         
                                         FROM F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS FIMCD
                                                JOIN dbo.DECISION D ON FIMCD.CLAIM_DETAIL_KEY = D.CLAIM_DETAIL_KEY AND FIMCD.CLAIM_KEY = D.CLAIM_KEY 
                                                JOIN dbo.DIM_VA_CLAIM DVC ON FIMCD.CLAIM_KEY = DVC.CLAIM_KEY  
                                                JOIN dbo.CLAIM_LINE_ADJUDICATION CLAIMLINADJ ON FIMCD.CLAIM_DETAIL_KEY = CLAIMLINADJ.CLAIM_DETAIL_KEY_IN AND CLAIMLINADJ.PAYER_ID LIKE 'TPA_'
                                                JOIN dbo.CLAIM_PROVIDER CP ON DVC.CLAIM_KEY = CP.CLAIM_KEY AND CP.PROVIDER_TYPE ='RENDERING'
                                                JOIN dbo.CLAIM_COB CC ON DVC.CLAIM_KEY = CC.CLAIM_KEY AND CC.PAYER_ID LIKE 'TPA_'
                                                JOIN CCRA.REFERRAL REFERRAL ON DVC.REFERRAL_NUMBER = REFERRAL.REFERRAL_NUMBER
                                                JOIN CCRA.PREAUTHORIZATION PREA ON REFERRAL.REFERRAL_KEY = PREA.REFERRAL_KEY
                                                JOIN CCRA.PAYMENT_AUTHORITY PAYAUTH ON REFERRAL.PAYMENT_AUTHORITY_CODE = PAYAUTH.CODE
                                                JOIN CCRA.PAYMENT_AUTHORITY_OBLIGATION LINK ON PAYAUTH.PAYMENT_AUTHORITY_KEY = LINK.PAYMENT_AUTHORITY_KEY 
                                                JOIN CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
                                                left JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY 
                                                left JOIN IPPS.INVOICE_DETAILS ID ON  FIMCD.CLAIM_KEY = ID.CLAIM_KEY AND FIMCD.CLAIM_DETAIL_KEY = ID.CLAIM_DETAIL_KEY
                                                left JOIN IPPS.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
                                                left Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_key And D.claim_detail_key = DS.claim_detail_key 
												Left join dbo.validation_info VI on D.decision_key = VI.decision_key
                                                --Left JOIN Credit_Memot_DETAILS ON FIMCD.claim_detail_key = Credit_Memot_DETAILS.Claim_detail_key 
                                                --Left JOIN Credit_Memot ON Credit_Memot_DETAILS.Credit_Memo_key = Credit_Memot.Credit_Memo_key

                             WHERE D.DECISION IN ('PAID', 'BILL_SENT') AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('Y') And DS.Printed = 'N'  or 
						           D.DECISION = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VI.validation_name  in ('DOLLAR_THRESHOLD_EXCEEDED') And DS.Printed = 'N' or
							       D.DECISION IN ( 'REJECTED', 'DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('N') And DS.Printed = 'N'   
									
    
	) T                                     
                                     GROUP BY
                                                T.OBLIGATION_NUMBER,
                                                T.Vendor_ID,
                                                T.Referral_Number,
                                                T.CCN_Invoice_Number,
                                                T.Provider,
                                                T.NPI,
                                                T.Payer_ID,
                                                T.Tax_ID,
                                                T.SEOC_ID,
                                                T.CCN_Invoice_Date_Received,
                                                T.Status,
                                                T.CCN_Invoice_Line_Amount,
                                                T.Batch_Invoice_Interests_Payment,
                                                T.Batch_Invoice_Date_Received,
                                                T.Batch_Invoice_Number,
                                                T.CCN_Invoice_Line,
                                                T. Principal_Amount,
                                                T.Check_Number,
                                                T.check_Date,
                                                T.CCN_Invoice_Number_Amended,
                                                T.Credit_Memo_Number,
                                                T.Date_Received_Amended,
                                                T.Original_Amount__Submited, 
                                                T.Amended_Amount,
                                                T.Net_Change_Amount,
                                                T.CARC,
                                                T.RARC,
                                                T.CAGC,
                                                T.Description
                                               
                     

UNION --3

SELECT
  *
  FROM

                      (SELECT                   
                                             --vf.validation_name,
                                                OBLIGATION.OBLIGATION_NUMBER,
                                                II.VENDOR_ID AS Vendor_ID,
                                                REFERRAL.REFERRAL_NUMBER As Referral_Number,
                                                DVC.CLAIM_ID AS CCN_Invoice_Number,
                                                CP.PROVIDER_NAME AS Provider,
                                                CP.NPI,
                                                CC.PAYER_ID As Payer_ID,
                                                CC.PAYER_TAX_ID AS Tax_ID,
                                                PREA.DESCRIPTION AS SEOC_ID,
                                                DVC.CLAIM_RECEIPT_DATE AS CCN_Invoice_Date_Received,
                                                D.DECISION AS Status,
                                                CLAIMLINADJ.PAID_AMOUNT As CCN_Invoice_Line_Amount,
                                                (CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT AS Batch_Invoice_Interests_Payment,
                                                II.INVOICE_DATE AS Batch_Invoice_Date_Received,
                                                II.INVOICE_NUMBER  AS Batch_Invoice_Number,
                                                FPMCD.SERVICE_LINE_NUMBER AS CCN_Invoice_Line,
                                                II.PRINCIPAL_AMOUNT AS Principal_Amount,
                                                II.IPPS_CHECK_NUMBER  AS Check_Number,
                                                II.CHECK_DATE AS check_Date,
                                                NULL AS CCN_Invoice_Number_Amended,
                                                NULL AS Credit_Memo_Number,
                                                NULL AS Date_Received_Amended,
                                                NULL AS Original_Amount__Submited, 
                                                II.ADJUSTMENT_AMOUNT  AS Amended_Amount,
                                                NULL AS Net_Change_Amount,
                                                VF.CARC_CODE AS CARC,
                                                VF.RARC_CODE AS RARC,
                                                NULL AS CAGC,
                                                CASE 
							                       WHEN D.DECISION In ('PAID', 'BILL_SENT') THEN 'NA'
							                       WHEN D.DECISION ='PENDING_REVIEW' THEN VI.MESSAGE ELSE VF.MESSAGE 
						                        End AS Description
                                                  
                                         

                                         FROM F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS FPMCD
                                                JOIN dbo.DECISION D ON FPMCD.CLAIM_DETAIL_KEY = D.CLAIM_DETAIL_KEY AND FPMCD.CLAIM_KEY = D.CLAIM_KEY 
                                                JOIN dbo.DIM_VA_CLAIM DVC ON FPMCD.CLAIM_KEY = DVC.CLAIM_KEY  
                                                JOIN dbo.CLAIM_LINE_ADJUDICATION CLAIMLINADJ ON FPMCD.CLAIM_DETAIL_KEY = CLAIMLINADJ.CLAIM_DETAIL_KEY_PR AND CLAIMLINADJ.PAYER_ID LIKE 'TPA_'
                                                JOIN dbo.CLAIM_PROVIDER CP ON DVC.CLAIM_KEY = CP.CLAIM_KEY AND CP.PROVIDER_TYPE ='RENDERING'
                                                JOIN dbo.CLAIM_COB CC ON DVC.CLAIM_KEY = CC.CLAIM_KEY AND CC.PAYER_ID LIKE 'TPA_'
                                                JOIN CCRA.REFERRAL REFERRAL ON DVC.REFERRAL_NUMBER = REFERRAL.REFERRAL_NUMBER
                                                JOIN CCRA.PREAUTHORIZATION PREA ON REFERRAL.REFERRAL_KEY = PREA.REFERRAL_KEY
                                                JOIN CCRA.PAYMENT_AUTHORITY PAYAUTH ON REFERRAL.PAYMENT_AUTHORITY_CODE = PAYAUTH.CODE
                                                JOIN CCRA.PAYMENT_AUTHORITY_OBLIGATION LINK ON PAYAUTH.PAYMENT_AUTHORITY_KEY = LINK.PAYMENT_AUTHORITY_KEY 
                                                JOIN CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
                                                left JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY 
                                                left JOIN IPPS.INVOICE_DETAILS ID ON  FPMCD.CLAIM_KEY = ID.CLAIM_KEY AND FPMCD.CLAIM_DETAIL_KEY = ID.CLAIM_DETAIL_KEY
                                                left JOIN IPPS.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
                                                left Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_key And D.claim_detail_key = DS.claim_detail_key 
												Left join dbo.validation_info VI on D.decision_key = VI.decision_key
                                                --Left JOIN Credit_Memot_DETAILS ON FIMCD.claim_detail_key = Credit_Memot_DETAILS.Claim_detail_key 
                                                --Left JOIN Credit_Memot ON Credit_Memot_DETAILS.Credit_Memo_key = Credit_Memot.Credit_Memo_key

                                WHERE D.DECISION IN ('PAID', 'BILL_SENT') AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('Y') And DS.Printed = 'N'  or 
						              D.DECISION = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VI.validation_name  in ('DOLLAR_THRESHOLD_EXCEEDED') And DS.Printed = 'N' or
							          D.DECISION IN ( 'REJECTED', 'DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('N') And DS.Printed = 'N'     
                                         
                                           
                                  )T
                              

                       GROUP BY
                                                T.OBLIGATION_NUMBER,
                                                T.Vendor_ID,
                                                T.Referral_Number,
                                                T.CCN_Invoice_Number,
                                                T.Provider,
                                                T.NPI,
                                                T.Payer_ID,
                                                T.Tax_ID,
                                                T.SEOC_ID,
                                                T.CCN_Invoice_Date_Received,
                                                T.Status,
                                                T.CCN_Invoice_Line_Amount,
                                                T.Batch_Invoice_Interests_Payment,
                                                T.Batch_Invoice_Date_Received,
                                                T.Batch_Invoice_Number,
                                                T.CCN_Invoice_Line,
                                                T. Principal_Amount,
                                                T.Check_Number,
                                                T.check_Date,
                                                T.CCN_Invoice_Number_Amended,
                                                T.Credit_Memo_Number,
                                                T.Date_Received_Amended,
                                                T.Original_Amount__Submited, 
                                                T.Amended_Amount,
                                                T.Net_Change_Amount,
                                                T.CARC,
                                                T.RARC,
                                                T.CAGC,
                                                T.Description
												
UNION --4                                                
SELECT
*
FROM 

     (SELECT                             
						    OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CP.PROVIDER_NAME AS Provider,
							CP.NPI,
							CC.PAYER_ID As Payer_ID,
							CC.PAYER_TAX_ID AS Tax_ID,
							PREA.DESCRIPTION AS SEOC_ID,
							DVC.CLAIM_RECEIPT_DATE AS CCN_Invoice_Date_Received,
							D.DECISION AS Status,
							CLAIMLINADJ.PAID_AMOUNT As CCN_Invoice_Line_Amount,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT AS Batch_Invoice_Interests_Payment,
							II.INVOICE_DATE AS Batch_Invoice_Date_Received,
							II.INVOICE_NUMBER  AS Batch_Invoice_Number,
							FIMCD.SERVICE_LINE_NUMBER AS CCN_Invoice_Line,
							II.PRINCIPAL_AMOUNT AS Principal_Amount,
							II.IPPS_CHECK_NUMBER  AS Check_Number,
							II.CHECK_DATE AS check_Date,
							NULL AS CCN_Invoice_Number_Amended,
							NULL AS Credit_Memo_Number,
							NULL AS Date_Received_Amended,
							NULL AS Original_Amount__Submited, 
							II.ADJUSTMENT_AMOUNT  AS Amended_Amount,
							NULL AS Net_Change_Amount,
							VF.CARC_CODE AS CARC,
							VF.RARC_CODE AS RARC,
							NULL AS CAGC,
							CASE 
							  WHEN D.DECISION In ('PAID', 'BILL_SENT') THEN 'NA'
							   WHEN D.DECISION ='PENDING_REVIEW' THEN VI.MESSAGE ELSE VF.MESSAGE 
						   End AS Description
							 
                                         

						FROM F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS FIMCD
							JOIN dbo.DECISION D ON FIMCD.CLAIM_DETAIL_KEY = D.CLAIM_DETAIL_KEY AND FIMCD.CLAIM_KEY = D.CLAIM_KEY 
							JOIN dbo.DIM_VA_CLAIM DVC ON FIMCD.CLAIM_KEY = DVC.CLAIM_KEY  
							JOIN dbo.CLAIM_LINE_ADJUDICATION CLAIMLINADJ ON FIMCD.CLAIM_DETAIL_KEY = CLAIMLINADJ.CLAIM_DETAIL_KEY_IN AND CLAIMLINADJ.PAYER_ID LIKE 'TPA_'
							JOIN dbo.CLAIM_PROVIDER CP ON DVC.CLAIM_KEY = CP.CLAIM_KEY AND CP.PROVIDER_TYPE ='RENDERING'
							JOIN dbo.CLAIM_COB CC ON DVC.CLAIM_KEY = CC.CLAIM_KEY AND CC.PAYER_ID LIKE 'TPA_'
							LEFT JOIN CCRA.REFERRAL REFERRAL ON DVC.REFERRAL_NUMBER = REFERRAL.REFERRAL_NUMBER
							LEFT JOIN CCRA.PREAUTHORIZATION PREA ON REFERRAL.REFERRAL_KEY = PREA.REFERRAL_KEY
							JOIN  DIM_PROCEDURE_CODE PROCOD on FIMCD.PROCEDURE_KEY = PROCOD.PROCEDURE_KEY
							JOIN  CCRA.CPT_OBLIGATION LINK ON PROCOD.PROCEDURE_KEY = LINK.PROCEDURE_KEY 
							JOIN  CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
							LEFT JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY 
							LEFT JOIN IPPS.INVOICE_DETAILS ID ON  FIMCD.CLAIM_KEY = ID.CLAIM_KEY AND FIMCD.CLAIM_DETAIL_KEY = ID.CLAIM_DETAIL_KEY
							LEFT JOIN IPPS.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
							Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_key And D.claim_detail_key = DS.claim_detail_key 
							Left join dbo.validation_info VI on D.decision_key = VI.decision_key
							----Left JOIN Credit_Memot_DETAILS ON FIMCD.claim_detail_key = Credit_Memot_DETAILS.Claim_detail_key 
       ----                     Left JOIN Credit_Memot ON Credit_Memot_DETAILS.Credit_Memo_key = Credit_Memot.Credit_Memo_key

					    WHERE D.DECISION IN ('PAID', 'BILL_SENT') AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('Y') And DS.Printed = 'N'  or 
						      D.DECISION = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VI.validation_name  in ('DOLLAR_THRESHOLD_EXCEEDED') And DS.Printed = 'N' or
							  D.DECISION IN ( 'REJECTED', 'DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('N') And DS.Printed = 'N'    
						       
					)T		  

                        GROUP BY
                                                T.OBLIGATION_NUMBER,
                                                T.Vendor_ID,
                                                T.Referral_Number,
                                                T.CCN_Invoice_Number,
                                                T.Provider,
                                                T.NPI,
                                                T.Payer_ID,
                                                T.Tax_ID,
                                                T.SEOC_ID,
                                                T.CCN_Invoice_Date_Received,
                                                T.Status,
                                                T.CCN_Invoice_Line_Amount,
                                                T.Batch_Invoice_Interests_Payment,
                                                T.Batch_Invoice_Date_Received,
                                                T.Batch_Invoice_Number,
                                                T.CCN_Invoice_Line,
                                                T. Principal_Amount,
                                                T.Check_Number,
                                                T.check_Date,
                                                T.CCN_Invoice_Number_Amended,
                                                T.Credit_Memo_Number,
                                                T.Date_Received_Amended,
                                                T.Original_Amount__Submited, 
                                                T.Amended_Amount,
                                                T.Net_Change_Amount,
                                                T.CARC,
                                                T.RARC,
                                                T.CAGC,
                                                T.Description
							  

UNION --5
SELECT
*
FROM
						(SELECT                                            
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CP.PROVIDER_NAME AS Provider,
							CP.NPI,
							CC.PAYER_ID As Payer_ID,
							CC.PAYER_TAX_ID AS Tax_ID,
							PREA.DESCRIPTION AS SEOC_ID,
							DVC.CLAIM_RECEIPT_DATE AS CCN_Invoice_Date_Received,
							D.DECISION AS Status,
							CLAIMLINADJ.PAID_AMOUNT As CCN_Invoice_Line_Amount,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT AS Batch_Invoice_Interests_Payment,
							II.INVOICE_DATE AS Batch_Invoice_Date_Received,
							II.INVOICE_NUMBER  AS Batch_Invoice_Number,
							FPMCD.SERVICE_LINE_NUMBER AS CCN_Invoice_Line,
							II.PRINCIPAL_AMOUNT AS Principal_Amount,
							II.IPPS_CHECK_NUMBER  AS Check_Number,
							II.CHECK_DATE AS check_Date,
							NULL AS CCN_Invoice_Number_Amended,
							NULL AS Credit_Memo_Number,
							NULL AS Date_Received_Amended,
							NULL AS Original_Amount__Submited, 
							II.ADJUSTMENT_AMOUNT  AS Amended_Amount,
							NULL AS Net_Change_Amount,
							VF.CARC_CODE AS CARC,
							VF.RARC_CODE AS RARC,
							NULL AS CAGC,					
							CASE 
							  WHEN D.DECISION In ('PAID', 'BILL_SENT') THEN 'NA' 
							  WHEN D.DECISION ='PENDING_REVIEW' THEN VI.MESSAGE ELSE VF.MESSAGE 
						    End AS Description
							 
							
							
							
                                                                                
						FROM F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS FPMCD
							JOIN dbo.DECISION D ON FPMCD.CLAIM_DETAIL_KEY = D.CLAIM_DETAIL_KEY AND FPMCD.CLAIM_KEY = D.CLAIM_KEY 
							JOIN dbo.DIM_VA_CLAIM DVC ON FPMCD.CLAIM_KEY = DVC.CLAIM_KEY  
							JOIN dbo.CLAIM_LINE_ADJUDICATION CLAIMLINADJ ON FPMCD.CLAIM_DETAIL_KEY = CLAIMLINADJ.CLAIM_DETAIL_KEY_PR and CLAIMLINADJ.PAYER_ID LIKE 'TPA_'
							JOIN dbo.CLAIM_PROVIDER CP ON DVC.CLAIM_KEY = CP.CLAIM_KEY AND CP.PROVIDER_TYPE ='RENDERING'
							JOIN dbo.CLAIM_COB CC ON DVC.CLAIM_KEY = CC.CLAIM_KEY AND CC.PAYER_ID LIKE 'TPA_'
							LEFT JOIN CCRA.REFERRAL REFERRAL ON DVC.REFERRAL_NUMBER = REFERRAL.REFERRAL_NUMBER
							LEFT JOIN CCRA.PREAUTHORIZATION PREA ON REFERRAL.REFERRAL_KEY = PREA.REFERRAL_KEY
							JOIN  DIM_PROCEDURE_CODE PROCOD on FPMCD.PROCEDURE_KEY = PROCOD.PROCEDURE_KEY
							JOIN  CCRA.CPT_OBLIGATION LINK ON PROCOD.PROCEDURE_KEY = LINK.PROCEDURE_KEY 
							JOIN  CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
							LEFT JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY 
							LEFT JOIN ipps.INVOICE_DETAILS ID ON  FPMCD.CLAIM_KEY  = ID.CLAIM_KEY  AND FPMCD.CLAIM_DETAIL_KEY  = ID.CLAIM_DETAIL_KEY 
							LEFT JOIN ipps.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
							Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_key And D.claim_detail_key = DS.claim_detail_key
							Left join dbo.validation_info VI on D.decision_key = VI.decision_key
							--Left JOIN Credit_Memot_DETAILS ON FIMCD.claim_detail_key = Credit_Memot_DETAILS.Claim_detail_key 
                            --Left JOIN Credit_Memot ON Credit_Memot_DETAILS.Credit_Memo_key = Credit_Memot.Credit_Memo_key

						  WHERE D.DECISION IN ('PAID', 'BILL_SENT') AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('Y') And DS.Printed = 'N'  or 
						        D.DECISION = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VI.validation_name  in ('DOLLAR_THRESHOLD_EXCEEDED') And DS.Printed = 'N' or
							    D.DECISION IN ( 'REJECTED', 'DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID AND VF.IS_INFORMATION_ONLY IN ('N') And DS.Printed = 'N'  
						     
                    )T

					   GROUP BY
                                                T.OBLIGATION_NUMBER,
                                                T.Vendor_ID,
                                                T.Referral_Number,
                                                T.CCN_Invoice_Number,
                                                T.Provider,
                                                T.NPI,
                                                T.Payer_ID,
                                                T.Tax_ID,
                                                T.SEOC_ID,
                                                T.CCN_Invoice_Date_Received,
                                                T.Status,
                                                T.CCN_Invoice_Line_Amount,
                                                T.Batch_Invoice_Interests_Payment,
                                                T.Batch_Invoice_Date_Received,
                                                T.Batch_Invoice_Number,
                                                T.CCN_Invoice_Line,
                                                T. Principal_Amount,
                                                T.Check_Number,
                                                T.check_Date,
                                                T.CCN_Invoice_Number_Amended,
                                                T.Credit_Memo_Number,
                                                T.Date_Received_Amended,
                                                T.Original_Amount__Submited, 
                                                T.Amended_Amount,
                                                T.Net_Change_Amount,
                                                T.CARC,
                                                T.RARC,
                                                T.CAGC,
                                                T.Description
End
