Available Payments For Reconciliation Function

FUNCTION XX_GET_AVAIL_PAYMENT_FUNC(P_LEDGER       IN NUMBER,

                                   P_BANK_ACCT_ID IN NUMBER,

                                   P_AS_OF_DATE   IN DATE) RETURN NUMBER IS

  X_AMOUNT NUMBER;

BEGIN

  SELECT ABS(SUM(AMOUNT))

    INTO X_AMOUNT

    FROM (SELECT (CASE

                   WHEN C.BANK_CURRENCY_CODE <> C.CURRENCY_CODE THEN

                    (C.AMOUNT * NVL(C.EXCHANGE_RATE, 1))

                   ELSE

                    C.AMOUNT

                 END) AMOUNT C.ORG_ID,

                 C.CURRENT_BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

                 C.CHECK_DATE EFFECTIVE_DATE,

                 L.LEDGER_ID,

                 C.BANK_ACCOUNT_ID

            from AP_CHECKS_V C, XX_LOGO_TL L -- Its Custom Table only for Convert Org to Ledger

           where c.check_status = 'Negotiable'

             AND L.ORG_ID = C.ORG_ID

             AND C.CHECK_DATE <= P_AS_OF_DATE

             AND C.BANK_ACCOUNT_ID = P_BANK_ACCT_ID

             AND L.LEDGER_ID = P_LEDGER

         

          UNION ALL

         

          SELECT (CASE

                   WHEN C.BANK_CURRENCY_CODE <> C.CURRENCY_CODE THEN

                    (C.AMOUNT * NVL(C.EXCHANGE_RATE, 1))

                   ELSE

                    C.AMOUNT

                 END) AMOUNT C.ORG_ID,

                 C.CURRENT_BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

                 C.CHECK_DATE EFFECTIVE_DATE,

                 L.LEDGER_ID,

                 C.BANK_ACCOUNT_ID

            FROM AP_CHECKS_VIEW C, XX_LOGO_TL L  -- Its Custom Table only for Convert Org to Ledger

           WHERE C.CHECK_STATUS = 'Voided'

             AND (C.VOID_DATE > P_AS_OF_DATE AND

                 C.CHECK_DATE <= P_AS_OF_DATE)

             AND C.BANK_ACCOUNT_ID = P_BANK_ACCT_ID

             AND L.ORG_ID = C.ORG_ID

             AND L.LEDGER_ID = P_LEDGER

         

         );

  RETURN X_AMOUNT;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    RETURN 0;

  WHEN OTHERS THEN

    RETURN 0;

END XX_GET_AVAIL_PAYMENT_FUNC;