Step 1 :


SQL> select * from location_queue ;

PROCESS_CODE                   SCHEDULED SCHED PERFORM_WHEN
------------------------------ --------- ----- --------------------
locationCNT CHICAGO,1672o           29-SEP-14
locationCNT BOSTON,1673           29-SEP-14
locationCNT CHICAGO,1673           29-SEP-14
locationCNT NEW YORK,1673           30-SEP-14
locationRETUPD                  19-DEC-14
locationZPUPD                   09-JAN-15

Each record need to be passed & location and sales need to be taken as input only for %locationCNT% records.

Input : location =CHICAGO, sales=1672 need to be 


Step 2:

SELECT COUNT(*) INTO value FROM areas WHERE location = &ocation AND   sales      = &ales;

Step 3 :

If count>0,delete the records from the table.
EXEC SQL DELETE FROM area   WHERE location = &ocation   AND   sales  = &sales;

Step 4 :

WHEN (location = 'CALIFORNIA'| location = 'BOSTON')
:VALUE:=PUTCOUNTS.LET_BOSTON
               (:location,:sales);
WHEN (location ='MASS'|location = 'CHICAGO' |location = 'NEW YORK')
:VALUE:=PUTCOUNTS.LET__CHICAGO
               (:location,:sales);
WHEN (location = 'MASCCEHUTES')
:VALUE:=PUTCOUNTS.LET_MASSCHEUTES
               (:location,:sales);
			   
			   

SQL> select * from location_queue ;

Location                          date            userid
------------------------------ --------- ----- --------------------
locationCNT CHICAGO,1672o           29-SEP-14    ldfo
locationCNT BOSTON,1673             29-SEP-14    lfoi
locationCNT CHICAGO,1673            29-SEP-14    lfoi
locationCNT NEW YORK,1673           30-SEP-14    lplm
locationRETUPD                      19-DEC-14    lfks
locationZPUPD                       09-JAN-15    fsds

Each record need to be passed & location and sales need to be taken as input only for %locationCNT% records.

Input : location =CHICAGO, sales=1672 need to be 


Step 2:

SELECT COUNT(*) INTO value FROM areas WHERE location = &location AND   sales      = &ales;

Step 3 :

If count>0,delete the records from the table.
EXEC SQL DELETE FROM area   WHERE location = &location   AND   sales  = &sales;

Step 4 :

if (location = 'CALIFORNIA'|| location = 'BOSTON')
:VALUE:=PUTCOUNTS.LET_BOSTON
               (:location,:sales);
if (location ='MASS'|location = 'CHICAGO' || location = 'NEW YORK')
:VALUE:=PUTCOUNTS.LET__CHICAGO
               (:location,:sales);
if (location = 'MASCCEHUTES')
:VALUE:=PUTCOUNTS.LET_MASSCHEUTES
               (:location,:sales);
			   

PUTCOUNTS.LET_MASSCHEUTES
PUTCOUNTS.LET__CHICAGO
PUTCOUNTS.LET_BOSTON

 DETAILS.LNM CHENNAI,2000 

Each record need to be validated and respective functions need to be triggered for location wise.
 select * from location_queue
 location_code
 ------------------------------ 
 DETAILS.LNM CHENNAI,2000 
 DETAILS.LNM BANGLORE,6000 
 DETAILS.LNM SALEM,5000 
 DETAILS.LNM TIRUPUR,2000 
 DETAILS.LNM TRICHY,4000 
 DETAILS.SKM 
 DETIALS.SKM 
 

select count(*) into count_locat from total_area where location='Chennai' and sales=2000;

if count_locat >0

delete from count_locat where location='Chennai' and sales=2000;

else 

print "no records found"

1) location=Chennai/banglore

Need to check function1[letchennai] returns o or 1.

value=putcount.letchennai('chennai',20000);

2) location =salem/trichy

Salem/trichy sales and location need to be passed in the function.

Need to check function1[letsalem] returns o or 1.

value=putcount.letsalem(:location,:sales);

Both the function [letsalem] and [letchennai] are different.

If value=0
then
do 
Mail "Job success" kkds@xxys.xom
else 
mail " Job failed" kkds@xxys.xom

fi

SQL> select * from location_queue;
select * from location_queue
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Step 1 :

SQL> select * from location_queue ;

PROCESS_CODE                   date
------------------------------ ---------  
locationCNT TRICHY,16720         29-SEP-14
locationCNT BANGLORE,16730       29-SEP-14
locationCNT TRICHY,16730         29-SEP-14
locationCNT SALEM,16730          30-SEP-14
locationRETUPD                   19-DEC-14
locationZPUPD                    09-JAN-15

Each record need to be passed & location and sales need to be taken as input only for %locationCNT% records.

Input : location =TRICHY, sales=1672 need to be 


Step 2:

select COUNT(*) into value from  areas where location = &ocation and sales= &ales;

Step 3 :

If count>0,delete the records from the table.

delete from areas  where  location = &ocation and   sales  = &sales;

Step 4 :

if (location = 'CHENNAI'| location = 'BANGLORE')
VALUE:=PUTCOUNTS.LET_BANGLORE(&location,&sales);
if(location ='KOVAI'|location = 'TRICHY' |location = 'SALEM')
VALUE:=PUTCOUNTS.LET__TRICHY('&location',&sales);
WHEN (location = 'TIRUPUR')
VALUE:=PUTCOUNTS.LET_TIRUPUR(:location,:sales);
			   
Step 5:
If value=0 , then calculate value1 & value2 by executing the below function.
value1:=PUTCOUNTS.PUTVALUE1(&LOCATION,&SALES;
value2:=PUTCOUNTS.PUTVALUE2(&LOCATION,&SALES);

step 6:

if [value=0 && value1=0 && value2=0] then
do 
print "Job got sucesss"
else
print "job got failed"

create table location_queue
(
location varchar2(30) NOT NULL, date_time date NOT NULL, id varchar2(5) NOT NULL);

insert INTO location_queue(location, date_time,id)VALUES('locationCNT CHICAGO,1672o', '29-SEP-14', 'ldfo');

insert INTO location_queue(location, date_time,id)VALUES('locationCNT BOSTON,1673',  '29-SEP-14', 'lfoi');

insert INTO location_queue(location, date_time,id)VALUES('locationCNT CHICAGO,1673', '29-SEP-14', 'lfoi');

insert INTO location_queue(location, date_time,id)VALUES('locationCNT NEW YORK,1673', '29-SEP-14', 'lplm');

insert INTO location_queue(location, date_time,id)VALUES('locationRETUPD', '29-SEP-14', 'lfks');

insert INTO location_queue(location, date_time,id)VALUES('locationZPUPD', '29-SEP-14', 'fsd');

insert INTO location_queue(location, date_time,id)VALUES('locationCNT CHENNAI,1672o', 'to_date', 'ldfo');

insert INTO location_queue(location, date_time,id)VALUES('locationCNT BANGALORE,1673',  'to_date', 'lfoi');

insert INTO location_queue(location, date_time,id)VALUES('locationCNT SALEM,1673', 'to_date', 'lfoi');

insert INTO location_queue(location, date_time,id)VALUES('locationCNT TRICHY,1673', 'to_date', 'lplm');

insert INTO location_queue(location, date_time,id)VALUES('locationRETUPD', '29-SEP-14', 'to_date');

insert INTO location_queue(location, date_time,id)VALUES('locationZPUPD', '29-SEP-14', 'to_date');

 SELECT sysdate, TO_DATE('27-07-2015', 'DD-MM-YYYY'), DATE '2015-07-27' FROM dual;

SQL> select * from location_queue ;

PROCESS_CODE                   date
------------------------------ ---------  
locationCNT TRICHY,16720         29-SEP-14
locationCNT BANGLORE,16730       29-SEP-14
locationCNT TRICHY,16730         29-SEP-14
locationCNT SALEM,16730          30-SEP-14
locationRETUPD                   19-DEC-14
locationZPUPD                    09-JAN-15

Create or replace procedure Main_Proc 
										
AS

cursor rec 
IS
      SELECT * FROM location; 

V_value       NUMBER;
v_proc_name       VARCHAR2(50);
V_value          NUMBER;
V_value2         NUMBER;
V_value3 		  NUMBER;
BEGIN 
v_proc_name := 'PROCESS';

DBMS_OUTPUT.PUT_LINE ('Insaide the'||v_proc_name);
DBMS_OUTPUT.PUT_LINE ('Now Getting the count from the table areas');
SELECT COUNT(*)
  INTO V_value
  FROM areas
  WHERE location = rec.location
    AND   sales    = rec.sales;
	
	IF V_value > 0 THEN
	DELETE FROM areas
        WHERE location = rec.location
        AND   sales      = rec.sales;
	ELSE
	DBMS_OUTPUT.PUT_LINE ('No Data found from the table areas');
	END IF;
	
	IF rec.location IN ('CHENNAI','BANGLORE') THEN
	V_value := PUTCOUNTS.LET_BANGLORE(rec.location,rec.sales);
    ELSIF   rec.location  IN ('SALEM','TRICHY','KOVAI') THEN
	V_value := PUTCOUNTS.LET__TRICHY(rec.location,rec.sales);
	ELSE rec.location  IN ('TIRUPUR') THEN
	V_value := PUTCOUNTS.LET_TIRUPUR(rec.location,rec.sales);
	 END IF;	
	 
	 IF V_value = 0 THEN
	 V_value2 := PUTCOUNTS.PUTVALUE1(rec.location,rec.sales);
	 V_value3 := PUTCOUNTS.PUTVALUE2(rec.location,rec.sales);
	 END IF;
	 
	 BEGIN
	 IF  V_value = 0 and  V_value2 = 0 and  V_value3 = 0 THEN

	 EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
     UTL_MAIL.send(sender     => 'me@address.com',
                   recipients => 'you@address.com',
                   subject    => 'Test Mail',
                   message    => 'Job Success',
                   mime_type   => 'text; charset=us-ascii');
     
    ELSE	
	 EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
     UTL_MAIL.send(sender     => 'me@address.com',
                   recipients => 'you@address.com',
                   subject    => 'Test Mail',
                   message    => 'Job FAIL',
                   mime_type   => 'text; charset=us-ascii');	
				  
	 END IF;	
       	EXCEPTION
            WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE ('Program errors while sending email due to:'||SQLERRM);	 
	 END;
	  
	EXCEPTION
            WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE ('Program errors due to:'||SQLERRM);
	

END;

 SELECT * FROM location_queue; 

location -> column name.

for i in rec
loop
	v_locattion_id :=substr('rec.location',12,4);
	v_sales := substr('rec.location',17,4);
	
v_proc_name := 'SDM JOBS';

DBMS_OUTPUT.PUT_LINE ('Inside the'||V_proc_name);
DBMS_OUTPUT.PUT_LINE ('Inside the programaa'||v_locatioon_id);
DBMS_OUTPUT.PUT_LINE ('Inside the week'||v_sales);

v_locattion_id :=substr('rec.location',12,4);
	v_sales := substr('rec.location',17,4);

SQL> select substr('rec.location',12,4) from dual;

S
-
n