SELECT
  A.DID AS DID,
  A.DCODE AS DCODE,
  A.DNAME AS DNAME,
  A.PID AS PID,
  A.PCODE AS PCODE,
  A.PNAME AS PNAME,
  "A"."ID" AS "A.ID",
  "A"."KODE" AS "A.KODE",
  "A"."NAMA" AS "A.NAMA",
  "A"."ALIASNAMA" AS "A.ALIASNAMA",
  "A"."CURRENCY_ID" AS "A.CURRENCY_ID",
  "C"."KURS" AS "C.KURS",
  "C"."NAMA" AS "C.NAMA",
  "C"."SIMBOL" AS "C.SIMBOL",
  COALESCE((
    SELECT
      FIRST 1 SKIP 0 "CR"."NILAI" AS "CR.NILAI"
    FROM
      "KURSITEM" AS "CR"
    WHERE
      CR.CURRENCY_ID = A.CURRENCY_ID
      AND "CR"."TANGGAL" <= '2017-10-31'
    ORDER BY
      "CR"."TANGGAL" DESC
  ),1) AS "RATE",
  CASE WHEN (J.TOTAL_DEBIT_A>J.TOTAL_KREDIT_A) THEN (J.TOTAL_DEBIT_A-J.TOTAL_KREDIT_A) ELSE 0 END DEBIT_ORIGIN,
  CASE WHEN (J.TOTAL_DEBIT>J.TOTAL_KREDIT) THEN (J.TOTAL_DEBIT-J.TOTAL_KREDIT) ELSE 0 END DEBIT,
  CASE WHEN (J.TOTAL_KREDIT_A>J.TOTAL_DEBIT_A) THEN (J.TOTAL_KREDIT_A-J.TOTAL_DEBIT_A) ELSE 0 END CREDIT_ORIGIN,
  CASE WHEN (J.TOTAL_KREDIT>J.TOTAL_DEBIT) THEN (J.TOTAL_KREDIT-J.TOTAL_DEBIT) ELSE 0 END CREDIT
FROM
  (
    SELECT
      FIRST 10 SKIP 0 "A".*,
      "D"."ID" AS "DID",
      "D"."DEPTID" AS "DCODE",
      "D"."NAMA" AS "DNAME",
      "P"."PROJECT_ID" AS "PID",
      "P"."ID" AS "PCODE",
      "P"."NAMAPEKERJAAN" AS "PNAME"
    FROM
      "KIRAAN" AS "A" CROSS
      JOIN "DEPT" AS "D" CROSS
      JOIN "JOB" AS "P"
      LEFT JOIN "SUBKLAS" AS "S" ON S.ID = A.SUBCLASSIFICATION_ID
      LEFT JOIN "KURSMSTR" AS "C" ON C.ID = A.CURRENCY_ID
    WHERE
      EXISTS (
        SELECT
          1
        FROM
          "JURNALVC" AS "J"
          LEFT JOIN "G_JURNAL" AS "G" ON G.ID = J.TRANSACTION_ID
        WHERE
          "G"."TANGGAL" <= '2017-10-31'
          AND J.ACCOUNT_ID = A.ID
          AND J.DEPARTMENT_ID = D.ID
          AND J.PROJECT_ID = P.PROJECT_ID
        GROUP BY
          "J"."DEPARTMENT_ID",
          "J"."PROJECT_ID",
          "J"."ACCOUNT_ID",
          "J"."CURRENCY_ID"
        HAVING
          ABS(SUM("J"."DEBIT_A" - "J"."KREDIT_A"))> 0.001
      )
      AND "S"."NOKLASIFIKASI" <= 3
    ORDER BY
      "DCODE" ASC,
      "PCODE" ASC,
      "A"."KODE" ASC,
      "C"."KURS" ASC
  ) AS "A"
  LEFT JOIN "SUBKLAS" AS "S" ON S.ID = A.SUBCLASSIFICATION_ID
  LEFT JOIN "KURSMSTR" AS "C" ON C.ID = A.CURRENCY_ID
  LEFT JOIN (
    SELECT
      J.DEPARTMENT_ID AS DEPARTMENT_ID,
      J.PROJECT_ID AS PROJECT_ID,
      J.ACCOUNT_ID AS ACCOUNT_ID,
      J.CURRENCY_ID AS CURRENCY_ID,
      COALESCE(SUM(J.DEBIT_A),0) AS TOTAL_DEBIT_A,
      COALESCE(SUM(J.DEBIT),0) AS TOTAL_DEBIT,
      COALESCE(SUM(J.KREDIT_A),0) AS TOTAL_KREDIT_A,
      COALESCE(SUM(J.KREDIT),0) AS TOTAL_KREDIT
    FROM
      "JURNALVC" AS "J"
      LEFT JOIN "G_JURNAL" AS "G" ON G.ID = J.TRANSACTION_ID
    WHERE
      "G"."TANGGAL" <= '2017-10-31'
    GROUP BY
      "J"."DEPARTMENT_ID",
      "J"."PROJECT_ID",
      "J"."ACCOUNT_ID",
      "J"."CURRENCY_ID"
    HAVING
      ABS(SUM("J"."DEBIT_A" - "J"."KREDIT_A"))> 0.001
  ) AS "J" ON J.DEPARTMENT_ID = A.DID
  AND J.ACCOUNT_ID = A.ID
  AND J.PROJECT_ID = A.PID
WHERE
  "S"."NOKLASIFIKASI" <= 3
ORDER BY
  "DCODE" ASC,
  "PCODE" ASC,
  "A"."KODE" ASC,
  "C"."KURS" ASC