Create formatted excel report using Database activity under Document Flow

I have an EXE file, which is using the VBA macro file at shared location and populate the formatted excel report using the BOD created from Sales Portal. That EXE file is used in SQL Server Stored Procedure. When I am using the Stored Procedure from SQL Server it is working properly and generating the formatted excel report, but when I am trying to use the same stored procedure within Document Flow Database Activity, I am getting error as mentioned below

com.infor.ion.container.common.exception.ServiceContainerException: Failed executing sql statement for logicalId 'cpqaccess'. Cause: First entry in the resultSet should be a <DataArea> tag. <DataArea> Tag not found.
at com.infor.ion.plugins.database.adapter.internal.JDBCAdapterDataSource.rollBack(JDBCAdapterDataSource.java:438)
at com.infor.ion.plugins.database.adapter.internal.IntermediateHandler.read(IntermediateHandler.java:285)
at com.infor.ion.plugins.database.adapter.internal.IntermediateHandler.handleMessage(IntermediateHandler.java:158)
at com.infor.ion.plugins.database.adapter.internal.IntermediateHandler.handleMessage(IntermediateHandler.java:136)
at com.infor.ion.plugins.database.adapter.internal.DatabaseAdapter.handleMessage(DatabaseAdapter.java:502)
at com.infor.ion.container.element.base.adapter.BaseElement.process(BaseElement.java:105)
at com.infor.ion.broker.process.channel.queue.internal.MessageDistributor.handleMessage(MessageDistributor.java:666)
at com.infor.ion.broker.process.channel.queue.internal.MessageDistributor.run(MessageDistributor.java:497)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.RuntimeException: First entry in the resultSet should be a <DataArea> tag. <DataArea> Tag not found.
at com.infor.ion.plugins.database.adapter.internal.ResultSet2Bod.buildElementsFromVarChar(ResultSet2Bod.java:369)
at com.infor.ion.plugins.database.adapter.internal.ResultSet2Bod.splitResultSet(ResultSet2Bod.java:305)
at com.infor.ion.plugins.database.adapter.internal.ResultSet2Bod.buildMessage(ResultSet2Bod.java:268)
at com.infor.ion.plugins.database.adapter.internal.IntermediateHandler.read(IntermediateHandler.java:277)

Anyone has any alternative solution for the same, Please inform.

Find more posts tagged with

ION Connect

Accepted answers

All comments

Legacy Contributor

Hi Arya,

Did your stored procedure account for preparing a Data Area that would be encapsulated in your BOD? See an example from the Infor ION Technology Connectors Administration Guide:

SELECT MySalesOrder.orderNumber as orderID, MySalesOrder.orderStatus, MySalesOrder.orderDateTime, MySalesOrder.customer, MySalesOrderLine.lineNumber, MySalesOrderLine.item, MySalesOrderLine.quantity, MySalesOrderLine.price FROM Orders AS MySalesOrder, OrderLines AS MySalesOrderLine WHERE MySalesOrderLine.orderNumber = MySalesOrder.orderNumber FOR XML AUTO, ELEMENTS, root('DataArea');

Legacy Contributor

Hi Mike,

No. The Stored Procedure is taking the BOD as a parameter and then just return it without any modification. The Stored Procedure is as below

CREATE PROCEDURE [dbo].[uspNCPFileGeneration] @xmlData XML

AS

SET NOCOUNT ON

DECLARE @sql1 VARCHAR(8000)

DECLARE @QuoteNumber VARCHAR(1000)

SELECT

@QuoteNumber = XT.XC.value('(AlternateDocumentID/ID/text())[1]', 'VARCHAR(100)')

FROM

@xmlData.nodes('/DataArea/NCPSalesOrder/SalesOrderHeader') AS XT(XC)

SET @sql1 = 'COPY /Y "Z:\TemplateNCPDevTestCost Analysis v1.xlsx" "z:NCPWorkflowDataDevTestProcessTemplate"'

EXEC Xp_cmdshell @sql1

--SET @sql1 = ''

--EXEC PCMSalesPortal81.dbo.pr_ExcelImportAndRunMacro

-- 'z:NCPWorkflowDataDevTestProcessTemplateCost Analysis v1.xlsx',

-- '\172.18.4.215ITReports_originalsExcel_MacrosNCPAnalysisgenerateReportDevTestV1.txt';

SET @sql1 = ''

set @sql1= 'ren "z:NCPWorkflowDataDevTestProcessTemplateCost Analysis v1.xlsx" "NCP - '+@QuoteNumber+ '.xlsx"'

EXEC xp_cmdshell @sql1

set @sql1= 'move /Y "z:NCPWorkflowDataDevTestProcessTemplateNCP - '+@QuoteNumber+ '.xlsx" "z:NCPWorkflowDataTestEnvironment"'

EXEC xp_cmdshell @sql1

SELECT @xmlData

Legacy Contributor

Hi Arya,

I highly recommend reading through the technology connectors' documentation. It covers expected formatting of a prepared dataset and how that data must be prepared in order for ION to retrieve the content successfully.

See page 45+ in the Infor ION Technology Connectors Administration Guide.

From the error output you posted, this caught my attention: Failed executing sql statement for logicalId 'cpqaccess'. Cause: First entry in the resultSet should be a <DataArea> tag. <DataArea> Tag not found.

Take another look at the example I provided earlier:

SELECT Orders.orderNumber, Orders.orderStatus, Orders.orderDateTime, Orders.customer, OrderLines.lineNumber, OrderLines.item, OrderLines.quantity, OrderLines.price FROM Orders, OrderLines WHERE OrderLines.orderNumber = Orders.orderNumber FOR XML AUTO, ELEMENTS, root('DataArea');

You'll see that the example stored procedure is preparing the query result data as XML and nesting in within a root element named "DataArea". ION's expectation is that the stored procedure is producing a ready-to-go XML payload directly from the database server.

Legacy Contributor

Hi Mike,

The problem here is the BOD consists of other information as well. So I can not create the BOD using the query. Please see the BOD as below.

<SyncNCPSalesOrder xmlns="schema.infor.com/.../2" xmlns:xsi="www.w3.org/.../XMLSchema-instance" xmlns:xsd="www.w3.org/.../XMLSchema" releaseID="9.2" versionID="2.5.0">

<ApplicationArea>

<Sender>

<LogicalID>lid://infor.erp.sales_portal_adpdevtest</LogicalID>

<ComponentID>SalesPortalNCPSalesOrder</ComponentID>

<ConfirmationCode>OnError</ConfirmationCode>

</Sender>

<CreationDateTime>2018-08-07T10:45:28.86Z</CreationDateTime>

<BODID>infor-nid:infor:Na:Na:DTQB000321_1:?NCPSalesOrder&amp;verb=Sync</BODID>

</ApplicationArea>

<DataArea>

<Sync>

<TenantID>infor</TenantID>

<AccountingEntityID>Na</AccountingEntityID>

<LocationID>Na</LocationID>

<ActionCriteria>

<ActionExpression actionCode="Add" />

</ActionCriteria>

</Sync>

<NCPSalesOrder>

<SalesOrderHeader>

<CustomerParty>

<PartyIDs>

<ID>CDBUS</ID>

</PartyIDs>

</CustomerParty>

<DocumentID>

<ID>DTQB000321_1</ID>

</DocumentID>

<AlternateDocumentID agencyRole="Customer">

<ID>DTQB000321_1</ID>

</AlternateDocumentID>

<UserArea>

<Property>

<NameValue name="CompanyName" type="StringType">Dealer_US</NameValue>

</Property>

<Property>

<NameValue name="ProjectImportance" type="StringType">Neither</NameValue>

</Property>

<Property>

<NameValue name="ProjectNetAmountWithoutNCP" type="DecimalBaseType">108033.8717</NameValue>

</Property>

<Property>

<NameValue name="ProjectQuoteNumber" type="StringType">DTQB000321_1</NameValue>

</Property>

<Property>

<NameValue name="RequesterName" type="StringType">Kumari, Aparna</NameValue>

</Property>

<Property>

<NameValue name="RequesterEmail" type="StringType">akumari@armstrongfluidtechnology.com</NameValue>

</Property>

<Property>

<NameValue name="RequesterPhone" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="Market" type="StringType">US</NameValue>

</Property>

<Property>

<NameValue name="TechSupportEmail" type="StringType">int-PEG@armstrongfluidtechnology.com </NameValue>

</Property>

</UserArea>

<TotalAmount>108033.87</TotalAmount>

<OrderDateTime>2018-08-07T07:46:26.2417644Z</OrderDateTime>

<Status>

<Code>11</Code>

</Status>

</SalesOrderHeader>

<SalesOrderLine>

<LineNumber>3</LineNumber>

<Item>

<ItemID>

<ID>Design Envelope and Commercial Pumps</ID>

</ItemID>

</Item>

<Description>Config Part

Design Envelope PiB

Constant Speed PiB

</Description>

<Quantity unitCode="EA">1</Quantity>

<UnitPrice>

<Amount currencyID="USD">23494.489704</Amount>

<PerQuantity unitCode="EA">1</PerQuantity>

</UnitPrice>

<UserArea>

<Property>

<NameValue name="ExtendedPrice" type="DecimalBaseType">23494.49</NameValue>

</Property>

<Property>

<NameValue name="NetPrice" type="DecimalBaseType">23494.49</NameValue>

</Property>

<Property>

<NameValue name="NCPStatus" type="StringType">NCP Required</NameValue>

</Property>

<Property>

<NameValue name="NCPValue" type="DecimalBaseType">0</NameValue>

</Property>

<Property>

<NameValue name="PriceCollection" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="NCPLeadTimeAdder" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="NCPCostAdder" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="Justification" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="Market" type="StringType">US</NameValue>

</Property>

<Property>

<NameValue name="CustomOption" type="StringType">None</NameValue>

</Property>

<Property>

<NameValue name="CustomRequest" type="StringType">Test ncp with None custom option</NameValue>

</Property>

<Property>

<NameValue name="Model" type="StringType">Design Envelope Sensorless 4372 0205-005.0</NameValue>

</Property>

</UserArea>

<DistributedCharge sequence="1">

<Description>CS Discount</Description>

<Amount currencyID="USD">1</Amount>

<Calculation>

<RateNumeric>0</RateNumeric>

</Calculation>

<UnitAdjustmentAmount currencyID="USD">0</UnitAdjustmentAmount>

<CalculationMethodCode>Percent</CalculationMethodCode>

</DistributedCharge>

<RequestedShipDateTime>0001-01-01T05:00:00Z</RequestedShipDateTime>

<ItemConfiguration>

<ConfigurationComponent>

<ConfiguratorLinks>

<ConfigurationID>1A16185E-E6A1-4553-A475-BFB2A923C6FF</ConfigurationID>

<ConfigurationDetailID>46c8bf11-ea53-4ce2-978b-921adbaf1cfe</ConfigurationDetailID>

</ConfiguratorLinks>

<Sequence>1</Sequence>

<ParentComponent>1</ParentComponent>

<ParentFeature>1</ParentFeature>

<Item>

<ItemID>

<ID>46c8bf11-ea53-4ce2-978b-921adbaf1cfe</ID>

</ItemID>

</Item>

</ConfigurationComponent>

</ItemConfiguration>

<PaymentTerm>

<Discount>

<DiscountPercent>1</DiscountPercent>

</Discount>

</PaymentTerm>

</SalesOrderLine>

<SalesOrderLine>

<LineNumber>5</LineNumber>

<Item>

<ItemID>

<ID>Design Envelope iFMS</ID>

</ItemID>

</Item>

<Description>Design Envelope iFMS</Description>

<Quantity unitCode="EA">1</Quantity>

<UnitPrice>

<Amount currencyID="USD">81090.381956</Amount>

<PerQuantity unitCode="EA">1</PerQuantity>

</UnitPrice>

<UserArea>

<Property>

<NameValue name="ExtendedPrice" type="DecimalBaseType">81090.38</NameValue>

</Property>

<Property>

<NameValue name="NetPrice" type="DecimalBaseType">81090.38</NameValue>

</Property>

<Property>

<NameValue name="NCPStatus" type="StringType">NCP Required</NameValue>

</Property>

<Property>

<NameValue name="NCPValue" type="DecimalBaseType">0</NameValue>

</Property>

<Property>

<NameValue name="PriceCollection" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="NCPLeadTimeAdder" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="NCPCostAdder" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="Justification" type="StringType"></NameValue>

</Property>

<Property>

<NameValue name="Market" type="StringType">US</NameValue>

</Property>

<Property>

<NameValue name="CustomOption" type="StringType" />

</Property>

<Property>

<NameValue name="CustomRequest" type="StringType" />

</Property>

<Property>

<NameValue name="Model" type="StringType">iFMS-4-B-0205H-003.0-A-2-X-A</NameValue>

</Property>

</UserArea>

<DistributedCharge sequence="1">

<Description>CS Discount</Description>

<Amount currencyID="USD">1</Amount>

<Calculation>

<RateNumeric>0</RateNumeric>

</Calculation>

<UnitAdjustmentAmount currencyID="USD">0</UnitAdjustmentAmount>

<CalculationMethodCode>Percent</CalculationMethodCode>

</DistributedCharge>

<RequestedShipDateTime>0001-01-01T05:00:00Z</RequestedShipDateTime>

<ItemConfiguration>

<ConfigurationComponent>

<ConfiguratorLinks>

<ConfigurationID>1A16185E-E6A1-4553-A475-BFB2A923C6FF</ConfigurationID>

<ConfigurationDetailID>5b7ff468-a77d-4611-960d-847283855936</ConfigurationDetailID>

</ConfiguratorLinks>

<Sequence>1</Sequence>

<ParentComponent>1</ParentComponent>

<ParentFeature>1</ParentFeature>

<Item>

<ItemID>

<ID>5b7ff468-a77d-4611-960d-847283855936</ID>

</ItemID>

</Item>

</ConfigurationComponent>

</ItemConfiguration>

<PaymentTerm>

<Discount>

<DiscountPercent>1</DiscountPercent>

</Discount>

</PaymentTerm>

</SalesOrderLine>

</NCPSalesOrder>

</DataArea>

</SyncNCPSalesOrder>

Copyright © 2025 Infor. All rights reserved.