Untitled

----------------------------------------------------------------------------------------------------------------------------
-- SCRIPT untuk INSERT data Penjualan yang lama ke dwh_product
----------------------------------------------------------------------------------------------------------------------------
INSERT INTO dwh_sales (
    doc_type, doc_no, doc_date, remark, so_no, so_date,
    customer_code, product_catalog_code, catalog_code, product_code,
    qty, tax_percentage, harga_katalok, fee_ds_percentage, fee_ds_amount,
    diskon_member_percentage, discount_promo_percentage, subtotal_harga_katalok,
    subtotal_harga_member, tax_amount, create_datetime, update_datetime,
    diskon_member_amount, discount_promo_amount, subtotal_harga_nett, dpp_amount,
    subtotal_product_value, cogs_amount, gross_margin, style, subtotal_volume_weight,
    subtotal_weight, used_volume_weight, price_range, member_age_range,
    string_year, string_year_month
)
SELECT 'Delivery Order MLM' AS doc_type, A.id_penjualan AS doc_no, A.create_date AS doc_date, 'MIGRASI DATA PENJUALAN DARI SYSTEM LAMA' AS remark, A.id_penjualan AS so_no, A.create_date AS so_date,
    lpad(A.id_member, 10, '0') AS customer_code, UPPER(TRIM(A.id_produk)) AS product_catalog_code, UPPER(TRIM(A.id_katalok)) AS catalog_code, UPPER(TRIM(A.id_produk)) AS product_code,
    A.jumlah AS qty, 0 AS tax_percentage, A.harga_katalok_penjualan AS harga_katalok, 0 AS fee_ds_percentage, 0 AS fee_ds_amount,
    A.persen_diskon_member_penjualan AS diskon_member_percentage, A.persen_diskon_promo_penjualan AS discount_promo_percentage, A.total_harga_katalok AS subtotal_harga_katalok,
    A.total_harga_netto AS subtotal_harga_member, 0 AS tax_amount, to_char(create_date, 'YYYYMMDD')||'000001'  AS create_datetime, '' AS update_datetime,
    A.total_harga_katalok - A.total_harga_netto AS diskon_member_amount, A.diskon_promo_penjualan AS discount_promo_amount, 0 AS subtotal_harga_nett, 0 AS dpp_amount,
    0 AS subtotal_product_value, 0 AS cogs_amount, 0 AS gross_margin, COALESCE(B.style, 'N/A') AS style, A.total_berat_produk AS subtotal_volume_weight,
    A.total_berat_produk AS subtotal_weight, A.total_berat_produk AS used_volume_weight,
    CASE
    WHEN A.jumlah IS NULL OR A.jumlah = 0 THEN '0 - 50k'
        WHEN (A.total_harga_netto / A.jumlah) > 10000000 THEN '> 10.000k'
        WHEN (A.total_harga_netto / A.jumlah) > 5000000 THEN '5.000k - 10.000k'
        WHEN (A.total_harga_netto / A.jumlah) > 4000000 THEN '4.000k - 5.000k'
        WHEN (A.total_harga_netto / A.jumlah) > 3000000 THEN '3.000k - 4.000k'
        WHEN (A.total_harga_netto / A.jumlah) > 2000000 THEN '2.000k - 3.000k'
        WHEN (A.total_harga_netto / A.jumlah) > 1000000 THEN '1.000k - 2.000k'
        WHEN (A.total_harga_netto / A.jumlah) > 500000 THEN '500k - 1.000k'
        WHEN (A.total_harga_netto / A.jumlah) > 400000 THEN '400k - 500k'
        WHEN (A.total_harga_netto / A.jumlah) > 300000 THEN '300k - 400k'
        WHEN (A.total_harga_netto / A.jumlah) > 200000 THEN '200k - 300k'
        WHEN (A.total_harga_netto / A.jumlah) > 100000 THEN '100k - 200k'
        WHEN (A.total_harga_netto / A.jumlah) > 50000 THEN '50k - 100k'
        ELSE '0 - 50k'
    END AS price_range,
    CASE
        WHEN C.dob_member IS NULL OR TRIM(C.dob_member) = '' THEN 'N/A'
        WHEN TO_CHAR(A.create_date - interval '64 years', 'YYYYMMDD') > C.dob_member THEN '> 64 tahun'
        WHEN TO_CHAR(A.create_date - interval '54 years', 'YYYYMMDD') > C.dob_member THEN '54-64 tahun'
        WHEN TO_CHAR(A.create_date - interval '44 years', 'YYYYMMDD') > C.dob_member THEN '44-54 tahun'
        WHEN TO_CHAR(A.create_date - interval '34 years', 'YYYYMMDD') > C.dob_member THEN '34-44 tahun'
        WHEN TO_CHAR(A.create_date - interval '24 years', 'YYYYMMDD') > C.dob_member THEN '24-34 tahun'
        WHEN TO_CHAR(A.create_date - interval '18 years', 'YYYYMMDD') > C.dob_member THEN '18-24 tahun'
        WHEN TO_CHAR(A.create_date - interval '9 years', 'YYYYMMDD') > C.dob_member THEN '9-18 tahun'
        WHEN TO_CHAR(A.create_date - interval '5 years', 'YYYYMMDD') > C.dob_member THEN '5-9 tahun'
        ELSE '0-5 tahun'
    END AS member_age_range,
    to_char(create_date, 'YYYY') AS string_year, to_char(create_date, 'YYYYMM') AS string_year_month
FROM copy_factpenjualanoldsys A
LEFT JOIN dwh_product B ON UPPER(TRIM(A.id_produk)) = UPPER(TRIM(B.product_code))
LEFT JOIN dwh_member C ON lpad(A.id_member, 10, '0') = C.member_code
WHERE NOT EXISTS (
    SELECT 1 FROM dwh_sales B
    WHERE A.id_penjualan = B.doc_no
)


----------------------------------------------------------------------------------------------------------------------------
-- INSERT data dwh_product dari data product system Lama
----------------------------------------------------------------------------------------------------------------------------
INSERT INTO dwh_product(
    product_code, product_name, style, style_image, size, color,
    flag_consignment, product_custom_field_id_gender, gender,
    product_custom_field_id_type, jenis, category_code, category_name,
    sub_category_code, sub_category_name, brand_code, brand_name,
    supplier_product_code, supplier_code, supplier_name, weight,
    length, width, height, product_vat)
WITH
product_brand AS (
    SELECT brand_code, brand_name
    FROM dwh_product
    GROUP BY brand_code, brand_name
),
product_category AS (
    SELECT category_code, category_name
    FROM dwh_product
    GROUP BY category_code, category_name
)
SELECT UPPER(TRIM(A.id_produk)) AS product_code, A.nama_produk, UPPER(TRIM(A.style_produk)) AS style, '' AS style_image, UPPER(TRIM(A.id_ukuran)) AS size, UPPER(TRIM(A.id_warna)) AS color,
    (CASE
        WHEN konsignasi_produk = 'T' THEN 'Y'
        WHEN konsignasi_produk = 'F' THEN 'N'
     END) AS flag_consignment, UPPER(TRIM(A.id_gender)) AS product_custom_field_id_gender, UPPER(TRIM(A.id_gender)) AS gender,
     UPPER(TRIM(A.id_jenis_produk)) AS product_custom_field_id_type, UPPER(TRIM(A.id_jenis_produk)) AS jenis, UPPER(TRIM(A.id_kategori)) AS category_code, COALESCE(E.category_name, F.ctgr_product_name, 'N/A') AS category_name,
     UPPER(TRIM(A.id_kategori)) AS sub_category_code, COALESCE(E.category_name, F.ctgr_product_name, 'N/A') AS sub_category_name, COALESCE(UPPER(TRIM(A.id_merek)), 'N/A') AS brand_code, COALESCE(B.brand_name, C.brand_name, 'N/A') AS brand_name,
     UPPER(TRIM(A.id_produk)) AS supplier_product_code, UPPER(TRIM(A.id_supplier)) AS supplier_code, COALESCE(D.supplier_name, 'N/A') AS supplier_name, berat AS weight,
     0 AS length, 0 AS width, 0 AS height, 0 AS product_vat
FROM copy_msproductoldsys A
LEFT JOIN product_brand B ON UPPER(TRIM(A.id_merek)) = UPPER(TRIM(B.brand_code))
LEFT JOIN tt_m_brand C ON UPPER(TRIM(A.id_merek)) = UPPER(TRIM(C.brand_code))
LEFT JOIN dwh_supplier D ON UPPER(TRIM(A.id_supplier)) = UPPER(TRIM(D.supplier_code))
LEFT JOIN product_category E ON UPPER(TRIM(A.id_kategori)) = UPPER(TRIM(E.category_code))
LEFT JOIN tt_m_ctgr_product F ON UPPER(TRIM(A.id_kategori)) = UPPER(TRIM(F.ctgr_product_code))
WHERE NOT EXISTS (SELECT 1 FROM dwh_product B WHERE UPPER(TRIM(A.id_produk)) = UPPER(TRIM(B.product_code)))
AND A.id_produk IS NOT NULL;

----------------------------------------------------------------------------------------------------------------------------
-- INSERT data product catalog dari product system Lama
----------------------------------------------------------------------------------------------------------------------------
INSERT INTO public.dwh_product_catalog (
    product_catalog_code, product_catalog_name, product_catalog_desc, flag_discontinue,
    catalog_price, disc_member_percent, product_value, catalog_code, catalog_name,
    start_date, end_date, page_no, page_used, product_code, product_name, style,
    style_image, size, color, flag_consignment, product_custom_field_id_gender,
    gender, product_custom_field_id_type, jenis, category_code, category_name,
    sub_category_code, sub_category_name, brand_code, brand_name,
    supplier_product_code, supplier_code, supplier_name, weight,
    length, width, height, flag_tax)
WITH
product_brand AS (
    SELECT brand_code, brand_name
    FROM dwh_product
    GROUP BY brand_code, brand_name
),
product_category AS (
    SELECT category_code, category_name
    FROM dwh_product
    GROUP BY category_code, category_name
)
SELECT UPPER(TRIM(A.id_produk)) AS product_catalog_code, A.nama_produk AS product_catalog_name, A.nama_produk AS product_catalog_desc,
    (CASE
        WHEN A.continue_produk = 'T' THEN 'N'
        WHEN A.continue_produk = 'F' THEN 'Y'
    END) AS flag_discontinue,
    A.harga_katalok_zona_1 AS catalog_price, A.diskon_member AS disc_member_percent, '' AS product_value, UPPER(TRIM(A.id_katalok )) AS catalog_code, COALESCE(G.catalog_name, 'N/A') AS catalog_name,
    COALESCE(to_char(G.start_date, 'YYYYMMDD'), 'N/A') AS start_date, COALESCE(to_char(G.end_date, 'YYYYMMDD'), 'N/A') AS end_date, A.halaman_produk AS page_no, A.pakai_halaman AS page_used, UPPER(TRIM(A.id_produk)) AS product_code, A.nama_produk AS product_name, A.style_produk AS style,
    '' AS style_image, UPPER(TRIM(A.id_ukuran)) AS size, UPPER(TRIM(A.id_warna)) AS color,
    (CASE
        WHEN konsignasi_produk = 'T' THEN 'Y'
        WHEN konsignasi_produk = 'F' THEN 'N'
     END) AS flag_consignment, UPPER(TRIM(A.id_gender)) AS product_custom_field_id_gender,
    UPPER(TRIM(A.id_gender)) AS gender, UPPER(TRIM(A.id_jenis_produk)) AS product_custom_field_id_type, UPPER(TRIM(A.id_jenis_produk)) AS jenis, UPPER(TRIM(A.id_kategori)) AS category_code, COALESCE(E.category_name, F.ctgr_product_name, 'N/A') AS category_name,
    UPPER(TRIM(A.id_kategori)) AS sub_category_code, COALESCE(E.category_name, F.ctgr_product_name, 'N/A') AS sub_category_name, UPPER(TRIM(A.id_merek)) AS brand_code, COALESCE(B.brand_name, C.brand_name, 'N/A') AS brand_name,
    UPPER(TRIM(A.id_produk)) AS supplier_product_code, UPPER(TRIM(A.id_supplier)) AS supplier_code, COALESCE(D.supplier_name, 'N/A') AS supplier_name, 0 AS weight,
    0 AS length, 0 AS width, 0 AS height,
    (CASE
        WHEN A.kena_pajak = 'T' THEN 'Y'
        WHEN A.kena_pajak = 'F' THEN 'N'
    END) AS flag_tax
FROM copy_msproductoldsys A
LEFT JOIN product_brand B ON UPPER(TRIM(A.id_merek)) = UPPER(TRIM(B.brand_code))
LEFT JOIN tt_m_brand C ON UPPER(TRIM(A.id_merek)) = UPPER(TRIM(C.brand_code))
LEFT JOIN dwh_supplier D ON UPPER(TRIM(A.id_supplier)) = UPPER(TRIM(D.supplier_code))
LEFT JOIN product_category E ON UPPER(TRIM(A.id_kategori)) = UPPER(TRIM(E.category_code))
LEFT JOIN tt_m_ctgr_product F ON UPPER(TRIM(A.id_kategori)) = UPPER(TRIM(F.ctgr_product_code))
LEFT JOIN dwh_catalog G ON UPPER(TRIM(A.id_katalok)) = UPPER(TRIM(G.catalog_code))
WHERE NOT EXISTS (
    SELECT 1 FROM dwh_product_catalog B
    WHERE UPPER(TRIM(A.id_produk)) = UPPER(TRIM(B.product_catalog_code))
    AND UPPER(TRIM(A.id_katalok)) = UPPER(TRIM(B.catalog_code))
)
AND A.id_produk IS NOT NULL;

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------