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)