New Exports Columns Implementation

Collection

Column Name

Values

Logic

Order 

Channel order ID

  fetch Customer Order Number from orders table

Order 

Customer order ID

The Customer order ID assigned to that order 

 fetch Vendor Order Number from orders table

Order 

Order item ID

The order item ID

 get id from orders _items table

Order

Shipment ID

The Shipment Id of a particular order

 get Shipment Id from shipment status logs

Order 

Invoice number

invoice number

Getting invoice Number Shipments Table  

get shipments.invoiceNumber

Order 

Sales channel

Getting channel_id From channels Table

Getting id From channels Table

Display Sales Channel as channels.channelLabel

Order 

Portal

Getting Portal Name From portals Table  

Getting channel_id Name From orders Table  

if(channel_id != 0 && channel_id != null )  

THEN

get portals.portal_name

Order 

Fulfilment center

Getting party_name from Party Table 

Getting warehouse_id from order_items Table 

fulfilmentCenterMap.get("facilityLabel")

Order 

Customer code

 gettting externalWmsChannelId

getting externalClientCode from external_wms_channel_account_mapping table
if (externalWmsChannelId != null && externalWmsChannelId != 0l)

Then

get externalClientCode From external_wms_channel_account_mapping table

Order 

Payment mode

COD
Prepaid

Getting isCOD from Shipments Table

Check wether payment is COD or Prepaid If orders.`isCOD

if 1 display cod otherwise Prepaid

Order 

Sold by

Getting party_name from Party Table 

Getting id from channels Table

Display Sold By as party.party_name

Order 

Shipment Zone

Local
Zonal
Metro
National
Remote Area

Getting region from Shipments Table

Getting isMetro from Shipments Table

Getting isSpecialplace from Shipments Table

if(region == “Local“)

THEN

Local

if(region == “Zonal“)

THEN

Zonal

if(region == “National“ && isMetro == 0)

THEN

Remote

if(region == “National“ && isMetro == 1)

THEN

Metro

Else

National

Order 

Replacement

Yes
No

Check wether the return shipment is  Exchange or not

return_shipments.isExchange AS isExchange

Order 

Replacement order item ID

Getting customerOrderNumber from orders Table

Getting original_order_item_id from order_items Table

Repalcement order item ID = orders .customerOrderNumber “- “ order_items .original_order_item_id

Order 

Sold as virtual kit

Yes
No

Check wether order is virtual kit or not 

get order_items.isVirtualKit from order_items Table

Order 

Virtual kit ESIN

  getting Virtual kit esin from Products api on the basis of product sku

Customer 

Shipping customer name

 get Shipping Customer Name from orders Table

Customer 

Shipping contact number

  get Shipping contact Number from orders Table

Customer 

Shipping email id

  get Shipping email id from orders Table

Customer 

Shipping address

  get Shipping Address from orders Table

Customer 

Shipping city

  get Shipping city from orders Table

Customer 

Shipping state

  get Shipping state from orders Table

Customer 

Shipping country

  get Shipping country from orders Table

Customer 

Billing customer Name

  get billing customer Name from orders Table

Customer 

Billing contact number

  get billing contact number from orders Table

Customer 

Billing email id

  get billing email id from orders Table

Customer 

Billing address

  get billing address from orders Table

Customer 

Billing pin code

  get billing pin code from orders Table

Customer 

Billing city

  get billing city from orders Table

Customer 

Billing state

  get billing state from orders Table

Customer 

Billing country

  get billing country from orders Table

Product

ESIN

Getting Product ESIN From Products API  

productInfoDto.getEsin()

Product

SKU

Getting Product SKU From Products API  

productInfoDto.getSku()

Product

EAN

Getting Product EAN From Products API  

productInfoDto.getEan()

Product

Product group code

Getting Product Group Code From Products API 

productInfoDto.getGroupCode()

Product

Title

Getting Product Description From Products API

 productInfoDto.getDescription()

Product

Brand

List of selling parties

Getting Product Brand Name From Products API 

productInfo.getBrand()

Product

Category

Category of the Order Item 

Getting Product Vertical Name From Products API

 productInfoDto.getVerticalName();

Product

Product weight

Getting Product Weight From Products API

productInfoDto.getWeight() / 1000

Product

HSN code

 Getting Product HSN code From Products API

get hsn From order_items Table

productInfoDto.getHsnCode()

Product

MRP

Getting Product mrp From Products API 

productInfoDto.getMrp()

Pricing

Invoice total

getting invoiceTotal from order_items Table

get order_items.invoiceTotal From order_items Table

Pricing

Selling price

Getting Product Details From Products API 

otherCharges = giftWrapCharges + codCharges;

get giftWrapCharges from order_items table with 2 decimal places

get cashOnDeliveryCharges from order_items with 2 decimal places

get invoiceTotal from above column

sellingPrice = dto.getInvoiceTotal() - dto.getShippingCharges() - otherCharges;

[upto 2 decimal places]

Pricing

Discount

Getting Product Details From Products API  

get selling Price from above column

get mrp from order_items with 2 decimal places

productInfoDto.getMrp()

discount = mrp - sellingPrice; [upto 2 decimal places]

Pricing

Discount percentage

Getting Product Details From Products API 

get discount logic from above column 

get mrp from order_items with 2 decimal places

productInfoDto.getMrp()

discountPercentage = (discount/mrp)*100; [upto 2 decimal places]

Pricing

Shipping charges

Getting Product Details on the basis of AccountId and product SKU From Products API 

get order_items.shippingCharges from order_items Table

Pricing

Other charges

Getting Product Details From Products API

get giftWrapCharges from order_items table with 2 decimal places

get cashOnDeliveryCharges from order_items with 2 decimal places 

otherCharges = giftWrapCharges + codCharges; [upto 2 decimal places]

Pricing

Tax amount

taxDetails.getTaxAmount()

Pricing

IGST rate

taxDetails.getIGST_Rate()

Pricing

IGST amount

taxDetails.getIGST_Amount()

Pricing

CGST rate

taxDetails.getCGST_Rate()

Pricing

CGST amount

taxDetails.getCGST_Amount()

Pricing

SGST rate

taxDetails.getSGST_Rate()

Pricing

SGST amount

 taxDetails.getSGST_Amount()

Pricing

UTGST rate

taxDetails.getUTGST_Rate()

Pricing

UTGST amount

taxDetails.getUTGST_Amount()

Shipping provider

Forward shipping provider

Getting Forward shipment courier Name from Shipments Table

get shipments.courierName From shipments Table

Shipping provider

Forward tracking ID

The Tracking ID assigned to that Order

Getting tracking ID from Shipments Table 

get tracking id from shipments.TrackingId

Shipping provider

Return shipping provider

Getting Return shipment courier Name from return_shipments Table  

get return_shipments.courierName

Shipping provider

Return tracking ID

Getting return tracking ID from return_shipments Table  

get Tracking id from return_shipments Table

Timeline

Order placed on channel

Date(dd-mm-yy)

Forward shipment courier Name

get orders.orderDate from orders table

Timeline

Order created in Eshopbox

 get order_items.created_at AS createdOnEshopbox

from order_items Table

Timeline

Order invoiced on

Date(dd-mm-yy)

get  shipments.invoiceDate from shipments table

Timeline

Expected order ready to ship on

Date(dd-mm-yy)

 get expectedShipDate From order_items

Timeline

Order ready to ship (RTS) on

Date(dd-mm-yy)

  getting packed Timestamp

Timeline

Time taken for order to ready to ship (RTS)

Date(dd-mm-yy)

getting created_at from order_items table

 difference between order_items.created_at AND packed TimeStamp

Timeline

Is order ready to ship on time

Yes
No

 Expected order ready to ship on Date > Order Ready to ship on (expected Ship Date) Date: Yes

else

No

Timeline

Reason for order ready to ship (RTS) delay

• {Unfulfillable reason}

• Order processing capacity exceeded at the fulfilment center

• Delay in order processing

• Order was on hold by seller

  pending [tbd]

Timeline

Is order ready to ship (RTS) SLA breached

Yes
No
SLA not applicable

  pending [tbd]

Timeline

Order handover done on

Date(dd-mm-yy)

Timeline

Expected order delivery date 

Date(dd-mm-yy)

Getting Expected Delivery Date from order_items Table

get order_items.expectedDeliveryDate

Timeline

Order delivered on

Date(dd-mm-yy)

Timeline

Time taken for order delivery 

Time Logic

→ If below 24 hrs then number hours must be displayed
→ If more than 24hr, then day and next set of hrs will be displayed.

For e.g. 1 day, 4 hrs
2 days, 3 hrs

Difference between  forwardDeliveredOn Timestamp and

fulfilledOn Timestamp

where fulfilledOn is ActualRTSOn and forwardDeliveredOn is forward shipment delivered date

if (forwardDeliveredOn > fulfilledOn )

int daysDiff = (int) (timeDiff / (1000 * 60 * 60 * 24))

Timeline

Is order delivered on time

Yes
No

 compare Deliverd Date Timestamp with Expected Delivery date

IF (Deliverd Date > Expected Delivery date )

THEN No

Else

Yes

Timeline

Is order delivery SLA breached

Yes
No
SLA not applicable

 pending [tbd]

Timeline

Number of delivery attempts done

0
1
2
3
4

This will be the out for delivery attempts  

forwardDeliveredOn.get("out_for_delivery_attempts")

Timeline

1st out for delivery attempt

Date(dd-mm-yy)

The first out for delivery attempt

Timeline

Latest out for delivery attempt

Date(dd-mm-yy)

The latest out for delivery attempt , ignoring the first attempt

Timeline

Order cancelled on

Date(dd-mm-yy)

 AND ( order_items.shipment_id IN ( :statusShipmentIds) OR "
(order_items.status = 'CANCELLED' AND order_items.shipment_id = 0 "
AND DATE(order_items.cancellationTime)>=(:cancelledOnStartRange) "
AND DATE(order_items.cancellationTime)<=(:cancelledOnEndRange)

Timeline

Return created on

Date(dd-mm-yy)

 AND (order_items.shipment_id IN (:statusShipmentIds) AND order_items.returnFlag = '1')

Timeline

Expected return pickup date

Date(dd-mm-yy)

  pending [tbd]

Timeline

Return picked on

Date(dd-mm-yy)

  pending [tbd]

Timeline

Number of attempt taken to pickup 

Date(dd-mm-yy)

 Pending [tbd]

Timeline

1st out for pickup attempt

Date(dd-mm-yy)

The first out for pickup attempt

Timeline

Latest out for pickup attempt

Date(dd-mm-yy)

The latest out for pickup attempt , ignoring the first attempt

Timeline

Is return pickup done on time

Yes
No

  pending [tbd]

Timeline

Is return pickup SLA breached

Yes
No
SLA not applicable

  pending [tbd]

Timeline

Return delivered on

Date(dd-mm-yy)

Getting Return Delivered On timestamp from return_shipments status _logs table where status = Delivered

Timeline

Return received on

Date(dd-mm-yy)

Scenario when return is received at the FC

order_items.shipment_id IN (:statusShipmentIds) AND order_items.returnFlag = '1'

Timeline

Return completed on

 order_items.warehouseInboundStatus IS/IS NOT
('COMPLETE') OR (order_items.shipment_id IN (:statusShipmentIds) AND order_items.status IN ('CANCELLED', 'REJECTED') )
OR (order_items.warehouseReversePickupReason IS/IS NOT
('lost') OR order_items.reversePickupReason IS/IS NOT
('lost')) ) );

Status

Current Status

New
Processing
Unfulfillable
On hold
Ready to ship
Handover done
On the way
Out for delivery
Delivered
Cancelled
Delivery cancelled
Order lost in transit
Awaiting pick up
Return on the way
Return in processing
Restocked
Quarantined
Return Lost in transit
Return lost in processing
Return Damaged

 Pending [tbd]

Status

Current status remark

 Pending [tbd]

Return 

Return ID

Getting Return Tracking Id from return_shipments table 

get return_shipments.`trackingID

Return 

Return type

Customer initiated return
Failed delivery return

 get warehouseInboundStatus from order_items Table

get return_shipment_id from order_items Table

get returnFlag from order_items Table

if(warehouseInboundStatus != null && return_shipment_id !=null && )

THEN Customer initiated return

if(warehouseInboundStatus != null && return_shipment_id !=null && returnFlag != 0)

THEN

Failed delivery return

Return 

Return reason

Getting Return reason from order_items table 

get order_items.`returnReason

Return 

Return sub reason

 Getting Return Sub reason from order_items table 

get order_items.`returnSubReason

Return 

Return resolution

Refund
Exchange

 Getting isExchange from return_shipments table 

if(isExchange.return_shipments == 0)

THEN

Refund

Else If(isExchange.return_shipments == 1)

THEN

Exchange

Return 

Return resolution details

For Prepaid Refund

• {Refund to the original payment mode}

For COD Refund

• Refund details: {Bank account details}

For Store credit refund

• {Refund as store credit}

For Exchange
-{New Variant details}

Getting repaymentType from return_shipments table 

return_shipments.repaymentType AS repaymentType

If(repaymentType is bank)

IF ( beneficiaryAccountType = savings or s )

then get Beneficiary bank Account details from refunds

If ( repaymentType is originalpaymentmode)

THEN print “Account Used in Payment”

 Getting isExchange from return_shipments table 

If ( repaymentType is storeCredits)

THEN print “Store Credits”

return_shipments.exchangeSize AS exchangeSize

Return 

Action taken on return item

Restocked
Quarantined

Getting reversePickupReason from order_items table 

if( reversePickupReason ==”good_inventory”)

THEN

Restocked

if(reversePickupReason ==”bad_inventory”)

THEN

Quarantined

else

BLANK

Return 

Return item condition

Item is Sellable
Item is Non Sellable
Item is missing

 WHERE order_items.returnFlag = '1' AND order_items.reversePickupReason IN ('good_inventory', 'bad_inventory', 'lost')

Return 

Return rejection reason

(Reason provided for rejection)

Getting qcRejectionReason from order_items table 

Return 

Incorrect return items in the shipment

Yes
No

Getting unexpectedItemQty from unexpected_order_items table  

return_shipment_unexpected.unexpectedItemQty AS rsh_unexpectedItemsQuantity

Return

Incorrect return items details

(Details/SKU of incorrect item)

 Getting unexpectedItemSKUs from unexpected_order_items table

Cancellation

Cancelled by

Customer
Seller

  Pending [tbd]

Cancellation

Cancellation reason

 get order_items.`cancellationReason

Cancellation

Cancellation sub reason

  Pending [tbd]

Cancellation

Cancel resolution

For prepaid order

• Refund

In case of COD, there will be no resolution

  getting

Cancellation

Cancel resolution details

For Prepaid Refund

• {Refund to the original payment mode}

In case of COD, there will be no resolution details

  Pending [tbd]

Failed delivery 

Order Failed to deliver on

Date(dd-mm-yy)

Pending [tbd]

Failed delivery 

Reason for failed delivery 

Address Issue
Customer Unavailable
Customer Unreachable
Customer Wants Open Delivery
Delivery Rescheduled
Exception
No Attempt
Out Of Delivery Area
Payment Issue
Rejected By Customer
Self Collect

Pending [tbd]

Failed Delivery 

Courier partner remark for failed delivery 

Courier partner remarks for failed delivery 

Pending [tbd]

Failed delivery 

Number of failed delivery attempts done

1
2
3
4

Pending [tbd]

Failed delivery 

Failed delivery action status

Pending
Requested

Pending [tbd]

Failed delivery 

Failed delivery action

Reattempt delivery requested for “date”
Reschedule delivery requested for “date”
Delivery cancelled

Pending [tbd]

Failed delivery

Failed delivery action taken by

Customer
Seller

 Pending [tbd]

Failed pickup

Return failed to pick on

 Pending [tbd]

Failed pickup

Reason for failed pickup

 Pending [tbd]

Failed pickup

Courier partner remark for failed pickup

 Pending [tbd]

Failed pickup

Number of failed delivery attempts done

1
2
3

 Pending [tbd]

Failed pickup

Failed pickup action status

Pending
Requested

 Pending [tbd]

Failed pickup

Failed pickup action

Reattempt pickup requested for “date”
Reschedule pickup requested for “date”
Pickup cancelled

 Pending [tbd]

Failed pickup 

Failed pickup action taken by

Customer
Seller

 Pending [tbd]