FIFO Query to generate report Which relates the out-quantity to in-quantity

[Former Member]

Dear Experts,

I am trying to generate a report to find out the Inward documents of Items which are issued or transfered. Items are managed with FIFO. Certain Items are serially managed and certain not.

I am using the tables OIVL, OIVE and OIVQ for getting the details.

Now my problem is in certain situations the relation between OIVE and OIVQ returns more line items than required and not able to specifically found out the rows. (Same TreeID is used for several transactions)

Please help

Thanks and regards

Ajith Gopalakrishnan

[Former Member]

HI,

Can u elaborate more specific. IF possible give the example.

Regards

Vaibhav A.

[Former Member]

Hi,

My requirement is to know the inventory incoming document for an Inventory issue/stock transfer.

Ie If 50 quantitiy of an Item 'A001', I am transfering from warehouse 'W1' to a different warehouse 'W2' .

Then this report should show how this 50 item came into the stock of 'W1'.

It can be Goods Receipt/Goods Receipt PO/ AP invoice/Inventory transfer from another warehouse.

Thanks in advance

Regards

Ajith G

[Former Member]

Hi,

I think the Inventory > Inventory Reports > Inventory Audit Report would be adequate. To see the movement in and out that Warehouse, you can select per Item and for Specific Wartehouses.

Kind regards

Carin

[Former Member]

Hi Ajith,

Go to Inventory / Inventory Reports / Inventory Posting List

Go to Inventory / Inventory Reports / Inventory Audit Report

I think this two reports give proper result.

Thanks,

Srujal Patel

[Former Member]

Hi All,

The specified reports does not serve my purpose as these reports does not link between the outgoing quantity and incoming quantity. Out going quantity of 50 can be from different incoming inventory.

This report is required as we are claiming VAT input credit(India).

In case if we are taking inventory from one state to another, we are not supposed to take this advantage and statutory requirments says that we need to reverse certain percentage of TAX of such items.

Thanks and regards

Ajith Gopalakrishnan

Edited by: Ajith G on Jun 20, 2011 3:59 PM

[former_member206488]

Hi,

Can you paste query you created here, so that modifictions required can be made

[Former Member]

Hi,

Please find below the query

Select T0.TransSeq, T0.BASE_REF,T0.DocDate,T0.ItemCode,T6.Dscription,T0.OutQty, T1.Filler,T5.BASE_REF,T5.TransType,
T5.DocDate,T5.DocLineNum,T5.InQty,T5.ItemCode,T2.LayerOutQ,T2.EntryTreeI,T3.LayerID,t3.TrasSeq
FROM OIVL T0
     Inner Join OWTR T1 on T0.BASE_REF=T1.DocNum
     Inner Join WTR1 T6 on T6.DocEntry=T1.DocEntry and T0.DocLineNum=T6.LineNum
     Inner Join OIVE T2 on T2.TransSeq=T0.TransSeq
     Inner Join OIVQ T3 on T3.TreeID=T2.EntryTreeI and T3.LocCode=T2.LocCode   -- Problem is in this relation
     Inner Join OIVL T5 on T5.TransSeq=T3.TransSeq
Where T0.TransType=67 and T0.DocDate Between 'YYYYMMDD' and 'YYYYMMDD' and T0.LocCode='WH1' and T1.Filler='WH1'
        and T1.DocStatus<>'C' and T6.Quantity>0

Thanks and regards

Ajith Gopalakrishnan

[Former Member]

Hi Experts,

I have gone through your Query

But Sorry most of the tables from Query is Not Available in SAP B1

Regards,

Sandip Kokate

[Former Member]

Hi,

Sorry for the delayed reply.

We are using SAP Business One 8.8 (PL 10).

Earlier(Prior to 8.8) OINM was the table for this purpose and Now SAP

moved OINM as a view and introduced these tables for detailed storage of inventory layers.

We will be grateful if anybody give a solution for this

Thanks and Regards

Ajith G

[Former Member]

HI ,

Can any one help in this?

Thanks in advance.

Regards

Ajith G

[Former Member]

Hi,

This Query help you a lot as I made this query for displaying the recieve and out quantity of an item as per the date i.e. On which date you recieve how much quantity of an item and how much quantity you release of an item. and please amend the remaining transtype from the transaction table (OINM).

In case of FIFO you know that First IN First OUT is followed so you can easily see that which item on a particular date you recieve and which item on a particular date you release...and if any more further enhancement you required then please let me know ....if will definitely make out some time to help you out.

select distinct SUM(InQty)as InQty,SUM(OutQty)as OutQty,ItemCode,Dscription,DocDate, TransType,

case TransType

when '-2' then 'opening Balance'

when '20' then 'Goods Reciept PO '

when '59' then 'Reciept From Production/Good Reciept '

when '15' then 'Deliveries '

end as 'TransName'

from (

select ItemCode,Dscription,DocDate, InQty,OutQty,TransType from oinm ) as OINM

group by DocDate,ItemCode,TransType,Dscription order by docdate asc

Thanks

Randy