Integration Functional Design Document

EBS AR Middleware enrichment details

ITC-INT-226, ITC-INT-227, ITC-INT-232, ITC-INT-236, ITC-INT-237
CONTENTS
OBJECT INFORMATION........................................................................................................................................................4
DOCUMENT CONTROL..........................................................................................................................................................5
CHANGE RECORD..................................................................................................................................................................5
DRAFT REVIEWERS...............................................................................................................................................................5
APPROVERS...........................................................................................................................................................................5
DOCUMENT REFERENCES – TABLE 3....................................................................................................................................6
1. OVERVIEW........................................................................................................................................................................7
SUMMARY.............................................................................................................................................................................7
VOLUME METRICS – TABLE 1...............................................................................................................................................7
KEY CONTRIBUTORS AND CONTACTS – TABLE 2.................................................................................................................7
ACRONYMS AND DEFINITIONS....................................................................................................................................7
2. ASSUMPTIONS.................................................................................................................................................................9
ASSUMPTIONS – TABLE 4......................................................................................................................................................9
3. REQUIREMENTS...........................................................................................................................................................10
INTERFACE REQUIREMENTS – TABLE 5..............................................................................................................................10
PREREQUISITE SETUPS........................................................................................................................................................10
4. DESIGN.............................................................................................................................................................................11
SOLUTION FLOW DIAGRAM................................................................................................................................................11
EXISTING DETAILED DESIGN FOR A REFERENCE:...............................................................................................................29
Existing Data Views............................................................................................................................................................31
DATA SELECTION CRITERIA................................................................................................................................................31
INTERFACE DEPENDENCIES – TABLE 6...............................................................................................................................31
1. DATA MAPPING.............................................................................................................................................................32
2. ERROR HANDLING.......................................................................................................................................................33
 TEST CONDITIONS.......................................................................................................................................................34
TEST CONDITIONS AND RESULTS – TABLE 10....................................................................................................................34
3. OPEN / CLOSED ISSUES...............................................................................................................................................35
OPEN/CLOSED ISSUES – TABLE 12......................................................................................................................................35
4. APPENDIX........................................................................................................................................................................36
LIST OF TABLES
No table of figures entries found.
OBJECT INFORMATION
Project Name NCR ERP Cloud Phase 2

RICEW ID ITC-INT-226, ITC-INT-227, ITC-INT-232, ITC-INT-236, ITC-INT-237

Interface Name Middleware invoice data enrichment for destination systems

RICEW Short Description Middleware enrichment

Primary RICEW Group Interface

Application Module AR

Source Systems ERP Cloud and on-premise oracle

Destination System Middleware

Volume 4 million Invoices per year 2019/20 million invoice lines per year 2019

Frequency Will follow batch program schedule

DOCUMENT CONTROL
Change Record
Date Author Version Change Reference

10/15/2021 KP/Rajiv 1.0 Draft

12/21/2021 Rajiv 1.1 Change

Draft Reviewers
Name Position Date

Som Tiwari NCR IT BA 10/18/21

French, Cheryl E NCR IT BA 10/18/21
Shalini Joshi IT Director 10/18/21
Natarajan, Selvakumar Accenture ITC 10/18/21
Seshadri, Sharan Accenture ITC 10/18/21
Murtuza N NCR IT BA 10/18/21
Chip fletcher Middleware SME 10/18/21
Sumit Saxena Middleware SME 10/18/21
Eric Stevens Adobe Solution SME 10/18/2021

Approvers
Name Position Date

Sumit Saxena NCR Architect Approval Needed

Srinivas Karnati NCR Manager Approval Needed
Neelam Nangia NCR Architect Approval Needed
Martin Kawecki Executive Director Approval Needed
Document References – Table 3
# Document Document link / Reference ID

Requirement
1 Document - NCR PL101_OTC_Invoicing_v.2
RTM
Detail FD
2 Requirement Invoice Integration FD Detail Req
Document
https://ncr.sharepoint.com/:x:/r/sites/erpcloudfin/_layouts/15/Doc.aspx?sourcedoc=
Mapping %7BD1E274B9-DD88-44D3-BD6E-C66A7165E169%7D&file=ITC-INT-090%20Mapping
3 %20.xlsx&action=default&mobileredirect=true
Document
 1. OVERVIEW
Summary
Summary

This document would contain all the middleware enrichment details to

support all destination systems such as Ariba, Coupa, Pagero , EDI,
Government portal, Pinnacle, Customer email, AEM (Invoice presentation),and
Invoice DB
AR Transactions refers to Invoices, Debit memo and Credit memos.

Volume Metrics – Table 1

# Source System Transaction Type Transaction Volume

Oracle Cloud ERP AR-

Transactions (Invoices, Over all 20 million lines in 2019/ 4 million invoices in 2019
1 Oracle EBS-OM
Debit Memo and
Credit memo)

Invoicing counts are given below in spread sheet

https://ncr.sharepoint.com/:x:/r/sites/erpcloudfin/_layouts/15/Doc.aspx?sourcedoc=%7BE0098C30-DB69-41A2-B830-
2CB48DDA1DA3%7D&file=invoice_Counts.xlsx&action=default&mobileredirect=true

Key Contributors and Contacts – Table 2

Primary Contact Secondary Contact(s)
System / Module
Name
Name Email Name Email

Bhushan
Oracle EBS – ITC KanisettyPhani.Bhushan@ncr.com
KanisettyPhani
Remove Govind Sharma from
Oracle EBS -ITC Govind Sharma Govind.Sharma@ncr.com
the list
Oracle EBS -ITC Rajiv Gulnawar Rajivkumar.Gulnawar@ncr.com
Oracle EBS -ITC Som Tiwari Som.Tiwari@ncr.com
Oracle EBS -ITC Neelam Nangia Neelam.nangia@ncr.com
ACRONYMS AND DEFINITIONS
Acronym Description
AR Accounts Receivables
AR Transactions Refers to AR Invoices, Credit Memos and Debit Memos
ERP Enterprise Resource Planning
OIC Oracle Integration for Cloud
MFT Manage File Transfer
FBDI File Based Data Import
UCM Universal Content Management
Source System Refers to billing systems from where billing extracts are sent
OM Order Management
Descriptive Flex Field – Refers to fields available in Oracle to store additional
DFF
information
KDD Key design decision
RTM Requirement’s traceability matrix
BU Business Unit – Oracle Cloud equivalent of org id (loosely)
ESS Enterprise Scheduler Service – Oracle Cloud’s scheduled job functionality
CMU Common Mapping Utility – NCR custom solution for lookup / cross walk data
mapping between legacy system to Oracle Cloud for GL accounts, config setups,
etc.
OneSource Thomson Reuters’ OneSource tax engine used for sales tax calculation in NCR
AEM Adobe Experience Manager – Cloud solution used for invoice presentation in NCR
GAMIT Global Account Management Invoice Tool – NCR legacy tool for consolidating
invoices across business units / ledgers / cross currencies.
2. ASSUMPTIONS
Assumptions – Table 4
# Assumptions

1 All pre req setups should be completed in ERP cloud with completed status

2 Sales order creation and fulfillment will continue to be in on-premises EBS, and Invoice creation will happen in
Oracle ERP Cloud

3 Any dataset that has been rationalized will leverage CMU to get the new value.

4 Invoice Accounting will be derived based on the Cloud Accounting Solution. Source System will not send invoice
distributions.
5 Primary source for Tax calculation shall be OneSource and not Oracle Cloud.

6 All customers and their sites required for customer invoicing should be available in CDM and ERP cloud

7 Customer Invoice preferences will be captured in CDM

 3. REQUIREMENTS
Interface Requirements – Table 5
Requirement ID Requirement Description

AR-Ability to receive all the CDM invoice preferences/rules

XXX

AR-Ability to push all the Billing transaction information from oracle cloud to middleware.
XXX

AR-Ability to pull document repository, CFS serial numbers, EMS portal/port information, order
management shipping information along with the serial numbers associated with sales order.
XXX

AR-Ability to deliver data to destination systems such as Ariba, Coupa, Pagero , EDI, Government
portal, Pinnacle, Customer email, AEM (Invoice presentation),cloud database and MYNCR.
XXX

AR-Ability to write, update and read invoice records from invoice database
XXX

Prerequisite Setups
These can be set up in the ERP Cloud instance through the Functional Setup Manager.

# Module Details
1 AR • Customers
• Receivables System Options
• Receivables Payment Terms
• Transaction Type
• Transaction Source
• Auto Accounting Rules
• Remit-to Address
• Receipt Methods
• Line Transaction Flex field.
Groping Rules
CDM Preferences
4. DESIGN
Solution Flow Diagram

Solution Description:
Enrich all billing transactions data from ERP cloud and on-premises application such as Order
management, CFS (Service contract), EMS (Shipping) and publish data to destination systems based
on CDM invoice preferences rules.

Destination systems: Ariba, Coupa, Pagero, EDI, Government portal, Pinnacle, Customer email, AEM
(Invoice presentation), database and MYNCR is indirect subscriber from invoice DB.
Middleware would write invoice data to invoicing database and would update invoice data based on
the status of the distribution.
Middleware rules and enrichment details below:
Core data logic/Data preparation/Step of data flow:
1) Once auto-invoice completes for respective BU, Oracle cloud BI report will pull all the newly
created invoices that are complete are send to middleware.
2) Manually completed created invoice per BU would also get pulled by Oracle cloud BIP
report and sent to middleware
3) The middleware should exclude or have filter criteria for invoices that are not eligible for
presentation and not required for any destination systems such as digital and government
portals; examples for exclusion are pre-paid maintenance (PPM) for CFS. This would be
handled via distribution method.
4) Middleware must apply/gather all CDM invoice preferences (reference invoice preference
section), AR cloud would generate the BIP report and push it to middleware. Middleware to
generate data in Json/CXML/XML/text/data as per the destination system requirement
mentioned below. Middleware to write the Json format to invoice database supporting
index records required for MYNCR and BOC Application for searching.
5) Middleware would gather all data from different sources including CDM preference and
write data to invoice database. Preference does not change the data only how it is
shown/distributed to external system
6) If an “additional document” = ‘Yes” in CDM invoice preference for the given customer
account, then middleware will have to pick related document images from document
repository system like EOJ or document repository system. (reference EOJ section)
7) If “subline Information” = ‘Yes” in CDM customer invoice preference, then get the serial
number from on-premises service contract (CFS) and order management (OM) for customer
invoice. Query is provided in the section below. System from which this information is to be
retrieved depends on invoice type table

8) If invoice transaction type is “Intercompany,” from oracle cloud and invoice BU is India. For
India intercompany export transactions, get EMS information from on-premises EMS
(Export management system) custom ERP tables. This is applicable for India plant invoicing
only. Query is provided in the section below.
9) If distribution method = ‘EDI,’ pull the shipping details from order management/shipping for
customer invoices. These invoices will not be sent to AEM for pdf generation. This will be
converted to Json and sent to invoice database for storage. Further destination will be as
per EDI section.
10) For PDF versions when required: Middleware will request the invoice from Adobe and Adobe will read the
data from Adobe AEM as passed over as Adobe AEM API and return the PDF for the invoice to middleware.

11) Shipping details MUST be in the invoice database in order to produce the correct
PDF for the invoices. The final invoice that is sent in Json to the customer should be
stored in the invoice database.
12)The JSON (JavaScript Object Notation) stored in the invoice database should
contain all information and data. This is required so that internal (BOC (Business
Operations Center)) users can see all the data, even if it was not sent to the
customer.
13) This would include BOC UI edits. The BOC application would perform edits on invoices,
credit memo and debit memo that must get relayed back to invoice DB.
14) Invoice database would store Json, metadata (header and lines) for MYNCR, and additional
documents.
15) There would be integration between Salesforce and oracle cloud to provide customer
contact information. This contact information would be used for digital portals/email.
Describe different contacts such as Billing and collection/collector. Customer contact
information example email would flow from oracle cloud BIP report to middleware for
invoice distribution
16) CMU should be used when data gets transformed from on-premises ERP to an oracle cloud.
Example CDM currently stores invoice preference based on operating unit that should get
changed to BU in oracle cloud. CMU would be used to convert data from on-premises
applications such as Order mgmt., CFS and EMS. This information would also be required
when getting preference data from CDM invoice preference. CMU would be mainly used
when joining queries between oracle cloud and any of the on-premise application.
17) The CDM (Customer Data Management) invoice preference application is from bottom to
top. The site-level preference would have the highest application, then account, and last is
country. If there are no CDM invoice preferences available for the invoice type, account,
and site, then country-level preference would be considered as default. The main
consideration of data from CDM invoice preference is from account and site
18)CDM Preferences Invoice type : update Invoice types on the document

List of CDM invoice preference is provided in the SharePoint given below :

https://ncr.sharepoint.com/:x:/r/sites/erpcloudfin/_layouts/15/Doc.aspx?
sourcedoc=%7B8A5A2F61-8E0F-49C5-A677-
 CDB631828652%7D&file=Invoice_Preference_for_workshop.xlsx&action=defau
lt&mobileredirect=true

AEM (Invoice presentation):

Below are the CDM preferences/rules that are needed to be considered for sending XML to
invoice presentation and destination systems as applicable for doing enrichment to core
invoice data. Add the customer preference value and customer invoice preference field tag
to invoice Json and then pass the consolidated Json tag to invoice DB.When fetching data
from customer invoice preference consider account and site number in combination of
invoice type.

1) Distribution Method:
i. Email: If the distribution method is email, then middleware should get the
customer email information from ERP customer contact cloud via BIP report and
send an email to the customer with an email. The customer primary and
secondary email contact information shall be part of BIP report.
ii. Tungsten: If the distribution method is tungsten, then middleware should get a
CSV/Excel file in the required format from Adobe AEM middleware and place it
in SharePoint. SharePoint details will be shared later after discussion with the
BOC team. ITC-INT-064 FD should be referred. Som to provide email address.
iii. Ariba: If the distribution method is Ariba, then middleware needs to apply
additional logic mentioned in the Ariba section.
iv. Coupa: If the distribution method is Coupa, then middleware needs to apply
additional logic mentioned in the Coupa section.
v. Pagero: If the distribution method is Pagero, then middleware needs to apply
additional logic mentioned in the Pagero section.
vi. EDI: If the distribution method is EDI, then middleware needs to apply additional
logic mentioned in the EDI section and send data to the customer such as
Walmart, Scan source, and JC-Penny.
vii. Paper(Pinnacle): If the distribution method is Paper, then middleware needs to
apply additional logic mentioned in the Pinnacle section. Batch print date
viii. No Delivery(Suppress Print) : If the distribution method is “No delivery,” then
middleware should not distribute customer invoices to any of the destination
systems. Customer invoices will get generated in Adobe, and XML will get stored
in the invoice database for MYNCR and BOC UI use.
2) Distribution Format: This is the format of invoice distribution example PDF, Excel, CSV, XML,
CXML, Json
3) Local language: When local language is selected in CDM preference at Bill-To site level, the
customer invoice generation should adhere to that selection in Adobe AEM.IF there is no value
on language preference, default English language should be considered. For example, when
Japan language is selected at Bill- To site level, then invoice generation for that customer site
should display labels in the Japanese/local language.
4) Invoice Display: IF CDM preference is selected to “Detail,” or “Summary “add the tag to invoice
Json and then pass it to invoice DB
5) Tax parameter from CDM to middleware. Tax: Header/Line as per as per customer preference.
If tax = ‘Header,” then Tax will be displayed in the header section of the CXML; else, we
must display Tax at line level.
Oracle cloud will only calculate tax at line level and header using the BI report. Add the
customer preference value and customer invoice preference field tag to invoice Json
and then pass the consolidated Json tag to invoice DB.
6) Tax level parameter = Yes then display Tax in single level no breakup else Tax should be
displayed in breakup level. Example for US tax should be displayed at state, county, and city-
level. Add the customer preference value and customer invoice preference field tag to invoice
Json and then pass the consolidated Json tag to invoice DB.
7) Freight parameter from CDM to middleware. Freight header/line as per customer preference.
If freight = ‘Header,’ then freight will be displayed in the header section of the
CXML
If freight = ‘Line,’ we will have to add a line with PO line reference number = 0;
else, it will get validated with PO line that is entered in the PAAs form.
Add the customer preference tag to invoice Json and then pass the Json
tag to invoice DB
8) If BIP Invoice cloud Credit memo parameter from CDM invoice preference to middleware.
Credit memo Yes/No as per customer preference will be required to send data to destination
systems. Add the customer preference value and customer invoice preference field tag to
invoice Json and then pass the consolidated Json tag to invoice DB.
9) Additional Documents: When the additional document is required based on CDM preference
for the customer invoice, then middleware will have to pick related document images from
document repository systems like EOJ. Add the customer preference value and customer
invoice preference field tag to invoice Json and then pass the consolidated Json tag to invoice
DB.
10) Payment Option: This option is applicable to the collector persona in Adobe AEM, and MYNCR
would also display the payment option to the customer.
11) Zero Dollar Invoice: This customer invoice preference from CDM would be used to store Json in
a cloud database and will be used for invoice presentation. Add the customer preference value
 and customer invoice preference field tag to invoice Json and then pass the consolidated Json
tag to invoice DB.
12) Subline Information: This customer CDM invoice preference would be used to show serial
numbers on customer invoices from on-premises order management or service contract in PDF
generation and online Adobe display. Add the customer preference value and customer invoice
preference field tag to invoice Json and then pass the consolidated Json tag to invoice DB.
13) E invoice flag: If the customer CDM preference flag = ‘Yes.’ then generated e-invoice XML
format for Turkey, else generate e-archive XML format for Turkey.
14) Include balance forward invoices if the summary flag is set to ‘Yes’ in the CDM customer
parameter. Add the customer preference value and customer invoice preference field tag to
invoice Json and then pass the consolidated Json tag to invoice DB.
When the summary flag is “Yes,” then the same steps from 3 to 8 will get repeated.
When excel sheet attachment = ‘Yes’, this invoice upload should be on hold until the
BOC team uploads the excel under location “\\wtc1501cifs\cdunix\ERP\
Digital_Summ_Inv\ERPPROD\snd
15) Middleware to check the invoice type that is selected in CDM preferences for the given
customer. The invoice type from CDM preference and invoice should be mapped based on the
Purchase order or Service contract when sending data to Ariba

Example: If Distribution method = ‘Ariba’, then

If Invoice type in (‘Time & Material,’ ‘GAM-Consolidated’) then
Include selected invoice types to send data to the digital portal.
If the invoice type is enabled for the service contract, then use invoice
presentation CXML and upload it in the digital portal.
If the invoice type is enabled for the purchase order, then we need to do
validation for fields such as ship to, payment term, supplier part id and item
description, and UOM needs to be mapped to the customer purchase order table
that is stored in DBCS against the purchase order and purchase order line.
16) If an invoice is getting uploaded against the purchase order via middleware, should ensure that
the purchase order and purchase order line number is available; if not, this invoice upload
process should be on hold in PAAS form in oracle cloud
17) Currency Code: This will be functional currency received from oracle cloud/CDM
18) AAS invoice presentation: There would be DFF values to store package and sub-package values.
The invoice data should be rollup up based on package and sub-package information. There
would be a CDM invoice preference to show package or package/ sub-package, and this
preference must be applied in invoice cloud data for presentation, and PDF invoices would be
downloaded using MYNCR or BOC UI portal.
Example:

in invoice line Item DFF- DFF- Amount

v number Sub- package
oi pack
c age
e
n
u
m
b
er
1 1 Item1 Sub- Package1 $5
0 pack
0 age1
0
1
1 2 Item2 Sub- Package1 $10
0 pack
0 age1
0
1
1 3 Item3 Sub- Package1 $15
0 pack
0 age2
0
1
1 4 Item4 Sub- Package1 $20
0 pack
0 age2
0
1

AAS Invoice display

i 1
n 0
v 0
o 0
i 1
c
e
n
u
m
b
e
r
P $
a 5
c 0
k
a
g
e
1
S
u
b
-
p
a
c
k
a
g
e
1
S
u
b
-
p
 a
c
k
a
g
e
2

Ariba:

1) When the purchase order is created in the digital portal inbound XML data of the customer purchase order shall be
stored in SOUP DBCS via middleware. Currently, this mechanism is handled through SOUP. There is validation and
enrichment process that check the data from purchase order table and transforms invoice data before sending it to
Ariba. Current on-premise tables are INFX.NCR_EINV_PORTAL_PO_EXT_HEADER and
INFX.NCR_EINV_PORTAL_PO_EXT_LINE.
2) Middleware will check customer distribution method parameter in CDM preferences if it is selected to ‘Ariba.’
Distribution method = ‘Ariba.’
3) Middleware to check the invoice type that is selected in CDM preferences for the given customer. The invoice type
from CDM and invoice should be mapped based on the Purchase order or Service contract. The BIP report or 090
invoice would have the invoice type that has to be mapped with customer invoice preference invoice type to get
below value.

If Distribution method = ‘Ariba,’ then

If Invoice type in (‘Service Contract’) then
Include selected invoice type to send data to the digital portal.
If the invoice type is enabled for the service contract, use invoice presentation CXML and upload it to the digital
portal.
Suppose the invoice type is enabled for the purchase order. In that case, we need to validate fields such as ship to,
payment term, supplier part id, and item description, and UOM needs to be mapped to the customer purchase
order table stored in DBCS against the purchase order and purchase order line.
If the invoice is getting uploaded against the purchase order via middleware, should ensure that the purchase
order and purchase order line number is available; if not, this invoice upload process should be on hold.
4) All the credentials related to the customer for digital portal would be stored in middleware. The credentials are
needed to access the digital portal when uploading invoices data. Currently on-premise is maintaining following
table for reference : NCR_AR_EINV_PORTAL_CRED
5) When uploading data to the digital portal, we must check additional parameters such as attachment required = ‘Y.’
6) If attachment required = ‘Y,’ check this attribute in CDM invoice preference and then include invoice pdf while
sending data to the customer.
7) Tax parameter from CDM to middleware. Tax: Header/Line as per as per customer preference.
If tax = ‘Header,” then Tax will be displayed in the header section of the CXML; else, we have to display Tax at line
level.
8) Tax level parameter = Yes then display Tax in single-level no breakup else Tax should be displayed in breakup level.
Example for US tax should be displayed at state, county, and city-level
9) Freight parameter from CDM to middleware. Freight header/line as per customer preference.
If freight = ‘Header,’ then freight will be displayed in the header section of the CXML
If freight = ‘Line,’ we will have to add a line with PO line reference number = 0; else, it will get validated with the
PO line that is entered in the PAAs form.
10) Credit memo parameter from CDM to middleware. Credit memo Yes/No as per customer preference.
The same steps mentioned in points number 3 to 8 will be repeated for credit memo logic.
11) Include balance forward invoices if the summary flag is set to ‘Yes’ in the CDM customer parameter.
When the summary flag is “Yes,” the same steps from 3 to 8 will be repeated. When excel sheet attachment =
‘Yes,’ this invoice upload should be on hold until the BOC team uploads the excel under location “\\wtc1501cifs\
cdunix\ERP\Digital_Summ_Inv\ERPPROD\snd
12) If summary flag = ‘Yes’ (Balance forward), do not upload any standalone invoices related to the summary invoice.
13) cXML data should match with Adobe PDF images, and the process should work in parallel.
14) Once the invoice is uploaded to digital portal capture; the success/failure status should be stored in middleware
DB. Example fields MCN, invoice number, status, and portal name.
15) Tracking data is stored in the invoice JSON for each invoice Fields are: DistributionType,
DistributionDate, DistributionResult,Contact and PortalResponse more fields can be added if needed.
16) Freight need to display only if it grater then Zero
17) Some Customers won't allow to upload invoices with older invoice date,for this we need to check
customer pereference for the no of days need to just the invoice date, so that it will be sucessfully
uploaded into Ariba .

IF it is a blanket PO, Purchase order number will be displayed under XML Tag MasterAgreementIDInfo
agreementID

<MasterAgreementIDInfo agreementID="BPO1241-R1">
</MasterAgreementIDInfo>

for others Purchase order number will be displayed under XML Tag OrderReference orderID

<OrderReference orderID="PO1241-R1">
<DocumentReference payloadID=""/>
</OrderReference>

Error notification need to be send to the respective people with error details as attachment .
Coupa:
1) When the purchase order is created in the digital portal inbound XML data of the customer purchase order shall
be stored in SOUP DBCS via middleware. Currently, this mechanism is handled through SOUP. There is validation
and enrichment process that check the data from purchase order table and transforms invoice data before
sending it to Coupa. Current on-premise tables are INFX.NCR_EINV_PORTAL_PO_EXT_HEADER and
INFX.NCR_EINV_PORTAL_PO_EXT_LINE.

2) Middleware will check customer distribution method parameter in CDM invoice preferences if
it is selected to ‘Coupa’. Distribution method = ‘Coupa’
3) Middleware to check the invoice type that is selected in CDM invoice preferences for the
given customer. The invoice type from CDM invoice preference and invoice should be mapped
based on the Purchase order or Service contract.

If Distribution method = ‘Coupa,’ then

If Invoice type in (‘SERVICE CONTRACT’) then
Include selected invoice types to send data to the digital portal.
If the invoice type is enabled for the service contract, use invoice presentation CXML and
upload it in the the digital portal.
4) Suppose the invoice type is enabled for the purchase order. In that case, we need to validate
fields such as ship to, payment term, supplier part id and item description, and UOM needs to
be mapped to the customer purchase order table stored in DBCS against the purchase order
and purchase order line.
5) If the invoice is uploaded against the purchase order via middleware, should ensure that the
purchase order and purchase order line number is available; if not, this invoice upload process
should be on hold.
6) All the credentials related to the customer would be stored in middleware. The credentials
are needed to access the digital portal when uploading invoices data. Currently on-premise is
maintaining following table for reference : NCR_AR_EINV_PORTAL_CRED
7) If the accounting flag in customer preference = ‘Yes,’ COA mapping should be derived based
on customer store number. These seven segments are related to the customer. This applies to
Darden customers only. The mapping logic is as below.
IF upper(v_addressee) LIKE 'DARDEN%'
 THEN
p_segment1 := '003';
p_segment3 := '003' || v_store_number;
ELSIF upper(v_addressee) LIKE 'OLIVE%' THEN
IF
v_country_code = 'US'
THEN
p_segment1 := '002';
p_segment3 := '002' || v_store_number;
ELSE
p_segment1 := '202';
p_segment3 := '202' || v_store_number;
END IF;
ELSIF upper(v_addressee) LIKE 'BAHAMA%' THEN
p_segment1 := '005';
p_segment3 := '005' || v_store_number;
ELSIF upper(v_addressee) LIKE 'SEASONS%' THEN
p_segment1 := '007';
p_segment3 := '007' || v_store_number;
ELSIF upper(v_addressee) LIKE 'YARD%' THEN
p_segment1 := '010';
p_segment3 := '010' || v_store_number;
ELSIF upper(v_addressee) LIKE 'LONGHORN%' THEN
p_segment1 := '012';
p_segment3 := '012' || v_store_number;
ELSIF upper(v_addressee) LIKE 'CAPITAL%'
THEN
IF upper(v_addressee) LIKE 'CAPITAL%BURGER%'
THEN
p_segment1 := '021';
p_segment3 := '021' || v_store_number;
 ELSE
p_segment1 := '013';
p_segment3 := '013' || v_store_number;
END IF;
ELSIF (upper(v_addressee) LIKE 'EDDIE%'
or upper(v_addressee) LIKE 'WILDFISH%') THEN
p_segment1 := '017';
p_segment3 := '017' || v_store_number;
ELSIF upper(v_addressee) LIKE 'CHEDDARS%' THEN
p_segment1 := '020';
p_segment3 := '020' || v_store_number;
ELSE
p_segment1 := '003';
p_segment3 := '003' || v_store_number;
END IF;
p_segment2 := '63010';
p_segment4 := '000';
p_segment5 := '00000';
p_segment6 := '0000000';
8) When uploading data to the digital portal we must check additional parameters such as
attachment required = ‘Y.’
9) If attachment required = ‘Y,’ check this attribute in CDM preference and then include invoice
pdf while sending data to the customer.
10) Tax parameter from CDM to middleware. Tax: Header/Line as per as per customer
preference.
If tax = ‘Header,” then Tax will be displayed in the header section of the CXML; else, we have
to display Tax at line level.
11) Tax level parameter = Yes then display Tax in single-level no breakup else Tax should be
displayed in breakup level. Example for US tax should be displayed at state, county, and city-
level
12) Freight parameter from CDM to middleware. Freight header/line as per customer preference.
If freight = ‘Header,’ then freight will be displayed in the header section of the XML
 If freight = ‘Line,’ we will have to add a line with PO line reference number = 0; else, it will get
validated with the PO line entered in the PAAs form.
13) Credit memo parameter from CDM to middleware. Credit memo Yes/No as per customer
preference.
14) The same steps mentioned in points number 3 to 12 will be repeated for credit memo logic.
15) Include balance forward invoices if the summary flag is set to ‘Yes’ in the CDM customer
parameter.
16) When the summary flag is “Yes,” the same steps from 3 to 12 will be repeated. When excel
sheet attachment = ‘Yes,’ this invoice upload should be on hold until the BOC team uploads
the excel under location “\\wtc1501cifs\cdunix\ERP\Digital_Summ_Inv\ERPPROD\snd
17) If summary flag = ‘Yes’ (Balance forward), do not upload any standalone invoices related to
the summary invoice.
18) cXML data should match with PDF images, and the process should work in parallel.
19) Once the invoice is uploaded to the digital portal, capture the success/failure status should be
stored in middleware DB. Example fields MCN, invoice number, status, and portal name.
20) Error notification need to be send to the respective people with error details as attachment .

Pagero:
1) Since Pagero is going to be enabled in Turkey, So this interface will have enabled for Turkey
BU.
2) Middleware will check customer distribution method parameter in CDM preferences if it is
selected to ‘Pagero.’ Distribution method = ‘Pagero.’
3) When BU is Turkey, Middleware will receive BIP report that will have all Turkey supported
mapping fields from ERP Cloud.
4) All the credentials related to the customer/government portal would be stored in
middleware. The credentials are needed to access via digital portal when uploading invoices
data.
5) cXML data should match with invoice Presentation PDF images, and the process should work
in parallel.
6) Once the invoice is uploaded to the digital portal, capture the success/failure status should be
stored in middleware DB. Example fields MCN, invoice number, status, and portal name.
EDI:
1) When the purchase order is received from the customer, inbound XML data of the customer
purchase order shall be stored in DBCS via middleware. This mechanism is handled through
SOUP.
2) Middleware will check customer distribution method parameter in CDM preferences if it is
selected to 'EDI'. Distribution method = 'EDI'.
3) Get the shipping delivery tracking number, delivery date, waybill number from on-premises
order management/shipping tables based on interface header context = 'ORDER ENTRY' for a
given invoice.
4) Creation of the Trading Partner ID. The bill to num has enough 0's right pad to make it a total
length of 11, then cat "IN" and the Location Code (42 if T&M/Emili,44 if OM, 45 if OKS)
5) Get AOR number and Vendor id from customer site table (attribute8) hz_cust_acct_sites_all
table. Below is the statement.
a. SELECT decode(instr(cas.attribute8, 'AOR', 1, 1), 0, NULL,
substr(cas.attribute8, instr(cas.attribute8, 'AOR', 1, 1) + 3, 6)) ||
decode(instr(cas.attribute8, 'ACCT', 1, 1), 0, NULL,
substr(cas.attribute8, instr(cas.attribute8, 'ACCT', 1, 1) + 4, 4)),
decode(instr(cas.attribute8, 'VEN', 1, 1), 0, NULL,
substr(cas.attribute8, instr(cas.attribute8, 'VEN', 1, 1) + 3,
length(cas.attribute8)))
INTO :sAORNumber :indAORNumber,
:sVendorID :indVendorID
FROM hz_cust_site_uses_all csu,
hz_cust_acct_sites_all cas
WHERE csu.site_use_id = :sFuaNum
AND csu.cust_acct_site_id = cas.cust_acct_site_id;

6) After doing all the required enrichment generate EDI 810 format for the customers such as
Walmart, Scan source and Homedepot. EDI can use existing mapping currently in production.
7) Customer acknowledgment will be received once EDI is received by the customer. The tracking
data for this distribution need to be added to the JSON invoice data. The EDI result string
should also be added tracking data.
8) Below is the current EDI standard format as a reference.
 Government portal: The customer generated invoices should match with
government portal invoices. All invoices for all government portals must be
written to the invoice database.

EOJ/Additional document: For Additional document attachment currently, the

process would be manual. The middleware automation would work only for
TAMBA billing.

India GST QR Code, IRN and eway bill generation:

1. On ERP Cloud run the oracle standard program to generate .XML format file.
2. Send generated .XML format file from ERP Cloud to Middleware.
3. Middleware will enrich data and apply rollup and other preferences.
4. Middleware will generate JSON format file to upload into DigiGST
Note: Middleware can use the same existing credentials to upload data into DigiGST portal
5. Middleware will receive QR Code, IRN and eway bill as a response in JSON format.
6. Middleware will place the received response file from DigiGST (both Success and Error) to UCM
directory.
7. Oracle cloud standard program will upload the file to ERP Cloud.
8. Middleware will also include received response (QR Code, IRN and eway bill information) in
AEM Presentation JSON.
9. Middleware will save updated JSON into invoicing DB.
1) To generate XML, all the prerequisite configurations have been provided to the Cloud AR
team. Example (The document Number sequence, Tax type, Transporter id to generate E-Way
Bill etc..etc..
2) Send XML data (Created based on government mapping elements in Cloud AR) to DigiGST to
generate IRN, QR Code, and e-Way bill using credentials provided by DigiGST.
3) Note: We can use the same existing credentials to upload data into DigiGST portal
4) Receive a response from DigiGST (both Success and Error) and publish it to Oracle Cloud.
5) For successfully uploaded documents (Invoice), publish the IRN, QR Code, and e-way bill
information to ERP AR Cloud. This must go into the Invoice database and oracle cloud
6) Frequency should be immediately after the invoice is generated and completed in ERP AR
Cloud.
7) Middleware would consume data from oracle cloud using BI and publish it to the government
portal
 Chile:

1. When BU is Chile Middleware will receive BIP report that will have all Chile supported mapping
fields from ERP Cloud.
2. Middleware will enrich data and apply rollup and other preferences.
3. Middleware will create .txt format file per below attached specified field positions.

4. Place the .txt format file on \\wtc1501cifs\cdunix\ChileInvoicing\Production\rcv

5. File format should be as per below format.
File Format:
'E' ||
lpad(out_rec.JGZZ_Fiscal_Code, 8 , '0') ||
'T' ||
lpad(out_rec.TipoDTE, 3 , '0') ||
'F' ||
lpad(out_rec.Folio, 10 , '0');

Example:
'E' -- Hard code value
HZ_Parties.JGZZ_Fiscal_Code
'T' -- Hard code value
FND_Document_Sequences.attribute15
'F' -- Hard code value
RA_Customer_Trx.trx_number
6. On DBNet Production Server (SUSDAY15604) CronJob has been created that will move .txt file
from \\wtc1501cifs\cdunix\ChileInvoicing\Production\rcv to \\susday15604\suite_sucursal\
in\carg_auto.
7. On DBNet Production Server (SUSDAY15604) DBNet CronJobs have been created to convert .txt
format files to JSON and also upload JSON files to Government Portal.

1) If the BU is Chile, the middleware will create country specific .txt format file conditions provided by Chile
local government.
2) Place the file in this folder \\susday014\cdunix\ChileInvoicing\Production\rcv
3) BOC will manually move the .txt file from the shared location to the DBNet server folder.
4) Archiving is required for this interface.
Colombia:

1. When BU is Colombia Middleware will receive BIP report that will have all Colombia supported
mapping fields from ERP Cloud.
2. Middleware will enrich data and apply rollup and other preferences.
3. Middleware will create .txt format file per below attached specified field positions.

4. Place the .txt format file on \\wtc1501cifs\cdunix\ColombiaInvoicing\Production\rcv

5. File format should be as per below format.
File Format:
'E' ||
lpad(out_rec.JGZZ_Fiscal_Code, 8 , '0') ||
'T' ||
lpad(out_rec.TipoDTE, 3 , '0') ||
'F' ||
lpad(out_rec.Folio, 10 , '0');

Example:
'E' -- Hard code value
HZ_Parties.JGZZ_Fiscal_Code
'T' -- Hard code value
FND_Document_Sequences.attribute15
'F' -- Hard code value
RA_Customer_Trx.trx_number

6. On DBNet Production Server (SUSDAY15604) CronJob has been created that will move .txt file
from \\wtc1501cifs\cdunix\ColombiaInvoicing\Production\rcv to \\susday15604\TXTJSON\
TXTJSON\TXT.
7. On DBNet Production Server (SUSDAY15604) DBNet CronJobs have been created to convert .txt
format files to JSON and also upload JSON files to Government Portal.

1) If the BU is Colombia, the middleware will create country-specific .txt format file conditions provided by
Colombia's local government.
2) Place the file in this folder \\susday014\cdunix\ColombiaInvoicing\Production\rcv
3) BOC will manually move the .txt file from the shared location to the DBNet server folder.
4) Archiving is required for this interface.
Peru:

1. When BU is Peru Middleware will receive BIP report that will have all Peru supported mapping
fields from ERP Cloud.
2. Middleware will enrich data and apply rollup and other preferences.
3. Middleware will create .txt format file per below attached specified field positions.

4. Place the .txt format file on \\wtc1501cifs\cdunix\ERP\PeruInvoicing\Production\rcv

5. File format should be as per below format.
File Format:
l_RA_Customer_Trx(i).attribute15
|| lpad(out_rec.JGZZ_Fiscal_Code, 8 , '0')
|| 'T'
|| lpad(out_rec.TipoDTE, 3 , '0')
|| 'F'
|| lpad(out_rec.Folio, 10 , '0');

Example:
l_RA_Customer_Trx(i).attribute15
HZ_Parties.JGZZ_Fiscal_Code
'T' -- Hard code value
FND_Document_Sequences.attribute15
'F' -- Hard code value
RA_Customer_Trx.trx_number

6. On DBNet Production Server (SUSDAY15604) CronJob has been created that will move .txt file
from \\wtc1501cifs\cdunix\ERP\PeruInvoicing\Production\rcv to \\susday15604\esuite_suc\in\
carg_auto.
7. On DBNet Production Server (SUSDAY15604) DBNet CronJobs have been created to convert .txt
format files to JSON and also upload JSON files to Government Portal.

1) If the BU is Peru, the middleware will create country specific .txt format file conditions provided by Peru
local government.
2) Place the file in this folder \\adc1551cifs\cdunix\PeruInvoicing\Production\rcv
3) BOC will manually move the .txt file from the shared location to the DBNet server folder.
4) Archiving is required for this interface.
Portugal:
1. ERP Cloud will generate standard/custom SAFT file.
2. Middleware will place the file in shared location for user to access
3. User will upload this to Portal.

Hungary:

1. ERP Cloud will run custom program to generate Hungary XML files.
2. ERP Cloud will send XML files to Middleware
3. Middleware will place the XML files on \\wtc1501cifs\cdunix\ERP\HungaryRealtime directory.
4. Then CronJob will move the files from shared location to Hungary server.

Italy:

1. When BU is Italy Middleware will receive BIP report that will have all Italy supported mapping
fields from ERP Cloud.
2. Middleware will enrich data and apply rollup and other preferences.
3. Middleware will create CXML format file as per Italy government format.

4. Middleware will place the CXML files on \\wtc1501cifs\cdunix\ERP\Italy_Einv\Production\rcv directory

5. Business will manually pick and upload into Italy Government Portal.

Note: Argentina and Mexico would have government requirements too that has to be documented.

Pinnacle:
1) Middleware will check customer distribution method parameter in CDM preferences if it is selected to
‘Pinnacle’. Distribution method = ‘Pinnacle’.
2) Middleware will send data to AEM
3) AEM will generate .PS file and will return to Middleware
4) Middleware will place the .PS file on AEM shared location
5) FTS will move from AEM server to Pinnacle server for distribution.

Note: Since Pinnacle directories was not yet created on AEM server, so will share exact location once created.

6) Generate .xml file in Pinnacle by applying the core logic.

7) Move the .xml file to RPC server on /u001/oracle/_NCR/BatchPrintingInput/input
8) Then AEM will generate .ps file to send to middleware, and then the middleware would do the distribution to
Pinnacle.
9) Middleware will request Adobe Experience Manager to generate and return the PRC (Postscript) files required
by Pinnacle

Invoice Tracking:
 1) After sending data to different systems based on delivery method middleware will insert tracking
information in invoicing DB table.
2) Invoice database shall store the invoice delivery details in the invoicing database. BOC application should
provide report for non-delivery invoices. The retention of these records in database would consist for 1 year of
history.
3) Middleware will pick tracking details from invoice database and shall get passed back to oracle cloud for
consumption. There will be functionality in BOC application to update invoice tracking manually by user.

4) Middleware shall store or update the invoice database tracking table once its gets successful or failure
response from distribution systems.

Tracking detail table requirement

Offline Report Attributes (Excel Columns)

 BU
 Invoice Number
 Invoice Date
 Account number/MCN
 Account name
 Delivery Method
 Distribution Type
 Distribution Date
 Distribution Status
 Contact
 Creation Date
 Created By
 Last Update Date
 Last Updated By

Invoice database:

Note: There is separate FD for invoice database, and it is on NCDM On-Prem DB.

1) Middleware would receive AR transactions from the ERP cloud.

2) Cloud database would store all the transactions and then send data in XML/Jason format to MYNCR for
consumption.
3) The Cloud database would store the invoices if underline data is changed or updated in CDM or oracle cloud.
4) Cloud database would store all invoices metadata.
5) MyNCR should only use the invoice database for all searching, results and invoice related data.
 Below are the queries to pull records from external systems
Invoice Type
Time & Material
Subscriptions
Service Contracts
Product/TS/PS
Parts Sales
Non-Trade
GAM-Consolidated
Digital Insights

OM Details:

1) Check If transaction type is Solutions (OM) then

--Get the item type code

SELECT pl.item_type_code --INTO vParent_Item_Type

FROM NCR_OM_ORDER_DTLS_V pl, NCR_OM_ORDER_DTLS_V l
WHERE l.line_id in (261428255) --(select INTERFACE_LINE_ATTRIBUTE6 from
NCR_AR_CLOUD_TRX_LINES_ALL where customer_trx_line_id=pass value from BIP report)
AND pl.line_id = l.parent_line_id;

If item_type_code not in ('STANDARD','CLASS') then

If it is invoice then use below query to get OM serial numbers

SELECT s.unit_serial_number, s.unit_line_id,

SUBSTRB(ms.attribute11, 1, 30) import_activity_loc,
SUBSTRB(ms.attribute14, 1, 20) import_date,
SUBSTRB(ms.attribute15, 1, 30) import_activity_num,
NUll stock_transfer_invoice -- Added against the RFC# 17985
FROM NCR_ONT_SERIAL_GENEALOGY s, MTL_SERIAL_NUMBERS ms
 WHERE s.unit_line_id in (select to_number(INTERFACE_LINE_ATTRIBUTE6) from
NCR_AR_CLOUD_TRX_LINES_ALL
where CUSTOMER_TRX_LINE_ID = 100003208492504)
AND s.unit_serial_number = ms.serial_number (+)
AND ms.attribute11 (+) is not null
AND EXISTS (SELECT 1
FROM WSH_DELIVERY_LINE_STATUS_V d
WHERE d.SOURCE_CODE = 'OE'
AND d.SOURCE_LINE_ID in (select to_number(INTERFACE_LINE_ATTRIBUTE6) from
NCR_AR_CLOUD_TRX_LINES_ALL
where CUSTOMER_TRX_LINE_ID = 100003208492504)
AND NVL(s.top_level_serial_number, s.unit_serial_number)
BETWEEN d.serial_number AND
NVL(d.to_serial_number, d.serial_number));

If it is Credit Memo then use below query to get OM serial numbers

SELECT s.unit_serial_number,
SUBSTRB(ms.attribute11, 1, 30) import_activity_loc,
SUBSTRB(ms.attribute14, 1, 20) import_date,
SUBSTRB(ms.attribute15, 1, 30) import_activity_num,
NUll stock_transfer_invoice -- Added against the RFC# 17985
FROM NCR_ONT_SERIAL_GENEALOGY s, MTL_SERIAL_NUMBERS ms
WHERE s.rma_line_id in (select to_number(INTERFACE_LINE_ATTRIBUTE6) from
NCR_AR_CLOUD_TRX_LINES_ALL
where CUSTOMER_TRX_LINE_ID = 100003208492504)
AND s.unit_serial_number = ms.serial_number (+)
AND ms.attribute11 (+) is not null;

CFS Details:

English Translation Cloud (Source Fields) XML Fields (Destination AEM)

Serial Numbers Not availble in Cloud one item for each serial number f
Below is the reference screenshot.

EMS Details:

English Translation Cloud (Source Fields) XML Fields (Destination AEM) Sou

Not availble in Cloud ExporterReference EM

Not availble in Cloud ContainerNumber EM

Shipping Information Not availble in Cloud ShipType EM

Not availble in Cloud OriginCountry EM
Not availble in Cloud FinalDestination EM
Not availble in Cloud PreCarriage EM
Not availble in Cloud ReceiptPreCarrier EM
Not availble in Cloud DischargePort EM

Not availble in Cloud VesselNumber EM

Not availble in Cloud LoadingPort EM
Bill To EM
ContainerNumber1 EM
Functional Currency EM

Query1:
SELECT distinct
nvl (wdt.mode_of_shipment ,' ') ShipType
,nvl (wdt.Remarks ,' ') DescriptionHSN
,nvl (wdt.OTHER_REFERENCE ,' ') OtherReference
,nvl (wdt.CONTAINER_DETAIL ,' ') ContainderDetails
,nvl (wdt.PORT_OF_LOADING ,' ') LoadingPort
 ,nvl (wdt.TERMS_OF_PAYMENT ,' ') TermsofDeliveryandPayment
,nvl (wdt.PORT_OF_DISCHARGE ,' ') DischargePort
,nvl (msi.segment1 ,' ') PartNo
,nvl (wdt.MARKS_NO ,' ') ContainerNumber
,nvl (TRIM(SUBSTR(wdt.MARKS_NO,INSTR(wdt.MARKS_NO, '(')+1,INSTR(wdt.MARKS_NO, ')')-
INSTR(wdt.MARKS_NO, '(')-1)),' ') ContainerNumber1
,nvl (to_char(wnd.delivery_id),' ') deliveryid
,nvl (to_char(wnd.organization_id),' ') organizationid
,LTRIM (TO_CHAR( NVL(wfc.attribute10,'0'))) freightamt
,LTRIM (TO_CHAR( NVL(wfc.attribute11,'0'))) insuramt
,LTRIM (TO_CHAR( NVL(wfc.attribute12,'0'))) miscamt
, LTRIM (TO_CHAR( NVL(wfc.attribute13,'0'))) otheramt
, rctl.customer_trx_id
, (select organization_code from org_organization_definitions
where organization_id = ool.ship_from_org_id) WarehouseCode
, ( select NVL(TERRITORY_SHORT_NAME,'XX') From hz_party_sites hzs, hz_locations hzl,
fnd_territories_tl ftl
where PARTY_SITE_NUMBER = rctl.SHIP_TO_SITE_NUMBER
and hzs.location_id = hzl.location_id
and TERRITORY_CODE = hzl.country) FinalDestination
FROM
oe_order_lines_all ool
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,wsh_delivery_details wdd,
wsh_freight_costs wfc,
NCR_WSH_DEL_TRACKER wdt,
infx.NCR_AR_CLOUD_TRX_LINES_ALL rctl,
apps.mtl_system_items msi
where wdd.SOURCE_HEADER_ID=ool.HEADER_ID
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wfc.DELIVERY_ID = wnd.delivery_id
AND wdt.DELIVERY_ID = wda.delivery_id
AND ool.line_id=to_number(rctl.INTERFACE_LINE_ATTRIBUTE6)
AND msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID
AND rctl.customer_trx_id = 1334608382; -- Pass invoice_id from cloud BIP report

Query2: This is needed later to display bill_to_address on India Export transactions will discuss because this ne special
requirement for Export Transactions.

SELECT
nvl(hps.addressee, ' ') || CHR(13) || CHR(10) ||
nvl(hl.address1, ' ') || CHR(13) || CHR(10) ||
(decode (hl.address2, null , '',hl.address2 || CHR(13) || CHR(10) )) ||
nvl(hl.city, ' ') || CHR(13) || CHR(10) ||
nvl(hl.country, ' ')
FROM
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl,
ra_customer_trx_all rct
WHERE
( hcsua.site_use_id = rct.BILL_TO_SITE_USE_ID
AND hcsua.site_use_code = 'BILL_TO' )-- optional but couldn't hurt
 AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hps.party_site_id = hcasa.party_site_id
AND hl.location_id = hps.location_id
AND rct.customer_trx_id = 1334608382; -- Pass invoice_id from cloud BIP report

Query3:

From Query1 if ShipType like '%SEA%' THEN

ContainerNumber1 = 'We certify that the goods covered under this Invoice were stuffed in Container no: '||
ContainerNumber1 || ' with self seal no under my supervision';
ELSE
ContainerNumber1 = 'Certify that the Qty,Description,Weight and the value of goods covered in this Invoice and the
Particulars given in the Packing List has been checked by us and the same has been packed and sealed with Self Seal
No :NCR-0796003980 under my supervision';

Query4: To displaying functional currency values for India Export Transactions we need to get values from EMS custom
ncrx.ncr_export_fx_rates tables.
Existing on-premises Invoice Engine presentation details for a referece:
1. Transactions need to be created and completed in Cloud Accounts Receivables for all billing
systems before they are eligible to generate AEM Presentation XML.
2. Data can be entered into AR one of two ways. IE supports both data entry methods.
Data can be entered manually into AR using two Oracle supplied forms, one for Invoices / Debits and one for
Credit Memos.
Data can be imported into AR using an Oracle supplied process called Auto Invoice.
3. To generate XML for OM transaction:
a. Check billing source from ra_customer_trx_all.interface_header_context = ‘ORDER
ENTRY’
b. Get OM transactions data from Oracle Cloud AR
c. Also get OM additional attributes data from OM View: NCR_OM_ORDER_DTLS_V,
since it was not available in oracle standard AR table and is needed for Invoice Image.
Example data elements: CDP (oe_order_headers-all.attribute12), CIS
(oe_order_headers_all.attribute11), Quote Number
(oe_order_headers_all.attribute8), Wot product group for rollups
(oe_order_headers_all,attribute16) etc. etc.
d. Apply CDM preferences (like: Rollup, Language etc...)
i) If rollup_flag = Y then
all lines, rolled up to single line
else
display all lines in XMLfile

ii) For language preference incase language was not selected from CDM customer site (Bill To) then
default it ‘EN’
Note: Additional preferences details can be found in CDM -- Billing Preference document.

e. Generate XML
Explanation details for Rollup preference can be found below.
 CDM
Preferences

Rollup of Delievery Summary Zero Dollar

Language
lines Method Flag Invoices

Say for instance, an invoice has 50,000 lines, if all lines are presented to customer, invoice will run to thousand of pages
Rollup lines is a functionality where multiple invoice lines are rolled up as a single line.

Process flow of Rollup Lines

PDF Showing multiple

Invoice with multiple Apply CMD Rollup
lines as single line and
lines Preference
amout is summed up

Example from a system:

Invoice Header
Lines of Invoice in this case 3 lines
Invoice Image Presented to Customer
*Note:- This is sample only, ERP has invoices which can have lines greater than 50K

4. To generate XML for CFS transaction:

a. Check billing source from rac_customer_trx_all.interface_header_context = ‘OKS
ONTRACTS’
b. Get CFS AR data from Oracle Cloud
 c. Get CFS additional attributes from CFS View: OKS_CONTRACT_DTLS_V, Since it is not
available in oracle standard AR table and is needed for Invoice Image.
Example data elements: Port number, Software Key number etc...)
d. Apply CDM preferences selected in against customer (like Rollup the lines into sub-line
information)
e. Generate XML
5. For ES, SCA (Zuora) and Sales Force we need to understand how the design flow was
developing in Cloud integration for retrieving additional attributes.

Note: We have additional logic/data elements based on country/region those

can be discussed further.

Existing Detailed Design for a reference:

The extract process is a custom extension that will populate the batch process interface (INF) tables for Invoice Engine,
NCR_IE_INF_HEADERS, NCR_IE_INF_FOR_USE_ATS, NCR_IE_INF_LINE_ITEMS, NCR_IE_INF_SERIALS. New invoice data
is obtained from tables within AR and from the feeder systems views and IE custom tables.

A single input parameter is required to determine which billing system is being processed. The input parameter cross
references NCR_IE_ORDER_SYSTEMS and NCR_IE_ORDER_SYS_BATCH_SOURCES.
NCR_IE_ORDER_SYS_BATCH_SOURCES is used to determine the associated invoice type values which need to be
extracted from AR. The NCR_IE_ORDER_SYSTEMS.extension_data_flag is used to indicate if the invoice type uses
custom tables or a view. If the custom tables are used the data will be moved during processing of each AR record.
The feeder_system_code from is used to drive special processing for this billing system. The sys_max_inv determines
the number of invoices to process in this run. The commit_inv flag is used to determine the number of invoices that
must be processed before a DB commit is invoked.

There are two tables that drive the state of processing, NCR_IE_PROCESS and NCR_IE_PROCESS_EXTERNALS.
NCR_IE_PROCESS will be used to determine the last state of running. The process code determines the state of the last
run. There are three states complete, in error, or incomplete. In the event that the process was complete the INF
tables are deleted and new data is extracted, otherwise the data remains in tact and new data is added to the old data.
NCR_IE_PROCESS_EXTERNALS is used to flag down hill processes that must use the IE INF data. An entry exists in this
table for each system that must access the INF data.

 Everything is complete and processing needs to continue as normal

 The last run was not complete and processing needs to pick up at the last commit point.
 The last run was not complete and processing needs to skip to the next batch process.

Two tables exist to handle which invoices need to be extracted for processing. NCR_IE_EXTRACT_LAST_INVOICE and
NCR_IE_EXTRACT_INVOICES. NCR_IE_EXTRACT_LAST_INVOICE contains a single record for each order system of the
last invoice produced. The sequential internal Oracle id, ra_customer_trx_all.customer_trx_id is stored to indicate the
last sequential invoice processed. Any invoice greater than this value (not including) is extracted for invoice thru
Invoice Engine. NCR_IE_EXTRACT_INVOICES contains a list of invoices that must be reproduced or that were not
processed or complete during the previous run range. The customer_trx_id is used to access back into Oracle for the
particular invoice to process.
There are two main cursors for processing, cust_trx_csr and cust_trx_lines_csr. Cust_trx_csr processes all the invoice
header records found on RA_CUSTOMER_TRX_ALL and cust_trx__lines_csr processes all the invoices lines associated
with each invoice by using the customer_trx_id to obtain all associated lines from ra_cusotmer_trx_lines_all.

NCR_IE_INTRFCE_CUST_TRX_LINES and NCR_IE_INTERFACE_SERIALS are the IE custom interface tables which house
extraneous data from the billing system. The first time the data has been processed the extraneous data is moved
from the interface tables to the permanent tables, NCR_IE_CUST_TRX_LINES and NCR_IE_SERIAL_NUMBERS. Once the
data has been moved the data cannot be altered or updated be the billing system. These tables are tied to
RA_INTERACE_LINES_ALL by using interface_line_attribute1-14 (interface_line_attribute15 is used for assignment
processing) concatenate together with a tilde (~) into a single field interace_line_attribute_key. This single field is done
to improve processing and to add a single index column. Once the data has been moved to the permanent tables the
cusotmer_trx_id and customer_trx_line_id are added and can be used to tie to RA_CUSTOMER_TRX_ALL and
RA_CUSTOMER_TRX_LINES_ALL.

Billing systems that do not use the IE custom table have DB views to pass additional data to IE for processing. This data
is maintained and updated by the billing systems.

For fatal errors all processing ceases and will rollback to the last commit point. Individual invoices that are in error are
flagged and marked for processing on the next invoice engine run on NCR_IE_EXTRACT_INVOICES.

The extract process constructs four record structures, NCR_IE_INF_HEADERS, NCR_IE_INF_FOR_USE_ATS,

NCR_INF_SERIALS NCR_IE_INF_LINE_ITEMS for each invoice. During processing all the AR data is extracted and placed
into the records. If the feeder system uses IE extension tables, the data is extract for the IE tables and overlaid onto
the AR data. Based on the billing system code any special data is processed and overlaid in the data records. After all
data massaging occurs the record is inserted into the DB.

Within this document if the field is not specifically listed the value remains null.

The following files support the extract process:

• NCR_IE ($IEXX_TOP/install/db/ie_pkg.sql) – Package of common functions and procedures used

by all Invoice Engine processes
• NCR_IE_EXTRACT_PKG ($IEXX_TOP/install/db/ieextrct_pkg.sql) – Main driver for extract logic
• NCR_IE_EXTRACT_AR_PKG ($IEXX_TOP/install/db/ieextar_pkg.sql) – Executes logic for assigning
IE fields for common AR fields for all systems
• NCR_IE_EXTRACT_OM_PKG ($IEXX_TOP/install/db/ieextom_pkg.sql) – Executes the logic for
assigning the IE fields for Order Management sources
• NCR_IE_EXTRACT_OKS_PKG ($IEXX_TOP/install/db/ieextoks_pkg.sql) – Executes the logic for
assigning the IE fields for Order Contracts sources
• NCR_IE_EXTRACT_IF_PKG ($IEXX_TOP/install/db/ieextif_pkg.sql) – Executes the logic for
assigning the IE fields for systems external to the Oracle application, like EMILI
• NCR_IE_EXTRACT_LANG_PKG ($IEXX_TOP/install/db/ieextlang_pkg.sql) – Executes the logic for
assigning the language-based IE fields
• NCR_IE_EXTRACT_GET_PKG ($IEXX_TOP/install/db/ieextget_pkg.sql) – Executes functions and
procedures to lookup data based on internal IDs
• NCR_IE_EXTRACT_ADD_PKG ($IEXX_TOP/install/db/ieextadd_pkg.sql) – Contains the insert
statements into the NCR_IE_INF% tables
• NCR_IE_B_BUILD_CONFIGS_PROC ($IEXX_TOP/install/db/iebconfig_proc.sql) – Sets a field to
uniquely identify configurations for OM
• NCR_IE_B_BOILER_PROC ($IEXX_TOP/install/db/ieboiler_proc.sql) – Main driver for boiler
process. Copies data from ‘INF’ to ‘B’ tables, calculates the totals and calls the other
procedures/functions/packages to execute the preference settings
 • NCR_IE_B_PREFERENCES_PROC ($IEXX_TOP/install/db/iebprefs_proc.sql) – Driver for preference
execution. Sets data for correspondence, forms, and media preferences and calls other
processes for rollup, summary, and product substitution preferences
• NCR_IE_B_PROD_SUBSTITUTE_PROC ($IEXX_TOP/install/db/iebprodsub_proc.sql) – Executes the
product substitution preferences
• NCR_IE_B_SEQ_INVOICES_PROC ($IEXX_TOP/install/db/iebseqinv_proc.sql) – Orders invoices in
a sequence for batch printing
• NCR_IE_B_SEQUENCE_LINES_PROC ($IEXX_TOP/install/db/iebseqline_proc.sql) – Orders the
lines within an invoice. Called multiple times throughout the boiler process.
• NCR_IE_B_CREATE_SUMMARIES_PROC ($IEXX_TOP/install/db/iebsummary_proc.sql) – Creates
the summary invoices
• NCR_IE_B_TSG_SYSTEM_ID_PROC ($IEXX_TOP/install/db/iebtsgsysid_proc.sql) – Creates System
ID line for System ID rollup option
• NCR_IE_B_WOT_PROD_PROC ($IEXX_TOP/install/db/iebwotprod_proc.sql) – Creates the WOT
product line for the WOT product rollup options
• NCR_IE ($IEXX_TOP/install/db/ie_pkg.sql) – Package of common functions and procedures used
by all Invoice Engine processes
• NCR_IE_B_GROUP_OKS_LINES_PKG ($IEXX_TOP/install/db/iebgroupoks_pkg.sql) – Combines
sub-lines for CFS according to grouping criteria prior to rollup preference execution
• NCR_IE_B_ROLLUP_OKS_PKG ($IEXX_TOP/install/db/iebrolloks_pkg.sql) – Executes the rollup
preference for CFS order system
• NCR_IE_B_ROLLUP_PKG ($IEXX_TOP/install/db/iebrollup_pkg.sql) – Executes the rollup
preference for non-CFS order systems

Existing Data Views

OKS_CONTRACT_DTLS_V This view supports extraneous data for Oracle contracts.
NCR_OM_ORDER_DTLS_V This view supports extraneous data for Oracle Order Management.

Data Selection Criteria

Interface Dependencies – Table 6
# Dependency

1 Customer for whom invoices need to be enriched should exist in cloud.

2T Items for which invoices need to be imported should exist in cloud.

3Ta Tax integration between OneSource and Cloud exist.

5 Mapping of ERP on Prem to Cloud values for Transaction types, Payment terms, Remit to address etc. will be
derived using common mapping utilities (CMU).
 1. DATA MAPPING
Sample Link:
 2. ERROR HANDLING
Error Handling – Table 9
 TEST CONDITIONS
Test Conditions and Results – Table 10
# Test Condition Expected Results
Create a new order billing invoice in the
EBS will pulls the newly created invoice transaction with source
1 source system (Oracle EBS) and push to EBS
OM% and creates an extract file.
interface tables.
Transfer the file to ERP Cloud and run the The file from Oracle EBS is transferred and the ERP cloud program
2
Cloud Import Program via OIC. picks it up and processes it into ERP Cloud.
Check the invoices are created in ERP Cloud
3 Review the invoice for accuracy in both ERP Cloud AR.
AR
Create a new order billing credit memo in the
EBS will pulls the newly created credit memo transaction with
4 source system (Oracle EBS) and push to EBS
source OM% and creates an extract file.
interface tables.
Transfer the file to ERP Cloud and run the The file from Oracle EBS is transferred and the ERP cloud program
5
Cloud Import Program via OIC. picks it up and processes it into ERP Cloud.
Check the credit memos are created in ERP
6 Review the credit memo for accuracy in both ERP Cloud AR.
Cloud AR
Program should update flag is EBS to
Records are available in the EBS interface table to ERP Cloud AR.
7 Interfaced once OIC imports finished without
any error
Program should update flag is EBS to E should EBS records updated to “E”
8
there be any exceptions/errors
 3. OPEN / CLOSED ISSUES
Open/Closed Issues – Table 12
# Issue Raised By / Assigned / Resolution Resolved Status
Date Responsibility By / Date
Needed Completed

9
4. APPENDIX