problem using view ICLOCI (roto IC0372)

[rireland]

Hi -
I am putting finishing touches on an app that inserts an OE using the COMAPI. All works, except that there are issues with inventory - ACCPAC is not kept current, and an order can reject for insufficient on-hand inventory.
I want to trap that in advance of connecting to ACCPAC, and so tracked down the Location Quantities view, ICILOCI. My plan was to pre-validate the on-hand in ACCPAC prior to building the new OE; I currently validate itemno, custid and ordnumber (in case they try to process the same one twice). I doesn these by means of a simple ADO query - no Views. Now, to validate on-hand quantity by location, I must use the view.
I am looking for advice - and maybe a snippet or two of code. Thanks in advance...
This is what I have:

' *******************************************
Global accSessionMgr As New AccpacSessionMgr
Global accSession As AccpacSession
Global mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Global mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink

Global ICLOCIheader As AccpacCOMAPI.AccpacView
Global ICLOCIheaderFields As AccpacCOMAPI.AccpacViewFields

accSessionMgr.AppID = "AS"
accSessionMgr.AppVersion = "53A"
accSessionMgr.ProgramName = "AS1000"
accSessionMgr.ServerName = ""
accSessionMgr.CreateSession "", 0, accSession
If accSession.CompanyID <> "SNOBAL" Then
MsgBox "wrong database"
End
End If
accSession.GetSignonInfo accSession.UserID, accSession.CompanyID, accSession.CompanyName

Set mDBLinkCmpRW = accSession.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Set mDBLinkSysRW = accSession.OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

mDBLinkCmpRW.OpenView "IC0372", ICLOCIheader
Set ICLOCIheaderFields = ICLOCIheader.Fields

ICLOCQTheader.Init
ICLOCQTheader.Order = 0
ICLOCQTheader.Browse "", True
Do While ICLOCQTheader.Fetch = False
Debug.Print ICLOCQTheaderFields("STOCKUNIT").Name
Loop
' *********************************************

 

[ettienne]

You need to use ICILOC view IC0290.

Dim ICILOC As AccpacCOMAPI.AccpacView
mDBLinkCmpRW.OpenView "IC0290", ICILOC

ICILOC.Init
ICILOC.Order = 0
ICILOC.Fields("ITEMNO").PutWithoutVerification "YOUR ITEM"
ICILOC.Fields("LOCATION").PutWithoutVerification "YOUR LOCATION"
If ICILOC.Read then
Msgbox "Qty on hand: " & ICILOC.Fields("QTYONHAND").Value - ICILOC.Fields("QTYSHNOCST").Value + ICILOC.Fields("QTYRENOCST").Value + ICILOC.Fields("QTYADNOCST").Value
else
Msgbox "Item and location not found"
end if


You can use ADO for this query as well.
You want to do something like:
Select ITEMNO, LOCATION, QTYONHAND, QTYSHNOCST, QTYRENOCST, QTYADNOCST From ICILOC Where ITEMNO = "YOUR ITEM" and LOCATION = "YOUR LOCATION"

"YOUR ITEM" will be the unformatted item number and "YOUR LOCATION" will be the location used for the order.

To get the correct quantity on hand you need to take (QTYONHAND - QTYSHNOCST + QTYRENOCST + QTYADNOCST).

 

[rireland]

absolutely awsome - thank you!
I much prefer the direct approach (ADO), since I understand that and it is faster, but the solution begs the question: How does one know when to employ a view over accessing the database in it's native mode?

I suppose that's rhetorical - I appreciate the solution - many thanks!

 

[ettienne]

If you are just going to query the database then ADO is safe, but if you want to write to the database you should always use the views.