create or alter procedure RPT$D$LIABILITY (
    "DATE" D$DATE not null,
    PARAMS_MASK D$MASK = 7)
returns (
    ROW_ID D$KEY,
    CLIENT_CODE D$KEY,
    CLIENT_FK D$KEY,
    CLIENT_LAST_NAME type of column CLIENTS.LAST_NAME,
    CLIENT_FIRST_NAME type of column CLIENTS.FIRST_NAME,
    CLIENT_MIDDLE_NAME type of column CLIENTS.MIDDLE_NAME,
    CLIENT_ADDRESS type of column CLIENTS.ADDRESS_REG,
    CLIENT_BIRTH_DATE type of column CLIENTS.BIRTH_DATE,
    CLIENT_DOC_TYPE_FK type of column DOC_TYPES.DOC_TYPE_ID,
    CLIENT_DOC_SERIES type of column CLIENTS.DOC_SERIES,
    CLIENT_DOC_NUMBER type of column CLIENTS.DOC_NUMBER,
    CLIENT_DOC_ISSUE_DATE type of column CLIENTS.DOC_ISSUE_DATE,
    CLIENT_DOC_ISSUER type of column CLIENTS.DOC_ISSUER,
    CLIENT_DOC_ISSUER_CODE type of column CLIENTS.DOC_ISSUER_CODE,
    CLIENT_PHONE type of column CLIENTS.PHONE_HOME,
    CLIENT_EMAIL type of column CLIENTS.EMAIL,
    D_ACCOUNT D$ACCOUNT,
    D_DATE D$DATE,
    D_NUM_DOG D$VC50,
    D_AMOUNT D$SUM,
    D_AMOUNT_RUR D$SUM,
    C_ACCOUNT D$ACCOUNT,
    C_DATE D$DATE,
    C_NUM_DOG D$VC50,
    C_AMOUNT D$SUM,
    C_AMOUNT_RUR D$SUM,
    SUM_RESULT_1_RUR type of D$SUM,
    SUM_RESULT_2_RUR type of D$SUM)
AS
DECLARE VARIABLE C1_CLIENT_FK D$KEY;
DECLARE VARIABLE C1_ACCOUNT D$ACCOUNT;
DECLARE VARIABLE C1_DATE D$DATE;
DECLARE VARIABLE C1_NUM_DOG D$VC50;
DECLARE VARIABLE C1_AMOUNT D$SUM;
DECLARE VARIABLE C1_AMOUNT_RUR D$SUM;
DECLARE VARIABLE C2_CLIENT_FK D$KEY;
DECLARE VARIABLE C2_ACCOUNT D$ACCOUNT;
DECLARE VARIABLE C2_DATE D$DATE;
DECLARE VARIABLE C2_NUM_DOG D$VC50;
DECLARE VARIABLE C2_AMOUNT D$SUM;
DECLARE VARIABLE C2_AMOUNT_RUR D$SUM;
DECLARE VARIABLE RESULT_CLIENT_FK D$KEY;
DECLARE VARIABLE RESULT_SUM TYPE OF D$SUM;
DECLARE VARIABLE IS_SUSPEND D$BOOLEAN;
DECLARE GET_ACCOUNT_D CURSOR FOR (
    SELECT
      A.CLIENT_FK, A.ACCOUNT, A.CONTRACT_NUM, A.DATE_IN, SUM(T.OP_DIRECTION * T.OP_SUM) AS OP_SUM,
      CAST(CR.RATE * SUM(T.OP_DIRECTION * T.OP_SUM) AS D$SUM) AS OP_SUM_RUR
    FROM D$ACCOUNTS A
    INNER JOIN D$TRANSACTIONS T ON T.ACCOUNT1 = A.ACCOUNT
    INNER JOIN D$OPERATIONS O ON O.OPERATION_ID = T.OPERATION_FK AND O.IS_ACTIVE = 1 AND O."DATE" <= :"DATE"
    INNER JOIN D$TYPES DT ON DT.TYPE_ID = A.DEPOSIT_TYPE_FK
    LEFT OUTER JOIN GET_LAST_CURRENCY_RATE(:"DATE", DT.CURRENCY_FK) CR ON 1 = 1
    WHERE A.STATUS IN (0, 1)
      AND :"DATE" BETWEEN A.DATE_IN AND A.DATE_OUT2 - 1
    GROUP BY A.CLIENT_FK, A.ACCOUNT, CR.RATE, 3, 4
    UNION ALL
    SELECT A.CLIENT_FK, A.ACCOUNT, A.CONTRACT_NUM, A.DATE_IN, A.OP_SUM, CAST(CR.RATE * A.OP_SUM AS D$SUM)
    FROM D$TMP$ACCOUNTS A
    LEFT OUTER JOIN GET_LAST_CURRENCY_RATE(:"DATE", A.CURRENCY_FK) CR ON 1 = 1
    WHERE A.STATUS IN (1, 2)
      AND A.OP_DIRECTION = +1
    ORDER BY 1, 2);
DECLARE GET_ACCOUNT_C CURSOR FOR (
    SELECT A.CLIENT_FK, A.ACCOUNT, A.CONTRACT_NUM, A.DATE_IN, A.OP_SUM, CAST(CR.RATE * A.OP_SUM AS D$SUM) AS OP_SUM_RUR
    FROM D$TMP$ACCOUNTS A
    LEFT OUTER JOIN GET_LAST_CURRENCY_RATE(:"DATE", A.CURRENCY_FK) CR ON 1 = 1
    WHERE A.STATUS IN (1, 2)
      AND A.OP_DIRECTION = -1
    ORDER BY A.CLIENT_FK, A.ACCOUNT);
DECLARE GET_AMOUNT CURSOR FOR (
    SELECT CLIENT_FK, SUM(SUMM) AS OP_SUM_RUR
    FROM (SELECT A.CLIENT_FK, CAST(CR.RATE * SUM(T.OP_DIRECTION * T.OP_SUM) AS D$SUM) AS SUMM
          FROM D$ACCOUNTS A
          INNER JOIN D$TRANSACTIONS T ON T.ACCOUNT1 = A.ACCOUNT
          INNER JOIN D$OPERATIONS O ON O.OPERATION_ID = T.OPERATION_FK AND O.IS_ACTIVE = 1 AND O."DATE" <= :"DATE"
          INNER JOIN D$TYPES DT ON DT.TYPE_ID = A.DEPOSIT_TYPE_FK
          LEFT OUTER JOIN GET_LAST_CURRENCY_RATE(:"DATE", DT.CURRENCY_FK) CR ON 1 = 1
          WHERE A.STATUS IN (0, 1)
            AND :"DATE" BETWEEN A.DATE_IN AND A.DATE_OUT2 - 1
          GROUP BY A.CLIENT_FK, A.ACCOUNT, CR.RATE
          UNION ALL
          SELECT A.CLIENT_FK, A.OP_DIRECTION * CAST(CR.RATE * A.OP_SUM AS D$SUM)
          FROM D$TMP$ACCOUNTS A
          LEFT OUTER JOIN GET_LAST_CURRENCY_RATE(:"DATE", A.CURRENCY_FK) CR ON 1 = 1
          WHERE A.STATUS IN (1, 2)) T
    GROUP BY T.CLIENT_FK
    ORDER BY T.CLIENT_FK);
BEGIN
  OPEN GET_ACCOUNT_D;
  OPEN GET_ACCOUNT_C;
  OPEN GET_AMOUNT;
  FETCH GET_ACCOUNT_D INTO :C1_CLIENT_FK, :C1_ACCOUNT, :C1_NUM_DOG, :C1_DATE, :C1_AMOUNT, :C1_AMOUNT_RUR;
  IF (ROW_COUNT = 0) THEN
    C1_CLIENT_FK         = -1;
  FETCH GET_ACCOUNT_C INTO :C2_CLIENT_FK, :C2_ACCOUNT, :C2_NUM_DOG, :C2_DATE, :C2_AMOUNT, :C2_AMOUNT_RUR;
  IF (ROW_COUNT = 0) THEN
    C2_CLIENT_FK         = -1;
  FETCH GET_AMOUNT INTO  :RESULT_CLIENT_FK, :RESULT_SUM;
  IF (ROW_COUNT = 0) THEN
    RESULT_CLIENT_FK     = -1;
  ROW_ID                 = 0;
  CLIENT_CODE            = 50000000;
  FOR
    SELECT
      T.CLIENT_FK, T.CLIENT_LAST_NAME, T.CLIENT_FIRST_NAME, NULLIF(NULLIF(T.CLIENT_MIDDLE_NAME, '-'), ''), NULLIF(TRIM(T.CLIENT_ADDRESS), ''), T.CLIENT_BIRTH_DATE,
      T.CLIENT_DOC_TYPE_FK, NULLIF(NULLIF(TRIM(T.CLIENT_DOC_SERIES), '-'), ''), NULLIF(NULLIF(TRIM(T.CLIENT_DOC_NUMBER), '-'), ''), T.CLIENT_DOC_ISSUE_DATE, NULLIF(NULLIF(TRIM(T.CLIENT_DOC_ISSUER), '-'), ''), NULLIF(NULLIF(TRIM(T.CLIENT_DOC_ISSUER_CODE), '-'), ''),
      NULLIF(TRIM(T.CLIENT_PHONE), ''), NULLIF(TRIM(T.CLIENT_EMAIL), '')
    FROM (
      SELECT DISTINCT
        A.CLIENT_FK, A.CLIENT_LAST_NAME, A.CLIENT_FIRST_NAME, A.CLIENT_MIDDLE_NAME, A.CLIENT_ADDRESS, NULLIF(A.CLIENT_BIRTH_DATE, DATE '1900-01-01') AS CLIENT_BIRTH_DATE,
        A.CLIENT_DOC_TYPE_FK, A.CLIENT_DOC_SERIES, A.CLIENT_DOC_NUMBER, NULLIF(A.CLIENT_DOC_ISSUE_DATE, DATE '1900-01-01') AS CLIENT_DOC_ISSUE_DATE, A.CLIENT_DOC_ISSUER, A.CLIENT_DOC_ISSUER_CODE,
        A.CLIENT_PHONE, NULL AS CLIENT_EMAIL
      FROM D$TMP$ACCOUNTS A
      WHERE A.STATUS = 2
      UNION ALL
      SELECT
        C.CLIENT_ID, C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, C.ADDRESS_REG, NULLIF(C.BIRTH_DATE, DATE '1899-12-30'),
        C.DOC_TYPE_FK, C.DOC_SERIES, C.DOC_NUMBER, NULLIF(C.DOC_ISSUE_DATE, DATE '1899-12-30'), C.DOC_ISSUER, C.DOC_ISSUER_CODE,
        COALESCE(C.PHONE_HOME, C.PHONE_WORK), C.EMAIL
      FROM CLIENTS C
      WHERE EXISTS (
          SELECT 1 FROM D$ACCOUNTS A
          WHERE A.CLIENT_FK = C.CLIENT_ID
            AND A.STATUS IN (0, 1)
            AND :"DATE" BETWEEN A.DATE_IN AND A.DATE_OUT2 - 1
        )
    ) T
    ORDER BY 1
  INTO
    :CLIENT_FK, :CLIENT_LAST_NAME, :CLIENT_FIRST_NAME, :CLIENT_MIDDLE_NAME, :CLIENT_ADDRESS, :CLIENT_BIRTH_DATE,
    :CLIENT_DOC_TYPE_FK, :CLIENT_DOC_SERIES, :CLIENT_DOC_NUMBER, :CLIENT_DOC_ISSUE_DATE, :CLIENT_DOC_ISSUER, :CLIENT_DOC_ISSUER_CODE,
    :CLIENT_PHONE, CLIENT_EMAIL DO
  BEGIN
    IF (CLIENT_FK = RESULT_CLIENT_FK) THEN
    BEGIN
      IF (BIN_AND(PARAMS_MASK, 4) = 4) THEN
      BEGIN
        SUM_RESULT_1_RUR = RESULT_SUM;
        SUM_RESULT_2_RUR = IIF(RESULT_SUM < 0, 0, RESULT_SUM);
        /* OLD ДО 13.10.2008 N 174-ФЗ
        IF (SUM_RESULT_2_RUR > 100000) THEN
          SUM_RESULT_2_RUR = 100000 + (SUM_RESULT_2_RUR - 100000) * 0.9;
        SUM_RESULT_2_RUR = IIF(SUM_RESULT_2_RUR > 400000, 400000, SUM_RESULT_2_RUR);
        */
        SUM_RESULT_2_RUR = IIF(SUM_RESULT_2_RUR > 1400000, 1400000, SUM_RESULT_2_RUR);
      END
      FETCH GET_AMOUNT INTO  :RESULT_CLIENT_FK, :RESULT_SUM;
      IF (ROW_COUNT = 0) THEN
        RESULT_CLIENT_FK = -1;
      CLIENT_CODE        = CLIENT_CODE + 1;
    END

    IS_SUSPEND           = 0;
    IF (BIN_AND(PARAMS_MASK, 1) = 0) THEN
      C1_CLIENT_FK       = -1;
    IF (BIN_AND(PARAMS_MASK, 2) = 0) THEN
      C2_CLIENT_FK       = -1;
    WHILE (CLIENT_FK IN (C1_CLIENT_FK, C2_CLIENT_FK)) DO
    BEGIN
      IF (BIN_AND(PARAMS_MASK, 1) = 1 AND CLIENT_FK = C1_CLIENT_FK) THEN
      BEGIN
        D_ACCOUNT        = C1_ACCOUNT;
        D_DATE           = C1_DATE;
        D_NUM_DOG        = C1_NUM_DOG;
        D_AMOUNT         = C1_AMOUNT;
        D_AMOUNT_RUR     = C1_AMOUNT_RUR;
        FETCH GET_ACCOUNT_D INTO :C1_CLIENT_FK, :C1_ACCOUNT, :C1_NUM_DOG, :C1_DATE, :C1_AMOUNT, :C1_AMOUNT_RUR;
        IF (ROW_COUNT = 0) THEN
          C1_CLIENT_FK   = -1;
      END
      IF (BIN_AND(PARAMS_MASK, 2) = 2 AND CLIENT_FK = C2_CLIENT_FK) THEN
      BEGIN
        C_ACCOUNT        = C2_ACCOUNT;
        C_DATE           = C2_DATE;
        C_NUM_DOG        = C2_NUM_DOG;
        C_AMOUNT         = C2_AMOUNT;
        C_AMOUNT_RUR     = C2_AMOUNT_RUR;
        FETCH GET_ACCOUNT_C INTO :C2_CLIENT_FK, :C2_ACCOUNT, :C2_NUM_DOG, :C2_DATE, :C2_AMOUNT, :C2_AMOUNT_RUR;
        IF (ROW_COUNT = 0) THEN
          C2_CLIENT_FK   = -1;
      END
      SUSPEND;
      IS_SUSPEND         = 1;
      ROW_ID             = ROW_ID + 1;
      SUM_RESULT_1_RUR   = NULL;
      SUM_RESULT_2_RUR   = NULL;
      D_ACCOUNT          = NULL;
      D_DATE             = NULL;
      D_NUM_DOG          = NULL;
      D_AMOUNT           = NULL;
      D_AMOUNT_RUR       = NULL;
      C_ACCOUNT          = NULL;
      C_DATE             = NULL;
      C_NUM_DOG          = NULL;
      C_AMOUNT           = NULL;
      C_AMOUNT_RUR       = NULL;
    END /* WHILE */
    IF (IS_SUSPEND = 0) THEN
    BEGIN
      SUSPEND;
      ROW_ID             = ROW_ID + 1;
    END
    SUM_RESULT_1_RUR     = NULL;
    SUM_RESULT_2_RUR     = NULL;
    D_ACCOUNT            = NULL;
    D_DATE               = NULL;
    D_NUM_DOG            = NULL;
    D_AMOUNT             = NULL;
    D_AMOUNT_RUR         = NULL;
    C_ACCOUNT            = NULL;
    C_DATE               = NULL;
    C_NUM_DOG            = NULL;
    C_AMOUNT             = NULL;
    C_AMOUNT_RUR         = NULL;
  END /* FOR */
  CLOSE GET_AMOUNT;
  CLOSE GET_ACCOUNT_C;
  CLOSE GET_ACCOUNT_D;
END