Query to get Unposted Journals in Oracle APPS R12

SELECT gjh.accrual_rev_flag accrual_rev_flag

      ,gjh.accrual_rev_period_name accrual_rev_period_name

  ,gjh.creation_date journal_creation_date

  ,gjh.currency_code currency_code

  ,gjh.doc_sequence_value doc_sequence_value

  ,fu.email_address email_address

  ,gjh.je_category je_category

  ,gjh.last_update_date last_update_date

  ,gjh.name name journal_header_name

  ,gsobv.name ledger_name

  ,gjh.period_name period_name

  ,gjh.posted_date posted_date

  ,gjh.reversed_je_header_id reversed_je_header_id

  ,gjh.running_total_accounted_dr running_total_accounted_dr

  ,gjh.status status

  ,fu.user_name user_name

  ,gjsv.user_je_source_name user_je_source_name

  ,fu1.user_name user_name

  ,gjbv.name journal_batch_name

  ,gjbv.posted_by_name posted_by_name

  ,ppx.email_address email_address

  ,period.period_set

  ,CASE WHEN ( ( MIN(fad.attached_document_id) ) IS NOT NULL OR ( MIN(fad1.attached_document_id) ) IS NOT NULL ) 

        THEN 'Y' 

ELSE 'N' 

END document_exists

  FROM applsys.fnd_user fu

      ,gl.gl_je_headers gjh

  ,apps.gl_sets_of_books_v gsobv

  ,applsys.fnd_user fu1

  ,apps.gl_je_sources_v gjsv

  ,apps.gl_je_batches_v gjbv

  ,apps.per_people_x ppx

  ,applsys.fnd_attached_documents fad

  ,applsys.fnd_attached_documents fad1

  ,(SELECT gpv.period_name AS period_name

          ,MAX((gpv.period_set_name||gpv.period_year||LPAD(gpv.period_num,2,0))) AS period_set 

  FROM apps.gl_periods_v gpv 

WHERE (gpv.period_year >= 2024) 

GROUP BY gpv.period_name

) period

 WHERE ( (gsobv.set_of_books_id = gjh.ledger_id)

   AND (fu1.employee_id = ppx.person_id(+))

   AND (gjh.last_updated_by = fu.user_id(+))

   AND (gjh.created_by = fu1.user_id(+))

   AND (gjh.je_batch_id = gjbv.je_batch_id(+))

   AND ((TO_CHAR(gjbv.je_batch_id)) = fad.pk1_value(+))

   AND ((TO_CHAR(gjh.je_header_id)) = fad1.pk2_value(+))

   AND (gjh.je_source = gjsv.je_source_name)

   AND (gjh.period_name = period.period_name(+)))

   AND (fad.entity_name(+) = 'GL_JE_BATCHES')

   AND (fad1.entity_name(+) = 'GL_JE_HEADERS')

   AND (gjh.posted_date >= :p_posted_date)

   AND (gjsv.user_je_source_name IN (:p_source))

   AND (gjh.status IN ('U'))

   AND (gjh.period_name = :p_period_name)

GROUP BY period.period_set

        ,gjh.accrual_rev_flag

,gjh.accrual_rev_period_name

,gjh.creation_date

,gjh.currency_code

,gjh.doc_sequence_value

,fu.email_address,gjh.je_category

,gjh.last_update_date

,gjh.name

,gsobv.name

,gjh.period_name

,gjh.posted_date

,gjh.reversed_je_header_id

,gjh.running_total_accounted_dr

,gjh.running_total_dr

,gjh.status

,fu.user_name

,gjsv.user_je_source_name

,fu1.user_name

,gjbv.name

,gjbv.posted_by_name

,ppx.email_address

ORDER BY gsobv.name ASC 

        ,period.period_set ASC 

,gjsv.user_je_source_name ASC 

,gjh.doc_sequence_value ASC 

Query to get Parent and Child Accounts in Oracle APPS R12

SELECT ffv1.flex_value parent_account

      ,ffvt1.description parent_account_desc

  ,ffv2.flex_value child_account

  ,ffvt2.description child_account_desc

  FROM fnd_flex_values_tl ffvt2

      ,fnd_flex_values ffv2

      ,fnd_flex_value_hierarchies ffvh

      ,fnd_flex_values_tl ffvt1

      ,fnd_flex_values ffv1

      ,fnd_id_flex_segments fifs

 WHERE fifs.application_id = 101

   AND fifs.id_flex_code = 'GL#'

   AND fifs.id_flex_num = 101

   AND fifs.application_column_name = 'SEGMENT4'

   AND fifs.enabled_flag = 'Y'

   AND ffv1.enabled_flag = 'Y'

   AND ffv1.summary_flag = 'Y'

   AND ffv1.flex_value != 'T'

   AND ffv1.flex_value_set_id = fifs.flex_value_set_id + 0

   AND NVL (TO_CHAR (ffv1.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND NVL (TO_CHAR (ffv1.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND ffvt1.flex_value_id = ffv1.flex_value_id + 0

   AND ffvt1.language = USERENV ('LANG')

   AND ffvh.flex_value_set_id = fifs.flex_value_set_id + 0

   AND ffvh.parent_flex_value = ffv1.flex_value

   AND ffv2.summary_flag = 'N'

   AND NVL (TO_CHAR (ffv2.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND NVL (TO_CHAR (ffv2.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND ffv2.flex_value_set_id + 0 = fifs.flex_value_set_id + 0

   AND ffv2.flex_value BETWEEN ffvh.child_flex_value_low AND ffvh.child_flex_value_high

   AND ffv2.enabled_flag = 'Y'

   AND ffvt2.flex_value_id = ffv2.flex_value_id + 0

   AND ffvt2.language = USERENV ('LANG')

ORDER BY ffv1.flex_value, ffv2.flex_value

Query to get GL Journal Details in R12

SELECT JEL.JE_HEADER_ID

      ,JEL.JE_LINE_NUM

  ,JEL.LAST_UPDATE_DATE

      ,JEL.LAST_UPDATED_BY

  ,JEL.LEDGER_ID

  ,JEL.CODE_COMBINATION_ID

      ,JEL.PERIOD_NAME

  ,JEL.STATUS

  ,JEL.CREATION_DATE

  ,JEL.CREATED_BY

  ,JEL.ENTERED_DR

  ,JEL.ENTERED_CR

  ,JEL.ACCOUNTED_DR

  ,JEL.ACCOUNTED_CR

  ,JEL.REFERENCE_1

  ,JEL.REFERENCE_2

  ,JEL.REFERENCE_3

  ,JEL.REFERENCE_4

  ,JEL.REFERENCE_5

  ,JEL.REFERENCE_6

  ,JEL.REFERENCE_7

  ,JEL.REFERENCE_8

  ,JEL.REFERENCE_9

  ,JEL.REFERENCE_10

  ,JEL.GL_SL_LINK_ID

  ,JEH.JE_CATEGORY

  ,JEH.JE_SOURCE

  ,JEH.NAME

  ,JEH.CURRENCY_CODE

  ,JEH.POSTED_DATE

  ,JEB.NAME

  ,PRDS.START_DATE

  ,PRDS.END_DATE

  ,GL.LEDGER_CATEGORY_CODE

  ,PRDS.ADJUSTMENT_PERIOD_FLAG 

  FROM GL_JE_LINES JEL

      ,GL_JE_HEADERS JEH

      ,GL_JE_BATCHES JEB

      ,GL_PERIOD_STATUSES PRDS

  ,GL_LEDGERS GL 

 WHERE JEL.JE_HEADER_ID = JEH.JE_HEADER_ID 

   AND JEH.ACTUAL_FLAG = 'A' 

   AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID (+) 

   AND JEL.PERIOD_NAME = PRDS.PERIOD_NAME 

   AND JEL.LEDGER_ID = PRDS.SET_OF_BOOKS_ID 

   AND JEL.LEDGER_ID = GL.LEDGER_ID 

   AND PRDS.APPLICATION_ID = 101 

   AND JEH.CURRENCY_CODE <> 'STAT' 

   AND JEB.STATUS = 'P' 

Is there any API to End Date GL Code Combinations from Backend in Oracle APPS

As per the Oracle as of now there is no API to End Date GL Code Combinations from Backend. Please refer below notes from Oracle Support.

• Is Effective Date From / 'Start Date Active' Mandatory for GL Code Combinations? Can it Be Changed to "null" Value? (Doc ID 2763034.1)
• Check if these details could have been ported from the segment values, in that case verify if you want to have Start/End Date for the segment values left in the system while the same details would be removed for the code combinations
• Any sql update on gl_code_combinations table will not be supported in any way. You must perform the changes in the code combinations form, manually.
• To port changes from segment values to code combinations, you may use Program - Inherit Segment Value Attributes (see referenced documents)
• GLNSVI: What is the Purpose of the Segment Value Inheritance Program/ Program - Inherit Segment Value Attributes ? (Doc ID 170569.1)

Query to get GL Account Description in Oracle APPS using GL_FLEXFIELDS _PKG

SELECT gcc.code_combination_id

  ,gcc.segment1

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1) SEGMENT1_DESC

  ,gcc.segment2

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2) SEGMENT2_DESC

  ,gcc.segment3

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3) SEGMENT3_DESC

  ,gcc.segment4

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4) SEGMENT4_DESC

  ,gcc.segment5

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5) SEGMENT5_DESC

  ,gcc.segment6

  ,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6) SEGMENT6_DESC

FROM gl_code_combinations gcc

WHERE gcc.code_combination_id = 100000

Here gl_flexfields_pkg.get_description_sql has below parameters:

1. Chart of Account ID
2. Number of the Segment
3. Segment value for which we need Description

Reconciled & Unreconciled Transactions Reports in Oracle Fusion

• Reconciled Transactions Report provides details of clearing account journal lines that have been successfully reconciled by both automatic and manual reconciliation processes.

• Unreconciled Transactions Report lists all unreconciled clearing account journal lines, specifically the unreconciled lines that weren't subjected to reconciliation yet, those lines rejected by previous automatic or manual reconciliation processes, and those lines that were marked as reviewed.

Journals Day Book Report in Oracle Fusion

• This report provides posted journal entries and journal details chronologically by accounting date for a specified range of dates, journal source, and journal category.
• Report sorts journal entries for each accounting date by document number.
• It prints the accounting date, document number, journal entry name, journal source and category, subledger document name and number, currency, and conversion rate.
• Report prints for each journal line, the line number, account segment value and description, functional debit and credit amounts, description, and cost center segment value.

Journals Details Report in Oracle Fusion

• This report provides information about manually entered journals prior to posting, including field by field, all data entered into the applications or data imported from external sources.

Journals Batch Summary Report in Oracle Fusion

• This report lists posted journal batches for a particular ledger, balancing segment value, currency, and date range. 
• It provides information about actual balances for your journal batches, source, batch, and posting dates, total entered debits and credits. 
• Report sorts the information by journal batch within each journal entry category.
• This report includes totals for each journal category and a grand total for each ledger and balancing segment value combination.
• It doesn't report on budget or encumbrance balances.

General Journals Report in Oracle Fusion

• This report provides journal activity for a given period or range of periods, balancing segment value, currency, and range of account segment values.

Journals Report in Oracle Fusion

• This report provides Journal activity for a given period or range of periods, balancing segment value, currency, and range of account segment values.
• It also prints the accounting date, category, journal name, reference, journal batch name, entered debit or credit amounts, net balance, and account total for each journal.
• Report includes a total for each balancing segment and a grand total for all the activity. 

Average Balance Audit Account Analysis Report in Oracle Fusion

• This Report displays the detail account activity that created the aggregate balances and related average balances.
• It displays daily Average Balance information for the selected accounts for the specified range of dates.
• It contains parameters such as the As-Of reporting date, average balance type (period,quarter, or year average-to-date), and account ranges.

Account Analysis for Contra Account Report in Oracle Fusion

• This report Prints balances by account segment and a secondary segment.
• It lists the contra account for each journal entry and the subledger document number for transactions imported from subledgers.
• It prints by date range, accounting Flex Field range, contra account, and amount range.

General Ledger Account Details Report in Oracle Fusion

• Provides journal information to trace each transaction back to its original source.
• Prints a separate page for each balancing segment value.
• For each journal line it prints the account affected, the concatenated description, the journal line amount, and the beginning and ending account balance.
• It also prints Journal details including source, category, journal name, and effective date.
• Report lists accounts in ascending order by account segment value.
• It prints a CR next to credit amounts

Account Analysis Report in Oracle Fusion General Ledger

• Prints balances by account segment and a secondary segment for each journal entry.
• Lists the subledger document number for transactions imported from subledgers. 

Balance Type (Year to Date, Project to Date, Period to Date and Quarter to Date)in Oracle General Ledger

SELECT lookup_code, meaning, description, REPLACE(description, '-', ' ') balance_type

  FROM fnd_lookup_values

 WHERE 1 = 1 

    AND lookup_type = 'PTD_YTD'

    AND language = USERENV('LANG')

API to get the Segment Delimiter for the specified (KFF)Key Flex Field Structure using FND_FLEX_EXT.GET_DELIMITER in Oracle APPS

DECLARE

  l_application_short_name VARCHAR2(50);--fnd_application.application_short_name

  l_key_flex_code          VARCHAR2(200);

  l_structure_number       NUMBER; -- gl_ledgers.chart_of_accounts_id

  l_segment_delimiter      VARCHAR2(10);

BEGIN

l_application_short_name := 'SQLGL';

l_key_flex_code          := 'GL#';

l_structure_number       := 51234;

l_segment_delimiter := fnd_flex_ext.get_delimiter

                    (

                      application_short_name => l_application_short_name

                     ,key_flex_code          => l_key_flex_code

                     ,structure_number       => l_structure_number

                    ); 

DBMS_OUTPUT.PUT_LINE('l_segment_delimiter = ' || l_segment_delimiter);

END;

GL Table Changes R12 in Oracle APPS

SL_NO	11i TABLES	R12 TABLES
1	GL_ACCOUNT_HIERARCHIES	GL_ACCOUNT_HIERARCHIES
2	GL_AHE_DETAIL_RANGES_GT	GL_AHE_DETAIL_RANGES_GT
3	GL_ALLOC_BATCHES	GL_ALLOC_BATCHES
4	GL_ALLOC_FORMULA_LINES	GL_ALLOC_FORMULA_LINES
5	GL_ALLOC_FORMULAS	GL_ALLOC_FORMULAS
6	GL_ALLOC_HISTORY	GL_ALLOC_HISTORY
7	GL_ALLOC_SEGVALS_INT
8	GL_APPLICATION_GROUPS
9	GL_ARCHIVE_HISTORY	GL_ARCHIVE_HISTORY
10	GL_AUTHORIZATION_LIMITS	GL_AUTHORIZATION_LIMITS
11	GL_AUTO_ALLOC_BAT_HIST_DET	GL_AUTO_ALLOC_BAT_HIST_DET
12	GL_AUTO_ALLOC_BATCH_HISTORY	GL_AUTO_ALLOC_BATCH_HISTORY
13	GL_AUTO_ALLOC_BATCHES	GL_AUTO_ALLOC_BATCHES
14	GL_AUTO_ALLOC_REV_BATCHES	GL_AUTO_ALLOC_REV_BATCHES
15	GL_AUTO_ALLOC_SET_HISTORY	GL_AUTO_ALLOC_SET_HISTORY
16	GL_AUTO_ALLOC_SETS	GL_AUTO_ALLOC_SETS
17	GL_AUTOMATIC_POSTING_OPTIONS	GL_AUTOMATIC_POSTING_OPTIONS
18	GL_AUTOMATIC_POSTING_SETS	GL_AUTOMATIC_POSTING_SETS
19	GL_AUTOREVERSE_OPTIONS	GL_AUTOREVERSE_OPTIONS
20	GL_BALANCES	GL_BALANCES
21	GL_BALANCES_DELTA	GL_BALANCES_DELTA
22	GL_BALANCES_DELTA_INTERIM	GL_BALANCES_DELTA_INTERIM
23	GL_BC_DUAL	GL_BC_DUAL
24	GL_BC_DUAL2	GL_BC_DUAL2
25	GL_BC_EVENT_TIMESTAMPS	GL_BC_EVENT_TIMESTAMPS
26	GL_BC_OPTION_DETAILS	GL_BC_OPTION_DETAILS
27	GL_BC_OPTIONS	GL_BC_OPTIONS
28	GL_BC_PACKET_ARRIVAL_ORDER	GL_BC_PACKET_ARRIVAL_ORDER
29	GL_BC_PACKETS	GL_BC_PACKETS
30	GL_BC_PERIOD_MAP	GL_BC_PERIOD_MAP
31	GL_BIS_SEGVAL_INT	GL_BIS_SEGVAL_INT
32	GL_BUDGET_ASSIGNMENT_RANGES	GL_BUDGET_ASSIGNMENT_RANGES
33	GL_BUDGET_ASSIGNMENTS	GL_BUDGET_ASSIGNMENTS
34	GL_BUDGET_BATCHES	GL_BUDGET_BATCHES
35	GL_BUDGET_ENTITIES	GL_BUDGET_ENTITIES
36	GL_BUDGET_FROZEN_RANGES	GL_BUDGET_FROZEN_RANGES
37	GL_BUDGET_INTERFACE	GL_BUDGET_INTERFACE
38	GL_BUDGET_INTERIM	GL_BUDGET_INTERIM
39	GL_BUDGET_PERIOD_RANGES	GL_BUDGET_PERIOD_RANGES
40	GL_BUDGET_RANGE_INTERIM	GL_BUDGET_RANGE_INTERIM
41	GL_BUDGET_TYPES	GL_BUDGET_TYPES
42	GL_BUDGET_VERSIONS	GL_BUDGET_VERSIONS
43	GL_BUDGETS	GL_BUDGETS
44	GL_CARRYFORWARD_RANGES	GL_CARRYFORWARD_RANGES
45	GL_CODE_COMBINATIONS	GL_CODE_COMBINATIONS
46	GL_CONCURRENCY_CONTROL	GL_CONCURRENCY_CONTROL
47	GL_CONCURRENT_SCHEDULES	GL_CONCURRENT_SCHEDULES
48	GL_CONS_BATCHES	GL_CONS_BATCHES
49	GL_CONS_FLEX_HIERARCHIES	GL_CONS_FLEX_HIERARCHIES
50	GL_CONS_FLEXFIELD_MAP	GL_CONS_FLEXFIELD_MAP