select advance_id, employee_id, employee_code, employee_name, employee_last_name, mission_number,
            service_code, service_label, DATE_CREATION, description, AMOUNT_ADVANCE, round(amount_used,decimals_number)
            amount_used, currency_id,
            rate_eur, rate_local
            from
            (
            select advance_id, employee_id, employee_code, employee_name, employee_last_name, mission_number,
            service_code, service_label, DATE_CREATION, description, AMOUNT_ADVANCE, currency_id,
            rate_eur, rate_local, amount_used = (CASE WHEN
            (AMOUNT_ADVANCE-AMOUNT_RETURN)-(AMOUNT_AFFECTED_CONVERSION+AMOUNT_AFFECTED_EXPENSE+AMOUNT_AFFECTED_NOTE) > 0
            THEN (AMOUNT_AFFECTED_CONVERSION+AMOUNT_AFFECTED_EXPENSE+AMOUNT_AFFECTED_NOTE)
            WHEN (AMOUNT_ADVANCE-AMOUNT_RETURN) < 0 THEN 0
            ELSE AMOUNT_ADVANCE-AMOUNT_RETURN END),
            decimals_number, mission_date_end, mission_id, amount_return
            FROM( -- GENERATING RESULT TABLE
            SELECT ADV_T.*,
            'AMOUNT_RETURN' = (CASE WHEN RET_T.AMOUNT_RETURN IS NULL THEN 0
            ELSE RET_T.AMOUNT_RETURN END),
            'AMOUNT_AFFECTED_CONVERSION' = (CASE WHEN CON_T.AMOUNT_AFFECTED_CONVERSION IS NULL THEN 0
            ELSE CON_T.AMOUNT_AFFECTED_CONVERSION END),
            'AMOUNT_AFFECTED_EXPENSE' = (CASE WHEN EXP_T.AMOUNT_AFFECTED_EXPENSE IS NULL THEN 0
            ELSE EXP_T.AMOUNT_AFFECTED_EXPENSE END),
            'AMOUNT_AFFECTED_NOTE' = (CASE WHEN NOT_T.AMOUNT_AFFECTED_NOTE IS NULL THEN 0
            ELSE NOT_T.AMOUNT_AFFECTED_NOTE END)

            FROM( -- GENERATING ADV_T (ADVANCE TABLE)
            SELECT A.*,
            EMPLOYEE.CODE 'employee_code',
            pers.name 'employee_last_name',
            pers.surname 'employee_name',
            service.code 'service_code',
            service.label 'service_label',
            C.CURRENCY_CODE 'CURRENCY_CODE',
            C.decimals_number,
            (case when A.mission_id = 0 then 0 else mission.number_piece end) 'mission_number',
            mission.date_end 'mission_date_end'

            FROM FC_BS_ADVANCE A
            left outer join SU_ST_EMPLOYEE EMPLOYEE on (EMPLOYEE.EMPLOYEE_ID = A.EMPLOYEE_ID)
            left outer join su_st_personalinfo pers on (pers.personalinfo_id = EMPLOYEE.personalinfo_id)
            left outer join CT_AC_CURRENCY C on (C.CURRENCY_ID = A.CURRENCY_ID)
            left outer join SU_ST_SERVICE service on (service.service_id = EMPLOYEE.service_id)
            left outer join SU_ST_COMPANY company on (company.company_id = EMPLOYEE.company_id)
            left outer join fc_bs_mission_order mission on (mission.mission_id = A.MISSION_ID)
            WHERE (A.ADVANCE_ID > 0) AND
            (A.EMPLOYEE_ID= ? or 1 = ?) AND
            (service.service_id = ? or 1 = ?) and
            (company.company_id = ? or 1 = ?) and
            (A.ADVANCE_ID != 0) AND
            (A.IS_CLOSED=0) AND
            (A.IS_DELETED=0)
            ) AS ADV_T
            -- CALCULATING RETURN AMOUNT
            LEFT OUTER JOIN
            (SELECT Z.ADVANCE_ID 'SOURCE_RETURN_ID', SUM(Z.AMOUNT_RETURN*A.RATE_EUR/Z.RATE_EUR) 'AMOUNT_RETURN'
            FROM FC_BS_ADV_RETURN Z ,FC_BS_ADVANCE A
            WHERE (Z.IS_DELETED=0)
            AND (A.ADVANCE_ID=Z.ADVANCE_ID)
            AND (A.ADVANCE_ID > 0)
            GROUP BY Z.ADVANCE_ID
            )AS RET_T
            ON (RET_T.SOURCE_RETURN_ID=ADV_T.ADVANCE_ID)
            -- CALCULATING AFFECTED AMOUNT BY CONVERSIONS
            LEFT OUTER JOIN
            (SELECT Z.ORIGINAL_ADVANCE_ID , SUM(Z.AMOUNT_SRC) 'AMOUNT_AFFECTED_CONVERSION'
            FROM FC_BS_ADVANCE Z
            WHERE (Z.IS_DELETED=0)
            GROUP BY Z.ORIGINAL_ADVANCE_ID
            )AS CON_T
            ON (CON_T.ORIGINAL_ADVANCE_ID=ADV_T.ADVANCE_ID)
            -- CALCULATING AFFECTED AMOUNT BY EXPENSES
            LEFT OUTER JOIN
            (SELECT Z.ADVANCE_ID, SUM(Z.AMOUNT_ADVANCE) 'AMOUNT_AFFECTED_EXPENSE'
            FROM FC_BS_EXPENSE Z
            WHERE Z.IS_REFUSED=0
            GROUP BY Z.ADVANCE_ID
            ) AS EXP_T
            ON (EXP_T.ADVANCE_ID=ADV_T.ADVANCE_ID)
            -- CALCULATING AFFECTED AMOUNT BY EXPENSE NOTE
            LEFT OUTER JOIN
            (SELECT Z.ADVANCE_ID, SUM(Z.AMOUNT_USED) 'AMOUNT_AFFECTED_NOTE'
            FROM FC_BS_ADV_NOTE_LIST Z
            GROUP BY ADVANCE_ID
            ) AS NOT_T
            ON (NOT_T.ADVANCE_ID=ADV_T.ADVANCE_ID)
            -- RESULT TABLE FORMED
            ) AS RESULT
            )as res
            where (mission_id = 0 and round(amount_used,decimals_number) = 0 AND amount_return = 0) or
            (mission_id != 0 and mission_date_end <= getDate() and round(amount_used,decimals_number) = 0 AND
            amount_return = 0) or
            (amount_advance - amount_return - round(amount_used, decimals_number) > 0 AND
            advance_id in (select distinct advance_id
            from fc_bs_adv_note_list))
            order by employee_code, date_creation