Complete GL Interface – PL/SQL Procedure
————–
a) set of books should be defined
b) Current Conversion rates and accounting periods need to be defines
c) Source and Category Name Should be defined
Interface Tables:
—————-
GL_INTERFACE
Base Tables:
———–
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BATCHES
Standard Program:
Go to General Ledger Vision Operations(USA)
Run =>Import
Here Give the Source name and Save.
While Click on the save button Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there………..
Validation Columns:
——————
Source period_name currency_code set_of_books_id
je_source je_catregory accounting_date entered_dr , entered_cr
accounted_cr accounted_dr encumberance_type_id
Source = ‘NEW’
period need to be open status in gl_period_statuses
souce_name defined in gl_je_source table
category_name defines gl_je_Category
currency available in fnd_Currencies
accounted_cr and accounted_dr total should be same.
Control file for GL_Interface:
LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
GROUP_ID)
BEGIN DATA
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11
Script
sqlldr apps/apps control=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl’ log=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log’
exit 0
GL Interface Package:
CREATE OR REPLACE package body APPS.xx_gl_int_pkg
is
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;
procedure main(errbuf out varchar2,
retcode out varchar2
)
is
cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
v_gl_int gl_interface%rowtype;
v_process_flag varchar2(10);
v_error_msg varchar2(100);
v_tot_err_msg varchar2(1000);
begin
dis_log(‘before entering the loop’);
for i in c1 loop
v_error_msg :=null;
v_process_flag:=’S’;
v_tot_err_msg:=null;
v_gl_int:=null;
–currency_code validation
begin
select currency_code into v_gl_int.currency_code
from fnd_currencies
where currency_code=i.currency_code;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid Currency Code =>’||i.currency_code;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at Currency Code =>’||i.currency_code;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
–user_je_source_name validation
begin
select user_je_source_name into v_gl_int.user_je_source_name
from gl_je_sources
where user_je_source_name=i.user_je_source_name;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid Sourec Name =>’||i.user_je_source_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at Sourec Name =>’||i.user_je_source_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
–category_name validation
begin
select user_je_category_name into v_gl_int.user_je_category_name
from gl_je_categories
where user_je_category_name=i.user_je_category_name;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid category_name =>’||i.user_je_category_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at category_name =>’||i.user_je_category_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
–user id validation
begin
select user_id into v_gl_int.created_by from fnd_user
where user_id = i.created_by;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid user id =>’||i.created_by;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at user id =>’||i.created_by;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
— set of books id validation
begin
SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid set of books id =>’||i.set_of_books_id;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception atset of books id =>’||i.set_of_books_id;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
v_gl_int.status :=i.status;
— v_gl_int.set_of_books_id :=i.set_of_books_id;
v_gl_int.accounting_date :=i.accounting_date;
— v_gl_int.currency_code :=i.currency_code;
v_gl_int.date_created :=i.date_created;
–v_gl_int.created_by :=i.created_by;
v_gl_int.actual_flag :=i.actual_flag ;
–v_gl_int.user_je_category_name :=i.user_je_category_name;
–v_gl_int.user_je_source_name :=i.user_je_source_name;
v_gl_int.segment1 :=i.segment1;
v_gl_int.segment2 :=i.segment2;
v_gl_int.segment3 :=i.segment3;
v_gl_int.segment4 :=i.segment4;
v_gl_int.segment5 :=i.segment5 ;
v_gl_int.entered_dr :=i.entered_dr;
v_gl_int.entered_cr :=i.entered_cr;
v_gl_int.accounted_dr :=i.accounted_dr;
v_gl_int.accounted_cr :=i.accounted_cr;
v_gl_int.group_id :=i.group_id;
dis_log(‘before inserting the loop’);
if v_process_flag = ‘S’ then
insert into gl_interface values v_gl_int;
end if;
update GL_INTERFACE_TEMP set process_flag=v_process_flag,
error_message=v_tot_err_msg
where rowid=i.row_id;
dis_log(‘after inserting the loop’);
end loop;
exception
when others then
dis_log(‘exception occured at main loop’);
end main;
end xx_gl_int_pkg;
Leave a Reply
Want to join the discussion?Feel free to contribute!