Wednesday, December 12, 2007

Inland Arrival Communication Preferences

Here's the query for communication preferences logic. The key is lt_serial = 0042


select distinct contact.pd_last_name, contact.pd_first_name, contact.pd_own_email, communicationPrefs.lt_serial,
communicationPrefs.lk_custno_1, communicationPrefs.lk_custno_2, communicationPrefs.cp_doc_type_id,
CommunicationPrefsDocType.doc_type_name, CommunicationPrefsDocType.doc_type_ref_id, CommunicationPrefs.cp_form_type_id,
CommunicationPrefsFormType.form_type_name, CommunicationPrefsFormType.form_type_ref_id, CommunicationPrefs.cp_role_id,
CommunicationPrefsRole.cp_role_name, CommunicationPrefsRole.cp_role_ref_id, CommunicationPrefsRoleDocType.fax_type
from ACInlandAgedTable with(nolock) LEFT OUTER JOIN CommunicationPrefs with(nolock)
ON ACInlandAgedTable.CustomerNumber = CommunicationPrefs.lk_custno_1
INNER JOIN CommunicationPrefsDocType with(nolock)
ON CommunicationPrefs.cp_doc_type_id = CommunicationPrefsDocType.doc_type_id
INNER JOIN CommunicationPrefsFormType with(nolock)
ON CommunicationPrefs.cp_form_type_id = CommunicationPrefsFormType.form_type_id
INNER JOIN CommunicationPrefsRole with(nolock)
ON CommunicationPrefs.cp_role_id = CommunicationPrefsRole.cp_role_id
INNER JOIN CommunicationPrefsRoleDocType with(nolock)
ON CommunicationPrefs.cp_role_id = CommunicationPrefsRoleDocType.cp_role_id
AND CommunicationPrefs.cp_doc_type_id = CommunicationPrefsRoleDocType.doc_type_id
LEFT OUTER JOIN Contact with(nolock)
ON CommunicationPrefs.lk_custno_2 = Contact.custno
Where communicationPrefs.lk_custno_1 is not NULL
and Contact.custno is not NULL
and communicationPrefs.lt_serial = '0042'
and CommunicationPrefsRoleDocType.fax_type = 'IMPORT'
and communicationPrefs.cp_doc_type_id = 7
Order by CommunicationPrefs.lk_custno_1

No comments: