Normalization / De-Normalization

Normalization:

It is the process of efficiently organizing data in a database. There are two goals of the normalization process:

• Eliminate redundant data (for example, storing the same data in more than one table).
• Ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Eliminate Repeating Groups – Make a separate table for each set of related attributes, and give each table a primary key.
Eliminate Redundant Data – If an attribute depends on only part of a multi-valued key, remove it to a separate table.
Eliminate Columns Not Dependent On Key – If attributes do not contribute to a description of the key, remove them to a separate table.
Isolate Independent Multiple Relationships – No table may contain two or more 1:n or n:m relationships that are not directly related.
Isolate Semantically Related Multiple Relationships – There may be practical constrains on information that justify separating logically related many-to-many relationships.

1st Normal Form (1NF):

Def: A table (relation) is in 1NF if

1. There are no duplicated rows in the table.
2. Each cell is single-valued (i.e., there are no repeating groups or arrays).
3. Entries in a column (attribute, field) are of the same kind.

Note:

• The order of the rows and columns are immaterial(of no importance).
• The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns).

2nd Normal Form (2NF):

Def: A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.

Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, “A table is in 2NF if it is in 1NF and if it has no partial dependencies.”

3rd Normal Form (3NF):

Def: A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

Note: A transitive dependency is a type of functional dependency in which the value in a non-key field is determined by the value in another non-key field and that field is not a candidate key.

Boyce-Codd Normal Form (BCNF):

Def: A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

4th Normal Form (4NF):

Def: A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies. A multi-valued dependency occurs when, for each value in field A, there is a set of values for field B and a set of values for field C but fields B and C are not related.

5th Normal Form (5NF):

Def: A table is in 5NF, also called “Projection-Join Normal Form” (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain-Key Normal Form (DKNF):

Def: A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.

De-Normalization:

Denormalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply Denormalization in the process of deriving a physical data model from a logical form.

Bind & Lexical Parameters in Reports

Bind & Lexical Parameters in Reports

 

Bind Parameters:

Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses. You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

Lexical Parameters:

Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.

FAQs in Oracle Forms – Part:3

FAQs in Oracle Forms – Part:3

 

What is the difference between Pre-Form and When-New-Form-Instance trigger?

Pre-Form trigger will be fired before entering into the form. It is the first trigger that fires when a form is run; fires before the form is visible. It is useful for setting access to form items, initializing global variables, and assigning unique primary key from an Oracle sequence.

When-New-Form-Instance trigger will be fired whenever form is ready to accept the data from the user.

Another main difference between the two is that you cannot navigate in a pre-from trigger (restricted) whereas you can navigate in a when-new-form-instance trigger. For example : go_block and execute-query will only work in when-new-form-instance trigger not in pre-from trigger.

What are the triggers fired while creating Master Detail form?

On-Clear Details (Form Level)
On-Populate-Details (Block Level)
On-Check-delete-Master (Block Level)

How will you get the block Name in a form?

By using SYSTEM.CURRENT_BLOCK

What is the difference between Pre-insert and On-insert trigger?

Pre-insert trigger fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert. On-insert trigger fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.

What is the difference between Pre-Query and Post-Query trigger?

Pre-Query trigger validates the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database. Post-Query trigger perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. It fires once for each record fetched into the block.

What is the trigger sequence while opening a form?

Pre-Form,
Pre-Block,
Pre-Record,
Pre-Item,
When-new-form-Instance,
When-new-block-Instance,
When-new-Record-instance,
When-new-Item-instance,
Post-item,
post-record,
Post-block,
Post-form

What is the difference between new form, open form & call form?

New_form:-Once we move into the destination automatically source will be closed.

Open_form:- It is a two way connection between source and destination. Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.

Call_form:- Call_form() runs an indicated form while keeping the parent form active. Without closing the destination one cannot come back to the source.

How to call a Report from a form?

By using RUN_PRODUCT Built-in.

Can we write Commit statement in forms triggers?

Yes

What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ?

These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively.

A query fetched 10 records. How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?

PRE-QUERY fires once. POST-QUERY fires 10 times.

How can you execute the user defined triggers in forms?

To execute a user-named trigger, you must call the EXECUTE_TRIGGER built-in procedure, as shown here: Execute_Trigger(‘my_user_named_trigger’);

What are Restricted Built-in Subprograms in forms?

Any built-in subprogram that initiates navigation is restricted. This includes subprograms that move the input focus from one item to another, and those that involve database transactions. Restricted built-ins are not allowed in triggers that fire in response to navigation.

For example, the GO_ITEM and NEXT_SET built-ins are both restricted procedures. GO_ITEM moves the input focus from a source item to a target item, which requires navigation. Similarly, the NEXT_SET procedure causes Oracle Forms to navigate internally to the block level, fetch a set of records, and then navigate to the first item in the first record. (Note that this navigation happens internally as a result of default processing, and may not be apparent to the form operator.) Because GO_ITEM and NEXT_SET both initiate navigation, they cannot be called from triggers that fire in response to internal navigational events; that is, triggers that fire while navigation is already occurring. Thus, a restricted procedure cannot be called from a Pre-Block trigger, because the Pre-Block trigger fires during internal navigation. In fact, restricted subprograms are not allowed from any PRE- or POST- navigational triggers. You can, however, call a restricted built-in subprogram from a When-New-Instance trigger. For example, the When-New-Item-Instance trigger fires after navigation to an item has succeeded, when the form is waiting for input.

What are System Variables in forms?

A system variable is a Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.

List system variables available in forms 10g?

SYSTEM.CURRENT_FORM

SYSTEM.CURRENT_ITEM

SYSTEM.CURRENT_VALUE

SYSTEM.CURSOR_BLOCK

SYSTEM.CURSOR_ITEM

SYSTEM.CURSOR_RECORD

SYSTEM.CURSOR_VALUE

SYSTEM.FORM_STATUS

SYSTEM.LAST_FORM

SYSTEM.LAST_QUERY

SYSTEM.LAST_RECORD

SYSTEM.MASTER_BLOCK

SYSTEM.MESSAGE_LEVEL

SYSTEM.SUPPRESS_WORKING…..etc.

What are the triggers associated with the image item?

When-Image-activated(fires when the operator double clicks on an image Items)

When-image-pressed(fires when the operator selects or deselects the image item)

What are the built-in routines available in forms to create and manipulate a parameter list?

Add_parameter

Create_Parameter_list

Delete_parameter

Destroy_parameter_list

Get_parameter_attr

Get_parameter_list

set_parameter_attr

What are the built-ins used to trapping errors in forms?

Error_type : Returns the error message type(character)

Error_code : Returns the error number

Error_text  : Returns the message text of the Oracle Forms error

Dbms_error_code : Returns the error number of the last database error that was detected.

Dbms_error_text : Returns the message number (such as ORA-01438) and message text of the database error

What is the predefined exception available in forms ?

The FORM_TRIGGER_FAILURE exception is a predefined PL/SQL exception available only in Oracle Forms.

What are the Built-ins used for sending Parameters to forms?

You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.

How do you reference a Parameter?

In pl/sql, You can reference and set the values of form parameters using bind variables syntax.

How do you reference a parameter indirectly?

To indirectly reference a parameter use the NAME_IN or COPY built-ins.

What is forms_DDL?

It issues dynamic sql statements at run time, including server side pl/sql and DDL

What is Text_io Package?

It allows you to read and write information to a file in the file system.

What is When-Database-Record trigger?

It fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. It generally occurs only when the operator modifies the first item in the record, and after the operator attempts to navigate out of the item.

What is the difference between $$DATE$$ & $$DBDATE$$?

$$DBDATE$$ retrieves the current database date

$$DATE$$ retrieves the current operating system date.

What is a timer?

Timer is a “internal time clock” that you can programmatically create to perform an action each time the timer expires.

What are built-ins associated with timers?

find_timer

create_timer

delete_timer

What is the difference between post database commit and post-form commit?

Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.

What is the form development process?

a) open template form 

b) Save as <your form>.fmb

c) Change the form module name as form name.

d) Delete the default blocks, window, and canvas

e) Create a window.

f) Assign the window property class to window

g) Create a canvas   

h) Assign canvas property class to the canvas

i) Assign the window to the canvas and canvas to the window

j) Create a data block       

k) Modify the form level properties. (sub class item à Text item)

l)  Modify the app_cusom package. In the program unit.

m) Modify the pre-form trigger (form level)

n) Modify the module level properties

o) Save and compile the form.

p) Place the .fmx in the server directory.

q) Register in the AOL

APPLICATION à FORMà FUNCTIONà MENU

What is template?

The TEMPLATE form is the required starting point for all development of new Forms. The TEMPLATE form includes platform–independent attachments of several Libraries.

APPSCORE :- It contains package and procedures that are required of all forms to support  the MENUS ,TOOLBARS.

APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.

FNDSQF  :- It contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.

CUSTOM :- It allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom    ( such as moving to another form and querying up specific records)

Where exactly you place your forms in APPS environment?

/apps/visappl/au/11.5.0/forms/US

What are the Different PLL’s used in Forms?

CUSTOM.pll

FNDSQF.pll

APPCORE.pll

APPCORE2.pll

appdaypk.pll

APPSTAND.pll

What are the triggers that can be modified during Forms Customization?

Pre-Forms

When-New-Form-Instance

Query_Find

Post-Form

Key-Clrfrm

Accept

What are the triggers that cannot be modified during Forms Customization?

STANDARD_ATTACHMENT

ZOOM

FOLDER_ACTION

KEY-HELP

KEY-EXIT

KEY-COMMIT

WHEN-WINDOW_CLOSED

CLOSE_WINDOW

FAQs in Oracle Forms – Part:2

FAQs in Oracle Forms – Part:2

 

What is a display item?

Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.

How many maximum number of radio buttons can you assign to a radio group?

Unlimited no of radio buttons can be assigned to a radio group.

Can you change the default value of the radio button group at run time?

No.

What triggers are associated with the radio group?

Only when-radio-changed trigger associated with radio group.

What are the various states of a form?

CHANGED: Indicates that the form contains at least one block with a Changed record.

NEW: Indicates that the form contains only New records.

QUERY: Indicates that a query is open.

What are the different objects that you cannot copy or reference in object groups?

objects of different modules

another object groups

individual block dependent items

program units

What are the different modals of a window?

Modeless windows

Modal windows

What are Modeless windows?

More than one Modeless window can be displayed at the same time, and operators can navigate among them if your application allows them to do so . On most GUI platforms, Modeless windows can also be layered to appear either in front of or behind other windows.

What are modal windows?

Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

How do you display console on a window ?

The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.

Can you have more than one content canvas attached with a window?

Yes. Each window you create must have at least one content canvas assigned to it. You can also create a window that has manipulate content canvas. At run time only one of the content canvas assign to a window is displayed at a time.

How many windows in a form can have console?

Only one window in a form can display the console, and you cannot change the console assignment at runtime.

What are the different window events activated at runtimes?

When_window_activated

When_window_closed

When_window_deactivated

When_window_resized

When a form call a pl/sql routine if there is an error in the pl/sql routine how do you place custom message in the form?

FND_MESSAGE.SHOW displays an informational message in a forms modal window or in a concurrent program log file only.
fnd_message.set_string(‘Message Text’);
fnd_message.show;
FND_MESSAGE.HINT to display a message in the forms status line and FND_MESSAGE.ERASE to clear the forms status line. FND_MESSAGE.HINT takes its message from the stack, displays the message, and then clears that message from the message stack.

List the built-in routines for controlling a window during run-time?

Find_window,

get_window_property,

hide_window,

move_window,

resize_window,

set_window_property,

show_View

What built-in is used for changing the properties of a window dynamically?

Set_window_property

What built-in is used for showing an alert during run-time?

Show_alert

Can you change alert messages at run-time?

Yes. By Set_alert_property.

What is the built-in function used for finding the alert?

Find_alert

What built-in routines are used to display editor dynamically?

Edit_text item

show_editor

What is the difference between COPY and NAME_IN ?

Copy is package procedure that writes values into a field. Name_in is a package function that returns the contents of the variable to which you apply.

Can you attach an lov to a field at run-time?

Yes. By Set_item_proprety

What is the built-in used to get and set lov properties during run-time?

Get_lov_property

Set_lov_property

Give built-in routines related to a record groups?

Create_group (Function)

Create_group_from_query(Function)

Delete_group(Procedure)

Add_group_column(Function)

Add_group_row(Procedure)

Delete_group_row(Procedure)

Populate_group(Function)

Populate_group_with_query(Function)

Set_group_Char_cell(procedure)

What is the built-in routine used to count the no of rows in a group?

Get_group_row_count

What are the built-ins that are used to attach an LOV programmatically to an item?

set_item_property

get_item_property

How many number of columns a record group can have?

A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of column does not exceed 64K.

What is the Maximum allowed length of Record group Column?

Record group column names cannot exceed 30 characters.

What are the built-ins used for Creating and deleting groups?

CREATE-GROUP (function)

CREATE_GROUP_FROM_QUERY(function)

DELETE_GROUP(procedure

What are the difference between Lov & List Item?

Lov is a property where as list item is an item. A list item can have only one column while a lov can have one or more columns.

What are the different display styles of list items?

Pop List

Text List

Combo box

What is Pop List?

The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.

What is a Text List?

The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values.

What is a Combo Box?

A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.

 

FAQs in Oracle Forms – Part:1

FAQs in Oracle Forms – Part:1

 

What are different types of modules available in oracle form?

Form module – a collection of objects and code routines.

Menu module – a collection of menus and menu item commands that together make up an application menu.

Library module – a collection of user named procedures, functions and packages that can be called from other modules in the application.

What are the default extensions of the files created by forms modules?

.fmb – form module binary

.fmx – form module executable

What are the default extensions of the files created by menu module?

.mmb, .mmx

What is data block & control block?

Data Block:-It is a Logical Collection of Items.
Control Block:-It is a data block which is totally independent of Database Table or View.

Note: – A data block can have control block items but not vice versa.

What is property class & visual attributes?

Property Class:

A property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the setting of any property in the class that makes sense for that object. Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a universal subclassing parent.

Visual Attributes:

Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application’s interface. Visual attributes can include the following properties: Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight Color and pattern properties: Foreground Color, Background Color, Fill Pattern. Every interface object has a Visual Attribute Group property that determines how the object’s individual visual attribute settings (Font Size, Foreground Color, etc.) are derived. The Visual Attribute Group property can be set to Default, NULL, or the name of a named visual attribute defined in the same module.

What is the difference between property class and visual attribute?

We can change Visual Attribute properties dynamically at runtime, but we cannot change Property class properties. When you inherit the both Visual Attribute properties and Property class properties to an item Visual Attribute properties overrides the Property class properties.

What is object group?

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module. Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

What is Record Group?

It is an internal memory data structure and a separate object in the form module. It’s main usage is to provide data to the LOV and dynamically to the list item and to perform client level validations.

What are the types of Record-Groups?

Static Record Group:- A static record group is not associated with a query; instead, you define its structure and row values at design time, and they remain fixed at runtime. Static record groups can be created and modified only at design time.

Query Based Record Group: – A query record group is a record group that has an associated SELECT statement. The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement. The records in a query record group are the rows retrieved by the query associated with that record group.

Non Query Record Group:- A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime. Non-query record groups can be created and modified only at runtime.

How to change Record Group dynamically?

By Using Non-Query Record Group.

What is Library?

A library is a collection of subprograms, including user-named procedures, functions, and packages. We can attach a library to any other form, menu or library module. It provides an easy method of reusing objects and enforcing standards across the entire development organization.

What are the types of canvases available in forms & what is the default canvas?

Content:-it is the base view of window which occupies the entire surface of window. It can have any no of canvases but at a time only one is visible.

Stacked:-It is always displayed above the content canvas because the content Canvas is the base view. It can have any no of stacked canvases and more than one stacked canvas can be displayed at a time.

Tool bar:- A toolbar canvas often is used to create toolbars for individual windows. There are two types of tool bars Horizontal and Vertical Tool bar Canvas. Horizontal tool bar canvases are displayed at the top of window and only one horizontal tool bar can be attached to a form module. Vertical tool bar is used to display top to bottom on the left side of the window.

Tab:-It is a collection of one or more tab pages. It is mainly used to display a large amount of related information a single dynamic form builder canvas object.

Content Canvas is the default canvas.

What are Alerts?

An alert is a modal window that displays a message notifying the operator of some application condition. There are three styles of alerts: Stop, Caution, and Note.
To display an alert, your application must execute the SHOW_ALERT built-in subprogram from a trigger or user-named subprogram. SHOW_ALERT is a function that returns a numeric constant.

Show_Alert(alert_name) Return NUMBER;

What are LOVs?

An LOV is a scrollable popup window that provides the end user with either a single or multi-column selection list. LOV values are derived from record groups. The LOVs in the Form Builder interface have the same auto-reduction and searching functionality as the LOVs you create for your own applications.

There are two built-in subprograms that can be used to display an LOV:
1) LIST_VALUES
2) SHOW_LOV

List the system variables related in Block and Field?

1. System.block_status

2. System.current_block

3. System.current_field

4. System.current_value

5. System.cursor_block

6. System.cursor_field

7. System.field_status

What is a master detail relationship?

A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.

What are the Various Master and Detail Relationships?

The various Master and Detail Relationship are:

a) NonIsolated :: The Master cannot be deleted when a child is existing.

b) Isolated :: The Master can be deleted when the child is existing.

c) Cascading :: The child gets deleted when the Master is deleted.

What are the procedures that will be created when a master details form created?

CHECK_PACKAGE_FAILURE
CHECK_ALL_MASTER_DETAILS
QUERY_MASTER_DETAILS

What is mouse navigate property of button?

When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to move the focus to the item when the operator activates the item with the mouse. When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulting validation) to move to the item when an operator activates the item with the mouse.

What is difference between PL/SQL library and object library in Forms?

PL/SQL library contains only pl/sql codes which can be used for standardizing the validations etc. in forms whereas Object Library is used to create, store, maintain, and distribute standard and reusable Form objects.

What are the different default triggers created when Master Deletes Property is set to Nonisolated?

On-Check-Delete-Master

On-Clear-Details

On-Populate-Details

What are the different default triggers created when Master Deletes Property is set to Cascade?

On-Clear-Details

On-Populate-Details

Pre-delete

What are the different default triggers created when Master Deletes Property is set to isolated?

On-Clear-Details

On-Populate-Details

What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?

Show_editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.

What is the “LOV of Validation” Property of an item? What is the use of it?

When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.

Calling a Report from a Form

Calling a Report from a Form

Using RUN_PRODUCT Built-in we can call a Report from a Form.

It will take 7 Parameters as follows

1) Product name :: REPORT/GRAPHICS

2) Module name  :: Report name

3) Communication Mode :: Synchronous /Asynchronous

SYN :: we cannot work with the Form until and unless you closes the Report.

ASYN:: We can work with Form & Reprot (Navigate)

4) Exec :: Batch/Run time

Batch :: it will fetch all records at a time

Run time :: It will fetch only one Record at a time

5) File name :: database/Filesystem

6) Parameters :: Report Parameters

7) Display :: For Graphics (Mostly null)

Ex :: If we have :p_deptno parameter then we can call as Follows

RUN_PRODUCT (‘REPORT’,’C:\TEST1.rep’,SYN,BATCH,DATABASE,p_deptno,’ ‘);

 

Steps to create a XML Publisher Report

Steps to create a XML Publisher Report

 

1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.

2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.

3] Set the user parameter as p_conc_request_id.

4] Add the default values to the Before Report and After Report triggers(not mandatory)

5] Ftp the Report to the Cust_Top/Report/Us.

6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.

7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.

8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.

9] Design the template in Ms Word(Using the .Rtf file).

10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.

11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.

12] Now go to the Responsibility and run the request.

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. The supplier (or External) bank account information are stored in the table called IBY_EXT_BANK_ACCOUNTS. The bank and bank branches information are stored in the table HZ_PARTIES. They are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank. Bank sites and Location information are stored in tables: HZ_PARTY_SITES and HZ_LOCATIONS. The bank_id and branch_id fields of IBY_EXT_BANK_ACCOUNTS table link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table (IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id).

Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. Click on Banking Details and then choose Create. After you have created the bank account, you can assign the bank account to the supplier site.

When the Bank is assigned to Vendors then it will be updated in a table called HZ_CODE_ASSIGNMENTS. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. IBY_EXTERNAL_PAYEES_ALL stores payment-related attributes for the funds disbursement payment process for external party payees.

Queries:

SELECT  aps.vendor_name "VERDOR NAME",
        apss.vendor_site_code "VENDOR SITE CODE",
        ieb.bank_name "BANK NAME",
        iebb.bank_branch_name "BANK BRANCH NAME",
        iebb.branch_number "BRANCH NUMBER",
        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM    ap.ap_suppliers aps,
        ap.ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts ieba,
        apps.iby_account_owners iao,
        apps.iby_ext_banks_v ieb,
        apps.iby_ext_bank_branches_v iebb
WHERE   aps.vendor_id = apss.vendor_id
        and iao.account_owner_party_id = aps.party_id
        and ieba.ext_bank_account_id = iao.ext_bank_account_id
        and ieb.bank_party_id = iebb.bank_party_id
        and ieba.branch_id = iebb.branch_party_id
        and ieba.bank_id = ieb.bank_party_id;

SELECT party_supp.party_name supplier_name
,      aps.segment1          supplier_number
,      ass.vendor_site_code  supplier_site
,      ieb.bank_account_num
,      ieb.bank_account_name
,      party_bank.party_name bank_name
,      branch_prof.bank_or_branch_number bank_number
,      party_branch.party_name branch_name
,      branch_prof.bank_or_branch_number branch_number
FROM   hz_parties party_supp
,      ap_suppliers aps
,      hz_party_sites site_supp
,      ap_supplier_sites_all ass
,      iby_external_payees_all iep
,      iby_pmt_instr_uses_all ipi
,      iby_ext_bank_accounts ieb
,      hz_parties party_bank
,      hz_parties party_branch
,      hz_organization_profiles bank_prof
,      hz_organization_profiles branch_prof
WHERE  party_supp.party_id = aps.party_id
AND    party_supp.party_id = site_supp.party_id
AND    site_supp.party_site_id = ass.party_site_id
AND    ass.vendor_id = aps.vendor_id
AND    iep.payee_party_id = party_supp.party_id
AND    iep.party_site_id = site_supp.party_site_id
AND    iep.supplier_site_id = ass.vendor_site_id
AND    iep.ext_payee_id = ipi.ext_pmt_party_id
AND    ipi.instrument_id = ieb.ext_bank_account_id
AND    ieb.bank_id = party_bank.party_id
AND    ieb.bank_id = party_branch.party_id
AND    party_branch.party_id = branch_prof.party_id
AND    party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
,        ass.vendor_site_code;

AutoInvoice in Oracle Apps R12

AutoInvoice in Oracle Apps R12

AutoInvoice is a powerful, flexible tool you can use to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos, and on–account credits in Oracle Receivables. You use a custom feeder program to transfers transaction data from an external system into the AutoInvoice interface tables. AutoInvoice then selects data from the interface tables and creates transaction in Receivables. Receivables rejects transactions with invalid information to ensure the integrity of your data. You can run AutoInvoice together with Customer Interface or separately.

Oracle Receivables can create invoices from the following Oracle applications:  Oracle Order Management, Oracle Projects,  Oracle Service, Oracle Property Manager.
Oracle Receivables can create invoices from the following non-Oracle applications:  Legacy system (for transaction history),  Non-Oracle billing applications, Non-Oracle order entry applications.

Oracle Receivables uses three interface tables for AutoInvoice:

1. RA_INTERFACE_LINES
2. RA_INTERFACE_DISTRIBUTIONS
3. RA_INTERFACE_SALESCREDITS

AutoInvoice transfers transaction data from the above three interface tables into the following Receivables tables:

• RA_BATCHES_ALL
• RA_CUSTOMER_TRX _ALL
• RA_CUSTOMER_TRX_LINES _ALL
• RA_CUST_TRX_LINE_GL_DIST_ALL
• RA_CUST_TRX_LINE_SALESREPS_ALL
• AR_PAYMENT_SCHEDULES_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL
• AR_ADJUSTMENTS_ALL

RA_INTERFACE_LINES:

• This table contains information relating to all transactions to be processed by AutoInvoice.
• Transactions include invoices, debit memos, credit memos, and on-account credits.
• Each record contains line, tax, freight, or finance charges information.
• The Line_Type field identifies the type of information contained in the record.
• A record can be a parent record: Line, Header Freight, or Charges; or a child record: Tax or linelevel Freight.
• A child record is linked to the parent record using the Link-To Transaction flexfield.

RA_INTERFACE_DISTRIBUTIONS:

• This table contains accounting distributions to be used by the transactions defined in RA_INTERFACE_LINES.
• Accounts defined in this table override any accounts created using AutoAccounting.
• You can choose to pass some or all account information to AutoInvoice. Any accounts that are not passed will be derived using AutoAccounting.
• Records in this table are linked to records in the RA_INTERFACE_LINES table using the Transaction flexfield.
• Not required if AutoAccounting determines GL distributions.

RA_INTERFACE_SALESCREDITS:

• This table contains all sales credit information for the transactions in the RA_INTERFACE_LINES table.
• The two tables are linked using the Transaction flexfield.
• Not required if not tracking sales credit.

What occurs during auto-invoice:

1. Populates the RA_Interface_Lines, RA_Interface_distribution and RA_Interface_salescredit tables.
2. Lines are grouped and ordered by the grouping rule and line ordering line. Grouping rules are mandatory and determine how transaction lines are grouped into transactions. Optionally, you can use line-ordering rules to determine the order in which lines are displayed on a transaction.
3. Tax, freight, commitments and credit memos are linked to transaction line by transaction line, transaction reference and transaction link-to descriptive flex fields. To uniquely identify imported transactions and link the tax, freight, commitments and credit memos, define the Transaction flexfields.
4. GL date is determined.
5. GL accounting code combinations are assigned using auto accounting rule.
6. TAX is determined.
7. All transactions are batched.
8. Validated lines are used to create the transaction.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.

Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

Transaction flexfields:

• Transaction flexfields are descriptive flexfields that AutoInvoice uses to uniquely identify transaction lines.
• Because they are unique for each transaction line, they can also be used to reference and link to other lines.
• Receivables lets you determine how you want to build your transaction flexfield structure and what information you want to capture.
• Define a flexfield for each import source. Specify which one to use during import.

Types of transaction flexfields:

• Invoice Header (optional): Specifies invoice header information
• Line (required): Uniquely identifies invoice lines
• Link-To (optional): Link tax and freight to invoice lines
• Reference (optional): Links credit memos to transactions

What is inside AutoInvoice?

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

• Master (RAXMTR)
• Import (RAXTRX)
• Purge (RAXDEL)

1] Auto Invoice Master program:

Selects and marks records in the interface tables to  be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

• Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables.
• Marks interface records for processing by marking request_id.
• Submits multiple workers for Parallel Processing by creating instances for request.

2] Auto Invoice Import Program:

Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

• Workhorse of Auto invoice
• Validates data
• Inserts records
• Deletes interface data
• Only when system option purge set to ‘Y’

3] Auto Invoice Purge Program:

Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run, and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

AutoInvoice Exception Handling:

Records that fail validation are called ‘Exceptions’

• Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL.
• Errors can be corrected in the Exception Handling window.
• Once corrections are made, Auto invoice must be resubmitted.
• Records that pass validation get transferred to Receivables tables.

AutoInvoice Exception Handling Windows:

• Interface Exception window displays exception messages associated with all invalid records.
• Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors.
• The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window.
• Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error.
• Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button.
• Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column.

Supplier data in R12

Supplier data in R12

The below query will tell you about the various tables used to store supplier information and their links with different modules like TCA, AP, E-Business Tax and Payment.

SELECT hp.party_id
      ,hp.party_number
      ,hp.party_name
      ,hp.party_type
      ,hp.orig_system_reference
      ,hp.tax_reference
      ,hpua.party_usg_assignment_id
      ,hpua.party_usage_code
      ,hpua.effective_start_date
      ,hpua.effective_end_date
      ,hop.organization_profile_id
      ,hop.organization_name_phonetic
      ,hop.organization_type
      ,hps.party_site_id
      ,hps.party_site_number
      ,hps.orig_system_reference party_site_reference
      ,hps.party_site_name
      ,aps.vendor_id
      ,aps.vendor_name
      ,aps.vendor_name_alt
      ,aps.segment1
      ,aps.vendor_type_lookup_code
      ,aps.payment_currency_code
      ,aps.invoice_currency_code
      ,aps.hold_all_payments_flag
      ,aps.vat_registration_num
      ,aps.match_option
      ,apss.vendor_site_id
      ,apss.vendor_site_code
      ,apss.purchasing_site_flag
      ,apss.rfq_only_site_flag
      ,apss.pay_site_flag
      ,apss.address_line1
      ,apss.address_line2
      ,apss.zip
      ,apss.country
      ,apss.bill_to_location_id
      ,apss.accts_pay_code_combination_id
      ,iep.ext_payee_id
      ,iep.payment_function
      ,iep.org_type
      ,iep.default_payment_method_code
      ,iep.remit_advice_delivery_method
      ,iep.remit_advice_fax
      ,zxtp.party_type_code
      ,zxtp.rep_registration_number

FROM   hz_parties hp
      ,hz_party_usg_assignments hpua
      ,hz_organization_profiles hop
      ,hz_party_sites hps
      ,ap_suppliers aps
      ,ap_supplier_sites_all apss
      ,iby_external_payees_all iep
      ,zx_party_tax_profile zxtp

WHERE  1 = 1
AND    hp.party_id        = hpua.party_id
AND    hp.party_id        = hop.party_id
AND    hp.party_id        = hps.party_id
AND    hp.party_id        = aps.party_id
AND    aps.vendor_id      = apss.vendor_id
AND    hps.party_site_id  = apss.party_site_id
AND    hp.party_id        = iep.payee_party_id
AND    apss.vendor_site_id= iep.supplier_site_id
AND    hps.party_site_id  = iep.party_site_id
AND    hp.party_id        = zxtp.party_id
AND    hp.party_id        = &party_id;