Claender Query

SELECT   LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Su", "Mo", "Tu",
         "We", "Th", "Fr", "Sa"
    FROM (SELECT   TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
                   CASE
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Dec%'
                      AND TO_CHAR (dt + 1, 'iw') = '01'
                         THEN '53'
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Jan%'
                      AND TO_CHAR (dt + 1, 'iw') = '53'
                         THEN '.5'
                      ELSE TO_CHAR (dt + 1, 'iw')
                   END week,
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Su",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Mo",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Tu",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "We",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Th",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Fr",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sa"
              FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
                      FROM all_objects
                     WHERE ROWNUM <=
                                ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
                              - TRUNC (SYSDATE, 'y'))
          GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
          ORDER BY TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

Making Not Required Parameter in Discoverer

The old version of Discover4i forced to enter values for all the paramters. If the value needs to be passed as NULL, then user had to enter either NULL or '' as the parameter value.

Discover 10g has got rid of this problem. The parameter can now be made a non-required parameter by checking "Require User to Enter a Value" checkbox as shown in the screenshot below.

Dependent Value Parameter in Discoverer reports

In Discover 4i there was no way to filter the list of second parameter based on the value selected in first parameter.
Discoverer Version 10g has overcome this problem and allows to filter the List of Values based on another parameter. This is very similar to dependent value set that we create in Oracle Apps.
In the paramter screen(shown in the screen shot below) Select Option "Filter the List of Values based on the selection conditions, then select the parameter based on which the value need to be filtered. Click OK and you are all set to filter the list based on another parameter.

Create List of Values (LOV) in Discoverer reports.

The Item classes in Discoverer are treated as List of Values (LOVs). A List of value can be created and referenced by several fields of the folder.
Following are the steps to create LOV in discoverer. In the example below I am creating LOV on US States.
1) Connect to Discoverer using Admin and select the business area where folder/LOV needs to be created.

2) Create a custom folder for US States using following query

 select * from ar_lookups
where lookup_type like 'STATE'

3) Select the field for which Item class is to be created and right click on that to select New Item Class. Click next on the wizard and finally click finish button to complete creation of Item class. In our example, the LOV is created on the lookup code field.

4) Now if any parameter is created based on Lookup Code column then the List of value will be attached to it.
5) This LOV can also be referenced by another column. For example there is another folder which has column Bill to State. Select that column and right click to go to the

properties. Click on Item Class and select the Item class created in step 3. 

Discoverer Desktop/Administrator tools Installation Steps (Secure Env)

1) Install BI Tool (discoverer) using below link:http://www.oracle.com/technology/software/products/ids/htdocs/101202winsoft.html
 or
install dektop and administrator from the setup cd.

Identify the Oracle Home for Discoverer
For example: You could find dis51usr.exe under the directory D:\oracle\BIToolsHome_1\bin. Then the Oracle Home is D:\oracle\BIToolsHome_1

2) Create SECURE folder in this path D:\oracle\BIToolsHome_1\discoverer\secure
and copy the file "example.dbc" to this dir.
(Discoverer requires Windows Environment variables to dynamically retrieve the location of the .dbc file on the PC.)


3.copy this (SET FND_SECURE=D:\oracle\BIToolsHome_1\discoverer\secure) path and add to "AUTOEXEC.BAT" file in "C Drive".




4. place TNSNAMES.ora file to this path (D:\oracle\BIToolsHome_1\network\ADMIN) for Discoverer tools.

OA clear cache (Woks like Bounceing Appache)

FUNCTIONAL ADMINISTRATOR responsibility.

Navigate to core services > caching > global components > Then press the clear cache button.

Sometimes you get this error if you haven't performed these steps

Patch info query in SQL (Oracle APPS)

select application_short_name, bug_number, to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') dated
from apps.ad_bugs
where bug_number = '5968876';

select * from apps.fnd_product_Groups ;
select * from fnd_product_installations where patch_level like '%AD%';

select * from WF_RESOURCES  where TYPE = 'WFTKN' and NAME = 'WF_VERSION';

select * from v$version

SELECT DECODE (bug_number
, '3554613', '4.5.0'
, '3263588', 'XDO.H'
, '3822219', '5.0.0'
, '4236958', '5.0.1'
, '4206181', '5.5.0'
, '4561451', '5.6.0'
, '4905678', '5.6.1'
, '5097966', '5.6.2') PATCH, bug_number
FROM ad_bugs
WHERE bug_number IN
('3554613'
, '3263588'
, '3822219'
, '4236958'
, '4206181'
, '4561451'
, '4905678'
, '5097966');

select WF_EVENT_XML.XMLVersion() XML_VERSION  from sys.dual;


select fnd_oracle_schema.getouvalue('LOGINSERVER') from dual