OMS Always-On Demo to SCIS connector

SCIS import setup

Authenticate and get bearer token

• get https://api.ibm.com/saascore/run/authentication-retrieve?orgId={{organizationid}}
• Authorization
  • Basic Auth with ibm id username and password
• Header
  • x-ibm-client-Id : saascore-{{clientid}}

Put COS credential as a secret in SCIS

• put: https://api.ibm.com/infohub/run/metadata/api/v1/{{geo}}/tenants/{{clientid}}/cos/{{cos-secrete-name}}
  • cos-secrete-name is a name that you give to the secret
• Authorization: bearer token from the authentication step
• header
  • x-ibm-client-Id: infohub-{{clientid}}
• body:

  {
      "value":"{{cos-api-key}}"
  }

Post import rule

• post: https://api.ibm.com/infohub/run/metadata/api/v1/{{geo}}/data/import/rules
• Authorization: bearer token from the authentication step
• header
  • x-ibm-client-Id: infohub-{{clientid}}
• body:

  {
      "name": "OMS to SCIS COS CSV bulk import rule",
      "tenantId": "{{clientid}}",
      "instructions": {
          "transform": {
              "schema": "USE_DEFAULT",
              "deleteTransformFileAfterRoutedToDestination": "true",
              "transformedFilePrefixName": "csvTransform",
              "sourceFormat": "csv"
          },
          "routeTo": {
              "streamingIngest": {
                  "priority": "1"
              },
              "destinationType": "streamingIngest"
          },
          "source": {
              "sourceType": "COS",
              "COS": {
                  "bucketName": "{{cos-bucket-name}}",
                  "prefixName": "{{cos-file-prefix}}",
                  "endpoint": "{{cos-end-point}}",
                  "instanceId": "{{cos-instance-id}}",
                  "bucketRegion": "{{cos-bucket-region}}",
                  "secretName": "{{cos-secrete-name}}",
                  "isPublicUrl": "false"
              }
          }
      }
  }

CP4D Project setup

Data assets

Use connected data to export from OMS and import into COS CSV files.

Organization import

Update SCIS organization with OMS' YFS_ORGANIZATION table

• Input
  • organization_from_oms connecting to OMS YFS_ORGANIZATION table
• Output
  • organization_to_scis connecting to COS CSV file scis/import/OMS-AOD/Organization.csv

Refinement Flow

Steps

1. Filtering

   filter(`IS_HUB_ORGANIZATION` == 'Y' | `IS_ENTERPRISE` == 'Y' | `IS_CARRIER` == 'Y')

2. Form columns for SCIS

   mutate(organization.organizationIdentifier = `ORGANIZATION_CODE`, location.locationIdentifier = `ORGANIZATION_CODE`, name=`ORGANIZATION_NAME`, division='', sourceLink='')

   mutate(orgType = case_when(`IS_CARRIER` == 'Y' ~ 'CARRIER', `IS_BUYER` == 'Y' ~ 'CUSTOMER', `IS_SELLER` == 'Y' ~ 'SUPPLIER', `IS_ENTERPRISE`=='Y' ~ 'ORGANIZATION', TRUE ~ 'OPERATIONS'))

3. Select the output columns

   select(`organization.organizationIdentifier`,`orgType`,`location.locationIdentifier`,`name`,`division`,`sourceLink`)

Location import

• Input
  • location_shipnode_from_oms connecting to OMS YFS_SHIPNODE table
  • location_person_info_from_oms connecting to OMS YFS_PERSON_INFO table
• Output
  • location_to_scis connecting to COS CSV file scis/import/OMS-AOD/Location.csv

Refinement Flow

1. Join YFS_SHIP_NODE with YFS_PERSON_INFO on YFS_SHIP_NODE.SHIP_NODE_ADDRESS_KEY = YFS_PERSON_INFO.PERSON_INFO_KEY

   left-joined data from location_person_info_from_oms based on columns SHIP_NODE_ADDRESS_KEY,PERSON_INFO_KEY 
   

2. Form columns for SCIS

   rename(city = `CITY`)

   mutate(locationIdentifier= `SHIP_NODE`, locationType = `NODE_TYPE`, locationName = `DESCRIPTION`, address1 = `ADDRESS_LINE1`, address2=`ADDRESS_LINE2`, postalCode=`ZIP_CODE`, stateProvince=`STATE`, country=`COUNTRY`, coordinates= paste(coalesce(`LATITUDE_s`, 9.9999), coalesce(`LONGITUDE_s`, 9.9999), sep=', '),includeInCorrelation=TRUE, geo='', locationSubType= '', sourceLink = '' )

3. Select the output columns

   select( `locationIdentifier`, `locationType`, `locationName`, `address1`, `address2`, `city`, `postalCode`, `stateProvince`, `country`, `coordinates`, `includeInCorrelation`, `geo`, `locationSubType`, `sourceLink` )

Product Import

• Input
  • product_from_oms connecting to OMS YFS_ITEM table
• Output
  • product_to_scis connecting to COS CSV file scis/import/OMS-AOD/Product.csv

Refinement Flow

1. Replaced all values '[\r\n]' with ' ' in DESCRIPTION

2. Form columns for SCIS

   rename(description = `DESCRIPTION`, status=`STATUS`)

   mutate(partNumber= `ITEM_ID`, productType='PRODUCT', category.code=`ITEM_TYPE`, brand.code='',family.code='',line.code='',segment.code='', status= if_else(str_trim(`status`) == '3000' , 'ACTIVE', 'INACTIVE'), value=`UNIT_COST`, valueCurrency=`COST_CURRENCY`, defaultQuantityUnits=`UOM`, name=`SHORT_DESCRIPTION`, plannerCode='', sourceLink=`IMAGE_LOCATION`)

3. Select the output columns

   select(`partNumber`,`productType`,`category.code`,`brand.code`,`family.code`,`line.code`,`segment.code`,`status`,`value`,`valueCurrency`,`defaultQuantityUnits`,`name`,`description`,`plannerCode`,`sourceLink`)

Order Header Import (In theory)

This process was not working due to:

1. CP4D formats timestamp and date to YYYY-MM-DD HH:mm:ss format (I think it is using database to format date/time in POSIXct format)
2. SCIS import expects date/time string in YYYY-MM-DDTHH:mm:ss (UTC/ISO format)
3. CP4D data refinery runtime doesn't support R functions of "format" or "gsub" (it works in the designer/preview mode but not in Spark jobs)

• Input
  • order_header_from_oms connecting to OMS YFS_ORDER_HEADER table
• Output
  • order_to_scis connecting to COS CSV file scis/import/OMS-AOD/Order.csv

Refinement Flow

1. Form columns for SCIS

   mutate(orderType = case_when(str_trim(`DOCUMENT_TYPE`) == '0001' ~ 'Sales Order', str_trim(`DOCUMENT_TYPE`) == '0002' ~ 'Planned Order', str_trim(`DOCUMENT_TYPE`) == '0003' ~ 'Return Order', str_trim(`DOCUMENT_TYPE`) == '0004' ~ 'Template Order', str_trim(`DOCUMENT_TYPE`) == '0005' ~ 'Purchase Order', str_trim(`DOCUMENT_TYPE`) == '0006' ~ 'Transfer Order', str_trim(`DOCUMENT_TYPE`) == '0007' ~ 'Master Order', TRUE ~ 'Unknown'))

   1. Date string format

   Having issue with date time formats. CP4D timestamp output format: "2022-03-28 12:51:31". SCIS expect input format: "2022-03-28T12:51:31".

   This works in the editor mode but Spark runtime can't find "format" function

   mutate(createdDate = format(`ORDER_DATE`, '%FT%T'), requestedShipDate=format(`REQ_SHIP_DATE`, '%FT%T'), requestedDeliveryDate=format(`REQ_DELIVERY_DATE`, '%FT%T'))
   
   #or
   
   mutate(createdDate = as.character(`ORDER_DATE`, '%FT%T'), requestedShipDate=as.character(`REQ_SHIP_DATE`, '%FT%T'), requestedDeliveryDate=as.character(`REQ_DELIVERY_DATE`, '%FT%T'))
   
   mutate(createdDate = date_format(`ORDER_DATE`, 'yyyy-MM-ddTHH24'), requestedShipDate=date_format(`REQ_SHIP_DATE`, 'yyyy-MM-ddTHH24'), requestedDeliveryDate=date_format(`REQ_DELIVERY_DATE`, yyyy-MM-ddTHH24'))

   This is what I am using now. It only sends the Date part of the timestamp

   mutate(createdDate = as.Date(`ORDER_DATE`), requestedShipDate=as.Date(`REQ_SHIP_DATE`), requestedDeliveryDate=as.Date(`REQ_DELIVERY_DATE`))

   mutate(orderIdentifier = `ORDER_NO`, vendor.organizationIdentifier = `SELLER_ORGANIZATION_CODE`, buyer.organizationIdentifier=`BUYER_ORGANIZATION_CODE`, shipFromInstructionLocation.locationIdentifier=`SHIP_NODE`, shipToLocation.locationIdentifier=`RECEIVING_NODE`, orderStatus='', createdDate=`ORDER_DATE`, requestedShipDate=`REQ_SHIP_DATE`, requestedDeliveryDate=`REQ_DELIVERY_DATE`, plannedShipDate='', plannedDeliveryDate='', quantity='', quantityUnits='', totalValue=`TOTAL_AMOUNT`, orderValueCurrency=`CURRENCY`, lineCount='', totalShippedQuantity='', exclude='', sourceLink='')

2. Select the output columns

   select(`orderIdentifier`,`orderType`,`vendor.organizationIdentifier`,`buyer.organizationIdentifier`,`shipFromInstructionLocation.locationIdentifier`,`shipToLocation.locationIdentifier`,`orderStatus`,`createdDate`,`requestedShipDate`,`requestedDeliveryDate`,`plannedShipDate`,`plannedDeliveryDate`,`quantity`,`quantityUnits`,`totalValue`,`orderValueCurrency`,`lineCount`,`totalShippedQuantity`,`exclude`,`sourceLink`)

Order Header Import (Actual)

Order Header import transformation is actually done with Jupyter Notebook order_refine_process.ipynb

Order Line Import

Due to the same issue as Order Header Import. Order line import transformation is actually done with Jupyter Notebook.

Shipment import

Update SCIS organization with OMS' YFS_SHIPMENT table

• Input
  • shipment_from_oms connecting to OMS YFS_SHIPMENT table
• Output
  • shipment_to_scis connecting to COS CSV file scis/import/OMS-AOD/Shipment.csv

Refinement Flow

Steps

1. Rename Column

   Renamed column SHIPMENT_NO to shipmentIdentifier

   Renamed column SHIPNODE_KEY to shipFromLocation.locationIdentifier

   Renamed column RECEIVING_NODE to shipToLocation.locationIdentifier

   Renamed column SELLER_ORGANIZATION_CODE to vendor.organizationIdentifier

   Renamed column BUYER_ORGANIZATION_CODE to buyer.organizationIdentifier

   Renamed column SCAC to carrier.organizationIdentifier

   Renamed column STATUS to status

2. Construct data for shipmentType column, based on DOCUMENT_TYPE

   Replaced values for shipmentType: DOCUMENT_TYPE where value contains "0001" as "OUTBOUND", DOCUMENT_TYPE where value contains "0003" as "INBOUND", DOCUMENT_TYPE where value is equal to "0005" as "INBOUND", ORDER_TYPE where value is equal to "Replenishment" as "INBOUND". Replaced all remaining values with "".

3. Construct data for status column

   Replaced values for status: status where value contains "1400" as "SHIPPED", status where value contains "1500" as "DELIVERED". Replaced all remaining values with "NOT SHIPPED".

4. For the Date fields like dateCreated, there are 2 ways to create the fields.

   Filter

   Filtered by: createdDate where value is not empty 

   Split column

   Split createdDate by text into Date,Time

   Concatenate

   Concatenated the values of Date,Time into dateCreated

5. Alternatively, one can also opt for custom code, as outlined below :

mutate(requestedTimeOfArrival = case_when( substr(coalesce(`REQUESTED_DELIVERY_DATE`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`REQUESTED_DELIVERY_DATE`, 12, 19) == "" ~ paste(substr(`REQUESTED_DELIVERY_DATE`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`REQUESTED_DELIVERY_DATE`, 1, 10), substr(`REQUESTED_DELIVERY_DATE`, 12, 19), sep="T") ))

mutate(committedTimeOfArrival = case_when( substr(coalesce(`DELIVERY_TS`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`DELIVERY_TS`, 12, 19) == "" ~ paste(substr(`DELIVERY_TS`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`DELIVERY_TS`, 1, 10), substr(`DELIVERY_TS`, 12, 19), sep="T") ))

mutate(actualShipDate = case_when( substr(coalesce(`SHIP_DATE`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`SHIP_DATE`, 12, 19) == "" ~ paste(substr(`SHIP_DATE`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`SHIP_DATE`, 1, 10), substr(`SHIP_DATE`, 12, 19), sep="T") ))

mutate(estimatedTimeOfArrival = case_when( substr(coalesce(`EXPECTED_DELIVERY_DATE`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`EXPECTED_DELIVERY_DATE`, 12, 19) == "" ~ paste(substr(`EXPECTED_DELIVERY_DATE`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`EXPECTED_DELIVERY_DATE`, 1, 10), substr(`EXPECTED_DELIVERY_DATE`, 12, 19), sep="T") ))

mutate(revisedEstimatedTimeOfArrival = case_when( substr(coalesce(`EXPECTED_DELIVERY_DATE`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`EXPECTED_DELIVERY_DATE`, 12, 19) == "" ~ paste(substr(`EXPECTED_DELIVERY_DATE`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`EXPECTED_DELIVERY_DATE`, 1, 10), substr(`EXPECTED_DELIVERY_DATE`, 12, 19), sep="T") ))

mutate(predictedTimeOfArrival = case_when( substr(coalesce(`EXPECTED_DELIVERY_DATE`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`EXPECTED_DELIVERY_DATE`, 12, 19) == "" ~ paste(substr(`EXPECTED_DELIVERY_DATE`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`EXPECTED_DELIVERY_DATE`, 1, 10), substr(`EXPECTED_DELIVERY_DATE`, 12, 19), sep="T") ))

mutate(actualTimeOfArrival = case_when( substr(coalesce(`ACTUAL_DELIVERY_DATE`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`ACTUAL_DELIVERY_DATE`, 12, 19) == "" ~ paste(substr(`ACTUAL_DELIVERY_DATE`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`ACTUAL_DELIVERY_DATE`, 1, 10), substr(`ACTUAL_DELIVERY_DATE`, 12, 19), sep="T") ))

6. Select the output columns -

select(`shipmentIdentifier`,`shipmentType`,`shipFromLocation.locationIdentifier`,`shipToLocation.locationIdentifier`,`vendor.organizationIdentifier`,`buyer.organizationIdentifier`,`carrier.organizationIdentifier`,`status`,`dateCreated`,`requestedTimeOfArrival`,`committedTimeOfArrival`,`actualShipDate`,`estimatedTimeOfArrival`,`revisedEstimatedTimeOfArrival`,`predictedTimeOfArrival`,`actualTimeOfArrival`,`lineCount`,`weight`,`weightUnits`,`currentLocationCoordinates`,`currentRegion`,`transportMode`,`houseAirwayBill`,`parcelTrackingNumber`,`airwayMasterNumber`,`billOfLadingNumber`,`proNumber`,`manifest`,`exclude`,`sourceLink`)

Shipment Line import

Update SCIS organization with OMS' YFS_SHIPMENT_LINE table

• Input
  • shipment_line_from_oms connecting to OMS YFS_SHIPMENT_LINE table
• Output
  • shipment_line_to_scis connecting to COS CSV file scis/import/OMS-AOD/ShipmentLine.csv

Refinement Flow

Steps

1. Perform an inner join with YFS_SHIPMENT table, as some fields are required from YFS_SHIPMENT table

   Secondary data source OMS Always On Demo DB/OMDB/YFS_SHIPMENT inner-joined data from OMS Always On Demo DB/OMDB/YFS_SHIPMENT based on columns SHIPMENT_KEY,SHIPMENT_KEY

2. Custom code for dateCreated, shipment.shipmentIdentifier, shipmentLineNumber

   mutate(dateCreated = case_when( substr(coalesce(`CREATETS_y`, as.POSIXct("3000-01-01")), 1, 10) == "3000-01-01" ~ "", substr(`CREATETS_y`, 12, 19) == "" ~ paste(substr(`CREATETS_y`, 1, 10), "00:00:00", sep="T"), TRUE ~ paste(substr(`CREATETS_y`, 1, 10), substr(`CREATETS_y`, 12, 19), sep="T") ))

   mutate(shipment.shipmentIdentifier = `SHIPMENT_NO`,shipmentLineNumber = `SHIPMENT_LINE_NO`)

3. Construct data for shipment.shipmentType, based on DOCUMENT_TYPE from YFS_SHIPMENT

   Replaced values for shipment.shipmentType: DOCUMENT_TYPE where value contains "0001" as "OUTBOUND", DOCUMENT_TYPE where value contains "0003" as "INBOUND", DOCUMENT_TYPE where value contains "0005" as "INBOUND", ORDER_TYPE_x where value contains "Replenishment" as "INBOUND". Replaced all remaining values with "".

4. Custom code for order.orderIdentifier, orderLine.orderLineNumber, quantityUnits,

   mutate(order.orderIdentifier = `ORDER_NO_x`, orderLine.orderLineNumber = `PRIME_LINE_NO`)

   mutate(quantityUnits = `UOM`)

   Renamed column QUANTITY to quantity

   mutate( valueCurrency = `CURRENCY` )

5. Select the output columns -

select(`shipment.shipmentIdentifier`,`shipmentLineNumber`,`shipment.shipmentType`,`order.orderIdentifier`,`orderLine.orderLineNumber`,`dateCreated`,`quantity`,`quantityUnits`,`value`,`valueCurrency`)