I created the XXRBKMGR process to maximize the efficiency of our Month End GL Close Reporting. We have approximately 250 nVision Report Templates that generate over 2,500 individual xls report files. To alleviate running each report manually the obvious first step is to group them into report books. Now we have 25 report books - run controls SP_RB_GRP01 thru SP_RB_GRP25. They are divided & sequenced based on how long the reports run & the priority of availability. As mentioned in the introduction, PeopleSoft does allow you to schedule a report book at a specific date/time - but there is no flexibility. Month End close is typically a stress filled exercise that requires last minute postings & possible time extensions - there is NO static schedule for running the close. Furthermore, a mindless static schedule could potentially take up threads that would interfere with normal nightly batch processing - queuing jobs indiscriminately can have adverse effects. Also, when a report book ends another one isn't queued up to run when the thread is available - it sticks to the schedule.
Sample Report Book Definition:
The dilemna is multi-faceted:
- The GL Closing is not on a predetermined schedule but rather a verbal command to begin once all postings & last minute adjustments have been made.
- Report Books must be processed by a limited group of threads so they don't effect the nightly batch. Long running nVision reports can potentially consume all processing threads & bring production to a halt.
- For increased efficiency load balancing must occur between our NT1/NT2 servers - a real-time assessment must be made to assign the server effectively.
- When a report book completes there is no time to waste - the next book must be quickly queued for execution. An idle thread translates to delays in final report delivery.
- Run Control dates for all report books must be maintained on a monthly basis.
- Periodic Status Notifications showing the report books that have Completed, are Running & still Pending would be useful.
The Solution:
That's it - execute XXRBKMGR.SQR - a "Single-Click" to handle everything... Automatic queuing, notifications, parameter settings, load balancing, etc...
Once XXRBKMGR is initially invoked it will requeue itself automatically every X minutes (currently set to 30) as long as there are report books running and/or pending. When all are complete the process ends.
Notification Samples:
The first occurrence of XXRBKMGR produces this email - it launched report books 6, 10 & 7 with the other 22 still pending. The numbers represent report book SP_RB_GRP<xx>.
XXRBKMGR report sample - Accessed via Process Monitor or click the URL provided in the email:
Here's some code snippets... it's a very tight streamlined process.
!**********************************************************************
!* *
!* MODULE: XXRBKMGR.SQR *
!* AUTHOR: TONY DELIA. *
!* DATE: 03/17/2016. *
!* SYSTEM: NSS/ADVANCE PUBLICATIONS - PSOFT 8.8 FINANCIALS. *
!* DESC: NVISION REPORTBOOK MANAGER. *
!* *
!**********************************************************************
!* *
!* TABLES: ps_xx_run_cntl_gl - Select *
!* ps_xx_rb_map - Select *
!* ps_xx_rb_hdr - Select, Update, Insert *
!* ps_xx_rb_run - Select, Update, Insert *
!* psprcsrqst - Select *
!* psnvsbook - Select, Update *
!* psnvsbookrequst - Select *
!* ps_nvs_report - Select, Update *
!* psserverstat - Select *
!* *
!**********************************************************************
The number of threads & the time increment between recurring XXRBKMGR executions are defined here:
#define RB_THREADS 3 ! Maximum Open Threads
#define RB_MINS 30 ! ReportBook Manager Increment
Below is the core logic for evaluating Complete, Running & Pending items and, if needed, will queue another report book with the proper load balancing. I'm acutely aware that this sample is a "teaser" - the RUN_Init function does all the heavy lifting. I created this years ago (XX_RUN.SQC) to load Process Requests for SQR, Application Engine, COBOL, Crystal Reports, nVision Report Books, etc.
evaluate $A_process_status
when = 'P'
if #THREADS < {RB_THREADS}
if #PRI_ctr < 2
let $B_server = $SVR_pri
let #PRI_ctr = #PRI_ctr + 1
else
let $B_server = $SVR_alt
end-if
let $RCX_svr = $B_server
let $RUN_type = '7'
let $RUN_prcs = 'RPTBOOK'
let $RUN_oprid = $A_oprid
let $RUN_cntl = $A_run_cntl_id
do RUN_Init($RUN_type, $RUN_prcs, $RUN_oprid, $RUN_cntl, #RUN_pi)
let $A_process_status = 'R'
let #A_process_instance = #RUN_pi
let #A_process_inst_stg = #prcs_process_instance
do Update_XX_RB_RUN
let #THREADS = #THREADS + 1
end-if
break
when = 'R'
let #A_active = instr('*5*6*7*16*', $B_runstatus, 1)
if #A_active > 0
let #THREADS = #THREADS + 1
if $B_server = $SVR_pri
let #PRI_ctr = #PRI_ctr + 1
end-if
else
let $A_process_status = 'C'
do Update_XX_RB_RUN
end-if
end-evaluate
Below is a snippet of the dynamic requeuing of XXRBKMGR - as long as there are Running or Pending report books it will queue itself in 30 minutes {RB_MINS}. There's the RUN_Init() teaser again... extremely powerful & useful utility I created. It's used extensively here at NSS/ACSI for a variety of applications.
let #PR_ctr = #P_ctr + #R_ctr
if #PR_ctr > 0
let $RCX_dttm = DateToStr(DateAdd(DateNow(),'minute',{RB_MINS}),'YYYYMMDD_HH24:MI')
let $SAV_dttm = $RCX_dttm
let $RCX_svr = 'PSUNX'
let $RUN_type = '1'
let $RUN_name = 'XXRBKMGR'
let $RUN_id = 'PSBATCH'
let $RUN_cntl = $prcs_run_cntl_id
if $RUN_cntl = ''
let $RUN_cntl = 'SP_RPTBOOK_MGR'
end-if
do RUN_Init($RUN_type, $RUN_name, $RUN_id, $RUN_cntl, #RUN_pi)
show ' '
show 'PI#: ' #RUN_pi edit 99999999 ' RC: ' $RUN_cntl ' Date/Time: ' $SAV_dttm
show ' '
else
let $HDR_status = 'C'
do Update_Report_Books_POST
end-if
do Update_XX_RB_HDR
do Send_Email
Once all reporting is complete & thoroughly reviewed by the GL team I have another process, written in PowerShell, that uploads the reports to SharePoint (another "Single-Click" solution).
This concludes my Custom Report Book Manager post... I hope you found it interesting.
