Laporan Rekap Piutang

CREATE OR REPLACE FUNCTION rpt.r_rekap_piutang(CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pRecordOwnerId          ALIAS FOR $2;
    pFilterSupplier         ALIAS FOR $3;
    pStartDate              ALIAS FOR $4;
    pEndDate                ALIAS FOR $5;


    vEmptyId                BIGINT := -99;
    vEmpty                  TEXT := '';
    vNone                   CHARACTER VARYING := '-';
    vNo                     CHARACTER VARYING := 'N';
    vYes                    CHARACTER VARYING := 'Y';
    vCreditDocTypeId        BIGINT :=201;
    vDateNow                CHARACTER VARYING := '';
    vStartDate              CHARACTER VARYING := '';
    vEndDate                CHARACTER VARYING := '';
    vSql                    CHARACTER VARYING := '';

BEGIN

    SELECT pEndDate INTO vEndDate;

    Open pRefHeader FOR
        SELECT record_owner_code, ou_company, pStartDate, pEndDate
        FROM m_record_owner WHERE record_owner_id=pRecordOwnerId;
    RETURN NEXT pRefHeader;

    -- hari sekarang
    SELECT TO_CHAR(NOW(),'YYYYMM') INTO vDateNow;

    -- periode mulai
    IF pStartDate='' THEN
        SELECT '0' INTO vStartDate;
    ELSE
        SELECT pStartDate INTO vStartDate;
    END IF;

    -- periode selesai
    IF pEndDate='' THEN
        SELECT vDateNow INTO vEndDate;
    ELSE
        SELECT pEndDate INTO vEndDate;
    END IF;

    -- ambil nominal hutang lancar dari fi_invoice_ar_balance
    vSql := 'INSERT INTO tt_rekap_hutang(session_uuid, supplier_id, before_due_amount,
        over_due_amount, payment_amount)
    SELECT ''' || pSessionId || ''', B.supplier_id,
        COALESCE(SUM(A.invoice_amount), 0), 0, 0
    FROM fi_invoice_ar_balance A
    INNER JOIN fi_invoice_ar B ON A.invoice_ar_id=B.invoice_ar_id
    WHERE A.flg_paid='''|| vNo ||''' AND A.record_owner_id=' || pRecordOwnerId || '
        AND A.doc_type_id=' || vCreditDocTypeId || ' AND B.doc_date <= ''' || vEndDate || '''';

    -- tambahkan filter jika periode mulai dipilih
    IF pStartDate <> '' THEN
        vSql := vSql || ' AND B.doc_date >= ''' || pStartDate || '''';
    END IF;

     -- tambahkan filter jika supplier dipilih
    IF pFilterSupplier <> '' THEN
        vSql := vSql || ' AND A.supplier_id=' || pFilterSupplier;
    END IF;

    vSql := vSql || ' AND B.due_date <= ''' || vDateNow || ''' GROUP BY B.supplier_id';

    EXECUTE vSql;

    -- ambil nominal hutang lewat jauth tempo dari fi_invoice_ar_balance
    vSql := 'INSERT INTO tt_rekap_hutang(session_uuid, supplier_id, before_due_amount,
        over_due_amount, payment_amount)
    SELECT ''' || pSessionId || ''', B.supplier_id,
        0, COALESCE(SUM(A.invoice_amount), 0), 0
    FROM fi_invoice_ar_balance A
    INNER JOIN fi_invoice_ar B ON A.invoice_ar_id=B.invoice_ar_id
    WHERE A.flg_paid='''|| vNo ||''' AND A.record_owner_id=' || pRecordOwnerId || '
        AND A.doc_type_id=' || vCreditDocTypeId || ' AND B.doc_date <= ''' || vEndDate || '''';

    -- tambahkan filter jika periode mulai dipilih
    IF pStartDate <> '' THEN
        vSql := vSql || ' AND B.doc_date >= ''' || pStartDate || '''';
    END IF;

     -- tambahkan filter jika supplier dipilih
    IF pFilterSupplier <> '' THEN
        vSql := vSql || ' AND A.supplier_id=' || pFilterSupplier;
    END IF;

    vSql := vSql || ' AND B.due_date > ''' || vDateNow || ''' GROUP BY B.supplier_id';

    EXECUTE vSql;

    -- ambil nominal nominal bayar dari fi_invoice_ar_balance
    vSql := 'INSERT INTO tt_rekap_hutang(session_uuid, supplier_id, before_due_amount,
        over_due_amount, payment_amount)
    SELECT ''' || pSessionId || ''', B.supplier_id,
        0, 0, COALESCE(SUM(A.payment_amount), 0)
    FROM fi_invoice_ar_balance A
    INNER JOIN fi_invoice_ar B ON A.invoice_ar_id=B.invoice_ar_id
    WHERE A.flg_paid='''|| vNo ||''' AND A.record_owner_id=' || pRecordOwnerId || '
        AND A.doc_type_id=' || vCreditDocTypeId || ' AND B.doc_date <= ''' || vEndDate || '''';

    -- tambahkan filter jika periode mulai dipilih
    IF pStartDate <> '' THEN
        vSql := vSql || ' AND B.doc_date >= ''' || pStartDate || '''';
    END IF;

    -- tambahkan filter jika supplier dipilih
    IF pFilterSupplier <> '' THEN
        vSql := vSql || ' AND A.supplier_id=' || pFilterSupplier;
    END IF;

    vSql := vSql || ' GROUP BY B.supplier_id';

    EXECUTE vSql;

    Open pRefDetail FOR
        SELECT A.supplier_id, B.supplier_name,
            COALESCE(SUM(A.before_due_amount),0) AS before_due_amount,
            COALESCE(SUM(A.over_due_amount),0) AS over_due_amount,
            COALESCE(SUM(A.payment_amount),0) AS payment_amount
            -- (before_due_amount+over_due_amount-payment_amount) AS balance_amount
        FROM tt_rekap_hutang A
        INNER JOIN m_supplier B ON A.supplier_id=B.supplier_id
        GROUP BY A.supplier_id, B.supplier_name;
    RETURN NEXT pRefDetail ;
    DELETE FROM tt_rekap_hutang WHERE session_uuid=pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, ptg, htg, pb, pj, batch
  COST 100;
  /