stored procedure stops calculating functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure stops calculating functions

Author

Topic

ashokv
Starting Member

6 Posts

Posted - 2006-11-12 : 22:27:16

Hi all,

I got a problem that, One of my application, i use stored procedures to calculation values for invoice. I have used nested stored procedures for this purpose. This procedure work fine when the number of items in bill is less than or equal to 12, but if it goes beyond, the calculated values right from start becomes zero. When I run the same procedure from query analyzer it works fine and it takes two seconds to complete. Can any one say me what kind of problem it is?( i think is some sort of buffer problem, because i used cursor for operation).

Thanks,

Ashok V
Chennai, India

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 22:48:04

Post your stored procedure here. We will have a look at it

ashokv
Starting Member

6 Posts

Posted - 2006-11-13 : 01:02:11

Hi all

Here are my sproc

Procedure 1

CREATE procedure sp_Calculation_Invoice
(
@invoice_id bigint
)
AS

SET ARITHABORT ON

DECLARE @item_id bigint
declare @freight numeric(15,4)
declare @insurance numeric(15,4)
DECLARE @rate_Freight decimal(15,4)
DECLARE @rate_Insurance decimal(15,4)
DECLARE @rate_PackChg decimal(15,4)
DECLARE @fobDiscount decimal(15,4)
DECLARE @otherDedcution decimal(15,4)
DECLARE @addedChgs varchar(4)
DECLARE @TotalItemValue decimal(15,5)
DECLARE @FOBVALUE decimal(15,5)
DECLARE @INVVALUE decimal(15,5)
DECLARE @invcur VARCHAR(3)
DECLARE @jobno bigint
DECLARE @UnitinRs decimal (15,2)
DECLARE @Rate decimal (15,4)

DECLARE @ComisnRate NUMERIC(8,5)
DECLARE @DiscntRate NUMERIC(8,5)
DECLARE @OthDedRate NUMERIC(8,5)
print @invoice_id

--RETRIEVE TOTAL ITEM VALUE FOR THAT INVOICE ID
SELECT
@TotalItemValue= SUM(ISNULL(TOTAL_VAL_FC,0))
FROM
S_ITEMS
WHERE
INVOICE_ID=@invoice_id

--Convert to FC if insurance and freight are in INR

select
@freight =
case
when isnull(FRGHT_CUR,'') = 'INR'
then isnull(FRGHT_AMT,0.00)/isnull(EXCG_RATE,0.00)
else isnull(FRGHT_AMT,0.00)
end,
@insurance =
case
when isnull(INSURC_CUR,'') = 'INR'
then isnull(INSURC_AMT,0.00)/isnull(EXCG_RATE,0.00)
else isnull(INSURC_AMT,0.00)
end

from
s_invoice where invoice_id = @invoice_id

--GET INVOICE DETAILS AND INSERT INTO TEMP INVOICE DETAILS

SELECT
@rate_Freight = (@freight/@TotalItemValue) * 100,
@rate_PackChg = (ISNULL(PACK_CHARGE,0)/@TotalItemValue) * 100,
@rate_Insurance = CASE
WHEN ISNULL(INSURC_RATE,0.00)=0.00 THEN (@insurance/@TotalItemValue) * 100
ELSE ISNULL(INSURC_RATE,0.00) END,
@fobDiscount = CASE
WHEN ISNULL(DISCNT_RATE,0.00)=0.00 THEN (ISNULL(DISCNT_AMT,0)/@TotalItemValue) * 100
ELSE ISNULL(DISCNT_RATE,0.00) END,
@otherDedcution = CASE
WHEN ISNULL(OTH_DEDUCT_RATE,0.00)=0.00 THEN (ISNULL(OTH_DEDUCT_AMT,0)/@TotalItemValue) * 100
ELSE ISNULL(OTH_DEDUCT_RATE,0.00) END,
@addedChgs = ISNULL(ADDED_CHARGES,''),
@invcur = ISNULL(INV_CUR,''),
@jobno= JOB_NO
FROM
S_INVOICE
WHERE
INVOICE_ID=@invoice_id

--select * from #TMPCALCULATE*/

CREATE TABLE #TMPITEMAMT
(
ITEM_ID BIGINT,
FOB_VALUE DECIMAL(15,5),
INV_VALUE DECIMAL(15,5)
)

--Open Cursor to select all items for the given invoice id
/* print( '@rate_Freigh:' + convert(varchar,@rate_Freight ))
print('@rate_Insurance:'+ convert(varchar,@rate_Insurance ))
print( '@rate_PackChg:'+convert(varchar,@rate_PackChg ))
print('@fobDiscount :'+ convert(varchar,@fobDiscount ))
print( '@otherDedcution:'+ convert(varchar,@otherDedcution))
print( ' @addedChgs:'+ convert(varchar,@addedChgs))
*/
--insert into #TMPITEMAMT SELECT ITEM_ID,null,null FROM S_ITEMS WHERE INVOICE_ID=@Invoice_id
DECLARE @ItemCursor CURSOR
SET @ItemCursor = CURSOR FAST_FORWARD
FOR
--SELECT ITEM_ID FROM #TMPITEMAMT
SELECT ITEM_ID FROM S_ITEMS WHERE INVOICE_ID=@Invoice_id

OPEN @ItemCursor
FETCH NEXT FROM @ItemCursor
INTO @item_id

WHILE @@FETCH_STATUS = 0

BEGIN

-- insert into DebugLog values('ITEM - BEGIN','@item_id',@item_id)
--loop through the cursor to execute stored proc
exec sp_Calculation_Items @item_id, @rate_Freight , @rate_Insurance , @rate_PackChg , @fobDiscount , @otherDedcution, @addedChgs

-- insert into DebugLog values('ITEM - END','@item_id',@item_id)

-- Clean up Accessory/ThirdParty/CESS table for this ITEMID
--insert into DebugLog values('before cleanup','@item_id',@item_id)
exec sp_CleanUpItem @item_id
-- insert into DebugLog values('ITEM - CLEANED','@item_id',@item_id)

FETCH NEXT FROM @ItemCursor
INTO @item_id

END

insert into DebugLog values('LAST ITEM COMPLETED','@item_id',@item_id)

CLOSE @ItemCursor
DEALLOCATE @ItemCursor

--Retrieve Exchange details for that invoiceid currencycode

SELECT
@UnitinRs = EXCG_RATE,
@Rate = 1 -- ISNULL(RATE,0) --In the Invoice table the Exchg rate is always stored for 1 unit of currency. Hence this is hard coded as "1"
FROM
S_INVOICE
WHERE
INVOICE_ID=@Invoice_id

--Update Item FOB Value in S_ITEMS
/*
SELECT @FOBVALUE=FOB_VALUE,
@INVVALUE=INV_VALUE
FROM #TMPITEMAMT
WHERE ITEM_ID=@item_id
*/
--print('LOOK AT HERE11')

UPDATE S_ITEMS
SET FOB_VAL_FC = tmp.FOB_VALUE,
FOB_VAL_RS = tmp.FOB_VALUE * (@UnitinRs / @Rate)
FROM S_ITEMS i, TempItemAmt tmp
WHERE
i.ITEM_ID = tmp.item_id

-- calculate FOB value and Invoice value from SUM(FOB_VAL) and SUM(INV_VALUE) from temp table

SELECT @FOBVALUE=SUM(ISNULL(FOB_VALUE,0)),
@INVVALUE=SUM(ISNULL(INV_VALUE,0))
FROM TempItemAmt
--WHERE ITEM_ID=@item_id

-- Update the above two values in the S_INVOICE for that invoice id
UPDATE
S_INVOICE
SET FOB_FC = @FOBVALUE,
INV_FC = @INVVALUE,
FOB_RS = @FOBVALUE * (@UnitinRs / @Rate),
INV_RS = @INVVALUE * (@UnitinRs / @Rate)
WHERE
INVOICE_ID=@Invoice_id
-----------------------
/*MODIFICATION BY MANI Date: 14-Oct-2006*/

SELECT @ComisnRate = ISNULL(comisn_rate, 0) FROM S_INVOICE WHERE INVOICE_ID=@Invoice_id and comisn_rate >0
SELECT @DiscntRate = ISNULL(discnt_rate, 0) FROM S_INVOICE WHERE INVOICE_ID=@Invoice_id AND discnt_rate > 0
SELECT @OthDedRate = ISNULL(oth_deduct_rate, 0) FROM S_INVOICE WHERE INVOICE_ID=@Invoice_id AND oth_deduct_rate > 0

IF @ComisnRate > 0
BEGIN
UPDATE
S_INVOICE
SET COMISN_AMT = @TotalItemValue * (@ComisnRate/100)
WHERE
INVOICE_ID=@Invoice_id
END
IF @DiscntRate > 0
BEGIN
UPDATE
S_INVOICE
SET DISCNT_AMT = @TotalItemValue * (@DiscntRate/100)
WHERE
INVOICE_ID=@Invoice_id
END
IF @OthDedRate > 0
BEGIN
UPDATE
S_INVOICE
SET oth_deduct_amt = @TotalItemValue * (@OthDedRate/100)
WHERE
INVOICE_ID=@Invoice_id
END
----------------------

--print('LOOK AT HERE')
--select * from #TMPITEMAMT
drop table #TMPCALCULATE
drop table #TMPITEMAMT

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Procedure 2

CREATE procedure sp_Calculation_Items
(
@item_id bigint,
@rate_Freight decimal(9,4),
@rate_Insurance decimal(9,4),
@rate_PackChg decimal(9,4),
@fobDiscount decimal(9,4),
@otherDedcution decimal(9,4),
@addedChgs varchar(4)
)
AS

SET ARITHABORT ON

DECLARE @FOBVALUE decimal(15,5)
DECLARE @INVVALUE decimal(15,5)
DECLARE @AdjustmentVal decimal(15,5)

DECLARE @TotalValue decimal(15,5)

/*
CREATE TABLE #TMPITEMAMT
(
ITEM_ID BIGINT,
FOB_VALUE DECIMAL(9,2),
INV_VALUE DECIMAL(9,2)
)
*/

------------------------------------------------------------------------------------
/*SELECT
@rate_Freight = isnull(rate_Freight,0.00),
@rate_Insurance = isnull(rate_Insurance,0.00),
@rate_PackChg = isnull(rate_PackChg,0.00),
@fobDiscount = isnull(fobDiscount,0.00),
@otherDedcution = isnull(otherDedcution,0.00),
@addedChgs = isnull(@addedChgs,0.00)
FROM
#TMPCALCULATE */

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

SELECT @TotalValue=TOTAL_VAL_FC
FROM S_ITEMS
WHERE ITEM_ID=@item_id

--@AdjustmentVal is common to all type of added charges and nature of contract
SET @AdjustmentVal = ((@rate_PackChg/100)*@TotalValue) - ((@fobDiscount/100)*@TotalValue) - ((@otherDedcution/100)*@TotalValue)

/*
print ('@item_id')
print( @item_id)

print('1')
print((@rate_PackChg/100)*@TotalValue)

print('2')
print((@fobDiscount/100)*@TotalValue)

print('3')
print((@otherDedcution/100)*@TotalValue)

print('@TotalValue')
print(@TotalValue)

print('@rate_PackChg')
print(@rate_PackChg)

print('@fobDiscount')
print(@fobDiscount)

print('@otherDedcution')
print(@otherDedcution)

print('@AdjustmentVal')
print(@AdjustmentVal)
*/
insert into debuglog values('Inside Items','@@error',@@error)
insert into debuglog values('Inside Items -2 ','@addedChgs',@addedChgs)
IF @addedChgs = 'F' --Freight
BEGIN
--FREIGHT IS ALREADY ADDED INTO THE TOTAL VALUE. SO SUBTRACT IT TO GET THE FOB VALUE
SET @FOBVALUE=@TotalValue + @AdjustmentVal - ((@rate_Freight/100)*@TotalValue)

--FREIGHT IS ALREADY ADDED, HENCE ADD ONLY INSURANCE TO THE INVOICE VALUE
SET @INVVALUE=@TotalValue + @AdjustmentVal + ((@rate_Insurance/100)*@TotalValue)
END
IF @addedChgs = 'I' --Insurance
BEGIN
--INSURANCE IS ALREADY ADDED INTO THE TOTAL VALUE. SO SUBTRACT IT TO GET THE FOB VALUE
SET @FOBVALUE=@TotalValue + @AdjustmentVal - ((@rate_Insurance/100)*@TotalValue)

--INSURANCE IS ALREADY ADDED, HENCE ADD ONLY FREIGHT TO THE INVOICE VALUE
SET @INVVALUE=@TotalValue + @AdjustmentVal + ((@rate_Freight/100)*@TotalValue)
END

IF @addedChgs = 'N' --None
BEGIN
--NONE ADDED INTO THE TOTAL VALUE. SO FOB VALUE EQUALS TOTAL VALUE
SET @FOBVALUE=@TotalValue + @AdjustmentVal

--NONE ADDED INTO THE TOTAL VALUE. HENCE ADD BOTH FREIGHT & INSURANCE TO THE INVOICE VALUE
SET @INVVALUE=@TotalValue + @AdjustmentVal + ((@rate_Freight/100)*@TotalValue) + ((@rate_Insurance/100)*@TotalValue)
END

IF @addedChgs = 'B' --Both
BEGIN
--BOTH ADDED INTO THE TOTAL VALUE. SO SUBTRACT BOTH TO GET THE FOB VALUE
SET @FOBVALUE=@TotalValue + @AdjustmentVal - ((@rate_Freight/100)*@TotalValue) - ((@rate_Insurance/100)*@TotalValue)

--BOTH ADDED INTO THE TOTAL VALUE. HENCE TOTAL VALUE EQUALS INVOICE VALUE
SET @INVVALUE=@TotalValue + @AdjustmentVal
END

/*INSERT INTO #tmpITEMAMT
VALUES(
@item_id,
@FOBVALUE,
@INVVALUE
)*/
insert into debuglog values('Inside Items -2 ','@@error',@@error)
insert into TempItemAmt values(@item_id,@FOBVALUE,@INVVALUE)
--update #tmpITEMAMT set FOBVALUE = @FOBVALUE,INVVALUE=@INVVALUE where item_id = @item_id

--select * from #tmpITEMAMT

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

procedure 3
CREATE PROCEDURE sp_Calculation_SB
(
@job_id bigint
)
AS
SET ARITHABORT ON

declare @invoice_id bigint
DECLARE @SBFOBFC DECIMAL(15,5)
DECLARE @SBFOBRS DECIMAL(15,5)

insert into DebugLog values('SB','JOB_NO',@job_id)

--open cursor to select all invoices for the given job_id
DECLARE @InvoiceCursor CURSOR
SET @InvoiceCursor = CURSOR FAST_FORWARD
FOR
SELECT INVOICE_ID FROM S_INVOICE WHERE JOB_NO=@job_id
print @job_id
OPEN @InvoiceCursor
FETCH NEXT FROM @InvoiceCursor
INTO @invoice_id

WHILE @@FETCH_STATUS = 0

BEGIN
--loop through the cursor to execute store proc

-- insert into DebugLog values('INVOICE','@invoice_id',@invoice_id)
exec sp_Calculation_Invoice @invoice_id
insert into DebugLog values('ERROR1','@invoice_id',@@error)
FETCH NEXT FROM @InvoiceCursor
INTO @invoice_id

END

insert into DebugLog values('ERROR2','@invoice_id',@@error)

--close cursor
CLOSE @InvoiceCursor
DEALLOCATE @InvoiceCursor

insert into DebugLog values('ERROR3','@invoice_id',@@error)

-- calculate FOB value and TOTAL Invoice value from SUM(FOB_VAL) and SUM(INV_VALUE) from S_INVOICE
SELECT @SBFOBFC = SUM(isnull(FOB_FC,0)),
@SBFOBRS = SUM(isnull(FOB_RS,0))
FROM S_INVOICE WHERE JOB_NO = @job_id

-- Update the above two values in the S_SB for that job id

UPDATE S_SB
SET FOB_RS = @SBFOBRS,
FOB_FC = @SBFOBFC
WHERE
JOB_NO = @job_id

--exec stored proc to calculate DBK amount
insert into DebugLog values('ERROR4','@invoice_id',@@error)
exec sp_calculation_DBKMAIN @job_id
insert into DebugLog values('ERROR5','@invoice_id',@@error)
--exec stored proc to calculate DEPB amount
exec sp_calculation_DEPBMAIN @job_id

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

procedure 4

CREATE procedure sp_CleanUpItem
(
@itemid bigint
) as

SET ARITHABORT ON
declare @accessory as char(1)
declare @thirdparty as char(1)
declare @cess as char(1)
declare @DBK char(1)
declare @DFIA char(1)
declare @LICENSE char(1)
declare @DEPB char(1)
declare @EOU char(1)
declare @JOBWORK char(1)
declare @Jobno bigint

insert into debuglog values('Cleanup-starts here',@itemid,@@error)

select @Jobno=job_no from s_items where item_id=@itemid

select @accessory = ACCESSORIES,
@thirdparty = THIRD_PARTY,
@cess = CESS_ITEM
from S_ITEMS
where ITEM_ID=@itemid

/*
print ('@accessory=' + @accessory)
print ('@thirdparty=' + @thirdparty)
print ('@cess=' + @cess)
*/

if @accessory='N'
delete from S_ITEM_ACCESS where ITEM_ID=@itemid

if @thirdparty='N'
delete from S_THIRD_PARTY where ITEM_ID=@itemid

if @cess='N'
delete from S_CESS where ITEM_ID=@itemid

SELECT
@DBK = SCM.DBK,
@DEPB = SCM.DEPB,
@DFIA = SCM.DFIA,
@EOU = SCM.EOU,
@LICENSE = LICENSE,
@JOBWORK = JOBWORK
FROM S_ITEMS IT, D_SCHEME_CODE_MAPPING SCM
WHERE IT.SCHEME_CD = SCM.SCHEME_CODE
AND IT.ITEM_ID = @itemid

If @DBK='N'
DELETE FROM S_DBK WHERE ITEM_ID=@itemid
If @DEPB='N'
DELETE FROM S_DEPB WHERE ITEM_ID=@itemid

PRINT ('DFIA: ' + @DFIA)
If @DFIA='N'
DELETE FROM S_DFRC WHERE ITEM_ID=@itemid
/*
If @EOU='N'
IF NOT EXISTS
(SELECT 1 FROM S_ITEMS WHERE JOB_NO = @Jobno AND
--SELECT EOU_SCH FROM S_ITEMS WHERE JOB_NO=26029

DELETE FROM S_EOU_REP WHERE ITEM_ID=@itemid
*/
If @LICENSE='N'
DELETE FROM S_DEEC WHERE ITEM_ID=@itemid

If @JOBWORK='N'
DELETE FROM S_JOB_WORK WHERE ITEM_ID=@itemid
insert into debuglog values('Cleanup-ends here',@itemid,@@error)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

procedure 5

CREATE procedure sp_calculation_DBK
@item_id varchar(10)
as

SET ARITHABORT ON

declare @maxEffDt smalldatetime
declare @dbk_serno varchar(15)
declare @DBK_RATE_ADV decimal(8,2)
declare @DBK_RATE_SPE_NUMBER decimal(8,2)
declare @DBK_RATE_EXCISE decimal(8,2)
declare @DBK_RATE_CUSTOM decimal(8,2)
declare @fob_val_rs decimal(15,5)

select @dbk_serno = dbk_serno
from s_dbk
where item_id = @item_id

Select @maxEffDt= max(effect_date)
from d_dbk_sched
where dbk_serno = @dbk_serno

select @DBK_RATE_ADV = DBK_RATE_ADV,
@DBK_RATE_SPE_NUMBER = DBK_RATE_SPE_NUMBER,
@DBK_RATE_EXCISE = DBK_RATE_EXCISE,
@DBK_RATE_CUSTOM = DBK_RATE_CUSTOM
from d_dbk_sched sdbk
where sdbk.effect_date = @maxEffDt
and dbk_serno = @dbk_serno

select @fob_val_rs = isnull(fob_val_rs,0.00)
from s_items
where item_id = @item_id

update s_dbk
set
DBK_RT_ADV =isnull( @DBK_RATE_ADV,0.00),
DBK_RT_SP_NO = isnull(@DBK_RATE_SPE_NUMBER,0.00),
DBK_RATE_EX = isnull(@DBK_RATE_EXCISE,0.00),
DBK_RATE_CU = isnull(@DBK_RATE_CUSTOM,0.00),
dbk_val =
case
when dbk_claim_type = 2 then (isnull(@fob_val_rs, 0) * (isnull(@dbk_rate_adv, 0)/100))
when dbk_claim_type = 3 then (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
when dbk_claim_type = 5 OR dbk_claim_type = 6 OR dbk_claim_type = 8 then
case
when (isnull(@fob_val_rs, 0) *(isnull(@dbk_rate_adv, 0)/100)) > (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
then (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
else (isnull(@fob_val_rs, 0) *(isnull(@dbk_rate_adv, 0)/100))
end
else 0
end,

EXCISE_AMT =
case
when dbk_claim_type = 2 then 0
when dbk_claim_type = 3 then (isnull(@dbk_rate_EXCISE, 0) * isnull(t.dbk_qty, 0))
when dbk_claim_type = 5 OR dbk_claim_type = 6 OR dbk_claim_type = 8 then
case
when (isnull(@fob_val_rs, 0) *(isnull(@dbk_rate_adv, 0)/100)) > (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
then (@DBK_RATE_EXCISE * T.DBK_QTY)
else (@FOB_VAL_RS* (@DBK_RATE_EXCISE/100))
end
else 0
end,

CUSTOM_AMT =
case
when dbk_claim_type = 2 then 0
when dbk_claim_type = 3 then (isnull(@dbk_rate_custom, 0) * isnull(t.dbk_qty, 0))
when dbk_claim_type = 5 OR dbk_claim_type = 6 OR dbk_claim_type = 8 then
case
when (isnull(@fob_val_rs, 0) *(isnull(@dbk_rate_adv, 0)/100)) > (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
then (@DBK_RATE_CUSTOM * T.DBK_QTY)
else (@FOB_VAL_RS* (@DBK_RATE_CUSTOM/100))
end
else 0
end
from s_dbk t
where t.item_id=@item_id

declare @dbkamt numeric(15,5)

select

@dbkamt =
case
when dbk_claim_type = 2 then (isnull(@fob_val_rs, 0) * (isnull(@dbk_rate_adv, 0)/100))
when dbk_claim_type = 3 then (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
when dbk_claim_type = 5 OR dbk_claim_type = 6 OR dbk_claim_type = 8 then
case
when (isnull(@fob_val_rs, 0) *(isnull(@dbk_rate_adv, 0)/100)) > (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
then (isnull(@dbk_rate_spe_number, 0) * isnull(t.dbk_qty, 0))
else (isnull(@fob_val_rs, 0) *(isnull(@dbk_rate_adv, 0)/100))
end
else 0
end

from s_dbk t
where
item_id = @item_id

insert into DebugLog values('DBK','dbk_val',@item_id)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_calculation_DBKMAIN
@job_no bigint
as
SET ARITHABORT ON
declare @item_id varchar(15)

create table #tempitemdbk
(
item_id varchar(25)
)

insert into #tempitemdbk
select
item_id
from
s_dbk
where
job_no = @job_no

DECLARE cur_itemdbk CURSOR FOR
select * from #tempitemdbk

open cur_itemdbk

fetch next from cur_itemdbk into
@item_id

while @@fetch_status=0
begin

print (@item_id)
exec sp_calculation_DBK @item_id

fetch next from cur_itemdbk into
@item_id

end

CLOSE cur_itemdbk
DEALLOCATE cur_itemdbk

drop table #tempitemdbk

/*SET DBK VALUES IN INVOICE AND SB TABLES */

SELECT INV_SERNO, SUM(ISNULL(DBK_VAL , 0)) INV_DBK
INTO #tmpINVDbk
FROM S_DBK
WHERE JOB_NO = @job_no
GROUP BY INV_SERNO

UPDATE S_INVOICE
SET DBK_RS = T.INV_DBK
FROM S_INVOICE INV, #tmpINVDbk T
WHERE INV.INV_SERNO = T.INV_SERNO
AND INV.JOB_NO = @job_no

DECLARE @DBK_SB DECIMAL(9,2)
SELECT @DBK_SB = SUM(ISNULL(INV_DBK, 0))
FROM #tmpINVDbk

UPDATE S_SB
SET DBK_RS = @DBK_SB
FROM S_SB SB
WHERE SB.JOB_NO =@job_no

DROP TABLE #tmpINVDbk
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

procedure 6

CREATE procedure sp_calculation_DEPB
@item_id varchar(10)
as

SET ARITHABORT ON
declare @item_f_cur numeric(15,5)
declare @item_qty numeric(15,5)

select @item_f_cur=isnull(it.item_rate,0)*isnull(inv.excg_rate,0), @item_qty=isnull(it.quantity,0) from s_items it join s_invoice inv on it.invoice_id= inv.invoice_id where it.item_id=@item_id
print @item_f_cur

print @item_qty

update s_depb
set DEPB_AMT =
case
when m.depb_claim_type = 2 then (isnull(i.fob_val_rs,0) * (isnull(m.rate,0)/100))

when m.depb_claim_type = 5 then
case
when (@item_f_cur) < (isnull(m.value_rest,0))
then (isnull(i.fob_val_rs,0) * (isnull(m.rate,0)/100))
else (isnull(m.value_rest,0) * isnull(t.quantity,0) * (isnull(m.rate,0)/100))
-- else 0
end
else 0
end

from s_items i, s_depb t, d_depb m
where t.item_id = i.item_id
and t.grp_cd = m.grp_cd
and t.item_cd = m.item_cd
and m.effect_date =
(select max(sdepb.effect_date)
from d_depb sdepb,s_depb t
where sdepb.grp_cd = t.grp_cd
and sdepb.item_cd = t.item_cd and t.item_id = @item_id)
and t.item_id=@item_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_calculation_DEPBMAIN
@job_no bigint
as
SET ARITHABORT ON
declare @item_id varchar(15)

create table #tempitemdepb
(
item_id varchar(25)
)

insert into #tempitemdepb
select
item_id
from
s_depb
where
job_no = @job_no

DECLARE cur_itemdepb CURSOR FOR
select * from #tempitemdepb

open cur_itemdepb

fetch next from cur_itemdepb into
@item_id

while @@fetch_status=0
begin

print (@item_id)
exec sp_calculation_DEPB @item_id

fetch next from cur_itemdepb into
@item_id

end

CLOSE cur_itemdepb
DEALLOCATE cur_itemdepb

drop table #tempitemdepb
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks,

Ashok V
Chennai, India

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-13 : 01:05:37

Duplicate post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74819

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources