/*
** NAME:           fiscal_periods.sql
** PURPOSE:        Creates fiscal year Records and fiscal period Records
** TABLES UPDATED: FTVFSYR, FTVFSPD
**
** Internal Script * Minimal QA Testing * Validate results with your
**   Period start and end dates are at month bounderies. 
**   Audit mode does not update tables.
**
**   Alter the Declare Section constants to determine the starting
**   and ending year. The first fiscal year is two digit start_year
**   plus one: a 1979 start year yields a 80 fiscal year code.
**
**    Note that table constraints prevent duplicates if periods
**    or years are inadvertently duplicated.
**
**  Output is spooled to the file: fiscal_periods.LST
**********************************************************************
** AUDIT TRAIL: 5.0.0
** Gregg Shadel  29*MAR*2001
** 1. This script updates the values of FTVFSYR and    ** GHS 03/29/01
**    FTVFSPD tables to add new fiscal years and periods
** Jim Beyer 09.Sep.2006
** Updated notes, reFormatted, reviewed for V7 compliance.
**********************************************************************
** © 2001, 2006 Sungard HE.  All rights reserved.
* AUDIT TRAIL End
*/

Set serveroutput on size 100000
Set ver off
Spool fiscal_periods

Declare
  **************** Variables used as parameters
  USER_ID VARCHAR2(30)   := 'FIXED_ASSETS'; ** If null, USER is used;
  UPDATE_MODE VARCHAR2(1):= 'A';      ** A For audit, U For update
  COAS VARCHAR2(1)       := 'U';      ** COAS Code to use
  FSYR_START_YR NUMBER   := 1979;     ** Year For start of first fiscal year
  FSYR_End_YR   NUMBER   := 1980;     ** Year For start of last fiscal year
  PD_START   VARCHAR2(6) := '01*JUL'; ** Day and Month of first period start
  PD_InCR    NUMBER      := 1;        ** Number of months between periods
  PD_EOQ_InD NUMBER      := 3;        ** Number of periods between EOQ_InD = 'Y'    
Records
  PRD_STATUS VARCHAR2(1) := 'N';      ** FTVFSPD_PRD_STATUS_InD Default
  EOY_InD VARCHAR2(1)    := 'N';      ** FTVFSYR_EOY_ACCR_STATUS_InD
  **************** Variables used internally in the script
  st_date DATE;        ** Fiscal Year start date
  end_date DATE;       ** Fiscal Year end date
  fsyr VARCHAR2(2);    ** Fiscal Year Code
  pd_per_yr NUMBER;    ** Number of periods per year
  pd_last VARCHAR2(2); ** Last Period code For ftvfsyr
  pd_code VARCHAR2(2); ** Number of the current period
  pd_st_date DATE;     ** Start date of period
  pd_end_date DATE;    ** End date of period
  pd_eoq VARCHAR2(1);  ** is period an EOQ period

  **************** Procedure Declaraction Section
  Procedure p ( s VARCHAR2 ) IS
    Begin
       dbms_output.put_line(s);
    End; ** p
  **************** Body of Script; fiscal_periods.sql

Begin
  If LENGTH(USER_ID) = 0 
    Then USER_ID := USER; 
End If;

  p ('User: ' || USER_ID || '  Date: ' || to_char(sysdate,'DD*MON*YYYY HH24:MM:SS'));

  If update_mode = 'U' Then
     p(' =====  UPDATE MODE ======');
  Else
     p(' =====  AUDIT MODE  ======');
  End If;

  pd_per_yr := 12 / PD_InCR;

  pd_last := ltrim(to_char(pd_per_yr,'09'));

  For yr In FSYR_START_YR .. FSYR_End_YR Loop

     fsyr := substr(to_char(yr+1),3,2); ** watch leading space
     st_date := to_date(PD_START || '*' || to_char(yr));

     end_date := last_day(add_months(st_date,11));

     p ( 'Fiscal Year: ' || fsyr || '   Start: ' ||
         to_char(st_date,'DD*MON*YYYY') || '   End: ' ||
         to_char(end_date,'DD*MON*YYYY') || '  Last PD: ' ||
         pd_last || '  EOY: ' || EOY_InD);

     If update_mode = 'U' 
     Then
        Begin
          InSERT InTO FTVFSYR (ftvfsyr_coas_code,
              ftvfsyr_fsyr_code, ftvfsyr_activity_date,
              ftvfsyr_user_id, ftvfsyr_start_date, ftvfsyr_end_date,
              ftvfsyr_last_period, ftvfsyr_eoy_accr_status_ind)
          VALUES (COAS, fsyr, sysdate, USER_ID, st_date, end_date,
              pd_last, EOY_InD);
        Exception When OTHERS Then
           p('... Error: Could not create FTVFSYR Fiscal Year Record');
        End;
     End If;

     pd_st_date  := st_date;

     For pd In 1 .. pd_per_yr Loop
        pd_code := ltrim(to_char(pd,'09'));

        pd_end_date := last_day(pd_st_date);

        If MOD(pd,PD_EOQ_InD) = 0 
        Then
           pd_eoq := 'Y';
        Else
           pd_eoq := 'N';
        End If;

        p( '  Pd: ' || pd_code || '   Start: '  ||
            to_char(pd_st_date,'DD*MON*YYYY') || '  End: ' ||
            to_char(pd_end_date,'DD*MON*YYYY') ||
            '   PRD: ' || PRD_STATUS || '  EOQ: ' || pd_eoq );

        If update_mode = 'U' 
        Then
           Begin
             Insert Into ftvfspd 
                (FTVFSPD_COAS_CODE, 
                FTVFSPD_FSYR_CODE,
                FTVFSPD_FSPD_CODE,
                FTVFSPD_ACTIVITY_DATE, 
                FTVFSPD_USER_ID,
                FTVFSPD_PRD_START_DATE,  
                FTVFSPD_PRD_END_DATE,
                FTVFSPD_PRD_STATUS_IND, 
                FTVFSPD_EOQ_IND)

             Values(COAS, 
                    fsyr, 
                    pd_code, 
                    sysdate, 
                    USER_ID,  
                    pd_st_date, 
                    pd_end_date,
                    PRD_STATUS, 
                    pd_eoq);
 
             Exception When OTHERS 
             Then
               p('... Error: Could not create FTVFSPD Fiscal Period Record');
             End;

        End If;

        ** Prepare For next fiscal period
        pd_st_date := add_months(pd_st_date, PD_InCR);

     End Loop; ** Period Loop

  End Loop; ** Fiscal Year Loop

  Commit;

End; ** End of fiscal_periods.sql
/

Spool off