CREATE OR REPLACE FUNCTION mlm_insert_temp_trx_penjualan_pulsa(character varying, bigint, bigint, character varying, character varying)
  RETURNS VOID AS
$BODY$
DECLARE
    pSessionId                  alias for $1;
    pTenantId                   alias for $2;
    pUserId                     alias for $3;
    pDatetime                   alias for $4;
    pDate                       alias for $5;

    vDbName                     character varying;
    vDbUser                     character varying;
    vDbPassword                 character varying;
    vDbPort                     character varying;
    vDbHostAddr                 character varying;

    vNo                         character varying(1);

BEGIN
    /**
     * 1. Ambil config HOST, DBNAME, PORT
     * 2. Insert ke tt via dblink
     */
    vNo := 'N';

    RAISE NOTICE 'pDate = %', pDate;

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.HOSTADDR')::text INTO vDbHostAddr;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.PORT')::text INTO vDbPort;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.NAME')::text INTO vDbName;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.USER')::text INTO vDbUser;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.PASSWORD')::text INTO vDbPassword;

    RAISE NOTICE 'vDbHostAddr = %', vDbHostAddr;
    RAISE NOTICE 'vDbPort = %', vDbPort;
    RAISE NOTICE 'vDbName = %', vDbName;
    RAISE NOTICE 'vDbUser = %', vDbUser;
    RAISE NOTICE 'vDbPassword = %', vDbPassword;

    -- DELETE TABLE tt_trx_penjualan_pulsa
    DELETE FROM tt_trx_penjualan_pulsa WHERE session_id = pSessionId;

    INSERT INTO tt_trx_penjualan_pulsa(
            session_id, trx_pulsa_id, member_code, item_name, item_amount,
            pv_percent, msisdn, create_datetime, create_user_id, update_datetime,
            update_user_id, version)
    SELECT pSessionId, A.id, A.member_code, A.item_name, A.item_amount,
            A.pv_percent, A.msisdn, A.create_datetime, A.create_user_id, A.update_datetime,
            A.update_user_id, A.version
    FROM dblink(
        'dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHostAddr||' user='||vDbUser||' password='||vDbPassword||''::text,
        'SELECT trx_pulsa_id, member_code, item_name, item_amount,
            pv_percent, msisdn, create_datetime, create_user_id, update_datetime,
            update_user_id, version
        FROM mlm_trx_penjualan_pulsa A
        WHERE LEFT(A.create_datetime,8) = '''||pDate||''''::text
        ) AS A
        (
            id                  bigint,
            member_code         character varying(10),
            item_name           character varying(50),
            item_amount         numeric,
            pv_percent          numeric,
            msisdn              character varying(30),
            create_datetime     character varying(14),
            create_user_id      bigint,
            update_datetime     character varying(14),
            update_user_id      bigint,
            version             bigint
        );

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;