Formated Search in Approval Procedure

[former_member209021]

I have a some difficulties with a Approval Procedures based in Sales Quotation against Sales Order and I need help of my fellows...

In my limited knowledge, to use a Formated Search into a Approval Procedure(A.P), is necessary who FS return 'TRUE' at value tested and this occurs in my tests over sql server... But, when I put this FS into the A.P nothing happen like I really wish...

The reason for this FS is check the "sum of quantities" if a Sales Order is greater than "sum of quantities" in the Sales Quotation origin (Base Document)... when I run this FS under a Sales Order incorrectly created over the Sales Quotation, I can see my criteria been returned like 'true' (see printscreen attachment), but the approval do not open in time Sales Order creation how is expected at the AP normal...

I have my doubts if this problem is been on sintax FS... I made many tests altering the sintax and all return me the result 'true'....

Where you see '= 10' in below codes I have used variable '$[Rdr1.BaseEntry]' to return my number document like a base to check and run the FS...

Sintaxe 1 (mus simple sintax)

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

Declare

@qtCota Numeric(10,4),

@qtPed Numeric(10,4)

Set @qtCota = (select SUM(Quantity) from qut1 where DocEntry = 10 and unitMsr = 'M2'

group by DocEntry)

Set @qtPed = (select SUM(Quantity) from rdr1 where BaseEntry = 10 and unitMsr = 'M2'

group by BaseEntry)

If @qtPed > @qtCota

  Begin

  select 'True', @qtPed as Ped, @qtCota as Cota

  End

Else

  Begin

  select 'false'

  End

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

Sintaxe 2 (little more complex sintax)

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

Declare

@qtCota Numeric(10,4),

@numCota Numeric,

@qtPed Numeric(10,4)

Set @numCota = (Select distinct rdr1.BaseEntry from Rdr1 inner join Ordr on Rdr1.DocEntry = 10)

Select 'true',

(select SUM(Quantity) from rdr1 where BaseEntry = @numCota and unitMsr = 'M2' group by BaseEntry) as Ped ,

(select SUM(Quantity) from qut1 where DocEntry = @numCota and unitMsr = 'M2' group by DocEntry) as Cota

from ORDR a

     inner join RDR1 b on a.DocEntry = b.DocEntry

     inner join QUT1 c on b.BaseEntry = c.DocEntry

     where b.BaseEntry = @numCota

     Group by

     c.DocEntry,

     b.BaseEntry

     Having (select SUM(Quantity) from rdr1 where BaseEntry = @numCota and unitMsr = 'M2'

  group by BaseEntry) >

  (select SUM(Quantity) from qut1 where DocEntry = @numCota and unitMsr = 'M2'

  group by DocEntry)

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

Sintaxe 3 (a commom sintax)

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

Declare

@qtCota Numeric(10,4),

@numCota Numeric,

@qtPed Numeric(10,4)

Set @numCota = (Select distinct rdr1.BaseEntry from Rdr1 inner join Ordr on Rdr1.DocEntry = 10)

Select

case when

(select SUM(Quantity) from rdr1 where BaseEntry = @numCota and unitMsr = 'M2'  group by BaseEntry) >

(select SUM(Quantity) from qut1 where DocEntry = @numCota and unitMsr = 'M2'  group by DocEntry)

Then 'true'

else 'false'

End

Anyone can help me to solve this... I'm late to put this function in operation, and my boss is upsetting me a lot for this... lol

Regards

Wagner Bezerra

[former_member209021]

My friend Nagarajan,

I got it!!!

I made a little code into Transaction Notification like below:

if @object_type in ('17')  --Sales Order

and @transaction_type in ('A','U')  --on Insert or Update

Begin

  Declare

     @BaseEntry int

  -- Get BaseRef from Qut1 x Rdr1

         if @object_type = '17'

            begin

             Set @BaseEntry = (Select rdr1.BaseRef from RDR1 left join ORDR on ORDR.DocEntry = RDR1.DocEntry

    Where ORDR.DocEntry = @list_of_cols_val_tab_del )

            end

  -- Test my conditions between QtyQuotation x QtySales Order

  if

  (select SUM(Quantity) from qut1 where DocEntry =  @BaseEntry and unitMsr = 'M2' group by DocEntry) <

  (select SUM(Quantity) from rdr1 where BaseEntry = @BaseEntry and unitMsr = 'M2' group by BaseEntry)

  Begin

  Update ORDR set Ordr.U_Aprovacao = 'SIM' Where DocEntry = @list_of_cols_val_tab_del

  End

End --<Fim>

Added to this, I create a UDF "Ordr.U_Aprovacao" where set by default 'NAO', then if my conditions are return, I set UDF like 'SIM'...

I'm following a SAP hierarchy (in my understood) where...

First: interpret Transacation definitions;

Second: interpret others (in this case, Approvel Procedures)

Now I got the Document in Approval Procedure...

Thank's a lot for you attention...

If you have comments, please, fell free!

[kothandaraman_nagarajan]

Hi,

Thanks for feedback. I am not sure we can update or insert values through query or stored procedure for UDF.

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Approval procedure will work only on header level not on row level. This is standard system behavior.

But if only one item at row level, approval procedure will work. If multiple items, then approval procedure will not work.

Below is FMS for above requirement:

SELECT Distinct 'TRUE' FROM QUT1 T0 left join  RDR1 T1 on T1.[BaseEntry]  = t0.docentry and T1.[BaseLine]  = T0.[LineNum] WHERE t0.docentry = $[$38.45.number] and  T0.[ItemCode]  = $[$38.1.0] GROUP BY T0.[Quantity] Having  T0.[Quantity] < sum(T1.[Quantity])

Thanks & Regards,

Nagarajan

[former_member209021]

Hi Nag,

Man, does not work yet...

I made a little modification in the code:

SELECT Distinct 'TRUE',

sum(T1.[Quantity]) qtySalesOrder,

Sum(T0.[Quantity]) qtyQuotation

FROM QUT1 T0 left join  RDR1 T1 on T1.[BaseEntry]  = t0.docentry

and T1.[BaseLine]  = T0.[LineNum]

and t0.unitMsr = 'M2'

WHERE t0.docentry = N'24' ** using variable $[$38.45.number]

GROUP BY T0.[Quantity]

Having   sum(T1.[Quantity]) > Sum(T0.[Quantity])

It's necessary because I have test the sum of all items based on quotation... Look in the printscreen the situation...

My quotation qty(sum all) is 1... If when I copy this Quotation to Sales Order with a qty (sum all) is greater than 1 (based quotation) then, is necessary an A.P.

I see TRUE on return select... I put in print the qty's only to check return SUM...

This only work if I run the selec over doc inserted...

*** If I made a FS only "Select TRUE" and put it in the A.P config, the A.P work normal... Just to force test A.P...

Is hard solve this!!!

[kothandaraman_nagarajan]

Hi,

1. Thanks for feedback. My idea is if any one of the item quantity exceeds from sales quotation, then SP should go for approval.

2. Try this modified query to capture sum of quotation quantity

SELECT Distinct 'TRUE' FROM QUT1 T0 left join  RDR1 T1 on T1.[BaseEntry]  = t0.docentry and T1.[BaseLine]  = T0.[LineNum] WHERE t0.docentry = $[$38.45.number] and  T0.[ItemCode]  = $[$38.1.0] GROUP BY T0.[Quantity] Having  sum(T0.[Quantity]) < sum(T1.[Quantity])

3. By standard, we have to use 'True' in approval procedure. there is no need of getting sum of quantity in approval procedure.

Thanks & Regards,

Nagarajan

[former_member209021]

Hi friend...

Definitely, does not work!

the values sum of qty and number of baseref does not return in time execution, before document inserted...

I have try all ways to do this work...

Some experts her in Brasil guarantee to me thats will not work anyway.

Thanks for all!

Wagner

wagnervaleriobezerra - skype