CREATE OR REPLACE FUNCTION f_check_post_processing_stock_ds_not_working(character varying,bigint)
  RETURNS SETOF refcursor AS
$BODY$
        DECLARE
            pResult     REFCURSOR := 'result';

            pDocDate                    ALIAS FOR $1;
            pTenantId                   ALIAS FOR $2;

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

        BEGIN

        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;

        DELETE FROM tt_for_check_post_processing_stock_ds_not_working WHERE doc_date =pDocDate;

        -- tarik data dari web ds
        INSERT INTO tt_for_check_post_processing_stock_ds_not_working(
                doc_date,doc_no,doc_type_id)
        SELECT doc_date,doc_no,doc_type_id
        FROM dblink(
            'dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHostAddr||' user='||vDbUser||' password='||vDbPassword||''::text,
            'SELECT doc_date,doc_no,doc_type_id
            FROM ds_log_product_balance A
            WHERE A.doc_date = '''||pDocDate||'''
             GROUP BY doc_date,doc_no,ref_id,doc_type_id'::text

            ) AS A
            (
                doc_date character varying,
                doc_no  character varying,
                doc_type_id bigint
            );

        Open pResult FOR
            SELECT *
            FROM sl_do_mlm A
            WHERE not exists(
                SELECT 1
                FROM tt_for_check_post_processing_stock_ds_not_working B
                WHERE B.doc_type_id = A.doc_type_id
                AND B.doc_no = A.doc_no
                AND B.doc_date = A.doc_date
            )
            AND A.doc_date =pDocDate;
        RETURN NEXT pResult;

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