Category Archives: 9i - Page 3

Setting Up Cash Management Open Interface

In SQL*Plus, execute script ce_999_int.sql as user apps to create the interface table.
Ensure that the view was not changed before running script.
After running script, ensure that there are no invalid objects for owner CE.

#ce_999_int.sql
 create table ce.CE_999_INTERFACE
 (
 TRX_ID Number(15) unique,
 BANK_ACCOUNT_ID NUMBER(15) not null,
 TRX_TYPE VARCHAR2(20) not null,
 TRX_TYPE_DSP VARCHAR2(80) not null,
 TRX_DATE DATE not null,
 CURRENCY_CODE VARCHAR2(15) not null,
 STATUS VARCHAR2(15) not null,
 STATUS_DSP VARCHAR2(80) not null,
 AMOUNT NUMBER(17,2) not null,
 GL_DATE DATE not null,
 CREATION_DATE DATE not null,
 CREATED_BY NUMBER(15) not null,
 LAST_UPDATE_DATE DATE not null,
 LAST_UPDATED_BY NUMBER(15) not null,
 TRX_NUMBER VARCHAR2(15),
 EXCHANGE_RATE_TYPE VARCHAR2(30),
 EXCHANGE_RATE_DATE DATE,
 EXCHANGE_RATE NUMBER(17,2),
 CLEARED_AMOUNT NUMBER(17,2),
 CHARGES_AMOUNT NUMBER(17,2),
 ERROR_AMOUNT NUMBER(17,2),
 ACCTD_AMOUNT NUMBER(17,2),
 ACCTD_CLEARED_AMOUNT NUMBER(17,2),
 ACCTD_CHARGES_AMOUNT NUMBER(17,2),
 ACCTD_ERROR_AMOUNT NUMBER(17,2),
 CLEARED_DATE DATE
 )
 /
create or replace view apps.CE_999_INTERFACE_V
 as select
 ROWID ROW_ID,
 TRX_ID,
 BANK_ACCOUNT_ID,
 TRX_TYPE,
 TRX_TYPE_DSP,
 TRX_NUMBER,
 TRX_DATE,
 CURRENCY_CODE,
 STATUS,
 STATUS_DSP,
 AMOUNT,
 GL_DATE,
 CREATION_DATE,
 CREATED_BY,
 LAST_UPDATE_DATE,
 LAST_UPDATED_BY,
 EXCHANGE_RATE_TYPE,
 EXCHANGE_RATE_DATE,
 EXCHANGE_RATE,
 CLEARED_AMOUNT,
 CHARGES_AMOUNT,
 ERROR_AMOUNT,
 ACCTD_AMOUNT,
 ACCTD_CLEARED_AMOUNT,
 ACCTD_CHARGES_AMOUNT,
 ACCTD_ERROR_AMOUNT,
 CLEARED_DATE
 from CE.CE_999_INTERFACE
 /
 accept database char prompt 'Enter database to connect to: '
 connect apps/apps@&database
 rem compile all invalid database objects
 alter view CE_999_RECONCILED_V compile
 /
 alter view CE_999_TRANSACTIONS_V compile
 /
 alter view CE_AVAILABLE_TRANSACTIONS_V compile
 /
 alter view CE_RECONCILED_TRANSACTIONS_V compile
 /
 alter package CE_AUTO_BANK_CLEAR compile body
 /
 alter package CE_AUTO_BANK_MATCH compile body
 /

2. Start Enterprise Manager
On the database server, change directory to db/9.2.0
Source the database environment
run $oemapp dbastudio

Select the APPS schema
Select the CE_999_PKG body
Enable the clear and unclear procedures
Compile procedure bodies