smusba
24th April 2011, 11:04
Dear All,
The below script works perfectly for inventory on hand as on date but shows variance when it is taken after a day of same date.
For Example:
If I take the report as on 17th April evening the inventory will be matching but if I take the report on 18th,19th.......any date it will show variance like negative quantiy that was not appearing on 17th April.
|*******************************************************************************
|* pinv6230 Stock Valuation by Item
|* H.A. Rietveld
|* 13-06-91
|* Version3.2
|*
|*******************************************************************************
|*1000 This program is for stock valuation by item
|*******************************************************************************
declaration:
table ttiitm001 | items
table ttiitm004 | Conversion Factor
table ttdinv000 | parameters inventory control
table ttdinv001 | items by warehouse
table ttdinv700 | stock transactions by item
table tticpr010 | cost pricec omponents
table tticpr300 | standard cost price by item
table ttcmcs003 | warehouses
table ttcmcs023 | item groups
table ttcmcs096 | statussen
|*** prog variables ************************************************************
domain tcdate copr.indt | introd. date for costprice
domain tcdate archive.date | date of archiving
extern domain tcqana storeqty | storage quantity
extern domain tcqiv1 item.stoc | item stock level
extern domain tcamnt item.matc | item material costs
extern domain tcamnt item.oprc | item operation costs
extern domain tcamnt amnt.matc | amount material costs
extern domain tcamnt amnt.oprc | amount operation costs
extern domain tcamnt amnt.copr | amount cost price
extern domain tcmcs.st40 item.cntr | item & cntr
extern domain tctext error.message | Error message
extern domain tcseak seab.f,seab.t,seak.f,seak.t
|*** form variables ************************************************************
extern domain tcitem item.f fixed | from item
extern domain tcitem item.t fixed | up to item
extern domain tccuni cntr.f fixed | from cntr
extern domain tccuni cntr.t fixed | up to cntr
extern domain tccitg citg.f fixed | from citg
extern domain tccitg citg.t fixed | up to citg
extern domain tccwar cwar.f fixed | from cwar
extern domain tccwar cwar.t fixed | up to cwar
extern domain tcdate stoc.date | stock valuation date
extern domain tcyesno suppr.zero.stoc | suppress zero stock y/n
extern domain tcyesno spec.by.item | specify by item y/n
|*** 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:
before.input:
if tdinv000.sths = tcyesno.no then | If no history stock transactions
stoc.date = date.num()
attr.input = false
endif
check.input:
if stoc.date > date.num() then
set.input.error("tdinv62301")
endif
if stoc.date <= archive.date then
set.input.error("tdinv62302")
endif
|*** choices *******************************************************************
choice.create.job:
before.choice:
if spec.by.item = tcyesno.yes then
reportgrp = 1
else
reportgrp = 2
endif
choice.cont.process:
before.choice:
check.all.input()
on.choice:
if open.report(1,"",1,language$,(spec.by.item = tcyesno.yes?1:2)) then
execute.stock.valuation.for.selected.items()
close.reports(2)
else
job.process.error = true
end()
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}
and {:item.t, :cntr.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.seab between :seab.f and :seab.t
and tiitm001.seak between :seak.f and :seak.t
and tiitm001.citg refers to tcmcs023
|order by tiitm001.reli,tiitm001.item
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 < 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
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()
if tiitm004.conv = 0 then
tiitm004.conv = 1
endif
storeqty = item.stoc / tiitm004.conv
print.record(1)
endif
endselect
}
function domain tcbool determine.stock.level()
{
if stoc.date < date.num() then
if stoc.date < 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
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)
if open.report(2,spool.device,0,language$,3) then
print.record(2)
endif
}
|**** end of source ****
The below script works perfectly for inventory on hand as on date but shows variance when it is taken after a day of same date.
For Example:
If I take the report as on 17th April evening the inventory will be matching but if I take the report on 18th,19th.......any date it will show variance like negative quantiy that was not appearing on 17th April.
|*******************************************************************************
|* pinv6230 Stock Valuation by Item
|* H.A. Rietveld
|* 13-06-91
|* Version3.2
|*
|*******************************************************************************
|*1000 This program is for stock valuation by item
|*******************************************************************************
declaration:
table ttiitm001 | items
table ttiitm004 | Conversion Factor
table ttdinv000 | parameters inventory control
table ttdinv001 | items by warehouse
table ttdinv700 | stock transactions by item
table tticpr010 | cost pricec omponents
table tticpr300 | standard cost price by item
table ttcmcs003 | warehouses
table ttcmcs023 | item groups
table ttcmcs096 | statussen
|*** prog variables ************************************************************
domain tcdate copr.indt | introd. date for costprice
domain tcdate archive.date | date of archiving
extern domain tcqana storeqty | storage quantity
extern domain tcqiv1 item.stoc | item stock level
extern domain tcamnt item.matc | item material costs
extern domain tcamnt item.oprc | item operation costs
extern domain tcamnt amnt.matc | amount material costs
extern domain tcamnt amnt.oprc | amount operation costs
extern domain tcamnt amnt.copr | amount cost price
extern domain tcmcs.st40 item.cntr | item & cntr
extern domain tctext error.message | Error message
extern domain tcseak seab.f,seab.t,seak.f,seak.t
|*** form variables ************************************************************
extern domain tcitem item.f fixed | from item
extern domain tcitem item.t fixed | up to item
extern domain tccuni cntr.f fixed | from cntr
extern domain tccuni cntr.t fixed | up to cntr
extern domain tccitg citg.f fixed | from citg
extern domain tccitg citg.t fixed | up to citg
extern domain tccwar cwar.f fixed | from cwar
extern domain tccwar cwar.t fixed | up to cwar
extern domain tcdate stoc.date | stock valuation date
extern domain tcyesno suppr.zero.stoc | suppress zero stock y/n
extern domain tcyesno spec.by.item | specify by item y/n
|*** 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:
before.input:
if tdinv000.sths = tcyesno.no then | If no history stock transactions
stoc.date = date.num()
attr.input = false
endif
check.input:
if stoc.date > date.num() then
set.input.error("tdinv62301")
endif
if stoc.date <= archive.date then
set.input.error("tdinv62302")
endif
|*** choices *******************************************************************
choice.create.job:
before.choice:
if spec.by.item = tcyesno.yes then
reportgrp = 1
else
reportgrp = 2
endif
choice.cont.process:
before.choice:
check.all.input()
on.choice:
if open.report(1,"",1,language$,(spec.by.item = tcyesno.yes?1:2)) then
execute.stock.valuation.for.selected.items()
close.reports(2)
else
job.process.error = true
end()
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}
and {:item.t, :cntr.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.seab between :seab.f and :seab.t
and tiitm001.seak between :seak.f and :seak.t
and tiitm001.citg refers to tcmcs023
|order by tiitm001.reli,tiitm001.item
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 < 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
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()
if tiitm004.conv = 0 then
tiitm004.conv = 1
endif
storeqty = item.stoc / tiitm004.conv
print.record(1)
endif
endselect
}
function domain tcbool determine.stock.level()
{
if stoc.date < date.num() then
if stoc.date < 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
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)
if open.report(2,spool.device,0,language$,3) then
print.record(2)
endif
}
|**** end of source ****