Sale order export at component level

Objective: Sale order Exports to be available at component level

Current scenario: We fetch details on the basis of orderItemID from order_items table and populate columns accordingly

Requirement (with example): Options available for user to choose for “VIRTUAL KIT COMPONENTS” or “NON_VIRTUALKIT COMPONENTS”

Logic to be Implemented:

There are some of the implementations required in multiple projects:

esb-client-portal-jobs

We need to create a new API for the sale order report at component level

Api name - /api/esb/v2/saleOrderExportJob/virtualKit
Class - ExportJobControllerV2.java
Project name - esb-client-portal-jobs
Job type - sale_order_report_virtual_kit_v2

Note - The code implementation will be quite similar to existing API /api/esb/v2/saleOrderExportJob but some changes and conditions need to be implemented.

Inside this new API we need to create a new method createSaleleOrderExportJobForComponent in ExportJobServiceV2.java
This method will be similar to saleOrderExportJob , here we need to change getOrderRecordsForComponent method similar to existing getOrderRecords method.
Need to add order_item_component_pk variable in OrderPaymentExportDto.
Inside getOrderRecordsForComponent we will prepare query on basis of order_item_components. Here is the sample query present as under:

Note -

Invoice total, shippingCharges, giftWrapCharges and CODCharges for order_item_components are divided in the ratio as under:

Ratio = (order_item_component base mrp)/ (Sum of all order_item_component base mrp mapped with same order_item id)

virtual kit level data

order_item_id_pk	mrp	invoiceTotal	shippingCharges	giftWrapCharges	CodCharges

order_item_id_pk	mrp	invoiceTotal	shippingCharges	giftWrapCharges	CodCharges
	2000	100	0	0	0

components level data

order_item_component_id_pk	mrp	Ratio	invoice Total	shippingCharges	giftWrapCharges	CodCharges

order_item_component_id_pk

mrp

Ratio

invoice Total

shippingCharges

giftWrapCharges

CodCharges

660

230

230/(230+3000)
= 0.071

100*0.071

7.1

0*0.071

= 0

0*0.071

= 0

0*0.071

= 0

661

3000

3000/(230+3000)
= 0.928

100*0.928 =

92.87

0* 0.928

= 0

0* 0.928

= 0

0* 0.928

= 0

SUM = 100

SUM = 0

Here we see that inside component table total sum of data of particular column is equal to column of virtual kit table.

## order_item_components query
SELECT
    orders.id AS order_id,
    order_items.id AS orderItemId_pk,
    order_item_components.id AS orderItemComponentId_pk,
    orders.channel_id AS channel_id,
    order_items.shipment_id AS shipment_id,
    channels.portal_id AS portal_id,
    channels.externalchannelid AS external_channel_id,
    orders.vendorordernumber AS orderId,
    orders.customerordernumber AS customerOrderNumber,
    orders.ordersiteid AS orderSiteID,
    shipments.couriername AS forwardShippingProvider,
    return_shipments.couriername AS reverseShippingProvider,
    IF(order_items.isVirtualKit = '1', CONCAT(order_items.id, '-', order_item_components.id), order_items.id) AS productAddtionalInfoMapKey,
    IF(orders.shipping_addressline2 IS NOT NULL, CONCAT(orders.shipping_addressline1, ' ', orders.shipping_addressline2), orders.shipping_addressline1) AS shippingAddress,
    orders.shipping_email AS shippingEmailId,
    order_items.expectedshipdate,
    order_items.brandaccountid AS brandAccountId,
    orders.riskscore AS riskScore,
    orders.riskscorereasons AS riskScoreReasons,
    orders.shipping_customername AS shippingCustomerName,
    orders.shipping_contactphone AS shippingContactPhone,
    orders.shipping_postalcode AS shippingPostalCode,
    orders.shipping_city AS shippingCity,
    orders.shipping_state AS shippingState,
    orders.shipping_countryname AS shippingCountry,
    IF(orders.billing_addressline2 IS NOT NULL, CONCAT(orders.billing_addressline1, ' ', orders.billing_addressline2), orders.billing_addressline1) AS billingAddress,
    orders.billing_contactphone AS billingContactPhone,
    orders.billing_postalcode AS billingPostalCode,
    orders.billing_email AS billingEmailId,
    orders.billing_customername AS billingCustomerName,
    orders.billing_city AS billingCity,
    orders.billing_state AS billingState,
    orders.billing_countryname AS billingCountry,
    order_items.onhold AS onHold,
    IF(
    order_items.isVirtualKit = '1', 
    order_items.invoiceTotal *(
      order_item_components.mrp / (
      SELECT SUM(order_item_components.mrp) 
      FROM order_item_components 
      WHERE order_item_components.order_items_id = order_items.id
      ) 
    ), 
    order_items.invoiceTotal
  ) AS invoiceTotal, 
  IF(
    order_items.isVirtualKit = '1', 
    order_items.shippingCharges *(
      order_item_components.mrp / (
      SELECT SUM(order_item_components.mrp) 
      FROM order_item_components 
      WHERE order_item_components.order_items_id = order_items.id
      ) 
    ), 
    order_items.shippingCharges
  ) AS shippingCharges, 
      order_items.sku,
    accounts.account_slug AS accountName,
    accounts.id AS accountId,
    order_items.orderitemid AS orderitemid,
    orders.iscod,
    orders.origin,
    shipments.region,
    shipments.ismetro AS isMetro,
    shipments.isspecialplace AS isSpecial,
    order_items.warehousereversepickupcreated,
    order_items.warehousereversepickuplastupdated,
    order_items.warehouseinboundstatus,
    order_items.returnflag,
    order_items.return_shipment_id,
    order_items.returnreason,
    order_items.customerorderitemid,
    IF(order_items.isVirtualKit = '1', order_item_components.reversepickupreason, order_items.reversepickupreason) AS reversepickupreason,
    return_shipments.customerreturnnumber,
    shipments.trackingid,
    order_items.isvirtualkit,
    IF(order_items.isVirtualKit = '1', order_item_components.qcrejectionreason, order_items.qcrejectionreason) AS qcrejectionreason,
    return_shipments.trackingid AS returnTrackingID,
    order_items.cancellationtime,
    orders.orderdate AS orderPlacedOn,
    orders.external_created_at,
    orders.created_at AS orderCreatedAt,
    order_items.created_at AS createdOnEshopbox,
    order_items.updated_at AS orderItemUpdatedAt,
    shipments.invoicedate,
    o.customerordernumber AS replaceOrderNumber,
    shipments.warehouse_id AS warehouseId,
    orders.channel_id AS channelId,
    order_items.lineitemorderstatus,
    order_items.status AS orderItemStatus,
    order_items.shipment_id AS shipmentId,
    shipments.external_wms_channel_id AS externalWmsChannelId,
    shipments.externalshipmentid,
    shipments.invoicenumber,
    shipments.invoice_url AS invoiceUrl,
    shipments.label_url AS labelUrl,
    shipments.dimension_length AS shipmentLength,
    shipments.dimension_width AS shipmentWidth,
    shipments.dimension_height AS shipmentHeight,
    shipments.weight AS shipmentWeight,
    order_items.original_order_item_id,
    order_items.expecteddeliverydate,
    order_items.expectedpickupdate,
    order_items.returnsubreason,
    return_shipments.isexchange AS isExchange,
    return_shipments.repaymenttype AS repaymentType,
    return_shipments.beneficiaryname AS beneficiaryName,
    return_shipments.beneficiaryifsccode AS beneficiaryIfscCode,
    return_shipments.beneficiaryaccountno AS beneficiaryAccountNo,
    return_shipments.beneficiaryaccounttype AS beneficiaryAccountType,
    return_shipments.exchangesize AS exchangeSize,
    order_items.resolutiontype AS returnResolution,
    IF(order_items.isVirtualKit = '1', order_item_components.productadditionalinfo, order_items.productadditionalinfo) AS productAdditionalInfo,
    IF(order_items.isVirtualKit = '1', order_item_components.tag, order_items.tag) AS tag,
    IF(
    order_items.isVirtualKit = '1', 
    order_items.`giftWrapCharges` *(
      order_item_components.mrp / (
      SELECT SUM(order_item_components.mrp) 
      FROM order_item_components 
      WHERE order_item_components.order_items_id = order_items.id
      ) 
    ), 
    order_items.`giftWrapCharges`
  ) AS giftWrapCharges, 
  IF(
    order_items.isVirtualKit = '1', 
    order_items.`cashOnDeliveryCharges` *(
      order_item_components.mrp / (
      SELECT SUM(order_item_components.mrp) 
      FROM order_item_components 
      WHERE order_item_components.order_items_id = order_items.id
      ) 
    ), 
    order_items.`cashOnDeliveryCharges`
  ) AS cashOnDeliveryCharges, 
    order_items.cancellationreason AS cancellationReason,
    order_items.remark AS additionalReason,
    order_items.packetnumber AS packetNumber,
    orders_unfulfillable.expectedshipdate AS unfulfillableExpectedShipDate,
    orders_unfulfillable.externalwarehouseid AS unfulfillableExternalWarehouseId,
    orders_unfulfillable.warehouse_id AS unfulfillableWarehouseId,
    shipment_unexpected.unexpecteditemqty AS sh_unexpectedItemsQuantity,
    shipment_unexpected.unexpecteditemskus AS sh_unexpectedItemSKUs,
    shipment_unexpected.qcrejectionreason AS sh_qcRejectionReason,
    return_shipment_unexpected.unexpecteditemqty AS rsh_unexpectedItemsQuantity,
    return_shipment_unexpected.unexpecteditemskus AS rsh_unexpectedItemSKUs,
    return_shipment_unexpected.qcrejectionreason AS rsh_qcRejectionReason,
    order_items.warehousereversepickupreason,
    order_items.warehouse_id AS orderItemsWarehouseId,
    return_shipments.type AS returnType,
    partyLocationCode.refpartyid AS locationCode,
    order_additional_details.receipt AS orderReceipt,
    orders_custom_fields.customfieldkey,
    orders_custom_fields.customfieldvalue,
    IF(orders.thirdpartyshipping = '0', 1, 0) AS esbShip,
    party.party_name AS partyName,
    IF(fsp.priority = '1', 'Priority', IF(fsp.express = '1', 'Express', 'Standard')) AS forwardShippingService,
    IF(rsp.priority = '1', 'Priority', IF(rsp.express = '1', 'Express', 'Standard')) AS reverseShippingService,
    IF(fsp.priority = '1', 'Priority', IF(fsp.express = '1', 'Express', 'Standard')) AS forwardShippingMode,
    orders.riskscore AS riskScore,
    orders.riskscorereasons AS riskScoreReasons,
    orders.pincodeserviceableremarks AS pincodeServiceableRemarks,
    orders.orderadditionalinfo AS orderAdditionalInfo,
    fsp.cp_id AS forwardCourierPartnerId,
    IF(order_items.returnflag = '1', IF(order_items.return_shipment_id <> 0, rsp.cp_id, fsp.cp_id), 0) AS returnCourierPartnerId,
    orders.balancedue AS balanceDue
FROM
    order_items
LEFT JOIN order_additional_details ON order_additional_details.order_id = order_items.order_id
LEFT JOIN order_items oi ON oi.id = order_items.original_order_item_id
LEFT JOIN order_item_components ON order_item_components.order_items_id = order_items.id
LEFT JOIN orders o ON o.id = oi.order_id
LEFT JOIN orders ON order_items.order_id = orders.id
LEFT JOIN orders_unfulfillable ON orders_unfulfillable.order_id = orders.id
    AND order_items.shipment_id = 0
LEFT JOIN channels ON channels.id = orders.channel_id
LEFT JOIN shipments ON shipments.id = order_items.shipment_id
LEFT JOIN external_wms_channels ON shipments.external_wms_channel_id = external_wms_channels.id
LEFT JOIN party ON external_wms_channels.billing_party_id = party.id
LEFT JOIN party AS partyLocationCode ON order_items.brandaccountid = partyLocationCode.account_id
    AND external_wms_channels.warehouse_id = partyLocationCode.warehouse_id
    AND partyLocationCode.type = 'billing'
LEFT JOIN return_shipments ON return_shipments.id = order_items.return_shipment_id
LEFT JOIN unexpected_order_items shipment_unexpected ON shipments.id = shipment_unexpected.shipment_id
LEFT JOIN unexpected_order_items return_shipment_unexpected ON return_shipments.id = return_shipment_unexpected.return_shipment_id
LEFT JOIN courier_partners fsp ON fsp.id = shipments.courier_partner_id
LEFT JOIN courier_partners rsp ON rsp.id = return_shipments.courier_partner_id
LEFT JOIN accounts ON accounts.id = order_items.brandaccountid
LEFT JOIN orders_custom_fields ON orders_custom_fields.shipment_id = order_items.shipment_id
LEFT JOIN (
    SELECT
        order_items.id,
        SUM(order_item_components.mrp) AS totalVirtualKitMrp
    FROM
        order_items
    LEFT JOIN order_item_components ON order_items.id = order_item_components.order_items_id
    WHERE
        order_items.isVirtualKit = '1'
) AS virtual_kit_mrp ON order_items.id = virtual_kit_mrp.id
WHERE 
  (
    order_items.brandaccountid = 28 
    AND order_items.id IN (30324554, 30324553, 30324552, 30324551, 30324550, 30324549, 30324548, 30324547, 30324546, 30324545, 30324544, 30324543, 30324542, 30324541, 30324540, 30324539, 30324538, 30324537, 30324536, 30324535, 30324534)
  ) 
  AND order_items.created_At >= NOW() - INTERVAL 220 DAY 
GROUP BY 
  order_items.id, 
  order_item_components.id 
LIMIT 
  100;

From getProductInfo , we need to remove calling product API and fetch product details from query in productAdditionalInfo. The query for fetching product data is as under:

SELECT 
order_items.id,
##channels.id AS channelId,
##accounts.id AS accountId,
##accounts.account_Slug AS accountSlug,
order_item_components.id AS orderItemComponentId, 
order_items.sku AS orderItemSku,
order_items.isVirtualKit,
order_item_components.esin AS orderItemComponentEsin,
IF(order_items.isVirtualKit='1', "VIRTUAL_COMBO","BASE") AS `type`,
IF(order_items.isVirtualKit='1', order_item_components.productAdditionalInfo,
order_items.productAdditionalInfo) AS productAdditionalInfo
FROM order_items
LEFT JOIN order_item_components 
ON order_items.id = order_item_components.order_items_id
LEFT JOIN accounts ON accounts.id = order_items.brandAccountId
WHERE 
 accounts.account_Slug="tab"
 AND
 order_items.id IN (30323711,30323475,23959610)
GROUP BY order_items.id, order_item_components.id
;

esb-notifications

Need to create a new task queue in /cloud-task-pusher of esb-notifications project

inventory-engine

Need to prepare cron for updating mrp and productAdditionalInfo in order_items and order_item_components table for last 1 year data, so that all mrp and product details should be displayed in export report.

cron API - api/esb/v1/productDetailCron
Class - ExportJobEndPoint.java
Project name - inventory-engine

Request body of productDetailCron is as under-
{ "entity": "order_item_components", "interval_days": 365 }
Here we can customize entity and interval_days for scheduler, entity can be either “order_item_components“ and “order_items“.
We need to create task queue from this cron and then need to fetch details using query

SELECT DISTINCT(esin) , productAdditionalInfo FROM order_item_components 
WHERE created_At >= NOW() - INTERVAL 365 DAY
AND mrp IS NULL ;

Then we need to update details using query as under:

UPDATE order_item_components SET mrp = 200 , 
productAdditionalInfo = "{\"tagloopRequired\":false,\"productDetails\":{\"unitPrice\":574.75,\"hsnCode\":\"62052090\",\"dimensionHeight\":2.54,\"dimensionLength\":38.350000000000001,\"dimensionUnit\":\"cm\",\"weight\":400,\"mrp\":2299,\"dimensionWidth\":29.210000000000001,\"type\":\"BASE\",\"verticalName\":\"Apparels\",\"imageUrl\":\"https:\\/\\/i.ibb.co\\/N3xMxWd\\/PMSX17136-B4-1.jpg\",\"accountSlug\":\"raymond\",\"sku\":\"PMSX17136-B481F040\",\"esin\":\"40LR03HF21Y\",\"brand\":\"Park Avenue\",\"additionalNames\":{\"ean\":\"8905573724846\",\"upc\":\"PMSX17136-B481F040\",\"others\":[]},\"groupCode\":\"PMSX17136-B4\",\"status\":\"ACTIVE\",\"weightUnit\":\"g\"}}"
WHERE esin IS NOT NULL AND mrp IS NOT NULL

Orders and Shipping Engine

Sale order export at component level

Logic to be Implemented:

Related content