Jira code: BLUEL-6 and BLUEL-8
Saved search to show the quantity fulfilled, quantity available from item record and quantity shipped per day of a particular item in different locations from a transaction type search. The multiple locations are given as static values in the formula. The sales order has both body location and line location which makes the saved search difficult also the summary restrictions need to be taken into consideration while creating a saved search. So in the formula field itself, we can give and summary type function also in the main summary type select filed can be set so by this way we can give more than one summary tye function to a single formula.
Criteria:
Main line= false
Type= sales order
Tax line= false
Shipping line= false
Cogs line= false
item= PA651201
location= chatham & Southwater
Inventory location= chatham & Southwater
Result:
We have corrected the issue in the formula in the following columns.
for “Southwater Shipped Per Day” you need to sum the number of items (PA651201) fulfilled in sales order during the previous quarter and divide it by 65.22. (Precondition in sales order form we consider the body “location” field value as “Southwater” also the transaction line item field location value as “Southwater”).
Formula: ROUND(CASE WHEN {location} = ‘Southwater’ THEN CASE WHEN {item.inventorylocation} = ‘Southwater’ THEN {quantityshiprecv}/65.22 ELSE 0 END ELSE 0 END,5).
-for “Southwater Available” we need to take the quantity available which is present in the “Southwater” location of the item (PA651201) from the item record.
Formula: CASE WHEN {item.inventorylocation} = ‘Southwater’ THEN NVL({item.locationquantityavailable},0) ELSE 0 END.
for “Southwater Stock in Days” we need to take the quantity available which is present in the “Southwater” location of the item (PA651201) from the item record then divide it by the sum the number of items (PA651201) fulfilled in sales order during the previous quarter and multiplied it by 65.22. (quantity available/(quantity fulfilled * 65.22))
Formula: ROUND(MAX(CASE WHEN {item.inventorylocation} = ‘Southwater’ THEN NVL({item.locationquantityavailable},0) ELSE 0 END) / NULLIF(SUM(ROUND(CASE WHEN {location} = ‘Southwater’ THEN CASE WHEN {item.inventorylocation} = ‘Southwater’ THEN {quantityshiprecv}*65.22 ELSE 0 END ELSE 0 END,5)), 0),5)
for “Southwater Stock in Days inc On Order” we need to take the sum of the quantity available and the quantity on order which is present in the “Southwater” location of the item (PA651201) from the item record then divide it by the sum of the number of items (PA651201) fulfilled in sales order during the previous quarter and multiplied by 65.22. (quantity available + quantity on order /(quantity fulfilled * 65.22))
Formula: ROUND(MAX(CASE WHEN {item.inventorylocation} = ‘Southwater’ THEN (NVL({item.locationquantityavailable},0) + NVL({item.locationquantityonorder},0)) ELSE 0 END ) / NULLIF(SUM(ROUND(CASE WHEN {location} = ‘Southwater’ THEN CASE WHEN {item.inventorylocation} = ‘Southwater’ THEN {quantityshiprecv}*65.22 ELSE 0 END ELSE 0 END,5)), 0),5)
for “Chatham Shipped Per Day” you need to sum the number of items (PA651201) fulfilled in sales order during the previous quarter and divide it by 65.22. (Precondition In sales order form we consider the body “location” field value as “Southwater” also the transaction line item field location value as “Southwater”)
Formula: ROUND(CASE WHEN {location} = ‘Chatham’ THEN CASE WHEN {item.inventorylocation} = ‘Chatham’ THEN {quantityshiprecv}/65.22 ELSE 0 END ELSE 0 END,5)
for “Chatham Available” we need to take the quantity available which is present in the “Southwater” location of the item (PA651201) from the item record.
Formula: CASE WHEN {item.inventorylocation} = ‘Chatham’ THEN NVL({item.locationquantityavailable},0) ELSE 0 END
for “Chatham Stock in Days” we need to take the quantity available which is present in the “Southwater” location of the item (PA651201) from the item record then divide it by the sum the number of items (PA651201) fulfilled in sales order during the previous quarter and multiplied it by 65.22. (quantity available/(quantity fulfilled * 65.22))
Formula: ROUND(MAX(CASE WHEN {item.inventorylocation} = ‘Chatham’ THEN NVL({item.locationquantityavailable},0) ELSE 0 END) / NULLIF(SUM(ROUND(CASE WHEN {location} = ‘Chatham’ THEN CASE WHEN {item.inventorylocation} = ‘Chatham’ THEN {quantityshiprecv}*65.22 ELSE 0 END ELSE 0 END,5)), 0),5)
for “Chatham Stock in Days inc On Order” we need to take the sum of the quantity available and the quantity on order which is present in the “Southwater” location of the item (PA651201) from the item record then divide it by the sum of the number of items (PA651201) fulfilled in sales order during the previous quarter and multiplied by 65.22. (quantity available + quantity on order /(quantity fulfilled * 65.22))
Formula: ROUND(MAX(CASE WHEN {item.inventorylocation} = ‘Chatham’ THEN (NVL({item.locationquantityavailable},0) + NVL({item.locationquantityonorder},0)) ELSE 0 END ) / NULLIF(SUM(ROUND(CASE WHEN {location} = ‘Chatham’ THEN CASE WHEN {item.inventorylocation} = ‘Chatham’ THEN {quantityshiprecv}*65.22 ELSE 0 END ELSE 0 END,5)), 0),5)
Limitation
For this saved search the location is given as static in the formula and the available filter is set for the Item name. So when an item is selected the saved search results will show the data from the location “Southwater” and “Chatham” only.