Below is a structured list of queries for Mixed Party + Order scenarios and Inventory Management & Transfers, along with several additional questions. Each section contains the Business Problem and the Fields to Retrieve, without example SQL code.
Business Problem:
Customer Service might need to verify addresses for orders placed or completed in October 2023. This helps ensure shipments are delivered correctly and prevents address-related issues.
Fields to Retrieve:
ORDER_IDPARTY_ID(Customer ID)CUSTOMER_NAME(or FIRST_NAME / LAST_NAME)STREET_ADDRESSCITYSTATE_PROVINCEPOSTAL_CODECOUNTRY_CODEORDER_STATUSORDER_DATE
Business Problem:
Companies often want region-specific analysis to plan local marketing, staffing, or promotions in certain areas—here, specifically, New York.
Fields to Retrieve:
ORDER_IDCUSTOMER_NAMESTREET_ADDRESS(or shipping address detail)CITYSTATE_PROVINCEPOSTAL_CODETOTAL_AMOUNTORDER_DATEORDER_STATUS
Business Problem:
Merchandising teams need to identify the best-selling product(s) in a specific region (New York) for targeted restocking or promotions.
Fields to Retrieve:
PRODUCT_IDINTERNAL_NAMETOTAL_QUANTITY_SOLDCITY/STATE(within New York region)REVENUE(optionally, total sales amount)
Business Problem:
Different physical or online stores (facilities) may have varying levels of performance. The business wants to compare revenue across facilities for sales planning and budgeting.
Fields to Retrieve:
FACILITY_IDFACILITY_NAMETOTAL_ORDERSTOTAL_REVENUEDATE_RANGE
Business Problem:
Warehouse managers need to track “shrinkage” such as lost or damaged inventory to reconcile physical vs. system counts.
Fields to Retrieve:
INVENTORY_ITEM_IDPRODUCT_IDFACILITY_IDQUANTITY_LOST_OR_DAMAGEDREASON_CODE(Lost, Damaged, Expired, etc.)TRANSACTION_DATE
Business Problem:
Avoiding out-of-stock situations is critical. This report flags items that have fallen below a certain reorder threshold or have zero available stock.
Fields to Retrieve:
PRODUCT_IDPRODUCT_NAMEFACILITY_IDQOH(Quantity on Hand)ATP(Available to Promise)REORDER_THRESHOLDDATE_CHECKED
Business Problem:
The business wants to know where open orders are currently assigned, whether in a physical store or a virtual facility (e.g., a distribution center or online fulfillment location).
Fields to Retrieve:
ORDER_IDORDER_STATUSFACILITY_IDFACILITY_NAMEFACILITY_TYPE_ID
Business Problem:
Sometimes the Quantity on Hand (QOH) doesn’t match the Available to Promise (ATP) due to pending orders, reservations, or data discrepancies. This needs review for accurate fulfillment planning.
Fields to Retrieve:
PRODUCT_IDFACILITY_IDQOH(Quantity on Hand)ATP(Available to Promise)DIFFERENCE(QOH - ATP)
Business Problem:
Operations teams need to audit when an order item’s status (e.g., from “Pending” to “Shipped”) was last changed, for shipment tracking or dispute resolution.
Fields to Retrieve:
ORDER_IDORDER_ITEM_SEQ_IDCURRENT_STATUS_IDSTATUS_CHANGE_DATETIMECHANGED_BY
Business Problem:
Marketing and sales teams want to see how many orders come from each channel (e.g., web, mobile app, in-store POS, marketplace) to allocate resources effectively.
Fields to Retrieve:
SALES_CHANNELTOTAL_ORDERSTOTAL_REVENUEREPORTING_PERIOD