rec_payment.sql

  MERGE INTO TT_PAY_REQ_SERVICES   T
  USING (
    SELECT T.N_LINE_ID,
           T.N_AMOUNT_REQ -
             GREATEST(LEAST(
               T.N_AMOUNT_REQ,
               A.N_AVAILABLE_AMOUNT -
                 COALESCE(
                   SUM(T.N_AMOUNT_REQ)
                     OVER(PARTITION BY T.N_ACCOUNT_ID
                          ORDER BY T.N_ACCOUNT_ID, T.N_LINE_NO
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)), 0)      N_AMOUNT_REQ,
           T.N_AMOUNT_REC -
             GREATEST(LEAST(
               T.N_AMOUNT_REC,
               A.N_AVAILABLE_AMOUNT -
                 COALESCE(
                   SUM(T.N_AMOUNT_REC)
                     OVER(PARTITION BY T.N_ACCOUNT_ID
                          ORDER BY T.N_ACCOUNT_ID, T.N_LINE_NO
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)), 0)      N_AMOUNT_REC
    FROM   TT_PAY_REQ_SERVICES                T
    INNER JOIN TT_ACCOUNTS_FOR_PAY_REQUESTS   A
    ON     A.N_ACCOUNT_ID = T.N_ACCOUNT_ID
    AND    A.N_BATCH_NO   = T.N_BATCH_NO
    WHERE  T.N_BATCH_NO = num_N_BATCH_NO
    ORDER BY T.N_ACCOUNT_ID, T.N_LINE_NO)   S
  ON (T.N_LINE_ID = S.N_LINE_ID)
  WHEN MATCHED THEN
    UPDATE
    SET    T.N_AMOUNT_REQ = S.N_AMOUNT_REQ,
           T.N_AMOUNT_REC = S.N_AMOUNT_REC;