SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //Dev02/Maroon/R2_1/Reports/SQL/Views/JobInformation.sql $
*    $DateTime: 2009/07/23 13:16:18 $
*    $Change: 26601 $
*    $Revision: #1 $
*    $Author: ssaad $
*    
*    Description: This view returns the job and related store information
*/

CREATE view [dbo].[JobInformation]
(
    OrderFID,
    StoreName,
    StoreContact,
    StorePhoneNumber,
    StoreFaxNumber
)
as

select
    Jobs.OrdersPriKey,
    Stores.StoreName,
    Stores.StoreContact,
    dbo.GetFormattedPhoneNumber
    (
        JobPhoneNumber.CountryCodeStandardFID,
        JobPhoneNumber.AreaCode,
        JobPhoneNumber.LocalNumber
    ) as StorePhoneNumber,
    dbo.GetFormattedPhoneNumber
    (
        StorePhoneNumber.CountryCodeStandardFID,
        StorePhoneNumber.AreaCode,
        StorePhoneNumber.LocalNumber
    ) as StoreFaxNumber
from Jobs
    left outer join JobPhoneType on JobPhoneType.TypeName = 'Phone'
    left outer join JobPhoneNumber on
    (
        Jobs.JobsID = JobPhoneNumber.JobsFID and
        JobPhoneNumber.JobPhoneTypeFID  = JobPhoneType.JobPhoneTypeID
    )
    left outer join Stores on Jobs.StoresID = Stores.StoreID
    left outer join StorePhoneType on StorePhoneType.TypeName = 'Fax'
    left outer join StorePhoneNumber on
    (
        Stores.StoreID = StorePhoneNumber.StoreFID and
        StorePhoneNumber.StorePhoneTypeFID = StorePhoneType.StorePhoneTypeID
    )

GO
GRANT SELECT ON  [dbo].[JobInformation] TO [MssExec]
GRANT INSERT ON  [dbo].[JobInformation] TO [MssExec]
GRANT DELETE ON  [dbo].[JobInformation] TO [MssExec]
GRANT UPDATE ON  [dbo].[JobInformation] TO [MssExec]
GO