My Macro Photography

My Macro Photography

Photography is my hobby and whenever I get time, I try to experiment things with my Canon DSLR 550D. Here I am posting few of my photographs. I am still a beginner in this field. If you are a photographer, your comment and suggestions will be highly appreciated!

















Dibyajyoti Koch. Have a nice day!

© Copyright for all images remains with the photographer.

Please DO NOT distribute, copy, publish or use the images or any part of the images in any way without express permission of the copyright holder.

Get Apps Environment Details From Database

Get Apps Environment Details From Database



This table tracks the mount points for the APPL_TOPs in an Applications system. Each mount point has a distinct host and path.



This table will track the servers used by the E-Business Suite system.

select  server_type,
from applsys.fnd_app_servers;


This table stores information about environment name and value for each of the concurrent process.

select  variable_name,
from   fnd_env_context
where  variable_name like '%\_TOP' escape '\'
and    concurrent_process_id =
     ( select max(concurrent_process_id)
       from fnd_env_context )
order by 1;

API’s to Create User,Reset Password and Add Responsibility

API’s to Create User,Reset Password and Add Responsibility

I have created few queries using Oracle provided package:’FND_USER_PKG’. These queries might be very useful when you donot have the Oracle Apps front end access or you like to get in done through backend.

Using the below query, you can create a User in Oracle application.Just pass username, password and email id as parameters and it will create a user.

v_user_name varchar2(30):=upper('&Enter_User_Name');
v_password varchar2(30):='&Enter_Password';
v_session_id integer := userenv('sessionid');
v_email varchar2(30):=upper('&Enter_Email_Id');
  fnd_user_pkg.createuser (
  x_user_name => v_user_name,
  x_owner => null,
  x_unencrypted_password => v_password,
  x_session_number => v_session_id,
  x_start_date => sysdate,
  x_end_date => null,
  x_email_address => v_email
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
when others then
  DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

May a times we forgot the apps password. Then you can use the below query to resent the password just in few seconds.

v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
 v_status:= fnd_user_pkg.ChangePassword (
    username => v_user_name,
    newpassword => v_new_password
  if v_status =true then
  dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
  DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  END if;

Use the below query to add a responsibility to a user. The advantage here is that you donot require system administrator responsibility access to add a responsibility.

v_user_name varchar2(30):=upper('&Enter_User_Name');
v_resp varchar2(30):='&Enter_Responsibility';
v_resp_key varchar2(30);
v_app_short_name varchar2(50);
    r.responsibility_key ,
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
    r.application_id =a.application_id
    and upper(r.responsibility_name) = upper(v_resp);

  fnd_user_pkg.AddResp (
  username => v_user_name,
  resp_app => v_app_short_name,
  resp_key => v_resp_key,
  security_group => 'STANDARD',
  description => null,
  start_date => sysdate,
  end_date => null
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

Few Interesting Questions on Oracle GL Journals Entry

Few Interesting Questions on Oracle GL Journals Entry

Is There a Report That Displays Information of One Specific Journal Entry Unposted/Posted?

No. General Ledger reports display information of journal batches posted or unposted. However, you can use the below sql query to find information of a particular journal entry.

select  b.je_batch_id batch_id ,
        h.je_header_id header_id ,
        l.je_line_num line ,
        l.code_combination_id ccid ,
        g.segment1 || '.' || g.segment2 || '.' || g.segment3 ||
        '.' || g.segment4 || '.' || g.segment5 || '.' || g.segment6 ||
        '.' || g.segment7 || '.' || g.segment8 || '.' || g.segment9 ||
        '.' || g.segment10 combination ,
        l.entered_dr entered_dr,
        l.entered_cr entered_cr,
        l.accounted_dr accounted_dr,
        l.accounted_cr accounted_cr,
from    gl_je_lines l,
        gl_je_headers h,
        gl_je_batches b,
        gl_code_combinations g
where   b.je_batch_id = h.je_batch_id
        and h.je_header_id = &je_header_id
        and l.je_header_id = h.je_header_id
        and h.je_batch_id = b.je_batch_id
        and l.code_combination_id = g.code_combination_id
order by h.je_header_id, l.je_line_num;

Can a Posted General Ledger Journal Entry be deleted?

After a journal entry is posted, it cannot be deleted. Posted journal entries cannot be deleted because that would eliminate the audit trail. To nullify the accounting effect of the posted journal entry, you can reverse it.

When can not a journal batch be deleted or modified?

A journal batch cannot be deleted or modified under the following circumstances:

a. The source is frozen

b. Funds have been reserved for the batch

c. Funds are in the process of being reserved for the batch

d. The batch is in the process of being posted

e. The batch is posted

f. The batch is approved

g. The batch is in the process of being approved

A journal batch should not be updated if it comes from a sub-ledger.

Changing accounting information in a journal that originated in a sub-module will unsynchronize the accounting information between the ledger and the sub-ledger. Instead of changing the sub-ledger journal, define a new journal to adjust the accounting information if necessary.

A journal batch that has funds reversed cannot be updated because the funds would not be re-reserved appropriately.

Which report shows details of a posted journal batch?

Journals – General (180 Char) and Journals – General (132 Char)

Is possible to restrict users from entering negative amounts in journal lines?

Unfortunately, it is not possible to restrict users from entering negative amounts in journal entry lines.

How to set up journal approval in General Ledger?

This is set up using Oracle Workflow Builder. The basics steps to setup Journal Approval are as below

a) Enable Journal Approval at the Ledger level
b) Setup Journal Sources for Journal Approval
c) Configure the profile options that control how the approval list will be built
d) Define Employees and Supervisors
e) Define Approval limits for approvers
f) Associate the employees to Oracle Apps users
g) Optional Workflow Configuration

For more information refer metalink notes: ID 176459.1 & ID 278349.1

How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?

  1. Query the Journal that needs the spreadsheet attachment.

  2. Click on the paperclip on the tool bar.

  3. Fill the following fields in the Attachment form.

     Category    –  Choose Journal from LOV

     Description –  optional

     Data Type   –  OLE Object from the LOV

  4. Right click on the large white portion of the Attachment form choose ‘Insert Object’ from the drop box.

  5. When the Insert Object Form appears check “create from file” and click on “Browse” to choose the file that should be attached from the directory structure.

  6. Save.

How do you easily copy a journal entry from one set of books to another?

There is no standard feature to copy journal entries between sets of books. However, there are some alternatives. Refer Metalink note: ID 204082.1

How to prevent user’s ability to reverse unposted journals?

For 11i, there is not a method to prevent users from reversing unposted journals. This is intended functionality to incorporate the maximum flexibility that users may require. However you can limit user access to journal reversals through user menus set up in Sys Admin responsibility.

This functionality changed in R12 – see Note 734848.1 In Release 12, a batch must be posted before it can be reversed.

How do you automatically generate a reversal journal entry for a journal category in the previous accounting period?

If you routinely generate and post large numbers of journal reversals as part of your month end closing and opening procedures, you can save time and reduce entry errors by using Automatic Journal Reversal to automatically generate and post your journal reversals.

First you define journal reversal criteria for journal categories. Journal reversal criteria let you specify the reversal method, period and date. You can also choose to enable automatic generation and posting of journals.

When you create a journal entry you want to automatically reverse, specify a journal category that has assigned reversal criteria. Your journal will be reversed based on the method, period and date criteria you defined for that journal category.

In Release 12, a reversal journal that is Unposted cannot be modified. Why?

This is the expected functionality in Release 12. However the profile GL: Edit Reverse Journals can be set to allow the modification. Refer metalink note: ID 567641.1

Reversing journal was deleted from the system, how can you still reverse the original journal?

General Ledger does not allow you to reverse a journal entry twice. . Refer metalink note: ID 145043.1 for details.

A journal entry with a source set up for automatic reversal is not reversed. Why?

General Ledger automatically submits the AutoReverse program when a period is opened if the profile option, GL: Launch AutoReverse After Open Period, is set to Yes. If a journal is created after the period has already been opened, then the AutoReverse program will need to be submitted manually.

A journal has been created and is unposted.  The following period has a reversing journal for the original journal and it is posted. Why it is so?

This is currently the functionality of the application to allow the reversing journal to be posted even if the original journal is not.

Query to get Customer Information in R12

Query to get Customer Information in R12

This query is tested in R12.1.3 instance. The query may take few minutes to execute depending upon the size of the data that are present in various HZ Tables.

select  distinct
        hp.party_name "Customer Name",
        hcsu.status loc_stat,
        hcsu.site_use_id, profile_name,
        HOU.NAME "Operating Unit"
from    apps.hz_parties hp,
        apps.hz_party_sites hps,
        apps.hz_locations hl,
        apps.hz_cust_accounts hca,
        apps.hz_cust_acct_sites hcas,
        apps.hz_cust_site_uses hcsu,
        apps.hz_customer_profiles hcp,
        apps.hz_cust_profile_classes hcpc,
        apps.ar_payment_schedules_all ps,
        apps.hr_operating_units hou
where   hp.party_id = hca.party_id(+)
        and hp.party_id = hcp.party_id
        and hp.party_id = hps.party_id
        and hps.party_site_id = hcas.party_site_id
        and hps.location_id = hl.location_id
        and hca.cust_account_id = hcas.cust_account_id
        and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
        and hca.cust_account_id = hcp.cust_account_id
        and hca.cust_account_id = ps.customer_id
        and hcp.profile_class_id = hcpc.profile_class_id
        and ps.customer_site_use_id = hcsu.site_use_id
        and hcsu.org_id = hou.organization_id;

LOVs in Discoverer Reports

LOVs in Discoverer Reports


We can create list of values (LOV) for any parameter in discoverer reports through Item Classes. Here suppose in Discoverer Report, we have a parameter called Period Name. It has a small LOV icon in the right side.

When clicked on the LOV icon, the below window with all the Period Names will appear where you can choose one or many period names.

Now the question is how to do this. You just need to do the below steps to accomplish this task.

1] Create a custom folder named ‘Time Periods’ in a business area with the below query.

select  distinct
        decode(period_num,1, 'January',
                          2, 'February',
                          3, 'March',
                          4, 'April',
                          5, 'May',
                          6, 'June',
                          7, 'July',
                          8, 'August',
                          9, 'September',
                          10, 'October',
                          11, 'November',
                          12, 'December', null) month_name
from gl_periods;

2] Select the business area in which you want to create an item class. Choose Insert > Item Class.

3] Select the LOV Item class attribute.


4] Select the above created folder (Time Periods) and choose the Period Name column.


5] Here you can select the various items of other folders that can use this item class. You can skip this stage and later you can manually assign this item class to other items.


6] Choose the defaults and click next.


7] Give a suitable name to the Item Class


8] Now go to the Item of the folder on which you created the Parameter (Period Name) and then go to Item Properties. Here you assign the item class that you have created just now.


9] If you already created the parameter in your workbook and your discover plus or desktop is open then close it and reopen. Then you can view the LOV attached to the parameter.

10] If you haven’t already created the parameter, then create the parameter and run the report. You can view the LOV attached to the parameter.

Working with Complex Folders in Discoverer

Working with Complex Folders in Discoverer


Complex folders contain items from one or more base folders. It is same like a view in database. It enables you to create a combined view of data from multiple folders.

A base folder can be any of the following types of folder:

  • a simple folder, containing items based on columns in a single database table or view
  • a custom folder, based on SQL statements
  • a complex folder, containing items from one or more base folders

However you could produce the same result set using a database view instead of a complex folder. But using complex folders has few advantages over using database views.

  • When we use complex folders, the sql queries are automatically optimized by Discoverer. If we use view, we cannot get that improved performance.
  • We can create complex folders without database privileges, but for view creation we require that.

How to create complex folders?

1] Logon to Discoverer Administrator.

2] Select the business area in which you want to create a complex folder.

3] Choose Insert > Folder > New to create a new complex folder.

4] Click the new folder’s icon on the Data tab and choose Edit | Properties. We can give a more descriptive name, Description and Identifier Name for the new folder.

5] On the “Workarea: Data tab”, Drag an item from any folder in any open business area to the new folder. Or you can simply do copy and paste.

Tip: You might find it easier to drag items between folders if you have two Workareas open. To open a second Workarea, choose Window | New Window (Shift W).

Note: When you add an item to a complex folder, the folders that it comes from must be joined to the folder of at least one other item already in the complex folder. If this is not the case, Discoverer Administrator will display an error dialog. 

Note: If you select items from two folders that are joined using more than one join, Discoverer displays the Choose Join dialog. Here you can select one or more joins and click OK.

Note: If you select an item from a simple folder that has a join that conflicts with existing items, Discoverer will display an error and you will not be allowed to add the item.

If you want to see all the joins, conditions and the workbooks that are based on your complex folder, go to Folder Properties | Dependents Tab.

What is complex folder reach through?

Complex folder reach through is a mechanism that enables Discoverer Plus and Discoverer Desktop users to add items to their worksheets in addition to those provided in a selected complex folder.

In Discoverer Administrator you can define one or more base folders within a complex folder as ’reach through enabled’. When a Discoverer Plus or Discoverer Desktop user selects an item from the complex folder, the associated reach through enabled base folders become available for selection in a worksheet.

Advantages of using the XML Publisher Report

Advantages of using the XML Publisher Report


Choose Your Design Tool:

Xml Publisher Report formats can be designed using Microsoft Word or Adobe Acrobat – tools most users are already familiar with. There is no proprietary design studio component required, meaning no extra cost and no extra learning curve.

Data from Anywhere:

Xml Publisher can accept and format any well-formed Xml data, as well as generate the Data. It can therefore be integrated with any database. Xml Publisher also allows you to bring data In from Multiple Data Sources into a Single Output Document.


Deliver To Anywhere:

Deliver your content via Printer, E-Mail, Fax, Webdav, or publish your report to a portal. The Delivery Manager’s open architecture allows for easy implementation of custom delivery channels, as well.

Communicate With The World:

Report Layouts can be created for any combination of 185 languages and 244 territories. These Layouts are converted to Industry-Standard files for easy In-House translation or delivery to third party translation providers. Because each translation is a separate file, modifications can still be made to the Layout without impacting translations. Xml Publisher also offers Advanced Bidirectional and Font Handling Support.

Unsurpassed Performance:

Xml Publisher is based on the W3c Xsl-Fo Standard and it is the Fastest, Most Scalable Implementation in the world today. It can handle very large data inputs and generate output in less time, using very low levels of CPU time and memory.

Open Standards. Easy Integration:

Xml Publisher is built on Open Standard technologies. It is a J2ee application that can be deployed to any J2ee container. Data is handled as Xml and the Layout Templates created in the desktop applications are internally converted to Xsl-Fo, another W3c standard. Outputs generated by the application are also industry standards such as Pdf, Rtf and Html. The delivery protocols are Internet Printing Protocol (Ipp), Webdav, Ftp, As2.

Oracle Discoverer Basics

Oracle Discoverer Basics

About Discoverer:

Discoverer is the end user adhoc query, reporting and analysis tool, which provides quick development environment to develop Data Warehousing & Business intelligence reports.

What is business intelligence?

Business Intelligence is the ability to analyze data to answer business questions and predict futures.

Features and Benefits:

  • Automated Summary Management – improving query performance in Discoverer Plus is now easier with this new feature.
  • Oracle Applications support – This enables you to connect to Oracle Applications EULs as well as standard Discoverer EULs.
  • Identifiers – all objects in a Business Area are now uniquely defined within each EUL by an Identifier.
  • Change schema owner – you can now manually edit the schema Owner attribute (for a Folder) or leave it blank.
  • Support for Oracle Materialized Views.
  • Analytic Functions – perform complex mathematical analysis with this extended range of statistical functions.

How does it Works?

When a user creates or opens a worksheet, Discoverer:

  •  Converts the worksheet into the corresponding SQL statements (e.g. by converting folder names and item names to table names and column names respectively).
  •  Sends the SQL statements to the database.
  •  Displays the result set that is returned from the database.

Discoverer Components:

  •  Oracle Discoverer Administrator
  •  Oracle Discoverer Plus
  •  Oracle Discoverer Desktop Edition (Viewer)

Oracle Discoverer Administrator:

Discoverer Administrator is a tool to hide the complexity of the database from business users, so they can answer business questions quickly and accurately using Oracle Discoverer.

Discoverer Administrator’s wizard-style interfaces enable you to:

  • Set up and maintain the End User Layer (EUL)
  • Create Folders, Items and Item Classes
  • Control access to information
  • Create conditions and calculations for Discoverer end users to include in their worksheets

Oracle Discoverer Plus:

It is a web portal like Oracle Application front end through which you can log on with your oracle application username and password and create and run discoverer reports.

Oracle Discoverer Desktop Edition (Viewer):

It works same as Discoverer Plus, but it is a software that comes with Oracle BI Publisher setup. Here also you can create and run your discoverer reports.

End User Layer (EUL):

End User Layer is a Metadata repository for Business areas, folders and items on which Discoverer workbooks or reports based on. The EUL is a set of database tables that contain information (or ‘metadata’) about the other tables and views in the database. 

Business Area:

A Business Area is a set of folders containing related information with a common business purpose.

Business Areas:

  • Meet the specific data needs of the users
  • Usually contain data from several different tables or views
  • Have tables or views and their associated columns mapped to “folders” and “items” respectively
  • Can contain many simple folders and complex folders
  • May contain folders from one or more physical databases include conditions, joins, calculations, formatting,   hierarchy structures, and other custom features
  •  May be accessible to one or many user IDs or roles; also, a user ID or role may be granted access to many business areas
  •  Let users access data, without having to understand the database structure


The tables and views loaded into a Business Area are called as folders. There are 3 types of folders that you can create – Simple, Complex and Custom.


The columns of the folders are named as items.

Item Classes:

Item classes are like LOVs in Oracle Application. We can create item classes based on any columns of a table and we can join that item classes to any parameters of the discoverer reports.


A Join relates two folders using common items.


Conditions filter worksheet data, enabling Discoverer end users to analyze only the data they are interested. Conditions are applied to the folders or items.

Calculations & Hierarchies:

A calculated item is an item that uses a formula to derive data for the item.

Hierarchies are default drill paths between item that you define in Discoverer Administrator. There are two types of Hierarchy in Discoverer Administration Edition:

  1. Item Hierarchies
  2. Date Hierarchies

Summaries & Summary Folders:

A summary improves query performance in Discoverer Plus or Discoverer Viewer by using pre-aggregated data created through Discoverer Administration Edition.

A Summary Folder is how Discoverer represents the underlying structure of a summary table or MV. Each Summary Folder has one or more Items (i.e. columns from a summary table or MV).


Automated Summary Management – It delivers fast performing queries while greatly reducing the amount of time spent on administration. ASM analyzes your tables, uses query statistics (when available) and default values (the summary policy) to determine how summaries are created for you. ASM is able to automatically create and maintain the best set of summaries based on this combination.


Database Query for Discoverer

Database Query for Discoverer

All discoverer folders and workbooks that you create get stored in database. You can use the below queries to find the details.

Find your Discoverer Workbook and Folder Details from Database:

select  fu.user_name owner,
        doc.doc_id "Document Id",
        doc.doc_name "Document Name",
        min(qs.qs_created_date) first_acc,
        max(qs.qs_created_date) Last_Acc
from    disco_apps.eul5_documents doc,
        apps.fnd_user fu,
        disco_apps.eul5_qpp_stats qs
where   '#'||fu.user_id = doc.doc_created_by
        and qs.qs_doc_name=doc.doc_name
        and qs.qs_doc_owner=fu.user_name
        and doc.doc_created_date<qs.qs_created_date
group by fu.user_name,
order by doc.doc_updated_date desc;

Find your Discoverer Workbook performance for different Business Areas:

select "Business Area",
        doc.doc_name "Work Book",
        min(qs_act_elap_time) "Fastest",
        max(qs_act_elap_time) "Slowest",
        round(avg(qs_act_elap_time),2) "AVG (s)",
        round(avg(qs_act_elap_time)/60,2) "AVG (m)",
        count(*) "Often",
        min(acc.qs_created_date) "First",
        max(acc.qs_created_date) "Last Access"
from    disco_apps.eul5_documents doc,
        apps.fnd_user usr,
        disco_apps.eul5_qpp_stats acc,
        (select distinct gd_doc_id from disco_apps.eul5_access_privs ) privs,
          select distinct doc.doc_id,ba.ba_name ba
          from disco_apps.eul5_documents doc
          ,disco_apps.eul5_elem_xrefs eex
          ,disco_apps.eul5_ba_obj_links bol
          ,disco_apps.eul5_objs obj
          ,disco_apps.eul5_bas ba
          WHERE doc.doc_id = eex.ex_from_id
          AND eex.ex_to_par_name = obj.obj_name
          AND obj.obj_id = bol.bol_obj_id
          AND bol.bol_ba_id = ba.ba_id
where   '#'||usr.user_id=doc.doc_created_by
        And doc.doc_name=acc.qs_doc_name
        And privs.gd_doc_id = doc.doc_id
        And usr.user_name = upper(acc.QS_DOC_OWNER)
        And doc.doc_created_date<acc.qs_created_date
        And doc.doc_id=BA.doc_id
group by,