This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Dec 5, 2012 5:15 PM by stopuz RSS

Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12

stopuz Newbie
Currently Being Moderated
Hi,

We had a custom procedure utilizing ce_200_transactions_v view and CE_AUTO_BANK_CLEAR.RECONCILE_TRX API to clear check payments based on a file we receive from the bank.

After upgrade, the above query is not returning any rows (even without any where clause). I know it is something about initialazing global context, but I could not get it workking.

I tried the following in SQL Developer in separete sessions:
BEGIN
   mo_global.set_policy_context('S', 0);
END;

SELECT count(*) FROM CE_200_TRANSACTIONS_V T 

-------------------

BEGIN
   mo_global.set_policy_context('M', 0);
END;

SELECT count(*) FROM CE_200_TRANSACTIONS_V T 
There are available transactions to clear in the system that I confirmed in the forms interface, but no rows returned.

MO: Security Profile is set.

Can you please advise?

Thanks,
Sinan
  • 1. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Please see these docs/links.

    How to view org-specific data in a MOAC environment [ID 415860.1]
    Oracle Applications Multiple Organizations Access Control for Custom Code [ID 420787.1]
    How to set the Organization Context in R12? [ID 437119.1]
    SQL Queries and Multi-Org Architecture in Release 12 [ID 462383.1]
    How To Retrieve Rows From Table Or Synonym For An ORG_ID In E-Business Suite 12 [ID 787677.1]

    https://forums.oracle.com/forums/search.jspa?threadID=&q=Organization+AND+Context+AND+R12&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

    Thanks,
    Hussein
  • 2. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    stopuz Newbie
    Currently Being Moderated
    Hussein,

    Please see below. I am able to select from a synonym for a table, but not from the view:
    SQL> disc
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> conn apps/apps@prod
    Connected.
    SQL> 
    SQL> 
    SQL> 
    SQL> exec mo_global.set_policy_context('S', 0);
    
    PL/SQL procedure successfully completed.
    
    SQL> select count(*) from po_headers;
    
      COUNT(*)
    ----------
         14425
    
    SQL> select count(*) from CE_200_TRANSACTIONS_V;
    
      COUNT(*)
    ----------
          0
    Thanks,
    Sinan
  • 3. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    stopuz Newbie
    Currently Being Moderated
    Also, I am a bit confused about this statement in "Oracle Applications Multiple Organizations Access Control for Custom Code [ID 420787.1]" :

    To use the single operating unit mode, you must set the value for the "*Initialization SQL Statement – Custom profile" to "mo_global.init('S',null);*". This initializes the operating unit context based on the "MO: Operating Unit" profile option and the "MO: Security Profile" profile option must not be set.

    Our setup is really simple, we have just one operating unit. Is it possible to avoid all this complicated MO thing by setting SQL Statement - ... profile option value as described above? What are the impacts? Would it affect running other concurrent programs?

    Thanks,
    Sinan
  • 4. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    stopuz Newbie
    Currently Being Moderated
    I have some more information on this:

    I can view rows from all of the underlying views/tables of CE_200_TRANSACTIONS_V view with one exception "CE_SECURITY_PROFILES_GT". My profile options are as follows:

    MO: Default Operating Unit: Setup Business Group
    MO: Distributed Environment: No
    MO: Operating Unit: Setup Business Group
    MO: Security Profile:
    MO: Set Client_Info for Debugging:
    Initialization SQL Statement - Custom:

    I execute "BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S', null); END;" in SQL Developer first and then select from these tables/views to test.

    Any comments appreciated.

    Thanks,
    Sinan
  • 5. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    This MOS Doc may be of help

    After upgrade to R12, no data exists in table CE_SECURITY_PROFILES_GT [ID 1235213.1]

    HTH
    Srini
  • 6. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Sinan,

    Please see these docs.

    Can't Select From Some Cash Management Views [ID 1394907.1]
    Unable To Assign Operating Unit To Payee [ID 729407.1]

    Thanks,
    Hussein
  • 7. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    stopuz Newbie
    Currently Being Moderated
    Srini,

    That note helped. I am able to view the data in SQL Developer now, but the issue continues as we had sat the program as a concurrent program. Somehow the security context is getting overwritten when we run it from there. I tried setting the Operating unit mode for this program to "Single", "Multi", and "None" but it did not make any difference.

    Can you please advise? Where I may be making a mistake?

    Thanks,
    Sinan

    Edited by: stopuz on Dec 3, 2012 4:42 PM
  • 8. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    stopuz wrote:
    Hussein,

    That note helped. I am able to view the data in SQL Developer now, but the issue continues as we had sat the program as a concurrent program. Somehow the security context is getting overwritten when we run it from there. I tried setting the Operating unit mode for this program to "Single", "Multi",
    and "None" but it did not make any difference.

    Can you please advise? Where I may be making a mistake?

    Thanks,
    Sinan
    From what responsibility you run this concurrent program?

    Do you have any of those profile options set at the application/responsibility level?

    Please enable trace/debug and check the contents of FND_LOG_MESSAGES table to find out where the security context is getting overwritten -- How Can Trace and Debug Be Turned On For A Concurrent Request? [ID 759389.1]

    Thanks,
    Hussein
  • 9. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    stopuz Newbie
    Currently Being Moderated
    Hussein,

    It is run under Cash Management Supervisor. Those profile options are not defined at Responsibility level.

    Thanks,
    Sinan
  • 10. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    stopuz wrote:
    Hussein,

    It is run under Cash Management Supervisor. Those profile options are not defined at Responsibility level.

    Thanks,
    Sinan
    What about the application level?

    Did you enable debug as requested above?

    Thanks,
    Hussein
  • 11. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    stopuz Newbie
    Currently Being Moderated
    Hussein,

    It is not set at application level either. I followed the document to enabled the debug options as described in there, but the concurrent request did not generate any logs. Am I supposed to write the debug messages myself within the custom code?

    Thanks,
    Sinan
  • 12. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Sinan,
    It is not set at application level either. I followed the document to enabled the debug options as described in there, but the concurrent request did not generate any logs. Am I supposed to write the debug messages myself within the custom code?
    If you enable debug/trace as per the docs and submit the request then you should get those debug messages in the table. You do not need to write any debug messages in your custom code.

    Thanks,
    Hussein
  • 13. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    stopuz Newbie
    Currently Being Moderated
    Hussein,

    At the top of the document it says:

    Attention: If using Release 12 - the following patch is required for the trace to work properly using the steps below. Patch 8743459
    Patch 8743459: 12.1.1: 'SQL TRACE' IN 'DEBUG OPTIONS' IS UNLIKELY WORKING.

    We are on R12.1.3, but when search for this patch in OAM, it does not seem to be applied. Should I apply the patch? But, we are just after debug info not trace, I though it is irrelevant.

    I deleted everything from fnd_log_messages table and enabled the profile option for the user. No one else is connecting to the system right now other than this account. There are no log entries in the table. Also, I tried to do the same thing for a built in program (a report which generates output), but it did not generate any log entries either.

    Thanks,
    Sinan
  • 14. Re: Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Attention: If using Release 12 - the following patch is required for the trace to work properly using the steps below. Patch 8743459
    Patch 8743459: 12.1.1: 'SQL TRACE' IN 'DEBUG OPTIONS' IS UNLIKELY WORKING.

    We are on R12.1.3, but when search for this patch in OAM, it does not seem to be applied. Should I apply the patch? But, we are just after debug info not trace, I though it is irrelevant.
    Please apply the patch.
    I deleted everything from fnd_log_messages table and enabled the profile option for the user. No one else is connecting to the system right now other than this account. There are no log entries in the table. Also, I tried to do the same thing for a built in program (a report which generates output), but it did not generate any log entries either.
    Please see profile options at the user level as per (How To Trace a Concurrent Request And Generate TKPROF File [ID 453527.1] -- 4. Debug based on fnd_log_messages), logout and login again for the table to get populated.

    Thanks,
    Hussein
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points