title: SQL fiscal periods over multiple years
body: <p>I am writing a command for Crystal reports in Oracle SQL Dev and i have ran into a problem. My User wants to have parameters that allow them to use a starting year/period in the format 'yyyymm' and ending parameter the same. The problem that i am occuring is that using these param they also want to go from say fiscal year 2012 to 2013 and period 10 (from fiscal year 2012) to period 06 ( from fiscal year 2013) how to setup my command to deal with this? Here is some of the code im currently working on <strong>Tables,views,APIs,etc have had their names changed for security purposes</strong>: </p>
  
  <pre><code>SELECT SOO.PART_NO,
     AP.ACCOUNTING_YEAR,
     AP.ACCOUNTING_PERIOD,
     INVENTORY_PART_API.GET_DESCRIPTION(SOO.CONTRACT, SOO.PART_NO)         AS PART_DESCRIPTION,
     SUM(SO.QTY_COMPLETE)                                                           AS QTY_COMP,
     MAX(PART_COST_API.GET_TOTAL_ACCUM_COST(SOO.CONTRACT,SOO.PART_NO,'1','*','*'))  AS TOTAL_ACCUM_COST,       
     MAX(ORA_DATABASE_NAME)                                                         AS ORA_DATABASE_NAME
  FROM SHOP_ORDER_OPERATION_TAB SOO
  LEFT JOIN ACCOUNTING_PERIOD_TAB AP
     ON SOO.CONTRACT = '09'
     AND SOO.OPER_STATUS_CODE = '90'
     AND SOO.CONTRACT = AP.COMPANY
     AND SOO.LAST_ACTIVITY_DATE BETWEEN AP.DATE_FROM AND AP.DATE_UNTIL 
  LEFT JOIN SHOP_ORD SO
     ON SOO.CONTRACT = '09'
     AND SOO.OPER_STATUS_CODE = '90'
     AND SO.ORDER_NO = SOO.ORDER_NO
     AND SO.RELEASE_NO = SOO.RELEASE_NO
     AND SO.SEQUENCE_NO = SOO.SEQUENCE_NO
     AND SO.CONTRACT = SOO.CONTRACT
  WHERE SOO.CONTRACT = '09'
     AND SOO.OPER_STATUS_CODE = '90'
     AND NVL(AP.ACCOUNTING_PERIOD,0) BETWEEN SUBSTR('201205',5,2) AND SUBSTR('201306',5,2) 
     AND NVL(AP.ACCOUNTING_YEAR,0) BETWEEN 2012 AND 2013                
  GROUP BY SOO.CONTRACT, 
     SOO.PART_NO ,
     AP.ACCOUNTING_YEAR,
     AP.ACCOUNTING_PERIOD
  ORDER BY 
     AP.ACCOUNTING_PERIOD ASC,
     AP.ACCOUNTING_YEAR ASC,
    SOO.PART_NO ASC;
  </code></pre>