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.
Saturday, December 5, 2009
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.
Pl/Sql FAQ-1
1. What is the difference b/w subquery and correlated subquery?
a) In a normal subquery, the inner query is executed first and then the result are passed off to the parent query.
b) In a correlated subquery, the outer query is executed first and process each row, the subquery is using. The info supplies by the result of the outer query.
2. How to delete duplicate rows from a table?
Delete test t1 where rowed< (Select max(rowed) from test t2 where t2.col1 = t1.col and t2.col2 = t1.col1.
3. what are the types of database triggers?
Ro. Total no of database triggers are 12. They are
a) Row level : once for every row affected by the triggering statement such as a trigger fired by an update statement that updates many rows.
b) Statement level : once for the triggering statement, no matters be many rows it affects.
c) Before : before triggering statement is executed.
d) After: After triggering statement is executed.
e) Instead of : triggers provider a transparent way of modifying that cant be modified through DML statement.
4. what is mutating error on a table?
It happens when a trigger on a table tries to insert, update or ever select the table of where trigger is being executed.
5. What is synonym? What is view?
A synonym is an alias for any table, sequence, procedure function or package. It requires no storage other than its definitions in the data dictionary.
A view is stored query, from one or more tables.
6. What is database link?
A database link is a pointer that defines a one_way communication path from an oracle database server to another database server
7. What is a dynamic SQL?
DDL statement can’t be used within PL/SQL using “Execute Immediate”
8. What are set operator?
Set operators combine the result of two component queries into a single result queries containing set operator are called components queries
UNION : all rows selected by either query
UNION ALL : All rows selected by either query, including all duplicate
INTERSECT : All distinct rows selected by both queries
MINUS : All distinct rows selected by the first query but not the second.
9, What is savepoint?
Savepoint are intermediate markers within the context of a transaction savepoint divide a long transaction into smaller parts we then have to option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction.
10. What are the benefits of using package? Name few oracle supplied package
An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for stand alone procedure. A package body can be replaced and recompiled without affecting the specification. Definition of procedure/variable can be private or public. For example I have 5 procedure out of 3 procedures can be used by a DBMS_SQL, DBMS_JOB, UTL_FILE.
11. What is deadlock?
A deadlock can occur when two or more user are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work.
a) In a normal subquery, the inner query is executed first and then the result are passed off to the parent query.
b) In a correlated subquery, the outer query is executed first and process each row, the subquery is using. The info supplies by the result of the outer query.
2. How to delete duplicate rows from a table?
Delete test t1 where rowed< (Select max(rowed) from test t2 where t2.col1 = t1.col and t2.col2 = t1.col1.
3. what are the types of database triggers?
Ro. Total no of database triggers are 12. They are
a) Row level : once for every row affected by the triggering statement such as a trigger fired by an update statement that updates many rows.
b) Statement level : once for the triggering statement, no matters be many rows it affects.
c) Before : before triggering statement is executed.
d) After: After triggering statement is executed.
e) Instead of : triggers provider a transparent way of modifying that cant be modified through DML statement.
4. what is mutating error on a table?
It happens when a trigger on a table tries to insert, update or ever select the table of where trigger is being executed.
5. What is synonym? What is view?
A synonym is an alias for any table, sequence, procedure function or package. It requires no storage other than its definitions in the data dictionary.
A view is stored query, from one or more tables.
6. What is database link?
A database link is a pointer that defines a one_way communication path from an oracle database server to another database server
7. What is a dynamic SQL?
DDL statement can’t be used within PL/SQL using “Execute Immediate”
8. What are set operator?
Set operators combine the result of two component queries into a single result queries containing set operator are called components queries
UNION : all rows selected by either query
UNION ALL : All rows selected by either query, including all duplicate
INTERSECT : All distinct rows selected by both queries
MINUS : All distinct rows selected by the first query but not the second.
9, What is savepoint?
Savepoint are intermediate markers within the context of a transaction savepoint divide a long transaction into smaller parts we then have to option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction.
10. What are the benefits of using package? Name few oracle supplied package
An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for stand alone procedure. A package body can be replaced and recompiled without affecting the specification. Definition of procedure/variable can be private or public. For example I have 5 procedure out of 3 procedures can be used by a DBMS_SQL, DBMS_JOB, UTL_FILE.
11. What is deadlock?
A deadlock can occur when two or more user are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work.
Saturday, November 28, 2009
What is NOCOPY hint?
Prior to Oracle 8i there were three types of parameter-passing options to procedures and functions:
* IN: parameters are passed by reference
* OUT: parameters are implemented as copy-out
* IN OUT: parameters are implemented as copy-in/copy-out
The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised so that changes could be rolled back. Because a copy of the parameter set was made rollback could be done. However this method imposed significant CPU and memory overhead when the parameters were large data collections for example PL/SQL Table or VARRAY types.
With the new NOCOPY option OUT and IN OUT parameters are passed by reference which avoids copy overhead. However parameter set copy is not created and in case of an exception rollback cannot be performed and the original values of parameters cannot be restored.
Here is an example of using the NOCOPY parameter option:
TYPE Note IS RECORD( Title VARCHAR2(15), Created_By VARCHAR2(20), Created_When DATE, Memo VARCHAR2(2000));
TYPE Notebook IS VARRAY(2000) OF Note;
CREATE OR REPLACE PROCEDURE Update_Notes(Customer_Notes IN OUT NOCOPY Notebook) IS BEGIN ...END;
* IN: parameters are passed by reference
* OUT: parameters are implemented as copy-out
* IN OUT: parameters are implemented as copy-in/copy-out
The technique of OUT and IN OUT parameters was designed to protect original values of them in case exceptions were raised so that changes could be rolled back. Because a copy of the parameter set was made rollback could be done. However this method imposed significant CPU and memory overhead when the parameters were large data collections for example PL/SQL Table or VARRAY types.
With the new NOCOPY option OUT and IN OUT parameters are passed by reference which avoids copy overhead. However parameter set copy is not created and in case of an exception rollback cannot be performed and the original values of parameters cannot be restored.
Here is an example of using the NOCOPY parameter option:
TYPE Note IS RECORD( Title VARCHAR2(15), Created_By VARCHAR2(20), Created_When DATE, Memo VARCHAR2(2000));
TYPE Notebook IS VARRAY(2000) OF Note;
CREATE OR REPLACE PROCEDURE Update_Notes(Customer_Notes IN OUT NOCOPY Notebook) IS BEGIN ...END;
What is Function Overloading?
Two or more procedures or functions are called overloaded when
a) They have the same names
b) Different no of formal parameters defined
c) Formal parameters differ in their datatypes/Subtypes and not in the same family
d) They belong to same subprogram/package/PL SQL Block
They are not overloaded when
a) Any of the above points are not satisfied
b) RETURN datatype alone differs and not the formal parameters
The main purpose of overloading procedures is that when a PL SQL block is found to do a same operation with different inputs we names them same and feed different parameters
a) They have the same names
b) Different no of formal parameters defined
c) Formal parameters differ in their datatypes/Subtypes and not in the same family
d) They belong to same subprogram/package/PL SQL Block
They are not overloaded when
a) Any of the above points are not satisfied
b) RETURN datatype alone differs and not the formal parameters
The main purpose of overloading procedures is that when a PL SQL block is found to do a same operation with different inputs we names them same and feed different parameters
Explain Actual and Formal Parameters
Formal Parameter: A variable declared in the parameter list of a subprogram specification
Example: create or replace procedure/function x (p_id number p_sal number)
Actual Parameter: A variable or expression refrenced in the parameter list of a subprogram call.
example: execute x( 100 v_sal)
Example: create or replace procedure/function x (p_id number p_sal number)
Actual Parameter: A variable or expression refrenced in the parameter list of a subprogram call.
example: execute x( 100 v_sal)
Diff between Function and Procedure?
1.Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
2.Function can be called from SQL statements where as procedure can not be called from the sql statements
3.Functions are normally used for computations where as procedures are normally used for executing business logic.
4.You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5.Function returns 1 value only. Procedure can return multiple values (max 1024).
2.Function can be called from SQL statements where as procedure can not be called from the sql statements
3.Functions are normally used for computations where as procedures are normally used for executing business logic.
4.You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5.Function returns 1 value only. Procedure can return multiple values (max 1024).
What is Forward Declaration?
In PL/SQL (and other languates like Pascal) a program unit such as a procedure or function must be defined before it can be invoked by another program unit. In PL/SQL package bodies forward declarations provide an optional means to get around this. You declare all program units before they actually appear in the package. A forward declaration is merely the name of the program unit and any parameters required. With forward declarations in place you are now free to arrange the program units in the package body in any order so as to improve readability or group program units together logically. Note that forward declarations are not required as long as you code a program unit before it is called. Additionally forward declarations can appear anywhere not just at the top of the package body. Anytime you declare a program unit in advance of the actual coding that is a forward declaration.
Example:
procedure A is
begin
B;
end A;
procedure B is
begin
null;
end B;
Will not work because during call to B, B is still unknown (1 Step compiler) therefore we need a forward declaration,
procedure B;
procedure A is
begin
B;
end A;
procedure B is
begin
null;
end B;
Now we can compile
Source=> Net
Example:
procedure A is
begin
B;
end A;
procedure B is
begin
null;
end B;
Will not work because during call to B, B is still unknown (1 Step compiler) therefore we need a forward declaration,
procedure B;
procedure A is
begin
B;
end A;
procedure B is
begin
null;
end B;
Now we can compile
Source=> Net
Oracle Reports FAQ-1
1.How many types of Columns are there in Reports6i?
There are three types of Columns. Formula Column, summary column, placeholder column.
2.Can you have more than one layout in One Report?
It is possible to have more than one layout in one report by using additional layout option in Layout Editor.
3.Can you run Report without Parameter Form?
Yes it is possible to run the report without parameter form by setting the PARAM value to Null
4.What are the minimum number of groups required for a matrix report?
The minimum of groups required for a matrix report are 4
5.Which of the following Option is valid for Panel/Print Order Property in Report Builder?
Across Down/Down Across
6.What is default Unit of Measurement in Report?
Default Unit of measurement is Inch.
7.How many types of Parameters are available in Reports?
There are two types of Parameters available in Reports
One is System Parameter and another is User Parameter.
8.What is the purpose of PlaceHolder Column in Report?
A column for which you set the datatype and value in PL/SQL that you define.
9.What is the purpose of Summary Column in Reports?
A performs a computation on another column's data.
10.What is the purpose of Formula Columns?
A column performs a user-defined computation on another column(s) data, including placeholder columns.
11.Which of the following Procedures displays message number and text that you specify?
SRW.MESSAGE
This procedure displays a message with the message number and text that you specify. The message is displayed in the format below. After the message is raised and you accept it, the report execution will continue.
12.What are bind variables?
Variable that 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.
13.Can lexical reference be made in PL/SQL statement?
No
14.Following of which trigger will fire first?
1. Between Pages
2. After Parameter Form
3. Before Parameter Form
4. Before Reprort
Answer : C
15. Is there a way to change the same format mask in a lot of fields in one step?
Select all the Items and change the format mask for all the item once.
There are three types of Columns. Formula Column, summary column, placeholder column.
2.Can you have more than one layout in One Report?
It is possible to have more than one layout in one report by using additional layout option in Layout Editor.
3.Can you run Report without Parameter Form?
Yes it is possible to run the report without parameter form by setting the PARAM value to Null
4.What are the minimum number of groups required for a matrix report?
The minimum of groups required for a matrix report are 4
5.Which of the following Option is valid for Panel/Print Order Property in Report Builder?
Across Down/Down Across
6.What is default Unit of Measurement in Report?
Default Unit of measurement is Inch.
7.How many types of Parameters are available in Reports?
There are two types of Parameters available in Reports
One is System Parameter and another is User Parameter.
8.What is the purpose of PlaceHolder Column in Report?
A column for which you set the datatype and value in PL/SQL that you define.
9.What is the purpose of Summary Column in Reports?
A performs a computation on another column's data.
10.What is the purpose of Formula Columns?
A column performs a user-defined computation on another column(s) data, including placeholder columns.
11.Which of the following Procedures displays message number and text that you specify?
SRW.MESSAGE
This procedure displays a message with the message number and text that you specify. The message is displayed in the format below. After the message is raised and you accept it, the report execution will continue.
12.What are bind variables?
Variable that 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.
13.Can lexical reference be made in PL/SQL statement?
No
14.Following of which trigger will fire first?
1. Between Pages
2. After Parameter Form
3. Before Parameter Form
4. Before Reprort
Answer : C
15. Is there a way to change the same format mask in a lot of fields in one step?
Select all the Items and change the format mask for all the item once.
Thursday, November 26, 2009
How to transfer funds between your internal banks?
Internal bank transfers can be handled in two ways.
1. By using the Cash Management functionality.
OR
2. You need to create a supplier and a customer in the name of your company.These should be used only for fund transfers (with fund transfer sites ). You can raise a standard invoice in AP on the Supplier ( Your Co. as supplier ) then make the payment selecting the bank account from which u are transferring funds. Now in AR you can create a Debit Memo on your company's name (Customer) and book a receipt selecting the bank in which the funds are to be transferred and apply the DM to the receipt. Finally at the month end you can pass a manual JV in gl and clear off the balance remaining in these accounts.
Difference between Quick payment and Manual payment?
Manual Payment
When ur making payments not through payables. Example- Through wire or typed cheque, then u update the paid invoice in payables (by selecting payment type as manual)
Note: you can record payment for invoices that are associated with any Payment Method type, except Electronic.
Quick payment
When you create a quick payment you can select an invoice regardless of the payments terms and due date. For example, you can create a Quick payment for an invoice that is not yet due OR when you are making payment through Oracle e-Commerce Gateway .
What is meant by accrual write off?
The liability for the uninvoiced receipts is kept in the AP Accrual Account (which is a suspense account) till the time you have an invoice for the receipt. This will happen as soon as you create the receipt(if you are using accrue on receipt ) or at period close. The same is accrued as soon as you enter an invoice for these receipts.
When you might want to write off this liability?
Here are some resons:
1.What PO warehouse has received may not be the one finance/accounting has invoiced. Hence you PO remains open as if it is not invoiced.
2.Sometimes finance may invoice without matching and pay vendor.Hence vendor may not question and procurement is also not questioning because they have received material.
3.Lot of times finance creates invoice for freight with accrual account with no reference to PO.this gets qualified for writeoff.
4.Invoicing for more quantity than what has been received. On the similar note invoicing in advance but never received.
Monday, November 23, 2009
What is the difference between Accrual Write Off Process in R12 and R11i?
In R11i Accrual Write Offs only affected the Accrual Reconciliation Report. One had to pass manual Journal Entries in GL to adjust the GL Inventory AP Accrual Account. In other words without Manual Journals the write off process had no impact in GL.
In R12 the adjustment in GL is automated. No Manual Journal is required. The create Accounting program and Transfer to GL program will transfer the write off journals automatically to General ledger.
In R12 the adjustment in GL is automated. No Manual Journal is required. The create Accounting program and Transfer to GL program will transfer the write off journals automatically to General ledger.
Thursday, November 19, 2009
Some PO FAQ from Net
1. Define Requisition?
A. It is an internal request raised by an employee or an inventory organization for the requirement of material for services.
2. What is the process for requisition import?
A. Use the Requisition Import process to import requisitions from other Oracle or non-Oracle systems. Work in Process, Master Scheduling/MRP, Order Entry, and Inventory (as well as custom systems) can provide requisition information for the Purchasing requisition interface table. For example, drop-ship orders from Order Entry and Kanban orders from Inventory are sent to the requisition interface table and converted into requisitions. Also, project and task information from Master Scheduling/MRP are imported through the Requisition Import process. Requisition Import creates a requisition line and one or more requisition distributions for each row it finds in the interface table. It then groups these lines on requisitions according to parameters you define below.
3. What is meant by RFQ?
A. RFQ terms is widely used in Purchasing context,it means request for quotation . This is prepared to request supplier to send his quotation for mentioned items and quantities. Supplier send its quotation to buyer, which buyer enters into application.
4. What are the types of RFQ's?
A. There are three types of RFQs
Standard: Use for one time purchase.
Bid: Use when the items are less & price is very high.
Catalog: Use when the number of items are more.
5. What is meant by quotation and quotation analysis?
A.
Quotation: Statement consisting of current price of the commodity which the supplier will be sending to the organisation, when we place RFQ.
Quotation Analysis: After receiving the quotation from different suppliers we select the best quotation, the process of selecting the best is know as quotation analysis.
6. What is meant my PO?
A. A legal document used to request someone to supply something in return for payment and providing specifications and quantities.
7. What are the types of PO?
A.
1. Standard Purchase Order
2. Blanket Purchase Agreement
3. Contract Purchase Order
4. Planned Purchase Order
We can issue blanket releases against blanket purchase agreement, we can issue scheduled releases against contract purchase agreement.
8. What are the types of receipts?
A. There are two types of receipts in purchasing
1.Express
2.Cascade
9. What is meant by receipt routing?
A. Receipt routing is a process by which the receipt is processed and sent to Accounts Payables for payment to vendor or supplier.
Receipt routing are of three types..
1. Standard routing
2. Direct routing
3. Inspection routing
Standard (Standard process of receipts)
Goods received from
supplier ----------> sub inventory.
Direct (Delivers directly to stores)
supplier---->receiving area(staging area)------>sub inventory
Inspection (Raw Material is checked for Quality parameters (as per the quality plan) then accepted or rejected and sent to stores)
supplier----> receiving area------->inspection(done)---->sub inventory.
10. What is the purpose of receiving transactions?
A. You record inspections, deliveries, and material movements within receiving and inspection by entering receiving transactions. These transactions provide a history that allows you to track an item from its source to its destination.
From the time of receipt, many transactions may be required to record the movement of an item through the receiving and inspection process. For example, after being received, an item can be transferred, inspected, and then delivered. To help you keep track of an item within receiving and inspection, these transactions are linked to each other. You can use the Receiving Transaction Summary to view an item's transaction history as if it were a family tree with parent and child transactions. A parent transaction can have one or more child transactions, but a child transaction has only one parent transaction.
11. What is the use of Auto Create?
A. Auto Create function -- Will create PO Document automatically (means u will come into PO window)by giving the requisition number in auto create window.
12. Why are expenses items typically accrued at period end, and why are inventory items always accrued on receipt?
A. As per Matching Concept all costs which are applicable to the revenue of the period should be charged against that revenue in order to determine net income of the business.
Inventory items are always accrued at receipt bcos you cannot book liability without receiving the asset.
A. It is an internal request raised by an employee or an inventory organization for the requirement of material for services.
2. What is the process for requisition import?
A. Use the Requisition Import process to import requisitions from other Oracle or non-Oracle systems. Work in Process, Master Scheduling/MRP, Order Entry, and Inventory (as well as custom systems) can provide requisition information for the Purchasing requisition interface table. For example, drop-ship orders from Order Entry and Kanban orders from Inventory are sent to the requisition interface table and converted into requisitions. Also, project and task information from Master Scheduling/MRP are imported through the Requisition Import process. Requisition Import creates a requisition line and one or more requisition distributions for each row it finds in the interface table. It then groups these lines on requisitions according to parameters you define below.
3. What is meant by RFQ?
A. RFQ terms is widely used in Purchasing context,it means request for quotation . This is prepared to request supplier to send his quotation for mentioned items and quantities. Supplier send its quotation to buyer, which buyer enters into application.
4. What are the types of RFQ's?
A. There are three types of RFQs
Standard: Use for one time purchase.
Bid: Use when the items are less & price is very high.
Catalog: Use when the number of items are more.
5. What is meant by quotation and quotation analysis?
A.
Quotation: Statement consisting of current price of the commodity which the supplier will be sending to the organisation, when we place RFQ.
Quotation Analysis: After receiving the quotation from different suppliers we select the best quotation, the process of selecting the best is know as quotation analysis.
6. What is meant my PO?
A. A legal document used to request someone to supply something in return for payment and providing specifications and quantities.
7. What are the types of PO?
A.
1. Standard Purchase Order
2. Blanket Purchase Agreement
3. Contract Purchase Order
4. Planned Purchase Order
We can issue blanket releases against blanket purchase agreement, we can issue scheduled releases against contract purchase agreement.
8. What are the types of receipts?
A. There are two types of receipts in purchasing
1.Express
2.Cascade
9. What is meant by receipt routing?
A. Receipt routing is a process by which the receipt is processed and sent to Accounts Payables for payment to vendor or supplier.
Receipt routing are of three types..
1. Standard routing
2. Direct routing
3. Inspection routing
Standard (Standard process of receipts)
Goods received from
supplier ----------> sub inventory.
Direct (Delivers directly to stores)
supplier---->receiving area(staging area)------>sub inventory
Inspection (Raw Material is checked for Quality parameters (as per the quality plan) then accepted or rejected and sent to stores)
supplier----> receiving area------->inspection(done)---->sub inventory.
10. What is the purpose of receiving transactions?
A. You record inspections, deliveries, and material movements within receiving and inspection by entering receiving transactions. These transactions provide a history that allows you to track an item from its source to its destination.
From the time of receipt, many transactions may be required to record the movement of an item through the receiving and inspection process. For example, after being received, an item can be transferred, inspected, and then delivered. To help you keep track of an item within receiving and inspection, these transactions are linked to each other. You can use the Receiving Transaction Summary to view an item's transaction history as if it were a family tree with parent and child transactions. A parent transaction can have one or more child transactions, but a child transaction has only one parent transaction.
11. What is the use of Auto Create?
A. Auto Create function -- Will create PO Document automatically (means u will come into PO window)by giving the requisition number in auto create window.
12. Why are expenses items typically accrued at period end, and why are inventory items always accrued on receipt?
A. As per Matching Concept all costs which are applicable to the revenue of the period should be charged against that revenue in order to determine net income of the business.
Inventory items are always accrued at receipt bcos you cannot book liability without receiving the asset.
Wednesday, November 18, 2009
What are the types of requisitions?
Purchase Requisitions Use the Requisitions window to create requisitions. You must choose the requisition type (internal or purchase). You can also provide a description, unlimited notes, and defaults for requisition lines. For each requisition line, you choose the item you want to order along with the quantity and delivery location. You can get sourced pricing from catalog quotations or open blanket purchase agreements. You can also choose a price from a list of historical purchase order prices. In the Distributions window, you can charge the item to the appropriate accounts, or you can let the Account Generator create the accounts for you. Once you complete the requisition, you send it through the approval process.
Internal Requisitions Unlike purchase requisitions, which are supplied from purchase orders, internal requisitions are supplied from internal sales orders. Internal requisitions are not picked up when you AutoCreate RFQs or purchase orders, nor can they be assigned to a buyer in the Assign Requisitions window.
Requisition Templates Use the Requisition Templates window to define requisition templates for items you purchase frequently. For example, if you frequently buy certain office supplies, you can set up an office supplies template for your requesters. This template consists of a list of all items, prices, and sourcing information you want available to the requester. When you want to order items from this template, use the Supplier Item Catalog, choose the office supplies template, and indicate the quantity you want to order. Purchasing supplies default sourcing and delivery information.
Imported Requisitions Purchasing lets you import or reschedule requisitions from other Oracle or non-Oracle systems. For example, Work in Process uses Requisition Import to create requisitions for outside processing. Similarly, Master Scheduling/MRP automatically creates requisitions using Requisition Import when you mass load purchase requisitions. Master Scheduling/MRP also automatically reschedules existing requisitions by updating the need-by date during this process. If you are using Inventory, you can send a requisition requirement to Purchasing and then run Requisition Import in Purchasing to create the requisition.
Paper Requisitions You do not need to create a requisition on-line to purchase an item. Instead, you can reference a paper requisition number directly on the purchase order Distribution Details. Later, you can use the Purchase Orders window to review the status of the purchase orders referencing the paper requisitions.
Internal Requisitions Unlike purchase requisitions, which are supplied from purchase orders, internal requisitions are supplied from internal sales orders. Internal requisitions are not picked up when you AutoCreate RFQs or purchase orders, nor can they be assigned to a buyer in the Assign Requisitions window.
Requisition Templates Use the Requisition Templates window to define requisition templates for items you purchase frequently. For example, if you frequently buy certain office supplies, you can set up an office supplies template for your requesters. This template consists of a list of all items, prices, and sourcing information you want available to the requester. When you want to order items from this template, use the Supplier Item Catalog, choose the office supplies template, and indicate the quantity you want to order. Purchasing supplies default sourcing and delivery information.
Imported Requisitions Purchasing lets you import or reschedule requisitions from other Oracle or non-Oracle systems. For example, Work in Process uses Requisition Import to create requisitions for outside processing. Similarly, Master Scheduling/MRP automatically creates requisitions using Requisition Import when you mass load purchase requisitions. Master Scheduling/MRP also automatically reschedules existing requisitions by updating the need-by date during this process. If you are using Inventory, you can send a requisition requirement to Purchasing and then run Requisition Import in Purchasing to create the requisition.
Paper Requisitions You do not need to create a requisition on-line to purchase an item. Instead, you can reference a paper requisition number directly on the purchase order Distribution Details. Later, you can use the Purchase Orders window to review the status of the purchase orders referencing the paper requisitions.
Sunday, November 8, 2009
How to Load Oracle Workflow Definition into Database
The Workflow Definitions Loader is called WFLOAD and can be found in: $FND_TOP/bin
By default it can be used the following ways.
To upload: WFLOAD apps/pwd 0 Y UPLOAD file.wft
To force: WFLOAD apps/pwd 0 Y FORCE file.wft
To download: WFLOAD apps/pwd 0 Y DOWNLOAD file.wft ITEMTYPE1 [ITEMTYPE2...ITEMTYPEn]
To upgrade: WFLOAD apps/pwd 0 Y UPGRADE file.wft
You can use a concurrent program to load process definitions. Run Workflow Definitions Loader concurrent program
By default it can be used the following ways.
To upload: WFLOAD apps/pwd 0 Y UPLOAD file.wft
To force: WFLOAD apps/pwd 0 Y FORCE file.wft
To download: WFLOAD apps/pwd 0 Y DOWNLOAD file.wft ITEMTYPE1 [ITEMTYPE2...ITEMTYPEn]
To upgrade: WFLOAD apps/pwd 0 Y UPGRADE file.wft
You can use a concurrent program to load process definitions. Run Workflow Definitions Loader concurrent program
Thursday, November 5, 2009
Payables Imp Topics
Payables Imp Topics( R12 Payables Management Fundamentals.pdf)
1)Procure to pay
2)Open interfaces
3)Suppliers overview and merge
4)Types of invoices
5)PO matching, receipt matching, Matching types, ERS invoices.
6)Different types of variances
7)Distribution types(freight, tax, misc) and distributions sets
8)Tolerance and Price correction
9)Invoice Approval Work flow
10)Types of holds and purposes
11)Payables options, Financial options, Payables Setup options
12)Imp profile options
Payments in Payables
1)Types of payments and uses
2)PPR-Diff stages and importance
3)Prepayment
4)Create refunds and voiding
5)Creating payment methods and PPP
6)3rd party payments
7)Profile options
Expense Reports and Credit Cards in Payables
1)Overview of expense reports, procurement card and credit cards
2)Reports
3)Imp profile options
1099 Reporting Overview and Reports
1)Overview
2)Reports
3)Imp profile options for 1099 reports
Period Close in AP
1)Overview and steps to confirm period closure
2)Imp profile options for period close
Different types of taxes
1)Self-assessed tax
2)Manual tax
3)Tax tolerance and variances (TRV, TERV , etc..)
4)Withholding tax and types of withholding(invoice time and payment time)
5)Withholding tax reports
6)Imp profile options for withholding
Complex POs and Retainage
1)Overview
2)Imp profile options for Complex PO and retainage
1)Procure to pay
2)Open interfaces
3)Suppliers overview and merge
4)Types of invoices
5)PO matching, receipt matching, Matching types, ERS invoices.
6)Different types of variances
7)Distribution types(freight, tax, misc) and distributions sets
8)Tolerance and Price correction
9)Invoice Approval Work flow
10)Types of holds and purposes
11)Payables options, Financial options, Payables Setup options
12)Imp profile options
Payments in Payables
1)Types of payments and uses
2)PPR-Diff stages and importance
3)Prepayment
4)Create refunds and voiding
5)Creating payment methods and PPP
6)3rd party payments
7)Profile options
Expense Reports and Credit Cards in Payables
1)Overview of expense reports, procurement card and credit cards
2)Reports
3)Imp profile options
1099 Reporting Overview and Reports
1)Overview
2)Reports
3)Imp profile options for 1099 reports
Period Close in AP
1)Overview and steps to confirm period closure
2)Imp profile options for period close
Different types of taxes
1)Self-assessed tax
2)Manual tax
3)Tax tolerance and variances (TRV, TERV , etc..)
4)Withholding tax and types of withholding(invoice time and payment time)
5)Withholding tax reports
6)Imp profile options for withholding
Complex POs and Retainage
1)Overview
2)Imp profile options for Complex PO and retainage
Thursday, October 29, 2009
Forms customization- About Custom.pll
One can find good info from below links..
http://oracle.anilpassi.com/custom.pll-2.html
http://oracle.anilpassi.com/custom.pll-best-practice.html
Some commands for CUSTOM.pll
For 11i,
To convert from CUSTOM.pll to CUSTOM.pld
f60gen module_type=LIBRARY module=CUSTOM script=YES userid=apps/apps
To convert back from CUSTOM.pld to CUSTOM.pll ( after having edited the text pld file )
f60gen module_type=LIBRARY module=CUSTOM parse=YES userid=apps/apps
To convert from CUSTOM.pll to CUSTOM.plx
f60gen module_type=LIBRARY module=CUSTOM userid=apps/apps
IMP: For R12 files, replace f60gen with frmcmp
http://oracle.anilpassi.com/custom.pll-2.html
http://oracle.anilpassi.com/custom.pll-best-practice.html
Some commands for CUSTOM.pll
For 11i,
To convert from CUSTOM.pll to CUSTOM.pld
f60gen module_type=LIBRARY module=CUSTOM script=YES userid=apps/apps
To convert back from CUSTOM.pld to CUSTOM.pll ( after having edited the text pld file )
f60gen module_type=LIBRARY module=CUSTOM parse=YES userid=apps/apps
To convert from CUSTOM.pll to CUSTOM.plx
f60gen module_type=LIBRARY module=CUSTOM userid=apps/apps
IMP: For R12 files, replace f60gen with frmcmp
Tuesday, October 27, 2009
Different Accounting Closing Period Status
Future: Payables allows invoice entry and accounting in a Future period. Payables does not allow payment entry or payment voiding in a Future period. Payables allows you to limit the number of Future periods based on the number you enter in the Future Periods field in the Financials Options window. After you change the status of a Future period to Open, you cannot change it back to Future.
Open: You can record transactions and account for them in an Open period. You cannot open a period if it is an adjusting period. You define adjusting periods using the Define Calendar window. Adjusting periods can have overlapping dates.
Closed: Payables does not allow transaction processing in a Closed period. You can reopen a Closed period if the corresponding general ledger and purchasing periods are also Open. You cannot close a period in which any of the following conditions exist:
• Outstanding payment batches.
• Future dated payments for which the Maturity Date is within the period but that still have a status of Issued.
• Unaccounted transactions.
• Accounted transactions that have not been transferred to general ledger.
Permanently Closed. Payables does not allow transaction processing in a Permanently Closed period. You cannot reopen a Permanently Closed period.
What is difference between debit memo and credit memo?
Credit Memo: Credit Memos are memos from a supplier representing a credit amount toward goods or services. Credit memos are always negative amounts.
Debit Memo: Debit Memos are invoices you enter to record a credit amount for a supplier who does not send you a credit memo. Always negative amount.
Debit Memo: Debit Memos are invoices you enter to record a credit amount for a supplier who does not send you a credit memo. Always negative amount.
Wednesday, October 14, 2009
What is AWR( Automatic workload repository ) and How to generate the AWR report?
Automatic workload repository is a collection of persistent system performance statistics owned by SYS.
It resides in SYSAUX tablespace. By default snapshot are generated once every 60min and maintained for 7 days by default.
you can generate AWR reports by running SQL scripts:
1)The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
2)The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
3) The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
4) The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
5) The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
6) The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Few Views which helps while generating the AWR report
DBA_HIST_SNAPSHOT
DBA_HIST_WR_CONTROL
DBA_HIST_BASELINE
How to Modify the AWR SNAP SHOT SETTINGS:
=====================================
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
Creating the Baseline:
======================
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/
Dropping the AWR baseline:
==========================
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
baseline_name => 'AWR First baseline');
END;
/
Dropping the AWR snaps in range:
================================
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/
Creating SNAPSHOT Manually:
===========================
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/
Workload Repository Views:
==========================
The following workload repository views are available:
* V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
* V$METRIC - Displays metric information.
* V$METRICNAME - Displays the metrics associated with each metric group.
* V$METRIC_HISTORY - Displays historical metrics.
* V$METRICGROUP - Displays all metrics groups.
* DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
* DBA_HIST_BASELINE - Displays baseline information.
* DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
* DBA_HIST_SNAPSHOT - Displays snapshot information.
* DBA_HIST_SQL_PLAN - Displays SQL execution plans.
* DBA_HIST_WR_CONTROL - Displays AWR settings.
It resides in SYSAUX tablespace. By default snapshot are generated once every 60min and maintained for 7 days by default.
you can generate AWR reports by running SQL scripts:
1)The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
2)The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
3) The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
4) The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
5) The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
6) The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Few Views which helps while generating the AWR report
DBA_HIST_SNAPSHOT
DBA_HIST_WR_CONTROL
DBA_HIST_BASELINE
How to Modify the AWR SNAP SHOT SETTINGS:
=====================================
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
Creating the Baseline:
======================
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/
Dropping the AWR baseline:
==========================
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
baseline_name => 'AWR First baseline');
END;
/
Dropping the AWR snaps in range:
================================
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/
Creating SNAPSHOT Manually:
===========================
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/
Workload Repository Views:
==========================
The following workload repository views are available:
* V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
* V$METRIC - Displays metric information.
* V$METRICNAME - Displays the metrics associated with each metric group.
* V$METRIC_HISTORY - Displays historical metrics.
* V$METRICGROUP - Displays all metrics groups.
* DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
* DBA_HIST_BASELINE - Displays baseline information.
* DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
* DBA_HIST_SNAPSHOT - Displays snapshot information.
* DBA_HIST_SQL_PLAN - Displays SQL execution plans.
* DBA_HIST_WR_CONTROL - Displays AWR settings.
Thursday, October 8, 2009
Legal Entity
The Legal Entity represents a legal company for which the user prepares tax reports. Tax identifiers and other relevant information are assigned to this entity.
Wednesday, October 7, 2009
What is the difference between gl_date,gl_posted?
Gl_date is the date the transaction took place. it can be the date when the journal entry was made or the interface was run to populate this data.
Gl_posted is the date when these entries were posted to gl (usually by running post journal entries program)
Gl_posted is the date when these entries were posted to gl (usually by running post journal entries program)
What are the journal entries in Procure to Pay Cycle?
1. On Entry of PO ________ No accounting impact
2. On Materil Receipt
Receiving Accural Dr
AP Accural Cr
3. On Completion of Inspection and goods accepted
Inventory Dr
Receiving Accural Cr
The Above mentioned entries in PO Module
4. On entering invoice in AP
AP Accural Dr
Accounts Payable Cr
5. On Payment
Accounts Payable Dr'
Cash/Bank Cr
2. On Materil Receipt
Receiving Accural Dr
AP Accural Cr
3. On Completion of Inspection and goods accepted
Inventory Dr
Receiving Accural Cr
The Above mentioned entries in PO Module
4. On entering invoice in AP
AP Accural Dr
Accounts Payable Cr
5. On Payment
Accounts Payable Dr'
Cash/Bank Cr
What Is AIM In Oracle Financials? Where we can use this AIM?What is Role of AIM while Implementation Project?
AIM is not a Module in ORA Apps like GL AP AR etc . It is an Implementation
Methodology which is recommended by Oracle for the successful Implementation of
Oracle E-Business Suite Projects.
In AIM (Application Implementation Methodology) there will be blank templates
available with the Software which will help us in proper Planning and Systematic
Implementation of the project by filling appropriate templates according to the
Modules we Implement. The concerned documents in general for
Functional: BR100(Setup Doc) TE40(Testing Doc) and MD50(Module Design)
Technical: CV100 and MD70.
You People better catch this Link
www . oracleappsblog . com /index.php/weblog/comments/download-oracle-aim-applications-implementation-methodology-software-paper/
to get AIM Software which contains all the required Doc's.
Methodology which is recommended by Oracle for the successful Implementation of
Oracle E-Business Suite Projects.
In AIM (Application Implementation Methodology) there will be blank templates
available with the Software which will help us in proper Planning and Systematic
Implementation of the project by filling appropriate templates according to the
Modules we Implement. The concerned documents in general for
Functional: BR100(Setup Doc) TE40(Testing Doc) and MD50(Module Design)
Technical: CV100 and MD70.
You People better catch this Link
www . oracleappsblog . com /index.php/weblog/comments/download-oracle-aim-applications-implementation-methodology-software-paper/
to get AIM Software which contains all the required Doc's.
What is SWEEP Program?Explain Process Of Sweep Program?
This particular program is run in order to transfer un-accounted invioce to next opened period during period end closing of Accounts Payable.
In fact you can't close Payable Period if you have Un-Accounted Invoice in Payables.
In order to negotiate (Transfer) these invoice to next open period this program is run. So that the Payable period can be closed.
In fact you can't close Payable Period if you have Un-Accounted Invoice in Payables.
In order to negotiate (Transfer) these invoice to next open period this program is run. So that the Payable period can be closed.
Explain if translation is necessary when using multi reporting currency?
First let us see what is Translation? Translation is a process that allows you to restate your financial functional currency into a reporting currency.
Now let us see what is MRC? MRC is a unique feature in Oracle that allows you to maintain and report in more than one functional currency.
So with MRC you can report in more than one currency than why do you have to do translation. If the report is a consoliated report than yes translation is necessary.
Now let us see what is MRC? MRC is a unique feature in Oracle that allows you to maintain and report in more than one functional currency.
So with MRC you can report in more than one currency than why do you have to do translation. If the report is a consoliated report than yes translation is necessary.
What is Automatic Offset ?
- It is a Accounts Payable feature & quite similar to Intercompany function in General Ledger.
- It is used to Create multiple Liability lines for the transactions between sister concerns (May be between sister companies).
- For Ex: Say two companies purchasing goods from same supplier. But When invoice is created only one supplier liability line is created whereas both the companies are liable to pay.
- Automatic Offset function helps here to create multiple liability lines to balance the journal at balancing level.
You can set this option at Accounting area of Payable Option.
There are three radio buttons 1. None 2. Accounts & 3. Balancing under Automatic Offset. On selecting.............
1. None: No Automatic Offset will be done
2. Accounts: The distribution line segments will be copied to the liability lines excepting Accounts segment which copied from the liability line itself.
3. Balancing: Only Balancing segment from distribution line segments will be copied to the liability lines excepting Accounts segment which copied from the liability line itself.
What is the navigation for applying Cross-Validation?
Cross-Validation Rules :cross-validation rules Determain which combination of Valid segment values are invalid regrdless of the user s responsbility.
1.Enable The Cross-Validation Rules at Structure level
Nav:setup > Financials > Flefields > Key > segment.
2.Define the Cross-Validation Rules
Nav:setup > Financials > Flefields > Key > Rules
1.Enable The Cross-Validation Rules at Structure level
Nav:setup > Financials > Flefields > Key > segment.
2.Define the Cross-Validation Rules
Nav:setup > Financials > Flefields > Key > Rules
What you meant Retained Earnings? When the Entries are created and what is the entry?
It refers to the earnings or loss that the company retains at the end of the year/end of the period.
It is basically the portion of net income from end of the year which is retained by a corportation.
If the net income is positive then profits for that period are retained.
If the net income is negative then losses for that period are retained
The Retained Earnings is retained from business or the total profits generated in a financial year which is not distributed to shareholders..
It is basically the portion of net income from end of the year which is retained by a corportation.
If the net income is positive then profits for that period are retained.
If the net income is negative then losses for that period are retained
The Retained Earnings is retained from business or the total profits generated in a financial year which is not distributed to shareholders..
What is flex field qualifier? Explain the types of flex field qualifiers
Oracle Applications require flexfield qualifiers for some of the key flex field structures.
There are total of four segment qualifiers
a) balancing segment
b) natural account segment
c) cost center segment
d) intercompany segment
Balancing segment - determines the segment level at which a balanced financial transaction occurs. Meaning total debits must equal to total credits. Typically COMPANY segment would be your balancing segment.
Natural segment - Natural segment is nothing but the account heads type. In other words natural segment determines the account type. There are five types of account types assets liabilities expenses revenue and owner's equity. Typically ACCOUNT segment would be your natural segment.
Also, Flexfield qualifiers are required for certain key flexfield segments.
For example each accounting flexfield structure requires two segments qualifiers which are balancing segment and natural account segment
There are total of four segment qualifiers
a) balancing segment
b) natural account segment
c) cost center segment
d) intercompany segment
Balancing segment - determines the segment level at which a balanced financial transaction occurs. Meaning total debits must equal to total credits. Typically COMPANY segment would be your balancing segment.
Natural segment - Natural segment is nothing but the account heads type. In other words natural segment determines the account type. There are five types of account types assets liabilities expenses revenue and owner's equity. Typically ACCOUNT segment would be your natural segment.
Also, Flexfield qualifiers are required for certain key flexfield segments.
For example each accounting flexfield structure requires two segments qualifiers which are balancing segment and natural account segment
What is the difference between Normal calendar and special calendar?
In GL we can define 2 type of calendars. One is Calendar (normal Calendar)and another Fiscal calendar.
Normal calendar uses the year in which an accounting period begin for the system name .
Fiscal Year uses the year in which your fiscal year ends.
In case of special calendars they will defined in AP module as type Recurring invoice payment terms withholding tax key indicator.
Recurring invoice payment terms withholding tax type calendar used for respective transactions as name indicates. Key indicator type calendar used for key indicator report
Fiscal Year uses the year in which your fiscal year ends.
In case of special calendars they will defined in AP module as type Recurring invoice payment terms withholding tax key indicator.
Recurring invoice payment terms withholding tax type calendar used for respective transactions as name indicates. Key indicator type calendar used for key indicator report
Subscribe to:
Posts (Atom)
