Hi Everyone ,

I'm in progress to produce report which will assist user by producing stock movement quantity together with stock value / amount .

When user execute the query, the script shall display

Item | Opening stock | Opening Value | receipt qty | Receipt value | issued qty | Issued value | Adj Qty | Adj Value | Closing qty | Closing value

I have a script which successfully count display for quantity::

declaration:
domain tcqiv1 issue.qty, opening.balance, receipt.qty, closing.balance, inv.adj

before.program:

issue.qty = 0
opening.balance = 0
receipt.qty = 0
closing.balance = 0
inv.adj = 0

detail.1:
before.layout:
if whinr110.kost = tckost.issue then
issue.qty = issue.qty + whinr110.qstk
else if whinr110.kost = tckost.receipt then
receipt.qty = receipt.qty + whinr110.qstk
else if whinr110.kost = tckost.stc.correction then
inv.adj = inv.adj + whinr110.qstk
endif
endif
endif

closing.balance = closing.balance + whinr110.qhnd
lattr.print = 0

after.whinr110.item.1:
before.layout:
opening.balance = whinr110.qhnd + issue.qty - receipt.qty - inv.adj

after.layout:

issue.qty = 0
opening.balance = 0
receipt.qty = 0
closing.balance = 0
inv.adj = 0

based on above I shall continue with stock value, however, as of today, I still fail . Does anyone has experience or idea about this ? :)

Why have you put lattr.print = 0 in the detail.1 ... before.layout?

The script successfully generate for my quantity movement.
I have no issue with that with my previous report .
May I know why you ask ?
there is constraint that I should know ?

lattr.print = 0 or lattr.print = false means that layout wont be printed.


What is exactly wrong in the report?

Any field out of the below is not getting printed? Or getting printed as Zero?

Item | Opening stock | Opening Value | receipt qty | Receipt value | issued qty | Issued value | Adj Qty | Adj Value | Closing qty | Closing value

Hi Ajesh ,

thank you for your time .

Now the script I have successfully giving me
Item | Opening Stock Qty | Receipt Stock Qty | Issued Stock Qty | Adj Stock Qty | Closing Stock Qty

All field printed and calculated as per range of date entered.

From this script, I need expand to fulfill user requirement which is they want stock qty side by side with value.

Item | Opening Stock Qty | Opening Stock Value | Receipt Stock Qty | Receipt Stock Value |Issued Stock Qty | Issued Stock Value | Adj Stock Qty | Adj Stock Value | Closing Stock Quantity | Closing Stock Value .

I have tried to use whina113. amnt(1) , so it will calculate as per qty that we already have. but when I compiled , I failed.
I also tried to use whina124 , but result same. I couldn't see the result since at compilation stage I already failed.

Thank you

Hi Ajesh ,

This is the file generated from above script which I shared .

Thank you

So in which table the Value for these Items exist? IMO, it should exist in whinr110 table itself. Can You check the fields in the above table like Amount or Value or Price or something like that?

The Table field should define a value in currency. Can you check which of the field in whinr110 it is? I think it should be in that table itself.

Hi Ajesh ,

the value not in whinr110.
we can capture value from whina113 ( inventory receipt cost details ) or whina124 ( inventory integration transaction ).

this is my text for calling the table in query that attached to the script ...

select
whinr110.item, | Item
tcibd001.dsca, | Description
tcibd001.citg, | Item Group
whinr110.qstk, | Quantity (Inventory Unit)
whinr110.kost, | Transaction Type
whinr110.qhnd, | Inventory After Transaction
whinr110.trdt, | Transaction Date
whinr110.cwar, | Warehouse
whina113.amnt(1) | Amount
from
tcibd001, | Items - General
whina113, | Inventory Receipt Transaction - Cost Details
whinr110 | Inventory Transactions by Item and Warehouse
where
whinr110.trdt >= whinr110.trdt.f and
whinr110.trdt <= whinr110.trdt.t and
whinr110.cwar >= whinr110.cwar.f and
whinr110.cwar <= whinr110.cwar.t and
tcibd001.citg >= tcibd001.citg.f and
tcibd001.citg <= tcibd001.citg.t and
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
whina113.cpcp = 'MAT' and
whinr110.cwar = whina113.cwar and
whinr110.item = tcibd001.item and
whinr110.item = whina113.item and
whinr110.trdt = whina113.trdt

why I have this error ?
I tried to compile my script for receipt amount :o

That query is completely incorrect.:p



select
whinr110.item, | Item
whinr110.qstk, | Quantity (Inventory Unit)
whinr110.kost, | Transaction Type
whinr110.qhnd, | Inventory After Transaction
whinr110.trdt, | Transaction Date
whinr110.cwar, | Warehouse
from whinr110 | Inventory Transactions by Item and Warehouse
where
whinr110.trdt >= :trdt.f and
whinr110.trdt <= :trdt.t and
whinr110.cwar >= :cwar.f and
whinr110.cwar <= :cwar.t and
whinr110.item >= :item.f and
whinr110.item <= :item.t
selectdo
select whina113.amnt(1) | Amount
from whina113
where whina113.cwar = :whinr110.cwar | Not sure about which |fields are there in whina113 Index.So Put accordingly
and whina113.cpcp = 'MAT'
and whina113.trdt = :whinr110.trdt
selectdo
endselect
select tcibd001.dsca,tcibd001.citg
from tcibd001
where tcibd001._index1 = {:whinr110.item}
selectdo
endselect
endselect



And try to use the indexes as much as possible in the queries. Its a good practice.

Hi Ajesh :D

Am still at learning process with try & error method.
I really appreciate your help.
I will share the outcome.

Thank you Guru

Hi Ajesh,

compilation = successful
execution = no error

trial run for specific date range = return no data .

I will go to basic back , recheck the table & linkage.
then only I go for calculation/summation script .

Thank you

Hi Datin

Where have you put this code in your Program Script? WHich Section?

And i am not too sure about the selection criteria of


and whina113.cpcp = 'MAT'


Below will give more chance of running


string hold.cpcp
hold.cpcp = "MAT"

and whina113.cpcp = :hold.cpcp



Hope you get what i am trying to say.

Hi Ajesh,

I'm not using standard SQL script .
Its a function in Baan .
I understand your point but I couldn't see how to do using this standard function.

Thank you

Hello Datin

Okk... I didnt know it was SQL Queries you were using. I thought you were using 4GL Program Scripts.

So your earlier Code was giving output but the modified script is not giving output at all? Is it correct?