Difficult...will try and get you a script tomorrow.

Oh that would be very helpful. Yes I think the data are not normalized.

I'm trying to decide how to even design it, if I can use VB Scripts or lookups to the SQL tables. So- I'd love insight from you guys because you sleep/live/breathe this stuff.  

In case it's useful at all I've got 2 SQL views that helped me initially:

1. I created a union query that would show me the periods & whether they are open:

select audtorg, fscyear, 'Status1' as FP, case when fp.STATUS1 = 1 then 'Period1-STATUS1' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status2'as FP, case when fp.STATUS2 = 1 then 'Period2-STATUS2' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status3'as FP, case when fp.STATUS3 = 1 then 'Period3-STATUS3'else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status4'as FP, case when fp.STATUS4 = 1 then 'Period4-STATUS4' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status5'as FP, case when fp.STATUS5 = 1 then 'Period5-STATUS5' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg,fscyear, 'Status6'as FP, case when fp.STATUS6 = 1 then 'Period6-STATUS6' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status7'as FP, case when fp.STATUS7 = 1 then 'Period7-STATUS7' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status8'as FP, case when fp.STATUS8 = 1 then 'Period8-STATUS8' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg,fscyear, 'Status9'as FP, case when fp.STATUS9 = 1 then 'Period9-STATUS9' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status10'as FP, case when fp.STATUS10 = 1 then 'Period10-STATUS10' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg, fscyear, 'Status11'as FP, case when fp.STATUS11 = 1 then 'Period11-STATUS11' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

union all

select audtorg,fscyear, 'Status12'as FP, case when fp.STATUS12 = 1 then 'Period12-STATUS12' else '0' end as PeriodStatus
from CSFSCST as FP
where 1=1 and FSCYEAR = datepart(year,getdate()) and PGMID = 'AP'

2. I created another query to identify today's date and the corresponding period. But of course I don't care about 'today's date'  - I actually care about the document date. I was thinking I'd sub in 'document date' for the query, or compare it to today's date. 

select FC.FSCYEAR, FC.AUDTORG, CONVERT(char(8), GETDATE(), 112) as TodayDate,
case
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate1 and FC.enddate1 then 'Period1-STATUS1'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate2 and FC.enddate2 then 'Period2-STATUS2'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate3 and FC.enddate3 then 'Period3-STATUS3'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate4 and FC.enddate4 then 'Period4-STATUS4'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate5 and FC.enddate5 then 'Period5-STATUS5'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate6 and FC.enddate6 then 'Period6-STATUS6'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate7 and FC.enddate7 then 'Period7-STATUS7'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate8 and FC.enddate8 then 'Period8-STATUS8'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate9 and FC.enddate9 then 'Period9-STATUS9'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate10 and FC.enddate10 then 'Period10-STATUS10'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate11 and FC.enddate11 then 'Period11-STATUS11'
when CONVERT(char(8), GETDATE(), 112) BETWEEN FC.bgndate12 and FC.enddate12 then 'Period12-STATUS12'
else 'UnknownPeriod' End as TodayPeriod

from CSFSC as FC
where 1=1
and FC.FSCYEAR = datepart(year,getdate())
and FC.ACTIVE = 1

Actually, you can do it pretty easily...I just forgot about this function.

It involves using the AccpacIsPeriodOpen Function. What we do, is to test a date by looping until an open period is found. If the period isn't open then a month is added to the data and tested again. Obviously replace %ShipDate field reference.

Dim PostDate
Dim PeriodClosed

PostDate = %ShipDate
Do
  PeriodClosed = Not AccpacIsPeriodOpen("ACCTEST", CDate(PostDate))
  If PeriodClosed Then
    PostDate = DateAdd("d", 1, PostDate)
  End If
Loop While PeriodClosed

PostDate

This is amazing. I am happy. So helpful that you have this function built in. THANK YOU!!!