Skip to Content
avatar image
Former Member

Stored Procedure for PO !!!

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Oct 30, 2015 at 06:32 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 30, 2015 at 08:28 AM

    Hi Harshal,

    Its working on my Side.

    Can i have a look on TV.

    Thanks'

    -

    --

    Atul Chakraborty

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 30, 2015 at 05:48 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Nov 02, 2015 at 11:37 AM

    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

    Add comment
    10|10000 characters needed characters exceeded