SP query for the OCRD and OCPR

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member]

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