Register View in Oracle Apps

Register View in Oracle Apps
--=====================================================================

--==============================
Create View
--==============================

create or replace view XXSD_ALERT_V as
select EMP_NO,GRADE
from XXSD.XXSD_EVENT_BASE_ALERT_TL

--==============================
Select  View
--==============================

select * from XXSD_ALERT_V

--==============================
Check  View is created in Database
--==============================

select * from all_objects
where object_name = 'XXSD_ALERT_V'

--==============================
check view is register in Oracle Apps or Not
--==============================

select * from FND_VIEWS where view_name in ('XXSD_ALERT_V')


--==============================
check view Columns are register in Oracle Apps or Not
--==============================

select * from FND_VIEW_COLUMNS
where view_id in ( select view_id from FND_VIEWS where view_name in ('XXSD_ALERT_V'))

--====================================================================
LAB
--=====================================================================
------------------------------------------------------------------------------
--REGISTER VIEW
------------------------------------------------------------------------------


DECLARE

CURSOR C1 IS

SELECT (select APPLICATION_ID from fnd_application_tl where APPLICATION_NAME = 'XXSD Custom Application') APP_ID,
(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, --'V' TABLE_T,  A.OWNER,
B.TEXT ,
A.OBJECT_NAME||'( it showing Emp Grade)'DISCRIPTION
FROM DBA_OBJECTS A, user_views B
WHERE 1=1
and A.OBJECT_NAME = B.VIEW_NAME
AND TRIM(A.OBJECT_NAME) NOT IN (SELECT VIEW_NAME FROM FND_VIEWS)
AND A.OBJECT_NAME LIKE 'XXSD_ALERT_V'
--AND A.OBJECT_TYPE NOT IN ('SYNONYM')
;



BEGIN

    FOR I IN C1 LOOP
     
        FND_XDFDICTIONARY_PKG.uploadview(I.APP_SHORT_NAME,I.OBJECT_NAME,I.TEXT, I.DISCRIPTION,-1);

    END LOOP;

END;


------------------------------------------------------------------------------
--REGISTER_VIEW_COLUMNS
------------------------------------------------------------------------------
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 'XXSD_ALERT_V')
    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

        FND_XDFDICTIONARY_PKG.uploadviewcolumn(I.APP_SHORT_NAME,I.TABLE_NAME,I.COLUMN_NAME,I.COLUMN_ID,-1,'Y',sysdate);       
       
    END LOOP;

END;