Query to generate a stocktake details

[Former Member]

Hi

I need a query to generate the stocktake details for a certain journal entry

I need to show all the item numbers, description, In whse, counted, Differemce, price, Inv Offset-Dec, Inv Offset-Inc

In brief I would like to get the fields in the Inventory Posting window for the stocktake (relative of course to a specific Journal entry)

Thanks for your help

[KennedyT21 · Accepted solution]

HI Sham Per,

Try This

Select * from (

select  Itemcode,sum(InQty-outqty) As Counted ,'0' As Stock, Warehouse  from OINM where TransType='10000071' 

group by  Itemcode,Warehouse

union all

   Select  Itemcode,'0' ,Sum(InQty-outqty)As Stock , Warehouse from OINM where TransType<>'10000071' 

group by  Itemcode,Warehouse

) as ob

Hope Helpful

Regards

Kennedy

[Former Member · Accepted solution]

I have created a query from the OINM table to return the '100000071' related transactions

I added to it a variable selection which will prompt for the " Document Key Created " which is the Origin No in the journal entry

this will then lists all the items with their transactions specific to a journal

SELECT *  FROM OINM T0 WHERE T0.[TransType] = 10000071  AND

  T0.[CreatedBy] =[%0]

this is what I needed , above replies helped me achieve my requirement

thanks

[Former Member]

Hi Sham.......

If you check OINM Table whet the transType='10000071' you get the complete details.....

Please check this table......

Regards,

Rahul