Problem:
After running a data pump import, the following is displayed in the “Data Pump Batch Exceptions Report”:
The business group specified is invalid Cause: You have specified an incorrect business group identifier or name. Action: Correct the business group value or call your local support representative.
Possible Solution:
Ensure that the business group in the data pump file matches the system profiles:
Profile > System > HR:Business Group Profile > System > HR: Security Profile
Problem:
When loading data via SQL*Loader into a table on an 11g database, the following message appears:
SQL*Loader-404: Column CHAR present more than once in APPS.TMP_HRDPV_CREATE_IN_CWK's INTO TABLE block.
Solution:
In the SQL*Loader control file, the column names had comma after them. There should be whitespace, and then CHAR followed by comma.
Here are some sample SQL*Loader scripts for the ce_statement_headers_int_all and ce_statement_lines_interface tables:
LOAD DATA REPLACE INTO TABLE ce.ce_statement_headers_int_all FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( statement_number CHAR, bank_account_num CHAR, statement_date CHAR, bank_name CONSTANT 'Bank Name', bank_branch_name CONSTANT '', creation_date SYSDATE, last_update_date SYSDATE, control_begin_balance DECIMAL EXTERNAL, control_total_dr DECIMAL EXTERNAL, control_total_cr DECIMAL EXTERNAL, control_end_balance DECIMAL EXTERNAL, control_dr_line_count INTEGER EXTERNAL, control_cr_line_count INTEGER EXTERNAL, control_line_count INTEGER EXTERNAL, currency_code CONSTANT 'TTD', int_calc_balance DECIMAL EXTERNAL ) LOAD DATA REPLACE INTO TABLE ce.ce_statement_lines_interface FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( bank_account_num CHAR, trx_date DATE, attribute1 CHAR, trx_code CHAR, bank_trx_number CHAR, amount DECIMAL EXTERNAL, attribute2 CHAR, trx_text CHAR, statement_number CHAR, line_number INTEGER EXTERNAL, original_amount DECIMAL EXTERNAL, currency_code CHAR, creation_date SYSDATE, last_update_date SYSDATE, charges_amount CONSTANT 0.00 )
Formatting Data File
There are eleven columns in the data file, in the following order:
The Excel worksheet must be formatted before exporting to Comma-Separated Values (CSV). There must be NO commas in any cell before exporting the data to CSV. The SQL*Plus script parses the data by commas, so any of the fields with commas will cause the program to fail
Remember to format the Amount Applied column without commas.
Preparing Data File
The file is usually saved with a .csv extension. It is renamed to remove spaces from the filename (8-character file name and 3-character extension). The data file can be named such that the data it contains can be identified from the name. For example, creatcs1.dat.
The first row of the data file contains the column headings of the Excel worksheet. Remember to delete the first row in the data file before uploading the file to the server.
Uploading Data File
Once prepared, the data file is uploaded to the server via FTP. From the Command Prompt, use ftp to log on to the server (as oraprod user) and change to the directory to upload the data file to:
| Server Name | Upload Directory |
| test | /raid01/oraclone/custload |
| ora | /raid01/oraprod/custload |
After uploading the data file, log on to the server using Telnet or PuTTY. Navigate to the upload directory (cd /raid01/oraclone). Copy and paste the data file to ‘creatcsh.dat’. To obtain the number of rows in the file, execute the command:
$ cat creatcsh.dat | wc
Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. The scripts automatically sets the ORG ID and serveroutput settings in SQL*Plus, so it is not necessary to set it manually.
Since data from a text file is accessed from the SQL procedure, we must tell the instance the directory to search for the data file. To set this up, execute the ‘CREATE DIRECTORY’ SQL command and specify the directory on the server that the data file is located in:
SQL> CREATE DIRECTORY CLONEDATA3 as ‘/raid01/oraclone’;
To verify that the directory has been set up, query the DBA_DIRECTORIES view:
SQL> select * from DBA_DIRECTORIES;
Running the Create Cash API script
The SQL code for loading the data is in the creatcsh.sql file, located in C:SCRIPTS. The code can be run by either copying/pasting the SQL code, or by running the script:
SQL> @ C:Scriptscreatcsh.sql
Note that the changes are not committed, you must manually commit the changes once you are satisfied:
SQL> commit;
Obtaining Data File
User passes the Microsoft Excel spreadsheet for the agent-customer relationships from analyst. Analyst extracts the customer number and agent number onto a new worksheet in Excel and saves the worksheet as type “Formatted Text (Space delimited)”. A sample of the data file is shown below:
01AAS00101PGCMM 999999
01ARE00101PGCMM 999999
01BLI00101PGCMM 999999
01BMA00101PGCMM 999999
01CPA00101PGCMM 999999
01CTR00101PGCMM 999999
01DEQ00101PGCMM 999999
The customer number is usually about 15 characters wide, right padded with spaces to 22 characters. The agent number is 6 characters wide.
This data file is passed to the DBA for loading.
Preparing Data File
The exported file is saved with a .prn extension by default. Rename the file (in Command Prompt) to an 8.3 format (8-character file name and 3-character extension). The data file can be named such that the data it contains can be identified from the name. For example:
| Type of Listing | File name |
| Provisor Customer Relationships | provcust.dat |
| Quantum Customer Relationships | quancust.dat |
The first row of the data file contains the column headings of the Excel worksheet. This header row must be deleted, so that there is only data in the file.
Uploading Data File
Once prepared, the data file is uploaded to the server via FTP. From the Command Prompt, use ftp to log on to the server (as oraprod user) and change to the directory to upload the data file to:
| Server Name | Upload Directory |
| testdata | /raid01/oraclone/custload |
| oradata | /raid01/oraprod/custload |
After uploading the data file, log on to the server using Telnet or PuTTY. Navigate to the upload directory (cd /raid01/oraclone). Rename (copy and paste) the data file to ‘datafile.dat’. To obtain the number of rows in the file, execute the command (shown in bold):
$ cat datafile.dat | wc
This command returns three figures. The first figure is the number of lines in the file. Verify that the data file has the correct number of rows to upload.
Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. Set the ORG ID and serveroutput settings in SQL*Plus before running the SQL procedure:
SQL> exec dbms_application_info.set_client_info(’21’);
SQL> set serveroutput on
Recall that ‘21’ refers to the ID of the Trinidad Operating Unit. Since the customer data is tied to the ORG ID, you must ensure that the correct ORG ID is set before running the code.
Since data from the text file is being accessed from the SQL procedure, the instance must have access to the file system. We must tell the Oracle instance the directory to search for the data file. To set this up, execute the ‘CREATE DIRECTORY’ SQL command and specify the directory on the server that the data file is located in:
SQL> CREATE DIRECTORY CLONEDATA3 as ‘/raid01/oraclone’;
To verify that the directory has been set up, query the DBA_DIRECTORIES view:
SQL> select * from DBA_DIRECTORIES;
Note that you only need to run the ‘CREATE DIRECTORY’ command once.
Running the TCA API
The SQL code for loading the data is in the tcaapi.sql file, located in C:SCRIPTS. The code can be run by either copying/pasting the SQL code, or by running the script:
SQL> @ C:Scriptstcaapi.sql
Note that the changes are not committed, you must manually commit the changes once you are satisfied the script completed without errors:
SQL> commit;
Errors Encountered
Problem: The API code is run with several dozen records in the data file and the following error is thrown:
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 85
Solution: This error is caused by the put_line statements. To overcome this, disable printing output from the procedure (set the value of c_print_data to ‘N’), or remove all put_line statements. Alternatively, set serveroutput to a large buffer:
SQL> set serveroutput on size 1000000
Problem: The code runs and completes successfully, but does not read or populate any records.
Solution: The record header from Excel still exists in the data file. Remove the headers and rerun.
This documentation covers how to create a custom application, program and executable in Oracle Applications. The custom application will load data from feeder systems into Oracle Application’s interface tables.
A custom application needs to be set up only once. The custom program executable will then need to be set up and the program defined. Finally, the program must be added to a request group, so that it appears in a user’s responsibility.
Step 1: Create a new application: Log on as the SYSADMIN user. Select the System Administrator responsibility, then navigate to Application, and then Register.
Step 2: Click the New button to insert a new row.
Insert a new application, Maryland Financial Group Limited. The Short Name chosen is MFGL and Basepath is MFGL_TOP. Save the record.
Step 3: Create a directory d:PRODprodapplmfgl on oracleserver. Under mfgl, create a directory ‘11.5.0. Under this directory, create the directories: bin, data, log, out, reports, and sql.
The d:PRODprodapplmfgl11.5.0 directory will be set to the environment variable MFGL_TOP.
Step 4: Under Windows, Oracle Applications stores the application top folders in the registry. Update the registry on oracleserver to add the MFGL_TOP base path:
[HKEY_LOCAL_MACHINESOFTWAREORACLEAPPLICATIONS11.5.0 PROD_oracleserver]
MFGL_TOP=”d:PRODprodapplmfgl11.5.0″
Step 5: To add MFGL_TOP as an environment variable, create customPROD_oracleserver.cmd in the d:PRODprodappl directory and put the following path into this file.
set MFGL_TOP=d:PRODprodapplmfgl11.5.0
Step 6: Navigate to Security, then ORACLE and finally DataGroup.
Insert a new record. Select the Maryland Financial Group Limited application, add APPS as Oracle ID and enter an optional description. Click Save.
Note that steps 1 to 6 only need to be done once. Since these steps have already been done, there is no need to go do them again.
Step 7: The data will be loaded via SQL*Loader. Create the control file GLMLAS400.ctl file and place the SQL*Loader command in it. Place the control file in the d:PRODprodapplmfgl11.5.0bin folder.
Step 8: Set up the concurrent program executable: Navigate to Concurrent, then Program and finally Executable.
Insert a new executable program. Ensure that the Execution Method is SQL*Loader, since selecting Host causes Oracle Application to look for an executable (“.EXE”) program rather than a “.CMD” program on Windows. Click Save.
Step 9: Define the concurrent program: Navigate to Concurrent, then Program and finally Define.
Insert a new record and fill in the fields to add the new program. Click Save.
Step 10: Click the Incompatibilities button. Insert a new incompatible program ‘Maryland – Load AS/400 GL Journals’. This will prevent another instance of this program from running if it is running already.
Step 11: The program must now be set up so that it can be submitted from Standard Request Submission. To submit the program from the General Ledger Manager, the request group for the responsibility must be found. Navigate to Security, then Responsibility, and finally Define.
Search for the ‘General Ledger Manager’ responsibility. Note the name of the Request Group.
Step 12: Add the new program to the Request Group. To do so, navigate to Security, then Responsibility and finally Request.
Search for the group noted in the previous step (in this case, ‘GL Concurrent Program Group’). Insert a new record for ‘Maryland – Load AS/400 GL Journals’.
Step 13: Before running the concurrent request, place the data file in the d:PRODprodapplmfgl11.5.0data directory, under the name GLMLAS400GL.dat. If this file already exists in the directory, it can be overwritten.
Step 14: Choose the General Ledger Manager responsibility. Submit a new request, ‘Maryland – Load AS/400 GL Journals’.
No parameters need to be entered for this request. The request will automatically search for the data file and load it if present. If not, the request completes in error and the log will have to be checked to determine the cause of the error.
For additional information on setting up a custom application, refer to Metalink Doc ID: Note:105127.1, Subject: FAQ (Customization).
We had some problems with loading the data because one of the fields, address_line3 was omitted from the data file. Verify the data file against the control file.
You must log on to the correct responsibility in order to import the data.
| Company | Org_id |
| Company A | 123 |
| Company B | 141 |
| Company C | 142 |
After the data is loaded, two concurrent programs must be run to import the data:
Data is loaded into:
Data is imported into:
Change VENDOR_SITE_CODE to ‘PRIMARY’.
For manual entry of vendor numbers, change the settings in the application and populate the SEGMENT1 field.
Valid TERMS_DATE: ‘Net 30 Days’
VENDOR_INTERFACE_ID must be in both tables. Insert it into the data file if necessary.
This documentation shows the user how to load data for GL Interface.
Step 1: The data file provided by the analyst can either be a .CSV file or a .DAT file.
Step 2: Rename the data file to a name without spaces e.g. datafilename.dat.
Step 3: Copy the data file to the project folder, into folder GLDataLoadElvis.
Step 4: Load on to oracleserver (128.1.2.213) via VNC.
Step 5: Load Command Prompt.
Step 6: Change to D:PRODprodappl. Run APPSORA.cmd.
Step 7: Change to folder D:PROJECT FOLDERGLDataLoadElvis
Step 8: At the Command Prompt, run SQL Loader:
sqlldr80 apps/apps control=elvis.ctl data=datafilename.dat
Step 9: If only about 50-60 lines load, the data was incorrect (the load process fails after 50 lines have failed). Check the log file elvis.log for more information.
Before uploading bank statement file (SQL Loader data file), ensure that the data file exactly maps to the lines.ctl and the headers.ctl.
Sample control files (Files may need to altered to best match statement) # HEADER.CTL LOAD DATA APPEND INTO TABLE ce_statement_headers_int_all FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( BANK_ACCOUNT_NUM CONSTANT 'XXXXXXXXXXXX', #ADD BANK ACCT NUMBER ORG_ID CONSTANT '23', #DETERMINE ORG ID STATEMENT_NUMBER, STATEMENT_DATE "TO_DATE(:STATEMENT_DATE,'YYYYMMDD')", DUMMY_COLUMN FILLER, DUMMY_COLUMN2 FILLER, DUMMY_COLUMN3 FILLER, DUMMY_COLUMN4 FILLER, DUMMY_COLUMN5 FILLER, DUMMY_COLUMN6 FILLER, DUMMY_COLUMN7 FILLER, DUMMY_COLUMN8 FILLER, DUMMY_COLUMN9 FILLER, DUMMY_COLUMN10 FILLER, CURRENCY_CODE CONSTANT 'TTD' ) # LINES.CTL load data append into table ce_statement_lines_interface FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( STATEMENT_NUMBER, TRX_DATE "TO_DATE(:TRX_DATE, 'YYYYMMDD')", BANK_ACCOUNT_NUM CONSTANT 'XXXXXXXXXXXX', #ADD BANK ACCT NUMBER LINE_NUMBER SEQUENCE(COUNT), currency_code constant 'TTD', dummy_filler2 filler, dummy_filler3 filler, dummy_filler4 filler, dummy_filler1 filler, TRX_CODE, TRX_TEXT, AMOUNT, BANK_TRX_NUMBER, dummy_filler5 filler, dummy_filler6 filler )
Save the data file as a .csv file.
Note:
To determine org_id, issue the following command in SQL*Plus:
Select ORGANIZATION_ID, NAME From HR_ALL_ORGANIZATION_UNITS;
Recent Comments