SBO_SP_PostTransactionNotice error in Hanna

[Former Member]

if :Object_Type = '67'  and (:Transaction_Type = 'U' or :Transaction_Type = 'A') THEN

begin

update "VISIONTEST"."OPDN" set "VISIONTEST"."OPDN"."U_GRNS" = 'Released'

from "VISIONTEST"."OPDN"

where OPDN."DocNum" = (Select Top 1 TO_NVARCHAR( OWTR."U_grn")

  from "VISIONTEST"."OWTR" where OPDN."DocEntry"= To_Nvarchar(OWTR."U_grn") and  OWTR."DocEntry"=:list_of_cols_val_tab_del);

end ;

END IF;

[former_member184146]

Hi,

     Try this

if :Object_Type = '67'  and (:Transaction_Type = 'U' or :Transaction_Type = 'A') THEN

begin

        update "VISIONTEST"."OPDN" set "VISIONTEST"."OPDN"."U_GRNS" = 'Released'

        where OPDN."DocNum" = (Select Top 1 TO_NVARCHAR( OWTR."U_grn")

        from "VISIONTEST"."OWTR" WHERE OWTR."DocEntry"=:list_of_cols_val_tab_del)               AND OPDN."DocEntry"= (SELECT To_Nvarchar(OWTR."U_grn") FROM

       "VISIONTEST"."OWTR" WHERE  OWTR."DocEntry"=:list_of_cols_val_tab_del );

end ;

END IF;

Regards,

Manish

[Former Member]

Dear  Manish ,

Your SP is created successfully but the UDF   "OPDN"."U_GRNS"  is not updated from Block to released.

[former_member184146]

Hi Muhammad,

                         You need to run the query and check what it is returning after executing, put your values in where condition and check.

Regards,

Manish

[Former Member]

can you plz explain how to execute, my values are by-default  Blocked and this SP will Released it when inventory transfer document will posted or updated.

[former_member184146]

Hi,

assume your

(Select Top 1 TO_NVARCHAR( OWTR."U_grn")

        from "VISIONTEST"."OWTR = 200024 i.e Docnum= 200024 and docentry= 200025

then run the below query

update "VISIONTEST"."OPDN" set "VISIONTEST"."OPDN"."U_GRNS" = 'Released'

where OPDN."DocNum" = 200024              AND OPDN."DocEntry"= 200025

if this update your field then the above TN should work.

Regards,

Manish

[former_member184146]

Hi,

Any update on this TN???

--Manish

[Former Member]

Manish,

update "VISIONTEST"."OPDN" set "VISIONTEST"."OPDN"."U_GRNS" = 'Released'

where OPDN."DocNum" = 200024

its update the status if i run this query. 

but when i put it  in  TN its execute successfully   and  i add a document  the block status not change in Released status

[Former Member]

this TN is working on SQL perfectly but i cnt convert it on HANNA.

if @object_type = '67'  and (@transaction_type = 'U' or @transaction_type = 'A')

begin

update OPDN set OPDN.U_GRNS = 'Released'

from OPDN

where OPDN.DocNum = (Select Top 1 convert( int,OWTR.U_grn)

  from OWTR where OPDN.DocNum = convert(int,OWTR.U_grn) and  OWTR.DocEntry = @list_of_cols_val_tab_del)

end

[former_member184146]

Hi,

     Can you explain a little that what you want to achieve with this command

(Select Top 1 TO_NVARCHAR( OWTR."U_grn")

my reason of asking is because this command is giving the result in an undefined order .

Regards,

Manish

[kothandaraman_nagarajan]

Hi,

Try:

if :Object_Type = '67'  and (:Transaction_Type = 'U' or :Transaction_Type = 'A') THEN

begin

update "VISIONTEST"."OPDN" set "VISIONTEST"."OPDN"."U_GRNS" = 'Released'

from "VISIONTEST"."OPDN"

where OPDN."DocNum" = (Select Top 1 ( OWTR."U_grn")

  from "VISIONTEST"."OWTR" where OPDN."DocEntry"= To_Nvarchar(OWTR."U_grn") and  OWTR."DocEntry"=:list_of_cols_val_tab_del);

end ;

END IF;

Thanks & Regards,

Nagarajan

[Former Member]

Same error.

[kothandaraman_nagarajan]

Please refer document

[Former Member]

Not help full .

[kothandaraman_nagarajan]

Without accessing your DB, i am not able to fix this error.