September 26, 2011 at 1:49 am
Hi there
I am trying to allocated receipts to transactions. 1 receipt can pay multiple transactions and multiple receipts can pay 1 transaction.
I have a transaction table which includes the trans, with a flag OP or CL. And amount Paid. When amount paid is same as Transamount the is CL (Closed), this also inclused the receipts and any credit adjustments.
A seperate table maintains the References.Trans_Ref(Empty at moment, what I Want to populate)
IE the Receipt number to the transactions that is paid off. Rec000001 Trans0001 Rec000001 Trans0002 etc.
I am trying to avoid using a cursor to do this work. Table has around 900 000 records. This is a once off run, but may need to be run a few times at the the end of ETL runs.
In essence this is payment allocation script or SSIS Package. Getting a bit lost / Brain block here. Can some-one assist.?
Thanks Lee.
September 26, 2011 at 2:49 am
Good morning and welcome to SSC!
It seems that your DDL script seems to have become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks!
September 26, 2011 at 2:52 am
This was removed by the editor as SPAM
October 9, 2011 at 11:10 pm
Further to my previous:
Here is a cursor solution which sort of solves problem, but as with cursors very very slow.
drop Procedure Alloc_Trans
go
Create Procedure Alloc_Trans
as
Begin
SET NOCOUNT ON;
DECLARE @cust_acct_no Varchar(20),@Trans_No Varchar(20),@Amount_Running decimal(12,2),@Total_amount decimal(12,2);
DECLARE @Trans_Ref Varchar(20),@Total_Ref decimal(12,2), @Total_Vat_Ref decimal(12,2) ;
Declare @Tran_Amt_insert decimal(12,2),@Tran_VatAmt_insert decimal(12,2);
Declare @transtatus Varchar(2);
DECLARE Transaction_cursor CURSOR FOR
SELECT cust_acct_no,Total_Amount, Tran_No
from Stage1_Transactions
WHERE Total_amount <0
ORDER BY cust_acct_no,Tran_date;--Credit amount only
OPEN Transaction_cursor;
FETCH NEXT FROM Transaction_cursor
INTO @cust_acct_no, @Total_amount,@Trans_no ;
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Amount_Running = @Total_amount;
While @Amount_Running < 0
Begin
Select top 1 @Trans_Ref = TRAN_NO,@Total_Ref = Tran_amount, @Total_Vat_Ref = VAT_AMT_CASH
from Stage1_Transactions
WHERE (total_amount >0) and TRAN_STATUS = 'OP' and CUST_ACCT_NO = @cust_acct_no
Order by Tran_date;
if (@Amount_Running + @Total_Ref + @Total_Vat_Ref) <= 0.00
begin
set @TranStatus = 'CL';
set @Tran_Amt_insert = @Total_Ref ;
set @Tran_VatAmt_insert = @Total_Vat_Ref;
set @Amount_Running = @Amount_Running + @Total_Ref + @Total_Vat_Ref;
end
Else begin
if @Total_Vat_Ref <> 0 begin
set @Tran_VatAmt_insert = @Amount_Running*( @Total_Ref / @Total_Vat_Ref)*-1;--Pro-Rata Vat
end
else
begin
set @Tran_VatAmt_insert = 0;
end
set @TranStatus = 'OP';
set @Tran_Amt_insert = @Amount_Running*-1 - @Tran_VatAmt_insert;
set @Amount_Running = 0;
end --else
Insert into stage1_transaction_ref (Tran_no,ref_tran_no,ref_cust_acct_no,tran_amount,vat_Amt_cash)
Values (@Trans_no,@Trans_Ref,@cust_acct_no,@Tran_Amt_insert,@Tran_VatAmt_insert);
update Stage1_Transactions
Set VAT_PAID_AMOUNT = isnull(VAT_PAID_AMOUNT,0.00) + @Tran_VatAmt_insert,
PAID_AMOUNT = ISNULL(PAID_AMOUNT,0.00) + @Tran_Amt_insert,
TRAN_STATUS = @TranStatus
Where TRAN_NO = @Trans_Ref;
set @TranStatus = '';
set @Tran_Amt_insert = 0.00;
set @Tran_VatAmt_insert = 0.00
end --While
FETCH NEXT FROM Transaction_cursor
INTO @cust_acct_no, @Total_amount,@Trans_no;
END
CLOSE Transaction_cursor;
DEALLOCATE Transaction_cursor;
End;
A better solution?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply