AiDOPWarehouse/doc/plan/sql/uat-data/_tmp_missing_kpi_facts.sql

SELECT tenant_id,factory_id,trans_type,COUNT(*) row_count,
       SUM(IFNULL(real_qty,0)) real_qty,SUM(IFNULL(qty_to_ship,0)) qty_to_ship
FROM mdp_std_ship_trans
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
GROUP BY tenant_id,factory_id,trans_type
ORDER BY tenant_id,factory_id,trans_type;

SELECT tenant_id,factory_id,COUNT(*) row_count,
       SUM(IFNULL(shipped_qty,0)) shipped_qty,SUM(IFNULL(remaining_qty,0)) remaining_qty,
       MAX(calc_time) latest_calc
FROM dwd_ship_trans
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
GROUP BY tenant_id,factory_id
ORDER BY tenant_id,factory_id;

SELECT tenant_id,factory_id,biz_month,COUNT(*) row_count,
       SUM(avg_inventory_value) inventory_value,SUM(monthly_outbound_cost) outbound_cost,
       AVG(turnover_days) turnover_days
FROM dwd_t8_material_inventory_turnover
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
GROUP BY tenant_id,factory_id,biz_month
ORDER BY tenant_id,factory_id,biz_month DESC;

SELECT tenant_id,lbs,COUNT(*) head_count,MAX(shtime) latest_date
FROM mdp_std_t8_kc_tz_head
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
GROUP BY tenant_id,lbs
ORDER BY tenant_id,lbs;

SELECT id,tenant_id,status,error_message,summary_json,start_time,end_time
FROM mdp_transform_run_log
WHERE job_code='S3_MDP_SYNC_TRANSFORM'
  AND tenant_id IN (838257186181189,838257212780613,838257237606469)
ORDER BY id DESC
LIMIT 9;

SELECT tenant_id,factory_id,module_code,metric_code,metric_value,biz_date,calc_time
FROM ado_s9_kpi_value_l1_day
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
  AND factory_id=1
  AND metric_code IN ('S1_L1_004','S3_L1_004','S9_L1_001')
  AND is_deleted=0
ORDER BY tenant_id,metric_code,biz_date DESC;

SELECT id,tenant_id,status,error_message,summary_json,start_time,end_time
FROM mdp_transform_run_log
WHERE job_code='S7_MDP_SYNC_TRANSFORM'
  AND tenant_id IN (838257186181189,838257212780613,838257237606469)
ORDER BY id DESC
LIMIT 6;

SELECT tenant_id,factory_id,LEFT(bill_no,20) bill_prefix,COUNT(*) row_count
FROM crm_seorder
WHERE tenant_id IN (838257186181189,838257212780613,838257237606469)
GROUP BY tenant_id,factory_id,LEFT(bill_no,20)
ORDER BY tenant_id,factory_id,row_count DESC;