cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure for PO !!!

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (4)

Answers (4)

KennedyT21
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Harshal,

Its working on my Side.

Can i have a look on TV.

Thanks'

-

--

Atul Chakraborty

Former Member
0 Kudos

Yes...

Shall I give my Ammy Admin ?

Former Member
0 Kudos

Hi Harshal,

its OK

Give me Ammy Admin.

I have Ammy 3.4 Version.

Thanks

Former Member
0 Kudos

I have 3.5 version ID is 53 407 900

Try if you can connect or I have Team viewe version 10

Former Member
0 Kudos

Hi Harshal,

I dont Have Ammy 3.5.

and i have TV 8.

Let me Download Ammy 3.5

Thanks

Former Member
0 Kudos

Hi Harshal,

Send me TV 10 details.

Thanks

Former Member
0 Kudos

Hi Atul,

Very sorry for not replying as urgently I need to go.

Regards,

Harshal Makwana

Former Member
0 Kudos

Hi Atul,

If possible Can you able to take TV today ?

Regards,

Harshal Makwana

former_member227598
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Kamlesh,

I have tried but its still not working. System allows to add doc even if U_SO is null or blank.

Thanks,

Harshal Makwana

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Atul,

It gives me an internal error if check this SP.

Thanks,

Harshal Makwana