CREATE OR REPLACE PROCEDURE del_merge_proc
IS
ownername VARCHAR2(30);
mymainquery CLOB; 
v_tat_value VARCHAR2(100);
prior_process_exist NUMBER;
prior_process_dt VARCHAR2(100);

BEGIN
--  DELETES THE RECORDS WHICH ARE OLDER THAN TWO WEEKS.

DELETE  FROM maint_data 
WHERE datatime_dt <=(WITH dates AS (select DECODE( 
    TRIM(TO_CHAR(SYSDATE,'DAY')), 
    'SATURDAY',1, 
    'SUNDAY',2, 
    'MONDAY',3, 
    'TUESDAY',4, 
    'WEDNESDAY',5, 
    'THURSDAY',6, 
    'FRIDAY',7) daynumber  
FROM dual )
SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7);
COMMIT;

/* KEEPING THE DATE IN THE STAMPING TABLE WHEN THE PROCEDURE RAN 
AND THE NEXT RUN IT SHOULD TAKE THE RECORDS BETWEEN PREVIOUS RUN AND SYSDATE */

SELECT COUNT(*) INTO prior_process_exist from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
IF prior_process_exist = 0 THEN
    prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
    INSERT INTO stamping_tbl(stamp_name,stamp_date)
    VALUES('PROCESS_DONE',prior_process_dt);
ELSE
    SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
    UPDATE stamping_tbl 
    SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
    WHERE stamp_name='PROCESS_DONE';
END IF;

FOR schemain IN (SELECT schema_name FROM schema_list  ORDER BY schema_name) 
LOOP
SELECT tat_value INTO v_tat_value FROM tat_info_tbl WHERE tat_name='MASTERTAT';
BEGIN
ownername := schemain.schema_name||'.';

mymainquery := 'MERGE INTO maint_data target
USING (
WITH unitvalues AS (
SELECT MNG_NO, BIDVAL_NO, 
    SUM(unitval) unitval,
    SUM(unitrate) unitrate,
    SUM(unitmargin)  unitmargin
FROM '||ownername||'rate_values R
GROUP BY MNG_NO,BIDVAL_NO)
SELECT DISTINCT 
    PHD.MNG_NO MNG_NO,
    RL.BIDVAL_NO BIDVAL_NO,
    PHD.ccd_code ccd_code,
    CASE WHEN UPPER(PHD.ccd_code)= UPPER('''||v_tat_value||''') 
    THEN 1
    ELSE 2
    END ccd_rate,
    RL.making_name making_name,
    PMR.making_unit making_unit
FROM '||ownername||'ps_hpold_desg PHD
    LEFT OUTER JOIN '||ownername||'ps_manage_rts PMR
    ON PHD.MNG_NO = PMR.MNG_NO
    LEFT OUTER JOIN '||ownername||'rate_values RL 
    ON (RL.BIDVAL_NO = PMR.BIDVAL_NO OR PMR.BIDVAL_NO IS NULL) AND PHD.MNG_NO = RL.MNG_NO
    INNER JOIN unitvalues ON unitvalues.MNG_NO=PHD.MNG_NO AND unitvalues.BIDVAL_NO = RL.BIDVAL_NO
    LEFT OUTER JOIN '||ownername||'GEDIS_SALESPERSON GLS ON PHD.LEAD_SALESPERSON = GLS.SALESPERSON_ID
    WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE( 
    TRIM(TO_CHAR(SYSDATE,''DAY'')), 
    ''SATURDAY'',1, 
    ''SUNDAY'',2, 
    ''MONDAY'',3, 
    ''TUESDAY'',4, 
    ''WEDNESDAY'',5, 
    ''THURSDAY'',6, 
    ''FRIDAY'',7) daynumber  
FROM dual )
SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7) 
    AND ('||prior_process_exist||' = 0)
    OR ('||prior_process_exist||' <> 0 AND PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'') AND SYSDATE))source
ON(source.MNG_NO = target.tg_MNG_NO
AND source.BIDVAL_NO=target.tg_BIDVAL_NO)
WHEN MATCHED THEN
UPDATE SET 
target.tg_ccd_code.ccd_code,
target.tg_ccd_rate =ccd_rate,  
target.tg_making_name = source.making_name, 
target.tg_making_unit = source.making_unit
WHEN NOT MATCHED THEN
INSERT 
(
target.tg_MNG_NO,
target.tg_BIDVAL_NO,
target.tg_ccd_code,
target.tg_ccd_rate,
target.making_name,
target.making_unit,
)
VALUES
(
source.MNG_NO,
source.tg_BIDVAL_NO,
source.ccd_code,
source.ccd_rate,
source.making_name,
source.making_unit
)';

EXECUTE IMMEDIATE mymainquery;
COMMIT;

MERGE INTO maint_data t
USING
(
SELECT * FROM(
WITH dates AS (select DECODE( 
    TRIM(TO_CHAR(SYSDATE,'DAY')), 
    'SATURDAY',1, 
    'SUNDAY',2, 
    'MONDAY',3, 
    'TUESDAY',4, 
    'WEDNESDAY',5, 
    'THURSDAY',6, 
    'FRIDAY',7) daynumber  
FROM dual )
SELECT (sysdate-(ROWNUM-1)) weekday_name,
CASE WHEN (daynumber)-(ROWNUM-1)<=0 THEN 8
ELSE (daynumber)-(ROWNUM-1)
END weekday_number
FROM dates CONNECT BY ROWNUM <=dates.daynumber+7)
)s
ON(TRUNC(s.weekday_name)=TRUNC(t.datatime_dt))
WHEN MATCHED THEN
UPDATE SET t.QO_SNAPSHOT_ID=s.weekday_number;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
END;

UPDATE STAMPING_TBL
         SET STAMP_DATE = SYSDATE

SQL> SELECT VALUE FROM v$parameter WHERE NAME='nls_date_format';
 
VALUE
--------------------------------------------------------------------------------
mm/dd/yyyy hh24:mi:ss
SQL> SELECT SYSDATE from dual;
 
SYSDATE
-----------
10/11/2013
SQL> DROP TABLE t;
 
Table dropped
SQL> CREATE TABLE t(dt DATE);
 
Table created
SQL> INSERT INTO t VALUES(SYSDATE);
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SELECT to_char(dt, 'DD-MON-YYYY HH24:MI:SS') dt from t;
 
DT
--------------------
11-OCT-2013 05:39:36
SQL> UPDATE t SET dt = SYSDATE+1;
 
1 row updated
SQL> COMMIT;
 
Commit complete
SQL> SELECT to_char(dt, 'DD-MON-YYYY HH24:MI:SS') dt from t;
 
DT
--------------------
12-OCT-2013 05:39:37

DECLARE
prior_process_dt DATE;
BEGIN
IF prior_process_exist = 0 THEN
    prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
    INSERT INTO stamping_tbl(stamp_name,stamp_date)
    VALUES('PROCESS_DONE',prior_process_dt);
ELSE
    SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';
    UPDATE stamping_tbl 
    SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
    WHERE stamp_name='PROCESS_DONE';
END IF;

 AND PHD.UPDATED_TIME BETWEEN TO_DATE('''||to_char(prior_process_dt, 'DD-MON-YYYY HH24:MI:SS')||''',''DD-MON-YYYY HH24:MI:SS'')
 

EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;

CREATE OR REPLACE PROCEDURE del_merge_proc
IS
BEGIN
  NULL;
END;

DECLARE
  prior_process_dt DATE;
BEGIN
  IF prior_process_exist = 0 THEN
    prior_process_dt    := SYSDATE;
    INSERT
    INTO stamping_tbl
      (
        stamp_name,
        stamp_date
      )
      VALUES
      (
        'PROCESS_DONE',
        prior_process_dt
      );
  ELSE
    SELECT stamp_date
    INTO prior_process_dt
    FROM stamping_tbl
    WHERE stamp_name = 'PROCESS_DONE';
    
    UPDATE stamping_tbl
    SET stamp_date  =SYSDATE
    WHERE stamp_name='PROCESS_DONE';
  END IF;

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> declare
  2    l_date date := sysdate;
  3  begin
  4    dbms_output.put_line(l_date);
  5  end;
  6  /
13.10.2013

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> declare
  2    l_date date := sysdate;
  3  begin
  4    dbms_output.put_line(l_date);
  5  end;
  6  /
13.10.2013 11:35:03

PL/SQL procedure successfully completed.

SQL>

PHD.UPDATED_TIME BETWEEN prior_process_dt AND SYSDATE;

WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE(
    TRIM(TO_CHAR(SYSDATE,''DAY'')),
    ''SATURDAY'',1,
    ''SUNDAY'',2,
    ''MONDAY'',3,
    ''TUESDAY'',4,
    ''WEDNESDAY'',5,
    ''THURSDAY'',6,
    ''FRIDAY'',7) daynumber
FROM dual )
SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7)

SET SERVEROUTPUT ON
DECLARE
v_sql varchar2(2000);
LAST_PROCESS_DONE   NUMBER;
LAST_PROCESS_DATE   date;
tcount NUMBER :=0;
begin
SELECT COUNT(*) INTO LAST_PROCESS_DONE from DATE_STAMP 
WHERE name = 'PROCESS_TIME';
IF LAST_PROCESS_DONE = 0 THEN
    LAST_PROCESS_DATE := SYSDATE;
    INSERT INTO stamping_tbl (name,dt_value)
    VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
ELSE
    SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl 
      WHERE name = 'PROCESS_TIME';
    UPDATE stamping_tbl 
    SET dt_value=SYSDATE
    WHERE name='PROCESS_TIME';
END IF;
v_sql := 'SELECT COUNT(*)  FROM dates_tbl
WHERE r_date BETWEEN '||LAST_PROCESS_DATE||' and sysdate';
execute immediate v_sql INTO tcount;
    DBMS_OUTPUT.PUT_LINE(tcount);
end;

SET SERVEROUTPUT ON
DECLARE
v_sql varchar2(2000);
LAST_PROCESS_DONE   NUMBER;
LAST_PROCESS_DATE   date;
tcount NUMBER :=0;
begin
SELECT COUNT(*) INTO LAST_PROCESS_DONE from DATE_STAMP 
WHERE name = 'PROCESS_TIME';
IF LAST_PROCESS_DONE = 0 THEN
    LAST_PROCESS_DATE := SYSDATE;
    INSERT INTO stamping_tbl (name,dt_value)
    VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
ELSE
    SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl 
      WHERE name = 'PROCESS_TIME';
    UPDATE stamping_tbl 
    SET dt_value=SYSDATE
    WHERE name='PROCESS_TIME';
END IF;
v_sql := 'SELECT COUNT(*)  FROM dates_tbl
WHERE r_date BETWEEN '''||LAST_PROCESS_DATE||''' and sysdate';
execute immediate v_sql INTO tcount;
    DBMS_OUTPUT.PUT_LINE(tcount);
end;

INSERT INTO stamping_tbl VALUES('PROCESS_TIME',SYSDATE);

After 30 minutes

SELECT a.dt_value,sysdate FROM stamping_tbl a;

10/13/2013 12:18:43 PM  10/13/2013 12:48:43 PM

INSERT INTO dates_tbl VALUES(sysdate-interval '10' minute);
INSERT INTO dates_tbl VALUES(sysdate-3/24);
INSERT INTO dates_tbl VALUES(sysdate-4/24);

The below pl/sql block is returning count as 3 .Instead of 1

SET SERVEROUTPUT ON
DECLARE
v_sql varchar2(2000);
LAST_PROCESS_DONE   NUMBER;
LAST_PROCESS_DATE   date;
tcount NUMBER :=0;
begin
SELECT COUNT(*) INTO LAST_PROCESS_DONE from stamping_tbl 
WHERE name = 'PROCESS_TIME';
IF LAST_PROCESS_DONE = 0 THEN
    LAST_PROCESS_DATE := SYSDATE;
    INSERT INTO stamping_tbl (name,dt_value)
    VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
ELSE
    SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl 
      WHERE name = 'PROCESS_TIME';
    UPDATE stamping_tbl 
    SET dt_value=SYSDATE
    WHERE name='PROCESS_TIME';
END IF;
v_sql := 'SELECT COUNT(*)  FROM dates_tbl
WHERE r_date BETWEEN '''||LAST_PROCESS_DATE||''' and sysdate';
execute immediate v_sql INTO tcount;
    DBMS_OUTPUT.PUT_LINE(tcount);
end;

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> create table stamping_tbl(name varchar2(20), dt_value date);

Table created.

SQL> INSERT INTO stamping_tbl VALUES('PROCESS_TIME',SYSDATE-1/24);

1 row created.

SQL>
SQL> SELECT dt_value, sysdate FROM stamping_tbl;

DT_VALUE             SYSDATE
-------------------- --------------------
13-oct-2013 17:09:52 13-oct-2013 18:09:52

SQL>
SQL> create table dates_tbl(r_date date);

Table created.

SQL> INSERT INTO dates_tbl VALUES(sysdate-interval '10' minute);

1 row created.

SQL> INSERT INTO dates_tbl VALUES(sysdate-3/24);

1 row created.

SQL> INSERT INTO dates_tbl VALUES(sysdate-4/24);

1 row created.

SQL>
SQL> select * from dates_tbl;

R_DATE
--------------------
13-oct-2013 17:59:52
13-oct-2013 15:09:52
13-oct-2013 14:09:52

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  v_sql varchar2(2000);
  3  LAST_PROCESS_DONE   NUMBER;
  4  LAST_PROCESS_DATE   date;
  5  tcount NUMBER :=0;
  6  begin
  7  SELECT COUNT(*) INTO LAST_PROCESS_DONE from stamping_tbl
  8  WHERE name = 'PROCESS_TIME';
  9  IF LAST_PROCESS_DONE = 0 THEN
 10      DBMS_OUTPUT.PUT_LINE('INSIDE IF BLOCK');
 11      LAST_PROCESS_DATE := SYSDATE;
 12      INSERT INTO stamping_tbl (name,dt_value)
 13      VALUES('PROCESS_TIME',LAST_PROCESS_DATE);
 14  ELSE
 15      DBMS_OUTPUT.PUT_LINE('INSIDE ELSE BLOCK');
 16      SELECT dt_value INTO LAST_PROCESS_DATE from stamping_tbl
 17        WHERE name = 'PROCESS_TIME';
 18      UPDATE stamping_tbl
 19      SET dt_value=SYSDATE
 20      WHERE name='PROCESS_TIME';
 21  END IF;
 22  v_sql := 'SELECT COUNT(*)  FROM dates_tbl
 23  WHERE r_date BETWEEN '''||LAST_PROCESS_DATE||''' and sysdate';
 24  execute immediate v_sql INTO tcount;
 25      DBMS_OUTPUT.PUT_LINE(LAST_PROCESS_DATE ||' , '||sysdate);
 26      DBMS_OUTPUT.PUT_LINE(tcount);
 27  end;
 28  /
INSIDE ELSE BLOCK
13-oct-2013 17:09:52 , 13-oct-2013 18:09:53
1

PL/SQL procedure successfully completed.