I'm trying to write an Oracle procedure to do the following and I'm having some trouble. Here's the problem:

I have a table that contains an item number and a location number. I want to take each of the item numbers and look in another item cross reference table to see if it exists. If so, I want to update the original item number with the new one. I need to do this same thing for the location numbers, which have another separate cross reference table. If either the item or location numbers do not exist in the cross reference tables, update a column in the original table with a status of FAIL, or something like that.

If anyone can give me some advice, I'd be forever grateful.