How can I change in custom.pll? after change how can I send on the server & what’s effect in condition?

1.     FTP the custom.pll form $AU_TOP/resource

2.     Open the custom.pll in form 6i.

3.     Include the following code for your form logic:

Begin

Form_name varchar2(50):= name_in(‘system.current_form’);

Begin

Logic;

End;

4.     Once changeover use the following command to generate custom.plx

F60gen module = custom.pll userid =apps/apps@mfgdev module_type = LIBRARY

5.     To generate the latest custom.plx & move this custom.plx to $AU_TOP/resource.

limitations of forms personalization when compared to CUSTOM.pll?

Can't create record group queries, hence can’t implement LOV Query changes.
Can't make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.

What is the use of CUSTOM Library in Oracle Apps?

Custom Library allows extensions of oracle application modules without modifying module code Custom Library can found in $AU_TOP/res/plsql directory. After designing & developing u must replace ur code in custom library. After ur code has been written u must compile & Generate the library using oracle forms on the application server ( not the development machine ) & place in $AU_TOP/resource directory. .plx (executable code) is created when u generate .pll (librarary code) when u generate a library using the forms generator COMPILE_ALL parameter set to ' YES '.

What are the key benefits of forms personalization over custom.pll?

·         Multiple users can develop forms personalization at any given point in time.

·         It is fairly easy to enable and disable forms personalization’s.

·         A programmer is not required to do simple things such as hide/disable fields or buttons.

·         Provides more visibility on customizations to the screen.

What is disadvantage of NOCOPY Parameter?

However, you need to be extremely careful while using this method in coding for high priority data transactions. When you pass parameters to a procedure by reference, any change that happens on the passed Parameters gets updated in the same memory location as the Actual parameter. So when an exception occurs in a procedure/function, these changes are not Rolled back. To be precise, the PL/SQL engine cannot rollback these parameter changes. So, if the actual values get changed, then it may result in incorrect results.

Thus, to conclude, it is up to a Developer to take a trade-off between using or not using the NOCOPY parameter. NOCOPY could provide better performance by reducing Memory and CPU cost, but at the same time, could result in incorrect results too.

What is NOCOPY parameter

When you use a Collection (Records, VARRAY etc having records of data) as a Return type (OUT or IN OUT parameter) as we know, it will be passed by value only. That means the entire data will be copied to the formal parameter location, which leads to lot of CPU and memory consumption.

Here comes the usage of NOCOPY parameter. Provide NOCOPY after the OUT/IN OUT parameter, which instructs the PL/SQL Engine to avoid using the pass by value method. So, the Engine uses pass by reference instead. This helps reducing a lot of CPU usage as well as memory consumption.

Eg: Just an example how to put the NOCOPY parameter.

PROCEDURE PRC_NOCOPY_EXAMPLE
(
P_EMPNO IN NUMBER,
P_ENAME IN VARCHAR2(50),
P_SAL OUT NOCOPY RECORD_SAL --PL/SQL Record
);
REC_SAL RECORD_SAL;

Now if you call the Procedure PRC_NOCOPY_EXAMPLE, the parameter P_SAL would only use Pass by reference method.

What is pragma and what are the different types of pragma?

Pragma is a compiler directive, which is used to provide an instruction to the compiler.

Four types of Pragma’s:
AUTONOMOUS_TRANSACTION
EXCEPTION_INIT
RESTRICT_REFERENCES
SERIALLY_REUSABLE

PRAGMA INLINE

Autonomous_Transactions: Tells the compiler that the function, procedure, top-level anonymous P/L SQL block, object method, or database trigger executes in its own transaction space.

Exception_Init: Tells the compiler to associate the specified error number with an identifier that has been declared an Exception in your current program or an accessible package.

Restrict_References: Tells the compiler the purity level of packaged program. The purity level is the degree to which a program does not read/write database tables and/or packaged variables.

Serially_Reusable: Tells the runtime engine that package data should not persist between references.  This is used to reduce per-user memory requirements when the package data is only needed for duration of call and not the duration of session.

PRAGMA INLINE: This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.

 

What is Pass by value and Pass by reference?

The PL/SQL has two different methods for passing parameter values between stored procedures and functions.

1. Pass by value
Actual value is being copied to another memory location for the calling procedure/function to use. i.e. it copies the actual value of the parameter into the formal parameter. So, both called and calling procedures has got 2 different memory locations to store the value.

2. Pass by reference

Only the memory location (address) is passed so that the data is not copied. The calling procedure and the called procedure, both uses the same value stored in a single memory location. So the actual and the formal parameters refer to the same memory location that holds the value.

As we know, there are three different types of parameters in PL/SQL:

We specify any one of the below types, along with the parameters so classify them as:

IN - Parameters passed to the procedure/function
OUT - Parameters that store the values returned (explicitly) from the procedure/function
IN OUT - Can pass values to the procedure and store the returned values

And by default, OUT and IN OUT parameters are passed by value whereas the IN parameters are always passed by reference. IN parameters are designed in such a way to reduce memory consumption and at the same time, the values won’t get overwritten since IN doesn’t allow give write access to the memory location.

And for OUT and IN OUT, when the values are modified by either the called procedure/function or the calling procedure/function, only their local copies get affected and not the original ones.

AR Customer Query

SELECT DISTINCT hcasa.org_id "Operating Unit Name" ,
  hp.party_name "Customer Name"                    ,
  hcas.account_number "Customer Number"            ,
  hl.address1 "Address 1"                          ,
  hl.address2 "Address 2"                          ,
  hl.city "City"                                   ,
  hl.state "State"                                 ,
  hl.postal_code "Postal Code"                     ,
  hl.country "Country"                             ,
  hps.party_site_number "Site Number"              ,
  hcsua.STATUS "Active Flag"                       ,
  hcsua.site_use_code "Usage (Bill To / Ship To)"  ,
  hcsua.attribute1 "Relation: PS US"               ,
  hcsua.TAX_REFERENCE "Tax Reg No Usage Level"     ,
  hcasa.attribute12 "Billing Setting Interface"    ,
  hcasa.attribute13 " Customer#"             ,
  hcasa.attribute2 "IBS BUC Code"                  ,
  hcasa.attribute1 "DUNS Number"                   ,
  hcp.contact_point_type "Phone Type"              ,
  hcp.phone_number "Phone Number"                 
  --  hps.party_site_id ,
  --  hps.location_id,
  --  hcasa.cust_acct_site_id
   FROM hz_parties hp          ,
  hz_cust_accounts hcas        ,
  hz_cust_acct_sites_all hcasa ,
  hz_cust_site_uses_all hcsua  ,
  hz_party_sites hps           ,
  hz_contact_points hcp        ,
  hz_locations hl
  WHERE hp.party_id         = hcas.party_id
AND hcas.cust_account_id    = hcasa.cust_account_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcsua.org_id            = hcasa.org_id
AND hp.party_id             = hps.party_id
AND hcasa.party_site_id     = hps.party_site_id
AND hcasa.status            = 'A'
AND HPS.STATUS              ='A'
AND hps.party_site_id       = hcp.owner_table_id(+)
AND hcp.contact_point_type  = 'PHONE'
AND hcp.OWNER_TABLE_NAME    = 'HZ_PARTY_SITES'
AND hcp.phone_line_type     = 'GEN'
AND hcp.status              = 'A'
AND hps.location_id         = hl.location_id
  --and hcas.account_number IN('13660','5856')
    --AND hcp.phone_number   IS NULL
AND hcasa.org_id IN (103,235,237,221);

What is the maximum number of segments / attribute that can be define for KFF/ DFF?

KFF => 30 Segments

DFF => 15 Attributes

What are the difference between SIT’s and EIT’s?

EIT => Extra Information Type

SIT => Special Information Type

Both are HRMS functionalities; SIT is normally KFF & EIT are normally DFF.

What is sequence in oracle plsql?

Sequence is used for creating value for primary key generation. Sequence guarantees generation of a new value, irrespective of a session or a user, by default. Sequence doesn’t give guarantee of order in generating values. So, the sequence guarantees uniqueness, not ordering.

E .g. if a sequence is referred from the terminal one, you may get values, such as 1,2,3,4, and if from another terminal, you may get values, such as 11,12,13,14

Values from a sequence can be accessed using any of the two methods NEXTVAL and CURVAL

NEXTVAL gives current value and increments the sequence.

CURVAL only returns the current value.

You can create a sequence using the following parameters:

·         INCREMENT BY: the sequence is incremented by this value.

·         MINVALUE/ NOMINVALUE: this decides the initial value of sequence.

·         MAXVALUE/NOMAXVALUE: it is the maximum value of a sequence0

·         CYCLE/NOCYCLE: if it is a cycle parameter then the values generated will be cyclic order, i.e. after reaching MAXVALUE the generation will start from MINVALUE.

·         CACHE/NOCACHE: if it is cache, then the call to NEXTVAL puts that many values in the session memory. i.e. if the cache is 20, then first call to NEXTVAL caches the first 20 values. If there is a sudden shutdown, then these values are lost.

·         ORDER/NOORDER: it indicates that the generated values are ordered according to time.

Create sequence test_seq

Increment by 1

Start with 1

Nomaxvalue

Nocycle

Cache 10;

What is PL/SQL Collections?

“A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

PL/SQL offers these collection types:

Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Although collections can have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections.

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters, to move data between client-side applications and stored subprograms.

To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.”

What is $flex$ & $profile$?

$flex$ is used when we need to return the flex value contained by any AOL value set or AOL object.

i.e. $flex$ would return the value held by that value set.

$profile$ are used to get the values of environment varable like org_id or mfg_organization_id etc. which are related to the current apps environment.

i.e. $profile$ org_id would return the operating unit which is active at that time for the user.

How to send message to output or log file FND_FILE.PUT_LINE

This API is used to send messages to output file or log file while we are working with PL/SQL Stored procedures.

For sending messages to output file we use this syntax

Syntax: FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’OUTPUT MESSAGE’);

For sending messages to log file we use this syntax

Syntax: FND_FILE.PUT_LINE(FND_FILE.LOG,’LOG MESSAGE’);

What is an Inline view?

Inline view is a sub query with an alias that you can use like a view inside a SQL statement. It’s not a schema object like normal view. i.e.

select e.deptno, v.dname , sum(e,sal)

from emp e,

(select distinct d.dname, d.deptno from dept d) v

Where e.deptno = v.deptno

Group by e.deptno, v.dname;

How to delete the duplicate rows from the table?

delete from emp a

      Where rowid > (select min (rowid) from emp b

                              where a.empno =b.empno)

How to find out the database name from SQL*PLUS command prompt?

Select * from global_name;

Or

Select name from v$database;

What are disadvantage of bulk collection?

Using the BULK COLLECT clause in PL/SQL implies following restrictions:

1.     We cannot bulk collect into an associative array having a string type for the key.

2.     BULK COLLECT clause can be used only in server-side programs (not in client-side programs) else, error is reported that it is not supported in client-side programs.

3.     Collections should be used as target variables listed in a BULK COLLECT INTO clause.

4.     Composite targets (such as objects) cannot be used in the RETURNING INTO clause else error is reported for feature with RETURNING clause.

5.     Multiple composite targets cannot be used in the BULK COLLECT INTO clause when implicit data type conversions are needed.

6.     When an implicit data type conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULK COLLECT INTO clause.

What is the difference between flexfield qualifier & segment qualifier?

Flexfield qualifier identifies segment in a flex field & segment qualifier identifies value in a segment

There are four types of flexfield qualifier:

·         Balancing segment qualifier

·         Cost center

·         Natural account

·         Intercompany

Segment qualifier:

·         Allow budgeting

·         Allow posting

·         Account type

·         Control account

·         Reconciliation flag

How will you display debug message for testing the form you modified?

Fnd_message.set_string('Test Message' || NAME_IN ('line.request_date'));

fnd_message.show;

or fnd_message.error;