Event Base Alert in Oracle Apps

Event Base Alert in Oracle Apps
--=================================================================
select * from XXSD.XX_EVENT_BASE_ALERT_TL

drop table XXSD.XX_EVENT_BASE_ALERT_TL

--===================================
Create Table
--===================================

create table XX_EVENT_BASE_ALERT_TL
( HDR_ID NUMBER,
 NAME  VARCHAR2(100),
 EMP_NO VARCHAR2(100),
 GRADE VARCHAR(2),
DOB DATE,
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
 )


--===================================
select check
--===================================

 select * from XX_EVENT_BASE_ALERT_TL

--===================================
Check Table isregister in database or not
--===================================

 select * from all_objects
 where object_name = 'XX_EVENT_BASE_ALERT_TL'

--===================================
Check Table  is register in Apps or not
--===================================


 select * from fnd_tables
 where table_name = 'XX_EVENT_BASE_ALERT_TL'

--===================================
Check Table columns are register in Apps or not
--===================================

 select * from fnd_columns
 where table_id = (  select TABLE_ID from fnd_tables
 where table_name = 'XX_EVENT_BASE_ALERT_TL')

--===============================================================
------------------------------------------------------------------------------
--REGISTER TABLE--
------------------------------------------------------------------------------

DECLARE

CURSOR C1 IS

SELECT (select APPLICATION_SHORT_NAME from fnd_application where APPLICATION_ID  =
(select APPLICATION_ID from fnd_application_tl where APPLICATION_NAME = 'XXSD Custom Application')) APP_SHORT_NAME,
A.OBJECT_NAME, 'T' TABLE_T,  A.OWNER
FROM DBA_OBJECTS A
WHERE 1=1
AND TRIM(A.OBJECT_NAME) NOT IN (SELECT TABLE_NAME FROM FND_TABLES)
AND A.OBJECT_NAME LIKE 'XX_EVENT_BASE_ALERT_TL'
--AND A.OBJECT_TYPE NOT IN ('SYNONYM')
;



BEGIN

    FOR I IN C1 LOOP

        AD_DD.REGISTER_TABLE (I.APP_SHORT_NAME,I.OBJECT_NAME,I.TABLE_T);

    END LOOP;

END;


------------------------------------------------------------------------------
--REGISTER_COLUMN--
------------------------------------------------------------------------------

DECLARE

CURSOR C1 IS

    SELECT (SELECT APPLICATION_SHORT_NAME FROM FND_APPLICATION WHERE APPLICATION_ID  =
    (SELECT APPLICATION_ID FROM FND_APPLICATION_TL WHERE APPLICATION_NAME = 'XXSD Custom Application')) APP_SHORT_NAME,
   TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID,NULLABLE,OWNER
    FROM DBA_TAB_COLUMNS
    WHERE TABLE_NAME IN ( SELECT OBJECT_NAME FROM DBA_OBJECTS A WHERE A.OBJECT_NAME LIKE 'XX_EVENT_BASE_ALERT_TL')
    AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM FND_TABLES WHERE TABLE_ID  IN (SELECT TABLE_ID FROM FND_COLUMNS));
   


BEGIN

    FOR I IN C1 LOOP

        AD_DD.REGISTER_COLUMN (I.APP_SHORT_NAME, I.TABLE_NAME,I.COLUMN_NAME, I.COLUMN_ID, I.DATA_TYPE, I.DATA_LENGTH, 'N', 'N');
       
    END LOOP;

END;

------------------------------------------------------------------------------
--REGISTER PRIMARY KEY and Primary Column --
------------------------------------------------------------------------------

DECLARE

CURSOR C1 IS

    SELECT (SELECT APPLICATION_SHORT_NAME FROM FND_APPLICATION WHERE APPLICATION_ID  =
    (SELECT APPLICATION_ID FROM FND_APPLICATION_TL WHERE APPLICATION_NAME = 'XXSD Custom Application')) APP_SHORT_NAME,
    TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID,NULLABLE,OWNER
    FROM DBA_TAB_COLUMNS
    WHERE TABLE_NAME IN ( SELECT OBJECT_NAME FROM DBA_OBJECTS A WHERE A.OBJECT_NAME LIKE 'XX_EVENT_BASE_ALERT_TL')
    AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM FND_TABLES WHERE TABLE_ID  IN (SELECT TABLE_ID FROM FND_PRIMARY_KEYS))
    AND COLUMN_NAME = 'HDR_ID'
    ;

BEGIN

    FOR I IN C1 LOOP

        AD_DD.REGISTER_PRIMARY_KEY(I.APP_SHORT_NAME,I.COLUMN_NAME,I.TABLE_NAME,I.DATA_TYPE,'S','Y','Y');

        AD_DD.REGISTER_PRIMARY_KEY_COLUMN(I.APP_SHORT_NAME, I.COLUMN_NAME, I.TABLE_NAME, I.COLUMN_NAME, 1);


    END LOOP;

END;

-----------------------------------------------------------------------------
RUN this Script from Backend testing
-----------------------------------------------------------------------------

declare

BEGIN

--fnd_global.APPS_INITIALIZE(l_user_id,l_resp_id, l_resp_appl_id);
fnd_global.APPS_INITIALIZE(1111,20419, 0);

insert into XX_EVENT_BASE_ALERT_TL ( HDR_ID,  NAME,  EMP_NO,  GRADE, DOB )
values ( '2','ORACLE ','1001','L8','1-MAY-1982');

commit;

END;

------------------------------------------------------------------------------
--Check our event base alert using this query--
------------------------------------------------------------------------------
SELECT REQUEST_ID

, REQUEST_DATE

, PHASE_CODE

, STATUS_CODE

, ORACLE_ID

, PROGRAM_APPLICATION_ID

, CONCURRENT_PROGRAM_ID

FROM APPLSYS.FND_CONCURRENT_REQUESTS

where Concurrent_program_id = (select Concurrent_program_id from fnd_concurrent_programs_tl where

USER_CONCURRENT_PROGRAM_NAME = 'Check Event Alert') order by request_date desc;

--================================================================


SELECT NAME, EMP_NO,GRADE
INTO  &V_NAME, &V_EMP_NO, &V_GRADE
FROM XXSD_EVENT_ALERT_TL A
WHERE  1=1
AND A.ROWID = :ROWID ;


NOTE:-
you must include ROWID in where clause of your alert other wise alert will not locate the row where the event or transaction is occured.