SET ANSI_NULLS ON
Statement:11/30/2018 5:44:08 PM 0:00:00.015: Executed Successfully
CCN_Reconciliation_Parameter.sql:12: SET QUOTED_IDENTIFIER ON
Error:11/30/2018 5:44:08 PM 0:00:00.062: Lookup Error - SQL Server Database Error: Invalid column name 'Printed'.
CCN_Reconciliation_Parameter.sql:71: CREATE PROCEDURE [dbo].[CCN_Reconciliation_Parameter] -- TPA4
@PAYER_ID VARCHAR(20)
AS 
BEGIN

		               SELECT                    
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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' 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
							LEFT 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
							JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY AND VF.IS_INFORMATION_ONLY IN ('Y')
							JOIN IPPS.INVOICE_DETAILS ID ON  FDCD.CLAIM_KEY = ID.CLAIM_KEY AND FDCD.CLAIM_DETAIL_KEY = ID.CLAIM_DETAIL_KEY
							JOIN IPPS.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
							Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_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 DS.Printed = 'N' 
						               
                        GROUP BY
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FDCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 
 

UNION --2


		               SELECT                    
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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 = 'PAID' THEN 'NA' ELSE VF.MESSAGE End AS DESCRIPTION
							VF.MESSAGE 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
							LEFT 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
							JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY AND VF.IS_INFORMATION_ONLY IN ('Y')
							AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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 
							--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 ('PENDING_REVIEW')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
							  And DS.Printed = 'N'			
                       
					   GROUP BY
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FDCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 
 

 UNION --3
  
		               SELECT                   
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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,
							VF.MESSAGE 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
							LEFT 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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 
							--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 = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'
										

                    GROUP BY  
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FIMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 

                     
UNION --4

                     SELECT                   
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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' ELSE VF.MESSAGE End AS Description
							--VF.MESSAGE 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
							LEFT 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							JOIN IPPS.INVOICE_DETAILS ID ON  FIMCD.CLAIM_KEY = ID.CLAIM_KEY AND FIMCD.CLAIM_DETAIL_KEY = ID.CLAIM_DETAIL_KEY
							JOIN IPPS.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
							Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_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 DS.Printed = 'N'         
						
						GROUP BY  
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FIMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 
                     

UNION --5
 
                       SELECT                   
						    OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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 = 'PENDING_REVIEW' THEN 'NA' ELSE VF.MESSAGE End AS DESCRIPTION
							VF.MESSAGE 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
							LEFT 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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							 
							--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 = 'PENDING_REVIEW'  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'
						                 
                        GROUP BY
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FPMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 


UNION --6

                      SELECT                   
						    OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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' ELSE VF.MESSAGE End AS Description
							--VF.MESSAGE 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
							LEFT 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							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							 
							--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 DS.Printed = 'N'

                        GROUP BY
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FPMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 

			
UNION --7

                       SELECT                             
						    OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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 = 'PENDING_REVIEW' THEN 'NA' ELSE VF.MESSAGE End AS DESCRIPTION
							VF.MESSAGE 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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							 
							--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 = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'           

                        GROUP BY
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FIMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 


UNION --8

                      SELECT                             
						    OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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' ELSE VF.MESSAGE End AS Description
							--VF.MESSAGE 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							--AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							JOIN IPPS.INVOICE_DETAILS ID ON  FIMCD.CLAIM_KEY = ID.CLAIM_KEY AND FIMCD.CLAIM_DETAIL_KEY = ID.CLAIM_DETAIL_KEY
							JOIN IPPS.INVOICE II ON II.INVOICE_KEY = ID.INVOICE_KEY
							Join [dbo].[decision_hist] DS on D.claim_key = DS.claim_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 DS.Printed = 'N'        

                        GROUP BY
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FIMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE
							 
						
UNION --9

                       SELECT                                            
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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 = 'PENDING_REVIEW' THEN 'NA' ELSE VF.MESSAGE End AS DESCRIPTION							
							VF.MESSAGE 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS') 
							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 
							--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 = 'PENDING_REVIEW' AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'

					   GROUP BY  
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FPMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 

                    
UNION --10						
                      SELECT                                            
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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' 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 AND VF.IS_INFORMATION_ONLY IN ('Y')
							--AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS') 
							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							 
							--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 DS.Printed = 'N'         

					    GROUP BY  
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FPMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 


UNION --11
 
		               SELECT                              
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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,
							VF.MESSAGE 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_'
							LEFT JOIN CCRA.REFERRAL REFERRAL ON DVC.REFERRAL_NUMBER = REFERRAL.REFERRAL_NUMBER
							LEFT JOIN CCRA.PREAUTHORIZATION PREA ON REFERRAL.REFERRAL_KEY =PREA.REFERRAL_KEY
							LEFT JOIN CCRA.PAYMENT_AUTHORITY PAYAUTH ON REFERRAL.PAYMENT_AUTHORITY_CODE = PAYAUTH.CODE
							LEFT JOIN CCRA.PAYMENT_AUTHORITY_OBLIGATION LINK ON PAYAUTH.PAYMENT_AUTHORITY_KEY = LINK.PAYMENT_AUTHORITY_KEY 
							LEFT JOIN CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
							LEFT JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY AND VF.IS_INFORMATION_ONLY IN ('N')
							--AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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						 
							--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 ( 'REJECTED','DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'           

                        GROUP BY
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FDCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 
 
            
UNION --12

		               SELECT                           
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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,
							VF.MESSAGE 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
							LEFT JOIN CCRA.PAYMENT_AUTHORITY PAYAUTH ON REFERRAL.PAYMENT_AUTHORITY_CODE = PAYAUTH.CODE
							LEFT JOIN CCRA.PAYMENT_AUTHORITY_OBLIGATION LINK ON PAYAUTH.PAYMENT_AUTHORITY_KEY = LINK.PAYMENT_AUTHORITY_KEY 
							LEFT JOIN CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
							LEFT JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY AND VF.IS_INFORMATION_ONLY IN ('N')
							--AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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							 
							--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 ( 'REJECTED','DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'           

                        GROUP BY   
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FIMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 
              
			         
UNION --13

		               SELECT                   
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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,
							VF.MESSAGE 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
							LEFT JOIN CCRA.PAYMENT_AUTHORITY PAYAUTH ON REFERRAL.PAYMENT_AUTHORITY_CODE = PAYAUTH.CODE
							LEFT JOIN CCRA.PAYMENT_AUTHORITY_OBLIGATION LINK ON PAYAUTH.PAYMENT_AUTHORITY_KEY = LINK.PAYMENT_AUTHORITY_KEY 
							LEFT JOIN CCRA.OBLIGATION OBLIGATION ON LINK.OBLIGATION_KEY = OBLIGATION.OBLIGATION_KEY
							LEFT JOIN VALIDATION_FAILURE VF ON D.DECISION_KEY = VF.DECISION_KEY AND VF.IS_INFORMATION_ONLY IN ('N')
							--AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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
							Left JOIN Credit_Memot_DETAILS ON FPMCD.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 ( 'REJECTED','DENIED') AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'          

                        GROUP BY                
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FPMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 


UNION --14

                       SELECT   
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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,
							VF.MESSAGE 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 AND VF.IS_INFORMATION_ONLY IN ('N')
							--AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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 							 
							--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 ( 'REJECTED', 'DENIED') AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
			                  And DS.Printed = 'N'          


						GROUP BY         
							OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FIMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 


UNION --15

		               SELECT                        
						    OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID AS Vendor_ID,
							REFERRAL.REFERRAL_NUMBER As Referral_Number,
							DVC.CLAIM_ID AS CCN_Invoice_Number,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_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,
							VF.MESSAGE 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 AND VF.IS_INFORMATION_ONLY IN ('N') 
							--AND VF.VALIDATION_NAME NOT IN ('OHI_EXISTS')
							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							 
							--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 ( 'REJECTED', 'DENIED')  AND CC.PAYER_ID = @PAYER_ID AND CLAIMLINADJ.PAYER_ID = @PAYER_ID
						      And DS.Printed = 'N'
						                 
					    GROUP BY   
                            OBLIGATION.OBLIGATION_NUMBER,
							II.VENDOR_ID ,
							REFERRAL.REFERRAL_NUMBER,
							DVC.CLAIM_ID ,
							CONCAT( CP.PROVIDER_FIRST_NAME,' ',CP.PROVIDER_MIDDLE_NAME,' ',CP.PROVIDER_LAST_NAME ) ,
							CP.NPI,
							CC.PAYER_ID,
							CC.PAYER_TAX_ID,
							PREA.DESCRIPTION,
							DVC.CLAIM_RECEIPT_DATE,
							D.DECISION,
							CLAIMLINADJ.PAID_AMOUNT,
							(CLAIMLINADJ.PAID_AMOUNT /II.INVOICE_AMOUNT)*II.INTEREST_AMOUNT,
							II.INVOICE_DATE,
							II.INVOICE_NUMBER,
							FPMCD.SERVICE_LINE_NUMBER,
							II.PRINCIPAL_AMOUNT,
							II.IPPS_CHECK_NUMBER,
							II.CHECK_DATE,
							II.ADJUSTMENT_AMOUNT,
							VF.CARC_CODE,
							VF.RARC_CODE,
							VF.MESSAGE 
						 
                                                 
                           
END
Script Execution:11/30/2018 5:44:21 PM: Execution finished in 0:00:13.385
