insert insert

CREATE OR REPLACE FUNCTION r_sls_commission_for_igs(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, character varying, character varying, bigint, bigint, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pUserId                 ALIAS FOR $3;
    pRoleId                 ALIAS FOR $4;
    pDatetime               ALIAS FOR $5;
    pOuId                   ALIAS FOR $6;
    pSalesId                ALIAS FOR $7;
    pYear                   ALIAS FOR $8;
    pQuarterCode            ALIAS FOR $9;
    pLineOfBusinessCode     ALIAS FOR $10;
    pBrandId                ALIAS FOR $11;
    pCtgrProductId          ALIAS FOR $12;
    pSubCtgrProductId       ALIAS FOR $13;

    result                      RECORD;
    vMonthFrom                  character varying(4);
    vMonthTo                    character varying(4);
    vDateFrom                   character varying(8);
    vDateTo                     character varying(8);
    vMainCoaExtCommission       character varying(10);
    vSubCoaExtCommission        character varying(10);
    vCurrGL                     character varying(5);
    vOneMonthAfterDateTo        character varying(6);
    vOneMonthBeforeDateTo       character varying(6);
    vOneMonthBeforeDateFrom     character varying(6);
    vComboId                    character varying := 'DIGITCURRENCY';
    vCurrCodeUSD                character varying := 'USD';
    vCurrCodeIDR                character varying := 'IDR';
    vRoundingDigitUSD           integer;
    vRoundingDigitIDR           integer;
    vEmptyId                    bigint := -99;
    vNol                        numeric := 0;
    vOne                        numeric := 1;
    vDefault                    character varying := 'DEFAULT';
    vEmptyString                character varying := '';
    vSpace                      character varying := ' ';

    vNo                         character varying := 'N';
    vYes                        character varying := 'Y';
    vStatusDocDraft             character varying := 'D';
    vStatusDocRelease           character varying := 'R';
    vGroupSegmentOthers         character varying := 'OTHERS';
    vGroupSegmentSo             character varying := 'SO';
    vTypeExcRateCom             character varying := 'COM';
    vTypeExcRateTax             character varying := 'TAX';
    vDocTypeSalesInvoice        bigint := 321;
    vDocTypeReturnSalesInvoice  bigint := 331;
    vDocTypeRg                  bigint := 111;
    vPrevClosingYearMonth       character varying(6);


    vCoaIdExtCommission         bigint;
    vTwoMonthsFromDateTo        character varying(6);
BEGIN
    /*
     * ambil nilai date from dan date to
     * ambil nilai pembulatan
     * ambil nilai valuta buku
     * ambil coa id
     * ambil 1 bulan setelah date to dan 1 bulan sebelum date from
     * delete fi_temp_summary_monthly_ar date_year_month des-apr
     * insert dari fi_summary_monthly_ar
     * ambil bulan terakhir tutup bulan
     * menjalankan function tutup bulan sementara
     * ambil data hasil tutup bulan sementara dan join ke tabel so (invoice yg pelunasannya sampe sebulan setelahnya)
     * ambil data yg doc_datenya sebulan sebelum dateFrom dan pelunasannya sebulan sebelum dateTo
     * update nilai price_idr
     * update table temporary for numerator_rate, denominator_rate, price_idr
     * update item_amount menjadi IDR
     * ambil others cost
     * update table cost tr_data_so_sales_for_commission non ext commission
     * update table cost tr_data_so_sales_for_commission ext commission
     */

    DELETE FROM tr_data_so_sales_commission_for_igs WHERE session_id = pSessionId;

    --ambil nilai dateFrom dan dateTo
    SELECT month_from, month_to
    FROM dt_period_quarter
    WHERE quarter = pQuarterCode INTO result;
    vMonthFrom := result.month_from;
    vMonthTo := result.month_to;

    vDateFrom := pYear || vMonthFrom;
    vDateTo := pYear || vMonthTo;

    /* ambil nilai pembulatan*/
    vRoundingDigitUSD := CAST(f_get_digit_decimal_currency(vComboId,vCurrCodeUSD) AS integer);
    vRoundingDigitIDR := CAST(f_get_digit_decimal_currency(vComboId,vCurrCodeIDR) AS integer);

    /* ambil valuta buku */
    vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');

    /* ambil coa id */
    vMainCoaExtCommission := f_get_value_system_config_by_param_code(pTenantId,'MainCoaExtCommission');
    vSubCoaExtCommission := f_get_value_system_config_by_param_code(pTenantId,'SubCoaExtCommission');

    SELECT coa_id INTO vCoaIdExtCommission
    FROM m_coa
    WHERE main_acc = vMainCoaExtCommission
        AND sub_acc = vSubCoaExtCommission;

    /* ambil 1 bulan setelah date to dan 1 bulan sebelum date from*/
    SELECT TO_CHAR(TO_DATE(vDateTo,'YYYYMMDD') + interval '1 Month','YYYYMM') INTO vOneMonthAfterDateTo;
    SELECT TO_CHAR(TO_DATE(vDateTo,'YYYYMMDD') - interval '1 Month','YYYYMM') INTO vOneMonthBeforeDateTo;
    SELECT TO_CHAR(TO_DATE(vDateFrom,'YYYYMMDD') - interval '1 Month','YYYYMM') INTO vOneMonthBeforeDateFrom;

    -- delete fi_temp_summary_monthly_ar
    DELETE FROM fi_temp_summary_monthly_ar
    WHERE date_year_month BETWEEN vOneMonthBeforeDateFrom AND vOneMonthAfterDateTo;

    --insert dari fi_summary_monthly_ar
    INSERT INTO fi_temp_summary_monthly_ar(date_year_month, tenant_id, ou_id, doc_type_id, invoice_id,
        invoice_amount, balance_amount, payment_amount, curr_code, version,
        create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.date_year_month, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
        A.invoice_amount, A.balance_amount, A.payment_amount, A.curr_code, vNol,
        pDatetime, pUserId, pDatetime, pUserId
    FROM fi_summary_monthly_ar A
    INNER JOIN m_ou_structure B ON A.ou_id = B.ou_id
    WHERE A.date_year_month BETWEEN vOneMonthBeforeDateFrom AND vOneMonthAfterDateTo
    AND B.ou_bu_id = pOuId;

    /* ambil bulan terakhir tutup bulan */
    SELECT MAX(date_year_month) INTO vPrevClosingYearMonth
    FROM m_admin_process_ledger
    WHERE ou_id = pOuId
        AND ledger_code = 'FIN.AR'
        AND status_ledger = '1';

    /* menjalankan function tutup bulan sementara */
    WHILE vPrevClosingYearMonth < vOneMonthAfterDateTo LOOP
        SELECT TO_CHAR(TO_DATE(vPrevClosingYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vPrevClosingYearMonth;
        PERFORM fi_temp_summary_ar(pTenantId,pSessionId,pOuId,vPrevClosingYearMonth,pDatetime,pUserId);
    END LOOP;

    --ambil data hasil tutup bulan sementara dan join ke tabel so (invoice yg pelunasannya sampe sebulan setelahnya)
    WITH vw_fi_all_invoice_ar_for_commission AS (
        SELECT A.invoice_ar_balance_id AS id, A.doc_type_id, A.invoice_ar_id
        FROM fi_invoice_ar_balance A
        INNER JOIN m_ou_structure C ON A.ou_id = C.ou_id
        WHERE A.tenant_id = pTenantId
        AND C.ou_bu_id = pOuId

        UNION ALL

        SELECT a.invoice_tax_ar_balance_id AS id, a.doc_type_id, b.invoice_ar_id
        FROM fi_invoice_tax_ar_balance a
        INNER JOIN fi_invoice_ar_balance b on a.invoice_ar_balance_id = b.invoice_ar_balance_id
        INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
        AND A.tenant_id = pTenantId
        AND D.ou_bu_id = pOuId
    )
    --ambil data hasil tutup bulan sementara dan join ke tabel so (invoice yg pelunasannya sampe sebulan setelahnya)
    INSERT INTO tr_data_so_sales_commission_for_igs(
            session_id, tenant_id, date_year_month, invoice_id, doc_type_id,
            inv_curr_code, inv_doc_no, inv_doc_date, ou_id, so_id,
            so_doc_no, so_doc_date, do_id, do_item_id, original_total_amount,
            so_item_id, product_id, qty_dlv_int, base_uom_id, partner_id,
            salesman_id, brand_id, line_of_business, numerator_rate, denominator_rate,
            original_price, total_amount_idr, total_amount_usd, other_costs, other_costs_idr,
            other_costs_usd, other_costs_curr_code, other_cost_ext_commission, warranty_sell_amount)
    SELECT pSessionId, pTenantId, ' ', A.invoice_id, A.doc_type_id,
        A.curr_code, B.doc_no, B.doc_date, A.ou_id, B.ref_id,
        G.doc_no, G.doc_date, C.ref_id, C.ref_item_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN -1 * SUM(C.ref_item_amount) ELSE SUM(C.ref_item_amount) END,
        F.so_item_id, F.product_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN SUM(-1*(F.qty_int * D.qty_dlv_so)/F.qty_so) ELSE SUM((F.qty_int * D.qty_dlv_so)/F.qty_so) END, F.base_uom_id, G.partner_id,
        G.salesman_id, I.brand_id, J.line_of_business, vOne, vOne,
        vNol, vNol, vNol, vNol, vNol,
        vNol, vCurrCodeIdr, vNol, vNol
    FROM fi_temp_summary_monthly_ar A
    INNER JOIN vw_fi_all_invoice_ar_for_commission H ON A.doc_type_id = H.doc_type_id AND A.invoice_id = H.id
    INNER JOIN sl_invoice B ON H.invoice_ar_id = B.invoice_id AND B.doc_type_id = H.doc_type_id
    INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
    INNER JOIN sl_so_balance_invoice D ON B.tenant_id = D.tenant_id AND B.ou_id = D.ou_id AND B.partner_id = D.partner_id
        AND C.ref_doc_type_id = D.ref_doc_type_id AND C.ref_id = D.ref_id AND C.ref_item_id = D.ref_item_id
        AND C.do_receipt_item_id = D.do_receipt_item_id
    INNER JOIN sl_do_item E ON C.ref_item_id = E.do_item_id
    INNER JOIN sl_so_item F ON E.ref_id = F.so_item_id
    INNER JOIN sl_so G ON F.so_id = G.so_id
    INNER JOIN m_product I ON F.product_id = I.product_id
    INNER JOIN m_partner J ON J.partner_id = G.partner_id
    /*LEFT JOIN sl_so_balance_warranty_invoice L ON B.tenant_id = L.tenant_id AND L.ou_id = B.ou_id AND L.partner_id = B.partner_id
        AND L.ref_doc_type_id = C.ref_doc_type_id AND L.ref_id = C.ref_id AND L.ref_item_id = C.ref_item_id
        AND L.do_receipt_item_id = C.do_receipt_item_id*/
    WHERE A.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeReturnSalesInvoice)
        AND A.date_year_month BETWEEN SUBSTRING(vDateFrom, 1, 6) AND vOneMonthAfterDateTo
        AND (pSalesId = vEmptyId OR G.salesman_id = pSalesId)
        AND (pBrandId = vEmptyId OR I.brand_id = pBrandId)
        AND (pCtgrProductId = vEmptyId OR I.ctgr_product_id = pCtgrProductId)
        AND (pSubCtgrProductId = vEmptyId OR I.sub_ctgr_product_id = pSubCtgrProductId)
        AND (pLineOfBusinessCode = vEmptyString OR J.line_of_business = pLineOfBusinessCode)
        AND B.doc_date BETWEEN vDateFrom and vDateTo
        AND F.qty_so > 0
        AND A.date_year_month = (
            SELECT MAX(Z.date_year_month)
            FROM fi_temp_summary_monthly_ar Z
            WHERE A.invoice_id = Z.invoice_id
            AND A.doc_type_id = Z.doc_type_id
            AND A.ou_id = Z.ou_id
            AND A.tenant_id = Z.tenant_id
        )
    GROUP BY A.invoice_id, A.doc_type_id, J.line_of_business, A.ou_id, A.curr_code, B.doc_no, B.doc_date, B.ref_id, C.ref_id, C.ref_item_id,
        F.so_item_id, F.product_id, I.product_code, I.product_name, F.base_uom_id, G.partner_id, I.brand_id, G.salesman_id, G.doc_no, G.doc_date;


    -- Adrian, Mar 7, 2017, Memisahkan insert untuk sl_so_balance_warranty_invoice
    --ambil data hasil tutup bulan sementara dan join ke tabel so (invoice yg pelunasannya sampe sebulan setelahnya)
    WITH vw_fi_all_invoice_ar_for_commission AS (
        SELECT A.invoice_ar_balance_id AS id, A.doc_type_id, A.invoice_ar_id
        FROM fi_invoice_ar_balance A
        INNER JOIN m_ou_structure C ON A.ou_id = C.ou_id
        WHERE A.tenant_id = pTenantId
        AND C.ou_bu_id = pOuId

        UNION ALL

        SELECT a.invoice_tax_ar_balance_id AS id, a.doc_type_id, b.invoice_ar_id
        FROM fi_invoice_tax_ar_balance a
        INNER JOIN fi_invoice_ar_balance b on a.invoice_ar_balance_id = b.invoice_ar_balance_id
        INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
        AND A.tenant_id = pTenantId
        AND D.ou_bu_id = pOuId
    )
    --ambil data hasil tutup bulan sementara dan join ke tabel so (invoice yg pelunasannya sampe sebulan setelahnya)
    INSERT INTO tr_data_so_sales_commission_for_igs(
            session_id, tenant_id, date_year_month, invoice_id, doc_type_id,
            inv_curr_code, inv_doc_no, inv_doc_date, ou_id, so_id,
            so_doc_no, so_doc_date, do_id, do_item_id, original_total_amount,
            so_item_id, product_id, qty_dlv_int, base_uom_id, partner_id,
            salesman_id, brand_id, line_of_business, numerator_rate, denominator_rate,
            original_price, total_amount_idr, total_amount_usd, other_costs, other_costs_idr,
            other_costs_usd, other_costs_curr_code, other_cost_ext_commission, warranty_sell_amount)
    SELECT pSessionId, pTenantId, ' ', A.invoice_id, A.doc_type_id,
        A.curr_code, B.doc_no, B.doc_date, A.ou_id, B.ref_id,
        G.doc_no, G.doc_date, C.ref_id, C.ref_item_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN -1 * SUM(L.warranty_sell_amount) ELSE SUM(L.warranty_sell_amount) END,
        F.so_item_id, F.product_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN SUM(-1*(F.qty_int * D.qty_dlv_so)/F.qty_so) ELSE SUM((F.qty_int * D.qty_dlv_so)/F.qty_so) END, F.base_uom_id, G.partner_id,
        G.salesman_id, I.brand_id, J.line_of_business, vOne, vOne,
        vNol, vNol, vNol, vNol, vNol,
        vNol, vCurrCodeIdr, vNol, vNol
    FROM fi_temp_summary_monthly_ar A
    INNER JOIN vw_fi_all_invoice_ar_for_commission H ON A.doc_type_id = H.doc_type_id AND A.invoice_id = H.id
    INNER JOIN sl_invoice B ON H.invoice_ar_id = B.invoice_id AND B.doc_type_id = H.doc_type_id
    INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
    INNER JOIN sl_so_balance_invoice D ON B.tenant_id = D.tenant_id AND B.ou_id = D.ou_id AND B.partner_id = D.partner_id
        AND C.ref_doc_type_id = D.ref_doc_type_id AND C.ref_id = D.ref_id AND C.ref_item_id = D.ref_item_id
        AND C.do_receipt_item_id = D.do_receipt_item_id
    INNER JOIN sl_do_item E ON C.ref_item_id = E.do_item_id
    INNER JOIN sl_so_item F ON E.ref_id = F.so_item_id
    INNER JOIN sl_so G ON F.so_id = G.so_id
    INNER JOIN m_product I ON F.product_id = I.product_id
    INNER JOIN m_partner J ON J.partner_id = G.partner_id
    INNER JOIN sl_so_balance_warranty_invoice L ON B.tenant_id = L.tenant_id AND L.ou_id = B.ou_id AND L.partner_id = B.partner_id
        AND L.ref_doc_type_id = C.ref_doc_type_id AND L.ref_id = C.ref_id AND L.ref_item_id = C.ref_item_id
        AND L.do_receipt_item_id = C.do_receipt_item_id
    WHERE A.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeReturnSalesInvoice)
        AND A.date_year_month BETWEEN SUBSTRING(vDateFrom, 1, 6) AND vOneMonthAfterDateTo
        AND (pSalesId = vEmptyId OR G.salesman_id = pSalesId)
        AND (pBrandId = vEmptyId OR I.brand_id = pBrandId)
        AND (pCtgrProductId = vEmptyId OR I.ctgr_product_id = pCtgrProductId)
        AND (pSubCtgrProductId = vEmptyId OR I.sub_ctgr_product_id = pSubCtgrProductId)
        AND (pLineOfBusinessCode = vEmptyString OR J.line_of_business = pLineOfBusinessCode)
        AND B.doc_date BETWEEN vDateFrom and vDateTo
        AND F.qty_so > 0
        AND A.date_year_month = (
            SELECT MAX(Z.date_year_month)
            FROM fi_temp_summary_monthly_ar Z
            WHERE A.invoice_id = Z.invoice_id
            AND A.doc_type_id = Z.doc_type_id
            AND A.ou_id = Z.ou_id
            AND A.tenant_id = Z.tenant_id
        )
    GROUP BY A.invoice_id, A.doc_type_id, J.line_of_business, A.ou_id, A.curr_code, B.doc_no, B.doc_date, B.ref_id, C.ref_id, C.ref_item_id,
        F.so_item_id, F.product_id, I.product_code, I.product_name, F.base_uom_id, G.partner_id, I.brand_id, G.salesman_id, G.doc_no, G.doc_date;


    WITH vw_fi_all_invoice_ar_for_commission AS (
        SELECT A.invoice_ar_balance_id AS id, A.doc_type_id, A.invoice_ar_id
        FROM fi_invoice_ar_balance A
        INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
        WHERE A.tenant_id = pTenantId
        AND D.ou_bu_id = pOuId

        UNION ALL

        SELECT a.invoice_tax_ar_balance_id AS id, a.doc_type_id, b.invoice_ar_id
        FROM fi_invoice_tax_ar_balance a
        INNER JOIN fi_invoice_ar_balance b on a.invoice_ar_balance_id = b.invoice_ar_balance_id
        INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
        AND A.tenant_id = pTenantId
        AND D.ou_bu_id = pOuId
    )

    --ambil data yg doc_datenya sebulan sebelum dateFrom dan pelunasannya satu bulan sebelum date to
    INSERT INTO tr_data_so_sales_commission_for_igs(
            session_id, tenant_id, date_year_month, invoice_id, doc_type_id,
            inv_curr_code, inv_doc_no, inv_doc_date, ou_id, so_id,
            so_doc_no, so_doc_date, do_id, do_item_id, original_total_amount,
            so_item_id, product_id, qty_dlv_int, base_uom_id, partner_id,
            salesman_id, brand_id, line_of_business, numerator_rate, denominator_rate,
            original_price, total_amount_idr, total_amount_usd, other_costs, other_costs_idr,
            other_costs_usd, other_costs_curr_code,other_cost_ext_commission, warranty_sell_amount)
    SELECT pSessionId, pTenantId, ' ', A.invoice_id, A.doc_type_id,
        A.curr_code, B.doc_no, B.doc_date, A.ou_id, B.ref_id,
        G.doc_no, G.doc_date, C.ref_id, C.ref_item_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN -1 * SUM(C.ref_item_amount) ELSE SUM(C.ref_item_amount) END,
        F.so_item_id, F.product_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN SUM(-1*(F.qty_int * D.qty_dlv_so)/F.qty_so) ELSE SUM((F.qty_int * D.qty_dlv_so)/F.qty_so) END, F.base_uom_id, G.partner_id,
        G.salesman_id, I.brand_id, J.line_of_business, vOne, vOne,
        vNol, vNol, vNol, vNol, vNol,
        vNol, vCurrCodeIdr, vNol, vNol
    FROM fi_temp_summary_monthly_ar A
    INNER JOIN vw_fi_all_invoice_ar_for_commission H ON A.doc_type_id = H.doc_type_id AND A.invoice_id = H.id
    INNER JOIN sl_invoice B ON H.invoice_ar_id = B.invoice_id AND B.doc_type_id = H.doc_type_id
    INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
    INNER JOIN sl_so_balance_invoice D ON B.tenant_id = D.tenant_id AND B.ou_id = D.ou_id AND B.partner_id = D.partner_id
        AND C.ref_doc_type_id = D.ref_doc_type_id AND C.ref_id = D.ref_id AND C.ref_item_id = D.ref_item_id
        AND C.do_receipt_item_id = D.do_receipt_item_id
    INNER JOIN sl_do_item E ON C.ref_item_id = E.do_item_id
    INNER JOIN sl_so_item F ON E.ref_id = F.so_item_id
    INNER JOIN sl_so G ON F.so_id = G.so_id
    INNER JOIN m_product I ON F.product_id = I.product_id
    INNER JOIN m_partner J ON J.partner_id = G.partner_id
    /*LEFT JOIN sl_so_balance_warranty_invoice L ON B.tenant_id = L.tenant_id AND L.ou_id = B.ou_id AND L.partner_id = B.partner_id
        AND L.ref_doc_type_id = C.ref_doc_type_id AND L.ref_id = C.ref_id AND L.ref_item_id = C.ref_item_id
        AND L.do_receipt_item_id = C.do_receipt_item_id*/
    WHERE A.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeReturnSalesInvoice)
        AND A.date_year_month = vOneMonthBeforeDateTo
        AND (pSalesId = vEmptyId OR G.salesman_id = pSalesId)
        AND (pBrandId = vEmptyId OR I.brand_id = pBrandId)
        AND (pCtgrProductId = vEmptyId OR I.ctgr_product_id = pCtgrProductId)
        AND (pSubCtgrProductId = vEmptyId OR I.sub_ctgr_product_id = pSubCtgrProductId)
        AND (pLineOfBusinessCode = vEmptyString OR J.line_of_business = pLineOfBusinessCode)
        AND SUBSTRING(B.doc_date, 1, 6) = vOneMonthBeforeDateFrom
        AND F.qty_so > 0
        AND A.date_year_month = (
            SELECT MAX(Z.date_year_month)
            FROM fi_temp_summary_monthly_ar Z
            WHERE A.invoice_id = Z.invoice_id
            AND A.doc_type_id = Z.doc_type_id
            AND A.ou_id = Z.ou_id
            AND A.tenant_id = Z.tenant_id
        )
    GROUP BY A.invoice_id, A.doc_type_id, J.line_of_business, A.ou_id, A.curr_code, B.doc_no, B.doc_date, B.ref_id, C.ref_id, C.ref_item_id,
        F.so_item_id, F.product_id, I.product_code, I.product_name, F.base_uom_id, G.partner_id, I.brand_id, G.salesman_id, G.doc_no, G.doc_date;


    WITH vw_fi_all_invoice_ar_for_commission AS (
        SELECT A.invoice_ar_balance_id AS id, A.doc_type_id, A.invoice_ar_id
        FROM fi_invoice_ar_balance A
        INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
        WHERE A.tenant_id = pTenantId
        AND D.ou_bu_id = pOuId

        UNION ALL

        SELECT a.invoice_tax_ar_balance_id AS id, a.doc_type_id, b.invoice_ar_id
        FROM fi_invoice_tax_ar_balance a
        INNER JOIN fi_invoice_ar_balance b on a.invoice_ar_balance_id = b.invoice_ar_balance_id
        INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
        AND A.tenant_id = pTenantId
        AND D.ou_bu_id = pOuId
    )

    --ambil data yg doc_datenya sebulan sebelum dateFrom dan pelunasannya satu bulan sebelum date to
    INSERT INTO tr_data_so_sales_commission_for_igs(
            session_id, tenant_id, date_year_month, invoice_id, doc_type_id,
            inv_curr_code, inv_doc_no, inv_doc_date, ou_id, so_id,
            so_doc_no, so_doc_date, do_id, do_item_id, original_total_amount,
            so_item_id, product_id, qty_dlv_int, base_uom_id, partner_id,
            salesman_id, brand_id, line_of_business, numerator_rate, denominator_rate,
            original_price, total_amount_idr, total_amount_usd, other_costs, other_costs_idr,
            other_costs_usd, other_costs_curr_code,other_cost_ext_commission, warranty_sell_amount)
    SELECT pSessionId, pTenantId, ' ', A.invoice_id, A.doc_type_id,
        A.curr_code, B.doc_no, B.doc_date, A.ou_id, B.ref_id,
        G.doc_no, G.doc_date, C.ref_id, C.ref_item_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN -1 * SUM(L.warranty_sell_amount) ELSE SUM(L.warranty_sell_amount) END,
        F.so_item_id, F.product_id, CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice THEN SUM(-1*(F.qty_int * D.qty_dlv_so)/F.qty_so) ELSE SUM((F.qty_int * D.qty_dlv_so)/F.qty_so) END, F.base_uom_id, G.partner_id,
        G.salesman_id, I.brand_id, J.line_of_business, vOne, vOne,
        vNol, vNol, vNol, vNol, vNol,
        vNol, vCurrCodeIdr, vNol, vNol
    FROM fi_temp_summary_monthly_ar A
    INNER JOIN vw_fi_all_invoice_ar_for_commission H ON A.doc_type_id = H.doc_type_id AND A.invoice_id = H.id
    INNER JOIN sl_invoice B ON H.invoice_ar_id = B.invoice_id AND B.doc_type_id = H.doc_type_id
    INNER JOIN sl_invoice_item C ON B.invoice_id = C.invoice_id
    INNER JOIN sl_so_balance_invoice D ON B.tenant_id = D.tenant_id AND B.ou_id = D.ou_id AND B.partner_id = D.partner_id
        AND C.ref_doc_type_id = D.ref_doc_type_id AND C.ref_id = D.ref_id AND C.ref_item_id = D.ref_item_id
        AND C.do_receipt_item_id = D.do_receipt_item_id
    INNER JOIN sl_do_item E ON C.ref_item_id = E.do_item_id
    INNER JOIN sl_so_item F ON E.ref_id = F.so_item_id
    INNER JOIN sl_so G ON F.so_id = G.so_id
    INNER JOIN m_product I ON F.product_id = I.product_id
    INNER JOIN m_partner J ON J.partner_id = G.partner_id
    INNER JOIN sl_so_balance_warranty_invoice L ON B.tenant_id = L.tenant_id AND L.ou_id = B.ou_id AND L.partner_id = B.partner_id
        AND L.ref_doc_type_id = C.ref_doc_type_id AND L.ref_id = C.ref_id AND L.ref_item_id = C.ref_item_id
        AND L.do_receipt_item_id = C.do_receipt_item_id
    WHERE A.doc_type_id IN (vDocTypeSalesInvoice, vDocTypeReturnSalesInvoice)
        AND A.date_year_month = vOneMonthBeforeDateTo
        AND (pSalesId = vEmptyId OR G.salesman_id = pSalesId)
        AND (pBrandId = vEmptyId OR I.brand_id = pBrandId)
        AND (pCtgrProductId = vEmptyId OR I.ctgr_product_id = pCtgrProductId)
        AND (pSubCtgrProductId = vEmptyId OR I.sub_ctgr_product_id = pSubCtgrProductId)
        AND (pLineOfBusinessCode = vEmptyString OR J.line_of_business = pLineOfBusinessCode)
        AND SUBSTRING(B.doc_date, 1, 6) = vOneMonthBeforeDateFrom
        AND F.qty_so > 0
        AND A.date_year_month = (
            SELECT MAX(Z.date_year_month)
            FROM fi_temp_summary_monthly_ar Z
            WHERE A.invoice_id = Z.invoice_id
            AND A.doc_type_id = Z.doc_type_id
            AND A.ou_id = Z.ou_id
            AND A.tenant_id = Z.tenant_id
        )
    GROUP BY A.invoice_id, A.doc_type_id, J.line_of_business, A.ou_id, A.curr_code, B.doc_no, B.doc_date, B.ref_id, C.ref_id, C.ref_item_id,
        F.so_item_id, F.product_id, I.product_code, I.product_name, F.base_uom_id, G.partner_id, I.brand_id, G.salesman_id, G.doc_no, G.doc_date;


    /*
     * Adrian, Mar 7 2017
     * Mennganti LEFT JOIN ke sl_so_balance_warranty_invoice menjadi update
     */
    /*WITH vw_warranty_sell_amount AS (
        SELECT A.invoice_id, B.do_item_id,
            CASE WHEN A.doc_type_id = vDocTypeReturnSalesInvoice
                THEN -1 * SUM(L.warranty_sell_amount)
            ELSE
                SUM(L.warranty_sell_amount)
            END AS sum_warranty_sell_amount
        FROM tr_data_so_sales_commission_for_igs A
        INNER JOIN sl_do_item B ON B.do_item_id = A.do_item_id
        INNER JOIN sl_invoice_item C ON C.ref_item_id = B.do_item_id
        INNER JOIN sl_invoice D ON D.invoice_id = C.invoice_id
        INNER JOIN sl_so_balance_warranty_invoice L ON L.ou_id = D.ou_id AND L.partner_id = D.partner_id
            AND L.ref_doc_type_id = C.ref_doc_type_id AND L.ref_id = C.ref_id AND L.ref_item_id = C.ref_item_id
            AND L.do_receipt_item_id = C.do_receipt_item_id
        WHERE A.session_id = pSessionId
        GROUP BY A.invoice_id, A.doc_type_id, B.do_item_id
    )
    UPDATE tr_data_so_sales_commission_for_igs A
    SET warranty_sell_amount = B.sum_warranty_sell_amount
    FROM vw_warranty_sell_amount B
    WHERE A.session_id = pSessionId
        AND A.invoice_id = B.invoice_id
        AND A.do_item_id = B.do_item_id;*/



    /* update nilai original_price and original total_amount */
    UPDATE tr_data_so_sales_commission_for_igs A
    SET original_price = A.original_total_amount / A.qty_dlv_int
    WHERE A.qty_dlv_int <> 0;

    /* update table temporary for numerator_rate, denominator_rate */
    UPDATE tr_data_so_sales_commission_for_igs A
    SET numerator_rate = B.amount_to,
        denominator_rate = B.amount_from
    FROM m_exchange_rate B
    WHERE A.session_id = pSessionId
        AND A.inv_doc_date = B.date_from
        AND B.type_exchange_rate = vTypeExcRateCom;

    /* others cost */
    INSERT INTO tr_data_costs_sales_commission_for_igs(
            session_id, tenant_id, ou_id, segment_id, so_id, gl_curr_code, gl_amount,
            coa_id)
    SELECT pSessionId, pTenantId, A.ou_bu_id, B.segmen_id, D.so_id, B.gl_curr_code, B.gl_amount,
        B.coa_id
    FROM gl_journal_trx A
        INNER JOIN vw_gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN m_segment_coa C ON B.segmen_id = C.segment_coa_id
        INNER JOIN tr_data_so_sales_commission_for_igs D ON C.segment_coa_code = D.so_doc_no
    WHERE A.status_doc = vStatusDocRelease
        AND (C.group_segment = vGroupSegmentOthers OR C.group_segment = vGroupSegmentSo)
    GROUP BY  A.ou_bu_id, B.segmen_id, D.so_id, B.gl_curr_code, B.gl_amount, B.coa_id
    UNION ALL
    SELECT pSessionId, pTenantId, A.ou_bu_id, B.segmen_id, D.so_id, B.gl_curr_code, ROUND(B.amount * B.numerator_rate / B.denominator_rate, vRoundingDigitUSD),
        B.coa_id
    FROM gl_journal_trx A
        INNER JOIN vw_gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
        INNER JOIN m_segment_coa C ON B.segmen_id = C.segment_coa_id
        INNER JOIN tr_data_so_sales_commission_for_igs D ON C.segment_coa_code = D.so_doc_no
    WHERE A.status_doc = vStatusDocDraft
        AND (C.group_segment = vGroupSegmentOthers OR C.group_segment = vGroupSegmentSo)
    GROUP BY A.ou_bu_id, B.segmen_id, D.so_id, B.gl_curr_code, B.amount, B.numerator_rate, B.denominator_rate, B.coa_id;


    /* update table cost tr_data_so_sales_for_commission non ext commission */
    UPDATE tr_data_so_sales_commission_for_igs B
    SET other_costs = (SELECT CASE WHEN B.doc_type_id = vDocTypeReturnSalesInvoice THEN 0 ELSE COALESCE(SUM(A.gl_amount),0) END
    FROM tr_data_costs_sales_commission_for_igs A WHERE A.so_id = B.so_id AND A.coa_id <> vCoaIdExtCommission AND A.session_id = B.session_id)
    WHERE B.session_id = pSessionId
        AND B.so_item_id = (SELECT MIN(K.so_item_id) FROM sl_so_item K WHERE K.so_id = B.so_id AND K.qty_so > 0);

    /* update table cost tr_data_so_sales_for_commission ext commission */
    UPDATE tr_data_so_sales_commission_for_igs B
    SET other_cost_ext_commission = (SELECT COALESCE(SUM(A.gl_amount),0) FROM tr_data_costs_sales_commission_for_igs A WHERE A.so_id = B.so_id AND A.coa_id = vCoaIdExtCommission AND A.session_id = B.session_id)
    WHERE B.session_id = pSessionId
        AND B.so_item_id = (SELECT MIN(K.so_item_id) FROM sl_so_item K WHERE K.so_id = B.so_id AND K.qty_so > 0);

    --update total_amount_usd
    UPDATE tr_data_so_sales_commission_for_igs
    SET total_amount_usd =
        CASE WHEN inv_curr_code = 'IDR'
                THEN  (original_total_amount * denominator_rate)/numerator_rate
            WHEN inv_curr_code = 'USD'
                THEN original_total_amount
            ELSE vNol
        END
    WHERE qty_dlv_int <> 0;

    --update total_amount_idr
    UPDATE tr_data_so_sales_commission_for_igs
    SET total_amount_idr = CASE WHEN inv_curr_code = 'USD'
                THEN (original_total_amount * numerator_rate)/denominator_rate
             WHEN inv_curr_code = 'IDR'
                THEN original_total_amount
            ELSE vNol
        END
    WHERE qty_dlv_int <> 0;

    --update other_costs_usd
    UPDATE tr_data_so_sales_commission_for_igs
    SET other_costs_usd = (other_costs * denominator_rate)/numerator_rate
    WHERE qty_dlv_int <> 0;

    -- update line_of_business yang kosong menjadi DEFAULT
    UPDATE tr_data_so_sales_commission_for_igs
    SET line_of_business = vDefault
    WHERE line_of_business IN (vEmptyString,vSpace);

    /* update bulan pelunasan */
    UPDATE tr_data_so_sales_commission_for_igs B
    SET date_year_month = A.date_year_month
    FROM fi_temp_summary_monthly_ar A
    WHERE A.balance_amount = A.payment_amount
    AND B.invoice_id = A.invoice_id
    AND B.doc_type_id = A.doc_type_id
    AND B.session_id = pSessionId
    AND B.tenant_id = pTenantId;

    Open pRefHeader FOR
    SELECT pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name, vDateFrom AS date_from, vDateTo AS date_to, pDatetime AS datetime, f_get_username(pUserId) AS username;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT A.line_of_business, f_get_partner_name(A.salesman_id) AS sales_name, f_get_partner_code(A.partner_id) AS partner_code, f_get_partner_name(A.partner_id) AS partner_name,
        A.so_doc_date AS so_date, A.so_doc_no AS so_no, A.doc_type_id, A.inv_doc_date AS inv_date, A.inv_doc_no AS inv_no, f_get_brand_name(A.brand_id) as brand_name, f_get_product_code(A.product_id) AS product_code,
        f_get_product_name(A.product_id) AS product_name, A.qty_dlv_int AS qty, A.inv_curr_code, SUM(A.original_total_amount / A.qty_dlv_int) AS original_price,
        A.denominator_rate/A.numerator_rate AS rate_to_usd, A.numerator_rate AS rate_to_idr, original_total_amount,
        A.total_amount_usd, A.total_amount_idr, A.other_costs_curr_code, A.other_costs, A.other_costs_usd, A.other_costs_idr, A.date_year_month,
        A.warranty_sell_amount
    FROM tr_data_so_sales_commission_for_igs A
    LEFT OUTER JOIN sl_so_external_commission B ON A.so_id = B.so_id AND A.so_item_id = (SELECT MIN(K.so_item_id) FROM sl_so_item K WHERE K.so_id = B.so_id AND K.qty_so > 0)
    WHERE A.session_id = pSessionId
        AND A.qty_dlv_int <> 0
    GROUP BY A.invoice_id, A.do_item_id, A.line_of_business, A.salesman_id,
    ORDER BY line_of_business, sales_name, partner_name, A.so_doc_no, A.doc_type_id, product_code;
    RETURN NEXT pRefDetail;


    DELETE FROM tr_data_so_sales_commission_for_igs WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/