Wednesday, 22 October 2025

198 TDH Receipts from CEVA to Oracle ERP

INIINNT.198 TDH Receipts from CEVA to Oracle ERP

1.1.   Functional Requirement

Objective is stated in the above introductory section.

Time Zone: Eastern Standard Time (U.S. and Canada)

TDH (Teladoc)

 

Suppliers & Product SKUs: to CEVA:

WM-1000 & 1500 (Scales)- TRANSTEK

HT-900 (Hypertension) – TRANSTEK

Accessories & Collateral: TRANSTEK

KITS (Welcome, Refill, Replacement): RRD

 

 

1.     Currently On-Hand is reported midmorning 1010 HRS (ET) . Request has been made to report On-Hand end of business (Post reporting/transmitting receipts, shipments) 1800 (6pm) EST.-->Action item: Seth with CEVA.

2.     Receiving report from CEVA is transmitted on hourly basis from 9 AM until 6 PM (ET) to a dedicated secured folder at TDOC serverà Action item: TDH team to work with CEVA IT 

3.     Receiving detail report will feature Purchase Order Number.

4.     Receiving report will feature physical receipts of KITS/Scales/Meters/Collateral/Accessories (Currently the practice is to report only conforming components)à Action item: Seth/Janine with CEVA.

5.     Following matrix illustrates relationship between Product Class and tracking of lot/serial numbers in Oracle ERP. The same output is expected on the receiving report from CEVA (Value in column (B)).

 

 

(A)

(B)

Product Class

Lot number tracked captured in Oracle with Shelf-Life expiration date (In-Future)

KITs(Welcome, Refill

Y

Scales

N

Meters

N

Collateral

Y

Accessories

Y

 

Note: Expiration date mandated by CCM – 45 days prior to expiration needs to act and cannot be picked for shipment. Needs to be reported to CCM Quality for disposition.

6.     Inter-ORG receipts from RRD will be based on shipment number (ASN). This is a manual act by RRD in the system (Oracle ERP).

7.     Receipts will be primarily based on Purchase Order for RRD & TRANSTEK.

 

 

1.2.   Scope

The scope of this document includes:

Technical detailed design for the Integration for Receiving Receipt and Inventory transactions to Oracle ERP Cloud

This document doesn’t include the technical design details for the Vendor systems/Source Systems.

 

1.3.    Assumptions

●        All the Inventory, Subinventory, and item setups are done in Oracle ERP.

●        Hourly transmission of files to TDH FTP secured directory. Absence of source file will trigger an email notification to TDH SCM team (Lookup code with names and email address).

●        TDH SCM team will spot check receipts with source file for validations/audit.

●        All inventory reported will be in positive integers and value greater than zero (0). No negative quantity or null rows will be reported.

●        Lot numbers for all Product SKUs will mandate Shelf-Life expiration date.

●        No Serial Numbers and Revisions will be reported.

●        Identification of source file @ TDH dedicated directory/folder (per TDH IT) and its consumption for processing.

●        Grouping of the records from the source file will be based on Item Code, Supplier PO, Lot Code and Site. The sum of the quantity will be calculated based on the grouping logic.

●        Site Value in the source file should never be Null as decision to perform action is based on Site Value.

●        In case of multiple document IDs in the source file for One PO, Random Document ID will get picked up and create receiving receipt.

●        Error handling: Errors found during the run/processing expected to report errors in specifications report.

●        Errors on account of no source file found: Need to email TDH SCM & IT team (Action Item: featured as open issue). This is primarily to reconfirm with CEVA IT. (Email List: Lookup with name & email address, maintained by TDH SCM Team).

●        Custom program will keep a track how many files were received, records processed, errored with date/time. This is primarily for audit purposes and for check and balances. This file can be at summary level as follows:

 

2.               Technical Approach

2.1.   Prerequisites

The following setups needs to be completed for the integration

●        Inventory, Sub inventory, and Inventory Item

●        Supplier, Supplier sites

●        Lookup (TDH_SCM_COMMON_LOOKUP) needs to be maintained in OIC to hold all the global variables like SFTP folder names, job properties file parameters, error notification details against USGHNetsuite interface code

 

The Integration user requires the following roles

Job Roles

Custom R&S will be created.

 

• TDH Warehouse Operator

 

The user’s privileges associated with this feature are:

 

•      Monitor Receiving Receipt Work Area

•      Put Away Receiving Receipt

•      Review Completed Inventory Transaction

•      Correct Receiving Receipt

•      Load File to Interface

•      Load Interface File for Import

•      Manage File Import and Export

 

Note: The above R&S is a glimpse to correct/update errors if requires manual intervention.

At the point when this document was composed, TDH Roles and Security team was not formed/established.

 

2.2.   Process Flow Diagram

The Receiving Receipt and Inv Transaction integration is divided into three Integrations 1.TDH_RCV_CEVA_ReceivingReceipt_IN

2.TDH_RCV_ReceivingReceipt_Callback (1.0)

3. TDH_INV_InventoryTransaction_callback

 

●        TDH_RCV_CEVA_ReceivingReceipt_IN integration will read the file from SFTP server and transform the data into Receiving Receipt and Inventory Transaction FBDI format and then submit the Bulk Import Process.

●        TDH_RCV_ReceivingReceipt_Callback integration will get triggered once the “Manage Receiving transaction” ESS Job is completed in Cloud.

●        TDH_RCV_ReceivingReceipt_Callback integration will get triggered once the “Manage Inventory transaction” ESS Job is completed in Cloud

 

 

 

Integration: Flow Diagram

 

 

 



 

2.3.   Process Flow Description

  1. CEVA team needs to perform data extraction,generate source file in predefined csv format and place it in the SFTP Location “/Inbound/CEVA/ReceivingReceipts/In”.
  2. Once the file is placed in above SFTP directory, below steps will be performed by OIC Integration (as per schedule).

 

TDH_RCV_CEVA_ReceivingReceipt_IN:

 

1.     Assignment of values to Global Variables by deriving values from OIC lookup “TDH_SCM_COMMON_LOOKUP” based on Interface Code “CEVA_RECEIPT198”.

  1. Using SFTP Adapter “TDH_OIC_SFTP” list all the source files from SFTP directory “/Inbound/CEVA/ReceivingReceipts/In” with file name pattern as “*.csv”.
  2. If no files are found, send notification and exit the integration.
  3. If files are found, for each listed file, download the file using SFTP Adapter “TDH_OIC_SFTP” to the OIC staging area. Read data files in segments and store data from files to a global object type variable.
  4. Extract unique POs from source data and store it in a variable.
  5. Submit BIP report ‘TDH_RCV_SupplierDetails_Report’ to get the Supplier, supplier site, line number and scheduling number details using unique PO numbers.
  6. Check for the supplier
    If supplier <> RRD Mexico <Supplier name from TDH_SCM_COMMON_LOOKUP> and site <> LIVAD then transform data into Receiving Receipt FBDI format and write in Receiving Receipt FBDI file at stage location

If Supplier = RRD Mexico <Supplier name from TDH_SCM_COMMON_LOOKUP>  and site <> LIVAD then transform data into Inventory Transaction FBDI format for sub inventory Transfer and write data into Inventory Transaction FBDI file in stage location

If Site = LIVAD then transform data into Inventory Transaction FBDI format to create inventory transaction and write data into Inventory Transaction FBDI file format in stage location.

  1. After FBDI, create a properties file containing values of the parameters needed to submit “Manage Receiving Transaction” and/or “Manage Inventory Transaction” ESS Jobs as per the file.
  2. Zip above created ReceivingReceipt/InventoryTransaction Import FBDI and properties file using “Stage Activity”.
  3. Using Oracle ERP Cloud Adapter “TDH_OIC_ERP_SCM_CLOUD” trigger “Bulk Import” operation to Import using above generated Zip File (This operation will upload the zip to UCM, submit Load Interface file for Import and trigger Import ESS Jobs).
  4. After submitting the Bulk Import Operation, wait till “Load Interface file for Import” is completed.
  5. If the “Load Interface file for Import”  ESS Job status is error, email notification will be sent (via OIC or Common Error Utility – TDH_CommonErrorHandling_CALE_V2) , and error file will be placed at SFTP Location “/Inbound/CEVA/ReceivingReceipts/Error”.
  6. If the “Load Interface file for Import” ESS Job status is successful, the process will internally trigger “Manage Receiving Transaction” and/or “Manage Inventory Transaction” ESS Job. On completion of the job Oracle Cloud will raise an event which in turn will trigger “TDH_RCV_ReceivingReceipt_Callback_IN and/or TDH_INV_InventoryTransaction_callback_IN”.
  7. If the “Load Interface file for Import” ESS Job status is successful, then Archive source file and created FBDI to SFTP Folder “/Inbound/CEVA/ReceivingReceipts/Archive”.
  8. If Load programs errors out, the file from the source directory will be placed in error directory to get it corrected by users
  9. In case of any error in import, email will get triggere with the log zip file link to the user
  10. Users are expected to correct data in the screen or extract the data to be reprocessed again, Create the source file with the same data and place it again to the source IN directory to get it reprocessed. The old processed files will be placed to Error directory.

 

Note : Any error while performing above steps will be handled using Common Utility Error Handler at Global Fault

 

 

TDH_RCV_ReceivingReceipt_Callback_IN:

  1. This integration will be triggered by Oracle Cloud on completion of Manage Receiving Transaction ESS Job.
  2. Integration will check the summary status from the payload provided by the cloud.
  3. If the status is Error, Log/Output files will be delivered using email notification

 

Manage Inventory Transaction

  1. This integration will be triggered by Oracle Cloud on completion of Manage Receiving Transaction ESS Job.
  2. Integration will check the summary status from the payload provided by the cloud.
  3. Integration will download the zip file created by the ERP and unzip the file in stage
  4. If any error file found in the zip file downloaded, Log/Output files will be delivered using email notification.

 

 

 

2.4.   Parameters

Parameter Name

Parameter Value

Type

NA

 

 

 

 

2.5.   Restart and Recovery

●        Place data File at SFTP Location

●        Resubmit TDH_RCV_CEVA_ReceivingReceipt_IN Integration manually from OIC

 

2.6.   Sample Source File Format

File Name

TDH_RCV_DAILY_DTL_YYYYMMDD 00:00:00.csv

System Date and Hours

YYYYMMDDHHMM

Output Type

.csv

Delimiter

Comma , “,”

 

 

 

Sample source file name: *.csv

 

 

 

 

 

           

3.              Technical Components

3.1.   Oracle Integration Cloud (OIC) Objects

3.2.   Object List

The following are the components/objects related to the Integration

Integration Name

Integration Identifier

Package Name

Integration Type

Description

TDH_RCV_CEVA_ReceivingReceipt_IN

TDH_RCV_CEVA_RECEIV_IN

 

Scheduled

Orchestration

Receiving Receipt File Based Inbound integration

TDH_RCV_ReceivingReceipt_Callback

TDH_RCV_RECEIPT_CALLBACK

 

App Driven Orchestration

Call-back for Manage Receiving Transaction ESS Job

TDH_INV_InventoryTransaction_callback

TDH_INV_INVENTOR_CALLBACK

 

App Driven Orchestration

Call-back for Manage Inventory Transaction ESS Job

 

3.3.   Connections Used

 

Connection Name

Adapter

Role

Description

TDH_OIC_SFTP

FTP

Trigger and Invoke

FTP Server Host: 147.154.19.246

FTP Server Port: 22

SFTP: Yes

TDH_OIC_SCM_CLOUD

Oracle ERP Cloud

Trigger and Invoke

ERP Cloud Host: https://fa-euwl-dev1-saasfaprod1.fa.ocs.oraclecloud.com/

TDH_OIC_SCM_REP_SERVICE_CLOUD

Soap Adapter

Invoke and trigger

To submit BIP reports and get data.

 

 

 

 

 

 

 

3.4.   OIC Lookup Details

 

Lookup Name: TDH_SCM_COMMON_LOOKUP

Columns Used

Description

Value

INTERFACECODE

 Unique code

CEVA_RECEIPTINT198

FROM_EMAIL

 

Mulesoftalerts@teladochealth.com

TO_EMAIL

 To email

SFTP_IN_FOLDER

 Source file path

/Inbound/CEVA/ReceivingReceipts/In

SFTP_ARCHIVE_FOLDER

 Archive File path

/Inbound/CEVA/ReceivingReceipts/Archive

SFTP_ERROR_FOLDER

 Error file Path

/Inbound/CEVA/ReceivingReceipts/Error

FILE_PATTERN

 Name pattern

*.csv

FILE_EXTENSION

 File extension

csv

REPORT_PATH

 BIP report path for PO Supplier details

/Custom/TDH_Integrations/SCM/TDH_RCV_SupplierDetails_Report.xdo

ENVIRONMENT

dev

SOURCE

 Source System

CEVA

TARGET

 Target System

Oracle ERP Cloud

EMAIL_FLAG

 

TRUE

JIRA_FLAG

 

TRUE

JIRA_PROJECT_KEY

 

PRISK

JIRA_ISSUE_TYPE

 

Bug

JIRA_PRIORITY

 

Medium

JIRA_LABELS

 

Info

JIRA_ASSIGNEE

 

<Jeera Assignee>

BU_LOOKUP_NAME

 Business Unit

TDH US BU

WAIT_LIMIT

 

 

SFTP_BIREPORT_FOLDER

 

 

SFTP_IMPERRORDTLS_FOLDER

 

 

Attribute1

 Ship To Org/Organization Code/Inv Transfer org

CEVA

Attribute2

 Employee Name required in FBDI

Bernard, Hanish

Attribute3

 The supplier to be compared with for receiving Receipt and Sub Inventory transfer

RRD Mexico
RR Donnelley Global Turnkey Solutions (As per the CR CON0000681-177018)

Attribute4

  Receiving Receipt – Subinventory

FG

Attribute5

 Receiving Receipt -Transaction Type

RECEIVE

Attribute6

 Receiving Receipt – Auto Transact Code

DELIVER

Attribute7

 Receiving Receipt -Source Document Code

PO

Attribute8

 Receiving Receipt – Receipt Source Code

VENDOR

Attribute9

 Receiving Receipt – Destination type Code

INVENTORY

Attribute10

 Receiving Receipt- Sold to LE

Teladoc Inc
Teladoc Health, Inc

(As per the CR CON0000681-178479)

Attribute11

SubInventory transfer – Sub Inventory Code

Intransit

Attribute12

 Subinventory Transfer -Subinventory

FG

Attribute13

 

Attribute14

 Site code for Inventory Transaction

LIVAD

Attribute15

 Inventory Trx- Organization name

IMM

Attribute16

 Inventory Transaction – Subinventory Code

FG

Attribute17

 

 

Attribute18

 

 

Attribute19

 

 

Attribute20

 Link to download the log files

https://fa-euwl-dev4-saasfaprod1.fa.ocs.oraclecloud.com/cs/idcplg?IdcService=GET_FILE&dID=

INTEGRATION_NAME

 Interface Name

TDH_RCV_ReceivingReceipt_IN

Segment1

 Inventory Transaction Accounting segment1

10010

Segment2

Inventory Transaction Accounting segment2

11100

Segment3

Inventory Transaction Accounting segment3

50430

Segment4

Inventory Transaction Accounting segment4

000

Segment5

Inventory Transaction Accounting segment5

40030

Segment6

Inventory Transaction Accounting segment6

000

Segment7

Inventory Transaction Accounting segment7

0000

Segment8

Inventory Transaction Accounting segment8

00000

Segment9

Inventory Transaction Accounting segment9

00000

Segment10

Inventory Transaction Accounting segment10

00000

Bearer_Token

 NA

 NA

 

 

3.4.1.   FTP Details

FTP Name:

Host Address

147.154.19.246

Port

22

Folder Name

/Inbound/CEVA/ReceivingReceipts/In

 

 

 

 

 

 

 

 

3.4.2.   API Details

Resource

Methods

Notes

Sample Payload (link)

NA

 

 

 

 

 

 

 

 

 

3.5.   BI Reports

TDH_RCV_SupplierDetails_Report

3.5.1.   BI Report Data Model

TDH_RCV_SuppliersDetails_DM

3.5.2.   BI Report Data Model Query

SELECT

    hp.party_name    supplier_name,

    pos.segment1     supplier_number,

    pss.vendor_site_code,

    pol.line_num,

    pll.shipment_num Shipment_number,

item_number,

line_status

FROM

    po_headers_all                 poh,

    po_lines_all                   pol,

    poz_suppliers                  pos,

    hz_parties                     hp,

    po_line_locations_archive_all  pll,

    poz_supplier_sites_all_m       pss,

    (SELECT unique inventory_item_id, item_number from egp_system_items_b) items_tab

WHERE

        poh.po_header_id = pol.po_header_id

    and inventory_item_id = pol.item_id

    AND poh.vendor_id = pos.vendor_id

    AND pos.party_id = hp.party_id

    AND poh.segment1 = :P_PO_NUM

    AND pll.po_line_id = pol.po_line_id

    AND poh.vendor_site_id = pss.vendor_site_id    

 

Table and View Usage

    po_headers_all

    po_lines_all

    poz_suppliers

    hz_parties

    egp_system_items_b

 

3.5.3.   BI Report Parameters

P_PO_NUMBER             Purchase Order           String

3.5.4.   BI Report Bursting Details

NA

3.5.5.   BI Report Bursting Query

NA

3.5.6.   BI Report Layout Details

NA

3.6.   ESS Jobs

NA

3.6.1.   ESS Job List of Values Details

NA

3.6.2.   ESS Job Parameters

NA

 

4.              Error handling

4.1.   Error Handling and Reporting

When the interface fails, the error message along with the log should be sent to email as per the lookup setups.

 

The Interface produces Exception Report with the below information and sends to the identified

 IT email

 

 

Scenario

Handling Section

Error Message

Action

Error while Loading file records in Interface Table

Email Notification / Global Fault Handler (use Common Error Handling Utility- TDH_CommonErrorHandling_CALE_V2)

Error while loading data to Interface table

Error log files can be downloaded via link provided in the email notification

Error while Import Job fails

Email Notification / Global Fault Handler (use Common Error Handling Utility- TDH_CommonErrorHandling_CALE_V2)

Error while importing Receiving Receipt or Inventory Transaction

Error log files can be downloaded via link provided in the email notification

Data File not found at SFTP Location

Email Notification / Global Fault Handler (use Common Error Handling Utility- TDH_CommonErrorHandling_CALE_V2)

Data File not found at SFTP Location

Email Notification will be sent out

 

 

4.2.   Restart

The interface will automatically rerun for 4 times if the extract fails

5.              Technical Unit Test

5.1.   Unit Test Scenarios

<Provide the Unit Test Scenarios>

Scenario

Details

Data File not found at SFTP

 

 

If the data file is not found at SFTP Location, Email Notification should be triggere

Data File found at SFTP

If data files are found, all files should be processed, and Bulk Import Operation should be triggered and the files should be Archived along with created FBD

Load Interface File for Import Triggered successfully

No Action Required or no email or file will be shared

Import ESS Job Triggered successfully

No Action Required or no email or file will be shared

Load Interface File for Import failed

Trigger email with error details and with the link to download the Oracle generated files

Import ESS Job failed

Trigger email with error details and with the link to download the Oracle generated files


By Joshi at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)