Export / Import the Reports from one envirornment to another

I am sure, in Answers/Dashboards, most of OBIEE  developers may  face a common issue of copying all their objects from one folder to another folder, one environment to another environment (in the developing stage) etc.
If you got the access to the system in which the Catalog is stored then, these actions are very easy. But how to do it in Dashboard catalog.

OBIEE 11g came up with a solution for this problem. Archive and Unarchive. In Archive you can archive (export) a folder with diff objects to the local file system. The archived file can be Unarchived (imported) into another folder. So simple… isn’t it?

Archiving

I am implementing Archive and Unarchive process using two instances one is ‘‘eld15’’ and second one is ‘‘obiee2’’ instances. Here ‘‘eld15’’ instances ‘Shared Folder’ having ‘Test_eld15’ reports folder. Using Archive/Unarchive process ‘Test_eld15’ folder will move to ‘‘obiee2’’ instances under ‘Shared Folder’.



Archive and Unarchive options are available under ‘Tasks’ option.



In Archive you can archive (export) a folder with diff objects to the local file system. Select the ‘Test_eld15’ folder and now, click on the Archive link, which will open a dialog box with some options for the archiving. Check the two boxes.

Keep Permissions: Folder level permissions also keep at the time of archiving the folder
Keep Timestamp: Kept the database timestamp


After clicking OK button, a dialog box will appear asking where the catalog to be saved. Select the desired location and save the file.

This archive file saved into a Downloads.


Unarchiving

 Select the ‘Shared folder’ into which the archived (exported) filed needs to be Unarchived (imported).

We are going to unarchive the ‘Test_eld15’ file will be created in the shared folder. Then click on the Unarchive link in ‘obiee2’ instances.

NOTE:If you are not able to Unarchive in Shared Folder then create a folder in Shared folder and Unarchive there.



The Unarchive dialog box will appear which allows us to select the file which needs to be Unarchived into the currently selected folder ( in our example, its ‘Test_eld15’). Click OK.



In this Unarchive window having three option. Each one having multiple options.

Archive file:  Browse the appropriate archive file name (‘Test_eld15’).

Replace
The Preferences dialog contains the following options in the Replace area:

• Force — Pastes all files, overwriting even those that have the read-only attribute set.
• All — Pastes all possible files, overwriting only those that do not have the read-only attribute set. (Default)
• Old — Pastes all possible files, but does not overwrite any existing files unless they are older than the source.
• None — Pastes all possible files, but does not overwrite any existing files.

 ACL
The Preferences dialog contains the following options in the Paste ACL area:

• Inherit — inherits the object’s permissions (ACL) from its new parent folder. (Default)
• Preserve — Preserves the object’s permissions (ACL) as it was in the original, mapping accounts as necessary.
• Create —   Preserves the object’s permissions (ACL) as it was in the original, creating and mapping accounts as necessary, depending on the mode and type of owner, as described in the following list:

Once it is successful click on “Ok” and a ‘Test_eld15’ file will be created in the shared folder shown.




Hope it helps.

Configuring the Data Source in BI PUBLISHER

Types of Supported Data Sources

Oracle BI Publisher supports various types of Data Sources such as JDBC data sources (Oracle Database, and other Databases), XML Files, BI EE Reports (Answers), JNDI data sources, OLAP data sources, Fusion Application Data sources, Web Services, HTTP data sources, and Discoverer.
Before you create a data model based on these data sources, you need to configure a connection to these data sources first.

Defining a JDBC Connection

1.	To define a JDBC connection click the Administration link found on the right side of the BI Publisher page. This displays the BI Publisher Administration page as shown below (Observe the Data Sources section in the screen below):
2.	Click the JDBC Connection link found under the Data Sources section in the Administration page.
3.	This displays the Data Sources page. In the JDBC section, click Add Data Source to create a JDBC connection to your database.
4.	In the Add Data Source page that is displayed, enter the details as given below - Data Source Name - OE Driver Type - Select a driver type to suit your Database (for example, you can select Oracle 10g or Oracle 11g to suit your Database). Database Driver Class - oracle.jdbc.driver.OracleDriver (Define a driver class to suit your Database) Connection String - Provide the database connection details. For example hostname:port:sid. User name - OE (Database user name) Password - OE (Database user password) Do not click Apply or Cancel after defining the above details. Click Test Connection.
5 .	If the connection to the database is established, a confirmation message is displayed indicating the success. ( As shown in the screen below). Then click Apply.
6 .	You can see this newly defined connection (OE) in the list of JDBC Data Sources.

How to Import from Excel to Oracle with SQL Developer

Step 0: The Empty Oracle Table and your Excel File

You have an Oracle table and you have one or more Excel files.

Data here but not there!?!

You do know how to view multiple objects at once in SQL Developer, right?

Step 1: Mouse-right click – Import Data

Yes, it's that easy.

Step 2: Select your input (XLS) file

Yes, we also support XLSX, CSV, etc

Step 3: Verify the data being read

Mind the headers!

Does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. The default options take care of this. You can also choose to preview more than 100 rows.
Here’s what it looks like if you uncheck the ‘Header’ box

Sometimes you may want the column headers as a row in the table?

Sometimes your Excel file has multiple headers, or you may need to only import a certain subset of the spreadsheet. Use the ‘Skip Rows’ option to get the right data.

Step 4: Create a script or import automatically

Script or do it for me?

For this exercise the ‘Insert’ method will be used.

Step 5:

Choose the Excel columns to be imported

You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.

Step 6:

If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.

Step 7: Verify your settings

Hit the ‘verify’ button. Fix any mistakes.

Ruh roh raggy!

SQL Developer is telling you it doesn’t know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.
So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.

You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.
After correcting this, go back to the Verification screen and see if that fixes the problem.

Step 8:

Everything looks right!

Click on the ‘Finish’ button.

Step 9: Verify the import look at your new table data

The data is there and the dates look right!

Note the ‘Log’ panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!

Query to find Legal Entity, Organization, Company Code

SELECT

       xep.legal_entity_id        "Legal Entity ID",

       xep.name                   "Legal Entity",

       hr_outl.name               "Organization Name",

       hr_outl.organization_id    "Organization ID",

       hr_loc.location_id         "Location ID",

       hr_loc.country             "Country Code",

       hr_loc.location_code       "Location Code",

       glev.flex_segment_value    "Company Code"

  FROM

       xle_entity_profiles            xep,

       xle_registrations              reg,

       --

       hr_operating_units             hou,

       -- hr_all_organization_units      hr_ou,

       hr_all_organization_units_tl   hr_outl,

       hr_locations_all               hr_loc,

       --

       gl_legal_entities_bsvs         glev

 WHERE

       1=1

   AND xep.transacting_entity_flag   =  'Y'

   AND xep.legal_entity_id           =  reg.source_id

   AND reg.source_table              =  'XLE_ENTITY_PROFILES'

   AND reg.identifying_flag          =  'Y'

   AND xep.legal_entity_id           =  hou.default_legal_context_id

   AND reg.location_id               =  hr_loc.location_id

   AND xep.legal_entity_id           =  glev.legal_entity_id

   --

   -- AND hr_ou.organization_id         =  hou.business_group_id

   AND hr_outl.organization_id       =  hou.organization_id

   AND hr_outl.language='US'

 ORDER BY hr_outl.name

R12 Query to find supplier payment method

In R12 supplier payment method information no more exists in the ap_supplier_sites_all table.
Query to find the supplier payment method


SELECT   ieppm.payment_method_code
       FROM ap_supplier_sites_all assa,
            ap_suppliers sup,
            iby_external_payees_all iepa,
            iby_ext_party_pmt_mthds ieppm
      WHERE sup.vendor_id = assa.vendor_id
        AND assa.pay_site_flag = 'Y'
        AND assa.vendor_site_id = iepa.supplier_site_id
        AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
        AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
            )
        AND assa.vendor_site_id = :p_vendor_site_id
        AND ieppm.primary_flag = 'Y'
   ORDER BY sup.vendor_name, assa.vendor_site_code

Clearing the cache using the Issue SQL page

Log into OBIEE Analytics using an ID that has the “Administration” link privilege.
http://server:9704/analytics
Click on the “Administration” link located at the top right of the page.

Click on the “Issue SQL” link at the bottom left of the page.

In the Issue SQL window, enter the command:  “call SAPurgeAllCache()” (without the quotes)
Click the “Issue SQL” button to execute the command.

You should see a message indicating that the command was executed successfully (assuming you have caching turned on).
Your cache directory located at …
[FMW_HOME]/instances/instance1/bifoundation/OracleBIServerComponent/ coreapplication_obis1/cache
… should now be empty.

EBS query to get concurrent program details

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader','Q','SQL*Plus', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where exe.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND appl.language='US'
AND prog.user_concurrent_program_name ='RIDO AR to Dealer Database Interface';