D365 Solutions Microsoft Dynamics Solved: October 2015

Saturday 24 October 2015

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]

/****** 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

//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();

}