Function Monthly Posting Erp

/* Modified by Adrian, Aug 31, 2017,
 * menambahkan update flag_process pada m_admin_process_ledger
 */

CREATE OR REPLACE FUNCTION gl_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);

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_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';

    PERFORM gl_reversed_sign_amount(pTenantId, vYearMonth);
    /*
     * Lakukan journal posting raw material, WIP, dan finish goods
     */
    PERFORM gl_posting_raw_materials_costing_journal(pTenantId, pSessionId, vOuId, vUserId, vYearMonth, vDatetime);
    PERFORM gl_posting_finish_goods_costing_journal(pTenantId, pSessionId, vOuId, vUserId, vYearMonth, vDatetime);

    -- ahen 10 jun 2015
    PERFORM gl_validate_journal(pTenantId, vOuId, pSessionId, pProcessNo, vUserId, vDatetime, vYearMonth, vPeriode);

    --Adrian, Aug 31, 2017
    UPDATE m_admin_process_ledger
    SET flag_process ='N',
        update_datetime = vDatetime,
        update_user_id = vUserId,
        version = version + 1
    WHERE ledger_code = 'GL'
        AND date_year_month = vYearMonth
        AND ou_id = vOuId;

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