Technical Articles
File Input Process using the ByDesign Cloud Studio
I was given a typical problem by a partner. The standard open sales order migration doesn’t allow for editing or further use of the sales order once migrated. This didn’t suit the end customer’s requirements for Sales Orders, so a new way had to be found.
I decided that we could use the Cloud Studio and create some custom business objects, store the raw data, and then use this information to create a sales order via ABSL. This way we had complete control over the data and the subsequent processing of the sales orders.
Step 1.
Create my Custom Business Objects in the ByDesign Studio. I called one SO_Root (Contains the SO Header info, usually found in the General Tab of the Open Sales Order Migration template) and SO_Items (Contains the data held in the Items tab).
The idea being that first we upload the items to the SO_Items BO, and then we upload the header details to the SO_Root BO. When the second import happens an ABSL script is triggered and the complete Sales Order is created. We will consider this process later.
First, lets look at how the data is imported into the custom BO.
Once the BO was created, I added a Service Integration to my solution. This is hard to get right, and it took me two goes to have it working.
Select the fields from your BO that you want included in the message. DO NOT include the UUID field, that is automatically added and will be populated by ByDesign as the records are added.
Ensure that you use your own ID as the readable ID (in this case SOID) and ensure to check the “Mass Processing” otherwise it will only do one record at a time, per upload.
Next Save and Activate this service integration, Once activated you will be able to download the xsd file.If this link isn’t available, then you haven’t saved, activated, closed and re-opened the service integration.
Download and save the xsd file as we need it now to create the upload file.
Step 2:
Open a blank excel work book and open the Developers Tab. if you do not have the Developers tab visible, open up options and activate it.
From Excel File>Options (bottom left hand side)
Customize Ribbon and move Developer from LHS to RHS
Now back into Excel and open the developer tab. Click on “Source” this should open a window on the RHS titled “XML Source” click the button xml maps (bottom rhs) and add the xsd file we downloaded from the studio.
This will populate the XML Source window with the XML data structure from the xsd file.
First drag and drop the field CreationDateTime to
cell A2 and ensure to put a label on it!
The date must be in the SAP Global date format
2020-03-29T12:00:00.000Z (anything else and it will fail)
Now find the List section and drag all SOItems tree from the XML Source window into the worksheet at position B5. You will see that clicking on the SOItems list will highlight all the elements.
Your excel sheet should look something like this now
you can delete any of the fields you don’t have data for. Remember if you have an element in your BO that is a compound data type, such as Amount or Quantity, it will have TWO fields and both of these need to be supplied.
Simply cut and paste your data into the columns as you need. If all is well the sheet should extend the banding and this way you know that the integration is set to “mass enabled” .
With your data pasted into this spreadsheet, it can be exported to xml. Use the “export” button in the developer part of the ribbon and save your file.
Now we need to login to ByDesign, ensure you log in as a key user. with access to the Application & User Management Work Center, as we need to access file input runs view.
Step 3
From the File Input screen we must create a new run. Select New from this screen
Ensure to choose the correct file integration from the drop down list
Then save and ACTIVATE the run – this is easy forgotten, but the run must be active before we can schedule it.
With the run active, change the view to show “unprocessed files” and upload our new xml file here. Click Add.
Change the view again, to show Active Runs, highlight the newly created run and click “Schedule”
Leave the settings at “immediate” then save and close this window. Within a few seconds the run will start and you will see the resulting messages in the Execution Details tab.
This Section has successfully created a new business object, created a new service integration, added data to a custom xml file and uploaded it to ByD. We now need to look at the method for creating sales orders when we upload the SO_Root data.
Step 4
Right click on the Business Object in your cloud studio solution and select the option “create script files” we will use the Root>Event:BeforeSave (untick the mass enable if you want to copy my code).
I use this event because as we load data to the SO_Root object via the file input run each new record will get saved and therefore trigger this event without any further intervention from user.
This is the ABSL Script I used to create my sales order. It is split into four key areas.
- We check that for the SOID in the root, there is at least one corresponding line item in the SO_Items object.
- We create and instance of the SalesOrder Root (instSO_Root) and our custom data to that,
- For each record we have in our first search we then create and instance of the item(SO_item) and add custom data for that too.
- For each item we need to create entries for the request (firstrequestschedule) where we add quantities and an entry in the price and tax calc if we want to override pricing from a standard price list.
var qrySOItems = SOItems.QueryByElements;
var selParam = qrySOItems.CreateSelectionParams();
selParam.Add(qrySOItems.SOID.content,”I”,”EQ”, this.SORootID.content);
var result = qrySOItems.Execute(selParam);
if(result.Count() > 0){
var elSO_Root: elementsof SalesOrder;
var elSO_Item: elementsof SalesOrder.Item;
var instSO_Root;
var instSO_Item;
var ReqTime = DateTime.ParseFromString(this.SORequestedDate.ToString());
elSO_Root.BuyerID.content = “Original Order ” + this.SORootID.content.RemoveLeadingZeros();
instSO_Root = SalesOrder.Create(elSO_Root);
var SOID = instSO_Root.ID;
instSO_Root.DateTime = this.SOPostingDate.ConvertToGlobalDateTime();
instSO_Root.BuyerParty.PartyKey.PartyID.content = this.AccountID;
instSO_Root.EmployeeResponsibleParty.PartyKey.PartyID.content = this.EmpResp.content;
instSO_Root.SalesUnitParty.PartyKey.PartyID.content = this.SalesUnit.content;
instSO_Root.SalesAndServiceBusinessArea.DistributionChannelCode.content = this.DistChannel.content;
instSO_Root.SalesOrderType = this.SOType.content;
//instSO_Root.RequestedFulfillmentPeriod.TimePointPeriod.StartTimePoint.DateTime.content = ReqTime.ConvertToGlobalDateTime();
instSO_Root.DeliveryTerms.Incoterms.ClassificationCode = this.Incoterms;
instSO_Root.DeliveryTerms.Incoterms.TransferLocationName = this.IncoLocation.content;
instSO_Root.FulfillmentBlockingReasonCode = this.DelBlock.content;
var itemid= 10;
foreach(var item in result){
elSO_Item.ID = itemid.ToString();
instSO_Item = instSO_Root.Item.Create(elSO_Item);
if(instSO_Item.IsSet()){
instSO_Item.ItemProduct.ProductKey.ProductID.content = item.LI_Product.content;
instSO_Item.LegacyContract = item.LI_LegContract.content;
instSO_Item.ItemProduct.ProductRequirementSpecificationKey.RequirementSpecificationID.content = item.LI_ProdSpec.content;
instSO_Item.ZContract_Quantity.content = item.LI_ContQTY.content;
instSO_Item.ZContract_Quantity.unitCode = item.LI_Line_QTY.unitCode;
instSO_Item.ZOutstanding_Quantity.content = item.LI_OS_QTY.content;
instSO_Item.ZOutstanding_Quantity.unitCode = item.LI_Line_QTY.unitCode;
instSO_Item.ZAlreadyCallOff_Quantity.content = item.LI_Called_Off.content;
instSO_Item.ZAlreadyCallOff_Quantity.unitCode = item.LI_Line_QTY.unitCode;
if(instSO_Item.FirstRequestedItemScheduleLine.IsSet()){
instSO_Item.FirstRequestedItemScheduleLine.Quantity.content = item.LI_Line_QTY.content;
instSO_Item.FirstRequestedItemScheduleLine.Quantity.unitCode = item.LI_Line_QTY.unitCode;
//instSO_Item.FirstRequestedItemScheduleLine.DateTimePeriod.StartDateTime.content = item.LI_Line_Req_Date.ConvertToGlobalDateTime();
}
else
{
instSO_Item.FirstRequestedItemScheduleLine.Create();
instSO_Item.FirstRequestedItemScheduleLine.Quantity.content = item.LI_Line_QTY.content;
instSO_Item.FirstRequestedItemScheduleLine.Quantity.unitCode = item.LI_Line_QTY.unitCode;
//instSO_Item.FirstRequestedItemScheduleLine.DateTimePeriod.StartDateTime.content = item.LI_Line_Req_Date.ConvertToGlobalDateTime();
}
}
if(instSO_Item.PriceAndTaxCalculationItem.IsSet()){
if(instSO_Item.PriceAndTaxCalculationItem.ItemMainPrice.IsSet()){
instSO_Item.PriceAndTaxCalculationItem.ItemMainPrice.Rate.DecimalValue = item.LI_Line_Value.content;
} else {
instSO_Item.PriceAndTaxCalculationItem.ItemMainPrice.Create();
instSO_Item.PriceAndTaxCalculationItem.ItemMainPrice.Rate.DecimalValue = item.LI_Line_Value.content;
}
}//End og price &tax IsSet()
itemid = itemid +10;
} //End of foreach Condition
raise SalesOrderCreated.Create(“S”,instSO_Root.ID);
}//End of Query Condition
Now create a Service Integration just as we did for the SO_Items and follow the same process to run a file input process as we did for the items. This action now resulted in a new complete sales order being created with the status “in Preparation”
As always these blogs are to help and the script is provided without warranty! if you would like more detail please let me know.
John Meadows
Very good!
Thank you very much!
Thanks John, very good article! I’ve just looked at my own notes and have a few additional tips on this topic, which might round off this post and make it possible to ditch my own notes and replace them with a link to this article:
We also like the concept of a separate inbound object (usually name pattern: In+Objectname) for the import task, so we have control of what is written in the destination objects and can use our own code for error handling, which is easier than the error handling of the standard workcenter file input.
Note on Step 1 Create Service Integration: if you use camelcase as name for the integration scenario, then the system will automatically split the name for the Service Interface in the UI. Example Technical Name “SOItemsIntegration” => UI Name “SO Items Integration” (translation is not possible, so choose a name which will be OK for the user)
Some notes on error prevention and handling (Step 3):
The import is processing in two steps in the background of the ByD System. In most cases, the user will not notice this two step processing, but it can be relevant during an error handling.
There are cases in which the processing run indicates that it was successful (step 1) but errors occurred in the background (step 2) and the file was not processed. An example is a wrong BIC (Bank Identifier Code) in the XML File. You check most of these errors in the workcenter “Application and User Management – Process Communication Errors”.
Similar errors can occur when processing large files. We had a customer, who wanted to upload 800k lines with the file input and we could only do this by splitting it into 25k pieces. With large files it can also happen that the run shows a successful status, but the processing of the second step (background step, invisible to the user) is not finished yet. In some of our cases this took up to 40 minutes. If no errors appear in the process communciation then just be patient with large files.
Note on for Step 2
Here is an old video from 2014 https://www.youtube.com/watch?v=klbi56fW0k4, which also shows how to handle the schema with Excel (Attention! date format in the video is wrong, the one above from John is the correct one).
Last but not least:
Before you export all data from Excel to an XML File: Please delete empty lines and do a check for duplicated lines. This will make your life much easier
Thanks for the blog John!
By any chance does any one have an example of the xml file right before uploading it to bydesign?
Because I followed the blog and nothing get imported... there are important attributes to set up on the xml to perform the appropriate actions and its not mentioned anywhere, in any blog or documentation?
I had this xml so far, no erros on bydesign but no data is imported :
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<LetteringItemIntegrationXMLInputRequest xmlns="http://0050040694-one-off.sap.com/YURZR4NBY_">
<MessageHeader>
<CreationDateTime>2021-06-15T00:00:00+02:00</CreationDateTime>
</MessageHeader>
<List listCompleteTransmissionIndicator="true" actionCode="01">
<LetteringItem>
<LetteringItemID>1</LetteringItemID>
<JournalEntryID>980000011641</JournalEntryID>
<JournalEntryItemID>1</JournalEntryItemID>
<GLAccount>
<CompanyUUID>00163E0D-6B66-1ED8-81A0-E32B113A63E0</CompanyUUID>
<ChartOfAccountsCode/>
<ChartOfAccountsItemCode>418000</ChartOfAccountsItemCode>
</GLAccount>
<FiscalYear>2019</FiscalYear>
<SetOfBooks>G1FR</SetOfBooks>
<Note>test</Note>
<LetteringGroupe>LET-test-excel-1</LetteringGroupe>
<CreatedBy>maxime</CreatedBy>
</LetteringItem>
</List>
</LetteringItemIntegrationXMLInputRequest>
Thanks/Regards
It isnt a very friendly process, with few clues about any errors!
I would use the debug mode in the studio to see what data is coming in and how it is being handled.
John
Thanks John for the blog , very impressive!
I've tried this to import journal entries voucher and it works very well.
However, since a while , we're tying to import Receivables Payables entries .
Everytime, we have these errors:
We think that we have filled all the mandatory fields .
Here is an example of an XML file used to do the import:
<OpenItemXMLInputRequest xmlns="http://0028603050-one-off.sap.com/YF4D4N4UY_">
<MessageHeader xmlns="">
<CreationDateTime></CreationDateTime>
</MessageHeader>
<List listCompleteTransmissionIndicator="true" actionCode="01" reconciliationPeriodCounterValue="0" xmlns="">
<TaxReceivablesPayablesEntry>
<ID></ID>
<CompanyID></CompanyID>
<BusinessPartnerInternalID></BusinessPartnerInternalID>
<ReceivablesPayablesEntryTypeCode></ReceivablesPayablesEntryTypeCode>
<BusinessTransactionDocumentDate></BusinessTransactionDocumentDate>
<CountryCode></CountryCode>
<AccountingTransactionDate></AccountingTransactionDate>
<TransactionCurrencyCode></TransactionCurrencyCode>
<Description></Description>
<CashFlowExpenseAndReceiptExplanation>
<TransactionCurrencyCode></TransactionCurrencyCode>
<CountryCode></CountryCode>
<Item>
<PropertyMovementDirectionCode></PropertyMovementDirectionCode>
<TaxationRegionCode></TaxationRegionCode>
<TaxJurisdictionCode></TaxJurisdictionCode>
<TransactionCurrencyTaxAmount currencyCode=""></TransactionCurrencyTaxAmount>
<TransactionCurrencyGrossAmount currencyCode=""></TransactionCurrencyGrossAmount>
<TransactionCurrencyNetAmount currencyCode=""></TransactionCurrencyNetAmount>
<ItemAccountingCodingBlockDistribution>
<ValidityDate></ValidityDate>
<CompanyID></CompanyID>
<GeneralLedgerAccountAliasCode></GeneralLedgerAccountAliasCode>
<TotalAmount currencyCode=""></TotalAmount>
</ItemAccountingCodingBlockDistribution>
</Item>
</CashFlowExpenseAndReceiptExplanation>
</TaxReceivablesPayablesEntry>
</List>
</OpenItemXMLInputRequest>
Have you ever tried to import Open Items by this way ?
Thanks/Regards
Mehdi
Mehdi
It is impossible to be sure, but I'm guessing that you have missed that data in your xml - or it is in the wrong format.
You should use the debug process in the studio to see what data is being input during the process.
John
Hi John, I see you could easily use SO business object to create new SO. I'm tryin do the same to create PO record via ABSL but didn't find the create method from PO business object. do you have any sugestion over my problem John?
Rizaldi
The process is exactly the same for a PO as it is for a SO.
First you establish an instance of the new PO based on your variables
then add the remaining data using the newly created instance - but use an "if" statement to ensure the instance exists, otherwise you will get an error dump
with the header instance created -now create the items
and then you can continue to add all the data necessary to make the PO consistent.
Hope this helps
John
The thing is I got error like this when I call Create method of PurchaseOrder
Error 1 Access of member 'Create' in a cross-deployment unit is not possible.
Still doesn't understand about this because I'm quite new to this SAP ERP system
Read the studio help document on deployment units. Your solution to create a PO must sit inside the Purchasing Deployment unit.
John
So it's not possible to access CRUD of another BO in different DU? Beside set the deployment unit of the solution is there any other way to achieve that?
Hello Everyone,
I have been asked by my managers to make a custom bo, from where we are posting journal entry vouchers and seeing the impact of it in standard journal voucher OWL,
how to do that! anyone?
thanks in advance
Regards,
Imad khan