Good Blog Link For Fusion

http://oracleapps88.blogspot.in/search/label/FUSION

Interface And Conversion Concept In Oracle Apps

How conversion and interface differ?

There are good numbers of parameter on which they can be categorize. Take few of them:

• Frequency
  • conversions are a one time event
  • interfaces are ongoing
• Occurrence in the project timeline
  • conversions executed before production
  • interfaces executed during production
• Manner of execution
  • conversions are batch
  • interfaces may be batch or real time
• Complexity
  • Conversion does have very complex, its totally depends upon the data mapping activity.
  • coordinating with other systems make interfaces more complex
• Maintenance
  • Maintence of interface is bit cost intensive task.

Interface Type
You have learned how interface is differ from Conversion/Migration. Now lets take few types of interfaces:
Normally in any system , there are two kinds of interface as:

Inbound Interfaces

• An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables.
• A typical inbound interface would follow these steps:
  1. Extract data from legacy system into a flat file.
  2. Use SQL*Loader or equivalent tool to upload information into a temporary table.
  3. Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.
  4. Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

Outbound Interfaces
o An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).
o A typical outbound interface would follow these steps:
- Write a PL/SQL program to extract data from Oracle base tables into a flat file.
- Use a custom program to read that data and post it into the legacy system

Do we have some other standard way to do interface?

• Open Interface is a table based interface registered as a concurrent program
  • process records in batches.
  • spawned(Pro-C) or PL/SQL based programs.
• API (Application Program Interface) is a parameter based stored procedure
  • directly impacts base database tables.
  • may be called from Oracle open interfaces,Forms, Reports.
• EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO's, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.(
• Enterprise Application Integration (EAI) solutions are often used when complex integration requirements exist.

What Is An Open Interface Table (OIT)?

• For inbound interfaces, the interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
• Open Interface Tables are standard Oracle tables.
  • Oracle uses OITs to provide a simple interface to Oracle base tables.
  • Oracle has list of all the open interface that oracle offered in there product.

Oracle Interface Program

• Most Oracle modules have standard import programs (concurrent processes) to facilitate custom inbound interfaces. The specific processing performed varies by application.
• These programs pull data from the open interface tables, validate the data, and then insert into one or more Oracle base tables.
• Upon successful completion of processing, the program deletes the processed rows from the interface table or marks them as completed.
• Depending on the import, errors can be viewed in various ways (exception reports, error tables, forms, etc…).

Examples of standard import programs:

• GL: Journal Import
• AP: Payables Open Interface
• AR: Customer Interface
• INV : Item Import
• AR - Autoinvoice

Package to Get Account Concatenated Description In Oracle Apps

GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION CHART_ACCOUNT_ID,CODE_OMBINATION_ID,SEGMENT_VALUE)

Types of Triggers in Oracle Apps

Types of Triggers in Oracle Apps

Triggers check for an event. When the event occurs they run the PL/SQL code associated with the trigger.

Report triggers are activated in response to report events such as the report opening and closing rather that the data that is contained in the report. They are activated in a predefined order for all reports.

Format triggers are executed before an object is formatted. A format trigger can be used to dynamically change the formatting attributes of the object.

Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.

Database triggers are procedures that are stored in the database and implicitly executed when a triggering statement such as INSERT, UPDATE, or DELETE is issued against the associated table.

Report Triggers

About report triggers

Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database. To create or modify a report trigger, you use the Report Triggers node in the Object Navigator. Report triggers must explicitly return TRUE or FALSE.
Oracle Reports has five global report triggers. You cannot create new global report triggers.


The trigger names indicate at what point the trigger fires:

<!--[if !supportLists]-->·         <!--[endif]-->Before Report trigger: Fires before the report is executed but after queries are parsed.

<!--[if !supportLists]-->·         <!--[endif]-->After Report trigger: Fires after you exit the Paper Design view, or after report output is sent to a specified destination, such as a file, a printer, or an e-mail ID. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.

<!--[if !supportLists]-->·         <!--[endif]-->Between Pages trigger: Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Paper Design view, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.

<!--[if !supportLists]-->·         <!--[endif]-->Before Parameter Form trigger: Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.

<!--[if !supportLists]-->·         <!--[endif]-->After Parameter Form trigger: Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

Order of report triggers execution

The order of events when a report is executed is as follows:

<!--[if !supportLists]-->1.      <!--[endif]-->Before Parameter Form trigger is fired.

Note: If the Parameter Form is used on the Web, the Before Parameter Form trigger fires twice: once when the Parameter Form is displayed, and a second time when the parameters are submitted. This is because Oracle Reports executes in a stateless fashion. There is no session to return to, so the Before Parameter Form trigger has to fire the second time to ensure that the parameters selected on the Parameter Form and passed on the command line are valid.

<!--[if !supportLists]-->2.      <!--[endif]-->Runtime Parameter Form appears (if not suppressed).

<!--[if !supportLists]-->3.      <!--[endif]-->After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter Form).

<!--[if !supportLists]-->4.      <!--[endif]-->Report is "compiled".

<!--[if !supportLists]-->5.      <!--[endif]-->Queries are parsed.

<!--[if !supportLists]-->6.      <!--[endif]-->Before Report trigger is fired.

<!--[if !supportLists]-->7.      <!--[endif]-->SET TRANSACTION READONLY is executed (if specified with the READONLY command line keyword or setting).

<!--[if !supportLists]-->8.      <!--[endif]-->The report is executed and the Between Pages trigger fires for each page except the first one. (Note that data can be fetched at any time while the report is being formatted.) COMMITs can occur during this time due to: SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.

<!--[if !supportLists]-->9.      <!--[endif]-->COMMIT is executed (if READONLY is specified) to end the transaction.

<!--[if !supportLists]-->10.  <!--[endif]-->After Report trigger is fired.

<!--[if !supportLists]-->11.  <!--[endif]-->COMMIT/ROLLBACK/NOACTION is executed based on what was specified with the ONSUCCESS command line keyword or setting.

Notes

• In steps 4 through 9, avoid DDL statements that would modify the tables on which the report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid throughout the execution of the report. In steps 7 through 9, avoid DML statements that would modify the contents of the tables on which the report is based. Queries may be executed in any order, which makes DML statements unreliable (unless performed on tables not used by the report).
• If you specify READONLY on the command line, you should avoid DDL altogether. When you execute a DDL statement (for example, with SRW.DO_SQL), a COMMIT is automatically issued. If you are using READONLY, this will prematurely end the transaction begun by SET TRANSACTION READONLY.
• As a general rule, any processing that will affect the data retrieved by the report should be performed in the Before Parameter Form or After Parameter Form triggers. (These are the two report triggers that fire before anything is parsed or fetched.) Any processing that will not affect the data retrieved by the report can be performed in the other triggers.
• Consistency is guaranteed if you use DML or DDL in (or before) the After Form Trigger. However, consistency is not guaranteed in the Before Report trigger, since Oracle Reports may have to start work on data cursors before that trigger based on the definition of the report. Before the Before Report trigger, Oracle Reports describes the tables involved and opens cursors. Any change to the tables after that will not be seen by the report.

Restrictions

• If you are sending your report output to the Paper Design view or Previewer, you should note that some or all of the report triggers may be fired before you see the report output. For example, suppose that you use SRW.MESSAGE to issue a message in the Between Pages trigger when a condition is met. If there are forward references in the report (for example, a total number of pages displayed before the last page), Oracle Reports may have to format ahead to compute the forward references. Hence, even though you have not yet seen a page, it may already have been formatted and the trigger fired.
• In report triggers, you can use the values of report-level columns and parameters. For example, you might need to use the value of a parameter called COUNT1 in a condition (for example, IF :COUNT1 = 10). Note, though, that you cannot reference any page-dependent columns (that is, a column with a Reset At property set to Page) or columns that rely on page-dependent columns.
• In the Before and After Parameter Form, and Before and After Report triggers, you can set the values of parameters (for example, give them a value in an assignment statement, :COUNT1 = 15). In the Before and After Report triggers, you can also set the values of report-level, placeholder columns.
• In the Between Pages trigger, you cannot set the values of any data model objects. Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not recommended. If you do this, you may get unpredictable results.
• A lexical reference cannot be used to create additional bind variables after the After Parameter Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference):

SELECT ENAME, SAL FROM EMP &WHERE_CLAUSE

If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you must specify the value in the After Parameter Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger. If you supplied this same value in the After Parameter Form trigger, the report would run.

WHERE SAL = :new_bind

Creating a report trigger

To create a report trigger:

<!--[if !supportLists]-->1.      <!--[endif]-->In the Object Navigator, expand the Report Triggers node.

<!--[if !supportLists]-->2.      <!--[endif]-->Double-click the PL/SQL icon for the trigger you want to create.

<!--[if !supportLists]-->3.      <!--[endif]-->In the PL/SQL Editor, define the PL/SQL for the report trigger.

Deleting a report trigger

To delete a report trigger:

<!--[if !supportLists]-->1.      <!--[endif]-->In the Object Navigator, expand the Report Triggers node.

<!--[if !supportLists]-->2.      <!--[endif]-->Double-click the PL/SQL icon for the trigger you want to delete.

<!--[if !supportLists]-->3.      <!--[endif]-->In the PL/SQL Editor, drag to select the PL/SQL code.

<!--[if !supportLists]-->4.      <!--[endif]-->Choose Edit > Delete.

Format Triggers

About format triggers

A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object. For example, you can use a format trigger to cause a value to display in bold if it is less than zero. Another example is to use a format trigger to use scientific notation for a field if its value is greater than 1,000,000.
A format trigger can fire multiple times for a given object, whenever Reports Builder attempts to format the object. Consider the case where Reports Builder starts to format the object at the bottom of a page. If the object does not fit on the page, Reports Builder stops formatting and reformats on the following page. In this case, the format trigger will fire twice. It is therefore not advisable to do any kind of "persistence" operation, such as logging, in this trigger.
The Reports Builder SRW built-in package contains PL/SQL procedures with which you can quickly change the format attributes of an object. These include procedures to:

<!--[if !supportLists]-->·         <!--[endif]-->change the border pattern and color of an object

<!--[if !supportLists]-->·         <!--[endif]-->change the interior pattern and color of an object

<!--[if !supportLists]-->·         <!--[endif]-->change the font size, style, weight, spacing, and justification of a field or boilerplate text

<!--[if !supportLists]-->·         <!--[endif]-->change the format mask of a field

<!--[if !supportLists]-->·         <!--[endif]-->access a field's value

Creating or editing a format trigger

To create or edit a format trigger using the Property Inspector:

<!--[if !supportLists]-->1.      <!--[endif]-->In the Paper Design view, double-click the object for which you want to create or edit a format trigger to display the Property Inspector.

<!--[if !supportLists]-->2.      <!--[endif]-->Under Advanced Layout, set the Format Trigger property by clicking the ... button to display the PL/SQL Editor.

<!--[if !supportLists]-->3.      <!--[endif]-->Define the PL/SQL for the format trigger.

To create or edit a format trigger using the Object Navigator:

<!--[if !supportLists]-->1.      <!--[endif]-->In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object for which you want to create or edit a format trigger.

<!--[if !supportLists]-->2.      <!--[endif]-->Double-click the PL/SQL icon next to the object for which you want to create or edit a format trigger to display the PL/SQL Editor.

<!--[if !supportLists]-->3.      <!--[endif]-->Define the PL/SQL for the format trigger.

Validation Triggers

Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.

Note: For JSP-based Web reports, the Runtime Parameter Form displays when you run a report in Reports Builder, but does not display in the runtime environment. If parameters are not specified on the Runtime Parameter Form, the validation trigger returns false and generates error message rep-546 Invalid Parameter Input error.

Validation triggers are also used to validate the Initial Value property of the parameter. Depending on whether the function returns TRUE or FALSE, the user is returned to the Runtime Parameter Form.

Database Triggers

About database triggers

Database triggers are procedures that are stored in the database and implicitly executed when a triggering statement such as INSERT, UPDATE, or DELETE is issued against the associated table. Triggers can be defined only on tables, not on views. However, triggers on the base table of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.
A trigger can include SQL and PL/SQL statements that execute as a unit, and can invoke other stored procedures. Use triggers only when necessary. Excessive use of triggers can result in cascading or recursive triggers. For example, when a trigger is fired, a SQL statement in the trigger body potentially can fire other triggers.
By using database triggers, you can enforce complex business rules and ensure that all applications behave in a uniform manner. Use the following guidelines when creating triggers:

<!--[if !supportLists]-->·         <!--[endif]-->Use triggers to guarantee that when a specific operation is performed, related actions are performed.

<!--[if !supportLists]-->·         <!--[endif]-->Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.

<!--[if !supportLists]-->·         <!--[endif]-->Do not define triggers that duplicate the functionality already built into Oracle. For example, do not define triggers to enforce data integrity rules that can be easily enforced using declarative integrity constraints.

<!--[if !supportLists]-->·         <!--[endif]-->Limit the size of triggers (60 lines or fewer is a good guideline). If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure, and call the procedure from the trigger.

<!--[if !supportLists]-->·         <!--[endif]-->Be careful not to create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the EMP table that itself issues an UPDATE statement on EMP causes the trigger to fire recursively until it has run out of memory.