/************************************************************************
**              EOMRPT.SQL
** End of Month Report 
**     1.  Reports on all documents awaiting approval
**     2.  Reports on all documents incomplete
**     3.  Reports on all documents in deferred edit
**     4.  Reports on all documents in FGBTRNI
**     5.  Reports on Invoices awaiting receipt 
**  for any document that exists prior to the parameter date.
**  This will aid in determining which documents need to be processed
**  or have their date changed before we close the period at month end.
**********************************************************************
** CHG    DATE       PROGRAMMER
**  01  11-JUN-1999  Deborah Colella
**      Correct original scripts, made y2k compliant
**
**  02  8-Sep-2006	Jim Beuer
**		Modified header.  Added © statement, migrated some notes
**  	to the blocks impacted.  Reformatted to reference pack stAndard
**	verified release 7.x data fields.
**    
**********************************************************************
**        PARAMETERS
** 1 - NEW DATE entered in format MM/DD/YYYY
**    
**********************************************************************
**       TABLES USED
**  fobuapp, fobdinp, fpbreqh, fpbpohd, fabinvh, fgbjvch, fgbtrni,
**  fgbench, fgbcshh, fpbrchd, ffbadjh
**    
**********************************************************************
**  
**	© 1999, 2007 Sungard Higher Education.  All rights reserved
**	Not for web publication
**
**********************************************************************
**       NOTES        
**
**  Some of the report sections are commented out.  These carry comments 
**  explaining what they do.  The developer is left the option of adding 
**  these to the reporting system if desired.
**
**  Note that this is example code and should be run in test prior to any
**  deployment 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      

Col User_Id      Head 'User Id'       For  A15
Col Orig_User    Head 'Orig User Id'  For  A15
Col Doc_Code     Head 'Document Code' For  A15          
Col Chg          Head 'Change Num'    For  999          
Col Amt          Head 'Doc Amt'                  
Col A_Date       Head 'Actvy Date'    For  A11         
Col Tran_Date    Head 'Trans Date'    For  A11          
Col Due_Date     Head 'Due Date'      For  A11          
Col Inv_Date     Head 'Inv Date'      For  A11          
Col Po_Date      Head 'Po Date'       For  A11          
Col Rqst_Date    Head 'Rqst Date'     For  A11          
Col Rcvd_Date    Head 'Rcvd Date'     For  A11          
Col Cplt         Head 'Cmplt'         For  A5          
Col Cncl         Head 'Cancl'         For  A5          
Col Type         Head 'Type'          For  A4          

Spool eomqry.rpt

/* Select Purchase Requisitions  Awaiting Approvals        */
Ttitle Center 'Purchase Requisitions Awaiting Approval' SKIP 1

Select FOBUAPP_USER_CODE USER_ID,
       FOBUAPP_DOC_CODE  DOC_CODE,
       FOBUAPP_DOC_AMT AMT,
       FOBUAPP_ORIG_USER ORIG_USER,
       To_char(FOBUAPP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
       To_char(FPBREQH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
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')
Order By
       FOBUAPP_USER_CODE,
       FOBUAPP_ORIG_USER,
       FOBUAPP_ACTIVITY_DATE                 
/

REM /* Select Requisitions Awaiting Deferred Edit    */
REM Ttitle Center 'Requisitions Awaiting Deferred Edit' SKIP 1
REM Select FPBREQH_USER_ID   USER_ID,
REM       FOBDINP_DOC_NUM   DOC_CODE,
REM       To_char(FOBDINP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
REM       To_char(FPBREQH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
REM From   fobdinp, fpbreqh
REM Where
REM       FOBDINP_SEQ_CODE = 1 And
REM       FOBDINP_DOC_NUM  = FPBREQH_CODE And
REM       fpbreqh_rqst_type_ind = 'P'  And                 
REM       Trunc(FPBREQH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
REM Order By
REM        FPBREQH_USER_ID ,
REM       FPBREQH_CODE                   
REM /

/* Select Incomplete Requisitions    */
Ttitle Center 'Incomplete Requisitions' SKIP 1
Select  
       FPBREQH_CODE DOC_CODE,
       TO_CHAR(FPBREQH_REQD_DATE,'DD-MON-YYYY')  RQST_DATE,                     
       TO_CHAR(FPBREQH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE,
       FPBREQH_COMPLETE_IND  CPLT,
       FPBREQH_CANCEL_IND    CNCL,
       FPBREQH_USER_ID       USER_ID
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')
Order By
       FPBREQH_USER_ID,
       FPBREQH_CODE
/                      

/* Select Purchase Orders And Change Purchase Orders Awaiting Approvals */
Ttitle Center 'Purhcase Orders Awaiting Approval' SKIP 1
Select FOBUAPP_USER_CODE   USER_ID,
       FOBUAPP_DOC_CODE    DOC_CODE,
       FOBUAPP_CHG_SEQ_NUM CHG,       
       FOBUAPP_DOC_AMT     AMT,
       FOBUAPP_ORIG_USER   ORIG_USER,
       TO_CHAR(FOBUAPP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
       TO_CHAR(FPBPOHD_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
From   
	fobuapp, 
	fpbpohd
Where
      FOBUAPP_SEQ_NUM = 2 
	And FOBUAPP_DOC_CODE = FPBPOHD_CODE 
	And Trunc(FPBPOHD_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
Order By
       FOBUAPP_USER_CODE,
       FOBUAPP_ORIG_USER,
       FOBUAPP_ACTIVITY_DATE                 
/

REM /* Select Purchase Orders Awaiting Deferred Edit    */
REM Ttitle Center 'Purchase Orders Awaiting Deferred Edit' SKIP 1
REM Select FPBPOHD_USER_ID   USER_ID,
REM        FOBDINP_DOC_NUM   DOC_CODE,
REM        FOBDINP_SEQ_NUM   CHG,       
REM        To_char(FOBDINP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
REM        To_char(FPBPOHD_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
REM From   fobdinp, fpbpohd
REM Where
REM        FOBDINP_SEQ_CODE = 2 And
REM        FOBDINP_DOC_NUM  = fpbpohd_code And
REM        Trunc(fpbpohd_trans_date) < To_date('&&new_date','MM/DD/YYYY')
REM Order By
REM        FPBPOHD_USER_ID ,
REM        FPBPOHD_CODE                   
REM /

/* Select Incomplete Purchase Orders    */
Ttitle Center 'Incomplete Purchase Orders' SKIP 1
Select  
       FPBPOHD_CODE                   DOC_CODE,
       FPBPOHD_CHANGE_SEQ_NUM         CHG,
       TO_CHAR(FPBPOHD_PO_DATE,'DD-MON-YYYY')    PO_DATE,                     
       TO_CHAR(FPBPOHD_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE,
       FPBPOHD_COMPLETE_IND   CPLT,
       FPBPOHD_CANCEL_IND     CNCL,
       FPBPOHD_USER_ID        USER_ID
From   fpbpohd
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')
Order By 
       FPBPOHD_USER_ID,
       FPBPOHD_CODE
/                      

REM /* Select Incomplete Receiving documents.  They do not have approvals or
REM    deferred editing processing.  */
REM Ttitle Center 'Incomplete Receiving Documents' SKIP 1
REM Select  
REM        fpbrchd_code                             DOC_CODE,
REM        To_char(fpbrchd_rcvd_date,'DD-MON-YYYY') RCVD_DATE,
REM        fpbrchd_COMPLETE_IND  CPLT,
REM        fpbrchd_USER_ID       USER_ID
REM From   fpbrchd
REM Where
REM        Nvl(fpbrchd_COMPLETE_IND,'N') = 'N' And
REM        Trunc(fpbrchd_rcvd_date) < To_date('&&new_date','MM/DD/YYYY')
REM Order By
REM        fpbrchd_USER_ID,
REM        fpbrchd_code
REM /                      

/* Select Invoices or Credit Memos Awaiting Approvals        */
Ttitle Center 'Invoices or Credit Memos  Awaiting Approval' SKIP 1
Select FOBUAPP_USER_CODE USER_ID,
       FOBUAPP_DOC_CODE  DOC_CODE,
       FOBUAPP_DOC_AMT AMT,
       FOBUAPP_ORIG_USER ORIG_USER,
       To_char(FOBUAPP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
       To_char(FABINVH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
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')
Order By
       FOBUAPP_USER_CODE,
       FOBUAPP_ORIG_USER,
       FOBUAPP_ACTIVITY_DATE                 
;
 
REM /* Select Invoices or Credit Memos Awaiting Deferred Edit    */
REM Ttitle Center 'Invoices or Credit Memos Awaiting Deferred Edit' SKIP 1

REM Select FABINVH_USER_ID   USER_ID,
REM        FOBDINP_DOC_NUM   DOC_CODE,
REM        To_char(FOBDINP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
REM        To_char(FABINVH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
REM  From   fobdinp, fabinvh
REM Where
REM        FOBDINP_SEQ_CODE = 3 And
REM        FOBDINP_DOC_NUM  = fabinvh_code And
REM        Trunc(fabinvh_trans_date) < To_date('&&new_date','MM/DD/YYYY')
REM Order By
REM       FABINVH_USER_ID ,
REM       FABINVH_CODE                   
REM /

/* Select Incomplete Invoices or Credit Memos     */
Ttitle Center 'Incomplete Invoices' SKIP 1
Select  
       FABINVH_CODE DOC_CODE,
       TO_CHAR(FABINVH_INVOICE_DATE,'DD-MON-YYYY') INV_DATE,                    
       TO_CHAR(FABINVH_PMT_DUE_DATE,'DD-MON-YYYY') DUE_DATE,                    
       TO_CHAR(FABINVH_TRANS_DATE,'DD-MON-YYYY')   TRAN_DATE,  
       FABINVH_COMPLETE_IND  CPLT,
       FABINVH_CANCEL_IND    CNCL,
       FABINVH_USER_ID       USER_ID
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')
Order By
      FABINVH_USER_ID,
      FABINVH_CODE
/                      

REM /* Select Invoices Awaiting Receipt      */
REM Ttitle Center 'Invoices Awaiting Receipt' SKIP 1
REM Select  
REM        fabinvh_code DOC_CODE,
REM        To_char(fabinvh_invoice_date,'DD-MON-YYYY') INV_DATE, 
REM        To_char(fabinvh_pmt_due_date,'DD-MON-YYYY') DUE_DATE,       
REM        To_char(fabinvh_trans_date,'DD-MON-YYYY')   TRAN_DATE,  
REM        FABINVH_COMPLETE_IND  CPLT,
REM        FABINVH_CANCEL_IND    CNCL,
REM        FABINVH_USER_ID       USER_ID
REM From  FABINVH
REM Where
REM       Nvl(FABINVH_COMPLETE_IND,'N')= 'R' And
REM      Trunc(FABINVH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
REM Order By
REM      FABINVH_USER_ID,
REM      fabinvh_code
REM /                      

/* Select Fixed Asset Adjustments Awaiting Approvals        */
Ttitle Center 'Fixed Asset Adjustments Awaiting Approval' SKIP 1
Select FOBUAPP_USER_CODE USER_ID,
       FOBUAPP_DOC_CODE  DOC_CODE,
       FOBUAPP_DOC_AMT AMT,
       FOBUAPP_ORIG_USER ORIG_USER,
       To_char(FOBUAPP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
       To_char(FFBADJH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
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')
Order By
       FOBUAPP_USER_CODE,
       FOBUAPP_ORIG_USER,
       FOBUAPP_ACTIVITY_DATE                 
/
 
/* Select Incomplete Fixed Asset Adjustments */
Ttitle Center 'Incomplete Fixed Asset Adjustments' SKIP 1
Select FFBADJH_CODE            DOC_CODE,
       FFBADJH_FUNCTION_CODE   FUNC,                      
       TO_CHAR(FFBADJH_TRANS_DATE,'DD-MON-YYYY')   TRAN_DATE,  
       FFBADJH_COMPLETE_IND    CPLT,
       FFBADJH_USER_ID         USER_ID
From   ffbadjh
Where
       Nvl(FFBADJH_COMPLETE_IND,'N') = 'N' 
       And Trunc(FFBADJH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
Order By
       ffbadjh_user_id,
       ffbadjh_code
/                      

/* Select Journal Vouchers Awaiting Approvals        */
Ttitle Center 'Journal Vouchers Awaiting Approval' SKIP 1
Select FOBUAPP_USER_CODE USER_ID,
       FOBUAPP_DOC_CODE  DOC_CODE,
       FOBUAPP_DOC_AMT AMT,
       FOBUAPP_ORIG_USER ORIG_USER,
       To_char(FOBUAPP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
       To_char(FGBJVCH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
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')
Order By
       FOBUAPP_USER_CODE,
       FOBUAPP_ORIG_USER,
       FOBUAPP_ACTIVITY_DATE                 
/

REM /* Select Journal Vouchers Awaiting Deferred Edit    */
REM Ttitle Center 'Journal Vouchers Awaiting Deferred Edit' SKIP 1
REM Select FGBJVCH_USER_ID   USER_ID,
REM        FOBDINP_DOC_NUM   DOC_CODE,
REM        To_char(FOBDINP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
REM        To_char(FGBJVCH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
REM From   fobdinp, fgbjvch
REM Where
REM        FOBDINP_SEQ_CODE = 20 And
REM        FOBDINP_DOC_NUM  = fgbjvch_doc_num And
REM        Trunc(fgbjvch_trans_date) < To_date('&&new_date','MM/DD/YYYY')
REM Order By
REM        FGBJVCH_USER_ID ,
REM        FGBJVCH_DOC_NUM                
REM /

/* Select Incomplete or Recurring JVs         */
Ttitle Center 'Incomplete or Recurring Journal Vouchers' SKIP 1
Select  
       FGBJVCH_DOC_NUM DOC_CODE,
       FGBJVCH_USER_ID USER_ID,
       FGBJVCH_DOC_AMT    AMT,
       FGBJVCH_STATUS_IND CPLT, 
       To_char(FGBJVCH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE,
       To_char(FGBJVCH_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE
From   FGBJVCH
Where 
       Nvl(FGBJVCH_STATUS_IND,'I') != 'C' 
       And Trunc(FGBJVCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
Order By 
       FGBJVCH_USER_ID,
       FGBJVCH_DOC_NUM
/

/* Select Encumbrances Awaiting Approvals        */
Ttitle Center 'Encumbrances Awaiting Approval' SKIP 1
Select FOBUAPP_USER_CODE USER_ID,
       FOBUAPP_DOC_CODE  DOC_CODE,
       FOBUAPP_DOC_AMT AMT,
       FOBUAPP_ORIG_USER ORIG_USER,
       To_char(FOBUAPP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
       To_char(FGBENCH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
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')
Order By
       FOBUAPP_USER_CODE,
       FOBUAPP_ORIG_USER,
       FOBUAPP_ACTIVITY_DATE                 
/

REM /* Select Encumbrances Awaiting Deferred Edit    */
REM Ttitle Center 'Encumbrances Awaiting Deferred Edit' SKIP 1

REM Select FGBENCH_USER_ID   USER_ID,
REM        FOBDINP_DOC_NUM   DOC_CODE,
REM        To_char(FOBDINP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
REM        To_char(FGBENCH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
REM From   fobdinp, fgbench
REM Where
REM        FOBDINP_SEQ_CODE = 25 And
REM        FOBDINP_DOC_NUM  = fgbench_num And
REM        Trunc(fgbench_trans_date) < To_date('&&new_date','MM/DD/YYYY')
REM Order By
REM        FGBENCH_USER_ID ,
REM        FGBENCH_NUM                
REM /

/* Select Incomplete Encumbrances or Encumbrances being changed    */
Ttitle Center 'Incomplete Encumbrances or Encumbrances being Changed' SKIP 1
Select  
       FGBENCH_NUM        DOC_CODE,
       FGBENCH_USER_ID    USER_ID,
       FGBENCH_DOC_AMT    AMT,
       FGBENCH_TYPE       TYPE, 
       FGBENCH_STATUS     CPLT, 
       To_char(FGBENCH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE,
       To_char(FGBENCH_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE
From   fgbench
Where 
       Nvl(FGBENCH_STATUS,'I') != 'C' 
       And Trunc(FGBENCH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
Order By 
       FGBENCH_USER_ID,
       FGBENCH_NUM
/

/* Select Direct Cash Receipts Awaiting Approvals        */
Ttitle Center 'Direct Cash Receipts Awaiting Approval' SKIP 1
Select FOBUAPP_USER_CODE USER_ID,
       FOBUAPP_DOC_CODE  DOC_CODE,
       FOBUAPP_DOC_AMT AMT,
       FOBUAPP_ORIG_USER ORIG_USER,
       To_char(FOBUAPP_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE,
       To_char(FGBCSHH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE
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')
Order By
       FOBUAPP_USER_CODE,
       FOBUAPP_ORIG_USER,
       FOBUAPP_ACTIVITY_DATE                 
/

/* Select Incomplete Direct Cash Receipts    */
Ttitle Center 'Incomplete Direct Cash Receipts' SKIP 1
Select  
       FGBCSHH_DOC_NUM    DOC_CODE,
       FGBCSHH_USER_ID    USER_ID,
       FGBCSHH_DOC_AMT    AMT,
       FGBCSHH_STATUS_IND CPLT, 
       To_char(FGBCSHH_TRANS_DATE,'DD-MON-YYYY') TRAN_DATE,
       To_char(FGBCSHH_ACTIVITY_DATE,'DD-MON-YYYY') A_DATE
From   fgbcshh
Where 
       Nvl(FGBCSHH_STATUS_IND,'I') != 'C' 
       And Trunc(FGBCSHH_TRANS_DATE) < To_date('&&new_date','MM/DD/YYYY')
Order By 
       FGBCSHH_USER_ID,
       FGBCSHH_DOC_NUM
/

/* Select transaction sitting in fgbtrni      */
Ttitle Center 'Transactions in FGBTRNI' SKIP 1
Select fgbtrni_system_id SYSTM_ID,
       fgbtrni_doc_code DOC_CODE,
       fgbtrni_trans_desc DESCRIPTION
From   fgbtrni
Where  
       Trunc(FGBTRNI_TRANS_DATE) < To_date( '&&new_date','MM/DD/YYYY')
       And  FGBTRNI_REC_TYPE = '1'
ORDER by 
       FGBTRNI_DOC_CODE
/
Spool Off