Downtime Query for Oracle Production Scheduling

SELECT org.organization_code organization_code,
    crd.resources resources,
    crm.resource_desc resource_description,
    TO_CHAR(grum.from_date, 'DD-MON-YYYY HH24:MI:SS') start_time,
    TO_CHAR(grum.to_date, 'DD-MON-YYYY HH24:MI:SS') stop_time,
    floor(((grum.to_date-grum.from_date)*24*60*60)/3600) || ':' || ROUND((((grum.to_date-grum.from_date)*24*60*60) -     floor(((grum.to_date-grum .from_date)*24*60*60)/3600)*3600)/60) || ':' || ROUND((((grum.to_date-grum.from_date)    *24*60*60) - floor(((grum.to_date-grum .from_date)*24*60*60)/3600)*3600 - (ROUND((((grum.to_date-    grum.from_date)*24*60* 60) - floor(((grum.to_date-grum.from_date)*24*60*60)/3600)*3600)/60)*60) ))
    elapsed_time,
    grum.reason_code reason_code,
    mtr.description reason_description
FROM     org_organization_definitions org,
    cr_rsrc_dtl crd,
    CR_RSRC_MST crm,
    gmp_rsrc_unavail_man grum,
    MTL_TRANSACTION_REASONS mtr
WHERE org.organization_id = crd.organization_id
    --AND org.organization_code = NVL(:ORG_CODE,org.organization_code)
    AND crd.resource_id = grum.resource_id
    AND crd.resources = crm.resources
    --AND crd.resources = NVL(:RESOURCES,crd.resources)
    AND grum.reason_code = mtr.reason_name(+)
    --AND grum.reason_code = NVL(:REASON_CODE,grum.reason_code)
    AND (TO_CHAR(grum.from_date,'YYYY/MM/DD') >= '2012/02/22'
    AND TO_CHAR(grum.to_date,'YYYY/MM/DD') <= '2012/02/22')
     -- and (grum.to_date >= '0-jan-12' and grum.to_date <'04-jan-12')
    -- AND to_char(nvl(grum.from_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS') between nv l    (:START_FROM_DATE,to_char(nvl(grum.from_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS'))
    AND nvl(:START_TO_DATE,to_char(nvl(grum.from_date,SYSDATE),'YYYY/MM/DD HH24:MI: SS'))
    -- AND to_char(nvl(grum.to_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS')
    between nvl (:STOP_FROM_DATE,to_char(nvl(grum.to_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS'))
    AND nvl(:STOP_TO_DATE,to_char(nvl(grum.to_date,SYSDATE),'YYYY/MM/DD HH24:MI:SS'))
ORDER BY org.organization_code, crd.resources, grum.from_date

Ship To and Bill To Address in Order Management

select     ooha.order_number,
    msi.segment1,
    hcsu_ship.site_use_id,
    hcasa_ship.cust_acct_site_id,
    hps.party_site_id,
    hp.party_name "Customer Name",
    sota.name "Order Type",
    CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(hl_ship.address1,hl_ship.address2),h         l_ship.address3),hl_ship.address4),hl_ship.state),hl_ship.country) "Ship To Address" ,
    round(oola.ordered_quantity2) "Ordered Quantity"

from oe_order_lines_all oola
    inner join OE_ORDER_HEADERS_ALL ooha on oola.header_id= ooha.header_id
--inner join oe_order_lines_all oola_shipto oola_shipto.ship_to_org_id = hcsu_ship.site_use_id
inner join so_order_types_all sota on ooha.order_type_id = sota.order_type_id
inner join HZ_CUST_ACCOUNTS hca on hca.cust_account_id = ooha.sold_to_org_id
inner join HZ_PARTIES hp on hp.party_id = hca.party_id
inner join mtl_system_items_b msi on oola.inventory_item_id = msi.inventory_item_id
    AND oola.ship_from_org_id = msi.organization_id
 --inner join HZ_CUST_SITE_USES_ALL hcsu_ship on oola.ship_to_org_id = hcsu_ship.site_use_id
inner join HZ_CUST_SITE_USES_ALL hcsu_ship on oola.invoice_to_org_id = hcsu_ship.site_use_id
inner join HZ_CUST_ACCT_SITES_ALL hcasa_ship on hcsu_ship.cust_acct_s ite_id = hcasa_ship.cust_acct_site_id i
nner join HZ_PARTY_SITES hps on hcasa_ship.party_site_id = hps.party _site_id
inner join HZ_PARTY_SITE_USES hpsu_ship on hpsu_ship.party_site_id = hp s.party_site_id
inner join HZ_LOCATIONS hl_ship on hps.location_id = hl_ship.location _id
where ooha.order_number = :ORDER_NUMBER

Convert columns into rows and vice-versa in Excel

We can chage the rows to columns and vice-versa very easily.

Follow the steps and get the result.

Step 1: Copy what ever you want to change.

Step 2 : Right click where you want to paste and select Paste Special


Step 3: Select Transpose option and click ok.


Thats it you're done

Dynamically Change The Column Names in OBIEE 11g

We can change the column names dynamically using presentation variables
see how it works.

Step1: Create a Dashboard Prompt for the presentation variable.

Step 2: Place the prompt in the ashboard page and hide it.

Step 3: Change the column name as @{Presentation Variable} {Deafult value}

Note : 1.Prompt should be taken on any different column other than the columns in the page.

2. If you want n number of columns,create n number of prompts and hide them.

Hide a Section in OBIEE 11g

Hey we can hide a section on a dashboard page.

Follow the steps

Go to Section Properties

Go to Format section

In CSS Style Write as Dispaly:none


Save ans see Run The Page

Connection failed while Importing Tables using Admin Tool in OBIEE 11g

Got the “The connection has failed” error while trying to import database tables into repository (.rpd) using OCI call interface.

Solution:

1. Copy the tnsnames.ora from Oracle Database home (ORACLE_HOME\NETWORK\ADMIN\) to the following locations.
• \OracleBI1\network\admin (Example: C:\OBI\Oracle_BI1\network\admin)
• \oracle_common\network\admin
• (Example: C:\OBI\oracle_common\network\admin)

2. Set the TNS_ADMIN environment variable value with one of the copied locations in the step 1 in user.cmd or user.sh file depending on your OS.

This file will be found under \instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup

(Example : C:\OBI\instances\instance2\bifoundation\OracleBIApplication\coreapplication\setup)

Conversion of Timestamp to date and vice-versa in Answers.

Conversion of Timestamp to date and vice-versa in Answers.

In Criteria ..on the column click on EDIT FORMULA

Change the formula as shown…

Formula : CAST (CAST (expression as CHAR (20)) as TIMESTAMP)

Click on ‘OK’.