Sairam Nimmagadda

Recently I encountered an Oracle Error. ORA-28113: Policy Predicate has Error After going through so much i decided to write a note about it , so that people who get stuck with this issue can have an option. So i was working on Creating a Materialized View and it is based off of a Select statement which is a Union of bunch of select statements with a bunch of tables and views. So as i try to create the Materialized view with the select statement i built , i was getting the error ORA-28113. After reading a lot of good material online and doing a lot of different things with the sql , finally found out that one of the Object (table) i am using in the SQL had policies. So went to dba_policies table and queried for the table and it had a bunch of policies enabled. So the work around could be either disabling the policies? or trying to create a custom view out of the table  and use the Custom view instead of the base table in the sql statement. Hope this helps people who are in the ...

A query which i recently developed, this might have been there already , but i still want to keep it in my blog and hope it can help someone in need. SELECT lgd.amount          gl_posted_amount       ,ctl.extended_amount ar_trx_amount ar_trx_amount       , ctl.description   inv_item_desc ,        gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8  gl_account       , gp.period_name        ,cta.TRX_NUMBER   Trx_number        ,ctl.INTERFACE_LINE_ATTRIBUTE1 Sales_Order_Number       ,ll.line_number Sales_order_line_number       ,CTT.NAME TRANSACTION_TYPE       ,FLV.MEANING RETURN_...

R12 SLA (Sub ledger Accounting) 1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL 2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process – Applies accounting rules – Loads SLA tables, GL tables – Creates detailed data per accounting rules, stores in SLA “distribution links” table 3) Below are the key tables for SLA in R12 XLA_AE_HEADERS xah XLA_AE_LINES xal XLA_TRANSACTION_ENTITIES xte XLA_DISTRIBUTION_LINKS xdl GL_IMPORT_REFERENCES gir Below are the possible joins between these XLA Tables xah.ae_header_id = xal.ae_header_id xah.application_id = xal.application_id xal.application_id = xte.application_id xte.application_id = xdl.application_id xah.entity_id = xte.entity_id xah.ae_header_id = xdl.ae_header_id xah.event_id = xdl.event_id xal.gl_sl_link_id = gir.gl_sl_link_id xal.gl...

select distinct a.form_name, a.enabled, c.user_form_name, d.application_name from fnd_form_custom_rules a,           fnd_form b,           fnd_form_tl c,           fnd_application_tl d where             enabled = ‘Y’ and       a.form_name = b.form_name and                   b.form_id     = c.form_id and      b.application_id = d.application_id order by application_name 

Bulk Collect & For All vs Cursor & For-Loop After more and more reads about BULK COLLECT and FORALL and their performance improvements      I decided to have a closer look on it by myself to see how powerful they really are. So I built a little test-case which inserts all entries from the all_object view into another table. The inserts happens on three different ways: First way is a simple cursor over the view and a insert in a loop with FETCH into local variables. This way also shows how slow the opening of the cursor itself is. The second way is a simple FOR – IN LOOP with the insert of the cursor variables. And, of course, the third way is the way with bulking the rows and inserting them with FORALL so lets see. So the other table looks like this (three columns are enough for this tests) SQL> create table temp (owner varchar2(30), name varchar2(30), type varchar2(19)); Table created. And the three diffrent procedures looks like this CREATE OR REPLACE P...

There is   no one-to-one mapping between AR, XLA and GL. The data that actually posts to the GL is the XLA table data, not AR. Depending on the AAD rules you have defined, one row that appears in AR could become 10 in XLA_DISTRIBUTION_LINKS, but when the data is posted into the GL, the accounts of the same type and ccid are merged to a single entry. For example, a transaction could have 34 gl_dist rows, but 201 rows in ar_distribution_links, but when it actually posts, consolidates to 9 rows in gl_import_references. The main link to bind information together is the gl_sl_link_id. It will show in the gl_je_lines, gl_import_refernces and xla_ae_lines table. Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction. ACTION: COMPLETE TRANSACTION : RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUS...

Oracle Reports in csv and tsv . For the output to be displayed in Excel format. I had to do a report to display the output with Japan Characters. declare      demoDocument     xoxo_ExcelDocumentType;      documentArray    xoxo_ExcelDocumentLine := xoxo_ExcelDocumentLine();      clobDocument     CLOB;        v_file        UTL_FILE.FILE_TYPE;        lc_desc       table.column%TYPE;        lc_subject        xoxo_email.subject%TYPE;        ln_batch_id       xoxo_email.batch_id%TYPE;        lc_email_address  xoxo_email.to_address%TYPE;        lc_message        xoxo_email.text_body%TYPE;        lc_appl_name      applsys.fnd_application.application_sho...