GL Periods

GL Periods

GL Date personalization in Journal Entry Form

User Requirements

Case	System Date	GL Date Basis	User Selected GL_date on Invoice Workbench Head/Lines/Distribution	Functionality required
I	20-Nov-2013	Current Date	20-Nov-2013	Allow to enter the transactions
II	20-Nov-2013	Date greater than system date	21-Nov-2013	Not Allowed to enter the transaction
III	20-Nov-2013	Date earlier than system date	19-Nov-2013	Not Allowed to enter the transaction
IV	20-Nov-2013	Last Date of immediate payables period.	31-Oct-2013	Allow to enter the transactions
V	20-Nov-2013	Any other date of previous periods.	30-Oct-2013	Not Allowed to enter the transaction

Implementation Areas

Responsibilities:

ü  Account Payables: Invoices, Payments

x  Account Receivables: Transactions, Receipts

ü  General Ledger: Journals

(Note: Functional Requirements are invoked from Account Receivables. Requirements are implemented only for Account Payables, General Ledger Responsibilities)

Variables:

Super User:

1.       Super User can make Today Transaction and as well as Last Date of Last Month Transaction. But he can’t make yesterday Transaction.

(This Facility has provided for complete month. i.e., Date 01- Date 30/31) 

2.       Super Users are 1. DEEPAK 2. AMIT JAIN (Account Payables).

                              1. AJAYGUPTA 2. AMIT JAIN  (General Ledgers).

Normal User:

1.       Normal User can make Today Transaction and Last Date of Last Month Transaction. But he can’t make yesterday Transaction.

         (This Facility has provided First 10 Days. i.e., From Date 01 – Date 10).

2.       Normal User can make Today Transaction, But he can’t make transaction for Yesterday as well as Last Date of Last Month)

(This Facility has provided Last 20 Days.  i.e., From Date 11 – Date 30/31).

3.       Normal Users are comes under AP Msf User, AP Accounting Msf User, Payables Mangers, GL Msf User, GAL User Max India.

General Ledger à Journals

GL Account Balance

  SELECT gb.ledger_id, gb.period_name,

         gcc.concatenated_segments Code_combination,

         SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))

            beginning_bal,

         SUM (

              NVL (gb.begin_balance_dr, 0)

            - NVL (gb.begin_balance_cr, 0)

            + (NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0)))

            end_bal

    FROM gl_balances gb, gl_code_combinations_kfv gcc

   WHERE     gb.code_combination_id = gcc.code_combination_id

         --AND gcc.CONCATENATED_SEGMENTS = '01.000.1110.0000.000' -- Enter GL Account

         AND gb.ledger_id = 2021  -- Enter the Ledger

         AND gb.Actual_flag = 'A'

         AND gb.period_name           = 'APR-14' --Enter the Period

         AND gb.currency_code = (SELECT currency_code

                                   FROM gl_ledgers

                                  WHERE ledger_id = gb.ledger_id)

GROUP BY gcc.concatenated_segments, gb.ledger_id, gb.period_name

ORDER BY 3

GL- Interface

GL- Interface

GL Accounts and Its description

SELECT gcc.concatenated_segments account,

       gl_flexfields_pkg.get_concat_description (gcc.chart_of_accounts_id,

                                                 gcc.code_combination_id)

          description

  FROM gl_code_combinations_kfv gcc

Key Flex Field Structures & How to Retrieve Information about segments based on code combination id

--Key Flex Field Structures & How to Retrieve Information about segments based on code combination id

---

-- How to fetch meanings of each segment related to a code combination id?

--

--Tables related to Flexi fields:

FND_ID_FLEXS

FND_ID_FLEX_SEGMENTS

FND_ID_FLEX_STRUCTURES

FND_FLEX_VALUES

FND_FLEX_VALUE_HIERARCHIES

--Below queries are used by me to reach out the main query..

/*

QUERY 1:

 The table (fnd_id_flexs) used in the below query stores registration information about key flexfields.

 Each row includes the four–character code that identifies the key flexfield and can be used to dig further

 The "id_flex_code" is the column which can be used to dig further */

SELECT application_table_name

      ,DECODE (application_table_type,

               'S','Specific',

               'G','Generic'

              ) table_type

      ,concatenated_segs_view_name

      ,set_defining_column_name

      ,unique_id_column_name

      ,description

      ,id_flex_code  -- Used to drill down

FROM   fnd_id_flexs

WHERE id_flex_name LIKE 'Accounting%';

/*

QUERY 2:

The below query gives information about the segment meanings

We got this data by using the value we got for "id_flex_code"  in query 1 */

SELECT segment_name

      ,segment_num

      ,enabled_flag

      ,required_flag

      ,flex_value_set_id  -- Used to drill down

      ,additional_where_clause

FROM  fnd_id_flex_segments

WHERE id_flex_code = 'GL#';

/*

QUERY 3:

This query can be used to get various possible values for each segment. */

SELECT ffv.flex_value_set_id

      ,ffv.flex_value_id

      ,ffv.flex_value

      ,ffv.enabled_flag

      ,ffvt.LANGUAGE

      ,ffvt.description

FROM   fnd_flex_values ffv

      ,fnd_flex_values_tl ffvt

WHERE  ffv.flex_value_id = ffvt.flex_value_id

AND    ffv.flex_value_set_id = '&id from query 2';

/*

Main Query

 Below is our highlight and it can be used to get all possible information based on code combination id

 I had simplified the same. The decode statement differs from client to client. But it can be framed with use of query 2

*/

SELECT fifs.segment_name

      ,fifs.segment_num

      ,ffv.flex_value

      ,ffvt.description

FROM  fnd_flex_values ffv

     ,fnd_flex_values_tl ffvt

     ,fnd_id_flex_segments fifs

     ,gl_code_combinations glv

WHERE ffv.flex_value_id = ffvt.flex_value_id

AND   ffv.flex_value_set_id = fifs.flex_value_set_id

AND   fifs.id_flex_code = 'GL#'

AND   ffv.flex_value    = DECODE(fifs.segment_name

                                ,'BUSINESS UNIT',glv.segment1

                                ,'LOCATION'     ,glv.segment2

                                ,'DEPARTMENT'   ,glv.segment3

                                ,'ACCOUNT'      ,glv.segment4

                                ,'PROJECT'      ,glv.segment5

                                ,'INTERCOMPANY' ,glv.segment6

                                ,'SPARE'        ,glv.segment7

                                )

AND  glv.code_combination_id = '&code_combination_id'

ORDER BY ffv.flex_value_set_id;