Available Receipts For Reconciliation View

CREATE OR REPLACE VIEW XX_GET_AVAILABLE_RECEIPTS_VIEW AS

SELECT (CASE

         WHEN C.REMIT_BANK_CURRENCY <> C.CURRENCY_CODE THEN

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

         ELSE

          C.AMOUNT

       END) AMOUNT,

       C.REMIT_BANK_CURRENCY,

       CBA.BANK_ACCOUNT_ID,

       C.STATE_DSP,

       C.ORG_ID,

       C.REMIT_BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

       C.GL_DATE EFFECTIVE_DATE,

       L.LEDGER_ID

  FROM AR_CASH_RECEIPTS_V C,

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

       CE_BANK_ACCOUNTS CBA

 WHERE C.STATE_DSP = 'Remitted'

   AND L.ORG_ID = C.ORG_ID

   AND CBA.BANK_ACCOUNT_NUM = C.REMIT_BANK_ACCOUNT_NUM

   AND C.PAYMENT_METHOD_DSP LIKE '%CDC%' -- I have Hard Code Only For Current Dated Cheque

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;

Available JV For Reconciliation

CREATE OR REPLACE VIEW XX_GET_AVAILABLE_JV_VIEW AS

SELECT H.JE_SOURCE,

       H.JE_CATEGORY,

       H.JE_HEADER_ID,

       H.DOC_SEQUENCE_VALUE,

       H.DEFAULT_EFFECTIVE_DATE GL_DATE,

       D.JE_LINE_NUM,

       D.DESCRIPTION,

       D.ACCOUNTED_CR,

       D.ACCOUNTED_DR,

       B.BANK_ACCOUNT_NAME,

       B.BANK_ACCOUNT_NUM,

       B.BANK_ACCOUNT_TYPE,

       H.LEDGER_ID,

       B.CURRENCY_CODE,

       B.BANK_ACCOUNT_ID,

       H.CURRENCY_CODE JV_CUR

  FROM CE_BANK_ACCOUNTS B, GL_JE_LINES D, GL_JE_HEADERS H

 WHERE B.ASSET_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID

   AND D.JE_HEADER_ID = H.JE_HEADER_ID

   AND H.JE_CATEGORY = '1'

   AND H.JE_SOURCE = 'Manual'

   AND NOT EXISTS

 (SELECT 0

          FROM CE_STATEMENT_RECONCILIATIONS P, GL.GL_JE_LINES K

         WHERE P.JE_HEADER_ID = K.JE_HEADER_ID

           AND P.REFERENCE_ID = K.JE_LINE_NUM

           AND P.REFERENCE_TYPE = 'JE_LINE'

           AND K.JE_HEADER_ID = H.JE_HEADER_ID

           AND K.JE_LINE_NUM = D.JE_LINE_NUM)