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 Then get externalClientCode From external_wms_channel_account_mapping table | |
Order | Payment mode | COD | 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 | 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 | 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 | 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 | 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 |
| pending [tbd] |
Timeline | Is order ready to ship (RTS) SLA breached | Yes | 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 For e.g. 1 day, 4 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 | 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 | pending [tbd] |
Timeline | Number of delivery attempts done | 0 | 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 " |
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 | pending [tbd] |
Timeline | Is return pickup SLA breached | Yes | 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 | |
Status | Current Status | New | 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 | 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 | 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
For COD Refund
For Store credit refund
For Exchange | 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 | 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 | 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 | 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 | Pending [tbd] |
Cancellation | Cancellation reason | get order_items.`cancellationReason | |
Cancellation | Cancellation sub reason | order_items.cancellationSubReason is present +”,” + order_items.additionalRemarks | |
Cancellation | Cancel resolution | For prepaid order
In case of COD, there will be no resolution | getting |
Cancellation | Cancel resolution details | For Prepaid Refund
In case of COD, there will be no resolution details | Pending [tbd] |
Failed delivery | Order Failed to deliver on | Date(dd-mm-yy) | Date on Failed Delivery |
Failed delivery | Reason for failed delivery | Address Issue | ndr_status_description present in track_payload against latest failed_delivery status |
Failed Delivery | Courier partner remark for failed delivery | Courier partner remarks for failed delivery | shipment_status_logs.remark against status failed_delivery(latest) |
Failed delivery | Number of failed delivery attempts done | 1 | shipment_status_logs count of failed delivery against status failed_delivery |
Failed delivery | Failed delivery action status | Pending | if latest shipment status is failed_delivery then set value → pending if latest shipment status is ndr_resolution_submitted then set value → requested |
Failed delivery | Failed delivery action | Reattempt delivery requested for “date” | find latest failed_delivery log then check if ndr_resolution_submitted is present after latest failed_delivery if True get shipment_status_logs.remarks for ndr_resolution_submitted and check if remarks contains ACTF001,ACTF004 for reattempt ACTF002 → reschedulled ACTF003 → Delivery Cancelled |
Failed delivery | Failed delivery action taken by | Customer | Pending [tbd] |
Failed pickup | Return failed to pick on | Latest pickup_cancelled on date from return_shipment_status_logs | |
Failed pickup | Reason for failed pickup | npr_status_description present in track_payload against latest pickup_cancelled status | |
Failed pickup | Courier partner remark for failed pickup | return_shipment_status_logs.remarks for latest pickup_cancelled status | |
Failed pickup | Number of failed delivery attempts done | 1 | return_shipment_status_logs count of failed pickup against status pickup_cancelled |
Failed pickup | Failed pickup action status | Pending | Pending [tbd] |
Failed pickup | Failed pickup action | Reattempt pickup requested for “date” | Pending [tbd] |
Failed pickup | Failed pickup action taken by | Customer | Pending [tbd] |
/
New Exports Columns Implementation