The stored proc for the most part seems to work however when it get a certain p_emp value I get then error 6502 ORA-06502: PL/SQL: numeric or value error. The p_emp does exist so there are no problems there all the data I am pulling is varchar2. I only have one number declared which is used for a counter. How ever for certain people the system just dies I have if I run the Select statemnets manually they work and give the full results. When I excute the Proc the same emp will cause it to fail.
PROCEDURE Emp_services_Equip_Email(P
_emp varchar2, P_FM varchar2) IS
vitem varchar2(1000);
vprimarynumber varchar2(1000);
vserialnumber varchar2(1000);
vemail varchar2(1000);
vfullname varchar2(1000);
vsubject varchar2(1000);
vBody varchar2(4000);
vempno varchar2(1000);
vorgid varchar2(1000);
num_recs number;
qty varchar2(1000);
amt varchar2(1000);
x varchar2(1000);
y varchar2(1000);
BEGIN
vSubject := '';
vBody := '';
Num_recs := 0;
if p_emp is not null then
Declare
Cursor A1 is select I.org_id, I.inventory_id,I.primary_n
umber,I.it
em_type,fy
_mth,U.QTY
_CHAR
from inventory I, (select u2.inventory_id, u2.fy_mth, u2.qty_char from usage_summary4 u2 where fy_mth = P_FM and employee_id = P_emp) U
where assigned_to = P_emp
and I.inventory_id = u.inventory_id (+)
order by item_type;
A1_tmp A1%rowtype;
begin
Num_recs := 1; -- Only sending out one email
select full_name, email into vfullname,vemail from employees where employee_id = p_emp and email is not null;
vsubject := vfullname || ' ' || p_emp || ' Assigned Services and Equipment';
vBody := 'HI ' || vfullname || ' (Oracle Number)' || chr(13) || chr(10) || chr(13) || chr(10) ||
'Below is a list if Items assigned to you.' || chr(13) || chr(10) || chr(13) || chr(10) ||
'If you have any questions please contact the Help Desk at Ext: 5600' || chr(13) || chr(10) || chr(13) || chr(10);
z := 'A2';
vBody := vBody || 'Admin Item Identifier Fy Mth Qty' || chr(13) || chr(10) || chr(13) || chr(10);
for A1_tmp in A1 loop
vBody := VBody || RPAD(a1_tmp.org_id,15) || RPAD(a1_tmp.item_type,25) || RPAD(a1_tmp.primary_number
,30); --|| RPAD(a1_tmp.fy_mth,12) || RPAD(a1_tmp.qty_char,10) ||chr(13);
end loop;
end;
-- SEND_MAIL.mail('test@tes.c
om',vemail
,vsubject,
vBody);
SEND_MAIL.mail('test@tes.c
om,'test@t
es.com',vs
ubject, vBody);
else
--Send to all employees
Declare
Cursor B1 is Select employee_id from employees where email is not null and employee_id <> '11084' order by employee_id;
B1_tmp B1%rowtype;
begin
for B1_tmp in B1 loop
--reset variables for next email
vSubject := '';
vBody := '';
Num_recs := Num_recs + 1;
Declare
Cursor C1 is select I.org_id, I.inventory_id,I.primary_n
umber,I.it
em_type,fy
_mth,U.QTY
_CHAR
from inventory I, (select u2.inventory_id, u2.fy_mth, u2.qty_char from usage_summary4 u2 where fy_mth = P_FM) U
where assigned_to = B1_tmp.employee_id
and I.inventory_id = u.inventory_id (+)
order by item_type;
C1_tmp C1%rowtype;
begin
y := B1_tmp.employee_id;
select full_name, email into vfullname,vemail from employees where employee_id = B1_tmp.employee_id;
vsubject := vfullname || ' ' || B1_tmp.employee_id || ' Assigned Services and Equipment';
vBody := 'HI ' || vfullname || ' (Oracle Number ' || B1_tmp.employee_id || ')' || chr(13) || chr(10) || chr(13) || chr(10) ||
'Below is a list if Items assigned to you.' || chr(13) || chr(10) || chr(13) || chr(10) ||
'If you have any questions please contact the Help Desk at Ext: 5600' || chr(13) || chr(10) || chr(13) || chr(10);
vBody := vBody || 'Admin1 Item Identifier Fy Mth Qty' || chr(13) || chr(10) || chr(13) || chr(10);
for C1_tmp in C1 loop
vBody := VBody || RPAD(c1_tmp.org_id,15) || RPAD(c1_tmp.item_type,25) || RPAD(c1_tmp.primary_number
,30) || RPAD(c1_tmp.fy_mth,12) || RPAD(c1_tmp.qty_char,10) ||chr(13);
end loop;
-- SEND_MAIL.mail('test@tes.c
om',vemail
,vsubject,
vBody);
SEND_MAIL.mail('test@tes.c
om','test@
tes.com',v
subject, vBody);
end;
end loop;
null;
end;
end if;
-- BG_LOG_ENTRY (31, 'OK', '', to_char(Num_recs) || ' email(s) were sent.' , '', '', '', '');
exception
when others then
x := Num_recs || ' ' || vfullname || ' ' || ' ' || y || ' ' || SQLCODE || ' ' || SQLERRM;
insert into temp_email (att1) values(x);
commit;
END;
Start Free Trial
