SQL CODE TO SCHEDULING SQL BACKUP SCHEDULING

SQL CODE TO SCHEDULING SQL BACKUP SCHEDULING

vikas mehta

DECLARE @FOLDERPATH VARCHAR(500)

DECLARE @FOLDERPATHWITHNAME VARCHAR(500)

Declare @NAME Varchar(200)

Declare @DATE Varchar(200)

Declare @TIME Varchar(200)

Declare @TIME1 Varchar(200)

--JUST NEED TO UPDATE BELOW PATH FOR DB BACKUP LOCATION

Set @FOLDERPATH = '\\path\'

select @TIME1 = Convert(time, getdate())

select @DATE = (select convert(varchar,getDate(),112))

select @TIME = ((select Convert(Varchar, DATEPART(mm, @TIME1))) + (Select CONVERT(Varchar, DATEPART(ss, @TIME1))))

Select @NAME = ax + '_' + @DATE --+ @TIME

Set @FOLDERPATHWITHNAME = @FOLDERPATH + @NAME + '.bak'

BACKUP DATABASE [ax] TO DISK = @FOLDERPATHWITHNAME WITH NOFORMAT, NOINIT, NAME = @NAME, SKIP, NOREWIND, NOUNLOAD, STATS = 10

**********script*******************************

USE [msdb]

GO

/****** Object:  Job [Ax Prod Backup Schedule]    Script Date: 6/12/2015 3:17:03 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 6/12/2015 3:17:03 PM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Ax Prod Backup Schedule',

                                @enabled=1,

                                @notify_level_eventlog=0,

                                @notify_level_email=0,

                                @notify_level_netsend=0,

                                @notify_level_page=0,

                                @delete_level=0,

                                @description=N'No description available.',

                                @category_name=N'[Uncategorized (Local)]',

                                @owner_login_name=N'vikasvikas\axaosadmin', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Ax prod Step 1]    Script Date: 6/12/2015 3:17:04 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Ax prod Step 1',

                                @step_id=1,

                                @cmdexec_success_code=0,

                                @on_success_action=1,

                                @on_success_step_id=0,

                                @on_fail_action=2,

                                @on_fail_step_id=0,

                                @retry_attempts=0,

                                @retry_interval=0,

                                @os_run_priority=0, @subsystem=N'TSQL',

                                @command=N'DECLARE @FOLDERPATH VARCHAR(500)

DECLARE @FOLDERPATHWITHNAME VARCHAR(500)

Declare @NAME Varchar(200)

Declare @DATE Varchar(200)

Declare @TIME Varchar(200)

Declare @TIME1 Varchar(200)

--JUST NEED TO UPDATE BELOW PATH FOR DB BACKUP LOCATION

Set @FOLDERPATH = ''\\sql bkup\''

select @TIME1 = Convert(time, getdate())

select @DATE = (select convert(varchar,getDate(),112))

select @TIME = ((select Convert(Varchar, DATEPART(mm, @TIME1))) + (Select CONVERT(Varchar, DATEPART(ss, @TIME1))))

Select @NAME = ''MicrosoftDynamicsAX'' + ''_'' + @DATE --+ @TIME

Set @FOLDERPATHWITHNAME = @FOLDERPATH + @NAME + ''.bak''

BACKUP DATABASE [MicrosoftDynamicsAX] TO DISK = @FOLDERPATHWITHNAME WITH NOFORMAT, NOINIT, NAME = @NAME, SKIP, NOREWIND, NOUNLOAD, STATS = 10

',

                                @database_name=N'master',

                                @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'AX 2012 Prod Job Schedule',

                                @enabled=1,

                                @freq_type=4,

                                @freq_interval=1,

                                @freq_subday_type=1,

                                @freq_subday_interval=0,

                                @freq_relative_interval=0,

                                @freq_recurrence_factor=0,

                                @active_start_date=20150421,

                                @active_end_date=99991231,

                                @active_start_time=235900,

                                @active_end_time=235959,

                                @schedule_uid=N'cddf7f74-3c12-414c-ab66-c12b7bc4628f'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

AX 2012 SALES TRADE AGGREMENTS UPLOAD FROM EXCEL X++ CODE

AX 2012 SALES TRADE AGGREMENTS UPLOAD FROM EXCEL X++ CODE

        class VIK_SalesMaster_ExcelImport extends RunBaseBatch

        {

             DialogField                         dialogfile;

             FilenameOpen                        fileName;

             localmacro.CurrentList

              fileName

            endmacro

        }

     

       dialog

        protected Object dialog()

        {

            DialogRunbase  _dialog = super();

       

            _dialog.caption("Sales : Trade Aggrement Upload");

            dialogfile = _dialog.addField("FilenameOpen","Enter file path : ");

            dialogfile.value(fileName);

       

            return _dialog;

        }

     

       getFromDialog

        public boolean getFromDialog()

        {

            ;

            fileName = dialogfile.value();

            return true;

        }

     

       run

        public void run()

        {

            SysExcelApplication excel;

            SysExcelWorkbooks workbooks;

            SysExcelWorkbook workbook;

            SysExcelWorksheets worksheets;

            SysExcelWorksheet worksheet;

            SysExcelCells cells;

            COMVariantType type;

            int row =1;

            int _noofprints;

            CustName name;

            int i,j;

            //sales price upload

            str queryPricingMaster,itemId,existingItemIdsList,tempValue;

            ResultSet resultSet;

            InventTable inventTable;

            AifEntityKeyList                        keys;

            DD_HI_RRP_Staging                       RRPStaging;

            PricePriceDiscJournalService            PriceDiscSvc;

            PricePriceDiscJournal                   PriceDiscJour;

            PricePriceDiscJournal_PriceDiscAdmTrans PriceDiscJourAdmTrans;

            PricePriceDiscJournal_InventDim         PriceDiscJourDim;

            PriceDiscAdmTrans   _PriceDiscAdmTrans;

            //vikas

            PriceDiscAdmTable                   priceDiscAdmTable;

            PriceDiscAdmTrans                   PriceDiscAdmTrans;

            PriceDiscAdmName                    PriceDiscAdmName;

            PriceDiscJournalName                pricediscjournalName;

            NumberSeq journalNum;

            //sales price upload//

            str _relation,_accountcode,_accountselection,_itemcode,_temrelation,_unit,_curcode;

            real _from,_amtincur;

            date _todate,_fromdate;

            PriceType _pricetype;

            itemId tempItemId;

            AviFiles

            SysOperationProgress progress1 = new SysOperationProgress();

            ;

       

        //excel

        define.filename(fileName)

        excel = SysExcelApplication::construct();

        workbooks = excel.workbooks();

        try

        {

        workbooks.open(filename);

        }

        catch (Exception::Error)

        {

        throw error("File cannot be opened");

        }

       

       

                workbook = workbooks.item(1);

                worksheets = workbook.worksheets();

                worksheet = worksheets.itemFromNum(1);

                cells = worksheet.cells();

                type = cells.item(row+1, 1).value().variantType();

       

            //progress1.setCaption("Updation of pricing master is in progress…");

            //progress1.setAnimation(AviUpdate);

       

       

       

         ttsBegin;

       

            Select firstOnly  PriceDiscAdmName;

            priceDiscAdmTable.clear();

            priceDiscAdmTable.initFromPriceDiscAdmName(PriceDiscAdmName);

            journalNum      = NumberSeq::newGetNum(SalesParameters::numRefPriceDiscJournalNum());

            pricediscjournalName        =   journalNum.num();

            priceDiscAdmTable.JournalNum = pricediscjournalName;

            priceDiscAdmTable.insert();

       

           while (type != COMVariantType::VT_EMPTY)

        {

            row++;

            tempItemId  = cells.item(Row,5).value().bStr();

                // Set PriceDiscJourAdmTrans

                  while select forUpdate * from inventTable where inventTable.ItemId == tempItemId

                {

       

                   // progress1.setText(strfmt("Item ID  %1", tempItemId));

                    PriceDiscAdmTrans.clear();

                    PriceDiscAdmTrans.JournalNum        = pricediscjournalName;

                    PriceDiscAdmTrans.relation          = real2int(cells.item(Row,1).value().double()) ;//relation

                    PriceDiscAdmTrans.InventDimId       = "Allblank";

                    PriceDiscAdmTrans.DisregardLeadTime = NoYes::Yes;

                    PriceDiscAdmTrans.AccountCode       = real2int(cells.item(Row,2).value().double()) ; //account code

                    PriceDiscAdmTrans.AccountRelation   = cells.item(Row,3).value().bStr();     //account relation

                    PriceDiscAdmTrans.ItemCode          = real2int(cells.item(Row,4).value().double());

                    PriceDiscAdmTrans.ItemRelation      = cells.item(Row,5).value().bStr();         //item realtaion item id

                    PriceDiscAdmTrans.QuantityAmountFrom= cells.item(Row,6).value().double();        //from quantity

                    PriceDiscAdmTrans.UnitId            = cells.item(Row,7).value().bStr();            //unit id

                    PriceDiscAdmTrans.Amount            = cells.item(Row,8).value().double();         //Amount in Curr

                    PriceDiscAdmTrans.Currency          = cells.item(Row,9).value().bStr();        //currency code

                    PriceDiscAdmTrans.FromDate          = cells.item(Row,10).value().date();        //from date

                    PriceDiscAdmTrans.ToDate            = cells.item(Row,11).value().date();         //todate

                    PriceDiscAdmTrans.insert();

                    itemId=inventTable.ItemId;

       

                }

                 type = cells.item(row+1, 1).value().variantType();

        }

            ttsCommit;

        excel.quit();

        }

     

       main

        public static void main(Args _args)

        {

            VIK_SalesMaster_ExcelImport    VIK_SalesMaster_ExcelImport = new VIK_SalesMaster_ExcelImport();

       

       

             if(VIK_SalesMaster_ExcelImport.prompt())

                VIK_SalesMaster_ExcelImport.run();

        }

      

DYNAMICS AX 2012 : ITEM CATEGORY UPDATE FOR ITEMS

ITEM CATEGORY UPDATE FOR ITEMS

   

    METHODS

       classDeclaration

        class Vik_ItemCategory_ExcelImport exts RunBaseBatch

        {

             DialogField                         dialogfile;

             FilenameOpen                        fileName;

             localmacro.CurrentList

              fileName

            macro

        }

     

       createcategory

        Public void createcategory(Itemid _itemno,EcoResCategoryCommodityCode _CategoryName,EcoResCategoryHierarchyName _name)

        {

       

                 //vikas

            EcoResDistinctProduct  ecoResDistinctProduct;

            EcoResProduct           ecoResProduct;

            EcoResProductTranslation ecoResProductTranslation;

            EcoResProductCategory   ecoResProductCategory;

            EcoResCategory          ecoResCategory;

            EcoResCategoryHierarchy ecoResCategoryHierarchy;

            ecoResCategoryTranslation   ecoResCategoryTranslation;

            //vikas

             //Create Producr Category (Hierarchy)

            select ecoResDistinctProduct where ecoResDistinctProduct.DisplayProductNumber == _itemno;

            Select firstOnly ecoResCategoryHierarchy where ecoResCategoryHierarchy.Name == _name;

            Select firstonly ecoResProductCategory where ecoResProductCategory.Product == ecoResDistinctProduct.RecId;

            if (!ecoResProductCategory)

            {

                ttsBegin;

                ecoResProductCategory.clear();

                ecoResProductCategory.initValue();

                ecoResProductCategory.CategoryHierarchy         = ecoResCategoryHierarchy.RecId;

                select * from ecoResCategory where ecoResCategory.Name == _CategoryName;

                ecoResProductCategory.Category                  = ecoResCategory.RecId;

                //ecoResProductCategory.category = ecoResCategoryTranslation.RecId;

                ecoResProductCategory.Product                   = ecoResDistinctProduct.RecId;

                //ecoResProductCategory.VIK_EcoResProductType     = VIK_EcoResProductType::findByProductType(_productType).RecId;

                //ecoResProductCategory.VIK_EcoResSubCategory     = VIK_EcoResSubCategory::findBySubCategory(_subCategory).RecId;

                ecoResProductCategory.insert();

                ttsCommit;

                info(strFmt("category for item %1 is created",_itemno));

            }

       

        }

     

       dialog

        protected Object dialog()

        {

            DialogRunbase  _dialog = super();

       

            _dialog.caption("Product Upload");

            dialogfile = _dialog.addField("FilenameOpen","Enter file path : ");

            dialogfile.value(fileName);

       

            return _dialog;

        }

     

       getFromDialog

        public boolean getFromDialog()

        {

            ;

            fileName = dialogfile.value();

            return true;

        }

     

       run

        public void run()

        {

            SysExcelApplication excel;

            SysExcelWorkbooks workbooks;

            SysExcelWorkbook workbook;

            SysExcelWorksheets worksheets;

            SysExcelWorksheet worksheet;

            SysExcelCells cells;

            COMVariantType type;

            int row =1;

            int _noofprints;

            EcoResCategoryCommodityCode _CatName;

            EcoResCategoryHierarchyName _Hiername;

            CustName name;

            int i,j;

            //sales price upload

            str queryPricingMaster,itemId,existingItemIdsList,tempValue;

            ResultSet resultSet;

            InventTable inventTable;

       

            //vikas

            EcoResProduct           ecoResProduct;

            EcoResProductTranslation ecoResProductTranslation;

            EcoResProductCategory   ecoResProductCategory;

            EcoResCategory          ecoResCategory;

            EcoResCategoryHierarchy checkEcoResCatH;

            EcoResCategory          checkEcoResCat;

               //checkEcoResCatH                     = EcoResCategoryHierarchy::findByName(_Hiername);

            //checkEcoResCat                      = EcoResCategory::findByName(_CatName,checkEcoResCatH.RecId);

            //vikas

       

            //vikas

            //sales price upload//

            str _relation,_accountcode,_accountselection,_itemcode,_temrelation,_unit,_curcode;

            real _from,_amtincur;

            date _todate,_fromdate;

            PriceType _pricetype;

       

            itemId tempItemId;

            str stritemid;

            AviFiles

            SysOperationProgress progress1 = new SysOperationProgress();

            ;

        

        //excel

        define.filename(fileName)

        excel = SysExcelApplication::construct();

        workbooks = excel.workbooks();

        try

        {

        workbooks.open(fileName);

        }

        catch (Exception::Error)

        {

        throw error("File cannot be opened");

        }

       

       

                workbook = workbooks.item(1);

                worksheets = workbook.worksheets();

                worksheet = worksheets.itemFromNum(1);

                cells = worksheet.cells();

                type = cells.item(row+1, 1).value().variantType();

       

         ttsBegin;

       

           while (type != COMVariantType::VT_EMPTY)

        {

            row++;

            tempItemId  = cells.item(Row,1).value().bStr();

           // stritemid = cells.item(Row,1).value().bStr();

            select inventTable where inventTable.ItemId == tempItemId;

            if(inventTable)

            {

            select ecoResProduct where ecoResProduct.DisplayProductNumber == inventTable.ItemId;

            select ecoResProductCategory    where ecoResProductCategory.product == ecoResProduct.RecId;

       

            _CatName  = cells.item(Row,2).value().bStr();

            _Hiername = cells.item(Row,3).value().bStr();

       

            checkEcoResCatH                     = EcoResCategoryHierarchy::findByName(_Hiername);

            checkEcoResCat                      = EcoResCategory::findByName(_CatName,checkEcoResCatH.RecId);

       

            if(!ecoResProductCategory && ecoResProduct && checkEcoResCatH && checkEcoResCat)

                {

                    this.createcategory(tempItemId,_CatName,_Hiername);

                }

            else if (ecoResProductCategory && ecoResProduct && checkEcoResCatH && checkEcoResCat)

                {

                    this.updatecategory(tempItemId,_CatName,_Hiername);

                }

            }

                 type = cells.item(row+1, 1).value().variantType();

        }

            ttsCommit;

        excel.quit();

        }

     

       updatecategory

        Public void updatecategory(Itemid _itemno,EcoResCategoryCommodityCode _CategoryName,EcoResCategoryHierarchyName _name)

        {

       

                 //vikas

            EcoResDistinctProduct  ecoResDistinctProduct;

            EcoResProduct           ecoResProduct;

            EcoResProductTranslation ecoResProductTranslation;

            EcoResProductCategory   ecoResProductCategory;

            EcoResCategory          ecoResCategory;

            EcoResCategoryHierarchy ecoResCategoryHierarchy;

            ecoResCategoryTranslation   ecoResCategoryTranslation;

            //vikas

             //Create Producr Category (Hierarchy)

            select ecoResDistinctProduct where ecoResDistinctProduct.DisplayProductNumber == _itemno;

            Select firstOnly ecoResCategoryHierarchy where ecoResCategoryHierarchy.Name == _name;

       

            while select forupdate ecoResProductCategory where ecoResProductCategory.Product == ecoResDistinctProduct.RecId

            if(ecoResProductCategory)

            {

                ttsBegin;

                //ecoResProductCategory.clear();

                //ecoResProductCategory.initValue();

                ecoResProductCategory.CategoryHierarchy         = ecoResCategoryHierarchy.RecId;

                select * from ecoResCategory where ecoResCategory.Name == _CategoryName;

                ecoResProductCategory.Category                  = ecoResCategory.RecId;

                //ecoResProductCategory.category = ecoResCategoryTranslation.RecId;

                ecoResProductCategory.Product                   = ecoResDistinctProduct.RecId;

                //ecoResProductCategory.VIK_EcoResProductType     = VIK_EcoResProductType::findByProductType(_productType).RecId;

                //ecoResProductCategory.VIK_EcoResSubCategory     = VIK_EcoResSubCategory::findBySubCategory(_subCategory).RecId;

                ecoResProductCategory.update();

                ttsCommit;

                info(strFmt("category for item %1 is updated",_itemno));

            }

       

        }

     

       main

        public static void main(Args _args)

        {

            Vik_ItemCategory_ExcelImport    Vik_ItemCategory_ExcelImport = new Vik_ItemCategory_ExcelImport();

       

            if(curext() == "AHD")

            {

             if(Vik_ItemCategory_ExcelImport.prompt())

                Vik_ItemCategory_ExcelImport.run();

            }

        }

     

    METHODS

  CLASS

***Element: 

AX 2012 MAP X++ FOR EXISTING RECORDS MAP INSERT MAP CHECK MAP LOOKUP

AX 2012 MAP X++ FOR EXISTING RECORDS

MAP for Exist in excel upload

public void run()

{

    SysExcelApplication     excel;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    SysExcelCell            cell;

    SysExcelStyles          styles;

    SysExcelStyle           estyle;

    SysExcelFont            font;

    int                     row = 1;

    //vikas

    ProdTable               prodTable,prodTableRef,prodtabledate;

    InventDim               inventDim;

    int                     total1,total2,total3,total4,total5,total6;

    SysExcelWorksheetHelper worksheetHelper;

    SysExcelRange           range;

    ItemId                  itemId;

    InventOnhand            inventonHand;

    inventDim               inventDimRef;

    InventDimParm           inventDimParm;

    WrkCtrCapRes            WrkCtrCapRes1,WrkCtrCapRes2,WrkCtrCapRes3;

    WrkCtrTable             wrkCtrTable1,wrkCtrTable2,wrkCtrTable3;

    int                     x,y,z,j,k,l,fordays,mapi,mapint;

    WrkCtrIdBase            machine;

    SchedFromDate           mindate,mindateLess,mapdatefind,fordate;

    SchedToDate             maxdate1;

    date               strdate;

    str                 dateinstring;

    TransDate               transdate;

    Map                     map = new Map(Types::String,Types::Integer);

    Map                     mapintkey = new Map(Types::Integer,Types::String);

    InventTrans            inventTransTable;

   

    int                     days;

    str itemOrginStatus;

    excel = SysExcelApplication::construct();

    workbooks = excel.workbooks();

    workbook = workbooks.add();

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

    styles = workbook.styles();

    estyle  = styles.add("Header");

    font = estyle.font();

    font.bold(true);

    cell = cells.item(1, 1);

    cell.font().bold(true);

    cell.value('MONTH PRODUCTION PLANNING');

    //ITEM    PLAN    OPN STK    BAL TO BE PRODUCED    MTD    Operation    M/C

    cell = cells.item(3, 1);

    cell.value('SIZE');

    cell = cells.item(3, 2);

    cell.value('TYPE');

    cell = cells.item(3, 3);

    cell.value('PLAN');

    cell = cells.item(3, 4);

    cell.value('OPN STK');

    cell = cells.item(3, 5);

    cell.value('BAL TO BE PRODUCED');

    cell = cells.item(3, 6);

    cell.value('MTD    Operation');//NO OF

    cell = cells.item(3, 7);

    cell.value('M/C');

    row = 3;

    select maxof(SchedStart) from prodtabledate where prodtabledate.InventRefId == 'P000179';

    maxdate1 = prodtabledate.schedstart;

    prodtabledate.clear();

    select minof(SchedStart) from prodtabledate where prodtabledate.InventRefId == 'P000179';

    mindate = prodtabledate.schedstart;

    mindateLess = mindate -1;

     days = date2num(maxdate1) - date2num(mindate);

     fordays = days+10;

    for(j=10;j<fordays;j++)

    {

        map.insert(mindateLess+1,j);

        mindateLess = mindateLess+1;

    }

    //mapintkey

    for(mapi=10;mapi<fordays;mapi++)

    {

        mapintkey.insert(mapi,mindateLess+1);

        mindateLess = mindateLess+1;

    }

    //mapdate = 2015/9/23  ...9/28/2015

    for(k=10;k<fordays;k++)

    {

        cell = cells.item(3, k);

         if (Mapintkey.exists(k))

        {

            strdate = Mapintkey.lookup(k);

            dateinstring = Mapintkey.lookup(k);

        }

       // strdate = date2str(transdate,321,DateDay::Digits2,DateSeparator::Slash,DateMonth::Digits1or2,dateSeparator::Slash, DateYear::Digits4);

        cell.value(Mapintkey.lookup(dateinstring));

    }

    //map.insert('2', 'ew');

    //info(strFmt("%1",map.lookup(2)));

 while select prodTable join InventDim   where prodTable.ProdId == 'P000179' && prodTable.InventDimId == InventDim.inventDimId

   {

           // itemId = prodTable.ItemId;

             row= row+1;

             cell = cells.item(row, 1);

             cell.value(InventDim.InventSizeId);

             cell = cells.item(row, 2);

             cell.value(InventDim.InventColorId);

    while select prodTableRef join inventDimRef where prodTableRef.InventRefId == prodTable.ProdId

       {

            //row = row+1;

            cell = cells.item(row, 3); //plan

            cell.value(prodTableRef.qtysched);

            itemId = prodTable.ItemId;

            InventDimParm.initFromInventDim(inventDimRef);

            inventonHand = inventonHand::newParameters(itemId,inventDimRef,InventDimParm);

            cell = cells.item(row, 4); //

            cell.value(inventonHand.availPhysical());

            itemId = prodTable.ItemId;

            cell = cells.item(row, 5); //plan

            cell.value(prodTableRef.qtysched - inventonHand.availPhysical());

           //cell = cells.item(row, 6); //plan

            //cell.value(prodTableRef.qtysched - inventonHand.availPhysical());

            cell = cells.item(row, 7); //pool

            cell.value(prodTableRef.ProdPoolId);

            while select WrkCtrCapRes1 order by WrkCtrId,transdate where WrkCtrCapRes1.RefId == prodTableRef.ProdId && WrkCtrCapRes1.RefType == 1 //&& WrkCtrCapRes1.WrkCtrId == "BREHM-1"

           {   x = 8;y =10;//z=2;

               wrkCtrTable1.clear();

               select wrkCtrTable1 where wrkCtrTable1.Name == WrkCtrCapRes1.WrkCtrId;

               machine = WrkCtrCapRes1.WrkCtrId;

               cell = cells.item(row,8 ); //machine

               cell.value(machine);

                cell = cells.item(row, 9); //actual

                cell.value('Actual');

                row = row +1;

                cell = cells.item(row, 9); //plan

                cell.value('Plan');

                 if (Map.exists(WrkCtrCapRes1.TransDate))

                {

                    mapint = Map.lookup(WrkCtrCapRes1.TransDate);

                }

                cell = cells.item(row,mapint);

                cell.value(WrkCtrCapRes1.WrkCtrHours()*wrkCtrTable1.capacity);

            }

       }

   

        }

    worksheet.columns().autoFit();

    excel.visible(true);

}

INVENTTABLE AND ECORESPRODUCT RELATION JOIN QUERY LINK ECORESCATEGORY

INVENTTABLE AND ECORESPRODUCT RELATION 

JOIN QUERY LINK ECORESCATEGORY

InventTable and ECORESPRODUCT

select ecorescategorygroc where ecorescategorygroc.Name == catgnames;

    while select * from inventTable join product where inventTable.Product == product.RecId

                    join    productCat  where productCat.Product  == product.RecId

                    join    category    where   category.RecId      == productCat.Category && category.Name == catgnames//category.ParentCategory == ecorescategorygroc.RecId// && category.Name == catgnames

                    join catHierarchy   where   catHierarchy.RecId  == category.CategoryHierarchy//

                             && inventTable.itemid like

PURCHASE ORDER CATEGORY PRODUCT RECEIPT CALLER CLASS

Purchase category

1.       PO invoice and product receipt

Changes have been done in the forms clicked ok methods of post and close ok

Blow is code for product receipt

// po category start

    VendPackingSlipJour         vendPackingSlipJour;

    Vik_PurchaseOrderCategory   poCategory;

    int                         daydiff,prodreceiptcount;

    PurchTable                  purchTableLocal;

    if(curext() == 'SDS')

    {

        if( classidget(purchFormLetter) == classnum(PurchFormLetter_PackingSlip)) //&& (purchTable.Vik_PurchaseCategory != Vik_PurchaseCategory::None))

        {

            select purchTableLocal where purchTableLocal.PurchId == purchFormLetter.purchTable().PurchId;

            select poCategory where poCategory.Vik_PurchaseCategory == purchTableLocal.Vik_PurchaseCategory;

            select count(RecId) from vendPackingSlipJour where vendPackingSlipJour.PurchId == purchTableLocal.PurchId;

            prodreceiptcount = int642int(vendPackingSlipJour.RecId);

            if(prodreceiptcount > poCategory.DeliveryTimes)

                throw error(strFmt(" The Product receipt for Purchase Order %1 has exceeded for the category %2",purchTableLocal.PurchId,purchTableLocal.Vik_PurchaseCategory));

        }

    }

 //   po category end

Below is code for Invoice

void clicked()

{

    // po category start

    Vik_PurchaseOrderCategory   poCategory;

    int                         daydiff,prodreceiptcount;

    PurchTable                  purchTableLocal;

    if(curext() == 'SDS')

    {

        select purchTableLocal where purchTableLocal.PurchId == purchFormLetter.purchTable().PurchId;

        if( classidget(purchFormLetter) == classnum(PurchFormLetter_Invoice) && (purchTable.Vik_PurchaseCategory != Vik_PurchaseCategory::None))

        {

           

            select poCategory where poCategory.Vik_PurchaseCategory == purchTableLocal.Vik_PurchaseCategory;

          //  daydiff = purchTableLocal.createdDateTime - DateTimeUtil::getSystemDateTime();

            daydiff = DateTimeutil::getDifference(DateTimeUtil::getSystemDateTime(), purchtablelocal.createddatetime) / 86400;

            if(daydiff > poCategory.ExpireDays)

                throw error(strFmt(" The Purchase Order %1 has exceeded the expire days for the category %2",purchTableLocal.PurchId,purchTableLocal.Vik_PurchaseCategory));

        }

    }

 //   po category end

    // <GEERU>

    if (SysCountryRegionCode::isLegalEntityInCountryRegion([ #isoRU ]) && ! purchFormLetter.checkBeforePost())

    {

        return;

    }

    // </GEERU>

    super();

}