Dear,

I have written a script which works perfectly for one date .

Now i want to give the date range for this report to know Inventory valuation by
Warehouse report. This is my script.

function determine.item.cost.price.data()
{
initialize.cost.price.data()
| if stoc.date.f < tiitm001.ltcp then
if stoc.date.t < tiitm001.ltcp then
copr.indt = determine.cost.price.introduction.date()
endif
read.cpr300.cost.price.data()
}

function initialize.cost.price.data()
{
item.matc = 0.0
item.oprc = 0.0
copr.indt = tiitm001.ltcp
}

function domain tcdate determine.cost.price.introduction.date()
{
select ticpr300.*
from ticpr300
where ticpr300._index1 = {:tiitm001.reli, :tiitm001.cntr}
| and ticpr300.indt <= :stoc.date.f |#irfan.o
and ticpr300.indt <= :stoc.date.t
order by ticpr300.item desc, ticpr300.cntr desc,
ticpr300.indt desc, ticpr300.cpcp desc
as set with 1 rows
selectdo
return(ticpr300.indt)
endselect
return(0)
}

function read.cpr300.cost.price.data()
{
select ticpr300.*, ticpr010.*
from ticpr300, ticpr010
where ticpr300._index1 = {:tiitm001.reli, :tiitm001.cntr,
:copr.indt}
and ticpr300.cpcp refers to ticpr010
selectdo
if ticpr010.cref = tccref.material or
ticpr010.cref = tccref.chg.material then
item.matc = item.matc + ticpr300.amnt
else
item.oprc = item.oprc + ticpr300.amnt
endif
endselect
}

function determine.item.stock.valuation.by.warehouse()
{
item.stoc = 0
select tdinv001.*, tcmcs003.*
from tdinv001, tcmcs003
where tdinv001._index2 inrange {:tiitm001.reli, :tiitm001.cntr,
:cwar.f}
and {:tiitm001.reli, :tiitm001.cntr,
:cwar.t}
and tdinv001.cwar refers to tcmcs003
selectdo
if determine.stock.level() then
determine.item.stock.level()
endif
if copr.indt <> 0 and report.item.stock() then
determine.item.stock.value()
|print.record(1) |#irfan.o
rprt_send()
endif
endselect
}

function domain tcbool determine.stock.level()
{
| if stoc.date.f < date.num() then |#irfan.o
if stoc.date.t < date.num() then
| if stoc.date.f < tdinv001.hstd then |#irfan.o
if stoc.date.t < tdinv001.hstd then
item.stoc = 0.0
return (false)
else
item.stoc = tdinv001.hstb
return (true)
endif
endif
item.stoc = tdinv001.stoc
return (false)
}

function determine.item.stock.level()
{
select tdinv700.*
from tdinv700
where tdinv700._index1 = {:tdinv001.item, :tdinv001.cntr,
:tdinv001.cwar}
| and tdinv700.trdt <= :stoc.date.f |#irfan.o
and tdinv700.trdt <= :stoc.date.t
selectdo
if tdinv700.kost = tckost.stc.correction or
( tdinv700.kost = tckost.stc.transfer and
tdinv700.koor <> tckoor.act.sls and
tdinv700.koor <> tckoor.sma.order ) or
tdinv700.kost = tckost.pur.receipt or
tdinv700.kost = tckost.prd.receipt or
tdinv700.kost = tckost.rpl.receipt then
item.stoc = item.stoc + tdinv700.quan
else
item.stoc = item.stoc - tdinv700.quan
endif
endselect
}

function domain tcbool report.item.stock()
{
if item.stoc = 0 and suppr.zero.stoc = tcyesno.yes then
return (false)
endif
return (true)
}

function determine.item.stock.value()
{
amnt.matc = round(item.matc * item.stoc,2,1)
amnt.oprc = round(item.oprc * item.stoc,2,1)
amnt.copr = amnt.matc + amnt.oprc
}

where are you facing error in full script?

Kindly explain your problem.

I 'm facing problem in stoc.date.f and stoc.date.t variables.

Whenever i give input from 01/01/10-31/01/10. I get the data only as of 31/01/10 but not as of the whole month. I want to get the history of the inventory valuation date.

Regards,
Suhaib

Hi,

it's just a typo! It should be:

and ticpr300.indt >= :stoc.date.f

It didnt work

Try this, it should work:

tdinv700.trdt between :stoc.date.f and :stoc.date.t

it didn't work

It should work. Validate the other data in the query - make sure nothing else is narrowing it down to that one date. Also validate that more than one record exists in the query range. That example and this one should both work.

and tdinv700.trdt >= :stoc.date.f
and tdinv700.trdt <= :stoc.date.t

Maybe post the actual code. Something other that the date has got to be impacting the query.

Dear,

This is my code again

|*** includes ******************************************************************

#include "itccom0010" | Report Handling
#include "itcmcs0095" | Parameter Handling

|*** program sections **********************************************************

before.program:
tcmcs0095.read.parm("tdinv000")
read.tcmcs096()

|*** forms *********************************************************************

form.1:
init.form:
get.screen.defaults()

|*** fields ********************************************************************

field.item.f:
when.field.changes:
item.t = item.f

field.cntr.f:
when.field.changes:
cntr.t = cntr.f

field.citg.f:
when.field.changes:
citg.t = citg.f

field.cwar.f:
when.field.changes:
cwar.t = cwar.f

field.stoc.date.f:
before.input:
if tdinv000.sths = tcyesno.no then | If no history stock transactions
| stoc.date.f = date.num()
stoc.date.t = date.num()
attr.input = false
endif
field.stoc.date.t:
check.input:
if stoc.date.t > date.num() then
set.input.error("tdinv62301")
endif
if stoc.date.t <= archive.date then
set.input.error("tdinv62302")
endif

|*** choices *******************************************************************

choice.create.job:
before.choice:
if spec.by.item = empty then |tcyesno.yes then
reportgrp = 1
else
reportgrp = 2
endif

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
execute.stock.valuation.for.selected.items()
rprt_close()
else
choice.again()
endif

|*** functions *****************************************************************

functions:

function read.tcmcs096()
{
select tcmcs096.*
from tcmcs096
where tcmcs096._index1 = {"INV",1}
selectdo
archive.date = lval(tcmcs096.koda)
selectempty
archive.date = 0
endselect
}

function execute.stock.valuation.for.selected.items()
{
select tiitm100.*, tiitm001.*, tcmcs023.*
from tiitm100, tiitm001, tcmcs023
|where tiitm100._index1 inrange {:item.f, :cntr.f} |#irfan.o
| and {:item.t, :cntr.t} |#irfan.o
where tiitm100._index1 inrange {:item.f}
and {:item.t}
and tiitm100.item refers to tiitm001.item
and tiitm001.czed = tcyesno.no
and tiitm001.citg inrange :citg.f and :citg.t
and tiitm001.stva = tcstva.stnd.copr
and tiitm001.citg refers to tcmcs023
selectdo
tiitm004.conv = 0 | customized by Sambhaji to calculate

select tiitm004.* from tiitm004 | storage quantity of item
where tiitm004.item = :tiitm001.item
selectdo
if tiitm001.cuni = tiitm001.stgu then
tiitm004.conv = 1
endif
storeqty = tiitm001.stoc / tiitm004.conv
selectempty
storeqty = 0
endselect

if storeqty = 0 then
select tiitm004.* from tiitm004
where tiitm004.citg = :tiitm001.citg
selectdo
if tiitm001.cuni = tiitm001.stgu then
tiitm004.conv = 1
endif
storeqty = tiitm001.stoc / tiitm004.conv
selectempty
storeqty = 0
endselect
endif

if storeqty = 0 then
select tiitm004.* from tiitm004
where tiitm004.basu = :tiitm001.cuni
selectdo
if tiitm001.cuni = tiitm001.stgu then
tiitm004.conv = 1
endif
storeqty = tiitm001.stoc / tiitm004.conv
selectempty
storeqty = 0
endselect
endif | upto this point

if not isspace(tiitm001.cntr) then
item.cntr = tiitm001.reli & " " & tiitm001.cntr
else
item.cntr = tiitm001.reli
endif
display("tiitm001.reli")
determine.item.cost.price.data()
determine.item.stock.valuation.by.warehouse()
if copr.indt = 0 and item.stoc <> 0 then
print.error.message()
endif
endselect
}

|*********************** determine item cost price data ************************

function determine.item.cost.price.data()
{
initialize.cost.price.data()
if (stoc.date.f > tiitm001.ltcp) and (stoc.date.t < tiitm001.ltcp) then
|if tiitm001.ltcp between stoc.date.f and stoc.date.t then
copr.indt = determine.cost.price.introduction.date()
endif
read.cpr300.cost.price.data()
}

function initialize.cost.price.data()
{
item.matc = 0.0
item.oprc = 0.0
copr.indt = tiitm001.ltcp
}

function domain tcdate determine.cost.price.introduction.date()
{
select ticpr300.*
from ticpr300
where ticpr300._index1 = {:tiitm001.reli, :tiitm001.cntr}
|and ticpr300.indt >= :stoc.date.f |#irfan.o
and ticpr300.indt >= :stoc.date.f
order by ticpr300.item desc, ticpr300.cntr desc,
ticpr300.indt desc, ticpr300.cpcp desc
as set with 1 rows
selectdo
return(ticpr300.indt)
endselect
return(0)
}

function read.cpr300.cost.price.data()
{
select ticpr300.*, ticpr010.*
from ticpr300, ticpr010
where ticpr300._index1 = {:tiitm001.reli, :tiitm001.cntr,
:copr.indt}
and ticpr300.cpcp refers to ticpr010
selectdo
if ticpr010.cref = tccref.material or
ticpr010.cref = tccref.chg.material then
item.matc = item.matc + ticpr300.amnt
else
item.oprc = item.oprc + ticpr300.amnt
endif
endselect
}

function determine.item.stock.valuation.by.warehouse()
{
item.stoc = 0
select tdinv001.*, tcmcs003.*
from tdinv001, tcmcs003
where tdinv001._index2 inrange {:tiitm001.reli, :tiitm001.cntr,
:cwar.f}
and {:tiitm001.reli, :tiitm001.cntr,
:cwar.t}
and tdinv001.cwar refers to tcmcs003
selectdo
if determine.stock.level() then
determine.item.stock.level()
endif
if copr.indt <> 0 and report.item.stock() then
determine.item.stock.value()
|print.record(1) |#irfan.o
rprt_send()
endif
endselect
}

function domain tcbool determine.stock.level()
{
| if stoc.date.f < date.num() then |#irfan.o
if stoc.date.t < date.num() then
if stoc.date.f > tdinv001.hstd then |#irfan.o

item.stoc = 0.0
return (false)
else
item.stoc = tdinv001.hstb
return (true)
endif
endif
item.stoc = tdinv001.stoc
return (false)
}

function determine.item.stock.level()
{
select tdinv700.*
from tdinv700
where tdinv700._index1 = {:tdinv001.item, :tdinv001.cntr,
:tdinv001.cwar}
and tdinv700.trdt >= :stoc.date.f
and tdinv700.trdt <= :stoc.date.t |#irfan.o
|and tdinv700.trdt inrange :stoc.date.f and :stoc.date.t
|order by tdinv700.trdt
selectdo
if tdinv700.kost = tckost.stc.correction or
( tdinv700.kost = tckost.stc.transfer and
tdinv700.koor <> tckoor.act.sls and
tdinv700.koor <> tckoor.sma.order ) or
tdinv700.kost = tckost.pur.receipt or
tdinv700.kost = tckost.prd.receipt or
tdinv700.kost = tckost.rpl.receipt then
item.stoc = item.stoc + tdinv700.quan
else
item.stoc = item.stoc - tdinv700.quan
endif
endselect
}

function domain tcbool report.item.stock()
{
if item.stoc = 0 and suppr.zero.stoc = tcyesno.yes then
return (false)
endif
return (true)
}

function determine.item.stock.value()
{
amnt.matc = round(item.matc * item.stoc,2,1)
amnt.oprc = round(item.oprc * item.stoc,2,1)
amnt.copr = amnt.matc + amnt.oprc
}

function print.error.message()
{
error.message = sprintf$(form.text$("tdinv62303"), item.cntr,
stoc.date.f)
|if open.report(2,spool.device,0,language$,3) then |#irfan.so
| print.record(2)
|endif |#irfan.eo
}

|**** end of source ****

|*********************** determine item cost price data ************************

function determine.item.cost.price.data()
{
initialize.cost.price.data()
if (stoc.date.f > tiitm001.ltcp) and (stoc.date.t < tiitm001.ltcp) then
|if tiitm001.ltcp between stoc.date.f and stoc.date.t then
copr.indt = determine.cost.price.introduction.date()
endif
read.cpr300.cost.price.data()
}


Generally stoc.date.f < stoc.date.t
Then following condition will always false and determine.cost.price.introduction.date() function will never execute.

if (stoc.date.f > tiitm001.ltcp) and (stoc.date.t < tiitm001.ltcp) then


It should be as below
if (stoc.date.f < tiitm001.ltcp) and (stoc.date.t > tiitm001.ltcp) then

May be this is not relevant to your original issue.

With the current code you will read only 1 record from ticpr300. As you sort the records in descending order you will get only the highest date.

You have to change your script to read and to process all records between the given dates.

Of course I am assuming everything works fine until it gets to the determine.item.stock.level. So assuming this routine:

function determine.item.stock.level()
{
select tdinv700.*
from tdinv700
where tdinv700._index1 = {:tdinv001.item, :tdinv001.cntr,
:tdinv001.cwar}
and tdinv700.trdt >= :stoc.date.f
and tdinv700.trdt <= :stoc.date.t |#irfan.o
|and tdinv700.trdt inrange :stoc.date.f and :stoc.date.t
|order by tdinv700.trdt
selectdo
if tdinv700.kost = tckost.stc.correction or
( tdinv700.kost = tckost.stc.transfer and
tdinv700.koor <> tckoor.act.sls and
tdinv700.koor <> tckoor.sma.order ) or
tdinv700.kost = tckost.pur.receipt or
tdinv700.kost = tckost.prd.receipt or
tdinv700.kost = tckost.rpl.receipt then
item.stoc = item.stoc + tdinv700.quan
else
item.stoc = item.stoc - tdinv700.quan
endif
endselect
}

is where the issue is. What I recommend is to put the session in debug mode. Watch it step thru the tdinv700 table and see how many records it gets. Then take the select out and create an easy sql filling in the variables with the same information for the test.

select tdinv700.*
from tdinv700
where tdinv700._index1 = {"item123", "123","456"}
and tdinv700.trdt >= date(2010,1,1)
and tdinv700.trdt <= date(2010,1,31)
[\code]
See if you get the same number of records and the same dates. If you are still not getting all the data - remove the dates from the above easy sql query and run again. Post the results of both queries(with and without dates). I know this works and I am leaning towards something in the data.

Below is a sample of one easy sql query have used:
[code=baan]
select tdinv700.orno,
tisfc001.mitm,
tdinv700.pono,
tdinv700.item,
tdinv700.cwar,
ticst001.opno
from tdinv700,
tisfc001,
ticst001
where (tdinv700.cwar = "AXM" or tdinv700.cwar = "AAU" or
tdinv700.cwar = "AAS" or tdinv700.cwar = "AXN" or
tdinv700.cwar = "AAP" or tdinv700.cwar = "AAV" or
tdinv700.cwar = "AXN" or tdinv700.cwar = "AAL") and
tdinv700.kost = tckost.prd.mat.issue and
tdinv700.koor = tckoor.act.sfc and
tdinv700.trdt >= date(2004,08,02) and
tdinv700.trdt <= date(2005,02,01) and
tdinv700.orno = tisfc001.pdno and
ticst001.pdno = tdinv700.orno and
ticst001.pono = tdinv700.pono
group by tdinv700.orno, tdinv700.pono, tdinv700.item, tdinv700.cwar,
tisfc001.mitm, ticst001.opno