Solved

Daily task does not run in SQL

Posted on 2009-05-13
4
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

617 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