Skip to Content
avatar image
Former Member

SP query for the OCRD and OCPR

Hi Experts,

I am using the below queries. Query 1 is working and when i update the query as like query 2 , then it is not working.

what would be the issue ?

Query 1

IF (@transaction_type = 'A'OR  @transaction_type ='U') AND @object_type = '2'

BEGIN

If exists (SELECT T0.CardCode FROM OCRD T0

WHERE ((ISNUll(T0.CntctPrsn,'')='') )  AND (T0.CardCode = @list_of_cols_val_tab_del))

begin

SET @error = 110

SET @error_message = N'MUST SPECIFY ALL CONTACT InFO'

end

END

QUery 2

IF (@transaction_type = 'A'OR  @transaction_type ='U') AND @object_type = '2'

BEGIN

If exists (SELECT T0.CardCode FROM OCRD T0 INNER JOIN OCPR T1 ON T0.CardCode = T1.CardCode

WHERE ((ISNUll(T0.CntctPrsn,'')='') and (ISNUll(T1.E_MailL,'')='') )   AND (T0.CardCode = @list_of_cols_val_tab_del))

begin

SET @error = 110

SET @error_message = N'MUST SPECIFY ALL CONTACT InFO'

end

END

Thanks in advance,

Regards,

Dwaraka

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 26, 2015 at 11:45 AM

    Hi,

    You should use OR operator. Refer the below one.

    IF (@transaction_type = 'A'OR  @transaction_type ='U') AND @object_type = '2'

    BEGIN

    If exists (SELECT T0.CardCode FROM OCRD T0 INNER JOIN OCPR T1 ON T0.CardCode = T1.CardCode

    WHERE ((ISNUll(T0.CntctPrsn,'')='')  OR (ISNUll(T1.E_MailL,'')='') )   AND (T0.CardCode = @list_of_cols_val_tab_del))

    begin

    SET @error = 110

    SET @error_message = N'MUST SPECIFY ALL CONTACT InFO'

    end

    END

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      I tried all kind of means, finally i have splitted into 2 query as below. now it works.Because the Email field is dependent on Contact person name field. so for me both is mandatory.

      --------------------------------------------------------------------------------------------------------------------------------

      -- Business Partner Control - Contact person name(OCRD)

      -----------------------------------------------------------------------------------------

      IF (@transaction_type = 'A'OR  @transaction_type ='U') AND @object_type = '2'

      BEGIN

      If exists (SELECT T0.CardCode FROM OCRD T0

      WHERE (ISNUll(T0.CntctPrsn,'')='') AND (T0.CardCode = @list_of_cols_val_tab_del))

      begin

      SET @error = 110

      SET @error_message = N'MUST SPECIFY ALL CONTACT Info'

      end

      END

      --------------------------------------------------------------------------------------------------------------------------------

      -- Business Partner Control - Contact person E-mail(OCPR)

      -----------------------------------------------------------------------------------------

      IF (@transaction_type = 'A'OR  @transaction_type ='U') AND @object_type = '2'

      BEGIN

      If exists (SELECT T0.CardCode FROM OCPR T0

      WHERE (ISNUll(T0.E_MailL,'')='') AND (T0.CardCode = @list_of_cols_val_tab_del))

      begin

      SET @error = 3524

      SET @error_message = N'MUST SPECIFY the Email ID'

      end

      END

  • avatar image
    Former Member
    Oct 26, 2015 at 12:42 PM

    Hi

    If you check the email in the contact person so there is an contact person that is why T0.CntctPrsn will never be NULL the problem is that part :

    ((ISNUll(T0.CntctPrsn,'')='')

    I think you can delete it from your query

    shachar

    Add comment
    10|10000 characters needed characters exceeded