Temp SO Update Data

--Cek progress SO

-- init table temp
CREATE TABLE IF NOT EXISTS  temp_sl_so_progress_data_20220218(
    so_id BIGINT NOT NULL,
    doc_no CHARACTER varying(30),
    doc_date CHARACTER varying(8),
    ou_id BIGINT,
    partner_id BIGINT,
    so_item_id BIGINT,
    product_id BIGINT
)WITH (
  OIDS=FALSE
);

-- table unutk view
CREATE TABLE IF NOT EXISTS temp_sl_so_progress_show_data_20220218(
    so_id BIGINT NOT NULL,
    doc_no CHARACTER varying(30),
    doc_date CHARACTER varying(8),
    ou_id BIGINT,
    partner_id BIGINT,
    so_item_id BIGINT,
    product_id BIGINT,
    po_id BIGINT DEFAULT -99,
    po_doc_no CHARACTER varying(30) DEFAULT '',
    po_doc_date CHARACTER varying(8) DEFAULT '',
    gr_id BIGINT DEFAULT -99,
    gr_doc_no CHARACTER varying(30) DEFAULT '',
    gr_doc_date CHARACTER varying(8) DEFAULT '',
    do_id BIGINT DEFAULT -99,
    do_doc_no CHARACTER varying(30) DEFAULT '',
    do_doc_date CHARACTER varying(8) DEFAULT '',
    invoice_id BIGINT DEFAULT -99,
    invoice_doc_no CHARACTER varying(30) DEFAULT '',
    invoice_doc_date CHARACTER varying(8) DEFAULT ''
)WITH (
  OIDS=FALSE
);

-- insert data ke temp table
INSERT INTO  temp_sl_so_progress_data_20220218(so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id)
SELECT A.so_id, A.doc_no, A.doc_date, A.ou_id, A.partner_id, B.so_item_id, B.product_id
FROM sl_so A
INNER JOIN sl_so_item B ON A.so_id = B.so_id
WHERE B.flg_tax_amount <> 'Y' AND
A.doc_no IN ('MEL-SO22.01.0605');

-- get data progress_so
-- data SO yang belum diapa2in
INSERT INTO temp_sl_so_progress_show_data_20220218(
    so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id
)
SELECT A.*
FROM  temp_sl_so_progress_data_20220218 A
WHERE NOT EXISTS (SELECT 1 FROM pu_po_item B WHERE B.ref_id=A.so_item_id) AND
    NOT EXISTS (SELECT 1 FROM sl_do_item C WHERE C.ref_id=A.so_item_id);


-- data so po gr
WITH data_so_po AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY A.doc_no ORDER BY A.doc_no ASC) AS line_no,
    A.so_id, A.doc_no, A.doc_date, A.partner_id, A.ou_id, A.so_item_id, A.product_id,
    B.po_id, B.doc_no AS po_no, B.doc_date AS po_date
    FROM  temp_sl_so_progress_data_20220218 A
    INNER JOIN pu_po B ON A.so_id = B.ref_id AND B.ref_doc_type_id = 301
    INNER JOIN pu_po_item C ON B.po_id = C.po_id AND C.ref_id = A.so_item_id
)
INSERT INTO temp_sl_so_progress_show_data_20220218(
    so_id, doc_no, doc_date, ou_id, partner_id, so_item_id, product_id,
    po_id, po_doc_no, po_doc_date,
    gr_id, gr_doc_no, gr_doc_date
)
SELECT A.so_id, A.doc_no, A.doc_date, A.ou_id, A.partner_id, A.so_item_id, A.product_id,
    A.po_id, A.po_no, A.po_date,
    B.receive_goods_id, B.doc_no, B.doc_date
FROM data_so_po A
INNER JOIN pu_receive_goods B ON A.po_id = B.ref_id
WHERE B.ref_doc_type_id = 101; --> docType PO

-- data so -> do
UPDATE temp_sl_so_progress_show_data_20220218 A
    SET do_id = C.do_id,
    do_doc_no = C.doc_no,
    do_doc_date = C.doc_date
FROM sl_do C
INNER JOIN sl_do_item D ON C.do_id = D.do_id
WHERE A.so_id = C.ref_id AND C.ref_doc_type_id = 301
AND D.ref_id = A.so_item_id;

-- data so -> do -> si
UPDATE temp_sl_so_progress_show_data_20220218 A
    SET invoice_id = B.invoice_id, invoice_doc_no = B.doc_no, invoice_doc_date = B.doc_date
FROM sl_invoice B
INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id AND C.ref_doc_type_id = 311
WHERE B.ref_doc_type_id = 301
AND A.do_id = C.ref_id
AND A.so_id = B.ref_id;

-- Get data final
    SELECT doc_no AS so_no, doc_date AS so_date, f_get_ou_code(ou_id) AS ou_code,
        f_get_partner_name(partner_id) AS customer, f_get_product_code(product_id), f_get_product_name(product_id),
        po_doc_no, po_doc_date, gr_doc_no, gr_doc_date, do_doc_no, do_doc_date, invoice_doc_no AS sales_invoice_no,
        invoice_doc_date AS sales_invoice_date
    -- SELECT *
    FROM temp_sl_so_progress_show_data_20220218 ORDER BY so_id;