Monthly Archives: October 2007

ORA-01403 Error on FF_DATABASE_ITEMS When Running Quickpay

Ran QuickPay for user Joe; failed with error ORA-01403
Error was encountered when processing assignment USER-416
Error ORA-01403: no data found
has occurred in table ff_database_items at location 552
Cause:        an oracle error has occurred.  The failure was reported on table ff_database_items at location 552 with the error text ORA-01403: no dat

Checked Metalink, got article:

Subject:  ORA-01403 Error on FF_DATABASE_ITEMS When Running Quickpay
Doc ID:  Note:330791.1

Chose responsibility UK USD – HRMS Manager
Ran Bulk Compile Formula

Completed in error

See Metalink document:

Subject:  Payroll Failing with Ora-20001 Ora-06512: At “Apps.Pay_balance_pkg ORA-01006
Doc ID:  Note:428219.1

$FF_TOP/bin/FFXBCP apps/<apps password> 0 Y -U %% %%

APP-FF-34004: Oracle Payroll:HOUSE_RENT:1950/01/01 FAILED
APP-FF-33005: The local variable NET_PAY_ASG_RUN was used before being initialized

Cause:        The variable named in the error message is being used before any value has been assigned to it, so it has no meaningful value.

Action:        Please ensure variables have been assigned to before using them.

Subject:  PTO Formula Fails to Compile with Error APP-FF-33005
Doc ID:  Note:336157.1

Rerun HRGLOBAL with the global option (run DataInstall, choose 1I)

Bulk Compile Formula Command Line:

$FF_TOP/bin/FFXBCP apps/<apps password> 0 Y -U %% %%

Reduced number of formula instances to process from 159 to 47.

Ran QuickPay, completed successfully!!!

Ran Element Creation, completed successfully!!!

Ran Active Users report (Concurrent Manager), completed successfully!!!


DataInstall set to Global

HRGLOBAL run, error with hrrbdeib.sql

DataInstall set to Global, UK HRMS, UK Payroll

HRGLOBAL run, error with hrrbdeib.sql

$FF_TOP/bin/FFXBCP apps/<apps password> 0 Y -U %% %%

HRGLOBAL run, error with hrrbdeib.sql

Running Customer Relationships (TCA) API

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.