cancel
Showing results for 
Search instead for 
Did you mean: 

Query on openqty doesn't work in SP_TransactionNotification

Former Member
0 Kudos

Hi everybody,

we would like to avoid that a user can add more pieces in OPDN as originally was ordered in OPOR. So i want to modify the SP_TransactionNotification in that way:

If @object_type = '20' and @transaction_type = 'A'

Begin

declare @baseqty int

declare @inqty int

declare @minline1 int

declare @maxline1 int

declare @baseline int

declare @baseentry int

set @minline1 = (select min(T0.linenum) from PDN1 T0 where T0.docentry=@list_of_cols_val_tab_del)

set @maxline1 = (select max(T0.linenum) from PDN1 T0 where T0.docentry=@list_of_cols_val_tab_del)

Begin

WHILE @minline1<=@maxline1

        BEGIN

            set @baseline = (select T0.BaseLine From PDN1 T0 where T0.DocEntry=@list_of_cols_val_tab_del AND T0.linenum=@minline1)

            set @baseentry = (select T0.BaseEntry From PDN1 T0 where T0.DocEntry=@list_of_cols_val_tab_del AND T0.linenum=@minline1)

            set @baseqty = (select T1.OpenCreQtyFROM POR1 T1 WHERE T1.DocEntry = @baseentry AND T1.LineNum = @baseline)

            SET @inqty = (SELECT T0.Quantity FROM PDN1 T0 WHERE T0.docentry=@list_of_cols_val_tab_del AND T0.linenum=@minline1)

            if @inqty > @baseqty

                BEGIN

                    SET @error = 1

                    SET @error_message = @baseqty

                End

            SET @minline1=@minline1+1

        end

    End

End

it works quite nice if i use T1.Quantity in the line with set @baseqty but if i set it to T1.OpenCreQty or T1.OpenQty the error_message always return 0 as value (but if i query it in SAP it's the correct value, not 0 because there are still pieces open )

I can't imagine why, but maybe some of you knows the answer

kr

Manuel

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Manuel,

The correct field name is OpenQty not OpenCreQty as in your SP.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

thanks for your fast reply, but i've already tried it with OpenQty and also get 0 as return value

kr

Manuel

Former Member
0 Kudos

Have your queried the PO to check the field value?

Former Member
0 Kudos

yes... if i query the field in SAP it shows the correct value. That's the major point why i am so confused...

Former Member
0 Kudos

Have you tried BaseOpnQty from PDN1 instead?

Former Member
0 Kudos

Thank you Gordon - you're the man!

That was the right field. I've never noticed that field before in PDN1. Many thanks, now it works like a charm

kr

Manuel

Answers (1)

Answers (1)

former_member218051
Active Contributor
0 Kudos

Hi Manuel,

If you want to compare the quantity ordered originally then you will have to consider the quantity field from por1.

OpenQty is the remaining open quantity and

OpenCreQty is the Credit Memo One.

Thanking you

Malhaar