/************************************************************************
**              BAKODEL.SQL
** FGRBAKO ORPHAN Delete Process  written for release 3.1
**   1.  Removes all Orphan FGRBAKO records IncludIng ones that have
**       already been posted In FGBTRNH.  ThIs needs to be run prior
**       to FGRBAVL rebuild as BAVL rebuild does not remove orphan
**       records.
**********************************************************************
**  CHG    DATE       PROGRAMMER
**  01  12*OCT*1999  Deborah Colella
**                   Created delete script.
**
**  02  8*Sep*2006   Jim Beyer
**   			   Updated header, applied reference pack standards
**			   verified field names for version 7.3
**    
**********************************************************************
**        PARAMETERS
** 1 * NONE
**    
**********************************************************************
**       TABLES USED
**  fpbreqh, fpbpohd, fabInvh, fgbjvch, fgbtrnh,
**  fgbench, fgbcshh, ffbadjh, 
**    
**********************************************************************/

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 bakodel.rpt 

/* Delete Orphaned Purchase RequIsitions */
Delete fgrbako
Where fgrbako.ROWID In
   (Select FGRBAKO.ROWID
    From 
	fpbreqh, 
	fgrbako
    Where FGRBAKO_DOC_TYPE = 1  
      And FGRBAKO_DOC_NUM = FPBREQH_CODE(+)
      And FPBREQH_CODE Is Null)            
/

/* Delete Orphaned Purchase and Change Orders*/
Delete fgrbako
Where fgrbako.ROWID In
   (Select fgrbako.ROWID
    From 
	fpbpohd, 
	fgrbako
    Where FGRBAKO_DOC_TYPE = 2  
      And FGRBAKO_DOC_NUM = FPBPOHD_CODE(+)
      And FPBPOHD_CODE Is Null)            
/

/* Delete Orphaned Invoices or Credit Memos */
Delete fgrbako
Where fgrbako.ROWID In
   (Select fgrbako.ROWID
    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)            
/
 
/* Delete Orphaned Fixed Asset Adjustments  */
Delete fgrbako
Where fgrbako.ROWID In
   (Select fgrbako.ROWID
    From 
       ffbadjh, 
       fgrbako
    Where FGRBAKO_DOC_TYPE = 60  
      And FGRBAKO_DOC_NUM = FFBADJH_CODE(+)
      And FFBADJH_CODE Is Null)            
/

/* Delete Orphaned Journal Vouchers   */
Delete fgrbako
Where fgrbako.ROWID In
   (Select fgrbako.ROWID
    From 
       fgbjvch, 
       fgrbako
    Where FGRBAKO_DOC_TYPE = 20  
      And FGRBAKO_DOC_NUM = FGBJVCH_DOC_NUM(+)
      And FGRBAKO_SUBMISSION_NUMBER = FGBJVCH_SUBMISSION_NUMBER(+)
      And FGBJVCH_DOC_NUM Is Null)            
/

/* Delete Orphaned  Encumbrances */
Delete fgrbako
Where fgrbako.ROWID In
   (Select fgrbako.ROWID
    From 
       fgbench, 
       fgrbako
    Where FGRBAKO_DOC_TYPE = 25  
      And FGRBAKO_DOC_NUM = FGBENCH_NUM(+)
      And FGBENCH_NUM Is Null)            
/

/* Select Orphaned Direct Cash Receipts   */
Delete fgrbako
Where fgrbako.ROWID In
   (Select fgrbako.ROWID
    From 
       fgbcshh, 
       fgrbako
    Where FGRBAKO_DOC_TYPE = 80  
      And FGRBAKO_DOC_NUM = FGBCSHH_DOC_NUM(+)
      And FGBCSHH_DOC_NUM Is Null)            
/

/* Delete FGRBAKO records already posted In FGBTRNH.*/
Delete fgrbako
Where fgrbako.ROWID In
   (Select fgrbako.ROWID
    From 
       fgbtrnh, 
fgrbako
    Where FGRBAKO_COAS_CODE = FGBTRNH_COAS_CODE
      And FGRBAKO_DOC_NUM   = FGBTRNH_DOC_CODE
      And FGRBAKO_SUBMISSION_NUMBER = FGBTRNH_SUBMISSION_NUMBER)
/
Spool Off