POS Cash Sale Orders

Efficiently synchronize in-store orders from OMS to NetSuite with Apache NiFi integration.

This integration flow is responsible to send the In-Store Orders information from OMS to NetSuite.

When using Shopify POS, these orders are fetched in OMS using Shopify Orders import, and these type of orders have the shipping method set as "POS_COMPLETED" in OMS.

Technical Implementation

This feed is generated in the integration layer using Apache NiFi.

In NiFi, the required POS Cash Sales order data is fetched by connecting to the OMS transactional database, and this data is prepared in the format required by NetSuite.

Some of the important conditions for POS Cash sales order data are:

1. To identify sales order, order_type_id = 'SALES_ORDER' in OrderHeader entity is used.

2. The order status should be 'ORDER_COMPLETED'.

3. The Sales Channel for the order is 'POS_SALES_CHANNEL'.

4. The Shipment Method set on Order Item Ship group is 'POS_COMPLETED'.

5. The sales order for which value for order_identification_type_id = 'NETSUITE_ORDER_ID' are fetched to be sent to NetSuite, so that same order is not sent once created in NetSuite.

SQL to fetch customer data from OMS

SELECT
  oh.order_name AS 'Order Id',
  os.status_datetime,
  oi.order_item_seq_id AS 'Order Line Id',
  oh.order_id AS 'External Id',
  e.description AS 'Sales Channel',
  p.product_type_id AS 'Product Type Id',
  CASE
    WHEN itm_gift_card.integration_type_id = 'NETSUITE_GIFT_CARD' THEN itm_gift_card.mapping_value
    ELSE gid.id_value
    END AS 'Item',
     oi.unit_price AS 'Price',
  adj.item_disc_amount AS 'Item Discount Amount',
  oi.quantity AS 'Quantity',
  'AVATAX' AS 'Tax Code',
  f.external_id AS 'Location',
  DATE_FORMAT(oh.order_date, '%c/%e/%Y') AS 'Date',
  pi.id_value AS 'Customer',
  CASE WHEN EXISTS (
    SELECT
      promo_name
    FROM
      product_promo
    WHERE
      promo_name = adj.discount_code
  ) THEN adj.discount_code WHEN adj.rate IS NOT NULL THEN 'SHOPIFY DISCOUNT' END AS 'Discount Item',
  adj.rate AS 'Rate',
  CASE WHEN f.external_id IN ('376', '415') THEN '5' ELSE '1' END AS 'Subsidiary',
  pa.address1 AS 'Address 1',
  pa.address2 AS 'Address 2',
  pa.city AS 'City',
  g.geo_code AS 'Country',
  pa.state_province_geo_id AS 'State',
  pa.postal_code AS 'Zip',
  CASE
    WHEN order_type.id_value IS NULL THEN e.enum_code
    ELSE order_type.id_value
  END AS 'Order Type',
  department.id_value AS 'Department',
  sales_channel.id_value AS 'Krewe Sales Channel',
  CASE WHEN itm_gift_card.integration_type_id = 'NETSUITE_GIFT_CARD' THEN 'Custom' ELSE (
    SELECT
      setting_value
    FROM
      product_store_setting pss
    WHERE
      setting_type_enum_id = 'PRICE_LEVEL_NETSUITE'
      AND (
        thru_date IS NULL
        OR thru_date > now()
      )
      AND pss.product_store_id = oh.product_store_id
    ORDER BY
      from_date DESC
    LIMIT
      1
  ) END AS 'Price Level',
  (
    SELECT
      id_value
    FROM
      order_identification
    WHERE
      oh.order_id = order_id
      AND order_identification_type_id = 'SHOPIFY_ORD_ID'
      AND (thru_date IS NULL OR thru_date > now()) LIMIT 1
  ) AS "HC Shopify POS Order Id",
  SUBSTRING_INDEX(
    oi_shopify_order_no.id_value,
    '-',
    -1
  )  as "Shopify Order Number"
FROM
  order_header oh
  INNER JOIN order_item oi ON oh.order_id = oi.order_id
  LEFT JOIN product p on oi.product_id = p.product_id
  LEFT JOIN integration_type_mapping itm_gift_card ON p.product_type_id = itm_gift_card.mapping_key
  AND itm_gift_card.integration_type_id = 'NETSUITE_GIFT_CARD'
  LEFT JOIN enumeration e ON e.enum_id = oh.sales_channel_enum_id
  AND e.enum_type_id = 'ORDER_SALES_CHANNEL'
  LEFT JOIN order_identification oid ON oh.order_id = oid.order_id
  AND oid.order_identification_type_id = 'NETSUITE_ORDER_ID' AND (oid.thru_date IS NULL OR oid.thru_date > now())
  JOIN good_identification gid ON gid.product_id = oi.product_id
  and gid.good_Identification_Type_Id = 'NETSUITE_PRODUCT_ID' and (gid.thru_date IS NULL OR gid.thru_date > now())
  INNER JOIN order_item_ship_group_assoc oisga ON oi.order_id = oisga.order_id
  AND oi.order_item_seq_id = oisga.order_item_seq_id
  INNER JOIN order_status os ON oi.order_id = os.order_id
  AND oi.order_item_seq_id = os.order_item_seq_id
  AND oi.status_id = os.status_id
  INNER JOIN order_item_ship_group oisg ON oisga.order_id = oisg.order_id
  AND oisga.ship_group_seq_id = oisg.ship_group_seq_id
  INNER JOIN facility AS f ON oisg.order_facility_id = f.facility_id
  LEFT JOIN facility_contact_mech_purpose fcmp ON fcmp.facility_id = f.facility_id
  AND fcmp.contact_mech_purpose_type_id = 'PRIMARY_LOCATION'
  AND fcmp.thru_date IS NULL
  LEFT JOIN postal_address pa ON pa.contact_mech_id = fcmp.contact_mech_id
  LEFT JOIN geo g ON g.geo_id = pa.country_geo_id
  LEFT JOIN order_role odr ON oh.order_id = odr.order_id
  AND odr.role_type_id = 'BILL_TO_CUSTOMER'
  AND odr.thru_date IS NULL
  INNER JOIN party_identification pi ON odr.party_id = pi.party_id
  AND party_identification_type_id = 'NETSUITE_CUSTOMER_ID'
  LEFT JOIN order_item_assoc oia ON oia.to_order_id = oi.order_id
  AND oia.to_order_item_seq_id = oi.order_item_seq_id
  AND oia.order_item_assoc_type_id = 'KIT_COMPONENT'
  LEFT JOIN(
    SELECT
      ori.order_id 'order_id',
      ori.order_item_seq_id 'order_item_seq_id',
      (
        SELECT
          SUM(oa.amount)
        FROM
          order_adjustment oa
          LEFT JOIN order_adjustment_attribute oaa ON oa.order_adjustment_id = oaa.order_adjustment_id
        WHERE
          oa.order_id = ori.order_id
          AND oa.order_item_seq_id = ori.order_item_seq_id
          AND oa.order_adjustment_type_id IN ('EXT_PROMO_ADJUSTMENT')
          AND oaa.order_adjustment_id IS NULL
      ) 'item_disc_amount',
      (
        SELECT
          GROUP_CONCAT(oaa.attr_value)
        FROM
          order_adjustment oa
          LEFT JOIN order_adjustment_attribute oaa ON oa.order_adjustment_id = oaa.order_adjustment_id
        WHERE
          oa.order_id = ori.order_id
          AND oa.order_item_seq_id = ori.order_item_seq_id
          AND oa.order_adjustment_type_id = 'EXT_PROMO_ADJUSTMENT'
          AND oaa.attr_name = 'discount_code'
      ) 'discount_code',
      (
        SELECT
          SUM(oa.amount)
        FROM
          order_adjustment oa
          JOIN order_adjustment_attribute oaa ON oa.order_adjustment_id = oaa.order_adjustment_id
        WHERE
          oa.order_id = ori.order_id
          AND oa.order_adjustment_type_id = 'EXT_PROMO_ADJUSTMENT'
          AND oaa.attr_name = 'discount_code'
      ) 'rate'
    FROM
      order_item ori
      INNER JOIN order_header orh ON ori.order_id = orh.order_id
    WHERE
      ori.status_id = 'ITEM_COMPLETED'
  ) adj ON oi.order_id = adj.order_id
  AND oi.order_item_seq_id = adj.order_item_seq_id
  LEFT JOIN(
    SELECT
      facility_id,
      id_value
    FROM
      facility_identification
    WHERE
      facility_iden_type_id = 'ORDR_ORGN_DPT'
      AND (
          THRU_DATE IS NULL
          OR THRU_DATE > now()
      )
  ) department ON f.facility_id = department.facility_id
  LEFT JOIN(
    SELECT
      facility_id,
      id_value
    FROM
      facility_identification
    WHERE
      facility_iden_type_id = 'ORDR_ORGN_SLS_CHNL'
      AND (
          THRU_DATE IS NULL
          OR THRU_DATE > now()
      )
  ) sales_channel ON f.facility_id = sales_channel.facility_id
  LEFT JOIN(
      SELECT
        facility_id,
        id_value
      FROM
        facility_identification
      WHERE
        facility_iden_type_id = 'NETSUITE_ORDR_TYPE'
        AND (
            THRU_DATE IS NULL
            OR THRU_DATE > now()
        )
  ) order_type ON f.facility_id = order_type.facility_id
  LEFT JOIN order_identification oi_shopify_order_no ON oi_shopify_order_no.order_id = oh.order_id AND oi_shopify_order_no.order_identification_type_id = 'SHOPIFY_ORD_NAME'
  LEFT JOIN (
    SELECT
        distinct
        oh.order_id
    FROM
        order_header oh
        JOIN order_item oi ON oh.order_id = oi.order_id
    LEFT JOIN good_identification gi ON oi.product_id = gi.product_id
            AND gi.good_identification_type_id = 'NETSUITE_PRODUCT_ID' and (gi.thru_date IS NULL OR gi.thru_date > now())
    WHERE
    good_identification_type_id is null
    AND oh.status_id = 'ORDER_COMPLETED'
    AND oh.order_type_id = 'SALES_ORDER'
    ) AS invalid_orders ON oh.order_id = invalid_orders.order_id
WHERE
  oh.status_id = 'ORDER_COMPLETED'
  AND oia.order_item_assoc_type_id IS NULL
  AND oh.sales_channel_enum_id = 'POS_SALES_CHANNEL'
  AND oisg.shipment_method_type_id = 'POS_COMPLETED'
  AND oh.order_type_id = 'SALES_ORDER'
  AND oid.order_id IS NULL
  AND invalid_orders.order_id IS NULL
order by
  oi.order_id,
  oi.order_item_seq_id

Implementation flow

1. This feed will be generated using a SQL to fetch data from HotWax. SQL outputs the feed in JSON format

2. The generated feed has a limit on total number of Orders in each file as external system can not accept more than 25k records in a single file.

3. Then the feed undergoes a JOLT transformation where an extra object is added to the JSON if there is any discount on the same item other than the discount due to discount_code.

4. JOLT also does the mapping for fields- Order Type, Department and Sales channel based on the value of Source name field fetche through SQL from HC.

5. Then the format of feed is converted from JSON to CSV.

6. Then the feed is sent to an sftp location.

NetSuite POS Cash Sale Feed Sample

Order Id	Order Line Id	External Id	Sales Channel	Item	Price	Amount	Quantity	Tax Code	Location	Date	Customer	Discount Item	Rate	Subsidiary	Address 1	Address 2	City	Country	State	Zip	Order Type	Department	Krewe Sales Channel	Price Level	Shopify Order Number	Order Note
"#KREWE37312"	00101	KR10236	POS Channel	19-08S BRETON | Matte Oyster 24K			1.000000	AVATAX	254	9/8/2023	5313849			1	1,818 Magazine Street		New Orleans	US	LA	70130	6	212	12	Base Price (MSRP)	36,167	New Exchange order @ 2023-12-14 11:27:56 exchange for order KR10236
"#KREWE37312"	00102	KR10236	POS Channel	RX12-03 ORLEANS II | Titanium			1.000000	AVATAX	254	9/8/2023	5313849			1	1,818 Magazine Street		New Orleans	US	LA	70130	6	212	12	Base Price (MSRP)	36,179
"#KREWE37310"	00101	KR10237	POS Channel	19-08S BRETON | Matte Oyster 24K			1.000000	AVATAX	372	9/8/2023	5313850			1	619 Royal Street		New Orleans	US	LA	70130	6	317	156	Base Price (MSRP)
"#KREWE37307"	00101	KR10238	POS Channel	30-01S OCTAVIA | Crystal + Black 24K			1.000000	AVATAX	376	9/8/2023	5313849	SHOPIFY DISCOUNT	-82.500	5	Gansevoort Street		New York	US	NY	10014	6	335	144	Base Price (MSRP)	36,123
"#KREWE37307"	00102	KR10238	POS Channel	19-08S BRETON | Matte Oyster 24K			1.000000	AVATAX	376	9/8/2023	5313849	SHOPIFY DISCOUNT	-82.500	5	Gansevoort Street		New York	US	NY	10014	6	335	144			New Exchange order @ 2023-12-14 11:27:56 exchange for order KREWE37307

Data Model Mapping

Fields	Data Type	Description	HC Field Mapping
Order Id	String	Order ID	OrderHeader.ORDER_NAME
Order Line Id	String	Order Line ID	OrderItem.ORDER_ITEM_SEQ_ID
External Id	String	External Order ID	OrderHeader.ORDER_ID
Sales Channel	String	Description of the sales channel	Enumeration.DESCRIPTION
Item	String	Concatenated product and item description	CONCAT(P.INTERNAL_NAME, OI.ITEM_DESCRIPTION)
Price	String	Empty field (price calculated externally)	Default empty
Amount	Unknown	Adjustments where discount is due to reason other than discount_code	adjustment.ITEM_DISC_AMOUNT
Quantity	Unknown	Quantity of items	OrderItem.QUANTITY
Tax Code	String	Tax code	"AVATAX"
Location	Number	Location external ID	Facility.EXTERNAL_ID
Date	Date	order date	OrderHeader.ORDER_DATE in format D/M/YYYY
Customer	Unknown	Customer ID	PartyIdentification.ID_VALUE where party_identification_type_id = 'NETSUITE_CUSTOMER_ID'
Discount Item	String	Discount Code	orderAdjustmentAttribute.ATTR_VALUE where ORDER_ADJUSTMENT_TYPE_ID = 'EXT_PROMO_ADJUSTMENT'
Rate	Numbwe	Adjustments where discount is due to discount_code	order_adjustment.Rate
Subsidiary	String	Subsidiary based on external ID	Mapping based on Facility external id
Address 1	String	Address line 1	postalAddress.address1
Address 2	String	Address line 2	PostalAddress.address2
City	String	City	PostalAddress.city
Country	String	Country	geo.geo_code
State	Unknown	State or province	PostalAddress.state_province_geo_id
Zip	String	Postal code	PostalAddress.postal_code
Order Type	Number	Order Type	Mapping based on Source
Department	Number	Department	Mapping based on Source Name
Krewe Sales Channel	Number	Sales Channel	Mapping based on Source
Price Level	String	Price level sent as custom for duplicate fields created due to discount other than discount code	default value "custom" for discount details rows and ProductStoreSetting.SettingValue where SettingTypeEnumId = 'PRICE_LEVEL_NETSUITE' for rest of the rows

Special handling

1. Order Item Discount-

   1. An extra record is sent for an order item if the item sent has an extra discount amount other than the discount due to discount_code.

   2. This row does not contain line id field and price level is sent as "custom" for these fields.

2. Limit on number of orders-

   1. This flow returns only a limited number orders. If the external system has some limit on total number of orders in the output. It can be configured from the queryRecord processor.