- Open Access
- Authors : Padmanaban Sanjeevi
- Paper ID : IJERTV9IS100099
- Volume & Issue : Volume 09, Issue 10 (October 2020)
- Published (First Online): 13-10-2020
- ISSN (Online) : 2278-0181
- Publisher Name : IJERT
- License: This work is licensed under a Creative Commons Attribution 4.0 International License
Framework for Integrating Retail Organization with Marketplace for Cash Reconciliation
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
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.
Translation mappings have been created to capture the details for each marketplace.
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
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).
This translation defines mapping for the Walmart sales reconciliation file
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.
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
Already Processed ?
Get distinct settlement_id,order_id,transaction_typ e
Error & Rollback
Split Order ?
(Multiple records No in ORDT)
Update xx_ce_mpl_settlement_dtl(spli t_order,store_number,
Get distinct item_id for order
Get store_number and aops order number by joining oe_order_lijnes
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.
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
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.