Accounting procedure

Hi,
I have the following procedure that I am trying to run on a data warehouse -
it is supposed to increase or decrease the running premium balance and the
fire fee balance based on the type of transaction, but it seems to just be
increasing the running balance - do you see where I am going wrong?
Thanks in advance
-- Local Variables
DECLARE
@.PolicyKeyID INT,
@.Policy_Number VARCHAR (10),
@.Trans_Type VARCHAR(40),
@.Billed_Premium DECIMAL(10,2),
@.Billed_Fire_Fee DECIMAL(10,2),
@.Pymt_Recvd DECIMAL(10,2),
@.SC_Recvd DECIMAL(10,2),
@.Transcode VARCHAR (4),
@.Portfolio_Set VARCHAR (4),
@.Type VARCHAR (25),
@.Typecode VARCHAR (4),
@.Previous_Policy_Number VARCHAR (10),
@.Running_Premium DECIMAL(10,2),
@.Running_Fire_Fee DECIMAL(10,2),
@.Error_Code_Tran INT,
@.Error_Code_Proc INT,
-- CONSTANTS declared for Transaction Types
@.NEW_BILL VARCHAR(15),
@.RENEWAL VARCHAR(20),
@.CANCELLATION VARCHAR(35),
@.CHANGE VARCHAR(15),
@.CASH_WITH_APP VARCHAR(25),
@.RETURNED_CHECK VARCHAR(35),
@.PAYMENT VARCHAR(35),
@.PYMT_REVERSAL VARCHAR(25),
@.DISBURSEMENT VARCHAR(25),
@.CANCEL_DISBURSEMENT VARCHAR(35)
-- Assign CONSTANTS
SET @.NEW_BILL = 'New'
SET @.RENEWAL = 'Renewal'
SET @.CANCELLATION = 'Cancellation'
SET @.CHANGE = 'Change'
SET @.CASH_WITH_APP = 'Cash With App'
SET @.RETURNED_CHECK = 'Returned Check'
SET @.PAYMENT = 'Payment'
SET @.PYMT_REVERSAL = 'Payment Reversal'
SET @.DISBURSEMENT = 'Disbursement'
SET @.CANCEL_DISBURSEMENT = 'Cancel Disbursement'
-- Set Error Codes
SET @.Error_Code_Tran = 0
SET @.Error_Code_Proc = 0
SET NOCOUNT ON
DECLARE crsrTransactions CURSOR LOCAL STATIC FOR
SELECT PolicyKeyID, Policy_Number, Portfolio_Set, Billed_Premium,
Billed_Fire_Fee, Pymt_Recvd, SC_Recvd,Trans_Type,Transcode,Type,Typec
ode
FROM Stage_Fire_Fee
ORDER BY Policy_Number, Policy_Date_Time ASC
OPEN crsrTransactions
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
-- Outer Loop for all Transactions
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Initialize Running totals, reinitialize for each Policy #
SET @.Running_Premium = 0
SET @.Running_Fire_Fee = 0
SET @.Previous_Policy_Number = @.Policy_Number
IF @.Trans_Type IN
(@.NEW_BILL,@.RENEWAL,@.CANCELLATION,@.CHANG
E,@.RETURNED_CHECK,@.DISBURSEMENT,@.CAN
CEL_DISBURSEMENT,@.PYMT_REVERSAL)
-- Found basic Billed, so increment our running Premium and Fire Fee totals
BEGIN
SET @.Running_Premium = @.Running_Premium + @.Billed_Premium
SET @.Running_Fire_Fee = @.Running_Fire_Fee + @.Billed_Fire_Fee
END
ELSE IF @.Trans_Type = (@.PAYMENT)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE IF @.Trans_Type = (@.CASH_WITH_APP)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE
BEGIN
RAISERROR ('Unknown Transaction Type: %s', 0, 1, @.Trans_Type)
SET @.Error_Code_Tran = 1
SET @.Error_Code_Proc = 1
END
IF @.Error_Code_Tran = 0 UPDATE Stage_Fire_Fee SET Stage_Premium_Bal =
@.Running_Premium, Stage_Fire_Fee_Bal = @.Running_Fire_Fee WHERE PolicyKeyID =
@.PolicyKeyID
SET @.Previous_Policy_Number = @.Policy_Number
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
SET @.Error_Code_Tran = 0
END
ENDOn Sun, 6 Nov 2005 16:44:36 -0800, Patrice wrote:

>Hi,
>I have the following procedure that I am trying to run on a data warehouse
-
>it is supposed to increase or decrease the running premium balance and the
>fire fee balance based on the type of transaction, but it seems to just be
>increasing the running balance - do you see where I am going wrong?
>Thanks in advance
Hi Patrice,
First some general advice:
1. Generally, don't store data that can be calculated in the database.
Each time the base data changes, you'll have to re-do all calculations.
Or you can choose to recalculate periodically, but then, the stored data
might be incorrect when you query the table.
2. Try to avoid cursors. They should only be used when all else fails,
or when you can prove that there is no reasonable set-based alternative.
On to your code.
It's hard to see what's going wrong, becuase you didn't include all
information needed to troubleshoot. Please post:
- The table structures, as CREATE TABLE statements (including all
constraints and properties, but excluding irrelevant columns),
- Some rows of sample data to illustrate the problem, as INSERT
statements,
- The expected output, and
- The output you are actually seeing.
Check out www.aspfaq.com/5006 for more suggestions on how to provide the
information we need in order to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Accessing stored proc multiple return values

Hi,
I have a problem. I have two stored procs. One I am building currently
(sp_load) and another that is already in the data warehouse and which I
have no control over (sp_log_event).
sp_log_event is for control logging. It accepts a process name
parameter. It outputs 3 return parameters by issuing the following
command:
SELECT
load_id,
last_succ_load_id,
datEventDate
FROM
ctl_event_log_header
WHERE
load_Id = @.intLoadId
I am no expert on this but as I understand it these are technically not
output parameters. If I create an Execute SQL Task in DTS I have the
option of setting these 3 return values to my global variables in my
package - which is easy enough and I am already doing this.
However my problem is that I now need to call this (sp_log_event) from
within the stored proc I am creating (sp_load). Something like EXEC
MY_SP @.processname
If the return was an output parameter i could simply do EXEC MY_SP
@.processname, @.loadid output
Also if it was just one return value I could do
EXEC @.loadid = (MY_SP @.processname)
But it is neither of these scenarios and I can't work out how I can get
access to these 3 returned values from the confines of my procedure.
load id is a primary key so the select will definitely only return one
record. how do i get access to the 3 return variables and assign them
to variables within my stored proc (sp_load)evs
BOL has very good examples how to use storerd procedure that has a few
OUTPUT parameters
BTW , it is really bad practice to use sp_ prefix to name stored
procedures, because in that way SQL Server is going to check for system
stored procedures first
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a problem. I have two stored procs. One I am building currently
> (sp_load) and another that is already in the data warehouse and which I
> have no control over (sp_log_event).
> sp_log_event is for control logging. It accepts a process name
> parameter. It outputs 3 return parameters by issuing the following
> command:
> SELECT
> load_id,
> last_succ_load_id,
> datEventDate
> FROM
> ctl_event_log_header
> WHERE
> load_Id = @.intLoadId
>
> I am no expert on this but as I understand it these are technically not
> output parameters. If I create an Execute SQL Task in DTS I have the
> option of setting these 3 return values to my global variables in my
> package - which is easy enough and I am already doing this.
> However my problem is that I now need to call this (sp_log_event) from
> within the stored proc I am creating (sp_load). Something like EXEC
> MY_SP @.processname
> If the return was an output parameter i could simply do EXEC MY_SP
> @.processname, @.loadid output
> Also if it was just one return value I could do
> EXEC @.loadid = (MY_SP @.processname)
> But it is neither of these scenarios and I can't work out how I can get
> access to these 3 returned values from the confines of my procedure.
> load id is a primary key so the select will definitely only return one
> record. how do i get access to the 3 return variables and assign them
> to variables within my stored proc (sp_load)
>|||Hi Uri,
Thanks for the reply. First of all, what is BOL? :)
Second of all - I am not actually naming my stored procs like that. I
just used that for simplicity. They are actually USP_CTL_xxx and
USP_ETL_xxx
Cheers though.
Uri Dimant wrote:
> evs
> BOL has very good examples how to use storerd procedure that has a few
> OUTPUT parameters
> BTW , it is really bad practice to use sp_ prefix to name stored
> procedures, because in that way SQL Server is going to check for system
> stored procedures first
>
>
> "evs" <evan.winstanley@.gmail.com> wrote in message
> news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...|||evs
BOL -Books On Line (tool suppliedb by MS with SQL Server)

> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
I was referencing to <(sp_log_event). from your previous post
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149052185.818089.227430@.h76g2000cwa.googlegroups.com...
> Hi Uri,
> Thanks for the reply. First of all, what is BOL? :)
> Second of all - I am not actually naming my stored procs like that. I
> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
> Cheers though.
>
> Uri Dimant wrote:
>
>|||On 30 May 2006 21:38:45 -0700, evs wrote:

>Hi,
>I have a problem. I have two stored procs. One I am building currently
>(sp_load) and another that is already in the data warehouse and which I
>have no control over (sp_log_event).
>sp_log_event is for control logging. It accepts a process name
>parameter. It outputs 3 return parameters by issuing the following
>command:
>SELECT
> load_id,
> last_succ_load_id,
> datEventDate
>FROM
> ctl_event_log_header
>WHERE
> load_Id = @.intLoadId
>
>I am no expert on this but as I understand it these are technically not
>output parameters. If I create an Execute SQL Task in DTS I have the
>option of setting these 3 return values to my global variables in my
>package - which is easy enough and I am already doing this.
>However my problem is that I now need to call this (sp_log_event) from
>within the stored proc I am creating (sp_load). Something like EXEC
>MY_SP @.processname
>If the return was an output parameter i could simply do EXEC MY_SP
>@.processname, @.loadid output
>Also if it was just one return value I could do
>EXEC @.loadid = (MY_SP @.processname)
>But it is neither of these scenarios and I can't work out how I can get
>access to these 3 returned values from the confines of my procedure.
>load id is a primary key so the select will definitely only return one
>record. how do i get access to the 3 return variables and assign them
>to variables within my stored proc (sp_load)
Hi evs,
CREATE TABLE #tmp ( load_id -- datatype
, last_succ_load_id -- datatype
, datEventDate -- datatype
);
INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
EXEC MY_SP @.processname;
SELECT load_id, last_succ_load_id, datEventDate
FROM #tmp;
DROP TABLE #tmp;
Hugo Kornelis, SQL Server MVP|||Hugo,
Thank you so much mate! Works perfectly. I was aware of temporary
tables I have just never used them before and didn't think of it as an
option. Thanks again.
Hugo Kornelis wrote:
> On 30 May 2006 21:38:45 -0700, evs wrote:
>
> Hi evs,
> CREATE TABLE #tmp ( load_id -- datatype
> , last_succ_load_id -- datatype
> , datEventDate -- datatype
> );
> INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
> EXEC MY_SP @.processname;
> SELECT load_id, last_succ_load_id, datEventDate
> FROM #tmp;
> DROP TABLE #tmp;
> --
> Hugo Kornelis, SQL Server MVP