Bad Debt Process

Owned by Chad
Aug 02, 2021

The process is replaced by Cognos report "Bad Debt Voucher"

Information:

Categories to eliminate: OLD, BAD, BNK, CNT, CS1, CSH, DED, EFT, GRN, EXM, FA, MEM, QCD, RFD, TC, TRI, WO, WOC, PAD, PLS, XAR, INS, X%

Other to eliminate: 'P' type detail codes, program codes '996%', '994%','82001', fund codes '9%', '095%', account code 'B%', 'A3109', DCode 'M596'

DCode do not have TW, TS, TU, TF codes and use AY instead: BN01, BN02, BN03, BN04, BN05, BN06, BN07, BN08, BN09, BN10, BN11, BN12, BNOL, BR01, BR02, BR03, BR04, BR05, BR06, BR07, BR08, BR09, BR10, BR11, BR12, BROL, NAGR, NATG, NATU, NAUG, UR13, UR14, UR15, UR16

Bad Debt Accounts:

  1. Housing (Category HOU) => 101001-A3900
  2. Extended Study - Fund 016501, 057504, 002521, 010000, 090019, 055003, 057102, 050002, 055002 => 050002-A3900
  3. Others => 001100-A3900

Use TAISMGR.TBBDETC, TAISMGR.TBRACCT in OPRD Database

Reserve percentage:

  1. INTR, LATE => .15
  2. BLLF,CLLT => .08
  3. FUND '090%' => .04
  4. Others => .01

Steps:

  1. Connect to ORPD Database and open query oprd_dcode_query.sql
  2. Change the A.TBDS in condition based on the quarter (TW: Winter, TS: Spring, TF: Summer, TF: Fall)
  3. Run the query and save the result as a CSV file and remove the double quotes(").
  4. Import the result to table BD_DCodes in Workspace database (MSSQL) and make sure the percentage add up to 100 for each detail code.
  5. Import total billed data into table BD_TotalBilled and execute stored procedure SP_FinishDCode
  6. Use View BD_DCodes, BD_View_TotalBilled, BD_View_DetailData, and BD_View_Reserves to create the spreadsheet JV