FLEX FIELDS (KFFDFF) AND SQL PLUS

KFF'S AND DFF'S


Steps Involved :

1. Registering A Tables & Columns :Register the Table & Columns if the KFF/DFF is on User Defined Table.

The following is theexampleAD_DD.register_table('AR','RAJ_KFF_TEST','T',8,10,90);ad_dd.register_column('AR','RAJ_KFF_TEST','Attribute_category',1,'Varchar2',20,'N','N');ad_dd.register_column('AR','RAJ_KFF_TEST','CCID',2,'NUMBER',10,'N’,'N');ad_dd.register_column('AR','RAJ_KFF_TEST','attribute1',3,'VARCHAR2',20,'N','N');For a KFF ....It is necessary to have a column to store CCID and a column to Store Structure Information In the above example the Attribute_category is used to store Structure Info.and CCID to store ccid number. These are specified at Registration of the Flexflied.For A DFF...It is enough to store only the Structure Info.Example the Attribute_category may act as an Structure Field.

2. Registering The FlexFiled :

After creating the table and registering it, register the FlexField You You want to Use.Remember : U have to Use this name when referencing the flexfield.Also u have to specify the CCID column & Structure Column for KFF here.Remember : U have to enable the columns of the table here, otherwise u can't define segments for the same.

3. Defining the Segments :

Every Flexfiled must have Segment Qualifiers And FlexField columns these are defined in the AOLat Segment Definition.After defining these segments freeze and compile the flexfield segments definition.These 3 steps complete the process of Registration of table and flexfield & definition of Segments.Some Finer Points :1. You have to check the Dynamic Insertion Allowed ( for KFF only) to allow the users to dynamically createan intelligent combination key.2. You can check the Protect check box to ensure that users do not change the definition of flexfield by mistake.Incorporating DFF's / KFF's in the forms :The whole process essentially requires 4 steps ....1. Modify 7 triggers2. Create Hidden Fields corresponding to the segments3. Define the FlexField in the New-Form-Instance-Trigger

4. Set the Profile Options

( Flex:Open_Descr_Window , Flex:Open-Key_Window ) to YESGeneric Activities to Open A Form In Apps ....You have to go through some steps to enable a form in Apps.
1. Open your Template.fmb ( this is provided by Oracle-Apps) .
2. Delete the BLOCKNAME ( datablock , canvas , window ) from the Template.fmb
3. Save this form Module with ur custom name.
4. Create A Canvas , subclass it with Profile class Canvas
5. Similary create a Window ( subclass it with Window profile class) , Datablock.
6. Modify the APPS_CUSTOM package body with following code....if (wnd = '') thenapp_window.close_first_window;Give your name of Window at the Bolded place.
7. Modify The pre-form trigger as follows..app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW');Give Your block name here at the bolded place.

Now modify the required triggers for implementing the DFF's and KFF's ......

.1. The Following triggers should be change...a. When-new-form-instanceb. pre-queryc. post-queryd. WHEN-NEW-ITEM-INSTANCEe. PRE-UPDATEf. WHEN-VALIDATE-RECORDg. WHEN-VALIDATE-ITEMadd FND_FLEX.EVENT('EVENT NAME') where EVENT NAME is the trigger name itself.eg. FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');

2. Create Hidden Fields ( Set Canvas to null) . These should be as many as the number of segments u defined.

3. Define Your flex-filed at the New-Form-Instance trigger as follows ...

FND_KEY_FLEX.DEFINE(BLOCK=>'FLEX_BLOCK',FIELD=>'KFF_SEGS',APPL_SHORT_NAME=>'AR', DESC_FLEX_NAME=>'DFF_FLEX')for DFF....Block - is the block name in the formField - Field which is acting as DFF/KFFAppl_Short_name :- AR/ AP / CS etc.,

Desc_FLEX_NAME :- Name of the Flex Filed U have given at the time of Registration.For KFF.....CODE - the number u give at the time of registering ur KFFNUM - Use the following SQL to get the number . ( default is 101 )

SELECT ID_FLEX_NUM FROM FND_ID_FLEX_STRUCTURES WHERE ID_FLEX_CODE='CODE';

4. Create a TEXT-FIELD to act as DFF or KFFFor DFF subclass it with ....TEXT_ITEM_DESC_FLEXFor Kff subclass it with ...TEXT_ITEM itself5. Save ur work andattach this form to a functionFunction to a menu.That's all....Good Luck !! Happy Experimenting...




SQL*Plus




1 Which command in SQL*Plus is used to save the query output to a file?

SPOOL


2 How would you extract a SQL statement in the SQL buffer of SQL*Plus?

Enter a SLASH (/)

3 What is the default display length of the DATE Datatype column?

Eight

4 Which Clause in a query limits the rows selected?

WHERE

5 Which SQL*Plus command always overwrites a file?

SPOOL

6 Which single-row function could you use to return a specific portion of a character?

SUBSTR

7 Which of the following is not a Group Function?

(AVG, COUNT, LEAST, STDDEV, VARIANCE)LEAST

8 When using multiple tables to query information, in which Clause do you specify the table names?

FROM

9 What are the special characters allowed in a table name?

(&, #, @, $)#, $

10 What is the default length of a CHAR Data type column, if no length is specified in the table definition?

1

11)Which named PL/Sql program must return a value?

FUNCTION must include a RETURN statement and must return a value

12 How do you return multiple values from a procedure?

Use OUT parameterOUT parameters pass values out of a procedureIN parameters pass values into the procedure

13 Which section of the PL/Sql block handles errors and abnormal conditions?

EXCEPTION SECTION

14 What is the mandatory clause in a SELECT statement when used inside a PL/Sql block?

INTO

15 In which section of a PL/Sql block is a constant assigned value?

DECLARATION SECTION

16 What is the name of the Pl/Sql block that is associated with a table and executes automatically when an event occurs?

TRIGGER

17 What type of constant can be defined when you declare a variable?

NOT NULL

18 What is the PL/Sql stored program that fire when an event occurs?

TRIGGER

19 In a PL/Sql block, what is the value of SQL%NOTFOUND before executing any DML Statement?

NULL

20 Which of the following requires an explicit cursor if processing more than one row?

(SELECT, UPDATE, DELETE, INSERT)SELECT

21 Which commands are allowed inside a PL/Sql block?

(TRUNCATE, DELETE, SAVEPOINT, ALTER TABLE)DELETE, SAVEPOINTDDL Statements are not allowed inside a PL/Sql blockTRUNCATE and ALTER TABLE are DDL statements

22 What is the PRAGMA exception_init used for?

To associate an exception name with and exception numberException_init is used to associate (give a name) to an error number

23 What command do you use to induce an error condition?

RAISE

24 What causes a TOO_MANY_ROWS exception?

A SELECT INTO returned more than one row

25 Which of the following types of exceptions cannot be handled in an exception?

(Syntax Errors, database Errors, Datatype Mismatch Errors, Divide By Zero Errors)Syntax ErrorsSyntax Errors are handled by PL/Sql compiler and not the runtime engineThe exception section cannot handle them

26 What function can be used to retrieve the error number for the current exception?

SQLCODE

27 What function can be used to retrieve the text associated with the current exception?

SQLERRM

28 What Clause do you need to include in your exception section to handle any exception not previously specified?

WHEN OTHERS

29 At a minimum, how many join conditions should be there in the WHERE Clause to avoid a Cartesian join if there are Three Tables in the FROM Clause?

2There should be at least (n - 1) join conditions when joining (n) tables to avoid a Cartesian join

30 A view can only be used to query and update data, you cannot Insert into or delete from a view.

True or falseFALSE

31 Which option is not available in Oracle when modifying tables?

(Add new Columns, Rename an Existing Column, Drop an Existing Column)Rename an Existing ColumnYou cannot rename an Existing column using ALTER TABLE