cancel
Showing results for 
Search instead for 
Did you mean: 

Help with SP to block same day DN to specific BP

former_member459477
Participant
0 Kudos

Hi Experts

I am trying to make SPTN to restrict Open DNs with same date to a particular BP.

So if a DN exists dated 25/11/14 to this customer a second DN dated 25/11/14 cannot be added unless first delivery note is closed.

I am having problem getting succesful outcome - please can anyone assist?

If @object_type = '15' and @transaction_type ='A'

BEGIN

If Exists (Select T0.DocEntry from [dbo].ODLN T0 INNER JOIN ODLN T1 ON T1.DocDate=T0.DocDate WHERE T0.DocStatus = 'O'AND T0.CardCode='CDOO001')

BEGIN

SELECT @error = -1, @error_message = 'No same day del to this customer'

END

End

Regards,

Karen

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try:

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

BEGIN

If exists (SELECT T0.docentry FROM ODLN T0 WHERE T0.[DocStatus] = 'o' and datediff(dd, getdate(), t0.docdate) =0 and t0.cardcode = 'c20000' and T0.docentry !=@list_of_cols_val_tab_del )

select @error =12select @error_message = 'Not authorizaed to post Delivery'

End

End

Thanks & Regards,

Nagarajan

former_member459477
Participant
0 Kudos

Hi Nagarajan,

After testing this SP seem to be working as per my requirement!

Thank you for your assistance with this it is appreciated.

Regards,

Karen

Answers (3)

Answers (3)

former_member459477
Participant
0 Kudos

I am making other attemps to get this working and I wondered if I should bring in DLN1

where row ship date matches ODLN.DocDate ?

But unfortunately below is not blocking - can anyone assist where problem is or suggest better solution to my problem.

--Block additional DNs same date to CDOO001 unless earlier DNs are closed

If @object_type = '15' and @transaction_type ='A'

BEGIN

If Exists (Select T0.DocEntry from [dbo].ODLN T0 INNER JOIN DLN1 T1 ON T0.DocDate=T1.ShipDate WHERE T1.LineStatus ='O' AND T1.BaseCard='CDOO001'AND T0.DocEntry=@list_of_cols_val_tab_del)

BEGIN

SELECT @error = -1, @error_message = 'No same day delivery to this customer'

END

End

Regards,

Karen

Former Member
0 Kudos

Try:

If @object_type = '15' and @transaction_type ='A'

BEGIN

If Exists (Select T0.DocEntry from ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry=T1.DocEntry AND T0.DocDate=T1.ShipDate AND T1.BaseCard='CDOO001' WHERE T0.DocEntry=@list_of_cols_val_tab_del)

Begin

SELECT @error = -1, @error_message = 'No same day delivery to this customer'

End

END

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

If @object_type = '15' and @transaction_type ='A'

BEGIN

If Exists ( SELECT distinct t0.docentry FROM ODLN T0 inner join ODLN T1 on T0.cardcode = T1.cardcode and T0.[DocDate]  = T1.[DocDate] WHERE T1.[DocStatus] = 'o' and  T0.[CardCode] = 'c20000' AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SELECT @error = -1, @error_message = 'No same day delivery to this customer'

END

End

Thanks & Regards,

Nagarajan

former_member459477
Participant
0 Kudos

Hi Nagarajan,

Thanks for response - I have now tested this:

1.Add a DN for specified BP

2.Try to add a second DN - this is correctly blocked by SP

3.Manually Close first DN

4.Try again to add second DN

Unfortunately even after closing first DN, second DN is still being blocked by the SP

Regards,

Karen

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Tried with count function . But still blocking delivery even all delivery closed. Let me check it again.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi,

Try:

If @object_type = '15' and @transaction_type ='A'

BEGIN

If Exists (Select T0.DocEntry from dbo.ODLN T0 INNER JOIN ODLN T1 ON T0.DocEntry=T1.DocEntry and T1.DocDate=T0.DocDate WHERE T0.DocStatus = 'O'AND T0.CardCode='CDOO001' AND T0.DocEntry=@list_of_key_cols_tab_del)

BEGIN

SELECT @error = -1, @error_message = 'No same day delivery to this customer'

END

End

Thanks.

Gordon

former_member459477
Participant
0 Kudos

Hi Gordon,

Thanks for reply

I tested SP but I am getting error message when trying to add DN:

"Conversion failed when cnverting the nvarchar value 'DocEntry' to data type int"

This is happening when trying to add DN for other BPs also

(There is one DN today (added before SP) which is closed)

Regards,

Karen