1,
create table cmcc_flexfield_test
(
HM_ID NUMBER,
HM_STRUCTURE_ID NUMBER,
username varchar2(30),
INVENTORY_ITEM_ID NUMBER not null,
ORGANIZATION_ID NUMBER not null,
LAST_UPDATE_DATE DATE default sysdate,
LAST_UPDATED_BY NUMBER default 1,
CREATION_DATE DATE default sysdate,
CREATED_BY NUMBER default 1,
LAST_UPDATE_LOGIN NUMBER default 1,
SEGMENT1 VARCHAR2(40),
SEGMENT2 VARCHAR2(40),
SEGMENT3 VARCHAR2(40),
SEGMENT4 VARCHAR2(40),
SEGMENT5 VARCHAR2(40),
SEGMENT6 VARCHAR2(40),
SEGMENT7 VARCHAR2(40),
SEGMENT8 VARCHAR2(40),
SEGMENT9 VARCHAR2(40),
SEGMENT10 VARCHAR2(40),
ATTRIBUTE1 VARCHAR2(240),
ATTRIBUTE2 VARCHAR2(240),
ATTRIBUTE3 VARCHAR2(240),
ATTRIBUTE4 VARCHAR2(240),
ATTRIBUTE5 VARCHAR2(240),
ATTRIBUTE6 VARCHAR2(240),
ATTRIBUTE7 VARCHAR2(240),
ATTRIBUTE8 VARCHAR2(240),
ATTRIBUTE9 VARCHAR2(240),
ATTRIBUTE10 VARCHAR2(240)
)
***********************************************************
2,
这个表必须要有的两个字段HM_ID,HM_STRUCTURE_ID，且必须用ID结尾（用来注册在ERP系统里）
并且创建一个sequence:table_name+_S
CREATE SEQUENCE CMCC_HM_COMBINATIONS_S;
/
create table CMCC_HM_COMBINATIONS
(
HM_ID NUMBER,--必须跟form里面注册的一致
HM_STRUCTURE_ID NUMBER,--必须跟form里面注册的一致
SEGMENT1 VARCHAR2(40),
SEGMENT2 VARCHAR2(40),
SEGMENT3 VARCHAR2(40),
SEGMENT4 VARCHAR2(40),
SEGMENT5 VARCHAR2(40),
SEGMENT6 VARCHAR2(40),
SEGMENT7 VARCHAR2(40),
SEGMENT8 VARCHAR2(40),
SEGMENT9 VARCHAR2(40),
SEGMENT10 VARCHAR2(40),
ENABLED_FLAG VARCHAR2(1),
SUMMARY_FLAG VARCHAR2(1),
START_DATE_ACTIVE DATE,
END_DATE_ACTIVE DATE,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY VARCHAR2(32),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(32),
LAST_UPDATE_LOGIN VARCHAR2(32),
REQUEST_ID NUMBER(15),
PROGRAM_APPLICATION_ID NUMBER(15),
PROGRAM_ID NUMBER(15),
PROGRAM_UPDATE_DATE DATE,
ATTRIBUTE1 VARCHAR2(240),
ATTRIBUTE2 VARCHAR2(240),
ATTRIBUTE3 VARCHAR2(240),
ATTRIBUTE4 VARCHAR2(240),
ATTRIBUTE5 VARCHAR2(240),
ATTRIBUTE6 VARCHAR2(240),
ATTRIBUTE7 VARCHAR2(240),
ATTRIBUTE8 VARCHAR2(240),
ATTRIBUTE9 VARCHAR2(240),
ATTRIBUTE10 VARCHAR2(240)
)
create public synonym CMCC_HM_COMBINATIONS for CMCC_HM_COMBINATIONS
*********************************************************************
3,
begin
ad_dd.register_table(p_appl_short_name => 'INV',
p_tab_name => 'CMCC_HM_COMBINATIONS',
p_tab_type => 'T');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','HM_ID',1,'NUMBER',38,'N','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE1',2,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE2',3,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE3',4,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE4',5,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE5',6,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE6',7,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE7',8,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE8',9,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE9',10,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ATTRIBUTE10',11,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','LAST_UPDTE_DATE',12,'DATE',9,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','LAST_UPDATED_BY',13,'VARCHAR2',32,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','CREATIION_DATE',14,'DATE',9,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','CREATED_BY',15,'VARCHAR2',32,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','LAST_UPDATE_LOGIN',16,'VARCHAR2',32,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT1',17,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT2',18,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT3',19,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT4',20,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT5',21,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT6',22,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT7',23,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT8',24,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT9',25,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SEGMENT10',26,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','ENABLED_FLAG',27,'VARCHAR2',1,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','SUMMARY_FLAG',28,'VARCHAR2',1,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','START_DATE_ACTIVE',29,'DATE',9,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','END_DATE_ACTIVE',30,'DATE',9,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','REQUEST_ID',31,'NUMBER',15,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','PROGRAM_APPLICATION_ID',32,'NUMBER',15,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','PROGRAM_ID',33,'NUMBER',33,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','PROGRAM_UPDATE_DATE',34,'DATE',9,'Y','N');
ad_dd.register_column('INV','CMCC_HM_COMBINATIONS','HM_STRUCTURE_ID',35,'NUMBER',38,'Y','N');
end;
commit;
*******************************************
4,
begin
ad_dd.register_table(p_appl_short_name => 'INV',
p_tab_name => 'CMCC_FLEXFIELD_TEST',
p_tab_type => 'T');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','HM_ID',1,'NUMBER',38,'N','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE1',2,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE2',3,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE3',4,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE4',5,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE5',6,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE6',7,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE7',8,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE8',9,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE9',10,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ATTRIBUTE10',11,'VARCHAR2',240,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','LAST_UPDTE_DATE',12,'DATE',9,'Y','N');
ad_dd.register_column
('INV','CMCC_FLEXFIELD_TEST','LAST_UPDATED_BY',13,'VARCHAR2',32,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','CREATIION_DATE',14,'DATE',9,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','CREATED_BY',15,'VARCHAR2',32,'Y','N');
ad_dd.register_column
('INV','CMCC_FLEXFIELD_TEST','LAST_UPDATE_LOGIN',16,'VARCHAR2',32,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT1',17,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT2',18,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT3',19,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT4',20,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT5',21,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT6',22,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT7',23,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT8',24,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT9',25,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','SEGMENT10',26,'VARCHAR2',40,'Y','N');
ad_dd.register_column
('INV','CMCC_FLEXFIELD_TEST','HM_STRUCTURE_ID',27,'NUMBER',38,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','USERNAME',28,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','INVENTORY_ITEM_ID',29,'VARCHAR2',40,'Y','N');
ad_dd.register_column('INV','CMCC_FLEXFIELD_TEST','ORGANIZATION_ID',30,'VARCHAR2',40,'Y','N');
end;
/
commit
/
********************************************************
5,
职责：Application Developer
-->Flexfiled
-->Key
-->Register
*************************************************************
6,
职责：Application Developer
-->Flexfiled
-->Key
-->Segments
***********************************************************
7,
在以下 tigger(FORM级)中加入
PRE-QUERY: FND_FLEX.EVENT('PRE-QUERY');
POST-QUERY: FND_FLEX.EVENT('POST-QUERY');
'PRE-INSERT: FND_FLEX.EVENT('PRE-INSERT');
PRE-UPDATE : FND_FLEX.EVENT('PRE-UPDATE');
WHEN-VALIDATE-ITEM: FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');
WHEN-VALIDATE-RECORD: FND_FLEX.EVENT('WHEN-VALIDATE-RECORD');
在此item下添加:
WHEN-VALIDATE-ITEM : FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');
WHEN-NEW-ITEM-INSTANCE: FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE');
在PER_FORM. 中添加 :
FND_KEY_FLEX.DEFINE (
BLOCK => 'Block_Name',
FIELD => 'Field_Name',
ID => 'ccid_field_name',
APPL_SHORT_NAME => 'shortname of application used to register flexfield',
--select fav.APPLICATION_NAME,fav.APPLICATION_SHORT_NAME from FND_APPLICATION_VL fav
--where fav.APPLICATION_NAME = 'Oracle General Ledger'
CODE => 'flexfield_code', --select fif.id_flex_code from FND_ID_FLEXS fif
--where fif.id_flex_code = 'GL#'
NUM => 'structure_number'); --select fifs.ID_FLEX_NUM from FND_ID_FLEX_STRUCTURES_VL fifs
--where fifs.ID_FLEX_CODE = 'GL#'
例如：
FND_KEY_FLEX.DEFINE (
BLOCK => 'CMCC_FLEXFIELD_TEST',
FIELD => 'KF',
ID => 'HM_ID',
APPL_SHORT_NAME => 'SQLGL',
CODE => 'GL#',
NUM => '50234');
