Tag Archives: Loading Data

CE Sample SQL*Loader Scripts

Posted by on June 5, 2008 No comments

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
)

Creating Cash API For AR

Posted by on November 13, 2007 No comments

Formatting Data File

There are eleven columns in the data file, in the following order:

  1. Receipt number
  2. Currency
  3. Customer number
  4. Payment method id
  5. Receipt Type
  6. Amount applied
  7. Apply date
  8. Apply GL date
  9. Comments
  10. Customer reference

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;

Running Customer Relationships (TCA) API

Posted by on October 12, 2007 No comments

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.

Importing Supplier Data

Posted by on October 3, 2006 No comments

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:

  1. Supplier Open Interface Import
  2. Supplier Sites Open Interface Import

Data is loaded into:

  1. AP_SUPPLIERS_INT
  2. AP_SUPPLIER_SITES_INT

Data is imported into:

  1. PO.PO_VENDORS
  2. PO.PO_VENDOR_SITES_ALL

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.

Loading Data Into GL Interface

Posted by on August 10, 2006 No comments

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.