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.

1. We check that for the SOID in the root, there is at least one corresponding line item in the SO_Items object.
2. We create and instance of the SalesOrder Root (instSO_Root) and our custom data to that,
3. 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.
4. 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