Hi,

I would like to know how i can download inventory balance at specific time using SQL Queries?

Tq.

Hi,

Please post your script so that people can better understand your problem.

The script

select
whinr110.cwar, | Warehouse
whinr110.item, | Item
tcibd001.dsca, | Description
whinr110.qhnd | Inventory After Transaction
from
tcibd001, | Items - General
whinr110 | Inventory Transactions by Item and Warehouse
where
whinr110.cwar >= whinr110.cwar.f and
whinr110.cwar <= whinr110.cwar.t and
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
whinr110.trdt >= whinr110.trdt.f and
whinr110.trdt <= whinr110.trdt.t and
whinr110.item = whinr110.item

Hi,

I guess you have an incorrect where condition. Try one of these.

select
whinr110.cwar, | Warehouse
whinr110.item, | Item
tcibd001.dsca, | Description
whinr110.qhnd | Inventory After Transaction
from
tcibd001, | Items - General
whinr110 | Inventory Transactions by Item and Warehouse
where
whinr110.cwar >= whinr110.cwar.f and
whinr110.cwar <= whinr110.cwar.t and
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
whinr110.trdt >= whinr110.trdt.f and
whinr110.trdt <= whinr110.trdt.t and
whinr110.item = tcibd001.item

or

select
whinr110.cwar, | Warehouse
whinr110.item, | Item
tcibd001.dsca, | Description
whinr110.qhnd | Inventory After Transaction
from
tcibd001, | Items - General
whinr110 | Inventory Transactions by Item and Warehouse
where
whinr110.cwar >= whinr110.cwar.f and
whinr110.cwar <= whinr110.cwar.t and
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
whinr110.trdt >= whinr110.trdt.f and
whinr110.trdt <= whinr110.trdt.t and
whinr110.item refers to tcibd001

Hi Bhusha,
Thank you. i already change the script but i still can't print the qty balance at one specific time.
For example the item has transaction on date 28.01.14 and 30.01.14.
But when i print for qty balance 29.01.14 the report can't be printed.

Hi,

Check the transaction date & time of that record. May be your input isn't correct which is why its not able to print that record.

Hi Bhushan,

In my query, i would like to print the latest inventory balance based on date given.
Based on my previous example, i want my query print the balance on 28.01.14 as it is the latest from 29.01.14.
Hope you could guide me. :)

Hi,

You will then require to make a few changes in your report layout as well also the inputs.

In your report, you will need to define a before.field layout, which will be based on warehouse if you want warehouse wise stock in hand.

And, in your script you will need to add a condition for the date field.

Explore more on this and you will definitely make a good stock report.

If you create a 4GL report instead, it will be something like this:-

declaration:


table ttcibd001
table twhinr110
extern domain tcdate input.date
extern domain tccwar input.cwar

functions:

function read.main.table()
{

select tcibd001.item,
tcibd001.dsca
from tcibd001
selectdo
get.balance()
rprt_send()
endselect
}

function get.balance()
{
db.set.to.default(twhinr110)
select whinr110.trdt,
whinr110.item,
whinr110.qhnd,
whinr110.cwar
from whinr110
where whinr110.item = :tcibd001.item
and whinr110.trdt <= :input.date | The date on which you want the balance
and whinr110.cwar = :input.cwar| The warehouse for which you want the on hand inventory
as set with 1 rows
order by whinr110.trdt desc
selectdo
endselect
}


The above program will print the on hand inventory for a particular warehouse on particular date.

Hope it gives some hint.

All the best!

Hi Bhushan,

i'm clueless :(
i still can't print the report from my SQL Query.
i'm trying this for my opening stock movement.

Hi,

Please post the screen shot of your report layout. Also check if you are putting correct input from and to dates.

e.g. It might happen, Item A has some transaction on 29th Jan 2014 4pm but you are giving input as 28th Jan 2014 12am to 29th Jan 2014 12am so it wont find the record.

I am guessing that you are trying to print all the records in table whinr110 within given transaction date range.