eos: stock balance logic Notes: 4th Nov 24 , 18th nov 24

18th nov 24 Logic

working: ----------------- file 2 DHLN 2024-03-24T00:00:00UTC+00 A461 3060 2030-01-01T00:00:00UTC+00 R463062 672 2030-02-2025T00:00:00UTC+00 Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity , , , , , , , , Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity A461,DHLN,2024,,11-18T11:41:56.245+01:00,2030,01,01,3060 R463062,DHLN,2024,,11-18T11:41:56.245+01:00,2030,02,20,672 file 3 DHLN 2024-03-24T00:00:00UTC+00 A461 3060 2030-01-01T00:00:00UTC+00 R463062 672 2030-02-2025T00:00:00UTC+00 Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity , , , , , , , , Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity A461,DHLN,2024,,11-18T11:53:26.25+01:00,2030,01,01,3060 R463062,DHLN,2024,,11-18T11:53:26.25+01:00,2030,02,20,672 -------------- input DHLN 2024-03-24T00:00:00UTC+00 A461 3060 2030-01-01T00:00:00UTC+00 R463062 672 2025-02-20T00:00:00UTC+00 ---------------------- Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity , , , , , , , , -------------- Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity A461,DHLN,2024,,11-04T15:26:20.58+01:00,2030,01,01,3060 R463062,DHLN,2024,,11-04T15:26:20.58+01:00,2025,02,20,672 -------------------------------------------------- file 2 DHLN 2024-03-24T00:00:00UTC+00 A461 3060 2030-01-01T00:00:00UTC+00 R463062 672 2030-02-2025T00:00:00UTC+00 Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity , DHLN, , , , , , , Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity A461,DHLN,,,,2030,01,01,3060 R463062,DHLN,,,,2030,02,20,672 ----------- file 3 DHLN 2024-03-24T00:00:00UTC+00 A461 3060 2030-01-03T00:00:00UTC+00 R463062 672 2030-01-02T00:00:00UTC+00 ----------- DHLN 2024-03-24T00:00:00UTC+00 A461 3060 2030-01-03T00:00:00UTC+00 R463062 672 2030-01-02T00:00:00UTC+00 Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity , DHLN, , , , , , , Product key,Site key,Current date year,Current date month,Current date day,Expiry date year,Expiry date month,Expiry date day,Quantity A461,DHLN,2030,01,01,3060 R463062,DHLN,2030,01,01,672 ------------------------------- Logic 3 stockBalance->companyCode Company code string yes skip stockBalance->sourceType stockBalance->source Location (plant code, shipping point...) where the goods are supplied from structure yes skip stockBalance->source->location Identification of the partner string yes Access the reference table of the given transformation to find the value Site key stockBalance->source->name1 Name of the partner string yes skip stockBalance->source->name2 Name of the partner string yes skip stockBalance->source->houseNumber House number string yes skip stockBalance->source->street1 Street string yes skip stockBalance->source->street2 skip stockBalance->source->postalCode ZIP code, Postal code string skip stockBalance->source->poBox PO Box string yes skip stockBalance->source->city City string yes skip stockBalance->source->state State string skip stockBalance->source->country Country string yes skip stockBalance->source->phone Phone number string yes skip stockBalance->source->globalLocationNumber Global location number string yes skip stockBalance->stockBalanceDate Date when the stock balance was issued in the WMS side (in the local timezone) string yes Add one day to the provided stock balance date and extract year from the result Current date year Add one day to the provided stock balance date and extract month from the result Current date month Add one day to the provided stock balance date and extract day from the result Current date day stockBalance->stockBalanceName Identification of the stock balance run string yes skip stockBalance->items List of Items structure yes skip stockBalance->items->item An item record array [1 to N] yes skip stockBalance->items->itemPosition Position of the item string yes skip stockBalance->items->stockBalanceItemtype category of the location for a stock balance item line string yes skip stockBalance->items->vendorLocation Vendor when stock is located at a vendor location string yes skip stockBalance->items->storageLocation Storage location string yes skip stockBalance->items->ssccNumber Serial Shipping Container Code (handling unit) string yes skip stockBalance->items->wmsQualityStatus Quality status of the pallet from a WMS perspective string yes skip stockBalance->items->qualityStatus Quality status of the pallet from a QFS perspetive (tbc)? string yes skip stockBalance->items->positiveRelease PHR Status of the pallet string yes skip stockBalance->items->positiveReleaseDate Modification date of PHR Status string yes skip stockBalance->items->materialReference Reference identifying the product string yes passthrough Product key stockBalance->items->batch Batch number in the Mars GBC format string yes skip stockBalance->items->quantityInAuom Quantity in an alternative unit of measure (base or not) string yes skip stockBalance->items->uomCode Sales unit of measure string yes skip stockBalance->items->quantityInBuom Quantity in base unit of measure string yes Sum quantityInBuom for all item records having identical materialReference and bestBeforeDate Quantity stockBalance->items->baseUom Base unit of measure string yes skip stockBalance->items->conversionFactorToBuom Conversion factor to convert quantity in sales UOM to base UOM string yes skip stockBalance->items->stockType Type of stock string yes skip stockBalance->items->batchAvailabilityDate Date when the batch should be ready in the warehouse to be picked string yes skip stockBalance->items->bestBeforeDate Shelf Life Expiration or Best-Before Date string yes Extract year from the provided Best-Before date Expiry date year Extract month from the provided Best-Before date Expiry date month Extract day from the provided Best-Before date Expiry date day skip stockBalance->items->productionDate Production Date of the batch string yes skip stockBalance->items->materialType Material Type string yes skip stockBalance->items->itemDescription Description of the item string yes skip stockBalance->items->localPalletId local pallet number - License Plate Number (LPN) or reference number used to identify the pallet instead of or in addition to SSCC - this field is only relevant for Agnostic site - it is available in the Experience API but not the core API string yes skip stockBalance->items->localReferenceNumber Local Lot Number or reference number used at the factory, if any - this field is only relevant for Agnostic site - it is available in the Experience API but not the core API string yes skip stockBalance->items->startProductionDate date when the production started (production goods receipt of the SSCC) string yes skip stockBalance->items->lastUpdateDate Timestamp when the quantity of product in the pallet was last updated in the WMS - this field is only relevant for Agnostic site - it is available in the Experience API but not the core API string yes skip ---------------------- routing logic: ai To handle the splitting of data from a single JSON structure into three separate output files based on specific criteria, we can outline a process involving conditional checks. The primary steps revolve around parsing the JSON, applying filtering conditions to segregate data, and finally generating the respective CSV files. Overview of the Process Input Parsing: Read the incoming JSON payload. Define Criteria: Set the criteria for determining whether items belong to the "Available Stock ON HAND", "Blocked Stock ON HAND", or "Non-Available Stock ON HAND" categories based on the required fields. Item Filtering: Iterate through the items, applying the criteria to filter them into the relevant categories. Prepare CSV Files: Create separate lists for each category and convert them into CSV format. File Naming: Generate filenames based on the given naming conventions with the appropriate date format and 3PL Prefix. Example Splitting Logic Below is a pseudo-code representation of how the logic might look: Input JSON Structure Assuming the input JSON structure is similar to what you provided: CopyReplit { "stockBalance": { "companyCode": " ", "items": { "item": [ { "itemPosition": " ", "stockBalanceItemtype": "storage location", "wmsQualityStatus": "", // Add some sample values "qualityStatus": "Available", // or Blocked, or Non-Available "positiveRelease": "Yes", // or No // ... other attributes... }, // ... potentially more items ... ] } } } Pseudo-code for File Splitting Logic CopyReplit import json import csv from datetime import datetime # Example input input_data = ''' {... } ''' # Your full JSON data here data = json.loads(input_data) current_date = datetime.now().strftime('%d%m%y') prefix = "3PL_Prefix" # Replace with actual 3PL Prefix logic # Initialize lists for each stock category available_stock = [] blocked_stock = [] non_available_stock = [] # Define your filtering criteria for item in data['stockBalance']['items']['item']: if item['stockBalanceItemtype'] == "storage location": if item['wmsQualityStatus'] == "Available" and item['qualityStatus'] == "Available" and item['positiveRelease'] == "Yes": available_stock.append(item) elif item['wmsQualityStatus'] == "Blocked" or item['qualityStatus'] == "Blocked": blocked_stock.append(item) else: non_available_stock.append(item) # Function to write to CSV def write_to_csv(items, filename): with open(filename, mode='w', newline='') as file: writer = csv.DictWriter(file, fieldnames=items[0].keys()) writer.writeheader() writer.writerows(items) # Generate CSV files based on the conditions if available_stock: write_to_csv(available_stock, f"rc_{prefix}_{current_date}_asoh.csv") if blocked_stock: write_to_csv(blocked_stock, f"rc_{prefix}_{current_date}_bs.csv") if non_available_stock: write_to_csv(non_available_stock, f"rc_{prefix}_{current_date}_nasoh.csv") Notes on the Code Logic Filtering: The logic checks the value of stockBalanceItemtype, wmsQualityStatus, qualityStatus, and positiveRelease to categorize each item. CSV Writing: A simple function write_to_csv is defined to handle writing items to CSV files. The field names are dynamically gathered from the item’s keys. File Naming: The naming convention for the files is strictly followed as per your specifications. Error Handling: In a production environment, ensure you handle errors such as empty lists or file writing issues gracefully. -------------------------------------------------- https://deepai.org/chat