Friday, August 22, 2014

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

 

 

2 comments:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete

  2. Thanks for the given information you are providing content very usefull to users.
    Learn Complete GL Interface Program in Oracle Apps R12 Actual cost of this course is 3,200/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course

    ReplyDelete