What is GL Interface in Oracle Apps R12
Introduction
The General Ledger (GL) interface in Oracle Apps R12 is a very critical interface; it covers the seamless movement of varied sub-ledger(Purchasing, Order Management, Oracle Payables, Oracle Receivables, Fixed Assets) financial data into the General Ledger, or GL, accounts. Not to underestimate, it has an important role in ensuring that the financial reporting in Oracle e-Business Suite is accurate and trustworthy. This document will help you understand all facets of the functionalities and processes involved with the GL interface.
Purpose and Function
As per it’s design GL interface to amalgamate financial transactions originating from various subledgers like Accounts Receivable, Accounts Payable, and Inventory Management, funneling them into the GL General Ledger. This consolidation process enables organizations to craft comprehensive financial statements, fostering a holistic comprehension of their financial stance and performance.
Key Features and Functionalities: GL Interface in Oracle Apps R12
The GL interface encompasses a range of features that streamline the transfer of financial data for interface processing. These features include:
- Data Validation: The GL interface meticulously validates data from subledgers to ensure its accuracy and consistency before interface processing and transferring it to the GL General Ledger.
- Transaction Aggregation: The interface aggregates transactions into journal entries, summarizing financial activities for each accounting period.
- Error Handling: The interface identifies and flags potential errors or inconsistencies in the data, preventing them from being posted to the General Ledger.
- Posting Options: Users can choose between summary or detail posting, allowing them to control the level of granularity in their financial records.
The GL interface processing typically involves the following steps:
Process Overview: GL Interface in Oracle apps R12
Below are key steps for interface processing of Oracle EBS General Ledger (GL) Interface:
- Data Extraction: Extract financial data from the subledgers and prepared for transfer.
- Data Validation: The extracted data undergoes rigorous validation to ensure its integrity and adherence to accounting rules.
- Transaction Aggregation: Aggregated transactions into journal entries, consolidating financial activities for each accounting period.
- Posting to GL Interface Table: First it validated and aggregated the data. Then transferred to the GL_INTERFACE table, a staging area for journal entries.
- Journal Import: The General Ledger Journal Import program extracts journal entries from the GL_INTERFACE table and creates corresponding journal entries in the General Ledger in the specific GL accounts.
- Posting to General Ledger: Creates the journal entries in the General Ledger and posted to the appropriate ledger accounts, completing the financial consolidation process.
Benefits of Using GL Interface in Oracle Apps R12
The GL interface offers several benefits to organizations, including:
- Enhanced Accuracy: The interface’s data validation and error handling mechanisms help maintain the accuracy of financial records.
- Streamlined Financial Reporting: The consolidation of financial data from subledgers facilitates efficient financial reporting and analysis.
- Improved Data Integrity: The interface ensures the consistency and integrity of financial data across the organization.
- Reduced Errors: The automated nature of the interface reduces the risk of manual errors and data discrepancies.
Interface vs. API: Decoding the Difference
API (Application Programming Interface): An API serves as a built-in program allowing direct data transfer to Oracle base tables sans the need for explicit programming. It expedites data movement into the base tables without the requirement for validation or insertion in interface tables. Oracle defines an API as a set of public programmatic interfaces, enabling communication between systems, databases, web servers, etc., typically by invoking available functions and methods.
Interface: In contrast, a user interface mandates writing codes for data validation and insertion into interface tables, followed by the insertion into Oracle base tables. It encompasses a group of published and supported inbound and outbound interface objects that Oracle provides for transactional data flow. These objects could be tables, concurrent programs referencing PL/SQL, Java or ProC/C++ packages or libraries.
Understanding Open Interfaces and APIs
Oracle’s stance emphasizes that direct posting to application tables might invalidate support agreements. Open interfaces, supported and licensed by Oracle, facilitate inbound and outbound data transactions. However, these published interfaces may sometimes fall short in meeting specific business requirements, demanding custom solutions.
Open Interfaces: These are a group of objects licensed by Oracle, enabling inbound and outbound data transactions in an approved format. Users insert data into these tables, and Oracle imports or exports it via concurrent requests, necessitating data to be in the correct pre-validated format.
APIs: APIs offer programmable hooks or coding blocks, allowing the execution of specific functions. For instance, Oracle Advanced Pricing provide the QP_PREQ_PUB.PRICE_REQUEST API, for geting unit selling price for an item.
Technical Overview: Components of GL Interface
GL Interface Tables:
- GL_INTERFACE table:
The Oracle EBS GL interface tables (GL_INTERFACE), crucial for financial data management, serves as a bridge between subledgers and the general ledger(GL). Seamlessly integrating transactions, it ensures accuracy and efficiency in financial reporting. Transitioning smoothly from source systems to the GL, this table facilitates streamlined processes. By optimizing data flow, businesses can enhance their financial visibility and decision-making prowess. With meticulous attention to detail, it captures transactions promptly, fostering real-time insights. Leveraging this interface empowers enterprises to maintain compliance and uphold financial integrity. Harnessing its capabilities drives operational excellence and fuels sustainable growth in the digital landscape. - GL_JE_HEADERS, GL_JE_LINES, GL_JE_BATCHES: Tables supporting journal entries.
Concurrent Program:
- Journal Import: Responsible for importing journal entries.
- Journal Posting: Populates GL_BALANCES.
Validations:
- Validation criteria encompass checking SOB, journal source, category names, actual flags, period statuses, chart of accounts IDs, enabled codes, and existing records.
Staging Table:
- XXGL_INTF_DATA_STG: GL Interface staged records to be kept before validation and inserted into the GL Interface tables
GL Interface Staging Table
CREATE TABLE xxdeas.XXGL_INTF_DATA_STG
(
transaction_id NUMBER,
user_je_source_name VARCHAR2(25) NOT NULL,
user_je_category_name VARCHAR2(25) NOT NULL,
status VARCHAR2(20) NOT NULL,
validation_comments VARCHAR2(4000),
legacy_batch_number VARCHAR2(100) NOT NULL,
batch_name VARCHAR2(100) NOT NULL,
batch_description VARCHAR2(100),
je_name VARCHAR2(100) NOT NULL,
je_description VARCHAR2(100),
je_line_number NUMBER,
legacy_line_reference VARCHAR2(20) NOT NULL,
legacy_line_description VARCHAR2(100),
je_line_description VARCHAR2(240),
je_reference VARCHAR2(100),
je_reversal_flag VARCHAR2(100),
je_reversal_period VARCHAR2(100),
je_reversal_method VARCHAR2(100),
source_creation_reference VARCHAR2(100),
creation_date DATE NOT NULL,
created_by NUMBER,
created_by_name VARCHAR2(100) NOT NULL,
last_update_date DATE NOT NULL,
last_updated_by NUMBER,
last_update_login NUMBER NOT NULL,
request_id NUMBER,
currency_code VARCHAR2(15) NOT NULL,
actual_flag VARCHAR2(10) NOT NULL,
accounting_date DATE NOT NULL,
transaction_date DATE,
entered_dr NUMBER,
entered_cr NUMBER,
accounted_dr NUMBER,
accounted_cr NUMBER,
currency_conversion_date DATE,
user_currency_conversion_type VARCHAR2(30),
currency_conversion_rate NUMBER,
period_name VARCHAR2(15),
code_combination_id NUMBER(15,0),
segment1 VARCHAR2(25),
segment2 VARCHAR2(25),
segment3 VARCHAR2(25),
segment4 VARCHAR2(25),
segment5 VARCHAR2(25),
segment6 VARCHAR2(25),
segment7 VARCHAR2(25),
segment8 VARCHAR2(25),
legacy_company VARCHAR2(10),
legacy_location VARCHAR2(10),
legacy_department VARCHAR2(10),
legacy_cost_center VARCHAR2(15),
legacy_account VARCHAR2(15),
legacy_sub_account VARCHAR2(10),
legacy_product_code VARCHAR2(10),
group_id NUMBER(15,0),
suspense_account NUMBER(15,0),
legacy_ledger_name VARCHAR2(30) NOT NULL,
ledger_id NUMBER(15,0)
);Package for GL Interface in Oracle EBS:
XXGL_INTERFACE_PKG: Includes procedures for validation and insertion logic.
Package – Part1
CREATE OR REPLACE PACKAGE BODY XXGL_INTERFACE_PKG
AS
--Fistly, define packge for display fnd log
PROCEDURE display_log(p_message_text IN VARCHAR2)
IS
BEGIN
fnd_file.put_line(fnd_file.log, p_message_text_in);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Error: Displaying the message in LOG FILE: ' || '-' ||SQLERRM);
END display_log;
--Secondly display fnd output message
PROCEDURE display_out(p_message IN VARCHAR2)
IS
BEGIN
fnd_file.put_line(fnd_file.output, p_message);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error: Displaying the message in OUTPUT FILE: ' || '-' || SQLERRM);
END display_out;
--Additionally, submit oracle ebs concurrent program from pl/sql
PROCEDURE submit_request(p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_appl_short_name IN VARCHAR2,
p_program_name IN VARCHAR2,
p_description IN VARCHAR2,
p_argument1 IN VARCHAR2,
p_argument2 IN VARCHAR2,
p_argument3 IN VARCHAR2,
p_argument4 IN VARCHAR2,
p_argument5 IN VARCHAR2,
p_argument6 IN VARCHAR2,
p_argument7 IN VARCHAR2,
p_argument8 IN VARCHAR2,
p_argument9 IN VARCHAR2,
p_argument10 IN VARCHAR2,
p_request_id OUT NOCOPY NUMBER)
IS
l_request_id NUMBER;
l_request_complete BOOLEAN;
l_phase VARCHAR2(20);
l_status VARCHAR2(20);
l_dev_phase VARCHAR2(20);
l_dev_status VARCHAR2(20);
l_message VARCHAR2(200);
BEGIN
p_retcode := pvg_func_success_num;
l_phase := NULL;
l_status := NULL;
l_dev_phase := NULL;
l_dev_status:= NULL;
l_message := NULL;
display_log('*******************************************');
display_log('Next, Calling the Conc. Program :' || p_description);
display_log('*******************************************');
l_request_id := fnd_request.submit_request(p_appl_short_name,
p_program,
p_description,
TO_CHAR(SYSDATE,
'YYYY-MM-DD HH24:MI:SS'),
FALSE,
p_argument1,
p_argument2,
p_argument3,
p_argument4,
p_argument5,
p_argument6,
p_argument7,
p_argument8,
p_argument9,
p_argument10);
COMMIT;
IF l_request_id = 0
THEN
display_log('Whenover, Program failed to submit :' ||p_description);
ELSE
p_request_id := l_request_id;
display_log('Program is successfully Submitted with Request# :' ||
l_request_id);
l_request_complete := fnd_concurrent.wait_for_request(request_id =>
l_request_id, phase => l_phase, status => l_status, dev_phase =>
l_dev_phase,dev_status => l_dev_status,message => l_message);
END IF;
EXCEPTION
WHEN OTHERS THEN
p_retcode := pvg_func_error_num;
display_log('Exception In Submit Conc. Program :' || '-' ||
SQLERRM);
END submit_request;
--Whenover, validate the gl interface stagging data
PROCEDURE validate(p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_status_flag IN VARCHAR2,
p_va_source IN VARCHAR2,
p_va_group_id IN VARCHAR2,
p_va_ledger_name IN VARCHAR2)
IS
--Next, Local Variables Declaration
l_error_flag VARCHAR2(1);
l_processed_recs NUMBER := 0;
l_successful_recs NUMBER := 0;
l_error_recs NUMBER := 0;
l_batch_rec_count NUMBER := 0;
l_ledger_id gl_ledgers.ledger_id%TYPE;
l_ledger_currency gl_ledgers.currency_code%TYPE;
l_je_source gl_je_sources.je_source_name%TYPE;
l_je_category gl_je_categories.je_category_name%TYPE;
l_chart_of_accounts gl_ledgers.chart_of_accounts_id%TYPE;
l_currency_code fnd_currencies.currency_code%TYPE;
l_bal_batch_name XXGL_INTF_DATA_STG.batch_name%TYPE;
l_bal_je_name XXGL_INTF_DATA_STG.je_name%TYPE;
l_period_name gl_periods.period_name%TYPE;
l_closing_status gl_period_statuses.closing_status%TYPE;
l_oracle_segment1 gl_code_combinations.segment1%TYPE;
l_oracle_segment2 gl_code_combinations.segment2%TYPE;
l_oracle_segment3 gl_code_combinations.segment3%TYPE;
l_oracle_segment4 gl_code_combinations.segment4%TYPE;
l_oracle_segment5 gl_code_combinations.segment5%TYPE;
l_oracle_segment6 gl_code_combinations.segment6%TYPE;
l_oracle_segment7 gl_code_combinations.segment7%TYPE;
l_oracle_segment8 gl_code_combinations.segment8%TYPE
l_conc_seg_delimiter VARCHAR2(1);
l_concated_segments gl_code_combinations_kfv.concatenated_segments%TYPE;
l_code_combination_id gl_code_combinations.code_combination_id%TYPE;
l_suspense_account gl_suspense_accounts.code_combination_id%TYPE;
l_created_by fnd_user.user_id%TYPE;
l_je_line_description XXGL_INTF_DATA_STG.je_line_description%TYPE;
l_currency_conversion_date XXGL_INTF_DATA_STG.currency_conversion_date%TYPE;
l_user_conversion_type XXGL_INTF_DATA_STG.user_currency_conversion_type%TYPE;
l_conversion_rate XXGL_INTF_DATA_STG.currency_conversion_rate%TYPE;
l_return_status VARCHAR2(1);
l_error_message VARCHAR2(4000);
l_dup_var_batch XXGL_INTF_DATA_STG.legacy_batch_number%TYPE;
l_set_dup VARCHAR2(1);
l_bal_batch XXGL_INTF_DATA_STG.legacy_batch_number%TYPE;
l_set_bal VARCHAR2(1);
l_bal_je XXGL_INTF_DATA_STG.je_name%TYPE;
l_set_je VARCHAR2(1);
l_segment1_flag VARCHAR2(2);
l_segment2_flag VARCHAR2(2);
l_segment3_flag VARCHAR2(2);
l_segment4_flag VARCHAR2(2);
l_segment5_flag VARCHAR2(2);
l_segment6_flag VARCHAR2(2);
l_code_comb_flag VARCHAR2(2);
--Fetch the journals record for validation.
CURSOR cur_journals_info(cp_status_flg IN VARCHAR2,
cp_source IN VARCHAR2,
cp_group_id IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT *
FROM XXGL_INTF_DATA_STG
WHERE status = cp_status_flg
AND user_je_source_name = NVL(cp_source, user_je_source_name)
--User Journal Source
AND legacy_batch_number = NVL(cp_group_id, legacy_batch_number)
AND legacy_ledger_name =
NVL(cp_ledger_name, legacy_ledger_name)
ORDER BY legacy_batch_number;
-- Fetch ledger Id Cursor
CURSOR cur_get_ledger_currency(cp_ledger_name IN VARCHAR2)
IS
SELECT ledger_id,
currency_code
FROM gl_ledgers
WHERE NAME = cp_ledger_name;
-- Query to Fetch Journal Source name in Oracle Apps R12(SQL)
CURSOR cur_journal_source(cp_source_name IN VARCHAR2)
IS
SELECT je_source_name
FROM gl_je_sources
WHERE user_je_source_name = cp_source_name
AND LANGUAGE = userenv('LANG');
--SQL to Fetch Journal category in Oracle EBS R12(Query)
CURSOR cur_journal_category(cp_category_name IN VARCHAR2)
IS
SELECT je_category_name
FROM gl_je_categories
WHERE user_je_category_name = cp_category_name
AND LANGUAGE = userenv('LANG');
--SQL to Fetch chart of account in Oracle EBS
CURSOR cur_chart_of_accounts(cp_ledger_id IN NUMBER)
IS
SELECT chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = cp_ledger_id;
--SQL to Fetch currency in Oracle e-business suite
CURSOR cur_currency_code(cp_currency_code IN VARCHAR2)
IS
SELECT currency_code
FROM fnd_currencies
WHERE currency_code = cp_currency_code
AND enabled_flag = 'Y';
--Fetch je batch id Cursor
CURSOR cur_je_batch(cp_source IN VARCHAR2,
cp_group_id IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT COUNT(1)
FROM XXGL_INTF_DATA_STG xgis,
XXGL_INTF_DATA_STG xgis1
WHERE xgis.legacy_batch_number = xgis1.legacy_batch_number
AND trunc(xgis.creation_date) = trunc(xgis1.creation_date)
AND xgis.group_id <> xgis1.group_id
-- Group id
AND xgis.user_je_source_name =
NVL(cp_source, xgis.user_je_source_name)
--User Source
AND xgis.legacy_batch_number =
NVL(cp_group_id, xgis.legacy_batch_number)
AND xgis.legacy_ledger_name =
NVL(cp_ledger_name, xgis.legacy_ledger_name);
--Fetch Debits and Credits values for each batch name
CURSOR cur_chk_batch_bal(cp_batch_name IN VARCHAR2,
cp_source IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT legacy_batch_number
FROM XXGL_INTF_DATA_STG
WHERE legacy_batch_number = cp_batch_name
AND user_je_source_name = NVL(cp_source, user_je_source_name)
AND legacy_ledger_name =
NVL(cp_ledger_name, legacy_ledger_name)
GROUP BY legacy_batch_number
HAVING SUM(entered_dr) <> SUM(abs(entered_cr))
AND SUM(accounted_dr) <> SUM(abs(accounted_cr))
ORDER BY legacy_batch_number;
--Fetch Debits and Credits values for each journal name Cursor
CURSOR cur_chk_je_bal(cp_je_name IN VARCHAR2,
cp_source IN VARCHAR2,
cp_group_id IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT je_name
FROM XXGL_INTF_DATA_STG
WHERE je_name = cp_je_name
AND user_je_source_name = NVL(cp_source, user_je_source_name)
--User Journal Source name
AND legacy_batch_number = NVL(cp_group_id, legacy_batch_number)
AND legacy_ledger_name =
NVL(cp_ledger_name, legacy_ledger_name)
GROUP BY je_name
HAVING SUM(entered_dr) <> SUM(abs(entered_cr))
AND SUM(accounted_dr) <> SUM(abs(accounted_cr))
ORDER BY je_name;GL Interface Package in Oracle EBS: Part2
Package – Part2
--SQL to Fetch period name in oracle ebs
CURSOR cur_get_period(cp_accounting_date IN DATE,
cp_ledger_id IN NUMBER)
IS
SELECT period_name
FROM gl_periods
WHERE cp_accounting_date BETWEEN NVL(start_date, SYSDATE)
AND NVL(end_date, SYSDATE)
AND period_set_name =
(SELECT period_set_name
FROM gl_ledgers
WHERE ledger_id = cp_ledger_id)
--Period Type in gl ledgers in oracle ebs
AND period_type =
(SELECT accounted_period_type
FROM gl_ledgers
WHERE ledger_id = cp_ledger_id)
AND adjustment_period_flag = 'N';
--SQL to Fetch period Status in Oracle ebs
CURSOR cur_period_status(cp_period_name IN VARCHAR2,
cp_accounting_date IN DATE)
IS
SELECT closing_status
FROM gl_period_statuses
WHERE application_id =
(SELECT application_id
FROM fnd_application_vl
WHERE application_short_name = 'SQLGL')
AND period_name = cp_period_name
AND cp_accounting_date BETWEEN NVL(start_date, SYSDATE)
--Accounting date
AND NVL(end_date, SYSDATE)
AND closing_status IN ('O', 'F');
-- Fetch GL segment1
CURSOR cur_get_segment1(cp_legacy_cost_center IN VARCHAR2)
IS
SELECT oracle_segment1
FROM xxgl_coa_xref_cost_cent_tab
WHERE source_segment2 = cp_legacy_cost_center
AND SYSDATE BETWEEN NVL(start_date, SYSDATE)
AND NVL(end_date, SYSDATE);
--Fetch GL segment2
CURSOR cur_get_segment2(cp_legacy_cost_center IN VARCHAR2)
IS
SELECT oracle_segment2
FROM xxgl_coa_xref_cost_cent_tab
WHERE source_segment2 = cp_legacy_cost_center
AND SYSDATE BETWEEN NVL(start_date, SYSDATE)
AND NVL(end_date, SYSDATE);
--Fetch GL segment3
CURSOR cur_get_segment3(cp_legacy_cost_center IN VARCHAR2)
IS
SELECT oracle_segment3
FROM xxgl_coa_xref_cost_cent_tab
WHERE source_segment2 = cp_legacy_cost_center
AND SYSDATE BETWEEN NVL(start_date, SYSDATE)
AND NVL(end_date, SYSDATE);
--Fetch GL segment4
CURSOR cur_get_segment4(cp_legacy_account IN VARCHAR2,
cp_legacy_sub_account IN VARCHAR2)
IS
SELECT oracle_segment4
FROM xxgl_coa_xref_nat_acct_tab
WHERE source_segment3 = cp_legacy_account
AND source_segment4 = cp_legacy_sub_account
--Active date
AND SYSDATE BETWEEN NVL(start_date, SYSDATE)
AND NVL(end_date, SYSDATE);
--Fetch GL segment5
CURSOR cur_get_segment5(cp_legacy_product_code IN VARCHAR2)
IS
SELECT oracle_segment5
FROM xxgl_coa_xref_prod_tab
WHERE source_product_code = cp_legacy_product_code
AND SYSDATE BETWEEN NVL(start_date, SYSDATE)
AND NVL(end_date, SYSDATE);
--Fetch GL segment6
CURSOR cur_get_segment6(cp_legacy_cost_center IN VARCHAR2)
IS
SELECT oracle_segment6
FROM xxgl_coa_xref_cost_cent_tab
WHERE source_segment2 = cp_legacy_cost_center
AND SYSDATE BETWEEN NVL(start_date, SYSDATE)
AND NVL(end_date, SYSDATE);
--Fetch suspense account code combination in oracle ebs
CURSOR cur_get_suspense_acc(cp_source_name IN VARCHAR2,
cp_category_name IN VARCHAR2,
cp_ledger_id IN NUMBER)
IS
SELECT code_combination_id
FROM gl_suspense_accounts
WHERE ledger_id = cp_ledger_id
AND je_source_name = cp_source_name
AND je_category_name = cp_category_name;
--Fetch created by
CURSOR cur_get_created_by(cp_created_by_name IN VARCHAR2)
IS
SELECT user_id
FROM fnd_user
WHERE user_name = cp_created_by_name;
--Fetch Conversion type profile in oracle ebs
CURSOR cur_get_type_profile
IS
SELECT fnd_profile.value('XXGL_CONVERSION_TYPE_PROFILE')
FROM dual;
--Fetch GL daily rates in oracle ebs r12
CURSOR cur_get_daily_rates(cp_conversion_type IN VARCHAR2,
cp_conversion_date IN DATE,
cp_from_currency IN VARCHAR2,
cp_to_currency IN VARCHAR2)
IS
SELECT round(conversion_rate, 2)
FROM gl_daily_rates
WHERE conversion_type = cp_conversion_type
AND conversion_date = cp_conversion_date
AND from_currency = cp_from_currency
--Currency code
AND to_currency = cp_to_currency;
BEGIN
FOR cur_journals_info_rec IN cur_journals_info(p_status_flag_in,
p_va_source_in,
p_va_group_id_in,
p_va_ledger_name_in)
LOOP
--Initialize Local variables
l_error_flag := 'N';
l_ledger_currency := NULL;
l_je_source := NULL;
l_je_category := NULL;
l_chart_of_accounts := NULL;
l_currency_code := NULL;
l_bal_batch_name := NULL;
l_bal_je_name := NULL;
l_period_name := NULL;
l_closing_status := NULL;
l_oracle_segment1 := NULL;
l_oracle_segment2 := NULL;
l_oracle_segment3 := NULL;
l_oracle_segment4 := NULL;
l_oracle_segment5 := NULL;
l_oracle_segment6 := NULL;
l_oracle_segment7 := '000';
l_oracle_segment8 := '0000';
l_oracle_segment9 := '0000';
l_conc_seg_delimiter := NULL;
l_code_combination_id := NULL;
l_je_line_description := NULL;
l_user_conversion_type:= NULL;
l_return_status := NULL;
l_error_message := NULL;
--
l_segment1_flag := NULL;
l_segment2_flag := NULL;
l_segment3_flag := NULL;
l_segment4_flag := NULL;
l_segment5_flag := NULL;
l_segment6_flag := NULL;
l_code_comb_flag := NULL;
l_processed_recs := NULL;
l_successful_recs := NULL;
l_error_recs := NULL;
l_batch_rec_count := NULL;
l_ledger_id := NULL;
l_suspense_account:= NULL;
l_created_by := NULL;
l_conversion_rate := NULL;
display_log('Validating for record id: ' ||
cur_journals_info_rec.staging_transaction_id);
display_log('---------------------------');
-- Check if Journal ledger name and currency is valid or not
display_log('Validating ledger name and currency.');
OPEN cur_get_ledger_currency(cur_journals_info_rec.legacy_ledger_name);
FETCH cur_get_ledger_currency
INTO l_ledger_id_num,
l_ledger_currency;
IF l_ledger_idIS NULL
THEN
l_error_flag:= 'Y';
display_log('Ledger name ' ||
cur_journals_info_rec.legacy_ledger_name ||
' is Invalid.');
ELSIF l_ledger_currencyIS NULL
THEN
l_error_flag:= 'Y';
display_log('Ledger Currency ' || l_ledger_currency||
' is Invalid for the ledger ' ||
cur_journals_info_rec.legacy_ledger_name || '.');
END IF;
CLOSE cur_get_ledger_currency;
-- Check if Journal Source is valid or not
display_log('Validating journal source.');
OPEN cur_journal_source(cur_journals_info_rec.user_je_source_name);
FETCH cur_journal_source
INTO l_je_source;
IF cur_journal_source%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Journal Source ' ||
cur_journals_info_rec.user_je_source_name ||
' is Invalid.');
END IF;
CLOSE cur_journal_source;
-- Check if Journal Category is valid or not
display_log('Validating journal category.');
OPEN cur_journal_category(cur_journals_info_rec.user_je_category_name);
FETCH cur_journal_category
INTO l_je_category;
IF cur_journal_category%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Journal Category ' ||
cur_journals_info_rec.user_je_category_name ||
' is Invalid.');
END IF;
CLOSE cur_journal_category;
-- Check if chart of accounts is valid or not
display_log('Validating chart of accounts.');
IF l_ledger_idIS NOT NULL
THEN
OPEN cur_chart_of_accounts(l_ledger_id_num);
FETCH cur_chart_of_accounts
INTO l_chart_of_accounts_num;
IF cur_chart_of_accounts%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Chart of accounts for the ledger ' ||
l_ledger_id|| ' does not exist.');
END IF;
CLOSE cur_chart_of_accounts;
END IF;
-- Check if currency is valid or not
display_log('Validating currency code.');
OPEN cur_currency_code(cur_journals_info_rec.currency_code);
FETCH cur_currency_code
INTO l_currency_code;
IF cur_currency_code%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Currency Code ' ||
cur_journals_info_rec.currency_code ||
' is Invalid.');
END IF;
CLOSE cur_currency_code;Package for GL Interface in Oracle EBS: Part3
Package – Part3
display_log('Duplicate Flag: ' || l_set_dup);
display_log('Batch Name Before: ' || l_dup_var_batch);
IF NVL(l_dup_var_batch, 'X') <>
cur_journals_info_rec.legacy_batch_number
THEN
display_log('Validating journal batch.');
OPEN cur_je_batch(p_va_source_in,
cur_journals_info_rec.legacy_batch_number,
p_va_ledger_name_in);
FETCH cur_je_batch
INTO l_batch_rec_count_num;
IF l_batch_rec_count> 0
THEN
l_set_dup:= 'Y';
ELSE
l_set_dup:= 'N';
END IF;
CLOSE cur_je_batch;
END IF;
l_dup_var_batch:= cur_journals_info_rec.legacy_batch_number;
display_log('Batch Name After: ' || l_dup_var_batch);
IF l_set_dup= 'Y'
THEN
l_error_flag:= 'Y';
display_log('Batch Name ' ||
cur_journals_info_rec.legacy_batch_number ||
' already exists.');
END IF;
-- Check if Sum of Debits is equal to Sum of credits
-- for each Journal Batch
display_log('Duplicate Flag Batch: ' || l_set_bal);
display_log('Batch Name Before: ' || l_bal_batch);
IF NVL(l_bal_batch, 'X') <>
cur_journals_info_rec.legacy_batch_number
THEN
display_log('Validating batch balance.');
OPEN cur_chk_batch_bal(cur_journals_info_rec.legacy_batch_number,
p_va_source_in, p_va_ledger_name_in);
FETCH cur_chk_batch_bal
INTO l_bal_batch_name;
IF cur_chk_batch_bal%FOUND
THEN
l_set_bal:= 'Y';
ELSE
l_set_bal:= 'N';
END IF;
CLOSE cur_chk_batch_bal;
END IF;
l_bal_batch:= cur_journals_info_rec.legacy_batch_number;
display_log('Batch Name After: ' || l_bal_batch);
IF l_set_bal= 'Y'
THEN
l_error_flag:= 'Y';
display_log('Mismatch in Debits and Credits for the batch ' ||
cur_journals_info_rec.legacy_batch_number || '.');
END IF;
-- Check if Sum of Debits is equal to Sum of credits
-- for each Journal Name
display_log('Duplicate Flag JE: ' || l_set_je);
display_log('JE Name Before: ' || l_bal_je);
IF NVL(l_bal_je, 'X') <> cur_journals_info_rec.je_name
THEN
OPEN cur_chk_je_bal(cur_journals_info_rec.je_name,
p_va_source_in, p_va_group_id_in,
p_va_ledger_name_in);
FETCH cur_chk_je_bal
INTO l_bal_je_name;
IF cur_chk_je_bal%FOUND
THEN
l_set_je:= 'Y';
ELSE
l_set_je:= 'N';
END IF;
CLOSE cur_chk_je_bal;
END IF;
l_bal_je:= cur_journals_info_rec.je_name;
display_log('JE Name After: ' || l_bal_je);
IF l_set_je= 'Y'
THEN
l_error_flag:= 'Y';
display_log('Mismatch in Debits and Credits for the journal ' ||
cur_journals_info_rec.je_name || '.');
END IF;
display_log('Validating period name.');
IF cur_journals_info_rec.period_name IS NULL AND
l_ledger_idIS NOT NULL
THEN
OPEN cur_get_period(cur_journals_info_rec.accounting_date,
l_ledger_id_num);
FETCH cur_get_period
INTO l_period_name;
IF cur_get_period%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('GL Period ' || l_period_name||
' is Invalid for the accounting date ' ||
cur_journals_info_rec.accounting_date || '.');
END IF;
CLOSE cur_get_period;
ELSE
l_period_name:= cur_journals_info_rec.period_name;
END IF;
-- Check if GL period status is valid or not
display_log('Validating period status.');
IF l_period_nameIS NOT NULL AND
cur_journals_info_rec.accounting_date IS NOT NULL
THEN
OPEN cur_period_status(l_period_name,
cur_journals_info_rec.accounting_date);
FETCH cur_period_status
INTO l_closing_status;
IF cur_period_status%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('GL Period ' || l_period_name||
' is not open or future dated.');
END IF;
CLOSE cur_period_status;
END IF;
-- Check if GL segment1 is valid or not
display_log('Validating oracle segment1.');
IF cur_journals_info_rec.segment1 IS NULL AND
cur_journals_info_rec.legacy_cost_center IS NOT NULL
THEN
OPEN cur_get_segment1(cur_journals_info_rec.legacy_cost_center);
FETCH cur_get_segment1
INTO l_oracle_segment1;
IF cur_get_segment1%NOTFOUND
THEN
l_segment1_flag:= 'S1';
display_log('GL segment1 ' || l_oracle_segment1||
' is Invalid.');
END IF;
CLOSE cur_get_segment1;
ELSE
l_oracle_segment1:= cur_journals_info_rec.segment1;
END IF;
-- Check if GL segment2 is valid or not
display_log('Validating oracle segment2.');
IF cur_journals_info_rec.segment2 IS NULL AND
cur_journals_info_rec.legacy_cost_center IS NOT NULL
THEN
OPEN cur_get_segment2(cur_journals_info_rec.legacy_cost_center);
FETCH cur_get_segment2
INTO l_oracle_segment2;
IF cur_get_segment2%NOTFOUND
THEN
l_segment2_flag:= 'S2';
display_log('GL segment2 ' || l_oracle_segment2||
' is Invalid.');
END IF;
CLOSE cur_get_segment2;
ELSE
l_oracle_segment2:= cur_journals_info_rec.segment2;
END IF;
-- Check if GL segment3 is valid or not
display_log('Validating oracle segment3.');
IF cur_journals_info_rec.segment3 IS NULL AND
cur_journals_info_rec.legacy_cost_center IS NOT NULL
THEN
OPEN cur_get_segment3(cur_journals_info_rec.legacy_cost_center);
FETCH cur_get_segment3
INTO l_oracle_segment3;
IF cur_get_segment3%NOTFOUND
THEN
l_segment3_flag:= 'S3';
display_log('GL segment3 ' || l_oracle_segment3||
' is Invalid.');
END IF;
CLOSE cur_get_segment3;
ELSE
l_oracle_segment3:= cur_journals_info_rec.segment3;
END IF;
-- Check if GL segment4 is valid or not
display_log('Validating oracle segment4.');
IF cur_journals_info_rec.segment4 IS NULL AND
cur_journals_info_rec.legacy_account IS NOT NULL AND
cur_journals_info_rec.legacy_sub_account IS NOT NULL
THEN
OPEN cur_get_segment4(cur_journals_info_rec.legacy_account,
cur_journals_info_rec.legacy_sub_account);
FETCH cur_get_segment4
INTO l_oracle_segment4;
IF cur_get_segment4%NOTFOUND
THEN
l_segment4_flag:= 'S4';
display_log('GL segment4 ' || l_oracle_segment4||
' is Invalid.');
END IF;
CLOSE cur_get_segment4;
ELSE
l_oracle_segment4:= cur_journals_info_rec.segment4;
END IF;
-- Check if GL segment5 is valid or not
display_log('Validating oracle segment5.');
IF cur_journals_info_rec.segment5 IS NULL AND
cur_journals_info_rec.legacy_product_code IS NOT NULL
THEN
OPEN cur_get_segment5(cur_journals_info_rec.legacy_product_code);
FETCH cur_get_segment5
INTO l_oracle_segment5;
IF cur_get_segment5%NOTFOUND
THEN
l_segment5_flag:= 'S5';
display_log('GL segment5 ' || l_oracle_segment5||
' is Invalid.');
END IF;
CLOSE cur_get_segment5;
ELSE
l_oracle_segment5:= cur_journals_info_rec.segment5;
END IF;
-- Check if GL segment6 is valid or not
display_log('Validating oracle segment6.');
IF cur_journals_info_rec.segment6 IS NULL AND
cur_journals_info_rec.legacy_cost_center IS NOT NULL
THEN
OPEN cur_get_segment6(cur_journals_info_rec.legacy_cost_center);
FETCH cur_get_segment6
INTO l_oracle_segment6;
IF cur_get_segment6%NOTFOUND
THEN
l_segment6_flag:= 'S6';
display_log('GL segment6 ' || l_oracle_segment6||
' is Invalid.');
END IF;
CLOSE cur_get_segment6;
ELSE
l_oracle_segment6:= cur_journals_info_rec.segment6;
END IF;
display_log('Validating created by.');
OPEN cur_get_created_by(cur_journals_info_rec.created_by_name);
FETCH cur_get_created_by
INTO l_created_by_num;
IF cur_get_created_by%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Created by name ' ||
cur_journals_info_rec.created_by_name ||
' is Invalid.');
END IF;
CLOSE cur_get_created_by;
-- Set currency_conversion_date field value
display_log('Set currency conversion date.');
IF l_currency_code<> l_ledger_currencyAND
NVL(cur_journals_info_rec.user_currency_conversion_type, 'NULL') <>
'User'
THEN
l_currency_conversion_date := cur_journals_info_rec.accounting_date;
ELSE
l_currency_conversion_date := cur_journals_info_rec.currency_conversion_date;
END IF; Package for GL Interface in Oracle EBS: Part4
Package – Part4
-- Set user_currency_conversion_type field value
display_log('Set user currency conversion type.');
IF l_currency_code<> l_ledger_currencyAND
NVL(cur_journals_info_rec.user_currency_conversion_type, 'NULL') <>
'User'
THEN
OPEN cur_get_type_profile;
FETCH cur_get_type_profile
INTO l_user_conversion_type;
IF cur_get_type_profile%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Custom Profile CONVERSION_TYPE_PROFILE is not created or setup.');
END IF;
CLOSE cur_get_type_profile;
ELSE
l_user_conversion_type:= cur_journals_info_rec.user_currency_conversion_type;
END IF;
-- Check if conversion rate is valid or not
display_log('Validating conversion rate.');
IF l_currency_code<> l_ledger_currencyAND
NVL(cur_journals_info_rec.user_currency_conversion_type, 'NULL') <>
'User' AND l_user_conversion_type IS NOT NULL AND
l_currency_conversion_date IS NOT NULL AND
l_currency_code IS NOT NULL AND
l_ledger_currency IS NOT NULL
THEN
OPEN cur_get_daily_rates(l_user_conversion_type,
l_currency_conversion_date,
l_currency_code,
l_ledger_currency);
FETCH cur_get_daily_rates
INTO l_conversion_rate_num;
IF cur_get_daily_rates%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Conversion rate ' || l_conversion_rate||
' is Invalid.');
END IF;
CLOSE cur_get_daily_rates;
ELSE
l_conversion_rate:= cur_journals_info_rec.currency_conversion_rate;
END IF;
-- Check if Code Combination is valid or not
IF l_chart_of_accountsIS NOT NULL
THEN
-- Get Segment delimiter
display_log('Get Segment delimiter.');
l_conc_seg_delimiter:= fnd_flex_ext.get_delimiter('SQLGL',
'GL#',
l_chart_of_accounts_num);
display_log('Segment delimiter: ' || l_conc_seg_delimiter|| '.');
END IF;
IF l_conc_seg_delimiterIS NOT NULL
THEN
-- Get concatenated segments
display_log('Get concatenated segments.');
l_concated_segments:= l_oracle_segment1||
l_conc_seg_delimiter||
l_oracle_segment2||
l_conc_seg_delimiter||
l_oracle_segment3||
l_conc_seg_delimiter||
l_oracle_segment4||
l_conc_seg_delimiter||
l_oracle_segment5||
l_conc_seg_delimiter||
l_oracle_segment6||
l_conc_seg_delimiter||
l_oracle_segment7||
l_conc_seg_delimiter||
l_oracle_segment8||
l_conc_seg_delimiter||
l_oracle_segment9;
display_log('Concatenated segments : ' ||
l_concated_segments|| '.');
END IF;
IF l_concated_segmentsIS NOT NULL AND
l_chart_of_accountsIS NOT NULL AND
l_je_sourceIS NOT NULL AND l_je_categoryIS NOT NULL
THEN
-- Get code combination_id
display_log('Get code combination_id.');
l_code_combination_id:= fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => l_chart_of_accounts_num,
validation_date => TO_CHAR(SYSDATE,
'DD-MON-YYYY'),
concatenated_segments => l_concated_segments);
display_log('Code combination id : ' ||
l_code_combination_id|| '.');
IF (l_code_combination_id= 0)
THEN
l_code_comb_flag:= 'CE';
display_log('Code combination ' ||
l_code_combination_id|| ' is Invalid.');
-- Get suspense account
display_log('Get suspense account.');
OPEN cur_get_suspense_acc(l_je_source,
l_je_category,
l_ledger_id_num);
FETCH cur_get_suspense_acc
INTO l_suspense_account_num;
IF cur_get_suspense_acc%NOTFOUND
THEN
l_error_flag:= 'Y';
display_log('Suspense account ' ||
l_suspense_account|| ' is Invalid.');
END IF;
CLOSE cur_get_suspense_acc;
END IF;
END IF;
-- Inserting Error record for segment1
IF l_suspense_accountIS NULL AND l_segment1_flag= 'S1'
THEN
l_message_text := 'GL segment1 ' ||l_oracle_segment1||' is Invalid.';
END IF;
-- Inserting Error record for segment2
IF l_suspense_accountIS NULL AND l_segment2_flag= 'S2'
THEN
l_message_text := 'GL segment2 ' ||l_oracle_segment2||' is Invalid.';
END IF;
-- Inserting Error record for segment3
IF l_suspense_accountIS NULL AND l_segment3_flag= 'S3'
THEN
l_message_text := 'GL segment3 ' ||l_oracle_segment3||' is Invalid.';
END IF;
-- Inserting Error record for segment4
IF l_suspense_accountIS NULL AND l_segment4_flag= 'S4'
THEN
l_message_text := 'GL segment4 ' ||l_oracle_segment4||' is Invalid.';
END IF;
-- Inserting Error record for segment5
IF l_suspense_accountIS NULL AND l_segment5_flag= 'S5'
THEN
l_message_text := 'GL segment5 ' || l_oracle_segment5|| ' is Invalid.';
END IF;
-- Inserting Error record for segment6
IF l_suspense_accountIS NULL AND l_segment6_flag= 'S6'
THEN
message_text => 'GL segment6 ' ||l_oracle_segment6||' is Invalid.';
END IF;
-- Inserting Error record for Code combination
IF l_suspense_accountIS NULL AND
l_code_comb_flag= 'CE'
THEN
message_text => 'Code combination ' l_code_combination_id||' is Invalid.';
END IF;
-- Updating the status to staging table and calling
-- common error log procedure to insert error messages
display_log('Updating staging table XXGL_INTF_DATA_STG.');
IF (l_error_flag= 'Y')
THEN
BEGIN
UPDATE XXGL_INTF_DATA_STG
SET status = pvg_error,
last_update_date = SYSDATE,
last_updated_by = pvg_user_id_num,
request_id = pvg_request_id_num
WHERE staging_transaction_id =
cur_journals_info_rec.staging_transaction_id;
EXCEPTION
WHEN OTHERS THEN
l_error_flag:= 'Y';
display_log('Exception in updating error record to table XXGL_INTF_DATA_STG: ' || '-' ||
SQLERRM);
END;
ELSIF (l_error_flag= 'N')
THEN
BEGIN
UPDATE XXGL_INTF_DATA_STG
SET status = pvg_valid,
last_update_date = SYSDATE,
last_updated_by = pvg_user_id_num,
created_by = l_created_by_num,
request_id = pvg_request_id_num,
segment1 = l_oracle_segment1,
segment2 = l_oracle_segment2,
segment3 = l_oracle_segment3,
segment4 = l_oracle_segment4,
segment5 = l_oracle_segment5,
segment6 = l_oracle_segment6,
segment7 = NVL(cur_journals_info_rec.segment7,
l_oracle_segment7),
segment8 = l_oracle_segment8,
segment9 = l_oracle_segment9,
code_combination_id = decode(l_code_combination_id_num,
0,
l_suspense_account_num,
l_code_combination_id_num),
suspense_account = l_suspense_account_num,
je_line_description =
(cur_journals_info_rec.legacy_company ||
decode(cur_journals_info_rec.legacy_company, NULL,
NULL, '.') ||
cur_journals_info_rec.legacy_cost_center ||
decode(cur_journals_info_rec.legacy_cost_center, NULL,
NULL, '.') ||
cur_journals_info_rec.legacy_account ||
decode(cur_journals_info_rec.legacy_account, NULL,
NULL, '.') ||
cur_journals_info_rec.legacy_sub_account ||
decode(cur_journals_info_rec.legacy_sub_account, NULL,
NULL, '.') ||
cur_journals_info_rec.legacy_product_code ||
decode(cur_journals_info_rec.legacy_product_code,
NULL, NULL, '-') ||
cur_journals_info_rec.legacy_line_description),
ledger_id = l_ledger_id_num,
currency_conversion_date = l_currency_conversion_date,
user_currency_conversion_type = l_user_conversion_type,
currency_conversion_rate = l_conversion_rate_num
WHERE staging_transaction_id =
cur_journals_info_rec.staging_transaction_id;
EXCEPTION
WHEN OTHERS THEN
l_error_flag:= 'Y';
display_log('Exception in updating validated record to table XXGL_INTF_DATA_STG: ' || '-' ||
SQLERRM);
END;
END IF;
-- loop End for cursor cur_journals_info
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_retcode := pvg_func_error_num;
display_log('Exception in procedure- validate: ' || '-' ||
SQLERRM);
END validate;Package for GL Interface in Oracle EBS – Part5
Package – Part5
/*====================================================================
Name: insert_rec_gl_interface
Description: This procedure will be used to validate the records
from the custom staging table XXXGL_INTF_DATA_STG.
====================================================================*/
PROCEDURE insert_rec_gl_interface(p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_batch_name IN VARCHAR2,
p_gl_source IN VARCHAR2,
p_gl_group_id IN VARCHAR2,
p_gl_ledger_name IN VARCHAR2)
IS
--Local Variables Declaration
l_error_flag VARCHAR2(1);
l_error_countNUMBER;
--Fetch error record count.
CURSOR cur_error_count(cp_batch_name IN VARCHAR2,
cp_source IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT COUNT(1)
FROM XXGL_INTF_DATA_STG
WHERE status = pvg_error
AND legacy_batch_number = cp_batch_name
AND user_je_source_name = NVL(cp_source, user_je_source_name)
--User Journal Source name
AND legacy_ledger_name =
NVL(cp_ledger_name, legacy_ledger_name);
--Fetch the validated journals record for.
CURSOR cur_journals_validated(cp_batch_name IN VARCHAR2,
cp_source IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT *
FROM XXGL_INTF_DATA_STG
WHERE status = pvg_valid
AND legacy_batch_number = cp_batch_name
AND user_je_source_name = NVL(cp_source, user_je_source_name)
--User Journal Source name
AND legacy_ledger_name =
NVL(cp_ledger_name, legacy_ledger_name);
BEGIN
p_retcode := pvg_func_success_num;
--Check if error record count is 0 or not
display_log('Check if error record count is 0 or not.');
OPEN cur_error_count(p_batch_name_in, p_gl_source_in,
p_gl_ledger_name_in);
FETCH cur_error_count
INTO l_error_count_num;
IF (l_error_count<> 0)
THEN
l_error_flag:= 'Y';
display_log('Error record count' || l_error_count||
'is > 0.');
END IF;
CLOSE cur_error_count;
-- Check record count to run the loop
IF l_error_count= 0
THEN
FOR cur_journals_validated_rec IN
cur_journals_validated(p_batch_name_in, p_gl_source_in,
p_gl_ledger_name_in)
LOOP
--Initialize Local variables
l_error_flag := 'N';
l_error_count:= 0;
-- Updating the gl_interface table
display_log('Updating open interface table gl_interface.');
IF (l_error_flag= 'N')
THEN
BEGIN
INSERT INTO gl_interface
(status,
ledger_id,
user_je_source_name,
user_je_category_name,
created_by,
date_created,
currency_code,
actual_flag,
accounting_date,
transaction_date,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
period_name,
reference1,
reference2,
reference4,
reference5,
reference6,
reference7,
reference8,
reference9,
reference10,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
code_combination_id,
group_id,
attribute1 --Added on 26-Nov-2012
)
VALUES
('NEW',
cur_journals_validated_rec.ledger_id,
cur_journals_validated_rec.user_je_source_name,
cur_journals_validated_rec.user_je_category_name,
cur_journals_validated_rec.created_by,
SYSDATE,
cur_journals_validated_rec.currency_code,
cur_journals_validated_rec.actual_flag,
cur_journals_validated_rec.accounting_date,
cur_journals_validated_rec.transaction_date,
cur_journals_validated_rec.entered_dr,
abs(cur_journals_validated_rec.entered_cr),
cur_journals_validated_rec.accounted_dr,
abs(cur_journals_validated_rec.accounted_cr),
cur_journals_validated_rec.period_name,
cur_journals_validated_rec.batch_name,
cur_journals_validated_rec.batch_description,
cur_journals_validated_rec.je_name,
cur_journals_validated_rec.je_description,
cur_journals_validated_rec.je_reference,
cur_journals_validated_rec.je_reversal_flag,
cur_journals_validated_rec.je_reversal_period,
cur_journals_validated_rec.je_reversal_method,
cur_journals_validated_rec.je_line_description,
cur_journals_validated_rec.currency_conversion_date,
cur_journals_validated_rec.user_currency_conversion_type,
cur_journals_validated_rec.currency_conversion_rate,
cur_journals_validated_rec.code_combination_id,
cur_journals_validated_rec.group_id,
cur_journals_validated_rec.staging_attribute2
);
display_log('Updated open interface table gl_interface.');
-- Updating status to staging table
BEGIN
display_log('Updating staging table XXGL_INTF_DATA_STG.');
UPDATE XXGL_INTF_DATA_STG
SET status = pvg_process,
request_id = pvg_request_id_num
WHERE staging_transaction_id = cur_journals_validated_rec.staging_transaction_id;
display_log('Updated staging table XXGL_INTF_DATA_STG.');
EXCEPTION
WHEN OTHERS THEN
p_retcode := pvg_func_error_num;
l_error_flag:= 'Y';
display_log('Exception in updating status P to staging table XXGL_INTF_DATA_STG: ' || '-' ||
SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
p_retcode := pvg_func_error_num;
l_error_flag:= 'Y';
display_log('Exception in inserting record to table gl_interface: ' || '-' ||
SQLERRM);
END;
END IF;
--Loop for cursor cur_journals_validated Ends
END LOOP;
p_retcode := pvg_func_success_num;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_retcode := pvg_func_error_num;
l_error_flag:= 'Y';
display_log('Exception in procedure- insert_rec_gl_interface: ' || '-' ||
SQLERRM);
END insert_rec_gl_interface;
/*====================================================================
Name: display_stats
Description: This procedure will be be called to display program
summary.
=====================================================================*/
PROCEDURE display_stats(p_je_proc_mode IN VARCHAR2,
p_je_post_gl IN VARCHAR2,
p_je_source IN VARCHAR2,
p_je_ledger_name IN VARCHAR2,
p_je_group_id IN VARCHAR2,
p_je_post_errors_suspense IN VARCHAR2,
p_je_create_summary IN VARCHAR2,
p_je_import_dff_journals IN VARCHAR2,
p_je_reprocess_error IN VARCHAR2,
p_je_submit_import IN VARCHAR2)
IS
--Local Variables Declaration
l_total_count NUMBER;
l_error_count NUMBER;
l_valid_count NUMBER;
l_process_countNUMBER;
l_sucess_flag VARCHAR2(1);
l_return_statusVARCHAR2(1);
l_error_msg VARCHAR2(4000);
--Fetch total number of records selected.
CURSOR cur_get_total_count
IS
SELECT COUNT(1)
FROM XXGL_INTF_DATA_STG
WHERE request_id = pvg_request_id_num;
--Fetch total number of records in E status.
CURSOR cur_get_error_count
IS
SELECT COUNT(1)
FROM XXGL_INTF_DATA_STG
WHERE request_id = pvg_request_id_num
AND status = pvg_error;
--Fetch total number of records in V status.
CURSOR cur_get_valid_count
IS
SELECT COUNT(1)
FROM XXGL_INTF_DATA_STG
WHERE request_id = pvg_request_id_num
AND status = pvg_valid;
--Fetch total number of records in P status.
CURSOR cur_get_process_count
IS
SELECT COUNT(1)
FROM XXGL_INTF_DATA_STG
WHERE request_id = pvg_request_id_num
AND status = pvg_process;
--Fetch Successful Records details.
CURSOR cur_get_success_records(cp_status_flag IN VARCHAR2)
IS
SELECT legacy_batch_number legacy_batch_number,
user_je_source_name user_je_source_name,
user_je_category_name user_je_category_name,
COUNT(1) rec_count,
SUM(entered_cr) sum_cr,
SUM(entered_dr) sum_dr
FROM XXGL_INTF_DATA_STG
WHERE request_id = pvg_request_id_num
AND status = cp_status_flag
GROUP BY legacy_batch_number,
user_je_source_name,
user_je_category_name
ORDER BY legacy_batch_number;
--Fetch Exceptions Records details.
CURSOR cur_get_exception_records(cp_status_flag IN VARCHAR2)
IS
SELECT legacy_batch_number legacy_batch_number,
user_je_source_name user_je_source_name,
user_je_category_name user_je_category_name,
segment1 || '.' || segment2 || '.' || segment3 || '.' ||
segment4 || '.' || segment5 || '.' || segment6 || '.' ||
segment7 || '.' || segment8 || '.' || segment9 orig_code_combination,
suspense_account suspense_account,
entered_cr entered_cr,
entered_dr entered_dr
FROM XXGL_INTF_DATA_STG
WHERE request_id = pvg_request_id_num
AND status = cp_status_flag
AND suspense_account IS NOT NULL
ORDER BY legacy_batch_number;
BEGIN
--Initialize Local variables
l_total_count := 0;
l_error_count := 0;
l_valid_count := 0;
l_process_count:= 0;
l_sucess_flag := NULL;
l_return_status:= NULL;
l_error_msg := NULL;Package for GL Interface in Oracle EBS: Part6
Package – Part6
-- Set status flag based on process mode parameter.
display_log('Set status flag based on process mode parameter.');
IF UPPER(p_je_proc_mode_in) = 'VALIDATE ONLY'
THEN
l_sucess_flag:= pvg_valid;
ELSE
l_sucess_flag:= pvg_process;
END IF;
-- Get total number of records.
display_log('Get total number of records.');
OPEN cur_get_total_count;
FETCH cur_get_total_count
INTO l_total_count_num;
CLOSE cur_get_total_count;
-- Get total number of records in E status.
display_log('Get total number of records in E status.');
OPEN cur_get_error_count;
FETCH cur_get_error_count
INTO l_error_count_num;
CLOSE cur_get_error_count;
-- Get total number of records in V status.
display_log('Get total number of records in V status.');
OPEN cur_get_valid_count;
FETCH cur_get_valid_count
INTO l_valid_count_num;
CLOSE cur_get_valid_count;
-- Get total number of records in P status.
display_log('Get total number of records in P status.');
OPEN cur_get_process_count;
FETCH cur_get_process_count
INTO l_process_count_num;
CLOSE cur_get_process_count;
-- Display GL common load statistics starts here.
displayput('==========================================================================================================================================================');
displayput('============================================================GL Common Load Statistics Report==============================================================');
displayput('Concurrent Request Id : ' ||
pvg_request_id_num);
displayput(' ');
displayput('Parameters: ');
displayput('----------- ');
displayput('Processing Mode : ' ||
p_je_proc_mode_in);
displayput('Post to GL : ' ||
p_je_post_gl_in);
displayput('Source : ' ||
p_je_source_in);
displayput('Ledger : ' ||
p_je_ledger_name_in);
displayput('Group Id : ' ||
p_je_group_id_in);
displayput('Post Errors to Suspense : ' ||
p_je_post_errors_suspense_in);
displayput('Create Summary Journals : ' ||
p_je_create_summary_in);
displayput('Import Descriptive Flexfiled : ' ||
p_je_import_dff_journals_in);
displayput('Reprocess Error Records : ' ||
p_je_reprocess_error_in);
displayput('Submit Import Program : ' ||
p_je_submit_import_in);
displayput(' ');
displayput('GL Common Load Program Summary ');
displayput('---------------------------------- ');
displayput('Total number of records selected : ' ||
l_total_count_num);
displayput('Total number of records in E (validation error) status : ' ||
l_error_count_num);
displayput('Total number of records in V (validated) status : ' ||
l_valid_count_num);
displayput('Total number of records in P (processed) status : ' ||
l_process_count_num);
displayput(' ');
-- Display Successful records.
display_log('Display Successful records.');
displayput('Successful Records ');
displayput('------------------ ');
displayput(rpad('Journal Batch', 100) ||
rpad('Journal Source', 25) ||
rpad('Journal Category', 25) ||
rpad('Total Number of Records', 25) ||
rpad('Sum Cr', 25) || rpad('Sum Dr', 25));
displayput(rpad('-------------', 100) ||
rpad('--------------', 25) ||
rpad('----------------', 25) ||
rpad('-----------------------', 25) ||
rpad('------', 25) || rpad('------', 25));
FOR cur_get_success_records_rec IN cur_get_success_records(l_sucess_flag)
LOOP
displayput(rpad(cur_get_success_records_rec.legacy_batch_number,
100) || rpad(cur_get_success_records_rec.user_je_source_name,
25) ||
rpad(cur_get_success_records_rec.user_je_category_name,
25) ||
rpad(cur_get_success_records_rec.rec_count, 25) ||
rpad(cur_get_success_records_rec.sum_cr, 25) ||
rpad(cur_get_success_records_rec.sum_dr, 25));
END LOOP; -- End of successful record loop
-- Display Exception records.
displayput(' ');
display_log('Display Exception records.');
displayput('Exceptions ');
displayput('---------- ');
displayput(rpad('Journal Batch', 100) ||
rpad('Journal Source', 25) ||
rpad('Journal Category', 25) ||
rpad('Original code combination', 140) ||
rpad('Suspense Account Mapped', 25) ||
rpad('Entered Cr', 25) || rpad('Entered Dr', 25));
displayput(rpad('-------------', 100) ||
rpad('--------------', 25) ||
rpad('----------------', 25) ||
rpad('-------------------------', 140) ||
rpad('-----------------------', 25) ||
rpad('----------', 25) || rpad('----------', 25));
FOR cur_get_exception_records_rec IN cur_get_exception_records(l_sucess_flag)
LOOP
displayput(rpad(cur_get_exception_records_rec.legacy_batch_number,
100) || rpad(cur_get_exception_records_rec.user_je_source_name,
25) ||
rpad(cur_get_exception_records_rec.user_je_category_name,
25) || rpad(cur_get_exception_records_rec.orig_code_combination,
140) ||
rpad(cur_get_exception_records_rec.suspense_account,
25) ||
rpad(cur_get_exception_records_rec.entered_cr, 25) ||
rpad(cur_get_exception_records_rec.entered_dr, 25));
END LOOP; -- End of exception record loop
-- Calling procedure list_errors to display Error records.
displayput(' ');
display_log('Calling procedure list_errors to display Error records.');
displayput('Records failed Validation ');
displayput('------------------------- ');
IF UPPER(l_return_status) = 'E'
THEN
display_log('Error Calling procedure list_errors to display Error records.');
ELSE
display_log('Calling procedure list_errors to display Error records successful.');
END IF;
display_Out(' ');
EXCEPTION
WHEN OTHERS THEN
display_log('Exception in procedure- display_stats: ' || '-' ||
SQLERRM);
END display_stats;
/*====================================================================
Name: main
Description: This procedure will be the main procedure to be called by
the GL common load program.
====================================================================*/
PROCEDURE main(p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER,
p_processing_mode IN VARCHAR2,
p_post_to_gl IN VARCHAR2,
p_source IN VARCHAR2,
p_ledger IN VARCHAR2,
p_group_id IN VARCHAR2,
p_post_errors_to_suspense IN VARCHAR2,
p_create_summary_journals IN VARCHAR2,
p_import_dff_journals IN VARCHAR2,
p_reprocess_error_records IN VARCHAR2,
p_submit_import_program IN VARCHAR2)
IS
--Local Variables Declaration
l_process_flag VARCHAR2(1);
l_error_flag VARCHAR2(1);
l_validate_errbuf VARCHAR2(4000);
l_validate_retcode NUMBER;
l_glint_errbuf VARCHAR2(4000);
l_glint_retcode NUMBER;
l_import_errbuf VARCHAR2(4000);
l_email_retcode NUMBER;
l_email_errbuf VARCHAR2(4000);
l_import_retcode NUMBER;
l_import_request_id NUMBER;
l_post_request_id NUMBER;
l_email_return_status VARCHAR2(1);
l_email_error_msg VARCHAR2(4000);
l_control_sequence NUMBER;
l_je_source_name gl_je_sources.je_source_name%TYPE;
l_chart_of_accounts_idgl_ledgers.chart_of_accounts_id%TYPE;
l_posting_run_id gl_je_batches.posting_run_id%TYPE;
--Fetch source name
CURSOR cur_get_source_name(cp_source_name IN VARCHAR2)
IS
SELECT je_source_name
FROM gl_je_sources
WHERE user_je_source_name = cp_source_name;
--Fetch legacy batch name
CURSOR cur_get_legacy_batch(cp_source IN VARCHAR2,
cp_group_id IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT DISTINCT legacy_batch_number
FROM XXGL_INTF_DATA_STG
WHERE status = pvg_new
AND (group_id IS NULL OR staging_transaction_id IS NULL)
AND user_je_source_name = NVL(cp_source, user_je_source_name)
--User Journal Source name
AND legacy_batch_number = NVL(cp_group_id, legacy_batch_number)
AND legacy_ledger_name =
NVL(cp_ledger_name, legacy_ledger_name)
ORDER BY legacy_batch_number;
--Fetch chart of account
CURSOR cur_get_chart_of_account(cp_ledger_id IN NUMBER)
IS
SELECT chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = cp_ledger_id;
--Fetch legacy batch name for validated records
CURSOR cur_get_validated_batch(cp_source IN VARCHAR2,
cp_group_id IN VARCHAR2,
cp_ledger_name IN VARCHAR2)
IS
SELECT DISTINCT legacy_batch_number,
user_je_source_name,
group_id,
legacy_ledger_name,
ledger_id
FROM XXGL_INTF_DATA_STG
WHERE status = pvg_valid
AND group_id IS NOT NULL
AND user_je_source_name = NVL(cp_source, user_je_source_name)
--User Journal Source name
AND legacy_batch_number = NVL(cp_group_id, legacy_batch_number)
AND legacy_ledger_name =
NVL(cp_ledger_name, legacy_ledger_name);GL Interface Package in Oracle EBS: Part7
Package – Part7
BEGIN
--Initialize Local variables
l_process_flag := NULL;
l_error_flag := 'N';
l_validate_errbuf := NULL;
l_validate_retcode := 0;
l_email_retcode := 0;
l_email_errbuf := NULL;
l_email_return_status:= NULL;
l_email_error_msg := NULL;
l_je_source_name := NULL;
-- Populating group_id into staging table based on legacy batch.
display_log('Populating group_id into staging table based on legacy batch.');
FOR cur_get_legacy_batch_rec IN cur_get_legacy_batch(p_source_in,
p_group_id_in,
p_ledger_in)
LOOP
l_control_sequence:= 0;
l_control_sequence:= gl_interface_control_s.nextval;
BEGIN
UPDATE XXGL_INTF_DATA_STG
SET group_id = l_control_sequence_num,
staging_transaction_id = XXGL_INTF_DATA_STG_s.nextval
WHERE status = pvg_new
AND legacy_batch_number =
cur_get_legacy_batch_rec.legacy_batch_number;
EXCEPTION
WHEN OTHERS THEN
l_error_flag:= 'Y';
display_log('Exception in updating group_id to table XXGL_INTF_DATA_STG: ' || '-' ||
SQLERRM);
END;
END LOOP; -- legacy batch loop Ends
COMMIT;
-- Firstly, Setting process flag status based on
-- Then, p_reprocess_error_records parameter values.
display_log('Setting process flag status.');
IF UPPER(p_reprocess_error_records_in) = 'ALL'
THEN
BEGIN
UPDATE XXGL_INTF_DATA_STG
SET status = pvg_new
WHERE status IN (pvg_error, pvg_valid)
AND user_je_source_name =
NVL(p_source_in, user_je_source_name)
--Batch Number
AND legacy_batch_number =
NVL(p_group_id_in, legacy_batch_number)
AND legacy_ledger_name =
NVL(p_ledger_in, legacy_ledger_name);
COMMIT;
l_process_flag:= 'N';
EXCEPTION
WHEN OTHERS THEN
l_error_flag:= 'Y';
display_log('Exception in updating status to table XXGL_INTF_DATA_STG: ' || '-' ||
SQLERRM);
END;
ELSIF UPPER(p_reprocess_error_records_in) = 'VALIDATION ERROR'
THEN
l_process_flag:= 'E';
ELSE
l_process_flag:= 'N';
END IF;
-- Check if p_processing_mode <> IMPORT VALIDATED RECORDS.
display_log('Check if p_processing_mode <> IMPORT VALIDATED RECORDS.');
IF UPPER(p_processing_mode_in) <> 'IMPORT VALIDATED RECORDS' AND
l_process_flagIS NOT NULL
THEN
-- Calling Validation procedure
validate(p_errbuf => l_validate_errbuf,
p_retcode => l_validate_retcode_num,
p_status_flag => l_process_flag,
p_va_source => p_source_in,
p_va_group_id => p_group_id_in,
p_va_ledger_name => p_ledger_in);
END IF;
-- Check procedure call status.
IF l_validate_retcode= pvg_func_error_num
THEN
display_log('Error in calling procedure validate.');
ELSE
display_log('Procedure call to validate is successful.');
END IF;
-- Check if p_processing_mode <> VALIDATE ONLY.
display_log('Check if p_processing_mode <> VALIDATE ONLY.');
IF UPPER(p_processing_mode_in) <> 'VALIDATE ONLY'
THEN
FOR get_validated_rec IN cur_get_validated_batch(p_source_in,
p_group_id_in,
p_ledger_in)
LOOP
--Initialize Local variables
l_glint_errbuf := NULL;
l_glint_retcode := 0;
l_import_errbuf := NULL;
l_import_retcode := 0;
l_import_request_id:= 0;
l_post_request_id := 0;
-- Getting je source name.
display_log('Getting je source name.');
OPEN cur_get_source_name(get_validated_rec.user_je_source_name);
FETCH cur_get_source_name
INTO l_je_source_name;
IF cur_get_source_name%NOTFOUND
THEN
display_log('Je source name is invalid: ' ||
l_je_source_name|| '.');
END IF;
CLOSE cur_get_source_name;
-- Calling insert_rec_gl_interface procedure.
display_log('Calling insert_rec_gl_interface procedure.');
insert_rec_gl_interface(p_errbuf => l_glint_errbuf,
p_retcode => l_glint_retcode_num,
p_batch_name => get_validated_rec.legacy_batch_number,
p_gl_source => get_validated_rec.user_je_source_name,
p_gl_group_id => get_validated_rec.group_id,
p_gl_ledger_name => get_validated_rec.legacy_ledger_name);
-- Check procedure call status.
IF l_glint_retcode= pvg_func_error_num
THEN
display_log('Error in calling procedure insert_rec_gl_interface.');
ELSE
display_log('Procedute call to insert_rec_gl_interface is successful.');
END IF;
-- Check submit import program parameter and gl_interface insert status.
display_log('Check submit import program parameter and gl_interface insert status.');
IF UPPER(p_submit_import_program_in) = 'Y' AND
l_glint_retcode= 0
THEN
display_log('Submit standard Journal Import program.');
submit_request(p_errbuf => l_import_errbuf,
p_retcode => l_import_retcode_num,
p_appl_short_name => 'SQLGL',
p_program => 'GLLEZLSRS',
p_description => 'Standard Journal Import Program',
p_argument1 => pvg_access_set_id_num,
p_argument2 => l_je_source_name,
p_argument3 => get_validated_rec.ledger_id,
p_argument4 => get_validated_rec.group_id,
p_argument5 => p_post_errors_to_suspense_in,
p_argument6 => p_create_summary_journals_in,
p_argument7 => p_import_dff_journals_in,
p_argument8 => NULL, p_argument9 => NULL,
p_argument10 => NULL,
p_request_id => l_import_request_id_num);
display_log('Submit standard Journal Import program successful for the Request Id: ' ||
l_import_request_id|| '.');
-- Getting Chart of account.
display_log('Getting chart of account.');
OPEN cur_get_chart_of_account(get_validated_rec.ledger_id);
FETCH cur_get_chart_of_account
INTO l_chart_of_accounts_id_num;
IF cur_get_chart_of_account%NOTFOUND
THEN
display_log('Chart of account id is invalid: ' ||
l_chart_of_accounts_id|| '.');
END IF;
CLOSE cur_get_chart_of_account;
IF UPPER(p_post_to_gl_in) = 'Y' AND
l_import_request_id<> 0 AND
l_chart_of_accounts_idIS NOT NULL
THEN
-- Getting posting run id.
display_log('Getting posting run id.');
l_posting_run_id:= gl_je_posting_s.nextval;
display_log('Posting run id : ' || l_posting_run_id|| '.');
-- Updating gl_je_batch with posting run id
BEGIN
UPDATE gl_je_batches
SET status = 'S',
posting_run_id = l_posting_run_id_num
WHERE group_id = get_validated_rec.group_id
AND status <> 'P';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
display_log('Exception in updating table gl_je_batches: ' || '-' ||
SQLERRM);
END;
-- Submit Standard Journal Post Program.
display_log('Submit Standard Journal Post Program.');
submit_request(p_errbuf => l_import_errbuf,
p_retcode => l_import_retcode_num,
p_appl_short_name => 'SQLGL',
p_program => 'GLPPOS',
p_description => 'Standard Journal Post Program',
p_argument1 => get_validated_rec.ledger_id,
p_argument2 => pvg_access_set_id_num,
p_argument3 => l_chart_of_accounts_id_num,
p_argument4 => l_posting_run_id_num,
p_request_id => l_post_request_id_num);
END IF;
-- Post to GL submit Ends
END IF;
--Sandard journal import ENDs
END LOOP; --validated batch loop Ends
END IF;
-- Call procedure display_stats to write details to Output file.
display_log('Call procedure display_stats to write details to Output file.');
display_stats(p_je_proc_mode => p_processing_mode_in,
p_je_post_gl => p_post_to_gl_in,
p_je_source => p_source_in,
p_je_ledger_name => p_ledger_in,
p_je_group_id => p_group_id_in,
p_je_post_errors_suspense => p_post_errors_to_suspense_in,
p_je_create_summary => p_create_summary_journals_in,
p_je_import_dff_journals => p_import_dff_journals_in,
p_je_reprocess_error => p_reprocess_error_records_in,
p_je_submit_import => p_submit_import_program_in);
display_log('Procedure call to display_stats is successful.');
EXCEPTION
WHEN OTHERS THEN
p_retcode := pvg_func_error_num;
display_log('Exception in procedure- main: ' || '-' || SQLERRM);
END main;
END XXGL_INTERFACE_PKG; GL Daily Rates Example:
- Daily Rates GL interface tables GL_DAILY_RATES and GL_DAILY_RATES_INTERFACE: Base and interface tables for daily rates.
- Table XXGL_DAILY_RATES_STG: Staging table for data movement.
- Package XXGL_DAILY_RATES_PKG: Package for validations and data insertion into the interface table.
Conclusion: GL Interface in Oracle Apps R12
The GL interface plays a vital role in the financial management of organizations in Oracle Apps R12. By facilitating the accurate and timely transfer of financial data from subledgers to the General Ledger, the interface contributes to the integrity of financial reporting and enables informed decision-making. Please review our articles for useful blogs for Oracle EBS, Python and Automation Solution. Happy Learning!
