BACKUP SUMMARY MONTHLY FIN AP

CREATE OR REPLACE FUNCTION r_summary_monthly_finance_ap(character varying, bigint, bigint, character varying, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
        pRefHeader                      REFCURSOR := 'refHeader';
        pRefDetail                      REFCURSOR := 'refDetail';
        pSessionId                      ALIAS FOR $1;
        pTenantId                       ALIAS FOR $2;
        pUserId                         ALIAS FOR $3;
        pDatetime                       ALIAS FOR $4;

        pOuId                           ALIAS FOR $5;
        pYearMonth                      ALIAS FOR $6;
        pOutstandingInvoice             ALIAS FOR $7;

        vYEs                            character varying := 'Y';
        vOutstanding                    text := ' ';
        vStatusOutstanding              character varying := 'Outstanding';
        vStatusPaid                     character varying := 'Paid';
        vTypeEom                        character varying := 'EOM';
        vGrandTotal                     numeric;
        vExchangeKurs                   character varying;
        vChangeExchangeKurs             character varying;

BEGIN
    -- ini untuk mengambil akhir bulan dari period yang mau di print, digunakan untuuk mengambil kurs akhir bulan di exchange rate
    SELECT (date_trunc('MONTH', to_date(pYearMonth,'YYYYMM')) + INTERVAL '1 MONTH - 1 day')::date INTO vExchangeKurs;

    vChangeExchangeKurs = (to_char(to_date(vExchangeKurs, 'YYYY-MM-DD'), 'YYYYMMDD'));

    IF pOutstandingInvoice <> vYEs THEN -- kalau bukan Y, maka
        vOutstanding = '  '; -- flg nya N
        ELSE
        vOutstanding = ' AND (a.balance_amount - a.payment_amount) > 0 '; -- flg nya Y
    END IF;

    -- ini untuk mendapatkan total dari semua GL nya
    SELECT ROUND(SUM((COALESCE(c.amount_to, 1) * (a.balance_amount - a.payment_amount) / COALESCE(c.amount_from, 1))), 2) INTO vGrandTotal
    FROM fi_summary_monthly_ap a
    INNER JOIN vw_fi_all_invoice_ap b ON a.doc_type_id = b.doc_type_id AND a.invoice_id = b.id
    LEFT JOIN m_exchange_rate c ON (vChangeExchangeKurs BETWEEN c.date_from AND c.date_to)
        AND a.curr_code = c.curr_code_from
        AND c.type_exchange_rate = vTypeEom
    WHERE a.ou_id = pOuId AND a.date_year_month = pYearMonth;


    Open pRefHeader FOR
        SELECT pOuId AS ou_id, f_get_ou_name(pOuId) as ou_name, pYearMonth AS year_month, pDatetime as datetime,
            f_get_username(pUserId) AS username, vGrandTotal AS grand_total,
            CASE WHEN pOutstandingInvoice = vYEs THEN 'Outstanding Invoice Only' ELSE 'All Invoice' END AS invoice;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    EXECUTE '
        SELECT f_get_doc_desc(a.doc_type_id) AS doc_desc, b.doc_no, b.doc_date, a.curr_code, COALESCE(c.amount_to, 1) AS amount_to,
            COALESCE(a.balance_amount, 0) AS balance_amount, COALESCE(a.payment_amount, 0) AS payment_amount,
            (a.balance_amount - a.payment_amount) AS outstanding_balance,
            CASE WHEN (a.balance_amount - a.payment_amount) > 0  THEN $1 ELSE $2 END AS status,
            (COALESCE(c.amount_to, 1) * (a.balance_amount - a.payment_amount) / COALESCE(c.amount_from, 1)) AS gl_amount
        FROM fi_summary_monthly_ap a
        INNER JOIN vw_fi_all_invoice_ap b ON a.doc_type_id = b.doc_type_id AND a.invoice_id = b.id
        LEFT JOIN m_exchange_rate c ON ( '''|| vChangeExchangeKurs ||''' BETWEEN c.date_from AND c.date_to)
            AND a.curr_code = c.curr_code_from
            AND c.type_exchange_rate = ''' || vTypeEom || '''
        WHERE a.ou_id = $3 AND a.date_year_month = $4 ' || vOutstanding
        USING vStatusOutstanding, vStatusPaid, pOuId, pYearMonth;
    RETURN NEXT pRefDetail;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /