Oracle Apps Technical

Saturday, 25 July 2015

Important Aggregate Function Queries

Click on the following Link
http://www.folkstalk.com/2011/12/sql-queries-interview-questions-oracle.html

Is Triggers allow the COMMIT ????

No,Because Trigger is not a seperate Transaction. Its a part of an existing transaction.

Consider this,

1. You have a AFTER UPDATE trigger on table T. This trigger will Write into a audit table when ever an update occurs on table T.

2. For a moment lets assume that We are able to commit inside a trigger.

3. Now i UPDATE T. So my trigger writes into the audit table and commits the changes.

4. Now i encounter some error in the next statement and my UPDATE rollbacks.

So what now? I have a audit table that says I have updated table T. But acutally i have not updated anything.

And thats why Oracle does not permit you to COMMIT within a trigger.

But if you are OK with this kind of a result then you can define your Trigger with PRAGMA AUTONOMOUS_TRANSACTION. This will allow you to commit inside a trigger.

But be very carefull when you use AUTONOMOUS_TRANSACTION. Wrong use of AUTONOMOUS_TRANSACTION can hurt you big time.

Friday, 24 July 2015

Oracle Forms Interview Questions & Answers

What are the Various Master and Detail Relation ships.
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 master-detail triggers?
On-heck_delete_master
On_clear_details
On_populate_details These are automatically created when you create Master-Details block.

What are the types of Blocks in Forms?
Base Table block - based on database table/views
Control Block - non-database items are placed here like Calculation values,buttons,checkbox etc.

What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

Can a property clause itself be based on a property clause?
Yes

What are the different windows events activated at runtimes?
When_window_activated
When_window_closed
When_window_deactivated
When_window_resized
Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.

What are the trigger associated with image items?
When-image-activated fires when the operators double clicks on an image itemwhen-image-pressed fires when an operator clicks or double clicks on an image item

What is trigger associated with the timer?
When-timer-expired.

What is the difference between CALL_FORM, NEW_FORM and OPEN_FORM?
CALL_FORM: start a new form and pass control to it. The parent form will be suspended until the called form is terminated.
NEW_FORM: terminate the current form and replace it with the indicated new form. The old form's resources (like cursors and locks) will be released.
OPEN_FORM: Opens the indicated new form without suspending or replacing the parent form.

When a form is invoked with call_form, Does oracle forms issues
a save point?
Yes

What is new_form built-in?
When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.

What are visual attributes?
Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.

Can one issue DDL statements from Forms?
DDL (Data Definition Language) commands like CREATE, DROP and ALTER are not directly supported from Forms because your Forms are not suppose to manipulate the database structure.
A statement like CREATE TABLE X (A DATE); will result in error:
Encountered the symbol "CREATE" which is a reserved word.
However, you can use the FORMS_DDL built-in to execute DDL statements. Eg:
FORMS_DDL('CREATE TABLE X (A DATE)');

Can one execute dynamic SQL from Forms?
Yes, use the FORMS_DDL built-in or call the DBMS_SQL database package from Forms. Eg:
FORMS_DDL('INSERT INTO X VALUES (' || col_list || ')');
Just note that FORMS_DDL will force an implicit COMMIT and may de-synchronize the Oracle Forms COMMIT mechanism.

What is the difference between the following statements?
Form A: Insert into emp(ename) values ('MK Maran');
Form B: FORMS_DDL('insert into emp(ename) values('||''MK Maran')');

User have to commit the form manually for Form A
Once the Form B statement executes, it will be implicitly commited

What are the vbx controls?
Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the ms visual basic environments and include such items as sliders, rides and knobs.

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.

How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code

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, lov can have one or more columns

What is the difference between static and dynamic lov
The static lov contains the predetermined values while the dynamic lov contains values that come at run time

What are the different types of Record Groups?
Query Record Groups
NonQuery Record Groups
State Record Groups

What are the different display styles of list items?
Text_list
Pop_list
Combo box

Can on bypass the Oracle login screen?
The first thing that the user sees when using runform is the Oracle logon prompt asking them for their username, password, and database to connect to. You can bypass this screen or customise it by displaying your own logon screen.

Eg:

ON-LOGIN Form-Level Trigger

declare
uname varchar2(10);
pass varchar2(10);
con_string varchar2(30);

begin
uname := 'scott';
pass :='tiger';
con_string='orcl';

logon(uname, pass||'@'||con_string);
end;

What are parameters?
Parameters provide a simple mechanism for defining and setting the valuesof inputs that are required by a form at startup. Form parameters are variables of type char,number,date that you define at design time.

What are 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.

Can one Maximize/ Minimize a Window in Forms?
On MS-Windows, Forms run inside a Windows Multiple-Document Interface (MDI) window. You can use SET_WINDOW_PROPERTY on the window called FORMS_MDI_WINDOW to resize this MDI (or any other named) window. Examples:
set_window_property(FORMS_MDI_WINDOW, WINDOW_STATE, MINIMIZE);
set_window_property(FORMS_MDI_WINDOW, POSITION, 7, 15);
set_window_property('my_window_name', WINDOW_STATE, MAXIMIZE);

What are the different modals of windows?
Modalless windows
Modal 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.

Dynamically enabling and disabling Concurrent Program Parameters

Suppose a concurrent program has three parameters – ParamA, ParamB and ParamC. If the value for ParamA is ‘ENABLE_B’, then ParamB should be enabled and if the value fo ParamA is ‘ENABLE_C’, then ParamC should be enabled. Assume that the values for the second and third parameters are fetched from a table.
The first approach that might come immediatly to mind is to setup the three parameters ParamA, ParamB and ParamC in the manner and link them up using $FLEX$:
ParamA has value set VS1 attached to it. VS1 is of type Independent and has the values ‘ENABLE_B’ and ‘ENABLE_C’.
ParamB has value set VS2 attached to it. VS2 is of type Table and in the Where/Order By clause the condition :$FLEX$.ParamA=’ENABLE_B’ is added.
ParamC has value set VS3 attached to it. VS3 is of tye Table and in the Where/Order By clause the condition :$FLEX$.ParamA=’ENABLE_C’ is added.
When the program is run, both parameters are initially disabled.

But the moment we select a value for the first parameter, ParamA, both ParamB and ParamC get enabled thus defeating our purpose. The only consolation, if it may be so called, is that the list of value for ParamC contains no values.

The correct approach is to use two additional dummy parameters to enable or disable the second and third parameters. We will look into this appoach in more details.
1. ParamA has value set XXSB1_VS1 attached to it. The value set XXSB1_VS1 is of type Independent and contains two values ‘ENABLE_B’ and ‘ENABLE_C’

2. The dummy parameter ParamA1 has a seeded character value set attached to it. Note that the Displayed checkbox is unchecked. Its default value is derived from the SQL statement

select decode(:$FLEX$.ParamA,'ENABLE_B','Y', null) from dual

The value for this parameter will be ‘Y’ if ParamA has the value ‘ENABLE_B’ and null otherwise

3. ParamB has value set XXSB1_VS2 attached to it.

4. Value set XXSB1_VS2 is of type Table and in the Where/Order By clause the condition :$FLEX$.ParamA1=’Y’ is added

5. The dummy parameter ParamB1 has a seeded character value set attached to it. Note that the Displayed checkbox is unchecked. Its default value is derived from the SQL statement

select decode(:$FLEX$.ParamA,'ENABLE_C','Y', null) from dual

The value for this parameter will be ‘Y’ if ParamA has the value ‘ENABLE_C’ and null otherwise

6. ParamC has value set XXSB1_VS3 attached to it.

7. Value set XXSB1_VS3 is of type Table and in the Where/Order By clause the condition :$FLEX$.ParamB1=’Y’ is added.

That is it, all the parameters have now been set up. When the program is run, the second and third parameters are initially disabled like in the previous approach.

Depending on the value of the first parameter, the second and third parameters are enabled or disabled.

The second approach works while the first does not because the Where/Order By clause for one of the value sets always translates to null=’Y’ which cannot be equated and hence the parameter to which it is attached remains disabled.

Tuesday, 21 July 2015

Forms Persionalization

Form Personalization with Examples

Form Personalization:

Form Personalization is a feature in Oracle Apps that allows us to change the behavior of form based screens.
It can be moved easily through FNDLOAD from one instance to another, It can be restricted at Site/User/Responsibility Level.

What can be done through Form Personalization?

1. Zoom from One form to another.

2. Pass data from one form to another through global variables.

3. Enable/Disable/Hide fields dynamically.

4. Launch URL directly from oracle form.

5. Call PL/SQL procedure.

Components of Form Personalization:

How To make a Field Mandatory in the Form:

Go for Any Perticular Form If you want to personalize, Open that form

Go as specified Navigation in Help ->Diagnostics-> Custom Code -> Personalize

Once Open That let we create 10.1 Seq For Make Field Mandatory (Make sure that This should be Enabled).

Condition->Trigger Event we are selection as WHEN-NEW-FORM-INSTANCE.

Object_type:Item(Column)

Target_Object :MTL_SYSTEM_ITEMS.LIST_PRICE_PER_UNIT_MIP(Which field you want to make it mandatory select that column as target Object).

Property_Name:Required

How to perform calculations in the field:

Calculations will be done through per each Record so we selected as WHEN-NEW-RECORD-INSTANCE

Let we calculate this in attirbute_13 in PO_LINES

Meaning of Special fields in Form Personalization:

• Menu(1-15) will be in tools

• Populate tools menu (SPECIAL 1-15)

• Populate reports menu (SPECIAL 16-30)

• Populate actions menu (SPECIAL 31-45)

• It is recommended to use Menu before Tools

Various components used In Form Personalization:

Trigger Event - These are predefined event. Based on the value chosen the function will execute only when that particular event triggers on the form.

Processing Mode - Query mode or Not in Query mode or Both

Context - At what level like user or Responsibility level

Menu - this is used to call one form from another. it is used to implement the zoom functionality. There are 45 menu entries which are used to call one form from current form.

Built In - It allows you to call Oracle standard built-in and Functions