Oracle Apps Technical Real Time Faq's - 04


1.    How to checks the request states?
*       A PL/SQL procedure can check the status of a concurrent request by calling.
ü  FND_CONCURENT.GET_REQUEST_STATUS
ü  FND_CONCURRENT.WAIT_FOR_REQUEST
ü  FND_CONCURRENT.GET_REQUEST_STATUS
*       This function returns the status of a concurrent request
*       If the request is already computed, it also returns the completion message.
*       This function returns both user friendly (translatable) phase and status values as well as developer phase and status vales that can drive program logic.
Syntax à Function FND_CONCURRENT.GET_REQUEST_STATUS
            (request_id in outnumber,
            Application in varchar2 default null,
            Program in varchar2 default null,
            Phase out varchar2,
            Status out varchar,
            dev_phase out varchar2,
            dev_status out varchar2,
Message out varchar2) return BOOLEAN;
*       When application and program are specified, the request_id of the last request for the specified program should be returned to request_id.
*       Phase and status values should be taken from  FND_LOOKUPS
dev_phase                         dev_status
Pending                              normal, standby, scheduled, paused
Running                              normal, waiting, resuming, terminating.
Complete                            normal, Error, warning, cancelled, terminated
Inactive                              disabled, on-hold, No-manager, suspended
Ø  FND_REQUEST.WAIT_FOR_REQUEST
*       This function waits for request completion, then returns the request phase/status and completion message to the caller.
*       Goes to sleep between checks for request completion.
Syntax àFND_CONCURRENT.WAIT_FOR_REQUEST
            ( request_id in number default null,
            interval in number default 60,
            max_wait in numbe default 0,
            phase out varchar2,
            status out varchar2,
dev_phase out varchar2,
dev_status out varchar2,
message out varchar2) return BOOLEN;
Ø  FND_CONCURRENT.SET_COMPLETION_STATUS
*       This function should be called from a concurrent program to set its completion states.
*       This function returns TRUE on success, other wise FALSE.
Syntax à function FND_CONCURRENT.SET_COMPLETION_STATUS
                     ( status in varchar2,
                               message in varchar2) return BOOLEAN;
normal status warning message à any message Error

2.    How to generate fmx at OS level?
*       Forms can be generated on the forms server as the APPLMGR user.
*       Generating the form on the Form server, $ FORM60_PATH Should be set and the current directory should be $AV_TOP/forms us.
Syntax à $F60 gen userid=apps/appsp     module=<form_name>. Fmb
            Output_file=<schema_top>/forms?<language>/<form_name>.fmx
            Module_type=form bath =no compile_all=special
3.    How to convert a form from 4.5 to 6.0?
*       To upgrade forms, the form can be directly compiled in the next release.
*       Form can be compiled by using if cmp 60.exe
*       FLINT 60 bath executable can be used to check whether the form is compatible to Apps or not.
4.    How to call a form from another form?
*       To invoke another form with in a form the function security routines should be used which are available in FND_DUNCTION package.
*       For this purpose, CALL_FORM built-in cannot be used since the Oracle Applications libraries do not support it.
*       FND_FUNCTION.EXECUTE should be used to open a new session of a form (CALL_FORM/ OPEN_FORM do not be used)
*       APP_NAVIGATE.EXECUTE procedure also can be used to open a form where an instance of the same form is reused, that has already been opened.
*       APP_NAVIOGATE.EXECUTE is similar to FND_FUNCTION.EXECUTE, except that is allow a form to be restarted if it is invoked a second.
*       FND_FUNCITON.EXECUTE always starts a new instance of a form.
Syntax à procedure APP_NAVIGATE.EXECUTE
            (Function_name in varchar2,
            Open_flag in varchar2 default ‘y’
            Sesson_flag in varchar2 default ‘SESSION’
            Other_params in varchar2 default null,
            Activate_flag in varchar2 default ‘ACTIVATE’
            Pinned in Boolean default FALSE);
Syntaxà Procedure FND_FUNCTION.EXECUTE
            (Function name in varchar2,
             Open flag in varhcar2 default ‘y’
             Session_flag in varhcar2 default ‘session’
            Other_params in varchar2 default null,
            Activate in varchar2 default ‘Active’
            Browser_target in varchar2 default null);
5.    What is the reason for not getting any data when a multi org view is queried?
*       To get the data correctly, the xxx-ALL must be referenced and the ORG_ID value should be specified to extract portioned data.
*       Multiorg views are partitioned by using ORG_ID.
*       So access through multiorg views will not return any roes, as the CLIENT_INFO Value is not set       
*       Use HR_OPERATING UNITS to identify the organization _id of the OU on which query is based.
*       Use FND_CLIENT_INFO package to set the value in CLIENT INPO using set_org_contest.
*       Execute fnd_client_info. Set_org_context (‘<org_id>’);
*       Now qurying of multiorg views can be done.

6.    How do you find that Multi-org is installed?
*       Multi organization architecture is meant to allow muliple companies or subsidiaries to store their records within a single data base.
*       Multiple organizations Architecture allows this by partitioning data through views in APPS schema.
*       Implementation of Multi org generally includes more than one business group.
*       To know whether Multiorg is existing or not
Select multi_org_flag
Form fnd_product_groups)
*       if the result is ‘Y’ means the database is group for Multiorg


7.    What are the triggers that fire on item?
1.    Pre_Text_Item
2.    when_New_Item_Instance
3.    post_text_Item
4.    post_Change
5.    When_validate_Item
6.    key_Next_Item
*       execute fnd_client_info.set_org_contest (‘Org_Id’)
*       execute dbms_application_info.set_client_info (‘Org_Id’)
8.    Transactional triggers in forms
*       Transactional triggers are the triggers that are related to accessing a data source.
*       These triggers fire for each record that is marked for insert, update or table when forms would typically insert. Update of delete statements.
*       Internally forms would be calling its internal insert_record, update_Record and Delete_Record built_ins as appropriate to perform the default processing.
*       Importantatn Transaction triggers are
1.    ON_LOCK
2.    ON_UPDATE
3.    ON_INSERT
4.    ON_DELETE

9.    Which triggers will fire when censor moves from one block to another block?
Trigger Firing Order                                                Level
  1. Post_Test_Item                                           Item
  2. Post_Record                                               Block
  3. Post_Block                                                 Block
  4. When_Create_Record                                   Block
  5. Pre_ Block                                                  Block
  6. Pre_Record                                                Block
  7. Pre_Text.Item                                             Block
  8. When_New_Block_Instance                           Block
  9. When_New_Recrd_Instance                           Block
  10. When_new_Item_Instance                           Form
10. What is the difference between PRE_COMMIT and POST_COMMIT triggers?
*       POST_FORMS_COMMIT triggers are the new name for the POST_COMMIT triggers.
*       When a form is being committed the following triggers are fired
(i) PRE_COMMIT (ii) ON_COMMIT (iii) POST_COMMIT

Pre – Commit Trigger

*       This triggers fires once during the Post and Commit transaction process. Before form builder processes any (changes) records to change.
*       Specifically it fires after form builder determines that there are inserts, updates or deletes in the form to post or commit, but before it commits the changes.
*       This trigger doesn’t fire when there is an attempt to commit, but validation determines that there are no changed records in the form.
*       This is a form level trigger.
*       Enter query mode should be set as ‘No’
*       This can be used to perform an action, such as setting up special locking requirements, at any time a database commit is going to occur.
*       If this trigger fails the post and commits processes fail, no records are written to the database and focus remains in the current item.
*       If a DML is performed in a pre-commit trigger and it fails, ten manual rollback must be performed, because form builder doesn’t perform an automatic roll back.
*       This trigger fires in post and commit transactions.

Post – Commit Trigger

*       This is also known as post-commit trigger.
*       Post-commit trigger fires once during the post and commit transactions.
*       If there are records in the form that have been marked as inserts, updates or deletes, the post-forms-commit trigger fires after these changes have been written to the database but before form builder issues the database commit to finalize the transaction.
*       If the operation or application initiates a commit when there are no records in the form have been marked as inserts, updates or deletes, form builder fires post-forms-commit trigger immediately, without posting changes to the database.
*       This is a form level trigger.
*       Enter query mode should be set to ‘No’
*       Post-forms-commit trigger should be used to perform an action, such as updating an audit trial any time a database commit is about to occur.
*       If this trigger fails, post and commit processing aborts and form builder issues a ROLLBACK and decrements the internal save point counter.
*       This triggers fires in Post and Commit transactions.

11. What is Recode function?
*       Decode function decodes an expression in a way similar to the IF_THEN_ELSE logic used in various languages.
*       Decode function decodes expression after comparing it to each search condition.
*       If the expression is the same as search, result is returned.
*       If the default value is committed, a null value is returned where a search value does not match any of the result values.
*       DECODE function facilitates conditional inquiries by doing the work of a CASE or IF_THEN_ELSE statement.
*       DECODE (column, expression, search1, result1, search2, result2…); 
Ex: - Select last_name, job, alary,
*              DECODE (‘IT_PROG’, 1.10*SALARY,
                     ‘ST_CLERK’, 1.15*SALARY,
                     ‘ST_REP’, 1.20*SALARY,
                     SALARY) REVISED_SALARY from employees;
12. How to call a Report in two applications?
*       SRW Package is a collection of PL/SQL constructs that contain many functions, procedures and exceptions that can be referenced in Reports.
1 SRW.Break                                          2. SRW.Context_Failure
3 SRW.Do_SQL                                        4. SRW.Do_SQL_Failure
5 SRW.Get_Page_Num                    6 SRW.Message
7 SRW.Program_Abort                              8 SRW.Refrence
9 SRW.Run_Report                                  10 SRW.Run_report_Failure
11 SRW.Set_Altr                                      12 SRW.Integer_Error
13 SRW.Set_Field_char                             14 SRW.Set_Field_Num
15 SRW.Set_Maxrow                                16 SRW.Trace_Add_Option
17 SRW.Trace_End                                  18 SRW.Trace_Start
19 SRW.User_Exit                                    20 SRW.User_Exit_Failure

 

SRW.Run_Report:

ü  SRW.Run_Report (command_line, char);
ü  Executes specified R25 RUN Command
ü  SRW.RUN_REPORT_FAILURE;
ü  Stops report execution when failure of SRW.Run_Report occurs.
ü  By using SRW.Run_Report, another report can be called to the screen from a button within one report.
ü  If this is used from a Report Trigger, BATCH=YES must be passed.
ü  DESTYPE can only be FILE, PRINTER or MAIL.
Ex: - Function F1 return Boolean is
            Begin
            SRW.RUN_REPORT (‘Report=Rep_A P_Param1=20’);
Calls Report Rep-A and displays to screen
Passes a parameter 20 to the param_1
Exception
When SRW.RUN_REPORT_FAILURE Then
SRW.Message (100, ‘Error Calling Report’);
Raise SRW.Program_Abort;
Return True;
End;
ü  SRW.DO_SQL (SQL statement char);
                      Executes specified SQL statement
ü  SRW.DO_SQL_FAILURE;
Stops report execution upon SRW.Do_SQL failure.
ü  SRW.Message (msg_number number, msg_text char);
Displays a specified message and message number
ü  SRW.Program_Abort;
Stops execution of report when raised.
ü  SRW.Set_Altr
Applies attribute settings, such as font, color to lay out objects.
This procedure applies formatting attributes to the current frame, repeating frame, field or boiler plate object.
ü  SRW.Set_Altr (object_id number, altr SRW.Generic_Altr);
            Object _id is always zero.
Altr is SRW.Altr (that is, the attributes to change)

SRW.set_Field

ü  The procedures in this package are very useful in format triggers.
ü  They are used to change data that will be displayed ion a particular item based on a specific condition.
SRW.Set_Field_char (Object_id, text char);
SRW.Set_Field_Date (Object_id, date date);
SRW.Set_Field_ Num (Object_id, number number);

13. Can a Report contain more than one template?
*       Templates define common characteristics and objects that can be applied to multiple reports.
*       For example template can be defined that include the company logo and sets font colors for selected areas of a report.
*       When a report is created through the Report Wizard, there is an option of applying a template (.rdf file) to the report.
*       When a template is chosen, objects in the margin area of a template are imported into the same locations in the current report section, over writing any objects if exists.
*       The characteristics of the objects in the body area of the template are applied to objects in the body area of the template are applied to objects in the body area of the current report section.
*       Any template properties, parameters, report triggers, program units and attached libraries are also applied.
*       Different (Multiple) templates can be applied to each section of the report.
*       If another template is applied later to a report the existing template objects will be deleted in the current report section.

14. How to add a template to the predefined templates list?
             i.    In a text editor open the Preferences File
            ii.    Scroll down to the template descriptions identified by Reports. Xxx – Template_Reso (who xxx specified a Report style) (Tabular, Break Above)
           iii.    For each Report style for which the template is defined
ü  To the Reports.xxx – Template_Disc list, add the description that should be appeared on the template page of the report wizard.
ü  To the corresponding Reports. XXX_template_file list, add the file name of the template in the same position as the addition that is made to the description list.
           iv.    copy the template file (file name.tdf) to
ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US
Preferences file:-
            Windows   à ORACLE_HOME\CAUPREFS.ORA (user preferences)
            ORACLE_HOME\CAGPREFS.ORA                     (Global Preferences)
UNIX à HOME_DIRECOTRY/Prefs.ora             (User preferences)
            $ORACLE_HOME/tools/admin/prefs.ora            (Global Preferences)
15. How to pass a parameter in a request set for three concurrent programs which are having same parameter?
             i).   For the first report in the Report set, click on the parameters button and ender the parameters that are to the shared by all reports in the Request (Report) set.
            ii).   Go to the nest report and click the parameters button and list the same shared parameters.
           iii).   Do the above step for each and every report in the request set
           iv).   ’Modify’ check box can be used to allow the users to change the values of the parameters in the ‘lower ’reports at submission time..
            v).    ‘Display check box can be used to allow the users to see the parameter values at submission time.
           vi).   Request set wizard can be used to quickly create a new Request set in which all of the request run sequentially or the entire request run in parallel.
            Sequentially à One after another
            Parallel à All at once.
          vii).   The action can be set whether to continue processing or abort processing. If a request ends with the statues ‘Error’.

16. What are Global variables in Reports?
Global variables are the variables that ca n be assigned to parameters in reports and those parameters can be used in reports
            create_parameter_list (------)
            add_parameter (----:Global_var);
            run_product (….);
17. What are Handlers?
*       Handler is a group of packaged procedures which is used by Oracle Applications to organize. PL/SQL code in forms.
*       Handlers provide a way to centralize the code so that it becomes easier to develop, maintain and debug.
*       The packaged procedures available in a handler are called form the triggers by passing the name of the trigger as an argument for the procedure to process.
*       Handlers are types:-        1) Item Handlers
                                         2) Event Handlers
                                         3) Table Handlers     
                                         4) Business Rules
*       Handlers reside in program units in the form or in stored packed in the database.

Adding Table handler Logic

ü  Coding logic for window and alternative region control.
ü  Adding fin-windows and/or ROW-LOV’S and enables query-find.
ü  Coding logic for item relations such as dependent fields.
ü  Coding messages to use message dictionary.
ü  Adding FF logic if required.
ü  Adding choices to the special mince and logic to modify choices the default menu and tool bar behavior is necessary.
ü  Coding any other logic.
ü  Creating a form function for the developed form and registering any sub functions.
ü  Testing the form by itself.
ü  Registering the form with AOL.
ü  Adding the form function to a menu or creating custom mence.
ü  Assigning the menu to the responsibility and assigning the responsibility to the user.
ü  Testing the form within Oracle Applications.

18. Registering of Application, form and a concurrent program through Application developer Responsibility
Application:-
Responsibility à Application Developer
<Application / Register >
Form:-
<Application / Form>
<Application / Function>
Menu:-
  <Application / Menu>
Messages:-
  <Application / Messages>
Table:-
  <Database /Table>
Sequence:-
  <Database / View>
Concurrent Program:-
  <Concurrent / Executable>
  <Concurrent / Program>

Application Developer (Responsibility):
ü  Flex field:
            +Key
            +Descriptive
            -Test
ü  Concurrent
               -Program
            -Executable
            -Library
ü  Application         
            -Register
            -Form
            -Function
            -Menu
            -Messages
            +Database
            +Lookups
            +Validation

Profile:
Attachments:
ü  Document Entities
ü  Document Categories
ü  Attachment Functions
Other:
Requests:
            - Run
            -Set
            -Profile
            -Concurrent
            -Change Organization
            -Running Jobs


+Key                                                     +Descriptive
  -Register                                              -Register
  -Segments                                           -Segments
  -Aliases                                               -Values
  -Cross Validation                                  
  -Values                                                +Lookups
  -Groups                                               -Application Object Library
  -Accounts                                             -Common
+Database                                             +Validation
  -Table                                                 -Set
  -View                                                  -Values
  -Sequence

ü  Lexical references cannot be made in Pl/SQL statements.
ü  Bind references can be done in PL/SQL statements.
ü  Lexical parameters can be referenced by entering an ampersand (    ) followed immediately by the column name or parameter.
ü  Before creating the query, a column or parameter in the data model should be created for each lexical reference in the query.
ü  For lexical parameters, initial value must be defined so that report builder uses this value to validate the query with a lexical reference.
Token
ü  If Oracle reports are executed by a concurrent program, (for Oracle Reports Program), then a keyword or a parameter with the same name as in the report builder, should be defined which for each parameter, which is known as taken.
ü  This is used to pass the parameters to the reports from the application (SRS Window)
Request Set
ü  Request set is the group of requests, that can be submitted regularly using a single transaction.
Incompatibility
ü  These are the list of programs that can be defined as incompatible with a pertain program.
ü  If any program is defined as incompatible to a particular program, then that program should not run simultaneously with the concurrent program, because they might interfere with its execution.

Application Developer Responsibility:
Various Screens
Different Executable Methods:
1                                                 Host
2                                                 Immediate
3                                                 Java Stored Procedure
4                                                 Java Concurrent Program
5                                                 Multi Language Function
6                                                 Oracle Reports
7                                                 PL/SQL stored Procedure
8                                                 Request set stage function
9                                                 Spawned
10                                              SQL*Loader
11                                              SQL*Plus
<Concurrent/Library>           Concurrent Library
Library types                    
Transaction Library
<Lookups>                         User
Access Levels                   
Extensible
System
<Validation/Set>                  List of values
List types                         
Long List of Values
Pop list                                               
No security                       Security type                    
Hireaxhial Security              Non-hireaxhial Security       
Char
Format type              Date
                               Date time
                               Number
                               Standard date
                               Standard date time
                               Time
Validation types à      Respondent   
                               Independent
                               None
                               Pair
                               Special
                               Table
                               Translatable Independent
                               Translatable Dependent

<Attachments / Attachment Functions>
Function           type            
Form              Report 

19. What is a Data Group?
*       A data group is a group of oracle applications and the Oracle ID’s of each application
*       Oracle ID grants access privileges to tables in an Oracle Database
*       Data group determines which Oracle Data base accounts responsibilities forms, concurrent programs and reports connect to.

20. What is a Responsibility?
*       Responsibility defines Applications Privileges
*       A responsibility is a level of authority in Oracle Applications that lets users only those Oracle Applications functions and data appropriate to their roles in an organization.
*       Each user has at list one or more responsibilities and several users can share the same responsibility
*       Each responsibility allows access to a specific application or a set of applications.
*       A set of books
*       A restricted list of windows that an user can navigate
*       Reports in a specific application.




No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...