ÿþUSE [DW]

GO

/****** Object:  View [dbo].[v_PH_PINNACLE]    Script Date: 10/12/2006 21:03:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[v_PH_PINNACLE]

AS

SELECT     pinnacle.ID, emp.EDW_PERS_ID, emp.UIN, emp.PERS_CONFIDENTIALITY_IND, emp.PERS_LNAME, emp.PERS_FNAME, emp.PERS_MNAME, 

                      email.EMAIL_ADDR, pinnacle.Billing_Date, pinnacle.Charge_Code, pinnacle.CT, pinnacle.Service_Number, pinnacle.Subscriber_Number, 

                      pinnacle.Sub_Service_Number, pinnacle.User_Name, pinnacle.Chart, pinnacle.Fund, pinnacle.Org, pinnacle.Account, pinnacle.Program, 

                      pinnacle.Activity, pinnacle.Amount, pinnacle.Description, pinnacle.To_Number, pinnacle.To_City, pinnacle.To_State, pinnacle.Duration, 

                      pinnacle.Connect_Date, pinnacle.From_Number, pinnacle.From_City, pinnacle.From_State, pinnacle.Created_Date, pinnacle.Created_By, 

                      pinnacle.Modified_Date, pinnacle.Modified_By, pinnacle.timestamp, email.EMAIL_PREFERRED_IND, 

                      pinnacle.Chart + '-' + pinnacle.Fund + '-' + pinnacle.Org + '-' + pinnacle.Program AS cfop, FUND.FIN_FUND_TITLE, ORG.ORG_TITLE, 

                      ORG.CAMPUS_LEVEL_1_TITLE, ORG.COLL_LEVEL_3_TITLE, ORG.SCHOOL_SUB_COLL_LEVEL_4_TITLE, ORG.DEPT_LEVEL_5_TITLE, 

                      ACCT.FIN_ACCT_TITLE, ACCT.FIN_ACCT_LEVEL_1_CD, ACCT.FIN_ACCT_LEVEL_1_TITLE, ACCT.FIN_ACCT_LEVEL_2_CD, 

                      ACCT.FIN_ACCT_LEVEL_2_TITLE, ACCT.FIN_ACCT_LEVEL_3_CD, ACCT.FIN_ACCT_LEVEL_3_TITLE, ACCT.FIN_ACCT_CD_NORM_BAL, 

                      ACCT.FIN_ACCT_TYPE_CD, PGM.FIN_PGM_TITLE

FROM         dbo.T_FIN_FUND_CD_HIST AS FUND RIGHT OUTER JOIN

                      dbo.T_FIN_PGM_CD_HIST AS PGM RIGHT OUTER JOIN

                      dbo.PH_PINNACLE AS pinnacle LEFT OUTER JOIN

                      dbo.T_FIN_ACCT_CD_HIST AS ACCT ON pinnacle.Chart = ACCT.COA_CD AND pinnacle.Account = ACCT.FIN_ACCT_CD ON 

                      PGM.FIN_PGM_CD = pinnacle.Program AND PGM.COA_CD = pinnacle.Chart ON FUND.FIN_FUND_CD = pinnacle.Fund AND 

                      FUND.COA_CD = pinnacle.Chart LEFT OUTER JOIN

                      dbo.T_ORG_CD_HIST AS ORG ON pinnacle.Chart = ORG.COA_CD AND pinnacle.Org = ORG.ORG_CD LEFT OUTER JOIN

                      dbo.V_EMPEE_PERS_HIST_1 AS emp RIGHT OUTER JOIN

                      dbo.PH_PHONES AS phones LEFT OUTER JOIN

                      dbo.V_EMPEE_CAMPUS_EMAIL_ADDR AS email ON phones.EDW_pers_id = email.EDW_PERS_ID AND email.EMAIL_PREFERRED_IND = 'Y' ON 

                      emp.EDW_PERS_ID = phones.EDW_pers_id ON pinnacle.Service_Number = phones.phone



GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]

Begin DesignProperties = 

   Begin PaneConfigurations = 

      Begin PaneConfiguration = 0

         NumPanes = 4

         Configuration = "(H (1[26] 4[25] 2[26] 3) )"

      End

      Begin PaneConfiguration = 1

         NumPanes = 3

         Configuration = "(H (1 [50] 4 [25] 3))"

      End

      Begin PaneConfiguration = 2

         NumPanes = 3

         Configuration = "(H (1 [50] 2 [25] 3))"

      End

      Begin PaneConfiguration = 3

         NumPanes = 3

         Configuration = "(H (4 [30] 2 [40] 3))"

      End

      Begin PaneConfiguration = 4

         NumPanes = 2

         Configuration = "(H (1 [56] 3))"

      End

      Begin PaneConfiguration = 5

         NumPanes = 2

         Configuration = "(H (2 [66] 3))"

      End

      Begin PaneConfiguration = 6

         NumPanes = 2

         Configuration = "(H (4 [50] 3))"

      End

      Begin PaneConfiguration = 7

         NumPanes = 1

         Configuration = "(V (3))"

      End

      Begin PaneConfiguration = 8

         NumPanes = 3

         Configuration = "(H (1[56] 4[18] 2) )"

      End

      Begin PaneConfiguration = 9

         NumPanes = 2

         Configuration = "(H (1 [75] 4))"

      End

      Begin PaneConfiguration = 10

         NumPanes = 2

         Configuration = "(H (1[66] 2) )"

      End

      Begin PaneConfiguration = 11

         NumPanes = 2

         Configuration = "(H (4 [60] 2))"

      End

      Begin PaneConfiguration = 12

         NumPanes = 1

         Configuration = "(H (1) )"

      End

      Begin PaneConfiguration = 13

         NumPanes = 1

         Configuration = "(V (4))"

      End

      Begin PaneConfiguration = 14

         NumPanes = 1

         Configuration = "(V (2))"

      End

      ActivePaneConfig = 0

   End

   Begin DiagramPane = 

      Begin Origin = 

         Top = 0

         Left = 0

      End

      Begin Tables = 

         Begin Table = "pinnacle"

            Begin Extent = 

               Top = 6

               Left = 38

               Bottom = 206

               Right = 221

            End

            DisplayFlags = 280

            TopColumn = 5

         End

         Begin Table = "emp"

            Begin Extent = 

               Top = 6

               Left = 259

               Bottom = 121

               Right = 485

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "phones"

            Begin Extent = 

               Top = 6

               Left = 523

               Bottom = 121

               Right = 734

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "email"

            Begin Extent = 

               Top = 32

               Left = 819

               Bottom = 307

               Right = 1083

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "FUND"

            Begin Extent = 

               Top = 14

               Left = 644

               Bottom = 129

               Right = 897

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "PGM"

            Begin Extent = 

               Top = 116

               Left = 762

               Bottom = 231

               Right = 1022

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "ORG"

            Begin Extent = 

               Top = 213

               Left = 155

               Bottom = 328

               Right = 412

            End

            DisplayFlags = 280

            TopC' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_PH_PINNACLE'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'olumn = 0

         End

         Begin Table = "ACCT"

            Begin Extent = 

               Top = 125

               Left = 420

               Bottom = 240

               Right = 666

            End

            DisplayFlags = 280

            TopColumn = 0

         End

      End

   End

   Begin SQLPane = 

   End

   Begin DataPane = 

      Begin ParameterDefaults = ""

      End

      Begin ColumnWidths = 38

         Width = 284

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

      End

   End

   Begin CriteriaPane = 

      Begin ColumnWidths = 11

         Column = 1440

         Alias = 900

         Table = 1170

         Output = 720

         Append = 1400

         NewValue = 1170

         SortType = 1350

         SortOrder = 1410

         GroupBy = 1350

         Filter = 1350

         Or = 1350

         Or = 1350

         Or = 1350

      End

   End

End

' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_PH_PINNACLE'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_PH_PINNACLE'