Framework for Integrating Retail Organization with Marketplace for Cash Reconciliation

Download Full-Text PDF Cite this Publication

Text Only Version

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

Leave a Reply

Your email address will not be published. Required fields are marked *