I have written a job that will help to create movement journal and post it.
Try this job
static void MovementJournalImportExcel(Args _args)
{
InventJournalTrans inventJournalTrans;
InventDim inventDim;
InventJournalTable inventJournalTable;
InventJournalCheckPost journalCheckPost;
InventJournalId journalId;
journalTableData journalTabledata;
InventBatch inventBatch;
InventBatch localInventBatch;
NumberSeq numberSeq;
NumberSequenceReference numberSequenceReference;
InventSerial inventSerial;
InventSerial localinventSerial;
int j,countno=0,i,k;
real Scarp;
FilenameOpen filename;
Sysexcelapplication excelapp=sysexcelapplication::construct();
sysexcelworksheet excelworksheet;
sysexcelrange excelrange;
sysexcelcells excelcells;
// comvariant cellvalue=new comvariant();
;
// Creating Journal Header
inventJournaltable.initValue();
inventJournalTable.JournalNameId = 'ERecover';
numberSeq = new NumberSeq();
numberSequenceReference = InventParameters::numRefInventJournalId();
numberseq = NumberSeq::newGetNum(numberSequenceReference);
inventJournalTable.JournalId = numberseq.num();
inventJournalTable.Description = InventJournalName::find(
inventJournalTable.JournalNameId).Description;
inventJournalTable.insert();
excelapp.workbooks().open('C:\\Documents and
Settings\\asrivastava\\Desktop\\ElectronicData_OnlyWstatus_10032007.xls');
excelworksheet=excelapp.worksheets().itemFromNum(1);
excelcells=excelworksheet.cells();
// Creating Unit Numbers
for(i=301;i<=5600;i++)
{
inventBatch.clear();
inventBatch.initValue();
inventBatch.itemId = excelcells.item(i,11).value().bStr();
inventBatch.inventBatchId = excelcells.item(i,1).value().bStr();
localinventBatch = InventBatch::find(inventBatch.inventBatchId,
inventBatch.itemId);
if(!localinventBatch)
{
inventBatch.OakSerialUnit = excelcells.item(i,2).value().bStr();
inventBatch.insert();
}
}
//Creating Appartment Numbers
for(k=1;k<=648;k++)
{
inventSerial.clear();
inventSerial.initValue();
inventSerial.InventSerialId = excelcells.item(k,8).value().bStr();
inventSerial.ItemId = excelcells.item(k,11).value().bStr();
localinventSerial = InventSerial::find(inventSerial.InventSerialId,
inventSerial.ItemId);
if(!localInventSerial)
{
inventSerial.ProdDate = systemDateGet();
inventSerial.insert();
}
}
// Creating Journal Lines
for(j=301;j<=5600;j++)
{
inventJournalTrans.clear();
inventJournalTrans.initValue();
inventJournalTrans.TransDate = systemDateGet();
inventJournalTrans.LedgerAccountIdOffset = "99999";
inventJournalTrans.JournalType = InventJournalType::Movement;
inventJournalTrans.JournalId = inventJournalTable.JournalId;
numberSeq = new NumberSeq();
numberSequenceReference =
InventParameters::numRefInventJournalVoucherId();
numberseq = NumberSeq::newGetNum(numberSequenceReference);
inventJournalTrans.Voucher = numberseq.num();
inventJournalTrans.ItemId = excelcells.item
(j,11).value().bStr();
// defaulting branch and item name
inventJournalTrans.CostAmount = InventTable::find(
inventJournalTrans.ItemId).inventTableModuleInvent().Price;
inventJournalTable = InventJournalTable::find(
inventJournalTrans.JournalId);
inventDim.InventLocationId = excelcells.item
(j,10).value().bStr();
inventDim.inventBatchId = excelcells.item
(j,1).value().bStr();
inventDim.inventSerialId = excelcells.item
(j,8).value().bStr();
inventJournalTrans.InventDimId =
inventDim::findOrCreate(inventDim).inventDimId;
inventJournalTrans.Qty = 1;
inventJournalTrans.AdjustmentNotes = "Initial Data Load";
inventJournalTrans.LineNum = j;
inventJournalTrans.insert();
}
excelapp.workbooks().item(1).saved(true);
excelapp.workbooks().close();
// Posting Journal
journalTableData = JournalTableData::newTable(inventJournalTable);
journalTableData.updateBlock
(JournalBlockLevel::InUse,JournalBlockLevel::None);
if (!infolog.num(Exception::Error))
{
infolog.clear(0);
journalCheckPost =
InventjournalCheckPost::newJournalCheckPost(JournalCheckPostType::Post,InventJournalTable);
journalCheckPost.parmAutoBlock(true);
journalCheckPost.run();
}
}
Regards
Ashish
On 10/7/07, madhubabu_r <[EMAIL PROTECTED]> wrote:
>
> Hi Ashwani,
>
> Thanks for such a detailed information which would be useful to all
> our fellow implementors.
>
> I could successfully upload the opening stock using
> InventJournalTable, InventJournalTrans and InventTrans. I have not
> included InventDim as the DimGroupIds of all the items already exists
> in InventDim table.
>
> I have to include InventTrans as I was getting an error that there
> should be transaction with status "Ordered". I have included
> InventTrans table with status Ordered of all the items.
>
> I have uploaded and then posted the Movement Journal. Now when you see
> the on-hand inventory you will see the items in Ordered as negative
> witht he quantity uploaded. Then I went to InventSum table and changed
> all those negative entries to "0".
>
> I have tested master scheduling and other functionalities. Its working
> fine.
>
> Please reply whether this is ok. As you have used a word
> "malfunctioing" I am worried.
>
> Awaiting your reply.
>
> Thanks and best regards,
>
> Madhu Babu Rapolu
>
> --- In
> Axapta-Knowledge-Village@yahoogroups.com<Axapta-Knowledge-Village%40yahoogroups.com>,
> ASHWANI JAIN
> <[EMAIL PROTECTED]> wrote:
> >
> > Hi Madhu,
> >
> > Uploading the opening stock using manual activity is really a
> tedious job, but yes we can do a partial time saving for the creation
> of journal (Invent journal) and journal lines (Invent journal trans).
> >
> > At the time of creation of record in journal lines (Inventory
> journal Trans), system automatically search for inventdim and if not
> found, then create a new record in InventDim, else allocate the
> available one on the InventJournalTrans. After the creation of new
> record in InventDim, same numbr is allcoated to respective transaction
> based on Dimension combination.
> >
> > Different inventJournalTrans lines can have same inventDim id.
> >
> > So in addition to above two tables, you have to import another third
> table "InventDim".
> >
> > Now the question arises, how to pick / define the correct InventDim
> in the import file in Table InventJournalTrans, as this table does not
> have Dimensions (config, color, size, warehouse, location etc...)
> >
> > Because system allocates, this on the basis of unique combination of
> dimension, so in excel using concatenate formula, create a new column
> and put formula in that to get value. Now there must be unique values
> in this field (refer above bold sentence).
> >
> > Filter out the duplicates, and now you should have single record of
> each type.
> > Take the current data of InventDim table to Excel by copy paste. Do
> same concatenate column in this worksheet also in excel.
> >
> > now using the Vlookup formula of Excel, check in earlier inventdim,
> what is the inventdim of those records. you will find some records /
> rows in excel where you will not get any inventdim.
> >
> > Take those records in new worksheet for import in inventdim. When
> you import these records, system will create the inventdim id for
> those records. Put these new alocated number along with the previous
> that you find in the InventJournalTrans. Now you can import
> InventJournal and InventJournalTran Tables.
> >
> > After importing them, ensure that all lines will be having inventdim
> id and cross validate for the dimensions (config, color, size,
> warehouse, location etc...) in them for 5 - 10 records. If found
> satisfactory, then you just have to click the Post button.
> > System will post the same in inventTrans and inventsum, to update
> the inventory.
> >
> > We should not have to import the records in InventTrans / InventSum,
> because these tables are the heart of Dynamics Ax, which may later on
> result in malfunctioning of the system.
> >
> > This whole lenghty explaination appears to be difficult /
> complicated, but once you do it, you will find it as the easiest and
> quicker exercise.
> >
> > In case you need any further assitance, do write to me.
> >
> > Regards,
> > Ashwani Jain
> >
> >
> > ----- Original Message ----
> > From: nigel_cox2003 <[EMAIL PROTECTED]>
> > To:
> > Axapta-Knowledge-Village@yahoogroups.com<Axapta-Knowledge-Village%40yahoogroups.com>
> > Sent: Wednesday, 26 September, 2007 10:07:34 PM
> > Subject: [Axapta-Knowledge-Village] Re: Excel templates for
> Uploading opening Stock
> >
> > Forgive me, madhubabu_r, if it's just a typo on your part, but you
> > list InventJournalTable and InventTrans as the tables you uploaded.
> > Shouldn't this be InventJournalTable and InventJournalTrans as Sumit
> > Loya listed?
> >
> > --- In Axapta-Knowledge- [EMAIL PROTECTED] ups.com, "Sumit Loya"
> > <loya.sumit@ ...> wrote:
> > >
> > > Hi Madhu,
> > >
> > > Am not exactly sure why this error is coming.
> > > The best way to load opening balances is thru Movement journal only.
> > >
> > > I wud not guess the reason for this error as this error information
> > is not
> > > descriptive enough..
> > >
> > > u can try and to debug and see from where this error is coming and
> > on what
> > > condition.
> > >
> > > Do let me know if u come to know anything on this front.
> > >
> > > One more thing if you are posting Movement journal then u need to
> > have data
> > > in InventJournalTable (Movement journal header) and
> > InventJournalTrans
> > > (Movement journal line). You need not create records in InventSum.
> > It will
> > > be taken care while posting the Movement journal also enure that the
> > > Inventory dimensions id that u will provide in InventJournalTrans
> > exists
> > > before you post the records.
> > >
> > > Regards,
> > > Sumit
> > >
> > > On 9/26/07, madhubabu_r <madhubabu_r@ ...> wrote:
> > > >
> > > > Hi Sumit,
> > > >
> > > > Finally shud i take the following tables in the excel templates:
> > > > InventJournalTable
> > > > InventTrans
> > > > InventSum???
> > > >
> > > > The tables I have uploaded are InventJournalTransa and
> > InventTrans. I
> > > > have managed all the number sequences in excel template itself. I
> > > > could upload the data into the Movement Journal Lines. Movement
> > > > Journal was also posted. I could see the onhand stock. But when I
> > was
> > > > trying to create a Sales order/Purchase Order /any inventory
> > > > journals - just after entering the item and i click save I am
> > getting
> > > > an error message - "Illegal Reference" and I cannot proceed
> > further.
> > > >
> > > > Any answers for this??
> > > >
> > > > Regards,
> > > >
> > > > Madhu
> > > > --- In Axapta-Knowledge- [EMAIL PROTECTED] ups.com<Axapta-Knowledge-
> > Village%40yahoogrou ps.com>,
> > > > "Sumit Loya"
> > > > <loya.sumit@ > wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I wudnt say only InventJournalTrans and InventDim..
> > > > >
> > > > > All item transactions are also mirrored in InventTrans table and
> > > > also
> > > > > the on-hand (stock detalis) are stored in InventSum table
> > (Onhand
> > > > > table).
> > > > >
> > > > > Regards,
> > > > > Sumit
> > > > >
> > > > > On 9/25/07, madhubabu_r <madhubabu_r@ > wrote:
> > > > > > Hi friends,
> > > > > >
> > > > > > Manually entering opening stock in Movement Journal must be
> > the
> > > > most
> > > > > > laborious job with all those Excise related entries also
> > > > included. In
> > > > > > my current project there are about 10000 items as opening
> > stock. I
> > > > > > would like to know which tables I have to consider for excel
> > > > upload of
> > > > > > these opening entries.
> > > > > >
> > > > > > Awaiting your valuable responses friends.
> > > > > >
> > > > > > Thanks and best regards,
> > > > > >
> > > > > > Madhu Babu Rapolu
> > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> > Why delete messages? Unlimited storage is just a click away.
> Go to http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
[Non-text portions of this message have been removed]
