CREATE PROCEDURE [dbo].[sp_Charges_Equal_Payments]

AS

DECLARE @BEGIN_POST_PERIOD INT
DECLARE @END_POST_PERIOD INT



SELECT * INTO #Charges

FROM

(SELECT *
 FROM Transactions.dbo.Transactions
 WHERE [PAY_CODE_NUMBER] = 99 
       AND [ORIGINAL_FSC] NOT IN (1,150,155,151)
       AND [CHARGE_AMOUNT] <>0
       AND [POSTING_PERIOD] BETWEEN @BEGIN_POST_PERIOD AND @END_POST_PERIOD) T1



SELECT * INTO #Payments

FROM

(SELECT
      T1.[INVOICE_NUMBER]
     ,T1.[POSTING_PERIOD] AS PAYMENT_POSTING_PERIOD
     ,T1.[PAY_CODE_NUMBER] 
     ,T1.[LINE_ITEM]
     ,T1.[PAYMENT_AMOUNT]
      FROM Transactions.dbo.Transactions T1
      JOIN Dictionaries.dbo.Paycode T2 ON T1.PAY_CODE_NUMBER = T2.Paycode
      JOIN #Charges T3 ON T1.INVOICE_NUMBER = T3.INVOICE_NUMBER
      WHERE T1.[PAYMENT_AMOUNT] <>0 
      AND T2.[Reporting Category 2] <> 'SELF PAY') T2


SELECT [Group_Number]
      ,[Division_Name]
      ,[Billing_Area_Id]
      ,[Billing_Area_Name]
      ,[Location_Name]
      ,[Location_Mnemonic]
      ,[Location_RptCat3]
      ,T1.[INVOICE_NUMBER]
      ,[PATIENT_ID]
      ,[Pt_MRN]
      ,[Pt_Last_Name]
      ,[Pt_First_Name]
      ,[Pt_DOB]
      ,[Pt_Addr1]
      ,[Pt_Addr2]
      ,[Pt_City_State]
      ,[Pt_Zip]
      ,[ORIGINAL_FSC]
      ,[FSC_AT_Transaction]
      ,T1.[PAY_CODE_NUMBER]
      ,T1.[LINE_ITEM]
      ,[Procedure_Code]
      ,[UNITS_SERVICE_ANALY_MOD]
      ,[POSTING_DATE]
      ,[POSTING_PERIOD]
      ,PAYMENT_POSTING_PERIOD
      ,[CHARGE_AMOUNT]
      ,T2.[PAYMENT_AMOUNT]
      ,[ADJUSTMENT_AMOUNT]
      ,[CREDIT_AMOUNT]
      ,[DEBIT_AMOUNT]
      ,[COPAY_AMOUNT]
      ,[APPROVED_AMOUNT]
      ,[ALLOWED_AMOUNT]
      ,[DEDUCTIBLE_AMOUNT]
      ,[Patient_Responsibility]
      ,[REJ_1]
      ,[REJ_2]
      ,[REJ_3]
      ,[REJ_4]
      ,[SERVICE_DATE]
      ,[Inv_DOS]
      ,[Ref_Physician_Name]
      ,[Ref_Location_Id]
      ,[Ref_Name]
      ,[Ref_Number]
      ,[Ref_Address_Line_1]
      ,[Ref_Address_Line_2]
      ,[Ref_City_State]
      ,[Ref_Zip_Code]
      ,[Reading_LOC_Id]
      ,[Reading_Loc_Name]
      ,[Reading_Loc_Address]
      ,[Reading_Loc_City_State]
      ,[Performing_Loc_Id]
      ,[Performing_Loc_Name]
      ,[Performing_Loc_Address]
      ,[Performing_Loc_City_State]
      ,[Provider_id]
      ,[Provider_Name]
      ,[Provider_Number]
      ,[Provider_Category]
      ,[MODIFIER_1]
      ,[MODIFIER_2]
      ,[MODIFIER_3]
      ,[Diagnosis_Pointer]
      ,[DIAGNOSIS_1]
      ,[DIAGNOSIS_2]
      ,[DIAGNOSIS_3]
      ,[AP_CPC_Flag]
      ,[BATCH_NUMBER]
      ,[Initials]
      ,[Accession_Number]
      ,[CERTIFICATE_NUMBER]
      ,[INSURANCE_GROUP]
      ,[PLAN_NUMBER]
      ,[Ins_Comp_Payerid]
      ,[Ins_Comp_Name]
      ,[Ins_Comp_Alt_Name]
      ,[Ins_Comp_Address1]
      ,[Ins_Comp_Address2]
      ,[Ins_Comp_City_State]
      ,[Ins_Comp_Zip_Code]
      ,[Current_FSC]
      ,[Corrected_Invoice_Number]
      ,[FEE_SCHEDULE]
      ,[FEE_SCHEDULE_EFFECTIVE_DT]
      ,[First_Credit_Date]
      ,[BANK_DEPOSIT_DATE]
      ,[COMMENT]
      ,[LINES_PAID]
      ,[PREVIOUS_FSC_ID]
      ,[STATEMENT_RUN_NUMBER]
      ,[CorrespondingLocationOnMedicareForm]
      ,[CorrespondingLocationCodeForMedicaid]
      ,[CorrespondingLocationForHic]
      ,[Original_Fsc_Name]
      ,[Current_Fsc_Name]
      ,[Original_Fsc_RptCat2]
      ,[Current_Fsc_RptCat2]
      ,[Paycode Description]
      ,[Paycode_RptCat2]
      ,[Paycode_RptCat4]
      ,[Transaction_Level]
      ,[FIN_DM_LoadTime]
      ,[PREVIOUS_FSC_NAME]
 
  FROM #Charges T1
  JOIN
      (SELECT
          [INVOICE_NUMBER]
         ,PAYMENT_POSTING_PERIOD
         ,[PAY_CODE_NUMBER] 
         ,[LINE_ITEM]
         ,[PAYMENT_AMOUNT]
          FROM #Payments) T2 ON (T1.[INVOICE_NUMBER] = T2.[INVOICE_NUMBER] AND T1.[FSC_AT_Transaction] = T2.[PAY_CODE_NUMBER] AND T1.[LINE_ITEM] = T2.[LINE_ITEM])
                   
  WHERE [CHARGE_AMOUNT] = T2.[PAYMENT_AMOUNT]

GROUP BY
[Group_Number]
      ,[Division_Name]
      ,[Billing_Area_Id]
      ,[Billing_Area_Name]
      ,[Location_Name]
      ,[Location_Mnemonic]
      ,[Location_RptCat3]
      ,T1.[INVOICE_NUMBER]
      ,[PATIENT_ID]
      ,[Pt_MRN]
      ,[Pt_Last_Name]
      ,[Pt_First_Name]
      ,[Pt_DOB]
      ,[Pt_Addr1]
      ,[Pt_Addr2]
      ,[Pt_City_State]
      ,[Pt_Zip]
      ,[ORIGINAL_FSC]
      ,[FSC_AT_Transaction]
      ,T1.[PAY_CODE_NUMBER]
      ,T1.[LINE_ITEM]
      ,[Procedure_Code]
      ,[UNITS_SERVICE_ANALY_MOD]
      ,[POSTING_DATE]
      ,[POSTING_PERIOD]
      ,PAYMENT_POSTING_PERIOD
      ,[CHARGE_AMOUNT]
      ,T2.[PAYMENT_AMOUNT]
      ,[ADJUSTMENT_AMOUNT]
      ,[CREDIT_AMOUNT]
      ,[DEBIT_AMOUNT]
      ,[COPAY_AMOUNT]
      ,[APPROVED_AMOUNT]
      ,[ALLOWED_AMOUNT]
      ,[DEDUCTIBLE_AMOUNT]
      ,[Patient_Responsibility]
      ,[REJ_1]
      ,[REJ_2]
      ,[REJ_3]
      ,[REJ_4]
      ,[SERVICE_DATE]
      ,[Inv_DOS]
      ,[Ref_Physician_Name]
      ,[Ref_Location_Id]
      ,[Ref_Name]
      ,[Ref_Number]
      ,[Ref_Address_Line_1]
      ,[Ref_Address_Line_2]
      ,[Ref_City_State]
      ,[Ref_Zip_Code]
      ,[Reading_LOC_Id]
      ,[Reading_Loc_Name]
      ,[Reading_Loc_Address]
      ,[Reading_Loc_City_State]
      ,[Performing_Loc_Id]
      ,[Performing_Loc_Name]
      ,[Performing_Loc_Address]
      ,[Performing_Loc_City_State]
      ,[Provider_id]
      ,[Provider_Name]
      ,[Provider_Number]
      ,[Provider_Category]
      ,[MODIFIER_1]
      ,[MODIFIER_2]
      ,[MODIFIER_3]
      ,[Diagnosis_Pointer]
      ,[DIAGNOSIS_1]
      ,[DIAGNOSIS_2]
      ,[DIAGNOSIS_3]
      ,[AP_CPC_Flag]
      ,[BATCH_NUMBER]
      ,[Initials]
      ,[Accession_Number]
      ,[CERTIFICATE_NUMBER]
      ,[INSURANCE_GROUP]
      ,[PLAN_NUMBER]
      ,[Ins_Comp_Payerid]
      ,[Ins_Comp_Name]
      ,[Ins_Comp_Alt_Name]
      ,[Ins_Comp_Address1]
      ,[Ins_Comp_Address2]
      ,[Ins_Comp_City_State]
      ,[Ins_Comp_Zip_Code]
      ,[Current_FSC]
      ,[Corrected_Invoice_Number]
      ,[FEE_SCHEDULE]
      ,[FEE_SCHEDULE_EFFECTIVE_DT]
      ,[First_Credit_Date]
      ,[BANK_DEPOSIT_DATE]
      ,[COMMENT]
      ,[LINES_PAID]
      ,[PREVIOUS_FSC_ID]
      ,[STATEMENT_RUN_NUMBER]
      ,[CorrespondingLocationOnMedicareForm]
      ,[CorrespondingLocationCodeForMedicaid]
      ,[CorrespondingLocationForHic]
      ,[Original_Fsc_Name]
      ,[Current_Fsc_Name]
      ,[Original_Fsc_RptCat2]
      ,[Current_Fsc_RptCat2]
      ,[Paycode Description]
      ,[Paycode_RptCat2]
      ,[Paycode_RptCat4]
      ,[Transaction_Level]
      ,[FIN_DM_LoadTime]
      ,[PREVIOUS_FSC_NAME]
      
      

DROP TABLE #CHARGES
DROP TABLE #PAYMENTS
Stored Procedure won't recognize Parameters