cancel
Showing results for 
Search instead for 
Did you mean: 

Approval SP

Former Member
0 Kudos

Hi ,

Iam using this SP to  set approval limit for a particular item group .Currently it will block invoicing if the credit days is 10 (OINV.DocTotal-OINV.PaidToDate>0 and DATEDIFF(Day,OINV.DocDate,GETDATE())>10 ).I need to create an exception in this SP to 60 days  only for the customers whose county is "TEST" &"TEST2" .

USE [NABIL_DB]

GO

/****** Object:  StoredProcedure [dbo].[SP_Oil_CreditDays_10]    Script Date: 11/08/2014 14:58:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SP_Oil_CreditDays_10]

  @list_of_cols_val_tab_del NVARCHAR(255), @error_message NVARCHAR(255) OUTPUT

AS

BEGIN

  declare @RowCount int

  select

  @RowCount=COUNT(RDR1.ItemCode)

  from ORDR

  inner join RDR1 on ORDR.DocEntry=RDR1.DocEntry

  inner join OITM on RDR1.ItemCode=OITM.ItemCode and OITM.U_SeriesGrp='O' and isnull(ORDR.U_Approved,'N')='N'

  where

  ORDR.DocEntry=@list_of_cols_val_tab_del

  if @RowCount>0

  begin

  select @RowCount=0

  select

  @RowCount=COUNT(OINV.DocEntry)

  from

  OINV

  inner join INV1 on OINV.DocEntry=INV1.DocEntry

  inner join OITM on INV1.ItemCode=OITM.ItemCode and OITM.U_SeriesGrp='O'

  where

  OINV.DocTotal-OINV.PaidToDate>0 and DATEDIFF(Day,OINV.DocDate,GETDATE())>10

  and OINV.CardCode=(select ORDR.CardCode from ORDR where ORDR.DocEntry=@list_of_cols_val_tab_del)

  if @RowCount>0

  begin

  select @error_message= 'This doccument required approval. Please select Approval to "Yes" to continue'

  return -1

  end

  else

  begin

  return 0

  end

  end

  return 0

End

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

What you mean by County?

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Nagarajan ,

only for the customers    whose crd1.county = Test & test1

Former Member
0 Kudos

Hi,

try this but modify it base on your requirements.

Declare @County nvarchar(max)

set @County = (your Select statement here to get the county value)

IF @County = 'Test' or @County = 'Test1"

     BEGIN

          --add your new sp here for the 60 days validation

     END

ELSE

     BEGIN

          --your 10 days validation here

     END

Jim

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Try add one more condition to see:

  where

  OINV.DocTotal-OINV.PaidToDate>0 and DATEDIFF(Day,OINV.DocDate,GETDATE())>10

  and OINV.CardCode=(select ORDR.CardCode from ORDR where ORDR.DocEntry=@list_of_cols_val_tab_del)

  and (OINV.CardCode NOT IN (SELECT CardCode FROM OCRD WHERE County in ('Test', 'Test2') AND DATEDIFF(Day,OINV.DocDate,GETDATE())< 60)

Thanks,

Gordon