Scribd
Upload
1K views2 pages

XXBBB GL Data Import File Typ Table Script

The document outlines the creation of a database table named 'XXBBB_GL_DATA_IMPORT_FILE_TYP' with various fields and constraints, including a primary key. It also includes the definition of a trigger for auto-generating file type IDs, the creation of a public synonym for the table, and the granting of permissions to different roles. Additionally, it contains SQL commands for inserting data, truncating the table, creating directories, and querying various related tables.

Uploaded by

Pavankumar Trvd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
1K views2 pages

XXBBB GL Data Import File Typ Table Script

The document outlines the creation of a database table named 'XXBBB_GL_DATA_IMPORT_FILE_TYP' with various fields and constraints, including a primary key. It also includes the definition of a trigger for auto-generating file type IDs, the creation of a public synonym for the table, and the granting of permissions to different roles. Additionally, it contains SQL commands for inserting data, truncating the table, creating directories, and querying various related tables.

Uploaded by

Pavankumar Trvd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

CREATE TABLE "XXBBB".

"XXBBB_GL_DATA_IMPORT_FILE_TYP"
( "FILE_TYPE_ID" NUMBER,
"CREATED_BY" NUMBER,
"CREATED_DATE" DATE,
"LAST_UPDATED_BY" NUMBER,
"LAST_UPDATE_DATE" DATE,
"FILE_TYPE_NAME" VARCHAR2(20 BYTE),
"FILE_TYPE_DESC" VARCHAR2(255 BYTE),
"PROCEDURE_NAME" VARCHAR2(200 BYTE),
"PARSE_PROCEDURE" VARCHAR2(200 BYTE),
"CONCURRENT_REQUEST" VARCHAR2(1 BYTE),
CONSTRAINT "XXBBB_GL_DATA_IMP_FILE_TYP" PRIMARY KEY ("FILE_TYPE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
TABLESPACE "XXBBBIDX" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT);
TABLESPACE "XXBBBTAB" ;

CREATE OR REPLACE EDITIONABLE TRIGGER "XXBBB"."XXBBB_GL_DATA_IMP_FILE_TYP"


before insert on XXBBB_GL_DATA_IMPORT_FILE_TYP
for each row
declare
uid number;
begin
if :new.file_type_id is null then
select XXBBB_GL_DATA_FILE_SEQ.nextval
into uid
from dual;
:new.AC := uid;
end if;
end;
/
ALTER TRIGGER "XXBBB"."XXBBB_GL_DATA_IMP_FILE_TYP" ENABLE;

CREATE OR REPLACE NONEDITIONABLE PUBLIC SYNONYM "XXBBB_GL_DATA_IMPORT_FILE_TYP" FOR


"XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP";

GRANT SELECT ON "XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP" TO "APPS_DEV_RO";


GRANT DELETE ON "XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP" TO "APPS" WITH GRANT
OPTION;
GRANT INSERT ON "XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP" TO "APPS" WITH GRANT
OPTION;
GRANT SELECT ON "XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP" TO "APPS" WITH GRANT
OPTION;
GRANT UPDATE ON "XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP" TO "APPS" WITH GRANT
OPTION;
GRANT SELECT ON "XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP" TO "APPS_RO";
GRANT SELECT ON "XXBBB"."XXBBB_GL_DATA_IMPORT_FILE_TYP" TO "APPS_DEV_RO";
INSERT INTO XXBBB_GL_DATA_IMPORT_FILE_TYP values(72,1130,'25-SEP-15',1130,'25-SEP-
15','NIGHTLY_CURR_RATE',
'BBB Nightly Currency Exchange
Update','XXBBB_GL_CUR_EXCHANGE_IMP_PKG.load_daily_exchange_rates','','Y');

truncate table xxbbb.XXBBB_GL_DATA_IMPORT_FILE_TYP;

commit

select *
from v$instance;

create or replace directory EBSCRP_INTERFACE as


'/u03/app/applmgr/EBSCRP/fs_ne/EBSapps/appl/xxbbb/12.0.0/interfaces/
daily_exchange/archive';

GRANT READ ON DIRECTORY EBSCRP_INTERFACE TO APPS;

create or replace directory '/tmp/';

show user

select * from all_directories;

select * from XXBBB_GL_DATA_IMP_FILE;

select * from XXBBB_GL_DATA_IMPORT_COL_MAP;

select * from XXBBB_GL_DATA_IMPORT_COLUMNS;

select * from XXBBB_GL_DATA_IMPORT_FILE_TYP;--actual table data load

select * from XXBBB_GL_DATA_IMPORT_SECURITY;

select * from XXBBB_GL_DATA_IMPORT_STAGE;

select * from gl_daily_rates_interface;

select * from gl_daily_rates;

select * from fnd_user where user_id=1138;

You might also like