Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;
Monday, December 19, 2011
How to make an Oracle Report Type concurrent program produce an excel friendly output?
Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;
Can you do fnd_request.submit_request from SQL Plus/Toad in Oracle?
You will need to initialize the global variables first using
fnd_global.apps_initialize(user_id => :user_id,
resp_id => :resp_id,
resp_appl_id => :resp_appl_id,
security_id => :security_id,
server_id => :server_id);
By doing the above, your global variables upon which Concurrent Managers depend upon will be populated. This will be equivalent to logging into Oracle Apps and submitting the concurrent request from a responsibility.
How do you link a Host type concurrent program to Concurrent Manager?
Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO
How to make concurrent program end with warning?
If the concurrent program is of type PL/SQL, you can assign a value of 1 to the retcode OUT Parameter.
For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");
In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?
You will need to do the below steps
Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
Enter below text( change colours as needed)
cd $OA_HTML/cabo/styles/cache
Take a backup of all the css files.
Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps Self Service, the Framework will rebuild the css file if found missing for your browser.
In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.
Which table is used to provide drill down from Oracle GL into sub-ledger
GL_IMPORT_REFERENCES column GL_SL_LINK_ID
Can we define cursor inside package if so how to call cursors inside the package?
Yes, we can. call using packagename.cursorname.
Ex: OPEN XX_INVOICE_VAL_PKG.c_eligible_invoice
LOOP XX_INVOICE_VAL_PKG.c_eligible_invoice..
END LOOP;
Ex: OPEN XX_INVOICE_VAL_PKG.c_eligible_invoice
LOOP XX_INVOICE_VAL_PKG.c_eligible_invoice..
END LOOP;
When you query Multi-Org tables in SQL*Plus or any tool (like TOAD), we get zero rows, though actually those tables got data? What is the reason? What do you do then?
Nee to set org context to the current user session.
Single-org:
Execute mo_global.set_policy_context('S',&org_id);
Multi-org:
Begin
FND_GLOBAL.apps_initialize(
l_user_id, -- User id
l_resp_id, -- Responsibility Id
200); -- Application Id
MO_GLOBAL.init('SQLAP');
End;
How do you stop inserting records after 50 records are inserted from flat file through sql*loader
This can be done using option LOAD
OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
item_number "trim(:item_number)"
, vendor_name "trim(:vendor_name)"
, vendor_site_name "trim(:vendor_site_name)"
, supplier_item "trim(:supplier_item)"
, process_flag Constant 'UNPROCESSED'
)
In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.
These options can also be given with sqlldr command as follows..
sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5 skip=1
Can we set Index for primary key? If so what is the name of the Index?
Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. For example, if you create a table foo as follows:
create table foo (a int primary key,
b varchar(20) unique);
Oracle will automatically create one index on foo.a and another on foo.b. Note that you cannot drop indexes for UNIQUE and PRIMARY KEY attributes. These indexes are dropped automatically when you drop the table or the key constraints (see the section on Constraints).
To find out what indexes you have, use
select index_name from user_indexes;
USER_INDEXES is another system table just like USER_TABLES. This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. You might also see weird names of the indexes created by Oracle for UNIQUE and PRIMARY KEY attributes, but you will not be able to drop these indexes.
How do you track a Request Set? If an error occurs in the first program in a Request Set, I need to control the execution of other programs in the Request Set, How do you achieve this?
While creating Request Set, you can select execution sequance as Parallel or Sequancially. In this case u select as Sequance, so one fails other wont be exceuted.
Decode vs Case
Example for Decode and Case:
SQL> SELECT ename,
2 DECODE(deptno, 10, 'ACCOUNTING',
3 20, 'RESEARCH',
4 30, 'SALES',
5 40, 'OPERATIONS',
6 'UNKNOWN') AS department
7 FROM emp
8 WHERE rownum < 4
9 /
SQL> SELECT ename,
2 CASE deptno
3 WHEN 10 THEN 'ACCOUNTING'
4 WHEN 20 THEN 'RESEARCH'
5 WHEN 30 THEN 'SALES'
6 WHEN 40 THEN 'OPERATIONS'
7 ELSE
8 'UNKNOWN'
9 END AS department
10 FROM emp
11 WHERE rownum < 4
12 /
Difference:
1.
DECODE works with expressions which are scalar values.
CASE can work with predicates and subqueries in searchable form
SQL> select ename,
2 case
3 when ename in ('KING','SMITH','ALLEN') then
4 'Managers'
5 when exists (select 1 from dept where deptno = emp.deptno and deptno = 10) then
6 'Guy from 10th'
7 else
8 'Another person'
9 end blah_blah
10 from emp
11 /
2.
CASE executes faster in the optimizer than does DECODE.
3.
DECODE will return "true" if you compare NULL to NULL. CASE will not.
DECODE(NULL, NULL, 1, 0)
will return '1'.
CASE NULL
WHEN NULL THEN 1
ELSE 0
END
will return '0'.
You would have to write it as:
CASE
WHEN NULL IS NULL THEN 1
ELSE 0
END
will return '1'
4.
DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure
SQL> Declare
1 a varchar2 := 'ONE';
2 Begin
3 procedure_01(case :a when 'ONE' then 1 else 0 end);
4 End;
5 /
The number = 1
SQL> SELECT ename,
2 DECODE(deptno, 10, 'ACCOUNTING',
3 20, 'RESEARCH',
4 30, 'SALES',
5 40, 'OPERATIONS',
6 'UNKNOWN') AS department
7 FROM emp
8 WHERE rownum < 4
9 /
SQL> SELECT ename,
2 CASE deptno
3 WHEN 10 THEN 'ACCOUNTING'
4 WHEN 20 THEN 'RESEARCH'
5 WHEN 30 THEN 'SALES'
6 WHEN 40 THEN 'OPERATIONS'
7 ELSE
8 'UNKNOWN'
9 END AS department
10 FROM emp
11 WHERE rownum < 4
12 /
Difference:
1.
DECODE works with expressions which are scalar values.
CASE can work with predicates and subqueries in searchable form
SQL> select ename,
2 case
3 when ename in ('KING','SMITH','ALLEN') then
4 'Managers'
5 when exists (select 1 from dept where deptno = emp.deptno and deptno = 10) then
6 'Guy from 10th'
7 else
8 'Another person'
9 end blah_blah
10 from emp
11 /
2.
CASE executes faster in the optimizer than does DECODE.
3.
DECODE will return "true" if you compare NULL to NULL. CASE will not.
DECODE(NULL, NULL, 1, 0)
will return '1'.
CASE NULL
WHEN NULL THEN 1
ELSE 0
END
will return '0'.
You would have to write it as:
CASE
WHEN NULL IS NULL THEN 1
ELSE 0
END
will return '1'
4.
DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure
SQL> Declare
1 a varchar2 := 'ONE';
2 Begin
3 procedure_01(case :a when 'ONE' then 1 else 0 end);
4 End;
5 /
The number = 1
What is $FLEX$ and $PROFILES$?
$FLEX$ and $PROFILES$ are Special Variables in oracle Apps environment and are used to hold values at runtime. Whenever we are working with value sets, we will be using both of these for modifying the data fetched, for basing the values of a parameter on the other parameter and also fetching the Profile Option values.
To segregate this based on the functionality
$FLEX$: Used for basing the value of a parameter on another parameter.
$PROFILES$: used for fetching the data stored in the specified profile option value which is currently active.
Where is it used?
Both these variables are used in the Parameter form of a Concurrent Program and are used at the Where Clause window in the value set of Table type.
Syntax:
:$FLEX$.previous_value_set_name
Important:
* $FLEX$ must always be in capitals.
* A ':' must precede the declaration of $FLEX$.
* The previous value set name must have already been assigned and saved on a different parameter.
:$PROFILES$.Profile_option_name
Important:
* $PROFILES$ must be always in capitals.
* ':' must always precede the declaration.
* Profile option name is the Profile Name and not to be confused with the User profile Name.
To segregate this based on the functionality
$FLEX$: Used for basing the value of a parameter on another parameter.
$PROFILES$: used for fetching the data stored in the specified profile option value which is currently active.
Where is it used?
Both these variables are used in the Parameter form of a Concurrent Program and are used at the Where Clause window in the value set of Table type.
Syntax:
:$FLEX$.previous_value_set_name
Important:
* $FLEX$ must always be in capitals.
* A ':' must precede the declaration of $FLEX$.
* The previous value set name must have already been assigned and saved on a different parameter.
:$PROFILES$.Profile_option_name
Important:
* $PROFILES$ must be always in capitals.
* ':' must always precede the declaration.
* Profile option name is the Profile Name and not to be confused with the User profile Name.
Saturday, December 5, 2009
EMU and NCU Currencies
EMU Currencies: The euro and the national currency of each state in the EMU.
NCU: National Currency Unit. Any EMU currency other than the euro.
In the Invoice Workbench, when an invoice requires an exchange rate between an NCU currency and a non-EMU currency, you enter only the exchange rate between the non-EMU currency and the euro. Since the rate between the euro and each NCU is fixed, the system then calculates and displays the complete exchange rate between the NCU and the non-EMU currency. This functionality is enabled when the following profile option is set to no: Currency: Allow Direct EMU/Non-EMU User Rates.
NCU: National Currency Unit. Any EMU currency other than the euro.
In the Invoice Workbench, when an invoice requires an exchange rate between an NCU currency and a non-EMU currency, you enter only the exchange rate between the non-EMU currency and the euro. Since the rate between the euro and each NCU is fixed, the system then calculates and displays the complete exchange rate between the NCU and the non-EMU currency. This functionality is enabled when the following profile option is set to no: Currency: Allow Direct EMU/Non-EMU User Rates.
Sunday, November 29, 2009
Oracle Apps FAQ-2
1. How to register a report?
a) Register concurrent à Program à executable
b) Register concurrent à program à Define
c) Attach it to appropriate request group security à responsibility à Request
d) FTP RDF in respective top/report/US
2. How to CREATE a VALUE SET? What are the different types of value sets?
Enter Application à Validation à Set
Types of value set
a) Independent
b) Dependent
c) Table
d) Pair
e) SPECIAL DELIVERY translate dependent
f) Translatable independent
3. Name few types of execution method in concurrent program executable
a) Oracle reports
b) Sql * Plus
c) Host
d) Java stored procedure
e) Pl/sql stored procedure
f) SQL * Loader
g) Spawned
h) Java CCP
i) Multilanguage function
j) Immediate
k) Request set stage function
4. How to register a form?
a) Define Application à form
b) Define Application à function à give link to form defined in step I
c) Go to Application à menu à Attach function to menu
d) FTP from to AU_TOP, generate it and copy to respective TOP
5. What are the steps to develp a form>
a) Copy appstand.fmh, Template.FMB, required pll in local directory
b) Change the registry for pll path. Save template.fmb as the new form name from name and start developing the form
c) FTP form in AU_TOP and generate fmb using f60gen and copy.fmx in respective top/forms/us directory
6. what is the use of custom.pll?
Custom.pll is used for customizations such as form, enforcing business riles and disabling fields that are not required for site without modifying standard apps forms.
7. How to PROGRAMATICALLY submit the request?
a) with the help of standard API Find_Request. Submit_request
8. What is request set?
With the help of request set we can submit several requests together using multiple execution paths. Its collection of concurrent programs like reports procedures grouped together.
9. What are user exists in reports? What are user exist available in apps?
a) A user exit is a program written and then linked in to the report builder executable user exist are written when content need to be passed from report builder to that pgm, which performs some function and then returns control to report builder.
1. FND SRWINIT, FND SRWEXIT, FND FORMAT_CURRENCY, FND FLEXIDVAL, FND FLEXSQL.
10. What is the API used for file I/o operation ? or which API is used to write to request log and request output?
a) fnd_file.put_line (Fnd_file.log, ‘message’);
b) fnd_file.put_line (fnd_file.out. ‘message’);
11. how do I programmatically capture application user_id?
Fnd_profile.value (‘user_id’) or fnd_global.user_id.
12. what are flexfields?
A flexfield is a field made up of segments . each segment has a name and a set of valid values. There are two types of ff’s: key ff, DFF
13. Which are the 2 parameters required to be defined when a program is registered as pl/sql stored procedure
ERRBUF, RETCODE
14. can we register executable/concurrent program programmatically then how?
Yes we can. It can be done with standard package fnd_program, fnd_program.executable, fnd_program.register.
15. what changes need to be made if a setup is changed from a single org to multi org?
Org_id should be added as a parameter in a report and report should be run for specific org_id only
16. What are sub functions? How is it different from from?
A sub function is a securable subset of a forms functionally
a) forms appear in a navigator window and can be navigated to sub functions do not appear in the navigator window and cant be navigated to
b) forms can exist on this own sub functions can only be called by ____ embodied within a form, they cant exist on their own
17. what is message dictionary?
Message dictionary allows defining messages which can be used in application without hard coding them into forms or programs.
18. What is the token in concurrent à program à parameters window?
For a parameter in an oracle report program, they keyword is parameter specified here. The value is case sensitive for example P_CUSTOMER_NO
19. What are different validation defaults types and default value in current à program à parameter window?
a) constant
b) profile
c) SQL statement
d) Segment
20. I have a concurrent program that involves lot of inserts and updates on a huge basis where do I specify rollback segment etc
concurrent ->program->session control
21. How do I change the priority of my concurrent program?
Log on as system admin concurrent à program à program à priority, enter numerical value in this field so it will take the request which has higher priority.
22. What is incompatibility?
When a program or list of programs is defined as incompatible means that defined program is not compatible with incompatible program list and cant run simultaneously in same conflict domain.
23. What is data group?
A data group defines the mapping b/w oracle applications and oracle ID’s. A data group determines oracle database accounts a responsibilities forms, concurrent programs, and reports connect to
a) Register concurrent à Program à executable
b) Register concurrent à program à Define
c) Attach it to appropriate request group security à responsibility à Request
d) FTP RDF in respective top/report/US
2. How to CREATE a VALUE SET? What are the different types of value sets?
Enter Application à Validation à Set
Types of value set
a) Independent
b) Dependent
c) Table
d) Pair
e) SPECIAL DELIVERY translate dependent
f) Translatable independent
3. Name few types of execution method in concurrent program executable
a) Oracle reports
b) Sql * Plus
c) Host
d) Java stored procedure
e) Pl/sql stored procedure
f) SQL * Loader
g) Spawned
h) Java CCP
i) Multilanguage function
j) Immediate
k) Request set stage function
4. How to register a form?
a) Define Application à form
b) Define Application à function à give link to form defined in step I
c) Go to Application à menu à Attach function to menu
d) FTP from to AU_TOP, generate it and copy to respective TOP
5. What are the steps to develp a form>
a) Copy appstand.fmh, Template.FMB, required pll in local directory
b) Change the registry for pll path. Save template.fmb as the new form name from name and start developing the form
c) FTP form in AU_TOP and generate fmb using f60gen and copy.fmx in respective top/forms/us directory
6. what is the use of custom.pll?
Custom.pll is used for customizations such as form, enforcing business riles and disabling fields that are not required for site without modifying standard apps forms.
7. How to PROGRAMATICALLY submit the request?
a) with the help of standard API Find_Request. Submit_request
8. What is request set?
With the help of request set we can submit several requests together using multiple execution paths. Its collection of concurrent programs like reports procedures grouped together.
9. What are user exists in reports? What are user exist available in apps?
a) A user exit is a program written and then linked in to the report builder executable user exist are written when content need to be passed from report builder to that pgm, which performs some function and then returns control to report builder.
1. FND SRWINIT, FND SRWEXIT, FND FORMAT_CURRENCY, FND FLEXIDVAL, FND FLEXSQL.
10. What is the API used for file I/o operation ? or which API is used to write to request log and request output?
a) fnd_file.put_line (Fnd_file.log, ‘message’);
b) fnd_file.put_line (fnd_file.out. ‘message’);
11. how do I programmatically capture application user_id?
Fnd_profile.value (‘user_id’) or fnd_global.user_id.
12. what are flexfields?
A flexfield is a field made up of segments . each segment has a name and a set of valid values. There are two types of ff’s: key ff, DFF
13. Which are the 2 parameters required to be defined when a program is registered as pl/sql stored procedure
ERRBUF, RETCODE
14. can we register executable/concurrent program programmatically then how?
Yes we can. It can be done with standard package fnd_program, fnd_program.executable, fnd_program.register.
15. what changes need to be made if a setup is changed from a single org to multi org?
Org_id should be added as a parameter in a report and report should be run for specific org_id only
16. What are sub functions? How is it different from from?
A sub function is a securable subset of a forms functionally
a) forms appear in a navigator window and can be navigated to sub functions do not appear in the navigator window and cant be navigated to
b) forms can exist on this own sub functions can only be called by ____ embodied within a form, they cant exist on their own
17. what is message dictionary?
Message dictionary allows defining messages which can be used in application without hard coding them into forms or programs.
18. What is the token in concurrent à program à parameters window?
For a parameter in an oracle report program, they keyword is parameter specified here. The value is case sensitive for example P_CUSTOMER_NO
19. What are different validation defaults types and default value in current à program à parameter window?
a) constant
b) profile
c) SQL statement
d) Segment
20. I have a concurrent program that involves lot of inserts and updates on a huge basis where do I specify rollback segment etc
concurrent ->program->session control
21. How do I change the priority of my concurrent program?
Log on as system admin concurrent à program à program à priority, enter numerical value in this field so it will take the request which has higher priority.
22. What is incompatibility?
When a program or list of programs is defined as incompatible means that defined program is not compatible with incompatible program list and cant run simultaneously in same conflict domain.
23. What is data group?
A data group defines the mapping b/w oracle applications and oracle ID’s. A data group determines oracle database accounts a responsibilities forms, concurrent programs, and reports connect to
Oracle Apps FAQ-1
1. What are alert in D2k forms?
An alert is a model window that displays a message notifying of some application condition. For example, do you want to save changes? Are you sure u want to exit? Or customer name can’t be bland ….
2. What is property class?
A property class is an object that contains a list of properties and their settings. Other objects can be based on property class. An object based on a property class can inherit the setting of any property in the class that makes sense for that object.
3. What is the difference b/w property class and visual attribute?
Visual attributes only for the visual properties of the item like font, color, whereas property class is for all the possible properties of objects.
4. What is an LOV?
LOV is a scrollable popup window that provides the use with selection list.
5. What are record groups and what are its types?
a) A record group is an internal form builder data structure that has a column/row framework similar to a database table.
b) Query record group – associated select statement
c) Non query Record group – doesn’t have query but can be changed programmatically.
d) Static record group – cant be changed programmatically.
6. What is restricted built_ins?
Any built_ins 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 build_ins are not allowed in trigger that fire in response to internal navigation.
7. What are categories of trigger?
a) block – processing triggers. Related to record mgmt in a block eg: when clear block.
b) Interface event triggers : interface event triggers fire in response to events that occur in the form interface eg: when button pressed. When checkbox changed.
c) Master_detail triggers : from builder generates master/detail triggers automatically when a master/detail relation is defined b/w blocks. This is to enforce co-ordination b/w 2 blocks. For example, on clear details.
d) Message handling triggers : message handling triggers fire in response to default messaging events. To trap or recover an error. Eg on_error, on_message
e) Navigational triggers : navigational triggers fire in response to navigational events. For instance clicking on a text item from another block eg: pre block, post text item, when new item instance.
f) Query_item trigger : Eg; pre query and post query
g) Transactional triggers : Fire in response to events that occur as a form interacts with the database. Eg: on_delete, pre_commit
h) Validation trigger : when form validated data in item or record e: when validate item.
8. What is the sequence of events fired while cursor moves from an item from i1st block to an item in 2nd block?
When validate item of that item A
Post_text_item of A
When validate record
Post record
Post block
Pre block
Pre record
Pre text item
When_new_block_instance
When_new_Record_Instance
When_new_item_instance
9. what are types of canvas?
a) Content : The default specifies that the canvas should occupy the entire content area of the window to which it is assigned.
b) Stacked : Specifies that the canvas should be displayed in its window at the same time as the window’s content canvas. They are usually displayed programmatically and overlay some portion of the content view displayed in the same window
c) Vertical toolbar canvas : specifies that the canvas should be displayed as a vertical toolbar under the menu bar of the window.
d) Horizontal toolbar canvas : specifies that the canvas should be displayed as a horizontal toolbar at the left side of the window to which it is assigned.
10. How do I attach menu to a form?
Form à properties à functional à menu source à File
Forms à Properties à functional à menu module à name of menu (main menu
11. What are 2 types of data blocks object?
a) Data blocks : data blocks are associated with data within a database. Data blocks can be based on database table, views, procedure or transactional triggers.
b) Control blocks : in contrast a control block is not associated with the database and the item in or control block do not relate to table columns within a database.
12. How do I dynamically change the title of window?
a) set_window_property built-in
13. Name few system variables in forms?
A system variable is a form builder variable that keeps track of an internal form builder state. For example, system.Block_status, system, mode, system.from_status.
14. How to attach reports in oracle application?
The steps are as follows:
a) Design you report
b) Generate the executable file of the report.
c) Move the executable as well as source file to the appropriate products folder.
d) Register the report as concurrent executable
e) Define the concurrent program for the executable registered
f) Add the concurrent program to the request group of the responsibility
15. What are different report triggers and what is their firing sequence?
There are fiver report trigger:
a) Before report
b) After report
c) Before parameter form
d) After parameter form
e) Between pages
The firing sequence for report trigger is
Before parameter form – After parameter form – before report – between pages – after report.
16. What is the use of cursors in PL/SQL? What is REF Cursor?
The cursor is used to handle multiple row query in PL/SQL. Oracle uses implicit cursors to handle its queries. Oracle uses unnamed memory spaces to store data used in implicit cursors, with REF cursors you can define a cursor variable, which will point to that memory space and can be used like pointers in our 3GL’s
17. What is record group?
Record group are used with LOV’s to hold SQL query for your list of values. The record group can contain static data as well it can access dates from database tables through sql queries
18. What is flexfield? What are DFF& KFF?
In oracle application field made up of segments each segment has Assigned name and a set of valid values. oracle application uses flexfield to capture into about your organization
19. What are Autonomous Transaction? Give Scenario where you have used Autonomous transaction In your report ?
An Autonomous transaction is an independent transaction started by another transaction , the main transaction ,Autonomous transaction lets you suspend the main transaction do SQL operations ,commit or rollback those operation then resume the main transaction Once started an autonomous transaction fully independent .It shares no locks ,resources .or commit dependencies with the main transaction so ,you can log events increment retry counters ,and soon even if the main transaction rolls back More important, Autonomous transaction help you build modules reusable software component . for example Stored procedures can start and finish autonomous transaction there own .A calling application need not know about a procedures autonomous operations, and the procedures need not know about the application transaction content.
Scenario: you can use autonomous transaction in your report for writing error message in your database table.
20. What is the use of trigger in the form?
Triggers are used in forms for event handling u can write PL/SQL code in trigger to respond to a particular event occurred in your forms like when user presses a button or when he commits the form
The different types to triggers available I forms are
a)Key Trigger
b)Navigational Trigger
c)Transaction triggers
d)Message Triggers
e)Error triggers
f)Query triggers
21. What is the use of temp table and interface table ?
Temporary table are used in I/F programs to hold the intermediate data. The data is loaded into temporary first and then after validation through the PL/SQL program, the data is loaded into the interface table
22. What are the steps to register concurrent program in APPS?
The steps to register the concurrent prom in APPS are follows
a)Register the prom as concurrent prom for the executable
b)Define the concurrent prom for the executable registered
c)Add the concurrent program to the request group of the responsibilities
23. How to pass parameters to a report ? Do you have to register them with AOL?
U can define parameters in the define concurrent prom form there is no need to register the parameters with AOL .But you may have to register the value sets for those parameters
24. Do you have to register feeder program of interface to AOL?
Yes, U have to register the feeder program as concurrent prom to APPS
25. What are the forms customization steps
Steps are as follows
a)Copy the “Template fmb”1 and “Appstand.fmb” from AU_top/Forms/us. Put it in custom directory .
the Libraries(FNDSQF,APPCORE,APPDAYPK,GLOBE,CUSTOM,JE,JA,VERT)are automatically attached
b)Create/open new forms .then customise
c)save this form in corresponding module.
26. How to use flexfield In report?
There are two ways to use flexfield in reports one is to use the views(table name+`_kfv`or `_dfv`) created by apps, and use the concatenated segment column that holds the concatenated segments of the key or descriptive flexfield
(or)
To use the FND user exits provided by apps
27. what is KFF, DFF?
KFF : # unique identifiers, storing key into # used for entering and displaying key into
For example oracle general uses a KFF called Accounting flex field to uniquely identify a general account.
DFF : # to capture additional info # to provide expansion space on your form with the help of []. [] represents DFF
An alert is a model window that displays a message notifying of some application condition. For example, do you want to save changes? Are you sure u want to exit? Or customer name can’t be bland ….
2. What is property class?
A property class is an object that contains a list of properties and their settings. Other objects can be based on property class. An object based on a property class can inherit the setting of any property in the class that makes sense for that object.
3. What is the difference b/w property class and visual attribute?
Visual attributes only for the visual properties of the item like font, color, whereas property class is for all the possible properties of objects.
4. What is an LOV?
LOV is a scrollable popup window that provides the use with selection list.
5. What are record groups and what are its types?
a) A record group is an internal form builder data structure that has a column/row framework similar to a database table.
b) Query record group – associated select statement
c) Non query Record group – doesn’t have query but can be changed programmatically.
d) Static record group – cant be changed programmatically.
6. What is restricted built_ins?
Any built_ins 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 build_ins are not allowed in trigger that fire in response to internal navigation.
7. What are categories of trigger?
a) block – processing triggers. Related to record mgmt in a block eg: when clear block.
b) Interface event triggers : interface event triggers fire in response to events that occur in the form interface eg: when button pressed. When checkbox changed.
c) Master_detail triggers : from builder generates master/detail triggers automatically when a master/detail relation is defined b/w blocks. This is to enforce co-ordination b/w 2 blocks. For example, on clear details.
d) Message handling triggers : message handling triggers fire in response to default messaging events. To trap or recover an error. Eg on_error, on_message
e) Navigational triggers : navigational triggers fire in response to navigational events. For instance clicking on a text item from another block eg: pre block, post text item, when new item instance.
f) Query_item trigger : Eg; pre query and post query
g) Transactional triggers : Fire in response to events that occur as a form interacts with the database. Eg: on_delete, pre_commit
h) Validation trigger : when form validated data in item or record e: when validate item.
8. What is the sequence of events fired while cursor moves from an item from i1st block to an item in 2nd block?
When validate item of that item A
Post_text_item of A
When validate record
Post record
Post block
Pre block
Pre record
Pre text item
When_new_block_instance
When_new_Record_Instance
When_new_item_instance
9. what are types of canvas?
a) Content : The default specifies that the canvas should occupy the entire content area of the window to which it is assigned.
b) Stacked : Specifies that the canvas should be displayed in its window at the same time as the window’s content canvas. They are usually displayed programmatically and overlay some portion of the content view displayed in the same window
c) Vertical toolbar canvas : specifies that the canvas should be displayed as a vertical toolbar under the menu bar of the window.
d) Horizontal toolbar canvas : specifies that the canvas should be displayed as a horizontal toolbar at the left side of the window to which it is assigned.
10. How do I attach menu to a form?
Form à properties à functional à menu source à File
Forms à Properties à functional à menu module à name of menu (main menu
11. What are 2 types of data blocks object?
a) Data blocks : data blocks are associated with data within a database. Data blocks can be based on database table, views, procedure or transactional triggers.
b) Control blocks : in contrast a control block is not associated with the database and the item in or control block do not relate to table columns within a database.
12. How do I dynamically change the title of window?
a) set_window_property built-in
13. Name few system variables in forms?
A system variable is a form builder variable that keeps track of an internal form builder state. For example, system.Block_status, system, mode, system.from_status.
14. How to attach reports in oracle application?
The steps are as follows:
a) Design you report
b) Generate the executable file of the report.
c) Move the executable as well as source file to the appropriate products folder.
d) Register the report as concurrent executable
e) Define the concurrent program for the executable registered
f) Add the concurrent program to the request group of the responsibility
15. What are different report triggers and what is their firing sequence?
There are fiver report trigger:
a) Before report
b) After report
c) Before parameter form
d) After parameter form
e) Between pages
The firing sequence for report trigger is
Before parameter form – After parameter form – before report – between pages – after report.
16. What is the use of cursors in PL/SQL? What is REF Cursor?
The cursor is used to handle multiple row query in PL/SQL. Oracle uses implicit cursors to handle its queries. Oracle uses unnamed memory spaces to store data used in implicit cursors, with REF cursors you can define a cursor variable, which will point to that memory space and can be used like pointers in our 3GL’s
17. What is record group?
Record group are used with LOV’s to hold SQL query for your list of values. The record group can contain static data as well it can access dates from database tables through sql queries
18. What is flexfield? What are DFF& KFF?
In oracle application field made up of segments each segment has Assigned name and a set of valid values. oracle application uses flexfield to capture into about your organization
19. What are Autonomous Transaction? Give Scenario where you have used Autonomous transaction In your report ?
An Autonomous transaction is an independent transaction started by another transaction , the main transaction ,Autonomous transaction lets you suspend the main transaction do SQL operations ,commit or rollback those operation then resume the main transaction Once started an autonomous transaction fully independent .It shares no locks ,resources .or commit dependencies with the main transaction so ,you can log events increment retry counters ,and soon even if the main transaction rolls back More important, Autonomous transaction help you build modules reusable software component . for example Stored procedures can start and finish autonomous transaction there own .A calling application need not know about a procedures autonomous operations, and the procedures need not know about the application transaction content.
Scenario: you can use autonomous transaction in your report for writing error message in your database table.
20. What is the use of trigger in the form?
Triggers are used in forms for event handling u can write PL/SQL code in trigger to respond to a particular event occurred in your forms like when user presses a button or when he commits the form
The different types to triggers available I forms are
a)Key Trigger
b)Navigational Trigger
c)Transaction triggers
d)Message Triggers
e)Error triggers
f)Query triggers
21. What is the use of temp table and interface table ?
Temporary table are used in I/F programs to hold the intermediate data. The data is loaded into temporary first and then after validation through the PL/SQL program, the data is loaded into the interface table
22. What are the steps to register concurrent program in APPS?
The steps to register the concurrent prom in APPS are follows
a)Register the prom as concurrent prom for the executable
b)Define the concurrent prom for the executable registered
c)Add the concurrent program to the request group of the responsibilities
23. How to pass parameters to a report ? Do you have to register them with AOL?
U can define parameters in the define concurrent prom form there is no need to register the parameters with AOL .But you may have to register the value sets for those parameters
24. Do you have to register feeder program of interface to AOL?
Yes, U have to register the feeder program as concurrent prom to APPS
25. What are the forms customization steps
Steps are as follows
a)Copy the “Template fmb”1 and “Appstand.fmb” from AU_top/Forms/us. Put it in custom directory .
the Libraries(FNDSQF,APPCORE,APPDAYPK,GLOBE,CUSTOM,JE,JA,VERT)are automatically attached
b)Create/open new forms .then customise
c)save this form in corresponding module.
26. How to use flexfield In report?
There are two ways to use flexfield in reports one is to use the views(table name+`_kfv`or `_dfv`) created by apps, and use the concatenated segment column that holds the concatenated segments of the key or descriptive flexfield
(or)
To use the FND user exits provided by apps
27. what is KFF, DFF?
KFF : # unique identifiers, storing key into # used for entering and displaying key into
For example oracle general uses a KFF called Accounting flex field to uniquely identify a general account.
DFF : # to capture additional info # to provide expansion space on your form with the help of []. [] represents DFF
Oracle Reports FAQ-2
1. What is bind variable and lexical variable?
Bind variable in report are parameters that are referenced with a colon in front of them, for example, :P_customer_no. This is considered a Bind reference. If you reference the same parameter proceeded by an ampersand, as & p_customer_no. Then its considered to be a lexical reference. A bind paramerter can only contain one value, while a lexical parameter can replace an entire clause example order by where clause.
2. What is the use of n anchor in the report?
Anchors are used for determining the vertical and horizontal positioning of a child object relative to its parent.
3. What is placeholder column?
Placeholder column datatype and a value can be set in PL/SQL like reports triggers or formula column.
4. What is a data link?
Data link relate the results of multiple queries. A data link causes the child query to be executed once for instance of its parent group.
5. What are the report triggers?
a) Report triggers execute at specific times during the execution and formatting of your report
b) After parameter from trigger
c) After report trigger
d) Before parameter form trigger
e) Before report trigger
f) Between pages trigger.
6. What is the validation trigger in report parameters property
Validation triggers are executed when parameter values are specified on the command line and when u accept the runtime parameter form. This is used for validating parameter foe example “Todate” can’t be earlier than “from date” etc.
7. What is confine mode?
In layout model if confine mode is on child objects can’t be moved outside their enclosing parent object and when it is off child object can be moved outside their enclosing parent objects. Toolbar it shows locks symbol.
8. What is flex mode?
In layout model when it is on parent borders “stretch” when child object are moved against them. And when it is off parent borders remain fixed when child object are moved against them.
Bind variable in report are parameters that are referenced with a colon in front of them, for example, :P_customer_no. This is considered a Bind reference. If you reference the same parameter proceeded by an ampersand, as & p_customer_no. Then its considered to be a lexical reference. A bind paramerter can only contain one value, while a lexical parameter can replace an entire clause example order by where clause.
2. What is the use of n anchor in the report?
Anchors are used for determining the vertical and horizontal positioning of a child object relative to its parent.
3. What is placeholder column?
Placeholder column datatype and a value can be set in PL/SQL like reports triggers or formula column.
4. What is a data link?
Data link relate the results of multiple queries. A data link causes the child query to be executed once for instance of its parent group.
5. What are the report triggers?
a) Report triggers execute at specific times during the execution and formatting of your report
b) After parameter from trigger
c) After report trigger
d) Before parameter form trigger
e) Before report trigger
f) Between pages trigger.
6. What is the validation trigger in report parameters property
Validation triggers are executed when parameter values are specified on the command line and when u accept the runtime parameter form. This is used for validating parameter foe example “Todate” can’t be earlier than “from date” etc.
7. What is confine mode?
In layout model if confine mode is on child objects can’t be moved outside their enclosing parent object and when it is off child object can be moved outside their enclosing parent objects. Toolbar it shows locks symbol.
8. What is flex mode?
In layout model when it is on parent borders “stretch” when child object are moved against them. And when it is off parent borders remain fixed when child object are moved against them.
Subscribe to:
Posts (Atom)
