!******************************************************************************* ! umglrex.sqc * !******************************************************************************* ! Description: Extracts data into the GL reporting tables * ! Owner: University of Maine System * ! Frequency: N/A * !******************************************************************************* ! Specifications By: Bill Elsemore * ! Date: March 25, 2007 * !******************************************************************************* ! Created By: Matt Byther * ! Date: March 25, 2007 * !******************************************************************************* ! This program is derived from UMGLINQ.SR, UMGLRPTG.SQR and UMGLQRPT.SQC * ! and replaces them. Heavy modifications were required and the code was * ! reworked for readability * !******************************************************************************* ! MODIFICATION LOG * !******************************************************************************* ! Mod # Programmer Date * ! ------ ---------- ---- * ! ??0307 M. Byther 3/25/2007 * ! New version (name) of program created from prior versions * ! Code reworked and clean up * ! All old comment out code removed * ! Several enhancements applied * ! With several years of data, deleting and ! rebuilding indexes inefficient* ! Removed code to truncate a rebuild all periods ! We should never do this again ! ??0507 M. Byther ! Continue with changes ! Added 11 fields to end of record ! ??0607 M. Byther ! Removed updating of status flags used by gl inquiry ! ??0607 M. Byther 6/15/2007 ! Fixed project in-service date field and PROG_TI_INC_DST ! ??0707 M. Byther 7/5/2007 ! Wasnt including period zero ! ??0807 M. Byther 7/31/2007 ! Not processing period 998 and zero when wildcards on runcontrol ! Process budgets in period zero ! ??0907 M. Byther 9/5/2007 ! In september, process june, period 998, period 0, july, august and sept ! ! ??0308 K. Van Dine ! 9.0 upgrade Fields moved from PROJECT_STATUS to PROJECT. Temporary ! fix pending review from Bill E. ! 011309 C. Chaisson ! 9.0 Upgrade fields moved from project_status to project, loosing history. ! Table UM_PROJECT_STAT tracks changes. Changes update to project table. ! 06/2009 C. Chaisson ! Added new project attribute 'CLOSINGCOMBO'along with its description. ! 04/2010 C. Chaisson ! Added new department attribute 'HR_DEPT' along with its description. ! ??0613 C. Chaisson ! Added effective dating for pstreenode, pstreedefn 'ACCT_GL_INQ'. ! New account tree was causing unique constraint issues. !******************************************************************************* !**********************************************************************************! ! MODIFICATION LOG ! !**********************************************************************************! ! Mod# Programmer Date Description ! ! ------- ---------- --------- --------------------------------------------------! ! FN92UPG ERPA 4/20/2016 Upgrade Retrofit (9.0 to 9.2) ! ! Changed alias names from rc_deptid to rc.deptid ! ! in the SQL statement ! !**********************************************************************************! !*********************************************************************** !Begin-Report Procedure !*********************************************************************** begin-setup #include 'setenv.sqc' !Set environment ! set up a generic date field..... always a good idea declare-variable date $edit_date date $per_end_dt date $time_s date $time_e date $Datetimestart date $startedprocess date $estcompletion end-declare load-lookup name=bu_lu rows=10 table=ps_bus_unit_tbl_fs key=business_unit return_value='descr || '','' || descrshort' end-setup !*********************************************************************** !Begin-Report Procedure !*********************************************************************** Begin-Report do Init-Report let $What_Time = 'S' do Time-SQR do Process-Main let $What_Time = 'E' do Time-SQR do Stdapi-Term End-Report !*********************************************************************** ! Init-Report Procedure !*********************************************************************** Begin-Procedure Init-Report move 'UMGLREX' to $Reportid do Init-DateTime do Init-Number do Get-Current-DateTime do Stdapi-Init display '' display $Reportid let $What_Time = 'S' let $ReportTitle = 'Load GL Inquiry Super Data' let #gl_total = 0 let #sl_total = 0 let #jrnl_line = 0 let #Ticker = 0 let $last_bu = ' ' let #counter = 0 let #show-proc-count = 0 End-Procedure Init-Report !*********************************************************************** ! Process-Main Procedure !*********************************************************************** Begin-Procedure Process-Main show 'Loading GL Inquiry Data ....' let $Calling_Procedure = 'Process-Main' let #PrevFY = 0 let #PrevAP = 0 do get-run-control ! set the load in process indicator and commit.... in case of failure ! let $load_ind = 'Y' ! ??0607 ! do update-load-ind ! ??0607 ! do Process-Commit ! ??0607 show '*******************************************************' show 'This is what you asked me to do.....' show '*******************************************************' show 'Fiscal Year: ' #rc_fy show 'Accounting Period: ' #rc_ap show 'Where clause: ' $where_clause show ' ' $ledger_999 show ' ' ! do Process-Load-Table-Info ! ??0607 ! do Delete-Records do Process-Main-Fetch ! rebuild the bitmap indexes dropped in 'Delete-Records' (if appropriate) ! if $index_dropped = 'Y' ! do build-indexes ! end-if ! reset the load in process indicator ! let $load_ind = ' ' ! ??0607 ! do update-load-ind ! ??0607 ! do Clear-Fast-Load-Tbl ! ??0607 End-Procedure Process-Main !*********************************************************************** begin-procedure Process-Load-Table-Info let $Datetimestart = datenow() if #rc_fy = 9999 let $Loadwhereclause = '' do Get-Load-Periods else if #rc_ap = 99 let $holdfy = to_char(#rc_fy) let $Loadwhereclause = 'WHERE LOAD.FISCAL_YEAR = ' || $holdfy do Get-Load-Periods else if #rc_fy = 0 do get-curr-ap move #curr_fy to #loadfiscalyear move #curr_ap to #loadperiod do Insert-Load-Table !let #loadprev = #curr_ap - 1 if #curr_ap = 998 let #loadprev = 12 else let #loadprev = #curr_ap - 1 end-if move #loadprev to #loadperiod do Insert-Load-Table else move #rc_fy to #loadfiscalyear move #rc_ap to #loadperiod do Insert-Load-Table end-if end-if end-if end-procedure Process-Load-Table-Info !*********************************************************************** begin-procedure Get-Load-Periods begin-SELECT DISTINCT LOAD.FISCAL_YEAR LOAD.ACCOUNTING_PERIOD move &LOAD.FISCAL_YEAR to #loadfiscalyear move &LOAD.ACCOUNTING_PERIOD to #loadperiod do Insert-Load-Table FROM PS_LEDGER LOAD [$Loadwhereclause] end-SELECT end-procedure Get-Load-Periods !*********************************************************************** begin-procedure Insert-Load-Table begin-SQL INSERT INTO PS_UM_FAST_LD_TBL ( PROCESS_DTTM, FISCAL_YEAR, ACCOUNTING_PERIOD, STARTDATETIME, UM_EST_COMPLETE ) VALUES ( $Datetimestart, #loadfiscalyear, #loadperiod, '', '' ) end-SQL begin-SQL COMMIT end-SQL end-procedure Insert-Load-Table !*********************************************************************** begin-procedure Delete-Prior-Load-Periods begin-SQL DELETE FROM PS_UM_FAST_LD_TBL WHERE PROCESS_DTTM = $Datetimestart AND FISCAL_YEAR = #fy AND ACCOUNTING_PERIOD < #ap end-SQL end-procedure Delete-Prior-Load-Periods !*********************************************************************** begin-procedure Update-Load-Times let $startedprocess = datenow() let $estcompletion = dateadd($startedprocess, 'MINUTE', 30) begin-SQL UPDATE PS_UM_FAST_LD_TBL SET STARTDATETIME = $startedprocess, UM_EST_COMPLETE = $estcompletion WHERE PROCESS_DTTM = $Datetimestart AND FISCAL_YEAR = #fy and ACCOUNTING_PERIOD = #ap end-Sql begin-SQL COMMIT end-SQL end-procedure Update-Load-Times !*********************************************************************** ! procedure Clear-Fast-Load-Tbl !*********************************************************************** begin-procedure Clear-Fast-Load-Tbl begin-SQL DELETE FROM PS_UM_FAST_LD_TBL WHERE PROCESS_DTTM = $Datetimestart end-SQL end-procedure Clear-Fast-Load-Tbl !*********************************************************************** ! tmp-to-reprting Procedure !*********************************************************************** Begin-Procedure tmp-to-reprting let $Calling_Procedure = 'tmp-to-reprting' do show-proc-start begin-sql on-error=sql-Recover insert into ps_um_glq_reprting (select * from ps_um_glq_rpt_tmp) end-sql do show-proc-end End-Procedure tmp-to-reprting !*********************************************************************** ! Process-Commit Procedure !*********************************************************************** Begin-Procedure Process-Commit let $Calling_Procedure = 'Process-Commit' begin-sql on-error=sql-Recover commit end-sql End-Procedure Process-Commit !*********************************************************************** ! update-load-ind Procedure !*********************************************************************** Begin-Procedure update-load-ind let $Calling_Procedure = 'update-load-ind' begin-sql on-error=sql-Recover update ps_um_installtn_fs set um_glq_load_ind = $load_ind end-sql End-Procedure update-load-ind !*********************************************************************** ! truncate-tmp Procedure !*********************************************************************** Begin-Procedure truncate-tmp let $Calling_Procedure = 'truncate-tmp' do show-proc-start begin-sql truncate table ps_um_glq_rpt_tmp end-sql do show-proc-end End-Procedure truncate-tmp !*********************************************************************** ! delete-tmp Procedure !*********************************************************************** Begin-Procedure delete-tmp let $Calling_Procedure = 'delete-tmp' do show-proc-start begin-sql delete from ps_um_glq_rpt_tmp end-sql do show-proc-end End-Procedure delete-tmp !*********************************************************************** ! Process-Main-Fetch Procedure !*********************************************************************** Begin-Procedure Process-Main-Fetch let $Calling_Procedure = 'Process-Main-Fetch' let $show_addl = $where_clause do show-proc-start let #fy = 0 let #ap = 0 begin-select distinct ! on-error=sql-recover ! ??0707 a.fiscal_year, a.accounting_period let #fy = &a.fiscal_year let #ap = &a.accounting_period show 'Main loop - Fiscal_year: ' #fy ' Accounting Period: ' #ap ! do Delete-Prior-Load-Periods ! ??0607 ! do Update-Load-Times ! ??0607 ! do delete-tmp ! Clear the ps_um_glq_rpt_tmp table do truncate-tmp do Process-Commit do get-end-dt do Process-Inserts do Process-Commit do Update-Budget ! ??0807 do Process-Commit ! ??0807 if #ap > 0 if #ap = 998 let #PrevAP = 12 else let #PrevAP = #ap - 1 end-if ! do Update-Budget ! ??0807 do Update-Encum do Update-Totals do Process-Commit ! do Process-Balance-Forward do Run-Forward-Inserts-New do Process-Commit end-if let #PrevFY = #fy if #ap <> 0 do process-glq-reprting do Process-Commit end-if !###$$$ it may be faster to delete only the changes rows and re-add them on a nightly basis !###$$$ should the reporting load flag be updated here. or message be changed ! delete the FY and AP being process from the summary and reporting table do delete-period-data ! insert data into the summary table for FY and AP do Summary-Inserts ! Move data from ps_um_glq_rpt_tmp to ps_um_glq_reprting for FY and AP do tmp-to-reprting do Process-Commit from sysadm.ps_cal_detp_tbl A ! from ps_ledger a [$where_clause] [$ledger_999] and a.setid = 'UMSYS' and a.calendar_id = 'F1' and (exists (select 'x' from ps_ledger a1 where a1.fiscal_year = a.fiscal_year and a1.accounting_period = a.accounting_period) or exists (select 'x' from ps_ledger_budg a2 where a2.fiscal_year = a.fiscal_year and a2.accounting_period = a.accounting_period) ) UNION ! ??0707 select ! ??0707 a.fiscal_year, ! ??0707 a.accounting_period ! ??0707 from (select fiscal_year, 0 accounting_period ! ??0707 from ps_cal_detp_tbl ! ??0707 where (fiscal_year = #rc_fy ! ??0707 or fiscal_year = #curr_fy) ! ??0807 and setid = 'UMSYS' ! ??0707 and calendar_id = 'F1' ! ??0707 and accounting_period = 1 ! ??0707 UNION ! ??0707 select fiscal_year, 998 accounting_period ! ??0707 from ps_cal_detp_tbl ! ??0707 where (fiscal_year = #rc_fy - 1 ! ??0707 or fiscal_year = #curr_fy) ! ??0807 and setid = 'UMSYS' ! ??0707 and calendar_id = 'F1' ! ??0707 and accounting_period = 1 ! ??0707 ) a ! ??0707 [$where_clause] ! ??0707 [$ledger_999] ! ??0707 !order by a.fiscal_year, a.accounting_period ! ??0707 order by fiscal_year, accounting_period ! ??0707 end-select do show-proc-end End-Procedure Process-Main-Fetch !*********************************************************************** ! Process-Inserts Procedure !*********************************************************************** Begin-Procedure Process-Inserts let $Calling_Procedure = 'Process-Inserts - PS_LEDGER' do show-proc-start ! Insert all Actuals from the Ledger begin-sql INSERT INTO ps_um_glq_rpt_tmp ( SELECT distinct b1.FISCAL_YEAR ,b1.ACCOUNTING_PERIOD ,b1.BUSINESS_UNIT ,b1.DEPTID ,b1.PROJECT_ID ,b1.PROGRAM_CODE ,b1.FUND_CODE ,b1.OPERATING_UNIT ,b1.ACCOUNT ,nvl(c.descr,' ') ,b1.CLASS_FLD ,b1.STATISTICS_CODE ,x.tree_node_num ,y.tree_node ,0 ,0 ,SUM(b1.posted_total_amt) ,0 ,SUM(b1.posted_total_amt) ,0 ,0 ,'Y' ,'N' ,'N' ,'N' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ',' ','','',' ',' ' ! 06/19/09, 04/22/2010 FROM ps_ledger b1 , ps_gl_account_tbl c , PSTREEleaf x, PSTREENODE y WHERE b1.fiscal_year = #fy and b1.accounting_period = #ap ! and c.account(+) = b1.account and c.account = b1.account and c.effdt = (select max(cef.effdt) from ps_gl_account_tbl cef where c.setid = cef.setid and c.account = cef.account and cef.effdt <= $per_end_dt) ! AND c.eff_status(+) = 'A' ! AND c.setid(+) = 'UMSYS' ! AND c.eff_status = 'A' ! ??0508 AND c.setid = 'UMSYS' and y.TREE_NAME = 'ACCT_GL_INQ' and y.effdt = (select max(yef.effdt) ! ??0613 from pstreedefn yef ! ??0613 where yef.tree_name = y.tree_name ! ??0613 and yef.setid = y.setid ! ??0613 and yef.effdt <= $per_end_dt) ! ??0613 and b1.account between X.range_from and X.range_to and X.effdt = (select max(xef.effdt) from pstreeleaf xef where xef.setid = x.setid and xef.setcntrlvalue = X.setcntrlvalue and xef.tree_name = X.tree_name and xef.tree_node_num = x.tree_node_num and xef.range_from = x.range_from and xef.range_to = x.range_to and xef .tree_branch = x.tree_branch and xef.effdt <= $per_end_dt) and y.tree_name = x.tree_name and y.setid = x.setid and y.setcntrlvalue = x.setcntrlvalue and x.tree_node_num between y.tree_node_num and y.tree_node_num_end and y.effdt = x.effdt and y.tree_level_num = (select max(ylv.tree_level_num) from pstreenode ylv where ylv.setid = x.setid and ylv.setcntrlvalue = x.setcntrlvalue and ylv.tree_name = x.tree_name and ylv.effdt = x.effdt and x.tree_node_num between ylv.tree_node_num and ylv.tree_node_num_end) GROUP BY b1.FISCAL_YEAR ,b1.ACCOUNTING_PERIOD ,b1.BUSINESS_UNIT ,b1.DEPTID ,b1.PROJECT_ID , b1.PROGRAM_CODE ,b1.FUND_CODE ,b1.OPERATING_UNIT ,b1.ACCOUNT ,c.descr ,b1.CLASS_FLD, b1.statistics_code, x.tree_node_num ,y.tree_node) end-sql do Process-Commit do show-proc-end let $Calling_Procedure = 'Process-Inserts - PS_LEDGER_BUDG' do show-proc-start ! Insert wherever budget activity occurs, but no Actuals are in Ledger begin-sql INSERT INTO ps_um_glq_rpt_tmp ( SELECT ! /*+ rule */ /*+ leading(b1 rx c x y ) */ distinct b1.FISCAL_YEAR ,b1.ACCOUNTING_PERIOD ,b1.BUSINESS_UNIT ,b1.DEPTID ,b1.PROJECT_ID ,b1.PROGRAM_CODE ,b1.FUND_CODE ,b1.OPERATING_UNIT ,b1.ACCOUNT ,nvl(c.descr,' ') ,b1.CLASS_FLD ,b1.STATISTICS_CODE ,x.tree_node_num ,y.tree_node ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'N' ,'N' ,'N' ,'N' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ',' ','','',' ',' ' ! 06/19/2009, 04/22/2010 FROM ps_ledger_budg b1 , ps_gl_account_tbl c , PSTREEleaf x, PSTREENODE y WHERE b1.fiscal_year = #fy and b1.accounting_period = #ap and (b1.ledger = 'BUDGET' or b1.ledger = 'BASEBUD' or b1.ledger = 'FUTBUD') ! and c.account(+) = b1.account and c.account = b1.account and c.effdt = (select max(cef.effdt) from ps_gl_account_tbl cef where c.setid = cef.setid and c.account = cef.account and cef.effdt <= $per_end_dt) ! AND c.eff_status(+) = 'A' ! AND c.setid(+) = 'UMSYS' ! AND c.eff_status = 'A' ! ??0507 AND c.setid = 'UMSYS' and y.TREE_NAME = 'ACCT_GL_INQ' and y.effdt = (select max(yef.effdt) ! ??0613 from pstreedefn yef ! ??0613 where yef.tree_name = y.tree_name ! ??0613 and yef.setid = y.setid ! ??0613 and yef.effdt <= $per_end_dt) ! ??0613 and b1.account between X.range_from and X.range_to ! and X.effdt = (select max(xef.effdt) ! ??0613 ! from pstreeleaf xef ! ??0613 ! where xef.setid = x.setid ! ??0613 ! and xef.setcntrlvalue = X.setcntrlvalue ! ??0613 ! and xef.tree_name = X.tree_name ! ??0613 ! and xef.tree_node_num = x.tree_node_num ! ??0613 ! and xef.range_from = x.range_from ! ??0613 ! and xef.range_to = x.range_to ! ??0613 ! and xef .tree_branch = x.tree_branch ! ??0613 ! and xef.effdt <= $per_end_dt) ! ??0613 and y.tree_name = x.tree_name and y.setid = x.setid and y.setcntrlvalue = x.setcntrlvalue and x.tree_node_num between y.tree_node_num and y.tree_node_num_end and y.effdt = x.effdt and y.tree_level_num = (select max(ylv.tree_level_num) from pstreenode ylv where ylv.setid = x.setid and ylv.setcntrlvalue = x.setcntrlvalue and ylv.tree_name = x.tree_name and ylv.effdt = x.effdt and x.tree_node_num between ylv.tree_node_num and ylv.tree_node_num_end) ! AND NOT EXISTS (SELECT 'X' FROM ps_um_glq_rpt_tmp AND NOT EXISTS (SELECT /*+ UNNEST */ 'X' FROM ps_um_glq_rpt_tmp rx WHERE rx.FISCAL_YEAR = b1.FISCAL_YEAR AND rx.ACCOUNTING_PERIOD = b1.ACCOUNTING_PERIOD AND rx.BUSINESS_UNIT = b1.BUSINESS_UNIT AND rx.DEPTID = b1.DEPTID AND rx.PROJECT_ID = b1.PROJECT_ID AND rx.PROGRAM_CODE = b1.PROGRAM_CODE AND rx.FUND_CODE = b1.FUND_CODE AND rx.OPERATING_UNIT = b1.OPERATING_UNIT AND rx.ACCOUNT = b1.ACCOUNT AND rx.CLASS_FLD = b1.CLASS_FLD AND rx.STATISTICS_CODE = b1.STATISTICS_CODE ) ! GROUP BY b1.FISCAL_YEAR ,b1.ACCOUNTING_PERIOD ,b1.BUSINESS_UNIT ,b1.DEPTID , ! b1.PROJECT_ID ,b1.PROGRAM_CODE ,b1.FUND_CODE ,b1.OPERATING_UNIT ,b1.ACCOUNT , ! c.descr ,b1.CLASS_FLD,b1.STATISTICS_CODE, x.tree_node_num ,y.tree_node ) end-sql do Process-Commit do show-proc-end let $Calling_Procedure = 'Process-Inserts - PS_LEDGER_KK' do show-proc-start ! Insert wherever encumbrance activity occurs, but no Actuals are in Ledger begin-sql INSERT INTO ps_um_glq_rpt_tmp ( SELECT /*+ leading(b1 rt c x y ) */ distinct b1.FISCAL_YEAR ,b1.ACCOUNTING_PERIOD ,b1.BUSINESS_UNIT ,b1.DEPTID ,b1.PROJECT_ID ,b1.PROGRAM_CODE ,b1.FUND_CODE ,b1.OPERATING_UNIT ,b1.ACCOUNT ,nvl(c.descr,' ') ,b1.CLASS_FLD ,b1.STATISTICS_CODE ,x.tree_node_num ,y.tree_node ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'N' ,'N' ,'N' ,'N' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ',' ','','',' ',' ' ! 06/19/2009, 04/22/2010 FROM ps_ledger_kk b1 , ps_gl_account_tbl c , PSTREEleaf x, PSTREENODE y WHERE b1.fiscal_year = #fy and b1.accounting_period = #ap and (b1.ledger = 'CC_EXP_PRE' or b1.ledger = 'CC_EXP_ENC') ! and c.account(+) = b1.account and c.account = b1.account and c.effdt = (select max(cef.effdt) from ps_gl_account_tbl cef where c.setid = cef.setid and c.account = cef.account and cef.effdt <= $per_end_dt) ! AND c.eff_status(+) = 'A' ! AND c.setid(+) = 'UMSYS' ! AND c.eff_status = 'A' ! ??0507 AND c.setid = 'UMSYS' and y.TREE_NAME = 'ACCT_GL_INQ' and y.effdt = (select max(yef.effdt) ! ??0613 from pstreedefn yef ! ??0613 where yef.tree_name = y.tree_name ! ??0613 and yef.setid = y.setid ! ??0613 and yef.effdt <= $per_end_dt) ! ??0613 and b1.account between X.range_from and X.range_to and X.effdt = (select max(xef.effdt) from pstreeleaf xef where xef.setid = x.setid and xef.setcntrlvalue = X.setcntrlvalue and xef.tree_name = X.tree_name and xef.tree_node_num = x.tree_node_num and xef.range_from = x.range_from and xef.range_to = x.range_to and xef .tree_branch = x.tree_branch and xef.effdt <= $per_end_dt) and y.tree_name = x.tree_name and y.setid = x.setid and y.setcntrlvalue = x.setcntrlvalue and x.tree_node_num between y.tree_node_num and y.tree_node_num_end and y.effdt = x.effdt and y.tree_level_num = (select max(ylv.tree_level_num) from pstreenode ylv where ylv.setid = x.setid and ylv.setcntrlvalue = x.setcntrlvalue and ylv.tree_name = x.tree_name and ylv.effdt = x.effdt and x.tree_node_num between ylv.tree_node_num and ylv.tree_node_num_end) ! AND NOT EXISTS (SELECT 'X' FROM ps_um_glq_rpt_tmp AND NOT EXISTS (SELECT /*+ UNNEST */ 'X' FROM ps_um_glq_rpt_tmp rt WHERE rt.FISCAL_YEAR = b1.FISCAL_YEAR AND rt.ACCOUNTING_PERIOD = b1.ACCOUNTING_PERIOD AND rt.BUSINESS_UNIT = b1.BUSINESS_UNIT AND rt.DEPTID = b1.DEPTID AND rt.PROJECT_ID = b1.PROJECT_ID AND rt.PROGRAM_CODE = b1.PROGRAM_CODE AND rt.FUND_CODE = b1.FUND_CODE AND rt.OPERATING_UNIT = b1.OPERATING_UNIT AND rt.ACCOUNT = b1.ACCOUNT AND rt.CLASS_FLD = b1.CLASS_FLD AND rt.STATISTICS_CODE = b1.STATISTICS_CODE ) ! GROUP BY b1.FISCAL_YEAR ,b1.ACCOUNTING_PERIOD ,b1.BUSINESS_UNIT ,b1.DEPTID , ! b1.PROJECT_ID ,b1.PROGRAM_CODE ,b1.FUND_CODE ,b1.OPERATING_UNIT ,b1.ACCOUNT , ! c.descr ,b1.CLASS_FLD,b1.STATISTICS_CODE, x.tree_node_num ,y.tree_node ) end-sql do Process-Commit do show-proc-end End-Procedure Process-Inserts !*********************************************************************** ! Update-Budget Procedure !*********************************************************************** Begin-Procedure Update-Budget let $Calling_Procedure = 'Update-Budget' let $show_addl = edit(#FY,'9999') || ' and ' || edit(#AP,'99') do show-proc-start move 0 to #Ticker move 0 to #Ticker_Total BEGIN-SELECT SUM(T1.POSTED_TOTAL_AMT) &T1.Amount T1.BUSINESS_UNIT, T1.DEPTID, T1.PROJECT_ID, T1.PROGRAM_CODE, T1.FUND_CODE, T1.OPERATING_UNIT, T1.ACCOUNT, T1.CLASS_FLD, T1.STATISTICS_CODE, T1.LEDGER do Ticker-Tracker move &T1.Amount to #T1.Amount move &T1.BUSINESS_UNIT to $T1.BUSINESS_UNIT move &T1.DEPTID to $T1.DEPTID move &T1.PROJECT_ID to $T1.PROJECT_ID move &T1.PROGRAM_CODE to $T1.PROGRAM_CODE move &T1.FUND_CODE to $T1.FUND_CODE move &T1.OPERATING_UNIT to $T1.OPERATING_UNIT move &T1.ACCOUNT to $T1.ACCOUNT move &T1.CLASS_FLD to $T1.CLASS_FLD move &T1.LEDGER to $T1.LEDGER move &T1.STATISTICS_CODE to $T1.STATISTICS_CODE do Run-Budg-Update FROM PS_LEDGER_BUDG T1 WHERE T1.FISCAL_YEAR = #FY AND T1.ACCOUNTING_PERIOD = #AP and (t1.ledger = 'BASEBUD' or t1.ledger = 'BUDGET') GROUP BY T1.BUSINESS_UNIT, T1.DEPTID, T1.PROJECT_ID, T1.PROGRAM_CODE, T1.FUND_CODE, T1.OPERATING_UNIT, T1.ACCOUNT, T1.CLASS_FLD, T1.LEDGER, T1.STATISTICS_CODE END-SELECT do Process-Commit do show-proc-end End-Procedure Update-Budget !*********************************************************************** ! Run-Budg-Update Procedure !*********************************************************************** Begin-Procedure Run-Budg-Update let $Calling_Procedure = 'Run-Budg-Update' #debugf let $show_addl = edit(#FY,'9999') || ' and ' || edit(#AP,'99') #debugf do show-proc-start evaluate $T1.LEDGER when = 'BASEBUD' BEGIN-SQL UPDATE ps_um_glq_rpt_tmp SET UM_BASE_BUDGET = #T1.Amount , UM_BBUDG_ACTIVITY = 'Y' WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP AND BUSINESS_UNIT = $T1.BUSINESS_UNIT AND DEPTID = $T1.DEPTID AND PROJECT_ID = $T1.PROJECT_ID AND PROGRAM_CODE = $T1.PROGRAM_CODE AND FUND_CODE = $T1.FUND_CODE AND OPERATING_UNIT = $T1.OPERATING_UNIT AND ACCOUNT = $T1.ACCOUNT AND CLASS_FLD = $T1.CLASS_FLD AND STATISTICS_CODE = $T1.STATISTICS_CODE END-SQL when = 'BUDGET' BEGIN-SQL UPDATE ps_um_glq_rpt_tmp SET UM_CURR_BUDGET = #T1.Amount , UM_CBUDG_ACTIVITY = 'Y' WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP AND BUSINESS_UNIT = $T1.BUSINESS_UNIT AND DEPTID = $T1.DEPTID AND PROJECT_ID = $T1.PROJECT_ID AND PROGRAM_CODE = $T1.PROGRAM_CODE AND FUND_CODE = $T1.FUND_CODE AND OPERATING_UNIT = $T1.OPERATING_UNIT AND ACCOUNT = $T1.ACCOUNT AND CLASS_FLD = $T1.CLASS_FLD AND STATISTICS_CODE = $T1.STATISTICS_CODE END-SQL end-evaluate #debugf do show-proc-end End-Procedure Run-Budg-Update !*********************************************************************** ! Update-Encum Procedure !*********************************************************************** Begin-Procedure Update-Encum let $calling_procedure = 'Update-Encum' let $show_addl = edit(#FY,'9999') || ' and ' || edit(#AP,'99') do show-proc-start move 0 to #Ticker move 0 to #Ticker_Total BEGIN-SELECT SUM(T2.POSTED_TOTAL_AMT) &T2.Amount T2.BUSINESS_UNIT, T2.DEPTID, T2.PROJECT_ID, T2.PROGRAM_CODE, T2.FUND_CODE, T2.OPERATING_UNIT, T2.ACCOUNT, T2.CLASS_FLD, T2.STATISTICS_CODE, T2.LEDGER do Ticker-Tracker move &T2.Amount to #T2.Amount move &T2.BUSINESS_UNIT to $T2.BUSINESS_UNIT move &T2.DEPTID to $T2.DEPTID move &T2.PROJECT_ID to $T2.PROJECT_ID move &T2.PROGRAM_CODE to $T2.PROGRAM_CODE move &T2.FUND_CODE to $T2.FUND_CODE move &T2.OPERATING_UNIT to $T2.OPERATING_UNIT move &T2.ACCOUNT to $T2.ACCOUNT move &T2.CLASS_FLD to $T2.CLASS_FLD move &T2.STATISTICS_CODE to $T2.STATISTICS_CODE move &T2.LEDGER to $T2.LEDGER do Run-Encum-Update FROM PS_LEDGER_KK T2 WHERE T2.FISCAL_YEAR = #FY AND T2.ACCOUNTING_PERIOD = #AP and (t2.ledger = 'CC_EXP_PRE' or t2.ledger = 'CC_EXP_ENC') GROUP BY T2.BUSINESS_UNIT, T2.DEPTID, T2.PROJECT_ID, T2.PROGRAM_CODE, T2.FUND_CODE, T2.OPERATING_UNIT, T2.ACCOUNT, T2.CLASS_FLD, T2.LEDGER, T2.STATISTICS_CODE END-SELECT do Process-Commit do show-proc-end End-Procedure Update-Encum !*********************************************************************** ! Run-Encum-Update Procedure !*********************************************************************** Begin-Procedure Run-Encum-Update #debugf let $calling_procedure = 'Run-Encum-Update' #debugf do show-proc-start evaluate $T2.LEDGER when = 'CC_EXP_PRE' BEGIN-SQL UPDATE ps_um_glq_rpt_tmp SET UM_PRE_ENCUMBRANCE = #T2.Amount WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP AND BUSINESS_UNIT = $T2.BUSINESS_UNIT AND DEPTID = $T2.DEPTID AND PROJECT_ID = $T2.PROJECT_ID AND PROGRAM_CODE = $T2.PROGRAM_CODE AND FUND_CODE = $T2.FUND_CODE AND OPERATING_UNIT = $T2.OPERATING_UNIT AND ACCOUNT = $T2.ACCOUNT AND CLASS_FLD = $T2.CLASS_FLD AND STATISTICS_CODE = $T2.STATISTICS_CODE END-SQL when = 'CC_EXP_ENC' if #T2.Amount <> 0 BEGIN-SQL UPDATE ps_um_glq_rpt_tmp SET UM_TOT_ENCUMBRANCE = #T2.Amount , UM_ENCUM_ACTIVITY = 'Y' WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP AND BUSINESS_UNIT = $T2.BUSINESS_UNIT AND DEPTID = $T2.DEPTID AND PROJECT_ID = $T2.PROJECT_ID AND PROGRAM_CODE = $T2.PROGRAM_CODE AND FUND_CODE = $T2.FUND_CODE AND OPERATING_UNIT = $T2.OPERATING_UNIT AND ACCOUNT = $T2.ACCOUNT AND CLASS_FLD = $T2.CLASS_FLD AND STATISTICS_CODE = $T2.STATISTICS_CODE END-SQL end-if end-evaluate #debugf do show-proc-end End-Procedure Run-Encum-Update !*********************************************************************** ! Update-Totals Procedure !*********************************************************************** Begin-Procedure Update-Totals let $calling_procedure = 'Update-Totals' let $show_addl = edit(#FY,'9999') || ' and ap = ' || edit(#AP,'99') || ' and prev ap = ' || edit(#prevAP,'99') do show-proc-start move 0 to #Ticker move 0 to #Ticker_Total BEGIN-SELECT U.BUSINESS_UNIT U.DEPTID U.PROJECT_ID U.PROGRAM_CODE U.FUND_CODE U.OPERATING_UNIT U.ACCOUNT U.CLASS_FLD U.STATISTICS_CODE U.UM_ACTUALS_CURR U.UM_BASE_BUDGET U.UM_CURR_BUDGET U.UM_PROJECT_TO_DT U.UM_PRE_ENCUMBRANCE U.UM_TOT_ENCUMBRANCE P.UM_BASE_BUDGET P.UM_CURR_BUDGET P.UM_ACTUALS_YTD P.UM_PROJECT_TO_DT P.UM_PRE_ENCUMBRANCE P.UM_TOT_ENCUMBRANCE do Ticker-Tracker let #P.UM_BASE_BUDGET = 0 let #P.UM_CURR_BUDGET = 0 let #P.UM_ACTUALS_YTD = 0 let #P.UM_PROJECT_TO_DT = 0 let #P.UM_PRE_ENCUMBRANCE = 0 let #P.UM_TOT_ENCUMBRANCE = 0 if not isnull(&P.UM_BASE_BUDGET) move &P.UM_BASE_BUDGET to #P.UM_BASE_BUDGET end-if if not isnull(&P.UM_CURR_BUDGET) move &P.UM_CURR_BUDGET to #P.UM_CURR_BUDGET end-if if not isnull(&P.UM_ACTUALS_YTD) move &P.UM_ACTUALS_YTD to #P.UM_ACTUALS_YTD end-if if not isnull(&P.UM_PROJECT_TO_DT) move &P.UM_PROJECT_TO_DT to #P.UM_PROJECT_TO_DT end-if if not isnull(&P.UM_PRE_ENCUMBRANCE) move &P.UM_PRE_ENCUMBRANCE to #P.UM_PRE_ENCUMBRANCE end-if if not isnull(&P.UM_TOT_ENCUMBRANCE) move &P.UM_TOT_ENCUMBRANCE to #P.UM_TOT_ENCUMBRANCE end-if move &U.BUSINESS_UNIT to $U.BUSINESS_UNIT move &U.DEPTID to $U.DEPTID move &U.PROJECT_ID to $U.PROJECT_ID move &U.PROGRAM_CODE to $U.PROGRAM_CODE move &U.FUND_CODE to $U.FUND_CODE move &U.OPERATING_UNIT to $U.OPERATING_UNIT move &U.ACCOUNT to $U.ACCOUNT move &U.CLASS_FLD to $U.CLASS_FLD move &U.STATISTICS_CODE to $U.STATISTICS_CODE move &U.UM_ACTUALS_CURR to #U.UM_ACTUALS_CURR move &U.UM_BASE_BUDGET to #U.UM_BASE_BUDGET move &U.UM_CURR_BUDGET to #U.UM_CURR_BUDGET move &U.UM_PROJECT_TO_DT to #U.UM_PROJECT_TO_DT move &U.UM_PRE_ENCUMBRANCE to #U.UM_PRE_ENCUMBRANCE move &U.UM_TOT_ENCUMBRANCE to #U.UM_TOT_ENCUMBRANCE do Run-Tot-Update FROM ps_um_glq_rpt_tmp U LEFT OUTER JOIN ps_um_glq_reprting P ON P.FISCAL_YEAR = U.FISCAL_YEAR AND P.BUSINESS_UNIT = U.BUSINESS_UNIT AND P.DEPTID = U.DEPTID AND P.PROJECT_ID = U.PROJECT_ID AND P.PROGRAM_CODE = U.PROGRAM_CODE AND P.FUND_CODE = U.FUND_CODE AND P.OPERATING_UNIT = U.OPERATING_UNIT AND P.ACCOUNT = U.ACCOUNT AND P.CLASS_FLD = U.CLASS_FLD AND P.STATISTICS_CODE = U.STATISTICS_CODE AND P.ACCOUNTING_PERIOD = #PrevAP WHERE U.FISCAL_YEAR = #FY AND U.ACCOUNTING_PERIOD = #AP END-SELECT do show-proc-end End-Procedure Update-Totals !*********************************************************************** ! Run-Tot-Update Procedure !*********************************************************************** Begin-Procedure Run-Tot-Update let $Calling_Procedure = 'Run-Tot-Update' #debugt let $show_addl = edit(#FY,'9999') || ' and ' || edit(#AP,'99') #debugt show-proc-start LET #ACCOUNT = to_number($U.ACCOUNT) if ($U.FUND_CODE = '12' or $U.FUND_CODE = '22') AND #ACCOUNT >= 40000 AND #ACCOUNT <= 89999 AND #AP = 4 let #counter = #counter + 1 BEGIN-SQL on-error=sql-recover UPDATE ps_um_glq_rpt_tmp SET UM_PRE_ENCUMBRANCE = (#P.UM_PRE_ENCUMBRANCE + #U.UM_PRE_ENCUMBRANCE) , UM_TOT_ENCUMBRANCE = (#P.UM_TOT_ENCUMBRANCE + #U.UM_TOT_ENCUMBRANCE) , UM_ACTUALS_YTD = (#P.UM_ACTUALS_YTD + #U.UM_ACTUALS_CURR) WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP AND BUSINESS_UNIT = $U.BUSINESS_UNIT AND DEPTID = $U.DEPTID AND PROJECT_ID = $U.PROJECT_ID AND PROGRAM_CODE = $U.PROGRAM_CODE AND FUND_CODE = $U.FUND_CODE AND OPERATING_UNIT = $U.OPERATING_UNIT AND ACCOUNT = $U.ACCOUNT AND CLASS_FLD = $U.CLASS_FLD AND STATISTICS_CODE = $U.STATISTICS_CODE END-SQL else BEGIN-SQL on-error=sql-recover UPDATE ps_um_glq_rpt_tmp SET UM_BASE_BUDGET = (#P.UM_BASE_BUDGET + #U.UM_BASE_BUDGET) , UM_CURR_BUDGET = (#P.UM_CURR_BUDGET + #U.UM_CURR_BUDGET) , UM_PRE_ENCUMBRANCE = (#P.UM_PRE_ENCUMBRANCE + #U.UM_PRE_ENCUMBRANCE) , UM_TOT_ENCUMBRANCE = (#P.UM_TOT_ENCUMBRANCE + #U.UM_TOT_ENCUMBRANCE) , UM_ACTUALS_YTD = (#P.UM_ACTUALS_YTD + #U.UM_ACTUALS_CURR) , UM_PROJECT_TO_DT = (#P.UM_PROJECT_TO_DT + #U.UM_PROJECT_TO_DT) WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP AND BUSINESS_UNIT = $U.BUSINESS_UNIT AND DEPTID = $U.DEPTID AND PROJECT_ID = $U.PROJECT_ID AND PROGRAM_CODE = $U.PROGRAM_CODE AND FUND_CODE = $U.FUND_CODE AND OPERATING_UNIT = $U.OPERATING_UNIT AND ACCOUNT = $U.ACCOUNT AND CLASS_FLD = $U.CLASS_FLD AND STATISTICS_CODE = $U.STATISTICS_CODE END-SQL end-if #debugt show-proc-end End-Procedure Run-Tot-Update !*********************************************************************** ! Delete-Period-Data Procedure !*********************************************************************** Begin-Procedure Delete-Period-Data let $calling_procedure = 'Delete-Period-Data' let $show_addl = edit(#FY,'9999') || ' and ' || edit(#AP,'99') do show-proc-start begin-sql delete ps_um_glq_reprting WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP; delete ps_um_glq_summary WHERE FISCAL_YEAR = #FY AND ACCOUNTING_PERIOD = #AP; end-sql do show-proc-end End-procedure Delete-Period-Data !*********************************************************************** ! Summary-Inserts Procedure !*********************************************************************** Begin-Procedure Summary-Inserts let $Calling_Procedure = 'Summary-Inserts' let $show_addl = edit(#FY,'9999') || ' and ' || edit(#AP,'99') do show-proc-start BEGIN-SQL on-error=sql-recover INSERT INTO PS_UM_GLQ_SUMMARY ( SELECT c.setid , c.tree_name , c.tree_level_num , c.tree_node , c.tree_node_num , c.tree_node_num_end , nvl(d.descr, ' ') , c.parent_node_num , a.FISCAL_YEAR ,a.ACCOUNTING_PERIOD ,a.BUSINESS_UNIT ,a.DEPTID ,a.PROJECT_ID ,a.PROGRAM_CODE ,a.FUND_CODE ,a.OPERATING_UNIT ,a.STATISTICS_CODE ,SUM(a.um_base_budget) ,SUM(a.um_curr_budget) ,SUM(a.um_actuals_curr) ,SUM(a.um_actuals_ytd) ,SUM(a.um_project_to_dt) ,SUM(a.um_pre_encumbrance) ,SUM(a.um_tot_encumbrance) ,MAX(a.um_actual_activity) ,MAX(a.um_bbudg_activity) ,MAX(a.um_cbudg_activity) ,MAX(a.um_encum_activity) FROM ps_um_glq_rpt_tmp a , pstreedefn b , pstreenode c , ps_tree_node_tbl d WHERE A.FISCAL_YEAR = #FY AND A.ACCOUNTING_PERIOD = #AP AND b.tree_name = 'ACCT_GL_INQ' AND c.setid = b.setid AND b.effdt = (select max(yef.effdt) ! ??0613 from pstreedefn yef ! ??0613 where yef.tree_name = b.tree_name ! ??0613 and yef.effdt <= $per_end_dt ! ??0613 and yef.setid = b.setid) ! ??0613 AND c.tree_name = b.tree_name AND c.effdt = b.effdt AND d.tree_node(+) = c.tree_node AND d.setid(+) = c.setid AND d.eff_status(+) = 'A' AND a.root_node_num BETWEEN c.tree_node_num AND c.tree_node_num_end GROUP BY c.setid , c.tree_name , c.tree_node , c.tree_node_num , c.tree_node_num_end , c.tree_level_num , d.descr , c.parent_node_num , a.FISCAL_YEAR ,a.ACCOUNTING_PERIOD ,a.BUSINESS_UNIT ,a.DEPTID ,a.PROJECT_ID ,a.PROGRAM_CODE ,a.FUND_CODE ,a.OPERATING_UNIT ,a.STATISTICS_CODE) END-SQL do show-proc-end End-Procedure Summary-Inserts !*********************************************************************** ! Run-Forward-Inserts-New Procedure !*********************************************************************** Begin-Procedure Run-Forward-Inserts-New let $Calling_Procedure = 'Run-Forward-Inserts-New' let $show_addl = edit(#FY,'9999') || ' and ' || edit(#AP,'99') || ' and ' || edit(#prevAP,'99') do show-proc-start if #ap = 4 let $Priorperiod1 = 'AND NOT (X.FUND_CODE IN (' || '''' || '12' || '''' || ',' let $Priorperiod1 = $Priorperiod1 || '''' || '22' || '''' || ') AND X.ACCOUNT between 40000 AND 89999)' let $Priorperiod2 = 'AND X1.FUND_CODE IN (' || '''' || '12' || '''' || ',' let $Priorperiod2 = $Priorperiod2 || '''' || '22' || '''' || ') AND X1.ACCOUNT between 40000 AND 89999' show 'Addtional where clause for period 4 processing....' show $Priorperiod1 show $Priorperiod2 else let $Priorperiod1 = '' let $Priorperiod2 = '' end-if begin-sql on-error=sql-recover INSERT INTO ps_um_glq_rpt_tmp (select /* index (pscum_glq_reprting) */ #fy , #ap , x.BUSINESS_UNIT , x.DEPTID , x.PROJECT_ID , x.PROGRAM_CODE , x.FUND_CODE , x.OPERATING_UNIT , x.ACCOUNT , x.DESCR , x.CLASS_FLD , x.STATISTICS_CODE , x.ROOT_NODE_NUM , x.TREE_NODE , x.UM_BASE_BUDGET , x.UM_CURR_BUDGET , 0 , x.UM_ACTUALS_YTD , x.UM_PROJECT_TO_DT , x.UM_PRE_ENCUMBRANCE , x.UM_TOT_ENCUMBRANCE , 'N' , 'N' , 'N' , 'N' , x.AMT1 , x.AMT2 , x.AMT3 , x.AMT4 , x.UM_FUTURE_BUDGET , x.UM_FUTURE_BUDGET_1 , x.UM_FUTURE_BUDGET_2 , x.UM_CURR_BUDGET_LY , x.UM_ACTUALS_YTD_LY , x.UM_CURR_BUDGET_PY , x.UM_ACTUALS_YTD_PY , x.UM_BU_DESCR , x.UM_BU_DESCRSHORT , x.UM_DEPT_STATUS , x.UM_DEPT_DESCR , x.UM_DEPT_DESCR_SHRT , x.UM_DEPT_MGR , x.UM_DEPT_YREND , x.UM_DEPT_YREND_D , x.UM_DEPT_CR_1 , x.UM_DEPT_CR_1_D , x.UM_DEPT_CR_2 , x.UM_DEPT_CR_2_D , x.UM_DEPT_CR_3 , x.UM_DEPT_CR_3_D , x.UM_DEPT_CR_4 , x.UM_DEPT_CR_4_D , x.UM_DEPT_EXP2 , x.UM_DEPT_EXP2_D , x.UM_DEPT_EXP4 , x.UM_DEPT_EXP4_D , x.UM_PROJ_EFF_STATUS , x.UM_PROJ_DESCR , x.UM_PROJ_MGR_ID , x.UM_PROJ_MGR_NAME , x.UM_PROJ_START_DT , x.UM_PROJ_END_DT , x.PROJECT_STATUS , x.UM_PROJ_FACIL_ID , x.UM_PROJ_FACIL_ID_D , x.UM_PROJ_BILL_CD , x.UM_PROJ_BILL_CD_D , x.UM_PROJ_CFDA_N , x.UM_PROJ_CFDA_N_D , x.UM_PROJ_FED_AGCY , x.UM_PROJ_FED_AGCY_D , x.UM_PROJ_GRANT_N , x.UM_PROJ_GRANT_N_D , x.UM_PROJ_GC_TYPE , x.UM_PROJ_GC_TYPE_D , x.UM_PROJ_DEPR_ST , x.UM_PROJ_DEPR_ST_D , x.UM_PROJ_LOC_NUM , x.UM_PROJ_LOC_NUM_D , x.UM_PROJ_IN_SRVC , x.UM_PROJ_EXPENSE , x.UM_PROJ_EXPENSE_D , x.UM_PROJ_CATEGORY , x.UM_PROJ_CATEGORY_D , x.UM_PROJ_MAJSRC , x.UM_PROJ_MAJSRC_D , x.UM_PROJ_MINSRC , x.UM_PROJ_MINSRC_D , x.UM_PROJ_LOC_SRCE , x.UM_PROJ_LOC_SRCE_D , x.UM_PROJ_GROUP , x.UM_PROJ_GROUP_D , x.UM_PROJ_SUBGROUP , x.UM_PROJ_SUBGROUP_D , x.UM_PROJ_CAP_TYPE , x.UM_PROJ_CAP_TYPE_D , x.UM_PROG_STATUS , x.UM_PROG_DESCR , x.UM_PROG_DESCR_SHRT , x.UM_PROG_MGR_NAME , x.UM_PROG_YREND , x.UM_PROG_YREND_D , x.UM_PROG_EXPENSE , x.UM_PROG_EXPENSE_D , x.UM_PROG_CAT1 , x.UM_PROG_CAT1_D , x.UM_PROG_CAT2 , x.UM_PROG_CAT2_D , x.UM_PROG_CAT3 , x.UM_PROG_CAT3_D , x.UM_PROG_CAT4 , x.UM_PROG_CAT4_D , x.UM_PROG_RESTR_1 , x.UM_PROG_RESTR_1_D , x.UM_PROG_RESTR_2 , x.UM_PROG_RESTR_2_D , x.UM_PROG_RESTR_3 , x.UM_PROG_RESTR_3_D , x.UM_FUND_STATUS , x.UM_FUND_DESCR , x.UM_FUND_DESCRSHRT , x.UM_OPUN_STATUS , x.UM_OPUN_DESCR , x.UM_OPUN_DESCRSHRT , x.UM_ACCT_STATUS , x.UM_ACCT_DESCR , x.UM_ACCT_DESCRSHRT , x.UM_ACCT_BAL_CLS , x.UM_ACCT_BAL_CLS_D , x.UM_ACCT_CASH_FLW , x.UM_ACCT_CASH_FLW_D , x.UM_ACCT_FBAL_CLS , x.UM_ACCT_FBAL_CLS_D , x.UM_ACCT_REV2 , x.UM_ACCT_REV2_D , x.UM_ACCT_REV4 , x.UM_ACCT_REV4_D , x.UM_ACCT_YREND , x.UM_ACCT_YREND_D , x.UM_CLASS_D , x.UM_PROG_ENDOW_DIST ! ??0507 , x.UM_PROG_TI_INC_DST ! ??0507 , x.UM_PROJ_BLD_LIFE ! ??0507 , x.UM_PROJ_FIN_CONT ! ??0507 , x.UM_PROJ_FIN_CONT_D ! ??0507 , x.UM_PROJ_GC_RPT ! ??0507 , x.UM_PROJ_GC_RPT_D ! ??0507 , x.UM_PROJ_IMPVT_LIFE ! ??0507 , x.UM_PROJ_LOC_EXPIRE ! ??0507 , x.UM_PROJ_CLOSNCOMBO ! 06/19/2009 , x.UM_PROJ_CLSCOMBO_D ! 06/19/2009 , x.UM_PROJ_MGR_EFFDT ! ??0507 , x.UM_PROJ_STAT_EFFDT ! ??0507 , x.UM_DEPT_HR ! 04/22/2010 , X.UM_DEPT_HR_D ! 04/22/2010 FROM ps_um_glq_reprting x WHERE x.FISCAL_YEAR = #FY AND x.ACCOUNTING_PERIOD = #PrevAP [$Priorperiod1] AND NOT EXISTS (SELECT 'X' FROM ps_um_glq_rpt_tmp bne WHERE #fy = bne.FISCAL_YEAR AND #ap = bne.ACCOUNTING_PERIOD AND x.BUSINESS_UNIT = bne.BUSINESS_UNIT AND x.DEPTID = bne.DEPTID AND x.PROJECT_ID = bne.PROJECT_ID AND x.PROGRAM_CODE = bne.PROGRAM_CODE AND x.FUND_CODE = bne.FUND_CODE AND x.OPERATING_UNIT = bne.OPERATING_UNIT AND x.ACCOUNT = bne.ACCOUNT AND x.CLASS_FLD = bne.CLASS_FLD AND x.STATISTICS_CODE = bne.STATISTICS_CODE )) end-sql if #ap = 4 begin-sql on-error=sql-recover INSERT INTO ps_um_glq_rpt_tmp (select /* index (pscum_glq_reprting) */ #fy , #ap , x1.BUSINESS_UNIT , x1.DEPTID , x1.PROJECT_ID , x1.PROGRAM_CODE , x1.FUND_CODE , x1.OPERATING_UNIT , x1.ACCOUNT , x1.DESCR , x1.CLASS_FLD , x1.STATISTICS_CODE , x1.ROOT_NODE_NUM , x1.TREE_NODE , 0 , 0 , 0 , x1.UM_ACTUALS_YTD , 0 , x1.UM_PRE_ENCUMBRANCE , x1.UM_TOT_ENCUMBRANCE , 'N' , 'N' , 'N' , 'N' , x1.AMT1 , x1.AMT2 , x1.AMT3 , x1.AMT4 , x1.UM_FUTURE_BUDGET , x1.UM_FUTURE_BUDGET_1 , x1.UM_FUTURE_BUDGET_2 , x1.UM_CURR_BUDGET_LY , x1.UM_ACTUALS_YTD_LY , x1.UM_CURR_BUDGET_PY , x1.UM_ACTUALS_YTD_PY , x1.UM_BU_DESCR , x1.UM_BU_DESCRSHORT , x1.UM_DEPT_STATUS , x1.UM_DEPT_DESCR , x1.UM_DEPT_DESCR_SHRT , x1.UM_DEPT_MGR , x1.UM_DEPT_YREND , x1.UM_DEPT_YREND_D , x1.UM_DEPT_CR_1 , x1.UM_DEPT_CR_1_D , x1.UM_DEPT_CR_2 , x1.UM_DEPT_CR_2_D , x1.UM_DEPT_CR_3 , x1.UM_DEPT_CR_3_D , x1.UM_DEPT_CR_4 , x1.UM_DEPT_CR_4_D , x1.UM_DEPT_EXP2 , x1.UM_DEPT_EXP2_D , x1.UM_DEPT_EXP4 , x1.UM_DEPT_EXP4_D , x1.UM_PROJ_EFF_STATUS , x1.UM_PROJ_DESCR , x1.UM_PROJ_MGR_ID , x1.UM_PROJ_MGR_NAME , x1.UM_PROJ_START_DT , x1.UM_PROJ_END_DT , x1.PROJECT_STATUS , x1.UM_PROJ_FACIL_ID , x1.UM_PROJ_FACIL_ID_D , x1.UM_PROJ_BILL_CD , x1.UM_PROJ_BILL_CD_D , x1.UM_PROJ_CFDA_N , x1.UM_PROJ_CFDA_N_D , x1.UM_PROJ_FED_AGCY , x1.UM_PROJ_FED_AGCY_D , x1.UM_PROJ_GRANT_N , x1.UM_PROJ_GRANT_N_D , x1.UM_PROJ_GC_TYPE , x1.UM_PROJ_GC_TYPE_D , x1.UM_PROJ_DEPR_ST , x1.UM_PROJ_DEPR_ST_D , x1.UM_PROJ_LOC_NUM , x1.UM_PROJ_LOC_NUM_D , x1.UM_PROJ_IN_SRVC , x1.UM_PROJ_EXPENSE , x1.UM_PROJ_EXPENSE_D , x1.UM_PROJ_CATEGORY , x1.UM_PROJ_CATEGORY_D , x1.UM_PROJ_MAJSRC , x1.UM_PROJ_MAJSRC_D , x1.UM_PROJ_MINSRC , x1.UM_PROJ_MINSRC_D , x1.UM_PROJ_LOC_SRCE , x1.UM_PROJ_LOC_SRCE_D , x1.UM_PROJ_GROUP , x1.UM_PROJ_GROUP_D , x1.UM_PROJ_SUBGROUP , x1.UM_PROJ_SUBGROUP_D , x1.UM_PROJ_CAP_TYPE , x1.UM_PROJ_CAP_TYPE_D , x1.UM_PROG_STATUS , x1.UM_PROG_DESCR , x1.UM_PROG_DESCR_SHRT , x1.UM_PROG_MGR_NAME , x1.UM_PROG_YREND , x1.UM_PROG_YREND_D , x1.UM_PROG_EXPENSE , x1.UM_PROG_EXPENSE_D , x1.UM_PROG_CAT1 , x1.UM_PROG_CAT1_D , x1.UM_PROG_CAT2 , x1.UM_PROG_CAT2_D , x1.UM_PROG_CAT3 , x1.UM_PROG_CAT3_D , x1.UM_PROG_CAT4 , x1.UM_PROG_CAT4_D , x1.UM_PROG_RESTR_1 , x1.UM_PROG_RESTR_1_D , x1.UM_PROG_RESTR_2 , x1.UM_PROG_RESTR_2_D , x1.UM_PROG_RESTR_3 , x1.UM_PROG_RESTR_3_D , x1.UM_FUND_STATUS , x1.UM_FUND_DESCR , x1.UM_FUND_DESCRSHRT , x1.UM_OPUN_STATUS , x1.UM_OPUN_DESCR , x1.UM_OPUN_DESCRSHRT , x1.UM_ACCT_STATUS , x1.UM_ACCT_DESCR , x1.UM_ACCT_DESCRSHRT , x1.UM_ACCT_BAL_CLS , x1.UM_ACCT_BAL_CLS_D , x1.UM_ACCT_CASH_FLW , x1.UM_ACCT_CASH_FLW_D , x1.UM_ACCT_FBAL_CLS , x1.UM_ACCT_FBAL_CLS_D , x1.UM_ACCT_REV2 , x1.UM_ACCT_REV2_D , x1.UM_ACCT_REV4 , x1.UM_ACCT_REV4_D , x1.UM_ACCT_YREND , x1.UM_ACCT_YREND_D , x1.UM_CLASS_D , x1.UM_PROG_ENDOW_DIST ! ??0507 , x1.UM_PROG_TI_INC_DST ! ??0507 , x1.UM_PROJ_BLD_LIFE ! ??0507 , x1.UM_PROJ_FIN_CONT ! ??0507 , x1.UM_PROJ_FIN_CONT_D ! ??0507 , x1.UM_PROJ_GC_RPT ! ??0507 , x1.UM_PROJ_GC_RPT_D ! ??0507 , x1.UM_PROJ_IMPVT_LIFE ! ??0507 , x1.UM_PROJ_LOC_EXPIRE ! ??0507 , x1.UM_PROJ_CLOSNCOMBO ! 06/19/2009 , x1.UM_PROJ_CLSCOMBO_D ! 06/19/2009 , x1.UM_PROJ_MGR_EFFDT ! ??0507 , x1.UM_PROJ_STAT_EFFDT ! ??0507 , x1.UM_DEPT_HR ! 04/22/2010 , x1.UM_DEPT_HR_D ! 04/22/2010 FROM ps_um_glq_reprting x1 WHERE x1.FISCAL_YEAR = #FY AND x1.ACCOUNTING_PERIOD = #PrevAP [$Priorperiod2] AND NOT EXISTS (SELECT 'X' FROM ps_um_glq_rpt_tmp bnee WHERE #fy = bnee.FISCAL_YEAR AND #ap = bnee.ACCOUNTING_PERIOD AND x1.BUSINESS_UNIT = bnee.BUSINESS_UNIT AND x1.DEPTID = bnee.DEPTID AND x1.PROJECT_ID = bnee.PROJECT_ID AND x1.PROGRAM_CODE = bnee.PROGRAM_CODE AND x1.FUND_CODE = bnee.FUND_CODE AND x1.OPERATING_UNIT = bnee.OPERATING_UNIT AND x1.ACCOUNT = bnee.ACCOUNT AND x1.CLASS_FLD = bnee.CLASS_FLD AND x1.STATISTICS_CODE = bnee.STATISTICS_CODE )) end-sql end-if do show-proc-end End-Procedure Run-Forward-Inserts-New !************************************************************************ ! Procedure get-run-control !************************************************************************ ! Business unit is not currently used begin-procedure get-run-control let $Calling_Procedure = 'get-run-control' if #prcs_process_instance > 0 begin-select on-error=sql-recover rc.fiscal_year, rc.accounting_period, rc.business_unit let #rc_fy = &rc.fiscal_year let #rc_ap = &rc.accounting_period let $rc_bu = rtrim(&rc.business_unit,' ') from ps_um_run_glrptg rc where rc.oprid = $prcs_oprid and rc.run_cntl_id = $prcs_run_cntl_id end-select else ! input $rc_bu 'Business Unit (ex. UMS01)' input #rc_fy 'Fiscal Year (ex. 2005)' input #rc_ap 'Accounting Period (ex. 7)' ! let $business_unit = upper($business_unit) end-if ! build a where clause based upon the fiscal year and accounting period ! the $where_clause works for the initial select of PS_LEDGER records, ! as well as the deletion of records from PS_GLQ tables. ! the $ledger_999 field contains a 'where', or an 'and' clause that is ! specific to the PS_LEDGER select. It eliminates the ap = 999 rows. ! Here are some examples.... ! #rc_fy = 2004 , #rc_ap = 12 : Process fy 2004 ap 12 ONLY ! #rc_fy = 9999 , #rc_ap = 99 : Process all fy's and ap's represented in the Ledger table ! #rc_fy = 2004 , #rc_ap = 99 : Process all ap's for fy 2004 ! #rc_fy = 0 , #rc_ap = 0 : Find the 'current fy/ap and process that one plus the one ! immediately preceeding it. Like 2005/04 and 2005/03 ! or 2005/01 and 2004/12 let $where_clause = '' let $ledger_999 = '' if #rc_fy > 0 and #rc_fy <> 9999 let $where_clause = 'where fiscal_year = ' || to_char(#rc_fy) if #rc_ap <> 99 let $where_clause = $where_clause || ' and accounting_period = ' || to_char(#rc_ap) end-if end-if let #curr_fy = 0 ! ??0807 if #rc_fy = 0 do get-curr-ap if #loadprev = 998 let #curr_ap = 12 end-if evaluate #curr_ap when > 4 let #curr_ap_prev = #curr_ap - 1 let $where_clause = 'where fiscal_year = ' || to_char(#curr_fy) let $where_clause = $where_clause || ' and (accounting_period = ' || to_char(#curr_ap) let $where_clause = $where_clause || ' or accounting_period = ' || to_char(#curr_ap_prev) let $where_clause = $where_clause || ' )' break when = 4 let #curr_ap_prev = #curr_ap - 1 let #curr_ap_prev_prev = #curr_ap_prev - 1 let #curr_ap_prev_prev_prev = #curr_ap_prev_prev - 1 let $where_clause = 'where fiscal_year = ' || to_char(#curr_fy) let $where_clause = $where_clause || ' and (accounting_period = ' || to_char(#curr_ap) let $where_clause = $where_clause || ' or accounting_period = ' || to_char(#curr_ap_prev) let $where_clause = $where_clause || ' or accounting_period = ' || to_char(#curr_ap_prev_prev) let $where_clause = $where_clause || ' or accounting_period = ' || to_char(#curr_ap_prev_prev_prev) let $where_clause = $where_clause || ' )' break when = 3 ! ??0907 month thre should behave like months 1 and 2 ! let #curr_ap_prev = #curr_ap - 1 ! let #curr_ap_prev_prev = #curr_ap_prev - 1 ! let $where_clause = 'where fiscal_year = ' || to_char(#curr_fy) ! let $where_clause = $where_clause || ' and (accounting_period = ' || to_char(#curr_ap) ! let $where_clause = $where_clause || ' or accounting_period = ' || to_char(#curr_ap_prev) ! let $where_clause = $where_clause || ' or accounting_period = ' || to_char(#curr_ap_prev_prev) ! let $where_clause = $where_clause || ' )' ! break when < 3 let $where_clause = 'where (fiscal_year = ' || to_char(#curr_fy) let $where_clause = $where_clause || ' and accounting_period <= ' || to_char(#curr_ap) || ')' let $where_clause = $where_clause || ' or (fiscal_year = ' || to_char(#curr_fy - 1) || ' and accounting_period between 12 and 998)' break end-evaluate end-if if $where_clause = '' let $ledger_999 = 'where accounting_period < 999' else if instr($where_clause,'accounting_period',1) = 0 let $ledger_999 = 'and accounting_period < 999' end-if end-if show 'Where_clause: ' $where_clause show '$ledger_999: ' $ledger_999 end-procedure get-run-control !*********************************************************************** ! Procedure process-glq-reprting !*********************************************************************** Begin-Procedure process-glq-reprting let #l_fy = #fy - 1 ! last fiscal year (1 year ago) let #p_fy = #fy - 2 ! previous fiscal year (2 years ago) let #Ticker = 0 let #Ticker_total = 0 let #c_tckr = 0 let $Calling_Procedure = 'process-glq-reprting' let $show_addl = 'ly/py:' || edit(#l_fy,'9999') || '/' || edit(#l_py,'9999') do show-proc-start let $hold_time = edit($time_s,'SYYYYMMDDHH24MISSNNNNNN') let $for_update = 'FOR UPDATE' ! do these one time per fiscal year/accounting period combination do get-end-dt do build-lookups do insert-chartattrs-temptable do insert-chartflds-temptable begin-select on-error=sql-recover ra.fiscal_year, ra.accounting_period ra.business_unit, ra.deptid, ra.project_id, ra.program_code, ra.fund_code, ra.operating_unit, ra.account, ra.class_fld, ra.statistics_code ! let $Calling_Procedure = 'process-glq-reprting-loop' ! let $show_addl = 'ly/py:' || edit(#l_fy,'9999') || '/' || edit(#l_py,'9999') ! do show-proc-start do Ticker-Tracker ! some previous and future numbers let $init_this = 'NBR' do init-fields do get-ly-py-stuff do get-futbud ! chart field descriptions, attributes and 'tree' attributes, etc. if $Prev_Business_Unit <> &ra.business_unit let $Prev_Business_Unit = &ra.business_unit let $bu_descr = ' ' let $bu_descrs = ' ' do get-bu-stuff end-if if rtrim(&ra.project_id,' ') <> '' if $Prev_Project_ID <> &ra.project_id let $Prev_Project_ID = &ra.project_id let $init_this = 'PRJ' do init-fields do get-project-stuff let $chart_nm = 'PROJECT_ID' let $chart_vlu = &ra.project_id do get-chart-attrs let $tree_nm = 'UMS_PROJECT' let $chart_vlu = &ra.project_id do get-chart-tree-attrs end-if else let $Prev_Project_ID = ' ' let $init_this = 'PRJ' do init-fields end-if ! dept is required.... never blank if $Prev_DeptID <> &ra.deptid let $Prev_DeptID = &ra.deptid let $init_this = 'DPT' do init-fields do get-dept-stuff let $chart_nm = 'DEPTID' let $chart_vlu = &ra.deptid do get-chart-attrs let $tree_nm = 'UMS_DEPT_ORG' let $chart_vlu = &ra.deptid do get-chart-tree-attrs let $tree_nm = 'UMS_DEPT_F' let $chart_vlu = &ra.deptid do get-chart-tree-attrs end-if if rtrim(&ra.program_code,' ') <> '' if $Prev_Program_Code <> &ra.program_code let $Prev_Program_Code = &ra.program_code let $init_this = 'PRG' do init-fields do get-program-stuff let $chart_nm = 'PROGRAM_CODE' let $chart_vlu = &ra.program_code do get-chart-attrs let $tree_nm = 'UMS_PROGRAM' let $chart_vlu = &ra.program_code do get-chart-tree-attrs end-if else let $Prev_Program_Code = ' ' let $init_this = 'PRG' do init-fields end-if ! fund is required.... never blank if $Prev_Fund_Code <> &ra.fund_code let $Prev_Fund_Code = &ra.fund_code let $fnd_descr= ' ' let $fnd_descrs = ' ' let $fnd_st = ' ' do get-fund-stuff end-if if rtrim(&ra.operating_unit,' ') <> '' if $Prev_Operating_Unit <> &ra.operating_unit let $Prev_Operating_Unit = &ra.operating_unit let $opn_descr = ' ' let $opn_descrs = ' ' let $opn_st = ' ' do get-opun-stuff end-if else let $Prev_Operating_Unit = ' ' let $opn_descr = ' ' let $opn_descrs = ' ' let $opn_st = ' ' end-if ! account is required.... never blank if $Prev_Account <> &ra.account let $Prev_Account = &ra.account let $init_this = 'ACC' do init-fields do get-acct-stuff let $chart_nm = 'ACCOUNT' let $chart_vlu = &ra.account do get-chart-attrs end-if if rtrim(&ra.class_fld,' ') <> '' let $um_class_d = ' ' do get-class-stuff else let $um_class_d = ' ' end-if do update-glq-reprting add 1 to #c_tckr ! if #c_tckr = 5000 ! exit-select ! do Process-Commit ! let #c_tckr = 0 ! end-if ! do show-proc-end from sysadm.PS_UM_GLQ_RPT_TMP ra where ra.fiscal_year = #fy and ra.accounting_period = #ap [$for_update] order by ra.fiscal_year, ra.accounting_period, ! ??0407 ra.business_unit, ra.deptid, ra.project_id, ra.program_code, ra.fund_code, ra.operating_unit, ra.account, ra.class_fld !ra.business_unit, ra.operating_unit, ra.fund_code, ra.class_fld, ra.account, ra.deptid, ra.project_id, ra.program_code !ra.business_unit, ra.deptid, ra.project_id, ra.program_code, ra.account, ra.fund_code, ra.operating_unit, ra.class_fld end-select let $Calling_Procedure = 'process-glq-reprting' let $time_s = edit($hold_time,'SYYYYMMDDHH24MISSNNNNNN') do show-proc-end End-Procedure process-glq-reprting !*********************************************************************** ! insert-chartflds-temptable Procedure ! put tree chart fields in a temp table to reduced reduntant lookups !*********************************************************************** begin-procedure insert-chartflds-temptable let $calling_procedure = 'insert-chartflds-temptable' do show-proc-start begin-sql truncate table sysadm.ps_um_glq_cfld_tmp; commit; end-sql begin-sql insert into sysadm.ps_um_glq_cfld_tmp ( select rp.tree_name, rq.tree_node, rq.tree_level_num, rr.descr, rp.range_from, rp.range_to from pstreeleaf rp, pstreenode rq, ps_tree_node_tbl rr where rp.tree_name in ('UMS_DEPT_ORG','UMS_DEPT_F','UMS_PROJECT','UMS_PROGRAM') and (rq.tree_level_num >= 2 and rq.tree_level_num <= 9) and rp.setid = 'UMSYS' and rp.effdt = (select max(aef.effdt) from pstreeleaf aef where aef.setid = rp.setid and aef.setcntrlvalue = rp.setcntrlvalue and aef.tree_name = rp.tree_name and aef.effdt <= $per_end_dt) and rq.tree_name = rp.tree_name and rq.setid = rp.setid and rq.setcntrlvalue = rp.setcntrlvalue and rp.tree_node_num between rq.tree_node_num and rq.tree_node_num_end and rq.effdt = rp.effdt and rr.setid = rq.setid and rr.tree_node = rq.tree_node and rr.effdt = (select max(rref.effdt) from ps_tree_node_tbl rref where rref.setid = rr.setid and rref.tree_node = rr.tree_node and rref.effdt < $per_end_dt) and rr.eff_status = 'A' ); commit; end-sql do show-proc-end end-procedure !*********************************************************************** ! insert-chartattrs-temptable Procedure ! ! put tree chart fields in a temp table to reduced reduntant lookups !*********************************************************************** begin-procedure insert-chartattrs-temptable let $calling_procedure = 'insert-chartattrs-temptable' do show-proc-start begin-sql truncate table sysadm.ps_um_glq_cflt_tmp; commit; end-sql begin-sql insert into sysadm.ps_um_glq_cflt_tmp ( select DISTINCT /*+ rule */ rg.fieldname, rg.chartfield_value, rg.CF_ATTRIBUTE, rg.CF_ATTRIB_VALUE, rh.descr60 FROM PS_CF_ATTRIB_TBL rg, PS_CF_ATTRIB_VALUE rh WHERE rg.fieldname IN ('DEPTID','PROJECT_ID','PROGRAM_CODE','ACCOUNT') AND RG.CF_ATTRIBUTE IN ('YR_END_SCHEDULE','ASSET_NUMBER','BILLING_CODES','CFDA_NUMBER','FEDERAL_AGENCY', 'GRANT_NUMBER','GC_TYPE','BAL_SHEET_CLASS','CASH_FLOW_CLASS','FUND_BAL_CLASS', 'REVENUE_CAT','REVENUE_CODE','FACILITY_ID','DEPR_STATUS','FEDERAL_LOC_NO', 'IN_SERVICE','EXPENSE_CODE','PROG_YREND','PROG_EXPENSE' ! ,'ENDOW_DIST','TI_INC_DIST','BLD_LIFE','FIN_CONTACT','GC_REPORT','IMPVT_LIFE','LOC_EXPIRE' ! ??0507 ,'ENDOW_DIST','TI_INCOME_DIST','BLD_LIFE','FIN_CONTACT','GC_REPORT','IMPVT_LIFE','LOC_EXPIRE' ! ??0607 ,'CLOSINGCOMBO','HR_DEPT') AND rg.SETID = 'UMSYS' AND rg.EFFDT = (SELECT MAX(BEF.EFFDT) FROM PS_CF_ATTRIB_TBL BEF WHERE rg.SETID = BEF.SETID AND rg.CHARTFIELD_VALUE = BEF.CHARTFIELD_VALUE AND rg.FIELDNAME = BEF.FIELDNAME AND rg.CF_ATTRIBUTE = BEF.CF_ATTRIBUTE AND BEF.EFFDT <= $per_end_dt) AND rh.SETID = rg.SETID AND rh.FIELDNAME = rg.FIELDNAME AND rh.CF_ATTRIBUTE = rg.CF_ATTRIBUTE AND rh.CF_ATTRIB_VALUE = rg.CF_ATTRIB_VALUE ); commit; end-sql do show-proc-end end-procedure !*********************************************************************** ! get-ly-py-stuff Procedure ! grabs a couple fields from 1 year ago and two years ago. !*********************************************************************** Begin-Procedure get-ly-py-stuff let $Calling_Procedure = 'get-ly-py-stuff' #debugf let $show_addl = edit(#l_fy,'9999') || '/' || edit(#p_fy,'9999') || '/' || edit(#ap,'99') #debugf do show-proc-start begin-select on-error=sql-recover /*+ index(rb ps_um_glq_reprting) */ rb.fiscal_year, rb.accounting_period, rb.um_curr_budget, rb.um_actuals_ytd ! show 'got one: ' &rb.um_actuals_ytd '/' &rb.um_curr_budget if &rb.fiscal_year = #l_fy let #ly_actuals = &rb.um_actuals_ytd let #ly_basbud = &rb.um_curr_budget else let #py_actuals = &rb.um_actuals_ytd let #py_basbud = &rb.um_curr_budget end-if ! if #ly_actuals + #py_actuals + #ly_basbud + #py_basbud > 0 ! do update-ly-py-stuff ! end-if from sysadm.PS_UM_GLQ_REPRTING rb where (rb.fiscal_year = #l_fy or rb.fiscal_year = #p_fy) and rb.accounting_period = #ap and rb.business_unit = &ra.business_unit and rb.deptid = &ra.deptid and rb.project_id = &ra.project_id and rb.program_code = &ra.program_code and rb.fund_code = &ra.fund_code and rb.operating_unit = &ra.operating_unit and rb.account = &ra.account and rb.class_fld = &ra.class_fld and rb.statistics_code = &ra.statistics_code end-select #debugf do show-proc-end End-Procedure get-ly-py-stuff !*********************************************************************** ! get-futbud Procedure ! ! gets the total_posted_amt from three scenerios in ledger_budg !*********************************************************************** Begin-Procedure get-futbud let $Calling_Procedure = 'get-futbud' #debug do show-proc-start begin-select on-error=sql-recover rd.scenario, !rd.posted_total_amt ! ??0507 sum(rd.posted_total_amt) &rd.posted_total_amt ! ??0507 evaluate &rd.scenario when = '1' let #futbud_1 = &rd.posted_total_amt when = '2' let #futbud_2 = &rd.posted_total_amt when-other let #futbud = &rd.posted_total_amt end-evaluate ! do update-futbud from ps_ledger_budg rd where rd.fiscal_year = #fy ! and rd.accounting_period = #ap ! ??0507 and rd.accounting_period <= #ap ! ??0507 and rd.business_unit = &ra.business_unit and rd.deptid = &ra.deptid and rd.project_id = &ra.project_id and rd.program_code = &ra.program_code and rd.fund_code = &ra.fund_code and rd.operating_unit = &ra.operating_unit and rd.account = &ra.account and rd.class_fld = &ra.class_fld and rd.statistics_code = &ra.statistics_code and rd.ledger = 'FUTBUD' group by rd.scenario ! ??0507 end-select #debugf do show-proc-end End-Procedure get-futbud !*********************************************************************** ! get-chart-attrs Procedure !*********************************************************************** Begin-Procedure get-chart-attrs let $Calling_Procedure = 'get-chart-attrs' #debugf let $show_addl = $chart_nm || ' : ' || $chart_vlu #debugf do show-proc-start begin-select DISTINCT /*+ rule */ fieldname chartfield_value CF_ATTRIBUTE CF_ATTRIB_VALUE descr60 let $attr = rtrim(&CF_ATTRIBUTE,' ') let $attr_value = rtrim(&CF_ATTRIB_VALUE,' ') let $attr_desc = rtrim(&descr60,' ') evaluate $chart_nm when = 'DEPTID' evaluate $attr when = 'YR_END_SCHEDULE' let $um_dept_yrend = $attr_value let $um_dept_yrend_d = $attr_desc break When = 'HR_DEPT' let $um_dept_hr = $attr_value let $um_dept_hr_d = $attr_desc end-evaluate break when = 'PROJECT_ID' evaluate $attr when = 'FACILITY_ID' let $um_proj_facil_id = $attr_value let $um_proj_facil_id_d = $attr_desc break when = 'BILLING_CODES' let $um_proj_bill_code = $attr_value let $um_proj_bill_code_d = $attr_desc break when = 'CFDA_NUMBER' let $um_proj_cfda_n = $attr_value let $um_proj_cfda_n_d = $attr_desc break when = 'FEDERAL_AGENCY' let $um_proj_fed_agcy = $attr_value let $um_proj_fed_agcy_d = $attr_desc break when = 'GRANT_NUMBER' let $um_proj_grant_n = $attr_value let $um_proj_grant_n_d = $attr_desc break when = 'GC_TYPE' let $um_proj_gc_type = $attr_value let $um_proj_gc_type_d = $attr_desc break when = 'GC_TYPE' let $um_proj_gc_type = $attr_value let $um_proj_gc_type_d = $attr_desc break when = 'DEPR_STATUS' let $um_proj_depr_st = $attr_value let $um_proj_depr_st_d = $attr_desc break when = 'FEDERAL_LOC_NO' let $um_proj_loc_num = $attr_value let $um_proj_loc_num_d = $attr_desc break when = 'IN_SERVICE' let $ValidFlag = 'Y' if length($attr_value) = 8 let #i = 1 while #i < 9 let #ascii_val = ascii(substr($attr_value, #i, 1)) if #ascii_val < 48 or #ascii_val > 57 let $ValidFlag = 'N' break end-if let #i = #i + 1 end-while if $ValidFlag = 'Y' let $attr_value_validate = substr($attr_value, 1, 4) || '-' || substr($attr_value, 5,2) || '-' || substr($attr_value,7,2) do Validate-Native-Date($attr_value_validate, $ValidFlag) if $ValidFlag = 'Y' let $um_proj_in_srvc = strtodate($attr_value,'YYYYMMDD') else show 'Invalid date value (' $attr_value ') found for IN_SERVICE' show 'Unknown reason' end-if else show 'Invalid date value (' $attr_value ') found for IN_SERVICE' show 'A non-numeric character was found, how can a date have alphabets' end-if else show 'Invalid date value (' $attr_value ') found for IN_SERVICE' show 'Must be exactly 8 characters long in YYYYMMDD format' end-if break when = 'EXPENSE_CODE' let $um_proj_expense = $attr_value let $um_proj_expense_d = $attr_desc break when = 'BLD_LIFE' ! ??0507 let $um_proj_bld_life = $attr_value ! ??0507 break ! ??0507 when = 'FIN_CONTACT' ! ??0507 let $um_proj_fin_cont = $attr_value ! ??0507 let $um_proj_fin_cont_d = $attr_desc ! ??0507 break ! ??0507 when = 'GC_REPORT' ! ??0507 let $um_proj_gc_rpt = $attr_value ! ??0507 let $um_proj_gc_rpt_d = $attr_desc ! ??0507 break ! ??0507 when = 'IMPVT_LIFE' ! ??0507 let $um_proj_impvt_life = $attr_value ! ??0507 break ! ??0507 when = 'LOC_EXPIRE' ! ??0507 let $um_proj_loc_expire = $attr_value ! ??0507 break ! ??0507 when = 'CLOSINGCOMBO' let $um_proj_closncombo = $attr_value ! 06/19/2009 let $um_proj_clscombo_d = $attr_desc ! 06/19/2009 end-evaluate break when = 'PROGRAM_CODE' evaluate $attr when = 'YR_END_SCHEDULE' let $um_prog_yrend = $attr_value let $um_prog_yrend_d = $attr_desc break when = 'EXPENSE_CODE' let $um_prog_expense = $attr_value let $um_prog_expense_d = $attr_desc break when = 'ENDOW_DIST' ! ??0508 let $um_prog_endow_dist = $attr_value ! ??0508 break ! when = 'TI_INC_DIST' ! ??0508 when = 'TI_INCOME_DIST' ! ??0607 let $um_prog_ti_inc_dst = $attr_value ! ??0508 end-evaluate break when = 'ACCOUNT' evaluate $attr when = 'BAL_SHEET_CLASS' let $um_acct_bal_cls = $attr_value let $um_acct_bal_cls_d = $attr_desc break when = 'CASH_FLOW_CLASS' let $um_acct_cash_cls = $attr_value let $um_acct_cash_cls_d = $attr_desc break when = 'FUND_BAL_CLASS' let $um_acct_fbal_cls = $attr_value let $um_acct_fbal_cls_d = $attr_desc break when = 'REVENUE_CAT' let $um_acct_rev_cat = $attr_value let $um_acct_rev_cat_d = $attr_desc break when = 'REVENUE_CODE' let $um_acct_rev_cd = $attr_value let $um_acct_rev_cd_d = $attr_desc break when = 'YR_END_SCHEDULE' let $um_acct_yrend = $attr_value let $um_acct_yrend_d = $attr_desc end-evaluate break end-evaluate FROM sysadm.ps_um_glq_cflt_tmp WHERE fieldname = $chart_nm and chartfield_value = $chart_vlu end-select #debugf do show-proc-end End-Procedure get-chart-attrs !*********************************************************************** ! get-chart-tree-attrs Procedure ! ! this is a generic tree lookup routine. You feed it the tree_name ! in the $tree_nm field and the value of the chart field (such as deptid ! for the UMSDEPT_ORG tree, in the $chart_vlu routine. ! It sorts out what to do with the returned values within the ! evaluates. !*********************************************************************** Begin-Procedure get-chart-tree-attrs let $Calling_Procedure = 'get-chart-tree-attrs' #debugf let $show_addl = $tre_nm #debugf do show-proc-start begin-select tree_name tree_node tree_level_num descr range_from range_to let #level = &tree_level_num let $node = &tree_node let $node_d = &descr evaluate $tree_nm when = 'UMS_DEPT_ORG' evaluate #level when = 3 let $cr1 = $node let $cr1_d = $node_d break when = 4 let $cr2 = $node let $cr2_d = $node_d break when = 5 let $cr3 = $node let $cr3_d = $node_d break when = 6 let $cr4 = $node let $cr4_d = $node_d when-other break end-evaluate when = 'UMS_DEPT_F' evaluate #level when = 2 let $dept_exp2 = $node let $dept_exp2_d = $node_d break when = 3 let $dept_exp4 = $node let $dept_exp4_d = $node_d break end-evaluate when = 'UMS_PROJECT' evaluate #level when = 3 let $proj_category = $node let $proj_category_d = $node_d break when = 4 let $proj_majsrc = $node let $proj_majsrc_d = $node_d break when = 5 let $proj_minsrc = $node let $proj_minsrc_d = $node_d break when = 6 let $proj_loc_srce = $node let $proj_loc_srce_d = $node_d break when = 7 let $proj_group = $node let $proj_group_d = $node_d break when = 8 let $proj_sub_group = $node let $proj_sub_group_d = $node_d break when = 9 let $proj_cap_type = $node let $proj_cap_type_d = $node_d break end-evaluate break when = 'UMS_PROGRAM' evaluate #level when = 3 let $um_prog_cat1 = $node let $um_prog_cat1_d = $node_d break when = 4 let $um_prog_cat2 = $node let $um_prog_cat2_d = $node_d break when = 5 let $um_prog_cat3 = $node let $um_prog_cat3_d = $node_d break when = 6 let $um_prog_cat4 = $node let $um_prog_cat4_d = $node_d break when = 7 let $um_prog_restr_1 = $node let $um_prog_restr_1_d = $node_d break when = 8 let $um_prog_restr_2 = $node let $um_prog_restr_2_d = $node_d break when = 9 let $um_prog_restr_3 = $node let $um_prog_restr_3_d = $node_d break end-evaluate end-evaluate from sysadm.ps_um_glq_cfld_tmp where tree_name = $tree_nm and $chart_vlu between range_from and range_to end-select #debugf do show-proc-end End-Procedure get-chart-tree-attrs !*********************************************************************** ! get-per-end-dt Procedure !*********************************************************************** Begin-Procedure get-per-end-dt let $Calling_Procedure = 'get-per-end-dt' #debugf do show-proc-start begin-select on-error=sql-recover rj.END_DT let $per_end_dt = &rj.end_dt from PS_CAL_DETP_TBL rj where rj.FISCAL_YEAR = #fy AND rj.accounting_period = #ap and setid = 'UMSYS' end-select #debugf do show-proc-end End-Procedure get-per-end-dt !*********************************************************************** ! update-futbud Procedure !*********************************************************************** Begin-Procedure update-futbud let $Calling_Procedure = 'update-futbud' #debugf do show-proc-start begin-sql on-error=sql-recover update sysadm.PS_UM_GLQ_RPT_TMP re set re.um_future_budget = #futbud, re.um_future_budget_1 = #futbud_1, re.um_future_budget_2 = #futbud_2 where re.fiscal_year = #fy and re.accounting_period = #ap and re.business_unit = ra.business_unit ! and re_deptid = ra.deptid !FN92UPG: Modification begin and re.deptid = ra.deptid !FN92UPG: Modification end and re.project_id = ra.project_id and re.program_code = ra.program_code and re.fund_code = ra.fund_code and re.operating_unit = ra.operating_unit and re.account = ra.account and re.class_fld = ra.class_fld and re.statistics_code = ra.statistics_code end-sql #debugf do show-proc-end End-Procedure update-futbud !*********************************************************************** ! update-glq-reprting Procedure !*********************************************************************** Begin-Procedure update-glq-reprting let $Calling_Procedure = 'update-glq-reprting' #debugf do show-proc-start do cleanse-data begin-sql on-error=sql-recover update sysadm.PS_UM_GLQ_RPT_TMP rs set rs.UM_FUTURE_BUDGET = #futbud, rs.UM_FUTURE_BUDGET_1 = #futbud_1, rs.UM_FUTURE_BUDGET_2 = #futbud_2, rs.UM_CURR_BUDGET_LY = #ly_basbud, rs.UM_ACTUALS_YTD_LY = #ly_actuals, rs.UM_CURR_BUDGET_PY = #py_basbud, rs.UM_ACTUALS_YTD_PY = #py_actuals, rs.UM_BU_DESCR = nvl($bu_descr,' '), rs.UM_BU_DESCRSHORT = nvl($bu_descrs,' '), rs.UM_DEPT_STATUS = nvl($dept_st,' '), rs.UM_DEPT_DESCR = nvl($dept_descr,' '), rs.UM_DEPT_DESCR_SHRT = nvl($dept_descrs,' '), rs.UM_DEPT_MGR = nvl($dept_mgr,' '), rs.UM_DEPT_YREND = nvl($um_dept_yrend,' '), rs.UM_DEPT_YREND_D = nvl($um_dept_yrend_d,' '), rs.UM_DEPT_CR_1 = nvl($cr1,' '), rs.UM_DEPT_CR_1_D = nvl($cr1_d,' '), rs.UM_DEPT_CR_2 = nvl($cr2,' '), rs.UM_DEPT_CR_2_D = nvl($cr2_d,' '), rs.UM_DEPT_CR_3 = nvl($cr3,' '), rs.UM_DEPT_CR_3_D = nvl($cr3_d,' '), rs.UM_DEPT_CR_4 = nvl($cr4,' '), rs.UM_DEPT_CR_4_D = nvl($cr4_d,' '), rs.UM_DEPT_EXP2 = nvl($dept_exp2,' '), rs.UM_DEPT_EXP2_D = nvl($dept_exp2_d,' '), rs.UM_DEPT_EXP4 = nvl($dept_exp4,' '), rs.UM_DEPT_EXP4_D = nvl($dept_exp4_d,' '), rs.UM_PROJ_EFF_STATUS = nvl($prj_st,' '), rs.UM_PROJ_DESCR = nvl($prj_descr,' '), rs.UM_PROJ_MGR_ID = nvl($prj_mgr_id,' '), rs.UM_PROJ_MGR_NAME = nvl($prj_mgr,' '), rs.UM_PROJ_START_DT = $prj_start_dt, rs.UM_PROJ_END_DT = $prj_end_dt, rs.PROJECT_STATUS = nvl($prj_status,' '), rs.UM_PROJ_FACIL_ID = nvl($um_proj_facil_id,' '), rs.UM_PROJ_FACIL_ID_D = nvl($um_proj_facil_id_d,' '), rs.UM_PROJ_BILL_CD = nvl($um_proj_bill_code,' '), rs.UM_PROJ_BILL_CD_D = nvl($um_proj_bill_code_d,' '), rs.UM_PROJ_CFDA_N = nvl($um_proj_cfda_n,' '), rs.UM_PROJ_CFDA_N_D = nvl($um_proj_cfda_n_d,' '), rs.UM_PROJ_FED_AGCY = nvl($um_proj_fed_agcy,' '), rs.UM_PROJ_FED_AGCY_D = nvl($um_proj_fed_agcy_d,' '), rs.UM_PROJ_GRANT_N = nvl($um_proj_grant_n,' '), rs.UM_PROJ_GRANT_N_D = nvl($um_proj_grant_n_d,' '), rs.UM_PROJ_GC_TYPE = nvl($um_proj_gc_type,' '), rs.UM_PROJ_GC_TYPE_D = nvl($um_proj_gc_type_d,' '), rs.UM_PROJ_DEPR_ST = nvl($um_proj_depr_st,' '), rs.UM_PROJ_DEPR_ST_D = nvl($um_proj_depr_st_d,' '), rs.UM_PROJ_LOC_NUM = nvl($um_proj_loc_num,' '), rs.UM_PROJ_LOC_NUM_D = nvl($um_proj_loc_num_d,' '), ! rs.UM_PROJ_IN_SRVC = $um_proj_in_srcv, ! ??0507 rs.UM_PROJ_IN_SRVC = $um_proj_in_srvc, ! ??0507 ! ??0607 rs.UM_PROJ_EXPENSE = nvl($um_proj_expense,' '), rs.UM_PROJ_EXPENSE_D = nvl($um_proj_expense_d,' '), rs.UM_PROJ_CATEGORY = nvl($proj_category,' '), rs.UM_PROJ_CATEGORY_D = nvl($proj_category_d,' '), rs.UM_PROJ_MAJSRC = nvl($proj_majsrc,' '), rs.UM_PROJ_MAJSRC_D = nvl($proj_majsrc_d,' '), rs.UM_PROJ_MINSRC = nvl($proj_minsrc,' '), rs.UM_PROJ_MINSRC_D = nvl($proj_minsrc_d,' '), rs.UM_PROJ_LOC_SRCE = nvl($proj_loc_srce,' '), rs.UM_PROJ_LOC_SRCE_D = nvl($proj_loc_srce_d,' '), rs.UM_PROJ_GROUP = nvl($proj_group,' '), rs.UM_PROJ_GROUP_D = nvl($proj_group_d,' '), rs.UM_PROJ_SUBGROUP = nvl($proj_sub_group,' '), rs.UM_PROJ_SUBGROUP_D = nvl($proj_sub_group_d,' '), rs.UM_PROJ_CAP_TYPE = nvl($proj_cap_type,' '), rs.UM_PROJ_CAP_TYPE_D = nvl($proj_cap_type_d,' '), rs.UM_PROG_STATUS = nvl($pgm_st,' '), rs.UM_PROG_DESCR = nvl($pgm_descr,' '), rs.UM_PROG_DESCR_SHRT = nvl($pgm_descrs,' '), rs.UM_PROG_MGR_NAME = nvl($pgm_mgr,' '), rs.UM_PROG_YREND = nvl($um_prog_yrend,' '), rs.UM_PROG_YREND_D = nvl($um_prog_yrend_d,' '), rs.UM_PROG_EXPENSE = nvl($um_prog_expense,' '), rs.UM_PROG_EXPENSE_D = nvl($um_prog_expense_d,' '), rs.UM_PROG_CAT1 = nvl($um_prog_cat1,' '), rs.UM_PROG_CAT1_D = nvl($um_prog_cat1_d,' '), rs.UM_PROG_CAT2 = nvl($um_prog_cat2,' '), rs.UM_PROG_CAT2_D = nvl($um_prog_cat2_d,' '), rs.UM_PROG_CAT3 = nvl($um_prog_cat3,' '), rs.UM_PROG_CAT3_D = nvl($um_prog_cat3_d,' '), rs.UM_PROG_CAT4 = nvl($um_prog_cat4,' '), rs.UM_PROG_CAT4_D = nvl($um_prog_cat4_d,' '), rs.UM_PROG_RESTR_1 = nvl($um_prog_restr_1,' '), rs.UM_PROG_RESTR_1_D = nvl($um_prog_restr_1_d,' '), rs.UM_PROG_RESTR_2 = nvl($um_prog_restr_2,' '), rs.UM_PROG_RESTR_2_D = nvl($um_prog_restr_2_d,' '), rs.UM_PROG_RESTR_3 = nvl($um_prog_restr_3,' '), rs.UM_PROG_RESTR_3_D = nvl($um_prog_restr_3_d,' '), rs.UM_FUND_STATUS = nvl($fnd_st,' '), rs.UM_FUND_DESCR = nvl($fnd_descr,' '), rs.UM_FUND_DESCRSHRT = nvl($fnd_descrs,' '), rs.UM_OPUN_STATUS = nvl($opn_st,' '), rs.UM_OPUN_DESCR = nvl($opn_descr,' '), rs.UM_OPUN_DESCRSHRT = nvl($opn_descrs,' '), rs.UM_ACCT_STATUS = nvl($acct_st,' '), rs.UM_ACCT_DESCR = nvl($acct_descr,' '), rs.UM_ACCT_DESCRSHRT = nvl($acct_descrs,' '), rs.UM_ACCT_BAL_CLS = nvl($um_acct_bal_cls,' '), rs.UM_ACCT_BAL_CLS_D = nvl($um_acct_bal_cls_d,' '), rs.UM_ACCT_CASH_FLW = nvl($um_acct_cash_cls,' '), rs.UM_ACCT_CASH_FLW_D = nvl($um_acct_cash_cls_d,' '), rs.UM_ACCT_FBAL_CLS = nvl($um_acct_fbal_cls,' '), rs.UM_ACCT_FBAL_CLS_D = nvl($um_acct_fbal_cls_d,' '), rs.UM_ACCT_REV2 = nvl($um_acct_rev_cat,' '), rs.UM_ACCT_REV2_D = nvl($um_acct_rev_cat_d,' '), rs.UM_ACCT_REV4 = nvl($um_acct_rev_cd,' '), rs.UM_ACCT_REV4_D = nvl($um_acct_rev_cd_d,' '), rs.UM_ACCT_YREND = nvl($um_acct_yrend,' '), rs.UM_ACCT_YREND_D = nvl($um_acct_yrend_d,' '), rs.UM_CLASS_D = nvl($um_class_d,' '), rs.UM_PROG_ENDOW_DIST = nvl($um_prog_endow_dist,' '), ! ??0507 rs.UM_PROG_TI_INC_DST = nvl($um_prog_ti_inc_dst,' '), ! ??0507 rs.UM_PROJ_BLD_LIFE = nvl($um_proj_bld_life,' '), ! ??0507 rs.UM_PROJ_FIN_CONT = nvl($um_proj_fin_cont,' '), ! ??0507 rs.UM_PROJ_FIN_CONT_D = nvl($um_proj_fin_cont_d,' '), ! ??0507 rs.UM_PROJ_GC_RPT = nvl($um_proj_GC_RPT,' '), ! ??0507 rs.UM_PROJ_GC_RPT_D = nvl($um_proj_gc_rpt_d,' '), ! ??0507 rs.UM_PROJ_IMPVT_LIFE = nvl($um_proj_impvt_life,' '), ! ??0507 rs.UM_PROJ_LOC_EXPIRE = nvl($um_proj_loc_expire,' '), ! ??0507 rs.UM_PROJ_CLOSNCOMBO = nvl($um_proj_closncombo,' '), ! 06/19/2009 rs.UM_PROJ_CLSCOMBO_D = nvl($um_proj_clscombo_d, ' '), ! 06/19/2009 rs.UM_PROJ_MGR_EFFDT = $prj_mgr_effdt, ! ??0507 rs.UM_PROJ_STAT_EFFDT = $prj_stat_effdt, ! ??0507 rs.UM_DEPT_HR = nvl($um_dept_hr, ' '), ! 04/22/2010 rs.UM_DEPT_HR_D = nvl($um_dept_hr_d, ' ') ! 04/22/2010 where rs.fiscal_year = #fy and rs.accounting_period = #ap and rs.business_unit = &ra.business_unit and rs.deptid = &ra.deptid and rs.project_id = &ra.project_id and rs.program_code = &ra.program_code and rs.fund_code = &ra.fund_code and rs.operating_unit = &ra.operating_unit and rs.account = &ra.account and rs.class_fld = &ra.class_fld and rs.statistics_code = &ra.statistics_code end-sql #debugf do show-proc-end End-Procedure update-glq-reprting !*********************************************************************** ! cleanse-data Procedure !*********************************************************************** Begin-Procedure cleanse-data !let $Calling_Procedure = 'cleanse-data' let $bu_descr = substr(rtrim($bu_descr,' '),1,30) let $bu_descrs = substr(rtrim($bu_descrs,' '),1,10) let $dept_st = substr(rtrim($dept_st,' '),1,1) let $dept_descr = substr(rtrim($dept_descr,' '),1,30) let $dept_descrs = substr(rtrim($dept_descrs,' '),1,10) let $dept_mgr = substr(rtrim($dept_mgr,' '),1,30) let $um_dept_yrend = substr(rtrim($um_dept_yrend,' '),1,20) let $um_dept_yrend_d = substr(rtrim($um_dept_yrend_d,' '),1,60) let $cr1 = substr(rtrim($cr1,' '),1,20) let $cr1_d = substr(rtrim($cr1_d,' '),1,30) let $cr2 = substr(rtrim($cr2,' '),1,20) let $cr2_d = substr(rtrim($cr2_d,' '),1,30) let $cr3 = substr(rtrim($cr3,' '),1,20) let $cr3_d = substr(rtrim($cr3_d,' '),1,30) let $cr4 = substr(rtrim($cr4,' '),1,20) let $cr4_d = substr(rtrim($cr4_d,' '),1,30) let $dept_exp2 = substr(rtrim($dept_exp2,' '),1,20) let $dept_exp2_d = substr(rtrim($dept_exp2_d,' '),1,30) let $dept_exp4 = substr(rtrim($dept_exp4,' '),1,20) let $dept_exp4_d = substr(rtrim($dept_exp4_d,' '),1,30) let $prj_st = substr(rtrim($prj_st,' '),1,1) let $prj_descr = substr(rtrim($prj_descr,' '),1,30) let $prj_mgr_id = substr(rtrim($prj_mgr_id,' '),1,11) let $prj_mgr = substr(rtrim($prj_mgr,' '),1,30) let $prj_status = substr(rtrim($prj_status,' '),1,1) let $um_proj_facil_id = substr(rtrim($um_proj_facil_id,' '),1,20) let $um_proj_facil_id_d = substr(rtrim($um_proj_facil_id_d,' '),1,60) let $um_proj_bill_code = substr(rtrim($um_proj_bill_code,' '),1,20) let $um_proj_bill_code_d = substr(rtrim($um_proj_bill_code_d,' '),1,60) let $um_proj_cfda_n = substr(rtrim($um_proj_cfda_n,' '),1,20) let $um_proj_cfda_n_d = substr(rtrim($um_proj_cfda_n_d,' '),1,60) let $um_proj_fed_agcy = substr(rtrim($um_proj_fed_agcy,' '),1,20) let $um_proj_fed_agcy_d = substr(rtrim($um_proj_fed_agcy_d,' '),1,60) let $um_proj_grant_n = substr(rtrim($um_proj_grant_n,' '),1,20) let $um_proj_grant_n_d = substr(rtrim($um_proj_grant_n_d,' '),1,60) let $um_proj_gc_type = substr(rtrim($um_proj_gc_type,' '),1,20) let $um_proj_gc_type_d = substr(rtrim($um_proj_gc_type_d,' '),1,60) let $um_proj_depr_st = substr(rtrim($um_proj_depr_st,' '),1,20) let $um_proj_depr_st_d = substr(rtrim($um_proj_depr_st_d,' '),1,60) let $um_proj_loc_num = substr(rtrim($um_proj_loc_num,' '),1,20) let $um_proj_loc_num_d = substr(rtrim($um_proj_loc_num_d,' '),1,60) let $um_proj_expense = substr(rtrim($um_proj_expense,' '),1,20) let $um_proj_expense_d = substr(rtrim($um_proj_expense_d,' '),1,60) let $proj_category = substr(rtrim($proj_category,' '),1,20) let $proj_category_d = substr(rtrim($proj_category_d,' '),1,30) let $proj_majsrc = substr(rtrim($proj_majsrc,' '),1,20) let $proj_majsrc_d = substr(rtrim($proj_majsrc_d,' '),1,30) let $proj_minsrc = substr(rtrim($proj_minsrc,' '),1,20) let $proj_minsrc_d = substr(rtrim($proj_minsrc_d,' '),1,30) let $proj_loc_srce = substr(rtrim($proj_loc_srce,' '),1,20) let $proj_loc_srce_d = substr(rtrim($proj_loc_srce_d,' '),1,30) let $proj_group = substr(rtrim($proj_group,' '),1,20) let $proj_group_d = substr(rtrim($proj_group_d,' '),1,30) let $proj_sub_group = substr(rtrim($proj_sub_group,' '),1,20) let $proj_sub_group_d = substr(rtrim($proj_sub_group_d,' '),1,30) let $proj_cap_type = substr(rtrim($proj_cap_type,' '),1,20) let $proj_cap_type_d = substr(rtrim($proj_cap_type_d,' '),1,30) let $pgm_st = substr(rtrim($pgm_st,' '),1,1) let $pgm_descr = substr(rtrim($pgm_descr,' '),1,30) let $pgm_descrs = substr(rtrim($pgm_descrs,' '),1,10) let $pgm_mgr = substr(rtrim($pgm_mgr,' '),1,30) let $um_prog_yrend = substr(rtrim($um_prog_yrend,' '),1,20) let $um_prog_yrend_d = substr(rtrim($um_prog_yrend_d,' '),1,30) let $um_prog_expense = substr(rtrim($um_prog_expense,' '),1,20) let $um_prog_expense_d = substr(rtrim($um_prog_expense_d,' '),1,60) let $um_prog_cat1 = substr(rtrim($um_prog_cat1,' '),1,20) let $um_prog_cat1_d = substr(rtrim($um_prog_cat1_d,' '),1,30) let $um_prog_cat2 = substr(rtrim($um_prog_cat2,' '),1,20) let $um_prog_cat2_d = substr(rtrim($um_prog_cat2_d,' '),1,30) let $um_prog_cat3 = substr(rtrim($um_prog_cat3,' '),1,20) let $um_prog_cat3_d = substr(rtrim($um_prog_cat3_d,' '),1,30) let $um_prog_cat4 = substr(rtrim($um_prog_cat4,' '),1,20) let $um_prog_cat4_d = substr(rtrim($um_prog_cat4_d,' '),1,30) let $um_prog_restr_1 = substr(rtrim($um_prog_restr_1,' '),1,20) let $um_prog_restr_1_d = substr(rtrim($um_prog_restr_1_d,' '),1,30) let $um_prog_restr_2 = substr(rtrim($um_prog_restr_2,' '),1,20) let $um_prog_restr_2_d = substr(rtrim($um_prog_restr_2_d,' '),1,30) let $um_prog_restr_3 = substr(rtrim($um_prog_restr_3,' '),1,20) let $um_prog_restr_3_d = substr(rtrim($um_prog_restr_3_d,' '),1,30) let $fnd_st = substr(rtrim($fnd_st,' '),1,1) let $fnd_descr = substr(rtrim($fnd_descr,' '),1,30) let $fnd_descrs = substr(rtrim($fnd_descrs,' '),1,10) let $opn_st = substr(rtrim($opn_st,' '),1,1) let $opn_descr = substr(rtrim($opn_descr,' '),1,30) let $opn_descrs = substr(rtrim($opn_descrs,' '),1,10) let $acct_st = substr(rtrim($acct_st,' '),1,1) let $acct_descr = substr(rtrim($acct_descr,' '),1,30) let $acct_descrs = substr(rtrim($acct_descrs,' '),1,10) let $um_acct_bal_cls = substr(rtrim($um_acct_bal_cls,' '),1,20) let $um_acct_bal_cls_d = substr(rtrim($um_acct_bal_cls_d,' '),1,60) let $um_acct_cash_cls = substr(rtrim($um_acct_cash_cls,' '),1,20) let $um_acct_cash_cls_d = substr(rtrim($um_acct_cash_cls_d,' '),1,60) let $um_acct_fbal_cls = substr(rtrim($um_acct_fbal_cls,' '),1,20) let $um_acct_fbal_cls_d = substr(rtrim($um_acct_fbal_cls_d,' '),1,60) let $um_acct_rev_cat = substr(rtrim($um_acct_rev_cat,' '),1,20) let $um_acct_rev_cat_d = substr(rtrim($um_acct_rev_cat_d,' '),1,60) let $um_acct_rev_cd = substr(rtrim($um_acct_rev_cd,' '),1,20) let $um_acct_rev_cd_d = substr(rtrim($um_acct_rev_cd_d,' '),1,60) let $um_acct_yrend = substr(rtrim($um_acct_yrend,' '),1,20) let $um_acct_yrend_d = substr(rtrim($um_acct_yrend_d,' '),1,60) let $um_class_d = substr(rtrim($um_class_d,' '),1,30) ! ??0507 increase from 20 let $um_prog_endow_dist = substr(rtrim($um_prog_endow_dist,' '),1,20) ! ??0507 let $um_prog_ti_inc_dst = substr(rtrim($um_prog_ti_inc_dst,' '),1,20) ! ??0507 let $um_proj_bld_life = substr(rtrim($um_proj_bld_life,' '),1,20) ! ??0507 let $um_proj_fin_cont = substr(rtrim($um_proj_fin_cont,' '),1,20) ! ??0507 let $um_proj_fin_cont_d = substr(rtrim($um_proj_fin_cont_d,' '),1,60) ! ??0507 let $um_proj_gc_rpt = substr(rtrim($um_proj_gc_rpt,' '),1,20) ! ??0507 let $um_proj_gc_rpt_d = substr(rtrim($um_proj_gc_rpt_d,' '),1,60) ! ??0507 let $um_proj_impvt_life = substr(rtrim($um_proj_impvt_life,' '),1,20) ! ??0507 let $um_proj_loc_expire = substr(rtrim($um_proj_loc_expire,' '),1,20) ! ??0507 let $um_proj_closncombo = substr(rtrim($um_proj_closncombo,' '),1,20) !06/19/2009 let $um_proj_clscombo_d = substr(rtrim($um_proj_clscombo_d,' '),1,30) !06/19/2009 let $um_dept_hr = substr(rtrim($um_dept_hr,' '),1,20) !04/22/2010 let $um_dept_hr_d = substr(rtrim($um_dept_hr_d,' '),1,60) !04/22/2010 End-Procedure cleanse-data !*********************************************************************** ! get-bu-stuff Procedure !*********************************************************************** Begin-Procedure get-bu-stuff let $Calling_Procedure = 'get-bu-stuff' #debugf do show-proc-start let $lu_return = '' let $bux = &ra.business_unit lookup bu_lu $bux $lu_return do PARSE-Func-Put($lu_return, ',', '', #O_cols) do PARSE-Func-Get(0, $bu_descr) do PARSE-Func-Get(1, $bu_descrs) #debugf do show-proc-end End-Procedure get-bu-stuff !*********************************************************************** ! get-dept-stuff Procedure !*********************************************************************** Begin-Procedure get-dept-stuff let $Calling_Procedure = 'get-dept-stuff' #debugf do show-proc-start let $lu_return = '' let $dpt_id = &ra.deptid lookup dpt_lu $dpt_id $lu_return do PARSE-Func-Put($lu_return, '{', '', #O_cols) do PARSE-Func-Get(0, $dept_st) do PARSE-Func-Get(1, $dept_descr) do PARSE-Func-Get(2, $dept_descrs) do PARSE-Func-Get(3, $dept_mgr) #debugf do show-proc-end End-Procedure get-dept-stuff !*********************************************************************** ! get-program-stuff Procedure !*********************************************************************** Begin-Procedure get-program-stuff let $Calling_Procedure = 'get-program-stuff' #debugf do show-proc-start let $lu_return = '' let $pgm_id = &ra.program_code lookup pgm_lu $pgm_id $lu_return do PARSE-Func-Put($lu_return, '{', '', #O_cols) do PARSE-Func-Get(0, $pgm_st) do PARSE-Func-Get(1, $pgm_descr) do PARSE-Func-Get(2, $pgm_descrs) do PARSE-Func-Get(3, $pgm_mgr) #debugf do show-proc-end End-Procedure get-program-stuff !*********************************************************************** ! get-fund-stuff Procedure !*********************************************************************** Begin-Procedure get-fund-stuff let $Calling_Procedure = 'get-fund-stuff' #debugf do show-proc-start let $lu_return = '' let $fnd_id = &ra.fund_code lookup fnd_lu $fnd_id $lu_return do PARSE-Func-Put($lu_return, '{', '', #O_cols) do PARSE-Func-Get(0, $fnd_st) do PARSE-Func-Get(1, $fnd_descr) do PARSE-Func-Get(2, $fnd_descrs) #debugf do show-proc-end End-Procedure get-fund-stuff !*********************************************************************** ! get-opun-stuff Procedure !*********************************************************************** Begin-Procedure get-opun-stuff let $Calling_Procedure = 'get-opun-stuff' #debugf do show-proc-start let $ou_return = '' let $oux = &ra.operating_unit lookup ou_lu $oux $ou_return do PARSE-Func-Put($ou_return, '{', '', #O_cols) do PARSE-Func-Get(0, $opn_st) do PARSE-Func-Get(1, $opn_descr) do PARSE-Func-Get(2, $opn_descrs) #debugf do show-proc-end End-Procedure get-opun-stuff !*********************************************************************** ! get-acct-stuff Procedure !*********************************************************************** Begin-Procedure get-acct-stuff let $Calling_Procedure = 'get-acct-stuff' #debugf do show-proc-start let $lu_return = '' let $act_id = &ra.account lookup act_lu $act_id $lu_return do PARSE-Func-Put($lu_return, '{', '', #O_cols) do PARSE-Func-Get(0, $acct_st) do PARSE-Func-Get(1, $acct_descr) do PARSE-Func-Get(2, $acct_descrs) #debugf do show-proc-end End-Procedure get-acct-stuff !*********************************************************************** ! get-project-stuff Procedure !*********************************************************************** Begin-Procedure get-project-stuff let $Calling_Procedure = 'get-project-stuff' #debugf do show-proc-start let $lu_return = '' let $prj_id = &ra.project_id lookup prj_lu $prj_id $lu_return do PARSE-Func-Put($lu_return, '{', '', #O_cols) do PARSE-Func-Get(0, $prj_st) do PARSE-Func-Get(1, $prj_descr) do PARSE-Func-Get(2, $prj_status) do PARSE-Func-Get(3, $prj_start_dt) do PARSE-Func-Get(4, $prj_end_dt) do PARSE-Func-Get(5, $prj_mgr_id) do PARSE-Func-Get(6, $prj_mgr) do PARSE-Func-Get(7, $prj_stat_effdt) ! ??0507 do PARSE-Func-Get(8, $prj_mgr_effdt) ! ??0507 ! UM_MOD Start CEC 03/2009 Projects were not showing correct end dates. ! Some where showing 1999 when they were really 2099. Below is ! no longer needed. show $prj_id ':' $prj_start_dt ':' $prj_end_dt if rtrim($prj_start_dt,' ') <> '' let $edit_date = strtodate($prj_start_dt,'MM/DD/YYYY') let $prj_start_dt = datetostr($edit_date,'DD-MON-YYYY') else let $prj_start_dt = '' end-if if rtrim($prj_end_dt,' ') <> '' let $edit_date = strtodate($prj_end_dt,'MM/DD/YYYY') let $prj_end_dt = datetostr($edit_date,'DD-MON-YYYY') else let $prj_end_dt = '' end-if ! ??0508 added below if rtrim($prj_stat_effdt,' ') <> '' let $edit_date = strtodate($prj_stat_effdt,'MM/DD/YYYY') let $prj_stat_effdt = datetostr($edit_date,'DD-MON-YYYY') else let $prj_stat_effdt = '' end-if if rtrim($prj_mgr_effdt,' ') <> '' let $edit_date = strtodate($prj_mgr_effdt,'MM/DD/YYYY') let $prj_mgr_effdt = datetostr($edit_date,'DD-MON-YYYY') else let $prj_mgr_effdt = '' end-if !UM_MOD End CEC #debugf do show-proc-end End-Procedure get-project-stuff !*********************************************************************** ! get-class-stuff Procedure !*********************************************************************** Begin-Procedure get-class-stuff let $Calling_Procedure = 'get-class-stuff' #debugf do show-proc-start begin-select loops=1 cls.fast_obj_title let $um_class_d = &cls.fast_obj_title from sysadm.ps_um_obj_xlat cls where cls.deptid = &ra.deptid and cls.project_id = &ra.project_id and cls.program_code = &ra.program_code and cls.fund_code = &ra.fund_code and cls.operating_unit = &ra.operating_unit and cls.account = &ra.account and cls.class_fld = &ra.class_fld and cls.fast_obj_title <> ' ' order by fast_obj_cd desc ! ??0507 pick highest ordered objcd end-select #debugf do show-proc-end End-Procedure get-class-stuff !*********************************************************************** ! init-fields Procedure !*********************************************************************** Begin-Procedure init-fields let $Calling_Procedure = 'init-fields' #debugf do show-proc-start ! a semi-smart initialization routine evaluate $init_this when = 'PRJ' !project let $prj_st = ' ' let $prj_descr = ' ' let $prj_status = ' ' let $prj_start_dt = '' let $prj_end_dt = '' let $prj_mgr_id = ' ' let $prj_mgr = ' ' let $prj_stat_effdt = '' ! ??0508 let $prj_mgr_effdt = '' ! ??0508 ! Chartfield Variables let $um_proj_facil_id = ' ' let $um_proj_facil_id_d = ' ' let $um_proj_bill_code = ' ' let $um_proj_bill_code_d = ' ' let $um_proj_cfda_n = ' ' let $um_proj_cfda_n_d = ' ' let $um_proj_fed_agcy = ' ' let $um_proj_fed_agcy_d = ' ' let $um_proj_grant_n = ' ' let $um_proj_grant_n_d = ' ' let $um_proj_gc_type = ' ' let $um_proj_gc_type_d = ' ' let $um_proj_depr_st = ' ' let $um_proj_depr_st_d = ' ' let $um_proj_loc_num = ' ' let $um_proj_loc_num_d = ' ' let $um_proj_in_srvc = '' let $um_proj_expense = ' ' let $um_proj_expense_d = ' ' let $um_proj_bld_life = ' ' ! ??0507 let $um_proj_fin_cont = ' ' ! ??0507 let $um_proj_fin_cont_d = ' ' ! ??0507 let $um_proj_gc_rpt = ' ' ! ??0507 let $um_proj_gc_rpt_d = ' ' ! ??0507 let $um_proj_impvt_life = ' ' ! ??0507 let $um_proj_loc_expire = ' ' ! ??0507 let $um_proj_closncombo = ' ' ! 06/19/2009 let $um_proj_clscombo_d = ' ' ! 06/19/2009 !Tree Attribute Variables let $proj_category = ' ' let $proj_category_d = ' ' let $proj_majsrc = ' ' let $proj_majsrc_d = ' ' let $proj_minsrc = ' ' let $proj_minsrc_d = ' ' let $proj_loc_srce = ' ' let $proj_loc_srce_d = ' ' let $proj_group = ' ' let $proj_group_d = ' ' let $proj_sub_group = ' ' let $proj_sub_group_d = ' ' let $proj_cap_type = ' ' let $proj_cap_type_d = ' ' break when = 'DPT' !dept let $dept_descr = ' ' let $dept_descrs = ' ' let $dept_st = ' ' let $dept_mgr = ' ' ! Chartfield Variables let $um_dept_yrend = ' ' let $um_dept_yrend_d = ' ' let $um_dept_hr = ' ' let $um_dept_hr_d = ' ' !Tree Attribute Variables let $cr1 = ' ' let $cr1_d = ' ' let $cr2 = ' ' let $cr2_d = ' ' let $cr3 = ' ' let $cr3_d = ' ' let $cr4 = ' ' let $cr4_d = ' ' let $dept_exp2 = ' ' let $dept_exp2_d = ' ' let $dept_exp4 = ' ' let $dept_exp4_d = ' ' break when = 'PRG' !program let $pgm_descr = ' ' let $pgm_descrs = ' ' let $pgm_st = ' ' let $pgm_mgr = ' ' !Chartfield Variables let $um_prog_yrend = ' ' let $um_prog_yrend_d = ' ' let $um_prog_expense = ' ' let $um_prog_expense_d = ' ' let $um_prog_endow_dist = ' ' ! ??0507 let $um_prog_ti_inc_dst = ' ' ! ??0507 !Tree Attribute Variables let $um_prog_cat1 = ' ' let $um_prog_cat1_d = ' ' let $um_prog_cat2 = ' ' let $um_prog_cat2_d = ' ' let $um_prog_cat3 = ' ' let $um_prog_cat3_d = ' ' let $um_prog_cat4 = ' ' let $um_prog_cat4_d = ' ' let $um_prog_restr_1 = ' ' let $um_prog_restr_1_d = ' ' let $um_prog_restr_2 = ' ' let $um_prog_restr_2_d = ' ' let $um_prog_restr_3 = ' ' let $um_prog_restr_3_d = ' ' break when = 'ACC' !account let $acct_descr = ' ' let $acct_descrs = ' ' let $acct_st = ' ' let $um_acct_bal_cls = ' ' let $um_acct_bal_cls_d = ' ' let $um_acct_cash_cls = ' ' let $um_acct_cash_cls_d = ' ' let $um_acct_fbal_cls = ' ' let $um_acct_fbal_cls_d = ' ' let $um_acct_rev_cat = ' ' let $um_acct_rev_cat_d = ' ' let $um_acct_rev_cd = ' ' let $um_acct_rev_cd_d = ' ' let $um_acct_yrend = ' ' let $um_acct_yrend_d = ' ' break when = 'NBR' !numbers let #futbud = 0 let #futbud_1 = 0 let #futbud_2 = 0 let #ly_basbud = 0 let #ly_actuals = 0 let #py_basbud = 0 let #py_actuals = 0 break when-other break end-evaluate #debugf do show-proc-end End-Procedure init-fields !*********************************************************************** ! get-end-dt Procedure !*********************************************************************** Begin-Procedure get-end-dt let $Calling_Procedure = 'get-end-dt' #debugf do show-proc-start ! default the per_end_dt to something let $per_end_dt = $AsOfToday ! adjust the per_end_dt for ap's 0, 998, 999 let #use_ap = #ap if #use_ap > 12 let #use_ap = 12 end-if if #use_ap < 1 let #use_ap = 1 end-if begin-select on-error=sql-recover ed.end_dt let $per_end_dt = &ed.end_dt from ps_CAL_DETP_TBL ed where ed.setid = 'UMSYS' and ed.calendar_id = 'F1' and ed.fiscal_year = #fy and ed.accounting_period = #use_ap end-select #debugf do show-proc-end End-Procedure get-end-dt !*********************************************************************** ! build-lookups Procedure !*********************************************************************** Begin-Procedure build-lookups let $Calling_Procedure = 'build-lookups' do show-proc-start let $ll_where = 'opn.setid = ''UMSYS'' and' || ' opn.effdt = (select max(opnef.effdt) from ps_oper_unit_tbl opnef' || ' where opn.setid = opnef.setid' || ' and opn.operating_unit = opnef.operating_unit' || ' and opnef.effdt <= ' || '''' || $per_end_dt || ''')' load-lookup name=ou_lu rows=35 table='ps_oper_unit_tbl opn' key=operating_unit return_value='eff_status || ''{'' || descr || ''{'' || descrshort' where=$ll_where let $ll_where = 'fnd.setid = ''UMSYS'' and' || ' fnd.effdt = (select max(fndef.effdt) from ps_fund_tbl fndef' || ' where fnd.setid = fndef.setid' || ' and fnd.fund_code = fndef.fund_code' || ' and fndef.effdt <= ' || '''' || $per_end_dt || ''')' load-lookup name=fnd_lu rows=50 table='ps_fund_tbl fnd' key=fund_code return_value='eff_status || ''{'' || descr || ''{'' || descrshort' where=$ll_where let $ll_where = 'dpt.setid = ''UMSYS'' and' || ' dpt.effdt = (select max(dptef.effdt) from ps_dept_tbl dptef' || ' where dpt.setid = dptef.setid' || ' and dpt.deptid = dptef.deptid' || ' and dptef.effdt <= ' || '''' || $per_end_dt || ''')' load-lookup name=dpt_lu rows=5000 table='ps_dept_tbl dpt' key=deptid return_value='eff_status || ''{'' || descr || ''{'' || descrshort || ''{'' || manager_name' where=$ll_where let $ll_where = 'pgm.setid = ''UMSYS'' and' || ' pgm.effdt = (select max(pgmef.effdt) from ps_program_tbl pgmef' || ' where pgm.setid = pgmef.setid' || ' and pgm.program_code = pgmef.program_code' || ' and pgmef.effdt <= ' || '''' || $per_end_dt || ''')' load-lookup name=pgm_lu rows=6000 table='ps_program_tbl pgm' key=program_code return_value='pgm.eff_status || ''{'' || pgm.descr || ''{'' || pgm.descrshort || ''{'' || pgm.manager_name' where=$ll_where let $ll_where = 'act.setid = ''UMSYS'' and' || ' act.effdt = (select max(actef.effdt) from ps_gl_account_tbl actef' || ' where act.setid = actef.setid' || ' and act.account = actef.account' || ' and actef.effdt <= ' || '''' || $per_end_dt || ''')' load-lookup name=act_lu rows=3000 table='ps_gl_account_tbl act' key=account return_value='eff_status || ''{'' || descr || ''{'' || descrshort' where=$ll_where let $ll_where = 'p1.business_unit = ''UMSYS''' || ' and p2.business_unit (+) = p1.business_unit' || ' and p2.project_id (+) = p1.project_id' || ' and (p2.effdt = (select max(p2ef.effdt)' || ' from ps_project_status p2ef' || ' where p2ef.business_unit = p2.business_unit' || ' and p2ef.project_id = p2.project_id' || ' and p2ef.effdt <= ' || '''' || $per_end_dt || ''') or p2.effdt is null)' ! || 'and (p2.effseq = (select max(p2es.effseq)' ! || ' from ps_project_status p2es' ! || ' where p2es.business_unit = p2.business_unit' ! || ' and p2es.project_id = p2.project_id' ! || ' and p2es.effdt = p2.effdt) or p2.effseq is null)' || ' and p5.business_unit (+) = p1.business_unit' ! 01/09 CEC Added project segment start || ' and p5.project_id (+) = p1.project_id' || ' and (p5.effdt = (select max(p5ef.effdt)' || ' from ps_um_project_stat p5ef' || ' where p5ef.business_unit = p5.business_unit' || ' and p5ef.project_id = p5.project_id' || ' and p5ef.effdt <= ' || '''' || $per_end_dt || ''') or p5.effdt is null)' ! || 'and (p5.effseq = (select max(p5es.effseq)' ! || ' from ps_project_status p5es' ! || ' where p5es.business_unit = p5.business_unit' ! || ' and p5es.project_id = p5.project_id' ! || ' and p5es.effdt = p5.effdt) or p5.effseq is null)' !01/09 CEC Added project segment End || ' and p3.business_unit (+) = p1.business_unit' || ' and p3.project_id (+) = p1.project_id' || ' and (p3.effdt = (select max(p3ef.effdt)' || ' from ps_project_mgr p3ef' || ' where p3ef.business_unit = p3.business_unit' || ' and p3ef.project_id = p3.project_id' || ' and p3ef.effdt <= ' || '''' || $per_end_dt || ''') or p3.effdt is null)' || ' and p4.emplid (+) = p3.project_manager' load-lookup name=prj_lu rows=13000 table='ps_project p1, ps_project_status p2, ps_project_mgr p3, ps_personal_data p4, ps_um_project_stat p5' key=p1.project_id !??0308 return_value='p1.eff_status || ''{'' || p1.descr || ''{'' || p2.project_status ||''{'' || p2.start_dt || ''{'' || p2.end_dt || ''{'' || p3.project_manager || ''{'' ||p4.name || ''{'' ||p2.effdt || ''{'' ||p3.effdt' !UM_MOD CEC 03/2009 Added to_char to correct date issue. return_value='p1.eff_status || ''{'' || p1.descr || ''{'' || p2.project_status ||''{'' || to_char(p5.start_dt,''MM/DD/YYYY'') || ''{'' || to_char(p5.end_dt,''MM/DD/YYYY'') || ''{'' || p3.project_manager || ''{'' ||p4.name || ''{'' ||to_char(p2.effdt,''MM/DD/YYYY'') || ''{'' ||to_char(p3.effdt,''MM/DD/YYYY'')' where=$ll_where do show-proc-end End-Procedure build-lookups !*********************************************************************** ! get-curr-ap Procedure !*********************************************************************** Begin-Procedure get-curr-ap let $Calling_Procedure = 'get-curr-ap' #debugf do show-proc-start begin-select cy.fiscal_year, cy.accounting_period let #curr_fy = &cy.fiscal_year let #curr_ap = &cy.accounting_period from sysadm.ps_cal_detp_tbl cy where cy.setid = 'UMSYS' and cy.calendar_id = 'F1' and to_char(sysdate,'DD-MON-YYYY') >= cy.begin_dt and to_char(sysdate,'DD-MON-YYYY') <= cy.end_dt end-select #debugf do show-proc-end End-Procedure get-curr-ap !*********************************************************************** ! show-proc-start Procedure !*********************************************************************** Begin-Procedure Show-proc-start add 1 to #show-proc-count let #space-count = #show-proc-count * 3 let $lead_space = rpad(' ',#space-count,' ') let $time_s = datenow() let $show_time = edit($time_s,'HH:MI:SS.NNNNNNN') show $lead_space 'Start procedure ' $Calling_Procedure ' ' $show_time ' ' $show_addl let $show_addl = '' End-procedure show-proc-start !*********************************************************************** ! show-proc-end Procedure !*********************************************************************** Begin-Procedure Show-proc-end let #space-count = #show-proc-count * 3 let $lead_space = rpad(' ',#space-count,' ') let $time_e = datenow() let #secs = datediff($time_e,$time_s,'second') let $show_time = edit($time_e,'HH:MI:SS.NNNNNN') show $lead_space 'End procedure ' $calling_procedure ' ' $show_time ' Elaspsed secs: ' #secs subtract 1 from #show-proc-count End-procedure Show-proc-end !*********************************************************************** ! SQL-Recover Procedure !*********************************************************************** Begin-Procedure sql-Recover if substr($Sql-Error,1,10) = 'ORA-00001:' !Skip if it is a duplicate else show 'Calling Procedure > ' $Calling_Procedure display $SQL-Error if $Calling_Procedure = 'update-glq-reprting' do show-some-stuff end-if stop end-if End-Procedure sql-Recover !*********************************************************************** ! show-some-stuff Procedure !*********************************************************************** Begin-Procedure show-some-stuff let $Calling_Procedure = 'show-some-stuff' ! show $Calling_Procedure show &ra.fiscal_year show &ra.accounting_period show &ra.business_unit show &ra.deptid show &ra.project_id show &ra.program_code show &ra.fund_code show &ra.operating_unit show &ra.account show &ra.class_fld show &ra.statistics_code show #futbud show #futbud_1 show #futbud_2 show #ly_basbud show #ly_actuals show #py_basbud show #py_actuals show $bu_descr show $bu_descrs show $dept_descr show $dept_descrs show $dept_st show $dept_mgr show $um_dept_yrend show $um_dept_yrend_d show $cr1 show $cr1_d show $cr2_d show $cr3 show $cr3_d show $cr4 show $cr4_d show $dept_exp2 show $dept_exp2_d show $dept_exp4 show $dept_exp4_d show $prj_st show $prj_descr show $prj_status show $prj_mgr show $prj_start_dt show $prj_end_dt show $um_proj_asset_num show $um_proj_asset_num_d show $um_proj_bill_code show $um_proj_bill_code_d show $um_proj_cfda_num show $um_proj_cfda_num_d show $um_proj_fed_agency show $um_proj_fed_agency_d show $um_proj_grant_num show $um_proj_grant_num_d show $um_proj_type show $um_proj_type_d show $pgm_descr show $pgm_descrs show $pgm_st show $pgm_mgr show $um_prog_yrend show $um_prog_yrend_d show $fnd_descr show $fnd_descrs show $fnd_st show $opn_descr show $opn_descrs show $opn_st show $um_acct_bal_cls show $um_acct_bal_cls_d show $um_acct_cash_cls show $um_acct_cash_cls_d show $um_acct_rev_cat show $um_acct_rev_cat_d show $um_acct_rev_cd show $um_acct_rev_cd_d show $acct_descr show $acct_descrs show $acct_st show $acct_stat_cd show $um_acct_yrend show $um_acct_yrend_d show $um_prog_endow_dist ! ??0507 show $um_prog_ti_inc_dst ! ??0507 show $um_proj_bld_life ! ??0507 show $um_proj_fin_cont ! ??0507 show $um_proj_fin_cont_d ! ??0507 show $um_proj_gc_rpt ! ??0507 show $um_proj_gc_rpt_d ! ??0507 show $um_proj_impvt_life ! ??0507 show $um_proj_loc_expire ! ??0507 show $um_proj_closncombo ! 06/19/2009 show $um_proj_clscombo_d ! 06/19/2009 show $prj_mgr_effdt ! ??0507 show $prj_stat_effdt ! ??0507 show $um_dept_hr ! 04/22/2010 show $um_dept_hr_d ! 04/22/2010 End-Procedure show-some-stuff !**************************************************************************************************************************** !****************************** unused procedures *************************************************************************** !**************************************************************************************************************************** !*********************************************************************** ! update-ly-py-stuff Procedure !*********************************************************************** Begin-Procedure update-ly-py-stuff let $Calling_Procedure = 'update-ly-py-stuff' #debugf do show-proc-start begin-sql on-error=sql-recover update sysadm.PS_UM_GLQ_RPT_TMP rc set rc.um_curr_budget_ly = #ly_basbud, rc.um_actuals_ytd_ly = #ly_actuals, rc.um_curr_budget_py = #py_basbud, rc.um_actuals_ytd_py = #py_actuals where rc.fiscal_year = #fy and rc.accounting_period = #ap and rc.business_unit = ra.business_unit !and rc_deptid = ra.deptid !FN92UPG: Modification begin and rc.deptid = ra.deptid !FN92UPG: Modification end and rc.project_id = ra.project_id and rc.program_code = ra.program_code and rc.fund_code = ra.fund_code and rc.operating_unit = ra.operating_unit and rc.account = ra.account and rc.class_fld = ra.class_fld and rc.statistics_code = ra.statistics_code end-sql #debugf do show-proc-end End-Procedure update-ly-py-stuff !*********************************************************************** ! Process-Balance-Forward Procedure !*********************************************************************** Begin-Procedure Process-Balance-Forward let $Calling_Procedure = 'Process-Balance-Forward' do show-proc-start show $Calling_Procedure ':' #FY ':' #PrevAP move 0 to #Ticker move 0 to #Ticker_Total BEGIN-SELECT F1.BUSINESS_UNIT, F1.DEPTID, F1.PROJECT_ID, F1.PROGRAM_CODE, F1.FUND_CODE, F1.OPERATING_UNIT, F1.ACCOUNT, F1.descr, F1.CLASS_FLD, F1.STATISTICS_CODE, F1.ROOT_NODE_NUM, F1.TREE_NODE, F1.UM_BASE_BUDGET, F1.UM_CURR_BUDGET, F1.UM_ACTUALS_YTD, F1.UM_PROJECT_TO_DT, F1.UM_PRE_ENCUMBRANCE, F1.UM_TOT_ENCUMBRANCE do Ticker-Tracker move &F1.BUSINESS_UNIT to $F1.BUSINESS_UNIT move &F1.DEPTID to $F1.DEPTID move &F1.PROJECT_ID to $F1.PROJECT_ID move &F1.PROGRAM_CODE to $F1.PROGRAM_CODE move &F1.FUND_CODE to $F1.FUND_CODE move &F1.OPERATING_UNIT to $F1.OPERATING_UNIT move &F1.ACCOUNT to $F1.ACCOUNT move &F1.CLASS_FLD to $F1.CLASS_FLD move &F1.STATISTICS_CODE to $F1.STATISTICS_CODE move &F1.ROOT_NODE_NUM to #F1.ROOT_NODE_NUM move &F1.TREE_NODE to $F1.TREE_NODE move &F1.DESCR to $F1.DESCR move &F1.UM_BASE_BUDGET to #F1.UM_BASE_BUDGET move &F1.UM_CURR_BUDGET to #F1.UM_CURR_BUDGET move &F1.UM_ACTUALS_YTD to #F1.UM_ACTUALS_YTD move &F1.UM_PROJECT_TO_DT to #F1.UM_PROJECT_TO_DT move &F1.UM_PRE_ENCUMBRANCE to #F1.UM_PRE_ENCUMBRANCE move &F1.UM_TOT_ENCUMBRANCE to #F1.UM_TOT_ENCUMBRANCE do Run-Forward-Inserts FROM ps_um_glq_reprting F1 WHERE F1.FISCAL_YEAR = #FY AND F1.ACCOUNTING_PERIOD = #PrevAP END-SELECT do show-proc-end End-Procedure Process-Balance-Forward !*********************************************************************** ! Run-Forward-Inserts Procedure !*********************************************************************** Begin-Procedure Run-Forward-Inserts let $Calling_Procedure = 'Run-Forward-Inserts' #debugt show-proc-start LET #ACCOUNT = to_number($F1.ACCOUNT) if ($F1.FUND_CODE = '12' or $F1.FUND_CODE = '22') AND #ACCOUNT >= 40000 AND #ACCOUNT <= 89999 AND #AP = 4 let #F1.UM_BASE_BUDGET = 0 let #F1.UM_CURR_BUDGET = 0 let #F1.UM_ACTUALS_CURR = 0 let #F1.UM_PROJECT_TO_DT = 0 end-if begin-sql on-error=sql-recover INSERT INTO ps_um_glq_rpt_tmp VALUES ( #fy ,#ap ,$F1.BUSINESS_UNIT ,$F1.DEPTID ,$F1.PROJECT_ID ,$F1.PROGRAM_CODE ,$F1.FUND_CODE ,$F1.OPERATING_UNIT ,$F1.ACCOUNT ,$F1.descr ,$F1.CLASS_FLD ,$F1.STATISTICS_CODE ,#F1.ROOT_NODE_NUM ,$F1.TREE_NODE ,#F1.UM_BASE_BUDGET ,#F1.UM_CURR_BUDGET ,0 ,#F1.UM_ACTUALS_YTD ,#F1.UM_PROJECT_TO_DT ,#F1.UM_PRE_ENCUMBRANCE ,#F1.UM_TOT_ENCUMBRANCE ,'N' ,'N' ,'N' ,'N' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,'' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ',' ','','', ' ',' ') !06/19/2009 This procedure not currently run, but is up to date. end-sql #debugt do show-proc-end End-Procedure Run-Forward-Inserts !*********************************************************************** ! Delete-Records Procedure !*********************************************************************** Begin-Procedure Delete-Records let $Calling_Procedure = 'Delete-Records' do show-proc-start ! if we are wildcarding the fy, then purge ALL data. this is a complete re-load ! truncate is much faster. let $index_dropped = 'N' if #rc_fy = 9999 ! drop the indexes.... just to speed up the process. ! first check to see which indexes exist, and only drop those. we get an error ! and program cancel if we try to drop a non-existant index. This assists in ! program restartability. do find-indexes ! oops..... never, EVER, drop this index. ! if $ps_um_glq_reprting = 'Y' ! begin-sql on-error=sql-Recover ! drop index PS_UM_GLQ_REPRTING ! end-sql ! end-if if $psaum_glq_reprting = 'Y' begin-sql on-error=sql-Recover drop index PSAUM_GLQ_REPRTING end-sql end-if if $psbum_glq_reprting = 'Y' begin-sql on-error=sql-Recover drop index PSBUM_GLQ_REPRTING end-sql end-if let $index_dropped = 'Y' begin-sql truncate table ps_um_glq_reprting end-sql begin-sql truncate table ps_um_glq_summary end-sql else ! if we are wildcarding the accounting period, then we must delete in segments ! with intervening commits. if #rc_ap = 99 begin-sql delete ps_um_glq_reprting [$where_clause] and accounting_period < 2; end-sql do Process-Commit begin-sql delete ps_um_glq_reprting [$where_clause] and accounting_period < 4 end-sql do Process-Commit begin-sql delete ps_um_glq_reprting [$where_clause] and accounting_period < 6 end-sql do Process-Commit begin-sql delete ps_um_glq_reprting [$where_clause] and accounting_period < 8 end-sql do Process-Commit begin-sql delete ps_um_glq_reprting [$where_clause] and accounting_period < 10 end-sql do Process-Commit begin-sql delete ps_um_glq_reprting [$where_clause] and accounting_period < 12 end-sql do Process-Commit begin-sql delete ps_um_glq_reprting [$where_clause] end-sql begin-sql delete ps_um_glq_summary [$where_clause] and accounting_period < 2; end-sql do Process-Commit begin-sql delete ps_um_glq_summary [$where_clause] and accounting_period < 4 end-sql do Process-Commit begin-sql delete ps_um_glq_summary [$where_clause] and accounting_period < 6 end-sql do Process-Commit begin-sql delete ps_um_glq_summary [$where_clause] and accounting_period < 8 end-sql do Process-Commit begin-sql delete ps_um_glq_summary [$where_clause] and accounting_period < 10 end-sql do Process-Commit begin-sql delete ps_um_glq_summary [$where_clause] and accounting_period < 12 end-sql do Process-Commit begin-sql delete ps_um_glq_summary [$where_clause] end-sql else ! if we are not wildcarding anything, then delete according to the where clause. Do NOT drop the indexes begin-sql delete ps_um_glq_reprting [$where_clause] end-sql do Process-Commit begin-sql delete ps_um_glq_summary [$where_clause] end-sql end-if end-if do truncate-tmp do Process-Commit do show-proc-end End-Procedure Delete-Records !*********************************************************************** ! find-indexes Procedure !*********************************************************************** Begin-Procedure find-indexes show 'find indexes...... ' let $Calling_Procedure = 'find-indexes' let $ps_um_glq_reprting = 'N' let $psaum_glq_reprting = 'N' let $psbum_glq_reprting = 'N' begin-select INDEX_NAME if &INDEX_NAME = 'PS_UM_GLQ_REPRTING' let $ps_um_glq_reprting = 'Y' end-if if &INDEX_NAME = 'PSAUM_GLQ_REPRTING' let $psaum_glq_reprting = 'Y' end-if if &INDEX_NAME = 'PSBUM_GLQ_REPRTING' let $psbum_glq_reprting = 'Y' end-if FROM DBA_INDEXES WHERE TABLE_NAME = 'PS_UM_GLQ_REPRTING' end-select End-Procedure find-indexes !*********************************************************************** ! build-indexes Procedure !*********************************************************************** Begin-Procedure build-indexes let $Calling_Procedure = 'build-indexes' do show-proc-start begin-sql on-error=sql-Recover CREATE INDEX PSAUM_GLQ_REPRTING ON PS_UM_GLQ_REPRTING (FUND_CODE) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 NOLOGGING end-sql begin-sql on-error=sql-Recover CREATE INDEX PSBUM_GLQ_REPRTING ON PS_UM_GLQ_REPRTING (BUSINESS_UNIT) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 NOLOGGING end-sql do show-proc-end End-Procedure build-indexes !*********************************************************************** ! Include Procedures !*********************************************************************** #Include 'reset.sqc' !Reset printer procedure #Include 'curdttim.sqc' !Get-Current-DateTime procedure #Include 'datetime.sqc' !Routines for date and time formatting #Include 'number.sqc' !Routines to format numbers #Include 'stdapi.sqc' !Update Process API #include 'validdt.sqc' !Validate dates #include 'tdfunc.sqc' !Parse comma delimited file #include 'umfnfunc.sqc' !Ticker-Tracker and more.... !********************************************************************** !* End of Program * !**********************************************************************