gl_posting_monthly_journal LIVE SPOORING 20180222

-- Function: gl_posting_monthly_journal(bigint, character varying, character varying)

-- DROP FUNCTION gl_posting_monthly_journal(bigint, character varying, character varying);

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);

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION gl_posting_monthly_journal(bigint, character varying, character varying)
  OWNER TO sts;