Generation of GL code combinations by Sql

Here is workaround for automatic/mass CCID generation or insertion of new CCID’s in GL_CODE_COMBINATIONS Table, when new values in Accounting Flexfield Segment are created, EXCEPT “Dynamic Insert” option activated .

you can use the api (function) -> fnd_flex_ext.get_ccid to create the ccid.

If the combination already exists, it returns the existing ccid else creates a new one and returns the id. Explicit commit command needs to be executed after calling this function.

 

Use below function which returns the CCID if it is exist and create the CCID if it doesn't exist in the application.

fnd_flex_ext.get_ccid(application_short_name ='SQLGL', key_flex_code ='GL#', structure_number =l_coa_id, 

validation_date =to_char(SYSDATE,apps.fnd_flex_ext.date_format), 

concatenated_segments =l_account); 

Initialize the mandatory parameter if you are running the code in SQL Plus/TOAD

 
BEGIN
    FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_appl_id);
 END;

Query to Find the Flex Structure 

SELECT fifst.STRUCTURE_VIEW_NAME,
fifst.CONCATENATED_SEGMENT_DELIMITER,
fifs.application_column_name,
fifs.segment_name,
fifs.segment_num,
ffs.flex_value_set_name
FROM fnd_id_flex_structures_vl fifst,
fnd_id_flex_segments fifs,
fnd_flex_value_sets ffs
WHERE fifst.id_flex_structure_code = 'ACCOUNTING_FLEXFIELD'
AND fifst.application_id = 101
AND fifst.id_flex_code = 'GL#'
AND fifs.application_id = fifst.application_id
AND fifs.id_flex_code = fifst.id_flex_code
AND fifs.id_flex_num = fifst.id_flex_num
AND ffs.flex_value_set_id = fifs.flex_value_set_id;
 

API for Load Journals into SubLedger Accounting

Oracle provides the ability to enter manual sub-ledger journals via a button on the SLA Journal Inquiry pages.

A manual subledger journal entry is not associated with a transaction or document of a subledger application. If you are considering using the manual SLA journal feature to represent accounting in legacy systems, you can take the advantage of API which allows you to import such transactions into SLA rather than into the GL directly (via the GL interface).

You can use the API XLA_JOURNAL_ENTRIES_PUB_PKG and any of these functions:

• Create_Journal_Entry_Header: This is used creates a new subledger journal entry header
• Update_Journal_Entry_Header: This is used to modifies an existing manual subledger journal entry header, if journal entry is not Final and GL_DATE used must belong to a valid accounting period.
• Delete_Journal_Entry:This deletes all information of a subledger journal entry including journal entry header and journal entry line
• Create_Journal_Entry_Line:This is used to Create the JE line
• Update_Journal_Entry_Line :This is used to Delete the JE line
• Delete_Journal_Entry_Line :This is used to Delete the JE line
• Complete_Journal_Entry: This is used to completes a subledger journal entry
• Reverse_Journal_Entry : This is used to reverse the entry.

These API's you can find more on Oracle Financial Services Accounting Hub Implementation Guide Release 12 Part No. B14420-01

Oracle 11g - New Features at a Glance

The new PL/SQL features for 11g Release 1 (11.1) are:

■ Enhancements to Regular Expression Built-in SQL Functions

■ SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types

  The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are

  predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE,

  respectively. Each subtype has the same range as its base type and has a NOT  NULL      constraint.

■ CONTINUE Statement

The CONTINUE statement exits the current iteration of a loop and transfers control to

the next iteration (in contrast with the EXIT statement, which exits a loop and

transfers control to the end of the loop). The CONTINUE statement has two forms: the

unconditional CONTINUE and the conditional CONTINUE WHEN.

■ Sequences in PL/SQL Expressions

The pseudocolumns CURRVAL and NEXTVAL make writing PL/SQL source code easier

for you and improve run-time performance and scalability. You can use sequence_

name.CURRVAL and sequence_name.NEXTVAL wherever you can use a NUMBER

expression

■ Dynamic SQL Enhancements

Native dynamic SQL now supports a dynamic SQL statement larger than 32 KB by

allowing it to be a CLOB

■ Named and Mixed Notation in PL/SQL Subprogram Invocations

■ PL/SQL Function Result Cache

■ Compound Triggers

■ More Control Over Triggers

The SQL statement CREATE TRIGGER now supports ENABLE, DISABLE, and FOLLOWS

clauses that give you more control over triggers. The DISABLE clause lets you to create a

trigger in the disabled state. This helps one to create a trigger successfully before you enable the trigger. The ENABLE clause explicitly specifies the default state. The FOLLOWS clause lets you control the firing order of triggers that are defined on the same table and have the same timing point.

■ Database Resident Connection Pool

■ Automatic Subprogram Inlining

■ PL/Scope

■ PL/SQL Hierarchical Profiler

■ PL/SQL Native Compiler Generates Native Code Directly