-- SQL Query To Find Order Header Contact Details SELECT doav.ADDRESS_USE_TYPE " " , dha.source_order_number , dha.header_id , hzporg.party_name , hzp.person_first_name , hzp.person_last_name , hzp.email_address , hzr.relationship_code , hzporg.party_id , hzp.party_id , DHA.SOLD_TO_PARTY_CONTACT_ID , SUBJECT_ID , OBJECT_ID , hzr.relationship_id FROM fusion.hz_relationships hzr , fusion.hz_parties hzp , fusion.hz_parties hzporg , fusion.doo_order_addresses_V doav , fusion.doo_headers_all dha WHERE hzr.object_id = hzp.party_id AND hzR.OBJECT_TYPE = 'PERSON' AND hzr.relationship_code = 'CONTACT' AND hzr.subject_id = hzporg.party_id AND hzr.relationship_id = doav.contact_id AND doav.header_id = dha.header_id AND doav.ADDRESS_USE_TYPE = 'SHIP_TO' AND dha.source_order_number IN (:SOURCE_ORDER_NUMBER) AND dha.submitted_flag ='Y' UNION SELECT 'Sold to Contact' " " , dha.source_order_number , dha.header_id , hzporg.party_name , hzp.person_first_name , hzp.person_last_name , hzp.email_address , hzr.relationship_code , hzporg.party_id , hzp.party_id , DHA.SOLD_TO_PARTY_CONTACT_ID , SUBJECT_ID , OBJECT_ID , hzr.relationship_id FROM Fusion.HZ_RELATIONSHIPS HZR , Fusion.Hz_parties hzp , fusion.hz_parties hzporg , Fusion.HZ_CUST_ACCOUNT_ROLES HCAR , fusion.doo_headers_all dha WHERE HZR.OBJECT_id = hzp.party_id AND hzR.OBJECT_TYPE = 'PERSON' AND hzr.relationship_code = 'CONTACT' AND HZR.RELATIONSHIP_ID = HCAR.RELATIONSHIP_ID AND HCAR.RELATIONSHIP_ID = DHA.SOLD_TO_PARTY_CONTACT_ID AND dha.source_order_number IN (:SOURCE_ORDER_NUMBER) AND dha.submitted_flag ='Y' AND hzr.subject_id = hzporg.party_id UNION SELECT doav.ADDRESS_USE_TYPE " " , dha.source_order_number , dha.header_id , hzporg.party_name , hzp.person_first_name , hzp.person_last_name , hzp.email_address , hzr.relationship_code , hzporg.party_id , hzp.party_id , DHA.SOLD_TO_PARTY_CONTACT_ID , SUBJECT_ID , OBJECT_ID , hzr.relationship_id FROM Fusion.HZ_RELATIONSHIPS HZR , Fusion.Hz_parties hzp , fusion.hz_parties hzporg , Fusion.HZ_CUST_ACCOUNT_ROLES HCAR , fusion.doo_order_addresses_V doav , fusion.doo_headers_all dha WHERE HZR.OBJECT_id = hzp.party_id AND hzR.OBJECT_TYPE = 'PERSON' AND hzr.relationship_code = 'CONTACT' AND HZR.RELATIONSHIP_ID = HCAR.RELATIONSHIP_ID AND HCAR.CUST_ACCOUNT_ROLE_ID = CUST_ACCOUNT_CONTACT_ID AND doav.header_id = dha.header_id AND doav.ADDRESS_USE_TYPE = 'BILL_TO' AND dha.source_order_number IN (:SOURCE_ORDER_NUMBER) AND dha.submitted_flag ='Y' AND hzr.subject_id = hzporg.party_id ORDER BY 2 , 1 -- This SQL will show all contact information for the Sold To contact on the header. SELECT dha.source_order_number , dha.order_number , dha.status_code , hzp.PARTY_NUMBER , hzp.PARTY_NAME , hzcp.PRIMARY_PER_PHONE_TYPE , hzcp.PHONE_TYPE , hzcp.TIMEZONE_CODE , hzcp.CONTACT_POINT_TYPE , hzcp.STATUS , hzcp.PRIMARY_FLAG , hzcp.EMAIL_ADDRESS , hzcp.PHONE_CALLING_CALENDAR , hzcp.LAST_CONTACT_DT_TIME , hzcp.PHONE_AREA_CODE , hzcp.PHONE_NUMBER , hzcp.PHONE_EXTENSION FROM fusion.doo_headers_all dha , fusion.hz_relationships hzr , fusion.hz_contact_points hzcp , fusion.hz_parties hzp WHERE dha.sold_to_party_contact_id = hzr.relationship_id (+) AND hzr.directional_flag (+) = 'F' AND hzr.subject_id = hzcp.owner_table_id (+) AND hzcp.OWNER_TABLE_ID = hzp.PARTY_id (+) AND ( dha.submitted_flag = 'Y' OR dha.status_code = 'DOO_DRAFT' ) AND dha.source_order_number IN (:SOURCE_ORDER_NUMBER)