************************************************************************
**              BAKOORPHANS.SQL
** Orphan FGRBAKO Records Report  written for Finance release 3.1
**     1.  Reports on all orphan FGRBAKO records to be deleted
**********************************************************************
** CHG    DATE       PROGRAMMER
**  01  11-OCT-1999  Deborah Colella
**      Created script
**  02  8-Sep-2006	Jim Beyer
**				Updated for reference package standares, added 
** 				copyright information, verified data fields for
**				version 7.x
**
*********************************************************************
**
**		© 1999, 2006 Sungard Higher Education
**		All Rights Reserved
**		Not for Web publication
**    
**********************************************************************
**        PARAMETERS
** 1 - NONE
**    
**********************************************************************
**       TABLES USED
**  fgrbako, fpbreqh, fpbpohd, fabinvh, fgbjvch, fgbench, fgbcshh,
**  ffbadjh
**  
**********************************************************************
**       NOTES        
** 1.  This process is to be run And verified before actually running
**      the delbako.sql script.  Then the BAVL rebuild process can be 
**      run (fgrbavl).
**    
************************************************************************

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 Doc_Code     Head 'Document Code' For  A15          
Col Subn         Head 'Sub/Chg Num'   For  999          

Spool bakoorphans.rpt

/* Select Orphaned Purchase Requisitions */
Ttitle Center 'Orphaned Purchase Requisitions' SKIP 1
Select FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
       fpbreqh, 
       fgrbako
Where
      FGRBAKO_DOC_TYPE = 1 
       AND FGRBAKO_DOC_NUM  = FPBREQH_CODE(+) 
       And FPBREQH_CODE     Is Null
Order By
       FGRBAKO_DOC_NUM
/

/* Select Orphaned Purchase And Change Orders*/
Ttitle Center 'Orphaned Purchase Orders' SKIP 1
Select FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
       fpbpohd, 
       fgrbako
Where
      FGRBAKO_DOC_TYPE = 2 
       And FGRBAKO_DOC_NUM  = FPBPOHD_CODE(+) 
       And FPBPOHD_CODE     Is Null
Order By  FGRBAKO_DOC_NUM
/

/* Select Orphaned Invoices or Credit Memos */
Ttitle Center 'Orphaned Invoices  or Credit Memos' SKIP 1
Select FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
       fabinvh, 
fgrbako
Where
      FGRBAKO_DOC_TYPE = 3 
       And FGRBAKO_DOC_NUM  = FABINVH_CODE(+) 
       And FGRBAKO_SUBMISSION_NUMBER = FABINVH_SUBMISSION_NUMBER(+) 
       And FABINVH_CODE     Is Null
Order By FGRBAKO_DOC_NUM
/
 
/* Select Orphaned Fixed Asset Adjustments  */
Ttitle Center 'Orphaned Fixed Asset Adjustments ' SKIP 1
Select FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
	ffbadjh, 
	fgrbako
Where
      FGRBAKO_DOC_TYPE = 60 
	And FGRBAKO_DOC_NUM  = FFBADJH_CODE(+) 
      And FFBADJH_CODE     Is Null
Order By FGRBAKO_DOC_NUM
/
 

/* Select Orphaned Journal Vouchers   */
Ttitle Center 'Orphaned Journal Vouchers' SKIP 1
Select FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
	fgbjvch, 
	fgrbako
Where
      FGRBAKO_DOC_TYPE = 20 
       And FGRBAKO_DOC_NUM  = FGBJVCH_DOC_NUM(+) 
       And GRBAKO_SUBMISSION_NUMBER = FGBJVCH_SUBMISSION_NUMBER(+) 
       And FGBJVCH_DOC_NUM     Is Null
Order By FGRBAKO_DOC_NUM
/

/* Orphaned  Encumbrances */
Ttitle Center 'Orphaned Encumbrances' SKIP 1
Select FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
       fgbench, 
       fgrbako
Where
      FGRBAKO_DOC_TYPE = 25 AND
      FGRBAKO_DOC_NUM  = FGBENCH_NUM(+) 
       And FGBENCH_NUM      Is Null
Order By FGRBAKO_DOC_NUM
/

/* Select Orphaned Direct Cash Receipts   */
Ttitle Center 'Orphaned Direct Cash Receipts' SKIP 1
Select FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
	fgbcshh, 
	fgrbako
Where
      FgrBAKO_DOC_TYPE = 80 AND
      FGRBAKO_DOC_NUM  = FGBCSHH_DOC_NUM(+) 
And FGBCSHH_DOC_NUM      Is Null
Order By FGRBAKO_DOC_NUM
/

/* Select FGRBAKO records already posted in FGBTRNH. May be dupes from above.*/
Ttitle Center 'Already Posted FGRBAKO Records' SKIP 1
Select DISTINCT FGRBAKO_DOC_NUM DOC_CODE,
       FGRBAKO_SUBMISSION_NUMBER SUBN
From   
	fgbtrnh, 
	fgrbako
Where
      FGRBAKO_COAS_CODE = FGBTRNH_COAS_CODE 
       And FGRBAKO_DOC_NUM  = FGBTRNH_DOC_CODE 
       And FGRBAKO_SUBMISSION_NUMBER = FGBTRNH_SUBMISSION_NUMBER
Order By FGRBAKO_DOC_NUM
/
Spool Off