Hi experts,
I have a requirement for a project.
I need to fetch all the records of ledgerJournalTrans table based on a ledgerdimension field like I need to fetch all the records with the purpose ‘Z06’ irrespective of the main account.
I am using AX 2012 R3… Please don’t mind to ask any questions if the requirement is not clear to you people.
Thanks in advance…
Aravind.
static
void
SR_Extract_SegmentName_values(Args _args)
{
#define.Purpose(
“Purpose”
)
LedgerJournalTrans ledgerJournalTrans;
container value = conNull
();
// Dimension
DimensionStorage dimensionStorage;
// Segment
int
segmentCount, segmentIndex;
DimensionStorageSegment segment;
str 100
segmentName, MainAccount, segmentValue;
container
segments;
while select * from ledgerJournalTrans where ledgerJournalTrans.AcknowledgementDate ==
20\01\2011
{
// Get dimension storage
dimensionStorage = DimensionStorage::findById(ledgerJournalTrans.LedgerDimension);
if (dimensionStorage == null
)
{
throw error("@SYS83964"
);
}
// Add segments for all hierarchies
segments =
conNull
();
// Get segments
segmentCount = dimensionStorage.segmentCountForHierarchy(
1
);
for (segmentIndex = 1
; segmentIndex <= segmentCount; segmentIndex++)
{
// Get segment
segment = dimensionStorage.getSegmentForHierarchy(
1
, segmentIndex);
if (segment.parmDimensionAttributeValueId() != 0
)
{
// Get segment name
segmentName = DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId()).DimensionAttribute).Name;
// Add segment value
segmentValue = se
static void DimLedgerTrans(Args _args)
{
#define.Department(“Department”)
LedgerJournalTrans ledgerJournalTrans;
container value = conNull();
// Dimension
DimensionStorage dimensionStorage;
// Segment
int segmentCount, segmentIndex;
DimensionStorageSegment segment;
str 100 segmentName, MainAccount, segmentValue;
container segments;
while select * from ledgerJournalTrans where ledgerJournalTrans.AcknowledgementDate == 20\01\2011
{
// Get dimension storage
dimensionStorage = DimensionStorage::findById(ledgerJournalTrans.LedgerDimension);
if (dimensionStorage == null)
{
throw error("@SYS83964");
}
// Add segments for all hierarchies
segments = conNull();
// Get segments
segmentCount = dimensionStorage.segmentCountForHierarchy(1);
for (segmentIndex = 1; segmentIndex <= segmentCount; segmentIndex++)
{
// Get segment
segment = dimensionStorage.getSegmentForHierarchy(1, segmentIndex);
if (segment.parmDimensionAttributeValueId() != 0)
{
// Get segment name
segmentName = DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId ()).DimensionAttribute).Name;
// Add segment value
segmentValue = segment.parmDisplayValue();
if (segmentName == #Department && segmentValue == “026”)
{
info(strFmt("%1-----%2",ledgerJournalTrans.RecId,ledgerJournalTrans.LedgerDimension));
}
}
}
}
}
You can also try a query something like this,
LedgerJournalTrans ledgerJournalTrans;
DimensionAttributeLevelValueView levelValueView;
DimensionAttribute dimAttribute;
while select RecId from ledgerJournalTrans
where ledgerJournalTrans.AccountType == LedgerJournalACType::Ledger
exists join levelValueView
where levelValueView.ValueCombinationRecId == ledgerJournalTrans.LedgerDimension
&& levelValueView.DimensionAttribute != DimensionAttribute::getMainAccountDimensionAttribute()
&& levelValueView.DisplayValue == “014”
exists join dimAttribute
where dimAttribute.RecId == levelValueView.DimensionAttribute
&& dimAttribute.BackingEntityTableId == tableNum(OMOperatingUnit)
{
info(strFmt("%1",ledgerJournalTrans.RecId));
}
NOTE: I have never tested it
FYI,
You can also use query framework with \Classes\SysQuery\addDimensionAttributeRange for applying a range(defualt dimension, ledger dimesnion) for a particular attribute value