1 2 Previous Next 17 Replies Latest reply: Dec 5, 2012 7:15 PM by stopuz RSS

    Cannot select from CE_200_TRANSACTIONS_V view after upgrade to R12

    stopuz
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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