Monday, March 30, 2015

Few Interfaces Explained here.


Order Import Interface.
Item import 
Item import
Inventory On-hand quantity Interface
Auto Invoice Interface.
AR Receipts
AP Invoices
Vendor
Purchase Orders
Requisition
Receiving
Journal import
Budget import
Daily Conversion Rates

Friday, March 27, 2015

Thursday, March 26, 2015

XML Publisher FAQs.

1.How does the concurrent request relate both data template and layout template it should be using for generating the output?

Ans: The concurrent program ‘short name’ will be mapped to the ‘code’ of the Data template. Layout template is attached to the data template, this forms the mapping between all the three.

2.What is Data Template and Layout Template?

Data Template:
Datatemplate is an xml structure which contains the queries to be run against the database so that desired output in xml format is generated, this generated xml output is then applied on to the layout template for the final output

Layout Template:

Layout template defines how the user views the output, basically it can be developed using Microsoft word document in rft (rich text format) or Adobe pdf format. The data output in xml format (from Data template) will be loaded in layout template at run time and the required final output file is generated.

3.How to get a output of a XMLP report in different formats like PDF, DOC, XLS, TXT ?

Ans:While submitting the concurrent program you select the output format in options form of “Up on Completions” selection.



4.Can you have multiple layout templates for a singe data template?

Ans: Yes! Multiple layouts can be attached, so that user will have a choice here to use one among them at the time of concurrent program submission.

5.Do we need to create multiple layout templates for printing report in multiple languages?

Ans:We can achieve multi language report by two ways.

       1.Different layout template for different languages This approach can be chosen if the layout is also different from language to language.

       2. Generate XLIFF template for each language
           XLIFF (XML Localization Interchange File Format) : format for exchanging localization data. XML based format that enables translators to concentrate on the text to be translated. We use this option when we want to use the same layout and apply specific translation.

6.How do you migrate layout or data template across instances?
Ans:We can use XDOLoader utility to migrate both data template and layout. Below is the 

Wednesday, March 25, 2015

About CUSTOM.pll.

Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a newbusiness rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.
CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are
1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime.
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens.
6. Enabling Special Menu

Primarily there are two methods of extending Oracle Forms, and these are 
    CUSTOM.pll
    FORMS Personalizations
In this article we will cover the basics of using CUSTOM.pll for extending Oracle Forms

How and why does CUSTOM.pll work?
Every form in Oracle Apps is created using something called as TEMPLATE.fmb. But some of the modules like HRMS have their own HR Specific Templates[HRTEMPLT.fmb]. These template files have form level triggers that make call to CUSTOM.pll. The triggers that can be trapped using CUSTOM.pll in HRMS screen can be different than those which can be trapped for other modules.

Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.
How to add code to this?
Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?
 Once you make changes you need to compile the pll. Use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special
While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.


For following Events call will go to CUSTOM Library:
 
WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)

Custom Library contains Custom Package which is having two Functions and one procedure.
1] ZOOM_AVAILABLE:
This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.

Sample code1:

function zoom_available return Boolean is
    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');
begin
    if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then
      return TRUE;
    else
      return FALSE;
    end if;
end zoom_available;


How to make the changes get affected?

Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.
Forms Personalization: an alternative of custom.pll 
In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.
Advantages of Forms Personalization over Custom.PLL:

  • Forms personalization can be used by an user with limited PL/SQL knowledge. 
  • Changes take place immediately on reopening the form.
  • Anything which can be done using Custom.PLL can be done using Forms Personalization also.
  • Personalizations are stored in base tables related to Form Personalization.
  • CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.
  • Easy to disable/enable with click of a button.
  • Can be moved easily through FNDLOAD from one instance to other.
  • Can be restricted at site/responsibility/user level.
  • Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

Monday, March 23, 2015

Briefly about Accounts Payables and FAQs

Invoices:Invoices is a formal document to pay to vendor. Upon shipment of goods invoices are normally sent by suppliers and same is entered in payable's module. You can manipulate(create,edit,adjust) group of invoices or invoices batches in invoices workbench.
Invoices can be manually entered or automatically imported from purchasing,EDI or Interface.

Types of Invoices:

Standard
Credit Memo
Debit Memo
Expense Report
Prepayments
Retainage Release
Transportation Invoice
Withhold Tax
Mixed.

Credit Memo and Debit Memo: Credit Memo is a negative amount invoice given by supplier to give a credit. Debit Memo is a negative amount invoice created by you and sent to supplier.

Invoice Holds: Holds are applied to prevent payments to suppliers

Payments: For each eligible Invoice or invoice lines, payments are created.

Prepayments in Oracle Payables:

Normal Payable cycle is that you first create invoice when you get one from you supplier for the goods that you have bought and then make a payment of the amount in the invoice to the supplier.
But there are scenarios where the supplier requests for an advance when you order the goods. Supplier wants you to pay a certain amount at the time of order and then pay the remaining upon delivery of the goods.

You can enter two types of prepayments: Temporary and Permanent.

Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel’s invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.

Permanent prepayments cannot be applied to invoices. For example, you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.

Withholding Tax Invoices in Oracle AP:

Withholding tax (WHT) is the amount withheld while making a payment to a supplier/contractor/any payee. Instead it is paid to the tax authorities.

You may be required to withhold taxes from your employee expense reports and supplier invoices. Payables lets you to automatically withhold tax based on the certain conditions you specify in Withholding Tax region of the Payables Options window.

Retainage Release Invoices in Oracle AP:

Retainage means retaining certain amount of money with us without paying the supplier in full. The retained amount is released or paid to the supplier when all the terms of the agreement have been met or all the work is completed.

AP: Payment Manager Process in R12.

Payment Batch lets you create payments for multiple invoices that meet the criteria you specify in a single shot. In R12, it lets you select invoices with different Operating units, Currencies in a single batch.

Note: The process of creating payment batches and processing payments in R12 has changed completely when compared to 11i.

Navigation: Payables responsibility > Payments > Entry > Payment Manager.




Wednesday, March 18, 2015

SQL Loader - Introduction,Structure of the data file,Structure of a Controlfile,Datafile as External and/or within controlfile,Run SQL Loader from Unix and Register as Conc Program.

#Introduction:
SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called log file, bad file or reject file, discard file.
  • Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
  • Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.
  • Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.
#Structure of the data file:
The data file can be in fixed record format or variable record format.
Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:
7369 SMITH      CLERK        7902  12/17/1980         800
7499 ALLEN      SALESMAN  7698  2/20/1981           1600
7521 WARD      SALESMAN  7698  2/22/1981           1250
Variable Record Format would like below where the data fields are separated by a delimiter.
Note: The Delimiter can be anything you like. In this case it is “|”
1196700|9|0|692.64
1378901|2|3900|488.62
1418700|2|2320|467.92

#Structure of a Control file:
OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.
SKIP = n — Number of logical records to skip (Default 0)
LOAD = n — Number of logical records to load (Default all)
ERRORS = n — Number of errors to allow (Default 50)
ROWS = n   — Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)
BINDSIZE = n — Size of conventional path bind array in bytes (System-dependent default)
SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} — Suppress messages during run
(header, feedback, errors, discards, partitions, all)
DIRECT = {TRUE | FALSE} –Use direct path (Default FALSE)
PARALLEL = {TRUE | FALSE} — Perform parallel load (Default FALSE)
LOADDATA statement is required at the beginning of the control file.
INFILE: INFILE keyword is used to specify location of the datafile or datafiles.
INFILE* specifies that the data is found in the control file and not in an external file. INFILE ‘$FILE’, can be used to send the filepath and filename as a parameter when registered as a concurrent program.
INFILE   ‘/home/vision/kap/import2.csv’ specifies the filepath and the filename.
#Ex:Datafile as External:
LOAD DATA
INFILE   ‘/home/vision/kap/import2.csv’
INTO TABLE kap_emp
FIELDS TERMINATED BY “,”
( emp_num, emp_name, department_num, department_name )
#Example where datafile is in the Control file:
LOAD DATA
INFILE *
INTO TABLE kap_emp
FIELDS TERMINATED BY “,”
( emp_num, emp_name, department_num, department_name )
BEGINDATA
7369,SMITH,7902,Accounting
7499,ALLEN,7698,Sales
7521,WARD,7698,Accounting
7566,JONES,7839,Sales
7654,MARTIN,7698,Accounting
#Example where file name and path is sent as a parameter when registered as a concurrent program
LOAD DATA
INFILE ‘$FILE’
INTO TABLE kap_emp
FIELDS TERMINATED BY “,”
( emp_num, emp_name, department_num, department_name )
TYPE OF LOADING:
INSERT   — If the table you are loading is empty, INSERT can be used.
APPEND  — If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn’t already exist, the new rows are simply loaded.
REPLACE — All rows in the table are deleted and the new data is loaded
TRUNCATE — SQL*Loader uses the SQL TRUNCATE command.
INTOTABLEis required to identify the table to be loaded into. In the above example INTO TABLE “APPS”.”BUDGET”, APPS refers to the Schema and BUDGET is the Table name.
FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)
OPTIONALLY ENCLOSED BY ‘”‘ specifies that data fields may also be enclosed by quotation marks.
TRAILINGNULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
#Steps to Run the SQL* LOADER from UNIX:
At the prompt, invoke SQL*Loader as follows:
sqlldr USERID=scott/tiger CONTROL= LOG=
SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.
#Register as concurrent Program:
Place the Control file in $CUSTOM_TOP/bin.
Define the Executable. Give the Execution Method as SQL*LOADER.
Define the Program. Add the Parameter for FILENAME.
Skip columns:
You can skip columns using the ‘FILLER’ option.
Load Data



TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)

Monday, March 9, 2015

1.Oracle OAF Overview,Architecture and Benefits.

#OAF Overview

With arrival for R12 and now Oracle Fusion, oracle is moving from Forms based interface to
Web based UI. So now days there is a buzz of OAF and ADF which has been the bases for development of these Web based UI for Oracle ebiz.

Oracle Application Framework(OA Framework) is a proprietary framework developed by Oracle Corporation for application development within the Oracle E-Business Suite.
OA Framework is an architecture for creating web based front end pages and J2EE type of applications within the Oracle EBS ERP platform. In order to develop and maintain OAF functionality, Oracle's JDeveloper tool is used. OA Framework uses the UIX other XML technologies for building the components.

OA Framework is based on J2EE technology called BC4J (Business Components for Java)
The OA Framework is a Model-view-controller (MVC) framework built using J2EE (Java 2 Platform, Enterprise Edition) technologies.

#OAF Architecture
  • OA Framework is based on Model View Controller design pattern.



Model: (Data) implemented using Oracle Business Components for Java (BC4J).

1. EO (Entity Object):Entity Object is based on database table or other data source.Entity   Object contains attributes which represent database columns.All insert/update/delete (DML Operations) transactions go through EO to database.
      2.VO( View Objects ):
   View Objects are based on EO or SQL Query which is again based on EO Objects
   Two types
    1. SQL based
    2. EO based
   Basically VO is synonymous to views used in PLSQL Programming they are used for joining   tables, filtering based on conditions and sorting the data. Entity Objects can be based on any number of EO and provide access to EO.
      3. Application Module:
     Its a container for VO. Once you create a Application Module you need to associate the      corresponding VO to the Application Modules. Access to the VO is always provided through the Application Module.Every Page in OAF Framework need to be associated with a AM.

View: (User Interface) Implemented using an Oracle technology called UIX.
      (UIX = User Interface XML).

Controller: (Code) User actions are handled by the OA Controller.
      (Ex: Clicking “Go” button)
Controller responds to user actions and directs application flow. Model objects like EO and VO can't be accessed directly from theController Class, except AM.
Contains methods such as,
1. ProcessRequest: Fires when OAF page loads for the first time.

2. ProcessFormRequest: Fires when user submits the page.

#Benefits:
  • Consistent and Compelling User Interface
  • Durable Personalization and Extensibility
  • User Interface Interactivity
  • Built-in Security
  • Integrated Development Environment
  • Mobile Support
  • Functional Administration Tool

1.Oracle Alerts With Examples.

Overview: You can use alerts simply to notify one person/multiple persons about an event or some data change. There are two type of alerts
  • Event based Alerts
  • Periodic Alerts
Event based Alerts: These Alerts are fired/triggered based on some change in data in the database. Ex: If you want to notify your manager when you create an item in the inventory you can use Event based alerts. When you create an item in the inventory it will create a new record in mtl_system_items_b, here inserting a record in the table is an event so when ever a new record is inserted it will send the alert. In same alert you can also send the information related to that particular item.
Periodic Alerts: These Alerts are triggered hourly, daily, weekly, monthly or yearly based on your input. Ex: If you want to know list of all items created on that day at the end of day you can use periodic alerts repeating periodically by single day. This alert is not based on any changes to database. This alert will notify you everyday regardless of data exists or not that means even if no items are created you will get a blank notification.
What can be done with Alerts?
  • You can send notifications
  • You can send log files as attachments to notifications
  • You can call PL/SQL stored procedures.
  • You can send approval emails and get the results.
  • Print some content dynamically
How to create an Alert?
  • Study your Business requirement and decide what type of alert you need either periodic alert or event based alert.
  • If you are going for periodic alert decide the frequency
  • If you have chosen event based alert then find out on what event (insert, update, delete) you want to fire the alert.
  • Decide what data need to be included in the alert
  • Based on the data you want in the alert write a SELECT SQL Statement to pull the data.
  • Create a distribution list grouping all the people to whom you want to send the alert.
Navigation:
  • Go to “Alert Manager” Responsibility
  • Alert >> Define
Transfer Alert from one instance/database to other:
  • Go to Alert Manager Responsibility
  • Alert >> Define
  • Go to “Tools” Menu on top
  • Click on “Transfer Alert”
  • Enter source and destination fields and click Transfer.
Screenshots:


Tuesday, March 3, 2015

8.Discoverer Report Migration from One Instance to Another Instance:

#To Move/Migrate the report we can use file->Export and File-> Import (to destination system)

Current Instance:

Select the required objects in the end user layer->Next select the folder name, first move it to selected area.

Again Click on LOV->Select the item class->move

Again click on LOV->select the workbook name->Move it to selected area

Click on next->Provide the name->Finish
(Save the log file for future verification purpose)  The extension is '.eex'

Now go to other instance : Open Administrator Edition->File->Import->Click on Add->Choose file->Next-->Next->Start.

Now the report along with corresponding objects will be imported to this instance,

Tip:Table used to delete existing report:

'delete from EUL5_Documents where doc_name=''Report_Name";

7.Deploying Discoverer Report into Oracle Apps.


After done with creating discoverer report make a note of workbook name and below  are the steps to register a discoverer report in Oracle Apps.

Make sure you followed each and every one correctly.

1. Go to Application Developer-->Function

2. Enter Function , User Function Name, Description(Optional) in Description Tab.
Ex:Function: TEST_DISCO
User Function Name: Discoverer Report
Description: Discoverer Report

3 In Properties Tab
Select Type “SSWA plsql function”
Maintenance Mode Support – None
Context Dependence - Responsibility

4. In Form Tab
Parameters --> Enter the Workbook name created previously

EX: Workbook=TEST_WORKBOOK
In case you want to give some parameters then you can use:
workbook=&parameters=~*~*~*
Example:
workbook=&Parameters=age~26*salary~ 1000*

5. In Web HTML Tab

Enter HTML Call - OracleOasis.RunDiscoverer

6. Now you can add this Function in any existing or new Menu

Also Note that Discoverer Report cannot be invoked when you’ve direct login into Oracle Applications through http://:/dev60cgi/f60cgi

You need to access through logging in from the browser web page. Otherwise it will give an Error.

7. Now navigate to Responsibility which contain the Menu to which you added this function.

8. Click the Function Name to Display the report.

6.Working with Worksheets : Subtotal,Groupsort,grandtotal,Number Format,Line,SQL Inspector


#Subtotal:Go to  tools->Totals->Check the check box->New(Project Status Code)->Count->Insert

Select the column->Change the lable to subtotal. Here you can insert item name also , to change dynamic title ->Ok->Ok.

#Group Sort:To avoid the repetation values in particulor column,we can use this feature. We can display only single time.

Select the column name->Right Click->Group Sort.

#Grand Total: Go to tools->Total->Click on New->Select the radio button->Grand total at bottom->Ok->Ok.

#Number Format:To apply the number format we can use this feature.For every amount field we need to apply the number format.

Select the amount column->Right Click->Format data->Number Tab. Click on decimal place take 2,Check the check box of 1000 separators.

#Line:After Applying group sort we need to display one underline for every grouped results.

#SQL Inspector:In modifying the workbook we need to find out the view name.Follow below steps.

Copy the folder then go to discoverer admin edition->Business Area->Select the folder->Right Click->Properties->Copy Identifier->Go to database->find the script of it->Modify(Here we can see the query with view name).

Note: We can avoid all these steps in Desktop Edition. got to ->SQL Inspector and there we can find the view name in the from clause.

5.Joins,format the results,Export the results,Parameters


#Joins In Discoverer: a join relates two folders using one or more matching items. In the database, a join relates two tables using matching columns.

Type of Joins

Single Item Joins

Multi Item Joins

Joining more than two folders

Non-Equi-Joins

One-to-many joins:Are the most common type of join. With a one-to-many join, one row in the master folder is joined to multiple rows in the detail folder.

One-to-one joins:One-to-one joins are joins between two folders where both items used in the join are primary keys. Therefore only one row in one folder will join to only one (or no) rows in the other folder.

Many-to-Many Joins:Many-to-many joins are not supported directly in Discoverer (or in any relational system). However, many-to-many joins can always be reworked and transformed into multiple one-to-many joins. Very occasionally, a many-to-many join is a valid construct.

#Format the results:With the format exception feature we can highlight with the color based on condition.

Select the particular column->right click->Format Exception

Click on New->Select the column name

Condition->Choose the color->Click on 'OK'.

#Export the Results:With this option we can export the entire output into excel sheet by using 'X' symbol in the tool bar.But with this we can export the results from only one worksheet.
To export all the worksheets from the workbook go to File->Export->Choose the number of Worksheets->Ok

#Parameters:To create new parameters go to tools->Parameters->Click on New(Provide Name,Prompt,Description)

After creation of the parameters we need to change the condition if we are using range of parameters.

Go to tools->Conditions, Use >= From value and <= to value (or) use between & operator.

Note: We can apply same parameters to multiple tabs/worksheets without creating into other sheets.

4.Multiple Worksheets in the same workbook and Scheduling Workbooks.


#We can include multiple worksheets in the same workbook.These worksheets may be created on same folder from same business area (or) other busniess area (or) other

folder from other business area (or) same business area.

To create new sheet go to sheet-> new sheet and click on edit sheet for editing.

#Scheduling:Whenever the report is taking much time to execute we can use this option.

Go to File->Scheduling->Select the required worksheets,mention time and date and frequency.

We can manage the scheduling by using scheduling manager.

File->Manage Workbooks->Scheduling Manager.

3.Creation of Workbooks using Page-detail and Cross tab.


#This chapter explains how to create Workbook using Page details and Cross tab.

• Build a new workbook or worksheet
• Edit a worksheet:

Select the type of display for the new worksheet or workbook:

• Table: Table output has columns of information with headings
• Crosstab: It is also called as drill down approach.Crosstab output looks like a bus or train timetable with values displayed across the top, down the left edge and in the intersection of rows and columns.

  • Normal tables have selected items across the top
  • Crosstabs have items across the top and items down the side

                Values are shown in the grid
                Some kind of aggregate (sum, count, etc.) if necessary

  • Page Items are also useful for filtering data
Example:

• Page-Detail: A page detail layout is a table or crosstab report produced for each value of the page item,such as an account.

Sample Page Item


 Select the data that belongs on the worksheet in the workbook

2.Components and Architecture of Discoverer with Explanation.


#Components:
  • Discoverer Administration Edition
  • Discoverer Desktop Edition
  • Discoverer Plus
  • Discoverer Viewer
#Architecture:

1.End User Layer(EUL):The End User Layer of Discoverer provides a way to mask the underlying complexity of the database to provide end users with an interface they can easily understand.

2.Business Area:A business area is a collection of related information in the database.example,
Ex:information about Sales may be stored in one business area, while information about material is stored in another business area.

3.Business Folders:
  • Simple Folders – Folders that are based on a database table (e.g.: ITEM)
  • Custom Folders – Folders that contain a custom SQL query.
  • Complex Folders – Folders that are based on multiple simple folders.
Security: We will give specific business area access to users under 'Tools->Security.

:: When we open administration in discoverer we can see 4 tabs.

Data-hierarchies-item class-summaries(explanations of all these)

Data:In data tab we can see all the objects and navigation, such as Business Area,Folders and thier properties such as joins,items selected..etc

Hierarchies: We can set up items in a hierarchy to enable end users to drill up and down the hierarchy to view related information.Using Hierarchy Wizard we can create hierarchies.

Discoverer will have two types of hierarchies

1.Date Hierarchy

2.Item Hierarchy

Item Classes:We can display the list of values of the parameters. We can create item_clause in admin edition.Select the folder->expand it->right click->new item clause

Summary Folders:Discoverer summary tables are database tables created by Discoverer Administrator that contain summarized data. Discoverer summary tables contain pre-aggregated and pre-joined data, and can improve query performance in Discoverer Plus and Discoverer Viewer.

We can use the Automated Summary Management (ASM) Wizard to create summary folders in discoverer or else  we can create manually by selecting the option 'Click the I want to specify the summaries myself radio button'.

1.Introduction to Discoverer and Connecting to it.


#Introduction:

Discoverer is reporting tool used to support organizational decisions and data will show in the form of excel format.

Versions:4i,6i,9i,10g,11g

#Connection Steps:

1.Take a copy of TNSNames.ora file for every instance we have separate TNS Names entry in tnsnames.ora file

2. we need to keep in network/admin folder in the discoverer home

Ex:Path : D:/Discoverer/Network/Admin

For every instance TNS Entry is same,only 'host name' and 'port number' and 'service name id' will vary. In place of host name,port number we can see the server id address also some times.

After Every Cloning the '.dbc' files will change , we need to download fresh '.dbc' file related to any instance and keep into secure folder

Set the environment variable initially

We usually download the .dbc files from unixbox by using any FTP(ex: putty,filezillah,toad..etc)

EUL Settings:

Oracle Discoverer administrator->tools->options->choose the option

*connect to both standard and application EUL's gateway

userid/pwd(applsys/pub)

Foundation Name: APPS

Monday, March 2, 2015

6.User Exits in Oracle Reports.

#User Exits in Oracle Reports.
User exit when called from Report triggers moves the control from report to this outside program there it performs the action programmed and returns back to report environment. 
The program which makes USER to EXIT from ongoing environment to perform certain action is USER EXIT. 
We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.
Below are the user exits available in Oracle Reports that makes AOL integration.
FND SRWINIT:
When "FND SRWINIT" user exit is called  in the report's Before Report trigger, the user exit uses the passed request id to query the Oracle Applications context that the concurrent request was submitted in (via the FND_CONCURRENT_REQUESTS table) and then log on with that context.

function BeforeReport return boolean is
begin
  SRW.user_exit('FND SRWINIT');
  return (TRUE);
end;
FND SRWEXIT:
It is used to free the memory which has been allocated by the other user exits.
FND SRWEXIT ensures that all the memory allocated for Oracle AOL user exits has been freed up properly. It is called in After Report trigger.

function AfterReport return boolean is
begin
  srw.user_exit( 'FND SRWEXIT' );
    RETURN (TRUE);
  --return (TRUE);
end;
FND FORMAT_CURRENCY:
It is used for MULTIPLE CURRNECY REPORTING (MRC).
This user exit formats the currency amount dynamically depending upon the precision
of the actual currency value, the standard precision, whether the value is in a mixed
currency region, the user's positive and negative format profile options, and the
location (country) of the site.

Syntax

FND FORMAT_CURRENCY
CODE=":column containing currency code"
DISPLAY_WIDTH="field width for display"
AMOUNT=":source column name"
DISPLAY=":display column name"
[MINIMUM_PRECISION=":P_MIN_PRECISION"]
[PRECISION="{STANDARD|EXTENDED}"]
[DISPLAY_SCALING_FACTOR="":P_SCALING_FACTOR"]

FND FLEXIDVAL:
This API is used for get descriptions from flexfields, gets input from FND FLEXSQL.
This user exit is called to populate fields for display. The key flexfields data retrieved by the query is passed into this exit from the formula column. With this exit values, descriptions and prompts can be displayed by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).

Syntax:

FND FLEXIDVAL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
DATA=":source column name"
[NUM=":structure defining source column/lexical"]
[DISPLAY="{ALL|flexfield qualifier|segment number}"]
[IDISPLAY="{ALL|flexfield qualifier|segment
    number}"]
[SHOWDEPSEG="{Y | N}"]
[VALUE=":output column name"]
[DESCRIPTION=":output column name"]
[APROMPT=":output column name"]
[LPROMPT=":output column name"]
[PADDED_VALUE=":output column name"]

[SECURITY=":column name"]

FND FLEXSQL:

FND FLEXSQL:


This fragment allows to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by the SELECT statement.This user exit is called once for each fragment you need for your select statement. All flexfield columns are defined in your report as type CHARACTER even though the table may use NUMBER or DATE or some other datatype.
This user exit FND FLEXSQL is called  with different arguments to specify that part of the query you would like to build.The user exit retrieves the appropriate column names (SQL fragment) and inserts it into the lexical parameter at runtime before the SQL query is executed.

Syntax:

FND FLEXSQL
CODE="flexfield code"
APPL_SHORT_NAME="application short name"
OUTPUT=":output lexical parameter name"
MODE="{ SELECT | WHERE | HAVING | ORDER BY}"
[DISPLAY="{ALL | flexfield qualifier | segment
    number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" |
    MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" |
    BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]

Note:

1. We use the User Exits in the Report triggers like before report, after report, before parameter form, after parameter form.
2. We have to create a mandatory parameter called P_CONC_REQUEST_ID when we work with FND SRWINIT, FND SRWEXIT. We have to create these variables.

P_CONC_REQUEST_ID Number(20) --
P_Struct_Num Number(20) --
P_Flex_Data Char(6000) (Segment1||' '||Segment2......||Segmentn)
3. The user exits are case sensitive we have to write them in capital letters only otherwise they will raise an error.