Step-step query OA aktif

--1. Init temp table
CREATE TABLE tt_data_balance_invoice_20210216
(
    so_balance_invoice_id bigint,
    ou_id bigint,
    partner_id bigint,
    so_id bigint,
    ref_doc_type_id bigint,
    ref_id bigint,
    ref_doc_no character varying(30),
    ref_doc_date character varying(8),
    ref_item_id bigint,
    invoice_id bigint,
    invoice_doc_no character varying(30),
    invoice_doc_date character varying(8),
    product_id bigint,
    group_brand_id bigint
)
WITH (
  OIDS=FALSE
);

--2. Insert data ke temp table
WITH data_balance_invoice AS (
    SELECT A.so_balance_invoice_id,
    A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.ref_item_id,
    A.invoice_id, B.doc_no, B.doc_date, C.product_id
    --SELECT *
    FROM sl_so_balance_invoice A
    INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
    INNER JOIN sl_do_item C ON A.ref_id = C.do_id AND A.ref_item_id = C.do_item_id
    WHERE A.ref_doc_type_id IN (311) -- data SO/SI
    AND B.doc_date BETWEEN '20201001' AND '20210131'
) INSERT INTO tt_data_balance_invoice_20210216
SELECT A.*, C.group_brand_id
FROM data_balance_invoice A
INNER JOIN m_product B ON A.product_id = B.product_id
INNER JOIN m_product_custom_for_sasa C ON B.product_id = C.product_id AND B.tenant_id = C.tenant_id

--3. Keterangan terkait kelompok barang (group barang) dari user:
/*
group_brand_code (group_brand_id)
Group Brand FB:
1. FB2 (91)
2. FB4 (83)
3. FBL (146)

Group Brand FE:
1. FE2 (82)
2. FE4 (84)
3. FEX (100)

Group Brand FL:
1. FL2 (130)
2. FL4 (131)
*/

--4. Init table temp final
CREATE TABLE tt_outlet_aktif_20210216
(
    ou_id bigint
    ou_code character varying(10),
    partner_id bigint,
    partner_code character varying(30),
    partner_name character varying(1024),
    address1 text,
    city character varying(100),
    oa_be bigint,
    oa_fx bigint,
    oa bigint
)
WITH (
  OIDS=FALSE
);

--5. Insert data ke temp table per data OU dan customer
INSERT INTO tt_outlet_aktif_20210216
SELECT A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city, 0 AS oa_be, 0 AS oa_fx, 0 AS oa
    FROM tt_data_balance_invoice_20210216 A
    INNER JOIN t_ou B ON A.ou_id = B.ou_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 D.flg_official = 'Y'
    GROUP BY A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city


--6. Update kolom oa_be
WITH data_oa_be AS (
    SELECT A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
    FROM tt_data_balance_invoice_20210216 A
    INNER JOIN t_ou B ON A.ou_id = B.ou_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 D.flg_official = 'Y'
    WHERE A.group_brand_id IN (91, 83, 146, 82, 84, 100, 130, 131)
    GROUP BY A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
)
UPDATE tt_outlet_aktif_20210216 SET oa_be = 1
WHERE EXISTS(
    SELECT 1
    FROM data_oa_be A
    WHERE A.ou_id = tt_outlet_aktif_20210216.ou_id
    AND A.partner_id = tt_outlet_aktif_20210216.partner_id
)

--7. Update kolom oa_fx
WITH data_oa_be AS (
    SELECT A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
    FROM tt_data_balance_invoice_20210216 A
    INNER JOIN t_ou B ON A.ou_id = B.ou_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 D.flg_official = 'Y'
    WHERE A.group_brand_id NOT IN (91, 83, 146, 82, 84, 100, 130, 131)
    GROUP BY A.ou_id, B.ou_code, C.partner_id, C.partner_code, C.partner_name, D.address1, D.city
)
UPDATE tt_outlet_aktif_20210216 SET oa_fx = 1
WHERE EXISTS(
    SELECT 1
    FROM data_oa_be A
    WHERE A.ou_id = tt_outlet_aktif_20210216.ou_id
    AND A.partner_id = tt_outlet_aktif_20210216.partner_id
)

--8. Update kolom oa
UPDATE tt_outlet_aktif_20210216 SET oa = 1
WHERE tt_outlet_aktif_20210216.oa_fx = 1 OR tt_outlet_aktif_20210216.oa_be = 1

--Get data OA BE
-- Partner (customer) yang pernah transaksi untuk kelompok barang FB, FE dan FL

-- Get data OA FX
-- Partner (customer) yang pernah transaksi untuk kelompok barang selain FB, FE dan FL

Cek data
WITH data_balance_invoice AS (
    SELECT A.so_balance_invoice_id,
    A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.ref_item_id,
    A.invoice_id, B.doc_no, B.doc_date, C.product_id
    --SELECT *
    FROM sl_so_balance_invoice A
    INNER JOIN sl_invoice B ON A.invoice_id = B.invoice_id
    INNER JOIN sl_do_item C ON A.ref_id = C.do_id AND A.ref_item_id = C.do_item_id
    WHERE A.ref_doc_type_id IN (311) -- data SO/SI
    AND B.doc_date BETWEEN '20201001' AND '20210131'
) SELECT A.*, C.group_brand_id
FROM data_balance_invoice A
WHERE partner_id = vPartnerId AND A.ou_id = vOuId
INNER JOIN m_product B ON A.product_id = B.product_id
INNER JOIN m_product_custom_for_sasa C ON B.product_id = C.product_id AND B.tenant_id = C.tenant_id

1. vPartnerId dan vOuId diisi dengan partnerId dan ouId yang ingin dibandingkan
2. Result dari query diatas, cek kolom group_brand_id, apakah termasuk kelompok FB, FE, atau FL,
3. Dari pengecekan poin 2, bandingkan dengan hasil di excel. Apakah kolom OA BE, OA FX, dan OA sudah sesuai nilainya