Shell Programming and Scripting

create or replace procedure newyork
as          
cursor rec 
IS
select * from location_queue where location like daily.mmm%';
V_LOC_CT       NUMBER;
v_location_id     VARCHAR2(50);
v_sales           NUMBER;
BEGIN 
for i in rec
loop
select substr(i.location,12,4)  INTO v_location_id from dual;
select substr(i.location,17,4) INTO v_sales from dual; 
DBMS_OUTPUT.PUT_LINE ('locationid is '||v_location_id);
DBMS_OUTPUT.PUT_LINE ('sales is '||v_sales);
SELECT COUNT(*) INTO LOC_CT FROM TOTAL_COUNT WHERE location_ID = v_location_id AND   sales= v_sales;
IF V_CHK_COUNT > 0 THEN
DELETE FROM TOTAL_COUNT WHERE location_ID = v_location_id AND sales= v_sales;
END

cursor rec 
IS
SELECT * FROM location_id_queue where location like '%DETAILS.LLL%';
V_value_COUNT       NUMBER;
V_proc_name       VARCHAR2(50);
V_valuexxx          NUMBER;
V_valuexxx2         NUMBER;
V_valuexxx3                           NUMBER;
v_location_id       VARCHAR2(50);
v_sales            NUMBER;
BEGIN 
for i in rec
loop
select substr(i.location,12,4)  INTO v_location_id from dual;
select substr(i.location,17,4) INTO v_sales from dual;                
v_proc_name := 'JOBS';
DBMS_OUTPUT.PUT_LINE ('Program name '||V_proc_name);
DBMS_OUTPUT.PUT_LINE ('outletid is '||v_location_id);
DBMS_OUTPUT.PUT_LINE ('sales is '||v_sales);
DBMS_OUTPUT.PUT_LINE ('Now Getting the count from the table for the outlet '||v_location_id);
SELECT COUNT(*) INTO V_value_COUNT FROM COUNT WHERE location_id = v_location_id AND   sales= v_sales;
                IF V_value_COUNT > 0 THEN
                DELETE FROM COUNT WHERE location_id = v_location_id AND sales= v_sales;
                ELSE
                DBMS_OUTPUT.PUT_LINE ('No Data found from the table TOTAL_Count '||v_location_id);
                END IF;
                IF v_location_id IN ('chen','bang') THEN
                select GET_COUNTS.GET_bang_chen_COUNT(v_location_id,v_sales) INTO V_valuexxx from dual;
  DBMS_OUTPUT.PUT_LINE ('chen/bang functions'|| V_valuexxx);
    ELSIF   v_location_id IN ('sale','tric','padi','madu','PUDU') THEN
   select GET_COUNTS.GET_OTHER_OUTLET_COUNT(v_location_id,v_sales) INTO V_valuexxx from dual;
  DBMS_OUTPUT.PUT_LINE ('sale/tric/padi/madu/PUDU function '|| V_valuexxx);
                ELSIF v_location_id IN ('TIRU') THEN
select GET_COUNTS.GET_TIRU_COUNT(v_location_id,v_sales) INTO V_valuexxx from dual;
DBMS_OUTPUT.PUT_LINE ('TIRU function'|| V_valuexxx);
                END IF; 
                IF V_valuexxx = 0 THEN
  select GET_COUNTS.GET_CRT_COUNT(v_location_id,v_sales) INTO V_valuexxx2 from dual;
select GET_COUNTS.GET_PANEL_COUNT(v_location_id,v_sales) INTO V_valuexxx3 from dual;
   END IF;
                 BEGIN
                IF  V_valuexxx = 0 and  V_valuexxx2 = 0 and  V_valuexxx3 = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Job Sucess'||v_location_id);
ELSE       
DBMS_OUTPUT.PUT_LINE ('Job Failed'|| v_location_id);
END IF; 
                EXCEPTION
            WHEN OTHERS THEN
                                                DBMS_OUTPUT.PUT_LINE ('Program errors while sending email due to:'||SQLERRM);   
                 END;
                  END LOOP;
                EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE ('Program errors due to:'||SQLERRM);
                
END;