Solved

Daily task does not run in SQL

Posted on 2009-05-13
4
403 Views
Last Modified: 2012-05-06
Hello frineds i had talked about this before. i use SQL Server 2005
i have a job ng_stage_data that runs daily but gives error.
the error is :---------
The job failed. The job was invoked by schedule 11(Stage data sched). The last step to run was step 1 (Stage data).
More in detail:-----  Executed as user : IBM/SQLAgent. The procedure or function ng_budget_stage_data expects parameter @pi_process_date, which was not supplied. [SQLSTATE 4200]. (Error 201). The job failed.


the detail store procedure is below:-----------
USE [P84]

GO

/****** Object:  StoredProcedure [dbo].[ng_budget_stage_data]    Script Date: 05/13/2009 10:14:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[ng_budget_stage_data]

	@pi_practice_id	char(4),

	@pi_process_date char(8),

    @pi_session_id uniqueidentifier,

    @pi_user_id int

AS

--{5.5.24.1}

----------------------

-- $Change: 40417 $

-- $DateTime: 2007/10/29 10:52:04 $

-- $Author: jowens $

----------------------
 

set nocount on

set transaction isolation level READ UNCOMMITTED

/*****************************************************************************************************

5.5.8		    11/11/2006  JBO             New stored proc.  This proc stages the budget data.  The letters

                                            print based on the results of this proc.
 

5.5.16          01/17/2007  JBO             Added new temp table & insert statements for budget statements.

5.5.18          02/06/2007  JBO             Added guar_id and guar_type to budget_process_data

5.5.19          02/13/2007  JBO             Added logic to determine that a perpetual budget plan

                                            should be re-established if the payment amount falls below

                                            a percentage of the total patient balance.

5.5.20          02/18/2007  JBO             Re-worked some SQL statements after unit testing 

5.5.21          02/26/2007  JBO             Added SQL for Terminated budget plans & corrections from unit testing

5.5.23.5	10/11/2007  CJS		    Fixed joins to patient_encounter and added (nolock).  This proc was causing deadlocks.	

5.5.24.1   	    10/29/2007                  Added payment_amt to budget_process_data logic

******************************************************************************************************/
 

BEGIN

	--delete any data that is too old

	DELETE FROM budget_process_data

	FROM practice_budget

	WHERE DATEDIFF(d, budget_process_data.create_timestamp, GETDATE()) > budget_archive_days;
 

	DELETE FROM budget_process_data_enc

	FROM practice_budget

	WHERE DATEDIFF(d, budget_process_data_enc.create_timestamp, GETDATE()) > budget_archive_days;
 

	--make sure that the we have no previously existing data for this practice/process date

	DELETE FROM budget_process_data

	WHERE process_date = @pi_process_date AND

		  practice_id = @pi_practice_id AND

		  letter_printed_ind = 'N';
 

	DELETE FROM budget_process_data_enc

	WHERE process_date = @pi_process_date AND

		  practice_id = @pi_practice_id AND

		  NOT EXISTS (SELECT 1 FROM budget_process_data bpd 

                      WHERE letter_printed_ind = 'N' AND 

                            bpd.process_date = budget_process_data_enc.process_date AND

                            bpd.practice_id = budget_process_data_enc.practice_id);
 

	--do initial insert into the process table for perpetual budgets

	INSERT INTO budget_process_data 

	(practice_id, session_id, acct_id, guar_id, guar_type, last_budget_letter_date, pat_bal, current_due_date, budget_cycle, process_date, created_by, modified_by, budget_id, payment_amt) 

	SELECT 

		ab.practice_id, @pi_session_id, ab.acct_id, a.guar_id, a.guar_type, ab.budget_last_letter_date, 

		ISNULL(SUM(c.pat_amt),0),ab.budget_due_date, ab.budget_cycle, @pi_process_date, @pi_user_id, @pi_user_id, ab.budget_id, ab.budget_payment_amt

	FROM 

		accounts_budget ab, practice_budget pb, patient_encounter pe (nolock), accounts a, charges c (nolock)

	WHERE 

		ab.practice_id = @pi_practice_id AND 

		ab.active_ind =  'Y' AND 

		ab.practice_id = pb.practice_id AND 

		DATEDIFF(dd, DATEADD(dd,pb.print_lead_days,GETDATE()),ab.budget_due_date) <= 0 AND 

		ab.budget_defunct_date IS NULL AND

		ab.acct_id = a.acct_id AND

		ab.practice_id = a.practice_id AND

		ab.budget_type = 'P' AND

		a.practice_id = pe.practice_id AND

		a.guar_id = pe.guar_id AND

		a.guar_type = pe.guar_type AND

		pe.enc_status IN ('B','R','U','A') AND

		pe.billable_ind = 'Y' AND

		pe.enc_id = c.source_id AND

		pe.practice_id = c.practice_id AND

		c.source_type = 'V'

	GROUP BY 

		ab.practice_id , ab.acct_id, a.guar_id, a.guar_type, ab.budget_last_letter_date, ab.budget_due_date, ab.budget_cycle, ab.budget_id, ab.budget_payment_amt

	HAVING 

		SUM(c.pat_amt) > 0;
 

	--insert encounter ids for perpetual budgets (used for budget statements option)

	INSERT INTO budget_process_data_enc 

	(practice_id, session_id, acct_id, enc_id, process_date, created_by, modified_by) 

	SELECT 

		ab.practice_id, @pi_session_id, ab.acct_id, pe.enc_id, @pi_process_date, @pi_user_id, @pi_user_id

	FROM 

		accounts_budget ab, practice_budget pb, patient_encounter pe (nolock), accounts a, charges c (nolock)

	WHERE 

		ab.practice_id = @pi_practice_id AND 

		ab.active_ind =  'Y' AND 

		ab.practice_id = pb.practice_id AND 

		DATEDIFF(dd, DATEADD(dd,pb.print_lead_days,GETDATE()),ab.budget_due_date) <= 0 AND 

		ab.budget_defunct_date IS NULL AND

		ab.acct_id = a.acct_id AND

		ab.practice_id = a.practice_id AND

		ab.budget_type = 'P' AND

		a.practice_id = pe.practice_id AND

		a.guar_id = pe.guar_id AND

		a.guar_type = pe.guar_type AND

		pe.enc_status IN ('B','R','U','A') AND

		pe.billable_ind = 'Y' AND

		pe.enc_id = c.source_id AND

		pe.practice_id = c.practice_id AND

		c.source_type = 'V'

	GROUP BY 

		ab.practice_id , ab.acct_id, pe.enc_id

	HAVING 

		SUM(c.pat_amt) > 0;
 

	--do initial insert into the process table for non-perpetual budgets

	INSERT INTO budget_process_data 

	(practice_id, session_id, acct_id, guar_id, guar_type, last_budget_letter_date, pat_bal, current_due_date, budget_cycle, process_date, created_by, modified_by, budget_id, payment_amt) 

	SELECT 

		ab.practice_id, @pi_session_id, ab.acct_id, a.guar_id, a.guar_type, ab.budget_last_letter_date, 

		ISNULL(SUM(c.pat_amt),0), ab.budget_due_date, ab.budget_cycle, @pi_process_date, @pi_user_id, @pi_user_id, ab.budget_id, ab.budget_payment_amt

	FROM 

		accounts_budget ab, budget_members bm, practice_budget pb, charges c (nolock), accounts a

	WHERE 

		ab.practice_id = @pi_practice_id AND 

		ab.active_ind = 'Y' AND 

		ab.practice_id = pb.practice_id AND 

		DATEDIFF(dd, DATEADD(dd,pb.print_lead_days,GETDATE()),ab.budget_due_date) <= 0 AND 

		ab.budget_defunct_date IS NULL AND

                ab.acct_id = a.acct_id AND

                ab.practice_id = a.practice_id AND

		ab.acct_id = bm.acct_id AND

		ab.budget_id = bm.budget_id AND

		ab.practice_id = bm.practice_id AND

		ab.budget_type = 'N' AND

		bm.source_id = c.source_id AND

		bm.source_type = 'V' AND

		bm.practice_id = c.practice_id

	GROUP BY 

		ab.practice_id , ab.acct_id, a.guar_id, a.guar_type, ab.budget_last_letter_date, ab.budget_due_date, ab.budget_cycle, ab.budget_id, ab.budget_payment_amt 

	HAVING 

		SUM(c.pat_amt) > 0;
 

	--insert encounter ids for non-perpetual budgets (used for budget statements option)

	INSERT INTO budget_process_data_enc 

	(practice_id, session_id, acct_id, enc_id, process_date, created_by, modified_by) 

	SELECT 

		ab.practice_id, @pi_session_id, ab.acct_id, bm.source_id, @pi_process_date, @pi_user_id, @pi_user_id

	FROM 

		accounts_budget ab, budget_members bm, practice_budget pb, charges c (nolock)

	WHERE 

		ab.practice_id = @pi_practice_id AND 

		ab.active_ind = 'Y' AND 

		ab.practice_id = pb.practice_id AND 

		DATEDIFF(dd, DATEADD(dd,pb.print_lead_days,GETDATE()),ab.budget_due_date) <= 0 AND 

		ab.budget_defunct_date IS NULL AND

		ab.acct_id = bm.acct_id AND

		ab.budget_id = bm.budget_id AND

		ab.practice_id = bm.practice_id AND

		ab.budget_type = 'N' AND

		bm.source_id = c.source_id AND

		bm.source_type = 'V' AND

		bm.practice_id = c.practice_id

	GROUP BY 

		ab.practice_id , ab.acct_id,  bm.source_id

	HAVING 

		SUM(c.pat_amt) > 0;
 

	--update paid amount for all non-perpetual budgets

	UPDATE budget_process_data 

	SET 

		paid = ABS(ISNULL((SELECT SUM(td.paid_amt) 

	 FROM

		accounts_budget ab, 

		budget_members bm, 

		trans_detail td (nolock), transactions t  (nolock)

	 WHERE             

		(budget_process_data.process_date = @pi_process_date AND

		 budget_process_data.practice_id = @pi_practice_id AND

		 budget_process_data.practice_id = bm.practice_id AND 

		 budget_process_data.acct_id = bm.acct_id) AND 

		(ab.acct_id = budget_process_data.acct_id AND

		 ab.practice_id = budget_process_data.practice_id AND

		 ab.active_ind = 'Y' AND

		 ab.budget_type = 'N') AND             

		(bm.practice_id = td.practice_id AND 

		 bm.source_id = td.source_id AND 

		 td.source_type = 'V' AND td.post_ind = 'Y') AND 

		(td.practice_id = t.practice_id AND 

		 td.trans_id = t.trans_id AND 

		 t.type = 'C' AND t.source = 'P' AND 

		 convert(char(8), t.tran_date, 112) >= budget_process_data.last_budget_letter_date) AND

         session_id = @pi_session_id

	 GROUP BY 

		 bm.practice_id, bm.acct_id),0));
 

	--update paid amount for all perpetual budgets

	UPDATE budget_process_data 

	SET 

		paid = ABS(ISNULL((SELECT SUM(td.paid_amt) 

	FROM

		accounts_budget ab, accounts a, patient_encounter pe (nolock),

		trans_detail td (nolock), transactions t (nolock) 

	WHERE             

		(budget_process_data.process_date = @pi_process_date AND

		 budget_process_data.practice_id = @pi_practice_id AND

		 budget_process_data.practice_id = ab.practice_id AND 

		 budget_process_data.acct_id = ab.acct_id) AND 

		(ab.acct_id = budget_process_data.acct_id AND

		 ab.practice_id = budget_process_data.practice_id AND

		 ab.active_ind = 'Y' AND

		 ab.budget_type = 'P') AND             

		(a.acct_id = ab.acct_id AND

		 a.practice_id = ab.practice_id AND

		 a.guar_id = pe.guar_id AND

		 a.guar_type = pe.guar_type AND

		 pe.enc_status in ('B','R','U','A') AND

		 pe.billable_ind = 'Y' AND

		 pe.practice_id = td.practice_id AND 

		 pe.enc_id = td.source_id AND 

		 td.source_type = 'V' AND td.post_ind = 'Y') AND 

		(td.practice_id = t.practice_id AND 

		 td.trans_id = t.trans_id AND 

		 t.type = 'C' AND t.source = 'P' AND 

		 convert(char(8), t.tran_date, 112) >= budget_process_data.last_budget_letter_date) AND

         session_id = @pi_session_id

	GROUP BY 

		 ab.practice_id, ab.acct_id),0));

  

	--update demographic data for persons who are guarantors

	UPDATE budget_process_data

	SET 

		FullName = per.first_name + ' ' + per.last_name, 

		address_line_1 = per.address_line_1, 

		address_line_2 = per.address_line_2, 

		City = per.city, 

		State = per.state, 

		Zip = per.zip, 

		Country = per.country_id, 

		FirstName = per.first_name, 

		NickName = case when  ( ISNULL(per.nickname, '') = '')  then per.first_name else per.nickname end 

	FROM 

		accounts a, 

		person per (nolock)  

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND

		budget_process_data.practice_id = a.practice_id AND 

		budget_process_data.acct_id = a.acct_id AND

		a.guar_type = 'P' AND 

		a.guar_id = per.person_id AND

        session_id = @pi_session_id;
 

	--update demographic data for employers who are guarantors

	UPDATE budget_process_data 

	SET 

		FullName = em.name, 

		address_line_1 = em.address_line_1, 

		address_line_2 = em.address_line_2, 

		City = em.city, 

		State = em.state, 

		Zip = em.zip, 

		Country = em.country_id, 

		FirstName = em.Name, 

		NickName = em.Name 

	FROM 

		accounts a, 

		employer_mstr em 

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND

		budget_process_data.practice_id = a.practice_id AND 

		budget_process_data.acct_id = a.acct_id AND 

		a.guar_type = 'E' AND 

		a.guar_id = em.employer_id AND

        session_id = @pi_session_id;
 

	--queue first letters to print

	UPDATE budget_process_data SET queue_first_letter_ind = 'Y'

	FROM 

		accounts_budget ab

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

		(budget_process_data.last_budget_letter_date IS NULL) AND

        ab.active_ind = 'Y' AND 

        session_id = @pi_session_id;
 

	--queque normal letters to print

	UPDATE budget_process_data SET queue_normal_letter_ind = 'Y'

	FROM 

		accounts_budget ab, practice_budget pb

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND 

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

         pb.practice_id = ab.practice_id AND

        GETDATE() > ab.budget_due_date AND

		ab.budget_delinquent_date IS NULL AND

		DATEDIFF(dd,GETDATE(), DATEADD(d, CASE ab.budget_cycle WHEN '1D' THEN 1 

												  			   WHEN '7D' THEN 7

															   WHEN '10D' THEN 10 

															   WHEN '12D' THEN 12

															   WHEN '15D' THEN 15

															   WHEN '20D' THEN 20 

															   WHEN '25D' THEN 25

															   WHEN '30D' THEN 30

															   WHEN 'M' THEN 30  

															   WHEN 'W' THEN 7 END, ab.budget_due_date)) <= pb.print_lead_days AND 

		(budget_process_data.last_budget_letter_date IS NOT NULL AND 

		 ab.budget_payment_amt < budget_process_data.pat_bal) AND 

		(budget_process_data.paid >= ab.budget_payment_amt OR 

		 budget_process_data.paid >= budget_process_data.pat_bal) AND

        ab.active_ind = 'Y' AND 

         session_id = @pi_session_id AND

         NOT EXISTS (SELECT 1 FROM budget_process_data bpd (nolock) 

                     WHERE ab.acct_id = bpd.acct_id AND

                           ab.practice_id = bpd.practice_id AND

                           ab.budget_last_letter_date = bpd.process_date AND 

                           ab.budget_id = bpd.budget_id AND

                           ab.active_ind = 'Y' AND

                           bpd.queue_normal_letter_ind = 'Y' AND 

                           bpd.letter_printed_ind = 'Y');
 

	--queue delinquent letters to print

	UPDATE budget_process_data SET queue_delinquent_letter_ind = 'Y'

	FROM

		accounts_budget ab, practice_budget pb

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND 

		pb.practice_id = budget_process_data.practice_id AND 

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

		(budget_process_data.last_budget_letter_date IS NOT NULL AND 

         ab.budget_delinquent_date IS NULL AND

		 budget_process_data.paid < ab.budget_payment_amt AND 

		 GETDATE() > DATEADD(dd,pb.grace_period_days,CONVERT(DATETIME, ab.budget_due_date)) AND 

		 budget_process_data.paid < budget_process_data.pat_bal) AND 

        ab.active_ind = 'Y' AND 

         session_id = @pi_session_id; 
 

	--queue underpayment letters to print

	UPDATE budget_process_data SET queue_under_letter_ind = 'Y', payment_amt = payment_amt + (payment_amt - paid)

	FROM 

		accounts_budget ab

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND 

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

		(budget_process_data.last_budget_letter_date IS NOT NULL AND 

		 ab.budget_payment_amt < budget_process_data.pat_bal) AND 

         ab.budget_delinquent_date IS NULL AND

         ab.active_ind = 'Y' AND 

         budget_process_data.queue_delinquent_letter_ind = 'N' AND

		(budget_process_data.paid < ab.budget_payment_amt OR 

		 budget_process_data.paid < budget_process_data.pat_bal) AND

		 budget_process_data.paid > 0 AND

		 GETDATE() > CONVERT(DATETIME, ab.budget_due_date) AND 

         session_id = @pi_session_id AND

         NOT EXISTS (SELECT 1 FROM budget_process_data bpd 

                     WHERE ab.acct_id = bpd.acct_id AND

                           ab.practice_id = bpd.practice_id AND

                           ab.budget_last_letter_date = bpd.process_date AND 

                           ab.active_ind = 'Y' AND

                           bpd.queue_under_letter_ind = 'Y' AND 

                           bpd.letter_printed_ind = 'Y');  
 

	--queue missed payment letters to print

	UPDATE budget_process_data SET queue_missed_letter_ind = 'Y', payment_amt = payment_amt * 2

	FROM 

		accounts_budget ab

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND 

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

		(budget_process_data.last_budget_letter_date IS NOT NULL AND 

         budget_process_data.queue_delinquent_letter_ind = 'N' AND

         ab.budget_delinquent_date IS NULL AND

         ab.active_ind = 'Y' AND 

		 ab.budget_payment_amt < budget_process_data.pat_bal) AND 

		(budget_process_data.paid = 0) AND 

		 GETDATE() > CONVERT(DATETIME, ab.budget_due_date) AND 

         session_id = @pi_session_id AND

         NOT EXISTS (SELECT 1 FROM budget_process_data bpd (nolock) 

                     WHERE ab.acct_id = bpd.acct_id AND

                           ab.practice_id = bpd.practice_id AND

                           ab.budget_last_letter_date = bpd.process_date AND 

                           ab.active_ind = 'Y' AND

                           bpd.queue_missed_letter_ind = 'Y' AND 

                           bpd.letter_printed_ind = 'Y');  
 

	--queue defunct letters to print

	UPDATE budget_process_data SET queue_defunct_letter_ind = 'Y'

	FROM

		accounts_budget ab, practice_budget pb

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND 

		pb.practice_id = budget_process_data.practice_id AND 

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

		(budget_process_data.last_budget_letter_date IS NOT NULL AND 

         ab.budget_delinquent_date IS NOT NULL AND

         ab.budget_defunct_date IS NULL AND 

         ab.active_ind = 'Y' AND 

		 budget_process_data.paid < ab.budget_payment_amt AND 

		 GETDATE() > DATEADD(dd,pb.budget_days_till_defunct,CONVERT(DATETIME, ab.budget_delinquent_date)) AND 

		 budget_process_data.paid < budget_process_data.pat_bal) AND 

         session_id = @pi_session_id; 
 

	--queue final letters to print

	UPDATE budget_process_data SET queue_final_letter_ind = 'Y'

	FROM

		accounts_budget ab

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND 

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

		(budget_process_data.last_budget_letter_date IS NOT NULL AND 

         ab.budget_delinquent_date IS NULL AND

         ab.budget_defunct_date IS NULL AND

         ab.active_ind = 'Y' AND 

		 ab.budget_payment_amt >= budget_process_data.pat_bal) AND 

		(budget_process_data.paid >= ab.budget_payment_amt OR 

		 budget_process_data.paid >= budget_process_data.pat_bal) AND

         session_id = @pi_session_id;
 

	--flag budgets that are eligible to be terminated

	UPDATE budget_process_data SET terminate_ind = 'Y'

	FROM

		accounts_budget ab, practice_budget pb

	WHERE 

		budget_process_data.process_date = @pi_process_date AND

		budget_process_data.practice_id = @pi_practice_id AND 

		pb.practice_id = budget_process_data.practice_id AND 

		(budget_process_data.practice_id = ab.practice_id) AND 

		(budget_process_data.acct_id = ab.acct_id) AND 

		(budget_process_data.last_budget_letter_date IS NOT NULL AND 

         ab.budget_delinquent_date IS NOT NULL AND

         ab.budget_defunct_date IS NOT NULL AND

         ab.active_ind = 'Y' AND 

		 ab.budget_payment_amt >= budget_process_data.pat_bal) AND 

		(budget_process_data.paid >= ab.budget_payment_amt OR 

		 budget_process_data.paid >= budget_process_data.pat_bal) AND

		 GETDATE() > DATEADD(dd,pb.budget_days_till_term,CONVERT(DATETIME, ab.budget_defunct_date)) AND

         session_id = @pi_session_id;
 

	--set the next budget due date for all payment intervals except monthly ('M')

	UPDATE budget_process_data SET next_due_date = CONVERT(VARCHAR,DATEADD(d, CASE budget_cycle WHEN '1D' THEN 1 

																								WHEN '7D' THEN 7

																								WHEN '10D' THEN 10 

																								WHEN '12D' THEN 12

																								WHEN '15D' THEN 15

																								WHEN '20D' THEN 20 

																								WHEN '25D' THEN 25

																								WHEN '30D' THEN 30

																								WHEN 'M' THEN 30

																								WHEN 'W' THEN 7 END,

															  CAST(SUBSTRING(current_due_date,5,2) + '/' + SUBSTRING(current_due_date,7,2) + '/' + SUBSTRING(current_due_date,1,4) AS DATETIME)), 112)

	WHERE budget_cycle IN ('1D','7D','10D','12D','15D','20D','25D','30D','M','W') AND queue_defunct_letter_ind = 'N' AND queue_delinquent_letter_ind = 'N' AND session_id = @pi_session_id;
 

	--do not increment the due date for delinquent or defunct budgetsu

	UPDATE budget_process_data SET next_due_date = current_due_date 

    WHERE (queue_defunct_letter_ind = 'Y' OR queue_delinquent_letter_ind = 'Y') AND session_id = @pi_session_id;
 

	--set the next budget due date for all payment intervals for monthly ('M')

	--UPDATE budget_process_data SET next_due_date = CONVERT(VARCHAR,DATEADD(m, 1,CAST(SUBSTRING(current_due_date,5, 2) + '/' + SUBSTRING(current_due_date, 7,2) + '/' + SUBSTRING(current_due_date,1,4) AS DATETIME)),112) 

	--WHERE budget_cycle = 'M' AND queue_defunct_letter_ind = 'N' AND session_id = @pi_session_id;
 

    --update remaining budgets amount for 'person' accounts

	UPDATE accounts_budget SET budget_remaining_amt = (SELECT SUM(pat_amt)

                                                       FROM patient_encounter pe (nolock), charges vc (nolock), accounts a

													   WHERE pe.practice_id = @pi_practice_id AND 

														     pe.guar_id = a.guar_id AND a.acct_id = accounts_budget.acct_id AND

														     pe.guar_type = 'P' AND enc_status IN ('B', 'U', 'R') AND

														     (pe.practice_id = vc.practice_id AND pe.enc_id = vc.source_id AND vc.source_type = 'V') AND 

  														     accounts_budget.active_ind = 'Y')
 

    --update remaining budgets amount for employer accounts

	UPDATE accounts_budget SET budget_remaining_amt = (SELECT SUM(pat_amt)

                                                       FROM invoices i (nolock), charges vc (nolock) 

                                                       WHERE i.practice_id = @pi_practice_id AND i.acct_id = accounts_budget.acct_id AND 

                                                             i.status IN ('B','U','R') AND (i.practice_id = vc.practice_id AND 

                                                             i.invoice_id = vc.source_id AND vc.source_type = 'I') AND

                                                             accounts_budget.active_ind = 'Y')
 

	--this next step will prompt the user to contact the patient to reestablish their budget plan 

    --because the payment amount no longer satisifies the minimum percentage for perpetual budgets.

	UPDATE accounts_budget SET reestablish_flag_date = @pi_process_date

	FROM practice_budget pb

	WHERE (budget_payment_amt < (budget_remaining_amt * (pb.budget_min_percent_pay / 100))) AND

	      pb.practice_id = accounts_budget.practice_id AND pb.practice_id = @pi_practice_id

	

END; 

set transaction isolation level READ COMMITTED

set nocount off

Open in new window

0
Comment
Question by:aatishpatel
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24375883
What is the statement executed in the job step, the error message seems pretty self-explanatory in that a parameter values was missing for @pi_process_date..
0
 

Author Comment

by:aatishpatel
ID: 24376162
In the job step its just written as----------

exec ng_budget_stage_data '0001'
0
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24377565
That won't do, the stored procedure requires 4 parameter values all of which are mandatory..

A sample exec statement for this procedure would be something like -

exec ng_budget_stage_data
      @pi_practice_id = '0001'
    , @pi_process_date = '13-May-2009'
    , @pi_session_id = '1AA92FAC-E45F-49E0-809A-5D23EA558574'
    , @pi_user_id = 1

The values indicated above for parameters 2 to 4 are just examples, but need to be populated.

It looks like this procedure is designed to be invoked from an application and not configured for automation as such.

0
 

Author Closing Comment

by:aatishpatel
ID: 31580995
thanx
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now