Prepare Control File Within a Minute

Prepare Control File Within a Minute

Select decode (column_id, 1, ' ', ' , ') ||
rpad (column_name, 33, ' ') ||
decode (data_type,
'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
'NUMBER', decode (data_precision, 0,
'INTEGER EXTERNAL NULLIF ('||column_name||
'=BLANKS)', decode (data_scale, 0,
'INTEGER EXTERNAL NULLIF ('||
column_name||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('||
column_name||'=BLANKS)')),
'DATE', 'DATE "mm/dd/yy" NULLIF ('||
column_name||'=BLANKS)', null)
from all_tab_columns
where table_name = upper ('$LOADTABLE')
and owner = upper ('$SCHEMA')
order by column_id;

Note: 
1. In the $LOADTABLE pass the table to which you want to SQL the data
2. In the $SCHEMA pass the schema in which the table is located.

Menu Structure Query

Menu Structure Query:

SELECT     LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.entry_sequence SEQUENCE,
           LPAD (' ', 6 * (LEVEL - 1)) || menu_entry.prompt prompt,
           menu_entry.grant_flag grant_flag,
           DECODE (menu_entry.sub_menu_id,
                   NULL, 'FUNCTION',
                   DECODE (menu_entry.function_id, NULL, 'SUBMENU', 'BOTH')
                  ) TYPE,
           menu2.user_menu_name, func2.user_function_name
      FROM fnd_menu_entries_vl menu_entry,
           fnd_menus_tl menu,
           fnd_form_functions_tl func,
           fnd_form_functions_tl func2,
           fnd_menus_tl menu2
     WHERE menu_entry.sub_menu_id = menu.menu_id(+)
       AND menu_entry.function_id = func.function_id(+)
       AND menu_entry.sub_menu_id = menu2.menu_id(+)
       AND menu_entry.function_id = func2.function_id(+)
       AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
                     (SELECT menu_id
                        FROM fnd_menus_tl menu2
                       WHERE menu2.user_menu_name = :MENU_NAME)
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
  ORDER SIBLINGS BY menu_entry.entry_sequence

Read-Only responsibility in Oracle Applications

Read-Only responsibility in Oracle Applications

1.      Identify the menu that is attached to the Responsibility.
2.      Identify the form functions that are attached to this menu.
3.      Now create a new form function exactly similar to the standard one but with the option  QUERY_ONLY="YES" in the location.
a.      Sysadmin > Application > Function > Form > Parameter.
4.      This makes the function read only.
5.      Now create a new menu with these read-only functions and attach the menu to a new responsibility which is assigned to users who need to have read-only privileges.
What if the user has 50 responsibilities? It consumes a lot of time in creating the functions, menus and finally adding relevant profiles to the responsibility. There is another simpler way to make all the responsibilities of a user as read-only.

How to make all the responsibilities of a User Read-Only in Oracle Applications

Use the following piece of code in Procedure Event for the Event Name WHEN-NEW-FORM-INSTANCE in Custom.pll. With out much effort you can simply make all the responsibilities of a user read-only. But you need to hardcode the user name, which is a setback. Custom.pll is located in $AU_TOP/resource.


BEGIN
IF event_name = ‘WHEN-NEW-FORM-INSTANCE’ THEN
IF FND_PROFILE.VALUE(’USER_NAME’)= ’XXXXXXX’ THEN /*ENTER THE USERNAME THAT IS USED BY AUDITORS*/
BEGIN
COPY(’Entering app_form.query_only_mode.’,'global.frd_debug’);
COPY(’YES’, ‘PARAMETER.QUERY_ONLY’);
APP_MENU2.SET_PROP(’FILE.SAVE’, ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP(’FILE.ACCEPT’, ENABLED,PROPERTY_OFF);
formname := NAME_IN(’system.current_form’);
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);
WHILE (blockname is not null) LOOP
IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
END IF;
blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
END LOOP;
END query_only_mode;
END IF;
END IF;
END;

AOL Standard SQL Scripts

Oracle Apps Application Object Library SQL scripts

Oracle apps provides a number of scripts which are very useful for the data base administrator for DB monitoring & tracking purpose.

Here is the application server path were you can see the following SQL scripts.

Oracle Applications $FND_TOP/sql directory has a collection of scripts. Most of them can be used for different purposes.

SQL Script	Purpose
afchrchk.sql	Check requested table.columns for invalid trailing spaces and control characters. These characters cause 'FRM-40654: Record has been updated.' Errors when column is queried in a form. Please note that afchrchk.sql SHOULD NOT run against FND_FLEX_VALUES with option 3 (check for control characters) set to Yes, and automatic fix set to Yes. This will strip the control characters from the COMPILED_VALUE_ATTRIBUTES column. Hence, impacting the General Ledger natural account value sets as they lose all their segment qualifiers.
afcmcreq.sql	Prints the Log file names of the managers that can run a given request
afcmrrq.sql	For use by DBA's for quick look of all running requests
afcmstat.sql	Displays all defined Managers and shows current status of manager processes.
afffanld.sql	Diagnoses inconsistencies in key flexfield segment delimiters that may be introduced by the 10.7 upgrade because of an AutoInstall bug (492585).
affixcvr.sql	Fixes cross-validation rules and lines by deleting any which refer to non-existent flexfield structures, and by disabling any rules which have no lines. User must manually commit or rollback after execution.
afhlpfix.sql	Find data integrity problems in FND_HELP and delete the orphan rows.
afimchk.sql	Checks to see if the Concurrent Manager monitor is actually running
afimlock.sql	Script to detect if the Internal Concurrent Manager is in a gridlock situation with another oracle process. The script will print the user, process id, machine, program and the terminal which is causing the gridlock.
afimpmon.sql	Sets the PMON method based on the argument ( for CONC_PMON_METHOD )
afimveri.sql	Resets the Concurrent Manager monitor
afpub.sql	Grants select privileges and creates synonym for APPLSYS PUBlic account
afqpmex.sql	This script is used for sql tuning. The input is the name of a file containing a sql statement. The output is the execution plan for the statement along with information about the index columns and the sizes of the tables involved. The results are spooled to the file explain.out. This is an elaboration of the old-fashioned results from querying plan_table after running "explain plan for...". This script assumes the file contains a single, bare SQL statement, with no blank lines and no terminating ";" or "/". Also see afqpmexq.slq/explainq.sql
afqpmexq.sql	This script is used for sql tuning. (See description for afqpmex.sql)
afqpmind.sql	This script is used for sql tuning. The input is a table name (without a schema prefix) and the output is information about it's indexes and the total size of the allocated extents. The data for the DISTINCT_KEYS column comes from the analyze command.
afqpminq.sql	This script is used for sql tuning. The input is a table name (without a schema prefix) and the output is information about it's indexes. The data for the DISTINCT_KEYS column comes from the analyze command.Also see afqpmiq.sql/iq.sql.
afqpmiq.sql	This script is used for sql tuning.
afqpmlck.sql	This script is used for sql tuning. It lists all sessions that are waiting for a lock. The process holding the root of the lock tree is the left most process printed. Those printed to the right of it are waiting for locks to the left of it.
afqpmmws.sql	This script is used for performance monitoring. This script analyzes a table of wait samples. The single input parameter for the script is a table of wait samples which has the same columns as fnd_wait_samples.Some ways to create the input table:create or replace view wait_samples_view as select * from fnd_wait_samples; create table temp_wait_samples as select * from fnd_wait_samples;
afqpmmys.sql	This script is used for sql tuning. It lists the session id, user process id, server process id, OS user id, and Oracle user id for the current session.
afqpmmyw.sql	This script is used for sql tuning. It returns the cummulative totals for all wait events for the current session.
afqpmopd.sql	This script is used for sql tuning. The input is the process id of the Oracle server process. The output includes the session id, the user process id, and the OS user id.
afqpmpid.sql	This script is used for sql tuning. The input is an OS process id for the user process. The output includes the session id, server process id, and OS user id for the process.
afqpmrid.sql	This script is used for sql tuning. The input is a concurrent manager request_id, and the output is the operating system process id of the corresponding FNDLIBR process.
afqpmsid.sql	This script is used for sql tuning. The input is the Oracle session id and the output includes the user process id, the server process id, the OS user id, and the program being run.
afqpmsql.sql	This script is used for sql tuning. The input is the user process id and the output is a listing of the sql statement currently in progress.
afqpmsqx.sql	This script is used for sql tuning. The input is the user process id. The output is the execution plan and a description of the tables and their indexes (the same as is produced by qpmex.slq/explain.sql).
afqpmwta.sql	This script is used for sql tuning. It returns a snapshot of all the wait events in the database at this instant.
afqpmwti.sql	This script is used for sql tuning.The input is the user process id and the output includes the table or index name of the last I/O. This is a moderately long-running script. When the DELAY column contains "CPU - recent:" this indicates that the query is currently using CPU and the last non-CPU delay will be reported. When the current (or most recent) delay was not for IO, the TABLE_OR_INDEX_NAME and TYPE columns are null.
afqpmwtp.sql	This script is used for sql tuning. Input is the user process id and the output is a snapshot of the current action of the server process.
afqpmwtr.sql	This script is used for sql tuning. It returns a snapshot of all the wait events, but only for the real-time processes. (compare to afqpmwta.sql/wait_all.sql)
afrqpend.sql	Selects all the Pending Requests with status Q
afrqrun.sql	Lists all Running, Terminating, Paused Requests
afrqscm.sql	Prints the Log file names of the managers that can run a given request
afrqstat.sql	Summary of concurrent request execution since Date
afrqwait.sql	Selects all the Pending Requests with status Q
afsecchk.sql	Release 7.5 Referential Integrity Display
afsetpri.sql	Used to set Program level priority for concurrent programs
afsetseq.sql	Set ORACLE Sequence
afsetsqx.sql	Set ORACLE Sequence. The difference between this script (afsetsqx.sql) and afsetseq.sql is that this script performs EXIT at the end as it is called directly from driver files.
afsyn01.sql	Drop synonyms for old tables, views and sequences.
afuiddrv.sql	Sets all sequences
afwebdbg.sql	Print WebServer setup debugging information.
afxpmmws.sql	This script is used for performance monitoring. This script analyzes a table of wait samples. The single input parameter for the script is a table of wait samples which has the same columns as fnd_wait_samples.
fdmchk.sql	Check menu entry dangling references.
fdmfix.sql	Check & fix menu entry dangling references.
FNDATPRG.sql	Purge audit trail tables before a given date
FNDCPDC2.sql	Delete a concurrent program and SRS definition if needed, calls FNDCPDCP.sql
FNDCPDCP.sql	Delete a concurrent program and SRS definition if needed
fndfbdpm.sql	Deletes a flexbuilder parameter.
fndfbfxn.sql	Generate a list of FlexBuilder functions and parameters for a given application. Some parameters have multiple definitions, but this report lists each parameter only once.
fndfbprm.sql	Generate a list of FlexBuilder functions and parameters, including detailed information about how the parameter is defined for a given application. This report includes all definitions for each parameter, ordered by sequence number. The information in this report corresponds to information in the Define FlexBuilder Parameters form.
fndffbdd.sql	Finds independant segment values which don't have the default dependant segment value defined for them.
FNDFFCVS.sql	Copy flexfield value set from one database to another creates a sql script filename.sql which can be run on another database to copy value sets
FNDFFDDS.sql	Delete a descriptive flexfield definition from AOL tables
FNDFMFXR.sql	Forms Trigger Exception Report
fndgofpr.sql	Creates Grants Only for a Full PRivileged Oracle ID. This assumes fndgsspr.sql was run first to create a limited set of SELECT only privileges which are not recreated here.
fndgsnpa.sql	Revokes all AOL grants and drops all AOL synonyms from a user. Must also revoke synonym GL_CURRENCIES which uses FND_CURRENCIES.
fndgsspr.sql	Creates Grants and Synonyms for a Select only Privileged Oracle ID.
FNDMDCMR.sql	Creates a report of all messages for an application in the given language.The report is located in the application's message directory and has the name {language short name}.FDDTMFEXT. For example: /applications/fnd/5.0/mesg/usaeng.msg. The standalone FNDMDCMF (Create Message File) runs this report after creating the binary message file to produce a human-readable version.
fndmncpy.sql	Copy application menus to another, custom application (Rel. 9 to 10 backup)
FNDNLADD.sql	Add missing translation rows for FND _TL tables.
FNDNLCHK.sql	Check _TL tables for inconsistent or missing translation data. FNDNLCHK does not fix anything, it only reports bad data. Use FNDNLADD to fix errors found by FNDNLCHK.
fndnlhlp.sql	Updates fnd_loader_formats
FNDNLICR.sql	NLS Insert non-ISO currency
FNDNLINS.sql	Update _TL tables to populate rows for new language when a new language is installed.
FNDNLMVL.sql	Install multi-language versions of _VL views.
fndnmts.sql	Populate FND%_TL subtables with data from main FND% tables ( In 10.5 this script is superceded by FNDNLADD.sql and should not be used by customers. )
fndnstm.sql	Populate FND_ main tables with data from subtables.
fndrspfm.sql	Report on Users who Access a Given Form
FNDRSTST.sql	SRS Test Program - expects three arguments and prints them
fndscats.sql	Signon Audit Time Stamp
FNDSCETS.sql	Set end time stamp (FND_LOGINS, FND_LOGIN_RESPONSIBILITIES, FND_LOGIN_RESP_FORMS)
FNDSCGRP.sql	Reports on which applications, sets and programs have been assigned to which responsibility. Accepts application name and responsibility name.
FNDSCPRG.sql	Purge signon audit tables from given date
FNDUDUAL.sql	This script ensures only one record exists in FND_DUAL.
fndutcsq.sql	Converts rows in FND_UNIQUE_IDENTIFIER_CONTROL to Sequences
srstest.sql	SRS test script
TSTSQPLS.sql	SQL script to test execution methods for RTs.
wfbkg.sql	WorkFlow BacKGround engine, starts the background engine, running for the indicated number of minutes.
wfbkgchk.sql	WorkFlow Background Check. It displays a status report on background work waiting to be processed.
wfdirchk.sql	WorkFlow Directoy Servoices Data Model Check
WFNLADD.sql	Add missing translation rows for WF _TL tables.
wfnldat.sql	Add default language data for standalone Workflow install.
wfnlena.sql	Enable/disable an installed language (workflow)
wfntfsh.sql	WorkFlow NoTiFication SHow status
wfprot.sql	WorkFlow PROTection level reset. It resets the protection level for all objects in a specified item type to the supplied value. After resetting the protection level NOTHING in the item type will be customizable by a higher access level.
wfrefchk.sql	WorkFlow Primary,Unique and Foreign Key constraint checker. It checks for all invalid workflow data that is missing primary key data for a foreign key
wfretry.sql	WorkFlow Handle error'ed activity. It displays a list of errored activities for the indicated item. Type in the name of the activity, and command to skip, retry, or reset.
wfrmall.sql	WorkFlow ReMove ALL. It DELETES all workflow information. ALL OF IT.
wfrmbref.sql	Deletes all invalid workflow data that is missing primary key data for a foreign key
wfrmita.sql	WorkFlow ReMove ITem Attribute. It deletes all workflow information for the specified item attribute.
wfrmitms.sql	WorkFlow ReMove ITMeS. It removes item status information for items which match the supplied type and key patterns.
wfrmitt.sql	WorkFlow ReMove ITemType. It deletes ALL workflow information for the specified item type.
wfrmtype.sql	WorkFlow ReMove TYPE. It purges ALL runtime data associated with a given item type.
wfrun.sql	WorkFlow RUN a process. It creates and starts the specified process.
wfstat.sql	WorkFlow item STATUS report. It displays a status report on the indicated item (132 charachter output).
wfstatus.sql	WorkFlow item STATUS report. It displays a status report on the indicated item (132 charachter output).
wfver.sql	WorkFlow VERsion display. It displays version information for all WF source.
wfverchk.sql	WorkFlow Version Check. It checks all workflow activities for potentially invalid version histories (more than one version of an activity active at any given time). Correct any errors found.

How to make a Form as READ ONLY

How to make a Form as READ ONLY

Follow the following steps inorder to make a form as Query only/Read Only.

1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to ‘Security -> Responsibility -> Define’
3. Query for the responsibility for which you want to make the form as ‘Query Only’ and copy the ‘Menu’ name.
4. Navigate to ‘Application -> Menu’ and query with the menu name which we got in step 3 in ‘User Menu Name’
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the ‘Function’ field w.r.t the form that needs to be made as ‘Query Only’.
6. Navigate to ‘Application -> Funtion’ and query with the function name we got in Step 5 in ‘User Function Name’ field.
7. Identify the correct Function for your form and then naviagate to the ‘Form’ tab.
8. Enter the value: QUERY_ONLY=YES in the parameters section in the Form tab, if you already have another value existing in the parameter form then you need to seperate them with a space..

Example: MODE=”PROJECT” QUERY_ONLY=YES

Attaching KFF in DFF

Attaching KFF in DFF:

Is there any way to pop up a key flexfield in the DFF segment or even in a key flexfield? Many a times, in mature global implementations, the need for the extra fields in many forms in insatiable. This article gives you simple steps to create a KFF in a DFF. You can extend the same to a KFF too.

Here are the steps:

Step1:

Create a KFF table in your custom schema with segment columns in there using the following SQLs in your custom. I am creating this table in AR schema.

CREATE TABLE AR.TEST_KFF_TBL
(
  CODE_COMBINATION_ID      NUMBER PRIMARY KEY NOT NULL,  
  STRUCTURE_ID             NUMBER             NOT NULL,
  LAST_UPDATE_DATE               DATE               NOT NULL,
  LAST_UPDATED_BY                NUMBER             NOT NULL,
  ENABLED_FLAG                   VARCHAR2(1)        NOT NULL,
  SUMMARY_FLAG                   VARCHAR2(1)        NOT NULL,
  SEGMENT1                       VARCHAR2(25),
  SEGMENT2                       VARCHAR2(25),
  SEGMENT3                       VARCHAR2(25),
  SEGMENT4                       VARCHAR2(25),
  SEGMENT5                       VARCHAR2(25),
  SEGMENT6                       VARCHAR2(25),
  SEGMENT7                       VARCHAR2(25),
  SEGMENT8                       VARCHAR2(25),
  SEGMENT9                       VARCHAR2(25),
  SEGMENT10                      VARCHAR2(25),
  SEGMENT11                      VARCHAR2(25),
  SEGMENT12                      VARCHAR2(25),
  SEGMENT13                      VARCHAR2(25),
  SEGMENT14                      VARCHAR2(25),
  SEGMENT15                      VARCHAR2(25),
  SEGMENT16                      VARCHAR2(25),
  SEGMENT17                      VARCHAR2(25),
  SEGMENT18                      VARCHAR2(25),
  SEGMENT19                      VARCHAR2(25),
  SEGMENT20                      VARCHAR2(25),
  SEGMENT21                      VARCHAR2(25),
  SEGMENT22                      VARCHAR2(25),
  SEGMENT23                      VARCHAR2(25),
  SEGMENT24                      VARCHAR2(25),
  SEGMENT25                      VARCHAR2(25),
  SEGMENT26                      VARCHAR2(25),
  SEGMENT27                      VARCHAR2(25),
  SEGMENT28                      VARCHAR2(25),
  SEGMENT29                      VARCHAR2(25),
  SEGMENT30                      VARCHAR2(25),
  DESCRIPTION                    VARCHAR2(240),
  START_DATE_ACTIVE              DATE,
  END_DATE_ACTIVE                DATE,
  ATTRIBUTE1                     VARCHAR2(150),
  ATTRIBUTE2                     VARCHAR2(150),
  ATTRIBUTE3                     VARCHAR2(150),
  ATTRIBUTE4                     VARCHAR2(150),
  ATTRIBUTE5                     VARCHAR2(150),
  ATTRIBUTE6                     VARCHAR2(150),
  ATTRIBUTE7                     VARCHAR2(150),
  ATTRIBUTE8                     VARCHAR2(150),
  ATTRIBUTE9                     VARCHAR2(150),
  ATTRIBUTE10                    VARCHAR2(150),
  CONTEXT                        VARCHAR2(150),
  SEGMENT_ATTRIBUTE1             VARCHAR2(60),
  SEGMENT_ATTRIBUTE2             VARCHAR2(60),
  SEGMENT_ATTRIBUTE3             VARCHAR2(60),
  SEGMENT_ATTRIBUTE4             VARCHAR2(60),
  SEGMENT_ATTRIBUTE5             VARCHAR2(60),
  SEGMENT_ATTRIBUTE6             VARCHAR2(60),
  SEGMENT_ATTRIBUTE7             VARCHAR2(60),
  SEGMENT_ATTRIBUTE8             VARCHAR2(60),
  SEGMENT_ATTRIBUTE9             VARCHAR2(60),
  SEGMENT_ATTRIBUTE10            VARCHAR2(60),
  SEGMENT_ATTRIBUTE11            VARCHAR2(60),
  SEGMENT_ATTRIBUTE12            VARCHAR2(60),
  SEGMENT_ATTRIBUTE13            VARCHAR2(60),
  SEGMENT_ATTRIBUTE14            VARCHAR2(60),
  SEGMENT_ATTRIBUTE15            VARCHAR2(60),
  SEGMENT_ATTRIBUTE16            VARCHAR2(60),
  SEGMENT_ATTRIBUTE17            VARCHAR2(60),
  SEGMENT_ATTRIBUTE18            VARCHAR2(60),
  SEGMENT_ATTRIBUTE19            VARCHAR2(60),
  SEGMENT_ATTRIBUTE20            VARCHAR2(60),
  SEGMENT_ATTRIBUTE21            VARCHAR2(60),
  SEGMENT_ATTRIBUTE22            VARCHAR2(60),
  SEGMENT_ATTRIBUTE23            VARCHAR2(60),
  SEGMENT_ATTRIBUTE24            VARCHAR2(60),
  SEGMENT_ATTRIBUTE25            VARCHAR2(60),
  SEGMENT_ATTRIBUTE26            VARCHAR2(60),
  SEGMENT_ATTRIBUTE27            VARCHAR2(60),
  SEGMENT_ATTRIBUTE28            VARCHAR2(60),
  SEGMENT_ATTRIBUTE29            VARCHAR2(60),
  SEGMENT_ATTRIBUTE30            VARCHAR2(60),
  SEGMENT_ATTRIBUTE31            VARCHAR2(60),
  SEGMENT_ATTRIBUTE32            VARCHAR2(60),
  SEGMENT_ATTRIBUTE33            VARCHAR2(60),
  SEGMENT_ATTRIBUTE34            VARCHAR2(60),
  SEGMENT_ATTRIBUTE35            VARCHAR2(60),
  SEGMENT_ATTRIBUTE36            VARCHAR2(60),
  SEGMENT_ATTRIBUTE37            VARCHAR2(60),
  SEGMENT_ATTRIBUTE38            VARCHAR2(60),
  SEGMENT_ATTRIBUTE39            VARCHAR2(60),
  SEGMENT_ATTRIBUTE40            VARCHAR2(60),
  SEGMENT_ATTRIBUTE41            VARCHAR2(60),
  SEGMENT_ATTRIBUTE42            VARCHAR2(60)
);
/
GRANT ALL ON AR.TEST_KFF_TBL TO APPS;
/

CREATE SEQUENCE AR.TEST_KFF_TBL_S
  INCREMENT BY 1
  START WITH 1000
  MINVALUE 1
  MAXVALUE 2000000000
  NOCYCLE
  NOORDER
  CACHE 20;
/
GRANT ALL ON AR.TEST_KFF_TBL_S TO APPS;
/

CREATE SYNONYM apps.TEST_KFF_TBL FOR AR.TEST_KFF_TBL;
/
CREATE SYNONYM apps.TEST_KFF_TBL_S FOR AR.TEST_KFF_TBL_S;
/

Step2:

Register the table. You can only register the table using APIs (up until 10.7 GUI we were able to do this manually in Application Developer screens. But now we use API only). Use the following to do that.

DECLARE
BEGIN 
   AD_DD.REGISTER_TABLE ('AR','TEST_KFF_TBL','T'); 
   
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','CODE_COMBINATION_ID',1,'NUMBER',15,'N','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','STRUCTURE_ID',2,'NUMBER',15,'N','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT1',3,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT2',4,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT3',5,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT4',6,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT5',7,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT6',8,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT7',9,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT8',10,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT9',11,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT10',12,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT11',13,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT12',14,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT13',15,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT14',16,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT15',17,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT16',18,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT17',19,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT18',20,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT19',21,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT20',22,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT21',23,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT22',24,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT23',25,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT24',26,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT25',27,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT26',28,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT27',29,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT28',30,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT29',31,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT30',32,'VARCHAR2',25,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','DESCRIPTION',33,'VARCHAR2',240,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','START_DATE_ACTIVE',34,'DATE',9,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','END_DATE_ACTIVE',35,'DATE',9,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','LAST_UPDATED_BY ',36,'NUMBER',15,'N','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ENABLED_FLAG',37,'VARCHAR2',1,'N','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SUMMARY_FLAG',38,'VARCHAR2',1,'N','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE1',39,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE2',40,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE3',41,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE4',42,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE5',43,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE6',44,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE7',45,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE8',46,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE9',47,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','ATTRIBUTE10',48,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','CONTEXT',49,'VARCHAR2',150,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE1',50,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE2',51,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE3',52,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE4',53,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE5',54,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE6',55,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE7',56,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE8',57,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE9',58,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE10',59,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE11',60,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE12',61,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE13',62,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE14',63,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE15',64,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE16',65,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE17',66,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE18',67,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE19',68,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE20',69,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE21',70,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE22',71,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE23',72,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE24',73,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE25',74,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE26',75,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE27',76,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE28',77,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE29',78,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE30',79,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE31',80,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE32',81,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE33',82,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE34',83,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE35',84,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE36',85,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE37',86,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE38',87,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE39',88,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE40',89,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE41',90,'VARCHAR2',60,'Y','N');
   AD_DD.REGISTER_COLUMN ('AR', 'TEST_KFF_TBL','SEGMENT_ATTRIBUTE42',91,'VARCHAR2',60,'Y','N');
 
COMMIT;  
END; 
/



Step3:

Verify the table registration. Register the KFF in that application. Check that the segment columns are enabled.

Step4:

Create a structure and enable as many as segments as you want. Make sure dynamic insert is enabled.

Step5:

Create a special value set based on this KFF. For special validation use the following for events:



Event: EDIT

FND POPID 
APPL_SHORT_NAME=AR 
CODE="TES#" 
NUM=101
REQUIRED=N
VALIDATE=FULL
ID=:!ID 
SEG=:!VALUE 
DESC=:!MEANING 
NAVIGATE=!DIR 
DINSERT=Y
---------------------------------------------------------------------------
Event: LOAD

FND LOADID 
APPL_SHORT_NAME=AR
CODE="TES#" 
NUM=101
REQUIRED=Y 
VALIDATE=FULL
ID=:!ID 
SEG=:!VALUE 
DESC=:!MEANING 
NAVIGATE=!DIR 
DINSERT=Y
---------------------------------------------------------------------------
Event: VALIDATE

FND VALID 
APPL_SHORT_NAME=AR
CODE="TES#" 
NUM=101
REQUIRED=N
VALIDATE=FULL
ID=:!ID 
SEG=:!VALUE 
DESC=:!MEANING 
DINSERT=Y
---------------------------------------------------------------------------



Step6:

Choose a DFF where you want this KFF be available. Enable a segment in that DFF and assign this value set.




Step7:

Test your setup. If you save this DFF segment, CCID is stored in the DFF segment and a row goes into your table (KFF table) with that CCID and appropriate segments. If you update the segment, it will create another row with new CCID.




http://www.slideshare.net/nuppara/popping-key-flexfield-in-descriptive-flexfield