Skip to content

Brokered Feed

Jump to bottom
Sadhana Deshmukh edited this page Aug 22, 2023 · 20 revisions

Brokered Order Items Feed

For Debugging Eligible Order for Brokered Order Item Feed.

  1. SQL For eligible Orders using BrokeredOrderItemsSyncQueue View

    1. For all eligible orders -

      SELECT 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OH.PRODUCT_STORE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID,
  OH.EXTERNAL_ID  
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
  LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_SEQ_ID 
  INNER JOIN FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
  OI.STATUS_ID = 'ITEM_APPROVED' 
  AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
  AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
  AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
  AND OISG.FACILITY_ID <> '_NA_' 
  AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
  AND (
      EFO.FULFILLMENT_STATUS = 'REJECT' 
      OR EFO.FULFILLMENT_STATUS IS NULL
  ) 
  AND (
      OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
      OR OISGA.CANCEL_QUANTITY IS NULL
  )
) 
GROUP BY 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OH.PRODUCT_STORE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID,
  OH.EXTERNAL_ID  
ORDER BY 
  OISGINR.RESERVED_DATETIME ASC, 
  OH.ENTRY_DATE ASC

    2. With ORDER_ID field in conditions -

      SELECT 
   OH.ORDER_ID, 
   OH.ORDER_NAME, 
   OH.ORDER_DATE, 
   OH.STATUS_ID, 
   OH.ENTRY_DATE, 
   UOM.ABBREVIATION, 
   OH.GRAND_TOTAL, 
   OH.PRODUCT_STORE_ID, 
   OISG.CONTACT_MECH_ID, 
   OISG.TELECOM_CONTACT_MECH_ID, 
   OISG.SHIPMENT_METHOD_TYPE_ID, 
   P.FIRST_NAME, 
   P.LAST_NAME, 
   SCENM.ENUM_CODE, 
   ODR.PARTY_ID,
   OH.EXTERNAL_ID  
FROM 
   ORDER_HEADER OH 
   INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
   LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
   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 
   LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
   AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
   AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
   LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
   AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
   AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_SEQ_ID 
   INNER JOIN FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
   INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
   LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
   LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
   LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
  OH.ORDER_ID = '39298' 
  AND OI.STATUS_ID = 'ITEM_APPROVED' 
  AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
  AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
  AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
  AND OISG.FACILITY_ID <> '_NA_' 
  AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
  AND (
      EFO.FULFILLMENT_STATUS = 'REJECT' 
      OR EFO.FULFILLMENT_STATUS IS NULL
  ) 
  AND (
      OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
      OR OISGA.CANCEL_QUANTITY IS NULL
  )
) 
GROUP BY 
   OH.ORDER_ID, 
   OH.ORDER_NAME, 
   OH.ORDER_DATE, 
   OH.STATUS_ID, 
   OH.ENTRY_DATE, 
   UOM.ABBREVIATION, 
   OH.GRAND_TOTAL, 
   OH.PRODUCT_STORE_ID, 
   OISG.CONTACT_MECH_ID, 
   OISG.TELECOM_CONTACT_MECH_ID, 
   OISG.SHIPMENT_METHOD_TYPE_ID, 
   P.FIRST_NAME, 
   P.LAST_NAME, 
   SCENM.ENUM_CODE, 
   ODR.PARTY_ID,
   OH.EXTERNAL_ID  
ORDER BY 
   OISGINR.RESERVED_DATETIME ASC, 
   OH.ENTRY_DATE ASC

    3. With RESERVED_DATETIME field in condition -

      SELECT 
   OH.ORDER_ID, 
   OH.ORDER_NAME, 
   OH.ORDER_DATE, 
   OH.STATUS_ID, 
   OH.ENTRY_DATE, 
   UOM.ABBREVIATION, 
   OH.GRAND_TOTAL, 
   OH.PRODUCT_STORE_ID, 
   OISG.CONTACT_MECH_ID, 
   OISG.TELECOM_CONTACT_MECH_ID, 
   OISG.SHIPMENT_METHOD_TYPE_ID, 
   P.FIRST_NAME, 
   P.LAST_NAME, 
   SCENM.ENUM_CODE, 
   ODR.PARTY_ID,
   OH.EXTERNAL_ID 
FROM 
   ORDER_HEADER OH 
   INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
   LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
   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 
   LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
   AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
   AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
   LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
   AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
   AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_SEQ_ID 
   INNER JOIN FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
   INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
   LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
   LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
   LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
  OI.STATUS_ID = 'ITEM_APPROVED' 
  AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
  AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
  AND OISGINR.RESERVED_DATETIME > '2021-07-25 11:56:00' 
  AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
  AND OISG.FACILITY_ID <> '_NA_' 
  AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
  AND (
      EFO.FULFILLMENT_STATUS = 'REJECT' 
      OR EFO.FULFILLMENT_STATUS IS NULL
  ) 
  AND (
      OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
      OR OISGA.CANCEL_QUANTITY IS NULL
  )
) 
GROUP BY 
   OH.ORDER_ID, 
   OH.ORDER_NAME, 
   OH.ORDER_DATE, 
   OH.STATUS_ID, 
   OH.ENTRY_DATE, 
   UOM.ABBREVIATION, 
   OH.GRAND_TOTAL, 
   OH.PRODUCT_STORE_ID, 
   OISG.CONTACT_MECH_ID, 
   OISG.TELECOM_CONTACT_MECH_ID, 
   OISG.SHIPMENT_METHOD_TYPE_ID, 
   P.FIRST_NAME, 
   P.LAST_NAME, 
   SCENM.ENUM_CODE, 
   ODR.PARTY_ID,
   OH.EXTERNAL_ID  
ORDER BY 
   OISGINR.RESERVED_DATETIME ASC, 
   OH.ENTRY_DATE ASC

  2. SQL for Eligible Order Items for Brokered Order Item Feed with ExternalFulfillmentOrderItem entity.

    1. For all eligible orders items -

      SELECT 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  SUM(OISGINR.QUANTITY), 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
  LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_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 FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  INNER JOIN PRODUCT PD ON OI.PRODUCT_ID = PD.PRODUCT_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
   OI.STATUS_ID = 'ITEM_APPROVED' 
   AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
   AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
   AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
   AND OISG.FACILITY_ID <> '_NA_' 
   AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
   AND (
        EFO.FULFILLMENT_STATUS = 'REJECT' 
        OR EFO.FULFILLMENT_STATUS IS NULL
   ) 
   AND (
        OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
        OR OISGA.CANCEL_QUANTITY IS NULL
   )
) 
GROUP BY 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
ORDER BY 
  OH.ENTRY_DATE ASC

    2. With ORDER_ID field in conditions -

      SELECT 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  SUM(OISGINR.QUANTITY), 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
  LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_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 FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  INNER JOIN PRODUCT PD ON OI.PRODUCT_ID = PD.PRODUCT_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
    OH.ORDER_ID = '39298' 
    AND OI.STATUS_ID = 'ITEM_APPROVED' 
    AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
    AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
    AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
    AND OISG.FACILITY_ID <> '_NA_' 
    AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
    AND (
         EFO.FULFILLMENT_STATUS = 'REJECT' 
         OR EFO.FULFILLMENT_STATUS IS NULL
    ) 
    AND (
         OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
         OR OISGA.CANCEL_QUANTITY IS NULL
    )
) 
GROUP BY 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
ORDER BY 
  OH.ENTRY_DATE ASC

    3. With RESERVED_DATETIME field in condition -

      SELECT 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  SUM(OISGINR.QUANTITY), 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
  LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_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 FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  INNER JOIN PRODUCT PD ON OI.PRODUCT_ID = PD.PRODUCT_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
   OI.STATUS_ID = 'ITEM_APPROVED' 
   AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
   AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
   AND OISGINR.RESERVED_DATETIME > '2021-07-25 11:56:00' 
   AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
   AND OISG.FACILITY_ID <> '_NA_' 
   AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
   AND (
        EFO.FULFILLMENT_STATUS = 'REJECT' 
        OR EFO.FULFILLMENT_STATUS IS NULL
   ) 
   AND (
        OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
        OR OISGA.CANCEL_QUANTITY IS NULL
   )
) 
GROUP BY 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
ORDER BY 
  OH.ENTRY_DATE ASC

  3. SQL for Brokered order without ExternalFulfillmentOrderItem entity.

    1. For all eligible orders -

      SELECT 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OH.PRODUCT_STORE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID,
  OH.EXTERNAL_ID  
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
   OI.STATUS_ID = 'ITEM_APPROVED' 
   AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
   AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
   AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
   AND OISG.FACILITY_ID <> '_NA_' 
   AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
   AND (
        OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
        OR OISGA.CANCEL_QUANTITY IS NULL
   )
) 
GROUP BY 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OH.PRODUCT_STORE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID,
  OH.EXTERNAL_ID  
ORDER BY 
  OISGINR.RESERVED_DATETIME ASC, 
  OH.ENTRY_DATE ASC

    2. With RESERVED_DATETIME field in conditions -

      SELECT 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OH.PRODUCT_STORE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID,
  OH.EXTERNAL_ID  
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
   OI.STATUS_ID = 'ITEM_APPROVED' 
   AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
   AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
   AND OISGINR.RESERVED_DATETIME > '2021-07-25 11:56:00' 
   AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
   AND OISG.FACILITY_ID <> '_NA_' 
   AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
   AND (
        OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
        OR OISGA.CANCEL_QUANTITY IS NULL
   )
)       
GROUP BY 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OH.PRODUCT_STORE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID,
  OH.EXTERNAL_ID  
ORDER BY 
  OISGINR.RESERVED_DATETIME ASC, 
  OH.ENTRY_DATE ASC

  4. SQL for Brokered order items without ExternalFulfillmentOrderItem entity.

    1. For all eligible orders items -

      SELECT 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  SUM(OISGINR.QUANTITY), 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
  LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_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 FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  INNER JOIN PRODUCT PD ON OI.PRODUCT_ID = PD.PRODUCT_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
   OI.STATUS_ID = 'ITEM_APPROVED' 
   AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
   AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
   AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
   AND OISG.FACILITY_ID <> '_NA_' 
   AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
   AND (
        OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
        OR OISGA.CANCEL_QUANTITY IS NULL
   )
) 
GROUP BY 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID,
  OI.ITEM_DESCRIPTION,  
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
ORDER BY 
  OH.ENTRY_DATE ASC

    2. With ORDER_ID field in condition -

      SELECT 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  SUM(OISGINR.QUANTITY), 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
  LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_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 FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  INNER JOIN PRODUCT PD ON OI.PRODUCT_ID = PD.PRODUCT_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
   -- for a particular order item, for only order remove condition of ORDER_ID
   OH.ORDER_ID = '39298' 
   AND OI.STATUS_ID = 'ITEM_APPROVED' 
   AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
   AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE') 
   AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
   AND OISG.FACILITY_ID <> '_NA_' 
   AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
   AND (
        OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
        OR OISGA.CANCEL_QUANTITY IS NULL
   )
) 
GROUP BY 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID,
  OI.ITEM_DESCRIPTION,  
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
ORDER BY 
  OH.ENTRY_DATE ASC

    3. With RESERVED_DATETIME field in condition -

      SELECT 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  SUM(OISGINR.QUANTITY), 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID, 
  OI.ITEM_DESCRIPTION, 
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
FROM 
  ORDER_HEADER OH 
  INNER JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID 
  LEFT OUTER JOIN ENUMERATION SCENM ON OH.SALES_CHANNEL_ENUM_ID = SCENM.ENUM_ID 
  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 
  LEFT OUTER JOIN ORDER_ITEM_SHIP_GRP_INV_RES OISGINR ON OISGA.ORDER_ID = OISGINR.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = OISGINR.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = OISGINR.SHIP_GROUP_SEQ_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 
  LEFT OUTER JOIN EXTERNAL_FULFILLMENT_ORDER_ITEM EFO ON OISGA.ORDER_ID = EFO.ORDER_ID 
  AND OISGA.ORDER_ITEM_SEQ_ID = EFO.ORDER_ITEM_SEQ_ID 
  AND OISGA.SHIP_GROUP_SEQ_ID = EFO.SHIP_GROUP_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 FACILITY F ON OISG.FACILITY_ID = F.FACILITY_ID 
  INNER JOIN FACILITY_TYPE FT ON F.FACILITY_TYPE_ID = FT.FACILITY_TYPE_ID 
  LEFT OUTER JOIN ORDER_ROLE ODR ON OH.ORDER_ID = ODR.ORDER_ID 
  LEFT OUTER JOIN PERSON P ON ODR.PARTY_ID = P.PARTY_ID 
  INNER JOIN PRODUCT PD ON OI.PRODUCT_ID = PD.PRODUCT_ID 
  LEFT OUTER JOIN UOM UOM ON OH.CURRENCY_UOM = UOM.UOM_ID 
WHERE 
(
   OI.STATUS_ID = 'ITEM_APPROVED' 
   AND OH.PRODUCT_STORE_ID IN ('SM_STORE') 
   AND FT.PARENT_TYPE_ID IN ('PHYSICAL_STORE')
   AND OISGINR.RESERVED_DATETIME > '2021-07-25 11:56:00'  
   AND OH.ORDER_TYPE_ID = 'SALES_ORDER' 
   AND OISG.FACILITY_ID <> '_NA_' 
   AND ODR.ROLE_TYPE_ID = 'BILL_TO_CUSTOMER' 
   AND (
        OISGA.QUANTITY > OISGA.CANCEL_QUANTITY 
        OR OISGA.CANCEL_QUANTITY IS NULL
   )
) 
GROUP BY 
  OH.PRODUCT_STORE_ID, 
  OH.ORDER_ID, 
  OH.ORDER_NAME, 
  OH.ORDER_DATE, 
  OH.STATUS_ID, 
  OH.ENTRY_DATE, 
  OH.EXTERNAL_ID, 
  UOM.ABBREVIATION, 
  OH.GRAND_TOTAL, 
  OI.ORDER_ITEM_SEQ_ID, 
  OI.STATUS_ID, 
  OISGINR.RESERVED_DATETIME, 
  OI.UNIT_PRICE, 
  OI.EXTERNAL_ID,
  OI.ITEM_DESCRIPTION,  
  OISG.SHIP_GROUP_SEQ_ID, 
  OISG.SHIPMENT_METHOD_TYPE_ID, 
  OISG.CONTACT_MECH_ID, 
  OISG.TELECOM_CONTACT_MECH_ID, 
  OISG.ORDER_FACILITY_ID, 
  EFO.EXTERNAL_FULFILLMENT_ORDER_ITEM_ID, 
  EFO.FULFILLMENT_STATUS, 
  P.FIRST_NAME, 
  P.LAST_NAME, 
  F.FACILITY_ID, 
  F.EXTERNAL_ID, 
  FT.FACILITY_TYPE_ID, 
  FT.PARENT_TYPE_ID, 
  OS.STATUS_DATETIME, 
  PD.PRODUCT_ID, 
  PD.PRODUCT_TYPE_ID, 
  SCENM.ENUM_CODE, 
  ODR.PARTY_ID 
ORDER BY 
  OH.ENTRY_DATE ASC

Clone this wiki locally