general ledger

This interface lets you load the rates automatically into General Ledger.

Pre-requisites:       Currencies

Conversion rate Types

Interface tables:

GL_DAILY_RATES_INTERFACE

Base tables:

GL_DAILY_RATES

GL_DAILY_CONVERSION_TYPES

Concurrent Program:You do not need to run any import programs. The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.

Validations:             Check if FROM_CURRENCY and TO_CURRENCY are valid.

Check if USER_CONVERSION_TYPE is valid.

Some important columns that need to be populated in the interface tables:

 GL_DAILY_RATES_INTERFACE:

 FROM_CURRENCY

TO_CURRENCY

FROM_CONVERSION_DATE

TO_CONVERSION_DATE

USER_CONVERSION_TYPE

CONVERSION_RATE

MODE_FLAG (D= Delete, I = Insert, U = Update)

INVERSE_CONVERSION_RATE

Oracle GL Budget Interface Details

Budget interface lets you load budget data from external sources into Oracle Applications.

Pre-requisites:Set of Books

Flex field Value sets

Code Combinations

Interface tables:

GL_BUDGET_INTERFACE

Base tables:

GL_BUDGETS

GL_BUDGET_ASSIGNMENTS

GL_BUDGET_TYPES

Concurrent program:

Budget Upload

Validations:

Check if CURRENCY_CODE is valid.

Check if SET_OF_BOOKS_ID is valid.

Check if BUDGET_ENTITY_NAME (budget organization) is valid.

Some important columns that need to be populated in the interface tables:

GL_BUDGET_INTERFACE:

BUDGET_NAME NOT

BUDGET_ENTITY_NAME

CURRENCY_CODE

FISCAL_YEAR

UPDATE_LOGIC_TYPE

BUDGET_ENTITY_ID

SET_OF_BOOKS_ID

CODE_COMBINATION_ID

BUDGET_VERSION_ID

PERIOD_TYPE

DR_FLAG

STATUS

ACCOUNT_TYPE

PERIOD1_AMOUNT through PERIOD60_AMOUNT

SEGMENT1 through SEGMENT30

Oracle GL Journal interface Details

This interface lets you import journals from other applications like Receivables, Payables etc to integrate the information with General Ledger.

Pre-requisites:

Set of Books

Flex field Value sets

Code Combinations

Currencies

Categories

Journal Sources

Interface tables:

GL_INTERFACE

Base tables:

GL_JE_HEADERS

GL_JE_LINES

GL_JE_BACTHES

Concurrent Program:

Journal Import

Journal Posting — populates GL_BALANCES

Validations:

Validate SOB, journal source name, journal category name, actual flag

A – Actual amounts

B – Budget amounts

E – Encumbrance amount

If you enter E in the interface table, then enter appropriate encumbrance ID, if

B enter budget id.

Check if accounting date or GL date based period name is valid (i.e., not closed).

Check if accounting date falls in open or future open period status.

Check chart of accounts id based on Sob id.

Check if code combination is valid and enabled.

Check if record already exists in GL interface table.

Check if already journal exists in GL application.

Some important columns that need to be populated in the interface tables:

GL_INTERFACE:

STATUS

SET_OF_BOOKS_ID

ACCOUNTING_DATE

CURRENCY_CODE

DATE_CREATED

CREATED_BY

ACTUAL_FLAG

USER_JE_CATEGORY_NAME

USER_JE_SOURCE_NAME

CURRENCY_CONVERSION_DATE

ENCUMBRANCE_TYPE_ID

BUDGET_VERSION_ID

USER_CURRENCY_CONVERSION_TYPE

CURRENCY_CONVERSION_RATE

SEGMENT1 to

ENTERED_DR

ENTERED_CR

ACCOUNTED_DR

ACCOUNTED_CR

TRANSACTION_DATE

PERIOD_NAME

JE_LINE_NUM

CHART_OF_ACCOUNTS_ID

FUNCTIONAL_CURRENCY_CODE

CODE_COMBINATION_ID

DATE_CREATED_IN_GL

GROUP_ID

WEDNESDAY, MAY 11, 2016

Query to Get Actuals Vs Budget Values in Oracle General Ledger

SELECT  

  gb.period_name Period_Name,

  (select max(hou.name) from hr_operating_units hou where hou.set_of_books_id=gb.ledger_id)"Organization Name",

  gb.period_year Period_Year,

  gb.actual_flag,

  gb.period_Num Period_Month,

 

  gcc.segment1 "Region",

  (select fft.description from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014150

  and GCC.SEGMENT1=ffv.flex_value)as "Region Name",

 

  gcc.segment2 "Dartepment",

  (select max(fft.description) from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014168

  and GCC.SEGMENT2=ffv.flex_value)as "Department Name",

 

  gcc.segment3 "Account",

  (select max(fft.description) from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014170

  and GCC.SEGMENT3=ffv.flex_value)as "Account Name",

 

  gcc.segment4 "Intercompany",

  (select max(fft.description) from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014150

  and GCC.SEGMENT4=ffv.flex_value)as "Intercompany Name",

 

  gcc.segment5 "Thematic Area",

  gcc.segment6 "Activity",

  gcc.segment7 "Project",

  --gcc.segment8  "Donor",

  gcc.segment9  "Location",

  gcc.segment10  "Contact",

  A1.DESCRIPTION "Region_Desc",

  A2.DESCRIPTION "Department_Desc",

  --A3.DESCRIPTION "Acc_Desc",

  A4.DESCRIPTION "Intercompany_Desc",

  A5.DESCRIPTION "Thematic_Desc",

  A6.DESCRIPTION "Activity_Desc",

  A7.DESCRIPTION "project_Desc",

  --A8.DESCRIPTION "Donor",

  A9.DESCRIPTION "Location_Desc",

  A10.DESCRIPTION "Contact_Desc",

  gb.currency_code Main_Currency,

 

  (select fft.description from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID = '1014154'

  and GCC.SEGMENT9=ffv.flex_value) "State",

 

  (select fft.description from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID = '1014150'

  and GCC.SEGMENT1=ffv.flex_value) "Company",

 

  ( SUM (gb.BEGIN_BALANCE_DR) - SUM (gb.BEGIN_BALANCE_CR)) Begin_Balance,

  SUM (gb.period_net_dr) Period_Debit,

  SUM (gb.period_net_cr) Period_Credit,

  (SUM (gb.period_net_dr) - SUM (gb.period_net_cr)) Period_Activity,

  (( SUM (gb.BEGIN_BALANCE_DR) - SUM (gb.BEGIN_BALANCE_CR)) + (SUM (gb.period_net_dr) - SUM (gb.period_net_cr)))Closing_Balance

 

FROM

  gl_balances gb,

  gl_code_combinations gcc,

  fnd_flex_values_tl A1,

  fnd_flex_values_tl A2,

  --fnd_flex_values_tl A3,

  fnd_flex_values_tl A4,

  fnd_flex_values_tl A5,

  fnd_flex_values_tl A6,

  fnd_flex_values_tl A7,

  --fnd_flex_values_tl A8,

  fnd_flex_values_tl A9,

  fnd_flex_values_tl A10,

  fnd_flex_values B1,

  fnd_flex_values B2,

  --fnd_flex_values B3,

  fnd_flex_values B4,

  fnd_flex_values B5,

  fnd_flex_values B6,

  fnd_flex_values B7,

  --fnd_flex_values B8,

  fnd_flex_values B9,

  fnd_flex_values B10

WHERE 1=1

  AND b.LEDGER_ID = '1'

  AND gb.currency_code = 'USD'

  AND gb.actual_flag <> 'E'

  AND gb.code_combination_id = gcc.code_combination_id

  and b1.FLEX_VALUE_SET_ID ='1014150'

  and b2.FLEX_VALUE_SET_ID ='1014168'

  --and b3.FLEX_VALUE_SET_ID ='1014149'

  and b4.FLEX_VALUE_SET_ID ='1014150'

  and b5.FLEX_VALUE_SET_ID ='1014187'

  and b6.FLEX_VALUE_SET_ID ='1014154'

  and b7.FLEX_VALUE_SET_ID ='1014154'

  --and b8.FLEX_VALUE_SET_ID ='1014153'

  and b9.FLEX_VALUE_SET_ID ='1014154'

  and b10.FLEX_VALUE_SET_ID ='1014756'

  and  A1.FLEX_VALUE_ID = B1.FLEX_VALUE_ID

  and  A2.FLEX_VALUE_ID = B2.FLEX_VALUE_ID

  --and  A3.FLEX_VALUE_ID = B3.FLEX_VALUE_ID

  and  A4.FLEX_VALUE_ID = B4.FLEX_VALUE_ID

  and  A5.FLEX_VALUE_ID = B5.FLEX_VALUE_ID

  and  A6.FLEX_VALUE_ID = B6.FLEX_VALUE_ID

  and  A7.FLEX_VALUE_ID = B7.FLEX_VALUE_ID

  --and  A8.FLEX_VALUE_ID = B8.FLEX_VALUE_ID

  and  A9.FLEX_VALUE_ID = B9.FLEX_VALUE_ID

  and  A10.FLEX_VALUE_ID = B10.FLEX_VALUE_ID

  and B1.FLEX_VALUE = gcc.segment1

  and B2.FLEX_VALUE = gcc.segment2

  --and B3.FLEX_VALUE = gcc.segment3

  and B4.FLEX_VALUE = gcc.segment4

  and B5.FLEX_VALUE = gcc.segment5

  and B6.FLEX_VALUE = gcc.segment6

  and B7.FLEX_VALUE = gcc.segment7

  --and B8.FLEX_VALUE = gcc.segment8

  and B9.FLEX_VALUE = gcc.segment9

  and B10.FLEX_VALUE = gcc.segment10

  and gcc.segment1  in (@p_Acc_Seg1)

  and gcc.segment2  in ( @p_Acc_Seg2)

  --and gcc.segment3 in  (:p_Acc_Seg3)

  and gcc.segment4  in ( @p_Acc_Seg4)

  AND gb.period_name  in (@p_Period)

 

GROUP BY gb.period_name,

         gb.period_year,

         gb.PERIOD_NUM,

         gb.ledger_id,       

         gcc.chart_of_accounts_id,

         gb.actual_flag,

         gcc.segment1,

         gcc.segment2,

         --gcc.segment3,

         gcc.segment4,

         gcc.segment5,

         gcc.segment6,

         gcc.segment7 ,

         --gcc.segment8  ,

         gcc.segment9 ,

         gcc.segment10  ,

         gcc.code_combination_id,

         A1.DESCRIPTION,

         A2.DESCRIPTION,

         --A3.DESCRIPTION,

         A4.DESCRIPTION,

         A5.DESCRIPTION,

         A6.DESCRIPTION,

         A7.DESCRIPTION ,

         --A8.DESCRIPTION ,

         A9.DESCRIPTION ,

         A10.DESCRIPTION,

         gb.currency_code

         

Order by gb.PERIOD_YEAR, gb.PERIOD_NUM