CREATE OR REPLACE FUNCTION r_report_raw_data_opname_nota(CHARACTER varying, bigint, 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;
    pPartnerId              ALIAS FOR $4;
    pYearMonth              ALIAS FOR $5;

    vOuBuId             bigint;
    vEmptyId            bigint := -99;
    vPrevYearMonth      CHARACTER VARYING(6);
    vLastArLedgerPeriod CHARACTER VARYING(6);
    vLastBulanSaldoAwal CHARACTER VARYING(6);
    vDateParameter CHARACTER VARYING(6); -- hanya dipakai jika period yang user pilih lebih besar dari tgl tutup bulan terakhir

    vFilterPartner  CHARACTER varying= '';
    vFilterPartner2 CHARACTER varying= '';
    vFilterOu   CHARACTER varying= '';
    vFilterOu2  CHARACTER varying= '';

    vFilterCtgrProduct      CHARACTER varying= '';
    vFilterSubCtgrProduct   CHARACTER varying= '';
    vFilterGolonganProduct  CHARACTER varying= '';
    vFilterProductCodeName  CHARACTER varying= '';

    vStockOpnameActivityId      bigint;


    vEmptyString            TEXT := '';
    vZero               bigint := 0;
    vFlgSummary         TEXT := 'SUMMARY_1';
    vFlgSummary2            TEXT := 'SUMMARY_2';
    vFlgFinal           TEXT := 'FINAL_1';
    vFlgFinal2          TEXT := 'FINAL_2';

    vSiDocumentId       bigint :=321;
    vTsiDocumentId      bigint :=361;


BEGIN
    DELETE FROM tt_raw_data_opname_nota WHERE session_id = pSessionId;

    SELECT (f_get_ou_bu_structure(pOuId)).ou_bu_id INTO vOuBuId;
    SELECT date_year_month INTO vLastArLedgerPeriod FROM m_admin_process_ledger WHERE tenant_id = pTenantId AND ou_id = vOuBuId AND ledger_code = 'FIN.AR' AND status_ledger = '1'  ORDER BY date_year_month DESC LIMIT 1;
    SELECT TO_CHAR(TO_DATE(vLastArLedgerPeriod,'YYYYMM') - INTERVAL '1 Month','YYYYMM') INTO vPrevYearMonth;
    SELECT TO_CHAR(TO_DATE(vLastArLedgerPeriod,'YYYYMM') + INTERVAL '1 Month','YYYYMM') INTO vLastBulanSaldoAwal;
    SELECT TO_CHAR(TO_DATE(pYearMonth,'YYYYMM') - INTERVAL '1 Month','YYYYMM') INTO vDateParameter; -- hanya dipakai jika period yang user pilih lebih besar dari tgl tutup bulan terakhir

    --A. filter terhadap ou
    IF pOuId != vEmptyId THEN
        vFilterOu = ' (B.ou_bu_id = '||pOuId||' OR B.ou_branch_id = '||pOuId||') AND ';
    END IF;

    --B. filter terhadap partner
    IF pPartnerId != vEmptyId THEN
        vFilterPartner = ' f_get_payment_group_customer_id(C.partner_id) = f_get_payment_group_customer_id('||pPartnerId||') AND ';
        vFilterPartner2 = ' f_get_payment_group_customer_id(A.partner_id) = f_get_payment_group_customer_id('||pPartnerId||') AND ';
    END IF;

    -- 1.a. jika periode yg user pilih <= bulan terakhir tutup bulan, ambil dari data hasil proses tutup bulan di periode yg user pilih (cth: user pilih juni 2017, terakhir tutup bulan juli)
    -- 1.b  jika periode yg user pilih > bulan terakhir tutup bulan, maka
        -- a. ambil dari data hasil proses tutup bulan terakhir
        -- b. Ambil Invoice yg msh o/s per terakhir tutup bulan + 1 s/d PARAM periode - 1 bulan
        -- c. Ambil invoice yg tgl dok di terakhir tutup bulan + 1 bulan s/d PARAM periode - 1 bulan
        -- d. Ambil summary data pelunasan di bulan  tutup bulan + 1 bulan s/d PARAM periode - 1 bulan
        -- e. Hanya ambil yg msh ada sisa (saldo awal - pelunasan <> 0)
    -- 2. Ambil Invoice yg msh o/s per awal periode yg user pilih
    -- 3. Ambil Invoice yg tgl dok di periode yg user pilih
    -- 4. Ambil summary data pelunasan di bulan periode yg user pilih per invoice, hasil no 2 dan 3

    IF( pYearMonth <= vLastArLedgerPeriod ) THEN

        -- 1.a ambil dari data hasil proses tutup bulan di periode yg user pilih
        EXECUTE '
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status, date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT $1, $3, $2, A.tenant_id,
            C.ou_id, A.doc_type_id, A.invoice_id, A.curr_code,
            A.invoice_amount, A.balance_amount, A.payment_amount
        FROM fi_summary_monthly_ar A, vw_fi_all_invoice_ar C, m_ou_structure B
        WHERE A.date_year_month = $2 AND
            A.invoice_id = C.id AND
            A.doc_Type_id = C.doc_Type_id AND
            C.ou_id = B.ou_id AND '||
            vFilterOu ||
            vFilterPartner
        USING pSessionId, pYearMonth, vFlgFinal;

    ELSE

        -- a. ambil dari data hasil proses tutup bulan di periode terakhir tutup bulan
        EXECUTE '
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status, date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT $1, $4, $2, A.tenant_id,
            C.ou_id, A.doc_type_id, A.invoice_id, A.curr_code,
            A.invoice_amount, A.balance_amount - A.payment_amount, 0
        FROM fi_summary_monthly_ar A, vw_fi_all_invoice_ar C, m_ou_structure B
        WHERE A.date_year_month = $3 AND
            A.invoice_id = C.id AND
            A.doc_Type_id = C.doc_Type_id AND
            C.ou_id = B.ou_id AND '||
            vFilterOu ||
            vFilterPartner || '
            A.balance_amount <> A.payment_amount '
        USING pSessionId, pYearMonth, vPrevYearMonth, vFlgSummary;

        -- b. Ambil Invoice yg msh o/s per terakhir tutup bulan + 1 s/d PARAM periode - 1 bulan
        EXECUTE '
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status, date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT $1, $3,$2, A.tenant_id,
            A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.curr_code,
            A.amount, A.amount, 0
        FROM fi_invoice_ar_balance A, m_ou_structure B, dt_date C
        WHERE A.tenant_id = $4 AND
            A.ou_id = B.ou_id AND ' ||
            vFilterOu ||
            vFilterPartner2 || '
            A.doc_date = C.string_date AND
            C.year_month_date BETWEEN $5 AND $6'
        USING pSessionId, pYearMonth, vFlgSummary, pTenantId, vLastBulanSaldoAwal, vDateParameter;

        -- c. Ambil Invoice yg tgl dok per terakhir tutup bulan + 1 s/d PARAM periode - 1 bulan
        EXECUTE '
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status, date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT $1, $3, $2, A.tenant_id,
            A.ou_id, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_curr_code,
            A.gov_tax_amount, A.gov_tax_amount, 0
        FROM fi_invoice_tax_ar_balance A, m_ou_structure B, dt_date C, fi_invoice_ar_balance D
        WHERE A.tenant_id = $4 AND
            A.ou_id = B.ou_id AND ' ||
            vFilterOu ||
            vFilterPartner2 || '
            A.invoice_ar_balance_id = D.invoice_ar_balance_id AND
            D.doc_date = C.string_date AND
            C.year_month_date BETWEEN $5 AND $6 '
        USING pSessionId, pYearMonth, vFlgSummary, pTenantId, vLastBulanSaldoAwal, vDateParameter;

        -- d. Ambil summary data pelunasan per terakhir tutup bulan + 1 s/d PARAM periode - 1 bulan
        -- d.1 Ambil pelunasan piutang dari dokumen alokasi credit AR, atas dokumen credit AR
        EXECUTE '
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status, date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT $1, $7, $2, A.tenant_id,
            E.ou_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
            0, 0, SUM(D.credit_amount) * -1
        FROM fi_allocation_ar A, dt_date C, fi_allocation_ar_balance D, tt_raw_data_opname_nota E
        WHERE A.tenant_id = $4 AND
            A.doc_date = C.string_date AND
            C.year_month_date BETWEEN $8 AND $9 AND
            A.allocation_ar_id = D.allocation_ar_id AND
            D.credit_id <> $5 AND
            D.flg_alloc = $6 AND
            D.credit_id = E.invoice_id AND
            E.flg_status = $3
        GROUP BY A.tenant_id, E.ou_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code '
        USING pSessionId, pYearMonth, vFlgSummary, pTenantId, vEmptyId, 'A',vFlgSummary2, vLastBulanSaldoAwal, vDateParameter;

        -- d.2 Ambil pelunasan piutang dari dokumen alokasi credit AR, atas dokumen debt AR
        EXECUTE '
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status,date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT $1, $7, $2, A.tenant_id,
            E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
            0, 0, SUM(D.debit_amount)
        FROM fi_allocation_ar A, dt_date C, fi_allocation_ar_balance D, tt_raw_data_opname_nota E
        WHERE A.tenant_id = $4 AND
            A.doc_date = C.string_date AND
            C.year_month_date BETWEEN $8 AND $9 AND
            A.allocation_ar_id = D.allocation_ar_id AND
            D.debit_id <> $5 AND
            D.flg_alloc = $6 AND
            D.debit_id = E.invoice_id AND
            E.flg_status = $3
        GROUP BY A.tenant_id, E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code '
        USING pSessionId, pYearMonth, vFlgSummary, pTenantId, vEmptyId, 'A', vFlgSummary2, vLastBulanSaldoAwal, vDateParameter;

        -- d.3 Ambil pelunasan piutang dari dokumen alokasi cashbank IN AR
        EXECUTE '
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status, date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT $1, $3, $2, A.tenant_id,
            E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
            0, 0, SUM(D.debit_amount)
        FROM fi_allocation_ar A, dt_date C, fi_allocation_ar_balance D, tt_raw_data_opname_nota E
        WHERE A.tenant_id = $4 AND
            A.doc_date = C.string_date AND
            C.year_month_date BETWEEN $8 AND $9 AND
            A.allocation_ar_id = D.allocation_ar_id AND
            D.debit_id <> $5 AND
            D.flg_alloc = $6 AND
            D.debit_id = E.invoice_id AND
            E.flg_status = $3
        GROUP BY A.tenant_id, E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code '
        USING pSessionId, pYearMonth, vFlgSummary, pTenantId, vEmptyId, 'C', vFlgSummary2, vLastBulanSaldoAwal, vDateParameter;

        -- e. Hanya ambil yg msh ada sisa (saldo awal - pelunasan <> 0)
        INSERT INTO tt_raw_data_opname_nota
            (session_id, flg_status, date_year_month, tenant_id,
            ou_id, doc_type_id, invoice_id, curr_code,
            invoice_amount, balance_amount, payment_amount)
        SELECT pSessionId, vFlgFinal, pYearMonth, A.tenant_id,
            A.ou_id, A.doc_type_id, A.invoice_id, A.curr_code,
            SUM(A.invoice_amount), SUM(A.balance_amount), SUM(A.payment_amount)
        FROM tt_raw_data_opname_nota A
        WHERE A.session_id = pSessionId
            AND A.flg_status IN (vFlgSummary, vFlgSummary2)
        GROUP BY A.date_year_month, A.tenant_id, A.ou_Id, A.doc_type_id, A.invoice_id, A.curr_code
        HAVING (SUM(A.balance_amount) - SUM(A.payment_amount)) <> 0;

    END IF;

    -- 2 Ambil Invoice yg msh o/s per awal periode yg user pilih
    EXECUTE '
    INSERT INTO tt_raw_data_opname_nota
        (session_id, flg_status, date_year_month, tenant_id,
        ou_id, doc_type_id, invoice_id, curr_code,
        invoice_amount, balance_amount, payment_amount)
    SELECT $1, $3, $2, A.tenant_id,
        A.ou_id, A.doc_type_id, A.invoice_ar_balance_id, A.curr_code,
        A.amount, A.amount, 0
    FROM fi_invoice_ar_balance A, m_ou_structure B, dt_date C
    WHERE A.tenant_id = $4 AND
        A.ou_id = B.ou_id AND ' ||
        vFilterOu ||
        vFilterPartner2 || '
        A.doc_date = C.string_date AND
        C.year_month_date = $2 '
    USING pSessionId, pYearMonth, vFlgFinal, pTenantId;

    -- 3. Ambil Invoice yg tgl dok di periode yg user pilih
    EXECUTE '
    INSERT INTO tt_raw_data_opname_nota
        (session_id, flg_status, date_year_month, tenant_id,
        ou_id, doc_type_id, invoice_id, curr_code,
        invoice_amount, balance_amount, payment_amount)
    SELECT $1, $3, $2, A.tenant_id,
        A.ou_id, A.doc_type_id, A.invoice_tax_ar_balance_id, A.tax_curr_code,
        A.gov_tax_amount, A.gov_tax_amount, 0
    FROM fi_invoice_tax_ar_balance A, m_ou_structure B, dt_date C, fi_invoice_ar_balance D
    WHERE A.tenant_id = $4 AND
        A.ou_id = B.ou_id AND ' ||
        vFilterOu ||
        vFilterPartner2 || '
        A.invoice_ar_balance_id = D.invoice_ar_balance_id AND
        D.doc_date = C.string_date AND
        C.year_month_date = $2 '
    USING pSessionId, pYearMonth, vFlgFinal, pTenantId;

    -- 4. Ambil summary data pelunasan di bulan yang user pilih
    -- Ambil pelunasan piutang dari dokumen alokasi credit AR, atas dokumen credit AR
    EXECUTE '
    INSERT INTO tt_raw_data_opname_nota
        (session_id, flg_status, date_year_month, tenant_id,
        ou_id, doc_type_id, invoice_id, curr_code,
        invoice_amount, balance_amount, payment_amount)
    SELECT $1, $7, $2, A.tenant_id,
        E.ou_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code,
        0, 0, SUM(D.credit_amount) * -1
    FROM fi_allocation_ar A, dt_date C, fi_allocation_ar_balance D, tt_raw_data_opname_nota E
    WHERE A.tenant_id = $4 AND
        A.doc_date = C.string_date AND
        C.year_month_date = $2 AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.credit_id <> $5 AND
        D.flg_alloc = $6 AND
        D.credit_id = E.invoice_id AND
        E.flg_status = $3
    GROUP BY A.tenant_id, E.ou_id, D.credit_doc_type_id, D.credit_id, D.credit_curr_code'
    USING pSessionId, pYearMonth, vFlgFinal, pTenantId, vEmptyId, 'A', vFlgFinal2;

    -- Ambil pelunasan piutang dari dokumen alokasi credit AR, atas dokumen debt AR
    EXECUTE '
    INSERT INTO tt_raw_data_opname_nota
        (session_id, flg_status,date_year_month, tenant_id,
        ou_id, doc_type_id, invoice_id, curr_code,
        invoice_amount, balance_amount, payment_amount)
    SELECT $1, $7, $2, A.tenant_id,
        E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount)
    FROM fi_allocation_ar A, dt_date C, fi_allocation_ar_balance D, tt_raw_data_opname_nota E
    WHERE A.tenant_id = $4 AND
        A.doc_date = C.string_date AND
        C.year_month_date = $2 AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.debit_id <> $5 AND
        D.flg_alloc = $6 AND
        D.debit_id = E.invoice_id AND
        E.flg_status = $3
    GROUP BY A.tenant_id, E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code '
    USING pSessionId, pYearMonth, vFlgFinal, pTenantId, vEmptyId, 'A', vFlgFinal2;

    -- Ambil pelunasan piutang dari dokumen alokasi cashbank IN AR
    EXECUTE '
    INSERT INTO tt_raw_data_opname_nota
        (session_id, flg_status, date_year_month, tenant_id,
        ou_id, doc_type_id, invoice_id, curr_code,
        invoice_amount, balance_amount, payment_amount)
    SELECT $1, $7, $2, A.tenant_id,
        E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code,
        0, 0, SUM(D.debit_amount)
    FROM fi_allocation_ar A, dt_date C, fi_allocation_ar_balance D, tt_raw_data_opname_nota E
    WHERE A.tenant_id = $4 AND
        A.doc_date = C.string_date AND
        C.year_month_date = $2 AND
        A.allocation_ar_id = D.allocation_ar_id AND
        D.debit_id <> $5 AND
        D.flg_alloc = $6 AND
        D.debit_id = E.invoice_id AND
        E.flg_status = $3
    GROUP BY A.tenant_id, E.ou_id, D.debit_doc_type_id, D.debit_id, D.debit_curr_code '
    USING pSessionId, pYearMonth, vFlgFinal, pTenantId, vEmptyId, 'C', vFlgFinal2;


    OPEN pRefHeader FOR
    SELECT f_get_ou_name(vOuBuId) AS ou_bu_name, pYearMonth AS period, f_get_ou_name(pOuId) AS ou_name,
            CASE WHEN pPartnerId != vEmptyId THEN f_get_partner_code(pPartnerId)||' - '||f_get_partner_name(pPartnerId) ELSE 'ALL' END AS partner,
            CASE WHEN pPartnerId != vEmptyId THEN
                f_get_partner_code(f_get_payment_group_customer_id(pPartnerId))||' - '||f_get_partner_name(f_get_payment_group_customer_id(pPartnerId)) ELSE 'ALL' END AS partner_group;
    RETURN NEXT pRefHeader;

    OPEN pRefDetail FOR

    SELECT f_get_ou_code(A.ou_id) AS ou_code, f_get_ou_name(A.ou_id) AS ou_name, A.date_year_month, F.city_name, H.region_name,
         f_get_doc_desc(A.doc_type_id) AS doc_desc, B.doc_no, B.doc_date, D.due_date AS lama,
         B.due_date AS due_date, SUM(A.invoice_amount) AS jumlah, SUM(A.balance_amount) AS saldo_awal, SUM(A.payment_amount) AS pelunasan,
         SUM(A.balance_amount) - SUM(A.payment_amount) AS sisa_saldo_awal,
        C.partner_code, f_get_partner_code(f_get_payment_group_customer_id(B.partner_id)) AS partner_group_code, C.partner_name,
        CASE WHEN E.phone2 != '' THEN E.phone1 || ' / ' || E.phone2 ELSE E.phone1 END AS phone,
        f_get_partner_code(COALESCE(I.salesman_id,vEmptyId)) AS salesman_code, f_get_partner_name(COALESCE(I.salesman_id,vEmptyId)) AS salesman_name,
        f_get_dkb_date_by_so_id(COALESCE(I.so_id,vEmptyId)) AS tgl_kirim,
        f_get_resi_receipt_date_by_so_id(COALESCE(I.so_id,vEmptyId)) AS tgl_terima
    FROM tt_raw_data_opname_nota A
    JOIN fi_invoice_ar_balance B ON a.invoice_id = B.invoice_ar_balance_id
    JOIN m_partner C ON B.partner_id = C.partner_id
    JOIN m_partner_type D ON C.partner_id = D.partner_id
    JOIN m_partner_address E ON C.partner_id = E.partner_id AND E.flg_official = 'Y'
    JOIN m_city F ON E.city = F.city_code
    JOIN m_region_customer G ON C.partner_id = G.customer_id
    JOIN m_region H ON G.region_id = H.region_id
    LEFT JOIN sl_so I ON I.so_id = B.ref_id AND I.doc_type_id = B.ref_doc_type_id AND B.doc_type_id IN (vSiDocumentId, vTsiDocumentId)
    WHERE A.session_id = pSessionId
        AND A.flg_status IN (vFlgFinal, vFlgFinal2)
    GROUP BY A.date_year_month, A.ou_id, A.doc_type_id, B.doc_no, B.doc_date, B.due_date, D.due_date, F.city_name, H.region_name,
        C.partner_code, C.partner_name, E.phone2, E.phone1, B.partner_id, I.so_id
    HAVING SUM(A.balance_amount) - SUM(A.payment_amount) <> 0
    ORDER BY B.doc_no;

    RETURN NEXT pRefDetail ;

    DELETE FROM tt_raw_data_opname_nota WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
/