Scenario:
Formula to return Quantity Back Ordered on Transfer Orders in Transaction Saved Search is working for most scenarios, but not all
Current formula is returning correct results using Case When conditions for specific Items and Transfer Order status and inventory location except when the Item has On Hand inventory which has been committed
Current formula:
CASE WHEN {item} IN ('NFBL001','NFBK001','NFBN001','NFPR001') and {type} = 'Transfer Order' and {status} IN ('Pending Fulfillment','Partially Fulfilled','Pending Receipt/Partially Fulfilled') and {item.inventorylocation} = 'XXXX' THEN ABS({quantity}+transferorderquantitycommitted}+nvl({transferorderquantityshipped},0)) WHEN {item} IN ('NFBLZP2','NFBKZP2','NFBNZP2','NFPRZP2') and {type} = 'Transfer Order' and {status} IN ('Pending Fulfillment','Partially Fulfilled','Pending Receipt/Partially Fulfilled') and {item.inventorylocation} = 'XXXX' THEN ABS(({quantity}+transferorderquantitycommitted}+nvl({transferorderquantityshipped},0))*2) WHEN {item} IN ('NFBLZP4','NFBKZP4','NFBNZP4','NFPRZP4') and {type} = 'Transfer Order' and {status} IN ('Pending Fulfillment','Partially Fulfilled','Pending Receipt/Partially Fulfilled') and {item.inventorylocation} = 'XXXX' THEN ABS(({quantity}+transferorderquantitycommitted}+nvl({transferorderquantityshipped},0))*4) else 0 END
Current Transfer Orders with Back Order quantities highlighted and summarized by Item in Excel file screenshot, Excel file is a download of Transfer Order transaction saved search without summary type (not grouped) and returns correct Back Order quantities
Formula in BOLD above does not return the Back Order Quantity for Item NFBKZP4 on TO11 circled in red (screenshot below) All other Back Order Quantities on TO10 and TO11 are returned by formula
Very much appreciate any suggestions, Thank You!
