Gl Account Control in Delivery

[krishnam_mathur]

Hi Team,

I need to place control on list of GL accounts allowed by Bp group.

Here is the code i wrote. It has some error. Can anyone please help me on this

if(@object_type ='15' and @transaction_type in ('A','U'))

begin

if exists ( select count(1) from odln t0 inner join dln1 t1 on t0.DocEntry=t1.DocEntry left join ocrd t2 on t0.CardCode=t2.CardCode left join ocrg t3 on t3.GroupCode=t2.Groupcode where t0.docentry=@list_of_cols_val_tab_del and

( (t3.Groupcode='102' and t1.AcctCode in ('40000002', '40001002', '40100002', '40400002', '45500002', '46500002', '49000002', '50000002', '50001002', '50100002', '50400002', '52100002') )

or

(t3.GroupCode='100' and t1.AcctCode in ('40000001', '40100001', '40400001', '45500001', '46500001', '46500011', '49000001', '50000001', '50100001', '50400001', '52100001', '58600000') )

or

(t3.groupcode='111' and t1.AcctCode in ('40000006', '40000007', '40000008', '40001006', '40400005', '40400006', '40500007', '45500006', '46500004', '50000006', '50000007', '50000008', '50001006', '50400005', '50400006', '50700000', '50800000', '51500000', '52000000', '52100007') )

or

(t3.groupcode='103' and t1.AcctCode in ('40000003', '40100003', '40400003', '45500003', '46500003', '49000003', '50000003', '50100003', '50400003')) ))

begin

set @error=-1

set @error_message = 'GL account not per BU'

end

end

[azizelmir · Accepted solution]

Hi Krishnam,

There is no error on your TN code! could you check the below:

IF @object_type ='15' and @transaction_type in ('A','U')
BEGIN
IF EXISTS (
select t0.docnum from odln t0 inner join dln1 t1 on t0.DocEntry=t1.DocEntry 
	left join ocrd t2 on t0.CardCode=t2.CardCode 
	left join ocrg t3 on t3.GroupCode=t2.Groupcode 
	where t0.docentry=@list_of_cols_val_tab_del 
	and
( (t3.Groupcode='102' and t1.AcctCode in ('40000002', '40001002', '40100002', '40400002', '45500002', '46500002', '49000002', '50000002', '50001002', '50100002', '50400002', '52100002') )
or
(t3.GroupCode='100' and t1.AcctCode in ('40000001', '40100001', '40400001', '45500001', '46500001', '46500011', '49000001', '50000001', '50100001', '50400001', '52100001', '58600000') )
or
(t3.groupcode='111' and t1.AcctCode in ('40000006', '40000007', '40000008', '40001006', '40400005', '40400006', '40500007', '45500006', '46500004', '50000006', '50000007', '50000008', '50001006', '50400005', '50400006', '50700000', '50800000', '51500000', '52000000', '52100007') )
or
(t3.groupcode='103' and t1.AcctCode in ('40000003', '40100003', '40400003', '45500003', '46500003', '49000003', '50000003', '50100003', '50400003')) 
))
	BEGIN
		set @error=-1
		set @error_message = 'GL account not per BU'
	END
END

Thank you,

Aziz

[Abdul]

Dear

You can try azizelmir solution but i will recommend to use your control dynamically because in future if you have new account code you have to update that account code in your transaction notification also. I suggest make it dynamic like create some UDT or user table where save the relationship then based on user table relation deploy transaction notification on delivery. In this way if in future there is new group or account end user can update the user table and no need to change transaction notification