reg materialized views

[satish]

Dear Erman,

Our developers were creating materialized views by referring directly to the table name like gl.gl_je_lines,gl.gl_je_headers.

1)The tables are non-editioned and MVIEW is also non-editioned,will this create any issues later?
2)What is suggested is to use evaluate using current edition clause while creating mview and stop referencing the table names directly in mview code and instead refer to synonyms

Thanks for the support

[satish]

MView created in apps schema and the code is as below

CREATE MATERIALIZED VIEW  test  as
(SELECT   gljl.entered_dr je_lines_entered_dr,
         gljl.entered_cr je_lines_entered_cr,
         gljl.accounted_dr je_lines_accounted_dr,
         apia.gl_date gl_date
    FROM gl.gl_je_batches gljb,
         gl.gl_je_headers gljh,-------------------------------->referring to schemaname.tablename
         gl.gl_je_lines gljl,
        gl.gl_code_combinations glcc,
       ap.ap_invoices_all apia,
        xla.xla_transaction_entities xlate,
       xla.xla_events xlae,
        gl.gl_import_references gir
   WHERE 1 = 1
     AND gljl.code_combination_id = glcc.code_combination_id
     AND gljb.je_batch_id = gljh.je_batch_id
     AND gljh.je_header_id = gljl.je_header_id
     AND xlate.source_id_int_1 = apia.invoice_id
     AND xlae.application_id = xlate.application_id
     AND gir.gl_sl_link_id = xlal.gl_sl_link_id
     AND gljh.je_source = 'Payables'
     AND gljh.je_category = 'Purchase Invoices'
     AND (gljl.entered_dr > 0 OR gljl.entered_cr > 0))

will this create any problems in future?

[ErmanArslansOracleBlog]

Read :

Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)
"Section 1.4.3.5: Materialized Views"

[satish]

Dear Erman,

Thanks for the update.

We are on 12c database using 12.1.0.2.As suggested in note id,we have created using evaluate using current edition as below

as apps user

SQL> CREATE MATERIALIZED VIEW  mviewtest1 EVALUATE USING CURRENT EDITION  AS
(SELECT   gljl.entered_dr je_lines_entered_dr,
  2    3           gljl.entered_cr je_lines_entered_cr,
  4           gljl.accounted_dr je_lines_accounted_dr,
  5           gljl.accounted_cr je_lines_accounted_cr, glcc.segment1 "UNIT CODE",
  6           glcc.segment4 "ACCOUNT CODE", apia.invoice_date transaction_date,
  7           apia.gl_date gl_date
    FROM gl_je_batches gljb,
  8    9           gl_je_headers gljh,
 10           gl_je_lines gljl,
 11          gl_code_combinations glcc,
 12         ap_invoices_all apia,
 13          xla_transaction_entities xlate,
 14         xla_events xlae,
 15        xla_ae_headers xlah,
 16         xla_ae_lines xlal,
 17        gl_import_references gir
 18     WHERE 1 = 1
 19       AND gljl.code_combination_id = glcc.code_combination_id
     AND gljb.je_batch_id = gljh.je_batch_id
     AND gljh.je_header_id = gljl.je_header_id
 20   21   22       AND xlate.source_id_int_1 = apia.invoice_id
 23       AND xlae.application_id = xlate.application_id
 24       AND xlae.entity_id = xlate.entity_id
 25       AND xlah.application_id = xlae.application_id
     AND xlah.event_id = xlae.event_id
 26   27       AND xlal.application_id = xlah.application_id
 28       AND xlal.ae_header_id = xlah.ae_header_id
     AND xlal.code_combination_id = glcc.code_combination_id
     AND gljl.code_combination_id = glcc.code_combination_id
 29   30   31       AND gljl.je_header_id = gir.je_header_id
 32       AND gljl.je_line_num = gir.je_line_num
     AND gir.gl_sl_link_table = xlal.gl_sl_link_table
 33   34       AND gir.gl_sl_link_id = xlal.gl_sl_link_id
     AND gljh.je_source = 'Payables'
 35   36       AND gljh.je_category = 'Purchase Invoices'
 37       AND (gljl.entered_dr > 0 OR gljl.entered_cr > 0));

Materialized view created.

SQL> select object_name,object_type,status from dba_objects where object_name=upper('mviewtest1');

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE             STATUS
----------------------- -------
MVIEWTEST1
TABLE                   VALID

MVIEWTEST1
MATERIALIZED VIEW       VALID

but when we select from MVIEWTEST1 table,nothing is returned.could you please correct if we are missing something


SQL> select * from MVIEWTEST1;

no rows selected

SQL>
SQL> desc MVIEWTEST1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JE_LINES_ENTERED_DR                                NUMBER
 JE_LINES_ENTERED_CR                                NUMBER
 JE_LINES_ACCOUNTED_DR                              NUMBER
 JE_LINES_ACCOUNTED_CR                              NUMBER
 UNIT CODE                                          VARCHAR2(25)
 ACCOUNT CODE                                       VARCHAR2(25)
 TRANSACTION_DATE                                   DATE
 GL_DATE                                   NOT NULL DATE

SQL>

[satish]

Is this process correct which we have posted in previous post?

[ErmanArslansOracleBlog]

1)Does the MW query itself return rows?
2)Did you refresh the MW?

[satish]

Thanks for the updates erman.

SQL> CREATE MATERIALIZED VIEW  mviewtest1 EVALUATE USING CURRENT EDITION  AS
(SELECT   gljl.entered_dr je_lines_entered_dr,
  2    3           gljl.entered_cr je_lines_entered_cr,
  4           gljl.accounted_dr je_lines_accounted_dr,
  5           gljl.accounted_cr je_lines_accounted_cr, glcc.segment1 "UNIT CODE",
  6           glcc.segment4 "ACCOUNT CODE", apia.invoice_date transaction_date,
  7           apia.gl_date gl_date
    FROM gl_je_batches gljb,
  8    9           gl_je_headers gljh,
 10           gl_je_lines gljl,
 11          gl_code_combinations glcc,
 12         ap_invoices_all apia,
 13          xla_transaction_entities xlate,
 14         xla_events xlae,
 15        xla_ae_headers xlah,
 16         xla_ae_lines xlal,
 17        gl_import_references gir
 18     WHERE 1 = 1
 19       AND gljl.code_combination_id = glcc.code_combination_id
     AND gljb.je_batch_id = gljh.je_batch_id
     AND gljh.je_header_id = gljl.je_header_id
 20   21   22       AND xlate.source_id_int_1 = apia.invoice_id
 23       AND xlae.application_id = xlate.application_id
 24       AND xlae.entity_id = xlate.entity_id
 25       AND xlah.application_id = xlae.application_id
     AND xlah.event_id = xlae.event_id
 26   27       AND xlal.application_id = xlah.application_id
 28       AND xlal.ae_header_id = xlah.ae_header_id
     AND xlal.code_combination_id = glcc.code_combination_id
     AND gljl.code_combination_id = glcc.code_combination_id
 29   30   31       AND gljl.je_header_id = gir.je_header_id
 32       AND gljl.je_line_num = gir.je_line_num
     AND gir.gl_sl_link_table = xlal.gl_sl_link_table
 33   34       AND gir.gl_sl_link_id = xlal.gl_sl_link_id
     AND gljh.je_source = 'Payables'
 35   36       AND gljh.je_category = 'Purchase Invoices'
 37       AND (gljl.entered_dr > 0 OR gljl.entered_cr > 0));


It seems like there was a problem with this table xla_transaction_entities(partitioned)
if we give xla_transaction_entities-does not returning any rows
if we give xla.xla_transaction_entities-returning rows

1)But just wanted to know the process we followed to create mview(EVALUATE USING CURRENT EDITION) is right or wrong.
OR
2)do we need to create a logical view first and then create a materialized view from it?

[ErmanArslansOracleBlog]

The process you need to follow is documented in "Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)" - > "Section 1.4.3.5: Materialized Views"

Yes. You need to create the logical view first.

As for Xla_transaction_entities ;
Xla_transaction_entities  is security enabled table, and hence data wouldn't be available until correct settings are used. So that's normal.. You should set context (org_id) for querying it without a schema. By using the schema owner, you do not have to set the org_id.

[Satish]

Great.Thanks for all the support.

But I think there would be problem if we use schemaname.tablename in future if there is any changes to the table.pls correct me if i am wrong

[satish]

can we refer schema.tablename directly in our code in r12.2?please correct us if we are wrong

[ErmanArslansOracleBlog]

I already clearly stated that;

Xla_transaction_entities  is security enabled table, and hence data wouldn't be available until correct settings are used. So that's normal.. You should set context (org_id) for querying it without a schema. By using the schema owner, you do not have to set the org_id.

[Satish]

Thanks erman.in our environment we didn't set orgid and they don't have ideas to set it. Now we have only one option to use schemaname.tablename in our code.

I just want to make sure it will not create any problems in future if we go with schemaname.tablename

Thanks for your understanding erman.

[ErmanArslansOracleBlog]

I sent you my comment for the present time.
Only Oracle can tell about the future :)
This is Oracle Applications...

[satish]

Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)

under Section 1.4.3.5: Materialized Views

In Oracle Database Release 11g Release 2 (11.2)
-----------------------------------------------

materialized view query is stored in an ordinary view, called the Logical View. The materialized view is then generated from the logical view

In Oracle Database 12c Release 1 (12.1) and later
-------------------------------------------------

it is possible to create a native materialized view definition that references editioned objects by using the "EVALUATE USING CURRENT EDITION" clause in the create statement.

As we are on 12.1.02,we will create a materialized view using EVALUATE USING CURRENT EDITION instead of creating logic view and then generating mview from it

Thanks again for the support

[satish]

As one of my colleague said if we refer schemaname.tablename in the code while creating mview,if any patch does any changes to that table,then there is a chance of mview quering obsolete columns.

Is it true in this case?

Thanks erman for the support you provide

[satish]

As one of my colleague said if we refer schemaname.tablename in the code while creating mview,if any patch does any changes to that table,then there is a chance of mview quering obsolete columns.

Is it true in this case?

Thanks erman for the support you provide

[ErmanArslansOracleBlog]

It depends on the change..
You should always check your customization after patching.. (after patching the relevant modules)
Specially, if your customization depends on a standard object.

as I already mentioned, if you have any concerns, you can also set the context (org_id) in your custom code, just before calling that object and use that object withouth specifying the schema name..
As for setting the context, you can ask it to your EBS developer.. They should know..

[satish]

thanks erman for all the support