Oracle GL Journal Interface

Using GL_INTERFACE table and Journal Import program in Oracle Applications

--DERIVE LEDGER ID

select * from gl_ledgers;

--CATEGORY NAME IS VALID OR NOT

SELECT 'X'
INTO x_variable
FROM gl_je_categories gjc
WHERE UPPER (gjc.user_je_category_name) = UPPER (p_cat_name);


--SOURCE NAME IS VALID OR NOT

SELECT 'X'
INTO x_variable
FROM gl_je_sources gjs
WHERE UPPER (gjs.user_je_source_name) = UPPER (p_source_name)


CHECK THAT PERTICULAR PERIOD IS OPEN OR NOT BY PASSING PARAMETER AS PERIOD NAME AND LEDGER ID

IF p_transaction_date IS NOT NULL
THEN

SELECT 'X'
INTO x_variable
FROM apps.gl_period_statuses gps,
apps.fnd_application fa,
apps.gl_periods glp
WHERE gps.period_name = glp.period_name
AND gps.closing_status IN ('O', 'F')
AND fa.application_short_name = 'SQLGL'
AND gps.application_id = fa.application_id
AND glp.period_set_name = 'XXX' --period set name
AND p_transaction_date BETWEEN glp.start_date AND glp.end_date
AND gps.ledger_id = v_ledger_id;
END IF;

--INSERT TO INTERFACE TABLE

BEGIN
INSERT INTO gl_interface
(status,
ledger_id,
accounting_date,
transaction_date,
attribute11,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
code_combination_id, entered_dr,
entered_cr, accounted_dr,
accounted_cr, reference1,
reference2, reference3,
reference4, reference5,
reference6, reference7,
reference8, reference9,
reference10,period_name,
reference_date, GROUP_ID)
VALUES ('NEW',
c_emp_pre.ledger_id,
c_emp_pre.transaction_date,
c_emp_pre.transaction_date,
c_emp_pre.project_id, --c_emp_pre.attribute1,
c_emp_pre.currency_code, SYSDATE, v_user_id,
c_emp_pre.actual_flag,
c_emp_pre.user_je_category_name,
c_emp_pre.user_je_source_name,
c_emp_pre.currency_conversion_date,
c_emp_pre.user_currency_conversion_type,
c_emp_pre.currency_conversion_rate,
c_emp_pre.ccid, c_emp_pre.entered_dr,
c_emp_pre.entered_cr, c_emp_pre.accounted_dr,
c_emp_pre.accounted_cr, c_emp_pre.reference1,
c_emp_pre.reference2, c_emp_pre.reference3,
c_emp_pre.reference4, c_emp_pre.reference5,
c_emp_pre.reference6, c_emp_pre.reference7,
c_emp_pre.reference8, c_emp_pre.reference9,
c_emp_pre.reference10|| ' - '|| c_emp_pre.jgzz_recon_ref,
c_emp_pre.period_name,
c_emp_pre.reference_date, v_request_id
);

After this you can upload this data by log in to application and importing journals from general ledger responsibility using import journal.

--If you want lanch 'Journal Import' program using API FND_REQUEST.SUBMIT_REQUEST to load data from interface table to Oracle GL

--Variables declaration

v_interface_run_id number;
v_je_source_name varchar2(25);
v_status varchar2(1) := 'S';
v_post_error_to_susp_flag varchar2(1) := 'N';
v_from_date date;
v_to_date date;
v_create_sumjourn_flag varchar2(1) := 'N';
v_desc_flex_flag varchar2(1) := 'W'; -- flag set to import with DFF with Validation
v_group_id number;
v_source varchar2(25);
v_sobid number:=1;
v_cr_interval number := 60; -- seconds
v_cr_max_wait number := 0; -- seconds(0 = Will not time out)
v_cr_phase_code varchar2(30);
v_cr_status_code varchar2(30);
v_cr_dev_phase varchar2(30);
v_cr_dev_status varchar2(30);
v_cr_message varchar2(240);
v_jimport_cr_complete boolean;
v_submit_jimport_failed exception;
v_run_jimport_failed exception;
v_records_left number;
v_error_code number;
v_error_text varchar2(250);

SELECT gl_journal_import_s.nextval
INTO v_interface_run_id
FROM dual;

INSERT INTO gl_interface_control
(status, set_of_books_id,interface_run_id, group_id, je_source_name)
VALUES (v_status, v_sobid, v_interface_run_id, v_group_id, v_je_source_name);

COMMIT;

p_jimport_cr_id := FND_REQUEST.SUBMIT_REQUEST('SQLGL','GLLEZL',
'Journal Import', '',FALSE,
v_interface_run_id,
v_sobid,
v_post_error_to_susp_flag,
v_from_date,
v_to_date,
v_create_sumjourn_flag,
v_desc_flex_flag);
COMMIT;

-- This is necessary to submit the process to the Concurrent Manager;

--- 'p_jimport_cr_id' is set to the Concurrent Request ID if the request was
--- successful and to 0 if it was not.
---

IF p_jimport_cr_id = 0
THEN
raise v_submit_jimport_failed;
ELSE
v_jimport_cr_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(p_jimport_cr_id,
v_cr_interval,
v_cr_max_wait,
v_cr_phase_code,
v_cr_status_code,
v_cr_dev_phase,
v_cr_dev_status,
v_cr_message);
IF (v_cr_dev_phase = 'COMPLETE' and v_cr_dev_status != 'NORMAL')
THEN
raise v_run_jimport_failed;
END IF;

Base Tables:

After import-

GL_JE_HEADERS

GL_JE_LINES

After posting journals:

GL_BALANCES