TDH INT072- Greatplains HHS GL Extract to Oracle ERP Cloud
1.
Integration Overview
The specification depicts
how the overall business process and data flow is architected for TDH INT072-
Greatplains HHS GL Extract to Oracle ERP Cloud
1.1.
Purpose
This document contains Technical Design and Components required to
implement customization for TDH INT072 -Greatplains HHS GL Extract to Oracle ERP Cloud along with
high level overview and functional requirement about the integration.
Teladoc is implementing Oracle Cloud Financial applications and as
a part of this initiative, integrations from various systems need to be built
to Oracle Cloud ERP. This design document provides details around the overall
business process of the integration and the data flow from Great Plains HHS to
Oracle ERP Cloud.
Source System: Great Plains HHS
Target System: Oracle ERP Cloud
1.1.
Scope
The scope of this document includes:
Technical detailed design for the Integration Greatplains
HHS GL Extract to Oracle ERP Cloud
This document doesn’t include the technical design details for the
Vendor systems/Source Systems.
●
All required Oracle General Ledger configurations should be
completed (including ledgers, calendars, legal entities, etc.).
●
Journal Source – Great Plains is defined in Oracle
●
Journal Category – Great Plains is defined in Oracle
●
The Accounting Period in which the file is interfaced is Open in
Oracle GL.
●
The COA values are defined and enabled in Oracle
●
The extract file provides only balanced journal entries
●
The extract file contains date values in “YYYY/MM/DD” format
●
The extract file contains valid value for “Segment1”
●
Ledger Name and Ledger Code is available in the “TDH_GL_Greatplains_LEDGER_LOOKUP”
●
Source COA to Oracle COA mapping is available in the “TDH_GL_GreatplainsHHS_COA_LOOKUP”
●
We will map values of Segments (Segment 1 to Segment 9)
from lookup based on the value provided in “Segment1” column in Source
File
●
Conversion Type and Effective Date of Transaction columns will be
mapped to Currency Conversion Type and Currency Conversion Date column of FBDI
to handle Foreign Currency. Hence, we
will not be mapping Converted Debit Amount and Converted Credit Amount columns
from Source to FBDI
●
The COA is updated in Greatplains for the employees
●
We will get separate files for each Ledger each Ledger having file
name pattern as TDH_GP_<Ledger Code>*.csv
●
SFTP folder structures are defined in the Teladoc SFTP server
1.
Technical Approach
The following setups needs to be complete for the integration
●
Ledgers, Calendars, Chart of Accounts, Legal Entities
●
Journal Category and Journal Source
●
The Accounting Period is open
●
The COA values are defined and enabled in Oracle
●
Lookup (TDH_FIN_COMMON_LOOKUP) needs
to be maintained in OIC to hold all the global variable like SFTP folder names,
Oracle User Source Name (“Great Plains”), job properties, error notification details
against interface code “GPHHS_Journal “
●
COA Lookup (TDH_GL_GreatplainsHHS_COA_LOOKUP)
needs to be maintained in OIC to hold old COA to new COA mappings.
●
Lookup (TDH_GL_Greatplains_LEDGER_LOOKUP)
needs to be maintained in OIC to hold Ledger Name and Ledger Code
The Integration user requires the following roles
Job roles
●
General Ledger Accountant
● Implementation Manager
Data Roles
●
General Ledger Accountant --- Ledger --- US USD USGAAP Ledger
●
General Ledger Accountant --- Ledger --- CA CAD USGAAP Ledger
●
General Ledger Accountant --- Ledger --- Consolidated USD Ledger
1.1.
Process Flow Diagram
The GL Journal Import to Oracle ERP Cloud is divided into two
Integration.
1.
TDH_GL_GreatplainsHHS_JournalImport_IN
(1.0)
2.
TDH_GL_JournalImport_Callback_IN
(1.0)
●
TDH_GL_GreatplainsHHS_JournalImport_IN (1.0) integration will read
the file from SFTP server and transform the data into Oracle GL Journal Import
FBDI format and then submit the Bulk Import Process.
●
TDH_GL_JournalImport_Callback_IN (1.0) integration will get
triggered once the “Import Journals”
ESS Job is completed in Cloud.
Integration: Flow Diagram
●
Greatplains HHS team needs to perform data extraction, generate
source file in predefined csv format and place it in the SFTP Location “/Inbound/GreatPlains/GreatPlainsJournals/In”.
●
Once the file is placed in the above SFTP directory, the steps
below will be performed by OIC Integration (as per schedule).
TDH_GL_GreatplainsHHS_JournalImport_IN
(1.0)
1.
Assignment of values to Global Variables by deriving values from
OIC lookup 'TDH_FIN_COMMON_LOOKUP'
based on Interface Code “GPHHS_Journal”.
2.
Fetch Ledger List from Lookup “TDH_GL_Greatplains_LEDGER_LOOKUP”
using Rest Adapter “TDH_OIC_REST”.
3.
For each Ledger, list all the source files
from SFTP directory “/Inbound/GreatPlains/GreatPlainsJournals/In” using
SFTP Adapter “TDH_OIC_SFTP” with file name pattern as “TDH_GP_<LedgerCode(US/CA)>*.csv”.
4. If no files are found, Common Error Handler Integration will be invoked.
5.
If files are found, for each listed file,
download the file using SFTP Adapter “TDH_OIC_SFTP” to the OIC staging
area.
6.
Read file data in segments and map the SourceCOA value to OracleCOA value using the lookup “TDH_GL_GreatplainsHHS_COA_LOOKUP” and
accordingly create Oracle GL Journal Import FBDI File in OIC Staging area as
per the provided mapping.
7.
Using Oracle ERP Cloud Adapter “TDH_OIC_FIN_CLOUD”
Rest services, derive values for all the required parameters of “Import
Journals” ESS Job. (Refer Parameter section for details).
8.
Create properties file containing parameter
values derived in previous step.
9.
Zip the GL Journal Import FBDI file and
properties file using “Stage Activity”.
10. Write the Zip File to SFTP directory “/Inbound/GreatPlains/GreatPlainsJournals/Archive” using
SFTP Adapter “TDH_OIC_SFTP” for future reference
11. Using Oracle ERP Cloud Adapter “TDH_OIC_FIN_CLOUD” trigger “Bulk
Import” (This operation will upload the zip to UCM, submit “Load
Interface file for Import” and trigger “Import Journals” ESS Job).
12. Wait till the “Load Interface File for Import” ESS Job is
completed. If the process is completed successfully, archive the source file to
SFTP folder “/Inbound/GreatPlains/GreatPlainsJournals/Archive”, else
archive the source file to SFTP Folder “/Inbound/GreatPlains/GreatPlainsJournals/Error”.
Note: Any error while
performing above steps will be handled using Common Utility Error Framework
TDH_GL_ JournalImport_Callback_IN:
1. This
integration will be triggered by Oracle Cloud if “Load Interface File for
Import” ends in error status or on completion (success/error) of “Import
Journals” ESS Job
2.
Regardless of status being Success/Error, send notification to
Common Error Handling Framework
Parameters required to submit Import Journal ESS Job (Values for
these will be included in the generated properties file)
|
Parameter Name |
Parameter Value |
Type |
|
Source |
JeSourceName |
Derived
using Rest API using Source Name mentioned in OIC Lookup |
|
DataAccessSet |
Data
Access Set ID |
Derived
from Cloud based on Ledger Name in Data File |
|
Ledger |
Ledger
ID Value |
Derived
from Cloud based on Ledger Name in Data File |
|
GroupID |
Current
Date and Time Value |
Derived
using OIC Function |
|
PostAccountErrorToSuspense |
N |
Constant
from OIC Lookup |
|
CreateSummaryJournals |
N |
Constant
from OIC Lookup |
|
ImportDescriptiveFlexfields |
N |
Constant
from OIC Lookup |
●
Place data File at SFTP Location
● Resubmit TDH_GL_GreatplainsHHS_JournalImport_IN (1.0) Integration manually from OIC
1.3. Sample Source File Format
|
File
Name |
TDH_GP_<Ledger
Code>*.csv |
|
System
Date and Hours |
YYYYMMDDHHMM |
|
Output
Type |
.csv |
|
Delimiter |
Comma
“,” |
Sample source file name:
TDH_GP_<LedgerCountry(US/CA)>202202021200.csv
2.
Technical Components
2.1. Oracle Integration Cloud
(OIC) Objects
|
Integration Name |
Integration Identifier |
Package Name |
Integration Type |
Description |
|
TDH_GL_GreatplainsHHS_JournalImport_IN |
TDH_GL_GREATPLAINS_JOURNAL_IN |
teladoc.oracle.fin |
Scheduled Orchestration |
GL Journals File Based Inbound
Integration to Oracle ERP Cloud |
|
TDH_GL_IN_JournalImport_Callback |
TDH_GL_IN_JOURNALIMPORT_CALLBACK |
teladoc.oracle.fin |
App Driven Orchestration |
Call-back for Import Journal ESS Job |
|
Connection Name |
Adapter |
Role |
Description |
|
TDH_OIC_SFTP |
FTP |
Trigger and Invoke |
OIC SFTP connection |
|
TDH_OIC_FIN_CLOUD |
Oracle ERP Cloud |
Trigger and Invoke |
ERP Cloud Host: https://fa-euwl-dev1-saasfaprod1.fa.ocs.oraclecloud.com/ |
|
TDH_OIC_REST |
REST |
Trigger and Invoke |
OIC Host: https://oic-development-idgl1oibf9ie-ia.integration.ocp.oraclecloud.com:443 |
|
TDH_OIC_REST_CLOUD |
REST |
Trigger and Invoke |
ERP Cloud Host: https://fa-euwl-dev1-saasfaprod1.fa.ocs.oraclecloud.com/ |
|
Lookup Name: TDH_FIN_COMMON_LOOKUP |
||
|
Columns Used |
Description |
Value |
|
INTERFACECODE |
|
GPHHS_JOURNAL |
|
FROM_EMAIL |
|
|
|
TO_EMAIL |
|
|
|
SFTP_IN_FOLDER |
|
/Inbound/Greatplains/GreatplainsJournals/In |
|
SFTP_OUT_FOLDER |
|
NA |
|
SFTP_ARCHIVE_FOLDER |
|
/Inbound/Greatplains/GreatplainsJournals/Archive |
|
SFTP_ERROR_FOLDER |
|
/Inbound/Greatplains/GreatplainsJournals/Error |
|
FILE_PATTERN |
|
TDH_GP_*.csv |
|
FILE_EXTENSION |
|
.csv |
|
SOURCE |
|
Great Plains |
|
LEDGER_NAME |
|
TDH_GL_Greatplains_LEDGER_LOOKUP |
|
POST_ACCOUNT_ERROR_TO_SUSPENSE |
|
N |
|
CREATE_SUMMARY_JOURNALS |
|
N |
|
IMPORT_DESCRIPTIVE_FLEXFIELDS |
|
N |
|
FILE_NAME1 |
|
NA |
|
FILE_NAME2 |
|
NA |
|
REPORTPATH |
|
NA |
|
COA_MAPPING_LOOKUP |
|
TDH_GL_GreatplainsHHS_COA_LOOKUP |
|
Environment |
|
Non-Prod |
|
INTEGRATION_NAME |
|
TDH_GL_GreatplainsHHS_JournalImport_IN |
|
BU_LOOKUP_NAME |
|
NA |
|
ASSIGN_TO_GROUP |
|
|
|
DOCUMENT_LINK |
|
https://fa-euwl-dev1-saasfaprod1.fa.ocs.oraclecloud.com/cs/idcplg?IdcService=GET_FILE&dID= |
|
Lookup
Name: TDH_GL_GreatplainsHHS_COA_LOOKUP |
|||
|
Columns
Used |
Description |
Value |
|
|
CombinedSourceCOA |
|
CombinedSegmentValue from Source |
|
|
OracleCOASegment1 |
|
Corresponding Oracle COA Segment 1 |
|
|
OracleCOASegment2 |
|
Corresponding Oracle COA Segment 2 |
|
|
OracleCOASegment3 |
|
Corresponding Oracle COA Segment 3 |
|
|
OracleCOASegment4 |
|
Corresponding Oracle COA Segment 4 |
|
|
OracleCOASegment5 |
|
Corresponding Oracle COA Segment 5 |
|
|
OracleCOASegment6 |
|
Corresponding Oracle COA Segment 6 |
|
|
OracleCOASegment7 |
|
Corresponding Oracle COA Segment 7 |
|
|
OracleCOASegment8 |
|
Corresponding Oracle COA Segment 8 |
|
|
OracleCOASegment9 |
|
Corresponding Oracle COA Segment 9 |
|
|
Lookup Name: TDH_GL_Greatplains_LEDGER_LOOKUP |
||
|
Columns Used |
Description |
Value |
|
LedgerCode |
|
Desired
Code for the Ledger |
|
LedgerName |
|
Ledger
Name |
|
LedgerCode |
LedgerName |
|
US |
US USD USGAAP Ledger |
|
CA |
CA CAD USGAAP Ledger |
|
Consolidated |
Consolidated USD Ledger |
|
FTP Name: |
|
|
Folder
Name |
●
/Inbound/GreatplainsHHS/GreatplainsHHSJournals/In ●
/Inbound/GreatplainsHHS/GreatplainsHHSJournals/Archive ●
/Inbound/GreatplainsHHS/GreatplainsHHSJournals/Error |
2.4.2.
API Details
|
Resource |
Methods |
Notes |
Sample Payload (link) |
|
dataAccessSetsLOV |
Get |
|
https://docs.oracle.com/en/cloud/saas/financials/22a/farfa/op-dataaccesssetslov-get.html |
|
ledgersLOV |
Get |
|
https://docs.oracle.com/en/cloud/saas/financials/22a/farfa/api-list-values-ledgers-list-values.html |
|
journalSourcesLOV |
Get |
|
https://docs.oracle.com/en/cloud/saas/financials/22a/farfa/op-journalsourceslov-get.html |
|
OICLookup |
Get |
|
NA
NA
3.5.2
BI Report Data Model Query
NA
NA
NA
3.5.5
BI Report Bursting Details
NA
3.5.6
BI Report Bursting Query
NA
3.5.7
BI Report Layout Details
NA
NA
3.6.1 ESS Job List of Values Details
NA
NA
3.1. Error Handling and
Reporting
|
Scenario |
Handling Section |
Error Message |
Action |
|
Error while Loading file records in Interface Table |
Global Fault Handler (use Common Error Handling Framework) |
Error while loading data to Interface table |
Consolidated Zip file link will be shared to common Error
handling framework Move the Source File to Error Folder |
|
Error while Importing Journal Entries to Ledger |
Global Fault Handler (use Common Error Handling Framework) |
Error while importing Journal Entries to Ledger |
Consolidated Zip file link will be shared to common Error
handling framework |
|
Data File not found at SFTP Location |
Common Error Handling Framework |
File not found for Ledger - <Ledger Name> |
Details will be shared to Common Error Handling Framework |
●
Place data File at SFTP Location
●
Resubmit TDH_GL_GreatplainsHHS_JournalImport_IN (1.0) Integration manually from OIC
|
Scenario |
Details |
|
Data File not found at SFTP |
If the data file is not
found at SFTP Location, invoke Common Error Handling Framework. Error
Handling Framework will send out the Notification |
|
Data File found at SFTP |
If data files are found, all files should be processed, Bulk
Import Operation should be triggered, and the files should be Archived along
with created FBDI |
|
Correct COA Mapping |
Generated FBDI file should have correct COA mapping |
|
Load Interface File for Import Triggered successfully |
●
Consolidated Zip file link will be shared to common Error
handling framework |
|
Import Journals ESS Job
Triggered successfully |
●
Consolidated Zip file link will be shared to common Error
handling framework ●
GL Base Tables should have Source File Data |
Open
Issues:
|
ID |
Issue |
Resolution |
Responsibility |
Target Date |
Impact Date |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Closed Issues:
|
ID |
Issue |
Resolution |
Responsibility |
Target Date |
Impact Date |
|
|
|
|
|
|
|
|
1 |
Do
we get the segment values/Latest oracle cloud COA from source file only |
Yes |
Functional
Team |
|
|
|
2 |
Do we get separate files for the US and CA
Ledger |
Separate
file for US and CA |
Functional
Team |
|
|
|
3 |
Mapping
file in the FDD is not accessible |
Mariappan
to share it again. |
Functional
Team |
|
|
No comments:
Post a Comment