---------------------------------------------------
--
--                  VISMA AVENDO
--
-- Release 1.0	 
--
-- Created: 14/4-2008
-- Updated: 
--
---------------------------------------------------

CREATE VIEW [<TABLE_SCHEMA>].[vConsignor]
AS
SELECT
-- Customer
<TABLE_SCHEMA>Customer.CustomerNo AS CustomerNum_Customer,
<TABLE_SCHEMA>Customer.Name AS Name1_Customer,
NULL AS Name2_Customer,
<TABLE_SCHEMA>Customer.Address1 AS StreetAddress1_Customer,
<TABLE_SCHEMA>Customer.Address2 AS StreetAddress2_Customer,
NULL AS StreetAddress3_Customer,
<TABLE_SCHEMA>Customer.PostCode AS Postcode_Customer,
<TABLE_SCHEMA>Customer.PostOffice AS City_Customer,
NULL AS Mobile_Customer,
<TABLE_SCHEMA>Customer.Telephone AS Phone_Customer,
<TABLE_SCHEMA>Customer.EmailAddress AS Email_Customer,
<TABLE_SCHEMA>Customer.Telefax AS Fax_Customer,
NULL AS PostOfficeBox_Customer,
NULL AS PostOfficeBoxCity_Customer,
NULL AS PostOfficeBoxPostcode_Customer,
<TABLE_SCHEMA>Customer.CountryNo AS CountryName_Customer,
NULL AS CustomerOrigin_Customer,
NULL AS Attention_Customer,
NULL AS Contact_Customer,
NULL AS ReceiverRef_Customer,
NULL AS OurRef_Customer,
NULL AS MessageToCarrier_Customer,
NULL AS MessageToDriver_Customer,
NULL AS MessageToReceiver_Customer,
NULL AS PurchaseNo_Customer,
NULL AS ShipmentTypeNo_Customer,

-- Order CustomerOrder / CustomerOrderCopy
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.CustomerNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.CustomerNo
END AS CustomerNum_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.OrderNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.OrderCopyNo
END AS OrderNumber_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.CustomerName
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.CustomerName
END AS Name1_Order,
NULL AS Name2_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryAddress1
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryAddress1
END AS StreetAddress1_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryAddress2
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryAddress2
END AS StreetAddress2_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryAddress3
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryAddress3
END AS StreetAddress3_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryPostCode
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryPostCode
END AS Postcode_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryPostOffice
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryPostOffice
END AS City_Order,
NULL AS Mobile_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.Telephone
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.Telephone
END AS Phone_Order,
NULL AS Email_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.Telefax
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.Telefax
END AS Fax_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.InvoiceAddress1
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.InvoiceAddress1
END AS PostOfficeBox_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.InvoicePostOffice
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.InvoicePostOffice
END AS PostOfficeBoxCity_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.InvoicePostCode
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.InvoicePostCode
END AS PostOfficeBoxPostcode_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryCountryNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryCountryNo
END AS CountryName_Order ,
NULL AS CustomerOrigin_Order,
NULL AS Attention_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.NameContactNODelivery
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.NameContactNODelivery
END AS Contact_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.ReferenceID
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.ReferenceID
END AS ReceiverRef_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.OurRef
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.OurRef
END AS OurRef_Order,
NULL AS MessageToCarrier_Order,
NULL AS MessageToDriver_Order,
NULL AS MessageToReceiver_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.CustomerPurchaseNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.CustomerPurchaseNo
END AS PurchaseNo_Order,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.ShipmentTypeNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.ShipmentTypeNo
END AS ShipmentTypeNo_Order,

-- Delivery
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.CustomerNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.CustomerNo
END AS CustomerNum_Delivery,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryCustomerName
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryCustomerName
END AS Name1_Delivery,
NULL AS Name2_Delivery,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryAddress1
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryAddress1
END AS StreetAddress1_Delivery,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryAddress2
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryAddress2
END AS StreetAddress2_Delivery,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryAddress3
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryAddress3
END AS StreetAddress3_Delivery,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryPostCode
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryPostCode
END AS Postcode_Delivery,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryPostOffice
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryPostOffice
END AS City_Delivery,
NULL AS Mobile_Delivery,
NULL AS Phone_Delivery,
NULL AS Email_Delivery,
NULL AS Fax_Delivery,
NULL AS PostOfficeBox_Delivery,
NULL AS PostOfficeBoxCity_Delivery,
NULL AS PostOfficeBoxPostcode_Delivery,
NULL AS CountryName_Delivery,
NULL AS CustomerOrigin_Delivery,
NULL AS Attention_Delivery,
NULL AS Contact_Delivery,
NULL AS ReceiverRef_Delivery,
NULL AS OurRef_Delivery,
NULL AS MessageToCarrier_Delivery,
NULL AS MessageToDriver_Delivery,
NULL AS MessageToReceiver_Delivery,
NULL AS PurchaseNo_Delivery,
NULL AS ShipmentTypeNo_Delivery,

-- Invoice
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.CustomerNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.CustomerNo
END AS CustomerNum_Invoice,
NULL AS Name1_Invoice,
NULL AS Name2_Invoice,
NULL AS StreetAddress1_Invoice,
NULL AS StreetAddress2_Invoice,
NULL AS StreetAddress3_Invoice,
NULL AS Postcode_Invoice,
NULL AS City_Invoice,
NULL AS Mobile_Invoice,
NULL AS Phone_Invoice,
NULL AS Email_Invoice,
NULL AS Fax_Invoice,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.InvoiceAddress1
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.InvoiceAddress1
END AS PostOfficeBox_Invoice,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.InvoicePostOffice
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.InvoicePostOffice
END AS PostOfficeBoxCity_Invoice,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.InvoicePostCode
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.InvoicePostCode
END AS PostOfficeBoxPostcode_Invoice,
NULL AS CountryName_Invoice,
NULL AS CustomerOrigin_Invoice,
NULL AS Attention_Invoice,
NULL AS Contact_Invoice,
NULL AS ReceiverRef_Invoice,
NULL AS OurRef_Invoice,
NULL AS MessageToCarrier_Invoice,
NULL AS MessageToDriver_Invoice,
NULL AS MessageToReceiver_Invoice,
NULL AS PurchaseNo_Invoice,
NULL AS ShipmentTypeNo_Invoice,

-- Contact
<TABLE_SCHEMA>Contact.ContactNo AS ContactNum_Contact,
<TABLE_SCHEMA>Contact.Name AS Name1_Contact,
NULL AS Name2_Contact,
<TABLE_SCHEMA>Contact.Address1 AS StreetAddress1_Contact,
<TABLE_SCHEMA>Contact.Address2 AS StreetAddress2_Contact,
<TABLE_SCHEMA>Contact.Address3 AS StreetAddress3_Contact,
<TABLE_SCHEMA>Contact.PostCode AS Postcode_Contact,
<TABLE_SCHEMA>Contact.PostOffice AS City_Contact,
<TABLE_SCHEMA>Contact.MobileTelephone AS Mobile_Contact,
<TABLE_SCHEMA>Contact.InternalTelephone AS Phone_Contact,
<TABLE_SCHEMA>Contact.EmailAddress AS Email_Contact,
<TABLE_SCHEMA>Contact.Telefax AS Fax_Contact,
NULL AS PostOfficeBox_Contact,
NULL AS PostOfficeBoxCity_Contact,
NULL AS PostOfficeBoxPostcode_Contact,
NULL AS CountryName_Contact,
NULL AS CustomerOrigin_Contact,
NULL AS Attention_Contact,
NULL AS Contact_Contact,
NULL AS CustomerNum_Contact,


-- Package
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.NumberOfPackages
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.NumberOfPackages
END AS PackagesCount,
NULL AS PackagesMarking,
NULL AS PackagesContents,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.TotalWeight
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.TotalWeight
END AS PackagesWeight,
NULL AS PackagesHeight,
NULL AS PackagesLength,
NULL AS PackagesWidth ,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.TotalVolume
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.TotalVolume
END AS PackagesVolume,
NULL AS CODAccount,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.TotalVat + <TABLE_SCHEMA>CustomerOrder.TotalDelivery 
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.TotalVat + <TABLE_SCHEMA>CustomerOrderCopy.TotalDelivery
END AS CODAmount,
<TABLE_SCHEMA>CustomerOrderCopy.Kid AS CODKID,
NULL AS CODReference,
NULL AS InsuranceAmount,
NULL AS InsuranceCategory,
NULL AS InsurancePolicyNo,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.TermsOfDeliveryNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.TermsOfDeliveryNo
END AS DeliveryTerms,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DepNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DepNo
END AS Department,
<TABLE_SCHEMA>CustomerOrderCopy.InvoiceNo AS InvoiceNumber,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.TermsOfPayCustNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.TermsOfPayCustNo
END AS PaymentTerms,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.PaymentTypeNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.PaymentTypeNo
END AS PaymentType,
NULL AS ProjectName,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.ProjectNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.ProjectNo
END AS ProjectNumber,
CASE 
  WHEN (<TABLE_SCHEMA>CustomerOrder.OrderNo IS NOT NULL) THEN <TABLE_SCHEMA>CustomerOrder.DeliveryMethodsNo
  ELSE <TABLE_SCHEMA>CustomerOrderCopy.DeliveryMethodsNo
END AS CarrierCode,
<TABLE_SCHEMA>CustomerOrder.OrderNo AS SearchKeyOrder1,
<TABLE_SCHEMA>CustomerOrderCopy.OrderCopyNo AS SearchKeyOrder2,
<TABLE_SCHEMA>CustomerOrderCopy.InvoiceNo AS SearchKeyInvoiceNum,
<TABLE_SCHEMA>Customer.CustomerNo AS SearchKeyCustomerNum,
NULL AS SearchKeyContactNum,
NULL AS Link_Table1,
<TABLE_SCHEMA>CustomerOrderCopy.InvoiceNo AS Link_Table2,
<TABLE_SCHEMA>CustomerOrderCopy.ContactNoInvoice AS Link_Table3,
<TABLE_SCHEMA>CustomerOrder.ContactNoInvoice AS Link_Table4,
NULL AS Link_Table5,
NULL AS Link_Table6,
NULL AS Link_Table7,
NULL AS Link_Table8,
NULL AS Link_Table9,
NULL AS Link_Table10
FROM <TABLE_SCHEMA>Customer
  LEFT OUTER JOIN  <TABLE_SCHEMA>CustomerOrder  
    ON <TABLE_SCHEMA>Customer.CustomerNo = <TABLE_SCHEMA>CustomerOrder.CustomerNo
  LEFT OUTER JOIN  <TABLE_SCHEMA>CustomerOrderCopy   
    ON <TABLE_SCHEMA>Customer.CustomerNo = <TABLE_SCHEMA>CustomerOrderCopy.CustomerNo
  LEFT OUTER JOIN  <TABLE_SCHEMA>Contact  
    ON <TABLE_SCHEMA>Customer.CustomerNo = <TABLE_SCHEMA>Contact.CustomerNo
GO