Oracle Forms

Hi,

i need the records to be displayed from bl_details,bl_voyage_details,bl_containers

bl_container table may or may not have records, if it has no records all records in bl_voyage_details related to bl_details should displayed

relationship between tables

bl_details - bl_voyage_details
bl_details.bdl_company=bl_voyage_details.bvd_company and bl_details.bdl_reference=bl_voyage_details.bvd_reference

bl_details - bl_containers
bl_details.bdl_company=bl_containers .bcr_company and bl_details.reference=bl_containers .bcr_reference

bl_voyage_details - bl_containers

bl_containers .bcr_company=bl_voyage_details.bvd_company and bl_voyage_details.reference=bl_containers .bcr_reference and
bl__voyage_details.bvd_serial=bl_containers .bcr_voy_parent


Select distinct bdl_mbol,bdl_number,bvd_voy,bvd_carrier,bvd_vessel,bvd_location,bcr_container,bcr_container_type,bdl_reference,bvd_serial,bcr_serial
from bl_voyage_details, bl_details , bl_containers
WHERE (bvd_company =:parameter.p_company)
AND (bvd_company=bdl_company)
and (bdl_company=bvd_company)
AND (bdl_reference=bvd_reference)
AND (bcr_company(+)=bdl_company)
and (bcr_reference(+)=bdl_reference)
and (bcr_voy_parent(+)=bvd_serial )
and (bcr_status(+)='N')


AND (bdl_status='O')
AND (bvd_status='O')
AND (bdl_type='I')
AND (bvd_type='I')
AND (
(:m_bl is null)
OR (
((bdl_mbol=:m_bl) and (bdl_job_number =:M_job))
or ((bdl_mbol=:m_bl)and(:M_job is null))
)
)
AND ((:bl is null) OR (bvd_reference=:m_ref))
AND ((:voy is null) OR (bvd_voy=:voy ))
AND ((:container is null) OR (bcr_container=:container) )
AND ((bvd_voy :voy_code)or(bvd_carrier :voy_carrier)or (bvd_vessel :voy_vessel ))
AND (bvd_location =:voy_location)
order by bdl_mbol,bdl_number,bvd_voy,bvd_carrier,bvd_vessel,bvd_location,bcr_container,bcr_container_type ;