Oracle Apps World: 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

Oracle Apps World

Search This Blog

Sunday, June 30, 2013

Attaching KFF in DFF

No comments:

Post a Comment