Spaces:
Running
Running
File size: 2,551 Bytes
69f2a47 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | -- ============================================================================
-- Views for Trade Sales Reporting
-- ============================================================================
-- View: Trade Sales Summary by Client
CREATE OR REPLACE VIEW trans.v_trade_sales_summary AS
SELECT
ts.client_id,
COUNT(DISTINCT ts.shipment_id) as total_shipments,
COUNT(DISTINCT ts.order_id) as total_orders,
COALESCE(SUM(tsi.shipped_qty), 0) as total_qty_shipped,
COUNT(DISTINCT tsi.sku) as unique_skus_shipped,
MIN(ts.shipment_date) as first_shipment_date,
MAX(ts.shipment_date) as last_shipment_date,
COUNT(DISTINCT CASE WHEN ts.status = 'shipped' THEN ts.shipment_id END) as shipped_count,
COUNT(DISTINCT CASE WHEN ts.status = 'draft' THEN ts.shipment_id END) as draft_count
FROM trans.scm_trade_shipment ts
LEFT JOIN trans.scm_trade_shipment_item tsi ON ts.shipment_id = tsi.shipment_id
GROUP BY ts.client_id;
-- View: Order Execution Status
CREATE OR REPLACE VIEW trans.v_order_execution_status AS
WITH po_summary AS (
SELECT
po_id,
SUM(ord_qty) as total_ordered_qty
FROM trans.scm_po_item
GROUP BY po_id
),
shipment_summary AS (
SELECT
poi.po_id,
SUM(tsi.shipped_qty) as total_shipped_qty,
COUNT(DISTINCT tsi.shipment_id) as shipment_count,
MAX(ts.shipment_date) as last_shipment_date
FROM trans.scm_trade_shipment_item tsi
JOIN trans.scm_po_item poi ON tsi.po_item_id = poi.po_item_id
JOIN trans.scm_trade_shipment ts ON tsi.shipment_id = ts.shipment_id
GROUP BY poi.po_id
)
SELECT
po.po_id as order_id,
po.po_no as order_no,
po.buyer_id as client_id,
po.supplier_id,
po.po_date as order_date,
COALESCE(pos.total_ordered_qty, 0) as total_ordered_qty,
COALESCE(ss.total_shipped_qty, 0) as total_shipped_qty,
COALESCE(pos.total_ordered_qty, 0) - COALESCE(ss.total_shipped_qty, 0) as pending_qty,
CASE
WHEN COALESCE(ss.total_shipped_qty, 0) = 0 THEN 'pending'
WHEN COALESCE(ss.total_shipped_qty, 0) < COALESCE(pos.total_ordered_qty, 0) THEN 'partial'
WHEN COALESCE(ss.total_shipped_qty, 0) >= COALESCE(pos.total_ordered_qty, 0) THEN 'completed'
ELSE 'pending'
END as execution_status,
COALESCE(ss.shipment_count, 0) as shipment_count,
ss.last_shipment_date
FROM trans.scm_po po
LEFT JOIN po_summary pos ON po.po_id = pos.po_id
LEFT JOIN shipment_summary ss ON po.po_id = ss.po_id
WHERE po.supplier_type = 'cnf' AND po.buyer_type = 'distributor';
|