SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Stored Procedure won't recognize Parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bconner
Starting Member

USA
48 Posts

Posted - 10/15/2012 :  17:24:19  Show Profile  Reply with Quote

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







When I run EXEC sp_Charges_Equal_Payments 201210, 201210
I get error:

Msg 8146, Level 16, State 2, Procedure sp_Charges_Equal_Payments, Line 0
Procedure sp_Charges_Equal_Payments has no parameters and arguments were supplied.


Brian

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/15/2012 :  17:30:19  Show Profile  Visit chadmat's Homepage  Reply with Quote
CREATE PROCEDURE [dbo].[sp_Charges_Equal_Payments]

@BEGIN_POST_PERIOD INT,
@END_POST_PERIOD INT

AS...


-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/15/2012 :  22:44:19  Show Profile  Reply with Quote
check this article to understand more on stored procedures and how you can use parameters in them to pass and return values

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bconner
Starting Member

USA
48 Posts

Posted - 10/16/2012 :  07:33:07  Show Profile  Reply with Quote
Thanks Chadmat that did the trick! Thank you as well visakh16 the link was informative.

Brian
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/16/2012 :  09:33:47  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000