Привет, проанализируй скрипт и внеси в него изменения.
В чем проблема - он возвращает слишком много данных,
И неправильно считает дни просрочки.
Он считает сейчас неверно.
Мне не нужны контракты, у которых нулевая просрочка на данный момент,
также - если у конракта  была просрочка, но он ее погасил - мне нужно чтобы отчет начинался с датыпросрочки последней, а не с первой.

WITH ACC_TMP AS (
    SELECT /*+ PARALLEL(8) MATERIALIZE*/ 
        A.ACNT_CONTRACT__OID AS ID, 
        A.ID AS AC_ID,  
        A.ACCOUNT_NAME, 
        A.CODE, 
        A.ACCOUNT_TYPE, 
        A.GL_NUMBER, 
        A.CURRENT_BALANCE
    FROM ODS.WAY_ACCOUNT_G A
    JOIN ODS.MPR_ACCT_E ACCT 
        ON ACCT.NUMBER_ = A.GL_NUMBER
        AND ACCT.BAL_ACCT = 1424
      --  AND A.ACNT_CONTRACT__OID IN (52435221) -- Объединены условия для разных OID
),
CONTRACTS AS (
    SELECT 
        C.ID, 
        C.CCAT, 
        C.CONTRACT_NUMBER
    FROM ODS.WAY_ACNT_CONTRACT_G C
    INNER JOIN ACC_TMP A ON C.ID = A.ID
    WHERE C.AMND_STATE = 'A'
),
ENTRIES AS (
    SELECT 
        I.ACCOUNT__OID,  
        E.ACNT_CONTRACT__ID, 
        E.M_TRANSACTION__ID, 
        E.POSTING_DATE, 
        (E.AMOUNT + E.FEE_AMOUNT + E.BALANCE) AS TOTAL_AMOUNT
    FROM ODS.WAY_ENTRY_G E
    JOIN ODS.WAY_ITEM_G I 
        ON E.ITEM__ID = I.ID 
    INNER JOIN ACC_TMP A 
        ON I.ACCOUNT__OID = A.AC_ID
        AND E.ACNT_CONTRACT__ID = A.ID
    WHERE 
        E.POSTING_DATE <= TO_DATE('08.10.2024','DD.MM.YYYY')
        AND NVL(E.IS_REVERSED, '1') <> 'Z'
),
AGGR_JOIN AS (
SELECT 
  ---  C.ID, 
 C.CCAT, 
  --  C.CONTRACT_NUMBER,
 E.ACCOUNT__OID,
 E.ACNT_CONTRACT__ID,
  E.M_TRANSACTION__ID,
  E.POSTING_DATE,
  SUM(E.TOTAL_AMOUNT) AS BAL
FROM CONTRACTS C
INNER JOIN ENTRIES E 
    ON C.ID = E.ACNT_CONTRACT__ID
GROUP BY 
    C.ID, 
    C.CCAT,
    C.CONTRACT_NUMBER,
    E.ACCOUNT__OID,
    E.ACNT_CONTRACT__ID,
    E.M_TRANSACTION__ID,
    E.POSTING_DATE
ORDER BY 
    E.POSTING_DATE, 
    E.M_TRANSACTION__ID),
  
source_data AS (
    SELECT
        ACNT_CONTRACT__ID,
        POSTING_DATE,
        M_TRANSACTION__ID,
        ACCOUNT__OID,
        BAL,
        CCAT,
        ROW_NUMBER() OVER (ORDER BY POSTING_DATE, M_TRANSACTION__ID) AS rn
    FROM
    AGGR_JOIN
),
marked_data AS (
    SELECT
        sd.*,
        CASE
            WHEN BAL = 0 THEN 1
            ELSE 0
        END AS is_zero_balance
    FROM
        source_data sd
),
grouped_data AS (
    SELECT
        md.*,
        SUM(is_zero_balance) OVER (ORDER BY rn) AS step_o
    FROM
        marked_data md
),
list_acct_o AS (
    SELECT
        gd.*,
        (SELECT
            LISTAGG(ACCOUNT__OID, ' ') WITHIN GROUP (ORDER BY rn)
         FROM
            grouped_data
         WHERE
            rn <= gd.rn
        ) AS list_acct_o
    FROM
        grouped_data gd
),
day_arr_od_calc AS (
    SELECT
        la.*,
        CASE
            WHEN BAL = 0 THEN 0
            ELSE TRUNC(POSTING_DATE) - TO_DATE('08.10.2024', 'DD.MM.YYYY')
        END AS day_arr_od
    FROM
        list_acct_o la
),
VX_VX AS (
SELECT
    ACCOUNT__OID,
    ACNT_CONTRACT__ID,
    M_TRANSACTION__ID,
    POSTING_DATE,
    BAL,
    day_arr_od,
    step_o - 1 AS step_o,
    rn - 1 AS last_row_o,
    list_acct_o,
    CCAT,
  TO_DATE('04.10.2024','DD.MM.YYYY') as REPORT_DATE
FROM
    day_arr_od_calc
ORDER BY
    POSTING_DATE, M_TRANSACTION__ID),
  
  AGGR_VX as
  
  (SELECT ACNT_CONTRACT__ID, MAX(last_row_o) as MAX_ROW
FROM  VX_VX
GROUP BY ACNT_CONTRACT__ID),
JNTRS2 AS
(
SELECT a.ACNT_CONTRACT__ID, a.STEP_o
FROM VX_VX a
JOIN AGGR_VX b  ON a.ACNT_CONTRACT__ID = b.ACNT_CONTRACT__ID AND a.last_row_o = b.MAX_ROW

),
AGGR_VX_2 AS

( select a.ACNT_CONTRACT__ID, a.day_arr_od, a.report_date 
from  VX_VX a
  JOIN  JNTRS2 b
  ON a.ACNT_CONTRACT__ID = b.ACNT_CONTRACT__ID AND a.STEP_o = b.STEP_o

),
AGGR_AFT_VX2 AS
(   SELECT ACNT_CONTRACT__ID, REPORT_DATE, MIN(day_arr_od)  as day_arr_od

FROM  AGGR_VX_2 
GROUP BY 
ACNT_CONTRACT__ID, REPORT_DATE
),

/*JOIN_VX_VX_FIRST AS

(SELECT 
    C.ID, 
    C.CCAT, 
    C.CONTRACT_NUMBER
FROM 
    ODS.WAY_ACNT_CONTRACT_G C
JOIN 
    ODS.WAY_ACCOUNT_G A ON C.ID = A.ACNT_CONTRACT__OID
JOIN 
    ODS.MPR_ACCT_E ACCT ON ACCT.NUMBER_ = A.GL_NUMBER
WHERE 
    ACCT.BAL_ACCT = 1424
    AND A.ACNT_CONTRACT__OID = 63729021
    AND C.AMND_STATE = 'A'),*/
  
  ALMOST_FIN AS
  
  (
  
  SELECT b.ACNT_CONTRACT__ID, b.day_arr_od,b.REPORT_DATE,a.CONTRACT_NUMBER
  
  from CONTRACTS a 
  JOIN  AGGR_AFT_VX2 b on a.id = ACNT_CONTRACT__ID
  ),
  
  FIN AS
  
  (SELECT REPORT_DATE,CONTRACT_NUMBER,day_arr_od from ALMOST_FIN
  WHERE  day_arr_od != 0 )
  
  SELECT * from FIN
  
  WITH ACC_TMP AS (
    SELECT /*+ PARALLEL(8) MATERIALIZE*/ 
        A.ACNT_CONTRACT__OID AS ID, 
        A.ID AS AC_ID,  
        A.ACCOUNT_NAME, 
        A.CODE, 
        A.ACCOUNT_TYPE, 
        A.GL_NUMBER, 
        A.CURRENT_BALANCE
    FROM ODS.WAY_ACCOUNT_G A
    JOIN ODS.MPR_ACCT_E ACCT 
        ON ACCT.NUMBER_ = A.GL_NUMBER
        AND ACCT.BAL_ACCT = 1424
      --  AND A.ACNT_CONTRACT__OID IN (52435221) -- Объединены условия для разных OID
),
CONTRACTS AS (
    SELECT 
        C.ID, 
        C.CCAT, 
        C.CONTRACT_NUMBER
    FROM ODS.WAY_ACNT_CONTRACT_G C
    INNER JOIN ACC_TMP A ON C.ID = A.ID
    WHERE C.AMND_STATE = 'A'
),
ENTRIES AS (
    SELECT 
        I.ACCOUNT__OID,  
        E.ACNT_CONTRACT__ID, 
        E.M_TRANSACTION__ID, 
        E.POSTING_DATE, 
        (E.AMOUNT + E.FEE_AMOUNT + E.BALANCE) AS TOTAL_AMOUNT
    FROM ODS.WAY_ENTRY_G E
    JOIN ODS.WAY_ITEM_G I 
        ON E.ITEM__ID = I.ID 
    INNER JOIN ACC_TMP A 
        ON I.ACCOUNT__OID = A.AC_ID
        AND E.ACNT_CONTRACT__ID = A.ID
    WHERE 
        E.POSTING_DATE <= TO_DATE('08.10.2024','DD.MM.YYYY')
        AND NVL(E.IS_REVERSED, '1') <> 'Z'
),
AGGR_JOIN AS (
SELECT 
  ---  C.ID, 
 C.CCAT, 
  --  C.CONTRACT_NUMBER,
 E.ACCOUNT__OID,
 E.ACNT_CONTRACT__ID,
  E.M_TRANSACTION__ID,
  E.POSTING_DATE,
  SUM(E.TOTAL_AMOUNT) AS BAL
FROM CONTRACTS C
INNER JOIN ENTRIES E 
    ON C.ID = E.ACNT_CONTRACT__ID
GROUP BY 
    C.ID, 
    C.CCAT,
    C.CONTRACT_NUMBER,
    E.ACCOUNT__OID,
    E.ACNT_CONTRACT__ID,
    E.M_TRANSACTION__ID,
    E.POSTING_DATE
ORDER BY 
    E.POSTING_DATE, 
    E.M_TRANSACTION__ID),
  
source_data AS (
    SELECT
        ACNT_CONTRACT__ID,
        POSTING_DATE,
        M_TRANSACTION__ID,
        ACCOUNT__OID,
        BAL,
        CCAT,
        ROW_NUMBER() OVER (ORDER BY POSTING_DATE, M_TRANSACTION__ID) AS rn
    FROM
    AGGR_JOIN
),
marked_data AS (
    SELECT
        sd.*,
        CASE
            WHEN BAL = 0 THEN 1
            ELSE 0
        END AS is_zero_balance
    FROM
        source_data sd
),
grouped_data AS (
    SELECT
        md.*,
        SUM(is_zero_balance) OVER (ORDER BY rn) AS step_o
    FROM
        marked_data md
),
list_acct_o AS (
    SELECT
        gd.*,
        (SELECT
            LISTAGG(ACCOUNT__OID, ' ') WITHIN GROUP (ORDER BY rn)
         FROM
            grouped_data
         WHERE
            rn <= gd.rn
        ) AS list_acct_o
    FROM
        grouped_data gd
),
day_arr_od_calc AS (
    SELECT
        la.*,
        CASE
            WHEN BAL = 0 THEN 0
            ELSE TRUNC(POSTING_DATE) - TO_DATE('08.10.2024', 'DD.MM.YYYY')
        END AS day_arr_od
    FROM
        list_acct_o la
),
VX_VX AS (
SELECT
    ACCOUNT__OID,
    ACNT_CONTRACT__ID,
    M_TRANSACTION__ID,
    POSTING_DATE,
    BAL,
    day_arr_od,
    step_o - 1 AS step_o,
    rn - 1 AS last_row_o,
    list_acct_o,
    CCAT,
  TO_DATE('04.10.2024','DD.MM.YYYY') as REPORT_DATE
FROM
    day_arr_od_calc
ORDER BY
    POSTING_DATE, M_TRANSACTION__ID),
  
  AGGR_VX as
  
  (SELECT ACNT_CONTRACT__ID, MAX(last_row_o) as MAX_ROW
FROM  VX_VX
GROUP BY ACNT_CONTRACT__ID),
JNTRS2 AS
(
SELECT a.ACNT_CONTRACT__ID, a.STEP_o
FROM VX_VX a
JOIN AGGR_VX b  ON a.ACNT_CONTRACT__ID = b.ACNT_CONTRACT__ID AND a.last_row_o = b.MAX_ROW

),
AGGR_VX_2 AS

( select a.ACNT_CONTRACT__ID, a.day_arr_od, a.report_date 
from  VX_VX a
  JOIN  JNTRS2 b
  ON a.ACNT_CONTRACT__ID = b.ACNT_CONTRACT__ID AND a.STEP_o = b.STEP_o

),
AGGR_AFT_VX2 AS
(   SELECT ACNT_CONTRACT__ID, REPORT_DATE, MIN(day_arr_od)  as day_arr_od

FROM  AGGR_VX_2 
GROUP BY 
ACNT_CONTRACT__ID, REPORT_DATE
),

/*JOIN_VX_VX_FIRST AS

(SELECT 
    C.ID, 
    C.CCAT, 
    C.CONTRACT_NUMBER
FROM 
    ODS.WAY_ACNT_CONTRACT_G C
JOIN 
    ODS.WAY_ACCOUNT_G A ON C.ID = A.ACNT_CONTRACT__OID
JOIN 
    ODS.MPR_ACCT_E ACCT ON ACCT.NUMBER_ = A.GL_NUMBER
WHERE 
    ACCT.BAL_ACCT = 1424
    AND A.ACNT_CONTRACT__OID = 63729021
    AND C.AMND_STATE = 'A'),*/
  
  ALMOST_FIN AS
  
  (
  
  SELECT b.ACNT_CONTRACT__ID, b.day_arr_od,b.REPORT_DATE,a.CONTRACT_NUMBER
  
  from CONTRACTS a 
  JOIN  AGGR_AFT_VX2 b on a.id = ACNT_CONTRACT__ID
  ),
  
  FIN AS
  
  (SELECT REPORT_DATE,CONTRACT_NUMBER,day_arr_od from ALMOST_FIN
  WHERE  day_arr_od != 0 )
  
  SELECT * from FIN