CREATE TABLE TSTRowCounts(RowCount1 BIGINT,TableName VARCHAR(128))
EXEC sp_MSForEachTable 'INSERT INTO TSTRowCounts
SELECT COUNT_BIG(*) AS RowCount1,
''?'' as TableName FROM ?'
SELECT top 100 TableName, RowCount1 FROM TSTRowCounts ORDER BY RowCount1 DESC
select * from TSTRowCounts
where RowCount1 >100000
Pre requistie:
Customer, Item Number, Retail channel, Currency
Price: Original price (Sales price - Discount)
Code:
Class declaration
using System.Reflection;
public AmountCur GetRetailPriceAndDiscounts(
str _customer,
str _itemId,
RetailChannelTable _retailChannel,
list _affiliationList = null,
Str _currency = Ledger::accountingCurrency(),
str _orderNo = '')
{
Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine crtSalesLine;
Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction crtSalesTransaction;
Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngineDiagnosticsObject diagnosticsObjectPrices;
Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngineDiagnosticsObject diagnosticsObjectDiscounts;
Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesAffiliationLoyaltyTier crtSalesAffiliationList;
Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.IPricingDataAccessor pricingManager;
container resultCon = conNull();
CLRObject clrSalesAffiliations;
CLRObject clrSalesLines;
RetailCustAffiliation retailCustAffiliation;
str defaultSalesUnit;
str lineId;
RetailTempOrderItem tempOrderItem;
System.DateTime dateTime, dateTimeUtc;
System.DateTimeOffset dateTimeOffsetUtc, dateTimeOffsetChannel;
RetailCurrencyOperations currencyAndRoundingHelper;
CLRObject enumeratorSalesLine;
RetailTransactionId transactionIdString = System.Guid::NewGuid().ToString('N');
CustTable custTable = custTable::find(_customer);
InventTable inventTable =InventTable::find(_itemId);
CurrencyCode channelCurrency = _currency;
int salesLinesOrderField = 1;
utcDateTime simulationDateTime = DateTimeUtil::utcNow();
const str discountOfferIdProperty = 'DiscountOfferId';
const str discountCodeProperty = 'Code';
const str salesTransactionCouponsProperty = 'Coupons';
defaultSalesUnit = InventTableModule::find(_itemId, ModuleInventPurchSales::Sales).UnitId;
crtSalesTransaction = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesTransaction();
crtSalesTransaction.set_Id(transactionIdString);
clrSalesLines = crtSalesTransaction.get_SalesLines();
crtSalesLine = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesLine();
crtSalesLine.set_ItemId(_itemId);
crtSalesLine.set_InventoryDimensionId('');
crtSalesLine.set_Quantity(1);
crtSalesLine.set_SalesOrderUnitOfMeasure(defaultSalesUnit);
crtSalesLine.set_OriginalSalesOrderUnitOfMeasure(defaultSalesUnit);
lineId = strRFix(int2str(salesLinesOrderField), 3, '0');
crtSalesLine.set_LineId(lineId);
crtSalesLine.set_LineNumber(salesLinesOrderField);
clrSalesLines.Add(crtSalesLine);
crtSalesTransaction.set_IsTaxIncludedInPrice(_retailChannel.PriceIncludesSalesTax);
crtSalesTransaction.set_CustomerId(_customer);
clrSalesAffiliations = crtSalesTransaction.get_AffiliationLoyaltyTierLines();
// Add customer retail affiliations.
If(_affiliationList == null)
{
while select retailCustAffiliation
where retailCustAffiliation.CustAccountNum == _customer
{
crtSalesAffiliationList = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesAffiliationLoyaltyTier();
crtSalesAffiliationList.set_AffiliationId(retailCustAffiliation.RetailAffiliationId);
clrSalesAffiliations.Add(crtSalesAffiliationList);
}
}
else
{
RetailAffiliation retailAffiliations;
ListEnumerator enumerator;
enumerator = _affiliationList.getEnumerator();
while(enumerator.moveNext())
{
retailAffiliations.clear();
retailAffiliations = RetailAffiliation::findByName(enumerator.current(), false);
if(retailAffiliations)
{
crtSalesAffiliationList = new Microsoft.Dynamics.Commerce.Runtime.DataModel.SalesAffiliationLoyaltyTier();
crtSalesAffiliationList.set_AffiliationId(retailAffiliations.RecId);
clrSalesAffiliations.Add(crtSalesAffiliationList);
}
}
}
dateTime = Global::utcDateTime2SystemDateTime(simulationDateTime);
dateTimeUtc = new System.DateTime(dateTime.get_Ticks(), System.DateTimeKind::Utc);
dateTimeOffsetUtc = new System.DateTimeOffset(dateTimeUtc);
dateTimeOffsetChannel = System.TimeZoneInfo::ConvertTimeBySystemTimeZoneId(dateTimeOffsetUtc, _retailChannel.ChannelTimeZoneInfoId);
if(_orderNo)
{
//Create CRT for COUPONS
CLRObject couponCollection;
PropertyInfo propertyInfo = crtSalesTransaction.GetType().GetProperty(salesTransactionCouponsProperty);
if (propertyInfo)
{
couponCollection = System.Activator::CreateInstance(propertyInfo.PropertyType);
propertyInfo.SetValue(crtSalesTransaction, couponCollection);
}
else
{
throw error("@Retail:CouponsCreationError");
}
System.Type couponType;
if (couponCollection)
{
System.Type[] argumentTypes = couponCollection.GetType().GetGenericArguments();
if (argumentTypes && argumentTypes.Length > 0)
{
couponType = argumentTypes.GetValue(0);
}
}
if (couponType)
{
PropertyInfo discountOfferIdPropertyInfo = couponType.GetProperty(discountOfferIdProperty);
PropertyInfo codePropertyInfo = couponType.GetProperty(discountCodeProperty);
if (discountOfferIdPropertyInfo && codePropertyInfo)
{
RetailCouponUsage retailCouponUsage;
RetailCouponCodeTable retailCouponCodeTable;
RetailCoupon retailCoupon;
while select retailCouponUsage
where retailCouponUsage.SalesId == _orderNo
join retailCouponCodeTable
where retailCouponCodeTable.CouponCodeId == retailCouponUsage.CouponCodeId
join retailCoupon
where retailCoupon.CouponNumber == retailCouponCodeTable.CouponNumber
{
SalesTable salesTable = SalesTable::find(_orderNo);
RetailSalesTable retailSalesTable = RetailSalesTable::findSalesTable(salesTable);
if(!retailSalesTable.RetailChannel)
{
throw error("@Retail:CouponChannelNotFound");
}
// Add coupon to list if valid.
if (RetailCouponHelper::validateCoupon(retailCouponCodeTable, retailSalesTable.RetailChannel)
&& RetailCouponHelper::ValidateCouponLimits(retailCouponCodeTable.CouponCodeId, _retailChannel.RetailChannelId, _customer, _orderNo))
{
CLRObject coupon = System.Activator::CreateInstance(couponType);
discountOfferIdPropertyInfo.SetValue(coupon, retailCoupon.DiscountOfferId);
codePropertyInfo.SetValue(coupon, retailCouponCodeTable.CouponCode);
couponCollection.Add(coupon);
}
}
}
}
}
pricingManager = new RetailPricingDataManagerSimulator(_retailChannel.RecId, transactionIdString, false, tempOrderItem, true);
tempOrderItem.clear();
tempOrderItem.itemId = _itemId;
tempOrderItem.Product = inventTable.Product;
tempOrderItem.insert();
currencyAndRoundingHelper = new RetailCurrencyOperations(CompanyInfoHelper::standardCurrency());
Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::SetCollectDiagnostics(crtSalesTransaction, true);
// Calculate prices.
Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculatePricesForTransaction(
crtSalesTransaction,
pricingManager,
currencyAndRoundingHelper,
custTable.PriceGroup,
channelCurrency,
dateTimeOffsetUtc); // dateTimeOffsetChannel (error at runtime)- replace with dateTimeOffsetUtc
diagnosticsObjectPrices = Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::GetPricingEngineDiagnosticsObject(crtSalesTransaction);
Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::SetCollectDiagnostics(crtSalesTransaction, true);
// Calculate discounts.
Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::CalculateDiscountsForLines(
pricingManager,
crtSalesTransaction,
currencyAndRoundingHelper,
channelCurrency,
custTable.LineDisc,
custTable.MultiLineDisc,
custTable.EndDisc,
true,
false,
dateTimeOffsetUtc); // dateTimeOffsetChannel (error at runtime)- replace with dateTimeOffsetUtc
diagnosticsObjectDiscounts = Microsoft.Dynamics.Commerce.Runtime.Services.PricingEngine.PricingEngine::GetPricingEngineDiagnosticsObject(crtSalesTransaction);
clrSalesLines = crtSalesTransaction.get_SalesLines();
enumeratorSalesLine = clrSalesLines.GetEnumerator();
while (enumeratorSalesLine.MoveNext())
{
crtSalesLine = enumeratorSalesLine.get_Current();
resultCon += [crtSalesLine.get_Price(), crtSalesLine.get_DiscountAmount()];
}
return crtSalesLine.get_Price() - crtSalesLine.get_DiscountAmount(); //Considering price - discount as original price
}
Reference: https://www.codecrib.com/2021/11/changes-to-internal-access-modifier-in-xpp.html
These are new two operators added to X++ libraries.
*=
/=
Example:
X *= 2
X /= 2
These are short for X = X * 2 and X = X / 2.
Login to D365FO URL,
Navigate to Sales order (Sample)
Create new order
