berikut

CREATE OR REPLACE FUNCTION dw_insert_sales_invoice(character varying, character varying, bigint, bigint, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE

    pSessionId              ALIAS FOR $1;
    pDataId                 ALIAS FOR $2;
    pTenantId               ALIAS FOR $3;
    pOuId                   ALIAS FOR $4;
    pDatetime               ALIAS FOR $5;
    pUserId                 ALIAS FOR $6;

    vDocTypeInvoice         bigint := 321;
    vDocTypeSoByBrand       bigint := 398;
    vDocTypeDo              bigint := 311;

    vLastId                 bigint := -99;
    vEmptyId                bigint := -99;

BEGIN

    --cek last_id for Invoice
    IF EXISTS(SELECT 1 FROM dw_last_data_log WHERE tenant_id = pTenantId AND ou_id = pOuId AND doc_type_id = vDocTypeInvoice) THEN
        SELECT data_log_id INTO vLastId
        FROM dw_last_data_log
        WHERE tenant_id = pTenantId AND
            ou_id = pOuId AND
            doc_type_id = vDocTypeInvoice;
    END IF;

    --persiapan dokumen yang akan di proses item" nya
    INSERT INTO tt_process_dw_sales_header (
        session_id, doc_type_id, doc_id, data_log_id, doc_no, doc_date,
        year_month, customer_id, salesman_id, warehouse_id, province, city)
    SELECT pSessionId, A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
            substr(B.doc_date, 0, 7) AS year_month, B.partner_id, B.salesman_id, E.warehouse_id,
            f_get_province_by_partner(B.partner_id) AS province, f_get_city_by_partner(B.partner_id) AS city
    FROM fi_invoice_ar_balance A
    INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
    INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
    INNER JOIN sl_do_item D ON C.ref_item_id = D.do_item_id
    INNER JOIN sl_do E ON D.do_id = E.do_id
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND A.invoice_ar_balance_id > vLastId
    AND A.doc_type_id = vDocTypeInvoice
    GROUP BY A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
            B.partner_id, B.salesman_id, E.warehouse_id;

    --get data Invoice Non FOC dari DO
    INSERT INTO tt_process_dw_sales (
        session_id, data_id, doc_type_id, doc_id, data_log_id, doc_no, doc_date,
        year_month, customer_id, salesman_id, warehouse_id,
        product_id, ctgr_product_id,
        sub_ctgr_product_id,
        brand_id,
        province, city,
        qty, price, item_amount,
        discount_amount, tax_amount)
    SELECT pSessionId, pDataId, A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
        substr(B.doc_date, 0, 7) AS year_month, B.partner_id, B.salesman_id, E.warehouse_id,
        D.product_id, f_get_ctgr_product_by_product_id(D.product_id) AS ctgr_product_id,
        f_get_sub_ctgr_product_by_product_id(D.product_id) AS sub_ctgr_product_id,
        f_get_brand_by_product_id(D.product_id) AS brand_id,
        f_get_province_by_partner(B.partner_id) AS province, f_get_city_by_partner(B.partner_id) AS city,
        SUM(G.qty_dlv_so), F.nett_sell_price, SUM(G.item_amount - G.regular_disc_amount) AS item_amount,
        SUM(G.regular_disc_amount) AS discount_amount, SUM(COALESCE(H.tax_amount,0)) AS tax_amount
    FROM fi_invoice_ar_balance A
        INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
        INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
        INNER JOIN sl_do_item D ON C.ref_item_id = D.do_item_id
        INNER JOIN sl_do E ON D.do_id = E.do_id
        INNER JOIN sl_so_item F ON D.ref_id = F.so_item_id
        INNER JOIN sl_so_balance_invoice G ON C.tenant_id = G.tenant_id
            AND G.ou_id = B.ou_id
            AND G.so_id = E.ref_id
            AND G.ref_item_id = C.ref_item_id
            AND G.ref_doc_type_id = E.doc_type_id
            AND G.ref_id = E.do_id
            AND C.do_receipt_item_id = G.do_receipt_item_id
            AND C.do_receipt_item_id = vEmptyId
        LEFT OUTER JOIN sl_so_balance_invoice_tax H ON B.tenant_id = H.tenant_id
            AND H.ou_id = B.ou_id
            AND H.so_id = E.ref_id
            AND H.ref_item_id = C.ref_item_id
            AND H.ref_doc_type_id = E.doc_type_id
            AND H.ref_id = E.do_id
            AND C.do_receipt_item_id = H.do_receipt_item_id
            AND C.do_receipt_item_id = vEmptyId
            AND F.tax_id = H.tax_id
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_type_id = vDocTypeInvoice AND
        A.invoice_ar_balance_id > vLastId AND
        F.nett_sell_price <> 0
        AND EXISTS (
            SELECT 1
            FROM tt_process_dw_sales_header I
            WHERE I.session_id = pSessionId
                AND A.doc_type_id = I.doc_type_id
                AND B.invoice_id = I.doc_id
                AND A.invoice_ar_balance_id = I.data_log_id
                AND B.doc_no = I.doc_no
                AND B.doc_date = I.doc_date
                AND substr(B.doc_date, 0, 7) = I.year_month
                AND B.partner_id = I.customer_id
                AND B.salesman_id = I.salesman_id
                AND E.warehouse_id = I.warehouse_id
                AND f_get_province_by_partner(B.partner_id) = I.province
                AND f_get_city_by_partner(B.partner_id) = I.city
        )
    GROUP BY A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
        B.partner_id, B.salesman_id, E.warehouse_id, D.product_id, F.nett_sell_price
    HAVING SUM(G.qty_dlv_so) > 0;

    --get data Invoice FOC
    INSERT INTO tt_process_dw_sales (
        session_id, data_id, doc_type_id, doc_id, data_log_id, doc_no, doc_date,
        year_month, customer_id, salesman_id, warehouse_id,
        product_id, ctgr_product_id,
        sub_ctgr_product_id,
        brand_id,
        province, city,
        qty_foc, price, item_amount,
        discount_amount, tax_amount)
    SELECT pSessionId, pDataId, A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
        substr(B.doc_date, 0, 7) AS year_month, B.partner_id, B.salesman_id, E.warehouse_id,
        D.product_id, f_get_ctgr_product_by_product_id(D.product_id) AS ctgr_product_id,
        f_get_sub_ctgr_product_by_product_id(D.product_id) AS sub_ctgr_product_id,
        f_get_brand_by_product_id(D.product_id) AS brand_id,
        f_get_province_by_partner(B.partner_id) AS province, f_get_city_by_partner(B.partner_id) AS city,
        SUM(G.qty_dlv_so), F.nett_sell_price, SUM(G.item_amount - G.regular_disc_amount) AS item_amount,
        SUM(G.regular_disc_amount) AS discount_amount, SUM(COALESCE(H.tax_amount,0)) AS tax_amount
    FROM fi_invoice_ar_balance A
        INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
        INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
        INNER JOIN sl_do_item D ON C.ref_item_id = D.do_item_id
        INNER JOIN sl_do E ON D.do_id = E.do_id
        INNER JOIN sl_so_item F ON D.ref_id = F.so_item_id
        INNER JOIN sl_so_balance_invoice G ON C.tenant_id = G.tenant_id
            AND G.ou_id = B.ou_id
            AND G.so_id = E.ref_id
            AND G.ref_item_id = C.ref_item_id
            AND G.ref_doc_type_id = E.doc_type_id
            AND G.ref_id = E.do_id
            AND C.do_receipt_item_id = G.do_receipt_item_id
            AND C.do_receipt_item_id = vEmptyId
        LEFT OUTER JOIN sl_so_balance_invoice_tax H ON B.tenant_id = H.tenant_id
            AND H.ou_id = B.ou_id
            AND H.so_id = E.ref_id
            AND H.ref_item_id = C.ref_item_id
            AND H.ref_doc_type_id = E.doc_type_id
            AND H.ref_id = E.do_id
            AND C.do_receipt_item_id = H.do_receipt_item_id
            AND C.do_receipt_item_id = vEmptyId
            AND F.tax_id = H.tax_id
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.doc_type_id = vDocTypeInvoice AND
        A.invoice_ar_balance_id > vLastId AND
        F.nett_sell_price = 0
        AND EXISTS (
            SELECT 1
            FROM tt_process_dw_sales_header I
            WHERE I.session_id = pSessionId
                AND A.doc_type_id = I.doc_type_id
                AND B.invoice_id = I.doc_id
                AND A.invoice_ar_balance_id = I.data_log_id
                AND B.doc_no = I.doc_no
                AND B.doc_date = I.doc_date
                AND substr(B.doc_date, 0, 7) = I.year_month
                AND B.partner_id = I.customer_id
                AND B.salesman_id = I.salesman_id
                AND E.warehouse_id = I.warehouse_id
                AND f_get_province_by_partner(B.partner_id) = I.province
                AND f_get_city_by_partner(B.partner_id) = I.city
        )
    GROUP BY A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
        B.partner_id, B.salesman_id, E.warehouse_id, D.product_id, F.nett_sell_price
    HAVING SUM(G.qty_dlv_so) > 0;

    -- INSERT DATA DO RECEIPT UNTUK DATA INVOICE
    WITH tt_process_dw_sales_inv_temp AS (
        SELECT data_id, doc_type_id, doc_id, data_log_id
        FROM tt_process_dw_sales
        WHERE session_id = pSessionId
            AND data_id =  pDataId
            AND doc_type_id = vDocTypeInvoice
        GROUP BY data_id, doc_type_id, doc_id, data_log_id
    )
    INSERT INTO tt_process_dw_sales (
        session_id, data_id, doc_type_id, doc_id, data_log_id, doc_no, doc_date,
        year_month, customer_id, salesman_id, warehouse_id,
        product_id, ctgr_product_id,
        sub_ctgr_product_id,
        brand_id,
        province, city,
        qty, qty_foc,
        price, item_amount,
        discount_amount, tax_amount)
    SELECT pSessionId, pDataId, B.doc_type_id, B.invoice_id, A.data_log_id, B.doc_no, B.doc_date,
        substr(B.doc_date, 0, 7) AS year_month, B.partner_id, B.salesman_id, F.warehouse_id,
        H.product_id, f_get_ctgr_product_by_product_id(H.product_id) AS ctgr_product_id,
        f_get_sub_ctgr_product_by_product_id(H.product_id) AS sub_ctgr_product_id,
        f_get_brand_by_product_id(H.product_id) AS brand_id,
        f_get_province_by_partner(B.partner_id) AS province, f_get_city_by_partner(B.partner_id) AS city,
        CASE WHEN D.nett_sell_price <> 0 THEN SUM(I.qty_dlv_so) ELSE 0 END AS qty,
        CASE WHEN D.nett_sell_price = 0 THEN SUM(I.qty_dlv_so) ELSE 0 END AS qty_foc,
        D.nett_sell_price, SUM(I.item_amount - I.regular_disc_amount) AS item_amount,
        SUM(I.regular_disc_amount) AS discount_amount, SUM(COALESCE(J.tax_amount, 0)) AS tax_amount
    FROM tt_process_dw_sales_inv_temp A
        INNER JOIN sl_invoice B ON A.doc_type_id = B.doc_type_id AND A.doc_id = B.invoice_id
        INNER JOIN sl_so C ON B.ref_id = C.so_id AND B.ref_doc_type_id = vDocTypeSoByBrand
        INNER JOIN sl_so_item D ON C.so_id = D.so_id
        INNER JOIN sl_do_item E ON D.so_item_id = E.ref_id
        INNER JOIN sl_do F ON E.do_id = F.do_id AND C.so_id = F.ref_id AND C.doc_type_id = F.ref_doc_type_id
        INNER JOIN in_do_receipt G ON F.do_id = G.ref_id AND G.ref_doc_type_id = vDocTypeDo
        INNER JOIN in_do_receipt_item H ON G.do_receipt_id = H.do_receipt_id AND H.ref_id = E.do_item_id
        INNER JOIN sl_so_balance_invoice I ON B.tenant_id = I.tenant_id
            AND I.ou_id = B.ou_id
            AND I.so_id = C.so_id
            AND I.ref_item_id = E.do_item_id
            AND I.ref_doc_type_id = F.doc_type_id
            AND I.ref_id = F.do_id
            AND H.do_receipt_item_id = I.do_receipt_item_id
        LEFT OUTER JOIN sl_so_balance_invoice_tax J ON B.tenant_id = J.tenant_id
            AND J.ou_id = B.ou_id
            AND J.so_id = C.so_id
            AND J.ref_item_id = E.do_item_id
            AND J.ref_doc_type_id = F.doc_type_id
            AND J.ref_id = F.do_id
            AND H.do_receipt_item_id = J.do_receipt_item_id
            AND D.tax_id = J.tax_id
    GROUP BY B.doc_type_id, B.invoice_id, A.data_log_id, B.doc_no, B.doc_date,
             B.partner_id, B.salesman_id, F.warehouse_id, H.product_id, D.nett_sell_price;

    IF EXISTS(SELECT 1 FROM tt_process_dw_sales WHERE session_id = pSessionId AND data_id = pDataId AND doc_type_id = vDocTypeInvoice) THEN
        SELECT MAX(data_log_id) INTO vLastId
        FROM tt_process_dw_sales
        WHERE session_id = pSessionId AND
            data_id = pDataId AND
            doc_type_id = vDocTypeInvoice;
    END IF;

    IF EXISTS(SELECT 1 FROM dw_last_data_log WHERE tenant_id = pTenantId AND ou_id = pOuId AND doc_type_id = vDocTypeInvoice) THEN
        UPDATE dw_last_data_log
        SET data_log_id = vLastId,
            update_datetime = pDatetime,
            update_user_id = pUserId,
            version = version + 1
        WHERE tenant_id = pTenantId AND
            ou_id = pOuId AND
            doc_type_id = vDocTypeInvoice;
    ELSE
        INSERT INTO dw_last_data_log (
            tenant_id, ou_id, doc_type_id, doc_desc, data_log_id,
            create_datetime, create_user_id, update_datetime, update_user_id, version)
        SELECT pTenantId, pOuId, vDocTypeInvoice, doc_desc, vLastId,
            pDatetime, pUserId, pDatetime, pUserId, 0 AS version
        FROM m_document
        WHERE doc_type_id = vDocTypeInvoice;
    END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /