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)

AP Payment Voucher Query

/********************************************** 

    Creation Date : 30 September, 2016

    Purpose       : AP Payment Voucher Query

    Module        : Payable

    Parameters    : Cheque No, Payment Voucher No

                    and Payment Date

    Created By    : Muhammad Waqas Khan

   

*********************************************/

SELECT CHK.DOC_SEQUENCE_VALUE "PAYMENT VOUCHER",

       CHK.CURRENCY_CODE "PAYMENT CURRENCY",

       CHK.CHECK_DATE "PAYMENT DATE",

       CHECK_NUMBER "PAYMENT NO.",

       CHK.FUTURE_PAY_DUE_DATE "CHECK_DATE", --MATURITY DATE

       CHK.PAYMENT_METHOD_CODE "PAYMENT TYPE",

       BNK.BANK_NAME "BANK NAME",

       CHK.BANK_ACCOUNT_NUM "BANK ACCOUNT NO.",

       CHK.VENDOR_NAME "PAID TO",

       CHK.AMOUNT "AMOUNT",

       AIA.INVOICE_NUM "INVOICE NO.",

       AIA.INVOICE_DATE "INVOICE DATE",

       AIA.DESCRIPTION "INVOICE DESCRIPTION",

       AIA.INVOICE_AMOUNT "INVOICE AMOUNT",

       SUPP_BANK.BANK_NAME "PAID TO BANK NAME",

       SUPP_BANK.BANK_ACCOUNT_NUM "PAID TO BANK ACCOUNT NO.",

       CHK.EXCHANGE_RATE CONVERSION_RATE,

       TO_CHAR((NVL(CHK.EXCHANGE_RATE, 1) * CHK.AMOUNT), '9,999.99') FUNCTIONAL_AMOUNT

  FROM AP_INVOICE_PAYMENTS_ALL IPA,

       AP_CHECKS_ALL CHK,

       AP_INVOICES_ALL AIA,

       FND_USER U,

       CE_BANK_ACCOUNTS BNKACC,

       CE_BANKS_V BNK,

       XLE_ENTITY_PROFILES LE,

       (SELECT APS.VENDOR_ID,

               HOP_BANK.ORGANIZATION_NAME BANK_NAME,

               IEBA.BANK_ACCOUNT_NUM

          FROM HZ_PARTIES               HZP,

               AP_SUPPLIERS             APS,

               IBY_EXTERNAL_PAYEES_ALL  HEPA,

               IBY_PMT_INSTR_USES_ALL   IPIUA,

               IBY_EXT_BANK_ACCOUNTS    IEBA,

               HZ_PARTIES               HZP_BANK,

               HZ_ORGANIZATION_PROFILES HOP_BANK

         WHERE HZP.PARTY_ID = APS.PARTY_ID

           AND HZP.PARTY_ID = HEPA.PAYEE_PARTY_ID

           AND HEPA.EXT_PAYEE_ID = IPIUA.EXT_PMT_PARTY_ID(+)

           AND IPIUA.INSTRUMENT_ID = IEBA.EXT_BANK_ACCOUNT_ID(+)

           AND IEBA.BANK_ID = HZP_BANK.PARTY_ID(+)

           AND HOP_BANK.PARTY_ID(+) = HZP_BANK.PARTY_ID

           AND HEPA.SUPPLIER_SITE_ID IS NULL) SUPP_BANK

 WHERE LE.LEGAL_ENTITY_ID = CHK.LEGAL_ENTITY_ID

   AND CHK.CHECK_ID = IPA.CHECK_ID

   AND BNK.BANK_PARTY_ID = BNKACC.BANK_ID

   AND BNKACC.BANK_ACCOUNT_NAME = CHK.BANK_ACCOUNT_NAME

   AND BNKACC.BANK_ACCOUNT_NUM = CHK.BANK_ACCOUNT_NUM

   AND AIA.INVOICE_ID = IPA.INVOICE_ID

   AND AIA.VENDOR_ID = SUPP_BANK.VENDOR_ID(+)

   AND CHK.CREATED_BY = U.USER_ID(+)

   AND CHK.LAST_UPDATED_BY = U.USER_ID(+)

   AND CHK.STATUS_LOOKUP_CODE <> 'VOIDED'

   --AND CHK.CHECK_NUMBER = '329'

      ------ PARAMETERS ----------------

   AND CHK.CHECK_NUMBER = NVL(:P_CHAQUE_NUMBER, CHK.CHECK_NUMBER)

   AND NVL(CHK.DOC_SEQUENCE_VALUE, 0) BETWEEN

       NVL(:P_VOUCHER_NO_FROM, NVL(CHK.DOC_SEQUENCE_VALUE, 0)) AND

       NVL(:P_VOUCHER_NO_TO, NVL(CHK.DOC_SEQUENCE_VALUE, 0))

   AND IPA.ORG_ID = NVL(:P_ORG_ID, IPA.ORG_ID)

   AND CHK.CHECK_DATE BETWEEN NVL(:FROM_DATE, CHK.CHECK_DATE) AND

       NVL(:TO_DATE, CHK.CHECK_DATE)

 ORDER BY CHK.DOC_SEQUENCE_VALUE,

          CHK.CHECK_NUMBER,

          AIA.INVOICE_NUM,

          AIA.INVOICE_DATE