RSI

CREATE OR REPLACE FUNCTION dw_insert_return_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;

    vDocTypeReturnInvoice   bigint := 380;

    vLastId                 bigint := -99;
    vRoundingModeNonTax     character varying(5);
    vDigitDppRoundingTax    integer;

BEGIN

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'dpp.rounding.decimal.tax')::integer INTO vDigitDppRoundingTax;

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

    DELETE FROM tt_process_dw_sales_header WHERE session_id = pSessionId;

    --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, province, city)
    SELECT pSessionId,A.doc_type_id, B.invoice_id, A.invoice_ar_balance_id, B.doc_no, B.doc_date,
            LEFT(B.doc_date, 6) AS year_month, B.partner_id, B.salesman_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
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND A.invoice_ar_balance_id > vLastId
    AND A.doc_type_id = vDocTypeReturnInvoice;

    --get data Return Invoice NON-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, price, item_amount,
        discount_amount, tax_amount)
    SELECT pSessionId, pDataId, A.doc_type_id,A.doc_id, A.data_log_id, A.doc_no, A.doc_date,
        A.year_month, A.partner_id, A.salesman_id, F.warehouse_to_id,
        G.product_id, f_get_ctgr_product_by_product_id(G.product_id) AS ctgr_product_id,
        f_get_sub_ctgr_product_by_product_id(G.product_id) AS sub_ctgr_product_id,
        f_get_brand_by_product_id(G.product_id) AS brand_id,
        A.province, A.city,
        -1 * G.qty_realization AS qty, E.nett_sell_price,
        -1 * f_get_dpp_after_discount(G.qty_realization * E.gross_sell_price, E.flg_tax_amount, f_get_tax_amount_after_discount(G.qty_realization * E.gross_sell_price, E.flg_tax_amount, E.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeReturnInvoice, E.curr_code))) AS item_amount,
        (G.qty_realization * 0 * -1) AS discount_amount,
        -1 * f_get_tax_amount_after_discount(G.qty_realization * E.gross_sell_price, E.flg_tax_amount, E.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeReturnInvoice, E.curr_code)) AS tax_amount
    FROM tt_process_dw_sales_header A
        INNER JOIN sl_invoice_item C ON A.doc_id = C.invoice_id
        INNER JOIN sl_so_balance_invoice D ON B.tenant_id = D.tenant_id
            AND D.ou_id = B.ou_id
            AND D.so_id = B.ref_id
            AND D.ref_item_id = C.ref_item_id
            AND D.ref_doc_type_id = C.ref_doc_type_id
            AND D.ref_id = C.ref_id
            AND D.do_receipt_item_id = C.do_receipt_item_id
        INNER JOIN sl_request_return_sales_brand_item E ON C.ref_item_id = E.request_return_sales_brand_item_id
        INNER JOIN in_inventory F ON C.ref_id = F.inventory_id AND C.ref_doc_type_id = F.doc_type_id
        INNER JOIN in_inventory_item G ON F.inventory_id = G.inventory_id AND E.brand_id = f_get_brand_by_product_id(G.product_id)
    WHERE A.session_id = pSessionId
        AND E.nett_sell_price <> 0;

    --get data Return 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,A.doc_id, A.data_log_id, A.doc_no, A.doc_date,
        A.year_month, A.partner_id, A.salesman_id, F.warehouse_to_id,
        G.product_id, f_get_ctgr_product_by_product_id(G.product_id) AS ctgr_product_id,
        f_get_sub_ctgr_product_by_product_id(G.product_id) AS sub_ctgr_product_id,
        f_get_brand_by_product_id(G.product_id) AS brand_id,
        A.province, A.city,
        -1 * G.qty_realization AS qty, E.nett_sell_price,
        -1 * f_get_dpp_after_discount(G.qty_realization * E.gross_sell_price, E.flg_tax_amount, f_get_tax_amount_after_discount(G.qty_realization * E.gross_sell_price, E.flg_tax_amount, E.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeReturnInvoice, E.curr_code))) AS item_amount,
        (G.qty_realization * 0 * -1) AS discount_amount,
        -1 * f_get_tax_amount_after_discount(G.qty_realization * E.gross_sell_price, E.flg_tax_amount, E.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeReturnInvoice, E.curr_code)) AS tax_amount
    FROM tt_process_dw_sales_header A
        INNER JOIN sl_invoice_item C ON A.doc_id = C.invoice_id
        INNER JOIN sl_so_balance_invoice D ON B.tenant_id = D.tenant_id
            AND D.ou_id = B.ou_id
            AND D.so_id = B.ref_id
            AND D.ref_item_id = C.ref_item_id
            AND D.ref_doc_type_id = C.ref_doc_type_id
            AND D.ref_id = C.ref_id
            AND D.do_receipt_item_id = C.do_receipt_item_id
        INNER JOIN sl_request_return_sales_brand_item E ON C.ref_item_id = E.request_return_sales_brand_item_id
        INNER JOIN in_inventory F ON C.ref_id = F.inventory_id AND C.ref_doc_type_id = F.doc_type_id
        INNER JOIN in_inventory_item G ON F.inventory_id = G.inventory_id AND E.brand_id = f_get_brand_by_product_id(G.product_id)
    WHERE WHERE A.session_id = pSessionId
        AND E.nett_sell_price = 0;

    IF EXISTS(SELECT 1 FROM tt_process_dw_sales WHERE session_id = pSessionId AND data_id = pDataId AND doc_type_id = vDocTypeReturnInvoice) 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 = vDocTypeReturnInvoice;
    END IF;

    IF EXISTS(SELECT 1 FROM dw_last_data_log WHERE tenant_id = pTenantId AND ou_id = pOuId AND doc_type_id = vDocTypeReturnInvoice) 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 = vDocTypeReturnInvoice;
    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, vDocTypeReturnInvoice, doc_desc, vLastId,
            pDatetime, pUserId, pDatetime, pUserId, 0 AS version
        FROM m_document
        WHERE doc_type_id = vDocTypeReturnInvoice;
    END IF;

    DELETE FROM tt_process_dw_sales_header WHERE session_id = pSessionId;

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