ADD Column in Package /Procedure Base ADI in Oracle

ADD Column in Package /Procedure Base ADI in Oracle :-

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

--First Time

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

CREATE TABLE APPS.XXSD_PROC_ADI_TL

(

  EMPLOYEE_NUMBER  VARCHAR2(100 BYTE),

  FIRST_NAME       VARCHAR2(100 BYTE),

  LAST_NAME        VARCHAR2(100 BYTE),

  DATE_OF_BIRTH    DATE,

  GRADE            VARCHAR2(10 BYTE)

)

select * from XXSD_PROC_ADI_TL

edit XXSD_PROC_ADI_TL

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

--Add Column thru ALter Table

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

Alter Table XXSD_PROC_ADI_TL ADD ( ATTRIBUTE1 VARCHAR2(100))

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

CREATE OR REPLACE PACKAGE APPS.XX_PROCEDURE_ADI  AS

  PROCEDURE XX_INSERT_DATA_ADI (

 P_EMPLOYEE_NUMBER  VARCHAR2

,P_FIRST_NAME VARCHAR2

,P_LAST_NAME VARCHAR2

,P_DATE_OF_BIRTH DATE

,P_GRADE VARCHAR2

,P_ATTRIBUTE1 VARCHAR2

);    

END;

/

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

CREATE OR REPLACE PACKAGE BODY APPS.XX_PROCEDURE_ADI  AS

PROCEDURE XX_INSERT_DATA_ADI (

 P_EMPLOYEE_NUMBER  VARCHAR2

,P_FIRST_NAME VARCHAR2

,P_LAST_NAME VARCHAR2

,P_DATE_OF_BIRTH DATE

,P_GRADE VARCHAR2

,P_ATTRIBUTE1 VARCHAR2

) IS         

      

      lc_sqlerrm              VARCHAR2 (2000);

      lc_error_msg            VARCHAR2 (2000);

     

      l_responsibility_id     NUMBER := apps.fnd_global.resp_id;

      l_resp_application_id   NUMBER := apps.fnd_global.resp_appl_id;

      l_org_id                NUMBER := apps.fnd_global.org_id;

      l_user_id               NUMBER := apps.fnd_global.user_id;

     

     

    BEGIN

      

    INSERT INTO XXSD_PROC_ADI_TL (

    EMPLOYEE_NUMBER

    ,FIRST_NAME

    ,LAST_NAME

    ,DATE_OF_BIRTH

    ,GRADE

    ,ATTRIBUTE1

     )

    VALUES (

    P_EMPLOYEE_NUMBER

    ,P_FIRST_NAME

    ,P_LAST_NAME

    ,P_DATE_OF_BIRTH

    ,P_GRADE

    ,P_ATTRIBUTE1

         );

    COMMIT;

     

    EXCEPTION WHEN OTHERS THEN

    lc_sqlerrm := SUBSTR(SQLERRM,1,1999);

    raise_application_error (-20001, 'OTHER_EXCEPTION - MSG | ' || lc_sqlerrm);

    END;

         

END;

/

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

------------------------------------------------------------------------------ 1

-- Fatching data for API

interface_code = 'XX_PROC_ADI_XINTG_INTF1'

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

select * from  BNE_INTERFACE_COLS_TL

where INTERFACE_CODE like 'XX_PROC_ADI%'

------------------------------------------------------------------------------ 2

Edit the sequence of column in below 2 columns (BNE_INTERFACE_COLS_B)

1. DISPLAY_ORDER

2. UPLOAD_PARAM_LIST_ITEM_NUM

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

edit  BNE_INTERFACE_COLS_B

where  INTERFACE_CODE = 'XX_PROC_ADI_XINTG_INTF1'

------------------------------------------------------------------------------ 3

-- API  for Add column    

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

BEGIN

 BNE_INTEGRATOR_UTILS.UPSERT_INTERFACE_COLUMN (

20003, --P_APPLICATION_ID --

'XX_PROC_ADI_XINTG_INTF1', --- P_INTERFACE_CODE ---

6, -- P_SEQUENCE_NUM ---

1, -- P_INTERFACE_COL_TYPE -- Real=1 , Custom = 2

'P_ATTRIBUTE1', -- INTERFACE_COL_NAME -- P_ATTRIBUTE1 ---

'Y', --ENABLED_FLAG --

'Y', ---REQUIRED_FLAG ---

'Y', --- DISPLAY_FLAG ---

NULL,NULL,NULL,NULL,NULL,NULL,NULL,

'N', -- READ_ONLY_FLAG  --

NULL, --'JAVA', -- VAL_TYPE --  17

NULL,NULL,NULL,NULL,NULL,

2,  -- DATA_TYPE  (2 for Varchar , 3 for DATE, 1 for Number ) - 23

'N',  --- NOT_NULL_FLAG -- 24

null, --20003, ---  VAL_COMPONENT_APP_ID  -- 25

null, --'XXTEST_XINTG_INTF1_C5_COMP', -- VAL_COMPONENT_CODE -- 26

'N', -- SUMMARY_FLAG -- 27

'Y', -- P_PROMPT_LEFT -- 28

'ATTRIBUTE1', -- P_PROMPT_ABOVE -- 29

'ATTRIBUTE1', -- USER_HINT -- 30

NULL,NULL,

'US','US',

NULL,NULL,

NULL, --5, -- EXPANDED_SQL_QUERY --

NULL, --5, --  LOV_TYPE --

NULL,NULL,NULL,NULL,

1111   -- USER_ID --

);

END;

------------------------------------------------------------------------------  4

--Insert new column row Here

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

edit BNE_ATTRIBUTES

where ATTRIBUTE_CODE like 'XX_PROC_ADI%'

--and ATTRIBUTE1 = 'P_ATTRIBUTE1'

------------------------------------------------------------------------------  5

--Insert new column row Here

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

edit BNE_PARAM_LIST_ITEMS

where PARAM_LIST_CODE  like 'XX_PROC_ADI%'

--and PARAM_NAME like 'P_ATTRIBUTE1%'

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