Generate

The EXECUTE_GENERATE_BUSPLAN_HORIZON program generated the publish horizon start and end dates for each business plan in scope as follows:

Logs a new task record for the current business plan using this task_name format: <Current Business Plan> - "GENERATE"

Sorts plans in the <Current Business Plan> based on plan_category in the ascending order.

SELECT  
    FROM    SZ_BUSINESS_PLAN_DETAILS
    WHERE   business_plan_id = ID of <Current Business Plan>
    ORDER BY business_plan_id, plan_category, sequence ASC

Determines the logic to derive <Now> for a given <Plan> as follows:

<Now> =         SELECT  COALESCE(plan_start_date, plan_publish_time) 
                    FROM    SZ_PLAN_PUBLISH_LOG
                    WHERE   plan_name = <Plan>.plan_name
                    AND     plan_category = <Plan>.plan_category

    If <Now> cannot be retrieved from Plan Publish Log, then

    <Now> = Today

Determines the logic to retrieve the existing <Horizon Start Date> and <Horizon End Date> dates for a given <Plan>, if the horizon dates have already been generated. The logic is as follows:

   <Horizon Start Date> =  SELECT  publish_horizon_start_date 
                            FROM    SZ_BUSINESS_PLAN_PUBLISH_HORIZON
                            WHERE   plan_name = <Plan>.plan_name
                            AND     plan_category = <Plan>.plan_category

    <Horizon End Date> =    SELECT  publish_horizon_end_date 
                            FROM    SZ_BUSINESS_PLAN_PUBLISH_HORIZON
                            WHERE   plan_name = <Plan>.plan_name
                            AND     plan_category = <Plan>.plan_category

Determines the <Latest Plan Log> and <Plan Calendar> for the <Current Plan> as follows:

<Latest Plan Log> =     SELECT 
                            FROM    SZ_PLAN_PUBLISH_LOG
                            WHERE   plan_name = <Current Plan>
                            AND     plan_category = <Current Plan>.plan_category
                            AND     plan_publish_time = MAX (plan_publish_time) across plan_name and plan_category


    <Plan Calendar> =       SELECT 
                            FROM    SZ_SCP_CALENDAR
                            WHERE   calendar_id = <Latest Plan Log>.scp_calendar_id

Determines <Previous Plan> and <Next Plan> for the <Current Plan> in <Current Business Plan> as follows:

<Previous Plan> =       SELECT  TOP 1
                            FROM    SZ_BUSINESS_PLAN_DETAILS
                            WHERE   business_plan_id = ID of <Current Business Plan>
                            AND     plan_category = <Current Plan>.plan_category
                            AND     sequence < <Current Plan>.sequence
                            ORDER BY business_plan_id, sequence DESC   


    <Next Plan> =           SELECT  TOP 1
                            FROM    SZ_BUSINESS_PLAN_DETAILS
                            WHERE   business_plan_id = ID of <Current Business Plan>
                            AND     plan_category = <Current Plan>.plan_category
                            AND     sequence > <Current Plan>.sequence
                            ORDER BY business_plan_id, sequence ASC

Determines <Horizon Start Date> for the <Current Plan> in <Current Business Plan> as follows:

If <Previous Plan> exists then
        Retrieve <Horizon End Date> for <Previous Plan> from SZ_BUSINESS_PLAN_PUBLISH_HORIZON
        <Current Plan>.Horizon Start Date = <Previous Plan>.Horizon End Date + 1 day  

    Else If <Plan Calendar> exists for <Current Plan>
        <Plan Calendar>.Current Period = Period in <Plan Calendar> for <Current Plan> where <Now> falls into
        <Current Plan>.Horizon Start Date = Start Date of <Plan Calendar>.Current Period

        Note: If Current Period cannot be located in <Plan Calendar> for <Current Plan>, the program continues as if <Plan Calendar> does not exist for <Current Plan>.  

    Else 
        <Current Plan>.Horizon Start Date = <Now>

    End if

Determines <Horizon End Date> for the <Current Plan> in <Current Business Plan> as follows:

If <Next Plan> exists and If <Plan Calendar> exists for <Next Plan> then 
        <Rolling Period> = Period in <Plan Calendar> for <Next Plan> where <Now> 
                            + <Current Plan>.Horizon Duration - 1 day falls into
        <Rolling Date>  = Date of the <Current Plan>.Rolling Day Sequence'th occurence of 
                            <Current Plan>.Rolling Day in <Rolling Period>

        If <Now> + <Current Plan>.Horizon Duration - 1 Day >= <Rolling Date> then
            <Rolling Period> = <Rolling Period> + 1 Period, where additional period is in the <Plan Calendar> for <Next Plan>

        End if

        <Current Plan>.Horizon End Date = <Rolling Period>.Period End Date

        Note: If Rolling Period cannot be located in <Plan Calendar> for <Next Plan>, the program continues as if <Plan Calendar> does not exist for <Next Plan>.  


    Else If <Plan Calendar> exists for <Current Plan>
        <Rolling Period> = Period in <Plan Calendar> for <Current Plan> where <Now> 
                            + <Current Plan>.Horizon Duration - 1 day falls into

        <Current Plan>.Horizon End Date = <Rolling Period>.Period End Date

        Note: If Rolling Period cannot be located in <Plan Calendar> for <Current Plan>, the program continues as if <Plan Calendar> does not exist for <Current Plan>.  

    Else 
        <Current Plan>.Horizon End Date = <Now> + <Current Plan.Horizon Duration> - 1 Day

    End if

Inserts the generated horizon start and end dates for the current plan in the current business plan into the SZ_BUSINESS_PLAN_PUBLISH_HORIZON table and commits the changes.
Note: If there are more plans to process in the current business plan, the logic repeats from point 3 to 9.
Completes the task log record with the actual status once all plans in <Current Business Plan> are processed.
Completes the task log record with the actual status once Business Plans in scope are processed.