INSERT INTO tt_current_main_data(
            session_id, tenant_code, raw_data_type, main_data_type, current_id)

-- INIT CURRENT ID
SELECT '${SESSION_ID}' AS session_id, B.tenant_code, 'SALES' AS raw_data_type, 'SLS_INVOICE_DO' AS main_data_type, COALESCE(MAX(A.so_balance_invoice_id), -99) AS current_id
FROM sl_so_balance_invoice A
INNER JOIN t_tenant B ON A.tenant_id = B.tenant_id
WHERE A.ref_doc_type_id IN(311, 502 )
AND A.flg_invoice = 'N'
GROUP BY B.tenant_code;

INSERT INTO tt_current_main_data(
            session_id, tenant_code, raw_data_type, main_data_type, current_id)

SELECT '${SESSION_ID}' AS session_id, B.tenant_code, 'SALES' AS raw_data_type, 'SLS_INVOICE_AR' AS main_data_type, COALESCE(MAX(A.invoice_ar_id), -99) AS current_id
FROM fi_invoice_ar_balance A
INNER JOIN t_tenant B ON A.tenant_id = B.tenant_id
WHERE A.doc_type_id = 321
GROUP BY B.tenant_code;

-- insert into raw data sales
INSERT INTO dw_raw_sales(
            tenant_code, doc_type, doc_no, doc_date, doc_year_month,
            customer_code, pkp, class_customer_code, customer_group_code,
            city_code, level_price_code, province_code, region_code, ou_code,
            warehouse_code, brand_code, series_code, group_brand_product_code,
            category_code, sub_category_code, product_code, promo_code, salesman_code,
            supplier_code, qty, add_discount_amount, regular_discount_percentage,
            regular_discount_amount, discount_percentage, discount_amount,
            nett_sell_price, gross_item_amount, nett_item_amount, ref_doc_no,
            ref_doc_date, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
-- GET RAW DATA DO
SELECT B.tenant_code, f_get_doc_desc(A.ref_doc_type_id) AS doc_type, A.ref_doc_no AS doc_no, A.ref_doc_date AS doc_date, substr(A.ref_doc_date,1,6) doc_year_month,
C.partner_code AS customer_code, COALESCE(L.flg_pkp, 'N') AS pkp, C.industry_type AS class_customer_code,
f_get_payment_group_customer_code(C.partner_id) AS customer_group_code, D.city AS city_code, C.price_level AS level_price_code,
D.state_or_province AS province_code, H.region_code, f_get_ou_code(A.ou_id) AS ou_code, f_get_warehouse_code(G.warehouse_id) AS warehouse_code,
f_get_brand_code(J.brand_id) AS brand_code, f_get_code_golongan_product(J.product_id) AS series_code,
F.group_brand_product AS group_brand_product_code, f_get_ctgr_product_code(J.ctgr_product_id) AS category_code,
f_get_sub_ctgr_product_code(J.sub_ctgr_product_id) AS sub_category_code, J.product_code,
COALESCE(M.promo_code, '') AS promo_code , f_get_partner_code(E.salesman_id) AS salesman_code,
f_get_partner_code(K.supplier_id) AS supplier_code,
A.qty_dlv_so AS qty, 0 AS add_discount_amount,
CASE WHEN (COALESCE(M.promo_type,  '') = '' OR COALESCE(M.promo_type, '') = 'PROMOEVENT')
    THEN E.regular_discount_percentage
    ELSE 0
    END AS regular_discount_percentage,
CASE WHEN (COALESCE(M.promo_type,  '') = '' OR COALESCE(M.promo_type, '') = 'PROMOEVENT')
    THEN A.regular_disc_amount
    ELSE 0
    END AS regular_discount_amount,

CASE WHEN (COALESCE(M.promo_type,  '') = 'PROMOEVENT')
    THEN IX.discount_percentage
    ELSE 0
    END AS discount_percentage,
CASE WHEN (COALESCE(M.promo_type,  '') = 'PROMOEVENT')
    THEN A.promo_disc_amount
    ELSE 0
    END AS discount_amount,
A.price_so AS nett_sell_price ,
A.item_amount + COALESCE(N.tax_amount, 0) AS gross_item_amount,
IX.nett_item_amount, E.ext_doc_no AS ref_doc_no, E.ext_doc_date AS ref_doc_date, 0 AS version,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS create_datetime,
-1 AS create_user_id,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS update_datetime,
-1 AS update_user_id
FROM sl_so_balance_invoice A
INNER JOIN t_tenant B ON A.tenant_id = B.tenant_id
INNER JOIN m_partner C ON A.partner_id = C.partner_id
INNER JOIN m_partner_address D ON C.partner_id = D.partner_id AND flg_official = 'Y'
INNER JOIN sl_so E ON A.so_id = E.so_id
INNER JOIN sl_so_additional_for_dlg F ON E.so_id = F.so_id
INNER JOIN sl_do G ON A.ref_id = G.do_id
INNER JOIN m_region H ON F.region_id = H.region_id
INNER JOIN sl_do_item I ON A.ref_id = I.do_id AND A.ref_item_id = I.do_item_id
INNER JOIN sl_so_item IX ON I.ref_id = IX.so_item_id
INNER JOIN m_product J ON I.product_id = J.product_id
INNER JOIN m_product_custom_for_dlg K ON J.product_id = K.product_id
LEFT JOIN m_partner_npwp L ON C.partner_id = L.partner_id
LEFT JOIN m_promo_sales M ON F.promo_sales_id = M.promo_sales_id
LEFT JOIN sl_so_balance_invoice_tax N ON E.so_id = N.so_id AND G.do_id = N.ref_id AND I.do_item_id = N.ref_item_id
WHERE A.ref_doc_type_id = 311 AND A.do_receipt_item_id = -99 AND A.flg_invoice = 'N'
AND EXISTS (
    SELECT 1 FROM dw_last_main_data Z, tt_current_main_data X
    WHERE Z.tenant_code = B.tenant_code
    AND Z.tenant_code = X.tenant_code
    AND Z.main_data_type = 'SLS_INVOICE_DO'
    AND Z.main_data_type = X.main_data_type
    AND X.session_id = '${SESSION_ID}'
    AND A.so_balance_invoice_id BETWEEN Z.last_id+1 AND X.current_id
);

INSERT INTO dw_raw_sales(
            tenant_code, doc_type, doc_no, doc_date, doc_year_month,
            customer_code, pkp, class_customer_code, customer_group_code,
            city_code, level_price_code, province_code, region_code, ou_code,
            warehouse_code, brand_code, series_code, group_brand_product_code,
            category_code, sub_category_code, product_code, promo_code, salesman_code,
            supplier_code, qty, add_discount_amount, regular_discount_percentage,
            regular_discount_amount, discount_percentage, discount_amount,
            nett_sell_price, gross_item_amount, nett_item_amount, ref_doc_no,
            ref_doc_date, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
-- GET DATA RAW DOR
SELECT B.tenant_code, f_get_doc_desc(GX.doc_type_id) AS doc_type, GX.doc_no, GX.doc_date, substr(GX.doc_date,1,6) doc_year_month,
C.partner_code AS customer_code, COALESCE(L.flg_pkp, 'N') AS pkp, C.industry_type AS class_customer_code,
f_get_payment_group_customer_code(C.partner_id) AS customer_group_code, D.city AS city_code, C.price_level AS level_price_code,
D.state_or_province AS province_code, H.region_code, f_get_ou_code(A.ou_id) AS ou_code, f_get_warehouse_code(G.warehouse_id) AS warehouse_code,
f_get_brand_code(J.brand_id) AS brand_code, f_get_code_golongan_product(J.product_id) AS series_code,
F.group_brand_product AS group_brand_product_code, f_get_ctgr_product_code(J.ctgr_product_id) AS category_code,
f_get_sub_ctgr_product_code(J.sub_ctgr_product_id) AS sub_category_code, J.product_code,
COALESCE(M.promo_code, '') AS promo_code , f_get_partner_code(E.salesman_id) AS salesman_code,
f_get_partner_code(K.supplier_id) AS supplier_code,
A.qty_dlv_so AS qty, 0 AS add_discount_amount,
CASE WHEN (COALESCE(M.promo_type,  '') = '' OR COALESCE(M.promo_type, '') = 'PROMOEVENT')
    THEN E.regular_discount_percentage
    ELSE 0
    END AS regular_discount_percentage,
CASE WHEN (COALESCE(M.promo_type,  '') = '' OR COALESCE(M.promo_type, '') = 'PROMOEVENT')
    THEN A.regular_disc_amount
    ELSE 0
    END AS regular_discount_amount,

CASE WHEN (COALESCE(M.promo_type,  '') = 'PROMOEVENT')
    THEN IX.discount_percentage
    ELSE 0
    END AS discount_percentage,
CASE WHEN (COALESCE(M.promo_type,  '') = 'PROMOEVENT')
    THEN A.promo_disc_amount
    ELSE 0
    END AS discount_amount,
A.price_so AS nett_sell_price ,
A.item_amount + COALESCE(N.tax_amount, 0) AS gross_item_amount,
IX.nett_item_amount, E.ext_doc_no AS ref_doc_no, E.ext_doc_date AS ref_doc_date, 0 AS version,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS create_datetime,
-1 AS create_user_id,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS update_datetime,
-1 AS update_user_id
FROM sl_so_balance_invoice A
INNER JOIN t_tenant B ON A.tenant_id = B.tenant_id
INNER JOIN m_partner C ON A.partner_id = C.partner_id
INNER JOIN m_partner_address D ON C.partner_id = D.partner_id AND flg_official = 'Y'
INNER JOIN sl_so E ON A.so_id = E.so_id
INNER JOIN sl_so_additional_for_dlg F ON E.so_id = F.so_id
INNER JOIN sl_do G ON A.ref_id = G.do_id
INNER JOIN in_do_receipt GX ON G.do_id = GX.ref_id AND G.doc_type_id = GX.ref_doc_type_id
INNER JOIN m_region H ON F.region_id = H.region_id
INNER JOIN sl_do_item I ON A.ref_id = I.do_id AND A.ref_item_id = I.do_item_id
INNER JOIN sl_so_item IX ON I.ref_id = IX.so_item_id
INNER JOIN m_product J ON I.product_id = J.product_id
INNER JOIN m_product_custom_for_dlg K ON J.product_id = K.product_id
LEFT JOIN m_partner_npwp L ON C.partner_id = L.partner_id
LEFT JOIN m_promo_sales M ON F.promo_sales_id = M.promo_sales_id
LEFT JOIN sl_so_balance_invoice_tax N ON E.so_id = N.so_id AND G.do_id = N.ref_id AND I.do_item_id = N.ref_item_id
WHERE A.ref_doc_type_id = 311 AND A.do_receipt_item_id != -99 AND A.flg_invoice = 'N'
AND EXISTS (
    SELECT 1 FROM dw_last_main_data Z, tt_current_main_data X
    WHERE Z.tenant_code = B.tenant_code
    AND Z.tenant_code = X.tenant_code
    AND Z.main_data_type = 'SLS_INVOICE_DO'
    AND Z.main_data_type = X.main_data_type
    AND X.session_id = '${SESSION_ID}'
    AND A.so_balance_invoice_id BETWEEN Z.last_id+1 AND X.current_id
);

INSERT INTO dw_raw_sales(
            tenant_code, doc_type, doc_no, doc_date, doc_year_month,
            customer_code, pkp, class_customer_code, customer_group_code,
            city_code, level_price_code, province_code, region_code, ou_code,
            warehouse_code, brand_code, series_code, group_brand_product_code,
            category_code, sub_category_code, product_code, promo_code, salesman_code,
            supplier_code, qty, add_discount_amount, regular_discount_percentage,
            regular_discount_amount, discount_percentage, discount_amount,
            nett_sell_price, gross_item_amount, nett_item_amount, ref_doc_no,
            ref_doc_date, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
-- GET RAW DATA SALES INVOICE
SELECT B.tenant_code, f_get_doc_desc(D.doc_type_id) AS doc_type, D.doc_no, D.doc_date, substr(D.doc_date,1,6) doc_year_month,
E.partner_code AS customer_code, COALESCE(L.flg_pkp, 'N') AS pkp, E.industry_type AS class_customer_code,
f_get_payment_group_customer_code(E.partner_id) AS customer_group_code, F.city AS city_code, E.price_level AS level_price_code,
F.state_or_province AS province_code, I.region_code, f_get_ou_code(A.ou_id) AS ou_code, f_get_warehouse_code(H.warehouse_id) AS warehouse_code,
f_get_brand_code(HY.brand_id) AS brand_code, f_get_code_golongan_product(HY.product_id) AS series_code,
G.group_brand_product AS group_brand_product_code, f_get_ctgr_product_code(HY.ctgr_product_id) AS category_code,
f_get_sub_ctgr_product_code(HY.sub_ctgr_product_id) AS sub_category_code, HY.product_code,
COALESCE(M.promo_code, '') AS promo_code , f_get_partner_code(D.salesman_id) AS salesman_code,
f_get_partner_code(HZ.supplier_id) AS supplier_code,
0 AS qty, A.promo_disc_amount+A.adj_promo_disc_amount AS add_discount_amount,
HYX.discount_percentage AS regular_discount_percentage, 0 regular_discount_amount, 0 discount_percentage,  0 discount_amount,
A.price_so AS nett_sell_price, 0 AS gross_item_amount, 0 AS nett_item_amount,
H.ext_doc_no AS ref_doc_no, H.ext_doc_date AS ref_doc_date, 0 AS version,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS create_datetime,
-1 AS create_user_id,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS update_datetime,
-1 AS update_user_id
FROM sl_so_balance_invoice A
INNER JOIN t_tenant B ON A.tenant_id = B.tenant_id
INNER JOIN fi_invoice_ar_balance C ON A.invoice_id = C.invoice_ar_id AND C.doc_type_id = 321
INNER JOIN sl_invoice D ON A.invoice_id = D.invoice_id
INNER JOIN m_partner E ON D.partner_id = E.partner_id
INNER JOIN m_partner_address F ON E.partner_id = F.partner_id AND F.flg_official = 'Y'
INNER JOIN sl_so_additional_for_dlg G ON D.ref_id = G.so_id AND D.ref_doc_type_id = 301
INNER JOIN sl_do H ON D.ref_id = H.ref_id AND D.ref_doc_type_id = H.ref_doc_type_id
INNER JOIN sl_do_item HX ON A.ref_id = HX.do_id AND A.ref_item_id = HX.do_item_id
INNER JOIN sl_so_item HYX ON HX.ref_id = HYX.so_item_id
INNER JOIN m_product HY ON HX.product_id = HY.product_id
INNER JOIN m_product_custom_for_dlg HZ ON HY.product_id = HZ.product_id
INNER JOIN m_region I ON G.region_id = I.region_id
LEFT JOIN m_partner_npwp L ON C.partner_id = L.partner_id
LEFT JOIN m_promo_sales M ON G.promo_sales_id = M.promo_sales_id
WHERE A.flg_invoice = 'Y' AND
A.ref_doc_type_id = 311
AND EXISTS (
    SELECT 1 FROM dw_last_main_data Z, tt_current_main_data X
    WHERE Z.tenant_code = B.tenant_code
    AND Z.tenant_code = X.tenant_code
    AND Z.main_data_type = 'SLS_INVOICE_AR'
    AND Z.main_data_type = X.main_data_type
    AND X.session_id = '${SESSION_ID}'
    AND C.invoice_ar_id BETWEEN Z.last_id+1 AND X.current_id
);

INSERT INTO dw_raw_sales(
            tenant_code, doc_type, doc_no, doc_date, doc_year_month,
            customer_code, pkp, class_customer_code, customer_group_code,
            city_code, level_price_code, province_code, region_code, ou_code,
            warehouse_code, brand_code, series_code, group_brand_product_code,
            category_code, sub_category_code, product_code, promo_code, salesman_code,
            supplier_code, qty, add_discount_amount, regular_discount_percentage,
            regular_discount_amount, discount_percentage, discount_amount,
            nett_sell_price, gross_item_amount, nett_item_amount, ref_doc_no,
            ref_doc_date, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
-- GET RAW DATA RN
SELECT B.tenant_code, f_get_doc_desc(A.ref_doc_type_id) AS doc_type, A.ref_doc_no AS doc_no, A.ref_doc_date AS doc_date, substr(A.ref_doc_date,1,6) doc_year_month,
C.partner_code AS customer_code, COALESCE(L.flg_pkp, 'N') AS pkp, C.industry_type AS class_customer_code,
f_get_payment_group_customer_code(C.partner_id) AS customer_group_code, D.city AS city_code, C.price_level AS level_price_code,
D.state_or_province AS province_code, COALESCE(J.region_code, '') AS region_code, f_get_ou_code(A.ou_id) AS ou_code, f_get_warehouse_code(AX.warehouse_to_id) AS warehouse_code,
f_get_brand_code(EX.brand_id) AS brand_code, f_get_code_golongan_product(EX.product_id) AS series_code,
COALESCE(I.group_brand_product, '') AS group_brand_product_code, f_get_ctgr_product_code(EX.ctgr_product_id) AS category_code,
f_get_sub_ctgr_product_code(EX.sub_ctgr_product_id) AS sub_category_code, EX.product_code,
COALESCE(K.promo_code, '') AS promo_code , f_get_partner_code(COALESCE(H.salesman_id,-99)) AS salesman_code,
f_get_partner_code(EY.supplier_id) AS supplier_code,
A.qty_dlv_so AS qty, 0 AS add_discount_amount,
0 regular_discount_percentage, 0 regular_discount_amount, 0 discount_percentage,  0 discount_amount,
A.price_so AS nett_sell_price,
A.item_amount + COALESCE(M.tax_amount, 0) AS gross_item_amount,
E.nett_item_amount, COALESCE(H.ext_doc_no, '') AS ref_doc_no, COALESCE(H.ext_doc_date, '') AS ref_doc_date, 0 AS version,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS create_datetime,
-1 AS create_user_id,
TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS update_datetime,
-1 AS update_user_id
FROM sl_so_balance_invoice A
INNER JOIN in_inventory AX ON A.ref_id = AX.inventory_id
INNER JOIN t_tenant B ON A.tenant_id = B.tenant_id
INNER JOIN m_partner C ON A.partner_id = C.partner_id
INNER JOIN m_partner_address D ON C.partner_id = D.partner_id AND flg_official = 'Y'
INNER JOIN sl_request_return_sales_item E ON A.ref_item_id = E.request_return_sales_item_id AND A.so_id = E.request_return_sales_id
INNER JOIN m_product EX ON E.product_id = EX.product_id
INNER JOIN m_product_custom_for_dlg EY ON EX.product_id = EY.product_id
LEFT JOIN sl_do_item F ON E.ref_id = F.do_item_id
LEFT JOIN sl_do G ON F.do_id = G.do_id
LEFT JOIN sl_so H ON G.ref_id = H.so_id
LEFT JOIN sl_so_item HX ON F.ref_id = HX.so_item_id
LEFT JOIN sl_so_additional_for_dlg I ON H.so_id = I.so_id
LEFT JOIN m_region J ON I.region_id = J.region_id
LEFT JOIN m_promo_sales K ON I.promo_sales_id = K.promo_sales_id
LEFT JOIN m_partner_npwp L ON C.partner_id = L.partner_id
LEFT JOIN sl_so_balance_invoice_tax M ON H.so_id = M.so_id AND G.do_id = M.ref_id AND F.do_item_id = M.ref_item_id
WHERE A.ref_doc_type_id = 502
AND EXISTS (
    SELECT 1 FROM dw_last_main_data Z, tt_current_main_data X
    WHERE Z.tenant_code = B.tenant_code
    AND Z.tenant_code = X.tenant_code
    AND Z.main_data_type = 'SLS_INVOICE_DO'
    AND Z.main_data_type = X.main_data_type
    AND X.session_id = '${SESSION_ID}'
    AND A.so_balance_invoice_id BETWEEN Z.last_id+1 AND X.current_id
);

-- UPDATE LAST ID
UPDATE dw_last_main_data A
   SET last_id=B.current_id,
       update_datetime=TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING
FROM tt_current_main_data B
WHERE A.tenant_code = B.tenant_code
AND A.raw_data_type = B.raw_data_type
AND A.main_data_type = B.main_data_type
AND B.session_id = '${SESSION_ID}';

-- REMOVE DW CURRENT ID
DELETE FROM tt_current_main_data where session_id = '${SESSION_ID}';