Attachment H – Technical Specifications SERIS to Accounts Receivable

Technical Specifications

SERIS To Oracle Accounts Receivable Interface

Document Control

Author / Update Date / Version / Comments
Lax Koonshetty / 19-Oct-10 / 1.0 / Original Document

Reviewers

nAME / pOSITION
Todd Leiby / Applications Director, OIT
Dan Krautheim / Director, DBA
Bob Kurtis / Applications Analyst/Developer, OIT

Contents

Technical Overview

Purpose

Background

Design

Technical Specifications

Business Requirements

Data Mapping Definition

Configuration

Setup

Concurrent Processes

User Procedures

Note:To update the table of contents, put the cursor anywhere in the table and press [F9]. To change the number of levels displayed, select the menu option Insert>Index and Tables, make sure the Table of Contents tab is active, and change the Number of Levels to a new value.

SERIS To Oracle GL Interface.doc1 10/13/2018

Attachment H – Technical Specifications SERIS to Accounts Receivable

Technical Overview

Purpose

The purpose of this process is to load daily transactional data from a feeder system to Oracle Financials Accounts Receivable module.

Background

SERIS maintains transactional data that is being loaded daily into SBT Accounting System for accounting purposes. Due to the growing needs, it was decided to replace SBT Accounting system with Oracle E-Business Suite package. As part of the migration efforts, this process will replace the daily scheduled interface from SERIS to SBT Accounting system.

Design

Technical Specifications

Business Requirements

General
  • Process should be registered as a concurrent request accessible by end users with Receivables Manager responsibility
  • Process should be automated to run on a daily basis
Parameters

Ledger ID

Selection Criteria

All detail journal lines where:

  • Batch ID = NEW
  • ‘GL002’ = COMPLETE
  • Application = RECEIVABLES
  • Document Type = TR, ROE, AM
Validation

Validation will be performed by the standard Oracle AutoInvoice Import program and errors will be corrected using Oracle standard correction form. For too many errors, RA_INTERFACE_LINES_ALL & RA_INTERFACE_DISTRIBUTIONS_ALL table is cleared & the custom process is re-run to extract corrected data

Import Defaults
  • Status = ‘NEW’
Output Report
  • Log file containing all the selected invoices should be displayed as a report with process status, to verify/confirm the processing of data in Oracle EBS
  • Record must be inserted in table ‘ APPS.BATCH_UPLOAD_STS‘ to reflect the processing completion of current batch

Data Mapping Definition

Target Table / Target Field / Source Table / Source Field / Hint
RA_INTERFACE_LINES_ALL / BATCH_SOURCE_NAME / ‘SERIS INVOICES’
RA_INTERFACE_LINES_ALL / LINE_TYPE / ‘LINE’
RA_INTERFACE_LINES_ALL / DESCRIPTION / GL_DPS_TRNS / ‘Process Type’ || ‘Allocation Type’
RA_INTERFACE_LINES_ALL / CURRENCY_CODE / ‘USD’
RA_INTERFACE_LINES_ALL / AMOUNT / GL_DPS_TRNS / TRNS_AMT
RA_INTERFACE_LINES_ALL / OVERRIDE_AUTO_ACCOUNTING_FLAG / ‘Y’
RA_INTERFACE_LINES_ALL / TERM_NAME / ‘IMMEDIATE’
RA_INTERFACE_LINES_ALL / LAST_UPDATE_DATE / SYSDATE
RA_INTERFACE_LINES_ALL / LAST_UPDATED_BY / From Oracle Setup
(select user_id
from fnd_user;)
RA_INTERFACE_LINES_ALL / CREATION_DATE / SYSDATE
RA_INTERFACE_LINES_ALL / CREATED_BY / From Oracle Setup
(select user_id
from fnd_user;)
RA_INTERFACE_LINES_ALL / PRINTING_OPTION / ‘NULL’
RA_INTERFACE_LINES_ALL / TRX_DATE / GL_DPS_TRNS / TRNS_DATE
RA_INTERFACE_LINES_ALL / DOCUMENT_NUMBER / GL_DPS_TRNS / DOC_NUM
RA_INTERFACE_LINES_ALL / ORIG_SYSTEM_BILL_ADDRESS_REF / Operating Unit || ‘DOC_TYPE’ || ‘SITE’
(Ex – DBTR SITE, DBRE SITE, DBRA SITE, BCTR SITE, BCRE SITE, BCRA SITE)
RA_INTERFACE_LINES_ALL / ORIG_SYSTEM_BILL_CUSTOMER_REF / Operating Unit || ‘DOC_TYPE’
(Ex – DBTR, DBRE, DBRA, BCTR, BCRE, BCRA)
RA_INTERFACE_LINES_ALL / ORIG_SYSTEM_SOLD_CUSTOMER_REF / Operating Unit || ‘DOC_TYPE’
(Ex – DBTR, DBRE, DBRA, BCTR, BCRE, BCRA)
RA_INTERFACE_LINES_ALL / RECEIPT_METHOD_NAME / ‘SERS Method’
RA_INTERFACE_LINES_ALL / CONVERSION_RATE / 1
RA_INTERFACE_LINES_ALL / CONVERSION_TYPE / ‘User’
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_CONTEXT / ‘SERS’
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE1 / GL_DPS_TRNS / DOC_NUM
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE2 / GL_DPS_TRNS / Member ID
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE3 / GL_DPS_TRNS / Invoice ID
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE4 / GL_DPS_TRNS / Agency ID
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE5 / GL_DPS_TRNS / Process Type
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE6 / GL_DP_TRNS / Allocation Type
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE7 / GL_DPS_TRNS / SSN
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE8 / GL_DP_TRNS / Reporting Period
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE9 / GL_DPS_TRNS / Reporting Year
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE10 / GL_DP_TRNS / Arrears Type
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE11 / GL_DPS_TRNS / Revenue Code
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE12 / From Custom sequence to avoid duplicate lines
RA_INTERFACE_LINES_ALL / HEADER_ATTRIBUTE_CATEGORY / ‘SERS’
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE13 / GL_DPS_TRNS / TOTAL_DIRECT
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE14 / GL_DPS_TRNS / TOTAL_PRENOTES
RA_INTERFACE_LINES_ALL / INTERFACE_LINE_ATTRIBUTE15 / GL_DP_TRNS / TOTAL_CHECKS
RA_INTERFACE_LINES_ALL / ORG_ID / From package MO_GLOBAL.GET_CURRENT_ORG_ID
Target Table / Target Field / Source Table / Source Field / Hint
RA_INTERFACE_DISTRIBUTIONS_ALL / ACCOUNT_CLASS / Debit=’REC’
Credit=‘REV’
RA_INTERFACE_DISTRIBUTIONS_ALL / AMOUNT / GL_DPS_TRNS / TRNS_AMT
RA_INTERFACE_DISTRIBUTIONS_ALL / PERCENT / If ACCOUNT_CLASS = ‘REC’, then 100
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_CONTEXT / ‘SERS’
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE1 / GL_DPS_TRNS / DOC_NUM
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE2 / GL_DPS_TRNS / Member ID
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE3 / GL_DPS_TRNS / Invoice ID
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE4 / GL_DPS_TRNS / Agency ID
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE5 / GL_DPS_TRNS / Process Type
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE6 / GL_DP_TRNS / Allocation Type
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE7 / GL_DPS_TRNS / SSN
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE8 / GL_DP_TRNS / Reporting Period
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE9 / GL_DPS_TRNS / Reporting Year
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE10 / GL_DP_TRNS / Arrears Type
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE11 / GL_DPS_TRNS / Revenue Code
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE12 / From Custom sequence to avoid duplicate lines
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE13 / GL_DPS_TRNS / TOTAL_DIRECT
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE14 / GL_DPS_TRNS / TOTAL_PRENOTES
RA_INTERFACE_DISTRIBUTIONS_ALL / INTERFACE_LINE_ATTRIBUTE15 / GL_DP_TRNS / TOTAL_CHECKS
RA_INTERFACE_DISTRIBUTIONS_ALL / SEGMENT1 / Fund
RA_INTERFACE_DISTRIBUTIONS_ALL / SEGMENT2 / Account
RA_INTERFACE_DISTRIBUTIONS_ALL / LAST_UPDATE_DATE / SYSDATE
RA_INTERFACE_DISTRIBUTIONS_ALL / LAST_UPDATED_BY / From Oracle Setup
(select user_id
from fnd_user;)
RA_INTERFACE_DISTRIBUTIONS_ALL / CREATION_DATE / SYSDATE
RA_INTERFACE_DISTRIBUTIONS_ALL / CREATED_BY / From Oracle Setup
(select user_id
from fnd_user;)
RA_INTERFACE_LINES_ALL / ORG_ID / From package MO_GLOBAL.GET_CURRENT_ORG_ID

Configuration

Setup

All required setups are completed while setting up Receivables Modules. For more information, please refer ‘Receivables Setup’ document.

Concurrent Processes

Following concurrent program and request sets will be defined

  • Define concurrent program as specified –

SERIS To OracleAR Interface

Parameters – Ledger ID = GL_SET_OF_BKS_ID

Executable Type – PL/SQL

  • Define Request Set as specified –

SERIS To OracleAR Import

Programs –SERIS To OracleAR Interface

AutoInvoice

User Procedures

Steps to run the process are as below

  • Login to Receivables Manager responsibility
  • Navigate to Other>Report>Run
  • Select ‘SERIS To Oracle AR Interface’ from the list of values (‘SERIS To Oracle AR Import’ to run both the custom process & ‘AutoInvoice’) & submit
  • After successful completion, select ‘AutoInvoice’ program from the list of values & submit (Ignore this step if you ran the request set)
  • Verify the log files of two spawned concurrent requests
  • Data Errors, if any, must be corrected using SQL
  • Re-submit ‘AutoInvoice’ program to load the corrected data elements, if any

SERIS To Oracle GL Interface.doc1 10/13/2018