REM ************************************************************************
REM*           JVREVERSAL.sql
REM*  jvreversal code from ActionLine modified by Deborah Colella   3/15/2000
REM*  This will recreate fgbjvcd records from a document that has posted to the
REM*  fgbtrnh tables.
REM*                                                                         
REM*  After running this sql, you will need to go into FGAJVCD and create the
REM*  header record for the new JV. When you go into the form, be sure to use
REM*  the new document code you identify below. Put in any amount for the
REM*  document total and then exit the form. Go back into FGAJVCD, call up your
REM*  new JV number again and do a next block. All the sequences will appear.  
REM*  To get the JV total, go to FGIJSUM and pull up the new JV. It will give  
REM*  you the total.                                                           
REM*                                                                           
REM* NOTE: If you do not want to reverse the document, then remove the decode
REM*  statement on the debit credit indicator and just use the field.
REM* ----------------------------------------------------------------------
REM*     PARAMETERS
REM*     OUTPUTDOCUMENT: The document number to be used on the journal voucher
REM*     INPUTDOCUMENT:  The document number in fgbtrnh to create the new
REM*                     journal voucher from.
REM* ----------------------------------------------------------------------
REM*    TABLES USED
REM*      FGBTRNH,
REM*      FGBJVCD
REM* ----------------------------------------------------------------------
REM* Updates:
REM*   4/16/01 (SDR) - Use next generated OUTPUTDOCUMENT journal voucher
REM*     number from fobseqn instead of entering it.
REM *************************************************************************

undefine INPUTDOCUMENT
undefine OUTPUTDOCUMENT
column OUTPUTDOCUMENT_SET new_value OUTPUTDOCUMENT
select 'J' || lpad(fobseqn_maxseqno_7+1,7,'0') OUTPUTDOCUMENT_SET from fobseqn
  where fobseqn_seqno_type   = 'J' for update;
update fobseqn set fobseqn_maxseqno_7 = fobseqn_maxseqno_7 + 1
  where fobseqn_seqno_type   = 'J';
commit;
insert into fimsmgr.fgbjvcd
    (fgbjvcd_doc_num, 
    fgbjvcd_submission_number, 
    fgbjvcd_seq_num, 
    fgbjvcd_activity_date, 
    fgbjvcd_user_id, 
    fgbjvcd_rucl_code, 
    fgbjvcd_trans_amt, 
    fgbjvcd_trans_desc, 
    fgbjvcd_dr_cr_ind, 
    fgbjvcd_fsyr_code, 
    fgbjvcd_acci_code, 
    fgbjvcd_coas_code, 
    fgbjvcd_fund_code, 
    fgbjvcd_orgn_code, 
    fgbjvcd_acct_code, 
    fgbjvcd_prog_code, 
    fgbjvcd_actv_code, 
    fgbjvcd_locn_code, 
    fgbjvcd_bank_code, 
    fgbjvcd_doc_ref_num, 
    fgbjvcd_vendor_pidm, 
    fgbjvcd_cmt_type, 
    fgbjvcd_cmt_pct, 
    fgbjvcd_dep_num, 
    fgbjvcd_encb_action_ind,
    fgbjvcd_prjd_code,
    fgbjvcd_dist_pct,
    fgbjvcd_posting_period,
    fgbjvcd_budget_period,
    fgbjvcd_accrual_ind,
    fgbjvcd_status_ind,
    fgbjvcd_abal_override,
    fgbjvcd_coas_code_pool,
    fgbjvcd_fund_code_pool)
  select '&OUTPUTDOCUMENT',
    fgbtrnh_submission_number,
    fgbtrnh_seq_num,
    fgbtrnh_activity_date,
    fgbtrnh_user_id,
    fgbtrnh_rucl_code,
    fgbtrnh_trans_amt,
    fgbtrnh_trans_desc,
    decode(fgbtrnh_dr_cr_ind,'D','C','C','D','+','-','-','+'),
    fgbtrnh_fsyr_code,
    fgbtrnh_acci_code,
    fgbtrnh_coas_code,
    fgbtrnh_fund_code,
    fgbtrnh_orgn_code,
    fgbtrnh_acct_code,
    fgbtrnh_prog_code,
    fgbtrnh_actv_code,
    fgbtrnh_locn_code,
    fgbtrnh_bank_code,
    fgbtrnh_doc_ref_num,
    fgbtrnh_vendor_pidm,
    fgbtrnh_cmt_type,
    fgbtrnh_cmt_pct,
    fgbtrnh_dep_num,
    fgbtrnh_encd_action_ind,
    fgbtrnh_prjd_code,
    fgbtrnh_dist_pct,
    fgbtrnh_posting_period,
    fgbtrnh_budget_period,
    fgbtrnh_accrual_ind,
    'P',
    fgbtrnh_abal_override,
    fgbtrnh_coas_code_pool,
    fgbtrnh_fund_code_pool
  from fgbtrnh
  where fgbtrnh_doc_code = '&&INPUTDOCUMENT'
  and fgbtrnh_coas_code = 'Z';
select 'fgbtrnh document number &INPUTDOCUMENT reversed by fgbjvcd document number &OUTPUTDOCUMENT' Results from dual;