a

-- modiefied by fredy, 24 March 2015
-- add document with payment order but not yet cashbank out
-- into aging AP document
CREATE OR REPLACE FUNCTION r_aging_ap(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying, 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;
    pRoleId             ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;
    pOuId               ALIAS FOR $6;
    pPartnerId          ALIAS FOR $7; -- bisa all
    pDueDateType        ALIAS FOR $8; -- bisa all
    pDueDateFrom        ALIAS FOR $9;
    pDueDateTo          ALIAS FOR $10;
    pDateNow            ALIAS FOR $11;
    pCurrCode           ALIAS FOR $12;

    vEmptyId            bigint := -99;
    vEmptyIdString      character varying := '-99';
    vFlagYes            character varying := 'Y';
    vFlagNo             character varying := 'N';
    vFlagInprogress     character varying := 'I';
    vDocPaymentOrderAp  bigint := 231;
    vDocPrepayment      bigint := 202;
    vDocPrepaymentInvoice       bigint := 203;
    vDocTypePo          bigint := 101;

    vRoundingMode   character varying;
    vDigit          integer;
BEGIN

    SELECT parameter_value INTO vRoundingMode
    FROM t_system_config A
    INNER JOIN t_parameter B ON A.parameter_id = B.parameter_id
    WHERE A.tenant_id = pTenantId
    AND B.parameter_code = 'rounding.mode.tax';

    SELECT parameter_value::integer INTO vDigit
    FROM t_system_config A
    INNER JOIN t_parameter B ON A.parameter_id = B.parameter_id
    WHERE A.tenant_id = pTenantId
    AND B.parameter_code = 'rounding.scale.tax';

    -- RAISE WARNING 'sessionId = ' || pSessionId;
    DELETE FROM tr_aging_ap WHERE session_id = pSessionId;

    /**
     * ambil finance ap balance yang flg payment <> Y
        balance = amount - payment amount

        ageDate = tgl system - dueDate  > 0

        due date type :
            NOT YET DUE : tgl system < due_date dokumen
            OVER DUE : tgl system > due_date dokumen
            CURRENT : tgl system = due_date
     */

    INSERT INTO tr_aging_ap(
            session_id, tenant_id, curr_code, partner_id, partner_code,
            partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
            ext_doc_date, due_date, age_date, amount, payment_amount,
            balance_over_due, balance_current, balance_not_yet_due,
            balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
            invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
    SELECT  pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
            f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.ext_doc_no,
            A.ext_doc_date, A.due_date, 0, A.amount, A.payment_amount,
            0, 0, 0,
            0, 0, 0, 0,
            A.invoice_ap_balance_id, A.doc_type_id, A.invoice_ap_id, B.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
    FROM fi_invoice_ap_balance A
    LEFT JOIN pu_po B ON A.ref_id = B.po_id AND A.ref_doc_type_id = B.doc_type_id AND B.doc_type_id = vDocTypePo
    WHERE A.flg_payment <> vFlagYes
    AND A.ou_id = pOuId
    AND A.tenant_id = pTenantId
    AND A.ref_doc_type_id NOT IN (vDocPrepayment)
    AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
    AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1;

    --insert prepayment balance
    INSERT INTO tr_aging_ap(
            session_id, tenant_id, curr_code, partner_id, partner_code,
            partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
            ext_doc_date, due_date, age_date, amount, payment_amount,
            balance_over_due, balance_current, balance_not_yet_due,
            balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
            invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
    SELECT  pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
            f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.ext_doc_no,
            A.ext_doc_date, A.due_date, 0, A.amount, A.payment_amount,
            0, 0, 0,
            0, 0, 0, 0,
            A.invoice_ap_balance_id, A.doc_type_id, A.invoice_ap_id, C.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
    FROM fi_invoice_ap_balance A
    LEFT JOIN fi_invoice_ap_balance B ON A.ref_id = B.invoice_ap_balance_id
    LEFT JOIN pu_po C ON B.ref_id = po_id
    WHERE A.flg_payment <> vFlagYes
    AND A.ou_id = pOuId
    AND A.tenant_id = pTenantId
    AND A.ref_doc_type_id IN (vDocPrepayment)
    AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
    AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1;

    INSERT INTO tr_aging_ap(
            session_id, tenant_id, curr_code, partner_id, partner_code,
            partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
            ext_doc_date, due_date, age_date, amount, payment_amount,
            balance_over_due, balance_current, balance_not_yet_due,
            balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
            invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
    SELECT  pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
            f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.tax_no, A.tax_date, ' ',
            ' ', A.due_date, 0, A.tax_amount, CASE WHEN vRoundingMode = 'RD' THEN TRUNC(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) ELSE ROUND(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) END,
            0, 0, 0,
            0, 0, 0, 0,
            A.invoice_tax_ap_balance_id, A.doc_type_id, A.invoice_ap_balance_id, C.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
    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
    LEFT JOIN pu_po C ON B.ref_id = C.po_id AND B.ref_doc_type_id = C.doc_type_id AND C.doc_type_id = vDocTypePo
    WHERE A.flg_payment <> vFlagYes
    AND A.ou_id = pOuId
    AND A.tenant_id = pTenantId
    AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
    AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1
    AND A.gov_tax_amount <> 0;

    /**
     * added by fredi, 24 March 2015
     * 1. ambil balance invoice yang fi_invoice_ap_balance.flg_payment = Y
     *    tetapi, cb_trx_cashbank_balance.flg_payment <> Y
     * 2. ambil balance tax invoice fi_invoice_tax_ap_balance.flg_payment = Y
     *    tetapi, cb_trx_cashbank_balance.flg_payment <> Y
     * 3. Update payment in temporary table, return payment amount that not yet cash bank out
     *
     * Perubahan 2017-12-29
     * hanya mengambil data jika cb_trx_cashbank_balance.flg_payment adalah N atau I
     */
    INSERT INTO tr_aging_ap(
            session_id, tenant_id, curr_code, partner_id, partner_code,
            partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
            ext_doc_date, due_date, age_date, amount, payment_amount,
            balance_over_due, balance_current, balance_not_yet_due,
            balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
            invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due)
    SELECT  pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
            f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.ext_doc_no,
            A.ext_doc_date, A.due_date, 0, A.amount, A.payment_amount,
            0, 0, 0,
            0, 0, 0, 0,
            A.invoice_ap_balance_id, A.doc_type_id, A.invoice_ap_id, B.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
    FROM fi_invoice_ap_balance A
    LEFT JOIN pu_po B ON A.ref_id = B.po_id AND A.ref_doc_type_id = B.doc_type_id AND B.doc_type_id = vDocTypePo
    WHERE A.flg_payment = vFlagYes
    AND A.ou_id = pOuId
    AND A.tenant_id = pTenantId
    AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
    AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1
    AND EXISTS (
        SELECT  1
        FROM    fi_payment_order_invoice B
        INNER JOIN cb_trx_cashbank_balance C ON B.payment_order_id =  C.payment_id
            AND C.doc_type_id = vDocPaymentOrderAp
        WHERE   A.doc_type_id = B.ref_doc_type_id
            AND A.invoice_ap_balance_id = B.ref_id
            AND C.flg_payment IN (vFlagNo, vFlagInprogress)
    );

    INSERT INTO tr_aging_ap(
            session_id, tenant_id, curr_code, partner_id, partner_code,
            partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
            ext_doc_date, due_date, age_date, amount, payment_amount,
            balance_over_due, balance_current, balance_not_yet_due,
            balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
            invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
    SELECT  pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
            f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.tax_no, A.tax_date, ' ',
            ' ', A.due_date, 0, A.tax_amount, CASE WHEN vRoundingMode = 'RD' THEN TRUNC(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) ELSE ROUND(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) END,
            0, 0, 0,
            0, 0, 0, 0,
            A.invoice_tax_ap_balance_id, A.doc_type_id, A.invoice_ap_balance_id, C.doc_no , (CURRENT_DATE - CAST(A.due_date AS DATE) )
    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
    LEFT JOIN pu_po C ON B.ref_id = C.po_id AND B.ref_doc_type_id = C.doc_type_id AND C.doc_type_id = vDocTypePo
    WHERE A.flg_payment = vFlagYes
    AND A.ou_id = pOuId
    AND A.tenant_id = pTenantId
    AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
    AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1
    AND A.gov_tax_amount <> 0
    AND EXISTS (
        SELECT  1
        FROM    fi_payment_order_invoice B
        INNER JOIN cb_trx_cashbank_balance C ON B.payment_order_id =  C.payment_id
            AND C.doc_type_id = vDocPaymentOrderAp
        WHERE   A.doc_type_id = B.ref_doc_type_id
            AND A.invoice_tax_ap_balance_id = B.ref_id
            AND C.flg_payment IN (vFlagNo, vFlagInprogress)
    );

    -- * 3. Update payment in temporary table, return payment amount that not yet cash bank out
    WITH os_payment_order AS (
        SELECT SUM(B.credit_amount - B.debit_amount) AS credit_amount,
            A.invoice_ap_balance_id, A.doc_type_id, A.ref_id
        FROM tr_aging_ap A
        INNER JOIN fi_payment_order_invoice B ON A.invoice_ap_balance_id = B.ref_id
            AND A.doc_type_id = B.ref_doc_type_id
        INNER JOIN cb_trx_cashbank_balance C ON B.payment_order_id = C.payment_id
            AND C.doc_type_id = vDocPaymentOrderAp
        WHERE A.session_id = pSessionId
            AND C.flg_payment IN (vFlagNo, vFlagInprogress)
        GROUP BY A.invoice_ap_balance_id, A.doc_type_id, A.ref_id
    )
    UPDATE tr_aging_ap
    SET payment_amount = payment_amount - A.credit_amount
    FROM os_payment_order A
    WHERE tr_aging_ap.session_id = pSessionId
        AND A.invoice_ap_balance_id = tr_aging_ap.invoice_ap_balance_id
        AND A.doc_type_id = tr_aging_ap.doc_type_id;

    UPDATE tr_aging_ap
    SET age_date = DATE_PART('day', to_timestamp(pDateNow, 'YYYYMMDD') - to_timestamp(due_date, 'YYYYMMDD'))
    WHERE session_id = pSessionId;

    UPDATE tr_aging_ap
    SET balance_over_due = amount - payment_amount
    WHERE session_id = pSessionId
    AND age_date > 0;

    UPDATE tr_aging_ap
    SET balance_over_in_7_days = amount - payment_amount
    WHERE session_id = pSessionId
    AND age_date > 0 AND age_date <= 7;

    UPDATE tr_aging_ap
    SET balance_over_7_days = amount - payment_amount
    WHERE session_id = pSessionId
    AND age_date > 7 AND age_date <= 14;

    UPDATE tr_aging_ap
    SET balance_over_14_days = amount - payment_amount
    WHERE session_id = pSessionId
    AND age_date > 14 AND age_date <= 30;

    UPDATE tr_aging_ap
    SET balance_over_30_days = amount - payment_amount
    WHERE session_id = pSessionId
    AND age_date > 30;

    UPDATE tr_aging_ap
    SET balance_current = amount - payment_amount
    WHERE session_id = pSessionId
    AND age_date = 0;

    UPDATE tr_aging_ap
    SET balance_not_yet_due = amount - payment_amount
    WHERE session_id = pSessionId
    AND age_date < 0;

    IF pPartnerId <> vEmptyId THEN
        DELETE FROM tr_aging_ap A
        WHERE A.session_id = pSessionId AND A.partner_id <> pPartnerId;
    END IF;

    IF pCurrCode <> vEmptyIdString THEN
        DELETE FROM tr_aging_ap A
        WHERE A.session_id = pSessionId AND A.curr_code <> pCurrCode;
    END IF;

    Open pRefHeader FOR
        SELECT f_get_ou_name(pOuId) AS ou_name, f_get_username(pUserId) AS username, pDatetime AS datetime;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT A.doc_desc AS doc_type, A.doc_no AS doc_no, A.doc_date AS doc_date, A.due_date AS due_date,
           A.partner_code AS partner_code, A.partner_name AS partner_name,
           A.curr_code AS curr_code, SUM(A.amount - A.payment_amount) AS amount,
           SUM(A.balance_not_yet_due) AS balance_not_yet_due,
           SUM(A.balance_current) AS balance_current,
           SUM(A.balance_over_7_days) AS balance_over_7_days,
           SUM(A.balance_over_14_days) AS balance_over_14_days,
           SUM(A.balance_over_30_days) AS balance_over_30_days,
           SUM(A.balance_over_in_7_days) AS balance_over_in_7_days,
           A.po_no AS po_no, A.over_due AS over_due
    FROM tr_aging_ap A
    WHERE A.session_id = pSessionId
    GROUP BY A.doc_desc, A.doc_no, A.doc_date, A.curr_code, A.due_date, A.partner_code, A.partner_name, A.po_no, A.over_due
    ORDER BY A.curr_code, A.partner_name, A.due_date, A.doc_date, A.doc_no;

    RETURN NEXT pRefDetail;

    DELETE FROM tr_aging_ap WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/