APEX

HI Experts,

Since yesterday every thing was fine, i do not know what happened in the evening, we are facing some issue with our production.

I'm getting the following error ,when i running the report

ORA-00917: missing comma
Error ERR-1101 Unable to process function body returning query.


Since long time we have even not touched the code , i'm wondering about this error...the same code was working fine just 2 days back.



Kindly help me with this...


Code

DECLARE
   date_from DATE:=:P46_TRANS_DATEFROM;
   date_to DATE:=:P46_TRANS_DATETO;
   --date_from DATE :=TO_DATE('22-Nov-2007','DD-Mon-yyyy');
   --date_to DATE :=TO_DATE('23-Nov-2007','DD-Mon-yyyy');
   date_counter DATE:=date_from;

   v_city varchar2(32):=:P46_CITY;
   str_month1 VARCHAR2(3):=SUBSTR(TO_CHAR(date_from,'DD-MON-YYYY'),4,3);
   str_year1 VARCHAR2(4):=SUBSTR(TO_CHAR(date_from,'DD-MON-YYYY'),8,4);
   str_month2 VARCHAR2(3):=SUBSTR(TO_CHAR(date_to,'DD-MON-YYYY'),4,3);
   str_year2 VARCHAR2(4):=SUBSTR(TO_CHAR(date_to,'DD-MON-YYYY'),8,4);
   day_from VARCHAR2(2):=SUBSTR(date_from,1,2);
   day_to VARCHAR2(2):=SUBSTR(date_to,1,2);
   CURSOR trans_alloc(m VARCHAR2,y NUMBER,email VARCHAR2) IS SELECT * FROM EFT_TRANS_ALLOCATION WHERE LOWER(MONTH)=LOWER(m) AND YEAR=y AND LOWER(email_id)=LOWER(email);
   CURSOR trans_schedules(m1 VARCHAR2,y1 NUMBER,m2 VARCHAR2,y2 NUMBER) IS SELECT * FROM OD_SHIFT_SCHEDULE WHERE (UPPER(MONTH)=m1 OR UPPER(MONTH)=m2) AND (YEAR=y1 OR YEAR=y2);
   CURSOR get_res_id(email VARCHAR2) IS SELECT resource_id,first_name,last_name FROM EFT_RESOURCES WHERE LOWER(email_id)=LOWER(email);
   rec OD_SHIFT_SCHEDULE%ROWTYPE;
   CURSOR get_shifts(id VARCHAR2) IS SELECT * FROM OD_SHIFTS WHERE shift_id=id;
   ta EFT_TRANS_ALLOCATION%ROWTYPE;
   sd OD_SHIFTS%ROWTYPE;
   sql_str VARCHAR2(4000);
   v_res_id NUMBER;
   v_fname VARCHAR2(50);
   v_lname VARCHAR2(50);
   v_shift_id VARCHAR2(32);
   s VARCHAR2(1000);
   d VARCHAR2(32);
   final_sql VARCHAR2(4000);
   v_consent VARCHAR2(3);
   s2 VARCHAR2(1000);
   dn DATE;
BEGIN
   DELETE FROM EFT_SHIFT_SCHEDULES_RPT;

   OPEN trans_schedules(UPPER(str_month1),TO_NUMBER(str_year1),UPPER(str_month2),TO_NUMBER(str_year2));

   LOOP
      FETCH trans_schedules INTO rec;

	  EXIT WHEN trans_schedules%NOTFOUND;
	  OPEN get_res_id(rec.name);
	  FETCH get_res_id INTO v_res_id,v_fname,v_lname;
	     OPEN trans_alloc(TO_CHAR(date_counter,'MON'),TO_NUMBER(TO_CHAR(date_counter,'YYYY')),rec.name);
		 FETCH trans_alloc INTO ta;
		 
		 IF trans_alloc%FOUND THEN
		 dbms_output.put_line (date_counter||'----'||date_to||'Res_id:----'||v_res_id||'-'||ta.slno);
		 dbms_output.put_line ('Found Alloc');
		 WHILE date_counter<=date_to
		 LOOP
			 --d:=d||'-'||str_month||'-'||str_year;
			 
			 d:=SUBSTR(TO_CHAR(date_counter,'DD-MON-YYYY'),1,2);	
			 s:='INSERT INTO temp_2 SELECT "'||d||'" from OD_SHIFT_SCHEDULE where slno='||rec.slno;
			 --dbms_output.put_line (d||'-'||s);
			 DELETE FROM temp_2;
			 EXECUTE IMMEDIATE s;
			 SELECT substr(trim(VAL),1,1) INTO v_shift_id FROM temp_2;
			 
			 s2:='INSERT INTO temp_3 SELECT "'||d||'" from EFT_TRANS_ALLOCATION where slno='||ta.slno;
			 DELETE FROM temp_3;
			 EXECUTE IMMEDIATE s2;
			 SELECT VAL INTO v_consent FROM temp_3;
			 
			 --dbms_output.put_line (v_shift_id||'-'||v_consent);
 			 --dbms_output.put_line (date_counter||'-'||date_to);
			 IF v_consent='Y' THEN
			 --dbms_output.put_line ('Im inside consent'||v_consent);
			 IF v_shift_id IS NOT NULL THEN
			 --dbms_output.put_line ('Im inside shift not null'||v_shift_id);
			    OPEN get_shifts(v_shift_id);
			    FETCH get_shifts INTO sd;
				IF sd.night_shift_indicator='Y' THEN
				   dn:=date_counter+1;
	               sql_str:='INSERT INTO eft_shift_schedules_rpt VALUES ('||v_res_id||','''||v_fname||''','''||v_lname||''','''||rec.name||''','''||sd.shift_name||''','''||date_counter||''','''||sd.start_from||''','''||sd.start_to||''','''||''','''||sd.active_yn||''','''||dn||''')';
				ELSE
				   sql_str:='INSERT INTO eft_shift_schedules_rpt VALUES ('||v_res_id||','''||v_fname||''','''||v_lname||''','''||rec.name||''','''||sd.shift_name||''','''||date_counter||''','''||sd.start_from||''','''||sd.start_to||''','''||''','''||sd.active_yn||''','''||date_counter||''')';
				END IF;
			    EXECUTE IMMEDIATE sql_str;
			    CLOSE get_shifts;
				--dbms_output.put_line (sql_str);
			 END IF;

			 END IF;
			 COMMIT;
			 date_counter:=date_counter+1;	
	     END LOOP;
		 END IF;
      CLOSE trans_alloc;
	  CLOSE get_res_id;
	  --dbms_output.put_line (rec.name);
	  date_counter:=date_from;
   END LOOP;
   COMMIT;
   CLOSE trans_schedules;

final_sql:='select a.first_name,a.last_name,a.email_id,a.shift_details,a.arrange_date,a.slot_from,a.slot_to,a.trans_arranged,a.active_yn,b.address1,b.address2,b.city,b.state,b.pin,b.worknumber,b.homenumber,b.mobilenumber,a.to_date from eft_shift_schedules_rpt a,eft_locations b where a.resource_id=b.resource_id and b.city =:P46_CITY';
--dbms_output.put_line (final_sql);
RETURN final_sql;
END;