-- +====================================================================+
-- | SRI .,
-- | Bangalore, India
-- +====================================================================+
-- |
-- | $Id: SRIHRCONTACTS.ddl 1.0 2010/10/18 SRI
-- |
-- |Description : SRIHRCONTACTS.ddl ,
-- | Creating SRI Custom Database Objects:
-- | Tables/Views/Grants/Synonyms
-- |Change History:
-- |---------------
-- |Version Date Author Remarks
-- |------- ------------ ----------------- -----------------------------
-- |Draft1A 20-OCT-2010 SRI S Initial Version
-- +====================================================================+
---------------------------------------------------------------------------
---- Enter SYSTEM-Schema Login-information
---------------------------------------------------------------------------
/*
ACCEPT system_schema_name PROMPT 'Enter System Schema Name: '
ACCEPT system_schema_pwd PROMPT 'Enter Schema Password: ' HIDE
ACCEPT database_sid PROMPT 'Enter SID: '
----
CONNECT &system_schema_name/&system_schema_pwd@&database_sid
*/
-------------------------------------------------------------------------
-- Enter APPS-Schema Login-information
-------------------------------------------------------------------------
ACCEPT apps_schema_name PROMPT 'Enter APPS Schema Name: '
ACCEPT apps_schema_pwd PROMPT 'Enter APPS Password: ' HIDE
ACCEPT database_sid PROMPT 'Enter SID: '
--
-------------------------------------------------------------------------
-- Enter CNV-Schema Login-information
-------------------------------------------------------------------------
ACCEPT SRI_schema_name PROMPT 'Enter SRI Custom Schema Name : (xxsri) '
ACCEPT SRI_schema_pwd PROMPT 'Enter SRI Custom Schema Password: ' HIDE
--
------------------------------------------------------------------------
-- Connect to APPS-Schema
-------------------------------------------------------------------------
CONNECT &apps_schema_name/&apps_schema_pwd@&database_sid
-------------------------------------------------------------------------
-- Drop SRI Synonyms created in APPS Schema
-------------------------------------------------------------------------
DROP SYNONYM apps.SRI_emp_contacts_data;
DROP SYNONYM apps.SRI_emp_contacts_trnsf;
DROP SYNONYM apps.SRI_emp_contacts_stg;
-------------------------------------------------------------------------
-- Connect to CNV-Schema
-------------------------------------------------------------------------
CONNECT &SRI_schema_name/&SRI_schema_pwd@&database_sid
-------------------------------------------------------------------------
-- Create Custom-Database-Objects in SRI schema for APPS
-------------------------------------------------------------------------
DROP TABLE xxsri.SRI_emp_contacts_data;
DROP TABLE xxsri.SRI_emp_contacts_trnsf;
DROP TABLE xxsri.SRI_emp_contacts_stg;
DROP TABLE apps.SRI_emp_contacts_data;
DROP TABLE apps.SRI_emp_contacts_trnsf;
DROP TABLE apps.SRI_emp_contacts_stg;
PROMPT 'CREATING TABLE SRI_emp_contacts_data ...'
PROMPT 'DATA_TABLESPACE: APPS_TS_TX_DATA....'
CREATE TABLE xxsri.SRI_emp_contacts_data
(
EMPLOYEE_NUMBER VARCHAR2(30),
CATEGORY VARCHAR2(20),
LAST_NAME VARCHAR2(150),
FIRST_NAME VARCHAR2(150),
TITLE VARCHAR2(30),
MIDDLE_NAME VARCHAR2(60),
CONTACT_TYPE VARCHAR2(80),
RELATIONSHIP VARCHAR2(80),
BIRTH_DATE DATE,
GENDER VARCHAR2(6),
PRIMARY_CONTACT VARCHAR2(80),
BENEFICIARY VARCHAR2(80),
DEPENDENT VARCHAR2(80),
ADDRESS_LINE1 VARCHAR2(240),
ADDRESS_LINE2 VARCHAR2(240),
TOWN VARCHAR2(30),
COUNTY VARCHAR2(60),
POSTCODE VARCHAR2(30),
COUNTRY VARCHAR2(60),
ADDRESS_TYPE VARCHAR2(240),
PHONE_TYPE VARCHAR2(80),
PHONE_NUMBER VARCHAR2(60),
BUSINESS_GROUP VARCHAR2(240),
SOURCE_SYSTEM VARCHAR2(240),
PRIMARY_KEY VARCHAR2(30),
HR_CREATION_DATE DATE,
UPDATE_DATE DATE,
STATUS_FLAG VARCHAR2(2),
RECORD_ID NUMBER,
ERROR_CODE VARCHAR2(240),
ERROR_MESSAGE VARCHAR2(240),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY VARCHAR2(30),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(30),
LAST_UPDATE_LOGIN VARCHAR2(30),
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
CONTACT_PERSON_ID NUMBER,
ADDRESS_ID NUMBER,
PHONE_ID NUMBER,
STATE VARCHAR2(80),
EMIRATE VARCHAR2(80),
POSTAL_CODE VARCHAR2(80),
ELIGIBLE_INSURANCE VARCHAR2(100),
ELIGIBLE_TICKET VARCHAR2(80),
ELIGIBLE_MEDICAL VARCHAR2(80),
ELIGIBLE_EDUCATION VARCHAR2(80),
EMIRATE_LOV VARCHAR2(80),
PO_BOX VARCHAR2(80),
FLAT_DOOR_BLOCK_NUMBER VARCHAR2(150),
BUILDING_NAME_VILLAGE VARCHAR2(150),
ROAD_STREET_AREA VARCHAR2(150),
STATE_LOV VARCHAR2(100),
PIN_CODE VARCHAR2(25),
IC_NO_NEW VARCHAR2(25),
IC_NO_OLD VARCHAR2(25),
INCOME_TAX_NO VARCHAR2(25),
INCOME_TAX_BRANCH VARCHAR2(25),
CURRENT_STATUS VARCHAR2(25),
EMPLOYER_ESTABLISHMENT VARCHAR2(25),
ADDRESS_STYLE VARCHAR2(80),
employee_person_id NUMBER,
RELATIONSHIP_code VARCHAR2(10)
) TABLESPACE &data_tablespace
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
/
PROMPT 'CREATING TABLE SRI_emp_contacts_trnsf ...'
CREATE TABLE xxsri.SRI_emp_contacts_trnsf
(
EMPLOYEE_NUMBER VARCHAR2(30),
CATEGORY VARCHAR2(20),
LAST_NAME VARCHAR2(150),
FIRST_NAME VARCHAR2(150),
TITLE VARCHAR2(30),
MIDDLE_NAME VARCHAR2(60),
CONTACT_TYPE VARCHAR2(80),
RELATIONSHIP VARCHAR2(80),
BIRTH_DATE DATE,
GENDER VARCHAR2(6),
PRIMARY_CONTACT VARCHAR2(80),
BENEFICIARY VARCHAR2(80),
DEPENDENT VARCHAR2(80),
ADDRESS_LINE1 VARCHAR2(240),
ADDRESS_LINE2 VARCHAR2(240),
TOWN VARCHAR2(30),
COUNTY VARCHAR2(60),
POSTCODE VARCHAR2(30),
COUNTRY VARCHAR2(60),
ADDRESS_TYPE VARCHAR2(240),
PHONE_TYPE VARCHAR2(80),
PHONE_NUMBER VARCHAR2(60),
BUSINESS_GROUP VARCHAR2(240),
SOURCE_SYSTEM VARCHAR2(240),
PRIMARY_KEY VARCHAR2(30),
HR_CREATION_DATE DATE,
UPDATE_DATE DATE,
STATUS_FLAG VARCHAR2(2),
RECORD_ID NUMBER,
ERROR_CODE VARCHAR2(240),
ERROR_MESSAGE VARCHAR2(240),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY VARCHAR2(30),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(30),
LAST_UPDATE_LOGIN VARCHAR2(30),
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
CONTACT_PERSON_ID NUMBER,
ADDRESS_ID NUMBER,
PHONE_ID NUMBER,
STATE VARCHAR2(80),
EMIRATE VARCHAR2(80),
POSTAL_CODE VARCHAR2(80),
ELIGIBLE_INSURANCE VARCHAR2(100),
ELIGIBLE_TICKET VARCHAR2(80),
ELIGIBLE_MEDICAL VARCHAR2(80),
ELIGIBLE_EDUCATION VARCHAR2(80),
EMIRATE_LOV VARCHAR2(80),
PO_BOX VARCHAR2(80),
FLAT_DOOR_BLOCK_NUMBER VARCHAR2(150),
BUILDING_NAME_VILLAGE VARCHAR2(150),
ROAD_STREET_AREA VARCHAR2(150),
STATE_LOV VARCHAR2(100),
PIN_CODE VARCHAR2(25),
IC_NO_NEW VARCHAR2(25),
IC_NO_OLD VARCHAR2(25),
INCOME_TAX_NO VARCHAR2(25),
INCOME_TAX_BRANCH VARCHAR2(25),
CURRENT_STATUS VARCHAR2(25),
EMPLOYER_ESTABLISHMENT VARCHAR2(25),
ADDRESS_STYLE VARCHAR2(80),
employee_person_id NUMBER,
RELATIONSHIP_code VARCHAR2(10)
) TABLESPACE &data_tablespace
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
/
PROMPT 'CREATING TABLE SRI_emp_contacts_stg ...'
CREATE TABLE xxsri.SRI_emp_contacts_stg
(
EMPLOYEE_NUMBER VARCHAR2(30),
CATEGORY VARCHAR2(20),
LAST_NAME VARCHAR2(150),
FIRST_NAME VARCHAR2(150),
TITLE VARCHAR2(30),
MIDDLE_NAME VARCHAR2(60),
CONTACT_TYPE VARCHAR2(80),
RELATIONSHIP VARCHAR2(80),
BIRTH_DATE DATE,
GENDER VARCHAR2(6),
PRIMARY_CONTACT VARCHAR2(80),
BENEFICIARY VARCHAR2(80),
DEPENDENT VARCHAR2(80),
ADDRESS_LINE1 VARCHAR2(240),
ADDRESS_LINE2 VARCHAR2(240),
TOWN VARCHAR2(30),
COUNTY VARCHAR2(60),
POSTCODE VARCHAR2(30),
COUNTRY VARCHAR2(60),
ADDRESS_TYPE VARCHAR2(240),
PHONE_TYPE VARCHAR2(80),
PHONE_NUMBER VARCHAR2(60),
BUSINESS_GROUP VARCHAR2(240),
SOURCE_SYSTEM VARCHAR2(240),
PRIMARY_KEY VARCHAR2(30),
HR_CREATION_DATE DATE,
UPDATE_DATE DATE,
STATUS_FLAG VARCHAR2(2),
RECORD_ID NUMBER,
ERROR_CODE VARCHAR2(240),
ERROR_MESSAGE VARCHAR2(240),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY VARCHAR2(30),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(30),
LAST_UPDATE_LOGIN VARCHAR2(30),
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
CONTACT_PERSON_ID NUMBER,
ADDRESS_ID NUMBER,
PHONE_ID NUMBER,
STATE VARCHAR2(80),
EMIRATE VARCHAR2(80),
POSTAL_CODE VARCHAR2(80),
ELIGIBLE_INSURANCE VARCHAR2(100),
ELIGIBLE_TICKET VARCHAR2(80),
ELIGIBLE_MEDICAL VARCHAR2(80),
ELIGIBLE_EDUCATION VARCHAR2(80),
EMIRATE_LOV VARCHAR2(80),
PO_BOX VARCHAR2(80),
FLAT_DOOR_BLOCK_NUMBER VARCHAR2(150),
BUILDING_NAME_VILLAGE VARCHAR2(150),
ROAD_STREET_AREA VARCHAR2(150),
STATE_LOV VARCHAR2(100),
PIN_CODE VARCHAR2(25),
IC_NO_NEW VARCHAR2(25),
IC_NO_OLD VARCHAR2(25),
INCOME_TAX_NO VARCHAR2(25),
INCOME_TAX_BRANCH VARCHAR2(25),
CURRENT_STATUS VARCHAR2(25),
EMPLOYER_ESTABLISHMENT VARCHAR2(25),
ADDRESS_STYLE VARCHAR2(80),
employee_person_id NUMBER,
RELATIONSHIP_code VARCHAR2(10)
) TABLESPACE &data_tablespace
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
/
-------------------------------------------------------------------------
-- Create Grants/Synonyms for xxsri-Objects to APPS-Schema
-------------------------------------------------------------------------
PROMPT 'Creating Grants for xxsri-Schema Objects to APPS-Schema...'
-- Create Grants
GRANT ALL ON &xxsri_schema_name..SRI_emp_contacts_data TO &apps_schema_name WITH GRANT OPTION;
GRANT ALL ON &xxsri_schema_name..SRI_emp_contacts_trnsf TO &apps_schema_name WITH GRANT OPTION;
GRANT ALL ON &xxsri_schema_name..SRI_emp_contacts_stg TO &apps_schema_name WITH GRANT OPTION;
--
-------------------------------------------------------------------------
-- Connect to APPS-Schema
-------------------------------------------------------------------------
CONNECT &apps_schema_name/&apps_schema_pwd@&database_sid
-------------------------------------------------------------------------
-- Create Synonyms for xxsri-Objects to APPS-Schema
-------------------------------------------------------------------------
PROMPT ' Creating Synonyms for xxsri-Schema Objects to APPS-Schema...'
-- Create Synonyms
CREATE OR REPLACE SYNONYM &apps_schema_name..SRI_emp_contacts_data FOR &xxsri_schema_name..SRI_emp_contacts_data;
CREATE OR REPLACE SYNONYM &apps_schema_name..SRI_emp_contacts_trnsf FOR &xxsri_schema_name..SRI_emp_contacts_trnsf;
CREATE OR REPLACE SYNONYM &apps_schema_name..SRI_emp_contacts_stg FOR &xxsri_schema_name..SRI_emp_contacts_stg;
/
0 comments:
Post a Comment