Oracle E-Business Suite Objects Mapping Between 11i and R12

Oracle E-Business Suite Objects Mapping Between 11i and R12

You can download the Oracle E-Business Suite Objects Mapping Between 11i and R12 files from the following link:

Oracle_E-Business_Suite_Objects_Mapping_Between_11i_and_R12_(11.5.10.2_12.1.3).rar

 

 

 

 

 

 

Advertisements

The Link between Oracle General Ledger Journal Line and Cash Management Reconciliation

In Oracle Cash Management Reconciliation when we select available transaction then select Journals (General Ledger Journals) what the journals will appear.

The main view which illustrate the link between General Ledger Journal Lines and Cash Management is CE_101_TRANSACTIONS_V which contains the following tables and views:

  1. CE_STATEMENT_RECONCILIATIONS.
  2. GL_JE_LINES.
  3. GL_JE_HEADERS.
  4. AP_BANK_ACCOUNTS.

The nature of the link between General Ledger Journals Lines and Cash Management Reconciliation is divided to the following relationships:

Relation between AP_BANK_ACCOUNTS and the GL_JE_LINES:

This is the main relation which define the bank account related to the journal line:

WHERE AP_BANK_ACCOUNTS.ASSET_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID

You can use the following statement to get journal lines and its bank accounts:

SELECT H.JE_SOURCE,
       H.JE_CATEGORY, 
       H.JE_HEADER_ID,
       H.DOC_SEQUENCE_VALUE,
       H.DEFAULT_EFFECTIVE_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
  FROM AP_BANK_ACCOUNTS B, 
       GL.GL_JE_LINES   D,
       GL.GL_JE_HEADERS H
 WHERE B.ASSET_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
   AND D.JE_HEADER_ID              = H.JE_HEADER_ID

Relation between CE_STATEMENT_RECONCILIATIONS and the GL_JE_LINES:

This relation show the Journal line reconciled or not.

WHERE CE_STATEMENT_RECONCILIATIONS.JE_HEADER_ID   = GL_JE_LINES.JE_HEADER_ID
  AND CE_STATEMENT_RECONCILIATIONS.REFERENCE_ID   = GL_JE_LINES.JE_LINE_NUM
  AND CE_STATEMENT_RECONCILIATIONS.REFERENCE_TYPE = 'JE_LINE'

You can use the following SQL statements to get a list of Reconcilated journal lines and reconcilation bank statement information:

SELECT H.JE_SOURCE,
       H.JE_CATEGORY, 
       H.JE_HEADER_ID,
       H.DOC_SEQUENCE_VALUE,
       H.DEFAULT_EFFECTIVE_DATE,             
       D.JE_LINE_NUM,
       D.DESCRIPTION,
       D.ACCOUNTED_CR,
       D.ACCOUNTED_DR,
       SH.STATEMENT_HEADER_ID,
       SH.STATEMENT_NUMBER,
       SH.STATEMENT_DATE,
       SD.STATEMENT_LINE_ID,
       SD.LINE_NUMBER          
  FROM CE_STATEMENT_RECONCILIATIONS S ,
       GL.GL_JE_LINES               D ,
       GL.GL_JE_HEADERS             H ,
       CE_STATEMENT_HEADERS_V       SH,
       CE_STATEMENT_LINES_V         SD     
 WHERE SH.STATEMENT_HEADER_ID = SD.STATEMENT_HEADER_ID
   AND S.STATEMENT_LINE_ID    = SD.STATEMENT_LINE_ID
   AND D.JE_HEADER_ID         = H.JE_HEADER_ID
   AND S.JE_HEADER_ID         = D.JE_HEADER_ID
   AND S.REFERENCE_ID         = D.JE_LINE_NUM
   AND S.REFERENCE_TYPE       = 'JE_LINE'

Now you can use the following SQL statements to get the Journal lines which are not reconciled (Available to reconciliation):

SELECT H.JE_SOURCE,
       H.JE_CATEGORY, 
       H.JE_HEADER_ID,
       H.DOC_SEQUENCE_VALUE,
       H.DEFAULT_EFFECTIVE_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
  FROM AP_BANK_ACCOUNTS B, 
       GL.GL_JE_LINES   D,
       GL.GL_JE_HEADERS H
 WHERE B.ASSET_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
   AND D.JE_HEADER_ID              = H.JE_HEADER_ID
   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)