[BACKUP] r_accounts_payable

--Modified by Adrian, Jul 5, 2017, memperbaiki ouId dan tenantId yang dipanteks 10

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

    pPeriodFrom         ALIAS FOR $5;
    pPeriodTo           ALIAS FOR $6;
    pUserId             ALIAS FOR $7;
    pRoleId             ALIAS FOR $8;
    pDatetime           ALIAS FOR $9;

    vCurrCode           character varying := '';
    vPrevPeriod         character varying := '';
    vEmptyId            bigint := -99;

BEGIN

--kurangin 1 bulan
SELECT TO_CHAR(TO_DATE(pPeriodFrom,'YYYYMM') - interval '1 Month','YYYYMM') INTO vPrevPeriod;

--1. ambil valuta pembukuan (dari system config) simpan ke variable
    SELECT B.parameter_value INTO vCurrCode
    from t_parameter A
    JOIN t_system_config B ON A.parameter_id = B.parameter_id AND B.tenant_id = 10
    WHERE A.parameter_code = 'ValutaBuku';


--2. ambil semua dokumen AP dari hasil tutup bulan (periode awal - 1 bulan) sebagai saldo awal
--a. ambil dari in_summary_monthly_ap yang balance_amount - payment_amount > 0
--b. simpan ke table temp tr_trx_ap dengan field: session_id, tenant_id, ou_id, flg_data, partner_id,
--curr_code, doc_type_id, doc_id, doc_date, doc_no, year_month_date, balance_amount, accumulated_amount, type_data)
--c. type data diisi 1 jika balance_amount > 0, diisi -1 jika balance amount < 0
--d. balance amount selalu diisi + walaupun data aslinya bernilai negatif
--e. flg_data diisi 'AAAA' sebagai tanda untuk saldo awal

    INSERT INTO tr_trx_ap (
    session_id, tenant_id, ou_id, flg_data, partner_id,
    curr_code, doc_type_id, doc_id, doc_date, doc_no, balance_amount, accumulated_amount, type_data)

    SELECT pSessionId, A.tenant_id, A.ou_id, 'AAAA', B.partner_id,
    A.curr_code, A.doc_type_id, B.id, B.doc_date, B.doc_no,
    (CASE WHEN (A.balance_amount - A.payment_amount) < 0 THEN (A.balance_amount - A.payment_amount)*-1 ELSE
    (A.balance_amount - A.payment_amount) END) AS balance_amount,
    0 AS accumulated_amount,
    (CASE WHEN A.balance_amount < 0 THEN -1 ELSE 1 END) AS type_data
    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
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND (A.balance_amount - A.payment_amount) > 0
    AND A.date_year_month = vPrevPeriod;

--3. ambil semua dokumen AP dari bulan berjalan (periode awal - periode akhir)
--   a. ambil dari fi_invoice_ap_balance simpan ke table temp tr_trx_ap
--   b. type data diisi 1 jika amount > 0, diisi -1 jika amount < 0
--   c. balance amount selalu diisi + walaupun data aslinya bernilai negatif
--   d. flg_data diisi 'BBBB' untuk amount +
--   e. flg_data diisi 'CCCC' untuk amount -

    INSERT INTO tr_trx_ap (
    session_id, tenant_id, ou_id, flg_data, partner_id,
    curr_code, doc_type_id, doc_id, doc_date, doc_no, balance_amount, accumulated_amount, type_data)
    SELECT pSessionId, A.tenant_id, A.ou_id,
    (CASE WHEN (A.amount) < 0 THEN 'CCCC' ELSE 'BBBB' END) AS flg_data,
    A.partner_id, A.curr_code, A.doc_type_id, A.invoice_ap_balance_id, A.doc_date, A.doc_no,
    (CASE WHEN (A.amount) < 0 THEN (A.amount)*-1 ELSE (A.amount) END) AS balance_amount,
    0 AS accumulated_amount,
    (CASE WHEN (A.amount) < 0 THEN -1 ELSE 1 END) AS type_data
    FROM fi_invoice_ap_balance A
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND SUBSTR(A.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo;

    INSERT INTO tr_trx_ap (
    session_id, tenant_id, ou_id, flg_data, partner_id,
    curr_code, doc_type_id, doc_id, doc_date, doc_no, balance_amount, accumulated_amount, type_data)
    SELECT pSessionId, A.tenant_id, A.ou_id,
        (CASE WHEN (A.gov_tax_amount) < 0 THEN 'CCCC' ELSE 'BBBB' END) AS flg_data,
        A.partner_id, A.tax_curr_code, A.doc_type_id, A.invoice_tax_ap_balance_id, A.tax_date, A.tax_no,
        (CASE WHEN (A.gov_tax_amount) < 0 THEN (A.gov_tax_amount)*-1 ELSE
            (A.gov_tax_amount) END) AS balance_amount, 0 AS accumulated_amount,
        (CASE WHEN (A.gov_tax_amount) < 0 THEN -1 ELSE 1 END) AS type_data
    FROM fi_invoice_tax_ap_balance A
    INNER JOIN fi_invoice_ap_balance B ON A.invoice_ap_balance_id = B.invoice_ap_balance_id
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND SUBSTR(B.doc_date, 1, 6) BETWEEN pPeriodFrom AND pPeriodTo;


--4. ambil dokumen pelunasan
--a. cek report ap card line 216-297
--b. copy sama persis hanya waktu lakukan update alokasi pelunasan pakai table temp yang dari step nomor 2 dan 3 (tr_trx_ap)
    INSERT INTO tr_doc_allocation_ap
    (session_id, tenant_id, ou_id,
    doc_type_id, doc_id, doc_date, doc_no,
    curr_code, amount)
    SELECT pSessionId, A.tenant_id, A.ou_id,
    B.debit_doc_type_id, B.debit_id, fi_get_date_invoice_ap(B.debit_doc_type_id, B.debit_id), fi_get_no_invoice_ap(B.debit_doc_type_id, B.debit_id),
    B.debit_curr_code, SUM(B.debit_amount)
    FROM fi_payment_order A, fi_payment_order_alloc_balance B
    WHERE A.tenant_id = pTenantId
    AND A.payment_order_id = B.payment_order_id
    AND A.ou_id = 10
    AND SUBSTR(A.doc_date, 1, 6) BETWEEN
    pPeriodFrom AND pPeriodTo
    AND flg_alloc = 'A'
    GROUP BY A.tenant_id, A.ou_id, B.debit_doc_type_id, B.debit_id, B.debit_curr_code;

    INSERT INTO tr_doc_allocation_ap
    (session_id, tenant_id, ou_id,
    doc_type_id, doc_id, doc_date, doc_no,
    curr_code, amount)
    SELECT pSessionId, A.tenant_id, A.ou_id,
    B.credit_doc_type_id, B.credit_id, fi_get_date_invoice_ap(B.credit_doc_type_id, B.credit_id), fi_get_no_invoice_ap(B.credit_doc_type_id, B.credit_id),
    B.credit_curr_code, SUM(B.credit_amount)
    FROM fi_payment_order A, fi_payment_order_alloc_balance B
    WHERE A.tenant_id = pTenantId
    AND A.payment_order_id = B.payment_order_id
    AND A.ou_id = 10
    AND SUBSTR(A.doc_date, 1, 6) BETWEEN
    pPeriodFrom AND pPeriodTo
    AND flg_alloc = 'A'
    GROUP BY A.tenant_id, A.ou_id, B.credit_doc_type_id, B.credit_id, B.credit_curr_code;

    INSERT INTO tr_doc_allocation_ap
    (session_id, tenant_id, ou_id,
    doc_type_id, doc_id, doc_date, doc_no,
    curr_code, amount)
    SELECT pSessionId, B.tenant_id, B.ou_id,
    B.credit_doc_type_id, B.credit_id, fi_get_date_invoice_ap(B.credit_doc_type_id, B.credit_id), fi_get_no_invoice_ap(B.credit_doc_type_id, B.credit_id),
    B.credit_curr_code, SUM(B.credit_amount)
    FROM fi_payment_order_alloc_balance B
    WHERE B.tenant_id = pTenantId
    AND B.ou_id = 10
    AND SUBSTR(B.ref_doc_date, 1, 6) BETWEEN
    pPeriodFrom AND pPeriodTo
    AND B.flg_alloc = 'C'
    AND B.ref_alloc_id <> -99
    GROUP BY B.tenant_id, B.ou_id, B.credit_doc_type_id, B.credit_id, B.credit_curr_code;

    /*
     * update akumulasi nilai untuk yg sudah dialokasikan di atas
     * untuk hitung sisa saldo nya
     */


    UPDATE tr_trx_ap
    SET accumulated_amount = accumulated_amount + (
                                SELECT SUM(A.amount)
                                FROM tr_doc_allocation_ap A
                                WHERE tr_trx_ap.tenant_id = A.tenant_id
                                AND tr_trx_ap.session_id = A.session_id
                                AND tr_trx_ap.ou_id = A.ou_id
                                AND tr_trx_ap.doc_type_id = A.doc_type_id
                                AND tr_trx_ap.doc_no = A.doc_no
                                AND tr_trx_ap.doc_date = A.doc_date
                                AND tr_trx_ap.curr_code = A.curr_code
                                AND tr_trx_ap.doc_id = A.doc_id
                                AND tr_trx_ap.session_id = pSessionId
                                AND A.session_id = pSessionId
                                GROUP BY A.session_id, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date
                            )
    WHERE EXISTS ( SELECT 1 FROM tr_doc_allocation_ap A
            WHERE tr_trx_ap.tenant_id = A.tenant_id AND
                tr_trx_ap.session_id = A.session_id AND
                tr_trx_ap.ou_id = A.ou_id AND
                tr_trx_ap.doc_type_id = A.doc_type_id AND
                tr_trx_ap.doc_date = A.doc_date AND
                tr_trx_ap.doc_no = A.doc_no AND
                tr_trx_ap.curr_code = A.curr_code AND
                tr_trx_ap.doc_id = A.doc_id AND
                tr_trx_ap.session_id = pSessionId
                AND
                A.session_id = pSessionId
                ) AND
        tr_trx_ap.session_id = pSessionId;

--5. hitung saldo akhirnya
--   a. insert ke tr_trx_ap
--   b. ambil dari table tr_trx_ap yang flag nya 'AAAA', 'BBBB', 'CCCC',
--   c. flg data diisi dengan 'ZZZZ'
--   d. type_data mengikuti data aslinya


    INSERT INTO tr_trx_ap (
    session_id, tenant_id, ou_id, flg_data, partner_id,
    curr_code, doc_type_id, doc_id, doc_date, doc_no, balance_amount, accumulated_amount, type_data)
    SELECT A.session_id, A.tenant_id, A.ou_id, 'ZZZZ', A.partner_id,
    A.curr_code, A.doc_type_id, A.doc_id, A.doc_date, A.doc_no, A.balance_amount - A.accumulated_amount, 0, A.type_data
    FROM tr_trx_ap A
    WHERE (A.balance_amount - A.accumulated_amount) > 0
    AND (A.flg_data = 'AAAA' OR A.flg_data = 'BBBB' OR A.flg_data = 'CCCC');




--6. ambil dokumen pelunasan (untuk ditampilkan di hasil akhir report)
--   a. cek report kartu ap line 302-364
--   b. logic sama persis cuman insert ke table temp tr_trx_ap
--   c. flg_data diisi 'CCCC', type_data diisi -1

    INSERT INTO tr_trx_ap (
    session_id, tenant_id, ou_id, flg_data, partner_id,
    curr_code, doc_type_id, doc_id, doc_date, doc_no, balance_amount, accumulated_amount, type_data)
    SELECT pSessionId, A.tenant_id, A.ou_id, 'CCCC',
    B.partner_id, A.debit_curr_code,
    A.debit_doc_type_id, A.debit_id, fi_get_date_invoice_ap(A.debit_doc_type_id, A.debit_id), fi_get_no_invoice_ap(A.debit_doc_type_id, A.debit_id),
    SUM(A.debit_amount), SUM(A.debit_amount), -1
    FROM fi_payment_order B, fi_payment_order_alloc_balance A
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND A.payment_order_id = B.payment_order_id
    AND SUBSTR(B.doc_date, 1, 6) BETWEEN
    pPeriodFrom AND pPeriodTo
    AND A.flg_alloc = 'A'
    GROUP BY A.tenant_id, B.partner_id, A.debit_curr_code, A.ou_id, A.debit_doc_type_id, A.debit_id;

    INSERT INTO tr_trx_ap (
    session_id, tenant_id, ou_id, flg_data, partner_id,
    curr_code, doc_type_id, doc_id, doc_date, doc_no, balance_amount, accumulated_amount, type_data)
    SELECT pSessionId, A.tenant_id, A.ou_id, 'CCCC', B.partner_id, A.credit_curr_code,
    A.credit_doc_type_id, A.credit_id, fi_get_date_invoice_ap(A.credit_doc_type_id, A.credit_id), fi_get_no_invoice_ap(A.credit_doc_type_id, A.credit_id),
    SUM(A.credit_amount), SUM(A.credit_amount), -1
    FROM fi_payment_order B, fi_payment_order_alloc_balance A
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND A.payment_order_id = B.payment_order_id
    AND SUBSTR(B.doc_date, 1, 6)
    BETWEEN pPeriodFrom AND pPeriodTo
    AND A.flg_alloc = 'A'
    GROUP BY A.tenant_id, B.partner_id, A.credit_curr_code, A.ou_id, A.credit_doc_type_id, A.credit_id;

    INSERT INTO tr_trx_ap (
    session_id, tenant_id, ou_id, flg_data, partner_id,
    curr_code, doc_type_id, doc_id, doc_date, doc_no, balance_amount, accumulated_amount, type_data)
    SELECT pSessionId, B.tenant_id, B.ou_id, 'CCCC', C.partner_id,
    B.credit_curr_code, B.credit_doc_type_id,
    B.credit_id, fi_get_date_invoice_ap(B.credit_doc_type_id, B.credit_id), fi_get_no_invoice_ap(B.credit_doc_type_id, B.credit_id),
    SUM(B.credit_amount), SUM(B.credit_amount), -1
    FROM fi_payment_order_alloc_balance B
    INNER JOIN vw_fi_all_invoice_ap C ON C.doc_type_id = B.credit_doc_type_id AND C.id = B.credit_id
    WHERE B.tenant_id = pTenantId
    AND B.ou_id = 10
    AND SUBSTR(B.ref_doc_date, 1, 6) BETWEEN
    pPeriodFrom AND pPeriodTo
    AND B.flg_alloc = 'C'
    AND B.ref_alloc_id <> -99
    GROUP BY B.tenant_id, B.ou_id, C.partner_id,
    B.credit_curr_code,  B.credit_doc_type_id,
    B.credit_id;


-- 7. siapkan hasil rekapnya
--   a. buat table temp tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance, plus_balance, book_plus_balance, minus_balance, book_minus_balance, book_ending_balance, ending_balance, eom_balance)
--   b. insert ke table temp tr_summary_ap untuk field beginning balance dan book_beginning_balance yg valuta <> valuta buku (di kurs kan dengan kurs komersial) dari tr_trx_ap yg flg = 'AAAA'

    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, book_ending_balance, ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, SUM(A.balance_amount * A.type_data),
    SUM(A.balance_amount * f_commercial_rate(A.tenant_id, A.doc_date, A.curr_code, vCurrCode)* A.type_data),
    0, 0, 0, 0, 0, 0, 0
    FROM tr_trx_ap A
    WHERE A.curr_code <> vCurrCode
    AND flg_data = 'AAAA'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   c. insert ke table temp tr_summary_ap untuk field beginning balance dan book_beginning_balance yg valuta = valuta buku dari tr_trx_ap yg flg = 'AAAA'

    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, book_ending_balance, ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, SUM(A.balance_amount * A.type_data), SUM(A.balance_amount * A.type_data),
    0, 0, 0, 0, 0, 0, 0
    FROM tr_trx_ap A
    WHERE A.curr_code = vCurrCode
    AND flg_data = 'AAAA'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   d. insert ke table temp tr_summary_ap untuk field plus balance dan book_plus_balance yg valuta <> valuta buku (di kurs kan dengan kurs komersial) dari tr_trx_ap yg flg = 'BBBB'

    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, book_ending_balance, ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, 0, 0,
    SUM(A.balance_amount),
    SUM(A.balance_amount * f_commercial_rate(A.tenant_id, A.doc_date, A.curr_code , vCurrCode)),
    0, 0, 0, 0, 0
    FROM tr_trx_ap A
    WHERE A.curr_code <> vCurrCode
    AND flg_data = 'BBBB'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   e. insert ke table temp tr_summary_ap untuk field plus balance dan book_plus_balance yg valuta = valuta buku dari tr_trx_ap yg flg = 'BBBB'

    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, book_ending_balance, ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, 0, 0,
    SUM(A.balance_amount),
    SUM(A.balance_amount),
    0, 0, 0, 0, 0
    FROM tr_trx_ap A
    WHERE A.curr_code = vCurrCode
    AND flg_data = 'BBBB'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   f. insert ke table temp tr_summary_ap untuk field minus balance dan book_minus_balance yg valuta <> valuta buku (di kurs kan dengan kurs komersial) dari tr_trx_ap yg flg = 'CCCC'
    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, book_ending_balance, ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, 0, 0,
    0, 0,
    SUM(A.balance_amount),
    SUM(A.balance_amount * f_commercial_rate(A.tenant_id, A.doc_date, A.curr_code, vCurrCode)),
    0, 0, 0
    FROM tr_trx_ap A
    WHERE A.curr_code <> vCurrCode
    AND flg_data = 'CCCC'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   g. insert ke table temp tr_summary_ap untuk field minus balance dan book_minus_balance yg valuta = valuta buku dari tr_trx_ap yg flg = 'CCCC'
    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, book_ending_balance, ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, 0, 0,
    0, 0,
    SUM(A.balance_amount),
    SUM(A.balance_amount),
    0, 0, 0
    FROM tr_trx_ap A
    WHERE A.curr_code = vCurrCode
    AND flg_data = 'CCCC'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   h. insert ke table temp tr_summary_ap untuk field ending balance dan book_ending_balance, dan eom_balance yg valuta <> valuta buku (di kurs kan dengan kurs komersial, untuk eom kurskan dengan kurs end of month) dari tr_trx_ap yg flg = 'ZZZZ' (perhatikan type nya waktu SUM)
    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, ending_balance, book_ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, 0, 0,
    0, 0, 0, 0,
    SUM(A.balance_amount * A.type_data),
    SUM(A.balance_amount * f_commercial_rate(A.tenant_id, A.doc_date, A.curr_code, vCurrCode) * A.type_data),
    SUM(A.balance_amount * f_eom_rate(A.tenant_id, SUBSTR(A.doc_date, 1, 6), A.curr_code, vCurrCode) * A.type_data)
    FROM tr_trx_ap A
    WHERE A.curr_code <> vCurrCode
    AND flg_data = 'ZZZZ'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   i. insert ke table temp tr_summary_ap untuk field ending balance dan book_ending_balance, dan eom_balance yg valuta = valuta buku dari tr_trx_ap yg flg = 'ZZZZ' (perhatikan type nya waktu SUM)

    INSERT INTO tr_summary_ap (session_id, tenant_id, ou_id, curr_code, partner_id, beginning_balance, book_beginning_balance,
    plus_balance, book_plus_balance, minus_balance, book_minus_balance, ending_balance, book_ending_balance, eom_balance)
    SELECT A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id, 0, 0,
    0, 0, 0, 0,
    SUM(A.balance_amount* A.type_data),
    SUM(A.balance_amount * A.type_data),
    SUM(A.balance_amount * A.type_data)
    FROM tr_trx_ap A
    WHERE A.curr_code = vCurrCode
    AND flg_data = 'ZZZZ'
    AND A.session_id = pSessionId
    GROUP BY A.session_id, A.tenant_id, A.ou_id, A.curr_code, A.partner_id;

--   j. kembalikan data sesuai yang diharapkan
IF pCurrCode <> '-99' THEN
        DELETE FROM tr_summary_ap WHERE session_id = pSessionId AND curr_code <> pCurrCode;
    END IF;

Open pRefHeader FOR
    SELECT f_get_ou_name(pOuId) AS ou_name,
    f_get_username(pUserId) AS username,
    pPeriodFrom AS start_month_year,
    pPeriodTo AS end_month_year,
    (CASE WHEN pCurrCode <> '-99' THEN pCurrCode ELSE 'ALL' END) AS curr_filter,
    pDatetime AS datetime;
RETURN NEXT pRefHeader;

Open pRefDetail FOR
    SELECT A.curr_code, f_get_partner_name(A.partner_id) AS partner_name,
    SUM(A.beginning_balance) AS beginning_balance_original, SUM(A.book_beginning_balance) AS beginning_balance_gl,
    SUM(A.plus_balance) AS credit_ap_original, SUM(A.book_plus_balance) AS credit_ap_gl,
    SUM(A.minus_balance) AS debit_ap_original, SUM(A.book_minus_balance) AS debit_ap_gl,
    SUM(A.ending_balance) AS end_balance_original, SUM(A.book_ending_balance) AS end_balance_gl,
    SUM(A.eom_balance) AS gl_amount_month_end, (SUM(A.eom_balance)-SUM(A.book_ending_balance)) AS gl_amount_curr_difference
    from tr_summary_ap A
    WHERE A.session_id = pSessionId
    GROUP BY A.curr_code, partner_name
    ORDER BY A.curr_code, partner_name;

RETURN NEXT pRefDetail;

DELETE FROM tr_summary_ap WHERE session_id = pSessionId;
DELETE FROM tr_trx_ap WHERE session_id = pSessionId;
DELETE FROM tr_doc_allocation_ap WHERE session_id = pSessionId;

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