pl/sql coding problem

[ESHA1]

 
 
CREATE OR REPLACE procedure TEST_EMAIL(errbuf  OUT   VARCHAR2,            
   retcode            OUT   VARCHAR2,
p_from_date date,
p_to_date date,
P_CANDIDATE_NAME VARCHAR2) as
   x_manager_name1     assignment.manager_name%type;
   x_EMAIL_ADDRESS     candidate.EMAIL_ADDRESS%type;               
   x_candidate_name1   assignment.candidate_name%TYPE;
   x_customer_name1    assignment.customer_name%TYPE;
   x_manager_name      assignment.manager_name%TYPE;
   x_time_sheet_id     tps_time_sheet_master.time_sheet_id%TYPE;
   x_assignment_id1    assignment.assignment_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_name, a.customer_name, a.manager_name,
               b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
               SUM (c.billable_hours),e.EMAIL_ADDRESS
          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_name = NVL (p_candidate_name, a.candidate_name)
           AND calendar_date BETWEEN p_from_date AND p_to_date
      GROUP BY a.candidate_name,
               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_id1 number)
   IS
      SELECT   a.candidate_name, a.customer_name, a.manager_name,
               b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
               SUM (c.billable_hours)
          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_id1
           AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
           AND calendar_date BETWEEN p_from_date AND p_to_date
      GROUP BY a.candidate_name,
               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_id 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.please send an email to'; 
 
v18:='Date:'||v_date; 
v15:='';
v16:='';
v14:=v9||v10||v11||chr(10)||v15||chr(10)||v16||chr(10)||v12||chr(10)||''||chr(10)||v13||chr(10)||' '||chr(10)||v18;
v3:=v1||''||v2;
   LOOP
      FETCH c_top
       INTO x_candidate_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh,x_EMAIL_ADDRESS;
x_mail:=x_EMAIL_ADDRESS;
      EXIT WHEN c_top%NOTFOUND;
begin
 open c_main( x_assignment_id1);
loop
fetch c_main 
INTO x_candidate_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
  v4 := 'Candidate  Name:'||x_candidate_name1;
  v15:= 'Customer   Name:'||x_customer_name1;
v16:=   'Manager    Name:'||x_manager_name;
v17:=x_fromdate;
v5:=rpad('AID',15)||rpad('from date',15)||rpad('to date',15)||rpad('billable hours',15);
v6:=rpad(x_assignment_id1,15)||rpad(x_fromdate,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||chr(10)||v17;*/
exit when c_main%notfound;
end loop;
 CLOSE c_main;
end;
      BEGIN
         OPEN c2 (x_assignment_id1);
         LOOP
            FETCH c2
             INTO m1, m2;
v9:=rpad(' ',15)||rpad('from 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)||''||chr(10)||v6||chr(10)||''||chr(10)||v8||chr(10)||v9||chr(10)||v10||chr(10)||v14;
            EXIT WHEN c2%NOTFOUND;
         END LOOP;
         CLOSE c2;
      END;
   END LOOP;
MAILOUT
  ('x.com', 
 'x.co.in', 
  '[e-mail]',  
  'Test Mail',  
  V7);
   CLOSE c_top;
COMMIT;
END;
/
 

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.please send an email to x.com

Corporation

Date:23-MAR-08

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

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:

 
LOOP
      FETCH c_top
       INTO x_candidate_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh,x_EMAIL_ADDRESS;
x_mail:=x_EMAIL_ADDRESS;
      EXIT WHEN c_top%NOTFOUND;
BEGIN
 OPEN c_main( x_assignment_id1);
LOOP
FETCH c_main 
INTO x_candidate_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
  v4 := 'Candidate  Name:'||x_candidate_name1;
  v15:= 'Customer   Name:'||x_customer_name1;
v16:=   'Manager    Name:'||x_manager_name;
v17:=x_fromdate;
v5:=RPAD('AID',15)||rpad('from date',15)||rpad('to date',15)||rpad('billable hours',15);
v6:=RPAD(x_assignment_id1,15)||rpad(x_fromdate,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||chr(10)  ||v17;*/
EXIT WHEN c_main%notfound;
END LOOP;
 CLOSE c_main;
END;
 
 

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:

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_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
EXIT WHEN c_main%NOTFOUND
IF(v4 IS NULL) THEN
  v4 := 'Candidate  Name:'||x_candidate_name1;
  v15:= 'Customer   Name:'||x_customer_name1;
v16:=   'Manager    Name:'||x_manager_name;
v17:=x_fromdate;
ELSE
v4:= v4||','||x_candidate_name1;
v15:= v15||','||x_customer_name1;
v16:= v16||','||x_manager_name;
v17:= v17||','||x_fromdate;
END IF
END LOOP
 
 

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:

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_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh;
EXIT WHEN c_main%NOTFOUND
IF(v4 IS NULL) THEN
  v4 := 'Candidate  Name:'||x_candidate_name1;
  v15:= 'Customer   Name:'||x_customer_name1;
v16:=   'Manager    Name:'||x_manager_name;
v17:=x_fromdate;
ELSE
v4:= v4||','||x_candidate_name1;
v15:= v15||','||x_customer_name1;
v16:= v16||','||x_manager_name;
v17:= v17||','||x_fromdate;
END IF
END LOOP
 
 

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

 
CREATE OR REPLACE procedure TEST_EMAIL(errbuf  OUT   VARCHAR2,            
   retcode            OUT   VARCHAR2,
p_from_date date,
p_to_date date
) as
   x_manager_name1     assignment.manager_name%type;
   x_EMAIL_ADDRESS     candidate.EMAIL_ADDRESS%type;               
   x_candidate_name1   assignment.candidate_name%TYPE;
   x_customer_name1    assignment.customer_name%TYPE;
   x_manager_name      assignment.manager_name%TYPE;
   x_time_sheet_id     tps_time_sheet_master.time_sheet_id%TYPE;
   x_assignment_id1    assignment.assignment_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_name, a.customer_name, a.manager_name,
               b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
               SUM (c.billable_hours),e.EMAIL_ADDRESS
          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_id1
           ---AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
           AND calendar_date BETWEEN p_from_date AND p_to_date
      GROUP BY a.candidate_name,
               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_id number)
   IS
      SELECT   a.candidate_name, a.customer_name, a.manager_name,
               b.time_sheet_id, a.assignment_id, b.from_date, b.TO_DATE,
               SUM (c.billable_hours)
          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_name = NVL (p_candidate_name, a.candidate_name)
           AND calendar_date BETWEEN p_from_date AND p_to_date
      GROUP BY a.candidate_name,
               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,please send an email to [e-mail]';
v13:='Xoriant Corporation';
v18:='Date:'||v_date; 
v16:='';
v14:=v9||v10||''||v11||chr(10)||v16||chr(10)||v12||chr(10)||''||chr(10)||v13||chr(10)||' '||chr(10)||v18;
v3:=v1||''||v2;
loop
fetch c_top 
INTO x_candidate_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh,x_EMAIL_ADDRESS   ;
fnd_file.PUT_LINE (fnd_file.output,'Candidate Name:'||x_candidate_name1);
fnd_file.PUT_LINE (fnd_file.output,'Customer Name:'||x_customer_name1);
fnd_file.PUT_LINE (fnd_file.output,'Manager Name:'||x_manager_name);
fnd_file.PUT_LINE (fnd_file.output,rpad('from date',15)||rpad('to date',15));
x_mail:=x_EMAIL_ADDRESS;
  v4 := 'Candidate Name:'||x_candidate_name1;
  v15:= 'Customer Name:'||x_customer_name1;
v16:=   'Manager Name:'||x_manager_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_assignment_id1);
loop
fetch c_main 
INTO x_candidate_name1, x_customer_name1, x_manager_name,
            x_time_sheet_id, x_assignment_id1, x_fromdate, x_todate, x_bh  ;
----x_assid := x_assid ||rpad(x_assignment_id1,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)||v17;*/
exit when c_main%notfound;
x_from :=x_fromdate;
x_to :=x_todate;
v6:=v6||rpad(x_fromdate,15)||rpad(x_todate,15)||rpad(x_bh,15)||chr(10);
fnd_file.PUT_LINE (fnd_file.output,rpad(x_fromdate,15)||rpad(x_todate,15)||rpad(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)||''||chr(10)||v16||chr(10)||''||chr(10)||v5||chr(10)||''||chr(10)||v6 ||chr(10)||''||chr(10)||v8||chr(10)||''||chr(10)||v9||chr(10)||''||chr(10)||v10||chr(10)||''||chr(10)||v14;          
end loop;
   CLOSE c_top;
COMMIT;
END;
/
 

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=203 in first and second cursor
but still it fetching 4 records for assignment_id=203

[amitpatel66]

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

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:

 
SELECT b.candidate_name, b.customer_name, b.manager_name, x.assignment_id,x.hrs FROM
(SELECT a.assignment_id,SUM (c.billable_hours) 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_id1
AND b.from_date >= p_from_date 
AND b.TO_DATE <= p_to_date
AND a.candidate_name = NVL (p_candidate_name, a.candidate_name)
GROUP BY a.assignment_id) x, assignment b
WHERE x.assignment_id = b.assignment_id
order by x.assignment_id