public static boolean SendSimple(str toEmail, str subject, str bodyHtml, boolean setPriortiy = false) { try { UserInfo uinfo; var mailMsgbuilder = new SysMailerMessageBuilder(); select uinfo where uinfo.id == 'Admin'; if (_setPriortiy) { mailMsgbuilder.setFrom(uinfo.networkAlias); mailMsgbuilder.addTo(_toEmail); mailMsgbuilder.setSubject(_subject); mailMsgbuilder.setBody(_bodyHtml); mailMsgbuilder.setPriority(System.Net.Mail.MailPriority::High); } else { mailMsgbuilder.setFrom(uinfo.networkAlias); mailMsgbuilder.addTo(_toEmail); mailMsgbuilder.setSubject(_subject); mailMsgbuilder.setBody(_bodyHtml); } SysMailerFactory::getNonInteractiveMailer().sendNonInteractive(mailMsgBuilder.getMessage()); return true; } catch { return false; } } Example for html body: body += strFmt("<b>%1</b> <br> %2 <br> <br>", date2str(runForDate,321,2,DateSeparator::Hyphen,2,DateSeparator::Hyphen,4)
Thursday, September 26, 2024
Send Email in D365 FO
Check if user is assigned with privilege in D365 FO
public static boolean checkIfUserAssignedWithPrivilege(str _privilege) { UserInfo userInfo; SecurityUserRole securityUserRole; SecurityPrivilege securityPrivilege; SecurityRolePrivilegeExplodedGraph securityRolePrivilegeExplodedGraph; select firstonly Id, RecId from userInfo exists join securityUserRole where securityUserRole.User == userInfo.Id exists join securityRolePrivilegeExplodedGraph where securityRolePrivilegeExplodedGraph.SecurityRole == securityUserRole.SecurityRole exists join securityPrivilege where securityPrivilege.RecId == securityRolePrivilegeExplodedGraph.SecurityPrivilege && securityPrivilege.Identifier == _privilege && userInfo.id == curUserId(); return userInfo.RecId; }
Thursday, March 28, 2024
BYOD export to selected legal entities
<?xml version="1.0" encoding="utf-8"?> <AxClass xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <Name>DMFDefinitionGroupExecution_DAX_Extension</Name> <SourceCode> <Declaration><![CDATA[ /// <summary> /// Extension class for DMFDefinitionGroupExecution table /// </summary> [extensionof(tableStr(DMFDefinitionGroupExecution))] final class DMFDefinitionGroupExecution_DAX_Extension { } ]]></Declaration> <Methods> <Method> <Name>insertOrdisplay</Name> <Source><![CDATA[ /// <summary> /// COC method for insertOrdisplay /// </summary> /// <param name = "_dMFDefinitionGroup">A <c>DMFDefinitionGroup</c> buffer.</param> /// <param name = "_executionId">Execution Id</param> /// <param name = "_description">Description</param> /// <param name = "_executeTargetStep">A boolean value, to indicate whether to copy data to target or not.</param> /// <param name = "_parameterStr"> Parameter str value.</param> /// <param name = "_isSelected">A boolean value, to indicate whether the record is selected or not.</param> /// <param name = "_company">Company</param> /// <returns>True, if the record creation is successful, else false.</returns> Public static server boolean insertOrdisplay(DMFDefinitionGroup _dMFDefinitionGroup, DMFExecutionId _executionId, Description _description, NoYes _executeTargetStep, str _parameterStr, boolean _isSelected, DataAreaId _company) { boolean ret = next insertOrdisplay(_dMFDefinitionGroup, _executionId, _description, _executeTargetStep, _parameterStr, _isSelected, _company); if (DMFDefinitionGroupExecution::isAllCompanyExportDMFParameterEnabled()) { DMFDefinitionGroupEntity definitionGroupEntity; select firstonly RecId from definitionGroupEntity where definitionGroupEntity.DefinitionGroup == _dMFDefinitionGroup.DefinitionGroupName && definitionGroupEntity.ExportAllCompanies && definitionGroupEntity.ExportToLegalEntity_DAX != strMin(); if (_dMFDefinitionGroup.OperationType == DMFOperationType::Export && definitionGroupEntity.RecId != 0) { DMFExecution dmfExecution; DMFDefinitionGroupExecution definitionGroupExecution; delete_from dmfExecution where dmfExecution.DefinitionGroup == _dMFDefinitionGroup.DefinitionGroupName notexists join definitionGroupExecution where definitionGroupExecution.ExecutionId == dmfExecution.ExecutionId; } } return ret; } ]]></Source> </Method> <Method> <Name>getSelectedLegalEntities</Name> <Source><![CDATA[ /// <summary> /// Convert passed string to container /// </summary> /// <param name = "_legalEntityStr">legal entity names as string</param> /// <returns>legal entity names in a container </returns> static container getSelectedLegalEntities(str _legalEntityStr) { CompanyInfo companyInfo; container tmpValues, conLegalEntity; int idx; if (_legalEntityStr) { tmpValues = str2con(_legalEntityStr, SysAppUtilities_DAX::semiColon); } for (idx=1; idx <= conLen(tmpValues); idx++) { companyInfo = CompanyInfo::findDataArea(conPeek(tmpValues, idx)); conLegalEntity += companyInfo.DataArea; } return conLegalEntity; } ]]></Source> </Method> <Method> <Name>insert</Name> <Source><![CDATA[ /// <summary> /// COC method for Insert /// </summary> public void insert() { next insert(); if (DMFDefinitionGroupExecution::isAllCompanyExportDMFParameterEnabled()) { container conLegalEntity = DMFDefinitionGroupExecution::getSelectedLegalEntities(this.DefinitionGroupEntity().ExportToLegalEntity_DAX); if (conLegalEntity != conNull() && ! conFind(conLegalEntity, this.Company) && this.DefinitionGroupEntity().ExportAllCompanies && this.DefinitionGroup().OperationType == DMFOperationType::Export) { DMFDefinitionGroupExecution definitionGroupExecution; delete_from definitionGroupExecution where definitionGroupExecution.ExecutionId == this.ExecutionId && definitionGroupExecution.Company == this.Company && definitionGroupExecution.Entity == this.Entity; } } } ]]></Source> </Method> <Method> <Name>isAllCompanyExportDMFParameterEnabled</Name> <Source><![CDATA[ /// <summary> /// check whether EnableAllCompanyExport in DMFCompatibilityParameters is enabled /// </summary> /// <returns> true or false</returns> public static boolean isAllCompanyExportDMFParameterEnabled() { DMFCompatibilityParameters dmfCompatibilityParameters; select firstonly EnableAllCompanyExport from dmfCompatibilityParameters index Key where dmfCompatibilityParameters.Key == 0; return dmfCompatibilityParameters.EnableAllCompanyExport; } ]]></Source> </Method> </Methods> </SourceCode> </AxClass> <?xml version="1.0" encoding="utf-8"?> <AxClass xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <Name>DMFDefinitionGroupExecution_ExportNowButton_DAX_Extension</Name> <SourceCode> <Declaration><![CDATA[ /// <summary> /// Extension class for DMFQuickImportExportEnhanced ExportNow button control /// </summary> [extensionof(formControlStr(DMFQuickImportExportEnhanced, ExportNow))] final class DMFDefinitionGroupExecution_ExportNowButton_DAX_Extension { } ]]></Declaration> <Methods> <Method> <Name>clicked</Name> <Source><![CDATA[ /// <summary> /// COC for clicked method button control /// </summary> public void clicked() { container conLegalEntity = conNull(); if (DMFDefinitionGroupExecution::isAllCompanyExportDMFParameterEnabled()) { FormDataSource definitionGroupDS = this.formRun().dataSource(formDataSourceStr(DMFQuickImportExportEnhanced, DMFDefinitionGroup)); DMFDefinitionGroupEntity definitionGroupEntity; DMFDefinitionGroup definitionGroup = definitionGroupDS.cursor(); select firstonly ExportToLegalEntity_DAX from definitionGroupEntity where definitionGroupEntity.DefinitionGroup == definitionGroup.DefinitionGroupName && definitionGroupEntity.ExportAllCompanies && definitionGroupEntity.ExportToLegalEntity_DAX != strMin(); conLegalEntity = DMFDefinitionGroupExecution::getSelectedLegalEntities(definitionGroupEntity.ExportToLegalEntity_DAX); } DataAreaId company = conLegalEntity != conNull() ? conPeek(conLegalEntity, 1) : curExt(); changecompany (company) { next clicked(); } } ]]></Source> </Method> </Methods> </SourceCode> </AxClass>
Monday, November 13, 2023
Post API call from D365 FO using X++
public static void main (Args _args) { URL url = strLTrim(@'https://test-integrations-syncoperations.azurewebsites.net/api/companyRating?code=xxxxxxxx';); System.IO.StringWriter stringWriter; Newtonsoft.Json.JsonTextWriter jsonWriter; System.Byte[] bytes; System.Text.Encoding utf8;
stringWriter = new System.IO.StringWriter(); jsonWriter = new Newtonsoft.Json.JsonTextWriter(stringWriter);
str jsonString = ""; jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("RegistrationNumber"); jsonWriter.WriteValue(5561234567);
jsonWriter.WritePropertyName("CountryCode"); jsonWriter.WriteValue('se');
jsonWriter.WriteEndObject(); jsonString = stringWriter.ToString();
str method = 'POST'; str contentType = @'application/json'; RetailWebRequest webRequest = RetailWebRequest::newUrl(url);
utf8 = System.Text.Encoding::get_UTF8();
bytes = utf8.GetBytes(jsonString);
webRequest.parmMethod(method); webRequest.parmContentType(contentType); webRequest.setContentBytes(bytes);
RetailCommonWebAPI webApi = RetailCommonWebAPI::construct(); RetailWebResponse webResponse = webApi.getResponse(webRequest); str responseData = webResponse.parmData();
info(responseData);
if (webResponse.parmHttpStatus() == 200) { Map responseMap = RetailCommonWebAPI::getMapFromJsonString(responseData); MapEnumerator mapEnumerator; mapEnumerator = responseMap.getEnumerator(); while (mapEnumerator.moveNext()) { if (mapEnumerator.currentKey() == 'companyRating') { info(strFmt(" Credit rating %1 %2",mapEnumerator.currentKey(),mapEnumerator.currentValue())); } } } else { info(responseData); } }
Sunday, June 11, 2023
Create Sales order charges for Header and Lines
DynamicsURL/api/services/SalesOrderChargesServiceGroup/SalesOrderChargesService/SalesOrderCharges
{
"SalesOrderCharges": [
{
"SalesHeaderCharges": [
{
"DataAreaId": "100",
"SalesId": "1000000499",
"ChargeLineNum": "13.00",
"ChargeCode": "XYZ",
"Amount": "25.50"
},
{
"DataAreaId": "100",
"SalesId": "1000000499",
"ChargeLineNum": "14.00",
"ChargeCode": "ABC",
"Amount": "35.50"
}
],
"SalesLineCharges": [
{
"DataAreaId": "100",
"SalesId": "1000000499",
"LineNum": "1.00",
"ChargeLineNum": "14.00",
"ChargeCode": "XYZ",
"Amount": "25.50"
},
{
"DataAreaId": "100",
"SalesId": "1000000499",
"ChargeLineNum": "12.00",
"LineNum": "2.00",
"ChargeCode": "ABC",
"Amount": "15.50"
}
]
}
]
}
/// <summary> /// This class is the data contract class for the <c>SalesLineChargesContract</c> service. /// </summary> [DataContract] class SalesHeaderChargesContract { SalesId salesId; DataAreaId dataAreaId; MarkupCode chargeCode; MarkupFixedAmount amount; LineNum chargesLineNum; [DataMember("SalesId")] public SalesId parmSalesId(salesId _salesId = salesId) { salesId = _salesId; return salesId; } [DataMember("DataAreaId")] public DataAreaId parmDataAreaId(DataAreaId _dataAreaId = dataAreaId) { dataAreaId = _dataAreaId; return dataAreaId; } [DataMember("ChargesLineNum")] public LineNum parmChargesLineNum(LineNum _chargesLineNum = chargesLineNum) { chargesLineNum = _chargesLineNum; return chargesLineNum; } [DataMember("ChargeCode")] public MarkupCode parmChargeCode(MarkupCode _chargeCode = chargeCode) { chargeCode = _chargeCode; return chargeCode; } [DataMember("Amount")] public MarkupFixedAmount parmAmount(MarkupFixedAmount _amount = amount) { amount = _amount; return amount; } }
/// <summary> /// Service class to sales line charges. /// </summary> [SysEntryPointAttribute(true)] class SalesHeaderChargesService { str ret = 'success'; boolean errors; /// <summary> /// Method to create sales line charges. /// </summary> /// <param name = "_contract">SalesLineChargesContract</param> /// <returns>Message to users</returns> public str createChargesheader(SalesHeaderChargesContract line) { try { CompanyInfo companyInfo = CompanyInfo::findDataArea(line.parmDataAreaId()); if(companyInfo) { changecompany(companyInfo.DataArea) { SalesTable salesTable = SalesTable::find(line.parmSalesId(),true); if (SalesTable.recid) { MarkupTrans markUpTrans; MarkupTable markupTable; markupTable = markupTable::find(MarkupModuleType::Cust,line.parmChargeCode()); if (markupTable.recid) { markUpTrans.initFromSalesTable(SalesTable);//Line number 2181 [this.TransTableId = salesLine.TableId;] in this initFromSalesLine() method is returning 1426 for TransTableId markUpTrans.TransTableId = tableNum(SalesTable);//tableNum(SalesLine) returns the expected 3071 for TransTableId; markUpTrans.initFromMarkupTable(markupTable); markUpTrans.initvalue(); markUpTrans.Linenum = line.parmChargesLineNum(); markUpTrans.Value = line.parmAmount(); markUpTrans.insert(); ttsbegin; SalesTable.update(); ttscommit; ret = strFmt("@:ChargeCodeSuccess",line.parmChargeCode(),line.parmSalesId()); } else { throw Error(strFmt("@:ChargeCodeValidation",line.parmChargeCode())); } } else { throw Error(strFmt("@:SalesLineValidation",line.parmSalesId())); } } } else { throw Error(strFmt("@HFG:HFGDataAreaIdInvalid",line.parmDataAreaId())); } } catch { errors = true; } catch(Exception::Error) { errors = true; } if(errors) { throw Error(SalesHeaderChargesService::getError()); } else { return ret; } return ret; } /// <summary> /// Convert infolog from container to string. /// </summary> /// <param name = "_logText">InfologData</param> /// <returns>Error message</returns> private static str getError() { SysInfologEnumerator enumerator; SysInfologMessageStruct msgStruct; Exception exception; str error; enumerator = SysInfologEnumerator::newData(infolog.cut()); while (enumerator.moveNext()) { msgStruct = new SysInfologMessageStruct(enumerator.currentMessage()); exception = enumerator.currentException(); error = strfmt("%1 - %2", error, msgStruct.message()); } return error; } }
/// <summary> /// This class is the data contract class for the <c>SalesLineChargesContract</c> service. /// </summary> [DataContract] class SalesLineChargesContract { SalesId salesId; DataAreaId dataAreaId; LineNum lineNum; MarkupCode chargeCode; MarkupFixedAmount amount; LineNum chargesLineNum; NameAlias readyToInvoice; [DataMember("SalesId")] public SalesId parmSalesId(salesId _salesId = salesId) { salesId = _salesId; return salesId; } [DataMember("ReadyToInvoice")] public NameAlias parmReadyToInvoice(NameAlias _readyToInvoice = readyToInvoice) { readyToInvoice = _readyToInvoice; return readyToInvoice; } [DataMember("DataAreaId")] public DataAreaId parmDataAreaId(DataAreaId _dataAreaId = dataAreaId) { dataAreaId = _dataAreaId; return dataAreaId; } [DataMember("ChargesLineNum")] public LineNum parmChargesLineNum(LineNum _chargesLineNum = chargesLineNum) { chargesLineNum = _chargesLineNum; return chargesLineNum; } [DataMember("LineNum")] public LineNum parmLineNum(LineNum _lineNum = lineNum) { lineNum = _lineNum; return lineNum; } [DataMember("ChargeCode")] public MarkupCode parmChargeCode(MarkupCode _chargeCode = chargeCode) { chargeCode = _chargeCode; return chargeCode; } [DataMember("Amount")] public MarkupFixedAmount parmAmount(MarkupFixedAmount _amount = amount) { amount = _amount; return amount; } }
[DataContractAttribute('SalesOrderChargesContract')] internal final class SalesOrderChargesContract { List SalesHeaderChargesList,SalesLineChargesList; [DataMemberAttribute("SalesHeaderCharges"), AifCollectionTypeAttribute("_SalesHeaderCharges",Types::Class, classStr( SalesHeaderChargesContract)), AifCollectionTypeAttribute("return",Types::Class, classStr( SalesHeaderChargesContract))] public List parmSalesHeaderChargesList(List _SalesHeaderCharges = SalesHeaderChargesList) { SalesHeaderChargesList = _SalesHeaderCharges; return SalesHeaderChargesList; } [DataMemberAttribute("SalesLineCharges"), AifCollectionTypeAttribute("_SalesLineCharges",Types::Class, classStr( SalesLineChargesContract)), AifCollectionTypeAttribute("return",Types::Class, classStr( SalesLineChargesContract))] public List parmSalesLineChargesList(List _SalesLineCharges = SalesLineChargesList) { SalesLineChargesList = _SalesLineCharges; return SalesLineChargesList; } }
[SysEntryPointAttribute(true)] class SalesOrderChargesService { [AifCollectionTypeAttribute('SalesOrderCharges', Types::Class, classStr(SalesOrderChargesContract))] public str processCharges(List SalesOrderCharges) { ListEnumerator orderEnumerator = SalesOrderCharges.getEnumerator(); SalesLineChargesService lineChargesService = new SalesLineChargesService(); SalesHeaderChargesService headerChargesService = new SalesHeaderChargesService(); SalesLineChargesContract lineChargesContract; SalesHeaderChargesContract headerChargesContract; while(orderEnumerator.moveNext()) { SalesOrderChargesContract SalesOrderChargesContract = orderEnumerator.current(); list lineContract = SalesOrderChargesContract.parmSalesLineChargesList(); list headerContract = SalesOrderChargesContract.parmSalesHeaderChargesList(); if (lineContract) { ListEnumerator lineContractEnumerator = lineContract.getEnumerator(); if (lineContractEnumerator) { while(lineContractEnumerator.moveNext()) { lineChargesContract = lineContractEnumerator.current(); lineChargesService.createChargesLines(lineChargesContract); } } } if (headerContract) { ListEnumerator headerContractEnumerator = headerContract.getEnumerator(); if (headerContractEnumerator) { while(headerContractEnumerator.moveNext()) { headerChargesContract = headerContractEnumerator.current(); headerChargesService.createChargesheader(headerChargesContract); } } } } return "Sales order charges created succesfully."; } }
Thursday, April 13, 2023
Custom API to import Headers and lines in Dynamics 365 FO
{
"Request":
[
{
"SalesId": "699-000004",
"LoadAddressId": "100-000306",
"UnloadStreetName": "Kuiperijstraat",
"ImportType": "Update",
"UnloadEmail": "abc@xyz.nl",
"UnloadCountryId": "NLD",
"TransTypeId": 2,
"UnloadName": "Galyteien",
"UnloadDate": "2023-02-07T12:00:00",
"ExtSalesId": "699-007540004",
"UnloadCity": "Utrecht",
"ImportGroupId": "LBHD",
"CustAccount": "INT00601",
"InvoiceAccount": "INT00601",
"UnloadZipCodeId": "4132942 VM",
"SalesType": "5",
"UnloadPhone": "+123456639305",
"CustRef": "2884517",
"CurrencyCode": "EUR",
"InvStatusCode": "UNLOAD",
"LoadTimeFrom": 37000,
"LoadTimeTill": 39600,
"SLAId": "1M",
"TransportOrderLineImport": [
{
"LineNum": 10,
"PlanQty1": 10,
"PlanQty2": 10,
"PlanQty3": 10,
"TransUnitId": "Colli",
"TransportQty": 1,
" TransportOrderLinePackageImport": [
{
"LineNum": 10,
"PackageLineNum": 1,
"Depth": 10,
"Height": 11,
"LabelPrint": "12",
"MeasurementUnitId": "m",
"PackageUnitId": "Rol",
"Volume": 13,
"VolumeUnitId": "M3",
"Weight": 14,
"WeightUnitId": "KG",
"Width": 15,
" TransportOrderLinePackageContentImport": [
{
"LineNum": 10,
"PackageLineNum": 1,
"PackageContentLineNum": 1,
"ItemId": "12345",
"ItemName": "test item",
"Qty": 5,
"UnitId": "Rol"
}
],
" TransportOrderLinePackageIdentificationImport": [
{
"LineNum": 10,
"PackageLineNum": 1,
"PackageIdentificationLineNum": 1,
"PackageIdentificationId": "testid1",
"PackageIdentificationTypeId": "EAN"
}
]
}
]
}
]
}
]
}
[DataContractAttribute(' TransportOrderHeaderImportContract')] internal final class TransportOrderHeaderImportContract { SalesId salesId; str LoadAddressId; str ImportType; str UnloadStreetName; Email UnloadEmail; str UnloadCountryId; int TransTypeId; str UnloadName; str UnloadDate; str ExtSalesId; str UnloadCity; str ImportGroupId; CustAccount CustAccount; str InvoiceAccount; str UnloadZipCodeId; str SalesType; str UnloadPhone; str CustRef; str CurrencyCode; str InvStatusCode; str SLAId; int LoadTimeFrom; int LoadTimeTill; List TransportOrderLineList; [DataMemberAttribute("TransportOrderLineImport"), AifCollectionTypeAttribute("_TransportOrderLineImport",Types::Class, classStr( TransportOrderLineImportContract)), AifCollectionTypeAttribute("return",Types::Class, classStr( TransportOrderLineImportContract))] public List parm TransportOrderLineList(List _TransportOrderLineImport = TransportOrderLineList) { TransportOrderLineList = _TransportOrderLineImport; return TransportOrderLineList; } [DataMemberAttribute('SalesId')] public SalesId parmSalesId(SalesId _salesId = salesId) { salesId = _salesId; return salesId; } [DataMemberAttribute('LoadAddressId')] public str parmLoadAddressId(str _LoadAddressId = LoadAddressId) { LoadAddressId = _LoadAddressId; return LoadAddressId; } [DataMemberAttribute('ImportType')] public str parmImportType(str _ImportType = ImportType) { ImportType = _ImportType; return ImportType; } [DataMemberAttribute('UnloadStreetName')] public str parmUnloadStreetName(str _UnloadStreetName = UnloadStreetName) { UnloadStreetName = _UnloadStreetName; return UnloadStreetName; } [DataMemberAttribute('UnloadEmail')] public SalesId parmUnloadEmail(Email _UnloadEmail = UnloadEmail) { UnloadEmail = _UnloadEmail; return UnloadEmail; } [DataMemberAttribute('UnloadCountryId')] public str parmUnloadCountryId(str _UnloadCountryId = UnloadCountryId) { UnloadCountryId = _UnloadCountryId; return UnloadCountryId; } [DataMemberAttribute('TransTypeId')] public int parmTransTypeId(int _TransTypeId = TransTypeId) { TransTypeId = _TransTypeId; return TransTypeId; } [DataMemberAttribute('UnloadName')] public str parmUnloadName(str _UnloadName = UnloadName) { UnloadName = _UnloadName; return UnloadName; } [DataMemberAttribute('UnloadDate')] public str parmUnloadDate(str _UnloadDate = UnloadDate) { UnloadDate = _UnloadDate; return UnloadDate; } [DataMemberAttribute('ExtSalesId')] public str parmExtSalesId(str _ExtSalesId = ExtSalesId) { ExtSalesId = _ExtSalesId; return ExtSalesId; } [DataMemberAttribute('UnloadCity')] public str parmUnloadCity(str _UnloadCity = UnloadCity) { UnloadCity = _UnloadCity; return UnloadCity; } [DataMemberAttribute('ImportGroupId')] public str parmImportGroupId(str _ImportGroupId = ImportGroupId) { ImportGroupId = _ImportGroupId; return ImportGroupId; } [DataMemberAttribute('CustAccount')] public CustAccount parmCustAccount(CustAccount _CustAccount = CustAccount) { CustAccount = _CustAccount; return CustAccount; } [DataMemberAttribute('InvoiceAccount')] public str parmInvoiceAccount(str _InvoiceAccount = InvoiceAccount) { InvoiceAccount = _InvoiceAccount; return InvoiceAccount; } [DataMemberAttribute('UnloadZipCodeId')] public str parmUnloadZipCodeId(str _UnloadZipCodeId = UnloadZipCodeId) { UnloadZipCodeId = _UnloadZipCodeId; return UnloadZipCodeId; } [DataMemberAttribute('SalesType')] public str parmSalesType(str _SalesType = SalesType) { SalesType = _SalesType; return SalesType; } [DataMemberAttribute('UnloadPhone')] public str parmUnloadPhone(str _UnloadPhone = UnloadPhone) { UnloadPhone = _UnloadPhone; return UnloadPhone; } [DataMemberAttribute('CustRef')] public str parmCustRef(str _CustRef = CustRef) { CustRef = _CustRef; return CustRef; } [DataMemberAttribute('CurrencyCode')] public str parmCurrencyCode(str _CurrencyCode = CurrencyCode) { CurrencyCode = _CurrencyCode; return CurrencyCode; } [DataMemberAttribute('InvStatusCode')] public str parmInvStatusCode(str _InvStatusCode = InvStatusCode) { InvStatusCode = _InvStatusCode; return InvStatusCode; } [DataMemberAttribute('SLAId')] public str parmSLAId(str _SLAId = SLAId) { SLAId = _SLAId; return SLAId; } [DataMemberAttribute('LoadTimeFrom')] public int parmLoadTimeFrom(int _LoadTimeFrom = LoadTimeFrom) { LoadTimeFrom = _LoadTimeFrom; return LoadTimeFrom; } [DataMemberAttribute('LoadTimeTill')] public int parmLoadTimeTill(int _LoadTimeTill = LoadTimeTill) { LoadTimeTill = _LoadTimeTill; return LoadTimeTill; } }
internal final class TransportOrderLineImportContract { LineNum LineNum; int PlanQty1; int PlanQty2; str TransUnitId; int PlanQty3; int TransportQty; List TransportOrderLinePackageList; [DataMemberAttribute(" TransportOrderLinePackageImport"), AifCollectionTypeAttribute("_ TransportOrderLinePackageImport",Types::Class, classStr( TransportOrderLinePackageImportContract)), AifCollectionTypeAttribute("return",Types::Class, classStr( TransportOrderLinePackageImportContract))] public List parm TransportOrderLinePackageList(List _ TransportOrderLinePackageImport = TransportOrderLinePackageList) { TransportOrderLinePackageList = _ TransportOrderLinePackageImport; return TransportOrderLinePackageList; } [DataMemberAttribute('LineNum')] public LineNum parmLineNum(LineNum _LineNum = LineNum) { LineNum = _LineNum; return LineNum; } [DataMemberAttribute('PlanQty1')] public int parmPlanQty1(int _PlanQty1 = PlanQty1) { PlanQty1 = _PlanQty1; return PlanQty1; } [DataMemberAttribute('PlanQty3')] public int parmPlanQty3(int _PlanQty3 = PlanQty3) { PlanQty3 = _PlanQty3; return PlanQty3; } [DataMemberAttribute('PlanQty2')] public int parmPlanQty2(int _PlanQty2 = PlanQty2) { PlanQty2 = _PlanQty2; return PlanQty2; } [DataMemberAttribute('TransUnitId')] public str parmTransUnitId(str _TransUnitId = TransUnitId) { TransUnitId = _TransUnitId; return TransUnitId; } [DataMemberAttribute('TransportQty')] public int parmTransportQty(int _TransportQty = TransportQty) { TransportQty = _TransportQty; return TransportQty; } }
internal final class TransportOrderLinePackageContentImportContract { LineNum LineNum; LineNum PackageLineNum; LineNum PackageIdentificationLineNum; ItemId itemId; ItemName itemName; Qty qty; str unitId; [DataMemberAttribute('LineNum')] public LineNum parmLineNum(LineNum _LineNum = LineNum) { LineNum = _LineNum; return LineNum; } [DataMemberAttribute('PackageLineNum')] public LineNum parmPackageLineNum(LineNum _PackageLineNum = PackageLineNum) { PackageLineNum = _PackageLineNum; return PackageLineNum; } [DataMemberAttribute('PackageIdentificationLineNum')] public LineNum parmPackageIdentificationLineNum(LineNum _PackageIdentificationLineNum = PackageIdentificationLineNum) { PackageIdentificationLineNum = _PackageIdentificationLineNum; return PackageIdentificationLineNum; } [DataMemberAttribute('itemId')] public itemId parmitemId(itemId _itemId = itemId) { itemId = _itemId; return itemId; } [DataMemberAttribute('itemName')] public itemName parmitemName(itemName _itemName = itemName) { itemName = _itemName; return itemName; } [DataMemberAttribute('UnitId')] public str parmUnitId(str _UnitId = UnitId) { UnitId = _UnitId; return UnitId; } [DataMemberAttribute('Qty')] public Qty parmQty(Qty _qty = qty) { qty = _qty; return qty; } }
internal final class TALTransportOrderLinePackageIdentificationImportContract { LineNum LineNum; LineNum PackageLineNum; LineNum PackageIdentificationLineNum; str PackageIdentificationId; str PackageIdentificationTypeId; [DataMemberAttribute('LineNum')] public LineNum parmLineNum(LineNum _LineNum = LineNum) { LineNum = _LineNum; return LineNum; } [DataMemberAttribute('PackageLineNum')] public LineNum parmPackageLineNum(LineNum _PackageLineNum = PackageLineNum) { PackageLineNum = _PackageLineNum; return PackageLineNum; } [DataMemberAttribute('PackageIdentificationLineNum')] public LineNum parmPackageIdentificationLineNum(LineNum _PackageIdentificationLineNum = PackageIdentificationLineNum) { PackageIdentificationLineNum = _PackageIdentificationLineNum; return PackageIdentificationLineNum; } [DataMemberAttribute('PackageIdentificationTypeId')] public str parmPackageIdentificationTypeId(str _PackageIdentificationTypeId = PackageIdentificationTypeId) { PackageIdentificationTypeId = _PackageIdentificationTypeId; return PackageIdentificationTypeId; } [DataMemberAttribute('PackageIdentificationId')] public str parmPackageIdentificationId(str _PackageIdentificationId = PackageIdentificationId) { PackageIdentificationId = _PackageIdentificationId; return PackageIdentificationId; } }
internal final class TransportOrderLinePackageImportContract { LineNum LineNum; LineNum PackageLineNum; int Depth; int Height; str LabelPrint; str MeasurementUnitId; str PackageUnitId; str VolumeUnitId; str WeightUnitId; int Volume; int Width; int Weight; List TransportOrderLinePackageContentImportList; List TransportOrderLinePackageIdentificationImportList; [DataMemberAttribute(" TransportOrderLinePackageContentImport"), AifCollectionTypeAttribute("_ TransportOrderLinePackageContentImport",Types::Class, classStr( TransportOrderLinePackageContentImportContract)), AifCollectionTypeAttribute("return",Types::Class, classStr( TransportOrderLinePackageContentImportContract))] public List parm OrderLinePackageContentImportList(List _ TransportOrderLinePackageContentImport = TransportOrderLinePackageContentImportList) { TransportOrderLinePackageContentImportList = _ TransportOrderLinePackageContentImport; return TransportOrderLinePackageContentImportList; } [DataMemberAttribute(" TransportOrderLinePackageIdentificationImport"), AifCollectionTypeAttribute("_ TransportOrderLinePackageIdentificationImport",Types::Class, classStr( TransportOrderLinePackageIdentificationImportContract)), AifCollectionTypeAttribute("return",Types::Class, classStr( TransportOrderLinePackageIdentificationImportContract))] public List parm TransportOrderLinePackageIdentificationImportList(List _ TransportOrderLinePackageIdentificationImport = TransportOrderLinePackageIdentificationImportList) { TransportOrderLinePackageIdentificationImportList = _ TransportOrderLinePackageIdentificationImport; return TransportOrderLinePackageIdentificationImportList; } [DataMemberAttribute('LineNum')] public LineNum parmLineNum(LineNum _LineNum = LineNum) { LineNum = _LineNum; return LineNum; } [DataMemberAttribute('PackageLineNum')] public LineNum parmPackageLineNum(LineNum _PackageLineNum = PackageLineNum) { PackageLineNum = _PackageLineNum; return PackageLineNum; } [DataMemberAttribute('Depth')] public int parmDepth(int _Depth = Depth) { Depth = _Depth; return Depth; } [DataMemberAttribute('Width')] public int parmWidth(int _Width = Width) { Width = _Width; return Width; } [DataMemberAttribute('Volume')] public int parmVolume(int _Volume = Volume) { Volume = _Volume; return Volume; } [DataMemberAttribute('Weight')] public int parmWeight(int _Weight = Weight) { Weight = _Weight; return Weight; } [DataMemberAttribute('Height')] public int parmHeight(int _Height = Height) { Height = _Height; return Height; } [DataMemberAttribute('LabelPrint')] public str parmLabelPrint(str _LabelPrint = LabelPrint) { LabelPrint = _LabelPrint; return LabelPrint; } [DataMemberAttribute('MeasurementUnitId')] public str parmMeasurementUnitId(str _MeasurementUnitId = MeasurementUnitId) { MeasurementUnitId = _MeasurementUnitId; return MeasurementUnitId; } [DataMemberAttribute('PackageUnitId')] public str parmPackageUnitId(str _PackageUnitId = PackageUnitId) { PackageUnitId = _PackageUnitId; return PackageUnitId; } [DataMemberAttribute('VolumeUnitId')] public str parmVolumeUnitId(str _VolumeUnitId = VolumeUnitId) { VolumeUnitId = _VolumeUnitId; return VolumeUnitId; } [DataMemberAttribute('WeightUnitId')] public str parmWeightUnitId(str _WeightUnitId = WeightUnitId) { WeightUnitId = _WeightUnitId; return WeightUnitId; } }
internal final class TransportOrderImportService { RecordInsertList headerInsert; RecordInsertList lineInsert; RecordInsertList linePackageInsert; RecordInsertList linePackageContentInsert; RecordInsertList linePackageIdentificationInsert; [AifCollectionType('Request', Types::Class, classStr( TransportOrderHeaderImportContract))] public TransportOrderResponse CreateTransportOrder(List Request) { List resultSet = new List(Types::Class); headerInsert = new RecordInsertList(tableNum(CIRTRAImportSalesTable)); lineInsert = new RecordInsertList(tableNum(CIRTRAImportSalesTableLinie)); linePackageInsert = new RecordInsertList(tableNum( ImportTransportOrderLinePackage)); linePackageContentInsert = new RecordInsertList(tableNum( ImportTransportOrderLinePackageContent)); linePackageIdentificationInsert = new RecordInsertList(tableNum( ImportTransportOrderLinePackageIdentification)); TransportOrderLineImportContract TransportOrderLineImportContract; CIRTRAImportSalesTable salesTable; ImportType ImportType; TransportOrderHeaderImportContract headerContract; SalesId salesIdResp; ttsbegin; ListEnumerator lEnum = Request.getEnumerator(); while (lEnum.MoveNext()) { headerContract = lEnum.current(); salesIdResp = headerContract.parmSalesId(); salesTable.SalesId = headerContract.parmSalesId(); salesTable.LoadAddressId = headerContract.parmLoadAddressId(); salesTable.UnloadStreetName = headerContract.parmUnloadStreetName(); salesTable.ImportType = str2Enum( ImportType,headerContract.parmImportType()); salesTable.UnloadEmail = headerContract.parmUnloadEmail(); salesTable.UnloadCountryId = headerContract.parmUnloadCountryId(); salesTable.TransTypeId = headerContract.parmTransTypeId(); salesTable.UnloadName = headerContract.parmUnloadName(); salesTable.ExtSalesId = headerContract.parmExtSalesId(); salesTable.UnloadCity = headerContract.parmUnloadCity(); salesTable.ImportGroupId = headerContract.parmImportGroupId(); salesTable.CustAccount = headerContract.parmCustAccount(); salesTable.InvoiceAccount = headerContract.parmInvoiceAccount(); salesTable.UnloadZipCodeId = headerContract.parmUnloadCity(); salesTable.SalesType = str2Int(headerContract.parmSalesType()); salesTable.UnloadPhone = headerContract.parmUnloadPhone(); salesTable.CustRef = headerContract.parmCustRef(); salesTable.CurrencyCode = headerContract.parmCurrencyCode(); salesTable.InvStatusCode = headerContract.parmInvStatusCode(); salesTable.LoadTimeFrom = headerContract.parmLoadTimeFrom(); salesTable.LoadTimeTill = headerContract.parmLoadTimeTill(); salesTable.SLAId = headerContract.parmSLAId(); if (salesTable.validateWrite()) { headerInsert.add(salesTable); } //Create lines this.createLinesImport(headerContract); // Insert the JSON to respective tables this.createOrder(); } ttscommit; // Send response TransportOrderResponse response = new TransportOrderResponse(); response.ParmDescription(strFmt("Transport successfully created for sales order %1",salesIdResp)); return response; } public void createLinesImport( TransportOrderHeaderImportContract Request) { List TransportOrderLineList = new List(Types::Class); TransportOrderLineList = Request.parm TransportOrderLineList(); ListEnumerator listLineEnumerator = TransportOrderLineList.getEnumerator(); TransportOrderLineImportContract lienImportContract; CIRTRAImportSalesTableLinie salesLine; while(listLineEnumerator.moveNext()) { lienImportContract = ListLineEnumerator.current(); salesLine.SalesId = Request.parmSalesId(); salesLine.PlanQty1 = lienImportContract.parmPlanQty1(); salesLine.PlanQty2 = lienImportContract.parmPlanQty2(); salesLine.PlanQty3 = lienImportContract.parmPlanQty3(); salesLine.TransUnitId= lienImportContract.parmTransUnitId(); salesLine.TransportQty= lienImportContract.parmTransportQty(); lineInsert.add(salesLine); this.createLinePackageImport(lienImportContract,salesLine.SalesId); } } public void createLinePackageImport( TransportOrderLineImportContract lienImportContract,SalesId _salesid) { List TransportOrderLinePackageList = new List(Types::Class); TransportOrderLinePackageList = lienImportContract.parm TransportOrderLinePackageList(); ListEnumerator listLineEnumerator = TransportOrderLinePackageList.getEnumerator(); TransportOrderLinePackageImportContract linePackageImportContract; ImportTransportOrderLinePackage linePackage; while(listLineEnumerator.moveNext()) { linePackageImportContract = ListLineEnumerator.current(); linePackage.SalesId = _salesid; linePackage.LineNum = lienImportContract.parmLineNum(); linePackage.PackageLineNum = linePackageImportContract.parmPackageLineNum(); linePackage.Depth = linePackageImportContract.parmDepth(); linePackage.Height = linePackageImportContract.parmHeight(); linePackage.LabelPrint = str2int(linePackageImportContract.parmLabelPrint()); linePackage.MeasurementUnitId = linePackageImportContract.parmMeasurementUnitId(); linePackage.PackageUnitId = linePackageImportContract.parmPackageUnitId(); linePackage.Volume = linePackageImportContract.parmVolume(); linePackage.VolumeUnitId = linePackageImportContract.parmVolumeUnitId(); linePackage.Weight = linePackageImportContract.parmWeight(); linePackage.WeightUnitId = linePackageImportContract.parmWeightUnitId(); linePackage.Width = linePackageImportContract.parmWidth(); linePackageInsert.add(linePackage); // create lines content this.createLinePackageContentImport(linePackageImportContract,_salesid); // create lines identifiction content this.createLinePackageIdentificationImport(linePackageImportContract,_salesid); } } public void createLinePackageContentImport( TransportOrderLinePackageImportContract _linePackageImportContract,SalesId _salesId) { List TransportOrderLineContentPackageList = new List(Types::Class); TransportOrderLineContentPackageList = _linePackageImportContract.parm OrderLinePackageContentImportList(); ListEnumerator listLineEnumerator = TransportOrderLineContentPackageList.getEnumerator(); TransportOrderLinePackageContentImportContract linePackageImportContentContract; ImportTransportOrderLinePackageContent lineContentPackage; while(listLineEnumerator.moveNext()) { linePackageImportContentContract = ListLineEnumerator.current(); lineContentPackage.SalesId = _salesId; lineContentPackage.LineNum = _linePackageImportContract.parmLineNum(); lineContentPackage.PackageContentLineNum = _linePackageImportContract.parmPackageLineNum(); lineContentPackage.ItemId = linePackageImportContentContract.parmitemId(); lineContentPackage.ItemName = linePackageImportContentContract.parmitemName(); lineContentPackage.Qty = linePackageImportContentContract.parmQty(); lineContentPackage.UnitId = linePackageImportContentContract.parmUnitId(); linePackageContentInsert.add(lineContentPackage); } } public void createLinePackageIdentificationImport( TransportOrderLinePackageImportContract _linePackageImportContract ,SalesId _salesId) { List TransportOrderLineIdnPackageList = new List(Types::Class); TransportOrderLineIdnPackageList = _linePackageImportContract.parm TransportOrderLinePackageIdentificationImportList(); ListEnumerator listLineEnumerator = TransportOrderLineIdnPackageList.getEnumerator(); TransportOrderLinePackageIdentificationImportContract linePackageImportIdnContract; ImportTransportOrderLinePackageIdentification lineIdnPackage; while(listLineEnumerator.moveNext()) { linePackageImportIdnContract = ListLineEnumerator.current(); lineIdnPackage.SalesId = _salesId; lineIdnPackage.LineNum = _linePackageImportContract.parmLineNum(); lineIdnPackage.PackageIdentificationId = linePackageImportIdnContract.parmPackageIdentificationId(); lineIdnPackage.PackageIdentificationLineNum = linePackageImportIdnContract.parmPackageIdentificationLineNum(); lineIdnPackage.PackageIdentificationTypeId = linePackageImportIdnContract.parmPackageIdentificationTypeId(); linePackageIdentificationInsert.add(lineIdnPackage); } } public void createOrder() { headerInsert.insertDatabase(); lineInsert.insertDatabase(); linePackageInsert.insertDatabase(); linePackageContentInsert.insertDatabase(); linePackageIdentificationInsert.insertDatabase(); } }
[DataContractAttribute] internal final class TransportOrderResponse { str response; [DataMemberAttribute("Response")] public str ParmDescription(str _response = response) { response = _response; return response; } }
Monday, April 10, 2023
Export and email excel through batch in D365 FO
Hi,
If you are looking for a code to export excel through batch and email the excel as an attachment to your vendors.
using System.IO; using OfficeOpenXml; using OfficeOpenXml.Style; using OfficeOpenXml.Table; class VendorReportingFileExport extends RunBaseBatch implements BatchRetryable { // Packed variables TransDate transDate; QueryRun queryRun; #define.CurrentVersion(1) #localmacro.CurrentList transDate #endmacro /// <summary> /// Indicates whether the class is shown in the list of <c>Journal</c> types. /// </summary> /// <returns> /// true if the class is shown in the list of <c>Journal</c> types; otherwise, false. /// </returns> /// <remarks> /// A class that can be used in a batch journal is a class where the same parameters can be used /// repeatedly. The dialog box can be shown and the parameters can be changed, but parameters of some /// classes might build on data that is only valid for a short time. Running a class two times with the /// same parameters is not always possible. If the <c>RunBaseBatch.canGoBatch</c> method is false, this /// method will not have any effect. /// </remarks> public boolean canGoBatchJournal() { return true; } /// <summary> /// Returns a class that contains the methods that are described by the <c>RunBaseDialogable</c> /// interface. /// </summary> /// <returns> /// A class that contains the methods that are described by the <c>RunBaseDialogable</c> interface. /// </returns> /// <remarks> /// A dialog box can be either built by using the <c>Dialog</c> class or by using a class that is /// created in the Application Object Tree (AOT). /// </remarks> public Object dialog() { DialogRunbase dialog = super(); #resAppl ; return dialog; } public void dialogPostRun(DialogRunbase dialog) { ; super(dialog); } public boolean init() { return true; } public boolean showQueryValues() { return true; } protected void new() { super(); } public container pack() { return [#CurrentVersion,#CurrentList,queryrun.pack()]; } public void exportFileStandard() { VendorReporting vendReporting; queryRun qrRunLoc = this.queryRun(); qrRunLoc.query().allowCrossCompany(true); while (qrRunLoc.next()) { MemoryStream memoryStream = new MemoryStream(); using(var package = new ExcelPackage(memoryStream)) { var worksheets = package.get_Workbook().get_Worksheets(); var worksheet = worksheets.Add("Inventory"); var cells = worksheet.get_Cells(); var currentRow=1 ; /*-------HEADER PART -------*/ var cell = cells.get_Item(currentRow,1); cell.set_Value(" tributor Name"); cell = null; cell = cells.get_Item(currentRow,2); cell.set_Value("ABCtributor Country"); cell = null; cell = cells.get_Item(currentRow,3); cell.set_Value("Date"); cell = null; cell = cells.get_Item(currentRow,4); cell.set_Value("Manufacture part nr"); cell = null; cell = cells.get_Item(currentRow,5); cell.set_Value("ABCtributor Part Number"); cell = null; cell = cells.get_Item(currentRow,6); cell.set_Value("Product Description"); cell = null; cell = cells.get_Item(currentRow,7); cell.set_Value("Total Units in Stock"); cell = null; cell = cells.get_Item(currentRow,8); cell.set_Value("Available Units (Free Stock)"); cell = null; cell = cells.get_Item(currentRow,9); cell.set_Value("ABCtributor Unit Cost (SEK)"); cell = null; cell = cells.get_Item(currentRow,10); cell.set_Value("Total Stock Value (SEK)"); cell = null; cell = cells.get_Item(currentRow,11); cell.set_Value("Open order Qty"); cell = null; /*-------HEADER PART END-------*/ var worksheet2 = worksheets.Add("Sales out"); vendReporting = qrRunLoc.get(tableNum(VendorReporting)); InventTable inventTable; InventQty totalUnitsStock; InventQtyReservPhysical physicallyReserved; while select inventTable where inventTable.PrimaryVendorId == vendReporting.VendorId && inventTable.DataAreaId == 'XYZ' { totalUnitsStock = 0; physicallyReserved = 0; currentRow += 1; cell = cells.get_Item(currentRow, 1); cell.set_Value(CompanyInfo::findDataArea(curExt()).Name); cell= null; cell= cells.get_Item(currentRow, 2); cell.set_Value(CompanyInfo::findDataArea(curExt()).postalAddress().CountryRegionId); cell= null; cell= cells.get_Item(currentRow, 3); cell.set_Value(date2Str(systemDateGet(),321,2,2,2,2,4)); cell= null; cell= cells.get_Item(currentRow, 4); cell.set_Value(InventItemBarcode::findItemId(inventTable.ItemId,false,false, SysAppUtilities_ABC::barcodeSetupMPN).itemBarCode); cell= null; cell= cells.get_Item(currentRow, 5); cell.set_Value(inventTable.ItemId); cell= null; cell= cells.get_Item(currentRow, 6); cell.set_Value(inventTable.itemName()); cell= null; cell= cells.get_Item(currentRow, 7); totalUnitsStock = this.onHandInventory(inventTable.itemid, SysAppUtilities_ABC::main, SysAppUtilities_ABC::standard); totalUnitsStock = totalUnitsStock + this.onHandInventory(inventTable.itemid, SysAppUtilities_ABC::high, SysAppUtilities_ABC::standard); cell.set_Value(totalUnitsStock); cell= null; cell= cells.get_Item(currentRow, 8); physicallyReserved = this.availPhysical(inventTable.itemid, SysAppUtilities_ABC::main, SysAppUtilities_ABC::standard); physicallyReserved = physicallyReserved + this.availPhysical(inventTable.itemid, SysAppUtilities_ABC::high, SysAppUtilities_ABC::standard); cell.set_Value(physicallyReserved); cell= null; cell= cells.get_Item(currentRow, 9); cell.set_Value(InventTable.ABCtITBaseCost_ABC); cell= null; cell= cells.get_Item(currentRow, 10); cell.set_Value(totalUnitsStock * InventTable.ABCtITBaseCost_ABC); cell= null; cell= cells.get_Item(currentRow, 11); cell.set_Value(this.getOpenOrderQty(inventTable.ItemId)); } cells = worksheet2.get_Cells(); currentRow=1 ; /*-------HEADER PART -------*/ cell = cells.get_Item(currentRow,1); cell.set_Value("ABCtributor Name"); cell = null; cell = cells.get_Item(currentRow,2); cell.set_Value("ABCtributor Country"); cell = null; cell = cells.get_Item(currentRow,3); cell.set_Value("Month/Week/Date"); cell = null; cell = cells.get_Item(currentRow,4); cell.set_Value("Customer Account Number"); cell = null; cell = cells.get_Item(currentRow,5); cell.set_Value("Customer Name"); cell = null; cell = cells.get_Item(currentRow,6); cell.set_Value("Customer Address"); cell = null; cell = cells.get_Item(currentRow,7); cell.set_Value("Customer Address 2"); cell = null; cell = cells.get_Item(currentRow,8); cell.set_Value("Customer City"); cell = null; cell = cells.get_Item(currentRow,9); cell.set_Value("Customer Postal Code"); cell = null; cell = cells.get_Item(currentRow,10); cell.set_Value("Customer VAT/NIF Number"); cell = null; cell = cells.get_Item(currentRow,11); cell.set_Value("Customer Country"); cell = null; cell = cells.get_Item(currentRow,12); cell.set_Value("Invoice date"); cell = null; cell = cells.get_Item(currentRow,13); cell.set_Value("ABCtributor Part Code"); cell = null; cell = cells.get_Item(currentRow,14); cell.set_Value("Vendor Part Code"); cell = null; cell = cells.get_Item(currentRow,15); cell.set_Value("Product description"); cell = null; cell = cells.get_Item(currentRow,16); cell.set_Value("Quantity Sold"); cell = null; cell = cells.get_Item(currentRow,17); cell.set_Value("ABCtribution Unit Sell Price"); cell = null; cell = cells.get_Item(currentRow,18); cell.set_Value("ABCtribution Cost Price"); cell = null; cell = cells.get_Item(currentRow,19); cell.set_Value("Currency"); cell = null; /*-------HEADER PART END-------*/ TransDate fromDate, toDate, prevDate; TransDate currentDate = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()); switch (vendReporting.Schedule) { case VendorReportingExpSchedule_ABC::Daily : fromDate = currentDate - 1; toDate = currentDate - 1; break; case VendorReportingExpSchedule_ABC::Weekly : PreferredLocale preferredLocale = (new xSession()).PreferredLocale(); prevDate = HcmDateTimeUtil::calculateDateWithOffset(PeriodUnit::Day,7,false, currentDate); toDate = DateTimeUtil::getEndOfWeekDate(preferredLocale, prevDate); fromDate = DateTimeUtil::getStartOfWeekDate(preferredLocale, prevDate); break; case VendorReportingExpSchedule_ABC::Monthly : prevDate = prevMth(currentDate); fromDate = DateStartMth(prevDate); toDate = endmth(prevDate); break; } CustInvoiceTrans custInvoiceTrans; container conCompanies = ['ABC', 'CDE', 'XYZ', 'LMN']; while select crosscompany : conCompanies custInvoiceTrans where custInvoiceTrans.InvoiceDate >= fromDate && custInvoiceTrans.InvoiceDate <= toDate { InventTable inventTableLoc = InventTable::find(custInvoiceTrans.ItemId); if (inventTableLoc.PrimaryVendorId == vendReporting.VendorId) { changecompany (custInvoiceTrans.DataAreaId) { InventTable inventTableIC = InventTable::find(custInvoiceTrans.ItemId); CustTable custTable = CustTable::find(SalesTable::find(custInvoiceTrans.SalesId).CustAccount); currentRow += 1; cell = cells.get_Item(currentRow,1); cell.set_Value(CompanyInfo::findDataArea(curExt()).Name); cell = null; cell = cells.get_Item(currentRow,2); cell.set_Value(CompanyInfo::findDataArea(curExt()).postalAddress().CountryRegionId); cell = null; cell = cells.get_Item(currentRow,3); cell.set_Value(this.getMonthWeekDate(vendReporting.schedule, prevDate)); cell = null; cell = cells.get_Item(currentRow,4); cell.set_Value(custTable.AccountNum); cell = null; cell = cells.get_Item(currentRow,5); cell.set_Value(custTable.name()); cell = null; cell = cells.get_Item(currentRow,6); cell.set_Value(custTable.postalAddress().Street); cell = null; cell = cells.get_Item(currentRow,7); cell.set_Value(custTable.postalAddress().BuildingCompliment); cell = null; cell = cells.get_Item(currentRow,8); cell.set_Value(custTable.postalAddress().City); cell = null; cell = cells.get_Item(currentRow,9); cell.set_Value(custTable.postalAddress().ZipCode); cell = null; cell = cells.get_Item(currentRow,10); cell.set_Value(custTable.VATNum); cell = null; cell = cells.get_Item(currentRow,11); cell.set_Value(custTable.countryRegionId()); cell = null; cell = cells.get_Item(currentRow,12); cell.set_Value(date2Str(CustInvoiceTrans.InvoiceDate,321,2,2,2,2,4)); cell = null; cell = cells.get_Item(currentRow,13); cell.set_Value(CustInvoiceTrans.ItemId); cell = null; cell = cells.get_Item(currentRow,14); cell.set_Value(InventItemBarcode::findItemId(inventTableIC.ItemId,false,false, SysAppUtilities_ABC::barcodeSetupMPN).itemBarCode); cell = null; cell = cells.get_Item(currentRow,15); cell.set_Value(inventTableIC.itemName()); cell = null; cell = cells.get_Item(currentRow,16); cell.set_Value(custInvoiceTrans.Qty); cell = null; cell = cells.get_Item(currentRow,17); cell.set_Value(custInvoiceTrans.LineAmount); if(Ledger::accountingCurrency(CompanyInfo::current()) != custInvoiceTrans.CurrencyCode) { cell = null; cell = cells.get_Item(currentRow,18); cell.set_Value(this.exchangeRate(Ledger::accountingCurrency(CompanyInfo::current()), custInvoiceTrans.CurrencyCode, inventTableIC.ABCtITICCost_ABC, custInvoiceTrans.InvoiceDate)); } else { cell = null; cell = cells.get_Item(currentRow,18); cell.set_Value(inventTableIC.ABCtITICCost_ABC); } cell = null; cell = cells.get_Item(currentRow,19); cell.set_Value(custInvoiceTrans.CurrencyCode); cell = null; } } } package.Save(); memoryStream.Seek(0,System.IO.SeekOrigin::Begin); str fileNameVal = "ABC_"+ VendTable::find(vendReporting.VendorId).name() + "_" + date2Str(DateStartMth(fromDate),321,2,2,2,2,2) + "_" + date2Str(endMth(toDate),321,2,2,2,2,2) + ".xlsx"; VendorReportingFileExport::sendEmail( fileNameVal, "Inventory and Sales out ", "Inventory and Sales out details", memoryStream, SysEmailParameters::find().SMTPUserName, vendReporting.Email); } } } public static void sendEmail(str _fileName, str _subject, str _body, System.IO.MemoryStream _memoryStream, Email _fromEmail, Email _toEmail) { SysMailerMessageBuilder mailer = new SysMailerMessageBuilder(); SysMailerSMTP smtp = new SysMailerSMTP(); try { mailer.setSubject(_subject); mailer.setFrom(_fromEmail); mailer.setBody(_body); mailer.addTo(_toEmail); mailer.addAttachment(_memoryStream, _fileName); smtp.sendNonInteractive(mailer.getMessage()); Info("@SYS58551"); } catch (Exception::CLRError) { System.Exception ex = ClrInterop::getLastException(); if (ex != null) { ex = ex.get_InnerException(); if (ex != null) { error(ex.ToString()); } } } catch (Exception::Error) { Error("Error sending email"); } } public Qty getOpenOrderQty(ItemId _itemid) { SalesLine salesLine; select sum(SalesQty) from salesLine where SalesLine.itemid == _itemid && SalesLine.salesStatus != SalesStatus::Invoiced; return salesLine.SalesQty; } public Amount exchangeRate (CurrencyCode _fromCurrency, CurrencyCode _toCurrency, Amount _amount, TransDate _transDate) { Amount exchRate; if(_amount == 0 ) { return 0; } else { CurrencyExchangeHelper currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), _transDate); exchRate = currencyExchangeHelper.calculateCurrencyToCurrency(_fromCurrency, _toCurrency, _amount, true); } return exchRate; } public str getMonthWeekDate(VendorReportingExpSchedule_ABC _schedule, TransDate _transDate) { str ret; switch (_schedule) { case VendorReportingExpSchedule_ABC::Daily: ret = date2Str(_transDate,321,2,2,2,2,4); break; case VendorReportingExpSchedule_ABC::Monthly: ret = int2Str(mthOfYr(_transDate)); break; case VendorReportingExpSchedule_ABC::Weekly: ret = int2Str(wkOfYr(_transDate)); break; } return ret; } public InventQty onHandInventory(ItemId _itemId, InventLocationId _inventLocationId, InventSiteId _inventSiteId) { InventOnhand inventOnhand; InventDim inventDimLoc; inventDimLoc.InventLocationId = _inventLocationId; inventDimLoc.InventSiteId = _inventSiteId; InventDim inventDim = inventDim::findOrCreate(inventDimLoc); InventDimParm inventDimParm; inventDimParm.initFromInventDim(inventDim); inventOnhand = InventOnhand::newParameters(_itemId, inventDim, inventDimParm); return inventOnhand.physicalInvent(); } public InventQtyReservPhysical availPhysical(ItemId _itemId, InventLocationId _inventLocationId, InventSiteId _inventSiteId) { InventOnhand inventOnhand; InventDim inventDimLoc; inventDimLoc.InventLocationId = _inventLocationId; inventDimLoc.InventSiteId = _inventSiteId; InventDim inventDim = inventDim::findOrCreate(inventDimLoc); InventDimParm inventDimParm; inventDimParm.initFromInventDim(inventDim); inventOnhand = InventOnhand::newParameters(_itemId, inventDim, inventDimParm); return inventOnhand.availPhysical(); } /// <summary> /// Contains the code that does the actual job of the class. /// </summary> public void run() { #OCCRetryCount try { ttsbegin; this.exportFileStandard(); ttscommit; } catch (Exception::Deadlock) { retry; } catch (Exception::UpdateConflict) { if (appl.ttsLevel() == 0) { if (xSession::currentRetryCount() >= #RetryNum) { throw Exception::UpdateConflictNotRecovered; } else { retry; } } else { throw Exception::UpdateConflict; } } } public boolean runsImpersonated() { return true; } public QueryRun queryRun() { return queryRun; } public boolean unpack(container _packedClass) { Integer version = conPeek(_packedClass,1); container packedQueryRun; switch (version) { case #CurrentVersion: [version, #CurrentList, packedQueryRun] = _packedClass; queryRun = new QueryRun(packedQueryRun); break; default: return false; } return true; } static VendorReportingFileExport_ABC construct() { return new VendorReportingFileExport(); } // Here goes a description of the class static ClassDescription description() { return "Vendor reporting excel export "; } static void main(Args args) { VendorReportingFileExport VendorReportingFileExport; ; VendorReportingFileExport = VendorReportingFileExport::construct(); if (VendorReportingFileExport.prompt()) VendorReportingFileExport.runOperation(); } [Hookable(false)] public final boolean isRetryable() { return true; } protected boolean canRunInNewSession() { return false; } void initQuery() { Query query = new Query(queryStr(VendorReportingQuery_ABC)); queryrun = new QueryRun(query); } public void initParmDefault() { super(); this.initQuery(); } }
Subscribe to:
Posts (Atom)