on 10-30-2015 5:56 AM
Hello Everyone,
I am trying to generate one SP to restrict purchase order having null value in row level field.
I have 1 scenario that in Purchase order we have created 1 UDF say U_SO(Sales Order No.). This UDF is auto filled through FMS for particular Item having open Sales Order. Now client requirement is to restrict purchase order if U_SO field is empty or null.
I have created 1 SP for this but its not working. can anybody suggest where I am wrong.
If @object_Type = '22' and @transaction_Type = 'A'
Begin
If Exists (Select OPOR.DocEntry from OPOR inner join POR1 on OPOR.DocEntry = POR1.DocEntry
where (POR1.U_SO Is Null or POR1.U_SO = '0') and OPOR.DocEntry = @List_of_cols_val_tab_del)
Begin
Select @error = -101,
@error_message = N'Please enter Sales Order No.'
End
End
Thanks,
Harshal Makwana
Try This
If @object_Type = '22' and @transaction_Type IN ('A','U')
Begin
If Exists (Select T0.DocEntry from OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
where Isnull(T1.U_SO,'')='' and T0.DocEntry = @List_of_cols_val_tab_del)
Begin
Select @error = -101,
@error_message = N'Please enter Sales Order No.'
End
End
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Harshal,
Try this
If @object_type='22' and @transaction_type in ('A','U')
BEGIN
If Exists (Select t0.docentry from por1 t0
Where T0.DocEntry = @list_of_cols_val_tab_del and (isnull(T0.U_SO,'')='' or T0.U_SO='0' ) )
BEGIN
Select @error = 22,
@error_message = 'Please mention Sales Order Number'
End
End
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Harshal ,
Pls find the Sp below...It will work
If @object_type = '22' and @transaction_type in ( 'A','U')
BEGIN
If exists
(Select P.DocEntry , P.DocNum ,P.U_SO , P.NumAtCard , P.Comments From OPOR P Where P.DocEntry=@list_of_cols_val_tab_del and P.U_SO is null )
Select @error = 1,
@error_message = 'Please mention Sales Order Number' end
Regards,
Ramasamy
Hi Harshal,
Its working on my Side.
Can i have a look on TV.
Thanks'
-
--
Atul Chakraborty
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Please try these.....
If @object_Type = '22' and @transaction_Type = 'A'
Begin
If Exists (Select OPOR.DocEntry from OPOR inner join POR1 on OPOR.DocEntry = POR1.DocEntry
where (POR1.U_SO Is Null or POR1.U_SO = '') and OPOR.DocEntry = @List_of_cols_val_tab_del)
Begin
Select @error = 10,
@error_message = N'Please enter Sales Order No.'
End
End
Thanks & Regards ,
Kamlesh Naware
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Harshal,
Check below Transaction Notification.
If @object_Type = '22' and @transaction_Type IN ('A','U')
Begin
If Exists (Select T0.DocEntry from OPOR T0 inner join POR1 T1 on T0.DocEntry = T1.DocEntry
where (T1.U_SO Is Null or T1.U_SO = ' ' or T1.U_SO = 'NULL' or T1.U_SO = '0') and T0.DocEntry = @List_of_cols_val_tab_del)
Begin
Select @error = -101,
@error_message = N'Please enter Sales Order No.'
End
End
Hope this helps
Thanks'
--
--
Atul Chakraborty
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.