To put data from csv file to a custom table

Following is the script

PROCEDURE load_csv_2_cust_table ( p_file_name IN VARCHAR2, p_file_path IN VARCHAR2 ) IS

l_file UTL_FILE.file_type;
l_file_line VARCHAR2 (4000);
temp_line VARCHAR2(4000);
csv_col VARCHAR(4000);
comma_pos1 NUMBER(5);
colon_pos1 NUMBER(5);
comma_pos2 NUMBER(5);
colon_pos2 NUMBER(5);
temp_str VARCHAR(4000);
table_counter NUMBER :=0;

BEGIN
l_file := UTL_FILE.fopen (p_file_path, p_file_name, 'r',32767);

IF UTL_FILE.is_open (l_file) THEN
LOOP
BEGIN
l_file_line := NULL;
UTL_FILE.get_line (l_file, l_file_line);
-- parse the delimited line
IF l_file_line IS NOT NULL
THEN gv_data_tbl.DELETE;
temp_line := l_file_line;
table_counter := 0;
LOOP
-- if temp line is null , exit out of for loop
IF trim(temp_line) IS NULL then EXIT ; END IF;
comma_pos1 := NVL(INSTR(temp_line,','),0);
colon_pos1 := NVL(INSTR(temp_line,'"'),0);
comma_pos2 := NVL(INSTR(temp_line,',',1,2),0);
colon_pos2 := NVL(INSTR(temp_line,'"',1,2),0);
-- only one column . no comma , colon
IF comma_pos1 = 0 THEN
temp_str := temp_line;
temp_line := null;
END IF;

-- no colon , but comma is there
IF colon_pos1 = 0 AND comma_pos1 <> 0
THEN
temp_str := SUBSTR(temp_line,1,comma_pos1-1);
temp_line := SUBSTR(temp_line,comma_pos1+1);
END IF;
-- comma and colon , comma comes first
IF colon_pos1 <> 0 AND comma_pos1 <>0 AND colon_pos1 > comma_pos1
THEN
temp_str := SUBSTR(temp_line,1,comma_pos1-1);
temp_line := SUBSTR(temp_line,comma_pos1+1);
END IF;

-- comma and colon , colon comes first
IF colon_pos1 <> 0 AND comma_pos1 <>0 AND colon_pos1 < comma_pos1
THEN
temp_str := SUBSTR(temp_line,2,colon_pos2-2);
temp_line := SUBSTR(temp_line,colon_pos2+2);
END IF;
table_counter := table_counter +1;
gv_data_tbl(table_counter):=temp_str;
END LOOP;
INSERT INTO custom_table(col1, col2, col3, col4) VALUES
(gv_data_tbl(1),gv_data_tbl(2),gv_data_tbl(3),gv_data_tbl(4));
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN EXIT;

END;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE;
END ;

Key Oracle Apps scripts and commands

-- Uploading and downloading concurrent programs
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ldt_name.ldt PROGRAM APPLICATION_SHORT_NAME="XX" CONCURRENT_PROGRAM_NAME="cp_short_name"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct ldt_name.ldt CUSTOM_MODE=FORCE


-- Responsibility from program name
select * from fnd_responsibility_tl where responsibility_id in(select responsibility_id from fnd_responsibility where request_group_id in(select request_group_id from fnd_request_group_units where request_unit_id in(select concurrent_program_id from fnd_concurrent_programs where concurrent_program_name like '%%')))

-- Responsibility from program name
select * from fnd_responsibility_tl where responsibility_id in (select responsibility_id from fnd_responsibility where request_group_id in (select request_group_id from fnd_request_group_units where request_unit_id in (select concurrent_program_id from fnd_concurrent_programs_tl where user_concurrent_program_name like '%')))
-- Executable from concurrent program name
select e.executable_name,e.execution_file_name from fnd_concurrent_programs c , fnd_concurrent_programs_tl ct, fnd_executables e where e.executable_id = c.executable_id and c.concurrent_program_id = ct.concurrent_program_id and ct.user_concurrent_program_name like '%%'

-- Getting primary key constraint
SELECT tbl2.column_name FROM all_constraints tbl1, all_cons_columns tbl2 WHERE tbl1.owner = 'OWNER_NAME' and tbl1.table_name LIKE UPPER('%%') AND tbl1.constraint_type = 'P' AND tbl1.owner = tbl2.owner AND tbl1.constraint_name = tbl2.constraint_name

-- Getting Initiating element from formula result rules element
select init_e.element_name initating_element ,res_e.element_name result_element,ff.formula_name,frr.RESULT_RULE_TYPEfrom PAY_FORMULA_RESULT_RULES_f frr, pay_element_types_f init_e, pay_element_types_f res_e, PAY_STATUS_PROCESSING_RULES_f psr, ff_formulas_f ffwhere frr.element_type_id = res_e.element_type_idand frr.status_processing_rule_id = psr.status_processing_rule_idand psr.element_type_id = init_e.element_type_idand ff.formula_id = psr.formula_idand res_e.element_name like 'GK403B3%'

Downloading AOL components
http://www.apps2fusion.com/apps/scripts/19-oracle-fndload-script-examples

1 - Printer Styles
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 - Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND" LOOKUP_TYPE="lookup name"

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"
Examples:Note <274528.1> - Note How To Download Single Context Using FNDLOAD For Descriptive Flexfield.Note <256573.1> - How To Download/Upload Descriptive Flexfield With Name $SRS$.

4 - Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"

6 - Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

8 - Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

8 - Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"

10 - Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SETAPPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"
Examples:Note <247126.1> - How To Download Request Set Definition From One Instance And Upload On Another Instance

11 - Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 - Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

Oracle Financials Key Points

Exporting Information to GL
(Product --> Transaction Type)

Payables --> Payments and invoices

Receivables --> Debit memos, credit memos, on-account credits,
invoices, chargebacks, receipts, adjustments,
guarantees, other receipts and deposits

Assets --> Deferred and standard depreciation expense
transactions and asset transaction information

Projects --> Labor cost, usage cost, vendor invoice
adjustments, and revenue information

Purchasing --> Accrued receipts
Cost Management/Inventory Inventory and work in process transactions


Importing Information to GL

Except for Oracle Assets, all subledgers integrate with Oracle General Ledger
(GL) through the GL_INTERFACE table.

• Run the Journal Import process automatically from the subledger at transaction transfer time or manually from Oracle General Ledger at a later time.

• Transfer information to Oracle General Ledger in detail (show all journal entry lines for transactions against each account within a category) or summary (show only totals for transactions against each account within a category).

Note: Oracle Assets transfers information directly into the GL_JE_BATCHES,
GL_JE_HEADERS, and GL_JE_LINES tables.



Oracle subledgers share:

The Oracle General Ledger set of books, consisting of a calendar, currency, and chart of accounts
The Oracle General Ledger exchange rates, including conversion rate types, daily rates, period rates, and historical rates
The Oracle General Ledger journal entry sources and categories used to record information about the origination and purpose of transactions




Set of Books
• A set of books determines the functional currency, account structure, and
accounting calendar for each company or group of companies.
• Each set of books has a number of options that indicate the accounting
practices you want to follow for that set of books.


Chart of Accounts
• Your chart of accounts is the account structure you define to fit the specific
needs of your organization.
• You can choose the number of account segments as well as the length, name,
and order of each segment.

Accounting Calendar
• The accounting calendar defines an accounting year and the periods it
contains.
• You can define multiple calendars and assign a different calendar to each set
of books.

Currencies
• You can select the functional currency for your set of books as well as other
currencies that you use to transact business.

OAF Personalizations Deployment Steps

Extracing From Source Instance

• Set profile option “FND: Personalization Document Root Path”. The value of this profile option will be the directory name on mid-tier
• Ensure that this directory exists on mid-tier machine of the destination environment
• Login to Functional Administrator Responsibility
• Navigate to tab “Personalization”
• Click on Import/Export
• Search the desired personalizations for the application name
• Extract the personalilzations
• Login to linux/unix mid-tier and cd to directory specified in profile option
• Run following command
• tar –cvf xxPersonalizations.tar ./*

Uploading to another instance

• Set profile option “FND: Personalization Document Root Path”. The value of this profile option will be the directory name on mid-tierEnsure that this directory exists on mid-tier machine of the destination environment
• FTP the xxPersonalizations.tar to the directory specific in above profile option
• Cd to that directory and run command tar –xvf xxPersonalizations.tar
• Login to Functional Administrator Responsibility
• Navigate to tab “Personalization”
• Click on Import/ExportSelect and Import the personalizations that have been imported