Journal Export V12 MT Cloud

My client wants to be able to access the details of all journals, manual and automatic, in an external file and view it in Excel (ideally). I understand that this has been requested as an enhancement but can anyone share any experiences/code snippet from an AEP delivery to meet this requirement?

Many Thanks

Mike

Inquiry Tab Placement.docx

Find more posts tagged with

Infor EPM Discussions

Comments

georges-vidal

HI, What about create a basic application studio report with export to Excel via a button. Should be a quick workarround.

martin-reischl

Hi Mike, Georges,

we did exactly that at a customer. You still need some code to query the journals and return them to a hyperbblock in XML format. Here is the code. You may not need all the filters, or different filters...

***

#define EngineVersion 5.0
#define RuntimeVersion 5.0

string CO_GetJournalGroupData(string alias, string cycle, string year, string period, string scenario, string topaccount, string group,
string topcomp, string topsegm, string topintco, string toppsegm, string topdetail, string toplevel,
string category, double dTimeshift)
@Description: "Get journal data for given context";
@Category: "Consolidation";
@Parameter[alias]: "OLAP connection name";
@Parameter[cycle]: "the Configuration set element to retrieve journals for";
@Parameter[year]: "the year to retrieve journals for";
@Parameter[period]: "the period to retrieve journals for";
@Parameter[scenario]: "the scenario to retrieve journals for";
@Parameter[topaccount]: "the account to retrieve journals for";
@Parameter[group]: "the group to retrieve journals for";
@Parameter[topcomp]: "the company to retrieve journals for";
@Parameter[topsegm]: "the segment to retrieve journals for";
@Parameter[topintco]: "the intercompany to retrieve journals for";
@Parameter[toppsegm]: "the partner segment to retrieve journals for";
@Parameter[topdetail]: "the detail to retrieve journals for";
@Parameter[toplevel]: "the level to retrieve journals for";
@Parameter[category]: "the category to retrieve journals for";
@Parameter[dTimeshift]: "Time shift from GMT to client time in hours";
@Returns: "XML with journal data";
{
/* // to test/debug
alias = "DEPMAPPS";
cycle = "SET_001";
year = "2021";
period = "12";
scenario = "ACT";
topaccount = "MA";
group = "GR0001";
topcomp = "Group Companies";
topsegm = "PHG_DIV";
topintco = "Sum";
toppsegm = "Sum";
topdetail = "60";
toplevel = "Cons. Other";
category = "*";
// */

string errorMessage = "";
int errorCode = 0;

// for potentially consolidated parameters, to resolve them to base elements as needed
StringList slAccount = CreateStringList();
StringList slComp = CreateStringList();
StringList slSegm = CreateStringList();
StringList slIntco = CreateStringList();
StringList slPSegm = CreateStringList();
StringList slDetail = CreateStringList();
StringList slLevel = CreateStringList();

// general journal parameters
string bookingNumber = "";
string description = "";
string summary = "";
string currency = "";
string carryForward = "";
string cancelled = "";
string bookingUser = "";
string bookingDate = "";

// line-specific parameters
string account = "";
string level = "";
string comp = "";
string segm = "";
string intco = "";
string segm2 = "";
string detail = "";
string partnersegm = "";
string partnersegm2 = "";
string segm3 = "";
string partnersegm3 = "";
string stage = "";
double debit = 0.0;
double credit = 0.0;

// for bookingdate conversion
DateTime dt = CreateDateTime();

OLAPConnection connection = OLAPCreatePooledNamedConnection(alias);
JournalStore journalStore = ConsolidationCreateJournalStore(connection);

// just to initialize variable
OLAPElement olEl = OLAPGetDimensionElement(connection, "BPDCYCLE", cycle);

// for each parameter: resolve unique names if necessary
if(StringStartsWith(cycle, "[")) { cycle = ResolveUniqueName(connection, cycle); }
if(StringStartsWith(year, "[")) { year = ResolveUniqueName(connection, year); }
if(StringStartsWith(period, "[")) { period = ResolveUniqueName(connection, period); }
if(StringStartsWith(scenario, "[")) { scenario = ResolveUniqueName(connection, scenario); }
if(StringStartsWith(group, "[")) { group = ResolveUniqueName(connection, group); }
if(StringStartsWith(topaccount, "[")) { topaccount = ResolveUniqueName(connection, topaccount); }
if(StringStartsWith(toplevel, "[")) { toplevel = ResolveUniqueName(connection, toplevel); }
if(StringStartsWith(category, "[")) { category = ResolveUniqueName(connection, category); }

// for potentially consolidated: check and resolve
if(topaccount!="*") {
olEl = OLAPGetDimensionElement(connection, "dACOUNT", topaccount);
if(OLAPGetElementType(connection, olEl)=="C") {
slAccount = ToStringList(OLAPGetChildElementList(connection, "dACOUNT", topaccount, true));
} else {
Append(slAccount, topaccount);
}
} else {
Append(slAccount, "*");
}

if(topcomp!="*") {
olEl = OLAPGetDimensionElement(connection, "dCOMPC", topcomp);
if(OLAPGetElementType(connection, olEl)=="C") {
slComp = ToStringList(OLAPGetChildElementList(connection, "dCOMPC", topcomp, true));
} else {
Append(slComp, topcomp);
}
} else {
Append(slComp, "*");
}

if(topsegm!="*") {
olEl = OLAPGetDimensionElement(connection, "dPSEGM", topsegm);
if(OLAPGetElementType(connection, olEl)=="C") {
slSegm = ToStringList(OLAPGetChildElementList(connection, "dPSEGM", topsegm, true));
} else {
Append(slSegm, topsegm);
}
} else {
Append(slSegm, "*");
}

if(topintco=="External") {
Append(slIntco, topintco);
Append(slIntco, "");
} else if(topintco=="*" or topintco=="Sum") {
Append(slIntco, "*");
} else {
olEl = OLAPGetDimensionElement(connection, "dINTCO", topintco);
if(OLAPGetElementType(connection, olEl)=="C") {
slIntco = ToStringList(OLAPGetChildElementList(connection, "dINTCO", topintco, true));
} else {
Append(slIntco, topintco);
}
}

if(toppsegm=="EXTERNAL") {
Append(slPSegm, toppsegm);
Append(slPSegm, "");
} else if(toppsegm=="*" or toppsegm=="Sum") {
Append(slPSegm, "*");
} else {
olEl = OLAPGetDimensionElement(connection, "dPRSEGM", toppsegm);
if(OLAPGetElementType(connection, olEl)=="C") {
slPSegm = ToStringList(OLAPGetChildElementList(connection, "dPRSEGM", toppsegm, true));
} else {
Append(slPSegm, toppsegm);
}
}

if(topdetail=="*" or topdetail=="60") {
Append(slDetail, "*");
} else {
olEl = OLAPGetDimensionElement(connection, "dDETAIL", topdetail);
if(OLAPGetElementType(connection, olEl)=="C") {
slDetail = ToStringList(OLAPGetChildElementList(connection, "dDETAIL", topdetail, true));
} else {
Append(slDetail, topdetail);
}
}

if(toplevel!="*") {
olEl = OLAPGetDimensionElement(connection, "dGLEVEL", toplevel);
if(OLAPGetElementType(connection, olEl)=="C") {
slLevel = ToStringList(OLAPGetChildElementList(connection, "dGLEVEL", toplevel, true));
} else {
Append(slLevel, toplevel);
}
} else {
Append(slLevel, "*");
}

/*NotifyDebug("Retrieving journals for: "+
"
cycle: "+cycle+
"
year: "+year+
"
period: "+period+
"
scenario: "+scenario+
"
group: "+group+
"
category: "+category+
"
slLevel: "+StringJoin(slLevel, ", ")+
"
slAccount: "+StringJoin(slAccount, ", ")+
"
slDetail: "+StringJoin(slDetail, ", ")+
"
slComp: "+StringJoin(slComp, ", ")+
"
slIntco: "+StringJoin(slIntco, ", ")+
"
slSegm: "+StringJoin(slSegm, ", ")+
"
slPSegm: "+StringJoin(slPSegm, ", "));*/

GroupJournalList journalList = ConsolidationGetGroupJournals(journalStore, cycle, year, period, scenario, group, ToStringList(category), slLevel, slAccount, slDetail, slComp, slIntco,
slSegm, ToStringList("*"), ToStringList("*"), slPSegm, ToStringList("*"), ToStringList("*"));

// start XML
XMLDocument xmlDoc = XMLCreateDocument();
XMLElement xmlTable = XMLCreateElement("Table");
XMLAddElement(xmlDoc, xmlTable);

// iterate through retrieved journals, create an element for each
int iRows = 1;
foreach(GroupJournal gJournal in journalList) {
//NotifyDebug("Journal "+iRows);

// retrieve general journal properties
bookingNumber = ConsolidationGetGroupJournalProperty(gJournal, "BookingNumber");
//NotifyDebug("-----------------------------------------------------------------");
//NotifyDebug(bookingNumber);
summary = ConsolidationGetGroupJournalProperty(gJournal, "Summary");
currency = ConsolidationGetGroupJournalProperty(gJournal, "Currency");
carryForward = ConsolidationGetGroupJournalProperty(gJournal, "CarryForward");
stage = ConsolidationGetGroupJournalProperty(gJournal, "Stage");
cancelled = ConsolidationGetGroupJournalProperty(gJournal, "cancelled");
bookingUser = ConsolidationGetGroupJournalProperty(gJournal, "bookinguser");
bookingDate = ConsolidationGetGroupJournalProperty(gJournal, "bookingdate");
level = ConsolidationGetGroupJournalProperty(gJournal, "level");
category = ConsolidationGetGroupJournalProperty(gJournal, "category");

// added 2022-07-01: convert the received bookingDate (always GMT) into the client's time
dt = ConvertToDateTime(bookingDate, "en-US");
dt = AddHours(dt, dTimeshift);
bookingDate = GetDateTimeAsString(dt, "G");

GroupJournalLineList gJournalLineList = ConsolidationGetGroupJournalLines(gJournal);
foreach(GroupJournalLine gJournalLine in gJournalLineList ) {

// retrieve line properties
account = ConsolidationGetGroupJournalLineProperty(gJournalLine, "account");
comp = ConsolidationGetGroupJournalLineProperty(gJournalLine, "company");
segm = ConsolidationGetGroupJournalLineProperty(gJournalLine, "Segment1");
intco = ConsolidationGetGroupJournalLineProperty(gJournalLine, "Intercompany");
segm2 = ConsolidationGetGroupJournalLineProperty(gJournalLine, "Segment2");
detail = ConsolidationGetGroupJournalLineProperty(gJournalLine, "Detail");
description = ConsolidationGetGroupJournalLineProperty(gJournalLine, "description");

//NotifyDebug("-------------------------------");
//NotifyDebug("Booking line: "+ToString(iLines));
//NotifyDebug("Company: "+comp);
//NotifyDebug("Segment: "+segm);
//NotifyDebug("Intercompany: "+intco);
//NotifyDebug("Partnersegment: "+segm2);
//NotifyDebug("Detail: "+detail);

partnersegm = ConsolidationGetGroupJournalLineProperty(gJournalLine, "psegment1");
partnersegm2 = ConsolidationGetGroupJournalLineProperty(gJournalLine, "psegment2");
segm3 = ConsolidationGetGroupJournalLineProperty(gJournalLine, "Segment3");
partnersegm3 = ConsolidationGetGroupJournalLineProperty(gJournalLine, "psegment3");
debit = ConsolidationGetGroupJournalLineNumericProperty(gJournalLine, "Debit");
credit = ConsolidationGetGroupJournalLineNumericProperty(gJournalLine, "Credit");

// add XML row
XMLElement row = XMLCreateElement("Row");
XMLAddElement(xmlTable, row);
XMLSetAttributeValue(row, "name", ToString(iRows));

XMLElement property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Account");
XMLSetAttributeValue(property, "value", account);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Bookinguser");
XMLSetAttributeValue(property, "value", bookingUser);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "BookingDate");
XMLSetAttributeValue(property, "value", bookingDate);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "member_caption");
XMLSetAttributeValue(property, "value", ToString(iRows));
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Category");
XMLSetAttributeValue(property, "value", category);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Summary");
XMLSetAttributeValue(property, "value", summary);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "BookingNumber");
XMLSetAttributeValue(property, "value", bookingNumber);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Name");
XMLSetAttributeValue(property, "value", summary);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Cancelled");
XMLSetAttributeValue(property, "value",cancelled);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Currency");
XMLSetAttributeValue(property, "value", currency);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Company");
XMLSetAttributeValue(property, "value", comp);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "GroupInfo");
XMLSetAttributeValue(property, "value", group);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Debit");
XMLSetAttributeValue(property, "value", ToString(debit));
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Credit");
XMLSetAttributeValue(property, "value", ToString(credit));
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "YearInfo");
XMLSetAttributeValue(property, "value", year);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Period");
XMLSetAttributeValue(property, "value", period);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "carryForward");
XMLSetAttributeValue(property, "value", carryForward);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "LevelInfo");
XMLSetAttributeValue(property, "value", level);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Scenario");
XMLSetAttributeValue(property, "value", scenario);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "stage");
XMLSetAttributeValue(property, "value", stage);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Intercompany");
XMLSetAttributeValue(property, "value", intco);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Detail");
XMLSetAttributeValue(property, "value", detail);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Description");
XMLSetAttributeValue(property, "value", description);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Segment1");
XMLSetAttributeValue(property, "value", segm);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "psegment1");
XMLSetAttributeValue(property, "value", partnersegm);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Segment2");
XMLSetAttributeValue(property, "value", segm2);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "psegment2");
XMLSetAttributeValue(property, "value", partnersegm2);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "Segment3");
XMLSetAttributeValue(property, "value", segm3);
XMLAddElement(row, property);

property = XMLCreateElement("Property");
XMLSetAttributeValue(property, "name", "psegment3");
XMLSetAttributeValue(property, "value", partnersegm3);
XMLAddElement(row, property);
iRows = iRows + 1;
}
}

return ToString(xmlDoc);
}

Mike Potashnick

Thanks Martin,. We will use that and add your name to the code in recognition. Drinks available when you next visit the UK!!

0911060553270289.pdf

martin-reischl

Actually it is all Kai's code ... the honour belongs to him. But the drinks are mine

Copyright © 2025 Infor. All rights reserved.