Framework for Integrating Retail Organization with Marketplace for Cash Reconciliation

Framework for Integrating Retail Organization with Marketplace for Cash Reconciliation

Padmanaban Sanjeevi

Office Depot

Abstract:- Retail organizations sell their product portfolios mainly through their brick and mortar stores To increase their clientele, they started to offer their products via online portals. This approach would bring an increase in sales at a cost to the organization, due to the need for advertisements. To solve this issue, retail organizations have chosen to sell their products through global e-commerce marketplaces, such as Amazon, eBay, Rakuten and Google, enabling them to expand their range of customers thereby improving their sales revenue.

Any organization will face a challenge when integrating with each different marketplace because each marketplace will use a different data structure for capturing sales information.

Any organization that utilizes a marketplace must develop new programs to ensure successful integration.

This article provides the details on the framework that will be used for seamlessly for any kind of marketplace integration for the systematic reconciliation of sales transactions with the bank statements in cash management.

Keywords: Marketplace, sku, seller central

INTRODUCTION

For any organization which implements the marketplace, the following steps has to be followed.

• Catalog of products which would be available in marketplace for sale

• A Seller central which acts as mediator between the marketplace and the seller (organization which sells the product in marketplace)

• Mechanism to send the available inventory of products to the seller central, so that when the customer place the order, marketplace will check the seller central for the available quantity of the of product

• A seller central which sends the order details to the retail organization to process the orders and ship the products to the customer

• Marketplace would use their own payment processor or another vendor such as PayPal to process the payments and send the details to the bank

• Reconciliation report from Marketplace for sale order reconciliation

• Bank statements sent to retail organization for the reconciliation

  This article provides the details of the framework for the reconciliation of the Sales order transactions and bank statements from banks.

  Translations Setup

  Translation mappings have been created to capture the details for each marketplace.

  SETTLEMENT_PROCESSES

  This translation has been defined to capture information such as file type, filename, formatting, url location to download the file

  , target location where the file would be placed for further processing

  PROCESS_NAME	WALMART_MPL	RAKUTEN_MPL
  Filemap_Tranlsation	PRE_STG_WALMART	PRE_STG_RAKUTEN
  URL	https://marketplace.walmartapis.com/v3/report/ reconreport/reconFile?reportDate=	https://marketplace.rakutenapis.com/v1/report/reconreport/r econFile?reportDate=
  AccessKeyId
  SecretAccessKey
  AppName	XYZ Company	XYZ Company
  AppVersion	1	1
  ReportType	Wal~Wal	Rakuten~Rakuten
  InboundDirectory	$XXFIN_DATA/inbound/mpl	$XXFIN_DATA/inbound/mpl
  FileName	Walmart_recon_mmddyyyy.csv	Rakuten_recon_mmddyyyy.txt
  ArchiveDirectory	$XXFIN_DATA/archive/inbound	$XXFIN_DATA/archive/inbound
  FilePosition	0	0
  ProcessType	WALMART	RAKUTEN
  ProcessorID	WALMART	RAKUTEN
  Providertype	WALMART	RAKUTEN
  Card type	WALMART	RAKUTEN
  CutoffDAte
  Seperator	COMMA	TAB
  URL2	https://marketplace.walmartapis.com/v3/token	https://marketplace.rakutenapis.com/v1/token

  PRE_STG_MAPPING

  A translation has been defined for each marketplace to define the mapping for the data element to the predefined columns in a custom staging table (XX_CE_MARKETPLACE_PRE_STG).

  PRE_STG_WALMART

  This translation defines mapping for the Walmart sales reconciliation file

  PRE_STG_RAKUTEN

  This translation defines mapping for the Rakuten sales reconciliation file

  A database view is created based on the mapping from the custom staging table for each market place. The following is the view definition for the Walmart Marketplace.

  create or replace force editionable view xx_ce_walmart_pre_stg_v (rec_id, report_date, process_name, filename, file_type, request_id, process_flag, err_msg, settlement_id, walmart_order, walmart_order_line, walmart_po, walmart_po_line, partner_order, transaction_type, transaction_date_time, shipped_qty, partner_item_id, partner_gtin, partner_item_name, product_tax_code, shipping_tax_code, gift_wrap_tax_code, ship_to_state, ship_to_county, county_code, ship_to_city, zip_code, shipping_method, total_tender_customer, payable_to_partner, commission_from_sale, commission_rate, gross_sales_revenue, refunded_retail_sales, sales_refund_for_escaln, gross_shipping_revenue, gross_shipping_refunded, shipping_refund_for_escln, net_shipping_revenue, gross_fee_revenue, gross_fee_refunded, fee_refund_for_escalation, net_fee_revenue, gift_wrap_quantity, gross_gift_wrap_revenue, gross_gift_wrap_refunded, gift_wrap_refund_for_escln, net_gift_wrap_revenue, tax_on_sales_revenue, tax_on_shipping_revenue, tax_on_gift_wrap_revenue, tax_on_fee_revenue,

  effective_tax_rate, tax_on_refunded_sales, tax_on_shipping_refund, tax_on_gift_wrap_refund, tax_on_fee_refund, tax_on_sales_refund_for_escln, tax_shipping_refund_escln, tax_gift_wrap_refund_escln, tax_fee_refund_escln, total_net_tax_collected, adjustment_description, adjustment_code, original_item_price, original_commission_amount, spec_category, contract_category) as select rec_id, report_date, process_name, filename, file_type, request_id, process_flag, err_msg, settlement_id, attribute1 walmart_order, attribute2 walmart_order_line, attribute3 walmart_po, attribute4 walmart_po_line, attribute5 partner_order, attribute6 transaction_type, attribute7 transaction_date_time, attribute8 shipped_qty, attribute9 partner_item_id, attribute10 partner_gtin, attribute11 partner_item_name, attribute12 product_tax_code, attribute13 shipping_tax_code, attribute14 gift_wrap_tax_code, attribute15 ship_to_state, attribute16 ship_to_county, attribute17 county_code, attribute18 ship_to_city, attribute19 zip_code, attribute20 shipping_method, attribute21 total_tender_customer, attribute22 payable_to_partner, attribute23 commission_from_sale, attribute24 commission_rate, attribute25 gross_sales_revenue, attribute26 refunded_retail_sales, attribute27 sales_refund_for_escaln, attribute28 gross_shipping_revenue, attribute29 gross_shipping_refunded, attribute30 shipping_refund_for_escln, attribute31 net_shipping_revenue, attribute32 gross_fee_revenue, attribute33 gross_fee_refunded, attribute34 fee_refund_for_escalation, attribute35 net_fee_revenue, attribute36 gift_wrap_quantity, attribute37 gross_gift_wrap_revenue, attribute38 gross_gift_wrap_refunded, attribute39 gift_wrap_refund_for_escln, attribute40 net_gift_wrap_revenue, attribute41 tax_on_sales_revenue, attribute42 tax_on_shipping_revenue, attribute43 tax_on_gift_wrap_revenue, attribute44 tax_on_fee_revenue, attribute45 effective_tax_rate, attribute46 tax_on_refunded_sales, attribute47 tax_on_shipping_refund, attribute48 tax_on_gift_wrap_refund, attribute49 tax_on_fee_refund, attribute50 tax_on_sales_refund_for_escln, attribute51 tax_shipping_refund_escln, attribute52 tax_gift_wrap_refund_escln, attribute53 tax_fee_refund_escln, attribute54 total_net_tax_collected, attribute55 adjustment_description, attribute56 adjustment_code, attribute57 original_item_price, attribute58 original_commission_amount, attribute59 spec_category, attribute60 contract_category from xx_ce_marketplace_pre_stg where process_name ='WALMART_MPL' and file_type='WAL';

  With this framework, whenever a new marketplace is implemented, just a new mapping and the corresponding view need to be defined ather than the creation of new database objects and modification of the program for each marketplace.

  CE_MKTPLC_DL

  This translation defines mapping for the exceptions encountered during the processing of reconciliation files

  Each marketplace will have cut-off day for a week. An Unix shell script program will be scheduled to run on the cut-off date of the marketplace, which will get the details from this translation to download the file from the marketplace using web service and transfer the datafile to the target location.

  CM Marketplace load program will read the file and will use data element mapping from the translation PRE_STG_RAKUTEN and PRE_STG_WALMART to insert the data into a pre-stage custom table. Any exception encountered during the process will be captured and will send an notification to the support team and archive the file for further analysis.

  Framework Process Flow for capturing data for each marketplace to stage processor table

  Framework for the Reconciliation Process flow

  xx_ce_ebay_trx_dtl_stg

  xx_ce_ebay_ca_dtl_stg

  xx_ce_ebay_trx_dtl_stg

  xx_ce_ebay_ca_dtl_stg

  XX_CE_MPL_SETTLEMENT_HDR XX_CE_MPL_SETTLEMENT_DTL

  Error Yes

  Already Processed ?

  Process_Staging

  Process_Staging

  No

  Get distinct settlement_id,order_id,transaction_typ e

  Loop

  Rollback

  Yes

  Error ?

  Error & Rollback

  No orders

  Split Order ?

  (Multiple records No in ORDT)

  Update xx_ce_mpl_settlement_dtl(spli t_order,store_number,

  aops_order_number)

  Yes

  XX_CE_AJ B998

  XX_CE_AJ B998

  No

  Process_Data_998

  Process_Data_998

  Get distinct item_id for order

  (settlement_id,order_id,transaction_ty pe,merchant_order_item_id)

  Rollback

  Yes

  Error ?

  Get store_number and aops order number by joining oe_order_lijnes

  XX_CE_AJ B999

  XX_CE_AJ B999

  No Update

  Process_Data_999

  Process_Data_999

  xx_ce_mpl_settlement_dtl(split_ord

  er,store_number, aops_order_number)

  Rollback

  Yes

  Error ?

  No

  End

  CE Market Places Process Settlement Program will validate data from the stage processor table and transfers the validated data to a main reconciliation settlement tables xx_ce_mpl_settlement_hdr and xx_ce_mpl_settlement_dtl table.

  Program will get the order details, which includes any sales tax from the reconciliation data, and validate against the sale order to make sure that the order amount and reconciliation amounts are matching.

  All the fee transactions are captured from the reconciliation data and will be used for payment to the marketplace as fee for hosting the skus in their marketplace.

  For each order, sales transaction will be transferred to auto reconciliation table to reconcile against the bank statements.

  Bank statements for the payment details from marketplace will be processed in Cash Management and auto reconciliation will validate bank statement against the sales order transaction and bank statement will be set to reconciled.

  Journal Entries will be created and interfaced to GL for the reconciled bank statements. Exception Report

  An exception report will display all the exception encountered during the process of marketplace reconciliation file and will be sent to business users.

  Benefits

• Ability to implement new marketplace with minimum configurations

• Reduced manual processing and streamlined business processes

• Enhanced accuracy and consistency of data

• Increase in sales revenue

CONCLUSION

With this framework, integration of any retail organization with marketplace for cash reconciliation would be simple with minimum effort to setup the configurations. With this approach, time to implement new marketplace is drastically reduced as well as maintenance of the processes is minimal.

1. https://marketplace.walmart.com/

   REFERENCES

2. How to Sell on Rakuten: Step-by-Step Guide for Newbie Rakuten Sellers