CCN_Reconciliation_Parameter_CHK_Up_Flg.sql:8: /****** Object:  StoredProcedure [dbo].[CCN_Reconciliation_Parameter_CHK_Up_Flg]    Script Date: 11/30/2018 5:43:34 PM ******/
SET ANSI_NULLS ON
Statement:11/30/2018 5:38:13 PM 0:00:00.000: Executed Successfully
CCN_Reconciliation_Parameter_CHK_Up_Flg.sql:12: SET QUOTED_IDENTIFIER ON
Error:11/30/2018 5:38:13 PM 0:00:00.031: Lookup Error - SQL Server Database Error: Invalid column name 'Printed'.
CCN_Reconciliation_Parameter_CHK_Up_Flg.sql:38: CREATE PROCEDURE [dbo].[CCN_Reconciliation_Parameter_CHK_Up_Flg] -- TPA4
@PAYER_ID VARCHAR(20)
AS 
BEGIN

--Dental- PAID & BILL_SENT Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time, 
      -- Decision_Hist.claim_key, Decision_Hist.Printed              
                     
					 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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_key
							--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

                      Where Decision_Hist.Printed = 'N' -- 'Y' To Reset
							--And D.DECISION IN ('PAID', 'BILL_SENT')
							And [Decision_Hist].decision In ('PAID', 'BILL_SENT')
							And CC.PAYER_ID = @PAYER_ID  
							And	CLAIMLINADJ.PAYER_ID = @PAYER_ID


--Dental-Pending Review Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time,
       --Decision_Hist.claim_key, Decision_Hist.Printed 
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_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
  
						WHERE Decision_Hist.Printed = 'N' -- 'Y' To Reset
							  --And D.DECISION = ('PENDING_REVIEW')
							  And [Decision_Hist].decision In ('PENDING_REVIEW')
							  And CC.PAYER_ID = @PAYER_ID
							  And	CLAIMLINADJ.PAYER_ID = @PAYER_ID


--Dental-Rejected, Denied Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time,
--       Decision_Hist.claim_key, Decision_Hist.Printed  
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_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
  
                      WHERE Decision_Hist.Printed = 'N'
							--And D.DECISION IN ( 'REJECTED','DENIED')
							And [Decision_Hist].decision In ('REJECTED','DENIED') 
							And CC.PAYER_ID = @PAYER_ID
							And CLAIMLINADJ.PAYER_ID = @PAYER_ID


-- Institutional - PAID & BILL_SENT Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time,
      --  Decision_Hist.claim_key, Decision_Hist.Printed 
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_key
							--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
 
                      WHERE Decision_Hist.Printed = 'N' -- 'Y' To Reset 
							--And D.DECISION In ('PAID', 'BILL_SENT')
							And [Decision_Hist].decision In ('PAID', 'BILL_SENT')
							And CC.PAYER_ID = @PAYER_ID
							And CLAIMLINADJ.PAYER_ID = @PAYER_ID 


--Institutional - Pending Review Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time,
      -- Decision_Hist.claim_key, Decision_Hist.Printed 
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_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
 
                      WHERE Decision_Hist.Printed = 'N' -- 'Y' To Reset 
							--And D.DECISION = 'PENDING_REVIEW'
							And [dbo].[Decision_Hist].decision = 'PENDING_REVIEW'
							And CC.PAYER_ID = @PAYER_ID
							And CLAIMLINADJ.PAYER_ID = @PAYER_ID


--Institutional-Rejected, Denied Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time, Decision_Hist.last_updated_date_time,
--       Decision_Hist.claim_key, Decision_Hist.Printed
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_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

  
                      WHERE Decision_Hist.Printed = 'N' 
					        --And DVC.CLAIM_ID In ('VA-OHI-TC7d')
							--And D.DECISION IN ( 'REJECTED','DENIED')
							And Decision_Hist.decision IN ('REJECTED','DENIED') 
							And CC.PAYER_ID = @PAYER_ID
							And CLAIMLINADJ.PAYER_ID = @PAYER_ID


--Proff-Paid & BILL_SENT Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time,
      -- Decision_Hist.claim_key, Decision_Hist.Printed 
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_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
  
                      WHERE Decision_Hist.Printed = 'N'
							--And D.DECISION IN ('PAID', 'BILL_SENT')
							And Decision_Hist.Decision In ('PAID', 'BILL_SENT')
							And CC.PAYER_ID = @PAYER_ID
							And CLAIMLINADJ.PAYER_ID = @PAYER_ID 


-- Proff-Pending Review Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time,
      -- Decision_Hist.claim_key, Decision_Hist.Printed 
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_keyy
							--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
  
                      WHERE Decision_Hist.Printed = 'N'
							--And D.DECISION = 'PENDING_REVIEW' 
							And Decision_Hist.Decision = 'PENDING_REVIEW'
							And CC.PAYER_ID = @PAYER_ID
							And CLAIMLINADJ.PAYER_ID = @PAYER_ID


--Proff-Rejected, Denied Section
UPDATE [dbo].[Decision_Hist] SET [dbo].[Decision_Hist].Printed = 'Y'
--Select Decision_Hist.decision, Decision_Hist.decision_key, Decision_Hist.decision_hist_key, Decision_Hist.hist_added_date_time,  
       --Decision_Hist.claim_key, Decision_Hist.Printed
                       
					   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 Decision_Hist ON D.claim_key = decision_hist.claim_key --And D.claim_detail_key = decision_hist.claim_detail_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
 
                      WHERE Decision_Hist.Printed = 'N'
							--And D.DECISION IN ( 'REJECTED','DENIED')
							And Decision_Hist.Decision In ( 'REJECTED','DENIED')
							And CC.PAYER_ID = @PAYER_ID
							And CLAIMLINADJ.PAYER_ID = @PAYER_ID


END
Script Execution:11/30/2018 5:38:23 PM: Execution finished in 0:00:24.415
