pl/sql coding problem

[ESHA1]

[code=oracle]


CREATE OR REPLACE procedure TEST_EMAIL(errb uf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_from_date date,
p_to_date date,
P_CANDIDATE_NAM E VARCHAR2) as
x_manager_name1 assignment.mana ger_name%type;
x_EMAIL_ADDRESS candidate.EMAIL _ADDRESS%type;
x_candidate_nam e1 assignment.cand idate_name%TYPE ;
x_customer_name 1 assignment.cust omer_name%TYPE;
x_manager_name assignment.mana ger_name%TYPE;
x_time_sheet_id tps_time_sheet_ master.time_she et_id%TYPE;
x_assignment_id 1 assignment.assi gnment_id%TYPE;
x_fromdate DATE;
x_todate DATE;
x_cdate date;
x_bh NUMBER;
m1 date;
m2 date;
v_date date;
V1 VARCHAR2(20000) ;
V2 VARCHAR2(20000) ;
V3 VARCHAR2(20000) ;
v4 VARCHAR2(20000) ;
v5 VARCHAR2(20000) ;
v6 varchar2(20000) ;
v7 varchar2(20000) ;
v8 varchar2(20000) ;
v9 varchar2(20000) ;
v10 varchar2(20000) ;
x_mail varchar2(20000) ;
v11 varchar2(20000) ;
v12 varchar2(20000) ;
v13 varchar2(20000) ;
v14 varchar2(20000) ;
v15 varchar2(20000) ;
v16 varchar2(20000) ;
v17 varchar2(20000) ;
v18 varchar2(20000) ;
cursor c_top is
sELECT a.candidate_nam e, a.customer_name , a.manager_name,
b.time_sheet_id , a.assignment_id , b.from_date, b.TO_DATE,
SUM (c.billable_hou rs),e.EMAIL_ADD RESS
FROM assignment a, tps_time_sheet_ master b,
tps_time_sheet_ detail c,
candidate E
WHERE a.assignment_id = b.assignment_id
AND b.time_sheet_id = c.time_sheet_id
and a.candidate_id= E.candidate_id
--AND a.assignment_id = p_assignment_id
AND a.candidate_nam e = NVL (p_candidate_na me, a.candidate_nam e)
AND calendar_date BETWEEN p_from_date AND p_to_date
GROUP BY a.candidate_nam e,
a.customer_name ,
a.manager_name,
b.time_sheet_id ,
b.from_date,
b.TO_DATE,
a.assignment_id ,
e.EMAIL_ADDRESS
ORDER BY assignment_id;
CURSOR c_main(p_assign ment_id1 number)
IS
SELECT a.candidate_nam e, a.customer_name , a.manager_name,
b.time_sheet_id , a.assignment_id , b.from_date, b.TO_DATE,
SUM (c.billable_hou rs)
FROM assignment a, tps_time_sheet_ master b,
tps_time_sheet_ detail c
WHERE a.assignment_id = b.assignment_id
AND b.time_sheet_id = c.time_sheet_id
----and a.candidate_id= E.candidate_id
AND a.assignment_id = p_assignment_id 1
AND a.candidate_nam e = NVL (p_candidate_na me, a.candidate_nam e)
AND calendar_date BETWEEN p_from_date AND p_to_date
GROUP BY a.candidate_nam e,
a.customer_name ,
a.manager_name,
b.time_sheet_id ,
b.from_date,
b.TO_DATE,
a.assignment_id
ORDER BY assignment_id;
CURSOR c2 (p_assignment_i d NUMBER)
IS
SELECT MAX (b.mdate), MIN (b.mdate)
FROM (SELECT mdate
FROM xxmissing_time_ date
WHERE mdate BETWEEN p_from_date AND p_to_date
AND aid = p_assignment_id ) b;
x_rec c_main%rowtype;
BEGIN
select sysdate into v_date from dual;
OPEN c_top;
v1:='During last week your time sheet(s) has (have) been received and entered into the system ';
v2:='as follows:';
v8:='Please note that we not received time sheet(s) for periods mention below:';
v9:='Also note that payroll is processed every alternate Tuesday.Please ensure that your time';
v10:='Sheet reaches us at least 24 hours prior to payroll processing in order to processing in order';
v11:='to process an accurate and timely payroll.';
v12:='In case of any discrepancy/clarification.p lease send an email to';

v18:='Date:'||v _date;
v15:='';
v16:='';
v14:=v9||v10||v 11||chr(10)||v1 5||chr(10)||v16 ||chr(10)||v12| |chr(10)||''||c hr(10)||v13||ch r(10)||' '||chr(10)||v18 ;
v3:=v1||''||v2;
LOOP
FETCH c_top
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh,x_EMAIL_AD DRESS;
x_mail:=x_EMAIL _ADDRESS;
EXIT WHEN c_top%NOTFOUND;
begin
open c_main( x_assignment_id 1);
loop
fetch c_main
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh;
v4 := 'Candidate Name:'||x_candi date_name1;
v15:= 'Customer Name:'||x_custo mer_name1;
v16:= 'Manager Name:'||x_manag er_name;
v17:=x_fromdate ;
v5:=rpad('AID', 15)||rpad('from date',15)||rpad ('to date',15)||rpad ('billable hours',15);
v6:=rpad(x_assi gnment_id1,15)| |rpad(x_fromdat e,15)||rpad(x_t odate,15)||rpad (x_bh,15);
/*v7:=v3||chr(10 )||'||chr(10)|| v4||chr(10)||'' ||chr(10)||v15| |chr(10)||' '||chr(10)||v16 ||chr(10)||''|| chr(10)||v5||ch r(10)||v17;*/
exit when c_main%notfound ;
end loop;
CLOSE c_main;
end;
BEGIN
OPEN c2 (x_assignment_i d1);
LOOP
FETCH c2
INTO m1, m2;
v9:=rpad(' ',15)||rpad('fr om date',15)||rpad ('to date',15);
v10:=rpad(' ',15)||rpad(m2, 15)||rpad(m1,15 );
v7:=v3||chr(10) ||''||chr(10)|| v4||chr(10)||'' ||chr(10)||v15| |chr(10)||' '||chr(10)||v16 ||chr(10)||''|| chr(10)||''||v5 ||chr(10)||''|| chr(10)||''||ch r(10)||v6||chr( 10)||''||chr(10 )||v8||chr(10)| |v9||chr(10)||v 10||chr(10)||v1 4;
EXIT WHEN c2%NOTFOUND;
END LOOP;
CLOSE c2;
END;
END LOOP;
MAILOUT
('x.com',
'x.co.in',
'x@gmail.com',
'Test Mail',
V7);
CLOSE c_top;
COMMIT;
END;
/
[/code]

I am using this pl/sql code to send email.BY using this code mail goes to particular person.but c_main cursor not able to fetch more than one record.and
in databse there is more than one record.
the output like this
During last week your time sheet(s) has (have) been received and entered into the
system as follows:
Candidate Name:
Customer Name:Citigroup Global Markets Inc.
Manager Name:
AID from date to date billable hours
203 07-JAN-08 13-JAN-08 40


Please note that we not received time sheet(s) for periods mention below:
from date to date

Also note that payroll is processed every alternate Tuesday.Please ensure that your
timeSheet reaches us at least 24 hours prior to payroll processing in order to
processing in orderto process an accurate and timely payroll.


In case of any discrepancy/clarification.p lease send an email to x.com

Corporation

Date:23-MAR-08

i am not able to fetch more than one aid,fromdate,to date

so can u suggest me how to modified this query.

[amitpatel66]

1. The very first thing you need to look at is reducing the usage of so many variables. You can insted use just one variable of type varchar and store the whole body message in to it rather using lots of variables which makes the code look messy.

2. I think your third cursor c2 takes the MAX and MIN of From and To Date, so in that case your cursor will hold only one value in it. So even you have more than one record in your table, it will take the MAX or MIN of it.

[ESHA1]

1. The very first thing you need to look at is reducing the usage of so many variables. You can insted use just one variable of type varchar and store the whole body message in to it rather using lots of variables which makes the code look messy.

2. I think your third cursor c2 takes the MAX and MIN of From and To Date, so in that case your cursor will hold only one value in it. So even you have more than one record in your table, it will take the MAX or MIN of it.

hi
i am having a problem with c_main cursor.
the script giving me right result
when i am using dbms_output.put _line

[amitpatel66]

hi
i am having a problem with c_main cursor.
the script giving me right result
when i am using dbms_output.put _line

If you see the below code properly:

[code=oracle]

LOOP
FETCH c_top
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh,x_EMAIL_AD DRESS;
x_mail:=x_EMAIL _ADDRESS;
EXIT WHEN c_top%NOTFOUND;
BEGIN
OPEN c_main( x_assignment_id 1);
LOOP
FETCH c_main
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh;
v4 := 'Candidate Name:'||x_candi date_name1;
v15:= 'Customer Name:'||x_custo mer_name1;
v16:= 'Manager Name:'||x_manag er_name;
v17:=x_fromdate ;
v5:=RPAD('AID', 15)||rpad('from date',15)||rpad ('to date',15)||rpad ('billable hours',15);
v6:=RPAD(x_assi gnment_id1,15)| |rpad(x_fromdat e,15) ||rpad(x_todate ,15)||rpad(x_bh ,15);
/*v7:=v3||chr(10 )||'||chr(10)|| v4||chr(10)||'' ||chr (10)||v15||chr( 10)||' '||chr(10)||v16 ||chr(10)||''|| chr(10)||v5||ch r(10) ||v17;*/
EXIT WHEN c_main%notfound ;
END LOOP;
CLOSE c_main;
END;

[/code]

The value for the variables v15, v4,v16 gets updated everytime for each value of the loop. So the last record will be stored in these variables. Finally you do an END LOOP and come out of the loop. So finally the variables will store the last record values in it.

[ESHA1]

hi
so plz can u suggest me what to do.because i have to fetch more than one record if available.
waiting for u r suggestion

[amitpatel66]

hi
so plz can u suggest me what to do.because i have to fetch more than one record if available.
waiting for u r suggestion

If you want all the values to be stored in a single variable, then try something like this:

[code=oracle]
declare
v4 varchar2(200) := NULL;
v15 varchar2(200) := NULL;
v16 varchar2(200) := NULL;
v17 varchar2(200) := NULL;
BEGIN
OPEN c_main
LOOP
FETCH c_main
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh;
EXIT WHEN c_main%NOTFOUND
IF(v4 IS NULL) THEN
v4 := 'Candidate Name:'||x_candi date_name1;
v15:= 'Customer Name:'||x_custo mer_name1;
v16:= 'Manager Name:'||x_manag er_name;
v17:=x_fromdate ;
ELSE
v4:= v4||','||x_cand idate_name1;
v15:= v15||','||x_cus tomer_name1;
v16:= v16||','||x_man ager_name;
v17:= v17||','||x_fro mdate;
END IF
END LOOP

[/code]

The above code will concatenate the existing value with the new value in the same variable, This way you will not loose any value

[ESHA1]

If you want all the values to be stored in a single variable, then try something like this:

[code=oracle]
declare
v4 varchar2(200) := NULL;
v15 varchar2(200) := NULL;
v16 varchar2(200) := NULL;
v17 varchar2(200) := NULL;
BEGIN
OPEN c_main
LOOP
FETCH c_main
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh;
EXIT WHEN c_main%NOTFOUND
IF(v4 IS NULL) THEN
v4 := 'Candidate Name:'||x_candi date_name1;
v15:= 'Customer Name:'||x_custo mer_name1;
v16:= 'Manager Name:'||x_manag er_name;
v17:=x_fromdate ;
ELSE
v4:= v4||','||x_cand idate_name1;
v15:= v15||','||x_cus tomer_name1;
v16:= v16||','||x_man ager_name;
v17:= v17||','||x_fro mdate;
END IF
END LOOP

[/code]

The above code will concatenate the existing value with the new value in the same variable, This way you will not loose any value

Hi
Thanks for your valuable suggestion its now giving me desire output.

[amitpatel66]

Hi
Thanks for your valuable suggestion its now giving me desire output.

You are Welcome. Do post back in case of any other issues.

MODERATOR

[ESHA1]

You are Welcome. Do post back in case of any other issues.

MODERATOR

Hi

by running this procedure output is repeating two times like this

Candidate Name:Yogesh Belsare
Customer Name:American Radiologist Network Inc.
Manager Name:Neil Iyer
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40
Candidate Name:Yogesh Belsare
Customer Name:American Radiologist Network Inc.
Manager Name:Neil Iyer
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40


Candidate Name:Vishal Deo
Customer Name:American Radiologist Network Inc.
Manager Name:
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40
Candidate Name:Vishal Deo
Customer Name:American Radiologist Network Inc.
Manager Name:
from date to date
01-MAR-08 06-MAR-08 32
07-MAR-08 13-MAR-08 40

i need only one record should display.

the pl/sql procedure like this
[code=oracle]

CREATE OR REPLACE procedure TEST_EMAIL(errb uf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_from_date date,
p_to_date date
) as
x_manager_name1 assignment.mana ger_name%type;
x_EMAIL_ADDRESS candidate.EMAIL _ADDRESS%type;
x_candidate_nam e1 assignment.cand idate_name%TYPE ;
x_customer_name 1 assignment.cust omer_name%TYPE;
x_manager_name assignment.mana ger_name%TYPE;
x_time_sheet_id tps_time_sheet_ master.time_she et_id%TYPE;
x_assignment_id 1 assignment.assi gnment_id%TYPE;
x_fromdate DATE;
x_todate DATE;
x_cdate date;
x_bh NUMBER;
m1 date;
m2 date;
v_date date;
V1 VARCHAR2(20000) ;
V2 VARCHAR2(20000) ;
V3 VARCHAR2(20000) ;
v4 VARCHAR2(20000) ;
v5 VARCHAR2(20000) ;
v6 varchar2(20000) ;
v7 varchar2(20000) ;
v8 varchar2(20000) ;
v9 varchar2(20000) ;
v10 varchar2(20000) ;
x_mail varchar2(20000) ;
v11 varchar2(20000) ;
v12 varchar2(20000) ;
v13 varchar2(20000) ;
v14 varchar2(20000) ;
v15 varchar2(20000) ;
v16 varchar2(20000) ;
v17 varchar2(20000) ;
v18 varchar2(20000) ;
x_assid number;
x_from varchar2(2000);
x_to varchar2(2000);
x_bilh number;
i NUMBER := 1;
CURSOR c_top
IS
SELECT a.candidate_nam e, a.customer_name , a.manager_name,
b.time_sheet_id , a.assignment_id , b.from_date, b.TO_DATE,
SUM (c.billable_hou rs),e.EMAIL_ADD RESS
FROM assignment a, tps_time_sheet_ master b,
tps_time_sheet_ detail c,
candidate E
WHERE a.assignment_id = b.assignment_id
AND b.time_sheet_id = c.time_sheet_id
and a.candidate_id= E.candidate_id
---and c.BILLING_FLAG= 'N'
----and a.candidate_id= E.candidate_id
---AND a.assignment_id = p_assignment_id 1
---AND a.candidate_nam e = NVL (p_candidate_na me, a.candidate_nam e)
AND calendar_date BETWEEN p_from_date AND p_to_date
GROUP BY a.candidate_nam e,
a.customer_name ,
a.manager_name,
b.time_sheet_id ,
b.from_date,
b.TO_DATE,
a.assignment_id ,
e.EMAIL_ADDRESS
ORDER BY assignment_id;
CURSOR c_main (p_assignment_i d number)
IS
SELECT a.candidate_nam e, a.customer_name , a.manager_name,
b.time_sheet_id , a.assignment_id , b.from_date, b.TO_DATE,
SUM (c.billable_hou rs)
FROM assignment a, tps_time_sheet_ master b,
tps_time_sheet_ detail c
WHERE a.assignment_id = b.assignment_id
AND b.time_sheet_id = c.time_sheet_id
---and c.BILLING_FLAG= 'N'
----and a.candidate_id= E.candidate_id
AND a.assignment_id = p_assignment_id
----AND a.candidate_nam e = NVL (p_candidate_na me, a.candidate_nam e)
AND calendar_date BETWEEN p_from_date AND p_to_date
GROUP BY a.candidate_nam e,
a.customer_name ,
a.manager_name,
b.time_sheet_id ,
b.from_date,
b.TO_DATE,
a.assignment_id
ORDER BY assignment_id;
begin
select sysdate into v_date from dual;
open c_top;
v1:='During last week, your time sheet(s) has (have) been received and entered into the system ';
v2:='as follows:';
v8:='Please note that we have not received time sheet(s) for period(s) mentioned below:';
v10:=' Xoriant employees should note that payroll is processed every alternate Tuesday. Please ensure that your time sheet reaches';
v11:= ' us at least 24 hours prior to payroll processing in order to process an accurate and timely payroll.';
v9:='If you are on vacation, please submit time sheet(s) with zero hours, approved by your manager.';
v12:='In case of any discrepancy/clarification,p lease send an email to etimesheet@xori ant.com';
v13:='Xoriant Corporation';
v18:='Date:'||v _date;
v16:='';
v14:=v9||v10||' '||v11||chr(10) ||v16||chr(10)| |v12||chr(10)|| ''||chr(10)||v1 3||chr(10)||' '||chr(10)||v18 ;
v3:=v1||''||v2;
loop
fetch c_top
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh,x_EMAIL_AD DRESS ;
fnd_file.PUT_LI NE (fnd_file.outpu t,'Candidate Name:'||x_candi date_name1);
fnd_file.PUT_LI NE (fnd_file.outpu t,'Customer Name:'||x_custo mer_name1);
fnd_file.PUT_LI NE (fnd_file.outpu t,'Manager Name:'||x_manag er_name);
fnd_file.PUT_LI NE (fnd_file.outpu t,rpad('from date',15)||rpad ('to date',15));
x_mail:=x_EMAIL _ADDRESS;
v4 := 'Candidate Name:'||x_candi date_name1;
v15:= 'Customer Name:'||x_custo mer_name1;
v16:= 'Manager Name:'||x_manag er_name;
v5:=rpad('From Date',15)||rpad ('To Date',15)||rpad ('Billable Hours',15);
select sysdate into v_date from dual;
begin
open c_main(x_assign ment_id1);
loop
fetch c_main
INTO x_candidate_nam e1, x_customer_name 1, x_manager_name,
x_time_sheet_id , x_assignment_id 1, x_fromdate, x_todate, x_bh ;
----x_assid := x_assid ||rpad(x_assign ment_id1,15);
/*v7:=v3||chr(10 )||'||chr(10)|| v4||chr(10)||'' ||chr(10)||v15| |chr(10)||' '||chr(10)||v16 ||chr(10)||''|| chr(10)||v5||ch r(10)||v17;*/
exit when c_main%notfound ;
x_from :=x_fromdate;
x_to :=x_todate;
v6:=v6||rpad(x_ fromdate,15)||r pad(x_todate,15 )||rpad(x_bh,15 )||chr(10);
fnd_file.PUT_LI NE (fnd_file.outpu t,rpad(x_fromda te,15)||rpad(x_ todate,15)||rpa d(x_bh,15));
EXIT WHEN c_main%NOTFOUND ;
END LOOP;
CLOSE c_main;
end;
exit when c_top%notfound;
v7:=v3||chr(10) ||''||chr(10)|| v4||chr(10)||'' ||chr(10)||v15| |chr(10)||''||c hr(10)||v16||ch r(10)||''||chr( 10)||v5||chr(10 )||''||chr(10)| |v6 ||chr(10)||''|| chr(10)||v8||ch r(10)||''||chr( 10)||v9||chr(10 )||''||chr(10)| |v10||chr(10)|| ''||chr(10)||v1 4;
end loop;
CLOSE c_top;
COMMIT;
END;
/
[/code]

by this procedure two times data is comming.
so plz help me how to avoid this


Thanks and Regards
Esha

[amitpatel66]

Hi,

Could you please try executing the queries of the CURSORS in TOAD / SQLPLUS and check which cursor is returining two records.
Run query by hard coding the values that you have as a sample output for which it is repeating.

[ESHA1]

Hi,

Could you please try executing the queries of the CURSORS in TOAD / SQLPLUS and check which cursor is returining two records.
Run query by hard coding the values that you have as a sample output for which it is repeating.

Hi,
i put assignment_id=2 03 in first and second cursor
but still it fetching 4 records for assignment_id=2 03

[amitpatel66]

Hi,
i put assignment_id=2 03 in first and second cursor
but still it fetching 4 records for assignment_id=2 03

So there are more than one record for a single assignment id is it?
So, you need to make your cursor query to fetch one record so that it does not repeat.

[ESHA1]

So there are more than one record for a single assignment id is it?
So, you need to make your cursor query to fetch one record so that it does not repeat.

Hi
actualy for every candidate there is a assignment_id and that is unique
for every week we enter a time sheet for that paricular candidate
say from 1-mar-08 to 6-mar-08 then 7-mar-08 to 13-mar-08 etc
.when we do data entery for every week, candidate name and assignment id will same only time sheet id will vary.
so i have to display data in such a way that when we choose from date and to date (for example 1-march-08 to 13-march-08) the output should like this

candidate name:xxxx
customer name:yyyy
from date to date billable hours
1-mar-08 6-mar-08 40
7-mar-08 13-mar-08 32


for all candidate

[ESHA1]

So there are more than one record for a single assignment id is it?
So, you need to make your cursor query to fetch one record so that it does not repeat.

Hi
actualy for every candidate there is a assignment_id and that is unique
for every week we enter a time sheet for that paricular candidate
say from 1-mar-08 to 6-mar-08 then 7-mar-08 to 13-mar-08 etc
.when we do data entery for every week, candidate name and assignment id will same only time sheet id will vary.
so i have to display data in such a way that when we choose from date and to date (for example 1-march-08 to 13-march-08) the output should like this

candidate name:xxxx
customer name:yyyy
from date to date billable hours
1-mar-08 6-mar-08 40
7-mar-08 13-mar-08 32


for all candidate

[amitpatel66]

Try this query:
[code=oracle]

SELECT b.candidate_nam e, b.customer_name , b.manager_name, x.assignment_id ,x.hrs FROM
(SELECT a.assignment_id ,SUM (c.billable_hou rs) hrs
FROM assignment a, tps_time_sheet_ master b,
tps_time_sheet_ detail c
WHERE a.assignment_id = b.assignment_id
AND b.time_sheet_id = c.time_sheet_id
AND a.assignment_id = p_assignment_id 1
AND b.from_date >= p_from_date
AND b.TO_DATE <= p_to_date
AND a.candidate_nam e = NVL (p_candidate_na me, a.candidate_nam e)
GROUP BY a.assignment_id ) x, assignment b
WHERE x.assignment_id = b.assignment_id
order by x.assignment_id
[/code]