Skip to Content
0
Former Member
Aug 11, 2016 at 10:29 AM

Store Procedure for Blocking Sales invoice creation which has open invoices based on Payment Terms

21 Views

Dear All,

I have an created a store procedure for blocking creation of new Sales invoices if already there are pending sales invoice for which incoming payment needs to be done.

The SP is as follows :

IF @Object_type in ('13') AND @transaction_type IN ('A','U')

BEGIN

if (select distinct isnull(t0.Extradays,0) from octg t0 inner join ocrd t1 on t1.GroupNum=t0.GroupNum where t0.PymntGroup=(select t0.PymntGroup from octg t0 inner join ocrd t1 on t1.GroupNum=t0.GroupNum

where t1.U_Restrict='Y' and t1.cardcode=(select CardCode from OINV where DocEntry=@list_of_cols_val_tab_del)))<>0

BEGIN

IF (select top 1 case when datediff(DAY,DocDate,GETDATE())>(select distinct isnull(t0.Extradays,0) from octg t0 inner join ocrd t1 on t1.GroupNum=t0.GroupNum

where t0.PymntGroup=(select t0.PymntGroup from octg t0 inner join ocrd t1 on t1.GroupNum=t0.GroupNum

where t1.U_Restrict='Y' and t1.cardcode=(select CardCode from OINV where DocEntry=@list_of_cols_val_tab_del)))

then 'T' else 'F' end from OINV where DocStatus='O'

and CardCode=(select CardCode from OINV where DocEntry=@list_of_cols_val_tab_del) order by DocDate)='T'

BEGIN

Set @error ='1001002'

set @error_message = 'This Customer Have Pending Invoice'

END

end

It doesn't work for payment group which has Extra days as Zero,What change to be done kindly let me know.

Thanks&Regards

Darshan Desai