FTP Integration

Objective: To store the following file types of data to the specified FTP Path

File	FTP path	File posting by	File to be read by
Inward consignment	/BENETTON/SAP_IFR_INDIA/ESB/IAP/IN/INWARD	Eshopbox	Benetton
Orders	/BENETTON/SAP_IFR_INDIA/ESB/IAP/IN/SORDER	Eshopbox	Benetton
Returns	/BENETTON/SAP_IFR_INDIA/ESB/IAP/IN/RORDER	Eshopbox	Benetton
Recall consignment	/BENETTON/SAP_IFR_INDIA/ESB/IAP/IN/RECALL	Eshopbox	Benetton
Create product	/BENETTON/SAP_IFR_INDIA/ESB/IAP/OUT/MATMAS	Benetton	Eshopbox
Create consignment	/BENETTON/SAP_IFR_INDIA/ESB/IAP/OUT/CONSIGN	Benetton	Eshopbox

There will be 4 crons to be made that will pick the specified type of FileType configuration data such as Account Slug, FTP Path, Username, Password, FTP IP Address, and Port.

SQL Query:

Table Used: accounts_ftp_config

SELECT accounts_ftp_config.id, accounts_ftp_config.accountSlug, accounts_ftp_config.fileType,
accounts_ftp_config.ftpUrl, accounts_ftp_config.port, accounts_ftp_config.username,
accounts_ftp_config.password
FROM accounts_ftp_config 
WHERE fileType = 'inward_consignment'

Result:

ID	accountSlug	fileType	ftpUrl	port	username	password
1	benetton	inward_consignment	mft.benettongroup.org	5522	esb_user1	VmMoFBXp
5	esh148	inward_consignment	esh148.com	9889	esh1	eshop

Similarly, we will do for other File types. and prepare Export API Request and Generate Access token to make Export API calls one by one for each accountSlug.

On the basis of File type, Export will be created and their respective CSV file will be stored onto the specified FTP Path.

JOB TYPE FOR EXPORT API:

inward_consignment: For Inward Consignment File
recall_consignment: For Recall Consignment File
sale_order_report: For Orders/Returns File

Follow the steps given below to export the required type of CSV file:

POST API:

https://benetton.myeshopbox.com/platform/api/v1/export-job

Request Body for Inward/Recall Consignment:

{
    "jobType": "inward_consignment",
    "columnsExport": {
        "Consignment details": [
            "Consignment number",
            "Consignment type",
            "Document number",
            "Consignment status",
            "Location (from)",
            "Location (to)",
            "Inward ID"
        ],
        "Product details": [
            "SKU",
            "ESIN",
            "EAN",
            "MRP",
            "Unit price",
            "Brand"
        ],
        "Timeline": [
            "Created on",
            "Scheduled for",
            "Delivered on",
            "Processing initiated on",
            "Inward on",
            "Completed on"
        ],
        "QC details": [
            "Ordered quantity",
            "Received quantity",
            "Accepted quantity",
            "Rejected quantity",
            "Pending quantity",
            "Shortage quantity",
            "Rejection reasons"
        ]
    },
    "exportFilters": {}
}

Request Body for Orders/Returns created within 30 days:

{
    "jobType": "sale_order_report",
    "columnsExport": {
        "Shipping provider": [
            "Forward shipping provider",
            "Forward tracking number",
            "Return shipping provider",
            "Return tracking number"
        ],
        "Cancellation": [
            "Cancellation type",
            "Cancellation reason",
            "Cancellation sub reason",
            "Cancel item resolution",
            "Cancel resolution details"
        ],
        "Customer": [
            "Shipping address",
            "Shipping email id",
            "Shipping customer name",
            "Shipping contact number",
            "Shipping pin code",
            "Shipping city",
            "Shipping state",
            "Shipping country",
            "Billing address",
            "Billing email id",
            "Billing customer Name",
            "Billing contact number",
            "Billing pin code",
            "Billing city",
            "Billing state",
            "Billing country"
        ],
        "Timeline": [
            "Placed on channel",
            "Created in Eshopbox",
            "Invoiced on",
            "Expected ready to ship(RTS)",
            "Fulfilled on",
            "Actual ready to ship(RTS) on",
            "Ready to ship (RTS) TAT",
            "Expected forward delivery date",
            "Forward delivered on",
            "Time taken to deliver",
            "Number of Attempts taken to deliver",
            "Forward delivery TAT",
            "Cancelled on",
            "Return created on",
            "Expected return pickup date",
            "Return picked on",
            "Return pickup TAT",
            "Return delivered on",
            "Return received on",
            "Return completed on"
        ],
        "Product": [
            "ESIN",
            "SKU",
            "EAN",
            "Product group code",
            "Title",
            "Brand",
            "Category",
            "Product weight",
            "HSN code",
            "MRP"
        ],
        "Fulfillment and tracking Status": [
            "Current fulfillment status",
            "Current forward tracking status",
            "Current forward tracking remark",
            "Current return tracking status",
            "Current return tracking remark"
        ],
        "Pricing": [
            "Invoice total",
            "Discount",
            "Discount percentage",
            "Shipping charges",
            "Other charges",
            "Selling price",
            "Tax amount",
            "IGST rate",
            "IGST amount",
            "CGST rate",
            "CGST amount",
            "SGST rate",
            "SGST amount",
            "UTGST rate",
            "UTGST amount"
        ],
        "Return": [
            "Return id",
            "Return type",
            "Return reason",
            "Return sub reason",
            "Return item resolution",
            "Return resolution details",
            "Return item action",
            "Return item condition",
            "Return rejection reason",
            "Incorrect return items in the shipment",
            "Need attention"
        ],
        "Order": [
            "Customer order id",
            "Channel order id",
            "Order item id",
            "Shipment id",
            "Invoice number",
            "Sales channel",
            "Portal",
            "Fulfilment center",
            "Customer code",
            "Payment method",
            "Sold by",
            "Zone",
            "Replacement",
            "Replacement for order item id",
            "Sold as a virtual kit",
            "Virtual kit ESIN"
        ]
    },
    "exportFilters": {
        "returnCreatedOn": {
            "value": [
                "2021-12-26",
                "2022-01-25"
            ],
            "condition": "is-between",
            "displayKey": "Return created on"
        },
        "orderStatus": {
            "value": [
                "EXPECTED",
                "Return in process",
                "COMPLETE"
            ],
            "condition": "is",
            "displayKey": "Current fulfillment status"
        },
        "orderReceivedOn": {
            "value": [
                "2021-07-25",
                "2022-01-25"
            ],
            "condition": "is-between",
            "displayKey": "Order Created in Eshopbox"
        }
    }
}

Response:

{
    "id": "75248",
    "jobType": "inward_consignment",
    "channelId": "0",
    "fileType": "CSV",
    "userName": "prateek.kaushik@eshopbox.com",
    "filePath": "",
    "warehouseCode": "",
    "status": "0",
    "userId": "879",
    "columnsExport": {
        "Consignment details": [
            "Consignment number",
            "Consignment type",
            "Document number",
            "Consignment status",
            "Location (from)",
            "Location (to)",
            "Inward ID"
        ],
        "Product details": [
            "SKU",
            "ESIN",
            "EAN",
            "MRP",
            "Unit price",
            "Brand"
        ],
        "Timeline": [
            "Created on",
            "Scheduled for",
            "Delivered on",
            "Processing initiated on",
            "Inward on",
            "Completed on"
        ],
        "QC details": [
            "Ordered quantity",
            "Received quantity",
            "Accepted quantity",
            "Rejected quantity",
            "Pending quantity",
            "Shortage quantity",
            "Rejection reasons"
        ]
    },
    "createdAt": "2022-01-25T18:14:11.000Z",
    "updatedAt": "2022-01-25T18:14:11.000Z",
    "isScheduledReport": "0"
}

Now to get the exported CSV link, call GET Export by ID API as shown below:

GET API:

https://benetton.myeshopbox.com/platform/api/v1/export-job/75248

Response:

{
    "columns": "eyJDb25zaWdubWVudCBkZXRhaWxzIjpbIkNvbnNpZ25tZW50IG51bWJlciIsIkNvbnNpZ25tZW50IHR5cGUiLCJEb2N1bWVudCBudW1iZXIiLCJDb25zaWdubWVudCBzdGF0dXMiLCJMb2NhdGlvbiAoZnJvbSkiLCJMb2NhdGlvbiAodG8pIiwiSW53YXJkIElEIl0sIlByb2R1Y3QgZGV0YWlscyI6WyJTS1UiLCJFU0lOIiwiRUFOIiwiTVJQIiwiVW5pdCBwcmljZSIsIkJyYW5kIl0sIlRpbWVsaW5lIjpbIkNyZWF0ZWQgb24iLCJTY2hlZHVsZWQgZm9yIiwiRGVsaXZlcmVkIG9uIiwiUHJvY2Vzc2luZyBpbml0aWF0ZWQgb24iLCJJbndhcmQgb24iLCJDb21wbGV0ZWQgb24iXSwiUUMgZGV0YWlscyI6WyJPcmRlcmVkIHF1YW50aXR5IiwiUmVjZWl2ZWQgcXVhbnRpdHkiLCJBY2NlcHRlZCBxdWFudGl0eSIsIlJlamVjdGVkIHF1YW50aXR5IiwiUGVuZGluZyBxdWFudGl0eSIsIlNob3J0YWdlIHF1YW50aXR5IiwiUmVqZWN0aW9uIHJlYXNvbnMiXX0=",
    "filePath": "/eshopbox-client-portal-prod.appspot.com/exportJobs/inward_consignment_220125124459159-4911.csv",
    "recordCount": "22669",
    "userName": "prateek.kaushik@eshopbox.com",
    "userId": "879",
    "warehouseCode": "",
    "createdAt": "2022-01-25T18:14:11.000Z",
    "accountId": "633",
    "reportDelivery": "",
    "dateRangeFilters": "",
    "id": "75248",
    "jobType": "inward_consignment",
    "channelId": "0",
    "fileType": "CSV",
    "status": "1",
    "updatedAt": "2022-01-25T18:15:01.000Z",
    "isScheduledReport": "0"
}

Once we have the CSV URL using which file can be downloaded, we need to store that file now onto the specified FTP Path.

Note: If the file is exported successfully then store the FileURL, FileURLGeneratedOn, Status('0' for failure and '1' for Successful)

INSERT INTO accounts_ftp_config_files(`fileUrl`, `fileURLGeneratedOn`, `status`) 
VALUES('/exportJobs/inward_consignment_220125124459159-4911.csv','2022-01-25 19:07:55','1')

Apps and Automations