FULL TEXT CATALOG SEARCH IN OBIEE

The full-text catalog search provides a mechanism for searching for objects in the Oracle BI Presentation Catalog that is similar to a full-text search engine. This full-text search provides more advanced options than the basic catalog search that Presentation Services provides, as described in the following list:

• Basic Catalog Search — Users can search for an object by its exact name, which is similar to using a Find dialog in many products.
  
• Full-text Catalog Search — Users can search for objects by various attributes, such as name, description, author, and the names and values of columns of data that the object references. Users obtain search results that correspond to the objects that they have access to open.
  

Below are the useful urls for Full Text Catalog Search in OBIEE

http://docs.oracle.com/cd/E25054_01/bi.1111/e10541/prescatadmin.htm#CACHJIEG

http://bidirect.blogspot.com/2013/12/obiee-11g-full-text-catalog-search-for.html

 

SOURCE TABLES FOR W_SALES_CYCLE_LINE_F

W_SALES_CYCLE_LINE_F.

W_SALES_CYCLE_LINE_F is loaded from W_SALES_PICKLINE_F,W_SALES_ORDER_LINE_F and  W_SALES_SCHEDULE_LINE_F.

W_SALES_PICKLINE_F has below sources :

WSH_DELIVERY_DETAILS
WSH_NEW_DELIVERIES
WSH_DELIVERY_ASSIGNMENTS
WSH_CARRIER_SERVICES
WSH_CARRIER_VEHICLE_TYPES
WSH_CARRIERS
OE_ORDER_LINES_ALL
OE_ORDER_HEADERS_ALL

W_SALES_ORDER_LINE_F has below sources :
 
OE_ORDER_LINES_ALL
OE_ORDER_HEADERS_ALL
GL_LEDGERS
OE_AGREEMENTS_B
HR_ORGANIZATION_INFORMATION
RA_SALESREPS_ALL

W_SALES_SCHEDULE_LINE_F is loaded from W_SALES_ORDER_LINE_F

EBS QUERY TO FIND RESOURCE AND ITS ASSOCIATED ALTERNATE RESOURCES

Below is the query to find the resources and its associated alternate resource in manufacturing  module.

SELECT A.RESOURCES,ALTERNATE_RESOURCE.ALTERNATE_RESOURCE,A.RESOURCE_DESC,A.RESOURCE_CLASS,
B.MIN_CAPACITY,B.MAX_CAPACITY,
B.IDEAL_CAPACITY,B.CAPACITY_UM,B.GROUP_RESOURCE,MP.ORGANIZATION_CODE
FROM
CR_RSRC_MST_B A,CR_RSRC_DTL B,MTL_PARAMETERS MP,APPS.CR_ARES_MST ALTERNATE_RESOURCE
WHERE A.RESOURCES=B.RESOURCES AND B.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND A.RESOURCES=ALTERNATE_RESOURCE.PRIMARY_RESOURCE
ORDER BY 1 DESC;

HOW TO CREATE AND DROP DATABASE LINK

Below is the syntax for creating and dropping DB Link in Oracle

Enter the DB Link Name, username, Password and Schema Name in the below syntax

Note: Schema Name should be in Quotes and you should have sufficient privileges to create and drop DB Link.

CREATE DATABASE LINK
DBLINKNAME 
CONNECT TO USER
IDENTIFIED BY PASSWORD
USING 'SCHEMA NAME';



DROP DATABASE LINK DBLINKNAME ;

HOW TO LOGIN TO OBIEE BYPASSING LOGIN PAGE

Please use the below url to bypass EBS security to Login into OBIEE.

Enter the Username and Password after NQSUser and NQPassword in URL below.

http://localhost:9704/analytics/saw.dll?Dashboard&NQUser=Username&NQPassword=Password

LIST OF BIAPPS MODULES

Below is the list of Modules available in BI APPS

1. Oracle Financial Analytics
2. Oracle Procurement and Spend Analytics
3. Oracle Supply Chain and Order Management Analytics
4. Oracle Project Analytics
5. Oracle Human Resources Analytics
6. Oracle Sales Analytics
7. Oracle Price Analytics
8. Oracle Marketing Analytics
9. Oracle Loyalty Analytics
10. Oracle Service Analytics
11. Oracle Call Center Telephony Analytics

ORACLE SQL SCRIPT TO GENERATE ALTER SCRIPT TO MODIFY THE DATATYPES OF EXISTING COLUMNS

In the below script CHAR_USED stores the value 'B','C' which means BYTE OR CHAR.
we can modify the script as per our need.

SELECT 'ALTER TABLE '||TABLE_NAME||' MODIFY ('||COLUMN_NAME||' '||DATA_TYPE||'('||CHAR_LENGTH||' CHAR));' AL, TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHAR_LENGTH,CHAR_USED FROM ALL_TAB_COLS WHERE TABLE_NAME LIKE '%'-- ENTER TABLE NAME
AND DATA_TYPE IN('VARCHAR2','CHAR') AND CHAR_USED='B';

The output would be like below

ALTER TABLE WC__SETUP_DS MODIFY (TYPE VARCHAR2(50 CHAR));
ALTER TABLE WC__SETUP_DS MODIFY (NAME VARCHAR2(40 CHAR));

BIAPPPS W_AP_XACT_F,W_AR_XACT_F,W_GL_OTHER_F LINEAGE

BIAPPS W_GL_ACCOUNT_D DATA LINEAGE

HOW TO UPDATE A RECORD IN TARGET TABLE WITHOUT USING UPDATE STRATEGY

A target table can be updated without using 'Update Strategy'. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as "Update as Update" and check the "Update" check-box.

Let's assume we have a target table "Customer" with fields as "Customer ID", "Customer Name" and "Customer Address". Suppose we want to update "Customer Address" without an Update Strategy. Then we have to define "Customer ID" as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.