How assign Sysadmin responsibility from back-end in oracle apps R12

 

Code 

   FND_USER_PKG is stand function 

  BEGIN

   FND_USER_PKG.ADDRESP

                       ('USERID',

                        'SYSADMIN',

                        'SYSTEM_ADMINISTRATOR',

                        'STANDARD',

                        'Add Sysadmin Responsibility to A user from Backend',

                        SYSDATE - 1,

                        SYSDATE + 99

                       );

   COMMIT;

   DBMS_OUTPUT.PUT_LINE ('Responsibility Added Successfully');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE (   ' Sys Admin Resp cannot be added: '

                            || SQLCODE

                            || SUBSTR (SQLERRM, 1, 100)

                           );

END;

FNDLOAD- Oracle Table

 

     Oracle Apps Table Migration using XDF Utility  

                            Oracle E Business Suite provides utilities like FNDLOAD,WFLOAD to migrate Application Objects from one instance to another.Similarly it provides XDF utility to migrate Database Objects.XDF utility is a Java class ,but a wrapped version of the it is available to be executed as Perl script too.Let us take an example to understand the utility in a better manner.Firstly we will create a custom table in a custom schema ,then generate the XDF file ,and finally apply the script to recreate the objects.

Requirement

                    Create XDF file for Table STRUCTURE

Input Data 

 Table names 

              BNE.XXST_GOLD_TAX_AUDIT_T

               BNE.XXST_GOLD_IMPORT_INT_BNE_T

Please follow below steps to genrate XDF file and upload into other instances

--#Step1

--  Create the initial table definition in your development database

create table BNE.XXST_GOLD_TAX_AUDIT_T
(
  OPERATING_UNIT VARCHAR2(100),
  INVOICE_NUM    VARCHAR2(200),
  VAT_CODE       VARCHAR2(100),
  VAT_NUMBER     VARCHAR2(100),
  VAT_DATE       DATE,
  USER_NAME      VARCHAR2(30),
  RESP_NAME      VARCHAR2(100),
  CREATION_DATE  DATE
)

create table BNE.XXST_GOLD_IMPORT_INT_BNE_T
(
  OPERATING_UNIT        VARCHAR2(100),
  SOURCE                VARCHAR2(100),
  INVOICE_NUM           VARCHAR2(200),
  INVOICE_LINE_NUM      VARCHAR2(10),
  CURRENCY              VARCHAR2(20),
  BILL_TO_CUSTOMER_NAME VARCHAR2(1000),
  VAT_CODE              VARCHAR2(100),
  VAT_NUMBER            VARCHAR2(100),
  VAT_DATE              DATE,
  CREATION_DATE         DATE,
  USER_NAME             VARCHAR2(100),
  RESPO_NAME            VARCHAR2(100),
  STATUS                VARCHAR2(5)
)

#Step2   Upgrade the table for Online Patching using the AD_ZD_TABLE.UPGRADE procedure

   ---This will generate an Editioning View (EV) for the table and then create an APPS synonym that points to the Editioning View.

     login in PLSQL and run below command

•   exec ad_zd_table.upgrade('<app_name>', '<Table_name>');
•   exec ad_zd_table.upgrade('BNE', 'XXST_GOLD_TAX_AUDIT_T');
•   exec ad_zd_table.upgrade('BNE', 'XXST_GOLD_IMPORT_INT_BNE_T'); 

#Step3

               Identify mapping between Apps synonym and table 

•           cd $AD_TOP/sql
• sqlplus --connect with DB.
• --Run sql 
• @ADZDSHOWEV.sql XXST_GOLD_TAX_AUDIT_T
•     @ADZDSHOWEV.sql XXST_GOLD_IMPORT_INT_BNE_T

---you can run below command.

1. $AD_TOP/sql/ADZDSHOWEV.sql XXST_GOLD_TAX_AUDIT_T
2.        $AD_TOP/sql/ADZDSHOWEV.sql XXST_GOLD_IMPORT_INT_BNE_T

#Step 4 --Export table definition from Database and generate XDF files
          

 Extract the table definition from your development database using the xdfgen.pl utility.   
 cd   $FND_TOP/bin/

•  perl xdfgen.pl apps/Stiapps4dev XXST_GOLD_TAX_AUDIT_T
•  perl xdfgen.pl apps/Stiapps4dev XXST_GOLD_IMPORT_INT_BNE_T

  # Step 5 -- Upload XDF into other instance Database

   perl xdfgen.pl <apps_user>/<apps_password> XYZ_USER_SERVICE

===========================================================

   ---Note if you are alterting table then run below command to update existing EV

   Regenerate the editioning view using AD_ZD_TABLE.PATCH

      exec ad_zd_table.patch('APPLSYS', 'XXST_GOLD_TAX_AUDIT_T')