FIFO method - Avg Price

[Former Member]

Hi experts,

     Just want to ask how SAP can determine the last avg price of the item if the method is FIFO. I'm creating a SQL query based from the quantity and price from the sales and get the avg price of the item.

Regards,

[Former Member]

I am sorry i not am aware of sql coding but we can achieve the result by using control break events by the following program, just copy paste in se38

DATA : it TYPE TABLE OF ekpo,

       wa TYPE ekpo.

DATA : count TYPE ekpo-netwr VALUE 0,

        count1 TYPE ekpo-netwr VALUE 0,

        count2 TYPE ekpo-netwr VALUE 0.

SELECT-OPTIONS : s_ebeln FOR wa-ebeln.

SELECT * FROM ekpo INTO  TABLE it

   WHERE ebeln IN s_ebeln.

SORT it BY ebeln.

LOOP AT it INTO wa.

   WRITE : / wa-ebeln, wa-ebelp, wa-netwr.

   count = count + wa-netwr.

   count1 = count1 + 1.

   AT END OF ebeln.

     IF sy-subrc = 0.

       WRITE 😕 'THE TOTAL OF  NETWR',count.

       count2 = count / count1.

       WRITE 😕  'THE AVERAGE OF NETWR',count2.

       CLEAR count.

       CLEAR count1.

       CLEAR count2.

     ENDIF.

   ENDAT.

ENDLOOP.

[kothandaraman_nagarajan]

Hi Arun,

Thanks for your effort and time. But this forum for SAP Business one and query required from SAP B1 tables.

Thanks & Regards,

Nagarajan

[Former Member]

you are welcome,Mr. Nagarajan

[Johan_H]

Hi Bryan,

Are you trying to determine the average cost price, the average sales price, or the average profit ?

Regards,

Johan

[Former Member]

Hi Johan,

     The COST price.

Regards,

[Johan_H]

Hi Bryan,

Ok, the Base Price Origin should be set to Cost Price (anyway). After that the system registers the Cost Price of each item in the document row tables (GrossBuyPr) at the moment the document is created.

So you can calculate the average GrossBuyPr based on all relevant documents.

Regards,

Johan

[former_member188586]

Hi

try with bellow Query  and analyze Avg Price

last 30 purchase item lists will display according to that you can calculate Average price  

SELECT TOP 30 T0.[DocNum],T0.[DocDate], T0.[CardName],  T1.[ItemCode],  T1.[Dscription], T1.[FreeTxt], T1.[Project], T1.[Quantity], T1.[Price] FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] =[%0] and T1.[TargetType] !='19' order by  T0.[DocDate] desc

Thanks&regards

Andakondaramudu