troubleshooting Question

Package Error for Validations

Avatar of samisyed
samisyed asked on
Oracle Database
6 Comments1 Solution1050 ViewsLast Modified:
create or replace package DL_JRNL_IMP_PKG
is
procedure validate_jrnl_imp_data;
end DL_JRNL_IMP_PKG;
/

create or replace package body DL_JRNL_IMP_PKG
is
procedure validate_jrnl_imp_data
is
err_msg varchar2(2000);
err_code number;
msg_text varchar2(2000);
v_set_of_books_id varchar2(10);
v_segment1 varchar2(10);
v_segment2 varchar2(10);
v_segment3 varchar2(10);
v_segment4 varchar2(10);
v_segment5 varchar2(10);
v_segment6 varchar2(10);
v_status varchar2(10);
v_user_je_source_name varchar2(10);
v_user_je_category_name varchar2(10);
v_actual_flag varchar2(10);
v_comb varchar2(10);
v_budget_version_id varchar2(10);
v_encumbrance_type_id varchar2(10);
v_proceed number:=1;
cursor c_dl_jrnl_stg is select * from DL_JRNL_STG;
begin
--------------------  Validation for Segments 1-6    ----------------------
for i in c_dl_jrnl_stg
loop
select distinct(segment1)into v_segment1
from gl_code_combinations
where segment1=i.segment1;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Segment1'
where segment1=i.segment1;
v_proceed:=0;
end;

select distinct(segment2) into v_segment2
from gl_code_combinations
where segment2=i.segment2;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Segment2'
where segment2=i.segment2;
v_proceed:=0;
end;

select distinct(segment3) into v_segment3
from gl_code_combinations
where segment3.i.segment3;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Segment3'
where segment3=i.segment3;
v_proceed:=0;
end;

select distinct(segment4) into v_segment4
from gl_code_combinations
where segment4=i.segment4;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Segment4'
where segment4=i.segment4;
v_proceed:=0;
end;

select distinct(segment5) into v_segment5
from gl_code_combinations
where segment5=i.segment5;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Segment5'
where segment5=i.segment5;
v_proceed:=0;
end;

select distinct(segment6) into v_segment6
from gl_code_combinations
where segment6=i.segment6;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Segment6';
v_proceed:=0;
end;

-------------------   END OF SEGMENTS VALIDATIONS  ---------------------

------- Validation for NEW Status   -----------------------------------
select rownum into v_status
from dual
where i.status='NEW';
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Status'
where status=i.status;
v_proceed:=0;
end;

----- End of Status Validation  ----------------------------------------

----- Validation for Source Name    -----------------------------------
select distinct(user_je_source_name) into v_user_je_source_name
from gl_je_sources
where user_je_source_name=i.user_je_source_name;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Source Name'
where user_je_source_name=i.user_je_source_name;
v_proceed:=0;
end;
---- End of Source Name Validation --------------------------------------

---- Validation for Category Name    ------------------------------------
select distinct(user_je_category_name) into v_user_je_category_name
from gl_je_categories
where user_je_category_name=i.user_je_category_name;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Category Name'
where user_je_category_name=i.user_je_category_name;
v_proceed:=0;
end;
----  End of Category Name Validaton     --------------------------------

------- Validation for Set of Books Id    -------------------------------
select distinct(set_of_books_id) into v_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
begin
update dl_jrnl_stg
set error_msg='Invalid SOB'
where set_of_books_id=i.set_of_books_id;
v_proceed:=0;
end;
----- End of SOB Validation    ----------------------------------------

------- Validation for Actual Flag  -----------------------------------
select rownum into v_actual_flag
from dual
where i.actual_flag in ('A','B','E');
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Flag'
where actual_flag=i.actual_flag;
v_proceed:=0;
end;
------ End of Actual Flag Validation   -------------------------------

----- Validation for Combination of Flag with ids   -----------------
select rownum into v_comb
from dual
where i.actual_flag='A'
and i.budget_version_id=null
and i.encumbrance_type_id=null;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='For Actual Flag A,budget_id and encumbrance_id must be null'
where actual_flag=i.actual_flag
and budget_version_id=i.budget_version_id
and encumbrance_type_id=i.encumbrance_type_id;
v_proceed:=0;
end;
----- End of Validation for Flag A with Combinations     --------------

---- Validation of Budget Version Id   -------------------------------
select distinct(budget_version_id) into v_budget_version_id
from gl_budget_versions
where i.actual_flag='B'
and budget_version_id=i.budget_version_id
and status in('C','O');
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Budget id for actual flag B'
where i.actual_flag='B'
and budget_verion_id=i.budget_version_id;
v_proceed:=0;
end;
-----Validation for Encumbrance Type Id  -----------------------------
select distinct(encumbrance_type_id) into v_encumbrance_type_id
from gl_encumbrance_types
where i.actual_flag='E'
and encumbrance_type_id=i.encumbrance_type_id;
exception
when no_data_found then
begin
update dl_jrnl_stg
set error_msg='Invalid Encumbrance id for Actual flag E'
where i.actual_flag='E'
and encumbrance_type_id=i.encumbrance_type_id;
v_proceed:=0;
end;
----Emd of emcumbrance id validation   ------------------------------
if ( v_proceed<>0) then
begin
insert into GL_INTERFACE(
 (
        STATUS                          ,
        SET_OF_BOOKS_ID                 ,
        USER_JE_SOURCE_NAME             ,
        USER_JE_CATEGORY_NAME           ,
        ACCOUNTING_DATE                 ,
        CURRENCY_CODE                   ,
        DATE_CREATED                    ,
        CREATED_BY                      ,
        ACTUAL_FLAG                     ,
        ENCUMBRANCE_TYPE_ID             ,
        BUDGET_VERSION_ID               ,
        USER_CURRENCY_CONVERSION_TYPE   ,
        CURRENCY_CONVERSION_DATE        ,
        CURRENCY_CONVERSION_RATE        ,
        SEGMENT1                        ,
        SEGMENT2                        ,
        SEGMENT3                        ,
        SEGMENT4                        ,
        SEGMENT5                        ,
        SEGMENT6                        ,
        ENTERED_DR                      ,
        ENTERED_CR                      ,
        ACCOUNTED_DR                    ,
        ACCOUNTED_CR                    ,
        PERIOD_NAME                     ,
        REFERENCE1                      ,
        REFERENCE2                      ,
        REFERENCE4                      ,
        REFERENCE5
)
values
(
        i.STATUS                                ,
        i.SET_OF_BOOKS_ID                 ,
        i.USER_JE_SOURCE_NAME             ,
        i.USER_JE_CATEGORY_NAME           ,
        i.ACCOUNTING_DATE                 ,
        i.CURRENCY_CODE                   ,
        sysdate                         ,
        fnd_global.user_id              ,
        i.ACTUAL_FLAG                     ,
        i.ENCUMBRANCE_TYPE_ID             ,
        i.BUDGET_VERSION_ID               ,
        i.USER_CURRENCY_CONVERSION_TYPE   ,
        i.CURRENCY_CONVERSION_DATE        ,
        i.CURRENCY_CONVERSION_RATE        ,
        i.SEGMENT1                        ,
        i.SEGMENT2                        ,
        i.SEGMENT3                        ,
        i.SEGMENT4                        ,
        i.SEGMENT5                        ,
        i.SEGMENT6                        ,
        i.ENTERED_DR                      ,
        i.ENTERED_CR                      ,
        i.ACCOUNTED_DR                    ,
        i.ACCOUNTED_CR                    ,
        i.PERIOD_NAME                     ,
        i.REFERENCE1                      ,
        i.REFERENCE2                      ,
        i.REFERENCE4                      ,
        i.REFERENCE5
);
commit;
exception
when others then
dbms_output.put_line('Unknown Error Found');
end;
end if;
end loop;
end;
end DL_JRNL_IMP_PKG ;
/
sho err;
/

This is my Code for Validation, here i'm getting the following errors :
Plz help in rectifying.

LINE/COL ERROR
-------- -----------------------------------------------------------------
31/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

43/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

55/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>

LINE/COL ERROR
-------- -----------------------------------------------------------------
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

67/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback

LINE/COL ERROR
-------- -----------------------------------------------------------------
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

79/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

LINE/COL ERROR
-------- -----------------------------------------------------------------

91/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

105/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one

LINE/COL ERROR
-------- -----------------------------------------------------------------
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

120/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma

LINE/COL ERROR
-------- -----------------------------------------------------------------
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

134/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<

LINE/COL ERROR
-------- -----------------------------------------------------------------
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

148/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge

LINE/COL ERROR
-------- -----------------------------------------------------------------
         <a single-quoted SQL string> pipe

162/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe


LINE/COL ERROR
-------- -----------------------------------------------------------------
178/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

196/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

210/1    PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update when while with <an identifier>

LINE/COL ERROR
-------- -----------------------------------------------------------------
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

224/2    PLS-00103: Encountered the symbol "STATUS" when expecting one of
         the following:
         ( select
         The symbol "select" was substituted for "STATUS" to continue.

253/1    PLS-00103: Encountered the symbol ")" when expecting one of the

LINE/COL ERROR
-------- -----------------------------------------------------------------
         following:
         . ( , * @ % & - + / at mod rem <an identifier>
         <a double-quoted delimited-identifier> <an exponent (**)> as
         from ||


Warning: Package Body created with compilation errors.



Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros