The method I embarked on is a bit abstract... completely disregarding the massive (& possibly outdated) Combo Data table & dynamically leveraging the combo edit definitions directly. Although there's complexity when cycling through the definitions for anchors, chartfields, groups, rules, treenodes & values the SQL footprint is very thin & efficient - even when working with high volumes of transactional data - results are fast & accurate. When I initially explained my approach one of the GL functional consultants, possibly unfamiliar with non-vanilla concepts & advanced development techniques, somewhat arrogantly smirked "Good luck with that...". Since that remark the process has been running for over 15 years without a single issue.
Above: The animated GIF above demonstrates the dynamic combination edit process. Validation is applied at the GL BU, Definition, Group & Rule levels. This particular demo is for a multi-BU batch process using SQR that I launched on my desktop.
General Overview:
Combination
Edits are used to validate combinations of GL
chartfield values relative to one another and prevents these invalid
combinations from posting to the ledger. For example, an account may
require a
department contained in a specific node or group of nodes in the
department
tree. A department may require a product within a group of values. Each
business unit may have their own set of requirements & rules. Some
examples of chartfields
are Account, Department, Product or User-Defined fields (CHARTFIELD1
thru 3).
The combination rules are contained in Process Groups – all must be
applied to
determine the combination’s validity. A typical PeopleSoft installation
will
schedule the Combo Build (FS_CEBD) process to run each night – this
reads the
definitions & populates the COMBO_DATA_TBL with "every possible
chartfield combination"
for each set of rules. Delivered PeopleSoft processes (Journal Edit for
example) will use this pre-built table for validation.
At my current company the build process runs for
45 minutes & creates over 20 million rows of data. My experience with larger
companies that have more complex accounting easily surpasses 100 million rows –
selection against the COMBO_DATA table is very slow. Also, the table reflects the data at
the time of creation – a newly entered chartfield value or tree node will not
be included until the build process is run again - even if it's implied within a range of tree leafs.
This blog post highlights an alternate method I devised which ignores
the Combo Data results & utilizes the actual Combo Edit Definitions. The
rules are applied dynamically in real-time against the chartfields. This allows
external data sources – batch data files or online applications (PeopleSoft or
external 3rd Party Applications such as Content Server or OnBase) to
have instant Combo Edit Validation.
Relevant Configuration Tables:
RECNAME DESCRIPTION
--------------- ------------------------------
BU_LED_COMB_TBL ChartField Combos for Ledger
COMBO_FLDS_TBL Combination ChartFields Table
COMBO_CF_DEFN Combination CF Definition
COMBO_GROUP_TBL ChartField Combination Groups
COMBO_RULE_TBL ChartField Combination Rule
COMBO_GRRUL_TBL CF Combination Group Rules
COMBO_CF_TBL ChartField Combination
COMBO_CF2_TBL ChartField Combination Fields
COMBO_CF2_REQ Combo CF2 Required Flag
COMBO_VAL_TBL ChartField Combo Value Table
COMBO_VAL2_TBL ChartField Combo Value Table
PSTREEDEFN Tree Definition
PSTREENODE Tree Node Definition
PSTREELEAF Tree Leaf Range(s)
--------------- ------------------------------
The above tables are utilized to dynamically apply combination edit validations.
PeopleSoft Combination Edit Pages:
Above: Specifying the Chartfields for the ACCDELDF1 Definition. It defines the combination of Acount, Department & Local 1 (CHARTFIELD2).
Above: Creating a group (EDITORIAL) & adding a rule (also named EDITORIAL).
Above: Configuring the rule EDITORIAL.
Above: The EDITORIAL rule uses explicit values for the chartfield ACCOUNT and tree nodes for the DEPARTMENT & LOCAL1 chartfields. (Local1 is user-defined chartfield CHARTFIELD2).
Notice this one rule utilizes multiple specifications - list of values, multiple nodes from Tree.DEPT and multiple nodes from Tree.LOCAL1.
PeopleSoft Online Demonstration:
Above: Custom Combination Edit Demonstration Page.
The Combination Edit Utility page allows the user to enter a combination of GL Chartfields then immediately receive the validation results when pressing the "EDIT" key. All rules that failed are displayed in the Combo Edit Errors area. All currently active Combo Edit Rules are also displayed.
The PeopleCode FieldChange event passes a chartfield string, delimited by tildes (~), to my custom PL/SQL function XX_CE. It will return a null if valid or a tilde (~) delimited list of all combination edit rules that failed.
XX_CE_WRK.PB_EDIT.FieldChange:
&Z1 = XX_CE_WRK.BUSINESS_UNIT_GL.Value;
&Z2 = XX_CE_WRK.ACCOUNT.Value;
&Z3 = XX_CE_WRK.DEPTID.Value;
&Z4 = XX_CE_WRK.CHARTFIELD2.Value;
&Z5 = XX_CE_WRK.CHARTFIELD3.Value;
&Z6 = XX_CE_WRK.PRODUCT.Value;
&Z7 = XX_CE_WRK.CHARTFIELD1.Value;
&Z8 = XX_CE_WRK.AFFILIATE.Value;
&COMBO = &Z1 | "~" | &Z2 | "~" | &Z3 | "~" | &Z4 | "~" | &Z5 | "~" | &Z6 | "~" | &Z7 | “~” | &Z8;
/* Modify Tilde (~) Delimiter for use with Substitute Function for CRLF */
SQLExec("SELECT REPLACE(SYSADM.XX_CE(:1),'~','$') FROM DUAL", &COMBO, &RESULT);
&CRLF = Char(13) | Char(10);
XX_CE_WRK.MESSAGE_DESCR.Value = Substitute(&RESULT, "$", &CRLF);
&MSG_RULES = "";
&SQL_CR = GetSQL(SQL.XX_CE_RULES, &Z1);
While &SQL_CR.Fetch(&CE_RULE)
&MSG_RULES = &MSG_RULES | &CE_RULE | &CRLF;
End-While;
&SQL_CR.Close();
XX_CE_WRK.MESSAGE_TEXT_LONG.Value = &MSG_RULES;
External Online Demonstration:
Above: This is a WinBatch Combo Edit Demo that runs on the Windows Desktop.
This demo application allows the user to enter the GL chartfields and performs the edit when the "GO" key is pressed. Like the prior online PeopleSoft example it calls the PL/SQL function XX_CE. The entered combination has been flagged for violating 4 Combo Edit rules.
;**********************************************************************
;* Execute Edit *
;**********************************************************************
:Execute_Edit
CE_string = StrCat(TDunit,"~",TDacct,"~",TDdept,"~",TDcf2,"~",TDcf2,"~",TDprod,"~",TDcf1, "~",TDaffl)
CE_sql = "SELECT XX_CE('%CE_string%') FROM DUAL"
ErrorMode(@OFF)
ORAds = ORA_conn.CreateDynaset(CE_sql, 0)
ErrorMode(@CANCEL)
TDORA_ValidSQL(ORA_conn, CE_sql, "Create Dynaset", @TRUE)
ORAds.MoveFirst
while ! ORAds.EOF
O_val = TDORA_SelectValue(ORAds, 0)
ORAds.MoveNext
endwhile
ObjectClose(ORAds)
TDlist = StrReplace(O_val, "~", @TAB)
TDmsg = "Chartfield Combination is Valid"
if TDlist <> ""
TDmsg = "Invalid Chartfield Combination"
endif
Return
;**********************************************************************
[Real World Examples]
Custom PeopleSoft AP Invoices:
Above: Concur Recycle page. When the [Recycle] button is pressed the GL coding on each invoice line is validated. If a combo error is detected the user must correct - if there are no errors the invoice is queued to process again.
Note - Concur Invoice is an external 3rd Party Application. When an interface file is sent they do not have the capability to make corrections & resend. I developed a "Recycle" page so our users may apply corrections directly in PeopleSoft & quickly reprocess. This is part of our custom 24/7 interface process.
Below is a portion of the PeopleCode invoked when the [Recycle] button is pressed. The chartfields are passed as a tilde (~) delimited string to the XX_CE() Combination Edit function. Simple.
Function CCR_Edit() Returns boolean;
DoSave();
...
<SNIP>
...
&MAX = &RS_DTL.ActiveRowCount;
For &X = 1 To &MAX Step 1
&Z1 = &RS_DTL(&X).XX_CCR_INV_DTL.BUSINESS_UNIT_GL.Value;
&Z2 = &RS_DTL(&X).XX_CCR_INV_DTL.ACCOUNT.Value;
&Z3 = &RS_DTL(&X).XX_CCR_INV_DTL.DEPTID.Value;
&Z4 = &RS_DTL(&X).XX_CCR_INV_DTL.CHARTFIELD2.Value;
&Z5 = &RS_DTL(&X).XX_CCR_INV_DTL.CHARTFIELD3.Value;
&Z6 = &RS_DTL(&X).XX_CCR_INV_DTL.PRODUCT.Value;
&Z7 = &RS_DTL(&X).XX_CCR_INV_DTL.CHARTFIELD1.Value;
&Z8 = &RS_DTL(&X).XX_CCR_INV_DTL.AFFILIATE.Value;
&COMBO = &Z1 | "~" | &Z2 | "~" | &Z3 | "~" | &Z4 | "~" | &Z5 | "~" | &Z6 | "~" | &Z7 | “~” | &Z8;
SQLExec("SELECT XX_CE(:1) FROM DUAL", &COMBO, &RESULT);
If All(&RESULT) Then
...
<SNIP>
...
End-If;
End-For;
...
<SNIP>
...
End-Function;
Content Server - P-Card Workflow:
Below is a portion of the OScript Code to perform the combination Edit.
Assoc rtn = ._InitErrorAssoc( "ValidateInvoiceLine" )
String bu = line.business_unit
String acct = line.account
String dept = line.department
String local1 = line.local1
String local2 = line.local2
String edition = line.edition
String project = line.project
// *** check chart of accounts combos
String validationStr = bu + "~" + acct + "~" + dept + "~" + local1 + "~" + local2 + "~" + edition + "~" + project
Dynamic result = ._ExecuteSQL( prgCtx.fDbConnect, "SELECT NVL( SYSADM.XX_CE@FS(:A1), 'OK' ) RESULT FROM DUAL", { validationStr } )
Content Server - AP Workflow:
Above: Also from our Customized Content Server Document Management System (LiveLink/OpenText). AP Invoice Workflow - it also calls the XX_CE() Combination Edit function when the [Add Line] button is pressed. Errors are identified & corrected on the spot.
Below is the OScript - very similar to the previous Content Server example.
String validationStr = bu + "~" + acct + "~" + dept + "~" + local1 + "~" + local2 + "~" + edition + "~" + project
Assoc retVal
retVal.OK = FALSE
retVal.result = FALSE
Dynamic result = CAPI.Exec( connection, "SELECT NVL( sysadm.XX_CE@FS(:A1), 'OK' ) RESULT FROM DUAL", validationStr )
Batch Processing:
Back in 2005 I was tasked with designing a highly sophisticated (and unique) interface processing system that operates on a fully automated 24/7 basis. When a file is transferred to our "File Depot" the associated job is queued via Process Scheduler within 5 minutes. The data is received from a variety of sources - JPMC, Fidelity, Concur, Hyperion Essbase, Money Network, Excel Spreadsheets, Mactive, Legacy Circulation Systems, Mactive, etc... For AP (Invoices/Distribution) & GL (AcctgLines/Journals/Budgets) I invoke the Dynamic Combination Edits in Batch Mode. It's similar to the XX_CE() online PL/SQL function except it applies the edits to an entire batch at a time (set processing updates). As part of our extensive interface audit procedures I retain a copy of all the rules in effect for each & every file.
The example below is for a Concur Invoice interface (designated by an origin of CCR). The batch is 19000-CCR-92531 - although this is under AP BU 19000 the interunit accounting is detected which uses BU's 19000 and 37601.
Below is a portion of the LOG for Batch 19000-CCR-92531. SQL is created dynamically for each rule – including Select Detail or Tree Node Access – and then applied via mass updates at the rule level. The execution time is a fraction of a second. Back in 2005, I stress tested our interface processes by running over 30 transaction files, each with over 100,000 rows, simultaneously. All processes were queued with the only delay being the active process limits of Process Scheduler. The timings in each case were excellent & 100% accurate.
[BU] 19000
[DEFN] ACCDEPT (ACCOUNT)
...[PROC] ACCDEPT
......[RULE] ACCDEPT(1)
......[RULE] ACCDEPT(2)
......[RULE] ACCDEPT(3)
......[RULE] ACCDEPT(5)
......[RULE] ACCDEPT(6)
......[RULE] ACCDEPT(7)
......[RULE] ACCDEPT(8)
[DEFN] ACCED (ACCOUNT)
...[PROC] ACCED
......[RULE] ACCED(1)
......[RULE] ACCED(2)
[DEFN] ACCPROJ (ACCOUNT)
...[PROC] ACCPROJ
......[RULE] ACCPROJ(1)
Beg[2018:01:25_11:35:25] End[2018:01:25_11:35:25] Sec[000000.12] HMS[0000:00:00.11901] Msg[NSS_Combo_Edits]
[BU] 37601
[DEFN] ACCDEPT (ACCOUNT)
...[PROC] ACCDEPT
......[RULE] ACCDEPT(1)
......[RULE] ACCDEPT(2)
......[RULE] ACCDEPT(4)
......[RULE] ACCDEPT(5)
......[RULE] ACCDEPT(6)
......[RULE] ACCDEPT(7)
......[RULE] ACCDEPT(8)
[DEFN] ACCED (ACCOUNT)
...[PROC] ACCED
......[RULE] ACCED(1)
......[RULE] ACCED(2)
[DEFN] ACCLOCAL1 (ACCOUNT)
...[PROC] ACCLOCAL1
......[RULE] ACCLOCAL1(1)
[DEFN] ACCLOCAL2 (ACCOUNT)
...[PROC] ACCLOCAL2
......[RULE] ACCLOCAL2(1)
[DEFN] ACCPROJ (ACCOUNT)
...[PROC] ACCPROJ
......[RULE] ACCPROJ(1)
[DEFN] DEPTPROJ (DEPTID)
...[PROC] DEPTPROJ
......[RULE] DEPTPROJ(1)
Beg[2018:01:25_11:35:25] End[2018:01:25_11:35:25] Sec[000000.18] HMS[0000:00:00.18499] Msg[NSS_Combo_Edits]
After all this chatter how about if I reveal the full source code for the XX_CE() function...
XX_CE.SQL - Online Combination Edit - Source Code:
/* ***************************************************************** */
/* * * */
/* * MODULE: XX_CE.SQL * */
/* * AUTHOR: TONY DELIA. * */
/* * DATE: 04/11/2005. * */
/* * DESC: COMBO EDIT FUNCTION. * */
/* * * */
/* ***************************************************************** */
/* * * */
/* * PARMS: P1 - Tilde (~) delimited string of chartfields. * */
/* * P2 - Output string of Combo Edit Errors or NULL. * */
/* * * */
/* * Input Chartfield String - Positional * */
/* * 1 - BUSINESS_UNIT * */
/* * 2 - ACCOUNT * */
/* * 3 - DEPTID * */
/* * 4 - CHARTFIELD2 (Local 1) * */
/* * 5 - CHARTFIELD3 (Local 2) * */
/* * 6 - PRODUCT (Edition) * */
/* * 7 - CHARTFIELD1 (Project) * */
/* * 8 - AFFILIATE * */
/* * * */
/* * USAGE: I_line := '12100~503100~305~001~ ~ ~ ~ '; * */
/* * xx_ce_sp(I_line, O_list); * */
/* * * */
/* * O_list contains tilde (~) delimited list of all * */
/* * Combination Edit Errors (or NULL if valid). * */
/* * Each error is represented in the format: * */
/* * * */
/* * <SETID>.<DEFN>.<GROUP>.<RULE>.<SEQ#> * */
/* * * */
/* * WRAPPER: The stored procedure may be called directly or * */
/* * thru the associated wrapper function XX_CE on a * */
/* * select statement (Example below). * */
/* * * */
/* * select x.xx_err_ce, * */
/* * x.cf_combo, * */
/* * xx_ce(x.cf_combo) xx_ce * */
/* * from (select distinct * */
/* * a.xx_err_ce, * */
/* * a.business_unit||'~'|| * */
/* * a.account ||'~'|| * */
/* * a.deptid ||'~'|| * */
/* * a.chartfield2 ||'~'|| * */
/* * a.chartfield3 ||'~'|| * */
/* * a.product ||'~'|| * */
/* * a.chartfield1 ||'~'|| * */
/* * a.affiliate cf_combo * */
/* * from ps_xx_gli_arc_dtl a * */
/* * where a.xx_idb_entity = 'ALL' * */
/* * and a.xx_batch_type = 'GLI' * */
/* * and a.xx_batch_id = 1384 * */
/* * order by 1 desc, 2) x * */
/* * order by x.xx_err_ce, * */
/* * x.cf_combo * */
/* * * */
/* ***************************************************************** */
/* * * */
/* * SCRIPTS: XX_CE.SQL - XX_CE Wrapper Function. * */
/* * XX_CE_SP.SQL - XX_CE_SP Stored Procedure. * */
/* * XX_TDF.SQL - Supporting Function Library. * */
/* * TDF_setid, TDF_treff, TDF_dbms. * */
/* * * */
/* ***************************************************************** */
/* * * */
/* * TABLES: ps_bu_led_comb_tbl - Select * */
/* * ps_combo_flds_tbl - Select * */
/* * ps_combo_cf_defn - Select * */
/* * ps_combo_group_tbl - Select * */
/* * ps_combo_rule_tbl - Select * */
/* * ps_combo_grrul_tbl - Select * */
/* * ps_combo_cf_tbl - Select * */
/* * ps_combo_cf2_tbl - Select * */
/* * ps_combo_cf2_req - Select * */
/* * ps_combo_val_tbl - Select * */
/* * ps_combo_val2_tbl - Select * */
/* * pstreedefn - Select * */
/* * pstreenode - Select * */
/* * pstreeleaf - Select * */
/* * * */
/* ***************************************************************** */
/* ***************************************************************** */
/* * REVISIONS: * */
/* ***************************************************************** */
/* * * */
/* * DATE PROGRAMMER DESCRIPTION * */
/* * ---------- --------------- ---------------------------------- * */
/* * * */
/* * 04/11/2005 DELIA,TONY ORIGINAL CODING. * */
/* * * */
/* ***************************************************************** */
create or replace function xx_ce(I_line in varchar2)
return varchar2
is
O_list varchar2(512) := ' ';
BEGIN
xx_ce_sp(I_line, O_list);
return O_list;
END;
/
/* ***************************************************************** */
/* * End of PL/SQL Program * */
/* ***************************************************************** */
Of course, the source code of XX_CE.SQL doesn't provide much insight into the dynamic combination edits... it's merely a function wrapper for my stored procedure XX_CE_SP.
Below is a very small snippet of code from the main stored procedure... In my code I draw heavily from my strong Assembler background - that's why my code is always tight, streamlined & efficient.
/* ***************************************************************** */
/* * COMBO Rule Match * */
/* ***************************************************************** */
PROCEDURE COMBO_Rule_Match
IS
BEGIN
SQL_exec := SQL_base || COMBO_match;
EXECUTE IMMEDIATE SQL_exec into CE_I_flag;
if CE_I_flag > 0 then
if CE_I_id = bitand(CE_R_anchor, CE_I_id) then
CE_R_match := CE_R_match + CE_I_id;
CE_P_match := CE_P_match + CE_I_id;
CE_R_invalid := CE_R_invalid + (CE_I_id * CG_direction);
CE_P_invalid := CE_P_invalid + (CE_I_id * CG_direction);
end if;
end if;
END COMBO_Rule_Match;
/* ***************************************************************** */
Designing & developing the Dynamic Combination Edit functionality was a fun & interesting exercise. An abstract challenge with success being a foregone conclusion. In the words of the great Colonel John "Hannibal" Smith - "I love it when a plan comes together".
