Guardian News & Media
GNM RCS
Oracle AP interfaces
Technical specification
Prepared by O3 Team Limited
Authors Nigel Robson
Creation date 02/12/2013
Document Ref. GNM_RCS_AP_Interfaces_TS.docx
Version draft for review
Introduction
Purpose
The document GNM_RCS_System_Interfaces_FS.docx is the functional specification that describes what business functions RCS supports in relation to its interfaces with other GNM systems as well as any external integration.
This document is one of a set of technical specifications that provide details of how those functions are implemented in RCS.
Scope
This document focusses on the Oracle AP interfaces: the data being transferred and how the transactions are managed. Separate documents deal with all the other inter-system interfaces that pass data between RCS/SLM and other systems.
This document is intended as a high-level technical document outlining how the relevant business functions are implemented in terms of software modules.
Importantly, this document does not aim to provide the level of detail that would be required in a programming specification in areas such as program structure, detailed business rules, data integrity, validation, locking considerations, data security, and calls to/from other software modules, performance considerations, and so forth.
For details of program logic and coding, the reader should refer to the program files themselves.
Oracle AP interface
Background
RCS originally sent supplier details and payment instructions to GNM’s (now decommissioned) AS400 finance system in Manchester by ftp.
When GNM replaced the old finance system with Oracle EBS the flat file transfers were simply redirected to the new system.
At a later point the flat file interfaces were replaced with message transfers over database links. This made the interfaces more real-time and robust. Many more messaging processes have subsequently been added.
Data sent to Oracle AP
Supplier interface
New and changed supplier details are sent to Oracle AP by means of an API call over a database link. One record is sent per supplier. For each message sent a record is retained in the SUPPLIER_MESSAGES table. These messages can be reviewed in the Supplier interface messages screen which is accessed from the menu option: Housekeeping → Interfaces → Supplier messages sent.
This opens an Oracle Form named rcs_sime_010_pc.fmb
The packaged function (API) that is executed in Oracle AP is called guard_supplier_interface.supplier_change_api
RCS makes the call and if the function returns TRUE RCS commits the changes, otherwise it rolls them back.
A flag column on the CONTENT_PROVIDERS table called NEEDS_SENDING_TP_ACCOUNT_YN is maintained so that the RCS database jobs knows which records need to be sent.
RCS owns the master of this data. This means it should not be changed in Oracle. Instead changes must be made in RCS and they will be propagated to Oracle automatically.
Bank accounts required interface
RCS advises Oracle AP which suppliers still need bank details in order for payment instructions to be released to Oracle AP. In this interface RCS passes an array of data listing the contributors for whom payments need to be made but where Oracle does not have the payee’s bank details.
The most recent list of Contributors needing bank details that was sent to Oracle is available in the Bank details requested screen.
This screen is accessible from the menu as follows: Housekeeping → Interfaces → Bank details requested.
This opens an Oracle Form named rcs_sbdr_010_pc.fmb
The packaged procedure (API) that is executed in Oracle is called guard_supplier_interface.bank_details_request
RCS makes the call and if the function returns TRUE RCS commits the changes, otherwise it rolls them back.
A flag column on the CONTENT_PROVIDERS table called BANK_DETAILS_COMPLETE_YN identifies whether a supplier has provided their bank details or not. Only suppliers with pending payments are sent to Oracle with this interface.
(An entirely separate interface, documented below, enables Oracle to notify RCS when bank details have been gathered for a supplier, at which point this flag gets reset and the payment instruction is released from RCS.)
.
Invoice interface
Payment instructions are sent to Oracle AP for Suppliers who have been setup in AP. These messages are recorded in RCS and can be reviewed in the Invoice interface screen. This screen is accessible from the menu option Housekeeping → Interfaces → Invoice messages sent.
This opens an Oracle Form named rcs_iime_010_pc.fmb
The packaged function (API) that is executed in Oracle is called guard_invoice_interface.create_invoice_api
RCS makes the call and if the function returns TRUE RCS commits the changes, otherwise it rolls them back.
Cancellation instructions
When an error is identified or a payment needs to be cancelled an instruction is sent to AP – either directly to the AP system or in the form of an email to someone in AP. Messages that are sent automatically relates to payment instructions held in the Hold Area in Oracle AP.
Each message sent is recorded in RCS and can be reviewed in the Invoice cancellation messages screen. This screen is accessible from the menu option: Housekeeping → Interfaces → Cancellations sent to AP.
This opens an Oracle Form named rcs_icme_010_pc.fmb
When invoices are cancelled in RCS they are queued for review in the Invoice error queue, aka the Hold queue. This queue allows the RCS administrator to choose whether to resend a payment instruction after correcting it, or to reject it altogether.
The screen displaying this queue is accessible from the menu option: Housekeeping → Interfaces → Invoice error queue.
This opens an Oracle Form named rcs_tihq_010_pc.fmb
.
Data received from Oracle AP
Bank details notifications
Oracle AP notifies RCS when a supplier’s bank details are setup, or removed. Once RCS knows that a contributor’s bank details are held by AP payment instructions can be sent to AP.
The screen displaying these messages is accessible from the menu option: Housekeeping → Interfaces → Invoice error queue.
This opens an Oracle Form named rcs_sbdm_010_pc.fmb
The messages shown in this screen are written by an RCS API (oracle_ap_supplier_change.bank_details_ccomplete) which is called by software in Oracle EBS. The Oracle EBS process controls the commit.
These messages are processed asynchronously in RCS by a database job executing oracle_financials_interface. process_bank_details_changes, which updates the record in the CONTENT_PROVIDERS table as advised.
.
Self-bill notifications
Oracle AP notifies RCS when a supplier’s self-bill status changes.
The screen displaying this queue of messages is accessible from the menu option: Housekeeping → Interfaces → Self-bill notifications from AP.
This opens an Oracle Form named rcs_ssbm_010_pc.fmb
The messages shown in this screen are written by an RCS API (oracle_ap_supplier_change.self_bill) which is called by software in Oracle EBS. The Oracle EBS process controls the commit.
These messages are processed asynchronously in RCS by a database job executing oracle_financials_interface.process_bank_details_changes, which updates the record in the CONTENT_PROVIDERS table as advised.
.
Tax position notifications (Form 1099 for IRS in USA)
Oracle AP notifies RCS once a supplier’s Form 1099 tax position is known. Once RCS knows that AP have discovered the contributor’s tax position it can send payment instructions to Oracle AP.
The screen displaying this queue of messages is accessible from the menu option: Housekeeping → Interfaces → Tax position notifications from AP.
This opens an Oracle Form named rcs_stpm_010_pc.fmb
The messages shown in this screen are written by an RCS API (oracle_ap_supplier_change.tax_position_known) which is called by software in Oracle EBS. The Oracle EBS process controls the commit.
These messages are processed asynchronously in RCS by a database job executing oracle_financials_interface. process_bank_details_changes, which updates the record in the CONTENT_PROVIDERS table as advised.
.
VAT notifications
Oracle AP notifies RCS whenever a supplier’s VAT position changes.
The screen displaying this queue of messages is accessible from the menu option: Housekeeping → Interfaces → VAT position notifications from AP.
This opens an Oracle Form named rcs_svme_010_pc.fmb
The messages shown in this screen are written by an RCS API (oracle_ap_supplier_change.vat) which is called by software in Oracle EBS. The Oracle EBS process controls the commit.
These messages are processed asynchronously in RCS by a database job executing oracle_financials_interface.process_vat_changes, which updates the record in the CONTENT_PROVIDERS table as advised.
.
Invoice status notifications (Invoice paid; Hold area status; Cancel/reject)
Oracle AP sends change of status notifications to RCS relating to payment instructions received from RCS. These include the amount paid, currency, and date.
The screen displaying this queue of messages is accessible from the menu option: Housekeeping → Interfaces → Invoice status notifications from AP.
This opens an Oracle Form named rcs_iawm_010_pc.fmb
The messages shown in this screen are written by a few RCS API modules:
oracle_ap_invoice_change.via_hold_area
oracle_ap_invoice_change.hold_area_status
oracle_ap_invoice_change.cancelled_or_rejected
oracle_ap_invoice_change.invoice_paid
Each of these is called by software in Oracle EBS. The Oracle EBS process controls the commit.
These messages are processed asynchronously in RCS by a database job executing oracle_financials_interface.process_invoice_changes, which updates the record in the SCHEDULED_TRANSACTIONS table as advised.
This status information is very useful for tracking a payment through the process, and to enable Editorial to answer queries from contributors without always having to refer them to AP.
End of Document