get_data_harga_jual_vs_hppa_vs_harga_beli

/*
SASA-551
1. [OK] Kode produk
2. [OK] Nama produk
3. [OK] Sell price grosir
4. [OK] sell price HET
5. [OK] sell price end user
6. [OK] sell price ojol
7. [OK] sell price bengkel
8. [OK] sell price retail
9. [OK] sell price retail 2
10. [OK] sell price prosas
11. [OK] purch price DPP
12. [OK] avg price (hppa)
*/

CREATE TABLE tt_data_sell_price_vs_hppa_purch_price (
        product_id bigint,
        uom_id bigint,
        grosir numeric default 0,
        het numeric default 0,
        enduser numeric default 0,
        ojol numeric default 0,
        bengkel numeric default 0,
        retail numeric default 0,
        retail2 numeric default 0,
        prosas numeric default 0,
        purch_price numeric default 0,
        average_price numeric default 0,
        active character varying default ''
);

--DELETE FROM tt_data_sell_price_vs_hppa_purch_price;

--grosir
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,grosir,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'GROSIR'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 14;

--het
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,het,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'HET'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 10;

--enduser
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,enduser,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'ENDUSER'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 10;

--ojol
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,ojol,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'OJOL'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 10;

--retail
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,retail,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'RETAIL'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 10;

--retail2
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,retail2,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'RETAIL2'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 10;

--bengkel
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,bengkel,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'BENGKEL'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 10;

--prosas
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,prosas,active)
    select A.product_id,  A.base_uom_id, B.sell_price,B.active_datetime
    from m_product A
    inner join m_sell_price_product_for_so B ON A.product_id = B.product_id
    WHERE price_level = 'PROSAS'
    AND '20210630' BETWEEN B.date_from AND B.date_to
    AND B.ou_id = 10;

--average_price
WITH get_data_avg_sale AS (
    -- sales order
    SELECT B.product_id, C.base_uom_id, (B.nett_sell_price) AS sell_price
    --SELECT A.*
    FROM sl_so A
    INNER JOIN sl_so_item B ON A.so_id = B.so_id
    INNER JOIN m_product C ON B.product_id = C.product_id
    WHERE LEFT(A.doc_date, 6) = '202106'
    AND A.status_doc IN ('F', 'R')
    AND C.active = 'Y'
    UNION ALL
    --pos
    SELECT B.product_id, C.base_uom_id, B.nett_sell_price AS sell_price
    FROM i_trx_pos A
    INNER JOIN i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND A.tenant_id = B.tenant_id AND A.process_no = B.process_no
    INNER JOIN m_product C ON B.product_id = C.product_id
    WHERE LEFT(A.doc_date, 6) = '202106'
    AND A.status = 'S'
    AND C.active = 'Y'
)
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,average_price)
SELECT product_id, base_uom_id, AVG(sell_price)
FROM get_data_avg_sale
GROUP BY product_id, base_uom_id;

--purch price
insert into tt_data_sell_price_vs_hppa_purch_price(product_id,uom_id,purch_price,active)
SELECT A.product_id, B.base_uom_id, ROUND(A.gl_amount/qty, 2), B.active_datetime
    FROM in_summary_monthly_amount A
    INNER JOIN m_product B ON A.product_id = B.product_id
    WHERE A.date_year_month = '202106'
    AND A.doc_type_id = 111
    AND B.active = 'Y';


SELECT B.product_code AS kode_produk,
    B.product_name AS nama_produk ,f_get_uom_name(A.uom_id) satuan,
    SUM(grosir) grosir ,
    SUM(het) het ,
    SUM(enduser) enduser ,
    SUM(ojol) enduser ,
    SUM(bengkel) bengel ,
    SUM(retail) retail ,
    SUM(retail2) retail2 ,
    SUM(prosas) prosas ,
    SUM(purch_price) purch_price ,
    SUM(average_price) average_price
    FROM tt_data_sell_price_vs_hppa_purch_price A
    INNER JOIN m_product B ON A.product_id = B.product_id
    WHERE B.active = 'Y'
    GROUP BY B.product_code, B.product_name, A.uom_id
    ORDER BY nama_produk;