/*********************************EOMUPDT.SQL***********************************
**  
**  © 1999 & 2006 by Sungard Higher Education.  Not for web publication.  All                         **  rights  reserved
***********************************************************************************
**
**               EOMUPDT.SQL
**  End of Month Date Update Report/Process   
**    1.  Updates transaction date with new date on all docs 
**        awaiting approval
**    2.  Updates transaction date with new date on all docs incomplete
**    3.  If documents in deferred edit mode, then run fgbtrni to move out
**        of deferred edit mode.  That is why no sql is in here to handle
**        updates for deferred edit.
**    4.  Updates transaction date with new date on all docs in FGBTRNI
**    5.  Updates transaction date with new date on Invoices awaiting receipt 
**   for any document that exists prior to the parameter date.
**   This updates all documents that need to have their date changed to
**     move the transaction date from the period being closed to the new
**     period just to be opened.  It also updates the posting period from the 
**     old period to the new posting period and any place the budget period
**     may need to be updated.  
** --------------------------------------------------------------------
**  CHG    DATE       PROGRAMMER
**   01  17-JUN-1999  Deborah Colella
**       Correct original scripts, made y2k compliant
**     
** --------------------------------------------------------------------
**         PARAMETERS
**  1 - NEW_DATE entered in format MM/DD/YYYY
**  2 - POSTING_PERIOD entered in format 99
**  3 - BUDGET_PERIOD entered in format 99
**     
** --------------------------------------------------------------------
**        TABLES USED
**   FOBUAPP, FOBDINP, FPBREQH, FPRREQA, FPBPOHD, FPRPODA, FABINVH,
**   FARINVA, FGBJVCH, FGBJVCD, FGBTRNI,
**   FGBENCH, FGBENCD, FGBCSHH, FGBCSHD, FPBRCHD, FFBADJH, FFRADJA
**     
** --------------------------------------------------------------------
**        NOTES        
**  1.  Deferred editing code is not specified as running trni before
**      running this script will either complete or approve the docs.
**  2.  Documents for Stores Inventory have not been coded in this 
**      sql script.
**  3.  Documents for receiving and Invoices awaiting receipt have 
**      been coded in the event the U of M will use these functions
**      soon in the future, but they have been *arked out.  
**  4.  Blanket orders have not been coded.    
** **********************************************************************
** Audit Trail
**
** Date: 08*Sep*2006  Jim Beyer
** Updated format, edited notes and header, veried V7 table and field names.
*************************************************************************
**
**  This is reference code.  Test it in your test enviroment before using 
**  any of it in production.
**
******************************************************************************/

Set Show     Off  
Set Echo     Off  
Set Verify   Off   
Set Newpage  0    
Set Embedded Off   
Set Linesize 80 
Set Pagesize 56       
Set Space    1     
Set Feedback On   
Set Termout  On   

Clear Breaks
Clear Computes
Clear Sql
Clear Buffer
Btitle Off      
Spool eomupdt.rpt 

/* Update Purchase Requisitions Awaiting Approvals (posting period) */
Update   fprreqa 
   Set   FPRREQA_PERIOD              = '&&New_Period'
   Where FPRREQA_REQH_CODE In 
        (Select  FOBUAPP_DOC_CODE 
         From 
fobuapp, 
fpbreqh, 
fprreqa
         Where FOBUAPP_SEQ_NUM       = 1 
           And FPBREQH_CODE          = FOBUAPP_DOC_CODE 
           And FPBREQH_RQST_TYPE_IND = 'P' 
           And FPRREQA_REQH_CODE     = FPBREQH_CODE
           And Trunc(FPBREQH_TRANS_DATE) < To_date('&&New_date','MM/DD/YYYY'))
/ 
/* Update Purchase Requisitions Awaiting Approvals (Trans date) */
Update   fpbreqh 
   Set   FPBREQH_TRANS_DATE = To_Date('&&New_Date','MM/DD/YYYY')
   Where FPBREQH_CODE in 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, fpbreqh  
         Where FOBUAPP_SEQ_NUM           = 1 
           And FOBUAPP_DOC_CODE          = FPBREQH_CODE
           And FPBREQH_RQST_TYPE_IND     = 'P' 
           And Trunc(FPBREQH_TRANS_DATE) < To_date('&&New_Date','MM/DD/YYYY'))
/
/* Update Incomplete Purchase Requisitions (posting period) */
Update   fprreqa 
   Set   FPRREQA_PERIOD              = '&&New_Period'
   Where FPRREQA_REQH_CODE In 
        (Select  FPBREQH_CODE 
         From  fpbreqh
         Where Nvl(FPBREQH_COMPLETE_IND, 'N')  = 'N'
           And FPBREQH_RQST_TYPE_IND           = 'P' 
           And Trunc(FPBREQH_TRANS_DATE)  < To_date('&&New_Date','MM/DD/YYYY'))
/
/* Update Incomplete Purchase Requisitions (trans date) */
Update   fpbreqh 
   Set   FPBREQH_TRANS_DATE = To_date('&&New_Date','MM/DD/YYYY')
   Where Nvl(FPBREQH_COMPLETE_IND, 'N') = 'N'
   And Fpbreqh_Rqst_Type_Ind            = 'P' 
   And Trunc(FPBREQH_TRANS_DATE)        < To_date('&&new_date','MM/DD/YYYY')
/
/* Update Purchase Orders and Change Orders Awaiting Approvals (posting period) */
Update   fprpoda 
   Set   FPRPODA_PERIOD              = '&&new_period'
   Where FPRPODA.ROWID In 
        (Select  fprpoda.ROWID 
         From fobuapp, 
              fpbpohd, 
              fprpoda
         Where FOBUAPP_SEQ_NUM                   = 2 
           And FPBPOHD_CODE                      = fobuapp_doc_code 

           And Nvl(FPBPOHD_CHANGE_SEQ_NUM,'999') =
                                           Nvl(FOBUAPP_CHG_SEQ_NUM,'999')
           And FPRPODA_POHD_CODE                 = FPBPOHD_CODE
           And Nvl(FPRPODA_CHANGE_SEQ_NUM,'999') = 
                                           Nvl(FPBPOHD_CHANGE_SEQ_NUM,'999')
           And Trunc(FPBPOHD_TRANS_DATE)  < To_date('&&new_date','MM/DD/YYYY'))
/ 
/* Update Purchase Orders and Change Orders Awaiting Approvals (trans date) */
Update   fpbpohd 
   Set   FPBPOHD_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where fpbpohd.ROWID In 
        (Select  fpbpohd.ROWID 
         From fobuapp,
              fpbpohd  
         Where FOBUAPP_SEQ_NUM              = 2 
           And FPBPOHD_CODE                 = FOBUAPP_DOC_CODE 
           And Nvl(FPBPOHD_CHANGE_SEQ_NUM,'999') 
                                 = Nvl(FOBUAPP_CHG_SEQ_NUM,'999')
           And Trunc(FPBPOHD_TRANS_DATE)   < To_date('&&new_date','MM/DD/YYYY'))
/
/* Update Incomplete Purchase Orders and Change Orders (posting period) */
Update   fprpoda 
   Set   FPRPODA_PERIOD              = '&&new_period'
   Where fprpoda.ROWID In 
        (Select  fprpoda.rowid 
         From  fpbpohd, 
               fprpoda
         Where Nvl(FPBPOHD_COMPLETE_IND, 'N') = 'N'
         And   Nvl(FPBPOHD_BLANKET_IND,'N')   = 'N'
         And   FPRPODA_POHD_CODE              = FPBPOHD_CODE
         And   FPRPODA_CHANGE_SEQ_NUM         = FPBPOHD_CHANGE_SEQ_NUM
         And Trunc(FPBPOHD_TRANS_DATE)     < To_date('&&new_date','MM/DD/YYYY'))
/
/* Update Incomplete Purchase Orders and Change Orders (trans date) */
Update   fpbpohd
   Set   FPBPOHD_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where Nvl(FPBPOHD_COMPLETE_IND, 'N')     = 'N'
   And   Nvl(FPBPOHD_BLANKET_IND,'N') = 'N' 
   And Trunc(FPBPOHD_TRANS_DATE)      < To_date('&&new_date','MM/DD/YYYY')
/

** /* Update Incomplete Receiving documents (RCVD date) */
** Update   fpbrchd
**    Set   FPBRCHD_RCVD_DATE = To_date('&&new_date','MM/DD/YYYY')
**    Where Nvl(FPBRCHD_COMPLETE_IND, 'N')     = 'N'
**    And Trunc(FPBRCHD_RCVD_DATE)      < To_date('&&new_date','MM/DD/YYYY')
** /

/* Update Invoices or Credit Memos Awaiting Approvals (posting period) */
ttitle center 'Updated Posting Period on Inv or Credit Memos Awaiting Approval' SKIP 1
Update   farinva 
   Set   FARINVA_PERIOD              = '&&new_period'
   Where FARINVA_INVH_CODE In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fabinvh, 
              farinva
         Where FOBUAPP_SEQ_NUM       = 3 
           And FABINVH_CODE          = FOBUAPP_DOC_CODE 
           And FARINVA_INVH_CODE     = FABINVH_CODE
           And Trunc(FABINVH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Invoices or Credit Memos Awaiting Approvals (Due Date) */
ttitle center 'Updated Due Date on Inv or Credit Memos Awaiting Approval' SKIP 1
Update   fabinvh 
   Set   FABINVH_PMT_DUE_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where FABINVH_CODE In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fabinvh  
         Where FOBUAPP_SEQ_NUM           = 3 
           And FOBUAPP_DOC_CODE          = FABINVH_CODE
           And Trunc(FABINVH_PMT_DUE_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/
/* Update Invoices or Credit Memos Awaiting Approvals (trans date) */
ttitle center 'Updated Trans Date on Inv or Credit Memos Awaiting Approval' SKIP 1
Update   FABINVH 
   Set   FABINVH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where FABINVH_CODE In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
               fabinvh  
         Where FOBUAPP_SEQ_NUM           = 3 
           And FOBUAPP_DOC_CODE          = FABINVH_CODE
           And Trunc(FABINVH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete Invoices or Credit Memos (posting period) */
Update   farinva
   Set   FARINVA_PERIOD              = '&&new_period'
   Where FARINVA_INVH_CODE In 
        (Select  FABINVH_CODE 
         From  fabinvh
         Where Nvl(FABINVH_COMPLETE_IND,'N') = 'N'
         And   Nvl(FABINVH_CANCEL_IND, 'N') != 'Y'
         And Trunc(FABINVH_TRANS_DATE)     < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete Invoices or Credit Memos (due date) */
Update   fabinvh 
   Set   FABINVH_PMT_DUE_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where Nvl(FABINVH_COMPLETE_IND, 'N') = 'N'
   And   Nvl(FABINVH_CANCEL_IND, 'N') != 'Y'
   And Trunc(FABINVH_PMT_DUE_DATE)      < To_date('&&new_date','MM/DD/YYYY')
/

/* Update Incomplete Invoices or Credit Memos (trans date) */
Update   fabinvh 
   Set   FABINVH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where Nvl(FABINVH_COMPLETE_IND, 'N') = 'N'
   And   Nvl(FABINVH_CANCEL_IND, 'N') != 'Y'
   And Trunc(FABINVH_TRANS_DATE)        < To_date('&&new_date','MM/DD/YYYY')
/

 /* Update Invoices Awaiting Receipt(posting period) */
 Update   farinva
    Set   FARINVA_PERIOD              = '&&new_period'
    Where FARINVA_INVH_CODE In 
         (Select  FABINVH_CODE 
          From  fabinvh
          Where Nvl(FABINVH_COMPLETE_IND, 'N')  = 'R'
         And Trunc(FABINVH_TRANS_DATE)  < To_date('&&new_date','MM/DD/YYYY'))
 /

 /* Update Invoices Awaiting Receipt(due date) */
 Update   fabinvh 
    Set   FABINVH_PMT_DUE_DATE = To_date('&&new_date','MM/DD/YYYY')
    Where Nvl(FABINVH_COMPLETE_IND, 'N') = 'R'
   And Trunc(FABINVH_PMT_DUE_DATE)      < To_date('&&new_date','MM/DD/YYYY')
 /

 /* Update Invoices Awaiting Receipt(trans date) */
 Update   fabinvh 
    Set   FABINVH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
    Where Nvl(FABINVH_COMPLETE_IND, 'N') = 'R'
   And Trunc(FABINVH_TRANS_DATE)       < To_date('&&new_date','MM/DD/YYYY')
 /

/* Update Fixed Asset Adjustments Awaiting Approvals (posting period) */
Update   ffradja 
   Set   FFRADJA_PERIOD              = '&&new_period'
   Where ffradja_adjh_code In 
        (Select  fobuapp_doc_code 
         From fobuapp, 
              ffbadjh, 
              ffradja
         Where FOBUAPP_SEQ_NUM       = 60 
           And FFBADJH_CODE          = FOBUAPP_DOC_CODE 
           And FFRADJA_ADJH_CODE     = FFBADJH_CODE
           And Trunc(FFBADJH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Fixed Asset Adjustments Awaiting Approvals (trans date) */
Update   ffbadjh 
   Set   FFBADJH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where ffbadjh_code In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              ffbadjh  
         Where FOBUAPP_SEQ_NUM           = 60 
           And fobuapp_DOC_CODE          = FFBADJH_CODE
           And Trunc(FFBADJH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete Fixed Assets Adjustments (posting period) */
Update   ffradja 
   Set   FFRADJA_PERIOD              = '&&new_period'
   Where FFRADJA_ADJH_CODE In 
        (Select  FFBADJH_CODE 
         From  ffbadjh
         Where Nvl(FFBADJH_COMPLETE_IND,'N')  = 'N'
         And Trunc(FFBADJH_TRANS_DATE)  < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete Fixed Asset Adjustments (trans date) */
Update   ffbadjh 
   Set   FFBADJH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where Nvl(FFBADJH_COMPLETE_INd,'N') = 'N'
   And Trunc(FFBADJH_TRANS_DATE)        < To_date('&&new_date','MM/DD/YYYY')
/

/* Update Journal Vouchers Awaiting Approvals (posting period) */
Update   fgbjvcd 
   Set   FGBJVCD_POSTING_PERIOD       = '&&new_period'
   Where fgbjvcd_doc_num In 
        (Select  fobuapp_doc_code 
         From fobuapp, 
              fgbjvch
         Where FOBUAPP_SEQ_NUM      = 20 
           And FGBJVCH_DOC_NUM      = FOBUAPP_DOC_CODE 
           And Trunc(FGBJVCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Journal Vouchers Awaiting Approvals (budget period) */
Update   fgbjvcd 
   Set   FGBJVCD_BUDGET_PERIOD          = '&&bud_period'
   Where fgbjvcd_doc_num In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fgbjvch, 
              fgbjvcd
         Where FOBUAPP_SEQ_NUM     = 20 
           And FGBJVCH_DOC_NUM     = FOBUAPP_DOC_CODE 
           And FGBJVCD_DOC_NUM     = FGBJVCH_DOC_NUM
           And FGBJVCD_BUDGET_PERIOD Is Not Null
           And Trunc(FGBJVCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Journal Vouchers Awaiting Approvals (trans date) */
Update   fgbjvch 
   Set   FGBJVCH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where FGBJVCH_DOC_NUM In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fgbjvch  
         Where FOBUAPP_SEQ_NUM           = 20 
           And FOBUAPP_DOC_CODE          = FGBJVCH_DOC_NUM
           And Trunc(FGBJVCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete or Recurring Journal Vouchers (posting period) */
Update   fgbjvcd 
   Set   FGBJVCD_POSTING_PERIOD       = '&&new_period'
   Where FGBJVCD_DOC_NUM In 
        (Select  FGBJVCH_DOC_NUM 
         From fgbjvch
         Where Nvl(FGBJVCH_STATUS_IND,'I') != 'C'
         And Trunc(FGBJVCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Incomplete or Recurring Journal Vouchers (budget period) */
Update   fgbjvcd 
   Set   FGBJVCD_BUDGET_PERIOD          = '&&bud_period'
   Where FGBJVCD_DOC_NUM In 
        (Select  FGBJVCH_DOC_NUM 
         From fgbjvch, 
              fgbjvcd
         Where Nvl(FGBJVCH_STATUS_IND,'I') != 'C'
         And FGBJVCH_DOC_NUM               = FGBJVCD_DOC_NUM
         And FGBJVCD_BUDGET_PERIOD         is not null
         And Trunc(FGBJVCH_TRANS_DATE)     < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete or Recurring Journal Vouchers (trans date) */
Update   fgbjvch 
   Set   FGBJVCH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where Nvl(FGBJVCH_STATUS_IND,'I') != 'C'
         And Trunc(FGBJVCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
/

/* Update Encumbrances Awaiting Approvals (posting period) */
Update   fgbencd 
   Set   FGBENCD_POSTING_PERIOD       = '&&new_period'
   Where FGBENCD_NUM In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fgbench
         Where FOBUAPP_SEQ_NUM      = 25 
           And FGBENCH_NUM      = FOBUAPP_DOC_CODE 
           And Trunc(FGBENCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Encumbrances Awaiting Approvals (trans date) */
Update   fgbench 
   Set   FGBENCH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where FGBENCH_NUM In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fgbench  
         Where FOBUAPP_SEQ_NUM           = 25 
           And FOBUAPP_DOC_CODE          = FGBENCH_NUM
           And Trunc(FGBENCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete Encumbrances (posting period) */
Update   fgbencd 
   Set   FGBENCD_POSTING_PERIOD       = '&&new_period'
   Where FGBENCD_NUM In 
        (Select  FGBENCH_NUM 
         From fgbench
         Where Nvl(FGBENCH_STATUS,'I') != 'C'
         And Trunc(FGBENCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Incomplete Encumbrances (trans date) */
Update   fgbench 
   Set   FGBENCH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where Nvl(FGBENCH_STATUS,'I') != 'C'
         And Trunc(FGBENCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
/

/* Update Direct Cash Receipts Awaiting Approvals (posting period) */
Update   fgbcshd 
   Set   FGBCSHD_POSTING_PERIOD       = '&&new_period'
   Where FGBCSHD_DOC_NUM In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fgbcshh
         Where FOBUAPP_SEQ_NUM      = 80 
           And FGBCSHH_DOC_NUM      = FOBUAPP_DOC_CODE 
           And Trunc(FGBCSHH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Direct Cash Receipts Awaiting Approvals (trans date) */
Update   fgbcshh 
   Set   FGBCSHH_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where FGBCSHH_DOC_NUM In 
        (Select  FOBUAPP_DOC_CODE 
         From fobuapp, 
              fgbcshh  
         Where FOBUAPP_SEQ_NUM           = 80 
           And FOBUAPP_DOC_CODE          = FGBCSHH_DOC_NUM
           And Trunc(FGBCSHH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Incomplete Direct Cash Receipts (posting period) */
Update   fgbcshd 
   Set   FGBCSHD_POSTING_PERIOD       = '&&new_period'
   Where FGBCSHD_DOC_NUM In 
        (Select  FGBCSHH_DOC_NUM 
         From fgbcshh
         Where Nvl(FGBCSHH_STATUS_IND,'I') != 'C'
         And Trunc(FGBCSHH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY'))
/ 

/* Update Incomplete Direct Cash Receipts (trans date) */
Update   fgbcshh 
   Set   fgbcshh_trans_date = To_date('&&new_date','MM/DD/YYYY')
   Where Nvl(FGBCSHH_STATUS_IND,'I') != 'C'
         And Trunc(FGBCSHH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
/

/* Update transactions sitting In FGBTRNI (budget period)*/
Update   fgbtrni 
   Set   FGBTRNI_BUDGET_PERIOD          = '&&bud_period'
   Where FGBTRNI.ROWID In
        (Select  FGBTRNI.ROWID 
         From fgbtrni
         Where FGBTRNI_BUDGET_PERIOD         is not null
         And Trunc(FGBTRNI_TRANS_DATE)     < To_date('&&new_date','MM/DD/YYYY'))
/

/* Update Transactions sitting In FGBTRNI (trans date) */
Update   fgbtrni 
   Set   FGBTRNI_TRANS_DATE = To_date('&&new_date','MM/DD/YYYY')
   Where Trunc(FGBTRNI_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
/

Spool Off