PL/SQL - FAQ 2

1.    TEMPLATE FORM 

Ø  The TEMPLATE form is the required starting point for all development of new forms.

Ø  The development of a new form is started by copying the TEMPLATE.fmb file, located in $ AV_top / forms/ us, to a local directory and renaming it as appropriate.

TEMPLATE FORM CONTAINS THE FOLLOWING:-

ü  Platform-independent references to object groups in the APPSTAND form

                     (STANDARD _PCAND_VA,

                      STANDARD_TOOLBAR,

                     STANDARD_CALENDAR)

ü  platform – independent attachments of several libraries 

            FND SQF

            APPCORE

            APPDAYPK

ü  Several form level triggers with requited code

ü  Program units that include a specification and a body for the package APP_CUSTOM, which contains default behaviors for window opening and closing events.

Ø  In general this code should be modified for the specific form under development.

ü  The application’s color pallet, containing the two colors required by the referenced visual attributes.

ü  Many referenced objects that support the calendar, the toolbar, alternative regions and the menu.

ü  Template form contains simple objects that show typical items and layout cosmetics.

Ø  These are deleted after developing the form

Blocks: Block Name, Detail Block

Window: Block Name

Canvas _view: Block Name

ü  Template form includes plat form-independent attachments of several libraries.

Ø  Some of the libraries are attached directly to the TEMPLATE (FNDSOF, APPCORE, APPDAYPK) while the others are attached to these libraries.

APPCORE, APPDAYPK, FNDSOF, CUSTOM, GLOBE, VERT, JA, JE, JL

APPCORE

Ø  Contains the packages and procedures that are required of all forms to support the Menu, Toolbar and other required standard behaviors.

Ø  Procedures and functions in APPCORE have names beginning with APP.

APPDAYPK

Ø  Contains the packages that control the Oracle applications calendar feature.

FINDSQF

Ø  Contains packages and procedures for message dictionary, flor fields, profiles and concurrent processing.

Ø  It also has various other utilities for navigation, MRG, WHO etc.

Ø  Procedures and functions have names beginning with FND.

CUSTOM

Ø  Custom library allows extension of Oracle Applications forms without modification of Oracle applications code.

Ø  Custom library can be used for customizations such as ZOOM (Moving to another form from one form and querying up specific records), enforcing business rules.

Ex: - Supplier name must be in upper case) and disabling fields that are not required for a Particular site.

Ø  All logic must branch based on the form and block for which it is run.

Ø  Oracle applications send events to the Custom library.

Ø  Custom code can take effect based on the events.

2.    What is sub query and correlated sub query?

Sub Query:-

Ø  A Sub Query is a SELECT statement that is embedded in a clause of another SQL statement called the parent statement.

Ø  Sub query (Inner Query) returns a value that is used by the outer query.

Ø  Scalar sub query is a sub query that returns exactly one column value from one row.

Correlated Sub Query

Ø  Correlated sub query are used for row – by –row processing.

Ø  Each sub query is executed once for every row of the outer query.

Ø  A correlated sub query is one way of reading (data) every row in a table and comparing values in each row against related data.

Ø  Oracle server performs correlated sub query when the sub query references a column from a table in the parent query. 

Ø  The inner query is driven by the outer query in correlated sub queries.

Ø  A correlated sub query is evaluated once for each row processed by the parent statement.

                                                  GET

                                    Candidate row from the outer query

                                                EXECUTE

                                Inner query using the candidate row value

                                                      USE

                     Values from inner query to qualify / disqualify candidate row

Ex: - Select last_name, salary, department_id from employers OUTER

       Where salary> (select AVG (Salary) from employees

       Where department id = outer.department_id);

3.    Types of Joins

  1 Equi Join                       

  2 Non-Equi Join

  3 Outer Join

  4 Self join

EQUI JOIN:-

Ø  Is also called simple or inner join.

Ø  An Equi join is a join condition that contains equality sign.

NON EQUI JOIN:-

Ø  It is a join condition that contains something other than the equality operator.

OUTER JOIN:-

Ø  Outer joins are used to also see rows that do not meet the join condition.

Ø  Outer join operator is (+).

Ø  The missing rows can be returned if an outer join operator is used in the join condition.

Ø  The operator (+) is placed on the side of the join that is deficient in information.

Ø  This operator has the effect of creating one or more null rows to each one or more rows from the (join condition) non deficient table can be joined.

SELF JOIN

Ø  Self join is used to join a table to itself.

Ex: - to find the name of each employer’s manager.

4.    Which trigger will fire when cursor moves from one block to another block?

WHEN_NEW_BLOCK_INSTANCE

5.    What are the triggers used in CUSTOM.Pll?

CUSTOM.Pll contains (CUSTOM package) the following functions and procedures.

CUSTOM.ZOOM_AVAILABLE à FUNCTION

CUSTOM.STYLEà FUNCTION

CUSTOM.EVENTà PROCEDURE.

Triggers in Custom.Pll:-

1.    WHEN _NEW_FORM_INSTANCE

2.    WHEN_FORM_NAVIGAT

3.    WHEN_NEW_BLOCK_INSTANCE

4.    WHEN_NEW_RECORD_INSTANCE

5.    WHEN_NEW_ITEM_INSTANCE

6.    ZOOM

7.    EXPORT

8.    SPECIAL 1-45

9.    KEY_Fn (n is a number between 1 and 8)

6.    What is the difference between pre-query and post -query?

Pre-query executes only once for the statement where as post-query executes for each record.

7.    List of some API’S

FND_PROGRAM.EXECUTABLE

FND_PROGRAM.REGISTER

FND_PROGRAM.PARAMETER

FND_PROGRAM.ADD_TO_GROUP

FND_REQUEST.SUBMIT_REQUEST

FND_PROFILE.VALUE

FND_PROFILE.GET

8.    How to get second parameter value based on first parameter?

  $flex $ value setname.

9.    What is Ref Cursor

       Ref cursor is a data type and executed at server side and with ref cursor multiple select statements can be executed.

       By increase the binary size, that number of records that are committed can be increased by using control file.

10. Forms can be development in APPS in two ways

A)       Customization by extension (using template.fond)

B)       Customization by modification (using custom.pll)

11. What are the advantages of packages?

       Packages bundle related Pl/SQL types, items and sub-programs in to one container.

       A package should have its specification and body, stored separately in the database.

       Specification is the interface to the applications.

       A declares the types, variables, constants, exceptions, cursors and sub programmes available for use. The body fully defines the cursors and sub programmes and so implements the specification.

       Once written and compiled the contents can be shared by many applications.

       When a packaged PL/SQL construct is called for the first time the whole package is loaded in to memory, thus later calls to constructs in the same package require no disk I/O.

       Public package constructs can be referenced from any Oracle server environments.

       Private package constructs can be referenced only by other constructs which are part of the same package.

ADVANTAGES

1.    Modularity

2.    Easier Application Design

3.    Hiding Information by using public and private.

4.    Added functionality.

5.    Better performance.

6.    Over loading.

Procedures and functions can be over loaded i.e. creating multiple sub programmes with the same name in the same package each taking parameters of different number or data type.

12. How to call WHO columns into the form

By using FND_STANDARD API’S

1. FND_STANDARD.FORM_INFO

  Provides information about the form.

  Should be called form when_new_form – instance – instance trigger.

2. FND_standard.set_who

    Loads WHO columns with proper user information.

    Should be called from PRE_UPDTE and PRE_INSERT

    Triggers for each block with WHO fields

    If this is used FND-GLOBAL need not be called.

    (FND_GLOBAL.WHO)

3. FND_STANDARD.SYSTEM_DATE

  This is a function which returns date.

  Behave exactly like SYSDATE built-in.

4. FNID_STANDARD.USER

  This is a function which returns varchar2

  Behaves exactly like built in USER.

APPCORE API’S

APP_COMBO

APP_DATE

APP_EXCEPTION

APP_FIELD

APP_FIND

APP_ITEM

APP_ITEM_PROPERTY

APP_NAVIGATE

APP_RECORD

APP_REGION

APP_STANDARD

APP_WINDOW

FNDSQF API’S

FND_CURRENCY

FND_DATE

FND_GLOBAL

FND_ORG

FND_STANDARD

FND_UTILITIES.OPEN_URL

FND_UTILITIES. PARAM_EXISTS

13. How to call flex fields in the form?

By using FND_FLEX.EVENT (EVENT varchar2)

14. How to register an executable and define a concurrent program through backend?

By using concurrent processing API’S

1. FND_CONC_GLOBAL.REQUES_DATA

                            .SET_REQUEST_GLOBALS

2. FND_CONCURRENT.AF_COMMIT

                          .AF_ROLLBACK

                          .GET_REQUEST_STATUS

                          .WAIT_FOR_REQUEST

                          .SET_COMPLETION_STATUS

3. FND_FILE. PUT

            . PUT_LINE

            .NEW_NAME

            .PUT_NAMES

            .CLOSE

4. FND-PROGRAM. MESSAGE

                     . EXECUTABLE

                     . REGISTER

                     . PARAMETER

                     . IN COMPATIBILITY

                     . EXECUTABLE_EXISTS

5. FND_REQUEST.SET-OPTIONS

                     .SET_REPEAT_OPTIONS

                     .SET_PRINT_OPTIONS

                     .SUBMIT_REQUEST

                     .SET_MODE

6. FND_REQUEST_INFO.GET_PARAM_NUMBER

                               . GET_PARAM_INFO

                               . GET_PROGRAM

                               . GET_PARAMETER

7. FND_SET. MESSAGE

            .ADD_PROGRAM

            .ADD_STAGE

            .IN COMPATIBILITY

8. FND_SUBMIT. SET_MODE

                     .SET_REQUEST_STATUS

                     .SUBMIT_PROGRAM

                     .SUBMIT_SET

9. FND_PROGRAM.EXECUTABLE

ü  is used to define a concurrent  program executable

ü  it takes 8 parameters ( all are IN mode )

Syntax         procedure FND_PROGRAM.EXECUTABLE

(Executable IN varchar2,

(Full name) à application IN varchar2,

(Executable short name) à short name IN varchar2,

                             Description IN varchar2 default null

                            Execution_method IN varchar2,

                           Execution_file_name IN varchar2 default null,

(Only for spawned & Immediate) à subroutine name IN varchar2 default null,

                                        Icon_name IN varchar2 default null,

                                        Language code IN varchar2 default (VS)

(For Java Concurrent Program) à execution_file_path IN varchar2 default null,

10. FND_PROGRAM.REGISTER

ü  This procedure no used to define a Concurrent Program.

ü   It has 30 IN parameters. Out of which 9 are mandatory, the remaining are default.

  Syntax à procedure FND_PROGRAM.REGISTER

            (program                IN varchar2,

             application              IN varchar2,

            enabled                   IN varchar2,

            short_name             IN varchar2,

            description               IN varchar2, default null,

( executable_short_name) à executable_name IN varchar2

                               executable_application IN varchar2,

                               mls_function_shelt_name IN varchar2,

                               mls_function_application IN varchar2,

                               inerementor IN varhcar2);

15. How to register a table and columns through back end?

       By using AD_DD package

ü  For registering a table à AD_DD.REGISTER_TABLE

ü  For registering columns à AD_DD.REGISTER_COLUMN.

ü  AD_DD BPI doesn’t check for the existence of the registered table or column in the data base schema, but only updates the required SQL tables.

ü  It should be ensured that, all the tables and columns registered exist actually and have the same format as that defined using AD_DD API.

ü  Views need not be registered.

16. How to write to a file through concurrent program.

       By using FND_FILE package and it can be used only for log and output files.

ü  FND_FILE package contains procedures to write text to log and output files.

ü  FND_FILE supports a maximum buffer line size of 32k for both and output files.

1.       FND_FILE.PUT

ü  This is used to write text to a file without a new line character

ü  Multilane calls to FND_FILE.PUT will produce consummated text.

ü  Procedure FND_FILE.PUT (which IN Number, Buff IN varchar2);

ü  Which à log output file

ü  Can be FND_FILE.LOG or FND_FILE.OUTPUT.

2.       FND_FILE.PUT_LINE

ü  This procedure as used to write a line of text to a file followed by a new line character.

ü  Procedure FND_FILE.PUT_LINE (which IN number, buff  IN varchar2);

EX: - FND_FILE.PUT_LINE (FND_FILE.LOG, find_message_get);

3.       FND_FILE.NEW_LINE

ü  This procedure is used to write line terminators to a file

ü  Procedure FND_FILE.NEW_LINE (which IN number,

LINES IN NATURAL: =1);

    Ex: - To write two newline characters to a log file

Fnd_file.new_line (fnd_file.log, 2);

4.       FND_FILE.PUT_NAMES

ü  This procedure as used to set the temporary log file and output filenames and the temporary directory to the user specified values.

ü  This should be called before calling my other FND_FILE procedure and only once per a session.

Function FND_REQUEST.SUBMIT_REQUEST

(Application      in varchar2    default null,

  Program        in varchar2    default null,

  Description     in varchar2    default null,

  Start-time      in varchar2    default null,

  Sub_request   in bookan      default False,

  argument1,

arguemnt2,

Argument 100) return number;

ü  If this is submitted from oracle forms, all the arguments (1 to 100) must be specified.

17. How to display Request ID in the reports?

       By using the parameter P_CONC_REQUEST_ID which needs to be defined always in the reports.

18. How to get username / user id in reports?

       By using the API FND_PROFILE THE OPTION values can be manipulated in client / server profile caches

       FND_PROFILE.GET, FND_GLOBAL.USER_ID

                                FND_GLOBAL.USER_NAME

       This procedure is located in FNDSQF library.

       This procedure is to locate to get the current value of the specified user profile option or null if the profile does not exist.

       The server_side PL/SQL package FND_GLOBAL returns the values which need to set who columns for inserts and updates from stored procedures.

       Procedure FND_PROFILE.GET ( name in varchar2, Value out varchar2);

       FND_PROFILE.GET (‘USER_ID’, user_id);

       FND_PROFILE.VALUE

This function exactly works like GET, except it returns the values of the specified profile option as a function result

       Function FND_PROGILE.VALUE (name in varchar2, Return varchar2);

       FND_PROFILE.PUT

ü  This is used to put a value to the specified profile option

ü  If the option doesn’t exist, it can also be created with PUT.

       Procedure FND_GET (‘USERNAME’, user name);

       FND_PROFILE.GET (‘USER_ID’, user_id);

ü  Username, user-id, vsp_id , Appl_shrt_Name, Resp_Appl_Id, Form_Name, Form_id, Form_Appl_Name, Form_Appl_Id, Logon_Date, Last_Lagon_Date, login_id, Gone_Request_Id, Gone_Program_Id,Gone_program_Application_Id, Gone_Login_Id, Gone_Print_Output, Gone_printstyle_ these are the user profile options that can be accessed via user profile option routines .

ü  The values of these profile options can be retrieved in forms, reports and program

ü  These values cannot be changed except Gone_Print_Output and Gone_Print_Style.

ü  SA’s and end users also cannot see the values or change these values.

19. In which directory log and output files are stored?

       APPLCSF is the top level directory in which the concurrent manager puts logs and output files.

       APPLLOG and APPLOUT are the subdirectories in which the concurrent manager puts log and output files.

       APPLCSF variable need to be set in the prod.env (environmental variable ), so that all product log files 

ü  $ APPLCSF/ $ APPLLOG à log files

ü  $APPLCSF/ $ APPLOUT à out files

       Concurrent manager log and output files should go to $ FND_TOP/ $ APPLOG and $ FND _TOP / $APPLOUT if $ APPLCSF is not set.

20. How to submit concurrent programs through OS?

       From the operating system the utility .CONCSUB  is used to submit is concurrent program.

       This is basically used to test a concurrent program.

       By using the WAIT token. The utility checks the request status every 60 seconds and returns the OS prompt upon completion of the request.

       Concurrent manager does not abort, shutdown or start up until the concurrent request completes.

ü  If the concurrent program is compatible with itself, it can be checked for data integrity and dead locks by submitting it many times so that it runs concurrently with itself.

ü  PL/SQL procedures can submit a request to run a program as a concurrent process by calling.

       FND_REQUEST. SUBMIT_REQUEST.

ü  Before submitting a request, the following functions also should be called optionally.

            FND_REQUEST.SET_OPTIONS

            FND_REQUEST.SET_REPEAT_OPTIONS

            FND_REQUEST.SET_PRINT_OPTIONS

            FND_REQUEST.SET_MODE