CREATE OR REPLACE FUNCTION gl_temp_posting_monthly_journal(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE

    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vOuId                   bigint;
    vUserId                 bigint;
    vYearMonth              character varying(6);
    vDatetime               character varying(14);
    vPeriode                character varying(10);

    vMinYearMonth           character varying(6);
    vMaxYearMonth           character varying(6);
    vUnposted               bigint;

BEGIN

    vPeriode := 'MONTHLY';

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'gl_temp_posting_monthly_journal' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vOuId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'ouId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    SELECT CAST(A.process_parameter_value AS character varying(6)) INTO vYearMonth
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'yearmonth';


    SELECT B.journal_year_month INTO vMinYearMonth
    FROM (
        SELECT A.ou_id, A.journal_year_month, A.ledger_code, A.journal_type,
            f_get_count_released_journal_trx(A.tenant_id, A.sub_ou_id, A.journal_year_month, A.journal_type) AS release_journal,
            f_get_count_unreleased_journal_trx(A.tenant_id, A.sub_ou_id, A.journal_year_month, A.journal_type) AS unrelease_journal
        FROM gl_admin_journal_trx A
        WHERE A.journal_process_periode = vPeriode
            AND A.tenant_id = pTenantId
            AND A.ou_id = vOuId
         ORDER BY A.ledger_code, A.journal_type
         ) B
    WHERE B.journal_type <> 'JV.MEMO'
    GROUP BY B.ou_id, B.journal_year_month, B.ledger_code, B.journal_type
    HAVING SUM(B.unrelease_journal) <> 0
    ORDER BY B.journal_year_month ASC
    LIMIT 1;

    WHILE vMinYearMonth <= vYearMonth LOOP

        SELECT SUM(B.unrelease_journal) AS unposted INTO vUnposted
        FROM (
            SELECT A.ou_id, A.journal_year_month, A.ledger_code, A.journal_type,
                f_get_count_released_journal_trx(A.tenant_id, A.sub_ou_id, A.journal_year_month, A.journal_type) AS release_journal,
                f_get_count_unreleased_journal_trx(A.tenant_id, A.sub_ou_id, A.journal_year_month, A.journal_type) AS unrelease_journal
            FROM gl_admin_journal_trx A
            WHERE A.journal_process_periode = vPeriode
                AND A.tenant_id = pTenantId
                AND A.ou_id = vOuId
                AND A.journal_year_month = vMinYearMonth
             ORDER BY A.ledger_code, A.journal_type
             ) B
        WHERE B.journal_type <> 'JV.MEMO'
        GROUP BY B.ou_id, B.journal_year_month, B.ledger_code, B.journal_type
        HAVING SUM(B.unrelease_journal) <> 0
        LIMIT 1;

        IF vUnposted <> 0 THEN
            RAISE NOTICE '%',vMinYearMonth;

            /*
             * Lakukan journal posting raw material, WIP, dan finish goods
             */
            PERFORM gl_temp_posting_raw_materials_costing_journal(pTenantId, pSessionId, vOuId, vUserId, vMinYearMonth, vDatetime);
            PERFORM gl_temp_posting_finish_goods_costing_journal(pTenantId, pSessionId, vOuId, vUserId, vMinYearMonth, vDatetime);

        END IF;

        SELECT TO_CHAR(TO_DATE(vMinYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vMinYearMonth;

    END LOOP;

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