Thursday, 28 April 2016

inventoryOnHandUploadKraco on ax 2009

static void _kr_inventoryOnHandUploadKraco(Args _args)
{
  Price price;
  InventJournalCheckPost inventJournalCheckPost;
  ItemId itemId;
  ASCIIIo file;
  Dialog dlg;
  FileNameOpen filename;
  Date conversionDate;
  DialogField dfFileName;
  DialogField dfConversionDate;
  container con;
  int linenum, read, added, skipped, last;
  str 4 slast;
  inventqty qty;
  str 10  whse;
  InventJournalCopy inventJournalCopy;
  real convFactor;

  InventJournalTable InventJournalTable, retInventJournalTable;
  InventJournalTrans InventJournalTrans;
  InventLocationId inventLocationId;
  InventDim InventDim;
  LedgerTrans ledgerTrans;
  InventItemPrice  inventItemPrice;
  InventTable      invent;
  InventTableModule inventMod;
  InventItemLocation inventLoc;
  UnitConvert      unitConv;
  ;
//  changecompany('t1uk')
  {
//     filename = 'C:\\Users\\DDyachenko\\Desktop\\1.txt';
//     conversiondate = today();
     linenum = 0;
     InventJournalTable = null; InventJournalTrans = null;inventDim = null;
     dlg = new Dialog('Open inventory onhand file');
     dfFileName = dlg.addFieldValue(typeid(FileNameOpen),filename);
     dfConversionDate = dlg.addFieldValue(typeid(TransDate),conversiondate, "Conversion date");
     if (!dlg.run())
       throw error('Cancelled by the user');
     ttsbegin;

     filename = dfFileName.value();
     conversionDate = dfConversionDate.value();
     if (conversionDate == datenull())
     {
        throw (error("Conversion date must be populated"));
     }
     file = new ASCIIIO(filename,'rt');
     file.inFieldDelimiter('\t');
     file.inRecordDelimiter('\n');
     info(strfmt('File %1',filename));
     select  inventJournalTable  order by journalid desc where inventjournaltable.journalid  like 'conv*';
     if (inventJournalTAble.RecId)
     {
           last = str2int(substr(inventJournalTable.JournalId,5,strlen(inventJournalTable.JournalId)-4));
           info(strfmt("Previous journal found %1",inventJournalTable.JournalId));

        slast = int2str(last);
        if (strlen(slast)<4)
           slast = strfmt("%1%2",strrep('0',4-strlen(slast)),slast);

        retInventJournaltable = InventJournalTable::find(strfmt("Conv%1rev",slast));
        if (!retInventJournalTable.RecId)
        {
           inventJournalCopy = InventJournalCopy::newStandard(null,inventJournalTable.JournalId);
           inventJournalCopy.krParmReverseSign(true);
           inventJournalCopy.krparmJournalIdTo(strfmt("Conv%1rev",slast));
           inventJournalCopy.run();
           retInventJournalTable = InventJournalTable::find(inventJournalCopy.krParmJournalIdTo());
           info(strfmt('Reversing journal %1 created',retInventJournalTable.JournalId));
           if (inventjournaltable.Posted)
           {
             inventJournalCheckPost = InventJournalCheckPost::newPostJournal(retinventJournalTable);
             inventJournalCheckPost.run();
             info(strfmt("Inventory journal %1 posted",retinventjournalTAble.JournalId));
           }
        }
        else
          info(strfmt("Reversing journal %1 exists",retInventJournaltable.JournalId));

     }
     else
       info('No previous journal found');

     last++;
     slast = int2str(last);
     if (strlen(slast)<4)
        slast = strfmt("%1%2",strrep('0',4-strlen(slast)),slast);
     info(strfmt("New journal %1",strfmt("Conv%1",slast)));
     InventJournalTable.clear();
     InventJournalTable.JournalId = strfmt("Conv%1",slast);
     InventJournalTable.Description = "Inventory on-hand balance";
     InventJournalTable.Reservation = ItemReservation::None;
     InventJournalTable.JournalType = InventJournalType::Movement;
     InventJournalTable.JournalNameId = "IMov";
     InventJournalTable.VoucherDraw = JournalVoucherDraw::Post;
     InventJournalTable.VoucherChange = InventJournalVoucherChange::DateChange;
     InventJournalTable.VoucherSeqId = "Inve_72";
     InventJournalTable.DetailSummary = DetailSummary::Summary;
     InventJournalTable.LedgerAccountIdOffset = "124710";  // JimB  12/23/11  Changed to balance 431001 in GL
     if (inventjournaltable.validateWrite())
        InventJournalTable.insert();
     else
       throw error('script aborted');

     while (file.status() == IO_Status::Ok)
     {
        read++;

        con = file.read();
        if (read == 1)
         continue;
        if (conlen(con) == 0)
          continue;
        if (conlen(con) < 2 )
        {
           warning(strfmt("line: %1, number of columns %2",read, conlen(con)));
           continue;
        }
        itemId = conpeek(con,1);

        IF (itemId == 'A106800'
         || itemId == 'HR9600'
         || itemId == 'HRA9600'
         || itemId == 'HRBJ5400'
         || itemId == 'KGAD1100'
         || itemId == 'KGAD4R00'
         || itemId == 'KGAF400'
         || itemId == 'KGAF1200'
         || itemId == 'KGBKLT00'
         || itemId == 'KGBR9R00'
         || itemId == 'KGCQCB00'
         || itemId == 'KGCQ4G00'
         || itemId == 'KGCQ4T00'
         || itemId == 'KGCQ8T00'
         || itemId == 'KGFB4000'
         || itemId == 'KGMD1T00'
         || itemId == 'KGMM1T00'
         || itemId == 'KGMR3T00'
         || itemId == 'NA400'
         || itemId == 'NA1200'
         || itemId == 'NA1600'
         || itemId == 'RPR2000' )
            itemId = substr(itemId, 1, strlen(itemId) - 2);


        if( !InventTable::find(itemid).RecId)
        {
            warning(strfmt('item %1 not found in the Item Master table',itemid));
            continue;
        }
        whse = 'CWA'; //conpeek(con,2);
        inventlocationid = whse;
/*        switch(whse)
        {
           case 'GHT':
             inventlocationid  = 'GHEA';
             break;
           default:
             inventLocationId = whse;
        }
*/

        select UnitId from inventMod
        where inventMod.ItemId == itemId
           && inventMod.ModuleType == ModuleInventPurchSales::Invent;

        select Factor from unitConv
        where unitConv.FromUnit == 'EA'
           && unitConv.ToUnit == inventMod.UnitId
           && unitConv.ItemId == itemId;

        if ( unitConv.Factor )
            convFactor = unitConv.Factor;
        else
            convFactor = 1;

//        InventDim.clear();
//        inventDim.inventSiteId = InventLocation::find(inventLocationId).InventSiteId;
/*        if ( !inventLocationId )
        {
            warning(strfmt("Location does not exit in %1 for item %2", whse, itemid));
            continue;
        }

        inventDim.InventLocationId = inventLocationId;

        inventDim = inventDim::findOrCreate(inventdim);
*/
        select inventDimId from inventLoc
        where inventLoc.ItemId == itemId
       join InventDim
       where InventDim.wMSLocationId == inventLoc.WMSLocationIdDefaultIssue
          && InventDim.InventLocationId == whse
          && InventDim.InventSiteId == 'KE'
          && InventDim.wMSLocationId like '*P*';

        if ( !InventLoc.inventDimId )
        {
/*           select inventDimId from InventDim
          where InventDim.InventLocationId == whse
//            && InventDim.InventSiteId == 'KE'
             && InventDim.wMSLocationId == '9999999999';
*/
           inventDim.clear();
           inventDim.InventSiteId = InventLocation::find(whse).InventSiteId;
           inventDim.InventLocationId = whse;
           inventDim.wMSLocationId = '9999999999';
           inventDim = InventDim::findOrCreate(inventDim);
        }


        if ( !InventLoc.inventDimId  && !InventDim.inventDimId )
        {
            warning(strfmt("Location does not exist in %1 for item %2", whse, itemid));
            continue;
        }


        qty = conpeek(con,2);
        if (qty ==0)
        {
           warning(strfmt("line %1, item %2 - zero qty",read, itemid));
           continue;
        }
        qty = qty / convFactor;
        // Look up price  JimB  10/26/11

        select Price, PriceUnit, ActivationDate  from inventItemPrice
        order by ActivationDate desc
        where inventItemPrice.ItemId == itemid;

        qty = round(qty, 1.0);
        //price = conpeek(con,4);
        price = inventItemPrice.Price;
        linenum++;

        InventJournalTrans.clear();
        InventJournalTrans.initFromInventJournalTable(InventJournalTable);
        InventJournalTrans.LineNum = linenum;
        InventJournalTrans.TransDate = conversionDate;
        InventJournalTrans.Voucher = "Conversion";
        InventJournalTrans.ItemId = itemid;
        InventJournalTrans.initFromInventTable(InventTable::find(itemid));
        inventJournalTrans.InventDimId = inventDim.inventDimId;

        InventJournalTrans.Qty = qty;
        InventJournalTrans.CostPrice = price;
        inventJournalTrans.CostAmount  = InventJournalTrans.calcCostAmount();
        if (inventJournaltrans.validateWrite())
        {
           InventJournalTrans.insert();
           added++;
        }
        else
          throw error('script aborted');

     }
     if (added >0)
     {

       info(strfmt("New journal %1 created. %2 lines added",inventJournalTable.JournalId, added));
       inventJournaltable.NumOfLines = added;
       inventJournaltable.update();
//       inventJournalCheckPost = InventJournalCheckPost::newPostJournal(inventJournalTable);
//       inventJournalCheckPost.run();
//       info(strfmt("Inventory journal %1 posted",inventjournalTAble.JournalId));

     }
     ttscommit;

  }
}
Posted by at No comments:

inventoryOnHandUpload on ax 2009

static void _kr_inventoryOnHandUpload(Args _args)
{
  Price price;
  InventJournalCheckPost inventJournalCheckPost;
  ItemId itemId;
  ASCIIIo file;
  Dialog dlg;
  FileNameOpen filename;
  Date conversionDate;
  DialogField dfFileName;
  DialogField dfConversionDate;
  container con;
  int linenum, read, added, skipped, last;
  str 4 slast;
  inventqty qty;
  str 10  whse;
  InventJournalCopy inventJournalCopy;

  InventJournalTable InventJournalTable, retInventJournalTable;
  InventJournalTrans InventJournalTrans;
  InventLocationId inventLocationId;
  InventDim InventDim;
  LedgerTrans ledgerTrans;
  ;
//  changecompany('t1uk')
  {
//     filename = 'C:\\Users\\DDyachenko\\Desktop\\1.txt';
//     conversiondate = today();
     linenum = 0;
     InventJournalTable = null; InventJournalTrans = null;inventDim = null;
     dlg = new Dialog('Open inventory onhand file');
     dfFileName = dlg.addFieldValue(typeid(FileNameOpen),filename);
     dfConversionDate = dlg.addFieldValue(typeid(TransDate),conversiondate, "Conversion date");
     if (!dlg.run())
       throw error('Cancelled by the user');
     ttsbegin;

     filename = dfFileName.value();
     conversionDate = dfConversionDate.value();
     if (conversionDate == datenull())
     {
        throw (error("Conversion date must be populated"));
     }
     file = new ASCIIIO(filename,'rt');
     file.inFieldDelimiter('\t');
     file.inRecordDelimiter('\n');
     info(strfmt('File %1',filename));
     select  inventJournalTable  order by journalid desc where inventjournaltable.journalid  like 'conv*';
     if (inventJournalTAble.RecId)
     {
           last = str2int(substr(inventJournalTable.JournalId,5,strlen(inventJournalTable.JournalId)-4));
           info(strfmt("Previous journal found %1",inventJournalTable.JournalId));

        slast = int2str(last);
        if (strlen(slast)<4)
           slast = strfmt("%1%2",strrep('0',4-strlen(slast)),slast);

        retInventJournaltable = InventJournalTable::find(strfmt("Conv%1rev",slast));
        if (!retInventJournalTable.RecId)
        {
           inventJournalCopy = InventJournalCopy::newStandard(null,inventJournalTable.JournalId);
           inventJournalCopy.krParmReverseSign(true);
           inventJournalCopy.krparmJournalIdTo(strfmt("Conv%1rev",slast));
           inventJournalCopy.run();
           retInventJournalTable = InventJournalTable::find(inventJournalCopy.krParmJournalIdTo());
           info(strfmt('Reversing journal %1 created',retInventJournalTable.JournalId));
           if (inventjournaltable.Posted)
           {
             inventJournalCheckPost = InventJournalCheckPost::newPostJournal(retinventJournalTable);
             inventJournalCheckPost.run();
             info(strfmt("Inventory journal %1 posted",retinventjournalTAble.JournalId));
           }
        }
        else
          info(strfmt("Reversing journal %1 exists",retInventJournaltable.JournalId));

     }
     else
       info('No previous journal found');

     last++;
     slast = int2str(last);
     if (strlen(slast)<4)
        slast = strfmt("%1%2",strrep('0',4-strlen(slast)),slast);
     info(strfmt("New journal %1",strfmt("Conv%1",slast)));
     InventJournalTable.clear();
     InventJournalTable.JournalId = strfmt("Conv%1",slast);
     InventJournalTable.Description = "Inventory on-hand balance";
     InventJournalTable.Reservation = ItemReservation::None;
     InventJournalTable.JournalType = InventJournalType::Movement;
     InventJournalTable.JournalNameId = "IMov";
     InventJournalTable.VoucherDraw = JournalVoucherDraw::Post;
     InventJournalTable.VoucherChange = InventJournalVoucherChange::DateChange;
     InventJournalTable.VoucherSeqId = "Inve_72";
     InventJournalTable.DetailSummary = DetailSummary::Summary;
     InventJournalTable.LedgerAccountIdOffset = "129450";
     if (inventjournaltable.validateWrite())
        InventJournalTable.insert();
     else
       throw error('script aborted');

     while (file.status() == IO_Status::Ok)
     {
        read++;

        con = file.read();
        if (read == 1)
         continue;
        if (conlen(con) ==0)
          continue;
        if (conlen(con)<5 )
        {
           warning(strfmt("line: %1, number of columns %2",read, conlen(con)));
           continue;
        }
        itemId = conpeek(con,1);
        if( !InventTable::find(itemid).RecId)
        {
            warning(strfmt('item %1 not found in the Item Master table',itemid));
            continue;
        }
        whse = conpeek(con,2);
        switch(whse)
        {
           case 'GHT':
             inventlocationid  = 'GHEA';
             break;
           default:
             inventLocationId = whse;
        }
        InventDim.clear();
        inventDim.inventSiteId = InventLocation::find(inventLocationId).InventSiteId;
        inventDim.InventLocationId = inventLocationId;
        inventDim = inventDim::findOrCreate(inventdim);
        qty = conpeek(con,3);
        if (qty ==0)
        {
           warning(strfmt("line %1, item %2 - zero qty",read, itemid));
           continue;
        }
        price = conpeek(con,4);

        linenum++;

        InventJournalTrans.clear();
        InventJournalTrans.initFromInventJournalTable(InventJournalTable);
        InventJournalTrans.LineNum = linenum;
        InventJournalTrans.TransDate = conversionDate;
        InventJournalTrans.Voucher = "Conversion";
        InventJournalTrans.ItemId = itemid;
        InventJournalTrans.initFromInventTable(InventTable::find(itemid));
        inventJournalTrans.InventDimId = inventDim.inventDimId;

        InventJournalTrans.Qty = qty;
        InventJournalTrans.CostPrice = price;
        inventJournalTrans.CostAmount  = InventJournalTrans.calcCostAmount();
        if (inventJournaltrans.validateWrite())
        {
           InventJournalTrans.insert();
           added++;
        }
        else
          throw error('script aborted');

     }
     if (added >0)
     {

       info(strfmt("New journal %1 created. %2 lines added",inventJournalTable.JournalId, added));
       inventJournaltable.NumOfLines = added;
       inventJournaltable.update();
       inventJournalCheckPost = InventJournalCheckPost::newPostJournal(inventJournalTable);
       inventJournalCheckPost.run();
       info(strfmt("Inventory journal %1 posted",inventjournalTAble.JournalId));

     }
     ttscommit;

  }
}
Posted by at No comments:

change_invmodelgrp,

static void _kr_change_invmodelgrp(Args _args)
{
   inventModelgroup t;
   ;
   ttsbegin;
   select forupdate t where t.ModelGroupId == 'std cost';
   t.InventModel = // InventModel::StdCost;//
              InventModel::FIFO;
   t.update();
   ttscommit;
   info('done');
}
Posted by at No comments:

RECORD Count on AOT

static void _KR_krac_RecordCount(Args _args)
{

    TreeNode node = TreeNode::findNode('\\Data Dictionary\\TAbles').AOTfirstChild();
    DictTable dt;
   // fieldnum fldid;
    Common t;
    SQLDictionary dict;
    container con;

    int i;
    ;
    info(strfmt("-- %1",curext()));
    while (node!=null)
    {
       t = null;
       print node.AOTname();
       dt = new DictTAble(tablename2id(node.aotname()));
       if (dt == null)
         throw error(strfmt("%1 not found", node.AOTname()));
       if (dt.dataPrCompany())
       {

           t = dt.makeRecord();
           select firstonly t;
           if (t.dataAreaId == 'krac')
           {
              select count(recid) from t;
              if (t.RecId >0)
                info(strfmt("%1 - %2",dt.name(DBBackend::Native), t.RecId));
           }

       }
       node = node.AOTnextSibling();
    }
}
Posted by at No comments:
Subscribe to: Posts (Atom)