Hi All i am getting the below error pls help me to solve the issue
datbase erre:ORA-20220: ORA-06502
PL/SQL: nemeric or value error: number precision too large in sp_watm_per_adj ORA-06512 at sp_watm_per_adj line 375 ORA-06512 at line 1
create or replace
PROCEDURE sp_watm_per_adj
( in_per_adj_cd in per_adj.per_adj_cd%type,
in_per_dt in per_adj.per_dt%type,
in_per_adj_seqno in per_adj.per_adj_seqno%type,
in_eff_dt in per_adj.eff_dt%type,
in_old_eff_dt in per_adj.eff_dt%type,
in_adj_stat_cd in per_adj.adj_stat_cd%type,
in_jrnl_vchr_nmbr in per_adj.jrnl_vchr_nmbr%type,
in_descr in per_adj.descr%type,
in_brch_cd in per_adj.brch_cd%type,
in_prod_cls_id in per_adj.prod_cls_id%type,
in_slbl_prod_cd in per_adj.slbl_prod_cd%type,
in_sales_amt in per_adj.sales_amt%type,
in_cos_amt in per_adj.cos_amt%type,
in_local_enty_cost_amt in per_adj.local_enty_cost_amt%type,
in_trans_unit_qty in per_adj.trans_unit_qty%type,
in_parnt_per_adj_cd in per_adj.parnt_per_adj_cd%type,
in_parnt_per_dt in per_adj.parnt_per_dt%type,
in_parnt_per_adj_seqno in per_adj.parnt_per_adj_seqno%type,
in_parnt_eff_dt in per_adj.parnt_eff_dt%type,
in_mkt_id in per_adj.mkt_id%type,
in_src_type in per_adj.src_type%type,
in_src_brch_cd in per_adj.src_brch_cd%type,
in_ctry_code in per_adj.ctry_code%type,
in_trans_cd in per_adj.trans_cd%type,
in_action in varchar2,
ret_text out varchar2,
ret_code out number )
AS
/*---------------------------------------------------------------------------
Comments Generated by PVCS.
$Archive: /opt/pvcs/vmfin/vm/gr_database/sp_watm_per_adj.sqv $
$Workfile: sp_watm_per_adj.sql $ (File name)
$Author: chenbin $ (Last modified user)
$Modtime: 26 Jan 2012 13:26:40 $ (Last modified time)
$Revision: 1.9 $ (Revision number)
Owner: globalprod
---------------------------------------------------------------------------*/
business_exception exception ;
duplicate_key exception ;
parent_key_not_found exception ;
PRAGMA EXCEPTION_INIT( duplicate_key, -1 ) ;
PRAGMA EXCEPTION_INIT( parent_key_not_found, -2291 ) ;
l_per_dt date;
l_eff_dt date;
l_rltn_type salable_prod_alt_id.rltn_type_cd%type ;
l_cls_type_cd per_adj.cls_type_cd%type ;
l_cls_cd per_adj.cls_cd%type ;
l_trans_type_cd per_adj.trans_type_cd%type ;
l_trans_cd per_adj.trans_cd%type ;
l_parnt_per_adj_type_cd per_adj.parnt_per_adj_type_cd%type ;
l_next_seqno per_adj.per_adj_seqno%type;
l_sales_lc_amt per_adj.sales_lc_amt%type;
l_cos_lc_amt per_adj.cos_lc_amt%type;
l_brch_cost_lc_amt per_adj.brch_cost_lc_amt%type;
l_rate exch_rate1_view.rate%type;
/**********************************************************************************/
-- 12/02/97 Also Insert/Update the local currency when adjustment made.
/**********************************************************************************/
FUNCTION fn_valid_local_prod RETURN BOOLEAN is
temp number;
Begin
-- New column rltn_type_cd (NOT NULL) added to salable_prod_alt_id table
-- use 'PRREL' for now.
l_rltn_type := 'PRREL' ;
-- check valid local product
temp := pg_validate.fn_is_valid_local_prod( in_brch_cd, in_slbl_prod_cd, in_src_type, in_src_brch_cd, 'F');
if temp < 1 then
ret_text := 'Invalid local product.';
return FALSE;
end if;
-- check product has cost, in salable_prod_alt_id
Begin
select 1 into temp
from salable_prod_alt_id
where brch_cd = in_brch_cd
and salable_prod_cd = in_slbl_prod_cd
and src_type = in_src_type
and src_brch_cd = in_src_brch_cd
and eff_dt = l_eff_dt
and rltn_type_cd = l_rltn_type
and rownum = 1 ;
return TRUE;
Exception
When no_data_found then
ret_text := 'No cost for the local product.';
return FALSE;
When others then
raise_application_error( -20999, 'Error in check prod_alt_id' || sqlerrm || sqlcode);
return FALSE;
End;
End fn_valid_local_prod;
/*********************************************************************/
FUNCTION fn_valid_mkt_ctry RETURN BOOLEAN is
temp number;
Begin
select 1 into temp
from mkt_ctry
where mkt_ctry.mkt_id = in_mkt_id
and mkt_ctry.ctry_code = in_ctry_code
and rownum = 1 ;
return TRUE;
Exception
When no_data_found then
ret_text := 'Country does not belong to Division/Market.';
return FALSE;
When others then
raise_application_error( -20999, 'Error in check fn_valid_mkt_ctry: ' || sqlerrm || sqlcode);
return FALSE;
End fn_valid_mkt_ctry;
/*********************************************************************/
FUNCTION fn_valid_prod_family RETURN BOOLEAN is
temp number;
Begin
select 1 into temp
from class, prod_class
where prod_class.prod_cd = Ltrim(Rtrim(Substr(in_slbl_prod_cd,1,8)))
and class.class_type_cd = prod_class.class_type_cd
and class.class_cd = prod_class.class_cd
and class.prod_class_id = prod_class.prod_class_id
and class.parnt_class_id = in_prod_cls_id
and class.parnt_class_cd = 'FAMIL'
and class.parnt_class_type_cd = 'PRCLS'
and rownum = 1 ;
return TRUE;
Exception
When no_data_found then
ret_text := 'Salable product does not belong to Product Family.';
return FALSE;
When others then
raise_application_error( -20999, 'Error in check fn_valid_prod_family: ' || sqlerrm || sqlcode);
return FALSE;
End fn_valid_prod_family;
/*********************************************************************/
BEGIN
l_per_dt := Last_Day (in_per_dt);
l_eff_dt := Last_Day (in_eff_dt);
Begin
Select adj_tran_type_cd, adj_tran_cd
Into l_trans_type_cd, l_trans_cd
From adjustment_rules
Where adj_type_cd = 'PERAD'
and adj_cd = in_per_adj_cd ;
/*05/19/99 BJ: Task 6846 4.d. Skipping the rule for 3 per_adj_cd below. */
if l_trans_type_cd = 'SALES' and (in_per_adj_cd = 'DOMGL' or in_per_adj_cd = 'DOMCL'
or in_per_adj_cd = 'DOMSL' or in_per_adj_cd = 'MISC') then
l_trans_cd := in_trans_cd;
end if;
Exception
When no_data_found then
ret_text := 'No transaction type for the given adjustment type.';
raise business_exception;
When others then
ret_text := 'Error while retrieving transaction type for the given adjustment type.';
raise business_exception;
End;
if l_trans_type_cd = 'SALES' and (in_per_adj_cd = 'DOMGL' or in_per_adj_cd = 'DOMCL') then
/*BJ 10/4/99. Removed DOMSL restriction - Jean's request.*/
/*BJ 11/22/99. Added branch 262, 263. Task #8427. */
if in_brch_cd not in ('002','262','263') then
ret_text := 'Invalid Branch - Only Branch 002 allowed for this adjustment type.';
raise business_exception;
end if;
end if;
/************ Check the mandatory values *************/
if in_per_adj_cd is NULL or in_per_adj_cd = '' then
ret_text := 'Invalid adjustment type.';
raise business_exception;
end if;
if in_brch_cd is NULL or in_brch_cd = '' then
ret_text := 'Invalid branch.';
raise business_exception;
end if;
--******ALL Adjustments must have mkt/family info - Mar. 97
l_cls_type_cd := 'PRCLS';
l_cls_cd := 'FAMIL' ;
if in_mkt_id is NULL or in_mkt_id = '' then
ret_text := 'Invalid market.';
raise business_exception;
end if;
if in_prod_cls_id is NULL or in_prod_cls_id = '' then
ret_text := 'Invalid product family.';
raise business_exception;
end if;
if l_trans_type_cd = 'SALES' then -- Sales adjustment
l_trans_type_cd := 'TRTYP' ;
if in_slbl_prod_cd <> '' or in_slbl_prod_cd is not NULL
or in_src_type <> '' or in_src_type is not NULL
or in_src_brch_cd <> '' or in_src_brch_cd is not null then
if Not fn_valid_mkt_ctry then
raise business_exception;
end if;
if Not fn_valid_prod_family then
raise business_exception;
end if;
if Not fn_valid_local_prod then
raise business_exception;
end if;
end if;
elsif l_trans_type_cd = 'ITRNT' then -- Inventory adjustment
--******Salable Product is now optional - Mar. 97
if in_slbl_prod_cd <> '' or in_slbl_prod_cd is not NULL
or in_src_type <> '' or in_src_type is not NULL
or in_src_brch_cd <> '' or in_src_brch_cd is not null then
if Not fn_valid_prod_family then
raise business_exception;
end if;
if Not fn_valid_local_prod then
raise business_exception;
end if;
end if;
if in_per_adj_cd = 'PERCS' then
if in_brch_cd = '' or in_brch_cd is null
or in_slbl_prod_cd = '' or in_slbl_prod_cd is NULL
or in_src_type = '' or in_src_type is NULL
or in_src_brch_cd = '' or in_src_brch_cd is null then
ret_text := 'Branch, Salable Product, Source Type or Source Branch cannot be null for Period Adj PERCS';
raise business_exception;
end if;
end if;
end if;
if in_parnt_per_adj_cd is Not Null or in_parnt_per_adj_cd <> '' then
l_parnt_per_adj_type_cd := 'PERAD';
end if;
/**********************************************************************/
/* convert the US$ to local amt. */
/**********************************************************************/
BEGIN
SELECT RATE into l_rate
FROM EXCH_RATE1_VIEW
WHERE BRCH_CD = in_brch_cd
AND RATE_EFF_DT = in_eff_dt
AND RATE_CD = 'MTAVG';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_sales_lc_amt := null;
l_cos_lc_amt := null;
l_brch_cost_lc_amt := null;
WHEN OTHERS THEN
ret_text := 'Error when fetching exch rate: ' || sqlerrm || '.' ;
raise business_exception;
END ;
l_sales_lc_amt := in_sales_amt / l_rate ;
l_cos_lc_amt := in_cos_amt / l_rate ;
l_brch_cost_lc_amt := in_local_enty_cost_amt / l_rate;
/**********************************************************************/
if in_action = 'C' then /********* New adjustment *************/
Begin
select per_adj_seq.nextval into l_next_seqno
from dual;
INSERT into per_adj
( per_adj_type_cd,
per_adj_cd,
per_dt,
per_adj_seqno,
eff_dt,
adj_stat_type_cd,
adj_stat_cd,
jrnl_vchr_nmbr,
descr,
brch_cd,
cls_type_cd,
cls_cd,
prod_cls_id,
slbl_prod_cd,
trans_type_cd,
trans_cd,
sales_amt,
cos_amt,
local_enty_cost_amt,
trans_unit_qty,
parnt_per_adj_type_cd,
parnt_per_adj_cd,
parnt_per_dt,
parnt_per_adj_seqno,
parnt_eff_dt,
mkt_id,
sales_lc_amt,
cos_lc_amt,
brch_cost_lc_amt,
src_type,
src_brch_cd,
ctry_code
)
values
( 'PERAD',
in_per_adj_cd,
l_per_dt,
l_next_seqno,
l_eff_dt,
'PASTS',
in_adj_stat_cd,
in_jrnl_vchr_nmbr,
in_descr,
in_brch_cd,
l_cls_type_cd,
l_cls_cd,
in_prod_cls_id,
in_slbl_prod_cd,
l_trans_type_cd,
l_trans_cd,
in_sales_amt,
in_cos_amt,
in_local_enty_cost_amt,
in_trans_unit_qty,
l_parnt_per_adj_type_cd,
in_parnt_per_adj_cd,
in_parnt_per_dt,
in_parnt_per_adj_seqno,
in_parnt_eff_dt,
in_mkt_id,
l_sales_lc_amt,
l_cos_lc_amt,
l_brch_cost_lc_amt,
in_src_type,
in_src_brch_cd,
in_ctry_code
);
end;
elsif in_action = 'E' then /**** Update an existing adjustment *******/
UPDATE per_adj SET
per_adj_cd = in_per_adj_cd,
trans_type_cd = l_trans_type_cd,
trans_cd = l_trans_cd,
eff_dt = l_eff_dt,
adj_stat_cd = in_adj_stat_cd,
jrnl_vchr_nmbr = in_jrnl_vchr_nmbr,
descr = in_descr,
brch_cd = in_brch_cd,
prod_cls_id = in_prod_cls_id,
slbl_prod_cd = in_slbl_prod_cd,
sales_amt = in_sales_amt,
cos_amt = in_cos_amt,
local_enty_cost_amt = in_local_enty_cost_amt,
trans_unit_qty = in_trans_unit_qty,
parnt_per_adj_type_cd = l_parnt_per_adj_type_cd,
parnt_per_adj_cd = in_parnt_per_adj_cd,
parnt_per_dt = in_parnt_per_dt,
parnt_per_adj_seqno = in_parnt_per_adj_seqno,
parnt_eff_dt = in_parnt_eff_dt,
mkt_id = in_mkt_id,
sales_lc_amt = l_sales_lc_amt,
cos_lc_amt = l_cos_lc_amt,
brch_cost_lc_amt = l_brch_cost_lc_amt,
src_type = in_src_type,
src_brch_cd = in_src_brch_cd,
ctry_code = in_ctry_code,
updt_user_id = user,
updt_dt_tm = sysdate
WHERE per_adj_type_cd = 'PERAD'
and per_dt = in_per_dt
and per_adj_seqno= in_per_adj_seqno ;
else
ret_text := 'Invalid option in the called Stored Procedure sp_watm_per_adj.';
raise business_exception;
end if;
ret_code := 1 ;
ret_text := to_char(l_next_seqno, '9999999');
exception
when business_exception then
ret_code := -1 ;
when duplicate_key THEN
ret_code := -1 ;
ret_text := 'Adjustment already exists for this month.' ;
when parent_key_not_found then
ret_code := -1 ;
ret_text := 'Field referenced not found.' ;
when no_data_found then
ret_code := 0 ;
ret_text := 'Record to be updated missing.' ;
when others then
ret_code := -2 ;
ret_text := 'Database error ' || sqlerrm || '.';
raise_application_error(-20220,sqlerrm|| ' in sp_watm_per_adj' );
end;