Register Table in Oracle Apps

 Register Table in Oracle Apps


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

select * from apps.XX_EMP_ADI


drop table apps.XX_EMP_ADI


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

Create Table

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


create table apps.XX_EMP_ADI

( HDR_ID NUMBER,

 NAME  VARCHAR2(100),

 EMP_NO VARCHAR2(100),

 GRADE VARCHAR(2),

 DOB DATE

 )


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

Check Table

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


 select * from apps.XX_EMP_ADI


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

Check  Table is created in Database

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


 select * from all_objects

 where object_name = 'XX_EMP_ADI'



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

Check Table is register or Not

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


 select * from fnd_tables

 where table_name = 'XX_EMP_ADI'



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

Check Table Columns are register or Not

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


 select * from fnd_columns

 where table_id = ( select table_id from fnd_tables

 where table_name = 'XX_EMP_ADI')


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

LAB

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

------------------------------------------------------------------------------

--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 = 'Receivables')) 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_EMP_ADI'

--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 = 'Receivables')) 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_EMP_ADI') 

    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 = 'Receivables')) 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_EMP_ADI') 

    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 = 'EMPNO'

    ;


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; 

Comments

Post a Comment

Popular posts from this blog

opaque schema xsd (standard file used in OIC)

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>
Read more

SQL,PLSQL interview practice and DSA patterns

 --=============sql=========================== --How to find duplicate records with the number they are duplicated? select job,count(*) from emp group by job; --Write a Query to select to the Nth highest salary from a table? select min(sal) from (select  sal from emp order by sal desc) where rownum < 3;   --Can Dual table be deleted or dropped or altered or inserted? select round(123.45) from dual; --it cant be deleted or dropped or altered or inserted --List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps? select * from emp where deptno = 20  and sal>(select avg(sal)from emp where deptno = 10); --List the empno, ename, sal, dname of all the 'Mgrs' and 'Analyst' working in NEWYORK, --DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc? select empno,ename,sal,dname from emp,dept where emp.deptno= dept.deptno and  loc in('NEWYORK', 'DALLAS') and job in ('manager','analyst')and trunc(mo...
Read more

QR code Generate From Oracle PLSQL

QR code Generate From Oracle PLSQL Here is a GITHUB link For necessary Files. https://github.com/Rehman0270/PLSQLQRcode 1) Download The required files from above link. 2) upload all jar archives in lib folder to your database  (Eample path in WINSCP to put the jar files : /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib ) 3) After uploading open putty and change directory to lib folder in WINSCP and run below commands one by one cd  /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose javase-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose qrgen-1.2.jar 4) Open sql developer and run this functions CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeBean" as import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; import java.sql.*; import javax.imageio.ImageIO; import java.awt.image.Buffered...
Read more