AIM

MD.050 APPLICATION EXTENSIONS

FUNCTIONAL DESIGN

UNIVERSAL COLD STORES

POS BATCH INTERFACE

Author: B.Palanivelu
Creation Date: March 29, 2008
Last Updated: March 29, 2008
Document Ref: UCS/JCCS/MD50- POS Interface
Version: 1.1
 Doc Ref: UCS/JCCS/MD50- POS Interface

Document Control

Change Record

Date Author Version Change Reference

21-Mar-08 B. Palanivelu Draft 1a No Previous Document

25-Mar-08 B. Palanivelu 1.0 Draft 1a
29-Mar-08 B. Palanivelu 1.1 1.1

Reviewers

Name Position

Mr. Khalaf Al Khalaf UCSTCL Project Sponsor

Mr. Ajmal UCSTCL Project Manager
Mr. Tarun UCSTCL IT Manager
Mr. Hassan KPMG Project Manager
Mr. Essam AlSayed Jeraisy Projects Manager
Mr. N R Moorthy Principle Consultant

Distribution

Copy Name Location

No.

1 Library Master Project Library

2 UCSTCL ………..
3

Approvals

Copy No. Name Signature

1 Mr. Suleiman Al-Khalaf

2 Mr. Khalaf Al Khalaf

3 Mr. Hassan Jamal

Note to Holders:

If you receive an electronic copy of this document and print it out, please write your
name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front
cover, for document control purposes.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 2
 Doc Ref: UCS/JCCS/MD50- POS Interface

Contents

Document Control............................................................................................................ ii

Topical Essay..................................................................................................................... 1
Basic Business Needs................................................................................................. 2
Solution Brief.............................................................................................................. 2
Major Features............................................................................................................ 4
Process Flow – Inbound POS Interface.....................................................................5
Definitions................................................................................................................... 6
Example....................................................................................................................... 7
Accounting Entries................................................................................................... 10
User Procedures........................................................................................................ 11
Business Rules........................................................................................................... 12
Assumptions............................................................................................................. 12
Open and Closed Issues for this Deliverable................................................................13
Open Issues............................................................................................................... 13
Closed Issues............................................................................................................. 13

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 3
 Doc Ref: UCS/JCCS/MD50- POS Interface

Topical Essay

Introduction

UCS as a strategic decision decides to implement Oracle E-Business Suite to handle

the Financial, Procurement, Inventory and Sales functions in Phase-1 and other
functions in a later stage. This will facilitate UCS to have a centralized integrated
system with better control and quicker reporting.

UCS uses decentralized Al-Amin POS system and POS Hardware at all retail outlets
and decides to continue with the existing Al-Amin POS System in all retail outlets.

Business Flow

All Retail Locations will be mapped and created as a separate Inventory

Organizations/Sub Inventory in Oracle. All material transfers to retail locations will
be initiated in Oracle before the material transfer and Inventory updated.

On receipt of material the same will be recorded in the Al-Amin system.

Retail Cash sales will be processed in the Al-Amin POS in all locations and at the
end of day the POS Cash Sales transaction details will be extracted from all
locations, formatted and uploaded into Oracle Sales Order Interface.

Oracle has a standard Interface facility to Import Orders entered in third party
system, Oracle also provides Import program which validates and import the
Orders into Oracle Order Management, the import orders will be progressed using
the Oracle Workflow Background Engine to update the respective Branch/Retail
locations Inventory in the Oracle EBS at the head office.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 4
 Doc Ref: UCS/JCCS/MD50- POS Interface

Basic Business Needs

54 Locations of UCS uses the POS System and cash sales are handled through the
Al-Amin POS system in a decentralized model, POS system retail sales transactions
are not consolidated, UCS head office is not able to monitor and control the retail
locations.

To have better control and to prepare quicker MIS reports and Financials the POS
Cash Sales activities are required to be interfaced with Oracle EBS.

To Interface will address the following requirements:

 Control, Analyze and Track all the POS Cash Sales Transactions in Oracle E-
Business Suite.
 Book the Revenue and Cost and monitor the POS cash collection
 Generate daily MIS report for all locations on Retail Sales
 Prepare consolidated financials considering al the POS Sales from all the
locations

Solution Brief

A batch interface will be developed to interface the POS Sales transactions to Oracle
E-Business Suite to update the Inventory in Oracle, book Cost of Sales and Account
Revenue.

The POS sales data will be extracted and populated in the Sales Order Interface
table in Oracle and the Standard Oracle Order Management module’s Order Import
program will validate and import the Order to Oracle Order Management in
Booked Status.

The Orders will be imported with the Transaction Type associated with the
Standard Order cycle Viz. Bill Only with Inventory Interface, the Oracle Workflow
background engine will progress the order and take care of the remaining cycle like
Inventory Interface and Receivables Interface to update the Inventory and Book
Revenue.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 5
 Doc Ref: UCS/JCCS/MD50- POS Interface

The following steps are involved:

a. Populate the POS System sales date into Oracle Sales Order Interface
b. Import the Orders into Oracle Order Management using the Run
Concurrent program – Order import with required Parameters
c. Standard Sales Order will be generated with booked status.
d. Run Work Flow background Engine Process
e. Inventory Transaction and Receivables Interface will be triggered
automatically by the Work Flow.
f. The Cost Manager will cost the Material Transactions automatically based
on the Average cost of the Item and book Cost of Goods Sold

Note :

Common customer account using the Sales Person name will be used to control the
cash collections and minimize the customer records.

UCS should establish connectivity to all POS locations to bring the POS sales data to
a centralized location, have control in the POS System, format and provide data in
the requested format.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 6
 Doc Ref: UCS/JCCS/MD50- POS Interface

Major Features

The following Oracle Standard Features will be used

 Standard Open Order Interface Program will be used

 Standard Oracle OTC Workflow Order Cycle will be used for Order
Processing

 The Workflow Background Engine will Progress the Inventory Interface and
Receivables Interface automatically

 The Cost Manager will cost the material transaction using the Average cost
dynamically

 The entire process can be scheduled and executed periodically at the user
defined interval automatically

 No manual steps required in Oracle except the Cash Receipt entry and
application

 This integration is protected during upgrade and across versions

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 7
 Doc Ref: UCS/JCCS/MD50- POS Interface

Process Flow – Inbound POS Interface

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 8
 Doc Ref: UCS/JCCS/MD50- POS Interface

Definitions

OTC

Over the Counter – Oracle provides special simplified work flow cycle for Counter
sales which uses simple Book the Order and Run AutoInvoice for Revenue booking.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 9
 Doc Ref: UCS/JCCS/MD50- POS Interface

Example

A) Following is the details of assumed sample date extracted from the POS System
from Dammam branch, for two POS Cash customers sale.

The person in charge for the sales and who collected the money from customer is
Mr. Mohammad (Sales Person).

Field Value Value Value

POS Cash Sales No. POS-1001 POS-1001 Pos-1002

Sale Date 25-MAR-2008 25-MAR-2008 25-MAR-2008

Sales Person Name Mohammad Mohammad Mohammad

Line No. / ID 1 2 1

Item Code 10011011001 20012512001 20012512001

UOM CAS KGS KGS

Qty 5 10.50 25

Selling Price 21.00 15.90 15.90

POS System Line ID 100010 100011 100020

Branch /Location Dammam Dammam Dammam

Customer Name Abdul Aziz Abdul Aziz Aslam

(Optional)

Total Cash Collected by Mr. Mohammad

POS Cash Sales No. SAR

POS-1001 5 X 21 105.00

10.5 X 15.90 166.95

POS-1002 25 X 15.90 397.50

Total 669.45

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 10
 Doc Ref: UCS/JCCS/MD50- POS Interface

B) The extracted date will be formatted and loaded in the Sales Order Open
Interface (OE_HEADERS_IFACE_ALL and OE_LINES_IFACE_ALL Tables)

C) The Order Import program will validate and import the Sales Order in Booked
Status. The imported Orders details are provided below for reference:

1) Order No. POS-1001 in Oracle Order Management

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 11
 Doc Ref: UCS/JCCS/MD50- POS Interface

2) Order No. POS-1001 in Oracle Order Management

D) The POS system imported Cash Sales Order will use will use the Counter Sales
Cycle work flow. The Workflow Background engine will interface the Order
Lines to Inventory and Receivables.

E) In Receivables the AutoInvoice import program will generate Invoice based on

the Order details.

F) Generate control Reports to validate the POS Cash Sales Import and Receivables
Interface.

G) Cash/Bank Receipt based on the cash deposit by the Sales Person Mohammed
will be entered for SAR 669.45 and applied using Auto Application Rule for
POS-1001 and POS-1002 Invoices and the invoices will be closed.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 12
 Doc Ref: UCS/JCCS/MD50- POS Interface

Accounting Entries

The following accounting Entries will be generated automatically in Inventory

Module after the POS Cash Sales Order is Imported and the Workflow
Background Engine processed the Order.

1) Event – Sales Order Shipping, Module – Cost Management

Debit: Cost of Goods Sold Account (COGS)

Credit: Inventory Valuation Account (Material Cost)

This COGS Account will be derived dynamically through the COGS Account
Generator definition and the Inventory Valuation account will be derived
from the shipping (Sales) Organization.

2) Event AR AutoInvoice - Module – Receivables

Debit: Receivables (Cash Sales)

Credit: Revenue (Sales – Cash)

This Revenue Account will be derived dynamically through the Auto

Accounting definition in Receivables. Receivables in AR will be tracked
based on the Location and Salesperson.

3) Event Receipt Accounting - Module - Receivables

Debit: Cash / Bank

Credit: Receivables

The receipt will be entered using the Sales Person Customer and applied
through the Auto Application Rule.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 13
 Doc Ref: UCS/JCCS/MD50- POS Interface

User Procedures

Summary

Step-1

Load Oracle formatted data to Sales order interface table (this will be
automated using Concurrent Request after registering the Request)

.
Step-2

Run or Schedule Sales Order Import concurrent program and check the
exception and correct (The orders will be imported in Booked Status)

Step-3

Run or Schedule the Work flow Background Engine

Step-4

Run the AutoInvoice Master Program to generate Sales Invoices

Step-5

Generate control Reports to verify the Uploaded and POS Cash Sales
Transitions comparing the Sales, Cost and margin

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 14
 Doc Ref: UCS/JCCS/MD50- POS Interface

Business Rules

All POS Al-Amin system Retail Cash Sales has to be interfaced with Oracle E-
Business suite to synchronize remote location inventory in Oracle for centralized
reporting and control.

This will be done on a regular on a daily basis in the day end after business hours.

The Oracle standard Interface, Order Cycle and Costing rules will be utilized.

Assumptions

This design assumes that the following statement is true:

The POS data are imported on a periodic interval in batch mode the suggested
interval could be daily after the business hours.

UCS should establish connectivity to all POS locations to bring the POS sales data to
a centralized location, have control in the POS System, format and provide data in
the requested format.

UCS should make necessary modifications in the existing POS System and have
necessary control and set flag after extracting the data for interface.

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 15
 Doc Ref: UCS/JCCS/MD50- POS Interface

Open and Closed Issues for this Deliverable

Open Issues

ID Issue Resolution Responsibility Target Date Impact

Date

Closed Issues

ID Issue Resolution Responsibility Target Date Impact

Date

File Ref: 730854742.doc (v. 1.1 )

UCS Confidential - For internal use only Document Control 16