DECLARE
FH UTL_FILE.FILE_TYPE;
LN VARCHAR2(300);
V_NAME VARCHAR2(30);
V_JOB VARCHAR2(30);
V_SAL NUMBER(7,2);
POS1 NUMBER(2);
POS2 NUMBER(2);
BEGIN
FH :=UTL_FILE.FOPEN (
'D:\ORACLE\VISDB\8.1.6\PLSQL\TEMP',
'A.LST',
'R');
LOOP
UTL_FILE.GET_LINE(FH, LN);
POS1 := INSTR(LN,',',1,1);
POS2 := INSTR(LN,',',1,2);
V_NAME := SUBSTR(LN,1,POS1-1);
V_JOB := SUBSTR(LN,POS1+1,POS2 - POS1 -1);
V_SAL := RTRIM(SUBSTR(LN,POS2+1));
INSERT INTO UTLX
VALUES
(V_NAME, V_JOB, V_SAL);
-- DBMS_OUTPUT.PUT_LINE(V_NAME||' '||V_JOB||' '||V_SAL);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(FH);
COMMIT;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
/
Thursday, March 11, 2010
Oracle Apps- Multi Org Setup FAQ
1. What is multi org architecture?
The multiorg architecture is meant to allow multiple companies or subsidiaries to store their records within a single database. The multiple Organization architecture allows this by partitioning data through views in the APPS schema. Multiorg also allows you to maintain multiple sets of books. Implementation of multiorg generally includes defining more than one Business Group.
2. How can I know if I am using multi org?
Run the SQL statement: select multi_org_flag from fnd_product_groups;
The result 'Y' means your database is setup for multiorg.
3. What are the relationships I can define in a multi org environment?
You define the relationships among inventory organizations, operating units, legal entities, Business Groups, and sets of books to create a multilevel company structure:
Business Groups separate major segments of a business. Each can have its own set of books. Each Group will also have a structure of other organizations classifications assigned to it.
1. Legal Entities post to a Set of Books
2. Operating Units are part of a Legal Entity
3. Inventory Organizations are part of an Operating Unit
4. Inventory Organizations define and maintain items used by other manufacturing modules (Order Entry, Purchasing, MRP, etc.). They also collect and pass data to the Financials modules.
4. I want to setup additional organizations, do I have to setup multiorg?
No, you do not need to setup multiorg. You do not have to be multi-org to have multiple organizations only if you intend to have multiple sets of books.
5. When my organization hierarchy changes, can I move an organization from one set of books or legal entity to another?
No, you should not try to move an organization from one set of books or legal entity to another because your data may not be valid for the new set of books or legal entity. Instead, you should disable the old organization and create a new organization for the appropriate set of books or legal entity. The new organization will contain your new data, and the disabled organization will act as an "old" or "history" entity that stores past transactions.
6. How can I setup a child org to be its own costing organization?
Steps:
1. Change the attribute control for "costing enabled" to organization level
2. Change the attribute control for "inventory asset value" to organization control Then you can make a child organization it's own costing organization by entering the organization name in the column labeled "Costing Organization"
7. How can I define organization restriction?
Use the Organization Access form (INVSDORA) to restrict the list of organizations displayed for each responsibility level. Refer to Oracle Inventory User's Guide for steps and important notes.
**WARNING** If you populate any rows in this form, you MUST populate a row for EACH responsibility that you wish to have access to that Organization.
8. What responsibility do I need to use to setup organization?
Use the General Ledger responsibility to define the Set of Books Use the Inventory Responsibility to define Organizations and other related information such as Inventory Key Flexfields, Locations, Workday calendar, other Organization Classifications, and other inventory information.
9. What are the main profile options relating to Organization setup and what are they used for?
1. HR: User Type = HR User This is necessary to allow the Inventory responsibility to complete the organization setup. Setting the profile to a value of 'User' as opposed to 'Payroll & User' will restrict the Inventory user from accessing any Payroll information if Oracle Payroll is installed.
2. HR: Business Group = {the users Business Group name} This points the responsibility to the appropriate Business Group. When multiple Business Groups are defined, you must associate each responsibility with one and only one Business Group. A responsibility can not see organization data from more than one Business Group.
3. MO: Operating Unit = {the users Operating Unit name} Used primarily in a multiorg environment.
This points the responsibility to the appropriate Operating Unit. Set the site level to the desired default operating unit If there is more than 1 Operating Unit Defined, this profile option must be set at the responsibility level for each responsibility.
The multiorg architecture is meant to allow multiple companies or subsidiaries to store their records within a single database. The multiple Organization architecture allows this by partitioning data through views in the APPS schema. Multiorg also allows you to maintain multiple sets of books. Implementation of multiorg generally includes defining more than one Business Group.
2. How can I know if I am using multi org?
Run the SQL statement: select multi_org_flag from fnd_product_groups;
The result 'Y' means your database is setup for multiorg.
3. What are the relationships I can define in a multi org environment?
You define the relationships among inventory organizations, operating units, legal entities, Business Groups, and sets of books to create a multilevel company structure:
Business Groups separate major segments of a business. Each can have its own set of books. Each Group will also have a structure of other organizations classifications assigned to it.
1. Legal Entities post to a Set of Books
2. Operating Units are part of a Legal Entity
3. Inventory Organizations are part of an Operating Unit
4. Inventory Organizations define and maintain items used by other manufacturing modules (Order Entry, Purchasing, MRP, etc.). They also collect and pass data to the Financials modules.
4. I want to setup additional organizations, do I have to setup multiorg?
No, you do not need to setup multiorg. You do not have to be multi-org to have multiple organizations only if you intend to have multiple sets of books.
5. When my organization hierarchy changes, can I move an organization from one set of books or legal entity to another?
No, you should not try to move an organization from one set of books or legal entity to another because your data may not be valid for the new set of books or legal entity. Instead, you should disable the old organization and create a new organization for the appropriate set of books or legal entity. The new organization will contain your new data, and the disabled organization will act as an "old" or "history" entity that stores past transactions.
6. How can I setup a child org to be its own costing organization?
Steps:
1. Change the attribute control for "costing enabled" to organization level
2. Change the attribute control for "inventory asset value" to organization control Then you can make a child organization it's own costing organization by entering the organization name in the column labeled "Costing Organization"
7. How can I define organization restriction?
Use the Organization Access form (INVSDORA) to restrict the list of organizations displayed for each responsibility level. Refer to Oracle Inventory User's Guide for steps and important notes.
**WARNING** If you populate any rows in this form, you MUST populate a row for EACH responsibility that you wish to have access to that Organization.
8. What responsibility do I need to use to setup organization?
Use the General Ledger responsibility to define the Set of Books Use the Inventory Responsibility to define Organizations and other related information such as Inventory Key Flexfields, Locations, Workday calendar, other Organization Classifications, and other inventory information.
9. What are the main profile options relating to Organization setup and what are they used for?
1. HR: User Type = HR User This is necessary to allow the Inventory responsibility to complete the organization setup. Setting the profile to a value of 'User' as opposed to 'Payroll & User' will restrict the Inventory user from accessing any Payroll information if Oracle Payroll is installed.
2. HR: Business Group = {the users Business Group name} This points the responsibility to the appropriate Business Group. When multiple Business Groups are defined, you must associate each responsibility with one and only one Business Group. A responsibility can not see organization data from more than one Business Group.
3. MO: Operating Unit = {the users Operating Unit name} Used primarily in a multiorg environment.
This points the responsibility to the appropriate Operating Unit. Set the site level to the desired default operating unit If there is more than 1 Operating Unit Defined, this profile option must be set at the responsibility level for each responsibility.
SQL Loader Mostly asked Interview questions
1. What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
2. How does one use the SQL*Loader utility?
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters.
Look at the following example:
sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data:
load data
infile 'c:\data\mydata.csv'
into table emp ( empno, empname, sal, deptno )
fields terminated by "," optionally enclosed by '"'
The mydata.csv file may look like this:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.
load data
infile *
replace
into table departments ( dept position (02:05) char(4), deptname position (08:27) char(20) )
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
3. Is there a SQL*Unloader to download data to a flat file?
Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3 from tab1 where col2 = 'XYZ';
spool off
Alternatively use the UTL_FILE PL/SQL package:
rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
You might also want to investigate third party tools like TOAD or ManageIT Fast Unloader from CA to help you unload data from Oracle.
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
2. How does one use the SQL*Loader utility?
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters.
Look at the following example:
sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data:
load data
infile 'c:\data\mydata.csv'
into table emp ( empno, empname, sal, deptno )
fields terminated by "," optionally enclosed by '"'
The mydata.csv file may look like this:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.
load data
infile *
replace
into table departments ( dept position (02:05) char(4), deptname position (08:27) char(20) )
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
3. Is there a SQL*Unloader to download data to a flat file?
Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3 from tab1 where col2 = 'XYZ';
spool off
Alternatively use the UTL_FILE PL/SQL package:
rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
You might also want to investigate third party tools like TOAD or ManageIT Fast Unloader from CA to help you unload data from Oracle.
Subscribe to:
Posts (Atom)