CREATE TABLE XX_GL_INTERFACE (
STATUS CHAR (3),
SET_OF_BOOKS_NAME CHAR (23),
ACCOUNTING_DATE CHAR (11),
CURRENCY CHAR (3),
CREATED_DATE CHAR (11),
CREATED_BY CHAR (2),
ACTUAL_FLAG CHAR (1),
SOURCE CHAR (6),
CATEGORY CHAR (12),
SEGMENT1 VARCHAR2 (25),
SEGMENT2 VARCHAR2 (25),
SEGMENT3 VARCHAR2 (25),
SEGMENT4 VARCHAR2 (25),
SEGMENT5 VARCHAR2 (25),
XX_ENTERED_CR NUMBER,
XX_ENTERED_DR NUMBER)
LOAD DATA
infile 'D:\oraforms\gldata.dat'
replace
into table XX_GL_INTERFACE
fields terminated by ","
TRAILING NULLCOLS
(STATUS,
SET_OF_BOOKS_NAME,
ACCOUNTING_DATE DATE 'DD-MON-RRRR',
CURRENCY,
CREATED_date DATE 'DD-MON-RRRR',
CREATED_BY,
ACTUAL_FLAG,
SOURCE,
CATEGORY,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
XX_ENTERED_CR,
XX_ENTERED_DR)
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,402,7340,0000,000,100,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,404,7340,0000,000,,100,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,410,7340,0000,000,200,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,430,2555,0000,000,,200,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,402,2555,0000,000,300,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,000,2520,2113,000,,300,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,000,1450,0000,000,250,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,000,1420,0000,000,,250,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,510,5210,0000,000,125,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,000,1460,0000,000,,125,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,000,1810,0000,000,107,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,520,5210,0000,000,,107,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,520,5290,0000,000,127,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,000,1430,0000,000,,127,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,520,5310,0000,000,135,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,520,5380,0000,000,,135,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,520,5370,0000,000,128,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,520,5360,0000,000,,128,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,520,5390,0000,000,66,,
New,Vision Operations (USA),30-APR-2005,USD,30-APR-2005,10,A,Manual,Expenditures,01,470,7730,0000,000,,66,
create OR REPLACE procedure GL_data_validation_pro(outbuff out varchar2,retcode out number) is
v_set_of_book_id number;
x_user_je_source_name gl_interface.USER_JE_SOURCE_NAME%type;
x_currency_code gl_interface.currency_code%type;
v_sum_cr gl_interface.entered_cr%type;
v_sum_dr gl_interface.entered_dr%type;
v_entered_cr gl_interface.entered_cr%type;
v_entered_dr gl_interface.entered_dr%type;
x_conversion_rate number;
v_func_currecny varchar2(20);
v_data_error boolean:=FALSE;
X_PERIOD VARCHAR2(10);
cursor xx_sobname is select distinct set_of_books_NAME from XX_gl_interface;
cursor xx_intfdata(p_sobname in varchar2) is select * from xx_gl_interface where set_of_books_NAME=p_sobname;
begin
fnd_file.put_line(fnd_file.log,'===================================');
fnd_file.put_line(fnd_file.log,'cUSTUM gL DATA iNTERFACE pROC');
fnd_file.put_line(fnd_file.log,'===================================');
for j in xx_sobname loop
fnd_file.put_line(fnd_file.log,'sTARTED vALIDATING sob:'||j.set_of_books_NAME);
begin
select sum(xx_entered_cr),sum(xx_entered_dr) into v_sum_cr,v_sum_cr
from xx_gl_interface where set_of_books_NAME=j.set_of_books_NAME;
IF V_SUM_CR!=V_SUM_DR THEN
fnd_file.put_line(fnd_file.log,'Error in credit or debit sum');
v_data_error:=TRUE;
end if;
end;
for i in xx_intfdata(j.set_of_books_NAME) loop
begin
select set_of_books_id,currency_code into v_set_of_book_id,v_func_currecny from gl_setS_of_books
where name=i.set_of_books_NAME;
exception when no_data_found then
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'No SOB details');
WHEN TOO_MANY_ROWS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'TOO MANY SOBS');
WHEN OTHERS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'No SOB OTHER ERROR'||SQLERRM);
end;
BEGIN
SELECT user_je_source_name
INTO x_user_je_source_name
FROM gl_je_sources_v
WHERE user_je_source_name = i.source;
EXCEPTION
WHEN no_data_found THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR nO SOURCE');
v_data_error:=TRUE;
WHEN TOO_MANY_ROWS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'TOO MANY SOURCES');
WHEN OTHERS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'No SOURCES OTHER ERROR'||SQLERRM);
END;
BEGIN
SELECT currency_code
INTO x_currency_code
FROM fnd_currencies_vl
WHERE currency_code = i.currency;
EXCEPTION WHEN NO_DATA_FOUND then
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Currency Code');
v_data_error:=TRUE;
WHEN TOO_MANY_ROWS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'TOO MANY CURRECNIES');
WHEN OTHERS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'No CURRECNY OTHER ERROR'||SQLERRM);
end;
BEGIN
SELECT closing_status
INTO x_period
FROM gl_period_statuses
WHERE set_of_books_id = v_set_of_book_id
AND closing_status = 'O'
AND adjustment_period_flag = 'N'
AND TO_DATE(I.accounting_date,'DD-MON-RRRR') BETWEEN start_date AND end_date AND ROWNUM=1;
EXCEPTION
WHEN no_data_found THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'PERIOD OPEN ERROR');
v_data_error:=TRUE;
WHEN TOO_MANY_ROWS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'TOO MANY PERIODS');
WHEN OTHERS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'nO pERIOD OTHER ERROR'||SQLERRM);
END;
if v_func_currecny!=i.currency then
bEGIN
SELECT conversion_rate
INTO x_conversion_rate
FROM gl_daily_rates
WHERE from_currency = i.currency
AND to_currency = v_func_currecny
AND TRUNC(conversion_date) = TRUNC(to_date(i.accounting_date,'DD-MON-RRRR'))
AND conversion_type ='Corporate'
AND status_code in ('C', 'O')
AND rownum < 2;
v_entered_cr:=i.xx_entered_cr*x_conversion_rate;
v_entered_dr:=i.xx_entered_dr*x_conversion_rate;
exception when no_data_found then
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Conversion rates');
v_data_error:=TRUE;
WHEN TOO_MANY_ROWS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'TOO MANY CONVERSION RATES');
WHEN OTHERS THEN
v_data_error:=TRUE;
fnd_file.put_line(fnd_file.log,'CURRECNY COMNVERSION OTHER'||SQLERRM);
end;
else
v_entered_cr:=i.xx_entered_cr;
v_entered_dr:=i.xx_entered_dr;
end if;
IF not v_data_error THEN
insert into gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_source_name,
user_je_category_name,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_cr,
entered_dr)
values
(i.status,
v_set_of_book_id,
i.accounting_date,
i.currency,
i.created_date,
i.created_by,
i.actual_flag,
i.source,
i.category,
i.segment1,
i.segment2,
i.segment3,
i.segment4,
i.segment5,
i.xx_entered_cr,
i.xx_entered_dr);
END IF;
end loop;
end loop;
commit;
exception when others then
fnd_file.put_line(fnd_file.log,'xx'||sqlerrm);
end;
/
No comments:
Post a Comment