Query for Price Change from last 5 GRPO Price

[former_member320372]

Hi All,

        I'm planning to set approval for purchase order document.Here i need a query,When the Item price is changing more 5%(Plus or Minus) from the Last five Purchasing price(GRPO Price). Please give your Suggestion.

[kothandaraman_nagarajan]

Hi,

Please note that approval query will not work at row level.Also, system will not trigger approval for updating document.

Thanks

[former_member320372]

Hi Nagarajan K ,

               Thanks for the reply. One of My client told me to set the  Approval for Purchase order, when the Price differ more than 5% from the avg price of last 5 purchase price or last purchase price....is there any way to achieve any one of my requirement from this two?

[Johan_H]

Hi Prabakaran,

There is the option of creating an alert with a query, and running that alert every x minutes or hours.

This will not stop a user from creating a PO, but it will let the supervisor know, so they can do something about it.

Regards,

Johan

[Johan_H]

Hi,

What if the last 5 purchasing prices differ more than 5% from each other to begin with?

For example:

GRPO 5 price = 500

GRPO 4 price = 526

GRPO 3 price = 499

GRPO 2 price = 525

GRPO 1 price = 500

New PO price = 525

Is the price in the PO good or bad ?

Regards,

Johan

[former_member320372]

Hi Johan Hakkesteegt,

     Thanks for the reply....I understand the problem in my Requirement. Can you please give the query for, when the Price is differ more than 5% from the last purchase price..

Cheers,

Prabakaran

[Johan_H]

Hi, Prabakaran,

Please try this:

SELECT h.DocNum

      ,h.CardCode

      ,h.CardName

      ,r.ItemCode

      ,r.Dscription

      ,r.Quantity

      ,r.Price

      ,(select AVG(t1.Price)

        from PDN1 t1 inner join OPDN t2 on t1.DocEntry = t2.DocEntry

        where t1.ItemCode = r.ItemCode

          and t2.DocDate = (select MAX(t3.DocDate)

                            from OPDN t3

                                 inner join PDN1 t4 on t3.DocEntry = t4.DocEntry

                            where t4.ItemCode = r.ItemCode)) AS LastPrice

FROM OPOR h

     INNER JOIN POR1 r ON h.DocEntry = r.DocEntry

WHERE h.DocStatus = 'O'

GROUP BY h.DocNum

      ,h.CardCode

      ,h.CardName

      ,r.ItemCode

      ,r.Dscription

      ,r.Quantity

      ,r.Price

HAVING (r.Price - (select AVG(t1.Price)

        from PDN1 t1 inner join OPDN t2 on t1.DocEntry = t2.DocEntry

        where t1.ItemCode = r.ItemCode

          and t2.DocDate = (select MAX(t3.DocDate)

                            from OPDN t3

                                 inner join PDN1 t4 on t3.DocEntry = t4.DocEntry

                            where t4.ItemCode = r.ItemCode))) / r.Price >= 0.05

OR

(r.Price - (select AVG(t1.Price)

        from PDN1 t1 inner join OPDN t2 on t1.DocEntry = t2.DocEntry

        where t1.ItemCode = r.ItemCode

          and t2.DocDate = (select MAX(t3.DocDate)

                            from OPDN t3

                                 inner join PDN1 t4 on t3.DocEntry = t4.DocEntry

                            where t4.ItemCode = r.ItemCode))) / r.Price <= -0.05

Regards,

Johan