dbo.v_data_assy_out2

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE VIEW dbo.v_data_assy_out2
AS
SELECT     dbo.tbl_data_out_dtl.assy_code, dbo.tbl_data_out_dtl.qty * ISNULL(dbo.tbl_data_out_dtl.pengali, 1) AS qty, dbo.tbl_data_out_dtl.out_date, dbo.tbl_data_out_hdr.id_terkait, dbo.tbl_data_out_hdr.doc_out_to_id, 
                         dbo.tbl_data_out_hdr.doc_out_no, dbo.tbl_data_out_hdr.doc_out_date, dbo.tbl_bc_out_hdr.CAR, dbo.tbl_bc_out_hdr.typedok
       ,(CASE WHEN doc_out_to_id = 3 AND CAR IS NULL THEN
                             (SELECT        tbl_sa_hdr.BC25NAJU
                               FROM            tbl_sa_hdr
                               WHERE        id_sa_hdr = tbl_data_out_hdr.id_terkait) ELSE tbl_bc_out_hdr.CAR END) AS CAR2
       , (CASE WHEN doc_out_to_id = 3 AND CAR IS NULL THEN
                             (SELECT        tbl_sa_hdr.typedok
                               FROM            tbl_sa_hdr
                               WHERE        id_sa_hdr = tbl_data_out_hdr.id_terkait) ELSE tbl_bc_out_hdr.typedok END) AS Typedok2
        ,dbo.tbl_bc_out_hdr.id_bc_out_hdr
        ,(CASE WHEN doc_out_to_id = 3 AND id_bc_out_hdr IS NULL THEN
                             (SELECT        tbl_sa_hdr.id_sa_hdr
                               FROM            tbl_sa_hdr
                               WHERE        id_sa_hdr = tbl_data_out_hdr.id_terkait) 
          ELSE tbl_bc_out_hdr.id_bc_out_hdr END) AS id_bc_out_hdr2
       ,(CASE WHEN doc_out_to_id = 3 AND id_bc_out_hdr IS NULL THEN
        (SELECT 
          noinv = STUFF(
            (SELECT '#' + nodok FROM [v_sa_inv_gab] where id_sa_hdr = (SELECT        tbl_sa_hdr.id_sa_hdr
                               FROM            tbl_sa_hdr
                               WHERE        id_sa_hdr = tbl_data_out_hdr.id_terkait) FOR XML PATH ('')), 1, 1, '')
             )  
         
          ELSE 
         (SELECT 
          noinv = STUFF(
            (SELECT '#' + nodok FROM [tbl_bc_out_dok] where [id_bc_out_hdr] = tbl_bc_out_hdr.id_bc_out_hdr and kdDok = '380'  FOR XML PATH ('')), 1, 1, '')
             )  
          
          END) AS no_inv
       
FROM            dbo.tbl_data_out_dtl INNER JOIN
                         dbo.tbl_data_out_hdr ON dbo.tbl_data_out_dtl.id_data_out_hdr = dbo.tbl_data_out_hdr.id_data_out_hdr LEFT OUTER JOIN
                         dbo.tbl_bc_out_hdr ON dbo.tbl_data_out_hdr.id_terkait = dbo.tbl_bc_out_hdr.id_bc_out_hdr
WHERE        (dbo.tbl_data_out_dtl.type_items = 'FG') AND (dbo.tbl_data_out_hdr.approve_date IS NOT NULL)      
GO

EXEC sys.sp_addextendedproperty N'MS_DiagramPane1', 
N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 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 = End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" 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 ', 'SCHEMA', N'dbo', 'VIEW', N'v_data_assy_out2'
GO

EXEC sys.sp_addextendedproperty N'MS_DiagramPaneCount', 1, 'SCHEMA', N'dbo', 'VIEW', N'v_data_assy_out2'
GO