Hi
When I am executing my function its giving error.. first time its
running properly.. but when I am executing second time I am getting
error
create or replace function irla_punching_medium_insert1(in_proj_code
numeric,in_pt_code varchar,in_mon_abs date) returns varchar as'
declare
l_p varchar;
credit_amount numeric;
debit_amount numeric;
l_vr_no varchar;
l_count integer;
begin
select irla_code into l_p from npo_project_irla_code where
npo_proj_code=$1 and pt_code=$2;
create table dummy_table without oids as
select side,irla_code,substr(type_charge,1,1)||substr(type_charge,2,1)
|| substr(type_charge,4,1) ||substr(type_charge,5,1) as
type_charge,npo_code from irla_npo_pm_codes_master
where type_charge=''(+-)CR'' or type_charge=''(+-)RC''
union all
select
side,irla_code,substr(type_charge,1,1)||substr(type_charge,3,1)||substr(
type_charge,4,1) || substr(type_charge,6,1) as type_charge,npo_code from
irla_npo_pm_codes_master
where type_charge=''(+-)CR'' or type_charge=''(+-)RC''
union all select side,irla_code,type_charge,npo_code from
irla_npo_pm_codes_master where type_charge=''(+)C'' or
type_charge=''(+)R'' ;
update dummy_table set irla_code=l_p where irla_code=''P'';
create table amount_table without oids as select
dt.side,dt.irla_code,sum(npoaa.amount) as amount,dt.type_charge from
npo_abs_amount npoaa,dummy_table dt where dt.side=npoaa.side and
dt.npo_code=npoaa.npo_code
and extract(month from to_date(npoaa.mon_abs,''yyyy-mm-dd''))||''/''||
extract(year from to_date(npoaa.mon_abs,''yyyy-mm-dd''))
=extract(month from to_date($1,''yyyy-mm-dd''))||''/''|| extract(year
from to_date($1,''yyyy-mm-dd'')) and npoaa.proj_code=$1 and
npoaa.pt_code= $2 group by dt.irla_code ,dt.type_charge,dt.side ;
drop table dummy_table;
select cast(substr(max(vr_no),1,2) || cast(substr(max(vr_no),3,1) as
integer)+1 as varchar) into l_vr_no from irla_punchingmedium_top_amt ;
select sum(amount) into credit_amount from amount_table where
side=''C'';
select sum(amount) into debit_amount from amount_table where side=''D'';
insert into irla_punchingmedium_top_amt
(vr_no,credit_tot_amt,class_of_vr,mon_abs,proj_code,pt_code,debit_tot_am
t)values
(l_vr_no,credit_amount,1,$3,$1,$2,debit_amount);
insert into irla_punchingmedium_amt
(amount,codehead,type_of_charge,vr_no) select
amount,irla_code,type_charge,(select l_vr_no) from amount_table;
raise notice''%'', l_vr_no;
drop table amount_table;
raise notice ''%2'', l_count;
return ''inserted'';
end;
'language'plpgsql';
ERROR: relation with OID 75275 does not exist
CONTEXT: SQL statement "update dummy_table set irla_code= $1 where
irla_code='P'"
PL/pgSQL function "irla_punching_medium_insert1" line 19 at SQL
statement
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times...Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES | BPO
Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities |
Retail & Distribution | Government
Tel +91-80-5193-0000(Ext:503)| Fax +91-80-51930009 | Cell No
+91-9980012376|www.infics.com
Information transmitted by this e-mail is proprietary to Infinite
Computer Solutions and/ or its Customers and is intended for use only by
the individual or entity to which it is addressed, and may contain
information that is privileged, confidential or exempt from disclosure
under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper
authority, you are notified that any use or dissemination of this
information in any manner is strictly prohibited. In such cases, please
notify us immediately at [EMAIL PROTECTED] and delete this mail from
your records.
Information transmitted by this e-mail is proprietary to Infinite Computer
Solutions and / or its Customers and is intended for use only by the individual
or the entity to which it is addressed, and may contain information that is
privileged, confidential or exempt from disclosure under applicable law. If you
are not the intended recipient or it appears that this mail has been forwarded
to you without proper authority, you are notified that any use or dissemination
of this information in any manner is strictly prohibited. In such cases, please
notify us immediately at [EMAIL PROTECTED] and delete this email from your
records.
