//Process Started
select firstOnly agreementClassification
where agreementClassification.AgreementRelationType == tableNum(SalesAgreementHeader);
row =1;
worksheetSysHeader = worksheets.itemFromName(worksheetHeader);
cells = worksheetSysHeader.cells();
do
{
row++;
salesAgreementHeader = SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());
if(!salesAgreementHeader)
{
salesAgreementHeader.clear();
salesAgreementHeader.initValue();
salesAgreementHeader.SalesNumberSequence = cells.item(row, 1).value().bStr();
salesAgreementHeader.CustAccount = cells.item(row, 2).value().bStr();
salesAgreementHeader.initFromCustTable();
salesAgreementHeader.AgreementClassification = agreementClassification.RecId;
salesAgreementHeader.Currency = cells.item(row, 9).value().bStr();
salesAgreementHeader.AgreementState = str2enum(agreementState, cells.item(row, 10).value().bStr());
salesAgreementHeader.DefaultAgreementLineEffectiveDate = str2Date(this.COMVariant2Str(cells.item(row, 5).value(), 0),213);
salesAgreementHeader.DefaultAgreementLineExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 6).value(), 0),213);
if (salesAgreementHeader.validateWrite())
{
salesAgreementHeader.insert();
}
}
else
{
salesAgreementHeader.selectForUpdate(true);
salesAgreementHeader.Currency = cells.item(row, 9).value().bStr();
salesAgreementHeader.AgreementState = str2enum(agreementState, cells.item(row, 10).value().bStr());
salesAgreementHeader.DefaultAgreementLineEffectiveDate = str2Date(this.COMVariant2Str(cells.item(row, 5).value(), 0),213);
salesAgreementHeader.DefaultAgreementLineExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 6).value(), 0),213);
if (salesAgreementHeader.validateWrite())
{
salesAgreementHeader.update();
}
}
if(salesAgreementHeader)
{
if(!conFind(salesAgreementIds, salesAgreementHeader.SalesNumberSequence))
{
salesAgreementIds += salesAgreementHeader.SalesNumberSequence;
}
salesAgreementHeaderDefault = SalesAgreementHeaderDefault::findSalesAgreementHeader(salesAgreementHeader.RecId);
if(salesAgreementHeaderDefault)
{
salesAgreementHeaderDefault.selectForUpdate(true);
salesAgreementHeaderDefault.CustomerRequisitionNumber = cells.item(row, 12).value().bStr();
salesAgreementHeaderDefault.update();
}
else
{
salesAgreementHeaderDefault.clear();
salesAgreementHeaderDefault.initValue();
salesAgreementHeaderDefault.SalesAgreementHeader = salesAgreementHeader.RecId;
salesAgreementHeaderDefault.CustomerRequisitionNumber = cells.item(row, 12).value().bStr();
salesAgreementHeaderDefault.insert();
}
agreementHeaderDefault = AgreementHeaderDefault::findAgreementHeader(salesAgreementHeader.RecId);
if(agreementHeaderDefault)
{
agreementHeaderDefault.selectForUpdate(true);
agreementHeaderDefault.DeliveryPostalAddress = CustTable::find(cells.item(row, 2).value().bStr()).postalAddress().RecId;
agreementHeaderDefault.DeliveryName = CustTable::find(cells.item(row, 2).value().bStr()).name();
agreementHeaderDefault.ContactPerson = cells.item(row, 4).value().bStr();
agreementHeaderDefault.ExternalReference = cells.item(row, 11).value().bStr();
agreementHeaderDefault.DeliveryTerm = cells.item(row, 8).value().bStr();
agreementHeaderDefault.PaymentTerms = cells.item(row, 7).value().bStr();
agreementHeaderDefault.ContactPersonDataAreaId = curext();
agreementHeaderDefault.PaymentTermsDataAreaId = curext();
agreementHeaderDefault.DeliveryTermDataAreaId = curext();
agreementHeaderDefault.ContactPersonDataAreaId = curext();
agreementHeaderDefault.update();
}
else
{
agreementHeaderDefault.clear();
agreementHeaderDefault.initValue();
agreementHeaderDefault.AgreementHeader = salesAgreementHeader.RecId;
agreementHeaderDefault.ContactPerson = cells.item(row, 4).value().bStr();
agreementHeaderDefault.ExternalReference = cells.item(row, 11).value().bStr();
agreementHeaderDefault.DeliveryTerm = cells.item(row, 8).value().bStr();
agreementHeaderDefault.PaymentTerms = cells.item(row, 7).value().bStr();
agreementHeaderDefault.DeliveryPostalAddress = CustTable::find(cells.item(row, 2).value().bStr()).postalAddress().RecId;
agreementHeaderDefault.DeliveryName = CustTable::find(cells.item(row, 2).value().bStr()).name();
agreementHeaderDefault.ContactPersonDataAreaId = curext();
agreementHeaderDefault.PaymentTermsDataAreaId = curext();
agreementHeaderDefault.DeliveryTermDataAreaId = curext();
agreementHeaderDefault.insert();
}
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
row =1;
cells = null;
worksheetSysHeader = worksheets.itemFromName(worksheetLine);
cells = worksheetSysHeader.cells();
do
{
row++;
salesAgreementHeader = SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());
agreementHeader = AgreementHeader::find(salesAgreementHeader.RecId);
agreementHeaderDefault = AgreementHeaderDefault::findAgreementHeader(agreementHeader.RecId);
salesAgreementHeaderDefault = SalesAgreementHeaderDefault::findSalesAgreementHeader(salesAgreementHeader.RecId);
if(salesAgreementHeader && agreementHeader)
{
select firstonly agreementLineQty
where agreementLineQty.Agreement == agreementHeader.RecId
&& agreementLineQty.ItemId == cells.item(row, 2).value().bStr();
if(!agreementLineQty)
{
agreementLineQty.clear();
agreementLineQty.initValue();
agreementLineQty.initFromAgreementHeader(agreementHeader);
agreementLineQty.Agreement = agreementHeader.RecId;
agreementLineQty.AgreementLineProduct = AgreementLineProduct::Item;
agreementLineQty.AgreementLineType = CommitmentType::ProductQuantity;
agreementLineQty.ItemId = cells.item(row, 2).value().bStr();
agreementLineQty.initFromInventTable();
inventDim = agreementLineQty.inventDim();
switch (agreementLineQty.agreementModuleType())
{
case ModuleSalesPurch::Sales:
inventDim.initFromInventTable(agreementLineQty.inventTable(), InventItemOrderSetupType::Sales, inventDim);
break;
case ModuleSalesPurch::Purch:
inventDim.initFromInventTable(agreementLineQty.inventTable(), InventItemOrderSetupType::Purch, inventDim);
break;
}
inventDim.InventDimId = InventDim::findOrCreate(inventDim).InventDimId;
agreementLineQty.setInventDimId(inventDim.InventDimId);
agreementLineQty.InventDimDataAreaId = curext();
agreementLineQty.ExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 14).value(), 0),213);
agreementLineQty.CommitedQuantity = str2num(this.COMVariant2Str(cells.item(row, 4).value(), 2));
agreementLineQty.ProductUnitOfMeasure = cells.item(row, 5).value().bStr();
agreementLineQty.PricePerUnit = str2num(this.COMVariant2Str(cells.item(row, 6).value(), 2));
agreementLineQty.LineDiscountAmount =str2num(this.COMVariant2Str(cells.item(row, 7).value(), 2));
agreementLineQty.LineDiscountPercent =str2num(this.COMVariant2Str(cells.item(row, 8).value(), 2));
agreementLineQty.LineNumber = AgreementLine::lastLineNum(agreementHeader.RecId) + 1;
if (agreementLineQty.validateWrite())
{
agreementLineQty.insert();
agreementLineQty.salesAgreementHeader().update();
}
}
else
{
agreementLineQty.selectForUpdate(true);
agreementLineQty.ExpirationDate = str2Date(this.COMVariant2Str(cells.item(row, 14).value(), 0),213);
agreementLineQty.CommitedQuantity = str2num(this.COMVariant2Str(cells.item(row, 4).value(), 2));
agreementLineQty.ProductUnitOfMeasure = cells.item(row, 5).value().bStr();
agreementLineQty.PricePerUnit = str2num(this.COMVariant2Str(cells.item(row, 6).value(), 2));
agreementLineQty.LineDiscountAmount =str2num(this.COMVariant2Str(cells.item(row, 7).value(), 2));
agreementLineQty.LineDiscountPercent =str2num(this.COMVariant2Str(cells.item(row, 8).value(), 2));
agreementLineQty.update();
agreementLineQty.salesAgreementHeader().update();
}
if(agreementLineQty)
{
agreementLineDefault = AgreementLineDefault::findAgreementLine(agreementLineQty.RecId);
if(!agreementLineDefault)
{
agreementLineDefault.clear();
agreementLineDefault.initValue();
agreementLineDefault.AgreementLine = agreementLineQty.RecId;
agreementLineDefault.initFromAgreementHeaderDefault(agreementHeaderDefault);
agreementLineDefault.initFromSalesAgreementHeaderDefault(salesAgreementHeaderDefault);
agreementLineDefault.insert();
}
}
}
else
{
info(strFmt("%1 Invalid sales agreement header reference",cells.item(row, 1).value().bStr()));
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
row =1;
cells = null;
worksheetSysHeader = worksheets.itemFromName(worksheetRef);
cells = worksheetSysHeader.cells();
do
{
row++;
salesAgreementHeader.clear();
agreementHeader.clear();
salesAgreementHeader = SalesAgreementHeader::findAgreementId(cells.item(row, 1).value().bStr());
agreementHeader = AgreementHeader::find(salesAgreementHeader.RecId);
agreementLine.clear();
select firstOnly agreementLine
where agreementLine.Agreement == agreementHeader.RecId
&& agreementLine.ItemId == cells.item(row, 3).value().bStr();
if(agreementLine)
{
salesLine.clear();
select firstOnly salesLine
where salesLine.SalesId == cells.item(row, 2).value().bStr()
&& salesLine.ItemId == cells.item(row, 3).value().bStr();
if(salesLine)
{
salesTable = salesLine.salesTable();
if(salesTable)
{
salesTable.selectForUpdate(true);
salesTable.initFromSalesAgreementHeader(salesAgreementHeader);
salesTable.update();
}
SalesTableForm::createAgreementLinkServer(salesLine, agreementLine);
}
else
{
info(strFmt("%1 %2 combination of Sales order and item number doesnot exist",
cells.item(row, 2).value().bStr(),
cells.item(row, 3).value().bStr()));
}
}
else
{
info(strFmt("%1 %2 combination of Sales agreement and item number doesnot exist",
cells.item(row, 1).value().bStr(),
cells.item(row, 3).value().bStr()));
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
ttsCommit;
//Process ended