YES! Have you heard of using dynamic view and sqltext in peoplecode? If you have not, then this maybe useful for you.
The cool thing about using a sqltext in peoplecode is to override the sql object in your view. First, you need to create a dynamic view that you will use as a prompt table. You may want to write a generic SQL in the view with all the fields you need to display in the search result.
Once you finished with creating dynamic view, then you can assign it as a prompt table edit. When the user click on the prompt table lookup from the page, it will display results from your generic SQL.
Now, in your peoplecode you can modify the generic SQL in the dynamic view by writing your own SQL with additional criteria using sqltext.
/* Update SQLText of the dynamic view */ RECORD_NAME.RECORD_FIELD.SqlText = "SELECT VAL1, VAL2 FROM PS_TABLE Where field_criteria = '" | &criteria_variable | "'";
1. Replace RECORD_NAME, RECORD_FIELD, VAL1, VAL2, PS_TABLE, field_criteria with your own
2. Assign criteria value to &criteria_variable
That's it, easy, huh!