, , ,

Some Standrad Oracle Apps Report Customizations

1. Report Name :- Inactive Item Report         (Inventory)
Customization :-   Add one field Transaction Qty to report
Short Name :-  INVIRSLO
Parameter :- Organization,Subinv Break Option,Category Set Name,Inactive From Date
Table :-  mtl_system_items ,
               mtl_material_transactions ,
                mtl_item_categories,
                mtl_categories.
 
Solution  :-    See the parameter listing which is required for the report with the help of report name and short name .
                           Then  FPT the report from apps/viappl/inv/11.5.0/reports/US to your directory.
                        Go to the data module and click on the Query In the select statement add transaction Qty from MTL_MATERIAL_TRANSACTION
                 Go the report builder navigator window ->click on the icon Layout editor.
                Go to the header section separate all the frames of header section. 
place the boilerplate text for the status.Add One  field and give the source to the field as status
                 save the report -ftp the rdf file
                 create concurrent program with required parameter as per original report  attach that program to the Oracle Payble or your responsibility group
 This Customize Inactive Items Report Shows Added Customer Status.
******************************************************************************************************************************************************************************************
2. Report Name: – A02 Item Summary Listing Report (PO)
Customization: – Displays the summarized item listing with necessary information of buyer.
                          Added a group for category wise summation of list price.
Short Name: – POXSUMIT,     A02_POXSUMIT
Parameter: – Title, Active/inactive (Active, Inactive, Both)
Table: – GL_SETS_OF_BOOKS, FINANCIALS_SYSTEM_PARAMETERS (View),
            MTL_DEFAULT_SETS_VIEW (View), FND_LOOKUPS (View), GL_CODE_COMBINATIONS,
            MTL_UNITS_OF_MEASURE_VL (View), PO_AGENTS, PER_ALL_PEOPLE_F, MTL_SYSTEM_ITEMS,
            MTL_CATEGORIES (View), MTL_ITEM_CATEGORIES, PO_LOOKUP_CODES (View)
******************************************************************************************************************************************************************************************
3.Report Name: – Subinventory Quantity report:  (INV)
Customization: – Customized this report to displays  price of every item and their sub inventories with total of price.
Short Name: – INVIRSIQ, A02_INVIRSIQ
Parameter: –
Table: – MTL_SYSTEM_ITEMS            msi,  
   MTL_ONHAND_QUANTITIES_detail moqd,
   MTL_SECONDARY_INVENTORIES si,
   MTL_ITEM_LOCATIONS mil,
  CST_ITEM_COST_DETAILS CICD
  org_organization_definitions  ood
*********************************************************************************************************************************************************************************************
4. Internal Requisition  Status Report. (PO)
Customization :-   Add total for Quantity Ordered
Short Name :-POXRQSIN
Parameter :-
Table :-  po_requisition_lines ,  po_requisition_headers  ,  mtl_system_items ,  hr_employees  
 
Solution          :-      See the parameter listing which is required for the report with the help of report name and short name . Then  FPT the report from apps/viappl/inv/11.5.0/reports/US to your directory.
 Create one summary Column for the Quantity order for Sum  Go the report builder navigator window ->click on the icon layout add the total field  give the source of the summary column to that field  save the report -ftp the rdf file create concurrent program with required parameter as per original report attach that program to the Oracle Inventory or your responsibility group. This customize Internal Requisition status report shows total for quantity order.
/by
, , , , , ,

Common Reporting Tools in Oracle

Oracle Reports: Fixed format reports delivered with the 11i release were built on this tool. This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.

Oracle Discoverer: is an intuitive tool for creating reports and performing on-line analysis. Discoverer uses the EUL (End User Layer), a meta data definition, which hides the complexity of the database from the end user and provides easy to use wizards for creating reports to suit individual needs. The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel.

Oracle XML Publisher: is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report. At runtime, XML Publisher merges the custom templates with the concurrent request extracts data to generate output in RTF, PDF, HTML and EXCEL.

RXi Report: (Variable reports) – variable format reports delivered with the E-Business 11i. With this tool a user has the ability to print the same report with multiple layouts. The user can also choose which columns he requires on a particular report. This tool is most used on Oracle Financials Applications.

FSG Reports (Financial Statement Generator): is a powerful report building tool for Oracle General Ledger. Some of benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only drawback of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.

Business Intelligence System (BI): is a set of tools to provide high level information for the managers (decision makers) to run their business such as the profitability of a particular business unit. The information this tool provides helps managers to take the right decision with the daily data that is uploaded on their systems

/by
, ,

Oracle Apps Concurrent Processing:An Introduction

This article gives an introduction of  Concurrent Processing in Oracle Application.
Concurrent Program:
An instance of an execution file, along with parameter definitions and incompatibilities. Several concurrent programs may use the same execution file to perform their specific tasks, each having different parameter defaults and incompatibilities.
Concurrent Program Executable:
An executable file that performs a specific task. The file may be a program written in a standard language, a reporting tool or an operating system language.
An execution method can be a PL/SQL Stored Procedure, an Oracle Tool such as Oracle Reports or SQL*Plus, a spawned process, or an operating system host language.
Concurrent Request:
A request to run a concurrent program as a concurrent process.
Concurrent Process:
An instance of a running concurrent program that runs simultaneously with other concurrent processes.
Concurrent Manager:
A program that processes user’s requests and runs concurrent programs. System Administrators define concurrent managers to run different kinds of requests.
There are many concurrent managers each monitoring the flow within each apps area.
But there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Concurrent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatibility checks.
We cannot delete a concurrent manager… but we can disable it… but it’s not recommended.

Concurrent Queue:
List of concurrent requests awaiting to be processed by a concurrent manager.
Phases and Statuses through which a concurrent request runs:
A concurrent request proceeds through three, possibly four, life cycle stages or phases: 

  • Pending                                       Request is waiting to be run
  • Running                                       Request is running
  • Completed                                   Request has finished
  • Inactive                                       Request cannot be run

Within each phase, a request’s condition or status may change.  Below appears a listing of each phase and the various states that a concurrent request can go through.
Concurrent Request Phase and Status 
Phase: PENDING      

  • Normal: Request is waiting for the next available manager.
  • Standby: Program to run request is incompatible with other program(s) currently running.
  • Scheduled: Request is scheduled to start at a future time or date.
  • Waiting: A child request is waiting for its Parent request to mark it ready to run.   

Phase:RUNNING       

  • Normal: Request is running normally.
  • Paused: Parent request pauses for all its child requests to complete. 
  • Resuming: All requests submitted by the same parent request have completed running.  The  Parent   request is waiting to be restarted.
  • Terminating: Running request is terminated, by selecting Terminate in the Status field of the Request Details zone.

Phase:COMPLETED    

  • Normal: Request completes normally.
  • Error: Request failed to complete successfully.
  • Warning: Request completes with warnings.  For example, a report is generated successfully but fails to print.
  • Cancelled: Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
  • Terminated: Running request is terminated, by selecting Terminate in the Status field of the Request Details zone.

 Phase:INACTIVE          

  • Disabled: Program to run request is not enabled.  Contact your system administrator.
  • On Hold: Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
  • No Manager: No manager is defined to run the request.  Check with your system administrator.

 Different execution methods of executabls:

  • FlexRpt                             The execution file is written using the FlexReport API.
  • FlexSql                             The execution file is written using the FlexSql API.
  • Host                                 The execution file is a host script.
  • Oracle Reports                  The execution file is an Oracle Reports file.
  • PL/SQL Stored Procedure   The execution file is a stored procedure.
  • SQL*Loader                      The execution file is a SQL script.
  • SQL*Plus                          The execution file is a SQL*Plus script.
  • SQL*Report                      The execution file is a SQL*Report script.
  • Spawned                          The execution file is a C or Pro*C program.
  • Immediate                       The execution file is a program written to run as a subroutine of the concurrent manager.

 Output formats of a concurrent program:

  • HTML
  • PDF
  • TEXT
  • PS (Post Script)
  • PCL(HP’s Printer Control Language)
/by
, ,

What is Fan Trap in Discoverer and how it handles them?

Fan Trap is a situation while running discoverer reports that return unexpected results due to a group of joined database tables. The most common manifestation of a fan trap occurs when a master table is joined to two or more detail tables independently.
If you use a straightforward SQL statement to aggregate data points here, you may get incorrect results due to fan trap. Now, if you enable fan trap detection in Discoverer and if you use Discoverer to aggregate the data points, Discoverer will never return incorrect results.

Example of Fan Trap:
Consider an example fan trap schema that includes a master folder (ACCOUNT) and two detail folders (SALES and BUDGET), as shown below:
Now let’s say we need to answer the question, “What is the total sales and total budget by account?
Straightforward SQL statement approach:
SELECT Account.Name,
       SUM(sales),
       SUM(budget)
FROM
      Account,
      Sales,
      Budget
Where
      Account.id=Sales.accid
      AND Account.id=Budget.accid
GROUP BY Account.Name;



Account    Sales Budget


Account 1   800   1200


Account 2   130    200


Account 3    600   750


Account 4    600   600


The above results are incorrect, because they are based on a single query in which the tables are first joined together in a temporary table, and then the aggregation is performed. However, this approach causes the aggregates to be summed (incorrectly) multiple times.


Discoverer Approach:


If we run the query in Discoverer interrogates the query, detects a fan trap, and rewrites the query to ensure the aggregation is done at the correct level. Discoverer rewrites the query using inline views, one for each master-detail aggregation, and then combines the results of the outer query.


Here are the results from discoverer which is correct:


Account   Sales   Budget


Account 1  400      400


Account 2  130      100


Account 3  200      750


Account 4  300      200


How to enable fan trap in discoverer?


By default, fan trap detection is always enabled for you. If you want to disable it (however not recommended), you can logon to Discoverer Plus, go to Tools > Options >Advanced Tab and click on ‘Disable fan trap detection’.




How Discoverer handles fan trap?


If a fan trap is detected, Discoverer can usually rewrite the query using inline views to ensure the aggregation is done at the correct level. Discoverer creates an inline view for each master-detail aggregation, and then combines the results of the outer query.


In some circumstances, Discoverer will detect a query that involves an unresolvable fan trap schema, as follows:


  • If the detail folders use different keys from the master for the join

  • If there is a direct join relationship between the detail folders (thereby creating an ambiguous circular relationship)

  • If non-aggregated values are chosen from more than one of the detail folders

  • If more than one detail folder has a separate join relationship to a different master folder



In the above circumstances, Discoverer disallows the query and displays an error message.





/by 
, , , , 
Registering Custom PLSQL Functions in Discoverer


Although Discoverer provides many functions for calculation in reports, sometime we require to use custom PL/SQL functions to meet additional Discoverer end user requirements (for example, to provide a complicated calculation). For this we first need to create the functions in database through Toad or other PL/SQL editors.


To access custom PL/SQL functions using Discoverer, you must register the functions in the EUL. When you have registered a custom PL/SQL function, it appears in the list of database functions in the “Edit Calculation dialog” and can be used in the same way as the standard Oracle functions.


Note: To register a PL/SQL function you must have EXECUTE privilege on that function.


You can register custom PL/SQL functions in two ways:


    

  •  Import automatically, by importing the functions (recommended)
    • 

  •  Manually
    • 



How to register custom PL/SQL functions automatically:


To register PL/SQL functions automatically you must import them in the following way:


 1. Choose Tools | Register PL/SQL Functions to display the “PL/SQL Functions dialog: Functions tab”.


  


 2. Click Import to display the “Import PL/SQL Functions dialog”. This dialog enables you to select the PL/SQL functions that you want to import.




 


3. Select the functions that you want to import. You can select more than one function at a time by holding down the Ctrl key and clicking another function.


 


4. Click OK.


Discoverer imports the selected functions and displays the function details in the “PL/SQL Functions dialog: Functions tab”. Information about the selected functions is imported automatically. In other words, you do not have to manually enter information or validate the information.


 


5. Click OK.


The PL/SQL function is now registered for use in Discoverer.


How to register custom PL/SQL functions manually:


To manually register a PL/SQL function for use in Discoverer:


1. Choose Tools | Register PL/SQL Functions to display the “PL/SQL Functions dialog: Functions tab”.


2. Click New and specify the function attributes.


 


3. Click Validate to check the validity and accuracy of the information you have entered.


4. If the function is invalid, correct the attributes and click Validate again.


5. (Optional) if the function accepts arguments:


a. Display the “PL/SQL Functions dialog: Arguments tab”.


b. On the Arguments tab, click New and specify the argument attributes.


 


6. Click OK when you have finished defining the function.


The custom PL/SQL function is now registered for use in Discoverer.


It is always recommended to register PL/SQL functions by importing automatically (especially if you have many functions to register), because it is easy to make mistakes when manually entering information about functions. When you import functions, all of the information about each function (for example, names, database links, return types, lists of arguments) is imported.




/by