HOW TO create table in Oracle R12 EBS database

-

Edition-Based Redefinition

EBR Upgrade or the R12.X.X Upgrade has very fine benefits

  • Downtime reduction
  • Run Edition
  • Patch Edition
  • Module upgrades
    •  Oracle Payments
      • Payment acknowledgement import
    • Sub ledger accounting 
      • Enhanced multi reporting currency functionality
    • MOAC
      • Multi Organization Access Control

There are change in approach on table creation or database object creation.

Step 1: Create a sequence in your custom schema

CREATE SEQUENCE xx_YOUR_SEQUENCE_seq START WITH 1 INCREMENT BY 1 NOCACHE;

Step 2: Provide Grants or Privileges from custom schema

GRANT ALTER, SELECT ON XXCustom_Schema.xx_YOUR_SEQUENCE_seq TO APPS WITH GRANT OPTION;

GRANT SELECT ON XXCustom_Schema.xx_YOUR_SEQUENCE_seq TO APPSRO;
 

Step 3: Create a table in your custom schema

CREATE TABLE XXCustom_Schema.XXCTS_YOUR_TABLE_TAB
(
  USER_ID               NUMBER default xx_YOUR_SEQUENCE_seq.nextval,
  USER_NAME             VARCHAR2(200 BYTE),
  ROLE_ID               NUMBER,
  EFFECTIVE_START_DATE  DATE                    DEFAULT trunc(sysdate),
  EFFECTIVE_END_DATE    DATE
)

Step 4: Provide Grants or Privileges from custom schema


GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, READ, DEBUG, FLASHBACK ON XXCustom_Schema.XXCTS_YOUR_TABLE_TAB TO APPS WITH GRANT OPTION;

GRANT  SELECT ON XXCustom_Schema.XXCTS_YOUR_TABLE_TAB TO APPSRO;

Step 5: Create Apps synonym from APPS schema


CREATE OR REPLACE SYNONYM apps. xx_YOUR_SEQUENCE_seq FOR XXCustom_Schema.xx_YOUR_SEQUENCE_seq

 

Step 6: Run AD_ZD Sciprt to upgrade the table from APPS schema

BEGIN
   ad_zd_table.upgrade (' XXCustom_Schema', 'XXCTS_YOUR_TABLE_TAB');
END;

 

 

 

 

 

 

 

Comments

Post a Comment

Popular posts from this blog

HOW TO auto approve workflow WF notification procedure

-
API to approve workflow notifications using a procedure in Oracle Apps R12 API: wf_notification Oracle Workflow communicates with users by sending notifications . Notifications contain messages that may request users to take some type of action and/or provide users with information. You define the notification activity and the notification message that the notification activity sends in the Workflow Builder. --To auto approve workflow notifications using a procedure. PROCEDURE approve_reject_proc (pAction IN VARCHAR2, --APPROVE/REJECT pComments IN VARCHAR2, -- User Comments -- Optional pNotification_id IN NUMBER, -- Notification ID pStatus OUT VARCHAR2, pMessage OUT VARCHAR2 ) IS l_user_name VARCHAR2(250); BEGIN BEGIN SELECT RECIPIENT_ROLE INTO l_user_name FROM WF_NOTIFICATIONS WHERE notification_id = pNotification_id; EXCEPTION WHEN OTHERS THEN l_user_name := NULL; END; wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction); wf_notification.Respond(pNotif...
Read more

HOW TO register table in oracle apps

-
API to register table in Oracle Apps R12 API: ad_dd.register_table You register your custom application tables using a PL/SQL routine in the AD_DD package. Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert. You can also use the AD_DD API to delete the registrations of tables and columns. You should delete the column registration first, then the table registration. To alter a registration you should first delete the registration, then re-register the table or column.  The AD_DD API does NOT check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need NOT register views. You should include call...
Read more

HOW TO update an existing user in oracle apps

-
API to create Accounting Key Flexfield Concatenated Segment description in Oracle Apps R12 API: GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION Update an existing Oracle User in R12 Oracle Apps using API -- FND_USER_PKG.UPDATEUSER We will try to update the email address for an existing user ‘DEMOUSER’ using the API.  There was no email_address populated to the user ‘DEMOUSER’ prior to running the API. DECLARE    v_user_name     VARCHAR2 (100)    := 'WIPUSER';    v_email_address  VARCHAR2 (100) := 'test@WIPRO.com'; BEGIN    fnd_user_pkg.updateuser                            (x_user_name                    => v_user_name,          ...
Read more