Scenario :
User needs to create a saved search to show the Total Value and Average Cost of each item on Item Receipts with Landed Costs.
I. Identify Cost Categories - Landed Cost.
--- Navigate to Setup > Accounting > Accounting Lists.
--- Type: Cost Category
--- View records and list all cost categories with Cost Type of ‘Landed Cost’.
Example: Freight and Insurance
II.
1. Navigate to Lists > Search > Saved Searches > New > Transaction.
2. Under Criteria tab > Standard sub tab, add the following fields:
--- Type | is Item Receipt
--- Account Type | is Other Current Asset
--- Item : Track Landed Cost | is True
3. Under Results tab > Columns sub tab, add the following fields:
--- Date | Maximum
--- Type | Group
--- Document Number | Group
--- Item | Group
--- Amount | Maximum
--- Formula (Currency) | Sum | Label: Sum of Landed Cost Allocated per Item
Formula: CASE WHEN ({memo} LIKE '%Insurance%') AND ({item} = {item.name}) THEN {amount} else 0 END+CASE WHEN ({memo} LIKE '%Freight%') AND ({item} = {item.name}) THEN {amount} else 0 END
--- Formula (Currency) | Sum | Label: Total Value per Item
Formula: max({amount})+sum(CASE WHEN ({memo} LIKE '%Insurance%') THEN {amount} else 0 END+CASE WHEN ({memo} LIKE '%Freight%') THEN {amount} else 0 END)
--- Quantity | Maximum
--- Formula (Currency) | Sum | Label: Average Cost per Item
Formula: max({amount})+sum(CASE WHEN ({memo} LIKE '%Insurance%') THEN {amount} else 0 END+CASE WHEN ({memo} LIKE '%Freight%') THEN {amount} else 0 END)/max(nvl({quantity},0))
4. Rename search.
5. Hit Save & Run.
This can be used as an alternate solution for Enhancement 183491.