How to Apply Date Track Functions on Custom Forms?
Below are the steps, Yellow highlighted is mandatory and very
important.
Step1:
Create DT Table
|
CREATE
TABLE APPS.XXX_HR_DT_DATA_F
(
SAMPLE_ID NUMBER(10) NOT
NULL,
EFFECTIVE_START_DATE DATE NOT
NULL,
EFFECTIVE_END_DATE DATE NOT
NULL,
SAMPLE_DESC VARCHAR2(200
BYTE) NOT NULL,
SAMPLE_NAME VARCHAR2(100
BYTE) NOT NULL,
SAMPLE_VALUE NUMBER NOT
NULL,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_LOGIN NUMBER(15),
CREATED_BY NUMBER(15),
CREATION_DATE DATE
);
|
Step2:
Create unique constraint on 3 columns
|
ALTER
TABLE APPS.XXX_HR_DT_DATA_F ADD
CONSTRAINT
XXX_HR_DT_DATA_F_PK
PRIMARY
KEY (SAMPLE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
ENABLE
VALIDATE;
|
Step3:
Create view
|
CREATE
OR REPLACE FORCE VIEW APPS.XXX_HR_DT_DATA_D
(
SAMPLE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
SAMPLE_DESC,
SAMPLE_NAME,
SAMPLE_VALUE,
LAST_UPDATE_DATE,
USER_NAME
)
BEQUEATH
DEFINER
AS
SELECT
sample_id,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
sample_desc,
sample_name,
sample_value,
LAST_UPDATE_DATE,
FUSER.user_name
FROM
XXX_HR_DT_DATA_F dt, fnd_user_view FUSER
WHERE
FUSER.user_id(+) = dt.last_updated_by;
|
Step4:
Create view for Data block
|
CREATE
OR REPLACE FORCE VIEW APPS.XXX_HR_DT_DATA_FV
AS
SELECT
"SAMPLE_ID",
"EFFECTIVE_START_DATE",
"EFFECTIVE_END_DATE",
"SAMPLE_DESC",
"SAMPLE_NAME",
"SAMPLE_VALUE",
"LAST_UPDATE_DATE",
"LAST_UPDATED_BY",
"LAST_UPDATE_LOGIN",
"CREATED_BY",
"CREATION_DATE",
rowid
row_id
FROM
XXX_HR_DT_DATA_F -- Any lookup or join to
collect required data
WHERE EFFECTIVE_START_DATE
<=
(SELECT
EFFECTIVE_DATE
FROM
FND_SESSIONS
WHERE
FND_SESSIONS.SESSION_ID = USERENV ('SESSIONID'))
AND
EFFECTIVE_END_DATE >=
(SELECT
EFFECTIVE_DATE
FROM
FND_SESSIONS
WHERE
FND_SESSIONS.SESSION_ID = USERENV ('SESSIONID'));
|
Step5:
Create sequence for ID column
|
create
sequence XXX_HR_DT_SAMPLE_S;
|
Step6:
Fill the DT tables with prerequisites
|
DT_COLUMN_PROMPTS_TL
DT_DATE_PROMPTS_TL
DT_TITLE_PROMPTS_TL
INSERT
INTO DT_TITLE_PROMPTS_TL (LANGUAGE,
SOURCE_LANG,
VIEW_NAME,
TITLE_PROMPT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
VALUES
('AR',
'AR',
'XXX_HR_DT_DATA_D',
'القالب',
SYSDATE,
-1,
-1,
1,
SYSDATE);
INSERT
INTO DT_TITLE_PROMPTS_TL (LANGUAGE,
SOURCE_LANG,
VIEW_NAME,
TITLE_PROMPT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
VALUES
('US',
'US',
'XXX_HR_DT_DATA_D',
'DT
Template',
SYSDATE,
-1,
-1,
1,
SYSDATE);
COMMIT;
|
Deploy Sample Form
|
Review
…
Block
Triggers
Session_Date
procedure
Initialize_form
procedure
|