I am working on a development in which I insert a log file in a table. The file I insert is something similar to this:
***ORACLE COA MX Journal Import Execution Report Date: 2022-10-11 14:23
Process ID: 890555. Page: 1
Total Total Total Unbalanced Unbalanced Flex Nonflex
Journal Entry Source Name Group ID Status Lines Batches Headers Batches Headers Errors Errors
---------------------------- ------------------ ------- ------- ------- ------- ---------- ---------- ---------- ----------
Manual 90005 Error 1 1 1 1 1 0 0
---------------------------- ------------------ ------- ------- ------- ------- ---------- ---------- ---------- ----------
*** TOTALS *** 1 1 1 1 1 0 0
======================================================= Batches Created ========================================================
Total Total Total Total
Warning Batch Name Period Name Lines Headers Accounted Debits Accounted Credits
------- ------------------------------------- ---------- ------- ------- ---------------- ----------------
================================================= Unbalanced Journal Entries** =================================================
Error Total
Code Journal Entry Name Batch Name Lines Period Name Total Debits Total Credits
----- ------------------------------------ ------------------------------------ ----- ----------- ---------------- ----------------
EU02 Adjustment SEM980701STA Importación de asientos SOAPUI 11102 1 Oct-22 1,300.00 0.00
========================================================= Error Lines ==========================================================
Accounting
Error Code Source Date Currency Entered Debit Entered Credit Account
------------------------------ ----------- ----------- -------- ---------------- ---------------- ------------------------
========================================================== Error Key ===========================================================
Period Error Codes
------------------
EP01 This date isn't in an open or future enterable period.
EP02 This ledger doesn't have an open or future enterable periods.
EP03 This date isn't within a period in an open encumbrance year.
EP04 This date isn't a business day.
EP05 There are no business days in this period.
Unbalanced Journal Error Codes
------------------------------
WU01 The journal entry is unbalanced. Suspense posting is allowed in this ledger and processed the entry.
EU02 The journal entry is unbalanced and suspense posting isn't allowed in the ledger.
EU03 The encumbrance journal entry is unbalanced and the Reserve for Encumbrance account isn't defined.
Flexfield Error Codes
---------------------
EF01 The account is inactive for the accounting date.
EF02 Detail posting isn't allowed for this account.
EF03 There are disabled accounts.
EF04 The account is invalid. Check your cross validation rules and segment values.
EF05 There is no account with this account combination ID.
EF06 The alternate account is invalid.
WF01 An alternate account was used instead of the original account.
WF02 A suspense account was used instead of the original account.
ORACLE COA MX Journal Import Execution Report Date: 2022-10-11 14:23
Process ID: 890555. Page: 2
========================================================== Error Key ===========================================================
Foreign Currency Error Codes
----------------------------
EC01 A conversion rate must be entered with the User conversion rate type.
EC02 The conversion date is missing.
EC03 A conversion rate type or an accounted amount must be supplied when entering foreign currency journal lines.
EC06 There is no conversion rate for this currency, conversion rate type, and conversion date.
EC08 There are invalid currencies.
EC09 No currencies are enabled.
EC10 Encumbrance journals can't be created in a foreign currency.
EC11 Invalid conversion rate type.
EC12 The entered amount must equal the accounted amount in a ledger or statistical currency journal line.
EC13 Amount is too large.
ECW1 Converted amounts can't be validated because the conversion rate type isn't specified.
Encumbrance Error Codes.
-----------------------
EE01 An encumbrance type is required for encumbrance lines.
EE02 Invalid or disabled encumbrance type.
EE03 Encumbrance journals can't be created in the STAT currency.
EE04 The BUDGET_VERSION_ID column must be null for encumbrance lines.
EE05 Average journals can't be created for encumbrances.
EE06 Originating company information can't be specified for encumbrances.
EE07 Encumbrance accounting is not enabled
Reversal Error Codes
------------------
ER01 A reversal period name must be provided.
ER02 This reversal period name is invalid. Check your calendar for valid periods.
ER03 The reversal date must be provided
ER04 This reversal date isn't in a valid period.
ER05 This reversal date isn't in your database date format.
ER06 Your reversal date must be the same as or after your effective date.
ER07 This reversal date isn't a business day.
ER08 There are no business days in your reversal period.
ER09 Default reversal information couldn't be determined.
Descriptive Flexfield Error Codes
--------------------------------
ED01 The context and attribute values don't form a valid descriptive flexfield for Journals - Journal Entry Lines.
ED02 The context and attribute values don't form a valid descriptive flexfield for Journals - Captured Information.
ED03 The context and attribute values don't form a valid descriptive flexfield for Value Added Tax.
Miscellaneous Error Codes
--------------------------------
EM01 Invalid journal entry category.
EM02 There are no journal entry categories defined.
EM05 The ENCUMBRANCE_TYPE_ID column must be null for actual journals.
EM06 The BUDGET_VERSION_ID column must be null for actual journals.
EM07 The statistical amount belongs in the entered_dr or entered_cr column when entering a STAT currency journal line.
EM09 There is no transaction code defined.
EM10 There are invalid transaction codes.
EM12 An error occurred when generating sequential numbering.
EM13 The assigned sequence is inactive.
EM14 There is a sequential numbering setup error resulting from a missing grant or synonym.
EM17 Sequential numbering is always used and there is no assignment for this ledger and journal entry category.
EM18 Manual document sequences can't be used with Journal Import.
EM19 Value Added Tax data is only valid in conjunction with actual journals.
EM24 Average journals can only be imported into consolidation ledgers.
EM25 Invalid average journal column value. Valid values are {Y_CODE, {N_CODE, and null.
ORACLE COA MX Journal Import Execution Report Date: 2022-10-11 14:23
Process ID: 890555. Page: 3
========================================================== Error Key ===========================================================
Miscellaneous Error Codes
--------------------------------
EM26 There are invalid originating companies.
EM27 Originating company information can only be specified when intercompany balancing is enabled.
EM29 You don't have access to this ledger and account combination.
EM30 This primary balancing segment value isn't valid for this ledger.
EM31 This management segment value isn't valid for this ledger.
EM32 The balancing segment value entered isn't valid for this legal entity.
EM33 The management segment value entered isn't valid for this legal entity.
EM34 The legal entity identifier must be populated because the ledger sequencing mode is set to legal entity.
EM35 The legal entity identifier must be NULL because the ledger sequencing mode isn't enabled.
Federal Financials Error Codes
--------------------------------
EG01 The GL journal import prevalidation process couldn't be validated. You must verify the ledger setup.
EG02 You must enter a valid Business Event Type Code.
EG03 The BETC value can't be derived. You must enter a valid BETC.
EG04 You must keep the BETC value blank because an imported invoice is matched to either a purchase order or receipt.
EG05 You must enter a valid value for the federal or nonfederal code.
EG06 You must enter a valid trading partner TAS value.
EG07 You must enter the required trading partner TAS for suppliers when the federal or nonfederal code is either Federal or General Fund.
EG08 You can't enter the trading partner TAS and BETC unless the federal or nonfederal code is Federal or General Fund.
EG09 You must enter a valid trading partner BETC.
EG10 You must enter the trading partner BETC for suppliers when the federal or nonfederal code is either Federal or General Fund.
EG11 You can't enter the trading partner TAS and BETC unless the federal or nonfederal code is Federal or General Fund.
EG12 You must enter a valid agency location code.
EG13 You must enter the agency location code.
EG14 You can't enter an agency location code for this transaction.
EG15 You must enter Y, N, or blank in the Data Act Reporting field.
EG16 You must select a value for the data act award type when the data act reportable indicator is enabled.
EG17 You must enter a valid data act award type value.
EG18 You must enter the procurement instrument identifier for procurement awards.
EG19 You can't enter a procurement instrument identifier for financial assistance awards.
EG20 You can't enter the parent award for financial assistance awards.
EG21 You can't enter the FAIN for procurement awards.
EG22 You must enter either FAIN or URI for financial assistance awards.
EG23 You must enter either FAIN or URI but not both at the same time.
EG24 You can't enter the URI for procurement awards.
** Batches listed under Unbalanced Batches** weren't imported.
***** End of Report ********
From this file I want to get 2 separate blocks, the first block should contain the following:
Total Total Total Unbalanced Unbalanced Flex Nonflex
Journal Entry Source Name Group ID Status Lines Batches Headers Batches Headers Errors Errors
---------------------------- ------------------ ------- ------- ------- ------- ---------- ---------- ---------- ----------
Manual 90005 Error 1 1 1 1 1 0 0
---------------------------- ------------------ ------- ------- ------- ------- ---------- ---------- ---------- ----------
*** TOTALS *** 1 1 1 1 1 0 0
and the second block should be:
================================================= Unbalanced Journal Entries** =================================================
Error Total
Code Journal Entry Name Batch Name Lines Period Name Total Debits Total Credits
----- ------------------------------------ ------------------------------------ ----- ----------- ---------------- ----------------
EU02 Adjustment SEM980701STA Importación de asientos SOAPUI 11102 1 Oct-22 1,300.00 0.00
Finally, I want to obtain the error description. In this case, when obtaining EU02, I must obtain the description of the list:
EU02 The journal entry is unbalanced and suspense posting isn't allowed in the ledger.
I have been testing with the query:
SELECT
REGEXP_SUBSTR(v_filecontent,
'Journal Entry Source Name Group ID Status Lines Batches Headers Batches Headers Errors Errors
---------------------------- ------------------ ------- ------- ------- ------- ---------- ---------- ---------- ----------[^,] *** TOTALS ***') "REGEXPR_SUBSTR" into v_resume
FROM DUAL;
CodePudding user response：
With your full text as above (stored it in the CLOB column of a table A_TBL)...
You can try it like this:
SET SERVEROUTPUT ON
Declare
FullText VarChar2(32767);
txt_1 VarChar2(4000);
txt_2 VarChar2(4000);
txt_errs VarChar2(4000);
errs VarChar2(4000);
err_list VarChar2(4000) := '';
old_err_list VarChar2(4000) := '';
errors VarChar2(4000) := '';
--
s1_start Number(5);
s1_end Number(5);
s2_start Number(5);
s2_end Number(5);
err_start Number(5);
err_end Number(5);
err_codes_start Number(5);
--
counter Number(2) := 0;
--
Begin
Select TXT Into FullText From A_TBL Where A1G = 123;
--
s1_start := InStr(FullText, 'Page: 1', 1, 1) Length('Page: 1');
s1_end := InStr(FullText, '======================================================= Batches Created', 1, 1) - 1;
txt_1 := SubStr(FullText, s1_start, s1_end - s1_start);
--
s2_start := InStr(FullText, 'Unbalanced Journal Entries** =================================================', 1, 1) 88;
s2_end := InStr(FullText, '========================================================= Error Lines', 1, 1) - 1;
txt_2 := SubStr(FullText, s2_start, s2_end - s2_start);
--
err_start := InStr(FullText, 'Unbalanced Journal Error Codes', 1, 1);
err_end := InStr(FullText, 'Flexfield Error Codes', 1, 1) - 1;
txt_errs := SubStr(FullText, err_start, err_end - err_start);
--
err_codes_start := InStr(txt_2, Chr(10), 1, 3) 1;
errs := SubStr(txt_2, err_codes_start);
For i In 1..6 Loop
If Length(errs) > 4 Then
err_list := err_list || SubStr(errs, 1, 4) || ';';
errs := SubStr(errs, InStr(errs, Chr(10), 1, 1) 1);
Else
EXIT;
End If;
End Loop;
While Length(err_list) > 4 Loop
counter := counter 1;
If counter = 1 Then
errors := errors || SubStr(txt_errs, InStr(txt_errs, SubStr(err_list, 1, 4)), InStr(txt_errs, Chr(10), InStr(txt_errs, SubStr(err_list, 1, 4)), 1) - InStr(txt_errs, SubStr(err_list, 1, 4))) || Chr(10);
else
errors := errors || SubStr(txt_errs, InStr(txt_errs, SubStr(err_list, 1, 4)), InStr(txt_errs, Chr(10), InStr(txt_errs, SubStr(old_err_list, 1, 4)), 1) - InStr(txt_errs, SubStr(old_err_list, 1, 4))) || Chr(10);
End If;
old_err_list := err_list;
err_list := REPLACE(err_list, SubStr(err_list, 1, 5), '');
End Loop;
DBMS_OUTPUT.PUT_LINE('S e c t i o n 1 ');
DBMS_OUTPUT.PUT_LINE(txt_1);
DBMS_OUTPUT.PUT_LINE('S e c t i o n 2 ');
DBMS_OUTPUT.PUT_LINE(txt_2);
DBMS_OUTPUT.PUT_LINE('E r r o r s ');
DBMS_OUTPUT.PUT_LINE(errors);
End;
/*
R e s u l t :
S e c t i o n 1
Total Total Total Unbalanced Unbalanced Flex Nonflex
Journal Entry Source Name Group ID Status Lines Batches Headers Batches Headers Errors Errors
---------------------------- ------------------ ------- ------- ------- ------- ---------- ---------- ---------- ----------
Manual 90005 Error 1 1 1 1 1 0 0
---------------------------- ------------------ ------- ------- ------- ------- ---------- ---------- ---------- ----------
*** TOTALS *** 1 1 1 1 1 0 0
S e c t i o n 2
Error Total
Code Journal Entry Name Batch Name Lines Period Name Total Debits Total Credits
----- ------------------------------------ ------------------------------------ ----- ----------- ---------------- ----------------
EU02 Adjustment SEM980701STA Importación de asientos SOAPUI 11102 1 Oct-22 1,300.00 0.00
E r r o r s
EU02 The journal entry is unbalanced and suspense posting isn't allowed in the ledger.
*/
For Loop generates the list of error codes from section 2 and While Loop gets the error texts from 'Unbalanced Journal Error Codes' part of your full text. I have tryed it with two error codes in section 2, and it works.
With Section 2 like here:
/*
S e c t i o n 2
Error Total
Code Journal Entry Name Batch Name Lines Period Name Total Debits Total Credits
----- ------------------------------------ ------------------------------------ ----- ----------- ---------------- ----------------
EU02 Adjustment SEM980701STA Importación de asientos SOAPUI 11102 1 Oct-22 1,300.00 0.00
EU03 xxxxxx
*/
The Errors should be:
/*
E r r o r s
EU02 The journal entry is unbalanced and suspense posting isn't allowed in the ledger.
EU03 The encumbrance journal entry is unbalanced and the Reserve for Encumbrance accoun
*/
You will, probably, need to do some additional adjustments to get it work with your file changing content. Regards...