996 lines
53 KiB
SQL
996 lines
53 KiB
SQL
|
|
--------------------------Order Level Data --------------------------
|
|
Drop table if exists public.Gm_final_table;
|
|
|
|
CREATE TABLE public.Gm_final_table AS
|
|
SELECT A.external_id,
|
|
cast(transaction_date as date) transaction_date ,
|
|
A.order_discount_amount,
|
|
A.shipping_cost,
|
|
A.shipping_province,
|
|
A.coupon_code,
|
|
A.discount_type,
|
|
A.coupon_applied_on,
|
|
A.brand_name,
|
|
B.product_type,
|
|
A.category_name,
|
|
B.name sku_name,
|
|
A.reseller_name,
|
|
A.reseller_email,
|
|
A.reseller_mobile,
|
|
A.tier_name ,
|
|
A.reseller_id,
|
|
A.sku,
|
|
A.quantity,
|
|
A.retail_price,
|
|
A.seller_margin,
|
|
A.discounted_price,
|
|
A.additional_discount,
|
|
A.item_type ,
|
|
A.cogs
|
|
FROM gm_dashboard A
|
|
LEFT JOIN raena_catalog_management.product B ON A.sku = B.sku;
|
|
|
|
Drop table if exists public.final_order_level_data;
|
|
|
|
CREATE TABLE public.final_order_level_data AS
|
|
SELECT A.*,
|
|
CURRENT_DATE,
|
|
date_part('Month',CURRENT_DATE) month_of_date,
|
|
date_part('Week',CURRENT_DATE) Week_of_date,
|
|
date_part('Day',CURRENT_DATE) Day_of_date,
|
|
retail_price*quantity AS revenue_on_retail,
|
|
(retail_price*quantity)-(seller_margin*quantity) AS revenue_on_wholesale,
|
|
(discounted_price*quantity) AS revenue_on_after_discount,
|
|
(cogs*quantity) AS total_cogs,
|
|
case when retail_price<>0 then cast(((cast(((retail_price*quantity)-(seller_margin*quantity)) AS decimal(22,2))-(cast(cogs*quantity AS decimal(22,2))))/cast(((retail_price*quantity)-(seller_margin*quantity)) AS decimal(22,2)))*100 AS decimal(22,2)) end pre_discount_gm,
|
|
case when discounted_price<>0 then cast(((cast((discounted_price*quantity) AS decimal(22,2))-(cast(cogs*quantity AS decimal(22,2))))/cast(((discounted_price*quantity)) AS decimal(22,2)))*100 AS decimal(22,2)) end post_discount_gm,
|
|
shipping_city,
|
|
shipping_pincode,
|
|
shipping_country_code,
|
|
order_warehouse
|
|
FROM public.Gm_final_table A
|
|
LEFT JOIN
|
|
(SELECT id ,
|
|
shipping_city,
|
|
shipping_pincode,
|
|
shipping_country_code,
|
|
order_warehouse
|
|
FROM raena_order_management.order
|
|
WHERE cast(created_at AS date) >=dateadd(d,-120,'2023-11-22')) B ON external_id =id
|
|
WHERE discounted_price <>0 and cast(transaction_date AS date) between dateadd(d,-120,'2023-11-22') and '2023-11-22';
|
|
|
|
-------------------------------------------SKU Level Data ---------------------------------
|
|
|
|
|
|
DROP TABLE IF EXISTS public.sku_warehouse_split_stage1;
|
|
|
|
CREATE TABLE public.sku_warehouse_split_stage1 AS
|
|
SELECT sku ,
|
|
CASE
|
|
WHEN shipping_province IN ('Kalimantan Timur',
|
|
'Kalimantan Utara',
|
|
'Kalimantan Selatan',
|
|
'Kalimantan Tengah',
|
|
'Kalimantan Barat') THEN 'Balikpapan - Semarandi'
|
|
WHEN shipping_province IN ('DKI Jakarta',
|
|
'Banten',
|
|
'Jawa Barat') THEN 'Bekasi'
|
|
WHEN shipping_province IN ('Sulawesi Selatan',
|
|
'Sulawesi Tengah',
|
|
'Gorontalo',
|
|
'Papua',
|
|
'Maluku Utara',
|
|
'Sulawesi Utara',
|
|
'Maluku',
|
|
'Sulawesi Tenggara',
|
|
'Papua Barat',
|
|
'Sulawesi Barat') THEN 'Makassar'
|
|
WHEN shipping_province IN ('Sumatera Utara',
|
|
'Sumatera Selatan',
|
|
'Riau',
|
|
'Sumatera Barat',
|
|
'Lampung',
|
|
'Aceh',
|
|
'Bangka Belitung',
|
|
'Jambi',
|
|
'Nanggroe Aceh Darussalam (NAD)',
|
|
'Kepulauan Riau',
|
|
'Bengkulu') THEN 'Pekanbaru - Medan'
|
|
WHEN shipping_province IN ('Jawa Tengah',
|
|
'DI Yogyakarta') THEN 'Semarang'
|
|
WHEN shipping_province IN ('Jawa Timur',
|
|
'Bali',
|
|
'Nusa Tenggara Timur (NTT)',
|
|
'Nusa Tenggara Timur',
|
|
'Nusa Tenggara Barat',
|
|
'Nusa Tenggara Barat (NTB)') THEN 'Surabaya'
|
|
END warehouse,
|
|
CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2023-11-22') AND '2023-11-22' THEN 'L7D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2023-11-22') AND dateadd(d,-7,'2023-11-22') THEN 'L14D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2023-11-22') AND dateadd(d,-14,'2023-11-22') THEN 'L21D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2023-11-22') AND dateadd(d,-21,'2023-11-22') THEN 'L28D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2023-11-22') AND dateadd(d,-28,'2023-11-22') THEN 'L35D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2023-11-22') AND dateadd(d,-35,'2023-11-22') THEN 'L42D'
|
|
END Bucket,
|
|
Total_revenue,
|
|
sum(revenue_on_after_discount) revenue_on_after_discount,
|
|
sum(revenue_on_after_discount)*100/Total_revenue AS contribution
|
|
FROM public.final_order_level_data A
|
|
LEFT JOIN
|
|
(SELECT CASE
|
|
WHEN shipping_province IN ('Kalimantan Timur',
|
|
'Kalimantan Utara',
|
|
'Kalimantan Selatan',
|
|
'Kalimantan Tengah',
|
|
'Kalimantan Barat') THEN 'Balikpapan - Semarandi'
|
|
WHEN shipping_province IN ('DKI Jakarta',
|
|
'Banten',
|
|
'Jawa Barat') THEN 'Bekasi'
|
|
WHEN shipping_province IN ('Sulawesi Selatan',
|
|
'Sulawesi Tengah',
|
|
'Gorontalo',
|
|
'Papua',
|
|
'Maluku Utara',
|
|
'Sulawesi Utara',
|
|
'Maluku',
|
|
'Sulawesi Tenggara',
|
|
'Papua Barat',
|
|
'Sulawesi Barat') THEN 'Makassar'
|
|
WHEN shipping_province IN ('Sumatera Utara',
|
|
'Sumatera Selatan',
|
|
'Riau',
|
|
'Sumatera Barat',
|
|
'Lampung',
|
|
'Aceh',
|
|
'Bangka Belitung',
|
|
'Jambi',
|
|
'Nanggroe Aceh Darussalam (NAD)',
|
|
'Kepulauan Riau',
|
|
'Bengkulu') THEN 'Pekanbaru - Medan'
|
|
WHEN shipping_province IN ('Jawa Tengah',
|
|
'DI Yogyakarta') THEN 'Semarang'
|
|
WHEN shipping_province IN ('Jawa Timur',
|
|
'Bali',
|
|
'Nusa Tenggara Timur (NTT)',
|
|
'Nusa Tenggara Timur',
|
|
'Nusa Tenggara Barat',
|
|
'Nusa Tenggara Barat (NTB)') THEN 'Surabaya'
|
|
END warehouse,
|
|
CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2023-11-22') AND '2023-11-22' THEN 'L7D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2023-11-22') AND dateadd(d,-7,'2023-11-22') THEN 'L14D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2023-11-22') AND dateadd(d,-14,'2023-11-22') THEN 'L21D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2023-11-22') AND dateadd(d,-21,'2023-11-22') THEN 'L28D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2023-11-22') AND dateadd(d,-28,'2023-11-22') THEN 'L35D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2023-11-22') AND dateadd(d,-35,'2023-11-22') THEN 'L42D'
|
|
END Bucket,
|
|
sum(coalesce (revenue_on_after_discount,0)) Total_revenue
|
|
FROM public.final_order_level_data
|
|
WHERE cast(transaction_date as date) BETWEEN dateadd(d,-41,'2023-11-22') AND '2023-11-22'
|
|
GROUP BY CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2023-11-22') AND '2023-11-22' THEN 'L7D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2023-11-22') AND dateadd(d,-7,'2023-11-22') THEN 'L14D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2023-11-22') AND dateadd(d,-14,'2023-11-22') THEN 'L21D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2023-11-22') AND dateadd(d,-21,'2023-11-22') THEN 'L28D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2023-11-22') AND dateadd(d,-28,'2023-11-22') THEN 'L35D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2023-11-22') AND dateadd(d,-35,'2023-11-22') THEN 'L42D'
|
|
END,
|
|
CASE
|
|
WHEN shipping_province IN ('Kalimantan Timur',
|
|
'Kalimantan Utara',
|
|
'Kalimantan Selatan',
|
|
'Kalimantan Tengah',
|
|
'Kalimantan Barat') THEN 'Balikpapan - Semarandi'
|
|
WHEN shipping_province IN ('DKI Jakarta',
|
|
'Banten',
|
|
'Jawa Barat') THEN 'Bekasi'
|
|
WHEN shipping_province IN ('Sulawesi Selatan',
|
|
'Sulawesi Tengah',
|
|
'Gorontalo',
|
|
'Papua',
|
|
'Maluku Utara',
|
|
'Sulawesi Utara',
|
|
'Maluku',
|
|
'Sulawesi Tenggara',
|
|
'Papua Barat',
|
|
'Sulawesi Barat') THEN 'Makassar'
|
|
WHEN shipping_province IN ('Sumatera Utara',
|
|
'Sumatera Selatan',
|
|
'Riau',
|
|
'Sumatera Barat',
|
|
'Lampung',
|
|
'Aceh',
|
|
'Bangka Belitung',
|
|
'Jambi',
|
|
'Nanggroe Aceh Darussalam (NAD)',
|
|
'Kepulauan Riau',
|
|
'Bengkulu') THEN 'Pekanbaru - Medan'
|
|
WHEN shipping_province IN ('Jawa Tengah',
|
|
'DI Yogyakarta') THEN 'Semarang'
|
|
WHEN shipping_province IN ('Jawa Timur',
|
|
'Bali',
|
|
'Nusa Tenggara Timur (NTT)',
|
|
'Nusa Tenggara Timur',
|
|
'Nusa Tenggara Barat',
|
|
'Nusa Tenggara Barat (NTB)') THEN 'Surabaya'
|
|
END) B ON CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2023-11-22') AND '2023-11-22' THEN 'L7D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2023-11-22') AND dateadd(d,-7,'2023-11-22') THEN 'L14D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2023-11-22') AND dateadd(d,-14,'2023-11-22') THEN 'L21D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2023-11-22') AND dateadd(d,-21,'2023-11-22') THEN 'L28D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2023-11-22') AND dateadd(d,-28,'2023-11-22') THEN 'L35D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2023-11-22') AND dateadd(d,-35,'2023-11-22') THEN 'L42D'
|
|
END=B.bucket
|
|
AND CASE
|
|
WHEN shipping_province IN ('Kalimantan Timur',
|
|
'Kalimantan Utara',
|
|
'Kalimantan Selatan',
|
|
'Kalimantan Tengah',
|
|
'Kalimantan Barat') THEN 'Balikpapan - Semarandi'
|
|
WHEN shipping_province IN ('DKI Jakarta',
|
|
'Banten',
|
|
'Jawa Barat') THEN 'Bekasi'
|
|
WHEN shipping_province IN ('Sulawesi Selatan',
|
|
'Sulawesi Tengah',
|
|
'Gorontalo',
|
|
'Papua',
|
|
'Maluku Utara',
|
|
'Sulawesi Utara',
|
|
'Maluku',
|
|
'Sulawesi Tenggara',
|
|
'Papua Barat',
|
|
'Sulawesi Barat') THEN 'Makassar'
|
|
WHEN shipping_province IN ('Sumatera Utara',
|
|
'Sumatera Selatan',
|
|
'Riau',
|
|
'Sumatera Barat',
|
|
'Lampung',
|
|
'Aceh',
|
|
'Bangka Belitung',
|
|
'Jambi',
|
|
'Nanggroe Aceh Darussalam (NAD)',
|
|
'Kepulauan Riau',
|
|
'Bengkulu') THEN 'Pekanbaru - Medan'
|
|
WHEN shipping_province IN ('Jawa Tengah',
|
|
'DI Yogyakarta') THEN 'Semarang'
|
|
WHEN shipping_province IN ('Jawa Timur',
|
|
'Bali',
|
|
'Nusa Tenggara Timur (NTT)',
|
|
'Nusa Tenggara Timur',
|
|
'Nusa Tenggara Barat',
|
|
'Nusa Tenggara Barat (NTB)') THEN 'Surabaya'
|
|
END=warehouse
|
|
WHERE cast(transaction_date as date) BETWEEN dateadd(d,-41,'2023-11-22') AND '2023-11-22'
|
|
GROUP BY sku ,
|
|
CASE
|
|
WHEN shipping_province IN ('Kalimantan Timur',
|
|
'Kalimantan Utara',
|
|
'Kalimantan Selatan',
|
|
'Kalimantan Tengah',
|
|
'Kalimantan Barat') THEN 'Balikpapan - Semarandi'
|
|
WHEN shipping_province IN ('DKI Jakarta',
|
|
'Banten',
|
|
'Jawa Barat') THEN 'Bekasi'
|
|
WHEN shipping_province IN ('Sulawesi Selatan',
|
|
'Sulawesi Tengah',
|
|
'Gorontalo',
|
|
'Papua',
|
|
'Maluku Utara',
|
|
'Sulawesi Utara',
|
|
'Maluku',
|
|
'Sulawesi Tenggara',
|
|
'Papua Barat',
|
|
'Sulawesi Barat') THEN 'Makassar'
|
|
WHEN shipping_province IN ('Sumatera Utara',
|
|
'Sumatera Selatan',
|
|
'Riau',
|
|
'Sumatera Barat',
|
|
'Lampung',
|
|
'Aceh',
|
|
'Bangka Belitung',
|
|
'Jambi',
|
|
'Nanggroe Aceh Darussalam (NAD)',
|
|
'Kepulauan Riau',
|
|
'Bengkulu') THEN 'Pekanbaru - Medan'
|
|
WHEN shipping_province IN ('Jawa Tengah',
|
|
'DI Yogyakarta') THEN 'Semarang'
|
|
WHEN shipping_province IN ('Jawa Timur',
|
|
'Bali',
|
|
'Nusa Tenggara Timur (NTT)',
|
|
'Nusa Tenggara Timur',
|
|
'Nusa Tenggara Barat',
|
|
'Nusa Tenggara Barat (NTB)') THEN 'Surabaya'
|
|
END,
|
|
CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-6,'2023-11-22') AND '2023-11-22' THEN 'L7D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-13,'2023-11-22') AND dateadd(d,-7,'2023-11-22') THEN 'L14D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-20,'2023-11-22') AND dateadd(d,-14,'2023-11-22') THEN 'L21D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-27,'2023-11-22') AND dateadd(d,-21,'2023-11-22') THEN 'L28D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-34,'2023-11-22') AND dateadd(d,-28,'2023-11-22') THEN 'L35D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-41,'2023-11-22') AND dateadd(d,-35,'2023-11-22') THEN 'L42D'
|
|
END ,
|
|
Total_revenue;
|
|
|
|
DROP TABLE IF EXISTS public.sku_warehouse_split_stage2;
|
|
|
|
CREATE TABLE public.sku_warehouse_split_stage2 AS
|
|
SELECT sku ,
|
|
warehouse ,
|
|
sum(CASE WHEN bucket = 'L7D' THEN contribution END) L7D_sku_contribution,
|
|
sum(CASE WHEN bucket = 'L14D' THEN contribution END) L14D_sku_contribution,
|
|
sum(CASE WHEN bucket = 'L21D' THEN contribution END) L21D_sku_contribution,
|
|
sum(CASE WHEN bucket = 'L28D' THEN contribution END) L28D_sku_contribution,
|
|
sum(CASE WHEN bucket = 'L35D' THEN contribution END) L35D_sku_contribution,
|
|
sum(CASE WHEN bucket = 'L42D' THEN contribution END) L42D_sku_contribution,
|
|
percentile_cont(0.5) within
|
|
GROUP (
|
|
ORDER BY contribution) median_contribution,
|
|
percentile_cont(0.95) within
|
|
GROUP (
|
|
ORDER BY contribution) Percentile95_contribution,
|
|
coalesce(stddev_samp(contribution),sum(contribution)) sd_contribution
|
|
FROM public.sku_warehouse_split_stage1
|
|
WHERE warehouse IS NOT NULL
|
|
GROUP BY sku,
|
|
warehouse ;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.sku_warehouse_split_stage3;
|
|
|
|
|
|
CREATE TABLE public.sku_warehouse_split_stage3 AS
|
|
SELECT * , (CASE
|
|
WHEN sd_contribution <= 0.1 THEN median_contribution
|
|
ELSE Percentile95_contribution
|
|
END)sku_qty_split_next_order
|
|
FROM public.sku_warehouse_split_stage2;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.sku_warehouse_split_final;
|
|
|
|
|
|
CREATE TABLE public.sku_warehouse_split_final AS
|
|
SELECT sku,
|
|
warehouse,
|
|
l7d_sku_contribution,
|
|
L14D_sku_contribution,
|
|
L21D_sku_contribution,
|
|
L28D_sku_contribution,
|
|
L35D_sku_contribution,
|
|
L42D_sku_contribution,
|
|
median_contribution,
|
|
Percentile95_contribution,
|
|
sd_contribution,
|
|
case when sum(sku_qty_split_next_order)over(partition BY sku)= 0 then 0 else sku_qty_split_next_order*100/sum(sku_qty_split_next_order)over(partition BY sku) end sku_qty_split_next_order_percentage
|
|
FROM public.sku_warehouse_split_stage3;
|
|
|
|
--------------------------------------Warehouse SKu Split----------------------------------
|
|
|
|
|
|
DROP TABLE IF EXISTS public.sku_level_data ;
|
|
|
|
CREATE TABLE public.sku_level_data AS
|
|
SELECT A.sku,
|
|
A.name sku_name,
|
|
A.is_private,
|
|
A.country,
|
|
CASE
|
|
WHEN A.is_archived = 'true' THEN 'Permanatly_delisted'
|
|
WHEN A.is_delisted = 'true' THEN 'delisted'
|
|
WHEN A.is_archived = 'false'
|
|
AND A.is_delisted = 'false' THEN 'Active'
|
|
END active_status,
|
|
A.created_at,
|
|
B.storage_location storage_type,
|
|
A.origin sourcing,
|
|
case when C.business_type like '%Consignment%' then 'Consignment' else 'Outright' end ordering_type ,
|
|
D.name brand_name ,
|
|
A.product_type ,
|
|
E.name category_name,
|
|
F.cogs ,
|
|
CASE
|
|
WHEN A.is_slash_price ='true'THEN cast(json_extract_path_text(slashed_tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) AS decimal(22,2))
|
|
ELSE cast(json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) AS decimal(22,2))
|
|
END effective_wholesale_price,
|
|
CASE
|
|
WHEN A.origin= 'Local' THEN coalesce(G.bekasi_buffer_time,2)
|
|
WHEN A.origin = 'Crossborder' THEN 15
|
|
END Bekasi_buffer_lead_time,
|
|
CASE
|
|
WHEN A.origin= 'Local' THEN coalesce(G.bekasi_lead_time,5)
|
|
WHEN A.origin = 'Crossborder' THEN 50
|
|
END bekasi_lead_time,
|
|
CASE
|
|
WHEN A.origin= 'Local' THEN (coalesce(G.bekasi_lead_time,5)+coalesce(G.bekasi_buffer_time,2))
|
|
WHEN A.origin = 'Crossborder' THEN 65
|
|
END bekasi_total_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN 45
|
|
WHEN origin = 'Crossborder' THEN 30
|
|
END bekasi_inventory_days,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(G.semarinda_buffer_time,2)
|
|
WHEN origin = 'Crossborder' THEN 15
|
|
END Balikpapan_Semarandi_buffer_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(G.semarinda_lead_time,12)
|
|
WHEN origin = 'Crossborder' THEN 50
|
|
END Balikpapan_Semarandi_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN (coalesce(G.semarinda_lead_time,12)+coalesce(G.semarinda_buffer_time,2))
|
|
WHEN origin = 'Crossborder' THEN 65
|
|
END Balikpapan_Semarandi_total_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN 30
|
|
WHEN origin = 'Crossborder' THEN 30
|
|
END Balikpapan_Semarandi_inventory_days,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(G.pekanbaru_buffer_time,2)
|
|
WHEN origin = 'Crossborder' THEN 15
|
|
END Pekanbaru_Medan_buffer_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(G.pekanbaru_lead_time,12)
|
|
WHEN origin = 'Crossborder' THEN 50
|
|
END Pekanbaru_Medan_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN (coalesce(G.pekanbaru_lead_time,12)+coalesce(G.pekanbaru_buffer_time,2))
|
|
WHEN origin = 'Crossborder' THEN 65
|
|
END Pekanbaru_Medan_total_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN 30
|
|
WHEN origin = 'Crossborder' THEN 30
|
|
END Pekanbaru_Medan_inventory_days,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(semarang_buffer_time,2)
|
|
WHEN origin = 'Crossborder' THEN 15
|
|
END Semarang_buffer_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(semarang_lead_time,7)
|
|
WHEN origin = 'Crossborder' THEN 50
|
|
END Semarang_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN ( coalesce(semarang_lead_time,7)+coalesce(semarang_buffer_time,2))
|
|
WHEN origin = 'Crossborder' THEN 65
|
|
END Semarang_total_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN 30
|
|
WHEN origin = 'Crossborder' THEN 30
|
|
END Semarang_inventory_days,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(surabaya_buffer_time,2)
|
|
WHEN origin = 'Crossborder' THEN 15
|
|
END Surabaya_buffer_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(surabaya_lead_time,7)
|
|
WHEN origin = 'Crossborder' THEN 50
|
|
END Surabaya_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(surabaya_lead_time,7)+coalesce(surabaya_buffer_time,2)
|
|
WHEN origin = 'Crossborder' THEN 65
|
|
END Surabaya_total_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN 30
|
|
WHEN origin = 'Crossborder' THEN 30
|
|
END Surabaya_inventory_days,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(makassar_buffer_time,2)
|
|
WHEN origin = 'Crossborder' THEN 15
|
|
END Makassar_buffer_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN coalesce(makassar_lead_time,12)
|
|
WHEN origin = 'Crossborder' THEN 50
|
|
END Makassar_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN (coalesce(makassar_lead_time,12)+coalesce(makassar_buffer_time,2))
|
|
WHEN origin = 'Crossborder' THEN 65
|
|
END Makassar_total_lead_time,
|
|
CASE
|
|
WHEN origin= 'Local' THEN 30
|
|
WHEN origin = 'Crossborder' THEN 30
|
|
END Makassar_inventory_days,
|
|
multiple_flag,
|
|
G.moq,
|
|
moqremark,
|
|
case when moqremark = 'NOT ORDER GIVE AWAY ITEM' then 'Yes' else 'No' end give_away
|
|
FROM raena_catalog_management.product A
|
|
LEFT JOIN public.storage_data B ON A.sku=B.sku
|
|
left join public.demand_forecasting_configs G on A.sku= G.sku
|
|
LEFT JOIN public.outright_consignment C ON A.sku = C.sku
|
|
LEFT JOIN raena_catalog_management.brand D ON A.brand_id = D.id
|
|
LEFT JOIN raena_catalog_management.category E ON A.category_id =E.id
|
|
LEFT JOIN (select distinct sku sku_code ,cogs_non_promo cogs from public.sku_cogs_audit sca where sku_cogs_action='CREATED') F ON A.sku= F.sku_code ;
|
|
---------------------------_basic Calculation -------------------------
|
|
|
|
DROP TABLE IF EXISTS public.basic_calculation;
|
|
|
|
|
|
CREATE TABLE public.basic_calculation AS
|
|
SELECT sku ,
|
|
CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2023-11-22') AND '2023-11-22' THEN 'L30D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2023-11-22') AND dateadd(d,-31,'2023-11-22') THEN 'L60D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2023-11-22') AND dateadd(d,-61,'2023-11-22') THEN 'L90D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2023-11-22') AND dateadd(d,-91,'2023-11-22') THEN 'L120D'
|
|
END txn_period,
|
|
sum(revenue_on_after_discount) revenue,
|
|
Total_revenue,
|
|
sum(revenue_on_after_discount)*100/Total_revenue AS contribution,
|
|
sum(quantity) quantity,
|
|
count(DISTINCT transaction_date) days,
|
|
cast(cast(sum(quantity) as FLOAT)/cast(count(DISTINCT cast(transaction_date as date) ) as FLOAT) as FLOAT) runrate
|
|
FROM public.final_order_level_data A
|
|
LEFT JOIN
|
|
(SELECT CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2023-11-22') AND '2023-11-22' THEN 'L30D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2023-11-22') AND dateadd(d,-31,'2023-11-22') THEN 'L60D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2023-11-22') AND dateadd(d,-61,'2023-11-22') THEN 'L90D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2023-11-22') AND dateadd(d,-91,'2023-11-22') THEN 'L120D'
|
|
END txn_period,
|
|
sum(coalesce (revenue_on_after_discount,0)) Total_revenue
|
|
FROM public.final_order_level_data
|
|
WHERE cast(transaction_date as date) BETWEEN dateadd(d,-120,'2023-11-22') AND '2023-11-22'
|
|
GROUP BY CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2023-11-22') AND '2023-11-22' THEN 'L30D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2023-11-22') AND dateadd(d,-31,'2023-11-22') THEN 'L60D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2023-11-22') AND dateadd(d,-61,'2023-11-22') THEN 'L90D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2023-11-22') AND dateadd(d,-91,'2023-11-22') THEN 'L120D'
|
|
END) B ON (CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2023-11-22') AND '2023-11-22' THEN 'L30D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2023-11-22') AND dateadd(d,-31,'2023-11-22') THEN 'L60D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2023-11-22') AND dateadd(d,-61,'2023-11-22') THEN 'L90D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2023-11-22') AND dateadd(d,-91,'2023-11-22') THEN 'L120D'
|
|
END)=B.txn_period
|
|
WHERE cast(transaction_date as date) BETWEEN dateadd(d,-120,'2023-11-22') AND '2023-11-22'
|
|
GROUP BY sku,
|
|
CASE
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-30,'2023-11-22') AND '2023-11-22' THEN 'L30D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-60,'2023-11-22') AND dateadd(d,-31,'2023-11-22') THEN 'L60D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-90,'2023-11-22') AND dateadd(d,-61,'2023-11-22') THEN 'L90D'
|
|
WHEN cast(transaction_date as date) BETWEEN dateadd(d,-120,'2023-11-22') AND dateadd(d,-91,'2023-11-22') THEN 'L120D'
|
|
END,
|
|
Total_revenue;
|
|
|
|
select 1;
|
|
|
|
DROP TABLE IF EXISTS public.basic_calculation_stage1;
|
|
|
|
|
|
CREATE TABLE public.basic_calculation_stage1 AS
|
|
SELECT A.sku ,
|
|
gold_price ,
|
|
CASE
|
|
WHEN L120D_revenue = 0
|
|
AND L90D_revenue = 0
|
|
AND L60D_revenue = 0 THEN 0
|
|
ELSE ((CASE WHEN L120D_revenue = 0 THEN 0 ELSE ((L90D_revenue -L120D_revenue)*100/L120D_revenue) END)
|
|
+ (CASE WHEN L90D_revenue= 0 THEN 0 ELSE ((L60D_revenue -L90D_revenue)*100/L90D_revenue) END)
|
|
+(CASE WHEN L60D_revenue = 0 THEN 0 ELSE ((L30D_revenue -L60D_revenue)*100/L60D_revenue)END)) / (CASE
|
|
WHEN L120D_revenue = 0 THEN 0
|
|
ELSE 1
|
|
END + CASE
|
|
WHEN L90D_revenue = 0 THEN 0
|
|
ELSE 1
|
|
END+CASE
|
|
WHEN L60D_revenue = 0 THEN 0
|
|
ELSE 1
|
|
END)
|
|
END AVG_revenue_growth
|
|
FROM
|
|
(SELECT sku ,
|
|
SUM(CASE WHEN txn_period ='L120D' THEN coalesce(revenue,0) ELSE 0 END) L120D_revenue,
|
|
SUM(CASE WHEN txn_period ='L90D' THEN coalesce(revenue,0) ELSE 0 END) L90D_revenue,
|
|
sum(CASE WHEN txn_period ='L60D' THEN coalesce(revenue,0) ELSE 0 END) L60D_revenue,
|
|
sum(CASE WHEN txn_period ='L30D' THEN coalesce(revenue,0) ELSE 0 END) L30D_revenue
|
|
FROM public.basic_calculation
|
|
GROUP BY sku) A
|
|
LEFT JOIN
|
|
(SELECT sku ,
|
|
json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',TRUE) gold_price
|
|
FROM raena_catalog_management.product p) B ON A.sku = B.sku;
|
|
|
|
|
|
Drop table if exists public.basic_calculation_stage1_v1;
|
|
|
|
create table public.basic_calculation_stage1_v1 as
|
|
select A.*,
|
|
abs(contribution-median_contribution) for_max_contribution
|
|
from public.basic_calculation A
|
|
left join (select sku ,
|
|
MEDIAN(contribution) median_contribution
|
|
from public.basic_calculation group by sku ) B
|
|
on A.sku = B.sku ;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS public.basic_calculation_stage2;
|
|
|
|
|
|
CREATE TABLE public.basic_calculation_stage2 AS
|
|
SELECT A.SKu ,
|
|
gold_price,
|
|
sum(CASE WHEN txn_period ='L30D' THEN coalesce(revenue,0) ELSE 0 END) L30D_revenue ,
|
|
B.AVG_revenue_growth ,
|
|
10 AS avg_expected_revenue_growth,
|
|
sum(CASE WHEN txn_period ='L30D' THEN coalesce(revenue,0) ELSE 0 END)*(1.10) expected_30D_revenue,
|
|
MEDIAN(contribution) median_of_contribution,
|
|
coalesce(stddev_samp(contribution),sum(contribution)) sd_of_contribution,
|
|
max(contribution) max_contribution ,
|
|
max(for_max_contribution) for_max_contribution,
|
|
avg(quantity) avg_quantity,
|
|
max(quantity) max_quantity,
|
|
case when L90D_revenue= 0 and L60D_revenue= 0 and L30D_revenue >0 then 'Yes' else 'No' end New_sku
|
|
FROM public.basic_calculation_stage1_v1 A
|
|
LEFT JOIN public.basic_calculation_stage1 B ON A.sku=B.sku
|
|
left join (select sku ,
|
|
coalesce(sum(case when txn_period ='L30D' then coalesce(revenue,0) end),0) as l30D_revenue,
|
|
coalesce(sum(case when txn_period ='L60D' then coalesce(revenue,0) end),0) as l60D_revenue,
|
|
coalesce(sum(case when txn_period ='L90D' then coalesce(revenue,0) end),0) as l90D_revenue
|
|
from public.basic_calculation_stage1_v1
|
|
where txn_period <> 'L120D'
|
|
group by sku) C On A.sku = C.sku
|
|
WHERE txn_period <> 'L120D'
|
|
GROUP BY A.SKu,
|
|
B.AVG_revenue_growth,
|
|
gold_price,
|
|
case when L90D_revenue= 0 and L60D_revenue= 0 and L30D_revenue >0 then 'Yes' else 'No' end;
|
|
|
|
|
|
--SELECT *FROM public.basic_calculation_stage2
|
|
|
|
DROP TABLE IF EXISTS public.basic_calculation_stage3;
|
|
|
|
/*
|
|
CREATE TABLE public.basic_calculation_stage3 AS
|
|
SELECT A.SKu,
|
|
percentile_cont(0.5) within
|
|
GROUP (
|
|
ORDER BY runrate) median_runrate,
|
|
max(runrate) max_runrate,
|
|
sum(CASE WHEN txn_period ='L30D' THEN quantity/days END)last_month_runrate,
|
|
CASE
|
|
WHEN --max(contribution)>0.02 or
|
|
txn_period ='L30D' and max(runrate)>= 30 THEN 'Fast Moving'
|
|
WHEN --max(contribution)>0.01 or
|
|
txn_period ='L30D' and max(runrate) between 5 and 29 THEN 'Slow Moving'
|
|
WHEN --max(contribution)<=0.01 or
|
|
txn_period ='L30D' and max(runrate)<5 THEN 'Not Moving'
|
|
END sku_type
|
|
FROM public.basic_calculation A
|
|
WHERE txn_period <> 'L120D'
|
|
GROUP BY A.SKu,txn_period;
|
|
*/
|
|
|
|
|
|
|
|
CREATE TABLE public.basic_calculation_stage3 AS
|
|
SELECT A.SKu,
|
|
percentile_cont(0.5) within
|
|
GROUP (
|
|
ORDER BY A.runrate) median_runrate,
|
|
max(A.runrate) max_runrate,
|
|
sum(CASE WHEN txn_period ='L30D' THEN quantity/days END)last_month_runrate,
|
|
CASE
|
|
WHEN --max(contribution)>0.02 or
|
|
max(B.runrate)> 30 THEN 'Fast Moving'
|
|
WHEN --max(contribution)>0.01 or
|
|
max(B.runrate)>= 5 THEN 'Slow Moving'
|
|
WHEN --max(contribution)<=0.01 or
|
|
max(B.runrate)<5 or max(B.runrate) is null THEN 'Not Moving'
|
|
END sku_type
|
|
FROM public.basic_calculation A
|
|
left join (select distinct sku , runrate from public.basic_calculation where txn_period = 'L30D' ) B on A.sku = B.sku
|
|
WHERE txn_period <> 'L120D'
|
|
GROUP BY A.SKu;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.basic_calculation_stage4;
|
|
|
|
|
|
CREATE TABLE public.basic_calculation_stage4 AS
|
|
SELECT A.* ,
|
|
CASE
|
|
WHEN sd_of_contribution= 0 THEN 0
|
|
ELSE (A.for_max_contribution)/sd_of_contribution
|
|
END AS max_deviation_contribution,
|
|
B.median_runrate,
|
|
B.max_runrate,
|
|
coalesce(B.last_month_runrate,0)last_month_runrate ,
|
|
B.sku_type,
|
|
(l30d_revenue/gold_price)*(1+(avg_revenue_growth/100)) AS qty1_median_rev_contribution,
|
|
median_runrate*30 qty2_avg_runrate,
|
|
avg_quantity qty3_avg_quantity,
|
|
expected_30D_revenue/gold_price qty4_expected_revenue_target,
|
|
0 buffer
|
|
FROM public.basic_calculation_stage2 A
|
|
LEFT JOIN public.basic_calculation_stage3 B ON A.sku = B.sku;
|
|
|
|
|
|
--SELECT * FROM public.basic_calculation_stage4;
|
|
|
|
--select * from public.basic_calculation_stage5
|
|
|
|
drop table if exists public.basic_calculation_stage5;
|
|
|
|
CREATE TABLE public.basic_calculation_stage5
|
|
as
|
|
select sku , MEDIAN(Max_of_qty) Max_of_qty , Avg(Max_of_qty) Avg_of_quantiy
|
|
from
|
|
(SELECT sku ,
|
|
qty1_median_rev_contribution Max_of_qty
|
|
FROM public.basic_calculation_stage4
|
|
UNION ALL
|
|
SELECT sku ,
|
|
qty2_avg_runrate
|
|
FROM public.basic_calculation_stage4
|
|
UNION ALL
|
|
SELECT sku ,
|
|
qty3_avg_quantity
|
|
FROM public.basic_calculation_stage4
|
|
UNION ALL
|
|
SELECT sku ,
|
|
qty4_expected_revenue_target
|
|
FROM public.basic_calculation_stage4) A
|
|
group by sku;
|
|
|
|
drop table if exists public.basic_calculation_stage6;
|
|
|
|
create table public.basic_calculation_stage6
|
|
as
|
|
select sku,
|
|
sum(CASE WHEN B.warehouse ='Balikpapan - Semarandi' THEN sku_qty_split_next_order_percentage/100 END) balikpapan_semarandi_split,
|
|
sum(CASE WHEN B.warehouse ='Bekasi' THEN sku_qty_split_next_order_percentage/100 END) Bekasi_split,
|
|
sum(CASE WHEN B.warehouse ='Makassar' THEN sku_qty_split_next_order_percentage/100 END) Makassar_split,
|
|
sum(CASE WHEN B.warehouse ='Pekanbaru - Medan' THEN sku_qty_split_next_order_percentage/100 END) pekanbaru_medan_split,
|
|
sum(CASE WHEN B.warehouse ='Semarang' THEN sku_qty_split_next_order_percentage/100 END) Semarang_split,
|
|
sum(CASE WHEN B.warehouse ='Surabaya' THEN sku_qty_split_next_order_percentage/100 END) Surabaya_split
|
|
from public.sku_warehouse_split_final B
|
|
group by sku;
|
|
|
|
drop table if exists public.final_basic_calucation_table;
|
|
|
|
CREATE TABLE public.final_basic_calucation_table AS
|
|
SELECT A.sku ,
|
|
A.gold_price ,
|
|
A.l30d_revenue ,
|
|
A.avg_revenue_growth,
|
|
A.avg_expected_revenue_growth ,
|
|
A.expected_30D_revenue,
|
|
A.median_of_contribution median_of_contribution_l90D,
|
|
A.sd_of_contribution standard_deviation_of_contribution_l90d,
|
|
A.max_deviation_contribution max_standard_deviation_of_contribution_l90d,
|
|
A.avg_quantity avg_quantity_sold_l90d,
|
|
A.max_quantity max_quantity_sold_l90d,
|
|
A.median_runrate median_runrate_l90d,
|
|
A.max_runrate max_runrate_l90d,
|
|
A.last_month_runrate l30d_runrate,
|
|
A.sku_type ,
|
|
A.new_sku as new_sku_flag,
|
|
A.qty1_median_rev_contribution ,
|
|
A.qty2_avg_runrate ,
|
|
A.qty3_avg_quantity ,
|
|
qty4_expected_revenue_target,
|
|
buffer ,
|
|
(case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30)
|
|
when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30)
|
|
when sku_type = 'Not Moving' then 0 end) overall_qty_required_per_day,
|
|
(case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30)
|
|
when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30)
|
|
when sku_type = 'Not Moving' then 0 end )*balikpapan_semarandi_split balikpapan_semarandi_split,
|
|
(case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30)
|
|
when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30)
|
|
when sku_type = 'Not Moving' then 0 end)*Bekasi_split Bekasi_split,
|
|
(case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30)
|
|
when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30)
|
|
when sku_type = 'Not Moving' then 0 end )*Makassar_split Makassar_split,
|
|
(case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30)
|
|
when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30)
|
|
when sku_type = 'Not Moving' then 0 end )*pekanbaru_medan_split pekanbaru_medan_split,
|
|
(case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30)
|
|
when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30)
|
|
when sku_type = 'Not Moving' then 0 end )*Semarang_split Semarang_split,
|
|
(case when sku_type= 'Fast Moving' then ((Max_of_qty*(1+buffer))/30)
|
|
when sku_type = 'Slow Moving' then ((Avg_of_quantiy*(1+buffer))/30)
|
|
when sku_type = 'Not Moving' then 0 end )*Surabaya_split Surabaya_split
|
|
FROM public.basic_calculation_stage4 A
|
|
LEFT JOIN public.basic_calculation_stage6 B ON A.sku =B.sku
|
|
LEFT join public.basic_calculation_stage5 C on A.sku=C.sku;
|
|
|
|
drop table if exists public.inbound_data;
|
|
|
|
create table public.inbound_data
|
|
as
|
|
select raena_code sku,
|
|
sum(case when warehouse_id = 'CGK WHS' then COALESCE(quantity,0) end) Cikarang_quantity ,
|
|
sum(case when warehouse_id = 'Makassar WHS' then COALESCE(quantity,0) end) Makassar_quantity ,
|
|
sum(case when warehouse_id = 'Medan WHS' then COALESCE(quantity,0) end) medan_quantity,
|
|
sum(case when warehouse_id = 'Samarinda WHS' then COALESCE(quantity,0) end) Samarinda_quantity ,
|
|
sum(case when warehouse_id = 'Semarang WHS' then COALESCE(quantity,0) end) semarang_quantity ,
|
|
sum(case when warehouse_id = 'Surabaya WHS' then COALESCE(quantity,0) end) Surabaya_quantity
|
|
from (select * from raena_erp_management.inbound_order where cast(expected_arrival_date as date) >'2023-11-22' and received_time is null ) A
|
|
left join raena_erp_management.inbound_order_sku B on A.id = B."orderIdId"
|
|
group by raena_code;
|
|
|
|
drop table if exists public.warehouse_in_stock;
|
|
|
|
create table public.warehouse_in_stock
|
|
as
|
|
select sku ,
|
|
sum(case when F.warehouse_code = 'WH-CGK45' then coalesce(F.stock_limit,0) end) Cikarang_in_stock,
|
|
sum(case when F.warehouse_code = 'WH-SUB51' then coalesce(F.stock_limit,0) end) Surabaya_in_stock,
|
|
sum(case when F.warehouse_code = 'WH-SRG18' then coalesce(F.stock_limit,0) end) Semarang_in_stock,
|
|
sum(case when F.warehouse_code = 'WH-MES07' then coalesce(F.stock_limit,0) end) Medan_in_stock,
|
|
sum(case when F.warehouse_code = 'WH-UPG04' then coalesce(F.stock_limit,0) end) Makassar_in_stock,
|
|
sum(case when F.warehouse_code = 'WH-AAP02' then coalesce(F.stock_limit,0) end) Samarinda_in_stock
|
|
from
|
|
raena_catalog_management.product C
|
|
left join raena_catalog_management.product_inventory F on C.id = F.product_id
|
|
group by sku;
|
|
|
|
DROP TABLE IF EXISTS public.demand_forecast_dashboard_base;
|
|
|
|
|
|
CREATE TABLE public.demand_forecast_dashboard_base AS
|
|
SELECT DISTINCT A.sku ,
|
|
A.brand_name ,
|
|
case when B.sku_type is not null then B.sku_type else 'Not Moving' end sku_type ,
|
|
case when new_sku_flag is not null then new_sku_flag else 'No' end new_sku_flag,
|
|
A.sourcing,
|
|
A.ordering_type ,
|
|
A.active_status,
|
|
multiple_flag,
|
|
moq,
|
|
moqremark,
|
|
give_away,
|
|
--cast(case when sourcing = 'Local' THEN 14*overall_qty_required_per_day
|
|
-- WHEN sourcing = 'Crossborder' THEN 30*overall_qty_required_per_day end as int) overall_qty_required_per_day,
|
|
cast(Cikarang_in_stock as int) Cikarang_in_stock,
|
|
cast(coalesce(E.Cikarang_quantity,0) as int) Cikarang_quantity_in_transit,
|
|
cast((Cikarang_in_stock+ coalesce(E.Cikarang_quantity,0)) as int) total_Cikarang_stock,
|
|
cast(l30d_runrate as int) Cikarang_runrate,
|
|
cast(CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Cikarang_in_stock+ coalesce(E.Cikarang_quantity,0))*(1.1)/l30d_runrate)
|
|
END as int) Cikarang_Inventory_day_for_future,
|
|
cast(bekasi_total_lead_time as int) cikarang_total_lead_time ,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Cikarang_in_stock+ coalesce(E.Cikarang_quantity,0))*(1.1)/l30d_runrate)
|
|
END<bekasi_total_lead_time THEN 'True'
|
|
ELSE 'False'
|
|
END cikarang_reorder_flag,
|
|
(cast(Bekasi_split*bekasi_inventory_days as int)-case when moq is not null and multiple_flag is not null then mod(cast(Bekasi_split*bekasi_inventory_days as int),cast(moq as int)) else 0 end ) cikarang_project_quantity_to_order,
|
|
cast(Surabaya_in_stock as int) Surabaya_in_stock,
|
|
cast(coalesce(E.Surabaya_quantity,0) as int) Surabaya_quantity_in_transit,
|
|
cast((Surabaya_in_stock+coalesce(E.Surabaya_quantity,0)) as int) total_Surabaya_stock,
|
|
cast(l30d_runrate as int) Surabaya_runrate,
|
|
cast(CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Surabaya_in_stock+coalesce(E.Surabaya_quantity,0))*(1.1)/l30d_runrate)
|
|
END as int) Surabaya_Inventory_day_for_future,
|
|
cast(Surabaya_total_lead_time as int) Surabaya_total_lead_time,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Surabaya_in_stock+coalesce(E.Surabaya_quantity,0))*(1.1)/l30d_runrate)
|
|
END<Surabaya_total_lead_time THEN 'True'
|
|
ELSE 'False'
|
|
END surabaya_reorder_flag,
|
|
(cast(Surabaya_split*Surabaya_inventory_days as int)-case when moq is not null and multiple_flag is not null then mod(cast(Surabaya_split*Surabaya_inventory_days as int),cast(moq as int)) else 0 end ) Surabaya_project_quantity_to_order,
|
|
cast(Semarang_in_stock as int) Semarang_in_stock,
|
|
cast(coalesce(E.semarang_quantity,0) as int) semarang_quantity_in_transit,
|
|
cast(Semarang_in_stock+coalesce(E.semarang_quantity,0) as int) total_semarang_stock,
|
|
cast(l30d_runrate as int) Semarang_runrate,
|
|
cast(CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Semarang_in_stock+coalesce(E.semarang_quantity,0))*(1.1)/l30d_runrate)
|
|
END as int) semarang_Inventory_day_for_future,
|
|
cast(Semarang_total_lead_time as int) Semarang_total_lead_time,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Semarang_in_stock+coalesce(E.semarang_quantity,0))*(1.1)/l30d_runrate)
|
|
END<Semarang_total_lead_time THEN 'True'
|
|
ELSE 'False'
|
|
END semarang_reorder_flag,
|
|
(cast(Semarang_split*Semarang_inventory_days as int)-case when moq is not null and multiple_flag is not null then mod(cast(Semarang_split*Semarang_inventory_days as int),cast(moq as int)) else 0 end ) Semarang_project_quantity_to_order,
|
|
cast(Medan_in_stock as int) Medan_in_stock,
|
|
cast(coalesce(E.medan_quantity,0) as int) medan_quantity_in_transit,
|
|
cast(Medan_in_stock+coalesce(E.medan_quantity,0) as int) total_medan_stock,
|
|
cast(l30d_runrate as int) Medan_runrate ,
|
|
cast(CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Medan_in_stock+coalesce(E.medan_quantity,0))*(1.1)/l30d_runrate)
|
|
END as int) medan_Inventory_day_for_future,
|
|
cast(Pekanbaru_Medan_total_lead_time as int) Medan_total_lead_time,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Medan_in_stock+coalesce(E.medan_quantity,0))*(1.1)/l30d_runrate)
|
|
END<Pekanbaru_Medan_total_lead_time THEN 'True'
|
|
ELSE 'False'
|
|
END medan_reorder_flag,
|
|
(cast(pekanbaru_medan_split*Pekanbaru_Medan_inventory_days as int)-case when moq is not null and multiple_flag is not null then mod(cast(pekanbaru_medan_split*Pekanbaru_Medan_inventory_days as int),cast(moq as int))else 0 end ) Medan_project_quantity_to_order,
|
|
cast(Makassar_in_stock as int) Makassar_in_stock,
|
|
cast(coalesce(E.Makassar_quantity,0) as int) Makassar_quantity_in_transit,
|
|
cast(Makassar_in_stock+coalesce(E.Makassar_quantity,0) as int) total_Makassar_stock,
|
|
cast(l30d_runrate as int) Makassar_runrate,
|
|
cast(CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Makassar_in_stock+coalesce(E.Makassar_quantity,0))*(1.1)/l30d_runrate)
|
|
END as int) Makassar_Inventory_day_for_future,
|
|
cast(Makassar_total_lead_time as int) Makassar_total_lead_time,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Makassar_in_stock+coalesce(E.Makassar_quantity,0))*(1.1)/l30d_runrate)
|
|
END<Makassar_total_lead_time THEN 'True'
|
|
ELSE 'False'
|
|
END makassar_reorder_flag,
|
|
(cast(Makassar_split*Makassar_inventory_days as int) -case when moq is not null and multiple_flag is not null then mod(cast(Makassar_split*Makassar_inventory_days as int),cast(moq as int))else 0 end ) Makassar_project_quantity_to_order,
|
|
cast(Samarinda_in_stock as int) Samarinda_in_stock,
|
|
cast(coalesce(E.Samarinda_quantity,0) as int) Samarinda_quantity_in_transit,
|
|
cast(Samarinda_in_stock+coalesce(E.Samarinda_quantity,0) as int) total_Samarinda_stock,
|
|
cast(l30d_runrate as int) Samarinda_runrate,
|
|
cast(CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Samarinda_in_stock+coalesce(E.Samarinda_quantity,0))*(1.1)/l30d_runrate)
|
|
END as int) Samarinda_Inventory_day_for_future,
|
|
cast(Balikpapan_Semarandi_total_lead_time as int) Semarandi_total_lead_time,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN l30d_runrate = 0 THEN 0 else((Samarinda_in_stock+coalesce(E.Samarinda_quantity,0))*(1.1)/l30d_runrate)
|
|
END<Balikpapan_Semarandi_total_lead_time THEN 'True'
|
|
ELSE 'False'
|
|
END semarandi_reorder_flag,
|
|
(cast(balikpapan_semarandi_split*Balikpapan_Semarandi_inventory_days as int)-case when moq is not null and multiple_flag is not null then mod(cast(balikpapan_semarandi_split*Balikpapan_Semarandi_inventory_days as int),cast(moq as int)) else 0 end ) semarandi_project_quantity_to_order
|
|
FROM public.sku_level_data A
|
|
LEFT JOIN public.final_basic_calucation_table B ON A.sku = B.sku
|
|
LEFT JOIN public.warehouse_in_stock C ON A.sku = C.sku
|
|
LEFT JOIN public.basic_calculation D ON A.sku=D.sku
|
|
LEFT JOIN public.inbound_data E ON A.sku = E.sku;
|
|
|
|
|
|
|
|
drop table if exists public.demand_forecast_dashboard_final;
|
|
|
|
create table public.demand_forecast_dashboard_final
|
|
as
|
|
SELECT *,
|
|
cast(cikarang_in_stock+surabaya_in_stock +semarang_in_stock +medan_in_stock +makassar_in_stock +samarinda_in_stock as int) AS total_in_stock ,
|
|
cast(cikarang_quantity_in_transit +surabaya_quantity_in_transit +semarang_quantity_in_transit +medan_quantity_in_transit +makassar_quantity_in_transit +samarinda_quantity_in_transit as int) AS total_quantity_in_transit,
|
|
cast(total_cikarang_stock +total_surabaya_stock +total_semarang_stock +total_medan_stock + total_makassar_stock +total_samarinda_stock as int) AS total_stock,
|
|
cast(cikarang_runrate as int) total_runrate,
|
|
cast(CASE
|
|
WHEN cikarang_runrate = 0 THEN 0 else((total_cikarang_stock +total_surabaya_stock +total_semarang_stock +total_medan_stock + total_makassar_stock +total_samarinda_stock)*(1.1)/cikarang_runrate)
|
|
END as int) total_inventory_day_for_future ,
|
|
cast(greatest(cikarang_total_lead_time,surabaya_total_lead_time,semarang_total_lead_time,medan_total_lead_time,makassar_total_lead_time,Semarandi_total_lead_time) as int) AS total_total_lead_time,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN cikarang_runrate = 0 THEN 0 else((total_cikarang_stock +total_surabaya_stock +total_semarang_stock +total_medan_stock + total_makassar_stock +total_samarinda_stock)*(1.1)/cikarang_runrate)
|
|
END<greatest(cikarang_total_lead_time,surabaya_total_lead_time,semarang_total_lead_time,medan_total_lead_time,makassar_total_lead_time,Semarandi_total_lead_time) THEN 'True'
|
|
ELSE 'False'
|
|
END overall_reorder_flag,
|
|
(coalesce(cikarang_project_quantity_to_order,0)+coalesce(semarandi_project_quantity_to_order,0)+coalesce(Makassar_project_quantity_to_order,0)
|
|
+coalesce(Semarang_project_quantity_to_order,0)+coalesce(Medan_project_quantity_to_order,0)+coalesce(Surabaya_project_quantity_to_order,0)) overall_qty_required_per_day
|
|
FROM public.demand_forecast_dashboard_base
|
|
WHERE sku NOT LIKE '%BAZ%';
|
|
|
|
Delete from public.OM_OOS_Base where report_date='2023-11-22';
|
|
|
|
|
|
Insert into public.OM_OOS_Base
|
|
SELECT *,
|
|
cast(cikarang_in_stock+surabaya_in_stock +semarang_in_stock +medan_in_stock +makassar_in_stock +samarinda_in_stock as int) AS total_in_stock ,
|
|
cast(cikarang_quantity_in_transit +surabaya_quantity_in_transit +semarang_quantity_in_transit +medan_quantity_in_transit +makassar_quantity_in_transit +samarinda_quantity_in_transit as int) AS total_quantity_in_transit,
|
|
cast(total_cikarang_stock +total_surabaya_stock +total_semarang_stock +total_medan_stock + total_makassar_stock +total_samarinda_stock as int) AS total_stock,
|
|
cast(cikarang_runrate as int) total_runrate,
|
|
cast(CASE
|
|
WHEN cikarang_runrate = 0 THEN 0 else((total_cikarang_stock +total_surabaya_stock +total_semarang_stock +total_medan_stock + total_makassar_stock +total_samarinda_stock)*(1.1)/cikarang_runrate)
|
|
END as int) total_inventory_day_for_future ,
|
|
cast(greatest(cikarang_total_lead_time,surabaya_total_lead_time,semarang_total_lead_time,medan_total_lead_time,makassar_total_lead_time,Semarandi_total_lead_time) as int) AS total_total_lead_time,
|
|
CASE
|
|
WHEN CASE
|
|
WHEN cikarang_runrate = 0 THEN 0 else((total_cikarang_stock +total_surabaya_stock +total_semarang_stock +total_medan_stock + total_makassar_stock +total_samarinda_stock)*(1.1)/cikarang_runrate)
|
|
END<greatest(cikarang_total_lead_time,surabaya_total_lead_time,semarang_total_lead_time,medan_total_lead_time,makassar_total_lead_time,Semarandi_total_lead_time) THEN 'True'
|
|
ELSE 'False'
|
|
END overall_reorder_flag,
|
|
(coalesce(cikarang_project_quantity_to_order,0)+coalesce(semarandi_project_quantity_to_order,0)+coalesce(Makassar_project_quantity_to_order,0)
|
|
+coalesce(Semarang_project_quantity_to_order,0)+coalesce(Medan_project_quantity_to_order,0)+coalesce(Surabaya_project_quantity_to_order,0)) overall_qty_required_per_day,
|
|
'2023-11-22' as Report_date
|
|
FROM public.demand_forecast_dashboard_base
|
|
WHERE sku NOT LIKE '%BAZ%';
|
|
|
|
|