Stored Procdure removes namespace and renders BOD unusable

Good morning

I need to change the AccountEntityID in a Sync.ItemMaster BOD and do NOT want to have that in the mapping as constant.

Therefore, in my document flow, I created a connection point with a stored procedure that uses this statement to change the company number:

set @xmlout.modify('replace value of (/DataArea/Sync/AccountingEntityID[1]/text())[1] with sql:variable("@comp")';

It works perfectly, BUT, it removes the namespace, version (red) etc. behind SyncItemMaster:

BEFORE:

<?xml version="1.0"?>
<SyncItemMaster xmlns="">schema.infor.com/.../2" xmlns:xsi="">www.w3.org/.../XMLSchema-instance" xsi:schemaLocation="schema.infor.com/.../2 ">schema.infor.com/.../SyncItemMaster.xsd" xmlns:xsd="">www.w3.org/.../XMLSchema" releaseID="9.2" versionID="2.14.0">
<ApplicationArea>
<Sender>
<LogicalID>lid://infor.ln.ln_gc</LogicalID>
<ComponentID>erp</ComponentID>
<ConfirmationCode>OnError</ConfirmationCode>
</Sender>
<CreationDateTime>2022-12-21T15:58:35Z</CreationDateTime>
<BODID>infor-nid:infor:112::P01031012:?ItemMaster&verb=Sync</BODID>
</ApplicationArea>
<DataArea>
<Sync>
<TenantID>infor</TenantID>
<AccountingEntityID>112</AccountingEntityID>

....

AFTER:

<SyncItemMaster releaseID="9.2">
<ApplicationArea>
<Sender>
<LogicalID>infor.databaseartikel_bwtinterface_artikel_bwtinterface_gc_ln_artikel</LogicalID>
<ComponentID>External</ComponentID>
<ConfirmationCode>OnError</ConfirmationCode>
</Sender>
<CreationDateTime>2022-12-21T15:58:37.581Z</CreationDateTime>
<BODID>infor.databaseartikel_bwtinterface_artikel_bwtinterface_gc_ln_artikel:1671638317581:1150488:0</BODID>
</ApplicationArea>
<DataArea>
<Sync>
<TenantID>infor</TenantID>
<AccountingEntityID>440</AccountingEntityID>

This leads to the effect that the mapping that follows returns no data any more.

How can I keep the red part despite using a stored procedure?

Or is there another dynamic way of change the company number (AccountEntityID)? (I read this from a table from within Infor LN database)

Thank you and kind regards

Matthias

Find more posts tagged with

ION Connect

Accepted answers

All comments

unknown

Hi Matthias,

I believe in LN system accounting entity can be configurable, but not sure if it can be mapped to a company. if this is possible then you can change it in LN itself.

otherwise, include company number in the connection point name in ION. each LN company have a separate connection point in ION.

example: if the current connection point name is ln_gc change it to ln_gc_440. This will reflect in ItemMaster BOD LogicalID value as lid://infor.ln.ln_gc_440

instead of stored procedure add a mapper in document flow and calculate AccountingEntity based on LogicalID value.

matthias-baumann

Hi Sadik

Thanks a lot for your suggestion!

The target company 440 is another company of our group in another country that uses LN, but it is no way linked to our system. I stored the BOD on our fileserver for them to pick up. So I cannot create a connection point for 440 as ION does not have access to this company.

However, I might look into changing the company on their system and provide the XML BOD file still with our company number.

Even if I get this to work, it would be nice to know how to influence a BOD via SQL stored procedure for other cases in the future.

So, I tried another workaround in the meantime: I made another mapping Sync to Sync BOD without any changes. This added the header attributes, but it also added an empty xmlns attribute, that leads to another error thereafter:

<?xml version="1.0" encoding="UTF-8"?>
<SyncItemMaster xmlns="">schema.infor.com/.../2" xmlns:xs="">www.w3.org/.../XMLSchema" releaseID="9.2">
<ApplicationArea xmlns="">

No idea, why the mapping does this, nor how to avoid it. Any idea?

Thanks again and kind regards

Matthias

unknown

Hi Matthias,

thanks for sharing additional details, so you are looking for a help on SQL query.

I am able to get the desired result with following sample code.

DECLARE @comp varchar(10)='440'

DECLARE @myDoc XML =N'<?xml version="1.0"?>
<SyncItemMaster xmlns="">schema.infor.com/.../2" xmlns:xsi="">www.w3.org/.../XMLSchema-instance" xsi:schemaLocation="schema.infor.com/.../2 ">schema.infor.com/.../SyncItemMaster.xsd" xmlns:xsd="">www.w3.org/.../XMLSchema" releaseID="9.2" versionID="2.14.x">
<ApplicationArea>
<Sender>
<LogicalID>lid://infor.ln.ln01/0060</LogicalID>
<ComponentID>erp</ComponentID>
<ConfirmationCode>OnError</ConfirmationCode>
</Sender>
<CreationDateTime>2022-11-17T15:15:08Z</CreationDateTime>
<BODID>infor-nid:ICSEUWNA001_TST:0060::000001_02:?ItemMaster&verb=Sync</BODID>
</ApplicationArea>
<DataArea>
<Sync>
<TenantID>ICSEUWNA001_TST</TenantID>
<AccountingEntityID>0060</AccountingEntityID>
<LocationID>
</LocationID>
<ActionCriteria>
<ActionExpression actionCode="Replace"/>
</ActionCriteria>
</Sync>
<ItemMaster>
<ItemMasterHeader>
<ItemID>
<ID accountingEntity="0060" lid="lid://infor.ln.ln01/0060" variationID="45340">000001_02</ID>
</ItemID>
<DisplayID>000001_02</DisplayID>
<GTIN>
</GTIN>
<LastModificationPerson>
<IDs>
<ID accountingEntity="0060" lid="lid://infor.ln.ln01/0060">JVH</ID>
</IDs>
<Name>jvheesen</Name>
</LastModificationPerson>
<ServiceIndicator>false</ServiceIndicator>
<Description>test</Description>
<Note/>
<Classification>
<Codes>
<Code listID="Item Types" sequence="1">Product</Code>
<Code listID="Item Groups" sequence="2" accountingEntity="0060">100000</Code>
<Code listID="MRO Classes" sequence="3">*</Code>
<Code listID="Order Policy" sequence="4">Anonymous</Code>
<Code listID="LN Service Item Groups" sequence="5" accountingEntity="0060">001</Code>
<Code listID="LN Configuration Controlled" sequence="6">Serialized</Code>
</Codes>
</Classification>
<HazardousMaterialIndicator>false</HazardousMaterialIndicator>
<LeadTimeDuration>PT0H0M0S</LeadTimeDuration>
<BackFlushedIndicator>false</BackFlushedIndicator>
<TrackingIndicator>true</TrackingIndicator>
<ItemStatus>
<Code listID="Item Status">Open</Code>
<TimePeriod>
<StartDateTime>1970-01-01T00:00:01Z</StartDateTime>
<EndDateTime>9999-12-31T00:00:00Z</EndDateTime>
</TimePeriod>
<ArchiveIndicator>false</ArchiveIndicator>
</ItemStatus>
<BaseUOMCode>PCS</BaseUOMCode>
<StorageUOMCode>PCS</StorageUOMCode>
<LastModificationDateTime>2022-11-17T15:15:08Z</LastModificationDateTime>
<UOMGroupReference>
<ID accountingEntity="0060" lid="lid://infor.ln.ln01/0060">000010</ID>
</UOMGroupReference>
<ItemValue>
<UnitValue>
<Amount currencyID="EUR">0</Amount>
<BaseAmount currencyID="EUR">0</BaseAmount>
<ReportAmount currencyID="EUR">0</ReportAmount>
<PerQuantity unitCode="PCS">1</PerQuantity>
<PerBaseUOMQuantity unitCode="PCS">1</PerBaseUOMQuantity>
</UnitValue>
<CostingMethodCode>Standard</CostingMethodCode>
</ItemValue>
<SerialControlIndicator>true</SerialControlIndicator>
<LotControlIndicator>false</LotControlIndicator>
<ConfiguredItemIndicator>false</ConfiguredItemIndicator>
<UserArea>
<Property>
<NameValue name="ln.SalesIndicator" type="IndicatorType">true</NameValue>
</Property>
<Property>
<NameValue name="ln.ProductionIndicator" type="IndicatorType">true</NameValue>
</Property>
<Property>
<NameValue name="ln.CostingIndicator" type="IndicatorType">false</NameValue>
</Property>
<Property>
<NameValue name="ln.WarehousingIndicator" type="IndicatorType">true</NameValue>
</Property>
<Property>
<NameValue name="ln.ProjectIndicator" type="IndicatorType">false</NameValue>
</Property>
<Property>
<NameValue name="ln.QualityIndicator" type="IndicatorType">false</NameValue>
</Property>
<Property>
<NameValue name="ln.PurchaseIndicator" type="IndicatorType">true</NameValue>
</Property>
<Property>
<NameValue name="ln.OrderingIndicator" type="IndicatorType">true</NameValue>
</Property>
<Property>
<NameValue name="ln.PlanningIndicator" type="IndicatorType">false</NameValue>
</Property>
<Property>
<NameValue name="ln.ServiceIndicator" type="IndicatorType">true</NameValue>
</Property>
<Property>
<NameValue name="ln.FreightIndicator" type="IndicatorType">false</NameValue>
</Property>
<Property>
<NameValue name="ln.ToolIndicator" type="IndicatorType">false</NameValue>
</Property>
<Property>
<NameValue name="ln.ReleaseToWarehousing" type="IndicatorType">true</NameValue>
</Property>
<Property>
<NameValue name="ln.DerivedFromItem" type="StringType">000001_02</NameValue>
</Property>
<Property>
<NameValue name="ln.SearchKey1" type="StringType">TEST</NameValue>
</Property>
<Property>
<NameValue name="ln.SearchKey2" type="StringType">000001_02</NameValue>
</Property>
<Property>
<NameValue name="ln.CustomizedIndicator" type="IndicatorType">false</NameValue>
</Property>
</UserArea>
<EnvironmentalComplianceCode listID="Environmental Compliance">Not Relevant</EnvironmentalComplianceCode>
<PhantomIndicator>false</PhantomIndicator>
<SellableIndicator>true</SellableIndicator>
<ProducibleIndicator>true</ProducibleIndicator>
<PurchasableIndicator>true</PurchasableIndicator>
<StockableIndicator>true</StockableIndicator>
<PackagingUnit>
<ID>PCS</ID>
<Name>Pieces/Each</Name>
<UnitCode>PCS</UnitCode>
<Dimensions>
<WidthMeasure unitCode="IN">0</WidthMeasure>
<LengthMeasure unitCode="IN">0</LengthMeasure>
<HeightMeasure unitCode="IN">0</HeightMeasure>
</Dimensions>
<TotalVolumeMeasure unitCode="IN3">0</TotalVolumeMeasure>
</PackagingUnit>
<ProcurementParameters>
<OrderMethodCode>Lot for Lot</OrderMethodCode>
<OrderIncrementQuantity unitCode="PCS">1</OrderIncrementQuantity>
<FixedOrderQuantity unitCode="PCS">1</FixedOrderQuantity>
<FixedOrderBaseUOMQuantity unitCode="PCS">1</FixedOrderBaseUOMQuantity>
<MinOrderQuantity unitCode="PCS">1</MinOrderQuantity>
<MinOrderBaseUOMQuantity unitCode="PCS">1</MinOrderBaseUOMQuantity>
<MaxOrderQuantity unitCode="PCS">99999999</MaxOrderQuantity>
<MaxOrderBaseUOMQuantity unitCode="PCS">99999999</MaxOrderBaseUOMQuantity>
<PlanningMethodCode>Planned</PlanningMethodCode>
<OrderPointQuantity unitCode="PCS">0</OrderPointQuantity>
<OrderPointBaseUOMQuantity unitCode="PCS">0</OrderPointBaseUOMQuantity>
<MaxQuantity unitCode="PCS">999999999</MaxQuantity>
<MaxBaseUOMQuantity unitCode="PCS">999999999</MaxBaseUOMQuantity>
<SafetyStockQuantity unitCode="PCS">0</SafetyStockQuantity>
<SafetyStockBaseUOMQuantity unitCode="PCS">0</SafetyStockBaseUOMQuantity>
<EOQQuantity unitCode="PCS">1</EOQQuantity>
<EOQBaseUOMQuantity unitCode="PCS">1</EOQBaseUOMQuantity>
<OrderCycleDuration>PT0H0M0S</OrderCycleDuration>
</ProcurementParameters>
</ItemMasterHeader>
<ItemLocation>
<WarehouseLocation>
<ID accountingEntity="0060" lid="lid://infor.ln.ln01/0060">W_CHKCWH</ID>
<Name>Kansas City warehouse</Name>
</WarehouseLocation>
<StockCarryingCost>
<Amount currencyID="EUR">0</Amount>
<Duration>P1Y</Duration>
</StockCarryingCost>
<InboundLeadTimeDuration>PT0H0M0S</InboundLeadTimeDuration>
<OutboundLeadTimeDuration>PT0H0M0S</OutboundLeadTimeDuration>
</ItemLocation>
</ItemMaster>
</DataArea>
</SyncItemMaster>'

SET @myDoc.modify('replace value of (/*:SyncItemMaster/*:DataArea/*:Sync/*:AccountingEntityID[1]/text())[1] with sql:variable("@comp")';

SELECT @myDoc

two points I want to highlight in this example.

The XML string assigned to XML data type is a nvarchar and not a varchar. see N before the single quote
in modify function to honor name space add *: like /*:SyncItemMaster instead of /SyncItemMaster

let me know if this help you.

matthias-baumann

Dear Sadik

Thanks a lot for your help!

I will be away for a fortnight, a but will gladly test it afterwards.

Kind regards

Matthias

matthias-baumann

Dear Sadik

Your solution takes me one step closer: the empty xmlns="" is gone using /*:

However, in ION, I am not free, I have to follow the way ION works. So I cannot declare this xml string as nvarchar, as it comes from the ION connection point, and there is no option for this.

DECLARE @myDoc XML =N'<?xml version="1.0"?>......

I will upload some documents to clarify the setting within the ION document flow.

Unfortunately, this is not so easy, I hope, it does not look too messy

But in case you don't have time to further investigate this, I fully understand!

[View:/cfs-file/__key/communityserver-discussions-components-files/73/2-Original-BOD-before-changing-company-per-SP.xml.xml:320:240]

[View:/cfs-file/__key/communityserver-discussions-components-files/73/3-Stored-Procedure.txt:320:240]

[View:/cfs-file/__key/communityserver-discussions-components-files/73/5-BOD-after-changing-company-per-SP.xml:320:240]

[View:/cfs-file/__key/communityserver-discussions-components-files/73/6-Second-Mapping.xml:320:240]

Thanks a lot
Matthias

Copyright © 2025 Infor. All rights reserved.