SUMMARY MONTHLY 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';
        vTypeCom                        character varying := 'COM';
        vGrandTotal                     numeric;
        vExchangeKurs                   character varying;
        vChangeExchangeKurs             character varying;

        vDocTypePrepaymentBalance           bigint := 212;
        vDocTypePrepaymentInvoiceBalance    bigint := 214;

BEGIN

    DELETE FROM tt_summary_monthly WHERE session_id = pSessionId;

    -- 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;

    -- Adrian, Mar 15, 2017, insert ke table temporary untuk type exchange rate Eom
    EXECUTE '
    INSERT INTO tt_summary_monthly
        (session_id, tenant_id, ou_id, year_month,
        doc_desc, doc_no, doc_date,
        curr_code, amount_to,
        balance_amount, payment_amount,
        outstanding_balance,
        status,
        gl_amount)
    SELECT $7, $8, $3, $4,
        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 || '
        AND (a.doc_type_id <> $5 AND a.doc_type_id <> $6 ) '
    USING vStatusOutstanding, vStatusPaid, pOuId, pYearMonth, vDocTypePrepaymentBalance, vDocTypePrepaymentInvoiceBalance, pSessionId, pTenantId;

    -- Adrian, Mar 15, 2017, insert ke table temporary untuk type exchange rate Com
    EXECUTE '
    INSERT INTO tt_summary_monthly
        (session_id, tenant_id, ou_id, year_month,
        doc_desc, doc_no, doc_date,
        curr_code, amount_to,
        balance_amount, payment_amount,
        outstanding_balance,
        status,
        gl_amount)
    SELECT $7, $8, $3, $4,
        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 ( b.doc_date BETWEEN c.date_from AND c.date_to)
        AND a.curr_code = c.curr_code_from
        AND c.type_exchange_rate = ''' || vTypeCom || '''
    WHERE a.ou_id = $3 AND a.date_year_month = $4 ' || vOutstanding || '
        AND (a.doc_type_id = $5 OR a.doc_type_id = $6 ) '
    USING vStatusOutstanding, vStatusPaid, pOuId, pYearMonth, vDocTypePrepaymentBalance, vDocTypePrepaymentInvoiceBalance, pSessionId, pTenantId;

    -- 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;*/

    SELECT ROUND(SUM(gl_amount), 2) INTO vGrandTotal
    FROM tt_summary_monthly
    WHERE session_id = pSessionId;


    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;*/
        SELECT doc_desc, doc_no, doc_date,
            curr_code, amount_to,
            balance_amount, payment_amount,
            outstanding_balance,
            status,
            gl_amount
        FROM tt_summary_monthly
        WHERE session_id = pSessionId;
    RETURN NEXT pRefDetail;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /