Initial Inventory Data Loading

Tew,



In the training database --> Cost Adjustment --> List tab, I reformatted
the grid so that Column 1 - 4 was PN, Date, Unit Cost, Reason Code. I
then build a 5 line spreadsheet with the same format. I pasted all 5
without any errors.



I used "EOY" as the reason code which is the code for "End of Year
Adjustment" in training.



I am also using Progress 8.03.305H.



Dale

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Tew L. P.
Sent: Friday, January 11, 2008 11:08 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Initial Inventory Data Loading



--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Dale Schuerman" <dale.schuerman@...>
wrote:
>
> This option depends on the number of parts you are loading.
>
>
>
> I load costs by copying the excel data into the list tab of the Cost
> Adjustment screen. As the copy progresses it gets very slow. I
found
> that doing 100 parts at a time then exiting the screen and
restarting
> the screen was worth the trouble.
>
>
>
> I create a Physical Inventory count group.
>
>
>
> I then generated a blank tag for each line in the excel spreadsheet.
>
>
>
> The tag entry screen has a list tab.
>
>
>
> I then formatted the spreadsheet to match the tag entry list
(Including
> adding a column with a tag number).
>
>
>
> I then copied the excel data to the list. As the copy progresses
it
> gets very slow. I found that doing 100 parts at a time then
exiting
> the screen and restarting the screen was worth the trouble.
>
>
>
> This options depends on the number of parts you are loading vs. a
> programming solution.
>
>
>
> This also gives you access to the variance report. To verify your
> costs and quantities where loaded. Anything wrong then can be
fixed.
> When everything is correct then you post the physical inventory.
>
>
>
> Hope this helps
>
>
>
> Dale
>
>
>
> ________________________________
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
> Of Tew L. P.
> Sent: Thursday, January 10, 2008 10:36 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Initial Inventory Data Loading
>
>
>
> Hi all,
>
> Anyone has idea on how to perform initial inventory data loading by
> directly import data into the Vantage database instead of manual
key in
> using QTY Adjustment Cost Adjustment Function?
>
> If can, which tables are required to update?
>
> Thanks.
>
> Tew
> TDS
>
>
>
>
>
######################################################################
##
> Attention:
> This email message is privileged and confidential. If you are not
the
> intended recipient please delete the message and notify the sender.
> Any views or opinions presented are solely those of the author.
>
> This email message has been scanned for Viruses and Content and
cleared
> by MailMarshal SMTP
>
> For more information go to http://www.ramsys.com
<http://www.ramsys.com>
>
######################################################################
##
>
>
> [Non-text portions of this message have been removed]
>

Hi Dale,

Thanks for responding to my question.

I tried to copy and paste 5 rows from Excel with the following
columns : -

Part, Date, Mtl Unit Cost, Reason Code

into the Cost Adjustment List tab.

However, it gave me error saying "A valid Reason Code is required"?

I used "End of Year Adjustment" for the Reason Code, which is the
existing Reason Code in the selection list if I manually key in the
Cost Adjustment entry.

Any idea what could be the cause of this error?

Thanks again in advance for any further helps.

Tew





[Non-text portions of this message have been removed]
Hi all,

Anyone has idea on how to perform initial inventory data loading by
directly import data into the Vantage database instead of manual key in
using QTY Adjustment Cost Adjustment Function?

If can, which tables are required to update?

Thanks.

Tew
TDS
--- In vantage@yahoogroups.com, "Tew L. P." <lptew@...> wrote:
>
> Hi all,
>
> Anyone has idea on how to perform initial inventory data loading by
> directly import data into the Vantage database instead of manual key
in
> using QTY Adjustment Cost Adjustment Function?
>
> If can, which tables are required to update?
>
> Thanks.
>
> Tew
> TDS
>
Is this SQL or Progress? DO you have Service Connect? WHat format of
data can you produce?
--- In vantage@yahoogroups.com, "Bruce R. VanderSchaaf"
<BVanderSchaaf@...> wrote:
>
> --- In vantage@yahoogroups.com, "Tew L. P." <lptew@> wrote:
> >
> > Hi all,
> >
> > Anyone has idea on how to perform initial inventory data loading
by
> > directly import data into the Vantage database instead of manual
key
> in
> > using QTY Adjustment Cost Adjustment Function?
> >
> > If can, which tables are required to update?
> >
> > Thanks.
> >
> > Tew
> > TDS
> >
> Is this SQL or Progress? DO you have Service Connect? WHat format
of
> data can you produce?
>
Hi Bruce,

Great to get your reply.

We are using Progres and do not have Service Connect.

We have the data in Excel file format.

Many thanks.

Tew
> Anyone has idea on how to perform initial inventory data loading by
> directly import data into the Vantage database instead of manual key in
> using QTY Adjustment Cost Adjustment Function?

Do you have Visual Studio 2003?
--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > Anyone has idea on how to perform initial inventory data loading by
> > directly import data into the Vantage database instead of manual
key in
> > using QTY Adjustment Cost Adjustment Function?
>
> Do you have Visual Studio 2003?
>

Yes, we have both Visual Studio 2003 and 2005.

Appreciate your help and enlightenment in this matter.

Thanks.

Tew
TDS
>
> Yes, we have both Visual Studio 2003 and 2005.
>

Cool. You can programmatically create a Vantage session and do the Inv
Adjustment commands or the Adjust Cost Commands. We just did the Adjust
material costs for purchase items and then rolled up everything. We used a
reason code that uses an "implementation only" account number so we could put
a JV in to back out the entry in the period just previous to going live.

To update the quantities, we created a project in VS2K3 and added references
for the Vantage objects used in the code below. The form has a button and this
is the code behind it. We used an ODBC connection to read from Access but you
can do the same for Excel. Everything that's in the form rdr("fieldname")
comes from the ODBC connection.

The call sequence came from turning on logging and doing the commands in the
test database.

Gook luck!

Mark W.

Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button13.Click

Dim rowQty As Epicor.Mfg.BO.InventoryQtyAdjDataSet.InventoryQtyAdjRow
Dim Cmd As New Odbc.OdbcCommand("select * from physical where tagqty >
0 order by part")
Dim ctr, errCnt, intTag As Integer
Dim strPart, errMsg, strMsg As String
Dim rdr As Odbc.OdbcDataReader

Try
IConn.Open()
Cmd.Connection = IConn
rdr = Cmd.ExecuteReader()

' Create Vantage session
objSess = New Epicor.Mfg.Core.Session("manager", "manager",
"AppServerDC://UFMERP:8301", Epicor.Mfg.Core.Session.LicenseType.Default)

' Create Vantage objects...
objPart = New Epicor.Mfg.BO.Part(objSess.ConnectionPool)
objQty = New Epicor.Mfg.BO.InventoryQtyAdj(objSess.ConnectionPool)
dsPart = New Epicor.Mfg.BO.PartDataSet
dsQty = New Epicor.Mfg.BO.InventoryQtyAdjDataSet
Catch ex As Exception
Call MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Error - Setting Up")
Application.Exit()
End Try

errCnt = 0
ctr = 0
Try
While rdr.Read()
strPart = Trim(rdr("partnum"))
intTag = rdr("tag")

' See if part exists
If objPart.PartExists(strPart) Then
dsQty = objQty.GetInventoryQtyAdj(strPart)

' Check to see if it's a kit
objQty.KitPartStatus(strPart, strMsg)
If strMsg = "" Then
dsQty.InventoryQtyAdj.Item(0).BinNum =
Trim(rdr("binnum"))
dsQty.InventoryQtyAdj.Item(0).AdjustQuantity =
CDec(rdr("tagQty"))
dsQty.InventoryQtyAdj.Item(0).ReasonCode = "PHYS"
dsQty.InventoryQtyAdj.Item(0).ReasonCodeDescription =
"Initial Physical"
dsQty.InventoryQtyAdj.Item(0).WhseBinDescription =
Trim(rdr("binDescription"))
dsQty.InventoryQtyAdj.Item(0).RowMod = "U"
' enter reference data here
dsQty.InventoryQtyAdj.Item(0).Reference = "Tag:" &
Format(intTag) & " from:" & Trim(rdr("part"))
Try
objQty.SetInventoryQtyAdj(dsQty)
Catch ex As Exception
Console.WriteLine(Format(intTag) & " " & strPart &
" : " & ex.Message)
End Try
End If
dsQty.Clear()
End If
End While
Catch ex As Exception
Call MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Error in Qty Adj " &
Format(intTag))
End Try

End Sub
This option depends on the number of parts you are loading.



I load costs by copying the excel data into the list tab of the Cost
Adjustment screen. As the copy progresses it gets very slow. I found
that doing 100 parts at a time then exiting the screen and restarting
the screen was worth the trouble.



I create a Physical Inventory count group.



I then generated a blank tag for each line in the excel spreadsheet.



The tag entry screen has a list tab.



I then formatted the spreadsheet to match the tag entry list(Including
adding a column with a tag number).



I then copied the excel data to the list. As the copy progresses it
gets very slow. I found that doing 100 parts at a time then exiting
the screen and restarting the screen was worth the trouble.



This options depends on the number of parts you are loading vs. a
programming solution.



This also gives you access to the variance report. To verify your
costs and quantities where loaded. Anything wrong then can be fixed.
When everything is correct then you post the physical inventory.



Hope this helps



Dale



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Tew L. P.
Sent: Thursday, January 10, 2008 10:36 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Initial Inventory Data Loading



Hi all,

Anyone has idea on how to perform initial inventory data loading by
directly import data into the Vantage database instead of manual key in
using QTY Adjustment Cost Adjustment Function?

If can, which tables are required to update?

Thanks.

Tew
TDS




########################################################################
Attention:
This email message is privileged and confidential. If you are not the
intended recipient please delete the message and notify the sender.
Any views or opinions presented are solely those of the author.

This email message has been scanned for Viruses and Content and cleared
by MailMarshal SMTP

For more information go to http://www.ramsys.com
########################################################################


[Non-text portions of this message have been removed]
--- In vantage@yahoogroups.com, "Dale Schuerman" <dale.schuerman@...>
wrote:
>
> This option depends on the number of parts you are loading.
>
>
>
> I load costs by copying the excel data into the list tab of the Cost
> Adjustment screen. As the copy progresses it gets very slow. I
found
> that doing 100 parts at a time then exiting the screen and
restarting
> the screen was worth the trouble.
>
>
>
> I create a Physical Inventory count group.
>
>
>
> I then generated a blank tag for each line in the excel spreadsheet.
>
>
>
> The tag entry screen has a list tab.
>
>
>
> I then formatted the spreadsheet to match the tag entry list
(Including
> adding a column with a tag number).
>
>
>
> I then copied the excel data to the list. As the copy progresses
it
> gets very slow. I found that doing 100 parts at a time then
exiting
> the screen and restarting the screen was worth the trouble.
>
>
>
> This options depends on the number of parts you are loading vs. a
> programming solution.
>
>
>
> This also gives you access to the variance report. To verify your
> costs and quantities where loaded. Anything wrong then can be
fixed.
> When everything is correct then you post the physical inventory.
>
>
>
> Hope this helps
>
>
>
> Dale
>
>
>
> ________________________________
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of Tew L. P.
> Sent: Thursday, January 10, 2008 10:36 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Initial Inventory Data Loading
>
>
>
> Hi all,
>
> Anyone has idea on how to perform initial inventory data loading by
> directly import data into the Vantage database instead of manual
key in
> using QTY Adjustment Cost Adjustment Function?
>
> If can, which tables are required to update?
>
> Thanks.
>
> Tew
> TDS
>
>
>
>
>
######################################################################
##
> Attention:
> This email message is privileged and confidential. If you are not
the
> intended recipient please delete the message and notify the sender.
> Any views or opinions presented are solely those of the author.
>
> This email message has been scanned for Viruses and Content and
cleared
> by MailMarshal SMTP
>
> For more information go to http://www.ramsys.com
>
######################################################################
##
>
>
> [Non-text portions of this message have been removed]
>

Hi Dale,

Thanks for responding to my question.

I tried to copy and paste 5 rows from Excel with the following
columns : -

Part, Date, Mtl Unit Cost, Reason Code

into the Cost Adjustment List tab.

However, it gave me error saying "A valid Reason Code is required"?

I used "End of Year Adjustment" for the Reason Code, which is the
existing Reason Code in the selection list if I manually key in the
Cost Adjustment entry.

Any idea what could be the cause of this error?

Thanks again in advance for any further helps.

Tew
TDS
--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
Hi Mark,

Thanks for responding to my question.

Your codes looks great. It is really a wonderful example that will
save us lots of time and efforts in programing way. We might try it
if the simple copy and paste suggested by Dale doesn't work out to be
our solution.

Best regards,

Tew (TDS)

>
> >
> > Yes, we have both Visual Studio 2003 and 2005.
> >
>
> Cool. You can programmatically create a Vantage session and do the
Inv
> Adjustment commands or the Adjust Cost Commands. We just did the
Adjust
> material costs for purchase items and then rolled up everything. We
used a
> reason code that uses an "implementation only" account number so we
could put
> a JV in to back out the entry in the period just previous to going
live.
>
> To update the quantities, we created a project in VS2K3 and added
references
> for the Vantage objects used in the code below. The form has a
button and this
> is the code behind it. We used an ODBC connection to read from
Access but you
> can do the same for Excel. Everything that's in the form rdr
("fieldname")
> comes from the ODBC connection.
>
> The call sequence came from turning on logging and doing the
commands in the
> test database.
>
> Gook luck!
>
> Mark W.
>
> Private Sub Button13_Click(ByVal sender As System.Object, ByVal
e As
> System.EventArgs) Handles Button13.Click
>
> Dim rowQty As
Epicor.Mfg.BO.InventoryQtyAdjDataSet.InventoryQtyAdjRow
> Dim Cmd As New Odbc.OdbcCommand("select * from physical
where tagqty >
> 0 order by part")
> Dim ctr, errCnt, intTag As Integer
> Dim strPart, errMsg, strMsg As String
> Dim rdr As Odbc.OdbcDataReader
>
> Try
> IConn.Open()
> Cmd.Connection = IConn
> rdr = Cmd.ExecuteReader()
>
> ' Create Vantage session
> objSess = New Epicor.Mfg.Core.Session
("manager", "manager",
> "AppServerDC://UFMERP:8301",
Epicor.Mfg.Core.Session.LicenseType.Default)
>
> ' Create Vantage objects...
> objPart = New Epicor.Mfg.BO.Part(objSess.ConnectionPool)
> objQty = New Epicor.Mfg.BO.InventoryQtyAdj
(objSess.ConnectionPool)
> dsPart = New Epicor.Mfg.BO.PartDataSet
> dsQty = New Epicor.Mfg.BO.InventoryQtyAdjDataSet
> Catch ex As Exception
> Call MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Error -
Setting Up")
> Application.Exit()
> End Try
>
> errCnt = 0
> ctr = 0
> Try
> While rdr.Read()
> strPart = Trim(rdr("partnum"))
> intTag = rdr("tag")
>
> ' See if part exists
> If objPart.PartExists(strPart) Then
> dsQty = objQty.GetInventoryQtyAdj(strPart)
>
> ' Check to see if it's a kit
> objQty.KitPartStatus(strPart, strMsg)
> If strMsg = "" Then
> dsQty.InventoryQtyAdj.Item(0).BinNum =
> Trim(rdr("binnum"))
> dsQty.InventoryQtyAdj.Item
(0).AdjustQuantity =
> CDec(rdr("tagQty"))
> dsQty.InventoryQtyAdj.Item(0).ReasonCode
= "PHYS"
> dsQty.InventoryQtyAdj.Item
(0).ReasonCodeDescription =
> "Initial Physical"
> dsQty.InventoryQtyAdj.Item
(0).WhseBinDescription =
> Trim(rdr("binDescription"))
> dsQty.InventoryQtyAdj.Item(0).RowMod = "U"
> ' enter reference data here
> dsQty.InventoryQtyAdj.Item(0).Reference
= "Tag:" &
> Format(intTag) & " from:" & Trim(rdr("part"))
> Try
> objQty.SetInventoryQtyAdj(dsQty)
> Catch ex As Exception
> Console.WriteLine(Format(intTag) & " "
& strPart &
> " : " & ex.Message)
> End Try
> End If
> dsQty.Clear()
> End If
> End While
> Catch ex As Exception
> Call MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Error in
Qty Adj " &
> Format(intTag))
> End Try
>
> End Sub
>