dbo.v_coo_data

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE VIEW dbo.v_coo_data
AS
SELECT        dbo.tbl_coo_data.id_coo_data, dbo.tbl_coo_data_dtl.VENDOR, dbo.tbl_coo_data_dtl.INVOICE_NO AS invoice_no2, dbo.tbl_coo_data_dtl.CONTIANER_NO, CONVERT(varchar, CONVERT(date, 
                         dbo.tbl_coo_data_dtl.INVOICE_DATE)) AS INVOICE_DATE, CONVERT(varchar, CONVERT(date, dbo.tbl_coo_data_dtl.ETD_VEBDER)) AS ETD_VEBDER, CONVERT(varchar, CONVERT(date, 
                         dbo.tbl_coo_data_dtl.ETD_PORT)) AS ETD_PORT, CONVERT(varchar, CONVERT(date, dbo.tbl_coo_data_dtl.ETA_PORT)) AS ETA_PORT, CONVERT(varchar, CONVERT(date, dbo.tbl_coo_data_dtl.ETA_FACT)) 
                         AS ETA_FACT, dbo.tbl_coo_data_dtl.TRANSPORT_WAY, dbo.tbl_coo_data_dtl.CARA_BAYAR, dbo.tbl_coo_data_dtl.ONEROUS, dbo.tbl_coo_data_dtl.SHIP_FROM, dbo.tbl_coo_data_dtl.VIA, 
                         dbo.tbl_coo_data_dtl.SHIP_TO, dbo.tbl_coo_data_dtl.PAY_BY, dbo.tbl_coo_data_dtl.SEAL_NO, dbo.tbl_coo_data_dtl.SHIP_NAME, dbo.tbl_coo_data_dtl.SHIP_COMPANY, dbo.tbl_coo_data_dtl.ORDER_NO, 
                         dbo.tbl_coo_data_dtl.ITEM_NUMBER, dbo.tbl_coo_data_dtl.DESCRIPTION, CONVERT(float, dbo.tbl_coo_data_dtl.ARRIV_PLAN_NUMBER) AS ARRIV_PLAN_NUMBER, dbo.tbl_coo_data_dtl.UOM, CONVERT(float, 
                         dbo.tbl_coo_data_dtl.PRICE) AS PRICE2, dbo.tbl_coo_data_dtl.CURRENT_MONEY, dbo.tbl_coo_data_dtl.CTN_NO_PREFIX, dbo.tbl_coo_data_dtl.CTN_NO_FROM, dbo.tbl_coo_data_dtl.CTN_NO_TO, 
                         dbo.tbl_coo_data_dtl.CTN_QUANTITY, dbo.tbl_coo_data_dtl.PACKING_QTY, CONVERT(float, dbo.tbl_coo_data_dtl.NET_WEIGHT) AS NET_WEIGHT, CONVERT(float, dbo.tbl_coo_data_dtl.GROSS_WEIGHT) 
                         AS GROSS_WEIGHT, dbo.tbl_coo_data_dtl.WEIGHT_UOM, dbo.tbl_coo_data_dtl.VOLUME, dbo.tbl_coo_data_dtl.VOLUME_UOM, dbo.tbl_coo_data_dtl.BL_DATE, dbo.tbl_coo_data_dtl.BL_NO, 
                         dbo.tbl_coo_data_dtl.FREIGHT, dbo.tbl_coo_data_dtl.EURO_NO, dbo.tbl_coo_data_dtl.PALET_NO, dbo.tbl_coo_data_dtl.HS_CODE, dbo.tbl_coo_data_dtl.COUNTRY_OF, dbo.tbl_coo_data_dtl.AKHIR, 
                         dbo.tbl_coo_data.file_name, dbo.tbl_coo_data.status_inv, dbo.tbl_coo_data.type_bc, dbo.tbl_coo_data.car, dbo.tbl_coo_data.nobc, dbo.tbl_coo_data.tglbc, dbo.tbl_coo_data.nobl, dbo.tbl_coo_data.tgbl, 
                         dbo.tbl_coo_data.upload_by, dbo.tbl_coo_data.upload_date, dbo.tbl_coo_data_dtl.id_coo, dbo.v_data_part_hs.part_no, dbo.v_data_part_hs.part_name, dbo.v_data_part_hs.part_name_customs, 
                         dbo.v_data_part_hs.hs_code AS hskode, dbo.v_data_part_hs.kode_barang, dbo.tbl_data_supplier.supplier_name, dbo.tbl_data_supplier.supplier_address, dbo.tbl_data_supplier.subcode_as400, 
                         dbo.tbl_data_supplier.supplier_country,
                             (SELECT        TOP (1) kd_negara
                               FROM            dbo.tbl_coo_negara
                               WHERE        (kd_negara = dbo.tbl_coo_data_dtl.COUNTRY_OF)) AS kd_negara,
                             (SELECT        TOP (1) kd_negara
                               FROM            dbo.tbl_coo_negara AS tbl_coo_negara_1
                               WHERE        (country_code = dbo.tbl_coo_data_dtl.COUNTRY_OF)) AS kd_negara2, ISNULL(ISNULL((CASE WHEN
                             (SELECT        TOP 1 tbl_coo_negara.kd_negara
                               FROM            tbl_coo_negara
                               WHERE        tbl_coo_negara.kd_negara = dbo.tbl_coo_data_dtl.COUNTRY_OF) IS NULL THEN
                             (SELECT        TOP 1 tbl_coo_negara.kd_negara
                               FROM            tbl_coo_negara
                               WHERE        tbl_coo_negara.country_code = dbo.tbl_coo_data_dtl.COUNTRY_OF) WHEN
                             (SELECT        TOP 1 tbl_coo_negara.kd_negara
                               FROM            tbl_coo_negara
                               WHERE        tbl_coo_negara.country_code = dbo.tbl_coo_data_dtl.COUNTRY_OF) IS NULL THEN
                             (SELECT        TOP 1 tbl_coo_negara.kd_negara
                               FROM            tbl_coo_negara
                               WHERE        tbl_coo_negara.country_code = dbo.tbl_coo_data_dtl.COUNTRY_OF) END), dbo.tbl_coo_data_dtl.COUNTRY_OF), dbo.tbl_data_supplier.supplier_country) AS kd_neg, 
                         CASE WHEN CAROUD = 'RD' THEN ROUND(CONVERT(decimal(32, 4), CONVERT(float, dbo.tbl_coo_data_dtl.ARRIV_PLAN_NUMBER) * CONVERT(float, dbo.tbl_coo_data_dtl.PRICE / CONVERT(float, 
                         ISNULL(dbo.tbl_coo_data_dtl.CAUPRI, 1)))), 2, 1) WHEN CAROUD = 'RU' THEN CONVERT(float, dbo.tbl_coo_data_dtl.ARRIV_PLAN_NUMBER) * CONVERT(float, dbo.tbl_coo_data_dtl.PRICE / CONVERT(float, 
                         ISNULL(dbo.tbl_coo_data_dtl.CAUPRI, 1))) ELSE CONVERT(decimal(32, 4), CONVERT(float, dbo.tbl_coo_data_dtl.ARRIV_PLAN_NUMBER) * CONVERT(float, dbo.tbl_coo_data_dtl.PRICE / CONVERT(float, 
                         ISNULL(dbo.tbl_coo_data_dtl.CAUPRI, 1)))) END AS amount, CASE WHEN CHARINDEX('.', dbo.tbl_coo_data_dtl.SHIP_COMPANY) 
                         > 0 THEN dbo.tbl_coo_data_dtl.INVOICE_NO ELSE ISNULL(dbo.tbl_coo_data_dtl.SHIP_COMPANY, dbo.tbl_coo_data_dtl.INVOICE_NO) END AS INVOICE_NO, CONVERT(float, ISNULL(dbo.tbl_coo_data_dtl.CAUPRI, 
                         1)) AS CAUPRI, dbo.tbl_coo_data_dtl.CATAMZ, dbo.tbl_coo_data_dtl.CAROUD, CONVERT(float, dbo.tbl_coo_data_dtl.PRICE / CAST(ISNULL(dbo.tbl_coo_data_dtl.CAUPRI, 1) AS float)) AS PRICE
FROM            dbo.tbl_coo_data INNER JOIN
                         dbo.tbl_coo_data_dtl ON dbo.tbl_coo_data.id_coo_data = dbo.tbl_coo_data_dtl.id_coo_data LEFT OUTER JOIN
                         dbo.tbl_data_supplier ON dbo.tbl_coo_data_dtl.VENDOR = dbo.tbl_data_supplier.subcode_as400 LEFT OUTER JOIN
                         dbo.v_data_part_hs ON dbo.tbl_coo_data_dtl.ITEM_NUMBER = dbo.v_data_part_hs.part_no
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 = Begin Table = "tbl_coo_data" Begin Extent = Top = 6 Left = 38 Bottom = 114 Right = 189 End DisplayFlags = 280 TopColumn = 10 End Begin Table = "tbl_coo_data_dtl" Begin Extent = Top = 6 Left = 227 Bottom = 114 Right = 416 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "tbl_data_supplier" Begin Extent = Top = 114 Left = 38 Bottom = 222 Right = 205 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "v_data_part_hs" Begin Extent = Top = 114 Left = 243 Bottom = 162 Right = 394 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 72 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 ', 'SCHEMA', N'dbo', 'VIEW', N'v_coo_data'
GO

EXEC sys.sp_addextendedproperty N'MS_DiagramPane2', 
N'= 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 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 ', 'SCHEMA', N'dbo', 'VIEW', N'v_coo_data'
GO

EXEC sys.sp_addextendedproperty N'MS_DiagramPaneCount', 2, 'SCHEMA', N'dbo', 'VIEW', N'v_coo_data'
GO