ERP Subject Area custom? field missing 26A

Rogzon

Hi!

All of a sudden one custom made analysis stopped working in our production system, it still works in our test system.

Since it is working in our test system I was able to get the issued SQL from test, as you can see in the sql there are three "custom fields" in the query.

It seems as if those three custom fields are missing from the subject area and I have absolutely no clue on how it got there and why it has stopped working.

The custom names relates to Account Code combinations and they refer to (segment3, segment4 and segment5).

A P2T was made three weeks ago and 26A was applied two weeks ago on test and 26A was applied q week ago in production.

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
0 s_0,
"General Ledger - Journals Real Time"."- Balancing Segment"."Balancing Segment Code" s_1,
"General Ledger - Journals Real Time"."- Cost Center Segment"."Cost Center Code" s_2,
"General Ledger - Journals Real Time"."- Header Details"."Journal Description" s_3,
"General Ledger - Journals Real Time"."- Header Details"."Journal Name" s_4,
"General Ledger - Journals Real Time"."- Journal Category"."Journal Category Name" s_5,
"General Ledger - Journals Real Time"."- Journal Line Details"."Line Description" s_6,
"General Ledger - Journals Real Time"."- Journal Line Details"."Line Effective Date" s_7,
"General Ledger - Journals Real Time"."- Journal Line Details"."Line Number" s_8,
"General Ledger - Journals Real Time"."- Ledger"."Ledger Name" s_9,
"General Ledger - Journals Real Time"."- Natural Account Segment"."Natural Account Segment Code" s_10,
"General Ledger - Journals Real Time"."GL_ANDAMAL_VI"."Segment Code" s_11,
"General Ledger - Journals Real Time"."GL_RESERV_VI"."Segment Code" s_12,
"General Ledger - Journals Real Time"."GL_UPPDRAG_VI"."Segment Code" s_13,
"General Ledger - Journals Real Time"."Journal Batch Details"."Journal Batch Name" s_14,
"General Ledger - Journals Real Time"."Journal Header Amounts"."Document Currency Name" s_15,
"General Ledger - Journals Real Time"."Journal Sequences"."Accounting Sequence Number" s_16,
"General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" s_17,
"General Ledger - Journals Real Time"."Time"."Fiscal Period" s_18,
"Subledger Accounting - Journals Real Time"."- General Information"."Transaction Number" s_19,
"Subledger Accounting - Journals Real Time"."Journal Details"."Journal Line Number" s_20,
CAST(NULL AS DOUBLE) s_21,
DESCRIPTOR_IDOF("General Ledger - Journals Real Time"."- Journal Category"."Journal Category Name") s_22,
DESCRIPTOR_IDOF("General Ledger - Journals Real Time"."Journal Header Amounts"."Document Currency Name") s_23,
DESCRIPTOR_IDOF("General Ledger - Journals Real Time"."Journal Source"."Journal Source Name") s_24,
SORTKEY("General Ledger - Journals Real Time"."Time"."Fiscal Period") s_25,
"General Ledger - Transactional Balances Real Time"."Entered"."Entered Beginning Balance" s_26,
"General Ledger - Transactional Balances Real Time"."Entered"."Entered Year to Date" s_27,
IFNULL("General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Credit",0) s_28,
IFNULL("General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Debit",0) s_29,
IFNULL("General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Entered Credit",0) s_30,
IFNULL("General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Entered Debit",0) s_31,
IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Credit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount CR" END,0) s_32,
IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Debit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount DR" END,0) s_33,
IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Debit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount DR" END,0)-IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Credit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount CR" END,0) s_34,
IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Entered Credit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Entered Amount CR" END,0) s_35,
IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Entered Debit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Entered Amount DR" END,0) s_36,
REPORT_SUM(IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Credit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount CR" END,0) BY ) s_37,
REPORT_SUM(IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Debit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount DR" END,0) BY ) s_38,
REPORT_SUM(IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Debit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount DR" END,0)-IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Accounted Credit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount CR" END,0) BY ) s_39,
REPORT_SUM(IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Entered Credit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Entered Amount CR" END,0) BY ) s_40,
REPORT_SUM(IFNULL(CASE WHEN "General Ledger - Journals Real Time"."Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "General Ledger - Journals Real Time"."Journal Line Amounts"."Journal Total Entered Debit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Entered Amount DR" END,0) BY ) s_41
FROM "General Ledger - Journals Real Time"
WHERE
(("Time"."Fiscal Period" = 'Apr-21') AND (IFNULL(CASE WHEN "Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "Journal Line Amounts"."Journal Total Accounted Debit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount DR" END, 0) - IFNULL(CASE WHEN "Journal Source"."Journal Source Name" in ('Manual','Spreadsheet','Manuell','Kalkylblad','HSI','INV','MATREC','MATUSE','PREL','SERVREC') THEN "Journal Line Amounts"."Journal Total Accounted Credit" ELSE "Subledger Accounting - Journals Real Time"."Journals"."Journal Line Accounted Amount CR" END, 0) <> 0))
ORDER BY 8 ASC NULLS LAST, 2 ASC NULLS LAST, 11 ASC NULLS LAST, 3 ASC NULLS LAST, 12 ASC NULLS LAST, 14 ASC NULLS LAST, 13 ASC NULLS LAST, 26 ASC NULLS LAST, 19 ASC NULLS LAST, 18 ASC NULLS LAST, 25 ASC NULLS LAST, 6 ASC NULLS LAST, 23 ASC NULLS LAST, 5 ASC NULLS LAST, 9 ASC NULLS LAST, 15 ASC NULLS LAST, 21 ASC NULLS LAST, 20 ASC NULLS LAST, 7 ASC NULLS LAST, 17 ASC NULLS LAST, 4 ASC NULLS LAST, 16 ASC NULLS LAST, 24 ASC NULLS LAST, 10 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/28535/erp-subject-area-custom-field-missing-26a

Hi,

Can you check if there is no change in the setup of GL KFF in your production system? Compare the setup of these segments in Test and Prod and then check?

Thanks.

Rogzon

Hi!

Actually I did get an answer from Oracle support, it is a time critical error.

They gave me the suggestion to run two processes:

1. Create Rules XML File for BI Extender Automation;
2. Import Oracle Fusion Data Extensions for Transactional Business Intelligence with the parameters: ERP and Financial Reporting;

I ran these two but the problem was still there.

Unfortunately Oracle had scheduled a "Exception Maintenance" so I can't really tell if it was running the processes or the restart of the system due to the scheduled maintenance.

Anyway the problem is solved, thanks for your time!

Regards,

/Roger

Rogzon

Oh, you were probably right suggesting KFF but the whole solution is described in: Doc ID: KB87540

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/76188#Comment_76188

Thanks for the update, Roger.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/76190#Comment_76190

Thanks for the reference but it seems the issue is still there in 26A while the knowledge base article talks about 22a/22b only.

Thanks.

Rogzon

Yep, I don't think they have fixed the bug, according to support it is a very rare condition.

I would say that the solution in the document is still valid.