If you writing the below the "NOT IN" using SQL Code then you looking this problem. You can't see name instead of ID. This is big Problem User Level.
1. MAWB (Master Table Column)
====================
SELECT A.AIRBILL_NUMBER d, B.ID r
FROM AIRBILL_STOCK A, MAWB B
WHERE A.ID=B.AIRBILL_STOCK_ID
AND B.ID NOT IN (SELECT MAWB_ID FROM CONSOL_MST);
2. HAWB (Details Table Column)
====================
SELECT HAWB_NUMBER, ID FROM HAWB_MST
WHERE ID NOT IN (SELECT HAWB_MST_ID FROM CONSOL_DTL);
This SQL Query can't solve this problem
---------------------------------------------------------------------------------------------
You Can try below the SQL Code. Then Solve your Problem...
1. MAWB (Master Table Column)
====================
List of Values :
Type : Function Body returning SQL Query
Language : PL/SQL
Function Body returning SQL Query :
if :P46_ID is null then
return 'SELECT A.AIRBILL_NUMBER, B.ID FROM AIRBILL_STOCK A, MAWB B
WHERE A.ID=B.AIRBILL_STOCK_ID
AND B.COM_ID=:GCOM_ID
AND B.ID NOT IN (SELECT MAWB_ID FROM CONSOL_MST)';
else
return 'SELECT A.AIRBILL_NUMBER, B.ID FROM AIRBILL_STOCK A, MAWB B
WHERE A.ID=B.AIRBILL_STOCK_ID
AND B.COM_ID=:GCOM_ID';
end if;
2. HAWB (Details Table Column)
====================
select case when :P46_ID is not null then Name else HAWB_NUMBER end name , case when :P46_ID is not null then All_ID else id end ID
from
(SELECT HAWB_NUMBER, ID, null name , null All_ID FROM HAWB_MST
where com_id = :GCOM_ID
AND ID NOT IN
(SELECT A.HAWB_MST_ID FROM CONSOL_DTL A, HAWB_MST B WHERE A.HAWB_MST_ID=B.ID)
union
SELECT null name , null All_ID, HAWB_NUMBER, ID FROM HAWB_MST
where com_id = :GCOM_ID)
where case when :P46_ID is not null then Name else HAWB_NUMBER end is not null
----------------------------- OR ---------------------------------------------------
Item : P44_BOOKING_ID
Type : Select List /Popup LOV
SQL Query :
------------------------------------------------------------------------------