CREATE PROCEDURE       "CALLPROMOSIGNBATCH"(batch_type_to_execute IN varchar2, 
size_code_param varchar2, debug_mode varchar2, batch_id_param varchar2, 
start_sign_id_param varchar2, end_sign_id_param varchar2, thread_number_for_size_param varchar2) as
var_SGN_ID        varchar(10);  
var_STR_NMB        number;  
var_row_number        number;
var_BATCH_NAME        varchar(50);
var_VER_NMB        number(10);
var_DL_NMB        number(10);
var_SRT_DT        DATE;
var_END_DT        DATE;
var_LAST_RUN_TIME    DATE;
var_LAST_RUN_END_TIME    DATE;
var_LAST_RUN_START_TIME    DATE;
var_MODIFIED_DATE    DATE;
var_CREATE_DATE        DATE;
var_hdr_batch_id number        := 0;
var_batch_id number        := 0;
var_batch_check number        := 0;
var_BATCH_TYPE varchar(5)    := '';
var_dtl_check number        := 0;
var_SIZE varchar(30)        := '';
proc_name VARCHAR2(80)        := 'SIGN_BATCH';
var_restart_batch varchar2(80)    := 'SIGN_BATCH_RESTART';
var_RESTART_SGN_ID number    := 0;
var_RESTART_SIZE_ID number    := 0;
err_num    NUMBER;
err_msg VARCHAR2(150);
active_batch_name varchar2(2)    := 'A';
put_up_today_batch_name varchar2(2)    := 'PT';
upcoming_batch_name varchar2(2)    := 'PU';
correction_batch_name varchar2(2) := 'C';
external_sign_kits_batch_name varchar2(2) := 'EX';
external_sign_kits varchar2(20) := 'External Sign Kits';
emergency_flag varchar2(1)    := '';
old_size_code number        := 0;
new_size_code number        := 0;
old_batch_name varchar2(100)    := '';
new_batch_name varchar2(100)    := '';
var_STR_GRP_NMB    number(22);
var_delimeter varchar(1)    := '|';
old_sign_id number(8) := 0;
new_sign_id number(8) := 0;
old_store_count number := 0;
new_store_count number := 0;
process_store varchar(1) := 'Y';
var_start_sign_id number := 0;
var_end_sign_id number  := 0;

-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR Cursor_Sign_Batch_Str(param_DL_NMB number, param_VER_NMB number, param_STR_GRP_NMB number)   
is
        select distinct vh.str_nmb 
    from ver_str@VDL1_CAMPT.TRU.COM vh
    where vh.dl_nmb = param_DL_NMB and vh.ver_nmb = param_VER_NMB
        and (param_STR_GRP_NMB = 0 or vh.str_nmb in (
            select SSGD.str_nmb from SGN_STR_GRP_DTL SSGD 
            where SSGD.STR_GRP_NUM = param_STR_GRP_NMB 
        )
    );

-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR batch_restart_check(param_BATCH_TYPE varchar) is
        select substr(ERR_DTL, 0, INSTR(err_dtl,var_delimeter) + (length(var_delimeter) -2)) as size_cd, 
         err_cd as sgn_id
        from ERR e where e.ERR_SRC = var_restart_batch
        and trim(substr(ERR_DTL, INSTR(err_dtl,var_delimeter)+length(var_delimeter))) = trim(param_BATCH_TYPE) and 
        to_date(err_tm, 'dd/MM/yy') = to_date(SYSDATE, 'dd/MM/yy')
    ;
-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR batch_restart_check_for_size(param_BATCH_TYPE varchar) is
        select substr(ERR_DTL, 0, INSTR(err_dtl,var_delimeter) + (length(var_delimeter) -2)) as size_cd, 
         err_cd as sgn_id
        from ERR e where e.ERR_SRC = var_restart_batch||'_'||size_code_param||'_'||thread_number_for_size_param
        and trim(substr(ERR_DTL, INSTR(err_dtl,var_delimeter)+length(var_delimeter))) = trim(param_BATCH_TYPE) and 
        to_date(err_tm, 'dd/MM/yy') = to_date(SYSDATE, 'dd/MM/yy')
    ;
-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR last_run_check(param_batch_name varchar) is
    select start_time, end_time from SPF_VERSION_CONTROL where JOB_TYPE = 'SIGN_BATCH' and FILE_NAME = param_batch_name;

-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR Active_Today_Curs_Sign_Batch(param_var_RESTART_SIZE_ID number, param_var_RESTART_SGN_ID number, param_emergency_flag varchar) 
is 
    select distinct sh.sgn_id,
    link_dtl.dl_nmb,
    link_dtl.ver_nmb,
    link_dtl.srt_dt,
    link_dtl.end_dt,
    'Active' || '-' || trim(size_dtl.CODES_CAT_KEY_DECODE) as Batch_Name ,
    active_batch_name as batch_type,
    SH.STR_GRP_NUM,
    sh.sgn_size_cd,
    link_dtl.STR_COUNT as STR_COUNT
    from (
        select dl_ver.ver_nmb, dl_ver.dl_nmb, dl_ver.srt_dt, dl_ver.end_dt, 
        dl_ver.sgn_link_id, 
        decode(se.link_exception_id, null, dl_ver.SGN_CLUSTER_ID, se.SGN_CLUSTER_ID) SGN_CLUSTER_ID,
        decode(se.link_exception_id, null, dl_ver.CREATE_DT, se.CREATE_DT) LINK_MODIFY_DT,
        dl_ver.STR_COUNT
        from 
        (
            select dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT
            , count(vs.str_nmb) as STR_COUNT
            from dl_HDR@VDL1_CAMPT.TRU.COM dh,
            ver_HDR@VDL1_CAMPT.TRU.COM vh, 
        ver_str@VDL1_CAMPT.TRU.COM vs,
        sgn_deal_cluster_link sd 
            where sd.dl_nmb = dh.dl_nmb
            and dh.STS_CD in ('S','C')
            and dh.dl_nmb = vh.dl_nmb
            -- CONDITION TO GET ACTIVE DEALS
            and to_date(sysdate, 'dd/MM/yy') between to_date(vh.SRT_DT, 'dd/MM/yy') and 
            to_date(vh.END_DT, 'dd/MM/yy')
        and vs.dl_nmb = vh.dl_nmb
        and vs.ver_nmb = vh.ver_nmb
        group by dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT
        ) 
        dl_ver, 
        sgn_deal_exception_link se
        where   dl_ver.ver_nmb = se.ver_nmb (+) 
        and     se.sgn_link_id (+) = dl_ver.sgn_link_id
    ) 
    link_dtl, 
    (
        select scd.codes_cat_key, scd.CODES_CAT_KEY_DECODE
                from sgn_codes_hdr sch,
                sgn_codes_dtl scd
                where sch.CODES_CAT_DESC = 'SIZE'
                and scd.codes_cat_num = sch.codes_cat_num
    ) 
    size_dtl,
    SGN_HDR SH, 
    SGN_CLUSTER sc,
    (
        SELECT START_TIME FROM SPF_VERSION_CONTROL WHERE JOB_TYPE = 'SIGN_BATCH' and FILE_NAME = active_batch_name
    ) 
    last_run
    WHERE 
    link_dtl.SGN_CLUSTER_ID = SH.SGN_CLUSTER_ID
    and sh.sgn_size_cd = size_dtl.codes_cat_key (+)
    and ( sh.sgn_size_cd > param_var_RESTART_SIZE_ID OR
    (sh.sgn_size_cd = param_var_RESTART_SIZE_ID AND sh.sgn_id >= param_var_RESTART_SGN_ID) )
    and sh.DELETE_FL = 'N'
    and 
    (
        param_emergency_flag = 'N'  
        or 
        (
            (SH.MODIFIED_DATE  between last_run.START_TIME AND sysdate) 
            or
            (SH.CREATE_DATE  between last_run.START_TIME AND sysdate) 
            or
            (link_dtl.LINK_MODIFY_DT  between last_run.START_TIME AND sysdate)
        )
    )
    AND sc.STR_NMB = sh.STR_NMB
    AND sc.DIV = sh.DIV
    AND sc.CLUSTER_ID = sh.CLUSTER_ID    
    AND sc.SGN_TYP_SUB_CLUSTER = sh.SGN_TYP_SUB_CLUSTER
    AND sc.DPT_NMB = sh.DPT_NMB    
    AND sc.DEAL_DESC_FL ='Y'
    AND sc.sgn_cluster_id = sh.sgn_cluster_id
    AND sh.sgn_size_cd = size_code_param
    AND sh.SGN_ID >= var_start_sign_id
    AND sh.SGN_ID <= var_end_sign_id
    order by sh.sgn_size_cd, sh.sgn_id, link_dtl.ver_nmb, link_dtl.dl_nmb;

-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR PutUp_Today_Curs_Sign_Batch(param_var_RESTART_SIZE_ID number, param_var_RESTART_SGN_ID number, param_emergency_flag varchar) 
is 
    select distinct sh.sgn_id,
    link_dtl.dl_nmb,
    link_dtl.ver_nmb,
    link_dtl.srt_dt,
    link_dtl.end_dt,
    to_char(link_dtl.srt_dt, 'MM/DD/yyyy') || '-' || trim(size_dtl.CODES_CAT_KEY_DECODE) as Batch_Name ,
    put_up_today_batch_name as batch_type,
    SH.STR_GRP_NUM,
    sh.sgn_size_cd,
    link_dtl.STR_COUNT as STR_COUNT
    from (
        select dl_ver.ver_nmb, dl_ver.dl_nmb, dl_ver.srt_dt, dl_ver.end_dt, 
        dl_ver.sgn_link_id, 
        decode(se.link_exception_id, null, dl_ver.SGN_CLUSTER_ID, se.SGN_CLUSTER_ID) SGN_CLUSTER_ID,
        decode(se.link_exception_id, null, dl_ver.CREATE_DT, se.CREATE_DT) LINK_MODIFY_DT,
        dl_ver.STR_COUNT
        from 
        (
            select dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT, count(vs.str_nmb) as STR_COUNT
            from dl_HDR@VDL1_CAMPT.TRU.COM dh,
            ver_HDR@VDL1_CAMPT.TRU.COM vh, 
        ver_str@VDL1_CAMPT.TRU.COM vs,
        sgn_deal_cluster_link sd 
            where sd.dl_nmb = dh.dl_nmb
            and dh.dl_nmb = vh.dl_nmb
            AND sd.dl_nmb = Vh.dl_nmb
            and dh.STS_CD in ('S','C')
            -- CONDITION TO GET ACTIVE DEALS
            and to_date(sysdate, 'dd/MM/yy') = to_date(vh.SRT_DT, 'dd/MM/yy')
        and vs.dl_nmb = vh.dl_nmb
        and vs.ver_nmb = vh.ver_nmb
        group by dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT
        ) 
        dl_ver, 
        sgn_deal_exception_link se
        where   dl_ver.ver_nmb = se.ver_nmb (+) 
        and     se.sgn_link_id (+) = dl_ver.sgn_link_id
    ) 
    link_dtl, 
    (
        select scd.codes_cat_key, scd.CODES_CAT_KEY_DECODE
                from sgn_codes_hdr sch,
                sgn_codes_dtl scd
                where sch.CODES_CAT_DESC = 'SIZE'
                and scd.codes_cat_num = sch.codes_cat_num
    ) 
    size_dtl,
    SGN_HDR SH, 
    SGN_CLUSTER sc,
    (
        SELECT START_TIME FROM SPF_VERSION_CONTROL WHERE JOB_TYPE = 'SIGN_BATCH' and FILE_NAME = put_up_today_batch_name
    ) 
    last_run
    WHERE 
    link_dtl.SGN_CLUSTER_ID = SH.SGN_CLUSTER_ID
    and sh.sgn_size_cd = size_dtl.codes_cat_key (+)
    and ( sh.sgn_size_cd > param_var_RESTART_SIZE_ID OR
    (sh.sgn_size_cd = param_var_RESTART_SIZE_ID AND sh.sgn_id >= param_var_RESTART_SGN_ID) )
    and sh.DELETE_FL = 'N'
    and 
    (
        param_emergency_flag = 'N'  
        or 
        (
            (SH.MODIFIED_DATE  between last_run.START_TIME AND sysdate) 
            or
            (SH.CREATE_DATE  between last_run.START_TIME AND sysdate) 
            or
            (link_dtl.LINK_MODIFY_DT  between last_run.START_TIME AND sysdate)
        )
    )
    AND sc.STR_NMB = sh.STR_NMB
    AND sc.DIV = sh.DIV
    AND sc.DPT_NMB = sh.DPT_NMB    
    AND sc.CLUSTER_ID = sh.CLUSTER_ID    
    AND sc.SGN_TYP_SUB_CLUSTER = sh.SGN_TYP_SUB_CLUSTER
    AND sc.DEAL_DESC_FL ='Y'
    AND sc.sgn_cluster_id = sh.sgn_cluster_id
    order by sh.sgn_size_cd, sh.sgn_id, link_dtl.ver_nmb, link_dtl.dl_nmb;

-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR PrintUpcoming_Curs_Sign_Batch(param_var_RESTART_SIZE_ID number, param_var_RESTART_SGN_ID number, param_emergency_flag varchar) 
is    
    select
    distinct sh.sgn_id,
    link_dtl.dl_nmb,
    link_dtl.ver_nmb,
    link_dtl.srt_dt,
    link_dtl.end_dt,    
    to_char(link_dtl.srt_dt, 'MM/dd/yyyy') || '-' || trim(size_dtl.CODES_CAT_KEY_DECODE) as 
    Batch_Name,
    upcoming_batch_name as batch_type,
    SH.STR_GRP_NUM,
    sh.sgn_size_cd,
    link_dtl.STR_COUNT as STR_COUNT
    from 
    (
        select dl_ver.ver_nmb, dl_ver.dl_nmb, dl_ver.srt_dt, dl_ver.end_dt, dl_ver.sgn_link_id,
        decode(se.link_exception_id, null, dl_ver.SGN_CLUSTER_ID, se.SGN_CLUSTER_ID) SGN_CLUSTER_ID,
        decode(se.link_exception_id, null, dl_ver.CREATE_DT, se.CREATE_DT) LINK_MODIFY_DT,
        dl_ver.STR_COUNT
        from 
        (
            select dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT, count(vs.str_nmb) as STR_COUNT
            from dl_HDR@VDL1_CAMPT.TRU.COM dh,
        ver_HDR@VDL1_CAMPT.TRU.COM vh,  
        ver_str@VDL1_CAMPT.TRU.COM vs,
            sgn_deal_cluster_link sd 
            where    sd.dl_nmb = dh.dl_nmb 
            and      dh.dl_nmb = vh.dl_nmb
            AND      sd.dl_nmb = Vh.dl_nmb
            AND      dh.STS_CD in ('S','C')
            and to_char(vh.srt_dt, 'dd/MM/yy') in 
            (
                select to_char ((sysdate+ mod((7 + batch_day - setup_day), 7)) , 'dd/MM/yy')   
                from sgn_batch_setup where setup_day = to_char(sysdate,'D')
            )
        and vs.dl_nmb = vh.dl_nmb
        and vs.ver_nmb = vh.ver_nmb
        group by dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT
        ) 
        dl_ver, 
        sgn_deal_exception_link se
        where   dl_ver.ver_nmb = se.ver_nmb (+) 
        and     se.sgn_link_id (+) = dl_ver.sgn_link_id
    ) 
    link_dtl, 
    (
        select scd.codes_cat_key, scd.CODES_CAT_KEY_DECODE
        from sgn_codes_hdr sch,
        sgn_codes_dtl scd
        where sch.CODES_CAT_DESC = 'SIZE'
        and scd.codes_cat_num = sch.codes_cat_num
    ) 
    size_dtl,
    SGN_HDR SH,
    SGN_CLUSTER sc,
    (
        SELECT START_TIME FROM SPF_VERSION_CONTROL WHERE JOB_TYPE = 'SIGN_BATCH' and FILE_NAME = upcoming_batch_name
    ) 
    last_run
    WHERE 
    link_dtl.SGN_CLUSTER_ID = SH.SGN_CLUSTER_ID
    and sh.sgn_size_cd = size_dtl.codes_cat_key (+)
    and ( sh.sgn_size_cd > param_var_RESTART_SIZE_ID OR
    (sh.sgn_size_cd = param_var_RESTART_SIZE_ID AND sh.sgn_id >= param_var_RESTART_SGN_ID) )
    and sh.DELETE_FL = 'N'
    and ( 
        param_emergency_flag = 'N'  
        or 
        (
            (SH.MODIFIED_DATE  between last_run.START_TIME AND sysdate) 
            or
            (SH.CREATE_DATE  between last_run.START_TIME AND sysdate) 
            or
            (link_dtl.LINK_MODIFY_DT  between last_run.START_TIME AND sysdate)
        )
    )
    AND sc.STR_NMB = sh.STR_NMB
    AND sc.DIV = sh.DIV
    AND sc.DPT_NMB = sh.DPT_NMB    
    AND sc.CLUSTER_ID = sh.CLUSTER_ID    
    AND sc.SGN_TYP_SUB_CLUSTER = sh.SGN_TYP_SUB_CLUSTER
    AND sc.DEAL_DESC_FL ='Y'
    AND sc.sgn_cluster_id = sh.sgn_cluster_id
    AND sh.SGN_TYP_ID <> 'PDF_002'
    order by sh.sgn_size_cd, link_dtl.srt_dt, sh.sgn_id, link_dtl.ver_nmb, link_dtl.dl_nmb;

-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR External_Sign_Kits_Curs(param_var_RESTART_SIZE_ID number, param_var_RESTART_SGN_ID number, param_emergency_flag varchar) 
is    
    select
    distinct sh.sgn_id,
    link_dtl.dl_nmb,
    link_dtl.ver_nmb,
    link_dtl.srt_dt,
    link_dtl.end_dt,
    to_char(link_dtl.srt_dt, 'MM/dd/yyyy') || '-' || external_sign_kits as Batch_Name,
    external_sign_kits_batch_name as batch_type,
    SH.STR_GRP_NUM,
    sh.sgn_size_cd,
    link_dtl.STR_COUNT as STR_COUNT
    from 
    (
        select dl_ver.ver_nmb, dl_ver.dl_nmb, dl_ver.srt_dt, dl_ver.end_dt, dl_ver.sgn_link_id,
        decode(se.link_exception_id, null, dl_ver.SGN_CLUSTER_ID, se.SGN_CLUSTER_ID) SGN_CLUSTER_ID,
        decode(se.link_exception_id, null, dl_ver.CREATE_DT, se.CREATE_DT) LINK_MODIFY_DT,
        dl_ver.STR_COUNT
        from 
        (
            select dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT, count(vs.str_nmb) as STR_COUNT
            from dl_HDR@VDL1_CAMPT.TRU.COM dh, 
        ver_HDR@VDL1_CAMPT.TRU.COM vh,  
        ver_str@VDL1_CAMPT.TRU.COM vs,
            sgn_deal_cluster_link sd 
            where    sd.dl_nmb = dh.dl_nmb 
            and      dh.dl_nmb = vh.dl_nmb
            AND      sd.dl_nmb = Vh.dl_nmb
            AND      dh.STS_CD in ('S','C')
            and to_char(vh.srt_dt, 'dd/MM/yy') in 
            (
                select to_char ((sysdate+ mod((7 + batch_day - setup_day), 7)) , 'dd/MM/yy')   
                from sgn_batch_setup where setup_day = to_char(sysdate,'D')
            )
        and vs.dl_nmb = vh.dl_nmb
        and vs.ver_nmb = vh.ver_nmb
        group by dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT
        ) 
        dl_ver, 
        sgn_deal_exception_link se
        where   dl_ver.ver_nmb = se.ver_nmb (+) 
        and     se.sgn_link_id (+) = dl_ver.sgn_link_id
    ) 
    link_dtl, 
    (
        select scd.codes_cat_key, scd.CODES_CAT_KEY_DECODE
        from sgn_codes_hdr sch,
        sgn_codes_dtl scd
        where sch.CODES_CAT_DESC = 'SIZE'
        and scd.codes_cat_num = sch.codes_cat_num
    ) 
    size_dtl,
    SGN_HDR SH,
    SGN_CLUSTER sc,
    (
        SELECT START_TIME FROM SPF_VERSION_CONTROL WHERE JOB_TYPE = 'SIGN_BATCH' and FILE_NAME = upcoming_batch_name
    ) 
    last_run
    WHERE 
    link_dtl.SGN_CLUSTER_ID = SH.SGN_CLUSTER_ID
    and sh.sgn_size_cd = size_dtl.codes_cat_key (+)
    and ( sh.sgn_size_cd > param_var_RESTART_SIZE_ID OR
    (sh.sgn_size_cd = param_var_RESTART_SIZE_ID AND sh.sgn_id >= param_var_RESTART_SGN_ID) )
    and sh.DELETE_FL = 'N'
    and ( 
        param_emergency_flag = 'N'  
        or 
        (
            (SH.MODIFIED_DATE  between last_run.START_TIME AND sysdate) 
            or
            (SH.CREATE_DATE  between last_run.START_TIME AND sysdate) 
            or
            (link_dtl.LINK_MODIFY_DT  between last_run.START_TIME AND sysdate)
        )
    )
    AND sc.STR_NMB = sh.STR_NMB
    AND sc.DIV = sh.DIV
    AND sc.DPT_NMB = sh.DPT_NMB    
    AND sc.CLUSTER_ID = sh.CLUSTER_ID    
    AND sc.SGN_TYP_SUB_CLUSTER = sh.SGN_TYP_SUB_CLUSTER
    AND sc.DEAL_DESC_FL ='Y'
    AND sc.sgn_cluster_id = sh.sgn_cluster_id
    AND sh.SGN_TYP_ID = 'PDF_002'
    order by sh.sgn_size_cd, link_dtl.srt_dt, sh.sgn_id, link_dtl.ver_nmb, link_dtl.dl_nmb;

-------------------------------------------------------------------------------------------------------------------------------------------------------

CURSOR Corrections_Curs_Sign_Batch(param_var_RESTART_SIZE_ID number, param_var_RESTART_SGN_ID number) is 
    select distinct
    sh.sgn_id,
    link_dtl.dl_nmb,
    link_dtl.ver_nmb,
    link_dtl.srt_dt,
    link_dtl.end_dt,
    trim(size_dtl.CODES_CAT_KEY_DECODE) size_name,
    correction_batch_name batch_type,
    SH.MODIFIED_DATE, 
    SH.CREATE_DATE,
    SH.STR_GRP_NUM,
    sh.sgn_size_cd,
    link_dtl.STR_COUNT as STR_COUNT
    from 
    (
        select dl_ver.ver_nmb, dl_ver.dl_nmb, dl_ver.srt_dt, dl_ver.end_dt, dl_ver.sgn_link_id,
        decode(se.link_exception_id, null, dl_ver.SGN_CLUSTER_ID, se.SGN_CLUSTER_ID) SGN_CLUSTER_ID,
        decode(se.link_exception_id, null, dl_ver.CREATE_DT, se.CREATE_DT) LINK_MODIFY_DT,
        dl_ver.STR_COUNT
        from (
            select dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT, count(vs.str_nmb) as STR_COUNT
            from 
            dl_HDR@VDL1_CAMPT.TRU.COM dh, 
            ver_HDR@VDL1_CAMPT.TRU.COM vh,  
        ver_str@VDL1_CAMPT.TRU.COM vs,
            sgn_deal_cluster_link sd 
            where    sd.dl_nmb = dh.dl_nmb 
            and      dh.dl_nmb = vh.dl_nmb
            AND      sd.dl_nmb = Vh.dl_nmb
            AND      dh.STS_CD in ('S','C')
            and to_date(sysdate, 'dd/MM/yy') between to_date(vh.SRT_DT, 'dd/MM/yy') and 
            to_date(vh.END_DT, 'dd/MM/yy')
        and vs.dl_nmb = vh.dl_nmb
        and vs.ver_nmb = vh.ver_nmb
        group by dh.dl_nmb,sd.sgn_link_id, vh.ver_nmb,sd.SGN_CLUSTER_ID, vh.srt_dt, vh.end_dt, 
            sd.CREATE_DT
        ) 
    dl_ver, 
    sgn_deal_exception_link se
    where   dl_ver.ver_nmb = se.ver_nmb (+) 
        and     se.sgn_link_id (+) = dl_ver.sgn_link_id) link_dtl, 
    (
        select scd.codes_cat_key, scd.CODES_CAT_KEY_DECODE
        from sgn_codes_hdr sch,
        sgn_codes_dtl scd
        where sch.CODES_CAT_DESC = 'SIZE'
        and scd.codes_cat_num = sch.codes_cat_num
    ) 
    size_dtl,
        (
        SELECT START_TIME FROM SPF_VERSION_CONTROL WHERE JOB_TYPE = 'SIGN_BATCH' and FILE_NAME = correction_batch_name
    ) 
    last_run,
    SGN_HDR SH,
    SGN_CLUSTER sc
    WHERE 
    link_dtl.SGN_CLUSTER_ID = SH.SGN_CLUSTER_ID
    and sh.sgn_size_cd = size_dtl.codes_cat_key (+)
    and ( sh.sgn_size_cd > param_var_RESTART_SIZE_ID OR
    (sh.sgn_size_cd = param_var_RESTART_SIZE_ID AND sh.sgn_id >= param_var_RESTART_SGN_ID) )
    and sh.DELETE_FL = 'N'
    and (
        (SH.MODIFIED_DATE  between last_run.START_TIME AND sysdate) 
        or
        (SH.CREATE_DATE  between last_run.START_TIME AND sysdate) 
        or
        (link_dtl.LINK_MODIFY_DT  between last_run.START_TIME AND sysdate)
    )    
    AND sc.STR_NMB = sh.STR_NMB
    AND sc.DIV = sh.DIV
    AND sc.DPT_NMB = sh.DPT_NMB    
    AND sc.CLUSTER_ID = sh.CLUSTER_ID    
    AND sc.SGN_TYP_SUB_CLUSTER = sh.SGN_TYP_SUB_CLUSTER
    AND sc.DEAL_DESC_FL ='Y'
    AND sc.sgn_cluster_id = sh.sgn_cluster_id
    order by sh.sgn_size_cd, sh.sgn_id, link_dtl.ver_nmb, link_dtl.dl_nmb;
-------------------------------------------------------------------------------------------------------------------------------------------------------

BEGIN


IF batch_type_to_execute = active_batch_name THEN
-------------------------------------------------------------------------------------------------------------------------------------------------------

var_start_sign_id := cast(start_sign_id_param as number);
var_end_sign_id := cast(end_sign_id_param as number);
var_batch_id := cast(batch_id_param as number);

-- CHECK IF THE BATCH ALREADY RAN FOR THE DAY
OPEN last_run_check(active_batch_name); 
fetch last_run_check into var_LAST_RUN_TIME, var_LAST_RUN_END_TIME;
IF last_run_check%NOTFOUND 
THEN
    var_LAST_RUN_TIME:=sysdate-1;
    var_LAST_RUN_END_TIME:=sysdate-1;
    insert into SPF_VERSION_CONTROL (start_time, end_time, JOB_TYPE, FILE_NAME) 
    values (var_LAST_RUN_TIME, var_LAST_RUN_TIME, 'SIGN_BATCH', active_batch_name);
    commit;
END IF;
CLOSE last_run_check;

-- CHECK IF THE RUN IS AN EMERGENCY RUN
var_LAST_RUN_START_TIME:= sysdate; 
IF to_char(var_LAST_RUN_END_TIME, 'MM/DD/YYYY') = to_char(SYSDATE, 'MM/DD/YYYY') 
THEN
    emergency_flag:='Y';
ELSE 
    emergency_flag:='N';
END IF;

-------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORE EACH SIGN ID IN THE ERR TABLE FOR RESTART LOGIC
OPEN batch_restart_check_for_size(active_batch_name);
fetch batch_restart_check_for_size into var_RESTART_SIZE_ID, var_RESTART_SGN_ID;
IF batch_restart_check_for_size%NOTFOUND 
THEN
    var_RESTART_SGN_ID:=0;
    var_RESTART_SIZE_ID:=0;
    INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
    values (err_id_seq.nextval,
    'N', var_RESTART_SGN_ID, SYSDATE, var_restart_batch||'_'||size_code_param||'_'||thread_number_for_size_param,
    var_RESTART_SIZE_ID||var_delimeter||active_batch_name );
    commit;
END IF;
CLOSE batch_restart_check_for_size;

-------------------------------------------------------------------------------------------------------------------------------------------------------

OPEN Active_Today_Curs_Sign_Batch(var_RESTART_SIZE_ID, var_RESTART_SGN_ID, emergency_flag);
LOOP -- 1st LOOP   
BEGIN -- 1st BEGIN
    var_BATCH_TYPE := '';
    FETCH Active_Today_Curs_Sign_Batch 
    INTO var_SGN_ID, var_DL_NMB, var_VER_NMB, var_SRT_DT, var_END_DT, var_BATCH_NAME, var_BATCH_TYPE, var_STR_GRP_NMB, new_size_code, new_store_count;
    EXIT WHEN 
        Active_Today_Curs_Sign_Batch%NOTFOUND;

    BEGIN -- 2nd BEGIN
         new_batch_name := var_BATCH_NAME;

        IF(var_batch_id = 0 or emergency_flag = 'Y') THEN 
            select decode(batch_id, null, 0, batch_id) into var_batch_id from (
            SELECT min(batch_id) as batch_id
            FROM SGN_BATCH_HDR sbh
            where to_date(RENDERING_DT, 'dd/MM/yy') = to_date(sysdate, 'dd/MM/yy')
            and BATCH_NAME = var_BATCH_NAME
            and BATCH_TYPE = var_BATCH_TYPE ) batch;
        END IF;

        IF(var_batch_id = 0 or (new_size_code <> old_size_code and emergency_flag = 'N' and (batch_id_param = '0' or batch_id_param = ''))) 
        THEN
            select to_char(spfbatchid_seq.NEXTVAL, '000000') 
            INTO var_batch_id from dual;                        
        END IF;    
        
        old_size_code := new_size_code;

        -- UPDATE THE SGN_ID FOR RESTART
        update ERR set err_cd = var_SGN_ID, err_dtl = new_size_code||var_delimeter||active_batch_name
        where trim(err_src) = var_restart_batch||'_'||size_code_param||'_'||thread_number_for_size_param
        and trim(substr(ERR_DTL, INSTR(err_dtl,var_delimeter)+length(var_delimeter))) = active_batch_name
        and to_date(err_tm, 'dd/MM/yy') = to_date(SYSDATE, 'dd/MM/yy');
        commit;

        new_sign_id := var_SGN_ID;
    
    IF new_sign_id = old_sign_id THEN
        IF new_store_count = old_store_count THEN
        process_store := 'N';
        ELSE
        process_store := 'Y';
        END IF;
    ELSE
        process_store := 'Y';
    END IF;

    old_store_count := new_store_count;
    old_sign_id := new_sign_id;

    IF process_store = 'Y' THEN
        -- FETCH ALL THE STORES FOR THE DEAL AND DEAL VERSION WHICH MATCH WITH THE STORE GROUP ADDED TO THE SIGN
        OPEN Cursor_Sign_Batch_Str(var_DL_NMB, var_VER_NMB, var_STR_GRP_NMB); 
        LOOP -- 2nd LOOP 
        FETCH Cursor_Sign_Batch_Str INTO var_STR_NMB;
        IF Cursor_Sign_Batch_Str%NOTFOUND THEN -- 1st MAJOR IF
            EXIT; 
        ELSE
            BEGIN 

            BEGIN
                INSERT INTO SGN_BATCH_HDR(BATCH_ID, BATCH_NAME, rENDERING_DT, BATCH_TYPE, STR_NMB)
                VALUES(var_batch_id , var_BATCH_NAME, sysdate , var_BATCH_TYPE, var_STR_NMB);
            EXCEPTION
                WHEN OTHERS THEN
                 err_msg := SQLERRM;
                 IF(err_msg not like '%unique%') THEN
                INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                END IF;
            END;

            BEGIN
               INSERT INTO SGN_BATCH_DTL (BATCH_ID, STR_NMB, SGN_ID, START_DT, END_DT)
               VALUES(var_batch_id, var_STR_NMB, var_SGN_ID, var_SRT_DT, var_END_DT);
            EXCEPTION
               WHEN OTHERS THEN
               err_msg := SQLERRM;
               IF(err_msg not like '%unique%') THEN
                  INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                  values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
               END IF;
                END;
            END; 
        END IF; 
        END LOOP ; 
        CLOSE Cursor_Sign_Batch_Str;
    ELSE
    IF(debug_mode = 'Y') THEN
         INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
            values (err_id_seq.nextval,
            'N', new_sign_id, SYSDATE, proc_name||size_code_param, 
            var_DL_NMB||var_delimeter||var_VER_NMB||var_delimeter||new_store_count||var_delimeter||active_batch_name );
            commit;
     END IF;       
       
    END IF;

    END; 
    EXCEPTION
        WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SQLERRM;
        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name||new_store_count, err_msg);
        
        IF Cursor_Sign_Batch_Str%ISOPEN THEN
            CLOSE Cursor_Sign_Batch_Str;
        END IF;
    
        COMMIT;

END; 
END LOOP; 
CLOSE Active_Today_Curs_Sign_Batch;  

END IF;


-------------------------------------------------------------------------------------------

IF batch_type_to_execute = put_up_today_batch_name THEN
-------------------------------------------------------------------------------------------------------------------------------------------------------
-- CHECK IF THE BATCH ALREADY RAN FOR THE DAY
OPEN last_run_check(put_up_today_batch_name); 
fetch last_run_check into var_LAST_RUN_TIME, var_LAST_RUN_END_TIME;
IF last_run_check%NOTFOUND 
THEN
    var_LAST_RUN_TIME:=sysdate-1;
    var_LAST_RUN_END_TIME:=sysdate-1;
    insert into SPF_VERSION_CONTROL (start_time, end_time, JOB_TYPE, FILE_NAME) 
    values (var_LAST_RUN_TIME, var_LAST_RUN_TIME, 'SIGN_BATCH', put_up_today_batch_name);
    commit;
END IF;
CLOSE last_run_check;

-- CHECK IF THE RUN IS AN EMERGENCY RUN
var_LAST_RUN_START_TIME:= sysdate; 
IF to_char(var_LAST_RUN_END_TIME, 'MM/DD/YYYY') = to_char(SYSDATE, 'MM/DD/YYYY') 
THEN
    emergency_flag:='Y';
ELSE 
    emergency_flag:='N';
END IF;

-------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORE EACH SIGN ID IN THE ERR TABLE FOR RESTART LOGIC
OPEN batch_restart_check(put_up_today_batch_name);
fetch batch_restart_check into var_RESTART_SIZE_ID, var_RESTART_SGN_ID;
IF batch_restart_check%NOTFOUND 
THEN
    var_RESTART_SGN_ID:=0;
    var_RESTART_SIZE_ID:=0;
    INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
    values (err_id_seq.nextval,
    'N', var_RESTART_SGN_ID, SYSDATE, var_restart_batch, var_RESTART_SIZE_ID||var_delimeter||put_up_today_batch_name );
    commit;
END IF;
CLOSE batch_restart_check;

-------------------------------------------------------------------------------------------------------------------------------------------------------

OPEN PutUp_Today_Curs_Sign_Batch(var_RESTART_SIZE_ID, var_RESTART_SGN_ID, emergency_flag);
LOOP -- 1st LOOP   
BEGIN -- 1st BEGIN
    var_BATCH_TYPE := '';
    FETCH PutUp_Today_Curs_Sign_Batch
    INTO var_SGN_ID, var_DL_NMB, var_VER_NMB, var_SRT_DT, var_END_DT, var_BATCH_NAME, var_BATCH_TYPE, var_STR_GRP_NMB, new_size_code, new_store_count;
    EXIT WHEN 
        PutUp_Today_Curs_Sign_Batch%NOTFOUND;

    BEGIN -- 2nd BEGIN
        new_batch_name := var_BATCH_NAME;

        IF(var_batch_id = 0 or emergency_flag = 'Y') THEN 
            select decode(batch_id, null, 0, batch_id) into var_batch_id from (
            SELECT min(batch_id) as batch_id
            FROM SGN_BATCH_HDR sbh
            where to_date(RENDERING_DT, 'dd/MM/yy') = to_date(sysdate, 'dd/MM/yy')
            and BATCH_NAME = var_BATCH_NAME
            and BATCH_TYPE = var_BATCH_TYPE ) batch;
        END IF;

    IF(var_batch_id = 0 or (new_size_code <> old_size_code and old_batch_name <> new_batch_name and emergency_flag = 'N')) 
        THEN
            select to_char(spfbatchid_seq.NEXTVAL, '000000') 
            INTO var_batch_id from dual;                        
        END IF;    
        
        old_size_code := new_size_code;
        old_batch_name := new_batch_name;

        -- UPDATE THE SGN_ID FOR RESTART
        update ERR set err_cd = var_SGN_ID, err_dtl = new_size_code||var_delimeter||put_up_today_batch_name
        where trim(err_src) = var_restart_batch 
        and trim(substr(ERR_DTL, INSTR(err_dtl,var_delimeter)+length(var_delimeter))) = put_up_today_batch_name
        and to_date(err_tm, 'dd/MM/yy') = to_date(SYSDATE, 'dd/MM/yy');
        commit;
        
    new_sign_id := var_SGN_ID;
    
    IF new_sign_id = old_sign_id THEN

            IF new_store_count = old_store_count THEN
            process_store := 'N';
            ELSE
            process_store := 'Y';
            END IF;
    ELSE
        process_store := 'Y';
        new_store_count := 0;
    END IF;

    old_store_count := new_store_count;
    old_sign_id := new_sign_id;


    IF process_store = 'Y' THEN
        -- FETCH ALL THE STORES FOR THE DEAL AND DEAL VERSION WHICH MATCH WITH THE STORE GROUP ADDED TO THE SIGN
        OPEN Cursor_Sign_Batch_Str(var_DL_NMB, var_VER_NMB, var_STR_GRP_NMB); 
        LOOP -- 2nd LOOP 
        FETCH Cursor_Sign_Batch_Str INTO var_STR_NMB;
        IF Cursor_Sign_Batch_Str%NOTFOUND THEN -- 1st MAJOR IF
            EXIT; 
        ELSE
            BEGIN -- 3rd BEGIN

                BEGIN
                    INSERT INTO SGN_BATCH_HDR(BATCH_ID, BATCH_NAME, rENDERING_DT, BATCH_TYPE, STR_NMB)
                    VALUES(var_batch_id , var_BATCH_NAME, sysdate , var_BATCH_TYPE, var_STR_NMB);
                EXCEPTION
                    WHEN OTHERS THEN
                     err_msg := SQLERRM;
                     IF(err_msg not like '%unique%') THEN
                        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                    END IF;
                END;

                     BEGIN
                        INSERT INTO SGN_BATCH_DTL (BATCH_ID, STR_NMB, SGN_ID, START_DT, END_DT)
                        VALUES(var_batch_id, var_STR_NMB, var_SGN_ID, var_SRT_DT, var_END_DT);
                             EXCEPTION
                        WHEN OTHERS THEN
                         err_msg := SQLERRM;
                         IF(err_msg not like '%unique%') THEN
                            INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                            values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                        END IF;
                     END;

                    END; -- 5th BEGIN ENDS
        END IF; -- 1st MAJOR IF ENDS
        END LOOP ; --2nd LOOP ENDS
        CLOSE Cursor_Sign_Batch_Str;
    ELSE
     IF(debug_mode = 'Y') THEN
        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
        values (err_id_seq.nextval,
        'N', new_sign_id, SYSDATE, proc_name||size_code_param, 
        var_DL_NMB||var_delimeter||var_VER_NMB||var_delimeter||new_store_count||var_delimeter||put_up_today_batch_name );
        commit;
     END IF; 
    END IF;
    --MOVED THE END OF 2nd BEGIN AFTER THE CLOSURE OF CURSOR
    END; -- 2nd BEGIN ENDS    
    EXCEPTION
        WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SQLERRM;
        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
        
        IF Cursor_Sign_Batch_Str%ISOPEN THEN
            CLOSE Cursor_Sign_Batch_Str;
        END IF;
    
        COMMIT;

END; --1st BEGIN ENDS
END LOOP; -- 1st LOOP ENDS
CLOSE PutUp_Today_Curs_Sign_Batch;  

update SPF_VERSION_CONTROL set end_time = sysdate where JOB_TYPE = proc_name and FILE_NAME = put_up_today_batch_name;
update SPF_VERSION_CONTROL set start_time = var_LAST_RUN_START_TIME where JOB_TYPE = proc_name and FILE_NAME = put_up_today_batch_name;

commit;

END IF;




-------------------------------------------------------------------------------------------

IF batch_type_to_execute = upcoming_batch_name THEN
-------------------------------------------------------------------------------------------------------------------------------------------------------
-- CHECK IF THE BATCH ALREADY RAN FOR THE DAY
OPEN last_run_check(upcoming_batch_name); 
fetch last_run_check into var_LAST_RUN_TIME, var_LAST_RUN_END_TIME;
IF last_run_check%NOTFOUND 
THEN
    var_LAST_RUN_TIME:=sysdate-1;
    var_LAST_RUN_END_TIME:=sysdate-1;
    insert into SPF_VERSION_CONTROL (start_time, end_time, JOB_TYPE, FILE_NAME) 
    values (var_LAST_RUN_TIME, var_LAST_RUN_TIME, 'SIGN_BATCH', upcoming_batch_name);
    commit;
END IF;
CLOSE last_run_check;

-- CHECK IF THE RUN IS AN EMERGENCY RUN
var_LAST_RUN_START_TIME:= sysdate; 
IF to_char(var_LAST_RUN_END_TIME, 'MM/DD/YYYY') = to_char(SYSDATE, 'MM/DD/YYYY') 
THEN
    emergency_flag:='Y';
ELSE 
    emergency_flag:='N';
END IF;

-------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORE EACH SIGN ID IN THE ERR TABLE FOR RESTART LOGIC
OPEN batch_restart_check(upcoming_batch_name);
fetch batch_restart_check into var_RESTART_SIZE_ID, var_RESTART_SGN_ID;
IF batch_restart_check%NOTFOUND 
THEN
    var_RESTART_SGN_ID:=0;
    var_RESTART_SIZE_ID:=0;
    INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
    values (err_id_seq.nextval,
    'N', var_RESTART_SGN_ID, SYSDATE, var_restart_batch, var_RESTART_SIZE_ID||var_delimeter||upcoming_batch_name );
    commit;
END IF;
CLOSE batch_restart_check;

-------------------------------------------------------------------------------------------------------------------------------------------------------

OPEN PrintUpcoming_Curs_Sign_Batch(var_RESTART_SIZE_ID, var_RESTART_SGN_ID, emergency_flag);
LOOP -- 1st LOOP   
BEGIN -- 1st BEGIN
    var_BATCH_TYPE := '';
    FETCH PrintUpcoming_Curs_Sign_Batch
    INTO var_SGN_ID, var_DL_NMB, var_VER_NMB, var_SRT_DT, var_END_DT, var_BATCH_NAME, var_BATCH_TYPE, var_STR_GRP_NMB, new_size_code, new_store_count;
    EXIT WHEN 
        PrintUpcoming_Curs_Sign_Batch%NOTFOUND;

    BEGIN -- 2nd BEGIN
        new_batch_name := var_BATCH_NAME;

        IF(var_batch_id = 0 or emergency_flag = 'Y') THEN 
            select decode(batch_id, null, 0, batch_id) into var_batch_id from (
            SELECT min(batch_id) as batch_id
            FROM SGN_BATCH_HDR sbh
            where to_date(RENDERING_DT, 'dd/MM/yy') = to_date(sysdate, 'dd/MM/yy')
            and BATCH_NAME = var_BATCH_NAME
            and BATCH_TYPE = var_BATCH_TYPE ) batch;
        END IF;

        IF(var_batch_id = 0 or (new_size_code <> old_size_code and old_batch_name <> new_batch_name and emergency_flag = 'N'))  
        THEN
            select to_char(spfbatchid_seq.NEXTVAL, '000000') 
            INTO var_batch_id from dual;                        
        END IF;    
        
        old_size_code := new_size_code;
        old_batch_name := new_batch_name;

        -- UPDATE THE SGN_ID FOR RESTART
        update ERR set err_cd = var_SGN_ID, err_dtl = new_size_code||var_delimeter||upcoming_batch_name
        where trim(err_src) = var_restart_batch 
        and trim(substr(ERR_DTL, INSTR(err_dtl,var_delimeter)+length(var_delimeter))) = upcoming_batch_name
        and to_date(err_tm, 'dd/MM/yy') = to_date(SYSDATE, 'dd/MM/yy');
        commit;
        
    new_sign_id := var_SGN_ID;
    
    IF new_sign_id = old_sign_id THEN

            IF new_store_count = old_store_count THEN
            process_store := 'N';
            ELSE
            process_store := 'Y';
            END IF;
    ELSE
        process_store := 'Y';
        new_store_count := 0;
    END IF;

    old_store_count := new_store_count;
    old_sign_id := new_sign_id;


    IF process_store = 'Y' THEN
        -- FETCH ALL THE STORES FOR THE DEAL AND DEAL VERSION WHICH MATCH WITH THE STORE GROUP ADDED TO THE SIGN
        OPEN Cursor_Sign_Batch_Str(var_DL_NMB, var_VER_NMB, var_STR_GRP_NMB); 
        LOOP -- 2nd LOOP 
        FETCH Cursor_Sign_Batch_Str INTO var_STR_NMB;
        IF Cursor_Sign_Batch_Str%NOTFOUND THEN -- 1st MAJOR IF
            EXIT; 
        ELSE
            BEGIN -- 3rd BEGIN

                BEGIN
                    INSERT INTO SGN_BATCH_HDR(BATCH_ID, BATCH_NAME, rENDERING_DT, BATCH_TYPE, STR_NMB)
                    VALUES(var_batch_id , var_BATCH_NAME, sysdate , var_BATCH_TYPE, var_STR_NMB);
                EXCEPTION
                    WHEN OTHERS THEN
                     err_msg := SQLERRM;
                     IF(err_msg not like '%unique%') THEN
                        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                    END IF;
                END;

                     BEGIN
                        INSERT INTO SGN_BATCH_DTL (BATCH_ID, STR_NMB, SGN_ID, START_DT, END_DT)
                        VALUES(var_batch_id, var_STR_NMB, var_SGN_ID, var_SRT_DT, var_END_DT);
                             EXCEPTION
                        WHEN OTHERS THEN
                         err_msg := SQLERRM;
                         IF(err_msg not like '%unique%') THEN
                            INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                            values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                        END IF;
                     END;

                    END; -- 5th BEGIN ENDS  
        END IF; -- 1st MAJOR IF ENDS
        END LOOP ; --2nd LOOP ENDS
        CLOSE Cursor_Sign_Batch_Str;
      ELSE
         IF(debug_mode = 'Y') THEN
           INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
           values (err_id_seq.nextval,
           'N', new_sign_id, SYSDATE, proc_name||size_code_param, 
           var_DL_NMB||var_delimeter||var_VER_NMB||var_delimeter||new_store_count||var_delimeter||upcoming_batch_name );
           commit;
         END IF;
    END IF;
    --MOVED THE END OF 2nd BEGIN AFTER THE CLOSURE OF CURSOR
    END; -- 2nd BEGIN ENDS    
    EXCEPTION
        WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SQLERRM;
        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
        
        IF Cursor_Sign_Batch_Str%ISOPEN THEN
            CLOSE Cursor_Sign_Batch_Str;
        END IF;
    
        COMMIT;

END; --1st BEGIN ENDS
END LOOP; -- 1st LOOP ENDS
CLOSE PrintUpcoming_Curs_Sign_Batch;  

update SPF_VERSION_CONTROL set end_time = sysdate where JOB_TYPE = proc_name and FILE_NAME = upcoming_batch_name;
update SPF_VERSION_CONTROL set start_time = var_LAST_RUN_START_TIME where JOB_TYPE = proc_name and FILE_NAME = upcoming_batch_name;

commit;

END IF;


-------------------------------------------------------------------------------------------

IF batch_type_to_execute = external_sign_kits_batch_name THEN
-------------------------------------------------------------------------------------------------------------------------------------------------------
-- CHECK IF THE BATCH ALREADY RAN FOR THE DAY
OPEN last_run_check(external_sign_kits_batch_name); 
fetch last_run_check into var_LAST_RUN_TIME, var_LAST_RUN_END_TIME;
IF last_run_check%NOTFOUND 
THEN
    var_LAST_RUN_TIME:=sysdate-1;
    var_LAST_RUN_END_TIME:=sysdate-1;
    insert into SPF_VERSION_CONTROL (start_time, end_time, JOB_TYPE, FILE_NAME) 
    values (var_LAST_RUN_TIME, var_LAST_RUN_TIME, 'SIGN_BATCH', external_sign_kits_batch_name);
    commit;
END IF;
CLOSE last_run_check;

-- CHECK IF THE RUN IS AN EMERGENCY RUN
var_LAST_RUN_START_TIME:= sysdate; 
IF to_char(var_LAST_RUN_END_TIME, 'MM/DD/YYYY') = to_char(SYSDATE, 'MM/DD/YYYY') 
THEN
    emergency_flag:='Y';
ELSE 
    emergency_flag:='N';
END IF;

-------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORE EACH SIGN ID IN THE ERR TABLE FOR RESTART LOGIC
OPEN batch_restart_check(external_sign_kits_batch_name);
fetch batch_restart_check into var_RESTART_SIZE_ID, var_RESTART_SGN_ID;
IF batch_restart_check%NOTFOUND 
THEN
    var_RESTART_SGN_ID:=0;
    var_RESTART_SIZE_ID:=0;
    INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
    values (err_id_seq.nextval,
    'N', var_RESTART_SGN_ID, SYSDATE, var_restart_batch, var_RESTART_SIZE_ID||var_delimeter||external_sign_kits_batch_name );
    commit;
END IF;
CLOSE batch_restart_check;

-------------------------------------------------------------------------------------------------------------------------------------------------------

OPEN External_Sign_Kits_Curs(var_RESTART_SIZE_ID, var_RESTART_SGN_ID, emergency_flag);
LOOP -- 1st LOOP   
BEGIN -- 1st BEGIN
    var_BATCH_TYPE := '';
    FETCH External_Sign_Kits_Curs
    INTO var_SGN_ID, var_DL_NMB, var_VER_NMB, var_SRT_DT, var_END_DT, var_BATCH_NAME, var_BATCH_TYPE, var_STR_GRP_NMB, new_size_code, new_store_count;
    EXIT WHEN 
        External_Sign_Kits_Curs%NOTFOUND;

    BEGIN -- 2nd BEGIN
        new_batch_name := var_BATCH_NAME;

        IF(var_batch_id = 0 or emergency_flag = 'Y') THEN 
            select decode(batch_id, null, 0, batch_id) into var_batch_id from (
            SELECT min(batch_id) as batch_id
            FROM SGN_BATCH_HDR sbh
            where to_date(RENDERING_DT, 'dd/MM/yy') = to_date(sysdate, 'dd/MM/yy')
            and BATCH_NAME = var_BATCH_NAME
            and BATCH_TYPE = var_BATCH_TYPE ) batch;
        END IF;

        IF(var_batch_id = 0 or (new_size_code <> old_size_code and old_batch_name <> new_batch_name and emergency_flag = 'N')) 
        THEN
            select to_char(spfbatchid_seq.NEXTVAL, '000000') 
            INTO var_batch_id from dual;                        
        END IF;    
        
        old_size_code := new_size_code;
        old_batch_name := new_batch_name;

        -- UPDATE THE SGN_ID FOR RESTART
        update ERR set err_cd = var_SGN_ID, err_dtl = new_size_code||var_delimeter||external_sign_kits_batch_name
        where trim(err_src) = var_restart_batch 
        and trim(substr(ERR_DTL, INSTR(err_dtl,var_delimeter)+length(var_delimeter))) = external_sign_kits_batch_name
        and to_date(err_tm, 'dd/MM/yy') = to_date(SYSDATE, 'dd/MM/yy');
        commit;
        
    new_sign_id := var_SGN_ID;
    
    IF new_sign_id = old_sign_id THEN
            IF new_store_count = old_store_count THEN
            process_store := 'N';
            ELSE
            process_store := 'Y';
            END IF;
    ELSE
        process_store := 'Y';
        new_store_count := 0;
    END IF;

    old_store_count := new_store_count;
    old_sign_id := new_sign_id;


    IF process_store = 'Y' THEN
        -- FETCH ALL THE STORES FOR THE DEAL AND DEAL VERSION WHICH MATCH WITH THE STORE GROUP ADDED TO THE SIGN
        OPEN Cursor_Sign_Batch_Str(var_DL_NMB, var_VER_NMB, var_STR_GRP_NMB); 
        LOOP -- 2nd LOOP 
        FETCH Cursor_Sign_Batch_Str INTO var_STR_NMB;
        IF Cursor_Sign_Batch_Str%NOTFOUND THEN -- 1st MAJOR IF
            EXIT; 
        ELSE
            BEGIN -- 3rd BEGIN

                BEGIN
                    INSERT INTO SGN_BATCH_HDR(BATCH_ID, BATCH_NAME, rENDERING_DT, BATCH_TYPE, STR_NMB)
                    VALUES(var_batch_id , var_BATCH_NAME, sysdate , var_BATCH_TYPE, var_STR_NMB);
                EXCEPTION
                    WHEN OTHERS THEN
                     err_msg := SQLERRM;
                     IF(err_msg not like '%unique%') THEN
                        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                    END IF;
                END;

                     BEGIN
                        INSERT INTO SGN_BATCH_DTL (BATCH_ID, STR_NMB, SGN_ID, START_DT, END_DT)
                        VALUES(var_batch_id, var_STR_NMB, var_SGN_ID, var_SRT_DT, var_END_DT);
                             EXCEPTION
                        WHEN OTHERS THEN
                         err_msg := SQLERRM;
                         IF(err_msg not like '%unique%') THEN
                            INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                            values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                        END IF;
                     END;

                    END; -- 5th BEGIN ENDS

        END IF; -- 1st MAJOR IF ENDS
        END LOOP ; --2nd LOOP ENDS
        CLOSE Cursor_Sign_Batch_Str;
      ELSE
         IF(debug_mode = 'Y') THEN
           INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
           values (err_id_seq.nextval,
           'N', new_sign_id, SYSDATE, proc_name||size_code_param, 
           var_DL_NMB||var_delimeter||var_VER_NMB||var_delimeter||new_store_count||var_delimeter||external_sign_kits_batch_name );
           commit;
         END IF;
    END IF;
    --MOVED THE END OF 2nd BEGIN AFTER THE CLOSURE OF CURSOR
    END; -- 2nd BEGIN ENDS    
    EXCEPTION
        WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SQLERRM;
        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
        
        IF Cursor_Sign_Batch_Str%ISOPEN THEN
            CLOSE Cursor_Sign_Batch_Str;
        END IF;
    
        COMMIT;

END; --1st BEGIN ENDS
END LOOP; -- 1st LOOP ENDS
CLOSE External_Sign_Kits_Curs;  

update SPF_VERSION_CONTROL set end_time = sysdate where JOB_TYPE = proc_name and FILE_NAME = external_sign_kits_batch_name;
update SPF_VERSION_CONTROL set start_time = var_LAST_RUN_START_TIME where JOB_TYPE = proc_name and FILE_NAME = external_sign_kits_batch_name;

commit;

END IF;

-------------------------------------------------------------------------------------------

IF batch_type_to_execute = correction_batch_name THEN
-------------------------------------------------------------------------------------------------------------------------------------------------------
-- CHECK IF THE BATCH ALREADY RAN FOR THE DAY
OPEN last_run_check(correction_batch_name); 
fetch last_run_check into var_LAST_RUN_TIME, var_LAST_RUN_END_TIME;
IF last_run_check%NOTFOUND 
THEN
    var_LAST_RUN_TIME:=sysdate-1;
    var_LAST_RUN_END_TIME:=sysdate-1;
    insert into SPF_VERSION_CONTROL (start_time, end_time, JOB_TYPE, FILE_NAME) 
    values (var_LAST_RUN_TIME, var_LAST_RUN_TIME, 'SIGN_BATCH', correction_batch_name);
    commit;
END IF;
CLOSE last_run_check;

-- CHECK IF THE RUN IS AN EMERGENCY RUN
var_LAST_RUN_START_TIME:= sysdate; 
IF to_char(var_LAST_RUN_END_TIME, 'MM/DD/YYYY') = to_char(SYSDATE, 'MM/DD/YYYY') 
THEN
    emergency_flag:='Y';
ELSE 
    emergency_flag:='N';
END IF;

-------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORE EACH SIGN ID IN THE ERR TABLE FOR RESTART LOGIC

-- STORE EACH SIGN ID IN THE ERR TABLE FOR RESTART LOGIC
OPEN batch_restart_check(correction_batch_name);
fetch batch_restart_check into var_RESTART_SIZE_ID, var_RESTART_SGN_ID;
IF batch_restart_check%NOTFOUND 
THEN
    var_RESTART_SGN_ID:=0;
    var_RESTART_SIZE_ID:=0;
    INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
    values (err_id_seq.nextval,
    'N', var_RESTART_SGN_ID, SYSDATE, var_restart_batch, var_RESTART_SIZE_ID||var_delimeter||correction_batch_name );
    commit;
END IF;
CLOSE batch_restart_check;

-------------------------------------------------------------------------------------------------------------------------------------------------------

OPEN Corrections_Curs_Sign_Batch(var_RESTART_SIZE_ID, var_RESTART_SGN_ID);
LOOP -- 1st LOOP   
BEGIN -- 1st BEGIN
    var_BATCH_TYPE := '';
        FETCH Corrections_Curs_Sign_Batch INTO var_SGN_ID, var_DL_NMB, var_VER_NMB, var_SRT_DT, var_END_DT, var_SIZE, 
                var_BATCH_TYPE, var_MODIFIED_DATE, var_CREATE_DATE, var_STR_GRP_NMB, new_size_code, new_store_count;
    EXIT WHEN 
        Corrections_Curs_Sign_Batch%NOTFOUND;

    BEGIN -- 2nd BEGIN
        
        IF(var_CREATE_DATE between var_LAST_RUN_TIME AND SYSDATE) THEN
            var_BATCH_NAME:= 'Additions' || '-' || var_SIZE;
        ELSE
            var_BATCH_NAME:= 'Corrections' || '-' || var_SIZE;
        END IF;

        new_batch_name := var_BATCH_NAME;

        IF(var_batch_id = 0 or emergency_flag = 'Y') THEN 
            select decode(batch_id, null, 0, batch_id) into var_batch_id from (
            SELECT min(batch_id) as batch_id
            FROM SGN_BATCH_HDR sbh
            where to_date(RENDERING_DT, 'dd/MM/yy') = to_date(sysdate, 'dd/MM/yy')
            and BATCH_NAME = var_BATCH_NAME
            and BATCH_TYPE = var_BATCH_TYPE ) batch;
        END IF;

    IF(var_batch_id = 0 or (new_size_code <> old_size_code and old_batch_name <> new_batch_name and emergency_flag = 'N')) 
        THEN
            select to_char(spfbatchid_seq.NEXTVAL, '000000') 
            INTO var_batch_id from dual;                        
        END IF;    
        
        old_size_code := new_size_code;
        old_batch_name := new_batch_name;

        -- UPDATE THE SGN_ID FOR RESTART
        update ERR set err_cd = var_SGN_ID, err_dtl = new_size_code||var_delimeter||correction_batch_name
        where trim(err_src) = var_restart_batch 
        and trim(substr(ERR_DTL, INSTR(err_dtl,var_delimeter)+length(var_delimeter))) = correction_batch_name
        and to_date(err_tm, 'dd/MM/yy') = to_date(SYSDATE, 'dd/MM/yy');
        commit;

    new_sign_id := var_SGN_ID;
    
    IF new_sign_id = old_sign_id THEN

            IF new_store_count = old_store_count THEN
            process_store := 'N';
            ELSE
            process_store := 'Y';
            END IF;
    ELSE
        process_store := 'Y';
        new_store_count := 0;
    END IF;

    old_store_count := new_store_count;
    old_sign_id := new_sign_id;


    IF process_store = 'Y' THEN
        -- FETCH ALL THE STORES FOR THE DEAL AND DEAL VERSION WHICH MATCH WITH THE STORE GROUP ADDED TO THE SIGN
        OPEN Cursor_Sign_Batch_Str(var_DL_NMB, var_VER_NMB, var_STR_GRP_NMB); 
        LOOP -- 2nd LOOP 
        FETCH Cursor_Sign_Batch_Str INTO var_STR_NMB;
        IF Cursor_Sign_Batch_Str%NOTFOUND THEN -- 1st MAJOR IF
            EXIT; 
        ELSE
            BEGIN -- 3rd BEGIN

                BEGIN
                    INSERT INTO SGN_BATCH_HDR(BATCH_ID, BATCH_NAME, rENDERING_DT, BATCH_TYPE, STR_NMB)
                    VALUES(var_batch_id , var_BATCH_NAME, sysdate , var_BATCH_TYPE, var_STR_NMB);
                EXCEPTION
                    WHEN OTHERS THEN
                     err_msg := SQLERRM;
                     IF(err_msg not like '%unique%') THEN
                        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                    END IF;
                END;

                     BEGIN
                        INSERT INTO SGN_BATCH_DTL (BATCH_ID, STR_NMB, SGN_ID, START_DT, END_DT)
                        VALUES(var_batch_id, var_STR_NMB, var_SGN_ID, var_SRT_DT, var_END_DT);
                             EXCEPTION
                        WHEN OTHERS THEN
                         err_msg := SQLERRM;
                         IF(err_msg not like '%unique%') THEN
                            INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
                            values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
                        END IF;
                     END;

                    END; -- 5th BEGIN ENDS
        
        END IF; -- 1st MAJOR IF ENDS
        END LOOP ; --2nd LOOP ENDS
        CLOSE Cursor_Sign_Batch_Str;
      ELSE
          IF(debug_mode = 'Y') THEN
              INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
               values (err_id_seq.nextval,
               'N', new_sign_id, SYSDATE, proc_name||size_code_param, 
               var_DL_NMB||var_delimeter||var_VER_NMB||var_delimeter||new_store_count||var_delimeter||correction_batch_name );
               commit;
          END IF;
    END IF;
    --MOVED THE END OF 2nd BEGIN AFTER THE CLOSURE OF CURSOR
    END; -- 2nd BEGIN ENDS

    
    EXCEPTION
        WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SQLERRM;
        INSERT INTO err (err_id, updt_fl, err_cd, err_tm, err_src, err_dtl)
        values (err_id_seq.nextval,'N', err_num, SYSDATE, proc_name, err_msg);
        
        IF Cursor_Sign_Batch_Str%ISOPEN THEN
            CLOSE Cursor_Sign_Batch_Str;
        END IF;
    
        COMMIT;

END; --1st BEGIN ENDS
END LOOP; -- 1st LOOP ENDS
CLOSE Corrections_Curs_Sign_Batch;  

--MOVED THIS PART OUTSIDE THE LOOP 
--DELETE DATA FOR PREVIOUS DATES

update SPF_VERSION_CONTROL set end_time = sysdate where JOB_TYPE = proc_name and FILE_NAME = correction_batch_name;
update SPF_VERSION_CONTROL set start_time = var_LAST_RUN_START_TIME where JOB_TYPE = proc_name and FILE_NAME = correction_batch_name;

commit;

END IF;

END ;